add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, June 8

Customer Cantacts Details in oracle


SELECT  
          hl.orig_system_reference
         ,hl.country
         ,hl.address1
         ,hl.address2
         ,hl.address3
         ,hl.address4
         ,hl.city
         ,hl.postal_code
         ,hl.state
         ,hl.province
         ,hl.county
         ,hl.content_source_type
         ,hl.actual_content_source actual_content_source
         ,hps.party_site_number
         ,hps.identifying_address_flag
         ,hps.status
         ,hps.party_site_name
         ,hps.created_by_module
         ,hps.actual_content_source site_actual_content_source
         ,hcasa.orig_system_reference site_orig_system_reference
         ,hcasa.status         acct_site_status
         ,hp.party_number
         ,hp.party_id
         ,hca.account_number
         ,hou.NAME operting_unit_name
         ,bill_to_flag
         ,HPS.PARTY_SITE_ID
         ,hcp.CONTACT_POINT_ID     
         ,hcp.CONTACT_POINT_TYPE   
         ,hcp.EMAIL_ADDRESS
         ,hcp.PHONE_NUMBER
         ,hcp.URL
         ,hcp.CONTACTS
         ,hcp.STATUS 
         ,hcp.OWNER_TABLE_NAME    
         ,hcp.OWNER_TABLE_ID      
         ,hcp.PRIMARY_FLAG          
         ,hcp.ORIG_SYSTEM_REFERENCE  
         
         
   FROM  ar.hz_locations        hl
        ,ar.hz_party_sites      hps
        ,ar.hz_cust_acct_sites_all  hcasa
        ,ar.hz_parties     hp
        ,ar.hz_cust_accounts   hca
        ,hr_operating_units hou
        ,HZ_CONTACT_POINTS hcp
       
   WHERE hcasa.party_site_id     = hps.party_site_id
   AND   hps.location_id         = hl.location_id
   AND   hp.party_id             = hca.party_id
   AND   hp.party_id             = hps.party_id
   AND   hca.cust_account_id     = hcasa.cust_account_id
   AND   hcasa.org_id            = hou.organization_id
   AND   hp.party_type           ='ORGANIZATION'
   AND   hps.status              = 'A'
   AND   hcasa.status            = 'A'
   AND   hp.status               = 'A'
   AND   hca.status              = 'A'
   AND   hp.party_name ='ECOLAB'
   AND   hcp.OWNER_TABLE_NAME ='HZ_PARTY_SITES'
   AND   hcp.CONTACT_POINT_PURPOSE='BUSINESS'
   AND   hcp.CONTACT_POINT_TYPE='EMAIL'
   And   hcp.OWNER_TABLE_ID=HPS.PARTY_SITE_ID(+)
  


Select
            CONTACT_POINT_ID      ,        
            CONTACT_POINT_TYPE     ,  
            EMAIL_ADDRESS,
            PHONE_NUMBER,
            URL,
            CONTACTS,    
            STATUS                  ,      
            OWNER_TABLE_NAME         ,     
            OWNER_TABLE_ID            ,    
            PRIMARY_FLAG               ,   
            ORIG_SYSTEM_REFERENCE         

From
            HZ_CONTACT_POINTS

Where 1=1
AND OWNER_TABLE_NAME ='HZ_PARTY_SITES'
AND CONTACT_POINT_PURPOSE='BUSINESS'
AND CONTACT_POINT_TYPE='EMAIL'
And OWNER_TABLE_ID = 10405 --Party_site_id

No comments: