The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select to_number(substr(cz.bom_sort_order,length(bom_sort_order)-3,4))
into p_txn_ii_rltns_rec.display_order
from cz_config_items_v cz
where cz.instance_hdr_id = p_txn_ii_rltns_rec.sub_config_inst_hdr_id
and cz.instance_rev_nbr = p_txn_ii_rltns_rec.sub_config_inst_rev_num
and cz.config_item_id = p_txn_ii_rltns_rec.sub_config_inst_item_id;
p_api_name => 'insert_row',
p_pkg_name => 'csi_t_ii_relationships_pkg');
csi_t_ii_relationships_pkg.insert_row(
px_txn_relationship_id => l_txn_relationship_id,
p_transaction_line_id => p_txn_ii_rltns_rec.transaction_line_id,
p_object_type => p_txn_ii_rltns_rec.object_type,
p_object_id => p_txn_ii_rltns_rec.object_id,
p_relationship_type_code => p_txn_ii_rltns_rec.relationship_type_code,
p_display_order => p_txn_ii_rltns_rec.display_order,
p_position_reference => p_txn_ii_rltns_rec.position_reference,
p_mandatory_flag => p_txn_ii_rltns_rec.mandatory_flag,
p_active_start_date => p_txn_ii_rltns_rec.active_start_date,
p_active_end_date => p_txn_ii_rltns_rec.active_end_date,
p_csi_inst_relationship_id => p_txn_ii_rltns_rec.csi_inst_relationship_id,
p_subject_type => p_txn_ii_rltns_rec.subject_type,
p_subject_id => p_txn_ii_rltns_rec.subject_id,
-- Added for CZ Integration (Begin)
p_sub_config_inst_hdr_id => p_txn_ii_rltns_rec.sub_config_inst_hdr_id ,
p_sub_config_inst_rev_num => p_txn_ii_rltns_rec.sub_config_inst_rev_num ,
p_sub_config_inst_item_id => p_txn_ii_rltns_rec.sub_config_inst_item_id ,
p_obj_config_inst_hdr_id => p_txn_ii_rltns_rec.obj_config_inst_hdr_id ,
p_obj_config_inst_rev_num => p_txn_ii_rltns_rec.obj_config_inst_rev_num ,
p_obj_config_inst_item_id => p_txn_ii_rltns_rec.obj_config_inst_item_id ,
p_target_commitment_date => p_txn_ii_rltns_rec.target_commitment_date ,
-- Added for CZ Integration (End)
p_attribute1 => p_txn_ii_rltns_rec.attribute1,
p_attribute2 => p_txn_ii_rltns_rec.attribute2,
p_attribute3 => p_txn_ii_rltns_rec.attribute3,
p_attribute4 => p_txn_ii_rltns_rec.attribute4,
p_attribute5 => p_txn_ii_rltns_rec.attribute5,
p_attribute6 => p_txn_ii_rltns_rec.attribute6,
p_attribute7 => p_txn_ii_rltns_rec.attribute7,
p_attribute8 => p_txn_ii_rltns_rec.attribute8,
p_attribute9 => p_txn_ii_rltns_rec.attribute9,
p_attribute10 => p_txn_ii_rltns_rec.attribute10,
p_attribute11 => p_txn_ii_rltns_rec.attribute11,
p_attribute12 => p_txn_ii_rltns_rec.attribute12,
p_attribute13 => p_txn_ii_rltns_rec.attribute13,
p_attribute14 => p_txn_ii_rltns_rec.attribute14,
p_attribute15 => p_txn_ii_rltns_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 => p_txn_ii_rltns_rec.context,
p_transfer_components_flag => p_txn_ii_rltns_rec.transfer_components_flag);
'csi_t_ii_relationships_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
PROCEDURE update_txn_ii_rltns_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_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_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_txn_ii_rltns_dtls';
SELECT *
FROM csi_t_ii_relationships
WHERE txn_relationship_id = p_rltns_id;
SAVEPOINT update_txn_ii_rltns_dtls;
Select to_number(substr(cz.bom_sort_order,length(bom_sort_order)-3,4))
into l_iir_rec.display_order
from cz_config_items_v cz
where cz.instance_hdr_id = p_txn_ii_rltns_tbl(l_ind).sub_config_inst_hdr_id
and cz.instance_rev_nbr= p_txn_ii_rltns_tbl(l_ind).sub_config_inst_rev_num
and cz.config_item_id = p_txn_ii_rltns_tbl(l_ind).sub_config_inst_item_id;
SELECT tl.processing_status
INTO l_processing_status
FROM csi_t_transaction_lines tl, csi_t_txn_line_details tld
WHERE tld.txn_line_detail_id = l_iir_rec.subject_id
AND tl.transaction_line_id = l_iir_rec.transaction_line_id
AND tl.transaction_line_id = tld.transaction_line_id;
SELECT tl.processing_status
INTO l_processing_status
FROM csi_t_transaction_lines tl, csi_t_txn_line_details tld
WHERE tld.txn_line_detail_id = l_iir_rec.object_id
AND tl.transaction_line_id = l_iir_rec.transaction_line_id
AND tl.transaction_line_id = tld.transaction_line_id;
SELECT tl.processing_status
INTO l_processing_status
FROM csi_t_transaction_lines tl, csi_t_txn_line_details tld
WHERE tld.txn_line_detail_id = l_iir_rec.object_id
AND tl.transaction_line_id = l_iir_rec.transaction_line_id
AND tl.transaction_line_id = tld.transaction_line_id;
'Unhandled exception in Update Txn reltns'||substr(sqlerrm,1,200));
l_iir_rec.last_updated_by := g_user_id;
l_iir_rec.last_update_date := sysdate;
l_iir_rec.last_update_login := g_login_id;
p_api_name => 'update_row',
p_pkg_name => 'csi_t_ii_relationships_pkg');
csi_t_ii_relationships_pkg.update_row(
p_txn_relationship_id => l_iir_rec.txn_relationship_id,
p_transaction_line_id => l_iir_rec.transaction_line_id,
p_object_type => l_iir_rec.object_type,
p_object_id => l_iir_rec.object_id,
p_relationship_type_code => l_iir_rec.relationship_type_code,
p_display_order => l_iir_rec.display_order,
p_position_reference => l_iir_rec.position_reference,
p_mandatory_flag => l_iir_rec.mandatory_flag,
p_active_start_date => l_iir_rec.active_start_date,
p_active_end_date => l_iir_rec.active_end_date,
p_csi_inst_relationship_id => l_iir_rec.csi_inst_relationship_id,
p_subject_type => l_iir_rec.subject_type,
p_subject_id => l_iir_rec.subject_id,
-- Added for CZ Integration (Begin)
p_sub_config_inst_hdr_id => l_iir_rec.sub_config_inst_hdr_id ,
p_sub_config_inst_rev_num => l_iir_rec.sub_config_inst_rev_num ,
p_sub_config_inst_item_id => l_iir_rec.sub_config_inst_item_id ,
p_obj_config_inst_hdr_id => l_iir_rec.obj_config_inst_hdr_id ,
p_obj_config_inst_rev_num => l_iir_rec.obj_config_inst_rev_num ,
p_obj_config_inst_item_id => l_iir_rec.obj_config_inst_item_id ,
p_target_commitment_date => l_iir_rec.target_commitment_date ,
-- Added for CZ Integration (End)
p_attribute1 => l_iir_rec.attribute1,
p_attribute2 => l_iir_rec.attribute2,
p_attribute3 => l_iir_rec.attribute3,
p_attribute4 => l_iir_rec.attribute4,
p_attribute5 => l_iir_rec.attribute5,
p_attribute6 => l_iir_rec.attribute6,
p_attribute7 => l_iir_rec.attribute7,
p_attribute8 => l_iir_rec.attribute8,
p_attribute9 => l_iir_rec.attribute9,
p_attribute10 => l_iir_rec.attribute10,
p_attribute11 => l_iir_rec.attribute11,
p_attribute12 => l_iir_rec.attribute12,
p_attribute13 => l_iir_rec.attribute13,
p_attribute14 => l_iir_rec.attribute14,
p_attribute15 => l_iir_rec.attribute15,
p_created_by => l_iir_cur_rec.created_by,
p_creation_date => l_iir_cur_rec.creation_date,
p_last_updated_by => g_user_id,
p_last_update_date => sysdate,
p_last_update_login => g_login_id,
p_object_version_number => l_iir_rec.object_version_number,
p_context => l_iir_rec.context,
p_transfer_components_flag => l_iir_rec.transfer_components_flag);
'csi_t_ii_relationships_pkg.update_row Failed. '||substr(sqlerrm,1,200));
ROLLBACK TO update_txn_ii_rltns_dtls;
ROLLBACK TO update_txn_ii_rltns_dtls;
ROLLBACK TO update_txn_ii_rltns_dtls;
END update_txn_ii_rltns_dtls;
PROCEDURE delete_txn_ii_rltns_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_ii_rltns_ids_tbl IN csi_t_datastructures_grp.txn_ii_rltns_ids_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) := 'delete_txn_ii_rltns_dtls';
SELECT txn_relationship_id
FROM csi_t_ii_relationships
WHERE transaction_line_id = p_txn_line_id;
SAVEPOINT delete_txn_ii_rltns_dtls;
p_api_name => 'delete_row',
p_pkg_name => 'csi_t_ii_relationships_pkg');
csi_t_ii_relationships_pkg.delete_row(
p_txn_relationship_id => l_rltns_id);
csi_t_ii_relationships_pkg.delete_row(
p_txn_relationship_id => ii_rec.txn_relationship_id);
ROLLBACK TO delete_txn_ii_rltns_dtls;
ROLLBACK TO delete_txn_ii_rltns_dtls;
ROLLBACK TO delete_txn_ii_rltns_dtls;
END delete_txn_ii_rltns_dtls;
l_select_stmt varchar2(2000);
l_select_stmt :=
'select txn_relationship_id, transaction_line_id,csi_inst_relationship_id, '||
' subject_id, object_id, relationship_type_code, display_order,'||
' position_reference, mandatory_flag, active_start_date, active_end_date, '||
' context, attribute1, attribute2, attribute3, attribute4, attribute5, '||
' attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, '||
' attribute12, attribute13, attribute14, attribute15, object_version_number,transfer_components_flag '||
'from csi_t_ii_relationships '||
'where transaction_line_id in '||p_txn_line_id_list;
dbms_sql.parse(l_iir_cur_id, l_select_stmt , dbms_sql.native);