R12 AR Customer Contacts Query

  SELECT    distinct
          hp2.party_name                        ,        
          hp1.person_first_name                 ,
           hp1.person_middle_name               ,
           hp1.person_last_name                 ,
           --hp1.party_name,
           hp2.orig_system_reference            ,          
           hp2.person_name_suffix               ,
           hp2.party_number                     ,
           hp2.party_type                       ,
           hcp.contact_point_type               ,
           hcp.contact_point_purpose            ,
           hcp.email_address                    ,
           hcp.telephone_type                   ,
           hcp.owner_table_name                 ,
           hcp.TIME_ZONE                        ,
           hcp.phone_area_code                  ,
           hcp.phone_country_code               ,
           hcp.phone_number                     ,
           hcp.phone_extension                  ,
           hcp.phone_line_type                  ,
           hcp.telex_number                     ,
           hcp.web_type                         ,
           hcp.url                              ,
           hcp.raw_phone_number                 ,
           hcp.primary_flag                     ,
           hp2.party_number subject_party_number,
           hp2.orig_system_reference obj_orig_system_ref,
           hr.relationship_code                 ,
           hr.subject_type                      ,
           hr.object_type                       ,
           hr.relationship_type                 ,
             hr.directional_flag                  ,
           --hr.created_by_module                 ,
           hoc.department_code                  ,
           hoc.department                       ,
           --hoc.title                            ,
           hoc.job_title                        ,
           hoc.status                           ,
           hoc.decision_maker_flag              ,
           --hp1.Person_pre_name_adjunct ,
           hca.account_number,
           hcr.cust_acct_site_id ,
           hcasa.orig_system_reference  acct_site_orig_sys_ref,
           'ACCOUNT__SITE_LEVEL' level_of_contact ,
           hl.address1,
           hl.address2,
           hl.address3,
           hl.address4,
           hl.city,
         hl.postal_code,
         hl.state,
         hl.province,
         hl.county         ,
         hl.country,
         hps.identifying_address_flag  ,
         hou.name,hou.organization_id
      FROM ar.hz_contact_points hcp       ,
           ar.hz_parties hp1              ,
           ar.hz_parties hp2              ,
           ar.hz_relationships hr         ,
           ar.hz_org_contacts hoc  ,
           ar.hz_cust_accounts hca,
           ar.hz_cust_acct_sites_all hcasa,
           ar.hz_cust_account_roles hcr,
           ar.hz_party_sites      hps,
           ar.hz_locations        hl,
           hr_operating_units hou
      WHERE 1                   = 1
      and hp2.party_id          = hca.party_id
      and hcr.role_type         ='CONTACT'
      and hca.cust_account_id   = hcr.cust_account_id
      and hr.party_id           = hcr.party_id
      --and hcr.cust_acct_site_id is not null
      AND  hca.cust_account_id            = hcasa.cust_account_id
      AND  hcr.cust_acct_site_id            = hcasa.cust_acct_site_id
    AND hcp.owner_table_id(+)   = hp1.party_id
    AND hcp.owner_table_name(+) = 'HZ_PARTIES'
    AND hr.subject_id           = hp1.party_id
    AND hr.object_id            = hp2.party_id
    AND hr.relationship_id      = hoc.party_relationship_id
    AND hr.subject_type         = 'PERSON'
    AND hr.object_type          = 'ORGANIZATION'
    AND hr.directional_flag     = 'F'
    AND hoc.status              = 'A'
    AND hca.status              = 'A'
    AND hp1.status              = 'A'
    AND hp2.status              = 'A'
    AND hr.status               = 'A'
    AND NVL(hcp.status,'A')     = 'A'
AND NVL(hcr.status,'A')     = 'A'
  AND hcasa.party_site_id     = hps.party_site_id
  and hps.party_id=hp2.party_id(+)
   AND   hps.location_id     = hl.location_id
   --and hcasa.location_id=hl.location_id
   AND   hcasa.org_id         = hou.organization_id
   --and hp2.party_name LIKE 'Marks%Spencer Plc.%'
   and hps.status='A'
   --and hp2.party_number=212414
   --and  hl.country='US'
--AND hou.name not in ('OBT GLOBAL INC.','ZENSAR THOUGHT DIGITAL LLC','ZENSAR FINLAND')
--AND (length(hl.state)>2 OR hl.state  like '.')
--AND length(hl.state)>2
  --AND hou.name not in ('OBT GLOBAL INC.','ZENSAR THOUGHT DIGITAL LLC','ZENSAR FINLAND')
   --and hp2.party_name='Cancer Research UK Limited'
   --order by 1,4;  --774
 union
select     hp.Party_name,
          'Contact_Person' person_first_name,
          hp.person_name_suffix   person_middle_name            ,
          hp.person_name_suffix   person_last_name              ,
           --hou.name,
           hp.orig_system_reference,
           hp.person_name_suffix               ,
           hp.party_number                     ,
           hp.party_type                       ,
           hp.person_name_suffix contact_point_type,
         
            hp.person_name_suffix contact_point_purpose            ,
           hp.person_name_suffix email_address                    ,
           hp.person_name_suffix telephone_type                   ,
           hp.person_name_suffix owner_table_name                 ,
           acv.per_lang_object_version TIME_ZONE                        ,
           hp.person_name_suffix phone_area_code                  ,
           hp.person_name_suffix phone_country_code               ,
           hp.person_name_suffix phone_number                     ,
           hp.person_name_suffix phone_extension                  ,
           hp.person_name_suffix phone_line_type                  ,
           hp.person_name_suffix telex_number                     ,
           hp.person_name_suffix web_type                         ,
           hp.person_name_suffix url                              ,
           hp.person_name_suffix raw_phone_number                 ,
           hp.person_name_suffix primary_flag                     ,
         
                      hp.party_number subject_party_number,
           hp.orig_system_reference obj_orig_system_ref,
         
         
           'CONTACT_OF' relationship_code,
           'PERSON' subject_type,
           'ORGANIZATION' object_type,
           'CONTACT' relationship_type,
           'F' directional_flag,
           hp.person_name_suffix department_code,
            hp.person_name_suffix department,
            hp.person_name_suffix job_title,
            'A' status,
            'N' decision_maker_flag,
            hca.account_number,
            hcasa.cust_acct_site_id,
           hcasa.orig_system_reference  acct_site_orig_sys_ref,
           'ACCOUNT__SITE_LEVEL' level_of_contact ,
           hl.address1,
           hl.address2,
           hl.address3,
           hl.address4,
           hl.city,
         hl.postal_code,
         hl.state,
         hl.province,
         hl.county         ,
         hl.country,
         hpsv.identifying_address_flag  ,
hou.name,hou.organization_id
from ar.hz_parties hp,
apps.hz_cust_accounts hca,
ar.hz_cust_acct_sites_all hcasa,
apps.ar_contacts_v acv,
apps.hz_party_sites_v hpsv,
apps.hr_operating_units hou,
ar.hz_locations hl
where hp.party_id=hca.party_id  
and hca.cust_account_id= hcasa.cust_account_id
and hcasa.cust_acct_site_id=acv.address_id(+)
and acv.last_name is  null
and hcasa.party_site_id=hpsv.party_site_id
and hpsv.party_id= hp.party_id
and hcasa.org_id= hou.organization_id
and hpsv.location_id=hl.location_id
and hp.status='A'
--and acv.status='A'
and hca.status='A'
and hpsv.status='A'