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
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 WHERE wdv.work_definition_id = wdo.work_definition_id 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

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

WHERE wdv.work_definition_id = wdo.work_definition_id

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)