CAC Inventory Accounts Setup

Description
Categories: Enginatics
Repository: Github
Report to show Valuation, Receiving, Profit and Loss and Inter-Org accounts as setup in the inventory organization parameters.

/* +=============================================================================+
-- | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Per ... 
Report to show Valuation, Receiving, Profit and Loss and Inter-Org accounts as setup in the inventory organization parameters.

/* +=============================================================================+
-- | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise is included in this |
-- | permission. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_inv_accts_setup_rept.sql
-- |
-- | Parameters:
-- | p_account_group -- The group of inventory organization parameter accounts
-- | you wish to report
-- | p_org_code -- Specific inventory organization you wish to report (optional)
-- | p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- | Description:
-- | Report to show Valuation, Receiving, Profit and Loss and Inter-Org accounts
-- | as setup in the inventory organization parameters
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.11 06 Mar 2019 Douglas Volz Added Cost Variance Account, used with Average, FIFO, LIFO
-- | 1.12 14 Mar 2019 Douglas Volz Added Expense A/P Accrual Account, Retroactive Price
-- | Adjustment Account, Receiving Clearing Account.
-- | 1.13 12 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters,
-- | an outer join to gcc in case of invalid accounts
-- | and change to gl.short_name.
-- | 1.14 28 Feb 2020 Douglas Volz Changed to multi-language views for the item
-- | master, inventory orgs and operating units.
-- +=============================================================================+*/
   more
Run CAC Inventory Accounts Setup and other Oracle EBS reports with Blitz Report™ on our demo environment
select	'Inter-org' Account_Group,
-- ===========================================
-- This first group of select statements is for
-- the Inter-Org Accounts Group
-- ===========================================
	'Inter-Org A/P Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.interorg_payables_account     = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Inter-Org'
	 or
	 :p_account_group is null)                                                                          -- inter-org
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
select	'Inter-org' Account_Group,
	'Inter-Org A/R Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.interorg_receivables_account = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Inter-Org'
	 or
	 :p_account_group is null)                                                                          -- inter-org
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
select	'Inter-org' Account_Group,
	'Intransit Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.intransit_inv_account        = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Inter-Org'
	 or
	 :p_account_group is null)                                                                          -- inter-org
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
select	'Inter-org' Account_Group,
	'Inter-Org PPV Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.interorg_price_var_account   = gcc.code_combination_id (+)
	-- ===========================================
	-- Organization joins to the HR org model
	-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Inter-Org'
	 or
	 :p_account_group is null)                                                                          -- inter-org
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
select	'Inter-org' Account_Group,
	'Inter-Org Xfer Credit Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.interorg_transfer_cr_account = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Inter-Org'
	 or
	 :p_account_group is null)                                                                          -- inter-org
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
-- ===========================================
-- This second group of select statements is
-- for the Profit and Loss Account_Group
-- ===========================================
select	'Profit and Loss' Account_Group,
	'Cost of Goods Sold Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.cost_of_sales_account        = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Profit and Loss'
	 or
	 :p_account_group is null)                                                                          -- Profit and Loss
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
select	'Profit and Loss' Account_Group,
	'Sales_Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.sales_account                = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_codee
and	(:p_account_group = 'Profit and Loss'
	 or
	 :p_account_group is null)                                                                          -- Profit and Loss
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
union all
-- ===========================================
-- This third group of select statements is
-- for the Other Accounts Group
-- ===========================================
-- Revision for version 1.5, change Account_Group for Deferred COGS
-- select 'Profit and Loss' Account_Group,
select	'Other Accounts' Account_Group,
	'Deferred COGS_Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.deferred_cogs_account        = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Profit and Loss'
	 or
	 :p_account_group is null)                                                                          -- Profit and Loss
-- Fix for version 1.4
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
/* +=============================================================================+
-- Revision for version 1.15
-- Comment this out, mp.cat_wt_account not found at client site
-- +=============================================================================+
union all
-- Revision for version 1.5, add On-Hand Adjustment Account
-- Usually only in use for OPM organizations
select	'Other Accounts' Account_Group,
	'On-Hand Adjustment Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.cat_wt_account               = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Other Accounts'
	 or
	 :p_account_group is null)                                                                          -- Other Accounts
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End revision for version 1.5
-- +=============================================================================+
-- End of commenting out, end of revision for version 1.15
-- +=============================================================================+*/
union all
-- Revision for version 1.6, add Expense Accounts to this report
select	'Other Accounts' Account_Group,
	'Expense Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.expense_account              = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Other Accounts'
	 or
	 :p_account_group is null)                                                                          -- Other Accounts
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End revision for version 1.6
union all
-- Revision for version 1.11, add Cost Variance Account to this report
select	'Other Accounts' Account_Group,
	'Avg Cost Variance Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.average_cost_var_account     = gcc.code_combination_id (+)
	-- ===========================================
	-- Organization joins to the HR org model
	-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Other Accounts'
	 or
	 :p_account_group is null)                                                                          -- Other Accounts
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End revision for version 1.11
union all
-- ===========================================
-- This third group of select statements is
-- for the Receiving Accounts Group
-- ===========================================
select	'Receiving' Account_Group,
	'Inv. A/P Accrual Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.ap_accrual_account           = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Receiving'
	 or
	 :p_account_group is null)                                                                          -- Receiving
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End fix for version 1.4
union all
-- Revision for version 1.9, add Warehouse Mgmt System (Wms) 
-- Default Goods Dispatched Account
-- from the Shipping Parameters, wsh_shipping_parameters.  
-- The Trip Stop Interface fails if this ccid is not populated
select	'Shipping' Account_Group,
	'Goods Dispatched Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	wsh_shipping_parameters wsp,
	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	wsp.organization_id             = mp.organization_id
-- Use an outer join to allow null values
-- as the Shipping Parameters form has this as
-- an optional field as opposed to a required field
and	wsp.goods_dispatched_account    = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Other Accounts'
	 or
	 :p_account_group is null)                                                                          -- Other Accounts
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End revision for version 1.9
union all
select	'Receiving' Account_Group,
	'IPV Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.invoice_price_var_account    = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Receiving'
	 or
	 :p_account_group is null)                                                                          -- Receiving
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End fix for version 1.4
union all
select	'Receiving' Account_Group,
	'PPV Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.purchase_price_var_account   = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Receiving'
	 or
	 :p_account_group is null)                                                                          -- Receiving
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End fix for version 1.4
       and sysdate < nvl(haou.date_to, sysdate + 1)
union all
-- Revision for version 1.12, add the Clearing Account
-- from Receiving Options, rcv_parameters
select	'Receiving' Account_Group,
	'Clearing Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	rcv_parameters rp,
	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	rp.organization_id              = mp.organization_id
and	rp.clearing_account_id          = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Receiving'
	 or
	 :p_account_group is null)                                                                          -- Receiving
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End fix for version 1.12
union all
-- Revision for version 1.12, add the Expense_Accrual_Account
-- from Purchasing Options, po_system_parameters_all
select	'Receiving' Account_Group,
	'Expense A/P Accrual Account' Account_Type,
	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	haou.name Organization_Name,
	&segment_columns
	mp.last_update_date Last_Update_Date
from	po_system_parameters_all psp,
	mtl_parameters mp,
	gl_code_combinations gcc,
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,
	hr_all_organization_units_vl haou2,
	gl_ledgers gl
where	mp.master_organization_id       = mp.organization_id
and	psp.org_id                      = haou2.organization_id
and	psp.accrued_code_combination_id = gcc.code_combination_id (+)
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
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
and	1=1                                                                                                   -- p_ledger, p_operating_unit, p_org_code
and	(:p_account_group = 'Receiving'
	 or
	 :p_account_group is null)                                                                          -- Receiving
-- avoid selecting disabled inventory organizations
and	sysdate < nvl(haou.date_to, sysdate + 1)
-- End fix for version 1.12
union all
-- ===========================================
-- This fourth group of select statements is
-- for the Valuation Account_Group
-- ===========================================
select	'Valuation' Account_Group,
	'Material Value Account' Account_Type,
	nvl(gl