TCA Queries for R12

Below are 2 TCA queries to get Party and Account data for R12.

---
Set the Context
----
exec mo_global.set_policy_context('S',&enter_org_id);


select stca.cust_account_id customer, u.cust_acct_site_id address,
stcsu.site_use_id site, stpsu.location_id party_location
from  hz_cust_site_uses u,
hz_cust_accounts stca,
hz_party_sites   stpsu,
hz_cust_acct_sites stps,
hz_cust_site_uses stcsu
where STCA.cust_account_id = l_cust_acct_id

AND u.cust_acct_site_id  = l_site_use_id
AND u.site_use_code = 'SHIP_TO'
AND u.site_use_id = STCSU.site_use_id
AND STCSU.cust_acct_site_id = STPS.cust_acct_site_id (+)
AND STPS.party_site_id = STPSU.party_site_id (+);


SELECT
CUST_ACCT.party_id,
CUST_ACCT.party_id,
ACCT_SITE.party_site_id,
ACCT_SITE.party_site_id,
LOC.location_id,
ACCT_SITE.cust_acct_site_id
FROM
hz_cust_accounts     CUST_ACCT,
hz_parties     PARTY,
hz_cust_acct_sites     ACCT_SITE,
hz_cust_site_uses     SITE_USES,
hz_party_sites     PARTY_SITE,
hz_locations     LOC
WHERE
CUST_ACCT.cust_account_id = l_cust_acct_id
AND CUST_ACCT.party_id = PARTY.party_id
AND CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id
AND ACCT_SITE.cust_acct_site_id = SITE_USES.cust_acct_site_id
AND SITE_USES.site_use_id = l_site_use_id
AND ACCT_SITE.party_site_id = PARTY_SITE.party_site_id
AND PARTY_SITE.location_id = LOC.location_id;