ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C1EE85AE0E4ED163E0530100007F9DEDY select sd.item, sd.item_description, sd.user_item_type, sd.planner, sd.organization_code, sd.organization_name, &category_set_columns sd.requirement_date, sd.supply_demand_type, sd.identifier, sd.quantity, sd.available_quantity, sd.onhand_qty_ current_onhand, sd.avail_qty_ current_available, sd.reservable_onhand_qty_ current_reservable_onhand, sd.reservable_avail_qty_ current_reservable_available, sd.reserved_qty_ current_reserved from ( select sd1.*, sum(sd1.qty_) over (partition by sd1.item, sd1.organization_code order by sd1.seq)+sd1.avail_qty_ available_quantity from ( select rownum seq, sd2.* from (select msiv.concatenated_segments item, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type, msiv.planner_code planner, ood.organization_code, ood.organization_name, msdt.requirement_date, xxen_util.meaning(msdt.supply_demand_source_type,'MRP_SUPPLY_DEMAND_SOURCE_TYPE',700) supply_demand_type, case msdt.disposition_type when 1 then (select nvl(clm_document_number,segment1) from po_headers_all where po_header_id = msdt.disposition_id ) when 2 then (select segment1 from mtl_sales_orders_kfv where sales_order_id = msdt.disposition_id ) when 3 then (select concatenated_segments from gl_code_combinations_kfv where code_combination_id = msdt.disposition_id ) when 4 then (select wip_entity_name from wip_entities where wip_entity_id = msdt.disposition_id ) when 5 then (select wip_entity_name from wip_entities where wip_entity_id = msdt.disposition_id ) when 6 then (select concatenated_segments from mtl_generic_dispositions_kfv where organization_id = msdt.organization_id and disposition_id = msdt.disposition_id ) when 8 then (select shipment_num from rcv_shipment_headers where shipment_header_id = msdt.disposition_id ) when 10 then (select segment1 from po_requisition_headers_all where requisition_header_id = msdt.disposition_id ) when 30 then (select schedule_designator from mrp_schedule_dates where mps_transaction_id = msdt.disposition_id and schedule_level = 2 and supply_demand_type = 2 ) when 31 then (select request_number from mtl_txn_request_headers where header_id = msdt.disposition_id ) else nvl2(msdt.disposition_type,msdt.c_column1,null) end identifier, msdt.quantity, msdt.on_hand_quantity, nvl2(msdt.on_hand_quantity,msdt.quantity,0) qty_, nvl(xxen_inv_sd.get_item_start_qty(msdt.inventory_item_id,msdt.organization_id,'ONHAND'),0) onhand_qty_, nvl(xxen_inv_sd.get_item_start_qty(msdt.inventory_item_id,msdt.organization_id,'AVAILABLE'),0) avail_qty_, nvl(xxen_inv_sd.get_item_start_qty(msdt.inventory_item_id,msdt.organization_id,'RESERVABLE_ONHAND'),0) reservable_onhand_qty_, nvl(xxen_inv_sd.get_item_start_qty(msdt.inventory_item_id,msdt.organization_id,'RESERVABLE_AVAILABLE'),0) reservable_avail_qty_, nvl(xxen_inv_sd.get_item_start_qty(msdt.inventory_item_id,msdt.organization_id,'RESERVED'),0) reserved_qty_, msdt.inventory_item_id, msdt.organization_id from mtl_supply_demand_temp msdt, mtl_system_items_vl msiv, org_organization_definitions ood where msiv.organization_id = msdt.organization_id and msiv.inventory_item_id = msdt.inventory_item_id and ood.organization_id = msdt.organization_id and msdt.record_type = 'SD' and nvl(msdt.quantity, -1) <> 0 and msdt.seq_num in (select to_number(column_value) from table(cast(xxen_inv_sd.get_seq_nums_table() as fnd_table_of_varchar2_30))) and 1=1 order by msiv.concatenated_segments, ood.organization_code, msdt.requirement_date, msdt.supply_demand_type desc, msdt.supply_demand_source_type, msdt.quantity, decode(sign(msdt.quantity),-1,-1*msdt.on_hand_quantity,msdt.on_hand_quantity), identifier ) sd2 ) sd1 ) sd where 2=2 Added Supply Demand Type as an optional, multiple-values parameter. It is applied after the Available Quantity running total, so that total still includes all supply and demand types. XXEN_INV_SD :Item is not null or :Item_Number_Contains is not null or (:Item_From is not null and :Item_To is not null) or (:Category_Set is not null and :Category is not null) or :Planner is not null US INV Item Supply/Demand Inventory Item Supply/Demand data as per the standard Inventory Item Supply/Demand form The run time depends on the number of items selected, as Oracle's supply/demand calculation runs once for each item and organization. Supply Demand Type only restricts the rows shown. At least one item selection critieria (Item, Item Contains, Item From/To Range, Category Set/Category or Planner) must be specified Enginatics &category_set_columns 1=1 2=2 :p_category :p_category_set_name :p_cutoff_date :p_item_contains :p_item_from :p_item_to :p_onhand_source_code 1 10 1=1 ood.organization_code=:p_organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 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 mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) 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 select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y F Code de l'organisation US Organization Code 2 20 1=1 msiv.concatenated_segments=:p_item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 F Article US Item 3 30 :p_item_contains Char F Le numéro d'article contient US Item Number Contains 4 40 :p_item_from LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 F Article de US Item From 5 50 :p_item_to LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 :$flex$.Item_From F Objet à US Item To 6 60 :p_category_set_name LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 select mcsv.category_set_name from mtl_default_category_sets mdcs , mtl_category_sets_vl mcsv where mdcs.functional_area_id = 1 and mdcs.category_set_id = mcsv.category_set_id F Ensemble de la catégorie US Category Set 7 &category_set_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'sd') sql_text from dual F Ensemble de la catégorie US Category Set 8 70 :p_category LOV INV Category 8E2FF36EDECB79D2E0530100007F1FF2 select mcv.category_concat_segs value, mcv.description from mtl_category_sets_vl mcsv, mtl_categories_v mcv where mcsv.category_set_name=:$flex$.category_set and mcsv.structure_id=mcv.structure_id order by mcv.category_concat_segs F Catégorie US Category 9 80 1=1 msiv.planner_code=:p_planner LOV INV Planner Code (all) A5D770D0ECB301D5E0530100007F19A3 select mp.planner_code value, mp.description from mtl_planners mp where nvl(mp.disable_date,sysdate+1)>=sysdate and (:$flex$.organization_code is null or mp.organization_id in (select mp0.organization_id from mtl_parameters mp0 where xxen_util.contains(:$flex$.organization_code,mp0.organization_code)='Y')) order by mp.planner_code US Planner 10 90 :p_cutoff_date Date F Date limite US Cutoff Date 11 1=1 msdt.requirement_date <= trunc(:p_cutoff_date) F Date limite US Cutoff Date 12 100 2=2 sd.supply_demand_type=:p_supply_demand_type LOV custom select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MRP_SUPPLY_DEMAND_SOURCE_TYPE' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Supply Demand Type Restricts the rows shown. Available Quantity still includes all supply and demand types. 13 110 :p_onhand_source_code LOV custom select x.value, x.description from ( select 'ATP subinventories only' value, null description from dual union select 'Net subinventories only' value, null description from dual union select 'All subinventories' value, null description from dual ) x order by x.value ATP subinventories only Y F Inclure la source manuelle US Include Onhand Source