ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 184A903C40A16B3FE0630100007F2880 Cost Type (OPM Orgs Only) Y select x.value, x.description from ( select cmm.cost_mthd_code value, cmm.cost_mthd_desc description from cm_mthd_mst cmm where exists (select 'x' from gmf_calendar_assignments gca, org_organization_definitions ood, mtl_parameters mp where gca.legal_entity_id = ood.legal_entity and gca.cost_type_id = cmm.cost_type_id and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or 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 (:$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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id and mp.process_enabled_flag = 'Y' ) ) x group by x.value, x.description order by value US 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 与所有现行清单组织有关的所有分类账 1A573DC3BA400377E0630100007FE5A2 GME Batch Number Y select gbh.batch_no value, gl.description description from gme_batch_header gbh, mtl_parameters mp, org_organization_definitions ood, org_acct_periods oap, gem_lookups gl where gbh.organization_id=mp.organization_id and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and gl.lookup_type (+) = 'GME_BATCH_STATUS' and gl.lookup_code (+) = gbh.batch_status and oap.organization_id = mp.organization_id and (:$flex$.period_name is not null and oap.period_name = :$flex$.period_name) and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) US 2EC46130C328100BE06369FB0905137D GME Batch Number To Y select gbh.batch_no value, gl.description description from gme_batch_header gbh, mtl_parameters mp, org_organization_definitions ood, org_acct_periods oap, gem_lookups gl where gbh.organization_id=mp.organization_id and (gbh.batch_no >= :$flex$.batch_number_from or :$flex$.batch_number_from is null) and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and gl.lookup_type (+) = 'GME_BATCH_STATUS' and gl.lookup_code (+) = gbh.batch_status and oap.organization_id = mp.organization_id and (:$flex$.period_name is not null and oap.period_name = :$flex$.period_name) and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) US 2EC46130C329100BE06369FB0905137D GME Batch Status Y select gl1.meaning value, gl1.description description from gem_lookups gl1 where gl1.lookup_type = 'GME_BATCH_STATUS' US 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 所有现行清单组织代码，不包括主组织 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期(分类账和/或组织代码参数而定) 19508EAA42357240E0630100007FD049 OPM Calendar Code Y select ccht.calendar_code value, ccht.calendar_desc description from cm_cldr_hdr_tl ccht where ccht.language = userenv('lang') and exists (select 'x' from gmf_calendar_assignments gca, gmf_period_statuses gps, org_organization_definitions ood, mtl_parameters mp where gca.calendar_code = gps.calendar_code and gca.legal_entity_id = gps.legal_entity_id and gca.legal_entity_id = ood.legal_entity and gca.calendar_code = ccht.calendar_code and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or 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 (:$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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id ) order by value desc US OPM Calendar Code for OPM Cost Reports 19508EAA42367240E0630100007FD049 OPM Period Code Y select ccd.period_code value, ccd.period_desc description from cm_cldr_dtl ccd where exists (select 1 from gmf_period_statuses gps where gps.calendar_code = :$flex$.opm_calendar_code and gps.legal_entity_id in (select ood.legal_entity from org_organization_definitions ood where (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))) and gps.period_code = ccd.period_code and gps.calendar_code = ccd.calendar_code ) order by ccd.end_date desc US OPM Period Code for OPM Cost Reporting 2E888FE5926328ACE0630100007FCF1DY with item_cost as -- For the batch materials, get the item costs (-- If the cost type is null, get the item -- costs from the month-end item costs. select gps.legal_entity_id, gic.cost_type_id, cmm.cost_mthd_code cost_type, cmm.cost_mthd_desc cost_type_desc, mp.organization_code, mp.organization_id, gic.inventory_item_id, msiv.concatenated_segments, msiv.description item_description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, gps.calendar_code, gps.period_code, gps.period_id, -- To avoid rounding errors, use decimal precision 9 round(sum(nvl(gic.acctg_cost,0)),9) item_cost from gl_item_cst gic, cm_mthd_mst cmm, -- Cost Types gmf_period_statuses gps, gmf_fiscal_policies gfp, gmf_calendar_assignments gca, mtl_system_items_vl msiv, -- Use this logic in case of cost organizations (select mp.organization_id, mp.organization_code, to_number(hoi.org_information2) legal_entity_id, nvl(cwa.cost_organization_id, mp.organization_id) cost_organization_id from mtl_parameters mp, hr_organization_information hoi, -- Legal Entity Id cm_whse_asc cwa where 2=2 -- p_org_code and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and mp.organization_id = cwa.organization_id (+) ) mp where gic.cost_type_id = gfp.cost_type_id and cmm.cost_type_id = gic.cost_type_id and mp.organization_id = msiv.organization_id and mp.cost_organization_id = gic.organization_id and gic.inventory_item_id = msiv.inventory_item_id and gic.period_id = gps.period_id and gic.delete_mark = 0 -- Active and msiv.inventory_asset_flag = 'Y' and gps.legal_entity_id = gfp.legal_entity_id and gps.cost_type_id = gfp.cost_type_id and gps.cost_type_id = gca.cost_type_id and gps.legal_entity_id = gca.legal_entity_id and gps.calendar_code = gca.calendar_code -- After running the Cost Update in Final Mode the gic.calendar_code -- is set to a null value. -- and gps.calendar_code = gic.calendar_code and mp.legal_entity_id = gps.legal_entity_id and :p_cost_type is null -- p_cost_type -- The Inventory Accounting Period Name may be different from the OPM Period Code and 2=2 -- p_org_code and 4=4 -- p_item_number, p_calendar_code, p_period_code group by gps.legal_entity_id, gic.cost_type_id, cmm.cost_mthd_code, -- cost_type cmm.cost_mthd_desc, -- cost_type_description mp.organization_code, mp.organization_id, gic.inventory_item_id, msiv.concatenated_segments, msiv.description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, gps.calendar_code, gps.period_code, gps.period_id union all -- If the cost type is not null, get the item -- costs from entered cost type. select gps.legal_entity_id, cmm.cost_type_id, cmm.cost_mthd_code cost_type, cmm.cost_mthd_desc cost_type_description, mp.organization_code, mp.organization_id, ccd.inventory_item_id, msiv.concatenated_segments, msiv.description item_description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, gps.calendar_code, gps.period_code, gps.period_id, -- To avoid rounding errors, use decimal precision 9 round(sum(nvl(ccd.cmpnt_cost,0)),9) item_cost from cm_cmpt_dtl ccd, cm_cmpt_mst_b ccm, cm_mthd_mst cmm, -- Cost Types gmf_period_statuses gps, gmf_calendar_assignments gca, mtl_system_items_vl msiv, -- Use this logic in case of cost organizations (select mp.organization_id, mp.organization_code, to_number(hoi.org_information2) legal_entity_id, nvl(cwa.cost_organization_id, mp.organization_id) cost_organization_id from mtl_parameters mp, hr_organization_information hoi, -- Legal Entity Id cm_whse_asc cwa where 2=2 -- p_org_code and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and mp.organization_id = cwa.organization_id (+) ) mp where ccd.cost_cmpntcls_id = ccm.cost_cmpntcls_id and ccm.product_cost_ind = 1 -- Yes and ccd.cost_type_id = cmm.cost_type_id -- Use Period Id, not Calendar Code with ccd and ccd.period_id = gps.period_id and ccd.organization_id = mp.cost_organization_id and mp.organization_id = msiv.organization_id and ccd.inventory_item_id = msiv.inventory_item_id and ccd.delete_mark = 0 -- Active and msiv.inventory_asset_flag = 'Y' and gps.cost_type_id = ccd.cost_type_id and gps.cost_type_id = gca.cost_type_id and gps.legal_entity_id = gca.legal_entity_id and gps.legal_entity_id = mp.legal_entity_id and gps.calendar_code = gca.calendar_code and :p_cost_type is not null -- p_cost_type and 2=2 -- p_org_code and 3=3 -- p_cost_type and 4=4 -- p_item_number, p_calendar_code, p_period_code group by gps.legal_entity_id, cmm.cost_type_id, cmm.cost_mthd_code, -- cost_type cmm.cost_mthd_desc, -- cost_type_description mp.organization_code, mp.organization_id, ccd.inventory_item_id, msiv.concatenated_segments, msiv.description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, gps.calendar_code, gps.period_code, gps.period_id ) -- item_cost ----------------main query starts here-------------- select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, gps.calendar_code Calendar_Code, gps.period_code Period_Code, gbh.batch_no Batch_Number, gbh.plan_start_date Batch_Start_Date, gbh.plan_cmplt_date Planned_Completion_Date, gbh.actual_cmplt_date Actual_Completion_Date, gbh.batch_close_date Closed_Date, gl1.meaning Batch_Status, msiv.concatenated_segments Item_Number, msiv.description Item_Description, gl2.meaning Row_Type, fcl.meaning Item_Type, misv.inventory_item_status_code Item_Status, ml1.meaning Make_Buy_Code, &category_segments1 &category_segments2 &category_segments3 gmd.original_qty * mucv.conversion_rate Primary_Original_Quantity, gmd.plan_qty * mucv.conversion_rate Primary_Planned_Quantity, msiv.primary_uom_code Primary_UOM_Code, gmd.original_qty Original_Transacted_Quantity, gmd.plan_qty Planned_Transacted_Quantity, gmd.dtl_um Planned_Transacted_UOM, gmd.actual_qty Actual_Transacted_Quantity, gmd.dtl_um Transacted_UOM_Code, gmd.actual_qty * mucv.conversion_rate Primary_Actual_Quantity, msiv.primary_uom_code Primary_UOM_Code, msiv.primary_uom_code Primary_UOM, ic.cost_type Cost_Type, ic.cost_type_desc Cost_Type_Description, ic.item_cost Item_Cost from gme_batch_header gbh, gme_material_details gmd, mtl_uom_conversions_view mucv, item_cost ic, gmf_period_statuses gps, gmf_fiscal_policies gfp, gmf_calendar_assignments gca, org_acct_periods oap, mtl_system_items_vl msiv, -- Product, Byproduct or Ingredient mtl_item_status_vl misv, gem_lookups gl1, -- Batch Status gem_lookups gl2, -- Line Type fnd_common_lookups fcl, -- Item Type mfg_lookups ml1, -- Make Buy Code mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl where mp.organization_id = gbh.organization_id and oap.organization_id = mp.organization_id and gmd.batch_id = gbh.batch_id and gbh.delete_mark = 0 and ic.inventory_item_id = gmd.inventory_item_id and ic.organization_id = gmd.organization_id and mucv.uom_code = gmd.dtl_um and mucv.inventory_item_id = gmd.inventory_item_id and mucv.organization_id = gmd.organization_id and gps.legal_entity_id = gfp.legal_entity_id and gps.cost_type_id = gfp.cost_type_id and gps.cost_type_id = gca.cost_type_id and gps.legal_entity_id = gca.legal_entity_id and gps.calendar_code = gca.calendar_code and msiv.organization_id = mp.organization_id and msiv.organization_id = gmd.organization_id and msiv.inventory_item_id = gmd.inventory_item_id and gl1.lookup_type (+) = 'GME_BATCH_STATUS' and gl1.lookup_code (+) = gbh.batch_status and gl2.lookup_type (+) = 'LINE_TYPE' and gl2.lookup_code (+) = gmd.line_type and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type and misv.inventory_item_status_code = msiv.inventory_item_status_code and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.planning_make_buy_code -- Avoid the item master organization and mp.organization_id <> mp.master_organization_id -- Avoid disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate +1) -- Get process organizations and nvl(mp.process_enabled_flag, 'N') = 'Y' -- Limit to either open batches or batches closed after the period start date and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) 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 --and (nvl(fnd_profile.value('XXEN_REPORT_USE_LEDGER_SECURITY'),'N')='N' or gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))) --and (nvl(fnd_profile.value('XXEN_REPORT_USE_OPERATING_UNIT_SECURITY'),'N')='N' or haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)) --and mp.organization_code in (select oav.organization_code 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_ledger, p_operating_unit, p_period_name, p_batch_number and 2=2 -- p_org_code and 4=4 -- p_item_number, p_calendar_code, p_period_code and 5=5 -- p_status_to_include order by nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, gps.calendar_code, gps.period_code, gbh.batch_no, -- Batch Number gl2.meaning, -- Line_Type msiv.concatenated_segments, -- Item_Number msiv.description, -- Item_Description fcl.meaning -- Inventory Item_Type Added Original Quantity columns at primary and transacted UOM #,##0.00000;[Red](#,##0.00000) US CAC OPM Batch Material Summary Report showing Batch materials in summary for each product, byproduct and ingredient. Displaying batches which were open during the monthly inventory accounting period or batches which were closed during the monthly inventory accounting period. If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end item costs. Parameters: ========== Period Name: enter the monthly inventory accounting period you wish to report (mandatory). Cost Type: enter a Cost Type to value the quantities using the Cost Type item costs; if Cost Type is not entered the report will use the Cost Type from your Fiscal Policies (optional). OPM Calendar Code: choose the OPM Calendar Code which corresponds to the period costs you wish to report (mandatory). OPM Period Code: enter the OPM Period Code which corresponds to the period costs and OPM Calendar Code you wish to report (mandatory). Category Sets 1 - 3: enter up to three item category sets you wish to report (optional). Item Number: specific Product, By-Product or Ingredient you wish to report (optional). Batch Number: enter any batch number which is open or was closed within the date range of the OPM Period Code and Calendar Code (optional). Batch Status: to minimize the report size, specify the batch statuses you wish to report (optional). Batch Number From: (optional). Batch Number To: (optional). Organization Code: enter the inventory organization(s), defaults to your session's inventory organization (optional). Operating Unit: enter the operating unit(s) you wish to report (optional). Ledger: enter the ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2025 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 22 Jan 2025 Douglas Volz Initial Coding based on client's sample report. -- +=============================================================================*/ Enginatics R12 only &category_segments1 &category_segments2 &category_segments3 1=1 2=2 3=3 4=4 5=5 :p_cost_type 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 oap.period_name value from org_acct_periods oap, (select max(oap.schedule_close_date) default_date from org_acct_periods oap, org_organization_definitions ood where ood.organization_id = oap.organization_id and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date and rownum = 1 Y US Period Name 2 20 3=3 cmm.cost_mthd_code = :p_cost_type LOV Cost Type (OPM Orgs Only) 184A903C40A16B3FE0630100007F2880 Y select x.value, x.description from ( select cmm.cost_mthd_code value, cmm.cost_mthd_desc description from cm_mthd_mst cmm where exists (select 'x' from gmf_calendar_assignments gca, org_organization_definitions ood, mtl_parameters mp where gca.legal_entity_id = ood.legal_entity and gca.cost_type_id = cmm.cost_type_id and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or 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 (:$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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id and mp.process_enabled_flag = 'Y' ) ) x group by x.value, x.description order by value US Cost Type 3 30 4=4 gps.calendar_code = :p_calendar_code LOV OPM Calendar Code 19508EAA42357240E0630100007FD049 Y select ccht.calendar_code value, ccht.calendar_desc description from cm_cldr_hdr_tl ccht where ccht.language = userenv('lang') and exists (select 'x' from gmf_calendar_assignments gca, gmf_period_statuses gps, org_organization_definitions ood, mtl_parameters mp where gca.calendar_code = gps.calendar_code and gca.legal_entity_id = gps.legal_entity_id and gca.legal_entity_id = ood.legal_entity and gca.calendar_code = ccht.calendar_code and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or 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 (:$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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id ) order by value desc select ccht.calendar_code value, ccht.calendar_desc description from cm_cldr_hdr_tl ccht where ccht.language = userenv('lang') and ccht.calendar_code = (select gps.calendar_code from gmf_calendar_assignments gca, gmf_period_statuses gps, ic_cldr_dtl icd, org_acct_periods oap, org_organization_definitions ood, mtl_parameters mp where gca.calendar_code = gps.calendar_code and gca.legal_entity_id = gps.legal_entity_id and gca.legal_entity_id = ood.legal_entity and gca.calendar_code = ccht.calendar_code -- added to tie the OPM period_id with the inventory acct_period_id and icd.fiscal_year = oap.period_year and icd.period_end_date = oap.schedule_close_date and icd.period = oap.period_num and icd.period_id = gps.period_id and oap.organization_id = mp.organization_id and oap.period_name = :$flex$.period_name and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or 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 (:$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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id group by gps.calendar_code ) order by value desc Y US OPM Calendar Code 4 40 4=4 gps.period_code = :p_period_code LOV OPM Period Code 19508EAA42367240E0630100007FD049 Y select ccd.period_code value, ccd.period_desc description from cm_cldr_dtl ccd where exists (select 1 from gmf_period_statuses gps where gps.calendar_code = :$flex$.opm_calendar_code and gps.legal_entity_id in (select ood.legal_entity from org_organization_definitions ood where (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))) and gps.period_code = ccd.period_code and gps.calendar_code = ccd.calendar_code ) order by ccd.end_date desc Y US OPM Period Code 5 50 5=5 gl1.meaning = :p_batch_status LOV GME Batch Status 2EC46130C329100BE06369FB0905137D Y select gl1.meaning value, gl1.description description from gem_lookups gl1 where gl1.lookup_type = 'GME_BATCH_STATUS' US Batch Status 6 60 1=1 gbh.batch_no = :p_batch_number LOV GME Batch Number 1A573DC3BA400377E0630100007FE5A2 Y select gbh.batch_no value, gl.description description from gme_batch_header gbh, mtl_parameters mp, org_organization_definitions ood, org_acct_periods oap, gem_lookups gl where gbh.organization_id=mp.organization_id and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and gl.lookup_type (+) = 'GME_BATCH_STATUS' and gl.lookup_code (+) = gbh.batch_status and oap.organization_id = mp.organization_id and (:$flex$.period_name is not null and oap.period_name = :$flex$.period_name) and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) US Batch Number 7 70 1=1 gbh.batch_no >= :p_batch_number_from LOV GME Batch Number 1A573DC3BA400377E0630100007FE5A2 Y select gbh.batch_no value, gl.description description from gme_batch_header gbh, mtl_parameters mp, org_organization_definitions ood, org_acct_periods oap, gem_lookups gl where gbh.organization_id=mp.organization_id and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and gl.lookup_type (+) = 'GME_BATCH_STATUS' and gl.lookup_code (+) = gbh.batch_status and oap.organization_id = mp.organization_id and (:$flex$.period_name is not null and oap.period_name = :$flex$.period_name) and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) US Batch Number From 8 80 1=1 gbh.batch_no <= :p_batch_number_to LOV GME Batch Number To 2EC46130C328100BE06369FB0905137D Y select gbh.batch_no value, gl.description description from gme_batch_header gbh, mtl_parameters mp, org_organization_definitions ood, org_acct_periods oap, gem_lookups gl where gbh.organization_id=mp.organization_id and (gbh.batch_no >= :$flex$.batch_number_from or :$flex$.batch_number_from is null) and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and gl.lookup_type (+) = 'GME_BATCH_STATUS' and gl.lookup_code (+) = gbh.batch_status and oap.organization_id = mp.organization_id and (:$flex$.period_name is not null and oap.period_name = :$flex$.period_name) and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) US Batch Number To 9 90 &category_segments1 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 10 100 &category_segments2 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 11 110 &category_segments3 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 12 120 4=4 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 13 130 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 14 140 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 15 150 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 23EEDC2D0B830DE1CA8999265B669A7A Count by OPM Org MFG 9 ACTUAL_COMPLETION_DATE 27 ACTUAL_TRANSACTED_QUANTITY 6 BATCH_NUMBER 7 BATCH_START_DATE 11 BATCH_STATUS 4 CALENDAR_CODE 10 CLOSED_DATE 31 COST_TYPE 32 COST_TYPE_DESCRIPTION 33 ITEM_COST 13 ITEM_DESCRIPTION 12 ITEM_NUMBER 16 ITEM_STATUS 15 ITEM_TYPE 20 Inv.Items Description 1 LEDGER 17 MAKE_BUY_CODE -19.002 Major Category -19.0021 Minor Category 2 OPERATING_UNIT 3 ORG_CODE 24 ORIGINAL_TRANSACTED_QUANTITY 5 PERIOD_CODE 8 PLANNED_COMPLETION_DATE 25 PLANNED_TRANSACTED_QUANTITY 26 PLANNED_TRANSACTED_UOM 29 PRIMARY_ACTUAL_QUANTITY 21 PRIMARY_ORIGINAL_QUANTITY 22 PRIMARY_PLANNED_QUANTITY 30 PRIMARY_UOM 23 PRIMARY_UOM_CODE 18 Product Cat 19 Product Description 14 ROW_TYPE 28 TRANSACTED_UOM_CODE CALENDAR_CODE ROW 4 LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE VALUE 1 COUNT ORG_CODE ROW 3 PERIOD_CODE ROW 5 S Site 23EEDC2D0B830DE1CA8999265B669A7A