Introduction:
This Post illustrates the steps required to getting customer Bill to Contact in Oracle EBS R12.
Script to Script for getting customer Bill to Contact in R12 Oracle Apps
FUNCTION xx_get_contact (pn_acc_number IN VARCHAR2,pn_site_use_id Number)
RETURN VARCHAR2
AS
v_contact VARCHAR2 (500);
BEGIN
SELECT DISTINCT SUBSTR (party.person_first_name,
1,
40
)
|| ‘ ‘
|| SUBSTR (party.person_last_name, 1, 50) into v_contact
FROM hz_contact_points cont_point,
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(+) = ‘EMAIL’
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 hcu.site_use_id = pn_site_use_id
AND role_acct.account_number = pn_acc_number
and SITE_USE_CODE=’BILL_TO’;
RETURN v_contact;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
fnd_file.put_line (fnd_file.LOG, ‘check the error..’ || SQLERRM);
END iron_get_contact;
What we expect in the script.
This script helps us to getting customer Bill to Contact in R12 Oracle Apps. A couple of tables which is being used in the scripts are HZ_PARTIES etc
Summary
This Post described the script for getting customer Bill to Contact in Oracle EBS R12.
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
