VIEW: APPS.MSD_SUPPLY_COMMIT_DS_V

Source

View Text - Preformatted

SELECT MDS.DEMAND_PLAN_ID, mis.plan_id, PLN.COMPILE_DESIGNATOR, 7, org.level_pk , 1, PRD.LEVEL_PK , 50, geo.level_pk , 9 tim_level_id, msc.capacity, to_number(null), mt.day, NULL, NULL, NULL, NULL, NULL, 1, 1, 'I', to_number(null), to_number(null), mis.ASL_LIABILITY_AGREEMENT_BASIS from MSC_PLANS PLN, MSC_SUPPLIER_CAPACITIES MSC, MSD_DP_SESSION MDS, msd_org_calendars moc, MSD_TIME MT, MSC_SYSTEM_ITEMS MSI, msc_item_suppliers mis, MSC_TRADING_PARTNERS MTP, MSD_LEVEL_VALUES_DS PRD, MSD_LEVEL_VALUES_DS ORG, MSD_LEVEL_VALUES_DS GEO WHERE MIS.PLAN_ID = PLN.PLAN_ID AND msc.plan_id = pln.plan_id and MSC.PLAN_ID=MSI.PLAN_ID AND pln.plan_id<>-1 and MSC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSC.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSC.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID AND MSC.SUPPLIER_ID = MIS.SUPPLIER_ID AND MSC.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID AND MSC.ORGANIZATION_ID = MIS.ORGANIZATION_ID AND MSC.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID AND MSC.SUPPLIER_SITE_ID = MIS.SUPPLIER_SITE_ID AND MOC.INSTANCE = Mis.SR_INSTANCE_ID AND MOC.SR_ORG_PK = mis.ORGANIZATION_ID AND MT.CALENDAR_CODE=MOC.CALENDAR_CODE AND MT.CALENDAR_TYPE=MOC.CALENDAR_TYPE AND MT.DAY BETWEEN msc.from_date and msc.to_date and mis.INCLUDE_LIABILITY_AGREEMENT = 1 and MTP.PARTNER_ID=MIS.SUPPLIER_ID AND MTP.PARTNER_TYPE=1 AND PRD.LEVEL_ID = 1 AND prd.instance=to_char(msi.sr_instance_id) and PRD.SR_LEVEL_PK = to_char(MSI.SR_INVENTORY_ITEM_ID) AND PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID AND ORG.LEVEL_ID = 7 AND org.instance=to_char(mis.sr_instance_id) and ORG.SR_LEVEL_PK = TO_CHAR(MIS.ORGANIZATION_ID) AND ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID AND GEO.LEVEL_ID = 50 AND geo.instance=to_char(mis.sr_instance_id) and GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID) AND GEO.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, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK, 50 GEO_LEVEL_ID, 9 TIME_LEVEL_ID, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY, to_number(null), 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, to_number(null) FROM MSD_CS_DATA MCD, MSD_LEVEL_VALUES_DS MLV_ORG, MSD_LEVEL_VALUES_DS MLV_PRD, MSD_LEVEL_VALUES_DS MLV_GEO, MSD_DP_SESSION MDS WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID FROM MSD_CS_DEFINITIONS WHERE NAME = 'MSD_SUPPLY_COMMIT') 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 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 AND MLV_GEO.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID

View Text - HTML Formatted

SELECT MDS.DEMAND_PLAN_ID
, MIS.PLAN_ID
, PLN.COMPILE_DESIGNATOR
, 7
, ORG.LEVEL_PK
, 1
, PRD.LEVEL_PK
, 50
, GEO.LEVEL_PK
, 9 TIM_LEVEL_ID
, MSC.CAPACITY
, TO_NUMBER(NULL)
, MT.DAY
, NULL
, NULL
, NULL
, NULL
, NULL
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MIS.ASL_LIABILITY_AGREEMENT_BASIS
FROM MSC_PLANS PLN
, MSC_SUPPLIER_CAPACITIES MSC
, MSD_DP_SESSION MDS
, MSD_ORG_CALENDARS MOC
, MSD_TIME MT
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_SUPPLIERS MIS
, MSC_TRADING_PARTNERS MTP
, MSD_LEVEL_VALUES_DS PRD
, MSD_LEVEL_VALUES_DS ORG
, MSD_LEVEL_VALUES_DS GEO
WHERE MIS.PLAN_ID = PLN.PLAN_ID
AND MSC.PLAN_ID = PLN.PLAN_ID
AND MSC.PLAN_ID=MSI.PLAN_ID
AND PLN.PLAN_ID<>-1
AND MSC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSC.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSC.SUPPLIER_ID = MIS.SUPPLIER_ID
AND MSC.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID
AND MSC.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND MSC.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND MSC.SUPPLIER_SITE_ID = MIS.SUPPLIER_SITE_ID
AND MOC.INSTANCE = MIS.SR_INSTANCE_ID
AND MOC.SR_ORG_PK = MIS.ORGANIZATION_ID
AND MT.CALENDAR_CODE=MOC.CALENDAR_CODE
AND MT.CALENDAR_TYPE=MOC.CALENDAR_TYPE
AND MT.DAY BETWEEN MSC.FROM_DATE
AND MSC.TO_DATE
AND MIS.INCLUDE_LIABILITY_AGREEMENT = 1
AND MTP.PARTNER_ID=MIS.SUPPLIER_ID
AND MTP.PARTNER_TYPE=1
AND PRD.LEVEL_ID = 1
AND PRD.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID)
AND PRD.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND ORG.LEVEL_ID = 7
AND ORG.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID)
AND ORG.SR_LEVEL_PK = TO_CHAR(MIS.ORGANIZATION_ID)
AND ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND GEO.LEVEL_ID = 50
AND GEO.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID)
AND GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID)
AND GEO.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
, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, 50 GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_NUMBER(NULL)
, 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
, TO_NUMBER(NULL)
FROM MSD_CS_DATA MCD
, MSD_LEVEL_VALUES_DS MLV_ORG
, MSD_LEVEL_VALUES_DS MLV_PRD
, MSD_LEVEL_VALUES_DS MLV_GEO
, MSD_DP_SESSION MDS
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_SUPPLY_COMMIT')
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
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
AND MLV_GEO.DEMAND_PLAN_ID=MDS.DEMAND_PLAN_ID