[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT todo.rowid row_id,
todo.*
FROM igs_pe_std_todo todo
WHERE todo.person_id = c_person_id
AND todo.s_student_todo_type = c_s_student_todo_type
AND todo.sequence_number = c_sequence_number
AND todo.logical_delete_dt IS NULL
AND NOT EXISTS
(SELECT tref.person_id,
tref.s_student_todo_type,
tref.sequence_number
FROM igs_pe_std_todo_ref tref
WHERE tref.person_id = todo.person_id
AND tref.s_student_todo_type = todo.s_student_todo_type
AND tref.sequence_number = todo.sequence_number
AND tref.s_student_todo_type = 'IGF_COA_COMP'
AND tref.logical_delete_dt IS NULL
GROUP BY tref.person_id, tref.s_student_todo_type, tref.sequence_number
);
SELECT distinct todo.person_id,
todo.s_student_todo_type,
todo.sequence_number
FROM igs_pe_std_todo todo,
igs_pe_std_todo_ref tref
WHERE todo.person_id = c_person_id
AND tref.person_id = todo.person_id
AND tref.s_student_todo_type = todo.s_student_todo_type
AND tref.sequence_number = todo.sequence_number
AND tref.cal_type = c_ci_cal_type
AND tref.ci_sequence_number = c_ci_sequence_number
AND todo.s_student_todo_type = 'IGF_COA_COMP'
AND todo.logical_delete_dt IS NULL
AND tref.logical_delete_dt IS NULL;
SELECT person_id
FROM igf_ap_fa_base_rec_all
WHERE base_id = c_base_id
AND ci_cal_type = c_ci_cal_type
AND ci_sequence_number = c_ci_sequence_number;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.update_to_do_items.debug','Updating table igs_pe_std_todo for person id: '||l_person_dtls.person_id);
igs_pe_std_todo_ref_pkg.update_row(
x_rowid => l_person_ref.row_id,
x_person_id => l_person_ref.person_id,
x_s_student_todo_type => l_person_ref.s_student_todo_type,
x_sequence_number => l_person_ref.sequence_number,
x_reference_number => l_person_ref.reference_number,
x_cal_type => l_person_ref.cal_type,
x_ci_sequence_number => l_person_ref.ci_sequence_number,
x_course_cd => l_person_ref.course_cd,
x_unit_cd => l_person_ref.unit_cd,
x_other_reference => l_person_ref.other_reference,
x_logical_delete_dt => sysdate,
x_mode => 'R',
x_uoo_id => l_person_ref.uoo_id
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.update_to_do_items.debug','c_person_todo%FOUND');
igs_pe_std_todo_pkg.update_row(
x_rowid => l_person_todo.row_id,
x_person_id => l_person_todo.person_id,
x_s_student_todo_type => l_person_todo.s_student_todo_type,
x_sequence_number => l_person_todo.sequence_number,
x_todo_dt => l_person_todo.todo_dt,
x_logical_delete_dt => sysdate,
x_mode => 'R'
);
fnd_message.set_token('NAME','igf_aw_coa_update.update_to_do_items :' || SQLERRM);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_update.update_to_do_items.exception','sql error:'||SQLERRM);
END update_to_do_items;
SELECT rate.*
FROM igf_aw_coa_rate_det rate
WHERE ci_cal_type = c_ci_cal_type
AND ci_sequence_number = c_ci_sequence_number
AND item_code = c_item_code
ORDER BY rate_order_num ASC;
SELECT group_id
FROM igs_pe_persid_group_all
WHERE group_cd = c_grp_code;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','OPEN c_rate_order');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','inside if condition');
OPEN c_chk_per_exist FOR 'SELECT 1
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = :base_id
AND fabase.person_id in ( '||lv_sql_stmt||') ' USING p_base_id,l_grp_id.group_id;
OPEN c_chk_per_exist FOR 'SELECT 1
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = :base_id
AND fabase.person_id in ( '||lv_sql_stmt||') ' USING p_base_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','c_chk_per_exist%NOTFOUND');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','person found in the person group id');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','l_grp_id.group_id IS NULL');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.is_attrib_matching.debug','l_rate_order.pid_group_cd IS NULL');
SELECT NVL(fab.lock_coa_flag,'N') lock_coa_flag
FROM igf_ap_fa_base_rec fab
WHERE fab.base_id = c_base_id;
SELECT items.*
FROM igf_aw_coa_items items
WHERE items.base_id = c_base_id;
SELECT terms.rowid row_id,
terms.*
FROM igf_aw_coa_itm_terms terms
WHERE base_id = c_base_id
AND item_code = c_item_code;
SELECT SUM(NVL(amount,0)) amount
FROM igf_aw_coa_itm_terms term
WHERE base_id = c_base_id
AND item_code = c_item_code;
SELECT item.rowid row_id,
item.*
FROM igf_aw_coa_items item
WHERE base_id = c_base_id
AND item_code = c_item_code;
SELECT 'X' exist
FROM igf_aw_coa_rate_det rate
WHERE ci_cal_type = cp_ci_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND item_code = cp_item_code
AND ROWNUM = 1;
lv_coa_itm_update VARCHAR2(1);
lv_coa_itm_update := 'N';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','base_id:'||p_base_id||' is locked');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','l_items.item_code:'||l_items.item_code||' is locked');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','term:'||igf_gr_gen.get_alt_code(l_terms.ld_cal_type,l_terms.ld_sequence_number)||' is locked');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','Fetching student attributes for base_id:'||p_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.evaluate.debug','ln_amount <> l_terms.amount for base_id:'||p_base_id);
igf_aw_coa_itm_terms_pkg.update_row(
x_rowid => l_terms.row_id,
x_base_id => l_terms.base_id,
x_item_code => l_terms.item_code,
x_amount => ln_amount,
x_ld_cal_type => l_terms.ld_cal_type,
x_ld_sequence_number => l_terms.ld_sequence_number,
x_mode => 'R',
x_lock_flag => l_terms.lock_flag
);
lv_coa_itm_update := 'Y';
igf_aw_coa_items_pkg.update_row(
x_rowid => l_item.row_id,
x_base_id => l_item.base_id,
x_item_code => l_item.item_code,
x_amount => l_sum_amt.amount,
x_pell_coa_amount => l_item.pell_coa_amount,
x_alt_pell_amount => l_item.alt_pell_amount,
x_fixed_cost => l_item.fixed_cost,
x_legacy_record_flag => l_item.legacy_record_flag,
x_mode => 'R',
x_lock_flag => l_item.lock_flag
);
IF lv_coa_itm_update = '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_coa_update.evaluate.debug','lv_coa_itm_update = Y');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_update.evaluate.exception','sql error message:'||SQLERRM);
fnd_message.set_token('NAME','igf_aw_coa_update.evaluate :' || SQLERRM);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_update.evaluate.exception','sql error:'||SQLERRM);
SELECT fa.base_id
FROM igf_ap_fa_base_rec_all fa
WHERE fa.ci_cal_type = c_ci_cal_type
AND fa.ci_sequence_number = c_ci_sequence_number
ORDER BY fa.base_id;
SELECT group_cd
FROM igs_pe_persid_group_all
WHERE group_id = c_grp_id;
SELECT party_number
FROM hz_parties
WHERE party_id = c_person_id;
SELECT distinct todo.person_id,
todo.s_student_todo_type,
todo.sequence_number
FROM igs_pe_std_todo todo,
igs_pe_std_todo_ref tref
WHERE tref.person_id = todo.person_id
AND tref.s_student_todo_type = todo.s_student_todo_type
AND tref.sequence_number = todo.sequence_number
AND tref.cal_type = c_ci_cal_type
AND tref.ci_sequence_number = c_ci_sequence_number
AND todo.s_student_todo_type = 'IGF_COA_COMP'
AND todo.logical_delete_dt IS NULL
AND tref.logical_delete_dt IS NULL;
SELECT tref.rowid row_id,
tref.*
FROM igs_pe_std_todo_ref tref
WHERE tref.person_id = c_person_id
AND tref.s_student_todo_type = c_s_student_todo_type
AND tref.sequence_number = c_sequence_number
AND tref.cal_type = c_ci_cal_type
AND tref.ci_sequence_number = c_ci_sequence_number
AND tref.s_student_todo_type = 'IGF_COA_COMP'
AND tref.logical_delete_dt IS NULL;
SELECT todo.rowid row_id,
todo.*
FROM igs_pe_std_todo todo
WHERE todo.person_id = c_person_id
AND todo.s_student_todo_type = c_s_student_todo_type
AND todo.sequence_number = c_sequence_number
AND todo.logical_delete_dt IS NULL
AND NOT EXISTS
(SELECT tref.person_id,
tref.s_student_todo_type,
tref.sequence_number
FROM igs_pe_std_todo_ref tref
WHERE tref.person_id = todo.person_id
AND tref.s_student_todo_type = todo.s_student_todo_type
AND tref.sequence_number = todo.sequence_number
AND tref.s_student_todo_type = 'IGF_COA_COMP'
AND tref.logical_delete_dt IS NULL
GROUP BY tref.person_id, tref.s_student_todo_type, tref.sequence_number
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_award_year:'||p_award_year);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_run_type:'||p_run_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_pid_group:'||p_pid_group);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','p_base_id:'||p_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=S with base_id:'||p_base_id);
update_to_do_items(p_base_id,lv_ci_cal_type,ln_ci_sequence_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=S done');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=Y with base_id:'||l_per_awd_rec.base_id);
update_to_do_items(l_per_awd_rec.base_id,lv_ci_cal_type,ln_ci_sequence_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=Y done');
'SELECT person_id,
person_number
FROM igs_pe_person_base_v
WHERE person_id IN ('||lv_sql_stmt||') ' USING p_pid_group;
'SELECT person_id,
person_number
FROM igs_pe_person_base_v
WHERE person_id IN ('||lv_sql_stmt||')';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=P with base_id:'||ln_base_id);
update_to_do_items(ln_base_id,lv_ci_cal_type,ln_ci_sequence_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=P done');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Starting Run_Type=A with base_id:'||ln_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Updating table igs_pe_std_todo for person id: '||l_person_id.person_id);
igs_pe_std_todo_ref_pkg.update_row(
x_rowid => l_person_ref.row_id,
x_person_id => l_person_ref.person_id,
x_s_student_todo_type => l_person_ref.s_student_todo_type,
x_sequence_number => l_person_ref.sequence_number,
x_reference_number => l_person_ref.reference_number,
x_cal_type => l_person_ref.cal_type,
x_ci_sequence_number => l_person_ref.ci_sequence_number,
x_course_cd => l_person_ref.course_cd,
x_unit_cd => l_person_ref.unit_cd,
x_other_reference => l_person_ref.other_reference,
x_logical_delete_dt => sysdate,
x_mode => 'R',
x_uoo_id => l_person_ref.uoo_id
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','c_person_todo%FOUND');
igs_pe_std_todo_pkg.update_row(
x_rowid => l_person_todo.row_id,
x_person_id => l_person_todo.person_id,
x_s_student_todo_type => l_person_todo.s_student_todo_type,
x_sequence_number => l_person_todo.sequence_number,
x_todo_dt => l_person_todo.todo_dt,
x_logical_delete_dt => sysdate,
x_mode => 'R'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_update.main.debug','Run_Type=A done');