| Parameter Name | SQL text | Validation |
|---|
| Organization | msib.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:org_code) |
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)
) |
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 |
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) |
msib.planning_make_buy_code=xxen_util.lookup_code(:make_or_buy,'MTL_PLANNING_MAKE_BUY',700) | LOV |
| Pegging | msib.end_assembly_pegging_flag=xxen_util.lookup_code(:pegging,'ASSEMBLY_PEGGING_CODE',0) |
msib.end_assembly_pegging_flag=xxen_util.lookup_code(:pegging,'ASSEMBLY_PEGGING_CODE',0) | 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
) |
: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
) |
: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 |
msib.bom_item_type=:bom_item_type | LOV Oracle |
| Inventory Items only | msib.inventory_item_flag='Y' |
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) |
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) |
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)
) |
(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 |
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 | LOV Oracle |
| Compression exists | (case when xxen_schub.get_planning_source='MSC' then xxen_schub.get_max_compression_days(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,msib.organization_id,xxen_schub.get_msc_inventory_item_id(msib.inventory_item_id)) else xxen_schub.get_max_compression_days(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,msib.organization_id,msib.inventory_item_id) end)>0 |
(case when xxen_schub.get_planning_source='MSC' then xxen_schub.get_max_compression_days(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,msib.organization_id,xxen_schub.get_msc_inventory_item_id(msib.inventory_item_id)) else xxen_schub.get_max_compression_days(xxen_schub.get_planning_instance_id,xxen_schub.get_plan_id,xxen_schub.get_plan_name,msib.organization_id,msib.inventory_item_id) end)>0 | LOV Oracle |
| Extended Qty | | Number |
