add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, July 23

Query to get Customer Informaton

SELECT DISTINCT hp.party_name "Customer Name",
  hca.account_number,
  hca.status,
  hcsu.location,
  hcsu.site_use_code,
  hcsu.status loc_stat,
  ps.class,
  hcsu.site_use_id,
  hcpc.name profile_name,
  hl.address1,
  hl.address2,
  hl.address3,
  hl.city,
  hl.state,
  hl.postal_code,
  ps.customer_id,
  ps.customer_site_use_id,
  hps.identifying_address_flag,
  ps.trx_date,
  HOU.NAME "Operating Unit"
FROM apps.hz_parties hp,
  apps.hz_party_sites hps,
  apps.hz_locations hl,
  apps.hz_cust_accounts hca,
  apps.hz_cust_acct_sites hcas,
  apps.hz_cust_site_uses hcsu,
  apps.hz_customer_profiles hcp,
  apps.hz_cust_profile_classes hcpc,
  apps.ar_payment_schedules_all ps,
  apps.hr_operating_units hou
WHERE hp.party_id           = hca.party_id(+)
AND hp.party_id             = hcp.party_id
AND hp.party_id             = hps.party_id
AND hps.party_site_id       = hcas.party_site_id
AND hps.location_id         = hl.location_id
AND hca.cust_account_id     = hcas.cust_account_id
AND hcas.cust_acct_site_id  = hcsu.cust_acct_site_id
AND hca.cust_account_id     = hcp.cust_account_id
AND hca.cust_account_id     = ps.customer_id
AND hcp.profile_class_id    = hcpc.profile_class_id
AND ps.customer_site_use_id =hcsu.site_use_id andhcsu.org_id =hou.organization_id;

No comments: