BI Publisher – SQL For Account with Details about Opty and SRs
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, (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, (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 ) AS SR_NUMBERS, (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 ) 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) WHERE HZ_PARTY_SITES.PARTY_ID = 100000001941254 AND HZ_PARTY_SITES.OVERALL_PRIMARY_FLAG = 'Y';