BI Publicher – SQL query to retrieve all install base assets filtered by Account Party and Service request BU Master Service Catalog

        
            SELECT * FROM cse_assets_vl WHERE ITEM_ID  IN (SELECT DISTINCT INVENTORY_ITEM_ID FROM QSC_PROD_GROUP_ITEMS WHERE PROD_GROUP_ID IN (
                (SELECT DISTINCT
                PGR.PROD_GROUP_ID
                FROM QSC_PROD_GRP_REL_B GrREL INNER JOIN QSC_PROD_GROUPS_TL PGR ON (GrREL.REL_PROD_GROUP_ID = PGR.PROD_GROUP_ID)
                WHERE PGR.LANGUAGE = 'US' AND GrREL.PROD_GROUP_ID IN (SELECT DISTINCT
                PGR.PROD_GROUP_ID
                FROM QSC_PROD_GRP_REL_B GrREL INNER JOIN QSC_PROD_GROUPS_TL PGR ON (GrREL.REL_PROD_GROUP_ID = PGR.PROD_GROUP_ID)
                WHERE PGR.LANGUAGE = 'US' AND GrREL.PROD_GROUP_ID = :ProductGroupId) )
                UNION
                (SELECT DISTINCT
                PGR.PROD_GROUP_ID
                FROM QSC_PROD_GRP_REL_B GrREL INNER JOIN QSC_PROD_GROUPS_TL PGR ON (GrREL.REL_PROD_GROUP_ID = PGR.PROD_GROUP_ID)
                WHERE PGR.LANGUAGE = 'US' AND GrREL.PROD_GROUP_ID IN 
                (
                SELECT DISTINCT
                PGR.PROD_GROUP_ID
                FROM QSC_PROD_GRP_REL_B GrREL INNER JOIN QSC_PROD_GROUPS_TL PGR ON (GrREL.REL_PROD_GROUP_ID = PGR.PROD_GROUP_ID)
                WHERE PGR.LANGUAGE = 'US' AND GrREL.PROD_GROUP_ID IN (SELECT DISTINCT
                PGR.PROD_GROUP_ID
                FROM QSC_PROD_GRP_REL_B GrREL INNER JOIN QSC_PROD_GROUPS_TL PGR ON (GrREL.REL_PROD_GROUP_ID = PGR.PROD_GROUP_ID)
                WHERE PGR.LANGUAGE = 'US' AND GrREL.PROD_GROUP_ID = :ProductGroupId))) 
                ) )  AND CUSTOMER_ID = :AccountPartyID
Leave a Reply Cancel reply
