[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL FROM DUAL;
-- Delete all the existing mappings
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ( (attachment_id = p_attachment_id)
AND (default_site = l_default_msite)
AND (msite_id = p_msite_id) );
SELECT lgl_phys_map_id INTO l_lgl_phys_map_id
FROM IBE_DSP_LGL_PHYS_MAP
WHERE attachment_id = p_attachment_id
AND default_site = g_yes
AND default_language = g_yes
AND lgl_phys_map_id < 10000;
-- Delete all the existing mappings
IF l_seed_data_exists = false THEN -- Added by YAXU, don't delete the seeded physicalMap
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ( (attachment_id = p_attachment_id)
AND (default_site = g_yes)
AND (lgl_phys_map_id > 10000) );
IF l_seed_data_exists = false THEN -- Added by YAXU, don't insert the seeded physicalMap again
OPEN lgl_phys_map_id_seq;
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,
l_msite_id,
l_language_code,
p_attachment_id,
l_deliverable_id,
l_default_msite,
l_default_lang);
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,
l_msite_id,
p_language_code_tbl(l_index),
p_attachment_id,
l_deliverable_id,
l_default_msite,
l_default_lang
);
l_language_code_tbl.DELETE(1, l_count);
l_language_code_tbl.DELETE(
p_msite_lang_tbl(l_index1).lang_count + 1,
l_language_code_tbl.COUNT);
PROCEDURE delete_physicalmap(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
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_lgl_phys_map_id_tbl IN LGL_PHYS_MAP_ID_TBL_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_physicalmap';
SAVEPOINT delete_physicalmap_grp;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE lgl_phys_map_id = p_lgl_phys_map_id_tbl(l_index);
ROLLBACK TO delete_physicalmap_grp;
ROLLBACK TO delete_physicalmap_grp;
ROLLBACK TO delete_physicalmap_grp;
END delete_physicalmap;
PROCEDURE delete_attachment(
p_attachment_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment';
SAVEPOINT delete_attachment_grp;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE attachment_id = p_attachment_id;
ROLLBACK TO delete_attachment_grp;
END delete_attachment;
PROCEDURE delete_deliverable(
p_deliverable_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
SELECT distinct b.attachment_id
from ibe_dsp_lgl_phys_map a, jtf_amv_attachments b
where a.item_id = p_deliverable_id
and a.attachment_id = b.attachment_id
and b.attachment_used_by_id = -1
and (b.file_id <=0 or b.file_id is null)
and b.application_id = 671
and b.attachment_id >= 10000;
SAVEPOINT delete_deliverable_grp;
SELECT OBJECT_VERSION_NUMBER into l_obj_ver FROM JTF_AMV_ATTACHMENTS
WHERE attachment_id = l_attachment_id;
JTF_AMV_ATTACHMENT_PUB.delete_act_attachment(
p_api_version => l_api_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_attachment_id =>l_attachment_id,
p_object_version =>l_obj_ver);
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE item_id = p_deliverable_id;
ROLLBACK TO delete_deliverable_grp;
END delete_deliverable;
PROCEDURE delete_msite(
p_msite_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_msite';
SAVEPOINT delete_msite_grp;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ( (msite_id = p_msite_id) AND (default_site = g_no) );
ROLLBACK TO delete_msite_grp;
END delete_msite;
PROCEDURE delete_msite_language(
p_msite_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_msite_language';
SAVEPOINT delete_msite_language_grp;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE msite_id = p_msite_id
AND default_site = g_no
AND default_language = g_no
AND language_code NOT IN (SELECT language_code
FROM IBE_MSITE_LANGUAGES WHERE msite_id = p_msite_id);
ROLLBACK TO delete_msite_language_grp;
END delete_msite_language;
PROCEDURE delete_attachment_msite(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
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_attachment_id IN NUMBER,
p_msite_id_tbl IN MSITE_ID_TBL_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment_msite';
SAVEPOINT delete_attachment_msite_grp;
SAVEPOINT delete_one_ath_msite_grp;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ( (attachment_id = p_attachment_id)
AND (default_site = g_yes) );
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ( (attachment_id = p_attachment_id)
AND (msite_id = p_msite_id_tbl(l_index))
AND (default_site = g_no) );
ROLLBACK TO delete_one_ath_msite_grp;
ROLLBACK TO delete_attachment_msite_grp;
ROLLBACK TO delete_attachment_msite_grp;
ROLLBACK TO delete_attachment_msite_grp;
END delete_attachment_msite;
PROCEDURE delete_dlv_all_all(
p_deliverable_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_dlv_all_all';
SAVEPOINT delete_dlv_all_all_grp;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE item_id = p_deliverable_id
AND default_site = g_yes
AND default_language = g_yes;
ROLLBACK TO delete_dlv_all_all_grp;
END delete_dlv_all_all;
SELECT IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL FROM DUAL;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ((item_id = l_deliverable_id)
AND (attachment_id = l_attachment_id)
AND (content_item_key is null)
AND (default_site = l_default_msite)
AND (msite_id = l_msite_id)
AND (lgl_phys_map_id >= 10000));
SELECT lgl_phys_map_id, object_version_number
INTO l_seed_lgl_phys_map_id,l_object_version_number
FROM IBE_DSP_LGL_PHYS_MAP
WHERE item_id = l_deliverable_id
AND content_item_key = l_old_content_item_key
AND attachment_id = -1
AND default_site = g_yes
AND default_language = g_yes
AND lgl_phys_map_id < 10000;
SELECT lgl_phys_map_id, object_version_number
INTO l_seed_lgl_phys_map_id,l_object_version_number
FROM IBE_DSP_LGL_PHYS_MAP
WHERE item_id = l_deliverable_id
AND attachment_id = l_attachment_id
AND content_item_key is null
AND default_site = g_yes
AND default_language = g_yes
AND lgl_phys_map_id < 10000;
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, content_item_key)
VALUES(l_lgl_phys_map_id, 1, SYSDATE,
FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id,
FND_GLOBAL.login_id, l_msite_id, l_language_code,
l_attachment_id, l_deliverable_id, l_default_msite,
l_default_lang, l_content_item_key);
ELSE -- update the seeded mapping
IF((l_ocm_integration IS NOT NULL)
AND (l_ocm_integration = 'Y')
AND (p_old_content_key <> p_new_content_key))
THEN
UPDATE IBE_DSP_LGL_PHYS_MAP
SET attachment_id = l_attachment_id,
content_item_key = l_content_item_key,
object_version_number = l_object_version_number+1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE lgl_phys_map_id = l_seed_lgl_phys_map_id;
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, content_item_key)
VALUES(l_lgl_phys_map_id, 1, SYSDATE,
FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id,
FND_GLOBAL.login_id, l_msite_id, l_language_code,
l_attachment_id, l_deliverable_id, l_default_msite,
l_default_lang, l_content_item_key);
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE ((attachment_id = -1)
AND (item_id = p_deliverable_id)
AND (content_item_key = l_old_content_item_key)
AND (lgl_phys_map_id >=10000));
IF(p_old_content_key <> p_new_content_key) -- modified by YAXU to update the mapping
THEN
l_old_attachment_id := TO_NUMBER(p_old_content_key);
UPDATE IBE_DSP_LGL_PHYS_MAP
set attachment_id = l_attachment_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE ((attachment_id = l_old_attachment_id)
AND (item_id = p_deliverable_id)
AND (content_item_key is null));
PROCEDURE delete_physicalmap(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false, --modified by YAXU, ewmove DEFAULT
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_deliverable_id IN NUMBER,
p_content_key IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(50) := 'delete_physicalmap';
SAVEPOINT DELETE_PHYSICALMAP;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE attachment_id = -1
AND content_item_key = l_content_item_key
AND item_id = l_deliverable_id;
DELETE FROM IBE_DSP_LGL_PHYS_MAP
WHERE attachment_id = l_attachment_id
AND content_item_key IS NULL
AND item_id = l_deliverable_id;
SELECT file_id into l_file_id
FROM JTF_AMV_ATTACHMENTS
WHERE attachment_id = l_attachment_id;
SELECT count(1) into l_other_item_count
FROM IBE_DSP_LGL_PHYS_MAP
WHERE attachment_id = l_attachment_id
AND content_item_key IS NULL
AND item_id <> l_deliverable_id;
SELECT OBJECT_VERSION_NUMBER into l_obj_ver FROM JTF_AMV_ATTACHMENTS
WHERE attachment_id = l_attachment_id;
JTF_AMV_ATTACHMENT_PUB.delete_act_attachment(
p_api_version => l_api_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_attachment_id =>l_attachment_id,
p_object_version =>l_obj_ver);
ROLLBACK TO DELETE_PHYSICALMAP;
ROLLBACK TO DELETE_PHYSICALMAP;
ROLLBACK TO DELETE_PHYSICALMAP;
END delete_physicalmap;
UPDATE IBE_DSP_LGL_PHYS_MAP
SET content_item_key = l_content_item_key
WHERE content_item_key = l_old_content_item_key
AND attachment_id = -1;
UPDATE IBE_DSP_LGL_PHYS_MAP
SET attachment_id = l_attachment_id
WHERE attachment_id = l_old_attachment_id
AND content_item_key IS NULL;
P_LAST_UPDATE_DATE IN VARCHAR2,
P_CUSTOM_MODE IN VARCHAR2,
P_UPLOAD_MODE IN VARCHAR2)
IS
BEGIN
IF (P_UPLOAD_MODE = 'NLS') THEN
null;
P_LAST_UPDATE_DATE,
P_CUSTOM_MODE);
P_LAST_UPDATE_DATE IN VARCHAR2,
P_CUSTOM_MODE IN VARCHAR2)
IS
l_rowExists NUMBER;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
INTO db_luby, db_ludate
FROM ibe_dsp_lgl_phys_map
WHERE lgl_phys_map_id = P_LGL_PHYS_MAP_ID;
UPDATE ibe_dsp_lgl_phys_map
SET LGL_PHYS_MAP_ID = P_LGL_PHYS_MAP_ID,
OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY = f_luby,
LAST_UPDATE_DATE = f_ludate,
MSITE_ID = P_MSITE_ID,
LANGUAGE_CODE = userenv('LANG'),
ATTACHMENT_ID = P_ATTACHMENT_ID,
ITEM_ID = P_ITEM_ID,
DEFAULT_LANGUAGE = P_DEFAULT_LANGUAGE,
DEFAULT_SITE = P_DEFAULT_SITE
WHERE lgl_phys_map_id = P_LGL_PHYS_MAP_ID;
select 1 into l_indexExists from ibe_dsp_lgl_phys_map where
item_id= to_number(P_ITEM_ID) and
language_code= P_LANGUAGE_CODE and
msite_id = to_number(P_MSITE_ID) and
default_language= P_DEFAULT_LANGUAGE and
default_site= P_DEFAULT_SITE ;
INSERT INTO ibe_dsp_lgl_phys_map (
LGL_PHYS_MAP_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MSITE_ID,
LANGUAGE_CODE,
ATTACHMENT_ID,
ITEM_ID,
DEFAULT_LANGUAGE,
DEFAULT_SITE)
VALUES (
P_LGL_PHYS_MAP_ID,
P_OBJECT_VERSION_NUMBER,
f_luby,
f_ludate,
f_luby,
f_ludate,
f_luby,
P_MSITE_ID,
P_LANGUAGE_CODE,
P_ATTACHMENT_ID,
P_ITEM_ID,
P_DEFAULT_LANGUAGE,
P_DEFAULT_SITE);