WIP Repetitive Value

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: Repetitive Value Report (XML)
Short Name: WIPREVAL_XML
DB package: WIP_WIPREVAL_XMLP_PKG
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
Parameter Name SQL text Validation
Quantity Precision
 
Number
Organization Id
 
Number
To
 
LOV Oracle
Classes From
 
LOV Oracle
To
 
LOV Oracle
Lines From
 
LOV Oracle
To
 
LOV Oracle
Assemblies From
 
LOV Oracle
Period Close Date
 
LOV Oracle
Period Start Date
 
LOV Oracle
Include Supplier
 
LOV Oracle
Include Bulk
 
LOV Oracle
Report Type
 
LOV Oracle