[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* PROCEDURE TO DO INSERTS INTO THE MLSED TABLES */
PROCEDURE Insert_Row(
px_PLAN_ID IN OUT NOCOPY NUMBER,
p_ORIGINAL_PLAN_ID IN NUMBER,
p_PLAN_GROUP_CODE IN VARCHAR2,
p_START_DATE_ACTIVE IN DATE,
p_END_DATE_ACTIVE IN DATE,
p_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
p_END_USER_TYPE IN VARCHAR2,
p_CUSTOMIZED_PLAN IN VARCHAR2,
p_PROFILE_CHECK_ID IN NUMBER,
p_RELATIONAL_OPERATOR IN VARCHAR2,
p_CRITERIA_VALUE_HIGH IN VARCHAR2,
p_CRITERIA_VALUE_LOW IN VARCHAR2,
p_CREATION_DATE IN DATE,
p_LAST_UPDATE_DATE IN DATE,
p_CREATED_BY IN NUMBER,
p_LAST_UPDATED_BY IN NUMBER,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_ATTRIBUTE1 IN VARCHAR2,
p_ATTRIBUTE2 IN VARCHAR2,
p_ATTRIBUTE3 IN VARCHAR2,
p_ATTRIBUTE4 IN VARCHAR2,
p_ATTRIBUTE5 IN VARCHAR2,
p_ATTRIBUTE6 IN VARCHAR2,
p_ATTRIBUTE7 IN VARCHAR2,
p_ATTRIBUTE8 IN VARCHAR2,
p_ATTRIBUTE9 IN VARCHAR2,
p_ATTRIBUTE10 IN VARCHAR2,
p_ATTRIBUTE11 IN VARCHAR2,
p_ATTRIBUTE12 IN VARCHAR2,
p_ATTRIBUTE13 IN VARCHAR2,
p_ATTRIBUTE14 IN VARCHAR2,
p_ATTRIBUTE15 IN VARCHAR2,
p_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_NAME IN VARCHAR2,
P_APPLICATION_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER )
IS
cursor C is
select rowid
from CSC_PLAN_HEADERS_B
where PLAN_ID = PX_PLAN_ID ;
SELECT CSC_PLAN_HEADERS_S.nextval
FROM sys.dual;
INSERT INTO CSC_PLAN_HEADERS_B (
PLAN_ID,
ORIGINAL_PLAN_ID,
PLAN_GROUP_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
USE_FOR_CUST_ACCOUNT,
END_USER_TYPE,
CUSTOMIZED_PLAN,
PROFILE_CHECK_ID,
RELATIONAL_OPERATOR,
CRITERIA_VALUE_HIGH,
CRITERIA_VALUE_LOW,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
APPLICATION_ID,
SEEDED_FLAG,
OBJECT_VERSION_NUMBER)
VALUES (
PX_PLAN_ID,
nvl(P_ORIGINAL_PLAN_ID, PX_PLAN_ID),
P_PLAN_GROUP_CODE,
P_START_DATE_ACTIVE,
P_END_DATE_ACTIVE,
P_USE_FOR_CUST_ACCOUNT,
P_END_USER_TYPE,
P_CUSTOMIZED_PLAN,
P_PROFILE_CHECK_ID,
P_RELATIONAL_OPERATOR,
P_CRITERIA_VALUE_HIGH,
P_CRITERIA_VALUE_LOW,
P_CREATION_DATE,
P_LAST_UPDATE_DATE,
P_CREATED_BY,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4,
P_ATTRIBUTE5,
P_ATTRIBUTE6,
P_ATTRIBUTE7,
P_ATTRIBUTE8,
P_ATTRIBUTE9,
P_ATTRIBUTE10,
P_ATTRIBUTE11,
P_ATTRIBUTE12,
P_ATTRIBUTE13,
P_ATTRIBUTE14,
P_ATTRIBUTE15,
P_ATTRIBUTE_CATEGORY,
L_APPLICATION_ID,
L_SEEDED_FLAG,
1 -- the first time a record is created, the object_version_number = 1
);
insert into CSC_PLAN_HEADERS_TL (
PLAN_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) select
PX_PLAN_ID,
P_NAME,
P_DESCRIPTION,
L.LANGUAGE_CODE,
userenv('LANG'),
P_CREATION_DATE,
P_LAST_UPDATE_DATE,
P_CREATED_BY,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from CSC_PLAN_HEADERS_TL T
where T.PLAN_ID = PX_PLAN_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_ROW;
/* PROCEDURE TO DO UPDATES INTO THE MLSED TABLES */
PROCEDURE Update_Row(
p_PLAN_ID IN NUMBER,
p_ORIGINAL_PLAN_ID IN NUMBER,
p_PLAN_GROUP_CODE IN VARCHAR2,
p_START_DATE_ACTIVE IN DATE,
p_END_DATE_ACTIVE IN DATE,
p_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
p_END_USER_TYPE IN VARCHAR2,
p_CUSTOMIZED_PLAN IN VARCHAR2,
p_PROFILE_CHECK_ID IN NUMBER,
p_RELATIONAL_OPERATOR IN VARCHAR2,
p_CRITERIA_VALUE_HIGH IN VARCHAR2,
p_CRITERIA_VALUE_LOW IN VARCHAR2,
p_LAST_UPDATE_DATE IN DATE,
p_LAST_UPDATED_BY IN NUMBER,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_ATTRIBUTE1 IN VARCHAR2,
p_ATTRIBUTE2 IN VARCHAR2,
p_ATTRIBUTE3 IN VARCHAR2,
p_ATTRIBUTE4 IN VARCHAR2,
p_ATTRIBUTE5 IN VARCHAR2,
p_ATTRIBUTE6 IN VARCHAR2,
p_ATTRIBUTE7 IN VARCHAR2,
p_ATTRIBUTE8 IN VARCHAR2,
p_ATTRIBUTE9 IN VARCHAR2,
p_ATTRIBUTE10 IN VARCHAR2,
p_ATTRIBUTE11 IN VARCHAR2,
p_ATTRIBUTE12 IN VARCHAR2,
p_ATTRIBUTE13 IN VARCHAR2,
p_ATTRIBUTE14 IN VARCHAR2,
p_ATTRIBUTE15 IN VARCHAR2,
p_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_NAME IN VARCHAR2,
P_APPLICATION_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER )
IS
l_seeded_flag VARCHAR2(3);
if ( p_last_updated_by IN (1, 120) ) then
l_seeded_flag := 'Y';
update CSC_PLAN_HEADERS_B set
ORIGINAL_PLAN_ID = P_ORIGINAL_PLAN_ID,
PLAN_GROUP_CODE = P_PLAN_GROUP_CODE,
START_DATE_ACTIVE = P_START_DATE_ACTIVE,
END_DATE_ACTIVE = P_END_DATE_ACTIVE,
USE_FOR_CUST_ACCOUNT = P_USE_FOR_CUST_ACCOUNT,
END_USER_TYPE = P_END_USER_TYPE,
CUSTOMIZED_PLAN = P_CUSTOMIZED_PLAN,
PROFILE_CHECK_ID = P_PROFILE_CHECK_ID,
RELATIONAL_OPERATOR = P_RELATIONAL_OPERATOR,
CRITERIA_VALUE_HIGH = P_CRITERIA_VALUE_HIGH,
CRITERIA_VALUE_LOW = P_CRITERIA_VALUE_LOW,
LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
LAST_UPDATED_BY = P_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
ATTRIBUTE1 = P_ATTRIBUTE1,
ATTRIBUTE2 = P_ATTRIBUTE2,
ATTRIBUTE3 = P_ATTRIBUTE3,
ATTRIBUTE4 = P_ATTRIBUTE4,
ATTRIBUTE5 = P_ATTRIBUTE5,
ATTRIBUTE6 = P_ATTRIBUTE6,
ATTRIBUTE7 = P_ATTRIBUTE7,
ATTRIBUTE8 = P_ATTRIBUTE8,
ATTRIBUTE9 = P_ATTRIBUTE9,
ATTRIBUTE10 = P_ATTRIBUTE10,
ATTRIBUTE11 = P_ATTRIBUTE11,
ATTRIBUTE12 = P_ATTRIBUTE12,
ATTRIBUTE13 = P_ATTRIBUTE13,
ATTRIBUTE14 = P_ATTRIBUTE14,
ATTRIBUTE15 = P_ATTRIBUTE15,
ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
-- APPLICATION_ID = L_APPLICATION_ID,
SEEDED_FLAG = L_SEEDED_FLAG,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
where PLAN_ID = P_PLAN_ID
RETURNING object_version_number INTO x_object_version_number;
update CSC_PLAN_HEADERS_TL set
NAME = P_NAME,
DESCRIPTION = P_DESCRIPTION,
SOURCE_LANG = userenv('LANG'),
LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
LAST_UPDATED_BY = P_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
where PLAN_ID = P_PLAN_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
update CSC_PLAN_HEADERS_B
set end_date_active = sysdate+1
where plan_id = p_plan_id;
select
ORIGINAL_PLAN_ID, PLAN_GROUP_CODE, START_DATE_ACTIVE,
END_DATE_ACTIVE, USE_FOR_CUST_ACCOUNT, END_USER_TYPE,
CUSTOMIZED_PLAN, PROFILE_CHECK_ID, RELATIONAL_OPERATOR,
CRITERIA_VALUE_HIGH, CRITERIA_VALUE_LOW, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE_CATEGORY,
OBJECT_VERSION_NUMBER
FROM CSC_PLAN_HEADERS_VL
WHERE PLAN_ID = P_PLAN_ID
AND OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER
FOR UPDATE OF PLAN_ID NOWAIT;
SELECT NAME, DESCRIPTION,
DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
FROM CSC_PLAN_HEADERS_TL
WHERE PLAN_ID = P_PLAN_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
FOR UPDATE OF PLAN_ID NOWAIT;
delete from CSC_PLAN_HEADERS_TL T
where not exists
(select NULL
from CSC_PLAN_HEADERS_B B
where B.PLAN_ID = T.PLAN_ID
);
update CSC_PLAN_HEADERS_TL T set (
NAME,
DESCRIPTION
) = (select
B.NAME,
B.DESCRIPTION
from CSC_PLAN_HEADERS_TL B
where B.PLAN_ID = T.PLAN_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.PLAN_ID,
T.LANGUAGE
) in (select
SUBT.PLAN_ID,
SUBT.LANGUAGE
from CSC_PLAN_HEADERS_TL SUBB, CSC_PLAN_HEADERS_TL SUBT
where SUBB.PLAN_ID = SUBT.PLAN_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME <> SUBT.NAME
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 CSC_PLAN_HEADERS_TL (
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PLAN_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
B.CREATION_DATE,
B.LAST_UPDATE_DATE,
B.CREATED_BY,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.PLAN_ID,
B.NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from CSC_PLAN_HEADERS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from CSC_PLAN_HEADERS_TL T
where T.PLAN_ID = B.PLAN_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE csc_plan_headers_tl
SET name = p_name,
description = NVL(p_description,description),
last_update_date = sysdate,
last_updated_by = fnd_load_util.owner_id(p_owner), /* R12 ATG Project: Removed the decode logic and using FND API*/
last_update_login = 0,
source_lang = userenv('LANG')
WHERE plan_id = p_plan_id
AND userenv('LANG') IN (language, source_lang) ;
p_LAST_UPDATE_DATE IN DATE,
p_LAST_UPDATED_BY IN NUMBER,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_ATTRIBUTE1 IN VARCHAR2,
p_ATTRIBUTE2 IN VARCHAR2,
p_ATTRIBUTE3 IN VARCHAR2,
p_ATTRIBUTE4 IN VARCHAR2,
p_ATTRIBUTE5 IN VARCHAR2,
p_ATTRIBUTE6 IN VARCHAR2,
p_ATTRIBUTE7 IN VARCHAR2,
p_ATTRIBUTE8 IN VARCHAR2,
p_ATTRIBUTE9 IN VARCHAR2,
p_ATTRIBUTE10 IN VARCHAR2,
p_ATTRIBUTE11 IN VARCHAR2,
p_ATTRIBUTE12 IN VARCHAR2,
p_ATTRIBUTE13 IN VARCHAR2,
p_ATTRIBUTE14 IN VARCHAR2,
p_ATTRIBUTE15 IN VARCHAR2,
p_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_NAME IN VARCHAR2,
X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
P_APPLICATION_ID IN NUMBER,
P_OWNER IN VARCHAR2)
IS
l_user_id NUMBER := 0;
update_row(
p_PLAN_ID => p_plan_id,
p_ORIGINAL_PLAN_ID => p_original_plan_id,
p_PLAN_GROUP_CODE => p_plan_group_code,
p_START_DATE_ACTIVE => to_date(p_start_date_active,'YYYY/MM/DD' ),
p_END_DATE_ACTIVE => to_date(p_end_date_active, 'YYYY/MM/DD'),
p_USE_FOR_CUST_ACCOUNT => p_use_for_cust_account,
p_END_USER_TYPE => p_end_user_type,
p_CUSTOMIZED_PLAN => p_customized_plan,
p_PROFILE_CHECK_ID => p_profile_check_id,
p_RELATIONAL_OPERATOR => p_relational_operator,
p_CRITERIA_VALUE_HIGH => p_criteria_value_high,
p_CRITERIA_VALUE_LOW => p_criteria_value_low,
p_LAST_UPDATE_DATE => p_last_update_date,
p_LAST_UPDATED_BY => p_last_updated_by,
p_LAST_UPDATE_LOGIN => 0,
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,
p_ATTRIBUTE_CATEGORY => p_attribute_category,
P_DESCRIPTION => p_description,
P_NAME => p_name,
P_APPLICATION_ID => p_application_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number );
insert_row(
px_PLAN_ID => l_plan_id,
p_ORIGINAL_PLAN_ID => p_original_plan_id,
p_PLAN_GROUP_CODE => p_plan_group_code,
p_START_DATE_ACTIVE => to_date(p_start_date_active, 'YYYY/MM/DD'),
p_END_DATE_ACTIVE => to_date(p_end_date_active, 'YYYY/MM/DD'),
p_USE_FOR_CUST_ACCOUNT => p_use_for_cust_account,
p_END_USER_TYPE => p_end_user_type,
p_CUSTOMIZED_PLAN => p_customized_plan,
p_PROFILE_CHECK_ID => p_profile_check_id,
p_RELATIONAL_OPERATOR => p_relational_operator,
p_CRITERIA_VALUE_HIGH => p_criteria_value_high,
p_CRITERIA_VALUE_LOW => p_criteria_value_low,
p_CREATION_DATE => p_last_update_date,
p_LAST_UPDATE_DATE => p_last_update_date,
p_CREATED_BY => p_last_updated_by,
p_LAST_UPDATED_BY => p_last_updated_by,
p_LAST_UPDATE_LOGIN => 0,
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,
p_ATTRIBUTE_CATEGORY => p_attribute_category,
P_DESCRIPTION => p_description,
P_NAME => p_name,
P_APPLICATION_ID => p_application_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number );