CAC Inventory and Intransit Value (Period-End) - Diagnostic Report

Description
Diagnostic script for CAC Inventory and Intransit Value (Period-End) report
with inv_organizations as
-- Revision for version 1.17
-- Get the list of 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,
  mca.organization_id category_organization_id,
  -- Revision for version 1.18
  mca.category_set_id,
  mp.material_account,
  -- Revision for version 1.21, better logic for Cost Group Accounting
   -- mp.cost_group_accounting,
  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 mp.primary_cost_method in (2,5,6) then 1 -- Average, FIFO or LIFO use Cost Group Accounting
     when nvl(mp.process_enabled_flag, 'N') = 'Y' then 2 -- Avoid OPM and Process Costing
     when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
     else 2
  end cost_group_accounting,
  -- End revision for version 1.21
  mp.primary_cost_method,
  mp.default_cost_group_id,
  haou.date_to disable_date,
  gl.currency_code
  from
  mtl_category_accounts mca,
  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
  where mp.organization_id              = mca.organization_id (+)
  -- Avoid the item master organization
  and 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
  and 1=1                             -- p_operating_unit, p_ledger
  and 2=2                             -- p_org_code
  group by
  nvl(gl.short_name, gl.name),
  gl.ledger_id,
  haou2.name, -- operating_unit
  haou2.organization_id, -- operating_unit_id
  mp.organization_code,
  mp.organization_id,
  mca.organization_id, -- category_organization_id
  -- Revision for version 1.18
  mca.category_set_id,
  mp.material_account,
  -- Revision for version 1.21
  -- mp.cost_group_accounting,
  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 mp.primary_cost_method in (2,5,6) then 1 -- Average, FIFO or LIFO use Cost Group Accounting
     when nvl(mp.process_enabled_flag, 'N') = 'Y' then 2 -- Avoid OPM and Process Costing
     when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting
     else 2
  end, -- cost_group_accounting
  -- End revision for version 1.21
  mp.primary_cost_method,
  mp.default_cost_group_id,
  haou.date_to,
  gl.currency_code
 ),
-- Get the inventory valuation accounts by organization, subinventory and category
valuation_accounts as
 (-- Standard Costing, no Cost Group Accounting
  select 'Std Cost No Cost Group Accounting' valuation_type,
  msub.organization_id,
  msub.secondary_inventory_name,
  null category_id,
  null category_set_id,
  msub.material_account,
  msub.asset_inventory,
  msub.quantity_tracked,
  msub.default_cost_group_id cost_group_id
  from mtl_secondary_inventories msub,
  inv_organizations mp
  where msub.organization_id = mp.organization_id
  and nvl(mp.cost_group_accounting,2) = 2 -- No
  -- Avoid organizations with category accounts
  and mp.category_organization_id is null
  and 3=3                             -- p_subinventory
  -- Revision for version 1.20
  -- Causing duplicate rows with Average Costing
  -- union all
  -- -- Not Standard Costing, no Cost Group Accounting
  -- select 'Not Std Cost No Cost Group Accounting' valuation_type,
  --  msub.organization_id,
  --  msub.secondary_inventory_name,
  --  null category_id,
  --  null category_set_id,
  --  mp.material_account,
  --  msub.asset_inventory,
  --  msub.quantity_tracked,
  --  msub.default_cost_group_id cost_group_id
  -- from mtl_secondary_inventories msub,
  --  inv_organizations mp
  -- where msub.organization_id = mp.organization_id
  -- and nvl(mp.cost_group_accounting,2) = 2 -- No
  -- and mp.primary_cost_method         <> 1 -- not Standard Costing
  -- -- Avoid organizations with category accounts
  -- and mp.category_organization_id is null
  -- End revision for version 1.20
  union all
  -- With Cost Group Accounting
  select 'Cost Group Accounting' valuation_type,
  msub.organization_id,
  msub.secondary_inventory_name,
  null category_id,
  null category_set_id,
  ccga.material_account,
  msub.asset_inventory,
  msub.quantity_tracked,
  msub.default_cost_group_id cost_group_id
  from mtl_secondary_inventories msub,
  cst_cost_group_accounts ccga,
  cst_cost_groups ccg,
  inv_organizations mp
  where msub.organization_id            = mp.organization_id
  and mp.cost_group_accounting        = 1 -- Yes
  -- Avoid organizations with category accounts
  and mp.category_organization_id is null
  and ccga.cost_group_id              = nvl(msub.default_cost_group_id, mp.default_cost_group_id)
  and ccga.cost_group_id              = ccg.cost_group_id
  and ccga.organization_id            = mp.organization_id
  and 3=3                             -- p_subinventory
  union all
  -- Category Accounting
  -- Revision for version 1.19
  select 'Category Accounting' valuation_type,
  mp.organization_id,
  cat_subinv.subinventory_code secondary_inventory_name,
  mc.category_id,
  mp.category_set_id,
  cat_subinv.material_account,
  cat_subinv.asset_inventory,
  cat_subinv.quantity_tracked,
  cat_subinv.cost_group_id
  from inv_organizations mp,
  mtl_categories_b mc,
  mtl_category_sets_b mcs,
  mtl_item_categories mic,
  (select msub.organization_id,
   nvl(mca.subinventory_code, msub.secondary_inventory_name) subinventory_code,
   mca.category_id,
   mp.category_set_id,
   mca.material_account,
   msub.asset_inventory,
   msub.quantity_tracked,
   msub.default_cost_group_id cost_group_id
   from mtl_secondary_inventories msub,
   mtl_category_accounts mca,
   inv_organizations mp
   where msub.organization_id            = mp.organization_id
   and msub.organization_id            = mca.organization_id (+)
   -- Revision for version 1.19
   -- and msub.secondary_inventory_name   = mca.subinventory_code (+)
   and msub.secondary_inventory_name   = nvl(mca.subinventory_code, msub.secondary_inventory_name)
   -- Only get organizations with category accounts
   and mp.category_organization_id is not null
   and 3=3                             -- p_subinventory
   -- For a given category_id, if a subinventory-specific category account exists
   -- exclude the category account with a null subinventory, to avoid double-counting
   and not exists
    (select 'x'
     from mtl_category_accounts mca2
     where mca.subinventory_code is null
     and mca2.subinventory_code is not null
     and mca2.organization_id = mca.organization_id
     and mca2.category_id     = mca.category_id
    )
   group by
   msub.organization_id,
   nvl(mca.subinventory_code, msub.secondary_inventory_name),
   mca.category_id,
   mp.category_set_id,
   mca.material_account,
   msub.asset_inventory,
   msub.quantity_tracked,
   msub.default_cost_group_id
  ) cat_subinv
  where mp.organization_id              = mic.organization_id
  and mp.category_set_id              = mic.category_set_id
  and mic.category_id                 = mc.category_id
  and mic.category_set_id             = mcs.category_set_id
  and mc.category_id                  = mic.category_id
  and mic.organization_id             = cat_subinv.organization_id (+)
  and mic.category_id                 = cat_subinv.category_id (+)
  group by
  'Category Accounting',
  mp.organization_id,
  cat_subinv.subinventory_code,
  mc.category_id,
  mp.category_set_id,
  cat_subinv.material_account,
  cat_subinv.asset_inventory,
  cat_subinv.quantity_tracked,
  cat_subinv.cost_group_id
  -- End revision for version 1.19
  union all
  select 'Intransit Accounting' valuation_type,
  interco.organization_id,
  'Intransit' secondary_inventory_name,
  null category_id,
  null category_set_id,
  interco.intransit_inv_account material_account,
  1 asset_inventory,
  1 quantity_tracked,
  mp.default_cost_group_id cost_group_id
  from inv_organizations mp,
  (select ic.intransit_inv_account,
   ic.organization_id
   from (select mip.intransit_inv_account,
    mip.to_organization_id organization_id
    from mtl_interorg_parameters mip,
    inv_organizations mp
    where mip.fob_point               = 1 -- shipment
    and mp.organization_id          = mip.to_organization_id
    union all
    select mip.intransit_inv_account,
    mip.from_organization_id organization_id
    from mtl_interorg_parameters mip,
    inv_organizations mp
    where mip.fob_point               = 2 -- receipt
    and mp.organization_id          = mip.from_organization_id
   ) ic
   group by
   ic.intransit_inv_account,
   ic.organization_id
  ) interco
  where mp.organization_id = interco.organization_id
 ),
non_cat_acc_onhand as
 (-- For non-category accounting
  select mp.organization_id,
  msiv.inventory_item_id,
  msiv.concatenated_segments,
  -- Revision for version 1.19
  regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description,
  msiv.primary_uom_code,
  msiv.inventory_item_status_code,
  msiv.item_type,
  msiv.inventory_asset_flag,
  oap.period_name,
  oap.open_flag || ':' || oap.summarized_flag period_status,
  cpcs.acct_period_id,
  nvl(cpcs.subinventory_code, 'Intransit') subinventory_code,
  sum(cpcs.rollback_quantity) rollback_quantity,
  sum(cpcs.rollback_value) rollback_value,
  sum(cpcs.rollback_intransit_value) rollback_intransit_value
  from mtl_system_items_vl msiv,
  cst_period_close_summary cpcs,
  org_acct_periods oap,
  inv_organizations mp
  where mp.organization_id              = msiv.organization_id
  and oap.acct_period_id              = cpcs.acct_period_id
  and oap.organization_id             = mp.organization_id
  and msiv.organization_id            = cpcs.organization_id
  and msiv.inventory_item_id          = cpcs.inventory_item_id
  and mp.category_organization_id is null
  -- Don't get zero quantities
  and nvl(cpcs.rollback_quantity,0)  <> 0
  -- Don't report expense items
  and msiv.inventory_asset_flag       = 'Y'
  and lower(oap.period_name) = lower(:p_period_name)
  -- Need to group by due to possibility for having multiple cost groups by subinventory
  group by
  mp.organization_id,
  msiv.inventory_item_id,
  msiv.concatenated_segments,
  regexp_replace(msiv.description,'[^[:alnum:]'' '']', null),
  msiv.primary_uom_code,
  msiv.inventory_item_status_code,
  msiv.item_type,
  msiv.inventory_asset_flag,
  oap.period_name,
  oap.open_flag || ':' || oap.summarized_flag,
  cpcs.acct_period_id,
  cpcs.subinventory_code
 ),
cat_acc_onhand as
 (-- This onhand inner query is for category accounting
  select onhand2.organization_id,
  onhand2.category_organization_id,
  onhand2.category_set_id,
  mic.category_id,
  onhand2.inventory_item_id,
  onhand2.concatenated_segments,
  onhand2.description,
  onhand2.primary_uom_code,
  onhand2.inventory_item_status_code,
  onhand2.item_type,
  onhand2.period_name,
  onhand2.period_status,
  onhand2.subinventory_code,
  sum(onhand2.rollback_quantity) rollback_quantity,
  sum(onhand2.rollback_value) rollback_value,
  sum(onhand2.rollback_intransit_value) rollback_intransit_value
  from mtl_item_categories mic,
  -- Inner select to have consistent outer joins with categories
  (select mp.organization_id,
   mp.category_set_id,
   mp.category_organization_id,
   msiv.inventory_item_id,
   msiv.concatenated_segments,
   regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description,
   msiv.primary_uom_code,
    msiv.inventory_item_status_code,
   msiv.item_type,
   oap.period_name,
   oap.open_flag || ':' || oap.summarized_flag period_status,
   nvl(cpcs.subinventory_code, 'Intransit') subinventory_code,
   sum(cpcs.rollback_quantity) rollback_quantity,
   sum(cpcs.rollback_value) rollback_value,
   sum(cpcs.rollback_intransit_value) rollback_intransit_value
   from mtl_system_items_vl msiv,
   cst_period_close_summary cpcs,
   org_acct_periods oap,
   inv_organizations mp
   where mp.organization_id              = msiv.organization_id
   and mp.category_organization_id is not null
   and oap.organization_id             = mp.organization_id
   and oap.acct_period_id              = cpcs.acct_period_id
   and cpcs.organization_id            = msiv.organization_id
   and cpcs.inventory_item_id          = msiv.inventory_item_id
   -- Don't get zero quantities
   and nvl(cpcs.rollback_quantity,0)  <> 0
   and lower(oap.period_name) = lower(:p_period_name)
   group by
   mp.organization_id,
   mp.category_set_id,
   mp.category_organization_id,
   msiv.inventory_item_id,
   msiv.concatenated_segments,
   regexp_replace(msiv.description,'[^[:alnum:]'' '']', null),
   msiv.primary_uom_code,
    msiv.inventory_item_status_code,
   msiv.item_type,
   oap.period_name,
   oap.open_flag || ':' || oap.summarized_flag,
   nvl(cpcs.subinventory_code, 'Intransit') -- subinventory_code
  ) onhand2
  where mic.inventory_item_id (+)       = onhand2.inventory_item_id
  and mic.organization_id (+)         = onhand2.organization_id
  and mic.category_set_id (+)         = onhand2.category_set_id
  -- Need to group by due to possibility for having multiple cost groups by subinventory
  group by
  onhand2.organization_id,
  onhand2.category_organization_id,
  onhand2.category_set_id,
  mic.category_id,
  onhand2.inventory_item_id,
  onhand2.concatenated_segments,
  onhand2.description,
  onhand2.primary_uom_code,
  onhand2.inventory_item_status_code,
  onhand2.item_type,
  onhand2.period_name,
  onhand2.period_status,
  onhand2.subinventory_code
 )
-- End revision for version 1.17
--
----------------main query starts here--------------
--
select
  1 record_group,
  mp.ledger,
  mp.ledger_id,
  mp.operating_unit,
  mp.operating_unit_id,
  mp.organization_code,
  mp.organization_id,
  mp.category_organization_id,
  mp.category_set_id,
  mp.material_account,
  mp.cost_group_accounting,
  mp.primary_cost_method,
  mp.default_cost_group_id,
  mp.disable_date,
  mp.currency_code,
  --
  va.valuation_type,
  va.organization_id va_organization_id,
  va.secondary_inventory_name,
  va.category_id va_category_id,
  va.category_set_id va_category_set_id,
  va.material_account va_material_account,
  va.asset_inventory,
  va.quantity_tracked,
  va.cost_group_id,
  (select
   oap.period_name
   from
   org_acct_periods oap
   where
   oap.organization_id = mp.organization_id and
   lower(oap.period_name) = lower(:p_period_name)
  ) period_name,
  nvl(
  (select
   oap.open_flag || ':' || oap.summarized_flag
   from
   org_acct_periods oap
   where
   oap.organization_id = mp.organization_id and
   lower(oap.period_name) = lower(:p_period_name)
  ),'*No Period*') period_status,
  null subinventory_code,
  to_number(null) oh_category_organization_id,
  to_number(null) oh_category_set_id,
  to_number(null) oh_category_id,
  (select
    count(cpcs.inventory_item_id)
   from
     cst_period_close_summary cpcs,
     org_acct_periods oap
   where
    oap.organization_id = mp.organization_id and
    oap.acct_period_id  = cpcs.acct_period_id and
    oap.organization_id = cpcs.organization_id and
    nvl(cpcs.rollback_quantity,0) <> 0 and
    lower(oap.period_name) = lower(:p_period_name)
  ) item_count,
  (select
    sum(cpcs.rollback_quantity)
   from
     cst_period_close_summary cpcs,
     org_acct_periods oap
   where
    oap.organization_id = mp.organization_id and
    oap.acct_period_id  = cpcs.acct_period_id and
    oap.organization_id = cpcs.organization_id and
    nvl(cpcs.rollback_quantity,0) <> 0 and
    lower(oap.period_name) = lower(:p_period_name)
  ) rollback_quantity,
  to_number(null) rollback_value,
  to_number(null) rollback_intransit_value
from
  inv_organizations   mp,
  valuation_accounts  va
where
  mp.organization_id = va.organization_id (+)
union all
select
  2 record_group,
  mp.ledger,
  mp.ledger_id,
  mp.operating_unit,
  mp.operating_unit_id,
  mp.organization_code,
  mp.organization_id,
  mp.category_organization_id,
  mp.category_set_id,
  mp.material_account,
  mp.cost_group_accounting,
  mp.primary_cost_method,
  mp.default_cost_group_id,
  mp.disable_date,
  mp.currency_code,
  --
  va.valuation_type,
  va.organization_id va_organization_id,
  va.secondary_inventory_name,
  va.category_id va_category_id,
  va.category_set_id va_category_set_id,
  va.material_account va_material_account,
  va.asset_inventory,
  va.quantity_tracked,
  va.cost_group_id,
  --
  onhand.period_name,
  nvl(onhand.period_status,'*No Period*'),
  onhand.subinventory_code,
  to_number(null) oh_category_organization_id,
  to_number(null) oh_category_set_id,
  to_number(null) oh_category_id,
  count(onhand.inventory_item_id) item_count,
  sum(onhand.rollback_quantity) rollback_quantity,
  sum(onhand.rollback_value) rollback_value,
  sum(onhand.rollback_intransit_value) rollback_intransit_value
from
  inv_organizations   mp,
  non_cat_acc_onhand  onhand,
  valuation_accounts  va
where
  mp.organization_id        = onhand.organization_id (+) and
  onhand.subinventory_code  = va.secondary_inventory_name (+) and
  onhand.organization_id    = va.organization_id (+)  and
  va.valuation_type (+) <> 'Category Accounting'
group by
  mp.ledger,
  mp.ledger_id,
  mp.operating_unit,
  mp.operating_unit_id,
  mp.organization_code,
  mp.organization_id,
  mp.category_organization_id,
  mp.category_set_id,
  mp.material_account,
  mp.cost_group_accounting,
  mp.primary_cost_method,
  mp.default_cost_group_id,
  mp.disable_date,
  mp.currency_code,
  --
  va.valuation_type,
  va.organization_id,
  va.secondary_inventory_name,
  va.category_id,
  va.category_set_id,
  va.material_account,
  va.asset_inventory,
  va.quantity_tracked,
  va.cost_group_id,
  --
  onhand.period_name,
  nvl(onhand.period_status,'*No Period*'),
  onhand.subinventory_code
union all
select
  3 record_group,
  mp.ledger,
  mp.ledger_id,
  mp.operating_unit,
  mp.operating_unit_id,
  mp.organization_code,
  mp.organization_id,
  mp.category_organization_id,
  mp.category_set_id,
  mp.material_account,
  mp.cost_group_accounting