某一个OU下只有收单地点、只有收货方和收单收货方的所有客户

某一个OU下只有收单地点的SQL:

 

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 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