All customers under an OU that have only the receipt location, only the receiving party and the receipt receipt

SQL in an OU that has only a single receipt location:

SELECT hou.name org_name, Hp.party_name customer_name, Hp.party_number customer_number, Bill_to_cust.cust_acct_site_ ID cust_acct_site_id, hps.party_site_id party_site_id, Hps.party_site_number party_site_number, Hca.account_number Account_number from ar.hz_cust_accounts HCA, hr_operating_units Hou, hz_parties hp, hz_party_sites HPS, (SELECT bill_cust . cust_account_id cust_account_id, bill_cust.cust_acct_site_id cust_acct_site_id, bill_cust.party_site_id party_site _id, bill_cust.org_id org_id from (SELECT hcasa.cust_account_id cust_account_id, hcasa.cust_acct_site_id cust_acct_ site_id, hcasa.party_site_id party_site_id, bill.org_id from Hz_cust_acct_sites_all Hcasa, (SELECT hcsua.cust_acct_ site_id cust_acct_site_id, hcsua.org_id org_id from Hz_cust_site_uses_all hcsua WHERE hcsua.site_use_code = ' BILL_TO ' and Hcsua.status = ' A ') Bill WHERE hcasa.cust_acct_site_id = bill.cust_acct_site_id and hcasa.org_id = bill.org_id and not EX Ists (SELECT hcasa.cust_account_id, hcasa.party_site_id party_site_id, cust_site.org_id from Hz_cust_acct_sites_all cust_site, Hz_cust_site_uses_all hcsua WHERE hcsua.cust_acct_ site_id = cust_site.cust_acct_site_id and cust_site.cust_account_id = hcasa.cust_account_id and cust_site.org_id = Hcasa . org_id and Hcsua.site_use_code = ' ship_to ' and hcsua.status = ' A ') bill_cust) bill_to_cust WHERE hp.party_id = Hca.party _id and hca.cust_account_id = bill_to_cust.cust_account_id and hou.organization_id = bill_to_cust.org_id and Bill_to_ cust.party_site_id = hps.party_site_id and hca.status = ' A ' ORDER by customer_name, Org_name;

SQL for only the receiving location under an OU:

SELECT hou.name org_name, Hp.party_name customer_name, Hp.party_number customer_number, Ship_to_cust.cust_acct_site_ ID cust_acct_site_id, hps.party_site_id party_site_id, Hps.party_site_number party_site_number, Hca.account_number Account_number from ar.hz_cust_accounts HCA, hr_operating_units Hou, hz_parties hp, hz_party_sites HPS, (SELECT ship_cust . cust_account_id cust_account_id, ship_cust.cust_acct_site_id cust_acct_site_id, ship_cust.party_site_id party_site _id, ship_cust.org_id org_id from (SELECT hcasa.cust_account_id cust_account_id, hcasa.cust_acct_site_id, hcasa.party_ site_id, ship.org_id from Hz_cust_acct_sites_all Hcasa, (SELECT hcsua.cust_acct_site_id cust_acct_site_id, Hcsua.org_ ID org_id from Hz_cust_site_uses_all hcsua where hcsua.site_use_code = ' ship_to ' and hcsua.status = ' A ') the ship where Hcasa. cust_acct_site_id = ship.cust_acct_site_id and hcasa.org_id = ship.org_id and not EXISTS (SELECT hcasa.cust_account_id, CU st_site.org_id from Hz_cust_acct_sites_all Cust_site,Hz_cust_site_uses_all hcsua WHERE hcsua.cust_acct_site_id = cust_site.cust_acct_site_id and cust_site.cust_account_id = hcasa.cust_account_id and hcasa.org_id = cust_site.org_id and Hcsua.site_use_code = ' bill_to ' and hcsua.status = ' A ') s Hip_cust) ship_to_cust WHERE hp.party_id = hca.party_id and hca.cust_account_id = ship_to_cust.cust_account_id and hou.or ganization_id = ship_to_cust.org_id and ship_to_cust.party_site_id = hps.party_site_id and hca.status = ' A ' ORDER by HP.PA Rty_name, Hou.name;

SQL with both a receipt location and a receiving location under an OU:

SELECT hou.name org_name, Hp.party_name customer_name, Hp.party_number customer_number, hcasa.cust_acct_site_id cust_ acct_site_id, hcasa.party_site_id party_site_id, Hps.party_site_number party_site_number, Hca.account_number account _number from ar.hz_cust_accounts HCA, hr_operating_units Hou, hz_parties hp, hz_party_sites HPS, Hz_cust_acct_sites_all H Casa WHERE hp.party_id = hca.party_id and hcasa.cust_account_id = hca.cust_account_id and hou.organization_id = Hcasa.org_ ID and hcasa.party_site_id = hps.party_site_id and hca.status = ' A ' minus SELECT hou.name org_name, Hp.party_name customer _name, Hp.party_number customer_number, bill_to_cust.cust_acct_site_id cust_acct_site_id, hps.party_site_id party_ site_id, Hps.party_site_number Party_site_number, Hca.account_number account_number from ar.hz_cust_accounts HCA, hr_ Operating_units Hou, hz_parties hp, hz_party_sites HPS, (SELECT bill_cust.cust_account_id cust_account_id, Bill_ cust.cust_acct_site_id cust_acct_site_id, bill_cust.party_site_id party_site_id, bill_cust.org_id org_id from (SELECT hcasa.cust_account_id cust_account_id, Hcasa.cust_acct_site _id cust_acct_site_id, hcasa.party_site_id party_site_id, bill.org_id from Hz_cust_acct_sites_all Hcasa, (SELECT hcsua.cust_acct_site_id cust_acct_site_id, hcsua.org_id org_id from Hz_cust_site_uses_all hcsua WHERE hcsua.site_use_ Code = ' Bill_to ' and hcsua.status = ' A ') Bill WHERE hcasa.cust_acct_site_id = bill.cust_acct_site_id and hcasa.org_id = Bi LL.ORG_ID and not EXISTS (SELECT hcasa.cust_account_id, hcasa.party_site_id party_site_id, cust_site.org_id from Hz_cust _acct_sites_all cust_site, Hz_cust_site_uses_all hcsua WHERE hcsua.cust_acct_site_id = cust_site.cust_acct_site_id and cust_site.cust_account_id = hcasa.cust_account_id and cust_site.org_id = hcasa.org_id and Hcsua.site_use_code = ' SHIP_ To ' and hcsua.status = ' A ') bill_cust) bill_to_cust WHERE hp.party_id = hca.party_id and hca.cust_account_id = Bill_to_cu st.cust_account_id and hou.organization_id = bill_to_cust.org_id and bill_to_cust.party_site_id = hps.party_site_id and hca.status = ' A ' minus SELECT hou.name org_name, HP.P Arty_name customer_name, Hp.party_number customer_number, ship_to_cust.cust_acct_site_id cust_acct_site_id, hps.party_site_id party_site_id, Hps.party_site_number party_site_number, Hca.account_number account_number from Ar.hz_cust_accounts HCA, hr_operating_units Hou, hz_parties hp, hz_party_sites HPS, (SELECT ship_cust.cust_account_id cust_account_id, ship_cust.cust_acct_site_id cust_acct_site_id, ship_cust.party_site_id party_site_id, Ship_ cust.org_id org_id from (SELECT hcasa.cust_account_id cust_account_id, hcasa.cust_acct_site_id, hcasa.party_site_id, ship.org_id from Hz_cust_acct_sites_all Hcasa, (SELECT hcsua.cust_acct_site_id cust_acct_site_id, hcsua.org_id org_id From Hz_cust_site_uses_all hcsua where hcsua.site_use_code = ' ship_to ' and hcsua.status = ' A ') ship where hcasa.cust_acct_ site_id = ship.cust_acct_site_id and hcasa.org_id = ship.org_id and not EXIsts (SELECT hcasa.cust_account_id, cust_site.org_id from Hz_cust_acct_sites_all Cust_site, Hz_cust_site_uses_all Hcsua WHERE hcsua.cust_acct_site_id = cust_site.cust_acct_site_id and cust_site.cust_account_id = Hcasa.cust_account_ ID and hcasa.org_id = cust_site.org_id and Hcsua.site_use_code = ' bill_to ' and hcsua.status = ' A ') ship_cust) Ship_to_cus T WHERE hp.party_id = hca.party_id and hca.cust_account_id = ship_to_cust.cust_account_id and hou.organization_id = ship_t o_cust.org_id and ship_to_cust.party_site_id = hps.party_site_id and hca.status = ' A ' ORDER by customer_name, Org_name;