ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 92C63F4C20A21E85E053BB6B63585CC8 CST Cost Type 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 or cct.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) ) order by cct.cost_type US 91D3167A335B1204E053BB6B63585EE3 CST Cost Type (No AvgRates) Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type desc US E234C6573B214FDAE05369FB090533D7 CST PII Sub-Elements Y select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code US Lookup type for profit in inventory resources 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 8E2FF36EDEF179D2E0530100007F1FF2 INV Item Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Inventory items, restricted to the accessible inventory organizations 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 所有现行清单组织代码，不包括主组织 8E2FF36EDF2279D2E0530100007F1FF2 INV Period Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name AR فترات دفتر الأستاذ العام السابقة للمخزون (تعتمد على دفتر الأستاذ و / أو معلمة رمز المؤسسة) للمؤسسات غير الرئيسية D Vergangene Bestands-GL-Perioden (abhängig von Ledger- und/oder Organisationscode-Parameter) für Nicht-Master-Organisationen E Períodos GL de inventario pasados (dependientes de los parámetros del libro mayor y/o del código de organización) para organizaciones no maestras F Périodes passées de GL d'inventaire (dépendant du grand livre et/ou du code d'organisation) pour les organisations non maîtresses I Periodi GL di inventario passati (dipendente dal parametro del ledger e/o del codice dell'organizzazione) per organizzazioni non master JA 非マスター組織の過去のインベントリGL期間（元帳および/または組織コードパラメータに依存する）。 KO 비 마스터 조직에 대한 과거 재고 GL 기간 (원장 및 / 또는 조직 코드 매개 변수에 따라 다름) PTB Períodos GL de inventário anteriores (ledger e/ou código de organização dependente do parâmetro) para organizações não-mestras RU Прошлые инвентаризационные GL-периоды (зависящие от бухгалтерской книги и/или параметра организационного кода) для неосновных организаций S Tidigare lager GL-perioder (storbok och / eller organisationskodparameter beroende) för icke-huvudorganisationer TR Ana olmayan kuruluşlar için geçmiş envanter GL dönemleri (genel muhasebe ve / veya kuruluş kodu parametresine bağlı) US Past inventory GL periods (ledger and/or organization code parameter dependent) for non master organizations ZHS 非主组织的过去库存GL期(分类账和/或组织代码参数而定) E7CCB27F39561272E0530100007F62B4 WIP Class Code (org dependent) Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US 8E2FF36EDEC379D2E0530100007F1FF2 WIP Job Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US 8E2FF36EDE9579D2E0530100007F1FF2 WIP Job Status Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US 7F2063CEC1FFCDEFE053BB6B63584F37Y select gl.name Ledger, haouv2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name, &company_segment &segment_columns wac.class_code WIP_Class, xxen_util.meaning(wac.class_type,'WIP_CLASS_TYPE',700) Class_Type, we.wip_entity_name WIP_Job, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, wdj.date_completed, wdj.date_closed, wdj.start_quantity, wdj.quantity_completed Assembly_Quantity_Completed, wdj.quantity_scrapped assembly_Quantity_Scrapped, wdj.quantity_completed + wdj.quantity_scrapped Total_assembly_Quantity, msiv.concatenated_segments Assembly_Number, msiv.description Assembly_Description, -- Revision for version 1.14 &category_columns msiv2.concatenated_segments Component_Number, msiv2.description Component_Description, gl.currency_code Currency_Code, sum(cic.item_cost) Gross_Item_Cost, nvl(cicd.item_cost,0) PII_Item_Cost, -- Revision for version 1.13 -- msi2.primary_uom_code UOM_Code, muomv.uom_code UOM_Code, -- +===================================================== -- need to include component yield in the icp calculation -- +===================================================== -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored sum((decode(wro.basis_type, null, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1), 1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) ) ) ) Quantity_Per_Assembly, -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored sum((decode(wro.basis_type, null, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)) ) ) ) Total_Required_Quantity, sum(nvl(wro.quantity_issued,0)) Quantity_Issued, -- need to include component yield in the calculation sum((nvl(wro.quantity_issued,0) - -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored decode(wro.basis_type, null, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)) ) ) ) Quantity_Left_in_WIP, sum((nvl(wro.quantity_issued,0) - -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored decode(wro.basis_type, null, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)) ) ) ) * sum(nvl(cic.item_cost,0)) Gross_Material_Usage_Var, sum((nvl(wro.quantity_issued,0) - -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored decode(wro.basis_type, null, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)) ) ) ) * nvl(cicd.item_cost,0) PII_in_WIP, sum((nvl(wro.quantity_issued,0) - -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored decode(wro.basis_type, null, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) * (nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0)) ) ) ) * (sum(nvl(cic.item_cost,0)) + nvl(cicd.item_cost,0)) Net_Material_Usage_Var from org_acct_periods oap, wip_entities we, wip_discrete_jobs wdj, wip_accounting_classes wac, wip_requirement_operations wro, mtl_parameters mp, mtl_system_items_vl msiv, mtl_system_items_vl msiv2, cst_item_costs cic, -- Revision for version 1.13 mtl_units_of_measure_vl muomv, cst_cost_types cct, gl_code_combinations gcc1, -- wip class accounts gl_code_combinations gcc2, -- product group info from cogs account hr_organization_information hoi, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv2, gl_ledgers gl, (select cicd.inventory_item_id, cicd.organization_id, sum(cicd.item_cost) item_cost from cst_item_cost_details cicd, bom_resources br, cst_cost_types cct where cicd.resource_id=br.resource_id and br.resource_code= :p_pii_sub_element and cicd.cost_type_id=cct.cost_type_id and cct.cost_type=:p_pii_cost_type group by cicd.inventory_item_id, cicd.organization_id ) cicd where 1=1 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 oap.organization_id = we.organization_id and wdj.date_closed >= oap.period_start_date and wdj.date_closed < oap.schedule_close_date+1 and we.wip_entity_id = wdj.wip_entity_id and wdj.class_code = wac.class_code and wdj.organization_id = wac.organization_id and wdj.wip_entity_id = wro.wip_entity_id -- ================================================================= -- mtl parameter, item master and period close snapshot joins -- ================================================================= and mp.organization_id = msiv.organization_id and msiv.organization_id = we.organization_id and msiv.inventory_item_id = we.primary_item_id -- fg assembly item and msiv.organization_id = msiv2.organization_id and msiv2.inventory_item_id = wro.inventory_item_id -- component item and msiv2.organization_id = cic.organization_id and msiv2.inventory_item_id = cic.inventory_item_id -- component item -- Revision for version 1.13 and msiv2.primary_uom_code = muomv.uom_code and cct.cost_type = :p_cost_type -- If you do not specify a cost type, gets the frozen or average cost type based on your costing method and cic.cost_type_id = nvl(cct.cost_type_id, mp.primary_cost_method) -- End of revision for version 1.13 and wac.material_account = gcc1.code_combination_id and msiv.cost_of_sales_account = gcc2.code_combination_id and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haouv.organization_id -- this gets the organization name and haouv2.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 msiv2.inventory_item_id = cicd.inventory_item_id(+) and msiv2.organization_id = cicd.organization_id(+) group by gl.name, haouv2.name, mp.organization_code, oap.period_name, &group_by_company_seg &group_by_segment_columns wac.class_code, we.wip_entity_name, xxen_util.meaning(wac.class_type,'WIP_CLASS_TYPE',700), xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700), wdj.date_completed, wdj.date_closed, wdj.start_quantity, wdj.quantity_completed, wdj.quantity_scrapped, wdj.net_quantity, msiv.concatenated_segments, msiv.description, -- Revision for version 1.14 -- For Category columns msiv.inventory_item_id, msiv.organization_id, -- End revision for version 1.14 msiv2.concatenated_segments, msiv2.description, gl.currency_code, -- Revision for version 1.13 -- msi2.primary_uom_code, muomv.uom_code, msiv2.inventory_item_id, msiv2.organization_id, cicd.item_cost order by 1,2,3,4,5,6,7,9,21 -- | 1.14 23 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and inventory org access controls. Add in category parameters. US CAC WIP Material Relief Report to show WIP material variances on closed jobs for discrete manufacturing, in summary by inventory organization, with WIP class, job status, name and other details. Including any profit in inventory or PII amounts. But unlike the more recent CAC WIP Manufacturing Variance and CAC WIP Material Usage Variance reports, this report uses the latest material issue quantities as stored on the WIP job definition, even if you run it for a prior period. It does not rollback the component issue quantities for a prior accounting period. Parameters: ========== Period Name: the accounting period you wish to report (mandatory). Cost Type: defaults to your Costing Method. If blank the report uses your Costing Method Cost Type (mandatory). PII Cost Type: enter the cost type for your profit in inventory item costs to report (optional). PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP (optional). Category Set 1: any item category you wish (optional). Category Set 2: any item category you wish (optional). Class Code: specific type of WIP class to report (optional). Job Status: specific WIP job status (optional). WIP Job: specific WIP job (optional). Assembly Number: specific assembly number you wish to report (optional) Component Number: specific component item you wish to report (optional) Show Phantom Components: show the material usage variances for phantom components (optional). Organization Code: any inventory organization, defaults to your session's inventory organization (optional). Operating Unit: specific operating unit (optional). Ledger: specific ledger (optional). /* +=============================================================================+ -- | Copyright 2009-24 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is acknowledged. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: xxx_wip_relief_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 11 Jan 2010 Douglas Volz Initial Coding based on XXX_ICP_WIP_COMPONENT_VAL_REPT.sql, -- | with this report analyzing the WIP variances on -- | closed jobs. Added org_acct_periods to limit to -- | jobs closed within the accounting period. -- | 1.9 26 Sep 2010 Douglas Volz Added Component Item Number to report sort. -- | 1.10 09 Feb 2012 Douglas Volz Included component yield in required quantities; -- | decided to not exclude WIP expense jobs as they -- | could have components with ICP issued to the job -- | and the cost accountants would need the visibility. -- | 1.11 23 Feb 2012 Douglas Volz Added component UOM code, to be consistent with -- | other reports. -- | 1.12 07 Oct 2020 Andy Haack Modify for Blitz Report with Blitz lookup functions -- | (xxen_util) and re-format sql code. -- | 1.13 11 Jul 2022 Douglas Volz Multi-language changes; added back PII parameters. Changed -- | wro.component_yield_factor to nvl(wro.component_yield_factor,1). -- | 1.14 23 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and inventory org access controls. -- | Added category set parameters. -- +=============================================================================+*/ Enginatics R12 only &category_columns &company_segment &group_by_company_seg &group_by_segment_columns &segment_columns 1=1 :p_cost_type :p_pii_cost_type :p_pii_sub_element 1 10 1=1 oap.period_name=:p_period_name LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name select distinct max(gps.period_name) keep (dense_rank last order by gps.effective_period_num) over () dafault_period from gl_period_statuses gps where gps.start_date<=sysdate and gps.set_of_books_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 Y US Period Name 2 20 :p_cost_type LOV CST Cost Type 92C63F4C20A21E85E053BB6B63585CC8 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 or cct.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) ) order by cct.cost_type select cct.cost_type from cst_cost_types cct, mtl_parameters mp where cct.cost_type_id = mp.primary_cost_method and mp.organization_id = nvl(fnd_profile.value('MFG_ORGANIZATION_ID'),mp.organization_id) and rownum < 2 Y US Cost Type 3 30 :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 US PII Cost Type 4 40 :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 US PII Sub-Element 5 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 6 60 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 7 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 8 80 1=1 wdj.class_code=:p_class_code LOV WIP Class Code (org dependent) E7CCB27F39561272E0530100007F62B4 Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US Class Code 9 90 1=1 wdj.status_type=xxen_util.lookup_code(:job_status,'WIP_JOB_STATUS',700) LOV WIP Job Status 8E2FF36EDE9579D2E0530100007F1FF2 Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US Job Status 10 100 1=1 crp.source_transaction_id in ( select mr.transaction_id from wip_entities we, mrp_recommendations mr where we.wip_entity_name=:job_name and we.wip_entity_id=mr.disposition_id and mr.order_type in (3,7,14,15,27,28) ) LOV WIP Job 8E2FF36EDEC379D2E0530100007F1FF2 Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US WIP Job 11 110 1=1 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 Assembly Number 12 120 1=1 msiv2.concatenated_segments = :p_item_number LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Component Number 13 130 1=1 wro.wip_supply_type<>6 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 N N US Show Phantom Components 14 140 1=1 mp.organization_code=:p_organization_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 15 150 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 16 160 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 select gl.name from gl_ledgers gl where gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where hou.organization_id=(select mgoat.organization_id from mo_glob_org_access_tmp mgoat where rownum=1)) US Ledger 17 &company_segment select distinct 'gcc2.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)|| 'xxen_util.segment_description(gcc2.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc2.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' 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 in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y') and (fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') US Ledger 18 &group_by_company_seg select distinct 'gcc2.'||lower(fifsv.application_column_name)||','||chr(10)|| 'xxen_util.segment_description(gcc2.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc2.chart_of_accounts_id)'||',' 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 in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y') and (fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') US Ledger 19 &group_by_segment_columns select 'gcc1.'||lower(fifsv.application_column_name)||','||chr(10)|| 'xxen_util.segment_description(gcc1.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc1.chart_of_accounts_id),' 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 gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' and rownum=1) and (fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) not in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') order by fifsv.segment_num US Ledger 20 &segment_columns select 'gcc1.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)|| 'xxen_util.segment_description(gcc1.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc1.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' 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 gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y' and rownum=1) and (fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) not in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') order by fifsv.segment_num US Ledger :$flex$.ledger Ledger Organization Code :$flex$.ledger Ledger Period Name :$flex$.ledger Ledger WIP Job :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Assembly Number :$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 Class Code :$flex$.organization_code Organization Code Component Number :$flex$.organization_code Organization Code PII Sub-Element :$flex$.organization_code Organization Code Period Name :$flex$.organization_code Organization Code WIP Job 65269219627113F89AF07633F68D99A7 Pivot by Org by WIP Class Y MFG 26 ASSEMBLY_DESCRIPTION 25 ASSEMBLY_NUMBER 22 ASSEMBLY_QUANTITY_COMPLETED 23 ASSEMBLY_QUANTITY_SCRAPPED 9 Account 10 Account desc 16 CLASS_TYPE 33 COMPONENT_DESCRIPTION 32 COMPONENT_NUMBER 34 CURRENCY_CODE 30 Class 5 Company 6 Company desc 20 DATE_CLOSED 19 DATE_COMPLETED 7 Department 8 Department desc 29 Family 35 GROSS_ITEM_COST 42 GROSS_MATERIAL_USAGE_VAR 31 Inv.Items Description 18 JOB_STATUS 1 LEDGER 44 NET_MATERIAL_USAGE_VAR 2 OPERATING_UNIT 3 ORG_CODE 4 PERIOD_NAME 43 PII_IN_WIP 36 PII_ITEM_COST 13 Product 27 Product Cat 28 Product Description 14 Product desc 40 QUANTITY_ISSUED 41 QUANTITY_LEFT_IN_WIP 38 QUANTITY_PER_ASSEMBLY 21 START_QUANTITY 11 Sub-Account 12 Sub-Account desc 24 TOTAL_ASSEMBLY_QUANTITY 39 TOTAL_REQUIRED_QUANTITY 37 UOM_CODE 15 WIP_CLASS 17 WIP_JOB GROSS_MATERIAL_USAGE_VAR VALUE 1 SUM LEDGER ROW 1 NET_MATERIAL_USAGE_VAR VALUE 3 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 PERIOD_NAME ROW 4 PII_IN_WIP VALUE 2 SUM WIP_CLASS ROW 5 S Site 65269219627113F89AF07633F68D99A7