[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* --- When instance_id is passed then select from csi_inst_trx_details_v --- */
/* --- else from csi_transactions --- */
/* ---------------------------------------------------------------------------------- */
PROCEDURE gen_select(
p_txnfind_rec IN csi_datastructures_pub.transaction_query_rec,
x_select_cl OUT NOCOPY VARCHAR2
)
IS
l_table_name VARCHAR2(30);
x_select_cl := 'SELECT transaction_id,transaction_date, source_transaction_date, '||
'transaction_type_id, source_group_ref_id, source_group_ref, source_header_ref_id, '||
'source_header_ref, source_line_ref_id, source_line_ref, source_dist_ref_id1, '||
'source_dist_ref_id2, inv_material_transaction_id, transaction_quantity, '||
'transaction_uom_code, transacted_by, transaction_status_code, '||
'transaction_action_code, message_id, context, attribute1, attribute2, attribute3, '||
'attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, '||
'attribute11, attribute12, attribute13, attribute14, attribute15, '||
'object_version_number, txn_sub_type_id, transaction_status_code, split_reason_code, '||
'last_updated_by FROM '||l_table_name;
END gen_select;
SELECT instr(p_rec_item, '%', 1, 1)
FROM dual;
SELECT instr(p_rec_item, '_', 1, 1)
FROM dual;
l_select_cl VARCHAR2(2000) := '';
gen_select(l_crit_transaction_rec,l_select_cl);
dbms_sql.parse(l_cur_get_transactions, l_select_cl|| l_transactions_where || l_order_by_cl , dbms_sql.native);
SELECT user_name
INTO l_txn_rec.txn_user_name
FROM fnd_user
WHERE user_id = l_txn_rec.txn_user_id;
SELECT source_txn_type_name
INTO l_txn_rec.transaction_type_name
FROM csi_txn_types
WHERE transaction_type_id = l_txn_rec.transaction_type_id;
SELECT name
INTO l_txn_rec.txn_sub_type_name
FROM csi_txn_sub_types
WHERE transaction_type_id = l_txn_rec.transaction_type_id
AND sub_type_id = l_txn_rec.txn_sub_type_id;
SELECT application_name
INTO l_txn_rec.source_application_name
FROM fnd_application_vl
WHERE application_id IN (SELECT source_application_id
FROM csi_txn_types
WHERE transaction_type_id = l_txn_rec.transaction_type_id);
SELECT meaning
INTO l_txn_rec.transaction_status_name
FROM csi_lookups
WHERE lookup_code = l_txn_rec.transaction_status_code
AND lookup_type = 'CSI_TRANSACTION_STATUS_CODE';
SELECT 'x'
INTO l_dummy
FROM csi_transactions
WHERE transaction_id=p_transaction_rec.transaction_id
AND rownum=1;
SELECT 'x'
INTO l_dummy
FROM csi_transactions
WHERE transaction_id=p_transaction_rec.transaction_id
AND rownum=1;
SELECT SYSDATE
INTO l_transaction_date
FROM dual;
csi_transactions_pkg.insert_row(
px_transaction_id => p_transaction_rec.transaction_id,
p_transaction_date => l_transaction_date,
p_source_transaction_date => p_transaction_rec.source_transaction_date,
p_transaction_type_id => p_transaction_rec.transaction_type_id,
p_txn_sub_type_id => p_transaction_rec.txn_sub_type_id,
p_source_group_ref_id => p_transaction_rec.source_group_ref_id,
p_source_group_ref => p_transaction_rec.source_group_ref,
p_source_header_ref_id => p_transaction_rec.source_header_ref_id,
p_source_header_ref => p_transaction_rec.source_header_ref,
p_source_line_ref_id => p_transaction_rec.source_line_ref_id,
p_source_line_ref => p_transaction_rec.source_line_ref,
p_source_dist_ref_id1 => p_transaction_rec.source_dist_ref_id1,
p_source_dist_ref_id2 => p_transaction_rec.source_dist_ref_id2,
p_inv_material_transaction_id => p_transaction_rec.inv_material_transaction_id,
p_transaction_quantity => p_transaction_rec.transaction_quantity,
p_transaction_uom_code => p_transaction_rec.transaction_uom_code,
p_transacted_by => p_transaction_rec.transacted_by,
p_transaction_status_code => p_transaction_rec.transaction_status_code,
p_transaction_action_code => p_transaction_rec.transaction_action_code,
p_message_id => p_transaction_rec.message_id,
p_context => p_transaction_rec.context,
p_attribute1 => p_transaction_rec.attribute1,
p_attribute2 => p_transaction_rec.attribute2,
p_attribute3 => p_transaction_rec.attribute3,
p_attribute4 => p_transaction_rec.attribute4,
p_attribute5 => p_transaction_rec.attribute5,
p_attribute6 => p_transaction_rec.attribute6,
p_attribute7 => p_transaction_rec.attribute7,
p_attribute8 => p_transaction_rec.attribute8,
p_attribute9 => p_transaction_rec.attribute9,
p_attribute10 => p_transaction_rec.attribute10,
p_attribute11 => p_transaction_rec.attribute11,
p_attribute12 => p_transaction_rec.attribute12,
p_attribute13 => p_transaction_rec.attribute13,
p_attribute14 => p_transaction_rec.attribute14,
p_attribute15 => p_transaction_rec.attribute15,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => p_transaction_rec.object_version_number,
p_split_reason_code => p_transaction_rec.split_reason_code,
p_gl_interface_status_code => p_transaction_rec.gl_interface_status_code
);
/* --- This procedure is used to update transactions --- */
/* --- and call table handler after performing all the validations --- */
/* ---------------------------------------------------------------------------------- */
PROCEDURE update_transactions(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_transaction_rec IN csi_datastructures_pub.transaction_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR lock_csr (p_transaction_rec IN csi_datastructures_pub.transaction_rec) IS
SELECT object_version_number
FROM csi_transactions
WHERE transaction_id = p_transaction_rec.transaction_id
FOR UPDATE OF object_version_number;
l_api_name CONSTANT VARCHAR2(30) := 'update_transactions';
l_last_update_date DATE;
SAVEPOINT update_transactions_pvt;
CSI_gen_utility_pvt.put_line( 'update_transactions');
p_validation_mode => 'UPDATE',
p_transaction_id => p_transaction_rec.transaction_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_validation_mode => 'UPDATE',
p_object_version_number => p_transaction_rec.object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
csi_transactions_pkg.update_row(
p_transaction_id => p_transaction_rec.transaction_id,
p_transaction_status_code => p_transaction_rec.transaction_status_code,
p_transaction_action_code => p_transaction_rec.transaction_action_code,
p_source_group_ref => p_transaction_rec.source_group_ref,
p_source_group_ref_id => p_transaction_rec.source_group_ref_id,
p_source_dist_ref_id2 => p_transaction_rec.source_dist_ref_id2,
p_source_header_ref => p_transaction_rec.source_header_ref,
p_source_header_ref_id => p_transaction_rec.source_header_ref_id,
p_source_line_ref => p_transaction_rec.source_line_ref,
p_source_line_ref_id => p_transaction_rec.source_line_ref_id,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => p_transaction_rec.object_version_number,
p_gl_interface_status_code => p_transaction_rec.gl_interface_status_code);
ROLLBACK TO update_transactions_pvt;
ROLLBACK TO update_transactions_pvt;
ROLLBACK TO update_transactions_pvt;
END update_transactions;
/* --- This procedure is used to insert records into csi_txn_errors --- */
/* ---------------------------------------------------------------------------------- */
PROCEDURE create_txn_error
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_txn_error_rec IN csi_datastructures_pub.transaction_error_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_transaction_error_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_txn_error';
csi_txn_errors_pkg.insert_row(
px_transaction_error_id => x_transaction_error_id,
p_transaction_id => p_txn_error_rec.transaction_id,
p_message_id => p_txn_error_rec.message_id,
p_error_text => p_txn_error_rec.error_text,
p_source_type => p_txn_error_rec.source_type,
p_source_id => p_txn_error_rec.source_id,
p_processed_flag => p_txn_error_rec.processed_flag,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_transaction_type_id => p_txn_error_rec.transaction_type_id ,
p_source_group_ref => p_txn_error_rec.source_group_ref,
p_source_group_ref_id => p_txn_error_rec.source_group_ref_id,
p_source_header_ref => p_txn_error_rec.source_header_ref,
p_source_header_ref_id => p_txn_error_rec.source_header_ref_id,
p_source_line_ref => p_txn_error_rec.source_line_ref,
p_source_line_ref_id => p_txn_error_rec.source_line_ref_id,
p_source_dist_ref_id1 => p_txn_error_rec.source_dist_ref_id1,
p_source_dist_ref_id2 => p_txn_error_rec.source_dist_ref_id2,
p_inv_material_transaction_id => p_txn_error_rec.inv_material_transaction_id,
p_error_stage => p_txn_error_rec.error_stage,
p_message_string => p_txn_error_rec.message_string,
p_instance_id => p_txn_error_rec.instance_id,
p_inventory_item_id => p_txn_error_rec.inventory_item_id,
p_serial_number => p_txn_error_rec.serial_number,
p_lot_number => p_txn_error_rec.lot_number,
p_transaction_error_date => p_txn_error_rec.transaction_error_date,
p_src_serial_num_ctrl_code => p_txn_error_rec.src_serial_num_ctrl_code,
p_src_location_ctrl_code => p_txn_error_rec.src_location_ctrl_code,
p_src_lot_ctrl_code => p_txn_error_rec.src_lot_ctrl_code,
p_src_rev_qty_ctrl_code => p_txn_error_rec.src_rev_qty_ctrl_code,
p_dst_serial_num_ctrl_code => p_txn_error_rec.dst_serial_num_ctrl_code,
p_dst_location_ctrl_code => p_txn_error_rec.dst_location_ctrl_code,
p_dst_lot_ctrl_code => p_txn_error_rec.dst_lot_ctrl_code,
p_dst_rev_qty_ctrl_code => p_txn_error_rec.dst_rev_qty_ctrl_code,
p_comms_nl_trackable_flag => p_txn_error_rec.comms_nl_trackable_flag);
/* --- 2. for validation_mode='UPDATE' check for not null and validate against --- */
/* --- csi_transactions table --- */
/* ---------------------------------------------------------------------------------- */
PROCEDURE validate_transaction_id (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_mode IN VARCHAR2,
p_transaction_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_dummy VARCHAR2(1);
ELSIF(p_validation_mode = 'UPDATE') THEN
IF ( (p_transaction_id IS NULL) OR (p_transaction_id = fnd_api.g_miss_num) ) THEN
fnd_message.set_name('CSI', 'CSI_TXN_ID_NOT_PASSED');
SELECT 'x'
INTO l_dummy
FROM csi_transactions
WHERE transaction_id=p_transaction_id;
SELECT 'x'
INTO l_dummy
FROM csi_txn_types
WHERE transaction_type_id=p_transaction_type_id;
SELECT select_id
,select_name
,from_table
,where_clause
FROM jtf_objects_vl jov,jtf_object_usages jou
WHERE jov.object_code = p_object_code
AND jov.object_code = jou.object_code
AND jou.object_user_code = 'CSI_TXN';
SELECT source_object_code
INTO l_source_code
FROM csi_txn_types
WHERE transaction_type_id=p_transaction_type_id;
sql_stmt := ' SELECT ' ||
l_name_column ||
' , ' ||
l_id_column ||
' FROM ' ||
l_FROM_clause ||
l_where_clause ||
l_id_column ||
' = :source_line_ref_id ';
/* --- 2. for validation_mode='UPDATE' check for not null and validate against --- */
/* --- csi_transactions table --- */
/* ---------------------------------------------------------------------------------- */
PROCEDURE validate_object_version_num (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_mode IN VARCHAR2,
p_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_dummy VARCHAR2(1);
IF(p_validation_mode = 'UPDATE') THEN
IF ( (p_object_version_number IS NULL) OR (p_object_version_number = fnd_api.g_miss_num) ) THEN
fnd_message.set_name('CSI', 'CSI_MISSING_OBJ_VER_NUM');
SELECT 'x'
INTO l_dummy
FROM csi_lookups
WHERE lookup_type='CSI_SPLIT_REASON_CODE'
AND lookup_code=p_split_reason_code;