[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO CZ_DB_LOGS
(RUN_ID,
LOGTIME,
LOGUSER,
URGENCY,
CALLER,
STATUSCODE,
MESSAGE,
MESSAGE_ID)
VALUES (p_run_id,
SYSDATE,
USER,
1,
'CZ_IB_TRANSACTIONS',
11276,
p_error_message,
m_COUNTER);
INSERT INTO CZ_DB_LOGS
(RUN_ID,
LOGTIME,
LOGUSER,
URGENCY,
CALLER,
STATUSCODE,
MESSAGE,
MESSAGE_ID)
VALUES
(p_run_id,
SYSDATE,
USER,
1,
'CZ_IB_TRANSACTIONS',
11276,
fnd_msg_pub.GET(i,fnd_api.g_false),
m_COUNTER);
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO m_RUN_ID FROM dual;
SELECT VALUE INTO DEBUG_MODE FROM CZ_DB_SETTINGS
WHERE UPPER(SETTING_ID)='CZ_IB_DEBUG_MODE' AND ROWNUM<2;
EXECUTE IMMEDIATE 'SELECT config_session_hdr_id FROM CSI_T_TRANSACTION_LINES WHERE rownum<2';
PROCEDURE delete_transaction_dtls
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_transaction_line_id IN NUMBER
,p_api_caller_identity IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN ' ||
' csi_t_txn_details_grp.delete_transaction_dtls ' ||
' ( ' ||
' p_api_version => :1 ' ||
' ,p_commit => :2 ' ||
' ,p_init_msg_list => :3 ' ||
' ,p_validation_level => :4 ' ||
' ,p_api_caller_identity => :5 ' ||
' ,p_transaction_line_id => :6 ' ||
' ,x_return_status => CZ_IB_TRANSACTIONS.m_return_status ' ||
' ,x_msg_count => CZ_IB_TRANSACTIONS.m_msg_count ' ||
' ,x_msg_data => CZ_IB_TRANSACTIONS.m_msg_data ' ||
' ); ' ||
x_msg_data := 'CZ_IB_TRANSACTIONS.delete_transaction_dtls : '||SQLERRM;
END delete_transaction_dtls;
m_txn_line_detail_tbl.DELETE;
m_txn_ext_attrib_vals_tbl.DELETE;
m_txn_line_detail_tbl.DELETE;
m_txn_ext_attrib_vals_tbl.DELETE;
m_txn_ii_rltns_tbl.DELETE;
m_txn_ii_rltns_tbl.DELETE;
m_config_pair_table.DELETE;
'SELECT transaction_line_id FROM CSI_T_TRANSACTION_LINES
WHERE config_session_hdr_id = :1 AND config_session_rev_num = :2 AND
config_session_item_id=NVL(:3,config_session_item_id) AND processing_status <> :4'
USING p_session_config_hdr_id,p_session_config_rev_nbr,p_instance_item_id,G_IB_TXN_STATUS_PROCESSED;
delete_transaction_dtls
(
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_none
,p_api_caller_identity => 'CONFIG'
,p_transaction_line_id => v_transaction_line_id
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
);
'SELECT DISTINCT transaction_line_id
FROM CSI_T_TXN_LINE_DETAILS
WHERE config_inst_hdr_id = :1 AND config_inst_rev_num = :2 AND processing_status <> :3'
USING p_instance_hdr_id,p_instance_rev_nbr,G_IB_TXN_STATUS_PROCESSED;
delete_transaction_dtls
(
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_none
,p_api_caller_identity => 'CONFIG'
,p_transaction_line_id => v_transaction_line_id
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
);
FOR i IN(SELECT DISTINCT config_hdr_id,config_rev_nbr FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id=p_config_hdr_id AND
instance_rev_nbr=p_config_rev_nbr)
LOOP
LOG_REPORT(m_RUN_ID,'config_hdr_id='||TO_CHAR(i.config_hdr_id)||' config_rev_nbr='||TO_CHAR(i.config_rev_nbr));
SELECT config_hdr_id,
config_rev_nbr,
config_item_id,
parent_config_item_id,
instance_hdr_id,
instance_rev_nbr,
target_hdr_id,
target_rev_nbr,
target_config_item_id,
NVL(ib_trackable, NO_FLAG),
location_id,
location_type_code,
inventory_item_id,
organization_id,
item_num_val, -- sselahi: changed from item_val
uom_code,
ext_activated_flag,
config_delta,
discontinued_flag,
component_instance_type,
NVL(tangible_item_flag,NO_FLAG),
NAME
BULK COLLECT INTO
t_config_hdr_tbl,
t_config_rev_nbr_tbl,
t_config_item_tbl,
t_parent_config_item_tbl,
t_instance_hdr_tbl,
t_instance_rev_nbr_tbl,
t_target_hdr_tbl,
t_target_rev_nbr_tbl,
t_target_item_tbl,
t_ib_trackable_tbl,
t_location_tbl,
t_location_type_code_tbl,
t_inventory_item_tbl,
t_organization_tbl,
t_quantity_tbl,
t_uom_code_tbl,
t_ext_activated_flag_tbl,
t_config_delta_tbl,
t_discontinued_flag_tbl,
t_component_instance_type_tbl,
t_tangible_item_flag_tbl,
t_name_tbl
FROM
(SELECT * FROM CZ_CONFIG_ITEMS ci
START WITH ci.instance_hdr_id=p_config_hdr_id AND
ci.instance_rev_nbr=p_config_rev_nbr AND component_instance_type='I' AND deleted_flag='0'
CONNECT BY PRIOR ci.config_item_id=ci.parent_config_item_id AND
ci.instance_hdr_id=p_config_hdr_id AND
ci.instance_rev_nbr=p_config_rev_nbr AND
PRIOR ci.instance_hdr_id=p_config_hdr_id AND
PRIOR ci.instance_rev_nbr=p_config_rev_nbr AND
deleted_flag='0' AND PRIOR deleted_flag='0' AND
(
(ci.ext_activated_flag='1' OR ci.config_delta <> 0) OR
(PRIOR ci.ext_activated_flag='1' OR PRIOR ci.config_delta <> 0)
)
) vi
WHERE vi.instance_hdr_id=p_config_hdr_id AND
vi.instance_rev_nbr=p_config_rev_nbr AND
((vi.item_num_val IS NOT NULL AND vi.item_num_val<>0)OR vi.target_config_item_id IS NOT NULL); --Bug6655994 Added a new condition
SELECT NVL(ib_trackable,NO_FLAG) INTO v_ib_trackable FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id = t_target_hdr_tbl(i) AND
instance_rev_nbr = t_target_rev_nbr_tbl(i) AND
config_item_id = t_target_item_tbl(i);
SELECT baseline_rev_nbr INTO v_baseline_rev_nbr
FROM cz_config_hdrs
WHERE config_hdr_id=p_config_hdr_id AND
config_rev_nbr=p_config_rev_nbr AND
deleted_flag=NO_FLAG;
SELECT instance_hdr_id,instance_rev_nbr,parent_config_item_id
INTO v_root_instance_hdr_id,v_root_instance_rev_nbr,v_root_config_item_id
FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id=p_config_hdr_id AND
instance_rev_nbr=p_config_rev_nbr AND
config_item_id=p_config_item_id;
t_hash_changed_item_tbl.DELETE;
t_hash_src_txn_flag_tbl.DELETE;
SELECT target_hdr_id,target_rev_nbr,target_config_item_id
INTO v_target_hdr_id,v_target_rev_nbr,v_target_config_item_id
FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id=t_instance_hdr_tbl(i) AND instance_rev_nbr=v_baseline_rev_nbr AND
config_item_id=t_config_item_tbl(i);
t_config_item_tbl.DELETE;
SELECT
config_item_id,attribute_group,attribute_name,attribute_value
BULK COLLECT INTO
t_config_item_tbl,t_attribute_group_tbl,t_attribute_name_tbl,t_attribute_value_tbl
FROM CZ_CONFIG_EXT_ATTRIBUTES
WHERE config_hdr_id = p_config_hdr_id AND
config_rev_nbr = p_config_rev_nbr AND deleted_flag=NO_FLAG;
tv_txn_line_dtls_tbl.DELETE;
tv_txn_ii_rltns_tbl.DELETE;
tv_txn_eav_tbl.DELETE;
tv_txn_party_tbl.DELETE;
tv_txn_line_dtls_tbl.DELETE;
tv_txn_ii_rltns_tbl.DELETE;
tv_txn_eav_tbl.DELETE;
SELECT ext_activated_flag||config_delta
INTO v_status
FROM CZ_CONFIG_ITEMS
WHERE instance_hdr_id = p_instance_hdr_id AND
instance_rev_nbr = p_instance_rev_nbr AND
component_instance_type='I' AND
deleted_flag='0';
* INSERT/UPDATE CZ data IN IB Transactions SCHEMA
*/
PROCEDURE Update_Instances
(
p_config_instance_tbl IN SYSTEM.cz_config_instance_tbl_type,
p_effective_date IN DATE,
p_txn_type_id IN NUMBER,
x_run_id OUT NOCOPY NUMBER
) IS
t_rltns_tbl txn_ii_rltns_tbl;
t_config_item_tbl.DELETE;
SELECT config_item_id
BULK COLLECT INTO t_config_item_tbl
FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
instance_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND
ib_trackable='1';
SELECT baseline_rev_nbr INTO v_baseline_rev_nbr
FROM CZ_CONFIG_HDRS
WHERE config_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
config_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND deleted_flag=NO_FLAG;
FOR k IN (SELECT target_hdr_id,target_rev_nbr,target_config_item_id,config_item_id
FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
instance_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND
target_config_item_id IS NOT NULL)
LOOP
BEGIN
IF v_baseline_rev_nbr IS NOT NULL THEN
SELECT target_hdr_id,target_rev_nbr,target_config_item_id
INTO v_target_hdr_id,v_target_rev_nbr,v_target_config_item_id
FROM CZ_CONFIG_ITEMS_V
WHERE instance_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
instance_rev_nbr=v_baseline_rev_nbr AND config_item_id=k.config_item_id;
SELECT baseline_rev_nbr INTO v_baseline_rev_nbr FROM CZ_CONFIG_HDRS
WHERE config_hdr_id=t_rltns_tbl(t).obj_config_inst_hdr_id AND
config_rev_nbr=t_rltns_tbl(t).obj_config_inst_rev_num AND
deleted_flag=NO_FLAG;
FOR t IN (SELECT config_item_id FROM cz_config_items
WHERE config_item_id<>v_cfg_item_id
START WITH config_item_id=v_cfg_item_id AND
instance_hdr_id=t_expired_rltns_tbl(nn).sub_config_inst_hdr_id AND
instance_rev_nbr=t_expired_rltns_tbl(nn).sub_config_inst_rev_num
CONNECT BY PRIOR parent_config_item_id=config_item_id AND
instance_hdr_id=t_expired_rltns_tbl(nn).sub_config_inst_hdr_id AND
instance_rev_nbr=t_expired_rltns_tbl(nn).sub_config_inst_rev_num)
LOOP
IF t_hash_dtls_tbl.EXISTS(t.config_item_id) THEN
v_txn_exp_line_id := t_hash_dtls_tbl(t.config_item_id);
END Update_Instances;
PROCEDURE update_CSI_Item_Inst_Status
(p_config_hdr_id IN NUMBER,
p_config_rev_nbr IN NUMBER,
p_config_status IN VARCHAR2,
x_run_id OUT NOCOPY NUMBER) IS
BEGIN
EXECUTE IMMEDIATE
'DECLARE ' ||
' v_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC; ' ||
' csi_item_instance_pub.update_item_instance( ' ||
' p_api_version => 1.0, ' ||
' p_commit => ''F'', ' ||
' p_init_msg_list => ''F'', ' ||
' p_validation_level => 100, ' ||
' p_instance_rec => v_instance_rec, ' ||
' p_ext_attrib_values_tbl => v_ext_attrib_values_tbl, ' ||
' p_party_tbl => v_party_tbl, ' ||
' p_account_tbl => v_party_account_tbl, ' ||
' p_pricing_attrib_tbl => v_pricing_attrib_tbl, ' ||
' p_org_assignments_tbl => v_org_assignments_tbl, ' ||
' p_asset_assignment_tbl => v_asset_assignment_tbl, ' ||
' p_txn_rec => v_txn_rec, ' ||
' x_instance_id_lst => x_instance_id_lst, ' ||
' x_return_status => CZ_IB_TRANSACTIONS.m_return_status, ' ||
' x_msg_count => CZ_IB_TRANSACTIONS.m_msg_count, ' ||
' x_msg_data => CZ_IB_TRANSACTIONS.m_msg_data); ' ||
LOG_REPORT(m_RUN_ID,'csi_item_instance_pub.update_item_instance() failed : '||CZ_IB_TRANSACTIONS.m_msg_data,CZ_IB_TRANSACTIONS.m_msg_count);
END update_CSI_Item_Inst_Status;
* The method will UPDATE the status OF the IB instance
* <=> CSI_T_TRANSACTION_LINES.CONFIG_VALID_STATUS / CSI_ITEM_INSTANCES.CONFIG_VALID_STATUS TO be INVALID
* IF either the CZ_CONFIG_HDRS.config_status field IS SET TO INCOMPLETE OR
* the CZ_CONFIG_HDRS.has_failures field IS SET TO TRUE, otherwise, it will be SET TO VALID
*/
PROCEDURE Update_Instances_Status
(
p_config_instance_tbl IN SYSTEM.cz_config_instance_tbl_type,
x_run_id OUT NOCOPY NUMBER
) IS
v_config_status VARCHAR2(255);
FOR k IN (SELECT config_status,has_failures FROM cz_config_hdrs
WHERE config_hdr_id=p_config_instance_tbl(i).config_hdr_id AND
config_rev_nbr=p_config_instance_tbl(i).new_config_rev_nbr AND deleted_flag=NO_FLAG)
LOOP
IF (k.config_status=INCOMPLETE_CONFIG_STATUS OR k.has_failures=YES_FLAG) THEN
v_config_status:=NO_FLAG;
' UPDATE CSI_T_TRANSACTION_LINES ' ||
' SET config_valid_status = '||v_config_status ||
' WHERE (config_session_hdr_id,config_session_rev_num) IN ' ||
' (SELECT DISTINCT config_hdr_id,config_rev_nbr FROM CZ_CONFIG_ITEMS ' ||
' WHERE instance_hdr_id=:1 AND ' ||
' instance_rev_nbr IN(:2,:3) AND deleted_flag=''0''); ' ||
update_CSI_Item_Inst_Status(p_config_instance_tbl(i).config_hdr_id,
p_config_instance_tbl(i).old_config_rev_nbr,
v_config_status,
x_run_id);
END Update_Instances_Status;
' FOR i IN(SELECT config_inst_item_id,config_inst_rev_num FROM CSI_ITEM_INSTANCES ' ||
' WHERE config_inst_hdr_id=:1 ' ||
' ORDER BY config_inst_item_id,config_inst_rev_num) ' ||
' LOOP ' ||
' CZ_IB_TRANSACTIONS.m_csi_rev_nbr_tbl(i.config_inst_item_id) := i.config_inst_rev_num; ' ||
m_csi_rev_nbr_tbl.DELETE;
'SELECT instance_description, location_id, location_type_code ' ||
' FROM CSI_ITEM_INSTANCES ' ||
' WHERE config_inst_hdr_id = :1 AND ' ||
' config_inst_rev_num = :2 AND config_inst_item_id=:3'
INTO x_instance_description, x_location_id, x_location_type_code
USING p_config_hdr_id, p_config_rev_nbr, p_config_item_id;
m_attribute_category_tbl.DELETE;
m_attribute_name_tbl.DELETE;
m_attribute_value_tbl.DELETE;
' SELECT ' ||
' a.attribute_category,a.attribute_code,b.attribute_value ' ||
' BULK COLLECT INTO ' ||
' CZ_IB_TRANSACTIONS.m_attribute_category_tbl,CZ_IB_TRANSACTIONS.m_attribute_name_tbl, ' ||
' CZ_IB_TRANSACTIONS.m_attribute_value_tbl ' ||
' FROM CSI_I_EXTENDED_ATTRIBS a, CSI_IEA_VALUES b, CSI_ITEM_INSTANCES c ' ||
' WHERE a.attribute_id = b.attribute_id AND ' ||
' b.instance_id = c.instance_id AND ' ||
' c.config_inst_hdr_id = :1 AND ' ||
' c.config_inst_rev_num = :2 AND ' ||
' c.config_inst_item_id = :3; ' ||
m_attribute_category_tbl.DELETE;
m_attribute_name_tbl.DELETE;
m_attribute_value_tbl.DELETE;
'SELECT ' ||
' b.attribute_value ' ||
' FROM CSI_I_EXTENDED_ATTRIBS a, CSI_IEA_VALUES b, CSI_ITEM_INSTANCES c ' ||
' WHERE a.attribute_id = b.attribute_id AND ' ||
' b.instance_id = c.instance_id AND ' ||
' c.config_inst_hdr_id = :1 AND ' ||
' c.config_inst_rev_num = :2 AND ' ||
' c.config_inst_item_id = :3 AND ' ||
' a.attribute_code = :4'
INTO x_attribute_value
USING p_config_hdr_id, p_config_rev_nbr, p_config_item_id, p_attribute_name;
m_attribute_category_tbl.DELETE;
m_attribute_name_tbl.DELETE;
m_attribute_value_tbl.DELETE;
' SELECT ' ||
' attribute_category, attribute_code, attribute_value ' ||
' BULK COLLECT INTO ' ||
' CZ_IB_TRANSACTIONS.m_attribute_category_tbl,CZ_IB_TRANSACTIONS.m_attribute_name_tbl, ' ||
' CZ_IB_TRANSACTIONS.m_attribute_value_tbl ' ||
' FROM CSI_T_EXTEND_ATTRIBS_V a ' ||
' WHERE txn_line_detail_id IN ' ||
' (SELECT txn_line_detail_id ' ||
' FROM CSI_T_TXN_LINE_DETAILS ' ||
' WHERE config_inst_hdr_id = :1 AND ' ||
' config_inst_rev_num=:2 AND config_inst_item_id=:3); ' ||
m_attribute_category_tbl.DELETE;
m_attribute_name_tbl.DELETE;
m_attribute_value_tbl.DELETE;
SELECT
attribute_group, attribute_name, attribute_value
BULK COLLECT INTO
x_attribute_category_tbl, x_attribute_name_tbl, x_attribute_value_tbl
FROM CZ_CONFIG_EXT_ATTRIBUTES
WHERE config_hdr_id = p_config_hdr_id AND
config_rev_nbr = p_config_rev_nbr AND
config_item_id = p_config_item_id AND deleted_flag='0';
m_config_hdr_tbl.DELETE;m_config_rev_nbr_tbl.DELETE;m_config_item_tbl.DELETE;
m_instance_description_tbl.DELETE;m_location_id_tbl.DELETE;
'SELECT ' ||
' config_item_id ' ||
'BULK COLLECT INTO ' ||
' CZ_IB_TRANSACTIONS.m_config_item_tbl ' ||
'FROM CZ_CONFIG_ITEMS_V ' ||
'WHERE instance_hdr_id=:1 AND instance_rev_nbr=:2 ' ||
' AND CZ_UTILS.conv_num(item_val) IS NOT NULL AND ib_trackable=''1''; ' ||
t_config_item_tbl:=m_config_item_tbl; m_config_item_tbl.DELETE;
m_attribute_category_tbl.DELETE;
m_attribute_name_tbl.DELETE;
m_attribute_value_tbl.DELETE;
t_attribute_category_tbl.DELETE;
t_attribute_name_tbl.DELETE;
t_attribute_value_tbl.DELETE;
x_txn_params_tbl.DELETE(x_txn_params_tbl.COUNT);
x_config_attribute_tbl.DELETE(x_config_attribute_tbl.COUNT);
FOR i IN (SELECT config_inst_item_id FROM CSI_T_TXN_LINE_DETAILS
WHERE config_inst_hdr_id = p_config_hdr_id AND
config_inst_rev_num = p_config_rev_nbr AND
config_inst_item_id = p_config_item_id)
LOOP
x_in_txn:=YES_FLAG;
FOR i IN (SELECT config_inst_item_id FROM CSI_ITEM_INSTANCES
WHERE config_inst_hdr_id = p_config_hdr_id AND
config_inst_rev_num = p_config_rev_nbr AND
config_inst_item_id = p_config_item_id)
LOOP
x_in_inst:=YES_FLAG;
x_Config_Pair_Table.DELETE(v_config_ind);
SELECT instance_hdr_id,instance_rev_nbr,config_item_id
BULK COLLECT INTO t_instance_hdr_tbl,t_instance_rev_nbr_tbl,t_config_item_tbl
FROM CZ_CONFIG_ITEMS
WHERE config_hdr_id = p_config_hdr_id AND
config_rev_nbr= p_config_rev_nbr AND
component_instance_type='I' AND
ib_trackable=YES_FLAG AND deleted_flag=NO_FLAG;
t_config_instance_tbl.DELETE(t_config_instance_tbl.COUNT);
Update_Instances
(
p_config_instance_tbl => t_config_instance_tbl,
p_effective_date => NULL,
p_txn_type_id => v_txn_type_id,
x_run_id => x_run_id
);
PROCEDURE Test_Update_Instances
(
p_instance_hdr_id NUMBER,
p_config_item_id NUMBER,
p_old_rev_nbr NUMBER,
p_new_rev_nbr NUMBER
) IS
v_run_id NUMBER;
Update_Instances
(
p_config_instance_tbl => SYSTEM.cz_config_instance_tbl_type(SYSTEM.cz_config_instance_type(p_instance_hdr_id,p_config_item_id,p_old_rev_nbr,p_new_rev_nbr)),
p_effective_date => SYSDATE,
p_txn_type_id => v_txn_type_id,
x_run_id => v_run_id
);
END Test_Update_Instances;