CAC Item Master Accounts Setup

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to show item master accounts and related information by item.

/* +=============================================================================+
-- | Copyright 2011 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original ...  Report to show item master accounts and related information by item.

/* +=============================================================================+
-- | Copyright 2011 - 2022 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_item_master_accts_rept.sql
-- |
-- | Parameters:
-- | p_include_non_costed_items -- Yes/No flag to include or not include non-costed items
-- | p_item_number -- Enter the specific inventory organization code
-- | 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)
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- |
-- | Description:
-- | Report to show item master accounts and related information
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 30 Mar 2011 Douglas Volz Initial Coding
-- | 1.1 18 Nov 2012 Douglas Volz Removed client-specific org conditions
-- | 1.2 12 Feb 2013 Douglas Volz Changed inventory category to be more generic
-- | 1.3 16 Nov 2015 Douglas Volz Modified for client's chart of accounts
-- | 1.4 21 Feb 2017 Douglas Volz Modified for client's chart of accounts and
-- | added parameters to this report
-- | 1.5 17 Jul 2018 Douglas Volz Modified for client's chart of accounts and
-- | modified to use the default category for Inventory
-- | 1.6 12 Jan 2019 Douglas Volz Use gl short_name, modify for any two categories,
-- | and add operating unit parameter.
-- | 1.7 28 Apr 2020 Douglas Volz Changed to multi-language views for the
-- | inventory orgs and operating units.
-- | 1.8 29 Apr 2022 Douglas Volz Modify summary report into the detailed report.
-- +=============================================================================+*/   more
Run CAC Item Master Accounts Setup and other Oracle EBS reports with Blitz Report™ on our demo environment
select
gl.name ledger,
haouv.name operating_unit,
msiv.organization_code,
msiv.segment1 item_number,
msiv.description item_description,
msiv.primary_uom_code uom_code,
&category_columns
misv.inventory_item_status_code_tl item_status,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy,
xxen_util.meaning(msiv.costing_enabled_flag,'YES_NO',0) allow_costs,
xxen_util.meaning(cic.inventory_asset_flag,'SYS_YES_NO',700) inventory_asset,
xxen_util.meaning(cic.based_on_rollup_flag,'SYS_YES_NO',700) based_on_cost_rollup,
cic.shrinkage_rate,
cct.cost_type,
gl.currency_code,
cic.item_cost,
&segment_columns
xxen_util.user_name(msiv.created_by) item_created_by,
xxen_util.client_time(msiv.creation_date) item_creation_date,
xxen_util.user_name(msiv.last_updated_by) item_last_updated_by,
xxen_util.client_time(msiv.last_update_date) item_last_update_date
from
gl_ledgers gl,
org_organization_definitions ood,
hr_all_organization_units_vl haouv,
(
select
mp.organization_code,
mp.primary_cost_method,
msiv.*
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_id<>mp.master_organization_id and
mp.organization_id=msiv.organization_id
) msiv,
mtl_item_status_vl misv,
cst_item_costs cic,
cst_cost_types cct,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
gl_code_combinations gcc3
where
1=1 and
gl.ledger_id=ood.set_of_books_id and
ood.organization_id=msiv.organization_id and
msiv.inventory_item_status_code=misv.inventory_item_status_code(+) and
msiv.organization_id=cic.organization_id(+) and
msiv.inventory_item_id=cic.inventory_item_id(+) and
decode(msiv.costing_enabled_flag,'Y',msiv.primary_cost_method)=cic.cost_type_id(+) and
decode(msiv.costing_enabled_flag,'Y',msiv.primary_cost_method)=cct.cost_type_id(+) and
ood.operating_unit=haouv.organization_id and
msiv.cost_of_sales_account=gcc1.code_combination_id(+) and 
msiv.sales_account=gcc2.code_combination_id(+) and
msiv.expense_account=gcc3.code_combination_id(+)
order by 1,2,3,4,5
Parameter Name SQL text Validation
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
 LOV
Item 
msiv.concatenated_segments like :item
 LOV
Include Non-Costed Items 
msiv.costing_enabled_flag='Y'
 LOV Oracle
Organization Code 
msiv.organization_code=:organization_code
 LOV
Operating Unit 
haouv.name=:operating_unit
 LOV
Ledger 
gl.name=:ledger
 LOV
Download
Related Blitz Reports
CAC Item Cost Summary CAC New Items CAC Onhand Lot Value (Real-Time) CAC Open Purchase Orders CAC ICP PII Item Cost Summary CAC Missing Material Accounting Transactions CAC Where Used by Cost Type CAC PO Receipt History for Item Costing CAC Item List Price vs. Item Cost
Blitz Report™

Blitz Report™ provides multiple benefits: