EBS:地点查询

SELECT HL.LOCATION_ID,
       HL.BUSINESS_GROUP_ID,
       HL.LOCATION_CODE AS "名称",
       HL.DESCRIPTION AS "说明",
       HL.INACTIVE_DATE AS "无效日期",
       HL.LEGAL_ADDRESS_FLAG AS "法定地址",
       HL.SHIP_TO_LOCATION_ID AS "收货地点ID",
       (SELECT HL2.LOCATION_CODE FROM HR.HR_LOCATIONS_ALL HL2
        WHERE HL2.LOCATION_ID = HL.SHIP_TO_LOCATION_ID
        ) AS "收货地点",
       HL.SHIP_TO_SITE_FLAG AS "收货地点",
       HL.RECEIVING_SITE_FLAG AS "接收地点",
       HL.BILL_TO_SITE_FLAG AS "收单地点",
       HL.IN_ORGANIZATION_FLAG AS "内部地点",
       HL.OFFICE_SITE_FLAG AS "办公地点",
       HL.INVENTORY_ORGANIZATION_ID AS "库存组织ID",
       (SELECT HOU.NAME FROM HR_ALL_ORGANIZATION_UNITS HOU
         WHERE HOU.ORGANIZATION_ID = HL.INVENTORY_ORGANIZATION_ID
         AND ROWNUM = 1 ) AS "库存组织",
       HL.STYLE AS "参考字段",  
       HL.ADDRESS_LINE_1 AS "地址行1",
       HL.ADDRESS_LINE_2 AS "地址行2",
       HL.ADDRESS_LINE_3 AS "地址行3",
       HL.TOWN_OR_CITY AS "省/市/SAR代码", 
       (SELECT --HRL.LOOKUP_CODE AS  TOWN_OR_CITY, 
               HRL.MEANING AS "省/市/SAR"  
           FROM HR_LOOKUPS HRL
          WHERE HRL.LOOKUP_TYPE = 'CN_PROVINCE' 
           AND HRL.LOOKUP_CODE = HL.TOWN_OR_CITY
           AND ROWNUM =1 ) AS "省/市/SAR",
       HL.COUNTRY AS "国家(地区)代码",
       (SELECT FT.TERRITORY_SHORT_NAME   -- 国家名称
               --FT.TERRITORY_CODE  AS COUNTRY -- 国家代码
          FROM FND_TERRITORIES_VL FT  
         WHERE OBSOLETE_FLAG<>'Y' 
           AND FT.TERRITORY_CODE = HL.COUNTRY
           AND ROWNUM = 1 
         --ORDER BY TERRITORY_SHORT_NAME 
          ) AS "国家(地区)",
       HL.POSTAL_CODE AS "邮政编码"
      -- ,HL.* 
  FROM HR.HR_LOCATIONS_ALL HL
  WHERE HL.INACTIVE_DATE IS NULL  -- 无效日期 
 
-- SELECT * FROM HR_ALL_ORGANIZATION_UNITS