[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msite_id, msite_root_section_id FROM ibe_msites_b
WHERE UPPER(master_msite_flag) = 'Y';
SELECT parent_section_id FROM ibe_dsp_msite_sct_sects
WHERE parent_section_id IS NOT NULL
AND mini_site_id = l_c_master_mini_site_id
START WITH child_section_id = l_c_section_id
CONNECT BY PRIOR parent_section_id = child_section_id
AND PRIOR mini_site_id = l_c_master_mini_site_id
AND mini_site_id = l_c_master_mini_site_id;
PROCEDURE Delete_Recursive_Sections
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_master_mini_site_id IN NUMBER,
p_section_id IN NUMBER,
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) :=
'Delete_Recursive_Sections';
SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE parent_section_id = l_c_section_id AND
mini_site_id = p_master_mini_site_id;
IBE_DSP_SECTION_GRP.Delete_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_section_id => p_section_id,
p_access_name => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Delete_Recursive_Sections
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_master_mini_site_id => p_master_mini_site_id,
p_section_id => l_child_section_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IBE_DSP_SECTION_GRP.Delete_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_section_id => p_section_id,
p_access_name => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Delete_Recursive_Sections;
IS SELECT parent_section_id, child_section_id, start_date_active,
end_date_active, sort_order
FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id;
IS SELECT S.section_id AS s_section_id
FROM ibe_dsp_msite_sct_sects MSS, ibe_dsp_sections_b S
WHERE MSS.child_section_id = S.section_id
AND MSS.parent_section_id = l_c_section_id
AND MSS.mini_site_id = l_c_master_mini_site_id
AND S.available_in_all_sites_flag = 'Y';
IS SELECT section_item_id
FROM ibe_dsp_section_items
WHERE section_id = l_c_section_id;
SELECT access_name, applicable_to_code
FROM jtf_amv_items_b
WHERE item_id = c_item_id
AND deliverable_type_code = 'TEMPLATE';
SELECT deliverable_id
FROM ibe_dsp_sections_b
WHERE section_id = c_section_id;
SELECT obj.item_id, obj.context_id, obj.object_type
FROM ibe_dsp_obj_lgl_ctnt obj, ibe_dsp_context_b context
WHERE obj.object_id = c_section_id
AND obj.object_type = 'S'
AND obj.context_id = context.context_id
AND context.context_type_code = 'LAYOUT_COMPONENT';
SELECT obj.item_id, obj.context_id, obj.object_type
FROM ibe_dsp_obj_lgl_ctnt obj, ibe_dsp_context_b context
WHERE obj.object_id = c_section_id
AND obj.object_type = 'S'
AND obj.context_id = context.context_id
AND context.context_type_code = 'LAYOUT_COMPONENT'
AND context.access_name <> 'CENTER';
DELETE FROM ibe_dsp_obj_lgl_ctnt obj
WHERE obj.object_id = p_target_section_id
AND obj.object_type = 'S'
AND EXISTS (
SELECT 1
FROM ibe_dsp_context_b context
WHERE obj.context_id = context.context_id
AND context.context_type_code = 'LAYOUT_COMPONENT')
AND NOT EXISTS(
SELECT 1
FROM ibe_dsp_context_b context, ibe_dsp_obj_lgl_ctnt obj1
WHERE obj1.context_id = obj.context_id
AND obj1.context_id = context.context_id
AND obj1.object_id = p_source_section_id
AND obj1.object_type = 'S'
AND context.context_type_code = 'LAYOUT_COMPONENT'
AND context.component_type_code <> 'CENTER');
UPDATE ibe_dsp_obj_lgl_ctnt
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ITEM_ID = mapping.item_id,
CREATED_BY = FND_GLOBAL.user_id,
CREATION_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE object_id = p_target_section_id
AND object_type = 'S'
AND context_id = mapping.context_id;
INSERT INTO ibe_dsp_obj_lgl_ctnt(OBJ_LGL_CTNT_ID,
OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID, CONTEXT_ID, OBJECT_TYPE, OBJECT_ID,
ITEM_ID)
VALUES(ibe_dsp_obj_lgl_ctnt_s1.NEXTVAL,1,FND_GLOBAL.user_id,SYSDATE,
FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.login_id, NULL,
mapping.context_id, 'S', p_target_section_id, mapping.item_id);
DELETE FROM ibe_dsp_obj_lgl_ctnt obj
WHERE obj.object_id = p_target_section_id
AND obj.object_type = 'S'
AND EXISTS (
SELECT 1
FROM ibe_dsp_context_b context
WHERE obj.context_id = context.context_id
AND context.context_type_code = 'LAYOUT_COMPONENT')
AND NOT EXISTS(
SELECT 1
FROM ibe_dsp_context_b context, ibe_dsp_obj_lgl_ctnt obj1
WHERE obj1.context_id = obj.context_id
AND obj1.context_id = context.context_id
AND obj1.object_id = p_source_section_id
AND obj1.object_type = 'S'
AND context.context_type_code = 'LAYOUT_COMPONENT');
UPDATE ibe_dsp_obj_lgl_ctnt
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ITEM_ID = mapping.item_id,
CREATED_BY = FND_GLOBAL.user_id,
CREATION_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE object_id = p_target_section_id
AND object_type = 'S'
AND context_id = mapping.context_id;
INSERT INTO ibe_dsp_obj_lgl_ctnt(OBJ_LGL_CTNT_ID,
OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID, CONTEXT_ID, OBJECT_TYPE, OBJECT_ID,
ITEM_ID)
VALUES(ibe_dsp_obj_lgl_ctnt_s1.NEXTVAL,1,FND_GLOBAL.user_id,SYSDATE,
FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.login_id, NULL,
mapping.context_id, 'S', p_target_section_id, mapping.item_id);
SELECT item_id
FROM jtf_amv_items_b
WHERE access_name = l_c_access_name
AND deliverable_type_code = l_c_deliverable_type_code
AND applicable_to_code = l_c_applicable_to_code
AND application_id = l_c_application_id;
IS SELECT section_type_code
FROM ibe_dsp_sections_b
WHERE section_id = l_c_section_id;
IS SELECT section_id, section_type_code
FROM ibe_dsp_sections_b
WHERE access_name = l_c_access_name;
IS SELECT section_item_id
FROM ibe_dsp_section_items
WHERE section_id = l_c_section_id;
CURSOR c4 IS SELECT msite_root_section_id
FROM ibe_msites_b
WHERE master_msite_flag = 'Y';
IS SELECT msite_id
FROM ibe_msites_vl
WHERE msite_id <> l_c_master_mini_site_id;
IS SELECT msite_id
FROM ibe_msites_vl
WHERE msite_id <> l_c_master_mini_site_id AND
(msite_root_section_id = l_c_section_id OR
EXISTS (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = msite_id
AND mini_site_id <> l_c_master_mini_site_id
AND child_section_id = l_c_parent_section_id));
UPDATE ibe_msites_b
SET msite_root_section_id = x_section_id,
object_version_number = object_version_number + 1
WHERE master_msite_flag = 'Y';
PROCEDURE Update_Hierarchy_Section
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_section_id IN NUMBER := FND_API.G_MISS_NUM,
p_object_version_number IN NUMBER,
p_mss_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
p_access_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_start_date_active IN DATE := FND_API.G_MISS_DATE,
p_end_date_active IN DATE := FND_API.G_MISS_DATE,
p_section_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_display_context_id IN NUMBER := FND_API.G_MISS_NUM,
p_deliverable_id IN NUMBER := FND_API.G_MISS_NUM,
p_available_in_all_sites_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_auto_placement_rule IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_order_by_clause IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
p_display_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_long_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_keywords IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_upd_dsc_scts_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
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_Hierarchy_Section';
IS SELECT parent_section_id
FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id;
IS SELECT mini_site_section_section_id
FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_master_mini_site_id
AND parent_section_id = l_c_section_id;
IS SELECT section_id, object_version_number FROM ibe_dsp_sections_b
WHERE section_id IN
(SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_master_mini_site_id
START WITH parent_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND PRIOR mini_site_id = l_c_master_mini_site_id
AND mini_site_id = l_c_master_mini_site_id);
SAVEPOINT UPDATE_HIERARCHY_SECTION_PVT;
IBE_DSP_SECTION_GRP.Update_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_section_id => p_section_id,
p_object_version_number => p_object_version_number,
p_access_name => p_access_name,
p_start_date_active => p_start_date_active,
p_end_date_active => p_end_date_active,
p_section_type_code => p_section_type_code,
p_status_code => p_status_code,
p_display_context_id => p_display_context_id,
p_deliverable_id => p_deliverable_id,
p_available_in_all_sites_flag => p_available_in_all_sites_flag,
p_auto_placement_rule => p_auto_placement_rule,
p_order_by_clause => p_order_by_clause,
p_display_name => p_display_name,
p_description => p_description,
p_long_description => p_long_description,
p_keywords => p_keywords,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IBE_DSP_MSITE_SCT_SECT_PVT.Update_MSite_Section_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_mini_site_section_section_id => FND_API.G_MISS_NUM,
p_object_version_number => p_mss_object_version_number,
p_mini_site_id => l_master_mini_site_id,
p_parent_section_id => l_parent_section_id,
p_child_section_id => p_section_id,
p_start_date_active => FND_API.G_MISS_DATE,
p_end_date_active => FND_API.G_MISS_DATE,
p_level_number => FND_API.G_MISS_NUM,
p_sort_order => p_sort_order,
p_concat_ids => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IBE_DSP_SECTION_GRP.Update_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_section_id => r3.section_id,
p_object_version_number => r3.object_version_number,
p_access_name => FND_API.G_MISS_CHAR,
p_start_date_active => FND_API.G_MISS_DATE,
p_end_date_active => FND_API.G_MISS_DATE,
p_section_type_code => FND_API.G_MISS_CHAR,
p_status_code => p_status_code,
p_display_context_id => FND_API.G_MISS_NUM,
p_deliverable_id => FND_API.G_MISS_NUM,
p_available_in_all_sites_flag => FND_API.G_MISS_CHAR,
p_auto_placement_rule => FND_API.G_MISS_CHAR,
p_order_by_clause => FND_API.G_MISS_CHAR,
p_display_name => FND_API.G_MISS_CHAR,
p_description => FND_API.G_MISS_CHAR,
p_long_description => FND_API.G_MISS_CHAR,
p_keywords => FND_API.G_MISS_CHAR,
p_attribute_category => FND_API.G_MISS_CHAR,
p_attribute1 => FND_API.G_MISS_CHAR,
p_attribute2 => FND_API.G_MISS_CHAR,
p_attribute3 => FND_API.G_MISS_CHAR,
p_attribute4 => FND_API.G_MISS_CHAR,
p_attribute5 => FND_API.G_MISS_CHAR,
p_attribute6 => FND_API.G_MISS_CHAR,
p_attribute7 => FND_API.G_MISS_CHAR,
p_attribute8 => FND_API.G_MISS_CHAR,
p_attribute9 => FND_API.G_MISS_CHAR,
p_attribute10 => FND_API.G_MISS_CHAR,
p_attribute11 => FND_API.G_MISS_CHAR,
p_attribute12 => FND_API.G_MISS_CHAR,
p_attribute13 => FND_API.G_MISS_CHAR,
p_attribute14 => FND_API.G_MISS_CHAR,
p_attribute15 => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO UPDATE_HIERARCHY_SECTION_PVT;
ROLLBACK TO UPDATE_HIERARCHY_SECTION_PVT;
ROLLBACK TO UPDATE_HIERARCHY_SECTION_PVT;
END Update_Hierarchy_Section;
PROCEDURE Delete_Hierarchy_Section
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_section_id IN NUMBER := FND_API.G_MISS_NUM,
p_access_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
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) :=
'Delete_Hierarchy_Section';
IS SELECT section_id FROM ibe_dsp_sections_b
WHERE access_name = l_c_access_name;
SELECT S.section_id
FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
WHERE S.section_id = MSS.child_section_id
AND MSS.mini_site_id = l_master_mini_site_id
AND S.section_id IN
(SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_master_mini_site_id
START WITH parent_section_id = l_section_id
AND mini_site_id = l_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND mini_site_id = l_master_mini_site_id)
ORDER BY MSS.level_number desc;
SAVEPOINT DELETE_HIERARCHY_SECTION_PVT;
IBE_DSP_SECTION_GRP.Delete_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_section_id => r1.section_id,
p_access_name => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IBE_DSP_SECTION_GRP.Delete_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_section_id => p_section_id,
p_access_name => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO DELETE_HIERARCHY_SECTION_PVT;
ROLLBACK TO DELETE_HIERARCHY_SECTION_PVT;
ROLLBACK TO DELETE_HIERARCHY_SECTION_PVT;
END Delete_Hierarchy_Section;
/*PROCEDURE Delete_Hierarchy_Section
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_section_id IN NUMBER := FND_API.G_MISS_NUM,
p_access_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
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) :=
'Delete_Hierarchy_Section';
IS SELECT section_id FROM ibe_dsp_sections_b
WHERE access_name = l_c_access_name;
SAVEPOINT DELETE_HIERARCHY_SECTION_PVT;
Delete_Recursive_Sections
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_master_mini_site_id => l_master_mini_site_id,
p_section_id => l_section_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO DELETE_HIERARCHY_SECTION_PVT;
ROLLBACK TO DELETE_HIERARCHY_SECTION_PVT;
ROLLBACK TO DELETE_HIERARCHY_SECTION_PVT;
END Delete_Hierarchy_Section;
IS SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id NOT IN
(SELECT msite_id FROM ibe_msites_b
WHERE UPPER(master_msite_flag) = 'Y');
IS SELECT section_item_id FROM ibe_dsp_section_items
WHERE section_id = l_c_section_id
AND inventory_item_id = l_c_inventory_item_id
AND organization_id = l_c_organization_id;
IS SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id NOT IN
(SELECT msite_id FROM ibe_msites_b
WHERE UPPER(master_msite_flag) = 'Y');
IS SELECT section_item_id FROM ibe_dsp_section_items
WHERE section_id = l_c_section_id
AND inventory_item_id = l_c_inventory_item_id
AND organization_id = l_c_organization_id;
IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Item
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_call_from_trigger => FALSE,
p_section_item_id => FND_API.G_MISS_NUM,
p_section_id => p_section_ids(i),
p_inventory_item_id => p_inventory_item_ids(j),
p_organization_id => p_organization_ids(j),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Item
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_call_from_trigger => FALSE,
p_section_item_id => p_section_item_ids(i),
p_section_id => FND_API.G_MISS_NUM,
p_inventory_item_id => FND_API.G_MISS_NUM,
p_organization_id => FND_API.G_MISS_NUM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IS SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id =
(SELECT parent_section_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_child_section_id
AND mini_site_id = l_c_master_mini_site_id)
AND mini_site_id <> l_c_master_mini_site_id;
IS SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id <> l_c_master_mini_site_id;
IS SELECT MSI.mini_site_section_item_id
FROM ibe_dsp_msite_sct_items MSI,
ibe_dsp_section_items SI,
ibe_dsp_msite_sct_sects MSS
WHERE MSI.mini_site_id = l_c_mini_site_id
AND MSI.section_item_id = SI.section_item_id
AND SI.section_id = MSS.child_section_id
AND MSS.mini_site_id = l_c_master_mini_site_id
AND MSS.child_section_id = l_c_section_id
UNION
SELECT MSI.mini_site_section_item_id
FROM ibe_dsp_msite_sct_items MSI,
ibe_dsp_section_items SI,
ibe_dsp_msite_sct_sects MSS
WHERE MSI.mini_site_id = l_c_mini_site_id
AND MSI.section_item_id = SI.section_item_id
AND SI.section_id = MSS.child_section_id
AND MSS.mini_site_id = l_c_master_mini_site_id
AND MSS.child_section_id IN
(SELECT child_section_id
FROM ibe_dsp_msite_sct_sects
START WITH parent_section_id = l_c_section_id
AND mini_site_id = l_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND mini_site_id = l_master_mini_site_id);
CURSOR c6(l_c_section_id IN NUMBER) IS SELECT msite_id
FROM ibe_msites_b
WHERE msite_root_section_id = l_c_section_id
AND master_msite_flag <> 'Y';
IS SELECT parent_section_id, start_date_active, end_date_active, sort_order
FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id;
DELETE FROM ibe_dsp_msite_sct_items
WHERE mini_site_section_item_id = r3.mini_site_section_item_id;
DELETE FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_old_mini_site_ids(i)
AND child_section_id = p_section_id;
DELETE FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_old_mini_site_ids(i)
AND child_section_id IN
(SELECT child_section_id
FROM ibe_dsp_msite_sct_sects
START WITH parent_section_id = p_section_id
AND mini_site_id = l_master_mini_site_id
CONNECT BY prior child_section_id = parent_section_id
AND mini_site_id = l_master_mini_site_id);
-- If so, update the section-msite record if the parent section
-- is not set correctly.
IF (l_parent_section_id <> -1) AND (l_parent_section_id IS NOT NULL) THEN
UPDATE IBE_DSP_MSITE_SCT_SECTS
SET parent_section_id = l_parent_section_id,
start_date_active = l_start_date_active,
end_date_active = l_end_date_active,
sort_order = l_sort_order,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE mini_site_id = l_old_mini_site_ids(j)
AND child_section_id = p_section_id
AND parent_section_id <> l_parent_section_id;
DELETE FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = p_mini_site_id;
DELETE FROM ibe_dsp_msite_sct_items
WHERE mini_site_id = p_mini_site_id;
PROCEDURE Update_Hierarchy_Item
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_last_update_date IN DATE,
p_web_status_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_long_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
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_Hierarchy_Item';
SAVEPOINT UPDATE_HIERARCHY_ITEM_PVT;
l_in_item_rec.LAST_UPDATED_BY := p_last_updated_by;
l_in_item_rec.LAST_UPDATE_DATE := p_last_update_date;
l_in_item_rec.LAST_UPDATE_LOGIN := p_last_update_login;
INV_ITEM_GRP.Update_Item
(
p_commit => FND_API.G_FALSE,
p_lock_rows => FND_API.G_TRUE,
p_validation_level => p_validation_level,
p_Item_rec => l_in_item_rec,
x_Item_rec => l_out_item_rec,
x_return_status => x_return_status,
x_Error_tbl => l_out_error_tbl
);
FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_UPDATE_INV_ITEM_FAIL');
FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_UPDATE_INV_ITEM_FAIL');
END Update_Hierarchy_Item;
IS SELECT section_item_id FROM ibe_dsp_section_items
WHERE inventory_item_id = l_c_inventory_item_id
AND organization_id = l_c_organization_id
AND EXISTS (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = section_id
AND mini_site_id = l_c_mini_site_id
AND mini_site_id <> l_c_master_mini_site_id);
DELETE FROM ibe_dsp_msite_sct_items
WHERE section_item_id IN
(SELECT section_item_id FROM ibe_dsp_section_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id);
PROCEDURE Update_Delete_Sct_Scts
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_msite_section_section_ids IN JTF_NUMBER_TABLE,
p_object_version_numbers IN JTF_NUMBER_TABLE,
p_start_date_actives IN JTF_DATE_TABLE,
p_end_date_actives IN JTF_DATE_TABLE,
p_sort_orders IN JTF_NUMBER_TABLE,
p_delete_flags IN JTF_VARCHAR2_TABLE_300,
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_Delete_Sct_Scts';
IS SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_section_section_id = l_c_msite_section_section_id;
SAVEPOINT UPDATE_DELETE_SCT_SCTS_PVT;
IF (p_delete_flags(i) = 'Y') THEN
OPEN c1(p_msite_section_section_ids(i));
Delete_Hierarchy_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_section_id => l_child_section_id,
p_access_name => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IBE_DSP_MSITE_SCT_SECT_pvt.Update_Msite_Section_Section
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_mini_site_section_section_id => p_msite_section_section_ids(i),
p_object_version_number => p_object_version_numbers(i),
p_mini_site_id => FND_API.G_MISS_NUM,
p_parent_section_id => FND_API.G_MISS_NUM,
p_child_section_id => FND_API.G_MISS_NUM,
p_start_date_active => p_start_date_actives(i),
p_end_date_active => p_end_date_actives(i),
p_level_number => FND_API.G_MISS_NUM,
p_sort_order => p_sort_orders(i),
p_concat_ids => FND_API.G_MISS_CHAR,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;
ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;
ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;
END Update_Delete_Sct_Scts;
IS SELECT section_type_code
FROM ibe_dsp_sections_b
WHERE section_id = l_c_section_id;
IS SELECT section_item_id
FROM ibe_dsp_section_items
WHERE section_id = l_c_section_id;
IS SELECT parent_section_id, child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_master_mini_site_id
START WITH child_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND PRIOR mini_site_id = l_c_master_mini_site_id
AND mini_site_id = l_c_master_mini_site_id;
IS SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id =
(SELECT parent_section_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id)
AND mini_site_id <> l_c_master_mini_site_id
UNION
SELECT msite_id AS mini_site_id FROM ibe_msites_b
WHERE msite_root_section_id = l_c_section_id
AND msite_id <> l_c_master_mini_site_id;
IS SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE child_section_id = l_c_dst_parent_section_id
AND mini_site_id = l_c_master_mini_site_id
START WITH child_section_id = l_c_src_section_id
AND mini_site_id = l_c_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND PRIOR mini_site_id = l_c_master_mini_site_id
AND mini_site_id = l_c_master_mini_site_id;
UPDATE ibe_dsp_msite_sct_sects
SET parent_section_id = p_dst_parent_section_id
WHERE child_section_id = p_src_section_id
AND mini_site_id = l_master_mini_site_id;
UPDATE ibe_dsp_msite_sct_sects
SET concat_ids = l_concat_ids,
level_number = l_level_number + 1
WHERE child_section_id = r3.child_section_id
AND mini_site_id = l_master_mini_site_id;
IS SELECT S.access_name, S.start_date_active, S.end_date_active,
S.section_type_code, S.status_code, S.display_context_id, S.deliverable_id,
S.available_in_all_sites_flag, S.auto_placement_rule, S.order_by_clause,
S.display_name, S.description, S.long_description, S.keywords,
S.attribute_category, S.attribute1, S.attribute2, S.attribute3,
S.attribute4, S.attribute5, S.attribute6, S.attribute7, S.attribute8,
S.attribute9, S.attribute10, S.attribute11, S.attribute12, S.attribute13,
S.attribute14, S.attribute15, MSS.sort_order
FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
WHERE S.section_id = l_c_section_id
AND MSS.child_section_id = S.section_id
AND MSS.mini_site_id = l_c_master_mini_site_id;
IS SELECT S.section_id, S.access_name, S.start_date_active,
S.end_date_active, S.section_type_code, S.status_code,
S.display_context_id, S.deliverable_id,
S.available_in_all_sites_flag, S.auto_placement_rule, S.order_by_clause,
S.display_name, S.description, S.long_description, S.keywords,
S.attribute_category, S.attribute1, S.attribute2, S.attribute3,
S.attribute4, S.attribute5, S.attribute6, S.attribute7, S.attribute8,
S.attribute9, S.attribute10, S.attribute11, S.attribute12, S.attribute13,
S.attribute14, S.attribute15, MSS.sort_order, MSS.parent_section_id
FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
WHERE S.section_id = MSS.child_section_id
AND MSS.mini_site_id = l_c_master_mini_site_id
AND S.section_id IN
(SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_master_mini_site_id
START WITH parent_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND mini_site_id = l_c_master_mini_site_id)
ORDER BY MSS.level_number;
IS SELECT section_item_id, section_id, inventory_item_id, organization_id,
start_date_active, end_date_active, sort_order, association_reason_code
FROM ibe_dsp_section_items
WHERE section_id = l_c_section_id
OR section_id IN
(SELECT child_section_id FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = l_c_master_mini_site_id
START WITH parent_section_id = l_c_section_id
AND mini_site_id = l_c_master_mini_site_id
CONNECT BY PRIOR child_section_id = parent_section_id
AND mini_site_id = l_c_master_mini_site_id);
IS SELECT language,source_lang,display_name,description,long_description,keywords
from ibe_dsp_sections_tl where section_id=l_c_section_id;
select count(*)into l_count_langs from fnd_languages where installed_flag in ('I','B');
update ibe_dsp_sections_tl set
display_name= r4.display_name,
description = r4.description,
long_description = r4.long_description,
keywords= r4.keywords,
source_lang = r4.source_lang
where language=r4.language and section_id=x_new_src_section_id
and language <>userenv('lang');
update ibe_dsp_sections_tl
set display_name = p_new_display_name
where section_id = x_new_src_section_id
and language= userenv('lang');
update ibe_dsp_sections_tl set
display_name = r4.display_name,
description = r4.description,
long_description = r4.long_description,
keywords= r4.keywords,
source_lang=r4.source_lang
where language=r4.language
and section_id=x_new_src_section_id
and language <> userenv('lang');
update ibe_dsp_sections_tl set
display_name = p_new_display_name,
description = r4.description,
long_description = r4.long_description,
keywords= r4.keywords
where language=r4.language
and section_id=x_new_src_section_id;
fnd_file.put_line(fnd_file.log,'get G_RET_STS_ERROR in Put_Section_Map, with IBE_DSP_SCT_MAP_INSERT_FAIL error');
FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_MAP_INSERT_FAIL');
fnd_file.put_line(fnd_file.log,'get G_RET_STS_UNEXP_ERROR in Put_Section_Map, with IBE_DSP_SCT_MAP_INSERT_FAIL error');
FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_MAP_INSERT_FAIL');
update ibe_dsp_sections_tl set
display_name= r4.display_name,
description = r4.description,
long_description = r4.long_description,
keywords= r4.keywords,
source_lang = r4.source_lang
where language=r4.language and section_id=l_section_id
and language <>userenv('lang');
fnd_file.put_line(fnd_file.log,'get G_RET_STS_ERROR in Put_Section_Map, with IBE_DSP_SCT_MAP_INSERT_FAIL error');
FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_MAP_INSERT_FAIL');
fnd_file.put_line(fnd_file.log,'get G_RET_STS_ERROR in Put_Section_Map, with IBE_DSP_SCT_MAP_INSERT_FAIL error');
FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_MAP_INSERT_FAIL');
IBE_UTIL.debug(' Calling Delete_Hierarchy_Section to delete sections if exception happens'||x_new_src_section_id );
fnd_file.put_line(fnd_file.log,' Calling Delete_Hierarchy_Section to delete sections if exception happens '||x_new_src_section_id);
Delete_Hierarchy_Section(p_api_version =>1.0,
p_init_msg_list=> FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
p_section_id => x_new_src_section_id,
p_access_name=> null,
x_return_status=>x_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data);
IBE_UTIL.debug(' Calling Delete_Hierarchy_Section when G_EXC_UNEXPECTED_ERROR'||x_new_src_section_id);
fnd_file.put_line(fnd_file.log,' Calling Delete_Hierarchy_Section when G_EXC_UNEXPECTED_ERROR '||x_new_src_section_id);
Delete_Hierarchy_Section(p_api_version =>1.0,
p_init_msg_list=> FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
p_section_id => x_new_src_section_id,
p_access_name=> null,
x_return_status=>x_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data);
IBE_UTIL.debug(' Calling Delete_Hierarchy_Section when Other exception happens'||x_new_src_section_id);
fnd_file.put_line(fnd_file.log,' Calling Delete_Hierarchy_Section when Other exception happens '||x_new_src_section_id);
Delete_Hierarchy_Section(p_api_version =>1.0,
p_init_msg_list=> FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
p_section_id => x_new_src_section_id,
p_access_name=> null,
x_return_status=>x_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data);
IS SELECT item_id,context_id from ibe_dsp_obj_lgl_ctnt where
object_type='S' and object_id= l_old_section_id;
-- dbms_output.put_line('inserting into ibe_dsp_lgl_phys_map');
select access_name,item_name,deliverable_type_code,applicable_to_code,keyword,description
into l_deliverable_rec.access_name,
l_deliverable_rec.display_name,
l_deliverable_rec.item_type,
l_deliverable_rec.item_applicable_to,
l_deliverable_rec.keywords,
l_deliverable_rec.description
from ibe_dsp_amv_items_v where item_id=p_item_id;
SELECT IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL FROM DUAL;
INSERT INTO IBE_DSP_LGL_PHYS_MAP (
lgl_phys_map_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
msite_id,
language_code,
attachment_id,
item_id,
default_site,
default_language
) VALUES (
l_lgl_phys_map_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
p_msite_id,
p_language_code,
p_attachment_id,
p_item_id,
p_default_site,
p_default_language);
l_obj_lgl_ctnt_rec_type.obj_lgl_ctnt_delete:= FND_API.g_false;
IBE_LOGICALCONTENT_GRP.save_delete_lgl_ctnt(
p_api_version =>l_api_version,
p_init_msg_list => FND_API.g_false,
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_object_type_code => 'S',
p_lgl_ctnt_tbl => l_obj_lgl_ctnt_tbl_type);
SELECT child_section_id
FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = c_master_mini_site_id
START WITH parent_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;
SELECT obj.item_id, obj.context_id, obj.object_type
FROM ibe_dsp_obj_lgl_ctnt obj, ibe_dsp_context_b context
WHERE obj.object_id = c_section_id
AND obj.object_type = 'S'
AND obj.context_id = context.context_id
AND context.context_type_code = 'LAYOUT_COMPONENT'
AND context.access_name <> 'CENTER';
IBE_UTIL.debug('Delete from ibe_dsp_obj_lgl_cnt_obj for section'||p_target_section_id);
fnd_file.put_line(fnd_file.log,'Delete from ibe_dsp_obj_lgl_cnt_obj for section '||p_target_section_id);
DELETE FROM ibe_dsp_obj_lgl_ctnt obj
WHERE obj.object_id = p_target_section_id
AND obj.object_type = 'S'
AND EXISTS (
SELECT 1
FROM ibe_dsp_context_b context
WHERE obj.context_id = context.context_id
AND context.context_type_code = 'LAYOUT_COMPONENT')
AND NOT EXISTS(
SELECT 1
FROM ibe_dsp_context_b context, ibe_dsp_obj_lgl_ctnt obj1
WHERE obj1.context_id = obj.context_id
AND obj1.context_id = context.context_id
AND obj1.object_id = p_source_section_id
AND obj1.object_type = 'S'
AND context.context_type_code = 'LAYOUT_COMPONENT'
AND context.component_type_code <> 'CENTER')
RETURNING context_id BULK COLLECT INTO l_context_ids;
UPDATE ibe_dsp_obj_lgl_ctnt
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ITEM_ID = mapping.item_id,
CREATED_BY = FND_GLOBAL.user_id,
CREATION_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE object_id = p_target_section_id
AND object_type = 'S'
AND context_id = mapping.context_id;
INSERT INTO ibe_dsp_obj_lgl_ctnt(OBJ_LGL_CTNT_ID,
OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID, CONTEXT_ID, OBJECT_TYPE, OBJECT_ID,
ITEM_ID)
VALUES(ibe_dsp_obj_lgl_ctnt_s1.NEXTVAL,1,FND_GLOBAL.user_id,SYSDATE,
FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.login_id, NULL,
mapping.context_id, 'S', p_target_section_id, mapping.item_id);
PROCEDURE Update_Hierarchy_Layout_Map
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_section_id IN NUMBER,
p_layout_id IN NUMBER,
p_layout_comp_id IN JTF_NUMBER_TABLE,
p_layout_temp_id IN JTF_NUMBER_TABLE,
p_object_versions IN JTF_NUMBER_TABLE,
p_actionflags IN JTF_VARCHAR2_TABLE_100,
p_cascading_flag IN NUMBER := 0,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_section_ids OUT NOCOPY JTF_NUMBER_TABLE,
x_layout_comp_ids OUT NOCOPY JTF_NUMBER_TABLE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Hierarchy_Layout_Map';
SELECT child_section_id
FROM ibe_dsp_msite_sct_sects
WHERE mini_site_id = c_master_mini_site_id
START WITH parent_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;
SAVEPOINT UPDATE_HIERARCHY_LAYOUT_MAP;
IBE_UTIL.debug('Calling Update_Hierarchy_Layout_Map');
UPDATE ibe_dsp_sections_b
SET deliverable_id = l_new_deliverable_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
object_version_number = object_version_number + 1
WHERE section_id = p_section_id;
DELETE FROM ibe_dsp_obj_lgl_ctnt obj
WHERE obj.object_id = p_section_id
AND obj.object_type = 'S'
AND obj.context_id = p_layout_comp_id(l_i);
INSERT INTO ibe_dsp_obj_lgl_ctnt(OBJ_LGL_CTNT_ID,
OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID, CONTEXT_ID, OBJECT_TYPE, OBJECT_ID,
ITEM_ID)
VALUES(ibe_dsp_obj_lgl_ctnt_s1.NEXTVAL, 1, FND_GLOBAL.user_id,
SYSDATE, FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.login_id,
NULL, p_layout_comp_id(l_i), 'S', p_section_id,
p_layout_temp_id(l_i));
UPDATE ibe_dsp_obj_lgl_ctnt
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ITEM_ID = p_layout_temp_id(l_i),
CREATED_BY = FND_GLOBAL.user_id,
CREATION_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE object_id = p_section_id
AND object_type = 'S'
AND context_id = p_layout_comp_id(l_i)
AND object_version_number = p_object_versions(l_i);
UPDATE ibe_dsp_sections_b
SET deliverable_id = l_new_deliverable_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
object_version_number = object_version_number + 1
WHERE section_id = child_section.child_section_id;
UPDATE ibe_dsp_sections_b
SET deliverable_id = l_org_deliverable_id
WHERE section_id = p_section_id;
IBE_UTIL.debug('After Calling Update_Hierarchy_Layout_Map');
ROLLBACK TO UPDATE_HIERARCHY_LAYOUT_MAP;
ROLLBACK TO UPDATE_HIERARCHY_LAYOUT_MAP;
ROLLBACK TO UPDATE_HIERARCHY_LAYOUT_MAP;
END Update_Hierarchy_Layout_Map;
PROCEDURE Update_Section_Dlv_Ctx
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_section_id IN NUMBER,
p_deliverable_id IN NUMBER,
p_display_context_id IN NUMBER,
p_object_version_number IN NUMBER,
p_saveds_flag IN NUMBER,
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_Section_Dlv_Ctx';
SELECT ctx.context_id
FROM ibe_dsp_context_b ctx
WHERE ctx.access_name = 'CENTER'
AND ctx.context_type_code = 'LAYOUT_COMPONENT'
AND ctx.component_type_code = 'SECTION';
SELECT map.obj_lgl_ctnt_id, map.object_version_number
FROM ibe_dsp_obj_lgl_ctnt map
WHERE map.object_id = c_section_id
AND map.object_type = 'S'
AND map.context_id = c_context_id;
SAVEPOINT UPDATE_SECTION_DLV_CTX;
IBE_UTIL.debug('Calling Update_Section_Dlv_Ctx starts');
IBE_UTIL.debug('Before calling IBE_DSP_SECTION_GRP.Update_Section');
IBE_DSP_SECTION_GRP.Update_Section
( 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_object_version_number => p_object_version_number,
p_deliverable_id => l_deliverable_id,
p_display_context_id => l_display_context_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IBE_DSP_SECTION_GRP.Update_Section
( 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_object_version_number => p_object_version_number,
p_deliverable_id => l_deliverable_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_lgl_ctnt_rec.obj_lgl_ctnt_delete := FND_API.G_FALSE;
IBE_LogicalContent_GRP.save_delete_lgl_ctnt(
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_type_code => 'S',
p_lgl_ctnt_tbl => l_lgl_ctnt_tbl);
IBE_DSP_SECTION_GRP.Update_Section
( 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_object_version_number => p_object_version_number,
p_display_context_id => l_display_context_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO UPDATE_SECTION_DLV_CTX;
ROLLBACK TO UPDATE_SECTION_DLV_CTX;
ROLLBACK TO UPDATE_SECTION_DLV_CTX;
END Update_Section_Dlv_Ctx;