[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OPEN line_csr FOR SELECT I.shp_list_item_id,
I.item_type_code
FROM ibe_sh_shp_list_items I
WHERE I.shp_list_id = l_list_header_id
AND I.item_type_code NOT IN ('CFG', 'OPT', 'SRV');
OPEN rel_line_csr FOR SELECT R.related_shp_list_item_id
FROM ibe_sh_shlitem_rels R
START WITH R.shp_list_item_id = l_line_id
CONNECT BY R.shp_list_item_id = PRIOR R.related_shp_list_item_id;
OPEN rel_line_csr FOR 'SELECT shp_list_item_id, '||
'related_shp_list_item_id, ' ||
'relationship_type_code ' ||
'FROM ibe_sh_shlitem_rels ' ||
'START WITH shp_list_item_id = :1 '||
'CONNECT BY shp_list_item_id = PRIOR related_shp_list_item_id'
USING p_list_line_id_tbl(i);
OPEN l_csr FOR SELECT R.shp_list_item_id,
R.related_shp_list_item_id,
R.relationship_type_code
FROM ibe_sh_shp_list_items L,
ibe_sh_shlitem_rels R
WHERE L.shp_list_id = l_list_header_id
AND R.shp_list_item_id = L.shp_list_item_id;
SELECT shp_list_item_id,
inventory_item_id,
quantity,
uom_code,
organization_id,
config_header_id,
config_revision_num,
complete_configuration_flag,
valid_configuration_flag,
item_type_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO l_shp_list_item_id,
l_inventory_item_id,
l_quantity,
l_uom_code,
l_organization_id,
l_config_header_id,
l_config_revision_num,
l_complete_configuration_flag,
l_valid_configuration_flag,
l_item_type_code,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15
FROM ibe_sh_shp_list_items
WHERE shp_list_item_id = p_list_line_id_tbl(k);
* for same standard item. Don't add a new line and just update the quantity.
*/
IF l_list_line_tbl_index <> 0 THEN
x_list_line_tbl(l_list_line_tbl_index).quantity
:= x_list_line_tbl(l_list_line_tbl_index).quantity + l_quantity;
SELECT shp_list_item_id,
quantity
INTO l_list_line_id,
l_quantity
FROM ibe_sh_shp_list_items
WHERE shp_list_id = p_list_header_id
AND organization_id = l_organization_id
AND item_type_code = 'STD'
AND inventory_item_id = l_inventory_item_id
AND uom_code = l_uom_code;
x_list_line_tbl(i).operation_code := 'UPDATE';
PROCEDURE Delete_Config_From_Shp_List(
p_shp_list_ids IN jtf_number_table,
p_shp_list_line_ids IN jtf_number_table,
p_usage_exists IN OUT NOCOPY NUMBER,
p_error_message IN OUT NOCOPY VARCHAR2,
p_return_value IN OUT NOCOPY NUMBER
)
IS
TYPE csr_type is REF CURSOR;
l_cursor_string CONSTANT VARCHAR2(200) := 'SELECT DISTINCT ' ||
'config_header_id, ' ||
'config_revision_num '||
' FROM ibe_sh_shp_list_items ' ||
' WHERE';
IBE_Util.Debug('Calling Delete_Configuration for' || l_line_config_id || ', ' || l_line_config_revnum);
CZ_CF_API.delete_configuration(
config_hdr_id => l_line_config_id ,
config_rev_nbr => l_line_config_revnum,
usage_exists => p_usage_exists ,
error_message => p_error_message ,
return_value => p_return_value);
END Delete_Config_From_Shp_List;
PROCEDURE Delete(
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_shop_list_ids IN jtf_number_table ,
p_obj_ver_numbers IN jtf_number_table
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete';
SELECT shp_list_item_id
FROM ibe_sh_shp_list_items
WHERE shp_list_id = l_shp_list_id;
SELECT ISSR.shlitem_rel_id
FROM ibe_sh_shlitem_rels ISSR,
ibe_sh_shp_list_items ISSLI
WHERE ISSR.shp_list_item_id = ISSLI.shp_list_item_id
AND ISSLI.shp_list_id = l_shp_list_id;
SAVEPOINT Delete_PVT;
IBE_Util.Debug('IBE_Shop_List_PVT.Delete(+)');
IBE_Util.Debug('Delete Configuration Using list ids - Start');
Delete_Config_From_Shp_List(
p_shp_list_ids => p_shop_list_ids,
p_shp_list_line_ids => NULL,
p_usage_exists => l_usage_exists,
p_error_message => x_msg_data,
p_return_value => l_return_value);
IBE_Util.Debug('Done CZ_CF_API.delete_configuration at '
|| TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
IBE_ShopList_Line_Relation_PKG.Delete_Row(
p_SHLITEM_REL_ID => l_shop_list_rel_line_id);
IBE_Shop_List_Line_PKG.Delete_Row(
p_SHP_LIST_ITEM_ID => l_shop_list_line_id);
IBE_Shop_List_Header_PKG.Delete_Row(
p_SHP_LIST_ID => p_shop_list_ids(i),
p_OBJECT_VERSION_NUMBER => p_obj_ver_numbers(i));
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
IBE_Util.Debug('IBE_Shop_List_PVT.Delete(-)');
ROLLBACK TO Delete_PVT;
ROLLBACK TO Delete_PVT;
ROLLBACK TO Delete_PVT;
END Delete;
PROCEDURE Delete_All_Lines(
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_shop_list_ids IN jtf_number_table ,
p_obj_ver_numbers IN jtf_number_table
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_All_Lines';
SELECT SHP_LIST_ITEM_ID
FROM IBE_SH_SHP_LIST_ITEMS
WHERE SHP_LIST_ID = l_Shp_List_Id;
SELECT IBE_SH_SHLITEM_RELS.SHLITEM_REL_ID
FROM IBE_SH_SHLITEM_RELS, IBE_SH_SHP_LIST_ITEMS
WHERE IBE_SH_SHLITEM_RELS.SHP_LIST_ITEM_ID = IBE_SH_SHP_LIST_ITEMS.SHP_LIST_ITEM_ID
AND IBE_SH_SHP_LIST_ITEMS.SHP_LIST_ID = l_Shp_List_Id;
SAVEPOINT Delete_All_Lines_PVT;
IBE_Util.Debug('IBE_Shop_List_PVT.Delete_All_Lines(+)');
IBE_Util.Debug('Delete Configuration Using list ids - Start');
Delete_Config_From_Shp_List(
p_shp_list_ids => p_shop_list_ids,
p_shp_list_line_ids => NULL,
p_usage_exists => l_usage_exists,
p_error_message => x_msg_data,
p_return_value => l_return_value);
IBE_Util.Debug('Done CZ_CF_API.delete_configuration at '
|| TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
IBE_ShopList_Line_Relation_PKG.Delete_Row(
p_SHLITEM_REL_ID => l_shop_list_rel_line_id);
IBE_Shop_List_Line_PKG.Delete_Row(
p_SHP_LIST_ITEM_ID => l_shop_list_line_id);
UPDATE IBE_SH_SHP_LISTS
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE SHP_LIST_ID = p_shop_list_ids(i)
AND OBJECT_VERSION_NUMBER = p_obj_ver_numbers(i);
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
IBE_Util.Debug('IBE_Shop_List_PVT.Delete_All_Lines(-)');
ROLLBACK TO Delete_All_Lines_PVT;
ROLLBACK TO Delete_All_Lines_PVT;
ROLLBACK TO Delete_All_Lines_PVT;
END Delete_All_Lines;
PROCEDURE Delete_Line(
p_object_version_number IN NUMBER,
p_Shp_List_Item_Id IN NUMBER
)
IS
l_shp_list_item_id_tbl jtf_number_table;
SELECT RELATED_SHP_LIST_ITEM_ID
FROM IBE_SH_SHLITEM_RELS
WHERE SHP_LIST_ITEM_ID = p_Shp_List_Item_Id;
SELECT SHLITEM_REL_ID
FROM IBE_SH_SHLITEM_RELS
WHERE SHP_LIST_ITEM_ID = l_shp_list_line_id;
IBE_Util.Debug('IBE_Shop_List_PVT.Delete_Line(+)...');
SELECT COUNT(*) into l_count
FROM IBE_SH_SHLITEM_RELS
WHERE SHP_LIST_ITEM_ID = p_Shp_List_Item_Id;
IBE_Shop_List_PVT.Delete_Line(
p_object_version_number => p_object_version_number,
p_Shp_List_Item_Id => l_shp_list_item_id_tbl(i));
IBE_ShopList_Line_Relation_PKG.Delete_Row(
p_SHLITEM_REL_ID => l_shop_list_rel_line_id);
IBE_Shop_List_Line_PKG.Delete_Row(
p_SHP_LIST_ITEM_ID => p_SHP_LIST_ITEM_ID,
p_object_version_number => p_object_version_number);
END Delete_Line;
PROCEDURE Delete_Lines(
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_shop_list_line_ids IN jtf_number_table ,
p_obj_ver_numbers IN jtf_number_table
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Lines';
SAVEPOINT Delete_Lines_PVT;
IBE_Util.Debug('IBE_Shop_List_PVT.Delete_Lines(+)');
IBE_Util.Debug('Delete Configuration Using line ids - Start');
Delete_Config_From_Shp_List(
p_shp_list_ids => NULL,
p_shp_list_line_ids => p_shop_list_line_ids,
p_usage_exists => l_usage_exists,
p_error_message => x_msg_data,
p_return_value => l_return_value);
IBE_Util.Debug('Done CZ_CF_API.delete_configuration at '
|| TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
select SHP_LIST_ID into l_shop_list_id
from IBE_SH_SHP_LIST_ITEMS
where SHP_LIST_ITEM_ID = p_shop_list_line_ids(1);
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
Delete_Line(
p_object_version_number => p_obj_ver_numbers(i),
p_Shp_List_Item_Id => p_shop_list_line_ids(i));
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
UPDATE IBE_SH_SHP_LISTS
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE SHP_LIST_ID = l_shop_list_id;
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
IBE_Util.Debug('IBE_Shop_List_PVT.Delete_Lines(-)');
ROLLBACK TO Delete_Lines_PVT;
ROLLBACK TO Delete_Lines_PVT;
ROLLBACK TO Delete_Lines_PVT;
END Delete_Lines;
IBE_Shop_List_Header_PKG.Insert_Row(
x_shp_list_id => x_sl_header_id ,
p_request_id => p_sl_header_rec.request_id ,
p_program_application_id => p_sl_header_rec.program_application_id ,
p_program_id => p_sl_header_rec.program_id ,
p_program_update_date => p_sl_header_rec.program_update_date ,
p_object_version_number => p_sl_header_rec.object_version_number ,
p_created_by => p_sl_header_rec.created_by ,
p_creation_date => p_sl_header_rec.creation_date ,
p_last_updated_by => p_sl_header_rec.last_updated_by ,
p_last_update_date => p_sl_header_rec.last_update_date ,
p_last_update_login => p_sl_header_rec.last_update_login ,
p_party_id => p_sl_header_rec.party_id ,
p_cust_account_id => p_sl_header_rec.cust_account_id ,
p_shopping_list_name => p_sl_header_rec.shopping_list_name ,
p_description => p_sl_header_rec.description ,
p_attribute_category => p_sl_header_rec.attribute_category ,
p_attribute1 => p_sl_header_rec.attribute1 ,
p_attribute2 => p_sl_header_rec.attribute2 ,
p_attribute3 => p_sl_header_rec.attribute3 ,
p_attribute4 => p_sl_header_rec.attribute4 ,
p_attribute5 => p_sl_header_rec.attribute5 ,
p_attribute6 => p_sl_header_rec.attribute6 ,
p_attribute7 => p_sl_header_rec.attribute7 ,
p_attribute8 => p_sl_header_rec.attribute8 ,
p_attribute9 => p_sl_header_rec.attribute9 ,
p_attribute10 => p_sl_header_rec.attribute10 ,
p_attribute11 => p_sl_header_rec.attribute11 ,
p_attribute12 => p_sl_header_rec.attribute12 ,
p_attribute13 => p_sl_header_rec.attribute13 ,
p_attribute14 => p_sl_header_rec.attribute14 ,
p_attribute15 => p_sl_header_rec.attribute15 ,
p_org_id => p_sl_header_rec.org_id);
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
IBE_Shop_List_Header_PKG.Update_Row(
p_shp_list_id => p_sl_header_rec.shp_list_id ,
p_request_id => p_sl_header_rec.request_id ,
p_program_application_id => p_sl_header_rec.program_application_id ,
p_program_id => p_sl_header_rec.program_id ,
p_program_update_date => p_sl_header_rec.program_update_date ,
p_object_version_number => p_sl_header_rec.object_version_number ,
p_created_by => p_sl_header_rec.created_by ,
p_creation_date => p_sl_header_rec.creation_date ,
p_last_updated_by => p_sl_header_rec.last_updated_by ,
p_last_update_date => p_sl_header_rec.last_update_date ,
p_last_update_login => p_sl_header_rec.last_update_login ,
p_party_id => p_sl_header_rec.party_id ,
p_cust_account_id => p_sl_header_rec.cust_account_id ,
p_shopping_list_name => p_sl_header_rec.shopping_list_name ,
p_description => p_sl_header_rec.description ,
p_attribute_category => p_sl_header_rec.attribute_category ,
p_attribute1 => p_sl_header_rec.attribute1 ,
p_attribute2 => p_sl_header_rec.attribute2 ,
p_attribute3 => p_sl_header_rec.attribute3 ,
p_attribute4 => p_sl_header_rec.attribute4 ,
p_attribute5 => p_sl_header_rec.attribute5 ,
p_attribute6 => p_sl_header_rec.attribute6 ,
p_attribute7 => p_sl_header_rec.attribute7 ,
p_attribute8 => p_sl_header_rec.attribute8 ,
p_attribute9 => p_sl_header_rec.attribute9 ,
p_attribute10 => p_sl_header_rec.attribute10 ,
p_attribute11 => p_sl_header_rec.attribute11 ,
p_attribute12 => p_sl_header_rec.attribute12 ,
p_attribute13 => p_sl_header_rec.attribute13 ,
p_attribute14 => p_sl_header_rec.attribute14 ,
p_attribute15 => p_sl_header_rec.attribute15 ,
p_org_id => p_sl_header_rec.org_id);
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
SELECT shp_list_item_id, quantity
INTO l_shp_list_item_id, l_quantity
FROM ibe_sh_shp_list_items
WHERE shp_list_id = p_sl_header_rec.shp_list_id
AND inventory_item_id = l_sl_line_rec.inventory_item_id
AND organization_id = l_sl_line_rec.organization_id
AND uom_code = l_sl_line_rec.uom_code
AND item_type_code = 'STD';
IBE_Shop_List_Line_PKG.Insert_Row(
x_shp_list_item_id => l_shp_list_item_id ,
p_object_version_number => l_sl_line_rec.object_version_number ,
p_creation_date => l_sl_line_rec.creation_date ,
p_created_by => l_sl_line_rec.created_by ,
p_last_updated_by => l_sl_line_rec.last_updated_by ,
p_last_update_date => l_sl_line_rec.last_update_date ,
p_last_update_login => l_sl_line_rec.last_update_login ,
p_request_id => l_sl_line_rec.request_id ,
p_program_id => l_sl_line_rec.program_id ,
p_program_application_id => l_sl_line_rec.program_application_id ,
p_program_update_date => l_sl_line_rec.program_update_date ,
p_shp_list_id => x_sl_header_id ,
p_inventory_item_id => l_sl_line_rec.inventory_item_id ,
p_organization_id => l_sl_line_rec.organization_id ,
p_uom_code => l_sl_line_rec.uom_code ,
p_quantity => l_sl_line_rec.quantity ,
p_config_header_id => l_sl_line_rec.config_header_id ,
p_config_revision_num => l_sl_line_rec.config_revision_num ,
p_complete_configuration_flag => l_sl_line_rec.complete_configuration_flag ,
p_valid_configuration_flag => l_sl_line_rec.valid_configuration_flag ,
p_item_type_code => l_sl_line_rec.item_type_code ,
p_attribute_category => l_sl_line_rec.attribute_category ,
p_attribute1 => l_sl_line_rec.attribute1 ,
p_attribute2 => l_sl_line_rec.attribute2 ,
p_attribute3 => l_sl_line_rec.attribute3 ,
p_attribute4 => l_sl_line_rec.attribute4 ,
p_attribute5 => l_sl_line_rec.attribute5 ,
p_attribute6 => l_sl_line_rec.attribute6 ,
p_attribute7 => l_sl_line_rec.attribute7 ,
p_attribute8 => l_sl_line_rec.attribute8 ,
p_attribute9 => l_sl_line_rec.attribute9 ,
p_attribute10 => l_sl_line_rec.attribute10 ,
p_attribute11 => l_sl_line_rec.attribute11 ,
p_attribute12 => l_sl_line_rec.attribute12 ,
p_attribute13 => l_sl_line_rec.attribute13 ,
p_attribute14 => l_sl_line_rec.attribute14 ,
p_attribute15 => l_sl_line_rec.attribute15 ,
p_org_id => l_sl_line_rec.org_id);
IBE_Shop_List_Line_PKG.Update_Row(
p_shp_list_item_id => l_sl_line_rec.shp_list_item_id ,
p_object_version_number => l_sl_line_rec.object_version_number ,
p_creation_date => l_sl_line_rec.creation_date ,
p_created_by => l_sl_line_rec.created_by ,
p_last_updated_by => l_sl_line_rec.last_updated_by ,
p_last_update_date => l_sl_line_rec.last_update_date ,
p_last_update_login => l_sl_line_rec.last_update_login ,
p_request_id => l_sl_line_rec.request_id ,
p_program_id => l_sl_line_rec.program_id ,
p_program_application_id => l_sl_line_rec.program_application_id ,
p_program_update_date => l_sl_line_rec.program_update_date ,
p_shp_list_id => l_sl_line_rec.shp_list_id ,
p_inventory_item_id => l_sl_line_rec.inventory_item_id ,
p_organization_id => l_sl_line_rec.organization_id ,
p_uom_code => l_sl_line_rec.uom_code ,
p_quantity => l_sl_line_rec.quantity ,
p_config_header_id => l_sl_line_rec.config_header_id ,
p_config_revision_num => l_sl_line_rec.config_revision_num ,
p_complete_configuration_flag => l_sl_line_rec.complete_configuration_flag ,
p_valid_configuration_flag => l_sl_line_rec.valid_configuration_flag ,
p_item_type_code => l_sl_line_rec.item_type_code ,
p_attribute_category => l_sl_line_rec.attribute_category ,
p_attribute1 => l_sl_line_rec.attribute1 ,
p_attribute2 => l_sl_line_rec.attribute2 ,
p_attribute3 => l_sl_line_rec.attribute3 ,
p_attribute4 => l_sl_line_rec.attribute4 ,
p_attribute5 => l_sl_line_rec.attribute5 ,
p_attribute6 => l_sl_line_rec.attribute6 ,
p_attribute7 => l_sl_line_rec.attribute7 ,
p_attribute8 => l_sl_line_rec.attribute8 ,
p_attribute9 => l_sl_line_rec.attribute9 ,
p_attribute10 => l_sl_line_rec.attribute10 ,
p_attribute11 => l_sl_line_rec.attribute11 ,
p_attribute12 => l_sl_line_rec.attribute12 ,
p_attribute13 => l_sl_line_rec.attribute13 ,
p_attribute14 => l_sl_line_rec.attribute14 ,
p_attribute15 => l_sl_line_rec.attribute15 ,
p_org_id => l_sl_line_rec.org_id);
IBE_ShopList_Line_Relation_PKG.Insert_Row(
x_shlitem_rel_id => l_sl_line_rel_id ,
p_request_id => p_sl_line_rel_tbl(i).request_id ,
p_program_application_id => p_sl_line_rel_tbl(i).program_application_id ,
p_program_id => p_sl_line_rel_tbl(i).program_id ,
p_program_update_date => p_sl_line_rel_tbl(i).program_update_date ,
p_object_version_number => p_sl_line_rel_tbl(i).object_version_number ,
p_created_by => p_sl_line_rel_tbl(i).created_by ,
p_creation_date => p_sl_line_rel_tbl(i).creation_date ,
p_last_updated_by => p_sl_line_rel_tbl(i).last_updated_by ,
p_last_update_date => p_sl_line_rel_tbl(i).last_update_date ,
p_last_update_login => p_sl_line_rel_tbl(i).last_update_login ,
p_shp_list_item_id => l_line_id_tbl(p_sl_line_rel_tbl(i).line_index),
p_related_shp_list_item_id => l_line_id_tbl(p_sl_line_rel_tbl(i).related_line_index),
p_relationship_type_code => p_sl_line_rel_tbl(i).relationship_type_code);
PROCEDURE Update_Config_Item_Lines(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2,
px_sl_line_tbl IN OUT NOCOPY SL_Line_Tbl_Type
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Config_Item_Lines';
IBE_Util.Debug('IBE_Shop_List_PVT.Update_Config_Item_Lines(+)');
IBE_Util.Debug('IBE_Shop_List_PVT.Update_Config_Item_Lines(-)');
END Update_Config_Item_Lines;
IBE_Shop_List_PVT.Delete_All_Lines(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_shop_list_ids => l_list_header_id_tbl,
p_obj_ver_numbers => l_obj_ver_num_tbl);
Update_Config_Item_Lines(
x_return_status => x_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
px_sl_line_tbl => l_sl_line_tbl);
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
p_last_update_date IN DATE := FND_API.G_MISS_DATE,
p_mode IN VARCHAR2 := 'MERGE' ,
p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_sl_header_rec IN SL_Header_Rec_Type ,
x_sl_header_id OUT NOCOPY NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Save_List_From_Quote';
IBE_Quote_Misc_pvt.Validate_User_Update
( p_init_msg_list => FND_API.G_TRUE
,p_quote_header_id => p_quote_header_id
,p_party_id => p_sl_header_rec.party_id
,p_cust_account_id => p_sl_header_rec.cust_account_id
,p_quote_retrieval_number => p_quote_retrieval_number
,p_validate_user => FND_API.G_TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IBE_Shop_List_PVT.Delete_All_Lines(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_shop_list_ids => l_list_header_id_tbl,
p_obj_ver_numbers => l_obj_ver_num_tbl);
SELECT DECODE(l_sl_header_rec.attribute_category, FND_API.G_MISS_CHAR, attribute_category, l_sl_header_rec.attribute_CATEGORY),
DECODE(l_sl_header_rec.attribute1, FND_API.G_MISS_CHAR, attribute1, l_sl_header_rec.attribute1),
DECODE(l_sl_header_rec.attribute2, FND_API.G_MISS_CHAR, attribute2, l_sl_header_rec.attribute2),
DECODE(l_sl_header_rec.attribute3, FND_API.G_MISS_CHAR, attribute3, l_sl_header_rec.attribute3),
DECODE(l_sl_header_rec.attribute4, FND_API.G_MISS_CHAR, attribute4, l_sl_header_rec.attribute4),
DECODE(l_sl_header_rec.attribute5, FND_API.G_MISS_CHAR, attribute5, l_sl_header_rec.attribute5),
DECODE(l_sl_header_rec.attribute6, FND_API.G_MISS_CHAR, attribute6, l_sl_header_rec.attribute6),
DECODE(l_sl_header_rec.attribute7, FND_API.G_MISS_CHAR, attribute7, l_sl_header_rec.attribute7),
DECODE(l_sl_header_rec.attribute8, FND_API.G_MISS_CHAR, attribute8, l_sl_header_rec.attribute8),
DECODE(l_sl_header_rec.attribute9, FND_API.G_MISS_CHAR, attribute9, l_sl_header_rec.attribute9),
DECODE(l_sl_header_rec.attribute10, FND_API.G_MISS_CHAR, attribute10, l_sl_header_rec.attribute10),
DECODE(l_sl_header_rec.attribute11, FND_API.G_MISS_CHAR, attribute11, l_sl_header_rec.attribute11),
DECODE(l_sl_header_rec.attribute12, FND_API.G_MISS_CHAR, attribute12, l_sl_header_rec.attribute12),
DECODE(l_sl_header_rec.attribute13, FND_API.G_MISS_CHAR, attribute13, l_sl_header_rec.attribute13),
DECODE(l_sl_header_rec.attribute14, FND_API.G_MISS_CHAR, attribute14, l_sl_header_rec.attribute14),
DECODE(l_sl_header_rec.attribute15, FND_API.G_MISS_CHAR, attribute15, l_sl_header_rec.attribute15)
INTO l_sl_header_rec.attribute_category,
l_sl_header_rec.attribute1,
l_sl_header_rec.attribute2,
l_sl_header_rec.attribute3,
l_sl_header_rec.attribute4,
l_sl_header_rec.attribute5,
l_sl_header_rec.attribute6,
l_sl_header_rec.attribute7,
l_sl_header_rec.attribute8,
l_sl_header_rec.attribute9,
l_sl_header_rec.attribute10,
l_sl_header_rec.attribute11,
l_sl_header_rec.attribute12,
l_sl_header_rec.attribute13,
l_sl_header_rec.attribute14,
l_sl_header_rec.attribute15
FROM aso_quote_headers
WHERE quote_header_id = p_quote_header_id;
OPEN l_csr FOR SELECT AQL.quote_line_id ,
AQL.inventory_item_id ,
AQL.quantity ,
AQL.uom_code ,
AQL.organization_id ,
AQL.item_type_code ,
AQLD.config_header_id ,
AQLD.config_revision_num ,
AQLD.complete_configuration_flag,
AQLD.valid_configuration_flag ,
AQL.attribute_category ,
AQL.attribute1 ,
AQL.attribute2 ,
AQL.attribute3 ,
AQL.attribute4 ,
AQL.attribute5 ,
AQL.attribute6 ,
AQL.attribute7 ,
AQL.attribute8 ,
AQL.attribute9 ,
AQL.attribute10 ,
AQL.attribute11 ,
AQL.attribute12 ,
AQL.attribute13 ,
AQL.attribute14 ,
AQL.attribute15 ,
AQL.pricing_line_type_indicator
FROM aso_quote_lines AQL,
aso_quote_line_details AQLD
WHERE AQL.quote_header_id = p_quote_header_id
AND AQL.quote_line_id = AQLD.quote_line_id(+)
ORDER BY AQL.quote_line_id;
OPEN l_csr_rel FOR SELECT ALR.quote_line_id,
ALR.related_quote_line_id,
ALR.relationship_type_code
FROM aso_line_relationships ALR,
aso_quote_lines AQL
WHERE ALR.quote_line_id = AQL.quote_line_id
AND AQL.quote_header_id = p_quote_header_id;
Update_Config_Item_Lines(
x_return_status => x_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
px_sl_line_tbl => l_sl_line_tbl);
FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
SELECT shp_list_item_id,
inventory_item_id,
quantity,
uom_code,
organization_id,
config_header_id,
config_revision_num,
complete_configuration_flag,
valid_configuration_flag,
item_type_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO l_shp_list_item_id,
l_inventory_item_id,
l_quantity,
l_uom_code,
l_organization_id,
l_config_header_id,
l_config_revision_num,
l_complete_configuration_flag,
l_valid_configuration_flag,
l_item_type_code,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15
FROM ibe_sh_shp_list_items
WHERE shp_list_item_id = p_list_line_id_tbl(k);
* for same standard item. Don't add a new line and just update the quantity.
*/
IF l_qte_line_tbl_index <> 0 THEN
x_qte_line_tbl(l_qte_line_tbl_index).quantity
:= x_qte_line_tbl(l_qte_line_tbl_index).quantity + l_quantity;
SELECT quote_line_id,
quantity
INTO l_quote_line_id,
l_quantity
FROM aso_quote_lines
WHERE quote_header_id = p_quote_header_id
AND organization_id = l_organization_id
AND item_type_code = 'STD'
AND inventory_item_id = l_inventory_item_id
AND uom_code = l_uom_code
AND currency_code = p_currency_code;
x_qte_line_tbl(i).operation_code := 'UPDATE';
OPEN l_csr FOR 'SELECT shp_list_item_id '||
'FROM ibe_sh_shlitem_rels ' ||
'START WITH related_shp_list_item_id = :1 '||
'CONNECT BY related_shp_list_item_id = PRIOR shp_list_item_id'
USING p_list_line_id_tbl(k);
lx_last_update_date DATE;
SELECT list_header_id
FROM qp_list_headers_vl
WHERE NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active+1, SYSDATE) >= SYSDATE
AND list_type_code = 'PRO'
AND active_flag = 'Y'
AND automatic_flag = 'Y'
AND ask_for_flag = 'Y'
AND currency_code = p_currency_code
AND name = p_promocode;
IBE_Quote_Save_pvt.DeleteAllLines(
p_api_version_number => p_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_quote_header_id => p_q_header_rec.quote_header_id,
p_last_update_date => p_q_header_rec.last_update_date,
p_sharee_number => p_quote_retrieval_number,
x_quote_header_id => lx_quote_header_id,
x_last_update_date => lx_last_update_date);
IBE_Quote_Misc_pvt.Update_Config_Item_Lines(
x_return_status => x_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
px_qte_line_dtl_tbl => l_qte_line_detail_tbl);
IBE_Util.Debug('Coming back from update_config_item_lines: lx_contMDL is Y');
x_last_update_date => lx_last_update_date,
x_Qte_Line_Tbl => lx_Qte_Line_Tbl);