ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 09737AE5380A0E64E0630100007F4174Y select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, onhand.period_name Period_Name, onhand.concatenated_segments Item_Number, onhand.description Item_Description, fcl.meaning Item_Type, misv.inventory_item_status_code_tl Item_Status, decode(onhand.subinventory_code, null, round(nvl(onhand.rollback_intransit_value,0) / decode(nvl(onhand.rollback_quantity,0), 0, 1, nvl(onhand.rollback_quantity,0)),5), round((nvl(onhand.rollback_value,0)) / decode(nvl(onhand.rollback_quantity,0), 0, 1, nvl(onhand.rollback_quantity,0)),5) ) Item_Cost, nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit, nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description, ml2.meaning Asset, muomv.uom_code UOM_Code, round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity, decode(onhand.subinventory_code, null, round(nvl(onhand.rollback_intransit_value,0),2), round(nvl(onhand.rollback_value,0),2) ) Onhand_Value from mtl_parameters mp, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, fnd_common_lookups fcl, -- Item Type mfg_lookups ml1, -- Intransit mfg_lookups ml2, -- Inventory Asset mtl_secondary_inventories msub, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Inner query for onhand quantities and values (-- For non-category accounting select mp.organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description, msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, msiv.inventory_asset_flag, oap.period_name, cpcs.acct_period_id, nvl(cpcs.subinventory_code, 'Intransit') subinventory_code, sum(cpcs.rollback_quantity) rollback_quantity, sum(cpcs.rollback_value) rollback_value, sum(cpcs.rollback_intransit_value) rollback_intransit_value from mtl_system_items_vl msiv, cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp where mp.organization_id = msiv.organization_id and oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = cpcs.organization_id and oap.organization_id = mp.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id -- Don't get zero quantities and nvl(cpcs.rollback_quantity,0) <> 0 -- Don't report expense items and msiv.inventory_asset_flag = 'Y' and 4=4 -- p_period_name and 2=2 -- p_org_code -- Need to group by due to possibility for having multiple cost groups by subinventory group by mp.organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null), msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, msiv.inventory_asset_flag, oap.period_name, cpcs.acct_period_id, cpcs.subinventory_code ) onhand -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== where mp.organization_id = onhand.organization_id and 2=2 -- p_org_code and muomv.uom_code = onhand.primary_uom_code and misv.inventory_item_status_code = onhand.inventory_item_status_code and onhand.subinventory_code = msub.secondary_inventory_name (+) and onhand.organization_id = msub.organization_id (+) -- =========================================== -- Lookup Codes -- =========================================== and fcl.lookup_code (+) = onhand.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' and ml1.lookup_code = 3 -- Intransit and ml1.lookup_type = 'MSC_CALENDAR_TYPE' and ml2.lookup_code = nvl(msub.asset_inventory,1) and ml2.lookup_type = 'SYS_YES_NO' and not exists (select 'x' from cst_item_costs cic, cst_cost_types cct where cic.organization_id = onhand.organization_id and cic.inventory_item_id = onhand.inventory_item_id and cic.cost_type_id = cct.cost_type_id and 3=3 -- p_cost_type ) -- =================================================================== -- Using the base tables to avoid using views -- =================================================================== 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 -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Revision for version 1.1, Operating Unit and Ledger Controls and Parameters 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 1=1 -- p_operating_unit, p_ledger -- order by Ledger, Operating_Unit, Org_Code, and Item order by nvl(gl.short_name, gl.name), -- Ledger haou2.name, -- Operating_Unit mp.organization_code, -- Org_Code onhand.period_name, -- Period_Name onhand.concatenated_segments, -- Item_Number nvl(onhand.subinventory_code, ml1.meaning) -- Subinventory_or_Intransit 1.1 04 Mar 2025 Douglas Volz Removed tabs, add ledger and operating unit columns and security access profiles. #,##0.00000;[Red](#,##0.00000) US CAC Cost Type Costs Not in Period Close Inventory Snapshot Report comparing the month-end items, balances and costs against any entered Cost Type, showing which item numbers in your month-end inventory which are not in your Cost Type. You automatically save off your month-end quantities and values when you close the inventory accounting period. Parameters: ========== Period Name (Closed): the closed inventory accounting period you wish to report (mandatory). Cost Type: enter a Cost Type to compare against the stored month-end items, quantities and values (mandatory). Item Number: enter the specific item number(s) you wish to report (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 2024 - 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. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | =================================================================== -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 15 Jan 2024 Douglas Volz Initial Coding -- | 1.1 04 Mar 2025 Douglas Volz Removed tabs, add ledger and operating unit -- | columns and security access profiles. -- +=============================================================================+*/ Enginatics 1=1 2=2 3=3 4=4 1 10 4=4 oap.period_name = :p_period_name LOV INV Period (closed and summarized) 91E8680353217CCEE053BB6B635868C2 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_close_date is not null and oap.summarized_flag='Y' and 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 (oap.period_close_date is not null and oap.summarized_flag = 'Y') and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date and rownum = 1 Y US Period Name (Closed) 2 20 3=3 cct.cost_type = :p_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 Y US Cost Type 3 50 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 4 60 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 5 70 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 6 80 1=1 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and 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 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, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger C6A882ECF65BA5FF20A4509DAD054203 Pivot by Org MFG 22 ASSET 7 Account 17 Class 5 Company 21 DESCRIPTION 6 Department 16 Family 19 ITEM_COST 11 ITEM_DESCRIPTION 10 ITEM_NUMBER 13 ITEM_STATUS 12 ITEM_TYPE 18 Inv.Items Description 1 LEDGER 24 ONHAND_QUANTITY 25 ONHAND_VALUE 2 OPERATING_UNIT 3 ORG_CODE 4 PERIOD_NAME 9 Product 14 Product Cat 15 Product Description 20 SUBINVENTORY_OR_INTRANSIT 8 Sub-Account 23 UOM_CODE LEDGER ROW 1 ONHAND_QUANTITY VALUE 1 SUM ONHAND_VALUE VALUE 2 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 S Site MFG C6A882ECF65BA5FF20A4509DAD054203 C6A882ECF65BA5FF20A4509DAD054203