[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1 from dual where exists (
select msite_id
from ibe_msites_b
where msite_id = p_msite_id
and default_language_code is not null );
select 1 from dual where exists (
select msite_id from ibe_msites_b where
msite_id = p_msite_id and default_currency_code is not null );
select 1 from dual where exists (
select msite_id from ibe_msites_b where
msite_id = p_msite_id and default_org_id is not null );
select 1 from dual where exists (
select language_code from fnd_languages_vl where
language_code = p_language);
select 1 from dual where exists (
select organization_id from hr_all_organization_units where
organization_id = p_orgid);
select 1 from dual where exists (
select currency_code from fnd_currencies_vl where
currency_code = p_currency);
select 1 from dual where exists (
select list_header_id from qp_list_headers_v where
currency_code = p_currency and
list_header_id in (p_walkin_prclstid,p_registered_prclstid,p_bizpartner_prclstid,p_partner_prclstid));
SELECT ibe_msites_b_s1.NEXTVAL
FROM DUAL;
select store_id from ibe_stores_b
where rownum < 2 ;*/
select 1 from dual where exists (
select msite_id from ibe_msites_b
where master_msite_flag = 'Y') ;
select 1 from dual
where exists (
select msite_id
from ibe_msites_b
where master_msite_flag = 'Y'
AND msite_id <> p_msite_id);
select 1 from dual where exists (
select lookup_code
from fnd_lookup_values_vl
where lookup_type='YES_NO'
and lookup_code=p_code);
select 1 from fnd_lookup_values_vl
where lookup_type = 'IBE_PARTY_ACCESS_CODE'
and lookup_code = p_code ;
l_operation_type:= 'INSERT';
l_operation_type:='UPDATE';
IBE_UTIL.Debug('[IBE_MSITE_GRP]Operation is an Update');
IF (((l_operation_type = 'UPDATE') AND
(l_resp_access_flag IS NOT NULL AND
l_resp_access_flag <> FND_API.G_MISS_char)) OR
l_operation_type = 'INSERT')
THEN
OPEN yes_no_cur(l_resp_access_flag );
IF (((l_operation_type = 'UPDATE') AND
(l_party_access_code IS NOT NULL AND
l_party_access_code <> FND_API.G_MISS_char)) OR
l_operation_type = 'INSERT')
THEN
OPEN C_party_access_code(l_party_access_code );
IF l_operation_type = 'INSERT'
THEN
OPEN msite_id_seq;
IF l_operation_type = 'INSERT'
THEN
IF (l_debug = 'Y') THEN
IBE_UTIL.Debug('Before insert into IBE_MSITES_B');
INSERT INTO IBE_MSITES_B (
MSITE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
DEFAULT_DATE_FORMAT,
PROFILE_ID,
MASTER_MSITE_FLAG,
WALKIN_ALLOWED_FLAG,
STORE_ID,
ATP_CHECK_FLAG,
MSITE_ROOT_SECTION_ID,
RESP_ACCESS_FLAG ,
PARTY_ACCESS_CODE ,
ACCESS_NAME,
URL,
THEME_ID,
PAYMENT_THRESHOLD_ENABLE_FLAG,
DOMAIN_NAME,
ENABLE_TRAFFIC_FILTER,
REPORTING_STATUS,
SITE_TYPE)
VALUES (
l_msite_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
p_msite_rec.start_date_active,
p_msite_rec.end_date_active,
p_msite_rec.date_format,
p_msite_rec.profile_id,
l_msite_master_flag,
walkin_allowed_code,
l_store_id,
l_atp_check_flag,
l_root_section_id,
l_resp_access_flag ,
l_party_access_code ,
p_msite_rec.access_name ,
DECODE(p_msite_rec.url,FND_API.G_MISS_CHAR,null,p_msite_rec.url) ,
DECODE(p_msite_rec.theme_id,FND_API.G_MISS_NUM,null,p_msite_rec.theme_id),
l_payment_thresh_enable_flag,
p_msite_rec.domain_name,
l_enable_traffic_filter,
l_reporting_status,
p_msite_rec.site_type);
IBE_UTIL.Debug('After insert into IBE_MSITES_B');
IBE_UTIL.Debug('Before insert into IBE_MSITES_TL');
insert into IBE_MSITES_TL (
MSITE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
MSITE_NAME,
MSITE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG ) select
l_msite_id,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
1,
p_msite_rec.Display_name,
p_msite_rec.description,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists(
select NULL
from IBE_MSITES_TL T
where T.MSITE_ID =l_msite_id
and T.LANGUAGE = L.LANGUAGE_CODE);
IBE_UTIL.Debug('After insert into IBE_MSITES_TL');
ELSIF l_operation_type = 'UPDATE'
THEN
-- added the following code for globalisation -- ssridhar
-- RESP_ACCESS_FLAG = l_resp_access_flag ,
-- PARTY_ACCESS_CODE = l_party_access_code ,
-- ACCESS_NAME = p_msite_rec.access_name
--Bug fix for not updating end_date_active
IF l_resp_access_flag = fnd_api.g_miss_char
THEN
l_resp_access_flag := NULL;
SELECT msite_root_section_id into l_cur_root_sct_id
FROM ibe_msites_b
WHERE msite_id = p_msite_rec.msite_id;
UPDATE IBE_MSITES_B SET
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
PROFILE_ID = p_msite_rec.profile_id,
DEFAULT_DATE_FORMAT = p_msite_rec.date_format ,
MASTER_MSITE_FLAG = l_msite_master_flag,
WALKIN_ALLOWED_FLAG = walkin_allowed_code,
STORE_ID = l_store_id ,
ATP_CHECK_FLAG = l_atp_check_flag,
MSITE_ROOT_SECTION_ID = l_root_section_id ,
OBJECT_VERSION_NUMBER = p_msite_rec.object_version_number + 1,
RESP_ACCESS_FLAG = NVL(l_resp_access_flag,resp_access_flag),
PARTY_ACCESS_CODE = nvl(l_party_access_code,party_access_code),
ACCESS_NAME = p_msite_rec.access_name,
START_DATE_ACTIVE =
nvl(p_msite_rec.start_date_active,start_date_active),
END_DATE_ACTIVE = p_msite_rec.end_date_active ,
URL = NVL(p_msite_rec.url,url),
THEME_ID = NVL(p_msite_rec.theme_id,theme_id),
PAYMENT_THRESHOLD_ENABLE_FLAG = l_payment_thresh_enable_flag,
DOMAIN_NAME = p_msite_rec.domain_name,
ENABLE_TRAFFIC_FILTER = p_msite_rec.enable_traffic_filter,
REPORTING_STATUS = p_msite_rec.reporting_status,
SITE_TYPE = p_msite_rec.site_type
WHERE
MSITE_ID = p_msite_rec.msite_id and
OBJECT_VERSION_NUMBER = p_msite_rec.object_version_number ;
UPDATE IBE_MSITES_TL SET
MSITE_NAME = decode( p_msite_rec.Display_name, FND_API.G_MISS_CHAR,
MSITE_NAME, p_msite_rec.Display_name),
MSITE_DESCRIPTION = decode( p_msite_rec.description,
FND_API.G_MISS_CHAR, MSITE_DESCRIPTION, p_msite_rec.description),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
OBJECT_VERSION_NUMBER= p_msite_rec.object_version_number +1 ,
SOURCE_LANG = userenv('LANG')
where msite_id = p_msite_rec.msite_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update IBE_MSITES_B set msite_root_section_id = l_root_section_id ,
store_id = l_store_id
where msite_id=p_msite_rec.msite_id;
select party_access_code
from ibe_msites_b
where msite_id = c_msite_id;
select language_code, enable_flag
from ibe_msite_languages
where msite_id = c_msite_id
order by language_code;
select m.currency_code, m.bizpartner_prc_listid,
m.registered_prc_listid, m.walkin_prc_listid,
m.orderable_limit, m.payment_threshold,m.partner_prc_listid
from ibe_msite_currencies m
where m.msite_id = c_msite_id
ORDER BY m.currency_code;
SELECT MR.msite_resp_id, MR.responsibility_id, MR.application_id,
MR.display_name, MR.start_date_active, MR.end_date_active,
MR.sort_order, MR.group_code
FROM ibe_msite_resps_vl MR
WHERE MR.msite_id = c_msite_id
ORDER BY MR.msite_resp_id, MR.group_code;
SELECT party_id, start_date_active, end_date_active
FROM ibe_msite_prty_accss
WHERE msite_id = c_msite_id;
SELECT ibe_msite_languages_s1.NEXTVAL
FROM DUAL;
l_insert_row NUMBER := 0;
DELETE FROM IBE_MSITE_LANGUAGES where
msite_id = p_msite_id;
INSERT INTO IBE_MSITE_LANGUAGES (
MSITE_LANGUAGE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MSITE_ID,
LANGUAGE_CODE,
ENABLE_FLAG)
VALUES (
l_msite_languages_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
p_msite_id,
p_msite_languages_tbl(l_index).language_code,
p_msite_languages_tbl(l_index).enable_flag);
l_insert_row := l_insert_row + 1;
update IBE_MSITES_B SET
DEFAULT_LANGUAGE_CODE =
p_msite_languages_tbl(default_index).language_code
WHERE MSITE_ID = p_msite_id;
if l_insert_row > 0 then
IF FND_API.to_boolean(p_commit) THEN
COMMIT;
SELECT ibe_msite_currencies_s1.NEXTVAL
FROM DUAL;
l_insert_row NUMBER := 0;
DELETE FROM IBE_MSITE_CURRENCIES where
msite_id = p_msite_id;
INSERT INTO IBE_MSITE_CURRENCIES (
MSITE_CURRENCY_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MSITE_ID,
CURRENCY_CODE,
WALKIN_PRC_LISTID,
REGISTERED_PRC_LISTID,
BIZPARTNER_PRC_LISTID,
ORDERABLE_limit,
PAYMENT_THRESHOLD,
PARTNER_PRC_LISTID)
VALUES (
l_msite_currencies_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
p_msite_id,
p_msite_currencies_tbl(l_index).currency_code,
p_msite_currencies_tbl(l_index).walkin_prc_lst_id,
p_msite_currencies_tbl(l_index).registered_prc_lst_id,
p_msite_currencies_tbl(l_index).biz_partner_prc_lst_id,
p_msite_currencies_tbl(l_index).orderable_limit,
l_payment_threshold,
p_msite_currencies_tbl(l_index).partner_prc_lst_id);
l_insert_row := l_insert_row + 1;
update IBE_MSITES_B SET
DEFAULT_CURRENCY_CODE =
p_msite_currencies_tbl(default_index).currency_code
WHERE MSITE_ID = p_msite_id;
if l_insert_row > 0 then
IF FND_API.to_boolean(p_commit) THEN
COMMIT;
l_insert_row NUMBER := 0;
SELECT ibe_msite_orgs_s1.NEXTVAL
FROM DUAL;
DELETE FROM IBE_MSITE_ORGS where
msite_id = p_msite_id;
INSERT INTO IBE_MSITE_ORGS (
MSITE_ORG_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MSITE_ID,
ORG_ID
)
VALUES (
l_msite_org_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
p_msite_id,
p_msite_orgids_tbl(l_index).orgid);
l_insert_row := l_insert_row + 1;
update IBE_MSITES_B SET
DEFAULT_ORG_ID = p_msite_orgids_tbl(default_index).orgid where
MSITE_ID = p_msite_id;
if l_insert_row > 0 then
IF FND_API.to_boolean(p_commit) THEN
COMMIT;
PROCEDURE delete_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_msite_id_tbl IN msite_delete_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_msite';
l_insert_row NUMBER := 0;
Select msite_resp_id
From IBE_MSITE_RESPS_B
Where msite_id = p_msite_id ;
SAVEPOINT delete_msite;
savepoint delete_msite_id;
ibe_physicalmap_grp.delete_msite(p_msite_id_tbl(l_index).msite_id);
delete from ibe_msite_languages where msite_id = p_msite_id_tbl(l_index).msite_id;
delete from ibe_msite_currencies where msite_id = p_msite_id_tbl(l_index).msite_id;
delete from ibe_msite_orgs where msite_id = p_msite_id_tbl(l_index).msite_id;
delete from ibe_dsp_msite_sct_sects where mini_site_id=p_msite_id_tbl(l_index).msite_id;
delete from ibe_dsp_msite_sct_items where mini_site_id=p_msite_id_tbl(l_index).msite_id;
delete from ibe_msites_tl where msite_id = p_msite_id_tbl(l_index).msite_id;
delete from ibe_msites_b where msite_id = p_msite_id_tbl(l_index).msite_id;
Ibe_Msite_Resp_Pvt.Delete_Msite_Resp(
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_msite_resp_id => rec_msite_resp.msite_resp_id ,
-- p_msite_id => FND_API.G_MISS_NUM,
--p_responsibility_id => FND_API.G_MISS_NUM,
--p_application_id => FND_API.G_MISS_NUM,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
delete from ibe_msite_prty_accss
where msite_id = p_msite_id_tbl(l_index).msite_id;
delete from ibe_wf_notif_msg_maps
where msite_id = p_msite_id_tbl(l_index).msite_id;
ROLLBACK TO delete_msite_id;
ROLLBACK TO delete_msite;
ROLLBACK TO delete_msite;
ROLLBACK TO delete_msite;
ROLLBACK TO delete_msite;
end delete_msite;
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_MSITE_ID in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_STORE_ID in NUMBER,
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
X_DEFAULT_CURRENCY_CODE in VARCHAR2,
X_DEFAULT_DATE_FORMAT in VARCHAR2,
X_DEFAULT_ORG_ID in NUMBER,
X_ATP_CHECK_FLAG in VARCHAR2,
X_WALKIN_ALLOWED_FLAG in VARCHAR2,
X_MSITE_ROOT_SECTION_ID in NUMBER,
X_PROFILE_ID in NUMBER,
X_MASTER_MSITE_FLAG in VARCHAR2,
X_MSITE_NAME in VARCHAR2,
X_MSITE_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_RESP_ACCESS_FLAG in VARCHAR2 ,
X_PARTY_ACCESS_CODE in VARCHAR2 ,
X_ACCESS_NAME in VARCHAR2 ,
X_URL in VARCHAR2 ,
X_THEME_ID in NUMBER ,
X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
X_DOMAIN_NAME in VARCHAR2 := NULL,
X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
X_REPORTING_STATUS in VARCHAR2 := 'N',
X_SITE_TYPE in VARCHAR2 := 'I')
is
cursor C is select ROWID from IBE_MSITES_B
where MSITE_ID = X_MSITE_ID
;
insert into IBE_MSITES_B (
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE11,
ATTRIBUTE10,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MSITE_ID,
OBJECT_VERSION_NUMBER,
STORE_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
DEFAULT_LANGUAGE_CODE,
DEFAULT_CURRENCY_CODE,
DEFAULT_DATE_FORMAT,
DEFAULT_ORG_ID,
ATP_CHECK_FLAG,
WALKIN_ALLOWED_FLAG,
MSITE_ROOT_SECTION_ID,
PROFILE_ID,
MASTER_MSITE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
RESP_ACCESS_FLAG ,
PARTY_ACCESS_CODE ,
ACCESS_NAME ,
URL ,
THEME_ID,
PAYMENT_THRESHOLD_ENABLE_FLAG,
DOMAIN_NAME,
ENABLE_TRAFFIC_FILTER,
REPORTING_STATUS,
SITE_TYPE
) values (
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE11,
X_ATTRIBUTE10,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_MSITE_ID,
X_OBJECT_VERSION_NUMBER,
X_STORE_ID,
X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE,
X_DEFAULT_LANGUAGE_CODE,
X_DEFAULT_CURRENCY_CODE,
X_DEFAULT_DATE_FORMAT,
X_DEFAULT_ORG_ID,
X_ATP_CHECK_FLAG,
X_WALKIN_ALLOWED_FLAG,
X_MSITE_ROOT_SECTION_ID,
X_PROFILE_ID,
X_MASTER_MSITE_FLAG,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN ,
X_RESP_ACCESS_FLAG ,
X_PARTY_ACCESS_CODE ,
X_ACCESS_NAME,
X_URL,
X_THEME_ID,
X_PAYMENT_THRESH_ENABLE_FLAG,
X_DOMAIN_NAME,
X_ENABLE_TRAFFIC_FILTER,
X_REPORTING_STATUS,
X_SITE_TYPE );
insert into IBE_MSITES_TL (
MSITE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MSITE_NAME,
MSITE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
X_MSITE_ID,
X_OBJECT_VERSION_NUMBER,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_MSITE_NAME,
X_MSITE_DESCRIPTION,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBE_MSITES_TL T
where T.MSITE_ID = X_MSITE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE11,
ATTRIBUTE10,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
STORE_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
DEFAULT_LANGUAGE_CODE,
DEFAULT_CURRENCY_CODE,
DEFAULT_DATE_FORMAT,
DEFAULT_ORG_ID,
ATP_CHECK_FLAG,
WALKIN_ALLOWED_FLAG,
MSITE_ROOT_SECTION_ID,
PROFILE_ID,
MASTER_MSITE_FLAG ,
RESP_ACCESS_FLAG ,
PARTY_ACCESS_CODE ,
ACCESS_NAME ,
URL ,
THEME_ID,
PAYMENT_THRESHOLD_ENABLE_FLAG,
DOMAIN_NAME,
ENABLE_TRAFFIC_FILTER,
REPORTING_STATUS,
SITE_TYPE
from IBE_MSITES_B
where MSITE_ID = X_MSITE_ID
for update of MSITE_ID nowait;
cursor c1 is select
MSITE_NAME,
MSITE_DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from IBE_MSITES_TL
where MSITE_ID = X_MSITE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of MSITE_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_MSITE_ID in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_STORE_ID in NUMBER,
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
X_DEFAULT_CURRENCY_CODE in VARCHAR2,
X_DEFAULT_DATE_FORMAT in VARCHAR2,
X_DEFAULT_ORG_ID in NUMBER,
X_ATP_CHECK_FLAG in VARCHAR2,
X_WALKIN_ALLOWED_FLAG in VARCHAR2,
X_MSITE_ROOT_SECTION_ID in NUMBER,
X_PROFILE_ID in NUMBER,
X_MASTER_MSITE_FLAG in VARCHAR2,
X_MSITE_NAME in VARCHAR2,
X_MSITE_DESCRIPTION in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER ,
X_RESP_ACCESS_FLAG in VARCHAR2 ,
X_PARTY_ACCESS_CODE in VARCHAR2 ,
X_ACCESS_NAME in VARCHAR2 ,
X_URL IN VARCHAR2 ,
X_THEME_ID IN NUMBER ,
X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
X_DOMAIN_NAME in VARCHAR2 := NULL,
X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
X_REPORTING_STATUS in VARCHAR2 := 'N',
X_SITE_TYPE in VARCHAR2 := 'I')
IS
begin
update IBE_MSITES_B set
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
STORE_ID = X_STORE_ID,
START_DATE_ACTIVE = X_START_DATE_ACTIVE,
END_DATE_ACTIVE = X_END_DATE_ACTIVE,
DEFAULT_LANGUAGE_CODE = X_DEFAULT_LANGUAGE_CODE,
DEFAULT_CURRENCY_CODE = X_DEFAULT_CURRENCY_CODE,
DEFAULT_DATE_FORMAT = X_DEFAULT_DATE_FORMAT,
DEFAULT_ORG_ID = X_DEFAULT_ORG_ID,
ATP_CHECK_FLAG = X_ATP_CHECK_FLAG,
WALKIN_ALLOWED_FLAG = X_WALKIN_ALLOWED_FLAG,
MSITE_ROOT_SECTION_ID = X_MSITE_ROOT_SECTION_ID,
PROFILE_ID = X_PROFILE_ID,
MASTER_MSITE_FLAG = X_MASTER_MSITE_FLAG,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN ,
RESP_ACCESS_FLAG = X_RESP_ACCESS_FLAG ,
PARTY_ACCESS_CODE = X_PARTY_ACCESS_CODE ,
ACCESS_NAME = X_ACCESS_NAME ,
URL = X_URL ,
THEME_ID = X_THEME_ID ,
PAYMENT_THRESHOLD_ENABLE_FLAG = X_PAYMENT_THRESH_ENABLE_FLAG,
DOMAIN_NAME = X_DOMAIN_NAME,
ENABLE_TRAFFIC_FILTER = X_ENABLE_TRAFFIC_FILTER,
REPORTING_STATUS = X_REPORTING_STATUS,
SITE_TYPE = X_SITE_TYPE
WHERE
MSITE_ID = X_MSITE_ID
AND OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
FND_API.G_MISS_NUM,
OBJECT_VERSION_NUMBER,
X_OBJECT_VERSION_NUMBER);
update IBE_MSITES_TL set
MSITE_NAME = X_MSITE_NAME,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
MSITE_DESCRIPTION = X_MSITE_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where MSITE_ID = X_MSITE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
FND_API.G_MISS_NUM,
OBJECT_VERSION_NUMBER,
X_OBJECT_VERSION_NUMBER);
end UPDATE_ROW;
procedure DELETE_ROW (
X_MSITE_ID in NUMBER
) IS
begin
delete from IBE_MSITES_TL
where MSITE_ID = X_MSITE_ID;
delete from IBE_MSITES_B
where MSITE_ID = X_MSITE_ID;
end DELETE_ROW;
X_LAST_UPDATE_DATE in varchar2,
X_CUSTOM_MODE in Varchar2
) IS
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from ibe_msites_tl
where msite_id = X_MSITE_ID
and language=userenv('LANG');-- bug #5089259
update ibe_msites_tl
set language = USERENV('LANG'),
source_lang = USERENV('LANG'),
object_version_number = object_version_number + 1,
msite_name = X_MSITE_NAME,
msite_description = X_MSITE_DESCRIPTION,
last_updated_by = f_luby,--decode(X_OWNER,'SEED',1,0),
last_update_date = f_ludate, --sysdate,
last_update_login=0
Where userenv('LANG') in (language,source_lang)
and msite_id = X_MSITE_ID;
X_LAST_UPDATE_DATE in varchar2,
X_CUSTOM_MODE in Varchar2)
IS
Owner_id NUMBER := 0;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from ibe_msites_b
where MSITE_ID = X_MSITE_ID;
UPDATE_ROW(
X_MSITE_ID => X_MSITE_ID,
X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => X_ATTRIBUTE1,
X_ATTRIBUTE2 => X_ATTRIBUTE2,
X_ATTRIBUTE3 => X_ATTRIBUTE3,
X_ATTRIBUTE4 => X_ATTRIBUTE4,
X_ATTRIBUTE5 => X_ATTRIBUTE5,
X_ATTRIBUTE6 => X_ATTRIBUTE6,
X_ATTRIBUTE7 => X_ATTRIBUTE7,
X_ATTRIBUTE8 => X_ATTRIBUTE8,
X_ATTRIBUTE9 => X_ATTRIBUTE9,
X_ATTRIBUTE11 => X_ATTRIBUTE10,
X_ATTRIBUTE10 => X_ATTRIBUTE11,
X_ATTRIBUTE12 => X_ATTRIBUTE12,
X_ATTRIBUTE13 => X_ATTRIBUTE13,
X_ATTRIBUTE14 => X_ATTRIBUTE14,
X_ATTRIBUTE15 => X_ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
X_STORE_ID => X_STORE_ID,
X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
X_DEFAULT_LANGUAGE_CODE => X_DEFAULT_LANGUAGE_CODE,
X_DEFAULT_CURRENCY_CODE => X_DEFAULT_CURRENCY_CODE,
X_DEFAULT_DATE_FORMAT => X_DEFAULT_DATE_FORMAT,
X_DEFAULT_ORG_ID => X_DEFAULT_ORG_ID,
X_ATP_CHECK_FLAG => X_ATP_CHECK_FLAG,
X_WALKIN_ALLOWED_FLAG => X_WALKIN_ALLOWED_FLAG,
X_MSITE_ROOT_SECTION_ID => X_MSITE_ROOT_SECTION_ID,
X_PROFILE_ID => X_PROFILE_ID,
X_MASTER_MSITE_FLAG => X_MASTER_MSITE_FLAG,
X_MSITE_NAME => X_MSITE_NAME,
X_MSITE_DESCRIPTION => X_MSITE_DESCRIPTION,
X_LAST_UPDATE_DATE => f_ludate, --SYSDATE,
X_LAST_UPDATED_BY => f_luby,--Owner_id,
X_LAST_UPDATE_LOGIN => 0 ,
X_RESP_ACCESS_FLAG => X_RESP_ACCESS_FLAG ,
X_PARTY_ACCESS_CODE => X_PARTY_ACCESS_CODE ,
X_ACCESS_NAME => X_ACCESS_NAME ,
X_URL => X_URL,
X_THEME_ID => X_THEME_ID,
X_PAYMENT_THRESH_ENABLE_FLAG => X_PAYMENT_THRESH_ENABLE_FLAG,
X_DOMAIN_NAME => X_DOMAIN_NAME,
X_ENABLE_TRAFFIC_FILTER => X_ENABLE_TRAFFIC_FILTER,
X_REPORTING_STATUS => X_REPORTING_STATUS,
X_SITE_TYPE => X_SITE_TYPE );
INSERT_ROW(
X_ROWID => Row_id,
X_MSITE_ID => X_MSITE_ID,
X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => X_ATTRIBUTE1,
X_ATTRIBUTE2 => X_ATTRIBUTE2,
X_ATTRIBUTE3 => X_ATTRIBUTE3,
X_ATTRIBUTE4 => X_ATTRIBUTE4,
X_ATTRIBUTE5 => X_ATTRIBUTE5,
X_ATTRIBUTE6 => X_ATTRIBUTE6,
X_ATTRIBUTE7 => X_ATTRIBUTE7,
X_ATTRIBUTE8 => X_ATTRIBUTE8,
X_ATTRIBUTE9 => X_ATTRIBUTE9,
X_ATTRIBUTE11 => X_ATTRIBUTE10,
X_ATTRIBUTE10 => X_ATTRIBUTE11,
X_ATTRIBUTE12 => X_ATTRIBUTE12,
X_ATTRIBUTE13 => X_ATTRIBUTE13,
X_ATTRIBUTE14 => X_ATTRIBUTE14,
X_ATTRIBUTE15 => X_ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
X_STORE_ID => X_STORE_ID,
X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
X_DEFAULT_LANGUAGE_CODE => X_DEFAULT_LANGUAGE_CODE,
X_DEFAULT_CURRENCY_CODE => X_DEFAULT_CURRENCY_CODE,
X_DEFAULT_DATE_FORMAT => X_DEFAULT_DATE_FORMAT,
X_DEFAULT_ORG_ID => X_DEFAULT_ORG_ID,
X_ATP_CHECK_FLAG => X_ATP_CHECK_FLAG,
X_WALKIN_ALLOWED_FLAG => X_WALKIN_ALLOWED_FLAG,
X_MSITE_ROOT_SECTION_ID => X_MSITE_ROOT_SECTION_ID,
X_PROFILE_ID => X_PROFILE_ID,
X_MASTER_MSITE_FLAG => X_MASTER_MSITE_FLAG,
X_MSITE_NAME => X_MSITE_NAME,
X_MSITE_DESCRIPTION => X_MSITE_DESCRIPTION,
X_CREATION_DATE => f_ludate, --SYSDATE,
X_CREATED_BY => f_luby,--Owner_id,
X_LAST_UPDATE_DATE => f_ludate, --SYSDATE,
X_LAST_UPDATED_BY => f_luby,--Owner_id,
X_LAST_UPDATE_LOGIN => 0 ,
X_RESP_ACCESS_FLAG => X_RESP_ACCESS_FLAG ,
X_PARTY_ACCESS_CODE => X_PARTY_ACCESS_CODE ,
X_ACCESS_NAME => X_ACCESS_NAME ,
X_URL => X_URL,
X_THEME_ID => X_THEME_ID,
X_PAYMENT_THRESH_ENABLE_FLAG => X_PAYMENT_THRESH_ENABLE_FLAG,
X_DOMAIN_NAME => X_DOMAIN_NAME,
X_ENABLE_TRAFFIC_FILTER => X_ENABLE_TRAFFIC_FILTER,
X_REPORTING_STATUS => X_REPORTING_STATUS,
X_SITE_TYPE => X_SITE_TYPE );
delete from IBE_MSITES_TL T
where not exists
(select NULL
from IBE_MSITES_B B
where B.MSITE_ID = T.MSITE_ID
);
update IBE_MSITES_TL T set (
MSITE_NAME,
MSITE_DESCRIPTION
) = (select
B.MSITE_NAME,
B.MSITE_DESCRIPTION
from IBE_MSITES_TL B
where B.MSITE_ID = T.MSITE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.MSITE_ID,
T.LANGUAGE
) in (select
SUBT.MSITE_ID,
SUBT.LANGUAGE
from IBE_MSITES_TL SUBB, IBE_MSITES_TL SUBT
where SUBB.MSITE_ID = SUBT.MSITE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.MSITE_NAME <> SUBT.MSITE_NAME
or (SUBB.MSITE_NAME is null and SUBT.MSITE_NAME is not null)
or (SUBB.MSITE_NAME is not null and SUBT.MSITE_NAME is null)
or SUBB.MSITE_DESCRIPTION <> SUBT.MSITE_DESCRIPTION
or (SUBB.MSITE_DESCRIPTION is null and SUBT.MSITE_DESCRIPTION is not null)
or (SUBB.MSITE_DESCRIPTION is not null and SUBT.MSITE_DESCRIPTION is null)
));
insert into IBE_MSITES_TL (
MSITE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MSITE_NAME,
MSITE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
B.MSITE_ID,
B.OBJECT_VERSION_NUMBER,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.MSITE_NAME,
B.MSITE_DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBE_MSITES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBE_MSITES_TL T
where T.MSITE_ID = B.MSITE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE IN VARCHAR2,
X_CUSTOM_MODE IN VARCHAR2,
X_UPLOAD_MODE IN VARCHAR2
)
IS
BEGIN
IF (X_UPLOAD_MODE = 'NLS') then
TRANSLATE_ROW(
X_MSITE_ID,
X_OWNER,
X_MSITE_NAME,
X_MSITE_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_CUSTOM_MODE);
X_LAST_UPDATE_DATE,
X_CUSTOM_MODE);