[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS1_PKG
Source
1 PACKAGE BODY FV_FACTS1_PKG AS
2 /* $Header: FVFCFIPB.pls 120.2.12000000.5 2007/07/31 14:21:45 sasukuma ship $ */
3 --------------------------------------------------------------------------------
4 g_module_name VARCHAR2(200);
5 gbl_set_of_books_id gl_ledgers_public_v.ledger_id%TYPE;
6 gbl_period_name gl_period_statuses.period_name%TYPE;
7 gbl_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE;
8 gbl_error_code NUMBER;
9 gbl_error_buf VARCHAR2(300);
10 gbl_run_type VARCHAR2(1);
11 gbl_fiscal_year gl_period_statuses.period_year%TYPE;
12 gbl_upd_begin_bal VARCHAR2(1);
13 gbl_period_num_low gl_period_statuses.period_num%TYPE;
14 gbl_period_num_high gl_period_statuses.period_num%TYPE;
15 gbl_bal_segment_name VARCHAR2(10);
16 gbl_acc_segment_name VARCHAR2(10);
17 gbl_acc_value_set_id NUMBER;
18 gbl_update_end_balance VARCHAR2(1);
19 gbl_currency_code gl_sets_of_books.currency_code%TYPE;
20 gbl_low_period_name gl_period_statuses.period_name%TYPE;
21 gbl_prev_acct fv_facts_report_t2.account_number%TYPE;
22 gbl_bal_segment fv_facts_report_t2.fund_value%TYPE;
23 gbl_sgl_acct_num VARCHAR2(4);
24 gbl_govt_non_govt_ind fv_facts1_period_attributes.g_ng_indicator%TYPE;
25 gbl_exch_non_exch fv_facts1_period_attributes.exch_non_exch%TYPE;
26 gbl_cust_non_cust fv_facts1_period_attributes.cust_non_cust%TYPE;
27 gbl_budget_subfunction fv_facts1_period_attributes.budget_subfunction%TYPE;
28 gbl_ene_exception VARCHAR2(25);
29 gbl_cnc_exception VARCHAR2(25);
30 gbl_bsf_exception VARCHAR2(25);
31 gbl_exception_category VARCHAR2(25);
32 gbl_dbr_flag NUMBER(1);
33 gbl_exception_exists varchar2(1) := 'N';
34 gbl_header_printed BOOLEAN := FALSE;
35
36 vg_acct_number VARCHAR2(30);
37 vg_fed_nonfed VARCHAR2(1);
38 vg_sgl_acct_number VARCHAR2(30);
39
40 --------------------------------------------------------------------------------
41 PROCEDURE get_segment_names;
42 PROCEDURE submit_exception_report;
43 PROCEDURE process_input_parameters;
44 PROCEDURE fund_group_info_setup;
45 PROCEDURE process_t1_records;
46 PROCEDURE get_fund_group_info
47 (p_fund_value IN VARCHAR2,
48 p_exists OUT NOCOPY VARCHAR2,
49 p_fg_null OUT NOCOPY VARCHAR2,
50 p_fund_group OUT NOCOPY VARCHAR2,
51 p_dept_id OUT NOCOPY VARCHAR2,
52 p_bureau_id OUT NOCOPY VARCHAR2);
53 PROCEDURE populate_temp2
54 ( p_fund_group IN Number,
55 p_account_number IN Varchar2,
56 p_dept_id IN Varchar2,
57 p_bureau_id IN Varchar2,
58 p_eliminations_dept IN Varchar2,
59 p_g_ng_indicator IN Varchar2,
60 p_amount IN Number,
61 p_d_c_indicator IN Varchar2,
62 p_fiscal_year IN Number,
63 p_record_category IN Varchar2,
64 p_ussgl_account IN Varchar2,
65 p_set_of_books_id IN Number,
66 p_reported_status IN Varchar2,
67 p_exch_non_exch IN Varchar2,
68 p_cust_non_cust IN Varchar2,
69 p_budget_subfunction IN Varchar2,
70 p_fund_value IN Varchar2,
71 p_beginning_bal IN Number,
72 p_ccid IN Number,
73 p_account_type IN Varchar2,
74 p_recipient_name IN Varchar2,
75 p_dr_amount IN Number,
76 p_cr_amount IN Number);
77 PROCEDURE cleanup_process;
78 PROCEDURE get_ussgl_acct_num
79 (p_acct_num IN Varchar2,
80 p_fund_value IN Varchar2,
81 p_sgl_acct_num OUT NOCOPY Number,
82 p_govt_non_govt OUT NOCOPY Varchar2,
83 p_exch_non_exch OUT NOCOPY Varchar2,
84 p_cust_non_cust OUT NOCOPY Varchar2,
85 p_budget_subfunction OUT NOCOPY Varchar2,
86 p_ene_exception OUT NOCOPY Varchar2,
87 p_cnc_exception OUT NOCOPY Varchar2,
88 p_bsf_exception OUT NOCOPY Varchar2,
89 p_exception_category OUT NOCOPY Varchar2);
90 FUNCTION get_account_type
91 (p_account_number VARCHAR2) RETURN VARCHAR2;
92 PROCEDURE get_ussgl_info
93 (p_ussgl_acct_num IN Varchar2,
94 p_enabled_flag IN OUT NOCOPY Varchar2,
95 p_reporting_type IN OUT NOCOPY Varchar2);
96 PROCEDURE edit_check(p_period_num in VARCHAR2,
97 p_period_year in VARCHAR2,
98 p_set_of_books_id in VARCHAR2,
99 p_status out nocopy varchar2);
100 PROCEDURE create_end_bal_record;
101
102 PROCEDURE update_facts1_run(p_period_year in VARCHAR2,
103 p_set_of_books_id in VARCHAR2);
104 PROCEDURE POPULATE_FV_FACTS_FED_ACCOUNTS;
105 -----addded for TB report --------------------------------------
106 PROCEDURE journal_processes;
107 PROCEDURE rollup_process;
108 -- Global Variables for Trial Balance processing
109 gbl_trial_balance_type Varchar2(1) := NULL;
110 gbl_treasury_symbol_id FV_Treasury_Symbols.treasury_symbol_id%TYPE;
111 gbl_fund_range_low FV_Fund_Parameters.fund_value%TYPE;
112 gbl_fund_range_high FV_Fund_Parameters.fund_value%TYPE;
113 gbl_period_num Gl_Balances.period_num%TYPE;
114 gbl_period_year gl_period_statuses.period_year%TYPE;
115
116 -- Global Variable for RXi
117 gbl_report_id FA_RX_Reports_V.report_id%TYPE;
118 gbl_attribute_set FA_RX_Rep_Columns_B.attribute_set%TYPE;
119 gbl_output_format Varchar2(30);
120 gbl_run_mode VARCHAR2(1);
121
122 gbl_parent_flag VARCHAR2(1);
123
124 --------------------------------------------------------------------------------
125 PROCEDURE MAIN(p_err_buff OUT NOCOPY VARCHAR2,
126 p_err_code OUT NOCOPY NUMBER,
127 p_sob_id IN NUMBER,
128 p_coa_id IN NUMBER,
129 p_run_type IN VARCHAR2,
130 p_period_name IN VARCHAR2,
131 p_fiscal_year IN NUMBER,
132 p_run_journal IN VARCHAR2,
133 p_run_reports IN VARCHAR2,
134 p_trading_partner_att IN VARCHAR2
135 )
136
137 IS
138
139 l_module_name VARCHAR2(200);
140 l_edit_check_status VARCHAR2(1);
141 l_run_mode VARCHAR2(25);
142 l_req_id NUMBER;
143 l_print_option BOOLEAN;
144 l_printer_name VARCHAR2(240);
145 call_status BOOLEAN;
146 l_copies NUMBER;
147 rphase VARCHAR2(80);
148 rstatus VARCHAR2(80);
149 dphase VARCHAR2(80);
150 dstatus VARCHAR2(80);
151 message VARCHAR2(80);
152 l_exception_count NUMBER;
153 l_exception_count2 NUMBER;
154 l_error_buf varchar2(2000);
155 l_error_code Number(15);
156 l_run_status varchar2(1);
157 l_row_exists NUMBER;
158
159 BEGIN
160
161 l_module_name := g_module_name || 'MAIN';
162 FV_UTILITY.LOG_MESG('In '||l_module_name);
163
164 l_edit_check_status := 'N';
165 l_run_mode := NULL;
166 l_printer_name := FND_PROFILE.VALUE('PRINTER');
167 l_copies := FND_PROFILE.VALUE('CONC_COPIES');
168
169 gbl_error_code := 0;
170 gbl_error_buf := NULL;
171 gbl_set_of_books_id := p_sob_id;
172 gbl_coa_id := p_coa_id;
173 gbl_run_type := p_run_type;
174 gbl_period_name := p_period_name;
175 gbl_fiscal_year := p_fiscal_year;
176 gbl_period_year := p_fiscal_year;
177
178 FV_UTILITY.LOG_MESG('Parameters ');
179 FV_UTILITY.LOG_MESG('---------- ');
180 FV_UTILITY.LOG_MESG('SOB ID: '||gbl_set_of_books_id);
181 FV_UTILITY.LOG_MESG('COA ID: '||gbl_coa_id);
182 FV_UTILITY.LOG_MESG('Run Type: '||gbl_run_type);
183 FV_UTILITY.LOG_MESG('Period: '||gbl_period_name);
184 FV_UTILITY.LOG_MESG('Fiscal Year: '||gbl_fiscal_year);
185 FV_UTILITY.LOG_MESG('Run Journal creation : '||p_run_journal);
186 FV_UTILITY.LOG_MESG('Trading Partner Attribute: '||p_trading_partner_att);
187
188 get_segment_names;
189
190 IF gbl_error_code = 0 THEN
191 process_input_parameters;
192 END IF;
193
194 IF gbl_error_code = 0 THEN
195 cleanup_process;
196 END IF;
197
198 gbl_exception_exists := 'N';
199
200 IF (gbl_run_type = 'Y') THEN
201 l_run_mode := 'Fiscal Year';
202 ELSIF (gbl_run_type = 'R') THEN
203 l_run_mode := 'Period';
204 END IF;
205
206 IF p_run_journal = 'Y' THEN
207
208 fv_utility.log_mesg('Calling Journal Creation process.');
209 l_req_id := FND_REQUEST.SUBMIT_REQUEST
210 ('FV','FVFC1JCR','','',FALSE, gbl_set_of_books_id, gbl_period_name,'I',
211 p_trading_partner_att);
212 FV_UTILITY.LOG_MESG(l_module_name||
213 ' REQUEST ID FOR JOURNAL CREATION PROCESS = '|| TO_CHAR(L_REQ_ID));
214 IF (l_req_id = 0) THEN
215 gbl_error_code := -1;
216 gbl_error_buf := ' Cannot submit FACTS Journal Creation process';
217 fv_utility.log_mesg(gbl_error_buf);
218 p_err_code := -1;
219 p_err_buff := gbl_error_buf;
220 RETURN;
221 ELSE
222 COMMIT;
223 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
224 rphase, rstatus,
225 dphase, dstatus, message);
226 IF call_status = FALSE THEN
227 gbl_error_buf := 'Cannot wait for the status of Journal Creation Process';
228 gbl_error_code := -1;
229 FV_UTILITY.LOG_MESG(l_module_name|| '.error4', gbl_error_buf) ;
230 p_err_code := -1;
231 p_err_buff := gbl_error_buf;
232 RETURN;
233 END IF;
234 END IF;
235 END IF;
236
237
238 IF gbl_error_code = 0 THEN
239 fv_utility.log_mesg('Calling Facts Attributes Creation process.');
240 SET_UP_FACTS_ATTRIBUTES(l_error_buf ,
241 l_error_code ,
242 gbl_set_of_books_id ,
243 gbl_fiscal_year);
244 gbl_error_code := l_error_code;
245 gbl_error_buf := l_error_buf;
246 END IF;
247
248
249 IF gbl_error_code = 0 THEN
250 FV_UTILITY.LOG_MESG('Calling Exception report');
251 submit_exception_report;
252 End if;
253
254
255 -- Peforming Edit check process
256 IF gbl_error_code = 0 THEN
257 FV_UTILITY.LOG_MESG('Calling Edit check');
258 EDIT_CHECK(GBL_PERIOD_NUM_HIGH , GBL_FISCAL_YEAR, GBL_SET_OF_BOOKS_ID, L_EDIT_CHECK_STATUS);
259 FV_UTILITY.LOG_MESG('Edit check status: '||l_edit_check_status);
260
261 End if;
262
263 -- Submit reports only if edit check is passed.
264 IF (gbl_error_code = 0 AND l_edit_check_status = 'Y' and p_run_reports = 'Y' ) then
265
266 --Populate ending balances only if it is run in year mode or
267 --if it is run by period then, only if period_num_high is the
268 --last period num of the the year.
269 --IF (gbl_update_end_balance = 'Y' OR gbl_run_type = 'Y') THEN
270 IF (gbl_run_type = 'Y') THEN
271
272 SELECT count(*)
273 INTO l_row_exists
274 FROM fv_facts_ending_balances
275 WHERE fiscal_year = gbl_period_year
276 AND set_of_books_id = gbl_set_of_books_id
277 AND rownum = 1;
278
279 IF (l_row_exists > 0) THEN
280 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,
282 ' DELETING RECORDS FROM FV_FACTS_ENDING_BALANCES FOR
283 THE YEAR = '|| GBL_PERIOD_YEAR);
284 END IF;
285 fv_utility.log_mesg('Deleting recs from fv_facts_ending_balances
286 for Period Year: '||gbl_period_year);
287
288 DELETE FROM fv_facts_ending_balances
289 WHERE set_of_books_id = gbl_set_of_books_id
290 AND fiscal_year = gbl_period_year;
291 fv_utility.log_mesg('Deleted '||SQL%ROWCOUNT ||' recs from fv_facts_ending_balances.');
292 COMMIT;
293 END IF;
294 create_end_bal_record;
295 END IF;
296
297 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS(
298 printer => l_printer_name,
299 copies => l_copies);
300
301 FV_UTILITY.LOG_MESG(l_module_name|| ' LAUNCHING FACTS I ATB FILE GENERATION PROCESS ...');
302
303 -- Submit ATB file process
304 l_req_id := FND_REQUEST.SUBMIT_REQUEST
305 ('FV','FVFACTSR','','',FALSE, 'FVFC1ATB', gbl_fiscal_year,
306 gbl_set_of_books_id, gbl_period_num_high);
307
308 FV_UTILITY.LOG_MESG(l_module_name|| ' REQUEST ID FOR ATB FILE = '|| TO_CHAR(L_REQ_ID));
309
310 -- if concurrent request submission failed then abort process
311 IF (l_req_id = 0) THEN
312 p_err_code := '-1';
313 p_err_buff := ' Cannot submit FACTS report ATB file process';
314 RETURN;
315 FV_UTILITY.LOG_MESG(l_module_name||gbl_error_buf);
316 ELSE
317 COMMIT;
318 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
319 rphase, rstatus,
320 dphase, dstatus, message);
321 IF call_status = FALSE THEN
322 p_err_buff := 'Cannot wait for the status of FACTS ATB Report';
323 p_err_code := -1;
324 FV_UTILITY.LOG_MESG(l_module_name||
325 '.error4', gbl_error_buf) ;
326 RETURN;
327 END IF;
328 END IF;
329
330 END IF; /*EDIT CHECK PASSED */
331
332 if (p_run_reports = 'Y' or l_edit_check_status = 'N') then
333 -- Print the FACTS I Detail Report
334 IF (gbl_error_code = 0) THEN
335 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS(
336 printer => l_printer_name,
337 copies => l_copies);
338
339 -- Submit FACTS I Detail Report concurrent program
340 FV_UTILITY.LOG_MESG(l_module_name||
341 ' LAUNCHING FACTS I DETAIL REPORT ...');
342
343 l_req_id := FND_REQUEST.SUBMIT_REQUEST
344 ('FV','FVFACTDR','','',FALSE, gbl_set_of_books_id, l_run_mode, gbl_fiscal_year,
345 p_period_name, gbl_period_num_high);
346
347 FV_UTILITY.LOG_MESG(l_module_name||
348 ' REQUEST ID FOR DETAIL REPORT = '|| TO_CHAR(L_REQ_ID));
349
350 -- If concurrent request submission failed then abort process
351 IF (l_req_id = 0) THEN
352 p_err_code := '-1';
353 p_err_buff := ' Cannot submit FACTS Detail report';
354 FV_UTILITY.LOG_MESG(l_module_name||gbl_error_buf);
355 RETURN;
356 ELSE
357 COMMIT;
358 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
359 rphase, rstatus,
360 dphase, dstatus, message);
361 IF call_status = FALSE THEN
362 p_err_buff := 'Cannot wait for the status of FACTS Detail Report';
363 p_err_code := -1;
364 FV_UTILITY.LOG_MESG(l_module_name||'.error4', gbl_error_buf) ;
365 RETURN;
366 END IF;
367 END IF;
368 END IF;
369
370 END IF; -- /* run reports */
371
372 IF gbl_error_code <> 0 THEN
373 p_err_code := gbl_error_code;
374 p_err_buff := gbl_error_buf;
375 ROLLBACK;
376 RETURN;
377 END IF;
378
379 --IF l_edit_check_status = 'Y' THEN
380 --UPDATE_FACTS1_RUN(GBL_PERIOD_NUM_HIGH, GBL_FISCAL_YEAR, GBL_SET_OF_BOOKS_ID, 'S');
381
382 UPDATE fv_facts1_run
383 SET status = decode(l_edit_check_status , 'Y', 'S' , 'F'),
384 run_fed_flag = 'I',
385 process_date = sysdate,
386 begin_bal_diff_flag = 'Y',
387 period_num = gbl_period_num_high
388 WHERE set_of_books_id = gbl_set_of_books_id
389 AND fiscal_year = gbl_fiscal_year
390 AND table_indicator = 'N';
391
392
393 -- END IF;
394
395 COMMIT;
396
397 FV_UTILITY.LOG_MESG('Facts I Main Process completed successfully.');
398 p_err_buff := 'Facts I Main Process completed successfully.';
399
400
401 EXCEPTION WHEN OTHERS THEN
402 p_err_code := SQLCODE;
403 p_err_buff := 'When others exception in Main - '||SQLERRM;
404 ROLLBACK;
405 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
406
407 END main;
408 --------------------------------------------------------------------------------
409 -- Get balancing and accounting segments
410 --------------------------------------------------------------------------------
411 PROCEDURE GET_SEGMENT_NAMES
412 IS
413
414 l_module_name VARCHAR2(200);
415 l_temp_mesg VARCHAR2(100);
416 l_app_id NUMBER := 101;
417 l_flex_code VARCHAR2(10) := 'GL#';
418 l_segment_found BOOLEAN;
419 invalid_bal_segment EXCEPTION;
420 invalid_acc_segment EXCEPTION;
421
422 BEGIN
423
424 l_module_name := g_module_name || 'GET_SEGMENT_NAMES';
425 FV_UTILITY.LOG_MESG('In '||l_module_name);
426
427 l_temp_mesg := ' getting balancing/accounting segment. ';
428
429
430 SELECT chart_of_accounts_id
431 INTO gbl_coa_id
432 FROM gl_ledgers_public_v
433 WHERE ledger_id = gbl_set_of_books_id;
434
435 FV_UTILITY.LOG_MESG('COA ID: '||gbl_coa_id);
436
437 -- Get Balancing Segment Name
438 -----------------------------
439 l_segment_found := FND_FLEX_APIS.get_segment_column
440 (l_app_id,
441 l_flex_code,
442 gbl_coa_id,
443 'GL_BALANCING',
444 gbl_bal_segment_name) ;
445
446 IF NOT l_segment_found THEN
447 RAISE invalid_bal_segment;
448 END IF;
449
450 -- Get Accounting Segment Name
451 ------------------------------
452 l_segment_found := FND_FLEX_APIS.get_segment_column
453 (l_app_id,
454 l_flex_code,
455 gbl_coa_id,
456 'GL_ACCOUNT',
457 gbl_acc_segment_name);
458 IF NOT l_segment_found THEN
459 RAISE invalid_acc_segment;
460 END IF;
461
462 -- Get the value set id
463 l_temp_mesg := ' getting account value set id. ';
464 SELECT flex_value_set_id
465 INTO gbl_acc_value_set_id
466 FROM fnd_id_flex_segments
467 WHERE application_column_name = gbl_acc_segment_name
468 AND id_flex_code = 'GL#'
469 AND id_flex_num = gbl_coa_id;
470
471 FV_UTILITY.LOG_MESG('Balancing Segment: '||gbl_bal_segment_name);
472 FV_UTILITY.LOG_MESG('Accounting Segment: '||gbl_acc_segment_name);
473 FV_UTILITY.LOG_MESG('Accounting value set id: '||gbl_acc_value_set_id);
474
475 EXCEPTION
476 WHEN invalid_bal_segment THEN
477 gbl_error_code := -1 ;
478 gbl_error_buf := 'Error while fetching balancing segment.';
479 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
480 WHEN invalid_acc_segment THEN
481 gbl_error_code := -1 ;
482 gbl_error_buf := 'Error while fetching accounting segment.';
483 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
484 WHEN NO_DATA_FOUND THEN
485 gbl_error_code := -1 ;
486 gbl_error_buf := l_module_name||' - No data found when'||l_temp_mesg;
487 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
488 WHEN OTHERS THEN
489 gbl_error_code := -1 ;
490 gbl_error_buf := l_module_name||' - When others error when'||
491 l_temp_mesg||SQLERRM;
492 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
493
494 END get_segment_names;
495 --------------------------------------------------------------------------------
496 -- PROCEDURE PROCESS_INPUT_PARAMETERS
497 --------------------------------------------------------------------------------
498 -- Identify the type of input parameters passed, whether fiscal year is passed
499 -- or period is passed. p_run_type determines the parameter passed. Valid
500 -- parameter type values are 'Y', indicating year and 'R', indicating period.
501 -- Global variables 'gbl_period_num_low' and 'gbl_period_num_high'
502 -- are loaded with the derived period number range.
503 --------------------------------------------------------------------------------
504 PROCEDURE PROCESS_INPUT_PARAMETERS
505
506 IS
507 l_module_name VARCHAR2(200);
508 l_temp_mesg VARCHAR2(100);
509 l_year NUMBER;
510 l_closing_status VARCHAR2(1);
511 l_end_period_num NUMBER;
512
513 BEGIN
514
515 l_module_name := g_module_name || 'PROCESS_INPUT_PARAMETERS';
516 FV_UTILITY.LOG_MESG('In '||l_module_name);
517
518 -- Error out if the required parameters are null.
519 IF (gbl_run_type = 'Y' AND gbl_fiscal_year IS NULL) OR
520 (gbl_run_type = 'R' AND gbl_period_name IS NULL) THEN
521 gbl_error_code := -1;
522 gbl_error_buf := 'Period Name is required if Run Type is R or '||
523 'Fiscal Year is required if Run Type is Y.';
524 FV_UTILITY.LOG_MESG(gbl_error_buf);
525 RETURN;
526 END IF;
527
528 -- Parameter type will be Y if year is passed and R
529 -- if period is passed.
530 IF gbl_run_type = 'Y' THEN
531
532 l_temp_mesg := ' getting first period of the year. ';
533 SELECT MIN(period_num)
534 INTO gbl_period_num_low
535 FROM gl_period_statuses
536 WHERE period_year = gbl_fiscal_year
537 AND application_id = 101
538 AND closing_status <> 'F'
539 AND closing_status <> 'N'
540 AND adjustment_period_flag = 'N'
541 AND ledger_id = gbl_set_of_books_id;
542
543 IF gbl_period_num_low = 0 THEN
544 RAISE NO_DATA_FOUND;
545 END IF;
546
547 l_temp_mesg := ' getting last period of the year. ';
548 SELECT MAX(period_num)
549 INTO gbl_period_num_high
550 FROM gl_period_statuses
551 WHERE period_year = gbl_fiscal_year
552 AND application_id = 101
553 AND closing_status <> 'F'
554 AND closing_status <> 'N'
555 AND ledger_id = gbl_set_of_books_id;
556
557 IF gbl_period_num_high = 0 THEN
558 RAISE NO_DATA_FOUND;
559 END IF;
560
561 l_temp_mesg := ' getting period name for last period of the year. ';
562 SELECT period_name
563 INTO gbl_period_name
564 FROM gl_period_statuses
565 WHERE period_num = gbl_period_num_high
566 AND period_year = gbl_fiscal_year
567 AND application_id = 101
568 AND ledger_id = gbl_set_of_books_id;
569
570 ELSE -- p_parameter_type = 'P'
571
572 -- Period name is passed, get the fiscal year and
573 -- the period number.
574 l_temp_mesg := ' getting period num/fiscal year for the period passed. ';
575 SELECT period_num, period_year, closing_status
576 INTO gbl_period_num_high, gbl_fiscal_year, l_closing_status
577 FROM gl_period_statuses
578 WHERE period_name = gbl_period_name
579 AND application_id = 101
580 AND ledger_id = gbl_set_of_books_id;
581
582 gbl_period_year := gbl_fiscal_year;
583
584
585 -- If the passed period status is F or N then get the period
586 -- number of the next lower period whose status is not F or N.
587 IF l_closing_status IN ('F' , 'N') THEN
588 l_temp_mesg := ' getting lower period number for the period passed. ';
589 SELECT Max(period_num)
590 INTO gbl_period_num_high
591 FROM gl_period_statuses
592 WHERE period_year = gbl_fiscal_year
593 AND application_id = 101
594 AND closing_status <> 'F'
595 AND closing_status <> 'N'
596 AND period_num <= gbl_period_num_high
597 AND ledger_id = gbl_set_of_books_id;
598 END IF;
599
600 l_temp_mesg := ' getting first period of the year. ';
601 SELECT MIN(period_num)
602 INTO gbl_period_num_low
603 FROM gl_period_statuses
604 WHERE period_year = gbl_fiscal_year
605 AND application_id = 101
606 AND adjustment_period_flag = 'N'
607 AND ledger_id = gbl_set_of_books_id;
608
609 IF gbl_period_num_low IS NULL THEN
610 RAISE NO_DATA_FOUND;
611 END IF;
612
613 l_temp_mesg := ' getting last period of the year. ';
614 SELECT MAX(period_num)
615 INTO l_end_period_num
616 FROM gl_period_statuses
617 WHERE period_year = gbl_fiscal_year
618 AND application_id = 101
619 AND ledger_id = gbl_set_of_books_id;
620
621 IF gbl_period_num_high IS NULL THEN
622 RAISE NO_DATA_FOUND;
623 END IF;
624
625 -- If the period being run for is the end period of the fiscal year
626 IF l_end_period_num = gbl_period_num_high THEN
627 gbl_update_end_balance := 'Y';
628 END IF;
629
630 END IF; -- p_parameter_type
631
632 IF gbl_period_num_low > gbl_period_num_high THEN
633 gbl_error_code := -1;
634 gbl_error_buf := 'PROCESS INPUT PARAMETERS - Period Number for ' ||
635 'Lower Period of the Range is greater than the ' ||
636 'Higher period.';
637 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
638 RETURN;
639 END IF;
640
641 l_temp_mesg := ' getting period name of first period of the year. ';
642 SELECT period_name
643 INTO gbl_low_period_name
644 FROM gl_period_statuses
645 WHERE period_num = gbl_period_num_low
646 AND period_year = gbl_fiscal_year
647 AND application_id = 101
648 AND ledger_id = gbl_set_of_books_id;
649
650 l_temp_mesg := ' getting currency code. ';
651 SELECT currency_code
652 INTO gbl_currency_code
653 FROM gl_ledgers_public_v
654 WHERE ledger_id = gbl_set_of_books_id;
655
656 IF gbl_currency_code IS NULL THEN
657 RAISE NO_DATA_FOUND;
658 END IF;
659
660 FV_UTILITY.LOG_MESG('Period low: '||gbl_period_num_low);
661 FV_UTILITY.LOG_MESG('Period high: '||gbl_period_num_high);
662 FV_UTILITY.LOG_MESG('Fiscal year: '||gbl_fiscal_year);
663 FV_UTILITY.LOG_MESG('Currency Code: '||gbl_currency_code);
664
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667 gbl_error_code := -1 ;
668 gbl_error_buf := l_module_name||' - No data found when '||l_temp_mesg;
669 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
670
671 WHEN OTHERS THEN
672 gbl_error_code := SQLCODE ;
673 gbl_error_buf := ' - When others error when '||l_temp_mesg||SQLERRM;
674 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
675 END process_input_parameters;
676 --------------------------------------------------------------------------------
677 -- FUND_GROUP_INFO_SETUP
678 -- Update fv_fund_parameters table with the required info.
679 --------------------------------------------------------------------------------
680 PROCEDURE FUND_GROUP_INFO_SETUP
681 IS
682 l_module_name VARCHAR2(200);
683 cnt BINARY_INTEGER := 0;
684 l_hash BINARY_INTEGER := 0;
685 l_fund_group fv_treasury_symbols.fund_group_code%type;
686 l_fund_val fv_fund_parameters.fund_value%TYPE;
687 l_dept_id fv_treasury_symbols.department_id%TYPE;
688 l_bureau_id fv_treasury_symbols.bureau_id%TYPE;
689 ln_fund_group_type fv_fund_groups.type%type ;
690 ln_facts1_rollup fv_fund_groups.fund_group_code%TYPE;
691
692 CURSOR fund_cur IS
693 SELECT ffp.fund_value fund_val, fts.fund_group_code fund_grp,
694 fts.department_id dep_id, fts.bureau_id bu_id
695 FROM fv_treasury_symbols fts, fv_fund_parameters ffp
696 WHERE ffp.set_of_books_id = gbl_set_of_books_id
697 AND fts.treasury_symbol_id = ffp.treasury_symbol_id;
698
699 BEGIN
700
701 l_module_name := g_module_name || 'FUND_GROUP_INFO_SETUP';
702 FV_UTILITY.LOG_MESG('In '||l_module_name);
703
704 gbl_error_code := 0;
705 gbl_error_buf := Null;
706
707 FOR fund_rec IN fund_cur
708 LOOP
709
710 l_fund_group := fund_rec.fund_grp;
711 l_dept_id := fund_rec.dep_id;
712 l_bureau_id := fund_rec.bu_id;
713
714 IF (l_fund_group IS NULL) THEN
715 l_fund_group := NULL;
716 l_dept_id := NULL;
717 l_bureau_id := NULL;
718 ELSE
719
720 -- Set the Fund Group
721 DECLARE
722 CURSOR facts1_rollup_cur IS
723 SELECT facts1_rollup
724 FROM fv_fund_groups
725 WHERE fund_group_code = l_fund_group
726 AND set_of_books_id = gbl_set_of_books_id;
727 BEGIN
728 ln_facts1_rollup := NULL;
729
730 OPEN facts1_rollup_cur;
731 FETCH facts1_rollup_cur INTO ln_facts1_rollup;
732 CLOSE facts1_rollup_cur;
733
734 IF ln_facts1_rollup IS NOT NULL THEN
735 l_fund_group := ln_facts1_rollup;
736 END IF;
737 END;
738 END IF;
739
740 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
741 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,
742 ' Fund Group: '||l_fund_group);
743 END IF;
744
745 --fv_utility.log_mesg('**********fund_value: '||fund_rec.fund_val);
746 --fv_utility.log_mesg('**********fund_group_code: '||l_fund_group);
747
748 UPDATE fv_fund_parameters
749 SET department_id = fund_rec.dep_id,
750 bureau_id = fund_rec.bu_id,
751 fund_group_code = l_fund_group
752 WHERE fund_value = fund_rec.fund_val
753 AND set_of_books_id = gbl_set_of_books_id;
754
755 END LOOP;
756
757
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
761 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,
762 ' No Data Found for fund group.');
763 END IF;
764
765 WHEN Others THEN
766 gbl_error_code := -1 ;
767 gbl_error_buf := l_module_name||' - When others exception - ' ||
768 to_char(sqlcode) || ' - ' || sqlerrm ;
769 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
770
771 END fund_group_info_setup;
772 --------------------------------------------------------------------------------
773 -- PROCEDURE PROCESS_T1_RECORDS
774 --------------------------------------------------------------------------------
775 PROCEDURE PROCESS_T1_RECORDS
776 IS
777
778 l_module_name VARCHAR2(100);
779 l_bal_segment VARCHAR2(30);
780 l_bal_segment_prv VARCHAR2(30);
781 l_diff_flag varchar2(1);
782 l_ending_amount NUMBER := 0;
783 l_t2_detail_amount NUMBER := 0;
784 l_stage varchar2(20);
785
786
787 /** moved the code to SET_UP_FACTS_ATTRIUTES */
788 begin
789
790 null;
791
792
793 END process_t1_records;
794 --------------------------------------------------------------------------------
795 -- PROCEDURE GET_FUND_GROUP_INFO
796 --------------------------------------------------------------------------------
797 -- Get the Fund Group, Dept Id, Bureau Id and from the fv_fund_parameters
798 -- table for the passed fund value.
799 --------------------------------------------------------------------------------
800 PROCEDURE GET_FUND_GROUP_INFO
801 ( p_fund_value IN VARCHAR2,
802 p_exists OUT NOCOPY VARCHAR2,
803 p_fg_null OUT NOCOPY VARCHAR2,
804 p_fund_group OUT NOCOPY VARCHAR2,
805 p_dept_id OUT NOCOPY VARCHAR2,
806 p_bureau_id OUT NOCOPY VARCHAR2)
807
808 IS
809
810 l_module_name VARCHAR2(200);
811
812 BEGIN
813
814 l_module_name := g_module_name || 'GET_FUND_GROUP_INFO';
815 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
816 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'In '||l_module_name);
817 END IF;
818 --FV_UTILITY.LOG_MESG('In '||l_module_name);
819
820 BEGIN
821 SELECT department_id,
822 bureau_id,
823 fund_group_code
824 INTO p_dept_id,
825 p_bureau_id,
826 p_fund_group
827 FROM fv_fund_parameters
828 WHERE fund_value = p_fund_value
829 AND set_of_books_id = gbl_set_of_books_id;
830 p_exists := 'Y';
831 EXCEPTION
832 WHEN NO_DATA_FOUND THEN
833 p_exists := 'N';
834 END;
835
836 IF (p_fund_group IS NULL) THEN
837 p_fg_null := 'Y';
838 ELSE
839 p_fg_null := 'N';
840 END IF;
841
842 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
843 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Fund Value: '||p_fund_value);
844 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Dept Id: '||p_dept_id);
845 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Bureau Id: '||p_bureau_id);
846 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Fund Group: '||p_fund_group);
847 END IF;
848
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN
851 gbl_error_buf := l_module_name||' No fund group data found for fund : ' || p_fund_value;
852 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
853 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,gbl_error_buf);
854 END IF;
855
856 WHEN OTHERS THEN
857 gbl_error_code := -1 ;
858 gbl_error_buf := l_module_name||' - When others exception - '||
859 to_char(SQLCODE) || ' - ' || SQLERRM ;
860 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
861
862 END get_fund_group_info;
863 --------------------------------------------------------------------------------
864 -- PROCEDURE POPULATE_TEMP2
865 --------------------------------------------------------------------------------
866 PROCEDURE POPULATE_TEMP2
867 ( p_fund_group IN Number,
868 p_account_number IN Varchar2,
869 p_dept_id IN Varchar2,
870 p_bureau_id IN Varchar2,
871 p_eliminations_dept IN Varchar2,
872 p_g_ng_indicator IN Varchar2,
873 p_amount IN Number,
874 p_d_c_indicator IN Varchar2,
875 p_fiscal_year IN Number,
876 p_record_category IN Varchar2,
877 p_ussgl_account IN Varchar2,
878 p_set_of_books_id IN Number,
879 p_reported_status IN Varchar2,
880 p_exch_non_exch IN Varchar2,
881 p_cust_non_cust IN Varchar2,
882 p_budget_subfunction IN Varchar2,
883 p_fund_value IN Varchar2,
884 p_beginning_bal IN Number,
885 p_ccid IN Number,
886 p_account_type IN Varchar2,
887 p_recipient_name IN Varchar2,
888 p_dr_amount IN Number,
889 p_cr_amount IN Number
890 )
891 IS
892 l_module_name VARCHAR2(200);
893 BEGIN
894
895 l_module_name := g_module_name||' POPULATE_TEMP2';
896
897 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
899 'Inserting a record in T2 for record_category :'||p_record_category||' for ccid: '||p_ccid);
900 END IF;
901
902 INSERT INTO fv_facts_report_t2
903 ( fund_group,
904 account_number,
905 dept_id,
906 bureau_id,
907 eliminations_dept,
908 g_ng_indicator,
909 amount,
910 d_c_indicator,
911 fiscal_year,
912 record_category,
913 ussgl_account,
914 set_of_books_id,
915 reported_status,
916 exch_non_exch,
917 cust_non_cust,
918 budget_subfunction,
919 fund_value,
920 beginning_balance,
921 ccid,
922 account_type,
923 recipient_name,
924 dr_amount,
925 cr_amount)
926 VALUES
927 ( p_fund_group,
928 p_account_number,
929 p_dept_id,
930 p_bureau_id,
931 p_eliminations_dept,
932 p_g_ng_indicator,
933 nvl(p_amount, 0),
934 DECODE(SIGN(nvl(p_amount, 0)), 0 ,'D', 1, 'D', -1, 'C'),
935 p_fiscal_year,
936 p_record_category,
937 p_ussgl_account,
938 p_set_of_books_id,
939 p_reported_status,
940 p_exch_non_exch,
941 p_cust_non_cust,
942 p_budget_subfunction,
943 p_fund_value,
944 p_beginning_bal,
945 p_ccid,
946 p_account_type,
947 p_recipient_name,
948 p_dr_amount,
949 p_cr_amount);
950
951 EXCEPTION
952 WHEN OTHERS THEN
953 gbl_error_code := -1;
954 gbl_error_buf := l_module_name||' - When others exception -'||
955 to_char(SQLCODE) || ' - ' || SQLERRM;
956 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
957
958 END populate_temp2;
959 --------------------------------------------------------------------------------
960 -- PROCEDURE GET_USSGL_ACCT_NUM
961 --------------------------------------------------------------------------------
962 -- Process the records to find exceptions and return the cust_non_cust,
963 -- exch_non_exch and no_val_subfunction exceptions individually
964 --------------------------------------------------------------------------------
965 PROCEDURE GET_USSGL_ACCT_NUM (p_acct_num IN Varchar2,
966 p_fund_value IN Varchar2,
967 p_sgl_acct_num OUT NOCOPY Number,
968 p_govt_non_govt OUT NOCOPY Varchar2,
969 p_exch_non_exch OUT NOCOPY Varchar2,
970 p_cust_non_cust OUT NOCOPY Varchar2,
971 p_budget_subfunction OUT NOCOPY Varchar2,
972 p_ene_exception OUT NOCOPY Varchar2,
973 p_cnc_exception OUT NOCOPY Varchar2,
974 p_bsf_exception OUT NOCOPY Varchar2,
975 p_exception_category OUT NOCOPY Varchar2)
976
977
978 IS
979
980 l_module_name VARCHAR2(200);
981 l_ussgl_acct_num VARCHAR2(4);
982 l_ussgl_enabled VARCHAR2(1);
983 l_reporting_type VARCHAR2(1);
984
985 l_exists VARCHAR2(1);
986 l_row_exists VARCHAR2(1);
987 l_g_ng_ind Fv_Facts_Report_T2.g_ng_indicator%TYPE;
988 l_e_ne_ind Fv_Facts_Attributes.exch_non_exch%TYPE;
989 l_c_nc_ind Fv_Facts_Attributes.cust_non_cust%TYPE;
990 l_c_nc Fv_Facts_Report_T2.cust_non_cust%TYPE;
991 l_budget_sub_ind Fv_Facts_Attributes.budget_subfunction%TYPE;
992 l_budget_sub Fv_Facts_Report_T2.budget_subfunction%TYPE;
993
994 BEGIN
995
996 l_module_name := g_module_name || 'GET_USSGL_ACCT_NUM';
997 --FV_UTILITY.LOG_MESG('In '||l_module_name);
998
999 l_exists := NULL;
1000 l_ussgl_enabled := NULL;
1001 l_reporting_type := NULL;
1002
1003 p_sgl_acct_num := NULL;
1004 p_govt_non_govt := NULL;
1005 p_exch_non_exch := NULL;
1006 p_cust_non_cust := NULL;
1007 p_budget_subfunction:= NULL;
1008
1009 p_exception_category:= NULL;
1010 p_bsf_exception := NULL;
1011 p_cnc_exception := NULL;
1012 p_ene_exception := NULL;
1013
1014 -- Validate the Account number and return the corresponding SGL
1015 -- number or parent for getting attributes.
1016 -- Verify whether the account number exists in FV_FACTS_ATTRIBUTES table
1017 -- Validate the USSGL Account Number
1018 gbl_error_code := 0;
1019
1020 GET_USSGL_INFO(p_acct_num, l_ussgl_enabled, l_reporting_type);
1021
1022 IF gbl_error_code <> 0 THEN
1023 RETURN;
1024 END IF;
1025
1026 IF l_ussgl_enabled IS NOT NULL THEN -- Account is USSGL_ACCOUNT
1027
1028 p_sgl_acct_num := p_acct_num;
1029
1030 IF l_ussgl_enabled = 'N' THEN
1031 p_exception_category:= 'USSGL_DISABLED';
1032 RETURN;
1033 END IF;
1034
1035 IF l_reporting_type = '2' THEN
1036 -- Account Number is not a valid FACTS I Account
1037 p_exception_category:= 'PROP_ACCT_FACTSII';
1038 RETURN;
1039 END IF;
1040
1041 BEGIN
1042
1043 SELECT 'X', govt_non_govt, exch_non_exch, cust_non_cust, budget_subfunction
1044 INTO l_exists, l_g_ng_ind, l_e_ne_ind, l_c_nc_ind, l_budget_sub_ind
1045 FROM fv_facts_attributes
1046 WHERE facts_acct_number = p_acct_num
1047 AND set_of_books_id = gbl_set_of_books_id;
1048
1049 p_govt_non_govt := l_g_ng_ind;
1050
1051 -- Account Number Valid
1052 -- If Budget_Subfunction is Checked 'Y' in FV_FACTS_ATTRIBUTES
1053 -- but Budget_Subfunction is empty in FV_FUND_PARAMETERS then
1054 -- the account gets reported to exception report
1055
1056 IF (l_budget_sub_ind = 'Y') THEN
1057 SELECT 'X', budget_subfunction
1058 INTO l_row_exists, l_budget_sub
1059 FROM fv_fund_parameters
1060 WHERE fund_value = P_FUND_VALUE
1061 AND set_of_books_id = gbl_set_of_books_id;
1062 END IF;
1063
1064 IF (l_budget_sub_ind = 'Y') THEN
1065 IF (l_budget_sub IS NULL) THEN
1066 p_bsf_exception := 'NO_VAL_SUBFUNCTION';
1067 ELSE
1068 p_budget_subfunction := l_budget_sub;
1069 END IF;
1070 ELSE
1071 p_budget_subfunction := NULL;
1072 END IF;
1073
1074 -- If the value in EXCH_NON_EXCH is 'Either Exchange or Non-exchange'
1075 -- the account does not get reported on the file, it instead gets
1076 -- reported on the Exception Report
1077
1078 IF (l_e_ne_ind = 'Y') THEN
1079 p_ene_exception := 'EXCH_NON_EXCH';
1080 ELSE
1081 IF (l_e_ne_ind = 'N') THEN
1082 p_exch_non_exch := NULL;
1083 ELSE
1084 p_exch_non_exch := l_e_ne_ind;
1085 END IF;
1086 END IF;
1087
1088 IF (l_c_nc_ind = 'Y') THEN
1089 SELECT 'X', fts.cust_non_cust
1090 INTO l_row_exists, l_c_nc
1091 FROM fv_treasury_symbols fts, fv_fund_parameters ffp
1092 WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
1093 AND ffp.set_of_books_id = gbl_set_of_books_id
1094 AND ffp.fund_value = P_FUND_VALUE;
1095 END IF;
1096
1097 IF (l_c_nc_ind = 'Y') THEN
1098 IF (l_c_nc IS NULL) THEN
1099 p_cnc_exception := 'CUST_NON_CUST';
1100 ELSE
1101 p_cust_non_cust := l_c_nc ;
1102 END IF;
1103 ELSE
1104 p_cust_non_cust := NULL;
1105 END IF;
1106
1107 EXCEPTION
1108 WHEN NO_DATA_FOUND THEN
1109 p_sgl_acct_num := p_acct_num;
1110 p_govt_non_govt := NULL;
1111 p_budget_subfunction:= NULL;
1112 p_exch_non_exch := NULL;
1113 p_cust_non_cust := NULL;
1114
1115 p_bsf_exception := NULL;
1116 p_cnc_exception := NULL;
1117 p_ene_exception := NULL;
1118 p_exception_category:= 'PROP_ACCT_NOT_SETUP';
1119 return;
1120
1121 WHEN OTHERS THEN
1122 gbl_error_code := -1;
1123 gbl_error_buf := l_module_name||' - When others error: '||SQLERRM;
1124 --fnd_file.put_line(fnd_file.log , 'first other error raised due to check in
1125 -- fv_facts_attributs or fund_parameter in [GET_USSGL_ACCOUNT_NUM]');
1126 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1127 RETURN;
1128 END;
1129
1130 ELSE -- account is not a ussgl_account
1131 -- Reset the holder variable
1132 l_exists := NULL;
1133
1134 --fnd_file.put_line(fnd_file.log , 'Account is not USSGL ,
1135 --so checking facts_attributes for a/c itself') ;
1136 BEGIN
1137 SELECT 'X', govt_non_govt, exch_non_exch, cust_non_cust, budget_subfunction
1138 INTO l_exists, l_g_ng_ind, l_e_ne_ind, l_c_nc_ind, l_budget_sub_ind
1139 FROM fv_facts_attributes
1140 WHERE facts_acct_number = p_acct_num
1141 AND set_of_books_id = gbl_set_of_books_id;
1142
1143 EXCEPTION
1144 WHEN NO_DATA_FOUND THEN
1145 NULL;
1146 END;
1147
1148 BEGIN
1149 SELECT parent_flex_value
1150 INTO l_ussgl_acct_num
1151 FROM fnd_flex_value_hierarchies
1152 WHERE (p_acct_num BETWEEN child_flex_value_low
1153 AND child_flex_value_high)
1154 AND flex_value_set_id = gbl_acc_value_set_id
1155 AND parent_flex_value <> 'T'
1156 AND parent_flex_value IN
1157 (SELECT ussgl_account
1158 FROM fv_facts_ussgl_accounts
1159 WHERE ussgl_account = parent_flex_value);
1160
1161 -- fnd_file.put_line(fnd_file.log , 'Parent and USSGL found for ' || p_acct_num || ' as ' || l_ussgl_acct_num);
1162 -- Parent Found. Perform Validations
1163 -- fnd_file.put_line(fnd_file.log , 'checking whether USSGL enabled for ' || l_ussgl_acct_num);
1164 GET_USSGL_INFO (l_ussgl_acct_num, l_ussgl_enabled, l_reporting_type);
1165
1166 IF gbl_error_code <> 0 THEN
1167 return;
1168 END IF;
1169
1170 IF l_ussgl_enabled IS NOT NULL THEN
1171 p_sgl_acct_num := l_ussgl_acct_num;
1172
1173 IF l_ussgl_enabled = 'N' THEN
1174 p_exception_category:= 'USSGL_DISABLED';
1175 RETURN;
1176 END IF;
1177
1178 IF l_reporting_type = '2' THEN
1179 -- Account Number is not a valid candidate for FACTS II
1180 -- reporting. Transaction is skipped with no Exception
1181 p_exception_category := 'PROP_ACCT_FACTSII' ;
1182 RETURN;
1183 END IF;
1184
1185 IF l_exists IS NOT NULL THEN
1186 --fnd_file.put_line(fnd_file.log , 'USSGL exists and facts
1187 --attributes found for' || p_acct_num );
1188 -- Parent is Valid USSGL Acct. Child exists on FV_FACTS_ATTRIBUTES
1189 p_govt_non_govt := l_g_ng_ind;
1190
1191 -- If Budget_Subfunction is Checked 'Y' in FV_FACTS_ATTRIBUTES
1192 -- but Budget_Subfunction is empty in FV_FUND_PARAMETERS then
1193 -- the account gets reported to exception report
1194 IF (l_budget_sub_ind = 'Y') THEN
1195 SELECT 'X', budget_subfunction
1196 INTO l_row_exists, l_budget_sub
1197 FROM fv_fund_parameters
1198 WHERE fund_value = P_FUND_VALUE
1199 AND set_of_books_id = gbl_set_of_books_id;
1200 END IF;
1201
1202 IF (l_budget_sub_ind = 'Y') THEN
1203 IF (l_budget_sub IS NULL) THEN
1204 p_bsf_exception := 'NO_VAL_SUBFUNCTION';
1205 ELSE
1206 p_budget_subfunction := l_budget_sub;
1207 END IF;
1208 ELSE
1209 p_budget_subfunction := NULL;
1210 END IF;
1211
1212 -- If the value in EXCH_NON_EXCH is 'Either Exchange or Non-exchange'
1213 -- the account does not get reported on the file, it instead gets
1214 -- reported on the Exception Report
1215 IF (l_e_ne_ind = 'Y') THEN
1216 p_ene_exception := 'EXCH_NON_EXCH';
1217 ELSE
1218 IF (l_e_ne_ind = 'N') THEN
1219 P_EXCH_NON_EXCH := NULL;
1220 ELSE
1221 P_EXCH_NON_EXCH := l_e_ne_ind;
1222 END IF;
1223 END IF;
1224
1225 IF (l_c_nc_ind = 'Y') THEN
1226 SELECT 'X', fts.cust_non_cust
1227 INTO l_row_exists, l_c_nc
1228 FROM fv_treasury_symbols fts, fv_fund_parameters ffp
1229 WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
1230 AND ffp.set_of_books_id = gbl_set_of_books_id
1231 AND ffp.fund_value = P_FUND_VALUE;
1232 END IF;
1233
1234 IF (l_c_nc_ind = 'Y') THEN
1235 IF (l_c_nc IS NULL) THEN
1236 p_cnc_exception := 'CUST_NON_CUST';
1237 ELSE
1238 p_cust_non_cust := l_c_nc ;
1239 END IF;
1240 ELSE
1241 p_cust_non_cust := NULL;
1242 END IF;
1243
1244 ELSE -- Else of l_exists
1245
1246 -- Account Type for further Validation
1247 BEGIN
1248 --USSGL exists but no facts attributes found for the acct num.
1249 --So check facts attribuetes from its USSGL acct.
1250 SELECT 'X', govt_non_govt, exch_non_exch, cust_non_cust, budget_subfunction
1251 INTO l_exists, l_g_ng_ind, l_e_ne_ind, l_c_nc_ind, l_budget_sub_ind
1252 FROM fv_facts_attributes
1253 WHERE facts_acct_number = l_ussgl_acct_num
1254 AND set_of_books_id = gbl_set_of_books_id;
1255
1256 --fnd_file.put_line(fnd_file.log , 'facts-attibutes found for' || p_acct_num );
1257 -- Parent is Valid USSGL Acct. Return Values
1258 p_sgl_acct_num := l_ussgl_acct_num ;
1259 p_govt_non_govt := l_g_ng_ind;
1260
1261 IF (l_budget_sub_ind = 'Y') THEN
1262 SELECT 'X', budget_subfunction
1263 INTO l_row_exists, l_budget_sub
1264 FROM fv_fund_parameters
1265 WHERE fund_value = p_fund_value
1266 AND set_of_books_id = gbl_set_of_books_id;
1267 END IF;
1268
1269 IF (l_budget_sub_ind = 'Y') THEN
1270 IF (l_budget_sub IS NULL) THEN
1271 p_bsf_exception := 'NO_VAL_SUBFUNCTION';
1272 ELSE
1273 P_BUDGET_SUBFUNCTION := l_budget_sub;
1274 END IF;
1275 ELSE
1276 p_budget_subfunction := NULL;
1277 END IF;
1278
1279 -- If the value in EXCH_NON_EXCH is 'Either Exchange or Non-exchange'
1280 -- the account does not get reported on the file, it instead gets
1281 -- reported on the Exception Report
1282 IF (l_e_ne_ind = 'Y') THEN
1283 p_ene_exception := 'EXCH_NON_EXCH';
1284 ELSE
1285 IF (l_e_ne_ind = 'N') THEN
1286 p_exch_non_exch := NULL;
1287 ELSE
1288 p_exch_non_exch := l_e_ne_ind;
1289 END IF;
1290 END IF;
1291
1292 IF (l_c_nc_ind = 'Y') THEN
1293 SELECT 'X', fts.cust_non_cust
1294 INTO l_row_exists, l_c_nc
1295 FROM fv_treasury_symbols fts, fv_fund_parameters ffp
1296 WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
1297 AND ffp.set_of_books_id = gbl_set_of_books_id
1298 AND ffp.fund_value = P_FUND_VALUE;
1299 END IF;
1300
1301 IF (l_c_nc_ind = 'Y') THEN
1302 IF (l_c_nc IS NULL) THEN
1303 p_cnc_exception := 'CUST_NON_CUST';
1304 ELSE
1305 p_cust_non_cust := l_c_nc;
1306 END IF;
1307 ELSE
1308 p_cust_non_cust := NULL;
1309 END IF;
1310
1311 EXCEPTION
1312 WHEN NO_DATA_FOUND THEN
1313 -- Budgetary Acct for which attributes are not set
1314 P_SGL_ACCT_NUM := l_ussgl_acct_num;
1315 P_GOVT_NON_GOVT := NULL;
1316 P_EXCH_NON_EXCH := NULL;
1317 P_CUST_NON_CUST := NULL;
1318 P_BUDGET_SUBFUNCTION:= NULL;
1319
1320 P_BSF_EXCEPTION := NULL;
1321 P_ENE_EXCEPTION := NULL;
1322 P_CNC_EXCEPTION := NULL;
1323 P_EXCEPTION_CATEGORY:= 'PROP_ACCT_NOT_SETUP';
1324 --fnd_file.put_line(fnd_file.log , 'NO facts-attibutes found for'
1325 --|| p_acct_num || 'So returning with prop_acct_not_setup');
1326 RETURN;
1327
1328 WHEN INVALID_NUMBER THEN
1329 -- Budgetary Acct for which attributes are not set
1330 P_SGL_ACCT_NUM := l_ussgl_acct_num;
1331 P_GOVT_NON_GOVT := NULL;
1332 P_EXCH_NON_EXCH := NULL;
1333 P_CUST_NON_CUST := NULL;
1334 P_BUDGET_SUBFUNCTION:= NULL;
1335
1336 P_BSF_EXCEPTION := NULL;
1337 P_ENE_EXCEPTION := NULL;
1338 P_CNC_EXCEPTION := NULL;
1339 P_EXCEPTION_CATEGORY:= 'PROP_ACCT_NOT_SETUP';
1340 -- FV_UTILITY.LOG_MESG('WHEN invalid number during facts-attibutes
1341 --found for'||p_acct_num||' So returning with prop_acct_not_setup');
1342 RETURN;
1343 END;
1344 END IF; -- End IF of l_exists
1345
1346 ELSE -- Else for l_ussgl_enabled IS NOT NULL
1347 -- Parent not exist in FV_FACTS_USSGL_ACCOUNTS table.
1348 -- Raise the Exception NON_USSGL_ACCT
1349 --fnd_file.put_line(fnd_file.log , 'NO USSGL FOUND found for'
1350 --||p_acct_num||' So returning with NON_USSGL_ACCT');
1351
1352 P_SGL_ACCT_NUM := NULL;
1353 P_GOVT_NON_GOVT := NULL;
1354 P_EXCH_NON_EXCH := NULL;
1355 P_CUST_NON_CUST := NULL;
1356 P_BUDGET_SUBFUNCTION := NULL;
1357
1358 P_BSF_EXCEPTION := NULL;
1359 P_ENE_EXCEPTION := NULL;
1360 P_CNC_EXCEPTION := NULL;
1361 P_EXCEPTION_CATEGORY := 'NON_USSGL_ACCT';
1362 RETURN;
1363 END IF; -- Else for l_ussgl_enabled IS NOT NULL
1364
1365 EXCEPTION -- Finding Parent From GL
1366 WHEN NO_DATA_FOUND THEN
1367 --fnd_file.put_line(fnd_file.log , 'NO parent found found for'
1368 --||p_acct_num||' So returning with NON_USSGL_ACCT');
1369 -- No Parent found. Raise the Exception NON_USSGL_ACCT
1370 P_SGL_ACCT_NUM := NULL;
1371 P_GOVT_NON_GOVT := NULL;
1372 P_EXCH_NON_EXCH := NULL;
1373 P_CUST_NON_CUST := NULL;
1374 P_BUDGET_SUBFUNCTION := NULL;
1375
1376 P_BSF_EXCEPTION := NULL;
1377 P_ENE_EXCEPTION := NULL;
1378 P_CNC_EXCEPTION := NULL;
1379 P_EXCEPTION_CATEGORY := 'NON_USSGL_ACCT';
1380 return;
1381
1382 WHEN TOO_MANY_ROWS THEN
1383 -- Too Many Parents. Process Exception
1384 P_SGL_ACCT_NUM := NULL;
1385 P_GOVT_NON_GOVT := NULL;
1386 P_EXCH_NON_EXCH := NULL;
1387 P_CUST_NON_CUST := NULL;
1388 P_BUDGET_SUBFUNCTION := NULL;
1389
1390 P_BSF_EXCEPTION := NULL;
1391 P_ENE_EXCEPTION := NULL;
1392 P_CNC_EXCEPTION := NULL;
1393 P_EXCEPTION_CATEGORY := 'USSGL_MULTIPLE_PARENTS';
1394 --fnd_file.put_line(fnd_file.log , 'MULTIPLE USSGL parent found found for'
1395 --||p_acct_num||' So returning with MULTIPLE_USSGL');
1396 RETURN;
1397
1398 WHEN INVALID_NUMBER THEN
1399 -- No Parent found. Raise the Exception NON_USSGL_ACCT
1400 P_SGL_ACCT_NUM := NULL;
1401 P_GOVT_NON_GOVT := NULL;
1402 P_EXCH_NON_EXCH := NULL;
1403 P_CUST_NON_CUST := NULL;
1404 P_BUDGET_SUBFUNCTION := NULL;
1405
1406 P_BSF_EXCEPTION := NULL;
1407 P_ENE_EXCEPTION := NULL;
1408 P_CNC_EXCEPTION := NULL;
1409 P_EXCEPTION_CATEGORY := 'NON_USSGL_ACCT';
1410 -- fnd_file.put_line(fnd_file.log , 'INVALID NUMBER error
1411 -- 0for account :'||p_acct_num||' returing with NON_USSGL_ACCOUNT');
1412 RETURN;
1413 END; -- Finding Parent From GL
1414 END IF; -- Main acct No Validation
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 -- No Parent found. Raise the Exception NON_USSGL_ACCT
1419 P_SGL_ACCT_NUM := NULL;
1420 P_GOVT_NON_GOVT := NULL;
1421 P_EXCH_NON_EXCH := NULL;
1422 P_CUST_NON_CUST := NULL;
1423 P_BUDGET_SUBFUNCTION := NULL;
1424
1425 P_BSF_EXCEPTION := NULL;
1426 P_ENE_EXCEPTION := NULL;
1427 P_CNC_EXCEPTION := NULL;
1428 P_EXCEPTION_CATEGORY := 'NON_USSGL_ACCT';
1429 fnd_file.put_line(fnd_file.log , 'FINAL WHEN OTHERS FIRED
1430 so will exit the process:' || p_acct_num );
1431 gbl_error_code := -1;
1432 gbl_error_buf := l_module_name||' - Final when others '||SQLERRM;
1433 RETURN;
1434 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1435
1436 END GET_USSGL_ACCT_NUM ;
1437 --------------------------------------------------------------------------------
1438 -- FUNCTION GET_ACCOUNT_TYPE
1439 --------------------------------------------------------------------------------
1440 FUNCTION GET_ACCOUNT_TYPE(p_account_number VARCHAR2) RETURN VARCHAR2
1441 IS
1442
1443 l_module_name VARCHAR2(200);
1444 l_account_type varchar2(1);
1445 l_found varchar2(1) := 'N';
1446 cnt binary_integer := 0;
1447
1448 BEGIN
1449
1450 l_module_name := g_module_name||'GET_ACCOUNT_TYPE';
1451 --FV_UTILITY.LOG_MESG('In '||l_module_name);
1452
1453 SELECT SUBSTR(compiled_value_attributes, 5, 1)
1454 INTO l_account_type
1455 FROM fnd_flex_values
1456 WHERE flex_value = p_account_number
1457 AND flex_value_set_id = gbl_acc_value_set_id;
1458
1459 RETURN (l_account_type);
1460
1461 EXCEPTION
1462 WHEN Others THEN
1463 gbl_error_code := -1 ;
1464 gbl_error_buf := l_module_name||' - When others exception - ' ||
1465 TO_CHAR(SQLCODE) || ' - ' ||SQLERRM ;
1466 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1467 END get_account_type;
1468 --------------------------------------------------------------------------------
1469 -- PROCEDURE GET_USSGL_INFO
1470 --------------------------------------------------------------------------------
1471 -- Gets the information like enabled flag and reporting type
1472 -- for the passed account number.
1473 --------------------------------------------------------------------------------
1474 PROCEDURE GET_USSGL_INFO (p_ussgl_acct_num IN Varchar2,
1475 p_enabled_flag IN OUT NOCOPY Varchar2,
1476 p_reporting_type IN OUT NOCOPY Varchar2)
1477 IS
1478 l_module_name VARCHAR2(200);
1479
1480 BEGIN
1481 l_module_name := g_module_name || 'GET_USSGL_INFO';
1482 --FV_UTILITY.LOG_MESG('In '||l_module_name);
1483
1484 SELECT ussgl_enabled_flag, reporting_type
1485 INTO p_enabled_flag, p_reporting_type
1486 FROM fv_facts_ussgl_accounts
1487 WHERE ussgl_account = p_ussgl_acct_num;
1488
1489 EXCEPTION
1490 WHEN NO_DATA_FOUND THEN NULL;
1491
1492 WHEN OTHERS THEN
1493 gbl_error_code := -1;
1494 gbl_error_buf := SQLERRM;
1495 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1496 RETURN;
1497 END get_ussgl_info;
1498 --------------------------------------------------------------------------------
1499 -- FUNCTION EDIT_CHECK
1500 --------------------------------------------------------------------------------
1501 procedure EDIT_CHECK(p_period_num in VARCHAR2,
1502 p_period_year in VARCHAR2,
1503 p_set_of_books_id in VARCHAR2,
1504 p_status out nocopy varchar2)
1505 IS
1506 l_module_name VARCHAR2(200);
1507 l_debit_amount number;
1508 l_credit_amount number;
1509 l_edit_check_passed varchar2(1);
1510
1511 CURSOR edit_check_c IS
1512 SELECT fund_group, dept_id, bureau_id,
1513 SUM(DECODE(d_c_indicator, 'D', 0, NVL(amount, 0))) credit_amount,
1514 SUM(DECODE(d_c_indicator, 'C', 0, NVL(amount, 0))) debit_amount
1515 FROM FV_FACTS1_PERIOD_BALANCES_V
1516 WHERE set_of_books_id = p_set_of_books_id
1517 AND period_year = p_period_year
1518 and period_num <= p_period_num
1519 GROUP BY fund_group, dept_id, bureau_id;
1520
1521
1522 BEGIN
1523
1524 l_module_name := g_module_name || 'EDIT_CHECK';
1525 FV_UTILITY.LOG_MESG('In '||l_module_name);
1526
1527 l_edit_check_passed := 'Y';
1528
1529 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FACTS I Edit Checks');
1530 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Set of Books: ' ||
1531 FND_PROFILE.VALUE('GL_SET_OF_BKS_NAME'));
1532 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Date: ' ||
1533 to_char(SYSDATE,'YYYY/MM/DD HH24:MI'));
1534 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1535 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1536 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1537
1538 FOR v_t2_record in edit_check_c
1539 LOOP
1540 l_debit_amount := v_t2_record.debit_amount;
1541 l_credit_amount := v_t2_record.credit_amount;
1542
1543 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Treasury Account Code: ' ||
1544 to_char(v_t2_record.fund_group, '0999') ||
1545 ' Dept. Id.: ' || v_t2_record.dept_id ||
1546 ' Bureau Id.: ' ||
1547 v_t2_record.bureau_id);
1548
1549 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Debit Amount: ' ||
1550 to_char(NVL(l_debit_amount, 0),
1551 '999,999,999,999,999,999,999,990.99'));
1552 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Credit Amount: ' ||
1553 to_char(NVL((-1 * l_credit_amount), 0),
1554 '999,999,999,999,999,999,999,990.99'));
1555
1556 IF (NVL(l_debit_amount ,0) = (-1 * NVL(l_credit_amount, 0)))
1557 THEN
1558 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Edit Check Status: PASSED');
1559 ELSE
1560 l_edit_check_passed := 'N';
1561 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Edit Check Status: FAILED');
1562 END IF;
1563
1564 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1565
1566 END LOOP;
1567
1568 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1569 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1570 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FACTS I Edit Checks Completed');
1571
1572 IF (l_edit_check_passed = 'N')
1573 THEN
1574 p_status := 'N';
1575 ELSE
1576 p_status := 'Y';
1577 END IF;
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 gbl_error_code := -1;
1582 gbl_error_buf := l_module_name||' - When others error '||SQLERRM;
1583 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1584 RAISE;
1585
1586 END EDIT_CHECK;
1587 --------------------------------------------------------------------------------
1588 -- PROCEDURE CREATE_END_BAL_RECORD
1589 --------------------------------------------------------------------------------
1590 PROCEDURE CREATE_END_BAL_RECORD
1591 IS
1592
1593 l_module_name VARCHAR2(200);
1594
1595 BEGIN
1596
1597 l_module_name := g_module_name || 'CREATE_END_BAL_RECORD';
1598 FV_UTILITY.LOG_MESG('In '||l_module_name);
1599
1600 INSERT INTO FV_FACTS_ENDING_BALANCES
1601 (fund_group,
1602 account_number,
1603 dept_id,
1604 bureau_id,
1605 eliminations_dept,
1606 g_ng_indicator,
1607 exch_non_exch,
1608 cust_non_cust,
1609 budget_subfunction,
1610 amount,
1611 d_c_indicator,
1612 fiscal_year,
1613 record_category,
1614 ussgl_account,
1615 set_of_books_id,
1616 reported_status,
1617 fund_value,
1618 beginning_balance,
1619 ccid,
1620 account_type,
1621 recipient_name)
1622 (SELECT /*+ PARALLEL(T2) */
1623 t2.fund_group,
1624 t2.account_number,
1625 t2.dept_id,
1626 t2.bureau_id,
1627 t2.eliminations_dept,
1628 t2.g_ng_indicator,
1629 t2.exch_non_exch,
1630 t2.cust_non_cust,
1631 t2.budget_subfunction,
1632 SUM(NVL(amount,0)),
1633 t2.d_c_indicator,
1634 gbl_fiscal_year,
1635 'ENDING_BAL',
1636 '',
1637 gbl_set_of_books_id,
1638 '',
1639 t2.fund_value,
1640 0,
1641 t2.ccid,
1642 t2.account_type,
1643 t2.recipient_name
1644 FROM fv_facts1_period_balances_v t2
1645 WHERE t2.set_of_books_id = gbl_set_of_books_id
1646 AND t2.end_bal_ind = 'Y'
1647 AND nvl(t2.amount,0) <> 0
1648 and period_year = gbl_fiscal_year
1649 and (period_num <= gbl_period_num_high)
1650 GROUP BY t2.fund_group, t2.account_number, t2.dept_id, t2.bureau_id,
1651 t2.eliminations_dept, t2.g_ng_indicator, t2.exch_non_exch,
1652 t2.cust_non_cust, t2.budget_subfunction, t2.d_c_indicator,
1653 t2.fund_value, t2.ccid, t2.account_type, t2.recipient_name
1654 HAVING SUM(NVL(amount,0)) <> 0) ;
1655
1656 fv_utility.log_mesg('Inserted '||SQL%ROWCOUNT ||' recs into fv_facts_ending_balances.');
1657
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 gbl_error_code := -1;
1661 gbl_error_buf := l_module_name||' - When others error '||SQLERRM;
1662 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, gbl_error_buf);
1663 RAISE;
1664
1665 END create_end_bal_record;
1666 --------------------------------------------------------------------------------
1667 -- PROCEDURE CLEANUP_PROCESS
1668 --------------------------------------------------------------------------------
1669 PROCEDURE CLEANUP_PROCESS IS
1670
1671 l_module_name VARCHAR2(200);
1672
1673 BEGIN
1674
1675 l_module_name := g_module_name || 'CLEANUP_PROCESS';
1676 FV_UTILITY.LOG_MESG('In '||l_module_name);
1677
1678
1679 DELETE FROM fv_facts_report_t2
1680 WHERE set_of_books_id = gbl_set_of_books_id;
1681
1682 /** cleanup the line balance differrence records */
1683 FV_UTILITY.LOG_MESG('Deleting from fv_facts1_diff_balances for Period Year: '||
1684 gbl_period_year);
1685
1686 DELETE FROM fv_facts1_diff_balances
1687 WHERE set_of_books_id = gbl_set_of_books_id
1688 and period_year = gbl_period_year
1689 and balance_type = 'D';
1690
1691 FV_UTILITY.LOG_MESG('Deleted '||SQL%ROWCOUNT||
1692 ' records from fv_facts1_diff_balances.');
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 gbl_error_code := -1 ;
1697 gbl_error_buf := l_module_name||' - When others exception - '||SQLERRM;
1698 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,gbl_error_buf);
1699 END;
1700 --------------------------------------------------------------------------------
1701 PROCEDURE JOURNAL_PROCESSES
1702 IS
1703 l_module_name VARCHAR2(200) := g_module_name || 'JOURNAL_PROCESSES';
1704 l_jrnl_select Varchar2(5000);
1705
1706
1707 BEGIN
1708
1709
1710 fnd_file.put_line(fnd_file.log , 'Inserting records into FV_FACTS_REPORT_T2');
1711
1712 INSERT INTO fv_facts_report_t2
1713 (fund_group,
1714 account_number,
1715 dept_id,
1716 bureau_id,
1717 eliminations_dept,
1718 g_ng_indicator,
1719 amount,
1720 d_c_indicator,
1721 fiscal_year,
1722 record_category,
1723 ussgl_account,
1724 set_of_books_id,
1725 reported_status,
1726 exch_non_exch,
1727 cust_non_cust,
1728 budget_subfunction,
1729 fund_value,
1730 ccid,
1731 account_type,
1732 beginning_balance,
1733 dr_amount,
1734 cr_amount)
1735 SELECT
1736 fund_group,
1737 account_number,
1738 dept_id,
1739 bureau_id,
1740 eliminations_dept,
1741 g_ng_indicator,
1742 0,
1743 'N',
1744 gbl_period_year,
1745 'TRIAL_BALANCE',
1746 ussgl_account,
1747 gbl_set_of_books_id,
1748 'R',
1749 exch_non_exch,
1750 cust_non_cust,
1751 budget_subfunction,
1752 fund_value,
1753 ccid,
1754 account_type,
1755 sum(decode(balance_type,'G',period_begin_bal,
1756 decode(period_num, gbl_period_num_high,0,amount) ) ) begin_balance,
1757 sum(decode(balance_type, 'G' , period_dr,
1758 decode(period_num , gbl_period_num_high,
1759 decode(sign(amount) , 1 , amount , 0),0) ) ) period_dr,
1760 sum(decode(balance_type, 'G' , period_cr,
1761 decode(period_num , gbl_period_num_high,
1762 decode(sign(amount) , 1 , 0 , amount),0) ) ) period_dr
1763 from
1764 fv_facts1_period_balances_v fpb
1765 where fpb.set_of_books_id = gbl_set_of_books_id
1766 and fpb.period_year = gbl_fiscal_year
1767 and period_num <= gbl_period_num_high
1768 and fund_value between gbl_fund_range_low and gbl_fund_range_high
1769 GROUP BY fund_group,
1770 account_number,
1771 dept_id,
1772 bureau_id,
1773 eliminations_dept,
1774 g_ng_indicator,
1775 ussgl_account,
1776 exch_non_exch,
1777 cust_non_cust,
1778 budget_subfunction,
1779 fund_value,
1780 ccid,
1781 account_type,
1782 period_num;
1783
1784 fnd_file.put_line(fnd_file.log , 'Completed inserting records into FV_FACTS_REPORT_T2 ' || SQL%ROWCOUNT);
1785
1786 commit;
1787
1788 EXCEPTION
1789
1790 WHEN OTHERS THEN
1791 gbl_error_code := SQLCODE;
1792 gbl_error_buf := SQLERRM || '-- [JOURNAL_PROCESS]';
1793 fnd_file.put_line(fnd_file.log , gbl_error_buf);
1794 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',gbl_error_buf);
1795 END JOURNAL_PROCESSES;
1796
1797 --------------------------------------------------------------------------------------------------------
1798 PROCEDURE TRIAL_BALANCE_MAIN (p_errbuf OUT NOCOPY Varchar2,
1799 p_retcode OUT NOCOPY Number,
1800 p_sob Gl_ledgers_public_v.ledger_id%TYPE,
1801 p_coa Gl_Code_Combinations.chart_of_accounts_id%TYPE,
1802 p_fund_range_low Fv_Fund_Parameters.fund_value%TYPE,
1803 p_fund_range_high Fv_Fund_Parameters.fund_value%TYPE,
1804 p_currency_code Varchar2,
1805 p_period_name Varchar2,
1806 p_report_id Number,
1807 p_attribute_set Varchar2,
1808 p_output_format Varchar2)
1809 IS
1810 l_module_name VARCHAR2(200) := g_module_name || 'TRIAL_BALANCE_MAIN';
1811 l_printer_name Varchar2(240) := Fnd_Profile.value('PRINTER');
1812 l_copies Number := Fnd_Profile.value('CONC_COPIES');
1813 l_print_option Boolean;
1814 l_report_type Varchar2(100);
1815 l_req_id Number;
1816 l_jrnl_exists Varchar2(1);
1817 l_errbuf varchar2(500);
1818 l_retcode varchar2(50);
1819 BEGIN
1820 p_errbuf := NULL;
1821 p_retcode := 0;
1822 gbl_error_code := 0;
1823
1824 -- Store the passed set of books id and chart of accounts id
1825 -- in the global variables
1826 gbl_set_of_books_id := p_sob;
1827 gbl_coa_id := p_coa;
1828 gbl_trial_balance_type := 'F';
1829 gbl_fund_range_low := p_fund_range_low;
1830 gbl_fund_range_high := p_fund_range_high;
1831 gbl_currency_code := p_currency_code;
1832 gbl_report_id := p_report_id;
1833 gbl_attribute_set := p_attribute_set;
1834 gbl_output_format := p_output_format;
1835
1836 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1837 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SET OF BOOKS ID - '|| GBL_SET_OF_BOOKS_ID);
1838 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CURRENCY CODE - '|| GBL_CURRENCY_CODE);
1839 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PERIOD NAME - '|| P_PERIOD_NAME);
1840 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1841 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRIAL BALANCE TYPE - '|| GBL_TRIAL_BALANCE_TYPE);
1842 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FUND RANGE LOW - '|| GBL_FUND_RANGE_LOW);
1843 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FUND RANGE HIGH - '|| GBL_FUND_RANGE_HIGH);
1844 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1845 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REPORT ID - '|| GBL_REPORT_ID);
1846 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ATTRIBUTE SET - '|| GBL_ATTRIBUTE_SET);
1847 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OUTPUT FORMAT - '|| GBL_OUTPUT_FORMAT);
1848 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1849 END IF;
1850
1851 --Getting the period number
1852 BEGIN
1853 SELECT period_num, period_year
1854 INTO gbl_period_num_high, gbl_fiscal_year
1855 FROM gl_period_statuses
1856 WHERE period_name = p_period_name
1857 AND application_id = 101
1858 AND closing_status NOT IN ('F','N')
1859 AND ledger_id = gbl_set_of_books_id;
1860
1861 gbl_period_name := p_period_name;
1862 gbl_period_year := gbl_fiscal_year;
1863
1864 SELECT MIN(period_num)
1865 INTO gbl_period_num_low
1866 FROM gl_period_statuses
1867 WHERE period_year = gbl_fiscal_year
1868 AND application_id = 101
1869 AND closing_status <> 'F'
1870 AND closing_status <> 'N'
1871 AND adjustment_period_flag = 'N'
1872 AND ledger_id = gbl_set_of_books_id;
1873
1874
1875 EXCEPTION
1876 WHEN NO_DATA_FOUND THEN
1877 gbl_error_code := -1;
1878 gbl_error_buf := l_module_name||' No data found getting period num/year.';
1879 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
1880
1881 WHEN OTHERS THEN
1882 gbl_error_code := -1;
1883 gbl_error_buf := l_module_name||' When others error getting period num/year.';
1884 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1885 END;
1886
1887 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1888 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Period Num - '||gbl_period_num);
1889 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Period Year - '||gbl_period_year);
1890 END IF;
1891
1892 -- Purge Temp tables
1893 CLEANUP_PROCESS;
1894
1895 -- get_segment_names;
1896
1897 IF gbl_error_code = 0 THEN
1898 fv_utility.log_mesg('Calling Facts Attributes Creation process.');
1899 SET_UP_FACTS_ATTRIBUTES(l_errbuf ,
1900 l_retcode ,
1901 gbl_set_of_books_id ,
1902 gbl_fiscal_year);
1903 gbl_error_code := l_retcode;
1904 gbl_error_buf := l_errbuf;
1905 END IF;
1906
1907
1908 ------------------------------------
1909 /*
1910 IF (gbl_error_code = 0)
1911 THEN
1912 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1913 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1914 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENTERING JOURNAL_PROCESS ...');
1915 END IF;
1916 JOURNAL_PROCESSES;
1917 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1918 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LEAVING JOURNAL_PROCESS ...');
1919 END IF;
1920 END IF;
1921 */
1922 -------------------------
1923
1924 IF (gbl_error_code = 0)
1925 THEN
1926 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1927 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1928 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENTERING ROLLUP_PROCESS ...');
1929 END IF;
1930 ROLLUP_PROCESS;
1931 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1932 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LEAVING ROLLUP_PROCESS ...');
1933 END IF;
1934 END IF;
1935
1936 IF (gbl_error_code = 0)
1937 THEN
1938 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1939 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ');
1940 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LAUNCHING THE FACTS I TRIAL BALANCE RXI REPORT ...');
1941 END IF;
1942
1943 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS (printer => l_printer_name,
1944 copies => l_copies);
1945
1946 l_req_id := FND_REQUEST.SUBMIT_REQUEST
1947 ('FV','RXFVF1TB','','',FALSE,
1948 'DIRECT', gbl_report_id, gbl_attribute_set, gbl_output_format,
1949 FND_PROFILE.VALUE('GL_SET_OF_BKS_NAME'), gbl_currency_code,
1950 gbl_fund_range_low, gbl_fund_range_high, p_period_name );
1951
1952 IF (l_req_id = 0)
1953 THEN
1954 gbl_error_buf := '** Cannot submit FACTS I Trial Balance RXi report **';
1955 gbl_error_code := '-1';
1956 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error4',gbl_error_buf);
1957 ELSE
1958 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1959 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REPORT REQUEST ID = '||L_REQ_ID);
1960 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1961 END IF;
1962 END IF;
1963 END IF;
1964
1965 IF (gbl_error_code <> 0)
1966 THEN
1967 p_errbuf := gbl_error_buf;
1968 p_retcode := -1;
1969 ROLLBACK;
1970 ELSE
1971 COMMIT;
1972 END IF;
1973
1974
1975 EXCEPTION
1976 WHEN OTHERS THEN
1977 p_retcode := '-1' ;
1978 p_errbuf := SQLERRM ||
1979 ' -- Error in Trial_Balance_Main';
1980 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',p_errbuf);
1981 END TRIAL_BALANCE_MAIN;
1982 --------------------------------------------------------------------------------
1983 -- PROCEDURE ROLLUP_PROCESS
1984 --------------------------------------------------------------------------------
1985 -- Rollup_Process get called from Trial_Balance_Main procedure.
1986 -- The purpose of this procedure is to build a 'group by' clause using
1987 -- segments chosen in an attribute set of RXi. This procedure also does
1988 -- rollup of the trial balance records in fv_facts_report_t2 table by
1989 -- the SEGMENTS.
1990 -- ---------------------------------------------------------------------
1991 PROCEDURE ROLLUP_PROCESS
1992 IS
1993 l_module_name VARCHAR2(200) := g_module_name || 'ROLLUP_PROCESS';
1994 l_group_by VARCHAR2(1000);
1995 l_statement VARCHAR2(5000);
1996
1997 CURSOR c_group IS
1998 SELECT column_name
1999 FROM fa_rx_rep_columns_b
2000 WHERE report_id = gbl_report_id
2001 AND attribute_set = gbl_attribute_set
2002 AND break = 'Y';
2003 BEGIN
2004 FOR crec IN c_group
2005 LOOP
2006 IF crec.column_name like 'SEGMENT%'
2007 THEN
2008 l_group_by := l_group_by || ',' || 'gcc.' || crec.column_name;
2009 END IF;
2010 END LOOP;
2011
2012 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2013 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GROUP BY CLAUSE IS: '|| L_GROUP_BY);
2014 END IF;
2015
2016 fv_utility.log_mesg('GROUP BY CLAUSE IS: '|| L_GROUP_BY);
2017
2018 l_statement := '
2019 INSERT INTO fv_facts_report_t2
2020 ( fund_group,
2021 account_number,
2022 dept_id,
2023 bureau_id,
2024 d_c_indicator,
2025 eliminations_dept,
2026 g_ng_indicator,
2027 amount,
2028 record_category,
2029 ussgl_account,
2030 set_of_books_id,
2031 exch_non_exch,
2032 cust_non_cust,
2033 budget_subfunction,
2034 fund_value,
2035 beginning_balance,
2036 dr_amount,
2037 cr_amount '||replace(l_group_by,'gcc.','')||')
2038 (SELECT 0,
2039 account_number,
2040 '||''''||'0'||''''||',
2041 '||''''||'0'||''''||',
2042 '||''''||'N'||''''||',
2043 eliminations_dept,
2044 g_ng_indicator,
2045 0,
2046 '||''''||'TRIAL_BAL'||''''||',
2047 ussgl_account,
2048 :gbl_set_of_books_id,
2049 exch_non_exch,
2050 cust_non_cust,
2051 budget_subfunction,
2052 fund_value,
2053 --SUM(beginning_balance),
2054 SUM(period_begin_bal),
2055 SUM(nvl(period_dr,0)),
2056 SUM(nvl(period_cr,0)) '|| l_group_by ||'
2057 FROM fv_facts_period_balances_tb_v t2, gl_code_combinations gcc
2058 WHERE t2.set_of_books_id = :gbl_set_of_books_id
2059 AND t2.ccid = gcc.code_combination_id
2060 AND t2.period_num <= :gbl_period_num_high
2061 AND t2.period_year = :gbl_fiscal_year
2062 AND (period_begin_bal <> 0 OR
2063 period_dr <> 0 OR
2064 period_cr <> 0)
2065 AND fund_value BETWEEN :gbl_fund_range_low AND :gbl_fund_range_high
2066 GROUP BY account_number, eliminations_dept,
2067 g_ng_indicator, ussgl_account, exch_non_exch, cust_non_cust, budget_subfunction,
2068 period_num, fund_value'|| l_group_by ||')';
2069
2070 fv_utility.log_mesg(l_statement);
2071 fv_utility.log_mesg('l_group_by: '||l_group_by);
2072 fv_utility.log_mesg('gbl_period_num_high: '||gbl_period_num_high);
2073 fv_utility.log_mesg('gbl_fiscal_year: '||gbl_fiscal_year);
2074 fv_utility.log_mesg('gbl_fund_range_low: '||gbl_fund_range_low);
2075 fv_utility.log_mesg('gbl_fund_range_high: '||gbl_fund_range_high);
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,'
2079 EXECUTING FOLLOWING STATEMENT IN THE ROLLUP PROCESS, STATMENT LENGTH IS ... '||LENGTH(L_STATEMENT));
2080 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,L_STATEMENT);
2081 END IF;
2082
2083 EXECUTE IMMEDIATE l_statement USING gbl_set_of_books_id, gbl_set_of_books_id, gbl_period_num_high, gbl_fiscal_year, gbl_fund_range_low, gbl_fund_range_high;
2084
2085 DELETE FROM fv_facts_report_t2
2086 WHERE record_category <> 'TRIAL_BAL'
2087 AND set_of_books_id = gbl_set_of_books_id;
2088
2089 EXCEPTION
2090 WHEN NO_DATA_FOUND THEN
2091 gbl_error_code := -1;
2092 gbl_error_buf := SQLERRM ||
2093 ' -- Error in Rollup_Process';
2094 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
2095
2096 WHEN OTHERS THEN
2097 gbl_error_code := -1 ;
2098 gbl_error_buf := SQLERRM ||
2099 ' -- Error in Rollup_Process';
2100 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
2101 '.final_exception',gbl_error_buf);
2102 END ROLLUP_PROCESS;
2103 --------------------------------------------------------------------------------
2104 PROCEDURE SET_UP_FACTS_ATTRIBUTES(p_err_buf OUT NOCOPY VARCHAR2,
2105 p_err_code OUT NOCOPY NUMBER,
2106 p_set_of_books_id IN NUMBER,
2107 p_period_year IN NUMBER)
2108 IS
2109
2110 l_module_name VARCHAR2(200);
2111 l_acct_type_condition VARCHAR2(2500);
2112
2113 l_bal_segment VARCHAR2(30);
2114 l_bal_segment_prv VARCHAR2(30);
2115 l_period_begin_bal fv_facts_report_t2.amount%TYPE;
2116 l_period_cy_bal fv_facts_report_t2.amount%TYPE;
2117 l_period_cy_cr_bal fv_facts_report_t2.amount%TYPE;
2118 l_begin_bal fv_facts_report_t2.amount%TYPE;
2119 l_curr_year_balance fv_facts_report_t2.amount%TYPE;
2120 l_t2_deail_amount fv_facts_report_t2.amount%TYPE;
2121 l_ending_amount fv_facts_report_t2.amount%TYPE;
2122
2123 l_exists VARCHAR2(1);
2124 l_stage VARCHAR2(25);
2125 l_fg_null VARCHAR2(1);
2126
2127 TYPE t_ref_cur IS REF CURSOR ;
2128 t1_record_c t_ref_cur ;
2129
2130 TYPE l_account_number_t is table of VARCHAR2(30);
2131 TYPE l_fund_value_t is table of VARCHAR2(30);
2132 TYPE l_fund_group_t is table of fv_treasury_symbols.fund_group_code%TYPE;
2133 TYPE l_dept_id_t is table of fv_treasury_symbols.department_id%TYPE;
2134 TYPE l_bureau_id_t is table of fv_treasury_symbols.bureau_id%TYPE;
2135 TYPE l_sgl_acct_num_t is table of VARCHAR2(4);
2136 TYPE l_govt_non_govt_ind_t is table of VARCHAR2(1);
2137 TYPE l_exch_non_exch_t is table of VARCHAR2(1);
2138 TYPE l_cust_non_cust_t is table of VARCHAR2(1);
2139 TYPE l_exception_status_t is table of VARCHAR2(1);
2140 TYPE l_budget_subfunction_t is table of VARCHAR2(3);
2141 TYPE l_ene_exception_t is table of VARCHAR2(25);
2142 TYPE l_cnc_exception_t is table of VARCHAR2(25);
2143 TYPE l_bsf_exception_t is table of VARCHAR2(25);
2144 TYPE l_exception_category_t is table of VARCHAR2(25);
2145 TYPE l_account_type_t is table of VARCHAR2(1);
2146 TYPE l_balance_amoun_t is table of number;
2147 TYPE l_ccid_t is table of number(15);
2148 TYPE l_rowid_t is table of ROWID;
2149
2150 l_account_number_L l_account_number_t ;
2151 l_fund_value_l l_fund_value_t;
2152 l_fund_group_l l_fund_group_t;
2153 l_dept_id_l l_dept_id_t;
2154 l_bureau_id_l l_bureau_id_t;
2155 l_sgl_acct_num_l l_sgl_acct_num_t;
2156 l_govt_non_govt_ind_l l_govt_non_govt_ind_t;
2157 l_exch_non_exch_l l_exch_non_exch_t;
2158 l_cust_non_cust_l l_cust_non_cust_t;
2159 l_exception_status_l l_exception_status_t;
2160 l_budget_subfunction_l l_ene_exception_t;
2161 l_exception_category_l l_exception_category_t;
2162 l_account_type_l l_account_type_t;
2163 l_new_record_l l_account_type_t;
2164 l_balance_amoun_l l_balance_amoun_t;
2165 l_begin_bal_l l_balance_amoun_t;
2166 l_per_begin_bal_l l_balance_amoun_t;
2167 l_cy_dr_bal_l l_balance_amoun_t;
2168 l_cy_cr_bal_l l_balance_amoun_t;
2169 l_ccid_l l_ccid_t;
2170 l_rowid_l l_rowid_t;
2171
2172 --l_ene_exception_l l_cnc_exception_t;
2173 --l_cnc_exception_l l_bsf_exception_t;
2174 --l_bsf_exception_l l_bsf_exception_t;
2175
2176 l_account_number_n l_account_number_t ;
2177 l_fund_value_n l_fund_value_t;
2178 l_fund_group_n l_fund_group_t;
2179 l_dept_id_n l_dept_id_t;
2180 l_bureau_id_n l_bureau_id_t;
2181 l_sgl_acct_num_n l_sgl_acct_num_t;
2182 l_govt_non_govt_ind_n l_govt_non_govt_ind_t;
2183 l_exch_non_exch_n l_exch_non_exch_t;
2184 l_cust_non_cust_n l_cust_non_cust_t;
2185 l_exception_status_n l_exception_status_t;
2186 l_budget_subfunction_n l_budget_subfunction_t;
2187 l_exception_category_n l_exception_category_t;
2188 l_account_type_n l_account_type_t;
2189 l_new_record_n l_account_type_t;
2190 l_balance_amoun_n l_balance_amoun_t;
2191 l_begin_bal_n l_balance_amoun_t;
2192 l_per_begin_bal_n l_balance_amoun_t;
2193 l_cy_dr_bal_n l_balance_amoun_t;
2194 l_cy_cr_bal_n l_balance_amoun_t;
2195 l_ccid_n l_ccid_t;
2196 l_indx binary_integer;
2197
2198
2199
2200 l_account_number VARCHAR2(30);
2201 l_fund_value VARCHAR2(30);
2202 l_fund_group fv_treasury_symbols.fund_group_code%TYPE;
2203 l_dept_id fv_treasury_symbols.department_id%TYPE;
2204 l_bureau_id fv_treasury_symbols.bureau_id%TYPE;
2205 l_sgl_acct_num VARCHAR2(4);
2206 l_govt_non_gov VARCHAR2(1);
2207 l_exch_non_exch VARCHAR2(1);
2208 l_cust_non_cust VARCHAR2(1);
2209 l_exception_status VARCHAR2(1);
2210 l_budget_subfunction VARCHAR2(3);
2211 l_ene_exception VARCHAR2(25);
2212 l_cnc_exception VARCHAR2(25);
2213 l_bsf_exception VARCHAR2(25);
2214 l_exception_category VARCHAR2(25);
2215 l_account_type VARCHAR2(1);
2216 l_balance_amount number;
2217 l_curr_year_bal number;
2218 l_ccid number(15);
2219 l_govt_non_govt_ind varchar2(1);
2220
2221 l_account_number_prv VARCHAR2(30);
2222 l_t2_detail_amount NUMBER;
2223 l_fed_account VARCHAR2(1);
2224 l_amount NUMBER;
2225 l_jrnl_run_flag VARCHAR2(1);
2226 l_select_stmt VARCHAR2(10000);
2227 l_select_stmt2 VARCHAR2(10000);
2228 l_last_fetch BOOLEAN;
2229
2230 l_int_run_month NUMBER;
2231 l_period_num_high NUMBER;
2232 l_period_num_low NUMBER;
2233 l_rec_count NUMBER;
2234 l_run_status VARCHAR2(1);
2235 l_populate_flag VARCHAR2(1);
2236 l_parameters VARCHAR2(500);
2237 l_exception_count NUMBER;
2238 l_diff_flag varchar2(1);
2239
2240
2241 BEGIN
2242
2243 p_err_code := 0;
2244 p_err_buf := null;
2245 l_module_name := g_module_name||'SET_UP_FACTS_ATTRIBUTES';
2246 FV_UTILITY.LOG_MESG('In '||l_module_name);
2247
2248 gbl_set_of_books_id := p_set_of_books_id;
2249 gbl_fiscal_year := p_period_year;
2250
2251
2252 begin
2253 select decode(period_num,null,'Y',0,'Y','N'),period_num into
2254 l_populate_flag, l_int_run_month
2255 from fv_facts1_run
2256 WHERE set_of_books_id = gbl_set_of_books_id
2257 AND fiscal_year = p_period_year;
2258 exception
2259 when no_data_found then
2260 l_populate_flag := 'Y';
2261 End;
2262
2263 FV_UTILITY.LOG_MESG('Deleting records from fv_facts_report_t2.');
2264 DELETE FROM fv_facts_report_t2
2265 WHERE set_of_books_id = gbl_set_of_books_id;
2266
2267
2268 GET_SEGMENT_NAMES;
2269
2270 /*
2271 IF gbl_trial_balance_type = 'F' then
2272 SELECT MAX(period_num)
2273 INTO l_period_num_high
2274 FROM gl_period_statuses
2275 WHERE period_year = p_period_year
2276 AND application_id = 101
2277 AND closing_status <> 'F'
2278 AND closing_status <> 'N'
2279 AND ledger_id = gbl_set_of_books_id;
2280
2281 SELECT MIN(period_num)
2282 INTO l_period_num_low
2283 FROM gl_period_statuses
2284 WHERE period_year = p_period_year
2285 AND application_id = 101
2286 AND closing_status <> 'F'
2287 AND closing_status <> 'N'
2288 AND adjustment_period_flag = 'N'
2289 AND ledger_id = gbl_set_of_books_id;
2290
2291 SELECT period_name
2292 INTO gbl_period_name
2293 FROM gl_period_statuses
2294 WHERE period_year = p_period_year
2295 AND application_id = 101
2296 AND period_num = l_period_num_high
2297 AND ledger_id = gbl_set_of_books_id;
2298
2299 else
2300 l_period_num_high := gbl_period_num_high;
2301 l_period_num_low := gbl_period_num_low;
2302 END IF;
2303 */
2304
2305
2306 l_period_num_high := gbl_period_num_high;
2307 l_period_num_low := gbl_period_num_low;
2308
2309
2310 FV_UTILITY.LOG_MESG('Period Num Low: '||l_period_num_low);
2311 FV_UTILITY.LOG_MESG('Period Num High: '||l_period_num_high);
2312 FV_UTILITY.LOG_MESG('High Period Name: '||gbl_period_name);
2313
2314
2315 SELECT currency_code
2316 INTO gbl_currency_code
2317 FROM gl_ledgers_public_v
2318 WHERE ledger_id = gbl_set_of_books_id;
2319 FV_UTILITY.LOG_MESG('Currency Code: '||gbl_currency_code);
2320
2321 l_acct_type_condition := ' AND glc.account_type NOT IN ('||''''||'D'||''''||', '||''''||'C'||''''||')';
2322
2323
2324 l_parameters := p_period_year||', '|| l_period_num_high||', '||''''||gbl_period_name||''''||', '||
2325 gbl_set_of_books_id||', ';
2326 l_select_stmt2 := ' glb.code_combination_id, ' ||
2327 ' glc.' || gbl_bal_segment_name || ' , glc.' || gbl_acc_segment_name ||
2328 ', ''NO'', ''#'', ''#'', ''#'', ''#'', ''#'', ''#'', ''#'', ''E'', -99 ,''N'',
2329 SUM (DECODE (period_num, :gbl_period_num_high,
2330 (begin_balance_dr - begin_balance_cr + NVL(period_net_dr,0)
2331 - NVL(period_net_cr,0)),0)) curr_year_bal,
2332 SUM (DECODE (period_num, :gbl_period_num_low,
2333 (begin_balance_dr - begin_balance_cr),0)) begin_bal,
2334 SUM (DECODE (period_num, :gbl_period_num_high,
2335 (NVL(period_net_dr,0)),0)) period_cy_bal,
2336 SUM (DECODE (period_num, :gbl_period_num_high,
2337 (NVL(period_net_cr,0)),0)) period_cy_cr_bal,
2338 SUM (DECODE (period_num, :gbl_period_num_high,
2339 (begin_balance_dr - begin_balance_cr),0)) period_begin_bal '||
2340 ' FROM gl_balances glb,gl_code_combinations GLC
2341 WHERE glb.actual_flag = '||''''||'A'||''''||'
2342 AND period_year = :gbl_fiscal_year
2343 AND period_num IN (:gbl_period_num_low, :gbl_period_num_high)
2344 AND glb.ledger_id = :gbl_set_of_books_id
2345 AND glb.template_id is NULL
2346 AND glb.currency_code = :gbl_currency_code
2347 AND glc.code_combination_id = glb.code_combination_id '
2348 || l_acct_type_condition
2349 ||' GROUP BY glb.code_combination_id ,'||'glc.'||gbl_bal_segment_name
2350 ||', glc.' || gbl_acc_segment_name
2351 ||' ORDER BY '||'glc.'||gbl_bal_segment_name ||', glc.' || gbl_acc_segment_name;
2352
2353 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2354 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2355 'l_select_stmt: '||l_select_stmt);
2356 END IF;
2357
2358
2359 l_account_number_n := l_account_number_t(null);
2360 l_fund_value_n := l_fund_value_t(null);
2361 l_fund_group_n := l_fund_group_t(null);
2362 l_dept_id_n := l_dept_id_t(null);
2363 l_bureau_id_n := l_bureau_id_t(null);
2364 l_sgl_acct_num_n := l_sgl_acct_num_t(null);
2365 l_govt_non_govt_ind_n := l_govt_non_govt_ind_t(null);
2366 l_exch_non_exch_n := l_exch_non_exch_t(null);
2367 l_cust_non_cust_n := l_cust_non_cust_t(null);
2368 l_exception_status_n := l_exception_status_t(null);
2369 l_budget_subfunction_n := l_budget_subfunction_t(null);
2370 l_exception_category_n:= l_exception_category_t(null);
2371 l_account_type_n := l_account_type_t(null);
2372 l_balance_amoun_n := l_balance_amoun_t(null);
2373 l_begin_bal_n := l_balance_amoun_t(null);
2374 l_per_begin_bal_n := l_balance_amoun_t(null);
2375 l_cy_dr_bal_n := l_balance_amoun_t(null);
2376 l_cy_cr_bal_n := l_balance_amoun_t(null);
2377 l_ccid_n:= l_ccid_t(null);
2378
2379 l_account_number_n.extend(10000);
2380 l_fund_value_n.extend(10000);
2381 l_fund_group_n.extend(10000);
2382 l_dept_id_n.extend(10000);
2383 l_bureau_id_n.extend(10000);
2384 l_sgl_acct_num_n.extend(10000);
2385 l_govt_non_govt_ind_n.extend(10000);
2386 l_exch_non_exch_n.extend(10000);
2387 l_cust_non_cust_n.extend(10000);
2388 l_exception_status_n.extend(10000);
2389 l_budget_subfunction_n.extend(10000);
2390 l_exception_category_n.extend(10000);
2391 l_account_type_n.extend(10000);
2392 l_balance_amoun_n.extend(10000);
2393 l_begin_bal_n.extend(10000);
2394 l_per_begin_bal_n.extend(10000);
2395 l_cy_dr_bal_n.extend(10000);
2396 l_cy_cr_bal_n.extend(10000);
2397 l_ccid_n.extend(10000);
2398
2399 l_select_stmt2 := ' SELECT ' || l_select_stmt2;
2400 fnd_file.put_line(fnd_file.log, l_select_stmt2);
2401 l_bal_segment_prv := '####';
2402 gbl_prev_acct := '####';
2403 gbl_bal_segment := '####';
2404 gbl_error_code := 0;
2405 gbl_error_buf := NULL;
2406 l_jrnl_run_flag := 'N';
2407 l_rec_count := 0;
2408
2409
2410 --------------------------------------------------
2411
2412
2413 /* check already being_bal differnce processed */
2414
2415 l_diff_flag := 'N';
2416
2417 begin
2418 select NVL(begin_bal_diff_flag , 'N') into l_diff_flag
2419 from fv_facts1_run
2420 where set_of_books_id = gbl_set_of_books_id
2421 and fiscal_year = gbl_fiscal_year;
2422
2423 -- To delete the erroneous record
2424 fnd_file.put_line(fnd_file.log,
2425 'Deleting the begin balance difference records from fv_facts1_diff_balances.');
2426
2427 if l_diff_flag = 'N' then
2428 DELETE FROM fv_facts1_diff_balances
2429 WHERE set_of_books_id = gbl_set_of_books_id
2430 and period_year = gbl_fiscal_year
2431 and balance_type = 'B';
2432 end if;
2433
2434 exception
2435 when no_data_found then
2436 l_diff_flag := 'N';
2437 End;
2438
2439 --------------------------------------------------
2440 fund_group_info_setup;
2441
2442
2443 IF gbl_error_code <> 0 THEN
2444 ROLLBACK;
2445 RETURN;
2446 END IF;
2447
2448 -----------------------------------------------
2449
2450 OPEN t1_record_c for l_select_stmt2 USING
2451 l_period_num_high,
2452 l_period_num_low,
2453 l_period_num_high, l_period_num_high, l_period_num_high,
2454 gbl_fiscal_year, l_period_num_low, l_period_num_high,gbl_set_of_books_id,
2455 gbl_currency_code;
2456
2457 l_last_fetch := FALSE;
2458
2459 LOOP
2460
2461 FETCH t1_record_c BULK COLLECT INTO l_ccid_l, l_fund_value_l,l_account_number_l,
2462 l_sgl_acct_num_l,
2463 l_exch_non_exch_l,
2464 l_cust_non_cust_l,
2465 l_account_type_l,
2466 l_budget_subfunction_l,
2467 l_dept_id_l,
2468 l_bureau_id_l,
2469 l_govt_non_govt_ind_l,
2470 l_exception_status_l,
2471 l_fund_group_l,
2472 l_new_record_l,
2473 l_balance_amoun_l,
2474 l_begin_bal_l ,
2475 l_cy_dr_bal_l ,
2476 l_cy_cr_bal_l,
2477 l_per_begin_bal_l LIMIT 10000;
2478
2479
2480 IF t1_record_c%NOTFOUND THEN
2481 l_last_fetch := TRUE;
2482 END IF;
2483
2484 l_indx := 0;
2485
2486 fv_utility.log_mesg('in Deriving attributes ');
2487 IF (l_ccid_l.count = 0 AND l_last_fetch) THEN
2488 EXIT;
2489 END IF;
2490
2491 FOR i IN l_ccid_l.first .. l_ccid_l.last
2492
2493 LOOP
2494
2495 begin
2496 select 'N' into l_new_record_l(i)
2497 from fv_facts1_period_attributes
2498 where ccid = l_ccid_l(i)
2499 and period_year = gbl_fiscal_year
2500 and set_of_books_id = gbl_set_of_books_id;
2501 exception
2502 when no_data_found then
2503 l_new_record_l(i) := 'Y';
2504 End;
2505
2506
2507 l_exception_status := NULL;
2508 l_exception_status_l(i) := NULL;
2509
2510 l_account_number := l_account_number_l(i);
2511 l_fund_value := l_fund_value_l(i);
2512 l_ccid := l_ccid_l(i);
2513 l_balance_amount := l_balance_amoun_l(i);
2514 l_exception_status := 'E';
2515 l_exception_status_l(i) := 'E';
2516
2517 l_bal_segment := l_fund_value;
2518
2519
2520 IF (l_bal_segment <> l_bal_segment_prv) THEN
2521 GET_FUND_GROUP_INFO(l_fund_value, l_exists, l_fg_null,
2522 l_fund_group, l_dept_id, l_bureau_id);
2523 --l_bal_segment_prv := l_bal_segment;
2524 END IF;
2525
2526 IF gbl_error_code <> 0 THEN
2527 RETURN;
2528 END IF;
2529
2530
2531 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2532 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2533 '---------------------------------');
2534 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2535 'Fund Value: '||l_fund_value);
2536 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2537 'Account Number: '|| l_account_number);
2538 END IF;
2539
2540 IF (l_exists = 'N') THEN
2541 l_fg_null := 'Y';
2542 l_fund_group := NULL;
2543 l_dept_id := NULL;
2544 l_bureau_id := NULL;
2545 l_fund_group_l(i) := NULL;
2546 l_dept_id_l(i) := NULL;
2547 l_bureau_id_l(i) := NULL;
2548 ELSIF (l_bureau_id IS NULL) THEN
2549 l_bureau_id := '00';
2550 l_bureau_id_l(i) := '00';
2551 END IF;
2552
2553 IF (l_fg_null = 'Y') THEN
2554
2555
2556 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2557 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2558 'Fund group is null.');
2559 END IF;
2560
2561 POPULATE_TEMP2(0000, ' ', '0', '0', '', '', l_balance_amount, 'D',
2562 p_period_year, 'NO_FUND_GROUP', '',
2563 gbl_set_of_books_id, 'E',
2564 '', '', '', l_bal_segment, 0, '',
2565 '', '', 0, 0);
2566
2567 IF gbl_error_code <> 0 THEN
2568 p_err_code := gbl_error_code;
2569 p_err_buf := gbl_error_buf ;
2570 RETURN ;
2571 END IF;
2572 l_exception_status := 'E';
2573 l_exception_status_l(i) := 'E';
2574
2575 END IF;
2576
2577 IF (l_fg_null = 'N') THEN -- 0
2578
2579 l_bureau_id_l(i) := l_bureau_id;
2580 l_fund_group_l(i):= l_fund_group;
2581 l_dept_id_l(i) := l_dept_id;
2582
2583 IF (gbl_prev_acct <> l_account_number or l_bal_segment <> l_bal_segment_prv) then
2584
2585 GET_USSGL_ACCT_NUM(l_account_number,
2586 l_fund_value, l_sgl_acct_num,
2587 l_govt_non_govt_ind, l_exch_non_exch,
2588 l_cust_non_cust, l_budget_subfunction,
2589 l_ene_exception, l_cnc_exception,
2590 l_bsf_exception, l_exception_category);
2591
2592 IF (gbl_error_code <> 0) THEN
2593 p_err_code := gbl_error_code;
2594 p_err_buf := gbl_error_buf ;
2595 FV_UTILITY.LOG_MESG('An error occurred in GET_USSGL_ACCT_NUM.
2596 No further processing of FACTS 1 will be done.');
2597 RETURN;
2598 END IF;
2599
2600 -- Get the Account Type
2601 l_account_type := GET_ACCOUNT_TYPE(l_account_number);
2602 l_account_type_l(I) := l_account_type;
2603
2604 gbl_prev_acct := l_account_number;
2605 gbl_bal_segment := l_fund_value;
2606 END IF;
2607
2608
2609 l_govt_non_govt_ind_l(i) := l_govt_non_govt_ind;
2610 l_exch_non_exch_l(i) := l_exch_non_exch;
2611 l_cust_non_cust_l(i) := l_cust_non_cust;
2612 l_budget_subfunction_l(i):= l_budget_subfunction;
2613 l_account_type_l(I) := l_account_type;
2614 l_sgl_acct_num_l(I) := l_sgl_acct_num;
2615
2616 IF (l_exception_category IN ('PROP_ACCT_NOT_SETUP', --1
2617 'PROP_ACCT_FACTSII', 'USSGL_DISABLED',
2618 'NON_USSGL_ACCT', 'USSGL_MULTIPLE_PARENTS')) THEN
2619
2620 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2621 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2622 'Exception: '||l_exception_category);
2623 END IF;
2624
2625 -- Account segment did not pass SGL validation.
2626 -- Insert into T2 as an exception
2627 POPULATE_TEMP2(l_fund_group, l_account_number, l_dept_id,
2628 l_bureau_id , '', '', l_balance_amount,
2629 'D', p_period_year, l_exception_category,
2630 l_sgl_acct_num, gbl_set_of_books_id, 'E',
2631 '', '', '', l_fund_value, 0, l_ccid,
2632 l_account_type, '', 0, 0);
2633
2634 IF gbl_error_code <> 0 THEN
2635 p_err_code := gbl_error_code;
2636 p_err_buf := gbl_error_buf ;
2637 RETURN;
2638 END IF;
2639
2640 l_exception_status := 'E';
2641 l_exception_status_l(i) := 'E';
2642
2643 ELSIF ((l_ene_exception IS NOT NULL) OR --1
2644 (l_cnc_exception IS NOT NULL) OR
2645 (l_bsf_exception IS NOT NULL)) THEN
2646 IF (l_ene_exception IS NOT NULL) THEN
2647 POPULATE_TEMP2(l_fund_group, l_account_number,
2648 l_dept_id, l_bureau_id, '', '', l_balance_amount,
2649 'N', p_period_year, l_ene_exception, l_sgl_acct_num,
2650 gbl_set_of_books_id, 'E', '', '', '',
2651 l_fund_value, 0, l_ccid, l_account_type, '', 0, 0);
2652
2653 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2654 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2655 'Exception: '||l_ene_exception);
2656 END IF;
2657
2658 END IF;
2659
2660 IF (l_cnc_exception IS NOT NULL) THEN
2661 POPULATE_TEMP2(l_fund_group, l_account_number,
2662 l_dept_id, l_bureau_id, '', '', l_balance_amount,
2663 'N', p_period_year, l_cnc_exception, l_sgl_acct_num,
2664 gbl_set_of_books_id, 'E', '', '', '',
2665 l_fund_value, 0, l_ccid, l_account_type, '', 0, 0);
2666
2667 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2668 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2669 'Exception: '||l_cnc_exception);
2670 END IF;
2671
2672 END IF;
2673
2674 IF (l_bsf_exception IS NOT NULL) THEN
2675 POPULATE_TEMP2(l_fund_group, l_account_number,
2676 l_dept_id, l_bureau_id, '', '', l_balance_amount,
2677 'N', p_period_year, l_bsf_exception, l_sgl_acct_num,
2678 gbl_set_of_books_id, 'E', '', '', '',
2679 l_fund_value, 0, l_ccid, l_account_type, '', 0, 0);
2680
2681 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2682 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2683 'Exception: '||l_bsf_exception);
2684 END IF;
2685
2686 END IF;
2687
2688 IF gbl_error_code <> 0 THEN
2689 p_err_code := gbl_error_code;
2690 p_err_buf := gbl_error_buf ;
2691 RETURN;
2692 END IF;
2693
2694 l_exception_status := 'E';
2695 l_exception_status_l(i) := 'E';
2696
2697 ELSE --1
2698
2699 IF l_govt_non_govt_ind IN ('N', 'X') THEN
2700 l_exception_status := '1' ;
2701 l_exception_status_l(i) := '1' ;
2702 ELSE
2703 l_exception_status := '2' ;
2704 l_exception_status_l(i) := '2' ;
2705 END IF;
2706 END IF; -- 1 exception_cateogry
2707 END IF; -- 0 l_fg_null = 'N'
2708
2709 l_bal_segment_prv := l_bal_segment;
2710 l_rec_count := l_rec_count + 1;
2711
2712 /* Insert the new ccid */
2713
2714 If l_new_record_l(i) = 'Y' then
2715 l_indx := l_indx + 1;
2716 l_account_number_n(l_indx) := l_account_number_l(i);
2717 l_fund_value_n(l_indx) := l_fund_value_l(i);
2718 l_fund_group_n(l_indx) := l_fund_group_l(i);
2719 l_dept_id_n(l_indx) := l_dept_id_l(i);
2720 l_bureau_id_n(l_indx) := l_bureau_id_l(i);
2721 l_sgl_acct_num_n(l_indx) := l_sgl_acct_num_l(i);
2722 l_govt_non_govt_ind_n(l_indx) := l_govt_non_govt_ind_l(i);
2723 l_exch_non_exch_n(l_indx) := l_exch_non_exch_l(i);
2724 l_cust_non_cust_n(l_indx) := l_cust_non_cust_l(i);
2725 l_exception_status_n(l_indx) := l_exception_status_l(i);
2726 l_budget_subfunction_n(l_indx) := l_budget_subfunction_l(i);
2727 l_account_type_n(l_indx) := l_account_type_l(i);
2728 l_balance_amoun_n(l_indx) := l_balance_amoun_l(i);
2729 l_begin_bal_n(l_indx) := l_begin_bal_l(i);
2730 l_per_begin_bal_n(l_indx) := l_per_begin_bal_l(i);
2731 l_cy_dr_bal_n(l_indx) := l_cy_dr_bal_l(i);
2732 l_cy_cr_bal_n(l_indx) := l_cy_cr_bal_l(i);
2733 l_ccid_n(l_indx):= l_ccid_l(i);
2734 End if;
2735
2736 -------------------------------------------------------
2737
2738 -- create a difference record.
2739
2740 if (l_exception_status = '2' and l_govt_non_govt_ind IN ('F', 'Y') ) then
2741
2742 l_curr_year_balance := l_balance_amoun_l(i) - l_begin_bal_l(i);
2743
2744 l_stage := 'Detail difference';
2745
2746 l_t2_detail_amount := 0;
2747
2748 SELECT NVL(SUM(NVL(t2.amount, 0)), 0)
2749 INTO l_t2_detail_amount
2750 FROM fv_facts1_line_balances t2
2751 WHERE t2.ccid = l_ccid_l(i)
2752 AND t2.set_of_books_id = gbl_set_of_books_id
2753 AND period_num <= gbl_period_num_high
2754 AND period_year = gbl_fiscal_year;
2755
2756 IF (l_curr_year_balance <> l_t2_detail_amount) THEN
2757
2758 -- Insert an exception record if there is a difference in the amount
2759 POPULATE_TEMP2(l_fund_group,
2760 l_account_number,
2761 l_dept_id, l_bureau_id,
2762 '', '', (l_curr_year_balance - l_t2_detail_amount),
2763 '', gbl_period_year, 'LINE_BAL_DIFF',
2764 l_sgl_acct_num, gbl_set_of_books_id, 'E',
2765 l_exch_non_exch, l_cust_non_cust,
2766 l_budget_subfunction, l_fund_value,
2767 0, l_ccid_l(i), l_account_type,
2768 'Other', 0, 0);
2769
2770 INSERT INTO fv_facts1_diff_balances
2771 (
2772 ccid,period_num,period_year,set_of_books_id,
2773 eliminations_dept,
2774 g_ng_indicator,
2775 amount,
2776 d_c_indicator,
2777 balance_type,
2778 recipient_name,
2779 account_number,
2780 fund_value)
2781 VALUES
2782 (l_ccid_l(i),gbl_period_num_high,gbl_fiscal_year,gbl_set_of_books_id,
2783 DECODE(l_govt_non_govt_ind, 'F', '00', ' '),
2784 DECODE(l_govt_non_govt_ind, 'F', l_govt_non_govt_ind, 'N'),
2785 (l_curr_year_balance - l_t2_detail_amount),
2786 DECODE(SIGN(l_curr_year_balance - l_t2_detail_amount), 0, 'D', 1, 'D', -1, 'C'),
2787 'D','Other', l_account_number, l_fund_value);
2788 END IF;
2789
2790 -------------------------------------------------------------------------
2791 -- Populate fv_facts1_diff_balances with previous year's ending balance
2792 -- and create a difference record
2793
2794 /* check the begin_balance record been created , if not run it */
2795
2796 IF (l_diff_flag = 'N' AND l_account_type IN ('A','L','O')) THEN
2797
2798 l_ending_amount := 0 ;
2799 l_stage := 'Ending balance diff';
2800
2801 SELECT NVL(SUM(amount), 0)
2802 INTO l_ending_amount
2803 FROM fv_facts_ending_balances
2804 WHERE ccid = l_ccid_l(i)
2805 AND set_of_books_id = gbl_set_of_books_id
2806 AND fiscal_year = (gbl_fiscal_year - 1)
2807 AND record_category = 'ENDING_BAL'
2808 AND account_number = l_account_number
2809 AND dept_id = l_dept_id
2810 AND bureau_id = l_bureau_id
2811 AND fund_value = l_fund_value
2812 AND account_type IN ('A','L','O');
2813
2814 IF l_begin_bal_l(i) <> l_ending_amount THEN
2815
2816 -- Insert an exception record if there is a difference in the amount
2817 POPULATE_TEMP2(l_fund_group,
2818 l_account_number,
2819 l_dept_id, l_bureau_id,
2820 '', '', l_begin_bal_l(i),
2821 '', gbl_period_year, 'BEG_BAL_DIFF',
2822 l_sgl_acct_num, gbl_set_of_books_id, 'E',
2823 l_exch_non_exch, l_cust_non_cust,
2824 l_budget_subfunction, l_fund_value,
2825 0, l_ccid_l(i), l_account_type,
2826 'Other', 0, 0);
2827
2828 INSERT INTO fv_facts1_diff_balances
2829 (ccid,period_num,period_year,set_of_books_id,
2830 eliminations_dept,
2831 g_ng_indicator,
2832 amount,
2833 d_c_indicator,
2834 balance_type,
2835 recipient_name,
2836 account_number,
2837 fund_value)
2838 VALUES
2839 (l_ccid_l(i),gbl_period_num_low,gbl_fiscal_year,gbl_set_of_books_id,
2840 DECODE(l_govt_non_govt_ind, 'F', '00', ' '),
2841 DECODE(l_govt_non_govt_ind, 'F', l_govt_non_govt_ind, 'N'),
2842 l_begin_bal_l(i) - l_ending_amount,
2843 DECODE(SIGN(l_begin_bal_l(i) - l_ending_amount), 0, 'D', 1, 'D', -1, 'C'),
2844 'B', 'Other', l_account_number, l_fund_value);
2845
2846 END IF; -- Populate Temp2 with previous year's ending bal
2847
2848 End if; /* diff_flag = 'N' */
2849
2850 End if; /* excpetion_status =2 and 'G_NG = 'Y' */
2851 ----------------------------------------------------
2852 END LOOP; /* for i loop */
2853
2854
2855 FV_UTILITY.log_MESG('Inserting no of new records ' || l_indx);
2856 FORALL i IN 1 .. l_indx
2857 INSERT INTO fv_facts1_period_attributes
2858 ( period_year,
2859 period_num,
2860 period_name,
2861 set_of_books_id,
2862 ccid,
2863 fund_value,
2864 account_number,
2865 ussgl_account,
2866 exch_non_exch ,
2867 cust_non_cust,
2868 account_type ,
2869 budget_subfunction,
2870 dept_id,
2871 bureau_id,
2872 g_ng_indicator,
2873 reported_group,
2874 fund_group,
2875 new_rec_flag,
2876 BALANCE_AMOUNT,
2877 BEGIN_BALANCE,
2878 PERIOD_CY_DR_BAL,
2879 PERIOD_CY_CR_BAL ,
2880 PERIOD_BEGIN_BAL,
2881 end_bal_ind
2882 )
2883 values (
2884 gbl_fiscal_year,
2885 l_period_num_high,
2886 gbl_period_name,
2887 gbl_set_of_books_id,
2888 l_ccid_n(i),
2889 l_fund_value_n(i),
2890 l_account_number_n(i),
2891 l_sgl_acct_num_n(i),
2892 l_exch_non_exch_n(i),
2893 l_cust_non_cust_n(i),
2894 l_account_type_n(i),
2895 l_budget_subfunction_n(i),
2896 decode(l_dept_id_n(i) ,NULL, '#', l_dept_id_n(i)),
2897 decode(l_bureau_id_n(i),NULL, '#' , l_bureau_id_n(i)),
2898 DECODE(l_govt_non_govt_ind_n(i), 'X', ' ', l_govt_non_govt_ind_n(i)),
2899 l_exception_status_n(i),
2900 decode(l_fund_group_n(i), NULL, -99 ,l_fund_group_n(i)),
2901 'Y',
2902 l_balance_amoun_n(i),
2903 l_begin_bal_n(i),
2904 l_cy_dr_bal_n(i),
2905 l_cy_cr_bal_n(i),
2906 l_per_begin_bal_n(i),
2907 DECODE(l_govt_non_govt_ind_n(i), 'F', 'Y', 'Y', 'Y', 'N')
2908 );
2909
2910
2911 -- Update facts attributes in fv_facts1_period_attributes
2912
2913 FV_UTILITY.log_MESG( 'Updating records ' || (l_ccid_l.count - l_indx));
2914
2915 FORALL i IN l_ccid_l.first .. l_ccid_l.last
2916 UPDATE fv_facts1_period_attributes
2917 SET ussgl_account = l_sgl_acct_num_l(i),
2918 exch_non_exch = l_exch_non_exch_l(i),
2919 cust_non_cust = l_cust_non_cust_l(i),
2920 account_type = l_account_type_l(i),
2921 budget_subfunction = l_budget_subfunction_l(i),
2922 fund_group = decode(l_fund_group_l(i), NULL, -99 ,l_fund_group_l(i)),
2923 dept_id = decode(l_dept_id_l(i) ,NULL, '#', l_dept_id_l(i)),
2924 bureau_id = decode(l_bureau_id_l(i),NULL, '#' , l_bureau_id_l(i)),
2925 g_ng_indicator = DECODE(l_govt_non_govt_ind_l(i), 'X', ' ', l_govt_non_govt_ind_l(i)),
2926 reported_group = l_exception_status_l(i),
2927 BALANCE_AMOUNT = l_balance_amoun_l(i),
2928 BEGIN_BALANCE = l_begin_bal_l(i),
2929 PERIOD_CY_DR_BAL = l_cy_dr_bal_l(i),
2930 PERIOD_CY_CR_BAL = l_cy_cr_bal_l(i),
2931 PERIOD_BEGIN_BAL = l_per_begin_bal_l(i),
2932 period_num = l_period_num_high,
2933 period_name = gbl_period_name,
2934 end_bal_ind = DECODE(l_govt_non_govt_ind_l(i), 'F', 'Y', 'Y', 'Y', 'N')
2935 WHERE ccid = l_ccid_l(i)
2936 and period_year = gbl_fiscal_year
2937 and set_of_books_id = gbl_set_of_books_id
2938 and l_new_record_l(i) = 'N';
2939 END LOOP;
2940
2941 FV_UTILITY.LOG_MESG('No of CCID processed ' || l_rec_count);
2942
2943 IF l_rec_count <> 0 THEN
2944
2945 l_exception_count := 0;
2946
2947 -- Count the exception records
2948 SELECT COUNT(*)
2949 INTO l_exception_count
2950 FROM fv_facts_report_t2
2951 WHERE set_of_books_id = gbl_set_of_books_id
2952 AND reported_status = 'E'
2953 and record_category NOT IN ('PROP_ACCT_NOT_SETUP', 'PROP_ACCT_FACTSII',
2954 'USSGL_DISABLED', 'NO_FUND_GROUP' )
2955 AND amount <> 0 ;
2956
2957
2958 if l_exception_count > 0 then
2959 FV_UTILITY.LOG_MESG('Set up Facts Attributes completed wth exceptions');
2960 p_err_code := 0;
2961 p_err_buf := 'Set up Facts Attributes completed with exceptions.';
2962 l_run_status := 'E';
2963 --gbl_exception_exists := 'Y';
2964 --submit_exception_report;
2965 else
2966 l_run_status := 'U';
2967 FV_UTILITY.LOG_MESG('Set up Facts Attributes completed successfully');
2968 p_err_buf := 'Set up Facts Attributes completed successfully.';
2969 END IF;
2970
2971 ELSE -- l_rec_count
2972 l_run_status := 'U';
2973 FV_UTILITY.LOG_MESG('No data found for this period year.');
2974 END IF;
2975
2976
2977 -- Update fv_facts1_run only if there were records
2978 -- found for the attribute creation process.
2979 IF l_rec_count > 0 THEN
2980 FV_UTILITY.LOG_MESG('Updating facts1 run status.');
2981 UPDATE fv_facts1_run
2982 SET status = l_run_status,
2983 process_date = sysdate,
2984 run_fed_flag = 'I',
2985 begin_bal_diff_flag = 'Y',
2986 period_num = l_period_num_high
2987 WHERE set_of_books_id = gbl_set_of_books_id
2988 AND fiscal_year = p_period_year
2989 AND table_indicator = 'N';
2990
2991 IF gbl_error_code <> 0 THEN
2992 p_err_code := gbl_error_code;
2993 p_err_buf := gbl_error_buf;
2994 ROLLBACK;
2995 RETURN;
2996 END IF;
2997 END IF;
2998
2999 COMMIT;
3000
3001 EXCEPTION
3002 WHEN OTHERS THEN
3003 p_err_code := -1;
3004 p_err_buf := l_module_name||' When others exception: '
3005 ||to_char(SQLCODE) || ' - ' || SQLERRM;
3006 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
3007
3008 END set_up_facts_attributes;
3009 --------------------------------------------------------------------------------
3010 PROCEDURE update_facts1_run(p_period_year in VARCHAR2,
3011 p_set_of_books_id in VARCHAR2)
3012 is
3013 l_module_name VARCHAR2(200);
3014 l_je_header_id number(15);
3015 l_stage number(15);
3016 l_posted_date date;
3017
3018 BEGIN
3019
3020 l_module_name := g_module_name || 'UPDATE_FACTS1_RUN';
3021 FV_UTILITY.LOG_MESG('In '||l_module_name);
3022
3023 UPDATE fv_facts1_run
3024 SET run_fed_flag = 'A',
3025 process_date = sysdate
3026 WHERE set_of_books_id = p_set_of_books_id
3027 AND fiscal_year = p_period_year
3028 AND table_indicator = 'N';
3029
3030 IF SQL%ROWCOUNT = 0 THEN
3031
3032 /* Get the je_header_id for the sob and year */
3033
3034 l_stage := 1;
3035
3036 select nvl(min(je_header_id),0)
3037 into l_je_header_id
3038 from gl_je_headers h
3039 WHERE ledger_id = gbl_set_of_books_id
3040 and exists (select'x'
3041 FROM gl_period_statuses g2
3042 WHERE g2.period_year = p_period_year
3043 AND g2.ledger_id = p_set_of_books_id
3044 AND g2.application_id = 101
3045 and g2.period_name = h.period_name);
3046
3047 l_stage := 2;
3048
3049 if l_je_header_id > 0 then
3050
3051 select nvl(posted_date,creation_date)
3052 into l_posted_date
3053 from gl_je_headers h
3054 WHERE je_header_id = l_je_header_id ;
3055
3056 l_stage := 3;
3057
3058
3059 SELECT currency_code
3060 INTO gbl_currency_code
3061 FROM gl_ledgers_public_v
3062 WHERE ledger_id = gbl_set_of_books_id;
3063
3064 if l_posted_date is not null then
3065
3066 FV_UTILITY.LOG_MESG('Initialzied fv_facts1_run with ' );
3067 FV_UTILITY.LOG_MESG(' from period ' || gbl_period_name);
3068 FV_UTILITY.LOG_MESG(' Header_id ' || l_je_header_id);
3069 FV_UTILITY.LOG_MESG(' posted_date ' || l_posted_date );
3070
3071 INSERT INTO fv_facts1_run(set_of_books_id, fiscal_year, status, table_indicator,process_date,
3072 run_fed_flag ,je_header_id,posted_date)
3073 values(gbl_set_of_books_id, p_period_year, 'A', 'N',sysdate,'A' ,
3074 l_je_header_id ,l_posted_date);
3075 else
3076 gbl_error_code := -1;
3077 gbl_error_buf := 'Cannot determine the inital header_id';
3078 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3079 END IF;
3080 Else
3081 gbl_error_code := -1;
3082 gbl_error_buf := 'No Journals exist for year '||p_period_year||' for ledger '||p_set_of_books_id;
3083 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3084 End if;
3085 END IF;
3086 EXCEPTION
3087 WHEN NO_DATA_FOUND THEN
3088 gbl_error_code := -1;
3089 gbl_error_buf := SQLERRM || 'In UPDATE_FACTS1_RUN - '|| l_stage ;
3090 WHEN OTHERS THEN
3091 gbl_error_code := -1;
3092 gbl_error_buf := SQLERRM || 'When others error in UPDATE_FACTS1_RUN - '||SQLERRM;
3093 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3094
3095 END update_facts1_run;
3096 --------------------------------------------------------------------------------
3097 PROCEDURE submit_exception_report
3098 IS
3099 l_req_id number(15);
3100 l_print_option BOOLEAN;
3101 l_printer_name VARCHAR2(240);
3102 call_status BOOLEAN;
3103 l_copies NUMBER;
3104 rphase VARCHAR2(80);
3105 rstatus VARCHAR2(80);
3106 dphase VARCHAR2(80);
3107 dstatus VARCHAR2(80);
3108 message VARCHAR2(80);
3109 l_module_name varchar2(80) ;
3110 l_run_mode varchar2(80) ;
3111
3112 BEGIN
3113 l_module_name := 'submit_exception_report';
3114
3115 l_run_mode := 'Fiscal Year';
3116 l_printer_name := FND_PROFILE.VALUE('PRINTER');
3117 l_copies := FND_PROFILE.VALUE('CONC_COPIES');
3118 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS(
3119 printer => l_printer_name,
3120 copies => l_copies);
3121
3122 FV_UTILITY.LOG_MESG(l_module_name|| ' Launching FACTS I exception report ...');
3123
3124 l_req_id := FND_REQUEST.SUBMIT_REQUEST
3125 ('FV','FVFACTSE','','',FALSE, l_run_mode, gbl_fiscal_year,
3126 gbl_set_of_books_id, gbl_period_name);
3127
3128 -- If concurrent request submission failed, abort process
3129 FV_UTILITY.LOG_MESG(l_module_name|| ' Request ID for exception report = '|| TO_CHAR(L_REQ_ID));
3130
3131 IF (l_req_id = 0) THEN
3132 gbl_error_code := '-1';
3133 gbl_error_buf := 'Cannot submit FACTS Exception report';
3134 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
3135 RETURN;
3136 ELSE
3137 COMMIT;
3138 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
3139 rphase, rstatus,
3140 dphase, dstatus, message);
3141 IF call_status = FALSE THEN
3142 gbl_error_buf := 'Cannot wait for the status of FACTS Exception Report';
3143 gbl_error_code := -1;
3144 FV_UTILITY.LOG_MESG(l_module_name||'.error4', gbl_error_buf) ;
3145 RETURN;
3146 END IF;
3147 END IF;
3148
3149 End submit_exception_report;
3150 --------------------------------------------------------------------------------
3151 -- Purpose of this procedure is to process all Federal or
3152 -- Federal/Non-Federal accounts in FV_FACTS_ATTRIBUTES Table.
3153 --
3154 -- For each such account, find if its a child account. If yes, insert
3155 -- this account along with its parent and fed_nonfed attribute into
3156 -- FV_FACTS_FED_ACCOUNTS table.
3157 -- Otherwise, if the account is a Parent Account, find all the child
3158 -- accounts and insert them into FV_FACTS_FED_ACCOUNTS table along
3159 -- with fed_nonfed attribute.
3160 -- ------------------------------------------------------------------
3161 PROCEDURE GET_FEDERAL_ACCOUNTS (p_err_buff OUT NOCOPY VARCHAR2,
3162 p_err_code OUT NOCOPY NUMBER,
3163 p_sob_id IN NUMBER,
3164 p_run_year IN NUMBER)
3165 IS
3166 l_module_name VARCHAR2(200);
3167 e_invalid_acc_segment EXCEPTION;
3168 vl_segment_status BOOLEAN;
3169 vl_apps_id NUMBER := 101;
3170 vl_flex_code VARCHAR2(25) := 'GL#';
3171 vl_child_flex_value_low Fnd_Flex_Value_Hierarchies.child_flex_value_low%TYPE;
3172 vl_child_flex_value_high Fnd_Flex_Value_Hierarchies.child_flex_value_high%TYPE;
3173 l_je_header_id number(15);
3174 l_no_new_accounts number(15);
3175 l_period_num number(15);
3176 l_error_code varchar2(25);
3177 l_error_buf varchar2(500);
3178
3179 l_req_id NUMBER;
3180 call_status BOOLEAN;
3181 l_copies NUMBER;
3182 rphase VARCHAR2(80);
3183 rstatus VARCHAR2(80);
3184 dphase VARCHAR2(80);
3185 dstatus VARCHAR2(80);
3186 message VARCHAR2(80);
3187
3188 CURSOR facts_attributes_cur IS
3189 SELECT facts_acct_number, govt_non_govt
3190 FROM fv_facts_attributes
3191 WHERE set_of_books_id = p_sob_id --vg_sob_id
3192 AND govt_non_govt in ('F', 'Y');
3193
3194 CURSOR fnd_flex_value_hierarchies_cur IS
3195 SELECT child_flex_value_low, child_flex_value_high
3196 FROM fnd_flex_value_hierarchies
3197 WHERE flex_value_set_id = gbl_acc_value_set_id
3198 AND parent_flex_value = vg_sgl_acct_number;
3199
3200 CURSOR fnd_flex_values_cur IS
3201 SELECT flex_value
3202 FROM fnd_flex_values
3203 WHERE flex_value_set_id = gbl_acc_value_set_id
3204 AND flex_value BETWEEN vl_child_flex_value_low AND vl_child_flex_value_high;
3205
3206
3207 BEGIN
3208 l_module_name := g_module_name || 'Get_Federal_Accounts';
3209
3210 gbl_set_of_books_id := p_sob_id;
3211 gbl_fiscal_year := p_run_year;
3212 FV_UTILITY.LOG_MESG('In '||l_module_name);
3213
3214 gbl_error_code := 0;
3215
3216 GET_SEGMENT_NAMES;
3217
3218 IF gbl_error_code <> 0 THEN
3219 p_err_code := gbl_error_code;
3220 p_err_buff := gbl_error_buf;
3221 FV_UTILITY.LOG_MESG('Error in get_segment_names procedure: '||gbl_error_buf);
3222 RETURN;
3223 END IF;
3224
3225 FV_UTILITY.LOG_MESG('Balancing Segment: '||gbl_bal_segment_name);
3226 FV_UTILITY.LOG_MESG('Accounting Segment: '||gbl_acc_segment_name);
3227 FV_UTILITY.LOG_MESG('Chart of Account ID: '||gbl_coa_id);
3228 FV_UTILITY.LOG_MESG('Account Value Set ID: '||gbl_acc_value_set_id);
3229
3230 -- Loop through records in FV_FACTS_ATTRIBUTES table with F/Y as Fed_NonFed Attribute
3231 FOR facts_attributes_rec IN facts_attributes_cur
3232 LOOP
3233 vg_acct_number := NULL;
3234 vg_fed_nonfed := NULL;
3235 vg_acct_number := facts_attributes_rec.facts_acct_number;
3236
3237 vg_fed_nonfed := facts_attributes_rec.govt_non_govt;
3238 vg_sgl_acct_number := NULL;
3239
3240 BEGIN
3241 SELECT parent_flex_value
3242 INTO vg_sgl_acct_number
3243 FROM fnd_flex_value_hierarchies
3244 WHERE vg_acct_number
3245 BETWEEN child_flex_value_low AND child_flex_value_high
3246 AND flex_value_set_id = gbl_acc_value_set_id
3247 AND parent_flex_value <> 'T'
3248 AND parent_flex_value IN
3249 (SELECT ussgl_account
3250 FROM fv_facts_ussgl_accounts
3251 WHERE ussgl_account = parent_flex_value);
3252
3253 gbl_parent_flag := 'N';
3254
3255 POPULATE_FV_FACTS_FED_ACCOUNTS;
3256
3257
3258 IF gbl_error_code <> 0 THEN
3259 p_err_code := gbl_error_code;
3260 p_err_buff := gbl_error_buf;
3261 FV_UTILITY.LOG_MESG('Error in populate_fv_facts_fed_accounts procedure: '||gbl_error_buf);
3262 RETURN;
3263 END IF;
3264
3265 EXCEPTION
3266 WHEN NO_DATA_FOUND THEN
3267 -- If parent not found, then account itself is the parent.
3268 -- Insert it into FV_FACTS_FED_ACCOUNTS if not already present.
3269 gbl_parent_flag := 'Y';
3270 vg_sgl_acct_number := vg_acct_number;
3271
3272 -- If parent not found, then account itself is parent, find all its child accounts
3273 -- and insert them into FV_FACTS_FED_ACCOUNTS table if not already present.
3274
3275 FOR fnd_flex_value_hierarchies_rec IN fnd_flex_value_hierarchies_cur
3276 LOOP
3277 vl_child_flex_value_low := NULL;
3278 vl_child_flex_value_high := NULL;
3279 vl_child_flex_value_low := fnd_flex_value_hierarchies_rec.child_flex_value_low;
3280 vl_child_flex_value_high := fnd_flex_value_hierarchies_rec.child_flex_value_high;
3281
3282 FOR fnd_flex_values_rec IN fnd_flex_values_cur
3283 LOOP
3284 vg_acct_number := fnd_flex_values_rec.flex_value;
3285 POPULATE_FV_FACTS_FED_ACCOUNTS;
3286
3287 IF gbl_error_code <> 0 THEN
3288 p_err_code := gbl_error_code;
3289 p_err_buff := gbl_error_buf;
3290 FV_UTILITY.LOG_MESG('Error in populate_fv_facts_fed_accounts procedure: '||gbl_error_buf);
3291 RETURN;
3292 END IF;
3293
3294
3295 END LOOP; -- fnd_flex_values_cur
3296 END LOOP; -- fnd_flex_value_hierarchies_cur
3297 END; -- Exception
3298 END LOOP; -- facts_attributes_cur
3299
3300 UPDATE_FACTS1_RUN(P_RUN_YEAR, GBL_SET_OF_BOOKS_ID);
3301
3302 if gbl_error_code = 0 then
3303
3304 COMMIT;
3305
3306 Fnd_Stats.GATHER_TABLE_STATS(ownname=>'FV',tabname=>'FV_FACTS1_FED_ACCOUNTS');
3307
3308 /* check whether to call the Journal creation automatically
3309 if there are new accounts created , then call the journal creation process
3310 until the last period the journal creation process ran for that sob and year */
3311
3312 l_je_header_id := 0;
3313
3314 select nvl(je_header_id,0),nvl(jc_run_month,0) into l_je_header_id,l_period_num
3315 from fv_facts1_RUN
3316 where set_of_books_id = gbl_set_of_books_id
3317 AND fiscal_year = gbl_fiscal_year;
3318
3319
3320 if (l_period_num > 0) then
3321
3322 /* Journal creation process already ran , so need to pikc journals for new a/c */
3323
3324 select count(*) into l_no_new_accounts from fv_facts1_fed_accounts
3325 where set_of_books_id = gbl_set_of_books_id
3326 AND fiscal_year = gbl_fiscal_year
3327 and jc_flag = 'N';
3328
3329 fv_utility.log_mesg('The Re run of Federal Account Creation Process , found ' ||
3330 l_no_new_accounts || ' new accounts');
3331
3332 if l_no_new_accounts > 0 then
3333
3334 select period_name into gbl_period_name
3335 from gl_period_statuses
3336 where ledger_id = gbl_set_of_books_id
3337 AND period_year = gbl_fiscal_year
3338 and application_id = 101
3339 and period_num = l_period_num;
3340
3341 fv_utility.log_mesg('Calling Journal Creation process.');
3342 l_req_id := FND_REQUEST.SUBMIT_REQUEST
3343 ('FV','FVFC1JCR','','',FALSE, gbl_set_of_books_id, gbl_period_name,'Y');
3344 FV_UTILITY.LOG_MESG(l_module_name||
3345 ' REQUEST ID FOR JOURNAL CREATION PROCESS = '|| TO_CHAR(L_REQ_ID));
3346 IF (l_req_id = 0) THEN
3347 gbl_error_code := -1;
3348 gbl_error_buf := ' Cannot submit FACTS Journal Creation process';
3349 fv_utility.log_mesg(gbl_error_buf);
3350 p_err_code := -1;
3351 p_err_buff := gbl_error_buf;
3352 RETURN;
3353 ELSE
3354 COMMIT;
3355 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
3356 rphase, rstatus,
3357 dphase, dstatus, message);
3358 IF call_status = FALSE THEN
3359 gbl_error_buf := 'Cannot wait for the status of Journal Creation Process';
3360 gbl_error_code := -1;
3361 FV_UTILITY.LOG_MESG(l_module_name|| '.error4', gbl_error_buf) ;
3362 p_err_code := -1;
3363 p_err_buff := gbl_error_buf;
3364 RETURN;
3365 END IF;
3366 END IF;
3367
3368 -- FV_FACTS1_GL_PKG.MAIN(l_error_buf, l_error_code, gbl_set_of_books_id, gbl_period_name, 'Y');
3369 -- p_err_code := l_error_code;
3370 -- p_err_buff := l_error_buf;
3371 End if;
3372
3373 End if;
3374
3375
3376 ELSE
3377 p_err_code := gbl_error_code;
3378 p_err_buff := gbl_error_buf;
3379 End if;
3380 EXCEPTION
3381 WHEN e_Invalid_Acc_segment THEN
3382 p_err_code := 2 ;
3383 p_err_buff := 'GET_FEDERAL_ACCOUNTS -- Error Reading Accounting Segments' ;
3384 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, p_err_buff);
3385 RETURN;
3386
3387 WHEN OTHERS THEN
3388 p_err_code := SQLCODE;
3389 p_err_buff := SQLERRM ||
3390 ' -- Error in Get_Federal_Accounts procedure';
3391 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3392 l_module_name||'.exception1',p_err_buff);
3393 RETURN;
3394 END GET_FEDERAL_ACCOUNTS;
3395 -- ------------------------------------------------------------------
3396 -- Procedure Populate_Fv_Facts_Fed_Accounts
3397 -- ------------------------------------------------------------------
3398 -- This procedure gets called from Get_Federal_Accounts procedure.
3399 -- Purpose of this procedure is insert rows into fv_facts_fed_accounts
3400 -- table.
3401 -- ------------------------------------------------------------------
3402 PROCEDURE POPULATE_FV_FACTS_FED_ACCOUNTS IS
3403 l_module_name VARCHAR2(200);
3404 vl_dummy VARCHAR2(1);
3405 l_fed_non_fed VARCHAR2(50);
3406 l_dummy_fed_non_fed VARCHAR2(50);
3407
3408 BEGIN
3409 l_module_name := g_module_name || 'Populate_Fv_Facts_Fed_Accounts';
3410
3411 l_fed_non_fed := NULL;
3412 l_dummy_fed_non_fed := NULL;
3413
3414 IF NOT gbl_header_printed THEN
3415 fnd_file.put_line(fnd_file.output,'Account Number '||rpad(' ', 16)||'Identified as/ Moved to');
3416 fnd_file.put_line(fnd_file.output,lpad(' ', 31)||'Moved from ');
3417 fnd_file.put_line(fnd_file.output,'------------------------------ '||'------------------------ ------------------------');
3418 gbl_header_printed := TRUE;
3419 END IF;
3420
3421 BEGIN
3422 SELECT fed_non_fed
3423 INTO vl_dummy
3424 FROM fv_facts1_fed_accounts
3425 WHERE account_number = vg_acct_number
3426 AND set_of_books_id = gbl_set_of_books_id
3427 AND fiscal_year = gbl_fiscal_year; --vg_sob_id;
3428
3429
3430 if (vl_dummy <> vg_fed_nonfed) then
3431
3432 -- To handle if the child is already processed
3433 -- before parent.
3434 IF gbl_parent_flag = 'N' THEN
3435 UPDATE fv_facts1_fed_accounts
3436 SET fed_non_fed = vg_fed_nonfed
3437 WHERE account_number = vg_acct_number
3438 AND set_of_books_id = gbl_set_of_books_id
3439 AND fiscal_year = gbl_fiscal_year;
3440 END IF;
3441
3442 l_dummy_fed_non_fed :=
3443 CASE vl_dummy
3444 WHEN 'F' THEN RPAD('Federal', 25)
3445 WHEN 'Y' THEN RPAD('Federal or Non-Federal', 25)
3446 END;
3447
3448 l_fed_non_fed :=
3449 CASE vg_fed_nonfed
3450 WHEN 'F' THEN 'Federal'
3451 WHEN 'Y' THEN 'Federal or Non-Federal'
3452 END;
3453
3454 -- fv_utility.log_mesg('Account Flag for ' || vg_acct_number
3455 -- || ' moved from ' || vl_dummy || ' To ' || vg_fed_nonfed);
3456 fnd_file.put_line(fnd_file.output, RPAD(vg_acct_number, 31) ||
3457 l_dummy_fed_non_fed || l_fed_non_fed );
3458 End if;
3459
3460
3461 EXCEPTION
3462 WHEN NO_DATA_FOUND THEN
3463 INSERT INTO fv_facts1_fed_accounts
3464 (account_number,
3465 sgl_account_number,
3466 set_of_books_id,
3467 fed_non_fed,
3468 last_run_date,
3469 jc_flag,
3470 fiscal_year
3471 )
3472 VALUES
3473 (vg_acct_number,
3474 vg_sgl_acct_number,
3475 gbl_set_of_books_id,
3476 vg_fed_nonfed,
3477 sysdate,
3478 'N',
3479 gbl_fiscal_year
3480 );
3481
3482 --fv_utility.log_mesg('Account ' || vg_acct_number || ' Identified as ' || vg_fed_nonfed);
3483
3484 l_fed_non_fed :=
3485 CASE vg_fed_nonfed
3486 WHEN 'F' THEN RPAD('Federal', 25)
3487 WHEN 'Y' THEN RPAD('Federal or Non-Federal', 25)
3488 END;
3489
3490 fnd_file.put_line(fnd_file.output, RPAD(vg_acct_number,31) || l_fed_non_fed);
3491
3492
3493 gbl_error_code := 0;
3494 END;
3495
3496 --COMMIT;
3497 EXCEPTION
3498 WHEN OTHERS THEN
3499 gbl_error_code := SQLCODE;
3500 gbl_error_buf := SQLERRM ||
3501 ' -- Error in Populate_Fv_Facts_Fed_Accounts procedure';
3502 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||
3503 '.exception1',gbl_error_buf);
3504 END POPULATE_FV_FACTS_FED_ACCOUNTS;
3505 --------------------------------------------------------------------------------
3506 --------------------------------------------------------------------------------
3507 BEGIN
3508 g_module_name := 'fv.plsql.FV_FACTS1_PKG.';
3509
3510
3511 END fv_facts1_pkg;