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
6 --skharida 26-Jun-2006 Bug#5208136 - Modified bill_the_person procedure , removed the usage of obsoleted columns of the table IGS_FI_INV_INT_ALL
7 --sapanigr 19-Jun-2006 Bug 5134985 - Modified bill_the_person procedure. Date of first bill for student changed from null.
8 --abshriva 8-Jun-2006 Bug 5178298 Invalid Value Message in Log File: Modified the messages
9 --abshriva 12-May-2006 Bug#5217319:- Amount precision change in bill_the_person,create_payplan_bills
10 --sapanigr 24-Feb-2006 Bug#5018036 - Removed cursor cur_person_number in bill_the_person procedure and replaced it by function call.
11 --sapanigr 15-Feb-2006 Bug#5018036 - Modified cursors in create_payplan_bills and bill_the_person. (R12 SQL Repository tuning)
12 --sapanigr 12-Feb-2006 Bug#5018036 - Modified cursor cur_person_number in bill_the_person procedure. (R12 SQL Repository tuning)
13 --sapanigr 23-Nov-2005 Bug#4744481 - Modified bill_the_person procedure.
14 --sapanigr 26-Oct-2005 Bug#4686200 - Modified the bill_the_person procedure.
15 --svuppala 04-Oct-2005 Bug# 3813498 Add Fee Class Description To Report To Sponsor From Billing Extract Process
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.
25 --vvutukur 07-dec-2003 Bug#3146325.Modified bill_the_person and create_payplan_bills.
26 --gmaheswa 12-Nov-2003 Bug 3227107, Address Changes. modified cursor cur_remit_addr as to select only active address records.
27 --smvk 10-Sep-2003 Bug 3045007, Modified the cursor c_clo_dis_pp_dtls in the procedure create_payplan_bills.
28 --smvk 05-Sep-2003 Enh#3045007. Created local procedure create_payplan_bills and its call in billing_extract
29 --schodava 25-Aug-2003 Bug #3021943 - Cut off Date parameter issue.
30 --shtatiko 21-AUG-2003 Bug# 3106262, modified bill_the_person.
31 --vvutukur 18-ul-2003 Enh#3038511.FICR106 Build. Modified procedure bill_the_person.
32 --pathipat 23-Jun-2003 Bug: 3018104 - Impact of igs_pe_persid_group change
33 -- Modified cur_person_id_group
34 --pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build - Modified billing_extract()
35 -- Added validation for manage_account - call to chk_manage_account()
36 --shtatiko 12-DEC-2002 Enh Bug#, 2584741 (Deposits), Modified bill_the_person to include Deposit records in report.
37 --jbegum 24-Sep-02 Bug#2564643 Removed the parameter p_n_subaccount_id
38 -- from the list parameters passed in call of procedure
39 -- billing_extract.
40 -- Obsoleted the local function get_include_in_bill.
41 -- Modified procedure bill_the_person.
42 -- Removed cursor cur_sub_account_id and validation related to it.
43 --vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
44 --vchappid 06-Jun-2002 Bug# 2349394, incase the fund code is sponsor then credit type description is passed else the
45 -- bill description from the fund master is passed
46 --smadathi 31-May-2002 Bug 2349394. Procedure bill_the_person modified.
47 --vchappid 15-May-2002 Bug# 2345299, Message IGS_FI_PRS_OR_PRSIDGRP is replaced with IGS_PRS_PRSIDGRP_NULL as the
48 -- message is not as per the text in the DLD
49 --vchappid 07-May-2002 Bug# 2347657, In the cases where the bill is getting generated for the first time, Start Date
50 -- of the bill is hard coded to '01-01-1200'. Removed the hard coded date in this case and in such
51 -- cases Start Date of the Bill would be Null, and the Bill would be Generated till the cut off
52 -- date provided to the process
53 --vchappid 29-Apr-2002 Bug# 2347609, Changed the cursor 'cur_remitt_addr' which is having additional where clause
54 -- removed clause 'person_id = p_n_person_id'
55 -- Bug# 2337820, removed the correspondance flag reference in the cursors 'cur_bill_to_addr',
56 -- 'cur_remit_addr'
57 -- Bug 2345299, removed the fnd_file.put_line call since it was not writing into the log file of
58 -- the report, instead added to the igs_ge_msg.stack to add into the stack and in the report
59 -- fnd_message.get is used to unwound the stack
60 --sarakshi 3-Apr-2002 added code according to sfcr018,bug:2293676, 'vchappid' incorporated review comments
61 --sarakshi 27-Feb-2002 bug:2238362, changed the view igs_pe_person_v to igs_fi_parties_v and used the
62 -- function igs_fi_gen_007.validate_person to validate person
63 --jbegum 20-Feb-02 As part Enh bug#2228910
64 -- Removed source_transaction_id column from call to
65 -- IGS_FI_INV_INT_PKG.update_row
66 -- Removed the source_transaction_id from Cursor CUR_CHARGE_TRANS_UPD
67 --jbegum 09-Feb-02 As part of Enh bug # 2201081
68 -- Added the local function get_include_in_bill
69 --maseghal 17-Jan-2002 ENH # 2170429
70 -- Obsoletion of SPONSOR_CD from Cursor CUR_CHARGE_TRANS_UPD and
71 -- UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
72 --smadathi 08-Oct-2001 Balance_flag references Removed from bill_the_person Procedure.
73 -- This is as per enhancement bug no. 2030448
74
75 -------------------------------------------------------------------
76 --
77 -- Procedure billing_extract generates the Bill Number and consolidates
78 -- the related Billing information into the extract tables.
79 -- o The input parameters to this process identify the Person
80 -- for which the Billing needs to be done.
81 -- o It also identifies the period, in terms of Cut Off date, for which
82 -- the Billing is to be carried out. All Outstanding Charges and any new
83 -- Credits against the Person, since the last Bill was generated,
84 -- are picked for creating the new Bill.
85 -- o The user specifies the Due Date, Remittance Address and the Bill To
86 -- Address as billing information. A Bill Number is generated by the
87 -- process and stored as reference information against the Transaction
88 -- records (Charges and Credits) which have been identified to be
89 -- included in the Bill.
90 -- o If any of the business rules fail then the reason is recorded as a message.
91 -- The Process Logs a message and exits by returning a Status (P_C_STATUS) of
92 -- 'FALSE' to the calling Program.
93 --
94 e_resource_busy EXCEPTION;
95 PRAGMA EXCEPTION_INIT(e_resource_busy,-0054);
96
97 l_b_txn_exist BOOLEAN := FALSE;
98
99 -- Procedure to create payment plan related billing records. forward declaration
100 PROCEDURE create_payplan_bills (p_n_bill_id IN NUMBER, p_n_person_id IN NUMBER, p_d_cut_off_date IN DATE);
101
102 PROCEDURE billing_extract
103 (
104 p_n_prsid_grp_id IN NUMBER,
105 p_n_person_id IN NUMBER,
106 p_c_test_mode IN VARCHAR2,
107 p_d_cutoff_dt IN DATE,
108 p_d_due_dt IN DATE,
109 p_n_remit_prty_site_id IN NUMBER,
110 p_c_site_usg_type_cd_1 IN VARCHAR2,
111 p_c_site_usg_type_cd_2 IN VARCHAR2,
112 p_c_site_usg_type_cd_3 IN VARCHAR2,
113 p_c_org_id IN VARCHAR2,
114 p_c_status OUT NOCOPY VARCHAR2
115 ) AS
116 ------------------------------------------------------------------
117 --Change History:
118 --Who When What
119 --abshriva 8-Jun-2006 Bug 5178298 Invalid Value Message in Log File: Added cursor cur_usr_profl_name,
120 -- Modified cursor cur_remit_addr
121 --sapanigr 15-Feb-2006 Bug# 5018036 Cursor cur_remit_addr now uses igs_or_inst_org_base_v, hz_party_sites and
122 -- igs_pe_hz_pty_sites igsps instead of igs_or_institution_v orv, igs_pe_addr_v pav
123 --svuppala 04-Oct-2005 Bug# 3813498 Add Fee Class Description To Report To Sponsor From Billing Extract Process
124 -- In PROCEDURE bill_the_person, added new cursor cur_fee_type and added fee_class_meaning
125 -- incase of Sponsor system fee type.
126 --pathipat 21-Jul-2004 Bug 3778782 - Modified the way bill_the_person is invoked
127 --pathipat 06-May-2004 Bug# 3578249 - Modified cursor cur_bill_to_addr_usage
128 --vvutukur 20-Jan-2004 Bug#3348787.Modified cursor cur_bill_to_addr_usage.
129 --schodava 25-Aug-2003 Bug #3021943 - Cut off Date parameter issue.
130 -- Modified cursor CUR_PERSON_IDS
131 --pathipat 23-Jun-2003 Bug: 3018104 - Impact of igs_pe_persid_group change
132 -- Modified cur_person_id_group - replaced igs_pe_persid_group_v
133 -- with igs_pe_persid_group
134 --gmaheswa 12-Nov-2003 Bug: 3227107 - Modified cur_remit_addr cursor to check active status of the records.
135 ------------------------------------------------------------------
136 --
137 -- Parameter Explanation :
138 --
139 -- p_n_prsid_grp_id -> The Person ID Group, the member of which are to be Billed.
140 -- p_n_person_id -> The Person ID who has to be Billed.
141 -- p_c_test_mode -> Whether the Billing Process is run in test mode or not. Default - Yes.
142 -- p_d_cutoff_dt -> The Cut Off date for Billing.
143 -- p_d_due_dt -> The Date by which any pending charges identified in this Bill are due.
144 -- p_n_remit_prty_site_id -> The Remittance Address for the Bill. The Party Site ID is recorded here.
145 -- p_c_site_usg_type_cd_1 -> The Bill is to be sent to the Physical Addresses of the Billed Person having
146 -- this address usage. The Lookup Code corresponding to the Usage is recorded here.
147 -- p_c_site_usg_type_cd_2 -> The Bill is to be sent to the Physical Addresses of the Billed Person having
148 -- this address usage. The Lookup Code corresponding to the Usage is recorded here.
149 -- p_c_site_usg_type_cd_3 -> The Bill is to be sent to the Physical Addresses of the Billed Person having
150 -- this address usage. The Lookup Code corresponding to the Usage is recorded here.
151 -- p_c_org_id -> Context Organisation ID.
152 -- p_c_status -> The Status with which the Package completes the processing.
153 -- Valid return values are 'TRUE' or 'FALSE'.
154 --
155 --
156 -- Cursor to find if the Person ID Group is valid.
157 --
158 CURSOR cur_person_id_group (
159 cp_n_prsid_grp_id IN NUMBER
160 ) IS
161 SELECT 'Y' found_person_id_group
162 FROM igs_pe_persid_group
163 WHERE group_id = cp_n_prsid_grp_id
164 AND closed_ind = 'N'
165 AND TRUNC(creation_date) <= TRUNC(SYSDATE);
166
167 -- Bug#2564643 Removed cursor cur_sub_account_id
168
169 --
170 -- Cursor to find if the Remittance Address is valid and if the Remit Address specified has an active Usage setting same as
171 -- the Usage which has been set in the Profile 'IGS: Remit To Address Usage'.
172 -- Added as part of the Bug 5178298
173 --
174 CURSOR cur_remit_addr (
175 cp_n_remit_prty_site_id IN NUMBER,
176 cp_d_due_dt IN DATE,
177 cp_profile_value IN VARCHAR2
178 ) IS
179 SELECT 'Y' found_remit_addr
180 FROM igs_or_inst_org_base_v oi,
181 hz_party_sites ps,
182 igs_pe_hz_pty_sites igsps,
183 igs_pe_partysiteuse_v ppv
184 WHERE ps.party_site_id = cp_n_remit_prty_site_id
185 AND ps.party_site_id = ppv.party_site_id
186 AND oi.oi_local_institution_ind = 'Y'
187 AND oi.party_id = ps.party_id
188 AND ps.party_site_id = igsps.party_site_id (+)
189 AND oi.inst_org_ind = 'I'
190 AND ppv.site_use_type = cp_profile_value
191 AND ppv.active = 'A'
192 AND (ps.status = 'A'
193 AND ((TRUNC(igsps.start_date) <= TRUNC(SYSDATE) AND TRUNC(igsps.start_date) <= TRUNC(cp_d_due_dt)) OR igsps.start_date IS NULL)
194 AND ((TRUNC(igsps.end_date) >= TRUNC(SYSDATE) AND TRUNC(igsps.end_date) >= TRUNC(cp_d_due_dt)) OR igsps.end_date IS NULL));
195
196 -- Cursor to find if any of the Bill To Address Usage 1, 2, 3 is valid.
197 --
198 CURSOR cur_bill_to_addr_usage (
199 cp_c_site_usg_type_cd IN VARCHAR2
200 ) IS
201 SELECT 'Y' found_bill_to_addr_usage
202 FROM fnd_lookup_values
203 WHERE lookup_type = 'PARTY_SITE_USE_CODE'
204 AND lookup_code = cp_c_site_usg_type_cd
205 AND view_application_id = 222
206 AND security_group_id = 0
207 AND language = USERENV('LANG')
208 AND enabled_flag = 'Y'
209 AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,TRUNC(SYSDATE)) AND NVL(end_date_active,TRUNC(SYSDATE));
210
211 --
212 -- Cursor to find the Person IDs that are part of the Person ID Group.
213 --
214 CURSOR cur_person_ids (
215 cp_n_prsid_grp_id IN NUMBER
216 ) IS
217 SELECT person_id
218 FROM igs_pe_prsid_grp_mem
219 WHERE group_id = cp_n_prsid_grp_id
220 AND NVL(start_date,SYSDATE) <= SYSDATE
221 AND NVL(end_date,SYSDATE) >= SYSDATE;
222
223 -- Cursor to find User Profile option name
224 -- Added as part of the Bug 3804379
225 --
226 CURSOR cur_usr_profl_name IS
227 SELECT user_profile_option_name
228 FROM fnd_profile_options_vl
229 WHERE (profile_option_name LIKE 'IGS_REMIT_TO_ADD_USG');
230 --
231 rec_cur_person_id_group cur_person_id_group%ROWTYPE;
232 rec_cur_remit_addr cur_remit_addr%ROWTYPE;
233 rec_cur_bill_to_addr_usage cur_bill_to_addr_usage%ROWTYPE;
234 --
235
236 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
237 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
238 l_v_usr_profl_name fnd_profile_options_vl.user_profile_option_name%TYPE := NULL;
239 l_v_bill_usg_lkp_type fnd_lookup_values.lookup_type%TYPE := 'PARTY_SITE_USE_CODE';
240 l_n_use_num NUMBER;
241
242
243 PROCEDURE bill_the_person (
244 p_n_person_id IN NUMBER
245 ) IS
246 ------------------------------------------------------------------
247 --Change History:
248 --Who When What
249 --skharida 26-Jun-2006 Bug# 5208136 - Removed the usage of obsoleted columns of the table IGS_FI_INV_INT_ALL
250 --sapanigr 19-Jun-2006 Bug 5134985 - First bill for a student modified to show start date as earlier of first charge or credit date.
251 -- Earlier this field was being left null.
252 --abshriva 8-Jun-2006 Bug 5178298 Invalid Value Message in Log File: Added proper messages
253 --
254 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
255 --sapanigr 24-Feb-2006 Bug#5018036 - Cursor cur_person_number removed and replaced by call to function igs_fi_gen_008.get_party_number.
256 --sapanigr 15-Feb-2006 Bug#5018036 - Cursor cur_bill_to_addr was a union of three select stmts. This is
257 -- changed to one select statement by using IN function to reduce share memory usage.
258 --sapanigr 12-Feb-2006 Bug#5018036 - Cursor cur_person_number now queries hz_parties instead of igs_fi_parties_v. (R12 SQL Repository tuning)
259 --sapanigr 23-Nov-2005 Bug#4744481 - Cursor cur_bill_to_addr modifed to check for active address usage.
260 --sapanigr 26-Oct-2005 Bug#4686200 - Modified the Cursor cur_bill_to_addr to check for active address.
261 --svuppala 04-Oct-2005 Bug# 3813498 Add Fee Class Description To Report To Sponsor From Billing Extract Process
262 -- Added new cursor cur_fee_type and added fee_class_meaning
263 -- incase of Sponsor system fee type.
264 --agairola 29-Aug-2005 Tuition Waiver Build: Modified changes as per TD
265 --uudayapr 01-Aug-2005 Bug#2767636 Modified the Cursor cur_bill_to_addr to add the Start and End Date
266 --pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
267 --pathipat 21-Jul-2004 Bug 3778782 - Raised exception instead of RETURN if Bill To address usage is not defined for a person.
268 -- Removed addition to stack for Bill To address validation failure and Planned Credits validation failure.
269 --vvutukur 07-dec-2003 Bug#3146325.Removed cursor cur_transactions_found and its usage. Added logic to
270 -- rollback the bill txns if no records found for processing.This is done using
271 -- boolean variable.
272 --shtatiko 21-AUG-2003 Bug# 3106262, Added reversal_gl_date column to igs_fi_inv_int_pkg.update_row call
273 -- and added gl_date, gl_posted_date and posting_control_id columns to igs_fi_cr_activities_pkg calls
274 --vvutukur 18-ul-2003 Enh#3038511.FICR106 Build. Modified cursor cur_planned_crd to to exclude the
275 -- planned credits for which the Award Year status is not OPEN.
276 --shtatiko 12-DEC-2002 Enh Bug# 2584741, Added c_bill_deposits and modified code so that report will include
277 -- Deposit records in report.
278 --jbegum 24-Sep-02 Bug#2564643
279 -- Modified procedure bill_the_person as follows:
280 -- Removed call to local function get_include_in_bill.
281 -- Removed the local variable l_psa_exist.
282 -- Removed cursor cur_sub_accts , which finds the Sub Account details for a given Sub Account.
283 -- Modified the following cursors by removing parameter subaccount_id to cursor
284 -- and usage of column subaccount_id in where clause.Also modified code related
285 -- to management of all these cursors accordingly.
286 -- cur_start_date,cur_charge_trans,cur_charge_trans_upd,cur_credit_trans,
287 -- cur_opening_balance,cur_charges_total,cur_credits_total,cur_transactions_found,
288 -- and cur_planned_crd.
289 -- Removed code assigning value to token SUB_ACCOUNT_NAME.
290 -- Removed the parameter p_subaccount_id in call to igs_fi_gen_001.finp_get_total_planned_credits
291 -- Removed the parameter subaccount_id from relevant TBH calls.
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',
301 -- 'cur_remit_addr'
302 -- Bug 2345299, removed the fnd_file.put_line call since it was not writing into the log file of
303 -- the report, instead added to the igs_ge_msg.stack to add into the stack and in the report
304 -- fnd_message.get is used to unwound the stack
305 --sarakshi 3-Apr-2002 added code according to sfcr018,bug:2293676
306 --jbegum 9-Feb-02 In the following cursors the join with the IGS_FI_PARTY_SUBACTS table
307 -- was removed as part of Enh bug # 2201081
308 -- CUR_CHARGE_TRANS , CUR_CHARGE_TRANS_UPD ,CUR_CHARGES_TOTAL,CUR_TRANSACTIONS_FOUND
309 --sarakshi 21-jan-2002 removed fee_cal_type,fee_ci_sequence_number from the where clause of cursor
310 -- cur_charge_trans,cur_charges_total,cur_transactions_found and
311 -- cur_charge_trans_upd, bug:2175865
312 --smadathi 08-Oct-2001 Balance_flag references Removed from igs_fi_inv_int_pkg.update_row call and
313 -- cur_charge_trans_upd cursor and optional_fee_flag added. This is as per enhancement bug no. 2030448
314 -------------------------------------------------------------------
315
316 -- Bug#2564643 Removed cursor cur_sub_accts
317
318 -- Bug#2564643 Modified cursor defination of cur_start_date
319 -- Removed the parameter subaccount_id to cursor and usage of column
320 -- subaccount_id in where clause.
321 --
322 -- Cursor to find the Start Date for the current Billing.
323 --
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
333 -- subaccount_id in where clause.
334 --
335 -- Cursor to find the Charge Transactions made by the Person.
336 -- The Charge transactions are identified on the basis of the Transaction Date.
337 -- The Effective Date is not to be considered for this purpose.
338 --
339 CURSOR cur_charge_trans (
340 cp_n_person_id IN NUMBER,
341 cp_d_start_date IN DATE,
342 cp_d_cutoff_dt IN DATE
343 ) IS
344 SELECT inv.invoice_id invoice_id,
345 inv.invoice_number invoice_number,
346 inv.fee_type fee_type,
347 inv.invoice_creation_date invoice_creation_date,
348 inv.invoice_desc invoice_desc,
349 NVL (inv.invoice_amount, 0) invoice_amount
350 FROM igs_fi_inv_int inv
351 WHERE inv.person_id = cp_n_person_id
352 AND (cp_d_start_date IS NULL OR (TRUNC(inv.invoice_creation_date) >= TRUNC(cp_d_start_date)))
353 AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_cutoff_dt)
354 AND inv.bill_date IS NULL
355 FOR UPDATE NOWAIT;
356
357 -- Bug#2564643 Modified cursor defination of cur_charge_trans_upd
358 -- Removed the parameter subaccount_id to cursor and usage of column
359 -- subaccount_id in where clause.
360 --
361 -- Cursor to find the Charge Transactions made by the Person .
362 -- The Charge transactions are identified on the basis of the Transaction Date.
363 -- The Effective Date is not to be considered for this purpose.
364 -- **
365 -- ** This cursor is meant for updation of the values in igs_fi_inv_int table **
366 -- ** Balance_flag reference is removed from cur_charge_trans_upd
367 --
368
369 --Change History
370 --Who When What
371 --skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the cursor
372 --jbegum 20 Feb 02 Enh bug#2228910
373 -- Removed the source_transaction_id from Cursor CUR_CHARGE_TRANS_UPD
374 --masehgal 17-Jan-2002 ENH # 2170429
375 -- Obsoletion of SPONSOR_CD from Cursor CUR_CHARGE_TRANS_UPD
376
377 CURSOR cur_charge_trans_upd (
378 cp_n_person_id IN NUMBER,
379 cp_d_start_date IN DATE,
380 cp_d_cutoff_dt IN DATE
381 ) IS
382 SELECT inv.row_id row_id,
383 inv.invoice_id invoice_id,
384 inv.person_id person_id,
385 inv.fee_type fee_type,
386 inv.fee_cat fee_cat,
387 inv.fee_cal_type fee_cal_type,
388 inv.fee_ci_sequence_number fee_ci_sequence_number,
389 inv.course_cd course_cd,
390 inv.attendance_mode attendance_mode,
391 inv.attendance_type attendance_type,
392 inv.invoice_amount_due invoice_amount_due,
393 inv.invoice_creation_date invoice_creation_date,
394 inv.invoice_desc invoice_desc,
395 inv.transaction_type transaction_type,
396 inv.currency_cd currency_cd,
397 inv.exchange_rate exchange_rate,
398 inv.status status,
399 inv.attribute_category attribute_category,
400 inv.attribute1 attribute1,
401 inv.attribute2 attribute2,
402 inv.attribute3 attribute3,
403 inv.attribute4 attribute4,
404 inv.attribute5 attribute5,
405 inv.attribute6 attribute6,
406 inv.attribute7 attribute7,
407 inv.attribute8 attribute8,
408 inv.attribute9 attribute9,
409 inv.attribute10 attribute10,
410 inv.org_id org_id,
411 inv.invoice_amount invoice_amount,
412 inv.bill_id bill_id,
413 inv.bill_number bill_number,
414 inv.bill_date bill_date,
415 inv.waiver_flag waiver_flag,
416 inv.waiver_reason waiver_reason,
417 inv.effective_date effective_date,
418 inv.invoice_number invoice_number,
419 inv.bill_payment_due_date bill_payment_due_date,
420 inv.last_update_date last_update_date,
421 inv.last_updated_by last_updated_by,
422 inv.creation_date creation_date,
423 inv.created_by created_by,
424 inv.last_update_login last_update_login,
425 inv.request_id request_id,
426 inv.program_application_id program_application_id,
427 inv.program_id program_id,
428 inv.program_update_date program_update_date,
429 inv.optional_fee_flag optional_fee_flag,
430 inv.reversal_gl_date reversal_gl_date,
431 inv.tax_year_code tax_year_code,
432 inv.waiver_name
433 FROM igs_fi_inv_int inv
434 WHERE inv.person_id = cp_n_person_id
435 AND (cp_d_start_date IS NULL OR (TRUNC(inv.invoice_creation_date) >= TRUNC(cp_d_start_date)))
436 AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_cutoff_dt)
437 AND inv.bill_date IS NULL;
438
439 -- Bug#2564643 Modified cursor defination of cur_credit_trans
440 -- Removed the parameter subaccount_id to cursor and usage of column
441 -- subaccount_id in where clause.
442 --
443 -- Cursor to find the Credit Transactions (Cleared Credit and Reversed Credit) made by the Person.
444 -- The Credit transactions are identified on the basis of the Effective Date.
445 -- The Transaction Date is not to be considered for this purpose.
446 --
447 CURSOR cur_credit_trans (
448 cp_n_person_id IN NUMBER,
449 cp_d_start_date IN DATE,
450 cp_d_cutoff_dt IN DATE
451 ) IS
452 SELECT credit_activity_id,
453 credit_number,
454 effective_date,
455 credit_type,
456 description,
457 (-amount) amount -- Negate the amount since this is a Credit Transaction.
458 FROM igs_fi_crdt_trnsctns
459 WHERE person_id = cp_n_person_id
460 AND (cp_d_start_date IS NULL OR (TRUNC(effective_date) >= TRUNC(cp_d_start_date)))
461 AND TRUNC(effective_date) <= TRUNC(cp_d_cutoff_dt)
462 AND credit_activity_id IN (SELECT credit_activity_id
463 FROM igs_fi_cr_activities
464 WHERE bill_date IS NULL)
465 FOR UPDATE OF credit_activity_id NOWAIT;
466
467 --
468 -- Cursor to find the Bill To Address from the Party Site IDs.
469 --
470
471 CURSOR cur_bill_to_addr (
472 cp_c_site_usg_type_cd_1 IN VARCHAR2,
473 cp_c_site_usg_type_cd_2 IN VARCHAR2,
474 cp_c_site_usg_type_cd_3 IN VARCHAR2
475 ) IS
476 SELECT pav.addr_line_1,
477 pav.addr_line_2,
478 pav.addr_line_3,
479 pav.addr_line_4,
480 pav.city,
481 pav.state,
482 pav.province,
483 pav.county,
484 pav.country,
485 pav.postal_code,
486 pav.delivery_point_code
487 FROM igs_pe_addr_v pav,
488 igs_pe_partysiteuse_v ppv
489 WHERE pav.person_id = p_n_person_id
490 AND pav.party_site_id = ppv.party_site_id
491 AND ppv.site_use_type IN (cp_c_site_usg_type_cd_1,cp_c_site_usg_type_cd_2,cp_c_site_usg_type_cd_3)
492 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(pav.start_dt,SYSDATE))
493 AND TRUNC(NVL(pav.end_dt,SYSDATE))
494 AND pav.status = 'A'
495 AND ppv.active = 'A';
496
497 --
498 -- Cursor to find the Remittance Address from the Remittance Party Site ID.
499 --
500 CURSOR cur_remitt_addr (
501 cp_n_remit_prty_site_id IN NUMBER
502 ) IS
503 SELECT addr_line_1,
504 addr_line_2,
505 addr_line_3,
506 addr_line_4,
507 city,
508 state,
509 province,
510 county,
511 country,
512 postal_code,
513 delivery_point_code
514 FROM igs_pe_addr_v
515 WHERE party_site_id = cp_n_remit_prty_site_id;
516
517 -- Bug#2564643 Modified cursor defination of cur_opening_balance
518 -- Removed the parameter subaccount_id to cursor and usage of column
519 -- subaccount_id in where clause.
520 --
521 -- Cursor to find the Opening Balance from the Closing Balance of the Previous Bill.
522 --
523 CURSOR cur_opening_balance (
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
533 -- Removed the parameter subaccount_id to cursor and usage of column
534 -- subaccount_id in where clause.
535 --
536 -- Cursor to find the Charge Amount made by the Person .
537 -- The Charge transactions are identified on the basis of the Transaction Date.
538 -- The Effective Date is not to be considered for this purpose.
539 --
540 CURSOR cur_charges_total (
541 cp_n_person_id IN NUMBER,
542 cp_d_start_date IN DATE,
543 cp_d_cutoff_dt IN DATE
544 ) IS
545 SELECT NVL (SUM (inv.invoice_amount), 0) total_charge_amount
546 FROM igs_fi_inv_int inv
547 WHERE inv.person_id = cp_n_person_id
548 AND (cp_d_start_date IS NULL OR (TRUNC(inv.invoice_creation_date) >= TRUNC(cp_d_start_date)))
549 AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_cutoff_dt)
550 AND inv.bill_date IS NULL;
551
552 -- Bug#2564643 Modified cursor defination of cur_credits_total
553 -- Removed the parameter subaccount_id to cursor and usage of column
554 -- subaccount_id in where clause.
555 --
556 -- Cursor to find the Credit Amount (Cleared Credit and Reversed Credit) made by the Person .
557 -- The Credit transactions are identified on the basis of the Effective Date.
558 -- The Transaction Date is not to be considered for this purpose.
559 --
560 CURSOR cur_credits_total (
561 cp_n_person_id IN NUMBER,
562 cp_d_start_date IN DATE,
563 cp_d_cutoff_dt IN DATE
564 ) IS
565 SELECT NVL (SUM (amount), 0) total_credit_amount
566 FROM igs_fi_crdt_trnsctns
567 WHERE person_id = cp_n_person_id
568 AND (cp_d_start_date IS NULL OR (TRUNC(effective_date) >= TRUNC(cp_d_start_date)))
569 AND TRUNC(effective_date) <= TRUNC(cp_d_cutoff_dt)
570 AND credit_activity_id IN (SELECT credit_activity_id
571 FROM igs_fi_cr_activities
572 WHERE bill_date IS NULL);
573
574 --
575 -- Cursor to find the Credit History records for a Person.
576 --
577 CURSOR cur_credit_hist (
578 cp_n_credit_act_id IN NUMBER
579 ) IS
580 SELECT ca.rowid,
581 ca.*
582 FROM igs_fi_cr_activities ca
583 WHERE credit_activity_id = cp_n_credit_act_id;
584 --
585 l_d_start_date DATE;
586 l_d_min_inv_dt DATE;
587 l_d_min_crd_dt DATE;
588 l_n_opening_balance NUMBER;
589 l_n_closing_balance NUMBER;
590 l_n_bill_id_seq NUMBER;
591 l_r_bill_row_id VARCHAR2(25);
592 l_r_bill_trans_row_id VARCHAR2(25);
593 l_r_bill_addr_row_id VARCHAR2(25);
594 l_n_transaction_id NUMBER;
595 l_n_bill_addr_id NUMBER;
596 l_person_number hz_parties.party_number%TYPE;
597 rec_cur_charges_total cur_charges_total%ROWTYPE;
598 rec_cur_credits_total cur_credits_total%ROWTYPE;
599 rec_cur_bill_to_addr cur_bill_to_addr%ROWTYPE;
600 rec_cur_remitt_addr cur_remitt_addr%ROWTYPE;
601 rec_cur_credit_hist cur_credit_hist%ROWTYPE;
602
603 --
604 -- Bug#2564643 Modified cursor defination of cur_planned_crd
605 -- Removed the parameter subaccount_id to cursor and usage of column
606 -- subaccount_id in where clause.
607
608 CURSOR cur_planned_crd(cp_person_id igf_ap_fa_base_rec.person_id%TYPE,
609 cp_cutoff_dt igf_aw_awd_disb.disb_date%TYPE) IS
610 SELECT
611 disb.award_id,
612 disb.disb_num,
613 disb.disb_date,
614 fmast.fund_id,
615 disb.ld_cal_type,
616 disb.ld_sequence_number,
617 disb.disb_net_amt,
618 fmast.bill_desc,
619 cr.description,
620 fcat.fed_fund_code
621 FROM
622 igf_aw_awd_disb disb,
623 igf_aw_award awd,
624 igf_aw_fund_mast fmast,
625 igf_aw_fund_cat fcat,
626 igf_ap_fa_base_rec base,
627 igs_fi_cr_types cr,
628 igf_ap_batch_aw_map bm
629 WHERE disb.award_id = awd.award_id
630 AND awd.fund_id = fmast.fund_id
631 AND awd.base_id = base.base_id
632 AND fmast.credit_type_id = cr.credit_type_id (+)
633 AND fmast.fund_code = fcat.fund_code
634 AND fmast.ci_cal_type = bm.ci_cal_type
635 AND fmast.ci_sequence_number = bm.ci_sequence_number
636 AND awd.award_status ='ACCEPTED'
637 AND disb.trans_type = 'P'
638 AND disb.show_on_bill = 'Y'
639 AND base.person_id = cp_person_id
640 AND TRUNC(disb.disb_date) <= TRUNC(cp_cutoff_dt)
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
650 SELECT
651 cra.credit_activity_id
652 FROM
653 igs_fi_cr_activities cra,
654 igs_fi_credits cr,
655 igs_fi_cr_types crt
656 WHERE
657 cra.status = 'CLEARED'
658 AND cr.party_id = cp_n_person_id
659 AND cra.credit_id = cr.credit_id
660 AND cr.credit_type_id = crt.credit_type_id
661 AND crt.credit_class IN ('ENRDEPOSIT', 'OTHDEPOSIT')
662 AND (cp_d_start_date IS NULL
663 OR (TRUNC(cr.effective_date) >= TRUNC(cp_d_start_date) ))
664 AND TRUNC(cr.effective_date) <= TRUNC(cp_d_cutoff_dt)
665 AND cra.bill_id IS NULL
666 AND (
667 (cp_c_rec_installed = 'Y'
668 AND cra.dr_gl_ccid IS NOT NULL
669 AND cra.cr_gl_ccid IS NOT NULL
670 )
671 OR
672 (cp_c_rec_installed = 'N'
673 AND cra.dr_account_cd IS NOT NULL
674 AND cra.cr_account_cd IS NOT NULL
675 )
676 )
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);
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
694 SELECT s_fee_type, fee_class
695 FROM igs_fi_fee_type ft
696 WHERE fee_type = cp_fee_type;
697
698 l_v_s_fee_type igs_fi_fee_type.s_fee_type%TYPE;
699 l_v_fee_class igs_fi_fee_type.fee_class%TYPE;
700 l_v_fee_class_meaning igs_lookup_values.meaning%TYPE;
701
702 CURSOR cur_min_inv_dt IS
703 SELECT MIN(invoice_creation_date)
704 FROM igs_fi_inv_int_all
705 WHERE person_id = p_n_person_id;
706
707 CURSOR cur_min_crd_dt IS
708 SELECT MIN(effective_date)
709 FROM igs_fi_credits_all
710 WHERE party_id = p_n_person_id;
711
712 BEGIN
713
714 --Set the flag l_b_txn_exist to FALSE for each person being processed.
715 --This flag checks whether atleast one transaction is liable for billing or not.
716 l_b_txn_exist := FALSE;
717
718 --
719 -- 6. Identify the Bill To Address.
720 -- Check if the Person has got Bill To Address Usage Types.
721 --
722 OPEN cur_bill_to_addr (
723 p_c_site_usg_type_cd_1,
724 p_c_site_usg_type_cd_2,
725 p_c_site_usg_type_cd_3
726 );
727 FETCH cur_bill_to_addr INTO rec_cur_bill_to_addr;
728 IF (cur_bill_to_addr%NOTFOUND) THEN
729 CLOSE cur_bill_to_addr;
730 fnd_message.set_name ('IGS', 'IGS_FI_NO_BILL_TO_ADDR');
731 app_exception.raise_exception; -- Raise exception. (pathipat)
732 END IF;
733 CLOSE cur_bill_to_addr;
734
735 --added as a part of bug:2293676
736 --Fetching the value of planned credits ind from igs_fi_control,
737 --if the out parameter returns any value then return
738 l_pln_crd_setup:=igs_fi_gen_001.finp_get_planned_credits_ind(l_message_name);
739 IF l_message_name IS NOT NULL THEN
740 fnd_message.set_name('IGS',l_message_name);
741 -- Raise the exception, message is added to stack in the invoking place in billing_extract()
742 app_exception.raise_exception;
743 END IF;
744
745 --
746 -- 4. Identify the Start Date for the Billing Period.
747 -- o The Start date is the next day of the Cut Off Date of the most recent Bill
748 -- that was generated for the identified Person .
749 -- o If this is the first Bill being generated for the Person
750 -- then there would no start date.
751 -- o All the transaction till the Cut Off Date would be taken for Billing.
752 --
753
754 -- Bug#2564643 Removed call to local function get_include_in_bill.
755 -- Also removed the IF condition which executed the following code in the
756 -- procedure bill_the_person if the function get_include_in_bill returned
757 -- 1 else the message IGS_FI_NOT_BLLBL is logged .
758
759
760 OPEN cur_start_date (
761 p_n_person_id
762 );
763 FETCH cur_start_date INTO l_d_start_date;
764 IF (cur_start_date%NOTFOUND) THEN
765 CLOSE cur_start_date;
766 l_d_start_date := NULL;
767 ELSE
768 CLOSE cur_start_date;
769 IF (l_d_start_date > p_d_cutoff_dt) THEN
770 fnd_message.set_name ('IGS', 'IGS_FI_CUTOFF_LSTDT');
771 -- Bug#2564643 Removed code assigning value to token SUB_ACCOUNT_NAME .
772 -- Bug#5018036 cur_person_number replaced by call to igs_fi_gen_008.get_party_number
773 l_person_number := igs_fi_gen_008.get_party_number(p_n_person_id);
774 fnd_message.set_token ('PERSON_NUMBER', l_person_number);
775 igs_ge_msg_stack.add;
776 END IF;
777 END IF;
778 --
779 -- Compute the following steps only if the Start Date is less than or equal to Cut Off Date
780 --
781 IF (NVL (l_d_start_date, p_d_cutoff_dt) <= p_d_cutoff_dt) THEN
782
783 --Create a savepoint for bill transactions.
784 SAVEPOINT s_bill_txn;
785
786 --
787 -- 8. Generate the Bill Number / Closing Balance.
788 --
789 IF (l_d_start_date IS NULL) THEN
790 l_n_opening_balance := 0;
791
792 -- If start date is null then assign it the earlier of first charge or credit date.
793 OPEN cur_min_inv_dt;
794 FETCH cur_min_inv_dt INTO l_d_min_inv_dt;
795 CLOSE cur_min_inv_dt;
796
797 OPEN cur_min_crd_dt;
798 FETCH cur_min_crd_dt INTO l_d_min_crd_dt;
799 CLOSE cur_min_crd_dt;
800
801 IF (nvl(l_d_min_inv_dt,SYSDATE)<nvl(l_d_min_crd_dt,SYSDATE)) THEN
802 l_d_start_date := l_d_min_inv_dt;
803 ELSE
804 l_d_start_date := l_d_min_crd_dt;
805 END IF;
806
807 ELSE
808 OPEN cur_opening_balance (
809 p_n_person_id,
810 l_d_start_date
811 );
812 FETCH cur_opening_balance INTO l_n_opening_balance;
813 IF (l_n_opening_balance IS NULL) THEN
814 l_n_opening_balance := 0;
815 END IF;
816 CLOSE cur_opening_balance;
817 END IF;
818 --
819 -- Get the total charge amount.
820 --
821 OPEN cur_charges_total (
822 p_n_person_id,
823 l_d_start_date,
824 p_d_cutoff_dt
825 );
826 FETCH cur_charges_total INTO rec_cur_charges_total;
827 CLOSE cur_charges_total;
828 --
829 -- Get the total credit amount.
830 --
831 OPEN cur_credits_total (
832 p_n_person_id,
833 l_d_start_date,
834 p_d_cutoff_dt
835 );
836 FETCH cur_credits_total INTO rec_cur_credits_total;
837 CLOSE cur_credits_total;
838
839 -- Bug#2564643 Removed the parameter p_subaccount_id in call to
840 -- igs_fi_gen_001.finp_get_total_planned_credits
841
842 --Added as a part of bug:2293676,to get the sum of the planned credits
843 l_message_name:=NULL;
844 IF l_pln_crd_setup = 'Y' THEN
845 l_planned_credits:=igs_fi_gen_001.finp_get_total_planned_credits(
846 p_person_id => p_n_person_id,
847 p_start_date => NULL,
848 p_end_date => p_d_cutoff_dt,
849 p_message_name => l_message_name);
850 IF l_message_name IS NOT NULL THEN
851 FND_MESSAGE.SET_NAME('IGS',l_message_name);
852 igs_ge_msg_stack.add;
853 l_flag :=FALSE;
854 END IF;
855 END IF;
856
857 --Skip the following code if above function returns some messages
858 IF l_flag THEN
859 l_n_closing_balance := l_n_opening_balance +
860 NVL (rec_cur_charges_total.total_charge_amount, 0) -
861 NVL (rec_cur_credits_total.total_credit_amount, 0);
862 --
863 -- 5. Identify the Transactions (Credit - Amount paid by the person/Charge - Amount laid on the person) to be Billed.
864 -- o All the Transactions from the Start Date (l_d_start_date) until the Cut Off Date (p_d_cutoff_dt)
865 -- are identified for Billing. The transactions both on the Start Date and the Cut Off Date
866 -- are also included in the Bill being generated.
867 --
868 --added as a part of bug:2293676
869 IF l_pln_crd_setup = 'Y' THEN
870 l_to_pay_amount:= NVL(l_n_closing_balance,0) - NVL(l_planned_credits,0);
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
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),
887 x_due_date => p_d_due_dt,
888 x_person_id => p_n_person_id,
889 x_bill_from_date => l_d_start_date,
890 x_opening_balance => igs_fi_gen_gl.get_formatted_amount(l_n_opening_balance),
891 x_cut_off_date => p_d_cutoff_dt,
892 x_closing_balance => igs_fi_gen_gl.get_formatted_amount(l_n_closing_balance),
893 x_to_pay_amount => igs_fi_gen_gl.get_formatted_amount(l_to_pay_amount), --added as a part of bug:2293676
894 x_printed_flag => 'N',
895 x_print_date => NULL,
896 x_mode => 'R'
897 );
898
899 --
900 -- Insert the Charge transactions information in the Bill Transactions table.
901 --
902
903 FOR rec_cur_charge_trans IN cur_charge_trans (
904 p_n_person_id,
905 l_d_start_date,
906 p_d_cutoff_dt
907 )
908 LOOP
909
910 --Set the flag to TRUE if charge transactions are found for processing.
911 l_b_txn_exist := TRUE;
912
913 l_r_bill_trans_row_id := NULL;
914 l_n_transaction_id := NULL;
915
916
917 --- Bug #3813498
918 -- Getting system fee type and fee class associated with the fee_type
919 OPEN cur_fee_type(rec_cur_charge_trans.fee_type);
920 FETCH cur_fee_type INTO l_v_s_fee_type,l_v_fee_class;
921 CLOSE cur_fee_type;
922
923 --Check the system fee type is either sponsor
924 IF l_v_s_fee_type = 'SPONSOR' THEN
925 -- check if it has Fee_Class defined
926 IF l_v_fee_class IS NOT NULL THEN
927 --Get the fee class meaning
928 l_v_fee_class_meaning := igs_fi_gen_gl.get_lkp_meaning(p_v_lookup_type => 'FEE_CLASS',
929 p_v_lookup_code => l_v_fee_class);
930
931 --Append the fee class meaning to the invoice_desc
932 rec_cur_charge_trans.invoice_desc := rec_cur_charge_trans.invoice_desc || ' : ' || l_v_fee_class_meaning;
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',
942 x_invoice_creditact_id => rec_cur_charge_trans.invoice_id,
943 x_transaction_date => rec_cur_charge_trans.invoice_creation_date,
944 x_transaction_number => rec_cur_charge_trans.invoice_number,
945 x_fee_credit_type => rec_cur_charge_trans.fee_type,
946 x_transaction_description => rec_cur_charge_trans.invoice_desc,
947 x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(rec_cur_charge_trans.invoice_amount),
948 x_mode => 'R'
949 );
950 END LOOP;
951
952 --
953 -- Insert the Credit transactions information in the Bill Transactions table.
954 --
955
956 FOR rec_cur_credit_trans IN cur_credit_trans (
957 p_n_person_id,
958 l_d_start_date,
959 p_d_cutoff_dt
960 )
961 LOOP
962
963 --Set the flag to TRUE if credit transactions are found for processing.
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',
973 x_invoice_creditact_id => rec_cur_credit_trans.credit_activity_id,
974 x_transaction_date => rec_cur_credit_trans.effective_date,
975 x_transaction_number => rec_cur_credit_trans.credit_number,
976 x_fee_credit_type => rec_cur_credit_trans.credit_type,
977 x_transaction_description => rec_cur_credit_trans.description,
978 x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(rec_cur_credit_trans.amount),
979 x_mode => 'R'
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
989 --Set the flag to TRUE if planned credit transactions are found for processing.
990 l_b_txn_exist := TRUE;
991
992 l_pln_crd_rowid:=NULL;
993 l_message_name:=NULL;
994 IF igs_fi_gen_001.finp_get_lfci_reln(l_cur_planned_crd.ld_cal_type,
995 l_cur_planned_crd.ld_sequence_number,
996 'LOAD',
997 l_fee_cal_type,
998 l_fee_ci_seq_num,
999 l_message_name) = FALSE THEN
1000 l_fee_cal_type:=NULL;
1001 l_fee_ci_seq_num:=NULL;
1002 END IF;
1003
1004 -- incase the fund code is sponsor then credit type description is passed else the
1005 -- bill description from the fund master is passed
1006 IF (l_cur_planned_crd.fed_fund_code = 'SPNSR') THEN
1007 l_bill_desc := l_cur_planned_crd.description;
1008 ELSE
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,
1018 x_pln_credit_date => l_cur_planned_crd.disb_date,
1019 x_fund_id => l_cur_planned_crd.fund_id,
1020 x_fee_cal_type => l_fee_cal_type,
1021 x_fee_ci_sequence_number => l_fee_ci_seq_num,
1022 x_pln_credit_amount => igs_fi_gen_gl.get_formatted_amount(l_cur_planned_crd.disb_net_amt),
1023 x_mode => 'R',
1024 x_bill_desc => l_bill_desc
1025 );
1026 END LOOP;
1027 END IF;
1028
1029
1030 --
1031 -- 7. Identify the Remittance Address.
1032 -- Insert the Remittance Address in the Bill Address Table.
1033 --
1034 OPEN cur_remitt_addr (p_n_remit_prty_site_id);
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',
1044 x_addr_line_1 => rec_cur_remitt_addr.addr_line_1,
1045 x_addr_line_2 => rec_cur_remitt_addr.addr_line_2,
1046 x_addr_line_3 => rec_cur_remitt_addr.addr_line_3,
1047 x_addr_line_4 => rec_cur_remitt_addr.addr_line_4,
1048 x_city => rec_cur_remitt_addr.city,
1049 x_state => rec_cur_remitt_addr.state,
1050 x_province => rec_cur_remitt_addr.province,
1051 x_county => rec_cur_remitt_addr.county,
1052 x_country => rec_cur_remitt_addr.country,
1053 x_postal_code => rec_cur_remitt_addr.postal_code,
1054 x_delivery_point_code => rec_cur_remitt_addr.delivery_point_code,
1055 x_mode => 'R'
1056 );
1057 END IF;
1058 CLOSE cur_remitt_addr;
1059 --
1060 -- Insert the Bill To Address in the Bill Address Table.
1061 --
1062 FOR rec_cur_bill_to_addr1 IN cur_bill_to_addr (
1063 p_c_site_usg_type_cd_1,
1064 p_c_site_usg_type_cd_2,
1065 p_c_site_usg_type_cd_3
1066 )
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',
1076 x_addr_line_1 => rec_cur_bill_to_addr1.addr_line_1,
1077 x_addr_line_2 => rec_cur_bill_to_addr1.addr_line_2,
1078 x_addr_line_3 => rec_cur_bill_to_addr1.addr_line_3,
1079 x_addr_line_4 => rec_cur_bill_to_addr1.addr_line_4,
1080 x_city => rec_cur_bill_to_addr1.city,
1081 x_state => rec_cur_bill_to_addr1.state,
1082 x_province => rec_cur_bill_to_addr1.province,
1083 x_county => rec_cur_bill_to_addr1.county,
1084 x_country => rec_cur_bill_to_addr1.country,
1085 x_postal_code => rec_cur_bill_to_addr1.postal_code,
1086 x_delivery_point_code => rec_cur_bill_to_addr1.delivery_point_code,
1087 x_mode => 'R'
1088 );
1089 END LOOP;
1090
1091 -- As per Deposits build, Bill should also include the deposit records.
1092 -- Fllowing code will identify and insert deposit records into Bill Deposits Table
1093 l_c_rec_installed := igs_fi_gen_005.finp_get_receivables_inst;
1094
1095 FOR rec_bill_deposits IN c_bill_deposits ( p_n_person_id, l_c_rec_installed, l_d_start_date, p_d_cutoff_dt )
1096 LOOP
1097
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'
1107 );
1108 END LOOP;
1109
1110 -- As per Payment Plan build, to create billing payment plan records, call to the local procedure create_payplan_bills is made
1111 create_payplan_bills (p_n_bill_id => l_n_bill_id_seq, p_n_person_id => p_n_person_id, p_d_cut_off_date => p_d_cutoff_dt);
1112
1113 --
1114 -- 10. Update the Charges/Credits table and Commit all data if the process is NOT running in Test Mode.
1115 --
1116
1117 --If there are no charges, no credits, no planned credits, no deposits, no payment plan records
1118 --found for processing..
1119 IF NOT l_b_txn_exist THEN
1120 --rollback all the bill transactions.
1121 ROLLBACK TO s_bill_txn;
1122 END IF;
1123
1124 IF (p_c_test_mode = 'N') THEN
1125 FOR rec_cur_charge_trans IN cur_charge_trans_upd (
1126 p_n_person_id,
1127 l_d_start_date,
1128 p_d_cutoff_dt
1129 )
1130 LOOP
1131
1132 --Change History
1133 --Who When What
1134 --skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
1135 --shtatiko 21-AUG-2003 Bug@ 3106262, Added reversal_gl_date column to igs_fi_inv_int_pkg.update_row
1136 --jbegum 24-Sep-02 Enh Bug#2564643
1137 -- Removed the subaccount_id parameter from call to igs_fi_inv_int_pkg.update_row
1138 --jbegum 20 feb 02 Enh bug # 2228910
1139 -- Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
1140 --masehgal 17-Jan-2002 ENH # 2170429
1141 -- Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
1142 --svuppala 21-Mar-2005 Modified bill_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
1143
1144 igs_fi_inv_int_pkg.update_row (
1145 x_rowid => rec_cur_charge_trans.row_id,
1146 x_invoice_id => rec_cur_charge_trans.invoice_id,
1147 x_person_id => rec_cur_charge_trans.person_id,
1148 x_fee_type => rec_cur_charge_trans.fee_type,
1149 x_fee_cat => rec_cur_charge_trans.fee_cat,
1150 x_fee_cal_type => rec_cur_charge_trans.fee_cal_type,
1151 x_fee_ci_sequence_number => rec_cur_charge_trans.fee_ci_sequence_number,
1152 x_course_cd => rec_cur_charge_trans.course_cd,
1153 x_attendance_mode => rec_cur_charge_trans.attendance_mode,
1154 x_attendance_type => rec_cur_charge_trans.attendance_type,
1155 x_invoice_amount_due => rec_cur_charge_trans.invoice_amount_due,
1156 x_invoice_creation_date => rec_cur_charge_trans.invoice_creation_date,
1157 x_invoice_desc => rec_cur_charge_trans.invoice_desc,
1158 x_transaction_type => rec_cur_charge_trans.transaction_type,
1159 x_currency_cd => rec_cur_charge_trans.currency_cd,
1160 x_status => rec_cur_charge_trans.status,
1161 x_attribute_category => rec_cur_charge_trans.attribute_category,
1162 x_attribute1 => rec_cur_charge_trans.attribute1,
1163 x_attribute2 => rec_cur_charge_trans.attribute2,
1164 x_attribute3 => rec_cur_charge_trans.attribute3,
1165 x_attribute4 => rec_cur_charge_trans.attribute4,
1166 x_attribute5 => rec_cur_charge_trans.attribute5,
1167 x_attribute6 => rec_cur_charge_trans.attribute6,
1168 x_attribute7 => rec_cur_charge_trans.attribute7,
1169 x_attribute8 => rec_cur_charge_trans.attribute8,
1170 x_attribute9 => rec_cur_charge_trans.attribute9,
1171 x_attribute10 => rec_cur_charge_trans.attribute10,
1172 x_invoice_amount => rec_cur_charge_trans.invoice_amount,
1173 x_bill_id => l_n_bill_id_seq,
1174 x_bill_number => TO_CHAR (l_n_bill_id_seq),
1175 x_bill_date => TRUNC(SYSDATE),
1176 x_waiver_flag => rec_cur_charge_trans.waiver_flag,
1177 x_waiver_reason => rec_cur_charge_trans.waiver_reason,
1178 x_effective_date => rec_cur_charge_trans.effective_date,
1179 x_invoice_number => rec_cur_charge_trans.invoice_number,
1180 x_exchange_rate => rec_cur_charge_trans.exchange_rate,
1181 x_bill_payment_due_date => p_d_due_dt,
1182 x_optional_fee_flag => rec_cur_charge_trans.optional_fee_flag,
1183 x_mode => 'R',
1184 x_reversal_gl_date => rec_cur_charge_trans.reversal_gl_date,
1185 x_tax_year_code => rec_cur_charge_trans.tax_year_code,
1186 x_waiver_name => rec_cur_charge_trans.waiver_name
1187 );
1188 END LOOP;
1189
1190 FOR rec_cur_credit_trans IN cur_credit_trans (
1191 p_n_person_id,
1192 l_d_start_date,
1193 p_d_cutoff_dt
1194 )
1195 LOOP
1196 OPEN cur_credit_hist (rec_cur_credit_trans.credit_activity_id);
1197 FETCH cur_credit_hist INTO rec_cur_credit_hist;
1198 IF (cur_credit_hist%FOUND) THEN
1199 igs_fi_cr_activities_pkg.update_row (
1200 x_rowid => rec_cur_credit_hist.rowid,
1201 x_credit_activity_id => rec_cur_credit_hist.credit_activity_id,
1202 x_credit_id => rec_cur_credit_hist.credit_id,
1203 x_status => rec_cur_credit_hist.status,
1204 x_transaction_date => rec_cur_credit_hist.transaction_date,
1205 x_amount => rec_cur_credit_hist.amount,
1206 x_dr_account_cd => rec_cur_credit_hist.dr_account_cd,
1207 x_cr_account_cd => rec_cur_credit_hist.cr_account_cd,
1208 x_dr_gl_ccid => rec_cur_credit_hist.dr_gl_ccid,
1209 x_cr_gl_ccid => rec_cur_credit_hist.cr_gl_ccid,
1210 x_bill_id => l_n_bill_id_seq,
1211 x_bill_number => TO_CHAR (l_n_bill_id_seq),
1212 x_bill_date => TRUNC(SYSDATE),
1213 x_posting_id => rec_cur_credit_hist.posting_id,
1214 x_mode => 'R',
1215 x_gl_date => rec_cur_credit_hist.gl_date,
1216 x_gl_posted_date => rec_cur_credit_hist.gl_posted_date,
1217 x_posting_control_id => rec_cur_credit_hist.posting_control_id
1218 );
1219 END IF;
1220 CLOSE cur_credit_hist;
1221 END LOOP;
1222
1223 -- Update the bill details for deposit records in credit activities table.
1224 FOR rec_bill_deposits IN c_bill_deposits ( p_n_person_id, l_c_rec_installed, l_d_start_date, p_d_cutoff_dt )
1225 LOOP
1226 -- Get the other credit activity details from cur_credit_hist cursor.
1227 -- Modified bill_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
1228 OPEN cur_credit_hist (rec_bill_deposits.credit_activity_id);
1229 FETCH cur_credit_hist INTO rec_cur_credit_hist;
1230 IF (cur_credit_hist%FOUND) THEN
1231 igs_fi_cr_activities_pkg.update_row (
1232 x_rowid => rec_cur_credit_hist.rowid,
1233 x_credit_activity_id => rec_cur_credit_hist.credit_activity_id,
1234 x_credit_id => rec_cur_credit_hist.credit_id,
1235 x_status => rec_cur_credit_hist.status,
1236 x_transaction_date => rec_cur_credit_hist.transaction_date,
1237 x_amount => rec_cur_credit_hist.amount,
1238 x_dr_account_cd => rec_cur_credit_hist.dr_account_cd,
1239 x_cr_account_cd => rec_cur_credit_hist.cr_account_cd,
1240 x_dr_gl_ccid => rec_cur_credit_hist.dr_gl_ccid,
1241 x_cr_gl_ccid => rec_cur_credit_hist.cr_gl_ccid,
1242 x_bill_id => l_n_bill_id_seq,
1243 x_bill_number => TO_CHAR (l_n_bill_id_seq),
1244 x_bill_date => TRUNC(SYSDATE),
1245 x_posting_id => rec_cur_credit_hist.posting_id,
1246 x_mode => 'R',
1247 x_gl_date => rec_cur_credit_hist.gl_date,
1248 x_gl_posted_date => rec_cur_credit_hist.gl_posted_date,
1249 x_posting_control_id => rec_cur_credit_hist.posting_control_id
1250 );
1251 END IF;
1252 CLOSE cur_credit_hist;
1253 END LOOP;
1254
1255 COMMIT;
1256 END IF;--Test Mode ='N'
1257 END IF;--l_flag
1258 END IF; -- NVL (l_d_start_date, p_d_cutoff_dt) <= p_d_cutoff_dt
1259
1260 RETURN;
1261 --
1262 END bill_the_person;
1263 --
1264 BEGIN
1265 --
1266 -- Set the Organization ID Context.
1267 --
1268
1269 igs_ge_gen_003.set_org_id (p_c_org_id);
1270
1271 -- Obtain the value of manage_accounts in the System Options form
1272 -- If it is null or 'OTHER', then this process is not available, so error out.
1273 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
1274 p_v_message_name => l_v_message_name
1275 );
1276 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
1277 fnd_message.set_name('IGS',l_v_message_name);
1278 igs_ge_msg_stack.add;
1279 p_c_status := 'FALSE';
1280 RETURN;
1281 END IF;
1282
1283 --
1284 -- 1. Validate the Input Parameters.
1285 --
1286 -- At least one of the Parameters : Person ID Group Code or Person ID should be specified.
1287 -- But both the parameters cannot be specified at the same time.
1288 --
1289 IF ((p_n_prsid_grp_id IS NULL) AND (p_n_person_id IS NULL)) THEN
1290 --
1291 -- Return FALSE if both the parameters are NULL.
1292 --
1293 fnd_message.set_name ('IGS', 'IGS_FI_PRS_PRSIDGRP_NULL');
1294 igs_ge_msg_stack.add;
1295 p_c_status := 'FALSE';
1296 RETURN;
1297 END IF;
1298 --
1299 IF ((p_n_prsid_grp_id IS NOT NULL) AND (p_n_person_id IS NOT NULL)) THEN
1300 --
1301 -- Return FALSE if both the parameters are NOT NULL.
1302 --
1303 fnd_message.set_name ('IGS', 'IGS_FI_PRS_OR_PRSIDGRP');
1304 igs_ge_msg_stack.add;
1305 p_c_status := 'FALSE';
1306 RETURN;
1307 END IF;
1308 IF (p_n_prsid_grp_id IS NOT NULL) THEN
1309 --
1310 -- Check if the Person ID Group Code is valid and not Closed.
1311 -- Bug 5178298 - changed the message to IGS_FI_INVALID_PARAMETER
1312 OPEN cur_person_id_group (p_n_prsid_grp_id);
1313 FETCH cur_person_id_group INTO rec_cur_person_id_group;
1314 IF (cur_person_id_group%NOTFOUND) THEN
1315 CLOSE cur_person_id_group;
1316 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1317 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP'));
1318 igs_ge_msg_stack.add;
1319 p_c_status := 'FALSE';
1320 RETURN;
1321 ELSE
1322 CLOSE cur_person_id_group;
1323 END IF;
1324 END IF;
1325 IF (p_n_person_id IS NOT NULL) THEN
1326 --
1327 -- Check if the Person ID is valid.
1328 -- Bug 5178298 - changed the message to IGS_FI_INVALID_PARAMETER
1329 IF igs_fi_gen_007.validate_person(p_n_person_id) = 'N' THEN
1330 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1331 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PARTY'));
1332 igs_ge_msg_stack.add;
1333 p_c_status := 'FALSE';
1334 RETURN;
1335 END IF;
1336 END IF;
1337
1338 -- Bug#2564643 Removed validation to check if the Sub Account ID is valid.
1339 -- This was being done thru cursor cur_sub_account_id
1340
1341 --
1342 -- Check if the value of the parameter Test Mode is valid.
1343 -- Bug 5178298 - changed the message to IGS_FI_INVALID_PARAMETER
1344 IF ((p_c_test_mode NOT IN ('Y', 'N')) OR (p_c_test_mode IS NULL)) THEN
1345 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER');
1346 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'TEST_MODE'));
1347 igs_ge_msg_stack.add;
1348 p_c_status := 'FALSE';
1349 RETURN;
1350 END IF;
1351 --
1352 -- Check if the Cut Off Date is valid.
1353 --
1354 IF ((p_d_cutoff_dt >= TRUNC(SYSDATE)) OR (p_d_cutoff_dt IS NULL)) THEN
1355 fnd_message.set_name ('IGS', 'IGS_FI_CUTOFF_DT');
1356 igs_ge_msg_stack.add;
1357 p_c_status := 'FALSE';
1358 RETURN;
1359 END IF;
1360 --
1361 -- Check if the Due Date is valid.
1362 --
1363 IF ((p_d_due_dt < TRUNC(SYSDATE)) OR (p_d_due_dt IS NULL)) THEN
1364 fnd_message.set_name ('IGS', 'IGS_RE_DUE_DT_CANT_BE_PAST_DT');
1365 igs_ge_msg_stack.add;
1366 p_c_status := 'FALSE';
1367 RETURN;
1368 END IF;
1369 --
1370 -- Check if the Remittance Address for the bill is valid and the Remittance Address Usage for the bill is valid.
1371 -- Bug 5178298 - Modified the cursor and the messages
1372 --
1373 IF (p_n_remit_prty_site_id IS NULL) THEN
1374 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1375 igs_ge_msg_stack.add;
1376 p_c_status := 'FALSE';
1377 RETURN;
1378 ELSE
1379 OPEN cur_usr_profl_name;
1380 FETCH cur_usr_profl_name INTO l_v_usr_profl_name;
1381 IF (cur_usr_profl_name%NOTFOUND) THEN
1382 CLOSE cur_usr_profl_name;
1383 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1384 igs_ge_msg_stack.add;
1385 p_c_status := 'FALSE';
1386 RETURN;
1387 ELSE
1388 CLOSE cur_usr_profl_name;
1389 OPEN cur_remit_addr (
1390 p_n_remit_prty_site_id,
1391 p_d_due_dt,
1392 FND_PROFILE.VALUE('IGS_REMIT_TO_ADD_USG')
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;
1402 ELSE
1403 CLOSE cur_remit_addr;
1404 END IF; -- End of cur_remit_addr%NOTFOUND
1405 END IF; -- End of cur_usr_profl_name%NOTFOUND
1406 END IF; -- End of p_n_remit_prty_site_id
1407
1408 -- Check if the Bill to Address 1 is NOT NULL, and
1409 -- Bug 5178298 - Modified the message
1410
1411 IF (p_c_site_usg_type_cd_1 IS NULL) THEN
1412 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER');
1413 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'BILL_ADDR_USG_1'));
1414 igs_ge_msg_stack.add;
1415 p_c_status := 'FALSE';
1416 RETURN;
1417 END IF;
1418 -- Bill To Address Usage 2 is not equal to Bill To Address Usage 1,
1419 -- Bill To Address Usage 3 is not equal to Bill To Address Usage 1,
1420 -- Bill To Address Usage 3 is not equal to Bill To Address Usage 2.
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;
1430 --
1431 -- Check if the Bill to Address Usage 1 is valid.
1432 -- Bug 5178298 - Modified the message
1433 OPEN cur_bill_to_addr_usage (p_c_site_usg_type_cd_1);
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;
1443 p_c_status := 'FALSE';
1444 RETURN;
1445 ELSE
1446 CLOSE cur_bill_to_addr_usage;
1447 END IF;
1448 IF (p_c_site_usg_type_cd_2 IS NOT NULL) THEN
1449 --
1450 -- Check if the Bill to Address Usage 2 is valid.
1451 -- Bug 5178298 - Modified the message
1452 OPEN cur_bill_to_addr_usage (p_c_site_usg_type_cd_2);
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;
1462 p_c_status := 'FALSE';
1463 RETURN;
1464 ELSE
1465 CLOSE cur_bill_to_addr_usage;
1466 END IF;
1467 END IF;
1468 IF (p_c_site_usg_type_cd_3 IS NOT NULL) THEN
1469 --
1470 -- Check if the Bill to Address Usage 3 is valid.
1471 -- Bug 5178298 - Modified the message
1472 OPEN cur_bill_to_addr_usage (p_c_site_usg_type_cd_3);
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;
1482 p_c_status := 'FALSE';
1483 RETURN;
1484 ELSE
1485 CLOSE cur_bill_to_addr_usage;
1486 END IF;
1487 END IF;
1488 --
1489 -- 2. Identify the Person to be Billed.
1490 --
1491 IF (p_n_person_id IS NOT NULL) THEN
1492 BEGIN
1493 bill_the_person (p_n_person_id);
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 p_c_status := 'WARN';
1497
1498 -- Note: In case of a handled exception with a functional message, SQLERRM would hold the message
1499 -- that has been set previously.
1500 -- In case of unhandled exception, SQLERRM would hold the ORA error that occured. The same
1501 -- would be raised here.
1502
1503 -- Log SQLERRM in case of any exception
1504 fnd_message.set_name('IGS','IGS_FI_ERR_TXT');
1505 fnd_message.set_token('TEXT',SQLERRM);
1506 igs_ge_msg_stack.add;
1507
1508 -- Log Person Number in the log file.
1509 fnd_message.set_name('IGS','IGS_FI_PERSON_NUM');
1510 fnd_message.set_token('PERSON_NUM',igs_fi_gen_008.get_party_number(p_n_person_id));
1511 igs_ge_msg_stack.add;
1512 END;
1513 ELSE
1514 FOR rec_cur_person_ids IN cur_person_ids (p_n_prsid_grp_id) LOOP
1515 -- Following procedure bill_the_person invoked in a begin-end block so that
1516 -- if process errors for one person, it can skip and move to the next person.
1517 BEGIN
1518 bill_the_person (rec_cur_person_ids.person_id );
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 -- If any exception happens, log the person details and skip.
1522 p_c_status := 'WARN';
1523
1524 -- Note: In case of a handled exception with a functional message, SQLERRM would hold the message
1525 -- that has been set previously.
1526 -- In case of unhandled exception, SQLERRM would hold the ORA error that occured. The same
1527 -- would be raised here.
1528
1529 -- Log SQLERRM in case of any unhandled exception
1530 fnd_message.set_name('IGS','IGS_FI_ERR_TXT');
1531 fnd_message.set_token('TEXT',SQLERRM);
1532 igs_ge_msg_stack.add;
1533
1534 -- Log Person Number in the log file.
1535 fnd_message.set_name('IGS','IGS_FI_PERSON_NUM');
1536 fnd_message.set_token('PERSON_NUM',igs_fi_gen_008.get_party_number(rec_cur_person_ids.person_id));
1537 igs_ge_msg_stack.add;
1538 END;
1539 END LOOP;
1540 END IF;
1541 --
1542 -- Return TRUE to the calling program saying that everything went fine.
1543 --
1544 -- Set the status to True only if it is not WARN already. This is to maintain the status as
1545 -- 'WARN' if all persons in a person id group had validation failures, which would have set
1546 -- the status to WARN.
1547 IF (p_c_status <> 'WARN') THEN
1548 p_c_status := 'TRUE';
1549 END IF;
1550
1551 RETURN;
1552 --
1553 EXCEPTION
1554 WHEN e_resource_busy THEN
1555 p_c_status:='FALSE';
1556 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_LOCKED');
1557 igs_ge_msg_stack.add;
1558 RAISE;
1559 WHEN OTHERS THEN
1560 ROLLBACK;
1561 p_c_status := 'FALSE';
1562 RAISE;
1563 END billing_extract;
1564
1565 PROCEDURE create_payplan_bills (p_n_bill_id IN NUMBER, p_n_person_id IN NUMBER, p_d_cut_off_date IN DATE) AS
1566
1567 /**********************************************************
1568 Created By : smvk
1569
1570 Date Created By : 03-Sep-03
1571
1572 Purpose : For creating paypment plan and installment billing record for a student
1573
1574 Know limitations, enhancements or remarks
1575
1576 Change History
1577
1578 Who When What
1579 abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
1580 vvutukur 07-dec-2003 Bug#3146325.Used boolean variable l_b_pmtplns_exist to check if any payment plan
1581 records are avaiable for processing the bill.
1582 smvk 10-Sep-2003 Bug 3045007, Modified the cursor c_clo_dis_pp_dtls to select
1583 records of plan_end_date less than or equal to cutoff date.
1584 ***************************************************************/
1585
1586 -- cursor to select the payment plan contract signed by student and payment plan status is ACTIVE.
1587 cursor c_act_pp_dtls (cp_n_person_id IN igs_fi_pp_std_attrs.person_id%TYPE) IS
1588 SELECT *
1589 FROM igs_fi_pp_std_attrs
1590 WHERE person_id = cp_n_person_id
1591 AND plan_status_code = 'ACTIVE';
1592
1593 --cursor to select the payment plan contract signed by student and payment plan status is CLOSED and DISQUALIFIED.
1594 cursor c_clo_dis_pp_dtls (cp_n_person_id IN igs_fi_pp_std_attrs.person_id%TYPE,
1595 cp_d_cutoff_date IN igs_fi_pp_std_attrs.plan_end_date%TYPE) IS
1596 SELECT *
1597 FROM igs_fi_pp_std_attrs
1598 WHERE person_id = cp_n_person_id
1599 AND plan_status_code IN ('CLOSED','DISQUALIFIED')
1600 AND TRUNC(plan_end_date) <= TRUNC(cp_d_cutoff_date);
1601
1602 --cursor to select the payment plan installment for the given payment plan identifier
1603 cursor c_pp_instlmnts ( cp_n_student_plan_id IN igs_fi_pp_instlmnts.student_plan_id%TYPE) IS
1604 SELECT *
1605 FROM igs_fi_pp_instlmnts
1606 WHERE student_plan_id = cp_n_student_plan_id;
1607
1608 l_c_rowid ROWID; -- to hold rowid while using insert_row tbh call.
1609 rec_act_pp_dtls c_act_pp_dtls%ROWTYPE; -- row type Active cursor variable
1610 rec_clo_dis_pp_dtls c_clo_dis_pp_dtls%ROWTYPE; -- row type Closed / Disqualified cursor variable
1611 rec_pp_instlmnts c_pp_instlmnts%ROWTYPE; -- row type payment plan installments cursor variable
1612
1613 BEGIN
1614 OPEN c_act_pp_dtls(p_n_person_id);
1615 FETCH c_act_pp_dtls INTO rec_act_pp_dtls;
1616
1617 IF c_act_pp_dtls%FOUND THEN
1618 CLOSE c_act_pp_dtls;
1619
1620 --Set the flag to TRUE if payment plan records are found for processing.
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,
1630 X_PLAN_END_DATE => rec_act_pp_dtls.plan_end_date,
1631 X_MODE => 'R'
1632 );
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,
1642 X_INSTALLMENT_LINE_NUM => rec_pp_instlmnts.installment_line_num,
1643 X_INSTALLMENT_DUE_DATE => rec_pp_instlmnts.due_date,
1644 X_INSTALLMENT_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.installment_amt),
1645 X_DUE_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.due_amt),
1646 X_MODE => 'R'
1647 );
1648 END LOOP; -- end of payment plan installment loop
1649
1650 ELSE
1651 CLOSE c_act_pp_dtls;
1652
1653 -- create billing payment plan records for each closed and disqualified payment plan records
1654 FOR rec_clo_dis_pp_dtls IN c_clo_dis_pp_dtls(p_n_person_id, p_d_cut_off_date) LOOP
1655
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,
1665 X_PLAN_END_DATE => rec_clo_dis_pp_dtls.plan_end_date,
1666 X_MODE => 'R'
1667 );
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,
1677 X_INSTALLMENT_LINE_NUM => rec_pp_instlmnts.installment_line_num,
1678 X_INSTALLMENT_DUE_DATE => rec_pp_instlmnts.due_date,
1679 X_INSTALLMENT_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.installment_amt),
1680 X_DUE_AMT => igs_fi_gen_gl.get_formatted_amount(rec_pp_instlmnts.due_amt),
1681 X_MODE => 'R'
1682 );
1683
1684 END LOOP; -- end of payment plan installment loop
1685 END LOOP; -- end of payment plan loop
1686
1687 END IF;
1688
1689 END create_payplan_bills;
1690
1691 END igs_fi_bill_extract;
[Home] [Help]