The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW_BULK (
p_mtl_ser_txn_if_rec IN WSH_SHIP_CONFIRM_ACTIONS.mtl_ser_txn_if_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW_BULK';
INSERT INTO mtl_serial_numbers_interface (
source_code,
source_line_id,
transaction_interface_id,
fm_serial_number,
to_serial_number,
process_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
attribute_category, -- Bug 3628620
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
--Bugfix 8467875 Start
cycles_since_mark,
cycles_since_new,
cycles_since_overhaul,
cycles_since_repair,
cycles_since_visit,
c_attribute1,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute2,
c_attribute20,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
d_attribute1,
d_attribute10,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
number_of_repairs,
n_attribute1,
n_attribute10,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
territory_code,
time_since_mark,
time_since_new,
time_since_overhaul,
time_since_repair,
time_since_visit,
serial_attribute_category
--Bugfix 8467875 End
) VALUES (
p_mtl_ser_txn_if_rec.source_code(i),
p_mtl_ser_txn_if_rec.source_line_id(i),
p_mtl_ser_txn_if_rec.transaction_interface_id(i),
p_mtl_ser_txn_if_rec.fm_serial_number(i),
p_mtl_ser_txn_if_rec.to_serial_number(i),
1,
SYSDATE,
g_userid,
SYSDATE,
g_userid,
p_mtl_ser_txn_if_rec.attribute_category(i), -- Bug 3628620
p_mtl_ser_txn_if_rec.attribute1(i),
p_mtl_ser_txn_if_rec.attribute2(i),
p_mtl_ser_txn_if_rec.attribute3(i),
p_mtl_ser_txn_if_rec.attribute4(i),
p_mtl_ser_txn_if_rec.attribute5(i),
p_mtl_ser_txn_if_rec.attribute6(i),
p_mtl_ser_txn_if_rec.attribute7(i),
p_mtl_ser_txn_if_rec.attribute8(i),
p_mtl_ser_txn_if_rec.attribute9(i),
p_mtl_ser_txn_if_rec.attribute10(i),
p_mtl_ser_txn_if_rec.attribute11(i),
p_mtl_ser_txn_if_rec.attribute12(i),
p_mtl_ser_txn_if_rec.attribute13(i),
p_mtl_ser_txn_if_rec.attribute14(i),
p_mtl_ser_txn_if_rec.attribute15(i),
--Bugfix 8467875 Start
p_mtl_ser_txn_if_rec.cycles_since_mark(i),
p_mtl_ser_txn_if_rec.cycles_since_new(i),
p_mtl_ser_txn_if_rec.cycles_since_overhaul(i),
p_mtl_ser_txn_if_rec.cycles_since_repair(i),
p_mtl_ser_txn_if_rec.cycles_since_visit(i),
p_mtl_ser_txn_if_rec.c_attribute1(i),
p_mtl_ser_txn_if_rec.c_attribute10(i),
p_mtl_ser_txn_if_rec.c_attribute11(i),
p_mtl_ser_txn_if_rec.c_attribute12(i),
p_mtl_ser_txn_if_rec.c_attribute13(i),
p_mtl_ser_txn_if_rec.c_attribute14(i),
p_mtl_ser_txn_if_rec.c_attribute15(i),
p_mtl_ser_txn_if_rec.c_attribute16(i),
p_mtl_ser_txn_if_rec.c_attribute17(i),
p_mtl_ser_txn_if_rec.c_attribute18(i),
p_mtl_ser_txn_if_rec.c_attribute19(i),
p_mtl_ser_txn_if_rec.c_attribute2(i),
p_mtl_ser_txn_if_rec.c_attribute20(i),
p_mtl_ser_txn_if_rec.c_attribute3(i),
p_mtl_ser_txn_if_rec.c_attribute4(i),
p_mtl_ser_txn_if_rec.c_attribute5(i),
p_mtl_ser_txn_if_rec.c_attribute6(i),
p_mtl_ser_txn_if_rec.c_attribute7(i),
p_mtl_ser_txn_if_rec.c_attribute8(i),
p_mtl_ser_txn_if_rec.c_attribute9(i),
p_mtl_ser_txn_if_rec.d_attribute1(i),
p_mtl_ser_txn_if_rec.d_attribute10(i),
p_mtl_ser_txn_if_rec.d_attribute2(i),
p_mtl_ser_txn_if_rec.d_attribute3(i),
p_mtl_ser_txn_if_rec.d_attribute4(i),
p_mtl_ser_txn_if_rec.d_attribute5(i),
p_mtl_ser_txn_if_rec.d_attribute6(i),
p_mtl_ser_txn_if_rec.d_attribute7(i),
p_mtl_ser_txn_if_rec.d_attribute8(i),
p_mtl_ser_txn_if_rec.d_attribute9(i),
p_mtl_ser_txn_if_rec.number_of_repairs(i),
p_mtl_ser_txn_if_rec.n_attribute1(i),
p_mtl_ser_txn_if_rec.n_attribute10(i),
p_mtl_ser_txn_if_rec.n_attribute2(i),
p_mtl_ser_txn_if_rec.n_attribute3(i),
p_mtl_ser_txn_if_rec.n_attribute4(i),
p_mtl_ser_txn_if_rec.n_attribute5(i),
p_mtl_ser_txn_if_rec.n_attribute6(i),
p_mtl_ser_txn_if_rec.n_attribute7(i),
p_mtl_ser_txn_if_rec.n_attribute8(i),
p_mtl_ser_txn_if_rec.n_attribute9(i),
p_mtl_ser_txn_if_rec.territory_code(i),
p_mtl_ser_txn_if_rec.time_since_mark(i),
p_mtl_ser_txn_if_rec.time_since_new(i),
p_mtl_ser_txn_if_rec.time_since_overhaul(i),
p_mtl_ser_txn_if_rec.time_since_repair(i),
p_mtl_ser_txn_if_rec.time_since_visit(i),
p_mtl_ser_txn_if_rec.serial_attribute_category(i)
--Bugfix 8467875 End
);
WSH_DEBUG_SV.log(l_module_name,'Rows inserted in mtl_serial_numbers_interface',SQL%ROWCOUNT);
END insert_row_bulk;
PROCEDURE Insert_Row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_trx_interface_id IN OUT NOCOPY NUMBER,
p_source_code IN VARCHAR2,
p_source_line_id IN NUMBER,
p_fm_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER DEFAULT NULL,
p_request_id IN NUMBER DEFAULT NULL,
p_program_application_id IN NUMBER DEFAULT NULL,
p_program_id IN NUMBER DEFAULT NULL,
p_program_update_date IN DATE DEFAULT NULL,
p_parent_serial_number IN VARCHAR2 DEFAULT NULL,
p_vendor_serial_number IN VARCHAR2 DEFAULT NULL,
p_vendor_lot_number IN VARCHAR2 DEFAULT NULL,
p_error_code IN VARCHAR2 DEFAULT NULL,
p_process_flag IN NUMBER DEFAULT 1)
IS
CURSOR row_id IS
SELECT rowid FROM mtl_serial_numbers_interface
WHERE transaction_interface_id = x_trx_interface_id
AND NVL(fm_serial_number,'-1') =
NVL(p_fm_serial_number, NVL(fm_serial_number,'-1'))
AND NVL(to_serial_number,'-1') =
NVL(p_to_serial_number, NVL(to_serial_number,'-1'));
SELECT mtl_material_transactions_s.nextval
FROM sys.dual;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
'START',
'Start of procedure INSERT_ROW, input parameters:
source_code='||p_source_code||
',source_line_id='||to_char(p_source_line_id)||
', transaction_interface_id='||to_char(x_trx_interface_id)||
', fm_serial_number='||p_fm_serial_number||
', to_serial_number='||p_to_serial_number||
', creation_date='||p_creation_date||
', created_by='||to_char(p_created_by));
', last_updated_by='||to_char(p_last_updated_by)||
', last_update_date='||p_last_update_date||
', last_update_login='||p_last_update_login||
', request_id='||p_request_id||
', program_application_id='||p_program_application_id||
', program_id='||p_program_id||
', program_update_date='||p_program_update_date||
', parent_serial_number='||p_parent_serial_number||
', vendor_serial_number='||p_vendor_serial_number||
', vendor_lot_number='||p_vendor_lot_number||
', error_code='||p_error_code||
', process_flag='||to_char(p_process_flag) );
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
'END',
'Insert failed. From serial number is NULL.
Raising WSH_FM_SERIALNO_NULL');
INSERT INTO mtl_serial_numbers_interface (
source_code,
source_line_id,
transaction_interface_id,
fm_serial_number,
to_serial_number,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
parent_serial_number,
vendor_serial_number,
vendor_lot_number,
error_code,
process_flag
) VALUES (
p_source_code,
p_source_line_id,
x_trx_interface_id,
p_fm_serial_number,
p_to_serial_number,
NVL(p_creation_date,SYSDATE),
NVL(p_created_by,g_userid),
NVL(p_last_updated_by,g_userid),
NVL(p_last_update_date,SYSDATE),
p_last_update_login,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_parent_serial_number,
p_vendor_serial_number,
p_vendor_lot_number,
p_error_code,
p_process_flag
);
WSH_DEBUG_SV.log(l_module_name,'Rows inserted',SQL%ROWCOUNT);
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
'END',
'No rowid found. Raising NO_DATA_FOUND.');
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
'END',
'End of procedure INSERT_ROW');
END Insert_Row;
PROCEDURE Update_Row (
x_rowid IN OUT NOCOPY VARCHAR2,
p_trx_interface_id IN NUMBER,
p_source_code IN VARCHAR2,
p_source_line_id IN NUMBER,
p_fm_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER DEFAULT NULL,
p_request_id IN NUMBER DEFAULT NULL,
p_program_application_id IN NUMBER DEFAULT NULL,
p_program_id IN NUMBER DEFAULT NULL,
p_program_update_date IN DATE DEFAULT NULL,
p_parent_serial_number IN VARCHAR2 DEFAULT NULL,
p_vendor_serial_number IN VARCHAR2 DEFAULT NULL,
p_vendor_lot_number IN VARCHAR2 DEFAULT NULL,
p_error_code IN VARCHAR2 DEFAULT NULL,
p_process_flag IN NUMBER DEFAULT 1)
IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
'START',
'Start of procedure UPDATE_ROW, input parameters:
source_code='||p_source_code||
', source_line_id='||p_source_line_id||
', transaction_interface_id='||p_trx_interface_id||
', fm_serial_number='||p_fm_serial_number||
', to_serial_number='||p_to_serial_number);
', last_updated_by='||p_last_updated_by||
', last_update_date='||p_last_update_date||
', last_update_login='||p_last_update_login||
', request_id='||p_request_id||
', program_application_id='||p_program_application_id||
', program_id='||p_program_id||
', program_update_date='||p_program_update_date||
', parent_serial_number='||p_parent_serial_number||
', vendor_serial_number='||p_vendor_serial_number||
', vendor_lot_number='||p_vendor_lot_number||
', error_code='||p_error_code||', process_flag='||p_process_flag );
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
'END',
'UPDATE failed. From serial number is NULL.
Raising WSH_FM_SERIALNO_NULL');
UPDATE mtl_serial_numbers_interface SET
source_code = p_source_code,
source_line_id = p_source_line_id,
transaction_interface_id = p_trx_interface_id,
fm_serial_number = p_fm_serial_number,
to_serial_number = p_to_serial_number,
last_updated_by = NVL(p_last_updated_by,g_userid),
last_update_date = NVL(p_last_update_date,SYSDATE),
last_update_login = p_last_update_login,
request_id = p_request_id,
program_application_id = p_program_application_id,
program_id = p_program_id,
program_update_date = p_program_update_date,
parent_serial_number = p_parent_serial_number,
vendor_serial_number = p_vendor_serial_number,
vendor_lot_number = p_vendor_lot_number,
error_code = p_error_code,
process_flag = p_process_flag
WHERE rowid = x_rowid;
WSH_DEBUG_SV.log(l_module_name,'Rows Updated',SQL%ROWCOUNT);
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
'END',
'No rows updated. Raising NO_DATA_FOUND.');
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
'END',
'End of procedure UPDATE_ROW');
END Update_Row;
PROCEDURE Delete_Row (
x_rowid IN OUT NOCOPY VARCHAR2 )
IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.DELETE_ROW',
'START',
'Start of procedure DELETE_ROW');
DELETE FROM mtl_serial_numbers_interface WHERE rowid = x_rowid;
WSH_DEBUG_SV.log(l_module_name,'Rows deleted',SQL%ROWCOUNT);
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.DELETE_ROW',
'END',
'No rows deleted. Raising NO_DATA_FOUND');
/* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.DELETE_ROW',
'END',
'End of procedure DELETE_ROW');
END Delete_Row;
SELECT * FROM mtl_serial_numbers_interface
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
'Lock record failed. Raising exception FORM_RECORD_DELETED');
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
WSH_DEBUG_SV.pop(l_module_name,'FORM_RECORD_DELETED');