[Home] [Help]
PACKAGE BODY: APPS.FVFCCHKB_PKG
Source
1 package body FVFCCHKB_PKG AS
2 --$Header: FVFCCHKB.pls 115.37 2002/04/03 14:18:43 pkm ship $
3
4 g_error_flag NUMBER(1);
5 g_treasury_symbol_id NUMBER(15);
6
7 -- Addded on 07/13/2000 By Supadman
8 -- Variable to hold log text.
9 v_log_text Varchar2(416) ;
10 v_log_counter Number := 0 ;
11
12
13 v_edit_check_number NUMBER(2);
14 v_edit_check_status VARCHAR2(25);
15 v_amount NUMBER := 0;
16 v_amount1 NUMBER := 0;
17 v_amount2 NUMBER := 0;
18 v_sgl_acct_number fv_facts_temp.sgl_acct_number%TYPE;
19 v_dummy_var VARCHAR2(3);
20 v_row_count NUMBER := 0;
21
22
23
24 PROCEDURE Create_log_record(text varchar2) ;
25
26 PROCEDURE create_status_record(p_edit_check_number number,
27 p_edit_check_status varchar2) ;
28
29 -- Procedure to initialize variables
30 PROCEDURE init_vars IS
31
32 BEGIN
33 v_edit_check_number := NULL;
34 v_edit_check_status := NULL;
35 v_amount := 0 ;
36 v_amount1 := 0 ;
37 v_amount2 := 0 ;
38 v_sgl_acct_number := NULL;
39 v_log_text := ' ' ;
40 v_dummy_var := NULL;
41 v_row_count := 0 ;
42
43 END init_vars;
44
45 PROCEDURE edit_check_1 IS
46
47 l_total_credit NUMBER := 0;
48 l_total_debit NUMBER := 0;
49
50 -- Cursor to fetch Credit/Debit Ending balance
51 -- from FV_FACTS_TEMP for budgetary accounts
52 CURSOR check1 IS
53 SELECT nvl(amount,0) amount, debit_credit,
54 sgl_acct_number
55 FROM fv_facts_temp
56 WHERE treasury_symbol_id = g_treasury_symbol_id
57 AND fct_int_record_category = 'REPORTED_NEW'
58 AND fct_int_record_type = 'BLK_DTL'
59 AND sgl_acct_number like '4%'
60 AND begin_end = 'E'
61 AND amount <> 0
62 ORDER BY sgl_acct_number;
63
64 BEGIN
65 init_vars;
66 v_edit_check_number := 1;
67
68 FOR check1_rec IN check1
69 LOOP
70 IF check1_rec.debit_credit = 'C' THEN
71 l_total_credit := l_total_credit + check1_rec.amount;
72 ELSE
73 l_total_debit := l_total_debit + check1_rec.amount;
74 END IF;
75
76 v_sgl_acct_number := check1_rec.sgl_acct_number;
77 v_amount := check1_rec.amount;
78 create_log_record(v_log_text);
79
80 END LOOP;
81
82 IF -1*(l_total_credit) = l_total_debit THEN
83 v_edit_check_status := 'Passed';
84 ELSE
85 g_error_flag := 2;
86 v_edit_check_status := 'Failed';
87 END IF;
88
89 create_status_record(v_edit_check_number, v_edit_check_status) ;
90
91 EXCEPTION WHEN OTHERS THEN
92 v_log_text := 'Exception when others in Edit Check 1. SQLCODE: '|| SQLCODE ;
93 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
94
95 v_log_text := 'SQLERRM: '|| SQLERRM ;
96 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
97
98 g_error_flag := 2;
99 END; --edit_check_1
100
101
102 PROCEDURE edit_check_2 IS
103
104 -- Cursor to fetch SGL account and associated attributes from
105 -- FV_FACTS_USSGL_ACCOUNTS for all budgetary accounts
106 -- existing in FV_FACTS_TEMP
107 CURSOR fv_facts_ussgl_accounts_c IS
108 SELECT ffa.ussgl_account,
109 ffa.total_resource_be_flag, ffa.total_resource_dc_flag,
110 ffa.resource_status_be_flag, ffa.resource_status_dc_flag
111 FROM fv_facts_ussgl_accounts ffa
112 WHERE ffa.ussgl_account like '4%'
113 AND EXISTS
114 (SELECT 'x'
115 FROM fv_facts_temp fft
116 WHERE fft.treasury_symbol_id = g_treasury_symbol_id
117 AND fft.sgl_acct_number = ffa.ussgl_account);
118
119 l_ussgl_account varchar2(30);
120 l_total_resource_be_flag varchar2(1);
121 l_total_resource_dc_flag varchar2(1);
122 l_resource_status_be_flag varchar2(1);
123 l_resource_status_dc_flag varchar2(1);
124 l_begin_bal number;
125 l_begin_bal_dc_ind varchar2(1);
126 l_end_bal number;
127 l_end_bal_dc_ind varchar2(1);
128 l_balance number;
129 l_dc_ind varchar2(1);
130 l_to_total number := 0;
131 l_st_total number := 0;
132
133 l_to_amount NUMBER := 0;
134 l_st_amount NUMBER := 0;
135
136 BEGIN
137 init_vars;
138 v_edit_check_number := 2;
139
140 OPEN fv_facts_ussgl_accounts_c;
141
142 LOOP
143
144 FETCH fv_facts_ussgl_accounts_c
145 INTO l_ussgl_account,
146 l_total_resource_be_flag,
147 l_total_resource_dc_flag,
148 l_resource_status_be_flag,
149 l_resource_status_dc_flag;
150
151 EXIT WHEN fv_facts_ussgl_accounts_c%NOTFOUND
152 OR fv_facts_ussgl_accounts_c%NOTFOUND IS NULL;
153
154 -- Fetch beginning balance and set debit_credit
155 -- indicator for the SGL account
156
157 BEGIN
158
159 SELECT nvl(sum(amount),0)
160 INTO l_begin_bal
161 FROM fv_facts_temp
162 WHERE treasury_symbol_id = g_treasury_symbol_id
163 AND fct_int_record_category = 'REPORTED_NEW'
164 AND fct_int_record_type = 'BLK_DTL'
165 AND sgl_acct_number = l_ussgl_account
166 AND begin_end = 'B';
167
168 IF (l_begin_bal > 0) THEN
169 l_begin_bal_dc_ind := 'D';
170 ELSE
171 l_begin_bal_dc_ind := 'C';
172 END IF;
173
174 EXCEPTION WHEN NO_DATA_FOUND THEN
175 l_begin_bal := 0;
176
177 END;
178
179 -- Fetch Ending Balance and set debit_credit
180 -- indicator for the SGL account
181
182 BEGIN
183
184 SELECT nvl(sum(amount),0)
185 INTO l_end_bal
186 FROM fv_facts_temp
187 WHERE treasury_symbol_id = g_treasury_symbol_id
188 AND fct_int_record_category = 'REPORTED_NEW'
189 AND fct_int_record_type = 'BLK_DTL'
190 AND sgl_acct_number = l_ussgl_account
191 AND begin_end = 'E';
192
193 IF (l_end_bal > 0) THEN
194 l_end_bal_dc_ind := 'D';
195 ELSE
196 l_end_bal_dc_ind := 'C';
197 END IF;
198
199 EXCEPTION WHEN NO_DATA_FOUND THEN
200 l_end_bal := 0;
201
202 END;
203
204 v_amount1 := 0;
205 v_amount2 := 0;
206
207 IF (l_total_resource_be_flag = 'E') THEN
208
209 v_amount1 := l_end_bal;
210
211 IF (l_total_resource_dc_flag = 'D' and l_end_bal_dc_ind = 'D') THEN
212 l_to_total := l_to_total + l_end_bal;
213 ELSIF (l_total_resource_dc_flag = 'C' and l_end_bal_dc_ind = 'C') THEN
214 l_to_total := l_to_total + l_end_bal;
215 ELSIF (l_total_resource_dc_flag = 'E') THEN
216 l_to_total := l_to_total + l_end_bal;
217 END IF;
218 ELSIF (l_total_resource_be_flag = 'B' ) THEN
219
220 v_amount1 := l_begin_bal;
221
222 IF (l_total_resource_dc_flag = 'D' and l_begin_bal_dc_ind = 'D') THEN
223 l_to_total := l_to_total + l_begin_bal;
224 ELSIF (l_total_resource_dc_flag = 'C' and l_begin_bal_dc_ind = 'C') THEN
225 l_to_total := l_to_total + l_begin_bal;
226 ELSIF (l_total_resource_dc_flag = 'E') THEN
227 l_to_total := l_to_total + l_begin_bal;
228 END IF;
229 END IF;
230
231 IF (l_resource_status_be_flag = 'E') THEN
232
233 v_amount2 := l_end_bal;
234
235 IF (l_resource_status_dc_flag = 'D' and l_end_bal_dc_ind = 'D') THEN
236 l_st_total := l_st_total + l_end_bal;
237 ELSIF (l_resource_status_dc_flag = 'C' and l_end_bal_dc_ind = 'C') THEN
238 l_st_total := l_st_total + l_end_bal;
239 ELSIF (l_resource_status_dc_flag = 'E') THEN
240 l_st_total := l_st_total + l_end_bal;
241 END IF;
242 ELSIF (l_resource_status_be_flag = 'B' ) THEN
243
244 v_amount2 := l_begin_bal;
245
246 IF (l_resource_status_dc_flag = 'D' and l_begin_bal_dc_ind = 'D') THEN
247 l_st_total := l_st_total + l_begin_bal;
248 ELSIF (l_resource_status_dc_flag = 'C' and l_begin_bal_dc_ind = 'C') THEN
249 l_st_total := l_st_total + l_begin_bal;
250 ELSIF (l_resource_status_dc_flag = 'E') THEN
251 l_st_total := l_st_total + l_begin_bal;
252 END IF;
253 ELSIF (l_resource_status_be_flag = 'S' ) THEN
254
255 l_balance := l_end_bal - l_begin_bal;
256 IF ( l_balance > 0) THEN
257 l_dc_ind := 'D';
258 ELSE
259 l_dc_ind := 'C';
260 END IF;
261 IF (l_resource_status_dc_flag = 'D' and l_dc_ind = 'D') THEN
262 l_st_total := l_st_total + l_balance;
263 ELSIF (l_resource_status_dc_flag = 'C' and l_dc_ind = 'C') THEN
264 l_st_total := l_st_total + l_balance;
265 ELSIF (l_resource_status_dc_flag = 'E') THEN
266 l_st_total := l_st_total + l_balance;
267 END IF;
268
269 v_amount2 := l_balance;
270
271 END IF;
272
273
274 IF (l_total_resource_be_flag = 'E') AND (l_resource_status_be_flag = 'E') THEN
275 IF l_end_bal_dc_ind = 'D' THEN
276 v_amount2 := 0;
277 ELSE
278 v_amount1 := 0;
279 END IF;
280 END IF;
281
282 v_sgl_acct_number := l_ussgl_account;
283 create_log_record(v_log_text);
284
285 END LOOP;
286
287 CLOSE fv_facts_ussgl_accounts_c;
288
289 l_st_total := -1*l_st_total;
290
291 IF (l_to_total = l_st_total) THEN
292 v_edit_check_status := 'Passed';
293 ELSE
294 g_error_flag := 2;
295 v_edit_check_status := 'Failed';
296 END IF;
297
298 create_status_record(v_edit_check_number, v_edit_check_status) ;
299
300
301 EXCEPTION WHEN OTHERS THEN
302 v_log_text := 'Exception when others in Edit Check 2. SQLCODE: '|| SQLCODE ;
303 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
304
305 v_log_text := 'SQLERRM: '|| SQLERRM ;
306 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
307
308 g_error_flag := 2;
309 END; --edit_check_2
310
311
312 PROCEDURE edit_check_3 IS
313
314 l_budget_credit NUMBER := 0;
315 l_budget_debit NUMBER := 0;
316
317 -- Cursor to fetch Credit and Debit Beginning balance
318 -- from FV_FACTS_TEMP for budgetary accounts
319 CURSOR check3 IS
320 SELECT nvl(amount,0) amount, debit_credit, sgl_acct_number
321 FROM fv_facts_temp
322 WHERE treasury_symbol_id = g_treasury_symbol_id
323 AND fct_int_record_category = 'REPORTED_NEW'
324 AND fct_int_record_type = 'BLK_DTL'
325 AND sgl_acct_number like '4%'
326 AND begin_end = 'B';
327
328
329 BEGIN
330 init_vars;
331 v_edit_check_number := 3;
332
333 FOR check3_rec in check3
334 LOOP
335 v_amount := check3_rec.amount;
336 v_sgl_acct_number := check3_rec.sgl_acct_number;
337
338 create_log_record(v_log_text);
339
340 IF check3_rec.debit_credit = 'C' THEN
341 l_budget_credit := l_budget_credit + v_amount;
342 ELSE
343 l_budget_debit := l_budget_debit + v_amount;
344 END IF;
345
346 END LOOP;
347
348 IF l_budget_debit = -1*(l_budget_credit) THEN
349 v_edit_check_status := 'Passed';
350 ELSE
351 g_error_flag := 2;
352 v_edit_check_status := 'Failed';
353 END IF;
354
355 create_status_record(v_edit_check_number, v_edit_check_status) ;
356
357
358 EXCEPTION WHEN OTHERS THEN
359 v_log_text := 'Exception when others in Edit Check 3. SQLCODE: '|| SQLCODE ;
360 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
361
362 v_log_text := 'SQLERRM: '|| SQLERRM ;
363 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
364
365 g_error_flag := 2;
366 END; --edit_check_3
367
368
369 PROCEDURE edit_check_4 IS
370
371 -- Cursor to fetch ending balance, account number for all accounts
372 -- where YE_ANTICIPATED_FLAG = 'Y'
373 CURSOR anticipated_items_c is
374 SELECT nvl(sum(fft.amount),0), fft.sgl_acct_number
375 FROM fv_facts_temp fft,
376 fv_facts_ussgl_accounts ffa
377 WHERE treasury_symbol_id = g_treasury_symbol_id
378 AND fct_int_record_category = 'REPORTED_NEW'
379 AND fct_int_record_type = 'BLK_DTL'
380 AND ffa.ussgl_account = fft.sgl_acct_number
381 AND ffa.ye_anticipated_flag = 'Y'
382 AND fft.begin_end = 'E'
383 GROUP BY fft.sgl_acct_number;
384
385 l_count number;
386 l_amount NUMBER := 0;
387 l_sgl_acct_number varchar2(30);
388
389
390 BEGIN
391
392 init_vars;
393
394 l_count := 0;
395 v_edit_check_number := 4;
396
397 OPEN anticipated_items_c;
398 LOOP
399
400 FETCH anticipated_items_c
401 INTO l_amount, l_sgl_acct_number;
402
403 EXIT WHEN anticipated_items_c%NOTFOUND OR anticipated_items_c%NOTFOUND IS NULL;
404
405 IF (l_amount <> 0) THEN
406 l_count := l_count +1;
407
408 v_amount := l_amount;
409 v_sgl_acct_number := l_sgl_acct_number;
410 create_log_record(v_log_text);
411 END IF;
412
413 END LOOP;
414
415 IF (l_count > 0) THEN
416 g_error_flag := 2;
417 v_edit_check_status := 'Failed';
418 ELSE
419 v_edit_check_status := 'Passed';
420 v_amount := NULL;
421 -- create_log_record(v_log_text);
422 END IF;
423
424 create_status_record(v_edit_check_number, v_edit_check_status) ;
425
426 CLOSE anticipated_items_c;
427
428 EXCEPTION WHEN OTHERS THEN
429
430 v_log_text := 'Exception when others in Edit Check 4. SQLCODE: '|| SQLCODE ;
431 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
432
433 v_log_text := 'SQLERRM: '|| SQLERRM ;
434 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
435
436 g_error_flag := 2;
437 END; --edit_check_4
438
439 PROCEDURE edit_check_5 IS
440
441 --Cursor to fetch Resource and Equity flags and amounts
442 CURSOR check5 IS
443 SELECT fft.sgl_acct_number, nvl(amount,0) amount,
444 ffa.ye_resource_equity_flag
445 FROM fv_facts_temp fft,
446 fv_facts_ussgl_accounts ffa
447 WHERE fft.treasury_symbol_id = g_treasury_symbol_id
448 AND fft.fct_int_record_category = 'REPORTED_NEW'
449 AND fft.fct_int_record_type = 'BLK_DTL'
450 AND ffa.ussgl_account = fft.sgl_acct_number
451 AND ffa.ye_resource_equity_flag in ('R','E')
452 AND fft.begin_end = ffa.ye_resource_equity_be_flag;
453
454 l_total_resources NUMBER := 0;
455 l_total_equity NUMBER := 0;
456
457 BEGIN
458
459 init_vars;
460 v_edit_check_number := 5;
461
462 FOR check5_rec IN check5
463 LOOP
464
465 v_amount1 := 0;
466 v_amount2 := 0;
467
468 v_sgl_acct_number := check5_rec.sgl_acct_number;
469
470 IF check5_rec.ye_resource_equity_flag = 'R' THEN
471 v_amount1 := check5_rec.amount;
472 l_total_resources := l_total_resources + check5_rec.amount;
473 create_log_record(v_log_text);
474 ELSE
475 v_amount2 := check5_rec.amount;
476 l_total_equity := l_total_equity + check5_rec.amount;
477 create_log_record(v_log_text);
478 END IF;
479
480 END LOOP;
481
482 IF l_total_resources = -1*(l_total_equity) THEN
483 v_edit_check_status := 'Passed' ;
484 ELSE
485 g_error_flag := 2;
486 v_edit_check_status := 'Failed' ;
487 END IF;
488
489 create_status_record(v_edit_check_number, v_edit_check_status) ;
490
491 EXCEPTION WHEN OTHERS THEN
492
493 v_log_text := 'Exception when others in Edit Check 5. SQLCODE: '|| SQLCODE ;
494 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
495
496 v_log_text := 'SQLERRM: '|| SQLERRM ;
497 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
498
499 g_error_flag := 2;
500 END; --edit_check_5
501
502
503 PROCEDURE edit_check_6 IS
504
505 l_set_of_books_id NUMBER;
506
507 CURSOR rt7_codes_c IS
508 SELECT ffa.rt7_code_id,
509 ffc.rt7_code,
510 ffa.preclosing_unexpended_amt
511 FROM fv_facts_authorizations ffa,
512 fv_facts_rt7_codes ffc
513 WHERE ffa.treasury_symbol_id = g_treasury_symbol_id
514 AND ffa.rt7_code_id = ffc.rt7_code_id
515 AND ffa.set_of_books_id = l_set_of_books_id;
516
517 l_rt7_code_id number(15);
518 l_rt7_code varchar2(3);
519 l_accounts_balance number;
520 l_unexp_amount number;
521 l_count number;
522
523 BEGIN
524
525 init_vars;
526 v_edit_check_number := 6;
527
528 l_set_of_books_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
529 --Fetch the authorization code for the treasury symbol
530
531 OPEN rt7_codes_c;
532 l_count := 0;
533 LOOP
534
535 FETCH rt7_codes_c
536 INTO l_rt7_code_id,
537 l_rt7_code,
538 l_unexp_amount;
539
540 EXIT WHEN rt7_codes_c%NOTFOUND OR rt7_codes_c%NOTFOUND IS NULL;
541
542 --Fetch sum of account balances for the authorization code
543
544 BEGIN
545
546 SELECT NVL(sum(fft.amount),0)
547 INTO l_accounts_balance
548 FROM fv_facts_temp fft,
549 fv_facts_rt7_accounts rta
550 WHERE rta.rt7_code_id = l_rt7_code_id
551 AND rta.rt7_ussgl_account = fft.sgl_acct_number
552 AND fft.treasury_symbol_id = g_treasury_symbol_id
553 AND fft.fct_int_record_category = 'REPORTED_NEW'
554 AND fft.fct_int_record_type = 'BLK_DTL'
555 AND fft.begin_end = decode(rta.rt7_ussgl_account, '4139','B','4149','B','E');
556
557 END;
558 v_dummy_var := l_rt7_code;
559
560 v_sgl_acct_number := '1. Preclosing Unexp Amt';
561 v_amount := l_unexp_amount;
562 create_log_record(v_log_text);
563
564 v_sgl_acct_number := '2. Sum of Account Balance';
565 v_amount := l_accounts_balance;
566 create_log_record(v_log_text);
567
568 v_sgl_acct_number := 'Difference (1-2)';
569 v_amount := (l_unexp_amount - l_accounts_balance);
570 create_log_record(v_log_text);
571
572 IF (l_accounts_balance <> l_unexp_amount) THEN
573 l_count := l_count + 1;
574 END IF;
575
576 END LOOP;
577 CLOSE rt7_codes_c;
578
579 IF (l_count > 0) THEN
580 v_edit_check_status := 'Failed' ;
581 g_error_flag := 2;
582 ELSE
583 v_edit_check_status := 'Passed' ;
584 END IF;
585
586 create_status_record(v_edit_check_number, v_edit_check_status) ;
587
588 EXCEPTION WHEN OTHERS THEN
589
590 v_log_text := 'Exception when others in Edit Check 6. SQLCODE: '|| SQLCODE ;
591 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
592
593 v_log_text := 'SQLERRM: '|| SQLERRM ;
594 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
595
596 g_error_flag := 2;
597 END; --edit_check_6
598
599
600 PROCEDURE edit_check_7 IS
601
602 -- Cursor to fetch ending balance for accounts where
603 -- fund_balance_account_flag is 'Y'
604 CURSOR check7 IS
605 SELECT nvl(fft.amount,0) amount, fft.sgl_acct_number
606 FROM fv_facts_temp fft,
607 fv_facts_ussgl_accounts ffa
608 WHERE ffa.fund_balance_account_flag = 'Y'
609 AND fft.sgl_acct_number = ffa.ussgl_account
610 AND fft.treasury_symbol_id = g_treasury_symbol_id
611 AND fft.fct_int_record_category = 'REPORTED_NEW'
612 AND fft.fct_int_record_type = 'BLK_DTL'
613 AND fft.begin_end = 'E';
614
615 l_unexp_amount NUMBER;
616 l_end_balance NUMBER := 0;
617
618 BEGIN
619
620 init_vars;
621 v_edit_check_number := 7;
622
623 --Fetch preclosing ending balance for the treasury symbol
624 SELECT preclosing_unexpended_amt
625 INTO l_unexp_amount
626 FROM fv_treasury_symbols
627 WHERE treasury_symbol_id = g_treasury_symbol_id;
628
629 v_amount2 := l_unexp_amount;
630 create_log_record(v_log_text);
631
632 -- reset v_amount --> l_unexp_amount to NULL
633 -- since it needs to be printed only once
634 v_amount2 := NULL;
635
636 FOR check7_rec in check7
637 LOOP
638 v_sgl_acct_number := check7_rec.sgl_acct_number;
639 v_amount1 := check7_rec.amount;
640 create_log_record(v_log_text);
641
642 l_end_balance := l_end_balance + check7_rec.amount;
643
644 END LOOP;
645
646 IF (l_unexp_amount is NULL ) THEN
647 v_edit_check_status := 'Failed' ;
648 ELSIF (l_unexp_amount = l_end_balance) THEN
649 v_edit_check_status := 'Passed' ;
650 ELSE
651 v_edit_check_status := 'Failed' ;
652 g_error_flag := 2;
653 END IF;
654
655 create_status_record(v_edit_check_number, v_edit_check_status) ;
656
657 EXCEPTION WHEN OTHERS THEN
658
659 v_log_text := 'Exception when others in Edit Check 7. SQLCODE: '|| SQLCODE ;
660 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
661
662 v_log_text := 'SQLERRM: '|| SQLERRM ;
663 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
664
665 g_error_flag := 2;
666 END; -- edit_check_7
667
668 -- Added the foll to fix bug 1974485
669 PROCEDURE edit_check_10 IS
670
671 l_total_amount NUMBER := 0;
672
673 CURSOR check10 IS
674 SELECT fft.sgl_acct_number,
675 SUM(NVL(fft.amount,0)) amount
676 FROM fv_facts_temp fft,
677 fv_facts_ussgl_accounts ffacc
678 WHERE ffacc.ussgl_account = fft.sgl_acct_number
679 AND fft.treasury_symbol_id = g_treasury_symbol_id
680 AND fft.fct_int_record_category = 'REPORTED_NEW'
681 AND fft.fct_int_record_type = 'BLK_DTL'
682 AND fft.begin_end = 'E'
683 AND ffacc.cancelled_flag = 'Y'
684 GROUP BY fft.sgl_acct_number;
685
686 BEGIN
687 init_vars;
688 v_edit_check_number := 10;
689
690 FOR check10_rec IN check10
691 LOOP
692 v_sgl_acct_number := check10_rec.sgl_acct_number;
693 v_amount := check10_rec.amount;
694 create_log_record(v_log_text);
695 l_total_amount := l_total_amount + check10_rec.amount;
696
697 END LOOP;
698
699 IF l_total_amount = 0 THEN
700 v_edit_check_status := 'Passed' ;
701 ELSE
702 v_edit_check_status := 'Failed' ;
703 END IF;
704
705 create_status_record(v_edit_check_number, v_edit_check_status) ;
706
707 EXCEPTION WHEN OTHERS THEN
708
709 v_log_text := 'Exception when others in Edit Check 10. SQLCODE: '||SQLCODE ;
710 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
711
712 v_log_text := 'SQLERRM: '||SQLERRM ;
713 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
714
715 g_error_flag := 2;
716 END; --edit_check_10
717
718 PROCEDURE edit_check_11 IS
719
720 CURSOR neg_rec_pay_c IS
721 SELECT ffacc.ussgl_account,
722 NVL(sum(fft.amount),0),
723 ffacc.ye_neg_receivables_flag,
724 ffacc.natural_balance
725 FROM fv_facts_temp fft,
726 fv_facts_ussgl_accounts ffacc
727 WHERE (ffacc.ye_neg_receivables_flag = 'Y'
728 OR ffacc.ye_neg_payables_flag = 'Y')
729 AND ffacc.ussgl_account = fft.sgl_acct_number
730 AND fft.treasury_symbol_id = g_treasury_symbol_id
731 AND fft.fct_int_record_category = 'REPORTED_NEW'
732 AND fft.fct_int_record_type = 'BLK_DTL'
733 AND fft.begin_end = 'E'
734 GROUP BY ffacc.ussgl_account, ffacc.ye_neg_receivables_flag, ffacc.natural_balance;
735
736 CURSOR general_acc_c IS
737 SELECT ffacc.ussgl_account, nvl(sum(fft.amount),0),
738 ffacc.natural_balance
739 FROM fv_facts_temp fft,
740 fv_facts_ussgl_accounts ffacc
741 WHERE ffacc.ye_general_flag = 'Y'
742 AND ffacc.ussgl_account = fft.sgl_acct_number
743 AND fft.treasury_symbol_id = g_treasury_symbol_id
744 AND fft.fct_int_record_category = 'REPORTED_NEW'
745 AND fft.fct_int_record_type = 'BLK_DTL'
746 AND fft.begin_end = 'E'
747 GROUP BY ffacc.ussgl_account, ffacc.natural_balance;
748
749 l_ussgl_account varchar2(30);
750 l_amount number;
751 l_acc_type varchar2(25);
752 l_count number;
753 l_neg_receivables_flag varchar2(1);
754 l_natural_balance FV_FACTS_USSGL_ACCOUNTS.NATURAL_BALANCE%TYPE;
755 l_dc_ind varchar2(1);
756
757 BEGIN
758
759 init_vars;
760
761 l_count := 0;
762 v_edit_check_number := 11;
763
764 --Fetch accounts with negative receivable and negative payables balances
765 OPEN neg_rec_pay_c;
766
767 LOOP
768 FETCH neg_rec_pay_c
769 INTO l_ussgl_account,
770 l_amount,
771 l_neg_receivables_flag,
772 l_natural_balance;
773
774 EXIT WHEN neg_rec_pay_c%NOTFOUND OR neg_rec_pay_c%NOTFOUND IS NULL;
775
776 SELECT DECODE (l_neg_receivables_flag,'Y','NR','NP')
777 INTO l_acc_type
778 FROM DUAL;
779
780 v_sgl_acct_number := l_ussgl_account;
781 v_amount := l_amount;
782 v_dummy_var := l_acc_type;
783 -- create_log_record(v_log_text);
784
785 IF (l_amount > 0) THEN
786 l_dc_ind := 'D';
787 ELSE
788 l_dc_ind := 'C';
789 END IF;
790
791 IF (l_amount <> 0 AND l_dc_ind <> l_natural_balance) THEN
792
793 create_log_record(v_log_text);
794
795 l_count := l_count +1;
796
797 --LGOEL: Update temp table if Edit check 11 failed
798 update fv_facts_temp
799 set document_number = 'Y'
800 where sgl_acct_number = l_ussgl_account
801 and treasury_symbol_id = g_treasury_symbol_id
802 and fct_int_record_category = 'REPORTED_NEW'
803 and fct_int_record_type = 'BLK_DTL';
804
805 -- Enable the foot note flag for this failed
806 -- edit check
807 UPDATE fv_facts_submission
808 SET foot_note_flag = 'Y'
809 WHERE treasury_symbol_id = g_treasury_symbol_id;
810
811 END IF;
812
813 END LOOP;
814
815 CLOSE neg_rec_pay_c;
816
817 -- Initialize variables
818 l_ussgl_account := NULL;
819 l_amount := 0;
820 l_natural_balance := NULL;
821
822 --Fetch General accounts which have a balance
823 OPEN general_acc_c;
824
825 LOOP
826
827 FETCH general_acc_c
828 INTO l_ussgl_account,
829 l_amount,
830 l_natural_balance;
831
832 EXIT WHEN general_acc_c%NOTFOUND OR general_acc_c%NOTFOUND IS NULL;
833
834 v_sgl_acct_number := l_ussgl_account;
835 v_amount := l_amount;
836 v_dummy_var := 'GL';
837 -- create_log_record(v_log_text);
838
839 IF (l_amount > 0) THEN
840 l_dc_ind := 'D';
841 ELSE
842 l_dc_ind := 'C';
843 END IF;
844
845 IF (l_amount <> 0 ) THEN
846
847 create_log_record(v_log_text);
848
849 l_count := l_count +1;
850
851 --LGOEL: Update temp table if Edit check 11 failed
852 update fv_facts_temp
853 set document_number = 'Y'
854 where sgl_acct_number = l_ussgl_account
855 and treasury_symbol_id = g_treasury_symbol_id
856 and fct_int_record_category = 'REPORTED_NEW'
857 and fct_int_record_type = 'BLK_DTL';
858
859 -- Enable the foot note flag for this failed
860 -- edit check
861 UPDATE fv_facts_submission
862 SET foot_note_flag = 'Y'
863 WHERE treasury_symbol_id = g_treasury_symbol_id;
864
865 END IF;
866 END LOOP;
867 CLOSE general_acc_c;
868
869 IF (l_count = 0) THEN
870 v_edit_check_status := 'Passed' ;
871
872 -- Disable the foot note flag for this passed
873 -- edit check in case it has failed earlier
874 UPDATE fv_facts_submission
875 SET foot_note_flag = 'N'
876 WHERE treasury_symbol_id = g_treasury_symbol_id;
877
878 ELSE
879 v_edit_check_status := 'Failed' ;
880 IF (g_error_flag = 0) THEN
881 g_error_flag := 1;
882 END IF;
883 END IF;
884
885 create_status_record(v_edit_check_number, v_edit_check_status) ;
886
887 EXCEPTION WHEN OTHERS THEN
888
889 v_log_text := 'Exception when others in Edit Check 11. SQLCODE: '||SQLCODE ;
890 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
891
892 v_log_text := 'SQLERRM: '|| SQLERRM ;
893 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
894
895 IF (g_error_flag = 0) THEN
896 g_error_flag := 1;
897 END IF;
898 END; --edit_check_11
899
900
901 PROCEDURE edit_check_12(p_facts_run_quarter number) IS
902
903 -- Cursor to fetch amounts
904 -- for disbursement and collection accounts
905 CURSOR check12 IS
906 SELECT fft.sgl_acct_number,
907 ffa.disbursements_flag,
908 ffa.collections_flag,
909 ffa.edck12_balance_type,
910 nvl(amount,0) amount
911 FROM fv_facts_temp fft,
912 fv_facts_ussgl_accounts ffa
913 WHERE (ffa.disbursements_flag = 'Y' or ffa.collections_flag = 'Y')
914 AND ffa.ussgl_account = fft.sgl_acct_number
915 AND fft.treasury_symbol_id = g_treasury_symbol_id
916 AND fft.fct_int_record_category = 'REPORTED_NEW'
917 AND fft.fct_int_record_type = 'BLK_DTL'
918 AND fft.begin_end = 'E';
919
920 l_disbursements number := 0;
921 l_collections number := 0;
922 l_net_outlays number := 0;
923 l_224_outlays number := 0;
924 v_begin_amount number := 0;
925
926 BEGIN
927 init_vars;
928 v_edit_check_number := 12;
929
930 --Fetch 224 Outlays
931 select decode(p_facts_run_quarter,1,sf224_qtr1_outlay,
932 2,sf224_qtr2_outlay,3,sf224_qtr3_outlay,sf224_qtr4_outlay)
933 into l_224_outlays
934 from fv_treasury_symbols
935 where treasury_symbol_id = g_treasury_symbol_id;
936
937 IF (l_224_outlays is NULL) THEN
938
939 v_amount := NULL;
940 v_amount1 := NULL;
941 v_amount2 := NULL;
942
943 create_log_record(v_log_text);
944 v_edit_check_status := 'Not Applicable' ;
945
946 create_status_record(v_edit_check_number, v_edit_check_status) ;
947
948 IF (g_error_flag = 0) then
949 g_error_flag := 1;
950 END IF;
951
952 ELSE
953
954 FOR check12_rec IN check12
955 LOOP
956 v_sgl_acct_number := check12_rec.sgl_acct_number;
957 v_begin_amount := 0;
958
959 IF check12_rec.edck12_balance_type = 'S' THEN
960 SELECT fft.amount
961 INTO v_begin_amount
962 FROM fv_facts_temp fft
963 WHERE fft.sgl_acct_number = check12_rec.sgl_acct_number
964 AND fft.treasury_symbol_id = g_treasury_symbol_id
965 AND fft.fct_int_record_category = 'REPORTED_NEW'
966 AND fft.fct_int_record_type = 'BLK_DTL'
967 AND fft.begin_end = 'B';
968 END IF;
969
970 IF check12_rec.collections_flag = 'Y' THEN
971
972 v_amount1 := check12_rec.amount - v_begin_amount;
973 v_amount2 := NULL;
974
975 create_log_record(v_log_text);
976 l_collections := l_collections + v_amount1;
977 ELSIF
978 check12_rec.disbursements_flag = 'Y' THEN
979 v_amount2 := check12_rec.amount - v_begin_amount;
980 v_amount1 := NULL;
981
982 create_log_record(v_log_text);
983 l_disbursements := l_disbursements + v_amount2;
984 END IF;
985
986 END LOOP;
987
988 l_net_outlays := -1*(l_disbursements + l_collections);
989
990 v_edit_check_number := 13;
991
992 v_sgl_acct_number := '1. Net Outlays';
993 v_amount := l_net_outlays;
994 create_log_record(v_log_text);
995
996 v_sgl_acct_number := '2. 224 Outlays';
997 v_amount := l_224_outlays;
998 create_log_record(v_log_text);
999
1000 v_sgl_acct_number := 'Difference (1-2)';
1001 v_amount := (l_net_outlays - l_224_outlays);
1002 create_log_record(v_log_text);
1003
1004 IF (l_net_outlays = l_224_outlays) THEN
1005 v_edit_check_status := 'Passed' ;
1006 ELSE
1007 v_edit_check_status := 'Failed' ;
1008 g_error_flag := 2;
1009 END IF;
1010
1011 v_edit_check_number := 12;
1012 create_status_record(v_edit_check_number, v_edit_check_status) ;
1013 -- Inserting dummy record for edit check 12 Net outlays printing
1014 v_edit_check_number := 13;
1015 create_status_record(v_edit_check_number, v_edit_check_status) ;
1016 END IF;
1017
1018 EXCEPTION WHEN OTHERS THEN
1019
1020 v_log_text := 'Exception when others in Edit Check 12. SQLCODE: '||SQLCODE ;
1021 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1022
1023 v_log_text := 'SQLERRM: '|| SQLERRM ;
1024 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1025
1026 g_error_flag := 2;
1027 END; --edit_check_12
1028
1029
1030 -- This is the main procedure which calls all the edit check procedures
1031
1032 procedure perform_edit_checks (errbuf out varchar2,
1033 retcode out number,
1034 p_treasury_symbol_id IN number,
1035 p_facts_run_quarter IN number,
1036 p_rep_fiscal_yr IN NUMBER)
1037 is
1038 -- Added to fix 1974485
1039 l_cancel_date NUMBER(4);
1040
1041 begin
1042
1043 g_error_flag := 0;
1044 g_treasury_symbol_id := p_treasury_symbol_id;
1045
1046 SELECT to_number(to_char(cancellation_date,'YYYY'))
1047 INTO l_cancel_date
1048 FROM fv_treasury_symbols
1049 WHERE treasury_symbol_id = g_treasury_symbol_id;
1050
1051 v_log_text := 'Edit Check process start...' ;
1052 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1053
1054 v_log_text := ' ' ;
1055 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1056
1057 edit_check_1;
1058 edit_check_2;
1059 edit_check_3;
1060 if (p_facts_run_quarter = 4) then
1061 edit_check_4;
1062 edit_check_5;
1063 edit_check_6;
1064 edit_check_7;
1065
1066 -- Added to fix 1974485
1067
1068 IF l_cancel_date = p_rep_fiscal_yr
1069 THEN edit_check_10;
1070 ELSE
1071 init_vars;
1072
1073 v_edit_check_number := 10;
1074 v_edit_check_status := 'Not Applicable' ;
1075
1076 create_status_record(v_edit_check_number, v_edit_check_status) ;
1077 -- create_log_record(v_log_text);
1078
1079 END IF;
1080
1081 edit_check_11;
1082
1083 else
1084
1085 -- Changed log text for bug 2053780
1086
1087 v_log_text := 'Edit Checks 4,5,6,7,10 and 11 are not needed' ;
1088 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1089
1090 v_log_text := ' ';
1091 v_edit_check_status := 'Not Needed';
1092
1093 v_edit_check_number := 4;
1094 create_status_record(v_edit_check_number, v_edit_check_status) ;
1095
1096 v_edit_check_number := 5;
1097 create_status_record(v_edit_check_number, v_edit_check_status) ;
1098
1099 v_edit_check_number := 6;
1100 create_status_record(v_edit_check_number, v_edit_check_status) ;
1101
1102 v_edit_check_number := 7;
1103 create_status_record(v_edit_check_number, v_edit_check_status) ;
1104
1105 v_edit_check_number := 10;
1106 create_status_record(v_edit_check_number, v_edit_check_status) ;
1107
1108 v_edit_check_number := 11;
1109 create_status_record(v_edit_check_number, v_edit_check_status) ;
1110
1111 end if;
1112
1113 edit_check_12(p_facts_run_quarter);
1114
1115 retcode := g_error_flag;
1116 if (retcode = 1) then
1117 errbuf := 'Soft Edit Check Failed.' ;
1118 v_log_text := 'Soft Edit Check Failed.' ;
1119 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1120
1121 elsif (retcode = 2) then
1122 errbuf := 'Hard Edit Check Failed';
1123 v_log_text := 'Hard Edit Check Failed.' ;
1124 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1125
1126 else
1127 v_log_text := 'Edit Check Passed.' ;
1128 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1129 end if;
1130
1131 end; --perform_edit_checks
1132
1133 -------------------------------------------------------------------------
1134 -- Procedure CREATE_STATUS_RECORD
1135 -------------------------------------------------------------------------
1136 -- This Procedure inserts the status information into
1137 -- fv_facts_edit_check_status
1138 -------------------------------------------------------------------------
1139
1140 PROCEDURE create_status_record (p_edit_check_number NUMBER,
1141 p_edit_check_status VARCHAR2)
1142 IS
1143
1144 BEGIN
1145 INSERT INTO fv_facts_edit_check_status
1146 (treasury_symbol_id,
1147 edit_check_number,
1148 edit_check_status)
1149 VALUES (g_treasury_symbol_id,
1150 p_edit_check_number,
1151 p_edit_check_status) ;
1152 EXCEPTION
1153 when others then
1154 g_error_flag := sqlcode ;
1155 fnd_file.put_line(FND_FILE.LOG, sqlerrm || ' [CREATE_STATUS_RECORD] ') ;
1156 return;
1157 END; -- create_status_record
1158 -------------------------------------------------------------------------
1159 -- Procedure CREATE_LOG_RECORD
1160 -------------------------------------------------------------------------
1161 -- This Procedure inserts the log information into the FACTS temp
1162 -- table under the record category 'EDIT_CHECK_LOG_INFO'
1163 -------------------------------------------------------------------------
1164 Procedure CREATE_LOG_RECORD (text varchar2)
1165 is
1166 Begin
1167 v_log_counter := v_log_counter + 1 ;
1168
1169 Insert into FV_FACTS_TEMP
1170 (FCT_INT_RECORD_CATEGORY ,
1171 TREASURY_SYMBOL_ID ,
1172 TBAL_ACCT_NUM ,
1173 FACTS_REPORT_INFO ,
1174 edit_check_number ,
1175 amount ,
1176 amount1 ,
1177 amount2 ,
1178 sgl_acct_number ,
1179 budget_function )
1180 Values
1181 ('FACTS2_EDIT_CHECK_LOG' ,
1182 g_treasury_symbol_id ,
1183 v_log_counter ,
1184 text ,
1185 v_edit_check_number ,
1186 v_amount ,
1187 v_amount1 ,
1188 v_amount2 ,
1189 v_sgl_acct_number ,
1190 v_dummy_var ) ;
1191 EXCEPTION
1192 When Others Then
1193 g_error_flag := sqlcode ;
1194 fnd_file.put_line(FND_FILE.LOG, sqlerrm || ' [CREATE_LOG_RECORD] ') ;
1195 return;
1196
1197 END CREATE_LOG_RECORD ;
1198
1199 end FVFCCHKB_PKG; -- Package body