WIE_WO_OPERATION_YIELDS

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
WO_OPERATION_ID WORK_ORDER_ID MATERIAL_ISSUED_QTY MATERIAL_REQUIRED_QTY OUTPUT_COMPLETED_QTY OUTPUT_EXPECTED_QTY INTERMEDIATE_IN_QTY INTERMEDIATE_OUT_QTY YIELD_LOSS PLANNED_YIELD PLANNED_CUM_YIELD ACTUAL_YIELD ACTUAL_CUM_YIELD

Name

WO_OPERATION_ID

WORK_ORDER_ID

MATERIAL_ISSUED_QTY

MATERIAL_REQUIRED_QTY

OUTPUT_COMPLETED_QTY

OUTPUT_EXPECTED_QTY

INTERMEDIATE_IN_QTY

INTERMEDIATE_OUT_QTY

YIELD_LOSS

PLANNED_YIELD

PLANNED_CUM_YIELD

ACTUAL_YIELD

ACTUAL_CUM_YIELD

Query

SQL_Statement
WITH operation_inputs AS ( SELECT wo_operation_id, SUM(nvl(issued_quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_in_qty, SUM(nvl(quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_in_req_qty FROM WIE_WO_OPERATION_MATERIALS WHERE supply_type=1 and nvl(contribute_to_yield_flag,'N')='Y' GROUP BY wo_operation_id ), operation_outputs AS ( SELECT wo_operation_id, SUM(nvl(completed_quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_out_qty, SUM(nvl(output_quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_out_req_qty FROM WIE_WO_OPERATION_OUTPUTS where completion_type='MANUAL' GROUP BY wo_operation_id ), operation_details AS ( SELECT op.work_order_id, op.wo_operation_id, op.op_yield_factor AS planned_yield, nvl(op.completed_quantity, 0) as intermediate_out_qty, op.operation_seq_number, op.previous_cp_op_seq_num, nvl(i.material_in_qty, 0) material_in_qty, nvl(i.material_in_req_qty, 0) material_in_req_qty, nvl(ot.material_out_qty, 0) material_out_qty, nvl(ot.material_out_req_qty, 0) material_out_req_qty FROM WIE_WO_OPERATIONS_B op LEFT JOIN operation_inputs i ON op.wo_operation_id = i.wo_operation_id LEFT JOIN operation_outputs ot ON op.wo_operation_id = ot.wo_operation_id ), recursive_yields (work_order_id, wo_operation_id, operation_seq_number, planned_yield, intermediate_out_qty, previous_cp_op_seq_num, material_in_qty, material_in_req_qty, material_out_qty, material_out_req_qty, intermediate_in_qty, yield_loss, actual_yield, actual_cum_yield, planned_cum_yield) AS ( SELECT work_order_id, wo_operation_id, operation_seq_number, planned_yield, intermediate_out_qty, previous_cp_op_seq_num, material_in_qty, material_in_req_qty, material_out_qty, material_out_req_qty, 0 AS intermediate_in_qty, material_in_qty - (material_out_qty + intermediate_out_qty) AS yield_loss, (material_out_qty + intermediate_out_qty) / NULLIF(material_in_qty,0) AS actual_yield, (material_out_qty + intermediate_out_qty) / NULLIF(material_in_qty,0) AS actual_cum_yield, planned_yield as planned_cum_yield FROM operation_details WHERE previous_cp_op_seq_num IS NULL UNION ALL SELECT od.work_order_id, od.wo_operation_id, od.operation_seq_number, od.planned_yield, od.intermediate_out_qty, od.previous_cp_op_seq_num, od.material_in_qty, od.material_in_req_qty, od.material_out_qty, od.material_out_req_qty, ry.intermediate_out_qty intermediate_in_qty, (ry.intermediate_out_qty + od.material_in_qty) - (od.material_out_qty + od.intermediate_out_qty) AS yield_loss, (od.material_out_qty + od.intermediate_out_qty) / NULLIF((ry.intermediate_out_qty + od.material_in_qty),0) AS actual_yield, (od.material_out_qty + od.intermediate_out_qty) / NULLIF(((ry.intermediate_out_qty / NULLIF(ry.actual_cum_yield, 0)) + od.material_in_qty),0) AS actual_cum_yield, (((ry.intermediate_out_qty * (ry.planned_cum_yield / NULLIF(ry.actual_cum_yield,0) )) + od.material_in_qty) * od.planned_yield)/ NULLIF(((ry.intermediate_out_qty / NULLIF(ry.planned_cum_yield, 0) ) + (od.material_in_qty)),0) AS planned_cum_yield FROM operation_details od JOIN recursive_yields ry ON od.previous_cp_op_seq_num = ry.operation_seq_number and od.work_order_id = ry.work_order_id ) SELECT wo_operation_id, work_order_id, material_in_qty material_issued_qty, material_in_req_qty material_required_qty, material_out_qty output_completed_qty, material_out_req_qty output_expected_qty, intermediate_in_qty, intermediate_out_qty, yield_loss, round(planned_yield, fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) planned_yield, round(planned_cum_yield,fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) planned_cum_yield, round(actual_yield, fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) actual_yield, round(actual_cum_yield, fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) actual_cum_yield FROM recursive_yields

SQL_Statement

WITH operation_inputs AS (

SELECT wo_operation_id, SUM(nvl(issued_quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_in_qty,

SUM(nvl(quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_in_req_qty

FROM WIE_WO_OPERATION_MATERIALS

WHERE supply_type=1 and nvl(contribute_to_yield_flag,'N')='Y'

GROUP BY wo_operation_id

),

operation_outputs AS (

SELECT wo_operation_id, SUM(nvl(completed_quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_out_qty,

SUM(nvl(output_quantity/nvl(batch_uom_conversion_rate,1),0)) AS material_out_req_qty

FROM WIE_WO_OPERATION_OUTPUTS

where completion_type='MANUAL'

GROUP BY wo_operation_id

),

operation_details AS (

SELECT

op.work_order_id,

op.wo_operation_id,

op.op_yield_factor AS planned_yield,

nvl(op.completed_quantity, 0) as intermediate_out_qty,

op.operation_seq_number,

op.previous_cp_op_seq_num,

nvl(i.material_in_qty, 0) material_in_qty,

nvl(i.material_in_req_qty, 0) material_in_req_qty,

nvl(ot.material_out_qty, 0) material_out_qty,

nvl(ot.material_out_req_qty, 0) material_out_req_qty

FROM WIE_WO_OPERATIONS_B op

LEFT JOIN operation_inputs i ON op.wo_operation_id = i.wo_operation_id

LEFT JOIN operation_outputs ot ON op.wo_operation_id = ot.wo_operation_id

),

recursive_yields (work_order_id, wo_operation_id, operation_seq_number, planned_yield, intermediate_out_qty, previous_cp_op_seq_num, material_in_qty, material_in_req_qty, material_out_qty, material_out_req_qty, intermediate_in_qty, yield_loss, actual_yield, actual_cum_yield, planned_cum_yield) AS (

SELECT

work_order_id,

wo_operation_id,

operation_seq_number,

planned_yield,

intermediate_out_qty,

previous_cp_op_seq_num,

material_in_qty,

material_in_req_qty,

material_out_qty,

material_out_req_qty,

0 AS intermediate_in_qty,

material_in_qty - (material_out_qty + intermediate_out_qty) AS yield_loss,

(material_out_qty + intermediate_out_qty) / NULLIF(material_in_qty,0) AS actual_yield,

(material_out_qty + intermediate_out_qty) / NULLIF(material_in_qty,0) AS actual_cum_yield,

planned_yield as planned_cum_yield

FROM operation_details

WHERE previous_cp_op_seq_num IS NULL

UNION ALL

SELECT

od.work_order_id,

od.wo_operation_id,

od.operation_seq_number,

od.planned_yield,

od.intermediate_out_qty,

od.previous_cp_op_seq_num,

od.material_in_qty,

od.material_in_req_qty,

od.material_out_qty,

od.material_out_req_qty,

ry.intermediate_out_qty intermediate_in_qty,

(ry.intermediate_out_qty + od.material_in_qty) - (od.material_out_qty + od.intermediate_out_qty) AS yield_loss,

(od.material_out_qty + od.intermediate_out_qty) / NULLIF((ry.intermediate_out_qty + od.material_in_qty),0) AS actual_yield,

(od.material_out_qty + od.intermediate_out_qty) / NULLIF(((ry.intermediate_out_qty / NULLIF(ry.actual_cum_yield, 0)) + od.material_in_qty),0) AS actual_cum_yield,

(((ry.intermediate_out_qty * (ry.planned_cum_yield / NULLIF(ry.actual_cum_yield,0) )) + od.material_in_qty) * od.planned_yield)/

NULLIF(((ry.intermediate_out_qty / NULLIF(ry.planned_cum_yield, 0) ) + (od.material_in_qty)),0) AS planned_cum_yield

FROM operation_details od

JOIN recursive_yields ry ON od.previous_cp_op_seq_num = ry.operation_seq_number and od.work_order_id = ry.work_order_id

)

SELECT

wo_operation_id,

work_order_id,

material_in_qty material_issued_qty,

material_in_req_qty material_required_qty,

material_out_qty output_completed_qty,

material_out_req_qty output_expected_qty,

intermediate_in_qty,

intermediate_out_qty,

yield_loss,

round(planned_yield, fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) planned_yield,

round(planned_cum_yield,fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) planned_cum_yield,

round(actual_yield, fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) actual_yield,

round(actual_cum_yield, fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) actual_cum_yield

FROM recursive_yields