XXEN_SCHUB_ITEM_FILTER

Description

System report controlling the item filter functionality on the Supply Chain Hub form

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

update bom_small_expl_temp bset set bset.request_id=null where (bset.organization_id,bset.component_item_id) in (
select
msib.organization_id,
msib.inventory_item_id
from
mtl_system_items_b msib
where
1=1
)

Parameter Name

SQL text

Validation

Organization

msib.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:org_code)

LOV

Buyer

msib.buyer_id in
(
select
ppx.person_id
from
per_people_x ppx
where
ppx.full_name=:buyer and
ppx.current_employee_flag='Y' and
ppx.person_id in (select pa.agent_id from po_agents pa)
)

LOV

Planner

msib.planner_code=:planner_code

LOV

Make or Buy

msib.planning_make_buy_code=xxen_util.lookup_code(:make_or_buy,'MTL_PLANNING_MAKE_BUY',700)

LOV

Exception

:exception_message is not null and
(msib.organization_id,msib.inventory_item_id) in
  (select
    to_number(substr(x.column_value,1,instr(x.column_value,'|')-1)),
    to_number(substr(x.column_value,instr(x.column_value,'|')+1))
   from table(xxen_schub.get_exceptions_item_list(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,null,:exception_message)) x
 )

LOV

Exception exists

:exception_exists='Y' and
(msib.organization_id,msib.inventory_item_id) in
  (select
    to_number(substr(x.column_value,1,instr(x.column_value,'|')-1)),
    to_number(substr(x.column_value,instr(x.column_value,'|')+1))
   from table(xxen_schub.get_exceptions_item_list(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,null,null)) x
 )

LOV Oracle

BOM Item Type

msib.bom_item_type=:bom_item_type

LOV Oracle

Inventory Items only

msib.inventory_item_flag='Y'

LOV Oracle

WIP Supply Type

msib.wip_supply_type=xxen_util.lookup_code(:wip_supply_type_incl,'WIP_SUPPLY',700)

LOV

Excl. WIP Supply Type

msib.wip_supply_type!=xxen_util.lookup_code(:wip_supply_type_excl,'WIP_SUPPLY',700)

LOV

WIP Job exists

(msib.inventory_item_id, msib.organization_id) in
(
select
wdj.primary_item_id,
wdj.organization_id
from
bom_small_expl_temp bset,
wip_discrete_jobs wdj
where
bset.organization_id = wdj.organization_id and
bset.component_item_id = wdj.primary_item_id and
wdj.status_type in (1,3,6,8,9,10,11,13,14,15,16,17)
)

LOV Oracle

On Hand exists

nvl((
select
sum(moq.transaction_quantity) on_hand
from
mtl_onhand_quantities moq
where
msib.organization_id=moq.organization_id and
msib.inventory_item_id=moq.inventory_item_id
),0)<=0