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.*
       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.*,
       pl.NAME price_list_name,
       org.NAME warehouse_name,
       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.*,
       ht.global_timezone_name timezone_name,
       hcas.org_id acct_org_id,
       ou.NAME acct_org_name,
  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

0 Responses to “Query to Fetch Party, Customer Account, Site Details R12”

Post a Comment


The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.