[Home] [Help]
80: -- mnade 5/28/2005 - Added the common cursors here.
81:
82: CURSOR g_c_get_details
83: (
84: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
85: cp_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
86: cp_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE,
87: cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
88: cp_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE,
138: order by cai.start_dt, fmast.description;
139:
140: CURSOR g_c_get_load_cals
141: (
142: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
143: cp_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
144: cp_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE,
145: cp_award_prd_cd igf_aw_awd_prd_term.award_prd_cd%TYPE
146: ) IS
180: order by cai.start_dt;
181:
182: --mnade 5/28/2005 Generic function to get base id and avoid join with igf_ap_base_rec_all
183: FUNCTION get_base_id_for_person (
184: p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
185: p_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
186: p_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE
187: ) RETURN NUMBER IS
188: CURSOR c_base_id (
185: p_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
186: p_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE
187: ) RETURN NUMBER IS
188: CURSOR c_base_id (
189: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
190: cp_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
191: cp_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE
192: )
193: IS
192: )
193: IS
194: SELECT
195: base.base_id
196: FROM igf_ap_fa_base_rec_all base
197: WHERE
198: person_id = cp_person_id
199: AND base.ci_cal_type = cp_fa_cal_type
200: AND base.ci_sequence_number = cp_fa_sequence_number;
316: log_to_fnd('build_sql_stmt','debug','SQL Stmt got from build_sql_stmt is '||p_sql_stmt);
317: END build_sql_stmt;
318:
319: FUNCTION efc_i(
320: l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
321: p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
322: ) RETURN NUMBER AS
323: ------------------------------------------------------------------
324: --Created by : veramach, Oracle India
396: --
397: (reverse chronological order - newest change first)
398: ***************************************************************/
399:
400: CURSOR c_efc(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
401: cp_months NUMBER)
402: IS
403: SELECT DECODE(f.award_fmly_contribution_type,
404: 2, DECODE(cp_months, 1 ,isir.sec_alternate_month_1,
428: ) efc,
429: isir.primary_efc primary_efc,
430: NVL(isir.auto_zero_efc,'N') auto_zero_efc
431: FROM igf_ap_isir_matched isir,
432: igf_ap_fa_base_rec_all f
433: WHERE isir.base_id = cp_base_id
434: AND isir.base_id = f.base_id
435: AND isir.active_isir='Y';
436:
440: l_awdprd_startdt DATE;
441:
442:
443: CURSOR get_round_off(
444: cp_base_id igf_ap_fa_base_rec.base_id%TYPE
445: ) IS
446: SELECT num_days_divisor,
447: roundoff_fact
448: FROM igf_ap_efc_v efc,
445: ) IS
446: SELECT num_days_divisor,
447: roundoff_fact
448: FROM igf_ap_efc_v efc,
449: igf_ap_fa_base_rec_all fabase
450: WHERE efc.ci_cal_type = fabase.ci_cal_type
451: AND efc.ci_sequence_number = fabase.ci_sequence_number
452: AND fabase.base_id = cp_base_id;
453: lv_round_off_rec get_round_off%ROWTYPE;
619:
620: END unmetneed_f;
621:
622: FUNCTION unmetneed_i(
623: l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
624: p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
625: ) RETURN NUMBER AS
626: ------------------------------------------------------------------
627: --Created by : veramach, Oracle India
672:
673: (reverse chronological order - newest change first)
674: ***************************************************************/
675:
676: coa_amt igf_ap_fa_base_rec_all.coa_f%TYPE;
677: need_f NUMBER;
678: l_efc_f NUMBER;
679:
680: BEGIN
693: END need_f;
694:
695:
696: FUNCTION need_i(
697: l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
698: p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
699: ) RETURN NUMBER AS
700: ------------------------------------------------------------------
701: --Created by : veramach, Oracle India
709: --Change History:
710: --Who When What
711: -------------------------------------------------------------------
712:
713: coa_amt igf_ap_fa_base_rec_all.coa_f%TYPE;
714: need_i NUMBER;
715: l_efc_i NUMBER;
716:
717:
1418: igf_aw_award_all awd,
1419: igf_aw_fund_mast fmast,
1420: igs_ca_inst_all cai,
1421: igf_aw_awd_disb disb,
1422: igf_ap_fa_base_rec_all base
1423: WHERE
1424: fmast.ci_cal_type = LTRIM(RTRIM(SUBSTR(p_parameter_1, 1, 10))) AND
1425: fmast.ci_sequence_number = TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_parameter_1, 11)))) AND
1426: awd.base_id = base.base_id AND
1777: (reverse chronological order - newest change first)
1778: ***************************************************************/
1779:
1780: /* Main Cursor to Get the incomplete items*/
1781: CURSOR c_tdcur(l_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1782: SELECT tdii.base_id,
1783: tdii.item_sequence_number,
1784: tdii.add_date,
1785: tdii.status_date,
1797:
1798: CURSOR c_base IS
1799: SELECT fabase.base_id
1800: FROM
1801: igf_ap_fa_base_rec_all fabase
1802: WHERE person_id = p_person_id AND
1803: fabase.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10)) AND
1804: fabase.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)));
1805:
1803: fabase.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10)) AND
1804: fabase.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)));
1805:
1806: l_tdrec c_tdcur%ROWTYPE;
1807: l_base igf_ap_fa_base_rec_all.base_id%TYPE;
1808: l_newcorsp_dt igf_ap_td_item_inst_all.corsp_date%TYPE;
1809: l_new_cnt NUMBER;
1810: BEGIN
1811:
1891: FROM
1892: igf_db_awd_disb_dtl_all disb
1893: WHERE award_id IN (SELECT award_id
1894: FROM igf_aw_award_all aw,
1895: igf_ap_fa_base_rec_all fbase
1896: WHERE fbase.base_id = aw.base_id AND
1897: fbase.person_id = p_person_id AND
1898: fbase.ci_cal_type = RTRIM(SUBSTR(p_award_year,1,10)) AND
1899: fbase.ci_sequence_number = TO_NUMBER(RTRIM(SUBSTR(p_award_year,11))));
1976: IS
1977: SELECT tdii.rowid row_id,tdii.*
1978: FROM
1979: igf_ap_td_item_inst_all tdii,
1980: igf_ap_fa_base_rec_all facon
1981: WHERE
1982: facon.base_id = tdii.base_id AND
1983: NVL(tdii.inactive_flag,'N')='N' AND
1984: tdii.status IN ('INC','REQ') AND
1997: WHERE
1998: ctext.active = 'N' AND
1999: ctext.base_id IN (SELECT base_id
2000: FROM
2001: igf_ap_fa_base_rec_all where person_id=p_person_id);
2002:
2003: l_cur_corr_text cur_corr_text%ROWTYPE;
2004:
2005:
2127: Change History
2128: Who When What
2129: rasahoo 18-NOV-2003 FA 128 - ISIR update 2004-05
2130: added new parameter award_fmly_contribution_type to
2131: igf_ap_fa_base_rec_pkg.update_row
2132: ugummall 25-SEP-2003 FA 126 Multiple FA Offices
2133: added new parameter assoc_org_num to
2134: igf_ap_fa_base_rec_pkg.update_row call.
2135:
2130: added new parameter award_fmly_contribution_type to
2131: igf_ap_fa_base_rec_pkg.update_row
2132: ugummall 25-SEP-2003 FA 126 Multiple FA Offices
2133: added new parameter assoc_org_num to
2134: igf_ap_fa_base_rec_pkg.update_row call.
2135:
2136: masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
2137: removed packaging hold
2138:
2189: CURSOR c_cust_text IS
2190: SELECT corr.custom_text
2191: FROM
2192: igf_ap_st_corr_text corr,
2193: igf_ap_fa_base_rec_all fbase
2194: WHERE
2195: fbase.base_id = corr.base_id AND
2196: fbase.person_id = p_person_id AND
2197: corr.active = 'Y';