[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_EDIT_CHECK
Source
1 package body fv_facts_edit_check AS
2 --$Header: FVFCCHKB.pls 120.51 2011/11/09 08:54:21 amaddula 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;
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_closing_grp fv_facts_temp.closing_grp%TYPE;
21 v_dummy_var VARCHAR2(3);
22 v_row_count NUMBER := 0;
23 g_ledger_id NUMBER(15);
24 g_period_num NUMBER(15);
25 g_period_year NUMBER(15);
26 v_beg_bal_sggl_acc fv_facts_temp.SGL_BEG_BAL_ACCT_NUM%type;
27 v_transaction_partner fv_facts_temp.transaction_partner%type;
28 v_aid fv_facts_temp.TRANSFER_DEPT_ID%type;
29 v_main fv_facts_temp.TRANSFER_MAIN_ACCT%type;
30 v_non_fed_exc_flag fv_facts_temp.non_fed_exc_flag%type;
31
32 PROCEDURE populate_bal_ret_tbl ;
33 PROCEDURE Create_log_record(text varchar2) ;
34
35 PROCEDURE create_status_record(p_edit_check_number number,
36 p_edit_check_status varchar2) ;
37
38 -- Procedure to initialize variables
39 PROCEDURE init_vars IS
40 l_module_name VARCHAR2(200) := g_module_name || 'init_vars';
41
42 BEGIN
43 v_edit_check_number := NULL;
44 v_edit_check_status := NULL;
45 v_amount := 0 ;
46 v_amount1 := 0 ;
47 v_amount2 := 0 ;
48 v_sgl_acct_number := NULL;
49 v_closing_grp := NULL;
50 v_log_text := ' ' ;
51 v_dummy_var := NULL;
52 v_row_count := 0 ;
53 v_beg_bal_sggl_acc := NULL;
54 EXCEPTION
55 WHEN OTHERS THEN
56 v_log_text := SQLERRM;
57 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
58 RAISE;
59
60 END init_vars;
61
62 PROCEDURE edit_check_1 IS
63 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_1';
64
65 l_total_credit NUMBER := 0;
66 l_total_debit NUMBER := 0;
67
68 -- Cursor to fetch Credit/Debit Ending balance
69 -- from FV_FACTS_TEMP for budgetary accounts
70 CURSOR check1 IS
71 SELECT nvl(amount,0) amount, debit_credit,
72 sgl_acct_number
73 FROM fv_facts_temp
74 WHERE treasury_symbol_id = g_treasury_symbol_id
75 AND fct_int_record_category = 'REPORTED_NEW'
76 AND fct_int_record_type = 'BLK_DTL'
77 AND sgl_acct_number like '4%'
78 AND begin_end = 'E'
79 AND amount <> 0
80 ORDER BY sgl_acct_number;
81
82 BEGIN
83 init_vars;
84 v_edit_check_number := 1;
85
86 FOR check1_rec IN check1
87 LOOP
88 IF check1_rec.debit_credit = 'C' THEN
89 l_total_credit := l_total_credit + check1_rec.amount;
90 ELSE
91 l_total_debit := l_total_debit + check1_rec.amount;
92 END IF;
93
94 /* Added space in from of account number to order the edit check 8 report information*/
95 v_sgl_acct_number := ' '||check1_rec.sgl_acct_number;
96 v_amount := check1_rec.amount;
97 create_log_record(v_log_text);
98
99 END LOOP;
100
101 IF -1*(l_total_credit) = l_total_debit THEN
102 v_edit_check_status := 'Passed';
103 ELSE
104 g_error_flag := 2;
105 v_edit_check_status := 'Failed';
106 END IF;
107
108 create_status_record(v_edit_check_number, v_edit_check_status) ;
109
110 EXCEPTION WHEN OTHERS THEN
111 v_log_text := SQLERRM;
112 g_error_flag := 2;
113 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
114 END; --edit_check_1
115
116
117 PROCEDURE edit_check_2 IS
118 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_2';
119
120 -- Cursor to fetch SGL account and associated attributes from
121 -- FV_FACTS_USSGL_ACCOUNTS for all budgetary accounts
122 -- existing in FV_FACTS_TEMP
123 CURSOR fv_facts_ussgl_accounts_c IS
124 SELECT ffa.ussgl_account,
125 ffa.total_resource_be_flag, ffa.total_resource_dc_flag,
126 ffa.resource_status_be_flag, ffa.resource_status_dc_flag
127 FROM fv_facts_ussgl_accounts ffa
128 WHERE ffa.ussgl_account like '4%'
129 AND EXISTS
130 (SELECT 'x'
131 FROM fv_facts_temp fft
132 WHERE fft.treasury_symbol_id = g_treasury_symbol_id
133 AND fft.sgl_acct_number = ffa.ussgl_account);
134
135 l_ussgl_account varchar2(30);
136 l_total_resource_be_flag varchar2(1);
137 l_total_resource_dc_flag varchar2(1);
138 l_resource_status_be_flag varchar2(1);
139 l_resource_status_dc_flag varchar2(1);
140 l_begin_bal number;
141 l_begin_bal_dc_ind varchar2(1);
142 l_end_bal number;
143 l_end_bal_dc_ind varchar2(1);
144 l_balance number;
145 l_dc_ind varchar2(1);
146 l_to_total number := 0;
147 l_st_total number := 0;
148
149 l_to_amount NUMBER := 0;
150 l_st_amount NUMBER := 0;
151
152 BEGIN
153 init_vars;
154 v_edit_check_number := 2;
155
156 OPEN fv_facts_ussgl_accounts_c;
157
158 LOOP
159
160 FETCH fv_facts_ussgl_accounts_c
161 INTO l_ussgl_account,
162 l_total_resource_be_flag,
163 l_total_resource_dc_flag,
164 l_resource_status_be_flag,
165 l_resource_status_dc_flag;
166
167 EXIT WHEN fv_facts_ussgl_accounts_c%NOTFOUND
168 OR fv_facts_ussgl_accounts_c%NOTFOUND IS NULL;
169
170 -- Fetch beginning balance and set debit_credit
171 -- indicator for the SGL account
172
173 BEGIN
174
175 SELECT nvl(sum(amount),0)
176 INTO l_begin_bal
177 FROM fv_facts_temp
178 WHERE treasury_symbol_id = g_treasury_symbol_id
179 AND fct_int_record_category = 'REPORTED_NEW'
180 AND fct_int_record_type = 'BLK_DTL'
181 AND sgl_acct_number = l_ussgl_account
182 AND begin_end = 'B';
183
184 IF (l_begin_bal > 0) THEN
185 l_begin_bal_dc_ind := 'D';
186 ELSE
187 l_begin_bal_dc_ind := 'C';
188 END IF;
189
190 EXCEPTION WHEN NO_DATA_FOUND THEN
191 l_begin_bal := 0;
192
193 END;
194
195 -- Fetch Ending Balance and set debit_credit
196 -- indicator for the SGL account
197
198 BEGIN
199
200 SELECT nvl(sum(amount),0)
201 INTO l_end_bal
202 FROM fv_facts_temp
203 WHERE treasury_symbol_id = g_treasury_symbol_id
204 AND fct_int_record_category = 'REPORTED_NEW'
205 AND fct_int_record_type = 'BLK_DTL'
206 AND sgl_acct_number = l_ussgl_account
207 AND begin_end = 'E';
208
209 IF (l_end_bal > 0) THEN
210 l_end_bal_dc_ind := 'D';
211 ELSE
212 l_end_bal_dc_ind := 'C';
213 END IF;
214
215 EXCEPTION WHEN NO_DATA_FOUND THEN
216 l_end_bal := 0;
217
218 END;
219
220 v_amount1 := 0;
221 v_amount2 := 0;
222
223 IF (l_total_resource_be_flag = 'E') THEN
224
225 v_amount1 := l_end_bal;
226
227 IF (l_total_resource_dc_flag = 'D' and l_end_bal_dc_ind = 'D') THEN
228 l_to_total := l_to_total + l_end_bal;
229 ELSIF (l_total_resource_dc_flag = 'C' and l_end_bal_dc_ind = 'C') THEN
230 l_to_total := l_to_total + l_end_bal;
231 ELSIF (l_total_resource_dc_flag = 'E') THEN
232 l_to_total := l_to_total + l_end_bal;
233 END IF;
234 ELSIF (l_total_resource_be_flag = 'B' ) THEN
235
236 v_amount1 := l_begin_bal;
237
238 IF (l_total_resource_dc_flag = 'D' and l_begin_bal_dc_ind = 'D') THEN
239 l_to_total := l_to_total + l_begin_bal;
240 ELSIF (l_total_resource_dc_flag = 'C' and l_begin_bal_dc_ind = 'C') THEN
241 l_to_total := l_to_total + l_begin_bal;
242 ELSIF (l_total_resource_dc_flag = 'E') THEN
243 l_to_total := l_to_total + l_begin_bal;
244 END IF;
245 END IF;
246
247 IF (l_resource_status_be_flag = 'E') THEN
248
249 v_amount2 := l_end_bal;
250
251 IF (l_resource_status_dc_flag = 'D' and l_end_bal_dc_ind = 'D') THEN
252 l_st_total := l_st_total + l_end_bal;
253 ELSIF (l_resource_status_dc_flag = 'C' and l_end_bal_dc_ind = 'C') THEN
254 l_st_total := l_st_total + l_end_bal;
255 ELSIF (l_resource_status_dc_flag = 'E') THEN
256 l_st_total := l_st_total + l_end_bal;
257 END IF;
258 ELSIF (l_resource_status_be_flag = 'B' ) THEN
259
260 v_amount2 := l_begin_bal;
261
262 IF (l_resource_status_dc_flag = 'D' and l_begin_bal_dc_ind = 'D') THEN
263 l_st_total := l_st_total + l_begin_bal;
264 ELSIF (l_resource_status_dc_flag = 'C' and l_begin_bal_dc_ind = 'C') THEN
265 l_st_total := l_st_total + l_begin_bal;
266 ELSIF (l_resource_status_dc_flag = 'E') THEN
267 l_st_total := l_st_total + l_begin_bal;
268 END IF;
269 ELSIF (l_resource_status_be_flag = 'S' ) THEN
270
271 l_balance := l_end_bal - l_begin_bal;
272 IF ( l_balance > 0) THEN
273 l_dc_ind := 'D';
274 ELSE
275 l_dc_ind := 'C';
276 END IF;
277 IF (l_resource_status_dc_flag = 'D' and l_dc_ind = 'D') THEN
278 l_st_total := l_st_total + l_balance;
279 ELSIF (l_resource_status_dc_flag = 'C' and l_dc_ind = 'C') THEN
280 l_st_total := l_st_total + l_balance;
281 ELSIF (l_resource_status_dc_flag = 'E') THEN
282 l_st_total := l_st_total + l_balance;
283 END IF;
284
285 v_amount2 := l_balance;
286
287 END IF;
288
289
290 IF (l_total_resource_be_flag = 'E') AND (l_resource_status_be_flag = 'E') THEN
291 IF l_end_bal_dc_ind = 'D' THEN
292 v_amount2 := 0;
293 ELSE
294 v_amount1 := 0;
295 END IF;
296 END IF;
297
298 /* Added space in from of account number to order the edit check 8 report information*/
299 v_sgl_acct_number := ' '||l_ussgl_account;
300 create_log_record(v_log_text);
301
302 END LOOP;
303
304 CLOSE fv_facts_ussgl_accounts_c;
305
306 l_st_total := -1*l_st_total;
307
308 IF (l_to_total = l_st_total) THEN
309 v_edit_check_status := 'Passed';
310 ELSE
311 g_error_flag := 2;
312 v_edit_check_status := 'Failed';
313 END IF;
314
315 create_status_record(v_edit_check_number, v_edit_check_status) ;
316
317
318 EXCEPTION WHEN OTHERS THEN
319 v_log_text := SQLERRM;
320 g_error_flag := 2;
321 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
322 END; --edit_check_2
323
324
325 PROCEDURE edit_check_3 IS
326 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_3';
327
328 l_budget_credit NUMBER := 0;
329 l_budget_debit NUMBER := 0;
330
331 -- Cursor to fetch Credit and Debit Beginning balance
332 -- from FV_FACTS_TEMP for budgetary accounts
333 CURSOR check3 IS
334 SELECT nvl(amount,0) amount, debit_credit, sgl_acct_number
335 FROM fv_facts_temp
336 WHERE treasury_symbol_id = g_treasury_symbol_id
337 AND fct_int_record_category = 'REPORTED_NEW'
338 AND fct_int_record_type = 'BLK_DTL'
339 AND sgl_acct_number like '4%'
340 AND begin_end = 'B';
341
342
343 BEGIN
344 init_vars;
345 v_edit_check_number := 3;
346
347 FOR check3_rec in check3
348 LOOP
349 v_amount := check3_rec.amount;
350 /* Added space in from of account number to order the edit check 8 report information*/
351 v_sgl_acct_number := ' '||check3_rec.sgl_acct_number;
352
353 create_log_record(v_log_text);
354
355 IF check3_rec.debit_credit = 'C' THEN
356 l_budget_credit := l_budget_credit + v_amount;
357 ELSE
358 l_budget_debit := l_budget_debit + v_amount;
359 END IF;
360
361 END LOOP;
362
363 IF l_budget_debit = -1*(l_budget_credit) THEN
364 v_edit_check_status := 'Passed';
365 ELSE
366 g_error_flag := 2;
367 v_edit_check_status := 'Failed';
368 END IF;
369
370 create_status_record(v_edit_check_number, v_edit_check_status) ;
371
372
373 EXCEPTION WHEN OTHERS THEN
374 v_log_text := SQLERRM;
375 g_error_flag := 2;
376 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
377 END; --edit_check_3
378
379
380 PROCEDURE edit_check_4 IS
381 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_4';
382
383 -- Cursor to fetch ending balance, account number for all accounts
384 -- where YE_ANTICIPATED_FLAG = 'Y'
385 CURSOR anticipated_items_c is
386 SELECT nvl(sum(fft.amount),0), fft.sgl_acct_number
387 FROM fv_facts_temp fft,
388 fv_facts_ussgl_accounts ffa
392 AND ffa.ussgl_account = fft.sgl_acct_number
389 WHERE treasury_symbol_id = g_treasury_symbol_id
390 AND fct_int_record_category = 'REPORTED_NEW'
391 AND fct_int_record_type = 'BLK_DTL'
393 AND ffa.ye_anticipated_flag = 'Y'
394 AND fft.begin_end = 'E'
395 GROUP BY fft.sgl_acct_number;
396
397 l_count number;
398 l_amount NUMBER := 0;
399 l_sgl_acct_number varchar2(30);
400
401
402 BEGIN
403
404 init_vars;
405
406 l_count := 0;
407 v_edit_check_number := 4;
408
409 OPEN anticipated_items_c;
410 LOOP
411
412 FETCH anticipated_items_c
413 INTO l_amount, l_sgl_acct_number;
414
415 EXIT WHEN anticipated_items_c%NOTFOUND OR anticipated_items_c%NOTFOUND IS NULL;
416
417 IF (l_amount <> 0) THEN
418 l_count := l_count +1;
419
420 v_amount := l_amount;
421 /* Added space in from of account number to order the edit check 8 report information*/
422 v_sgl_acct_number := ' '||l_sgl_acct_number;
423 create_log_record(v_log_text);
424 END IF;
425
426 END LOOP;
427
428 IF (l_count > 0) THEN
429 g_error_flag := 2;
430 v_edit_check_status := 'Failed';
431 ELSE
432 v_edit_check_status := 'Passed';
433 v_amount := NULL;
434 -- create_log_record(v_log_text);
435 END IF;
436
437 create_status_record(v_edit_check_number, v_edit_check_status) ;
438
439 CLOSE anticipated_items_c;
440
441 EXCEPTION WHEN OTHERS THEN
442 v_log_text := SQLERRM;
443 g_error_flag := 2;
444 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
445 END; --edit_check_4
446
447 PROCEDURE edit_check_5 IS
448 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_5';
449
450 --Cursor to fetch Resource and Equity flags and amounts
451 CURSOR check5 IS
452 SELECT fft.sgl_acct_number, nvl(amount,0) amount,
453 ffa.ye_resource_equity_flag
454 FROM fv_facts_temp fft,
455 fv_facts_ussgl_accounts ffa
456 WHERE fft.treasury_symbol_id = g_treasury_symbol_id
457 AND fft.fct_int_record_category = 'REPORTED_NEW'
458 AND fft.fct_int_record_type = 'BLK_DTL'
459 AND ffa.ussgl_account = fft.sgl_acct_number
460 AND ffa.ye_resource_equity_flag in ('R','E')
461 AND fft.begin_end = ffa.ye_resource_equity_be_flag;
462
463 l_total_resources NUMBER := 0;
464 l_total_equity NUMBER := 0;
465
466 BEGIN
467
468 init_vars;
469 v_edit_check_number := 5;
470
471 FOR check5_rec IN check5
472 LOOP
473
474 v_amount1 := 0;
475 v_amount2 := 0;
476
477 /* Added space in from of account number to order the edit check 8 report information*/
478 v_sgl_acct_number := ' '||check5_rec.sgl_acct_number;
479
480 IF check5_rec.ye_resource_equity_flag = 'R' THEN
481 v_amount1 := check5_rec.amount;
482 l_total_resources := l_total_resources + check5_rec.amount;
483 create_log_record(v_log_text);
484 ELSE
485 v_amount2 := check5_rec.amount;
486 l_total_equity := l_total_equity + check5_rec.amount;
487 create_log_record(v_log_text);
488 END IF;
489
490 END LOOP;
491
492 IF l_total_resources = -1*(l_total_equity) THEN
493 v_edit_check_status := 'Passed' ;
494 ELSE
495 g_error_flag := 2;
496 v_edit_check_status := 'Failed' ;
497 END IF;
498
499 create_status_record(v_edit_check_number, v_edit_check_status) ;
500
501 EXCEPTION WHEN OTHERS THEN
502 v_log_text := SQLERRM;
503 g_error_flag := 2;
504 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
505 END; --edit_check_5
506
507
508 PROCEDURE edit_check_6 (p_ledger_id NUMBER) IS
509 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_6';
510
511 -- l_set_of_books_id NUMBER;
512
513 CURSOR rt7_codes_c IS
514 SELECT ffa.rt7_code_id,
515 ffc.rt7_code,
516 ffa.preclosing_unexpended_amt
517 FROM fv_facts_authorizations ffa,
518 fv_facts_rt7_codes ffc
519 WHERE ffa.treasury_symbol_id = g_treasury_symbol_id
520 AND ffa.rt7_code_id = ffc.rt7_code_id
521 AND ffa.set_of_books_id = p_ledger_id;
522
523 l_rt7_code_id number(15);
524 l_rt7_code varchar2(3);
525 l_accounts_balance number;
526 l_unexp_amount number;
527 l_count number;
528
529 BEGIN
530
531 init_vars;
532 v_edit_check_number := 6;
533
534 -- l_set_of_books_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
535 --Fetch the authorization code for the treasury symbol
536
537 OPEN rt7_codes_c;
538 l_count := 0;
539 LOOP
540
541 FETCH rt7_codes_c
542 INTO l_rt7_code_id,
543 l_rt7_code,
544 l_unexp_amount;
545
546 EXIT WHEN rt7_codes_c%NOTFOUND OR rt7_codes_c%NOTFOUND IS NULL;
547
548 --Fetch sum of account balances for the authorization code
549
550 BEGIN
551
552 SELECT NVL(sum(fft.amount),0)
553 INTO l_accounts_balance
554 FROM fv_facts_temp fft,
555 fv_facts_rt7_accounts rta
556 WHERE rta.rt7_code_id = l_rt7_code_id
557 AND rta.rt7_ussgl_account = fft.sgl_acct_number
558 AND fft.treasury_symbol_id = g_treasury_symbol_id
559 AND fft.fct_int_record_category = 'REPORTED_NEW'
563 END;
560 AND fft.fct_int_record_type = 'BLK_DTL'
561 AND fft.begin_end = decode(rta.rt7_ussgl_account, '4139','B','4149','B','E');
562
564 v_dummy_var := l_rt7_code;
565
566 /* Added space in from of account number to order the edit check 8 report information*/
567 v_sgl_acct_number := ' 1. Preclosing Unexp Amt';
568 v_amount := l_unexp_amount;
569 create_log_record(v_log_text);
570
571 /* Added space in from of account number to order the edit check 8 report information*/
572 v_sgl_acct_number := ' 2. Sum of Account Balance';
573 v_amount := l_accounts_balance;
574 create_log_record(v_log_text);
575
576 /* Added space in from of account number to order the edit check 8 report information*/
577 v_sgl_acct_number := ' Difference (1-2)';
578 v_amount := (l_unexp_amount - l_accounts_balance);
579 create_log_record(v_log_text);
580
581 IF (l_accounts_balance <> l_unexp_amount) THEN
582 l_count := l_count + 1;
583 END IF;
584
585 END LOOP;
586 CLOSE rt7_codes_c;
587
588 IF (l_count > 0) THEN
589 v_edit_check_status := 'Failed' ;
590 g_error_flag := 2;
591 ELSE
592 v_edit_check_status := 'Passed' ;
593 END IF;
594
595 create_status_record(v_edit_check_number, v_edit_check_status) ;
596
597 EXCEPTION WHEN OTHERS THEN
598 v_log_text := SQLERRM;
599 g_error_flag := 2;
600 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
601 END; --edit_check_6
602
603
604 PROCEDURE edit_check_7 IS
605 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_7';
606
607 -- Cursor to fetch ending balance for accounts where
608 -- fund_balance_account_flag is 'Y'
609 CURSOR check7 IS
610 SELECT nvl(fft.amount,0) amount, fft.sgl_acct_number
611 FROM fv_facts_temp fft,
612 fv_facts_ussgl_accounts ffa
613 WHERE ffa.fund_balance_account_flag = 'Y'
614 AND fft.sgl_acct_number = ffa.ussgl_account
615 AND fft.treasury_symbol_id = g_treasury_symbol_id
616 AND fft.fct_int_record_category = 'REPORTED_NEW'
617 AND fft.fct_int_record_type = 'BLK_DTL'
618 AND fft.begin_end = 'E';
619
620 l_unexp_amount NUMBER;
621 l_end_balance NUMBER := 0;
622
623 BEGIN
624
625 init_vars;
626 v_edit_check_number := 7;
627
628 -- Fetch preclosing ending balance for the treasury symbol
629 -- Bug 13102945: Fetch preclosing amounts from fv_preclosing_amounts instead of
630 -- fv_treasury_symbols
631 SELECT preclosing_unexpended_amt
632 INTO l_unexp_amount
633 FROM fv_preclosing_amounts
634 WHERE treasury_symbol_id = g_treasury_symbol_id;
635
636 v_amount2 := l_unexp_amount;
637 create_log_record(v_log_text);
638
639 -- reset v_amount --> l_unexp_amount to NULL
640 -- since it needs to be printed only once
641 v_amount2 := NULL;
642
643 FOR check7_rec in check7
644 LOOP
645 /* Added space in from of account number to order the edit check 8 report information*/
646 v_sgl_acct_number := ' '||check7_rec.sgl_acct_number;
647 v_amount1 := check7_rec.amount;
648 create_log_record(v_log_text);
649
650 l_end_balance := l_end_balance + check7_rec.amount;
651
652 END LOOP;
653
654 IF (l_unexp_amount is NULL ) THEN
655 v_edit_check_status := 'Failed' ;
656 ELSIF (l_unexp_amount = l_end_balance) THEN
657 v_edit_check_status := 'Passed' ;
658 ELSE
659 v_edit_check_status := 'Failed' ;
660 g_error_flag := 2;
661 END IF;
662
663 create_status_record(v_edit_check_number, v_edit_check_status) ;
664
665 EXCEPTION WHEN OTHERS THEN
666 v_log_text := SQLERRM;
667 g_error_flag := 2;
668 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
669 END; -- edit_check_7
670
671 PROCEDURE edit_check_8 IS
672 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_8';
673
674
675 -- Cursor to fetch amounts for Obligations Incurred
676 -- This cursor fetches only if the account is End-Begin.
677
678 CURSOR check8_col1b_cur
679 (
680 p_treasury_symbol_id NUMBER
681 )
682 IS
683 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) obligations_incurred_s_amt
684 FROM fv_facts_temp fft,
685 fv_facts_ussgl_accounts ffa
686 WHERE ffa.obligations_incurred_flag = 'Y'
687 AND ffa.ussgl_account = fft.sgl_acct_number
688 AND fft.treasury_symbol_id = p_treasury_symbol_id
689 AND fft.fct_int_record_category = 'REPORTED_NEW'
690 AND fft.fct_int_record_type = 'BLK_DTL';
691 -- AND ffa.resource_status_be_flag IN ('S');
692
693
694 -- Cursor to fetch amounts for Spending from Collections and PYA
695 -- This cursor fetches only if the account is End-Begin.
696 CURSOR check8_col2b_cur
697 (
698 p_treasury_symbol_id NUMBER
699 )
700 IS
701 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
702 FROM fv_facts_temp fft,
703 fv_facts_ussgl_accounts ffa
704 WHERE ffa.spndng_from_coll_and_pya_flag = 'Y'
705 AND ffa.ussgl_account = fft.sgl_acct_number
706 AND fft.treasury_symbol_id = p_treasury_symbol_id
707 AND fft.fct_int_record_category = 'REPORTED_NEW'
708 AND fft.fct_int_record_type = 'BLK_DTL';
709 -- AND ffa.total_resource_be_flag IN ('S');
710
711 -- Cursor to fetch amounts for Obligations as of 10/1
712 -- Column 3 always use beginning balance
716 )
713 CURSOR check8_col3_cur
714 (
715 p_treasury_symbol_id NUMBER
717 IS
718 SELECT SUM(NVL(amount,0)) obligations_as_of_10_1_amt
719 FROM fv_facts_temp fft,
720 fv_facts_ussgl_accounts ffa
721 WHERE ffa.obligations_as_of_10_1_flag = 'Y'
722 AND ffa.ussgl_account = fft.sgl_acct_number
723 AND fft.treasury_symbol_id = p_treasury_symbol_id
724 AND fft.fct_int_record_category = 'REPORTED_NEW'
725 AND fft.fct_int_record_type = 'BLK_DTL'
726 AND fft.begin_end = 'B';
727
728 -- Cursor to fetch amounts for Obligations Transferred and Obligations Period/End
729 -- Column 4 and 5 always use ending balance
730 CURSOR check8_col4_and_5_cur
731 (
732 p_treasury_symbol_id NUMBER
733 )
734 IS
735 SELECT NVL(SUM(DECODE(ffa.obligations_transferred_flag, 'Y', NVL(amount,0), 0)),0) obligations_transferred_amt,
736 NVL(SUM(DECODE(ffa.obligations_period_end_flag, 'Y', NVL(amount,0), 0)),0) obligations_period_end_amt
737 FROM fv_facts_temp fft,
738 fv_facts_ussgl_accounts ffa
739 WHERE (
740 ffa.obligations_transferred_flag = 'Y' OR
741 ffa.obligations_period_end_flag = 'Y'
742 )
743 AND ffa.ussgl_account = fft.sgl_acct_number
744 AND fft.treasury_symbol_id = p_treasury_symbol_id
745 AND fft.fct_int_record_category = 'REPORTED_NEW'
746 AND fft.fct_int_record_type = 'BLK_DTL'
747 AND fft.begin_end = 'E';
748
749 -- Cursor to fetch amounts
750 -- for Disbursements and Collections (only Ending balance type)
751 CURSOR check8_disb_colla_cur
752 (
753 p_treasury_symbol_id NUMBER
754 )
755 IS
756 SELECT NVL(SUM(DECODE(ffa.disbursements_flag, 'Y', NVL(amount,0), 0)),0) disbursements_amt,
757 NVL(SUM(DECODE(ffa.collections_flag, 'Y', NVL(amount,0), 0)),0) collections_amt
758 FROM fv_facts_temp fft,
759 fv_facts_ussgl_accounts ffa
760 WHERE (
761 ffa.disbursements_flag = 'Y' OR
762 ffa.collections_flag = 'Y'
763 )
764 AND ffa.ussgl_account = fft.sgl_acct_number
765 AND fft.treasury_symbol_id = p_treasury_symbol_id
766 AND fft.fct_int_record_category = 'REPORTED_NEW'
767 AND fft.fct_int_record_type = 'BLK_DTL'
768 AND fft.begin_end = 'E'
769 AND ffa.edck12_balance_type IN ('E' ,'S');-- by ks for bug bug 5328107
770
771 -- Cursor to fetch amounts
772 -- for Disbursements (only Ending - Beginning balance type)
773 CURSOR check8_disbb_cur
774 (
775 p_treasury_symbol_id NUMBER
776 )
777 IS
778 --SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) beg_disbursements_amt
779 -- above line commnted out by ks for bug bug 5328107
780 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0),0)), 0) beg_disbursements_amt
781 FROM fv_facts_temp fft,
782 fv_facts_ussgl_accounts ffa
783 WHERE ffa.disbursements_flag = 'Y'
784 AND ffa.ussgl_account = fft.sgl_acct_number
785 AND fft.treasury_symbol_id = p_treasury_symbol_id
786 AND fft.fct_int_record_category = 'REPORTED_NEW'
787 AND fft.fct_int_record_type = 'BLK_DTL'
788 AND ffa.edck12_balance_type = 'S';
789
790 -- Cursor to fetch amounts
791 -- for Collections (only Ending - Beginning balance type)
792 CURSOR check8_collb_cur
793 (
794 p_treasury_symbol_id NUMBER
795 )
796 IS
797 -- ks for bug bug 5328107
798 SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), 0)), 0) beg_collections_amt
799 FROM fv_facts_temp fft,
800 fv_facts_ussgl_accounts ffa
801 WHERE ffa.collections_flag = 'Y'
802 AND ffa.ussgl_account = fft.sgl_acct_number
803 AND fft.treasury_symbol_id = p_treasury_symbol_id
804 AND fft.fct_int_record_category = 'REPORTED_NEW'
805 AND fft.fct_int_record_type = 'BLK_DTL'
806 AND ffa.edck12_balance_type = 'S';
807
808 l_obligations_incurred_amt NUMBER;
809 l_spndng_from_coll_and_pya_amt NUMBER;
810 l_obligations_as_of_10_1_amt NUMBER;
811 l_obligations_transferred_amt NUMBER;
812 l_obligations_period_end_amt NUMBER;
813 l_disbursements_amt NUMBER;
814 l_collections_amt NUMBER;
815 l_beg_disbursements_amt NUMBER;
816 l_beg_collections_amt NUMBER;
817
818 BEGIN
819 init_vars;
820
821 l_obligations_incurred_amt := 0;
822 l_spndng_from_coll_and_pya_amt := 0;
823 l_obligations_as_of_10_1_amt := 0;
824 l_obligations_transferred_amt := 0;
825 l_obligations_period_end_amt := 0;
826 l_disbursements_amt := 0;
827 l_collections_amt := 0;
828 l_beg_disbursements_amt := 0;
829 l_beg_collections_amt := 0;
830
831 /*FOR check8_col1a_rec IN check8_col1a_cur (g_treasury_symbol_id) LOOP
832 l_obligations_incurred_amt := NVL(l_obligations_incurred_amt, 0) + NVL(check8_col1a_rec.obligations_incurred_be_amt, 0);
833 END LOOP;*/
834
835 FOR check8_col1b_rec IN check8_col1b_cur (g_treasury_symbol_id) LOOP
836 l_obligations_incurred_amt := NVL(l_obligations_incurred_amt, 0) + NVL(check8_col1b_rec.obligations_incurred_s_amt, 0);
837 END LOOP;
838
839 /* FOR check8_col2a_rec IN check8_col2a_cur (g_treasury_symbol_id) LOOP
840 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);
841 END LOOP;*/
842
843 FOR check8_col2b_rec IN check8_col2b_cur (g_treasury_symbol_id) LOOP
844 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);
845 END LOOP;
846
847 FOR check8_col3_rec IN check8_col3_cur (g_treasury_symbol_id) LOOP
851 FOR check8_col4_and_5_rec IN check8_col4_and_5_cur (g_treasury_symbol_id) LOOP
848 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);
849 END LOOP;
850
852 l_obligations_transferred_amt := NVL(l_obligations_transferred_amt, 0) + NVL(check8_col4_and_5_rec.obligations_transferred_amt, 0);
853 l_obligations_period_end_amt := NVL(l_obligations_period_end_amt, 0) + NVL(check8_col4_and_5_rec.obligations_period_end_amt, 0);
854 END LOOP;
855
856
857 FOR check8_disb_colla_rec IN check8_disb_colla_cur (g_treasury_symbol_id) LOOP
858 l_disbursements_amt := NVL(l_disbursements_amt, 0) + NVL(check8_disb_colla_rec.disbursements_amt, 0);
859 l_collections_amt := NVL(l_collections_amt, 0) + NVL(check8_disb_colla_rec.collections_amt, 0);
860 END LOOP;
861
862 fnd_file.put_line(fnd_file.log , 'Ending disbursement ' || l_disbursements_amt);
863 fnd_file.put_line(fnd_file.log , 'Ending collection ' || l_collections_amt);
864
865 FOR check8_disbb_rec IN check8_disbb_cur (g_treasury_symbol_id) LOOP
866 l_beg_disbursements_amt := NVL(check8_disbb_rec.beg_disbursements_amt, 0);
867 END LOOP;
868
869 FOR check8_collb_rec IN check8_collb_cur (g_treasury_symbol_id) LOOP
870 l_beg_collections_amt := NVL(check8_collb_rec.beg_collections_amt, 0);
871 END LOOP;
872
873 --- since we want to get only the acvity or ending_balances , we need to
874 -- substract any begining disbursement or collection from ending_balaces;
875
876 fnd_file.put_line(fnd_file.log , 'beg disbursement ' || l_beg_disbursements_amt);
877 fnd_file.put_line(fnd_file.log , 'beg collection ' || l_beg_collections_amt);
878 l_disbursements_amt := NVL(l_disbursements_amt, 0) + NVL(l_beg_disbursements_amt, 0);
879 l_collections_amt := NVL(l_collections_amt, 0) + NVL(l_beg_collections_amt, 0);
880
881 l_obligations_incurred_amt := -1*l_obligations_incurred_amt; --Cr balance report as + and Dr balance report as -
882 l_obligations_as_of_10_1_amt := -1*l_obligations_as_of_10_1_amt; --Cr balance report as + and Dr balance report as -
883 l_obligations_transferred_amt := -1*l_obligations_transferred_amt; --Cr balance report as + and Dr balance report as -
884 l_obligations_period_end_amt := -1*l_obligations_period_end_amt; --Cr balance report as + and Dr balance report as -
885
886 IF ((NVL(l_obligations_incurred_amt, 0) -
887 NVL(l_spndng_from_coll_and_pya_amt, 0) +
888 NVL(l_obligations_as_of_10_1_amt, 0) +
889 NVL(l_obligations_transferred_amt, 0) -
890 NVL(l_obligations_period_end_amt, 0))
891 =
892 (-1*(NVL(l_disbursements_amt, 0) +
893 NVL(l_collections_amt, 0)))) THEN
894 v_edit_check_status := 'Passed' ;
895 ELSE
896 v_edit_check_status := 'Failed' ;
897 g_error_flag := 2;
898 END IF;
899
900 v_edit_check_number := 8;
901
902 v_sgl_acct_number := '1Obligations Incurred';
903 v_amount := l_obligations_incurred_amt;
904 v_amount1 := l_obligations_incurred_amt;
905 create_log_record(v_log_text);
906
907 v_sgl_acct_number := '2- Spending from Collections and PYA';
908 v_amount := l_spndng_from_coll_and_pya_amt;
909 v_amount1 := -1*l_spndng_from_coll_and_pya_amt;
910 create_log_record(v_log_text);
911
912 v_sgl_acct_number := '3+ Obligations as of 10/1';
913 v_amount := l_obligations_as_of_10_1_amt;
914 v_amount1 := l_obligations_as_of_10_1_amt;
915 create_log_record(v_log_text);
916
917 v_sgl_acct_number := '4+/- Obligations Transferred';
918 v_amount := l_obligations_transferred_amt;
919 v_amount1 := l_obligations_transferred_amt;
920 create_log_record(v_log_text);
921
922 v_sgl_acct_number := '5- Obligations Period End';
923 v_amount := l_obligations_period_end_amt;
924 v_amount1 := -1*l_obligations_period_end_amt;
925 create_log_record(v_log_text);
926
927 /*v_edit_check_number := 9;
928 v_sgl_acct_number := '6Disbursements (+)';
929 v_amount := l_disbursements_amt;
930 v_amount1 := -1*l_disbursements_amt;
931 create_log_record(v_log_text);
932
933 v_sgl_acct_number := '7Collections (-)';
934 v_amount := l_collections_amt;
935 v_amount1 := -1*l_collections_amt;
936 create_log_record(v_log_text);*/
937
938 v_edit_check_number := 8;
939 create_status_record(v_edit_check_number, v_edit_check_status);
940 -- Inserting dummy record for edit check 9
941 /*v_edit_check_number := 9;
942 create_status_record(v_edit_check_number, v_edit_check_status);*/
943
944 EXCEPTION
945 WHEN OTHERS THEN
946 v_log_text := SQLERRM;
947 g_error_flag := 2;
948 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
949 END; --edit_check_8
950
951 --CGAC ER
952 PROCEDURE edit_check_9(g_facts_run_quarter IN number) IS
953 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_9';
954
955 l_set_of_books_id NUMBER;
956 l_accounts_balance NUMBER;
957 l_subclass_amt NUMBER;
958
959 BEGIN
960
961 init_vars;
962 v_edit_check_number := 9;
963
964 l_set_of_books_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
965 --Fetch the authorization code for the treasury symbol
966
967 /*SELECT NVL(preclosing_subclass_amt, 0)
968 INTO l_subclass_amt
969 FROM fv_treasury_symbols
970 WHERE treasury_symbol_id = g_treasury_symbol_id
971 AND set_of_books_id = l_set_of_books_id;*/
972
973 SELECT NVL(PRECLOSING_SUBCLASS43_AMT, 0)
974 INTO l_subclass_amt
975 FROM FV_PRECLOSING_AMOUNTS
976 WHERE treasury_symbol_id = g_treasury_symbol_id
977 AND ledger_id = l_set_of_books_id;
978
979
980
984 SELECT NVL(sum(fft.amount),0)
981 --Fetch sum of account balances for the authorization code
982
983 BEGIN
985 INTO l_accounts_balance
986 FROM fv_facts_temp fft,
987 fv_facts_rt7_accounts rta,
988 fv_facts_rt7_codes rtc
989 WHERE rtc.factsII_edit_code = 'Subclass 43'
990 AND rta.rt7_code_id = rtc.rt7_code_id
991 AND rta.rt7_ussgl_account = fft.sgl_acct_number
992 AND fft.treasury_symbol_id = g_treasury_symbol_id
993 AND fft.fct_int_record_category = 'REPORTED_NEW'
994 AND fft.fct_int_record_type = 'BLK_DTL'
995 AND fft.begin_end = rta.rt7_be_flag
996 AND DECODE(rta.rt7_transaction_partner,'N',fft.transaction_partner,
997 rta.rt7_transaction_partner) = fft.transaction_partner;
998
999 END;
1000
1001 /* Added space in from of account number to order the edit check 8 report
1002 * information*/
1003 v_sgl_acct_number := ' 1. Preclosing Subclass 43 Amt';
1004 v_amount := l_subclass_amt;
1005 create_log_record(v_log_text);
1006 fv_utility.log_mesg('Subclass 43 amt: '||l_subclass_amt);
1007
1008 /* Added space in from of account number to order the edit check 8 report
1009 * information*/
1010 v_sgl_acct_number := ' 2. Sum of Account Balance';
1011 v_amount := l_accounts_balance;
1012 create_log_record(v_log_text);
1013 fv_utility.log_mesg('Total amt: '||l_accounts_balance);
1014
1015 /* Added space in from of account number to order the edit check 8 report
1016 * information*/
1017
1018 v_sgl_acct_number := ' Difference (1-2)';
1019 v_amount := (l_subclass_amt - l_accounts_balance);
1020 create_log_record(v_log_text);
1021
1022 IF (l_accounts_balance <> l_subclass_amt) THEN
1023 IF (g_facts_run_quarter = 4) THEN
1024 v_edit_check_status := 'Failed' ;
1025 g_error_flag := 2;
1026 ELSE
1027 v_edit_check_status := 'Not Applicable' ;
1028 END IF;
1029
1030 ELSE
1031 IF (g_facts_run_quarter = 4) THEN
1032 v_edit_check_status := 'Passed' ;
1033 ELSE
1034 v_edit_check_status := 'Not Applicable' ;
1035 END IF;
1036 END IF;
1037
1038 create_status_record(v_edit_check_number, v_edit_check_status) ;
1039
1040 EXCEPTION WHEN OTHERS THEN
1041 v_log_text := SQLERRM;
1042 g_error_flag := 2;
1043 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1044 l_module_name||'.final_exception',v_log_text);
1045 END; --edit_check_9
1046
1047 -- Added the foll to fix bug 1974485
1048 PROCEDURE edit_check_10 IS
1049 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_10';
1050
1051 l_total_amount NUMBER := 0;
1052
1053 CURSOR check10 IS
1054 SELECT fft.sgl_acct_number,
1055 SUM(NVL(fft.amount,0)) amount
1056 FROM fv_facts_temp fft,
1057 fv_facts_ussgl_accounts ffacc
1058 WHERE ffacc.ussgl_account = fft.sgl_acct_number
1059 AND fft.treasury_symbol_id = g_treasury_symbol_id
1060 AND fft.fct_int_record_category = 'REPORTED_NEW'
1061 AND fft.fct_int_record_type = 'BLK_DTL'
1062 AND fft.begin_end = 'E'
1063 AND ffacc.cancelled_flag = 'Y'
1064 GROUP BY fft.sgl_acct_number;
1065
1066 BEGIN
1067 init_vars;
1068 v_edit_check_number := 10;
1069
1070 FOR check10_rec IN check10
1071 LOOP
1072 /* Added space in from of account number to order the edit check 8 report information*/
1073 v_sgl_acct_number := ' '||check10_rec.sgl_acct_number;
1074 v_amount := check10_rec.amount;
1075 create_log_record(v_log_text);
1076 l_total_amount := l_total_amount + check10_rec.amount;
1077
1078 END LOOP;
1079
1080 IF l_total_amount = 0 THEN
1081 v_edit_check_status := 'Passed' ;
1082 ELSE
1083 v_edit_check_status := 'Failed' ;
1084 END IF;
1085
1086 create_status_record(v_edit_check_number, v_edit_check_status) ;
1087
1088 EXCEPTION WHEN OTHERS THEN
1089 v_log_text := SQLERRM;
1090 g_error_flag := 2;
1091 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1092 END; --edit_check_10
1093
1094 PROCEDURE edit_check_11 IS
1095 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_11';
1096
1097 CURSOR neg_rec_pay_c IS
1098 SELECT ffacc.ussgl_account,
1099 NVL(sum(fft.amount),0),
1100 ffacc.ye_neg_receivables_flag,
1101 ffacc.natural_balance
1102 FROM fv_facts_temp fft,
1103 fv_facts_ussgl_accounts ffacc
1104 WHERE (ffacc.ye_neg_receivables_flag = 'Y'
1105 OR ffacc.ye_neg_payables_flag = 'Y')
1106 AND ffacc.ussgl_account = fft.sgl_acct_number
1107 AND fft.treasury_symbol_id = g_treasury_symbol_id
1108 AND fft.fct_int_record_category = 'REPORTED_NEW'
1109 AND fft.fct_int_record_type = 'BLK_DTL'
1110 AND fft.begin_end = 'E'
1111 GROUP BY ffacc.ussgl_account, ffacc.ye_neg_receivables_flag, ffacc.natural_balance;
1112
1113 CURSOR general_acc_c IS
1114 SELECT ffacc.ussgl_account, nvl(sum(fft.amount),0),
1115 ffacc.natural_balance
1116 FROM fv_facts_temp fft,
1117 fv_facts_ussgl_accounts ffacc
1118 WHERE ffacc.ye_general_flag = 'Y'
1119 AND ffacc.ussgl_account = fft.sgl_acct_number
1120 AND fft.treasury_symbol_id = g_treasury_symbol_id
1121 AND fft.fct_int_record_category = 'REPORTED_NEW'
1122 AND fft.fct_int_record_type = 'BLK_DTL'
1123 AND fft.begin_end = 'E'
1124 GROUP BY ffacc.ussgl_account, ffacc.natural_balance;
1125
1129 l_count number;
1126 l_ussgl_account varchar2(30);
1127 l_amount number;
1128 l_acc_type varchar2(25);
1130 l_neg_receivables_flag varchar2(1);
1131 l_natural_balance FV_FACTS_USSGL_ACCOUNTS.NATURAL_BALANCE%TYPE;
1132 l_dc_ind varchar2(1);
1133
1134 BEGIN
1135
1136 init_vars;
1137
1138 l_count := 0;
1139 v_edit_check_number := 11;
1140
1141 --Fetch accounts with negative receivable and negative payables balances
1142 OPEN neg_rec_pay_c;
1143
1144 LOOP
1145 FETCH neg_rec_pay_c
1146 INTO l_ussgl_account,
1147 l_amount,
1148 l_neg_receivables_flag,
1149 l_natural_balance;
1150
1151 EXIT WHEN neg_rec_pay_c%NOTFOUND OR neg_rec_pay_c%NOTFOUND IS NULL;
1152
1153 SELECT DECODE (l_neg_receivables_flag,'Y','NR','NP')
1154 INTO l_acc_type
1155 FROM DUAL;
1156
1157 /* Added space in from of account number to order the edit check 8 report information*/
1158 v_sgl_acct_number := ' '||l_ussgl_account;
1159 v_amount := l_amount;
1160 v_dummy_var := l_acc_type;
1161 -- create_log_record(v_log_text);
1162
1163 IF (l_amount > 0) THEN
1164 l_dc_ind := 'D';
1165 ELSE
1166 l_dc_ind := 'C';
1167 END IF;
1168
1169 IF (l_amount <> 0 AND l_dc_ind <> l_natural_balance) THEN
1170
1171 create_log_record(v_log_text);
1172
1173 l_count := l_count +1;
1174
1175 --LGOEL: Update temp table if Edit check 11 failed
1176 update fv_facts_temp
1177 set document_number = 'Y'
1178 where sgl_acct_number = l_ussgl_account
1179 and treasury_symbol_id = g_treasury_symbol_id
1180 and fct_int_record_category = 'REPORTED_NEW'
1181 and fct_int_record_type = 'BLK_DTL';
1182
1183 -- Enable the foot note flag for this failed
1184 -- edit check
1185 UPDATE fv_facts_submission
1186 SET foot_note_flag = 'Y'
1187 WHERE treasury_symbol_id = g_treasury_symbol_id;
1188
1189 END IF;
1190
1191 END LOOP;
1192
1193 CLOSE neg_rec_pay_c;
1194
1195 -- Initialize variables
1196 l_ussgl_account := NULL;
1197 l_amount := 0;
1198 l_natural_balance := NULL;
1199
1200 --Fetch General accounts which have a balance
1201 OPEN general_acc_c;
1202
1203 LOOP
1204
1205 FETCH general_acc_c
1206 INTO l_ussgl_account,
1207 l_amount,
1208 l_natural_balance;
1209
1210 EXIT WHEN general_acc_c%NOTFOUND OR general_acc_c%NOTFOUND IS NULL;
1211
1212 /* Added space in from of account number to order the edit check 8 report information*/
1213 v_sgl_acct_number := ' '||l_ussgl_account;
1214 v_amount := l_amount;
1215 v_dummy_var := 'GL';
1216 -- create_log_record(v_log_text);
1217
1218 IF (l_amount > 0) THEN
1219 l_dc_ind := 'D';
1220 ELSE
1221 l_dc_ind := 'C';
1222 END IF;
1223
1224 IF (l_amount <> 0 ) THEN
1225
1226 create_log_record(v_log_text);
1227
1228 l_count := l_count +1;
1229
1230 --LGOEL: Update temp table if Edit check 11 failed
1231 update fv_facts_temp
1232 set document_number = 'Y'
1233 where sgl_acct_number = l_ussgl_account
1234 and treasury_symbol_id = g_treasury_symbol_id
1235 and fct_int_record_category = 'REPORTED_NEW'
1236 and fct_int_record_type = 'BLK_DTL';
1237
1238 -- Enable the foot note flag for this failed
1239 -- edit check
1240 UPDATE fv_facts_submission
1241 SET foot_note_flag = 'Y'
1242 WHERE treasury_symbol_id = g_treasury_symbol_id;
1243
1244 END IF;
1245 END LOOP;
1246 CLOSE general_acc_c;
1247
1248 IF (l_count = 0) THEN
1249 v_edit_check_status := 'Passed' ;
1250
1251 -- Disable the foot note flag for this passed
1252 -- edit check in case it has failed earlier
1253 UPDATE fv_facts_submission
1254 SET foot_note_flag = 'N'
1255 WHERE treasury_symbol_id = g_treasury_symbol_id;
1256
1257 ELSE
1258 v_edit_check_status := 'Failed' ;
1259 IF (g_error_flag = 0) THEN
1260 g_error_flag := 1;
1261 END IF;
1262 END IF;
1263
1264 create_status_record(v_edit_check_number, v_edit_check_status) ;
1265
1266 EXCEPTION WHEN OTHERS THEN
1267 v_log_text := SQLERRM;
1268 IF (g_error_flag = 0) THEN
1269 g_error_flag := 1;
1270 END IF;
1271 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1272 END; --edit_check_11
1273
1274
1275 PROCEDURE edit_check_12(p_facts_run_quarter number) IS
1276 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_12';
1277
1278 -- Cursor to fetch amounts
1279 -- for disbursement and collection accounts
1280 CURSOR check12 IS
1281 SELECT fft.sgl_acct_number,
1282 ffa.disbursements_flag,
1283 ffa.collections_flag,
1284 ffa.edck12_balance_type,
1285 sum(nvl(amount,0)) amount
1286 FROM fv_facts_temp fft,
1287 fv_facts_ussgl_accounts ffa
1288 WHERE (ffa.disbursements_flag = 'Y' or ffa.collections_flag = 'Y')
1289 AND ffa.ussgl_account = fft.sgl_acct_number
1290 AND fft.treasury_symbol_id = g_treasury_symbol_id
1291 AND fft.fct_int_record_category = 'REPORTED_NEW'
1292 AND fft.fct_int_record_type = 'BLK_DTL'
1293 AND fft.begin_end = 'E'
1294 group by
1295 fft.sgl_acct_number,
1296 ffa.disbursements_flag,
1297 ffa.collections_flag,
1298 ffa.edck12_balance_type;
1299 l_disbursements number := 0;
1300 l_collections number := 0;
1304
1301 l_net_outlays number := 0;
1302 l_224_outlays number := 0;
1303 v_begin_amount number := 0;
1305 BEGIN
1306 init_vars;
1307 v_edit_check_number := 12;
1308
1309 --Fetch 224 Outlays
1310 select decode(p_facts_run_quarter,1,sf224_qtr1_outlay,
1311 2,sf224_qtr2_outlay,3,sf224_qtr3_outlay,sf224_qtr4_outlay)
1312 into l_224_outlays
1313 from fv_treasury_symbols
1314 where treasury_symbol_id = g_treasury_symbol_id;
1315
1316 IF (l_224_outlays is NULL) THEN
1317
1318 v_amount := NULL;
1319 v_amount1 := NULL;
1320 v_amount2 := NULL;
1321
1322 create_log_record(v_log_text);
1323 v_edit_check_status := 'Not Applicable' ;
1324
1325 create_status_record(v_edit_check_number, v_edit_check_status) ;
1326
1327 IF (g_error_flag = 0) then
1328 g_error_flag := 1;
1329 END IF;
1330
1331 ELSE
1332
1333 FOR check12_rec IN check12
1334 LOOP
1335 /* Added space in from of account number to order the edit check 8 report information*/
1336 v_sgl_acct_number := ' '||check12_rec.sgl_acct_number;
1337 v_begin_amount := 0;
1338
1339 IF check12_rec.edck12_balance_type = 'S' THEN
1340 SELECT sum(nvl(fft.amount,0))
1341 INTO v_begin_amount
1342 FROM fv_facts_temp fft
1343 WHERE fft.sgl_acct_number = check12_rec.sgl_acct_number
1344 AND fft.treasury_symbol_id = g_treasury_symbol_id
1345 AND fft.fct_int_record_category = 'REPORTED_NEW'
1346 AND fft.fct_int_record_type = 'BLK_DTL'
1347 AND fft.begin_end = 'B';
1348 END IF;
1349
1350 IF check12_rec.collections_flag = 'Y' THEN
1351
1352 v_amount1 := NVL(check12_rec.amount,0) - NVL(v_begin_amount,0);
1353 v_amount2 := NULL;
1354
1355 create_log_record(v_log_text);
1356 l_collections := l_collections + v_amount1;
1357 ELSIF
1358 check12_rec.disbursements_flag = 'Y' THEN
1359 v_amount2 := NVL(check12_rec.amount,0) - NVL(v_begin_amount,0);
1360 v_amount1 := NULL;
1361
1362 create_log_record(v_log_text);
1363 l_disbursements := NVL(l_disbursements,0) + NVL(v_amount2,0);
1364 END IF;
1365
1366 END LOOP;
1367
1368 l_net_outlays := -1*(NVL(l_disbursements,0) + NVL(l_collections,0));
1369
1370 v_edit_check_number := 12.1;
1371
1372 /* Added space in from of account number to order the edit check 8 report information*/
1373 v_sgl_acct_number := ' 1. Net Outlays';
1374 v_amount := l_net_outlays;
1375 create_log_record(v_log_text);
1376
1377 /* Added space in from of account number to order the edit check 8 report information*/
1378 v_sgl_acct_number := ' 2. 224 Outlays';
1379 v_amount := l_224_outlays;
1380 create_log_record(v_log_text);
1381
1382 /* Added space in from of account number to order the edit check 8 report information*/
1383 v_sgl_acct_number := ' Difference (1-2)';
1384 v_amount := (l_net_outlays - l_224_outlays);
1385 create_log_record(v_log_text);
1386
1387 IF (l_net_outlays = l_224_outlays) THEN
1388 v_edit_check_status := 'Passed' ;
1389 ELSE
1390 v_edit_check_status := 'Failed' ;
1391 g_error_flag := 2;
1392 END IF;
1393
1394 v_edit_check_number := 12;
1395 create_status_record(v_edit_check_number, v_edit_check_status) ;
1396 -- Inserting dummy record for edit check 12 Net outlays printing
1397 v_edit_check_number := 12.1;
1398 create_status_record(v_edit_check_number, v_edit_check_status) ;
1399 END IF;
1400
1401 EXCEPTION WHEN OTHERS THEN
1402 v_log_text := SQLERRM;
1403 g_error_flag := 2;
1404 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
1405
1406 END; --edit_check_12
1407
1408 PROCEDURE edit_check_13
1409 IS
1410 l_control_acct_num VARCHAR2(30);
1411 l_auth_type VARCHAR2(1);
1412 l_unexp_exp VARCHAR2(1);
1413 l_beg_bal NUMBER:=0;
1414 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_13';
1415 l_closing_gp VARCHAR2(2);
1416 l_edit_check_status VARCHAR2(1):='Y';
1417 exp_date DATE;
1418 whether_Exp VARCHAR2(1);
1419 beg_date DATE;
1420 close_date DATE;
1421 flg VARCHAR2(1);
1422 sum_ending_bal NUMBER;
1423 l_temp_count NUMBER;
1424 l_facts_insert_flg VARCHAR2(1);
1425 l_has_data_count NUMBER;
1426 l_prior_year NUMBER(15) := g_period_year-1;
1427
1428 CURSOR closing_acct_c(p_closing_grp VARCHAR2)
1429 IS
1430 SELECT SUM(tmp.amount) amt,
1431 clos.authority_code auth_type
1432 FROM fv_facts_temp tmp,
1433 fv_facts2_closing_validation clos
1434 WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
1435 and tmp.fct_int_record_category='REPORTED_NEW'
1436 and tmp.fct_int_record_type='BLK_DTL'
1437 AND tmp.sgl_acct_number = clos.ussgl_account
1438 AND clos.closing_grp = p_closing_grp
1439 AND clos.closing_acct_flag ='Y'
1440 AND tmp.begin_end = 'B'
1441 group by clos.authority_code;
1442
1443 CURSOR end_bal_cur(p_whether_exp VARCHAR2,p_closing_grp VARCHAR2)
1444 IS
1445 SELECT bal.ending_bal,
1446 bal.ussgl_account ,
1447 bal.authority_type
1448 FROM fv_facts2_retain_bal bal,
1449 fv_facts2_closing_validation clos
1450 WHERE bal.treasury_symbol_id = g_treasury_symbol_id
1451 AND clos.closing_grp = p_closing_grp
1452 AND bal.closing_grp = p_closing_grp
1453 AND bal.period_year = l_prior_year
1454 --AND bal.period_num = p_period_num
1455 AND clos.ussgl_account = bal.ussgl_account
1459
1456 AND (clos.authority_code is null or bal.authority_type=clos.authority_code)
1457 AND (clos.expired_unexpired IS NULL
1458 OR clos.expired_unexpired = p_whether_exp);
1460
1461 CURSOR get_all_closing_acct
1462 IS
1463 SELECT closing_grp, ussgl_account
1464 FROM fv_facts2_closing_validation
1465 WHERE closing_acct_flag='Y'
1466 ORDER BY closing_grp;
1467
1468 BEGIN
1469 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Entering Edit Check 13');
1471 END IF;
1472 --fnd_file.put_line(fnd_file.log , '********************** EDIT CHEK 13 BEGIN ************');
1473 init_vars;
1474 v_edit_check_number :=13;
1475
1476 v_edit_check_status := 'Failed';
1477 l_auth_type :=NULL;
1478 -- check if treasury symbol has expired
1479 SELECT expiration_date
1480 INTO exp_date
1481 FROM fv_treasury_symbols
1482 WHERE treasury_symbol_id = g_treasury_symbol_id;
1483
1484 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1485 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Expiration date for treasury id '||g_treasury_symbol_id||' is '||exp_date);
1486 END IF;
1487 -- fnd_file.put_line(fnd_file.log , 'exp_date ::' || exp_date);
1488
1489 IF (exp_date IS NOT NULL) THEN
1490 SELECT start_date,
1491 end_date
1492 INTO beg_date,
1493 close_date
1494 FROM gl_period_statuses
1495 WHERE period_year = g_period_year
1496 AND period_num = g_period_num
1497 AND application_id =101
1498 AND set_of_books_id = g_ledger_id;
1499
1500 IF(exp_date <= close_date) THEN
1501 whether_Exp := 'E';
1502 ELSE
1503 whether_Exp := 'U';
1504 END IF;
1505
1506 ELSE
1507 whether_Exp := 'U';
1508 END IF;
1509 -- fnd_file.put_line(fnd_file.log , 'whether_Exp ::' || whether_Exp);
1510
1511 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1512 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' Expired / Unexpired -'||whether_Exp);
1513 END IF;
1514
1515 SELECT COUNT(*)
1516 INTO l_temp_count
1517 FROM fv_facts_temp tmp,
1518 fv_facts2_closing_validation clos
1519 WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
1520 AND tmp.fct_int_record_category='REPORTED_NEW'
1521 AND tmp.fct_int_record_type ='BLK_DTL'
1522 AND tmp.sgl_acct_number = clos.ussgl_account
1523 AND clos.closing_grp IS NOT NULL
1524 AND clos.closing_acct_flag ='Y'
1525 AND tmp.begin_end = 'B';
1526
1527 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_temp_count ::'||l_temp_count) ;
1528 -- Check whether the hard edit check 13 checkbox selected in Federal Financial options
1529 -- Form
1530 BEGIN
1531 SELECT hard_edit_13_flag
1532 INTO flg
1533 FROM fv_facts2_Edit_params
1534 WHERE set_of_books_id = g_ledger_id;
1535 EXCEPTION
1536 WHEN NO_DATA_FOUND THEN
1537 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'No data found for hard edit check flag for period year-'||g_period_year,v_log_text);
1538 flg:='N';
1539 END;
1540
1541 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1542 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' Is hard edit 13 flag on Federal financial options form selected ? '||flg);
1543 END IF;
1544 --fnd_file.put_line(fnd_file.log , 'flg ::' || flg);
1545 /* Iterating over the closing_act_c and calculating the begining bal and end balance
1546 * Also populating the FV_FACTS_TEMP table.
1547 * Report will use this table to show data.
1548 */
1549 FOR get_all_closing_rec IN get_all_closing_acct
1550 LOOP
1551
1552 l_beg_bal := 0;
1553 v_amount1 := l_beg_bal;
1554 l_control_acct_num := get_all_closing_rec.ussgl_account;
1555 l_closing_gp := get_all_closing_rec.closing_grp;
1556 v_beg_bal_sggl_acc :=l_control_acct_num;
1557 v_closing_grp := l_closing_gp;
1558
1559 --fnd_file.put_line(fnd_file.log , 'beginning sgl account number ::Begining balance ::l_auth_type::l_closing_gp' || l_control_acct_num||
1560 --'::'||l_beg_bal||'::'||l_auth_type||'::'||l_closing_gp);
1561
1562 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1563 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' l_control_acct_num ::l_closing_gp -- '|| l_control_acct_num||
1564 '::'||l_closing_gp);
1565 END IF;
1566
1567 -- Retreiving the values for a beginning balances for the current year
1568 -- For a closing group
1569 OPEN closing_acct_c(l_closing_gp);
1570 IF closing_acct_c%NOTFOUND then
1571 l_beg_bal :=0;
1572 v_amount1 :=l_beg_bal;
1573 l_auth_type :=null;
1574 l_unexp_exp :=null;
1575 ELSE
1576 LOOP
1577 FETCH closing_acct_c INTO l_beg_bal,l_auth_type;
1578 v_amount1 :=l_beg_bal;
1579 EXIT WHEN closing_acct_c%NOTFOUND;
1580 END LOOP;
1581 END IF;
1582 CLOSE closing_acct_c;
1583
1584 v_sgl_acct_number:='';
1585 sum_ending_bal :=0;
1586 FOR end_bal_rec IN end_bal_cur(whether_Exp,l_closing_gp)
1587 LOOP
1588 l_facts_insert_flg:='N';
1589 IF (l_auth_type IS NOT NULL) THEN
1590 IF(l_auth_type =end_bal_rec.authority_type) THEN
1591 sum_ending_bal:=sum_ending_bal + end_bal_rec.ending_bal;
1592 l_facts_insert_flg:='Y';
1593 -- fnd_file.put_line(fnd_file.log ,'sum_ending_bal ::l_auth_type ::end_bal_rec.authority_type ::'||sum_ending_bal||'::'||l_auth_type||'::'||end_bal_rec.authority_type);
1594 END IF;
1595 ELSE
1599 END IF;
1596 sum_ending_bal:=sum_ending_bal + end_bal_rec.ending_bal;
1597 l_facts_insert_flg:='Y';
1598 --fnd_file.put_line(fnd_file.log ,'sum_ending_bal ::l_auth_type ::'||sum_ending_bal||':: null');
1600
1601 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1602 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' l_facts_insert_flg :: Total Ending Balance -- '|| l_facts_insert_flg||
1603 '::'||sum_ending_bal);
1604 END IF;
1605
1606 IF l_facts_insert_flg = 'Y' THEN
1607 v_sgl_acct_number:=end_bal_rec.ussgl_account;
1608 v_amount :=end_bal_rec.ending_bal;
1609 -- fnd_file.put_line(fnd_file.log ,'v_sgl_acct_number ::v_amount::'||v_sgl_acct_number||'::'||v_amount);
1610 create_log_record(v_log_text);
1611 END IF ;
1612 END LOOP;
1613
1614 /*
1615 * Comparing the ending balances of prior year and beginning balances of current year.
1616 * If the balances are not equal then status on FACTS II Submission form can be either
1617 * option edit checks failed or Required edit checks failed.It depends on the Hard edit
1618 * check flag selected on Federal Financial options forms
1619 */
1620 IF (sum_ending_bal <> l_beg_bal) THEN
1621 l_edit_check_status:='N';
1622 IF (flg ='Y' or g_error_flag = 2) THEN
1623 g_error_flag := 2; -- Hard edit failed, so bulk file cannot be generated
1624 ELSE
1625 g_error_flag :=1; -- Not a hard edit, so bulk file can be generated
1626 END IF;
1627 END IF;
1628
1629 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1630 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' Error flag(1- optional edit fail,2- failed,3-passed) :: Total Ending Balance -- '|| g_error_flag||
1631 '::'||sum_ending_bal);
1632 END IF;
1633 -- To display layout when no value inserted
1634 -- in to fv_facts_temp table.
1635 SELECT count(*) into l_has_data_count
1636 FROM fv_facts_temp
1637 WHERE treasury_symbol_id =g_treasury_symbol_id
1638 AND edit_check_number=13
1639 AND closing_grp =l_closing_gp;
1640
1641 IF (l_has_data_count=0) THEN
1642 v_sgl_acct_number:='';
1643 v_amount:=0;
1644 create_log_record(v_log_text);
1645 END IF;
1646
1647 -- fnd_file.put_line(fnd_file.log ,'v_edit_check_status ::g_error_flag::'||v_edit_check_status||'::'||g_error_flag);
1648 END LOOP;
1649
1650 IF l_edit_check_status='N' THEN
1651 v_edit_check_status := 'Failed';
1652 ELSE
1653 v_edit_check_status := 'Passed';
1654 END IF;
1655
1656 create_status_record(v_edit_check_number, v_edit_check_status);
1657 --fnd_file.put_line(fnd_file.log , '********************** EDIT CHEK 13 END ************');
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 v_log_text := 'Exception when others in Edit Check 13. SQLCODE: '|| SQLCODE ;
1661 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1662 v_log_text := 'SQLERRM: '|| SQLERRM ;
1663 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1664 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1665 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' Error flag(1- optional edit fail,2- failed,3-passed) :: Total Ending Balance -- '|| g_error_flag||
1666 '::'||sum_ending_bal);
1667 END IF;
1668 g_error_flag := 2;
1669 END edit_check_13;
1670
1671 PROCEDURE edit_check_14 IS
1672 l_control_acct_amt NUMBER := 0;
1673 control_sum NUMBER :=0;
1674 l_summation_acct_amt NUMBER := 0;
1675 summation_sum NUMBER :=0;
1676 flg varchar2(1);
1677
1678 CURSOR control_check1 IS
1679 SELECT nvl(amount,0) amt, facts.sgl_acct_number control_acct
1680 FROM fv_facts_temp facts, fv_facts_ussgl_accounts uss
1681 WHERE facts.treasury_symbol_id = g_treasury_symbol_id
1682 AND uss.ussgl_account = facts.sgl_acct_number
1683 AND uss.reclassification_ctrl_flag = 'Y' AND begin_end = 'E'
1684 AND amount <> 0 and facts.fct_int_record_category = 'REPORTED_NEW'
1685 AND facts.fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
1686
1687 CURSOR sum_check1 IS
1688 SELECT nvl(amount,0) amt, facts.sgl_acct_number summation_acct
1689 FROM fv_facts_temp facts, fv_facts_ussgl_accounts uss
1690 WHERE facts.treasury_symbol_id = g_treasury_symbol_id
1691 AND uss.ussgl_account = facts.sgl_acct_number
1692 AND uss.reclassification_sum_acc_flag = 'Y' AND begin_end = 'E'
1693 AND amount <> 0 and facts.fct_int_record_category = 'REPORTED_NEW'
1694 AND facts.fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
1695
1696 BEGIN
1697 init_vars;
1698 v_edit_check_number := 14;
1699 v_edit_check_number := 14.1;
1700 v_sgl_acct_number := null;
1701 for check1_rec in control_check1
1702 loop
1703 v_amount1:= check1_rec.amt;
1704 l_control_acct_amt := check1_rec.amt;
1705 v_sgl_acct_number := ' '||check1_rec.control_acct;
1706 control_sum := control_sum + l_control_acct_amt;
1707
1708 create_log_record(v_log_text);
1709 end loop;
1710 v_edit_check_number := 14;
1711 v_sgl_acct_number := null;
1712
1713 IF (control_sum <> 0) THEN
1714 for check2_rec in sum_check1
1715 loop
1716 v_amount1:= check2_rec.amt;
1717 l_summation_acct_amt := check2_rec.amt;
1718 v_sgl_acct_number := ' '||check2_rec.summation_acct;
1719 summation_sum := summation_sum + l_summation_acct_amt;
1720
1721 create_log_record(v_log_text);
1722 end loop;
1723 IF (abs(summation_sum) < abs(control_sum)) THEN
1724 v_edit_check_status := 'Failed';
1725
1726 select hard_edit_14_flag into flg from fv_facts2_Edit_params where set_of_books_id = g_ledger_id ;
1727
1728
1729 IF (flg ='Y' or g_error_flag = 2) THEN
1730 g_error_flag := 2; -- Hard edit failed, so bulk file cannot be generated
1731 ELSE
1735 v_edit_check_status := 'Passed';
1732 g_error_flag :=1; -- Not a hard edit, so bulk file can be generated
1733 END IF;
1734 ELSE
1736 END IF;
1737
1738 ELSE
1739 if(g_error_flag <> 2) then
1740 g_error_flag := 1; -- Need not perform edit check 14 if control account is 0
1741 end if;
1742 v_edit_check_status := 'Not Needed';
1743 END IF;
1744
1745 create_status_record(v_edit_check_number, v_edit_check_status) ;
1746 v_edit_check_number := 14.1;
1747 create_status_record(v_edit_check_number, v_edit_check_status) ;
1748
1749 EXCEPTION WHEN OTHERS THEN
1750 v_log_text := 'Exception when others in Edit Check 14. SQLCODE: '|| SQLCODE ;
1751 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1752
1753 v_log_text := 'SQLERRM: '|| SQLERRM ;
1754 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1755
1756 g_error_flag := 2;
1757 END; --edit_check_14
1758
1759 -- FACTS II Edit Checks ER. Bug 11683152.
1760 -- Edit Check 15.
1761 /*Ending balance of summary account 4157 must be < or = the Ending balance of
1762 summary account 4397.*/
1763 PROCEDURE edit_check_15 IS
1764 amt_4397 NUMBER;
1765 amt_4157 NUMBER;
1766 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_15';
1767
1768 CURSOR check_4157 IS
1769 SELECT nvl(amount,0) amt, sgl_acct_number acct
1770 FROM fv_facts_temp
1771 WHERE treasury_symbol_id = g_treasury_symbol_id
1772 AND sgl_acct_number = '4157'
1773 AND begin_end = 'E'
1774 AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
1775 AND fct_int_record_type = 'BLK_DTL';
1776
1777 CURSOR check_4397 IS
1778 SELECT nvl(amount,0) amt, sgl_acct_number acct
1779 FROM fv_facts_temp
1780 WHERE treasury_symbol_id = g_treasury_symbol_id
1781 AND sgl_acct_number = '4397'
1782 AND begin_end = 'E'
1783 AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
1784 AND fct_int_record_type = 'BLK_DTL';
1785
1786 BEGIN
1787 init_vars;
1788 v_edit_check_number := 15;
1789 v_sgl_acct_number := NULL;
1790 g_error_flag := 0;
1791
1792 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1793 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check 15');
1794 END IF;
1795
1796 BEGIN
1797 FOR check_15_rec1 IN check_4397 LOOP
1798 v_amount1 := check_15_rec1.amt;
1799 --preprended 5 so that 4397 record appears above the 4157 record in the layout
1800 v_sgl_acct_number :='54397';
1801 amt_4397 := v_amount1;
1802 create_log_record(v_log_text);
1803 END LOOP;
1804 EXCEPTION
1805 WHEN NO_DATA_FOUND THEN
1806 NULL;
1807 END;
1808
1809 BEGIN
1810 FOR check_15_rec IN check_4157 LOOP
1811 v_amount1 := check_15_rec.amt;
1812 -- Prepended 6 (so that 4157 record appears below 4397) as per layout in report(FVFCCHKR.rdf)
1813 -- used by this edit check.
1814 -- Use same frame as that used for displaying Edit check 8.
1815 v_sgl_acct_number :='64157';
1816 create_log_record(v_log_text);
1817 amt_4157 := v_amount1;
1818 END LOOP;
1819 EXCEPTION
1820 WHEN NO_DATA_FOUND THEN
1821 NULL;
1822 END;
1823
1824 IF amt_4397 IS NOT NULL AND amt_4157 IS NOT NULL THEN
1825 -- Bug 11832872: 4157 is a credit account and 4397 is a debit account
1826 IF (amt_4397 + amt_4157 <= amt_4157) THEN
1827 v_edit_check_status := 'Passed';
1828 ELSE
1829 v_edit_check_status := 'Failed';
1830 g_error_flag := 2; --Hard Edit Failed so bulk file cannot be created
1831 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1832 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Hard edit failed for edit check 15');
1833 END IF;
1834 END IF;
1835 v_sgl_acct_number := '7Difference (Should be <= zero)';
1836 v_amount1 := (amt_4397 + amt_4157);
1837 create_log_record(v_log_text);
1838 ELSE
1839 v_edit_check_status := 'Not needed';
1840 END IF;
1841 create_status_record(v_edit_check_number, v_edit_check_status) ;
1842 EXCEPTION WHEN OTHERS THEN
1843 v_log_text := 'Exception when others in Edit Check 15. SQLCODE: '|| SQLCODE ;
1844 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1845 v_log_text := 'SQLERRM: '|| SQLERRM ;
1846 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1847 g_error_flag := 2;
1848 END; --edit_check_15
1849
1850 -- Edit Check 16.
1851 /*Ending balance of summary account 4158 must be < or = the Ending balance of
1852 summary account 4398.*/
1853 PROCEDURE edit_check_16 IS
1854 amt_4398 NUMBER;
1855 amt_4158 NUMBER;
1856 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_16';
1857
1858 CURSOR check_4158 IS
1859 SELECT nvl(amount,0) amt, sgl_acct_number acct
1860 FROM fv_facts_temp
1861 WHERE treasury_symbol_id = g_treasury_symbol_id
1862 AND sgl_acct_number = '4158'
1863 AND begin_end = 'E'
1864 AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
1865 AND fct_int_record_type = 'BLK_DTL';
1866
1867 CURSOR check_4398 IS
1868 SELECT nvl(amount,0) amt, sgl_acct_number acct
1869 FROM fv_facts_temp
1870 WHERE treasury_symbol_id = g_treasury_symbol_id
1871 AND sgl_acct_number = '4398'
1872 AND begin_end = 'E'
1873 AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
1874 AND fct_int_record_type = 'BLK_DTL';
1875
1876 BEGIN
1877 init_vars;
1878 v_edit_check_number := 16;
1879 v_sgl_acct_number := NULL;
1880 g_error_flag := 0;
1881
1882 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1886 BEGIN
1883 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check 16');
1884 END IF;
1885
1887 FOR check_16_rec1 IN check_4398 LOOP
1888 v_amount1 := check_16_rec1.amt;
1889 --preprended 5 so that 4398 record appears above the 4158 record in the layout
1890 v_sgl_acct_number :='54398';
1891 amt_4398 := v_amount1;
1892 create_log_record(v_log_text);
1893 END LOOP;
1894 EXCEPTION
1895 WHEN NO_DATA_FOUND THEN
1896 NULL;
1897 END;
1898
1899 BEGIN
1900 FOR check_16_rec IN check_4158 LOOP
1901 v_amount1 := check_16_rec.amt;
1902 -- Prepended 6 (so that 4158 record appears below 4398 as per layout in report(FVFCCHKR.rdf)
1903 -- used by this edit check.
1904 -- Use same frame as that used for displaying Edit check 8
1905 v_sgl_acct_number :='64158';
1906 amt_4158 := v_amount1;
1907 create_log_record(v_log_text);
1908 END LOOP;
1909 EXCEPTION
1910 WHEN NO_DATA_FOUND THEN
1911 NULL;
1912 END;
1913
1914 IF amt_4398 IS NOT NULL AND amt_4158 IS NOT NULL THEN
1915 -- Bug 11832872: 4158 is a credit account and 4398 is a debit account
1916 IF (amt_4398 + amt_4158 <= 0) THEN
1917 v_edit_check_status := 'Passed';
1918 ELSE
1919 v_edit_check_status := 'Failed';
1920 g_error_flag := 2; --Hard Edit Failed so bulk file cannot be created
1921 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1922 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Hard edit failed for edit check 16');
1923 END IF;
1924 END IF;
1925 v_sgl_acct_number := '7Difference (Should be <= zero)';
1926 v_amount1 := (amt_4398 + amt_4158);
1927 create_log_record(v_log_text);
1928 ELSE
1929 v_edit_check_status := 'Not Needed';
1930 END IF;
1931 create_status_record(v_edit_check_number, v_edit_check_status) ;
1932
1933 EXCEPTION WHEN OTHERS THEN
1934 v_log_text := 'Exception when others in Edit Check 16. SQLCODE: '|| SQLCODE ;
1935 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1936 v_log_text := 'SQLERRM: '|| SQLERRM ;
1937 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
1938 g_error_flag := 2;
1939 END; --edit_check_16
1940
1941 -- Edit Check 17
1942 /*Account 4134 and/or 4144 cannot exceed the amount in USSGL 4871/4971 account*/
1943 PROCEDURE edit_check_17 IS
1944 chk_17_1_sum NUMBER := 0;
1945 chk_17_2_sum NUMBER := 0;
1946 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_17';
1947
1948 CURSOR chk17_1_4871_4971 IS
1949 SELECT nvl(amount,0) amt , sgl_acct_number
1950 FROM fv_facts_temp
1951 WHERE treasury_symbol_id = g_treasury_symbol_id
1952 AND sgl_acct_number in (4871, 4971)
1953 AND begin_end = 'E'
1954 AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
1955 AND fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
1956
1957 CURSOR chk17_2_4134_4144 IS
1958 SELECT nvl(amount,0) amt , sgl_acct_number
1959 FROM fv_facts_temp
1960 WHERE treasury_symbol_id = g_treasury_symbol_id
1961 AND sgl_acct_number in (4134, 4144)
1962 AND begin_end = 'E'
1963 AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
1964 AND fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
1965
1966 BEGIN
1967 init_vars;
1968 v_edit_check_number := 17;
1969 v_sgl_acct_number := NULL;
1970 chk_17_1_sum := NULL;
1971 g_error_flag := 0;
1972
1973 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1974 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check 17');
1975 END IF;
1976
1977 FOR check1_rec IN chk17_1_4871_4971
1978 LOOP
1979 v_amount1 := check1_rec.amt;
1980 v_sgl_acct_number := ' '||check1_rec.sgl_acct_number;
1981 IF chk_17_1_sum IS NOT NULL then
1982 chk_17_1_sum := chk_17_1_sum + v_amount1;
1983 ELSE
1984 chk_17_1_sum := v_amount1;
1985 END if;
1986 create_log_record(v_log_text);
1987 END LOOP;
1988
1989 v_amount1 := 0;
1990 IF chk_17_1_sum IS NOT NULL THEN
1991 v_edit_check_number := 17.1;
1992 v_sgl_acct_number := NULL;
1993
1994 FOR check2_rec IN chk17_2_4134_4144
1995 LOOP
1996 v_amount1:= check2_rec.amt;
1997 v_sgl_acct_number := ' '||check2_rec.sgl_acct_number;
1998 IF chk_17_1_sum IS NOT NULL then
1999 chk_17_2_sum := chk_17_2_sum + v_amount1;
2000 ELSE
2001 chk_17_2_sum := v_amount1;
2002 END if;
2003 create_log_record(v_log_text);
2004 END LOOP;
2005 v_edit_check_number := 17.2;
2006 v_sgl_acct_number := '6Difference (Should be >= zero)';
2007 v_amount1 := (chk_17_1_sum + chk_17_2_sum);
2008 create_log_record(v_log_text);
2009
2010 -- Bug 11832872: 4134 and 4144 are credit account and 4871 and 4971 are debit accounts
2011 IF (chk_17_1_sum + chk_17_2_sum >= 0 ) THEN
2012 v_edit_check_status := 'Passed';
2013 ELSIF (chk_17_2_sum = 0 ) THEN
2014 v_edit_check_status := 'Failed';
2015 g_error_flag := 2; --Hard Edit Failed so bulk file cannot be created
2016 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2017 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Hard edit failed for edit check 17');
2018 END IF;
2019 ELSE
2020 v_edit_check_status := 'Failed';
2021 g_error_flag := 2; --Hard Edit Failed so bulk file cannot be created
2022 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2023 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Hard edit failed for edit check 17');
2024 END IF;
2025 END IF;
2029 v_edit_check_number := 17;
2026 ELSE
2027 v_edit_check_status := 'Not Needed';
2028 END IF;
2030 create_status_record(v_edit_check_number, v_edit_check_status) ;
2031 v_edit_check_number := 17.1;
2032 create_status_record(v_edit_check_number, v_edit_check_status) ;
2033 /*Added to display "Difference (Should be > 0)" at the bottom on the report*/
2034 v_edit_check_number := 17.2;
2035 create_status_record(v_edit_check_number, v_edit_check_status) ;
2036
2037 EXCEPTION WHEN OTHERS THEN
2038 v_log_text := 'Exception when others in Edit Check 17. SQLCODE: '|| SQLCODE ;
2039 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2040 v_log_text := 'SQLERRM: '|| SQLERRM ;
2041 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2042 g_error_flag := 2;
2043 END; --edit_check_17
2044
2045 -- Edit Check Non Federal Exception
2046 /*Display when USSGL summary account is 4221, 4251, 4230, 4233 or 4212 and the
2047 Posted Transaction Partner is E when the TASF for the transaction does not have
2048 the non Federal exemption checkbox checked on Define Treasury Account Symbols form*/
2049 PROCEDURE edit_check_nfe IS
2050 v_found_flag boolean DEFAULT FALSE;
2051 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_NFE';
2052
2053 CURSOR check_18 IS
2054 SELECT nvl(amount,0) amt , sgl_acct_number, transaction_partner,
2055 treasury_dept_code, treasury_acct_code, decode(fffa.non_fed_exc_flag,'Y', 'Allowed','Not Allowed')non_fed_exc_flag_desc
2056 FROM fv_facts_temp facts, fv_facts_federal_accounts fffa, fv_treasury_symbols fts
2057 WHERE facts.treasury_symbol_id = g_treasury_symbol_id
2058 AND facts.treasury_symbol_id = fts.treasury_symbol_id
2059 AND fts.federal_Acct_symbol_id = fffa.federal_Acct_symbol_id
2060 AND ((transaction_partner = 'E'
2061 AND (fffa.NON_FED_EXC_FLAG IS NULL OR fffa.NON_FED_EXC_FLAG ='N')
2062 AND sgl_acct_number IN (4221, 4251, 4230, 4233, 4212))
2063 OR (transaction_partner = 'X'
2064 AND sgl_acct_number IN (4221, 4251, 4230, 4233)) )
2065 AND begin_end = 'E'
2066 AND amount <> 0
2067 AND fct_int_record_category = 'REPORTED_NEW'
2068 AND fct_int_record_type = 'BLK_DTL'
2069 ORDER BY sgl_acct_number;
2070
2071 BEGIN
2072 init_vars;
2073 v_edit_check_number := 17.9;
2074 v_sgl_acct_number := NULL;
2075 g_error_flag := 0;
2076
2077 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2078 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check Non Federal Exception');
2079 END IF;
2080
2081 FOR check_18_rec IN check_18
2082 LOOP
2083 v_found_flag := TRUE;
2084 v_amount1 := check_18_rec.amt;
2085 v_sgl_acct_number := ' '||check_18_rec.sgl_acct_number;
2086 v_transaction_partner := check_18_rec.transaction_partner;
2087 v_aid := check_18_rec.treasury_dept_code;
2088 v_main := check_18_rec.treasury_acct_code;
2089 v_non_fed_exc_flag := check_18_rec.non_fed_exc_flag_desc;
2090 create_log_record(v_log_text);
2091 END LOOP;
2092
2093 IF v_found_flag = TRUE THEN
2094 v_edit_check_status := 'Failed';
2095 ELSE
2096 v_edit_check_status := 'Passed';
2097 END IF;
2098
2099 v_edit_check_number := 17.9;
2100 create_status_record(v_edit_check_number, v_edit_check_status) ;
2101
2102 EXCEPTION WHEN OTHERS THEN
2103 v_log_text := 'Exception when others in Edit Check NFE SQLCODE: '|| SQLCODE ;
2104 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2105
2106 v_log_text := 'SQLERRM: '|| SQLERRM ;
2107 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2108
2109 g_error_flag := 2;
2110 END; --edit_check_nfe
2111
2112 -- Bug 12985834: FACTS II 2012 enhancement
2113 -- Edit Check 18 ensures that the sum of the USSGL account balances that
2114 -- comprise each of the SF 133 lines is greater or equal to zero
2115 PROCEDURE edit_check_18 IS
2116
2117 l_1700_status NUMBER := 0;
2118 l_1800_status NUMBER := 0;
2119 l_2004_status NUMBER := 0;
2120 l_2104_status NUMBER := 0;
2121 l_combined_status NUMBER := 0;
2122 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_18';
2123
2124 BEGIN
2125 init_vars;
2126 g_error_flag := 0;
2127
2128 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2129 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check 18');
2130 END IF;
2131
2132 /*Calculation for SF 133 Line 1700:
2133 1)Accounts with 1700, 1800 checkbox checked on SF 133 Line Attribute Tab
2134 of US SGL Accounts Form and
2135 2)Accounts that have a BEA Category Code = D and PYA = X and
2136 3)Accounts with Begin/End of Ending or Ending-Beginning on SF 133 Line
2137 Attribute Tab of the US SGL Accounts Form and
2138 4)Accounts with Expired/Unexpired of Unexpired or Unexpired/Expired
2139 on SF 133 Line Attribute Tab of the US SGL Accounts Form.
2140
2141 If the resulting total is >= 0 the edit passes,Otherwise it fails
2142 */
2143 v_edit_check_number := 18.1;
2144 v_amount1 := NULL;
2145
2146 SELECT sum(amount) amt INTO v_amount1
2147 FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
2148 WHERE treasury_symbol_id = g_treasury_symbol_id
2149 AND trim(bea_category) = 'D'
2150 AND pya_flag = 'X'
2151 AND temp.sgl_acct_number = ussgl.ussgl_account
2152 AND ussgl.SF133_1700_1800 = 'Y'
2153 AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
2154 AND (ussgl.UNEXP_EXP ='U' OR ussgl.UNEXP_EXP ='UE')
2155 AND amount <> 0
2156 AND fct_int_record_category = 'REPORTED_NEW'
2157 AND fct_int_record_type = 'BLK_DTL';
2158
2159 IF v_amount1 IS NOT NULL THEN
2160 v_sgl_acct_number := ' '||1700;
2161 create_log_record(v_log_text);
2165 l_1700_status := 5;
2162 IF v_amount1 >= 0 THEN
2163 l_1700_status := 1;
2164 ELSE
2166 END IF;
2167 END IF;
2168
2169 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2170 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2171 'Line 1700 status:'|| l_1700_status);
2172 END IF;
2173
2174 /*Calculation for SF 133 Line 1800:
2175 1)Accounts with 1700, 1800 checkbox checked on SF 133 Line Attribute Tab
2176 of US SGL Accounts Form and
2177 2)Accounts that have a BEA Category Code = M and PYA = X and
2178 3)Accounts with Begin/End of Ending or Ending-Beginning on SF 133 Line
2179 Attribute Tab of the US SGL Accounts Form and
2180 4)Accounts with Expired/Unexpired of Unexpired or Unexpired/Expired
2181 on SF 133 Line Attribute Tab of the US SGL Accounts Form.
2182
2183 If the resulting total is >= 0 the edit passes,Otherwise it fails
2184 */
2185 v_edit_check_number := 18.2;
2186
2187 SELECT sum(amount) amt INTO v_amount1
2188 FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
2189 WHERE treasury_symbol_id = g_treasury_symbol_id
2190 AND trim(bea_category) = 'M'
2191 AND pya_flag = 'X'
2192 AND temp.sgl_acct_number = ussgl.ussgl_account
2193 AND ussgl.SF133_1700_1800 = 'Y'
2194 AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
2195 AND (ussgl.UNEXP_EXP ='U' OR ussgl.UNEXP_EXP ='UE')
2196 AND amount <> 0
2197 AND fct_int_record_category = 'REPORTED_NEW'
2198 AND fct_int_record_type = 'BLK_DTL' ;
2199
2200 IF v_amount1 IS NOT NULL THEN
2201 v_sgl_acct_number := ' '||1800;
2202 create_log_record(v_log_text);
2203 IF v_amount1 >= 0 THEN
2204 l_1800_status := 1;
2205 ELSE
2206 l_1800_status := 5;
2207 END IF;
2208 END IF;
2209
2210 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2211 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2212 'Line 1800 status:'|| l_1800_status);
2213 END IF;
2214
2215 /*Calculation for SF 133 Line 2004:
2216 1)Accounts with 2004, 2104 checkbox checked on SF 133 Line Attribute Tab
2217 of US SGL Accounts Form and
2218 2)Accounts with Direct or Reimbursable code = D and PYA = X and
2219 3)Accounts with Begin/End of Ending or Ending-Beginning on SF 133 Line
2220 Attribute Tab of the US SGL Accounts Form and
2221 4)Accounts with Expired/Unexpired of Unexpired or Unexpired/Expired
2222 on SF 133 Line Attribute Tab of the US SGL Accounts Form and
2223 5)Accounts with Expired/Unexpired of Unexpired, Fund Attribute
2224 Apportionment Category Code should be = A, C or E.
2225
2226 If the resulting total is <= 0 the edit passes,Otherwise it fails
2227 */
2228 v_edit_check_number := 18.3;
2229
2230 SELECT sum(amount) amt INTO v_amount1
2231 FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
2232 WHERE treasury_symbol_id = g_treasury_symbol_id
2233 AND trim(REIMBURSEABLE_FLAG) = 'D'
2234 AND pya_flag = 'X'
2235 AND temp.sgl_acct_number = ussgl.ussgl_account
2236 AND ussgl.SF133_2004_2104 = 'Y'
2237 AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
2238 AND (ussgl.UNEXP_EXP ='UE' OR
2239 (ussgl.UNEXP_EXP ='U' AND temp.APPOR_CAT_CODE IN('A','C','E')))
2240 AND amount <> 0
2241 AND fct_int_record_category = 'REPORTED_NEW'
2242 AND fct_int_record_type = 'BLK_DTL';
2243
2244 IF v_amount1 IS NOT NULL THEN
2245 v_sgl_acct_number := ' '||2004;
2246 create_log_record(v_log_text);
2247 IF v_amount1 <= 0 THEN
2248 l_2004_status := 1;
2249 ELSE
2250 l_2004_status := 5;
2251 END IF;
2252 END IF;
2253
2254 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2255 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2256 'Line 2004 status:'|| l_2004_status);
2257 END IF;
2258
2259 /*Calculation for SF 133 Line 2004:
2260 1)Accounts with 2004, 2104 checkbox checked on SF 133 Line Attribute Tab
2261 of US SGL Accounts Form and
2262 2)Accounts with Direct or Reimbursable code = R and PYA = X and
2263 3)Accounts with Begin/End of Ending or Ending-Beginning on SF 133 Line
2264 Attribute Tab of the US SGL Accounts Form and
2265 4)Accounts with Expired/Unexpired of Unexpired or Unexpired/Expired
2266 on SF 133 Line Attribute Tab of the US SGL Accounts Form and
2267 5)Accounts with Expired/Unexpired of Unexpired, Fund Attribute
2268 Apportionment Category Code should be = A, C or E.
2269
2270 If the resulting total is <= 0 the edit passes,Otherwise it fails
2271 */
2272 v_edit_check_number := 18.4;
2273
2274 SELECT sum(amount) amt INTO v_amount1
2275 FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
2276 WHERE treasury_symbol_id = g_treasury_symbol_id
2277 AND trim(REIMBURSEABLE_FLAG) = 'R'
2278 AND pya_flag = 'X'
2279 AND temp.sgl_acct_number = ussgl.ussgl_account
2280 AND ussgl.SF133_2004_2104 = 'Y'
2281 AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
2282 AND (ussgl.UNEXP_EXP ='UE' OR
2283 (ussgl.UNEXP_EXP ='U' AND temp.APPOR_CAT_CODE IN('A','C','E')))
2284 AND amount <> 0
2285 AND fct_int_record_category = 'REPORTED_NEW'
2286 AND fct_int_record_type = 'BLK_DTL';
2287
2288 IF v_amount1 IS NOT NULL THEN
2289 v_sgl_acct_number := ' '||2104;
2290 create_log_record(v_log_text);
2291 IF v_amount1 <= 0 THEN
2292 l_2104_status := 1;
2293 ELSE
2294 l_2104_status := 5;
2295 END IF;
2296 END IF;
2297
2298 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2302
2299 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2300 'Line 2104 status:'|| l_2104_status);
2301 END IF;
2303 -- Value of an individual sub edit status is 0,1,5 when status is not
2304 -- applicable(no entries),pass or fail.
2305 l_combined_status := l_1700_status + l_1800_status + l_2004_status
2306 + l_2104_status;
2307
2308 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2309 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2310 'Combined status:'|| l_combined_status);
2311 END IF;
2312
2313 IF l_combined_status = 0 THEN
2314 v_edit_check_status := 'Not Applicable';
2315 ELSIF l_combined_status >= 5 THEN
2316 v_edit_check_status := 'Failed';
2317 g_error_flag := 2;
2318 ELSE
2319 v_edit_check_status := 'Passed';
2320 END IF;
2321
2322 v_edit_check_number := 18;
2323 create_status_record(v_edit_check_number, v_edit_check_status) ;
2324
2325 -- Insert status records for 18.1, 18.2, 18.3 and 18.4 so that Line 1700, Line 1800
2326 -- Line 2004 and Line 2104 respectively display on the report even if they
2327 -- have no records in them.
2328 v_edit_check_number := 18.1;
2329 create_status_record(v_edit_check_number, v_edit_check_status) ;
2330 v_edit_check_number := 18.2;
2331 create_status_record(v_edit_check_number, v_edit_check_status) ;
2332 v_edit_check_number := 18.3;
2333 create_status_record(v_edit_check_number, v_edit_check_status) ;
2334 v_edit_check_number := 18.4;
2335 create_status_record(v_edit_check_number, v_edit_check_status) ;
2336
2337 EXCEPTION WHEN OTHERS THEN
2338 v_log_text := 'Exception when others in Edit Check 18. SQLCODE: '|| SQLCODE ;
2339 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2340 v_log_text := 'SQLERRM: '|| SQLERRM ;
2341 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2342 g_error_flag := 2;
2343 END; --edit_check_18
2344
2345 -- Bug 12985834: FACTS II 2012 enhancement
2346 -- Edit Check 19 ensures that PYA Attribute "B:Backdated Adjustments to prior-year
2347 -- reporting" debits = credits for each TAS, and PYA Attribute "P:Non-Backdated
2348 -- Adjustments to prior-year reporting" debits = credits for each TAS.
2349 PROCEDURE edit_check_19 IS
2350
2351 l_pyab_bud_status NUMBER := 0;
2352 l_pyab_prop_status NUMBER := 0;
2353 l_pyap_bud_status NUMBER := 0;
2354 l_pyap_prop_status NUMBER := 0;
2355 l_sum_amount NUMBER := 0;
2356 l_combined_status NUMBER := 0;
2357
2358 CURSOR check19_bud(pya_val VARCHAR2) IS
2359 SELECT sgl_acct_number, amount
2360 FROM fv_facts_temp
2361 WHERE treasury_symbol_id = g_treasury_symbol_id
2362 AND fct_int_record_category = 'REPORTED_NEW'
2363 AND fct_int_record_type = 'BLK_DTL'
2364 AND pya_flag = pya_val
2365 AND begin_end = 'E'
2366 AND amount <> 0
2367 AND sgl_acct_number LIKE '4%'
2368 ORDER BY sgl_acct_number;
2369
2370 CURSOR check19_prop(pya_val VARCHAR2) IS
2371 SELECT sgl_acct_number, amount
2372 FROM fv_facts_temp
2373 WHERE treasury_symbol_id = g_treasury_symbol_id
2374 AND fct_int_record_category = 'REPORTED_NEW'
2375 AND fct_int_record_type = 'BLK_DTL'
2376 AND pya_flag = pya_val
2377 AND begin_end = 'E'
2378 AND amount <> 0
2379 AND sgl_acct_number NOT LIKE '4%'
2380 ORDER BY sgl_acct_number;
2381
2382 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_19';
2383
2384 BEGIN
2385 init_vars;
2386 g_error_flag := 0;
2387
2388 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2389 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check 19');
2390 END IF;
2391
2392 /*For PYA = "B:Backdated Adjustments to prior-year reporting",
2393 Sum of the ending balance of the budgetary accounts must equal "0".
2394 If the sum of the ending balances does not equal "0", edit fails and the
2395 FACTS II file will not be generated.
2396 */
2397 v_edit_check_number := 19.1;
2398 v_amount1 := NULL;
2399
2400 FOR check_19_brec IN check19_bud('B')
2401 LOOP
2402 v_amount1 := check_19_brec.amount;
2403 v_sgl_acct_number := ' '||check_19_brec.sgl_acct_number;
2404 create_log_record(v_log_text);
2405 l_sum_amount := l_sum_amount + v_amount1;
2406 END LOOP;
2407
2408 IF v_amount1 IS NOT NULL THEN
2409 IF l_sum_amount = 0 THEN
2410 l_pyab_bud_status := 1;
2411 ELSE
2412 l_pyab_bud_status := 5;
2413 END IF;
2414 ELSE
2415 l_pyab_bud_status := 0;
2416 END IF;
2417
2418 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2419 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2420 'PYA = B, Budgetary accounts- status:'|| l_pyab_bud_status);
2421 END IF;
2422
2423 /*For PYA = "B:Backdated Adjustments to prior-year reporting",
2424 Sum of the ending balance of the proprietary accounts must equal "0".
2425 If the sum of the ending balances does not equal "0", edit fails and the
2426 FACTS II file will not be generated.
2427 */
2428 v_edit_check_number := 19.2;
2429 v_amount1 := NULL;
2430 l_sum_amount := 0;
2431 FOR check_19_prec IN check19_prop('B')
2432 LOOP
2433 v_amount1 := check_19_prec.amount;
2434 v_sgl_acct_number := ' ' || check_19_prec.sgl_acct_number;
2435 create_log_record(v_log_text);
2436 l_sum_amount := l_sum_amount + v_amount1;
2437 END LOOP;
2438
2439 IF v_amount1 IS NOT NULL THEN
2440 IF l_sum_amount = 0 THEN
2441 l_pyab_prop_status := 1;
2442 ELSE
2443 l_pyab_prop_status := 5;
2447 END IF;
2444 END IF;
2445 ELSE
2446 l_pyab_prop_status := 0;
2448
2449 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2450 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2451 'PYA = B, Proprietary accounts- status:'|| l_pyab_prop_status);
2452 END IF;
2453
2454 /*For PYA = "P:Non-Backdated Adjustments to prior-year reporting",
2455 Sum of the ending balance of the budgetary accounts must equal "0".
2456 If the sum of the ending balances does not equal "0", edit fails and the
2457 FACTS II file will not be generated.
2458 */
2459 v_edit_check_number := 19.3;
2460 v_amount1 := NULL;
2461 l_sum_amount := 0;
2462 FOR check_19_brec IN check19_bud('P')
2463 LOOP
2464 v_amount1 := check_19_brec.amount;
2465 v_sgl_acct_number := ' ' || check_19_brec.sgl_acct_number;
2466 create_log_record(v_log_text);
2467 l_sum_amount := l_sum_amount + v_amount1;
2468 END LOOP;
2469
2470 IF v_amount1 IS NOT NULL THEN
2471 IF l_sum_amount = 0 THEN
2472 l_pyap_bud_status := 1;
2473 ELSE
2474 l_pyap_bud_status := 5;
2475 END IF;
2476 ELSE
2477 l_pyap_bud_status := 0;
2478 END IF;
2479
2480 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2481 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2482 'PYA = P, Budgetary accounts- status:'|| l_pyap_bud_status);
2483 END IF;
2484
2485 /*For PYA = "P:Non-Backdated Adjustments to prior-year reporting",
2486 Sum of the ending balance of the proprietary accounts must equal "0".
2487 If the sum of the ending balances does not equal "0", edit fails and the
2488 FACTS II file will not be generated.
2489 */
2490 v_edit_check_number := 19.4;
2491 v_amount1 := NULL;
2492 FOR check_19_prec IN check19_prop('P')
2493 LOOP
2494 v_amount1 := check_19_prec.amount;
2495 v_sgl_acct_number := ' ' || check_19_prec.sgl_acct_number;
2496 create_log_record(v_log_text);
2497 l_sum_amount := l_sum_amount + v_amount1;
2498 END LOOP;
2499
2500 IF v_amount1 IS NOT NULL THEN
2501 IF l_sum_amount = 0 THEN
2502 l_pyap_prop_status := 1;
2503 ELSE
2504 l_pyap_prop_status := 5;
2505 END IF;
2506 ELSE
2507 l_pyap_prop_status := 0;
2508 END IF;
2509
2510 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2511 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2512 'PYA = P, Proprietary accounts- status:'|| l_pyap_prop_status);
2513 END IF;
2514
2515 -- Value of an individual sub edit status is 0,1,5 when status is not
2516 -- applicable(no entries),pass or fail.
2517 l_combined_status := l_pyab_bud_status + l_pyab_prop_status + l_pyap_bud_status
2518 + l_pyap_prop_status;
2519
2520 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2521 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2522 'Combined status:'|| l_combined_status);
2523 END IF;
2524
2525 IF l_combined_status = 0 THEN
2526 v_edit_check_status := 'Not Applicable';
2527 ELSIF l_combined_status >= 5 THEN
2528 v_edit_check_status := 'Failed';
2529 g_error_flag := 2;
2530 ELSE
2531 v_edit_check_status := 'Passed';
2532 END IF;
2533
2534 v_edit_check_number := 19;
2535 create_status_record(v_edit_check_number, v_edit_check_status) ;
2536
2537 -- Insert status records for 19.1, 19.2, 19.3 and 19.4 so that subedits
2538 -- (PYA B, Budgetary; PYA B, Proprietary; PYA P, Budgetary; PYA P, Proprierary)
2539 -- display on the report even if they have no records in them.
2540 v_edit_check_number := 19.1;
2541 create_status_record(v_edit_check_number, v_edit_check_status) ;
2542 v_edit_check_number := 19.2;
2543 create_status_record(v_edit_check_number, v_edit_check_status) ;
2544 v_edit_check_number := 19.3;
2545 create_status_record(v_edit_check_number, v_edit_check_status) ;
2546 v_edit_check_number := 19.4;
2547 create_status_record(v_edit_check_number, v_edit_check_status) ;
2548
2549 EXCEPTION WHEN OTHERS THEN
2550 v_log_text := 'Exception when others in Edit Check 19. SQLCODE: '|| SQLCODE ;
2551 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2552 v_log_text := 'SQLERRM: '|| SQLERRM ;
2553 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2554 g_error_flag := 2;
2555 END; --edit_check_19
2556
2557 -- Edit Check 20
2558 -- This edit ensures that USSGL accounts' 4201, 4139 and 4149 beginning balance is
2559 -- equal to ending balance for each FACTS II Adjusted Trial Balance.
2560 PROCEDURE edit_check_20 IS
2561
2562 l_status_4201 NUMBER := 0;
2563 l_status_4139 NUMBER := 0;
2564 l_status_4149 NUMBER := 0;
2565 l_combined_sum NUMBER := 0;
2566
2567 l_module_name VARCHAR2(200) := g_module_name || 'edit_check_20';
2568 p_acct_number FV_FACTS_TEMP.SGL_ACCT_NUMBER%TYPE;
2569
2570 CURSOR chk20 IS
2571 SELECT SUM(nvl(tempbeg.amount,0)) begamt , SUM( nvl(tempend.amount,0)) endamt,
2572 tempbeg.sgl_acct_number begsgl_acct_number, tempend.sgl_acct_number endsgl_acct_number
2573 FROM (SELECT amount, sgl_acct_number
2574 FROM fv_facts_temp
2575 WHERE treasury_symbol_id = g_treasury_symbol_id
2576 AND fct_int_record_category = 'REPORTED_NEW'
2577 AND fct_int_record_type = 'BLK_DTL'
2578 AND sgl_acct_number IN('4201','4139','4149')
2579 AND begin_end = 'B') tempbeg FULL OUTER JOIN
2580 (SELECT amount, sgl_acct_number
2581 FROM fv_facts_temp
2582 WHERE treasury_symbol_id = g_treasury_symbol_id
2583 AND fct_int_record_category = 'REPORTED_NEW'
2587 ON tempbeg.sgl_acct_number = tempend.sgl_acct_number
2584 AND fct_int_record_type = 'BLK_DTL'
2585 AND sgl_acct_number IN('4201','4139','4149')
2586 AND begin_end = 'E') tempend
2588 GROUP BY tempbeg.sgl_acct_number, tempend.sgl_acct_number
2589 ORDER BY tempbeg.sgl_acct_number, tempend.sgl_acct_number;
2590
2591 BEGIN
2592 init_vars;
2593 v_edit_check_number := 20;
2594 g_error_flag := 0;
2595
2596 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2597 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Begin edit check 20');
2598 END IF;
2599
2600 FOR rec IN chk20
2601 LOOP
2602 v_amount1 := rec.begamt;
2603 v_amount2 := rec.endamt;
2604 IF ( rec.begsgl_acct_number IS NOT NULL)THEN
2605 p_acct_number := rec.begsgl_acct_number;
2606 END IF;
2607
2608 IF ( rec.endsgl_acct_number IS NOT NULL)THEN
2609 p_acct_number := rec.endsgl_acct_number;
2610 END IF;
2611
2612 v_sgl_acct_number := ' '||p_acct_number;
2613 create_log_record(v_log_text);
2614
2615 IF (v_amount1 <> v_amount2)THEN
2616 IF p_acct_number = 4201 THEN
2617 l_status_4201 := 4;
2618 ELSIF p_acct_number = 4139 THEN
2619 l_status_4139 := 4;
2620 ELSIF p_acct_number = 4149 THEN
2621 l_status_4149 := 4;
2622 END IF;
2623 ELSE
2624 IF p_acct_number = 4201 THEN
2625 l_status_4201 := 1;
2626 ELSIF p_acct_number = 4139 THEN
2627 l_status_4139 := 1;
2628 ELSIF p_acct_number = 4149 THEN
2629 l_status_4149 := 1;
2630 END IF;
2631 END IF;
2632
2633 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2634 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Account: '||
2635 v_sgl_acct_number||' v_amount1: '||v_amount1||' v_amount2: '||v_amount2 );
2636 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'l_status_4201,
2637 l_status_4139, l_status_4149: '||l_status_4201||l_status_4139||l_status_4149);
2638 END IF;
2639
2640 END LOOP;
2641
2642 l_combined_sum := l_status_4201 + l_status_4139 + l_status_4149;
2643
2644 IF (l_combined_sum = 0) THEN
2645 v_edit_check_status := 'Not Applicable';
2646 ELSIF l_combined_sum >= 4 THEN
2647 v_edit_check_status := 'Failed';
2648 ELSE
2649 v_edit_check_status := 'Passed';
2650 END IF;
2651
2652 create_status_record(v_edit_check_number, v_edit_check_status) ;
2653
2654 EXCEPTION WHEN OTHERS THEN
2655 v_log_text := 'Exception when others in Edit Check 20. SQLCODE: '|| SQLCODE ;
2656 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2657 v_log_text := 'SQLERRM: '|| SQLERRM ;
2658 FND_FILE.PUT_LINE(FND_FILE.LOG, v_log_text) ;
2659 g_error_flag := 2;
2660 END; --edit_check_20
2661
2662
2663 -- This is the main procedure which calls all the edit check procedures
2664
2665 procedure perform_edit_checks (errbuf OUT NOCOPY varchar2,
2666 retcode OUT NOCOPY number,
2667 p_treasury_symbol_id IN number,
2668 p_facts_run_quarter IN number,
2669 p_rep_fiscal_yr IN NUMBER,
2670 p_period_num IN NUMBER,
2671 p_ledger_id IN NUMBER)
2672 is
2673 l_module_name VARCHAR2(200) := g_module_name || 'perform_edit_checks';
2674 -- Added to fix 1974485
2675 l_cancel_date NUMBER(4);
2676 no_rec NUMBER;
2677 l_subclass_prefix_code fv_treasury_symbols.sub_level_prefix_code%TYPE;
2678 hard_edit_failed_flg VARCHAR2(1) DEFAULT 'N';
2679
2680 begin
2681
2682 fnd_file.put_line(FND_file.LOG,'Running 7/24 debug version');
2683 g_error_flag := 0;
2684 g_treasury_symbol_id := p_treasury_symbol_id;
2685 g_ledger_id := p_ledger_id;
2686 g_period_num := p_period_num;
2687 g_period_year :=p_rep_fiscal_yr;
2688
2689 SELECT to_number(to_char(cancellation_date,'YYYY')),sub_level_prefix_code
2690 INTO l_cancel_date, l_subclass_prefix_code
2691 FROM fv_treasury_symbols
2692 WHERE treasury_symbol_id = g_treasury_symbol_id;
2693
2694 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2695 v_log_text := 'Edit Check process start...' ;
2696 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',v_log_text);
2697 v_log_text := ' ' ;
2698 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',v_log_text);
2699 END IF;
2700
2701 edit_check_1;
2702 edit_check_2;
2703 edit_check_3;
2704 edit_check_8;
2705
2706 IF NVL(l_subclass_prefix_code, '-XXX') = '43'
2707 THEN
2708 edit_check_9(p_facts_run_quarter);
2709 retcode := g_error_flag;
2710 IF (retcode = 2) THEN
2711 errbuf := 'Hard Edit Check Failed';
2712 v_log_text := 'Hard Edit Check Failed.' ;
2713 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error2',errbuf);
2714
2715 ELSE
2716 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2717 v_log_text := 'Edit Check Passed.' ;
2718 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2719 l_module_name||'.message4',v_log_text);
2720 END IF;
2721
2722 END IF;
2723 ELSE
2724 init_vars;
2725 v_edit_check_number := 9;
2726 v_edit_check_status := 'Not Applicable' ;
2727 create_status_record(v_edit_check_number, v_edit_check_status) ;
2728 END IF;
2729
2730 if (p_facts_run_quarter = 4) then
2731 edit_check_4;
2732 edit_check_5;
2733 edit_check_6 (p_ledger_id);
2734 edit_check_7;
2735
2739 THEN edit_check_10;
2736 -- Added to fix 1974485
2737
2738 IF l_cancel_date = p_rep_fiscal_yr
2740 ELSE
2741 init_vars;
2742
2743 v_edit_check_number := 10;
2744 v_edit_check_status := 'Not Applicable' ;
2745
2746 create_status_record(v_edit_check_number, v_edit_check_status) ;
2747 -- create_log_record(v_log_text);
2748
2749 END IF;
2750
2751 edit_check_11;
2752
2753 else
2754
2755 -- Changed log text for bug 2053780
2756
2757 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2758 v_log_text := 'Edit Checks 4,5,6,7,10 and 11 are not needed' ;
2759 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message2',v_log_text);
2760 END IF;
2761
2762 v_log_text := ' ';
2763 v_edit_check_status := 'Not Needed';
2764
2765 v_edit_check_number := 4;
2766 create_status_record(v_edit_check_number, v_edit_check_status) ;
2767
2768 v_edit_check_number := 5;
2769 create_status_record(v_edit_check_number, v_edit_check_status) ;
2770
2771 v_edit_check_number := 6;
2772 create_status_record(v_edit_check_number, v_edit_check_status) ;
2773
2774 v_edit_check_number := 7;
2775 create_status_record(v_edit_check_number, v_edit_check_status) ;
2776
2777 v_edit_check_number := 10;
2778 create_status_record(v_edit_check_number, v_edit_check_status) ;
2779
2780 v_edit_check_number := 11;
2781 create_status_record(v_edit_check_number, v_edit_check_status) ;
2782
2783 end if;
2784
2785 edit_check_12(p_facts_run_quarter);
2786
2787 SELECT COUNT(*)
2788 INTO no_rec
2789 FROM fv_facts2_edit_params
2790 WHERE set_of_books_id = p_ledger_id
2791 AND period_year = p_rep_fiscal_yr
2792 AND period_num = p_period_num;
2793
2794 edit_check_13;
2795
2796 -- FV FACTSII Minor Enhancements. Bug 11683152. Removed Edit Check 14.
2797 -- Added calls to edit_check_15, edit_check_16, edit_check_17 and edit_check_nfe.
2798
2799 /*IF(no_rec = 1) THEN
2800 edit_check_14;
2801 ELSE
2802 v_edit_check_status := 'Not Needed';
2803 v_edit_check_number := 14;
2804 create_status_record(v_edit_check_number, v_edit_check_status) ;
2805 v_edit_check_number := 14.1;
2806 create_status_record(v_edit_check_number, v_edit_check_status) ;
2807 END IF;*/
2808 IF (g_error_flag = 2) THEN
2809 hard_edit_failed_flg := 'Y';
2810 END IF;
2811
2812 edit_check_15;
2813 IF (g_error_flag = 2) THEN
2814 hard_edit_failed_flg := 'Y';
2815 END IF;
2816
2817 edit_check_16;
2818 IF (g_error_flag = 2) THEN
2819 hard_edit_failed_flg := 'Y';
2820 END IF;
2821
2822 edit_check_17;
2823 IF (g_error_flag = 2) THEN
2824 hard_edit_failed_flg := 'Y';
2825 END IF;
2826
2827 edit_check_nfe;
2828
2829 -- Bug 12985834: FACTS II 2012 enhancement
2830 -- Edits 18 and 20 are hard edits in first quarter of 2012
2831 edit_check_18;
2832 IF g_error_flag = 2 THEN
2833 IF (p_facts_run_quarter = 1 AND p_rep_fiscal_yr = 2012) THEN
2834 hard_edit_failed_flg := 'Y';
2835 ELSE
2836 g_error_flag := 1;
2837 END IF;
2838 END IF;
2839
2840 edit_check_19;
2841 IF g_error_flag = 2 THEN
2842 IF (p_facts_run_quarter = 1 AND p_rep_fiscal_yr = 2012) THEN
2843 hard_edit_failed_flg := 'Y';
2844 ELSE
2845 g_error_flag := 1;
2846 END IF;
2847 END IF;
2848
2849 edit_check_20;
2850 IF g_error_flag = 2 THEN
2851 IF (p_facts_run_quarter = 1 AND p_rep_fiscal_yr = 2012) THEN
2852 hard_edit_failed_flg := 'Y';
2853 ELSE
2854 g_error_flag := 1;
2855 END IF;
2856 END IF;
2857
2858 IF hard_edit_failed_flg = 'Y' THEN
2859 g_error_flag := 2;
2860 END IF;
2861
2862 retcode := g_error_flag;
2863 if (retcode = 1) then
2864 errbuf := 'Soft Edit Check Failed.' ;
2865 v_log_text := 'Soft Edit Check Failed.' ;
2866 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',errbuf);
2867
2868 elsif (retcode = 2) then
2869 errbuf := 'Hard Edit Check Failed';
2870 v_log_text := 'Hard Edit Check Failed.' ;
2871 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error2',errbuf);
2872
2873 else
2874 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2875 v_log_text := 'Edit Check Passed.' ;
2876 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message4',v_log_text);
2877 END IF;
2878 end if;
2879 -- SF133 enhancement
2880 if (fv_sf133_noyear.sf133_runmode = 'NO' and fv_sf133_oneyear.sf133_runmode = 'NO')then
2881 populate_bal_ret_tbl;
2882 end if;
2883 EXCEPTION
2884 WHEN OTHERS THEN
2885 v_log_text := SQLERRM;
2886 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
2887 RAISE;
2888 end; --perform_edit_checks
2889
2890 -------------------------------------------------------------------------
2891 -- Procedure CREATE_STATUS_RECORD
2892 -------------------------------------------------------------------------
2893 -- This Procedure inserts the status information into
2894 -- fv_facts_edit_check_status
2895 -------------------------------------------------------------------------
2896
2897 PROCEDURE create_status_record (p_edit_check_number NUMBER,
2898 p_edit_check_status VARCHAR2)
2899 IS
2900 l_module_name VARCHAR2(200) := g_module_name || 'create_status_record';
2901
2902 BEGIN
2903 INSERT INTO fv_facts_edit_check_status
2904 (treasury_symbol_id,
2905 edit_check_number,
2906 edit_check_status)
2910 EXCEPTION
2907 VALUES (g_treasury_symbol_id,
2908 p_edit_check_number,
2909 p_edit_check_status) ;
2911 when others then
2912 v_log_text := SQLERRM;
2913 g_error_flag := sqlcode ;
2914 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
2915 return;
2916 END; -- create_status_record
2917 -------------------------------------------------------------------------
2918 -- Procedure CREATE_LOG_RECORD
2919 -------------------------------------------------------------------------
2920 -- This Procedure inserts the log information into the FACTS temp
2921 -- table under the record category 'EDIT_CHECK_LOG_INFO'
2922 -------------------------------------------------------------------------
2923 Procedure CREATE_LOG_RECORD (text varchar2)
2924 is
2925 l_module_name VARCHAR2(200) := g_module_name || 'CREATE_LOG_RECORD';
2926 Begin
2927 v_log_counter := v_log_counter + 1 ;
2928
2929 Insert into FV_FACTS_TEMP
2930 (FCT_INT_RECORD_CATEGORY ,
2931 TREASURY_SYMBOL_ID ,
2932 TBAL_ACCT_NUM ,
2933 FACTS_REPORT_INFO ,
2934 edit_check_number ,
2935 amount ,
2936 amount1 ,
2937 amount2 ,
2938 sgl_acct_number ,
2939 budget_function,
2940 closing_grp ,
2941 SGL_BEG_BAL_ACCT_NUM,
2942 TRANSACTION_PARTNER,
2943 TRANSFER_DEPT_ID,
2944 TRANSFER_MAIN_ACCT,
2945 NON_FED_EXC_FLAG
2946 )
2947 Values
2948 ('FACTS2_EDIT_CHECK_LOG' ,
2949 g_treasury_symbol_id ,
2950 v_log_counter ,
2951 text ,
2952 v_edit_check_number ,
2953 v_amount ,
2954 v_amount1 ,
2955 v_amount2 ,
2956 v_sgl_acct_number ,
2957 v_dummy_var ,
2958 v_closing_grp ,
2959 v_beg_bal_sggl_acc,
2960 v_transaction_partner,
2961 v_aid,
2962 v_main,
2963 v_non_fed_exc_flag
2964 ) ;
2965 EXCEPTION
2966 When Others Then
2967 v_log_text := SQLERRM;
2968 g_error_flag := sqlcode ;
2969 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_log_text);
2970 return;
2971 END CREATE_LOG_RECORD ;
2972 ------------------------------------------
2973
2974 PROCEDURE populate_bal_ret_tbl
2975 IS
2976 /* Commented for bug 8768896
2977 CURSOR ret_bal_c
2978 IS
2979 SELECT tmp.amount ,
2980 tmp.sgl_acct_number ,
2981 tmp.treasury_symbol_id,
2982 att.authority_type,
2983 clos.closing_grp
2984 FROM fv_facts_temp tmp,
2985 fv_facts_attributes att,
2986 fv_facts2_closing_validation clos
2987 WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
2988 AND tmp.begin_end = 'E'
2989 AND tmp.fct_int_record_category='REPORTED_NEW'
2990 AND tmp.fct_int_record_type='BLK_DTL'
2991 AND tmp.sgl_acct_number = att.ussgl_acct_number
2992 AND clos.ussgl_Account = att.ussgl_acct_number
2993 AND att.authority_type = tmp.authority_type
2994 AND att.set_of_books_id = g_ledger_id
2995 AND clos.closing_grp IS NOT NULL
2996 AND (clos.authority_code is null or tmp.authority_type = clos.authority_code) ;
2997
2998
2999 CURSOR ret_bal_c
3000 IS
3001 SELECT tmp.amount ,
3002 tmp.sgl_acct_number ,
3003 tmp.treasury_symbol_id,
3004 att.authority_type,
3005 clos.closing_grp
3006 FROM fv_facts_temp tmp,
3007 fv_facts_attributes att,
3008 fv_facts2_closing_validation clos
3009 WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
3010 AND tmp.begin_end = 'E'
3011 AND tmp.fct_int_record_category='REPORTED_NEW'
3012 AND tmp.fct_int_record_type='BLK_DTL'
3013 AND tmp.sgl_acct_number = att.facts_acct_number
3014 AND clos.ussgl_Account = att.facts_acct_number
3015 AND att.set_of_books_id = g_ledger_id
3016 AND clos.closing_grp IS NOT NULL
3017 AND (nvl(clos.authority_code,'N')='N' or tmp.authority_type = clos.authority_code)
3018 AND (nvl(att.authority_type,'N')='N' or att.authority_type = tmp.authority_type);
3019 */
3020
3021 CURSOR ret_bal_c IS
3022 SELECT tmp.amount ,
3023 tmp.sgl_acct_number ,
3024 tmp.treasury_symbol_id,
3025 tmp.authority_type,
3026 clos.closing_grp
3027 FROM fv_facts_temp tmp,
3028 fv_facts2_closing_validation clos
3029 WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
3030 AND tmp.begin_end = 'E'
3031 AND tmp.fct_int_record_category='REPORTED_NEW'
3032 AND tmp.fct_int_record_type='BLK_DTL'
3033 and clos.ussgl_Account = tmp.sgl_acct_number
3034 -- Added by Vijay
3035 AND ( nvl(tmp.authority_type,'N')='N' or nvl(clos.authority_code,'N')='N'
3036 or tmp.authority_type=clos.authority_code)
3037 AND clos.closing_grp IS NOT NULL;
3038
3039
3040 l_rec_exists NUMBER :=0;
3041 l_module_name VARCHAR2(200);
3042 l_end_bal NUMBER:=0;
3043 l_ussgl_account VARCHAR2(30);
3044 l_treasury_symb NUMBER;
3045 l_auth_type VARCHAR2(1);
3046 l_period_name VARCHAR2(15);
3047 l_count NUMBER;
3048 l_test NUMBER :=0;
3049 l_user_id NUMBER(15):=FND_GLOBAL.USER_ID;
3050 l_closing_grp VARCHAR2(2);
3051 BEGIN
3052 l_module_name := g_module_name || 'populate_bal_ret_tbl';
3053 -- Checking for the period entry in fv_Facts2_edit_params table
3054 -- If there is an entry then empty the FV_FACTS2_RETAIN_BAL table
3055 --fnd_file.put_line(fnd_file.log , '**********************POPULATE RETAIN BAL 13 BEGIN ************');
3056 SELECT COUNT(*)
3057 INTO l_rec_exists
3058 FROM fv_Facts2_edit_params
3062 -- Getting the
3059 WHERE set_of_books_id = g_ledger_id
3060 AND period_year = g_period_year
3061 AND period_num = g_period_num;
3063 /*select period_name into l_period_name from gl_period_statuses
3064 where period_name = p_rep_fiscal_yr and period_num = p_period_num
3065 and application_id=101 and set_of_books_id = p_ledger_id;*/
3066 --fnd_file.put_line(fnd_file.log , '**********************l_rec_exists ::'||l_rec_exists);
3067 IF (l_rec_exists = 1) THEN
3068 BEGIN
3069 DELETE
3070 FROM FV_FACTS2_RETAIN_BAL
3071 WHERE treasury_symbol_id = g_treasury_symbol_id
3072 AND period_year <> g_period_year-1;
3073 EXCEPTION
3074 WHEN NO_DATA_FOUND THEN
3075 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'No data found for treasury symbol id '||g_treasury_symbol_id,v_log_text);
3076 END;
3077 /* SELECT COUNT(*)
3078 INTO l_count
3079 FROM fv_facts_temp tmp,
3080 fv_facts_attributes att,
3081 fv_facts2_closing_validation clos
3082 WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
3083 AND tmp.begin_end = 'E'
3084 AND tmp.sgl_acct_number = att.ussgl_acct_number
3085 AND tmp.sgl_acct_number = clos.ussgl_Account
3086 AND clos.closing_grp IS NOT NULL;*/
3087 --fnd_file.put_line(fnd_file.log , l_module_name||'l_count ::'||l_count);
3088 -- Using cursor ret_bal_c to populate the fv_facts2_retain_bal table
3089 FOR ret_bal_rec IN ret_bal_c
3090 LOOP
3091 l_end_bal :=ret_bal_rec.amount;
3092 l_ussgl_account:=ret_bal_rec.sgl_acct_number;
3093 l_treasury_symb:=ret_bal_rec.treasury_symbol_id;
3094 l_auth_type :=ret_bal_rec.authority_type;
3095 l_closing_grp :=ret_bal_rec.closing_grp;
3096
3097 /*fnd_file.put_line(fnd_file.log , l_module_name||'l_end_bal ::'||l_end_bal);
3098 fnd_file.put_line(fnd_file.log , l_module_name||'l_ussgl_account ::'||l_ussgl_account);
3099 fnd_file.put_line(fnd_file.log , l_module_name||'l_treasury_symb ::'||l_treasury_symb);
3100 fnd_file.put_line(fnd_file.log , l_module_name||'l_auth_type ::'||l_auth_type);*/
3101 -- Inserting the ending balance values to FV_FACTS2_RETAIN_BAL table
3102 INSERT
3103 INTO FV_FACTS2_RETAIN_BAL
3104 (
3105 USSGL_ACCOUNT ,
3106 TREASURY_SYMBOL_ID,
3107 AUTHORITY_TYPE ,
3108 ENDING_BAL ,
3109 period_num,
3110 closing_grp,
3111 period_year,
3112 LAST_UPDATE_DATE ,
3113 CREATION_DATE ,
3114 CREATED_BY ,
3115 last_updated_by
3116 )
3117 VALUES
3118 (
3119 l_ussgl_account,
3120 l_treasury_symb,
3121 l_auth_type ,
3122 l_end_bal ,
3123 g_period_num ,
3124 l_closing_grp,
3125 g_period_year ,
3126 sysdate ,
3127 sysdate ,
3128 l_user_id ,
3129 l_user_id
3130 );
3131 END LOOP;
3132 END IF;
3133 END populate_bal_ret_tbl;
3134 end fv_facts_edit_check; -- Package body