WIP Repetitive Value

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Repetitive Value Report
Application: Work in Process
Source: Repetitive Value Report (XML)
Short Name: WIPREVAL_XML
DB package: WIP_WIPREVAL_XMLP_PKG
Run WIP Repetitive Value and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Report Type 
 
 LOV Oracle
Include Bulk 
 
 LOV Oracle
Include Supplier 
 
 LOV Oracle
Period Start Date 
 
 LOV Oracle
Period Close Date 
 
 LOV Oracle
Assemblies From 
 
 LOV Oracle
To 
 
 LOV Oracle
Lines From 
 
 LOV Oracle
To 2 
 
 LOV Oracle
Classes From 
 
 LOV Oracle
To 3 
 
 LOV Oracle
Download
Related Blitz Reports
CAC ICP PII WIP Material Usage Variance CAC Manufacturing Variance Detail CAC Manufacturing Variance Summary CAC Material Account Summary CAC ICP PII Material Account Summary CAC ICP PII Material Account Detail CAC Material Account Detail CAC WIP Resource Efficiency with Method and Lot Variances CAC WIP Resource Efficiency
Blitz Report™

Blitz Report™ provides multiple benefits: