XXEN_SCHUB_ITEM_SEARCH

Description
Categories: Supply Chain Hub
System report controlling the search functionality on the Supply Chain Hub form
insert into xxen_report_search_ids xrsi (rowid1)
select
msib.rowid row_id
from
mtl_system_items_b msib
where
1=1 and
msib.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
rownum<=nvl(fnd_profile.value('XXEN_SCHUB_SEARCH_ROW_LIMIT'),1000)
Parameter Name SQL text Validation
Item Created By
msib.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_by)
LOV
Item Type
msib.item_type=xxen_util.lookup_code(:item_type,'ITEM_TYPE',3)
LOV
Exclude Item Type
not (msib.item_type=xxen_util.lookup_code(:exclude_item_type,'ITEM_TYPE',3))
LOV
Item Status
msib.inventory_item_status_code in (select misv.inventory_item_status_code from mtl_item_status_vl misv where misv.inventory_item_status_code_tl=:item_status)
LOV
Operating Unit
:operating_unit=:operating_unit
LOV
Category Set
(msib.organization_id, msib.inventory_item_id) in (
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_item_categories mic
where
mcsv.category_set_name=:category_set_name and
mcsv.category_set_id=mic.category_set_id)
LOV
Category
(msib.organization_id, msib.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_kfv mck
where
mck.concatenated_segments=:category and
mck.structure_id=mcs.structure_id and
mck.category_id=mic.category_id and
mcs.category_set_id=mic.category_set_id
)
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
Cross Reference Type
msib.inventory_item_id in (
select
mcrb.inventory_item_id
from
mtl_cross_references_b mcrb
where
mcrb.cross_reference_type=:cross_reference_type and
sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate)
)
LOV
Purchase Order
msib.inventory_item_id in (
select
pla.item_id
from
hr_all_organization_units haou,
po_headers_all pha,
po_lines_all pla
where
haou.organization_id=pha.org_id and
pha.segment1=:po_number and
(haou.name=:operating_unit or :operating_unit is null and pha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
pha.po_header_id=pla.po_header_id
)
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,'?')
Item
upper(msib.segment1) like upper(:item)
LOV
Invoice UOM
attribute15=:INV_UOM
LOV Oracle
Description
(msib.organization_id, msib.inventory_item_id) in (
select /*+ cardinality(msit 100)*/
msit.organization_id,
msit.inventory_item_id
from
mtl_system_items_tl msit
where
contains(msit.description,nvl(xxen_report.search_text(:description),'üüü'),1)>0 and
(:language is null or msit.language=(select flv.language_code from fnd_languages_vl flv where flv.description=:language))
)
Long Description
(msib.organization_id, msib.inventory_item_id) in (
select /*+ cardinality(msit 100)*/
msit.organization_id,
msit.inventory_item_id
from
mtl_system_items_tl msit
where
contains(msit.long_description,nvl(xxen_report.search_text(:long_description),'üüü'),2)>0 and
(:language is null or msit.language=(select flv.language_code from fnd_languages_vl flv where flv.description=:language))
)
Language
:language=:language
LOV
Item-Number contains
upper(msib.segment1) like '%'||upper(:item)||'%'
LOV
Cross Reference
msib.inventory_item_id in (
select
mcrb.inventory_item_id
from
mtl_cross_references_b mcrb
where
mcrb.cross_reference=:cross_reference and
mcrb.cross_reference_type=:cross_reference_type and
sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate)
)
LOV
BOM Item Type
msib.bom_item_type=:bom_item_type
LOV Oracle
BOM Status
(msib.inventory_item_id, msib.organization_id) in
(
select
bsb.assembly_item_id,
bsb.organization_id
from
bom_structures_b bsb
where
bsb.attribute15=:bom_status and
bsb.structure_type_id=1
)
LOV
BOM Exists
:bom_exists=:bom_exists and
msib.inventory_item_id in
(
select
bsb.assembly_item_id
from
bom_structures_b bsb
where
(:organization_name is null or bsb.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)) and
bsb.assembly_type=1
)
LOV Oracle
Routing Exists
:routing_exists=:routing_exists and
msib.inventory_item_id in
(
select
bor.assembly_item_id
from
bom_operational_routings bor
where
(:organization_name is null or bor.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name))
)
LOV Oracle
Item Creation Date from
msib.creation_date>=:creation_date_from
Date
Item Creation Date to
msib.creation_date<:creation_date_to+1
Date
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
Sales Order
msib.inventory_item_id in (
select
oola.inventory_item_id
from
hr_all_organization_units haou,
oe_order_headers_all ooha,
oe_order_lines_all oola
where
ooha.order_number=:order_number and
(haou.name=:operating_unit or :operating_unit is null and ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=ooha.org_id and
ooha.header_id=oola.header_id and
oola.item_type_code not in ('CLASS','MODEL','OPTION')
)
LOV
Sales Order Created By
msib.inventory_item_id in (
select
oola.inventory_item_id
from
fnd_user fu,
hr_all_organization_units haou,
oe_order_headers_all ooha,
oe_order_lines_all oola
where
fu.user_name=:order_created_by and
fu.user_id=ooha.created_by and
(haou.name=:operating_unit or :operating_unit is null and ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=ooha.org_id and
ooha.header_id=oola.header_id and
oola.item_type_code not in ('CLASS','MODEL','OPTION')
)
LOV
Project
msib.inventory_item_id in (
select
oola.inventory_item_id
from
pa_projects_all ppa,
hr_all_organization_units haou,
oe_order_lines_all oola
where
ppa.segment1=:project_number and
ppa.project_id=oola.project_id and
(haou.name=:operating_unit or :operating_unit is null and oola.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=oola.org_id and
oola.item_type_code not in ('CLASS','MODEL','OPTION')
)
LOV
Task
msib.inventory_item_id in (
select
oola.inventory_item_id
from
pa_projects_all ppa,
pa_tasks pt,
oe_order_lines_all oola
where
pt.task_number=:task_number and
ppa.segment1=:project_number and
ppa.project_id=pt.project_id and
ppa.project_id=oola.project_id and
pt.task_id=oola.task_id
)
LOV
Order Type
msib.inventory_item_id in (
select
oola.inventory_item_id
from
hr_all_organization_units haou,
oe_order_headers_all ooha,
oe_order_lines_all oola
where
ooha.order_type_id in (select ottt.transaction_type_id from oe_transaction_types_tl ottt where ottt.name=:order_type and ottt.language=userenv('lang')) and
(haou.name=:operating_unit or :operating_unit is null and ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=ooha.org_id and
ooha.header_id=oola.header_id and
oola.item_type_code not in ('CLASS','MODEL','OPTION')
)
LOV
Sched. Ship Date From
msib.inventory_item_id in (
select
oola.inventory_item_id
from
hr_all_organization_units haou,
oe_order_lines_all oola
where
oola.schedule_ship_date>=:schedule_ship_date_from and
(haou.name=:operating_unit or :operating_unit is null and oola.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=oola.org_id and
oola.item_type_code not in ('CLASS','MODEL','OPTION')
)
Date
Sched. Ship Date To
msib.inventory_item_id in (
select
oola.inventory_item_id
from
hr_all_organization_units haou,
oe_order_lines_all oola
where
oola.schedule_ship_date<:schedule_ship_date_to+1 and
(haou.name=:operating_unit or :operating_unit is null and oola.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=oola.org_id and
oola.item_type_code not in ('CLASS','MODEL','OPTION')
)
Date
Customer
msib.inventory_item_id in (
select
oola.inventory_item_id
from
hr_all_organization_units haou,
hz_parties hp,
hz_cust_accounts hca,
oe_order_headers_all ooha,
oe_order_lines_all oola
where
hp.party_name=:customer_name and
(haou.name=:operating_unit or :operating_unit is null and ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)) and
haou.organization_id=ooha.org_id and
hp.party_id=hca.party_id and
hca.cust_account_id=ooha.sold_to_org_id and
ooha.header_id=oola.header_id
)
LOV
Organization Name
msib.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)
LOV
Subinventory
msib.inventory_item_id in (
select
x.inventory_item_id
from
(
select
sum(moqd.primary_transaction_quantity) quantity,
moqd.inventory_item_id
from
mtl_onhand_quantities_detail moqd
where
moqd.subinventory_code=:secondary_inventory_name and
moqd.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)
having sum(moqd.primary_transaction_quantity)>0
group by
moqd.inventory_item_id
) x
)
LOV
Locator
msib.inventory_item_id in (
select
x.inventory_item_id
from
(
select
sum(moqd.primary_transaction_quantity) quantity,
moqd.inventory_item_id
from
mtl_onhand_quantities_detail moqd
where
moqd.locator_id in (
select
milk.inventory_location_id
from
mtl_item_locations_kfv milk
where
milk.concatenated_segments=:locator and
milk.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name) and
milk.subinventory_code=:secondary_inventory_name
) and
moqd.subinventory_code=:secondary_inventory_name and
moqd.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)
having sum(moqd.primary_transaction_quantity)>0
group by
moqd.inventory_item_id
) x
)
LOV
Inventory Items only
msib.inventory_item_flag='Y'
LOV Oracle
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