Wednesday, March 30, 2016

Query to find customer site level contact points R12 ,Customer phone number



Customer Site level contact points

Modules Involved : AR
Description      : get the customer site level communication details

select HP.PARTY_NAME               PARTY_NAME,
       hps.party_site_number       Site_Number,
       hcp.contact_point_type      Contact_Type,
       hcp.phone_line_type         Phone_Line_Type,
       hcp.phone_country_code      Country_Code,
       hcp.phone_number            Phone_number,
       hcp1.contact_point_type     Contact_Type,
       hcp1.email_address          Email_Address ,
       HCP1.EMAIL_FORMAT           EMAIL_FORMAT,
       HCP1.CONTACT_POINT_PURPOSE  CONTACT_POINT_PURPOSE,
       HL.ADDRESS1||','||HL.ADDRESS2||' '||HL.ADDRESS3||' '||HL.ADDRESS4||' '||HL.CITY||','||HL.STATE||','
       ||hl.POSTAL_CODE||','||hl.country SITE_ADDRESS
  FROM apps.hz_parties             hp,
       apps.hz_party_sites         hps,
       APPS.HZ_CONTACT_POINTS      HCP,
       APPS.HZ_CONTACT_POINTS      HCP1,
       apps.hz_locations           hl
WHERE 1=1
  AND hp.party_id              = hps.party_id
  AND hcp.owner_table_name     = 'HZ_PARTY_SITES'
  AND hcp.owner_table_id       = hps.party_site_id
  AND hcp.contact_point_type   = 'PHONE'
  AND hcp1.owner_table_name    = 'HZ_PARTY_SITES'
  AND hcp1.owner_table_id      = hps.party_site_id
  and HCP1.CONTACT_POINT_TYPE  = 'EMAIL'
  and hps.location_id          = hl.location_id
  and HPS.PARTY_SITE_NUMBER    = '100095'

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...