ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEA279D2E0530100007F1FF2 INV Organization Code (all) Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.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 and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US All active inventory organization codes FBDA678CB4526153E0530F1A0C0A8952Y with init_items as ( select /*+ materialize */ msib.segment1 item_number ,msib.description item_description ,msib.inventory_item_id ,moq.subinventory_code ,moq.locator_id ,msib.organization_id ,moq.lot_number as lot ,sum(moq.primary_transaction_quantity) on_hand ,sum(moq.secondary_transaction_quantity) secondary_onhand from mtl_system_items_b msib ,mtl_onhand_quantities_detail moq ,mtl_parameters mp where 1=1 and moq.inventory_item_id = msib.inventory_item_id and moq.organization_id = msib.organization_id and mp.organization_id = msib.organization_id and moq.organization_id = moq.organization_id group by msib.segment1 ,msib.description ,msib.inventory_item_id ,moq.subinventory_code ,moq.locator_id ,msib.organization_id ,moq.lot_number ) , items as ( select /*+ materialize */ aa.* from init_items aa where not exists (select * from mtl_material_transactions mmt,mtl_transaction_types mtt where aa.inventory_item_id = mmt.inventory_item_id and aa.organization_id = mmt.organization_id and mtt.transaction_type_id = mmt.transaction_type_id and mmt.transaction_type_id not in (24) and trunc(mmt.transaction_date) > trunc(sysdate) - :p_period and mmt.organization_id = aa.organization_id ) ) , onhand_data as ( select /*+ materialize */ aa.item_number ,aa.item_description ,haou.name organization_name ,aa.subinventory_code ,mic.category_concat_segs ,mc.description category_desc ,cv.item_cost ,aa.on_hand ,aa.on_hand * cv.item_cost as item_value ,aa.secondary_onhand ,aa.organization_id ,aa.inventory_item_id ,aa.locator_id ,inv_project.get_locator(aa.locator_id,aa.organization_id) as locator_one ,aa.lot from items aa ,cst_item_cost_type_v cv ,hr_all_organization_units haou ,mtl_category_sets mcs ,mtl_item_categories_v mic ,mtl_categories_v mc where cv.organization_id = aa.organization_id and cv.inventory_item_id = aa.inventory_item_id and 2=2 and cv.cost_type = 'Frozen' and haou.organization_id = aa.organization_id and mic.inventory_item_id = aa.inventory_item_id and mic.organization_id = aa.organization_id and mic.category_set_id = mcs.category_set_id and mcs.category_set_name = 'Inv.Items' and mic.enabled_flag = 'Y' and mc.category_id = mic.category_id order by 1 ) select aa.item_number ,aa.item_description ,aa.organization_name ,aa.subinventory_code ,aa.locator_id,aa.organization_id ,inv_project.get_locator(aa.locator_id,aa.organization_id) as locator ,aa.locator_one ,aa.lot ,aa.category_concat_segs ,aa.category_desc ,aa.item_cost ,aa.on_hand ,aa.item_value ,aa.secondary_onhand ,(select max(mmt.transaction_date) from mtl_material_transactions mmt where aa.inventory_item_id = mmt.inventory_item_id and aa.organization_id = mmt.organization_id and mmt.transaction_type_id not in (24) ) last_transacted_date from onhand_data aa colin_boer@gjames.com.au 15 US GJ Items Not Transacted Report This report provides an extract of Inventory Items that are in Stock but not transacted for a specific period. 1=1 2=2 :p_period 1 10 1=1 mp.organization_code = :p_organization_code LOV INV Organization Code (all) 8E2FF36EDEA279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.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 and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code M1 Y US Organization 2 20 1=1 msib.SEGMENT1 = :p_item_number LOV custom Y select SEGMENT1 value, SEGMENT1 description from MTL_SYSTEM_ITEMS_B msi ,mtl_parameters mp where 1=1 and mp.organization_code=:$flex$.organization and msi.organization_id = mp.organization_id order by 1 US Item Number 3 30 2=2 mic.category_concat_segs = :p_category LOV custom Y select mc.category_concat_segs as value ,mc.description as description from mtl_category_sets mcs ,mtl_categories_v mc where mcs.structure_id = mc.structure_id and mcs.category_set_name = 'GJ Class' and mc.enabled_flag = 'Y' order by 1 US Category 4 40 1=1 moq.Subinventory_code like '%'|| :p_subinventory || '%' Char US Subinventory 5 50 :p_period LOV custom Y with day_vals as ( select 30 as day_val from dual union all select 60 as day_val from dual union all select 180 as day_val from dual union all select 365 as day_val from dual ) select day_val as value, to_char(day_val) as description from day_vals order by 1 30 Y US Period