WIP Discrete Job Value Report - Standard Costing

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