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