[Home] [Help]
68: -- Bug ID : 1723272
69: -- who when what
70: -- mesriniv 20-Apr-2001 1.Change has been done in the cursor c_stud_det
71: -- to fetch the data from IGF_AP_FA_CON_V
72: -- instead of IGF_AP_FA_BASE_REC.
73: --------------------------------------------------------------------------------
74: -- Bug ID : 1731302 COA not calcuated for the whole award year
75: -- avenkatr 18-Apr-01 1. When the award year IS split and before
76: -- it is assigned to the cal_type it IS trimmed
85: E_SKIP_STD_NO_ITEMS EXCEPTION;
86: E_SKIP_STD_NO_TERMS EXCEPTION;
87:
88: FUNCTION overaward_amount(
89: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
90: ) RETURN NUMBER AS
91: ------------------------------------------------------------------
92: --Created by : veramach, Oracle India
93: --Date created: 08-JAN-2004
126: END overaward_amount;
127:
128: FUNCTION coa_needs_update(
129: p_item_code igf_aw_coa_items.item_code%TYPE,
130: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
131: p_amount igf_aw_coa_items.amount%TYPE,
132: p_pell_coa_amount igf_aw_coa_items.pell_coa_amount%TYPE,
133: p_alt_pell_amount igf_aw_coa_items.alt_pell_amount%TYPE,
134: p_fixed_cost igf_aw_coa_items.fixed_cost%TYPE
150:
151: -- check if the student's COA item needs update or not
152: CURSOR c_coa_item(
153: cp_item_code igf_aw_coa_items.item_code%TYPE,
154: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
155: cp_amount igf_aw_coa_items.amount%TYPE,
156: cp_pell_coa_amount igf_aw_coa_items.pell_coa_amount%TYPE,
157: cp_alt_pell_amount igf_aw_coa_items.alt_pell_amount%TYPE,
158: cp_fixed_cost igf_aw_coa_items.fixed_cost%TYPE
171:
172: -- get lock flag
173: CURSOR c_get_lock_flg(
174: cp_item_code igf_aw_coa_items.item_code%TYPE,
175: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
176: ) IS
177: SELECT lock_flag
178: FROM igf_aw_coa_items
179: WHERE item_code = cp_item_code
205: END IF;
206: END coa_needs_update;
207:
208: FUNCTION does_term_mismatch(
209: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
210: p_process_id NUMBER
211: ) RETURN BOOLEAN AS
212: ------------------------------------------------------------------
213: --Created by : veramach, Oracle India
222: --Who When What
223: -------------------------------------------------------------------
224: --Cursor to check for load calandar match
225: CURSOR c_load_cal_chk(
226: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
227: c_process_id NUMBER
228: ) IS
229: SELECT DISTINCT ld_cal_type,ld_sequence_number
230: FROM igf_aw_coa_itm_terms
237: c_cal_chk_rec c_load_cal_chk%ROWTYPE;
238:
239: -- New cursor Added on 20-DEC-02 for for terms inconsistency check
240: CURSOR c_load_cal_chk1(
241: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
242: c_process_id NUMBER
243: ) IS
244: SELECT DISTINCT ld_cal_type,ld_sequence_number
245: FROM igf_aw_award_t
277: END does_term_mismatch;
278:
279:
280: FUNCTION iscoalocked(
281: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
282: ) RETURN BOOLEAN AS
283: ------------------------------------------------------------------
284: --Created by : ridas, Oracle India
285: --Date created: 01-Nov-2004
292: --Who When What
293: -------------------------------------------------------------------
294:
295: CURSOR c_coa(
296: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
297: ) IS
298: SELECT rowid row_id,
299: item_code,
300: lock_flag
301: FROM igf_aw_coa_items
302: WHERE base_id = cp_base_id;
303:
304: CURSOR c_coa_terms(
305: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
306: cp_item_code igf_aw_coa_items.item_code%TYPE
307: ) IS
308: SELECT rowid row_id,
309: lock_flag
349:
350:
351:
352: PROCEDURE delete_coa(
353: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
354: ) AS
355: ------------------------------------------------------------------
356: --Created by : veramach, Oracle India
357: --Date created: 26-Dec-2003
365: --Who When What
366: -------------------------------------------------------------------
367:
368: CURSOR c_coa(
369: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
370: ) IS
371: SELECT rowid row_id,
372: item_code
373: FROM igf_aw_coa_items
373: FROM igf_aw_coa_items
374: WHERE base_id = cp_base_id;
375:
376: CURSOR c_coa_terms(
377: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
378: cp_item_code igf_aw_coa_items.item_code%TYPE
379: ) IS
380: SELECT rowid row_id
381: FROM igf_aw_coa_itm_terms
570: --Porcedure add_coa_items calculates the cost of attendance for a student
571: --CREATED BY :gmuralid
572: -------------------------------------------------------------------------------------------------
573: PROCEDURE add_coa_items(
574: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
575: p_grp_code IN igf_aw_coa_grp_item.coa_code%TYPE,
576: exeorder IN VARCHAR2,
577: l_process_id IN NUMBER,
578: result OUT NOCOPY VARCHAR2
659: l_rowid ROWID;
660:
661: -- Cursor to check for item match
662: CURSOR c_check_item_match(
663: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
664: c_process_id NUMBER
665: ) IS
666: SELECT DISTINCT coa_group_items.new_item,
667: coa_group_items.item_amount,
693: chk_item_match_rec c_check_item_match%ROWTYPE;
694:
695: -- select item's terms and term amount
696: CURSOR c_item_term(
697: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
698: cp_item_code igf_aw_coa_itm_terms.item_code%TYPE,
699: cp_ld_cal_type igf_aw_coa_itm_terms.ld_cal_type%TYPE,
700: cp_ld_sequence_number igf_aw_coa_itm_terms.ld_sequence_number%TYPE
701: ) IS
711:
712:
713: --Cursor to fetch item details for the base id
714: CURSOR c_items(
715: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
716: c_item_code igf_aw_coa_itm_terms.item_code%TYPE
717: ) IS
718: SELECT item.rowid row_id,
719: item.*
724: l_items c_items%ROWTYPE;
725:
726: --Cursor to fetch the sum amount of all the terms for the base id
727: CURSOR c_terms(
728: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
729: ) IS
730: SELECT item_code,
731: SUM(NVL(amount,0)) amount
732: FROM igf_aw_coa_itm_terms term
1663: --Procedure to print output file
1664: --CREATED BY:gmuralid
1665: -------------------------------------------------------------------------------
1666: PROCEDURE print_output_file(
1667: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
1668: ) IS
1669:
1670: CURSOR c_out_file(
1671: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1667: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
1668: ) IS
1669:
1670: CURSOR c_out_file(
1671: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1672: ) IS
1673: SELECT ca.alternate_code term,
1674: SUM(NVL(terms.amount,0)) amount
1675: FROM igf_aw_coa_itm_terms terms,
1682: ORDER BY 1;
1683: c_out_file_rec c_out_file%ROWTYPE;
1684:
1685: CURSOR c_total_coa(
1686: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1687: ) IS
1688: SELECT coa_f total
1689: FROM igf_ap_fa_base_rec
1690: WHERE base_id = c_base_id;
1685: CURSOR c_total_coa(
1686: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1687: ) IS
1688: SELECT coa_f total
1689: FROM igf_ap_fa_base_rec
1690: WHERE base_id = c_base_id;
1691: c_total_coa_rec c_total_coa%ROWTYPE;
1692:
1693: BEGIN
1735:
1736:
1737:
1738: PROCEDURE process_student(
1739: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1740: p_grp_code igf_aw_coa_grp_item.coa_code%TYPE,
1741: p_process_id NUMBER
1742: ) IS
1743: ------------------------------------------------------------------
1756: -------------------------------------------------------------------
1757:
1758: -- Cursor below retrieves all existing COA information for a student
1759: CURSOR cur_per_coa(
1760: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1761: ) IS
1762: SELECT 'x'
1763: FROM igf_aw_coa_itm_terms coa
1764: WHERE coa.base_id = p_base_id
1768: lv_result VARCHAR2(5);
1769:
1770: -- Get pell COA amounts
1771: CURSOR c_pell_coa(
1772: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1773: ) IS
1774: SELECT SUM(NVL(pell_coa_amount,0)) pell_coa,
1775: SUM(NVL(alt_pell_amount,0)) alt_pell_coa
1776: FROM igf_aw_coa_items
1780: l_new_coa c_pell_coa%ROWTYPE;
1781:
1782: -- check whether the student has pell award
1783: CURSOR c_pell_award(
1784: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1785: ) IS
1786: SELECT 'x'
1787: FROM igf_aw_fund_cat_all fcat,
1788: igf_aw_fund_mast_all fmast,
1797:
1798:
1799: --This cursor is to fetch person details
1800: CURSOR c_base_rec (
1801: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1802: ) IS
1803: SELECT NVL(fab.lock_coa_flag,'N') lock_coa_flag
1804: FROM igf_ap_fa_base_rec fab
1805: WHERE fab.base_id = c_base_id;
1800: CURSOR c_base_rec (
1801: c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1802: ) IS
1803: SELECT NVL(fab.lock_coa_flag,'N') lock_coa_flag
1804: FROM igf_ap_fa_base_rec fab
1805: WHERE fab.base_id = c_base_id;
1806:
1807: l_base_rec c_base_rec%ROWTYPE;
1808:
1989: p_update_coa IN VARCHAR2,
1990: p_update_method IN VARCHAR2,
1991: l_run_type IN VARCHAR2,
1992: p_pergrp_id IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
1993: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
1994: ) IS
1995: --------------------------------------------------------------------------------
1996: -- this procedure is called from concurrent manager.
1997: -- if the parameters passed are not correct then procedure exits
2023:
2024: TYPE CpregrpCurTyp IS REF CURSOR ;
2025: cur_per_grp CpregrpCurTyp ;
2026: TYPE CpergrpTyp IS RECORD(
2027: person_id igf_ap_fa_base_rec_all.person_id%TYPE,
2028: person_number igs_pe_person_base_v.person_number%TYPE
2029: );
2030: per_grp_rec CpergrpTyp ;
2031:
2033:
2034: --Cursor below retrieves all the students belonging to a given AWARD YEAR
2035:
2036: CURSOR c_per_awd_yr(
2037: c_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2038: c_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2039: ) IS
2040: SELECT fa.base_id
2041: FROM igf_ap_fa_base_rec_all fa
2034: --Cursor below retrieves all the students belonging to a given AWARD YEAR
2035:
2036: CURSOR c_per_awd_yr(
2037: c_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2038: c_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2039: ) IS
2040: SELECT fa.base_id
2041: FROM igf_ap_fa_base_rec_all fa
2042: WHERE fa.ci_cal_type = c_ci_cal_type
2037: c_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2038: c_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2039: ) IS
2040: SELECT fa.base_id
2041: FROM igf_ap_fa_base_rec_all fa
2042: WHERE fa.ci_cal_type = c_ci_cal_type
2043: AND fa.ci_sequence_number = c_ci_sequence_number;
2044: per_awd_rec c_per_awd_yr%ROWTYPE;
2045:
2058: FROM igs_pe_persid_group_all
2059: WHERE group_id = c_grp_id;
2060: c_grp_cd c_group_code%ROWTYPE;
2061:
2062: ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
2063: lv_err_msg fnd_new_messages.message_name%TYPE;
2064:
2065: BEGIN
2066: igf_aw_gen.set_org_id(NULL);