oracle ebs级联查询,Oracle EBS常用查询 － 客户

Oracle EBS中对客户信息进行了划分，从不同层次上对其进行管理，从高到低分别是Party / Account / Site。

表HZ_PARTIES用于保存客户PARTY信息

表HZ_CUST_ACCOUNTS_ALL用于保存存客户ACCOUNT信息

表HZ_PARTY_SITES和HZ_CUST_ACCT_SITES_ALL用于保存存客户SITE信息

表HZ_CUST_ACCT_SITE_USES_ALL用于保存存客户SITE用途信息

--查询客户PARTY 和 ACCOUNT信息

SELECT HP.PARTY_ID,HP.PARTY_NUMBER,HP.PARTY_NAME,HP.PARTY_TYPE,HP.CUSTOMER_KEY,HCAA.CUST_ACCOUNT_ID,HCAA.ACCOUNT_NUMBER,HCAA.STATUS,HP.COUNTRY || ' ' || HP.PROVINCE || ' ' || HP.CITY || ' ' || HP.COUNTY || ' ' || HP.ADDRESS1 || ' ' || HP.ADDRESS2 || ' ' || HP.ADDRESS3 || ' ' || HP.ADDRESS4 PARTY_ADDRESS

FROM HZ_PARTIES HP

INNER JOIN HZ_CUST_ACCOUNTS_ALL HCAA ON HP.PARTY_ID = HCAA.PARTY_ID

WHERE PARTY_NAME LIKE '%PARTY-NAME%';

--查询客户SITE信息

SELECT HP.PARTY_ID,HOU.ORGANIZATION_ID,HOU.NAME AS OU_NAME,HCSUA.CUST_ACCT_SITE_ID,HCSUA.SITE_USE_CODE,HCSUA.STATUS AS SITE_STATUS,DECODE(HCSUA.SITE_USE_CODE,'SHIP_TO',HL.ADDRESS1 || ' ' || HL.ADDRESS2 || ' ' || HL.CITY || ' ' || HL.STATE || ' ' || HL.COUNTRY || ' ' || hl.postal_code) "Ship_TO_Address",'BILL_TO',HL.ADDRESS1 || ' ' || HL.ADDRESS2 || ' ' || HL.CITY || ' ' || HL.STATE || ' ' || HL.COUNTRY || ' ' || HL.POSTAL_CODE) "Bill_TO_Address",HP.COUNTRY || ' ' || HP.PROVINCE || ' ' || HP.CITY || ' ' || HP.COUNTY || ' ' || HP.ADDRESS1 || ' ' || HP.ADDRESS2 || ' ' || HP.ADDRESS3 || ' ' || HP.ADDRESS4 PARTY_ADDRESS

FROM HZ_PARTIES HP

INNER JOIN HZ_CUST_ACCOUNTS_ALL HCAA ON HP.PARTY_ID = HCAA.PARTY_ID

inner join HZ_CUST_ACCT_SITES_ALL HCASA on HCASA.CUST_ACCOUNT_ID = HCAA.CUST_ACCOUNT_ID

INNER JOIN HZ_CUST_SITE_USES_ALL HCSUA ON HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID

INNER JOIN HR_OPERATING_UNITS HOU ON HOU.ORGANIZATION_ID = HCASA.ORG_ID

INNER JOIN HZ_PARTY_SITES HPS ON HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID

INNER JOIN hz_locations HL ON HL.LOCATION_ID = HPS.LOCATION_ID

WHERE PARTY_NAME LIKE '%PARTY-NAME%';

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。