with inv_organizations as
-- Get the list of organizations, ledgers and operating units for Discrete organizations
(select nvl(gl.short_name, gl.name) ledger,
gl.ledger_id,
haou2.name operating_unit,
haou2.organization_id operating_unit_id,
mp.organization_code,
mp.organization_id,
haou.date_to disable_date,
gl.currency_code
from mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl
-- Avoid the item master organization
where mp.organization_id <> mp.master_organization_id
-- Avoid disabled inventory organizations
and sysdate < nvl(haou.date_to, sysdate +1)
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mp.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
-- Revision for version 1.24, Operating Unit and Ledger Controls and Parameters
and (nvl(fnd_profile.value('XXEN_REPORT_USE_LEDGER_SECURITY'),'N')='N' or gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)))
and (nvl(fnd_profile.value('XXEN_REPORT_USE_OPERATING_UNIT_SECURITY'),'N')='N' or haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11))
and 1=1 -- p_org_code, p_operating_unit, p_ledger
group by
nvl(gl.short_name, gl.name),
gl.ledger_id,
to_number(hoi.org_information2),
haou2.name, -- operating_unit
haou2.organization_id, -- operating_unit_id
mp.organization_code,
mp.organization_id,
haou.date_to,
gl.currency_code
), -- inv_organizations
wip_value as
(select inv_orgs.ledger,
inv_orgs.operating_unit,
inv_orgs.organization_code,
wpb.organization_id,
oap.period_name period_name,
oap.schedule_close_date,
wdj.material_account,
wdj.material_overhead_account,
wdj.resource_account,
wdj.outside_processing_account,
wdj.overhead_account,
nvl(wdj.est_scrap_account,wdj.material_account) scrap_account,
wdj.class_code class_code,
ml2.meaning class_type,
we.wip_entity_name,
regexp_replace(we.description,'[^[:alnum:]'' '']', null) job_description,
wdj.wip_entity_id wip_entity_id,
ml1.meaning job_status,
we.creation_date,
wdj.scheduled_start_date,
wdj.date_released,
wdj.date_completed date_completed,
wdj.date_closed date_closed,
case
when (oap.schedule_close_date - we.creation_date) < 31 then '30 days'
when (oap.schedule_close_date - we.creation_date) < 61 then '60 days'
when (oap.schedule_close_date - we.creation_date) < 91 then '90 days'
when (oap.schedule_close_date - we.creation_date) < 121 then '120 days'
when (oap.schedule_close_date - we.creation_date) < 151 then '150 days'
when (oap.schedule_close_date - we.creation_date) < 181 then '180 days'
else 'Over 180 days'
end Aged_Creation_Date,
case
when (nvl(wdj.date_completed,oap.schedule_close_date) -
nvl(wdj.date_released,(nvl(wdj.date_completed,sysdate)))) < 31 then '30 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) -
nvl(wdj.date_released,(nvl(wdj.date_completed,sysdate)))) < 61 then '60 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) -
nvl(wdj.date_released,(nvl(wdj.date_completed,sysdate)))) < 91 then '90 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) -
nvl(wdj.date_released,(nvl(wdj.date_completed,sysdate)))) < 121 then '120 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) -
nvl(wdj.date_released,(nvl(wdj.date_completed,sysdate)))) < 151 then '150 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) -
nvl(wdj.date_released,(nvl(wdj.date_completed,sysdate)))) < 181 then '180 days'
else 'Over 180 days'
end Aged_Compln_vs_Release_Date,
case
when (nvl(wdj.date_completed,oap.schedule_close_date) - we.creation_date) < 31 then '30 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) - we.creation_date) < 61 then '60 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) - we.creation_date) < 91 then '90 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) - we.creation_date) < 121 then '120 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) - we.creation_date) < 151 then '150 days'
when (nvl(wdj.date_completed,oap.schedule_close_date) - we.creation_date) < 181 then '180 days'
else 'Over 180 days'
end Aged_Compln_vs_Creation_Date,
wdj.start_quantity start_quantity,
wdj.quantity_completed quantity_completed,
wdj.quantity_scrapped quantity_scrapped,
wdj.completion_subinventory,
regexp_replace(msub.description,'[^[:alnum:]'' '']', null) subinventory_description,
wdj.primary_item_id,
wdj.project_id,
wdj.lot_number,
inv_orgs.currency_code,
sum(nvl(wpb.pl_material_in,0)-
nvl(wpb.tl_material_out,0)-
nvl(wpb.pl_material_out,0)-
nvl(wpb.tl_material_var,0)-
nvl(wpb.pl_material_var,0)) matl_value,
sum(nvl(wpb.pl_material_overhead_in,0)-
nvl(wpb.tl_material_overhead_out,0)-
nvl(wpb.pl_material_overhead_out,0)-
nvl(wpb.tl_material_overhead_var,0)-
nvl(wpb.pl_material_overhead_var,0)) moh_value,
sum(nvl(wpb.tl_resource_in,0)+
nvl(wpb.pl_resource_in,0)-
nvl(wpb.tl_resource_out,0)-
nvl(wpb.pl_resource_out,0)-
nvl(wpb.tl_resource_var,0)-
nvl(wpb.pl_resource_var,0)) res_value,
sum(nvl(wpb.tl_outside_processing_in,0)+
nvl(wpb.pl_outside_processing_in,0)-
nvl(wpb.tl_outside_processing_out,0)-
nvl(wpb.pl_outside_processing_out,0)-
nvl(wpb.tl_outside_processing_var,0)-
nvl(wpb.pl_outside_processing_var,0)) osp_value,
sum(nvl(wpb.tl_overhead_in,0)+
nvl(wpb.pl_overhead_in,0)-
nvl(wpb.tl_overhead_out,0)-
nvl(wpb.pl_overhead_out,0)-
nvl(wpb.tl_overhead_var,0)-
nvl(wpb.pl_overhead_var,0)) ovhd_value,
sum(nvl(wpb.tl_scrap_in,0)-
nvl(wpb.tl_scrap_out,0)-
nvl(wpb.tl_scrap_var,0)) scrap_value,
sum(nvl(wpb.pl_material_in,0)) matl_costs_in,
sum(nvl(wpb.pl_material_overhead_in,0)) moh_costs_in,
sum(nvl(wpb.tl_resource_in,0)+
nvl(wpb.pl_resource_in,0)) res_costs_in,
sum(nvl(wpb.tl_outside_processing_in,0)+
nvl(wpb.pl_outside_processing_in,0)) osp_costs_in,
sum(nvl(wpb.tl_overhead_in,0)+
nvl(wpb.pl_overhead_in,0)) ovhd_costs_in,
sum(nvl(wpb.tl_scrap_in,0)) scrap_costs_in,
sum(nvl(wpb.tl_material_out,0)+
nvl(wpb.pl_material_out,0)) matl_costs_out,
sum(nvl(wpb.tl_material_overhead_out,0)+
nvl(wpb.pl_material_overhead_out,0)) moh_costs_out,
sum(nvl(wpb.tl_resource_out,0)+
nvl(wpb.pl_resource_out,0)) res_costs_out,
sum(nvl(wpb.tl_outside_processing_out,0)+
nvl(wpb.pl_outside_processing_out,0)) osp_costs_out,
sum(nvl(wpb.tl_overhead_out,0)+
nvl(wpb.pl_overhead_out,0)) ovhd_costs_out,
sum(nvl(wpb.tl_scrap_out,0)) scrap_costs_out,
sum(nvl(wpb.tl_material_var,0)+
nvl(wpb.pl_material_var,0)) matl_relief,
sum(nvl(wpb.tl_material_overhead_var,0)+
nvl(wpb.pl_material_overhead_var,0)) moh_relief,
sum(nvl(wpb.tl_resource_var,0)+
nvl(wpb.pl_resource_var,0)) res_relief,
sum(nvl(wpb.tl_outside_processing_var,0)+
nvl(wpb.pl_outside_processing_var,0)) osp_relief,
sum(nvl(wpb.tl_overhead_var,0)+
nvl(wpb.pl_overhead_var,0)) ovhd_relief,
sum(nvl(wpb.tl_scrap_var,0)) scrap_relief
from wip_period_balances wpb,
wip_discrete_jobs wdj,
wip_entities we,
wip_accounting_classes wac,
mtl_secondary_inventories msub,
org_acct_periods oap,
inv_organizations inv_orgs,
mfg_lookups ml1, -- job status
mfg_lookups ml2 -- class type
-- ===========================================
-- WIP Job Entity and accounting period joins
-- ===========================================
where wpb.wip_entity_id = wdj.wip_entity_id
and we.wip_entity_id = wdj.wip_entity_id
and wpb.acct_period_id <= oap.acct_period_id
and wpb.organization_id = oap.organization_id
and wac.class_code = wdj.class_code
and wac.organization_id = wdj.organization_id
and msub.secondary_inventory_name (+) = wdj.completion_subinventory
and msub.organization_id (+) = wdj.organization_id
and 2=2 -- p_period_name
and inv_orgs.organization_id = wdj.organization_id
-- ===========================================
-- Inventory accounting period joins to limit
-- to wip activity within the accounting period.
-- ===========================================
-- Limit to jobs closed after the period start date
and nvl(trunc(wdj.date_closed), oap.period_start_date) >= oap.period_start_date
-- ===========================================
-- Lookup Code joins
-- ===========================================
and ml1.lookup_type = 'WIP_JOB_STATUS'
and ml1.lookup_code = wdj.status_type
and ml2.lookup_type = 'WIP_CLASS_TYPE'
and ml2.lookup_code = wac.class_type
-- ===========================================
-- Expense WIP Accounting Classes
-- 4 - Expense Non-standard
-- 6 - Maintenance
-- 7 - Expense Non-standard Lot Based
-- ===========================================
and wac.class_type = decode(wac.class_type,
1, wac.class_type,
2, wac.class_type,
3, wac.class_type,
4, decode(upper(:p_include_expense_wip), -- p_include_expense_wip
'Y', wac.class_type,
'N', 99,
99),
5, wac.class_type,
6, decode(upper(:p_include_expense_wip), -- p_include_expense_wip
'Y', wac.class_type,
'N', 99,
99),
7, wac.class_type,
wac.class_type)
group by
inv_orgs.ledger,
inv_orgs.operating_unit,
inv_orgs.organization_code,
wpb.organization_id,
oap.period_name,
oap.schedule_close_date,
wdj.material_account,
wdj.material_overhead_account,
wdj.resource_account,
wdj.outside_processing_account,
wdj.overhead_account,
nvl(wdj.est_scrap_account,wdj.material_account), -- scrap_account
wdj.class_code,
ml2.meaning, -- class_type
we.wip_entity_name,
regexp_replace(we.description,'[^[:alnum:]'' '']', null), -- Job_Description
wdj.wip_entity_id,
ml1.meaning, -- job status
we.creation_date,
wdj.scheduled_start_date,
wdj.date_released,
wdj.date_completed,
wdj.date_closed,
wdj.start_quantity,
wdj.quantity_completed,
wdj.quantity_scrapped,
wdj.completion_subinventory,
regexp_replace(msub.description,'[^[:alnum:]'' '']', null), -- Subinventory_Description,
wdj.primary_item_id,
wdj.project_id,
wdj.lot_number,
inv_orgs.currency_code
) -- wip_value
------------ main sql query starts here ---------------------
select wip.ledger Ledger,
wip.operating_unit Operating_Unit,
wip.organization_code Org_Code,
wip.period_name Period_Name,
gcc.concatenated_segments WIP_Valuation_Account,
&segment_columns
wip.class_code WIP_Class,
wip.class_type Class_Type,
wip.wip_entity_name WIP_Job,
wip.job_description Job_Description,
wip.job_status Job_Status,
wip.creation_date Creation_Date,
wip.scheduled_start_date Schedule_Start_Date,
wip.date_released Date_Released,
wip.date_completed Date_Completed,
wip.date_closed Date_Closed,
wip.aged_creation_date Aged_Creation_Date,
wip.aged_compln_vs_release_date Aged_Compln_vs_Release_Date,
wip.aged_compln_vs_creation_date Aged_Compln_vs_Creation_Date,
muomv.uom_code UOM_Code,
wip.start_quantity Start_Quantity,
wip.quantity_completed Quantity_Completed,
wip.quantity_scrapped Quantity_Scrapped,
wip.quantity_completed + quantity_scrapped Total_Quantity,
wip.completion_subinventory Completion_Subinventory,
wip.subinventory_description Subinventory_Description,
msiv.concatenated_segments Item_Number,
msiv.description Item_Description,
&category_columns
pp.segment1 Project_Number,
pp.name Project_Name,
wip.lot_number Lot_Number,
wip.currency_code Currency_Code,
sum(wip.matl_value) WIP_Material_Value,
sum(wip.moh_value) WIP_Material_Overhead_Value,
sum(wip.res_value) WIP_Resource_Value,
sum(wip.osp_value) WIP_Outside_Processing_Value,
sum(wip.ovhd_value) WIP_Overhead_Value,
sum(wip.scrap_value) WIP_Scrap_Value,
sum(wip.costs_in) WIP_Costs_In,
sum(wip.costs_out) WIP_Costs_Out,
sum(wip.relief) WIP_Relief,
sum(wip.value) WIP_Value
from gl_code_combinations_kfv gcc,
mtl_system_items_vl msiv,
mtl_units_of_measure_vl muomv,
pa_projects_all pp,
(
-- ============================
-- WIP Material Value
-- ============================
select wv.ledger,
wv.operating_unit,
wv.organization_code,
wv.organization_id,
wv.period_name period_name,
wv.schedule_close_date,
wv.material_account code_combination_id,
wv.class_code class_code,
wv.class_type,
wv.wip_entity_name,
wv.job_description,
wv.wip_entity_id,
wv.job_status,
wv.creation_date,
wv.scheduled_start_date,
wv.date_released,
wv.date_completed date_completed,
wv.date_closed date_closed,
wv.Aged_Creation_Date,
wv.Aged_Compln_vs_Release_Date,
wv.Aged_Compln_vs_Creation_Date,
wv.start_quantity start_quantity,
wv.quantity_completed quantity_completed,
wv.quantity_scrapped quantity_scrapped,
wv.completion_subinventory,
wv.subinventory_description,
wv.primary_item_id,
wv.project_id,
wv.lot_number,
wv.currency_code,
wv.matl_value,
0 moh_value,
0 res_value,
0 osp_value,
0 ovhd_value,
0 scrap_value,
wv.matl_costs_in costs_in,
wv.matl_costs_out costs_out,
wv.matl_relief relief,
wv.matl_value value
from wip_value wv
union all
-- ============================
-- WIP Material Overhead Value
-- ============================
select wv.ledger,
wv.operating_unit,
wv.organization_code,
wv.organization_id,
wv.period_name period_name,
wv.schedule_close_date,
wv.material_overhead_account code_combination_id,
wv.class_code class_code,
wv.class_type,
wv.wip_entity_name,
wv.job_description,
wv.wip_entity_id,
wv.job_status,
wv.creation_date,
wv.scheduled_start_date,
wv.date_released,
wv.date_completed date_completed,
wv.date_closed date_closed,
wv.Aged_Creation_Date,
wv.Aged_Compln_vs_Release_Date,
wv.Aged_Compln_vs_Creation_Date,
wv.start_quantity start_quantity,
wv.quantity_completed quantity_completed,
wv.quantity_scrapped quantity_scrapped,
wv.completion_subinventory,
wv.subinventory_description,
wv.primary_item_id,
wv.project_id,
wv.lot_number,
wv.currency_code,
0 matl_value,
wv.moh_value,
0 res_value,
0 osp_value,
0 ovhd_value,
0 scrap_value,
wv.moh_costs_in costs_in,
wv.moh_costs_out costs_out,
wv.moh_relief relief,
wv.moh_value value
from wip_value wv
union all
-- ============================
-- WIP Resource Value
-- ============================
select wv.ledger,
wv.operating_unit,
wv.organization_code,
wv.organization_id,
wv.period_name period_name,
wv.schedule_close_date,
wv.resource_account code_combination_id,
wv.class_code class_code,
wv.class_type,
wv.wip_entity_name,
wv.job_description,
wv.wip_entity_id,
wv.job_status,
wv.creation_date,
wv.scheduled_start_date,
wv.date_released,
wv.date_completed date_completed,
wv.date_closed date_closed,
wv.Aged_Creation_Date,
wv.Aged_Compln_vs_Release_Date,
wv.Aged_Compln_vs_Creation_Date,
wv.start_quantity start_quantity,
wv.quantity_completed quantity_completed,
wv.quantity_scrapped quantity_scrapped,
wv.completion_subinventory,
wv.subinventory_description,
wv.primary_item_id,
wv.project_id,
wv.lot_number,
wv.currency_code,
0 matl_value,
0 moh_value,
wv.res_value,
0 osp_value,
0 ovhd_value,
0 scrap_value,
wv.res_costs_in costs_in,
wv.res_costs_out costs_out,
wv.res_relief relief,
wv.res_value value
from wip_value wv
union all
-- ============================
-- WIP Outside Processing Value
-- ============================
select wv.ledger,
wv.operating_unit,
wv.organization_code,
wv.organization_id,
wv.period_name period_name,
wv.schedule_close_date,
wv.outside_processing_account code_combination_id,
wv.class_code class_code,
wv.class_type,
wv.wip_entity_name,
wv.job_description,
wv.wip_entity_id,
wv.job_status,
wv.creation_date,
wv.scheduled_start_date,
wv.date_released,
wv.date_completed date_completed,
wv.date_closed date_closed,
wv.Aged_Creation_Date,
wv.Aged_Compln_vs_Release_Date,
wv.Aged_Compln_vs_Creation_Date,
wv.start_quantity start_quantity,
wv.quantity_completed quantity_completed,
wv.quantity_scrapped quantity_scrapped,
wv.completion_subinventory,
wv.subinventory_description,
wv.primary_item_id,
wv.project_id,
wv.lot_number,
wv.currency_code,
0 matl_value,
0 moh_value,
0 res_value,
wv.osp_value,
0 ovhd_value,
0 scrap_value,
wv.osp_costs_in costs_in,
wv.osp_costs_out costs_out,
wv.osp_relief relief,
wv.osp_value value
from wip_value wv
union all
-- ============================
-- WIP Overhead Value
-- ============================
select wv.ledger,
wv.operating_unit,
wv.organization_code,
wv.organization_id,
wv.period_name period_name,
wv.schedule_close_date,
wv.overhead_account code_combination_id,
wv.class_code class_code,
wv.class_type,
wv.wip_entity_name,
wv.job_description,
wv.wip_entity_id,
wv.job_status,
wv.creation_date,
wv.scheduled_start_date,
wv.date_released,
wv.date_completed date_completed,
wv.date_closed date_closed,
wv.Aged_Creation_Date,
wv.Aged_Compln_vs_Release_Date,
wv.Aged_Compln_vs_Creation_Date,
wv.start_quantity start_quantity,
wv.quantity_completed quantity_completed,
wv.quantity_scrapped quantity_scrapped,
wv.completion_subinventory,
wv.subinventory_description,
wv.primary_item_id,
wv.project_id,
wv.lot_number,
wv.currency_code,
0 matl_value,
0 moh_value,
0 res_value,
0 osp_value,
wv.ovhd_value,
0 scrap_value,
wv.ovhd_costs_in costs_in,
wv.ovhd_costs_out costs_out,
wv.ovhd_relief relief,
wv.ovhd_value value
from wip_value wv
union all
-- ============================
-- WIP Scrap Value
-- ============================
select wv.ledger,
wv.operating_unit,
wv.organization_code,
wv.organization_id,
wv.period_name period_name,
wv.schedule_close_date,
wv.scrap_account code_combination_id,
wv.class_code class_code,
wv.class_type,
wv.wip_entity_name,
wv.job_description,
wv.wip_entity_id,
wv.job_status,
wv.creation_date,
wv.scheduled_start_date,
wv.date_released,
wv.date_completed date_completed,
wv.date_closed date_closed,
wv.Aged_Creation_Date,
wv.Aged_Compln_vs_Release_Date,
wv.Aged_Compln_vs_Creation_Date,
wv.start_quantity start_quantity,
wv.quantity_completed quantity_completed,
wv.quantity_scrapped quantity_scrapped,
wv.completion_subinventory,
wv.subinventory_description,
wv.primary_item_id,
wv.project_id,
wv.lot_number,
wv.currency_code,
0 matl_value,
0 moh_value,
0 res_value,
0 osp_value,
0 ovhd_value,
wv.scrap_value,
wv.scrap_costs_in costs_in,
wv.scrap_costs_out costs_out,
wv.scrap_relief relief,
wv.scrap_value value
from wip_value wv
) wip
-- ===================================================
-- Code combination, item and project joins
-- ===================================================
where gcc.code_combination_id (+) = wip.code_combination_id
and msiv.inventory_item_id = wip.primary_item_id
and msiv.organization_id = wip.organization_id
and msiv.primary_uom_code = muomv.uom_code
and pp.project_id (+) = wip.project_id
group by
wip.ledger,
wip.operating_unit,
wip.organization_code,
wip.period_name,
gcc.concatenated_segments, -- WIP Valuation Account
&segment_columns_grp
wip.class_code,
wip.class_type,
wip.wip_entity_name,
wip.job_description,
wip.job_status,
wip.creation_date,
wip.scheduled_start_date,
wip.date_released,
wip.date_completed,
wip.date_closed,
wip.aged_creation_date,
wip.aged_compln_vs_release_date,
wip.aged_compln_vs_creation_date,
muomv.uom_code,
wip.start_quantity,
wip.quantity_completed,
wip.quantity_scrapped,
wip.quantity_completed + wip.quantity_scrapped,
wip.completion_subinventory,
wip.subinventory_description,
msiv.concatenated_segments,
-- Needed for inline select on category
msiv.inventory_item_id,
msiv.organization_id,
msiv.description,
pp.segment1, -- Project Number
pp.name, -- Project Name
wip.lot_number,
wip.currency_code
-- order by Ledger, Operating Unit, Org Code, Accounts, WIP Class and WIP Job
order by
wip.ledger,
wip.operating_unit,
wip.organization_code,
gcc.concatenated_segments, -- WIP Valuation Account
wip.class_code,
wip.wip_entity_name |
