ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A486BD05719B2A82E053BB6B6358FC8E CST Cost Type (No ICP PII) Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') order by cct.cost_type US All cost types except ICP (intercompany profit) or PII (profit in inventory). 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 与所有现行清单组织有关的所有分类账 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 所有现行清单组织代码，不包括主组织 A0462A72D570FBCFE053BB6B635826CD select nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, cct.cost_type Cost_Type, bd.department_code Department_Code, bro.resource_code Overhead, bro.unit_of_measure Overhead_UOM, cdo.rate_or_amount Overhead_Rate, ml1.meaning Overhead_Basis, brr.resource_code Resource_Code, brr.unit_of_measure Resource_UOM, crc.resource_rate Resource_Rate, -- Revision for version 1.5 ml2.meaning Allow_Costs, &segment_columns cro.last_update_date Last_Rate_Update_Date from cst_department_overheads cdo, cst_resource_overheads cro, bom_departments bd, -- Revision for version 1.1 bom_department_resources bdr, -- Revision for version 1.5 -- cst_resource_costs crc, -- Possible to have an time-basis overhead -- associated to an uncosted resource which -- is not in cst_resource_costs. Avoiding -- outer join issue with Release 12.1.3. (select crc2.cost_type_id, crc2.resource_id, crc2.resource_rate from cst_resource_costs crc2, mtl_parameters mp, cst_cost_types cct, cst_cost_types cct2 -- Avg Rates Cost Type where cct.cost_type_id = crc2.cost_type_id (+) and cct2.cost_type_id = nvl(mp.avg_rates_cost_type_id, mp.primary_cost_method) and mp.organization_id = crc2.organization_id and 2=2 -- p_org_code, p_cost_type ) crc, -- End revision for version 1.5 bom_resources bro, bom_resources brr, cst_cost_types cct, -- Revision for version 1.5 cst_cost_types cct2, -- Avg Rates Cost Type mtl_parameters mp, mfg_lookups ml1, -- Basis -- Revision for version 1.5 mfg_lookups ml2, -- allow_costs gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl where brr.resource_id = cro.resource_id and cct.cost_type_id = cro.cost_type_id -- Revision for version 1.5 and cct2.cost_type_id = nvl(mp.avg_rates_cost_type_id, mp.primary_cost_method) and cro.overhead_id = cdo.overhead_id and bro.resource_id = cdo.overhead_id and cct.cost_type_id = cdo.cost_type_id and cdo.basis_type in (3, 4) and cdo.department_id = bd.department_id -- Revision for version 1.1 and bd.department_id = bdr.department_id and bdr.resource_id = brr.resource_id -- End revision for version 1.1 -- Revision for version 1.5 and brr.resource_id = crc.resource_id (+) -- and cct.cost_type_id = crc.cost_type_id -- End revision for version 1.5 and mp.organization_id = brr.organization_id and brr.organization_id = bro.organization_id and ml1.lookup_type = 'CST_BASIS_SHORT' and ml1.lookup_code = cdo.basis_type -- Revision for version 1.5 and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = brr.allow_costs_flag -- End revision for version 1.5 and bro.absorption_account = gcc.code_combination_id (+) -- =========================================== -- Organization joins to the HR org model -- =========================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id -- Revision for version 1.5 and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 1=1 -- p_operating_unit, p_ledger and 2=2 -- p_org_code, p_cost_type -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Order by Ledger, Operating Unit, Org Code, Cost Type, Overhead Code and Resource Code order by nvl(gl.short_name, gl.name), -- Ledger haou.name, -- Operating_Unit mp.organization_code, -- Org_Code cct.cost_type, -- Cost_Type bro.resource_code, -- Overhead brr.resource_code -- Resource Y US CAC Department Overhead Setup Report to show the departments and the overhead codes assigned to each department. In addition, this report displays the corresponding overhead and resource rates. Note: if a resource does not have a cost, the Resource Rate column has a blank or empty value. Parameters: =========== Cost Type: enter the cost type you wish to use to report. If left blank, depending on your Costing Method, it defaults to your AvgRates or Frozen Cost Type (optional). 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). 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 2016 - 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. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: xxx_dept_ovhd_setup.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 18 Oct 2016 Douglas Volz Initial Coding -- | 1.1 07 Nov 2016 Douglas Volz Added Department / Resource relationships -- | 1.2 17 Jul 2018 Douglas Volz Made Cost Type parameter optional -- | 1.3 12 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters -- | and change to gl.short_name. Added Last -- | rate update column to report. -- | 1.4 27 Apr 2020 Douglas Volz Changed to multi-language views for the -- | inventory orgs and operating units. -- | 1.5 22 Jan 2024 Douglas Volz Add select statement for cst_resource_costs, overheads -- | were not reported if the resource cost was missing. -- | Added resource "Allow Costs" column. Removed -- | tabs and added inventory access controls. -- +=============================================================================+*/ Enginatics &segment_columns 1=1 2=2 1 -100 &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 2 10 2=2 cct.cost_type = :p_cost_type LOV CST Cost Type (No ICP PII) A486BD05719B2A82E053BB6B6358FC8E Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') order by cct.cost_type select cct.cost_type value, cct.description from cst_cost_types cct, mtl_parameters mp where nvl(cct.disable_date,sysdate + 1) > sysdate and cct.organization_id is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id) and (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or (fnd_profile.value('MFG_ORGANIZATION_ID') is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)) ) and rownum < 2 group by cct.cost_type, cct.description order by cct.cost_type US Cost Type 3 20 2=2 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 4 30 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 5 40 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 9734691EC5D1188812A8C6E3A2BE7D22 Count by Org by Department Count by Ledger, Operating Unit, Org Code, Cost Type and Department MFG 13 ALLOW_COSTS 16 Account 4 COST_TYPE 14 Company 5 DEPARTMENT_CODE 15 Department 19 LAST_RATE_UPDATE_DATE 1 LEDGER 2 OPERATING_UNIT 3 ORG_CODE 6 OVERHEAD 9 OVERHEAD_BASIS 8 OVERHEAD_RATE 7 OVERHEAD_UOM 18 Product 10 RESOURCE_CODE 12 RESOURCE_RATE 11 RESOURCE_UOM 17 Sub-Account COST_TYPE ROW 4 DEPARTMENT_CODE ROW 5 LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 OVERHEAD VALUE 1 COUNT S Site 9734691EC5D1188812A8C6E3A2BE7D22