Introduction
This script will used to find the customer contact points.
SELECT DECODE(cont_point.contact_point_purpose,’DUNNING’,cont_point.ORIG_SYSTEM_REFERENCE||’-DUN’,’COLLECTIONS’,cont_point. ORIG_SYSTEM_REFERENCE||’-COLL’,’BUSINESS’,cont_point.ORIG_SYSTEM_REFERENCE||’-BUSS’,cont_point.ORIG_SYSTEM_REFERENCE) Cnct_Point_Orig_Sys_Ref,
hpsub.ORIG_SYSTEM_REFERENCE Party_Orig_Sys_Ref,
hpobj.ORIG_SYSTEM_REFERENCE Party_Site_Orig_Sys_Ref,
cont_point.Primary_flag,
‘I’ Insert_Update_Indicator,
cont_point.contact_point_type,
cont_point.contact_point_purpose,
DECODE(cont_point.email_address,’x’,NULL,cont_point.email_address)email_address,
cont_point.email_format,
cont_point.phone_area_code,
cont_point.phone_country_code,
cont_point.phone_extension,
cont_point.phone_line_type,
cont_point.phone_number raw_phone_number,
cont_point.url,
cont_point.phone_calling_calendar,
cont_point.creation_date from_date,
NULL to_date1,
cont_point.attribute_category User_Defined_Context_Prompt ,
cont_point.attribute1 DFF_Segment1 ,
cont_point.attribute2 DFF_Segment2 ,
cont_point.attribute3 DFF_Segment3 ,
cont_point.attribute4 DFF_Segment4 ,
cont_point.attribute5 DFF_Segment5 ,
cont_point.attribute6 DFF_Segment6 ,
cont_point.attribute7 DFF_Segment7 ,
cont_point.attribute8 DFF_Segment8 ,
cont_point.attribute9 DFF_Segment9 ,
cont_point.attribute10 DFF_Segment10 ,
cont_point.attribute11 DFF_Segment11 ,
cont_point.attribute12 DFF_Segment12 ,
cont_point.attribute13 DFF_Segment13 ,
cont_point.attribute14 DFF_Segment14 ,
cont_point.attribute15 DFF_Segment15 ,
cont_point.attribute16 DFF_Segment16 ,
cont_point.attribute17 DFF_Segment17 ,
cont_point.attribute18 DFF_Segment18 ,
cont_point.attribute19 DFF_Segment19 ,
cont_point.attribute20 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 ,
hoc.ORIG_SYSTEM_REFERENCE||’-‘||’REL’ Rel_Source_System_Ref
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_acct_sites_all hcasa,
apps.hz_cust_accounts hca,
apps.hz_contact_points cont_point,
apps.hz_cust_site_uses_all hcsua
WHERE
1=1
AND hpsub.status = ‘A’
and hcasa.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 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 cont_point.owner_table_id = hr.party_id
AND hpobj.party_id = hca.party_id
AND hcar.cust_account_id =hcasa.cust_account_id(+)
AND hpobj.party_id =p_party_id
AND NVL(hcsua.SITE_USE_CODE,’~’) IN(‘BILL_TO’,’SHIP_TO’,’~’)
and hcar.cust_acct_site_id =hcsua.cust_acct_site_id(+)
and hcar.cust_acct_site_id =hcasa.cust_acct_site_id(+);
