ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A0462A72D596FBCFE053BB6B635826CDY select y.account_group, y.account_type, y.ledger, y.operating_unit, y.org_code, y.organization_name, &segment_columns y.last_update_date from ( select case when rowgen.column_value<=5 then 'Inter-org' when rowgen.column_value<=7 then 'Profit and Loss' when rowgen.column_value<=10 then 'Other Accounts' when rowgen.column_value=12 then 'Shipping' when rowgen.column_value<=16 then 'Receiving' else 'Valuation' end account_group, decode(rowgen.column_value, 1,'Inter-Org A/P Account', 2,'Inter-Org A/R Account', 3,'Intransit Account', 4,'Inter-Org PPV Account', 5,'Inter-Org Xfer Credit Account', 6,'Cost of Goods Sold Account', 7,'Sales Account', 8,'Deferred COGS Account', 9,'Expense Account', 10,'Avg Cost Variance Account', 11,'Inv. A/P Accrual Account', 12,'Goods Dispatched Account', 13,'IPV Account', 14,'PPV Account', 15,'Clearing Account', 16,'Expense A/P Accrual Account', 17,'Material Value Account', 18,'Matl Ovhd Value Account', 19,'Resource Value Account', 20,'OSP Value Account', 21,'Overhead Value Account', 22,'Receiving Value Account' ) account_type, decode(rowgen.column_value, 1,mp.interorg_payables_account, 2,mp.interorg_receivables_account, 3,mp.intransit_inv_account, 4,mp.interorg_price_var_account, 5,mp.interorg_transfer_cr_account, 6,mp.cost_of_sales_account, 7,mp.sales_account, 8,mp.deferred_cogs_account, 9,mp.expense_account, 10,mp.average_cost_var_account, 11,mp.ap_accrual_account, 12,wsp.goods_dispatched_account, 13,mp.invoice_price_var_account, 14,mp.purchase_price_var_account, 15,rp.clearing_account_id, 16,psp.accrued_code_combination_id, 17,mp.material_account, 18,mp.material_overhead_account, 19,mp.resource_account, 20,mp.outside_processing_account, 21,mp.overhead_account, 22,rp.receiving_account_id ) code_combination_id, nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, haouv.name organization_name, mp.last_update_date, rowgen.column_value sort_order from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, wsh_shipping_parameters wsp, rcv_parameters rp, po_system_parameters_all psp, table(xxen_util.rowgen(22)) rowgen where 1=1 and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haouv.organization_id and sysdate<nvl(haouv.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=wsp.organization_id(+) and mp.organization_id=rp.organization_id(+) and haouv_ou.organization_id=psp.org_id(+) and (rowgen.column_value<>12 or wsp.organization_id is not null) and (rowgen.column_value not in (15,22) or rp.organization_id is not null) and (rowgen.column_value<>16 or psp.org_id is not null and mp.master_organization_id=mp.organization_id) ) y, gl_code_combinations gcc where (:p_account_group is null or upper(y.account_group)=upper(:p_account_group)) and y.code_combination_id=gcc.code_combination_id(+) order by y.sort_order, y.ledger, y.operating_unit, y.org_code Rebuilt to the Blitz Report SQL standards: one pass over the organizations instead of 23 queries, the Account Group parameter selects Deferred COGS under Other Accounts and Goods Dispatched under Shipping (they were selected by Profit and Loss and Other Accounts) and accepts any capitalization, the Sales Account and Deferred COGS Account labels lost their underscores. Setup report without organization security, so that every user sees the setup of all organizations. US CAC Inventory Accounts Setup 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 (doug@volzconsulting.com) -- | -- | 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. -- | 1.15 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &segment_columns 1=1 :p_account_group 1 -100 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) Y US Chart of Accounts 2 10 :p_account_group LOV custom select 'Inter-Org' value, 'Inter-Org Accounts Group' description from dual union all select 'Other Accounts' value, 'Other Accounts Group' description from dual union all select 'Profit and Loss' value, 'Profit and Loss Accounts Group' description from dual union all select 'Receiving' value, 'Receiving Accounts Group' description from dual union all select 'Valuation' value, 'Valuation Accounts Group' description from dual union all select null value, null description from dual order by 1 US Account Group 3 20 1=1 mp.organization_code=:p_org_code LOV custom select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 4 30 1=1 haouv_ou.name=:p_operating_unit LOV HR Operating Unit (all) 8E2FF36EDE8879D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) order by hou.name US Operating Unit 5 40 1=1 gl.name=:p_ledger LOV GL Ledger (all) A95C471001B20DD1E0530100007FBF83 select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and gl.object_type_code='L' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',4), gl.name US Ledger