SELECT hz_organization_profiles.party_id,
            hz_party_sites.location_id,
            hz_organization_profiles.party_number,
            hz_organization_profiles.organization_name,
            hz_locations.address1,
            hz_locations.city,
            hz_locations.country,
            hz_party_sites.END_DATE_ACTIVE,
            hz_party_sites.STATUS,
            ( CASE
            WHEN hz_organization_profiles.preferred_contact_person_id IS NULL
            THEN 0
            ELSE hz_organization_profiles.preferred_contact_person_id
            END ) AS
            ACCOUNT_CONTACT,
            ( CASE
            WHEN (SELECT hz_parties.party_name
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id) IS
            NULL
            THEN 'NO DATA'
            ELSE (SELECT hz_parties.party_name
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id)
            END ) AS Contact,
            (CASE WHEN (SELECT hz_parties.primary_phone_country_code
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id) IS NULL THEN 'NO_DATA' ELSE (SELECT hz_parties.primary_phone_country_code
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id) END )
            AS
            Contact_Phone_Country,
            ( CASE
            WHEN(SELECT hz_parties.primary_phone_area_code
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id) IS
            NULL
            THEN
            'NO DATA'
            ELSE (SELECT hz_parties.primary_phone_area_code
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id)
            END ) AS
            Contact_Phone_AreaCode,
            ( CASE
            WHEN (SELECT hz_parties.primary_phone_number
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id) IS
            NULL
            THEN 'NO DATA'
            ELSE (SELECT hz_parties.primary_phone_number
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id)
            END ) AS
            Contact_Phone,
            ( CASE
            WHEN (SELECT hz_parties.email_address
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id) IS
            NULL
            THEN 'NO DATA'
            ELSE (SELECT hz_parties.email_address
            FROM fusion.hz_parties
            WHERE hz_parties.party_id =
            hz_organization_profiles.preferred_contact_person_id)
            END ) AS
            Contact_Email,
            (SELECT Count(*)
            FROM fusion.moo_opty
            WHERE moo_opty.cust_party_id = hz_organization_profiles.party_id
            AND moo_opty.status_cd = 'WON') AS OPTY_WON
            ,
            (SELECT Count(*)
            FROM fusion.moo_opty
            WHERE moo_opty.cust_party_id = hz_organization_profiles.party_id
            AND moo_opty.status_cd = 'LOST') AS
            OPTY_LOST,
            (SELECT Count(*)
            FROM fusion.moo_opty
            WHERE moo_opty.cust_party_id = hz_organization_profiles.party_id
            AND moo_opty.status_cd = 'OPEN') AS
            OPTY_OPEN,
            (SELECT Count(*)
            FROM fusion.moo_opty
            WHERE moo_opty.cust_party_id = hz_organization_profiles.party_id
            AND moo_opty.status_cd = 'NO_SALE') AS
            OPTY_NO_SALE,
            (SELECT Count(*)
            FROM fusion.svc_service_requests
            WHERE svc_service_requests.account_party_id =
            hz_organization_profiles.party_id) AS SR_TOTAL
            ,
            ( 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.account_party_id =
            hz_organization_profiles.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.account_party_id =
            hz_organization_profiles.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.account_party_id =
            hz_organization_profiles.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.account_party_id =
            hz_organization_profiles.party_id)
            END ) AS SR_TITLE
            FROM fusion.hz_party_sites
            inner join fusion.hz_organization_profiles
            ON ( hz_party_sites.party_id =
            hz_organization_profiles.party_id )
            inner join fusion.hz_locations
            ON ( hz_locations.location_id = hz_party_sites.location_id )
            inner join fusion.hz_parties on (hz_parties.party_id = hz_organization_profiles.party_id)
            WHERE hz_party_sites.party_id = :PARTY_ID
            AND hz_party_sites.overall_primary_flag = 'Y' AND hz_parties.PARTY_TYPE = 'ORGANIZATION'