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;

 

8 comments:

Anonymous said...

Nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training

Anonymous said...

Good Blog Thanks For Sharing this informative airtrl/
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Rifath said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
¬Oracle Fusion HCM Online Training

Anonymous said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion Financials Online Training

shaik shah said...

Thanks for sharing this informative article ,It would be helpful to all
Oracle Fusion HCM Online Training

shaik shah said...
This comment has been removed by the author.
Rainbow Training Institute said...

Very interesting blog I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

Big Data and Hadoop Online Training
Big Data Hadoop Training
Hyderabad

Rainbow Training Institute said...

Thank you for sharing such a nice and interesting blog.

Spark and Scala Online Training
Spark Scala Training
Hyderabad

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *