VIEW: APPS.MSD_ON_HAND_DS_V

Source

View Text - Preformatted

SELECT MDS.DEMAND_PLAN_ID, MS.PLAN_ID PLAN_ID, TO_CHAR(NULL) CS_NAME, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK, D_ORG.LEVEL_ID ORG_LEVEL_ID, D_ITM.LEVEL_PK PRD_LEVEL_VALUE_PK, D_ITM.LEVEL_ID PRD_LEVEL_ID, 9 TIME_LEVEL_ID, MS.NEW_ORDER_QUANTITY, TRUNC(MS.NEW_SCHEDULE_DATE) , MS.CREATION_DATE, MS.CREATED_BY, MS.LAST_UPDATE_DATE, MS.LAST_UPDATED_BY, MS.LAST_UPDATE_LOGIN, 1, 1, 'I', TO_NUMBER(NULL), TO_NUMBER(NULL) FROM MSC_SUPPLIES MS, MSD_DP_SESSION MDS, MSC_SYSTEM_ITEMS MSI, MSD_LEVEL_VALUES_DS D_ITM, MSD_LEVEL_VALUES_DS D_ORG WHERE MS.PLAN_ID = -1 AND MS.PLAN_ID=MSI.PLAN_ID AND MS.INVENTORY_ITEM_ID =MSI.INVENTORY_ITEM_ID AND MS.ORGANIZATION_ID=MSI.ORGANIZATION_ID AND MS.SR_INSTANCE_ID=MSI.SR_INSTANCE_ID AND MS.ORDER_TYPE IN (8,18) AND NVL(MS.NEW_ORDER_QUANTITY,0)<>0 AND D_ORG.LEVEL_ID=7 AND D_ORG.INSTANCE=TO_CHAR(MS.SR_INSTANCE_ID) AND D_ORG.SR_LEVEL_PK=TO_CHAR(MS.ORGANIZATION_ID) AND D_ORG.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID AND D_ITM.LEVEL_ID=1 AND D_ITM.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID) AND D_ITM.SR_LEVEL_PK=TO_CHAR(MSI.SR_INVENTORY_ITEM_ID) AND D_ITM.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID UNION ALL SELECT MDS.DEMAND_PLAN_ID, 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, 9 TIME_LEVEL_ID, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY, TO_DATE(MCD.ATTRIBUTE_43,'YYYY/MM/DD'), 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_DS MLV_ORG, MSD_LEVEL_VALUES_DS MLV_PRD, MSD_DP_SESSION MDS WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID FROM MSD_CS_DEFINITIONS WHERE NAME = 'MSD_ON_HAND') 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 MLV_ORG.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID 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 MLV_PRD.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID

View Text - HTML Formatted

SELECT MDS.DEMAND_PLAN_ID
, MS.PLAN_ID PLAN_ID
, TO_CHAR(NULL) CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, D_ORG.LEVEL_ID ORG_LEVEL_ID
, D_ITM.LEVEL_PK PRD_LEVEL_VALUE_PK
, D_ITM.LEVEL_ID PRD_LEVEL_ID
, 9 TIME_LEVEL_ID
, MS.NEW_ORDER_QUANTITY
, TRUNC(MS.NEW_SCHEDULE_DATE)
, MS.CREATION_DATE
, MS.CREATED_BY
, MS.LAST_UPDATE_DATE
, MS.LAST_UPDATED_BY
, MS.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIES MS
, MSD_DP_SESSION MDS
, MSC_SYSTEM_ITEMS MSI
, MSD_LEVEL_VALUES_DS D_ITM
, MSD_LEVEL_VALUES_DS D_ORG
WHERE MS.PLAN_ID = -1
AND MS.PLAN_ID=MSI.PLAN_ID
AND MS.INVENTORY_ITEM_ID =MSI.INVENTORY_ITEM_ID
AND MS.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MS.SR_INSTANCE_ID=MSI.SR_INSTANCE_ID
AND MS.ORDER_TYPE IN (8
, 18)
AND NVL(MS.NEW_ORDER_QUANTITY
, 0)<>0
AND D_ORG.LEVEL_ID=7
AND D_ORG.INSTANCE=TO_CHAR(MS.SR_INSTANCE_ID)
AND D_ORG.SR_LEVEL_PK=TO_CHAR(MS.ORGANIZATION_ID)
AND D_ORG.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID
AND D_ITM.LEVEL_ID=1
AND D_ITM.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.SR_LEVEL_PK=TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID UNION ALL SELECT MDS.DEMAND_PLAN_ID
, 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
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD')
, 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_DS MLV_ORG
, MSD_LEVEL_VALUES_DS MLV_PRD
, MSD_DP_SESSION MDS
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_ON_HAND')
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 MLV_ORG.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID
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 MLV_PRD.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID