Introduction
This Post illustrates the steps required to get customers bill to email address in Oracle EBS R12
Script to customers bill to email address in R12 Oracle Apps
FUNCTION xx_cust_contact_email (p_acc_id IN NUMBER,p_cust_account_site_id Number )
RETURN VARCHAR2
IS
v_contact_email VARCHAR2 (1000);
BEGIN
SELECT DECODE (SUBSTR (cont_point.email_address, -1, 1),
‘x’, SUBSTR (cont_point.email_address,
1,
INSTR (cont_point.email_address,
SUBSTR (cont_point.email_address,
-1,
1
)
)
– 1
),
cont_point.email_address
) email_addressinto
INTO v_contact_email
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 role_acct.cust_account_id = p_acc_id
AND hcasa.cust_acct_site_id =p_cust_account_site_id
AND hcasa.cust_acct_site_id = hcu.cust_acct_site_id
and SITE_USE_CODE=’BILL_TO’;
RETURN v_contact_email;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
Summary
This Post described the script for customers bill to email address 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
