ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 71AE847F746D3151E0536109825E6409Y select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, haou.name Organization_Name, mgd.segment1 Account_Alias, mgd.description Description, &segment_columns mgd.effective_date Effective_Date, mgd.disable_date Disable_Date, mgd.disposition_id Alias_Id from mtl_generic_dispositions mgd, mtl_parameters mp, gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl where mgd.distribution_account = gcc.code_combination_id and mgd.organization_id = mp.organization_id -- =========================================== -- Organization joins for the HR org model -- =========================================== 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 -- Revision for version 1.1 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) 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_id=glsnav.ledger_set_id(+)) and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and 1=1 -- p_ledger, p_operating_unit, p_org_code -- End revision for version 1.1 order by nvl(gl.short_name, gl.name), -- Ledger haou2.name, -- Operating_Unit mp.organization_code, -- Org_Code haou.name, -- Organization_Name mgd.segment1 -- Account_Alias Added operating unit security US CAC Inventory Account Alias Setup Report to show accounts assigned for inventory account aliases /* +=============================================================================+ -- | Copyright 2011 - 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_acct_alias_setup_rept.sql -- | -- | Parameters: -- | p_ledger -- Ledger you wish to report, enter a null for all -- | ledgers. (optional) -- | -- | Description: -- | Report to show accounts used for account aliases -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 03 Feb 2011 Douglas Volz Initial Coding -- | 1.1 28 Mar 2011 Douglas Volz Added Ledger parameter, effective date -- | and disable date -- | 1.2 07 Oct 2015 Douglas Volz Removed prior client's org restrictions. -- | 1.3 27 Apr 2020 Douglas Volz Changed to multi-language views for the -- | inventory orgs and operating units. Enginatics &segment_columns 1=1 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 Y 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 1=1 mp.organization_code = :p_org_code LOV custom Y 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 ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_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 3 20 1=1 haou2.name = :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y 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')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name US Operating Unit 4 30 1=1 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y 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$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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_id=glsnav.ledger_set_id(+)) 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,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger