[Home] [Help]
PACKAGE BODY: APPS.FV_GTAS_INTERFACE
Source
1 PACKAGE BODY fv_gtas_interface AS
2 /* $Header: FVGTINTB.pls 120.30.12020000.1 2013/02/12 20:00:19 appldev noship $ */
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_reports VARCHAR2(1);
11 gbl_fiscal_year gl_period_statuses.period_year%TYPE;
12 gbl_period_num_low gl_period_statuses.period_num%TYPE;
13 gbl_period_num_high gl_period_statuses.period_num%TYPE;
14 gbl_bal_segment_name VARCHAR2(10);
15 gbl_acc_segment_name VARCHAR2(10);
16 gbl_acc_value_set_id NUMBER;
17 gbl_currency_code gl_sets_of_books.currency_code%TYPE;
18 gbl_prev_acct fv_gtas1_report_t2.account_number%TYPE;
19 gbl_bal_segment fv_gtas1_report_t2.fund_value%TYPE;
20
21 gbl_exception_exists varchar2(1) := 'N';
22 gbl_header_printed BOOLEAN := FALSE;
23 gbl_exception_occurred BOOLEAN := FALSE;
24
25 vg_acct_number VARCHAR2(30);
26 --vg_fed_nonfed VARCHAR2(1);
27 vg_sgl_acct_number VARCHAR2(30);
28
29 Vl_Catb_Rc_Map_Status Varchar2(10);
30 Vl_Prn_Rc_Map_Status Varchar2(10);
31 Vl_Pci_Rc_Map_Status Varchar2(10);
32
33 g_pci_prg_header_id fv_facts_prc_hdr.prc_header_id%type;
34 G_Prc_Prg_Header_Id Fv_Facts_Prc_Hdr.Prc_Header_Id%Type;
35 G_Catb_Prg_Header_Id Fv_Facts_Prc_Hdr.Prc_Header_Id%Type;
36 G_Catb_Prc_Flag Varchar2(1);
37 G_Pci_Prc_Flag Varchar2(1);
38 G_Prc_Prc_Flag Varchar2(1);
39 g_catb_prG_SEG fv_facts_prc_hdr.program_segment%TYPE;
40 g_prc_prG_SEG fv_facts_prc_hdr.program_segment%TYPE;
41 g_pci_prG_SEG fv_facts_prc_hdr.program_segment%TYPE;
42
43 g_Treasury_Symbol_Low Varchar2(35);
44 g_Treasury_Symbol_high Varchar2(35);
45
46 vg_fed_nonfed_flag VARCHAR2(1);
47 vg_authority_type_flag VARCHAR2(1);
48 vg_bud_impact_ind_flag VARCHAR2(1);
49 vg_trading_partner_flag VARCHAR2(1);
50 g_is_acct_parent VARCHAR2(1);
51 gbl_reporting_period NUMBER;
52 gbl_agency_id_low fv_treasury_symbols.department_id%TYPE;
53 gbl_agency_id_high fv_treasury_symbols.department_id%TYPE;
54 gbl_main_account_low fv_treasury_symbols.fund_group_code%TYPE;
55 gbl_main_account_high fv_treasury_symbols.fund_group_code%TYPE;
56
57 --------------------------------------------------------------------------------
58 PROCEDURE get_segment_names;
59 Procedure Submit_Exception_Report;
60 --PROCEDURE process_input_parameters;
61
62 PROCEDURE populate_temp2
63 ( p_account_number IN Varchar2,
64 p_amount IN Number,
65 --p_d_c_indicator IN Varchar2 DEFAULT NULL,
66 p_fiscal_year IN Number,
67 p_record_category IN Varchar2,
68 p_ussgl_account IN Varchar2,
69 p_set_of_books_id IN Number,
70 p_reported_status IN Varchar2 DEFAULT NULL,
71 p_exch_non_exch IN Varchar2 DEFAULT NULL,
72 p_cust_non_cust IN Varchar2 DEFAULT NULL,
73 p_fund_value IN Varchar2,
74 p_beginning_bal IN Number DEFAULT 0,
75 p_ccid IN Number,
76 p_account_type IN Varchar2 DEFAULT NULL,
77 p_dr_amount IN Number DEFAULT 0,
78 p_cr_amount IN Number DEFAULT 0,
79 p_DIRECT_OR_REIMB_CODE IN VARCHAR2 DEFAULT NULL,
80 p_APPOR_CAT_CODE IN VARCHAR2 DEFAULT NULL,
81 p_APPOR_CAT_B_CODE IN VARCHAR2 DEFAULT NULL,
82 p_PROGRAM_REPT_CODE IN VARCHAR2 DEFAULT NULL,
83 p_BEA_CAT_CODE IN VARCHAR2 DEFAULT NULL,
84 p_BORR_SRC_CODE IN VARCHAR2 DEFAULT NULL,
85 p_NEW_BAL_CODE IN VARCHAR2 DEFAULT NULL,
86 p_CUR_SUBSEQUENT_CODE IN VARCHAR2 DEFAULT NULL,
87 p_PYA_CODE IN VARCHAR2 DEFAULT NULL,
88 p_CREDIT_COHORT_YR IN VARCHAR2 DEFAULT NULL,
89 p_PROGRAM_COST_IND IN VARCHAR2 DEFAULT NULL,
90 p_TREASURY_SYMBOL_ID IN NUMBER);
91 PROCEDURE cleanup_process;
92 PROCEDURE get_ussgl_acct_num (p_acct_num IN Varchar2,
93 p_fund_value IN Varchar2,
94 p_ccid IN NUMBER,
95 p_amount IN NUMBER,
96 p_sgl_acct_num OUT NOCOPY Number,
97 p_exch_non_exch OUT NOCOPY Varchar2,
98 p_cust_non_cust OUT NOCOPY Varchar2,
99 p_DIRECT_OR_REIMB_CODE OUT NOCOPY Varchar2,
100 p_APPOR_CAT_CODE OUT NOCOPY Varchar2,
101 p_APPOR_CAT_B_CODE OUT NOCOPY Varchar2,
102 p_PROGRAM_REPT_CODE OUT NOCOPY Varchar2,
103 p_BEA_CAT_CODE OUT NOCOPY Varchar2,
104 p_BORR_SRC_CODE OUT NOCOPY Varchar2,
105 p_NEW_BAL_CODE OUT NOCOPY Varchar2,
106 p_CUR_SUBSEQUENT_CODE OUT NOCOPY Varchar2,
107 p_PYA_CODE OUT NOCOPY Varchar2,
108 p_CREDIT_COHORT_YR OUT NOCOPY Varchar2,
109 p_PROGRAM_COST_IND OUT NOCOPY Varchar2,
110 p_exception_category OUT NOCOPY Varchar2,
111 p_Treasury_symbol_id OUT NOCOPY NUMBER,
112 p_end_bal_ind OUT NOCOPY VARCHAR2);
113
114 FUNCTION get_account_type
115 (p_account_number VARCHAR2) RETURN VARCHAR2;
116 PROCEDURE get_ussgl_info
117 (P_Ussgl_Acct_Num In Varchar2,
118 p_enabled_flag IN OUT NOCOPY Varchar2,
119 p_acct_not_found OUT NOCOPY VARCHAR2);
120
121 PROCEDURE create_end_bal_record;
122
123 PROCEDURE check_prc_map_seg(p_fund_value IN VARCHAR2,
124 p_code_type IN VARCHAR2,
125 p_sob_id IN NUMBER,
126 p_catb_status OUT NOCOPY VARCHAR2,
127 p_prn_status OUT NOCOPY VARCHAR2,
128 p_pci_status OUT NOCOPY VARCHAR2
129 );
130
131 PROCEDURE get_prc_map_val(P_Segment_Name IN Varchar2,
132 P_Ccid IN Number,
133 P_Prc_Header_Id IN Number,
134 p_prc_val OUT NOCOPY VARCHAR2);
135 PROCEDURE log (module IN VARCHAR2,
136 message_line IN VARCHAR2);
137
138 PROCEDURE delete_fed_accounts(p_set_of_books_id IN VARCHAR2,
139 p_period_year IN VARCHAR2);
140 PROCEDURE populate_fv_gtas_fed_accounts;
141 PROCEDURE update_gtas_run(p_period_year in VARCHAR2,
142 p_set_of_books_id in VARCHAR2);
143 FUNCTION get_acct_type(p_acct_num IN VARCHAR2)
144 RETURN VARCHAR2;
145 PROCEDURE print_header;
146 PROCEDURE del_disabled_accts(p_acct_num IN VARCHAR2);
147
148 gbl_fund_range_low FV_Fund_Parameters.fund_value%TYPE;
149 gbl_fund_range_high FV_Fund_Parameters.fund_value%TYPE;
150 gbl_period_num Gl_Balances.period_num%TYPE;
151 gbl_period_year gl_period_statuses.period_year%TYPE;
152
153 g_Treasury_Symbol_Low_id number(15);
154 g_Treasury_Symbol_high_id number(15);
155
156 --------------------------------------------------------------------------------
157 --------------------------------------------------------------------------------
158 -- Get the period num for the parameter period and also the first period num
159 -- for the year.
160 --------------------------------------------------------------------------------
161 PROCEDURE GET_PROCESS_DATES
162 IS
163 l_module_name VARCHAR2(200);
164 l_temp_mesg VARCHAR2(250);
165 BEGIN
166 l_module_name := g_module_name || 'GET_PROCESS_DATES';
167 FV_UTILITY.LOG_MESG('Begin: '||l_module_name);
168
169 -- Get the period year for the period parameter passed.
170 l_temp_mesg := 'getting period year.';
171 SELECT period_year
172 INTO gbl_period_year
173 FROM gl_period_statuses p
174 WHERE p.application_id = 101
175 AND p.ledger_id = gbl_set_of_books_id
176 AND p.period_name = gbl_period_name;
177 -- Get the first period of the year
178 l_temp_mesg := 'getting first period number of the year.';
179 SELECT MIN(period_num)
180 INTO gbl_period_num_low
181 FROM gl_period_statuses
182 WHERE period_year = gbl_period_year
183 AND application_id = 101
184 AND closing_status <> 'F'
185 AND closing_status <> 'N'
186 AND adjustment_period_flag = 'N'
187 AND ledger_id = gbl_set_of_books_id;
188 -- Get the period num for the parameter to period
189 l_temp_mesg := 'getting period number of the parameter to period.';
190
191 SELECT period_num,
192 CASE
193 WHEN to_char(end_date, 'MM')between 1 and 9 then (to_char(end_date, 'MM')+3)
194 WHEN to_char(end_date, 'MM')between 10 and 12 then (to_char(end_date, 'MM')-9)
195 END
196 INTO gbl_period_num_high, gbl_reporting_period
197 FROM gl_period_statuses p
198 WHERE period_name = gbl_period_name
199 AND p.application_id = 101
200 AND p.ledger_id = gbl_set_of_books_id
201 AND p.period_year = gbl_period_year;
202
203 IF (gbl_period_num_low = 0 OR gbl_period_num_high = 0) THEN
204 gbl_error_code := 2 ;
205 gbl_error_buf := l_module_name||' Period number '||
206 'found zero for the passed fiscal year.' ;
207 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
208 RETURN;
209 END IF;
210 FV_UTILITY.LOG_MESG('Period Year: '||gbl_period_year);
211 FV_UTILITY.LOG_MESG('Period Number Low: '||gbl_period_num_low);
212 FV_UTILITY.LOG_MESG('Period Number High: '||gbl_period_num_high);
213
214 FV_UTILITY.LOG_MESG('End: '||l_module_name);
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 gbl_error_code := 2;
218 gbl_error_buf := l_module_name||' - No data found when '||l_temp_mesg;
219 FV_UTILITY.LOG_MESG(gbl_error_buf);
220 WHEN OTHERS THEN
221 gbl_error_code := 2;
222 gbl_error_buf := l_module_name||' - When others error when '||l_temp_mesg||':'||SQLERRM;
223 Fv_Utility.Log_Mesg(Gbl_Error_Buf);
224 End Get_Process_Dates;
225
226 PROCEDURE MAIN(p_err_buff OUT NOCOPY VARCHAR2,
227 p_err_code OUT NOCOPY NUMBER,
228 P_Sob_Id In Number,
229 p_period_name In VARCHAR2,
230 p_agency_id_low in varchar2,
231 p_agency_id_high in varchar2,
232 p_main_account_low IN VARCHAR2,
233 p_main_account_high IN VARCHAR2,
234 p_save_ending_Bal IN VARCHAR2 DEFAULT 'N',
235 p_run_reports IN VARCHAR2
236 )
237 IS
238
239 l_module_name VARCHAR2(200) := g_module_name || 'MAIN';
240
241 l_req_id NUMBER;
242 l_print_option BOOLEAN;
243 l_printer_name VARCHAR2(240);
244 call_status BOOLEAN;
245 l_copies NUMBER;
246 rphase VARCHAR2(80);
247 rstatus VARCHAR2(80);
248 dphase VARCHAR2(80);
249 dstatus VARCHAR2(80);
250 message VARCHAR2(80);
251
252 l_error_buf varchar2(2000);
253 l_error_code Number(15);
254
255 l_row_exists NUMBER;
256 x number;
257 xml_layout boolean;
258 BEGIN
259
260 log(l_module_name, 'Begin');
261
262 l_printer_name := FND_PROFILE.VALUE('PRINTER');
263 l_copies := FND_PROFILE.VALUE('CONC_COPIES');
264
265 gbl_error_code := 0;
266 gbl_error_buf := NULL;
267 gbl_set_of_books_id := p_sob_id;
268 gbl_run_reports := p_run_reports;
269 Gbl_Period_Name := P_Period_Name;
270
271 Get_Process_Dates;
272
273 gbl_fiscal_year := gbl_period_year;
274
275 log(l_module_name, 'Parameters ');
276 log(l_module_name, '---------- ');
277 log(l_module_name, 'SOB ID: '||Gbl_Set_Of_Books_Id);
278 log(l_module_name, 'Run Reports: '||gbl_run_reports);
279 log(l_module_name, 'Period: '||gbl_period_name);
280 log(l_module_name, 'Fiscal Year: '||Gbl_Fiscal_Year);
281 log(l_module_name, 'p_agency_id_Low: '||p_agency_id_low);
282 log(l_module_name, 'p_agency_id_high: '||p_agency_id_high);
283 log(l_module_name, 'p_main_account_low: '||p_main_account_low);
284 log(l_module_name, 'p_main_account_high: '||p_main_account_high);
285
286 get_segment_names;
287
288 IF gbl_error_code = 0 THEN
289 cleanup_process;
290 END IF;
291
292 gbl_exception_exists := 'N';
293
294 /*
295 fv_utility.log_mesg('Calling Journal Creation process.');
296 L_Req_Id := Fnd_Request.Submit_Request
297 ('FV','FVGTJCRB','','',FALSE, gbl_set_of_books_id, gbl_period_name,'N');
298 FV_UTILITY.LOG_MESG(l_module_name||
299 ' REQUEST ID FOR JOURNAL CREATION PROCESS = '|| TO_CHAR(L_REQ_ID));
300 IF (l_req_id = 0) THEN
301 Gbl_Error_Code := -1;
302 gbl_error_buf := ' Cannot submit GTAS Journal Creation process';
303 fv_utility.log_mesg(gbl_error_buf);
304 p_err_code := -1;
305 p_err_buff := gbl_error_buf;
306 RETURN;
307 ELSE
308 COMMIT;
309 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
310 rphase, rstatus,
311 dphase, dstatus, message);
312 IF call_status = FALSE THEN
313 gbl_error_buf := 'Cannot wait for the status of Journal Creation Process';
314 gbl_error_code := -1;
315 FV_UTILITY.LOG_MESG(l_module_name|| '.error4', gbl_error_buf) ;
316 p_err_code := -1;
317 p_err_buff := gbl_error_buf;
318 RETURN;
319 END IF;
320 END IF;
321 */
322 gbl_agency_id_low := p_agency_id_low;
323 gbl_agency_id_high := p_agency_id_high;
324 gbl_main_account_low := p_main_account_low;
325 gbl_main_account_high := p_main_account_high;
326
327 IF gbl_error_code = 0 THEN
328 log(l_module_name, 'Calling GTAS Attributes Creation process.');
329 SET_UP_GTAS_ATTRIBUTES(l_error_buf ,
330 l_error_code ,
331 gbl_set_of_books_id ,
332 gbl_fiscal_year);
333 gbl_error_code := l_error_code;
334 gbl_error_buf := l_error_buf;
335 END IF;
336
337 IF gbl_error_code = 0 THEN
338 log(l_module_name, 'Calling Exception report');
339 submit_exception_report;
340 End if;
341
342 --Create ending balances only if p_save_ending_Bal parameter is Y
343 --and there are no errors
344 IF (gbl_error_code = 0 AND p_save_ending_Bal = 'Y' ) then
345 SELECT count(*)
346 Into L_Row_Exists
347 FROM fv_gtas_ending_balances
348 WHERE fiscal_year = gbl_period_year
349 AND set_of_books_id = gbl_set_of_books_id
350 AND rownum = 1;
351
352 IF (l_row_exists > 0) THEN
353 log(l_module_name, 'Deleting recs from fv_gtas_ending_bal
354 for Period Year: '||gbl_period_year);
355
356 DELETE FROM fv_gtas_ending_balances
357 WHERE set_of_books_id = gbl_set_of_books_id
358 AND fiscal_year = gbl_period_year;
359 log(l_module_name, 'Deleted '||SQL%ROWCOUNT ||
360 ' recs from fv_gtas_ending_balances.');
361 COMMIT;
362 END IF;
363
364 create_end_bal_record;
365 END IF;
366
367 --Submit reports only if edit check is passed.
368 IF (gbl_error_code = 0 AND p_run_reports = 'Y' ) then
369
370 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS(
371 printer => l_printer_name,
372 copies => l_copies);
373
374 log(l_module_name, ' LAUNCHING GTAS I ATB FILE GENERATION PROCESS ...');
375
376 -- Submit ATB file process
377 xml_layout := FND_REQUEST.ADD_LAYOUT('FV',
378 'FVGTINTATB',
379 'en',
380 'US',
381 'PDF');
382 L_Req_Id := Fnd_Request.Submit_Request
383 ('FV','FVGTINTATB','','',FALSE, gbl_set_of_books_id,gbl_period_name,
384 gbl_period_num_high, gbl_period_year,
385 gbl_agency_id_low, gbl_agency_id_high,
386 gbl_main_account_low, gbl_main_account_high);
387
388 log(l_module_name, 'Request id for atb file = '|| TO_CHAR(L_REQ_ID));
389
390 -- if concurrent request submission failed then abort process
391 IF (l_req_id = 0) THEN
392 p_err_code := '-1';
393 p_err_buff := ' Cannot submit GTAS report ATB file process';
394 RETURN;
395 log(l_module_name, gbl_error_buf);
396 ELSE
397 COMMIT;
398 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
399 rphase, rstatus,
400 dphase, dstatus, message);
401 IF call_status = FALSE THEN
402 p_err_buff := 'Cannot wait for the status of GTAS ATB Report';
403 p_err_code := -1;
404 log(l_module_name, '.error4'|| gbl_error_buf) ;
405 RETURN;
406 END IF;
407 END IF;
408
409 -- Print the GTAS Bulk File
410 L_Req_Id := fnd_request.submit_request
411 ('FV','FVGBLKCR','','',FALSE, 'FVGBLKCR', '',gbl_period_num_high, gbl_period_year,
412 gbl_set_of_books_id, lpad(to_char(gbl_reporting_period),2,'0')) ;
413 COMMIT;
414 call_status := fnd_concurrent.wait_for_request(L_Req_Id, 0, 0, rphase,
415 rstatus, dphase, dstatus, message);
416
417 END IF;
418
419 IF gbl_error_code = 0 THEN
420 UPDATE fv_gtas_run
421 SET status = 'Y',
422 run_fed_flag = 'I',
423 process_date = sysdate,
424 begin_bal_diff_flag = 'Y',
425 period_num = gbl_period_num_high
426 WHERE set_of_books_id = gbl_set_of_books_id
427 AND fiscal_year = gbl_fiscal_year
428 AND table_indicator = 'N';
429
430 COMMIT;
431 END IF;
432
433 IF gbl_error_code <> 0 THEN
434 p_err_code := gbl_error_code;
435 p_err_buff := gbl_error_buf;
436 log(l_module_name, gbl_error_buf);
437 ROLLBACK;
438 RETURN;
439 END IF;
440
441 log(l_module_name, 'GTAS Interface Process completed successfully.');
442 p_err_buff := 'GTAS Interface Process completed successfully';
443
444 log(l_module_name, 'End');
445
446 EXCEPTION WHEN OTHERS THEN
447 p_err_code := SQLCODE;
448 p_err_buff := 'When others exception in Main - '||SQLERRM;
449 ROLLBACK;
450 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
451
452 END main;
453 --------------------------------------------------------------------------------
454 -- Get balancing and accounting segments
455 --------------------------------------------------------------------------------
456 PROCEDURE GET_SEGMENT_NAMES
457 IS
458
459 l_module_name VARCHAR2(200):= g_module_name || 'GET_SEGMENT_NAMES';
460 l_temp_mesg VARCHAR2(100);
461 l_app_id NUMBER := 101;
462 l_flex_code VARCHAR2(10) := 'GL#';
463 l_segment_found BOOLEAN;
464 invalid_bal_segment EXCEPTION;
465 invalid_acc_segment EXCEPTION;
466
467 BEGIN
468
469 log(l_module_name, 'Begin');
470
471 l_temp_mesg := ' getting balancing/accounting segment. ';
472
473
474 SELECT chart_of_accounts_id
475 INTO gbl_coa_id
476 FROM gl_ledgers_public_v
477 WHERE ledger_id = gbl_set_of_books_id;
478
479 log(l_module_name, 'COA ID: '||gbl_coa_id);
480
481 -- Get Balancing Segment Name
482 -----------------------------
483 l_segment_found := FND_FLEX_APIS.get_segment_column
484 (l_app_id,
485 l_flex_code,
486 gbl_coa_id,
487 'GL_BALANCING',
488 gbl_bal_segment_name) ;
489
490 IF NOT l_segment_found THEN
491 RAISE invalid_bal_segment;
492 END IF;
493
494 -- Get Accounting Segment Name
495 ------------------------------
496 l_segment_found := FND_FLEX_APIS.get_segment_column
497 (l_app_id,
498 l_flex_code,
499 gbl_coa_id,
500 'GL_ACCOUNT',
501 gbl_acc_segment_name);
502 IF NOT l_segment_found THEN
503 RAISE invalid_acc_segment;
504 END IF;
505
506 -- Get the value set id
507 l_temp_mesg := ' getting account value set id. ';
508 SELECT flex_value_set_id
509 INTO gbl_acc_value_set_id
510 FROM fnd_id_flex_segments
511 WHERE application_column_name = gbl_acc_segment_name
512 AND id_flex_code = 'GL#'
513 AND id_flex_num = gbl_coa_id;
514
515 log(l_module_name, 'Balancing Segment: '||gbl_bal_segment_name);
516 log(l_module_name, 'Accounting Segment: '||gbl_acc_segment_name);
517 log(l_module_name, 'Accounting value set id: '||gbl_acc_value_set_id);
518
519 EXCEPTION
520 WHEN invalid_bal_segment THEN
521 gbl_error_code := -1 ;
522 gbl_error_buf := 'Error while fetching balancing segment.';
523 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
524 WHEN invalid_acc_segment THEN
525 gbl_error_code := -1 ;
526 gbl_error_buf := 'Error while fetching accounting segment.';
527 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
528 WHEN NO_DATA_FOUND THEN
529 gbl_error_code := -1 ;
530 gbl_error_buf := l_module_name||' - No data found when'||l_temp_mesg;
531 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
532 WHEN OTHERS THEN
533 gbl_error_code := -1 ;
534 gbl_error_buf := l_module_name||' - When others error when'||
535 l_temp_mesg||SQLERRM;
536 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
537
538 END get_segment_names;
539
540 --------------------------------------------------------------------------------
541 -- PROCEDURE POPULATE_TEMP2
542 --------------------------------------------------------------------------------
543 PROCEDURE POPULATE_TEMP2
544 ( p_account_number IN Varchar2,
545 p_amount IN Number,
546 --p_d_c_indicator IN Varchar2,
547 p_fiscal_year IN Number,
548 p_record_category IN Varchar2,
549 p_ussgl_account IN Varchar2,
550 p_set_of_books_id IN Number,
551 p_reported_status IN Varchar2,
552 p_exch_non_exch IN Varchar2,
553 p_cust_non_cust IN Varchar2,
554 p_fund_value IN Varchar2,
555 p_beginning_bal IN Number,
556 p_ccid IN Number,
557 p_account_type IN Varchar2,
558 p_dr_amount IN Number,
559 p_cr_amount IN Number,
560 p_DIRECT_OR_REIMB_CODE IN VARCHAR2,
561 p_APPOR_CAT_CODE IN VARCHAR2,
562 p_APPOR_CAT_B_CODE IN VARCHAR2,
563 p_PROGRAM_REPT_CODE IN VARCHAR2,
564 p_BEA_CAT_CODE IN VARCHAR2,
565 p_BORR_SRC_CODE IN VARCHAR2,
566 p_NEW_BAL_CODE IN VARCHAR2,
567 p_CUR_SUBSEQUENT_CODE IN VARCHAR2,
568 p_PYA_CODE IN VARCHAR2,
569 p_CREDIT_COHORT_YR IN VARCHAR2,
570 p_PROGRAM_COST_IND IN VARCHAR2,
571 p_TREASURY_SYMBOL_ID IN NUMBER)
572 IS
573 l_module_name VARCHAR2(200);
574 BEGIN
575
576 l_module_name := g_module_name||' POPULATE_TEMP2';
577
578 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
579 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
580 'Inserting a record in T2 for record_category :'||
581 p_record_category||' for ccid: '||p_ccid);
582 END IF;
583
584 INSERT INTO fv_gtas1_report_t2
585 (account_number ,
586 amount ,
587 d_c_indicator ,
588 fiscal_year ,
589 record_category ,
590 ussgl_account ,
591 set_of_books_id ,
592 reported_status ,
593 exch_non_exch ,
594 cust_non_cust ,
595 fund_value ,
596 beginning_balance ,
597 ccid ,
598 account_type ,
599 dr_amount ,
600 cr_amount ,
601 DIRECT_OR_REIMB_CODE ,
602 APPOR_CAT_CODE ,
603 APPOR_CAT_B_CODE ,
604 PROGRAM_REPT_CODE ,
605 BEA_CAT_CODE ,
606 BORR_SRC_CODE ,
607 NEW_BAL_CODE ,
608 CUR_SUBSEQUENT_CODE ,
609 PYA_CODE ,
610 CREDIT_COHORT_YR ,
611 PROGRAM_COST_IND ,
612 TREASURY_SYMBOL_ID )
613 VALUES
614 ( p_account_number,
615 nvl(p_amount, 0),
616 DECODE(SIGN(nvl(p_amount, 0)), 0 ,'D', 1, 'D', -1, 'C'),
617 p_fiscal_year,
618 p_record_category,
619 p_ussgl_account,
620 p_set_of_books_id,
621 'E',--p_reported_status,
622 p_exch_non_exch,
623 p_cust_non_cust,
624 p_fund_value,
625 p_beginning_bal,
626 p_ccid,
627 p_account_type,
628 p_dr_amount,
629 p_cr_amount,
630 p_DIRECT_OR_REIMB_CODE ,
631 p_APPOR_CAT_CODE ,
632 p_APPOR_CAT_B_CODE ,
633 p_PROGRAM_REPT_CODE ,
634 p_BEA_CAT_CODE ,
635 p_BORR_SRC_CODE ,
636 p_NEW_BAL_CODE ,
637 p_CUR_SUBSEQUENT_CODE ,
638 p_PYA_CODE ,
639 p_CREDIT_COHORT_YR ,
640 p_PROGRAM_COST_IND ,
641 p_TREASURY_SYMBOL_ID );
642
643 gbl_exception_occurred := TRUE;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 gbl_error_code := -1;
648 gbl_error_buf := l_module_name||' - When others exception -'||
649 to_char(SQLCODE) || ' - ' || SQLERRM;
650 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
651
652 END populate_temp2;
653 --------------------------------------------------------------------------------
654 -- PROCEDURE GET_USSGL_ACCT_NUM
655 --------------------------------------------------------------------------------
656 -- Process the records to find exceptions and return the cust_non_cust,
657 -- exch_non_exch and no_val_subfunction exceptions individually
658 --------------------------------------------------------------------------------
659 PROCEDURE GET_USSGL_ACCT_NUM (p_acct_num IN Varchar2,
660 p_fund_value IN Varchar2,
661 p_ccid IN number,
662 p_amount IN NUMBER,
663 p_sgl_acct_num OUT NOCOPY Number,
664 p_exch_non_exch OUT NOCOPY Varchar2,
665 p_cust_non_cust OUT NOCOPY Varchar2,
666 p_DIRECT_OR_REIMB_CODE OUT NOCOPY Varchar2,
667 p_APPOR_CAT_CODE OUT NOCOPY Varchar2,
668 p_APPOR_CAT_B_CODE OUT NOCOPY Varchar2,
669 p_PROGRAM_REPT_CODE OUT NOCOPY Varchar2,
670 p_BEA_CAT_CODE OUT NOCOPY Varchar2,
671 p_BORR_SRC_CODE OUT NOCOPY Varchar2,
672 p_NEW_BAL_CODE OUT NOCOPY Varchar2,
673 p_CUR_SUBSEQUENT_CODE OUT NOCOPY Varchar2,
674 p_PYA_CODE OUT NOCOPY Varchar2,
675 p_CREDIT_COHORT_YR OUT NOCOPY Varchar2,
676 p_PROGRAM_COST_IND OUT NOCOPY Varchar2,
677 p_exception_category OUT NOCOPY Varchar2,
678 p_treasury_symbol_id OUT NOCOPY Number,
679 p_end_bal_ind OUT NOCOPY VARCHAR2)
680
681 IS
682 l_module_name VARCHAR2(200):= g_module_name||'GET_USSGL_ACCT_NUM';
683 l_ussgl_acct_num fv_gtas_attributes.USSGL_ACCT_NUMBER%type;
684 l_ussgl_enabled VARCHAR2(1);
685 L_Ussgl_Not_Found VARCHAR2(1);
686
687 --L_Exists VARCHAR2(1);
688 doesGTASAccountExist boolean default false;
689 cnt number;
690 L_Exch_Non_Exch1 Fv_gtas_Attributes.Exch_Non_Exch1%type;
691 L_Exch_Non_Exch2 Fv_gtas_Attributes.Exch_Non_Exch2%type;
692 L_Exch_Non_Exch3 Fv_gtas_Attributes.Exch_Non_Exch3%type;
693 L_Pya_Code1 Fv_gtas_Attributes.Pya_Code1%type;
694
695 L_Cust_Non_Cust1 Fv_gtas_Attributes.Cust_Non_Cust1%type;
696 L_Cust_Non_Cust2 Fv_gtas_Attributes.Cust_Non_Cust2%type;
697
698 L_Apportionment_Category1 Fv_gtas_Attributes.Apportionment_Category1%type;
699 L_Apportionment_Category2 Fv_gtas_Attributes.Apportionment_Category1%type;
700 L_Apportionment_Category3 Fv_gtas_Attributes.Apportionment_Category1%type;
701 L_Bea_Category_Acct_Type Fv_gtas_Attributes.Bea_Category_Acct_Type%type;
702
703 l_Cur_Sub_Code1 Fv_gtas_Attributes.Cur_Sub_Code1%type;
704
705 l_Outlays_Bea_Category_Code fv_fund_parameters.Outlays_Bea_Category_Code%type;
706 l_Bea_Category fv_fund_parameters.Bea_Category%type;
707 l_appor_cat fv_fund_parameters.fund_category%type;
708 l_c_nc fv_fund_parameters.cust_non_cust%type;
709 l_bsc fv_fund_parameters.BORROWING_SOURCE_CODE%type;
710 l_drc fv_fund_parameters.DIRECT_OR_REIMB_CODE%type;
711 l_Financing_Account fv_Treasury_symbols.financing_account%type;
712 l_cohort_seg_name fv_pya_fiscalyear_segment.cohort_segment_name%type;
713 vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
714 select_stmt VARCHAR2(10000);
715 Vl_Cursor Integer;
716 Vl_Prc_Val Varchar2(4);
717 I integer;
718 l_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%type;
719 l_Acct_Num Varchar2(8);
720 l_temp_acct_num fv_gtas_attributes.gtas_acct_number%TYPE;
721 l_dir_or_reimb_flag fv_gtas_attributes.direct_or_reimb_flag%TYPE;
722 l_appor_cat_b_prog_flag fv_gtas_attributes.appor_cat_b_prog_flag%TYPE;
723 l_prog_rep_cat_flag fv_gtas_attributes.prog_rep_cat_flag%TYPE;
724 l_bea_category_flag fv_gtas_attributes.bea_category_flag%TYPE;
725 l_borrowing_source_flag fv_gtas_attributes.borrowing_source_flag%TYPE;
726 l_program_cost_ind_flag fv_gtas_attributes.program_cost_ind_flag%TYPE;
727 l_new_or_bal_flag fv_gtas_attributes.new_or_bal_flag%TYPE;
728
729 --l_prc_map_val VARCHAR2(30);
730 BEGIN
731 log(l_module_name, 'Begin');
732
733 l_ussgl_enabled := NULL;
734 L_Ussgl_Not_Found := NULL;
735 l_Acct_Num := p_Acct_Num;
736 p_sgl_acct_num := NULL;
737 p_exch_non_exch := NULL;
738 p_cust_non_cust := NULL;
739 p_DIRECT_OR_REIMB_CODE :=NULL;
740 p_APPOR_CAT_CODE := NULL;
741 p_APPOR_CAT_B_CODE := NULL;
742 p_PROGRAM_REPT_CODE := NULL;
743 p_BEA_CAT_CODE := NULL;
744 p_BORR_SRC_CODE := NULL;
745 p_NEW_BAL_CODE := NULL;
746 p_CUR_SUBSEQUENT_CODE := NULL;
747 p_PYA_CODE := NULL;
748 p_CREDIT_COHORT_YR := NULL;
749 p_PROGRAM_COST_IND := NULL;
750 p_exception_category := NULL;
751 p_treasury_symbol_id := NULL;
752 p_end_bal_ind := NULL;
753 l_dir_or_reimb_flag := NULL;
754 l_appor_cat_b_prog_flag := NULL;
755 l_prog_rep_cat_flag := NULL;
756 l_bea_category_flag := NULL;
757 l_borrowing_source_flag := NULL;
758 l_program_cost_ind_flag := NULL;
759
760 --Modified for ER14712945
761 Select fp.treasury_symbol_id, fp.Outlays_Bea_Category_Code,
762 fp.Bea_Category, fp.Fund_Category,
763 fp.Cust_Non_Cust, fp.Borrowing_Source_Code,
764 fp.Direct_Or_Reimb_Code, tas.financing_account
765 into l_treasury_symbol_id, l_Outlays_Bea_Category_Code,
766 l_Bea_Category, l_appor_cat,
767 l_c_nc, l_bsc,
768 l_drc, l_financing_account
769 From Fv_Fund_Parameters fp,
770 fv_treasury_symbols tas
771 Where fp.Fund_Value = P_Fund_Value
772 AND fp.set_of_books_id = gbl_set_of_books_id
773 AND fp.treasury_symbol_id = tas.treasury_symbol_id;
774
775 p_treasury_symbol_id := l_treasury_symbol_id;
776
777 -- Validate the Account number and return the corresponding SGL
778 -- number or parent for getting attributes.
779 -- Verify whether the account number exists in FV_GTAS_ATTRIBUTES table
780 -- Validate the USSGL Account Number
781 Gbl_Error_Code := 0;
782 --Check if USSGL account is defined and enabled in FV_FACTS_USSGL_ACCOUNTS
783 GET_USSGL_INFO(p_acct_num, l_ussgl_enabled,l_ussgl_not_found);
784
785 IF L_Ussgl_Enabled Is Not Null and L_Ussgl_Enabled = 'N' THEN
786 p_exception_category:= 'USSGL_DISABLED';
787 --ER:Print the account number
788 log(l_module_name,'Ussgl disabled');
789 print_header;
790 fnd_file.put_line(fnd_file.output, rpad(p_acct_num,24)||rpad(P_Fund_Value,23)) ;
791 del_disabled_accts(p_acct_num);
792
793 Return;
794 End If;
795
796 log(l_module_name, 'l_ussgl_not_found' || l_ussgl_not_found);
797
798 IF gbl_error_code <> 0 THEN
799 RETURN;
800 End If;
801 Gbl_Error_Code := 0;
802
803 -- If USSGL account is not defined, check if it has a parent USSGL account.
804 -- Report exception if account has multiple parents
805 --ER 14750905
806 --If it has multiple parents which are enabled, then error
807 If L_Ussgl_Not_Found = 'Y' Then --1
808 BEGIN
809 SELECT SUBSTR(parent_flex_value,1,6)
810 INTO l_ussgl_acct_num
811 FROM fnd_flex_value_hierarchies
812 WHERE (p_acct_num BETWEEN child_flex_value_low
813 AND child_flex_value_high)
814 AND flex_value_set_id = gbl_acc_value_set_id
815 AND parent_flex_value <> 'T'
816 AND SUBSTR(parent_flex_value,1,6) IN
817 (SELECT ussgl_account
818 From Fv_Facts_Ussgl_Accounts
819 Where Ussgl_Account = SUBSTR(Parent_Flex_Value,1,6))
820 AND parent_flex_value IN
821 (
822 SELECT flex_value
823 FROM fnd_flex_values
824 WHERE flex_value_set_id = gbl_acc_value_set_id
825 AND enabled_flag = 'Y'
826 );
827
828 Exception
829 When No_Data_Found Then
830 L_Ussgl_Acct_Num := Null;
831 WHEN TOO_MANY_ROWS THEN
832 P_eXCEPTION_CATEGORY := 'USSGL_MULTIPLE_PARENTS';
833 --Create exception row
834 populate_temp2(
835 p_account_number => p_Acct_Num,
836 p_amount => p_amount,
837 p_fiscal_year => gbl_period_year,
838 p_record_category => p_exception_category,
839 p_ussgl_account => l_ussgl_acct_num,
840 p_set_of_books_id => gbl_set_of_books_id,
841 p_fund_value => p_fund_value,
842 p_ccid => p_ccid,
843 p_treasury_symbol_id => p_treasury_symbol_id);
844 when others then
845 log(l_module_name, 'Begin 2'||SQLERRM);
846 END;
847
848 If l_ussgl_acct_num Is Not Null Then
849 l_Acct_Num := l_ussgl_acct_num;
850 Get_Ussgl_Info(l_Acct_Num, L_Ussgl_Enabled,L_Ussgl_Not_Found);
851 IF gbl_error_code <> 0 THEN
852 Return;
853 END IF;
854 -- Disabled US SGL Account
855 IF L_Ussgl_Enabled Is Not Null and L_Ussgl_Enabled = 'N' THEN
856 p_exception_category:= 'USSGL_DISABLED';
857 log(l_module_name,'Ussgl disabled');
858 print_header;
859 fnd_file.put_line(fnd_file.output, rpad(p_acct_num,24)||rpad(P_Fund_Value,23)) ;
860 del_disabled_accts(p_acct_num);
861 Return;
862 End If;
863 Gbl_Error_Code := 0;
864 End If;
865 End If;
866
867 Begin
868 Select count(*) into cnt From Fv_Gtas_Attributes Where
869 GTAS_ACCT_NUMBER = l_Acct_Num;
870 doesGTASAccountExist := TRUE;
871 -- Account not reported in GTAS
872 EXCEPTION
873 When others Then
874 doesGTASAccountExist := false;
875 P_Exception_Category := 'GTAS_UNDEFINED';
876 log(l_module_name,'GTAS Undefined');
877 print_header;
878 fnd_file.put_line(fnd_file.output, rpad(p_acct_num,24)||rpad(P_Fund_Value,23)) ;
879 del_disabled_accts(p_acct_num);
880 log(l_module_name, 'Begin 3'||SQLERRM);
881 Return;
882 End;
883 log(l_module_name, 'cnt'||cnt);
884
885
886 -- Neither the Account nor the US SGL Account is defined in the Define GTAS
887 -- Attributes window and is not reported in GTAS.
888 If Cnt = 0 Then
889 P_Exception_Category := 'GTAS_UNDEFINED';
890 print_header;
891 fnd_file.put_line(fnd_file.output, rpad(p_acct_num,24)||rpad(P_Fund_Value,23)) ;
892 log(l_module_name, 'gtas_undefined');
893 del_disabled_accts(p_acct_num);
894 Return;
895 End If;
896
897
898 begin
899 select decode(BALANCE_TYPE, 'BE','S',BALANCE_TYPE )
900 into p_end_bal_ind
901 from FV_GTAS_ATTRIBUTES
902 Where GTAS_ACCT_NUMBER = l_Acct_Num
903 AND set_of_books_id = gbl_set_of_books_id;
904 exception
905 when no_data_found then
906 log(l_module_name, 'Begin 12'||SQLERRM);
907 return;
908 end;
909
910 --Not a Valid US SGL Account
911 If L_Ussgl_Not_Found = 'Y' And l_ussgl_acct_num IS NULL AND Not Doesgtasaccountexist Then
912 P_Exception_Category := 'NON_USSGL_ACCT';
913 print_header;
914 fnd_file.put_line(fnd_file.output, rpad(p_acct_num,24)||rpad(P_Fund_Value,23)) ;
915 log(l_module_name,'Non Ussgl account');
916 del_disabled_accts(p_acct_num);
917 return;
918 END IF;
919
920 -- Multiple US SGL Account Parents Defined
921 If P_Exception_Category IS NOT NULL AND P_Exception_Category = 'USSGL_MULTIPLE_PARENTS'
922 And Not Doesgtasaccountexist Then
923 P_Exception_Category := 'USSGL_MULTIPLE_PARENTS';
924 --Create exception row
925 populate_temp2(
926 p_account_number => p_Acct_Num,
927 p_amount => p_amount,
928 p_fiscal_year => gbl_period_year,
929 p_record_category => p_exception_category,
930 p_ussgl_account => l_ussgl_acct_num,
931 p_set_of_books_id => gbl_set_of_books_id,
932 p_fund_value => p_fund_value,
933 p_ccid => p_ccid,
934 p_treasury_symbol_id => p_treasury_symbol_id);
935 Return;
936 END IF;
937
938
939 --p_sgl_acct_num := l_acct_num;
940 p_sgl_acct_num := l_ussgl_acct_num;
941
942 BEGIN
943 --Modified to use account num to fetch gtas attributes,
944 --if account num is not found in gtas attributes, then
945 --use ussgl account
946 BEGIN
947 SELECT gtas_acct_number
948 INTO l_temp_acct_num
949 FROM fv_gtas_attributes
950 WHERE gtas_acct_number = p_acct_num;
951
952 EXCEPTION WHEN NO_DATA_FOUND THEN
953 log(l_module_name, 'GTAS attributes not found with: '||p_acct_num);
954 log(l_module_name, 'Using: '||p_sgl_acct_num);
955
956 l_temp_acct_num := p_sgl_acct_num;
957 END;
958
959
960 Select Exch_Non_Exch1, Exch_Non_Exch2, Exch_Non_Exch3,
961 Pya_Code1,
962 Cust_Non_Cust1, Cust_Non_Cust2, borrowing_source_flag,
963 direct_or_reimb_flag,
964 Apportionment_Category1, Apportionment_Category2, Apportionment_Category3,
965 appor_cat_b_prog_flag,
966 prog_rep_cat_flag,
967 bea_category_flag,
968 Bea_Category_Acct_Type,
969 program_cost_ind_flag,
970 new_or_bal_flag,
971 Cur_Sub_Code1
972 Into L_Exch_Non_Exch1, L_Exch_Non_Exch2, L_Exch_Non_Exch3,
973 L_Pya_Code1,
974 L_Cust_Non_Cust1, L_Cust_Non_Cust2,
975 l_borrowing_source_flag,
976 l_dir_or_reimb_flag,
977 L_Apportionment_Category1,L_Apportionment_Category2, L_Apportionment_Category3,
978 l_appor_cat_b_prog_flag,
979 l_prog_rep_cat_flag,
980 l_bea_category_flag,
981 L_Bea_Category_Acct_Type,
982 l_program_cost_ind_flag,
983 l_new_or_bal_flag,
984 l_Cur_Sub_Code1
985 From Fv_Gtas_Attributes
986 --WHERE GTAS_ACCT_NUMBER = l_Acct_Num
987 WHERE GTAS_ACCT_NUMBER = l_temp_acct_num
988 AND set_of_books_id = gbl_set_of_books_id;
989 Exception
990 when others then
991 log(l_module_name, 'begin 9'||SQLERRM);
992 return;
993 end;
994
995 log(l_module_name, 'l_Acct_Num'||l_Acct_Num);
996 log(l_module_name, 'l_temp_acct_num'||l_temp_acct_num);
997
998 --Only one Exchange or Non-exchange Code must be defined for GL Account
999 IF (L_Exch_Non_Exch1 is not null) then
1000 p_exch_non_exch := L_Exch_Non_Exch1;
1001 End If;
1002
1003 --Custodial or Noncustodial Code not valid for GL Account
1004 --Bug14298991
1005 IF (L_Cust_Non_Cust1 IS NULL AND
1006 L_Cust_Non_Cust2 IS NULL ) THEN
1007 log(l_module_name,'No gtas cust non cust defined');
1008 ELSE
1009 If Not((L_Cust_Non_Cust1 Is Not Null And L_C_Nc = L_Cust_Non_Cust1) Or
1010 (L_Cust_Non_Cust2 Is Not Null And L_C_Nc = L_Cust_Non_Cust2)) Then
1011 P_Exception_Category := 'CUST_NON_CUST_INVALID';
1012 --Create exception row
1013 populate_temp2(
1014 p_account_number => l_temp_acct_num,
1015 p_amount => p_amount,
1016 p_fiscal_year => gbl_period_year,
1017 p_record_category => p_exception_category,
1018 p_ussgl_account => l_ussgl_acct_num,
1019 p_set_of_books_id => gbl_set_of_books_id,
1020 p_fund_value => p_fund_value,
1021 p_ccid => p_ccid,
1022 p_treasury_symbol_id => p_treasury_symbol_id);
1023 ELSE
1024 p_cust_non_cust := L_C_Nc;
1025 end if;
1026 END IF;
1027
1028 -- Apportionment Category Code not valid for GL Account
1029 --Bug14298991. If gtas attributes contains values then if
1030 --any value is equal to the value derived from fund then pass exception.
1031 --If gtas attributes values do not equal to values derived from fund then
1032 --raise an exception.
1033 --If there are no attributes defined, then do not raise exception
1034 IF (L_Apportionment_Category1 IS NULL AND
1035 L_Apportionment_Category2 IS NULL AND
1036 L_Apportionment_Category3 IS NULL) THEN
1037 log(l_module_name,'No gtas appor cat defined');
1038 ELSE
1039 If Not((L_Apportionment_Category1 Is Not Null And l_appor_cat = L_Apportionment_Category1) Or
1040 (L_Apportionment_Category2 Is Not Null And l_appor_cat = L_Apportionment_Category2)Or
1041 (L_Apportionment_Category3 Is Not Null And L_Appor_Cat = L_Apportionment_Category3)) Then
1042 P_Exception_Category := 'APPOR_CAT_INVALID';
1043 --Create exception row
1044 populate_temp2(
1045 p_account_number => l_temp_acct_num,
1046 p_amount => p_amount,
1047 p_fiscal_year => gbl_period_year,
1048 p_record_category => p_exception_category,
1049 p_ussgl_account => l_ussgl_acct_num,
1050 p_set_of_books_id => gbl_set_of_books_id,
1051 p_fund_value => p_fund_value,
1052 p_ccid => p_ccid,
1053 p_treasury_symbol_id => p_treasury_symbol_id);
1054 ELSE
1055 p_APPOR_CAT_CODE := l_appor_cat;
1056 End If;
1057 END IF;
1058
1059
1060 --Get apportionment category b code if appor cat is B
1061 --Get prog rep cat code if appor cat is A or B
1062 If( l_appor_cat IN ('A','B') And
1063 (L_Apportionment_Category1 IN ('A','B') Or
1064 L_Apportionment_Category2 IN ('A','B') Or
1065 L_Apportionment_Category3 IN ('A','B'))) Then
1066
1067 IF l_appor_cat = 'A' THEN
1068 --IF the PROG_REP_CAT_FLAG is checked, then get
1069 --prog rep category
1070 IF l_prog_rep_cat_flag = 'Y' THEN
1071
1072 Check_Prc_Map_Seg(p_Fund_Value, 'N', Gbl_Set_Of_Books_Id, Vl_Catb_Rc_Map_Status,
1073 Vl_Prn_Rc_Map_Status, Vl_Pci_Rc_Map_Status);
1074
1075 log(l_module_name,'after call to Check_Prc_Map_Seg');
1076 log(l_module_name,' Vl_Prn_Rc_Map_Status:'||Vl_Prn_Rc_Map_Status);
1077
1078 IF (Vl_Prn_Rc_Map_Status = 'FAIL') THEN
1079 P_Exception_Category := 'PRC_MISSING';
1080 --Create exception row
1081 populate_temp2(
1082 p_account_number => l_temp_acct_num,
1083 p_amount => p_amount,
1084 p_fiscal_year => gbl_period_year,
1085 p_record_category => p_exception_category,
1086 p_ussgl_account => l_ussgl_acct_num,
1087 p_set_of_books_id => gbl_set_of_books_id,
1088 p_fund_value => p_fund_value,
1089 p_ccid => p_ccid,
1090 p_treasury_symbol_id => p_treasury_symbol_id);
1091
1092 ELSIF (Vl_Prn_Rc_Map_Status = 'PASS' AND g_prc_prc_flag = 'Y') THEN
1093 Get_Prc_Map_Val(g_PRC_prG_SEG, P_Ccid,
1094 G_Prc_Prg_Header_Id, Vl_Prc_Val);
1095 p_PROGRAM_REPT_CODE := Vl_Prc_Val;
1096 log(l_module_name, 'p_PROGRAM_REPT_CODE'||p_PROGRAM_REPT_CODE);
1097 END IF;
1098 END IF;
1099
1100 ELSIF l_appor_cat = 'B' THEN
1101 --IF the APPOR_CAT_B_PROG_FLAG is checked, then get
1102 --apportionment category b
1103 IF l_appor_cat_b_prog_flag = 'Y' THEN
1104 --Check appor cat b code
1105 Check_Prc_Map_Seg(p_Fund_Value, 'B', Gbl_Set_Of_Books_Id, Vl_Catb_Rc_Map_Status,
1106 Vl_Prn_Rc_Map_Status, Vl_Pci_Rc_Map_Status);
1107
1108 log(l_module_name,'after call to Check_Prc_Map_Seg');
1109 log(l_module_name,' Vl_Catb_Rc_Map_Status:'||Vl_Catb_Rc_Map_Status);
1110
1111 IF (Vl_Catb_Rc_Map_Status = 'FAIL') THEN
1112 P_Exception_Category := 'APPOR_CATB_MISSING';
1113 --Create exception row
1114 populate_temp2(
1115 p_account_number => l_temp_acct_num,
1116 p_amount => p_amount,
1117 p_fiscal_year => gbl_period_year,
1118 p_record_category => p_exception_category,
1119 p_ussgl_account => l_ussgl_acct_num,
1120 p_set_of_books_id => gbl_set_of_books_id,
1121 p_fund_value => p_fund_value,
1122 p_ccid => p_ccid,
1123 p_treasury_symbol_id => p_treasury_symbol_id);
1124
1125 ELSIF (Vl_Catb_Rc_Map_Status = 'PASS' AND G_Catb_Prc_Flag='Y' ) THEN
1126 Get_Prc_Map_Val(g_catb_prG_SEG, P_Ccid,
1127 G_Catb_Prg_Header_Id, Vl_Prc_Val);
1128 p_APPOR_CAT_B_CODE := Vl_Prc_Val;
1129 log(l_module_name, 'p_APPOR_CAT_B_CODE'||p_APPOR_CAT_B_CODE);
1130 END IF;
1131 END IF;
1132
1133 --IF the PROG_REP_CAT_FLAG is checked, then get
1134 --prog rep category
1135 IF l_prog_rep_cat_flag = 'Y' THEN
1136 --Check prog rep cat code
1137 Check_Prc_Map_Seg(p_Fund_Value, 'N', Gbl_Set_Of_Books_Id, Vl_Catb_Rc_Map_Status,
1138 Vl_Prn_Rc_Map_Status, Vl_Pci_Rc_Map_Status);
1139
1140 log(l_module_name,'after call to Check_Prc_Map_Seg');
1141 log(l_module_name,' Vl_Prn_Rc_Map_Status:'||Vl_Prn_Rc_Map_Status);
1142
1143 IF (Vl_Prn_Rc_Map_Status = 'FAIL') THEN
1144 P_Exception_Category := 'PRC_MISSING';
1145 --Create exception row
1146 populate_temp2(
1147 p_account_number => l_temp_acct_num,
1148 p_amount => p_amount,
1149 p_fiscal_year => gbl_period_year,
1150 p_record_category => p_exception_category,
1151 p_ussgl_account => l_ussgl_acct_num,
1152 p_set_of_books_id => gbl_set_of_books_id,
1153 p_fund_value => p_fund_value,
1154 p_ccid => p_ccid,
1155 p_treasury_symbol_id => p_treasury_symbol_id);
1156
1157 ELSIF (Vl_Prn_Rc_Map_Status = 'PASS' AND g_prc_prc_flag = 'Y') THEN
1158 Get_Prc_Map_Val(g_PRC_prG_SEG, P_Ccid,
1159 G_Prc_Prg_Header_Id, Vl_Prc_Val);
1160 p_PROGRAM_REPT_CODE := Vl_Prc_Val;
1161 log(l_module_name, 'p_PROGRAM_REPT_CODE'||p_PROGRAM_REPT_CODE);
1162 END IF;
1163
1164 END IF;
1165
1166 END IF;
1167
1168 END IF;
1169
1170 Vl_Prc_Val:= null;
1171
1172 --Modified for CR 27
1173 IF l_program_cost_ind_flag = 'Y' THEN
1174 --Check prog cost ind code
1175 Check_Prc_Map_Seg(p_Fund_Value, 'P', Gbl_Set_Of_Books_Id, Vl_Catb_Rc_Map_Status,
1176 Vl_Prn_Rc_Map_Status, Vl_Pci_Rc_Map_Status);
1177
1178 log(l_module_name,'after call to Check_Prc_Map_Seg');
1179 log(l_module_name,' Vl_Pci_Rc_Map_Status:'||Vl_Pci_Rc_Map_Status);
1180
1181 IF (Vl_Pci_Rc_Map_Status = 'FAIL') THEN
1182 P_Exception_Category := 'PCI_INVALID';
1183 --Create exception row
1184 populate_temp2(
1185 p_account_number => l_temp_acct_num,
1186 p_amount => p_amount,
1187 p_fiscal_year => gbl_period_year,
1188 p_record_category => p_exception_category,
1189 p_ussgl_account => l_ussgl_acct_num,
1190 p_set_of_books_id => gbl_set_of_books_id,
1191 p_fund_value => p_fund_value,
1192 p_ccid => p_ccid,
1193 p_treasury_symbol_id => p_treasury_symbol_id);
1194
1195 ELSIF (Vl_Pci_Rc_Map_Status = 'PASS' AND G_Pci_Prc_Flag = 'Y') THEN
1196 Get_Prc_Map_Val(g_pci_prG_SEG, P_Ccid,
1197 G_Pci_Prg_Header_Id , Vl_Prc_Val);
1198
1199 p_PROGRAM_COST_IND:= Vl_Prc_Val;
1200 log(l_module_name, 'p_PROGRAM_COST_IND'||p_PROGRAM_COST_IND);
1201 END IF;
1202 --Return;
1203 END IF;
1204
1205 IF l_bea_category_flag = 'Y' THEN
1206 IF L_Bea_Category_Acct_Type = 'B' THEN
1207 p_BEA_CAT_CODE := L_Bea_Category;
1208 ELSIF L_Bea_Category_Acct_Type = 'O' THEN
1209 p_BEA_CAT_CODE := l_Outlays_Bea_Category_Code;
1210 END IF;
1211 END IF;
1212
1213 --IF Borrowing Source has NOT been defined in gtas attributes
1214 --no need to fetch borrowing source, if it has been defined
1215 --then fetch the borr source
1216 IF l_borrowing_source_flag = 'Y' THEN
1217 p_BORR_SRC_CODE := l_bsc;
1218 END IF;
1219
1220 --Only one Prior Year Adjustment Code must be defined for GL Account
1221 IF L_Pya_Code1 IS NOT NULL THEN
1222 p_PYA_CODE := L_Pya_Code1;
1223 END IF;
1224
1225 --Direct or Reimbursable Code not valid for GL Account
1226 log(l_module_name, 'L_Drc: '||L_Drc);
1227 IF l_dir_or_reimb_flag = 'Y' THEN
1228 p_DIRECT_OR_REIMB_CODE := l_drc;
1229 END IF;
1230
1231 -- Get Credit Cohort Year
1232 --Modified for bug14624921. CCY must be fetched only for
1233 --budgetary accounts
1234 IF get_account_type(l_Acct_Num) IN ('D', 'C') THEN
1235 log(l_module_name, 'Acct is a Budgetary acct, getting CCY');
1236 begin
1237
1238 If l_Financing_Account ='D' Or l_Financing_Account = 'G' Then
1239 SELECT cohort_segment_name
1240 INTO l_cohort_seg_name
1241 FROM fv_pya_fiscalyear_segment
1242 Where Set_Of_Books_Id = Gbl_Set_Of_Books_Id;
1243
1244 Select_Stmt := 'SELECT ' || l_cohort_seg_name ||
1245 ' from gl_code_combinations where code_combination_id=' || p_ccid;
1246 Vl_Cursor := Dbms_Sql.Open_Cursor ;
1247 Dbms_Sql.Parse(vl_cursor,Select_Stmt, Dbms_Sql.V7);
1248 Dbms_Sql.Define_Column(vl_cursor,1,'VARCHAR2',150);
1249 I:= Dbms_Sql.Execute(vl_cursor);
1250 I := Dbms_Sql.Fetch_Rows(vl_cursor) ;
1251 Dbms_Sql.Column_Value(Vl_Cursor, 1, p_CREDIT_COHORT_YR);
1252 dbms_sql.close_cursor(Vl_Cursor);
1253 end if;
1254 Exception
1255 When Others Then
1256 Null;
1257 End;
1258 END IF;
1259
1260 -- Get New Bal Value
1261 If (l_new_or_bal_flag = 'Y' AND
1262 l_financing_account NOT IN ('D','G')) Then
1263 BEGIN
1264 SELECT fyr_segment_value
1265 INTO vl_fyr_segment_value
1266 FROM fv_pya_fiscalyear_map
1267 Where Period_Year = gbl_period_year
1268 AND set_of_books_id = gbl_set_of_books_id;
1269 EXCEPTION
1270 WHEN NO_DATA_FOUND THEN
1271 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1272 'Please set up the Budget Fiscal Year Segment Mapping for period year '
1273 ||gbl_period_year);
1274 RAISE;
1275 END;
1276
1277
1278 If Vl_Fyr_Segment_Value Is Not Null Then
1279 If Vl_Fyr_Segment_Value < gbl_period_year Then
1280 p_NEW_BAL_CODE := 'BAL';
1281 Else
1282 p_NEW_BAL_CODE := 'NEW';
1283 End If;
1284 End If;
1285 End If;
1286
1287 --Cur or Subsequent Code not valid for GL Account
1288 log(l_module_name, 'L_Cur_Sub_Code1: '||L_Cur_Sub_Code1);
1289 IF L_Cur_Sub_Code1 IS NOT NULL THEN
1290 p_CUR_SUBSEQUENT_CODE := L_Cur_Sub_Code1;
1291 END IF;
1292
1293 EXCEPTION
1294 WHEN NO_DATA_FOUND THEN
1295 p_sgl_acct_num := p_acct_num;
1296 p_exception_category:= 'PROP_ACCT_NOT_SETUP';
1297 --Create exception row
1298 populate_temp2(
1299 p_account_number => p_Acct_Num,
1300 p_amount => p_amount,
1301 p_fiscal_year => gbl_period_year,
1302 p_record_category => p_exception_category,
1303 p_ussgl_account => l_ussgl_acct_num,
1304 p_set_of_books_id => gbl_set_of_books_id,
1305 p_fund_value => p_fund_value,
1306 p_ccid => p_ccid,
1307 p_treasury_symbol_id => p_treasury_symbol_id);
1308 gbl_error_code := -1;
1309 gbl_error_buf := l_module_name||' - When no data found error: '||SQLERRM;
1310 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1311 return;
1312
1313 WHEN OTHERS THEN
1314 gbl_error_code := -1;
1315 gbl_error_buf := l_module_name||' - When others error: '||SQLERRM;
1316 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1317 RETURN;
1318 END GET_USSGL_ACCT_NUM ;
1319 --------------------------------------------------------------------------------
1320 PROCEDURE CHECK_PRC_MAP_SEG(p_fund_value IN VARCHAR2,
1321 p_code_type IN VARCHAR2,
1322 p_sob_id IN NUMBER,
1323 p_catb_status OUT NOCOPY VARCHAR2,
1324 p_prn_status OUT NOCOPY VARCHAR2,
1325 p_pci_status OUT NOCOPY VARCHAR2
1326 --, vl_prc_val OUT NOCOPY VARCHAR2
1327 )
1328 IS
1329
1330 l_module_name VARCHAR2(200):= g_module_name||'check_prc_map_seg';
1331
1332 CURSOR fund_cur(cv_fund_value IN VARCHAR2,
1333 cv_sob_id IN NUMBER) IS
1334 SELECT fund_value,
1335 fund_category,
1336 treasury_symbol_id
1337 From Fv_Fund_Parameters
1338 WHERE fund_value = p_fund_value
1339 AND set_of_books_id = p_sob_id;
1340
1341 --vl_ts_id NUMBER;
1342 vl_fund_value fv_fund_parameters.fund_value%TYPE;
1343 vl_treasury_symbol_id NUMBER;
1344 vl_fund_category VARCHAR2(1);
1345 vl_prg_seg fv_facts_prc_hdr.program_segment%TYPE;
1346 Vl_Prc_Header_Id Number;
1347 vl_prc_flag fv_facts_prc_hdr.prc_mapping_flag%TYPE;
1348 vl_code_type fv_facts_prc_hdr.code_type%TYPE;
1349 Vl_Status Varchar2(15);
1350 --Vl_Prc_Found varchar2(1);
1351
1352 BEGIN
1353
1354 log(l_module_name, 'Begin:');
1355 log(l_module_name,'p_fund_value: '||p_fund_value);
1356 log(l_module_name,'p_code_type: '||p_code_type);
1357
1358 p_catb_status:= 'FAIL';
1359 p_prn_status := 'FAIL';
1360 p_pci_status := 'FAIL';
1361 vl_code_type := p_code_type;
1362
1363
1364 OPEN fund_cur(p_fund_value, p_sob_id);
1365 FETCH fund_cur INTO vl_fund_value,
1366 vl_fund_category,
1367 vl_treasury_symbol_id;
1368 CLOSE fund_cur;
1369
1370 log(l_module_name,'vl_fund_category: '||vl_fund_category);
1371 log(l_module_name,'vl_treasury_symbol_id: '||vl_treasury_symbol_id);
1372
1373 vl_status := '';
1374 vl_prg_seg := NULL;
1375 vl_prc_flag := NULL;
1376 vl_prc_header_id := NULL;
1377
1378 Loop
1379
1380 BEGIN
1381 SELECT program_segment,
1382 prc_mapping_flag, prc_header_id
1383 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1384 From Fv_Facts_Prc_Hdr Ffh
1385 WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
1386 AND ffh.code_type = vl_code_type
1387 AND ffh.set_of_books_id = p_sob_id
1388 AND ffh.fund_value = p_fund_value;
1389 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1390 END;
1391
1392 IF vl_prg_seg IS NOT NULL THEN
1393 Vl_Status := 'PASS';
1394 EXIT;
1395 END IF;
1396
1397 IF vl_fund_category = 'A' THEN
1398 BEGIN
1399 SELECT program_segment,
1400 prc_mapping_flag, prc_header_id
1401 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1402 FROM fv_facts_prc_hdr ffh
1403 WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
1404 AND ffh.code_type = vl_code_type
1405 And Ffh.Set_Of_Books_Id = P_Sob_Id
1406 AND ffh.fund_value = 'ALL-A';
1407 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1408 END;
1409
1410 IF vl_prg_seg IS NOT NULL THEN
1411 Vl_Status := 'PASS';
1412 EXIT;
1413 END IF;
1414 End If;
1415
1416 if vl_fund_category = 'B' then
1417 BEGIN
1418 SELECT program_segment,
1419 prc_mapping_flag, prc_header_id
1420 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1421 FROM fv_facts_prc_hdr ffh
1422 WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
1423 AND ffh.code_type = vl_code_type
1424 And Ffh.Set_Of_Books_Id = P_Sob_Id
1425 AND ffh.fund_value = 'ALL-B';
1426 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1427 End;
1428 end if;
1429
1430 IF vl_prg_seg IS NOT NULL THEN
1431 Vl_Status := 'PASS';
1432 EXIT;
1433 END IF;
1434
1435 BEGIN
1436 SELECT program_segment,
1437 prc_mapping_flag, prc_header_id
1438 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1439 FROM fv_facts_prc_hdr ffh
1440 WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
1441 AND ffh.code_type = vl_code_type
1442 AND ffh.set_of_books_id = p_sob_id
1443 AND ffh.fund_value = 'ALL-FUNDS';
1444 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1445 END;
1446
1447 IF vl_prg_seg IS NOT NULL THEN
1448 vl_status := 'PASS';
1449 EXIT;
1450 END IF;
1451
1452 IF vl_fund_category = 'A' then
1453 BEGIN
1454 SELECT program_segment,
1455 prc_mapping_flag, prc_header_id
1456 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1457 FROM fv_facts_prc_hdr ffh
1458 WHERE ffh.treasury_symbol_id = -1
1459 AND ffh.code_type = vl_code_type
1460 And Ffh.Set_Of_Books_Id = P_Sob_Id
1461 AND ffh.fund_value = 'ALL-A';
1462 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1463 END;
1464
1465 IF vl_prg_seg IS NOT NULL THEN
1466 vl_status := 'PASS';
1467 EXIT;
1468 End If;
1469 End If;
1470
1471 IF vl_fund_category = 'B' then
1472 BEGIN
1473 SELECT program_segment,
1474 prc_mapping_flag, prc_header_id
1475 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1476 FROM fv_facts_prc_hdr ffh
1477 WHERE ffh.treasury_symbol_id = -1
1478 AND ffh.code_type = vl_code_type
1479 And Ffh.Set_Of_Books_Id = P_Sob_Id
1480 AND ffh.fund_value = 'ALL-B';
1481
1482 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1483 END;
1484
1485 IF vl_prg_seg IS NOT NULL THEN
1486 vl_status := 'PASS';
1487 Exit;
1488 End If;
1489 End If;
1490
1491 BEGIN
1492 SELECT program_segment,
1493 prc_mapping_flag, prc_header_id
1494 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
1495 FROM fv_facts_prc_hdr ffh
1496 WHERE ffh.treasury_symbol_id = -1
1497 AND ffh.set_of_books_id = p_sob_id
1498 AND ffh.code_type = vl_code_type
1499 AND ffh.fund_value = 'ALL-FUNDS';
1500 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1501 END;
1502 IF vl_prg_seg IS NOT NULL THEN
1503 Vl_Status := 'PASS';
1504 EXIT;
1505 END IF;
1506
1507 vl_status := 'FAIL';
1508
1509 IF vl_code_type = 'B' AND vl_fund_category = 'B' THEN
1510 p_catb_status := 'FAIL';
1511 log(l_module_name, 'vl_code_typ is B '||p_catb_status);
1512 EXIT;
1513 ELSIF vl_code_type = 'N' THEN
1514 p_prn_status := 'FAIL';
1515 log(l_module_name, 'vl_code_typ is N '||p_prn_status);
1516 EXIT;
1517 ELSIF vl_code_type = 'P' THEN
1518 p_pci_status := 'FAIL';
1519 log(l_module_name, 'vl_code_typ is P '||p_pci_status);
1520 EXIT;
1521 End If;
1522 exit;
1523 end loop;
1524
1525 log(l_module_name, 'vl_status: '||vl_status);
1526 log(l_module_name, 'vl_prc_flag: '||vl_prc_flag);
1527
1528 IF vl_status = 'PASS' THEN
1529 IF vl_prc_flag = 'Y' THEN
1530 --Fetch header_ids
1531 If vl_code_type = 'B' Then
1532 G_Catb_Prg_header_id := vl_prc_header_id;
1533 g_catb_prc_flag := vl_prc_flag;
1534 g_catb_prG_SEG := vl_prg_seg;
1535 p_catb_status :='PASS';
1536 Elsif vl_code_type = 'N' Then
1537 G_Prc_Prg_Header_Id := Vl_Prc_Header_Id;
1538 g_prc_prc_flag := vl_prC_flag;
1539 g_PRC_prG_SEG := vl_prg_seg;
1540 p_prn_status := 'PASS';
1541 Else
1542 G_Pci_Prg_Header_Id := Vl_Prc_Header_Id;
1543 g_pci_prc_flag := vl_prc_flag;
1544 g_pci_prG_SEG := vl_prg_seg;
1545 p_pci_status := 'PASS';
1546 end if;
1547 log(l_module_name, 'vl_prc_header_id: '||vl_prc_header_id);
1548 log(l_module_name, 'vl_prg_seg: '||vl_prg_seg);
1549
1550 End If;
1551 END IF;
1552
1553 log(l_module_name, 'End');
1554
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 gbl_error_buf := SQLERRM;
1558 gbl_error_code := -1;
1559 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1560 l_module_name||'.final_exception',gbl_error_buf);
1561
1562 END check_prc_map_seg;
1563 --------------------------------------------------------------------------------
1564 Procedure GET_PRC_MAP_VAL(P_Segment_Name IN Varchar2,
1565 P_Ccid IN Number,
1566 P_Prc_Header_Id IN Number,
1567 p_prc_val OUT NOCOPY VARCHAR2)
1568
1569 Is
1570 Select_Stmt varchar2(100);
1571
1572 k varchar2(150);
1573 l_module_name VARCHAR2(200) := g_module_name||'Get_Prc_Map_Val';
1574 l_prg_val FV_FACTS_PRC_DTL.PROGRAM_VALUE%TYPE;
1575 Begin
1576
1577 Select_Stmt := 'SELECT ' || p_Segment_Name ||
1578 ' from gl_code_combinations where code_combination_id=' || p_Ccid;
1579 log(l_module_name, 'Select_Stmt:'||Select_Stmt);
1580
1581 EXECUTE IMMEDIATE Select_Stmt INTO l_prg_val;
1582 log(l_module_name, 'l_prg_val:'||l_prg_val);
1583
1584 -- IF l_prg_val IS NOT NULL THEN
1585 begin
1586 SELECT reporting_code
1587 INTO p_prc_val
1588 From Fv_Facts_Prc_Dtl
1589 WHERE prc_header_id = P_Prc_Header_Id
1590 And Program_Value = l_prg_val
1591 And Set_Of_Books_Id = Gbl_Set_Of_Books_Id;
1592
1593 Exception
1594 When No_Data_Found Then
1595 log(l_module_name, 'INSIDE 1');
1596
1597 begin
1598 SELECT reporting_code
1599 INTO p_prc_val
1600 FROM fv_facts_prc_dtl
1601 WHERE prc_header_id = P_Prc_Header_Id
1602 And Program_Value = 'ALL'
1603 And Set_Of_Books_Id = Gbl_Set_Of_Books_Id;
1604 exception
1605 When Others Then
1606 log(l_module_name, 'INSIDE 2');
1607 Null;
1608 end;
1609 End;
1610
1611 log(l_module_name, 'p_prc_val'||p_prc_val);
1612 exception
1613 When Others Then
1614 Null;
1615 end Get_Prc_Map_Val;
1616 --------------------------------------------------------------------------------
1617 -- FUNCTION GET_ACCOUNT_TYPE
1618 --------------------------------------------------------------------------------
1619 FUNCTION GET_ACCOUNT_TYPE(p_account_number VARCHAR2) RETURN VARCHAR2
1620 IS
1621
1622 l_module_name VARCHAR2(200) := g_module_name||'GET_ACCOUNT_TYPE';
1623 l_account_type varchar2(1);
1624
1625 BEGIN
1626
1627 log(l_module_name, 'Begin');
1628
1629 SELECT SUBSTR(compiled_value_attributes, 5, 1)
1630 INTO l_account_type
1631 FROM fnd_flex_values
1632 WHERE flex_value = p_account_number
1633 AND flex_value_set_id = gbl_acc_value_set_id;
1634
1635 log(l_module_name, 'End');
1636 RETURN (l_account_type);
1637
1638 EXCEPTION
1639 WHEN Others THEN
1640 gbl_error_code := -1 ;
1641 gbl_error_buf := l_module_name||' - When others exception - ' ||
1642 TO_CHAR(SQLCODE) || ' - ' ||SQLERRM ;
1643 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1644 END get_account_type;
1645 --------------------------------------------------------------------------------
1646 -- PROCEDURE GET_USSGL_INFO
1647 --------------------------------------------------------------------------------
1648 -- Gets the information like enabled flag and reporting type
1649 -- for the passed account number.
1650 --------------------------------------------------------------------------------
1651 PROCEDURE GET_USSGL_INFO (p_ussgl_acct_num IN Varchar2,
1652 P_Enabled_Flag In Out Nocopy Varchar2,
1653 p_acct_not_found OUT NOCOPY VARCHAR2)
1654 IS
1655 l_module_name VARCHAR2(200);
1656 BEGIN
1657 l_module_name := g_module_name || 'GET_USSGL_INFO';
1658 p_acct_not_found := 'N';
1659 log(l_module_name, 'Inside get ussgl info p_ussgl_acct_num'|| p_ussgl_acct_num);
1660
1661 SELECT ussgl_enabled_flag
1662 INTO p_enabled_flag
1663 FROM fv_facts_ussgl_accounts
1664 Where Ussgl_Account = P_Ussgl_Acct_Num;
1665 EXCEPTION
1666 When No_Data_Found Then
1667 p_acct_not_found := 'Y';
1668 WHEN OTHERS THEN
1669 p_acct_not_found := 'Y';
1670 gbl_error_code := -1;
1671 gbl_error_buf := SQLERRM;
1672 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
1673 RETURN;
1674 END get_ussgl_info;
1675
1676 --------------------------------------------------------------------------------
1677 -- PROCEDURE CREATE_END_BAL_RECORD
1678 --------------------------------------------------------------------------------
1679 PROCEDURE CREATE_END_BAL_RECORD
1680 IS
1681
1682 l_module_name VARCHAR2(200) := g_module_name || 'CREATE_END_BAL_RECORD';
1683
1684 BEGIN
1685
1686 log(l_module_name, 'Begin');
1687 --Using the view to insert ending balances
1688 --'P' is prior year end balance
1689 --'L' is current year activity
1690 --Both together are curr year end bal
1691 Insert Into Fv_Gtas_Ending_Balances
1692 (Ccid,
1693 Set_Of_Books_Id,
1694 Fund_Value,
1695 Account_Number,
1696 Authority_Type_Code,
1697 Fed_Non_Fed,
1698 Trading_Partner_Agency_Id,
1699 Trading_Partner_Main_Account,
1700 Budget_Impact_Ind,
1701 Fiscal_Year,
1702 D_C_INDICATOR,
1703 AMOUNT,
1704 end_bal_ind)
1705 (SELECT /*+ PARALLEL(T2) */
1706 T2.Ccid,
1707 Gbl_Set_Of_Books_Id,
1708 t2.fund_value,
1709 T2.Account_Number,
1710 T2.Authority_Type_Code,
1711 T2.Fed_Non_Fed,
1712 T2.Trading_Partner_Agency_Id,
1713 T2.Trading_Partner_Main_Account,
1714 t2.Budget_Impact_Ind,
1715 Gbl_Fiscal_Year,
1716 T2.D_C_Indicator,
1717 Sum(Nvl(Amount,0)),
1718 'Y'
1719 FROM fv_gtas1_period_balances_v t2
1720 WHERE t2.set_of_books_id = gbl_set_of_books_id
1721 And Period_Year = Gbl_Fiscal_Year
1722 and period_num <= gbl_period_num_high
1723 AND t2.begin_end = 'E'
1724 AND t2.balance_type IN ('P','L')
1725 AND nvl(t2.amount,0) <> 0
1726 group by T2.Ccid, Gbl_Set_Of_Books_Id, t2.fund_value, T2.Account_Number,
1727 T2.Authority_Type_Code, T2.Fed_Non_Fed, T2.Trading_Partner_Agency_Id,
1728 T2.Trading_Partner_Main_Account, t2.Budget_Impact_Ind,
1729 Gbl_Fiscal_Year, T2.D_C_Indicator
1730 HAVING SUM(NVL(amount,0)) <> 0) ;
1731
1732 log(l_module_name, 'Inserted '||SQL%ROWCOUNT ||' recs into fv_gtas_ending_bal');
1733
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 gbl_error_code := -1;
1737 gbl_error_buf := l_module_name||' - When others error '||SQLERRM;
1738 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, gbl_error_buf);
1739 RAISE;
1740
1741 END create_end_bal_record;
1742 --------------------------------------------------------------------------------
1743 -- PROCEDURE CLEANUP_PROCESS
1744 --------------------------------------------------------------------------------
1745 PROCEDURE CLEANUP_PROCESS IS
1746
1747 l_module_name VARCHAR2(200) := g_module_name || 'CLEANUP_PROCESS';
1748
1749 BEGIN
1750
1751 log(l_module_name, 'Begin');
1752
1753
1754 DELETE FROM fv_gtas1_report_t2
1755 WHERE set_of_books_id = gbl_set_of_books_id;
1756
1757 /** cleanup the line balance differrence records */
1758 FV_UTILITY.LOG_MESG('Deleting from fv_gtas_diff_balances for Period Year: '||
1759 gbl_period_year);
1760
1761 DELETE FROM fv_gtas_diff_balances
1762 WHERE set_of_books_id = gbl_set_of_books_id
1763 and period_year = gbl_period_year
1764 and balance_type IN ('B', 'D');
1765 --and balance_type = 'D';
1766
1767 log(l_module_name, 'Deleted '||SQL%ROWCOUNT||
1768 ' records from fv_gtas_diff_balances.');
1769
1770 --Set the reported_group to Null so that fv_gtas1_period_balances_v
1771 --picks up only the rows processed in the current run
1772 log(l_module_name, 'Setting reported_group to NULL');
1773 UPDATE fv_gtas1_period_attributes
1774 SET reported_group = NULL
1775 WHERE set_of_books_id = gbl_set_of_books_id
1776 AND period_year = gbl_period_year;
1777
1778 log(l_module_name, 'End');
1779
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 gbl_error_code := -1 ;
1783 gbl_error_buf := l_module_name||' - When others exception - '||SQLERRM;
1784 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,gbl_error_buf);
1785 END;
1786 --------------------------------------------------------------------------------
1787 PROCEDURE SET_UP_GTAS_ATTRIBUTES(p_err_buf OUT NOCOPY VARCHAR2,
1788 p_err_code OUT NOCOPY NUMBER,
1789 p_set_of_books_id IN NUMBER,
1790 p_period_year IN NUMBER)
1791 IS
1792
1793 l_module_name VARCHAR2(200) := g_module_name||'SET_UP_GTAS_ATTRIBUTES';
1794 l_acct_type_condition VARCHAR2(2500);
1795
1796 l_bal_segment VARCHAR2(30);
1797 l_bal_segment_prv VARCHAR2(30);
1798 l_period_begin_bal fv_gtas1_report_t2.amount%TYPE;
1799 l_period_cy_bal fv_gtas1_report_t2.amount%TYPE;
1800 l_period_cy_cr_bal fv_gtas1_report_t2.amount%TYPE;
1801 l_begin_bal fv_gtas1_report_t2.amount%TYPE;
1802 l_curr_year_balance fv_gtas1_report_t2.amount%TYPE;
1803 l_t2_deail_amount fv_gtas1_report_t2.amount%TYPE;
1804 l_ending_amount fv_gtas1_report_t2.amount%TYPE;
1805
1806 l_exists VARCHAR2(1);
1807 l_stage VARCHAR2(25);
1808 --l_fg_null VARCHAR2(1);
1809
1810 TYPE t_ref_cur IS REF CURSOR ;
1811 t1_record_c t_ref_cur ;
1812
1813 TYPE l_account_number_t is table of VARCHAR2(30);
1814 TYPE l_end_bal_ind_t is table of VARCHAR2(1);
1815 TYPE l_fund_value_t is table of VARCHAR2(30);
1816 TYPE l_sgl_acct_num_t is table of VARCHAR2(6);
1817 TYPE l_exch_non_exch_t is table of VARCHAR2(1);
1818 TYPE l_cust_non_cust_t is table of VARCHAR2(1);
1819 TYPE l_exception_status_t is table of VARCHAR2(1);
1820 TYPE l_exception_category_t is table of VARCHAR2(25);
1821 TYPE l_account_type_t is table of VARCHAR2(1);
1822 TYPE l_balance_amoun_t is table of number;
1823 TYPE l_ccid_t is table of number(15);
1824 TYPE l_rowid_t is table of ROWID;
1825 TYPE l_DIRECT_OR_REIMB_CODE_t is table of varchar2(1);
1826 --fv_fund_parameters.DIRECT_OR_REIMB_CODE%type;
1827 TYPE l_APPOR_CAT_CODE_t is table of varchar2(1);
1828 --fv_fund_parameters.fund_category%type;
1829 TYPE l_APPOR_CAT_B_CODE_t is table of varchar2(4);
1830 --fv_facts_prc_Dtl.reporting_code%type;
1831 TYPE l_PROGRAM_REPT_CODE_t is table of varchar2(4);
1832 --fv_facts_prc_Dtl.reporting_code%type;
1833 TYPE l_BEA_CAT_CODE_t is table of varchar2(1);
1834 --fv_fund_parameters.BEA_CATEGORY%type;
1835 TYPE l_BORR_SRC_CODE_t is table of varchar2(1);
1836 --fv_fund_parameters.BORROWING_SOURCE_CODE%type;
1837 TYPE l_NEW_BAL_CODE_t is table of VARCHAR2(3) ;
1838 TYPE l_CUR_SUBSEQUENT_CODE_t is table of VARCHAR2(4) ;
1839 TYPE l_PYA_CODE_t is table of VARCHAR2(4) ;
1840 TYPE l_CREDIT_COHORT_YR_t is table of VARCHAR2(150);
1841 TYPE l_PROGRAM_COST_IND_t is table of VARCHAR2(4) ;
1842 --fv_facts_prc_Dtl.reporting_code%type;
1843 TYPE l_TREASURY_SYMBOL_ID_t is table of number(15);
1844 --fv_treasury_symbols.treasury_symbol_id%type;
1845
1846 -- individual entries
1847 l_account_number_L l_account_number_t ;
1848 l_end_bal_ind_l l_end_bal_ind_t;
1849 l_fund_value_l l_fund_value_t;
1850 l_sgl_acct_num_l l_sgl_acct_num_t;
1851 l_exch_non_exch_l l_exch_non_exch_t;
1852 l_cust_non_cust_l l_cust_non_cust_t;
1853 l_exception_status_l l_exception_status_t;
1854 --l_exception_category_l l_exception_category_t;
1855 l_account_type_l l_account_type_t;
1856 l_new_record_l l_account_type_t;
1857 l_balance_amoun_l l_balance_amoun_t;
1858 l_begin_bal_l l_balance_amoun_t;
1859 l_per_begin_bal_l l_balance_amoun_t;
1860 l_cy_dr_bal_l l_balance_amoun_t;
1861 l_cy_cr_bal_l l_balance_amoun_t;
1862 l_ccid_l l_ccid_t;
1863 --l_rowid_l l_rowid_t;
1864 l_DIRECT_OR_REIMB_CODE_l l_DIRECT_OR_REIMB_CODE_t;
1865 l_APPOR_CAT_CODE_l l_APPOR_CAT_CODE_t;
1866 l_APPOR_CAT_B_CODE_l l_APPOR_CAT_B_CODE_t ;
1867 l_PROGRAM_REPT_CODE_l l_PROGRAM_REPT_CODE_t ;
1868 l_BEA_CAT_CODE_l l_BEA_CAT_CODE_t ;
1869 l_BORR_SRC_CODE_l l_BORR_SRC_CODE_t ;
1870 l_NEW_BAL_CODE_l l_NEW_BAL_CODE_t ;
1871 l_CUR_SUBSEQUENT_CODE_l l_CUR_SUBSEQUENT_CODE_t;
1872 l_PYA_CODE_l l_PYA_CODE_t ;
1873 l_CREDIT_COHORT_YR_l l_CREDIT_COHORT_YR_t;
1874 l_PROGRAM_COST_IND_l l_PROGRAM_COST_IND_t;
1875 l_tREASURY_SYMBOL_ID_l l_tREASURY_SYMBOL_ID_t ;
1876 l_account_number_n l_account_number_t ;
1877 l_fund_value_n l_fund_value_t;
1878 l_end_bal_ind_n l_end_bal_ind_t;
1879 l_sgl_acct_num_n l_sgl_acct_num_t;
1880 l_exch_non_exch_n l_exch_non_exch_t;
1881 l_cust_non_cust_n l_cust_non_cust_t;
1882 l_exception_status_n l_exception_status_t;
1883 l_exception_category_n l_exception_category_t;
1884 l_account_type_n l_account_type_t;
1885 --l_new_record_n l_account_type_t;
1886 l_balance_amoun_n l_balance_amoun_t;
1887 l_begin_bal_n l_balance_amoun_t;
1888 l_per_begin_bal_n l_balance_amoun_t;
1889 l_cy_dr_bal_n l_balance_amoun_t;
1890 l_cy_cr_bal_n l_balance_amoun_t;
1891 l_ccid_n l_ccid_t;
1892 l_DIRECT_OR_REIMB_CODE_n l_DIRECT_OR_REIMB_CODE_t;
1893 l_APPOR_CAT_CODE_n l_APPOR_CAT_CODE_t;
1894 l_APPOR_CAT_B_CODE_n l_APPOR_CAT_B_CODE_t ;
1895 l_PROGRAM_REPT_CODE_n l_PROGRAM_REPT_CODE_t ;
1896 l_BEA_CAT_CODE_n l_BEA_CAT_CODE_t ;
1897 l_BORR_SRC_CODE_n l_BORR_SRC_CODE_t ;
1898 l_NEW_BAL_CODE_n l_NEW_BAL_CODE_t ;
1899 l_CUR_SUBSEQUENT_CODE_n l_CUR_SUBSEQUENT_CODE_t;
1900 l_PYA_CODE_n l_PYA_CODE_t ;
1901 l_CREDIT_COHORT_YR_n l_CREDIT_COHORT_YR_t;
1902 l_PROGRAM_COST_IND_n l_PROGRAM_COST_IND_t;
1903 l_tREASURY_SYMBOL_ID_n l_tREASURY_SYMBOL_ID_t ;
1904 l_indx binary_integer;
1905
1906 l_end_bal_ind VARCHAR2(1);
1907 l_account_number VARCHAR2(30);
1908 l_fund_value VARCHAR2(30);
1909 l_sgl_acct_num VARCHAR2(6);
1910 l_exch_non_exch VARCHAR2(1);
1911 l_cust_non_cust VARCHAR2(1);
1912 l_exception_status VARCHAR2(1);
1913
1914 l_exception_category VARCHAR2(25);
1915 l_account_type VARCHAR2(1);
1916 l_balance_amount number;
1917 l_curr_year_bal number;
1918 l_ccid number(15);
1919 l_DIRECT_OR_REIMB_CODE fv_fund_parameters.DIRECT_OR_REIMB_CODE%type;
1920 l_APPOR_CAT_CODE fv_fund_parameters.fund_category%type;
1921 l_APPOR_CAT_B_CODE fv_facts_prc_Dtl.reporting_code%type;
1922 l_PROGRAM_REPT_CODE fv_facts_prc_Dtl.reporting_code%type;
1923 l_BEA_CAT_CODE fv_fund_parameters.BEA_CATEGORY%type;
1924 l_BORR_SRC_CODE fv_fund_parameters.BORROWING_SOURCE_CODE%type;
1925 l_NEW_BAL_CODE VARCHAR2(3) ;
1926 l_CUR_SUBSEQUENT_CODE VARCHAR2(4) ;
1927 l_PYA_CODE VARCHAR2(4) ;
1928 l_CREDIT_COHORT_YR VARCHAR2(150);
1929 l_PROGRAM_COST_IND fv_facts_prc_Dtl.reporting_code%type;
1930 l_tREASURY_SYMBOL_ID fv_treasury_symbols.treasury_symbol_id%type;
1931 l_account_number_prv VARCHAR2(30);
1932 l_t2_detail_amount NUMBER;
1933 l_fed_account VARCHAR2(1);
1934 l_amount NUMBER;
1935 l_jrnl_run_flag VARCHAR2(1);
1936 l_select_stmt VARCHAR2(10000);
1937 l_select_stmt2 VARCHAR2(10000);
1938 l_last_fetch BOOLEAN;
1939 l_int_run_month NUMBER;
1940 l_period_num_high NUMBER;
1941 l_period_num_low NUMBER;
1942 l_rec_count NUMBER;
1943 l_run_status VARCHAR2(1);
1944 l_populate_flag VARCHAR2(1);
1945 l_parameters VARCHAR2(500);
1946 l_exception_count NUMBER;
1947 l_diff_flag varchar2(1);
1948
1949 l_aid_condition VARCHAR2(200);
1950 l_main_account_condition VARCHAR2(200);
1951 BEGIN
1952
1953 log(l_module_name, 'Begin');
1954 p_err_code := 0;
1955 p_err_buf := null;
1956
1957 gbl_set_of_books_id := p_set_of_books_id;
1958 gbl_fiscal_year := p_period_year;
1959
1960 begin
1961 select decode(period_num,null,'Y',0,'Y','N'),period_num into
1962 l_populate_flag, l_int_run_month
1963 from fv_gtas_run
1964 WHERE set_of_books_id = gbl_set_of_books_id
1965 AND fiscal_year = p_period_year;
1966 exception
1967 when no_data_found then
1968 l_populate_flag := 'Y';
1969 End;
1970
1971 log(l_module_name, 'Deleting records from fv_gtas1_report_t2.');
1972 DELETE FROM fv_gtas1_report_t2
1973 WHERE set_of_books_id = gbl_set_of_books_id;
1974
1975 GET_SEGMENT_NAMES;
1976
1977 l_period_num_high := gbl_period_num_high;
1978 l_period_num_low := gbl_period_num_low;
1979
1980 log(l_module_name, 'Period Num Low: '||l_period_num_low);
1981 log(l_module_name, 'Period Num High: '||l_period_num_high);
1982 log(l_module_name, 'High Period Name: '||gbl_period_name);
1983
1984 IF gbl_agency_id_low IS NOT NULL THEN
1985 l_aid_condition := ' and fts.department_id between '||
1986 ''''||gbl_agency_id_low||'''' ||' and '||
1987 ''''||gbl_agency_id_high||'''' ;
1988 END IF;
1989
1990 IF gbl_main_account_low IS NOT NULL THEN
1991 l_main_account_condition := ' and fts.fund_group_code between '||
1992 ''''||gbl_main_account_low||''''||' and '||
1993 ''''||gbl_main_account_high||'''' ;
1994 END IF;
1995
1996
1997 l_parameters := p_period_year||', '|| l_period_num_high||', '
1998 ||''''||gbl_period_name||''''||', '||
1999 gbl_set_of_books_id||', ';
2000 l_select_stmt2 := ' glb.code_combination_id, ' ||
2001 ' glc.' || Gbl_Bal_Segment_Name || ' , glc.' || Gbl_Acc_Segment_Name ||
2002 ', ''NO'', ''#'', ''#'', ''E'', ''#'',''N'', ''#'', ''#'', ''#'', ''#'', ''#'', ''#'',
2003 ''#'', ''#'', ''#'', ''#'', ''#'', -99, ''#'',
2004 SUM (DECODE (period_num, :gbl_period_num_high,
2005 (begin_balance_dr - begin_balance_cr + NVL(period_net_dr,0)
2006 - NVL(period_net_cr,0)),0)) curr_year_bal,
2007 SUM (DECODE (period_num, :gbl_period_num_low,
2008 (begin_balance_dr - begin_balance_cr),0)) begin_bal,
2009 SUM (DECODE (period_num, :gbl_period_num_high,
2010 (NVL(period_net_dr,0)),0)) period_cy_bal,
2011 SUM (DECODE (period_num, :gbl_period_num_high,
2012 (NVL(period_net_cr,0)),0)) period_cy_cr_bal,
2013 SUM (DECODE (period_num, :gbl_period_num_high,
2014 (begin_balance_dr - begin_balance_cr),0)) period_begin_bal '||
2015 ' FROM gl_balances glb,gl_code_combinations GLC
2016 WHERE glb.actual_flag = '||''''||'A'||''''||'
2017 AND period_year = :gbl_fiscal_year
2018 AND period_num IN (:gbl_period_num_low, :gbl_period_num_high)
2019 AND glb.ledger_id = :gbl_set_of_books_id
2020 AND glb.template_id is NULL
2021 AND glb.currency_code <> ''STAT''
2022 AND glc.code_combination_id = glb.code_combination_id
2023 AND glc.'||gbl_bal_segment_name||' in
2024 (select ffp.fund_value
2025 from fv_fund_parameters ffp,
2026 fv_treasury_symbols fts
2027 where fts.treasury_symbol_id = ffp.treasury_symbol_id
2028 and fts.set_of_books_id = '||gbl_set_of_books_id||
2029 ' and fts.gtas_reportable_indicator = ''Y'' '
2030 ||l_aid_condition||l_main_account_condition
2031 ||') GROUP BY glb.code_combination_id ,'||'glc.'||gbl_bal_segment_name
2032 ||', glc.' || gbl_acc_segment_name
2033 ||' ORDER BY '||'glc.'||gbl_bal_segment_name ||', glc.' || gbl_acc_segment_name;
2034
2035 l_account_number_n := l_account_number_t(null);
2036 l_end_bal_ind_n := l_end_bal_ind_t(null);
2037 l_fund_value_n := l_fund_value_t(null);
2038 l_sgl_acct_num_n := l_sgl_acct_num_t(null);
2039 l_exch_non_exch_n := l_exch_non_exch_t(null);
2040 l_cust_non_cust_n := l_cust_non_cust_t(null);
2041 l_exception_status_n := l_exception_status_t(null);
2042 l_exception_category_n:= l_exception_category_t(null);
2043 l_account_type_n := l_account_type_t(null);
2044 l_balance_amoun_n := l_balance_amoun_t(null);
2045 l_begin_bal_n := l_balance_amoun_t(null);
2046 l_per_begin_bal_n := l_balance_amoun_t(null);
2047 l_cy_dr_bal_n := l_balance_amoun_t(null);
2048 l_cy_cr_bal_n := l_balance_amoun_t(null);
2049 l_ccid_n:= l_ccid_t(null);
2050 l_DIRECT_OR_REIMB_CODE_n := l_DIRECT_OR_REIMB_CODE_t(null);
2051 l_APPOR_CAT_CODE_n := l_APPOR_CAT_CODE_t(null);
2052 l_APPOR_CAT_B_CODE_n := l_APPOR_CAT_B_CODE_t(null);
2053 l_PROGRAM_REPT_CODE_n := l_PROGRAM_REPT_CODE_t(null);
2054 l_BEA_CAT_CODE_n := l_BEA_CAT_CODE_t(null);
2055 l_BORR_SRC_CODE_n := l_BORR_SRC_CODE_t(null);
2056 l_NEW_BAL_CODE_n := l_NEW_BAL_CODE_t(null);
2057 l_CUR_SUBSEQUENT_CODE_n := l_CUR_SUBSEQUENT_CODE_t(null);
2058 l_PYA_CODE_n := l_PYA_CODE_t(null);
2059 l_CREDIT_COHORT_YR_n := l_CREDIT_COHORT_YR_t(null);
2060 l_PROGRAM_COST_IND_n := l_PROGRAM_COST_IND_t(null);
2061 l_tREASURY_SYMBOL_ID_n := l_tREASURY_SYMBOL_ID_t(null);
2062 l_exception_status_n := l_exception_status_t(null);
2063 l_exception_category_n:= l_exception_category_t(null);
2064 l_balance_amoun_n := l_balance_amoun_t(null);
2065 l_begin_bal_n := l_balance_amoun_t(null);
2066 l_per_begin_bal_n := l_balance_amoun_t(null);
2067 l_cy_dr_bal_n := l_balance_amoun_t(null);
2068 l_cy_cr_bal_n := l_balance_amoun_t(null);
2069 l_account_number_n.extend(10000);
2070 l_fund_value_n.extend(10000);
2071 l_end_bal_ind_n.extend(10000);
2072 l_sgl_acct_num_n.extend(10000);
2073 l_exch_non_exch_n.extend(10000);
2074 l_cust_non_cust_n.extend(10000);
2075 l_exception_status_n.extend(10000);
2076 l_exception_category_n.extend(10000);
2077 l_account_type_n.extend(10000);
2078 l_balance_amoun_n.extend(10000);
2079 l_begin_bal_n.extend(10000);
2080 l_per_begin_bal_n.extend(10000);
2081 l_cy_dr_bal_n.extend(10000);
2082 l_cy_cr_bal_n.extend(10000);
2083 l_ccid_n.extend(10000);
2084 l_DIRECT_OR_REIMB_CODE_n.extend(10000);
2085 l_APPOR_CAT_CODE_n.extend(10000);
2086 l_APPOR_CAT_B_CODE_n.extend(10000);
2087 l_PROGRAM_REPT_CODE_n.extend(10000);
2088 l_BEA_CAT_CODE_n.extend(10000);
2089 l_BORR_SRC_CODE_n.extend(10000);
2090 l_NEW_BAL_CODE_n.extend(10000);
2091 l_CUR_SUBSEQUENT_CODE_n.extend(10000);
2092 l_PYA_CODE_n.extend(10000);
2093 l_CREDIT_COHORT_YR_n.extend(10000);
2094 l_PROGRAM_COST_IND_n.extend(10000);
2095 l_tREASURY_SYMBOL_ID_n.extend(10000);
2096
2097 l_select_stmt2 := ' SELECT ' || l_select_stmt2;
2098
2099 log(l_module_name, l_select_stmt2);
2100
2101 l_bal_segment_prv := '####';
2102 gbl_prev_acct := '####';
2103 gbl_bal_segment := '####';
2104 gbl_error_code := 0;
2105 gbl_error_buf := NULL;
2106 l_jrnl_run_flag := 'N';
2107 l_rec_count := 0;
2108
2109
2110 --------------------------------------------------
2111 /* check already being_bal differnce processed */
2112 l_diff_flag := 'N';
2113
2114 begin
2115 select NVL(begin_bal_diff_flag , 'N') into l_diff_flag
2116 from fv_gtas_run
2117 where set_of_books_id = gbl_set_of_books_id
2118 and fiscal_year = gbl_fiscal_year;
2119
2120 -- To delete the erroneous record
2121 log(l_module_name,
2122 'Deleting the begin balance difference records from fv_gtas_diff_balances.');
2123
2124 if l_diff_flag = 'N' then
2125 DELETE FROM fv_gtas_diff_balances
2126 WHERE set_of_books_id = gbl_set_of_books_id
2127 and period_year = gbl_fiscal_year
2128 and balance_type = 'B';
2129 end if;
2130
2131 exception
2132 when no_data_found then
2133 l_diff_flag := 'N';
2134 End;
2135
2136
2137 BEGIN
2138 OPEN t1_record_c for l_select_stmt2 USING
2139 l_period_num_high,
2140 l_period_num_low,
2141 l_period_num_high, l_period_num_high, l_period_num_high,
2142 gbl_fiscal_year, l_period_num_low, l_period_num_high,gbl_set_of_books_id;
2143
2144 l_last_fetch := FALSE;
2145
2146 LOOP
2147 FETCH t1_record_c BULK COLLECT INTO
2148 l_ccid_l,
2149 l_fund_value_l,
2150 l_account_number_l,
2151 l_sgl_acct_num_l,
2152 l_exch_non_exch_l,
2153 l_cust_non_cust_l,
2154 l_exception_status_l,
2155 l_account_type_l,
2156 l_new_record_l,
2157 l_DIRECT_OR_REIMB_CODE_l,
2158 l_APPOR_CAT_CODE_l,
2159 l_APPOR_CAT_B_CODE_l,
2160 l_PROGRAM_REPT_CODE_l,
2161 l_BEA_CAT_CODE_l,
2162 l_BORR_SRC_CODE_l,
2163 l_NEW_BAL_CODE_l,
2164 l_CUR_SUBSEQUENT_CODE_l,
2165 l_PYA_CODE_l,
2166 l_CREDIT_COHORT_YR_l,
2167 l_PROGRAM_COST_IND_l,
2168 l_treasury_symbol_id_l,
2169 l_end_bal_ind_l,
2170 l_balance_amoun_l,
2171 l_begin_bal_l ,
2172 l_cy_dr_bal_l ,
2173 l_cy_cr_bal_l,
2174 l_per_begin_bal_l LIMIT 10000;
2175
2176 IF t1_record_c%NOTFOUND THEN
2177 l_last_fetch := TRUE;
2178 END IF;
2179
2180 l_indx := 0;
2181
2182 log(l_module_name, 'In setup gtas attributes ');
2183 IF (l_ccid_l.count = 0 AND l_last_fetch) THEN
2184 EXIT;
2185 END IF;
2186
2187 FOR i IN l_ccid_l.first .. l_ccid_l.last
2188 LOOP
2189
2190 begin
2191 select 'N' into l_new_record_l(i)
2192 from fv_gtas1_period_attributes
2193 where ccid = l_ccid_l(i)
2194 and period_year = gbl_fiscal_year
2195 and set_of_books_id = gbl_set_of_books_id;
2196 exception
2197 when no_data_found then
2198 l_new_record_l(i) := 'Y';
2199 End;
2200
2201 log(l_module_name, 'l_ccid_l(i)'|| l_ccid_l(i));
2202
2203 log(l_module_name, 'l_new_record_l(i)'||l_new_record_l(i));
2204
2205 l_exception_status := NULL;
2206 l_exception_status_l(i) := NULL;
2207 l_account_number := l_account_number_l(i);
2208 l_fund_value := l_fund_value_l(i);
2209 l_ccid := l_ccid_l(i);
2210 l_balance_amount := l_balance_amoun_l(i);
2211 l_exception_status := 'E';
2212 l_exception_status_l(i) := 'E';
2213 --l_tREASURY_SYMBOL_ID_l(i) :=0;
2214 l_bal_segment := l_fund_value;
2215
2216
2217 log(l_module_name,'---------------------------------');
2218 log(l_module_name,'Fund Value: '||l_fund_value);
2219 log(l_module_name,'Account Number: '|| l_account_number);
2220
2221 --If the account exists in gtas fed accounts table
2222 --set exception status to 2 else to 1, this is to help
2223 --identify ccids later in the balances view used for reporting
2224 BEGIN
2225 SELECT '2'
2226 INTO l_exception_status_l(i)
2227 FROM fv_gtas_fed_accounts
2228 WHERE set_of_books_id = gbl_set_of_books_id
2229 AND account_number = l_account_number
2230 AND fiscal_year = gbl_fiscal_year;
2231
2232 EXCEPTION WHEN NO_DATA_FOUND THEN
2233 l_exception_status_l(i) := '1';
2234 END;
2235 log(l_module_name, 'l_exception_status: '||l_exception_status_l(i));
2236
2237 IF (gbl_prev_acct <> l_account_number or
2238 l_bal_segment <> l_bal_segment_prv) then
2239
2240 GET_USSGL_ACCT_NUM(l_account_number,
2241 l_fund_value, l_ccid,
2242 l_balance_amount,
2243 l_sgl_acct_num,
2244 l_exch_non_exch,
2245 l_cust_non_cust,
2246 l_DIRECT_OR_REIMB_CODE,
2247 l_APPOR_CAT_CODE,
2248 l_APPOR_CAT_B_CODE,
2249 l_PROGRAM_REPT_CODE,
2250 l_BEA_CAT_CODE,
2251 l_BORR_SRC_CODE,
2252 l_NEW_BAL_CODE,
2253 l_CUR_SUBSEQUENT_CODE,
2254 l_PYA_CODE ,
2255 l_CREDIT_COHORT_YR ,
2256 l_PROGRAM_COST_IND,
2257 l_exception_category,
2258 l_treasury_symbol_id,
2259 l_end_bal_ind);
2260
2261 IF (gbl_error_code <> 0) THEN
2262 p_err_code := gbl_error_code;
2263 p_err_buf := gbl_error_buf ;
2264 log(l_module_name, 'An error occurred in GET_USSGL_ACCT_NUM.
2265 No further processing of GTAS will be done.');
2266 RETURN;
2267 END IF;
2268
2269 -- Get the Account Type
2270 l_account_type := GET_ACCOUNT_TYPE(l_account_number);
2271 l_account_type_l(I) := l_account_type;
2272
2273 gbl_prev_acct := l_account_number;
2274 gbl_bal_segment := l_fund_value;
2275 END IF;
2276
2277 --Assign all values to the corresponding columns
2278 l_sgl_acct_num_l(i) := l_sgl_acct_num;
2279 l_cust_non_cust_l(i) := l_cust_non_cust;
2280 l_exch_non_exch_l(i) := l_exch_non_exch;
2281 l_DIRECT_OR_REIMB_CODE_l(i) := l_DIRECT_OR_REIMB_CODE;
2282 l_APPOR_CAT_CODE_l(i) := l_APPOR_CAT_CODE;
2283 l_APPOR_CAT_B_CODE_l(i) := l_APPOR_CAT_B_CODE;
2284 l_PROGRAM_REPT_CODE_l(i) := l_PROGRAM_REPT_CODE;
2285 l_BEA_CAT_CODE_l(i) := l_BEA_CAT_CODE;
2286 l_BORR_SRC_CODE_l(i) := l_BORR_SRC_CODE;
2287 l_NEW_BAL_CODE_l(i) := l_NEW_BAL_CODE;
2288 l_CUR_SUBSEQUENT_CODE_l(i) := l_CUR_SUBSEQUENT_CODE;
2289 l_PYA_CODE_l(i) := l_PYA_CODE;
2290 l_CREDIT_COHORT_YR_l(i) := l_CREDIT_COHORT_YR;
2291 l_PROGRAM_COST_IND_l(i) := l_PROGRAM_COST_IND;
2292 -- l_exception_category_l(i) := l_exception_category;
2293 l_treasury_symbol_id_l(i) := l_treasury_symbol_id;
2294 l_end_bal_ind_l(i) := l_end_bal_ind;
2295
2296
2297 l_bal_segment_prv := l_bal_segment;
2298 l_rec_count := l_rec_count + 1;
2299
2300 --Insert the new ccid
2301 If l_new_record_l(i) = 'Y' then
2302 log(l_module_name, 'l_indx'||l_indx);
2303
2304 l_indx := l_indx + 1;
2305 l_account_number_n(l_indx) := l_account_number_l(i);
2306 l_fund_value_n(l_indx) := l_fund_value_l(i);
2307 l_sgl_acct_num_n(l_indx) := l_sgl_acct_num_l(i);
2308 l_exch_non_exch_n(l_indx) := l_exch_non_exch_l(i);
2309 l_cust_non_cust_n(l_indx) := l_cust_non_cust_l(i);
2310 l_exception_status_n(l_indx) := l_exception_status_l(i);
2311 l_account_type_n(l_indx) := l_account_type_l(i);
2312 l_balance_amoun_n(l_indx) := l_balance_amoun_l(i);
2313 l_begin_bal_n(l_indx) := l_begin_bal_l(i);
2314 l_per_begin_bal_n(l_indx) := l_per_begin_bal_l(i);
2315 l_cy_dr_bal_n(l_indx) := l_cy_dr_bal_l(i);
2316 l_cy_cr_bal_n(l_indx) := l_cy_cr_bal_l(i);
2317 l_ccid_n(l_indx):= l_ccid_l(i);
2318 l_DIRECT_OR_REIMB_CODE_n(l_indx):= l_DIRECT_OR_REIMB_CODE_l(i);
2319 l_APPOR_CAT_CODE_n(l_indx):= l_APPOR_CAT_CODE_l(i);
2320 l_APPOR_CAT_B_CODE_n(l_indx):= l_APPOR_CAT_B_CODE_l(i);
2321 l_PROGRAM_REPT_CODE_n(l_indx) := l_PROGRAM_REPT_CODE_l(i);
2322 l_BEA_CAT_CODE_n(l_indx):= l_BEA_CAT_CODE_l(i);
2323 l_BORR_SRC_CODE_n(l_indx):= l_BORR_SRC_CODE_l(i);
2324 l_NEW_BAL_CODE_n(l_indx):= l_NEW_BAL_CODE_l(i);
2325 l_CUR_SUBSEQUENT_CODE_n(l_indx):= l_CUR_SUBSEQUENT_CODE_l(i);
2326 l_PYA_CODE_n(l_indx):= l_PYA_CODE_l(i);
2327 l_CREDIT_COHORT_YR_n(l_indx):= l_CREDIT_COHORT_YR_l(i);
2328 l_PROGRAM_COST_IND_n(l_indx):= l_PROGRAM_COST_IND_l(i);
2329 l_tREASURY_SYMBOL_ID_n(l_indx):= l_tREASURY_SYMBOL_ID_l(i);
2330 l_end_bal_ind_n(l_indx) := l_end_bal_ind_l(i);
2331 End if;
2332
2333 -------------------------------------------------------
2334 /*
2335 -- create a difference record.
2336 if (l_exception_status <> 'E') then
2337 l_curr_year_balance := l_balance_amoun_l(i) - l_begin_bal_l(i);
2338
2339 l_stage := 'Detail difference';
2340 l_t2_detail_amount := 0;
2341
2342 SELECT NVL(SUM(NVL(t2.amount, 0)), 0)
2343 INTO l_t2_detail_amount
2344 FROM fv_gtas_activity_balances t2
2345 WHERE t2.ccid = l_ccid_l(i)
2346 AND t2.set_of_books_id = gbl_set_of_books_id
2347 AND period_num <= gbl_period_num_high
2348 AND period_year = gbl_fiscal_year;
2349
2350 IF (l_curr_year_balance <> l_t2_detail_amount) THEN
2351 log(l_module_name, '*****inserting detail difference record');
2352 log(l_module_name,
2353 'Inserting into fv_gtas_diff_balances values: ');
2354 log(l_module_name, 'l_ccid_l(i): '||l_ccid_l(i));
2355 log(l_module_name, 'gbl_period_num_low: '||gbl_period_num_low);
2356 log(l_module_name, 'gbl_fiscal_year: '||gbl_fiscal_year);
2357 log(l_module_name, 'gbl_set_of_books_id: '||gbl_set_of_books_id);
2358 log(l_module_name, 'balance_type: D');
2359
2360
2361 INSERT INTO fv_gtas_diff_balances
2362 (
2363 ccid,period_num,period_year,set_of_books_id,
2364 amount,
2365 d_c_indicator,
2366 balance_type,
2367 account_number,
2368 fund_value)
2369 VALUES
2370 (l_ccid_l(i),gbl_period_num_high,gbl_fiscal_year,gbl_set_of_books_id,
2371 (l_curr_year_balance - l_t2_detail_amount),
2372 DECODE(SIGN(l_curr_year_balance - l_t2_detail_amount),
2373 0, 'D', 1, 'D', -1, 'C'),
2374 'D', l_account_number, l_fund_value);
2375
2376 END IF;
2377 END IF;
2378 -------------------------------------------------------------------------
2379 -- Populate fv_gtas_diff_balances with previous year's ending balance
2380 -- and create a difference record
2381
2382 -- check the begin_balance record been created , if not run it
2383
2384 IF (l_diff_flag = 'N' AND
2385 l_account_type IN ('A','L','O')) THEN
2386
2387 l_ending_amount := 0 ;
2388 l_stage := 'Ending balance diff';
2389
2390 SELECT NVL(SUM(amount), 0)
2391 Into L_Ending_Amount
2392 FROM fv_gtas_ending_balances
2393 WHERE ccid = l_ccid_l(i)
2394 AND set_of_books_id = gbl_set_of_books_id
2395 AND fiscal_year = (gbl_fiscal_year - 1)
2396 AND record_category = 'ENDING_BAL'
2397 AND account_number = l_account_number
2398 And Fund_Value = L_Fund_Value
2399 AND account_type IN ('A','L','O');
2400
2401 log(l_module_name, 'L_Ending_Amount: '||L_Ending_Amount);
2402
2403 IF l_begin_bal_l(i) <> l_ending_amount THEN
2404 log(l_module_name, '*****inserting end bal difference record');
2405 log(l_module_name,
2406 'Inserting into fv_gtas_diff_balances values: ');
2407 log(l_module_name, 'l_ccid_l(i): '||l_ccid_l(i));
2408 log(l_module_name, 'gbl_period_num_low: '||gbl_period_num_low);
2409 log(l_module_name, 'gbl_fiscal_year: '||gbl_fiscal_year);
2410 log(l_module_name, 'gbl_set_of_books_id: '||gbl_set_of_books_id);
2411 log(l_module_name, 'balance_type: B');
2412
2413
2414 INSERT INTO fv_gtas_diff_balances
2415 (ccid,period_num,period_year,set_of_books_id,
2416 amount,
2417 d_c_indicator,
2418 balance_type,
2419 account_number,
2420 fund_value)
2421 VALUES
2422 (l_ccid_l(i),gbl_period_num_low,gbl_fiscal_year,gbl_set_of_books_id,
2423 l_begin_bal_l(i) - l_ending_amount,
2424 DECODE(SIGN(l_begin_bal_l(i) - l_ending_amount), 0, 'D', 1, 'D', -1, 'C'),
2425 'B', l_account_number, l_fund_value);
2426 log(l_module_name, 'afterinsert fv_Gtas_diff');
2427 END IF; -- Populate Temp2 with previous year's ending bal
2428
2429 END IF;
2430 */
2431 ----------------------------------------------------
2432 END LOOP; -- for i loop
2433
2434
2435 log(l_module_name, 'Inserting no of new records ' || l_indx);
2436 FORALL i IN 1 .. l_indx
2437 INSERT INTO fv_gtas1_period_attributes
2438 ( period_year,
2439 period_num,
2440 period_name,
2441 set_of_books_id,
2442 ccid,
2443 fund_value,
2444 account_number,
2445 ussgl_account,
2446 exch_non_exch ,
2447 cust_non_cust,
2448 account_type ,
2449 reported_group,
2450 new_rec_flag,
2451 BALANCE_AMOUNT,
2452 BEGIN_BALANCE,
2453 PERIOD_CY_DR_BAL,
2454 PERIOD_CY_CR_BAL ,
2455 PERIOD_BEGIN_BAL,
2456 DIRECT_OR_REIMB_CODE ,
2457 APPOR_CAT_CODE ,
2458 APPOR_CAT_B_CODE ,
2459 PROGRAM_REPT_CODE ,
2460 BEA_CAT_CODE,
2461 BORR_SRC_CODE,
2462 NEW_BAL_CODE,
2463 CUR_SUBSEQUENT_CODE,
2464 PYA_CODE,
2465 CREDIT_COHORT_YR,
2466 PROGRAM_COST_IND,
2467 treasury_symbol_id,
2468 end_bal_ind
2469 )
2470 values (
2471 gbl_fiscal_year,
2472 l_period_num_high,
2473 gbl_period_name,
2474 gbl_set_of_books_id,
2475 l_ccid_n(i),
2476 l_fund_value_n(i),
2477 l_account_number_n(i),
2478 l_sgl_acct_num_n(i),
2479 l_exch_non_exch_n(i),
2480 l_cust_non_cust_n(i),
2481 l_account_type_n(i),
2482 l_exception_status_n(i),
2483 'Y',
2484 l_balance_amoun_n(i),
2485 l_begin_bal_n(i),
2486 l_cy_dr_bal_n(i),
2487 l_cy_cr_bal_n(i),
2488 l_per_begin_bal_n(i),
2489 l_DIRECT_OR_REIMB_CODE_n(i),
2490 l_APPOR_CAT_CODE_n(i) ,
2491 l_APPOR_CAT_B_CODE_n(i) ,
2492 l_PROGRAM_REPT_CODE_n(i) ,
2493 l_BEA_CAT_CODE_n(i),
2494 l_BORR_SRC_CODE_n(i),
2495 l_NEW_BAL_CODE_n(i),
2496 l_CUR_SUBSEQUENT_CODE_n(i),
2497 l_PYA_CODE_n(i),
2498 l_CREDIT_COHORT_YR_n(i),
2499 l_PROGRAM_COST_IND_n(i),
2500 l_treasury_symbol_id_n(i),
2501 l_end_bal_ind_n(i)
2502 );
2503
2504 -- Update gtas attributes in fv_gtas1_period_attributes
2505
2506 log(l_module_name, 'Updating records ' || (l_ccid_l.count - l_indx));
2507
2508 FORALL i IN l_ccid_l.first .. l_ccid_l.last
2509 UPDATE fv_gtas1_period_attributes
2510 SET ussgl_account = l_sgl_acct_num_l(i),
2511 exch_non_exch = l_exch_non_exch_l(i),
2512 cust_non_cust = l_cust_non_cust_l(i),
2513 account_type = l_account_type_l(i),
2514 reported_group = l_exception_status_l(i),
2515 BALANCE_AMOUNT = l_balance_amoun_l(i),
2516 BEGIN_BALANCE = l_begin_bal_l(i),
2517 PERIOD_CY_DR_BAL = l_cy_dr_bal_l(i),
2518 PERIOD_CY_CR_BAL = l_cy_cr_bal_l(i),
2519 PERIOD_BEGIN_BAL = l_per_begin_bal_l(i),
2520 period_num = l_period_num_high,
2521 period_name = gbl_period_name,
2522 end_bal_ind = l_end_bal_ind_l(i),
2523 DIRECT_OR_REIMB_CODE = l_DIRECT_OR_REIMB_CODE_l(i),
2524 APPOR_CAT_B_CODE = l_APPOR_CAT_B_CODE_l(i),
2525 APPOR_CAT_CODE= l_APPOR_CAT_CODE_l(i) ,
2526 PROGRAM_REPT_CODE = l_PROGRAM_REPT_CODE_l(i) ,
2527 BEA_CAT_CODE = l_BEA_CAT_CODE_l(i),
2528 BORR_SRC_CODE = l_BORR_SRC_CODE_l(i),
2529 NEW_BAL_CODE = l_NEW_BAL_CODE_l(i),
2530 CUR_SUBSEQUENT_CODE = l_CUR_SUBSEQUENT_CODE_l(i),
2531 PYA_CODE = l_PYA_CODE_l(i),
2532 CREDIT_COHORT_YR = l_CREDIT_COHORT_YR_l(i),
2533 PROGRAM_COST_IND = l_PROGRAM_COST_IND_l(i),
2534 treasury_symbol_id = l_treasury_symbol_id_l(i)
2535 WHERE ccid = l_ccid_l(i)
2536 and period_year = gbl_fiscal_year
2537 and set_of_books_id = gbl_set_of_books_id
2538 and l_new_record_l(i) = 'N';
2539 END LOOP;
2540 exception
2541 when others then
2542 log(l_module_name, 'after2 '||SQLERRM);
2543 end;
2544
2545
2546 log(l_module_name, 'No of CCID processed ' || l_rec_count);
2547
2548 IF l_rec_count <> 0 THEN
2549
2550 l_exception_count := 0;
2551
2552 -- Count the exception records
2553 SELECT COUNT(*)
2554 INTO l_exception_count
2555 FROM fv_gtas1_report_t2
2556 WHERE set_of_books_id = gbl_set_of_books_id
2557 AND reported_status = 'E'
2558 AND amount <> 0 ;
2559
2560
2561 if l_exception_count > 0 then
2562 log(l_module_name, 'Set up GTAS Attributes completed wth exceptions');
2563 p_err_code := 0;
2564 p_err_buf := 'Set up GTAS Attributes completed with exceptions.';
2565 l_run_status := 'E';
2566 --gbl_exception_exists := 'Y';
2567 --submit_exception_report;
2568 else
2569 l_run_status := 'U';
2570 log(l_module_name, 'Set up GTAS Attributes completed successfully');
2571 p_err_buf := 'Set up GTAS Attributes completed successfully.';
2572 END IF;
2573
2574 ELSE -- l_rec_count
2575 l_run_status := 'U';
2576 log(l_module_name, 'No data found for this period year.');
2577 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No data found for this period year.');
2578 END IF;
2579
2580
2581 -- Update fv_gtas_run only if there were records
2582 -- found for the interface process.
2583 IF l_rec_count > 0 THEN
2584 log(l_module_name, 'Updating GTAS run status.');
2585 UPDATE fv_gtas_run
2586 SET status = l_run_status,
2587 process_date = sysdate,
2588 run_fed_flag = 'I',
2589 begin_bal_diff_flag = 'Y',
2590 period_num = l_period_num_high
2591 WHERE set_of_books_id = gbl_set_of_books_id
2592 AND fiscal_year = p_period_year
2593 AND table_indicator = 'N';
2594
2595 IF gbl_error_code <> 0 THEN
2596 p_err_code := gbl_error_code;
2597 p_err_buf := gbl_error_buf;
2598 ROLLBACK;
2599 RETURN;
2600 END IF;
2601 END IF;
2602
2603 COMMIT;
2604 log(l_module_name, 'End');
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 p_err_code := -1;
2608 p_err_buf := l_module_name||' When others exception: '
2609 ||to_char(SQLCODE) || ' - ' || SQLERRM;
2610 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
2611
2612 END set_up_gtas_attributes;
2613 --------------------------------------------------------------------------------
2614 --------------------------------------------------------------------------------
2615 PROCEDURE SUBMIT_EXCEPTION_REPORT
2616 IS
2617 l_req_id number(15);
2618 l_print_option BOOLEAN;
2619 l_printer_name VARCHAR2(240);
2620 call_status BOOLEAN;
2621 l_copies NUMBER;
2622 rphase VARCHAR2(80);
2623 rstatus VARCHAR2(80);
2624 dphase VARCHAR2(80);
2625 dstatus VARCHAR2(80);
2626 message VARCHAR2(80);
2627 l_module_name varchar2(80) := 'submit_exception_report';
2628 l_run_mode varchar2(80) ;
2629 l_exception_count NUMBER;
2630 xml_layout boolean;
2631 BEGIN
2632 log(l_module_name, 'Begin');
2633
2634 --Count exceptions in fv_gtas1_report_t2
2635 --and submit exception report if there are any exceptions
2636 SELECT count(*)
2637 INTO l_exception_count
2638 FROM fv_gtas1_report_t2
2639 WHERE set_of_books_id = gbl_set_of_books_id
2640 AND fiscal_year = gbl_fiscal_year;
2641
2642 IF l_exception_count > 0 THEN
2643 l_run_mode := 'Fiscal Year';
2644 l_printer_name := FND_PROFILE.VALUE('PRINTER');
2645 l_copies := FND_PROFILE.VALUE('CONC_COPIES');
2646 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS(
2647 printer => l_printer_name,
2648 copies => l_copies);
2649
2650 log(l_module_name, l_module_name|| ' Launching GTAS Interface exception report ...');
2651 xml_layout := FND_REQUEST.ADD_LAYOUT('FV',
2652 'FVGTINTEXC',
2653 'en',
2654 'US',
2655 'PDF');
2656 l_req_id := FND_REQUEST.SUBMIT_REQUEST
2657 ('FV','FVGTINTEXC','','',FALSE, gbl_set_of_books_id,
2658 gbl_period_name, gbl_agency_id_low, gbl_agency_id_high,
2659 gbl_main_account_low, gbl_main_account_high);
2660
2661 -- If concurrent request submission failed, abort process
2662 log(l_module_name,'Request ID for exception report = '|| TO_CHAR(L_REQ_ID));
2663
2664 IF (l_req_id = 0) THEN
2665 Gbl_Error_Code := '-1';
2666 gbl_error_buf := 'Cannot submit GTAS Interface Exception report';
2667 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
2668 RETURN;
2669 ELSE
2670 COMMIT;
2671 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
2672 rphase, rstatus,
2673 dphase, dstatus, message);
2674 IF call_status = FALSE THEN
2675 gbl_error_buf := 'Cannot wait for the status of GTAS Interface Exception Report';
2676 gbl_error_code := -1;
2677 FV_UTILITY.LOG_MESG(l_module_name||'.error4', gbl_error_buf) ;
2678 RETURN;
2679 END IF;
2680 END IF;
2681 --IF there are exceptions then error out and return
2682 Gbl_Error_Code := '-1';
2683 gbl_error_buf := 'Exceptions exist, please fix all exceptions '||
2684 'and rerun the GTAS Interface Process';
2685 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
2686 RETURN;
2687 ELSE
2688 log(l_module_name, 'No exceptions found to report');
2689 END IF;
2690 log(l_module_name, 'End');
2691 End submit_exception_report;
2692 --------------------------------------------------------------------------------
2693 --------------------------------------------------------------------------------
2694 -- Called from the concurrent program Federal Account Creation Process
2695 -- Purpose of this procedure is to process all Federal accounts in
2696 -- FV_GTAS_ATTRIBUTES Table.
2697 -- For each such account, find if it is a child account. If yes, insert
2698 -- this account along with its parent and fed_nonfed, authority_type,
2699 -- bud_impact_ind and trading_partner attributes into
2700 -- FV_GTAS_FED_ACCOUNTS table.
2701 -- Otherwise, if the account is a Parent Account, find all the child
2702 -- accounts and insert them into FV_GTAS_FED_ACCOUNTS table along
2703 -- with all above attributes.
2704 -- Before populating the fv gtas fed accounts table, delete all rows for
2705 -- sob id and period year.
2706 --------------------------------------------------------------------------------
2707 PROCEDURE GET_FEDERAL_ACCOUNTS (p_err_buff OUT NOCOPY VARCHAR2,
2708 p_err_code OUT NOCOPY NUMBER,
2709 p_sob_id IN NUMBER,
2710 p_run_year IN NUMBER)
2711 IS
2712 l_module_name VARCHAR2(200);
2713
2714 vl_child_flex_value_low Fnd_Flex_Value_Hierarchies.child_flex_value_low%TYPE;
2715 vl_child_flex_value_high Fnd_Flex_Value_Hierarchies.child_flex_value_high%TYPE;
2716
2717 CURSOR gtas_attributes_cur IS
2718 SELECT gtas_acct_number, fed_non_fed1, fed_non_fed2, fed_non_fed3,
2719 authority_type1, authority_type2, authority_type3,
2720 authority_type4, authority_type5, authority_type6,
2721 bud_impact_ind1, bud_impact_ind2, trading_partner_flag
2722 FROM fv_gtas_attributes
2723 WHERE set_of_books_id = p_sob_id
2724 AND (
2725 (fed_non_fed1 IS NOT NULL OR fed_non_fed2 IS NOT NULL OR fed_non_fed3 IS NOT NULL)
2726 OR
2727 (authority_type1 IS NOT NULL OR authority_type2 IS NOT NULL OR authority_type3 IS NOT NULL OR
2728 authority_type4 IS NOT NULL OR authority_type5 IS NOT NULL OR authority_type6 IS NOT NULL)
2729 OR
2730 (bud_impact_ind1 IS NOT NULL OR bud_impact_ind2 IS NOT NULL)
2731 )
2732 ORDER BY 1 ;
2733
2734 CURSOR fnd_flex_value_hierarchies_cur IS
2735 SELECT child_flex_value_low, child_flex_value_high
2736 FROM fnd_flex_value_hierarchies
2737 WHERE flex_value_set_id = gbl_acc_value_set_id
2738 AND SUBSTR(parent_flex_value,1,6) = vg_sgl_acct_number;
2739 --AND parent_flex_value = vg_sgl_acct_number;
2740
2741 CURSOR fnd_flex_values_cur IS
2742 SELECT flex_value
2743 FROM fnd_flex_values
2744 WHERE flex_value_set_id = gbl_acc_value_set_id
2745 AND flex_value BETWEEN vl_child_flex_value_low AND vl_child_flex_value_high
2746 ORDER BY 1;
2747
2748
2749 BEGIN
2750 l_module_name := g_module_name || 'Get_Federal_Accounts';
2751
2752 gbl_set_of_books_id := p_sob_id;
2753 gbl_fiscal_year := p_run_year;
2754 log(l_module_name, 'In '||l_module_name);
2755 log(l_module_name, 'Parameters: ');
2756 log(l_module_name, 'p_sob_id: '||p_sob_id);
2757 log(l_module_name, 'p_run_year: '||p_run_year);
2758 gbl_error_code := 0;
2759
2760
2761 GET_SEGMENT_NAMES;
2762
2763
2764 IF gbl_error_code <> 0 THEN
2765 p_err_code := gbl_error_code;
2766 p_err_buff := gbl_error_buf;
2767 log(l_module_name, 'Error in get_segment_names procedure: '||gbl_error_buf);
2768 RETURN;
2769 END IF;
2770
2771 --Delete from fv_gtas_fed_accounts for the parameters
2772 --before populating
2773 DELETE_FED_ACCOUNTS(p_sob_id, p_run_year) ;
2774
2775
2776 -- Loop through records in FV_GTAS_ATTRIBUTES
2777 FOR gtas_attributes_rec IN gtas_attributes_cur
2778 LOOP
2779
2780 log(l_module_name, 'LOOP gtas_attributes_rec Begins.... ');
2781 vg_acct_number := NULL;
2782 vg_fed_nonfed_flag := NULL;
2783 vg_sgl_acct_number := NULL;
2784 vg_trading_partner_flag := NULL;
2785 vg_authority_type_flag := NULL;
2786 vg_bud_impact_ind_flag := NULL;
2787
2788 IF (gtas_attributes_rec.authority_type1 IS NOT NULL
2789 OR gtas_attributes_rec.authority_type2 IS NOT NULL
2790 OR gtas_attributes_rec.authority_type3 IS NOT NULL
2791 OR gtas_attributes_rec.authority_type4 IS NOT NULL
2792 OR gtas_attributes_rec.authority_type5 IS NOT NULL
2793 OR gtas_attributes_rec.authority_type6 IS NOT NULL)
2794 THEN
2795 vg_authority_type_flag := 'Y';
2796 END IF;
2797
2798 IF (gtas_attributes_rec.bud_impact_ind1 IS NOT NULL
2799 OR gtas_attributes_rec.bud_impact_ind2 IS NOT NULL)
2800 THEN
2801 vg_bud_impact_ind_flag := 'Y';
2802 END IF;
2803
2804 IF (gtas_attributes_rec.fed_non_fed1 IS NOT NULL OR
2805 gtas_attributes_rec.fed_non_fed2 IS NOT NULL OR
2806 gtas_attributes_rec.fed_non_fed3 IS NOT NULL) THEN
2807 vg_fed_nonfed_flag := 'Y';
2808 END IF;
2809
2810 --Added for change request 37, bug 15885526
2811 IF gtas_attributes_rec.trading_partner_flag = 'Y' THEN
2812 vg_trading_partner_flag := 'Y';
2813 END IF;
2814
2815 vg_acct_number := gtas_attributes_rec.gtas_acct_number;
2816
2817 log(l_module_name, 'vg_acct_number: '||vg_acct_number);
2818 log(l_module_name, 'vg_fed_nonfed_flag: '||vg_fed_nonfed_flag);
2819 log(l_module_name, 'vg_authority_type_flag: '||vg_authority_type_flag);
2820 log(l_module_name, 'vg_bud_impact_ind_flag: '||vg_bud_impact_ind_flag);
2821 log(l_module_name, 'vg_trading_partner_flag: '||vg_trading_partner_flag);
2822
2823
2824 BEGIN
2825 SELECT SUBSTR(parent_flex_value,1,6)
2826 INTO vg_sgl_acct_number
2827 FROM fnd_flex_value_hierarchies
2828 WHERE vg_acct_number
2829 BETWEEN child_flex_value_low AND child_flex_value_high
2830 AND flex_value_set_id = gbl_acc_value_set_id
2831 AND parent_flex_value <> 'T'
2832 AND SUBSTR(parent_flex_value,1,6) IN
2833 (SELECT ussgl_account
2834 FROM fv_facts_ussgl_accounts
2835 WHERE ussgl_account = SUBSTR(parent_flex_value,1,6))
2836 AND parent_flex_value IN
2837 (
2838 SELECT flex_value
2839 FROM fnd_flex_values
2840 WHERE flex_value_set_id = gbl_acc_value_set_id
2841 AND enabled_flag = 'Y'
2842 );
2843
2844 g_is_acct_parent := 'N';
2845
2846 log(l_module_name, 'g_is_acct_parent: '||g_is_acct_parent);
2847
2848 -- Account is a child account, insert it into GTAS_FED_ACCOUNTS
2849 POPULATE_FV_GTAS_FED_ACCOUNTS;
2850
2851
2852 IF gbl_error_code <> 0 THEN
2853 p_err_code := gbl_error_code;
2854 p_err_buff := gbl_error_buf;
2855 log(l_module_name, 'Error in populate_fv_gtas_fed_accounts procedure: '||
2856 gbl_error_buf);
2857 RETURN;
2858 END IF;
2859
2860 EXCEPTION
2861 WHEN NO_DATA_FOUND THEN
2862 -- If parent not found, then account itself is a parent, find all its child accounts
2863 -- and insert them into FV_GTAS_FED_ACCOUNTS table if not already present.
2864
2865 g_is_acct_parent := 'Y';
2866 vg_sgl_acct_number := vg_acct_number;
2867
2868 log(l_module_name, 'g_is_acct_parent: '||g_is_acct_parent);
2869
2870 FOR fnd_flex_value_hierarchies_rec IN fnd_flex_value_hierarchies_cur
2871 LOOP
2872 log(l_module_name, 'fnd_flex_value_hierarchies_rec LOOP Begins');
2873 vl_child_flex_value_low := NULL;
2874 vl_child_flex_value_high := NULL;
2875 vl_child_flex_value_low := fnd_flex_value_hierarchies_rec.child_flex_value_low;
2876 vl_child_flex_value_high := fnd_flex_value_hierarchies_rec.child_flex_value_high;
2877
2878 log(l_module_name, 'vl_child_flex_value_low: '||vl_child_flex_value_low);
2879 log(l_module_name, 'vl_child_flex_value_high: '||vl_child_flex_value_high);
2880
2881 FOR fnd_flex_values_rec IN fnd_flex_values_cur
2882 LOOP
2883 log(l_module_name, 'fnd_flex_values_rec LOOP Begins...... ');
2884 vg_acct_number := fnd_flex_values_rec.flex_value;
2885 log(l_module_name, 'vg_acct_number: '||vg_acct_number);
2886
2887 POPULATE_FV_GTAS_FED_ACCOUNTS;
2888
2889 IF gbl_error_code <> 0 THEN
2890 p_err_code := gbl_error_code;
2891 p_err_buff := gbl_error_buf;
2892 log(l_module_name, 'Error in populate_fv_gtas_fed_accounts procedure: '||
2893 gbl_error_buf);
2894 RETURN;
2895 END IF;
2896
2897 log(l_module_name, 'END of fnd_flex_values_rec Loop');
2898 END LOOP; -- fnd_flex_values_cur
2899 log(l_module_name, 'END of fnd_flex_value_hierarchies_cur Loop');
2900 END LOOP; -- fnd_flex_value_hierarchies_cur
2901 END; -- Exception
2902 log(l_module_name, 'END of gtas_attributes_cur Loop');
2903 END LOOP; -- gtas_attributes_cur
2904
2905 UPDATE_GTAS_RUN(P_RUN_YEAR, GBL_SET_OF_BOOKS_ID);
2906 log(l_module_name, 'g_error_code: '||gbl_error_code);
2907
2908 log(l_module_name, 'gbl_error_code ->'|| gbl_error_code);
2909 log(l_module_name, 'gbl_error_buf ->'|| gbl_error_buf);
2910 p_err_code := gbl_error_code;
2911 p_err_buff := gbl_error_buf;
2912
2913 COMMIT;
2914
2915 EXCEPTION
2916 WHEN OTHERS THEN
2917 p_err_code := SQLCODE;
2918 p_err_buff := SQLERRM ||
2919 ' -- Error in Get_Federal_Accounts procedure';
2920 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2921 l_module_name||'.exception1',p_err_buff);
2922 RETURN;
2923 END GET_FEDERAL_ACCOUNTS;
2924 --------------------------------------------------------------------------------
2925 PROCEDURE LOG (module IN VARCHAR2, message_line IN VARCHAR2) IS
2926
2927 BEGIN
2928 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2929 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, module, message_line);
2930 END IF;
2931 EXCEPTION
2932 WHEN OTHERS THEN
2933 FV_UTILITY.LOG_MESG('When others error in module: log: '||sqlerrm);
2934 END LOG;
2935 --------------------------------------------------------------------------------
2936 PROCEDURE DELETE_FED_ACCOUNTS(p_set_of_books_id IN VARCHAR2,
2937 p_period_year IN VARCHAR2) IS
2938 l_module_name VARCHAR2(200) := g_module_name||'DELETE_FED_ACCOUNTS';
2939 BEGIN
2940 log(l_module_name, 'IN '||l_module_name);
2941 log(l_module_name, 'p_set_of_books_id: '||p_set_of_books_id);
2942 log(l_module_name, 'p_period_year: '||p_period_year);
2943
2944 DELETE FROM fv_gtas_fed_accounts
2945 WHERE set_of_books_id = p_set_of_books_id
2946 AND fiscal_year = p_period_year;
2947
2948 EXCEPTION
2949 WHEN NO_DATA_FOUND THEN
2950 log(l_module_name, 'No data found to delete.');
2951 WHEN OTHERS THEN
2952 gbl_error_code := -1;
2953 gbl_error_buf := SQLERRM ||
2954 'When others error in DELETE_FED_ACCOUNTS - '||SQLERRM;
2955 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
2956 END DELETE_FED_ACCOUNTS;
2957 --------------------------------------------------------------------------------
2958 PROCEDURE POPULATE_FV_GTAS_FED_ACCOUNTS IS
2959 l_module_name VARCHAR2(200):= g_module_name||'POPULATE_FV_GTAS_FED_ACCOUNTS';
2960
2961 vl_authority_type_flag fv_gtas_fed_accounts.authority_type_flag%TYPE;
2962 vl_bud_impact_ind_flag fv_gtas_fed_accounts.budget_impact_ind_flag%TYPE;
2963 vl_trading_partner_flag fv_gtas_fed_accounts.trading_partner_flag%TYPE;
2964 vl_fed_non_fed_flag fv_gtas_fed_accounts.fed_non_fed_flag%TYPE;
2965 l_ledger_name gl_ledgers_v.name%TYPE;
2966 l_dummy NUMBER;
2967
2968 BEGIN
2969
2970 log(l_module_name, 'BEGIN: '||l_module_name);
2971 log(l_module_name, 'vg_acct_number: '||vg_acct_number);
2972 log(l_module_name, 'gbl_acc_value_set_id: '||gbl_acc_value_set_id);
2973
2974 --ER 14750905
2975 --Check if the child has multiple parents before inserting
2976 --If it has multiple parents which are enabled, then error
2977 BEGIN
2978 SELECT 1
2979 INTO l_dummy
2980 FROM fnd_flex_value_hierarchies
2981 WHERE vg_acct_number
2982 BETWEEN child_flex_value_low AND child_flex_value_high
2983 AND flex_value_set_id = gbl_acc_value_set_id
2984 AND parent_flex_value <> 'T'
2985 AND SUBSTR(parent_flex_value,1,6) IN
2986 (SELECT ussgl_account
2987 FROM fv_facts_ussgl_accounts
2988 WHERE ussgl_account = SUBSTR(parent_flex_value,1,6))
2989 AND parent_flex_value IN
2990 (
2991 SELECT flex_value
2992 FROM fnd_flex_values
2993 WHERE flex_value_set_id = gbl_acc_value_set_id
2994 AND enabled_flag = 'Y'
2995 );
2996
2997 EXCEPTION
2998
2999 WHEN TOO_MANY_ROWS THEN
3000 gbl_error_code := SQLCODE;
3001 gbl_error_buf := 'Account: '||vg_acct_number||' has multiple active parents!';
3002 log(l_module_name, 'Account: '||vg_acct_number||' has multiple active parents!');
3003 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||
3004 '.too many rows',gbl_error_buf);
3005 RETURN;
3006 END;
3007
3008 SELECT name
3009 INTO l_ledger_name
3010 FROM GL_LEDGERS_V
3011 WHERE ledger_id = gbl_set_of_books_id;
3012
3013 IF NOT gbl_header_printed THEN
3014 fnd_file.put_line(fnd_file.output, ' GTAS Federal Account Creation Process Report');
3015 fnd_file.put_line(fnd_file.output, ' ');
3016 fnd_file.put_line(fnd_file.output,'Ledger: '||l_ledger_name);
3017 fnd_file.put_line(fnd_file.output, 'Fiscal Year: '||gbl_fiscal_year);
3018 fnd_file.put_line(fnd_file.output, ' ');
3019 --fnd_file.put_line(fnd_file.output,'USSGL Account Number '||rpad(' ', 16)||' Identified as');
3020 fnd_file.put_line(fnd_file.output,'USSGL Account Number Fed Non-Fed Trading Ptnr Auth Type Bud Impact');
3021 fnd_file.put_line(fnd_file.output,'------ -------------- ----------- ------------ --------- ----------');
3022 gbl_header_printed := TRUE;
3023 END IF;
3024
3025 BEGIN
3026 SELECT fed_non_fed_flag, authority_type_flag, budget_impact_ind_flag, trading_partner_flag
3027 INTO vl_fed_non_fed_flag, vl_authority_type_flag, vl_bud_impact_ind_flag,
3028 vl_trading_partner_flag
3029 FROM fv_gtas_fed_accounts
3030 WHERE account_number = vg_acct_number
3031 AND set_of_books_id = gbl_set_of_books_id
3032 AND fiscal_year = gbl_fiscal_year; --vg_sob_id;
3033
3034 log(l_module_name, 'Account: '||vg_acct_number||' exists with');
3035 log(l_module_name, 'vl_fed_non_fed_flag: '||vl_fed_non_fed_flag);
3036 log(l_module_name, 'vl_authority_type_flag: '||vl_authority_type_flag);
3037 log(l_module_name, 'vl_bud_impact_ind_flag: '||vl_bud_impact_ind_flag);
3038 log(l_module_name, 'vl_trading_partner_flag: '||vl_trading_partner_flag);
3039
3040 IF (vl_fed_non_fed_flag <> vg_fed_nonfed_flag OR
3041 vl_authority_type_flag <> vg_authority_type_flag OR
3042 vl_bud_impact_ind_flag <> vg_bud_impact_ind_flag OR
3043 vl_trading_partner_flag <> vg_trading_partner_flag) THEN
3044 -- To handle if the child is already processed
3045 -- before parent.
3046 IF g_is_acct_parent = 'N' THEN
3047
3048 log(l_module_name, 'Updating account: '||vg_acct_number);
3049
3050 UPDATE fv_gtas_fed_accounts
3051 SET fed_non_fed_flag = vg_fed_nonfed_flag,
3052 authority_type_flag = vg_authority_type_flag,
3053 budget_impact_ind_flag = vg_bud_impact_ind_flag,
3054 trading_partner_flag = vg_trading_partner_flag
3055 WHERE account_number = vg_acct_number
3056 AND set_of_books_id = gbl_set_of_books_id
3057 AND fiscal_year = gbl_fiscal_year;
3058 END IF;
3059
3060
3061 fnd_file.put_line(fnd_file.output, RPAD(vg_sgl_acct_number,' ',6)||RPAD(vg_acct_number,' ',15) ||
3062 RPAD(NVL(vl_fed_non_fed_flag,' '),12)||RPAD(NVL(vl_authority_type_flag,' '),10)||
3063 RPAD(NVL(vl_bud_impact_ind_flag,' '),11)||RPAD(NVL(vl_trading_partner_flag,' '),13)||
3064 RPAD(NVL(vg_fed_nonfed_flag,' '),12)||RPAD(NVL(vg_authority_type_flag,' '),10)||
3065 RPAD(NVL(vg_bud_impact_ind_flag,' '),11)||RPAD(NVL(vg_trading_partner_flag,' '),13));
3066
3067
3068 END IF;
3069 EXCEPTION
3070 WHEN NO_DATA_FOUND THEN
3071
3072 INSERT INTO fv_gtas_fed_accounts
3073 (account_number,
3074 sgl_account_number,
3075 set_of_books_id,
3076 fed_non_fed_flag,
3077 last_run_date,
3078 jc_flag,
3079 fiscal_year,
3080 authority_type_flag,
3081 budget_impact_ind_flag,
3082 trading_partner_flag
3083 )
3084 VALUES
3085 (vg_acct_number,
3086 vg_sgl_acct_number,
3087 gbl_set_of_books_id,
3088 vg_fed_nonfed_flag,
3089 sysdate,
3090 'N',
3091 gbl_fiscal_year,
3092 vg_authority_type_flag,
3093 vg_bud_impact_ind_flag,
3094 vg_trading_partner_flag
3095 );
3096
3097
3098 gbl_error_code := 0;
3099
3100 fnd_file.put_line(fnd_file.output, RPAD(vg_sgl_acct_number,7)||RPAD(vg_acct_number,15)||
3101 RPAD(NVL(vg_fed_nonfed_flag,' '),12)||
3102 RPAD(NVL(vg_trading_partner_flag,' '),13)||RPAD(NVL(vg_authority_type_flag,' '),10)||
3103 RPAD(NVL(vg_bud_impact_ind_flag,' '),11));
3104
3105 END;
3106
3107
3108 log(l_module_name, 'END: '||l_module_name);
3109 EXCEPTION
3110 WHEN OTHERS THEN
3111 gbl_error_code := SQLCODE;
3112 gbl_error_buf := SQLERRM ||
3113 ' -- Error in Populate_Fv_gtas_Fed_Accounts procedure';
3114 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||
3115 '.exception1',gbl_error_buf);
3116
3117 END POPULATE_FV_GTAS_FED_ACCOUNTS;
3118 --------------------------------------------------------------------------------
3119 PROCEDURE UPDATE_GTAS_RUN(p_period_year in VARCHAR2,
3120 p_set_of_books_id in VARCHAR2) IS
3121
3122 l_module_name VARCHAR2(200);
3123 l_stage number(15);
3124 l_posted_date date;
3125
3126 BEGIN
3127
3128 l_module_name := g_module_name || 'UPDATE_GTAS_RUN';
3129 log(l_module_name, 'In '||l_module_name);
3130
3131 UPDATE fv_gtas_run
3132 SET run_fed_flag = 'A',
3133 process_date = sysdate
3134 WHERE set_of_books_id = p_set_of_books_id
3135 AND fiscal_year = p_period_year;
3136 --AND table_indicator = 'N';
3137
3138 --If running for first time, row will not exist above
3139 --then insert a new row
3140 IF SQL%ROWCOUNT = 0 THEN
3141 INSERT INTO fv_gtas_run(set_of_books_id, fiscal_year,
3142 status, process_date,
3143 run_fed_flag,posted_date)
3144 VALUES(gbl_set_of_books_id, p_period_year, 'A', sysdate,'A' ,
3145 l_posted_date);
3146 END IF;
3147
3148 EXCEPTION
3149 WHEN NO_DATA_FOUND THEN
3150 gbl_error_code := -1;
3151 gbl_error_buf := SQLERRM || 'In UPDATE_GTAS_RUN - '|| l_stage ;
3152 WHEN OTHERS THEN
3153 gbl_error_code := -1;
3154 gbl_error_buf := SQLERRM || 'When others error in UPDATE_GTAS_RUN - '||SQLERRM;
3155 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3156 END UPDATE_GTAS_RUN;
3157 --------------------------------------------------------------------------------
3158 FUNCTION GET_ACCT_TYPE(p_acct_num IN VARCHAR2) RETURN VARCHAR2 IS
3159 l_module_name VARCHAR2(200):= g_module_name || 'GET_ACCT_TYPE';
3160 l_acct_type VARCHAR2(1);
3161 BEGIN
3162
3163 log(l_module_name, 'p_acct_num: '||p_acct_num);
3164
3165 -- Get Account Type
3166 SELECT substr(compiled_value_attributes, 5, 1)
3167 INTO l_acct_type
3168 FROM fnd_flex_values
3169 WHERE flex_value_set_id = gbl_acc_value_set_id
3170 AND flex_value = p_acct_num ;
3171
3172 log(l_module_name, 'l_acct_type: '||l_acct_type);
3173 RETURN l_acct_type;
3174
3175 EXCEPTION
3176 WHEN OTHERS THEN
3177 gbl_error_code := -1;
3178 gbl_error_buf := SQLERRM || 'When others error in GET_ACCT_TYPE - '||SQLERRM;
3179 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3180 END GET_ACCT_TYPE;
3181 --------------------------------------------------------------------------------
3182 PROCEDURE PRINT_HEADER IS
3183 l_module_name VARCHAR2(200):= g_module_name || 'PRINT_HEADER';
3184
3185 BEGIN
3186 IF NOT gbl_header_printed THEN
3187 --IF p_hdr_type = 'DISABLED_USSGL' THEN
3188 fnd_file.put_line(fnd_file.output,
3189 'The following GL Accounts do not have valid US SGL Accounts');
3190 fnd_file.put_line(fnd_file.output,
3191 '-----------------------------------------------------------');
3192 fnd_file.put_line(fnd_file.output,
3193 'GL ACCOUNT FUND');
3194 fnd_file.put_line(fnd_file.output,
3195 '----------------------- -----------------------');
3196
3197 gbl_header_printed := TRUE;
3198 END IF;
3199 EXCEPTION
3200 WHEN OTHERS THEN
3201 gbl_error_code := -1;
3202 gbl_error_buf := SQLERRM || 'When others error in PRINT_HEADER - '||SQLERRM;
3203 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3204
3205 END PRINT_HEADER;
3206 --------------------------------------------------------------------------------
3207 --If the USSGL is disabled, then delete all rows
3208 --from period attributes table since these rows
3209 --might exist from a previous run
3210 PROCEDURE DEL_DISABLED_ACCTS(p_acct_num IN VARCHAR2) IS
3211 l_module_name VARCHAR2(200):= g_module_name || 'BEFOREREPORT';
3212 BEGIN
3213 log(l_module_name, 'Begin');
3214
3215 log(l_module_name,'Deleting: '||p_acct_num||' from period attributes.');
3216 DELETE FROM fv_gtas1_period_attributes
3217 WHERE period_year = gbl_period_year
3218 AND set_of_books_id = gbl_set_of_books_id
3219 AND account_number = p_acct_num ;
3220
3221 log(l_module_name, 'End');
3222 EXCEPTION
3223 WHEN OTHERS THEN
3224 gbl_error_code := -1;
3225 gbl_error_buf := SQLERRM ||
3226 'When others error in DEL_DISABLED_ACCTS - '||SQLERRM;
3227 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_error_buf);
3228 END DEL_DISABLED_ACCTS;
3229 --------------------------------------------------------------------------------
3230 BEGIN
3231 g_module_name := 'fv.plsql.FV_GTAS_PKG.';
3232 --------------------------------------------------------------------------------
3233 END fv_gtas_interface;