Tech Trainings

Oracle Cloud ERP – Customer Org References Query

June 9, 2022 by admin

SELECT  
 hp.party_number, hp.party_name, hca.ACCOUNT_NUMBER,
 TRUNC(HCA.ACCOUNT_TERMINATION_DATE  )    AccountStatus,
 HPS.PARTY_SITE_NAME PARTY_SITE_NAME,
   hps.party_site_number  SITE_NUMBER,
 
       hl.address1        ,
       hl.address2        ,
       hl.address3        ,
       hl.address4        ,
       hl.city            ,
       hl.state           ,
       hl.postal_code      	
,hp.ORIG_SYSTEM_REFERENCE PARTY_ORIG_REF
,   hca.ORIG_SYSTEM_REFERENCE   CUST_ACC_ORIG_REF
,  hcasa.ORIG_SYSTEM_REFERENCE CUST_ACC_SITE_ORG_REF	 
,hl.ORIG_SYSTEM_REFERENCE LOCATION_REF  
from hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, hz_party_sites hps, hz_cust_accounts hca, hz_parties hp
, hz_locations hl
where 1=1
and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
and hps.party_site_id = hcasa.party_site_id
and hcasa.cust_account_id = hca.cust_account_id
and hp.party_id = hps.party_id
AND HP.PARTY_NUMBER  = :P_PARTY_NUMBER
AND hl.location_id = hps.location_id 

Comments |0|