Introduction
This Post illustrates the steps required to get PHONE NO with area code of the customer in Oracle EBS R12.
Script to get PHONE NO with area code of the customer
SELECT LISTAGG(
cont_point.phone_area_code
|| DECODE (cont_point.contact_point_type,
‘TLX’, cont_point.telex_number,
REGEXP_REPLACE (cont_point.phone_number,
‘[^[:alnum:]” ”]’,
NULL
)
),’|’) WITHIN GROUP (ORDER BY cont_point.phone_number) into lv_phone_no
FROM hz_contact_points cont_point,
–hz_contact_points cont_point1,
hz_cust_account_roles acct_role,
hz_parties party,
hz_parties rel_party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_cust_accounts role_acct,
hz_contact_restrictions cont_res,
hz_person_language per_lang,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcu
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = ‘CONTACT’
AND org_cont.party_relationship_id = rel.relationship_id
AND rel.subject_id = party.party_id
AND rel_party.party_id = rel.party_id
AND cont_point.owner_table_id(+) = rel_party.party_id
AND cont_point.contact_point_type(+) = ‘PHONE’
— AND cont_point1.owner_table_id(+) = rel_party.party_id
–AND cont_point1.contact_point_type = ‘EMAIL’
AND cont_point.phone_line_type = ‘GEN’
–and cont_point.primary_flag(+) = ‘Y’
AND acct_role.cust_account_id = role_acct.cust_account_id
AND role_acct.party_id = rel.object_id
AND party.party_id = per_lang.party_id(+)
AND per_lang.native_language(+) = ‘Y’
AND party.party_id = cont_res.subject_id(+)
AND cont_res.subject_table(+) = ‘HZ_PARTIES’
AND role_acct.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
AND hcasa.cust_acct_site_id = hcu.cust_acct_site_id
AND cont_point.phone_number IS NOT NULL
–AND cont_point1.EMAIL_ADDRESS IS NOT NULL
AND hcu.site_use_id = pn_site_use_id –/*Pass site use id */
AND ROWNUM <= 8;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
