[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM csi_item_instances
WHERE instance_id = p_ins_id;
SELECT instance_hdr_id
,config_item_id
,has_failures
FROM cz_config_details_v d
,cz_config_hdrs_v h
WHERE d.instance_hdr_id = h.config_hdr_id
AND d.instance_rev_nbr = h.config_rev_nbr
AND d.component_instance_type = 'I'
AND d.config_hdr_id = p_config_hdr_id
AND d.config_rev_nbr = p_config_rev_nbr;
SELECT instance_id
,object_version_number
,config_valid_status
FROM csi_item_instances
WHERE config_inst_hdr_id=p_config_inst_hdr_id
AND config_inst_item_id=p_config_inst_item_id;
SELECT has_failures
,config_status
FROM cz_config_details_v d
,cz_config_hdrs h
WHERE d.instance_hdr_id = p_config_ins_hdr_id
AND d.instance_rev_nbr = p_config_ins_rev_nbr
-- AND d.component_instance_type = 'I'
AND d.config_hdr_id = h.config_hdr_id
AND d.config_rev_nbr = h.config_rev_nbr;
SELECT instance_id
,object_version_number
,config_valid_status
FROM csi_item_instances
WHERE config_inst_hdr_id=p_config_inst_hdr_id
AND config_inst_item_id=p_config_inst_item_id;
SELECT cip.party_id
INTO l_party_id
FROM csi_i_parties cip
WHERE cip.instance_id = p_instance_rec.instance_id
AND cip.relationship_type_code = 'OWNER';
SELECT component_instance_type
,config_hdr_id
,config_rev_nbr
INTO l_component_ins_type
,l_config_hdr_id
,l_config_rev_nbr
FROM cz_config_items_v
WHERE instance_hdr_id = p_instance_rec.config_inst_hdr_id
AND instance_rev_nbr = p_instance_rec.config_inst_rev_num
AND config_item_id = p_instance_rec.config_inst_item_id;
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_config_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_config_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = p_txn_rec.transaction_id;
SELECT 'Y'
INTO l_create_event_called
FROM csi_item_instances_h
WHERE instance_id = p_instance_rec.instance_id
AND new_accounting_class_code = 'CUST_PROD'
AND transaction_id <> p_txn_rec.transaction_id
AND ROWNUM = 1;
csi_gen_utility_pvt.put_line('Firing the Update Instance Event');
CSI_BUSINESS_EVENT_PVT.UPDATE_INSTANCE_EVENT
( 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_instance_id => p_instance_rec.instance_id
,p_subject_instance_id => null
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line(' Error from CSI_BUSINESS_EVENT.UPDATE_INSTANCE_EVENT');
/* Procedure name: update_item_instance */
/* Description : procedure used to update an Item */
/* Instance */
/*----------------------------------------------------*/
PROCEDURE update_item_instance
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_instance_rec IN csi_datastructures_pub.instance_rec
,p_ext_attrib_values_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
,p_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
,p_pricing_attrib_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl
,p_org_assignments_tbl IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl
,p_asset_assignment_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_tbl
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,x_instance_id_lst OUT NOCOPY csi_datastructures_pub.id_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_ITEM_INSTANCE';
l_updated BOOLEAN := FALSE;
SELECT *
FROM csi_item_instances
WHERE instance_id = p_ins_id;
SELECT *
FROM csi_item_instances
WHERE instance_id=p_ins_id;
l_update NUMBER;
SELECT instance_hdr_id
,config_item_id
,has_failures
FROM cz_config_details_v d
,cz_config_hdrs_v h
WHERE d.instance_hdr_id = h.config_hdr_id
AND d.instance_rev_nbr = h.config_rev_nbr
AND d.component_instance_type = 'I'
AND d.config_hdr_id = p_config_hdr_id
AND d.config_rev_nbr = p_config_rev_nbr;
SELECT instance_id
,object_version_number
,config_valid_status
FROM csi_item_instances
WHERE config_inst_hdr_id=p_config_inst_hdr_id
AND config_inst_item_id=p_config_inst_item_id;
SELECT has_failures
,config_status
FROM cz_config_details_v d
,cz_config_hdrs h
WHERE d.instance_hdr_id = p_config_ins_hdr_id
AND d.instance_rev_nbr = p_config_ins_rev_nbr
-- AND d.component_instance_type = 'I'
AND d.config_hdr_id = h.config_hdr_id
AND d.config_rev_nbr = h.config_rev_nbr;
SELECT instance_id
,object_version_number
,config_valid_status
FROM csi_item_instances
WHERE config_inst_hdr_id=p_config_inst_hdr_id
AND config_inst_item_id=p_config_inst_item_id;
SELECT ip_account_id
,active_end_date
,object_version_number
from csi_ip_accounts
where instance_party_id=p_ins_pty_id
and relationship_type_code<>'OWNER';
SAVEPOINT update_item_instance;
csi_gen_utility_pvt.put_line( 'update_item_instance');
csi_gen_utility_pvt.put_line( 'update_item_instance' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level );
csi_gen_utility_pvt.put_line('Inside CSI_ITEM_INSTANCE_PUB.Update_Item_Instance');
SELECT instance_id
INTO l_inst_id
FROM csi_i_parties
WHERE instance_party_id = p_party_tbl(l_party_rec).instance_party_id
AND ROWNUM = 1;
SELECT 'Y'
INTO l_create_event_called
FROM csi_item_instances_h
WHERE instance_id = l_inst_id
AND new_accounting_class_code = 'CUST_PROD'
AND ROWNUM = 1;
SELECT 'Y'
INTO l_create_event_called
FROM csi_item_instances_h
WHERE instance_id = p_instance_rec.instance_id
AND new_accounting_class_code = 'CUST_PROD'
AND ROWNUM = 1;
IF NOT (csi_Item_Instance_Pvt.Anything_To_Update(p_instance_rec => p_instance_rec ))
THEN
-- If Anything is getting updated then lock check will be made in pvt.
IF p_instance_rec.instance_id IS NOT NULL AND
p_instance_rec.instance_id <> fnd_api.g_miss_num
THEN
csi_item_instance_pvt.get_instance_lock_status
( p_instance_id => p_instance_rec.instance_id ,
p_lock_status => l_lock_status
);
csi_gen_utility_pvt.put_line('Calling CSI_ITEM_INSTANCE_CUHK.Update_Item_Instance_Pre ..');
CSI_ITEM_INSTANCE_CUHK.Update_Item_Instance_Pre
(
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_instance_rec => p_instance_rec
,p_ext_attrib_values_tbl => p_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => p_pricing_attrib_tbl
,p_org_assignments_tbl => p_org_assignments_tbl
,p_asset_assignment_tbl => p_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_CUHK.Update_Item_Instance_Pre API ');
csi_gen_utility_pvt.put_line('ERROR FROM JTF_USR_HKS.Ok_to_execute API Update Pre Customer');
csi_gen_utility_pvt.put_line('Calling CSI_ITEM_INSTANCE_VUHK.Update_Item_Instance_Pre ..');
CSI_ITEM_INSTANCE_VUHK.Update_Item_Instance_Pre
(
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_instance_rec => p_instance_rec
,p_ext_attrib_values_tbl => p_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => p_pricing_attrib_tbl
,p_org_assignments_tbl => p_org_assignments_tbl
,p_asset_assignment_tbl => p_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_VUHK.Update_Item_Instance_Pre API ');
csi_gen_utility_pvt.put_line('ERROR FROM JTF_USR_HKS.Ok_to_execute API Update Pre Vertical');
SELECT cip.party_id
INTO l_owner_party_id
FROM csi_i_parties cip
WHERE cip.instance_id = p_instance_rec.instance_id
AND cip.relationship_type_code = 'OWNER';
SELECT src_change_owner
INTO l_src_change_owner
FROM csi_txn_sub_types
WHERE sub_type_id = P_txn_rec.txn_sub_type_id
AND transaction_type_id = P_txn_rec.transaction_type_id;
SELECT instance_party_id,
instance_id,
party_source_table,
party_id,
relationship_type_code,
contact_flag,
object_version_number,
'Y'
INTO l_cascade_party_tbl(1).instance_party_id,
l_cascade_party_tbl(1).instance_id,
l_cascade_party_tbl(1).party_source_table,
l_cascade_party_tbl(1).party_id,
l_cascade_party_tbl(1).relationship_type_code,
l_cascade_party_tbl(1).contact_flag,
l_cascade_party_tbl(1).object_version_number,
l_cascade_party_tbl(1).cascade_ownership_flag
FROM csi_i_parties
WHERE instance_id=p_instance_rec.instance_id
AND relationship_type_code='OWNER'
AND (active_end_date IS NULL OR active_end_date>sysdate);
SELECT ip_account_id,
instance_party_id,
party_account_id,
relationship_type_code,
1,
object_version_number
INTO l_cascade_account_tbl(1).ip_account_id,
l_cascade_account_tbl(1).instance_party_id,
l_cascade_account_tbl(1).party_account_id,
l_cascade_account_tbl(1).relationship_type_code,
l_cascade_account_tbl(1).parent_tbl_index,
l_cascade_account_tbl(1).object_version_number
FROM csi_ip_accounts
WHERE instance_party_id=l_cascade_party_tbl(1).instance_party_id
AND relationship_type_code='OWNER'
AND (active_end_date IS NULL OR active_end_date>sysdate);
IF (csi_Item_Instance_Pvt.Anything_To_Update(p_instance_rec => p_instance_rec ))
THEN
l_new_instance_rec := p_instance_rec;
l_updated:=TRUE;
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_new_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
csi_gen_utility_pvt.put_line('Calling CSI_ITEM_INSTANCE_CUHK.Update_Item_Instance_Post ..');
CSI_ITEM_INSTANCE_CUHK.Update_Item_Instance_Post
(
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_instance_rec => p_instance_rec
,p_ext_attrib_values_tbl => p_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => p_pricing_attrib_tbl
,p_org_assignments_tbl => p_org_assignments_tbl
,p_asset_assignment_tbl => p_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_CUHK.Update_Item_Instance_Post API ');
csi_gen_utility_pvt.put_line('ERROR FROM JTF_USR_HKS.Ok_to_execute API Update Post Customer');
l_update:=1;
l_t_party_tbl.DELETE(party_row);
l_t_party_tbl.DELETE(party_row);
l_t_account_tbl.DELETE(acct_row);
lc_party_tbl(l_update) := l_t_party_tbl(party_row);
l_t_party_tbl.DELETE(party_row);
l_new_account_tbl(l_acct_row).parent_tbl_index := l_update;
l_t_account_tbl.DELETE(acct_row);
l_update:=l_update+1;
SELECT party_id
INTO l_tem_party_id
FROM csi_i_parties
WHERE instance_party_id=lc_party_tbl(i).instance_party_id
AND relationship_type_code='OWNER';
SELECT active_end_date
INTO l_bump_date --lc_party_tbl(k).active_end_date
FROM csi_i_parties
WHERE instance_party_id=lc_party_tbl(k).instance_party_id;
csi_party_relationships_pub.update_inst_party_relationship
(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_party_tbl => lc_party_tbl
,p_party_account_tbl=> l_new_account_tbl
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT object_version_number
INTO la_account_tbl(i).object_version_number
FROM csi_ip_accounts
WHERE ip_account_id=la_account_tbl(i).ip_account_id;
csi_organization_unit_pvt.update_organization_unit
(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_org_unit_rec => p_org_assignments_tbl(tab_row)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lookup_tbl => l_ou_lookup_tbl
,p_ou_count_rec => l_ou_count_rec
,p_ou_id_tbl => l_ou_id_tbl
);
csi_pricing_attribs_pvt.update_pricing_attribs
( 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_pricing_attribs_rec => p_pricing_attrib_tbl(tab_row)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_item_instance_pvt.update_extended_attrib_values
( 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_ext_attrib_rec => p_ext_attrib_values_tbl(tab_row)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
-- ,p_ext_id_tbl => l_ext_id_tbl
-- ,p_ext_count_rec => l_ext_count_rec
-- ,p_ext_attr_tbl => l_ext_attr_tbl
-- ,p_ext_cat_tbl => l_ext_cat_tbl
);
csi_asset_pvt.update_instance_asset
(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_instance_asset_rec => p_asset_assignment_tbl(tab_row)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lookup_tbl => l_asset_lookup_tbl
,p_asset_count_rec => l_asset_count_rec
,p_asset_id_tbl => l_asset_id_tbl
,p_asset_loc_tbl => l_asset_loc_tbl
);
,p_validation_mode => 'UPDATE'
)
THEN
FND_MESSAGE.SET_NAME('CSI','CSI_CONFIG_KEY_EXISTS');
SELECT component_instance_type
,config_hdr_id
,config_rev_nbr
INTO l_component_ins_type
,l_config_hdr_id
,l_config_rev_nbr
FROM cz_config_items_v
WHERE instance_hdr_id = p_instance_rec.config_inst_hdr_id
AND instance_rev_nbr = p_instance_rec.config_inst_rev_num
AND config_item_id = p_instance_rec.config_inst_item_id;
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_config_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_config_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
IF l_updated
THEN
l_instance_rec:=l_temp_instance_rec;
SELECT object_version_number
INTO l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id=p_instance_rec.instance_id;
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
SELECT active_end_date
INTO l_active_end_date
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
SELECT 'x'
INTO l_dummy
FROM csi_i_version_labels
WHERE instance_id = p_instance_rec.instance_id
AND version_label = p_instance_rec.version_label
AND ROWNUM=1;
SELECT cip.party_id
INTO l_party_id
FROM csi_i_parties cip
WHERE cip.instance_id = p_instance_rec.instance_id
AND cip.relationship_type_code = 'OWNER';
-- OWNER Party_id and Internal_party_id selection moved up.
IF l_party_id IS NOT NULL AND
l_internal_party_id IS NOT NULL AND
l_party_id <> l_internal_party_id
THEN
-- End addition by sk for fixing bug 2245976
l_transaction_type := 'NEW';
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = p_txn_rec.transaction_id;
csi_gen_utility_pvt.put_line('Calling CSI_ITEM_INSTANCE_VUHK.Update_Item_Instance_Post ..');
CSI_ITEM_INSTANCE_VUHK.Update_Item_Instance_Post
(
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_instance_rec => p_instance_rec
,p_ext_attrib_values_tbl => p_ext_attrib_values_tbl
,p_party_tbl => p_party_tbl
,p_account_tbl => p_account_tbl
,p_pricing_attrib_tbl => p_pricing_attrib_tbl
,p_org_assignments_tbl => p_org_assignments_tbl
,p_asset_assignment_tbl => p_asset_assignment_tbl
,p_txn_rec => p_txn_rec
,x_instance_id_lst => x_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_VUHK.Update_Item_Instance_Post API ');
csi_gen_utility_pvt.put_line('ERROR FROM JTF_USR_HKS.Ok_to_execute API Update Post Vertical');
SELECT source_code, owner_party_id
INTO l_source_code, l_owner_party_id
FROM csi_item_instances
WHERE instance_id = l_instance_id;
csi_gen_utility_pvt.put_line('Before calling update instance event');
csi_gen_utility_pvt.put_line('Calling Update Instance Event');
CSI_BUSINESS_EVENT_PVT.UPDATE_INSTANCE_EVENT
( 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_instance_id => l_instance_id
,p_subject_instance_id => null
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_gen_utility_pvt.put_line(' Error from CSI_BUSINESS_EVENT.UPDATE_INSTANCE_EVENT');
ROLLBACK TO update_item_instance;
ROLLBACK TO update_item_instance;
ROLLBACK TO update_item_instance;
END update_item_instance;
SELECT instance_hdr_id
,config_item_id
,has_failures
FROM cz_config_details_v d
,cz_config_hdrs_v h
WHERE d.instance_hdr_id = h.config_hdr_id
AND d.instance_rev_nbr = h.config_rev_nbr
AND d.component_instance_type = 'I'
AND d.config_hdr_id = p_config_hdr_id
AND d.config_rev_nbr = p_config_rev_nbr;
SELECT instance_id
,object_version_number
,config_valid_status
FROM csi_item_instances
WHERE config_inst_hdr_id=p_config_inst_hdr_id
AND config_inst_item_id=p_config_inst_item_id;
SELECT has_failures
,config_status
FROM cz_config_details_v d
,cz_config_hdrs h
WHERE d.instance_hdr_id = p_config_ins_hdr_id
AND d.instance_rev_nbr = p_config_ins_rev_nbr
-- AND d.component_instance_type = 'I'
AND d.config_hdr_id = h.config_hdr_id
AND d.config_rev_nbr = h.config_rev_nbr;
SELECT instance_id
,object_version_number
,config_valid_status
FROM csi_item_instances
WHERE config_inst_hdr_id=p_config_inst_hdr_id
AND config_inst_item_id=p_config_inst_item_id;
,p_validation_mode => 'UPDATE'
)
THEN
FND_MESSAGE.SET_NAME('CSI','CSI_CONFIG_KEY_EXISTS');
SELECT component_instance_type
,config_hdr_id
,config_rev_nbr
INTO l_component_ins_type
,l_config_hdr_id
,l_config_rev_nbr
FROM cz_config_items_v
WHERE instance_hdr_id = p_instance_rec.config_inst_hdr_id
AND instance_rev_nbr = p_instance_rec.config_inst_rev_num
AND config_item_id = p_instance_rec.config_inst_item_id;
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_config_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
csi_item_instance_pvt.update_item_instance
(
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_instance_rec => l_config_instance_rec
,p_txn_rec => p_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
);
csi_gen_utility_pvt.put_line( 'Error from UPDATE_ITEM_INSTANCE_PVT..');
UPDATE CSI_TRANSACTIONS
set contracts_invoked = 'Y'
where transaction_id = p_txn_rec.transaction_id;
l_select_stmt VARCHAR2(20000) := '';
x_select_stmt => l_select_stmt ,
p_active_instance_only => p_active_instance_only);
dbms_sql.parse(l_cur_get_inst_rel, l_select_stmt , dbms_sql.native);
l_select_stmt VARCHAR2(20000) := ' select instance_id,instance_number, external_reference, '||
'inventory_item_id,inventory_revision,inv_master_organization_id,serial_number, '||
'mfg_serial_number_flag,lot_number,quantity,unit_of_measure,accounting_class_code, '||
'instance_condition_id,instance_status_id,customer_view_flag,merchant_view_flag, '||
'sellable_flag,system_id,instance_type_code,active_start_date,active_end_date, '||
'location_type_code,location_id,inv_organization_id,inv_subinventory_name,inv_locator_id, '||
'pa_project_id,pa_project_task_id,in_transit_order_line_id,wip_job_id,po_order_line_id, '||
'last_oe_order_line_id,last_oe_rma_line_id,last_po_po_line_id,last_oe_po_number, '||
'last_wip_job_id,last_pa_project_id,last_pa_task_id,last_oe_agreement_id,install_date, '||
'manually_created_flag,return_by_date,actual_return_date,creation_complete_flag, '||
'completeness_flag,context,attribute1,attribute2,attribute3,attribute4,attribute5, '||
'attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,attribute12, '||
'attribute13,attribute14,attribute15,object_version_number,last_txn_line_detail_id, '||
'install_location_type_code,install_location_id,instance_usage_code,last_vld_organization_id, '||
'config_inst_hdr_id,config_inst_rev_num,config_inst_item_id,config_valid_status, '||
'instance_description,network_asset_flag,maintainable_flag,pn_location_id, '||
'asset_criticality_code,category_id,equipment_gen_object_id,instantiation_flag, '||
'linear_location_id,operational_log_flag,checkin_status,supplier_warranty_exp_date,attribute16, '||
'attribute17,attribute18,attribute19,attribute20,attribute21,attribute22,attribute23, '||
'attribute24,attribute25,attribute26,attribute27,attribute28,attribute29,attribute30, '||
-- Addition of columns for FA Integration
'purchase_unit_price, purchase_currency_code, payables_unit_price, payables_currency_code, '||
'sales_unit_price, sales_currency_code, operational_status_code '||
-- End addition of columns for FA Integration
'from csi_item_instances where instance_id = :instance_id ';
dbms_sql.parse(l_cur_get_instance_rel, l_select_stmt , dbms_sql.native);
SELECT last_purge_date
INTO l_last_purge_date
FROM CSI_ITEM_INSTANCES
WHERE instance_id = p_instance_rec.instance_id;
l_select_stmt VARCHAR2(20000) := ' SELECT * FROM CSI_I_VERSION_LABELS ';
l_select_stmt := l_select_stmt || ' where '||l_where_clause;
dbms_sql.parse(l_get_ver_cursor_id, l_select_stmt , dbms_sql.native);
/* Procedure name: Update_version_label */
/* Description : procedure for Update */
/* version label for */
/* an Item Instance */
/*----------------------------------------------------*/
PROCEDURE update_version_label
( p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_version_label_tbl IN csi_datastructures_pub.version_label_tbl
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,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_VERSION_LABEL';
SAVEPOINT update_version_label_pub;
csi_gen_utility_pvt.put_line( 'update_version_label');
csi_gen_utility_pvt.put_line( 'update_version_label:'||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list );
csi_item_instance_pvt.update_version_label
( 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_version_label_rec => p_version_label_tbl(l_count)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
ROLLBACK TO update_version_label_pub;
ROLLBACK TO update_version_label_pub;
ROLLBACK TO update_version_label_pub;
END update_version_label;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_VERSION_LABEL';
l_select_stmt VARCHAR2(20000);
SELECT /*+ INDEX(iea CSI_I_EXTENDED_ATTRIBS_N04)*/ -- Added by sguthiva for bug 2367664
iea.attribute_id attribute_id
,iea.attribute_level attribute_level
,iea.master_organization_id master_organization_id
,iea.inventory_item_id inventory_item_id
,iea.item_category_id item_category_id
,iea.instance_id instance_id
,iea.attribute_code attribute_code
,iea.attribute_name attribute_name
,iea.attribute_category attribute_category
,iea.description description
,iea.active_start_date active_start_date
,iea.active_end_date active_end_date
,iea.context context
,iea.attribute1 attribute1
,iea.attribute2 attribute2
,iea.attribute3 attribute3
,iea.attribute4 attribute4
,iea.attribute5 attribute5
,iea.attribute6 attribute6
,iea.attribute7 attribute7
,iea.attribute8 attribute8
,iea.attribute9 attribute9
,iea.attribute10 attribute10
,iea.attribute11 attribute11
,iea.attribute12 attribute12
,iea.attribute13 attribute13
,iea.attribute14 attribute14
,iea.attribute15 attribute15
,iea.object_version_number object_version_number
FROM csi_i_extended_attribs iea
WHERE attribute_level = 'GLOBAL'
UNION ALL
SELECT /*+ INDEX(iea CSI_I_EXTENDED_ATTRIBS_N01)*/ -- Added by sguthiva for bug 2367664
iea.attribute_id attribute_id
,iea.attribute_level attribute_level
,iea.master_organization_id master_organization_id
,iea.inventory_item_id inventory_item_id
,iea.item_category_id item_category_id
,iea.instance_id instance_id
,iea.attribute_code attribute_code
,iea.attribute_name attribute_name
,iea.attribute_category attribute_category
,iea.description description
,iea.active_start_date active_start_date
,iea.active_end_date active_end_date
,iea.context context
,iea.attribute1 attribute1
,iea.attribute2 attribute2
,iea.attribute3 attribute3
,iea.attribute4 attribute4
,iea.attribute5 attribute5
,iea.attribute6 attribute6
,iea.attribute7 attribute7
,iea.attribute8 attribute8
,iea.attribute9 attribute9
,iea.attribute10 attribute10
,iea.attribute11 attribute11
,iea.attribute12 attribute12
,iea.attribute13 attribute13
,iea.attribute14 attribute14
,iea.attribute15 attribute15
,iea.object_version_number object_version_number
FROM csi_i_extended_attribs iea
WHERE attribute_level = 'INSTANCE'
AND instance_id = p_instance_id
UNION ALL
SELECT /*+ INDEX(ia CSI_ITEM_INSTANCES_U01)
INDEX(iea CSI_I_EXTENDED_ATTRIBS_N01)
*/ -- Added by sguthiva for bug 2367664
iea.attribute_id attribute_id
,iea.attribute_level attribute_level
,iea.master_organization_id master_organization_id
,iea.inventory_item_id inventory_item_id
,iea.item_category_id item_category_id
,iea.instance_id instance_id
,iea.attribute_code attribute_code
,iea.attribute_name attribute_name
,iea.attribute_category attribute_category
,iea.description description
,iea.active_start_date active_start_date
,iea.active_end_date active_end_date
,iea.context context
,iea.attribute1 attribute1
,iea.attribute2 attribute2
,iea.attribute3 attribute3
,iea.attribute4 attribute4
,iea.attribute5 attribute5
,iea.attribute6 attribute6
,iea.attribute7 attribute7
,iea.attribute8 attribute8
,iea.attribute9 attribute9
,iea.attribute10 attribute10
,iea.attribute11 attribute11
,iea.attribute12 attribute12
,iea.attribute13 attribute13
,iea.attribute14 attribute14
,iea.attribute15 attribute15
,iea.object_version_number object_version_number
FROM csi_i_extended_attribs iea, csi_item_instances ia
WHERE iea.attribute_level = 'ITEM'
AND iea.inventory_item_id = ia.inventory_item_id --p_inv_item_id
AND iea.master_organization_id = ia.inv_master_organization_id --p_org_id;
SELECT /*+ INDEX(ia CSI_ITEM_INSTANCES_U01)
INDEX(iea CSI_I_EXTENDED_ATTRIBS_N01)
INDEX(ic MTL_ITEM_CATEGORIES_U1)
*/ -- Added by sguthiva for bug 2367664
iea.attribute_id attribute_id
,iea.attribute_level attribute_level
,iea.master_organization_id master_organization_id
,iea.inventory_item_id inventory_item_id
,iea.item_category_id item_category_id
,iea.instance_id instance_id
,iea.attribute_code attribute_code
,iea.attribute_name attribute_name
,iea.attribute_category attribute_category
,iea.description description
,iea.active_start_date active_start_date
,iea.active_end_date active_end_date
,iea.context context
,iea.attribute1 attribute1
,iea.attribute2 attribute2
,iea.attribute3 attribute3
,iea.attribute4 attribute4
,iea.attribute5 attribute5
,iea.attribute6 attribute6
,iea.attribute7 attribute7
,iea.attribute8 attribute8
,iea.attribute9 attribute9
,iea.attribute10 attribute10
,iea.attribute11 attribute11
,iea.attribute12 attribute12
,iea.attribute13 attribute13
,iea.attribute14 attribute14
,iea.attribute15 attribute15
,iea.object_version_number object_version_number
FROM csi_i_extended_attribs iea
,csi_item_instances ia
,mtl_item_categories ic
WHERE iea.attribute_level = 'CATEGORY'
-- AND iea.inventory_item_id = ia.inventory_item_id -- commented for Bug # 3189494
-- AND iea.master_organization_id = ia.inv_master_organization_id -- commented for Bug # 3189494
AND ic.organization_id = ia.inv_master_organization_id
AND ic.inventory_item_id = ia.inventory_item_id
AND ic.category_id = iea.item_category_id
AND ia.instance_id = p_instance_id;
l_select_stmt := 'SELECT cv.attribute_value_id attribute_value_id' ||
',cv.attribute_id attribute_id' ||
',cv.instance_id instance_id' ||
',ca.attribute_code attribute_code' ||
',cv.attribute_value attribute_value' ||
',cv.active_start_date active_start_date' ||
',cv.active_end_date active_end_date' ||
',cv.context context' ||
',cv.attribute1 attribute1' ||
',cv.attribute2 attribute2' ||
',cv.attribute3 attribute3' ||
',cv.attribute4 attribute4' ||
',cv.attribute5 attribute5' ||
',cv.attribute6 attribute6' ||
',cv.attribute7 attribute7' ||
',cv.attribute8 attribute8' ||
',cv.attribute9 attribute9' ||
',cv.attribute10 attribute10' ||
',cv.attribute11 attribute11' ||
',cv.attribute12 attribute12' ||
',cv.attribute13 attribute13' ||
',cv.attribute14 attribute14' ||
',cv.attribute15 attribute15' ||
',cv.object_version_number object_version_number '||
'FROM csi_iea_values cv, csi_i_extended_attribs ca ' ||
'WHERE cv.attribute_id = ca.attribute_id' ;
l_select_stmt := l_select_stmt || ' AND '||l_where_clause;
dbms_sql.parse(l_cur_get_ext, l_select_stmt , dbms_sql.native);
/* procedure name: update_extended_attrib_values */
/* description : Updates extended attrib values for */
/* for an item instance */
/*----------------------------------------------------*/
PROCEDURE update_extended_attrib_values
( p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_ext_attrib_tbl IN csi_datastructures_pub.extend_attrib_values_tbl
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,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_extended_attrib_values';
SAVEPOINT update_extended_attrib_values;
csi_gen_utility_pvt.put_line( 'update_extended_attrib_values');
csi_item_instance_pvt.update_extended_attrib_values
(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_ext_attrib_rec => p_ext_attrib_tbl(tab_row)
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
-- ,p_ext_id_tbl => l_ext_id_tbl
-- ,p_ext_count_rec => l_ext_count_rec
-- ,p_ext_attr_tbl => l_ext_attr_tbl
-- ,p_ext_cat_tbl => l_ext_cat_tbl
);
csi_gen_utility_pvt.put_line( ' Failed Pub:update_extended_attrib_values..');
ROLLBACK TO update_extended_attrib_values;
ROLLBACK TO update_extended_attrib_values;
ROLLBACK TO update_extended_attrib_values;
END update_extended_attrib_values;
l_api_name CONSTANT VARCHAR2(30) := 'delete_extended_attrib_values';
SAVEPOINT delete_extended_attrib_values;
csi_gen_utility_pvt.put_line( 'delete_extended_attrib_values');
ROLLBACK TO delete_extended_attrib_values;
ROLLBACK TO delete_extended_attrib_values;
ROLLBACK TO delete_extended_attrib_values;
SELECT quantity, install_date, active_end_date
FROM csi_item_instances
WHERE instance_id = cp_instance_id;
SELECT 'x' INTO l_exists
FROM dual
WHERE EXISTS (SELECT 'x'
FROM csi_ii_relationships
WHERE object_id = p_instance_rec.instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND NVL (active_end_date,(sysdate+1)) > sysdate);
SELECT 'x'
INTO l_exists
FROM csi_ii_relationships
WHERE object_id = p_instance_rec.instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND NVL (active_end_date,(sysdate+1)) > sysdate
AND ROWNUM=1;