1. Tables and Views for SCM
  2. MSC_AP_WD_OPERATION_OUTPUTS_V

MSC_AP_WD_OPERATION_OUTPUTS_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

WD_OPERATION_OUTPUT_ID

OUTPUT_SEQ_NUMBER

OUTPUT_TYPE

OUTPUT_QUANTITY

USAGE_QUANTITY

UOM_CODE

WORK_DEFINITION_ID

WD_OPERATION_ID

BILL_SEQUENCE_ID

OPERATION_SEQ_NUMBER

OP_EFFECTIVE_FROM_DATE

ORGANIZATION_ID

INVENTORY_ITEM_ID

COMPL_SUBINVENTORY_CODE

WORK_DEFINITION_CODE

ORGANIZATION_CODE

COMPONENT_TYPE

ITEM_STRUCT_COMPONENT_REF_FLAG

COMPONENT_NAME

ASSEMBLY_NAME

PRIMARY_FLAG

OUTPUT_OP_EFF_FROM_DATE

OUTPUT_OP_EFF_TO_DATE

WORK_METHOD_CODE

Query

SQL_Statement

SELECT wom.WD_OPERATION_OUTPUT_ID,

wom.OUTPUT_SEQ_NUMBER,

(CASE

WHEN wom.output_type = 'PRODUCT' and wom.primary_flag = 'Y'

THEN 1

WHEN wom.output_type = 'PRODUCT' and wom.primary_flag = 'N'

THEN 2

WHEN wom.output_type = 'BY_PRODUCT' and wom.primary_flag = 'N'

THEN 3 END)OUTPUT_TYPE,

NVL(DECODE(wom.uom_code, esi1.uom_code, wom.OUTPUT_QUANTITY,

DECODE(wom.OUTPUT_QUANTITY, NULL, NULL,

inv_convert.inv_um_convert(esi1.inventory_item_id,

10,

wom.OUTPUT_QUANTITY,

wom.uom_code,

esi1.uom_code,

NULL,

NULL)

)

), -99999) OUTPUT_QUANTITY,

DECODE(wob.work_method_code,2,(NVL(DECODE(wom.uom_code, esi1.uom_code, wom.OUTPUT_QUANTITY,

DECODE(wom.OUTPUT_QUANTITY, NULL, NULL,

inv_convert.inv_um_convert(esi1.inventory_item_id,

10,

wom.OUTPUT_QUANTITY,

wom.uom_code,

esi1.uom_code,

NULL,

NULL)

)

), -99999)/

nvl(decode(primary_output.uom_code, wob.primary_uom_code,primary_output.output_quantity,

inv_convert.inv_um_convert(primary_output.inventory_item_id, 10,primary_output.output_quantity, primary_output.uom_code, wob.primary_uom_code,null, null)), -99999)

), NVL(DECODE(wom.uom_code, esi1.uom_code, wom.OUTPUT_QUANTITY,

DECODE(wom.OUTPUT_QUANTITY, NULL, NULL,

inv_convert.inv_um_convert(esi1.inventory_item_id,

10,

wom.OUTPUT_QUANTITY,

wom.uom_code,

esi1.uom_code,

NULL,

NULL)

)

), -99999) ) usage_quantity,

esi1.uom_code,

wob.work_definition_id,

wom.wd_operation_id,

wob.bill_sequence_id,

wob.operation_seq_number,

wob.effectivity_date op_effective_from_date,

wob.organization_id,

wom.inventory_item_id,

wom.compl_subinventory_code,

wob.work_definition_code,

wob.organization_code,

1 component_type,

2 item_struct_component_ref_flag,

esi1.item_number component_name,

wob.assembly_name,

wom.primary_flag,

primary_output.output_op_eff_from_date output_op_eff_from_date,

primary_output.output_op_eff_to_date output_op_eff_to_date,

wob.work_method_code

FROM msc_ap_wd_operations_v wob,

wis_wd_operation_outputs wom,

msc_planned_egp_system_items esi1,

(

SELECT

woo.*,

wop1.effective_from_date output_op_eff_from_date,

nvl(wop1.effective_to_date, sysdate+36500) output_op_eff_to_date

FROM

wis_wd_operation_outputs woo ,

wis_work_definitions wd1,

wis_work_methods_b wwm,

wis_wd_operations_v wop1

WHERE woo.primary_flag = 'Y'

AND wd1.work_Definition_id = woo.work_Definition_id

AND wd1.organization_id = woo.organization_id

AND wd1.work_method_id = wwm.work_method_id

AND wwm.work_method_code = 'PROCESS_MANUFACTURING'

AND wop1.wd_operation_id = woo.wd_operation_id

) primary_output

WHERE wob.wd_operation_id = wom.wd_operation_id

AND wom.organization_id = esi1.organization_id

AND wom.inventory_item_id = esi1.inventory_item_id

AND wom.work_definition_id = primary_output.work_definition_id(+)

AND wob.assembly_item_id = primary_output.inventory_item_id(+)

AND nvl(wob.disable_date, sysdate+36500) >= decode(wob.WORK_METHOD_CODE,2,primary_output.output_op_eff_from_date(+), sysdate-36500)

and wob.effectivity_date <= decode(wob.WORK_METHOD_CODE,2,primary_output.output_op_eff_to_date(+),wob.effectivity_date)