AR发票与客户的关联关系

 

SELECT RCA.BILL_TO_CUSTOMER_ID
      ,RCA.BILL_TO_SITE_USE_ID
      ,RCA.BILL_TO_ADDRESS_ID
      ,RCA.*
  FROM RA_CUSTOMER_TRX_ALL       RCA
      ,RA_CUSTOMER_TRX_LINES_ALL RCLA
 WHERE RCA.CUSTOMER_TRX_ID = RCLA.CUSTOMER_TRX_ID
   AND RCA.CUSTOMER_TRX_ID = 1255735
   AND RCA.ORG_ID = 128;

--根据收单方/收货方ID(BILL_TO SHIP_TO)--SITE_USE_ID获取客户地点ID--CUST_ACCT_SITE_ID
SELECT HCSUA.SITE_USE_ID
      ,HCSUA.SITE_USE_CODE
      ,HCSUA.CUST_ACCT_SITE_ID
  FROM HZ_CUST_SITE_USES_ALL HCSUA
 WHERE HCSUA.SITE_USE_ID = 4448; --bill_to_site_use_id
--根据客户地点ID--CUST_ACCT_SITE_ID获取PARTY_SITE_ID
SELECT HCASA.CUST_ACCT_SITE_ID
      ,HCASA.CUST_ACCOUNT_ID
      ,HCASA.PARTY_SITE_ID
  FROM HZ_CUST_ACCT_SITES_ALL HCASA
 WHERE HCASA.CUST_ACCT_SITE_ID = 4335;
--根据客户PARTY_SITE_ID获取LOCATION_ID
SELECT HPS.PARTY_SITE_ID
      ,HPS.PARTY_ID
      ,HPS.PARTY_SITE_NUMBER
      ,HPS.LOCATION_ID
      ,HPS.PARTY_SITE_NAME
  FROM HZ_PARTY_SITES HPS
 WHERE HPS.PARTY_SITE_ID = 4385;
--根据LOCATION_ID获取地址详细信息
SELECT * FROM HZ_LOCATIONS HL WHERE HL.LOCATION_ID = 3759;

--发票收单方收货方获取方式

CREATE OR REPLACE FUNCTION MEW_GET_CUSTOMER_ADDRESS_FNC(P_SITE_USE_ID NUMBER)
  RETURN VARCHAR2 IS
  V_ADDRESS VARCHAR2(1000);
BEGIN
  SELECT HL.ADDRESS1 || '
  ' || HL.ADDRESS2 || '
  ' || HL.ADDRESS3 || '
  ' || HL.ADDRESS4
    INTO V_ADDRESS
    FROM HZ_CUST_SITE_USES_ALL  HCSUA
        ,HZ_CUST_ACCT_SITES_ALL HCASA
        ,HZ_PARTY_SITES         HPS
        ,HZ_LOCATIONS           HL
   WHERE HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID
     AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
     AND HPS.LOCATION_ID = HL.LOCATION_ID
     AND HCSUA.SITE_USE_ID = P_SITE_USE_ID;
  RETURN V_ADDRESS;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/