PLSQL Function To Get All customer Phone Number in Oracle Apps R12

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;