Friday, September 14, 2012
Query to Fetch Party, Customer Account, Site Details R12
Below queries developed by us is just a reference for newbies in TCA,
Query to fetch Party, Person profile and Organization profile details
SELECT hp.*
,hpp.*
,hop.*
FROM hz_parties hp,
hz_person_profiles hpp,
hz_organization_profiles hop
WHERE hp.party_id = hpp.party_id(+)
AND hp.party_id = hop.party_id(+)
AND hop.content_source_type(+) = 'USER_ENTERED'
AND hpp.content_source_type(+) = 'USER_ENTERED'
AND hop.effective_end_date IS
NULL
AND hpp.effective_end_date IS
NULL
AND hp.status != 'M'
AND hp.party_name = 'SHAREORACLEAPPS';
Query to fetch customer account details
SELECT cust.*,
cust.price_list_id,
pl.NAME price_list_name,
cust.warehouse_id,
org.NAME warehouse_name,
hcp.profile_class_id,
hcpc.NAME AS cust_profile_class_name
FROM hz_cust_accounts cust,
so_price_lists pl,
hr_all_organization_units org,
hz_cust_profile_classes hcpc,
hz_customer_profiles hcp
WHERE cust.price_list_id =
pl.price_list_id(+)
AND hcp.cust_account_id = cust.cust_account_id
AND hcp.site_use_id IS NULL
AND hcp.profile_class_id = hcpc.profile_class_id
AND cust.warehouse_id =
org.organization_id(+)
AND cust.cust_account_id = :vl_cust_account_id
Query to fetch Party site, Customer Account Site, address details
SELECT hps.*,
hl.timezone_id,
ht.global_timezone_name
timezone_name,
hcas.org_id acct_org_id,
ou.NAME acct_org_name,
hl.*,
hcas.*
FROM hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hr_operating_units ou,
hz_timezones ht,
hz_cust_accounts hca
WHERE hl.location_id =
hps.location_id
AND hps.party_site_id =
hcas.party_site_id
AND ou.organization_id =
hcas.org_id
AND hca.cust_account_id = hcas.cust_account_id
AND ht.timezone_id(+) =
hl.timezone_id
AND hps.status !=
'M'
AND hcas.cust_account_id = :vl_cust_account_id
AND hcas.cust_acct_site_id = :vl_cust_acct_site_id
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Query to Fetch Party, Customer Account, Site Details R12”
Post a Comment