[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| updated for the base_id with which it was |
| added. PREFLEND to do item is not updated. |
| Skipped when trying to add it again. |
| - main proc and log_input_params gets changed|
| cdcruz 01-Oct-2003 Bug 3085558 - FA121 Verificaion Wksht |
| all parameters made optional now |
| bkkumar 04-jun-2003 Bug #2858504
| Added legacy_record_flag
| in the table handler calls for
| igf_ap_td_item_inst_pkg.insert_row
| rasingh 09-Jan-2003 Bug 2738442, Message IGF_AP_INVALID_QUERY |
| added |
| brajendr 19-OCT-2002 Bug # 2632359 |
| Modified the Log file contents with |
| descriptive messages |
|
| brajendr 12-Oct-2002 Changes the paramter order as specified in |
| the concurrent Job |
| Modified the Messages IGF_AP_NO_BASEID |
| bkkumar 04-jun-2003 Bug #2858504 |
| Added legacy_record_flag |
| in the table handler calls for |
| igf_ap_td_item_inst_pkg.insert_row |
| gvarapra 13-sep-2004 Added the validation to auto update the |
| To Do Item status that are attached to the |
| system to do type of ISIR or PROFILE. |
*=======================================================================*/
-- global Variables
ln_skip_items NUMBER := 0;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_ci_cal_type
AND sequence_number = cp_ci_sequence_number;
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type='IGF_GE_PARAMETERS'
AND lookup_code IN ('AWARD_YEAR','PERSON_NUMBER','PERSON_ID_GROUP','ITEM_CODE','PARAMETER_PASS','UPDATE_TD_MODE');
SELECT description
FROM igf_ap_td_item_mst
WHERE todo_number = cp_todo_number;
SELECT pe.person_number
FROM igs_pe_person_base_v pe,
igf_ap_fa_base_rec_all fa
WHERE pe.person_id = fa.person_id
AND fa.base_id = cp_base_id;
SELECT description
FROM igs_pe_all_persid_group_v
WHERE group_id = cp_person_id_grp;
SELECT description
FROM igf_ap_td_item_mst_all
WHERE todo_number = cp_item_number;
ELSIF parameter_rec.lookup_code ='UPDATE_TD_MODE' THEN
l_upd_mode_pmpt := TRIM(parameter_rec.meaning);
|| If item is PREFLEND, then the base id with which it was added is updated not with
|| the current base id
|| (reverse chronological order - newest change first)
*/
-- Get the details of Item codes and its descritpions
CURSOR c_item_details(
cp_todo_number igf_ap_td_item_mst.todo_number%TYPE
) IS
SELECT item_code, description, required_for_application, freq_attempt, max_attempt, career_item, system_todo_type_code
FROM igf_ap_td_item_mst
WHERE todo_number = cp_todo_number;
SELECT 1 value, fab.base_id base_id
FROM igf_ap_fa_base_rec fab,
igf_ap_td_item_inst inst,
igf_ap_td_item_mst mst
WHERE fab.person_id = cp_person_id
AND mst.todo_number = cp_todo_number
AND mst.career_item = 'Y'
AND inst.item_sequence_number = mst.todo_number
AND inst.base_id = fab.base_id;
SELECT item_inst.ROWID row_id, item_inst.*
FROM igf_ap_td_item_inst_all item_inst
WHERE base_id = cp_base_id
AND item_sequence_number = cp_item_sequence_number;
SELECT 'x'
FROM
igf_sl_cl_pref_lenders
WHERE person_id = cp_person_id AND
TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND NVL(TRUNC(end_date),TRUNC(sysdate));
Here, we have to update the item based on the following conditions:
If p_upd_mode = UPD, then update the item at all times
If p_upd_mode = NO_UPD_IF_COMP, then update is the status is NOT COM
*/
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','p_upd_mode:'||p_todo_number||' and lc_to_do_item.status:'||lc_to_do_item.status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','calling igf_ap_td_item_inst_pkg.update_row with base_id/item_seq_number:'||lc_to_do_item.base_id||'/'||lc_to_do_item.item_sequence_number);
igf_ap_td_item_inst_pkg.update_row (
x_mode => 'R',
x_rowid => lc_to_do_item.row_id,
x_base_id => lc_to_do_item.base_id,
x_item_sequence_number => lc_to_do_item.item_sequence_number,
x_status => l_status,
x_status_date => TRUNC(SYSDATE),
x_add_date => lc_to_do_item.add_date,
x_corsp_date => lc_to_do_item.corsp_date,
x_corsp_count => lc_to_do_item.corsp_count,
x_inactive_flag => lc_to_do_item.inactive_flag,
x_required_for_application => lc_to_do_item.required_for_application,
x_freq_attempt => lc_to_do_item.freq_attempt,
x_max_attempt => lc_to_do_item.max_attempt,
x_legacy_record_flag => lc_to_do_item.legacy_record_flag,
x_clprl_id => lc_to_do_item.clprl_id
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.add_to_do.debug','calling igf_ap_td_item_inst_pkg.insert_row with base_id/item_seq_number:'||l_base_id||'/'||p_todo_number);
igf_ap_td_item_inst_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
x_base_id => l_base_id,
x_item_sequence_number => p_todo_number,
x_status => l_status,
x_status_date => TRUNC(SYSDATE),
x_add_date => TRUNC(SYSDATE),
x_corsp_date => NULL,
x_corsp_count => NULL,
x_inactive_flag => 'N',
x_required_for_application => lc_item_details.required_for_application,
x_freq_attempt => lc_item_details.freq_attempt,
x_max_attempt => lc_item_details.max_attempt,
x_legacy_record_flag => NULL,
x_clprl_id => NULL
);
SELECT person_id, person_number, full_name
FROM igs_pe_prsid_grp_mem_v
WHERE group_id = cp_person_id_grp
AND TRUNC(SYSDATE) BETWEEN NVL(start_date,TRUNC(SYSDATE)) AND NVL(end_date,TRUNC(SYSDATE));
SELECT base_id
FROM igf_ap_fa_base_rec
WHERE person_id = cp_person_id
AND ci_cal_type = cp_ci_cal_type
AND ci_sequence_number = cp_ci_sequence_number;
SELECT pe.person_number, pe.full_name person_name, fa.person_id
FROM igf_ap_fa_base_rec fa,
igs_pe_person_base_v pe
WHERE fa.base_id = cp_base_id
AND fa.person_id = pe.person_id;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type='IGF_GE_PARAMETERS'
AND lookup_code IN ('PERSON_NUMBER');
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE payment_isir='Y'
AND base_id=p_base_id
AND system_record_type NOT IN ('INTERNAL','SIMULATED');
SELECT cssp_id
FROM igf_ap_css_profile_all
WHERE active_profile='Y'
AND base_id=p_base_id;
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE base_id=p_base_id
AND system_record_type IN ('ORIGINAL');
SELECT cssp_id
FROM igf_ap_css_profile_all
WHERE base_id=p_base_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_todo_grps_prc_pkg.each_student_todo.debug','calling igf_ap_batch_ver_prc_pkg.update_process_status');
igf_ap_batch_ver_prc_pkg.update_process_status(p_base_id, NULL);
OPEN c_person_id_grp FOR 'SELECT person_id,person_number,full_name
FROM igs_pe_person_base_v
WHERE person_id in ('||lv_sql_stmt||') ' USING p_person_id_grp;
OPEN c_person_id_grp FOR 'SELECT person_id,person_number,full_name
FROM igs_pe_person_base_v
WHERE person_id in ('||lv_sql_stmt||')';
lv_sql_stmt := 'SELECT COUNT(1) FROM ( '||lv_sql_stmt||')';