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;
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;
No comments:
Post a Comment