[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_flag VARCHAR2)
IS
------------------------------------------------------------------
--Created by : ridas, Oracle India
--Date created: 02-NOV-2004
--
--Purpose:
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
--Cursor to fetch organizational unit
CURSOR c_org_unit(cp_org_unit igf_ap_fa_ant_data.org_unit_cd%TYPE)
IS
SELECT 'x'
FROM igs_or_inst_org_base_v
WHERE party_number = cp_org_unit
AND inst_org_ind = 'O'
AND SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE);
SELECT 'x'
FROM igs_ps_type_v
WHERE course_type = cp_prog_type
AND NVL(closed_ind,'N')<>'Y';
SELECT 'x'
FROM igs_ad_location
WHERE location_cd = cp_prog_loc
AND NVL(closed_ind,'N')<>'Y';
SELECT 'x'
FROM igs_ps_ver
WHERE course_cd = cp_prog_cd
AND SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE)
AND rownum = 1;
SELECT 'x'
FROM igs_pr_class_std_v
WHERE class_standing = cp_class_stnd
AND NVL(closed_ind,'N')<>'Y';
SELECT 'x'
FROM igs_lookups_view
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code
AND enabled_flag = 'Y';
SELECT 'x'
FROM igs_en_atd_type
WHERE attendance_type = cp_atten_type
AND NVL(closed_ind,'N')<>'Y';
SELECT 'x'
FROM igs_en_atd_mode
WHERE attendance_mode = cp_atten_mode
AND NVL(closed_ind,'N')<>'Y';
SELECT ant.rowid row_id,
ant.*
FROM igf_ap_fa_ant_data ant
WHERE base_id = cp_base_id
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','calling igf_ap_fa_ant_data_pkg.update_row');
igf_ap_fa_ant_data_pkg.update_row (
x_mode => 'R',
x_rowid => l_ant_data.row_id,
x_base_id => p_base_id,
x_ld_cal_type => l_ant_data.ld_cal_type,
x_ld_sequence_number => l_ant_data.ld_sequence_number,
x_org_unit_cd => p_interface.org_unit_cd ,
x_program_type => p_interface.program_type,
x_program_location_cd => p_interface.program_location_cd,
x_program_cd => p_interface.program_cd,
x_class_standing => p_interface.class_standing,
x_residency_status_code => p_interface.residency_status_code,
x_housing_status_code => p_interface.housing_status_code,
x_attendance_type => p_interface.attendance_type,
x_attendance_mode => p_interface.attendance_mode,
x_months_enrolled_num => p_interface.months_enrolled_num,
x_credit_points_num => p_interface.credit_points_num
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','calling igf_ap_fa_ant_data_pkg.insert_row');
igf_ap_fa_ant_data_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
x_base_id => p_base_id,
x_ld_cal_type => p_ld_cal_type,
x_ld_sequence_number => p_ld_sequence_number,
x_org_unit_cd => p_interface.org_unit_cd ,
x_program_type => p_interface.program_type,
x_program_location_cd => p_interface.program_location_cd,
x_program_cd => p_interface.program_cd,
x_class_standing => p_interface.class_standing,
x_residency_status_code => p_interface.residency_status_code,
x_housing_status_code => p_interface.housing_status_code,
x_attendance_type => p_interface.attendance_type,
x_attendance_mode => p_interface.attendance_mode,
x_months_enrolled_num => p_interface.months_enrolled_num,
x_credit_points_num => p_interface.credit_points_num
);
IF p_delete_flag = 'Y' THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_anticipated_data.process_anti_data.debug','deleting from igf_aw_anticpt_ints');
DELETE FROM igf_aw_anticpt_ints
WHERE batch_num = p_interface.batch_num
AND ci_alternate_code = p_interface.ci_alternate_code
AND ld_alternate_code = p_interface.ld_alternate_code
AND person_number = p_interface.person_number;
UPDATE igf_aw_anticpt_ints
SET IMPORT_STATUS_TYPE = 'I'
WHERE batch_num = p_interface.batch_num
AND ci_alternate_code = p_interface.ci_alternate_code
AND ld_alternate_code = p_interface.ld_alternate_code
AND person_number = p_interface.person_number;
UPDATE igf_aw_anticpt_ints
SET IMPORT_STATUS_TYPE = 'E'
WHERE batch_num = p_interface.batch_num
AND ci_alternate_code = p_interface.ci_alternate_code
AND ld_alternate_code = p_interface.ld_alternate_code
AND person_number = p_interface.person_number;
SELECT intf.*
FROM igf_aw_anticpt_ints intf
WHERE intf.batch_num = cp_batch_id
AND intf.ci_alternate_code = cp_alternate_code
AND intf.import_status_type = 'R'
AND intf.import_record_type in ('I','U')
ORDER BY intf.person_number;
SELECT 'x' val
FROM igf_aw_anticpt_ints intf
WHERE intf.batch_num = cp_batch_id
AND intf.ci_alternate_code = cp_alternate_code
AND intf.import_status_type = 'R'
AND intf.import_record_type in ('I','U')
AND rownum = 1;
SELECT ant.base_id
FROM igf_ap_fa_ant_data ant
WHERE base_id = cp_base_id
AND ld_cal_type = cp_ld_cal_type
AND ld_sequence_number = cp_ld_sequence_number;
SELECT batch_num
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num
AND batch_type= cp_batch_type
AND rownum = 1;
SELECT ld_cal_type, ld_sequence_number
FROM igf_aw_awd_ld_cal_v cal
WHERE cal.aw_cal_type = p_cal_type
AND cal.aw_sequence_number = p_sequence_number
AND cal.ld_alternate_code = p_alternate_code;
fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','DELETE_FLAG'),40) ||': '||igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_del_ind) );
UPDATE igf_aw_anticpt_ints
SET IMPORT_STATUS_TYPE = 'E'
WHERE batch_num = l_interface.batch_num
AND ci_alternate_code = l_interface.ci_alternate_code
AND ld_alternate_code = l_interface.ld_alternate_code
AND person_number = l_interface.person_number;
UPDATE igf_aw_anticpt_ints
SET IMPORT_STATUS_TYPE = 'E'
WHERE batch_num = l_interface.batch_num
AND ci_alternate_code = l_interface.ci_alternate_code
AND ld_alternate_code = l_interface.ld_alternate_code
AND person_number = l_interface.person_number;
UPDATE igf_aw_anticpt_ints
SET IMPORT_STATUS_TYPE = 'E'
WHERE batch_num = l_interface.batch_num
AND ci_alternate_code = l_interface.ci_alternate_code
AND ld_alternate_code = l_interface.ld_alternate_code
AND person_number = l_interface.person_number;