[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_deleted_ranges IN VARCHAR2
, p_new_ranges IN VARCHAR2);
PROCEDURE UPDATE_BIS_BUCKET_WRAPPER (
p_bucket_id IN BIS_BUCKET.bucket_id%TYPE
,p_short_name IN BIS_BUCKET.short_name%TYPE
,p_name IN BIS_BUCKET_TL.name%TYPE
,p_type IN BIS_BUCKET.type%TYPE
,p_application_id IN BIS_BUCKET.application_id%TYPE
,p_range1_name IN BIS_BUCKET_TL.range1_name%TYPE
,p_range1_low IN BIS_BUCKET.range1_low%TYPE
,p_range1_high IN BIS_BUCKET.range1_high%TYPE
,p_range2_name IN BIS_BUCKET_TL.range2_name%TYPE
,p_range2_low IN BIS_BUCKET.range2_low%TYPE
,p_range2_high IN BIS_BUCKET.range2_high%TYPE
,p_range3_name IN BIS_BUCKET_TL.range3_name%TYPE
,p_range3_low IN BIS_BUCKET.range3_low%TYPE
,p_range3_high IN BIS_BUCKET.range3_high%TYPE
,p_range4_name IN BIS_BUCKET_TL.range4_name%TYPE
,p_range4_low IN BIS_BUCKET.range4_low%TYPE
,p_range4_high IN BIS_BUCKET.range4_high%TYPE
,p_range5_name IN BIS_BUCKET_TL.range5_name%TYPE
,p_range5_low IN BIS_BUCKET.range5_low%TYPE
,p_range5_high IN BIS_BUCKET.range5_high%TYPE
,p_range6_name IN BIS_BUCKET_TL.range6_name%TYPE
,p_range6_low IN BIS_BUCKET.range6_low%TYPE
,p_range6_high IN BIS_BUCKET.range6_high%TYPE
,p_range7_name IN BIS_BUCKET_TL.range7_name%TYPE
,p_range7_low IN BIS_BUCKET.range7_low%TYPE
,p_range7_high IN BIS_BUCKET.range7_high%TYPE
,p_range8_name IN BIS_BUCKET_TL.range8_name%TYPE
,p_range8_low IN BIS_BUCKET.range8_low%TYPE
,p_range8_high IN BIS_BUCKET.range8_high%TYPE
,p_range9_name IN BIS_BUCKET_TL.range9_name%TYPE
,p_range9_low IN BIS_BUCKET.range9_low%TYPE
,p_range9_high IN BIS_BUCKET.range9_high%TYPE
,p_range10_name IN BIS_BUCKET_TL.range10_name%TYPE
,p_range10_low IN BIS_BUCKET.range10_low%TYPE
,p_range10_high IN BIS_BUCKET.range10_high%TYPE
,p_description IN BIS_BUCKET_TL.description%TYPE
,p_updatable IN BIS_BUCKET.updatable%TYPE := 'F'
,p_expandable IN BIS_BUCKET.expandable%TYPE := 'F'
,p_discontinuous IN BIS_BUCKET.discontinuous%TYPE := 'F'
,p_overlapping IN BIS_BUCKET.overlapping%TYPE := 'F'
,p_uom IN BIS_BUCKET.uom%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2
)
IS
l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
BIS_BUCKET_PVT.UPDATE_BIS_BUCKET (
p_bis_bucket_rec => l_bis_bucket_rec
,x_return_status => l_return_status
,x_error_tbl => l_error_tbl
);
END UPDATE_BIS_BUCKET_WRAPPER;
PROCEDURE UPDATE_CUST_BUCKET (
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_id IN BIS_BUCKET_CUSTOMIZATIONS.id%TYPE
, p_bucket_id IN BIS_BUCKET_CUSTOMIZATIONS.bucket_id%TYPE
, p_user_id IN BIS_BUCKET_CUSTOMIZATIONS.user_id%TYPE
, p_responsibility_id IN BIS_BUCKET_CUSTOMIZATIONS.responsibility_id%TYPE
, p_application_id IN BIS_BUCKET_CUSTOMIZATIONS.application_id%TYPE
, p_org_id IN BIS_BUCKET_CUSTOMIZATIONS.org_id%TYPE
, p_site_id IN BIS_BUCKET_CUSTOMIZATIONS.site_id%TYPE
, p_page_id IN BIS_BUCKET_CUSTOMIZATIONS.page_id%TYPE
, p_function_id IN BIS_BUCKET_CUSTOMIZATIONS.function_id%TYPE
, p_range1_low IN BIS_BUCKET_CUSTOMIZATIONS.range1_low%TYPE
, p_range1_high IN BIS_BUCKET_CUSTOMIZATIONS.range1_high%TYPE
, p_range2_low IN BIS_BUCKET_CUSTOMIZATIONS.range2_low%TYPE
, p_range2_high IN BIS_BUCKET_CUSTOMIZATIONS.range2_high%TYPE
, p_range3_low IN BIS_BUCKET_CUSTOMIZATIONS.range3_low%TYPE
, p_range3_high IN BIS_BUCKET_CUSTOMIZATIONS.range3_high%TYPE
, p_range4_low IN BIS_BUCKET_CUSTOMIZATIONS.range4_low%TYPE
, p_range4_high IN BIS_BUCKET_CUSTOMIZATIONS.range4_high%TYPE
, p_range5_low IN BIS_BUCKET_CUSTOMIZATIONS.range5_low%TYPE
, p_range5_high IN BIS_BUCKET_CUSTOMIZATIONS.range5_high%TYPE
, p_range6_low IN BIS_BUCKET_CUSTOMIZATIONS.range6_low%TYPE
, p_range6_high IN BIS_BUCKET_CUSTOMIZATIONS.range6_high%TYPE
, p_range7_low IN BIS_BUCKET_CUSTOMIZATIONS.range7_low%TYPE
, p_range7_high IN BIS_BUCKET_CUSTOMIZATIONS.range7_high%TYPE
, p_range8_low IN BIS_BUCKET_CUSTOMIZATIONS.range8_low%TYPE
, p_range8_high IN BIS_BUCKET_CUSTOMIZATIONS.range8_high%TYPE
, p_range9_low IN BIS_BUCKET_CUSTOMIZATIONS.range9_low%TYPE
, p_range9_high IN BIS_BUCKET_CUSTOMIZATIONS.range9_high%TYPE
, p_range10_low IN BIS_BUCKET_CUSTOMIZATIONS.range10_low%TYPE
, p_range10_high IN BIS_BUCKET_CUSTOMIZATIONS.range10_high%TYPE
, p_range1_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range1_name%TYPE
, p_range2_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range2_name%TYPE
, p_range3_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range3_name%TYPE
, p_range4_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range4_name%TYPE
, p_range5_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range5_name%TYPE
, p_range6_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range6_name%TYPE
, p_range7_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range7_name%TYPE
, p_range8_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range8_name%TYPE
, p_range9_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range9_name%TYPE
, p_range10_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range10_name%TYPE
, p_customized IN BIS_BUCKET_CUSTOMIZATIONS.customized%TYPE
, p_deleted_ranges IN VARCHAR2
, p_new_ranges IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
SAVEPOINT SP_UPDATE_BUCKET_CUST;
UPDATE bis_bucket_customizations SET
bucket_id = p_bucket_id,
user_id = p_user_id,
responsibility_id = p_responsibility_id,
application_id = p_application_id,
org_id = p_org_id,
site_id = p_site_id,
page_id = p_page_id,
function_id = p_function_id,
range1_low = p_range1_low,
range1_high = p_range1_high,
range2_low = p_range2_low,
range2_high = p_range2_high,
range3_low = p_range3_low,
range3_high = p_range3_high,
range4_low = p_range4_low,
range4_high = p_range4_high,
range5_low = p_range5_low,
range5_high = p_range5_high,
range6_low = p_range6_low,
range6_high = p_range6_high,
range7_low = p_range7_low,
range7_high = p_range7_high,
range8_low = p_range8_low,
range8_high = p_range8_high,
range9_low = p_range9_low,
range9_high = p_range9_high,
range10_low = p_range10_low,
range10_high = p_range10_high,
CUSTOMIZED = p_customized,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE id = p_id;
UPDATE bis_bucket_customizations_tl SET
range1_name = p_range1_name,
range2_name = p_range2_name,
range3_name = p_range3_name,
range4_name = p_range4_name,
range5_name = p_range5_name,
range6_name = p_range6_name,
range7_name = p_range7_name,
range8_name = p_range8_name,
range9_name = p_range9_name,
range10_name = p_range10_name,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
source_lang = userenv('LANG')
where id = p_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
, p_deleted_ranges => p_deleted_ranges
, p_new_ranges => p_new_ranges
);
ROLLBACK TO SP_UPDATE_BUCKET_CUST;
ROLLBACK TO SP_UPDATE_BUCKET_CUST;
x_msg_data := x_msg_data||' -> BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
x_msg_data := SQLERRM||' at BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
ROLLBACK TO SP_UPDATE_BUCKET_CUST;
x_msg_data := x_msg_data||' -> BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
x_msg_data := SQLERRM||' at BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
ROLLBACK TO SP_UPDATE_BUCKET_CUST;
END UPDATE_CUST_BUCKET;
, p_deleted_ranges IN VARCHAR2
, p_new_ranges IN VARCHAR2
)
IS
l_deleted_ranges VARCHAR2(100);
SELECT L.language_code FROM fnd_languages L
WHERE L.installed_flag IN ('I', 'B')
AND L.language_code <> userenv('LANG');
SELECT range1_name, range2_name, range3_name, range4_name,
range5_name, range6_name, range7_name, range8_name, range9_name, range10_name
FROM bis_bucket_customizations_tl
WHERE id = p_id
AND language = source_lang
AND source_lang = p_lang_code;
c_delete_marker VARCHAR2(10) := '@#!$';
Handle deleted ranges first for non-US rows.
Make the range name labels as NULL for the deleted range numbers.
*/
IF (p_deleted_ranges IS NOT NULL) THEN
l_deleted_ranges := p_deleted_ranges;
WHILE (LENGTH(l_deleted_ranges) > 0) LOOP
l_next_pos := INSTR(l_deleted_ranges, ',');
l_range_num := TO_NUMBER(l_deleted_ranges);
l_deleted_ranges := NULL;
l_range_num := TO_NUMBER(SUBSTR(l_deleted_ranges, 1, l_next_pos - 1));
l_deleted_ranges := SUBSTR(l_deleted_ranges, l_next_pos + 1);
l_range_labels(l_range_num) := c_delete_marker;
and insert the range labels from US rows for newly created blank range columns.
*/
IF (p_new_ranges IS NOT NULL) THEN
l_new_ranges := p_new_ranges;
Arrange all the ranges one after the other by shifting left over the deleted ranges.
*/
l_pointer := 1;
IF (l_range_labels(l_pointer) = c_delete_marker) THEN
FOR j IN l_pointer..(l_range_labels.COUNT - 1) LOOP
l_range_labels(j) := l_range_labels(j + 1);
Insert new range labels from US rows.
*/
IF (c_bucket%ISOPEN) THEN
CLOSE c_bucket;
UPDATE bis_bucket_customizations_tl
SET range1_name = NVL(l_range_labels(1), l_bucket_rec.range1_name),
range2_name = NVL(l_range_labels(2), l_bucket_rec.range2_name),
range3_name = NVL(l_range_labels(3), l_bucket_rec.range3_name),
range4_name = NVL(l_range_labels(4), l_bucket_rec.range4_name),
range5_name = NVL(l_range_labels(5), l_bucket_rec.range5_name),
range6_name = NVL(l_range_labels(6), l_bucket_rec.range6_name),
range7_name = NVL(l_range_labels(7), l_bucket_rec.range7_name),
range8_name = NVL(l_range_labels(8), l_bucket_rec.range8_name),
range9_name = NVL(l_range_labels(9), l_bucket_rec.range9_name),
range10_name = NVL(l_range_labels(10), l_bucket_rec.range10_name)
WHERE language = source_lang
AND language = l_lang_rec.language_code
AND id = p_id ;
SELECT range1_low, range1_high,
range2_low, range2_high,
range3_low, range3_high,
range4_low, range4_high,
range5_low, range5_high,
range6_low, range6_high,
range7_low, range7_high,
range8_low, range8_high,
range9_low, range9_high,
range10_low, range10_high
FROM bis_bucket
WHERE bucket_id = p_bucket_id;
SELECT range1_name,
range2_name,
range3_name,
range4_name,
range5_name,
range6_name,
range7_name,
range8_name,
range9_name,
range10_name
FROM bis_bucket_tl
WHERE bucket_id = p_bucket_id
AND language = p_lang;
SELECT L.language_code FROM fnd_languages L
WHERE L.installed_flag IN ('I', 'B');
UPDATE bis_bucket_customizations SET
user_id = NULL,
responsibility_id = NULL,
application_id = NULL,
org_id = NULL,
site_id = NULL,
page_id = NULL,
function_id = NULL,
range1_low = l_base_bucket_rec.range1_low,
range1_high = l_base_bucket_rec.range1_high,
range2_low = l_base_bucket_rec.range2_low,
range2_high = l_base_bucket_rec.range2_high,
range3_low = l_base_bucket_rec.range3_low,
range3_high = l_base_bucket_rec.range3_high,
range4_low = l_base_bucket_rec.range4_low,
range4_high = l_base_bucket_rec.range4_high,
range5_low = l_base_bucket_rec.range5_low,
range5_high = l_base_bucket_rec.range5_high,
range6_low = l_base_bucket_rec.range6_low,
range6_high = l_base_bucket_rec.range6_high,
range7_low = l_base_bucket_rec.range7_low,
range7_high = l_base_bucket_rec.range7_high,
range8_low = l_base_bucket_rec.range8_low,
range8_high = l_base_bucket_rec.range8_high,
range9_low = l_base_bucket_rec.range9_low,
range9_high = l_base_bucket_rec.range9_high,
range10_low = l_base_bucket_rec.range10_low,
range10_high = l_base_bucket_rec.range10_high,
CUSTOMIZED = 'F',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE bucket_id = p_bucket_id;
UPDATE bis_bucket_customizations_tl SET
range1_name = l_base_bucket_tl_rec.range1_name,
range2_name = l_base_bucket_tl_rec.range2_name,
range3_name = l_base_bucket_tl_rec.range3_name,
range4_name = l_base_bucket_tl_rec.range4_name,
range5_name = l_base_bucket_tl_rec.range5_name,
range6_name = l_base_bucket_tl_rec.range6_name,
range7_name = l_base_bucket_tl_rec.range7_name,
range8_name = l_base_bucket_tl_rec.range8_name,
range9_name = l_base_bucket_tl_rec.range9_name,
range10_name = l_base_bucket_tl_rec.range10_name,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE id IN (SELECT id FROM bis_bucket_customizations WHERE bucket_id = p_bucket_id)
AND language = l_lang_rec.language_code;
PROCEDURE DELETE_BIS_BUCKET_WRAPPER (
p_bucket_id IN BIS_BUCKET.bucket_id%TYPE := BIS_UTILITIES_PUB.G_NULL_NUM
,p_short_name IN BIS_BUCKET.short_name%TYPE := BIS_UTILITIES_PUB.G_NULL_CHAR
,x_return_status OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(10);
BIS_BUCKET_PVT.DELETE_BIS_BUCKET(
p_bucket_id => p_bucket_id
,p_short_name => p_short_name
,x_return_status => l_return_status
,x_error_tbl => l_error_tbl
);
END DELETE_BIS_BUCKET_WRAPPER;
SELECT COUNT(short_name) INTO l_short_count
FROM BIS_BUCKET
WHERE short_name = l_bis_bucket_rec.short_name;
SELECT bis_bucket_s.nextval INTO l_bucket_id FROM dual;
INSERT into BIS_BUCKET (
BUCKET_ID,
SHORT_NAME,
TYPE,
APPLICATION_ID,
RANGE1_LOW,
RANGE1_HIGH,
RANGE2_LOW,
RANGE2_HIGH,
RANGE3_LOW,
RANGE3_HIGH,
RANGE4_LOW,
RANGE4_HIGH,
RANGE5_LOW,
RANGE5_HIGH,
RANGE6_LOW,
RANGE6_HIGH,
RANGE7_LOW,
RANGE7_HIGH,
RANGE8_LOW,
RANGE8_HIGH,
RANGE9_LOW,
RANGE9_HIGH,
RANGE10_LOW,
RANGE10_HIGH,
UPDATABLE,
EXPANDABLE,
DISCONTINUOUS,
OVERLAPPING,
UOM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (
l_bucket_id,
l_bis_bucket_rec.short_name,
l_bis_bucket_rec.type,
l_bis_bucket_rec.application_id,
l_bis_bucket_rec.range1_low,
l_bis_bucket_rec.range1_high,
l_bis_bucket_rec.range2_low,
l_bis_bucket_rec.range2_high,
l_bis_bucket_rec.range3_low,
l_bis_bucket_rec.range3_high,
l_bis_bucket_rec.range4_low,
l_bis_bucket_rec.range4_high,
l_bis_bucket_rec.range5_low,
l_bis_bucket_rec.range5_high,
l_bis_bucket_rec.range6_low,
l_bis_bucket_rec.range6_high,
l_bis_bucket_rec.range7_low,
l_bis_bucket_rec.range7_high,
l_bis_bucket_rec.range8_low,
l_bis_bucket_rec.range8_high,
l_bis_bucket_rec.range9_low,
l_bis_bucket_rec.range9_high,
l_bis_bucket_rec.range10_low,
l_bis_bucket_rec.range10_high,
NVL(l_bis_bucket_rec.updatable,'F'),
NVL(l_bis_bucket_rec.expandable,'F'),
NVL(l_bis_bucket_rec.discontinuous,'F'),
NVL(l_bis_bucket_rec.overlapping,'F'),
l_bis_bucket_rec.uom,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
);
INSERT into BIS_BUCKET_TL (
BUCKET_ID,
LANGUAGE,
NAME,
RANGE1_NAME,
RANGE2_NAME,
RANGE3_NAME,
RANGE4_NAME,
RANGE5_NAME,
RANGE6_NAME,
RANGE7_NAME,
RANGE8_NAME,
RANGE9_NAME,
RANGE10_NAME,
DESCRIPTION,
TRANSLATED,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT
l_bucket_id,
L.LANGUAGE_CODE,
l_bis_bucket_rec.name,
l_bis_bucket_rec.range1_name,
l_bis_bucket_rec.range2_name,
l_bis_bucket_rec.range3_name,
l_bis_bucket_rec.range4_name,
l_bis_bucket_rec.range5_name,
l_bis_bucket_rec.range6_name,
l_bis_bucket_rec.range7_name,
l_bis_bucket_rec.range8_name,
l_bis_bucket_rec.range9_name,
l_bis_bucket_rec.range10_name,
l_bis_bucket_rec.description,
'Y',
userenv('LANG'),
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT exists (
SELECT NULL
FROM BIS_BUCKET_TL B
WHERE B.BUCKET_ID = l_bucket_id
AND B.LANGUAGE = L.LANGUAGE_CODE
);
SELECT bis_bucket_customizations_s.nextval
INTO l_id
FROM DUAL;
INSERT INTO bis_bucket_customizations
(ID,
BUCKET_ID,
RANGE1_LOW,
RANGE1_HIGH,
RANGE2_LOW,
RANGE2_HIGH,
RANGE3_LOW,
RANGE3_HIGH,
RANGE4_LOW,
RANGE4_HIGH,
RANGE5_LOW,
RANGE5_HIGH,
RANGE6_LOW,
RANGE6_HIGH,
RANGE7_LOW,
RANGE7_HIGH,
RANGE8_LOW,
RANGE8_HIGH,
RANGE9_LOW,
RANGE9_HIGH,
RANGE10_LOW,
RANGE10_HIGH,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CUSTOMIZED)
VALUES
( l_id,
l_bucket_id,
l_bis_bucket_rec.RANGE1_LOW,
l_bis_bucket_rec.RANGE1_HIGH,
l_bis_bucket_rec.RANGE2_LOW,
l_bis_bucket_rec.RANGE2_HIGH,
l_bis_bucket_rec.RANGE3_LOW,
l_bis_bucket_rec.RANGE3_HIGH,
l_bis_bucket_rec.RANGE4_LOW,
l_bis_bucket_rec.RANGE4_HIGH,
l_bis_bucket_rec.RANGE5_LOW,
l_bis_bucket_rec.RANGE5_HIGH,
l_bis_bucket_rec.RANGE6_LOW,
l_bis_bucket_rec.RANGE6_HIGH,
l_bis_bucket_rec.RANGE7_LOW,
l_bis_bucket_rec.RANGE7_HIGH,
l_bis_bucket_rec.RANGE8_LOW,
l_bis_bucket_rec.RANGE8_HIGH,
l_bis_bucket_rec.RANGE9_LOW,
l_bis_bucket_rec.RANGE9_HIGH,
l_bis_bucket_rec.RANGE10_LOW,
l_bis_bucket_rec.RANGE10_HIGH,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
'F');
INSERT INTO bis_bucket_customizations_tl
(ID,
RANGE1_NAME,
RANGE2_NAME,
RANGE3_NAME,
RANGE4_NAME,
RANGE5_NAME,
RANGE6_NAME,
RANGE7_NAME,
RANGE8_NAME,
RANGE9_NAME,
RANGE10_NAME,
LANGUAGE,
TRANSLATED,
SOURCE_LANG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT
l_id,
l_bis_bucket_rec.range1_name,
l_bis_bucket_rec.range2_name,
l_bis_bucket_rec.range3_name,
l_bis_bucket_rec.range4_name,
l_bis_bucket_rec.range5_name,
l_bis_bucket_rec.range6_name,
l_bis_bucket_rec.range7_name,
l_bis_bucket_rec.range8_name,
l_bis_bucket_rec.range9_name,
l_bis_bucket_rec.range10_name,
L.LANGUAGE_CODE,
'Y',
userenv('LANG'),
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BIS_BUCKET_CUSTOMIZATIONS_TL T
where T.ID = l_id
and T.LANGUAGE = L.LANGUAGE_CODE);
PROCEDURE UPDATE_BIS_BUCKET (
p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
--l_bis_bucket_rec_orig BIS_BUCKET_PUB.bis_bucket_rec_type;
SAVEPOINT SP_UPDATE_BUCKET;
SELECT bucket_id, short_name INTO l_bucket_id, l_short_name
FROM BIS_BUCKET
WHERE SHORT_NAME = p_bis_bucket_rec.short_name;
SELECT bucket_id, short_name INTO l_bucket_id, l_short_name
FROM BIS_BUCKET
WHERE BUCKET_ID = p_bis_bucket_rec.bucket_id;
,p_error_proc_name => G_PKG_NAME||'.UPDATE_BIS_BUCKET'
,p_error_type => BIS_UTILITIES_PUB.G_ERROR
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
UPDATE BIS_BUCKET SET
TYPE = l_bis_bucket_rec.type
,APPLICATION_ID = l_bis_bucket_rec.application_id
,RANGE1_LOW = l_bis_bucket_rec.range1_low
,RANGE1_HIGH = l_bis_bucket_rec.range1_high
,RANGE2_LOW = l_bis_bucket_rec.range2_low
,RANGE2_HIGH = l_bis_bucket_rec.range2_high
,RANGE3_LOW = l_bis_bucket_rec.range3_low
,RANGE3_HIGH = l_bis_bucket_rec.range3_high
,RANGE4_LOW = l_bis_bucket_rec.range4_low
,RANGE4_HIGH = l_bis_bucket_rec.range4_high
,RANGE5_LOW = l_bis_bucket_rec.range5_low
,RANGE5_HIGH = l_bis_bucket_rec.range5_high
,RANGE6_LOW = l_bis_bucket_rec.range6_low
,RANGE6_HIGH = l_bis_bucket_rec.range6_high
,RANGE7_LOW = l_bis_bucket_rec.range7_low
,RANGE7_HIGH = l_bis_bucket_rec.range7_high
,RANGE8_LOW = l_bis_bucket_rec.range8_low
,RANGE8_HIGH = l_bis_bucket_rec.range8_high
,RANGE9_LOW = l_bis_bucket_rec.range9_low
,RANGE9_HIGH = l_bis_bucket_rec.range9_high
,RANGE10_LOW = l_bis_bucket_rec.range10_low
,RANGE10_HIGH = l_bis_bucket_rec.range10_high
,UPDATABLE = NVL(l_bis_bucket_rec.updatable,'F')
,EXPANDABLE = NVL(l_bis_bucket_rec.expandable,'F')
,DISCONTINUOUS = NVL(l_bis_bucket_rec.discontinuous,'F')
,OVERLAPPING = NVL(l_bis_bucket_rec.overlapping,'F')
,UOM = l_bis_bucket_rec.uom
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = l_user_id
,LAST_UPDATE_LOGIN = l_login_id
WHERE SHORT_NAME = l_short_name;
UPDATE BIS_BUCKET_TL SET
NAME = l_bis_bucket_rec.name
,RANGE1_NAME = l_bis_bucket_rec.range1_name
,RANGE2_NAME = l_bis_bucket_rec.range2_name
,RANGE3_NAME = l_bis_bucket_rec.range3_name
,RANGE4_NAME = l_bis_bucket_rec.range4_name
,RANGE5_NAME = l_bis_bucket_rec.range5_name
,RANGE6_NAME = l_bis_bucket_rec.range6_name
,RANGE7_NAME = l_bis_bucket_rec.range7_name
,RANGE8_NAME = l_bis_bucket_rec.range8_name
,RANGE9_NAME = l_bis_bucket_rec.range9_name
,RANGE10_NAME = l_bis_bucket_rec.range10_name
,DESCRIPTION = l_bis_bucket_rec.description
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = l_user_id
,LAST_UPDATE_LOGIN = l_login_id
,SOURCE_LANG = userenv('LANG')
WHERE BUCKET_ID = l_bucket_Id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
SELECT ID, CUSTOMIZED INTO l_id, l_custom
FROM bis_bucket_customizations
WHERE bucket_id = l_bucket_Id AND rownum < 2;
IF (l_custom = 'F') THEN --update cust tables
update BIS_BUCKET_CUSTOMIZATIONS set
RANGE1_LOW = l_bis_bucket_rec.range1_low
,RANGE1_HIGH = l_bis_bucket_rec.range1_high
,RANGE2_LOW = l_bis_bucket_rec.range2_low
,RANGE2_HIGH = l_bis_bucket_rec.range2_high
,RANGE3_LOW = l_bis_bucket_rec.range3_low
,RANGE3_HIGH = l_bis_bucket_rec.range3_high
,RANGE4_LOW = l_bis_bucket_rec.range4_low
,RANGE4_HIGH = l_bis_bucket_rec.range4_high
,RANGE5_LOW = l_bis_bucket_rec.range5_low
,RANGE5_HIGH = l_bis_bucket_rec.range5_high
,RANGE6_LOW = l_bis_bucket_rec.range6_low
,RANGE6_HIGH = l_bis_bucket_rec.range6_high
,RANGE7_LOW = l_bis_bucket_rec.range7_low
,RANGE7_HIGH = l_bis_bucket_rec.range7_high
,RANGE8_LOW = l_bis_bucket_rec.range8_low
,RANGE8_HIGH = l_bis_bucket_rec.range8_high
,RANGE9_LOW = l_bis_bucket_rec.range9_low
,RANGE9_HIGH = l_bis_bucket_rec.range9_high
,RANGE10_LOW = l_bis_bucket_rec.range10_low
,RANGE10_HIGH = l_bis_bucket_rec.range10_high
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = l_user_id
,LAST_UPDATE_LOGIN = l_login_id
where BUCKET_ID = l_bucket_Id;
update BIS_BUCKET_CUSTOMIZATIONS_TL set
RANGE1_NAME = l_bis_bucket_rec.range1_name
,RANGE2_NAME = l_bis_bucket_rec.range2_name
,RANGE3_NAME = l_bis_bucket_rec.range3_name
,RANGE4_NAME = l_bis_bucket_rec.range4_name
,RANGE5_NAME = l_bis_bucket_rec.range5_name
,RANGE6_NAME = l_bis_bucket_rec.range6_name
,RANGE7_NAME = l_bis_bucket_rec.range7_name
,RANGE8_NAME = l_bis_bucket_rec.range8_name
,RANGE9_NAME = l_bis_bucket_rec.range9_name
,RANGE10_NAME = l_bis_bucket_rec.range10_name
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = l_user_id
,LAST_UPDATE_LOGIN = l_login_id
,SOURCE_LANG = userenv('LANG')
where ID = l_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
ROLLBACK TO SAVEPOINT SP_UPDATE_BUCKET;
,p_error_proc_name => G_PKG_NAME||'.UPDATE_BIS_BUCKET'
,p_error_type => BIS_UTILITIES_PUB.G_ERROR
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
ROLLBACK TO SAVEPOINT SP_UPDATE_BUCKET;
ROLLBACK TO SAVEPOINT SP_UPDATE_BUCKET;
,p_error_proc_name => G_PKG_NAME||'.UPDATE_BIS_BUCKET'
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
END UPDATE_BIS_BUCKET;
PROCEDURE DELETE_BIS_BUCKET (
p_bucket_id IN BIS_BUCKET.bucket_id%TYPE := BIS_UTILITIES_PUB.G_NULL_NUM
,p_short_name IN BIS_BUCKET.short_name%TYPE := BIS_UTILITIES_PUB.G_NULL_CHAR
,x_return_status OUT NOCOPY VARCHAR2
,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_dcount NUMBER;
SELECT bucket_id INTO l_bucket_id FROM BIS_BUCKET
WHERE SHORT_NAME = p_short_name;
DELETE from BIS_BUCKET_CUSTOMIZATIONS_TL
WHERE id in (
SELECT id
FROM BIS_BUCKET_CUSTOMIZATIONS
WHERE bucket_id = l_bucket_id);
DELETE from BIS_BUCKET_CUSTOMIZATIONS
WHERE bucket_id = l_bucket_id;
DELETE from BIS_BUCKET_TL
WHERE bucket_id = l_bucket_id;
DELETE from BIS_BUCKET
WHERE short_name = p_short_name;
SELECT count(BUCKET_ID) INTO l_dcount FROM BIS_BUCKET
WHERE BUCKET_ID = p_bucket_id;
,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
,p_error_type => BIS_UTILITIES_PUB.G_ERROR
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
DELETE from BIS_BUCKET_CUSTOMIZATIONS_TL
WHERE id in (
SELECT id
FROM BIS_BUCKET_CUSTOMIZATIONS
WHERE bucket_id = p_bucket_id);
DELETE from BIS_BUCKET_CUSTOMIZATIONS
WHERE bucket_id = p_bucket_id;
DELETE from BIS_BUCKET_TL
WHERE bucket_id = p_bucket_id;
DELETE from BIS_BUCKET
WHERE bucket_id = p_bucket_id;
,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
,p_error_type => BIS_UTILITIES_PUB.G_ERROR
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
,p_error_type => BIS_UTILITIES_PUB.G_ERROR
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
,p_error_proc_name => G_PKG_NAME||'.DELETE_BIS_BUCKET'
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
END DELETE_BIS_BUCKET;
SELECT
BC.BUCKET_ID,
B.NAME,
B.TYPE,
BC.APPLICATION_ID,
BC.RANGE1_NAME,
BC.RANGE1_LOW,
BC.RANGE1_HIGH,
BC.RANGE2_NAME,
BC.RANGE2_LOW,
BC.RANGE2_HIGH,
BC.RANGE3_NAME,
BC.RANGE3_LOW,
BC.RANGE3_HIGH,
BC.RANGE4_NAME,
BC.RANGE4_LOW,
BC.RANGE4_HIGH,
BC.RANGE5_NAME,
BC.RANGE5_LOW,
BC.RANGE5_HIGH,
BC.RANGE6_NAME,
BC.RANGE6_LOW,
BC.RANGE6_HIGH,
BC.RANGE7_NAME,
BC.RANGE7_LOW,
BC.RANGE7_HIGH,
BC.RANGE8_NAME,
BC.RANGE8_LOW,
BC.RANGE8_HIGH,
BC.RANGE9_NAME,
BC.RANGE9_LOW,
BC.RANGE9_HIGH,
BC.RANGE10_NAME,
BC.RANGE10_LOW,
BC.RANGE10_HIGH,
B.DESCRIPTION,
B.UPDATABLE,
B.EXPANDABLE,
B.DISCONTINUOUS,
B.OVERLAPPING,
B.UOM
FROM BIS_BUCKET_CUSTOMIZATIONS_VL BC,BIS_BUCKET_VL B
WHERE B.SHORT_NAME = p_short_name
AND B.BUCKET_ID=BC.BUCKET_ID;
SELECT count(SHORT_NAME) INTO l_dcount FROM BIS_BUCKET
WHERE SHORT_NAME = p_short_name;
,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
,p_error_type => BIS_UTILITIES_PUB.G_ERROR
,p_error_table => l_error_tbl
,x_error_table => x_error_tbl
);
UPDATE BIS_BUCKET_TL
SET
NAME = l_bis_bucket_rec.name
,RANGE1_NAME = l_bis_bucket_rec.range1_name
,RANGE2_NAME = l_bis_bucket_rec.range2_name
,RANGE3_NAME = l_bis_bucket_rec.range3_name
,RANGE4_NAME = l_bis_bucket_rec.range4_name
,RANGE5_NAME = l_bis_bucket_rec.range5_name
,RANGE6_NAME = l_bis_bucket_rec.range6_name
,RANGE7_NAME = l_bis_bucket_rec.range7_name
,RANGE8_NAME = l_bis_bucket_rec.range8_name
,RANGE9_NAME = l_bis_bucket_rec.range9_name
,RANGE10_NAME = l_bis_bucket_rec.range10_name
,DESCRIPTION = l_bis_bucket_rec.description
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = l_user_id
,LAST_UPDATE_LOGIN = l_login_id
,SOURCE_LANG = userenv('LANG')
WHERE BUCKET_ID = l_bis_bucket_rec.bucket_id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
UPDATE BIS_BUCKET_CUSTOMIZATIONS_TL
SET
RANGE1_NAME = l_bis_bucket_rec.range1_name
,RANGE2_NAME = l_bis_bucket_rec.range2_name
,RANGE3_NAME = l_bis_bucket_rec.range3_name
,RANGE4_NAME = l_bis_bucket_rec.range4_name
,RANGE5_NAME = l_bis_bucket_rec.range5_name
,RANGE6_NAME = l_bis_bucket_rec.range6_name
,RANGE7_NAME = l_bis_bucket_rec.range7_name
,RANGE8_NAME = l_bis_bucket_rec.range8_name
,RANGE9_NAME = l_bis_bucket_rec.range9_name
,RANGE10_NAME = l_bis_bucket_rec.range10_name
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = l_user_id
,LAST_UPDATE_LOGIN = l_login_id
,SOURCE_LANG = userenv('LANG')
WHERE id in (
SELECT id
FROM BIS_BUCKET_CUSTOMIZATIONS
WHERE bucket_id = l_bis_bucket_rec.bucket_id)
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
SELECT 1
FROM fnd_lookup_types a, fnd_lookup_values b
WHERE UPPER(b.lookup_code) = UPPER(cp_lookup_code)
AND b.lookup_type = a.lookup_type
AND a.lookup_type = 'BIS_BUCKET_TYPE';
SELECT region_code
FROM ak_region_items
WHERE (attribute1 = p1
OR attribute1 IS NULL)
AND attribute2 = p2
GROUP BY region_code;
SELECT user_function_name
FROM fnd_form_functions_vl
WHERE parameters like '%pRegionCode=%' -- For perf tuning.
AND parameters like '%pRegionCode=' || p1 || '&%'
AND type = 'WWW';
SELECT user_function_name
FROM fnd_form_functions_vl
WHERE upper(web_html_call) like 'BISVIEWER.SHOWREPORT(''' || p1 || '''%'
AND type = 'WWW';
select short_name into l_bucket_short_name
FROM bis_bucket
WHERE bucket_id = p_bucket_id;
SELECT COUNT(application_id) into l_count
FROM FND_APPLICATION
WHERE APPLICATION_ID = p_application_id;
DELETE FROM BIS_BUCKET_TL T
WHERE NOT EXISTS
(SELECT NULL
FROM BIS_BUCKET B
WHERE B.BUCKET_ID = T.BUCKET_ID
);
UPDATE BIS_BUCKET_TL T SET (
NAME,
RANGE1_NAME,
RANGE2_NAME,
RANGE3_NAME,
RANGE4_NAME,
RANGE5_NAME,
RANGE6_NAME,
RANGE7_NAME,
RANGE8_NAME,
RANGE9_NAME,
RANGE10_NAME,
DESCRIPTION
) = (SELECT
B.NAME,
B.RANGE1_NAME,
B.RANGE2_NAME,
B.RANGE3_NAME,
B.RANGE4_NAME,
B.RANGE5_NAME,
B.RANGE6_NAME,
B.RANGE7_NAME,
B.RANGE8_NAME,
B.RANGE9_NAME,
B.RANGE10_NAME,
B.DESCRIPTION
FROM BIS_BUCKET_TL B
WHERE B.BUCKET_ID = T.BUCKET_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.BUCKET_ID,
T.LANGUAGE
) IN (SELECT
SUBT.BUCKET_ID,
SUBT.LANGUAGE
FROM BIS_BUCKET_TL SUBB, BIS_BUCKET_TL SUBT
WHERE SUBB.BUCKET_ID = SUBT.BUCKET_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.NAME <> SUBT.NAME
OR SUBB.RANGE1_NAME <> SUBT.RANGE1_NAME
OR (SUBB.RANGE1_NAME IS NULL AND SUBT.RANGE1_NAME IS NOT NULL)
OR (SUBB.RANGE1_NAME IS NOT NULL AND SUBT.RANGE1_NAME IS NULL)
OR SUBB.RANGE2_NAME <> SUBT.RANGE2_NAME
OR (SUBB.RANGE2_NAME IS NULL AND SUBT.RANGE2_NAME IS NOT NULL)
OR (SUBB.RANGE2_NAME IS NOT NULL AND SUBT.RANGE2_NAME IS NULL)
OR SUBB.RANGE3_NAME <> SUBT.RANGE3_NAME
OR (SUBB.RANGE3_NAME IS NULL AND SUBT.RANGE3_NAME IS NOT NULL)
OR (SUBB.RANGE3_NAME IS NOT NULL AND SUBT.RANGE3_NAME IS NULL)
OR SUBB.RANGE4_NAME <> SUBT.RANGE4_NAME
OR (SUBB.RANGE4_NAME IS NULL AND SUBT.RANGE4_NAME IS NOT NULL)
OR (SUBB.RANGE4_NAME IS NOT NULL AND SUBT.RANGE4_NAME IS NULL)
OR SUBB.RANGE5_NAME <> SUBT.RANGE5_NAME
OR (SUBB.RANGE5_NAME IS NULL AND SUBT.RANGE5_NAME IS NOT NULL)
OR (SUBB.RANGE5_NAME IS NOT NULL AND SUBT.RANGE5_NAME IS NULL)
OR SUBB.RANGE6_NAME <> SUBT.RANGE6_NAME
OR (SUBB.RANGE6_NAME IS NULL AND SUBT.RANGE6_NAME IS NOT NULL)
OR (SUBB.RANGE6_NAME IS NOT NULL AND SUBT.RANGE6_NAME IS NULL)
OR SUBB.RANGE7_NAME <> SUBT.RANGE7_NAME
OR (SUBB.RANGE7_NAME IS NULL AND SUBT.RANGE7_NAME IS NOT NULL)
OR (SUBB.RANGE7_NAME IS NOT NULL AND SUBT.RANGE7_NAME IS NULL)
OR SUBB.RANGE8_NAME <> SUBT.RANGE8_NAME
OR (SUBB.RANGE8_NAME IS NULL AND SUBT.RANGE8_NAME IS NOT NULL)
OR (SUBB.RANGE8_NAME IS NOT NULL AND SUBT.RANGE8_NAME IS NULL)
OR SUBB.RANGE9_NAME <> SUBT.RANGE9_NAME
OR (SUBB.RANGE9_NAME IS NULL AND SUBT.RANGE9_NAME IS NOT NULL)
OR (SUBB.RANGE9_NAME IS NOT NULL AND SUBT.RANGE9_NAME IS NULL)
OR SUBB.RANGE10_NAME <> SUBT.RANGE10_NAME
OR (SUBB.RANGE10_NAME IS NULL AND SUBT.RANGE10_NAME IS NOT NULL)
OR (SUBB.RANGE10_NAME IS NOT NULL AND SUBT.RANGE10_NAME IS NULL)
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
));
INSERT INTO BIS_BUCKET_TL (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
BUCKET_ID,
NAME,
RANGE1_NAME,
RANGE2_NAME,
RANGE3_NAME,
RANGE4_NAME,
RANGE5_NAME,
RANGE6_NAME,
RANGE7_NAME,
RANGE8_NAME,
RANGE9_NAME,
RANGE10_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) SELECT /*+ ORDERED */
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.BUCKET_ID,
B.NAME,
B.RANGE1_NAME,
B.RANGE2_NAME,
B.RANGE3_NAME,
B.RANGE4_NAME,
B.RANGE5_NAME,
B.RANGE6_NAME,
B.RANGE7_NAME,
B.RANGE8_NAME,
B.RANGE9_NAME,
B.RANGE10_NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM BIS_BUCKET_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND B.LANGUAGE = userenv('LANG')
AND NOT EXISTS
(SELECT NULL
FROM BIS_BUCKET_TL T
WHERE T.BUCKET_ID = B.BUCKET_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);