The following lines contain the word 'select', 'insert', 'update' or 'delete':
fnd_message.set_name('IBE','IBE_M_SELECT_SUB_SECTION_PRMT');
fnd_message.set_name('IBE','IBE_M_SELECT_PRODUCT_PRMT');
SELECT 1
FROM ibe_dsp_sections_b
WHERE section_id = c_section_id
AND section_type_code = 'F';
SELECT 1
FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = c_master_mini_site_id
AND parent_section_id = c_section_id;
l_sql VARCHAR2(2000) := 'SELECT mic.inventory_item_id, '||
'msi.concatenated_segments, ms.description '||
'FROM mtl_item_categories mic, mtl_system_items_kfv msi, '||
' mtl_system_items_vl ms ' ||
'WHERE mic.inventory_item_id = ms.inventory_item_id ' ||
' AND mic.organization_id = ms.organization_id ' ||
' AND mic.organization_id = msi.organization_id ' ||
' AND mic.inventory_item_id = msi.inventory_item_id ' ||
' AND mic.category_set_id = :category_set_id ' ||
' AND mic.organization_id = :organization_id ' ||
' AND mic.category_id = :category_id ' ||
-- This is for fixing bug 3037399 and 3036491
' AND NOT(ms.replenish_to_order_flag = ' || '''' || 'Y' || '''' ||
' AND ms.base_item_id is not null ' ||
' AND ms.auto_created_config_flag = ' || '''' || 'Y' || '''' || ') ';
SELECT dest_object_id
FROM ibe_ct_relation_rules
WHERE relation_type_code = 'AUTOPLACEMENT'
AND origin_object_type = 'S'
AND dest_object_type = 'C'
AND origin_object_id = c_section_id;
SELECT DISTINCT mini_site_id
FROM ibe_dsp_msite_sct_sects
WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
AND child_section_id = c_section_id;
SELECT section_item_id
FROM ibe_dsp_section_items
WHERE section_id = c_section_id
AND inventory_item_id = c_item_id
AND organization_id = c_organization_id;
SELECT mini_site_section_item_id
FROM ibe_dsp_msite_sct_items
WHERE mini_site_id = c_minisite_id
AND section_item_id = c_section_item_id;
DELETE FROM IBE_DSP_MSITE_SCT_ITEMS
WHERE mini_site_section_item_id = l_temp;
l_inventory_item_ids.delete;
l_organization_ids.delete;
l_start_date_actives.delete;
l_end_date_actives.delete;
l_sort_orders.delete;
l_association_reason_codes.delete;
x_section_item_ids.delete;
l_sql VARCHAR2(2000) := 'SELECT 1 FROM mtl_item_categories mic, ibe_ct_relation_rules rule, mtl_system_items_vl ms ' ||
' WHERE mic.category_set_id = :c_category_set_id ' ||
' AND mic.organization_id = :p_organization_id ' ||
' AND mic.inventory_item_id = :c_inventory_item_id ' ||
' AND mic.category_id = rule.dest_object_id ' ||
' AND rule.relation_type_code = ' || '''' || 'AUTOPLACEMENT' || '''' ||
' AND rule.origin_object_type = ' || '''' || 'S' || '''' ||
' AND rule.dest_object_type = ' || '''' || 'C' || '''' ||
' AND rule.origin_object_id = :c_section_id ' ||
' AND ms.inventory_item_id = mic.inventory_item_id ' ||
' AND ms.organization_id = mic.organization_id ' ||
-- This is for fixing bug 3037399 and 3036491
' AND NOT(ms.replenish_to_order_flag = ' || '''' || 'Y' || '''' ||
' AND ms.base_item_id is not null ' ||
' AND ms.auto_created_config_flag = ' || '''' || 'Y' || '''' || ') ';
SELECT 1 FROM mtl_item_categories mic, ibe_ct_relation_rules rule,
mtl_system_items_vl ms
WHERE mic.category_set_id = c_category_set_id
AND mic.organization_id = p_organization_id
AND mic.inventory_item_id = c_inventory_item_id
AND mic.category_id = rule.dest_object_id
AND rule.relation_type_code = 'AUTOPLACEMENT'
AND rule.origin_object_type = 'S'
AND rule.dest_object_type = 'C'
AND rule.origin_object_id = c_section_id
AND ms.inventory_item_id = mic.inventory_item_id
AND ms.organization_id = mic.organization_id
AND ms.web_status in ('PUBLISHED','UNPUBLISHED')
AND NOT(ms.replenish_to_order_flag = 'Y'
AND ms.base_item_id is not null
AND ms.auto_created_config_flag = 'Y');
SELECT si.section_item_id, si.inventory_item_id,
msi.concatenated_segments, ms.description
FROM ibe_dsp_section_items si, mtl_system_items_kfv msi,
mtl_system_items_vl ms
WHERE si.inventory_item_id = ms.inventory_item_id
AND si.organization_id = ms.organization_id
AND si.organization_id = msi.organization_id
AND si.inventory_item_id = msi.inventory_item_id
AND si.section_id = c_section_id
AND si.organization_id = c_organization_id;
IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Item
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_section_id => p_section_id,
p_inventory_item_id => l_inventory_item_id,
p_organization_id => p_organization_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
printDebuglog('Commit the delete section_item assoication');
printDebuglog('G_RET_STS_ERROR in Delete_Section_Item');
printOutput('G_RET_STS_ERROR in Delete_Section_Item');
printDebuglog('G_RET_STS_UNEXP_ERROR in Delete_Section_Item');
printOutput('G_RET_STS_UNEXP_ERROR in Delete_Section_Item');
IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Items_For_Item
(p_inventory_item_id => l_inventory_item_id,
p_organization_id => p_organization_id);
SELECT child_section_id
FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = c_master_mini_site_id
AND sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
START WITH child_section_id = c_section_id
AND mini_site_id = c_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND mini_site_id = c_master_mini_site_id
AND PRIOR mini_site_id = c_master_mini_site_id;
SELECT access_name, display_name
FROM ibe_dsp_sections_vl
WHERE section_id = c_section_id;
SELECT display_name
FROM ibe_dsp_sections_vl
WHERE section_id = c_section_id;