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

FIND_NUMBER

COMP_EFFECTIVITY_DATE

COMP_DISABLE_DATE

BASIS_TYPE

PRODUCT_YIELD

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, wom.primary_uom_code, wom.output_quantity,

DECODE(wom.output_quantity, NULL, NULL,

inv_convert.inv_um_convert(wom.inventory_item_id,

10,

wom.Output_Quantity,

wom.uom_code,

wom.primary_uom_code,

NULL,

NULL)

)

), -99999) OUTPUT_QUANTITY,

DECODE(wob.work_method_code,2,(NVL(DECODE(wom.uom_code, wom.primary_uom_code, wom.output_quantity,

DECODE(wom.output_quantity, NULL, NULL,

inv_convert.inv_um_convert(wom.inventory_item_id,

10,

wom.output_quantity,

wom.uom_code,

wom.primary_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, wom.primary_uom_code, wom.output_quantity,

DECODE(wom.OUTPUT_QUANTITY, NULL, NULL,

inv_convert.inv_um_convert(wom.inventory_item_id,

10,

wom.output_quantity,

wom.uom_code,

wom.primary_uom_code,

NULL,

NULL)

)

), -99999) ) usage_quantity,

wom.primary_uom_code AS 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,

NVL2(wom.component_sequence_id, 1, 2) item_struct_component_ref_flag,

wom.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,

wom.find_number,

wom.comp_effectivity_date,

wom.comp_disable_date,

wom.basis_type,

wom.product_yield

FROM msc_ap_wd_operations_v wob,

msc_wis_wd_operation_outputs_v wom,

(

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.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)