[Home] [Help]
47: 2. Added check in validate_param() to check if active balance rule has been defined for the
48: balance type of HOLDS.
49: 3. Also added in the same function, check if the process start date is not later than the
50: last_conversion_date of the balance rule when the hold plan name is at 'Account' level.
51: 4. In validate_holds(), removed insertion of balance_amount into the igs_fi_person_holds
52: table as the column is being obsoleted. Added cursor c_bal_amount to obtain holds balance
53: from igs_fi_balances (in place of balance amount from igs_fi_person_holds)
54: 5. Added parameter balance_rule_id in calls to check_exclusion_rules()
55: 6. Removed igs_ge_date.igsdate(p_process_start_date) and replaced with just
49: 3. Also added in the same function, check if the process start date is not later than the
50: last_conversion_date of the balance rule when the hold plan name is at 'Account' level.
51: 4. In validate_holds(), removed insertion of balance_amount into the igs_fi_person_holds
52: table as the column is being obsoleted. Added cursor c_bal_amount to obtain holds balance
53: from igs_fi_balances (in place of balance amount from igs_fi_person_holds)
54: 5. Added parameter balance_rule_id in calls to check_exclusion_rules()
55: 6. Removed igs_ge_date.igsdate(p_process_start_date) and replaced with just
56: p_process_Start_date. similarly for process_end_date also.
57: pkpatel 30-SEP-2002 Bug No: 2600842
142: p_person_number IN igs_pe_person_v.person_number%TYPE ,
143: p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
144: P_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
145: P_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE,
146: p_process_end_date IN igs_fi_person_holds.process_end_dT%TYPE ,
147: p_process_start_date IN igs_fi_person_holds.process_start_dT%TYPE ,
148: P_test_run IN VARCHAR2 ,
149: P_hold_type OUT NOCOPY igs_fi_hold_plan.hold_type%Type,
150: P_hold_plan_level OUT NOCOPY igs_fi_hold_plan.hold_plan_level%TYPE,
143: p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
144: P_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
145: P_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE,
146: p_process_end_date IN igs_fi_person_holds.process_end_dT%TYPE ,
147: p_process_start_date IN igs_fi_person_holds.process_start_dT%TYPE ,
148: P_test_run IN VARCHAR2 ,
149: P_hold_type OUT NOCOPY igs_fi_hold_plan.hold_type%Type,
150: P_hold_plan_level OUT NOCOPY igs_fi_hold_plan.hold_plan_level%TYPE,
151: P_holds_charges OUT NOCOPY igs_fi_inv_int.invoice_amount%TYPE,
271: l_credit_amnt_rec c_credit_amnt%ROWTYPE;
272: l_invoice_amnt_rec c_invoice_amnt%ROWTYPE;
273: l_message_name fnd_new_messages.message_name%TYPE := NULL;
274: l_holds_balance igs_fi_balances.holds_balance%TYPE :=0;
275: l_payment_due_date igs_fi_person_holds.process_start_dT%TYPE;
276: l_credit_subac igs_fi_credits.amount%TYPE :=0;
277: l_tot_credits igs_fi_credits.amount%TYPE :=0;
278: l_charges igs_fi_inv_int.invoice_amount%TYPE :=0;
279: l_final_balance igs_fi_credits.amount%TYPE :=0;
571: --To validate holds to be applied
572:
573: FUNCTION validate_holds(p_person_id IN igs_pe_person_v.person_id%TYPE ,
574: p_person_number IN igs_pe_person_v.person_number%TYPE ,
575: p_hold_start_date IN igs_fi_person_holds.hold_start_dt%type,
576: p_hold_type IN igs_fi_hold_plan.hold_type%TYPE,
577: p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%TYPE,
578: p_hold_plan_level IN igs_fi_hold_plan.hold_plan_level%TYPE,
579: p_process_start_dt IN igs_fi_person_holds.process_start_dT%TYPE ,
575: p_hold_start_date IN igs_fi_person_holds.hold_start_dt%type,
576: p_hold_type IN igs_fi_hold_plan.hold_type%TYPE,
577: p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%TYPE,
578: p_hold_plan_level IN igs_fi_hold_plan.hold_plan_level%TYPE,
579: p_process_start_dt IN igs_fi_person_holds.process_start_dT%TYPE ,
580: p_process_end_dt IN igs_fi_person_holds.process_end_dT%TYPE ,
581: p_offset_days IN NUMBER,
582: p_holds_charges IN igs_fi_inv_int.invoice_amount%TYPE,
583: p_holds_final_balance IN igs_fi_credits.amount%TYPE,
576: p_hold_type IN igs_fi_hold_plan.hold_type%TYPE,
577: p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%TYPE,
578: p_hold_plan_level IN igs_fi_hold_plan.hold_plan_level%TYPE,
579: p_process_start_dt IN igs_fi_person_holds.process_start_dT%TYPE ,
580: p_process_end_dt IN igs_fi_person_holds.process_end_dT%TYPE ,
581: p_offset_days IN NUMBER,
582: p_holds_charges IN igs_fi_inv_int.invoice_amount%TYPE,
583: p_holds_final_balance IN igs_fi_credits.amount%TYPE,
584: p_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
595: Purpose : To validate holds to be applied
596: Known Limitations,Enhancements or Remarks:
597: Change History :
598: Who When What
599: sapanigr 04-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_person_holds
600: are now rounded off to currency precision
601: svuppala 17-AUG-2005 Bug 4557933 - Unable to remove holds that were put on with concurrent manager
602: Passing global values to AUTHORISING_PERSON_ID and AUTH_RESP_ID from SWS Holds API
603: while calling insert_row of IGS_PE_PERS_ENCUMB_PKG
603: while calling insert_row of IGS_PE_PERS_ENCUMB_PKG
604: smadathi 28-Aug-2003 Enh Bug 3045007. Added 2 new IN parameter - p_n_student_plan_id and
605: p_d_last_inst_due_date
606: pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
607: Added param x_release_credit_id to TBH calls of igs_fi_person_holds
608: pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
609: Removed parameter p_auth_person_id. Passed Null to authorising_person_id
610: in the call to igs_pe_pers_encumb_pkg.insert_row
611: ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
608: pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
609: Removed parameter p_auth_person_id. Passed Null to authorising_person_id
610: in the call to igs_pe_pers_encumb_pkg.insert_row
611: ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
612: pathipat 04-OCT-2002 Enh Bug: 2562745 -- obsoleted column balance_amount from igs_fi_person_holds
613: Removed column balance_amount from call to igs_fi_person_holds_pkg.insert_row()
614: pkpatel 04-OCT-2002 Bug No: 2600842
615: Added the parameter auth_resp_id in the call to TBH igs_pe_pers_encumb_pkg
616:
609: Removed parameter p_auth_person_id. Passed Null to authorising_person_id
610: in the call to igs_pe_pers_encumb_pkg.insert_row
611: ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
612: pathipat 04-OCT-2002 Enh Bug: 2562745 -- obsoleted column balance_amount from igs_fi_person_holds
613: Removed column balance_amount from call to igs_fi_person_holds_pkg.insert_row()
614: pkpatel 04-OCT-2002 Bug No: 2600842
615: Added the parameter auth_resp_id in the call to TBH igs_pe_pers_encumb_pkg
616:
617: ***************************************************************/
724: RETURN FALSE;
725: END IF;
726: IF p_hold_plan_level ='S' THEN
727: l_rowid := NULL;
728: --To insert the required data elements as shown below to the intermediate table IGS_FI_PERSON_HOLDS
729: --Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
730: igs_fi_person_holds_pkg.insert_row(
731: x_Mode => 'R',
732: x_RowId => l_rowid,
726: IF p_hold_plan_level ='S' THEN
727: l_rowid := NULL;
728: --To insert the required data elements as shown below to the intermediate table IGS_FI_PERSON_HOLDS
729: --Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
730: igs_fi_person_holds_pkg.insert_row(
731: x_Mode => 'R',
732: x_RowId => l_rowid,
733: x_person_id => p_person_id,
734: x_hold_plan_name => p_hold_plan_name ,
749:
750: ELSIF p_hold_plan_level ='F' THEN
751: l_rowid := NULL;
752: -- Call to igs_fi_gen_gl.get_formatted_amount formats amounts by rounding off to currency precision
753: igs_fi_person_holds_pkg.insert_row(
754: x_Mode => 'R',
755: x_Rowid => l_rowid,
756: x_person_id => p_person_id,
757: x_hold_plan_name => p_hold_plan_name ,
786:
787:
788: FUNCTION validate_param(p_person_id IN igs_pe_person_v.person_id%TYPE ,
789: p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE ,
790: P_process_start_date IN igs_fi_person_holds.process_start_dT%TYPE ,
791: P_process_end_date IN igs_fi_person_holds.process_end_dT%TYPE ,
792: P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
793: P_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
794: P_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE
787:
788: FUNCTION validate_param(p_person_id IN igs_pe_person_v.person_id%TYPE ,
789: p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE ,
790: P_process_start_date IN igs_fi_person_holds.process_start_dT%TYPE ,
791: P_process_end_date IN igs_fi_person_holds.process_end_dT%TYPE ,
792: P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
793: P_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
794: P_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE
795: )
1092: l_holds_final_balance igs_fi_credits.amount%TYPE := 0;
1093: l_count PLS_INTEGER := 0;
1094: l_msg_str_0 VARCHAR2(1000) :=NULL;
1095: l_msg_str_1 VARCHAR2(1000) :=NULL;
1096: l_process_start_date igs_fi_person_holds.process_start_dT%TYPE;
1097: l_process_end_date igs_fi_person_holds.process_end_dT%TYPE;
1098:
1099: l_process_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
1100:
1093: l_count PLS_INTEGER := 0;
1094: l_msg_str_0 VARCHAR2(1000) :=NULL;
1095: l_msg_str_1 VARCHAR2(1000) :=NULL;
1096: l_process_start_date igs_fi_person_holds.process_start_dT%TYPE;
1097: l_process_end_date igs_fi_person_holds.process_end_dT%TYPE;
1098:
1099: l_process_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
1100:
1101: l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
1628: P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%TYPE,
1629: P_test_run IN VARCHAR2 ,
1630: P_release OUT NOCOPY PLS_INTEGER,
1631: P_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE,
1632: p_release_credit_id IN igs_fi_person_holds.release_credit_id%TYPE := NULL,
1633: p_hold_plan_level IN igs_fi_hold_plan.hold_plan_level%TYPE := NULL
1634: ) RETURN BOOLEAN IS
1635: /***************************************************************
1636: Created By : bayadav
1644: smadathi 28-Aug-2003 Enh Bug 3045007.
1645: pathipat 12-Aug-2003 Enh 3076768 - Auto Release of Holds. Modification as per TD
1646: Added two IN params - p_release_credit_id and p_hold_plan_level
1647: Modified cursor c_hold_type - added join with igs_fi_hold_plan, removed trunc for start and end dates
1648: Removed cursor c_hold_plan, modified TBH calls to igs_fi_person_holds
1649: vvutukur 05-Mar-2003 Enh#2824994.Modified code such that log file output is not shown in horizontal tabular format, instead it
1650: it is shown in linear/logging format.Also used global variables to log the details instead of calling generic function
1651: everytime to get the lookup meaning.
1652: pathipat 26-Feb-2003 Enh:2747341 - Additional Security for Holds
1699: hplan.payment_plan_threshold_pcent payment_plan_threshold_pcent,
1700: c.student_plan_id student_plan_id,
1701: c.last_instlmnt_due_date last_instlmnt_due_date
1702: FROM igs_pe_pers_encumb a,
1703: igs_fi_person_holds c,
1704: igs_fi_hold_plan hplan
1705: WHERE (a.person_id = p_person_id OR p_person_id IS NULL)
1706: AND a.start_dt <= TRUNC(SYSDATE)
1707: AND (a.expiry_dt IS NULL OR TRUNC(SYSDATE) < a.expiry_dt )
1711: AND c.hold_type = a.encumbrance_type
1712: AND (c.hold_plan_name = p_hold_plan_name OR p_hold_plan_name is null)
1713: AND (hplan.hold_plan_level = p_hold_plan_level OR p_hold_plan_level IS NULL);
1714:
1715: CURSOR c_credit_amount(l_process_start_date igs_fi_person_holds.process_start_dt%TYPE ) IS
1716: SELECT crd.credit_id,
1717: crd.amount,
1718: crd.effective_date
1719: FROM igs_fi_credits crd,
1732: WHERE fhpl.hold_plan_name = cp_c_hold_plan_name;
1733:
1734: l_c_fee_type igs_fi_fee_type_all.fee_type%TYPE;
1735:
1736: CURSOR c_total_balance(l_fee_cal_type igs_fi_person_holds.fee_cal_type%TYPE,
1737: l_fee_ci_sequence_number igs_fi_person_holds.fee_ci_sequence_number%TYPE,
1738: l_process_start_dt igs_fi_person_holds.process_start_dt%TYPE,
1739: cp_c_fee_type igs_fi_fee_type_all.fee_type%TYPE
1740: ) IS
1733:
1734: l_c_fee_type igs_fi_fee_type_all.fee_type%TYPE;
1735:
1736: CURSOR c_total_balance(l_fee_cal_type igs_fi_person_holds.fee_cal_type%TYPE,
1737: l_fee_ci_sequence_number igs_fi_person_holds.fee_ci_sequence_number%TYPE,
1738: l_process_start_dt igs_fi_person_holds.process_start_dt%TYPE,
1739: cp_c_fee_type igs_fi_fee_type_all.fee_type%TYPE
1740: ) IS
1741: SELECT invoice_id,
1734: l_c_fee_type igs_fi_fee_type_all.fee_type%TYPE;
1735:
1736: CURSOR c_total_balance(l_fee_cal_type igs_fi_person_holds.fee_cal_type%TYPE,
1737: l_fee_ci_sequence_number igs_fi_person_holds.fee_ci_sequence_number%TYPE,
1738: l_process_start_dt igs_fi_person_holds.process_start_dt%TYPE,
1739: cp_c_fee_type igs_fi_fee_type_all.fee_type%TYPE
1740: ) IS
1741: SELECT invoice_id,
1742: invoice_amount_due,
1758: (TRUNC(SYSDATE) >= TRUNC(start_dt) AND TRUNC(end_dt) IS NULL)
1759: )
1760: );
1761:
1762: -- Get the balance amount from igs_fi_balances instead of from igs_fi_person_holds
1763: -- where the column balance_amount is getting obsoleted.
1764: CURSOR c_bal_amount (cp_person_id igs_pe_pers_encumb.person_id%TYPE,
1765: cp_process_start_dt igs_fi_person_holds.process_start_dt%TYPE) IS
1766: SELECT holds_balance
1761:
1762: -- Get the balance amount from igs_fi_balances instead of from igs_fi_person_holds
1763: -- where the column balance_amount is getting obsoleted.
1764: CURSOR c_bal_amount (cp_person_id igs_pe_pers_encumb.person_id%TYPE,
1765: cp_process_start_dt igs_fi_person_holds.process_start_dt%TYPE) IS
1766: SELECT holds_balance
1767: FROM igs_fi_balances
1768: WHERE party_id = cp_person_id
1769: AND holds_balance IS NOT NULL
1967: FETCH c_igs_fi_hold_plan INTO l_c_fee_type;
1968: CLOSE c_igs_fi_hold_plan;
1969: --fetched fee_type_invoice_amount that stores the value of charges as on the process_start_date when the Holds application process ran and placed a hold on the student.
1970: l_total_charges := l_hold_type_rec.fee_type_invoice_amount ;
1971: --get the outstanding balance for the particular student for the fee types defined in the hold plan in the fee period from the intermediate table IGS_FI_PERSON_HOLDS
1972: OPEN c_total_balance(l_hold_type_rec.fee_cal_type ,
1973: l_hold_type_rec.fee_ci_sequence_number,
1974: l_hold_type_rec.process_start_dt,
1975: l_c_fee_type
2121:
2122: -- Update the value of Release Credit Id in the Holds table if releasing of hold was successful
2123: -- Added as part of Automatic Release of Holds build
2124: -- The Credit Id which caused the hold to be released will be the release_credit_id
2125: igs_fi_person_holds_pkg.update_row ( x_rowid => l_hold_type_rec.row_id,
2126: x_person_id => l_hold_type_rec.person_id,
2127: x_hold_plan_name => l_hold_type_rec.hold_plan_name,
2128: x_hold_type => l_hold_type_rec.hold_type,
2129: x_hold_start_dt => l_hold_type_rec.hold_start_dt,
2260: CURSOR c_person_hold IS
2261: SELECT person_id, person_number
2262: FROM igs_pe_person_base_v per
2263: WHERE EXISTS ( SELECT '1'
2264: FROM igs_fi_person_holds hold
2265: WHERE hold.person_id = per.person_id);
2266:
2267: l_person_group_rec c_person_group%ROWTYPE;
2268: l_person_hold_rec c_person_hold%ROWTYPE;
2267: l_person_group_rec c_person_group%ROWTYPE;
2268: l_person_hold_rec c_person_hold%ROWTYPE;
2269: l_person_number igs_pe_person.person_number%TYPE :=NULL;
2270: l_start_date igs_pe_pers_encumb.start_dt%TYPE;
2271: l_passed_hold_type igs_fi_person_holds.hold_type%TYPE ;
2272: l_hold_plan_name igs_fi_hold_plan.hold_plan_name%TYPE :=NULL;
2273: l_message_name fnd_new_messages.message_name%TYPE :=NULL;
2274: l_message_name_1 fnd_new_messages.message_name%TYPE :=NULL;
2275: l_release_hold VARCHAR2(10) :='Y';
2656: ***************************************************************/
2657:
2658: -- Cursor to check if the student has any active Balance level
2659: -- or Fee Type level.
2660: CURSOR cur_active_holds(cp_person_id igs_fi_person_holds.person_id%TYPE,
2661: cp_hold_plan_level igs_fi_hold_plan.hold_plan_level%TYPE) IS
2662: SELECT 'X'
2663: FROM igs_pe_pers_encumb pe_encmb,
2664: igs_fi_person_holds fi_holds,
2660: CURSOR cur_active_holds(cp_person_id igs_fi_person_holds.person_id%TYPE,
2661: cp_hold_plan_level igs_fi_hold_plan.hold_plan_level%TYPE) IS
2662: SELECT 'X'
2663: FROM igs_pe_pers_encumb pe_encmb,
2664: igs_fi_person_holds fi_holds,
2665: igs_fi_hold_plan hplan
2666: WHERE pe_encmb.person_id = p_person_id
2667: AND pe_encmb.start_dt <= TRUNC(SYSDATE)
2668: AND (pe_encmb.expiry_dt IS NULL OR TRUNC(SYSDATE) < pe_encmb.expiry_dt)
2672: AND fi_holds.hold_plan_name = hplan.hold_plan_name
2673: AND hplan.hold_plan_level = p_hold_plan_level
2674: AND ROWNUM < 2;
2675:
2676: CURSOR cur_credit_number(cp_release_credit_id igs_fi_person_holds.release_credit_id%TYPE) IS
2677: SELECT credit_number
2678: FROM igs_fi_credits_all
2679: WHERE credit_id = cp_release_credit_id;
2680: