客户档表关联

----------------------------********客户地点层传真************---------------------------------

SELECT HCA.ACCOUNT_NUMBER,HP.PARTY_NAME, CON.RAW_PHONE_NUMBER,CON.CONTACT_POINT_ID
     FROM hz_contact_points con,hz_party_sites HPS,HZ_PARTIES HP ,HZ_CUST_ACCOUNTS HCA
     WHERE con.owner_table_id = HPS.party_site_id
     AND HPS.PARTY_ID=HP.PARTY_ID
     AND HP.PARTY_ID=HCA.PARTY_ID
     AND con.PHONE_LINE_TYPE ='FAX'
     AND CON.STATUS='A' ;

 

----------------------------********客户收单方联系人************---------------------------------

/*BEGIN
  MO_GLOBAL.init('ONT');
END;
*/

SELECT HCA.ACCOUNT_NUMBER,
       HP.PARTY_NAME,
       DECODE(ACCT_ROLE.CUST_ACCOUNT_ROLE_ID,
              NULL,
              NULL,
              SUBSTRB(PARTY.PERSON_LAST_NAME, 1, 50) || '' ||
              SUBSTRB(PARTY.PERSON_FIRST_NAME, 1, 40)) CONTACT_NAME
  FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
       HZ_PARTIES            PARTY,
       HZ_RELATIONSHIPS      REL,
       HZ_CUST_SITE_USES     SU,
       HZ_CUST_ACCOUNTS      HCA,
       HZ_PARTIES            HP

 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+)
   AND REL.SUBJECT_ID = PARTY.PARTY_ID(+)
   AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
   AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
   AND REL.DIRECTIONAL_FLAG(+) = 'F'
   AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT'
   AND SU.CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID(+)
   AND HCA.PARTY_ID = HP.PARTY_ID(+)
   AND ACCT_ROLE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
   AND SU.SITE_USE_CODE = 'BILL_TO'

 

---------------**************订单关联的客户****************----------------------

SELECT HP.PARTY_NAME, HCA.ACCOUNT_NUMBER
  FROM HZ_PARTIES            HP,
       HZ_CUST_ACCOUNTS      HCA,
       HZ_CUST_SITE_USES_ALL SHIP_SU,
       OE_ORDER_LINES_ALL    OOL,
       OE_ORDER_HEADERS_ALL  OOH
 WHERE HP.PARTY_ID = HCA.PARTY_ID
   AND OOH.ORG_ID = SHIP_SU.ORG_ID
   AND OOH.SOLD_TO_ORG_ID = HCA.cust_account_id
   AND OOH.HEADER_ID = OOL.HEADER_ID
   AND OOH.Ship_To_Org_Id = SHIP_SU.SITE_USE_ID
   AND OOH.ORDER_NUMBER =:ORDERNUMBER

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24899662/viewspace-683711/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24899662/viewspace-683711/