View: MSC_CRITICAL_PATHS_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_CRITICAL_PATHS_V

View Text

SELECT MCP.PLAN_ID
, MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID
, MS.INVENTORY_ITEM_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MS.SUPPLIER_ID
, MS.SUPPLIER_SITE_ID
, MS.NEW_WIP_START_DATE
, MS.NEW_SCHEDULE_DATE
, MS.TRANSACTION_ID
, MCP.SUPPLY_ID
, MCP.DEMAND_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MS.NEW_ORDER_QUANTITY
, MS.SOURCE_ORGANIZATION_ID
, MS.SOURCE_SR_INSTANCE_ID
, NULL
, NULL
, MTP.ORGANIZATION_CODE
, MSC_GET_NAME.SUPPLIER(MS.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER_SITE(MS.SUPPLIER_SITE_ID)
, NVL(MS.ORDER_NUMBER
, TO_CHAR(MS.TRANSACTION_ID))
, MSI.ITEM_NAME
, MSC_GET_NAME.ORG_CODE(MS.SOURCE_ORGANIZATION_ID
, MS.SOURCE_SR_INSTANCE_ID)
, MS.FIRM_PLANNED_TYPE
, TO_NUMBER(NULL)
, NULL
, MSI.LOW_LEVEL_CODE
, ROUND(NVL(MS.EARLIEST_COMPLETION_DATE - MS.NEED_BY_DATE
, 0)
, 2)
, MS.EARLIEST_START_DATE
, MS.EARLIEST_COMPLETION_DATE
, TO_NUMBER(NULL)
, MSC_GET_NAME.SUPPLY_TYPE(MS.TRANSACTION_ID
, MS.PLAN_ID)
, MS.MIN_START_DATE
, TO_NUMBER(NULL)
, MS.SCHEDULED_DEMAND_ID
, MSC_GET_NAME.DEMAND_NAME(MS.PLAN_ID
, MS.SCHEDULED_DEMAND_ID)
, MS.NEED_BY_DATE
, MCP.PATH_NUMBER
, ROUND(NVL(MCP.TIME_GAP
, 0)/60
, 2)
, MCP.LEVEL_NUMBER
, TO_NUMBER(NULL)
FROM MSC_CRITICAL_PATHS MCP
, MSC_SUPPLIES MS
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS ML
WHERE MCP.PLAN_ID = MS.PLAN_ID
AND MCP.SUPPLY_ID = MS.TRANSACTION_ID
AND MCP.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID
AND MSI.PLAN_ID = MS.PLAN_ID
AND MSI.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MCP.ROUTING_SEQUENCE_ID IS NULL
AND MTP.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE =3
AND MTP.SR_TP_ID = MS.ORGANIZATION_ID
AND ML.LOOKUP_TYPE ='SYS_YES_NO'
AND ML.LOOKUP_CODE = MS.FIRM_PLANNED_TYPE
AND MCP.OPERATION_SEQUENCE_ID IS NULL UNION ALL SELECT MCP.PLAN_ID
, MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID
, MS.INVENTORY_ITEM_ID
, MRR.DEPARTMENT_ID
, MRR.RESOURCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MRR.START_DATE
, MRR.END_DATE
, MRR.TRANSACTION_ID
, MCP.SUPPLY_ID
, MCP.DEMAND_ID
, MCP.ROUTING_SEQUENCE_ID
, MCP.OPERATION_SEQUENCE_ID
, MRR.RESOURCE_HOURS
, MS.NEW_ORDER_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MDR.DEPARTMENT_CODE
, MDR.RESOURCE_CODE
, MTP.ORGANIZATION_CODE
, NULL
, NULL
, NVL(MS.ORDER_NUMBER
, TO_CHAR(MS.TRANSACTION_ID))
, MSI.ITEM_NAME
, NULL
, MS.FIRM_PLANNED_TYPE
, MRR.FIRM_FLAG
, ML.MEANING
, MSI.LOW_LEVEL_CODE
, ROUND(NVL(MS.EARLIEST_COMPLETION_DATE - MS.NEED_BY_DATE
, 0)
, 2)
, MRR.EARLIEST_START_DATE
, MRR.EARLIEST_COMPLETION_DATE
, MRR.RESOURCE_HOURS
, MSC_GET_NAME.SUPPLY_TYPE(MS.TRANSACTION_ID
, MS.PLAN_ID)
, MS.MIN_START_DATE
, MRR.OPERATION_SEQ_NUM
, MS.SCHEDULED_DEMAND_ID
, MSC_GET_NAME.DEMAND_NAME(MS.PLAN_ID
, MS.SCHEDULED_DEMAND_ID)
, MS.NEED_BY_DATE
, MCP.PATH_NUMBER
, ROUND(NVL(MCP.TIME_GAP
, 0)/60
, 2)
, MCP.LEVEL_NUMBER
, MRR.RESOURCE_SEQ_NUM
FROM MSC_CRITICAL_PATHS MCP
, MSC_SUPPLIES MS
, MSC_SYSTEM_ITEMS MSI
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_RESOURCE_REQUIREMENTS MRR
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS ML
WHERE MCP.PLAN_ID = MS.PLAN_ID
AND MCP.SUPPLY_ID = MS.TRANSACTION_ID
AND MCP.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID
AND MSI.PLAN_ID = MS.PLAN_ID
AND MSI.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MRR.PLAN_ID = MCP.PLAN_ID
AND MRR.SR_INSTANCE_ID = MCP.SR_INSTANCE_ID
AND MRR.SUPPLY_ID = MCP.SUPPLY_ID
AND MRR.OPERATION_SEQ_NUM = MCP.OPERATION_SEQUENCE_ID
AND NVL(MRR.ROUTING_SEQUENCE_ID
, -1) = NVL(MCP.ROUTING_SEQUENCE_ID
, -1)
AND NVL(MRR.PARENT_ID
, 2) =2
AND MDR.PLAN_ID = MRR.PLAN_ID
AND MDR.SR_INSTANCE_ID = MRR.SR_INSTANCE_ID
AND MDR.ORGANIZATION_ID = MRR.ORGANIZATION_ID
AND MDR.DEPARTMENT_ID = MRR.DEPARTMENT_ID
AND MDR.RESOURCE_ID = MRR.RESOURCE_ID
AND MTP.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE =3
AND MTP.SR_TP_ID = MS.ORGANIZATION_ID
AND ML.LOOKUP_TYPE ='RESOURCE_FIRM_TYPE'
AND ML.LOOKUP_CODE = MRR.FIRM_FLAG

Columns