VIEW: APPS.MSC_RESOURCE_REQUIREMENTS_V

Source

View Text - Preformatted

SELECT RES.ROWID , RES.TRANSACTION_ID 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) , RES.start_DATE , RES.END_DATE , mr.inventory_ITEM_ID , mi1.item_name , res.SOURCE_ITEM_ID , mi2.item_name , RES.supply_ID , round(res.UNADJUSTED_RESOURCE_HOURS,6) ,round( decode(res.resource_hours, 0, to_number(null), RES.DAILY_RESOURCE_HOURS),6) , RES.OPERATION_SEQ_NUM , MR.NEW_SCHEDULE_DATE ,decode(res.plan_id, -1, to_number(null), decode(res.resource_hours, 0, to_number(null), MR.NEW_ORDER_QUANTITY)) , msc_get_name.supply_order_number( mr.order_type ,mr.order_number ,mr.plan_id ,mr.sr_instance_id ,mr.transaction_id ,mr.disposition_id ) , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mr.order_type,92,70,mr.order_type)) , MR.DAILY_RATE , MR.LAST_UNIT_COMPLETION_DATE , RES.LOAD_RATE ,res.sr_instance_id ,res.assigned_units ,1 , decode(mr.order_type,70, decode(nvl(mr.firm_planned_type,2),1,7,res.firm_flag),res.firm_flag) , decode(mr.order_type,70, decode(nvl(mr.firm_planned_type,2),1,msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',7),msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',nvl(res.firm_flag,0))), 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.TOUCH_TIME,6) ,round( decode(res.resource_hours,0,to_number(null),res.resource_hours),6) ,res.batch_number ,res.resource_seq_num ,to_number(null) ,res.overloaded_capacity ,res.earliest_start_date ,res.earliest_completion_date ,res.ULPSD ,res.ULPCD ,res.UEPSD ,res.UEPCD ,res.EACD ,res.schedule_flag ,msc_get_name.lookup_meaning('MSC_SCHEDULE_TYPE', res.schedule_flag) ,res.quantity_in_queue ,res.quantity_running ,res.quantity_waiting_to_move ,res.quantity_completed ,res.usage_rate ,res.hours_expended ,null ,to_number(null) ,null ,to_number(Null) ,decode(res.record_source, 2, msc_get_name.lookup_meaning('MSC_ORIGINATION_CODE_TYPE',2 ), msc_get_name.lookup_meaning('MSC_ORIGINATION_CODE_TYPE',1 )) ,null serial_number ,to_number(null) equipment_item_id ,null equipment_name ,msc_get_name.setup_code (res.plan_id , res.sr_instance_id , res.resource_id , res.organization_id, res.from_setup_id ) ,msc_get_name.setup_code (res.plan_id , res.sr_instance_id , res.resource_id, res.organization_id, res.setup_id ) ,RES.GROUP_SEQUENCE_ID ,RES.GROUP_SEQUENCE_NUMBER ,msc_get_name.setup_std_op_code( res.plan_id, res.sr_instance_id, res.department_id, res.organization_id, res.supply_id, res.operation_seq_num, res.resource_seq_num, res.parent_seq_num, res.setup_id, res.schedule_flag) ,RES.SETUP_ID ,to_number(null) RES_INSTANCE_ID ,null View_Type ,res.STEP_QUANTITY ,res.STEP_QUANTITY_UOM ,to_number(null) RES_INST_TRANSACTION_ID ,RES.JIT_END_DATE ,MR.SCHEDULE_PRIORITY ,res.actual_start_date ,round(res.total_resource_hours,6) ,res.firm_start_date ,res.firm_end_date ,res.operation_name ,res.activity_name ,mi1.description FROM msc_system_items mi1, msc_items mi2, msc_supplies MR, msc_resource_requirements RES, msc_department_resources mdr, msc_system_items msi WHERE mr.inventory_item_id = mi1.inventory_item_id(+) and mr.plan_id = mi1.plan_id(+) and mr.organization_id =mi1.organization_id(+) and mr.sr_instance_id = mi1.sr_instance_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 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 not exists( select 1 from msc_resource_instance_reqs mrir where res.plan_id = mrir.plan_id and res.sr_instance_id = mrir.sr_instance_id and res.organization_id = mrir.organization_id and res.department_id = mrir.department_id and res.resource_id = mrir.resource_id and res.supply_id = mrir.supply_id and res.operation_seq_num = mrir.operation_seq_num and res.resource_seq_num = mrir.resource_seq_num and nvl(res.orig_resource_seq_num, -23453) = nvl(mrir.orig_resource_seq_num, -23453) and decode(mrir.plan_id,-1, -23453, nvl(res.parent_seq_num,-23453)) = decode(mrir.plan_id,-1, -23453, nvl(mrir.parent_seq_num,-23453)) and nvl(mrir.parent_id,2)=2 ) 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 , round(ms.weight_capacity_used,6) ,round( ms.volume_capacity_used,6) , ms.OPERATION_SEQ_NUM , ms.NEW_SCHEDULE_DATE , ms.NEW_ORDER_QUANTITY , msc_get_name.supply_order_number( ms.order_type ,ms.order_number ,ms.plan_id ,ms.sr_instance_id ,ms.transaction_id ,ms.disposition_id ) , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(ms.order_type,92,70,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) ,to_number(null) ,to_char(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,null ,to_number(null) ,null ,to_number(null) ,null ,null ,to_number(null) ,null ,null ,null ,to_number(null) ,to_number(null) ,null ,to_number(null) ,to_number(null) ,null ,to_number(null) ,to_number(null) ,to_number(null) ,to_date(null) JIT_END_DATE ,MS.SCHEDULE_PRIORITY ,to_date(null) ,to_number(null) ,to_date(null) ,to_date(null) ,to_char(null) ,to_char(null) ,mi1.description FROM msc_system_items mi1, msc_items mi2, msc_interorg_ship_methods mism, Msc_supplies ms WHERE ms.inventory_item_id = mi1.inventory_item_id(+) and ms.plan_id = mi1.plan_id(+) and ms.organization_id =mi1.organization_id(+) and ms.sr_instance_id = mi1.sr_instance_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 union all SELECT /*+ ordered */ RES.ROWID , RES.TRANSACTION_ID ,RES.ORGANIZATION_ID , msc_get_name.org_code(res.organization_id,res.sr_instance_id) , RES.plan_id , jo.DEPARTMENT_ID ,msc_get_name.department_code( decode(res.resource_id, -1, 1, 2),jo.department_id, res.organization_id, res.plan_id,res.sr_instance_id) , res.resource_id ,msc_get_name.resource_code(res.resource_id, jo.department_id,res.organization_id, res.plan_id, res.sr_instance_id) ,res.reco_start_date, RES.reco_completion_DATE , mr.inventory_ITEM_ID , msi.item_name , to_number(null), null, res.transaction_id, round((RES.reco_completion_DATE - res.reco_start_date)*24, 6) resource_hours, to_number(null), RES.OPERATION_SEQ_NUM , MR.NEW_SCHEDULE_DATE , to_number(null), msc_get_name.supply_order_number( mr.order_type ,mr.order_number ,mr.plan_id ,mr.sr_instance_id ,mr.transaction_id ,mr.disposition_id ), msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mr.order_type,92,70,mr.order_type)) , to_number(null) , MR.LAST_UNIT_COMPLETION_DATE , to_number(null), res.sr_instance_id ,res.assigned_units,1 , res.firm_flag , msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',nvl(res.firm_flag,0)) ,to_number(null), to_number(null), jo.yield, to_number(null),to_number(null), to_number(null) ,res.batch_number ,res.resource_seq_num ,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),res.schedule_flag, msc_get_name.lookup_meaning('MSC_SCHEDULE_TYPE', res.schedule_flag) ,to_number(null),to_number(null), to_number(null),to_number(null), res.usage_rate_or_amount ,to_number(null), decode(res.recommended, 'Y', 'Yes', 'y', 'Yes','n','No', 'N', 'No', null, 'No'), res.schedule_seq_num, 'MSC_JOB_OP_RESOURCES', res.usage_rate_or_amount , null ,null ,to_number(null) ,null ,null ,msc_get_name.setup_code( res.plan_id , res.sr_instance_id , res.resource_id, res.organization_id, res.setup_id) , res.group_sequence_id , res.group_sequence_number ,null ,res.setup_id ,to_number(null) ,'RESOURCE' ,to_number(null) ,to_number(null) ,to_number(null) ,to_date(null) JIT_END_DATE ,MR.SCHEDULE_PRIORITY ,to_date(null) ,to_number(null) ,to_date(null) ,to_date(null) ,to_char(null) ,to_char(null) ,msi.description FROM msc_job_op_resources RES, msc_supplies MR, msc_job_operations jo, msc_system_items msi WHERE MR.TRANSACTION_ID = RES.transaction_ID and mr.sr_instance_id = res.sr_instance_id and mr.plan_id = res.plan_id and jo.plan_id =res.plan_id and jo.sr_instance_id = res.sr_instance_id and jo.operation_seq_num = res.operation_seq_num and jo.transaction_id = res.transaction_id and msi.plan_id = res.plan_id and msi.organization_id =res.organization_id and msi.sr_instance_id = res.sr_instance_id and mr.inventory_item_id =msi.inventory_item_id and res.plan_id = -1 and (res.plan_id = -1 and res.operation_seq_num <> nvl(mr.job_op_seq_num,-23453) ) UNION ALL select mrr.rowid , mrr.transaction_id , mrr.organization_id , msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id) , mrr.plan_id , mrr.department_id , msc_get_name.department_code(decode(mrr.resource_id, -1, 1, 2), mrr.department_id, mrr.organization_id, mrr.plan_id,mrr.sr_instance_id) , mrr.resource_id , msc_get_name.resource_code(mrr.resource_id,mrr.department_id, mrr.organization_id, mrr.plan_id,mrr.sr_instance_id) , mrir.start_date , mrir.end_date , mr.inventory_item_id , mi1.item_name , mrr.source_item_id , mi2.item_name , mrir.supply_id ,decode(mrir.plan_id,-1,round(mrir.resource_instance_hours,6),round(mrir.UNADJUSTED_RESOURCE_HOURS,6)) ,round( decode(mrir.resource_instance_hours, 0, to_number(null), mrir.daily_res_instance_hours) ,6) , mrr.operation_seq_num , mr.new_schedule_date , decode(mrr.plan_id, -1, to_number(null), decode(mrir.resource_instance_hours, 0, to_number(null), mr.new_order_quantity)) , msc_get_name.supply_order_number( mr.order_type ,mr.order_number ,mr.plan_id ,mr.sr_instance_id ,mr.transaction_id ,mr.disposition_id ) , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mr.order_type,92,70,mr.order_type)) , mr.daily_rate , mr.last_unit_completion_date , mrr.load_rate , mrr.sr_instance_id , mrr.assigned_units , 1 , mrr.firm_flag , msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',nvl(mrr.firm_flag,0)) , mrir.status , mrir.applied , mrr.yield , mrr.reverse_cumulative_yield ,round(mrir.TOUCH_TIME,6) ,round( decode(mrir.resource_instance_hours, 0, to_number(null), mrir.resource_instance_hours) ,6) , mrr.batch_number , mrr.resource_seq_num ,to_number(null) , mrr.overloaded_capacity , mrr.earliest_start_date , mrr.earliest_completion_date , mrr.ulpsd , mrr.ulpcd , mrr.uepsd , mrr.uepcd , mrr.eacd , mrr.schedule_flag , msc_get_name.lookup_meaning('MSC_SCHEDULE_TYPE', mrr.schedule_flag) , mrr.quantity_in_queue , mrr.quantity_running , mrr.quantity_waiting_to_move , mrr.quantity_completed , mrr.usage_rate , mrr.hours_expended , null ,decode(mrir.setup_sequence_num,-1,to_number(null),mrir.setup_sequence_num) , null , to_number(null) , decode(mrr.record_source, 2, msc_get_name.lookup_meaning('MSC_ORIGINATION_CODE_TYPE',2 ), msc_get_name.lookup_meaning('MSC_ORIGINATION_CODE_TYPE',1 )) , mrir.serial_number , mrir.equipment_item_id , msc_get_name.item_name(mrir.equipment_item_id, mrir.organization_id, mrir.plan_id, mrir.sr_instance_id) , msc_get_name.setup_code(mrr.plan_id,mrr.sr_instance_id, mrr.resource_id, mrr.organization_id, mrr.from_setup_id) , msc_get_name.setup_code(mrr.plan_id,mrr.sr_instance_id, mrr.resource_id, mrr.organization_id, mrr.setup_id) , mrr.group_sequence_id , mrr.group_sequence_number ,msc_get_name.setup_std_op_code( mrr.plan_id, mrr.sr_instance_id, mrr.department_id, mrr.organization_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num, mrr.parent_seq_num, mrr.setup_id, mrr.schedule_flag) , mrr.setup_id , mrir.res_instance_id , 'INSTANCE' ,mrr.STEP_QUANTITY ,mrr.STEP_QUANTITY_UOM ,mrir.RES_INST_TRANSACTION_ID ,mrr.JIT_END_DATE ,MR.SCHEDULE_PRIORITY ,mrr.actual_start_date ,round(mrr.total_resource_hours,6) ,mrr.firm_start_date ,mrr.firm_end_date ,mrr.operation_name ,mrr.activity_name ,mi1.description from msc_resource_requirements mrr, msc_resource_instance_reqs mrir, msc_department_resources mdr, msc_supplies mr, msc_system_items msi, msc_items mi1, msc_items mi2 where mrr.plan_id = mrir.plan_id and mrr.sr_instance_id = mrir.sr_instance_id and mrr.organization_id = mrir.organization_id and mrr.department_id = mrir.department_id and mrr.resource_id = mrir.resource_id and mrr.supply_id = mrir.supply_id and mrr.operation_seq_num = mrir.operation_seq_num and mrr.resource_seq_num = mrir.resource_seq_num and nvl(mrr.orig_resource_seq_num, -23453) = nvl(mrir.orig_resource_seq_num, -23453) and decode(mrir.plan_id,-1, -23453, nvl(mrr.parent_seq_num,-23453)) = decode(mrir.plan_id,-1, -23453, nvl(mrir.parent_seq_num,-23453)) and nvl(mrr.parent_id, -23453) = nvl(mrir.parent_id, -23453) and nvl(mrir.parent_id,2) = 2 and nvl(mrr.parent_id,2) = 2 and mr.plan_id = mrr.plan_id and mr.sr_instance_id = mrr.sr_instance_id and mr.transaction_id = mrr.supply_id and mr.inventory_item_id = mi1.inventory_item_id and mrr.source_item_id = mi2.inventory_item_id(+) 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 mrr.plan_id = msi.plan_id(+) and mrr.sr_instance_id = msi.sr_instance_id(+) and mrr.organization_id = msi.organization_id(+) and mrr.assembly_item_id = msi.inventory_item_id(+)

View Text - HTML Formatted

SELECT RES.ROWID
, RES.TRANSACTION_ID 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)
, RES.START_DATE
, RES.END_DATE
, MR.INVENTORY_ITEM_ID
, MI1.ITEM_NAME
, RES.SOURCE_ITEM_ID
, MI2.ITEM_NAME
, RES.SUPPLY_ID
, ROUND(RES.UNADJUSTED_RESOURCE_HOURS
, 6)
, ROUND( DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, RES.DAILY_RESOURCE_HOURS)
, 6)
, RES.OPERATION_SEQ_NUM
, MR.NEW_SCHEDULE_DATE
, DECODE(RES.PLAN_ID
, -1
, TO_NUMBER(NULL)
, DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, MR.NEW_ORDER_QUANTITY))
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER( MR.ORDER_TYPE
, MR.ORDER_NUMBER
, MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.TRANSACTION_ID
, MR.DISPOSITION_ID )
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, DECODE(MR.ORDER_TYPE
, 92
, 70
, MR.ORDER_TYPE))
, MR.DAILY_RATE
, MR.LAST_UNIT_COMPLETION_DATE
, RES.LOAD_RATE
, RES.SR_INSTANCE_ID
, RES.ASSIGNED_UNITS
, 1
, DECODE(MR.ORDER_TYPE
, 70
, DECODE(NVL(MR.FIRM_PLANNED_TYPE
, 2)
, 1
, 7
, RES.FIRM_FLAG)
, RES.FIRM_FLAG)
, DECODE(MR.ORDER_TYPE
, 70
, DECODE(NVL(MR.FIRM_PLANNED_TYPE
, 2)
, 1
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, 7)
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, NVL(RES.FIRM_FLAG
, 0)))
, 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.TOUCH_TIME
, 6)
, ROUND( DECODE(RES.RESOURCE_HOURS
, 0
, TO_NUMBER(NULL)
, RES.RESOURCE_HOURS)
, 6)
, RES.BATCH_NUMBER
, RES.RESOURCE_SEQ_NUM
, TO_NUMBER(NULL)
, RES.OVERLOADED_CAPACITY
, RES.EARLIEST_START_DATE
, RES.EARLIEST_COMPLETION_DATE
, RES.ULPSD
, RES.ULPCD
, RES.UEPSD
, RES.UEPCD
, RES.EACD
, RES.SCHEDULE_FLAG
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCHEDULE_TYPE'
, RES.SCHEDULE_FLAG)
, RES.QUANTITY_IN_QUEUE
, RES.QUANTITY_RUNNING
, RES.QUANTITY_WAITING_TO_MOVE
, RES.QUANTITY_COMPLETED
, RES.USAGE_RATE
, RES.HOURS_EXPENDED
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, DECODE(RES.RECORD_SOURCE
, 2
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, 2 )
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, 1 ))
, NULL SERIAL_NUMBER
, TO_NUMBER(NULL) EQUIPMENT_ITEM_ID
, NULL EQUIPMENT_NAME
, MSC_GET_NAME.SETUP_CODE (RES.PLAN_ID
, RES.SR_INSTANCE_ID
, RES.RESOURCE_ID
, RES.ORGANIZATION_ID
, RES.FROM_SETUP_ID )
, MSC_GET_NAME.SETUP_CODE (RES.PLAN_ID
, RES.SR_INSTANCE_ID
, RES.RESOURCE_ID
, RES.ORGANIZATION_ID
, RES.SETUP_ID )
, RES.GROUP_SEQUENCE_ID
, RES.GROUP_SEQUENCE_NUMBER
, MSC_GET_NAME.SETUP_STD_OP_CODE( RES.PLAN_ID
, RES.SR_INSTANCE_ID
, RES.DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.SUPPLY_ID
, RES.OPERATION_SEQ_NUM
, RES.RESOURCE_SEQ_NUM
, RES.PARENT_SEQ_NUM
, RES.SETUP_ID
, RES.SCHEDULE_FLAG)
, RES.SETUP_ID
, TO_NUMBER(NULL) RES_INSTANCE_ID
, NULL VIEW_TYPE
, RES.STEP_QUANTITY
, RES.STEP_QUANTITY_UOM
, TO_NUMBER(NULL) RES_INST_TRANSACTION_ID
, RES.JIT_END_DATE
, MR.SCHEDULE_PRIORITY
, RES.ACTUAL_START_DATE
, ROUND(RES.TOTAL_RESOURCE_HOURS
, 6)
, RES.FIRM_START_DATE
, RES.FIRM_END_DATE
, RES.OPERATION_NAME
, RES.ACTIVITY_NAME
, MI1.DESCRIPTION
FROM MSC_SYSTEM_ITEMS MI1
, MSC_ITEMS MI2
, MSC_SUPPLIES MR
, MSC_RESOURCE_REQUIREMENTS RES
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_SYSTEM_ITEMS MSI
WHERE MR.INVENTORY_ITEM_ID = MI1.INVENTORY_ITEM_ID(+)
AND MR.PLAN_ID = MI1.PLAN_ID(+)
AND MR.ORGANIZATION_ID =MI1.ORGANIZATION_ID(+)
AND MR.SR_INSTANCE_ID = MI1.SR_INSTANCE_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 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 NOT EXISTS( SELECT 1
FROM MSC_RESOURCE_INSTANCE_REQS MRIR
WHERE RES.PLAN_ID = MRIR.PLAN_ID
AND RES.SR_INSTANCE_ID = MRIR.SR_INSTANCE_ID
AND RES.ORGANIZATION_ID = MRIR.ORGANIZATION_ID
AND RES.DEPARTMENT_ID = MRIR.DEPARTMENT_ID
AND RES.RESOURCE_ID = MRIR.RESOURCE_ID
AND RES.SUPPLY_ID = MRIR.SUPPLY_ID
AND RES.OPERATION_SEQ_NUM = MRIR.OPERATION_SEQ_NUM
AND RES.RESOURCE_SEQ_NUM = MRIR.RESOURCE_SEQ_NUM
AND NVL(RES.ORIG_RESOURCE_SEQ_NUM
, -23453) = NVL(MRIR.ORIG_RESOURCE_SEQ_NUM
, -23453)
AND DECODE(MRIR.PLAN_ID
, -1
, -23453
, NVL(RES.PARENT_SEQ_NUM
, -23453)) = DECODE(MRIR.PLAN_ID
, -1
, -23453
, NVL(MRIR.PARENT_SEQ_NUM
, -23453))
AND NVL(MRIR.PARENT_ID
, 2)=2 ) 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
, ROUND(MS.WEIGHT_CAPACITY_USED
, 6)
, ROUND( MS.VOLUME_CAPACITY_USED
, 6)
, MS.OPERATION_SEQ_NUM
, MS.NEW_SCHEDULE_DATE
, MS.NEW_ORDER_QUANTITY
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER( MS.ORDER_TYPE
, MS.ORDER_NUMBER
, MS.PLAN_ID
, MS.SR_INSTANCE_ID
, MS.TRANSACTION_ID
, MS.DISPOSITION_ID )
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, DECODE(MS.ORDER_TYPE
, 92
, 70
, 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)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL) JIT_END_DATE
, MS.SCHEDULE_PRIORITY
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, MI1.DESCRIPTION
FROM MSC_SYSTEM_ITEMS MI1
, MSC_ITEMS MI2
, MSC_INTERORG_SHIP_METHODS MISM
, MSC_SUPPLIES MS
WHERE MS.INVENTORY_ITEM_ID = MI1.INVENTORY_ITEM_ID(+)
AND MS.PLAN_ID = MI1.PLAN_ID(+)
AND MS.ORGANIZATION_ID =MI1.ORGANIZATION_ID(+)
AND MS.SR_INSTANCE_ID = MI1.SR_INSTANCE_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 UNION ALL SELECT /*+ ORDERED */ RES.ROWID
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, JO.DEPARTMENT_ID
, MSC_GET_NAME.DEPARTMENT_CODE( DECODE(RES.RESOURCE_ID
, -1
, 1
, 2)
, JO.DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.PLAN_ID
, RES.SR_INSTANCE_ID)
, RES.RESOURCE_ID
, MSC_GET_NAME.RESOURCE_CODE(RES.RESOURCE_ID
, JO.DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.PLAN_ID
, RES.SR_INSTANCE_ID)
, RES.RECO_START_DATE
, RES.RECO_COMPLETION_DATE
, MR.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, TO_NUMBER(NULL)
, NULL
, RES.TRANSACTION_ID
, ROUND((RES.RECO_COMPLETION_DATE - RES.RECO_START_DATE)*24
, 6) RESOURCE_HOURS
, TO_NUMBER(NULL)
, RES.OPERATION_SEQ_NUM
, MR.NEW_SCHEDULE_DATE
, TO_NUMBER(NULL)
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER( MR.ORDER_TYPE
, MR.ORDER_NUMBER
, MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.TRANSACTION_ID
, MR.DISPOSITION_ID )
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, DECODE(MR.ORDER_TYPE
, 92
, 70
, MR.ORDER_TYPE))
, TO_NUMBER(NULL)
, MR.LAST_UNIT_COMPLETION_DATE
, TO_NUMBER(NULL)
, RES.SR_INSTANCE_ID
, RES.ASSIGNED_UNITS
, 1
, RES.FIRM_FLAG
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, NVL(RES.FIRM_FLAG
, 0))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, JO.YIELD
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RES.BATCH_NUMBER
, RES.RESOURCE_SEQ_NUM
, 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)
, RES.SCHEDULE_FLAG
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCHEDULE_TYPE'
, RES.SCHEDULE_FLAG)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RES.USAGE_RATE_OR_AMOUNT
, TO_NUMBER(NULL)
, DECODE(RES.RECOMMENDED
, 'Y'
, 'YES'
, 'Y'
, 'YES'
, 'N'
, 'NO'
, 'N'
, 'NO'
, NULL
, 'NO')
, RES.SCHEDULE_SEQ_NUM
, 'MSC_JOB_OP_RESOURCES'
, RES.USAGE_RATE_OR_AMOUNT
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, MSC_GET_NAME.SETUP_CODE( RES.PLAN_ID
, RES.SR_INSTANCE_ID
, RES.RESOURCE_ID
, RES.ORGANIZATION_ID
, RES.SETUP_ID)
, RES.GROUP_SEQUENCE_ID
, RES.GROUP_SEQUENCE_NUMBER
, NULL
, RES.SETUP_ID
, TO_NUMBER(NULL)
, 'RESOURCE'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL) JIT_END_DATE
, MR.SCHEDULE_PRIORITY
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, MSI.DESCRIPTION
FROM MSC_JOB_OP_RESOURCES RES
, MSC_SUPPLIES MR
, MSC_JOB_OPERATIONS JO
, MSC_SYSTEM_ITEMS MSI
WHERE MR.TRANSACTION_ID = RES.TRANSACTION_ID
AND MR.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND MR.PLAN_ID = RES.PLAN_ID
AND JO.PLAN_ID =RES.PLAN_ID
AND JO.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND JO.OPERATION_SEQ_NUM = RES.OPERATION_SEQ_NUM
AND JO.TRANSACTION_ID = RES.TRANSACTION_ID
AND MSI.PLAN_ID = RES.PLAN_ID
AND MSI.ORGANIZATION_ID =RES.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND MR.INVENTORY_ITEM_ID =MSI.INVENTORY_ITEM_ID
AND RES.PLAN_ID = -1
AND (RES.PLAN_ID = -1
AND RES.OPERATION_SEQ_NUM <> NVL(MR.JOB_OP_SEQ_NUM
, -23453) ) UNION ALL SELECT MRR.ROWID
, MRR.TRANSACTION_ID
, MRR.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(MRR.ORGANIZATION_ID
, MRR.SR_INSTANCE_ID)
, MRR.PLAN_ID
, MRR.DEPARTMENT_ID
, MSC_GET_NAME.DEPARTMENT_CODE(DECODE(MRR.RESOURCE_ID
, -1
, 1
, 2)
, MRR.DEPARTMENT_ID
, MRR.ORGANIZATION_ID
, MRR.PLAN_ID
, MRR.SR_INSTANCE_ID)
, MRR.RESOURCE_ID
, MSC_GET_NAME.RESOURCE_CODE(MRR.RESOURCE_ID
, MRR.DEPARTMENT_ID
, MRR.ORGANIZATION_ID
, MRR.PLAN_ID
, MRR.SR_INSTANCE_ID)
, MRIR.START_DATE
, MRIR.END_DATE
, MR.INVENTORY_ITEM_ID
, MI1.ITEM_NAME
, MRR.SOURCE_ITEM_ID
, MI2.ITEM_NAME
, MRIR.SUPPLY_ID
, DECODE(MRIR.PLAN_ID
, -1
, ROUND(MRIR.RESOURCE_INSTANCE_HOURS
, 6)
, ROUND(MRIR.UNADJUSTED_RESOURCE_HOURS
, 6))
, ROUND( DECODE(MRIR.RESOURCE_INSTANCE_HOURS
, 0
, TO_NUMBER(NULL)
, MRIR.DAILY_RES_INSTANCE_HOURS)
, 6)
, MRR.OPERATION_SEQ_NUM
, MR.NEW_SCHEDULE_DATE
, DECODE(MRR.PLAN_ID
, -1
, TO_NUMBER(NULL)
, DECODE(MRIR.RESOURCE_INSTANCE_HOURS
, 0
, TO_NUMBER(NULL)
, MR.NEW_ORDER_QUANTITY))
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER( MR.ORDER_TYPE
, MR.ORDER_NUMBER
, MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.TRANSACTION_ID
, MR.DISPOSITION_ID )
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, DECODE(MR.ORDER_TYPE
, 92
, 70
, MR.ORDER_TYPE))
, MR.DAILY_RATE
, MR.LAST_UNIT_COMPLETION_DATE
, MRR.LOAD_RATE
, MRR.SR_INSTANCE_ID
, MRR.ASSIGNED_UNITS
, 1
, MRR.FIRM_FLAG
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, NVL(MRR.FIRM_FLAG
, 0))
, MRIR.STATUS
, MRIR.APPLIED
, MRR.YIELD
, MRR.REVERSE_CUMULATIVE_YIELD
, ROUND(MRIR.TOUCH_TIME
, 6)
, ROUND( DECODE(MRIR.RESOURCE_INSTANCE_HOURS
, 0
, TO_NUMBER(NULL)
, MRIR.RESOURCE_INSTANCE_HOURS)
, 6)
, MRR.BATCH_NUMBER
, MRR.RESOURCE_SEQ_NUM
, TO_NUMBER(NULL)
, MRR.OVERLOADED_CAPACITY
, MRR.EARLIEST_START_DATE
, MRR.EARLIEST_COMPLETION_DATE
, MRR.ULPSD
, MRR.ULPCD
, MRR.UEPSD
, MRR.UEPCD
, MRR.EACD
, MRR.SCHEDULE_FLAG
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCHEDULE_TYPE'
, MRR.SCHEDULE_FLAG)
, MRR.QUANTITY_IN_QUEUE
, MRR.QUANTITY_RUNNING
, MRR.QUANTITY_WAITING_TO_MOVE
, MRR.QUANTITY_COMPLETED
, MRR.USAGE_RATE
, MRR.HOURS_EXPENDED
, NULL
, DECODE(MRIR.SETUP_SEQUENCE_NUM
, -1
, TO_NUMBER(NULL)
, MRIR.SETUP_SEQUENCE_NUM)
, NULL
, TO_NUMBER(NULL)
, DECODE(MRR.RECORD_SOURCE
, 2
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, 2 )
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, 1 ))
, MRIR.SERIAL_NUMBER
, MRIR.EQUIPMENT_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MRIR.EQUIPMENT_ITEM_ID
, MRIR.ORGANIZATION_ID
, MRIR.PLAN_ID
, MRIR.SR_INSTANCE_ID)
, MSC_GET_NAME.SETUP_CODE(MRR.PLAN_ID
, MRR.SR_INSTANCE_ID
, MRR.RESOURCE_ID
, MRR.ORGANIZATION_ID
, MRR.FROM_SETUP_ID)
, MSC_GET_NAME.SETUP_CODE(MRR.PLAN_ID
, MRR.SR_INSTANCE_ID
, MRR.RESOURCE_ID
, MRR.ORGANIZATION_ID
, MRR.SETUP_ID)
, MRR.GROUP_SEQUENCE_ID
, MRR.GROUP_SEQUENCE_NUMBER
, MSC_GET_NAME.SETUP_STD_OP_CODE( MRR.PLAN_ID
, MRR.SR_INSTANCE_ID
, MRR.DEPARTMENT_ID
, MRR.ORGANIZATION_ID
, MRR.SUPPLY_ID
, MRR.OPERATION_SEQ_NUM
, MRR.RESOURCE_SEQ_NUM
, MRR.PARENT_SEQ_NUM
, MRR.SETUP_ID
, MRR.SCHEDULE_FLAG)
, MRR.SETUP_ID
, MRIR.RES_INSTANCE_ID
, 'INSTANCE'
, MRR.STEP_QUANTITY
, MRR.STEP_QUANTITY_UOM
, MRIR.RES_INST_TRANSACTION_ID
, MRR.JIT_END_DATE
, MR.SCHEDULE_PRIORITY
, MRR.ACTUAL_START_DATE
, ROUND(MRR.TOTAL_RESOURCE_HOURS
, 6)
, MRR.FIRM_START_DATE
, MRR.FIRM_END_DATE
, MRR.OPERATION_NAME
, MRR.ACTIVITY_NAME
, MI1.DESCRIPTION
FROM MSC_RESOURCE_REQUIREMENTS MRR
, MSC_RESOURCE_INSTANCE_REQS MRIR
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_SUPPLIES MR
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEMS MI1
, MSC_ITEMS MI2
WHERE MRR.PLAN_ID = MRIR.PLAN_ID
AND MRR.SR_INSTANCE_ID = MRIR.SR_INSTANCE_ID
AND MRR.ORGANIZATION_ID = MRIR.ORGANIZATION_ID
AND MRR.DEPARTMENT_ID = MRIR.DEPARTMENT_ID
AND MRR.RESOURCE_ID = MRIR.RESOURCE_ID
AND MRR.SUPPLY_ID = MRIR.SUPPLY_ID
AND MRR.OPERATION_SEQ_NUM = MRIR.OPERATION_SEQ_NUM
AND MRR.RESOURCE_SEQ_NUM = MRIR.RESOURCE_SEQ_NUM
AND NVL(MRR.ORIG_RESOURCE_SEQ_NUM
, -23453) = NVL(MRIR.ORIG_RESOURCE_SEQ_NUM
, -23453)
AND DECODE(MRIR.PLAN_ID
, -1
, -23453
, NVL(MRR.PARENT_SEQ_NUM
, -23453)) = DECODE(MRIR.PLAN_ID
, -1
, -23453
, NVL(MRIR.PARENT_SEQ_NUM
, -23453))
AND NVL(MRR.PARENT_ID
, -23453) = NVL(MRIR.PARENT_ID
, -23453)
AND NVL(MRIR.PARENT_ID
, 2) = 2
AND NVL(MRR.PARENT_ID
, 2) = 2
AND MR.PLAN_ID = MRR.PLAN_ID
AND MR.SR_INSTANCE_ID = MRR.SR_INSTANCE_ID
AND MR.TRANSACTION_ID = MRR.SUPPLY_ID
AND MR.INVENTORY_ITEM_ID = MI1.INVENTORY_ITEM_ID
AND MRR.SOURCE_ITEM_ID = MI2.INVENTORY_ITEM_ID(+)
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 MRR.PLAN_ID = MSI.PLAN_ID(+)
AND MRR.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID(+)
AND MRR.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND MRR.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)