AR Bill to Ship to Party Report

 AR Bill to Ship to Party Report

Select hps.party_site_number  ship_to_party_site_num

,(select hps_1.party_site_number 

from  hz_party_sites hps_1

    ,hz_cust_acct_sites_all hcs_1

    ,hz_cust_site_uses_all  hcsu_1

where hps_1.party_site_id = hcs_1.party_site_id

and hcs_1.CUST_ACCT_SITE_ID  = hcsu_1.CUST_ACCT_SITE_ID 

and hcsu_1.SITE_USE_CODE = 'BILL_TO'

--and hps_1.party_site_name = hps.party_site_name

and hcsu_1.site_use_id = hcsu.BILL_TO_SITE_USE_ID 

) bill_to_party_site_num

,hca.account_number

from hz_party_sites hps

, hz_cust_acct_sites_all hcs 

,hz_cust_site_uses_all  hcsu

,hz_cust_accounts hca

where hps.party_site_name = :P_PARTY_SITE_NAME

and hps.party_site_id = hcs.party_site_id

and hcs.CUST_ACCT_SITE_ID  = hcsu.CUST_ACCT_SITE_ID 

and hcsu.SITE_USE_CODE = 'SHIP_TO'

and hca.cust_account_id = hcs.cust_account_id