ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A371EBC32C06FA76E053BB6B63586DBFY select nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, ottt.name Order_Type_Name, otta.transaction_type_id Order_Type_Id, otta.transaction_type_code Transaction_Type_Code, otta.order_category_code Order_Category, otta.currency_code Currency_Code, otta.start_date_active Start_Date, otta.end_date_active End_Date, rctt.name AR_Transaction_Name, rctt.description AR_Transaction_Description, rctt.type AR_Transaction_Type, &segment_columns -- Revision for version 1.8 &segment_columns2 -- Revision for version 1.7 otta.creation_date Creation_Date, otta.last_update_date Last_Update_Date from oe_transaction_types_tl ottt, oe_transaction_types_all otta, gl_code_combinations gcc1, -- COGS_Accounts -- Revision for version 1.8 gl_code_combinations gcc2, -- Sales_Accounts -- Revision for version 1.3 -- Revision for version 1.1 -- ra_cust_trx_types rctt, (select rctt.cust_trx_type_id, rctt.org_id, rctt.name, rctt.description, rctt.type, rctt.gl_id_rev from ra_cust_trx_types_all rctt ) rctt, hr_organization_information hoi, hr_all_organization_units_vl haou, -- operating unit gl_ledgers gl where ottt.transaction_type_id = otta.transaction_type_id and ottt.language = userenv('lang') and gcc1.code_combination_id (+)= otta.cost_of_goods_sold_account -- Revision for version 1.8 and gcc2.code_combination_id (+)= rctt.gl_id_rev -- Revision for version 1.2 and otta.cust_trx_type_id = rctt.cust_trx_type_id (+) and otta.transaction_type_code <> 'LINE' -- Revision for version 1.6 and otta.org_id = rctt.org_id -- Revision for version 1.7 and hoi.org_information_context = 'Operating Unit Information' and hoi.organization_id = haou.organization_id -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information3) -- this joins OU to GL -- End revision for version 1.7 and haou.organization_id = otta.org_id -- this gets the operating unit 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 haou.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_operating_unit, p_ledger order by 1,2,3,4,5,7 Added operating unit security 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. -- +=============================================================================+*/ 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 8E2FF36EDEE379D2E0530100007F1FF2 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 gl.chart_of_accounts_id from gl_ledgers gl where 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(+))) 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 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 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 haou.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 20 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