[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igs_ad_recruit_pi_v
WHERE person_id = p_person_id AND
TRUNC(calculation_date) = TRUNC(p_calculation_date) AND
probability_type_code_id = p_prblty_type_code_id;
SELECT 'X'
FROM igs_ad_code_classes iacc
WHERE iacc.code_id = prblty_val_rec.prblty_type_code_id AND
iacc.class = 'PROB_TYPE';
SELECT 'X'
FROM igs_ad_code_classes iacc
WHERE iacc.code_id = prblty_val_rec.prblty_source_code_id AND
iacc.class = 'PROB_SOURCE';
--Cursor to select all the records in pending status in the batch_id as given by the user
CURSOR prblty_val_cur(c_prblty_val_batch_id igs_ad_recrt_pi_int.prblty_val_batch_id%TYPE) IS
SELECT arpi.*
FROM igs_ad_recrt_pi_int arpi
WHERE arpi.prblty_val_batch_id = c_prblty_val_batch_id AND
arpi.status = '2' ; -- '2' pending
SELECT pe_person_id
FROM igs_pe_person_id_type_v
WHERE api_person_id = c_api_person_id AND
person_id_type = c_person_id_type ;
SELECT 'X'
FROM HZ_PARTIES
WHERE party_id = c_person_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = 'E007', -- 'E007' Invalid person
status = '3' , -- '3' Error
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = 'E006', -- 'E006' Insufficient Information of a person
status = '3' , -- '3' Error
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
SELECT *
FROM igs_ad_recruit_pi_v
WHERE person_id = c_person_id AND
calculation_date = c_calculation_date AND
probability_type_code_id = c_prblty_type_code_id;
igs_ad_recruit_pi_pkg.update_row (
x_mode => 'R',
x_rowid => null_hdlg_adm_recrt_pi_rec.row_id,
x_probability_index_id => null_hdlg_adm_recrt_pi_rec.probability_index_id,
x_person_id => null_hdlg_adm_recrt_pi_rec.person_id,
x_probability_type_code_id => null_hdlg_adm_recrt_pi_rec.probability_type_code_id,
x_calculation_date => null_hdlg_adm_recrt_pi_rec.calculation_date,
x_probability_value => NVL(prblty_val_rec.probability_value,null_hdlg_adm_recrt_pi_rec.probability_value),
x_probability_source_code_id => NVL(prblty_val_rec.prblty_source_code_id,null_hdlg_adm_recrt_pi_rec.probability_source_code_id)
);
UPDATE igs_ad_recrt_pi_int
SET error_code = NULL,
status = '1' , --'1' Complete
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = 'E005', -- 'E005' Update failed
status = '3' ,
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = l_error_code,
status = '3' ,
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = 'E003', -- 'E003' Duplicate record found
status = '3' ,
match_ind = '23' -- '23' Match to be reviewed, but there was no discrepancy and so retaining the existing
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
igs_ad_recruit_pi_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_probability_index_id => l_probability_index_id,
x_person_id => l_person_id,
x_probability_type_code_id => prblty_val_rec.prblty_type_code_id,
x_calculation_date => prblty_val_rec.calculation_date,
x_probability_value => prblty_val_rec.probability_value,
x_probability_source_code_id => prblty_val_rec.prblty_source_code_id
);
UPDATE igs_ad_recrt_pi_int
SET error_code = NULL,
status = '1' ,
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = 'E004', -- 'E004' Insert failed
status = '3' ,
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
UPDATE igs_ad_recrt_pi_int
SET error_code = l_error_code,
status = '3' ,
match_ind = NULL
WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
DELETE
FROM igs_ad_recrt_pi_int
WHERE status = '1' AND
prblty_val_batch_id = p_prblty_val_batch_id;