VIEW: APPS.MSC_CRITICAL_PATHS_V

Source

View Text - Preformatted

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 AND mrr.schedule_flag = 1

View Text - HTML Formatted

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
AND MRR.SCHEDULE_FLAG = 1