VIEW: APPS.MSD_NO_CUTOFF_BURNOFF_CAL_CS_V

Source

View Text - Preformatted

SELECT MIS.PLAN_ID, PLN.COMPILE_DESIGNATOR, ORG.LEVEL_PK , 7, PRD.LEVEL_PK , 1, GEO.LEVEL_PK , 50, 9 TIME_LEVEL_ID, SUM(MAD.CUTOFF_DAYS), NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, 'I', TO_NUMBER(NULL), TO_NUMBER(NULL) FROM MSC_PLANS PLN, MSC_ITEM_SUPPLIERS MIS, MSC_TRADING_PARTNERS MTP, MSC_SYSTEM_ITEMS MSI, MSC_ASL_AUTH_DETAILS MAD, MSD_LEVEL_VALUES PRD, MSD_LEVEL_VALUES ORG, MSD_LEVEL_VALUES GEO WHERE MIS.PLAN_ID = PLN.PLAN_ID AND PLN.PLAN_ID <> -1 AND MSI.PLAN_ID = MIS.PLAN_ID AND MSI.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID AND MSI.ORGANIZATION_ID = MIS.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID AND MAD.INCLUDE_LIABILITY_AGREEMENT = 1 AND MAD.SUPPLIER_ID = MIS.SUPPLIER_ID AND MAD.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID AND MAD.ORGANIZATION_ID = MIS.ORGANIZATION_ID AND MAD.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID AND MAD.SUPPLIER_SITE_ID = MIS.SUPPLIER_SITE_ID AND MTP.PARTNER_ID=MIS.SUPPLIER_ID AND MTP.PARTNER_TYPE=1 AND PRD.LEVEL_ID = 1 AND PRD.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID) AND PRD.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID) AND ORG.LEVEL_ID = 7 AND ORG.SR_LEVEL_PK = TO_CHAR(MIS.ORGANIZATION_ID) AND ORG.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID) AND GEO.LEVEL_ID = 50 AND GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID) AND GEO.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID) GROUP BY MIS.PLAN_ID, PLN.COMPILE_DESIGNATOR, ORG.LEVEL_PK , PRD.LEVEL_PK ,GEO.LEVEL_PK UNION ALL SELECT /* LEGACY DATA */ TO_NUMBER(NULL) PLAN_ID, MCD.CS_NAME CS_NAME, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK, 7 ORG_LEVEL_ID, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK, 1 PRD_LEVEL_ID, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK, 50 GEO_LEVEL_ID, 9 TIME_LEVEL_ID, TO_NUMBER(MCD.ATTRIBUTE_41), NULL, MCD.CREATION_DATE CREATION_DATE, MCD.CREATED_BY CREATED_BY, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE, MCD.LAST_UPDATED_BY LAST_UPDATED_BY, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, MCD.LAST_REFRESH_NUM LAST_REFRESH_NUM, MCD.CREATED_BY_REFRESH_NUM CREATED_BY_REFRESH_NUM, MCD.ACTION_CODE ACTION_CODE, TO_NUMBER(NULL) PRD_PARENT_LEVEL_ID, TO_NUMBER(NULL) PRD_PARENT_LEVEL_VALUE_PK FROM MSD_CS_DATA MCD, MSD_LEVEL_VALUES MLV_ORG, MSD_LEVEL_VALUES MLV_PRD, MSD_LEVEL_VALUES MLV_GEO WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID FROM MSD_CS_DEFINITIONS WHERE NAME = 'MSD_NUM_CUTOFF_BURNOFF_CAL') AND MCD.ATTRIBUTE_10 = 7 AND MLV_ORG.LEVEL_ID = 7 AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE AND MCD.ATTRIBUTE_2 = 1 AND MLV_PRD.LEVEL_ID = 1 AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE AND MCD.ATTRIBUTE_6 = 50 AND MLV_GEO.LEVEL_ID = 50 AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE

View Text - HTML Formatted

SELECT MIS.PLAN_ID
, PLN.COMPILE_DESIGNATOR
, ORG.LEVEL_PK
, 7
, PRD.LEVEL_PK
, 1
, GEO.LEVEL_PK
, 50
, 9 TIME_LEVEL_ID
, SUM(MAD.CUTOFF_DAYS)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_PLANS PLN
, MSC_ITEM_SUPPLIERS MIS
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_ASL_AUTH_DETAILS MAD
, MSD_LEVEL_VALUES PRD
, MSD_LEVEL_VALUES ORG
, MSD_LEVEL_VALUES GEO
WHERE MIS.PLAN_ID = PLN.PLAN_ID
AND PLN.PLAN_ID <> -1
AND MSI.PLAN_ID = MIS.PLAN_ID
AND MSI.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND MAD.INCLUDE_LIABILITY_AGREEMENT = 1
AND MAD.SUPPLIER_ID = MIS.SUPPLIER_ID
AND MAD.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID
AND MAD.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND MAD.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND MAD.SUPPLIER_SITE_ID = MIS.SUPPLIER_SITE_ID
AND MTP.PARTNER_ID=MIS.SUPPLIER_ID
AND MTP.PARTNER_TYPE=1
AND PRD.LEVEL_ID = 1
AND PRD.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND PRD.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID)
AND ORG.LEVEL_ID = 7
AND ORG.SR_LEVEL_PK = TO_CHAR(MIS.ORGANIZATION_ID)
AND ORG.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID)
AND GEO.LEVEL_ID = 50
AND GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID)
AND GEO.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID) GROUP BY MIS.PLAN_ID
, PLN.COMPILE_DESIGNATOR
, ORG.LEVEL_PK
, PRD.LEVEL_PK
, GEO.LEVEL_PK UNION ALL SELECT /* LEGACY DATA */ TO_NUMBER(NULL) PLAN_ID
, MCD.CS_NAME CS_NAME
, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, 7 ORG_LEVEL_ID
, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK
, 1 PRD_LEVEL_ID
, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, 50 GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41)
, NULL
, MCD.CREATION_DATE CREATION_DATE
, MCD.CREATED_BY CREATED_BY
, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MCD.LAST_UPDATED_BY LAST_UPDATED_BY
, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MCD.LAST_REFRESH_NUM LAST_REFRESH_NUM
, MCD.CREATED_BY_REFRESH_NUM CREATED_BY_REFRESH_NUM
, MCD.ACTION_CODE ACTION_CODE
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_ID
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_VALUE_PK
FROM MSD_CS_DATA MCD
, MSD_LEVEL_VALUES MLV_ORG
, MSD_LEVEL_VALUES MLV_PRD
, MSD_LEVEL_VALUES MLV_GEO
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_NUM_CUTOFF_BURNOFF_CAL')
AND MCD.ATTRIBUTE_10 = 7
AND MLV_ORG.LEVEL_ID = 7
AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE
AND MCD.ATTRIBUTE_2 = 1
AND MLV_PRD.LEVEL_ID = 1
AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE
AND MCD.ATTRIBUTE_6 = 50
AND MLV_GEO.LEVEL_ID = 50
AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE