[Home] [Help]
137: AND awdint.ci_alternate_code = p_alternate_code
138: AND awdint.import_status_type IN ('U','R')
139: ORDER BY awdint.person_number, awdint.fund_code, awdint.award_number_txt;
140: l_out_person_id hz_parties.party_id%TYPE;
141: l_out_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
142:
143: g_ci_cal_type igf_ap_batch_aw_map.ci_cal_type%TYPE;
144: g_ci_sequence_number igf_ap_batch_aw_map_all.ci_sequence_number%TYPE;
145:
147: g_sys_award_year igf_ap_batch_aw_map_all.sys_award_year%TYPE;
148: g_fund_code igf_aw_fund_mast_all.fund_code%TYPE;
149: g_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
150: g_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
151: g_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
152: g_award_id igf_aw_award_all.award_id%TYPE;
153: g_disb_net_amount igf_aw_li_dact_ints.disb_net_amt%TYPE;
154: g_person_id hz_parties.party_id%TYPE;
155: g_processing_string VARCHAR2(1000);
170: TYPE g_old_award_hist_tab_type IS TABLE OF igf_aw_award_level_hist%ROWTYPE INDEX BY BINARY_INTEGER;
171: 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
172:
173:
174: PROCEDURE lock_std_coa (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
175: )
176: IS
177: -----------------------------------------------------------------------------
178: -- Created By : ridas
184: --
185: -----------------------------------------------------------------------------
186:
187: --Cursor to fetch person details
188: CURSOR c_get_fab (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
189: )
190: IS
191: SELECT fab.rowid row_id,
192: fab.*
189: )
190: IS
191: SELECT fab.rowid row_id,
192: fab.*
193: FROM igf_ap_fa_base_rec_all fab
194: WHERE fab.base_id = p_base_id
195: AND NVL(fab.lock_coa_flag,'N') <> 'Y';
196:
197: l_get_fab c_get_fab%ROWTYPE;
198:
199:
200: --Cursor to fetch item details
201: CURSOR c_items(
202: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
203: ) IS
204: SELECT items.rowid row_id,
205: items.*
206: FROM igf_aw_coa_items items
209:
210:
211: --Cursor to fetch term details
212: CURSOR c_terms(
213: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
214: cp_item_code igf_aw_item.item_code%TYPE
215: ) IS
216: SELECT terms.rowid row_id,
217: terms.*
424: added to the legacy awards table.
425: ***************************************************************/
426:
427: -- validates the award year interface record
428: CURSOR cur_check_sys_awd (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
429: SELECT award_id, legacy_record_flag
430: FROM igf_aw_award_all
431: WHERE base_id = p_base_id
432: AND award_number_txt = li_awd_rec.award_number_txt;
475:
476: CURSOR c_get_person_ssn IS
477: SELECT api.api_person_id, api.person_id_type, api.start_dt, api.end_dt
478: FROM igs_pe_alt_pers_id api,
479: igf_ap_fa_base_rec_all fabase,
480: igs_pe_person_id_typ pid
481: WHERE fabase.person_id = api.pe_person_id
482: AND fabase.base_id = g_base_id
483: AND api.person_id_Type = pid.person_id_type
525: c_std_max_yr_amt_rec c_std_max_yr_amt%ROWTYPE;
526:
527: CURSOR c_std_max_lf_count IS
528: SELECT NVL(SUM( NVL(awd.accepted_amt,awd.offered_amt)), 0) lf_total
529: FROM igf_aw_award_all awd, igf_aw_fund_mast_all fund, igf_ap_fa_base_rec_all fabase
530: WHERE fund.fund_code = g_fund_code
531: AND fabase.person_id = g_person_id
532: AND awd.base_id = fabase.base_id
533: AND awd.fund_id = fund.fund_id;
543: WHERE base_id = g_base_id);
544:
545: -- nsidana 11/27/2003 FA131 COD updates build.
546: -- Get the details of transaction number for the base ID and the APP_TRANS_ID present in the interface table.
547: CURSOR c_get_trans_num(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,cp_app_trans_num_txt VARCHAR2) IS
548: SELECT transaction_num
549: FROM
550: igf_ap_isir_matched
551: WHERE
1828: l_warning VARCHAR2(200);
1829: l_trans_type VARCHAR2(1);
1830: l_cal_type igs_ca_inst_all.cal_type%TYPE;
1831: ln_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1832: ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
1833: l_hol_rel_ind VARCHAR2(30) := NULL;
1834:
1835:
1836: BEGIN
3254: l_warning_records PLS_INTEGER;
3255: l_rejected_records PLS_INTEGER;
3256: l_total_records PLS_INTEGER;
3257: lv_profile_value VARCHAR2(30);
3258: lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
3259:
3260:
3261: -- cursor to get the calendar alternate code
3262: CURSOR cur_alt_code ( x_ci_cal_type igs_ca_inst.cal_type%TYPE,
3577: p_sequence_number igs_ca_inst.sequence_number%TYPE
3578: ) RETURN NUMBER IS
3579: CURSOR cur_base_id IS
3580: SELECT base.base_id
3581: FROM igf_ap_fa_base_rec_all base,
3582: hz_parties hp
3583: WHERE
3584: base.ci_sequence_number = p_sequence_number
3585: AND base.ci_cal_type = p_cal_type
3585: AND base.ci_cal_type = p_cal_type
3586: AND base.person_id = hp.party_id
3587: AND hp.party_number = p_person_number;
3588:
3589: l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
3590:
3591: BEGIN
3592: IF(p_person_number IS NOT NULL AND p_cal_type IS NOT NULL AND p_sequence_number IS NOT NULL)THEN
3593: OPEN cur_base_id; FETCH cur_base_id INTO l_base_id; CLOSE cur_base_id;