[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_stmt varchar2(4000);
csi_t_txn_line_dtls_pvt.build_txn_lines_select(
p_txn_line_query_rec => l_txn_line_query_rec,
x_lines_select_stmt => l_lines_where_clause,
x_lines_restrict => l_lines_restrict,
x_return_status => l_return_status);
l_select_stmt :=
'select 1 l_value from csi_t_transaction_lines where '||l_lines_where_clause;
csi_t_gen_utility_pvt.add(' Select Stmt:'||l_select_stmt);
dbms_sql.parse(l_cursor_id, l_select_stmt , dbms_sql.native);
SELECT csi_t_transaction_lines_s2.nextval
INTO px_txn_line_rec.source_transaction_id
FROM dual;
SELECT 'Y' INTO l_skip_tl_create
FROM csi_t_transaction_lines
WHERE transaction_line_id = px_txn_line_rec.transaction_line_id;
p_api_name => 'insert_row',
p_pkg_name => 'csi_t_transaction_lines_pkg');
csi_t_transaction_lines_pkg.insert_row(
px_transaction_line_id => l_txn_line_id,
p_source_transaction_type_id => px_txn_line_rec.source_transaction_type_id,
p_source_transaction_table => px_txn_line_rec.source_transaction_table,
p_source_txn_header_id => px_txn_line_rec.source_txn_header_id,
p_source_transaction_id => px_txn_line_rec.source_transaction_id,
-- Added for CZ Integration (Begin)
p_config_session_hdr_id => px_txn_line_rec.config_session_hdr_id ,
p_config_session_rev_num => px_txn_line_rec.config_session_rev_num ,
p_config_session_item_id => px_txn_line_rec.config_session_item_id ,
p_config_valid_status => px_txn_line_rec.config_valid_status ,
p_source_transaction_status => px_txn_line_rec.source_transaction_status ,
-- Added for CZ Integration (End)
p_error_code => px_txn_line_rec.error_code,
p_error_explanation => px_txn_line_rec.error_explanation,
p_processing_status => 'SUBMIT',
p_attribute1 => px_txn_line_rec.attribute1,
p_attribute2 => px_txn_line_rec.attribute2,
p_attribute3 => px_txn_line_rec.attribute3,
p_attribute4 => px_txn_line_rec.attribute4,
p_attribute5 => px_txn_line_rec.attribute5,
p_attribute6 => px_txn_line_rec.attribute6,
p_attribute7 => px_txn_line_rec.attribute7,
p_attribute8 => px_txn_line_rec.attribute8,
p_attribute9 => px_txn_line_rec.attribute9,
p_attribute10 => px_txn_line_rec.attribute10,
p_attribute11 => px_txn_line_rec.attribute11,
p_attribute12 => px_txn_line_rec.attribute12,
p_attribute13 => px_txn_line_rec.attribute13,
p_attribute14 => px_txn_line_rec.attribute14,
p_attribute15 => px_txn_line_rec.attribute15,
p_created_by => g_user_id,
p_creation_date => sysdate,
p_last_updated_by => g_user_id,
p_last_update_date => sysdate,
p_last_update_login => g_login_id,
p_object_version_number => 1.0,
p_context => px_txn_line_rec.context);
fnd_message.set_token('MESSAGE','insert_row failed '||sqlerrm);
PROCEDURE update_transaction_dtls (
p_api_version IN NUMBER
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec
,px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
,px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl
,px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl
,px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl
,px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl
,px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_transaction_dtls';
SAVEPOINT update_transaction_dtls;
l_c_tld_tbl.delete;
l_u_tld_tbl.delete;
l_d_tld_tbl.delete;
l_c_pty_tbl.delete;
l_u_pty_tbl.delete;
l_c_pty_acct_tbl.delete;
l_u_pty_acct_tbl.delete;
l_c_eav_tbl.delete;
l_u_eav_tbl.delete;
l_c_oa_tbl.delete;
l_u_oa_tbl.delete;
l_c_ii_tbl.delete;
l_u_ii_tbl.delete;
l_d_pty_ids_tbl.delete;
l_d_pty_acct_ids_tbl.delete;
x_tmp_line_detail_tbl.delete;
x_tmp_party_detail_tbl.delete;
x_tmp_pty_acct_detail_tbl.delete;
x_tmp_ii_rltns_tbl.delete;
x_tmp_org_assgn_tbl.delete;
x_tmp_ext_attrib_vals_tbl.delete;
x_tmp_pty_acct_ids_tbl.delete;
SELECT instance_id
INTO px_txn_line_detail_tbl(i).instance_id
FROM csi_t_txn_line_details
WHERE txn_line_detail_id = px_txn_line_detail_tbl(i).txn_line_detail_id
AND instance_id is NOT null;
l_txn_line_rec.source_transaction_type_id ||') found so add this instance to be deleted');
Debug('This instance will just be updated and not deleted ..'||
' Either the Instance ID was NULL or not a Mass Update Transaction Type');
||') found so add this instance to be deleted');
ELSE -- update to an existing party
Debug('Upd Txn party detail, pu_ind: '|| pu_ind||'type id:'||l_txn_line_rec.source_transaction_type_id);
Debug('Upd Txn line detail Update Pty Crt Acct, pau_ind: '|| pau_ind);
ELSE -- update to an existing account detail
Debug('Upd line detail Update Pty Upd Acct, pau_ind: '
|| pau_ind||px_txn_pty_acct_detail_tbl(pau_ind).preserve_detail_flag);
Debug('Update Acct, preserve flag: N');
Debug('Upd Txn line detail Update Pty Upd acct, pau_ind: '|| pau_ind);
debug(' Update:'||l_u_tld_tbl.count);
debug(' Delete:'||l_d_tld_tbl.count);
csi_t_txn_line_dtls_pvt.update_txn_line_dtls(
p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_line_rec => l_txn_line_rec,
p_txn_line_detail_tbl => l_u_tld_tbl,
px_txn_ii_rltns_tbl => l_u_ii_tbl,
px_txn_party_detail_tbl => l_u_pty_tbl,
px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
px_txn_org_assgn_tbl => l_u_oa_tbl,
px_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF l_d_pty_acct_ids_tbl.count > 0 THEN -- Mass Update; remove new accounts
csi_t_txn_parties_grp.delete_txn_pty_acct_dtls(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_txn_pty_acct_ids_tbl => l_d_pty_acct_ids_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
IF l_d_pty_ids_tbl.count > 0 THEN -- Mass Update; remove current and new associations
csi_t_txn_parties_grp.delete_txn_party_dtls(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_txn_party_ids_tbl => l_d_pty_ids_tbl
,x_txn_pty_acct_ids_tbl => x_tmp_pty_acct_ids_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
ELSE -- No updates being procesed..
px_txn_line_detail_tbl := x_tmp_line_detail_tbl;
csi_t_txn_details_pvt.delete_transaction_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_transaction_line_id => l_d_tld_tbl(d).transaction_line_id,
p_txn_line_detail_id => l_d_tld_tbl(d).txn_line_detail_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_gen_utility_pvt.add('Transaction details updated successfully.');
ROLLBACK TO update_transaction_dtls;
ROLLBACK TO update_transaction_dtls;
ROLLBACK TO update_transaction_dtls;
END update_transaction_dtls;
PROCEDURE delete_transaction_dtls(
p_api_version IN NUMBER
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_transaction_line_id IN NUMBER
,p_txn_line_detail_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_transaction_dtls';
SELECT txn_line_detail_id
FROM csi_t_txn_line_details
WHERE transaction_line_id = p_transaction_line_id
AND decode(txn_line_detail_id, p_txn_line_detail_id,p_txn_line_detail_id,-99999)
= nvl(p_txn_line_detail_id,-99999); -- Added for Mass update R12
SELECT txn_party_detail_id
FROM csi_t_party_details
WHERE txn_line_detail_id = p_txn_line_dtl_id;
SELECT txn_account_detail_id
FROM csi_t_party_accounts
WHERE txn_party_detail_id = p_txn_party_dtl_id;
SELECT txn_relationship_id
FROM csi_t_ii_relationships
WHERE transaction_line_id = p_transaction_line_id;
SELECT csit.transaction_line_id, csii.txn_relationship_id
FROM csi_t_ii_relationships csii , csi_t_txn_line_details csit
WHERE csit.txn_line_detail_id = csii.subject_id
AND csii.subject_type = 'T'
AND csii.subject_id in ( SELECT subject_id
FROM csi_t_ii_relationships
WHERE object_type ='T' AND object_id = p_txn_line_dtl_id)
AND csii.object_id = p_txn_line_dtl_id ;
SELECT csit.transaction_line_id, csii.txn_relationship_id
FROM csi_t_ii_relationships csii , csi_t_txn_line_details csit
WHERE csit.txn_line_detail_id = csii.object_id
AND csii.object_type = 'T'
AND csii.object_id in ( SELECT object_id
FROM csi_t_ii_relationships
WHERE subject_type ='T' AND subject_id = p_txn_line_dtl_id)
AND csii.subject_id = p_txn_line_dtl_id ;
SELECT txn_operating_unit_id
FROM csi_t_org_assignments
WHERE txn_line_detail_id = p_txn_line_dtl_id;
SELECT txn_attrib_detail_id
FROM csi_t_extend_attribs
WHERE txn_line_detail_id = p_txn_line_dtl_id;
SELECT transaction_system_id
FROM csi_t_txn_systems
WHERE transaction_line_id = p_transaction_line_id;
SAVEPOINT delete_transaction_dtls;
x_transaction_line_rec => l_txn_line_rec,-- Added for Mass update R12
x_return_status => l_return_status);
IF l_txn_line_rec.source_transaction_type_id <> 3 THEN -- Added for Mass update R12
-- excluding the Mass Update Transactions
csi_t_vldn_routines_pvt.check_ib_creation(
p_transaction_line_id => p_transaction_line_id,
x_return_status => l_return_status);
csi_t_txn_systems_grp.delete_txn_system(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_txn_system_id => sys_rec.transaction_system_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_pty_acct_ids_tbl => l_pty_acct_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_parties_pvt.delete_txn_party_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_party_ids_tbl => l_party_ids_tbl,
x_txn_pty_acct_ids_tbl => l_txn_pty_acct_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_rltnshps_pvt.delete_txn_ii_rltns_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_ii_rltns_ids_tbl => l_ii_rltns_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_ous_pvt.delete_txn_org_assgn_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_org_assgn_ids_tbl => l_org_assgn_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_attribs_pvt.delete_txn_ext_attrib_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_ext_attrib_ids_tbl => l_ext_attrib_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_line_dtls_pvt.delete_txn_line_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_line_detail_ids_tbl => l_line_dtl_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_transaction_lines_pkg.delete_row(
p_transaction_line_id => p_transaction_line_id);
csi_t_gen_utility_pvt.add('Transaction Details Deleted Successfully
for Transaction Line ID:'||to_char(p_transaction_line_id)||
' Txn line detail ID:'||to_char(p_txn_line_detail_id));
ROLLBACK TO delete_transaction_dtls;
ROLLBACK TO delete_transaction_dtls;
ROLLBACK TO delete_transaction_dtls;
END delete_transaction_dtls;
Select transaction_system_id, system_name , description,
system_type_code, system_number, bill_to_contact_id,
ship_to_contact_id, technical_contact_id, config_system_type,
service_admin_contact_id, ship_to_site_use_id,
bill_to_site_use_id, coterminate_day_month, customer_id,
install_site_use_id, transaction_line_id, start_date_active,
end_date_active,context, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15, object_version_number
From csi_t_txn_systems
Where transaction_system_id = p_txn_system_id;
l_select_stmt varchar2(2000);
l_select_stmt :=
'select transaction_system_id, transaction_line_id, system_name,'||
' description, system_type_code, system_number, customer_id,'||
' bill_to_contact_id, ship_to_contact_id, technical_contact_id,'||
' service_admin_contact_id, ship_to_site_use_id, bill_to_site_use_id,'||
' install_site_use_id, coterminate_day_month, config_system_type,'||
' context, attribute1, attribute2, attribute3, attribute4, attribute5,'||
' attribute6, attribute7, attribute8, attribute9, attribute10,'||
' attribute11, attribute12, attribute13, attribute14, attribute15,'||
' object_version_number '||
'from csi_t_txn_systems '||
'where transaction_system_id in '||p_txn_system_id_list;
dbms_sql.parse(l_sys_cur_id, l_select_stmt , dbms_sql.native);
SELECT a.txn_relationship_id, a.transaction_line_id,a.csi_inst_relationship_id,
a.subject_id, a.subject_type, a.object_id, a.object_type , a.relationship_type_code, a.display_order,
a.position_reference, a.mandatory_flag, a.active_start_date, a.active_end_date,
a.context, a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5,
a.attribute6, a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11,
a.attribute12, a.attribute13, a.attribute14, a.attribute15, a.object_version_number ,
a.sub_config_inst_hdr_id , a.sub_config_inst_rev_num , a.sub_config_inst_item_id ,
a.obj_config_inst_hdr_id , a.obj_config_inst_rev_num , a.obj_config_inst_item_id, a.target_commitment_date , a.transfer_components_flag
FROM csi_t_ii_relationships a
WHERE ((a.object_id = c_txn_line_detail_id
AND a.object_type = 'T') OR (a.subject_id = c_txn_line_detail_id
AND a.subject_type = 'T'))
GROUP BY a.txn_relationship_id, a.transaction_line_id,a.csi_inst_relationship_id, a.subject_id, a.subject_type, a.object_id, a.object_type , a.relationship_type_code, a.display_order,
a.position_reference, a.mandatory_flag, a.active_start_date, a.active_end_date,
a.context, a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5,
a.attribute6, a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11,
a.attribute12, a.attribute13, a.attribute14, a.attribute15, a.object_version_number ,
a.sub_config_inst_hdr_id , a.sub_config_inst_rev_num , a.sub_config_inst_item_id ,
a.obj_config_inst_hdr_id , a.obj_config_inst_rev_num , a.obj_config_inst_item_id , a.target_commitment_date , a.transfer_components_flag;
csi_t_txn_details_grp.update_txn_line_dtls(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_txn_line_rec => p_upd_txn_line_rec,
p_txn_line_detail_tbl => p_upd_txn_line_dtl_tbl,
px_txn_ii_rltns_tbl => l_ii_rltns_tbl,
px_txn_party_detail_tbl => l_pty_dtl_tbl,
px_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
px_txn_org_assgn_tbl => l_org_assgn_tbl,
px_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT transaction_line_id
INTO l_txn_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = px_crt_txn_line_rec.source_transaction_table
AND source_transaction_id = px_crt_txn_line_rec.source_transaction_id;
p_api_name => 'insert_row',
p_pkg_name => 'csi_t_transaction_lines_pkg');
csi_t_transaction_lines_pkg.insert_row(
px_transaction_line_id => l_txn_line_id,
p_source_transaction_type_id => px_crt_txn_line_rec.source_transaction_type_id,
p_source_transaction_table => px_crt_txn_line_rec.source_transaction_table,
p_source_txn_header_id => px_crt_txn_line_rec.source_txn_header_id,
p_source_transaction_id => px_crt_txn_line_rec.source_transaction_id,
-- Added for CZ Integration (Begin)
p_config_session_hdr_id => px_crt_txn_line_rec.config_session_hdr_id ,
p_config_session_rev_num => px_crt_txn_line_rec.config_session_rev_num ,
p_config_session_item_id => px_crt_txn_line_rec.config_session_item_id ,
p_config_valid_status => px_crt_txn_line_rec.config_valid_status ,
p_source_transaction_status => px_crt_txn_line_rec.source_transaction_status ,
-- Added for CZ Integration (End)
p_error_code => px_crt_txn_line_rec.error_code,
p_error_explanation => px_crt_txn_line_rec.error_explanation,
p_processing_status => px_crt_txn_line_rec.processing_status,
p_attribute1 => px_crt_txn_line_rec.attribute1,
p_attribute2 => px_crt_txn_line_rec.attribute2,
p_attribute3 => px_crt_txn_line_rec.attribute3,
p_attribute4 => px_crt_txn_line_rec.attribute4,
p_attribute5 => px_crt_txn_line_rec.attribute5,
p_attribute6 => px_crt_txn_line_rec.attribute6,
p_attribute7 => px_crt_txn_line_rec.attribute7,
p_attribute8 => px_crt_txn_line_rec.attribute8,
p_attribute9 => px_crt_txn_line_rec.attribute9,
p_attribute10 => px_crt_txn_line_rec.attribute10,
p_attribute11 => px_crt_txn_line_rec.attribute11,
p_attribute12 => px_crt_txn_line_rec.attribute12,
p_attribute13 => px_crt_txn_line_rec.attribute13,
p_attribute14 => px_crt_txn_line_rec.attribute14,
p_attribute15 => px_crt_txn_line_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.login_id,
p_object_version_number => 1.0,
p_context => px_crt_txn_line_rec.context);
UPDATE csi_t_txn_line_details
SET transaction_line_id = l_txn_line_id
WHERE txn_line_detail_id = l_line_dtl_ids_tbl(l_ind).txn_line_detail_id;
SELECT ordered_quantity
INTO l_split_line_qty
FROM oe_order_lines_all
WHERE line_id = px_split_txn_line_rec.source_transaction_id
AND header_id = nvl(px_split_txn_line_rec.source_txn_header_id,header_id);
px_line_dtl_tbl.delete; -- Role of input table is complete
l_ii_rltns_tbl.delete;