WIS_WD_OP_YIELD

Details

Schema: FUSION
Object owner: WIS
Object type: VIEW

Columns

Name
WD_OPERATION_ID OPERATION_SEQ_NUMBER WORK_DEFINITION_ID VERSION_NUMBER OP_YIELD_FACTOR CUMULATIVE_YIELD REV_CUMULATIVE_YIELD

Name

WD_OPERATION_ID

OPERATION_SEQ_NUMBER

WORK_DEFINITION_ID

VERSION_NUMBER

OP_YIELD_FACTOR

CUMULATIVE_YIELD

REV_CUMULATIVE_YIELD

Query

SQL_Statement
WITH cum_yield_parent_child ( wd_operation_id, operation_seq_number, work_definition_id, version_number, op_yield_factor, cumulative_yield ) AS ( SELECT wdo.wd_operation_id, wdo.operation_seq_number, wdo.work_definition_id, wdv.version_number, CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS op_yield_factor, CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS cumulative_yield FROM wis_wd_op_dependencies wopd, wis_wd_operations_b wdo, wis_wd_versions wdv, wis_standard_operations_b stdo, wis_work_definitions wwd, wis_work_methods_b wwm WHERE wopd.next_wd_operation_id = wdo.wd_operation_id AND wdo.work_definition_id = wdv.work_definition_id AND wdv.work_definition_id = wwd.work_definition_id AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID' AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y' AND wwd.work_method_id = wwm.work_method_id AND wwm.work_method_code = 'DISCRETE_MANUFACTURING' AND wdo.standard_operation_id = stdo.standard_operation_id (+) AND wopd.previous_wd_operation_id IS NULL AND wopd.next_wd_operation_id IS NOT NULL AND wdo.operation_type = 'IN_HOUSE' AND wdv.effective_from_date BETWEEN wdo.effective_from_date AND NVL(wdo.effective_to_date, wdv.effective_from_date) AND ( SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1) OR wdv.effective_from_date >= SYSTIMESTAMP ) AND 'Y' = (CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.count_point_flag ELSE wdo.count_point_flag END) UNION ALL SELECT child.wd_operation_id, child.operation_seq_number, child.work_definition_id, wdv.version_number, CASE WHEN child.referenced_flag = 'Y' THEN stdchild.op_yield_factor ELSE child.op_yield_factor END AS op_yield_factor, (CASE WHEN child.referenced_flag = 'Y' THEN stdchild.op_yield_factor ELSE child.op_yield_factor END) * parent.cumulative_yield AS cumulative_yield FROM cum_yield_parent_child parent, wis_wd_op_dependencies wopd, wis_wd_operations_b child, wis_wd_versions wdv, wis_standard_operations_b stdchild, wis_work_definitions wwd, wis_work_methods_b wwm WHERE parent.wd_operation_id = wopd.previous_wd_operation_id AND wopd.next_wd_operation_id = child.wd_operation_id AND child.work_definition_id = wdv.work_definition_id AND wdv.work_definition_id = wwd.work_definition_id AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID' AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y' AND wwd.work_method_id = wwm.work_method_id AND wwm.work_method_code = 'DISCRETE_MANUFACTURING' AND child.standard_operation_id = stdchild.standard_operation_id (+) AND child.operation_type = 'IN_HOUSE' AND wdv.effective_from_date BETWEEN child.effective_from_date AND NVL(child.effective_to_date, wdv.effective_from_date) AND ( SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1) OR wdv.effective_from_date >= SYSTIMESTAMP ) AND 'Y' = (CASE WHEN child.referenced_flag = 'Y' THEN stdchild.count_point_flag ELSE child.count_point_flag END) ), rev_cum_yield_child_parent ( wd_operation_id, operation_seq_number, work_definition_id, version_number, op_yield_factor, rev_cumulative_yield ) AS ( SELECT wdo.wd_operation_id, wdo.operation_seq_number, wdo.work_definition_id, wdv.version_number, CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS op_yield_factor, CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS rev_cumulative_yield FROM wis_wd_op_dependencies wopd, wis_wd_operations_b wdo, wis_wd_versions wdv, wis_standard_operations_b stdo, wis_work_definitions wwd, wis_work_methods_b wwm WHERE wopd.previous_wd_operation_id = wdo.wd_operation_id AND wdo.work_definition_id = wdv.work_definition_id AND wdv.work_definition_id = wwd.work_definition_id AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID' AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y' AND wwd.work_method_id = wwm.work_method_id AND wwm.work_method_code = 'DISCRETE_MANUFACTURING' AND wdo.standard_operation_id = stdo.standard_operation_id (+) AND wopd.next_wd_operation_id IS NULL AND wopd.previous_wd_operation_id IS NOT NULL AND wdo.operation_type = 'IN_HOUSE' AND wdv.effective_from_date BETWEEN wdo.effective_from_date AND NVL(wdo.effective_to_date, wdv.effective_from_date) AND ( SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1) OR wdv.effective_from_date >= SYSTIMESTAMP ) AND 'Y' = ( CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.count_point_flag ELSE wdo.count_point_flag END ) UNION ALL SELECT parent.wd_operation_id, parent.operation_seq_number, parent.work_definition_id, wdv.version_number, CASE WHEN parent.referenced_flag = 'Y' THEN stdparent.op_yield_factor ELSE parent.op_yield_factor END AS op_yield_factor, (CASE WHEN parent.referenced_flag = 'Y' THEN stdparent.op_yield_factor ELSE parent.op_yield_factor END) * child.rev_cumulative_yield AS rev_cumulative_yield FROM rev_cum_yield_child_parent child, wis_wd_op_dependencies wopd, wis_wd_operations_b parent, wis_wd_versions wdv, wis_standard_operations_b stdparent, wis_work_definitions wwd, wis_work_methods_b wwm WHERE wopd.next_wd_operation_id = child.wd_operation_id AND wopd.previous_wd_operation_id = parent.wd_operation_id AND parent.work_definition_id = wdv.work_definition_id AND wdv.work_definition_id = wwd.work_definition_id AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID' AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y' AND wwd.work_method_id = wwm.work_method_id AND wwm.work_method_code = 'DISCRETE_MANUFACTURING' AND parent.standard_operation_id = stdparent.standard_operation_id (+) AND parent.operation_type = 'IN_HOUSE' AND wdv.effective_from_date BETWEEN parent.effective_from_date AND NVL(parent.effective_to_date, wdv.effective_from_date) AND ( SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1) OR wdv.effective_from_date >= SYSTIMESTAMP ) AND 'Y' = ( CASE WHEN parent.referenced_flag = 'Y' THEN stdparent.count_point_flag ELSE parent.count_point_flag END ) ) SELECT cum_yield.wd_operation_id, MIN(cum_yield.operation_seq_number) AS operation_seq_number, MIN(cum_yield.work_definition_id) AS work_definition_id, MIN(cum_yield.version_number) AS version_number, MIN(cum_yield.op_yield_factor) AS op_yield_factor, ROUND(MIN(cum_yield.cumulative_yield), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) AS cumulative_yield, ROUND(MIN(rev_cum_yield.rev_cumulative_yield), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) AS rev_cumulative_yield FROM cum_yield_parent_child cum_yield, rev_cum_yield_child_parent rev_cum_yield WHERE cum_yield.wd_operation_id = rev_cum_yield.wd_operation_id GROUP BY cum_yield.wd_operation_id UNION ALL SELECT wdo.wd_operation_id, wdo.operation_seq_number, wdo.work_definition_id, wdv.version_number, case when wdo.referenced_flag='Y' then stdo.op_yield_factor else wdo.op_yield_factor end op_yield_factor, round(exp(SUM(ln(case when wdo.referenced_flag='Y' then stdo.op_yield_factor else wdo.op_yield_factor end)) OVER(PARTITION BY wdo.work_definition_id, wdv.version_number ORDER BY operation_seq_number)), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) cumulative_yield, round(exp(SUM(ln(case when wdo.referenced_flag='Y' then stdo.op_yield_factor else wdo.op_yield_factor end)) OVER(PARTITION BY wdo.work_definition_id, wdv.version_number ORDER BY operation_seq_number DESC)), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) rev_cumulative_yield FROM wis_wd_operations_b wdo, wis_standard_operations_b stdo, wis_wd_versions wdv, wis_work_definitions wwd WHERE wdv.work_definition_id = wdo.work_definition_id AND wdv.work_definition_id = wwd.work_definition_id AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'N' AND wdv.effective_from_date between wdo.effective_from_date and nvl(wdo.effective_to_date, wdv.effective_from_date) AND (systimestamp between wdv.effective_from_date and nvl(wdv.effective_to_date,sysdate+1) OR wdv.effective_from_date >= systimestamp) AND wdo.standard_operation_id = stdo.standard_operation_id(+) AND wdo.operation_type = 'IN_HOUSE' AND 'Y' = (case when wdo.referenced_flag='Y' then stdo.count_point_flag else wdo.count_point_flag end)

SQL_Statement

WITH cum_yield_parent_child (

wd_operation_id,

operation_seq_number,

work_definition_id,

version_number,

op_yield_factor,

cumulative_yield

) AS (

SELECT

wdo.wd_operation_id,

wdo.operation_seq_number,

wdo.work_definition_id,

wdv.version_number,

CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS op_yield_factor,

CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS cumulative_yield

FROM

wis_wd_op_dependencies wopd,

wis_wd_operations_b wdo,

wis_wd_versions wdv,

wis_standard_operations_b stdo,

wis_work_definitions wwd,

wis_work_methods_b wwm

WHERE wopd.next_wd_operation_id = wdo.wd_operation_id

AND wdo.work_definition_id = wdv.work_definition_id

AND wdv.work_definition_id = wwd.work_definition_id

AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID'

AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y'

AND wwd.work_method_id = wwm.work_method_id

AND wwm.work_method_code = 'DISCRETE_MANUFACTURING'

AND wdo.standard_operation_id = stdo.standard_operation_id (+)

AND wopd.previous_wd_operation_id IS NULL

AND wopd.next_wd_operation_id IS NOT NULL

AND wdo.operation_type = 'IN_HOUSE'

AND wdv.effective_from_date BETWEEN wdo.effective_from_date AND NVL(wdo.effective_to_date, wdv.effective_from_date)

AND (

SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1)

OR wdv.effective_from_date >= SYSTIMESTAMP

)

AND 'Y' = (CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.count_point_flag ELSE wdo.count_point_flag END)

UNION ALL

SELECT

child.wd_operation_id,

child.operation_seq_number,

child.work_definition_id,

wdv.version_number,

CASE WHEN child.referenced_flag = 'Y' THEN stdchild.op_yield_factor ELSE child.op_yield_factor END AS op_yield_factor,

(CASE WHEN child.referenced_flag = 'Y' THEN stdchild.op_yield_factor ELSE child.op_yield_factor END)

* parent.cumulative_yield AS cumulative_yield

FROM

cum_yield_parent_child parent,

wis_wd_op_dependencies wopd,

wis_wd_operations_b child,

wis_wd_versions wdv,

wis_standard_operations_b stdchild,

wis_work_definitions wwd,

wis_work_methods_b wwm

WHERE parent.wd_operation_id = wopd.previous_wd_operation_id

AND wopd.next_wd_operation_id = child.wd_operation_id

AND child.work_definition_id = wdv.work_definition_id

AND wdv.work_definition_id = wwd.work_definition_id

AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID'

AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y'

AND wwd.work_method_id = wwm.work_method_id

AND wwm.work_method_code = 'DISCRETE_MANUFACTURING'

AND child.standard_operation_id = stdchild.standard_operation_id (+)

AND child.operation_type = 'IN_HOUSE'

AND wdv.effective_from_date BETWEEN child.effective_from_date AND NVL(child.effective_to_date, wdv.effective_from_date)

AND (

SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1)

OR wdv.effective_from_date >= SYSTIMESTAMP

)

AND 'Y' = (CASE WHEN child.referenced_flag = 'Y' THEN stdchild.count_point_flag ELSE child.count_point_flag END)

),

rev_cum_yield_child_parent (

wd_operation_id,

operation_seq_number,

work_definition_id,

version_number,

op_yield_factor,

rev_cumulative_yield

) AS (

SELECT

wdo.wd_operation_id,

wdo.operation_seq_number,

wdo.work_definition_id,

wdv.version_number,

CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS op_yield_factor,

CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.op_yield_factor ELSE wdo.op_yield_factor END AS rev_cumulative_yield

FROM

wis_wd_op_dependencies wopd,

wis_wd_operations_b wdo,

wis_wd_versions wdv,

wis_standard_operations_b stdo,

wis_work_definitions wwd,

wis_work_methods_b wwm

WHERE

wopd.previous_wd_operation_id = wdo.wd_operation_id

AND wdo.work_definition_id = wdv.work_definition_id

AND wdv.work_definition_id = wwd.work_definition_id

AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID'

AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y'

AND wwd.work_method_id = wwm.work_method_id

AND wwm.work_method_code = 'DISCRETE_MANUFACTURING'

AND wdo.standard_operation_id = stdo.standard_operation_id (+)

AND wopd.next_wd_operation_id IS NULL

AND wopd.previous_wd_operation_id IS NOT NULL

AND wdo.operation_type = 'IN_HOUSE'

AND wdv.effective_from_date BETWEEN wdo.effective_from_date AND NVL(wdo.effective_to_date, wdv.effective_from_date)

AND (

SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1)

OR wdv.effective_from_date >= SYSTIMESTAMP

)

AND 'Y' = (

CASE WHEN wdo.referenced_flag = 'Y' THEN stdo.count_point_flag ELSE wdo.count_point_flag END

)

UNION ALL

SELECT

parent.wd_operation_id,

parent.operation_seq_number,

parent.work_definition_id,

wdv.version_number,

CASE WHEN parent.referenced_flag = 'Y' THEN stdparent.op_yield_factor ELSE parent.op_yield_factor END AS op_yield_factor,

(CASE WHEN parent.referenced_flag = 'Y' THEN stdparent.op_yield_factor ELSE parent.op_yield_factor END)

* child.rev_cumulative_yield AS rev_cumulative_yield

FROM

rev_cum_yield_child_parent child,

wis_wd_op_dependencies wopd,

wis_wd_operations_b parent,

wis_wd_versions wdv,

wis_standard_operations_b stdparent,

wis_work_definitions wwd,

wis_work_methods_b wwm

WHERE

wopd.next_wd_operation_id = child.wd_operation_id

AND wopd.previous_wd_operation_id = parent.wd_operation_id

AND parent.work_definition_id = wdv.work_definition_id

AND wdv.work_definition_id = wwd.work_definition_id

AND NVL(wdv.parallel_ops_status_code, 'VALID') = 'VALID'

AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'Y'

AND wwd.work_method_id = wwm.work_method_id

AND wwm.work_method_code = 'DISCRETE_MANUFACTURING'

AND parent.standard_operation_id = stdparent.standard_operation_id (+)

AND parent.operation_type = 'IN_HOUSE'

AND wdv.effective_from_date BETWEEN parent.effective_from_date AND NVL(parent.effective_to_date, wdv.effective_from_date)

AND (

SYSTIMESTAMP BETWEEN wdv.effective_from_date AND NVL(wdv.effective_to_date, SYSDATE+1)

OR wdv.effective_from_date >= SYSTIMESTAMP

)

AND 'Y' = (

CASE WHEN parent.referenced_flag = 'Y' THEN stdparent.count_point_flag ELSE parent.count_point_flag END

)

SELECT

cum_yield.wd_operation_id,

MIN(cum_yield.operation_seq_number) AS operation_seq_number,

MIN(cum_yield.work_definition_id) AS work_definition_id,

MIN(cum_yield.version_number) AS version_number,

MIN(cum_yield.op_yield_factor) AS op_yield_factor,

ROUND(MIN(cum_yield.cumulative_yield), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) AS cumulative_yield,

ROUND(MIN(rev_cum_yield.rev_cumulative_yield), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) AS rev_cumulative_yield

FROM cum_yield_parent_child cum_yield, rev_cum_yield_child_parent rev_cum_yield WHERE cum_yield.wd_operation_id = rev_cum_yield.wd_operation_id

GROUP BY cum_yield.wd_operation_id

UNION ALL

SELECT wdo.wd_operation_id,

wdo.operation_seq_number,

wdo.work_definition_id,

wdv.version_number,

case when wdo.referenced_flag='Y' then stdo.op_yield_factor else wdo.op_yield_factor end op_yield_factor,

round(exp(SUM(ln(case when wdo.referenced_flag='Y' then stdo.op_yield_factor else wdo.op_yield_factor end)) OVER(PARTITION BY wdo.work_definition_id, wdv.version_number ORDER BY operation_seq_number)), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) cumulative_yield,

round(exp(SUM(ln(case when wdo.referenced_flag='Y' then stdo.op_yield_factor else wdo.op_yield_factor end)) OVER(PARTITION BY wdo.work_definition_id, wdv.version_number ORDER BY operation_seq_number DESC)), fnd_profile.value('INV_QUANTITY_DECIMAL_PRECISION')) rev_cumulative_yield

FROM wis_wd_operations_b wdo,

wis_standard_operations_b stdo,

wis_wd_versions wdv,

wis_work_definitions wwd

WHERE wdv.work_definition_id = wdo.work_definition_id

AND wdv.work_definition_id = wwd.work_definition_id

AND NVL(wwd.ENABLE_PARALLEL_OPS_FLAG,'N') = 'N'

AND wdv.effective_from_date between wdo.effective_from_date and nvl(wdo.effective_to_date, wdv.effective_from_date)

AND (systimestamp between wdv.effective_from_date and nvl(wdv.effective_to_date,sysdate+1)

OR

wdv.effective_from_date >= systimestamp)

AND wdo.standard_operation_id = stdo.standard_operation_id(+)

AND wdo.operation_type = 'IN_HOUSE'

AND 'Y' = (case when wdo.referenced_flag='Y' then stdo.count_point_flag else wdo.count_point_flag end)