Chuck Oracle Engineer

客戶主檔串到聯絡人再串到聯絡人角色

研究了很久都找不到來源，問了SR之後他們也給錯方向

但SR內容有參考到一篇

Note: This is INTERNAL ONLY research. No action should be taken by the customer on this information. This is research only, and may NOT be applicable to your specific situation.

Searched on: MOS
TCA APIs Usage and Customer Account Creation Sample Code, Queries, and Screens ( Doc ID 837568.1 )

分析了一下就出結果了

SELECT hp_per.party_name contact_name,
       fv.meaning contact_point_type,
       phone_country_code || '-' || phone_area_code || '-' || hcp_ph.phone_number phone_number,
       hcp_ph.email_address "E-Mail",
       rol.cust_acct_site_id,
       rol.cust_account_id,
       NULL site_use_code,
       RESPONSIBILITY_TYPE,
       '---------', hp_rel.*
  FROM hz_cust_account_roles rol,
       hz_parties hp_rel,
       hz_relationships rel,
       hz_parties hp_per,
       hz_contact_points hcp_ph,
       fnd_lookup_values fv,
       hz_cust_account_roles hcar,
       hz_role_responsibility hrr
 WHERE rol.party_id = hp_rel.party_id
   AND hp_rel.party_id = rel.party_id
   AND rel.object_type = 'PERSON'
   AND rel.relationship_code = 'CONTACT'
   AND rel.object_id = hp_per.party_id
   AND hcar.cust_account_role_id = hrr.CUST_ACCOUNT_ROLE_ID 
   AND  hp_rel.party_id = hcar.party_id
   AND rol.cust_account_id = hcar.cust_account_id 
   AND rol.cust_account_id = 2562905 --!!
   AND rol.cust_acct_site_id IS NULL --!!
   AND hcar.cust_acct_site_id IS NULL --!!
   AND rel.status = 'A'
   AND rol.status = 'A'
   AND hcp_ph.phone_line_type = fv.lookup_code(+)
   AND fv.lookup_type(+) = 'PHONE_LINE_TYPE'
   AND fv.LANGUAGE(+) = USERENV('LANG')
   --AND rol.cust_acct_site_id = 37024
   --AND rol.cust_account_id = 502839
   AND hp_rel.party_id = hcp_ph.owner_table_id
   AND hcp_ph.owner_table_name = 'HZ_PARTIES'