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