[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_AGING_PVT
Source
1 PACKAGE BODY OZF_Claim_Aging_PVT AS
2 /* $Header: ozfvcagb.pls 120.2 2006/04/11 08:53:13 kdhulipa 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)
180 * bucket type = 'FUTURE' due_date >= sysdate + ABS(dyas_start)
181 * due_date <= sysdate + ABS(dyas_to)
182 *-----------------------------------------------------------*/
183 --modified for Bugfix 5143538 dates are truncated and then compared with bucket dates
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
346 AND b.condition_type = 'GT'
343 FROM ozf_claims c
344 , ozf_aging_bucket_dates b
345 WHERE TRUNC(c.due_date, 'DD') >= TRUNC(b.bucket_date, 'DD')
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
456 CURSOR bucket_name_csr (p_id in number) IS
457 SELECT bucket_name
458 FROM ozf_x_aging_buckets
459 WHERE aging_bucket_id = p_id;
460
461 CURSOR bucket_lines_csr (p_id in number) IS
462 SELECT aging_bucket_id
463 , aging_bucket_line_id
464 , days_start
465 , days_to
466 , type
467 , report_heading1
468 , report_heading2
469 FROM ozf_x_aging_bucket_lns
470 WHERE aging_bucket_id = p_id
471 ORDER BY bucket_sequence_num;
472
473 TYPE bucket_lines_tbl IS TABLE OF bucket_lines_csr%ROWTYPE
474 INDEX BY BINARY_INTEGER;
475
476 l_bucket_lines_rec bucket_lines_csr%ROWTYPE;
477 l_bucket_lines_tbl bucket_lines_tbl;
478 l_days_start NUMBER;
479 l_days_to NUMBER;
480
481 BEGIN
482 SAVEPOINT Aging_Summary;
483
484 IF g_debug THEN
485 OZF_Utility_PVT.debug_message('== START populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
486 END IF;
487
491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
488 FND_FILE.PUT_LINE(FND_FILE.LOG, '== START populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
489 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*----------------------------------- Claim Aging Execution Report -------------------------------------*');
490 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
492
493 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Aging Bucket id = '||p_bucket_id);
494
495 IF p_bucket_id IS NOT NULL THEN
496 OPEN bucket_name_csr(p_bucket_id);
497 FETCH bucket_name_csr INTO l_bucket_name;
498 CLOSE bucket_name_csr;
499 END IF;
500
501 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Aging Buket Name: ' || l_bucket_name);
502 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
503 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Bucket Line Name Start Date End Date Bucket Line Type' );
504 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '' );
505 OPEN bucket_lines_csr(p_bucket_id);
506 LOOP
507 FETCH bucket_lines_csr INTO l_bucket_lines_tbl(l_counter);
508 EXIT WHEN bucket_lines_csr%NOTFOUND
509 OR bucket_lines_csr%NOTFOUND IS NULL;
510 l_counter := l_counter + 1;
511 END LOOP;
512 CLOSE bucket_lines_csr;
513
514 -- check if atleast one bucket exists
515 IF l_bucket_lines_tbl.count = 0 THEN
516 -- raise error and log message
517 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
518 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AGING_NO_BUCKET');
519 FND_MSG_PUB.add;
520 END IF;
521 RAISE FND_API.g_exc_error;
522 END IF;
523
524 -- Truncate existing data
525 /* TRUNCATE TABLE ozf.ozf_aging_summary_all; */
526 DELETE FROM ozf_aging_summary_all;
527 /* TRUNCATE table ozf.ozf_aging_bucket_dates; */
528 DELETE FROM ozf_aging_bucket_dates;
529
530 FOR i IN 1..l_bucket_lines_tbl.count LOOP
531 IF i = 1 THEN
532 l_seq_type := 'F';
533 ELSIF i = l_bucket_lines_tbl.count THEN
534 l_seq_type := 'L';
535 ELSE
536 l_seq_type := 'M';
537 END IF;
538
539 -- set a bound here to avoid SQL limitation: (full) year must be between -4713 and +9999, and not be 0
540 IF l_bucket_lines_tbl(i).days_start > 9999 THEN
541 l_days_start := 9999;
542 ELSIF l_bucket_lines_tbl(i).days_start < -9999 THEN
543 l_days_start := -9999;
544 ELSE
545 l_days_start := l_bucket_lines_tbl(i).days_start;
546 END IF;
547
548 IF l_bucket_lines_tbl(i).days_to > 9999 THEN
549 l_days_to := 9999;
550 ELSIF l_bucket_lines_tbl(i).days_to < -9999 THEN
551 l_days_to := -9999;
552 ELSE
553 l_days_to := l_bucket_lines_tbl(i).days_to;
554 END IF;
555
556 IF l_bucket_lines_tbl(i).type = 'PAST' THEN
557 l_start_date := sysdate - l_days_to;
558 l_end_date := sysdate - l_days_start;
559 ELSIF l_bucket_lines_tbl(i).type = 'CURRENT' OR
560 l_bucket_lines_tbl(i).type = 'FUTURE' THEN
561 l_start_date := sysdate + l_days_start;
562 l_end_date := sysdate + l_days_to;
563 END IF;
564
565 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);
566
567 populate_aging_dates(
568 p_bucket_id => l_bucket_lines_tbl(i).aging_bucket_id,
569 p_bucket_line_id => l_bucket_lines_tbl(i).aging_bucket_line_id,
570 p_bucket_sequence => i,
571 p_bucket_type => l_bucket_lines_tbl(i).type,
572 p_days_start => l_days_start,
573 p_days_to => l_days_to,
574 p_seq_type => l_seq_type,
575 x_return_status => l_return_status
576 );
577 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Line#'||i||' -- days_start ::' || l_bucket_lines_tbl(i).days_start);
578 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Line#'||i||' -- days_to ::' || l_bucket_lines_tbl(i).days_to);
579 FND_FILE.PUT_LINE(FND_FILE.LOG, 'return status#'||i||' ::' || l_return_status);
580
581 IF l_return_status = FND_API.g_ret_sts_error THEN
582 -- raise error and log message
583 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
584 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AGING_SUMMARY_ERR');
585 FND_MSG_PUB.add;
586 END IF;
587 RAISE FND_API.g_exc_error;
588 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
589 RAISE FND_API.g_exc_unexpected_error;
590 END IF;
591 END LOOP;
592
593 -- populate table ozf_aging_summary
594 populate_aging_summary(
595 x_return_status => l_return_status
596 );
597 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
598 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total number of Bucket lines selected: ' || to_char(l_bucket_lines_tbl.count));
599
600 IF l_return_status <> FND_API.g_ret_sts_success THEN
601 -- raise error and log message
602 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
603 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AGING_POPULATE_ERR');
604 FND_MSG_PUB.add;
605 END IF;
606 RAISE FND_API.g_exc_error;
607 END IF;
608
609 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Successful' );
610 FND_FILE.PUT_LINE(FND_FILE.LOG, '== END populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
611 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
612 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
613
614 IF g_debug THEN
615 OZF_Utility_PVT.debug_message('== END populating aging : time stamp :: '||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
616 END IF;
617 EXCEPTION
618 WHEN FND_API.g_exc_error THEN
619 ROLLBACK TO Aging_Summary;
620 /*
621 FND_MSG_PUB.count_and_get (
622 p_encoded => FND_API.g_false
623 ,p_count => l_msg_count
624 ,p_data => l_msg_data
625 );
626 */
627 OZF_UTILITY_PVT.write_conc_log;
628 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'FND_API.g_exc_error');
629 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || FND_MSG_PUB.get(2, FND_API.g_false)||')');
630 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
631 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
632
633 ERRBUF := l_msg_data;
634 RETCODE := 2;
635 WHEN FND_API.g_exc_unexpected_error THEN
636 ROLLBACK TO Aging_Summary;
637 /*
638 FND_MSG_PUB.count_and_get (
639 p_encoded => FND_API.g_false
640 ,p_count => l_msg_count
641 ,p_data => l_msg_data
642 );
643 */
644 OZF_UTILITY_PVT.write_conc_log;
645 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'FND_API.g_exc_unexpected_error');
646 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || FND_MSG_PUB.get(2, FND_API.g_false)||')');
647 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
648 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
649 ERRBUF := l_msg_data;
650 RETCODE := 2;
651 WHEN OTHERS THEN
652 ROLLBACK TO Aging_Summary;
653 OZF_UTILITY_PVT.write_conc_log;
654 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'OTHERS exception');
655 ERRBUF := substr(sqlerrm, 1, 80);
656 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || ERRBUF||')');
657 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
658 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
659 RETCODE := 2;
660 END Populate_Aging;
661
662 END OZF_Claim_Aging_PVT;