[Home] [Help]
181:
182: RETURN l_b_bool;
183: END validate_tin;
184:
185: PROCEDURE update_credits(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
186: p_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) AS
187:
188: /******************************************************************
189: Created By : Amit Gairola
197: ***************************************************************** */
198:
199: -- Cursor for selecting all the records from the credits table for the person id
200: -- having the tax_year_name as the current tax year
201: CURSOR cur_crd(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
202: cp_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) IS
203: SELECT crd.rowid row_id,
204: crd.*
205: FROM igs_fi_credits_all crd
280: x_waiver_name => l_rec_crd.waiver_name);
281: END LOOP;
282: END update_credits;
283:
284: PROCEDURE update_charges(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
285: p_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) AS
286:
287: /******************************************************************
288: Created By : Amit Gairola
296: agairola 05-Aug-2005 Changes as per Waiver build: 3392095
297: ***************************************************************** */
298:
299: -- Cursor for selecting all the Charge records for the person and tax year.
300: CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
301: cp_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) IS
302: SELECT inv.rowid row_id, inv.*
303: FROM igs_fi_inv_int_all inv
304: WHERE person_id = cp_n_person_id
503: fnd_file.put_line(fnd_file.log,
504: p_v_parm_name||' : '||p_v_parm_value);
505: END log_line;
506:
507: FUNCTION chk_non_credit_course(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
508: p_v_override_excl VARCHAR2,
509: p_v_load_cal_type igs_ca_inst.cal_type%TYPE,
510: p_n_load_ci_seq igs_ca_inst.sequence_number%TYPE) RETURN BOOLEAN AS
511: /******************************************************************
544: RETURN TRUE;
545: END chk_non_credit_course;
546:
547: PROCEDURE log_params(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
548: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
549: p_n_person_grp_id igs_pe_persid_group_all.group_id%TYPE,
550: p_v_override_excl VARCHAR2,
551: p_v_file_addr_correction VARCHAR2,
552: p_v_test_run VARCHAR2) AS
601: g_v_line_sep);
602: END log_params;
603:
604: FUNCTION validate_params(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
605: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
606: p_n_person_grp_id igs_pe_persid_group_all.group_id%TYPE,
607: p_v_override_excl VARCHAR2,
608: p_v_file_addr_correction VARCHAR2,
609: p_v_test_run VARCHAR2) RETURN BOOLEAN AS
618: ***************************************************************** */
619: l_b_validate BOOLEAN;
620: l_c_val VARCHAR2(1);
621:
622: CURSOR cur_val_person_id(cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
623: SELECT 'x'
624: FROM igs_pe_person_base_v
625: WHERE person_id = cp_n_person_id;
626:
620: l_c_val VARCHAR2(1);
621:
622: CURSOR cur_val_person_id(cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
623: SELECT 'x'
624: FROM igs_pe_person_base_v
625: WHERE person_id = cp_n_person_id;
626:
627: CURSOR cur_val_persgrp(cp_n_pers_grp_id igs_pe_persid_group_all.group_id%TYPE,
628: cp_v_closed_ind igs_pe_persid_group_all.closed_ind%TYPE) IS
837:
838: RETURN l_v_sys_fund_type;
839: END get_sys_fund_type;
840:
841: PROCEDURE get_alt_person_id(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
842: p_v_person_id_type igs_pe_person_id_typ.person_id_type%TYPE,
843: p_v_s_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE,
844: p_v_api_pers_id OUT NOCOPY igs_pe_alt_pers_id.api_person_id%TYPE,
845: p_v_api_pers_id_uf OUT NOCOPY igs_pe_alt_pers_id.api_person_id_uf%TYPE) AS
852: Known limitations,enhancements,remarks:
853: Change History
854: Who When What
855: ***************************************************************** */
856: CURSOR cur_api_pid1(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
857: cp_v_s_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE) IS
858: SELECT altid.api_person_id,
859: altid.api_person_id_uf
860: FROM igs_pe_alt_pers_id altid,
864: AND pid.s_person_id_type = cp_v_s_person_id_type
865: AND sysdate BETWEEN altid.start_dt AND NVL(altid.end_dt, sysdate)
866: AND pid.closed_ind = 'N';
867:
868: CURSOR cur_api_pid2(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
869: cp_v_person_id_type igs_pe_person_id_typ.person_id_type%TYPE,
870: cp_v_s_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE) IS
871: SELECT altid.api_person_id,
872: altid.api_person_id_uf
901:
902: END get_alt_person_id;
903:
904: PROCEDURE box236_credits(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
905: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
906: p_v_override_excl VARCHAR2,
907: p_n_orig_credit OUT NOCOPY NUMBER,
908: p_n_adj_credit OUT NOCOPY NUMBER) AS
909:
925: -- Cursor for selecting the Negative Charge Adjustment credits, where the fee type is associated
926: -- to the current tax year being processed
927: CURSOR cur_qtre_crd(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
928: cp_d_end_date DATE,
929: cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
930: SELECT crd.*,
931: crd.rowid row_id,
932: fts.fee_type,
933: inv.invoice_id
950: -- Cursor for checking if any charge for the same person and having the same FTCI was
951: -- reported in a prior year
952: CURSOR cur_chk_inv(cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
953: cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
954: cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
955: cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
956: cp_d_start_date DATE) IS
957: SELECT 'x'
958: FROM igs_fi_inv_int_all inv,
1157:
1158: END box236_credits;
1159:
1160: PROCEDURE box236_charges(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1161: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1162: p_v_override_excl VARCHAR2,
1163: p_n_orig_charge OUT NOCOPY NUMBER,
1164: p_n_adj_charge OUT NOCOPY NUMBER,
1165: p_v_next_acad_flag OUT NOCOPY VARCHAR2) AS
1179: l_n_adj_chg NUMBER;
1180: l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
1181: l_n_load_ci_seq igs_ca_inst.sequence_number%TYPE;
1182:
1183: CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1184: cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1185: cp_d_end_date DATE) IS
1186: SELECT inv.*, inv.rowid row_id
1187: FROM igs_fi_inv_int_all inv,
1202: AND cal_type = cp_v_load_cal_type
1203: AND sequence_number = cp_n_load_ci_seq
1204: AND next_acad_flag = 'Y';
1205:
1206: CURSOR cur_chk_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1207: cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
1208: cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
1209: cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
1210: cp_d_start_date DATE) IS
1377: p_v_next_acad_flag := l_v_next_acad_flag;
1378: END box236_charges;
1379:
1380: PROCEDURE box45_credits(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1381: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1382: p_n_orig_credit OUT NOCOPY NUMBER,
1383: p_n_adj_credit OUT NOCOPY NUMBER) AS
1384:
1385: /******************************************************************
1406: l_v_message_name VARCHAR2(2000);
1407: l_var VARCHAR2(1);
1408: l_n_box_num NUMBER(5);
1409:
1410: CURSOR cur_chk_spn(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1411: cp_v_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE) IS
1412: SELECT 'x'
1413: FROM igf_aw_fund_mast_all fmast,
1414: igf_aw_fund_cat_all fcat
1415: WHERE fmast.fund_code = fcat.fund_code
1416: AND fcat.sys_fund_type = cp_v_sys_fund_type
1417: AND fmast.party_id = cp_n_person_id;
1418:
1419: CURSOR cur_crd(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1420: cp_d_start_date DATE,
1421: cp_d_end_date DATE) IS
1422: SELECT crd.*,
1423: crd.rowid row_id,
1435: SELECT 'x'
1436: FROM igf_db_awd_disb_dtl_all
1437: WHERE spnsr_credit_id = cp_n_credit_id;
1438:
1439: CURSOR cur_chk_orig_credit(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1440: cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
1441: cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
1442: cp_d_start_date DATE) IS
1443: SELECT crd.credit_id,
1639: p_n_adj_credit := l_n_aid_adj_credit;
1640: END box45_credits;
1641:
1642: PROCEDURE box45_charges(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1643: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1644: p_n_orig_charge OUT NOCOPY NUMBER,
1645: p_n_adj_charge OUT NOCOPY NUMBER) AS
1646:
1647: /******************************************************************
1659: ***************************************************************** */
1660: l_n_aid_orig_charge NUMBER;
1661: l_n_aid_adj_charge NUMBER;
1662:
1663: CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1664: cp_d_start_date DATE,
1665: cp_d_end_date DATE) IS
1666: SELECT inv.rowid row_id,
1667: inv.*,
1673: AND inv.tax_year_code IS NULL
1674: AND TRUNC(inv.invoice_creation_date) BETWEEN TRUNC(cp_d_start_date) AND TRUNC(cp_d_end_date)
1675: AND ft.s_fee_type IN ('AID_ADJ','WAIVER_ADJ');
1676:
1677: CURSOR cur_chk_orig_credit(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1678: cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
1679: cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
1680: cp_d_start_date DATE) IS
1681: SELECT crd.credit_id,
1825: p_n_adj_charge := l_n_aid_adj_charge;
1826:
1827: END box45_charges;
1828:
1829: FUNCTION compute_box8(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1830: p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) RETURN VARCHAR2 AS
1831:
1832: /******************************************************************
1833: Created By : Amit Gairola
1892:
1893: RETURN l_v_half_time_flag;
1894: END compute_box8;
1895:
1896: FUNCTION compute_box9(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1897: p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) RETURN VARCHAR2 AS
1898:
1899: /******************************************************************
1900: Created By : Amit Gairola
1920: FROM igs_ca_load_to_teach_v
1921: WHERE load_cal_type = cp_v_cal_type
1922: AND load_ci_sequence_number = cp_n_ci_seq;
1923:
1924: CURSOR cur_en_su_att(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1925: cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1926: cp_v_teach_cal_type igs_ca_inst.cal_type%TYPE,
1927: cp_n_teach_ci_seq igs_ca_inst.sequence_number%TYPE) IS
1928: SELECT 'x'
1981: RETURN l_v_grad_flag;
1982: END compute_box9;
1983:
1984: PROCEDURE insert_1098t_data(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1985: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1986: p_v_full_name igs_pe_person_base_v.full_name%TYPE,
1987: p_n_box2 igs_fi_1098t_data.billed_amt%TYPE,
1988: p_n_box3 igs_fi_1098t_data.adj_amt%TYPE,
1989: p_n_box4 igs_fi_1098t_data.fin_aid_amt%TYPE,
1982: END compute_box9;
1983:
1984: PROCEDURE insert_1098t_data(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1985: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1986: p_v_full_name igs_pe_person_base_v.full_name%TYPE,
1987: p_n_box2 igs_fi_1098t_data.billed_amt%TYPE,
1988: p_n_box3 igs_fi_1098t_data.adj_amt%TYPE,
1989: p_n_box4 igs_fi_1098t_data.fin_aid_amt%TYPE,
1990: p_n_box5 igs_fi_1098t_data.fin_aid_adj_amt%TYPE,
2007:
2008: ***************************************************************** */
2009:
2010: CURSOR cur_data_exists(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
2011: cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
2012: SELECT tdata.rowid row_id, tdata.*
2013: FROM igs_fi_1098t_data tdata
2014: WHERE tdata.party_id = cp_n_person_id
2015: AND tdata.tax_year_name = cp_v_tax_year_name
2023: l_v_api_pers_id_uf igs_pe_alt_pers_id.api_person_id_uf%TYPE;
2024:
2025: l_v_rowid VARCHAR2(2000);
2026:
2027: CURSOR cur_party_sites(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2028: cp_v_party_site_use_code igs_fi_1098t_setup.perm_party_site_use_code%TYPE) IS
2029: SELECT ps.location_id,
2030: ps.identifying_address_flag,
2031: ps.status
2069: SELECT rowid
2070: FROM igs_fi_1098t_dtls
2071: WHERE stu_1098t_id = cp_n_stu_1098t_id;
2072:
2073: CURSOR cur_1098t_data_lat(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2074: cp_v_tax_year_name igs_fi_1098t_data.tax_year_name%TYPE) IS
2075: SELECT dat.*, dat.rowid row_id
2076: FROM igs_fi_1098t_data dat
2077: WHERE party_id = cp_n_person_id
2790: END IF;
2791: END insert_1098t_data;
2792:
2793: PROCEDURE extract_data_main(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
2794: p_n_person_id igs_pe_person_base_v.person_id%TYPE,
2795: p_v_override_excl VARCHAR2,
2796: p_v_file_addr_correction VARCHAR2) AS
2797: /******************************************************************
2798: Created By : Amit Gairola
2804: Who When What
2805: abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
2806: ***************************************************************** */
2807:
2808: CURSOR cur_pers_dtl(cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
2809: SELECT person_number,
2810: first_name,
2811: last_name
2812: FROM igs_pe_person_base_v
2808: CURSOR cur_pers_dtl(cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
2809: SELECT person_number,
2810: first_name,
2811: last_name
2812: FROM igs_pe_person_base_v
2813: WHERE person_id = cp_n_person_id;
2814:
2815: l_rec_pers_dtl cur_pers_dtl%ROWTYPE;
2816:
2813: WHERE person_id = cp_n_person_id;
2814:
2815: l_rec_pers_dtl cur_pers_dtl%ROWTYPE;
2816:
2817: CURSOR cur_chk_rec_exists(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2818: cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
2819: cp_v_status igs_fi_1098t_data.status_code%TYPE) IS
2820: SELECT dat.override_flag, dat.irs_filed_flag
2821: FROM igs_fi_1098t_data dat
2825: ORDER BY stu_1098t_id DESC;
2826:
2827: l_rec_1098t_data cur_chk_rec_exists%ROWTYPE;
2828:
2829: CURSOR cur_non_res_alien(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2830: cp_d_start_date DATE,
2831: cp_d_end_date DATE) IS
2832: SELECT 'x'
2833: FROM igs_pe_typ_instances_all ptyp,