Script to get PHONE NO with area code of the customer

SELECT    LISTAGG( 

               cont_point.phone_area_code

                         || DECODE (cont_point.contact_point_type,

                                    'TLX', cont_point.telex_number,

                                    REGEXP_REPLACE (cont_point.phone_number,

                                                    '[^[:alnum:]'' '']',

                                                    NULL

                                                   )

                                   ),'|') WITHIN GROUP (ORDER BY cont_point.phone_number) into lv_phone_no

                    FROM hz_contact_points cont_point,

                         --hz_contact_points cont_point1,

                         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(+) = 'PHONE'

                     -- AND cont_point1.owner_table_id(+) = rel_party.party_id

                     --AND cont_point1.contact_point_type = 'EMAIL'

                     AND cont_point.phone_line_type = 'GEN'

                     --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 hcasa.cust_acct_site_id = hcu.cust_acct_site_id

                     AND cont_point.phone_number IS NOT NULL

                     --AND cont_point1.EMAIL_ADDRESS IS NOT NULL

                     AND hcu.site_use_id = pn_site_use_id --/*Pass site use id */

                     AND ROWNUM <= 8;

*****Posted By -- Alam (Doyensys)