[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
added as part of Prevent Dropping Core Units. Enh Bug# 3052432
vkarthik 12-DEC-2003 Added process_en_spat to proces term records from the interface table
and necessary call
ptandon 16-DEC-2003 Modified procedure process_en_sua to log warning messages in case of
successful unit attempts also so as to show warnings if term record
creation fails. Term Based Fee Calc build. Bug# 2829263.
smaddali 19-OCT-2004 Modified procedure log_err_messages for performance issues
jtmathew 12-JAN-2005 Modified procedures process_he_spa and process_he_susa
to add additional fields to lr_he_spa_rec and lr_he_susa_rec
for changes described by HEFD350.
jhanda 15-July-2005 Changed for build 4327991
ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) in LEGACY_BATCH_PROCESS
procedure as a part of bug#4958173
smaddali 10-apr-06 Modified for bug#5091858 BUILD EN324
******************************************************************************/
-- cursor for find the lookup meaning for a given lookup code and type
CURSOR gc_lkups (cp_lookup_code igs_lookup_values.lookup_code%TYPE,
cp_lookup_type igs_lookup_values.lookup_type%TYPE) IS
SELECT meaning
FROM igs_lookup_values
WHERE lookup_code = cp_lookup_code
AND lookup_type = cp_lookup_type;
FUNCTION get_last_updated_by RETURN NUMBER AS
BEGIN
IF FND_GLOBAL.USER_ID IS NULL THEN
RETURN -1;
END get_last_updated_by ;
FUNCTION get_last_update_date RETURN DATE AS
BEGIN
RETURN SYSDATE;
END get_last_update_date ;
FUNCTION get_last_update_login RETURN NUMBER AS
BEGIN
IF FND_GLOBAL.LOGIN_ID IS NULL THEN
RETURN -1;
END get_last_update_login;
FUNCTION get_program_update_date RETURN DATE AS
BEGIN
IF (FND_GLOBAL.CONC_REQUEST_ID = -1) THEN
RETURN NULL;
END get_program_update_date;
procedure delete_err_messages(
p_int_table_code IN VARCHAR2,
p_int_table_id IN NUMBER
) AS
BEGIN
/*
This procedure deletes the error message
records for the particualr interface table corresponding
to the p_int_table_code and p_int_table_id
*/
DELETE FROM igs_en_lgcy_err_int
WHERE int_table_id = p_int_table_id
AND int_table_code = p_int_table_code;
PROCEDURE insert_log_err_msgs(
p_int_table_code IN VARCHAR2,
P_int_table_id IN NUMBER,
p_err_msg_id IN NUMBER,
p_msg_ret_status IN VARCHAR2,
p_msg_text IN VARCHAR2,
p_msg_number IN NUMBER
) AS
l_created_by NUMBER;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
l_created_by := get_last_updated_by;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
INSERT INTO igs_en_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES
(
p_err_msg_id,
p_int_table_code,
p_int_table_id,
p_msg_number,
p_msg_text,
l_created_by,
l_creation_date,
l_last_updated_by ,
l_last_update_date ,
l_last_update_login ,
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date
);
END insert_log_err_msgs;
|| imported then all the associated error messages would also be deleted.
|| But the logic for that is coded in the individual procedure
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| kkillams 20-12-2002 Added new validation inside the FOR LOOP
|| which sets the l_err_msg_id variable with the
|| next "legacy error" sequence number, w.r.t. bug :2717455
|| smaddali 19-oct-04 Modified this procedure for bug#3930425, performance issue with cursor c_msg_text_num
------------------------------------------------------------------------------*/
--smaddali: bug#3930425, modified this cursor for performance, to be based on appl_id and message_name as this is the PK
CURSOR c_msg_text_num (cp_appl_name FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE ,
cp_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE) IS
SELECT message_number
FROM FND_NEW_MESSAGES msg ,fnd_application apl
WHERE apl.application_id = msg.application_id
AND apl.application_short_name = cp_appl_name
AND message_name = cp_message_name;
SELECT igs_en_lgcy_err_int_s.NEXTVAL INTO l_err_msg_id FROM DUAL;
insert_log_err_msgs(
p_int_table_code => p_int_table_code,
p_int_table_id => p_int_table_id,
p_err_msg_id => l_err_msg_id,
p_msg_ret_status => p_msg_ret_status,
p_msg_text => l_msg_text,
p_msg_number => l_msg_number
);
SELECT igs_en_lgcy_err_int_s.NEXTVAL INTO l_err_msg_id FROM DUAL;
FND_MSG_PUB.DELETE_MSG(l_msg_index);
insert_log_err_msgs(
p_int_table_code => p_int_table_code,
p_int_table_id => p_int_table_id,
p_err_msg_id => l_err_msg_id,
p_msg_ret_status => p_msg_ret_status,
p_msg_text => l_msg_text,
p_msg_number => l_msg_number);
SELECT suarc.*
FROM igs_as_lgcy_suarc_int suarc
WHERE batch_id = p_batch_id AND import_status IN ('U', 'R')
ORDER BY suarc.person_number ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_en_sua,
p_int_table_id => l_as_suarefcd_rec.LEGACY_SUAR_INT_ID
);
DELETE FROM igs_as_lgcy_suarc_int
WHERE LEGACY_SUAR_INT_ID = l_as_suarefcd_rec.LEGACY_SUAR_INT_ID;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_as_lgcy_suarc_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE LEGACY_SUAR_INT_ID = l_as_suarefcd_rec.LEGACY_SUAR_INT_ID;
SELECT *
FROM igs_en_lgcy_spa_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
NVL(key_program,'N') DESC,
NVL(primary_program_type,'SECONDARY') ASC,
commencement_dt ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_en_spa,
p_int_table_id => l_en_spa_cur_rec.legacy_en_spa_int_id
);
DELETE FROM igs_en_lgcy_spa_int
WHERE legacy_en_spa_int_id = l_en_spa_cur_rec.legacy_en_spa_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_en_lgcy_spa_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_en_spa_int_id = l_en_spa_cur_rec.legacy_en_spa_int_id;
SELECT * FROM igs_en_lgy_spat_int
WHERE
batch_id = p_batch_id AND
import_status IN ('U', 'R')
ORDER BY
person_number ASC,
program_cd ASC,
legacy_en_spat_int_id ASC
FOR UPDATE NOWAIT;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages (
p_int_table_code => g_cst_en_spat,
p_int_table_id => lc_lgy_spat_int.legacy_en_spat_int_id );
DELETE FROM igs_en_lgy_spat_int
WHERE legacy_en_spat_int_id = lc_lgy_spat_int.legacy_en_spat_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_en_lgy_spat_int
SET import_status =
DECODE (l_return_status, 'S', 'I',
'U', 'E',
'W', 'W',
'E'),
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE
legacy_en_spat_int_id = lc_lgy_spat_int.legacy_en_spat_int_id;
SELECT *
FROM igs_en_lgy_susa_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
program_cd ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
lr_en_susa_rec.selection_dt := l_en_susa_cur_rec.selection_dt ;
delete_err_messages(
p_int_table_code => g_cst_en_susa,
p_int_table_id => l_en_susa_cur_rec.legacy_en_susa_int_id
);
DELETE FROM igs_en_lgy_susa_int
WHERE legacy_en_susa_int_id = l_en_susa_cur_rec.legacy_en_susa_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_en_lgy_susa_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_en_susa_int_id = l_en_susa_cur_rec.legacy_en_susa_int_id;
SELECT *
FROM igs_en_lgcy_spi_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
program_cd ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_en_spi,
p_int_table_id => l_en_spi_cur_rec.legacy_en_spi_int_id
);
DELETE FROM igs_en_lgcy_spi_int
WHERE legacy_en_spi_int_id = l_en_spi_cur_rec.legacy_en_spi_int_id ;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_en_lgcy_spi_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_en_spi_int_id = l_en_spi_cur_rec.legacy_en_spi_int_id ;
SELECT *
FROM igs_en_lgy_spaa_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
program_cd ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_en_spaa,
p_int_table_id => l_en_spaa_cur_rec.legacy_en_spaa_int_id
);
DELETE FROM igs_en_lgy_spaa_int
WHERE legacy_en_spaa_int_id = l_en_spaa_cur_rec.legacy_en_spaa_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_en_lgy_spaa_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_en_spaa_int_id = l_en_spaa_cur_rec.legacy_en_spaa_int_id;
SELECT *
FROM igs_re_lgcy_spr_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY ca_person_number ASC,
START_DT ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_re_sprvsr,
p_int_table_id => l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id
);
DELETE FROM igs_re_lgcy_spr_int
WHERE legacy_re_sprvsr_int_id = l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_re_lgcy_spr_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_re_sprvsr_int_id = l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id;
SELECT *
FROM igs_re_lgcy_the_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
program_cd ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_re_the,
p_int_table_id => l_re_the_cur_rec.legacy_re_the_int_id
);
DELETE FROM igs_re_lgcy_the_int
WHERE legacy_re_the_int_id = l_re_the_cur_rec.legacy_re_the_int_id ;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_re_lgcy_the_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_re_the_int_id = l_re_the_cur_rec.legacy_re_the_int_id ;
SELECT suai.*
FROM igs_en_lgcy_sua_int suai,
igs_ca_inst ci
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
AND suai.teach_calendar_alternate_code = ci.alternate_code(+)
ORDER BY suai.person_number ASC,
suai.transfer_dt DESC,
ci.start_dt ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_en_sua,
p_int_table_id => l_en_sua_cur_rec.legacy_en_sua_int_id
);
DELETE FROM igs_en_lgcy_sua_int
WHERE legacy_en_sua_int_id = l_en_sua_cur_rec.legacy_en_sua_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_en_lgcy_sua_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_en_sua_int_id = l_en_sua_cur_rec.legacy_en_sua_int_id;
SELECT *
FROM igs_he_lgcy_spa_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
program_cd ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_he_spa,
p_int_table_id => l_he_spa_cur_rec.legacy_hesa_spa_int_id
);
DELETE FROM igs_he_lgcy_spa_int
WHERE legacy_hesa_spa_int_id = l_he_spa_cur_rec.legacy_hesa_spa_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_he_lgcy_spa_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_hesa_spa_int_id = l_he_spa_cur_rec.legacy_hesa_spa_int_id;
SELECT *
FROM igs_he_lgy_susa_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
ORDER BY person_number ASC,
program_cd ASC
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_he_susa,
p_int_table_id => l_he_susa_cur_rec.legacy_hesa_susa_int_id
);
DELETE FROM igs_he_lgy_susa_int
WHERE legacy_hesa_susa_int_id = l_he_susa_cur_rec.legacy_hesa_susa_int_id ;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_he_lgy_susa_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_hesa_susa_int_id = l_he_susa_cur_rec.legacy_hesa_susa_int_id ;
SELECT *
FROM igs_av_lgcy_lvl_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_av_avstdl,
p_int_table_id => l_av_avstdl_cur_rec.legacy_lvl_int_id
);
DELETE FROM igs_av_lgcy_lvl_int
WHERE legacy_lvl_int_id = l_av_avstdl_cur_rec.legacy_lvl_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_av_lgcy_lvl_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_lvl_int_id = l_av_avstdl_cur_rec.legacy_lvl_int_id;
SELECT *
FROM igs_av_lgcy_unt_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_av_untstd,
p_int_table_id => l_av_untstd_cur_rec.legacy_unt_int_id
);
DELETE FROM igs_av_lgcy_unt_int
WHERE legacy_unt_int_id = l_av_untstd_cur_rec.legacy_unt_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_av_lgcy_unt_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_unt_int_id = l_av_untstd_cur_rec.legacy_unt_int_id;
SELECT *
FROM igs_as_lgcy_suo_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_as_uotcm,
p_int_table_id => l_as_uotcm_cur_rec.legacy_suao_int_id
);
DELETE FROM igs_as_lgcy_suo_int
WHERE legacy_suao_int_id = l_as_uotcm_cur_rec.legacy_suao_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_as_lgcy_suo_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_suao_int_id = l_as_uotcm_cur_rec.legacy_suao_int_id;
SELECT *
FROM igs_pr_lgcy_spo_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_pr_out,
p_int_table_id => l_pr_out_cur_rec.legacy_pr_spo_int_id
);
DELETE FROM igs_pr_lgcy_spo_int
WHERE legacy_pr_spo_int_id = l_pr_out_cur_rec.legacy_pr_spo_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_pr_lgcy_spo_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_pr_spo_int_id = l_pr_out_cur_rec.legacy_pr_spo_int_id;
SELECT *
FROM igs_pr_lgy_clsr_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_pr_cr,
p_int_table_id => l_pr_cr_cur_rec.legacy_cls_rank_int_id
);
DELETE FROM igs_pr_lgy_clsr_int
WHERE legacy_cls_rank_int_id = l_pr_cr_cur_rec.legacy_cls_rank_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_pr_lgy_clsr_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_cls_rank_int_id = l_pr_cr_cur_rec.legacy_cls_rank_int_id;
SELECT *
FROM igs_gr_lgcy_grd_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_gr_grd,
p_int_table_id => l_gr_grd_cur_rec.legacy_gr_int_id
);
DELETE FROM igs_gr_lgcy_grd_int
WHERE legacy_gr_int_id = l_gr_grd_cur_rec.legacy_gr_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_gr_lgcy_grd_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_gr_int_id = l_gr_grd_cur_rec.legacy_gr_int_id;
SELECT *
FROM igs_as_lgcy_stc_int
WHERE batch_id = p_batch_id
AND import_status IN ('U','R')
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_as_trncmt,
p_int_table_id => l_as_trncmt_cur_rec.legacy_cmts_int_id
);
DELETE FROM igs_as_lgcy_stc_int
WHERE legacy_cmts_int_id = l_as_trncmt_cur_rec.legacy_cmts_int_id;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE igs_as_lgcy_stc_int
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE legacy_cmts_int_id = l_as_trncmt_cur_rec.legacy_cmts_int_id;
SELECT * from IGS_EN_SPI_RCOND_INTS
WHERE import_status IN ('U','R')
AND batch_id = p_batch_id
FOR UPDATE NOWAIT;
l_last_update_date DATE ;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_program_update_date DATE;
delete_err_messages(
p_int_table_code => g_cst_en_spi_rcond,
p_int_table_id => l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID
);
DELETE FROM IGS_EN_SPI_RCOND_INTS
WHERE LGCY_EN_SPI_RCONS_INT_ID = l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID ;
l_last_updated_by := get_last_updated_by;
l_last_update_date := get_last_update_date;
l_last_update_login := get_last_update_login;
l_program_update_date := get_program_update_date;
UPDATE IGS_EN_SPI_RCOND_INTS
SET import_status = DECODE(l_return_status,
'S','I',
'U','E',
'W','W',
'E'),
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE LGCY_EN_SPI_RCONS_INT_ID = l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID ;
p_delete_flag IN VARCHAR2
) AS
CURSOR c_batch_desc IS
SELECT description
FROM igs_en_lgcy_bat_int
WHERE batch_id = p_batch_id;
IF p_delete_flag IS NOT NULL AND p_delete_flag = 'Y' THEN
l_deletion_flag := TRUE;