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). 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 所有现行清单组织代码，不包括主组织 A3BFC166CC567F77E053BB6B6358CAD3 with crc as -- Revision for version 1.6 -- 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 cct.cost_type = nvl(:p_cost_type, cct2.cost_type) -- p_cost_type 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.6 ----------------main query starts here-------------- select flv.meaning Status, -- Set Up nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, cct.cost_type Cost_Type, brr.resource_code Resource_Code, brr.unit_of_measure Resource_UOM, -- Revision for version 1.1 cce.cost_element Cost_Element, -- Revision for version 1.7 gl.currency_code Currency_Code, crc.resource_rate Resource_Rate, -- Revision for version 1.7 ml3.meaning Resource_Type, ml4.meaning Charge_Type, ml1.meaning Resource_Basis, -- End revision for version 1.7 -- Revision for version 1.6 ml2.meaning Allow_Costs, bd.department_code Department, bro.resource_code Overhead_Code, bro.unit_of_measure Overhead_UOM from cst_resource_overheads cro, bom_department_resources bdr, bom_departments bd, -- Revision for version 1.6 -- cst_resource_costs crc, crc, -- End revision for version 1.6 bom_resources bro, -- Overhead Sub-Elements bom_resources brr, -- Resource and OSP Sub-Elements cst_cost_types cct, -- Revision for version 1.4 cst_cost_types cct2, -- Avg Rates Cost Type cst_cost_elements cce, mtl_parameters mp, mfg_lookups ml1, -- Basis -- Revision for version 1.6 mfg_lookups ml2, -- Allow Costs -- Revision for version 1.7 mfg_lookups ml3, -- Resource Type mfg_lookups ml4, -- Charge Type -- End revision for version 1.7 hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, -- Revision for version 1.5 fnd_lookup_values flv where brr.resource_id = cro.resource_id and bro.resource_id = cro.overhead_id and brr.resource_id = bdr.resource_id and bdr.department_id = bd.department_id and brr.resource_id = crc.resource_id (+) and cct.cost_type_id = cro.cost_type_id -- Revision for version 1.6 -- and cct.cost_type_id = crc.cost_type_id -- Revision for version 1.4 and cct2.cost_type_id = nvl(mp.avg_rates_cost_type_id, mp.primary_cost_method) and cct.cost_type = nvl(:p_cost_type, cct2.cost_type) -- p_cost_type -- End revision for version 1.4 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 = brr.default_basis_type -- Revision for version 1.6 and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = brr.allow_costs_flag -- End revision for version 1.6 -- Revision for version 1.7 and ml3.lookup_type = 'BOM_RESOURCE_TYPE' and ml3.lookup_code = brr.resource_type and ml4.lookup_type = 'BOM_AUTOCHARGE_TYPE' and ml4.lookup_code = brr.autocharge_type -- End revision for version 1.7 -- Revision for version 1.5 and flv.lookup_type = 'CHECK RANGE STATUS' and flv.lookup_code = 'SET UP' and flv.language = userenv('lang') -- Revision for version 1.1 and cce.cost_element_id = brr.cost_element_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.6 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 -- Revision for version 1.1 -- Add check for resources which are not associated with overheads union all select fcl.meaning Status, -- Missing nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, cct.cost_type Cost_Type, brr.resource_code Resource_Code, brr.unit_of_measure Resource_UOM, -- Revision for version 1.1 cce.cost_element Cost_Element, -- Revision for version 1.7 gl.currency_code Currency_Code, crc.resource_rate Resource_Rate, -- Revision for version 1.7 ml3.meaning Resource_Type, ml4.meaning Charge_Type, ml1.meaning Resource_Basis, -- End revision for version 1.7 -- Revision for version 1.6 ml2.meaning Allow_Costs, bd.department_code Department, fcl.meaning Overhead_Code, -- Missing null Overhead_UOM from bom_department_resources bdr, bom_departments bd, -- Revision for version 1.6 -- cst_resource_costs crc, crc, -- End revision for version 1.6 bom_resources brr, -- Resource and OSP Sub-Elements cst_cost_types cct, -- Revision for version 1.4 cst_cost_types cct2, -- Revision for version 1.1 cst_cost_elements cce, mtl_parameters mp, mfg_lookups ml1, -- Basis -- Revision for version 1.6 mfg_lookups ml2, -- Allow Costs -- Revision for version 1.7 mfg_lookups ml3, -- Resource Type mfg_lookups ml4, -- Charge Type -- End revision for version 1.7 hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, fnd_common_lookups fcl where brr.resource_id = bdr.resource_id and bdr.department_id = bd.department_id and brr.resource_id = crc.resource_id (+) -- Revision for version 1.6 -- and cct.cost_type_id = crc.cost_type_id -- Revision for version 1.4 and cct2.cost_type_id = nvl(mp.avg_rates_cost_type_id, mp.primary_cost_method) and cct.cost_type = nvl(:p_cost_type, cct2.cost_type) -- p_cost_type -- End revision for version 1.4 and mp.organization_id = brr.organization_id and ml1.lookup_type = 'CST_BASIS_SHORT' and ml1.lookup_code = brr.default_basis_type -- Revision for version 1.6 and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = brr.allow_costs_flag -- End revision for version 1.6 -- Revision for version 1.7 and ml3.lookup_type = 'BOM_RESOURCE_TYPE' and ml3.lookup_code = brr.resource_type and ml4.lookup_type = 'BOM_AUTOCHARGE_TYPE' and ml4.lookup_code = brr.autocharge_type -- End revision for version 1.7 -- Revision for version 1.5 and fcl.lookup_type = 'NL_IZA_REJECT_REASON' and fcl.lookup_code = 'MISSING' -- Revision for version 1.1 and cce.cost_element_id = brr.cost_element_id and brr.resource_id not in (select cro.resource_id from cst_resource_overheads cro where cro.resource_id = brr.resource_id and cro.organization_id = brr.organization_id and cct.cost_type_id = cro.cost_type_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.6 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 -- Order by Status, Ledger, Operating_Unit, Org_Code, Cost_Type, Resource_Code, Res Basis, Department and Overhead Code order by 1,2,3,4,5,6,8,14,15 Replaced tab characters with spaces Y US CAC Resources Associated with Overheads Setup Report to show which resources are associated with which overheads and which resources are associated with which departments. And find any resources which do not have any overhead associations. If there are no overhead associations the first report column will say "Missing". If the resource/overhead association exists, the first column of the report will say "Set Up". Note: if a resource does not have a cost, the Resource Rate column has a blank or empty value. Parameters: =========== Resource/Overhead Cost Type: enter the cost type you wish to report for your resources and overheads. 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_res_ovhd_setup.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 11 Nov 2016 Douglas Volz Initial Coding -- | 1.1 16 Jun 2017 Douglas Volz Add check for resources which are not -- | associated with overheads and added the -- | resource cost element. -- | 1.2 17 Jul 2018 Douglas Volz Made Cost Type parameter optional -- | 1.3 16 Jan 2020 Douglas Volz Added org code and operating unit parameters. -- | 1.4 20 Apr 2020 Douglas Volz Make Cost Type default work for all cost methods -- | 1.5 09 Jul 2022 Douglas Volz Changes for multi-language lookup values. -- | 1.6 22 Jan 2024 Douglas Volz Add with 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. -- | 1.7 31 Jan 2024 Douglas Volz Add Currency Code, Resource Type and Charge Type columns. -- +=============================================================================+*/ Enginatics 1=1 2=2 :p_cost_type 1 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 Resource/Overhead Cost Type 2 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 3 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 4 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$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code 4742FEED4458541D5247AC8495BD494C Count by Org by Resource Count by Status, Ledger, Operating Unit, Org Code and Resource MFG 14 ALLOW_COSTS 12 CHARGE_TYPE 8 COST_ELEMENT 5 COST_TYPE 9 CURRENCY_CODE 15 DEPARTMENT 2 LEDGER 3 OPERATING_UNIT 4 ORG_CODE 16 OVERHEAD_CODE 17 OVERHEAD_UOM 13 RESOURCE_BASIS 6 RESOURCE_CODE 10 RESOURCE_RATE 11 RESOURCE_TYPE 7 RESOURCE_UOM 1 STATUS COST_TYPE ROW 5 LEDGER ROW 2 OPERATING_UNIT ROW 3 ORG_CODE ROW 4 RESOURCE_CODE VALUE 1 COUNT STATUS ROW 1 S Site 4742FEED4458541D5247AC8495BD494C