WIP Value

Description
Categories: BI Publisher
Application: Work in Process
Source: WIP Value Report (XML)
Short Name: WIPUTVAL_XML
DB package: WIP_WIPUTVAL_XMLP_PKG
select ml2.lookup_code
,ml2.meaning
,oap.schedule_close_date
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.pl_material_in,0),2,
nvl(wpb.pl_material_overhead_in,0),3,nvl(wpb.tl_resource_in,0)+
nvl(wpb.pl_resource_in,0),4,nvl(wpb.tl_outside_processing_in,0)+
nvl(wpb.pl_outside_processing_in,0),5,nvl(wpb.tl_overhead_in,0)+
nvl(wpb.pl_overhead_in,0),12,nvl(wpb.tl_scrap_in,0),0)),
:C_PRECISION))
Costs_Incurred
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_out,0)+
nvl(wpb.pl_material_out,0),2,nvl(wpb.pl_material_overhead_out,0)+ nvl(wpb.tl_material_overhead_out,0),3,
nvl(wpb.tl_resource_out,0)+nvl(wpb.pl_resource_out,0),4,nvl(wpb.tl_outside_processing_out,0)+nvl(wpb.pl_outside_processing_out,
0),5,nvl(wpb.tl_overhead_out,0)+nvl(wpb.pl_overhead_out,0),
12,nvl(wpb.tl_scrap_out,0),0)),:C_PRECISION))
Costs_Relieved
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_var,0)+
nvl(wpb.pl_material_var,0),2,nvl(wpb.pl_material_overhead_var,0)+ nvl(wpb.tl_material_overhead_var,0),3,
nvl(wpb.tl_resource_var,0)+nvl(wpb.pl_resource_var,0),4,
nvl(wpb.tl_outside_processing_var,0)+nvl(wpb.pl_outside_processing_var,
0),5,nvl(wpb.tl_overhead_var,0)+nvl(wpb.pl_overhead_var,0),
12,nvl(wpb.tl_scrap_var,0),0)),:C_PRECISION))
Elemental_Var
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,6,nvl(wpb.tl_material_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)+
nvl(wpb.tl_material_overhead_var,0),7,nvl(wpb.tl_resource_var,0),
8,nvl(wpb.tl_outside_processing_var,0),9,
nvl(wpb.tl_overhead_var,0),13,nvl(wpb.tl_scrap_var,0),0)),
:C_PRECISION))
Single_Level
,(sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.pl_material_in,0),2,
nvl(wpb.pl_material_overhead_in,0),3,nvl(wpb.tl_resource_in,0)+
nvl(wpb.pl_resource_in,0),4,nvl(wpb.tl_outside_processing_in,0)+
nvl(wpb.pl_outside_processing_in,0),5,nvl(wpb.tl_overhead_in,0)+
nvl(wpb.pl_overhead_in,0),12,nvl(wpb.tl_scrap_in,0),0)),
:C_PRECISION))
-
sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_out,0)+
nvl(wpb.pl_material_out,0),2,nvl(wpb.pl_material_overhead_out,0)+ nvl(wpb.tl_material_overhead_out,0),3,
nvl(wpb.tl_resource_out,0)+nvl(wpb.pl_resource_out,0),4,nvl(wpb.tl_outside_processing_out,0)+nvl(wpb.pl_outside_processing_out,
0),5,nvl(wpb.tl_overhead_out,0)+nvl(wpb.pl_overhead_out,0),
12,nvl(wpb.tl_scrap_out,0),0)),:C_PRECISION))
- 
sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_var,0)+
nvl(wpb.pl_material_var,0),2,nvl(wpb.pl_material_overhead_var,0)+ nvl(wpb.tl_material_overhead_var,0),3,
nvl(wpb.tl_resource_var,0)+nvl(wpb.pl_resource_var,0),4,
nvl(wpb.tl_outside_processing_var,0)+nvl(wpb.pl_outside_processing_var,
0),5,nvl(wpb.tl_overhead_var,0)+nvl(wpb.pl_overhead_var,0),
12,nvl(wpb.tl_scrap_var,0),0)),:C_PRECISION))) NET_ACTIVITY
--,NULL ENDING_BALANCE
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_wip_acct_sort','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','PADDED_VALUE') C_WIP_ACCT_SORT
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','VALUE') C_ACCT_FIELD1
from
gl_code_combinations gcc
,mfg_lookups ml
,mfg_lookups ml2
,wip_period_balances wpb
,org_acct_periods oap
,mtl_system_items msi
,wip_entities we
,wip_discrete_jobs wdj
where wdj.organization_id=:P_Organization_Id
&C_Limit_Classes_DJ
&C_Closed_Jobs
&C_Limit_Types
&C_Limit_Jobs
and wpb.wip_entity_id=wdj.wip_entity_id
and &P_PROJECT_WHERE
and wpb.organization_id=:P_Organization_Id
and ml.lookup_type='WIP_CLASS_TYPE'
and ml.lookup_code=wpb.class_type
and oap.organization_id=:P_Organization_Id
and oap.acct_period_id=wpb.acct_period_id
and oap.schedule_close_date=:C_Period_Close_Date
and wdj.date_released is not null 
and trunc(wdj.date_released) <= :C_Period_Close_Date
and we.wip_entity_id=wdj.wip_entity_id
and we.organization_id=:P_Organization_Id 
and msi.inventory_item_id &P_OUTER=wdj.primary_item_id
and msi.organization_id &P_OUTER=:P_Organization_Id
and &P_FLEXWHERE1
and ml2.lookup_type='WIP_ELEMENT_VAR_TYPE'
and (ml2.lookup_code <= 9 or ml2.lookup_code in (12,13))
and gcc.chart_of_accounts_id=:P_STRUCT_NUM
and :p_report_type=1
and gcc.code_combination_id =
decode(ml2.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,12,
wdj.est_scrap_account,13,wdj.est_scrap_var_account,0)
group by ml2.lookup_code
,ml2.meaning
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_wip_acct_sort','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','PADDED_VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','VALUE')
,oap.schedule_close_date
UNION
select ml2.lookup_code
,ml2.meaning
--,null C_ACCT_FLEX
,max(oap.schedule_close_date)
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.pl_material_in,0),2,
nvl(wpb.pl_material_overhead_in,0),3,nvl(wpb.tl_resource_in,0)+
nvl(wpb.pl_resource_in,0),4,nvl(wpb.tl_outside_processing_in,0)+
nvl(wpb.pl_outside_processing_in,0),5,nvl(wpb.tl_overhead_in,0)+
nvl(wpb.pl_overhead_in,0),12,nvl(wpb.tl_scrap_in,0),0)),
:C_PRECISION))
Costs_Incurred
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_out,0)+
nvl(wpb.pl_material_out,0),2,nvl(wpb.pl_material_overhead_out,0)+ nvl(wpb.tl_material_overhead_out,0),3,
nvl(wpb.tl_resource_out,0)+nvl(wpb.pl_resource_out,0),4,nvl(wpb.tl_outside_processing_out,0)+nvl(wpb.pl_outside_processing_out,
0),5,nvl(wpb.tl_overhead_out,0)+nvl(wpb.pl_overhead_out,0),
12,nvl(wpb.tl_scrap_out,0),0)),:C_PRECISION))
Costs_Relieved
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_var,0)+
nvl(wpb.pl_material_var,0),2,nvl(wpb.pl_material_overhead_var,0)+ nvl(wpb.tl_material_overhead_var,0),3,
nvl(wpb.tl_resource_var,0)+nvl(wpb.pl_resource_var,0),4,
nvl(wpb.tl_outside_processing_var,0)+nvl(wpb.pl_outside_processing_var,
0),5,nvl(wpb.tl_overhead_var,0)+nvl(wpb.pl_overhead_var,0),
12,nvl(wpb.tl_scrap_var,0),0)),:C_PRECISION))
Elemental_Var
,sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,6,nvl(wpb.tl_material_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)+
nvl(wpb.tl_material_overhead_var,0),7,nvl(wpb.tl_resource_var,0),
8,nvl(wpb.tl_outside_processing_var,0),9,
nvl(wpb.tl_overhead_var,0),13,nvl(wpb.tl_scrap_var,0),0)),
:C_PRECISION))
Single_Level
--,NULL NET_ACTIVITY
,(sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.pl_material_in,0),2,
nvl(wpb.pl_material_overhead_in,0),3,nvl(wpb.tl_resource_in,0)+
nvl(wpb.pl_resource_in,0),4,nvl(wpb.tl_outside_processing_in,0)+
nvl(wpb.pl_outside_processing_in,0),5,nvl(wpb.tl_overhead_in,0)+
nvl(wpb.pl_overhead_in,0),12,nvl(wpb.tl_scrap_in,0),0)),
:C_PRECISION))
-
sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_out,0)+
nvl(wpb.pl_material_out,0),2,nvl(wpb.pl_material_overhead_out,0)+ nvl(wpb.tl_material_overhead_out,0),3,
nvl(wpb.tl_resource_out,0)+nvl(wpb.pl_resource_out,0),4,nvl(wpb.tl_outside_processing_out,0)+nvl(wpb.pl_outside_processing_out,
0),5,nvl(wpb.tl_overhead_out,0)+nvl(wpb.pl_overhead_out,0),
12,nvl(wpb.tl_scrap_out,0),0)),:C_PRECISION))
- 
sum(round((:P_EXCHANGE_RATE) *
(decode(ml2.lookup_code,1,nvl(wpb.tl_material_var,0)+
nvl(wpb.pl_material_var,0),2,nvl(wpb.pl_material_overhead_var,0)+ nvl(wpb.tl_material_overhead_var,0),3,
nvl(wpb.tl_resource_var,0)+nvl(wpb.pl_resource_var,0),4,
nvl(wpb.tl_outside_processing_var,0)+nvl(wpb.pl_outside_processing_var,
0),5,nvl(wpb.tl_overhead_var,0)+nvl(wpb.pl_overhead_var,0),
12,nvl(wpb.tl_scrap_var,0),0)),:C_PRECISION))) NET_ACTIVITY
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_wip_acct_sort','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','PADDED_VALUE') C_WIP_ACCT_SORT
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','VALUE') C_ACCT_FIELD1
from
gl_code_combinations gcc
,mfg_lookups ml
,mfg_lookups ml2
,wip_period_balances wpb
,org_acct_periods oap
,mtl_system_items msi
,wip_entities we
,wip_discrete_jobs wdj
where wdj.organization_id=:P_Organization_Id
&C_Limit_Classes_DJ
&C_Closed_Jobs
&C_Limit_Types
&C_Limit_Jobs
and wpb.wip_entity_id=wdj.wip_entity_id
and &P_PROJECT_WHERE
and wpb.organization_id=:P_Organization_Id
and ml.lookup_type='WIP_CLASS_TYPE'
and ml.lookup_code=wpb.class_type
and oap.organization_id=:P_Organization_Id
and oap.acct_period_id=wpb.acct_period_id
and oap.schedule_close_date <= :C_Period_Close_Date
and wdj.date_released is not null 
and trunc(wdj.date_released) <= :C_Period_Close_Date
and we.wip_entity_id=wdj.wip_entity_id
and we.organization_id=:P_Organization_Id 
and msi.inventory_item_id &P_OUTER=wdj.primary_item_id
and msi.organization_id &P_OUTER=:P_Organization_Id
and &P_FLEXWHERE1
and ml2.lookup_type='WIP_ELEMENT_VAR_TYPE'
and (ml2.lookup_code <= 9 or ml2.lookup_code in (12,13))
and gcc.chart_of_accounts_id=:P_STRUCT_NUM
and :p_report_type=2
and gcc.code_combination_id =
decode(ml2.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,12,
wdj.est_scrap_account,13,wdj.est_scrap_var_account,0)
group by ml2.lookup_code
,ml2.meaning
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_wip_acct_sort','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','PADDED_VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field','SQLGL','GL#',GCC.CHART_OF_ACCOUNTS_ID,NULL,GCC.CODE_COMBINATION_ID,'ALL','Y','VALUE')
union
select ml2.lookup_code
,ml2.meaning 
<