ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEC879D2E0530100007F1FF2 GL Chart of Accounts (inv org related) 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 AR دليل الحسابات المتعلقة بمنظمات المخزون D Kontenplan für Bestandsorganisationen E Plan de cuentas relacionado con las organizaciones de inventario F Plan comptable relatif aux organismes d'inventaire I Piano dei conti relativo alle organizzazioni dell'inventario JA 棚卸し組織に関する勘定科目一覧表 KO 재고 조직과 관련된 계정과 목표 PTB Plano de contas relacionado às organizações de inventário RU План счетов организаций, занимающихся инвентаризацией S Kontoplan relaterade till lagerorganisationer TR Envanter organizasyonları ile ilgili hesap planı US Chart of accounts related to inventory organizations ZHS 与库存组织有关的会计科目表 8E2FF36EDF3A79D2E0530100007F1FF2 GL Ledger (inv org related) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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, gl.name AR جميع دفاتر الأستاذ المتعلقة بجميع منظمات المخزون النشطة D Alle Ledger, die sich auf alle aktiven Inventarorganisationen beziehen E Todos los libros de contabilidad relacionados con todas las organizaciones de inventario activas F Tous les grands livres relatifs à tous les organismes d'inventaire actifs I Tutti i libri contabili relativi a tutte le organizzazioni di inventario attive JA すべてのアクティブな在庫組織に関連するすべての台帳 KO 모든 활성 재고 조직과 관련된 모든 원장 PTB Todos os livros contábeis relacionados a todas as organizações de inventário ativas RU Все бухгалтерские книги, относящиеся ко всем организациям, занимающимся активным инвентаризацией. S Alla huvudböcker relaterade till alla aktiva lagerorganisationer TR Tüm aktif envanter organizasyonlarıyla ilgili tüm defterler US All ledgers related to all active inventory organizations ZHS 与所有现行清单组织有关的所有分类账 31341B624172B711E06369FB0905676C INV Account Alias Y select mgd.segment1 value, mgd.description description from mtl_generic_dispositions mgd, org_organization_definitions ood where mgd.organization_id = ood.organization_id and (mgd.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 nvl(ood.disable_date,sysdate)>=sysdate group by mgd.segment1, mgd.description order by mgd.segment1 US List of values for the Inventory Account Alias. 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US EC5CECA520510FC8E0530100007F5EB3 INV Item Number (costing enabled only items) Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null 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 (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US 9B9627743E84607DE053BB6B635805FB INV Operating Unit Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 AR جميع وحدات التشغيل المرتبطة بمؤسسة مخزون غير رئيسية نشطة D Alle Betriebseinheiten, die mit einer aktiven Nicht-Master-Inventarisierungsorganisation verbunden sind E Todas las unidades operativas relacionadas con una organización de inventario no maestra activa F Toutes les unités opérationnelles liées à un organisme d'inventaire non maître actif I Tutte le unità operative legate a un'organizzazione di inventario non master attiva JA アクティブな非マスターインベントリ組織に関連するすべてのオペレーティング・ユニット KO 활성 비 마스터 재고 조직과 관련된 모든 운영 단위 PTB Todas as unidades operacionais relacionadas a uma organização ativa de inventário não-mestre RU Все операционные единицы, относящиеся к действующей организации, не являющейся ведущим поставщиком инвентарных запасов. S Alla operativa enheter relaterade till en aktiv organisation som inte är huvudlager TR Etkin bir ana envanter organizasyonu ile ilgili tüm işletim birimleri US All operating units related to an active non master inventory organization ZHS 与在用的非总清单组织有关的所有业务单位： 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) 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 (:$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 AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 3104A2840AEF37DDE0630100007F093A with inv_organizations as -- Get the list of organizations, ledgers and operating units for Discrete and OPM organizations (select nvl(gl.short_name, gl.name) ledger, gl.ledger_id, to_number(hoi.org_information2) legal_entity_id, haou2.name operating_unit, haou2.organization_id operating_unit_id, mp.organization_code, mp.organization_id, nvl(mp.process_enabled_flag, 'N') process_enabled_flag, haou.date_to disable_date, gl.currency_code from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl -- Avoid the item master organization where mp.organization_id <> mp.master_organization_id -- Avoid disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate +1) 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 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 1=1 -- p_org_code, p_operating_unit, p_ledger -- Revision for Operating Unit and Ledger Controls and Parameters and (nvl(fnd_profile.value('XXEN_REPORT_USE_LEDGER_SECURITY'),'N')='N' or 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 (nvl(fnd_profile.value('XXEN_REPORT_USE_OPERATING_UNIT_SECURITY'),'N')='N' or 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)) group by nvl(gl.short_name, gl.name), gl.ledger_id, to_number(hoi.org_information2), haou2.name, -- operating_unit haou2.organization_id, -- operating_unit_id mp.organization_code, mp.organization_id, nvl(mp.process_enabled_flag, 'N'), -- process_enabled_flag haou.date_to, gl.currency_code ) -- inv_organizations -----------------main query starts here-------------- select mmt_lot_txn.ledger Ledger, mmt_lot_txn.operating_unit Operating_Unit, mmt_lot_txn.organization_code Org_Code, oap.period_name Period_Name, gcc.concatenated_segments Accounts, &segment_columns fl.meaning Create_Accounting, msiv.concatenated_segments Item_Number, msiv.description Item_Description, fcl.meaning Item_Type, misv.inventory_item_status_code Item_Status, ml1.meaning Make_Buy_Code, &category_columns ml2.meaning Accounting_Line_Type, mtt.transaction_type_name Transaction_Type, mmt_lot_txn.account_alias Account_Alias, fu.user_name User_Name, mtr.reason_name Reason_Code, mmt_lot_txn.transaction_reference Transaction_Comments, mta_lot_amt.transaction_id Transaction_Id, mta_lot_amt.transaction_date Transaction_Date, &p_show_lot_num_col -- mmt_lot_txn.lot_number Lot_Number, decode(mta_lot_amt.accounting_line_type, 7, 'WIP', 14, '', 1, decode(mmt_lot_txn.transaction_action_id, 2, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 3, decode(mmt_lot_txn.organization_id, mta_lot_amt.organization_id, mmt_lot_txn.subinventory_code, mmt_lot_txn.transfer_subinventory), 21, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 22, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 28, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), mmt_lot_txn.subinventory_code)) Subinventory, muomv.uom_code UOM_Code, decode(sign(decode(mta_lot_amt.base_transaction_value, 0, mta_lot_amt.primary_quantity, mta_lot_amt.base_transaction_value)), -1, -1 * abs(mmt_lot_txn.primary_quantity), +1, +1 * abs(mmt_lot_txn.primary_quantity)) Quantity, mmt_lot_txn.currency_code Currency_Code, cce.cost_element Cost_Element, round(mta_lot_amt.unit_cost, 5) Item_Cost, round(decode(sign(decode(mta_lot_amt.base_transaction_value, 0, mta_lot_amt.primary_quantity, mta_lot_amt.base_transaction_value)), -1, -1 * abs(mmt_lot_txn.primary_quantity), +1, +1 * abs(mmt_lot_txn.primary_quantity)) * mta_lot_amt.unit_cost, 2) Amount from mtl_transaction_reasons mtr, mtl_transaction_types mtt, mtl_system_items_vl msiv, mtl_item_status_vl misv, mtl_units_of_measure_vl muomv, org_acct_periods oap, cst_cost_elements cce, gl_code_combinations_kfv gcc, fnd_user fu, mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY mfg_lookups ml2, -- accounting line type fnd_common_lookups fcl, fnd_lookups fl, -- This section for reporting lot numbers (select inv_orgs.ledger, inv_orgs.operating_unit, inv_orgs.organization_code, mmt.inventory_item_id, mmt.organization_id, mmt.acct_period_id, mmt.transaction_type_id, mmt.transaction_action_id, mmt.subinventory_code, mmt.transfer_subinventory, mmt.reason_id, mmt.transaction_reference, mmt.created_by, mgd.segment1 account_alias, mmt.transaction_id, mtln.lot_number, mmt.locator_id, mmt.actual_cost unit_cost, decode(mtln.primary_quantity, null, mmt.primary_quantity, mtln.primary_quantity) primary_quantity, inv_orgs.currency_code from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln, mtl_generic_dispositions mgd, inv_organizations inv_orgs where mmt.transaction_source_id = mgd.disposition_id and mmt.organization_id = inv_orgs.organization_id and mmt.transaction_source_type_id = 6 -- Account Alias and mmt.transaction_id = mtln.transaction_id (+) and nvl('&p_show_lot_number', 'N') = 'Y' and 2=2 -- p_trx_date_from, p_trx_date_to, p_acct_alias, p_item_number -- This section for not reporting lot numbers union all select inv_orgs.ledger, inv_orgs.operating_unit, inv_orgs.organization_code, mmt.inventory_item_id, mmt.organization_id, mmt.acct_period_id, mmt.transaction_type_id, mmt.transaction_action_id, mmt.subinventory_code, mmt.transfer_subinventory, mmt.reason_id, mmt.transaction_reference, mmt.created_by, mgd.segment1 account_alias, mmt.transaction_id, null lot_number, mmt.locator_id, mmt.actual_cost unit_cost, mmt.primary_quantity, inv_orgs.currency_code from mtl_material_transactions mmt, mtl_generic_dispositions mgd, inv_organizations inv_orgs where mmt.transaction_source_id = mgd.disposition_id and mmt.organization_id = inv_orgs.organization_id and mmt.transaction_source_type_id = 6 -- Account Alias and nvl('&p_show_lot_number', 'N') = 'N' and 2=2 -- p_trx_date_from, p_trx_date_to, p_acct_alias, p_item_number -- p_acct_alias ) mmt_lot_txn, (select mta.transaction_id, mta.organization_id, mta.inventory_item_id, mta.transaction_date, nvl(al.code_combination_id, mta.reference_account) code_combination_id, decode(al.code_combination_id, null, 'Y', 'N') create_acctg, mta.transaction_source_type_id, mta.accounting_line_type, mta.cost_element_id, mta.primary_quantity primary_quantity, round(sum(mta.base_transaction_value)/decode(mta.primary_quantity, 0, 1, mta.primary_quantity), 8) unit_cost, sum(mta.base_transaction_value) base_transaction_value from mtl_transaction_accounts mta, xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al, inv_organizations inv_orgs where mta.transaction_source_type_id = 6 -- Account Alias and 3=3 -- p_trx_date_from, p_trx_date_to and mta.organization_id = inv_orgs.organization_id -- ======================================================== -- SLA table joins to get the exact account numbers -- Outer joins in case Create Accounting has not been run. -- ======================================================== and ah.application_id (+) = al.application_id and ah.application_id (+) = 707 and ah.ae_header_id (+) = al.ae_header_id and ah.ledger_id (+) = al.ledger_id and al.ae_header_id (+) = xdl.ae_header_id and al.ae_line_num (+) = xdl.ae_line_num and xdl.application_id (+) = 707 and xdl.source_distribution_type (+) = 'MTL_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 (+) = mta.inv_sub_ledger_id group by mta.transaction_id, mta.organization_id, mta.transaction_date, mta.inventory_item_id, nvl(al.code_combination_id, mta.reference_account), -- code_combination_id decode(al.code_combination_id, null, 'Y', 'N'), -- create_acctg, mta.inventory_item_id, mta.transaction_source_type_id, mta.accounting_line_type, mta.cost_element_id, mta.primary_quantity) mta_lot_amt -- ======================================================== -- Material txn, org, item, period and accounting code joins -- ======================================================== where mmt_lot_txn.transaction_id = mta_lot_amt.transaction_id and mtt.transaction_type_id = mmt_lot_txn.transaction_type_id and msiv.inventory_item_id = mmt_lot_txn.inventory_item_id and msiv.organization_id = mmt_lot_txn.organization_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code and mmt_lot_txn.reason_id = mtr.reason_id(+) and cce.cost_element_id (+) = mta_lot_amt.cost_element_id and fu.user_id = mmt_lot_txn.created_by and oap.acct_period_id = mmt_lot_txn.acct_period_id and gcc.code_combination_id (+) = mta_lot_amt.code_combination_id -- ======================================================== -- Lookup Types -- ======================================================== and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.planning_make_buy_code and ml2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml2.lookup_code = mta_lot_amt.accounting_line_type and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type and fl.lookup_type (+) = 'YES_NO' and fl.lookup_code (+) = mta_lot_amt.create_acctg group by mmt_lot_txn.ledger, mmt_lot_txn.operating_unit, mmt_lot_txn.organization_code, oap.period_name, gcc.concatenated_segments, -- Accounts &segment_columns_grp fl.meaning, -- Create Accounting msiv.concatenated_segments, -- Item Number msiv.description, -- Item Description muomv.uom_code, fcl.meaning, -- Item Type misv.inventory_item_status_code, ml1.meaning, -- Make Buy Code -- For inline category query msiv.inventory_item_id, msiv.organization_id, ml2.meaning, -- Accounting Line Type mtt.transaction_type_name, mmt_lot_txn.account_alias, fu.user_name, mtr.reason_name, mmt_lot_txn.transaction_reference, mta_lot_amt.transaction_id, mta_lot_amt.transaction_date, mmt_lot_txn.lot_number, mmt_lot_txn.locator_id, mmt_lot_txn.organization_id, decode(mta_lot_amt.accounting_line_type, 7, 'WIP', 14, '', 1, decode(mmt_lot_txn.transaction_action_id, 2, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 3, decode(mmt_lot_txn.organization_id, mta_lot_amt.organization_id, mmt_lot_txn.subinventory_code, mmt_lot_txn.transfer_subinventory), 21, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 22, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 28, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), mmt_lot_txn.subinventory_code)), -- Subinventory msiv.primary_uom_code, decode(sign(decode(mta_lot_amt.base_transaction_value, 0, mta_lot_amt.primary_quantity, mta_lot_amt.base_transaction_value)), -1, -1 * abs(mmt_lot_txn.primary_quantity), +1, +1 * abs(mmt_lot_txn.primary_quantity)), -- Quantity mmt_lot_txn.currency_code, cce.cost_element, round(mta_lot_amt.unit_cost, 5), -- Item Cost round(decode(sign(decode(mta_lot_amt.base_transaction_value, 0, mta_lot_amt.primary_quantity, mta_lot_amt.base_transaction_value)), -1, -1 * abs(mmt_lot_txn.primary_quantity), +1, +1 * abs(mmt_lot_txn.primary_quantity)) * mta_lot_amt.unit_cost, 2) -- Amount -- Order by Ledger, Operating Unit, Org Code, Accounts, Item Number, Accounting Line Type, -- Account Alias, Transaction Id, Transaction Date, Lot Number and Subinventory order by mmt_lot_txn.ledger, mmt_lot_txn.operating_unit, mmt_lot_txn.organization_code, gcc.concatenated_segments, -- Accounts msiv.concatenated_segments, -- Item Number ml2.meaning, -- Accounting Line Type mmt_lot_txn.account_alias, mta_lot_amt.transaction_id, -- Transaction Id mta_lot_amt.transaction_date, --Transaction Date mmt_lot_txn.lot_number, --Lot Number decode(mta_lot_amt.accounting_line_type, 7, 'WIP', 14, '', 1, decode(mmt_lot_txn.transaction_action_id, 2, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 3, decode(mmt_lot_txn.organization_id, mta_lot_amt.organization_id, mmt_lot_txn.subinventory_code, mmt_lot_txn.transfer_subinventory), 21, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 22, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), 28, decode(sign(mta_lot_amt.primary_quantity), -1, mmt_lot_txn.subinventory_code, 1, mmt_lot_txn.transfer_subinventory, mmt_lot_txn.subinventory_code), mmt_lot_txn.subinventory_code)) -- Subinventory 1.12 25 Mar 2025 Douglas Volz Cleaned up code for Blitz Report and added Create Accounting Y/N column. Y US CAC Material Account Alias with Lot Numbers Report to display the material account alias transactions by lot number. Specify Yes for Show Lot Number to split out the transaction quantities and amounts by transaction lot number. And if processed by Create Accounting, the Create Accounting column shows "Yes". Parameters: =========== Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show Lot Number: enter Yes to see transactions by lot number, enter No to exclude lot information (mandatory). Account Alias: enter the account alias to report (optional). Category Set 1, 2, 3: any item category you wish (optional). Item Number: enter the item numbers you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2009-2025 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) -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 06 Nov 2009 Douglas Volz Initial Coding -- | 1.1 11 Nov 2009 Douglas Volz Added Org Code and transaction ID -- | 1.2 12 Nov 2009 Douglas Volz Added item and description -- | 1.3 06 Jan 2010 Douglas Volz Made dates a parameter -- | 1.4 12 Jan 2010 Douglas Volz Added quantity and unit cost columns -- | 1.5 12 Jan 2010 Douglas Volz Added account alias information -- | 1.6 20 Jun 2010 Douglas Volz Added created by information and fixed sort -- | 1.7 27 Jun 2010 Douglas Volz Fixed column label for user name, added Ledger parameter -- | 1.8 16 Jul 2010 Douglas Volz Added primary unit of measure (UOM), reason -- | code and transaction reference (comments) and added lot number -- | 1.9 06 Feb 2012 Douglas Volz Rewrite SQL report to solve cross-joining problem -- | with having multiple lot numbers per material transaction and multiple material overheads -- | 1.10 22 Jun 2015 Douglas Volz Added back comments to this code, removed client-specific SLA rules -- | 1.11 17 May 2017 Douglas Volz Added category sets -- | 1.12 25 Mar 2025 Douglas Volz Cleaned up code for Blitz Report and added Create Accounting Y/N column. -- +=============================================================================+*/ Enginatics &category_columns &p_show_lot_num_col &p_show_lot_number &segment_columns &segment_columns_grp 1=1 2=2 3=3 1 -10 &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')) US Chart of Accounts 2 &segment_columns_grp select 'gcc.'||lower(fifsv.application_column_name)||',' 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 2=2 mmt.transaction_date >= :p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id US Transaction Date From 4 3=3 mta.transaction_date >= :p_trx_date_from US Transaction Date From 5 20 2=2 mmt.transaction_date < :p_trx_date_to+1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id US Transaction Date To 6 3=3 mta.transaction_date < :p_trx_date_to+1 US Transaction Date To 7 30 &p_show_lot_num_col mmt_lot_txn.lot_number, LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Show Lot Number 8 &p_show_lot_number <parameter_value> US Show Lot Number 9 40 2=2 mgd.segment1 = :p_account_alias LOV INV Account Alias 31341B624172B711E06369FB0905676C Y select mgd.segment1 value, mgd.description description from mtl_generic_dispositions mgd, org_organization_definitions ood where mgd.organization_id = ood.organization_id and (mgd.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 nvl(ood.disable_date,sysdate)>=sysdate group by mgd.segment1, mgd.description order by mgd.segment1 US Inventory Account Alias 10 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 11 60 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 12 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 13 80 2=2 mmt.inventory_item_id in (select msiv.inventory_item_id from mtl_system_items_vl msiv, mtl_parameters mp where msiv.concatenated_segments = :p_item_number and msiv.organization_id = mp.organization_id and (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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))) LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null 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 (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Item Number 14 90 1=1 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 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 (:$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 15 100 1=1 haou2.name = :p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 16 110 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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, gl.name US Ledger :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code Item Number Pivot by Org by Alias MFG 23 ACCOUNTING_LINE_TYPE 5 ACCOUNTS 25 ACCOUNT_ALIAS 38 AMOUNT 8 Account 36 COST_ELEMENT 11 CREATE_ACCOUNTING 35 CURRENCY_CODE 21 Class 6 Company 7 Department 20 Family 37 ITEM_COST 13 ITEM_DESCRIPTION 12 ITEM_NUMBER 16 ITEM_STATUS 15 ITEM_TYPE 22 Inv.Items Description 1 LEDGER 32 LOCATOR 31 LOT_NUMBER 17 MAKE_BUY_CODE 2 OPERATING_UNIT 3 ORG_CODE 4 PERIOD_NAME 10 Product 18 Product Cat 19 Product Description -22.0023 Product Family -22.0024 Product Family Description 34 QUANTITY 27 REASON_CODE 33 SUBINVENTORY 9 Sub-Account 28 TRANSACTION_COMMENTS 30 TRANSACTION_DATE 29 TRANSACTION_ID 24 TRANSACTION_TYPE 14 UOM_CODE 26 USER_NAME ACCOUNTS ROW 5 ACCOUNT_ALIAS ROW 6 AMOUNT VALUE 1 SUM LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 PERIOD_NAME ROW 4 S Site Pivot by Org by Alias