ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E7B8D5BA75FBA5ACE05369FB090593D7 CST Accounting Line Type Y select meaning value, description from mfg_lookups where lookup_type='CST_ACCOUNTING_LINE_TYPE' and enabled_flag='Y' order by meaning US 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 E234C6573B214FDAE05369FB090533D7 CST PII Sub-Elements Y select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code US Lookup type for profit in inventory resources 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）限制的操作单元。 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 8E2FF36EDEB679D2E0530100007F1FF2 INV Transaction Source Type Y select mtst.transaction_source_type_name value, mtst.description from mtl_txn_source_types mtst order by transaction_source_type_name US 8E2FF36EDEB579D2E0530100007F1FF2 INV Transaction Type Y select mtt.transaction_type_name value, mtt.description from mtl_transaction_types mtt order by mtt.transaction_type_name US EA71636DEBC41B7CE0530100007F3AE8 with pii as -- =========================================================== -- Revision 1.29, Inline select for Profit in Inventory costs -- =========================================================== (select sum(nvl(cicd.item_cost, 0)) pii_item_cost, sum(nvl(cicd.item_cost,0)) * decode(sign('&p_sign_pii'),1,1,-1,-1,1) corrected_pii_item_cost, cicd.inventory_item_id, cicd.organization_id, cct.cost_type pii_cost_type from cst_item_cost_details cicd, bom_resources br, cst_cost_types cct, mtl_parameters mp where cicd.resource_id = br.resource_id and cicd.cost_type_id = cct.cost_type_id and mp.organization_id = cicd.organization_id 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 3=3 -- p_org_code and 5=5 -- p_pii_cost_type, p_pii_sub_element &only_no_pii group by cicd.inventory_item_id, cicd.organization_id, cct.cost_type ) ----------------main query starts here-------------- select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, acct_dist.organization_code Org_Code, -- Revision for version 1.8 haou2_from.name From_OU, haou2_to.name To_OU, nvl((select ml2.meaning from mfg_lookups ml2 where ml2.lookup_type = 'MTL_FOB_POINT' and ml2.lookup_code = acct_dist.fob_point),'') FOB_Point, acct_dist.ship_from_org Ship_From_Org, acct_dist.ship_to_org Ship_To_Org, -- Revision for version 1.25 -- ah.period_name Period_Name, oap.period_name Period_Name, -- End revision for version 1.25 &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.6 and 1.19, add in item type fcl.meaning Item_Type, -- Revision for version 1.22 misv.inventory_item_status_code Item_Status, -- Revision for version 1.23 ml2.meaning Make_Buy_Code, -- Revision for version 1.12 and 1.24 &category_columns ml1.meaning Accounting_Line_Type, mtt.transaction_type_name Transaction_Type, -- Revision for version 1.13 mtst.transaction_source_type_name Transaction_Source, -- Revision for version 1.27 mtr.reason_name reason, mtr.description reason_description, acct_dist.document_order_num Document_Number, decode(acct_dist.transaction_source_type_id, 2, acct_dist.order_type, -- Sales order 8, acct_dist.order_type, -- Internal order 12, acct_dist.order_type, -- RMA 101, acct_dist.order_type, -- Internal RMA null) Order_Type, br.resource_code Sub_Element, acct_dist.transaction_id Transaction_Id, acct_dist.transfer_transaction_id Transfer_Transaction_Id, acct_dist.parent_transaction_id Parent_Transaction_Id, acct_dist.transaction_date Transaction_Date, acct_dist.creation_date Creation_Date, fu.user_name Created_By, acct_dist.transaction_reference Transaction_Comments, -- End revision for version 1.27 -- Revision for version 1.25 msub.secondary_inventory_name Subinventory, msub.description Subinventory_Description, -- Revision for version 1.27 acct_dist.transfer_subinventory Transfer_Subinventory, ccg.cost_group Cost_Group, ccg_xfer.cost_group Transfer_Cost_Group, -- End revision for version 1.27 &p_show_project &p_show_wip_job muomv.uom_code UOM_Code, acct_dist.primary_quantity Quantity, -- Revision for version 1.27 cce.cost_element Cost_Element, gl.currency_code Currency_Code, acct_dist.Matl_Amount Material_Amount, acct_dist.Matl_Ovhd_Amount Material_Overhead_Amount, acct_dist.Resource_Amount Resource_Amount, acct_dist.OSP_Amount Outside_Processing_Amount, acct_dist.Overhead_Amount Overhead_Amount, -- Revision for version 1.27 round(acct_dist.mta_amount / decode(acct_dist.primary_quantity, 0, 1, acct_dist.primary_quantity),5) Item_Cost, -- End revision for version 1.27 acct_dist.mta_amount Amount -- Revision for version 1.26 &p_show_pii from mtl_system_items_vl msiv, org_acct_periods oap, mtl_transaction_types mtt, -- Revision for version 1.21 mtl_units_of_measure_vl muomv, -- Revision for version 1.22 mtl_item_status_vl misv, -- Revision for version 1.13 mtl_txn_source_types mtst, -- Revision for version 1.29 pii, -- Revision for version 1.27 mtl_secondary_inventories msub, mtl_transaction_reasons mtr, cst_cost_groups ccg, cst_cost_groups ccg_xfer, cst_cost_elements cce, bom_resources br, fnd_user fu, -- End revision for version 1.27 gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit hr_organization_information hoi_from, hr_all_organization_units_vl haou_from, -- inv_organization_id hr_all_organization_units_vl haou2_from, -- operating unit hr_organization_information hoi_to, hr_all_organization_units_vl haou_to, -- inv_organization_id hr_all_organization_units_vl haou2_to, -- operating unit gl_ledgers gl, -- Revision for version 1.19 fnd_common_lookups fcl, -- Item Type mfg_lookups ml1, -- Accounting Line Type mfg_lookups ml2, -- Planning Make Buy Code -- Revision for versions 1.26 and 1.27 &pii_lookup_table &project_tables &mtl_sla_tables -- Revision for version 1.26 -- =========================================================== -- Inline select for finding components with PII not issued to WIP -- =========================================================== -- get the corrected wip component issue status (select wro.organization_id, wro.wip_entity_id, wro.primary_item_id, 'N' wip_pii_component_quantity, 5 transaction_source_type_id from (select wrosum.wip_entity_id, wrosum.organization_id, wrosum.primary_item_id, wrosum.inventory_item_id, sum(wrosum.quantity_issued) quantity_issued from (select wro.wip_entity_id, wro.organization_id, wdj.primary_item_id, wro.inventory_item_id, wro.quantity_issued from wip_requirement_operations wro, wip_discrete_jobs wdj, -- Revision for version 1.29 -- Only select components with PII -- cst_item_cost_details cicd, -- cst_cost_types cct, -- bom_resources br, pii cicd, -- End revision for version 1.29 mtl_parameters mp where wdj.wip_entity_id = wro.wip_entity_id and wdj.organization_id = wro.organization_id and wdj.organization_id = mp.organization_id -- Do not select phantom (6) or bulk (4) WIP supply types, typically not issued to WIP and wro.wip_supply_type not in (4,6) -- find jobs to highlight that were open during the report period or closed after the transaction to date and (wdj.date_closed is null -- the job is open and wdj.creation_date < :p_trx_date_to + 1 or -- the job is closed and ...the job was closed after the transaction from date wdj.date_closed is not null and wdj.date_closed >= :p_trx_date_to + 1 ) -- Only select components with PII and cicd.inventory_item_id = wro.inventory_item_id and cicd.organization_id = wro.organization_id -- Revision for version 1.29 -- and cicd.cost_type_id = cct.cost_type_id -- and cicd.resource_id = br.resource_id -- and cicd.item_cost <> 0 and cicd.pii_item_cost <> 0 -- End revision for version 1.29 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 3=3 -- p_org_code &only_no_pii union all select mmt.transaction_source_id, wro.organization_id, wdj.primary_item_id, mmt.inventory_item_id, decode(mmt.transaction_type_id, 35, mmt.primary_quantity, -- wip component issue 43, -1 * mmt.primary_quantity -- wip component return ) quantity_issued from mtl_material_transactions mmt, wip_discrete_jobs wdj, wip_requirement_operations wro, -- Revision for version 1.29 -- Only select components with PII -- cst_item_cost_details cicd, -- cst_cost_types cct, -- bom_resources br, pii cicd, -- End revision for version 1.29 mtl_parameters mp where mmt.transaction_source_type_id = 5 and mmt.transaction_source_id = wdj.wip_entity_id and mmt.organization_id = wdj.organization_id and mp.organization_id = wdj.organization_id -- Revision for version 1.29, join to wdj, not mmt, for performance and wro.wip_entity_id = wdj.wip_entity_id and wro.organization_id = wdj.organization_id -- End revision for version 1.29 and mmt.operation_seq_num = wro.operation_seq_num and mmt.transaction_date >= :p_trx_date_to + 1 -- Do not select phantom (6) or bulk (4) WIP supply types, typically not issued to WIP and wro.wip_supply_type not in (4,6) -- find jobs to highlight that were open during the report period or closed after the transaction to date and (wdj.date_closed is null -- the job is open and wdj.creation_date < :p_trx_date_to + 1 or -- the job is closed and ...the job was closed after the transaction from date wdj.date_closed is not null and wdj.date_closed >= :p_trx_date_to + 1 ) -- Only select components with PII and cicd.inventory_item_id = wro.inventory_item_id and cicd.organization_id = wro.organization_id -- Revision for version 1.29 and cicd.inventory_item_id = mmt.inventory_item_id and cicd.organization_id = mmt.organization_id -- and cicd.cost_type_id = cct.cost_type_id -- and cicd.resource_id = br.resource_id -- and cicd.item_cost <> 0 and cicd.pii_item_cost <> 0 -- End revision for version 1.29 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 3=3 -- p_org_code &only_no_pii ) wrosum group by wrosum.wip_entity_id, wrosum.organization_id, wrosum.primary_item_id, wrosum.inventory_item_id) wro -- Only select components with no issues to WIP where nvl(wro.quantity_issued,0) = 0 &only_no_pii group by wro.organization_id, wro.wip_entity_id, wro.primary_item_id, 'N', -- wip_pii_component_quantity 5 --transaction_source_type_id ) wro_pii, -- End revision for version 1.26 -- ========================================================================== -- Use this inline table to fetch the material transactions -- ========================================================================== ( -- First join to non-WIP entries select mp.organization_code organization_code, -- Fix for version 1.6 -- Revision for version 1.5 decode(mmt.transaction_action_id, 3, mp_mmt_org.organization_code, -- Direct Org Transfer, txn_id 3 9, mp_mmt_org.organization_code, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_xfer_org.organization_code, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_xfer_org.organization_code, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_mmt_org.organization_code, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_xfer_org.organization_code, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_xfer_org.organization_code, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_mmt_org.organization_code, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_mmt_org.organization_code, -- Logical Intransit Shipment, tnx_id 60, 65 '') ship_from_org, decode(mmt.transaction_action_id, 3, mp_xfer_org.organization_code, -- Direct Org Transfer, txn_id 3 9, mp_xfer_org.organization_code, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_mmt_org.organization_code, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_mmt_org.organization_code, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_xfer_org.organization_code, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_mmt_org.organization_code, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_mmt_org.organization_code, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_xfer_org.organization_code, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_xfer_org.organization_code, -- Logical Intransit Shipment, tnx_id 60, 65 '') ship_to_org, -- End revision for version 1.5 -- End revision for version 1.6 mp.organization_id organization_id, -- Revision for version 1.8 decode(mmt.transaction_action_id, 3, mp_mmt_org.organization_id, -- Direct Org Transfer, txn_id 3 9, mp_mmt_org.organization_id, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_xfer_org.organization_id, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_xfer_org.organization_id, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_mmt_org.organization_id, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_xfer_org.organization_id, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_xfer_org.organization_id, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_mmt_org.organization_id, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_mmt_org.organization_id, -- Logical Intransit Shipment, tnx_id 60, 65 mta.organization_id) ship_from_org_id, decode(mmt.transaction_action_id, 3, mp_xfer_org.organization_id, -- Direct Org Transfer, txn_id 3 9, mp_xfer_org.organization_id, -- Logical Intercompany Sales Issue, txn_id 11 10, mp_mmt_org.organization_id, -- Logical Intercompany Shipment Receipt, txn_id 10 12, mp_mmt_org.organization_id, -- Receive from intransit, Int Req Intr Rcpt, txn_id 12,61 13, mp_xfer_org.organization_id, -- Logical Intercompany Receipt Return, txn_id 13 15, mp_mmt_org.organization_id, -- Logical Intransit Receipt, txn_id 76 -- Revision for version 1.11 17, mp_mmt_org.organization_id, -- Logical Expense Requisition Receipt, txn_id 27 21, mp_xfer_org.organization_id, -- Intransit Shipment, Int Order Intr Ship, txn_id 21,62 22, mp_xfer_org.organization_id, -- Logical Intransit Shipment, tnx_id 60, 65 mta.organization_id) ship_to_org_id, mmt.fob_point fob_point, -- End revision for version 1.8 mmt.acct_period_id acct_period_id, mta.reference_account reference_account, mta.inv_sub_ledger_id inv_sub_ledger_id, mmt.inventory_item_id, mmt.transaction_source_type_id, -- Revision for version 1.27 &show_document_number 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, mmt.transaction_id, mmt.transfer_transaction_id, mmt.parent_transaction_id, mta.transaction_date, mta.creation_date, mmt.created_by, mmt.reason_id, regexp_replace(mmt.transaction_reference,'[^[:alnum:]'' '']', null) transaction_reference, -- End for revision 1.27 mmt.transaction_type_id, mta.accounting_line_type, -- Revision for version 1.15 decode(mta.accounting_line_type, 7, ml1.meaning, 14, ml1.meaning, 1, decode(mmt.transaction_action_id, 2, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 3, decode(mmt.organization_id, mta.organization_id, mmt.subinventory_code, mmt.transfer_subinventory ), 21, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 22, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 28, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), mmt.subinventory_code -- Fix for version 1.6 -- ) ) ) subinventory_code, -- End revision for version 1.15 -- Revision for version 1.27 mmt.transfer_subinventory, mmt.cost_group_id, mmt.transfer_cost_group_id, -- End revision for version 1.27 -- Revision for version 1.26 mmt.transaction_source_id, -- Revision for version 1.25 mmt.project_id, -- Revision for version 1.27 mmt.task_id, mmt.pm_cost_collected, -- End revision for version 1.27 null wip_class, null class_type, null wip_type, null job, null fg_item, null fg_description, -- End revision for version 1.25 -- Revision for version 1.20 and 1.28, logic fix for the transaction quantity decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity ) primary_quantity, -- Revision for version 1.27 mta.cost_element_id, mta.resource_id, -- End revision for version 1.27 -- Revision for version 1.13 decode(mta.cost_element_id, 1, mta.base_transaction_value, 0) Matl_Amount, decode(mta.cost_element_id, 2, mta.base_transaction_value, 0) Matl_Ovhd_Amount, decode(mta.cost_element_id, 3, mta.base_transaction_value, 0) Resource_Amount, decode(mta.cost_element_id, 4, mta.base_transaction_value, 0) OSP_Amount, decode(mta.cost_element_id, 5, mta.base_transaction_value, 0) Overhead_Amount, -- End revision for version 1.13 mta.base_transaction_value mta_amount &p_show_pii2 from mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_parameters mp, mtl_parameters mp_xfer_org, -- Transfer Org mtl_parameters mp_mmt_org, -- MMT Org mfg_lookups ml1, -- Accounting Line Type -- Revision for version 1.27 mfg_lookups ml2 -- Transaction Action -- ======================================================== -- Material transaction, date, org and item joins -- ======================================================== where mta.transaction_id = mmt.transaction_id and mp.organization_id = mta.organization_id and mp_xfer_org.organization_id = nvl(mmt.transfer_organization_id, mmt.organization_id) and mp_mmt_org.organization_id = mmt.organization_id -- to use the mmt index N1 by inventory_item_id, organization_id and date and mmt.inventory_item_id = mta.inventory_item_id and ml1.lookup_type (+) = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code (+) = mta.accounting_line_type -- Revision for version 1.27 and ml2.lookup_type (+) = 'MTL_TRANSACTION_ACTION' and ml2.lookup_code (+) = mmt.transaction_action_id -- End revision for version 1.27 and 2=2 -- p_trx_date_from, p_trx_date_to 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 3=3 -- p_org_code and 4=4 -- p_item_number, p_min_transaction_amount, p_max_transaction_amount, p_absolute_txn_amount and 6=6 -- p_transaction_type and 7=7 -- p_transaction_source &only_non_wip_sources1 union all -- Revision for version 1.25 -- Now join to WIP entries select mp.organization_code organization_code, null ship_from_org, null ship_to_org, mp.organization_id organization_id, -- Revision for version 1.8 mp.organization_id ship_from_org_id, mp.organization_id ship_to_org_id, mmt.fob_point fob_point, -- End revision for version 1.8 mmt.acct_period_id acct_period_id, mta.reference_account reference_account, mta.inv_sub_ledger_id inv_sub_ledger_id, mmt.inventory_item_id, mmt.transaction_source_type_id, -- Revision for version 1.27 decode(mmt.transaction_source_type_id, 5, (select we.wip_entity_name -- Job or Schedule from wip_entities we where we.wip_entity_id = mmt.transaction_source_id), null ) document_order_num, null order_type, mmt.transaction_id, mmt.transfer_transaction_id, mmt.parent_transaction_id, mta.transaction_date, mta.creation_date, mmt.created_by, mmt.reason_id, regexp_replace(mmt.transaction_reference,'[^[:alnum:]'' '']', null) transaction_reference, -- End for revision 1.27 mmt.transaction_type_id, mta.accounting_line_type, -- Revision for version 1.15 decode(mta.accounting_line_type, 7, ml1.meaning, 14, ml1.meaning, 1, decode(mmt.transaction_action_id, 2, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 3, decode(mmt.organization_id, mta.organization_id, mmt.subinventory_code, mmt.transfer_subinventory ), 21, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 22, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), 28, decode(sign (mta.primary_quantity), -1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code ), mmt.subinventory_code -- Fix for version 1.6 -- ) ) ) subinventory_code, -- End revision for version 1.15 -- Revision for version 1.27 mmt.transfer_subinventory, mmt.cost_group_id, mmt.transfer_cost_group_id, -- End revision for version 1.27 -- Revision for version 1.26 mmt.transaction_source_id, -- Revision for version 1.25 mmt.project_id, -- Revision for version 1.27 mmt.task_id, mmt.pm_cost_collected, -- End revision for version 1.27 wac.class_code wip_class, ml3.meaning class_type, ml4.meaning wip_type, we.wip_entity_name job, msiv2.concatenated_segments fg_item, msiv2.description fg_description, -- End revision for version 1.25 -- Revision for version 1.20 and 1.28, logic fix for the transaction quantity decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity ) primary_quantity, -- Revision for version 1.27 mta.cost_element_id, mta.resource_id, -- End revision for version 1.27 -- Revision for version 1.13 decode(mta.cost_element_id, 1, mta.base_transaction_value, 0) Matl_Amount, decode(mta.cost_element_id, 2, mta.base_transaction_value, 0) Matl_Ovhd_Amount, decode(mta.cost_element_id, 3, mta.base_transaction_value, 0) Resource_Amount, decode(mta.cost_element_id, 4, mta.base_transaction_value, 0) OSP_Amount, decode(mta.cost_element_id, 5, mta.base_transaction_value, 0) Overhead_Amount, -- End revision for version 1.13 mta.base_transaction_value mta_amount &p_show_pii2 from mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_parameters mp, -- Revision for version 1.25 wip_entities we, wip_accounting_classes wac, wip_discrete_jobs wdj, wip_flow_schedules wfs, mtl_system_items_vl msiv2, mfg_lookups ml1, -- Accounting Line Type mfg_lookups ml3, -- Class Type mfg_lookups ml4 -- WIP Type -- End revision for version 1.25 -- ======================================================== -- Material Transaction, date, wip job, org and item joins -- ======================================================== where mta.transaction_id = mmt.transaction_id and mp.organization_id = mta.organization_id and mta.transaction_source_type_id = 5 -- This gets rid of the full table scan on wip_entities and wdj.wip_entity_id (+) = mta.transaction_source_id and wdj.organization_id (+) = mta.organization_id and wfs.wip_entity_id (+) = mta.transaction_source_id and wfs.organization_id (+) = mta.organization_id and we.wip_entity_id (+) = mta.transaction_source_id and wac.class_code = nvl(wdj.class_code, wfs.class_code) and wac.organization_id = mp.organization_id and we.primary_item_id = msiv2.inventory_item_id and we.organization_id = msiv2.organization_id and ml1.lookup_type (+) = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code (+) = mta.accounting_line_type and ml3.lookup_type (+) = 'WIP_CLASS_TYPE' and ml3.lookup_code (+) = wac.class_type and ml4.lookup_type (+) = 'WIP_ENTITY' and ml4.lookup_code (+) = we.entity_type -- to use the mmt index N1 by inventory_item_id, organization_id and date and mmt.inventory_item_id = mta.inventory_item_id and 2=2 -- p_trx_date_from, p_trx_date_to 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 3=3 -- p_org_code and 4=4 -- p_item_number, p_min_transaction_amount, p_max_transaction_amount, p_absolute_txn_amount and 6=6 -- p_transaction_type &only_non_wip_sources2 -- End revision for version 1.25 ) acct_dist -- ======================================================== -- Material Transaction, org and item joins -- ======================================================== where msiv.organization_id = acct_dist.organization_id and msiv.inventory_item_id = acct_dist.inventory_item_id and mtt.transaction_type_id = acct_dist.transaction_type_id -- Revision for version 1.14 -- Join to mta instead of mmt for faster performance -- and mmt.transaction_source_type_id = mtst.transaction_source_type_id and mtst.transaction_source_type_id = acct_dist.transaction_source_type_id -- Revision for version 1.21 and msiv.primary_uom_code = muomv.uom_code -- Revision for version 1.22 and msiv.inventory_item_status_code = misv.inventory_item_status_code and oap.acct_period_id = acct_dist.acct_period_id -- Revision for version 1.27 -- This join does not work with interorg transfers -- and oap.organization_id = acct_dist.organization_id -- Revision for version 1.26 and acct_dist.inventory_item_id = pii.inventory_item_id (+) and acct_dist.organization_id = pii.organization_id (+) and acct_dist.organization_id = wro_pii.organization_id (+) and acct_dist.inventory_item_id = wro_pii.primary_item_id (+) and acct_dist.transaction_source_id = wro_pii.wip_entity_id (+) and acct_dist.transaction_source_type_id = wro_pii.transaction_source_type_id (+) -- End revision for version 1.26 -- Revision for version 1.27 and acct_dist.reason_id = mtr.reason_id (+) and acct_dist.created_by = fu.user_id (+) and acct_dist.resource_id = br.resource_id (+) and acct_dist.subinventory_code = msub.secondary_inventory_name (+) and acct_dist.organization_id = msub.organization_id (+) and acct_dist.cost_group_id = ccg.cost_group_id (+) and acct_dist.cost_group_id = ccg_xfer.cost_group_id (+) and acct_dist.cost_element_id = cce.cost_element_id (+) -- End revision for version 1.27 -- ======================================================== -- Revision for version 1.25, 1.27 Dynamic SQL joins -- ======================================================== &project_table_joins &pii_table_joins -- ======================================================== -- using base tables for organization information -- ======================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = acct_dist.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.31 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)) and 1=1 -- p_operating_unit, p_ledger, p_only_zero_amounts -- ======================================================== -- Revision for version 1.8 -- Joins for From and To Operating Units -- ======================================================== and hoi_from.org_information_context = 'Accounting Information' and hoi_from.organization_id = acct_dist.ship_from_org_id and hoi_from.organization_id = haou_from.organization_id -- this gets the organization name and haou2_from.organization_id = to_number(hoi_from.org_information3) -- this gets the operating unit id and hoi_to.org_information_context = 'Accounting Information' and hoi_to.organization_id = acct_dist.ship_to_org_id and hoi_to.organization_id = haou_to.organization_id -- this gets the organization name and haou2_to.organization_id = to_number(hoi_to.org_information3) -- this gets the operating unit id -- ======================================================== -- Lookup values to see more detail -- ======================================================== and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' and ml1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code = acct_dist.accounting_line_type -- Revision for version 1.23 and ml2.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml2.lookup_code = msiv.planning_make_buy_code -- ======================================================== -- Revision for version 1.25, SLA and Non-SLA joins. -- ======================================================== &mtl_sla_table_joins &mtl_non_sla_table_joins -- Revision for version 1.15 order by nvl(gl.short_name, gl.name), -- Ledger haou2.name, -- Operating_Unit acct_dist.organization_code, -- Org_Code -- Revision for version 1.25 -- ah.period_name, oap.period_name, -- End revision for version 1.25 &segment_columns_grp msiv.concatenated_segments, -- Item_Number ml1.meaning, -- Accounting Line Type mtt.transaction_type_name, -- Revision for version 1.25 msub.secondary_inventory_name &order_by_project &order_by_wip ,acct_dist.organization_id 1.31 28 Jul 2025 Douglas Volz Added Reason Codes and GL & OU Security Profiles. Y (:Accounting_Line_Type is not null or :Transaction_Type is not null or :Transaction_Source_Type is not null or :Transaction_Id is not null or :Minimum_Absolute_Amount is not null or :Only_Zero_Amounts is not null or :Item_Number is not null) and (:Organization_Code is not null) US CAC Material Account Detail Report to get the Material accounting distributions, in detail, for each item, organization and individual transaction. Including Ship From and Ship To information for inter-org transfers. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. Use Show Projects to display or not display the project information (project number, name, task and project cost collection status) and use Show WIP Job to display or not display the WIP job information (WIP class, class type, WIP job, description, assembly number and assembly description). For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Both Flow and Workorderless show up as the WIP Type "Flow schedule". And to get all positive and negative amounts above a threshold value, use the Minimum Absolute Amount parameter. Note: this report has identical code and logic as the CAC ICP PII Material Account Detail report, however, but with the use of hidden parameters, the PII (profit in inventory) features have been turned off. Parameters: =========== Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information (mandatory). If you choose No the report uses the pre-Create Accounting entries. Show Projects: display the project information. Enter Yes or No, use to limit the report size (mandatory). Show WIP: display the WIP job or flow schedule information. Enter Yes or No, use to limit the report size (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). Accounting Line Type: enter the accounting purpose or line type to report (optional). Transaction Type: select the material transaction type, such as PO Receipt, Return to Vendor or Account Alias Issue (optional). Transaction Source Type: select the material transaction source, such as Purchase Order or Account Alias (optional). Transaction Id: enter the transaction number or identifier to report (optional). Minimum Absolute Amount: enter the minimum debit or credit to report (optional). To see all accounting entries, enter zero (0). Only Zero Amounts: use this parameter to find entries with a zero transaction amount (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 2009- 2022 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. -- +=============================================================================+ -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 06 Nov 2009 Douglas Volz Initial Coding -- | 1.29 19 Oct 2022 Douglas Volz Performance improvements for PII item costs and WIP components. -- | 1.30 21 Oct 2022 Douglas Volz Logic change for resolving Internal RMA order numbers. -- | 1.31 28 Jul 2025 Douglas Volz Added Reason Codes and GL & OU Security Profiles. -- +=============================================================================+*/ Please specify at least one or more of the following: Accounting Line Type, Transaction Type, Transaction Source Type, Transaction Id, Minimum Absolute Amount, Only Zero Amounts or Item Number, plus one or more Organization Codes. Hint: to show all detailed entries, enter zero (0) for the Minimum Absolute Amount, along with one or more Organization Codes. Cost Accounting - Transactions Enginatics R12 only &category_columns &mtl_non_sla_table_joins &mtl_sla_table_joins &mtl_sla_tables &only_no_pii &only_non_wip_sources1 &only_non_wip_sources2 &order_by_project &order_by_wip &p_show_pii &p_show_pii2 &p_show_project &p_show_wip_job &p_sign_pii &pii_lookup_table &pii_table_joins &project_table_joins &project_tables &segment_columns &segment_columns_grp &show_document_number 1=1 2=2 3=3 4=4 5=5 6=6 7=7 :p_trx_date_to 1 -60 5=5 br.resource_code = :p_pii_sub_element LOV CST PII Sub-Elements E234C6573B214FDAE05369FB090533D7 Y select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code PII Y US PII Sub-Element 2 -50 5=5 cct.cost_type = :p_pii_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 PII Y US PII Cost Type 3 -40 &p_show_pii -- Indicate the PII WIP condition only for WIP valuation accounting line types and only for: -- WIP completion (44), WIP completion return (17), WIP assembly scrap (90), WIP return from scrap (91) ,case when (acct_dist.accounting_line_type = 7 and acct_dist.transaction_type_id in (17, 44, 90, 91)) then fl1.meaning else null end PII_Zero_Component_Quantity, -- Revision for version 1.25 pii.pii_cost_type PII_Cost_Type, nvl(pii.pii_item_cost,0) PII_Item_Cost, -- Revision for version 1.27 -- PII Amount = PII Function X PII Item Cost X Primary Quantity -- If the Gross Amount is zero there is no PII decode(acct_dist.mta_amount, 0, 0, acct_dist.pii_function * round(sum(acct_dist.primary_quantity * nvl(pii.pii_item_cost,0)),2)) PII_Amount, -- Net Amount = Amount - (PII Function X Corrected PII Item Cost X Primary Quantity) acct_dist.mta_amount - decode(acct_dist.mta_amount, 0, 0, acct_dist.pii_function * round(acct_dist.primary_quantity * nvl(pii.corrected_pii_item_cost,0),2)) Net_Amount -- End revision for version 1.27 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 N Y US Show Profit in Inventory 4 &only_no_pii and 1 = 2 -- invalidate this select statement if PII is not selected N US Show Profit in Inventory 5 &p_show_pii2 -- ========================================================================== -- Revision for version 1.27 and 1.28, create a function to turn PII on or -- off, based on the transaction action, accounting line type and cost element. -- ========================================================================== ,decode(mmt.transaction_action_id, 24, 0, -- No PII on Cost Updates decode(mta.accounting_line_type, 1, decode(mta.cost_element_id, -- Inv Valuation -- Assume cost_element_id 1 always exists 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 1), -- No cost element id (should not exist) 2, decode(mta.cost_element_id, -- Account or offset, also used for COGS and Accrual 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 1), -- No cost element id (normal condition) 3, 0, -- Overhead absorption has no PII 4, 0, -- Resource absorption has no PII 5, 1, -- Receiving Inspection has PII 6, 0, -- PPV has no cost element id and no PII 7, decode(mta.cost_element_id, -- WIP, PII on material costs -- Assume cost_element_id 1 always exists 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 0), 8, 0, -- WIP variance has no PII 9, 1, -- Inter-Org Payables, has PII 10, 1, -- Inter-org receivables, has PII 11, 0, -- Inter-org transfer credit has no PII 12, 0, -- Inter-org freight charge has no PII 13, 0, -- Average cost variance has no PII 14, 1, -- Intransit has PII 15, 0, -- Encumbrance Reversal 16, 1, -- Accrual has PII 17, 0, -- IPV has no PII 18, 0, -- Exchange rate variance has no PII 19, 0, -- Special Charge Expense has no PII 20, 1, -- Expense has PII 21, decode(mta.cost_element_id, -- Lot based job split issue 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 1), 22, decode(mta.cost_element_id, -- Lot based job split return 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 1), 23, decode(mta.cost_element_id, -- Lot based job merge issue 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 1), 24, decode(mta.cost_element_id, -- Lot based job merge return 1, 1, 2, 0, 3, 0, 4, 0, 5, 0, 1), 25, 0, -- Lot based job bonus issue 26, 0, -- Lot based job bonus return 27, 0, -- Lot based job quantity update issue 28, 0, -- Lot based job quantity update return 29, 1, -- Estimated Scrap Absorption has PII 30, 1, -- Profit in inventory has PII 31, 1, -- Clearing, part of receiving transactions 32, 0, -- Retroactive Price Adjustment has no PII 33, 0, -- Shikyu Variance has no PII 34, 1, -- Inter-org Profit (has not seen this used on a transaction) 35, decode(mta.cost_element_id, -- Cost of Goods Sold -- Assume cost_element_id 1 always exists 1, 1, -- Material 2, 0, 3, 0, 4, 0, 5, 0, 1), 36, decode(mta.cost_element_id, -- Deferred Cost of Goods Sold -- Assume cost_element_id 1 always exists 1, 1, -- Material 2, 0, 3, 0, 4, 0, 5, 0, 1), 37, 0, -- Standard Cost Update Adjustment 38, 0, -- Landed Cost Absorption 99, 0, -- Unknown 0) ) pii_function Y US Show Profit in Inventory 6 &pii_lookup_table fnd_lookups fl1, Y US Show Profit in Inventory 7 &pii_lookup_table2 ,mfg_lookups ml2 Y US Show Profit in Inventory 8 &pii_table_joins and fl1.lookup_type (+) = 'YES_NO' and fl1.lookup_code (+) = wro_pii.wip_pii_component_quantity Y US Show Profit in Inventory 9 -30 :p_sign_pii Number 1 US Numeric Sign for PII 10 -20 &show_document_number 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), -- Revision for version 1.20, remove concatenated acct segments 3, ml2.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 -- Revision for version 1.20, remove concatenated acct segments 1, ml2.meaning, -- Issue from stores 2, ml2.meaning, -- Subinventory transfer 3, ml2.meaning, -- Direct organization transfer 5, ml2.meaning, -- Planning transfer 6, ml2.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, ml2.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, ml2.meaning, -- Logical Intransit Receipt 21, ml2.meaning, -- Intransit Shipment 22, ml2.meaning, -- Logical Intransit Shipment 24, ml2.meaning, -- Average Cost Update -- Revision for version 1.20, remove concatenated acct segments 27, ml2.meaning, -- Receipt into Stores ml2.meaning), -- Revision for version 1.30 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), ml2.meaning -- Any other source type -- End revision for version 1.30 ) document_order_num, 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 Y US Show Document Number 11 -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')) Y US Chart of Accounts 12 &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 13 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 14 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 15 30 &mtl_sla_tables xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al, 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 Y US Show SLA Accounting 16 &mtl_non_sla_table_joins and gcc.code_combination_id (+) = acct_dist.reference_account N US Show SLA Accounting 17 &mtl_sla_table_joins and ah.ledger_id = gl.ledger_id 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 xdl.application_id = 707 and xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = acct_dist.inv_sub_ledger_id and gcc.code_combination_id (+) = al.code_combination_id Y US Show SLA Accounting 18 40 &p_show_project pp.segment1 Project_Number, pp.name Project_Name, pt.task_number Task_Number, pt.task_name Task_Name, pt.wbs_level WBS_Level, pt.description Task_Description, al.meaning Project_Cost_Collected, 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 N Y US Show Projects 19 &order_by_project ,pp.segment1 ,pp.name -- Revision for version 1.27 ,pt.task_number ,pt.task_name Y US Show Projects 20 &project_table_joins and acct_dist.project_id = pp.project_id (+) -- Revision for version 1.27 and acct_dist.task_id = pt.task_id (+) and al.lookup_code (+) = acct_dist.pm_cost_collected and al.lookup_type (+) = 'AR_PROCESS_STATUS' -- End revision for version 1.27 Y US Show Projects 21 &project_tables pa_projects_all pp, pa_tasks pt, ar_lookups al, Y US Show Projects 22 50 &p_show_wip_job acct_dist.wip_class WIP_Class, acct_dist.class_type Class_Type, acct_dist.wip_type WIP_Type, acct_dist.job WIP_Job, acct_dist.fg_item Assembly_Number, acct_dist.fg_description Assembly_Description, 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 N Y US Show WIP Job 23 &only_non_wip_sources1 and mta.transaction_source_type_id <> 5 Y US Show WIP Job 24 &only_non_wip_sources2 and 1 = 2 -- invalidate this WIP select statement N US Show WIP Job 25 &order_by_wip ,acct_dist.wip_class ,acct_dist.wip_type ,acct_dist.job Y US Show WIP Job 26 60 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'acct_dist') 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 27 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'acct_dist') 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 28 80 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'acct_dist') 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 29 90 1=1 ml1.meaning = :p_accounting_line_type LOV CST Accounting Line Type E7B8D5BA75FBA5ACE05369FB090593D7 Y select meaning value, description from mfg_lookups where lookup_type='CST_ACCOUNTING_LINE_TYPE' and enabled_flag='Y' order by meaning US Accounting Line Type 30 100 6=6 mmt.transaction_type_id in (select mtt.transaction_type_id from mtl_transaction_types mtt where mtt.transaction_type_name = :p_transaction_type) LOV INV Transaction Type 8E2FF36EDEB579D2E0530100007F1FF2 Y select mtt.transaction_type_name value, mtt.description from mtl_transaction_types mtt order by mtt.transaction_type_name US Transaction Type 31 110 7=7 mmt.transaction_source_type_id in (select mtst.transaction_source_type_id from mtl_txn_source_types mtst where mtst.transaction_source_type_name = :p_transaction_source) LOV INV Transaction Source Type 8E2FF36EDEB679D2E0530100007F1FF2 Y select mtst.transaction_source_type_name value, mtst.description from mtl_txn_source_types mtst order by transaction_source_type_name US Transaction Source Type 32 120 4=4 mta.transaction_id = :p_transaction_id Number US Transaction Id 33 130 4=4 abs(mta.base_transaction_value) >= :p_min_absolute_amount Number US Minimum Absolute Amount 34 140 4=4 mta.base_transaction_value = 0 Number US Only Zero Amounts 35 150 4=4 mmt.inventory_item_id in (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 36 160 3=3 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 37 170 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 38 180 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 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code PII Sub-Element