Skip to content

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

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