Skip to content

BI Publisher – SQL For Collecting info about an Account

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'

Leave a Reply

Your email address will not be published. Required fields are marked *