[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_AGING_PVT
Source
1 PACKAGE BODY OZF_Claim_Aging_PVT AS
2 /* $Header: ozfvcagb.pls 120.2.12010000.2 2010/02/22 05:10:26 hbandi ship $ */
3
4 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
5
6 --------------------------------------------------------------------------------
7 PROCEDURE insert_aging_dates(
8 p_bucket_id IN NUMBER,
9 p_bucket_line_id IN NUMBER,
10 p_bucket_sequence IN NUMBER,
11 p_bucket_type IN VARCHAR2,
12 p_bucket_date IN DATE,
13 p_condition_type IN VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2
15 )
16 IS
17 BEGIN
18 x_return_status := FND_API.g_ret_sts_success;
19 -- insert data
20 INSERT INTO ozf_aging_bucket_dates (
21 aging_bucket_id,
22 aging_bucket_line_id,
23 bucket_sequence_num,
24 bucket_type,
25 bucket_date,
26 condition_type
27 ) VALUES (
28 p_bucket_id,
29 p_bucket_line_id,
30 p_bucket_sequence,
31 p_bucket_type,
32 p_bucket_date,
33 p_condition_type
34 );
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 x_return_status := FND_API.g_ret_sts_error;
39 END;
40
41 --------------------------------------------------------------------------------
42 PROCEDURE populate_aging_dates(
43 p_bucket_id IN NUMBER,
44 p_bucket_line_id IN NUMBER,
45 p_bucket_sequence IN NUMBER,
46 p_bucket_type IN VARCHAR2,
47 p_days_start IN NUMBER,
48 p_days_to IN NUMBER,
49 p_seq_type IN VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2
51 )
52 IS
53 l_type VARCHAR2(30);
54 l_high_val NUMBER := 500;
55 l_return_status VARCHAR2(1);
56 l_bucket_date DATE;
57 l_bucket_date_range NUMBER;
58
59 BEGIN
60 x_return_status := FND_API.g_ret_sts_success;
61
62 /*
63 FND_PROFILE.get( 'bucket_date_range'
64 , l_high_val
65 );
66 */
67
68 /*------------------------------------------------------------
69 * Claim Aging Calculating rule:
70 * bucket type = 'CURRENT' claim_date >= sysdate + dyas_start
71 * claim_date <= sysdate + dayd_to
72 * bucket type = 'PAST' due_date <= sysdate - ABS(dyas_start)
73 * due_date >= sysdate - ABS(dyas_to)
74 * bucket type = 'FUTURE' due_date >= sysdate + ABS(dyas_start)
75 * due_date <= sysdate + ABS(dyas_to)
76 *-----------------------------------------------------------*/
77 l_bucket_date_range := ABS(p_days_start - p_days_to);
78 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Aging Bucket: Date Range = '|| l_bucket_date_range);
79
80 -- start of Bugfix 5143538
81 -- Absolute value of days must be added for past and future to support negatives
82 IF p_seq_type = 'M' OR
83 l_bucket_date_range < l_high_val THEN
84 l_type := 'EQ';
85 FOR i in p_days_start..p_days_to LOOP
86 IF p_bucket_type = 'PAST' THEN
87 l_bucket_date := sysdate - ABS(i);
88 ELSIF p_bucket_type = 'CURRENT' OR
89 p_bucket_type = 'FUTURE' THEN
90 l_bucket_date := sysdate + ABS(i);
91 END IF;
92 insert_aging_dates(
93 p_bucket_id => p_bucket_id,
94 p_bucket_line_id => p_bucket_line_id,
95 p_bucket_sequence => p_bucket_sequence,
96 p_bucket_type => p_bucket_type,
97 p_bucket_date => l_bucket_date,
98 p_condition_type => l_type,
99 x_return_status => l_return_status
100 );
101 IF l_return_status = FND_API.g_ret_sts_error THEN
102 RAISE FND_API.g_exc_error;
103 END IF;
104 END LOOP;
105 ELSE
106 IF p_seq_type = 'L' THEN
107 --l_type := 'GT';
108 IF p_bucket_type = 'PAST' THEN
109 l_type := 'LT';
110 l_bucket_date := sysdate - ABS(p_days_start);
111 ELSIF p_bucket_type = 'CURRENT' OR
112 p_bucket_type = 'FUTURE' THEN
113 l_type := 'GT';
114 l_bucket_date := sysdate + ABS(p_days_start);
115 END IF;
116
117 insert_aging_dates(
118 p_bucket_id => p_bucket_id,
119 p_bucket_line_id => p_bucket_line_id,
120 p_bucket_sequence => p_bucket_sequence,
121 p_bucket_type => p_bucket_type,
122 p_bucket_date => l_bucket_date,
123 p_condition_type => l_type,
124 x_return_status => l_return_status
125 );
126 IF l_return_status = FND_API.g_ret_sts_error THEN
127 RAISE FND_API.g_exc_error;
128 END IF;
129 ELSIF p_seq_type = 'F' THEN
130 --l_type := 'LT';
131 IF p_bucket_type = 'PAST' THEN
132 l_type := 'LT';
133 l_bucket_date := sysdate - ABS(p_days_to);
134 ELSIF p_bucket_type = 'CURRENT' THEN
135 l_type := 'LT';
136 l_bucket_date := sysdate + p_days_to;
137 ELSIF p_bucket_type = 'FUTURE' THEN
138 l_type := 'GT';
139 l_bucket_date := sysdate + ABS(p_days_to);
140 END IF;
141 -- end of Bugfix 5143538
142
143 insert_aging_dates(
144 p_bucket_id => p_bucket_id,
145 p_bucket_line_id => p_bucket_line_id,
146 p_bucket_sequence => p_bucket_sequence,
147 p_bucket_type => p_bucket_type,
148 p_bucket_date => l_bucket_date,
149 p_condition_type => l_type,
150 x_return_status => l_return_status
151 );
152 IF l_return_status = FND_API.g_ret_sts_error THEN
153 RAISE FND_API.g_exc_error;
154 END IF;
155 END IF;
156 END IF;
157
158 EXCEPTION
159 WHEN FND_API.g_exc_error THEN
160 x_return_status := FND_API.g_ret_sts_error;
161
162 WHEN FND_API.g_exc_unexpected_error THEN
163 x_return_status := FND_API.g_ret_sts_unexp_error ;
164
165 WHEN OTHERS THEN
166 x_return_status := FND_API.g_ret_sts_unexp_error;
167 END;
168
169 --------------------------------------------------------------------------------
170 PROCEDURE populate_aging_summary(
171 x_return_status OUT NOCOPY VARCHAR2
172 )
173 IS
174 /*------------------------------------------------------------
175 * Claim Aging Calculating rule:
176 * bucket type = 'CURRENT' claim_date >= sysdate + dyas_start
177 * claim_date <= sysdate + dayd_to
178 * bucket type = 'PAST' due_date <= sysdate - ABS(dyas_start)
179 * due_date >= sysdate - ABS(dyas_to)
183 --modified for Bugfix 5143538 dates are truncated and then compared with bucket dates
180 * bucket type = 'FUTURE' due_date >= sysdate + ABS(dyas_start)
181 * due_date <= sysdate + ABS(dyas_to)
182 *-----------------------------------------------------------*/
184 CURSOR aging_summary_csr IS
185 SELECT c.cust_account_id cust_account_id
186 , b.aging_bucket_id aging_bucket_id
187 , b.aging_bucket_line_id aging_bucket_line_id
188 , b.bucket_sequence_num bucket_sequence_num
189 , c.org_id
190 , SUM(c.acctd_amount) amount
191 FROM ozf_claims c
192 , ozf_aging_bucket_dates b
193 WHERE TRUNC(c.claim_date, 'DD') = TRUNC(b.bucket_date, 'DD')
194 AND b.condition_type = 'EQ'
195 AND b.bucket_type = 'CURRENT'
196 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
197 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
198 GROUP BY c.cust_account_id
199 , b.aging_bucket_id
200 , b.aging_bucket_line_id
201 , b.bucket_sequence_num
202 , c.org_id
203 UNION ALL
204 SELECT c.cust_account_id cust_account_id
205 , b.aging_bucket_id aging_bucket_id
206 , b.aging_bucket_line_id aging_bucket_line_id
207 , b.bucket_sequence_num bucket_sequence_num
208 , c.org_id
209 , SUM(c.acctd_amount) amount
210 FROM ozf_claims c
211 , ozf_aging_bucket_dates b
212 WHERE TRUNC(c.claim_date, 'DD') <= TRUNC(b.bucket_date, 'DD')
213 AND b.condition_type = 'LT'
214 AND b.bucket_type = 'CURRENT'
215 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
216 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
217 GROUP BY c.cust_account_id
218 , b.aging_bucket_id
219 , b.aging_bucket_line_id
220 , b.bucket_sequence_num
221 , c.org_id
222 UNION ALL
223 SELECT c.cust_account_id cust_account_id
224 , b.aging_bucket_id aging_bucket_id
225 , b.aging_bucket_line_id aging_bucket_line_id
226 , b.bucket_sequence_num bucket_sequence_num
227 , c.org_id
228 , SUM(c.acctd_amount) amount
229 FROM ozf_claims c
230 , ozf_aging_bucket_dates b
231 WHERE TRUNC(c.claim_date, 'DD') >= TRUNC(b.bucket_date , 'DD')
232 AND b.condition_type = 'GT'
233 AND b.bucket_type = 'CURRENT'
234 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
235 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
236 GROUP BY c.cust_account_id
237 , b.aging_bucket_id
238 , b.aging_bucket_line_id
239 , b.bucket_sequence_num
240 , c.org_id
241 UNION ALL
242 SELECT c.cust_account_id cust_account_id
243 , b.aging_bucket_id aging_bucket_id
244 , b.aging_bucket_line_id aging_bucket_line_id
245 , b.bucket_sequence_num bucket_sequence_num
246 , c.org_id
247 , SUM(c.acctd_amount) amount
248 FROM ozf_claims c
249 , ozf_aging_bucket_dates b
250 WHERE TRUNC(c.due_date, 'DD') = TRUNC(b.bucket_date, 'DD')
251 AND b.condition_type = 'EQ'
252 AND b.bucket_type = 'PAST'
253 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
254 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
255 GROUP BY c.cust_account_id
256 , b.aging_bucket_id
257 , b.aging_bucket_line_id
258 , b.bucket_sequence_num
259 , c.org_id
260 UNION ALL
261 SELECT c.cust_account_id cust_account_id
262 , b.aging_bucket_id aging_bucket_id
263 , b.aging_bucket_line_id aging_bucket_line_id
264 , b.bucket_sequence_num bucket_sequence_num
265 , c.org_id
266 , SUM(c.acctd_amount) amount
267 FROM ozf_claims c
268 , ozf_aging_bucket_dates b
269 WHERE TRUNC(c.due_date, 'DD') <= TRUNC(b.bucket_date , 'DD')
270 AND b.condition_type = 'LT'
271 AND b.bucket_type = 'PAST'
272 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
273 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
274 GROUP BY c.cust_account_id
275 , b.aging_bucket_id
276 , b.aging_bucket_line_id
277 , b.bucket_sequence_num
278 , c.org_id
279 UNION ALL
280 SELECT c.cust_account_id cust_account_id
281 , b.aging_bucket_id aging_bucket_id
282 , b.aging_bucket_line_id aging_bucket_line_id
283 , b.bucket_sequence_num bucket_sequence_num
284 , c.org_id
285 , SUM(c.acctd_amount) amount
286 FROM ozf_claims c
287 , ozf_aging_bucket_dates b
288 WHERE TRUNC(c.due_date, 'DD') >= TRUNC(b.bucket_date , 'DD')
289 AND b.condition_type = 'GT'
290 AND b.bucket_type = 'PAST'
291 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
292 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
293 GROUP BY c.cust_account_id
294 , b.aging_bucket_id
295 , b.aging_bucket_line_id
296 , b.bucket_sequence_num
297 , c.org_id
298 UNION ALL
299 SELECT c.cust_account_id cust_account_id
300 , b.aging_bucket_id aging_bucket_id
301 , b.aging_bucket_line_id aging_bucket_line_id
302 , b.bucket_sequence_num bucket_sequence_num
303 , c.org_id
304 , SUM(c.acctd_amount) amount
305 FROM ozf_claims c
306 , ozf_aging_bucket_dates b
307 WHERE TRUNC(c.due_date, 'DD') = TRUNC(b.bucket_date, 'DD')
308 AND b.condition_type = 'EQ'
309 AND b.bucket_type = 'FUTURE'
310 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
311 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
312 GROUP BY c.cust_account_id
313 , b.aging_bucket_id
314 , b.aging_bucket_line_id
315 , b.bucket_sequence_num
316 , c.org_id
317 UNION ALL
318 SELECT c.cust_account_id cust_account_id
319 , b.aging_bucket_id aging_bucket_id
320 , b.aging_bucket_line_id aging_bucket_line_id
321 , b.bucket_sequence_num bucket_sequence_num
322 , c.org_id
323 , SUM(c.acctd_amount) amount
324 FROM ozf_claims c
325 , ozf_aging_bucket_dates b
326 WHERE TRUNC(c.due_date, 'DD') <= TRUNC(b.bucket_date, 'DD')
327 AND b.condition_type = 'LT'
328 AND b.bucket_type = 'FUTURE'
329 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
330 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
331 GROUP BY c.cust_account_id
332 , b.aging_bucket_id
333 , b.aging_bucket_line_id
334 , b.bucket_sequence_num
335 , c.org_id
336 UNION ALL
337 SELECT c.cust_account_id cust_account_id
338 , b.aging_bucket_id aging_bucket_id
339 , b.aging_bucket_line_id aging_bucket_line_id
340 , b.bucket_sequence_num bucket_sequence_num
341 , c.org_id
342 , SUM(c.acctd_amount) amount
343 FROM ozf_claims c
344 , ozf_aging_bucket_dates b
345 WHERE TRUNC(c.due_date, 'DD') >= TRUNC(b.bucket_date, 'DD')
346 AND b.condition_type = 'GT'
347 AND b.bucket_type = 'FUTURE'
348 --AND c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
349 AND c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
350 GROUP BY c.cust_account_id
351 , b.aging_bucket_id
352 , b.aging_bucket_line_id
353 , b.bucket_sequence_num
354 , c.org_id;
355
356 CURSOR aging_col_total_csr IS
357 SELECT aging_bucket_id aging_bucket_id
358 , aging_bucket_line_id aging_bucket_line_id
359 , bucket_sequence_num bucket_sequence_num
360 , org_id
361 , SUM(amount) amount
362 FROM ozf_aging_summary_all
363 GROUP BY aging_bucket_id
364 , aging_bucket_line_id
365 , bucket_sequence_num
366 , org_id;
367
368 l_aging_summary_rec aging_summary_csr%ROWTYPE;
369 l_aging_col_total_rec aging_col_total_csr%ROWTYPE;
370
371 BEGIN
372 x_return_status := FND_API.g_ret_sts_success;
373
374 OPEN aging_summary_csr;
375 LOOP
376 FETCH aging_summary_csr INTO l_aging_summary_rec;
377 EXIT WHEN aging_summary_csr%NOTFOUND
378 OR aging_summary_csr%NOTFOUND IS NULL;
379 IF l_aging_summary_rec.amount IS NOT NULL THEN
380 INSERT INTO ozf_aging_summary_all (
381 cust_account_id,
382 aging_bucket_id,
383 aging_bucket_line_id,
384 bucket_sequence_num,
385 amount,
386 org_id
387 ) VALUES (
388 l_aging_summary_rec.cust_account_id,
389 l_aging_summary_rec.aging_bucket_id,
390 l_aging_summary_rec.aging_bucket_line_id,
391 l_aging_summary_rec.bucket_sequence_num,
392 l_aging_summary_rec.amount,
393 l_aging_summary_rec.org_id
394 );
395 END IF;
396 END LOOP;
397 CLOSE aging_summary_csr;
398
399 OPEN aging_col_total_csr;
400 LOOP
401 FETCH aging_col_total_csr INTO l_aging_col_total_rec;
402 EXIT WHEN aging_col_total_csr%NOTFOUND
403 OR aging_col_total_csr%NOTFOUND IS NULL;
404 INSERT INTO ozf_aging_summary_all (
405 cust_account_id,
406 aging_bucket_id,
407 aging_bucket_line_id,
408 bucket_sequence_num,
409 amount,
410 org_id
411 ) VALUES (
412 -1,
413 l_aging_col_total_rec.aging_bucket_id,
414 l_aging_col_total_rec.aging_bucket_line_id,
415 l_aging_col_total_rec.bucket_sequence_num,
416 l_aging_col_total_rec.amount,
417 l_aging_col_total_rec.org_id
418 );
419 END LOOP;
420 CLOSE aging_col_total_csr;
421
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 x_return_status := FND_API.g_ret_sts_error;
426 END;
427 --------------------------------------------------------------------------------
428 -- API name : Populate_Aging
429 -- Type : Private
430 -- Pre-reqs : None
431 -- Function :
432 -- Parameters :
433 --
434 -- IN : p_bucket_id IN NUMBER Optional
435 --
436 -- Version : Current version 1.0
437 --
438 --------------------------------------------------------------------------------
439
440 PROCEDURE Populate_Aging (
441 ERRBUF OUT NOCOPY VARCHAR2,
442 RETCODE OUT NOCOPY NUMBER,
443 p_bucket_id IN NUMBER
444 )
445 IS
446
447 l_counter NUMBER := 1;
448 l_msg_data VARCHAR2(80);
449 l_msg_count NUMBER;
450 l_return_status VARCHAR2(1);
451 l_seq_type VARCHAR2(1);
452 l_bucket_name VARCHAR2(20);
453 l_start_date DATE;
454 l_end_date DATE;
455 --hbandi variables for getting the Org_id
456 l_org_id NUMBER;
457
458 CURSOR bucket_name_csr (p_id in number) IS
459 SELECT bucket_name
460 FROM ozf_x_aging_buckets
461 WHERE aging_bucket_id = p_id;
462
463 CURSOR bucket_lines_csr (p_id in number) IS
464 SELECT aging_bucket_id
465 , aging_bucket_line_id
466 , days_start
467 , days_to
468 , type
469 , report_heading1
470 , report_heading2
471 FROM ozf_x_aging_bucket_lns
472 WHERE aging_bucket_id = p_id
473 ORDER BY bucket_sequence_num;
474
475 TYPE bucket_lines_tbl IS TABLE OF bucket_lines_csr%ROWTYPE
476 INDEX BY BINARY_INTEGER;
477
478 l_bucket_lines_rec bucket_lines_csr%ROWTYPE;
479 l_bucket_lines_tbl bucket_lines_tbl;
480 l_days_start NUMBER;
481 l_days_to NUMBER;
482
483 BEGIN
484 SAVEPOINT Aging_Summary;
485
486 IF g_debug THEN
487 OZF_Utility_PVT.debug_message('== START populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
488 END IF;
489
490 FND_FILE.PUT_LINE(FND_FILE.LOG, '== START populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*----------------------------------- Claim Aging Execution Report -------------------------------------*');
492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
493 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
494
495 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Aging Bucket id = '||p_bucket_id);
496
497 IF p_bucket_id IS NOT NULL THEN
498 OPEN bucket_name_csr(p_bucket_id);
499 FETCH bucket_name_csr INTO l_bucket_name;
500 CLOSE bucket_name_csr;
501 END IF;
502
503 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Aging Buket Name: ' || l_bucket_name);
504 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
505 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Bucket Line Name Start Date End Date Bucket Line Type' );
506 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '' );
507 OPEN bucket_lines_csr(p_bucket_id);
511 OR bucket_lines_csr%NOTFOUND IS NULL;
508 LOOP
509 FETCH bucket_lines_csr INTO l_bucket_lines_tbl(l_counter);
510 EXIT WHEN bucket_lines_csr%NOTFOUND
512 l_counter := l_counter + 1;
513 END LOOP;
514 CLOSE bucket_lines_csr;
515
516 -- check if atleast one bucket exists
517 IF l_bucket_lines_tbl.count = 0 THEN
518 -- raise error and log message
519 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
520 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AGING_NO_BUCKET');
521 FND_MSG_PUB.add;
522 END IF;
523 RAISE FND_API.g_exc_error;
524 END IF;
525
526 /*Hbandi Added this code for the Bug Fix #9273717 (+) */
527 l_org_id := MO_GLOBAL.get_current_org_id;
528 DELETE FROM ozf_aging_summary_all WHERE org_id = l_org_id;
529
530 /*End of hbandi added Code (-) */
531
532 -- Truncate existing data
533 /* TRUNCATE TABLE ozf.ozf_aging_summary_all; */
534 -- DELETE FROM ozf_aging_summary_all; --Hbandi Commented this statement and added in the above code snippet
535
536 /* TRUNCATE table ozf.ozf_aging_bucket_dates; */
537 DELETE FROM ozf_aging_bucket_dates;
538
539 FOR i IN 1..l_bucket_lines_tbl.count LOOP
540 IF i = 1 THEN
541 l_seq_type := 'F';
542 ELSIF i = l_bucket_lines_tbl.count THEN
543 l_seq_type := 'L';
544 ELSE
545 l_seq_type := 'M';
546 END IF;
547
548 -- set a bound here to avoid SQL limitation: (full) year must be between -4713 and +9999, and not be 0
549 IF l_bucket_lines_tbl(i).days_start > 9999 THEN
550 l_days_start := 9999;
551 ELSIF l_bucket_lines_tbl(i).days_start < -9999 THEN
552 l_days_start := -9999;
553 ELSE
554 l_days_start := l_bucket_lines_tbl(i).days_start;
555 END IF;
556
557 IF l_bucket_lines_tbl(i).days_to > 9999 THEN
558 l_days_to := 9999;
559 ELSIF l_bucket_lines_tbl(i).days_to < -9999 THEN
560 l_days_to := -9999;
561 ELSE
562 l_days_to := l_bucket_lines_tbl(i).days_to;
563 END IF;
564
565 IF l_bucket_lines_tbl(i).type = 'PAST' THEN
566 l_start_date := sysdate - l_days_to;
567 l_end_date := sysdate - l_days_start;
568 ELSIF l_bucket_lines_tbl(i).type = 'CURRENT' OR
569 l_bucket_lines_tbl(i).type = 'FUTURE' THEN
570 l_start_date := sysdate + l_days_start;
571 l_end_date := sysdate + l_days_to;
572 END IF;
573
574 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad(l_bucket_lines_tbl(i).report_heading1|| ' ' ||l_bucket_lines_tbl(i).report_heading2, 46, ' ')||' '||l_start_date||' '||l_end_date||' '||l_bucket_lines_tbl(i).type);
575
576 populate_aging_dates(
577 p_bucket_id => l_bucket_lines_tbl(i).aging_bucket_id,
578 p_bucket_line_id => l_bucket_lines_tbl(i).aging_bucket_line_id,
579 p_bucket_sequence => i,
580 p_bucket_type => l_bucket_lines_tbl(i).type,
581 p_days_start => l_days_start,
582 p_days_to => l_days_to,
583 p_seq_type => l_seq_type,
584 x_return_status => l_return_status
585 );
586 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Line#'||i||' -- days_start ::' || l_bucket_lines_tbl(i).days_start);
587 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Line#'||i||' -- days_to ::' || l_bucket_lines_tbl(i).days_to);
588 FND_FILE.PUT_LINE(FND_FILE.LOG, 'return status#'||i||' ::' || l_return_status);
589
590 IF l_return_status = FND_API.g_ret_sts_error THEN
591 -- raise error and log message
592 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
593 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AGING_SUMMARY_ERR');
594 FND_MSG_PUB.add;
595 END IF;
596 RAISE FND_API.g_exc_error;
597 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
598 RAISE FND_API.g_exc_unexpected_error;
599 END IF;
600 END LOOP;
601
602 -- populate table ozf_aging_summary
603 populate_aging_summary(
604 x_return_status => l_return_status
605 );
606 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
607 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total number of Bucket lines selected: ' || to_char(l_bucket_lines_tbl.count));
608
609 IF l_return_status <> FND_API.g_ret_sts_success THEN
610 -- raise error and log message
611 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
612 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AGING_POPULATE_ERR');
613 FND_MSG_PUB.add;
614 END IF;
615 RAISE FND_API.g_exc_error;
616 END IF;
617
618 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Successful' );
619 FND_FILE.PUT_LINE(FND_FILE.LOG, '== END populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
620 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
621 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
622
623 IF g_debug THEN
624 OZF_Utility_PVT.debug_message('== END populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
625 END IF;
626 EXCEPTION
627 WHEN FND_API.g_exc_error THEN
628 ROLLBACK TO Aging_Summary;
629 /*
630 FND_MSG_PUB.count_and_get (
631 p_encoded => FND_API.g_false
632 ,p_count => l_msg_count
633 ,p_data => l_msg_data
634 );
635 */
636 OZF_UTILITY_PVT.write_conc_log;
637 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'FND_API.g_exc_error');
638 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || FND_MSG_PUB.get(2, FND_API.g_false)||')');
639 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
640 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
641
642 ERRBUF := l_msg_data;
643 RETCODE := 2;
644 WHEN FND_API.g_exc_unexpected_error THEN
645 ROLLBACK TO Aging_Summary;
646 /*
647 FND_MSG_PUB.count_and_get (
648 p_encoded => FND_API.g_false
649 ,p_count => l_msg_count
650 ,p_data => l_msg_data
651 );
652 */
653 OZF_UTILITY_PVT.write_conc_log;
654 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'FND_API.g_exc_unexpected_error');
655 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || FND_MSG_PUB.get(2, FND_API.g_false)||')');
656 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
657 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
658 ERRBUF := l_msg_data;
659 RETCODE := 2;
660 WHEN OTHERS THEN
661 ROLLBACK TO Aging_Summary;
662 OZF_UTILITY_PVT.write_conc_log;
663 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'OTHERS exception');
664 ERRBUF := substr(sqlerrm, 1, 80);
665 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || ERRBUF||')');
666 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
667 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
668 RETCODE := 2;
669 END Populate_Aging;
670
671 END OZF_Claim_Aging_PVT;