ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 91D3167A335B1204E053BB6B63585EE3 CST Cost Type (No AvgRates) Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type desc US 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 与库存组织有关的会计科目表 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger 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 AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit 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 Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 09737AE530280E64E0630100007F4174 INV COGS Transaction Types Y select mtt.transaction_type_name value, mtt.description from mtl_transaction_types mtt where mtt.transaction_source_type_id in (2,3,6,12,13) order by mtt.transaction_type_name US Transaction types related to COGS, including possible user-defined transactions for Account Alias Issue/Receipts, Account Issues/Receipts and Miscellaneous Issues/Receipts transactions. 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 91D022B15B12FFB9E053BB6B63587F0B INV Item (master, costing enabled only) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where 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) ) and msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US All costing enabled items from item master org 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 所有现行清单组织代码，不包括主组织 09737AD5DDE40E60E0630100007F7164 select gl.name Ledger, haou2.name Operating_Unit, cogs.organization_code Org_Code, haou.name Organization_Name, -- Revision for version 1.4 -- oap.period_name Period_Name, ah.period_name Period_Name, -- End revision for version 1.4 &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, fcl.meaning Item_Type, misv.inventory_item_status_code_tl Item_Status, ml1.meaning Make_Buy_Code, ml2.meaning Based_on_Rollup, ml3.meaning Inventory_Asset, ml4.meaning Accounting_line_Type, cogs.transaction_type_name Transaction_Type, mtst.transaction_source_type_name Transaction_Source, -- Revision for version 1.3 cogs.document_order_num Document_Number, cogs.order_type Order_Type, cogs.transaction_id Transaction_Id, cogs.transaction_date Transaction_Date, cogs.transaction_reference Transaction_Comments, &category_columns muomv.uom_code UOM_Code, sum(cogs.primary_quantity) Quantity, -- Revision for version 1.6 sum(cogs.material_value) Original_COGS_Material, sum(cogs.material_overhead_value) Original_COGS_Material_Ovhd, sum(cogs.resource_value) Original_COGS_Resource, sum(cogs.outside_processing_value) Original_COGS_OSP, sum(cogs.overhead_value) Original_COGS_Overhead, -- End revision for version 1.6 sum(cogs.base_transaction_value) Original_COGS, -- Revision for version 1.6 round(sum(decode(cic.material_cost, null, cogs.material_value, cic.material_cost * cogs.primary_quantity)),2) New_COGS_Material, round(sum(decode(cic.material_overhead_cost, null, cogs.material_overhead_value, cic.material_overhead_cost * cogs.primary_quantity)),2) New_COGS_Material_Overhead, round(sum(decode(cic.resource_cost, null, cogs.resource_value, cic.resource_cost * cogs.primary_quantity)),2) New_COGS_Resource, round(sum(decode(cic.outside_processing_cost, null, cogs.outside_processing_value, cic.outside_processing_cost * cogs.primary_quantity)),2) New_COGS_OSP, round(sum(decode(cic.overhead_cost, null, cogs.overhead_value, cic.overhead_cost * cogs.primary_quantity)),2) New_COGS_Overhead, -- End revision for version 1.6 -- Revision for version 1.3 round(sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)),2) New_COGS, round(decode(sign(sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value)),-1,0,1, sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value), sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value)),2) Debit_COGS_Amount, abs(round(decode(sign(sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value)), 1,0,-1, sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value), sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value)),2)) Credit_COGS_Amount, round(sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value),2) Difference, -- Percent Difference = Difference / Original COGS round((sum(decode(cic.item_cost, null, cogs.base_transaction_value, cic.item_cost * cogs.primary_quantity)) - sum(cogs.base_transaction_value)) / (decode(sum(cogs.base_transaction_value), 0, 1, sum(cogs.base_transaction_value))) * 100, 1) Percent_Difference -- End revision for version 1.3 from -- Revision for version 1.3 mtl_system_items_vl msiv, mtl_txn_source_types mtst, cst_item_costs cic, cst_cost_types cct, mtl_item_status_vl misv, mtl_units_of_measure_vl muomv, -- Revision for version 1.4 -- org_acct_periods oap, mfg_lookups ml1, -- planning make/buy code, MTL_PLANNING_MAKE_BUY mfg_lookups ml2, -- based on rollup, CST_BONROLLUP_VAL mfg_lookups ml3, -- inventory_asset_flag, SYS_YES_NO mfg_lookups ml4, -- accounting line type, CST_ACCOUNTING_LINE_TYPE fnd_common_lookups fcl, -- Item Type -- Revision for version 1.2 (select mp.organization_code, mp.organization_id, mta.transaction_id, mta.inventory_item_id, mmt.transaction_type_id, mmt.acct_period_id, mtt.transaction_type_name, mta.accounting_line_type, mta.reference_account, mta.inv_sub_ledger_id, mmt.transaction_source_type_id, -- Revision for version 1.3 decode(mmt.transaction_source_type_id, 1, (select poh.segment1 -- Purchase Order from po_headers_all poh where poh.po_header_id = mmt.transaction_source_id), 2, (select ooh.order_number -- Sales order from oe_order_headers_all ooh, oe_order_lines_all ool where ooh.header_id = ool.header_id and ool.line_id = mmt.trx_source_line_id), 3, ml5.meaning, -- Account 4, (select ooh.order_number -- Move order from oe_order_headers_all ooh, oe_order_lines_all ool where ooh.header_id = ool.header_id and ool.line_id = mmt.trx_source_line_id), 5, (select we.wip_entity_name -- Job or Schedule from wip_entities we where we.wip_entity_id = mmt.transaction_source_id), 6, (select mgd.segment1 -- Account alias from mtl_generic_dispositions mgd where mgd.disposition_id = mmt.transaction_source_id), 7, (select prh.segment1 -- Internal requisition from po_requisition_headers_all prh where prh.requisition_header_id = mmt.transaction_source_id), 8, (select ooh.order_number -- Internal order from oe_order_headers_all ooh, oe_order_lines_all ool where ooh.header_id = ool.header_id and ool.line_id = mmt.trx_source_line_id), 9, (select mcch.cycle_count_header_name -- Cycle count from mtl_cycle_count_headers mcch where mcch.cycle_count_header_id = mmt.transaction_source_id), 10, (select mpi.description -- Physical inventory from mtl_physical_inventories mpi where mpi.physical_inventory_id = mmt.transaction_source_id), 11, (select description -- Std cost update from cst_cost_updates ccu where ccu.cost_update_id = mmt.cost_update_id), 12, (select ooh.order_number -- RMA from oe_order_headers_all ooh, oe_order_lines_all ool where ooh.header_id = ool.header_id and ool.line_id = mmt.trx_source_line_id), 13, decode(mmt.transaction_action_id, -- Inventory 1, ml5.meaning, -- Issue from stores 2, ml5.meaning, -- Subinventory transfer 3, ml5.meaning, -- Direct organization transfer 5, ml5.meaning, -- Planning transfer 6, ml5.meaning, -- Ownership xfer / consignment 9, (select ooh.order_number -- Logical Intercompany Sales from oe_order_headers_all ooh, oe_order_lines_all ool, mtl_material_transactions mmt_parent where ooh.header_id = ool.header_id and ool.line_id = mmt_parent.trx_source_line_id and mmt.parent_transaction_id = mmt_parent.transaction_id), 10, (select ooh.order_number -- Logical Intercompany Receipt from oe_order_headers_all ooh, oe_order_lines_all ool, mtl_material_transactions mmt_parent where ooh.header_id = ool.header_id and ool.line_id = mmt_parent.trx_source_line_id and mmt.parent_transaction_id = mmt_parent.transaction_id), 12, ml5.meaning, -- Intransit Receipt 13, (select ooh.order_number -- Logical Intercompany Sales Return from oe_order_headers_all ooh, oe_order_lines_all ool where ooh.header_id = ool.header_id and ool.line_id = mmt.trx_source_line_id), 14, (select ooh.order_number -- Logical Intercompany Sales Return from oe_order_headers_all ooh, oe_order_lines_all ool, mtl_material_transactions mmt_parent where ooh.header_id = ool.header_id and ool.line_id = mmt_parent.trx_source_line_id and mmt.parent_transaction_id = mmt_parent.transaction_id), 15, ml5.meaning, -- Logical Intransit Receipt 21, ml5.meaning, -- Intransit Shipment 22, ml5.meaning, -- Logical Intransit Shipment 24, ml5.meaning, -- Average Cost Update 27, ml5.meaning, -- Receipt into Stores ml5.meaning), 101, (select ooh.order_number -- Internal RMA from oe_order_headers_all ooh, oe_order_lines_all ool where ooh.header_id = ool.header_id and ool.line_id = mmt.trx_source_line_id), ml5.meaning -- Any other source type ) document_order_num, nvl((select ottt.name from oe_order_lines_all ool, oe_order_headers_all ooh, oe_transaction_types_tl ottt where ooh.order_type_id = ottt.transaction_type_id and ooh.header_id = ool.header_id and mmt.trx_source_line_id = ool.line_id and ottt.language = userenv('lang')),'') order_type, mta.transaction_date, mmt.transaction_reference, -- End revision for version 1.3 -- Revision for version 1.3, logic fix for the transaction quantity decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity/ (select count(*) from mtl_transaction_accounts mta2 where mta2.transaction_id = mta.transaction_id and mta2.reference_account = mta.reference_account and mta2.accounting_line_type = mta.accounting_line_type) ) primary_quantity, -- End revision for version 1.3 -- Revision for version 1.6 decode(nvl(mta.cost_element_id,1), 1, mta.base_transaction_value, 0) material_value, decode(mta.cost_element_id, 2, mta.base_transaction_value, 0) material_overhead_value, decode(mta.cost_element_id, 3, mta.base_transaction_value, 0) resource_value, decode(mta.cost_element_id, 4, mta.base_transaction_value, 0) outside_processing_value, decode(mta.cost_element_id, 5, mta.base_transaction_value, 0) overhead_value, -- End revision for version 1.6 mta.base_transaction_value base_transaction_value from mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_transaction_types mtt, -- Revision for version 1.3 mfg_lookups ml5, -- Transaction action mtl_parameters mp where mta.transaction_id = mmt.transaction_id and mtt.transaction_type_id = mmt.transaction_type_id -- Revision for version 1.3 and ml5.lookup_type (+) = 'MTL_TRANSACTION_ACTION' and ml5.lookup_code (+) = mmt.transaction_action_id -- End revision for version 1.3 and mp.organization_id = mta.organization_id and 2=2 -- p_txn_date_from, p_txn_date_to, p_org_code, p_item_number, p_transaction_type and mmt.transaction_source_type_id = mtt.transaction_source_type_id and mta.transaction_source_type_id = mtt.transaction_source_type_id -- Revision for version 1.3 -- Limit to COGS entries or to material transactions which replicate COGS entries and mta.accounting_line_type in (35, 2) -- Cost of Goods Sold, Account -- End revision for version 1.3 -- Revision for version 1.5 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) ) cogs, -- End revision for version 1.2 gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units haou, -- inv_organization_id hr_all_organization_units haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.3 xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al -- End revision for version 1.3 where msiv.organization_id = cogs.organization_id and msiv.inventory_item_id = cogs.inventory_item_id and msiv.primary_uom_code = muomv.uom_code and msiv.inventory_item_status_code = misv.inventory_item_status_code and mtst.transaction_source_type_id = cogs.transaction_source_type_id -- Revision for version 1.4 -- and oap.acct_period_id = cogs.acct_period_id and cogs.organization_id (+) = cic.organization_id and cogs.inventory_item_id (+) = cic.inventory_item_id and cic.cost_type_id (+) = cct.cost_type_id and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.planning_make_buy_code and ml2.lookup_type = 'CST_BONROLLUP_VAL' and ml2.lookup_code = cic.based_on_rollup_flag and ml3.lookup_type = 'SYS_YES_NO' and ml3.lookup_code = to_char(cic.inventory_asset_flag) and ml4.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml4.lookup_code = cogs.accounting_line_type -- =================================================================== -- using the base tables to avoid the performance issues -- with org_organization_definitions and hr_operating_units -- =================================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = cogs.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate +1) 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.3, Subledger accounting joins -- =================================================================== and ah.application_id = al.application_id and ah.application_id = 707 and ah.ae_header_id = al.ae_header_id and al.ledger_id = ah.ledger_id and al.ae_header_id = xdl.ae_header_id and al.ae_line_num = xdl.ae_line_num and al.code_combination_id = gcc.code_combination_id (+) and xdl.application_id = 707 and xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = cogs.inv_sub_ledger_id -- End revision for version 1.3 -- =================================================================== -- Revision for version 1.5 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) -- End revision for version 1.5 and 1=1 -- p_ledger, p_operating_unit, p_cost_type group by gl.name, -- Ledger haou2.name, -- Operating Unit cogs.organization_code, haou.name, -- Organization Name -- Revision for version 1.4 -- oap.period_name, ah.period_name, -- End revision for version 1.4 &segment_columns_grp msiv.concatenated_segments, -- Item Number msiv.description, -- Item Description fcl.meaning, -- Item Type misv.inventory_item_status_code_tl, -- Item Status ml1.meaning, -- Make_Buy_Code ml2.meaning, -- Based_on_Rollup ml3.meaning, -- Inventory_Asset cogs.transaction_type_name, -- Transaction Type ml4.meaning, -- Accounting_Line_Type cogs.transaction_type_name, -- Transaction Type mtst.transaction_source_type_name, -- Transaction_Source -- Revision for version 1.3 cogs.document_order_num, -- Document_Number cogs.order_type, -- Order_Type cogs.transaction_id, -- Transaction_Id cogs.transaction_date, -- Transaction_Date cogs.transaction_reference, -- Transaction_Comments -- for inline selects msiv.inventory_item_id, msiv.organization_id, muomv.uom_code having abs(round(sum(decode((nvl(cic.item_cost,0) * cogs.primary_quantity), 0, cogs.base_transaction_value, (nvl(cic.item_cost,0) * cogs.primary_quantity))),2) - sum(cogs.base_transaction_value)) >= nvl(:p_min_value_diff,.01) -- p_min_value_diff order by 1,2,3,5,6,7,8,9,10,11,18,20 Y US CAC Recost Cost of Goods Sold Report to compare the "as transacted" cost of goods sold (COGS) transactions in Oracle Inventory with the "what if" costs from a simulation cost type. Use this report when you need to make manual journal entry corrections to your COGS entries, due to incorrect item costs. In addition to reporting the normal COGS entries for the COGS Recognition, Sales Order Issue, RMA Receipt and RMA Return transactions, you can also report user-defined material transactions such as Account Issues/Receipts, Miscellaneous Issues/Receipts and Account Alias transactions which could represent COGS entries from other ERP Systems. To report additional user-defined transactions enter these along with the defaulted values for the COGS Transaction Type parameter: COGS Recognition, Sales Order Issue, RMA Receipt and RMA Return. Note: in order for COGS entries to be reported you must first run Create Accounting. Parameters: =========== Transaction Date From: enter the starting transaction date for COGS transactions (mandatory). Transaction Date To: enter the ending transaction date for PO COGS transactions (mandatory). COGS Transaction Types: enter the COGS related transaction types you wish to report (mandatory). Recost Cost Type: enter the cost type you wish to use to recost your COGS transactions (mandatory). Minimum Value Difference: the absolute smallest COGS difference you want to report (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) 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 2006- 2023 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_recost_cogs_detail_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 02 Apr 2006 Douglas Volz Initial Coding -- | 1.1 28 Apr 2006 Douglas Volz Final Coding -- | 1.2 02 May 2017 Douglas Volz Modified for Client's use, added inline table -- | to get one row for COGS accounting entries, to -- | to get the mmt correct qty and avoid cross-joining. -- | 1.3 13 Nov 2023 Douglas Volz Modified for organization access, removed tabs and -- | added subledger accounting ccids. -- | 1.4 16 Nov 2023 Douglas Volz Remove org_acct_periods, use period name from xla_ae_headers. -- | 1.5 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. -- | 1.6 12 Mar 2024 Douglas Volz Add cost elements for COGS per client request. -- +=============================================================================+*/ Enginatics &category_columns &segment_columns &segment_columns_grp 1=1 2=2 :p_min_value_diff 1 -10 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",' 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 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 mta.transaction_date >= :p_trx_date_from and 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 Y US Transaction Date From 4 20 2=2 mta.transaction_date < :p_trx_date_to + 1 and 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 Y US Transaction Date To 5 30 2=2 mtt.transaction_type_name = :p_transaction_type LOV INV COGS Transaction Types 09737AE530280E64E0630100007F4174 Y select mtt.transaction_type_name value, mtt.description from mtl_transaction_types mtt where mtt.transaction_source_type_id in (2,3,6,12,13) order by mtt.transaction_type_name COGS Recognition;RMA Receipt;RMA Return;Sales order issue Y US COGS Transaction Types 6 40 1=1 cct.cost_type = :p_cost_type LOV CST Cost Type (No AvgRates) 91D3167A335B1204E053BB6B63585EE3 Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type desc select cct.cost_type from cst_cost_types cct, mtl_parameters mp where nvl(cct.disable_date,sysdate)>=sysdate and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and mp.primary_cost_method=cct.cost_type_id Y US Recost Cost Type 7 50 :p_min_value_diff Number 1 Y US Minimum Value Difference 8 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=11)) US Category Set 1 9 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 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 10 80 &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 11 90 2=2 mmt.inventory_item_id = (select msiv.inventory_item_id from mtl_system_items_vl msiv where msiv.organization_id = mta.organization_id and msiv.concatenated_segments = :p_item_number) LOV INV Item (master, costing enabled only) 91D022B15B12FFB9E053BB6B63587F0B Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where 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) ) and msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US Item Number 12 100 2=2 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 13 110 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 14 120 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 :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Operating Unit :$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 COGS Summary by Org Y MFG 25 ACCOUNTING_LINE_TYPE 8 Account 16 BASED_ON_ROLLUP 36 CREDIT_COGS_AMOUNT 29 Class 6 Company 35 DEBIT_COGS_AMOUNT 37 DIFFERENCE 20 DOCUMENT_NUMBER 7 Department 28 Family 17 INVENTORY_ASSET 12 ITEM_DESCRIPTION 11 ITEM_NUMBER 14 ITEM_STATUS 13 ITEM_TYPE 30 Inv.Items Description 1 LEDGER 15 MAKE_BUY_CODE 34 NEW_COGS 2 OPERATING_UNIT 21 ORDER_TYPE 4 ORGANIZATION_NAME 3 ORG_CODE 33 ORIGINAL_COGS 38 PERCENT_DIFFERENCE 5 PERIOD_NAME 10 Product 26 Product Cat 27 Product Description 32 QUANTITY 9 Sub-Account 24 TRANSACTION_COMMENTS 23 TRANSACTION_DATE 22 TRANSACTION_ID 19 TRANSACTION_SOURCE 18 TRANSACTION_TYPE 31 UOM_CODE CREDIT_COGS_AMOUNT VALUE 4 SUM DEBIT_COGS_AMOUNT VALUE 3 SUM DIFFERENCE VALUE 5 SUM LEDGER ROW 1 NEW_COGS VALUE 2 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 ORIGINAL_COGS VALUE 1 SUM S Site COGS Summary by Org