Introduction
This script will used to identify the customer contact roles
SELECT DISTINCT Batch_Identifier,
NULL interface_row_id,
NULL import_status_code,
‘I’ Insert_Update_Indicator,
hcar.ORIG_SYSTEM_REFERENCE Contact_Role_Orig_Sys_Ref,
hoc.ORIG_SYSTEM_REFERENCE||’-‘||’REL’ Rel_Src_Sys_Ref,
hcar.role_type,
NULL Role_Level,
NULL Primary_Role,
NULL Role_Type_Primary_Contact,
hcar.attribute_category User_Defined_Context_Prompt ,
hcar.attribute1 DFF_Segment1 ,
hcar.attribute2 DFF_Segment2 ,
hcar.attribute3 DFF_Segment3 ,
hcar.attribute4 DFF_Segment4 ,
hcar.attribute5 DFF_Segment5 ,
hcar.attribute6 DFF_Segment6 ,
hcar.attribute7 DFF_Segment7 ,
hcar.attribute8 DFF_Segment8 ,
hcar.attribute9 DFF_Segment9 ,
hcar.attribute10 DFF_Segment10 ,
hcar.attribute11 DFF_Segment11 ,
hcar.attribute12 DFF_Segment12 ,
hcar.attribute13 DFF_Segment13 ,
hcar.attribute14 DFF_Segment14 ,
hcar.attribute15 DFF_Segment15 ,
NULL DFF_Segment16 ,
NULL DFF_Segment17 ,
NULL DFF_Segment18 ,
NULL DFF_Segment19 ,
NULL DFF_Segment20 ,
NULL DFF_Segment21 ,
NULL DFF_Segment22 ,
NULL DFF_Segment23 ,
NULL DFF_Segment24 ,
NULL DFF_Segment25 ,
NULL DFF_Segment26 ,
NULL DFF_Segment27 ,
NULL DFF_Segment28 ,
NULL DFF_Segment29 ,
NULL DFF_Segment30 ,
NULL dff_num_Segment1 ,
NULL dff_num_Segment2 ,
NULL dff_num_Segment3 ,
NULL dff_num_Segment4 ,
NULL dff_num_Segment5 ,
NULL dff_num_Segment6 ,
NULL dff_num_Segment7 ,
NULL dff_num_Segment8 ,
NULL dff_num_Segment9 ,
NULL dff_num_Segment10 ,
NULL dff_num_Segment11 ,
NULL dff_num_Segment12 ,
NULL dff_date_Segment1 ,
NULL dff_date_Segment2 ,
NULL dff_date_Segment3 ,
NULL dff_date_Segment4 ,
NULL dff_date_Segment5 ,
NULL dff_date_Segment6 ,
NULL dff_date_Segment7 ,
NULL dff_date_Segment8 ,
NULL dff_date_Segment9 ,
NULL dff_date_Segment10 ,
NULL dff_date_Segment11 ,
NULL dff_date_Segment12
FROM
apps.hz_cust_account_roles hcar,
apps.hz_parties hpsub,
apps.hz_parties hprel,
apps.hz_parties hpobj,
apps.hz_org_contacts hoc,
apps.hz_relationships hr,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_site_uses_all hcsua
WHERE
1=1
AND hpsub.status = ‘A’
AND hcar.STATUS = ‘A’
AND hpobj.status = ‘A’
AND hprel.status = ‘A’
AND hr.status = ‘A’
AND hcar.status = ‘A’
AND hcsua.org_id=p_org_id
AND hcar.role_type = ‘CONTACT’
AND hcar.party_id = hr.party_id
AND hcar.cust_account_id=hcasa.cust_account_id(+)
and hcar.cust_acct_site_id=hcasa.cust_acct_site_id(+)
AND hcar.cust_acct_site_id=hcsua.cust_acct_site_id(+)
AND NVL(hcsua.SITE_USE_CODE,’~’) IN(‘BILL_TO’,’SHIP_TO’,’~’)
— AND hca.party_id=hpsub.party_id
and hca.cust_account_id=hcar.cust_account_id
AND hr.party_id = hprel.party_id
AND hr.subject_id = hpsub.party_id
AND hr.object_id = hpobj.party_id
AND hoc.party_relationship_id = hr.relationship_id
–AND hr.directional_flag = ‘F’
AND hpobj.party_id =p_party_id;
