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) 
  • September 25, 2018 | 21 views
  • Comments