[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ID,
NAME,
VERSION,
OBJECT_VERSION_NUMBER,
CODE,
SFWT_FLAG,
STREAM_TYPE_SCOPE,
DESCRIPTION,
START_DATE,
END_DATE,
BILLABLE_YN,
TAXABLE_DEFAULT_YN,
CUSTOMIZATION_LEVEL,
STREAM_TYPE_CLASS,
ACCRUAL_YN,
ALLOCATION_FACTOR,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- Added by RGOOTY for ER 3935682: Start
STREAM_TYPE_PURPOSE,
CONTINGENCY,
SHORT_DESCRIPTION
-- Added by RGOOTY for ER 3935682: End
FROM OKL_STRM_TYPE_V
WHERE OKL_STRM_TYPE_V.id = p_id;
l_styv_rec.LAST_UPDATED_BY,
l_styv_rec.LAST_UPDATE_DATE,
l_styv_rec.LAST_UPDATE_LOGIN,
-- Added by RGOOTY for ER 3935682: Start
l_styv_rec.stream_type_purpose,
l_styv_rec.contingency,
l_styv_rec.short_description;
-- Scenario 2: The Changed Field-Values include that needs Validation and Update
-- 1) End_Date is Changed
ELSIF (p_upd_styv_rec.start_date = G_MISS_DATE AND
(p_upd_styv_rec.end_date <> G_MISS_DATE OR
-- IS NULL Condition has been added in case end_date was updated to NULL
p_upd_styv_rec.end_date IS NULL ) AND
p_upd_styv_rec.stream_type_scope = G_MISS_CHAR AND
p_upd_styv_rec.taxable_default_yn = G_MISS_CHAR AND
p_upd_styv_rec.stream_type_class = G_MISS_CHAR AND
p_upd_styv_rec.accrual_yn = G_MISS_CHAR AND
p_upd_styv_rec.capitalize_yn = G_MISS_CHAR AND
p_upd_styv_rec.periodic_yn = G_MISS_CHAR AND
p_upd_styv_rec.fundable_yn = G_MISS_CHAR AND
p_upd_styv_rec.allocation_factor = G_MISS_CHAR) OR
-- 2) Critical Attributes are Changed but does not mandate new version
-- as Start_Date is in Future and Not Changied
(p_upd_styv_rec.start_date = G_MISS_DATE AND
p_db_styv_rec.start_date >= p_date AND
(p_upd_styv_rec.stream_type_scope <> G_MISS_CHAR OR
p_upd_styv_rec.taxable_default_yn <> G_MISS_CHAR OR
p_upd_styv_rec.stream_type_class <> G_MISS_CHAR OR
-- mvasudev, 02/25/2002
-- IS NULL Condition has been added in case these attributes were updated to NULL
(p_upd_styv_rec.capitalize_yn <> G_MISS_CHAR OR p_upd_styv_rec.capitalize_yn IS NULL ) OR
(p_upd_styv_rec.periodic_yn <> G_MISS_CHAR OR p_upd_styv_rec.periodic_yn IS NULL ) OR
(p_upd_styv_rec.fundable_yn <> G_MISS_CHAR OR p_upd_styv_rec.fundable_yn IS NULL ) OR
(p_upd_styv_rec.allocation_factor <> G_MISS_CHAR OR p_upd_styv_rec.allocation_factor IS NULL ) OR
-- end,mvasudev, 02/25/2002
p_upd_styv_rec.accrual_yn <> G_MISS_CHAR)) OR
-- 3) Start_Date is Shifted , but in Future
(p_upd_styv_rec.start_date <> G_MISS_DATE AND
p_db_styv_rec.start_date > p_date)
-- Commented out to disregard multiple versions in Future , 04/11/2002
--AND p_upd_styv_rec.start_date < p_db_styv_rec.start_date)
THEN
l_action := '2';
PROCEDURE check_updates (
p_styv_rec IN styv_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
/* Commented till final decision made regarding Versioning
-- 04/11/2002
-- Cursor to fetch streams that would be impacted by stream-type update
CURSOR l_okl_stm_csr(p_sty_id NUMBER,p_sysdate DATE)
IS
SELECT '1' FROM dual
WHERE EXISTS
(SELECT '1'
FROM OKL_STRM_TYPE_TL STYL,
OKL_STREAMS STMB,
OKL_STRM_ELEMENTS SELB
WHERE STMB.STY_ID = STYL.ID
AND STMB.SAY_CODE = 'CURR'
AND SELB.STM_ID = STMB.ID
AND SELB.STREAM_ELEMENT_DATE > p_sysdate
);
SELECT '1' FROM dual
WHERE EXISTS
(SELECT '1'
FROM OKL_AE_TEMPLATES_V
WHERE sty_id = p_sty_id
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, p_sysdate)
);
END check_updates;
-- auto_update code with name
l_styv_rec.CODE := l_styv_rec.NAME;
/* public api to insert streamtype */
okl_strm_type_pub.insert_strm_type(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => l_styv_rec,
x_styv_rec => x_styv_rec);
PROCEDURE update_stream_type(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_styv_rec IN styv_rec_type,
x_styv_rec OUT NOCOPY styv_rec_type
) IS
CURSOR l_okl_styv_pk_csr (p_id IN NUMBER) IS
SELECT
START_DATE,
END_DATE
FROM OKL_STRM_TYPE_B
WHERE OKL_STRM_TYPE_B.id = p_id;
l_api_name CONSTANT VARCHAR2(30) := 'update_stream_type';
-- auto_update code with name
l_styv_rec.CODE := l_styv_rec.NAME;
-- public api to update_stream_type
OKL_STRM_TYPE_PUB.update_strm_type(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => l_styv_rec,
x_styv_rec => x_styv_rec);
/* public api to update_stream_type *
okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => l_upd_styv_rec,
x_styv_rec => x_styv_rec);
/* Scenario 2: The Changed Field-Values include that needs Validation and Update *
ELSIF l_action = '2' THEN
check_updates( p_styv_rec => l_styv_rec,
x_return_status => l_return_status,
x_msg_data => x_msg_data);
/* public api to update formulae *
okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => l_upd_styv_rec,
x_styv_rec => x_styv_rec);
-- DO NOT Update Old-record if new Start_Date is after Old End_Date
IF l_upd_styv_rec.start_date <> G_MISS_DATE
AND l_db_styv_rec.end_date IS NOT NULL
AND l_upd_styv_rec.start_date > l_db_styv_rec.end_date
THEN
-- determine_action() updated on 04/11/2002 never yields this scenario
NULL;
/* call verify changes to update the database *
IF l_oldversion_enddate > l_db_styv_rec.end_date THEN
check_updates( p_styv_rec => l_styv_rec,
x_return_status => l_return_status,
x_msg_data => x_msg_data);
/* public api to update stream types *
okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => l_styv_rec,
x_styv_rec => x_styv_rec);
-- auto_update code with name
l_styv_rec.CODE := l_styv_rec.NAME;
-- The earliest START_DATE, when Update, can be TOMORROW only
IF l_upd_styv_rec.start_date = G_MISS_DATE THEN
--l_styv_rec.start_date := l_sysdate ;
/* call verify changes to update the database *
IF l_styv_rec.end_date > l_db_styv_rec.end_date THEN
check_updates( p_styv_rec => l_styv_rec,
x_return_status => l_return_status,
x_msg_data => x_msg_data);
/* public api to insert stream type *
okl_strm_type_pub.insert_strm_type(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => l_styv_rec,
x_styv_rec => x_styv_rec);
END update_stream_type;
PROCEDURE update_stream_type(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_styv_tbl IN styv_tbl_type,
x_styv_tbl OUT NOCOPY styv_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_stream_type_tbl';
update_stream_type(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_styv_rec => p_styv_tbl(rec_num),
x_styv_rec => x_styv_tbl(rec_num) );
END update_stream_type;