PLSQL Function To Get All customer Phone Number in Oracle Apps R12
CREATE OR REPLACE FUNCTION APPS.CUSTOMER_ALL_PHONES(c_ID NUMBER)
RETURN VARCHAR2
IS
co_owner varchar2(999);
abc varchar2(100);
cursor c1 is SELECT '+'||ltrim(PHONE_COUNTRY_CODE||'-'||PHONE_AREA_CODE||'-'||PHONE_NUMBER,'-') PHONE_NUMBER_1
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
and status = 'A'
and CONTACT_POINT_TYPE='PHONE'
AND owner_table_id IN (SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = c_id)
UNION
SELECT '+'||ltrim(PHONE_COUNTRY_CODE||'-'||PHONE_AREA_CODE||'-'||PHONE_NUMBER,'-') PHONE_NUMBER_2
FROM hz_contact_points
WHERE CONTACT_POINT_TYPE='PHONE'
AND owner_table_name = 'HZ_PARTY_SITES'
and status = 'A'
AND owner_table_id IN (SELECT party_site_id
FROM hz_cust_acct_sites_all
WHERE cust_account_id = c_id)
UNION
SELECT '+'||ltrim(PHONE_COUNTRY_CODE||'-'||PHONE_AREA_CODE||'-'||PHONE_NUMBER,'-') PHONE_NUMBER_3
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
and status = 'A'
and CONTACT_POINT_TYPE='PHONE'
AND owner_table_id IN (
SELECT party_id
FROM hz_cust_account_roles
WHERE role_type = 'CONTACT'
AND cust_account_id = c_id);
begin
for i in c1 loop
select i.RAW_PHONE_NUMBER into abc from dual;
co_owner := co_owner||', '||abc;
abc := null;
end loop;
return(co_owner);
end;
