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 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 与库存组织有关的会计科目表 8E2FF36EDF3A79D2E0530100007F1FF2 GL Ledger (inv org related) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name AR جميع دفاتر الأستاذ المتعلقة بجميع منظمات المخزون النشطة D Alle Ledger, die sich auf alle aktiven Inventarorganisationen beziehen E Todos los libros de contabilidad relacionados con todas las organizaciones de inventario activas F Tous les grands livres relatifs à tous les organismes d'inventaire actifs I Tutti i libri contabili relativi a tutte le organizzazioni di inventario attive JA すべてのアクティブな在庫組織に関連するすべての台帳 KO 모든 활성 재고 조직과 관련된 모든 원장 PTB Todos os livros contábeis relacionados a todas as organizações de inventário ativas RU Все бухгалтерские книги, относящиеся ко всем организациям, занимающимся активным инвентаризацией. S Alla huvudböcker relaterade till alla aktiva lagerorganisationer TR Tüm aktif envanter organizasyonlarıyla ilgili tüm defterler US All ledgers related to all active inventory organizations ZHS 与所有现行清单组织有关的所有分类账 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US EC5CECA520510FC8E0530100007F5EB3 INV Item Number (costing enabled only items) Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US 9B9627743E84607DE053BB6B635805FB INV Operating Unit Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 AR جميع وحدات التشغيل المرتبطة بمؤسسة مخزون غير رئيسية نشطة D Alle Betriebseinheiten, die mit einer aktiven Nicht-Master-Inventarisierungsorganisation verbunden sind E Todas las unidades operativas relacionadas con una organización de inventario no maestra activa F Toutes les unités opérationnelles liées à un organisme d'inventaire non maître actif I Tutte le unità operative legate a un'organizzazione di inventario non master attiva JA アクティブな非マスターインベントリ組織に関連するすべてのオペレーティング・ユニット KO 활성 비 마스터 재고 조직과 관련된 모든 운영 단위 PTB Todas as unidades operacionais relacionadas a uma organização ativa de inventário não-mestre RU Все операционные единицы, относящиеся к действующей организации, не являющейся ведущим поставщиком инвентарных запасов. S Alla operativa enheter relaterade till en aktiv organisation som inte är huvudlager TR Etkin bir ana envanter organizasyonu ile ilgili tüm işletim birimleri US All operating units related to an active non master inventory organization ZHS 与在用的非总清单组织有关的所有业务单位： 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 EB27FA4B5555156DE0530100007FD8D2 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 &p_show_sla_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.25 &p_show_subinventory &p_show_project &p_show_wip_job -- End revision for version 1.25 muomv.uom_code UOM_Code, sum(acct_dist.primary_quantity) Quantity, gl.currency_code Currency_Code, sum(acct_dist.Matl_Amount) Material_Amount, sum(acct_dist.Matl_Ovhd_Amount) Material_Overhead_Amount, sum(acct_dist.Resource_Amount) Resource_Amount, sum(acct_dist.OSP_Amount) Outside_Processing_Amount, sum(acct_dist.Overhead_Amount) Overhead_Amount, sum(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, 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, &subledger_tab -- 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 version 1.25 &subinventory_table &project_tables -- Revision for version 1.26 -- =========================================================== -- Revision 1.28, sum up the accounting transactions as an -- intermediary step. Needed for accurate quantities. -- =========================================================== (select acct_dist2.organization_code, acct_dist2.ship_from_org, acct_dist2.ship_to_org, acct_dist2.organization_id, acct_dist2.ship_from_org_id, acct_dist2.ship_to_org_id, acct_dist2.fob_point, acct_dist2.acct_period_id, &p_show_non_sla &p_show_sla acct_dist2.inventory_item_id, acct_dist2.transaction_source_type_id, acct_dist2.transaction_type_id, acct_dist2.accounting_line_type, &p_show_subinventory2 &p_show_project2 &p_show_wip_job2 sum(acct_dist2.primary_quantity) primary_quantity, sum(acct_dist2.Matl_Amount) Matl_Amount, sum(acct_dist2.Matl_Ovhd_Amount) Matl_Ovhd_Amount, sum(acct_dist2.Resource_Amount) Resource_Amount, sum(acct_dist2.OSP_Amount) OSP_Amount, sum(acct_dist2.Overhead_Amount) Overhead_Amount, sum(acct_dist2.mta_amount) mta_amount -- Revision for version 1.26 &p_show_pii2 from &mtl_sla_tables -- Revision for version 1.26 &pii_lookup_table -- fnd_lookups fl1, -- =========================================================== -- 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, 'Y' 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 -- and 1 = 2 -- invalidate this select statement if PII is not selected 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, 'Y', -- 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, -- 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, -- Revision for version 1.28 mmt.transaction_id, mmt.inventory_item_id, mmt.transaction_source_type_id, 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.26 mmt.transaction_source_id, -- Revision for version 1.25 mmt.project_id, 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 -- Use quantity adjusted for Standard Cost Updates, else use the primary qty. decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity) primary_quantity, -- End revision for version 1.28 -- 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 -- Revision for version 1.28 &p_show_pii3 from mtl_transaction_accounts mta, mtl_material_transactions mmt, mtl_parameters mp, -- Fix for version 1.6 -- Revision for version 1.5 -- mtl_parameters mp_owning_org, -- Owning Org -- End fix for version 1.6 mtl_parameters mp_xfer_org, -- Transfer Org mtl_parameters mp_mmt_org, -- MMT Org mfg_lookups ml1 -- Accounting Line Type -- End revision for version 1.5 -- ======================================================== -- Material transaction, date, org and item joins -- ======================================================== where mta.transaction_id = mmt.transaction_id and mp.organization_id = mta.organization_id -- Fix for version 1.6 and mp_xfer_org.organization_id = nvl(mmt.transfer_organization_id, mmt.organization_id) and mp_mmt_org.organization_id = mmt.organization_id -- End fix for version 1.6 -- 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 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 &only_non_wip_sources1 -- and mta.transaction_source_type_id <> 5 union all -- Revision for version 1.25 -- Now join to WIP entries select mp.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, -- Revision for version 1.28 mmt.transaction_id, mmt.inventory_item_id, mmt.transaction_source_type_id, 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.26 mmt.transaction_source_id, -- Revision for version 1.25 mmt.project_id project_id, 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 -- Use quantity adjusted for Standard Cost Updates, else use the primary qty. decode(mmt.transaction_action_id, 24, mmt.quantity_adjusted, mta.primary_quantity) primary_quantity, -- End revision for version 1.28 -- 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 -- Revision for version 1.28 &p_show_pii3 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 &only_non_wip_sources2 -- and 1 = 2 -- invalidate this WIP select statement -- End revision for version 1.25 ) acct_dist2 where acct_dist2.organization_id = wro_pii.organization_id (+) and acct_dist2.inventory_item_id = wro_pii.primary_item_id (+) and acct_dist2.transaction_source_id = wro_pii.wip_entity_id (+) and acct_dist2.transaction_source_type_id = wro_pii.transaction_source_type_id (+) -- Revision for version 1.26 &pii_table_joins &mtl_sla_table_joins group by acct_dist2.organization_code, acct_dist2.ship_from_org, acct_dist2.ship_to_org, acct_dist2.organization_id, acct_dist2.ship_from_org_id, acct_dist2.ship_to_org_id, acct_dist2.fob_point, acct_dist2.acct_period_id, &group_by_non_sla &group_by_sla acct_dist2.inventory_item_id, acct_dist2.transaction_source_type_id, acct_dist2.transaction_type_id, -- Revision for version 1.25 &group_by_subinventory2 &group_by_project2 &group_by_wip_job2 &group_by_pii2 acct_dist2.accounting_line_type ) 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 (+) -- End revision for version 1.26 -- ======================================================== -- Revision for version 1.25, Dynamic SQL joins -- ======================================================== &subinv_table_joins &project_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 and 1=1 -- p_operating_unit, p_ledger &subledger_join -- ======================================================== -- 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.17, outer join for CCIDs and gcc.code_combination_id (+) = acct_dist.code_combination_id -- ========================================================== group by nvl(gl.short_name, gl.name), &group_by_subledger haou2.name, acct_dist.organization_code, -- Revision for version 1.8 haou2_from.name, -- From OU haou2_to.name, -- To OU -- Revision for version 1.19 acct_dist.fob_point, -- FOB Point -- End revision for version 1.8 -- Revision for version 1.5 acct_dist.ship_from_org, acct_dist.ship_to_org, -- End revision for version 1.5 -- Revision for version 1.25 -- ah.period_name, oap.period_name, -- End revision for version 1.25 &segment_columns_grp msiv.concatenated_segments, msiv.description, -- Revision for version 1.19 fcl.meaning, -- item_type -- Revision for version 1.22 misv.inventory_item_status_code, -- Revision for version 1.23 ml2.meaning, -- make_buy_code -- Revision for version 1.12 and 1.24 acct_dist.organization_id, acct_dist.inventory_item_id, -- End revision for version 1.12 and 1.24 -- Revision for version 1.11 ml1.meaning, -- accounting line type mtt.transaction_type_name, -- Revision for version 1.13 mtst.transaction_source_type_name, -- Revision for version 1.25 &group_by_subinventory &group_by_project &group_by_wip_job muomv.uom_code, -- Revision for version 1.26 &group_by_pii gl.currency_code -- Revision for version 1.15 order by nvl(gl.short_name, gl.name), -- Ledger &order_by_subledger 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 &order_by_subinventory &order_by_project &order_by_wip_job ,acct_dist.organization_id Replaced tab characters with spaces Y D CAC Material Account Summary Bericht, um die Materialbuchhaltungseinträge für jeden Artikel, jede Organisation und jedes Unterlager mit Beträgen zu erhalten. Einschließlich der Informationen "Versand von" und "Versand an" für Transfers zwischen Organisationen. Dieser Bericht enthält alle Materialtransaktionen, aber um den Bericht kleiner zu halten, werden keine WIP-Auftragsinformationen angezeigt, wie z. B. WIP-Buchungsklasse, Klassenbeschreibung, Baugruppennummer, Baugruppenbeschreibung oder Auftragsnummer. Verwenden Sie den Parameter "Show Subinventory" (Unterbestand anzeigen), um die Größe dieses Berichts je nach Bedarf zu reduzieren. Wenn Sie "Ja" wählen, erhalten Sie den Unterlagerungscode, wenn Sie "Nein" wählen, erhalten Sie nur den Buchhaltungszeilentyp für den Bestand (Bestandsbewertung), wodurch die Größe dieses Berichts stark reduziert wird. /* +=============================================================================+ -- | Copyright 2009-20 Douglas Volz Consulting, Inc. | | | Die Erlaubnis zur Verwendung dieses Codes wird erteilt, sofern der ursprüngliche Autor | -- | | anerkannt wird. Diese | | | Erlaubnis beinhaltet keine ausdrücklichen oder anderweitigen Garantien. -- | | Erlaubnis. | -- +=============================================================================+ -- | -- | Originalautor: Douglas Volz (doug@volzconsulting.com) -- | -- | | Programmname: xxx_mtl_dist_xla_sum_rept.sql -- | -- | Parameter: -- | p_trx_date_from -- Start-Transaktionsdatum für PII-bezogene Transaktionen, obligatorisch -- | p_trx_date_to -- End-Transaktionsdatum für PII-bezogene Transaktionen, obligatorisch -- | p_category_set1 -- Der erste zu meldende Positionstypensatz, typischerweise der -- | Kosten- oder Produktlinien-Kategoriesatz -- | p_category_set2 -- Der zweite zu meldende Artikelkategoriesatz, typischerweise der -- | Bestandskategoriesatz -- | p_org_code -- Spezifische Bestandsorganisation, über die Sie berichten möchten (optional) -- | p_operating_unit -- Die Betriebseinheit, die Sie berichten möchten, lassen Sie leer für alle -- | operating units (optional) -- | p_ledger -- Hauptbuch, über das Sie berichten möchten, für alle leer lassen -- | ledger (optional) -- | p_show_subinv -- Zeigen Sie den Subinventar-Code an oder zeigen Sie den Subinventar-Code nicht an. -- | Geben Sie einen 'Y'- oder 'N'-Wert ein. Obligatorisch. Verwenden Sie diese Option, um die Berichtsgröße zu begrenzen. -- | -- | Beschreibung: -- | Bericht, um die Materialbuchhaltungseinträge für jeden Artikel, jede Organisation und jedes Unterlager zu erhalten -- | mit Beträgen. Einschließlich Versand von und Versand an Informationen für organisationsübergreifende -- | | Transfers. -- | -- | Version Geändert am Geändert von Beschreibung -- | ======= =========== ============== ========================================= -- | 1.0 06 Nov 2009 Douglas Volz Erstkodierung -- | 1.19 22 Apr 2020 Douglas Volz Item Type Lookup Code in innere Abfragen einfügen, um zu vermeiden -- | Fehler beim Erstellen von äußeren Joins zu mehreren Tabellen (12.1.3). -- | Artikelstamm wieder in innere Abfragen für den Artikeltyp eingefügt -- | Lookup und FOB-Punkt in einen Lookup-Code für Sprachen geändert. -- | 1.20 03. Mai 2020 Douglas Volz Kann mehrere mta-Zeilen mit der gleichen CCID, Menge -- | und Buchungszeilentyp haben. Um falsches Summieren zu vermeiden, -- | müssen Sie die Anzahl der Zeilen zählen und dann durch die -- | Gesamtmenge summieren. Für Standardkosten-Updates müssen Sie jedoch -- | die angepasste Menge verwenden. -- | 1.21 14. Mai 2020 Douglas Volz Mehrsprachige Tabelle für UOM_Code verwenden, mtl_units_of_measure_vl -- | 1.22 17. Mai 2020 Douglas Volz Innere Abfrage group by entfernen, nicht benötigt. -- +=============================================================================+*/ I CAC Material Account Summary Rapporto per ottenere le voci contabili del materiale per ogni articolo, organizzazione, sottoinventario con importi. Include le informazioni Ship From e Ship To per i trasferimenti tra le organizzazioni. Questo rapporto include tutte le transazioni di materiale, ma per mantenere il rapporto più piccolo non mostra le informazioni sul lavoro WIP, come la classe contabile WIP, la descrizione della classe, il numero di montaggio, la descrizione del montaggio o il numero di commessa. Usa il parametro Show Subinventory per ridurre le dimensioni di questo rapporto, se necessario. Se scegliete Sì otterrete il codice del sottoinventario, se scegliete No otterrete solo il tipo di linea contabile per l'inventario, (valutazione dell'inventario) riducendo così notevolmente le dimensioni di questo rapporto. /* +=============================================================================+ -- | Copyright 2009-20 Douglas Volz Consulting, Inc. | -- | Il permesso di usare questo codice è concesso a condizione che l'autore originale sia | -- | | riconosciuto. Nessuna garanzia, espressa o di altro tipo, è inclusa in questo permesso. -- | permesso. | -- +=============================================================================+ -- | -- | Autore originale: Douglas Volz (doug@volzconsulting.com) -- | -- | Nome del programma: xxx_mtl_dist_xla_sum_rept.sql -- | -- | Parametri: -- | p_trx_date_from -- data di inizio della transazione per le transazioni relative al PII, obbligatoria -- | p_trx_date_to -- data di fine della transazione per le transazioni relative al PII, obbligatoria -- | p_category_set1 -- La prima categoria di voci da segnalare, tipicamente la -- | Set di categorie di costo o di linea di prodotto -- | p_category_set2 -- La seconda categoria di articoli da segnalare, tipicamente la -- | Set di categorie di inventario -- | p_org_code -- Organizzazione specifica dell'inventario che vuoi segnalare (opzionale) -- | p_operating_unit -- Unità operativa che vuoi segnalare, lascia vuoto per tutte -- | unità operative (opzionale) -- p_ledger -- Contabilità generale che vuoi segnalare, lascia in bianco per tutti -- | ledger (opzionale) -- | p_show_subinv -- visualizza il codice del sottoinventario o non visualizzarlo. -- | Inserisci un valore 'Y' o 'N'. Obbligatorio. Usare per limitare la dimensione del report. -- | -- | Descrizione: -- | Report per ottenere le voci della contabilità materiali per ogni articolo, organizzazione e sottoinventario -- | | con importi. Include le informazioni Ship From e Ship To per i trasferimenti inter-org. -- | trasferimenti. -- | -- | Versione Modificata il Modificato da Descrizione -- | ======= =========== ============== ========================================= -- | 1.0 06 Nov 2009 Douglas Volz Codifica iniziale -- | 1.19 22 Apr 2020 Douglas Volz Mettere il codice di ricerca del tipo di elemento nelle query interne, per evitare -- | creare errori di outer joins a tabelle multiple (12.1.3). -- | Rimetti l'item master nelle query interne per il tipo di articolo -- | lookup e cambiato il punto FOB in un codice di ricerca per le lingue. -- | 1.20 03 maggio 2020 Douglas Volz Può avere più righe mta con lo stesso CCID, quantità -- | e tipo di linea contabile. Per evitare di sommare erroneamente, -- | bisogno di contare il numero di righe e poi dividere nella -- | somma della quantità totale. Tuttavia, per gli aggiornamenti dei costi standard, -- | utilizzare la quantità aggiustata. -- | 1.21 14 maggio 2020 Douglas Volz Usa la tabella multilingue per UOM_Code, mtl_units_of_measure_vl -- | 1.22 17 maggio 2020 Douglas Volz Rimuovere la query interna group by, non necessaria. -- +=============================================================================+*/ KO CAC Material Account Summary 금액이있는 각 품목, 조직, 창고에 대한 자재 회계 입력을 가져 오려면보고합니다. 조직 간 이전을위한 출고 처 및 납품처 정보 포함. 이 보고서에는 모든 자재 트랜잭션이 포함되지만 보고서를 작게 유지하기 위해 WIP 계정 분류, 분류 설명, 조립품 번호, 조립품 설명 또는 작업 주문 번호와 같은 WIP 작업 정보는 표시되지 않습니다. 필요에 따라이 보고서의 크기를 줄이려면 창고 표시 매개 변수를 사용합니다. 예를 선택하면 창고 코드가 표시되고 아니오를 선택하면 재고에 대한 회계 라인 유형 (재고 평가) 만 표시되므로이 보고서의 크기가 크게 줄어 듭니다. / * + ============================================= ============================= + -| 저작권 2009-20 Douglas Volz Consulting, Inc. | -| 이 코드를 사용할 수있는 권한은 원래 작성자가 | -| 인정. 여기에는 명시 적 또는 기타 어떠한 보증도 포함되지 않습니다. -| 허가. | -+ ============================================= ============================= + -| -| 원저자 : Douglas Volz (doug@volzconsulting.com) -| -| 프로그램 이름 : xxx_mtl_dist_xla_sum_rept.sql -| -| 매개 변수 : -| p_trx_date_from-PII 관련 트랜잭션의 시작 트랜잭션 날짜, 필수 -| p_trx_date_to-PII 관련 트랜잭션의 종료 트랜잭션 날짜, 필수 -| p_category_set1-보고 할 첫 번째 항목 범주 세트, 일반적으로 -| 비용 또는 제품 라인 범주 세트 -| p_category_set2-보고 할 두 번째 항목 범주로, 일반적으로 -| 재고 범주 세트 -| p_org_code-보고 할 특정 재고 조직 (선택 사항) -| p_operating_unit-보고 할 운영 단위, 모두 비워 둡니다. -| 운영 단위 (선택 사항) -| p_ledger-보고하려는 총계정 원장, 모두 비워 둡니다. -| 원장 (선택 사항) -| p_show_subinv-창고 코드를 표시하거나 창고 코드를 표시하지 않습니다. -| 'Y'또는 'N'값을 입력하십시오. 필수입니다. 보고서 크기를 제한하는 데 사용합니다. -| -| 기술: -| 각 품목, 조직 및 창고에 대한 자재 회계 입력을 가져 오는 보고서 -| 금액. 조직간에 대한 출고 처 및 납품처 정보 포함 -| 전송. -| -| 수정 된 버전 설명에 의해 수정 됨 -| ======= =========== =============================== ======================= -| 1.0 2009 년 11 월 6 일 Douglas Volz 초기 코딩 -| 1.19 22 Apr 2020 Douglas Volz 항목 유형 조회 코드를 내부 쿼리에 넣습니다. -| 여러 테이블에 대한 외부 조인 오류 생성 (12.1.3). -| 항목 유형에 대한 내부 쿼리에 항목 마스터를 다시 넣습니다. -| 조회 및 FOB 지점을 언어 조회 코드로 변경했습니다. -| 1.20 03 May 2020 Douglas Volz는 동일한 CCID, 수량으로 여러 mta 행을 가질 수 있습니다. -| 및 회계 라인 유형. 잘못 합산하지 않으려면 -| 행 수를 계산 한 다음 -| 총 수량 합계. 그러나 표준 비용 업데이트의 경우 -| 조정 된 양을 사용하십시오. -| 1.21 2020 년 5 월 14 일 Douglas Volz UOM_Code, mtl_units_of_measure_vl에 다국어 테이블 사용 -| 1.22 2020 년 5 월 17 일 Douglas Volz 필요하지 않은 내부 쿼리 그룹을 제거합니다. -+ ============================================== ============================= + * / PTB CAC Material Account Summary Relatório para obter os lançamentos contábeis de material para cada item, organização, subinventario com valores. Incluindo informações de Ship From e Ship To para transferências inter-org. Este relatório inclui todas as transações de material, mas para manter o relatório menor, ele não exibe informações de trabalho WIP, tais como Classe de Contabilidade WIP, Descrição de Classe, Número de Montagem, Descrição de Montagem ou Número de Pedido de Trabalho. Use o parâmetro Mostrar Subinventor para reduzir o tamanho deste relatório, conforme necessário. Se você escolher Sim você obtém o Código do Subinventor, se escolher Não você obtém apenas o Tipo de Linha de Contabilidade para o inventário, (Avaliação do inventário) reduzindo assim grandemente o tamanho deste relatório. /* +=============================================================================+ | Copyright 2009-20 Douglas Volz Consulting, Inc. | | A permissão para usar este código é concedida desde que o autor original seja | | reconhecido. Nenhuma garantia, expressa ou não, está incluída nisto |. | permissão. | -- +=============================================================================+ -- | Autor original: Douglas Volz (doug@volzconsulting.com) | Nome do programa: xxx_mtl_dist_xla_sum_rept.sql | Parâmetros: | p_trx_date_from -- data de início da transação para transações relacionadas com PII, obrigatório | p_trx_date_to -- data final de transação para transações relacionadas com PII, obrigatório | p_category_set1 -- A primeira categoria de item definida para relatar, tipicamente o | Conjunto de categorias de custo ou linha de produtos | p_category_set2 -- A segunda categoria de item definida para relatar, tipicamente o | Conjunto de Categorias de Estoque | p_org_code -- Organização de inventário específica que você deseja relatar (opcional) | p_operating_unit -- Unidade operacional que você deseja relatar, deixe em branco para todos | unidades operacionais (opcional) | p_ledger -- livro razão geral que você deseja relatar, deixe em branco para todos | ledgers (opcional) | p_show_subinv -- exibir o código do subinventor ou não exibir o código do subinventor. | insira um valor 'Y' ou 'N'. Obrigatório. Use para limitar o tamanho do relatório. -- | -- | Descrição: | Relatório para obter os lançamentos contábeis de material para cada item, organização e subinventario | com valores. Incluindo informações de Ship From e Ship To para Interorg | transferências. -- | | Versão Modificada em Modificada por Descrição -- | ======= =========== ============== ========================================= | 1.0 06 Nov 2009 Douglas Volz Codificação Inicial | 1.19 22 Abr 2020 Douglas Volz Colocar o código do tipo de item em consultas internas, para evitar | criando erros de união externa a múltiplas tabelas (12.1.3). -- | colocar o item mestre de volta em consultas internas para o tipo de item | lookup e mudou o ponto FOB para um código de pesquisa de idiomas. | 1.20 03 maio 2020 Douglas Volz Pode ter múltiplas linhas de mta com o mesmo CCID, quantidade | e tipo de linha de contabilidade. Para evitar a soma incorreta, | necessidade de contar o número de filas e depois dividir em | soma total da quantidade. Entretanto, para Atualizações de Custo Padrão, -- | utilize a quantidade ajustada. | 1.21 14 de maio de 2020 Douglas Volz Usar tabela multilíngüe para UOM_Code, mtl_units_of_measure_vl | 1.22 17 de maio de 2020 Douglas Volz Remover grupo de consulta interno por, não necessário. -- +=============================================================================+*/ S CAC Material Account Summary Rapportera för att få de materiella bokföringsposterna för varje artikel, organisation, underlager med belopp. Inkluderar Ship From och Ship To information för överföringar mellan organisationer. Denna rapport innehåller alla väsentliga transaktioner, men för att hålla rapporten mindre visar den inte WIP-jobbinformation, såsom WIP-redovisningsklass, klassbeskrivning, monteringsnummer, monteringsbeskrivning eller jobbordernummer. Använd parametern Visa underinventering för att minska storleken på den här rapporten efter behov. Om du väljer Ja får du lagerkoden, om du väljer nej får du bara redovisningstypen för lager, (lagervärdering), vilket minskar storleken på denna rapport. / * + ================================================== ================================ + - | Upphovsrätt 2009-20 Douglas Volz Consulting, Inc. | - | Tillstånd att använda den här koden beviljas förutsatt att den ursprungliga författaren är | - | erkänd. Inga garantier, uttryckliga eller på annat sätt ingår i detta | - | lov. | - + ================================================= ================================ + - | - | Ursprunglig författare: Douglas Volz (doug@volzconsulting.com) - | - | Programnamn: xxx_mtl_dist_xla_sum_rept.sql - | - | Parametrar: - | p_trx_date_from - starttransaktionsdatum för PII-relaterade transaktioner, obligatoriskt - | p_trx_date_to - transaktionsdatum för PII-relaterade transaktioner, obligatoriskt - | p_category_set1 - Den första artikelkategorin som ska rapporteras, vanligtvis - | Kostnad eller produktlinjekategoriuppsättning - | p_category_set2 - Den andra artikelkategorin som ska rapporteras, vanligtvis - | Inventory Category Set - | p_org_code - Specifik inventeringsorganisation som du vill rapportera (valfritt) - | p_operating_unit - Operationsenhet som du vill rapportera, lämna tom för alla - | manöverenheter (tillval) - | p_ledger - huvudbok som du vill rapportera, lämna tomt för alla - | ledgers (valfritt) - | p_show_subinv - visa underinventeringskoden eller visa inte underinventeringskoden. - | Ange ett 'Y' eller 'N' värde. Obligatorisk. Använd för att begränsa rapportstorleken. - | - | Beskrivning: - | Rapportera för att få de väsentliga bokföringsposterna för varje artikel, organisation och underlager - | med belopp. Inklusive Ship From och Ship To information för inter-org - | överföringar. - | - | Version modifierad på modifierad av beskrivning - | ===================================================== ========================= - | 1.0 06 nov 2009 Douglas Volz inledande kodning - | 1.19 22 apr 2020 Douglas Volz Sätta artikeltypsökningskod i inre frågor, för att undvika - | skapa yttre kopplingar fel till flera tabeller (12.1.3). - | Sätt tillbaka artikelmastern i inre frågor för artikeltypen - | uppslag och ändrat FOB-punkt till en uppslagskod för språk. - | 1.20 03 maj 2020 Douglas Volz Kan ha flera mta-rader med samma CCID, kvantitet - | och bokföringstyp. För att undvika att summera fel, - | måste räkna antalet rader och sedan dela i - | total kvantitetssumma. För standardkostnadsuppdateringar, - | använd den justerade kvantiteten. - | 1.21 14 maj 2020 Douglas Volz Använd flerspråkig tabell för UOM_Code, mtl_units_of_measure_vl - | 1.22 17 maj 2020 Douglas Volz Ta bort inre frågegrupp av, behövs inte. - + ================================================== ================================ + * / TR CAC Material Account Summary Tutarlı her kalem, organizasyon, alt envanter için malzeme muhasebesi girişlerini almak için rapor. Kuruluşlar arası aktarımlar için Gönderen ve Gönderilecek Yer bilgileri dahil. Bu rapor tüm malzeme hareketlerini içerir, ancak raporu daha küçük tutmak için Devam Eden Çalışma Hesap Sınıfı, Sınıf Açıklaması, Montaj Numarası, Montaj Açıklaması veya İş Emri Numarası gibi Devam Eden Çalışma işi bilgilerini görüntülemez. Bu raporun boyutunu gerektiği gibi küçültmek için Alt Envanteri Göster parametresini kullanın. Evet'i seçerseniz, Alt Stok Kodunu alırsınız, Hayır'ı seçerseniz, yalnızca envanter için Muhasebe Satır Türünü (Stok değerlemesi) alırsınız, böylece bu raporun boyutunu büyük ölçüde azaltırsınız. / * + ============================================= ============================ + - | Telif Hakkı 2009-20 Douglas Volz Consulting, Inc. | - | Bu kodu kullanma izni, orijinal yazarın | - | kabul edildi. Buna açık veya başka hiçbir garanti dahil edilmemiştir | - | izin. | - + =============================================== ============================ + - | - | Orijinal Yazar: Douglas Volz (doug@volzconsulting.com) - | - | Program Adı: xxx_mtl_dist_xla_sum_rept.sql - | - | Parametreler: - | p_trx_date_from - PII ile ilgili işlemler için işlem başlangıç tarihi, zorunlu - | p_trx_date_to - PII ile ilgili işlemler için işlem bitiş tarihi, zorunlu - | p_category_set1 - Raporlanacak ilk öğe kategorisi, genellikle - | Maliyet veya Ürün Grubu Kategori Kümesi - | p_category_set2 - Raporlanacak ikinci öğe kategorisi, genellikle - | Envanter Kategori Kümesi - | p_org_code - Raporlamak istediğiniz belirli envanter organizasyonu (isteğe bağlı) - | p_operating_unit - Bildirmek istediğiniz İşletim Birimi, tümü için boş bırakın - | işletim birimleri (isteğe bağlı) - | p_ledger - rapor etmek istediğiniz genel muhasebe, tümü için boş bırakın - | defterler (isteğe bağlı) - | p_show_subinv - alt envanter kodunu görüntüleyin veya alt envanter kodunu görüntüleme. - | Bir 'E' veya 'N' değeri girin. Zorunlu. Rapor boyutunu sınırlamak için kullanın. - | - | Açıklama: - | Her bir kalem, organizasyon ve alt envanter için malzeme muhasebesi girişlerini almak için rapor - | miktarlarla. Kuruluşlar arası için Gönderim ve Varış yeri bilgileri dahil - | transferler. - | - | Açıklamaya Göre Değiştirildiğinde Değiştirilen Sürüm - | ======= =========== ============== ================== ======================= - | 1.0 06 Kas 2009 Douglas Volz İlk Kodlama - | 1.19 22 Nis 2020 Douglas Volz İç sorgulara Öğe Türü arama kodunu ekleyin, kaçınmak için - | birden çok tabloya dış birleştirme hataları oluşturma (12.1.3). - | Materyal yöneticisini materyal türü için iç sorgulara geri koy - | arama ve değiştirilen FOB noktası diller için bir arama koduna dönüştürülür. - | 1.20 03 Mayıs 2020 Douglas Volz, aynı CCID'ye sahip birden fazla mta satırına sahip olabilir, miktar - | ve muhasebe satırı türü. Yanlış toplamayı önlemek için, - | satır sayısını saymanız ve ardından - | toplam miktar toplamı. Ancak, Standart Maliyet Güncellemeleri için, - | ayarlanan miktarı kullanın. - | 1.21 14 Mayıs 2020 Douglas Volz UOM_Code, mtl_units_of_measure_vl için çoklu dil tablosu kullanın - | 1.22 17 Mayıs 2020 Douglas Volz İç sorgu grubunu kaldır, gerekli değil. - + =============================================== ============================ + * / US CAC Material Account Summary Report to get the Material accounting distributions, in summary, for each item, organization and subinventory. 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. With parameters to also limit the report size. Use Show Subinventories to display or not display the subinventory information. Use Show Projects to display or not display the project number and name 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". Also note this report version shows the latest item status and make buy codes, even if you run the report for prior accounting periods. Note: this report has identical code and logic as the CAC ICP PII Material Account Summary report, however, 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 number and name. Enter Yes or No, use to limit the report size (mandatory). Show Subinventories: display the subinventory code and description. Enter Yes or No, use to limit the report size (mandatory). Show WIP: display the WIP job or flow schedule information (WIP class, class type, WIP job, description, assembly number and assembly description). 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). 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- 2024 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.27 07 Oct 2022 Douglas Volz Correction for period name joins for interorg transactions. -- | 1.28 16 Oct 2022 Douglas Volz Correction for quantity calculations and PII logic. -- | 1.30 13 Jun 2024 Douglas Volz Removed tabs, reinstalled parameters, including org access restrictions. -- +=============================================================================+*/ ZHS CAC Material Account Summary 报表获取每个项目、机构、子库存的材料会计分录与金额。 包括机构间调拨的发货人和发货人信息。 该报告包括所有物料交易，但为了使报告更小，它不显示WIP作业信息，如WIP会计类别、类别说明、装配号、装配说明或作业单号。 根据需要，使用 "显示子库存 "参数来缩小该报告的大小。 如果选择 "是"，则得到子库存代码，如果选择 "否"，则只得到库存的会计行类型（库存估价），从而大大缩小了该报告的大小。 /* +=============================================================================+ -- 版权所有 2009-20道格拉斯-沃尔兹咨询公司。 | -- 允许使用该代码，但前提是原作者必须是｜。 -- 认可。 不包含任何明示或暗示的保证。 -- 允许。 | -- +=============================================================================+ -- | -- 原作者：Douglas Volz ()Douglas Volz (doug@volzconsulting.com) -- | -- 程序名称：xxx_mtl_dist_xla_sum_rept.sql。 -- | -- 参数。 -- p_trx_date_from -- 与PII相关的交易的起始交易日期，必须填写。 -- -- P_trx_date_to -- -- 与PII相关的交易结束日期，必须填写。 -- p_category_set1 -- 要报告的第一个项目类别，通常是p_category_set1。 -- 成本或产品系列类别集 -- p_category_set2 -- 要报告的第二个项目类别，通常是p_category_set2。 -- 库存类别集 -- -- p_org_code -- -- 您希望报告的具体清单组织（可选）。 -- | p_operating_unit -- 您希望报告的操作单位，所有单位留空。 -- 操作单元(可选) -- p_ledger -- 你想报告的总账，所有的总账留空。 -- 账目（可选） -- p_show_subinv -- 显示子库存代码或不显示子库存代码。 -- 输入'Y'或'N'值。 必须输入。 用于限制报告的大小。 -- | -- 说明 -- 报告获取每个项目、组织和子库存的材料会计分录。 -- 含有数量。 包括器官间的发运人和收运人信息。 -- 转让。 -- | -- | ======= =========== ============== ========================================= | 1.0 2009年11月6日 道格拉斯-沃尔兹初始编码。 -- | 1.19 2020年4月22日 Douglas Volz 将项目类型查询代码放入内部查询中，以避免出现问题。 -- 为多个表创建外部连接错误（12.1.3）。 -- 将项目主表放回项目类型的内部查询中。 -- 1.20 2020年5月3日 Douglas Volz 可以拥有多条相同CCID、数量的MTA行。 -- 和会计行类型。 为了避免错误地进行求和。 -- 需要计算行数，然后除以 -- | 总量之和。 然而，对于标准成本更新： -- 使用调整后的数量。 -- 1.21 2020年5月14日 Douglas Volz 为UOM_Code, mtl_units_ofmeasure_vl使用多语言表。 -- 1.22 2020年5月17日 Douglas Volz 删除内部查询组，不需要。 -- +=============================================================================+*/ Enginatics R12 only &category_columns &group_by_non_sla &group_by_pii &group_by_pii2 &group_by_project &group_by_project2 &group_by_sla &group_by_subinventory &group_by_subinventory2 &group_by_subledger &group_by_wip_job &group_by_wip_job2 &mtl_sla_table_joins &mtl_sla_tables &only_no_pii &only_non_wip_sources1 &only_non_wip_sources2 &order_by_project &order_by_subinventory &order_by_subledger &order_by_wip_job &p_show_non_sla &p_show_pii &p_show_pii2 &p_show_pii3 &p_show_project &p_show_project2 &p_show_sla &p_show_sla_ledger &p_show_subinventory &p_show_subinventory2 &p_show_wip_job &p_show_wip_job2 &p_sign_pii &pii_lookup_table &pii_table_joins &project_table_joins &project_tables &segment_columns &segment_columns_grp &subinv_table_joins &subinventory_table &subledger_join &subledger_tab 1=1 2=2 3=3 4=4 5=5 :p_trx_date_to 1 -50 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 -40 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 -30 &p_show_pii ,acct_dist.wip_pii_component_quantity PII_Zero_Component_Quantity, -- End revision for version 1.26 -- 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.28 -- PII Amount = Primary Quantity X PII Item Cost X PII_function round(sum(acct_dist.primary_quantity) * nvl(pii.pii_item_cost,0) * nvl(acct_dist.pii_function,0) -- If the Amount is zero PII is zero * decode(sum(acct_dist.mta_amount), 0, 0, 1) ,2) PII_Amount, -- Net Amount = Amount - PII Amount X PII_function sum(acct_dist.mta_amount) - round(sum(acct_dist.primary_quantity) * nvl(pii.pii_item_cost,0) * nvl(acct_dist.pii_function,0) -- If the Amount is zero PII is zero * decode(sum(acct_dist.mta_amount), 0, 0, 1) ,2) Net_Amount -- End revision for version 1.28 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 &group_by_pii pii.pii_cost_type, nvl(pii.pii_item_cost,0), nvl(acct_dist.pii_function,0), acct_dist.wip_pii_component_quantity, -- End revision for version 1.26 Y US Show Profit in Inventory 5 &group_by_pii2 acct_dist2.pii_function, case when (acct_dist2.accounting_line_type = 7 and acct_dist2.transaction_type_id in (17, 44, 90, 91)) then fl1.meaning else null end, Y US Show Profit in Inventory 6 &only_no_pii and 1 = 2 -- invalidate this select statement if PII is not selected N US Show Profit in Inventory 7 &p_show_pii2 -- 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_dist2.accounting_line_type = 7 and acct_dist2.transaction_type_id in (17, 44, 90, 91)) then fl1.meaning else null end wip_pii_component_quantity, acct_dist2.pii_function -- End revision for version 1.26 Y US Show Profit in Inventory 8 &p_show_pii3 -- ========================================================================== -- 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 9 &pii_lookup_table fnd_lookups fl1, Y US Show Profit in Inventory 10 &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 11 -20 :p_sign_pii Number 1 US Numeric Sign for PII 12 -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')) Y US Chart of Accounts 13 &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 14 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 15 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 16 30 &p_show_sla al.code_combination_id, al.ledger_id, 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 17 &group_by_non_sla acct_dist2.reference_account, N US Show SLA Accounting 18 &group_by_sla al.code_combination_id, al.ledger_id, acct_dist2.inventory_item_id, acct_dist2.transaction_source_type_id, acct_dist2.transaction_type_id, Y US Show SLA Accounting 19 &group_by_subledger nvl(gl_sub.short_name, gl_sub.name), Y US Show SLA Accounting 20 &mtl_sla_table_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 xdl.application_id = 707 and xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = acct_dist2.inv_sub_ledger_id Y US Show SLA Accounting 21 &mtl_sla_tables xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al, Y US Show SLA Accounting 22 &order_by_subledger nvl(gl_sub.short_name, gl_sub.name), Y US Show SLA Accounting 23 &p_show_non_sla acct_dist2.reference_account code_combination_id, N US Show SLA Accounting 24 &p_show_sla_ledger nvl(gl.short_name, gl.name) ledger, N US Show SLA Accounting 25 &p_show_sla_ledger nvl(gl_sub.short_name, gl_sub.name) ledger, Y US Show SLA Accounting 26 &subledger_join and gl_sub.ledger_id=acct_dist.ledger_id Y US Show SLA Accounting 27 &subledger_tab gl_ledgers gl_sub, Y US Show SLA Accounting 28 40 &p_show_subinventory msub.secondary_inventory_name Subinventory, msub.description Subinventory_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 Y Y US Show Subinventory 29 &group_by_subinventory msub.secondary_inventory_name, msub.description, Y US Show Subinventory 30 &group_by_subinventory2 acct_dist2.subinventory_code, Y US Show Subinventory 31 &order_by_subinventory ,msub.secondary_inventory_name Y US Show Subinventory 32 &p_show_subinventory2 acct_dist2.subinventory_code, Y US Show Subinventory 33 &subinv_table_joins and acct_dist.subinventory_code = msub.secondary_inventory_name (+) and acct_dist.organization_id = msub.organization_id (+) Y US Show Subinventory 34 &subinventory_table mtl_secondary_inventories msub, Y US Show Subinventory 35 50 &p_show_project pp.segment1 Project_Number, pp.name Project_Name, 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 36 &group_by_project pp.segment1, pp.name, Y US Show Projects 37 &group_by_project2 acct_dist2.project_id, Y US Show Projects 38 &order_by_project ,pp.segment1 ,pp.name Y US Show Projects 39 &p_show_project2 acct_dist2.project_id, Y US Show Projects 40 &project_table_joins and acct_dist.project_id = pp.project_id (+) Y US Show Projects 41 &project_tables pa_projects_all pp, Y US Show Projects 42 60 &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 43 &group_by_wip_job acct_dist.wip_class, acct_dist.class_type, acct_dist.wip_type, acct_dist.job, acct_dist.fg_item, acct_dist.fg_description, -- End revision for version 1.25 Y US Show WIP Job 44 &group_by_wip_job2 acct_dist2.wip_class, acct_dist2.class_type, acct_dist2.wip_type, acct_dist2.transaction_source_id, acct_dist2.job, acct_dist2.fg_item, acct_dist2.fg_description, Y US Show WIP Job 45 &only_non_wip_sources1 and mta.transaction_source_type_id <> 5 Y US Show WIP Job 46 &only_non_wip_sources2 and 1 = 2 -- invalidate this WIP select statement N US Show WIP Job 47 &order_by_wip_job ,acct_dist.wip_class ,acct_dist.wip_type ,acct_dist.job Y US Show WIP Job 48 &p_show_wip_job2 acct_dist2.wip_class, acct_dist2.class_type, acct_dist2.wip_type, acct_dist2.transaction_source_id, acct_dist2.job, acct_dist2.fg_item, acct_dist2.fg_description, Y US Show WIP Job 49 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=11)) US Category Set 1 50 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 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 51 90 &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 52 100 4=4 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 Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Item Number 53 110 3=3 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 54 120 1=1 haou2.name = :p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 55 130 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code Item Number :$flex$.organization_code Organization Code PII Sub-Element Pivot by Org Y MFG 25 ACCOUNTING_LINE_TYPE 38 AMOUNT -27.0037 ASSEMBLY_DESCRIPTION -27.0036 ASSEMBLY_NUMBER 12 Account -27.0033 CLASS_TYPE 32 CURRENCY_CODE 23 Class 10 Company 11 Department 6 FOB_POINT 4 FROM_OU 22 Family 16 ITEM_DESCRIPTION 15 ITEM_NUMBER 18 ITEM_STATUS 17 ITEM_TYPE 24 Inv.Items Description 1 LEDGER 19 MAKE_BUY_CODE 33 MATERIAL_AMOUNT 34 MATERIAL_OVERHEAD_AMOUNT -36.0041 NET_AMOUNT 2 OPERATING_UNIT 3 ORG_CODE 36 OUTSIDE_PROCESSING_AMOUNT 37 OVERHEAD_AMOUNT 9 PERIOD_NAME -36.004 PII_AMOUNT -36.0038 PII_COST_TYPE -36.0039 PII_ITEM_COST -36.0037 PII_ZERO_COMPONENT_QUANTITY -27.0031 PROJECT_NAME -27.003 PROJECT_NUMBER 14 Product 20 Product Cat 21 Product Description 31 QUANTITY 35 RESOURCE_AMOUNT 7 SHIP_FROM_ORG 8 SHIP_TO_ORG 28 SUBINVENTORY 29 SUBINVENTORY_DESCRIPTION 13 Sub-Account 5 TO_OU 27 TRANSACTION_SOURCE 26 TRANSACTION_TYPE 30 UOM_CODE -27.0032 WIP_CLASS -27.0035 WIP_JOB -38.0039 WIP_PII_COMPONENT_QUANTITY -27.0034 WIP_TYPE ACCOUNTING_LINE_TYPE ROW 4 AMOUNT VALUE 7 SUM LEDGER ROW 1 MATERIAL_AMOUNT VALUE 2 SUM MATERIAL_OVERHEAD_AMOUNT VALUE 3 SUM NET_AMOUNT VALUE 9 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 OUTSIDE_PROCESSING_AMOUNT VALUE 5 SUM OVERHEAD_AMOUNT VALUE 6 SUM PII_AMOUNT VALUE 8 SUM QUANTITY VALUE 1 SUM RESOURCE_AMOUNT VALUE 4 SUM S Site Pivot by Org