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

Description
Categories: Enginatics, Toolkit - Cost Accounting
Repository: Github
Report to compare the General Ledger inventory balances with the perpetual inventory values (based on the stored month-end inventory balances, generated when the inventory accounting period is closed).

/* +=============================================================================+
-- | Copyright 2010-20 Douglas Volz Consulting, Inc. |
-- | All rights re ... 
Report to compare the General Ledger inventory balances with the perpetual inventory values (based on the stored month-end inventory balances, generated when the inventory accounting period is closed).

/* +=============================================================================+
-- | Copyright 2010-20 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 enter
-- | a NULL or % symbol (optional parameter)
-- |
-- | Description:
-- | Report to reconcile G/L and the Inventory and WIP Perpetual
-- | by Ledger and full account, for a desired accounting period.
-- |
-- | ============================================================================
-- | Does not consider cost groups and assumes the elemental cost accounts by
-- | subinventory are the same as the material account.
-- | This script also uses a custom lookup code called XXX_CST_GLINV_RECON_ACCOUNTS
-- | as a means to determine the valid inventory account numbers
-- | ============================================================================
-- |
-- | 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.
-- +=============================================================================+*/

XXX_INV_RECON_REPT_V5-20-Jul-2016.sql
   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
nvl(mp.cost_group_accounting,-99)<>1
union
--organization
select
mp.acct
from
(
select mp.material_account acct, mp.primary_cost_method, mp.cost_group_accounting from mtl_parameters mp union
select mp.material_overhead_account, mp.primary_cost_method, mp.cost_group_accounting from mtl_parameters mp union
select mp.resource_account, mp.primary_cost_method, mp.cost_group_accounting from mtl_parameters mp union
select mp.overhead_account, mp.primary_cost_method, mp.cost_group_accounting from mtl_parameters mp union
select mp.outside_processing_account, mp.primary_cost_method, mp.cost_group_accounting from mtl_parameters mp
) mp
where
mp.primary_cost_method<>1 and --non frozen
nvl(mp.cost_group_accounting,-99)<>1
union
--intransit accounting
select mip.intransit_inv_account from mtl_interorg_parameters mip
union
--receiving accounting
select rp.receiving_account_id from rcv_parameters rp
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
wac.organization_id in (select mp.organization_id from mtl_parameters mp where nvl(mp.cost_group_accounting,-99)<>1)
union
--cost group accounting
select
ccga.acct
from
(
select ccga.material_account acct, ccga.cost_group_id from cst_cost_group_accounts ccga union
select ccga.material_overhead_account, ccga.cost_group_id from cst_cost_group_accounts ccga union
select ccga.resource_account, ccga.cost_group_id from cst_cost_group_accounts ccga union
select ccga.overhead_account, ccga.cost_group_id from cst_cost_group_accounts ccga union
select ccga.outside_processing_account, ccga.cost_group_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
msi.organization_id in (select mp.organization_id from mtl_parameters mp where mp.cost_group_accounting=1)
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
wac.organization_id in (select mp.organization_id from mtl_parameters mp where mp.cost_group_accounting=1)
)
) 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,
sum(nvl(net_recon_bal.gl_inventory_amount,0)) GL_Inventory,
sum(nvl(net_recon_bal.gl_payables_amount,0)) GL_Payables,
sum(nvl(net_recon_bal.gl_wip_amount,0)) GL_Work_in_Process,
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.inv_onhand_value,0)) Inventory_Value,
sum(nvl(net_recon_bal.wip_value,0)) WIP_Value,
sum(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.inv_onhand_value,0) + nvl(net_recon_bal.wip_value,0)) Difference
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,
    sum(nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0)) gl_beg_balance,
    sum(nvl(gl_per_sum.inventory_amount,0)) gl_inventory_amount,
    sum(nvl(gl_per_sum.payables_amount,0)) gl_payables_amount,
    sum(nvl(gl_per_sum.wip_amount,0)) gl_wip_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,
    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,
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='Payables' then gjl.amount end),0) payables_amount,
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='WIP' then gjl.amount end),0) wip_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</