[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Changes for bug 3901123 . Commented this cursor to replace with a single select - Performance
CURSOR installed_cur(p_inst_hdr_id in number) IS
SELECT cii.config_inst_rev_num
FROM csi_item_instances cii,
cz_config_items czItems
WHERE cii.config_inst_hdr_id = p_inst_hdr_id
AND czItems.instance_hdr_id = p_inst_hdr_id
AND czItems.component_instance_type in ('I','R') -- I = Root instance
AND czItems.config_item_id = cii.config_inst_item_id
AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
AND nvl(cii.active_end_date, sysdate+1);
SELECT ctd.config_inst_rev_num
FROM csi_t_transaction_lines ctl,
csi_t_txn_line_details ctd
WHERE ctd.config_inst_hdr_id = p_inst_hdr_id
AND ctl.transaction_line_id = ctd.transaction_line_id
AND ctl.source_transaction_status = 'PROPOSED'
AND not exists (SELECT 'X' FROM csi_t_txn_line_details ctlx
WHERE ctlx.config_inst_hdr_id = ctd.config_inst_hdr_id
AND ctlx.config_inst_baseline_rev_num = ctd.config_inst_rev_num);
SELECT cii.config_inst_hdr_id, -- changes made for MACD locking bug, 4147624
cii.config_inst_rev_num,
cii.config_inst_item_id
INTO x_install_config_rec.config_inst_hdr_id,
x_install_config_rec.config_inst_rev_num,
x_install_config_rec.config_inst_item_id
FROM csi_item_instances cii
WHERE cii.config_inst_hdr_id = p_config_header_id
AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
AND nvl(cii.active_end_date, sysdate+1)
AND EXISTS (SELECT 'Y' -- bug 3901123
FROM cz_config_items czItems
WHERE czItems.instance_hdr_id = p_config_header_id
AND czItems.instance_rev_nbr = cii.config_inst_rev_num
AND czItems.config_item_id = cii.config_inst_item_id
AND czItems.component_instance_type = 'I' -- I = Root instance
AND czItems.deleted_flag = '0');
SELECT lock_source_appln_id, -- pass the locking details except the locked CZ keys
lock_source_header_ref,
lock_source_line_ref1,
lock_source_line_ref2,
lock_source_line_ref3,
lock_id,
lock_status
INTO x_install_config_rec.source_application_id,
x_install_config_rec.source_txn_header_ref,
x_install_config_rec.source_txn_line_ref1,
x_install_config_rec.source_txn_line_ref2,
x_install_config_rec.source_txn_line_ref3,
x_install_config_rec.lock_id,
x_install_config_rec.lock_status
FROM CSI_ITEM_INSTANCE_LOCKS
WHERE CONFIG_INST_HDR_ID = p_config_header_id
AND CONFIG_INST_ITEM_ID = x_install_config_rec.config_inst_item_id
AND LOCK_STATUS <> 0;
SELECT cti.sub_config_inst_hdr_id,
cti.sub_config_inst_rev_num,
cti.sub_config_inst_item_id,
cti.obj_config_inst_hdr_id,
cti.obj_config_inst_rev_num,
cti.obj_config_inst_item_id
FROM csi_t_ii_relationships cti
WHERE cti.relationship_type_code = 'CONNECTED-TO'
AND ((
cti.sub_config_inst_hdr_id = p_inst_hdr_id
AND
cti.sub_config_inst_rev_num = p_inst_rev_num
)
OR
(
cti.obj_config_inst_hdr_id = p_inst_hdr_id
AND
cti.obj_config_inst_rev_num = p_inst_rev_num
)
);
SELECT subject_id,
object_id
FROM csi_ii_relationships cir,
csi_item_instances cii
WHERE cii.config_inst_hdr_id = p_inst_hdr_id
AND cii.config_inst_rev_num = p_inst_rev_num
AND cir.relationship_type_code = 'CONNECTED-TO'
AND ( cir.subject_id = cii.instance_id
OR
cir.object_id = cii.instance_id)
AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
AND nvl(cir.active_end_date, sysdate+1);
SELECT subject_id ,
object_id ,
instance_id ,
decode (subject_id, instance_id, config_inst_hdr_id, -9999) sub_inst_hdr_id,
decode (object_id, instance_id, config_inst_hdr_id, -9999) obj_inst_hdr_id,
config_inst_item_id,
config_inst_rev_num
FROM csi_ii_relationships cir,
csi_item_instances cii
WHERE cii.config_inst_hdr_id = p_inst_hdr_id
AND cii.config_inst_rev_num = p_inst_rev_num
AND cir.relationship_type_code = 'CONNECTED-TO'
AND ( cir.subject_id = cii.instance_id
OR
cir.object_id = cii.instance_id)
AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
AND nvl(cir.active_end_date, sysdate+1);
SELECT config_inst_hdr_id ,
config_inst_rev_num,
config_inst_item_id
INTO l_sub_hdr_id,
l_sub_rev_num,
l_sub_item_id
FROM csi_item_instances
WHERE instance_id = installed_rec.subject_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT config_inst_hdr_id ,
config_inst_rev_num,
config_inst_item_id
INTO l_obj_hdr_id,
l_obj_rev_num,
l_obj_item_id
FROM csi_item_instances
WHERE instance_id = installed_rec.object_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT config_inst_hdr_id ,
config_inst_rev_num,
config_inst_item_id
INTO l_sub_hdr_id,
l_sub_rev_num,
l_sub_item_id
FROM csi_item_instances
WHERE instance_id = installed_rec.subject_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT config_inst_hdr_id ,
config_inst_rev_num,
config_inst_item_id
INTO l_obj_hdr_id,
l_obj_rev_num,
l_obj_item_id
FROM csi_item_instances
WHERE instance_id = installed_rec.object_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT cii.config_inst_hdr_id ,
cii.config_inst_rev_num,
cii.config_inst_item_id
INTO l_root_hdr_id,
l_root_rev_num,
l_root_item_id
FROM csi_item_instances cii
WHERE cii.config_inst_hdr_id = l_conn_hdr_id
AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
AND nvl(cii.active_end_date, sysdate+1)
AND EXISTS (SELECT 'Y'
FROM cz_config_items czItems
WHERE czItems.instance_hdr_id = l_conn_hdr_id
AND czItems.instance_rev_nbr = cii.config_inst_rev_num
AND czItems.config_item_id = cii.config_inst_item_id
AND czItems.component_instance_type = 'I' -- I = Root instance
AND czItems.deleted_flag = '0');
SELECT lock_source_appln_id,
lock_source_header_ref,
lock_source_line_ref1,
lock_source_line_ref2,
lock_source_line_ref3,
lock_id,
lock_status
INTO x_config_pair_table(l_o_ind).source_application_id,
x_config_pair_table(l_o_ind).source_txn_header_ref,
x_config_pair_table(l_o_ind).source_txn_line_ref1,
x_config_pair_table(l_o_ind).source_txn_line_ref2,
x_config_pair_table(l_o_ind).source_txn_line_ref3,
x_config_pair_table(l_o_ind).lock_id,
x_config_pair_table(l_o_ind).lock_status
FROM CSI_ITEM_INSTANCE_LOCKS
WHERE config_inst_hdr_id = l_root_hdr_id
AND config_inst_item_id = l_root_item_id
AND LOCK_STATUS <> 0;
SELECT ctl.CONFIG_SESSION_HDR_ID,
ctl.CONFIG_SESSION_REV_NUM,
ctl.CONFIG_SESSION_ITEM_ID,
ctld.CONFIG_INST_HDR_ID,
ctld.CONFIG_INST_REV_NUM,
ctld.CONFIG_INST_ITEM_ID,
ctld.instance_id
FROM csi_t_transaction_lines ctl,
csi_t_txn_line_details ctld
WHERE ctl.transaction_line_id = ctld.transaction_line_id
AND CONFIG_INST_HDR_ID = p_config_inst_hdr_id
AND CONFIG_INST_REV_NUM = p_config_inst_rev_num;
SELECT line_number||'.'||
shipment_number||'.'||
option_number
INTO l_config_rec.source_txn_line_ref1
--,l_config_rec.source_txn_line_ref2
--,l_config_rec.source_txn_line_ref3
FROM oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE oeh.header_id = oel.header_id
AND oeh.order_number = px_config_tbl(l_key).source_txn_header_ref
AND oel.config_header_id = l_CONFIG_SESSION_HDR_ID
AND oel.config_rev_nbr = l_CONFIG_SESSION_REV_NUM
AND oel.configuration_id = l_CONFIG_SESSION_ITEM_ID;
SELECT instance_id
INTO l_config_tbl(l_child_ind).instance_id
FROM CSI_ITEM_INSTANCES
WHERE CONFIG_INST_HDR_ID = l_config_rec.config_inst_hdr_id
-- AND CONFIG_INST_REV_NUM = l_config_rec.config_inst_rev_num
AND CONFIG_INST_ITEM_ID = l_config_rec.config_inst_item_id;
SELECT lock_status,
lock_id
INTO x_lock_status,
x_lock_id
FROM csi_item_instance_locks
WHERE config_inst_hdr_id = p_config_inst_header_id
AND config_inst_rev_num = p_config_inst_rev_num
AND config_inst_item_id = p_config_inst_item_id;
SELECT sub_config_inst_hdr_id,
sub_config_inst_rev_num,
sub_config_inst_item_id
FROM csi_t_ii_relationships
WHERE obj_config_inst_hdr_id = p_config_inst_header_id
AND obj_config_inst_rev_num = p_config_inst_rev_num
AND obj_config_inst_item_id = p_config_inst_item_id
AND sub_config_inst_hdr_id <> l_parent_hdr_id
-- AND sub_config_inst_rev_num <> l_parent_rev_num
AND sub_config_inst_item_id <> l_parent_item_id
AND relationship_type_code = 'CONNECTED-TO';
SELECT obj_config_inst_hdr_id,
obj_config_inst_rev_num,
obj_config_inst_item_id
FROM csi_t_ii_relationships
WHERE sub_config_inst_hdr_id = p_config_inst_header_id
AND sub_config_inst_rev_num = p_config_inst_rev_num
AND sub_config_inst_item_id = p_config_inst_item_id
AND obj_config_inst_hdr_id <> l_parent_hdr_id
-- AND obj_config_inst_rev_num <> l_parent_rev_num
AND obj_config_inst_item_id <> l_parent_item_id
AND relationship_type_code = 'CONNECTED-TO';
SELECT *
FROM csi_item_instance_locks
WHERE root_config_inst_hdr_id = l_root_inst_hdr_id
AND root_config_inst_rev_num = l_root_inst_rev_num
AND NOT( config_inst_hdr_id = l_config_inst_hdr_id
AND config_inst_rev_num = l_config_inst_rev_num
AND config_inst_item_id = l_config_inst_item_id )
AND lock_status <> 0;
SELECT root_config_inst_hdr_id,
root_config_inst_rev_num,
root_config_inst_item_id
INTO l_root_inst_hdr_id,
l_root_inst_rev_num,
l_root_inst_item_id
FROM csi_item_instance_locks
WHERE config_inst_hdr_id = p_config_rec.config_inst_hdr_id
AND config_inst_rev_num = p_config_rec.config_inst_rev_num
AND config_inst_item_id = p_config_rec.config_inst_item_id;
l_config_tbl.DELETE; -- Deleting the children from the List
l_config_tbl.DELETE; -- Ignoring the previously loaded list
Select config_inst_hdr_id,
config_inst_item_id,
config_inst_rev_num,
lock_id,
lock_source_appln_id,
lock_source_header_ref
Into l_lock_config_rec.config_inst_hdr_id,
l_lock_config_rec.config_inst_item_id,
l_lock_config_rec.config_inst_rev_num,
l_lock_config_rec.lock_id,
l_lock_config_rec.source_application_id,
l_lock_config_rec.source_txn_header_ref
From csi_item_instance_locks
Where config_inst_hdr_id = p_config_tbl(1).config_inst_hdr_id
And config_inst_item_id = p_config_tbl(1).config_inst_item_id
And config_inst_rev_num = p_config_tbl(1).config_inst_rev_num;
DELETE FROM CSI_ITEM_INSTANCE_LOCKS
WHERE lock_id = l_lock_config_rec.lock_id;
SELECT config_session_hdr_id,
config_session_rev_num,
config_session_item_id
FROM csi_t_transaction_lines
WHERE config_session_hdr_id = p_sess_hdr_id
-- Added the and condition for Bug 3711457
AND config_session_rev_num = p_sess_rev_num
ORDER BY config_session_item_id;
CZ_CF_API.delete_configuration(
config_hdr_id => p_session_hdr_id,
config_rev_nbr => p_session_rev_num_old,
usage_exists => l_usage_exists,
Error_message => l_error_message,
Return_value => l_return_value);
Select count(*)
Into l_found
From csi_item_instances i,
cz_config_items_v c
Where i.config_inst_hdr_id = c.instance_hdr_id
and i.config_inst_rev_num = c.instance_rev_nbr
and i.config_inst_item_id = c.config_item_id
and c.config_hdr_id = p_config_hdr_id
and c.config_rev_nbr = p_config_rev_nbr
and c.config_item_id = p_config_item_id;