XXEN_SCHUB_ITEM_SEARCH

msiv.concatenated_segments=:item

(msiv.organization_id, msiv.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))
)

(msiv.organization_id, msiv.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

upper(msiv.concatenated_segments) like '%'||upper(:item_number_contains)||'%'

msiv.item_type=xxen_util.lookup_code(:item_type,'ITEM_TYPE',3)

not (msiv.item_type=xxen_util.lookup_code(:exclude_item_type,'ITEM_TYPE',3))

msiv.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)

(msiv.organization_id, msiv.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)

(msiv.organization_id, msiv.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
)

msiv.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)
)

msiv.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)
)

msiv.bom_item_type=:bom_item_type

(msiv.inventory_item_id, msiv.organization_id) in
(
select
bsb.assembly_item_id,
bsb.organization_id
from
bom_structures_b bsb
where
bsb.alternate_bom_designator=:bom_alternate_designator and
bsb.structure_type_id=1
)

:bom_exists=:bom_exists and
(msiv.inventory_item_id,msiv.organization_id) in
(
select
bsb.assembly_item_id,
bsb.organization_id
from
org_access_view oav,
bom_structures_b bsb
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = bsb.organization_id and
bsb.assembly_type=1
union
select
grvr.inventory_item_id,
oav.organization_id
from
org_access_view oav,
gmd_recipe_validity_rules grvr,
gmd_recipes gr,
fm_form_mst ffm
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = nvl(grvr.organization_id,oav.organization_id) and
grvr.recipe_id = gr.recipe_id and
gr.formula_id = ffm.formula_id and
grvr.validity_rule_status in (700,900) and
grvr.delete_mark = 0 and
grvr.recipe_use in (0,1) and
trunc(sysdate) between grvr.start_date and nvl(grvr.end_date, trunc(sysdate)+1) and
gr.recipe_status IN (700,900)
)

:routing_exists=:routing_exists and
(msiv.inventory_item_id,msiv.organization_id) in
(
select
bor.assembly_item_id,
bor.organization_id
from
org_access_view oav,
bom_operational_routings bor
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = bor.organization_id
union
select
grvr.inventory_item_id,
oav.organization_id
from
org_access_view oav,
gmd_recipe_validity_rules grvr,
gmd_recipes gr,
fm_form_mst ffm,
fm_rout_hdr frh
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = nvl(grvr.organization_id,oav.organization_id) and
grvr.recipe_id = gr.recipe_id and
gr.formula_id = ffm.formula_id and
gr.routing_id = frh.routing_id and
grvr.validity_rule_status in (700,900) and
grvr.delete_mark = 0 and
grvr.recipe_use in (0,1) and
trunc(sysdate) between grvr.start_date and nvl(grvr.end_date, trunc(sysdate)+1) and
gr.recipe_status IN (700,900) and
frh.delete_mark = 0
)

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

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

msiv.inventory_item_flag='Y'

msiv.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_by)

msiv.creation_date>=:creation_date_from

msiv.creation_date<:creation_date_to+1

msiv.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)
)

msiv.planner_code=:planner_code

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

exists
(
select
 null
from
 mtl_onhand_quantities moq
where
    moq.organization_id = msiv.organization_id
and moq.inventory_item_id = msiv.inventory_item_id
group by
 moq.organization_id
,moq.inventory_item_id
having
 sum(moq.transaction_quantity) > 0
)

msiv.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
)

(msiv.organization_id,msiv.inventory_item_id) in
(
select
 ms.to_organization_id,ms.item_id
from
 mtl_supply            ms
,po_line_locations_all polla
where
    :po_exists is not null
and ms.po_line_location_id     = polla.line_location_id
and ms.supply_type_code       in ('PO','RECEIVING')
and ms.destination_type_code   = 'INVENTORY'
and ms.po_line_location_id    is not null
and ms.quantity                > 0
and nvl(polla.cancel_flag,'N') = 'N'
and (   :organization_name is null
     or ms.to_organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)
    )
and (   (:operating_unit is null and polla.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat))
     or polla.org_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:operating_unit)
    )
)

msiv.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')
)

msiv.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')
)

(msiv.organization_id,msiv.inventory_item_id) in
(
select
 md.organization_id,md.inventory_item_id
from
 mtl_demand          md
,oe_order_lines_all  ool
where
        :so_exists is not null
and md.demand_source_line = ool.line_id
and md.demand_source_type = 2 -- sales order
and md.line_item_quantity > nvl(md.completed_quantity,0)
and ool.open_flag         = 'Y'
and nvl(ool.cancelled_flag,'N') = 'N'
and
(   :organization_name is null
 or md.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)
)
and 
(   (:operating_unit is null and ool.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat))
 or ool.org_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:operating_unit)
)
)

msiv.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')
)

msiv.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
)

msiv.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')
)

msiv.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')
)

msiv.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')
)

msiv.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
)

:p_supplier_name is not null and
(msiv.organization_id,msiv.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_supplier_item_list(:p_supplier_name)) x
)

:operating_unit=:operating_unit

:planned_orgs_only is not null and msiv.organization_id in (select xspov.organization_id from xxen_schub_plan_orgs_v xspov where xspov.sr_instance_id=xxen_schub.get_planning_instance_id and xspov.compile_designator = xxen_schub.get_plan_name and nvl(xspov.plan_id,-99) = nvl(xxen_schub.get_plan_id,-99))

msiv.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name)

(msiv.inventory_item_id,msiv.organization_id) in (
select
x.inventory_item_id,
x.organization_id
from
(
select
sum(moqd.primary_transaction_quantity) quantity,
moqd.inventory_item_id,
moqd.organization_id
from
mtl_onhand_quantities_detail moqd
where
moqd.subinventory_code=:secondary_inventory_name and
(:organization_name is null or moqd.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name))
group by
moqd.inventory_item_id,
moqd.organization_id
having 
sum(moqd.primary_transaction_quantity)>0
) x
)

(msiv.inventory_item_id,msiv.organization_id) in (
select
x.inventory_item_id,
x.organization_id
from
(
select
sum(moqd.primary_transaction_quantity) quantity,
moqd.inventory_item_id,
moqd.organization_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
(:organization_name is null or 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
(:organization_name is null or moqd.organization_id in (select haou.organization_id from hr_all_organization_units haou where haou.name=:organization_name))
group by
moqd.inventory_item_id,
moqd.organization_id
having 
sum(moqd.primary_transaction_quantity)>0
) x
)

:exception_message is not null and
(msiv.organization_id,msiv.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
 )

:exception_exists='Y' and
(msiv.organization_id,msiv.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
 )

:mrp_msc_order_type is not null and
(msiv.organization_id,msiv.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_order_type_item_list(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,null,:mrp_msc_order_type,:p_action)) x
)

:p_action=:p_action

:planned_order_exists='Y' and
(msiv.organization_id,msiv.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_order_type_item_list(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,null,xxen_util.meaning('5','MRP_ORDER_TYPE',700),null)) x
)

:planned_order_date_to is not null and
(msiv.organization_id,msiv.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_planned_date_to_item_list(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,null,:planned_order_date_to)) x
)

(msiv.inventory_item_id,msiv.organization_id) in
(
select
we.primary_item_id,
we.organization_id
from
org_access_view oav,
wip_entities we
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = we.organization_id and
we.wip_entity_name=:job_name
)

(msiv.inventory_item_id,msiv.organization_id) in
(
select /*+ cardinality(wdj 10)*/
we.primary_item_id,
we.organization_id
from
org_access_view oav,
wip_discrete_jobs wdj,
wip_entities we
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = wdj.organization_id and
wdj.status_type=to_number(xxen_util.lookup_code(:wip_status,'WIP_JOB_STATUS',700)) and
wdj.wip_entity_id=we.wip_entity_id and
wdj.organization_id=we.organization_id and
we.entity_type=1
union
select
we.primary_item_id,
we.organization_id
from
org_access_view oav,
gme_batch_header gbh,
wip_entities we
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = gbh.organization_id and
gbh.batch_status=to_number(xxen_util.lookup_code(:wip_status,'GME_BATCH_STATUS',553)) and
gbh.batch_id=we.wip_entity_id and
gbh.organization_id=we.organization_id and
we.entity_type=10
)

(msiv.inventory_item_id,msiv.organization_id) in
(
select
we.primary_item_id,
we.organization_id
from
org_access_view oav,
wip_discrete_jobs wdj,
wip_entities we
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = wdj.organization_id and
wdj.scheduled_start_date<:wip_start_date_to+1 and
wdj.wip_entity_id=we.wip_entity_id and
wdj.organization_id=we.organization_id and
we.entity_type=1 and
wdj.status_type=1
union
select
we.primary_item_id,
we.organization_id
from
org_access_view oav,
gme_batch_header gbh,
wip_entities we
where
oav.resp_application_id = fnd_global.resp_appl_id and
oav.responsibility_id = fnd_global.resp_id and
(:organization_name is null or oav.organization_name = :organization_name) and
oav.organization_id = gbh.organization_id and
gbh.plan_start_date<:wip_start_date_to+1 and
gbh.batch_id=we.wip_entity_id and
gbh.organization_id=we.organization_id and
we.entity_type=10 and
gbh.batch_status=1
)

(msiv.inventory_item_id,msiv.organization_id) in
(select mss.inventory_item_id, mss.organization_id
 from
   (select distinct
     mss.inventory_item_id,
     mss.organization_id,
     max(mss.safety_stock_quantity) keep (dense_rank last order by mss.effectivity_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock_quantity
    from
     mtl_safety_stocks mss
    where
     mss.effectivity_date<=sysdate
   ) mss
 where mss.safety_stock_quantity>=:safety_stock_quantity_from
)