View: MSC_RESOURCE_REQUIREMENTS_V

Product:	MSC - Advanced Supply Chain Planning
Description:	this view shows all the resource requirements
Implementation/DBA Data:	APPS.MSC_RESOURCE_REQUIREMENTS_V

View Text

SELECT RES.ROWID
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, RES.DEPARTMENT_ID
, MSC_GET_NAME.DEPARTMENT_CODE(DECODE(RES.RESOURCE_ID
, -1
, 1
, 2)
, RES.DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.PLAN_ID
, RES.SR_INSTANCE_ID)
, RES.RESOURCE_ID
, MSC_GET_NAME.RESOURCE_CODE(RES.RESOURCE_ID
, RES.DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.PLAN_ID
, RES.SR_INSTANCE_ID)
, NVL(RES.FIRM_START_DATE
, RES.START_DATE)
, NVL(RES.FIRM_END_DATE
, RES.END_DATE)
, MR.INVENTORY_ITEM_ID
, MI1.ITEM_NAME
, RES.SOURCE_ITEM_ID
, MI2.ITEM_NAME
, RES.SUPPLY_ID
, DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, DECODE(RES.PARENT_SEQ_NUM
, NULL
, ( RES.RESOURCE_HOURS*NVL(MDR.EFFICIENCY
, 100)*NVL(MDR.UTILIZATION
, 100)*.0001)
, RES.RESOURCE_HOURS))
, DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, RES.DAILY_RESOURCE_HOURS)
, RES.OPERATION_SEQ_NUM
, MR.NEW_SCHEDULE_DATE
, DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, DECODE(MRT.CFM_ROUTING_FLAG
, 3
, NVL(RES.CUMMULATIVE_QUANTITY
, MR.NEW_ORDER_QUANTITY)
, NVL(RES.CUMMULATIVE_QUANTITY
, MR.NEW_ORDER_QUANTITY)))
, NVL(NVL(MR.ORDER_NUMBER
, MR.WIP_ENTITY_NAME)
, TO_CHAR(MR.TRANSACTION_ID))
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, MR.ORDER_TYPE)
, MR.DAILY_RATE
, MR.LAST_UNIT_COMPLETION_DATE
, RES.LOAD_RATE
, RES.SR_INSTANCE_ID
, RES.ASSIGNED_UNITS
, 1
, RES.FIRM_FLAG
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, NVL(RES.FIRM_FLAG
, 0))
, RES.STATUS
, RES.APPLIED
, RES.YIELD
, RES.REVERSE_CUMULATIVE_YIELD
, ROUND(RES.RESOURCE_HOURS*(NVL(MDR.UTILIZATION
, 100)*.01)
, 6)
, DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, RES.RESOURCE_HOURS)
, RES.BATCH_NUMBER
, RES.RESOURCE_SEQ_NUM
, MR.NEW_ORDER_QUANTITY * DECODE(MDR.UOM_CLASS_TYPE
, 1
, MSI.UNIT_WEIGHT
, MSI.UNIT_VOLUME) * NVL(DECODE(RES.END_DATE
, NULL
, RES.RESOURCE_HOURS
, RES.DAILY_RESOURCE_HOURS * MSC_GET_NAME.GET_NUMBER_WORK_DAYS( NVL(RES.FIRM_START_DATE
, RES.START_DATE)
, NVL(RES.FIRM_END_DATE
, RES.END_DATE)
, RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID))
, 0)
, RES.OVERLOADED_CAPACITY
, RES.EARLIEST_START_DATE
, RES.EARLIEST_COMPLETION_DATE
, RES.ULPSD
, RES.ULPCD
, RES.UEPSD
, RES.UEPCD
, RES.EACD
FROM MSC_ITEMS MI1
, MSC_ITEMS MI2
, MSC_SUPPLIES MR
, MSC_RESOURCE_REQUIREMENTS RES
, MSC_ROUTINGS MRT
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_SYSTEM_ITEMS MSI
WHERE MR.INVENTORY_ITEM_ID = MI1.INVENTORY_ITEM_ID(+)
AND RES.SOURCE_ITEM_ID = MI2.INVENTORY_ITEM_ID(+)
AND MR.TRANSACTION_ID = RES.SUPPLY_ID
AND MR.PLAN_ID = RES.PLAN_ID
AND NVL(RES.PARENT_ID
, 2) = 2
AND RES.PLAN_ID = MRT.PLAN_ID (+)
AND RES.SR_INSTANCE_ID = MRT.SR_INSTANCE_ID (+)
AND RES.ROUTING_SEQUENCE_ID = MRT.ROUTING_SEQUENCE_ID (+)
AND MDR.PLAN_ID = RES.PLAN_ID
AND MDR.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND MDR.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND MDR.RESOURCE_ID = RES.RESOURCE_ID
AND MDR.DEPARTMENT_ID = RES.DEPARTMENT_ID
AND RES.PLAN_ID = MSI.PLAN_ID(+)
AND RES.ORGANIZATION_ID =MSI.ORGANIZATION_ID(+)
AND RES.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID(+)
AND RES.ASSEMBLY_ITEM_ID =MSI.INVENTORY_ITEM_ID(+)
AND NVL( MDR.UNIT_OF_MEASURE
, 1) = DECODE(MDR.BATCHABLE_FLAG
, 1
, DECODE(MDR.UOM_CLASS_TYPE
, 1
, MSI.WEIGHT_UOM
, MSI.VOLUME_UOM)
, NVL( MDR.UNIT_OF_MEASURE
, 1) ) UNION ALL SELECT MS.ROWID
, MS.TRANSACTION_ID
, MS.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID)
, MS.PLAN_ID
, MS.SOURCE_ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(MS.SOURCE_ORGANIZATION_ID
, MS.SR_INSTANCE_ID)
, MISM.TRANSACTION_ID
, MS.SHIP_METHOD
, MS.NEW_WIP_START_DATE
, MS.NEW_DOCK_DATE
, MS.INVENTORY_ITEM_ID
, MI1.ITEM_NAME
, MS.SOURCE_ITEM_ID
, MI2.ITEM_NAME
, MS.TRANSACTION_ID
, MS.WEIGHT_CAPACITY_USED
, MS.VOLUME_CAPACITY_USED
, MS.OPERATION_SEQ_NUM
, MS.NEW_SCHEDULE_DATE
, MS.NEW_ORDER_QUANTITY
, NVL(MS.ORDER_NUMBER
, MS.WIP_ENTITY_NAME)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, MS.ORDER_TYPE)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, MS.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, 2
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
FROM MSC_ITEMS MI1
, MSC_ITEMS MI2
, MSC_INTERORG_SHIP_METHODS MISM
, MSC_SUPPLIES MS
WHERE MS.INVENTORY_ITEM_ID = MI1.INVENTORY_ITEM_ID(+)
AND MS.SOURCE_ITEM_ID = MI2.INVENTORY_ITEM_ID(+)
AND MISM.PLAN_ID = MS.PLAN_ID
AND MISM.TO_ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MISM.FROM_ORGANIZATION_ID = MS.SOURCE_ORGANIZATION_ID
AND MISM.SHIP_METHOD = MS.SHIP_METHOD
AND MISM.SR_INSTANCE_ID = MS.SR_INSTANCE_ID

Columns