MSC_AP_WD_OP_RESOURCES_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

WORK_DEFINITION_ID ORGANIZATION_CODE ASSEMBLY_NAME WORK_DEFINITION_CODE OPERATION_SEQ_NUMBER EFFECTIVITY_DATE SR_WD_OPERATION_ID RESOURCE_SEQ_NUMBER RESOURCE_ID RESOURCE_CODE PRIMARY_RESOURCE_ID PRIMARY_RESOURCE_CODE PR_USAGE_RATE ALT_USAGE_RATE BASIS_TYPE ALT_ASSIGNED_UNITS PR_ASSIGNED_UNITS ALT_UOM_CODE PR_UOM_CODE RESOURCE_TYPE ALTERNATE_NUMBER PRINCIPAL_FLAG SETUP_ID SUBSTITUTE_GROUP_NUM WORK_CENTER_CODE SCHEDULE_FLAG WOR_ROW_ID WALT_ROW_ID WD_OP_ALT_RESOURCE_ID OUTPUT_OP_EFF_FROM_DATE OUTPUT_OP_EFF_TO_DATE

Query

SQL_Statement

select wop.work_definition_id, wop.organization_code, wop.assembly_name, wop.work_definition_code, wop.operation_seq_number, wop.effectivity_date, wop.wd_operation_id SR_WD_OPERATION_ID, wor.resource_seq_number, nvl(walt.resource_id,wor.resource_id) resource_id , nvl(walt.resource_code,wor.resource_code) resource_code , nvl2(walt.resource_id,wor.resource_id,null) primary_resource_id, nvl2(walt.resource_id,wor.resource_code,null) primary_resource_code, decode(wor.basis_type,2,wor.usage_rate,decode(woo1.output_quantity,null,wor.usage_rate,wor.usage_rate/ nvl(decode(woo1.uom_code, wop.primary_uom_code,woo1.output_quantity, inv_convert.inv_um_convert(wop.assembly_item_id, 10,woo1.output_quantity, woo1.uom_code, wop.primary_uom_code,null, null)),-99999) ) ) pr_usage_rate , decode(wor.basis_type,2,walt.usage_rate,decode(woo1.output_quantity,null,walt.usage_rate,walt.usage_rate/ nvl(decode(woo1.uom_code, wop.primary_uom_code,woo1.output_quantity, inv_convert.inv_um_convert(wop.assembly_item_id, 10,woo1.output_quantity, woo1.uom_code, wop.primary_uom_code,null, null)),-99999) ))alt_usage_rate, to_number(wor.basis_type) basis_type, walt.assigned_units alt_assigned_units, wor.assigned_units pr_assigned_units, walt.uom_code alt_uom_code, wor.uom_code pr_uom_code, 0 resource_type, nvl(walt.priority_ranking,0) alternate_number, decode(wor.principal_flag, 'Y',1 ,'N',2) principal_flag, null setup_id, 0 substitute_group_num, wcb.work_center_code, decode(wor.schedule_type, 'YES', 1, 'NO',2,'PRIOR', 3, 'NEXT', 4, 1) schedule_flag, wor.ROW_ID wor_row_id , walt.row_id walt_row_id, walt.wd_op_alt_resource_id WD_OP_ALT_RESOURCE_ID, woo1.output_op_eff_from_date output_op_eff_from_date, woo1.output_op_eff_to_date output_op_eff_to_date from MSC_AP_WD_OPERATIONS_V wop, WIS_WD_OPERATION_RESOURCES_V wor, wis_work_centers_b wcb, WIS_WD_OP_ALT_RESOURCES_V walt, ( select woo.*,wop1.effectivity_date output_op_eff_from_date, nvl(wop1.disable_date, sysdate+36500) output_op_eff_to_date from wis_wd_operation_outputs woo , MSC_AP_WD_OPERATIONS_V wop1 where woo.primary_flag = 'Y' and wop1.work_Definition_id = woo.work_Definition_id and wop1.organization_id = woo.organization_id and wop1.WORK_METHOD_CODE = 2 and wop1.wd_operation_id = woo.wd_operation_id ) woo1 where WOP.REFERENCED_FLAG='N' AND wop.work_method_code in (1,2) AND wop.wd_operation_id = wor.wd_operation_id and wor.schedule_type <> 'NO' and wor.work_center_id = wcb.work_center_id and walt.WD_OPERATION_RESOURCE_ID(+)= wor.wd_operation_resource_id AND wop.work_definition_id = woo1.work_definition_id(+) AND wop.organization_id = woo1.organization_id(+) AND nvl(wop.disable_date, sysdate+36500) >= decode(wop.WORK_METHOD_CODE,2,woo1.output_op_eff_from_date, sysdate-36500) and wop.effectivity_date <= decode(wop.WORK_METHOD_CODE,2,woo1.output_op_eff_to_date,wop.effectivity_date) UNION select wop.work_definition_id, wop.organization_code, wop.assembly_name, wop.work_definition_code, wop.operation_seq_number, wop.effectivity_date, wop.wd_operation_id SR_WD_OPERATION_ID, wor.resource_seq_number, nvl(walt.resource_id,wor.resource_id) resource_id , nvl((select resource_code from WIS_RESOURCES_VL where resource_id=walt.resource_id),wor.resource_code) resource_code , nvl2(walt.resource_id,wor.resource_id,null) primary_resource_id, nvl2(walt.resource_id,wor.resource_code,null) primary_resource_code, decode(wor.basis_type,2,wor.usage_rate,decode(woo1.output_quantity,null,wor.usage_rate,wor.usage_rate/ nvl(decode(woo1.uom_code, wop.primary_uom_code,woo1.output_quantity, inv_convert.inv_um_convert(wop.assembly_item_id, 10,woo1.output_quantity, woo1.uom_code, wop.primary_uom_code,null, null)),-99999) ) ) pr_usage_rate , decode(wor.basis_type,2,walt.usage_rate,decode(woo1.output_quantity,null,walt.usage_rate,walt.usage_rate/ nvl(decode(woo1.uom_code, wop.primary_uom_code,woo1.output_quantity, inv_convert.inv_um_convert(wop.assembly_item_id, 10,woo1.output_quantity, woo1.uom_code, wop.primary_uom_code,null, null)),-99999) ))alt_usage_rate, to_number(wor.basis_type) basis_type, walt.assigned_units alt_assigned_units, wor.assigned_units pr_assigned_units, walt.uom_code alt_uom_code, wor.uom_code pr_uom_code, 0 resource_type, nvl(walt.priority_ranking,0) alternate_number, decode(wor.principal_flag, 'Y',1 ,'N',2) principal_flag, null setup_id, 0 substitute_group_num, wcb.work_center_code, decode(wor.schedule_type, 'YES', 1, 'NO',2,'PRIOR', 3, 'NEXT', 4, 1) schedule_flag, wor.ROW_ID wor_row_id , walt.row_id walt_row_id , walt.std_op_alt_resource_id WD_OP_ALT_RESOURCE_ID, woo1.output_op_eff_from_date output_op_eff_from_date, woo1.output_op_eff_to_date output_op_eff_to_date from MSC_AP_WD_OPERATIONS_V wop, WIS_WD_OPERATION_RESOURCES_V wor, wis_work_centers_b wcb, WIS_WD_OP_ALT_RESOURCES_V walt, ( select woo.*,wop1.effectivity_date output_op_eff_from_date, nvl(wop1.disable_date, sysdate+36500) output_op_eff_to_date from wis_wd_operation_outputs woo , MSC_AP_WD_OPERATIONS_V wop1 where woo.primary_flag = 'Y' and wop1.work_Definition_id = woo.work_Definition_id and wop1.organization_id = woo.organization_id and wop1.WORK_METHOD_CODE = 2 and wop1.wd_operation_id = woo.wd_operation_id ) woo1 where WOR.wd_operation_id = WOP.wd_operation_id AND WOP.REFERENCED_FLAG='Y' AND wop.work_method_code in (1,2) and wor.schedule_type <> 'NO' and wor.work_center_id = wcb.work_center_id and wop.wd_operation_id = walt.wd_operation_id (+) and( walt.std_operation_resource_id(+)=wor.STD_OPERATION_RESOURCE_ID) AND wop.work_definition_id = woo1.work_definition_id(+) AND wop.organization_id = woo1.organization_id(+) AND nvl(wop.disable_date, sysdate+36500) >= decode(wop.WORK_METHOD_CODE,2,woo1.output_op_eff_from_date, sysdate-36500) and wop.effectivity_date <= decode(wop.WORK_METHOD_CODE,2,woo1.output_op_eff_to_date,wop.effectivity_date)