[Home] [Help]
PACKAGE BODY: APPS.FV_UPG_FACTS_EB_TO_GTAS
Source
1 PACKAGE BODY FV_UPG_FACTS_EB_TO_GTAS AS
2 /* $Header: FVUPFGTB.pls 120.3.12020000.3 2013/02/22 21:36:44 snama noship $ */
3 g_err_code NUMBER := 0;
4 g_err_buff VARCHAR2(200);
5 g_sob_id NUMBER;
6 g_acc_value_set_id NUMBER;
7 g_module VARCHAR2(150);
8 g_coa_id NUMBER;
9 g_bal_segment VARCHAR2(30);
10 g_acc_segment VARCHAR2(30);
11 g_hdr1_printed BOOLEAN := FALSE;
12 g_hdr2_printed BOOLEAN := FALSE;
13 g_fiscal_year NUMBER;
14 g_period_num_high NUMBER;
15
16 PROCEDURE get_ussgl_acct_num(p_acct_num IN VARCHAR2,
17 p_gtas_sgl_acct_num OUT NOCOPY VARCHAR2);
18 PROCEDURE get_fed_non_fed(p_acct_num IN VARCHAR2,
19 p_facts_fed_non_fed IN VARCHAR2,
20 p_fed_non_fed OUT NOCOPY VARCHAR2);
21 PROCEDURE check_fed_nonfed(p_account_number IN VARCHAR2,
22 p_fed_nonfed_type IN VARCHAR2,
23 p_code_exists OUT NOCOPY VARCHAR2);
24 PROCEDURE get_segment_names;
25 PROCEDURE log(module IN VARCHAR2, message_line IN VARCHAR2);
26 PROCEDURE print_header(p_hdr_num IN NUMBER);
27 PROCEDURE process_facts1_eb;
28 PROCEDURE process_facts2_eb;
29 PROCEDURE process_non_facts_eb;
30
31 PROCEDURE MAIN(p_err_buff OUT NOCOPY VARCHAR2,
32 p_err_code OUT NOCOPY NUMBER,
33 p_fiscal_year IN NUMBER) IS
34
35 l_statement VARCHAR2(1000);
36 l_module VARCHAR2(100) := g_module || 'MAIN.';
37 l_facts1_eb_count NUMBER;
38 l_facts2_eb_count NUMBER;
39 BEGIN
40
41 log(l_module, 'Begin');
42 g_fiscal_year := p_fiscal_year;
43 log(l_module, 'g_fiscal_year: ' || g_fiscal_year);
44 --Deleting any existing rows in gtas ending bal table
45 fnd_file.put_line(fnd_file.log,
46 'Deleting from fv_gtas_ending_balances table.');
47
48 --IF there are no rows in facts 1 and facts 2 ending
49 --balances table, then error and return
50 SELECT count(*)
51 INTO l_facts1_eb_count
52 FROM fv_facts_ending_balances
53 WHERE fiscal_year = g_fiscal_year - 1;
54
55 SELECT count(*)
56 INTO l_facts2_eb_count
57 FROM fv_factsii_ending_balances
58 WHERE fiscal_year = g_fiscal_year - 1;
59
60 IF l_facts1_eb_count = 0 AND l_facts2_eb_count = 0 THEN
61 p_err_code := -1;
62 p_err_buff := 'No Facts 1 and Facts 2 ending balances found to migrate.';
63 RETURN;
64 END IF;
65
66 DELETE FROM fv_gtas_ending_balances;
67
68 IF g_err_code = 0 THEN
69 process_facts1_eb;
70 END IF;
71
72 IF g_err_code = 0 THEN
73 process_facts2_eb;
74 END IF;
75
76 IF g_err_code = 0 THEN
77 process_non_facts_eb;
78 END IF;
79
80 IF g_err_code <> 0 THEN
81 p_err_code := g_err_code;
82 p_err_buff := g_err_buff;
83 RETURN;
84 END IF;
85
86 COMMIT;
87
88 --Output comma delimited values for user verification
89 fnd_file.put_line(fnd_file.output, ' ');
90 fnd_file.put_line(fnd_file.output, ' ');
91 fnd_file.put_line(fnd_file.output, ' ');
92 fnd_file.put_line(fnd_file.output,
93 '================================================================================');
94 fnd_file.put_line(fnd_file.output,
95 '**********************************Migrated Data*********************************');
96 fnd_file.put_line(fnd_file.output,
97 '================================================================================');
98
99 l_statement := 'select
100 ''SET_OF_BOOKS_ID, FISCAL_YEAR, CCID,FUND_VALUE, ACCOUNT_NUMBER,
101 D_C_INDICATOR,' ||
102 'AUTHORITY_TYPE_CODE, FED_NON_FED, TRADING_PARTNER_AGENCY_ID,' ||
103 'TRADING_PARTNER_MAIN_ACCOUNT, AMOUNT''
104 from dual';
105
106 fv_flatfiles.create_flat_file(l_statement);
107
108 l_statement := 'select
109 SET_OF_BOOKS_ID||'',''||FISCAL_YEAR||'',''||CCID||'',''||FUND_VALUE||'',''||
110 ACCOUNT_NUMBER||'',''||D_C_INDICATOR||'',''||AUTHORITY_TYPE_CODE||'',''||
111 FED_NON_FED||'',''||TRADING_PARTNER_AGENCY_ID||'',''||
112 TRADING_PARTNER_MAIN_ACCOUNT||'',''||AMOUNT
113 from fv_gtas_ending_balances
114 order by 1';
115 fv_flatfiles.create_flat_file(l_statement);
116
117 log(l_module, 'End');
118 fnd_file.put_line(fnd_file.log,
119 'Migrate Facts End Bal to GTAS End Bal
120 completed.');
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 fnd_file.put_line(fnd_file.log,
125 l_module || ' :When others error: ' || sqlerrm);
126 p_err_code := -1;
127 p_err_buff := l_module || ' :When others error: ' || sqlerrm;
128 END MAIN;
129 ------------------------------------------------------------------------------------
130 PROCEDURE GET_USSGL_ACCT_NUM(p_acct_num IN VARCHAR2,
131 p_gtas_sgl_acct_num OUT NOCOPY VARCHAR2) IS
132 l_module VARCHAR2(100) := g_module || 'GET_USSGL_ACCT_NUM.';
133 BEGIN
134 log(l_module, 'p_acct_num: ' || p_acct_num);
135 log(l_module, 'g_acc_value_set_id: ' || g_acc_value_set_id);
136
137 SELECT SUBSTR(parent_flex_value, 1, 6)
138 INTO p_gtas_sgl_acct_num
139 FROM fnd_flex_value_hierarchies
140 WHERE p_acct_num BETWEEN child_flex_value_low AND
141 child_flex_value_high
142 AND flex_value_set_id = g_acc_value_set_id
143 AND parent_flex_value <> 'T'
144 --AND SUBSTR(parent_flex_value,1,6) IN
145 and exists
146 (SELECT ussgl_account
147 FROM fv_facts_ussgl_accounts
148 WHERE ussgl_account = SUBSTR(parent_flex_value, 1, 6))
149 AND parent_flex_value IN
150 (SELECT flex_value
151 FROM fnd_flex_values
152 WHERE flex_value_set_id = g_acc_value_set_id
153 AND enabled_flag = 'Y');
154
155 log(l_module, 'gtas_sgl_acct_number: ' || p_gtas_sgl_acct_num);
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 fnd_file.put_line(fnd_file.log, l_module || ':' || sqlerrm);
160 g_err_code := -1;
161 g_err_buff := l_module || ':' || sqlerrm;
162 WHEN TOO_MANY_ROWS THEN
163 fnd_file.put_line(fnd_file.log,
164 l_module || ':' || 'Too many parents for: ' ||
165 p_acct_num);
166 g_err_code := -1;
167 g_err_buff := l_module || ':' || 'Too many parents for: ' ||
168 p_acct_num;
169 WHEN OTHERS THEN
170 fnd_file.put_line(fnd_file.log,
171 l_module || ' :When others error: ' || sqlerrm);
172 g_err_code := -1;
173 g_err_buff := l_module || ' :When others error: ' || sqlerrm;
174 END get_ussgl_acct_num;
175 -------------------------------------------------------------------------------
176 --Checks whether a passed value exists in fv_gtas_attributes.fed_non_fed1
177 --2 or 3 columns
178 PROCEDURE CHECK_FED_NONFED(p_account_number IN VARCHAR2,
179 p_fed_nonfed_type IN VARCHAR2,
180 p_code_exists OUT NOCOPY VARCHAR2) IS
181 l_module VARCHAR2(80) := g_module || 'CHECK_FED_NONFED';
182 l_fednonfed_code fv_gtas_attributes.fed_non_fed1%TYPE;
183 l_fednonfed_code_tmp fv_gtas_attributes.fed_non_fed1%TYPE;
184 l_fednonfed_select VARCHAR2(200);
185 l_fednonfed_code_count NUMBER;
186 BEGIN
187
188 log(l_module, 'Begin');
189 log(l_module, 'p_account_number: ' || p_account_number);
190
191 SELECT 'Y'
192 INTO p_code_exists
193 FROM fv_gtas_attributes
194 WHERE gtas_acct_number = p_account_number
195 AND set_of_books_id = g_sob_id
196 AND (fed_non_fed1 = p_fed_nonfed_type OR
197 fed_non_fed2 = p_fed_nonfed_type OR
198 fed_non_fed3 = p_fed_nonfed_type);
199
200 log(l_module, 'p_code_exists: ' || p_code_exists);
201 log(l_module, 'End');
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 p_code_exists := 'N';
205 log(l_module, 'p_code_exists: ' || p_code_exists);
206 WHEN OTHERS THEN
207 g_err_code := 2;
208 g_err_buff := l_module || '-When others error: ' || SQLERRM;
209 FV_UTILITY.LOG_MESG(g_err_buff);
210 END CHECK_FED_NONFED;
211 --------------------------------------------------------------------------------
212 PROCEDURE GET_SEGMENT_NAMES IS
213
214 l_app_id NUMBER := 101;
215 l_flex_code VARCHAR2(10) := 'GL#';
216 l_segment_found BOOLEAN := FALSE;
217 invalid_bal_segment EXCEPTION;
218 invalid_acc_segment EXCEPTION;
219 l_module VARCHAR2(100) := g_module || 'GET_SEGMENT_NAMES';
220
221 BEGIN
222
223 log(l_module, 'Begin');
224
225 SELECT chart_of_accounts_id
226 INTO g_coa_id
227 FROM gl_ledgers_public_v
228 WHERE ledger_id = g_sob_id;
229
230 log(l_module, 'COA ID: ' || g_coa_id);
231
232 -- Get Balancing Segment Name
233 l_segment_found := FND_FLEX_APIS.get_segment_column(l_app_id,
234 l_flex_code,
235 g_coa_id,
236 'GL_BALANCING',
237 g_bal_segment);
238 IF NOT l_segment_found THEN
239 RAISE invalid_bal_segment;
240 END IF;
241 log(l_module, 'Balancing Segment: ' || g_bal_segment);
242
243 -- Get Accounting Segment Name
244 l_segment_found := FND_FLEX_APIS.get_segment_column(l_app_id,
245 l_flex_code,
246 g_coa_id,
247 'GL_ACCOUNT',
248 g_acc_segment);
249
250 IF NOT l_segment_found THEN
251 RAISE invalid_acc_segment;
252 END IF;
253 log(l_module, 'Accounting Segment: ' || g_acc_segment);
254
255 IF (g_bal_segment IS NULL OR g_acc_segment IS NULL) THEN
256 RAISE NO_DATA_FOUND;
257 END IF;
258
259 --Get value set id of accounting segment
260 SELECT flex_value_set_id
261 INTO g_acc_value_set_id
262 FROM fnd_id_flex_segments
263 WHERE application_column_name = g_acc_segment
264 AND id_flex_code = 'GL#'
265 AND id_flex_num = g_coa_id;
266 log(l_module,
267 'Accounting Segment value set id: ' || g_acc_value_set_id);
268
269 --Get the last period number of the previous year
270 log(l_module, ' getting last period of the previous year. ');
271 SELECT MAX(period_num)
272 INTO g_period_num_high
273 FROM gl_period_statuses
274 WHERE period_year = g_fiscal_year - 1
275 AND application_id = 101
276 AND closing_status <> 'F'
277 AND closing_status <> 'N'
278 AND ledger_id = g_sob_id;
279
280 log(l_module, 'End');
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 g_err_code := -1;
284 g_err_buff := 'Balancing or Accounting segment not found.';
285 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
286 WHEN invalid_bal_segment THEN
287 g_err_code := -1;
288 g_err_buff := 'Error while fetching balancing segment.';
289 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
290 WHEN invalid_acc_segment THEN
291 g_err_code := -1;
292 g_err_buff := 'Error while fetching accounting segment.';
293 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
294 WHEN OTHERS THEN
295 g_err_code := -1;
296 g_err_buff := 'When others error while getting
297 Balancing or Accounting segment - ' ||
298 SQLERRM;
299 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
300 END get_segment_names;
301 --------------------------------------------------------------------------------
302 PROCEDURE LOG(module IN VARCHAR2, message_line IN VARCHAR2) IS
303
304 l_module VARCHAR2(1000) := g_module || 'LOG';
305
306 BEGIN
307 --log(l_module,message_line);
308
309 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
310 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, module, message_line);
311 END IF;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 g_err_code := SQLCODE;
316 g_err_buff := l_module || ' - When others error: ' || SQLERRM;
317 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
318 END log;
319 --------------------------------------------------------------------------------
320 --Modified for change request 35
321 --Valid facts i and ii fed code can be 'F', 'N', 'E', 'X'
322 --IF facts end bal fed nonfed is F
323 --and F exists in gtas attributes, return F
324 --else if G exists on gtas attributes return G else error
325 --IF facts end bal fed nonfed is X and
326 --gtas attributes includes an N, then return N
327 PROCEDURE GET_FED_NON_FED(p_acct_num IN VARCHAR2,
328 p_facts_fed_non_fed IN VARCHAR2,
329 p_fed_non_fed OUT NOCOPY VARCHAR2) IS
330 l_module VARCHAR2(200) := g_module || 'get_fed_non_fed.';
331 l_code_exists VARCHAR2(1);
332 l_err_buff VARCHAR2(200) := 'GTAS Federal or Non-Federal Code not compatible with FACTS Fed/Non Fed value.';
333 BEGIN
334
335 log(l_module, 'p_facts_fed_non_fed : ' || p_facts_fed_non_fed);
336
337 IF p_facts_fed_non_fed = 'F' THEN
338 check_fed_nonfed(p_acct_num, 'F', l_code_exists);
339 IF l_code_exists = 'Y' THEN
340 p_fed_non_fed := 'F';
341 RETURN;
342 ELSE
343 --if F does not exist on gtas attributes
344 --check if gtas attributes includes a G
345 check_fed_nonfed(p_acct_num, 'G', l_code_exists);
346 IF l_code_exists = 'Y' THEN
347 p_fed_non_fed := 'G';
348 RETURN;
349 ELSE
350 log(l_module, 'Error: F does not exist in gtas attributes.');
351 g_err_code := -1;
352 g_err_buff := l_err_buff || ':' || p_acct_num;
353 RETURN;
354 END IF;
355 END IF;
356
357 ELSIF p_facts_fed_non_fed = 'N' THEN
358 --check if gtas attributes includes an N, if it does not,
359 --then error
360 check_fed_nonfed(p_acct_num, 'N', l_code_exists);
361
362 IF l_code_exists = 'Y' THEN
363 p_fed_non_fed := 'N';
364 RETURN;
365 ELSE
366 log(l_module, 'Error: N does not exist in gtas attributes.');
367 g_err_code := -1;
368 g_err_buff := l_err_buff || ':' || p_acct_num;
369 RETURN;
370 END IF;
371 ELSIF p_facts_fed_non_fed = 'X' THEN
372 --check if gtas attributes includes X, return N, if it does not,
373 --then error
374 check_fed_nonfed(p_acct_num, 'X', l_code_exists);
375 IF l_code_exists = 'Y' THEN
376 p_fed_non_fed := 'N';
377 RETURN;
378 ELSE
379 log(l_module, 'Error: X does not exist in gtas attributes.');
380 g_err_code := -1;
381 g_err_buff := l_err_buff || ':' || p_acct_num;
382 RETURN;
383 END IF;
384 ELSIF p_facts_fed_non_fed = 'E' THEN
385 --check if gtas attributes includes X, return E, if it does not,
386 --then error
387 check_fed_nonfed(p_acct_num, 'E', l_code_exists);
388 IF l_code_exists = 'Y' THEN
389 p_fed_non_fed := 'E';
390 RETURN;
391 ELSE
392 log(l_module, 'Error: E does not exist in gtas attributes.');
393 g_err_code := -1;
394 g_err_buff := l_err_buff || ':' || p_acct_num;
395 RETURN;
396 END IF;
397
398 ELSE
399 --Fed non fed does not exist so error
400 g_err_code := -1;
401 g_err_buff := l_err_buff || ':' || p_acct_num;
402 log(l_module, l_err_buff);
403 RETURN;
404 END IF;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 g_err_code := SQLCODE;
409 g_err_buff := l_module || ' - When others error: ' || SQLERRM;
410 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
411 END get_fed_non_fed;
412 --------------------------------------------------------------------------------
413 PROCEDURE PRINT_HEADER(p_hdr_num IN NUMBER) IS
414 l_module VARCHAR2(200) := g_module || 'print_header.';
415 BEGIN
416
417 IF p_hdr_num = 1 AND NOT g_hdr1_printed THEN
418 fnd_file.put_line(fnd_file.output,
419 'Agency ID not found for the following Eliminations Dept');
420 fnd_file.put_line(fnd_file.output,
421 '=======================================================');
422 fnd_file.put_line(fnd_file.output,
423 'Account Number Eliminations Dept');
424 fnd_file.put_line(fnd_file.output,
425 '-------------- -----------------');
426 g_hdr1_printed := TRUE;
427
428 ELSIF p_hdr_num = 21 AND NOT g_hdr2_printed THEN
429 fnd_file.put_line(fnd_file.output, ' ');
430 fnd_file.put_line(fnd_file.output, ' ');
431 fnd_file.put_line(fnd_file.output, ' ');
432 fnd_file.put_line(fnd_file.output,
433 'Agency ID not found for the following Transfer Dept ID');
434 fnd_file.put_line(fnd_file.output,
435 '=======================================================');
436 fnd_file.put_line(fnd_file.output,
437 'Account Number Transfer Dept ID');
438 fnd_file.put_line(fnd_file.output,
439 '-------------- -----------------');
440 g_hdr2_printed := TRUE;
441 END IF;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 g_err_code := SQLCODE;
446 g_err_buff := l_module || ' - When others error: ' || SQLERRM;
447 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
448 END print_header;
449
450 --------------------------------------------------------------------------------
451 PROCEDURE PROCESS_FACTS1_EB IS
452 l_module VARCHAR2(200) := g_module || 'PROCESS_FACTS1_EB.';
453 --Fetch accounts existing in FACTS I ending balances
454 CURSOR facts1_acct_cur IS
455 SELECT DISTINCT account_number, set_of_books_id
456 FROM fv_gtas_fed_accounts fgfa
457 WHERE fiscal_year = g_fiscal_year
458 AND fed_non_fed_flag = 'Y'
459 AND EXISTS
460 (SELECT 1
461 FROM fv_facts_ending_balances ff1e
462 WHERE ff1e.set_of_books_id = fgfa.set_of_books_id
463 AND ff1e.account_number = fgfa.account_number
464 AND ff1e.fiscal_year = g_fiscal_year - 1)
465 ORDER BY set_of_books_id;
466
467 CURSOR facts1_eb_cur(v_sob_id IN NUMBER, v_acct_number IN VARCHAR2) IS
468 SELECT set_of_books_id,
469 fiscal_year,
470 ccid,
471 fund_value,
472 account_number,
473 d_c_indicator,
474 g_ng_indicator,
475 amount,
476 eliminations_dept
477 FROM fv_facts_ending_balances
478 WHERE set_of_books_id = v_sob_id
479 AND account_number = v_acct_number
480 AND fiscal_year = g_fiscal_year - 1;
481
482 l_temp_acct_num fv_gtas_attributes.gtas_acct_number%TYPE;
483 l_ussgl_acct_number fv_gtas_attributes.ussgl_acct_number%TYPE;
484 l_fed_non_fed VARCHAR2(1);
485 l_aid VARCHAR2(3);
486 l_main_account VARCHAR2(4);
487 l_prev_sob_id NUMBER;
488
489 BEGIN
490 log(l_module, 'Begin');
491
492 FOR fed_acct_rec IN facts1_acct_cur LOOP
493
494 FOR f1_eb_rec IN facts1_eb_cur(fed_acct_rec.set_of_books_id,
495 fed_acct_rec.account_number) LOOP
496
497 g_sob_id := fed_acct_rec.set_of_books_id;
498 log(l_module, 'g_sob_id: ' || g_sob_id);
499
500 --If segment names already retrieved for the same sob id
501 --then no need to get it again
502 IF g_sob_id <> NVL(l_prev_sob_id, -9999) THEN
503 get_segment_names;
504 END IF;
505
506 log(l_module, 'Account_number: ' || fed_acct_rec.account_number);
507
508 --Get the gtas attributes for the account
509 --if it exists in fv gtas attributes
510 --If not, then get the ussgl account and get its attributes
511 l_temp_acct_num := NULL;
512
513 BEGIN
514 SELECT gtas_acct_number
515 INTO l_temp_acct_num
516 FROM fv_gtas_attributes
517 WHERE set_of_books_id = fed_acct_rec.set_of_books_id
518 AND gtas_acct_number = fed_acct_rec.account_number;
519
520 EXCEPTION
521 WHEN NO_DATA_FOUND THEN
522 log(l_module,
523 'Account: ' || fed_acct_rec.account_number ||
524 ' not found in gtas.');
525 log(l_module, 'Trying ussgl account');
526
527 get_ussgl_acct_num(fed_acct_rec.account_number,
528 l_ussgl_acct_number);
529
530 IF g_err_code <> 0 THEN
531 RETURN;
532 END IF;
533
534 BEGIN
535 SELECT ussgl_acct_number
536 INTO l_temp_acct_num
537 FROM fv_gtas_attributes
538 WHERE set_of_books_id = fed_acct_rec.set_of_books_id
539 AND gtas_acct_number = l_ussgl_acct_number;
540
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 g_err_code := -1;
544 g_err_buff := l_module || ' - USSGL Account not found: ' ||
545 l_ussgl_acct_number;
546 log(l_module,
547 'USSGL Account not found for ' || l_ussgl_acct_number);
548 RETURN;
549 END;
550 END;
551
552 --Check if fed non fed value exists on gtas attributes
553 --If facts fed non fed is F
554 l_fed_non_fed := NULL;
555 get_fed_non_fed(l_temp_acct_num,
556 f1_eb_rec.g_ng_indicator,
557 l_fed_non_fed);
558
559 --If fed non fed is G, then agency id is 099, main acct is 0000
560 --If F, then get agency id from mapping table
561 l_aid := NULL;
562 l_main_account := NULL;
563 IF l_fed_non_fed = 'G' THEN
564 l_aid := '099';
565 l_main_account := '0000';
566 ELSIF l_fed_non_fed = 'F' THEN
567 BEGIN
568 l_aid := NULL;
569 SELECT aid
570 INTO l_aid
571 FROM fv_agency_id_map
572 --WHERE star_dept_reg = NVL(f1_eb_rec.eliminations_dept, '-ZZ')
573 WHERE star_dept_reg = NVL(SUBSTR(f1_eb_rec.eliminations_dept,1,2), '-ZZ')
574 AND rownum = 1;
575
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 print_header(1);
579 fnd_file.put_line(fnd_file.output,
580 RPAD(f1_eb_rec.account_number, 38, ' ') ||
581 NVL(f1_eb_rec.eliminations_dept, 'BLANK'));
582 END;
583 END IF;
584
585 INSERT INTO fv_gtas_ending_balances
586 (set_of_books_id,
587 fiscal_year,
588 ccid,
589 fund_value,
590 account_number,
591 d_c_indicator,
592 fed_non_fed,
593 trading_partner_agency_id,
594 trading_partner_main_account,
595 amount)
596 VALUES
597 (f1_eb_rec.set_of_books_id,
598 f1_eb_rec.fiscal_year,
599 f1_eb_rec.ccid,
600 f1_eb_rec.fund_value,
601 f1_eb_rec.account_number,
602 f1_eb_rec.d_c_indicator,
603 f1_eb_rec.g_ng_indicator,
604 l_aid,
605 l_main_account,
606 f1_eb_rec.amount);
607
608 log(l_module,
609 'Inserted facts1 row: Account number: ' ||
610 f1_eb_rec.account_number || ' Fiscal Year: ' ||
611 f1_eb_rec.fiscal_year);
612
613 l_prev_sob_id := g_sob_id;
614
615 END LOOP;
616
617 END LOOP;
618 log(l_module, 'End');
619 EXCEPTION
620 WHEN OTHERS THEN
621 g_err_code := SQLCODE;
622 g_err_buff := l_module || ' - When others error: ' || SQLERRM;
623 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
624 END PROCESS_FACTS1_EB;
625 --------------------------------------------------------------------------------
626 PROCEDURE PROCESS_FACTS2_EB IS
627
628 --Fetch accounts existing in FACTS II ending balances
629 --but not in FACTS I ending balances
630 CURSOR facts2_acct_cur IS
631 SELECT DISTINCT account_number,
632 set_of_books_id,
633 authority_type_flag,
634 fed_non_fed_flag
635 FROM fv_gtas_fed_accounts fgfa
636 WHERE fiscal_year = g_fiscal_year
637 AND (fed_non_fed_flag = 'Y' OR authority_type_flag = 'Y')
638 AND EXISTS((SELECT account
639 FROM fv_factsii_ending_balances ff2e
640 WHERE ff2e.set_of_books_id = fgfa.set_of_books_id
641 AND ff2e.account = fgfa.account_number
642 AND ff2e.fiscal_year = g_fiscal_year - 1)
643 MINUS
644 (SELECT account_number
645 FROM fv_facts_ending_balances ff1e
646 WHERE ff1e.set_of_books_id = fgfa.set_of_books_id
647 AND ff1e.account_number = fgfa.account_number
648 AND ff1e.fiscal_year = g_fiscal_year - 1));
649
650 CURSOR facts2_eb_cur(v_sob_id IN NUMBER, v_acct_number IN VARCHAR2) IS
651 SELECT set_of_books_id,
652 fiscal_year,
653 ccid,
654 fund,
655 account,
656 (CASE
657 WHEN (ending_balance_dr - ending_balance_cr) >= 0 THEN
658 'D'
659 ELSE
660 'C'
661 END) d_c_ind,
662 (ending_balance_dr - ending_balance_cr) amount,
663 DECODE(transfer_dept_id, NULL, NULL, 'F') fed_non_fed,
664 transfer_dept_id,
665 transfer_main_acct
666 FROM fv_factsii_ending_balances
667 WHERE set_of_books_id = v_sob_id
668 AND account = v_acct_number
669 AND fiscal_year = g_fiscal_year - 1;
670
671 l_prev_sob_id NUMBER;
672 l_temp_acct_num fv_gtas_attributes.gtas_acct_number%TYPE;
673 l_ussgl_acct_number fv_gtas_attributes.ussgl_acct_number%TYPE;
674 l_fed_non_fed VARCHAR2(1);
675 l_aid VARCHAR2(3);
676 l_main_account VARCHAR2(4);
677 l_authority_type_code fv_facts_attributes.authority_type%TYPE;
678
679 l_module VARCHAR2(200) := g_module || 'PROCESS_FACTS2_EB.';
680 l_code_exists VARCHAR2(1) := 'N';
681 BEGIN
682 log(l_module, 'Begin');
683
684 FOR fed_acct_rec IN facts2_acct_cur LOOP
685
686 g_sob_id := fed_acct_rec.set_of_books_id;
687
688 --If segment names already retrieved for the same sob id
689 --then no need to get it again
690 IF g_sob_id <> NVL(l_prev_sob_id, -9999) THEN
691 get_segment_names;
692 END IF;
693
694 FOR f2_eb_rec IN facts2_eb_cur(fed_acct_rec.set_of_books_id,
695 fed_acct_rec.account_number) LOOP
696
697 BEGIN
698 l_aid := NULL;
699 SELECT aid
700 INTO l_aid
701 FROM fv_agency_id_map
702 WHERE star_dept_reg = f2_eb_rec.transfer_dept_id
703 AND rownum = 1;
704
705 EXCEPTION
706 WHEN NO_DATA_FOUND THEN
707 print_header(2);
708 fnd_file.put_line(fnd_file.output,
709 RPAD(f2_eb_rec.account, 38, ' ') ||
710 NVL(f2_eb_rec.transfer_dept_id, 'BLANK'));
711 END;
712
713 --If authority type flag on gtas fed accounts is Y
714 --copy authority type code from facts attributes, else null
715 IF fed_acct_rec.authority_type_flag = 'Y' THEN
716 BEGIN
717 l_authority_type_code := NULL;
718
719 SELECT authority_type
720 INTO l_authority_type_code
721 FROM fv_facts_attributes
722 WHERE set_of_books_id = fed_acct_rec.set_of_books_id
723 AND facts_acct_number = fed_acct_rec.account_number;
724
725 EXCEPTION
726 WHEN NO_DATA_FOUND THEN
727 log(l_module,
728 'Authority Type Code not found for account: ' ||
729 fed_acct_rec.account_number);
730 END;
731 END IF;
732
733 --Get the gtas attributes for the account
734 --if it exists in fv gtas attributes
735 --If not, then get the ussgl account and get its attributes
736 l_temp_acct_num := NULL;
737
738 BEGIN
739 SELECT gtas_acct_number
740 INTO l_temp_acct_num
741 FROM fv_gtas_attributes
742 WHERE set_of_books_id = fed_acct_rec.set_of_books_id
743 AND gtas_acct_number = fed_acct_rec.account_number;
744
745 EXCEPTION
746 WHEN NO_DATA_FOUND THEN
747 log(l_module,
748 'Account: ' || fed_acct_rec.account_number || ' not found.');
749 log(l_module, 'Trying ussgl account');
750
751 get_ussgl_acct_num(fed_acct_rec.account_number,
752 l_ussgl_acct_number);
753
754 BEGIN
755 SELECT ussgl_acct_number
756 INTO l_temp_acct_num
757 FROM fv_gtas_attributes
758 WHERE set_of_books_id = fed_acct_rec.set_of_books_id
759 AND gtas_acct_number = l_ussgl_acct_number;
760
761 EXCEPTION
762 WHEN NO_DATA_FOUND THEN
763 g_err_code := -1;
764 g_err_buff := l_module || ' - USSGL Account not found: ' ||
765 l_ussgl_acct_number;
766 log(l_module,
767 'USSGL Account not found: ' || l_ussgl_acct_number);
768 RETURN;
769 END;
770 END;
771 --If fed nonfed flag gtas attribute is Y then derive
772 --fed non fed value
773 l_fed_non_fed := NULL;
774
775 IF fed_acct_rec.fed_non_fed_flag = 'Y' THEN
776 --CR 35 changed again
777 --IF transfer dept is not null then check if F or G
778 --exists on gtas
779 --IF transfer dept is null then check if N or E
780 --exists on gtas and use these values appropriately
781 IF f2_eb_rec.fed_non_fed IS NOT NULL THEN
782 check_fed_nonfed(l_temp_acct_num, 'F', l_code_exists);
783 IF l_code_exists = 'Y' THEN
784 l_fed_non_fed := 'F';
785 ELSIF l_code_exists = 'N' THEN
786 check_fed_nonfed(l_temp_acct_num, 'G', l_code_exists);
787 IF l_code_exists = 'Y' THEN
788 l_fed_non_fed := 'G';
789 END IF;
790 END IF;
791 ELSIF f2_eb_rec.fed_non_fed IS NULL THEN
792 check_fed_nonfed(l_temp_acct_num, 'N', l_code_exists);
793 IF l_code_exists = 'Y' THEN
794 l_fed_non_fed := 'N';
795 ELSIF l_code_exists = 'N' THEN
796 check_fed_nonfed(l_temp_acct_num, 'E', l_code_exists);
797 IF l_code_exists = 'Y' THEN
798 l_fed_non_fed := 'E';
799 END IF;
800 END IF;
801 END IF;
802
803 --If fed non fed is G, then agency id is 099, main acct is 0000
804 --If F, then get agency id from mapping table
805 IF l_fed_non_fed = 'F' THEN
806 l_main_account := f2_eb_rec.transfer_main_acct;
807 BEGIN
808 l_aid := NULL;
809 SELECT aid
810 INTO l_aid
811 FROM fv_agency_id_map
812 --WHERE star_dept_reg = NVL(f2_eb_rec.transfer_dept_id, '-ZZ')
813 WHERE star_dept_reg = NVL(SUBSTR(f2_eb_rec.transfer_dept_id,1,2), '-ZZ')
814 AND rownum = 1;
815
816 EXCEPTION
817 WHEN NO_DATA_FOUND THEN
818 fnd_file.put_line(fnd_file.output,
819 RPAD(f2_eb_rec.account, 38, ' ') ||
820 NVL(f2_eb_rec.transfer_dept_id, 'BLANK'));
821 END;
822 ELSIF l_fed_non_fed = 'G' THEN
823 l_aid := '099';
824 l_main_account := '0000';
825 ELSE
826 l_aid := NULL;
827 l_main_account := NULL;
828 END IF;
829 END IF;
830
831 INSERT INTO fv_gtas_ending_balances
832 (set_of_books_id,
833 fiscal_year,
834 ccid,
835 fund_value,
836 account_number,
837 d_c_indicator,
838 authority_type_code,
839 fed_non_fed,
840 trading_partner_agency_id,
841 trading_partner_main_account,
842 amount)
843 VALUES
844 (f2_eb_rec.set_of_books_id,
845 f2_eb_rec.fiscal_year,
846 f2_eb_rec.ccid,
847 f2_eb_rec.fund,
848 f2_eb_rec.account,
849 f2_eb_rec.d_c_ind,
850 l_authority_type_code,
851 l_fed_non_fed,
852 l_aid,
853 l_main_account,
854 f2_eb_rec.amount);
855
856 log(l_module,
857 'Inserted facts2 row: Account number: ' || f2_eb_rec.account ||
858 ' Fiscal Year: ' || f2_eb_rec.fiscal_year);
859
860 l_prev_sob_id := g_sob_id;
861 END LOOP;
862
863 END LOOP;
864 log(l_module, 'End');
865 EXCEPTION
866 WHEN OTHERS THEN
867 g_err_code := SQLCODE;
868 g_err_buff := l_module || ' - When others error: ' || SQLERRM;
869 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
870 END PROCESS_FACTS2_EB;
871 --------------------------------------------------------------------------------
872 PROCEDURE PROCESS_NON_FACTS_EB IS
873
874 CURSOR fed_acct_not_in_facts_cur IS
875 SELECT DISTINCT account_number, sgl_account_number, set_of_books_id,
876 authority_type_flag
877 FROM fv_gtas_fed_accounts fgfa
878 WHERE fiscal_year = g_fiscal_year
879 AND (fgfa.fed_non_fed_flag = 'Y' OR
880 fgfa.authority_type_flag = 'Y')
881 AND NOT EXISTS((SELECT account
882 FROM fv_factsii_ending_balances ff2e
883 WHERE ff2e.set_of_books_id = fgfa.set_of_books_id
884 AND ff2e.account = fgfa.account_number
885 AND ff2e.fiscal_year = g_fiscal_year - 1) UNION ALL
886 (SELECT account_number
887 FROM fv_facts_ending_balances ff1e
888 WHERE ff1e.set_of_books_id = fgfa.set_of_books_id
889 AND ff1e.account_number = fgfa.account_number
890 AND ff1e.fiscal_year = g_fiscal_year - 1));
891
892 l_module VARCHAR2(200) := g_module || 'PROCESS_NON_FACTS_EB.';
893 l_select VARCHAR2(1000);
894 l_prev_sob_id NUMBER;
895 TYPE l_ref_cur IS REF CURSOR;
896 gl_record l_ref_cur;
897 l_ccid NUMBER;
898 l_fund_value fv_gtas_ending_balances.fund_value%TYPE;
899 l_account_number fv_gtas_ending_balances.account_number%TYPE;
900 l_amount NUMBER;
901
902 l_temp_acct_num fv_gtas_attributes.gtas_acct_number%TYPE;
903 l_ussgl_acct_number fv_gtas_attributes.ussgl_acct_number%TYPE;
904 l_fed_non_fed VARCHAR2(1);
905 l_aid VARCHAR2(3);
906 l_main_account VARCHAR2(4);
907 l_code_exists VARCHAR2(1);
908 l_fednonfed_count NUMBER;
909 l_fed_nonfed_code VARCHAR2(1);
910 l_d_c_indicator VARCHAR2(1);
911 l_authority_type_code VARCHAR2(1);
912
913 BEGIN
914 log(l_module, 'Begin');
915
916 FOR acct_rec IN fed_acct_not_in_facts_cur LOOP
917
918 g_sob_id := acct_rec.set_of_books_id;
919
920 --If segment names already retrieved for the same sob id
921 --then no need to get it again
922 IF g_sob_id <> NVL(l_prev_sob_id, -9999) THEN
923 get_segment_names;
924 END IF;
925
926 --Fetch prior year ending balances of accounts
927
928 l_select := 'SELECT glb.code_combination_id, glc.' || g_bal_segment ||
929 ' , glc.' || g_acc_segment || ', SUM (begin_balance_dr - begin_balance_cr
930 + NVL(period_net_dr,0) - NVL(period_net_cr,0)) end_bal
931 FROM gl_balances glb,gl_code_combinations GLC
932 WHERE glb.actual_flag = ' || '''' || 'A' || '''' || '
933 AND period_year = :g_fiscal_year
934 AND period_num = :g_period_num_high
935 AND glb.ledger_id = :g_set_of_books_id
936 AND glb.template_id is NULL
937 AND glb.currency_code <> ''STAT''
938 AND glc.code_combination_id = glb.code_combination_id
939 AND glc.' || g_acc_segment || '= ' || '''' ||
940 acct_rec.account_number || '''' ||
941 ' GROUP BY glb.code_combination_id,' || 'glc.' ||
942 g_bal_segment || ', glc.' || g_acc_segment || ' HAVING SUM (begin_balance_dr - begin_balance_cr
943 + NVL(period_net_dr,0) - NVL(period_net_cr,0)) <> 0
944 ORDER BY ' || 'glc.' || g_bal_segment || ', glc.' ||
945 g_acc_segment;
946
947 log(l_module, l_select);
948 --dbms_output.put_line(l_select);
949
950 OPEN gl_record FOR l_select
951 USING g_fiscal_year - 1, g_period_num_high, g_sob_id;
952
953 LOOP
954 l_ccid := NULL;
955 l_fund_value := NULL;
956 l_account_number := NULL;
957 l_amount := 0;
958
959 FETCH gl_record
960 INTO l_ccid, l_fund_value, l_account_number, l_amount;
961
962 IF l_ccid IS NOT NULL THEN
963 log(l_module,
964 'ccid: ' || l_ccid || ' fund: ' || l_fund_value ||
965 ' account: ' || l_account_number);
966
967 --Get the gtas attributes for the account
968 --if it exists in fv gtas attributes
969 --If not, then get the ussgl account and get its attributes
970 l_temp_acct_num := NULL;
971
972 BEGIN
973 SELECT gtas_acct_number
974 INTO l_temp_acct_num
975 FROM fv_gtas_attributes
976 WHERE set_of_books_id = g_sob_id
977 AND gtas_acct_number = l_account_number;
978
979 EXCEPTION
980 WHEN NO_DATA_FOUND THEN
981 log(l_module,
982 'Account: ' || l_account_number || ' not found.');
983 log(l_module, 'Trying ussgl account');
984
985 get_ussgl_acct_num(l_account_number, l_ussgl_acct_number);
986
987 BEGIN
988 SELECT ussgl_acct_number
989 INTO l_temp_acct_num
990 FROM fv_gtas_attributes
991 WHERE set_of_books_id = g_sob_id
992 AND gtas_acct_number = l_ussgl_acct_number;
993
994 EXCEPTION
995 WHEN NO_DATA_FOUND THEN
996
997 g_err_code := -1;
998 g_err_buff := l_module || ' - USSGL Account not found: ' ||
999 l_ussgl_acct_number;
1000 log(l_module,
1001 'USSGL Account not found: ' || l_ussgl_acct_number);
1002 RETURN;
1003 END;
1004 END;
1005
1006 --Fetch the fed non fed from gtas attributes
1007 --IF G is the only value in gtas attributes, assign G
1008 check_fed_nonfed(l_temp_acct_num, 'G', l_code_exists);
1009 IF l_code_exists = 'Y' THEN
1010 SELECT COUNT(*)
1011 INTO l_fednonfed_count
1012 FROM (SELECT fed_non_fed1
1013 FROM fv_gtas_attributes
1014 WHERE gtas_acct_number = l_temp_acct_num
1015 AND fed_non_fed1 IS NOT NULL
1016 AND set_of_books_id = g_sob_id
1017 UNION
1018 SELECT fed_non_fed2
1019 FROM fv_gtas_attributes
1020 WHERE gtas_acct_number = l_temp_acct_num
1021 AND fed_non_fed2 IS NOT NULL
1022 AND set_of_books_id = g_sob_id
1023 UNION
1024 SELECT fed_non_fed3
1025 FROM fv_gtas_attributes
1026 WHERE gtas_acct_number = l_temp_acct_num
1027 AND fed_non_fed3 IS NOT NULL
1028 AND set_of_books_id = g_sob_id);
1029
1030 IF l_fednonfed_count = 1 THEN
1031 log(l_module, 'fed nonfed code: G');
1032 l_fed_nonfed_code := 'G';
1033 END IF;
1034
1035 ELSE
1036 --If gtas attributes fed non fed includes N, assign N
1037 check_fed_nonfed(l_temp_acct_num, 'N', l_code_exists);
1038 IF l_code_exists = 'Y' THEN
1039 l_fed_nonfed_code := 'N';
1040 ELSE
1041 check_fed_nonfed(l_temp_acct_num, 'F', l_code_exists);
1042 --If gtas attributes fed non fed includes F, assign F
1043 IF l_code_exists = 'Y' THEN
1044 l_fed_nonfed_code := 'N';
1045 END IF;
1046 END IF;
1047 END IF;
1048
1049 IF l_fed_nonfed_code = 'G' THEN
1050 l_aid := '099';
1051 l_main_account := '0000';
1052 ELSE
1053 l_aid := NULL;
1054 l_main_account := NULL;
1055 END IF;
1056
1057 --If authority type flag on gtas fed accounts is Y
1058 --copy authority type code from facts attributes, else null
1059 l_authority_type_code := NULL;
1060 IF acct_rec.authority_type_flag = 'Y' THEN
1061 BEGIN
1062
1063 SELECT authority_type
1064 INTO l_authority_type_code
1065 FROM fv_facts_attributes
1066 WHERE set_of_books_id = acct_rec.set_of_books_id
1067 AND facts_acct_number = acct_rec.account_number;
1068
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND THEN
1071 log(l_module,
1072 'Authority Type Code not found for account: ' ||
1073 acct_rec.account_number);
1074 END;
1075 END IF;
1076
1077 IF l_amount >= 0 THEN
1078 l_d_c_indicator := 'D';
1079 ELSE
1080 l_d_c_indicator := 'C';
1081 END IF;
1082
1083 INSERT INTO fv_gtas_ending_balances
1084 (set_of_books_id,
1085 fiscal_year,
1086 ccid,
1087 fund_value,
1088 account_number,
1089 d_c_indicator,
1090 fed_non_fed,
1091 trading_partner_agency_id,
1092 trading_partner_main_account,
1093 amount,
1094 authority_type_code)
1095 VALUES
1096 (g_sob_id,
1097 g_fiscal_year - 1,
1098 l_ccid,
1099 l_fund_value,
1100 l_account_number,
1101 l_d_c_indicator,
1102 l_fed_nonfed_code,
1103 l_aid,
1104 l_main_account,
1105 l_amount,
1106 l_authority_type_code);
1107
1108 END IF; --if l_ccid is not null
1109 EXIT WHEN gl_record%NOTFOUND;
1110 l_prev_sob_id := g_sob_id;
1111 END LOOP;
1112
1113 END LOOP;
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 g_err_code := SQLCODE;
1117 g_err_buff := l_module || ' - When others error: ' || SQLERRM;
1118 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module, g_err_buff);
1119 END PROCESS_NON_FACTS_EB;
1120 --------------------------------------------------------------------------------
1121 BEGIN
1122 g_module := 'fv.plsql.FV_UPG_FACTS_EB_TO_GTAS.';
1123 --------------------------------------------------------------------------------
1124 END FV_UPG_FACTS_EB_TO_GTAS;