[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_EDIT_CHECK
Source
1 package body fv_facts_edit_check AS
2 --$Header: FVFCCHKB.pls 120.14.12010000.1 2008/07/28 06:30:27 appldev ship $
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_FACTS_EDIT_CHECK.';
4
5 g_error_flag NUMBER(1);
6 g_treasury_symbol_id NUMBER(15);
7
8 -- Addded on 07/13/2000 By Supadman
9 -- Variable to hold log text.
10 v_log_text Varchar2(416) ;
11 v_log_counter Number := 0 ;
12
13
14 v_edit_check_number NUMBER(2);
15 v_edit_check_status VARCHAR2(25);
16 v_amount NUMBER := 0;
17 v_amount1 NUMBER := 0;
18 v_amount2 NUMBER := 0;
19 v_sgl_acct_number fv_facts_temp.sgl_acct_number%TYPE;
20 v_dummy_var VARCHAR2(3);
21 v_row_count NUMBER := 0;
22
23
24
25 PROCEDURE Create_log_record(text varchar2) ;
26
27 PROCEDURE create_status_record(p_edit_check_number number,
28 p_edit_check_status varchar2) ;
29
30 -- Procedure to initialize variables
31 PROCEDURE init_vars IS
32 l_module_name VARCHAR2(200) := g_module_name || 'init_vars';
33
34 BEGIN
35 v_edit_check_number := NULL;
36 v_edit_check_status := NULL;
37 v_amount := 0 ;
38 v_amount1 := 0 ;
39 v_amount2 := 0 ;
40 v_sgl_acct_number := NULL;
41 v_log_text := ' ' ;
42 v_dummy_var := NULL;
43 v_row_count := 0 ;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 v_log_text := SQLERRM;
48 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
49 RAISE;
50
51 END init_vars;
52
53 PROCEDURE edit_check_1 IS
54 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_1';
55
56 l_total_credit NUMBER := 0;
57 l_total_debit NUMBER := 0;
58
59 -- Cursor to fetch Credit/Debit Ending balance
60 -- from FV_FACTS_TEMP for budgetary accounts
61 CURSOR check1 IS
62 SELECT nvl(amount,0) amount, debit_credit,
63 sgl_acct_number
64 FROM fv_facts_temp
65 WHERE treasury_symbol_id = g_treasury_symbol_id
66 AND fct_int_record_category = 'REPORTED_NEW'
67 AND fct_int_record_type = 'BLK_DTL'
68 AND sgl_acct_number like '4%'
69 AND begin_end = 'E'
70 AND amount <> 0
71 ORDER BY sgl_acct_number;
72
73 BEGIN
74 init_vars;
75 v_edit_check_number := 1;
76
77 FOR check1_rec IN check1
78 LOOP
79 IF check1_rec.debit_credit = 'C' THEN
80 l_total_credit := l_total_credit + check1_rec.amount;
81 ELSE
82 l_total_debit := l_total_debit + check1_rec.amount;
83 END IF;
84
85 /* Added space in from of account number to order the edit check 8 report information*/
86 v_sgl_acct_number := ' '||check1_rec.sgl_acct_number;
87 v_amount := check1_rec.amount;
88 create_log_record(v_log_text);
89
90 END LOOP;
91
92 IF -1*(l_total_credit) = l_total_debit THEN
93 v_edit_check_status := 'Passed';
94 ELSE
95 g_error_flag := 2;
96 v_edit_check_status := 'Failed';
97 END IF;
98
99 create_status_record(v_edit_check_number, v_edit_check_status) ;
100
101 EXCEPTION WHEN OTHERS THEN
102 v_log_text := SQLERRM;
103 g_error_flag := 2;
104 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
105 END; --edit_check_1
106
107
108 PROCEDURE edit_check_2 IS
109 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_2';
110
111 -- Cursor to fetch SGL account and associated attributes from
112 -- FV_FACTS_USSGL_ACCOUNTS for all budgetary accounts
113 -- existing in FV_FACTS_TEMP
114 CURSOR fv_facts_ussgl_accounts_c IS
115 SELECT ffa.ussgl_account,
116 ffa.total_resource_be_flag, ffa.total_resource_dc_flag,
117 ffa.resource_status_be_flag, ffa.resource_status_dc_flag
118 FROM fv_facts_ussgl_accounts ffa
119 WHERE ffa.ussgl_account like '4%'
120 AND EXISTS
121 (SELECT 'x'
122 FROM fv_facts_temp fft
123 WHERE fft.treasury_symbol_id = g_treasury_symbol_id
124 AND fft.sgl_acct_number = ffa.ussgl_account);
125
126 l_ussgl_account varchar2(30);
127 l_total_resource_be_flag varchar2(1);
128 l_total_resource_dc_flag varchar2(1);
129 l_resource_status_be_flag varchar2(1);
130 l_resource_status_dc_flag varchar2(1);
131 l_begin_bal number;
132 l_begin_bal_dc_ind varchar2(1);
133 l_end_bal number;
134 l_end_bal_dc_ind varchar2(1);
135 l_balance number;
136 l_dc_ind varchar2(1);
137 l_to_total number := 0;
138 l_st_total number := 0;
139
140 l_to_amount NUMBER := 0;
141 l_st_amount NUMBER := 0;
142
143 BEGIN
144 init_vars;
145 v_edit_check_number := 2;
146
147 OPEN fv_facts_ussgl_accounts_c;
148
149 LOOP
150
151 FETCH fv_facts_ussgl_accounts_c
152 INTO l_ussgl_account,
153 l_total_resource_be_flag,
154 l_total_resource_dc_flag,
155 l_resource_status_be_flag,
156 l_resource_status_dc_flag;
157
158 EXIT WHEN fv_facts_ussgl_accounts_c%NOTFOUND
159 OR fv_facts_ussgl_accounts_c%NOTFOUND IS NULL;
160
161 -- Fetch beginning balance and set debit_credit
162 -- indicator for the SGL account
163
164 BEGIN
165
166 SELECT nvl(sum(amount),0)
167 INTO l_begin_bal
168 FROM fv_facts_temp
169 WHERE treasury_symbol_id = g_treasury_symbol_id
170 AND fct_int_record_category = 'REPORTED_NEW'
171 AND fct_int_record_type = 'BLK_DTL'
172 AND sgl_acct_number = l_ussgl_account
173 AND begin_end = 'B';
174
175 IF (l_begin_bal > 0) THEN
176 l_begin_bal_dc_ind := 'D';
177 ELSE
178 l_begin_bal_dc_ind := 'C';
179 END IF;
180
181 EXCEPTION WHEN NO_DATA_FOUND THEN
182 l_begin_bal := 0;
183
184 END;
185
186 -- Fetch Ending Balance and set debit_credit
187 -- indicator for the SGL account
188
189 BEGIN
190
191 SELECT nvl(sum(amount),0)
192 INTO l_end_bal
193 FROM fv_facts_temp
194 WHERE treasury_symbol_id = g_treasury_symbol_id
195 AND fct_int_record_category = 'REPORTED_NEW'
196 AND fct_int_record_type = 'BLK_DTL'
197 AND sgl_acct_number = l_ussgl_account
198 AND begin_end = 'E';
199
200 IF (l_end_bal > 0) THEN
201 l_end_bal_dc_ind := 'D';
202 ELSE
203 l_end_bal_dc_ind := 'C';
204 END IF;
205
206 EXCEPTION WHEN NO_DATA_FOUND THEN
207 l_end_bal := 0;
208
209 END;
210
211 v_amount1 := 0;
212 v_amount2 := 0;
213
214 IF (l_total_resource_be_flag = 'E') THEN
215
216 v_amount1 := l_end_bal;
217
218 IF (l_total_resource_dc_flag = 'D' and l_end_bal_dc_ind = 'D') THEN
219 l_to_total := l_to_total + l_end_bal;
220 ELSIF (l_total_resource_dc_flag = 'C' and l_end_bal_dc_ind = 'C') THEN
221 l_to_total := l_to_total + l_end_bal;
222 ELSIF (l_total_resource_dc_flag = 'E') THEN
223 l_to_total := l_to_total + l_end_bal;
224 END IF;
225 ELSIF (l_total_resource_be_flag = 'B' ) THEN
226
227 v_amount1 := l_begin_bal;
228
229 IF (l_total_resource_dc_flag = 'D' and l_begin_bal_dc_ind = 'D') THEN
230 l_to_total := l_to_total + l_begin_bal;
231 ELSIF (l_total_resource_dc_flag = 'C' and l_begin_bal_dc_ind = 'C') THEN
232 l_to_total := l_to_total + l_begin_bal;
233 ELSIF (l_total_resource_dc_flag = 'E') THEN
234 l_to_total := l_to_total + l_begin_bal;
235 END IF;
236 END IF;
237
238 IF (l_resource_status_be_flag = 'E') THEN
239
240 v_amount2 := l_end_bal;
241
242 IF (l_resource_status_dc_flag = 'D' and l_end_bal_dc_ind = 'D') THEN
243 l_st_total := l_st_total + l_end_bal;
244 ELSIF (l_resource_status_dc_flag = 'C' and l_end_bal_dc_ind = 'C') THEN
245 l_st_total := l_st_total + l_end_bal;
246 ELSIF (l_resource_status_dc_flag = 'E') THEN
247 l_st_total := l_st_total + l_end_bal;
248 END IF;
249 ELSIF (l_resource_status_be_flag = 'B' ) THEN
250
251 v_amount2 := l_begin_bal;
252
253 IF (l_resource_status_dc_flag = 'D' and l_begin_bal_dc_ind = 'D') THEN
254 l_st_total := l_st_total + l_begin_bal;
255 ELSIF (l_resource_status_dc_flag = 'C' and l_begin_bal_dc_ind = 'C') THEN
256 l_st_total := l_st_total + l_begin_bal;
257 ELSIF (l_resource_status_dc_flag = 'E') THEN
258 l_st_total := l_st_total + l_begin_bal;
259 END IF;
260 ELSIF (l_resource_status_be_flag = 'S' ) THEN
261
262 l_balance := l_end_bal - l_begin_bal;
263 IF ( l_balance > 0) THEN
264 l_dc_ind := 'D';
265 ELSE
266 l_dc_ind := 'C';
267 END IF;
268 IF (l_resource_status_dc_flag = 'D' and l_dc_ind = 'D') THEN
269 l_st_total := l_st_total + l_balance;
270 ELSIF (l_resource_status_dc_flag = 'C' and l_dc_ind = 'C') THEN
271 l_st_total := l_st_total + l_balance;
272 ELSIF (l_resource_status_dc_flag = 'E') THEN
273 l_st_total := l_st_total + l_balance;
274 END IF;
275
276 v_amount2 := l_balance;
277
278 END IF;
279
280
281 IF (l_total_resource_be_flag = 'E') AND (l_resource_status_be_flag = 'E') THEN
282 IF l_end_bal_dc_ind = 'D' THEN
283 v_amount2 := 0;
284 ELSE
285 v_amount1 := 0;
286 END IF;
287 END IF;
288
289 /* Added space in from of account number to order the edit check 8 report information*/
290 v_sgl_acct_number := ' '||l_ussgl_account;
291 create_log_record(v_log_text);
292
293 END LOOP;
294
295 CLOSE fv_facts_ussgl_accounts_c;
296
297 l_st_total := -1*l_st_total;
298
299 IF (l_to_total = l_st_total) THEN
300 v_edit_check_status := 'Passed';
301 ELSE
302 g_error_flag := 2;
303 v_edit_check_status := 'Failed';
304 END IF;
305
306 create_status_record(v_edit_check_number, v_edit_check_status) ;
307
308
309 EXCEPTION WHEN OTHERS THEN
310 v_log_text := SQLERRM;
311 g_error_flag := 2;
312 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
313 END; --edit_check_2
314
315
316 PROCEDURE edit_check_3 IS
317 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_3';
318
319 l_budget_credit NUMBER := 0;
320 l_budget_debit NUMBER := 0;
321
322 -- Cursor to fetch Credit and Debit Beginning balance
323 -- from FV_FACTS_TEMP for budgetary accounts
324 CURSOR check3 IS
325 SELECT nvl(amount,0) amount, debit_credit, sgl_acct_number
326 FROM fv_facts_temp
327 WHERE treasury_symbol_id = g_treasury_symbol_id
328 AND fct_int_record_category = 'REPORTED_NEW'
329 AND fct_int_record_type = 'BLK_DTL'
330 AND sgl_acct_number like '4%'
331 AND begin_end = 'B';
332
333
334 BEGIN
335 init_vars;
336 v_edit_check_number := 3;
337
338 FOR check3_rec in check3
339 LOOP
340 v_amount := check3_rec.amount;
341 /* Added space in from of account number to order the edit check 8 report information*/
342 v_sgl_acct_number := ' '||check3_rec.sgl_acct_number;
343
344 create_log_record(v_log_text);
345
346 IF check3_rec.debit_credit = 'C' THEN
347 l_budget_credit := l_budget_credit + v_amount;
348 ELSE
349 l_budget_debit := l_budget_debit + v_amount;
350 END IF;
351
352 END LOOP;
353
354 IF l_budget_debit = -1*(l_budget_credit) THEN
355 v_edit_check_status := 'Passed';
356 ELSE
357 g_error_flag := 2;
358 v_edit_check_status := 'Failed';
359 END IF;
360
361 create_status_record(v_edit_check_number, v_edit_check_status) ;
362
363
364 EXCEPTION WHEN OTHERS THEN
365 v_log_text := SQLERRM;
366 g_error_flag := 2;
367 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
368 END; --edit_check_3
369
370
371 PROCEDURE edit_check_4 IS
372 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_4';
373
374 -- Cursor to fetch ending balance, account number for all accounts
375 -- where YE_ANTICIPATED_FLAG = 'Y'
376 CURSOR anticipated_items_c is
377 SELECT nvl(sum(fft.amount),0), fft.sgl_acct_number
378 FROM fv_facts_temp fft,
379 fv_facts_ussgl_accounts ffa
380 WHERE treasury_symbol_id = g_treasury_symbol_id
381 AND fct_int_record_category = 'REPORTED_NEW'
382 AND fct_int_record_type = 'BLK_DTL'
383 AND ffa.ussgl_account = fft.sgl_acct_number
384 AND ffa.ye_anticipated_flag = 'Y'
385 AND fft.begin_end = 'E'
386 GROUP BY fft.sgl_acct_number;
387
388 l_count number;
389 l_amount NUMBER := 0;
390 l_sgl_acct_number varchar2(30);
391
392
393 BEGIN
394
395 init_vars;
396
397 l_count := 0;
398 v_edit_check_number := 4;
399
400 OPEN anticipated_items_c;
401 LOOP
402
403 FETCH anticipated_items_c
404 INTO l_amount, l_sgl_acct_number;
405
406 EXIT WHEN anticipated_items_c%NOTFOUND OR anticipated_items_c%NOTFOUND IS NULL;
407
408 IF (l_amount <> 0) THEN
409 l_count := l_count +1;
410
411 v_amount := l_amount;
412 /* Added space in from of account number to order the edit check 8 report information*/
413 v_sgl_acct_number := ' '||l_sgl_acct_number;
414 create_log_record(v_log_text);
415 END IF;
416
417 END LOOP;
418
419 IF (l_count > 0) THEN
420 g_error_flag := 2;
421 v_edit_check_status := 'Failed';
422 ELSE
423 v_edit_check_status := 'Passed';
424 v_amount := NULL;
425 -- create_log_record(v_log_text);
426 END IF;
427
428 create_status_record(v_edit_check_number, v_edit_check_status) ;
429
430 CLOSE anticipated_items_c;
431
432 EXCEPTION WHEN OTHERS THEN
433 v_log_text := SQLERRM;
434 g_error_flag := 2;
435 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
436 END; --edit_check_4
437
438 PROCEDURE edit_check_5 IS
439 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_5';
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 /* Added space in from of account number to order the edit check 8 report information*/
469 v_sgl_acct_number := ' '||check5_rec.sgl_acct_number;
470
474 create_log_record(v_log_text);
471 IF check5_rec.ye_resource_equity_flag = 'R' THEN
472 v_amount1 := check5_rec.amount;
473 l_total_resources := l_total_resources + check5_rec.amount;
475 ELSE
476 v_amount2 := check5_rec.amount;
477 l_total_equity := l_total_equity + check5_rec.amount;
478 create_log_record(v_log_text);
479 END IF;
480
481 END LOOP;
482
483 IF l_total_resources = -1*(l_total_equity) THEN
484 v_edit_check_status := 'Passed' ;
485 ELSE
486 g_error_flag := 2;
487 v_edit_check_status := 'Failed' ;
488 END IF;
489
490 create_status_record(v_edit_check_number, v_edit_check_status) ;
491
492 EXCEPTION WHEN OTHERS THEN
493 v_log_text := SQLERRM;
494 g_error_flag := 2;
495 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
496 END; --edit_check_5
497
498
499 PROCEDURE edit_check_6 (p_ledger_id NUMBER) IS
500 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_6';
501
502 -- l_set_of_books_id NUMBER;
503
504 CURSOR rt7_codes_c IS
505 SELECT ffa.rt7_code_id,
506 ffc.rt7_code,
507 ffa.preclosing_unexpended_amt
508 FROM fv_facts_authorizations ffa,
509 fv_facts_rt7_codes ffc
510 WHERE ffa.treasury_symbol_id = g_treasury_symbol_id
511 AND ffa.rt7_code_id = ffc.rt7_code_id
512 AND ffa.set_of_books_id = p_ledger_id;
513
514 l_rt7_code_id number(15);
515 l_rt7_code varchar2(3);
516 l_accounts_balance number;
517 l_unexp_amount number;
518 l_count number;
519
520 BEGIN
521
522 init_vars;
523 v_edit_check_number := 6;
524
525 -- l_set_of_books_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
526 --Fetch the authorization code for the treasury symbol
527
528 OPEN rt7_codes_c;
529 l_count := 0;
530 LOOP
531
532 FETCH rt7_codes_c
533 INTO l_rt7_code_id,
534 l_rt7_code,
535 l_unexp_amount;
536
537 EXIT WHEN rt7_codes_c%NOTFOUND OR rt7_codes_c%NOTFOUND IS NULL;
538
539 --Fetch sum of account balances for the authorization code
540
541 BEGIN
542
543 SELECT NVL(sum(fft.amount),0)
544 INTO l_accounts_balance
545 FROM fv_facts_temp fft,
546 fv_facts_rt7_accounts rta
547 WHERE rta.rt7_code_id = l_rt7_code_id
548 AND rta.rt7_ussgl_account = fft.sgl_acct_number
549 AND fft.treasury_symbol_id = g_treasury_symbol_id
550 AND fft.fct_int_record_category = 'REPORTED_NEW'
551 AND fft.fct_int_record_type = 'BLK_DTL'
552 AND fft.begin_end = decode(rta.rt7_ussgl_account, '4139','B','4149','B','E');
553
554 END;
555 v_dummy_var := l_rt7_code;
556
557 /* Added space in from of account number to order the edit check 8 report information*/
558 v_sgl_acct_number := ' 1. Preclosing Unexp Amt';
559 v_amount := l_unexp_amount;
560 create_log_record(v_log_text);
561
562 /* Added space in from of account number to order the edit check 8 report information*/
563 v_sgl_acct_number := ' 2. Sum of Account Balance';
564 v_amount := l_accounts_balance;
565 create_log_record(v_log_text);
566
567 /* Added space in from of account number to order the edit check 8 report information*/
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 v_log_text := SQLERRM;
590 g_error_flag := 2;
591 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
592 END; --edit_check_6
593
594
595 PROCEDURE edit_check_7 IS
596 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_7';
597
598 -- Cursor to fetch ending balance for accounts where
599 -- fund_balance_account_flag is 'Y'
600 CURSOR check7 IS
601 SELECT nvl(fft.amount,0) amount, fft.sgl_acct_number
602 FROM fv_facts_temp fft,
603 fv_facts_ussgl_accounts ffa
604 WHERE ffa.fund_balance_account_flag = 'Y'
605 AND fft.sgl_acct_number = ffa.ussgl_account
606 AND fft.treasury_symbol_id = g_treasury_symbol_id
607 AND fft.fct_int_record_category = 'REPORTED_NEW'
608 AND fft.fct_int_record_type = 'BLK_DTL'
609 AND fft.begin_end = 'E';
610
611 l_unexp_amount NUMBER;
612 l_end_balance NUMBER := 0;
613
614 BEGIN
615
616 init_vars;
617 v_edit_check_number := 7;
618
619 --Fetch preclosing ending balance for the treasury symbol
620 SELECT preclosing_unexpended_amt
621 INTO l_unexp_amount
622 FROM fv_treasury_symbols
623 WHERE treasury_symbol_id = g_treasury_symbol_id;
624
625 v_amount2 := l_unexp_amount;
629 -- since it needs to be printed only once
626 create_log_record(v_log_text);
627
628 -- reset v_amount --> l_unexp_amount to NULL
630 v_amount2 := NULL;
631
632 FOR check7_rec in check7
633 LOOP
634 /* Added space in from of account number to order the edit check 8 report information*/
635 v_sgl_acct_number := ' '||check7_rec.sgl_acct_number;
636 v_amount1 := check7_rec.amount;
637 create_log_record(v_log_text);
638
639 l_end_balance := l_end_balance + check7_rec.amount;
640
641 END LOOP;
642
643 IF (l_unexp_amount is NULL ) THEN
644 v_edit_check_status := 'Failed' ;
645 ELSIF (l_unexp_amount = l_end_balance) THEN
646 v_edit_check_status := 'Passed' ;
647 ELSE
648 v_edit_check_status := 'Failed' ;
649 g_error_flag := 2;
650 END IF;
651
652 create_status_record(v_edit_check_number, v_edit_check_status) ;
653
654 EXCEPTION WHEN OTHERS THEN
655 v_log_text := SQLERRM;
656 g_error_flag := 2;
657 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
658 END; -- edit_check_7
659
660 PROCEDURE edit_check_8 IS
661 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_8';
662
663
664 -- Cursor to fetch amounts for Obligations Incurred
665 -- This cursor fetches only if the account is End-Begin.
666
667 CURSOR check8_col1b_cur
668 (
669 p_treasury_symbol_id NUMBER
670 )
671 IS
672 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) obligations_incurred_s_amt
673 FROM fv_facts_temp fft,
674 fv_facts_ussgl_accounts ffa
675 WHERE ffa.obligations_incurred_flag = 'Y'
676 AND ffa.ussgl_account = fft.sgl_acct_number
677 AND fft.treasury_symbol_id = p_treasury_symbol_id
678 AND fft.fct_int_record_category = 'REPORTED_NEW'
679 AND fft.fct_int_record_type = 'BLK_DTL';
680 -- AND ffa.resource_status_be_flag IN ('S');
681
682
683 -- Cursor to fetch amounts for Spending from Collections and PYA
684 -- This cursor fetches only if the account is End-Begin.
685 CURSOR check8_col2b_cur
686 (
687 p_treasury_symbol_id NUMBER
688 )
689 IS
690 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) spndng_from_coll_and_pya_s_amt
691 FROM fv_facts_temp fft,
692 fv_facts_ussgl_accounts ffa
693 WHERE ffa.spndng_from_coll_and_pya_flag = 'Y'
694 AND ffa.ussgl_account = fft.sgl_acct_number
695 AND fft.treasury_symbol_id = p_treasury_symbol_id
696 AND fft.fct_int_record_category = 'REPORTED_NEW'
697 AND fft.fct_int_record_type = 'BLK_DTL';
698 -- AND ffa.total_resource_be_flag IN ('S');
699
700 -- Cursor to fetch amounts for Obligations as of 10/1
701 -- Column 3 always use beginning balance
702 CURSOR check8_col3_cur
703 (
704 p_treasury_symbol_id NUMBER
705 )
706 IS
707 SELECT SUM(NVL(amount,0)) obligations_as_of_10_1_amt
708 FROM fv_facts_temp fft,
709 fv_facts_ussgl_accounts ffa
710 WHERE ffa.obligations_as_of_10_1_flag = 'Y'
711 AND ffa.ussgl_account = fft.sgl_acct_number
712 AND fft.treasury_symbol_id = p_treasury_symbol_id
713 AND fft.fct_int_record_category = 'REPORTED_NEW'
714 AND fft.fct_int_record_type = 'BLK_DTL'
715 AND fft.begin_end = 'B';
716
717 -- Cursor to fetch amounts for Obligations Transferred and Obligations Period/End
718 -- Column 4 and 5 always use ending balance
719 CURSOR check8_col4_and_5_cur
720 (
721 p_treasury_symbol_id NUMBER
722 )
723 IS
724 SELECT NVL(SUM(DECODE(ffa.obligations_transferred_flag, 'Y', NVL(amount,0), 0)),0) obligations_transferred_amt,
725 NVL(SUM(DECODE(ffa.obligations_period_end_flag, 'Y', NVL(amount,0), 0)),0) obligations_period_end_amt
726 FROM fv_facts_temp fft,
727 fv_facts_ussgl_accounts ffa
728 WHERE (
729 ffa.obligations_transferred_flag = 'Y' OR
730 ffa.obligations_period_end_flag = 'Y'
731 )
732 AND ffa.ussgl_account = fft.sgl_acct_number
733 AND fft.treasury_symbol_id = p_treasury_symbol_id
734 AND fft.fct_int_record_category = 'REPORTED_NEW'
735 AND fft.fct_int_record_type = 'BLK_DTL'
736 AND fft.begin_end = 'E';
737
738 -- Cursor to fetch amounts
739 -- for Disbursements and Collections (only Ending balance type)
740 CURSOR check8_disb_colla_cur
741 (
742 p_treasury_symbol_id NUMBER
743 )
744 IS
745 SELECT NVL(SUM(DECODE(ffa.disbursements_flag, 'Y', NVL(amount,0), 0)),0) disbursements_amt,
746 NVL(SUM(DECODE(ffa.collections_flag, 'Y', NVL(amount,0), 0)),0) collections_amt
747 FROM fv_facts_temp fft,
748 fv_facts_ussgl_accounts ffa
749 WHERE (
750 ffa.disbursements_flag = 'Y' OR
751 ffa.collections_flag = 'Y'
752 )
753 AND ffa.ussgl_account = fft.sgl_acct_number
754 AND fft.treasury_symbol_id = p_treasury_symbol_id
755 AND fft.fct_int_record_category = 'REPORTED_NEW'
756 AND fft.fct_int_record_type = 'BLK_DTL'
757 AND fft.begin_end = 'E'
758 AND ffa.edck12_balance_type IN ('E' ,'S');-- by ks for bug bug 5328107
759
760 -- Cursor to fetch amounts
761 -- for Disbursements (only Ending - Beginning balance type)
762 CURSOR check8_disbb_cur
763 (
767 --SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) beg_disbursements_amt
764 p_treasury_symbol_id NUMBER
765 )
766 IS
768 -- above line commnted out by ks for bug bug 5328107
769 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0),0)), 0) beg_disbursements_amt
770 FROM fv_facts_temp fft,
771 fv_facts_ussgl_accounts ffa
772 WHERE ffa.disbursements_flag = 'Y'
773 AND ffa.ussgl_account = fft.sgl_acct_number
774 AND fft.treasury_symbol_id = p_treasury_symbol_id
775 AND fft.fct_int_record_category = 'REPORTED_NEW'
776 AND fft.fct_int_record_type = 'BLK_DTL'
777 AND ffa.edck12_balance_type = 'S';
778
779 -- Cursor to fetch amounts
780 -- for Collections (only Ending - Beginning balance type)
781 CURSOR check8_collb_cur
782 (
783 p_treasury_symbol_id NUMBER
784 )
785 IS
786 -- ks for bug bug 5328107
787 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), 0)), 0) beg_collections_amt
788 FROM fv_facts_temp fft,
789 fv_facts_ussgl_accounts ffa
790 WHERE ffa.collections_flag = 'Y'
791 AND ffa.ussgl_account = fft.sgl_acct_number
792 AND fft.treasury_symbol_id = p_treasury_symbol_id
793 AND fft.fct_int_record_category = 'REPORTED_NEW'
794 AND fft.fct_int_record_type = 'BLK_DTL'
795 AND ffa.edck12_balance_type = 'S';
796
797 l_obligations_incurred_amt NUMBER;
798 l_spndng_from_coll_and_pya_amt NUMBER;
799 l_obligations_as_of_10_1_amt NUMBER;
800 l_obligations_transferred_amt NUMBER;
801 l_obligations_period_end_amt NUMBER;
802 l_disbursements_amt NUMBER;
803 l_collections_amt NUMBER;
804 l_beg_disbursements_amt NUMBER;
805 l_beg_collections_amt NUMBER;
806
807 BEGIN
808 init_vars;
809
810 l_obligations_incurred_amt := 0;
811 l_spndng_from_coll_and_pya_amt := 0;
812 l_obligations_as_of_10_1_amt := 0;
813 l_obligations_transferred_amt := 0;
814 l_obligations_period_end_amt := 0;
815 l_disbursements_amt := 0;
816 l_collections_amt := 0;
817 l_beg_disbursements_amt := 0;
818 l_beg_collections_amt := 0;
819
820 /*FOR check8_col1a_rec IN check8_col1a_cur (g_treasury_symbol_id) LOOP
821 l_obligations_incurred_amt := NVL(l_obligations_incurred_amt, 0) + NVL(check8_col1a_rec.obligations_incurred_be_amt, 0);
822 END LOOP;*/
823
824 FOR check8_col1b_rec IN check8_col1b_cur (g_treasury_symbol_id) LOOP
825 l_obligations_incurred_amt := NVL(l_obligations_incurred_amt, 0) + NVL(check8_col1b_rec.obligations_incurred_s_amt, 0);
826 END LOOP;
827
828 /* FOR check8_col2a_rec IN check8_col2a_cur (g_treasury_symbol_id) LOOP
829 l_spndng_from_coll_and_pya_amt := NVL(l_spndng_from_coll_and_pya_amt, 0) + NVL(check8_col2a_rec.spndng_frm_coll_and_pya_be_amt, 0);
830 END LOOP;*/
831
832 FOR check8_col2b_rec IN check8_col2b_cur (g_treasury_symbol_id) LOOP
833 l_spndng_from_coll_and_pya_amt := NVL(l_spndng_from_coll_and_pya_amt, 0) + NVL(check8_col2b_rec.spndng_from_coll_and_pya_s_amt, 0);
834 END LOOP;
835
836 FOR check8_col3_rec IN check8_col3_cur (g_treasury_symbol_id) LOOP
837 l_obligations_as_of_10_1_amt := NVL(l_obligations_as_of_10_1_amt, 0) + NVL(check8_col3_rec.obligations_as_of_10_1_amt, 0);
838 END LOOP;
839
840 FOR check8_col4_and_5_rec IN check8_col4_and_5_cur (g_treasury_symbol_id) LOOP
841 l_obligations_transferred_amt := NVL(l_obligations_transferred_amt, 0) + NVL(check8_col4_and_5_rec.obligations_transferred_amt, 0);
842 l_obligations_period_end_amt := NVL(l_obligations_period_end_amt, 0) + NVL(check8_col4_and_5_rec.obligations_period_end_amt, 0);
843 END LOOP;
844
845
846 FOR check8_disb_colla_rec IN check8_disb_colla_cur (g_treasury_symbol_id) LOOP
847 l_disbursements_amt := NVL(l_disbursements_amt, 0) + NVL(check8_disb_colla_rec.disbursements_amt, 0);
848 l_collections_amt := NVL(l_collections_amt, 0) + NVL(check8_disb_colla_rec.collections_amt, 0);
849 END LOOP;
850
851 fnd_file.put_line(fnd_file.log , 'Ending disbursement ' || l_disbursements_amt);
852 fnd_file.put_line(fnd_file.log , 'Ending collection ' || l_collections_amt);
853
854 FOR check8_disbb_rec IN check8_disbb_cur (g_treasury_symbol_id) LOOP
855 l_beg_disbursements_amt := NVL(check8_disbb_rec.beg_disbursements_amt, 0);
856 END LOOP;
857
858 FOR check8_collb_rec IN check8_collb_cur (g_treasury_symbol_id) LOOP
859 l_beg_collections_amt := NVL(check8_collb_rec.beg_collections_amt, 0);
860 END LOOP;
861
862 --- since we want to get only the acvity or ending_balances , we need to
863 -- substract any begining disbursement or collection from ending_balaces;
864
865 fnd_file.put_line(fnd_file.log , 'beg disbursement ' || l_beg_disbursements_amt);
866 fnd_file.put_line(fnd_file.log , 'beg collection ' || l_beg_collections_amt);
867 l_disbursements_amt := NVL(l_disbursements_amt, 0) + NVL(l_beg_disbursements_amt, 0);
868 l_collections_amt := NVL(l_collections_amt, 0) + NVL(l_beg_collections_amt, 0);
869
870 l_obligations_incurred_amt := -1*l_obligations_incurred_amt; --Cr balance report as + and Dr balance report as -
871 l_obligations_as_of_10_1_amt := -1*l_obligations_as_of_10_1_amt; --Cr balance report as + and Dr balance report as -
875 IF ((NVL(l_obligations_incurred_amt, 0) -
872 l_obligations_transferred_amt := -1*l_obligations_transferred_amt; --Cr balance report as + and Dr balance report as -
873 l_obligations_period_end_amt := -1*l_obligations_period_end_amt; --Cr balance report as + and Dr balance report as -
874
876 NVL(l_spndng_from_coll_and_pya_amt, 0) +
877 NVL(l_obligations_as_of_10_1_amt, 0) +
878 NVL(l_obligations_transferred_amt, 0) -
879 NVL(l_obligations_period_end_amt, 0))
880 =
881 (-1*(NVL(l_disbursements_amt, 0) +
882 NVL(l_collections_amt, 0)))) THEN
883 v_edit_check_status := 'Passed' ;
884 ELSE
885 v_edit_check_status := 'Failed' ;
886 g_error_flag := 2;
887 END IF;
888
889 v_edit_check_number := 8;
890
891 v_sgl_acct_number := '1Obligations Incurred';
892 v_amount := l_obligations_incurred_amt;
893 v_amount1 := l_obligations_incurred_amt;
894 create_log_record(v_log_text);
895
896 v_sgl_acct_number := '2- Spending from Collections and PYA';
897 v_amount := l_spndng_from_coll_and_pya_amt;
898 v_amount1 := -1*l_spndng_from_coll_and_pya_amt;
899 create_log_record(v_log_text);
900
901 v_sgl_acct_number := '3+ Obligations as of 10/1';
902 v_amount := l_obligations_as_of_10_1_amt;
903 v_amount1 := l_obligations_as_of_10_1_amt;
904 create_log_record(v_log_text);
905
906 v_sgl_acct_number := '4+/- Obligations Transferred';
907 v_amount := l_obligations_transferred_amt;
908 v_amount1 := l_obligations_transferred_amt;
909 create_log_record(v_log_text);
910
911 v_sgl_acct_number := '5- Obligations Period End';
912 v_amount := l_obligations_period_end_amt;
913 v_amount1 := -1*l_obligations_period_end_amt;
914 create_log_record(v_log_text);
915
916 v_edit_check_number := 9;
917 v_sgl_acct_number := '6Disbursements (+)';
918 v_amount := l_disbursements_amt;
919 v_amount1 := -1*l_disbursements_amt;
920 create_log_record(v_log_text);
921
922 v_sgl_acct_number := '7Collections (-)';
923 v_amount := l_collections_amt;
924 v_amount1 := -1*l_collections_amt;
925 create_log_record(v_log_text);
926
927 v_edit_check_number := 8;
928 create_status_record(v_edit_check_number, v_edit_check_status);
929 -- Inserting dummy record for edit check 9
930 v_edit_check_number := 9;
931 create_status_record(v_edit_check_number, v_edit_check_status);
932
933 EXCEPTION
934 WHEN OTHERS THEN
935 v_log_text := SQLERRM;
936 g_error_flag := 2;
937 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
938 END; --edit_check_8
939
940 -- Added the foll to fix bug 1974485
941 PROCEDURE edit_check_10 IS
942 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_10';
943
944 l_total_amount NUMBER := 0;
945
946 CURSOR check10 IS
947 SELECT fft.sgl_acct_number,
948 SUM(NVL(fft.amount,0)) amount
949 FROM fv_facts_temp fft,
950 fv_facts_ussgl_accounts ffacc
951 WHERE ffacc.ussgl_account = fft.sgl_acct_number
952 AND fft.treasury_symbol_id = g_treasury_symbol_id
953 AND fft.fct_int_record_category = 'REPORTED_NEW'
954 AND fft.fct_int_record_type = 'BLK_DTL'
955 AND fft.begin_end = 'E'
956 AND ffacc.cancelled_flag = 'Y'
957 GROUP BY fft.sgl_acct_number;
958
959 BEGIN
960 init_vars;
961 v_edit_check_number := 10;
962
963 FOR check10_rec IN check10
964 LOOP
965 /* Added space in from of account number to order the edit check 8 report information*/
966 v_sgl_acct_number := ' '||check10_rec.sgl_acct_number;
967 v_amount := check10_rec.amount;
968 create_log_record(v_log_text);
969 l_total_amount := l_total_amount + check10_rec.amount;
970
971 END LOOP;
972
973 IF l_total_amount = 0 THEN
974 v_edit_check_status := 'Passed' ;
975 ELSE
976 v_edit_check_status := 'Failed' ;
977 END IF;
978
979 create_status_record(v_edit_check_number, v_edit_check_status) ;
980
981 EXCEPTION WHEN OTHERS THEN
982 v_log_text := SQLERRM;
983 g_error_flag := 2;
984 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
985 END; --edit_check_10
986
987 PROCEDURE edit_check_11 IS
988 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_11';
989
990 CURSOR neg_rec_pay_c IS
991 SELECT ffacc.ussgl_account,
992 NVL(sum(fft.amount),0),
993 ffacc.ye_neg_receivables_flag,
994 ffacc.natural_balance
995 FROM fv_facts_temp fft,
996 fv_facts_ussgl_accounts ffacc
997 WHERE (ffacc.ye_neg_receivables_flag = 'Y'
998 OR ffacc.ye_neg_payables_flag = 'Y')
999 AND ffacc.ussgl_account = fft.sgl_acct_number
1000 AND fft.treasury_symbol_id = g_treasury_symbol_id
1001 AND fft.fct_int_record_category = 'REPORTED_NEW'
1002 AND fft.fct_int_record_type = 'BLK_DTL'
1003 AND fft.begin_end = 'E'
1004 GROUP BY ffacc.ussgl_account, ffacc.ye_neg_receivables_flag, ffacc.natural_balance;
1005
1006 CURSOR general_acc_c IS
1007 SELECT ffacc.ussgl_account, nvl(sum(fft.amount),0),
1008 ffacc.natural_balance
1009 FROM fv_facts_temp fft,
1013 AND fft.treasury_symbol_id = g_treasury_symbol_id
1010 fv_facts_ussgl_accounts ffacc
1011 WHERE ffacc.ye_general_flag = 'Y'
1012 AND ffacc.ussgl_account = fft.sgl_acct_number
1014 AND fft.fct_int_record_category = 'REPORTED_NEW'
1015 AND fft.fct_int_record_type = 'BLK_DTL'
1016 AND fft.begin_end = 'E'
1017 GROUP BY ffacc.ussgl_account, ffacc.natural_balance;
1018
1019 l_ussgl_account varchar2(30);
1020 l_amount number;
1021 l_acc_type varchar2(25);
1022 l_count number;
1023 l_neg_receivables_flag varchar2(1);
1024 l_natural_balance FV_FACTS_USSGL_ACCOUNTS.NATURAL_BALANCE%TYPE;
1025 l_dc_ind varchar2(1);
1026
1027 BEGIN
1028
1029 init_vars;
1030
1031 l_count := 0;
1032 v_edit_check_number := 11;
1033
1034 --Fetch accounts with negative receivable and negative payables balances
1035 OPEN neg_rec_pay_c;
1036
1037 LOOP
1038 FETCH neg_rec_pay_c
1039 INTO l_ussgl_account,
1040 l_amount,
1041 l_neg_receivables_flag,
1042 l_natural_balance;
1043
1044 EXIT WHEN neg_rec_pay_c%NOTFOUND OR neg_rec_pay_c%NOTFOUND IS NULL;
1045
1046 SELECT DECODE (l_neg_receivables_flag,'Y','NR','NP')
1047 INTO l_acc_type
1048 FROM DUAL;
1049
1050 /* Added space in from of account number to order the edit check 8 report information*/
1051 v_sgl_acct_number := ' '||l_ussgl_account;
1052 v_amount := l_amount;
1053 v_dummy_var := l_acc_type;
1054 -- create_log_record(v_log_text);
1055
1056 IF (l_amount > 0) THEN
1057 l_dc_ind := 'D';
1058 ELSE
1059 l_dc_ind := 'C';
1060 END IF;
1061
1062 IF (l_amount <> 0 AND l_dc_ind <> l_natural_balance) THEN
1063
1064 create_log_record(v_log_text);
1065
1066 l_count := l_count +1;
1067
1068 --LGOEL: Update temp table if Edit check 11 failed
1069 update fv_facts_temp
1070 set document_number = 'Y'
1071 where sgl_acct_number = l_ussgl_account
1072 and treasury_symbol_id = g_treasury_symbol_id
1073 and fct_int_record_category = 'REPORTED_NEW'
1074 and fct_int_record_type = 'BLK_DTL';
1075
1076 -- Enable the foot note flag for this failed
1077 -- edit check
1078 UPDATE fv_facts_submission
1079 SET foot_note_flag = 'Y'
1080 WHERE treasury_symbol_id = g_treasury_symbol_id;
1081
1082 END IF;
1083
1084 END LOOP;
1085
1086 CLOSE neg_rec_pay_c;
1087
1088 -- Initialize variables
1089 l_ussgl_account := NULL;
1090 l_amount := 0;
1091 l_natural_balance := NULL;
1092
1093 --Fetch General accounts which have a balance
1094 OPEN general_acc_c;
1095
1096 LOOP
1097
1098 FETCH general_acc_c
1099 INTO l_ussgl_account,
1100 l_amount,
1101 l_natural_balance;
1102
1103 EXIT WHEN general_acc_c%NOTFOUND OR general_acc_c%NOTFOUND IS NULL;
1104
1105 /* Added space in from of account number to order the edit check 8 report information*/
1106 v_sgl_acct_number := ' '||l_ussgl_account;
1107 v_amount := l_amount;
1108 v_dummy_var := 'GL';
1109 -- create_log_record(v_log_text);
1110
1111 IF (l_amount > 0) THEN
1112 l_dc_ind := 'D';
1113 ELSE
1114 l_dc_ind := 'C';
1115 END IF;
1116
1117 IF (l_amount <> 0 ) THEN
1118
1119 create_log_record(v_log_text);
1120
1121 l_count := l_count +1;
1122
1123 --LGOEL: Update temp table if Edit check 11 failed
1124 update fv_facts_temp
1125 set document_number = 'Y'
1126 where sgl_acct_number = l_ussgl_account
1127 and treasury_symbol_id = g_treasury_symbol_id
1128 and fct_int_record_category = 'REPORTED_NEW'
1129 and fct_int_record_type = 'BLK_DTL';
1130
1131 -- Enable the foot note flag for this failed
1132 -- edit check
1133 UPDATE fv_facts_submission
1134 SET foot_note_flag = 'Y'
1135 WHERE treasury_symbol_id = g_treasury_symbol_id;
1136
1137 END IF;
1138 END LOOP;
1139 CLOSE general_acc_c;
1140
1141 IF (l_count = 0) THEN
1142 v_edit_check_status := 'Passed' ;
1143
1144 -- Disable the foot note flag for this passed
1145 -- edit check in case it has failed earlier
1146 UPDATE fv_facts_submission
1147 SET foot_note_flag = 'N'
1148 WHERE treasury_symbol_id = g_treasury_symbol_id;
1149
1150 ELSE
1151 v_edit_check_status := 'Failed' ;
1152 IF (g_error_flag = 0) THEN
1153 g_error_flag := 1;
1154 END IF;
1155 END IF;
1156
1157 create_status_record(v_edit_check_number, v_edit_check_status) ;
1158
1159 EXCEPTION WHEN OTHERS THEN
1160 v_log_text := SQLERRM;
1161 IF (g_error_flag = 0) THEN
1162 g_error_flag := 1;
1163 END IF;
1164 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1165 END; --edit_check_11
1166
1167
1168 PROCEDURE edit_check_12(p_facts_run_quarter number) IS
1169 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_12';
1170
1171 -- Cursor to fetch amounts
1172 -- for disbursement and collection accounts
1176 ffa.collections_flag,
1173 CURSOR check12 IS
1174 SELECT fft.sgl_acct_number,
1175 ffa.disbursements_flag,
1177 ffa.edck12_balance_type,
1178 sum(nvl(amount,0)) amount
1179 FROM fv_facts_temp fft,
1180 fv_facts_ussgl_accounts ffa
1181 WHERE (ffa.disbursements_flag = 'Y' or ffa.collections_flag = 'Y')
1182 AND ffa.ussgl_account = fft.sgl_acct_number
1183 AND fft.treasury_symbol_id = g_treasury_symbol_id
1184 AND fft.fct_int_record_category = 'REPORTED_NEW'
1185 AND fft.fct_int_record_type = 'BLK_DTL'
1186 AND fft.begin_end = 'E'
1187 group by
1188 fft.sgl_acct_number,
1189 ffa.disbursements_flag,
1190 ffa.collections_flag,
1191 ffa.edck12_balance_type;
1192 l_disbursements number := 0;
1193 l_collections number := 0;
1194 l_net_outlays number := 0;
1195 l_224_outlays number := 0;
1196 v_begin_amount number := 0;
1197
1198 BEGIN
1199 init_vars;
1200 v_edit_check_number := 12;
1201
1202 --Fetch 224 Outlays
1203 select decode(p_facts_run_quarter,1,sf224_qtr1_outlay,
1204 2,sf224_qtr2_outlay,3,sf224_qtr3_outlay,sf224_qtr4_outlay)
1205 into l_224_outlays
1206 from fv_treasury_symbols
1207 where treasury_symbol_id = g_treasury_symbol_id;
1208
1209 IF (l_224_outlays is NULL) THEN
1210
1211 v_amount := NULL;
1212 v_amount1 := NULL;
1213 v_amount2 := NULL;
1214
1215 create_log_record(v_log_text);
1216 v_edit_check_status := 'Not Applicable' ;
1217
1218 create_status_record(v_edit_check_number, v_edit_check_status) ;
1219
1220 IF (g_error_flag = 0) then
1221 g_error_flag := 1;
1222 END IF;
1223
1224 ELSE
1225
1226 FOR check12_rec IN check12
1227 LOOP
1228 /* Added space in from of account number to order the edit check 8 report information*/
1229 v_sgl_acct_number := ' '||check12_rec.sgl_acct_number;
1230 v_begin_amount := 0;
1231
1232 IF check12_rec.edck12_balance_type = 'S' THEN
1233 SELECT sum(nvl(fft.amount,0))
1234 INTO v_begin_amount
1235 FROM fv_facts_temp fft
1236 WHERE fft.sgl_acct_number = check12_rec.sgl_acct_number
1237 AND fft.treasury_symbol_id = g_treasury_symbol_id
1238 AND fft.fct_int_record_category = 'REPORTED_NEW'
1239 AND fft.fct_int_record_type = 'BLK_DTL'
1240 AND fft.begin_end = 'B';
1241 END IF;
1242
1243 IF check12_rec.collections_flag = 'Y' THEN
1244
1245 v_amount1 := NVL(check12_rec.amount,0) - NVL(v_begin_amount,0);
1246 v_amount2 := NULL;
1247
1248 create_log_record(v_log_text);
1249 l_collections := l_collections + v_amount1;
1250 ELSIF
1251 check12_rec.disbursements_flag = 'Y' THEN
1252 v_amount2 := NVL(check12_rec.amount,0) - NVL(v_begin_amount,0);
1253 v_amount1 := NULL;
1254
1255 create_log_record(v_log_text);
1256 l_disbursements := NVL(l_disbursements,0) + NVL(v_amount2,0);
1257 END IF;
1258
1259 END LOOP;
1260
1261 l_net_outlays := -1*(NVL(l_disbursements,0) + NVL(l_collections,0));
1262
1263 v_edit_check_number := 13;
1264
1265 /* Added space in from of account number to order the edit check 8 report information*/
1266 v_sgl_acct_number := ' 1. Net Outlays';
1267 v_amount := l_net_outlays;
1268 create_log_record(v_log_text);
1269
1270 /* Added space in from of account number to order the edit check 8 report information*/
1271 v_sgl_acct_number := ' 2. 224 Outlays';
1272 v_amount := l_224_outlays;
1273 create_log_record(v_log_text);
1274
1275 /* Added space in from of account number to order the edit check 8 report information*/
1276 v_sgl_acct_number := ' Difference (1-2)';
1277 v_amount := (l_net_outlays - l_224_outlays);
1278 create_log_record(v_log_text);
1279
1280 IF (l_net_outlays = l_224_outlays) THEN
1281 v_edit_check_status := 'Passed' ;
1282 ELSE
1283 v_edit_check_status := 'Failed' ;
1284 g_error_flag := 2;
1285 END IF;
1286
1287 v_edit_check_number := 12;
1288 create_status_record(v_edit_check_number, v_edit_check_status) ;
1289 -- Inserting dummy record for edit check 12 Net outlays printing
1290 v_edit_check_number := 13;
1291 create_status_record(v_edit_check_number, v_edit_check_status) ;
1292 END IF;
1293
1294 EXCEPTION WHEN OTHERS THEN
1295 v_log_text := SQLERRM;
1296 g_error_flag := 2;
1297 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1298
1299 END; --edit_check_12
1300
1301
1302 -- This is the main procedure which calls all the edit check procedures
1303
1304 procedure perform_edit_checks (errbuf OUT NOCOPY varchar2,
1305 retcode OUT NOCOPY number,
1306 p_treasury_symbol_id IN number,
1307 p_facts_run_quarter IN number,
1308 p_rep_fiscal_yr IN NUMBER,
1309 p_ledger_id IN NUMBER)
1310 is
1311 l_module_name VARCHAR2(200) := g_module_name || 'perform_edit_checks';
1312 -- Added to fix 1974485
1313 l_cancel_date NUMBER(4);
1314
1315 begin
1316
1317 fnd_file.put_line(FND_file.LOG,'Running 7/24 debug version');
1318 g_error_flag := 0;
1319 g_treasury_symbol_id := p_treasury_symbol_id;
1320
1324 WHERE treasury_symbol_id = g_treasury_symbol_id;
1321 SELECT to_number(to_char(cancellation_date,'YYYY'))
1322 INTO l_cancel_date
1323 FROM fv_treasury_symbols
1325
1326 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1327 v_log_text := 'Edit Check process start...' ;
1328 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',v_log_text);
1329 v_log_text := ' ' ;
1330 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',v_log_text);
1331 END IF;
1332
1333 edit_check_1;
1334 edit_check_2;
1335 edit_check_3;
1336 edit_check_8;
1337 if (p_facts_run_quarter = 4) then
1338 edit_check_4;
1339 edit_check_5;
1340 edit_check_6 (p_ledger_id);
1341 edit_check_7;
1342
1343 -- Added to fix 1974485
1344
1345 IF l_cancel_date = p_rep_fiscal_yr
1346 THEN edit_check_10;
1347 ELSE
1348 init_vars;
1349
1350 v_edit_check_number := 10;
1351 v_edit_check_status := 'Not Applicable' ;
1352
1353 create_status_record(v_edit_check_number, v_edit_check_status) ;
1354 -- create_log_record(v_log_text);
1355
1356 END IF;
1357
1358 edit_check_11;
1359
1360 else
1361
1362 -- Changed log text for bug 2053780
1363
1364 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1365 v_log_text := 'Edit Checks 4,5,6,7,10 and 11 are not needed' ;
1366 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message2',v_log_text);
1367 END IF;
1368
1369 v_log_text := ' ';
1370 v_edit_check_status := 'Not Needed';
1371
1372 v_edit_check_number := 4;
1373 create_status_record(v_edit_check_number, v_edit_check_status) ;
1374
1375 v_edit_check_number := 5;
1376 create_status_record(v_edit_check_number, v_edit_check_status) ;
1377
1378 v_edit_check_number := 6;
1379 create_status_record(v_edit_check_number, v_edit_check_status) ;
1380
1381 v_edit_check_number := 7;
1382 create_status_record(v_edit_check_number, v_edit_check_status) ;
1383
1384 v_edit_check_number := 10;
1385 create_status_record(v_edit_check_number, v_edit_check_status) ;
1386
1387 v_edit_check_number := 11;
1388 create_status_record(v_edit_check_number, v_edit_check_status) ;
1389
1390 end if;
1391
1392 edit_check_12(p_facts_run_quarter);
1393
1394 retcode := g_error_flag;
1395 if (retcode = 1) then
1396 errbuf := 'Soft Edit Check Failed.' ;
1397 v_log_text := 'Soft Edit Check Failed.' ;
1398 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',errbuf);
1399
1400 elsif (retcode = 2) then
1401 errbuf := 'Hard Edit Check Failed';
1402 v_log_text := 'Hard Edit Check Failed.' ;
1403 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error2',errbuf);
1404
1405 else
1406 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1407 v_log_text := 'Edit Check Passed.' ;
1408 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message4',v_log_text);
1409 END IF;
1410 end if;
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 v_log_text := SQLERRM;
1415 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1416 RAISE;
1417 end; --perform_edit_checks
1418
1419 -------------------------------------------------------------------------
1420 -- Procedure CREATE_STATUS_RECORD
1421 -------------------------------------------------------------------------
1422 -- This Procedure inserts the status information into
1423 -- fv_facts_edit_check_status
1424 -------------------------------------------------------------------------
1425
1426 PROCEDURE create_status_record (p_edit_check_number NUMBER,
1427 p_edit_check_status VARCHAR2)
1428 IS
1429 l_module_name VARCHAR2(200) := g_module_name || 'create_status_record';
1430
1431 BEGIN
1432 INSERT INTO fv_facts_edit_check_status
1433 (treasury_symbol_id,
1434 edit_check_number,
1435 edit_check_status)
1436 VALUES (g_treasury_symbol_id,
1437 p_edit_check_number,
1438 p_edit_check_status) ;
1439 EXCEPTION
1440 when others then
1441 v_log_text := SQLERRM;
1442 g_error_flag := sqlcode ;
1443 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1444 return;
1445 END; -- create_status_record
1446 -------------------------------------------------------------------------
1447 -- Procedure CREATE_LOG_RECORD
1448 -------------------------------------------------------------------------
1449 -- This Procedure inserts the log information into the FACTS temp
1450 -- table under the record category 'EDIT_CHECK_LOG_INFO'
1451 -------------------------------------------------------------------------
1452 Procedure CREATE_LOG_RECORD (text varchar2)
1453 is
1454 l_module_name VARCHAR2(200) := g_module_name || 'CREATE_LOG_RECORD';
1455 Begin
1456 v_log_counter := v_log_counter + 1 ;
1457
1458 Insert into FV_FACTS_TEMP
1459 (FCT_INT_RECORD_CATEGORY ,
1460 TREASURY_SYMBOL_ID ,
1461 TBAL_ACCT_NUM ,
1462 FACTS_REPORT_INFO ,
1463 edit_check_number ,
1464 amount ,
1465 amount1 ,
1466 amount2 ,
1467 sgl_acct_number ,
1468 budget_function )
1469 Values
1470 ('FACTS2_EDIT_CHECK_LOG' ,
1471 g_treasury_symbol_id ,
1472 v_log_counter ,
1473 text ,
1474 v_edit_check_number ,
1475 v_amount ,
1476 v_amount1 ,
1477 v_amount2 ,
1478 v_sgl_acct_number ,
1479 v_dummy_var ) ;
1480 EXCEPTION
1481 When Others Then
1482 v_log_text := SQLERRM;
1483 g_error_flag := sqlcode ;
1484 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1485 return;
1486 END CREATE_LOG_RECORD ;
1487
1488 end fv_facts_edit_check; -- Package body