CAC Inventory to G/L Reconciliation (Restricted by Org Access)

Description
Categories: Enginatics
Repository: Github
For Discrete Costing, this report compares the General Ledger inventory balances with the perpetual inventory values (based on the stored month-end inventory and WIP balances, generated when the inventory accounting period is closed, plus a calculated month-end receiving value). Inventory balances includes Receiving, Onhand Inventory (Stock), Intransit and Work in Process (WIP). This report auto ...  For Discrete Costing, this report compares the General Ledger inventory balances with the perpetual inventory values (based on the stored month-end inventory and WIP balances, generated when the inventory accounting period is closed, plus a calculated month-end receiving value). Inventory balances includes Receiving, Onhand Inventory (Stock), Intransit and Work in Process (WIP). This report automatically discovers your valuation accounts based on your setups, such as the Cost Method (Standard, Average, FIFO or LIFO Costing), and also if using Project Manufacturing (PJM - Cost Group Accounting) or Warehouse Management (WMS - Cost Group Accounting) or even if using Category Accounts. But note as maintenance work orders are normally charged to expense accounts, maintenance (EAM) work orders are not included in this report. Also note this report does not break out the perpetual account values by cost element; it assumes the elemental cost accounts by subinventory or cost group are the same as the material account, as the stored month-end perpetual inventory balances are only stored by organization, accounting period and item, but not by cost element.

/* +=============================================================================+
-- | Copyright 2010-23 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_RECON_REPT.sql
-- |
-- | Parameters:
-- | P_PERIOD_NAME -- Enter the Period Name you wish to reconcile balances for (mandatory)
-- | P_LEDGER -- general ledger you wish to report, for all ledgers left this parameter blank
-- | ============================================================================
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 24 Sep 2004 Douglas Volz Initial Coding based on earlier work with
-- | the following scripts and designs:
-- | XXX_GL_RECON.sql,
-- | XXX_PERPETUAL_INV_RECON_SUM.sql,
-- | XXX_PERPETUAL_RCV_RECON_SUM.sql,
-- | XXX_PERPETUAL_WIP_RECON_SUM.sql,
-- | MD050 Inventory Reconciliation
-- | 1.1 28 Jun 2010 Douglas Volz Updated design and code for Release 12,
-- | changed GL_SETS_OF_BOOKS to GL_LEDGERS
-- | 1.2 14 Nov 2010 Douglas Volz Modified for Cost SIG Presentation
-- | 1.3 11 Mar 2014 Douglas Volz Changed the COA segments to be generic and removed
-- | the second product line join to gl_code_combinations
-- | 1.4 07 Apr 2014 Douglas Volz Added join condition to avoid secondary ledgers and
-- | added an explicit to_char on the accounts
-- | ml.lookup_code to avoid an "invalid number" SQL error.
-- | 1.5 20 Jul 2016 Douglas Volz Added condition to avoid summary journals
-- | 1.6 18 May 2020 Douglas Volz Avoid disabled inventory organizations.
-- | 1.7 07 Dec 2020 Douglas Volz/Andy Haack Only report inventory organization ledgers. Initial Blitz version.
-- | 1.8 22 Feb 2023 Douglas Volz Add in Receiving Value, fixes for Category Accounting.
-- +=============================================================================+*/
   more
with y as (
select distinct
gcc.&account_segment account
from
(
--subinventory
select
msi.acct
from
(
select msi.material_account acct, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union
select msi.material_overhead_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union
select msi.resource_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union
select msi.overhead_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union
select msi.outside_processing_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi
) msi,
mtl_parameters mp
where
msi.asset_inventory=1 and
msi.organization_id=mp.organization_id and
mp.primary_cost_method=1 and --frozen
-- Revision for version 1.8
-- nvl(mp.cost_group_accounting,-99)<>1
2 = case
 when nvl(mp.cost_group_accounting,2) = 1 then 1
 when exists (select 'x'
   from   pjm_org_parameters pop
   where  mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled
 when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
 when nvl(mp.cost_group_accounting,2) = 2 then 2
 else 2
    end and
5=5 and
mp.organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+)))
-- End for revision 1.8
union
--organization
select
mp.acct
from
(
select mp.material_account acct, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union
select mp.material_overhead_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union
select mp.resource_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union
select mp.overhead_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union
select mp.outside_processing_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp
) mp
where
mp.primary_cost_method<>1 and --non frozen
-- Revision for version 1.8
-- nvl(mp.cost_group_accounting,-99)<>1
2 = case
 when nvl(mp.cost_group_accounting,2) = 1 then 1
 when exists (select 'x'
   from   pjm_org_parameters pop
   where  mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled
 when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
 when nvl(mp.cost_group_accounting,2) = 2 then 2
 else 2
    end and
5=5
-- End for revision 1.8
union
--intransit accounting
select mip.intransit_inv_account from mtl_interorg_parameters mip
-- Revision for version 1.8
where 6=6 and
(
mip.from_organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+))) or
mip.to_organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+)))
)
union
--receiving accounting
select rp.receiving_account_id from rcv_parameters rp
-- Revision for version 1.8
where 7=7 and
rp.organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+)))
union
--wip accounting when cost group accounting is not in use
select distinct
wac.acct
from
(
select wac.material_account acct, wac.class_type, wac.organization_id from wip_accounting_classes wac union
select wac.material_overhead_account, wac.class_type, wac.organization_id from wip_accounting_classes wac union
select wac.resource_account, wac.class_type, wac.organization_id from wip_accounting_classes wac union
select wac.overhead_account, wac.class_type, wac.organization_id from wip_accounting_classes wac union
select wac.outside_processing_account, wac.class_type, wac.organization_id from wip_accounting_classes wac
) wac
where
wac.class_type not in (4,6,7) and --4-expense non-standard, 6-maintenance, 7-expense non-standard lot based
-- Revision for version 1.8
-- wac.organization_id in (select mp.organization_id from mtl_parameters mp where nvl(mp.cost_group_accounting,-99)<>1)
wac.organization_id in 
(select mp.organization_id from mtl_parameters mp where
 2 = case
 when nvl(mp.cost_group_accounting,2) = 1 then 1
 when exists (select 'x'
   from   pjm_org_parameters pop
   where  mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled
 when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
 when nvl(mp.cost_group_accounting,2) = 2 then 2
 else 2
    end and
 5=5
)
-- End revision for version 1.8
union
--cost group accounting
select
ccga.acct
from
(
select ccga.material_account acct, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union
select ccga.material_overhead_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union
select ccga.resource_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union
select ccga.overhead_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union
select ccga.outside_processing_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga
) ccga
where
ccga.cost_group_id in (
select
msi.default_cost_group_id --from subinventory
from
mtl_secondary_inventories msi
where
msi.asset_inventory=1 and
-- Revision for version 1.8
-- msi.organization_id in (select mp.organization_id from mtl_parameters mp where mp.cost_group_accounting=1)
msi.organization_id in 
(select mp.organization_id from mtl_parameters mp where
 1 = case
 when nvl(mp.cost_group_accounting,2) = 1 then 1
 when exists (select 'x'
   from   pjm_org_parameters pop
   where  mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled
 when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
 when nvl(mp.cost_group_accounting,2) = 2 then 2
 else 2
    end and
 5=5
)
)
-- End revision for version 1.8
union
select
ccwac.cost_group_id --wip accounting when cost group accounting is in use
from
cst_cg_wip_acct_classes ccwac,
wip_accounting_classes wac
where
ccwac.organization_id=wac.organization_id and
ccwac.class_code=wac.class_code and
wac.class_type not in (4,6,7) and --4-expense non-standard, 6-maintenance, 7-expense non-standard lot based
-- Revision for version 1.8
-- wac.organization_id in (select mp.organization_id from mtl_parameters mp where mp.cost_group_accounting=1)
wac.organization_id in 
(select mp.organization_id from mtl_parameters mp where
 1 = case
 when nvl(mp.cost_group_accounting,2) = 1 then 1
 when exists (select 'x'
   from   pjm_org_parameters pop
   where  mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled
 when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
 when nvl(mp.cost_group_accounting,2) = 2 then 2
 else 2
    end and
 5=5
-- End revision for version 1.8
)
) x,
gl_code_combinations gcc
where
x.acct=gcc.code_combination_id
)
------------------------SQL starts here-----------------------
select
net_recon_bal.period_name,
net_recon_bal.ledger,
&segment_columns
sum(nvl(net_recon_bal.gl_beg_balance,0)) GL_Beginning_Balance,
-- Revision for version 1.8
sum(nvl(net_recon_bal.gl_receiving_amount,0)) GL_Receiving,
sum(nvl(net_recon_bal.gl_inventory_amount,0)) GL_Inventory,
sum(nvl(net_recon_bal.gl_wip_amount,0)) GL_Work_in_Process,
sum(nvl(net_recon_bal.gl_payables_amount,0)) GL_Payables,
sum(nvl(net_recon_bal.gl_other_amount,0)) GL_Other,
sum(nvl(net_recon_bal.gl_end_balance,0)) GL_Ending_Balance,
sum(nvl(net_recon_bal.receiving_value,0)) Receiving_Value,
sum(nvl(net_recon_bal.inv_onhand_value,0)) Inventory_Value,
sum(nvl(net_recon_bal.wip_value,0)) WIP_Value,
-- Revision for version 1.8
sum(nvl(net_recon_bal.receiving_value,0) + nvl(net_recon_bal.inv_onhand_value,0) + nvl(net_recon_bal.wip_value,0)) Total_Perpetual_Value,
sum(nvl(net_recon_bal.gl_end_balance,0)) - sum(nvl(net_recon_bal.receiving_value,0) + nvl(net_recon_bal.inv_onhand_value,0) + nvl(net_recon_bal.wip_value,0)) Difference
-- End revision for version 1.8
from
-- ==============================================
-- 1.0 first select the general ledger balances
-- ==============================================
(
select gb.period_name period_name,
    gl.name ledger,
    gcc.segment1 seg1,
    gcc.segment2 seg2,
    gcc.segment3 seg3,
    gcc.segment4 seg4,
    gcc.segment5 seg5,
    gcc.segment6 seg6,
    gcc.segment7 seg7,
    gcc.segment8 seg8,
    gcc.segment9 seg9,
    gcc.segment10 seg10,
    gcc.segment11 seg11,
    gcc.segment12 seg12,
    gcc.segment13 seg13,
    gcc.segment14 seg14,
    gcc.segment15 seg15,
    sum(nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0)) gl_beg_balance,
    -- Revision for version 1.8
    sum(nvl(gl_per_sum.receiving_amount,0)) gl_receiving_amount,
    sum(nvl(gl_per_sum.inventory_amount,0)) gl_inventory_amount,
    sum(nvl(gl_per_sum.wip_amount,0)) gl_wip_amount,
    sum(nvl(gl_per_sum.payables_amount,0)) gl_payables_amount,
    sum(nvl(gl_per_sum.other_amount,0)) gl_other_amount,
    sum(nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0)) +
      sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0)) gl_end_balance,
    -- Revision for version 1.8
    null receiving_value,
    null inv_onhand_value,
    null wip_value
    from
    gl_ledgers gl,
    gl_code_combinations gcc,
    gl_balances gb,
    (select gjh.period_name period_name,
        gjh.ledger_id,
        gjl.code_combination_id,
        gcc.segment1 seg1,
        gcc.segment2 seg2,
        gcc.segment3 seg3,
        gcc.segment4 seg4,
        gcc.segment5 seg5,
        gcc.segment6 seg6,
        gcc.segment7 seg7,
        gcc.segment8 seg8,
        gcc.segment9 seg9,
        gcc.segment10 seg10,
        gcc.segment11 seg11,
        gcc.segment12 seg12,
        gcc.segment13 seg13,
        gcc.segment14 seg14,
        gcc.segment15 seg15,
        nvl(sum(case when gjh.je_source='Cost Management' and gjh.je_category='Receiving' then gjl.amount end),0) receiving_amount,
        nvl(sum(case when gjh.je_source='Cost Management' and gjh.je_category='Inventory' then gjl.amount end),0) inventory_amount,
        nvl(sum(case when gjh.je_source='Cost Management' and gjh.je_category='WIP' then gjl.amount end),0) wip_amount,
        nvl(sum(case when gjh.je_source='Payables' then gjl.amount end),0) payables_amount,
        nvl(sum(case when gjh.je_source not in ('Cost Management','Payables') then gjl.amount end),0) other_amount,
        nvl(sum(gjl.amount),0) monthly_activity
     from
     gl_je_headers gjh,
     (select nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) amount, gjl.* from gl_je_lines gjl) gjl,
     gl_code_combinations gcc,
     gl_ledgers gl
     where 1=1  -- gjh.period_name=:period_name
     -- Revision for version 1.8
     and    gjh.ledger_id           = gl.ledger_id
     and    gjh.je_header_id        = gjl.je_header_id
     and    gjh.status              = 'P'
     and    gjh.actual_flag         = 'A'
     and    gcc.summary_flag        = 'N'
     and    gjl.code_combination_id = gcc.code_combination_id
     and    gcc.&account_segment in (select y.account from y)
     -- ===========================================
     -- Revision for version 1.1
     -- Only get inventory organization ledgers
     -- ===========================================
     and    gl.ledger_id in 
                (select distinct gl.ledger_id
                 from   hr_organization_information hoi,
                        hr_all_organization_units haou,
                        mtl_parameters mp,
                        gl_ledgers gl
                 where  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    gl.ledger_id                  = to_number(hoi.org_information1) -- get the ledger_id
                 -- avoid selecting disabled inventory organizations
                 and    sysdate < nvl(haou.date_to, sysdate + 1)
                 -- Revision for version 1.8
                 and    8=8
                 and 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_i