EBS:地点查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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

  

posted @   samrv  阅读(200)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示