View: MRP_AP_RESOURCE_REQUIREMENTS_V

View Text

SELECT /*+ ORDERED */ WO.DEPARTMENT_ID
, WOR.RESOURCE_ID
, MAWJV.ORGANIZATION_ID
, MAWJV.INVENTORY_ITEM_ID
, WO.WIP_ENTITY_ID
, WO.OPERATION_SEQ_NUM
, WO.OPERATION_SEQUENCE_ID
, WOR.RESOURCE_SEQ_NUM
, NVL( WOR.START_DATE
, MAWJV.START_DATE) FIRST_UNIT_START_DATE
, DECODE( WOR.BASIS_TYPE
, 1
, (NVL( WOR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE/ MUC1.CONVERSION_RATE
, 0) * WO.SCHEDULED_QUANTITY)
, NVL( WOR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE/ MUC1.CONVERSION_RATE
, 0)) OPERATION_HOURS_REQUIRED
, DECODE( WOR.BASIS_TYPE
, 1
, NVL( MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE
, 0) * WOR.APPLIED_RESOURCE_UNITS
, DECODE( WOR.APPLIED_RESOURCE_UNITS
, 0
, 0
, NVL( WOR.USAGE_RATE_OR_AMOUNT*MUC2.CONVERSION_RATE/ MUC1.CONVERSION_RATE
, 0))) HOURS_EXPENDED
, MAWJV.DEMAND_CLASS
, WOR.BASIS_TYPE
, WOR.ASSIGNED_UNITS RESOURCE_UNITS
, WOR.COMPLETION_DATE
, MAWJV.WIP_JOB_TYPE
, MAWJV.SCHEDULED_COMPLETION_DATE
, WO.SCHEDULED_QUANTITY
, MAWJV.START_DATE
, MAWJV.MPS_NET_QUANTITY
, MAWJV.NET_QUANTITY
, MUC1.UOM_CODE
, MUC2.RN RN7
, MUC1.RN RN6
, WOR.RN RN5
, WO.RN RN4
, MAWJV.RN3 RN3
, MAWJV.RN2 RN2
, MAWJV.RN1 RN1
FROM MRP_AP_WIP_JOBS_V MAWJV
, MRP_AP_WOPRS_SN WO
, MRP_AP_WOPR_RESS_SN WOR
, MRP_AP_RESS_SN BR
, MRP_AP_UOM_CNVS_SN MUC1
, MRP_AP_UOM_CNVS_SN MUC2
WHERE WO.ORGANIZATION_ID = MAWJV.ORGANIZATION_ID
AND WO.WIP_ENTITY_ID = MAWJV.WIP_ENTITY_ID
AND WO.SCHEDULED_QUANTITY - NVL(WO.QUANTITY_COMPLETED
, 0) > 0
AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND BR.ORGANIZATION_ID = MAWJV.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND MUC2.UOM_CODE = BR.UNIT_OF_MEASURE
AND MUC2.INVENTORY_ITEM_ID = 0
AND MUC1.UOM_CLASS = MUC2.UOM_CLASS
AND MUC1.INVENTORY_ITEM_ID = 0

Columns