[Home] [Help]
PACKAGE BODY: APPS.IGI_DUNN_POST_UPG_PKG
Source
1 PACKAGE BODY IGI_DUNN_POST_UPG_PKG as
2 /* $Header: igidunkb.pls 120.11 2008/02/19 09:27:27 mbremkum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGI_DUNN_POST_UPG_PKG';
5 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(255) := 'IGI.PLSQL.IGIDUNKB.IGI_DUNN_POST_UPG_PKG.';
13 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
14
15 PROCEDURE Put_Debug_Msg (
16 p_path IN VARCHAR2,
17 p_debug_msg IN VARCHAR2
18 );
19
20 PROCEDURE DUNNING_UPG(ERRBUF OUT NOCOPY VARCHAR2,
21 RETCODE OUT NOCOPY VARCHAR2) IS
22
23 l_old_dls_id NUMBER;
24 l_dls_id NUMBER;
25 l_name ar_dunning_letter_sets.name%TYPE;
26 l_old_ccy_code igi_dun_letter_set_cur.currency_code%TYPE;
27 l_ccy_code igi_dun_letter_set_cur.currency_code%TYPE;
28 l_old_customer_profile_id NUMBER;
29 l_aging_bct_id ar_aging_buckets.aging_bucket_id%TYPE;
30 l_not_exists BOOLEAN;
31 l_length NUMBER;
32
33 l_full_path VARCHAR2(255);
34
35 BEGIN
36
37 l_full_path := g_path || 'DUNNING_UPG';
38
39 FOR r_bkts IN c_bkts LOOP
40
41 SELECT ar_aging_buckets_s.NEXTVAL INTO l_aging_bct_id
42 FROM dual;
43
44 l_length := length(to_char(l_aging_bct_id));
45
46 INSERT INTO ar_aging_buckets
47 (
48 aging_bucket_id,
49 bucket_name,
50 status,
51 aging_type,
52 description,
53 created_by,
54 creation_date,
55 last_updated_by,
56 last_update_date,
57 last_update_login
58 )
59 VALUES(
60 l_aging_bct_id, -- aging_bucket_id
61 /*Changed the Bucket Name to Dunning Letter Set name - mbremkum*/
62 (substr(r_bkts.name,0,(20-l_length)) || l_aging_bct_id),-- bucket_name
63 'A', -- status
64 'INTTIER', -- aging_type
65 r_bkts.name, -- description
66 -1, -- created_by
67 SYSDATE, -- creation_date
68 -1, -- last_updated_by
69 SYSDATE, -- last_update_date
70 -1 -- last_update_login
71 )
72 RETURNING aging_bucket_id INTO l_aging_bucket_id;
73
74 FOR r_bkts_lines IN c_bkts_lines(r_bkts.dls_id) LOOP
75
76 INSERT INTO ar_aging_bucket_lines_b
77 (
78 aging_bucket_line_id,
79 aging_bucket_id,
80 bucket_sequence_num,
81 days_start,
82 days_to,
83 type,
84 created_by,
85 creation_date,
86 last_updated_by,
87 last_update_date,
88 last_update_login
89 )
90 VALUES(
91 ar_aging_bucket_lines_s.NEXTVAL,
92 l_aging_bucket_id,
93 r_bkts_lines.dunning_line_num,
94 r_bkts_lines.range_of_days_from,
95 r_bkts_lines.range_of_days_to,
96 'CURRENT',
97 -1,
98 SYSDATE,
99 -1,
100 SYSDATE,
101 -1
102 );
103
104 END LOOP;
105
106 END LOOP;
107
108 FOR r_aging_bkts IN c_aging_bkts LOOP
109
110 BEGIN
111
112 INSERT INTO ar_charge_schedules
113 (
114 schedule_id,
115 schedule_name,
116 schedule_description,
117 object_version_number,
118 created_by,
119 creation_date,
120 last_updated_by,
121 last_update_date,
122 last_update_login
123 )
124 VALUES(
125 ar_charge_schedules_s.NEXTVAL, -- schedule_id
126 r_aging_bkts.name || '_' || r_aging_bkts.ccy_code, -- schedule_name
127 r_aging_bkts.name || '_' || r_aging_bkts.ccy_code, -- schedule_description
128 1, -- object_version_number
129 -1, -- created_by
130 SYSDATE, -- creation_date
131 -1, -- last_updated_by
132 SYSDATE, -- last_update_date
133 -1 -- last_update_login
134 )
135 RETURNING schedule_id INTO l_schedule_id;
136
137 IF (g_debug_mode = 'Y') THEN
138 Put_Debug_Msg(l_full_path, 'Dunning Letter Set Name: ' || r_aging_bkts.name);
139 Put_Debug_Msg(l_full_path, 'Currency Code: ' || r_aging_bkts.ccy_code);
140 Put_Debug_Msg(l_full_path, 'Dunning Letter Set ID: ' || r_aging_bkts.dls_id);
141 Put_Debug_Msg(l_full_path, 'Schedule ID: ' || l_schedule_id);
142 END IF;
143 --dbms_output.put_line('Inserted Rows in ar_charge_schedules : '|| SQL%ROWCOUNT);
144
145
146 UPDATE hz_cust_profile_amts hcpa
147 SET (interest_type, /*interest_fixed_amount,*/ interest_schedule_id,
148 last_updated_by, last_update_date) =
149 (SELECT distinct DECODE(idls.charge_per_invoice_flag, 'Y', 'CHARGES_SCHEDULE', 'N', 'CHARGE_PER_TIER'),
150 /*interest_fixed_amount column is used if interest_type is 'FIXED_AMOUNT' - mbremkum*/
151 /*DECODE(idls.charge_per_invoice_flag, 'Y', idclsl.invoice_charge_amount, NULL),*/
152 /*Schedule ID is always populated if interest_type is
153 'CHARGES_SCHEDULE' or 'CHARGE_PER_TIER' - mbremkum*/
154 l_schedule_id,
155 -1, SYSDATE
156 FROM igi_dun_letter_sets idls,
157 igi_dun_cust_letter_set_lines idclsl
158 /*Added the below condition so that update is based on
159 dunning_letter_set_id from the cursor - mbremkum*/
160 WHERE idls.dunning_letter_set_id = r_aging_bkts.dls_id
161 AND idls.dunning_letter_set_id = idclsl.dunning_letter_set_id
162 AND idclsl.customer_profile_id = hcpa.cust_account_profile_id
163 AND idclsl.currency_code = hcpa.currency_code
164 AND hcpa.currency_code = r_aging_bkts.ccy_code
165 AND NVL(idclsl.site_use_id,-99)= NVL(hcpa.site_use_id, -99))
166 WHERE EXISTS (SELECT 'Y'
167 FROM igi_dun_letter_sets idls,
168 igi_dun_cust_letter_set_lines idclsl
169 WHERE idls.dunning_letter_set_id = r_aging_bkts.dls_id
170 AND idls.dunning_letter_set_id = idclsl.dunning_letter_set_id
171 AND idclsl.customer_profile_id = hcpa.cust_account_profile_id
172 AND idclsl.currency_code = hcpa.currency_code
173 AND hcpa.currency_code = r_aging_bkts.ccy_code
174 AND NVL(idclsl.site_use_id,-99)= NVL(hcpa.site_use_id, -99));
175
176 /*Added the below query to update schedule_id and interest_type in Customer Profile Class Amount*/
177
178 UPDATE hz_cust_prof_class_amts hcpca
179 SET (interest_type, interest_schedule_id,
180 last_updated_by, last_update_date) =
181 (SELECT distinct DECODE(idls.charge_per_invoice_flag, 'Y', 'CHARGES_SCHEDULE', 'N', 'CHARGE_PER_TIER'),
182 l_schedule_id,
183 -1, SYSDATE
184 FROM igi_dun_letter_sets idls,
185 igi_dun_cust_letter_set_lines idclsl
186 WHERE idls.dunning_letter_set_id = r_aging_bkts.dls_id
187 AND idls.dunning_letter_set_id = idclsl.dunning_letter_set_id
188 AND idclsl.customer_profile_class_id = hcpca.profile_class_id
189 AND idclsl.currency_code = hcpca.currency_code
190 AND hcpca.currency_code = r_aging_bkts.ccy_code)
191 WHERE EXISTS (SELECT 'Y'
192 FROM igi_dun_letter_sets idls,
193 igi_dun_cust_letter_set_lines idclsl
194 WHERE idls.dunning_letter_set_id = r_aging_bkts.dls_id
195 AND idls.dunning_letter_set_id = idclsl.dunning_letter_set_id
196 AND idclsl.customer_profile_class_id = hcpca.profile_class_id
197 AND idclsl.currency_code = hcpca.currency_code
198 AND hcpca.currency_code = r_aging_bkts.ccy_code);
199
200 --dbms_output.put_line('Updated Rows in hz_cust_profile_amts : '|| SQL%ROWCOUNT);
201
202 SELECT aging_bucket_id INTO l_aging_bucket_id
203 FROM ar_aging_buckets
204 WHERE description = r_aging_bkts.name;
205
206 IF (g_debug_mode = 'Y') THEN
207 Put_Debug_Msg(l_full_path, 'Aging bucket ID: ' || l_aging_bucket_id);
208 END IF;
209
210 --dbms_output.put_line('Inserted Rows in ar_aging_buckets : '|| SQL%ROWCOUNT);
211
212 INSERT INTO ar_charge_schedule_hdrs
213 (
214 schedule_header_id,
215 schedule_id,
216 schedule_header_type,
217 aging_bucket_id,
218 start_date,
219 end_date,
220 status,
221 object_version_number,
222 created_by,
223 creation_date,
224 last_updated_by,
225 last_update_date,
226 last_update_login
227 )
228 VALUES(
229 ar_charge_schedule_hdrs_s.NEXTVAL,
230 l_schedule_id,
231 'AMOUNT',
232 l_aging_bucket_id,
233 to_date('01-01-1900', 'DD-MM-YYYY'),
234 null,
235 'A',
236 1,
237 -1,
238 SYSDATE,
239 -1,
240 SYSDATE,
241 -1
242 )
243 RETURNING schedule_header_id INTO l_schedule_header_id;
244
245 IF (g_debug_mode = 'Y') THEN
246 Put_Debug_Msg(l_full_path, 'Charge Schedule Header ID: ' || l_schedule_header_id);
247 END IF;
248
249 --dbms_output.put_line('Inserted Rows in ar_charge_schedule_hdrs : '|| SQL%ROWCOUNT);
250
251 FOR r_aging_bkt_lines IN c_aging_bkt_lines(r_aging_bkts.dls_id, r_aging_bkts.ccy_code) LOOP
252
253 SELECT aging_bucket_line_id INTO l_aging_bucket_line_id
254 FROM ar_aging_bucket_lines_b
255 WHERE aging_bucket_id = l_aging_bucket_id
256 AND bucket_sequence_num = r_aging_bkt_lines.dunning_line_num
257 AND days_start = r_aging_bkt_lines.range_of_days_from
258 AND days_to = r_aging_bkt_lines.range_of_days_to;
259
260 IF (g_debug_mode = 'Y') THEN
261 Put_Debug_Msg(l_full_path, 'Aging Bucket Line ID: ' || l_aging_bucket_line_id);
262 END IF;
263
264
265 --dbms_output.put_line('Inserted Rows in ar_aging_bucket_lines_b : '|| SQL%ROWCOUNT);
266
267 INSERT INTO ar_charge_schedule_lines
268 (
269 schedule_line_id,
270 schedule_header_id,
271 schedule_id,
272 aging_bucket_id,
273 aging_bucket_line_id,
274 amount,
275 rate,
276 object_version_number,
277 created_by,
278 creation_date,
279 last_updated_by,
280 last_update_date,
281 last_update_login
282 )
283 VALUES(
284 ar_charge_schedule_lines_s.NEXTVAL,
285 l_schedule_header_id,
286 l_schedule_id,
287 l_aging_bucket_id,
288 l_aging_bucket_line_id,
289 decode(r_aging_bkt_lines.charge_type, 'Y',
290 r_aging_bkt_lines.invoice_charge_amount,
291 'N', r_aging_bkt_lines.letter_charge_amount),
292 NULL,
293 1,
294 -1,
295 SYSDATE,
296 -1,
297 SYSDATE,
298 -1
299 );
300
301 IF (g_debug_mode = 'Y') THEN
302 Put_Debug_Msg(l_full_path, 'Inserted Rows in ar_charge_schedule_lines : '|| SQL%ROWCOUNT);
303 END IF;
304
305 --dbms_output.put_line('Inserted Rows in ar_charge_schedule_lines : '|| SQL%ROWCOUNT);
306
307 END LOOP;
308
309 EXCEPTION
310
311 WHEN OTHERS THEN
312 IF (g_debug_mode = 'Y') THEN
313 Put_Debug_Msg(l_full_path, SQLERRM);
314 END IF;
315 ROLLBACK;
316 APP_EXCEPTION.Raise_Exception;
317 END;
318 END LOOP;
319
320 /*Create new charge schedules when amounts are updated at customer level - Start - mbremkum*/
321
322 IF (g_debug_mode = 'Y') THEN
323 Put_Debug_Msg(l_full_path, 'Create new schedules and override existing if amounts updated at customer level');
324 END IF;
325
326 FOR r_aging_bkts_site IN c_aging_bkts_site LOOP
327
328 FOR r_override_dunning_letter IN c_override_dunning_letter(r_aging_bkts_site.dls_id, r_aging_bkts_site.ccy_code, r_aging_bkts_site.charge_type) LOOP
329
330 l_old_dls_id := -9999;
331 l_old_ccy_code := 'XXX';
332 l_old_customer_profile_id := -9999;
333
334 FOR r_aging_bkt_lines_site IN c_aging_bkt_lines_site(r_override_dunning_letter.dls_id, r_override_dunning_letter.ccy_code,r_override_dunning_letter.customer_profile_id ) LOOP
335
336 l_not_exists := TRUE;
337
338 BEGIN
339
340 IF (l_old_dls_id <> r_aging_bkt_lines_site.dunning_letter_set_id OR l_old_ccy_code <> r_aging_bkt_lines_site.ccy_code OR l_old_customer_profile_id <> r_aging_bkt_lines_site.customer_profile_id) THEN
341
342 BEGIN
343
344 SELECT distinct adls.dunning_letter_set_id,
345 adls.name,
346 idlsc.currency_code INTO l_dls_id, l_name, l_ccy_code
347 FROM igi_dun_letter_set_cur idlsc,
348 ar_dunning_letter_sets adls,
349 igi_dun_letter_sets idls
350 WHERE adls.dunning_letter_set_id = r_aging_bkt_lines_site.dunning_letter_set_id
351 AND idlsc.dunning_letter_set_id = r_aging_bkt_lines_site.dunning_letter_set_id
352 AND idls.dunning_letter_set_id = r_aging_bkt_lines_site.dunning_letter_set_id
353 AND idlsc.currency_code = r_aging_bkt_lines_site.ccy_code
354 AND idls.use_dunning_flag = 'Y'
355 AND NOT EXISTS (SELECT 'Y'
356 FROM ar_charge_schedules acs
357 WHERE acs.schedule_name = adls.name || '_' || idlsc.currency_code || '_' || r_aging_bkt_lines_site.customer_profile_id);
358 EXCEPTION
359
360 WHEN NO_DATA_FOUND THEN
361 l_not_exists := FALSE;
362
363 END;
364
365 END IF;
366
367 IF ( (l_old_dls_id <> r_aging_bkt_lines_site.dunning_letter_set_id OR l_old_ccy_code <> r_aging_bkt_lines_site.ccy_code OR l_old_customer_profile_id <> r_aging_bkt_lines_site.customer_profile_id) AND l_not_exists) THEN
368 /*Create charge schedule for each unique Dunning Letter Set ID*/
369
370 l_old_dls_id := r_aging_bkt_lines_site.dunning_letter_set_id;
371 l_old_ccy_code := r_aging_bkt_lines_site.ccy_code;
372 l_old_customer_profile_id := r_aging_bkt_lines_site.customer_profile_id;
373
374 /*Check if a Charge Scedule already exists by that name. Also Fetch the Dunning Letter Name and Currency*/
375 IF (g_debug_mode = 'Y') THEN
376 Put_Debug_Msg(l_full_path, 'Creating new Charge Schedule for Dunning Letter Set ID: ' || r_aging_bkt_lines_site.dunning_letter_set_id || ' and Currency: ' ||r_aging_bkt_lines_site.ccy_code );
377 END IF;
378
379 INSERT INTO ar_charge_schedules
380 (
381 schedule_id,
382 schedule_name,
383 schedule_description,
384 object_version_number,
385 created_by,
386 creation_date,
387 last_updated_by,
388 last_update_date,
389 last_update_login
390 )
391 VALUES(
392 ar_charge_schedules_s.NEXTVAL, -- schedule_id
393 l_name || '_' || l_ccy_code || '_' || r_aging_bkt_lines_site.customer_profile_id, -- schedule_name
394 l_name || '_' || l_ccy_code || '_' || r_aging_bkt_lines_site.customer_profile_id, -- schedule_description
395 1, -- object_version_number
396 -1, -- created_by
397 SYSDATE, -- creation_date
398 -1, -- last_updated_by
399 SYSDATE, -- last_update_date
400 -1 -- last_update_login
401 )
402 RETURNING schedule_id INTO l_schedule_id;
403
404 IF (g_debug_mode = 'Y') THEN
405 Put_Debug_Msg(l_full_path, 'Dunning Letter Set Name(Site Override): ' || l_name);
406 Put_Debug_Msg(l_full_path, 'Currency Code(Site Override): ' || l_ccy_code);
407 Put_Debug_Msg(l_full_path, 'Dunning Letter Set ID(Site Override): ' || l_dls_id);
408 Put_Debug_Msg(l_full_path, 'Customer Profile ID(Site Override): ' || r_aging_bkt_lines_site.customer_profile_id);
409 Put_Debug_Msg(l_full_path, 'Schedule ID(Site Override): ' || l_schedule_id);
410 END IF;
411
412 UPDATE hz_cust_profile_amts hcpa
413 SET (interest_type, interest_schedule_id,
414 last_updated_by, last_update_date) =
415 (SELECT distinct DECODE(idls.charge_per_invoice_flag, 'Y', 'CHARGES_SCHEDULE', 'N', 'CHARGE_PER_TIER'),
416 l_schedule_id,-1, SYSDATE
417 FROM igi_dun_letter_sets idls,
418 igi_dun_cust_letter_set_lines idclsl
419 WHERE idls.dunning_letter_set_id = l_dls_id
420 AND idls.dunning_letter_set_id = idclsl.dunning_letter_set_id
421 AND hcpa.cust_account_profile_id = r_aging_bkt_lines_site.customer_profile_id
422 AND hcpa.currency_code = r_aging_bkt_lines_site.ccy_code
423 AND NVL(hcpa.site_use_id, -99) = NVL(r_aging_bkt_lines_site.site_use_id, -99))
424 WHERE EXISTS (SELECT 'Y'
425 FROM igi_dun_letter_sets idls,
426 igi_dun_cust_letter_set_lines idclsl
427 WHERE idls.dunning_letter_set_id = l_dls_id
428 AND idls.dunning_letter_set_id = idclsl.dunning_letter_set_id
429 AND hcpa.cust_account_profile_id = r_aging_bkt_lines_site.customer_profile_id
430 AND hcpa.currency_code = r_aging_bkt_lines_site.ccy_code
431 AND NVL(hcpa.site_use_id, -99) = NVL(r_aging_bkt_lines_site.site_use_id, -99));
432
433 SELECT aging_bucket_id INTO l_aging_bucket_id FROM ar_aging_buckets
434 WHERE description = r_aging_bkt_lines_site.name;
435
436 INSERT INTO ar_charge_schedule_hdrs
437 (
438 schedule_header_id,
439 schedule_id,
440 schedule_header_type,
441 aging_bucket_id,
442 start_date,
443 end_date,
444 status,
445 object_version_number,
446 created_by,
447 creation_date,
448 last_updated_by,
449 last_update_date,
450 last_update_login
451 )
452 VALUES(
453 ar_charge_schedule_hdrs_s.NEXTVAL,
454 l_schedule_id,
455 'AMOUNT',
456 l_aging_bucket_id,
457 to_date('01-01-1900', 'DD-MM-YYYY'),
458 null,
459 'A',
460 1,
461 -1,
462 SYSDATE,
463 -1,
464 SYSDATE,
465 -1
466 )
467 RETURNING schedule_header_id INTO l_schedule_header_id;
468
469 IF (g_debug_mode = 'Y') THEN
470 Put_Debug_Msg(l_full_path, 'Charge Schedule Header ID(Site Override): ' || l_schedule_header_id);
471 END IF;
472
473 END IF;
474
475 IF l_not_exists THEN
476
477 IF (g_debug_mode = 'Y') THEN
478 Put_Debug_Msg(l_full_path, 'Dunning Line Number(Site Override): ' || r_aging_bkt_lines_site.dunning_line_num);
479 Put_Debug_Msg(l_full_path, 'Range of days from(Site Override): ' || r_aging_bkt_lines_site.range_of_days_from);
480 Put_Debug_Msg(l_full_path, 'Range of days to(Site Override): ' || r_aging_bkt_lines_site.range_of_days_to);
481 END IF;
482
483 SELECT aging_bucket_line_id INTO l_aging_bucket_line_id
484 FROM ar_aging_bucket_lines_b
485 WHERE aging_bucket_id = l_aging_bucket_id
486 AND bucket_sequence_num = r_aging_bkt_lines_site.dunning_line_num
487 AND days_start = r_aging_bkt_lines_site.range_of_days_from
488 AND days_to = r_aging_bkt_lines_site.range_of_days_to;
489
490 IF (g_debug_mode = 'Y') THEN
491 Put_Debug_Msg(l_full_path, 'Aging Bucket Line ID(Site Override): ' || l_aging_bucket_line_id);
492 END IF;
493
494 INSERT INTO ar_charge_schedule_lines
495 (
496 schedule_line_id,
497 schedule_header_id,
498 schedule_id,
499 aging_bucket_id,
500 aging_bucket_line_id,
501 amount,
502 rate,
503 object_version_number,
504 created_by,
505 creation_date,
506 last_updated_by,
507 last_update_date,
508 last_update_login
509 )
510 VALUES(
511 ar_charge_schedule_lines_s.NEXTVAL,
512 l_schedule_header_id,
513 l_schedule_id,
514 l_aging_bucket_id,
515 l_aging_bucket_line_id,
516 decode(r_aging_bkt_lines_site.charge_type, 'Y', r_aging_bkt_lines_site.invoice_charge_amount,
517 'N', r_aging_bkt_lines_site.letter_charge_amount),
518 NULL,
519 1,
520 -1,
521 SYSDATE,
522 -1,
523 SYSDATE,
524 -1
525 );
526 IF (g_debug_mode = 'Y') THEN
527 Put_Debug_Msg(l_full_path, 'Inserted Rows in ar_charge_schedule_lines(Site Override): '|| SQL%ROWCOUNT);
528 END IF;
529
530 END IF;
531
532 EXCEPTION
533
534 WHEN OTHERS THEN
535 IF (g_debug_mode = 'Y') THEN
536 Put_Debug_Msg(l_full_path, SQLERRM);
537 END IF;
538 ROLLBACK;
539 APP_EXCEPTION.Raise_Exception;
540 END;
541
542 END LOOP;
543
544 END LOOP;
545
546 END LOOP;
547
548 FOR r_aging_bkts_uu IN c_aging_bkts_uu LOOP
549
550 BEGIN
551
552 INSERT INTO ar_charge_schedules
553 (
554 schedule_id,
555 schedule_name,
556 schedule_description,
557 object_version_number,
558 created_by,
559 creation_date,
560 last_updated_by,
561 last_update_date,
562 last_update_login
563 )
564 VALUES(
565 ar_charge_schedules_s.NEXTVAL,
566 r_aging_bkts_uu.name || '_' || r_aging_bkts_uu.ccy_code || '_'
567 || decode(r_aging_bkts_uu.charge_type, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'),
568 r_aging_bkts_uu.name || '_' || r_aging_bkts_uu.ccy_code|| '_'
569 || decode(r_aging_bkts_uu.charge_type, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'),
570 1,
571 -1,
572 SYSDATE,
573 -1,
574 SYSDATE,
575 -1
576 )
577 RETURNING schedule_id INTO l_schedule_id;
578
579 IF (g_debug_mode = 'Y') THEN
580 Put_Debug_Msg(l_full_path, 'Dunning Letter Set Name (Un Used): ' || r_aging_bkts_uu.name);
581 Put_Debug_Msg(l_full_path, 'Currency Code (Un Used): ' || r_aging_bkts_uu.ccy_code);
582 Put_Debug_Msg(l_full_path, 'Dunning Letter Set ID (Un Used): ' || r_aging_bkts_uu.dls_id);
583 Put_Debug_Msg(l_full_path, 'Schedule ID (Un Used): ' || l_schedule_id);
584 END IF;
585
586 SELECT aging_bucket_id INTO l_aging_bucket_id
587 FROM ar_aging_buckets
588 WHERE description = r_aging_bkts_uu.name;
589
590 INSERT INTO ar_charge_schedule_hdrs
591 (
592 schedule_header_id,
593 schedule_id,
594 schedule_header_type,
595 aging_bucket_id,
596 start_date,
597 end_date,
598 status,
599 object_version_number,
600 created_by,
601 creation_date,
602 last_updated_by,
603 last_update_date,
604 last_update_login
605 )
606 VALUES(
607 ar_charge_schedule_hdrs_s.NEXTVAL,
608 l_schedule_id,
609 'AMOUNT',
610 l_aging_bucket_id,
611 to_date('01-01-1900', 'DD-MM-YYYY'),
612 null,
613 'A',
614 1,
615 -1,
616 SYSDATE,
617 -1,
618 SYSDATE,
619 -1
620 )
621 RETURNING schedule_header_id INTO l_schedule_header_id;
622
623 FOR r_aging_bkt_lines IN c_aging_bkt_lines(r_aging_bkts_uu.dls_id, r_aging_bkts_uu.ccy_code) LOOP
624
625 SELECT aging_bucket_line_id INTO l_aging_bucket_line_id
626 FROM ar_aging_bucket_lines_b
627 WHERE aging_bucket_id = l_aging_bucket_id
628 AND bucket_sequence_num = r_aging_bkt_lines.dunning_line_num
629 AND days_start = r_aging_bkt_lines.range_of_days_from
630 AND days_to = r_aging_bkt_lines.range_of_days_to;
631
632 IF (g_debug_mode = 'Y') THEN
633 Put_Debug_Msg(l_full_path, 'Aging Bucket Line ID (Un Used): ' || l_aging_bucket_line_id);
634 END IF;
635
636 INSERT INTO ar_charge_schedule_lines
637 (
638 schedule_line_id,
639 schedule_header_id,
640 schedule_id,
641 aging_bucket_id,
642 aging_bucket_line_id,
643 amount,
644 rate,
645 object_version_number,
646 created_by,
647 creation_date,
648 last_updated_by,
649 last_update_date,
650 last_update_login
651 )
652 VALUES(
653 ar_charge_schedule_lines_s.NEXTVAL,
654 l_schedule_header_id,
655 l_schedule_id,
656 l_aging_bucket_id,
657 l_aging_bucket_line_id,
658 decode(r_aging_bkt_lines.charge_type, 'Y',
659 r_aging_bkt_lines.letter_charge_amount,
660 'N', r_aging_bkt_lines.invoice_charge_amount),
661 NULL,
662 1,
663 -1,
664 SYSDATE,
665 -1,
666 SYSDATE,
667 -1
668 );
669
670 IF (g_debug_mode = 'Y') THEN
671 Put_Debug_Msg(l_full_path, 'Inserted Rows in ar_charge_schedule_lines (Un Used): '|| SQL%ROWCOUNT);
672 END IF;
673
674 END LOOP; /*FOR r_aging_bkt_lines IN c_aging_bkt_lines*/
675
676 EXCEPTION
677
678 WHEN OTHERS THEN
679 IF (g_debug_mode = 'Y') THEN
680 Put_Debug_Msg(l_full_path, SQLERRM);
681 END IF;
682 ROLLBACK;
683 APP_EXCEPTION.Raise_Exception;
684 END;
685
686 END LOOP; /*FOR r_aging_bkts_uu IN c_aging_bkts_uu LOOP*/
687
688 FOR r_aging_bkts_uu_site IN c_aging_bkts_uu_site LOOP
689
690 FOR r_override_dunning_letter_uu IN c_override_dunning_letter_uu(r_aging_bkts_uu_site.dls_id, r_aging_bkts_uu_site.ccy_code, r_aging_bkts_uu_site.charge_type) LOOP
691
692 l_old_dls_id := -9999;
693 l_old_ccy_code := 'XXX';
694 l_old_customer_profile_id := -9999;
695
696 FOR r_aging_bkt_lines_site IN c_aging_bkt_lines_site(r_override_dunning_letter_uu.dls_id, r_override_dunning_letter_uu.ccy_code,r_override_dunning_letter_uu.customer_profile_id ) LOOP
697
698 l_not_exists := TRUE;
699
700 BEGIN
701
702 IF (l_old_dls_id <> r_aging_bkt_lines_site.dunning_letter_set_id OR l_old_ccy_code <> r_aging_bkt_lines_site.ccy_code OR l_old_customer_profile_id <> r_aging_bkt_lines_site.customer_profile_id) THEN
703
704 BEGIN
705
706 SELECT distinct adls.dunning_letter_set_id,
707 adls.name,
708 idlsc.currency_code INTO l_dls_id, l_name, l_ccy_code
709 FROM igi_dun_letter_set_cur idlsc,
710 ar_dunning_letter_sets adls,
711 igi_dun_letter_sets idls
712 WHERE adls.dunning_letter_set_id = r_aging_bkt_lines_site.dunning_letter_set_id
713 AND idlsc.dunning_letter_set_id = r_aging_bkt_lines_site.dunning_letter_set_id
714 AND idls.dunning_letter_set_id = r_aging_bkt_lines_site.dunning_letter_set_id
715 AND idlsc.currency_code = r_aging_bkt_lines_site.ccy_code
716 AND idls.use_dunning_flag = 'Y'
717 AND NOT EXISTS (SELECT 'Y'
718 FROM ar_charge_schedules acs
719 WHERE acs.schedule_name = adls.name || '_' || idlsc.currency_code || '_' || r_aging_bkt_lines_site.customer_profile_id || '_'
720 || decode(r_aging_bkt_lines_site.charge_type, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'));
721 EXCEPTION
722
723 WHEN NO_DATA_FOUND THEN
724 l_not_exists := FALSE;
725
726 END;
727
728 END IF;
729
730 IF ( (l_old_dls_id <> r_aging_bkt_lines_site.dunning_letter_set_id OR l_old_ccy_code <> r_aging_bkt_lines_site.ccy_code OR l_old_customer_profile_id <> r_aging_bkt_lines_site.customer_profile_id) AND l_not_exists) THEN
731 /*Create charge schedule for each unique Dunning Letter Set ID*/
732
733 l_old_dls_id := r_aging_bkt_lines_site.dunning_letter_set_id;
734 l_old_ccy_code := r_aging_bkt_lines_site.ccy_code;
735 l_old_customer_profile_id := r_aging_bkt_lines_site.customer_profile_id;
736
737 /*Check if a Charge Scedule already exists by that name. Also Fetch the Dunning Letter Name and Currency*/
738 IF (g_debug_mode = 'Y') THEN
739 Put_Debug_Msg(l_full_path, 'Creating new Charge Schedule for Dunning Letter Set ID (Site Override - Un Used): ' || r_aging_bkt_lines_site.dunning_letter_set_id || ' and Currency: ' ||r_aging_bkt_lines_site.ccy_code );
740 END IF;
741
742 INSERT INTO ar_charge_schedules
743 (
744 schedule_id,
745 schedule_name,
746 schedule_description,
747 object_version_number,
748 created_by,
749 creation_date,
750 last_updated_by,
751 last_update_date,
752 last_update_login
753 )
754 VALUES(
755 ar_charge_schedules_s.NEXTVAL, -- schedule_id
756 l_name || '_' || l_ccy_code || '_' || r_aging_bkt_lines_site.customer_profile_id || '_'
757 || decode(r_aging_bkt_lines_site.charge_type, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'), -- schedule_name
758 l_name || '_' || l_ccy_code || '_' || r_aging_bkt_lines_site.customer_profile_id || '_'
759 || decode(r_aging_bkt_lines_site.charge_type, 'Y', 'PER_LETTER', 'N', 'PER_INVOICE'), -- schedule_description
760 1, -- object_version_number
761 -1, -- created_by
762 SYSDATE, -- creation_date
763 -1, -- last_updated_by
764 SYSDATE, -- last_update_date
765 -1 -- last_update_login
766 )
767 RETURNING schedule_id INTO l_schedule_id;
768
769 IF (g_debug_mode = 'Y') THEN
770 Put_Debug_Msg(l_full_path, 'Dunning Letter Set Name(Site Override - Un Used): ' || l_name);
771 Put_Debug_Msg(l_full_path, 'Currency Code(Site Override - Un Used): ' || l_ccy_code);
772 Put_Debug_Msg(l_full_path, 'Dunning Letter Set ID(Site Override - Un Used): ' || l_dls_id);
773 Put_Debug_Msg(l_full_path, 'Customer Profile ID(Site Override - Un Used): ' || r_aging_bkt_lines_site.customer_profile_id);
774 Put_Debug_Msg(l_full_path, 'Schedule ID(Site Override - Un Used): ' || l_schedule_id);
775 END IF;
776
777 SELECT aging_bucket_id INTO l_aging_bucket_id FROM ar_aging_buckets
778 WHERE description = r_aging_bkt_lines_site.name;
779
780 INSERT INTO ar_charge_schedule_hdrs
781 (
782 schedule_header_id,
783 schedule_id,
784 schedule_header_type,
785 aging_bucket_id,
786 start_date,
787 end_date,
788 status,
789 object_version_number,
790 created_by,
791 creation_date,
792 last_updated_by,
793 last_update_date,
794 last_update_login
795 )
796 VALUES(
797 ar_charge_schedule_hdrs_s.NEXTVAL,
798 l_schedule_id,
799 'AMOUNT',
800 l_aging_bucket_id,
801 to_date('01-01-1900', 'DD-MM-YYYY'),
802 null,
803 'A',
804 1,
805 -1,
806 SYSDATE,
807 -1,
808 SYSDATE,
809 -1
810 )
811 RETURNING schedule_header_id INTO l_schedule_header_id;
812
813 IF (g_debug_mode = 'Y') THEN
814 Put_Debug_Msg(l_full_path, 'Charge Schedule Header ID(Site Override): ' || l_schedule_header_id);
815 END IF;
816
817 END IF;
818
819 IF l_not_exists THEN
820
821 IF (g_debug_mode = 'Y') THEN
822 Put_Debug_Msg(l_full_path, 'Dunning Line Number(Site Override - Un Used): ' || r_aging_bkt_lines_site.dunning_line_num);
823 Put_Debug_Msg(l_full_path, 'Range of days from(Site Override - Un Used): ' || r_aging_bkt_lines_site.range_of_days_from);
824 Put_Debug_Msg(l_full_path, 'Range of days to(Site Override - Un Used): ' || r_aging_bkt_lines_site.range_of_days_to);
825 END IF;
826
827 SELECT aging_bucket_line_id INTO l_aging_bucket_line_id
828 FROM ar_aging_bucket_lines_b
829 WHERE aging_bucket_id = l_aging_bucket_id
830 AND bucket_sequence_num = r_aging_bkt_lines_site.dunning_line_num
831 AND days_start = r_aging_bkt_lines_site.range_of_days_from
832 AND days_to = r_aging_bkt_lines_site.range_of_days_to;
833
834 IF (g_debug_mode = 'Y') THEN
835 Put_Debug_Msg(l_full_path, 'Aging Bucket Line ID(Site Override - Un Used): ' || l_aging_bucket_line_id);
836 END IF;
837
838 INSERT INTO ar_charge_schedule_lines
839 (
840 schedule_line_id,
841 schedule_header_id,
842 schedule_id,
843 aging_bucket_id,
844 aging_bucket_line_id,
845 amount,
846 rate,
847 object_version_number,
848 created_by,
849 creation_date,
850 last_updated_by,
851 last_update_date,
852 last_update_login
853 )
854 VALUES(
855 ar_charge_schedule_lines_s.NEXTVAL,
856 l_schedule_header_id,
857 l_schedule_id,
858 l_aging_bucket_id,
859 l_aging_bucket_line_id,
860 decode(r_aging_bkt_lines_site.charge_type, 'Y', r_aging_bkt_lines_site.letter_charge_amount,
861 'N', r_aging_bkt_lines_site.invoice_charge_amount),
862 NULL,
863 1,
864 -1,
865 SYSDATE,
866 -1,
867 SYSDATE,
868 -1
869 );
870 IF (g_debug_mode = 'Y') THEN
871 Put_Debug_Msg(l_full_path, 'Inserted Rows in ar_charge_schedule_lines(Site Override - Un Used): '|| SQL%ROWCOUNT);
872 END IF;
873
874 END IF;
875
876 EXCEPTION
877
878 WHEN OTHERS THEN
879 IF (g_debug_mode = 'Y') THEN
880 Put_Debug_Msg(l_full_path, SQLERRM);
881 END IF;
882 ROLLBACK;
883 APP_EXCEPTION.Raise_Exception;
884 END;
885
886 END LOOP;
887
888 END LOOP;
889
890 END LOOP;
891
892 /*End - Create new charge schedules when amounts are updated at customer level - mbremkum*/
893
894 -- Update Customer Profiles
895 UPDATE hz_customer_profiles hcp
896 SET late_charge_type = (SELECT DECODE(dunning_charge_type, 'A', 'ADJ',
897 'I', 'INV', dunning_charge_type)
898 FROM igi_dun_cust_prof idcp
899 WHERE idcp.customer_profile_id = hcp.cust_account_profile_id
900 AND idcp.use_dunning_flag = 'Y'),
901 /*Added the following to update hz_customer_profiles if Dunning Flaf is enabled - mbremkum*/
902 dunning_letters = (SELECT idcp.use_dunning_flag
903 FROM igi_dun_cust_prof idcp
904 WHERE idcp.customer_profile_id = hcp.cust_account_profile_id
905 AND idcp.use_dunning_flag = 'Y')
906 WHERE EXISTS (SELECT 'Y'
907 FROM igi_dun_cust_prof idcp1
908 WHERE idcp1.customer_profile_id = hcp.cust_account_profile_id
909 AND idcp1.use_dunning_flag = 'Y');
910
911 --dbms_output.put_line('Updated Rows in hz_customer_profiles : '|| SQL%ROWCOUNT);
912
913 -- Update Customer Profile Classes
914 UPDATE hz_cust_profile_classes hcpc
915 SET late_charge_type = (SELECT DECODE(dunning_charge_type, 'A', 'ADJ',
916 'I', 'INV', dunning_charge_type)
917 FROM igi_dun_cust_prof_class idcpc
918 WHERE idcpc.customer_profile_class_id = hcpc.profile_class_id
919 AND idcpc.use_dunning_flag = 'Y'),
920 /*Added the following to update hz_cust_profile_classes if Dunning Flaf is enabled - mbremkum*/
921 dunning_letters = (SELECT idcpc.use_dunning_flag
922 FROM igi_dun_cust_prof_class idcpc
923 WHERE idcpc.customer_profile_class_id = hcpc.profile_class_id
924 AND idcpc.use_dunning_flag = 'Y')
925 WHERE EXISTS (SELECT 'Y'
926 FROM igi_dun_cust_prof_class idcpc1
927 WHERE idcpc1.customer_profile_class_id = hcpc.profile_class_id
928 AND idcpc1.use_dunning_flag = 'Y');
929
930 COMMIT;
931
932 --dbms_output.put_line('Updated Rows in hz_cust_profile_classes : '|| SQL%ROWCOUNT);
933
934
935 EXCEPTION
936
937 WHEN OTHERS THEN
938 errbuf := SQLERRM;
939 retcode := 2;
940 IF (g_debug_mode = 'Y') THEN
941 Put_Debug_Msg(l_full_path, 'Dunning Migration Failed with: ' || errbuf);
942 END IF;
943 END DUNNING_UPG;
944
945 PROCEDURE Put_Debug_Msg (
946 p_path IN VARCHAR2,
947 p_debug_msg IN VARCHAR2
948 ) IS
949 BEGIN
950 IF(g_state_level >= g_debug_level) THEN
951 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
952 END IF;
953 RETURN;
954 EXCEPTION
955 WHEN OTHERS THEN
956 NULL;
957 RETURN;
958 END Put_Debug_Msg;
959
960 END IGI_DUNN_POST_UPG_PKG;