WIS_WD_OP_DEP_OP_YIELD

Details

Schema: FUSION
Object owner: WIS
Object type: VIEW

Columns

Name
WD_OPERATION_ID OPERATION_SEQ_NUMBER WORK_DEFINITION_ID WORK_DEFINITION_VERSION_ID VERSION_NUMBER OP_YIELD_FACTOR CUMULATIVE_YIELD REV_CUMULATIVE_YIELD CUMULATIVE_TRANSFER_PCT INPUT_SCALE_FACTOR OUTPUT_SCALE_FACTOR

Name

WD_OPERATION_ID

OPERATION_SEQ_NUMBER

WORK_DEFINITION_ID

WORK_DEFINITION_VERSION_ID

VERSION_NUMBER

OP_YIELD_FACTOR

CUMULATIVE_YIELD

REV_CUMULATIVE_YIELD

CUMULATIVE_TRANSFER_PCT

INPUT_SCALE_FACTOR

OUTPUT_SCALE_FACTOR

Query

SQL_Statement
WITH wm AS ( SELECT /+ materialize / work_method_id FROM wis_work_methods_b WHERE work_method_code = 'PROCESS_MANUFACTURING' ), wd_op_dep AS ( SELECT wdo.work_definition_id AS wd_id, wdo.operation_seq_number, wdv.version_number, wdv.work_definition_version_id wd_ver_id, opdep.previous_wd_operation_id AS prev_op, opdep.next_wd_operation_id AS curr_op, nvl(opdep.transfer_percent, 100) AS transfer_pct, /* set default to 100 / CASE WHEN wdo.referenced_flag = 'Y' THEN nvl(stdo.op_yield_factor, 1.0) ELSE nvl(wdo.op_yield_factor, 1.0) END op_yield / set default to 1 / FROM wis_wd_operations_b wdo, wis_standard_operations_b stdo, wis_wd_versions wdv, wm, wis_work_definitions wd, wis_wd_op_dependencies opdep WHERE wdv.work_definition_id = wdo.work_definition_id AND wdo.standard_operation_id = stdo.standard_operation_id (+) AND wdv.work_definition_id = wd.work_definition_id AND wd.enable_parallel_ops_flag = 'Y' / Only for enforce dep WDs. / AND wd.work_method_id = wm.work_method_id / Only for Process Work Methods / AND wdo.work_definition_id = opdep.work_definition_id AND wdv.work_definition_version_id = opdep.work_definition_version_id AND wdo.wd_operation_id = nvl(opdep.next_wd_operation_id, opdep.previous_wd_operation_id) AND opdep.dependency_type = 'ORA_FINISH_TO_START' / Include only Finish to Start deps / ), wd_op_dep_a AS ( SELECT /+ materialize / FROM wd_op_dep WHERE curr_op IS NOT NULL ), branches_rec ( wd_id, wd_ver_id, version_number, prev_op, curr_op, operation_seq_number, transfer_pct, op_yield, net_yld, net_transfer_pct ) AS ( SELECT wd_id, wd_ver_id, version_number, prev_op, curr_op, operation_seq_number, transfer_pct, op_yield, ln(transfer_pct / 100) + ln(op_yield) AS net_yld, ln(transfer_pct / 100) AS net_transfer_pct FROM wd_op_dep_a WHERE prev_op IS NULL /* starting point / UNION ALL SELECT / leading(dep b) / dep.wd_id, dep.wd_ver_id, dep.version_number, dep.prev_op, dep.curr_op, dep.operation_seq_number, dep.transfer_pct, dep.op_yield, b.net_yld + ln(dep.transfer_pct / 100) + ln(dep.op_yield) AS net_yld, b.net_transfer_pct + ln(dep.transfer_pct / 100) AS net_transfer_pct FROM wd_op_dep_a dep, branches_rec b WHERE b.wd_id = dep.wd_id AND b.wd_ver_id = dep.wd_ver_id AND b.curr_op = dep.prev_op ) CYCLE wd_id, wd_ver_id, curr_op SET is_cycle TO 1 DEFAULT 0, fwd_yields AS ( SELECT wd_id, wd_ver_id, version_number, curr_op, operation_seq_number, op_yield, SUM(exp(net_yld)) AS fwd_yld, SUM(exp(net_transfer_pct)) AS fwd_transfer_pct FROM branches_rec GROUP BY wd_id, wd_ver_id, version_number, curr_op, operation_seq_number, op_yield ), final_yield AS ( SELECT fy.wd_id, fy.wd_ver_id, fy.curr_op, fy.fwd_yld AS net_yield FROM fwd_yields fy, ( SELECT wd_id, wd_ver_id, prev_op FROM wd_op_dep WHERE curr_op IS NULL ) depops WHERE depops.wd_id = fy.wd_id AND depops.wd_ver_id = fy.wd_ver_id AND depops.prev_op = fy.curr_op ) SELECT fw.curr_op wd_operation_id, fw.operation_seq_number operation_seq_number, fw.wd_id work_definition_id, fw.wd_ver_id work_definition_version_id, fw.version_number version_number, fw.op_yield op_yield_factor, round(fw.fwd_yld, 30) cumulative_yield, round((fy.net_yield / nvl(fw.fwd_yld, 1.0)) fw.op_yield, 30) rev_cumulative_yield, round(fw.fwd_transfer_pct * 100, 30) cumulative_transfer_pct, round((fw.fwd_yld / nvl((fw.op_yield * fw.fwd_transfer_pct), 1.0)), 30) input_scale_factor, round((fw.fwd_yld / nvl(fw.fwd_transfer_pct, 1.0)), 30) output_scale_factor FROM fwd_yields fw, final_yield fy WHERE fw.wd_id = fy.wd_id AND fw.wd_ver_id = fy.wd_ver_id ORDER BY fw.wd_id, fw.wd_ver_id, fw.curr_op

SQL_Statement

WITH wm AS (

SELECT /*+ materialize */

work_method_id

FROM

wis_work_methods_b

WHERE

work_method_code = 'PROCESS_MANUFACTURING'

), wd_op_dep AS (

SELECT

wdo.work_definition_id AS wd_id,

wdo.operation_seq_number,

wdv.version_number,

wdv.work_definition_version_id wd_ver_id,

opdep.previous_wd_operation_id AS prev_op,

opdep.next_wd_operation_id AS curr_op,

nvl(opdep.transfer_percent, 100) AS transfer_pct, /* set default to 100 */

CASE

WHEN wdo.referenced_flag = 'Y' THEN

nvl(stdo.op_yield_factor, 1.0)

ELSE

nvl(wdo.op_yield_factor, 1.0)

END op_yield /* set default to 1 */

FROM

wis_wd_operations_b wdo,

wis_standard_operations_b stdo,

wis_wd_versions wdv,

wm,

wis_work_definitions wd,

wis_wd_op_dependencies opdep

WHERE

wdv.work_definition_id = wdo.work_definition_id

AND wdo.standard_operation_id = stdo.standard_operation_id (+)

AND wdv.work_definition_id = wd.work_definition_id

AND wd.enable_parallel_ops_flag = 'Y' /* Only for enforce dep WDs. */

AND wd.work_method_id = wm.work_method_id /* Only for Process Work Methods */

AND wdo.work_definition_id = opdep.work_definition_id

AND wdv.work_definition_version_id = opdep.work_definition_version_id

AND wdo.wd_operation_id = nvl(opdep.next_wd_operation_id, opdep.previous_wd_operation_id)

AND opdep.dependency_type = 'ORA_FINISH_TO_START' /* Include only Finish to Start deps */

), wd_op_dep_a AS (

SELECT /*+ materialize */

*

FROM

wd_op_dep

WHERE

curr_op IS NOT NULL

), branches_rec (

wd_id,

wd_ver_id,

version_number,

prev_op,

curr_op,

operation_seq_number,

transfer_pct,

op_yield,

net_yld,

net_transfer_pct

) AS (

SELECT

wd_id,

wd_ver_id,

version_number,

prev_op,

curr_op,

operation_seq_number,

transfer_pct,

op_yield,

ln(transfer_pct / 100) + ln(op_yield) AS net_yld,

ln(transfer_pct / 100) AS net_transfer_pct

FROM

wd_op_dep_a

WHERE

prev_op IS NULL /* starting point */

UNION ALL

SELECT /* leading(dep b) */

dep.wd_id,

dep.wd_ver_id,

dep.version_number,

dep.prev_op,

dep.curr_op,

dep.operation_seq_number,

dep.transfer_pct,

dep.op_yield,

b.net_yld + ln(dep.transfer_pct / 100) + ln(dep.op_yield) AS net_yld,

b.net_transfer_pct + ln(dep.transfer_pct / 100) AS net_transfer_pct

FROM

wd_op_dep_a dep,

branches_rec b

WHERE

b.wd_id = dep.wd_id

AND b.wd_ver_id = dep.wd_ver_id

AND b.curr_op = dep.prev_op

)

CYCLE wd_id, wd_ver_id, curr_op SET is_cycle TO 1 DEFAULT 0, fwd_yields AS (

SELECT

wd_id,

wd_ver_id,

version_number,

curr_op,

operation_seq_number,

op_yield,

SUM(exp(net_yld)) AS fwd_yld,

SUM(exp(net_transfer_pct)) AS fwd_transfer_pct

FROM

branches_rec

GROUP BY

wd_id,

wd_ver_id,

version_number,

curr_op,

operation_seq_number,

op_yield

), final_yield AS (

SELECT

fy.wd_id,

fy.wd_ver_id,

fy.curr_op,

fy.fwd_yld AS net_yield

FROM

fwd_yields fy,

(

SELECT

wd_id,

wd_ver_id,

prev_op

FROM

wd_op_dep

WHERE

curr_op IS NULL

) depops

WHERE

depops.wd_id = fy.wd_id

AND depops.wd_ver_id = fy.wd_ver_id

AND depops.prev_op = fy.curr_op

)

SELECT

fw.curr_op wd_operation_id,

fw.operation_seq_number operation_seq_number,

fw.wd_id work_definition_id,

fw.wd_ver_id work_definition_version_id,

fw.version_number version_number,

fw.op_yield op_yield_factor,

round(fw.fwd_yld, 30) cumulative_yield,

round((fy.net_yield / nvl(fw.fwd_yld, 1.0)) * fw.op_yield,

30) rev_cumulative_yield,

round(fw.fwd_transfer_pct * 100, 30) cumulative_transfer_pct,

round((fw.fwd_yld / nvl((fw.op_yield * fw.fwd_transfer_pct), 1.0)),

30) input_scale_factor,

round((fw.fwd_yld / nvl(fw.fwd_transfer_pct, 1.0)),

30) output_scale_factor

FROM

fwd_yields fw,

final_yield fy

WHERE

fw.wd_id = fy.wd_id

AND fw.wd_ver_id = fy.wd_ver_id

ORDER BY

fw.wd_id,

fw.wd_ver_id,

fw.curr_op