Skip to content
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';

        
        
        

Leave a Reply

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