WIP Repetitive Value

select wor.wip_entity_id res_entity_id,
       wor.organization_id res_org_id,
       wor.operation_seq_num oper_seq_num,
       bd.department_code department,
       br.resource_code res_code,
       br.unit_of_measure res_uom,
       ml_basis.meaning basis,
       ml_autocharge.meaning autocharge,
       ml_std_rate.meaning std_rate,
       round(decode(wor.BASIS_TYPE, 1,
           decode(:REPORT_RUN_OPT,
             2, (wor.USAGE_RATE_OR_AMOUNT * wtr.key6),
             3, (wor.USAGE_RATE_OR_AMOUNT * wtr.key4),
             0),
             decode(:REPORT_RUN_OPT,
             2, (wor.usage_rate_or_amount * (decode(wtr.key6,0,0,1))),
             3, (wor.usage_rate_or_amount * (decode(wtr.key4,0,0,1))),
             0)), :P_qty_precision)    std_res_units,
       round(sum(NVL(wta.PRIMARY_QUANTITY,0)), :P_qty_precision)
                                                             app_res_units,
       round(decode(wor.BASIS_TYPE, 1,
           decode(:REPORT_RUN_OPT,
             2, (wor.USAGE_RATE_OR_AMOUNT * wtr.key6),
             3, (wor.USAGE_RATE_OR_AMOUNT * wtr.key4),
             0),
             decode(:REPORT_RUN_OPT,
             2, (wor.usage_rate_or_amount * (decode(wtr.key6,0,0,1))),
             3, (wor.usage_rate_or_amount * (decode(wtr.key4,0,0,1))),
             0)) * decode(br.functional_currency_flag, 1, 1, 
                   NVL(crc.resource_rate,0)), :precision)    res_std_cost,
       round(sum(NVL(wta.PRIMARY_QUANTITY,0) * 
         decode(wor.STANDARD_RATE_FLAG,1,NVL(wt.standard_resource_rate, 0),
            NVL(wt.ACTUAL_RESOURCE_RATE,0))), :precision) res_app_cost,
       round(sum(NVL(wta.PRIMARY_QUANTITY,0) *
         decode(wor.STANDARD_RATE_FLAG,1,NVL(wt.standard_resource_rate, 0), 
            NVL(wt.ACTUAL_RESOURCE_RATE,0))) -
       (decode(wor.BASIS_TYPE, 1,
           decode(:REPORT_RUN_OPT,
             2, (wor.USAGE_RATE_OR_AMOUNT * wtr.key6),
             3, (wor.USAGE_RATE_OR_AMOUNT * wtr.key4),
             0),
            decode(:REPORT_RUN_OPT,
             2, (wor.usage_rate_or_amount * (decode(wtr.key6,0,0,1))),
             3, (wor.usage_rate_or_amount * (decode(wtr.key4,0,0,1))),
             0)) * decode(br.functional_currency_flag, 1, 1, 
                   NVL(crc.resource_rate,0))), :precision)    eff_var,
       wtr.key1 res_line_id,
       wor.repetitive_schedule_id,
       wor.resource_seq_num
from   
       mfg_lookups ml_basis,
       mfg_lookups ml_autocharge,
       mfg_lookups ml_std_rate,
       wip_operations wo,
       wip_operation_resources wor,
       wip_transactions wt,
       wip_txn_allocations wta,
       wip_temp_reports wtr,
       cst_resource_costs crc,
       bom_resources br,
       bom_departments bd
where 
       wtr.wip_entity_id = :wip_entity_id
and    wtr.organization_id = :org_id
and    wtr.program_source = 'WIPREVAL'
and    wor.repetitive_schedule_id = wtr.key2
and    wt.wip_entity_id = :wip_entity_id
and    wt.organization_id = :org_id
and    wt.TRANSACTION_TYPE  in (1, 3) 
and    wt.operation_seq_num = wor.operation_seq_num
and    wt.resource_seq_num = wor.resource_seq_num
and    wt.resource_id = wor.resource_id
and    wt.line_id = :line_id
and    wt.transaction_date >=:FROM_PERIOD_START_DATE 
and    wt.transaction_date  < :TO_PERIOD_END_DATE + 1
and    wta.transaction_id = wt.transaction_id 
and    wta.repetitive_schedule_id = wor.repetitive_schedule_id
and    wta.organization_id = :org_id
and    wo.operation_seq_num = wor.operation_seq_num
and    wo.wip_entity_id = :wip_entity_id
and    wo.organization_id = :org_id
and    wo.repetitive_schedule_id = wor.repetitive_schedule_id
and    bd.department_id = nvl(wor.department_id, wo.department_id)
and    br.resource_id = wor.resource_id
and    crc.resource_id (+) = wor.resource_id
and    crc.cost_type_id (+) = 1
and    ml_basis.lookup_code = wor.basis_type
and    ml_basis.lookup_type = 'CST_BASIS'
and    ml_autocharge.lookup_code = wor.autocharge_type
and    ml_autocharge.lookup_type = 'BOM_AUTOCHARGE_TYPE'
and    ml_std_rate.lookup_code = wor.STANDARD_RATE_FLAG
and    ml_std_rate.lookup_type = 'SYS_YES_NO'
and    wor.organization_id = :org_id
group by wor.OPERATION_SEQ_NUM,
         wor.resource_seq_num,
         wor.repetitive_schedule_id,
         wor.wip_entity_id,
         wtr.key1,
         wor.STANDARD_RATE_FLAG,
         wor.organization_id,
         bd.department_code,
         br.resource_code,
         br.unit_of_measure,
         ml_basis.meaning,
         ml_autocharge.meaning,
         ml_std_rate.meaning,
         wor.USAGE_RATE_OR_AMOUNT,
         wor.BASIS_TYPE, 
         crc.resource_rate,
         wtr.key6,
         wtr.key4,
         br.functional_currency_flag
UNION
select wor.wip_entity_id res_entity_id,
       wor.organization_id res_org_id,
       wor.operation_seq_num oper_seq_num,
       bd.department_code department,
       br.resource_code res_code,
       br.unit_of_measure res_uom,
       ml_basis.meaning basis,
       ml_autocharge.meaning autocharge,
       ml_std_rate.meaning std_rate,
       round(decode(wor.BASIS_TYPE, 1,
           decode(:REPORT_RUN_OPT,
             2, (wor.USAGE_RATE_OR_AMOUNT * wtr.key6),
             3, (wor.USAGE_RATE_OR_AMOUNT * wtr.key4),
             0),
             decode(:REPORT_RUN_OPT,
             2, (wor.usage_rate_or_amount * (decode(wtr.key6,0,0,1))),
             3, (wor.usage_rate_or_amount * (decode(wtr.key4,0,0,1))),
             0)), :P_qty_precision)  std_res_units,
       0                                               app_res_units,
       round(decode(wor.BASIS_TYPE, 1,
           decode(:REPORT_RUN_OPT,
             2, (wor.USAGE_RATE_OR_AMOUNT * wtr.key6),
             3, (wor.USAGE_RATE_OR_AMOUNT * wtr.key4),
             0),
             decode(:REPORT_RUN_OPT,
             2, (wor.usage_rate_or_amount * (decode(wtr.key6,0,0,1))),
             3, (wor.usage_rate_or_amount * (decode(wtr.key4,0,0,1))),
             0)) * decode(br.functional_currency_flag, 1, 1, 
                   NVL(crc.resource_rate,0)), :precision)    res_std_cost,
       0                                                 res_app_cost,
       0 -
       round(decode(wor.BASIS_TYPE, 1,
           decode(:REPORT_RUN_OPT,
             2, (wor.USAGE_RATE_OR_AMOUNT * wtr.key6),
             3, (wor.USAGE_RATE_OR_AMOUNT * wtr.key4),
             0),
            decode(:REPORT_RUN_OPT,
             2, (wor.usage_rate_or_amount * (decode(wtr.key6,0,0,1))),
             3, (wor.usage_rate_or_amount * (decode(wtr.key4,0,0,1))),
             0)) * decode(br.functional_currency_flag, 1, 1, 
                   NVL(crc.resource_rate,0)), :precision)     eff_var,
       wtr.key1 res_line_id,
       wor.repetitive_schedule_id,
       wor.resource_seq_num
from   
       mfg_lookups ml_basis,
       mfg_lookups ml_autocharge,
       mfg_lookups ml_std_rate,
       wip_operations wo,
       wip_operation_resources wor,
       wip_temp_reports wtr,
       cst_resource_costs crc,
       bom_resources br,
       bom_departments bd
where 
       wtr.wip_entity_id = :wip_entity_id
and    wtr.organization_id = :org_id
and    wtr.program_source = 'WIPREVAL'
and    wor.repetitive_schedule_id = wtr.key2
and    not exists
       (select 'x' from 
       wip_transactions wt,
       wip_txn_allocations wta
       where wt.wip_entity_id = :wip_entity_id
       and    wt.organization_id = :org_id
       and    wt.TRANSACTION_TYPE in (1,3) 
       and    wt.operation_seq_num = wor.operation_seq_num
       and    wt.resource_seq_num = wor.resource_seq_num
       and    wt.resource_id = wor.resource_id
       and    wt.line_id = :line_id
       and    wt.transaction_date >= :FROM_PERIOD_START_DATE
       and    wt.transaction_date < :TO_PERIOD_END_DATE + 1
       and    wta.transaction_id = wt.transaction_id 
       and    wta.repetitive_schedule_id = wor.repetitive_schedule_id
       and    wta.organization_id = :org_id)
and    wo.operation_seq_num = wor.operation_seq_num
and    wo.wip_entity_id = :wip_entity_id
and    wo.organization_id = :org_id
and    wo.repetitive_schedule_id = wor.repetitive_schedule_id
and    bd.department_id = nvl(wor.department_id, wo.department_id)
and    br.resource_id = wor.resource_id
and    crc.resource_id (+) = wor.resource_id
and    crc.cost_type_id (+) = 1
and    ml_basis.lookup_code = wor.basis_type
and    ml_basis.lookup_type = 'CST_BASIS'
and    ml_autocharge.lookup_code = wor.autocharge_type
and    ml_autocharge.lookup_type = 'BOM_AUTOCHARGE_TYPE'
and    ml_std_rate.lookup_code = wor.STANDARD_RATE_FLAG
and    ml_std_rate.lookup_type = 'SYS_YES_NO'
and    wor.organization_id = :org_id 
 and wor.wip_entity_id=:wip_entity_id 
 and wtr.key1=:line_id
group by wor.OPERATION_SEQ_NUM,
         wor.resource_seq_num,
         wor.repetitive_schedule_id,
         wor.wip_entity_id,
         wtr.key1,
         wor.STANDARD_RATE_FLAG,
         wor.organization_id,
         bd.department_code,
         br.resource_code,
         br.unit_of_measure,
         ml_basis.meaning,
         ml_autocharge.meaning,
         ml_std_rate.meaning,
         wor.USAGE_RATE_OR_AMOUNT,
         wor.BASIS_TYPE, 
         crc.resource_rate,
         wtr.key6,
         wtr.key4,
         br.functional_currency_flag
order by 3,4,5