[Home] [Help]
1: PACKAGE BODY igs_fi_bill_extract AS
2: /* $Header: IGSFI61B.pls 120.16 2006/06/27 14:15:06 skharida ship $ */
3: ------------------------------------------------------------------
4: --Change History:
5: --Who When What
16: -- In PROCEDURE bill_the_person, added new cursor cur_fee_type and added fee_class_meaning
17: -- incase of Sponsor system fee type.
18: --uudayapr 28-Jun-2005 Bug 2767636 Modified the bill_the_person procedure.
19: --svuppala 11-Mar-2005 Bug 4240402 Timezone impact; truncating the time part in calling place of the table handlers
20: -- IGS_FI_INV_INT_PKG, IGS_FI_CR_ACTIVITIES_PKG, IGS_FI_BILL_PKG.
21: -- Modified the sysdate entries as Trunc(Sysdate).
22: --pathipat 21-Jul-2004 Bug 3778782 - Modified procedure billing_extract() and bill_the_person()
23: --pathipat 06-May-2004 Bug# 3578249 - Modified procedure billing_extract()
24: --vvutukur 23-Jan-2004 Bug#3348787.Modified procedure billing_extract.
292: --vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
293: --vchappid 06-Jun-2002 Bug# 2349394, incase the fund code is sponsor then credit type description is passed else the
294: -- bill description from the fund master is passed
295: --smadathi 31-May-2002 Bug 2349394. Cursor cur_planned_crd modified to select the column bill_desc from
296: -- igf_aw_fund_mast MO view. Also the insert row call to IGS_FI_BILL_PLN_CRD_PKG modified
297: -- to add new column bill_desc.
298: --vchappid 29-Apr-2002 Bug# 2347609, Changed the cursor 'cur_remitt_addr' which is having additional where clause
299: -- removed clause 'person_id = p_n_person_id'
300: -- Bug# 2337820, removed the correspondance flag reference in the cursors 'cur_bill_to_addr',
324: CURSOR cur_start_date (
325: cp_n_person_id IN NUMBER
326: ) IS
327: SELECT (MAX (cut_off_date) + 1) start_date
328: FROM igs_fi_bill
329: WHERE person_id = cp_n_person_id;
330:
331: -- Bug#2564643 Modified cursor defination of cur_charge_trans
332: -- Removed the parameter subaccount_id to cursor and usage of column
524: cp_n_person_id IN NUMBER,
525: cp_d_start_dt IN DATE
526: ) IS
527: SELECT NVL (closing_balance, 0) closing_balance
528: FROM igs_fi_bill
529: WHERE person_id = cp_n_person_id
530: AND TRUNC(cut_off_date) = TRUNC(cp_d_start_dt - 1);
531:
532: -- Bug#2564643 Modified cursor defination of cur_charges_total
641: AND bm.award_year_status_code = 'O';
642:
643: -- The following cursor has been added as a part of Deposits Build (Bug# 2584741)
644: -- This cursor will fetch records which are to be included in report. These are
645: -- inserted into igs_fi_bill_dpsts_table from where report will pick up data.
646: CURSOR c_bill_deposits (cp_n_person_id igs_pe_person_v.person_id%TYPE,
647: cp_c_rec_installed IN VARCHAR2,
648: cp_d_start_date IN DATE,
649: cp_d_cutoff_dt IN DATE) IS
677: FOR UPDATE OF credit_activity_id NOWAIT;
678: rec_bill_deposits c_bill_deposits%ROWTYPE;
679: l_c_bill_deposits_row_id VARCHAR2(25);
680:
681: l_planned_credits igs_fi_bill_pln_crd.pln_credit_amount%TYPE;
682: l_to_pay_amount igs_fi_bill.to_pay_amount%TYPE;
683: l_message_name fnd_new_messages.message_name%TYPE:=NULL;
684: l_pln_crd_setup igs_fi_control_all.planned_credits_ind%TYPE;
685: l_pln_crd_rowid VARCHAR2(25);
678: rec_bill_deposits c_bill_deposits%ROWTYPE;
679: l_c_bill_deposits_row_id VARCHAR2(25);
680:
681: l_planned_credits igs_fi_bill_pln_crd.pln_credit_amount%TYPE;
682: l_to_pay_amount igs_fi_bill.to_pay_amount%TYPE;
683: l_message_name fnd_new_messages.message_name%TYPE:=NULL;
684: l_pln_crd_setup igs_fi_control_all.planned_credits_ind%TYPE;
685: l_pln_crd_rowid VARCHAR2(25);
686: l_fee_cal_type igs_fi_bill_pln_crd.fee_cal_type%TYPE;
682: l_to_pay_amount igs_fi_bill.to_pay_amount%TYPE;
683: l_message_name fnd_new_messages.message_name%TYPE:=NULL;
684: l_pln_crd_setup igs_fi_control_all.planned_credits_ind%TYPE;
685: l_pln_crd_rowid VARCHAR2(25);
686: l_fee_cal_type igs_fi_bill_pln_crd.fee_cal_type%TYPE;
687: l_fee_ci_seq_num igs_fi_bill_pln_crd.fee_ci_sequence_number%TYPE;
688: l_flag BOOLEAN := TRUE;
689: l_bill_desc igs_fi_bill_pln_crd.bill_desc%TYPE;
690: l_c_rec_installed igs_fi_control.rec_installed%TYPE;
683: l_message_name fnd_new_messages.message_name%TYPE:=NULL;
684: l_pln_crd_setup igs_fi_control_all.planned_credits_ind%TYPE;
685: l_pln_crd_rowid VARCHAR2(25);
686: l_fee_cal_type igs_fi_bill_pln_crd.fee_cal_type%TYPE;
687: l_fee_ci_seq_num igs_fi_bill_pln_crd.fee_ci_sequence_number%TYPE;
688: l_flag BOOLEAN := TRUE;
689: l_bill_desc igs_fi_bill_pln_crd.bill_desc%TYPE;
690: l_c_rec_installed igs_fi_control.rec_installed%TYPE;
691:
685: l_pln_crd_rowid VARCHAR2(25);
686: l_fee_cal_type igs_fi_bill_pln_crd.fee_cal_type%TYPE;
687: l_fee_ci_seq_num igs_fi_bill_pln_crd.fee_ci_sequence_number%TYPE;
688: l_flag BOOLEAN := TRUE;
689: l_bill_desc igs_fi_bill_pln_crd.bill_desc%TYPE;
690: l_c_rec_installed igs_fi_control.rec_installed%TYPE;
691:
692: -- Bug #3813498 To get system fee type and fee class associated with the fee_type
693: CURSOR cur_fee_type(cp_fee_type igs_fi_fee_type.fee_type%TYPE) IS
871: ELSE
872: l_to_pay_amount:=NULL;
873: END IF;
874:
875: -- Bug#2564643 Removed the subaccount_id from call to igs_fi_bill_pkg.insert_row
876: -- 9. Insert the data in the Billing Tables.
877: --
878: -- Insert the Bill Information.
879: -- -- Modified bill_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
878: -- Insert the Bill Information.
879: -- -- Modified bill_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
880:
881: l_r_bill_row_id := NULL;
882: igs_fi_bill_pkg.insert_row (
883: x_rowid => l_r_bill_row_id,
884: x_bill_id => l_n_bill_id_seq,
885: x_bill_number => NULL,
886: x_bill_date => TRUNC(SYSDATE),
933:
934: END IF;
935: END IF;
936:
937: igs_fi_bill_trnsctns_pkg.insert_row (
938: x_rowid => l_r_bill_trans_row_id,
939: x_transaction_id => l_n_transaction_id,
940: x_bill_id => l_n_bill_id_seq,
941: x_transaction_type => 'D',
964: l_b_txn_exist := TRUE;
965:
966: l_r_bill_trans_row_id := NULL;
967: l_n_transaction_id := NULL;
968: igs_fi_bill_trnsctns_pkg.insert_row (
969: x_rowid => l_r_bill_trans_row_id,
970: x_transaction_id => l_n_transaction_id,
971: x_bill_id => l_n_bill_id_seq,
972: x_transaction_type => 'C',
980: );
981: END LOOP;
982:
983: --Added as a part of bug:2293676
984: --Fetching all the planned credits and inserting into igs_fi_bill_pln_crd table
985: IF l_pln_crd_setup = 'Y' THEN
986:
987: FOR l_cur_planned_crd IN cur_planned_crd(p_n_person_id,p_d_cutoff_dt) LOOP
988:
1009: l_bill_desc := l_cur_planned_crd.bill_desc;
1010: END IF;
1011:
1012: l_pln_crd_rowid := NULL;
1013: igs_fi_bill_pln_crd_pkg.insert_row(
1014: x_rowid => l_pln_crd_rowid,
1015: x_bill_id => l_n_bill_id_seq,
1016: x_award_id => l_cur_planned_crd.award_id,
1017: x_disb_num => l_cur_planned_crd.disb_num,
1035: FETCH cur_remitt_addr INTO rec_cur_remitt_addr;
1036: IF (cur_remitt_addr%FOUND) THEN
1037: l_r_bill_addr_row_id := NULL;
1038: l_n_bill_addr_id := NULL;
1039: igs_fi_bill_addr_pkg.insert_row (
1040: x_rowid => l_r_bill_addr_row_id,
1041: x_bill_addr_id => l_n_bill_addr_id,
1042: x_bill_id => l_n_bill_id_seq,
1043: x_addr_type => 'R',
1067: LOOP
1068: EXIT WHEN cur_bill_to_addr%ROWCOUNT > 3;
1069: l_r_bill_addr_row_id := NULL;
1070: l_n_bill_addr_id := NULL;
1071: igs_fi_bill_addr_pkg.insert_row (
1072: x_rowid => l_r_bill_addr_row_id,
1073: x_bill_addr_id => l_n_bill_addr_id,
1074: x_bill_id => l_n_bill_id_seq,
1075: x_addr_type => 'B',
1098: --Set the flag to TRUE if deposit transactions are found for processing.
1099: l_b_txn_exist := TRUE;
1100:
1101: l_c_bill_deposits_row_id := NULL;
1102: igs_fi_bill_dpsts_pkg.insert_row (
1103: x_rowid => l_c_bill_deposits_row_id,
1104: x_bill_id => l_n_bill_id_seq,
1105: x_credit_activity_id => rec_bill_deposits.credit_activity_id,
1106: x_mode => 'R'
1393: );
1394: FETCH cur_remit_addr INTO rec_cur_remit_addr;
1395: IF (cur_remit_addr%NOTFOUND) THEN
1396: CLOSE cur_remit_addr;
1397: fnd_message.set_name ('IGS', 'IGS_FI_BILL_REMIT_ADDR_INVALID');
1398: fnd_message.set_token('REMIT_ADD_USG',l_v_usr_profl_name);
1399: igs_ge_msg_stack.add;
1400: p_c_status := 'FALSE';
1401: RETURN;
1421: --
1422: IF ((p_c_site_usg_type_cd_1 = p_c_site_usg_type_cd_2) OR
1423: (p_c_site_usg_type_cd_1 = p_c_site_usg_type_cd_3) OR
1424: (p_c_site_usg_type_cd_2 = p_c_site_usg_type_cd_3)) THEN
1425: fnd_message.set_name ('IGS', 'IGS_FI_BILL_TO_ADDR_USGS');
1426: igs_ge_msg_stack.add;
1427: p_c_status := 'FALSE';
1428: RETURN;
1429: END IF;
1434: FETCH cur_bill_to_addr_usage INTO rec_cur_bill_to_addr_usage;
1435: IF (cur_bill_to_addr_usage%NOTFOUND) THEN
1436: CLOSE cur_bill_to_addr_usage;
1437: l_n_use_num := 1;
1438: fnd_message.set_name ('IGS', 'IGS_FI_BILL_TO_ADDUSG');
1439: fnd_message.set_token ('USG_NUM', l_n_use_num);
1440: fnd_message.set_token ('ADDR_UGS_VAL', p_c_site_usg_type_cd_1);
1441: fnd_message.set_token ('BILL_USG_LKP_TYPE', l_v_bill_usg_lkp_type);
1442: igs_ge_msg_stack.add;
1453: FETCH cur_bill_to_addr_usage INTO rec_cur_bill_to_addr_usage;
1454: IF (cur_bill_to_addr_usage%NOTFOUND) THEN
1455: CLOSE cur_bill_to_addr_usage;
1456: l_n_use_num := 2;
1457: fnd_message.set_name ('IGS', 'IGS_FI_BILL_TO_ADDUSG');
1458: fnd_message.set_token ('USG_NUM', l_n_use_num);
1459: fnd_message.set_token ('ADDR_UGS_VAL', p_c_site_usg_type_cd_2);
1460: fnd_message.set_token ('BILL_USG_LKP_TYPE', l_v_bill_usg_lkp_type);
1461: igs_ge_msg_stack.add;
1473: FETCH cur_bill_to_addr_usage INTO rec_cur_bill_to_addr_usage;
1474: IF (cur_bill_to_addr_usage%NOTFOUND) THEN
1475: CLOSE cur_bill_to_addr_usage;
1476: l_n_use_num := 3;
1477: fnd_message.set_name ('IGS', 'IGS_FI_BILL_TO_ADDUSG');
1478: fnd_message.set_token ('USG_NUM', l_n_use_num);
1479: fnd_message.set_token ('ADDR_UGS_VAL', p_c_site_usg_type_cd_3);
1480: fnd_message.set_token ('BILL_USG_LKP_TYPE', l_v_bill_usg_lkp_type);
1481: igs_ge_msg_stack.add;
1621: l_b_txn_exist := TRUE;
1622:
1623: -- create billing payment plan records for active payment plan record
1624: l_c_rowid := NULL;
1625: igs_fi_bill_p_plans_pkg.insert_row (
1626: X_ROWID => l_c_rowid,
1627: X_STUDENT_PLAN_ID => rec_act_pp_dtls.student_plan_id,
1628: X_BILL_ID => p_n_bill_id,
1629: X_PLAN_START_DATE => rec_act_pp_dtls.plan_start_date,
1633:
1634: -- create billing installments record for active payment plan record
1635: FOR rec_pp_instlmnts IN c_pp_instlmnts(rec_act_pp_dtls.student_plan_id) LOOP
1636: l_c_rowid := NULL;
1637: igs_fi_bill_instls_pkg.insert_row(
1638: X_ROWID => l_c_rowid,
1639: X_STUDENT_PLAN_ID => rec_pp_instlmnts.student_plan_id,
1640: X_BILL_ID => p_n_bill_id,
1641: X_INSTALLMENT_ID => rec_pp_instlmnts.installment_id,
1656: --Set the flag to TRUE if payment plan records are found for processing.
1657: l_b_txn_exist := TRUE;
1658:
1659: l_c_rowid := NULL;
1660: igs_fi_bill_p_plans_pkg.insert_row (
1661: X_ROWID => l_c_rowid,
1662: X_STUDENT_PLAN_ID => rec_clo_dis_pp_dtls.student_plan_id,
1663: X_BILL_ID => p_n_bill_id,
1664: X_PLAN_START_DATE => rec_clo_dis_pp_dtls.plan_start_date,
1668:
1669: -- create billing installments record for closed and disqualified payment plan record
1670: FOR rec_pp_instlmnts IN c_pp_instlmnts(rec_clo_dis_pp_dtls.student_plan_id) LOOP
1671: l_c_rowid := NULL;
1672: igs_fi_bill_instls_pkg.insert_row(
1673: X_ROWID => l_c_rowid,
1674: X_STUDENT_PLAN_ID => rec_pp_instlmnts.student_plan_id,
1675: X_BILL_ID => p_n_bill_id,
1676: X_INSTALLMENT_ID => rec_pp_instlmnts.installment_id,
1687: END IF;
1688:
1689: END create_payplan_bills;
1690:
1691: END igs_fi_bill_extract;