add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, January 30

Oracle Receivable Contacts Query



SELECT DISTINCT
  (rc.first_name||' '||rc.last_name) name,
  rc.title            ,
  DECODE(HCP.PHONE_LINE_TYPE,'GEN',HCP.PHONE_AREA_CODE ||'-' ||HCP.PHONE_NUMBER,NULL) "tel_number",
  hcp.phone_extension "Extension"      ,
  DECODE(HCP.PHONE_LINE_TYPE,'FAX',HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER,NULL) FAX_NUMBER ,
  rc.email_address                    ,
  hca.account_number "Legacy customer",
FROM hz_parties hp          ,
  ra_contacts rc               ,
  hz_cust_site_uses_all hcsu   ,
  hz_cust_acct_sites_all hcas  ,
  hz_party_sites hps           ,
  HZ_CUST_ACCOUNTS hca         ,
  hz_party_relationships hprel ,
  HZ_CONTACT_POINTS HCP
WHERE 1 = 1
   AND hcsu.cust_acct_site_id        =hcas.cust_Acct_Site_id
   AND hcas.party_site_id            =hps.party_site_id
   AND rc.contact_id                 =hcSU.CONTACT_ID
   AND rc.party_relationship_id      = hprel.party_relationship_id
   AND hprel.party_id                = hcp.owner_table_id
   AND hprel.party_relationship_type = 'CONTACT_OF'
   AND rc.status                     = 'A'
   AND hcp.status                    = 'A'
   AND hcp.contact_point_type        ='PHONE'
   AND hp.party_id                   =hps.party_id
   AND HCA.CUST_ACCOUNT_ID           =HCAS.CUST_ACCOUNT_ID
   AND hcsu.site_use_code            ='SHIP_TO'
   AND HCSU.STATUS                   = 'A'
   AND hcas.org_id                   =736;
   --AND hcas.org_id =457;

No comments: