Oracle ERP 標準地址串法

 

Oracle ERP 標準地址串法

 

一、Set OU

1. 從菜單 Edit->Preferences->Profiles    可通過MO: Operating Unit查詢出OU(假設該值為：Vision Operations)2. 再通過以下SQL可查詢出對應OU ID

SELECT ORGANIZATION_ID
  FROM HR_OPERATING_UNITS
 WHERE NAME = 'Vision Operations';

3. 假定查出的結OU ID為204，通過以下PL/SQL設定Client環境變數

BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(204);
END;

二、Standard Address SQL

SELECT ARP_ADDR_PKG.FORMAT_ADDRESS(RA.ADDRESS_STYLE,
                                   RA.ADDRESS1,
                                   RA.ADDRESS2,
                                   RA.ADDRESS3,
                                   RA.ADDRESS4,
                                   RA.CITY,
                                   RA.COUNTY,
                                   RA.STATE,
                                   RA.PROVINCE,
                                   RA.POSTAL_CODE,
                                   TERR.TERRITORY_SHORT_NAME) CONCATENATED_ADDRESS
  FROM RA_ADDRESSES       RA
      ,FND_TERRITORIES_VL TERR
 WHERE RA.ADDRESS_ID = P_ADDRESS_ID --參數為指定的地址ID
   AND RA.COUNTRY = TERR.TERRITORY_CODE(+);