XXEN_SCHUB_ITEM_FILTER

Description
Categories: Supply Chain Hub
System report controlling the item filter functionality on the Supply Chain Hub form
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
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
Default Organization ID
nvl(:Default_Organization_ID,0) = nvl(:Default_Organization_ID,0)
Number
Plan Source
nvl(:plan_source,'?') = nvl(:plan_source,'?')
Plan Instance ID
nvl(:Plan_Instance_ID,0) = nvl(:Plan_Instance_ID,0)
Number
Default Plan
nvl(:Default_Plan,'?') = nvl(:Default_Plan,'?')
Planner
msib.planner_code=:planner_code
LOV
Make or Buy
msib.planning_make_buy_code=(
select
flv.lookup_code
from
fnd_lookup_values flv
where
flv.meaning=:make_or_buy and
flv.lookup_type='MTL_PLANNING_MAKE_BUY' and
flv.language=userenv('lang') and
flv.view_application_id=700 and
flv.security_group_id=0
)
LOV
Exception
(msib.organization_id, msib.inventory_item_id) in
(
select distinct
med.organization_id,
med.inventory_item_id
from
  mrp_exception_details  med
, fnd_lookup_values_vl   flvv
where
        flvv.meaning             = :exception_message
and flvv.lookup_type         = 'MRP_EXCEPTION_CODE_TYPE'
and flvv.view_application_id = 700
and flvv.lookup_code         = med.exception_type
and :Plan_Source = 'MRP'
and med.compile_designator   = :Default_Plan
and med.organization_id      = :Default_Organization_ID
union
select distinct
  xsmev.organization_id
, xsmev.inventory_item_id
from
  xxen_schub_msc_exceptions_v   xsmev
where
        xsmev.exception_type_meaning      = :exception_message
and :Plan_Source = 'MSC'
and xsmev.sr_instance_id              = :Plan_Instance_ID
and xsmev.compile_designator          = :Default_Plan
and xsmev.organization_id             = :Default_Organization_ID
)
LOV
Exception exists
(msib.organization_id, msib.inventory_item_id) in
(
select distinct
med.organization_id,
med.inventory_item_id
from
  mrp_exception_details  med
, fnd_lookup_values_vl   flvv
where
        :exception_exists = 'Y'
and :Plan_Source = 'MRP'
and med.compile_designator   = :Default_Plan
and med.organization_id      = :Default_Organization_ID
and flvv.lookup_type         = 'MRP_EXCEPTION_CODE_TYPE'
and flvv.view_application_id = 700
and flvv.lookup_code         = med.exception_type
and med.exception_type    in (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29)
union
select distinct
  xsmev.organization_id
, xsmev.inventory_item_id
from
  xxen_schub_msc_exceptions_v   xsmev
where
        :exception_exists = 'Y'
and :Plan_Source = 'MSC'
and xsmev.sr_instance_id              = :Plan_Instance_ID
and xsmev.compile_designator          = :Default_Plan
and xsmev.organization_id             = :Default_Organization_ID
and xsmev.exception_type    in (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29)
)
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 Job exists
(msib.inventory_item_id, msib.organization_id) in
(
select
wdj.primary_item_id,
wdj.organization_id
from
wip_discrete_jobs wdj
where
wdj.status_type in (1,3,6,8,9,10,11,13,14,15,16,17) and
wdj.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')
)
LOV Oracle
On Hand exists
nvl((
select
sum(moqd.primary_transaction_quantity) on_hand
from
mtl_onhand_quantities_detail moqd,
mtl_secondary_inventories msi
where
msib.organization_id=moqd.organization_id and
msib.inventory_item_id=moqd.inventory_item_id and
moqd.subinventory_code=msi.secondary_inventory_name and
moqd.organization_id=msi.organization_id and
msi.availability_type=1
),0)<=0
LOV Oracle
Exception exists
(msib.organization_id, msib.inventory_item_id) not in
(
select distinct
med.organization_id,
med.inventory_item_id
from
  mrp_exception_details  med
, fnd_lookup_values_vl   flvv
where
        :exception_exists = 'N'
and :Plan_Source = 'MRP'
and med.compile_designator   = :Default_Plan
and med.organization_id      = :Default_Organization_ID
and flvv.lookup_type         = 'MRP_EXCEPTION_CODE_TYPE'
and flvv.view_application_id = 700
and flvv.lookup_code         = med.exception_type
and med.exception_type    in (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29)
union
select distinct
  xsmev.organization_id
, xsmev.inventory_item_id
from
  xxen_schub_msc_exceptions_v   xsmev
where
        :exception_exists = 'N'
and :Plan_Source = 'MSC'
and xsmev.sr_instance_id              = :Plan_Instance_ID
and xsmev.compile_designator          = :Default_Plan
and xsmev.organization_id             = :Default_Organization_ID
and xsmev.exception_type    in (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29)
)
WIP Job exists
not exists (
select
null
from
wip_discrete_jobs wdj
where
msib.inventory_item_id=wdj.primary_item_id and
msib.organization_id=wdj.organization_id and
wdj.status_type in (1,3,6,8,9,10,11,13,14,15,16,17) and
wdj.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')
)
On Hand exists
(
select
sum(moqd.primary_transaction_quantity) on_hand
from
mtl_onhand_quantities_detail moqd,
mtl_secondary_inventories msi
where
msib.organization_id=moqd.organization_id and
msib.inventory_item_id=moqd.inventory_item_id and
moqd.subinventory_code=msi.secondary_inventory_name and
moqd.organization_id=msi.organization_id and
msi.availability_type=1
)>0