ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A371EBC32C06FA76E053BB6B63586DBFY select nvl(gl.short_name,gl.name) ledger, haouv.name operating_unit, ottt.name order_type_name, otta.transaction_type_id order_type_id, xxen_util.meaning(otta.transaction_type_code,'TRANSACTION_TYPE',660) transaction_type_code, xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) order_category, otta.currency_code, otta.start_date_active start_date, otta.end_date_active end_date, rctta.name ar_transaction_name, rctta.description ar_transaction_description, xxen_util.meaning(rctta.type,'INV/CM',222) ar_transaction_type, &segment_columns &segment_columns2 otta.creation_date, otta.last_update_date from oe_transaction_types_all otta, oe_transaction_types_tl ottt, hr_all_organization_units_vl haouv, hr_organization_information hoi, gl_ledgers gl, ra_cust_trx_types_all rctta, gl_code_combinations gcc1, gl_code_combinations gcc2 where 1=1 and otta.transaction_type_code<>'LINE' and otta.transaction_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') and otta.org_id=haouv.organization_id and haouv.organization_id=hoi.organization_id and hoi.org_information_context='Operating Unit Information' and to_number(hoi.org_information3)=gl.ledger_id and otta.cust_trx_type_id=rctta.cust_trx_type_id(+) and otta.org_id=rctta.org_id(+) and otta.cost_of_goods_sold_account=gcc1.code_combination_id(+) and rctta.gl_id_rev=gcc2.code_combination_id(+) order by ledger, operating_unit, order_type_name, order_type_id, otta.transaction_type_code, otta.currency_code Rebuilt to the Blitz Report SQL standards. Order types without a receivables transaction type are reported, codes show their meaning. Setup report without organization security, so that every user sees the setup of all organizations. US CAC Order Type Setup Report to display the sales order transaction types with the corresponding receivables (A/R) transaction types. /* +=============================================================================+ -- | Copyright 2016 - 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_oe_transaction_types.sql -- | -- | Parameters: -- | 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 display the sales order transaction types with the corresponding -- | receivables (A/R) transaction types. -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 15 Nov 2016 Douglas Volz Initial Coding -- | 1.1 09 Jan 2017 Douglas Volz Added description and Receivables Txn Type -- | 1.2 11 Jan 2017 Douglas Volz Added Order Category, Order Type (DFF), -- | and Disti Plus Pricing (DFF), and add a -- | new section for Order Types with no COGS -- | account. -- | 1.3 14 Mar 2017 Douglas Volz Changed apps.ra_cust_trx_types to -- | apps.ra_cust_trx_types_all, added A/R -- | revenue account -- | 1.4 23 Aug 2017 Douglas Volz Add user-defined field (descriptive flex- -- | field) for the COGS ICP account, a contra- -- | account which is used to record the ICP -- | portion of the COGS entry. -- | 1.5 13 Mar 2019 Douglas Volz Added Operating Unit Parameter. -- | 1.6 22 May 2019 Douglas Volz Added missing ORG_ID join -- | 1.7 14 Apr 2020 Douglas Volz Added creation date to report -- | 1.8 16 Apr 2020 Douglas Volz Moved sales ccid joins to outer query -- | and added Ledger parameter. -- | 1.9 28 Apr 2020 Douglas Volz Changed to multi-language views for the -- | inventory orgs and operating units. -- | 1.10 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &segment_columns &segment_columns2 1=1 1 -100 &segment_columns select 'gcc1.'||lower(fifsv.application_column_name)||' "COGS '||substrb(fifsv.form_left_prompt,1,21)||'",' text from fnd_id_flex_segments_vl 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 in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER')) and rownum=1 Y US Chart of Accounts 2 &segment_columns2 select 'gcc2.'||lower(fifsv.application_column_name)||' "Rev '||substrb(fifsv.form_left_prompt,1,21)||'",' text from fnd_id_flex_segments_vl 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 US Chart of Accounts 3 10 1=1 haouv.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 4 20 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