SELECT
 
 contact.party_id,
 
 contact.PARTY_NUMBER,
 
(CASE WHEN contact.PERSON_NAME IS NULL THEN 'NO DATA' ELSE contact.PERSON_NAME  END) AS PERSON_NAME,
 
REPLACE((CASE WHEN contact.JOB_TITLE IS NULL THEN 'NO DATA' ELSE contact.JOB_TITLE END), ',', ';')AS JOB_TITLE,
 
(CASE WHEN contact.PERSON_PRE_NAME_ADJUNCT IS NULL THEN 'NO DATA' ELSE contact.PERSON_PRE_NAME_ADJUNCT  END) AS PERSON_PRE_NAME_ADJUNCT,
 
(CASE WHEN hz.email_address IS NULL THEN 'NO DATA' ELSE hz.email_address END) AS email_address,
 
(CASE WHEN hz.primary_phone_country_code IS NULL THEN 'NO DATA' ELSE hz.primary_phone_country_code END)AS primary_phone_country_code,
 
(CASE WHEN hz.primary_phone_area_code IS NULL THEN 'NO DATA' ELSE hz.primary_phone_area_code END) AS primary_phone_area_code,
 
(CASE WHEN hz.primary_phone_number IS NULL THEN 'NO DATA' ELSE hz.primary_phone_number END) AS primary_phone_number,
 
(CASE WHEN location.address1 IS NULL THEN 'NO DATA' ELSE location.address1 END) AS address1,
 
(CASE WHEN location.city IS NULL THEN 'NO DATA' ELSE location.city END) AS city,
 
(CASE WHEN location.country IS NULL THEN 'NO DATA' ELSE location.country END) AS country,
 
( CASE
 
           WHEN (SELECT Listagg(svc_service_requests.sr_number, ';')
 
                          within GROUP (ORDER BY svc_service_requests.sr_number)
 
                        AS
 
                        SR_NUMBERS
 
                 FROM   fusion.svc_service_requests
 
                 WHERE  svc_service_requests.PRIMARY_CONTACT_PARTY_ID =
 
                        contact.party_id) IS NULL THEN
 
           'NO DATA'
 
           ELSE (SELECT Listagg(svc_service_requests.sr_number, ';')
 
                          within GROUP (ORDER BY svc_service_requests.sr_number)
 
                        AS
 
                        SR_NUMBERS
 
                 FROM   fusion.svc_service_requests
 
                 WHERE  svc_service_requests.PRIMARY_CONTACT_PARTY_ID =
 
                        contact.party_id)
 
         END )AS SR_NUMBERS,
 
   ( CASE
 
           WHEN (SELECT Listagg(svc_service_requests.title, ';')
 
                          within GROUP (ORDER BY svc_service_requests.sr_number) AS
 
                        SR_NUMBERS
 
                 FROM   fusion.svc_service_requests
 
                 WHERE  svc_service_requests.PRIMARY_CONTACT_PARTY_ID =
 
                        contact.party_id) IS NULL THEN
 
           'NO DATA'
 
           ELSE (SELECT Listagg(svc_service_requests.title, ';')
 
                          within GROUP (ORDER BY svc_service_requests.title) AS
 
                        SR_NUMBERS
 
                 FROM   fusion.svc_service_requests
 
                 WHERE  svc_service_requests.PRIMARY_CONTACT_PARTY_ID =
 
                        contact.party_id)
 
         END )  AS SR_TITLE,
 
       (SELECT Count(*)
 
        FROM   fusion.svc_service_requests
 
        WHERE  svc_service_requests.PRIMARY_CONTACT_PARTY_ID =
 
               contact.party_id)  AS SR_TOTAL      
 
FROM FUSION.HZ_PERSON_PROFILES contact INNER JOIN fusion.hz_party_sites  site ON (contact.party_id =site.party_id )
 
inner join fusion.hz_locations location ON (location.location_id = site.location_id)
 
inner join fusion.hz_parties  hz ON (hz.party_id = contact.party_id)
 
WHERE location.STATUS_FLAG = 'A' AND site.STATUS = 'A'  AND   contact.STATUS = 'A' AND hz.INTERNAL_FLAG = 'N' and contact.PARTY_ID IN (:contactID)