1 PACKAGE BODY igs_fi_ad_hoc_wav AS
2 /* $Header: IGSFI70B.pls 120.3 2006/06/01 15:26:21 sapanigr noship $ */
3
4 ------------------------------------------------------------------
5 --Created by : Jabeen Begum, Oracle IDC
6 --Date created: 5/12/2001
7 --Purpose: This package is used for group application of waiver on charges
8 --or group release of waiver present on charges
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 --sapanigr 30-May-2006 Bug 5251760 - Modified procedure group_waiver_proc for R12 XBuild3 SQL Repository Perf tuning.
15 --sapanigr 17-Feb-2006 Bug 5018046 - Modified procedure group_waiver_proc for R12 SQL Repository Perf tuning.
16 --pathipat 26-Apr-2004 Bug 3578249 - Modified function test_mode() and group_waiver_proc()
17 --vvutukur 20-Jan-2004 Bug#3348787.Modified function test_mode.
18 --uudayapr 26-SEP-2003 Bug:3055356 - Replaced the Default Intiallization of sysdate to l_release_dt
19 -- when null ,Added code for message log in group_waiver_proc procedure.
20 --pathipat 24-Jun-2003 Bug: 3018104 - Impact of changes to person id group views
21 -- Replaced igs_pe_persid_group_v with igs_pe_persid_group in group_waiver_proc()
22 --shtatiko 22-APR-2003 Enh# 2831569, Modified group_waiver_proc
23 --vvutukur 21-Jan-2003 Bug#2751136.Modifications done in procedure group_waiver_proc and function lookup_desc.
24 -- SYKRISHN 03-JAN2003 Bug 2684895 - Logged person group code instead of person group id
25 -- used igs_fi_gen_005.finp_get_prsid_grp_code()
26 -- some lookups were missing - introduced 4 lookup codes to IGS_FI_LOCKBOX
27 --smvk 16-Sep-2002 Bug # 2564643. Removed the parameters p_n_sub_acc_1 and p_n_sub_acc_2
28 -- from procedure group_waiver_proc. As a part of Subaccount Removal Build.
29 --vvutukur 05-Jul-2002 Modified group_waiver_proc procedure to exclude reversed charges
30 -- from being selected through cursor c_per_chg as part of Bug#2405762.
31 --jbegum 13-Jun-2002 Bug#2412433 Added a validation checking if the Balance Type passed
32 -- is a STANDARD Balance.If it is then concurrent process errors out.
33 --sarakshi 28-Feb-2002 bug:2238362, changed the view igs_pe_person_v to igs_fi_parties_v
34 --sbaliga 25-feb-2002 Modified procedure group_waiver_proc as part of #2144600
35 -------------------------------------------------------------------
36
37 FUNCTION fee_perd (p_cal_type IN igs_ca_inst.cal_type%TYPE ,
38 p_seq_number IN igs_ca_inst.sequence_number%TYPE ) RETURN VARCHAR2 IS
39 ------------------------------------------------------------------
40 --Created by : Jabeen Begum, Oracle IDC
41 --Date created: 20/12/2001
42 --
43 --
44 --Purpose: Returns the fee period for the cal type and sequence number combination.
45 --
46 --
47 --Known limitations/enhancements and/or remarks:
48 --
49 --Change History:
50 --Who When What
51 -------------------------------------------------------------------
52
53 CURSOR cur_perd(cp_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
54 cp_seq_num igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
55 SELECT cal_type , start_dt , end_dt
56 FROM igs_ca_inst
57 WHERE cal_type = cp_cal_type
58 AND sequence_number = cp_seq_num;
59
60 l_fee_per cur_perd%ROWTYPE;
61 l_string VARCHAR2(50);
62
63 BEGIN
64 OPEN cur_perd(p_cal_type,
65 p_seq_number);
66 FETCH cur_perd INTO l_fee_per;
67 IF cur_perd%NOTFOUND THEN
68 CLOSE cur_perd;
69 RETURN NULL;
70 ELSE
71 l_string := l_fee_per.cal_type||' '||l_fee_per.start_dt||' - '||l_fee_per.end_dt;
72 CLOSE cur_perd;
73 END IF;
74
75 RETURN l_string;
76
77 END fee_perd;
78
79 FUNCTION lookup_desc( p_type IN igs_lookup_values.lookup_type%TYPE,
80 p_code IN igs_lookup_values.lookup_code%TYPE ) RETURN VARCHAR2 IS
81 ------------------------------------------------------------------
82 --Created by : Jabeen Begum, Oracle IDC
83 --Date created: 5/12/2001
84 --
85 --
86 --Purpose: Returns the meaning for the given lookup code.
87 --
88 --
89 --Known limitations/enhancements and/or remarks:
90 --
91 --Change History:
92 --Who When What
93 --vvutukur 21-Jan-2003 Bug#2751136.Used igs_lookup_values in stead of igs_lookups_view.Removed cursor cur_desc and its implementation,instead,
94 -- made a call to igs_fi_gen_gl.get_lkp_meaning,which returns the meaning of lookup code of a specific lookup type.
95 -------------------------------------------------------------------
96 l_desc igs_lookup_values.meaning%TYPE;
97
98 BEGIN
99
100 IF p_code IS NULL THEN
101 RETURN NULL ;
102 ELSE
103 l_desc := igs_fi_gen_gl.get_lkp_meaning(p_v_lookup_type => p_type,
104 p_v_lookup_code => p_code
105 );
106 END IF ;
107
108 RETURN l_desc;
109
110 END lookup_desc;
111
112 PROCEDURE log_messages ( p_msg_name IN VARCHAR2 ,
113 p_msg_val IN VARCHAR2
114 ) IS
115 ------------------------------------------------------------------
116 --Created by : Jabeen Begum, Oracle IDC
117 --Date created: 5/12/2001
118 --
119 --
120 --Purpose: This procedure is private to this package body .
121 -- The procedure logs all the parameter values ,
122 -- table values
123 --
124 --
125 --Known limitations/enhancements and/or remarks:
126 --
127 --Change History:
128 --Who When What
129 -------------------------------------------------------------------
130 BEGIN
131
132 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
133 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
134 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
135 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
136
137 END log_messages ;
138
139 /* Removed the function sub_name as a part of Subaccount removal Build. Bug # 2564643 */
140
141 FUNCTION test_mode (p_code IN fnd_lookup_values.lookup_code%TYPE) RETURN VARCHAR2 IS
142
143 ------------------------------------------------------------------
144 --Created by : Jabeen Begum, Oracle IDC
145 --Date created: 20/12/2001
146 --
147 --
148 --Purpose: Returns the meaning of the code passed to the test mode parameter.
149 --
150 --
151 --Known limitations/enhancements and/or remarks:
152 --
153 --Change History:
154 --Who When What
155 --pathipat 26-Apr-2004 Bug 3578249 - Modified cur_mode - replaced fnd_lookup_values
156 -- with igs_lookup_values.
157 --vvutukur 20-Jan-2004 Bug#3348787.Modified cursor cur_mode.
158 -------------------------------------------------------------------
159
160 CURSOR cur_mode(cp_code fnd_lookup_values.lookup_code%TYPE) IS
161 SELECT meaning
162 FROM igs_lookup_values
163 WHERE lookup_code = cp_code
164 AND lookup_type = 'YES_NO';
165
166 l_mode fnd_lookup_values.meaning%TYPE ;
167
168 BEGIN
169
170 OPEN cur_mode(p_code);
171 FETCH cur_mode INTO l_mode ;
172 CLOSE cur_mode ;
173 RETURN l_mode ;
174
175 END test_mode;
176
177 PROCEDURE group_waiver_proc( errbuf OUT NOCOPY VARCHAR2 ,
178 retcode OUT NOCOPY NUMBER ,
179 p_c_action IN VARCHAR2 ,
180 p_c_bal_type IN igs_lookups_view.lookup_code%TYPE ,
181 p_d_start_dt IN VARCHAR2 ,
182 p_d_end_dt IN VARCHAR2 ,
183 p_d_release_dt IN VARCHAR2 ,
184 p_n_person_id IN igs_pe_person_v.person_id%TYPE ,
185 p_n_pers_id_grp_id IN igs_pe_persid_group_v.group_id%TYPE ,
186 p_c_fee_period IN VARCHAR2 ,
187 /* Removed the parameters p_n_sub_acc_1 and p_n_sub_acc_2, as a part of Bug # 2564643 */
188 p_c_fee_type_1 IN igs_fi_inv_int.fee_type%TYPE ,
189 p_c_fee_type_2 IN igs_fi_inv_int.fee_type%TYPE ,
190 p_c_fee_type_3 IN igs_fi_inv_int.fee_type%TYPE ,
191 p_c_test_flag IN fnd_lookup_values.lookup_code%TYPE
192 ) AS
193 ------------------------------------------------------------------
194 --Created by : Jabeen Begum, Oracle IDC
195 --Date created: 5/12/2001
196 --
197 --Purpose: This procedure is the main procedure for applying or releasing waivers on
198 -- group of charges having similar attributes, at one go.
199 --
200 --Known limitations/enhancements and/or remarks:
201 --
202 --Change History:
203 --Who When What
204 --sapanigr 30-May-2006 Bug 5251760 - Modified cursor c_per_id by replacing igs_fi_parties_v with base tables
205 --sapanigr 17-Feb-2006 Bug 5018036 - 1. Added validation to check if both person id and person id group are not null
206 -- so that full table scan on IGS_FI_INV_INT_ALL can be avoided.
207 -- 2. Broke up UNION in cursor c_person to make two different cursors c_person and c_pers_group.
208 -- This solves Non mergability issue and has improved performance.
209 --pathipat 26-Apr-2004 Bug 3578249 - Modified code w.r.t p_c_test_flag - Checked against Y and N
210 -- instead of against 1 and 2 respectively due to change in lookup_type used.
211 --uudayapr 26-sep-2003 Bug:3055356-Added the validation for logging message to log file when release date
212 -- is provided when the Action is waive.
213 -- Removed the Default intiallization to Sysdate when release date is not given
214 -- for release action and placed the code for logging the error message to the
215 -- log file and error out the process.
216 -- Added the Code for logging message to log file when start date and end date
217 -- is provided when the action is release.
218 --pathipat 24-Jun-2003 Bug: 3018104 - Impact of changes to person id group views
219 -- Replaced igs_pe_persid_group_v with igs_pe_persid_group in c_grp_id
220 --shtatiko 22-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option
221 --vvvutukur 21-Jan-2003 Bug#2751136.Removed app_exception.raise_exception whenever a parameter validation fails, instead, used a variable
222 -- l_valid to identify the failure of validation,after logging the error message. After all the parameters are validated,
223 -- set the retcode to 2 and stopped processing further.Also, used message IGS_FI_NO_PERS_PGRP instead of
224 -- IGS_FI_PRS_OR_PRSIDGRP,when both person number and person group parameters have been passed to the process.
225 -- SYKRISHN 03-JAN2003 Bug 2684895 - Logged person group code instead of person group id
226 -- used igs_fi_gen_005.finp_get_prsid_grp_code()
227 -- some lookups were missing - introduced 4 lookup codes to IGS_FI_LOCKBOXLOCKBOX
228 --smvk 19-Sep-2002 Bug # 2564643. Removed the parameters p_n_sub_acc_1 and p_n_sub_acc_2
229 -- from procedure group_waiver_proc. As a part of Subaccount Removal Build.
230 --vvutukur 05-Jul-2002 Excluded reversed charges from being selected through
231 -- cursor c_per_chg as part of Bug#2405762.
232 --sbaliga 25-feb-2002 Excluded charges of type 'REFUND' from being selected
233 -- through cursor c_per_chg as part of #2144600.
234 -------------------------------------------------------------------
235
236
237 l_start_dt igs_fi_inv_wav_det.start_dt%TYPE ;
238 l_end_dt igs_fi_inv_wav_det.end_dt%TYPE ;
239 l_release_dt igs_fi_inv_wav_det.end_dt%TYPE ;
240 l_rowid igs_fi_inv_wav_det_v.row_id%TYPE ;
241 l_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE ;
242 l_fee_ci_seq_num igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE ;
243 l_per_num igs_pe_person_v.person_number%TYPE ;
244 l_msg_str VARCHAR2(1000) ;
245
246 TYPE tab_party_rec IS TABLE OF hz_parties.party_id%TYPE INDEX BY BINARY_INTEGER;
247 v_tab_party_rec tab_party_rec ;
248 l_n_party_id hz_parties.party_id%TYPE;
249
250
251
252 CURSOR c_grp_id (cp_group_id igs_pe_persid_group_v.group_id%TYPE) IS
253 SELECT 'X'
254 FROM igs_pe_persid_group
255 WHERE group_id = cp_group_id
256 AND closed_ind = 'N'
257 AND TRUNC(create_dt) <= TRUNC(SYSDATE);
258
259 l_v_grp_id VARCHAR2(1);
260
261 CURSOR c_per_id (cp_n_person_id hz_parties.party_id%TYPE) IS
262 SELECT hz.party_number person_number,
263 nvl(pp.person_name, hz.party_name) full_name
264 FROM hz_parties hz, hz_person_profiles pp
265 WHERE hz.party_id = cp_n_person_id
266 AND pp.party_id = cp_n_person_id;
267
268 l_v_per_id c_per_id%ROWTYPE;
269
270 CURSOR c_cal_per (cp_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE ,
271 cp_ci_seq_num igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
272 SELECT 'X'
273 FROM igs_ca_type t , igs_ca_inst i , igs_ca_stat s
274 WHERE t.closed_ind = 'N'
275 AND t.s_cal_cat = 'FEE'
276 AND t.cal_type = cp_cal_type
277 AND t.cal_type = i.cal_type
278 AND i.sequence_number = cp_ci_seq_num
279 AND i.cal_status = s.cal_status
280 AND s.s_cal_status = 'ACTIVE';
281
282 l_v_cal_per VARCHAR2(1);
283
284 CURSOR c_pers_group (cp_person_grp igs_pe_persid_group_v.group_id%TYPE) IS
285 SELECT person_id
286 FROM igs_pe_prsid_grp_mem
287 WHERE group_id = cp_person_grp
288 AND (start_date <= TRUNC(SYSDATE) OR start_date IS NULL)
289 AND (end_date >= TRUNC(SYSDATE) OR end_date IS NULL)
290 ORDER BY 1;
291
292 CURSOR c_person(cp_person_id hz_parties.party_id%TYPE) IS
293 SELECT party_id person_id
294 FROM hz_parties
295 WHERE party_id = cp_person_id
296 AND party_type IN ('ORGANIZATION','PERSON');
297
298 CURSOR c_per_chg (cp_person_id igs_pe_person_v.person_id%TYPE ,
299 cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE ,
300 cp_fee_ci_seq_no igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE ,
301 /* Removed the cursor parameters cp_sub_acc1 and cp_sub_acc2 as a part of the Bug # 2564643 */
302 cp_fee_type1 igs_fi_inv_int.fee_type%TYPE ,
303 cp_fee_type2 igs_fi_inv_int.fee_type%TYPE ,
304 cp_fee_type3 igs_fi_inv_int.fee_type%TYPE) IS
305 SELECT *
309 AND fee_ci_sequence_number = cp_fee_ci_seq_no
306 FROM igs_fi_inv_int
307 WHERE person_id = cp_person_id
308 AND fee_cal_type = cp_fee_cal_type
310 AND transaction_type <>'REFUND'
311 AND NVL(waiver_flag,'N') <> 'Y'
312 AND ((cp_fee_type1 IS NULL AND cp_fee_type2 IS NULL AND cp_fee_type3 IS NULL) OR
313 (cp_fee_type1 IS NOT NULL AND fee_type = cp_fee_type1) OR
314 (cp_fee_type2 IS NOT NULL AND fee_type = cp_fee_type2) OR
315 (cp_fee_type3 IS NOT NULL AND fee_type = cp_fee_type3)
316 );
317
318 CURSOR c_chg_wav (cp_inv_id igs_fi_inv_wav_det.invoice_id%TYPE ,
319 cp_bal_type igs_fi_inv_wav_det.balance_type%TYPE ,
320 cp_rel_dt igs_fi_inv_wav_det.end_dt%TYPE) IS
321 SELECT *
322 FROM igs_fi_inv_wav_det_v
323 WHERE invoice_id = cp_inv_id
324 AND balance_type = cp_bal_type
325 AND ((end_dt IS NOT NULL AND end_dt > cp_rel_dt) OR (end_dt IS NULL));
326
327 l_chg_wav c_chg_wav%ROWTYPE;
328 l_valid BOOLEAN := TRUE;
329 l_v_message_name fnd_new_messages.message_name%TYPE;
330 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
331 l_validation_exp EXCEPTION;
332
333 BEGIN
334
335 /** sets the orgid **/
336 IGS_GE_GEN_003.set_org_id(NULL) ;
337
338 /** initialises the out NOCOPY parameter to 0 **/
339 retcode := 0 ;
340
341 /** Converting the date parameters passed as datatype VARCHAR2 to datatype DATE **/
342 IF p_d_start_dt IS NOT NULL THEN
343 l_start_dt := IGS_GE_DATE.igsdate(p_d_start_dt) ;
344 END IF;
345 IF p_d_end_dt IS NOT NULL THEN
346 l_end_dt := IGS_GE_DATE.igsdate(p_d_end_dt) ;
347 END IF;
348 IF p_d_release_dt IS NOT NULL THEN
349 l_release_dt := IGS_GE_DATE.igsdate(p_d_release_dt) ;
350 END IF;
351
352 /** Extracting the Fee Cal Type and Fee Ci Sequence Number from the Fee Period Parameter **/
353 l_fee_cal_type := RTRIM(SUBSTR(p_c_fee_period ,1,10)) ;
354 l_fee_ci_seq_num := TO_NUMBER(RTRIM(SUBSTR(p_c_fee_period,12))) ;
355
356 /** Extracting the person_number if a person id has been passed in parameter p_n_person_id **/
357 IF p_n_person_id IS NOT NULL THEN
358
359 OPEN c_per_id(p_n_person_id);
360 FETCH c_per_id INTO l_v_per_id;
361 IF c_per_id%NOTFOUND THEN
362 l_per_num := NULL;
363 CLOSE c_per_id;
364 ELSE
365 l_per_num := l_v_per_id.person_number;
366 CLOSE c_per_id;
367 END IF;
368
369 END IF;
370
371 /** logs all the parameters in the LOG **/
372 log_messages(lookup_desc('IGS_FI_LOCKBOX','ACTION'),p_c_action);
373 log_messages(lookup_desc('IGS_FI_LOCKBOX','BALANCE_TYPE'),lookup_desc('IGS_FI_BALANCE_TYPE',p_c_bal_type));
374 log_messages(lookup_desc('IGS_FI_LOCKBOX','WAIVE_START_DT'),l_start_dt);
375 log_messages(lookup_desc('IGS_FI_LOCKBOX','WAIVE_END_DT'),l_end_dt);
376 log_messages(lookup_desc('IGS_FI_LOCKBOX','WAIVE_REL_DT'),l_release_dt);
377 log_messages(lookup_desc('IGS_FI_LOCKBOX','PARTY'),l_per_num);
378 log_messages(lookup_desc('IGS_FI_LOCKBOX','PERSON_GROUP'),igs_fi_gen_005.finp_get_prsid_grp_code(p_n_pers_id_grp_id));
379 log_messages(lookup_desc('IGS_FI_LOCKBOX','FEE_PERIOD'),fee_perd(l_fee_cal_type,l_fee_ci_seq_num));
380
381 /* Removed the code which logs the value of the parameters p_n_sub_acc_1 and p_n_sub_acc_2 */
382
383 log_messages(lookup_desc('IGS_FI_LOCKBOX','FEE_TYPE1'),p_c_fee_type_1);
384 log_messages(lookup_desc('IGS_FI_LOCKBOX','FEE_TYPE2'),p_c_fee_type_2);
385 log_messages(lookup_desc('IGS_FI_LOCKBOX','FEE_TYPE3'),p_c_fee_type_3);
386 log_messages(lookup_desc('IGS_FI_LOCKBOX','TEST_MODE'),test_mode(p_c_test_flag));
387
388 -- Check the value of Manage Accounts System Option value.
389 -- If its NULL or OTHER then this process should error out by logging message.
390 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
391 p_v_message_name => l_v_message_name );
392 IF l_v_manage_accounts IS NULL OR l_v_manage_accounts = 'OTHER' THEN
393 fnd_message.set_name ( 'IGS', l_v_message_name );
394 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
395 RAISE l_validation_exp;
396 END IF;
397
398 /** Only person id or group id should be passed to the process
399 If both are passed as parameter then the process errors out NOCOPY **/
400
401 IF (p_n_person_id IS NOT NULL AND p_n_pers_id_grp_id IS NOT NULL) THEN
402 FND_MESSAGE.SET_NAME('IGS','IGS_FI_NO_PERS_PGRP');
403 IGS_GE_MSG_STACK.ADD;
404 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
405 l_valid := FALSE;
406 END IF;
407
408 -- Bug# 5018036, Either of the two parameters must be specified.
409 IF (p_n_person_id IS NULL AND p_n_pers_id_grp_id IS NULL) THEN
410 fnd_message.set_name ( 'IGS', 'IGS_FI_PRS_PRSIDGRP_NULL') ;
411 igs_ge_msg_stack.ADD;
412 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
413 l_valid := FALSE;
414 END IF;
415
416 /** Validating whether the Person Id Group passed to parameter p_n_pers_id_grp_id
417 is a valid Person Id Group **/
418 IF p_n_pers_id_grp_id IS NOT NULL THEN
419
423 IF c_grp_id%NOTFOUND THEN
420 OPEN c_grp_id(p_n_pers_id_grp_id);
421 FETCH c_grp_id INTO l_v_grp_id;
422
424 CLOSE c_grp_id;
425 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVPERS_ID_GRP');
426 IGS_GE_MSG_STACK.ADD;
427 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
428 l_valid := FALSE;
429 END IF;
430 CLOSE c_grp_id;
431
432 END IF;
433
434 /** Validating whether the Person Id passed to parameter p_n_person_id
435 is a valid Person Id **/
436
437 IF p_n_person_id IS NOT NULL THEN
438
439 OPEN c_per_id(p_n_person_id);
440 FETCH c_per_id INTO l_v_per_id;
441
442 IF c_per_id%NOTFOUND THEN
443 CLOSE c_per_id;
444 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_PERSON_ID');
445 IGS_GE_MSG_STACK.ADD;
446 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
447 l_valid := FALSE;
448 END IF;
449 CLOSE c_per_id;
450
451 END IF;
452
453 /** Validating whether the value passed to parameter p_c_action
454 is either WAIVE OR RELEASE **/
455
456 IF p_c_action NOT IN ('WAIVE','RELEASE') THEN
457 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
458 IGS_GE_MSG_STACK.ADD;
459 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
460 l_valid := FALSE;
461 END IF;
462
463 /** Validating whether the start date has been provided in case the action is 'WAIVE'
464 Also if the start date has been provided,it should be greater than or equal sysdate **/
465
466 IF p_c_action = 'WAIVE' THEN
467 IF l_start_dt IS NULL THEN
468 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WV_STRT_DT');
469 IGS_GE_MSG_STACK.ADD;
470 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
471 l_valid := FALSE;
472 ELSIF TRUNC(l_start_dt) < TRUNC(SYSDATE) THEN
473 FND_MESSAGE.SET_NAME('IGS','IGS_FI_ST_NOT_LT_CURRDT');
474 IGS_GE_MSG_STACK.ADD;
475 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
476 l_valid := FALSE;
477 END IF;
478
479 --Added the If Condtion to log a message when Waiver Release date is mentioned
480 --and the Action Type is WAIVE.
481 IF l_release_dt IS NOT NULL THEN
482 FND_MESSAGE.SET_NAME('IGS','IGS_FI_RL_DT_NR');
483 IGS_GE_MSG_STACK.ADD;
484 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
485 END IF;
486
487
488 END IF;
489
490 /** Validating whether the end date is greater than or equal to the start date
491 in case the action is 'WAIVE' **/
492
493 IF p_c_action = 'WAIVE' THEN
494 IF l_end_dt IS NOT NULL AND l_end_dt < l_start_dt THEN
495 FND_MESSAGE.SET_NAME('IGS','IGS_GE_END_DT_GE_ST_DATE');
496 IGS_GE_MSG_STACK.ADD;
497 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
498 l_valid := FALSE;
499 END IF;
500 END IF;
501
502 /** Validating that for action 'RELEASE' if the release date has been provided,it should be greater than
503 or equal to sysdate. If it has not been provided then it is set to sysdate**/
504
505 IF p_c_action = 'RELEASE' THEN
506 --Added the If condition to check whether l_end_dt or
507 --l_start_dt is Null if Not Log the ErrorMessage to the Log File.
508
509 IF l_end_dt IS NOT NULL OR l_start_dt IS NOT NULL THEN
510 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WR_DTS_NR');
511 IGS_GE_MSG_STACK.ADD;
512 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
513 END IF;
514
515 IF l_release_dt IS NULL THEN
516 --Removed the Default Intiallization to Sysdate
517 --and Logged the Error Message and Errored Out
518 FND_MESSAGE.SET_NAME('IGS','IGS_FI_RLS_DT');
519 IGS_GE_MSG_STACK.ADD;
520 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
521 l_valid := FALSE;
522 ELSIF TRUNC(l_release_dt) < TRUNC(SYSDATE) THEN
523 FND_MESSAGE.SET_NAME('IGS','IGS_FI_RLS_CRR_DT');
524 IGS_GE_MSG_STACK.ADD;
525 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
526 l_valid := FALSE;
527 END IF;
528 END IF;
529
530 /** Validating whether the Calendar Type passed is a FEE calendar and it is not closed
531 (ie. closed_ind = 'N')
532 Also the Calendar Instance of the Calendar Type passed should have system calendar
533 status as ACTIVE **/
534
535 OPEN c_cal_per(l_fee_cal_type,l_fee_ci_seq_num);
536 FETCH c_cal_per INTO l_v_cal_per;
537
538 IF c_cal_per%NOTFOUND THEN
539 CLOSE c_cal_per;
540 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
541 IGS_GE_MSG_STACK.ADD;
542 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
543 l_valid := FALSE;
544 END IF;
545 CLOSE c_cal_per;
546
547 -- Validation added as part of Bug#2412433
548 /** Validating that the Balance Type is not a STANDARD Balance **/
549
550 IF p_c_bal_type = 'STANDARD' THEN
551 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
552 IGS_GE_MSG_STACK.ADD;
553 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
554 l_valid := FALSE;
558 IF NOT l_valid THEN
555 END IF;
556
557 --if any of the parameter validation fails,process logs the error messages and error out.
559 retcode := 2;
560 RETURN;
561 END IF;
562
563 -- Bug 5018036. If person id group is passed then fetch records using c_pers_group else use cur_person.
564 IF p_n_pers_id_grp_id IS NOT NULL THEN
565 OPEN c_pers_group(p_n_pers_id_grp_id);
566 FETCH c_pers_group BULK COLLECT INTO v_tab_party_rec;
567 CLOSE c_pers_group;
568 ELSE
569 OPEN c_person(p_n_person_id);
570 FETCH c_person BULK COLLECT INTO v_tab_party_rec;
571 CLOSE c_person;
572 END IF;
573
574 /** Looping thru all identified persons for the Ad Hoc Group Waiver Process **/
575
576 IF v_tab_party_rec.COUNT > 0 THEN
577 -- Loop across all the Person ids identified for processing for Refunds
578 FOR l_n_cntr IN v_tab_party_rec.FIRST..v_tab_party_rec.LAST
579 LOOP
580 l_n_party_id := v_tab_party_rec(l_n_cntr);
581
582 OPEN c_per_id(l_n_party_id);
583 FETCH c_per_id INTO l_v_per_id;
584
585
586 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PROC_PERSON');
587 FND_MESSAGE.SET_TOKEN('NUMBER',l_v_per_id.person_number);
588 FND_MESSAGE.SET_TOKEN('NAME',l_v_per_id.full_name);
589 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
590
591 CLOSE c_per_id;
592 /* Removed the logging of subaccount related information, as a part of Bug # 2564643 */
593 l_msg_str := RPAD(lookup_desc('IGS_FI_LOCKBOX','INVOICE_NUMBER'),62) ||
594 RPAD(lookup_desc('IGS_FI_LOCKBOX','FEE_TYPE'),12) ||
595 LPAD(lookup_desc('IGS_FI_LOCKBOX','INVOICE_AMOUNT'),22);
596
597 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_str);
598
599
600 /** Looping thru all identified charges for a person for the Ad Hoc Group Waiver Process **/
601
602 FOR l_rec_per_chg IN c_per_chg( l_n_party_id ,
603 l_fee_cal_type ,
604 l_fee_ci_seq_num ,
605 /* Removed the parameters p_n_sub_acc_1 and p_n_sub_acc_2 as a part of Bug # 2564643 */
606 p_c_fee_type_1 ,
607 p_c_fee_type_2 ,
608 p_c_fee_type_3 )
609 LOOP
610
611 IF p_c_action = 'WAIVE' THEN
612
613 BEGIN
614
615 /** Logging message that for this charge the waiver is being done **/
616 /* Removed the logging of subaccount related information, as a part of Bug # 2564643 */
617 l_msg_str := RPAD(l_rec_per_chg.invoice_number,62) ||
618 RPAD(l_rec_per_chg.fee_type,12) ||
619 LPAD(TO_CHAR(l_rec_per_chg.invoice_amount),22);
620
621 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_str);
622
623 /** For every charge found for a person , inserting a waiver record in the table
624 igs_fi_inv_wav_det if Test Mode is NO.
625 This record is created for an identified charge and the balance type passed in
626 the parameter p_c_bal_type **/
627
628 IF p_c_test_flag = 'Y' THEN
629
630 igs_fi_inv_wav_det_pkg.before_dml
631 (p_action => 'INSERT',
632 x_rowid => l_rowid,
633 x_invoice_id => l_rec_per_chg.invoice_id,
634 x_balance_type => p_c_bal_type,
635 x_start_dt => l_start_dt,
636 x_end_dt => l_end_dt
637 );
638
639 ELSIF p_c_test_flag = 'N' THEN
640
641
642 igs_fi_inv_wav_det_pkg.insert_row
643 (x_rowid => l_rowid,
644 x_invoice_id => l_rec_per_chg.invoice_id,
645 x_balance_type => p_c_bal_type,
646 x_start_dt => l_start_dt,
647 x_end_dt => l_end_dt,
648 x_mode => 'R'
649 ) ;
650
651 END IF;
652
653 EXCEPTION
654 WHEN OTHERS THEN
655 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
656 END ;
657
658 ELSIF p_c_action = 'RELEASE' THEN
659
660 /** For every charge found for a person , checking whether waiver exists for this
661 charge and the balance type passed in the parameter p_c_bal_type .
662 If waiver record found in the table igs_fi_inv_wav_det, release the waiver **/
663
664 /** Release functionality is as follows :
665 If start date of the waiver record is greater than release date then delete the
666 waiver record.
667 If start date of waiver record is less than or equal to release date then
668 update the end date to release date.
669 Note : For releasing a waiver only those waiver records are considered whose
670 end date is either null or is greater than the release date.
671 This is so because waiver records with end date less than release date will
672 already be used for waiver before the release date **/
673 FOR l_chg_wav IN c_chg_wav(l_rec_per_chg.invoice_id,
674 p_c_bal_type,
675 l_release_dt)
676 LOOP
677
678 /** Logging message that for this charge the release is being done **/
679 /* Removed the logging of subaccount related information, as a part of Bug # 2564643 */
680 l_msg_str := RPAD(l_rec_per_chg.invoice_number,62) ||
681 RPAD(l_rec_per_chg.fee_type,12) ||
682 LPAD(TO_CHAR(l_rec_per_chg.invoice_amount),22);
683
684 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_str);
685
686 IF p_c_test_flag = 'N' THEN
687
688 IF l_chg_wav.start_dt > l_release_dt THEN
689
690 igs_fi_inv_wav_det_pkg.delete_row
691 (x_rowid => l_chg_wav.row_id
692 );
693
694 ELSE
695
696 igs_fi_inv_wav_det_pkg.update_row
697 (x_rowid => l_chg_wav.row_id,
698 x_invoice_id => l_chg_wav.invoice_id,
699 x_balance_type => l_chg_wav.balance_type,
700 x_start_dt => l_chg_wav.start_dt,
701 x_end_dt => l_release_dt,
702 x_mode => 'R'
703 ) ;
704
705 END IF;/** End if of IF l_chg_wav.start_dt > l_release_dt **/
706
707
708 END IF;/** End if of IF p_c_test_flag = 'N' **/
709
710 END LOOP; /** End of loop for waiver records **/
711
712 END IF; /** End if of IF checking for WAIVE or RELEASE **/
713
714 END LOOP; /** End of charges loop**/
715
716 END LOOP;/** End of person loop**/
717
718 END IF; /** End if of IF checking for v_tab_party_rec.COUNT > 0 **/
719
720
721 EXCEPTION
722
723 WHEN l_validation_exp THEN
724 retcode := 2;
725 WHEN OTHERS THEN
726 retcode := 2;
727 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
728 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
729
730 END group_waiver_proc ; /** procedure ends here **/
731
732 END igs_fi_ad_hoc_wav; /** End of package body **/
[Home] [Help]