[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and exists (
select 1
from mtl_system_items msi
where msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and msi.build_in_wip_flag = 'Y'
and msi.pick_components_flag = 'N'
and eng_item_flag = decode( x_see_eng_item,
1, eng_item_flag,
'N') );
select 1 into x_success
from mtl_transactions_interface mti
where mti.rowid = p_rowid
and mti.accounting_class is not null
and ( ( mti.source_project_id is null
and exists (
select 'class is valid'
from cst_cg_wip_acct_classes_v cwac
where cwac.class_code = mti.accounting_class
and cwac.organization_id = mti.organization_id
and cwac.class_type = 1
)
)
or( mti.source_project_id is not null
and exists (
select 'class is valid'
from cst_cg_wip_acct_classes_v cwac,
mrp_project_parameters mpp, mtl_parameters mp
where cwac.class_code = mti.accounting_class
and cwac.class_type = 1
and mpp.project_id = mti.source_project_id
and mpp.organization_id = mti.organization_id
and mpp.organization_id = mp.organization_id
and cwac.cost_group_id = decode(mp.primary_cost_method,1,-1,mpp.costing_group_id)
and cwac.organization_id = mti.organization_id
)
)
);
select inventory_item_id,
organization_id,
revision,
bom_revision
into x_item_id,
x_org_id,
x_rev,
x_bom_rev
from mtl_transactions_interface
where rowid = p_rowid ;
/* select 1 into x_success
from mtl_transactions_interface mti,
mtl_system_items msi
where mti.rowid = p_rowid
and msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and ( (
msi.revision_qty_control_code = 2
and exists
(
select 1
from mtl_item_revisions mir
where mir.organization_id = mti.organization_id
and mir.inventory_item_id = mti.inventory_item_id
and mir.revision = mti.revision
)
)
or
(
msi.revision_qty_control_code = 1
and mti.revision is null
)
);
select 1 into x_success
from mtl_transactions_interface mti,
mtl_system_items msi
where mti.rowid = p_rowid
and msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and ( (
x_bom_rev_exists = 1
and exists
(
select 1
from mtl_item_revisions mir
where mir.organization_id = mti.organization_id
and mir.inventory_item_id = mti.inventory_item_id
and mir.revision = mti.bom_revision
)
)
or
(
x_bom_rev_exists = 0
and mti.bom_revision is null
)
);
select inventory_item_id,
organization_id,
transaction_date
into x_item_id,
x_org_id,
x_txn_date
from mtl_transactions_interface
where rowid = p_rowid ;
select 1 into x_success
from mtl_transactions_interface mti,
mtl_system_items msi
where mti.rowid = p_rowid
and msi.inventory_item_id = mti.inventory_item_id
and msi.organization_id = mti.organization_id
and ( (x_rtg_exists >= 1
and exists (
select 1
from mtl_rtg_item_revisions mrir
where mrir.organization_id = mti.organization_id
and mrir.inventory_item_id = mti.inventory_item_id
and mrir.process_revision = mti.routing_revision
and mrir.implementation_date is not null
)
)
or
(
x_rtg_exists = 0
and mti.routing_revision is null
)
);
select bom_revision,
bom_revision_date,
organization_id,
inventory_item_id
into x_bom_rev,
x_bom_rev_date,
x_org_id,
x_item_id
from mtl_transactions_interface
where rowid = p_rowid ;
select routing_revision,
routing_revision_date,
organization_id,
inventory_item_id,
transaction_date
into x_rtg_rev,
x_rtg_rev_date,
x_org_id,
x_item_id,
x_txn_date
from mtl_transactions_interface
where rowid = p_rowid ;
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and ( (alternate_bom_designator is null)
or (alternate_bom_designator is not null
and exists (
select 1
from bom_bill_alternates_v bba
where bba.alternate_bom_designator =
mti.alternate_bom_designator
and bba.organization_id = mti.organization_id
/* and bba.assembly_type = 1 */ /*changed condition to include engineering BOMs also as WOL transactions are currently supported for primary routings and erroring for alt routings due to this validation */
and bba.assembly_type in (1,2)
and bba.assembly_item_id = mti.inventory_item_id)));
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and ( (alternate_routing_designator is null)
or (alternate_routing_designator is not null
and exists (
select 1
from bom_routing_alternates_v bra
where bra.alternate_routing_designator =
mti.alternate_routing_designator
and bra.organization_id = mti.organization_id
/*and bra.routing_type = 1 *//*changed condition to include engineering routings also as WOL transactions are currently supported for primary routings and erroring for alt routings due to this validation*/
and bra.routing_type in (1,2)
and bra.assembly_item_id = mti.inventory_item_id)));
SELECT subinventory_code, transaction_action_id -- CFM Scrap Section
INTO x_subinv_code, x_txn_action
FROM mtl_transactions_interface mti
WHERE ROWID = p_rowid;
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and subinventory_code is not null
and exists (
(
select 1
from mtl_system_items msi
where mti.inventory_item_id = msi.inventory_item_id
and mti.organization_id = msi.organization_id
and msi.restrict_subinventories_code = 2
)
union (
select 1
from mtl_system_items msi, mtl_item_sub_val_v msvv
where mti.inventory_item_id = msi.inventory_item_id
and mti.organization_id = msi.organization_id
and msi.restrict_subinventories_code = 1
and msi.inventory_asset_flag = 'N'
and msvv.organization_id = mti.organization_id
and msvv.inventory_item_id = mti.inventory_item_id
and msvv.secondary_inventory_name =
mti.subinventory_code
)
union (
select 1
from mtl_system_items msi, mtl_item_sub_ast_trk_val_v msvv
where mti.inventory_item_id = msi.inventory_item_id
and mti.organization_id = msi.organization_id
and msi.restrict_subinventories_code = 1
and msi.inventory_asset_flag = 'Y'
and msvv.organization_id = mti.organization_id
and msvv.inventory_item_id = mti.inventory_item_id
and msvv.secondary_inventory_name =
mti.subinventory_code
)
);
SELECT locator_id, transaction_action_id -- CFM Scrap Section
INTO x_loc_id, x_txn_action
FROM mtl_transactions_interface mti
WHERE ROWID = p_rowid;
select 1 into x_success
from mtl_transactions_interface mti
where mti.rowid = p_rowid
and mti.subinventory_code is not null
and ( ( mti.locator_id is not null
and exists
(
select 1
from mtl_item_locations mil
where mil.inventory_location_id =
mti.locator_id
and mil.subinventory_code =
mti.subinventory_code
and mil.organization_id =
mti.organization_id
)
)
or
(
locator_id is null
)
);
select count(*) into x_proj_ref_exists
from mtl_transactions_interface mti
where mti.rowid = p_rowid
and source_project_id is not null ;
select 1 into x_success
from mtl_transactions_interface mti,
mtl_item_locations mil
where mti.rowid = p_rowid
and ( ( mti.locator_id is not null
and mil.inventory_location_id = mti.locator_id
and mil.organization_id = mti.organization_id
and mil.segment19 = mti.source_project_id
AND nvl(mil.segment20, -1)
= nvl(mti.source_task_id , -1)
)
or ( mti.locator_id is null
and mti.organization_id = mil.organization_id --fix for 4896646
and ( mil.segment1 is not null
or mil.segment2 is not null
or mil.segment3 is not null
or mil.segment4 is not null
or mil.segment5 is not null
or mil.segment6 is not null
or mil.segment7 is not null
or mil.segment8 is not null
or mil.segment9 is not null
or mil.segment10 is not null
or mil.segment11 is not null
or mil.segment12 is not null
or mil.segment13 is not null
or mil.segment14 is not null
or mil.segment15 is not null
or mil.segment16 is not null
or mil.segment17 is not null
or mil.segment18 is not null
or mil.segment19 is not null
or mil.segment20 is not null
)
)
) ;
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and ( ( demand_class is not null
and exists
(
select 1
from so_demand_classes_active_v sdca
where sdca.demand_class_code = mti.demand_class
)
)
or
(
demand_class is null
)
);
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and ( ( schedule_group is not null
and exists
( select 1
from wip_schedule_groups_val_v wsg
where wsg.schedule_group_id = mti.schedule_group
and wsg.organization_id = mti.organization_id
)
)
or (
schedule_group is null
)
) ;
SELECT
mti.build_sequence,
mti.transaction_source_id,
mti.organization_id,
mti.repetitive_line_id,
mti.schedule_group
INTO
x_build_sequence,
x_wip_entity_id,
x_organization_id,
x_line_id,
x_schedule_group_id
FROM mtl_transactions_interface mti
WHERE rowid = p_rowid;
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and ( ( repetitive_line_id is not null
and exists
(
select 1
from wip_lines_val_v wl
where wl.line_id = mti.repetitive_line_id
and wl.organization_id = mti.organization_id
)
)
or(
repetitive_line_id is null
)
);
select organization_id into l_org_id
from mtl_transactions_interface
where rowid = p_rowid;
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and ( ( source_project_id is not null
and exists (
select 1
from mtl_parameters mps
where nvl(mps.project_reference_enabled,2) = 1
and mps.organization_id = mti.organization_id
)
and exists (
select 1
from mtl_project_v mp
where mp.project_id = mti.project_id
)
)
or( source_project_id is null )
) ;
select 1 into x_success
from mtl_transactions_interface mti,
mtl_parameters mps
where mti.rowid = p_rowid
and mps.organization_id = mti.organization_id
and ( ( nvl(mps.project_control_level,1) = 2
and ( ( mti.source_project_id is not null
and mti.source_task_id is not null
and exists (
select 1
from pa_tasks_expend_v pt
where pt.project_id = mti.source_project_id
and pt.task_id = mti.source_task_id
)
)
or ( mti.source_project_id is null
and mti.source_task_id is null
)
)
)
or( nvl(mps.project_control_level,1) = 1 )
);
select 1 into x_success
from dual
where p_status in (
select lookup_code
from mfg_lookups
where lookup_type = 'WIP_FLOW_SCHEDULE_STATUS');
As we are inserting the flow schedules one after
the other into wip_flow_schedules, we don't have
to check for duplicate schedule_number in MTI
itself as this will error out by itself
***************************************************/
select 1 into x_success
from mtl_transactions_interface mti
where mti.rowid = p_rowid
and((mti.scheduled_flag <> 1
and not exists(
select 'exists'
from wip_entities
where wip_entity_name = mti.schedule_number))
or(mti.scheduled_flag = 1
and exists(
select 'exists'
from wip_entities
where wip_entity_id = mti.transaction_source_id)));
As we are inserting the flow schedules one after
the other into wip_flow_schedules, we don't have
to check for duplicate schedule_number in MTI
itself as this will error out by itself
***************************************************/
select 1 into x_success
from sys.dual
where not exists(
select 'exists'
from wip_entities
where wip_entity_name = p_schedule_number );
select 1 into x_success
from mtl_transactions_interface mti
where rowid = p_rowid
and mti.scheduled_flag in (
select lookup_code
from mfg_lookups
where lookup_type = 'SYS_YES_NO');
SELECT 1
INTO x_success
FROM mtl_transactions_interface mti
WHERE rowid = p_rowid
AND ((end_item_unit_number IS NULL
AND (pjm_unit_eff.enabled = 'N'
OR pjm_unit_eff.unit_effective_item(mti.inventory_item_id,mti.organization_id) = 'N'))
OR (end_item_unit_number IS NOT NULL
AND (pjm_unit_eff.enabled = 'Y'
AND pjm_unit_eff.unit_effective_item(mti.inventory_item_id,mti.organization_id) = 'Y'
AND end_item_unit_number IN (SELECT unit_number
FROM pjm_unit_numbers_lov_v N, mtl_parameters P
WHERE P.organization_id = mti.organization_id and
P.master_organization_id = N.master_organization_id)
AND (mti.scheduled_flag <> 1
OR exists (SELECT 1
FROM wip_flow_schedules wfs
WHERE wfs.wip_entity_id = mti.transaction_source_id
AND wfs.end_item_unit_number = mti.end_item_unit_number
)))));