WIP Discrete Job Value Report - Standard Costing

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: Discrete Job Value Report - Standard Costing (XML)
Short Name: WIPRDJVR_XML
DB package: WIP_WIPRDJVR_XMLP_PKG
select 
       wdj.wip_entity_id ovr_res_entity_id, 
       wdj.organization_id ovr_res_org_id,
       wor.operation_seq_num oper_seq_num,
       bd.department_code department,
       bro.resource_code res_code, 
       brr.resource_code resource_code,
       ml_basis.meaning basis,
       cdo.rate_or_amount std_rate,
       round(sum(decode(cdo.basis_type,
              3, wor.USAGE_RATE_OR_AMOUNT * 
                  decode(wor.BASIS_TYPE, 1,
                     decode(:REPORT_RUN_OPT,
                           2, wdj.start_quantity, 
                           3, wdj.quantity_completed,
                           0),
                        1),
              4, wor.USAGE_RATE_OR_AMOUNT *
                  decode(wor.BASIS_TYPE, 1,
                     decode(:REPORT_RUN_OPT,
                           2, wdj.start_quantity, 
                           3, wdj.quantity_completed,
                           0),
                        1) * decode(brr.functional_currency_flag, 1, 1, 
                                     NVL(crc.resource_rate,0)),
              0)), :P_qty_precision)                           std_bas_fctr,
       round(sum(decode(cdo.basis_type,
              3, wor.applied_resource_units,
              4, wor.applied_resource_value,
              0)), :P_qty_precision)                           app_bas_fctr,
       round(sum(cdo.rate_or_amount * 
             decode(cdo.basis_type,
              3, wor.USAGE_RATE_OR_AMOUNT *           
                  decode(wor.BASIS_TYPE, 1,
                     decode(:REPORT_RUN_OPT,
                           2, wdj.start_quantity, 
                           3, wdj.quantity_completed,
                           0),    
                        1),
              4, wor.USAGE_RATE_OR_AMOUNT *
                  decode(wor.BASIS_TYPE, 1,
                     decode(:REPORT_RUN_OPT,
                           2, wdj.start_quantity, 
                           3, wdj.quantity_completed,
                           0),    
                        1) * decode(brr.functional_currency_flag, 1, 1, 
                                     NVL(crc.resource_rate,0)),
              0)), :precision)                             res_ovr_std_cost,
       round(sum(cdo.rate_or_amount *
             decode(cdo.basis_type,
              3, wor.applied_resource_units,
              4, wor.applied_resource_value,
              0)), :precision)                             res_ovr_app_cost,
       round(sum(cdo.rate_or_amount *
             decode(cdo.basis_type,
             3, wor.applied_resource_units,
             4, wor.applied_resource_value,
            0) -
       (cdo.rate_or_amount *
            decode(cdo.basis_type,
              3, wor.USAGE_RATE_OR_AMOUNT *
                  decode(wor.BASIS_TYPE, 1,
                     decode(:REPORT_RUN_OPT,
                           2, wdj.start_quantity, 
                           3, wdj.quantity_completed,
                           0),
                        1),
              4, wor.USAGE_RATE_OR_AMOUNT *
                 decode(wor.BASIS_TYPE, 1,
                     decode(:REPORT_RUN_OPT,
                           2, wdj.start_quantity, 
                           3, wdj.quantity_completed,
                           0),
                        1) * decode(brr.functional_currency_flag, 1, 1, 
                                    NVL(crc.resource_rate,0)),
             0))), :precision)                              res_ovr_eff_var
from   
       cst_department_overheads cdo,
       cst_resource_overheads cro,
       bom_departments bd,
       cst_resource_costs crc,
       bom_resources bro,
       bom_resources brr,
       mfg_lookups ml_basis,
       wip_operation_resources wor,
       wip_operations wo,
       wip_discrete_jobs wdj
where 
       wo.wip_entity_id = wdj.wip_entity_id
and    wor.organization_id = :org_id
and    wo.wip_entity_id = wor.wip_entity_id
and    wo.organization_id = :org_id
and    wo.operation_seq_num = wor.operation_seq_num
and    &P_PROJECT_WHERE 
and    bd.department_id = nvl(wor.department_id, wo.department_id)
and    brr.resource_id = wor.resource_id
and    crc.resource_id(+) = wor.resource_id
and    crc.cost_type_id(+) = 1
and    cro.resource_id = wor.resource_id
and    cro.cost_type_id = 1
and    cro.overhead_id = cdo.overhead_id
and    cdo.department_id = bd.department_id
and    cdo.cost_type_id = 1
and    cdo.basis_type in (3, 4)
and    bro.resource_id = cdo.overhead_id
and    ml_basis.lookup_code = cdo.basis_type
and    ml_basis.lookup_type = 'CST_BASIS_SHORT' 
 and wdj.wip_entity_id=:parent_entity_id 
 and wdj.organization_id=:parent_org_id
group by wor.OPERATION_SEQ_NUM,
         wor.STANDARD_RATE_FLAG,
         wdj.wip_entity_id,
         wdj.organization_id,
         bd.department_code,
         bro.resource_code,
         bro.unit_of_measure,
         cdo.basis_type,
         cdo.rate_or_amount,
         brr.resource_code,
         ml_basis.meaning,
         wor.USAGE_RATE_OR_AMOUNT,
         wor.BASIS_TYPE, 
         wdj.START_QUANTITY,
         wdj.quantity_completed,   
         brr.functional_currency_flag,
         crc.resource_rate,
         wor.applied_resource_units,
         wor.applied_resource_value
Parameter Name SQL text Validation
Project_Dummy
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Submitted by
 
Quantity Precision
 
Number
Account Period Id
 
Number
Account Structure
 
Number
Organization Id
 
Number
Project Id
 
LOV Oracle
Cost Group To
 
LOV Oracle
Cost Group From
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
To
 
Assemblies From
 
Status
 
LOV Oracle
To
 
LOV Oracle
Jobs From
 
LOV Oracle
To
 
LOV Oracle
Classes From
 
LOV Oracle
Include Supplier
 
LOV Oracle
Include Bulk
 
LOV Oracle
Class Type
 
LOV Oracle
Report Type
 
LOV Oracle
Sort By
 
LOV Oracle