[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select transaction_temp_id, rowid
from mtl_material_transactions_temp
where transaction_header_id = x_txn_header_id
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION;
select transaction_interface_id, rowid
from mtl_transactions_interface
where transaction_header_id = x_txn_header_id
and transaction_source_type_id = 5
and transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION;
select mtl_material_transactions_s.nextval into l_temp_id
from sys.dual;
update mtl_transactions_interface
set transaction_interface_id = l_temp_id
where rowid = inv_rec.rowid;
select mtl_material_transactions_s.nextval into l_temp_id
from sys.dual;
update mtl_material_transactions_temp
set transaction_temp_id = l_temp_id
where rowid = inv_rec.rowid;
procedure get_message_stack(p_delete_stack in varchar2 := null,
p_separator in varchar2 := null,
p_msg OUT NOCOPY VARCHAR2) is
l_curMsg VARCHAR2(2000) := '';
if(fnd_api.to_boolean(nvl(p_delete_stack,fnd_api.g_true))) then
fnd_msg_pub.delete_msg;
if(fnd_api.to_boolean(nvl(p_delete_stack,fnd_api.g_true))) then
fnd_msg_pub.delete_msg;
/* Deletes transaction records from the mtl temp tables */
procedure delete_temp_records(p_header_id IN NUMBER) is
begin
-- Delete all serial numbers tied to lots
delete from mtl_serial_numbers_temp
where transaction_temp_id in
( select msnt.transaction_temp_id
from mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
where mmtt.transaction_header_id = p_header_id
and mtlt.transaction_temp_id = mmtt.transaction_temp_id
and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
);
delete from mtl_serial_numbers_temp
where transaction_temp_id in
( select msnt.transaction_temp_id
from mtl_serial_numbers_temp msnt,
mtl_material_transactions_temp mmtt
where mmtt.transaction_header_id = p_header_id
and mmtt.transaction_temp_id = msnt.transaction_temp_id
);
delete from mtl_transaction_lots_temp
where transaction_temp_id in
( select mtlt.transaction_temp_id
from mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
where mmtt.transaction_header_id = p_header_id
and mtlt.transaction_temp_id = mmtt.transaction_temp_id
);
delete from mtl_material_transactions_temp
where transaction_header_id = p_header_id;
end delete_temp_records;
/* Deletes transaction records from the mtl temp tables */
procedure delete_temp_records(p_temp_id IN NUMBER) is
begin
-- Delete all serial numbers tied to lots
delete from mtl_serial_numbers_temp msnt
where transaction_temp_id in
(select mtlt.serial_transaction_temp_id
from mtl_transaction_lots_temp mtlt
where mtlt.transaction_temp_id = p_temp_id
);
delete from mtl_serial_numbers_temp
where transaction_temp_id = p_temp_id;
delete from mtl_transaction_lots_temp
where transaction_temp_id = p_temp_id;
delete from mtl_material_transactions_temp
where transaction_header_id = p_temp_id;
end delete_temp_records;
procedure update_serial(p_serial_number in VARCHAR2,
p_inventory_item_id in number,
p_organization_id in number,
p_wip_entity_id in number,
p_line_mark_id in number := null,
p_operation_seq_num in number,
p_intraoperation_step_type in number,
x_return_status OUT NOCOPY VARCHAR2) is
l_objID NUMBER;
select current_status,
initialization_date,
completion_date,
ship_date,
revision,
lot_number,
group_mark_id,
lot_line_mark_id,
current_organization_id,
current_locator_id,--10
current_subinventory_code,
original_wip_entity_id,
original_unit_vendor_id,
vendor_lot_number,
vendor_serial_number,
last_receipt_issue_type,
last_txn_source_id,
last_txn_source_type_id,
last_txn_source_name,
parent_item_id,--20
parent_serial_number
into l_current_status,
l_initialization_date,
l_completion_date,
l_ship_date,
l_revision,
l_lot_number,
l_group_mark_id,
l_lot_line_mark_id,
l_current_organization_id,
l_current_locator_id,--10
l_current_subinventory_code,
l_original_wip_entity_id,
l_original_unit_vendor_id,
l_vendor_lot_number,
l_vendor_serial_number,
l_last_receipt_issue_type,
l_last_txn_source_id,
l_last_txn_source_type_id,
l_last_txn_source_name,
l_parent_item_id,--20
l_parent_serial_number
from mtl_serial_numbers
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id
for update nowait;
inv_serial_number_pub.updateserial(p_api_version => 1.0,
p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_serial_number => p_serial_number,
p_initialization_date => l_initialization_date,
p_completion_date => l_completion_date,
p_ship_date => l_ship_date,
p_revision => l_revision,
p_lot_number => l_lot_number,
p_current_locator_id => l_current_locator_id,
p_subinventory_code => l_current_subinventory_code,
p_trx_src_id => l_original_wip_entity_id,
p_unit_vendor_id => l_original_unit_vendor_id,
p_vendor_lot_number => l_vendor_lot_number,
p_vendor_serial_number => l_vendor_serial_number,
p_receipt_issue_type => l_last_receipt_issue_type,
p_txn_src_id => l_last_txn_source_id,
p_txn_src_name => l_last_txn_source_name,
p_txn_src_type_id => l_last_txn_source_type_id,
p_current_status => l_current_status,
p_parent_item_id => l_parent_item_id,
p_parent_serial_number => l_parent_serial_number,
p_serial_temp_id => null,
p_last_status => l_last_status,
p_status_id => null,
x_object_id => l_objID,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => p_operation_seq_num,
p_intraoperation_step_type => p_intraoperation_step_type,
p_line_mark_id => p_line_mark_id);
p_procedure_name => 'update_serial',
p_error_text => SQLERRM);
end update_serial;
p_delete_stack => fnd_api.g_false,
p_separator => ' ');
SELECT inv_lot_sel_attr.is_enabled(
'Lot Attributes',
p_org_id,
p_item_id)
INTO l_require_lot_attr
FROM dual
WHERE NOT EXISTS -- new lot
(SELECT 'X'
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number = p_lot_number);
SELECT msi.shelf_life_code
INTO l_shelf_life_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_org_id
AND NOT EXISTS -- new lot
(SELECT 'X'
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number = p_lot_number);
SELECT serial_number
FROM mtl_serial_numbers
WHERE current_organization_id = p_org_id
and wip_entity_id = p_we_id
AND inventory_item_id = p_item_id;
select organization_id, primary_item_id
into l_org_id, l_item_id
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
SELECT wip_entity_id, inventory_item_id
INTO l_wip_entity_id, l_item_id
FROM mtl_serial_numbers
WHERE current_organization_id = p_org_id
AND serial_number = p_serial_number
AND inventory_item_id = nvl(l_item_id, inventory_item_id);
SELECT we.wip_entity_id
INTO l_wip_entity_id
FROM mtl_serial_numbers msn,
wip_entities we,
mtl_object_genealogy mog
WHERE
((mog.genealogy_origin = 1 and
mog.parent_object_id = we.gen_object_id and
mog.object_id = msn.gen_object_id)
or
(mog.genealogy_origin = 2 and
mog.parent_object_id = msn.gen_object_id and
mog.object_id = we.gen_object_id))
and mog.end_date_active is null
and msn.serial_number = p_serial_number
and msn.current_organization_id = p_org_id;
SELECT wdj.lot_number,
wdj.scheduled_start_date,
DECODE(msi.revision_qty_control_code,
WIP_CONSTANTS.REV,
NVL(wdj.bom_revision,
BOM_revisions.GET_ITEM_REVISION_FN
('EXCLUDE_OPEN_HOLD',-- eco_status
'ALL', -- examine_type
p_org_id, -- org_id
l_item_id, -- item_id
l_sch_st_date) -- rev_date
),
NULL
)
INTO l_lot_number,
l_sch_st_date,
l_item_rev
FROM mtl_system_items msi,
wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = l_wip_entity_id
AND msi.organization_id = wdj.organization_id
AND msi.inventory_item_id = wdj.primary_item_id;