[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| veramach 04-Dec-2003 FA 131 COD Updates |
| Changed column names in legacy award/ |
| disbursement table to be in sync with the CS |
| nsidana 11/27/2003 FA131 COD updated for 2004-2005 build. |
| New cols added to legacy award and legacy award |
| disbursements table. Impact done here. |
| sjadhav 19-Nov-2003 Bug 3160568 FA 125 Build. Added run routine |
| Added validations for EXT Award Import |
| Added validations for attendance_type_code |
| veramach 1-NOV-2003 FA 125 Multiple Distr Methods |
| Changed calll to igf_aw_awd_disb_pkg.update_row|
| to reflect the addition of attendance_type_code|
| brajendr 08-Oct-2003 Bug # 3116511 - Update the Auth Date |
| |
| sjalasut June 16,2003 Created as Part of Fa118.2 Legacy Import Build |
| sjalasut August 4, 2003 Import Sponsorships and Federal Work Study |
| funds for FACR117, part of sep 03 patch |
| sjalasut Aug 12, 2003 Bug 3093913. Changed the message for validatio|
| n on transaction type. |
*=======================================================================*/
/***************************************************************
Created By : nsidana
Date Created By : 11/28/2003
Purpose : Legacy Awards Import package body.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
added to the legacy awards table and 1 feild in the legacy award
disbursements table.
***************************************************************/
-- cursor to fetch records from the award legacy interface tables
CURSOR cur_legacy_award_int(p_alternate_code igs_ca_inst.alternate_code%TYPE,
p_batch_num igf_aw_li_awd_ints.batch_num%TYPE) IS
SELECT
awdint.batch_num,
trim(awdint.ci_alternate_code) ci_alternate_code,
trim(awdint.fund_code) fund_code,
trim(awdint.person_number) person_number,
trim(awdint.award_number_txt) award_number_txt,
awdint.import_status_type,
awdint.offered_amt,
awdint.accepted_amt,
trim(awdint.award_status_code) award_status_code,
awdint.award_date,
awdint.alt_pell_schedule_type,
awdint.import_record_type,
awdint.created_by,
awdint.creation_date,
awdint.last_updated_by,
awdint.last_update_date,
awdint.last_update_login,
awdint.request_id,
awdint.program_application_id,
awdint.program_id,
awdint.program_update_date,
awdint.lock_award_flag, --new col for FA131 COD updates build
awdint.app_trans_num_txt, --new col for FA131 COD updates build
awdint.authorization_date,
awdint.publish_in_ss_flag
FROM igf_aw_li_awd_ints awdint
WHERE awdint.batch_num = p_batch_num
AND awdint.ci_alternate_code = p_alternate_code
AND awdint.import_status_type IN ('U','R')
ORDER BY awdint.person_number, awdint.fund_code, awdint.award_number_txt;
SELECT * FROM igf_aw_award_all
WHERE award_id = cp_award_id;
g_old_award_rec c_get_awd_details%ROWTYPE; -- Collects information of the old award (that is getting deleted)
g_update_mode BOOLEAN := FALSE; -- To Track whether running in update mode or not
g_old_award_hist_col g_old_award_hist_tab_type; -- Holds the history of the award that is getting deleted when running in update_mode
SELECT fab.rowid row_id,
fab.*
FROM igf_ap_fa_base_rec_all fab
WHERE fab.base_id = p_base_id
AND NVL(fab.lock_coa_flag,'N') <> 'Y';
SELECT items.rowid row_id,
items.*
FROM igf_aw_coa_items items
WHERE base_id = cp_base_id
AND NVL(lock_flag,'N') <> 'Y';
SELECT terms.rowid row_id,
terms.*
FROM igf_aw_coa_itm_terms terms
WHERE base_id = cp_base_id
AND item_code = cp_item_code
AND NVL(lock_flag,'N') <> 'Y';
igf_aw_coa_items_pkg.update_row(
x_rowid => l_items.row_id,
x_base_id => l_items.base_id,
x_item_code => l_items.item_code,
x_amount => l_items.amount,
x_pell_coa_amount => l_items.pell_coa_amount,
x_alt_pell_amount => l_items.alt_pell_amount,
x_fixed_cost => l_items.fixed_cost,
x_legacy_record_flag => l_items.legacy_record_flag,
x_mode => 'R',
x_lock_flag => 'Y'
);
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 => l_terms.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 => 'Y'
);
igf_ap_fa_base_rec_pkg.update_row
(x_Mode => 'R',
x_rowid => l_get_fab.row_id,
x_base_id => l_get_fab.base_id,
x_ci_cal_type => l_get_fab.ci_cal_type,
x_person_id => l_get_fab.person_id,
x_ci_sequence_number => l_get_fab.ci_sequence_number,
x_org_id => l_get_fab.org_id,
x_coa_pending => l_get_fab.coa_pending,
x_verification_process_run => l_get_fab.verification_process_run,
x_inst_verif_status_date => l_get_fab.inst_verif_status_date,
x_manual_verif_flag => l_get_fab.manual_verif_flag,
x_fed_verif_status => l_get_fab.fed_verif_status,
x_fed_verif_status_date => l_get_fab.fed_verif_status_date,
x_inst_verif_status => l_get_fab.inst_verif_status,
x_nslds_eligible => l_get_fab.nslds_eligible,
x_ede_correction_batch_id => l_get_fab.ede_correction_batch_id,
x_fa_process_status_date => l_get_fab.fa_process_status_date,
x_ISIR_corr_status => l_get_fab.ISIR_corr_status,
x_ISIR_corr_status_date => l_get_fab.ISIR_corr_status_date,
x_ISIR_status => l_get_fab.ISIR_status,
x_ISIR_status_date => l_get_fab.ISIR_status_date,
x_coa_code_f => l_get_fab.coa_code_f,
x_coa_code_i => l_get_fab.coa_code_i,
x_coa_f => l_get_fab.coa_f,
x_coa_i => l_get_fab.coa_i,
x_disbursement_hold => l_get_fab.disbursement_hold,
x_fa_process_status => l_get_fab.fa_process_status,
x_notification_status => l_get_fab.notification_status,
x_notification_status_date => l_get_fab.notification_status_date,
x_packaging_status => l_get_fab.packaging_status,
x_packaging_status_date => l_get_fab.packaging_status_date,
x_total_package_accepted => l_get_fab.total_package_accepted,
x_total_package_offered => l_get_fab.total_package_offered,
x_admstruct_id => l_get_fab.admstruct_id,
x_admsegment_1 => l_get_fab.admsegment_1,
x_admsegment_2 => l_get_fab.admsegment_2,
x_admsegment_3 => l_get_fab.admsegment_3,
x_admsegment_4 => l_get_fab.admsegment_4,
x_admsegment_5 => l_get_fab.admsegment_5,
x_admsegment_6 => l_get_fab.admsegment_6,
x_admsegment_7 => l_get_fab.admsegment_7,
x_admsegment_8 => l_get_fab.admsegment_8,
x_admsegment_9 => l_get_fab.admsegment_9,
x_admsegment_10 => l_get_fab.admsegment_10,
x_admsegment_11 => l_get_fab.admsegment_11,
x_admsegment_12 => l_get_fab.admsegment_12,
x_admsegment_13 => l_get_fab.admsegment_13,
x_admsegment_14 => l_get_fab.admsegment_14,
x_admsegment_15 => l_get_fab.admsegment_15,
x_admsegment_16 => l_get_fab.admsegment_16,
x_admsegment_17 => l_get_fab.admsegment_17,
x_admsegment_18 => l_get_fab.admsegment_18,
x_admsegment_19 => l_get_fab.admsegment_19,
x_admsegment_20 => l_get_fab.admsegment_20,
x_packstruct_id => l_get_fab.packstruct_id,
x_packsegment_1 => l_get_fab.packsegment_1,
x_packsegment_2 => l_get_fab.packsegment_2,
x_packsegment_3 => l_get_fab.packsegment_3,
x_packsegment_4 => l_get_fab.packsegment_4,
x_packsegment_5 => l_get_fab.packsegment_5,
x_packsegment_6 => l_get_fab.packsegment_6,
x_packsegment_7 => l_get_fab.packsegment_7,
x_packsegment_8 => l_get_fab.packsegment_8,
x_packsegment_9 => l_get_fab.packsegment_9,
x_packsegment_10 => l_get_fab.packsegment_10,
x_packsegment_11 => l_get_fab.packsegment_11,
x_packsegment_12 => l_get_fab.packsegment_12,
x_packsegment_13 => l_get_fab.packsegment_13,
x_packsegment_14 => l_get_fab.packsegment_14,
x_packsegment_15 => l_get_fab.packsegment_15,
x_packsegment_16 => l_get_fab.packsegment_16,
x_packsegment_17 => l_get_fab.packsegment_17,
x_packsegment_18 => l_get_fab.packsegment_18,
x_packsegment_19 => l_get_fab.packsegment_19,
x_packsegment_20 => l_get_fab.packsegment_20,
x_miscstruct_id => l_get_fab.miscstruct_id,
x_miscsegment_1 => l_get_fab.miscsegment_1,
x_miscsegment_2 => l_get_fab.miscsegment_2,
x_miscsegment_3 => l_get_fab.miscsegment_3,
x_miscsegment_4 => l_get_fab.miscsegment_4,
x_miscsegment_5 => l_get_fab.miscsegment_5,
x_miscsegment_6 => l_get_fab.miscsegment_6,
x_miscsegment_7 => l_get_fab.miscsegment_7,
x_miscsegment_8 => l_get_fab.miscsegment_8,
x_miscsegment_9 => l_get_fab.miscsegment_9,
x_miscsegment_10 => l_get_fab.miscsegment_10,
x_miscsegment_11 => l_get_fab.miscsegment_11,
x_miscsegment_12 => l_get_fab.miscsegment_12,
x_miscsegment_13 => l_get_fab.miscsegment_13,
x_miscsegment_14 => l_get_fab.miscsegment_14,
x_miscsegment_15 => l_get_fab.miscsegment_15,
x_miscsegment_16 => l_get_fab.miscsegment_16,
x_miscsegment_17 => l_get_fab.miscsegment_17,
x_miscsegment_18 => l_get_fab.miscsegment_18,
x_miscsegment_19 => l_get_fab.miscsegment_19,
x_miscsegment_20 => l_get_fab.miscsegment_20,
x_prof_judgement_flg => l_get_fab.prof_judgement_flg,
x_nslds_data_override_flg => l_get_fab.nslds_data_override_flg,
x_target_group => l_get_fab.target_group,
x_coa_fixed => l_get_fab.coa_fixed,
x_profile_status => l_get_fab.profile_status,
x_profile_status_date => l_get_fab.profile_status_date,
x_profile_fc => l_get_fab.profile_fc,
x_coa_pell => l_get_fab.coa_pell,
x_manual_disb_hold => l_get_fab.manual_disb_hold,
x_pell_alt_expense => l_get_fab.pell_alt_expense,
x_assoc_org_num => l_get_fab.assoc_org_num,
x_award_fmly_contribution_type => l_get_fab.award_fmly_contribution_type,
x_packaging_hold => l_get_fab.packaging_hold,
x_isir_locked_by => l_get_fab.isir_locked_by ,
x_adnl_unsub_loan_elig_flag => l_get_fab.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => l_get_fab.lock_awd_flag,
x_lock_coa_flag => 'Y'
);
nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
added to the legacy awards table.
***************************************************************/
-- validates the award year interface record
CURSOR cur_check_sys_awd (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
SELECT award_id, legacy_record_flag
FROM igf_aw_award_all
WHERE base_id = p_base_id
AND award_number_txt = li_awd_rec.award_number_txt;
SELECT fmast.fund_code, fcat.fed_fund_code, fmast.fund_id
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_cat fcat
WHERE fmast.fund_code = fcat.fund_code
AND fmast.fund_code = p_fund_code
AND fmast.ci_cal_type = g_ci_cal_type
AND fmast.ci_sequence_number = g_ci_sequence_number
AND discontinue_fund = 'N';
SELECT 'X' present_in_award
FROM igf_aw_award_all
WHERE base_id = g_base_id
AND fund_id = g_fund_id;
SELECT 'X' exist_hold
FROM igf_aw_li_hold_ints
WHERE person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt
AND ci_alternate_code = li_awd_rec.ci_alternate_code;
SELECT fmast.max_yearly_amt
FROM igf_aw_fund_mast_all fmast, igf_aw_fund_cat_all fcat
WHERE fmast.fund_id = g_fund_id
AND fcat.fed_fund_code = g_fed_fund_code
AND fmast.ci_cal_type = g_ci_cal_type
AND fmast.ci_sequence_number = g_ci_sequence_number
AND fmast.fund_code = fcat.fund_code
AND fmast.discontinue_fund = 'N';
SELECT api.api_person_id, api.person_id_type, api.start_dt, api.end_dt
FROM igs_pe_alt_pers_id api,
igf_ap_fa_base_rec_all fabase,
igs_pe_person_id_typ pid
WHERE fabase.person_id = api.pe_person_id
AND fabase.base_id = g_base_id
AND api.person_id_Type = pid.person_id_type
AND pid.s_person_id_type = 'SSN'
AND SYSDATE BETWEEN api.start_Dt AND NVL(api.end_dt,SYSDATE);
nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
added to the legacy awards table.
***************************************************************/
CURSOR c_get_pay_isir IS
SELECT isir_id payment_isir_id
FROM igf_ap_isir_matched_all
WHERE base_id = g_base_id
AND system_record_type = 'ORIGINAL'
AND payment_isir = 'Y';
SELECT fund_source FROM igf_aw_fund_cat_all WHERE fund_code = li_awd_rec.fund_code;
SELECT NVL(remaining_amt,0) remaining_amt, NVL(max_award_amt,0) max_award_amt,
max_yearly_amt, max_life_amt
FROM igf_aw_fund_mast_all
WHERE fund_id = g_fund_id;
SELECT NVL(SUM(NVL(awd.accepted_amt,awd.offered_amt)), 0) yr_total
FROM igf_aw_award_all awd
WHERE awd.fund_id = g_fund_id
AND awd.base_id = g_base_id;
SELECT NVL(SUM( NVL(awd.accepted_amt,awd.offered_amt)), 0) lf_total
FROM igf_aw_award_all awd, igf_aw_fund_mast_all fund, igf_ap_fa_base_rec_all fabase
WHERE fund.fund_code = g_fund_code
AND fabase.person_id = g_person_id
AND awd.base_id = fabase.base_id
AND awd.fund_id = fund.fund_id;
SELECT MST.item_code, mst.todo_number
FROM igf_ap_td_item_mst mst, igf_aw_fund_td_map fund
WHERE fund.fund_id = g_fund_id
AND mst.todo_number = fund.item_sequence_number
AND fund.item_sequence_number NOT IN (SELECT item_sequence_number
FROM IGF_AP_TD_ITEM_INST
WHERE base_id = g_base_id);
SELECT transaction_num
FROM
igf_ap_isir_matched
WHERE
BASE_ID=cp_base_id AND
transaction_num=cp_app_trans_num_txt;
Purpose : Bug # 4635941 - Since new award is created when runnig in update mode, there wud be some difference in
award attributes.This function will identify the differences in the old award (deleted) and new award (created).
For the difference in award attributes, it inserts a history record
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
CURSOR c_lookup_attribute IS
SELECT lookup_code FROM igf_lookups_view
WHERE lookup_type = 'IGF_AW_AWARD_ATTRIBUTES';
SELECT igf_aw_award_level_hist_s.NEXTVAL INTO l_award_hist_tran_id from dual;
/* If award attributes Change, then insert */
IF (l_awd_attr_changed) THEN
igf_aw_award_level_hist_pkg.insert_row
(
x_rowid => l_row_id,
x_award_id => g_new_award_rec.award_id,
x_award_hist_tran_id => l_award_hist_tran_id,
x_award_attrib_code => l_award_atrr_code,
x_award_change_source_code => 'CONCURRENT_PROCESS',
x_old_offered_amt => g_old_award_rec.offered_amt,
x_new_offered_amt => g_new_award_rec.offered_amt,
x_old_accepted_amt => g_old_award_rec.accepted_amt,
x_new_accepted_amt => g_new_award_rec.accepted_amt,
x_old_paid_amt => g_old_award_rec.paid_amt,
x_new_paid_amt => g_new_award_rec.paid_amt,
x_old_lock_award_flag => g_old_award_rec.lock_award_flag,
x_new_lock_award_flag => g_new_award_rec.lock_award_flag,
x_old_award_status_code => g_old_award_rec.award_status,
x_new_award_status_code => g_new_award_rec.award_status,
x_old_adplans_id => g_old_award_rec.adplans_id,
x_new_adplans_id => g_new_award_rec.adplans_id,
x_mode => 'R'
);
Purpose : Bug # 4635941 - Maintains the Award History details of the deleted award and assigns
those to the newly created award (g_award_id)
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
BEGIN
FOR i IN g_old_award_hist_col.FIRST..g_old_award_hist_col.LAST LOOP
INSERT INTO igf_aw_award_level_hist (
award_id,
award_hist_tran_id,
award_attrib_code,
award_change_source_code,
old_offered_amt,
new_offered_amt,
old_accepted_amt,
new_accepted_amt,
old_paid_amt,
new_paid_amt,
old_lock_award_flag,
new_lock_award_flag,
old_award_status_code,
new_award_status_code,
old_adplans_id,
new_adplans_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date
) VALUES (
g_award_id,
g_old_award_hist_col(i).award_hist_tran_id,
g_old_award_hist_col(i).award_attrib_code,
g_old_award_hist_col(i).award_change_source_code,
g_old_award_hist_col(i).old_offered_amt,
g_old_award_hist_col(i).new_offered_amt,
g_old_award_hist_col(i).old_accepted_amt,
g_old_award_hist_col(i).new_accepted_amt,
g_old_award_hist_col(i).old_paid_amt,
g_old_award_hist_col(i).new_paid_amt,
g_old_award_hist_col(i).old_lock_award_flag,
g_old_award_hist_col(i).new_lock_award_flag,
g_old_award_hist_col(i).old_award_status_code,
g_old_award_hist_col(i).new_award_status_code,
g_old_award_hist_col(i).old_adplans_id,
g_old_award_hist_col(i).new_adplans_id,
g_old_award_hist_col(i).created_by,
g_old_award_hist_col(i).creation_date,
g_old_award_hist_col(i).last_updated_by,
g_old_award_hist_col(i).last_update_date,
g_old_award_hist_col(i).last_update_login,
g_old_award_hist_col(i).request_id,
g_old_award_hist_col(i).program_id,
g_old_award_hist_col(i).program_application_id,
g_old_award_hist_col(i).program_update_date
);
SELECT a.visa_type,
a.visa_category,
a.visa_number,
a.visa_expiry_date,
b.visit_start_date entry_date
FROM igs_pe_visa a,
igs_pe_visit_histry b
WHERE a.person_id = cv_person_id
AND NVL(a.visa_expiry_date,SYSDATE) >= SYSDATE
AND a.visa_id = b.visa_id
ORDER BY a.visa_expiry_date DESC;
SELECT person_first_name,
person_last_name,
address1,
address2,
address3,
address4,
city,
state,
province,
county,
country
FROM hz_parties
WHERE party_id = cv_person_id;
igf_aw_award_pkg.insert_row(
x_rowid => l_awd_rowid,
x_award_id => g_award_id,
x_fund_id => g_fund_id,
x_base_id => g_base_id,
x_offered_amt => li_awd_rec.offered_amt,
x_accepted_amt => li_awd_rec.accepted_amt,
x_paid_amt => NULL,
x_packaging_type => NULL,
x_batch_id => NULL,
x_manual_update => NULL,
x_rules_override => NULL,
x_award_date => li_awd_rec.award_date,
x_award_status => li_awd_rec.award_status_code,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_rvsn_id => NULL,
x_alt_pell_schedule => l_alt_pell_schedule,
x_award_number_txt => li_awd_rec.award_number_txt,
x_legacy_record_flag => 'Y',
x_adplans_id => NULL,
x_lock_award_flag => li_awd_rec.lock_award_flag,
x_app_trans_num_txt => li_awd_rec.app_trans_num_txt,
x_awd_proc_status_code => NULL,
x_notification_status_code => 'R',
x_notification_status_date => TRUNC(SYSDATE),
x_publish_in_ss_flag => li_awd_rec.publish_in_ss_flag
);
IF g_update_mode AND g_old_award_hist_col.COUNT > 0 THEN
-- Maintain / preserve the old award history if running in UPDATE MODE and if some history exists
maintain_old_award_hist_rec;
IF g_update_mode THEN
create_new_award_hist_rec;
g_update_mode := FALSE;
g_old_award_hist_col.DELETE;
PROCEDURE delete_award_and_child_records(p_award_id IN igf_aw_award_all.award_id%TYPE) IS
/***************************************************************
Created By : nsidana
Date Created By : 11/28/2003
Purpose : deletes awards and child records.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
bvisvana 17-Oct-2005 Bug # 4635941 - Capturing the award history before deleting the award
Capturing the award details before deleting the award
***************************************************************/
CURSOR c_get_awd_row IS
SELECT ROWID row_id
FROM igf_aw_award_all
WHERE award_id = p_award_id;
SELECT ROWID row_id
FROM igf_aw_award_level_hist
WHERE award_id = p_award_id;
SELECT * FROM igf_aw_award_level_hist
WHERE award_id = p_award_id;
SELECT ROWID row_id, auth_id
FROM igf_aw_awd_disb_all
WHERE award_id = p_award_id;
SELECT ROWID row_id
FROM igf_db_awd_disb_dtl_all
WHERE award_id = p_award_id;
SELECT ROWID row_id
FROM igf_db_disb_holds_all
WHERE award_id = p_award_id;
SELECT ROWID row_id
FROM igf_se_auth
WHERE auth_id = cp_auth_id;
SELECT ROWID row_id
FROM igf_aw_db_chg_dtls
WHERE award_id = p_award_id;
SELECT ROWID row_id
FROM igf_aw_db_cod_dtls
WHERE award_id = p_award_id;
BEGIN --begin of delete_award_and_child_records
-- bvisvana - Bug # 4635941 - START
-- While deleting the award, collect details of the award and also its history.
g_hist_cnt := 0;
g_old_award_hist_col(g_hist_cnt).last_updated_by := c_award_hist_rec.last_updated_by;
g_old_award_hist_col(g_hist_cnt).last_update_date := c_award_hist_rec.last_update_date;
g_old_award_hist_col(g_hist_cnt).last_update_login := c_award_hist_rec.last_update_login;
g_old_award_hist_col(g_hist_cnt).program_update_date := c_award_hist_rec.program_update_date;
igf_db_awd_disb_dtl_pkg.delete_row(c_get_disb_dtl_row_rec.row_id);
igf_aw_db_cod_dtls_pkg.delete_row(c_get_cod_dtl_row_rec.row_id);
igf_aw_db_chg_dtls_pkg.delete_row(c_get_disb_chg_dtls_row_rec.row_id);
igf_db_disb_holds_pkg.delete_row(c_get_disb_holds_row_rec.row_id);
igf_se_auth_pkg.delete_row(c_get_auth_row_rec.row_id);
igf_aw_awd_disb_pkg.delete_row(c_get_disb_row_rec.row_id);
igf_aw_award_level_hist_pkg.delete_row(award_hist_rec.row_id);
igf_aw_award_pkg.delete_row(c_get_awd_row_rec.row_id);
END delete_award_and_child_records;
Purpose : updates awards records with legacy status.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
CURSOR c_get_award IS
SELECT ROWID row_id,awd.*
FROM igf_aw_award_all awd
WHERE award_id = p_award_id;
igf_aw_award_pkg.update_row (
x_rowid => c_get_award_rec.row_id,
x_award_id => c_get_award_rec.award_id,
x_fund_id => c_get_award_rec.fund_id,
x_base_id => c_get_award_rec.base_id,
x_offered_amt => c_get_award_rec.offered_amt,
x_accepted_amt => c_get_award_rec.accepted_amt,
x_paid_amt => c_get_award_rec.paid_amt,
x_packaging_type => c_get_award_rec.packaging_type,
x_batch_id => c_get_award_rec.batch_id,
x_manual_update => c_get_award_rec.manual_update,
x_rules_override => c_get_award_rec.rules_override,
x_award_date => c_get_award_rec.award_date,
x_award_status => c_get_award_rec.award_status,
x_attribute_category => c_get_award_rec.attribute_category,
x_attribute1 => c_get_award_rec.attribute1,
x_attribute2 => c_get_award_rec.attribute2,
x_attribute3 => c_get_award_rec.attribute3,
x_attribute4 => c_get_award_rec.attribute4,
x_attribute5 => c_get_award_rec.attribute5,
x_attribute6 => c_get_award_rec.attribute6,
x_attribute7 => c_get_award_rec.attribute7,
x_attribute8 => c_get_award_rec.attribute8,
x_attribute9 => c_get_award_rec.attribute9,
x_attribute10 => c_get_award_rec.attribute10,
x_attribute11 => c_get_award_rec.attribute11,
x_attribute12 => c_get_award_rec.attribute12,
x_attribute13 => c_get_award_rec.attribute13,
x_attribute14 => c_get_award_rec.attribute14,
x_attribute15 => c_get_award_rec.attribute15,
x_attribute16 => c_get_award_rec.attribute16,
x_attribute17 => c_get_award_rec.attribute17,
x_attribute18 => c_get_award_rec.attribute18,
x_attribute19 => c_get_award_rec.attribute19,
x_attribute20 => c_get_award_rec.attribute20,
x_rvsn_id => c_get_award_rec.rvsn_id,
x_alt_pell_schedule => c_get_award_rec.alt_pell_schedule,
x_award_number_txt => c_get_award_rec.award_number_txt,
x_legacy_record_flag => 'Y', -- this is required as the award record is modified to have legacy as N by other processes
x_adplans_id => c_get_award_rec.adplans_id,
x_app_trans_num_txt => c_get_award_rec.app_trans_num_txt,
x_lock_award_flag => c_get_award_rec.lock_award_flag,
x_awd_proc_status_code => c_get_award_rec.awd_proc_status_code,
x_notification_status_code => c_get_award_rec.notification_status_code,
x_notification_status_date => c_get_award_rec.notification_status_date,
x_publish_in_ss_flag => c_get_award_rec.publish_in_ss_flag
);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','calling DELETE with award id' || l_award_id );
g_update_mode := TRUE; -- bvisvana - Bug # 4635941 - To track the update mode
delete_award_and_child_records(l_award_id);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','deleted records with award id' || l_award_id );
nsidana 11/28/2003 FA131 COD updates build. Added validations on 1 feild in the
legacy award disbursements table.
***************************************************************/
CURSOR cur_legacy_disb_int IS
SELECT
trim(disbint.ci_alternate_code) ci_alternate_code,
trim(disbint.person_number) person_number,
trim(disbint.award_number_txt) award_number_txt,
disbint.disbursement_num,
trim(disbint.ld_alternate_code) ld_alternate_code,
trim(disbint.tp_alternate_code) tp_alternate_code,
disbint.offered_amt,
disbint.accepted_amt,
disbint.fee_1_amt,
disbint.fee_2_amt,
disbint.disb_date,
trim(disbint.trans_type_code) trans_type_code,
disbint.elig_status_date,
disbint.affirm_flag,
disbint.int_rebate_amt,
disbint.force_disb_flag,
disbint.min_credit_pts_num,
disbint.disb_exp_date,
disbint.verf_enfr_date,
disbint.planned_credit_flag,
disbint.fee_paid_1_amt,
disbint.fee_paid_2_amt,
disbint.created_by,
disbint.creation_date,
disbint.last_updated_by,
disbint.last_update_date,
disbint.last_update_login,
trim(fee_class_code) fee_class_code,
authorization_date,
TRIM(attendance_type_code) attendance_type_code,
disbint.base_attendance_type_code -- new col added as part of FA131 COD updates build.
FROM igf_aw_li_disb_ints disbint
WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
AND person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt
ORDER BY disbursement_num;
SELECT fee_class_code
FROM igf_aw_li_disb_ints
WHERE person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt
GROUP BY fee_class_code
HAVING COUNT(fee_class_code) > 1;
SELECT SUM(offered_amt) total_offered_amt,
SUM(accepted_amt) total_accepted_amt,
COUNT(disbursement_num) number_of_disb,
NVL(MAX(disbursement_num),0) max_disb_number,
NVL(MIN(disbursement_num),0) min_disb_number
FROM igf_aw_li_disb_ints
WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
AND person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt;
SELECT fund_id, tp_cal_type, tp_sequence_number
FROM igf_aw_fund_tp_all
WHERE fund_id = g_fund_id
AND tp_cal_type = p_cal_type
AND tp_sequence_number = p_sequence_number;
SELECT fmast.max_yearly_amt
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE fcat.fund_code = fmast.fund_code
AND fcat.fed_fund_code = g_fed_fund_code
AND fmast.fund_id = g_fund_id
AND fmast.discontinue_fund = 'N';
SELECT fee_cls_id
FROM igf_sp_fc_all
WHERE fund_id = g_fund_id
AND fee_class = p_fee_class_code;
SELECT disb_activity_num
FROM igf_aw_li_dact_ints
WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
AND person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt
AND disbursement_num = p_disbursement_num;
SELECT meaning FROM igs_lookups_view WHERE lookup_type = p_lookup_type AND
lookup_code = p_lookup_code and enabled_flag = 'Y';
SELECT count(*)
FROM igf_aw_li_disb_ints disbint
WHERE disbint.ci_alternate_code = li_awd_rec.ci_alternate_code
AND disbint.person_number = li_awd_rec.person_number
AND disbint.award_number_txt = li_awd_rec.award_number_txt
ORDER BY disbursement_num;
SELECT count(distinct(ld_alternate_code))
FROM igf_aw_li_disb_ints disbint
WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
AND person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt;
SELECT attendance_type
FROM igs_en_atd_type
WHERE attendance_type = cp_atd_code;
SELECT COUNT (DISTINCT ld_alternate_code) terms,
COUNT (tp_alternate_code) tp
FROM igf_aw_li_disb_ints
WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
AND person_number = li_awd_rec.person_number
AND award_number_txt = li_awd_rec.award_number_txt;
nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
added to the legacy awards table and 1 feild in the legacy award
disbursements table.
--veramach 1-NOV-2003 FA 125 Multiple Distr Methods
-- Changed calll to igf_aw_awd_disb_pkg.update_row to reflect the addition of attendance_type_code
--rasahoo 25/Aug/2003 #3101894 Called procedure send_work_auth with parameter 'LEGACY'
***************************************************************/
l_rowid VARCHAR2(25);
SELECT cal_type, sequence_number
FROM igs_ca_inst
WHERE alternate_code = p_alternate_code;
SELECT rowid row_id, disb.*
FROM igf_aw_awd_disb_all disb
WHERE rowid = cp_row_id;
SELECT fed_fund_code
FROM igf_aw_fund_cat_all
WHERE fund_code = cp_fund_code;
igf_aw_awd_disb_pkg.insert_row(
x_rowid => l_rowid,
x_award_id => g_award_id,
x_disb_num => cur_legacy_disb_int_rec.disbursement_num,
x_tp_cal_type => c_tp_calseq_rec.cal_type,
x_tp_sequence_number=> c_tp_calseq_rec.sequence_number,
x_disb_gross_amt => cur_legacy_disb_int_rec.offered_amt,
x_fee_1 => cur_legacy_disb_int_rec.fee_1_amt,
x_fee_2 => cur_legacy_disb_int_rec.fee_2_amt,
x_disb_net_amt => l_net_amount,
x_disb_date => cur_legacy_disb_int_rec.disb_date,
x_trans_type => l_trans_type,
x_elig_status => NULL,
x_elig_status_date => cur_legacy_disb_int_rec.elig_status_date,
x_affirm_flag => cur_legacy_disb_int_rec.affirm_flag,
x_hold_rel_ind => l_hol_rel_ind,
x_manual_hold_ind => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_late_disb_ind => NULL,
x_fund_dist_mthd => NULL,
x_prev_reported_ind => NULL,
x_fund_release_date => NULL,
x_fund_status => NULL,
x_fund_status_date => NULL,
x_fee_paid_1 => cur_legacy_disb_int_rec.fee_paid_1_amt,
x_fee_paid_2 => cur_legacy_disb_int_rec.fee_paid_2_amt,
x_cheque_number => NULL,
x_ld_cal_type => c_ld_calseq_rec.cal_type,
x_ld_sequence_number=> c_ld_calseq_rec.sequence_number,
x_disb_accepted_amt => cur_legacy_disb_int_rec.accepted_amt,
x_disb_paid_amt => NULL,
x_rvsn_id => NULL,
x_int_rebate_amt => cur_legacy_disb_int_rec.int_rebate_amt,
x_force_disb => cur_legacy_disb_int_rec.force_disb_flag,
x_min_credit_pts => cur_legacy_disb_int_rec.min_credit_pts_num,
x_disb_exp_dt => cur_legacy_disb_int_rec.disb_exp_date,
x_verf_enfr_dt => cur_legacy_disb_int_rec.verf_enfr_date,
x_fee_class => cur_legacy_disb_int_rec.fee_class_code,
x_show_on_bill => cur_legacy_disb_int_rec.planned_credit_flag,
x_attendance_type_code => cur_legacy_disb_int_rec.attendance_type_code,
x_base_attendance_type_code => cur_legacy_disb_int_rec.base_attendance_type_code,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
SELECT
trim(disbact.ci_alternate_code) ci_alternate_code,
trim(disbact.person_number) person_number,
trim(disbact.award_number_txt) award_number_txt,
disbact.disbursement_num,
disbact.disb_activity_num,
trim(disbact.disb_activity_type) disb_activity_type,
disbact.disb_net_amt,
disbact.disb_date,
trim(disbact.sf_status_code) sf_status_code,
disbact.sf_status_date,
trim(disbact.sf_invoice_num_txt) sf_invoice_num_txt,
trim(disbact.sf_credit_num_txt) sf_credit_num_txt,
trim(disbact.disb_status_code) disb_status_code,
disbact.notification_date,
disbact.created_by,
disbact.creation_date,
disbact.last_updated_by,
disbact.last_update_date,
disbact.last_update_login,
trim(disbact.spnsr_credit_num_txt) spnsr_credit_num_txt,
trim(disbact.spnsr_charge_num_txt) spnsr_charge_num_txt,
trim(disbact.origination_fee_amt ) origination_fee_amt ,
trim(disbact.guarntee_fee_amt ) guarntee_fee_amt ,
trim(disbact.origination_fee_paid_amt ) origination_fee_paid_amt ,
trim(disbact.guarntee_fee_paid_amt ) guarntee_fee_paid_amt ,
trim(disbact.interest_rebate_amt ) interest_rebate_amt ,
trim(disbact.disbursement_accepted_amt ) disbursement_accepted_amt
FROM igf_aw_li_dact_ints disbact
WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
AND person_number = li_awd_disb_rec.person_number
AND award_number_txt = li_awd_disb_rec.award_number_txt
AND disbursement_num = li_awd_disb_rec.disbursement_num
ORDER BY disb_activity_num;
SELECT MAX(disb_activity_num) last_activity
FROM igf_aw_li_dact_ints
WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
AND person_number = li_awd_disb_rec.person_number
AND award_number_txt = li_awd_disb_rec.award_number_txt
AND disbursement_num = li_awd_disb_rec.disbursement_num;
SELECT credit_number, amount, credit_id
FROM igs_fi_credits
WHERE party_id = g_person_id
AND credit_number = p_credit_num;
SELECT cal_type, sequence_number
FROM igs_ca_inst
WHERE alternate_code = p_alternate_code;
SELECT invoice_number, invoice_amount, invoice_id
FROM igs_fi_inv_int
WHERE person_id = g_person_id
AND invoice_number = p_invoice_num;
SELECT disbact.disb_net_amt, disbact.disb_date
FROM igf_aw_li_dact_ints disbact
WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
AND person_number = li_awd_disb_rec.person_number
AND award_number_txt = li_awd_disb_rec.award_number_txt
AND disbursement_num = li_awd_disb_rec.disbursement_num
ORDER BY disb_activity_num;
SELECT *
FROM igf_aw_li_dact_ints
WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
AND person_number = li_awd_disb_rec.person_number
AND award_number_txt = li_awd_disb_rec.award_number_txt
AND disbursement_num = li_awd_disb_rec.disbursement_num
AND disb_activity_num = p_disb_seq_num;
SELECT ROWID row_id
FROM igf_aw_db_chg_dtls
WHERE award_id = p_award_id
AND disb_num = p_disb_num
AND disb_seq_num = p_disb_seq_num;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disb_act_int_rec.debug','inserting into igf_db_awd_disb_dtl table');
igf_db_awd_disb_dtl_pkg.insert_row (
x_rowid => l_rowid,
x_award_id => g_award_id,
x_disb_num => li_awd_disb_rec.disbursement_num,
x_disb_seq_num => cur_li_disb_act_int_rec.disb_activity_num,
x_disb_gross_amt => cur_li_disb_act_int_rec.disbursement_accepted_amt,
x_fee_1 => cur_li_disb_act_int_rec.origination_fee_amt ,
x_fee_2 => cur_li_disb_act_int_rec.guarntee_fee_amt ,
x_disb_net_amt => cur_li_disb_act_int_rec.disb_net_amt,
x_disb_adj_amt => l_adjust_amt ,
x_disb_date => cur_li_disb_act_int_rec.disb_date,
x_fee_paid_1 => cur_li_disb_act_int_rec.origination_fee_paid_amt,
x_fee_paid_2 => cur_li_disb_act_int_rec.guarntee_fee_paid_amt ,
x_disb_activity => cur_li_disb_act_int_rec.disb_activity_type,
x_disb_batch_id => NULL,
x_disb_ack_date => NULL,
x_booking_batch_id => NULL,
x_booked_date => NULL,
x_disb_status => cur_li_disb_act_int_rec.disb_status_code,
x_disb_status_date => NULL,
x_sf_status => cur_li_disb_act_int_rec.sf_status_code,
x_sf_status_date => cur_li_disb_act_int_rec.sf_status_date,
x_sf_invoice_num => l_trans_invoice_id, -- dont get confused by the col name. this is invoice id only
x_spnsr_credit_id => l_trans_spnsr_credit_id,
x_spnsr_charge_id => l_trans_spnsr_charge_id,
x_sf_credit_id => l_trans_credit_id,
x_error_desc => NULL,
x_mode => 'R',
x_notification_date => cur_li_disb_act_int_rec.notification_date,
x_interest_rebate_amt => cur_li_disb_act_int_rec.interest_rebate_amt,
x_ld_cal_type => r_get_cal_typ_seq_num.cal_type,
x_ld_sequence_number => r_get_cal_typ_seq_num.sequence_number
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disb_act_int_rec.debug','inserting into igf_aw_db_chg_dtls table');
igf_aw_db_chg_dtls_pkg.delete_row(lv_rowid.row_id);
igf_aw_db_chg_dtls_pkg.insert_row (
x_rowid => l_rowid,
x_award_id => g_award_id,
x_disb_num => li_awd_disb_rec.disbursement_num,
x_disb_seq_num => cur_li_disb_act_int_rec.disb_activity_num,
x_disb_accepted_amt => cur_li_disb_act_int_rec.disbursement_accepted_amt,
x_orig_fee_amt => cur_li_disb_act_int_rec.origination_fee_amt,
x_disb_net_amt => cur_li_disb_act_int_rec.disb_net_amt,
x_disb_date => cur_li_disb_act_int_rec.disb_date,
x_disb_activity => cur_li_disb_act_int_rec.disb_activity_type,
x_disb_status => cur_li_disb_act_int_rec.disb_status_code,
x_disb_status_date => NULL,
x_disb_rel_flag => NULL,
x_first_disb_flag => NULL,
x_interest_rebate_amt => cur_li_disb_act_int_rec.interest_rebate_amt ,
x_disb_conf_flag => NULL,
x_pymnt_prd_start_date => NULL,
x_note_message => NULL,
x_batch_id_txt => NULL,
x_ack_date => NULL,
x_booking_id_txt => NULL,
x_booking_date => NULL,
x_mode => 'R'
);
SELECT
trim(disbhold.ci_alternate_code) ci_alternate_code,
trim(disbhold.person_number) person_number,
trim(disbhold.award_number_txt) award_number_txt,
disbhold.disbursement_num,
trim(disbhold.hold_code) hold_code,
disbhold.hold_date,
disbhold.release_date,
trim(disbhold.release_reason_txt) release_reason_txt,
disbhold.created_by,
disbhold.creation_date,
disbhold.last_updated_by,
disbhold.last_update_date,
disbhold.last_update_login
FROM igf_aw_li_hold_ints disbhold
WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
AND person_number = li_awd_disb_rec.person_number
AND award_number_txt = li_awd_disb_rec.award_number_txt
AND disbursement_num = li_awd_disb_rec.disbursement_num;
igf_db_disb_holds_pkg.insert_row(
x_rowid => l_rowid,
x_hold_id => l_hold_id,
x_award_id => g_award_id,
x_disb_num => li_awd_disb_rec.disbursement_num,
x_hold => cur_li_disb_hold_int_rec.hold_code,
x_hold_date => cur_li_disb_hold_int_rec.hold_date,
x_hold_type => 'SYSTEM',
x_release_date => cur_li_disb_hold_int_rec.release_date,
x_release_flag => l_release_flag,
x_release_reason=> cur_li_disb_hold_int_rec.release_reason_txt
);
p_delete_flag IN VARCHAR2
) IS
/***************************************************************
Created By : nsidana
Date Created By : 11/28/2003
Purpose : Main procedure for the legacy awards and disbursements import process.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
|| tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
SELECT cal.alternate_code
FROM igs_ca_inst cal
WHERE cal.cal_type = x_ci_cal_type
AND cal.sequence_number = x_ci_sequence_number;
SELECT award_year_status_code, sys_award_year
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = x_ci_cal_type
AND ci_sequence_number = x_ci_sequence_number;
UPDATE igf_aw_li_awd_ints
SET import_status_type = p_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
AND person_number = ctx_li_awd_rec.person_number
AND award_number_txt = ctx_li_awd_rec.award_number_txt
AND import_status_type = ctx_li_awd_rec.import_status_type;
DELETE FROM igf_aw_li_dact_ints
WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
AND person_number = ctx_li_awd_rec.person_number
AND award_number_txt = ctx_li_awd_rec.award_number_txt;
DELETE FROM igf_aw_li_hold_ints
WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
AND person_number = ctx_li_awd_rec.person_number
AND award_number_txt = ctx_li_awd_rec.award_number_txt;
DELETE FROM igf_aw_li_disb_ints
WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
AND person_number = ctx_li_awd_rec.person_number
AND award_number_txt = ctx_li_awd_rec.award_number_txt;
DELETE FROM igf_aw_li_awd_ints
WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
AND person_number = ctx_li_awd_rec.person_number
AND award_number_txt = ctx_li_awd_rec.award_number_txt
AND import_status_type = ctx_li_awd_rec.import_status_type;
FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DELETE_FLAG'),25) || ' : '||
igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag));
IF(l_return_status_awd <> 'E' AND p_delete_flag ='Y')THEN
del_or_upd_int_records('D',cur_legacy_award_int_rec,NULL); -- NULL here does not matter as the op is delete
IF(p_delete_flag = 'N')THEN
del_or_upd_int_records('U',cur_legacy_award_int_rec,'I');
IF(p_delete_flag = 'N')THEN
del_or_upd_int_records('U',cur_legacy_award_int_rec,'W');
SELECT base.base_id
FROM igf_ap_fa_base_rec_all base,
hz_parties hp
WHERE
base.ci_sequence_number = p_sequence_number
AND base.ci_cal_type = p_cal_type
AND base.person_id = hp.party_id
AND hp.party_number = p_person_number;
p_delete_flag IN VARCHAR2
)
IS
/***************************************************************
Created By : nsidana
Date Created By : 11/28/2003
Purpose :
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
lv_errbuf VARCHAR2(4000);
p_delete_flag);