WIP Expense Job Value

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: Expense Job Value Report (XML)
Short Name: WIPREJVR_XML
DB package: WIP_WIPREJVR_XMLP_PKG
select 
       wpb.wip_entity_id cum_entity_id,
       wpb.organization_id cum_organization_id,
       ml_elem_type.meaning elem_var_type,
       null acct_no,
       sum(round(decode(ml_elem_type.lookup_code,
            1, nvl(wpb.pl_material_in,0),
            2, nvl(wpb.pl_material_overhead_in,0),
            3, (nvl(wpb.pl_resource_in,0) + nvl(wpb.tl_resource_in,0)),
            4, (nvl(wpb.pl_outside_processing_in,0) + 
                  nvl(wpb.tl_outside_processing_in,0)),
            5, (nvl(wpb.pl_overhead_in,0) + nvl(wpb.tl_overhead_in,0)),
            NULL), :PRECISION))                                  costs_in,
       sum(round(decode(ml_elem_type.lookup_code, 
            1, (nvl(wpb.pl_material_out,0) + nvl(wpb.tl_material_out,0)),   
            2, (nvl(wpb.pl_material_overhead_out,0) + 
                  nvl(wpb.tl_material_overhead_out,0)), 
            3, (nvl(wpb.pl_resource_out,0) + nvl(wpb.tl_resource_out,0)), 
            4, (nvl(wpb.pl_outside_processing_out,0) + 
                  nvl(wpb.tl_outside_processing_out,0)), 
            5, (nvl(wpb.pl_overhead_out,0) + nvl(wpb.tl_overhead_out,0)), 
            NULL), :PRECISION))                                 costs_out,
       sum(round(decode(ml_elem_type.lookup_code,  
            1, (nvl(wpb.pl_material_var,0) + nvl(wpb.tl_material_var,0)),    
            2, (nvl(wpb.pl_material_overhead_var,0) + 
                  nvl(wpb.tl_material_overhead_var,0)),  
            3, (nvl(wpb.pl_resource_var,0) + nvl(wpb.tl_resource_var,0)),  
            4, (nvl(wpb.pl_outside_processing_var,0) + 
                  nvl(wpb.tl_outside_processing_var,0)),             
            5, (nvl(wpb.pl_overhead_var,0) + nvl(wpb.tl_overhead_var,0)),  
            NULL), :PRECISION))                                 costs_var,
       sum(round(decode(ml_elem_type.lookup_code,
            1, (nvl(wpb.pl_material_in,0) - 
                nvl(wpb.pl_material_out,0) - nvl(wpb.tl_material_out,0)),
            2, (nvl(wpb.pl_material_overhead_in,0) -
                nvl(wpb.pl_material_overhead_out,0) - 
                nvl(wpb.tl_material_overhead_out,0)),
            3, (nvl(wpb.pl_resource_in,0) + nvl(wpb.tl_resource_in,0) - 
                nvl(wpb.pl_resource_out,0) - nvl(wpb.tl_resource_out,0)),
            4, (nvl(wpb.pl_outside_processing_in,0) + 
                nvl(wpb.tl_outside_processing_in,0) -
                nvl(wpb.pl_outside_processing_out,0) - 
                nvl(wpb.tl_outside_processing_out,0)),
            5, (nvl(wpb.pl_overhead_in,0) + nvl(wpb.tl_overhead_in,0) -
                nvl(wpb.pl_overhead_out,0) - nvl(wpb.tl_overhead_out,0)),  
            NULL), :PRECISION))                                period_act,
       sum(round(decode(ml_elem_type.lookup_code,  
            6, nvl(wpb.tl_material_var,0) + 
               nvl(wpb.tl_material_overhead_var,0) +
               nvl(wpb.pl_material_var,0) +
               nvl(wpb.pl_material_overhead_var,0) + 
               nvl(wpb.pl_resource_var,0) + nvl(wpb.pl_overhead_var,0) +
               nvl(wpb.pl_outside_processing_var,0),
            7, nvl(wpb.tl_resource_var,0),
            8, nvl(wpb.tl_outside_processing_var,0),
            9, nvl(wpb.tl_overhead_var,0),
            NULL), :PRECISION))                               single_level, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield_acct', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD_ACCT1
from
     wip_period_balances wpb,
     wip_discrete_jobs wdj,
     gl_code_combinations gcc,
     mfg_lookups ml_elem_type
where 
      wpb.acct_period_id IN
              (select oap.acct_period_id
               from org_acct_periods oap
               where trunc(oap.period_start_date) >= :PER_START_DATE
               and   trunc(oap.schedule_close_date) <= :PER_SCHD_CLS_DATE
                and   oap.organization_id = wdj.organization_id)
and   wdj.wip_entity_id = wpb.wip_entity_id
and   wdj.organization_id = wpb.organization_id
and   ml_elem_type.lookup_type = 'WIP_ELEMENT_VAR_TYPE'
and   decode(ml_elem_type.lookup_code, 
             1, wdj.material_account,
             2, wdj.material_overhead_account,
             3, wdj.resource_account,
             4, wdj.outside_processing_account,
             5, wdj.overhead_account,             6, wdj.material_variance_account,
             7, wdj.resource_variance_account,
             8, wdj.outside_proc_variance_account,
             9, wdj.overhead_variance_account) = gcc.code_combination_id 
 and wpb.wip_entity_id=:parent_entity_id 
 and wpb.organization_id=:parent_org_id
group by wpb.wip_entity_id,
          wpb.organization_id,
         ml_elem_type.meaning,
         &P_FLEXDATA_ACCT,
         ml_elem_type.lookup_code,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield_acct', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
order by ml_elem_type.lookup_code
Parameter Name SQL text Validation
Submitted by
 
Quantity Precision
 
Number
Account Structure
 
Number
Organization Id
 
Number
To
 
Assemblies From
 
Status
 
LOV Oracle
To
 
LOV Oracle
Jobs From
 
LOV Oracle
To
 
LOV Oracle
Classes From
 
LOV Oracle
Period Close Date
 
LOV Oracle
Period Start Date
 
LOV Oracle
Report Type
 
LOV Oracle
Sort By
 
LOV Oracle