Tuesday, 30 January 2018

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;

 

0 comments:

Post a Comment