[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_TRANSACTIONS
Source
1 PACKAGE BODY fv_facts_transactions AS
2 /* $Header: FVFCPROB.pls 120.89.12010000.2 2008/10/03 07:12:22 yanasing ship $ */
3 -- ========================================================================
4 -- Parameters
5 -- ========================================================================
6 g_module_name VARCHAR2(100);
7 vp_errbuf VARCHAR2(1000) ;
8 vp_retcode NUMBER ;
9 vp_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE ;
10 vp_report_fiscal_yr NUMBER(4) ;
11 vp_report_qtr NUMBER(1) ;
12 vp_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE ;
13 vp_run_mode VARCHAR2(1) ;
14 vp_currency_code VARCHAR2(15) ;
15 vp_contact_fname VARCHAR2(20) ;
16 vp_contact_lname VARCHAR2(30) ;
17 vp_contact_phone VARCHAR2(10) ;
18 vp_contact_extn VARCHAR2(5) ;
19 vp_contact_email VARCHAR2(50) ;
20 vp_contact_fax VARCHAR2(10) ;
21 vp_contact_maiden VARCHAR2(25) ;
22 vp_supervisor_name VARCHAR2(40) ;
23 vp_supervisor_phone VARCHAR2(10) ;
24 vp_supervisor_extn VARCHAR2(5) ;
25 vp_agency_name_1 VARCHAR2(40) ;
26 vp_agency_name_2 VARCHAR2(40) ;
27 vp_address_1 VARCHAR2(40) ;
28 vp_address_2 VARCHAR2(40) ;
29 vp_city VARCHAR2(25) ;
30 vp_state VARCHAR2(2) ;
31 vp_zip VARCHAR2(10) ;
32 -- ========================================================================
33 -- FACTS Attributes
34 -- ========================================================================
35 va_cohort VARCHAR2(2) ;
36 va_legis_ind_val VARCHAR2(1) ;
37 va_balance_type_val VARCHAR2(1) ;
38 va_balance_type_flag VARCHAR2(1) ;
39 va_advance_flag VARCHAR2(1) ;
40 va_transfer_ind VARCHAR2(1) ;
41 va_def_indef_val VARCHAR2(1) ;
42 va_public_law_code_flag VARCHAR2(1) ;
43 va_def_indef_flag VARCHAR2(1) ;
44 va_appor_cat_flag VARCHAR2(1) ;
45 va_authority_type_flag VARCHAR2(1) ;
46 va_reimburseable_flag VARCHAR2(1) ;
47 va_public_law_code_val VARCHAR2(7) ;
48 va_appor_cat_val VARCHAR2(1) ;
49 va_appor_cat_b_dtl VARCHAR2(3) ;
50 va_availability_flag VARCHAR2(1) ;
51 va_authority_type_val VARCHAR2(1) ;
52 va_reimburseable_val VARCHAR2(1) ;
53 va_bea_category_flag VARCHAR2(1) ;
54 va_appor_cat_b_txt VARCHAR2(25) ;
55 va_transaction_partner_val VARCHAR2(1) ;
56 va_bea_category_val VARCHAR2(5) ;
57 va_function_flag VARCHAR2(1) ;
58 va_borrowing_source_flag VARCHAR2(1) ;
59 va_def_liquid_flag VARCHAR2(1) ;
60 va_deficiency_val VARCHAR2(1) ;
61 va_borrowing_source_val VARCHAR2(6) ;
62 va_legis_ind_flag VARCHAR2(1) ;
63 va_budget_function VARCHAR2(3) ;
64 va_deficiency_flag VARCHAR2(1) ;
65 va_advance_type_val VARCHAR2(1) ;
66 va_transfer_dept_id VARCHAR2(2) ;
67 va_transfer_main_acct VARCHAR2(4) ;
68
69 va_pl_code_col VARCHAR2(25);
70 va_advance_type_col VARCHAR2(25);
71 va_tr_dept_id_col VARCHAR2(25);
72 va_tr_main_acct_col VARCHAR2(25);
73 va_prn_num VARCHAR2(100);
74 va_prn_txt VARCHAR2(100);
75
76 -- ========================================================================
77 -- Other GLOBAL Variables
78 -- ========================================================================
79 v_period_num gl_period_statuses.period_num%TYPE ;
80 --v_debug varchar2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
81 v_year_gtn2001 BOOLEAN ;
82 v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE ;
83 v_chart_of_accounts_id gl_code_combinations.chart_of_accounts_id%TYPE ;
84 v_acc_val_set_id fnd_flex_value_sets.flex_value_set_id%TYPE ;
85 v_bal_seg_name VARCHAR2(20);
86 v_acc_seg_name VARCHAR2(20);
87 v_fyr_segment_name VARCHAR2(20);
88 v_time_frame fv_treasury_symbols.time_frame%TYPE ;
89 v_financing_acct fv_facts_federal_accounts.financing_account%TYPE ;
90 v_cohort_seg_name VARCHAR2(20) ;
91 v_period_name gl_period_statuses.period_name%TYPE ;
92 v_period_start_dt DATE ;
93 v_period_end_dt DATE ;
94 v_begin_period_name gl_period_statuses.period_name%TYPE ;
95 v_begin_period_start_dt DATE ;
96 v_begin_period_end_dt DATE ;
97 v_begin_period_num gl_period_statuses.period_num%TYPE ;
98 v_cohort_select VARCHAR2(20) ;
99 v_fiscal_yr VARCHAR2(25);
100 v_dummy_cohort VARCHAR2(25);
101 v_acct_attr_flag VARCHAR2(1) ;
102 v_record_category fv_facts_temp.fct_int_record_category%TYPE ;
103 v_sgl_acct_num fv_facts_ussgl_accounts.ussgl_account%TYPE ;
104 v_amount NUMBER ;
105 v_year_budget_auth VARCHAR2(3);
106 v_tbal_fund_value fv_fund_parameters.fund_value%TYPE ;
107 v_acct_num fv_Facts_attributes.facts_acct_number%TYPE ;
108 v_tbal_indicator fv_facts_temp.tbal_indicator%TYPE ;
109 v_period_activity NUMBER;
110 v_edit_check_code NUMBER ;
111 v_g_edit_check_code NUMBER(15);
112 v_cohort_where VARCHAR2(120) ;
113 v_begin_amount NUMBER ;
114 v_prn_prg_seg_name VARCHAR2(20) ;
115 v_catb_prg_seg_name VARCHAR2(20) ;
116
117 v_facts_attributes_setup BOOLEAN ;
118
119 v_catb_rc_flag VARCHAR2(1);
120 v_catb_rc_header_id NUMBER;
121 v_funds_count BINARY_INTEGER;
122
123 v_prn_program_value VARCHAR2(30);
124 v_prn_rc_flag VARCHAR2(1);
125 v_prn_rc_header_id NUMBER;
126
127 v_catb_program_value VARCHAR2(30);
128 v_catb_prg_val_set_id fnd_flex_value_sets.flex_value_set_id%TYPE ;
129 v_prn_prg_val_set_id fnd_flex_value_sets.flex_value_set_id%TYPE ;
130 error_code BOOLEAN;
131 error_message VARCHAR2(600);
132
133
134 TYPE segment_rec IS RECORD
135 (
136 segment VARCHAR2(10),
137 fund_value VARCHAR2(25),
138 prc_flag VARCHAR2(1),
139 prc_header_id NUMBER,
140 code_type VARCHAR2(1)
141 );
142
143 TYPE segment_tab IS TABLE OF segment_rec INDEX BY BINARY_INTEGER;
144 v_segs_array segment_tab;
145
146 -- ========================================================================
147 -- FACTS File Constants
148 -- ========================================================================
149 vc_fiscal_yr VARCHAR2(4);
150 vc_dept_regular VARCHAR2(2);
151 vc_dept_transfer VARCHAR2(2);
152 vc_rpt_fiscal_yr VARCHAR2(4);
153 vc_atb_seq_num VARCHAR2(3);
154 vc_main_account VARCHAR2(4);
155 vc_sub_acct_symbol VARCHAR2(3);
156 vc_maf_seq_num VARCHAR2(3);
157 vc_record_indicator VARCHAR2(1);
158 vc_transfer_to_from VARCHAR2(1);
159 vc_current_permanent_flag VARCHAR2(1);
160 vc_rpt_fiscal_month VARCHAR2(2);
161
162 --------------------------------------------------------------------------------
163 -- Procedures used in the FACTS II Process
164 --------------------------------------------------------------------------------
165 PROCEDURE load_treasury_symbol_id;
166 PROCEDURE purge_facts_transactions;
167 PROCEDURE get_qualifier_segments;
168 PROCEDURE get_treasury_symbol_info;
169 PROCEDURE get_period_info;
170 PROCEDURE get_ussgl_acct_num (acct_num IN VARCHAR2,
171 sgl_acct_num OUT NOCOPY VARCHAR2,
172 exception_category OUT NOCOPY VARCHAR2);
173 PROCEDURE create_facts_record;
174 PROCEDURE load_facts_attributes (acct_num VARCHAR2,
175 fund_val VARCHAR2,
176 ve_amount number);
177 PROCEDURE get_ussgl_info (ussgl_acct_num VARCHAR2,
178 enabled_flag OUT NOCOPY VARCHAR2,
179 reporting_type OUT NOCOPY VARCHAR2);
180 PROCEDURE get_account_type (acct_num VARCHAR2,
181 acct_type OUT NOCOPY VARCHAR2);
182 PROCEDURE get_sgl_parent(acct_num VARCHAR2,
183 sgl_acct_num OUT NOCOPY VARCHAR2);
184 PROCEDURE process_facts_transactions;
185 PROCEDURE calc_balance (fund_value VARCHAR2,
186 acct_num VARCHAR2,
187 period_num NUMBER,
188 period_year NUMBER,
189 balance_type VARCHAR2,
190 fiscal_year VARCHAR2,
191 amount OUT NOCOPY NUMBER,
192 period_activity OUT NOCOPY NUMBER);
193 PROCEDURE get_program_segment(v_fund_value VARCHAR2);
194 PROCEDURE build_appor_select (acct_number VARCHAR2,
195 fund_value VARCHAR2,
196 fiscal_year VARCHAR2,
197 appor_period VARCHAR2,
198 select_stmt OUT NOCOPY VARCHAR2);
199 PROCEDURE get_segment_text(p_program VARCHAR2,
200 p_prg_val_set_id IN NUMBER,
201 p_seg_txt OUT NOCOPY VARCHAR2);
202 PROCEDURE default_processing(vl_fund_value varchar2,
203 vl_acct_num varchar2,
204 rec_cat varchar2 := 'R',
205 vb_amount number,
206 ve_amount number);
207
208 PROCEDURE facts_rollup_records;
209 PROCEDURE check_prc_map_seg(p_treasury_symbol_id IN NUMBER,
210 p_sob_id IN NUMBER,
211 p_fund_value OUT NOCOPY VARCHAR2,
212 p_catb_status OUT NOCOPY VARCHAR2,
213 p_prn_status OUT NOCOPY VARCHAR2);
214 PROCEDURE get_prc_val(p_catb_program_val IN VARCHAR2,
215 p_catb_rc_val OUT NOCOPY VARCHAR2,
216 p_catb_rc_desc OUT NOCOPY VARCHAR2,
217 p_catb_exception OUT NOCOPY NUMBER,
218 p_prn_program_val IN VARCHAR2,
219 p_prn_rc_val OUT NOCOPY VARCHAR2,
220 p_prn_rc_desc OUT NOCOPY VARCHAR2,
221 p_prn_exception OUT NOCOPY NUMBER);
222 --------------------------------------------------------------------------------
223 -- FACTS2 SUBMISSION PROCEDURE
224 --------------------------------------------------------------------------------
225 PROCEDURE submit(errbuf OUT NOCOPY varchar2,
226 retcode OUT NOCOPY number,
227 p_ledger_id IN NUMBER) IS
228
229 -- Submits concurrent request FVFCTTRC
230
231 l_module_name VARCHAR2(200);
232 sob NUMBER(15);
233 rphase VARCHAR2(80);
234 rstatus VARCHAR2(80);
235 dphase VARCHAR2(80);
236 dstatus VARCHAR2(80);
237 message VARCHAR2(80);
238 l_call_status BOOLEAN;
239 req_id NUMBER;
240 submitted_TS NUMBER := 0;
241 vl_fiscalyear_count NUMBER;
242
243 vl_prc_map_count NUMBER := 0;
244 vl_prc_no_code_count NUMBER := 0;
245 vl_catb_rc_map_status VARCHAR2(15);
246 vl_prn_rc_map_status VARCHAR2(15);
247 vl_fund fv_fund_parameters.fund_value%TYPE;
248
249 TYPE g_request_ids IS RECORD (request_id NUMBER) ;
250
251 TYPE g_request_ids_type IS TABLE OF g_request_ids
252 INDEX BY BINARY_INTEGER;
253 l_request_ids g_request_ids_type;
254 l_counter NUMBER := 1;
255 l_flag NUMBER;
256 CURSOR facts_record IS
257 SELECT fv_facts_submission.rowid,
258 fv_facts_submission.Set_Of_Books_Id ,
259 fv_facts_submission.Run_Mode,
260 fv_treasury_symbols.Treasury_Symbol,
261 fv_facts_submission.rep_fyr ,
262 fv_facts_submission.rep_period_num,
263 fv_facts_submission.first_name,
264 fv_facts_submission.last_name,
265 fv_facts_submission.phone_no,
266 fv_facts_submission.phone_ext,
267 fv_facts_submission.email_address,
268 fv_facts_submission.fax_num ,
269 fv_facts_submission.mothers_m_name ,
270 fv_facts_submission.supervisor_name ,
271 fv_facts_submission.supervisor_phone,
272 fv_facts_submission.supervisor_ext ,
273 fv_facts_submission.agency_name_1 ,
274 fv_facts_submission.agency_name_2 ,
275 fv_facts_submission.address_1 ,
276 fv_facts_submission.address_2 ,
277 fv_facts_submission.city ,
278 fv_facts_submission.state ,
279 fv_facts_submission.zip ,
280 fv_facts_submission.currency_code,
281 fv_facts_submission.treasury_symbol_id
282 FROM fv_facts_submission ,
283 fv_treasury_symbols
284 WHERE submit_flag = 'Y'
285 AND fv_treasury_symbols.set_of_books_id = sob
286 AND fv_facts_submission.set_of_books_id = sob
287 AND fv_facts_submission.treasury_symbol_id =
288 fv_treasury_symbols.treasury_symbol_id;
289
290
291 BEGIN
292 retcode := 0;
293 l_module_name := g_module_name || 'submit';
294 sob := p_ledger_id;
295
296 SELECT count(*)
297 INTO vl_fiscalyear_count
298 FROM fv_pya_fiscalyear_map
299 WHERE set_of_books_id = sob;
300
301 -- Check whether program reporting code mapping has
302 -- been done for set of books. If not, then write error
303 -- message and exit process.
304 SELECT count(*)
305 INTO vl_prc_map_count
306 FROM fv_facts_prc_hdr
307 WHERE set_of_books_id = sob;
308
309 -- Check whether code Type is updated
310 -- for the existing data. If not, error out the process.
311
312 SELECT count(1)
313 INTO vl_prc_no_code_count
314 FROM fv_facts_prc_hdr
315 WHERE set_of_books_id = sob
316 AND code_type IS NULL ;
317
318 IF vl_fiscalyear_count > 0 THEN
319 IF vl_prc_map_count > 0 THEN
320 IF vl_prc_no_code_count > 0 THEN
321 errbuf:= 'Reporting Code Type has not been updated for existing ' ||
322 'Records. Please update the records and resubmit!';
323 retcode := -1;
324 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
325 RETURN;
326 END IF;
327
328 FOR crec IN facts_record
329 LOOP -- 1
330 LOOP -- 2
331
332 -- Check whether a segment mapping exists for all
333 -- funds of the treasury symbol. If not, then write
334 -- a log message and continue processing of the next
335 -- treasury symbol.
336 check_prc_map_seg(crec.treasury_symbol_id,
337 crec.set_of_books_id,
338 vl_fund, vl_catb_rc_map_status,vl_prn_rc_map_status);
339
340
341
342 IF retcode <> 0 THEN
343 RETURN;
344 END IF;
345
346 -- If no prc mapping found for the treasury symbol,
347 -- then update submission form with the proper status,
348 -- skip processing for that treasury symbol and
349 -- continue with the next treasury symbol, if any.
350
351
352 IF vl_catb_rc_map_status = 'FAIL' AND vl_prn_rc_map_status = 'FAIL' THEN
353
354 FV_UTILITY.LOG_MESG(
355 'No program reporting code mapping found for'||
356 ' Treasury Symbol: '||crec.treasury_symbol||
357 ' and Fund Value: '||vl_fund);
358
359 UPDATE fv_facts_submission
360 SET submitted_by = fnd_global.user_name,
361 facts2_status = 'NO PRC MAPPED',
362 submit_flag = 'N'
363 WHERE rowid = crec.rowid;
364 retcode := -1;
365 COMMIT; EXIT;
366
367 ELSIF vl_catb_rc_map_status = 'FAIL' THEN
368
369 FV_UTILITY.LOG_MESG(
370 'No Category B reporting code mapping found for'||
371 ' Treasury Symbol: '||crec.treasury_symbol||
372 ' and Fund Value: '||vl_fund);
373
374 UPDATE fv_facts_submission
375 SET submitted_by = fnd_global.user_name,
376 facts2_status = 'NO CATB PRC MAPPED',
377 submit_flag = 'N'
378 WHERE rowid = crec.rowid;
379 retcode := -1;
380 COMMIT;
381
382 EXIT; -- go to next treasury symbol
383
384 ELSIF vl_prn_rc_map_status = 'FAIL' THEN
385
386 FV_UTILITY.LOG_MESG(
387 'No Program Category Number reporting code mapping found for'||
388 ' Treasury Symbol: '||crec.treasury_symbol||
389 ' and Fund Value: '||vl_fund);
390
391 UPDATE fv_facts_submission
392 SET submitted_by = fnd_global.user_name,
393 facts2_status = 'NO PRN PRC MAPPED',
394 submit_flag = 'N'
395 WHERE rowid = crec.rowid;
396 retcode := -1;
397 COMMIT; EXIT;
398
399 ELSE
400 req_id := FND_REQUEST.SUBMIT_REQUEST(
401 'FV',
402 'FVFCTTRC',
403 '','',
404 FALSE,
405 crec.Set_Of_Books_Id,
406 crec.Treasury_Symbol,
407 crec.rep_fyr ,
408 crec.rep_period_num,
409 crec.Run_Mode ,
410 crec.first_name ,
411 crec.last_name ,
412 crec.phone_no ,
413 crec.phone_ext ,
414 crec.email_address,
415 crec.fax_num ,
416 crec.mothers_m_name,
417 crec.supervisor_name,
418 crec.supervisor_phone,
419 crec.supervisor_ext ,
420 crec.agency_name_1,
421 crec.agency_name_2,
422 crec.address_1 ,
423 crec.address_2 ,
424 crec.city,
425 crec.state,
426 crec.zip,
427 crec.currency_code );
428
429 UPDATE fv_facts_submission
430 SET submitted_by = fnd_global.user_name,
431 submitted_id = req_id,
432 facts2_status = 'IN PROCESS'
433 WHERE rowid = crec.rowid;
434 submitted_TS := submitted_TS + 1;
435 COMMIT;
436 l_request_ids(l_counter).request_id := req_id;
437 l_counter := l_counter+1 ;
438
439 -- Exit the loop and go to the next treasury symbol
440 EXIT;
441
442 END IF;
443 END LOOP; -- 2
444 END LOOP; -- 1
445
446 l_counter := 1;
447 l_flag := 0;
448 errbuf:= 'No of Treasury Symbol(s) '||
449 'Submitted for Facts II processs : ' || to_char(submitted_TS);
450
451 WHILE submitted_TS > 0 AND l_flag = 0
452 LOOP
453
454 -- Check status of completed concurrent program
455 -- and if complete exit
456 l_call_status := fnd_concurrent.wait_for_request(
457 l_request_ids(l_counter).request_id,
458 0,
459 0,
460 rphase,
461 rstatus,
462 dphase,
463 dstatus,
464 message);
465
466 IF l_call_status = FALSE THEN
467 errbuf := 'Can not wait for the status of the request ID:'||
468 l_request_ids(l_counter).request_id ;
469 retcode := '2';
470 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
471 ELSIF dphase= 'COMPLETE' THEN
472 IF l_counter = l_request_ids.COUNT THEN
473 l_flag := 1;
474 END IF;
475
476 l_counter := l_counter+1 ;
477
478 END IF;
479
480 END LOOP;
481
482 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
483 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,errbuf);
484 END IF;
485
486 ELSE -- vl_prc_map_count = 0
487 errbuf:= 'Program Reporting Code Mapping has not been done! '||
488 'Please map the Program Reporting Code and resubmit!';
489 retcode := -1;
490 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
491 END IF;
492
493 ELSE -- vl_fiscalyear_count = 0
494 errbuf:= 'Budget Fiscal Year Mapping has not been done! '||
495 'Please map the Budget Fiscal Year Segments and resubmit!';
496 retcode := -1;
497 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
498 END IF;
499 EXCEPTION
500 WHEN OTHERS THEN
501 errbuf := SQLERRM;
502 retcode := -1;
503 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
504 l_module_name||'.final_exception',errbuf);
505 RAISE;
506
507
508 END SUBMIT;
509 -------------------------------------------------------------------------------
510 -- PROCEDURE MAIN
511 --------------------------------------------------------------------------------
512 -- Main procedure that is called to execute FACTS process.
513 -- This calls all subsequent procedures that are part of the FACTS
514 -- process.
515 --------------------------------------------------------------------------------
516 PROCEDURE main(
517 errbuf OUT NOCOPY VARCHAR2,
518 retcode OUT NOCOPY NUMBER,
519 p_ledger_id NUMBER,
520 treasury_symbol VARCHAR2,
521 report_fiscal_yr NUMBER ,
522 report_period_num NUMBER ,
523 run_mode VARCHAR2,
524 contact_fname VARCHAR2,
525 contact_lname VARCHAR2,
526 contact_phone NUMBER ,
527 contact_extn NUMBER ,
528 contact_email VARCHAR2,
529 contact_fax NUMBER,
530 contact_maiden VARCHAR2,
531 supervisor_name VARCHAR2,
532 supervisor_phone NUMBER ,
533 supervisor_extn NUMBER ,
534 agency_name_1 VARCHAR2,
535 agency_name_2 VARCHAR2,
536 address_1 VARCHAR2,
537 address_2 VARCHAR2,
538 city VARCHAR2,
539 state VARCHAR2,
540 zip VARCHAR2,
541 currency_code VARCHAR2)
542 IS
543 l_module_name VARCHAR2(200);
544 vl_facts2_status varchar2(25);
545
546 vl_fund VARCHAR2(25);
547 vl_catb_rc_map_status VARCHAR2(10);
548 vl_prn_rc_map_status VARCHAR2(10);
549
550 BEGIN
551 l_module_name := g_module_name || 'main';
552 -- Load FACTS Parameters into Global Variables
553 vp_set_of_books_id := p_ledger_id ;
554 vp_treasury_symbol := treasury_symbol ;
555 vp_report_fiscal_yr := report_fiscal_yr ;
556 v_period_num := report_period_num;
557 vp_run_mode := run_mode ;
558 vp_retcode := 0 ;
559 vp_currency_code := currency_code ;
560
561 -- Load FACTS Conacts Information to Global Variables
562 vp_contact_fname := RPAD(contact_fname,20);
563 vp_contact_lname := RPAD(contact_lname,30) ;
564 vp_contact_phone := LPAD(contact_phone,10);
565 vp_contact_extn := NVL(LPAD(contact_extn,5),LPAD('',5));
566 vp_contact_email := RPAD(contact_email,50);
567 vp_contact_fax := LPAD(contact_fax,10);
568 vp_contact_maiden := RPAD(contact_maiden,25);
569 vp_supervisor_name := RPAD(supervisor_name,40);
570 vp_supervisor_phone := LPAD(supervisor_phone,10);
571 vp_supervisor_extn := NVL(LPAD(supervisor_extn,5),LPAD('',5));
572 vp_agency_name_1 := RPAD(agency_name_1,40);
573 vp_agency_name_2 := NVL(RPAD(agency_name_2,40),RPAD('',40));
574 vp_address_1 := RPAD(address_1,40);
575 vp_address_2 := NVL(RPAD(address_2,40),RPAD('',40));
576 vp_city := RPAD(city,25) ;
577 vp_state := RPAD(state,2);
578 vp_zip := RPAD(zip,10);
579
580
581
582 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
583 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
584 'Deriving Treasury Symbol Id .....') ;
585 END IF;
586 load_treasury_symbol_id ;
587
588 IF vp_retcode <> 0 THEN
589 retcode := vp_retcode;
590 errbuf := vp_errbuf;
591 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
592 l_module_name,vp_errbuf);
593 RETURN;
594 END IF;
595 check_prc_map_seg(v_treasury_symbol_id,
596 p_ledger_id,
597 vl_fund, vl_catb_rc_map_status,vl_prn_rc_map_status);
598
599
600 IF vp_retcode <> 0 OR vl_catb_rc_map_status = 'FAIL' OR
601 vl_prn_rc_map_status = 'FAIL' THEN
602 retcode := vp_retcode;
603 errbuf := vp_errbuf;
604 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
605 l_module_name,vp_errbuf);
606 RETURN;
607 END IF;
608
609 IF vp_report_fiscal_yr > 2001
610 THEN v_year_gtn2001 := TRUE;
611 END IF;
612
613
614 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
615 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
616 'Running FACTSII process');
617 END IF;
618
619 vc_fiscal_yr := LPAD(to_char(vp_report_fiscal_yr),4) ;
620
621 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
622 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
623 'Deriving Treasury Symbol Id .....') ;
624 END IF;
625 load_treasury_symbol_id ;
626
627 IF vp_retcode = 0 THEN
628 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
629 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
630 'Purging FACTS Transactions.....') ;
631 END IF;
632 purge_facts_transactions ;
633 END IF ;
634
635 IF vp_retcode = 0 THEN
636 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
637 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
638 'Deriving Qualifier Segments.....') ;
639 END IF;
640 get_qualifier_segments ;
641 END IF ;
642
643 IF vp_retcode = 0 THEN
644 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
646 'Deriving Treasury Symbol information.....');
647 END IF;
648 get_treasury_symbol_info ;
649 END IF ;
650
651 IF vp_retcode = 0 THEN
652 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
654 'Deriving Period information.....') ;
655 END IF;
656 get_period_info ;
657 END IF ;
658
659 IF vp_retcode = 0 THEN
660 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
662 'Starting FACTS Main Process.....') ;
663 END IF;
664 process_facts_transactions ;
665 END IF ;
666
667 IF vp_retcode = 0 THEN
668 retcode := v_edit_check_code ;
669 IF v_g_edit_check_code = 1 THEN
670 vl_facts2_status := 'SOFT EDIT FAILED';
671 ELSIF v_g_edit_check_code = 2 THEN
672 vl_facts2_status := 'HARD EDIT CHECK FAILED';
673 ELSE
674 vl_facts2_status := 'COMPLETED';
675 END IF ;
676 -- If public law code and other attributes are not setup
677 -- on the system parameters form, end the process with a warning.
678 IF NOT v_facts_attributes_setup
679 THEN
680 retcode := 1;
681 errbuf := 'Generate FACTS II Reports and Bulk Files Process completed with warning '||
682 'because the Public Law, Advance and Transfer attribute '||
683 'columns are not established on the '||
684 'Define System Parameters Form.';
685 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
686 END IF;
687
688 ELSIF vp_retcode = 1 THEN
689 retcode := vp_retcode ;
690 errbuf := vp_errbuf ;
691 vl_facts2_status := 'NO_TRANSACTION_FOUND';
692 ELSE
693 retcode := vp_retcode ;
694 errbuf := vp_errbuf ;
695 vl_facts2_status := 'ERROR';
696 ROLLBACK ;
697 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
698 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
699 'Stopped FACTS Main Process.....') ;
700 END IF;
701 END IF ;
702
703 ---- Updating fv_facts_submission with the status and uncheck submit flag
704 UPDATE fv_facts_submission
705 SET submit_flag = 'N',
706 facts2_status = vl_facts2_status
707 WHERE submit_flag = 'Y'
708 AND treasury_symbol_id = v_treasury_symbol_id ;
709 COMMIT;
710 EXCEPTION
711 WHEN OTHERS THEN
712 vp_retcode := sqlcode ;
713 vp_errbuf := sqlerrm || ' [MAIN] ' ;
714 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
715 l_module_name||'.final_exception',vp_errbuf);
716
717 ROLLBACK;
718
719 UPDATE fv_facts_submission
720 SET submit_flag = 'N',
721 facts2_status = 'ERROR'
722 WHERE submit_flag = 'Y'
723 AND treasury_symbol_id = v_treasury_symbol_id ;
724
725 COMMIT ;
726 END main ;
727 --------------------------------------------------------------------------------
728 PROCEDURE load_treasury_symbol_id
729 IS
730 l_module_name VARCHAR2(200);
731 BEGIN
732 l_module_name := g_module_name || 'load_treasury_symbol_id';
733 SELECT treasury_symbol_id
734 INTO v_treasury_symbol_id
735 FROM fv_treasury_symbols
736 WHERE treasury_symbol = vp_treasury_symbol
737 AND set_of_books_id = vp_set_of_books_id ;
738
739 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
740 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
741 ' Treasury Symbol ID: ' ||v_treasury_symbol_id);
742 END IF;
743
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN
746 vp_retcode := -1 ;
747 vp_errbuf := 'Treasury Symbol Id cannot be found for the Treasury
748 Symbol - '||vp_treasury_symbol||' [ GET_TREASURY_SYMBOL_ID ] ' ;
749 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
750 l_module_name||'.exception1',vp_errbuf);
751 WHEN TOO_MANY_ROWS Then
752 vp_retcode := -1 ;
753 vp_errbuf := 'More than one Treasury Symbol Id found for the Treasury
754 Symbol - '||vp_treasury_symbol||' [ GET_TREASURY_SYMBOL_ID ] ' ;
755 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
756 l_module_name||'.exception2',vp_errbuf);
757 WHEN OTHERS THEN
758 vp_errbuf := SQLERRM;
759 vp_retcode := -1;
760 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
761 l_module_name||'.final_exception',vp_errbuf);
762 RAISE;
763 END load_treasury_symbol_id ;
764 --------------------------------------------------------------------------------------
765 -- Purges all FACTS transactions from the FV_FACTS_TEMP table for
766 -- the passed Treasaury Symbol.
767 --------------------------------------------------------------------------------------
768 PROCEDURE purge_facts_transactions
769 IS
770 l_module_name VARCHAR2(200);
771 BEGIN
772 l_module_name := g_module_name || 'purge_facts_transactions';
773 -- Delete from the temp table based on the treasury_symbol_id
774
775 DELETE FROM fv_facts_temp
776 WHERE treasury_symbol_id = v_treasury_symbol_id ;
777
778 DELETE FROM fv_facts_edit_check_status
779 WHERE treasury_symbol_id = v_treasury_symbol_id ;
780
781 COMMIT ;
782
783 EXCEPTION
784 -- Exception Processing
785 When NO_DATA_FOUND Then
786 Null ;
787 When Others Then
788 vp_retcode := sqlcode ;
789 vp_errbuf := sqlerrm ;
790 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
791 l_module_name||'.final_exception',vp_errbuf);
792 END purge_facts_transactions ;
793 --------------------------------------------------------------------------------
794 -- Gets the Accounting and Balancing segment names for the Chart
795 -- Of Accounts associated with the passed set of Books.
796 --------------------------------------------------------------------------------
797 PROCEDURE get_qualifier_segments
798 IS
799 l_module_name VARCHAR2(200);
800 num_boolean BOOLEAN ;
801 apps_id NUMBER;
802 flex_code VARCHAR2(25);
803 seg_number NUMBER ;
804 seg_app_name VARCHAR2(40) ;
805 seg_prompt VARCHAR2(25) ;
806 seg_value_set_name VARCHAR2(40) ;
807 invalid_segment EXCEPTION ;
808 BEGIN
809
810 l_module_name := g_module_name || 'get_qualifier_segments';
811 apps_id := 101 ;
812 flex_code := 'GL#' ;
813
814 -- Getting the Chart of Accounts Id
815 BEGIN
816 SELECT chart_of_accounts_id
817 INTO v_chart_of_accounts_id
818 FROM gl_ledgers_public_v
819 WHERE ledger_id = vp_set_of_books_id;
820 EXCEPTION
821 WHEN NO_DATA_FOUND THEN
822 vp_retcode := -1 ;
823 vp_errbuf := 'Error getting Chart of Accounts Id for ledger id '
824 ||vp_set_of_books_id;
825 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf);
826 RETURN;
827 END;
828 -- Getting the Account and Balancing segments' application column names
829 BEGIN
830 FV_UTILITY.get_segment_col_names(v_chart_of_accounts_id,
831 v_acc_seg_name,
832 v_bal_seg_name,
833 error_code,
834 error_message);
835 EXCEPTION
836 WHEN OTHERS THEN
837 vp_retcode := -1;
838 vp_errbuf := error_message;
839 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf);
840 RETURN;
841 END;
842
843 -- Getting the Value Set Id for the Account Segment
844 BEGIN
845 -- Getting the Value set Id for finding hierarchies
846 SELECT flex_value_set_id
847 INTO v_acc_val_set_id
848 FROM fnd_id_flex_segments
849 WHERE application_column_name = v_acc_seg_name
850 AND application_id = 101
851 AND id_flex_code = 'GL#'
852 AND id_flex_num = v_chart_of_accounts_id;
853 EXCEPTION
854 WHEN NO_DATA_FOUND THEN
855 vp_retcode := -1 ;
856 vp_errbuf := 'Error getting Value Set Id for segment'
857 ||v_acc_seg_name||' [GET_USSGL_ACCOUNT_NUM]' ;
858 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
859 RETURN;
860 END ;
861
862 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
863 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
864 ' COA ID: '||v_chart_of_accounts_id);
865 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
866 ' ACC SEG: '||v_acc_seg_name);
867 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
868 ' BAL SEG: '||v_bal_seg_name);
869 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
870 ' ACC Val Set ID: '||v_acc_val_set_id);
871 END IF;
872
873 -- Getting Fiscal year segment name from fv_pya_fiscal_year_segment
874 SELECT application_column_name
875 INTO v_fyr_segment_name
876 FROM fv_pya_fiscalyear_segment
877 WHERE set_of_books_id = vp_set_of_books_id;
878 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
879 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
880 'Fiscal year segment: '||v_fyr_segment_name);
881 END IF;
882 EXCEPTION
883 WHEN OTHERS THEN
884 vp_retcode := sqlcode;
885 vp_errbuf := sqlerrm ;
886 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
887 l_module_name||'.final_exception',vp_errbuf);
888 END get_qualifier_segments ;
889 --------------------------------------------------------------------------------------
890 -- Gets all the information that remains contant throughout the
891 -- FACTS output file.
892 --------------------------------------------------------------------------------------
893 PROCEDURE get_treasury_symbol_info
894 IS
895 l_module_name VARCHAR2(200);
896 vl_fund_category VARCHAR2(1) ;
897 vl_resource_type VARCHAR2(80) ;
898 vl_time_frame VARCHAR2(25) ;
899 vl_established_fy NUMBER ;
900 vl_financing_acct VARCHAR2(1) ;
901 vl_years_available NUMBER ;
902 vl_fiscal_month_count NUMBER ;
903 BEGIN
904 l_module_name := g_module_name || 'get_treasury_symbol_info';
905 SELECT
906 fts.resource_type,
907 RPAD(fffa.treasury_dept_code, 2),
908 fts.time_frame,
909 fts.established_fiscal_yr,
910 fffa.financing_account,
911 fffa.cohort_segment_name,
912 RPAD(fffa.treasury_acct_code, 4),
913 NVL(LPAD(fts.tafs_sub_acct,3, '0'),'000'),
914 --NVL(LPAD(fts.tafs_split_code, 3, '0'),'000'),
915 fts.years_available,
916 NVL(fts.dept_transfer, ' ')
917 INTO
918 vl_resource_type,
919 vc_dept_regular,
920 vl_time_frame,
921 vl_established_fy,
922 vl_financing_acct,
923 v_cohort_seg_name,
924 vc_main_account,
925 vc_sub_acct_symbol,
926 --vc_acct_split_seq_num,
927 vl_years_available,
928 vc_dept_transfer
929 FROM
930 fv_facts_federal_accounts fffa,
931 fv_treasury_symbols fts
932 WHERE fffa.federal_acct_symbol_id = fts.federal_acct_symbol_id
933 AND fts.treasury_symbol = vp_treasury_symbol
934 AND fts.set_of_books_id = vp_set_of_books_id
935 AND fffa.set_of_books_id = vp_set_of_books_id ;
936
937 v_time_frame := vl_time_frame;
938 v_financing_acct := vl_financing_acct;
939
940 /* IF v_year_gtn2001 THEN
941 vc_acct_split_seq_num := '000';
942 END IF; */
943
944 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
946 l_module_name, 'Financing Acct >>> - ' ||
947 vl_financing_acct || ' >>>> - Cohort Seg Name - ' ||
948 v_cohort_seg_name) ;
949 END IF ;
950 ------------------------------------------------
951 -- Deriving COHORT Value
952 ------------------------------------------------
953 IF vl_financing_acct NOT IN ('D', 'G') THEN
954 -- Consider COHORT value only for 'D' and 'G' financing Accounts
955 v_cohort_seg_name := NULL ;
956 END IF ;
957
958 -- Deriving FISCAL_YEAR
959 IF vl_time_frame = 'SINGLE' THEN
960 vc_fiscal_yr := ' ' || substr(to_char(vl_established_fy), 3, 2) ;
961 ELSIF vl_time_frame IN ('NO_YEAR', 'REVOLVING') THEN
962 vc_fiscal_yr := ' X' ;
963 ELSIF vl_time_frame IN ('MULTIPLE') THEN
964 vc_fiscal_yr := SUBSTR(TO_CHAR(vl_established_fy), 3,2) ||
965 SUBSTR(TO_CHAR(vl_established_fy + vl_years_available - 1),3,2) ;
966 END IF ;
967
968
969 -- Preparer Id and Certifier Id and rpt_fiscal_yr
970 -- are derived from Parameters
971 vc_rpt_fiscal_yr := LPAD(to_char(vp_report_fiscal_yr), 4) ;
972
973 SELECT to_char(count(*) , '09')
974 INTO vl_fiscal_month_count
975 FROM gl_period_statuses
976 WHERE ledger_id = vp_set_of_books_id
977 AND application_id = 101
978 AND period_year = vp_report_fiscal_yr
979 -- AND adjustment_period_flag = 'N'
980 AND period_num <= v_period_num ;
981
982 vc_rpt_fiscal_month := LTRIM(TO_CHAR(vl_fiscal_month_count,'09')) ;
983
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 vp_retcode := -1 ;
987 vp_errbuf := 'Error Getting Treasury Symbol related Information'||
988 ' for the passed Treasury Symbol [GET_TREASURY_SYMBOL_INFO] ' ;
989 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
990 l_module_name||'.exception1', vp_errbuf) ;
991 WHEN TOO_MANY_ROWS THEN
992 vp_retcode := -1 ;
993 vp_errbuf := 'More than one set of information returned for the'||
994 ' passed Treasury Symbol [GET_TREASURY_SYMBOL_INFO]' ;
995 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
996 l_module_name||'.exception2', vp_errbuf) ;
997 WHEN OTHERS THEN
998 vp_errbuf := SQLERRM;
999 vp_retcode := -1;
1000 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1001 l_module_name||'.final_exception',vp_errbuf);
1002 RAISE;
1003 END get_treasury_symbol_info ;
1004 --------------------------------------------------------------------------------
1005 -- Gets the Period infomation like Period Number, Period_year,
1006 -- quarter number and other corresponding period information based on
1007 -- the quarter number passed to the Main Procedure
1008 --------------------------------------------------------------------------------
1009 PROCEDURE get_period_info
1010 IS
1011 l_module_name VARCHAR2(200);
1012 BEGIN
1013 l_module_name := g_module_name || 'get_period_info';
1014
1015 BEGIN
1016 -- selecting quarter based on period number as part
1017 -- of 'FACTS II monthly reporting'
1018 SELECT period_name,
1019 start_date,
1020 end_date ,
1021 quarter_num
1022 INTO v_period_name,
1023 v_period_start_dt,
1024 v_period_end_dt,
1025 vp_report_qtr
1026 FROM gl_period_statuses
1027 WHERE ledger_id = vp_set_of_books_id
1028 AND application_id = 101
1029 AND period_year = vp_report_fiscal_yr
1030 AND period_num = v_period_num ;
1031
1032 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1033 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1034 l_module_name,' Period Name: '||v_period_name);
1035 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1036 l_module_name,' Period Start Dt: '||v_period_start_dt);
1037 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1038 l_module_name,' Period End Dt: '||v_period_end_dt);
1039 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1040 l_module_name,' Quarter Num: '||vp_report_qtr);
1041 END IF;
1042
1043 EXCEPTION
1044 WHEN NO_DATA_FOUND THEN
1045 vp_retcode := -1 ;
1046 vp_errbuf := 'Error Getting Period Information for the passed '||
1047 'period [GET_PERIOD_INFO]' ;
1048 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
1049 RETURN;
1050 WHEN TOO_MANY_ROWS THEN
1051 vp_retcode := -1 ;
1052 vp_errbuf := 'More than one period information returned for the '||
1053 'passed Period [GET_PERIOD_INFO]' ;
1054 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
1055 RETURN;
1056 END ;
1057
1058 BEGIN
1059
1060 -- Select Period Information for Beginning Period
1061 SELECT period_name,
1062 start_date,
1063 end_date,
1064 period_num
1065 INTO v_begin_period_name,
1066 v_begin_period_start_dt,
1067 v_begin_period_end_dt,
1068 v_begin_period_num
1069 FROM gl_period_statuses
1070 WHERE period_num =
1071 (SELECT MIN(period_num)
1072 FROM gl_period_statuses
1073 WHERE period_year = vp_report_fiscal_yr
1074 AND ledger_id = vp_set_of_books_id
1075 AND application_id = 101
1076 AND adjustment_period_flag = 'N')
1077 AND application_id = 101
1078 -- AND adjustment_period_flag = 'N'
1079 AND ledger_id = vp_set_of_books_id
1080 AND period_year = vp_report_fiscal_yr ;
1081
1082 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1083 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1084 l_module_name,' Begin Prd Name: '||v_begin_period_name);
1085 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1086 l_module_name,
1087 ' Begin Prd St Dt: '|| v_begin_period_start_dt);
1088 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1089 ' Begin Prd End Dt: '|| v_begin_period_end_dt);
1090 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1091 ' Begin Prd Num: '||v_begin_period_num);
1092 END IF;
1093
1094 EXCEPTION
1095 WHEN NO_DATA_FOUND THEN
1096 vp_retcode := -1 ;
1097 vp_errbuf := 'Error Getting Beginning Period Information ' ||
1098 '[GET_PERIOD_INFO]' ;
1099 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
1100 l_module_name, vp_errbuf) ;
1101 RETURN;
1102 WHEN TOO_MANY_ROWS THEN
1103 vp_retcode := -1 ;
1104 vp_errbuf := 'More than one Beginning Period Returned !!'||
1105 ' [GET_PERIOD_INFO]' ;
1106 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
1107 l_module_name, vp_errbuf) ;
1108 RETURN;
1109 END ;
1110 EXCEPTION
1111 -- Exception Processing
1112 WHEN OTHERS THEN
1113 vp_retcode := sqlcode ;
1114 vp_errbuf := sqlerrm || ' [GET_PERIOD_INFO] ' ;
1115 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1116 l_module_name||'.final_exception',vp_errbuf);
1117 END get_period_info ;
1118
1119 --------------------------------------------------------------------------------
1120 -- PROCEDURE process_facts_transactions
1121 --------------------------------------------------------------------------------
1122 -- This procedure selects all the transactions that needs to be
1123 -- analyzed for reporting in the FACTS output file. After getting the
1124 -- list of transactions that needs to be reported, it applies all the
1125 -- FACTS attributes for the account number and performs further
1126 -- processing for Legislative Indicator and Apportionment Category.
1127 -- It populates the table FV_FACTS_TEMP for edit check process to
1128 -- perform edit checks.
1129 --------------------------------------------------------------------------------
1130 PROCEDURE process_facts_transactions
1131 IS
1132 l_module_name VARCHAR2(200);
1133
1134 vl_main_cursor INTEGER ;
1135 vl_main_select VARCHAR2(2000) ;
1136 vl_fund_value VARCHAR2(25) ;
1137 vl_acct_num VARCHAR2(25) ;
1138 vl_cohort_yr VARCHAR2(25) ;
1139 vl_exec_ret INTEGER ;
1140 vl_row_count NUMBER := 0 ;
1141 vl_main_fetch INTEGER ;
1142 vl_old_acct_num VARCHAR2(25);
1143 vl_sgl_acct_num VARCHAR2(25) ;
1144 vl_amount NUMBER := 0 ;
1145 ve_amount NUMBER := 0 ; -- bug5065974
1146 vb_amount NUMBER := 0 ; -- bug5065974
1147 vb_balance_amount NUMBER := 0;
1148
1149 vl_old_exception VARCHAR2(30);
1150 vl_period_activity NUMBER := 0;
1151 vl_legis_cursor INTEGER ;
1152 vl_legis_select VARCHAR2(5000) ;
1153 vl_legis_ref VARCHAR2(240) ;
1154 vl_legis_amount NUMBER := 0 ;
1155 vl_effective_date DATE;
1156 vl_period_name gl_je_lines.period_name%TYPE;
1157 vl_exception_cat NUMBER := 0;
1158 vl_sgl_acct_num_bak VARCHAR2(25);
1159 vl_tran_type VARCHAR2(25) ;
1160 vl_appor_cursor INTEGER ;
1161 vl_appor_period VARCHAR2(500) ;
1162 vl_appor_select VARCHAR2(2000) ;
1163 vl_catb_program VARCHAR2(25) ;
1164 vl_prn_program VARCHAR2(25) ;
1165 vl_appor_ctr NUMBER ;
1166 vl_ec_retcode NUMBER := 0 ;
1167 vl_ec_errbuf VARCHAR2(400) ;
1168 vl_req_id NUMBER ;
1169 vl_disbursements_flag VARCHAR2(1);
1170 vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%TYPE;
1171
1172 vl_je_source gl_je_headers.je_source%TYPE;
1173 vl_pl_code VARCHAR2(150);
1174 vl_tr_main_acct VARCHAR2(150);
1175 vl_tr_dept_id VARCHAR2(150);
1176 vl_advance_type VARCHAR2(150);
1177 vl_count NUMBER;
1178 l_req_id NUMBER;
1179
1180 l_call_status BOOLEAN ;
1181 rphase VARCHAR2(30);
1182 rstatus VARCHAR2(30);
1183 dphase VARCHAR2(30);
1184 dstatus VARCHAR2(30);
1185 message VARCHAR2(240);
1186
1187 footnote_count NUMBER := 0;
1188
1189 vl_par_pgm_val VARCHAR2(3);
1190 vl_catb_rc_val VARCHAR2(3);
1191 vl_catb_pgm_desc VARCHAR2(25);
1192 vl_catb_exception NUMBER;
1193 vl_prn_rc_val VARCHAR2(3);
1194 vl_prn_pgm_desc VARCHAR2(25);
1195 vl_prn_exception NUMBER;
1196
1197 -- for data access security
1198 das_id NUMBER;
1199 das_where VARCHAR2(600);
1200
1201 CURSOR footnote_select
1202 (p_tsymbol_id fv_treasury_symbols.treasury_symbol_id%TYPE)
1203 IS
1204 SELECT treasury_symbol_id,
1205 sgl_acct_number
1206 FROM fv_facts_temp
1207 WHERE fct_int_record_type = 'BLK_DTL'
1208 AND fct_int_record_category = 'REPORTED_NEW'
1209 AND document_number = 'Y'
1210 AND treasury_symbol_id = p_tsymbol_id ;
1211
1212 BEGIN
1213 l_module_name := g_module_name || 'process_facts_transactions';
1214 vl_old_acct_num := ' ' ;
1215 vl_old_exception := ' ' ;
1216
1217
1218
1219 -- Get all the transaction balances for the combinations that have
1220 -- fund values which are associated with the passed Treasury
1221 -- Symbol. Sum all the amounts and group the data by Account Number
1222 -- and Fund Value.
1223 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1224 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1225 'Selecting FACTS Transactions.....') ;
1226 END IF;
1227 BEGIN
1228 vl_main_cursor := DBMS_SQL.OPEN_CURSOR ;
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 vp_retcode := sqlcode ;
1232 vp_errbuf := sqlerrm ;
1233 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1234 l_module_name||'.vl_main_cursor', vp_errbuf) ;
1235 RETURN;
1236 END ;
1237
1238 IF v_cohort_seg_name IS NOT NULL THEN
1239 v_cohort_select := ', GLCC.' || v_cohort_seg_name ;
1240 ELSE
1241 v_cohort_select := ' ' ;
1242 END IF ;
1243
1244
1245 /* --------- Comented out for bug5065974
1246 -- Get the balances for the Account Number and Fund Value
1247 vl_main_select :=
1248 'SELECT
1249 GLCC.' || v_acc_seg_name ||
1250 ', GLCC.' || v_bal_seg_name ||
1251 ', GLCC.' || v_fyr_segment_name ||
1252 v_cohort_select ||
1253 ' FROM GL_BALANCES GLB,
1254 GL_CODE_COMBINATIONS GLCC,
1255 FV_FUND_PARAMETERS FFP,
1256 FV_TREASURY_SYMBOLS FTS
1257 WHERE FTS.TREASURY_SYMBOL = :treasury_symbol
1258 AND GLB.code_combination_id = GLCC.code_combination_id
1259 AND glb.actual_flag = :actual_flag
1260 AND FTS.TREASURY_SYMBOL_ID = FFP.TREASURY_SYMBOL_ID
1261 AND GLB.TEMPLATE_ID IS NULL
1262 AND GLCC.' || v_bal_seg_name || '= FFP.FUND_VALUE
1263 AND GLB.SET_OF_BOOKS_ID = :set_of_books_id
1264 AND FFP.SET_OF_BOOKS_ID = :set_of_books_id
1265 AND FTS.SET_OF_BOOKS_ID = :set_of_books_id
1266 AND GLB.PERIOD_YEAR = :report_fiscal_yr
1267 AND glb.currency_code = :currency_code
1268 GROUP BY GLCC.' || v_acc_seg_name ||
1269 ', GLCC.' || v_bal_seg_name ||
1270 ', GLCC.' || v_fyr_segment_name ||v_cohort_select ||
1271 ' ORDER BY GLCC.' || v_acc_seg_name ;
1272
1273
1274 ------------------------------------------- > */
1275
1276
1277 -- added for bug 5065974 by ks
1278 -- Get the balances for the Account Number and Fund Value and year begin and current
1279 -- end balances
1280
1281 vl_main_select :=
1282 'SELECT
1283 GLCC.' || v_acc_seg_name ||
1284 ', GLCC.' || v_bal_seg_name ||
1285 ', GLCC.' || v_fyr_segment_name ||
1286 v_cohort_select ||
1287 ',SUM(decode(glb.period_name,:b_period_name,glb.begin_balance_dr - glb.begin_balance_cr,0)) beg_amt
1288 ,SUM(decode(glb.period_name,:e_period_name,glb.begin_balance_dr - glb.begin_balance_cr +
1289 glb.period_net_dr - glb.period_net_cr,0)) end_amount
1290 FROM GL_BALANCES GLB,
1291 GL_CODE_COMBINATIONS GLCC,
1292 FV_FUND_PARAMETERS FFP,
1293 FV_TREASURY_SYMBOLS FTS
1294 WHERE FTS.TREASURY_SYMBOL = :treasury_symbol
1295 AND FTS.SET_OF_BOOKS_ID = :set_of_books_id
1296 AND FFP.TREASURY_SYMBOL_ID = FTS.TREASURY_SYMBOL_ID
1297 AND FFP.SET_OF_BOOKS_ID = :set_of_books_id
1298 AND GLCC.' || v_bal_seg_name || '= FFP.FUND_VALUE
1299 AND GLB.code_combination_id = GLCC.code_combination_id
1300 AND glb.actual_flag = :actual_flag
1301 AND GLB.TEMPLATE_ID IS NULL
1302 AND GLB.ledger_id = :set_of_books_id
1303 AND GLB.PERIOD_NAME in(:b_period_name , :e_period_name)
1304 AND glb.currency_code = :currency_code
1305 GROUP BY GLCC.' || v_acc_seg_name ||
1306 ', GLCC.' || v_bal_seg_name ||
1307 ', GLCC.' || v_fyr_segment_name ||v_cohort_select ||
1308 ' ORDER BY GLCC.' || v_acc_seg_name ;
1309
1310
1311
1312 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1313 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1314 'Main Select: '||vl_main_select);
1315 END IF;
1316
1317 BEGIN
1318 dbms_sql.parse(vl_main_cursor, vl_main_select, DBMS_SQL.V7) ;
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 vp_retcode := sqlcode ;
1322 vp_errbuf := sqlerrm ;
1323 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1324 l_module_name||'.dbms_sql_parse', vp_errbuf) ;
1325 RETURN;
1326 END ;
1327
1328 -- Bind the variables
1329 dbms_sql.bind_variable(vl_main_cursor,':actual_flag', 'A');
1330 dbms_sql.bind_variable(vl_main_cursor,':treasury_symbol', vp_treasury_symbol);
1331 dbms_sql.bind_variable(vl_main_cursor,':set_of_books_id', vp_set_of_books_id);
1332 --dbms_sql.bind_variable(vl_main_cursor,':report_fiscal_yr', vp_report_fiscal_yr);
1333 dbms_sql.bind_variable(vl_main_cursor,':currency_code', vp_currency_code);
1334 dbms_sql.bind_variable(vl_main_cursor,':e_period_name', v_period_name); -- added bug5065974
1335 dbms_sql.bind_variable(vl_main_cursor,':b_period_name', v_begin_period_name); -- added bug5065974
1336
1337 dbms_sql.define_column(vl_main_cursor, 1, vl_acct_num, 25);
1338 dbms_sql.define_column(vl_main_cursor, 2, vl_fund_value, 25);
1339 dbms_sql.define_column(vl_main_cursor, 3, v_fiscal_yr, 25);
1340 -- dbms_sql.bind_variable(vl_main_cursor,':report_fiscal_yr', vp_report_fiscal_yr); -- removed bug 5065974
1341 IF v_cohort_seg_name IS NOT NULL THEN
1342 dbms_sql.define_column(vl_main_cursor, 4, vl_cohort_yr, 25);
1343 dbms_sql.define_column(vl_main_cursor, 5, vb_amount); -- 5065974
1344 dbms_sql.define_column(vl_main_cursor, 6, ve_amount); -- 5065974
1345 else
1346 dbms_sql.define_column(vl_main_cursor, 4, vb_amount); -- 5065974
1347 dbms_sql.define_column(vl_main_cursor, 5, ve_amount); -- 5065974
1348 End if;
1349
1350
1351 BEGIN
1352 vl_exec_ret := dbms_sql.execute(vl_main_cursor);
1353 EXCEPTION
1354 WHEN OTHERS THEN
1355 vp_retcode := sqlcode ;
1356 VP_ERRBUF := sqlerrm ;
1357 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1358 l_module_name||'.dbms_sql_parse', vp_errbuf) ;
1359 RETURN;
1360 END ;
1361
1362 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1363 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1364 'Processing FACTS Transactions starts.....');
1365 END IF;
1366 LOOP
1367 -- This is a Dummy Loop since we have no command in PL/SQL to skip
1368 -- the Loop in the middle and continue with the next iteration.
1369 LOOP /* Dummy */
1370 -- Reseting all the Variables before fetching the Next Row
1371
1372 va_transaction_partner_val := ' ' ;
1373 va_cohort := ' ' ;
1374 va_def_indef_val := ' ' ;
1375 va_appor_cat_b_dtl := ' ' ;
1376 va_appor_cat_b_txt := LPAD(' ',25) ;
1377 va_public_law_code_val := ' ' ;
1378 va_appor_cat_val := ' ' ;
1379 va_authority_type_val := ' ' ;
1380 va_reimburseable_val := ' ' ;
1381 va_bea_category_val := ' ' ;
1382 va_borrowing_source_val := ' ' ;
1383 va_legis_ind_val := ' ' ;
1384 va_balance_type_val := ' ' ;
1385 va_availability_flag := ' ';
1386 va_function_flag := ' ';
1387 va_budget_function := ' ';
1388 va_advance_type_val := ' ';
1389 va_transfer_dept_id := ' ';
1390 va_transfer_main_acct := ' ';
1391 v_dummy_cohort := NULL;
1392 vl_cohort_yr := NULL;
1393 v_cohort_where := NULL;
1394 vl_disbursements_flag := NULL;
1395 va_prn_num := ' ' ;
1396 va_prn_txt := LPAD(' ',25) ;
1397
1398 v_catb_program_value := NULL;
1399 v_prn_program_value := NULL;
1400
1401 vl_main_fetch := dbms_sql.fetch_rows(vl_main_cursor) ;
1402
1403 IF (vl_main_fetch = 0) THEN
1404 IF ( vl_row_count = 0) THEN
1405 -- No Rows to process for FACTS II Report !!
1406 vp_retcode := 1 ;
1407 VP_ERRBUF := 'No Data found for FACTS II process' ;
1408
1409 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1410 '=======================' ||
1411 '===================================================') ;
1412 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1413 vp_errbuf) ;
1414 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1415 '=======================' ||
1416 '===================================================') ;
1417 RETURN;
1418 END IF;
1419 EXIT;
1420 END IF;
1421
1422 -- Increase the counter for number of records
1423 vl_row_count := vl_row_count + 1 ;
1424
1425 -- Fetch the Records into Variables
1426 dbms_sql.column_value(vl_main_cursor, 1, vl_acct_num);
1427 dbms_sql.column_value(vl_main_cursor, 2, vl_fund_value);
1428 dbms_sql.column_value(vl_main_cursor, 3, v_fiscal_yr);
1429
1430 IF v_cohort_seg_name IS NOT NULL THEN
1431 dbms_sql.column_value(vl_main_cursor, 4, vl_cohort_yr);
1432 dbms_sql.column_value(vl_main_cursor, 5, vb_amount);
1433 dbms_sql.column_value(vl_main_cursor, 6, ve_amount);
1434 else
1435 dbms_sql.column_value(vl_main_cursor, 4, vb_amount);
1436 dbms_sql.column_value(vl_main_cursor, 5, ve_amount);
1437 END IF;
1438
1439 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1440 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1441 '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
1442 END IF;
1443
1444 -- FACTS Account Number Validation Process
1445 IF vl_acct_num <> vl_old_acct_num THEN
1446
1447 -- Identify/Validate the SGL parent account number for
1448 -- the account number fetched
1449 get_ussgl_acct_num(vl_acct_num ,
1450 vl_sgl_acct_num ,
1451 v_record_category) ;
1452
1453 IF vp_retcode <> 0 THEN
1454 RETURN ;
1455 END IF ;
1456 -- Store the Acct Number to compare with next fetch
1457 vl_old_acct_num := vl_acct_num ;
1458 vl_old_exception := v_record_category ;
1459 END IF ;
1460
1461 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1462 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1463 'Processing for> Acct-'||vl_acct_num||
1464 ' > USSGL Acct-'||vl_sgl_acct_num||' > Fund-'||vl_fund_value||
1465 ' Cohort >-'||vl_cohort_yr|| ' > Category - ' ||
1466 v_record_category ) ;
1467 END IF ;
1468
1469 -- Cohort where clause is set to a global variable to use in
1470 -- CALC_BALANCE Procedure and futher in the process
1471 IF v_cohort_seg_name IS NOT NULL THEN
1472 v_cohort_where := ' AND GLCC.' || v_cohort_seg_name || ' = ' ||
1473 '''' || vl_cohort_yr || '''' ;
1474 ELSE
1475 v_cohort_where := ' ' ;
1476 END IF ;
1477
1478 -- Account Number Validated and Exceptions are processed
1479 -- proceeding with further processing.
1480 IF v_record_category IS NOT NULL THEN
1481 IF v_record_category IN ('NON_BUD_ACCT', 'NON_FACTSII') THEN
1482 -- No Exception Record Required in Temp Table. Continue with
1483 -- the main loop.
1484 EXIT ;
1485 ELSIF v_record_category IN
1486 ('USSGL_DISABLED', 'BUD_ACCT_NOT_SETUP',
1487 'USSGL_MULTIPLE_PARENTS') THEN
1488 v_sgl_acct_num := vl_sgl_acct_num ;
1489 -- Get the ending balance for the account and create an
1490 -- exception record
1491
1492 /* ------------ > commented out for bug#5065974
1493 calc_balance (vl_fund_value,
1494 vl_acct_num,
1495 v_period_num,
1496 vp_report_fiscal_yr,
1497 'E',
1498 v_fiscal_yr,
1499 vl_amount,
1500 vl_period_activity);
1501 ---v_amount := vl_amount;
1502 ------------------------------------------- */
1503 v_amount := ve_amount; -- bug 5065974
1504
1505 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1506 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1507 'Creating exception: '|| v_record_category);
1508 END IF;
1509 create_facts_record ;
1510 IF vp_retcode <> 0 THEN
1511 RETURN ;
1512 END IF ;
1513 -- Exiting the Process Flow (dummy loop)
1514 -- after creating Exception Record.
1515 EXIT ;
1516 END IF ;
1517 END IF ;
1518
1519
1520 -- Fix for bug 2798371
1521 IF vl_cohort_yr IS NOT NULL THEN
1522 BEGIN
1523 SELECT TO_NUMBER(vl_cohort_yr)
1524 INTO v_dummy_cohort
1525 FROM DUAL;
1526 --Bug#4234865 Changed v_dummy_cohort to vl_cohort_yr
1527 IF LENGTH(vl_cohort_yr) = 1 THEN
1528 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1529 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1530 'Cohort value: '||vl_cohort_yr|| ' is a single digit!');
1531 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1532 'Taking Cohort value from report parameter.');
1533 END IF;
1534 v_dummy_cohort := vp_report_fiscal_yr;
1535 --Bug#4234865 Added the ELSE part
1536 ELSE
1537 v_dummy_cohort := vl_cohort_yr;
1538 END IF;
1539
1540 EXCEPTION WHEN INVALID_NUMBER THEN
1541 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1542 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1543 'Cohort value: '||vl_cohort_yr|| ' is non-numeric!');
1544 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1545 'Taking Cohort value from report parameter.');
1546 END IF;
1547 v_dummy_cohort := vp_report_fiscal_yr;
1548 END;
1549 END IF;
1550
1551 va_cohort := NVL(LPAD(substr(v_dummy_cohort, LENGTH(v_dummy_cohort)-1, 2),
1552 2, ' '), ' ') ;
1553
1554 v_year_budget_auth := ' ';
1555 BEGIN
1556 SELECT disbursements_flag
1557 INTO vl_disbursements_flag
1558 FROM fv_facts_ussgl_accounts
1559 WHERE ussgl_account = vl_sgl_acct_num;
1560
1561 IF (v_time_frame = 'NO_YEAR'
1562 AND v_financing_acct = 'N'
1563 AND vl_disbursements_flag = 'Y')
1564 THEN
1565 BEGIN
1566 SELECT fyr_segment_value
1567 INTO vl_fyr_segment_value
1568 FROM fv_pya_fiscalyear_map
1569 WHERE period_year = vp_report_fiscal_yr
1570 AND set_of_books_id = vp_set_of_books_id;
1571 EXCEPTION
1572 WHEN NO_DATA_FOUND THEN
1573 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1574 'Please set up the Budget Fiscal Year Segment Mapping for period year '||vp_report_fiscal_yr);
1575 RAISE;
1576 END;
1577
1578 IF vl_fyr_segment_value IS NOT NULL THEN
1579 IF vl_fyr_segment_value = v_fiscal_yr THEN
1580 v_year_budget_auth := 'NEW';
1581 ELSE
1582 v_year_budget_auth := 'BAL';
1583 END IF;
1584 END IF;
1585 END IF;
1586
1587 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1588 THEN
1589 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1590 'Year bud auth: '||v_year_budget_auth);
1591 END IF;
1592 EXCEPTION WHEN OTHERS THEN
1593 vp_retcode := sqlcode;
1594 vp_errbuf := 'Error when processing v_year_budget_auth: '||
1595 sqlerrm;
1596 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1597 l_module_name||'.exception_1',vp_errbuf);
1598 RETURN;
1599 END;
1600
1601 -- Acct Number Passed Validations. Load FACTS attributes
1602 -- based on the flag v_acct_attr_flag
1603 -- Move the account number into global variable
1604 v_sgl_acct_num := vl_sgl_acct_num ;
1605 --v_amount := vl_amount ;
1606 v_amount := ve_amount ;
1607
1608 IF v_acct_attr_flag = 'Y' THEN
1609 load_facts_attributes (vl_acct_num, vl_fund_value,ve_amount) ;
1610 ELSE
1611 load_facts_attributes (vl_sgl_acct_num, vl_fund_value,ve_amount) ;
1612 END IF ;
1613 IF vp_retcode <> 0 THEN
1614 RETURN ;
1615 END IF ;
1616
1617 -- v_tbal_indicator set to 'F' to indicate FACTS transaction
1618 v_tbal_indicator := 'F' ;
1619
1620 -------------- Legislation Indicator Processing Starts ----------------
1621 IF va_legis_Ind_flag = 'Y' OR va_public_law_code_flag = 'Y'
1622 OR va_advance_flag = 'Y' OR va_transfer_ind = 'Y' THEN
1623 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1624 IF (va_legis_ind_flag ='Y' AND va_public_law_code_flag ='Y') THEN
1625 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1626 THEN
1627 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1628 ' ++ Leg Ind and P.Law Processing ++') ;
1629 END IF;
1630 ELSIF (va_legis_ind_flag = 'Y' AND va_public_law_code_flag = 'N')
1631 THEN
1632 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1633 THEN
1634 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1635 ' ++ Leg Ind Processing ++') ;
1636 END IF;
1637 ELSIF va_legis_ind_flag = 'N' AND va_public_law_code_flag = 'Y'
1638 THEN
1639 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1640 THEN
1641 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1642 ' ++ Pub Law Processing ++') ;
1643 END IF;
1644 END IF ;
1645
1646 IF va_advance_flag = 'Y' THEN
1647 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1648 THEN
1649 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1650 '++ Advance Type Processing ++') ;
1651 END IF;
1652 END IF;
1653 IF va_transfer_ind = 'Y' THEN
1654 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1655 THEN
1656 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1657 '++ Transfer Acct Processing ++') ;
1658 END IF;
1659 END IF;
1660 END IF ;
1661 BEGIN -- Legislative processing
1662 -- Calculate the Beginning balance for the current account
1663 -- and fund value combination and create record in temp
1664 -- table for Legislative Indicator 'A' and Balance Type 'B'
1665 -- Default Public Law Code values for beginning and
1666 -- ending balances
1667 IF va_public_law_code_flag = 'Y' THEN
1668 --Bug#3219532
1669 --va_public_law_code_val := '000-000' ;
1670 va_public_law_code_val := ' ' ;
1671 END IF ;
1672
1673 -- Legislative Ind values for beginning and ending balances
1674 IF va_legis_ind_flag = 'Y' THEN
1675 va_legis_ind_val := 'A' ;
1676 END IF ;
1677
1678 -- Advance Type values for beginning and ending balances
1679 IF va_advance_flag = 'Y' THEN
1680 va_advance_type_val := 'X' ;
1681 END IF ;
1682
1683 -- Transfer values for beginning and ending balances
1684 IF va_transfer_ind = 'Y' THEN
1685 --Bug#3219532
1686 --va_transfer_dept_id := '00' ;
1687 --va_transfer_main_acct := '0000' ;
1688 va_transfer_dept_id := ' ' ;
1689 va_transfer_main_acct := ' ' ;
1690 END IF ;
1691
1692 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1693 THEN
1694 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1695 'Period number '||v_begin_period_num) ;
1696 END IF;
1697
1698 /* ------- bug 5065974 -------------------
1699 calc_balance (vl_fund_value,
1700 vl_acct_num,
1701 v_begin_period_num,
1702 vp_report_fiscal_yr,
1703 'B',
1704 v_fiscal_yr,
1705 v_begin_amount,
1706 vl_period_activity);
1707 ------------------------------ > */
1708
1709 IF vp_retcode <> 0 THEN
1710 RETURN ;
1711 END IF ;
1712
1713 vb_balance_amount := vb_amount;
1714 FOR begin_balance_rec IN (SELECT SUM(NVL(f.ending_balance_dr, 0) - NVL(f.ending_balance_cr, 0)) amount,
1715 f.public_law,
1716 f.advance_flag,
1717 f.transfer_dept_id,
1718 f.transfer_main_acct
1719 FROM fv_factsii_ending_balances f
1720 WHERE f.set_of_books_id = vp_set_of_books_id
1721 AND f.fiscal_year = vp_report_fiscal_yr-1
1722 AND f.account = vl_acct_num
1723 AND f.fund = vl_fund_value
1724 AND f.fyr = v_fiscal_yr
1725 AND NVL(f.cohort, '-1') = DECODE (v_cohort_seg_name, NULL, NVL(f.cohort,'-1'), vl_cohort_yr)
1726 GROUP BY f.public_law,
1727 f.advance_flag,
1728 f.transfer_dept_id,
1729 f.transfer_main_acct) LOOP
1730 v_amount := begin_balance_rec.amount;
1731 vb_balance_amount := vb_balance_amount - v_amount;
1732 v_record_category := 'REPORTED';
1733 va_public_law_code_val := RTRIM(begin_balance_rec.public_law);
1734 va_advance_type_val := begin_balance_rec.advance_flag;
1735 va_transfer_dept_id := begin_balance_rec.transfer_dept_id;
1736 va_transfer_main_acct := begin_balance_rec.transfer_main_acct;
1737 v_period_activity := 0;
1738 IF (va_public_law_code_val IS NULL) THEN
1739 vl_sgl_acct_num_bak := v_sgl_acct_num;
1740 v_sgl_acct_num := vl_acct_num ;
1741 v_record_category := 'PUBLIC_LAW_NOT_DEFINED';
1742 create_facts_record ;
1743
1744 -- added KS
1745 IF (va_balance_type_flag IN ('B' , 'S') ) THEN
1746 va_balance_type_val := 'B' ;
1747 elsif (va_balance_type_flag IN ('E' , 'S') ) THEN
1748 va_balance_type_val := 'E' ;
1749 END IF;
1750
1751 v_record_category := 'REPORTED';
1752 v_sgl_acct_num := vl_sgl_acct_num_bak ;
1753 vl_exception_cat := 1;
1754 ELSE
1755 IF (va_balance_type_flag IN ('B' , 'S') ) THEN
1756 va_balance_type_val := 'B' ;
1757 create_facts_record;
1758 END IF;
1759 IF (va_balance_type_flag IN ('E' , 'S') ) THEN
1760 va_balance_type_val := 'E' ;
1761 create_facts_record;
1762 END IF;
1763 END IF;
1764 END LOOP;
1765
1766 IF (vb_balance_amount <> 0) THEN
1767 va_public_law_code_val := NULL;
1768 va_legis_ind_val := NULL;
1769 va_advance_type_val := NULL;
1770 va_transfer_dept_id := NULL;
1771 va_transfer_main_acct := NULL;
1772
1773 IF va_public_law_code_flag = 'Y' THEN
1774 va_public_law_code_val := ' ' ;
1775 END IF ;
1776
1777 IF va_legis_ind_flag = 'Y' THEN
1778 va_legis_ind_val := 'A' ;
1779 END IF ;
1780
1781 IF va_advance_flag = 'Y' THEN
1782 va_advance_type_val := 'X' ;
1783 END IF ;
1784
1785 IF va_transfer_ind = 'Y' THEN
1786 va_transfer_dept_id := ' ' ;
1787 va_transfer_main_acct := ' ' ;
1788 END IF ;
1789 IF (va_balance_type_flag IN ('B' , 'S') ) THEN
1790
1791 -- Creating FACTS Record with Beginning Balance
1792 va_balance_type_val := 'B' ;
1793 v_record_category := 'REPORTED' ;
1794 --v_amount := v_begin_amount ; -- bug 5065974
1795 v_amount := vb_balance_amount ; -- bug 5065974
1796 v_period_activity := 0 ;
1797 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1798 THEN
1799 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1800 'Begin Balance(' || va_balance_type_flag || ') >>>> - ' || to_char(v_amount)) ;
1801 END IF ;
1802
1803 create_facts_record ;
1804 IF vp_retcode <> 0 THEN
1805 RETURN ;
1806 END IF ;
1807 END IF;
1808
1809 IF (va_balance_type_flag IN ('E' , 'S') ) THEN
1810 -- Creating FACTS Record with Ending Balance
1811 va_balance_type_val := 'E' ;
1812 v_record_category := 'REPORTED' ;
1813 --v_amount := v_begin_amount ;
1814 v_amount := vb_balance_amount ;
1815 v_period_activity := 0 ;
1816 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1817 THEN
1818 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Begin Balance(' ||
1819 va_balance_type_flag || ') >>>> - ' || to_char(v_amount)) ;
1820 END IF ;
1821
1822 create_facts_record ;
1823 IF vp_retcode <> 0 THEN
1824 RETURN ;
1825 END IF ;
1826 END IF;
1827 END IF;
1828
1829 -- Select the records for other Legislative Indicator values,
1830 -- derived from Budget Execution tables and store them in a
1831 -- cursor. Then roll them up and insert the summarized record
1832 -- into the temp table.
1833 BEGIN
1834 vl_legis_cursor := DBMS_SQL.OPEN_CURSOR ;
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 vp_retcode := sqlcode ;
1838 VP_ERRBUF := sqlerrm ;
1839 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1840 l_module_name||'.vl_legis_cursor', vp_errbuf) ;
1841 RETURN ;
1842 END ;
1843
1844 IF va_pl_code_col IS NOT NULL THEN
1845 va_pl_code_col := ', gjl.'||va_pl_code_col;
1846 END IF;
1847
1848 IF va_tr_main_acct_col IS NOT NULL THEN
1849 va_tr_main_acct_col := ', gjl.'||va_tr_main_acct_col;
1850 END IF;
1851
1852 IF va_tr_dept_id_col IS NOT NULL THEN
1853 va_tr_dept_id_col := ', gjl.'||va_tr_dept_id_col;
1854 END IF;
1855
1856 IF va_advance_type_col IS NOT NULL THEN
1857 va_advance_type_col := ', gjl.'||va_advance_type_col;
1858 END IF;
1859
1860 -- Data Access Security:
1861 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
1862 das_where := gl_access_set_security_pkg.get_security_clause
1863 (das_id,
1864 gl_access_set_security_pkg.READ_ONLY_ACCESS,
1865 gl_access_set_security_pkg.CHECK_LEDGER_ID,
1866 to_char(vp_set_of_books_id), null,
1867 gl_access_set_security_pkg.CHECK_SEGVALS,
1868 null, 'glcc', null);
1869 -- Get the transactions for the account Number and Fund (and
1870 -- cohort segment, if required)
1871 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1872 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1873 'vl_legis_Select') ;
1874 END IF;
1875
1876 vl_legis_select :=
1877 'SELECT gjl.reference_1,
1878 NVL(gjl.entered_dr, 0) - NVL(gjl.entered_cr, 0) amout,
1879 gjl.effective_date , gjl.period_name, gjh.je_source '||
1880 va_pl_code_col || va_tr_main_acct_col || va_tr_dept_id_col ||
1881 va_advance_type_col ||
1882 ' FROM gl_je_lines gjl,
1883 gl_code_combinations glcc,
1884 gl_je_headers gjh
1885 WHERE gjl.code_combination_id = glcc.code_combination_id
1886 AND gjl.status = :je_status
1887 AND gjl.ledger_id = :set_of_books_id
1888 AND glcc.'||v_acc_seg_name|| ' = :acct_num
1889 AND NVL(gjl.entered_dr, 0) - NVL(gjl.entered_cr, 0) <> 0
1890 AND glcc.'||v_bal_seg_name||' = :fund_value '||
1891 v_cohort_where ||
1892 ' AND glcc.'||v_fyr_segment_name||' = :fiscal_yr
1893 AND gjh.je_header_id = gjl.je_header_id
1894 AND NVL(gjh.je_from_sla_flag, ''N'') = ''N''
1895 AND gjh.currency_code = :currency_code ';
1896
1897 vl_legis_select :=
1898 vl_legis_select || ' AND gjl.period_name in ' ||
1899 ' ( SELECT period_name
1900 FROM gl_period_statuses
1901 WHERE application_id = 101
1902 AND ledger_id = :set_of_books_id
1903 AND period_num BETWEEN :begin_period_num AND :period_num
1904 AND period_year = :report_fiscal_yr) ' ;
1905
1906 IF (das_where IS NOT NULL) THEN
1907 vl_legis_select := vl_legis_select || 'AND ' || das_where;
1908 END IF;
1909
1910 vl_legis_select := vl_legis_select || ' UNION ALL ';
1911 -- Used TO_CHAR for bug 6332685
1912 vl_legis_select := vl_legis_select ||
1913 'SELECT TO_CHAR(SOURCE_DISTRIBUTION_ID_NUM_1),
1914 NVL(xl.accounted_dr, 0) - NVL(xl.accounted_cr, 0) amount,
1915 gjl.effective_date , gjl.period_name, gjh.je_source '||
1916 va_pl_code_col || va_tr_main_acct_col || va_tr_dept_id_col ||
1917 va_advance_type_col ||
1918 ' FROM gl_je_lines gjl,
1919 gl_code_combinations glcc,
1920 gl_je_headers gjh,
1921 gl_import_references gli,
1922 xla_distribution_links xdl,
1923 xla_ae_lines xl
1924 WHERE gjl.code_combination_id = glcc.code_combination_id
1925 AND gjl.status = :je_status
1926 AND NVL(gjh.je_from_sla_flag, ''N'') = ''Y''
1927 AND gjl.ledger_id = :set_of_books_id
1928 AND glcc.'||v_acc_seg_name|| ' = :acct_num
1929 AND NVL(xl.accounted_dr, 0) - NVL(xl.accounted_cr, 0) <> 0
1930 AND glcc.'||v_bal_seg_name||' = :fund_value '||
1931 v_cohort_where ||
1932 ' AND glcc.'||v_fyr_segment_name||' = :fiscal_yr
1933 AND gjh.je_header_id = gjl.je_header_id
1934 AND gjh.currency_code = :currency_code
1935 and gli.je_batch_id = gjh.je_batch_id
1936 and gli.je_header_id = gjh.je_header_id
1937 and gli.je_line_num = gjl.je_line_num
1938 AND xl.gl_sl_link_id = gli.gl_sl_link_id
1939 AND xdl.ae_header_id = xl.ae_header_id
1940 AND xdl.ae_line_num = xl.ae_line_num ';
1941
1942 --AND xl.code_combination_id = glcc.code_combination_id
1943 vl_legis_select :=
1944 vl_legis_select || ' AND gjl.period_name in ' ||
1945 ' ( SELECT period_name
1946 FROM gl_period_statuses
1947 WHERE application_id = 101
1948 AND ledger_id = :set_of_books_id
1949 AND period_num BETWEEN :begin_period_num AND :period_num
1950 AND period_year = :report_fiscal_yr) ' ;
1951
1952 IF (das_where IS NOT NULL) THEN
1953 vl_legis_select := vl_legis_select || 'AND ' || das_where;
1954 END IF;
1955
1956 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1957 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1958 vl_legis_select) ;
1959 END IF;
1960
1961 BEGIN
1962 dbms_sql.parse(vl_legis_cursor,vl_legis_select,DBMS_SQL.V7);
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 vp_retcode := sqlcode ;
1966 vp_errbuf := sqlerrm ;
1967 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1968 l_module_name||'.dbms_sql_parse_vl_legis_cursor',
1969 vp_errbuf) ;
1970 RETURN ;
1971 END ;
1972
1973
1974 -- Bind the variables
1975 dbms_sql.bind_variable(vl_legis_cursor,':je_status', 'P');
1976 dbms_sql.bind_variable(vl_legis_cursor,':set_of_books_id',
1977 vp_set_of_books_id);
1978 dbms_sql.bind_variable(vl_legis_cursor,':acct_num',
1979 vl_acct_num);
1980 dbms_sql.bind_variable(vl_legis_cursor,':fund_value',
1981 vl_fund_value);
1982 dbms_sql.bind_variable(vl_legis_cursor,':fiscal_yr', v_fiscal_yr);
1983 dbms_sql.bind_variable(vl_legis_cursor,':currency_code',
1984 vp_currency_code);
1985 dbms_sql.bind_variable(vl_legis_cursor,':begin_period_num',
1986 v_begin_period_num);
1987 dbms_sql.bind_variable(vl_legis_cursor,':period_num', v_period_num);
1988 dbms_sql.bind_variable(vl_legis_cursor,':report_fiscal_yr',
1989 vp_report_fiscal_yr);
1990
1991 vl_count := 0;
1992
1993 dbms_sql.define_column(vl_legis_cursor, 1, vl_legis_ref, 240);
1994 dbms_sql.define_column(vl_legis_cursor, 2, vl_legis_amount );
1995 dbms_sql.define_column(vl_legis_cursor, 3, vl_effective_date );
1996 dbms_sql.define_column(vl_legis_cursor, 4, vl_period_name, 15 );
1997 dbms_sql.define_column(vl_legis_cursor, 5, vl_je_source, 25 );
1998
1999 vl_count := 6;
2000
2001 IF va_pl_code_col IS NOT NULL THEN
2002 dbms_sql.define_column(vl_legis_cursor, vl_count, vl_pl_code, 150);
2003 vl_count := vl_count + 1;
2004 END IF;
2005
2006 IF va_tr_main_acct_col IS NOT NULL THEN
2007 dbms_sql.define_column(vl_legis_cursor, vl_count,
2008 vl_tr_main_acct, 150);
2009 vl_count := vl_count + 1;
2010 END IF;
2011
2012 IF va_tr_dept_id_col IS NOT NULL THEN
2013 dbms_sql.define_column(vl_legis_cursor, vl_count,
2014 vl_tr_dept_id, 150);
2015 vl_count := vl_count + 1;
2016 END IF;
2017
2018 IF va_advance_type_col IS NOT NULL THEN
2019 dbms_sql.define_column(vl_legis_cursor, vl_count,
2020 vl_advance_type, 150);
2021 END IF;
2022
2023 BEGIN
2024 vl_exec_ret := dbms_sql.execute(vl_legis_cursor);
2025 EXCEPTION
2026 WHEN OTHERS THEN
2027 vp_retcode := sqlcode ;
2028 vp_errbuf := sqlerrm ;
2029 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2030 l_module_name||'.dbms_sql_execute_vl_legis_cursor',
2031 vp_errbuf) ;
2032 RETURN ;
2033 END ;
2034
2035 LOOP
2036 vl_exception_cat := 0;
2037 IF dbms_sql.fetch_rows(vl_legis_cursor) = 0 THEN
2038 EXIT;
2039 ELSE
2040
2041 vl_count := 0;
2042 -- Fetch the Records into Variables
2043 dbms_sql.column_value(vl_legis_cursor,1,vl_legis_ref);
2044 dbms_sql.column_value(vl_legis_cursor,2,vl_legis_amount);
2045 dbms_sql.column_value(vl_legis_cursor,3,vl_effective_date);
2046 dbms_sql.column_value(vl_legis_cursor,4,vl_period_name);
2047 dbms_sql.column_value(vl_legis_cursor,5,vl_je_source);
2048
2049 --fnd_file.put_line(fnd_file.log , 'vl_legis_ref ' || vl_legis_ref);
2050 --fnd_file.put_line(fnd_file.log , 'vl_je_source ' || vl_je_source);
2051 --fnd_file.put_line(fnd_file.log , 'vl_legis_amount ' || vl_legis_amount);
2052 vl_count := 6;
2053
2054 IF va_pl_code_col IS NOT NULL THEN
2055 dbms_sql.column_value(vl_legis_cursor,
2056 vl_count, vl_pl_code);
2057 vl_count := vl_count + 1;
2058 END IF;
2059
2060 IF va_tr_main_acct_col IS NOT NULL THEN
2061 dbms_sql.column_value(vl_legis_cursor, vl_count,
2062 vl_tr_main_acct);
2063 vl_count := vl_count + 1;
2064 END IF;
2065
2066 IF va_tr_dept_id_col IS NOT NULL THEN
2067 dbms_sql.column_value(vl_legis_cursor, vl_count,
2068 vl_tr_dept_id);
2069 vl_count := vl_count + 1;
2070 END IF;
2071
2072 IF va_advance_type_col IS NOT NULL THEN
2073 dbms_sql.column_value(vl_legis_cursor, vl_count,
2074 vl_advance_type);
2075 END IF;
2076
2077 IF ( FND_LOG.LEVEL_STATEMENT >=
2078 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2079 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2080 l_module_name,'Ref 1-'||NVL(vl_legis_ref,'Ref Null')) ;
2081 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2082 l_module_name,'P Law-'||
2083 NVL(va_public_law_code_val, 'P Law Null')) ;
2084 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2085 l_module_name,'Amt:'||
2086 NVL(TO_CHAR(vl_legis_amount), 'Amt Null')) ;
2087 END IF ;
2088
2089 END IF;
2090 --------------------------------------------------------------------------------
2091 ----------- Public Law Specific Processing -----------+
2092 -- If the public law code is required then check the journal source.
2093 -- If the journal source is YE Close and Budgetary Transaction then
2094 -- get the public law code from BE details table. If the journal
2095 -- source is not these two, then get the public law code from the
2096 -- corresponding attribute field on the je line.
2097
2098 IF va_public_law_code_flag = 'N' THEN
2099 va_public_law_code_val := ' ' ;
2100 ELSE
2101
2102 -- added KS
2103 IF (va_balance_type_flag IN ('B' , 'S') ) THEN
2104 va_balance_type_val := 'B' ;
2105 elsif (va_balance_type_flag IN ('E' , 'S') ) THEN
2106 va_balance_type_val := 'E' ;
2107 END IF;
2108
2109 IF vl_legis_ref IS NOT NULL THEN
2110
2111 BEGIN
2112 SELECT public_law_code
2113 INTO va_public_law_code_val
2114 FROM fv_be_trx_dtls
2115 WHERE transaction_id = vl_legis_ref
2116 AND set_of_books_id = vp_set_of_books_id ;
2117
2118 IF va_public_law_code_val is NULL THEN
2119 -- Create Exception
2120 v_amount := vl_legis_amount ;
2121 v_period_activity := vl_legis_amount ;
2122 vl_sgl_acct_num_bak := v_sgl_acct_num;
2123 v_sgl_acct_num := vl_acct_num ;
2124 va_public_law_code_val := NULL;
2125 v_record_category := 'PUBLIC_LAW_NOT_DEFINED' ;
2126 IF ( FND_LOG.LEVEL_STATEMENT >=
2127 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2128 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2129 l_module_name,'Creating exception :'||
2130 v_record_category);
2131 END IF;
2132
2133 create_facts_record ;
2134 v_record_category := 'REPORTED';
2135 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2136 vl_exception_cat := 1;
2137 END IF ;
2138
2139 EXCEPTION
2140 WHEN NO_DATA_FOUND THEN
2141 v_amount := vl_legis_amount ;
2142 v_period_activity := vl_legis_amount ;
2143 vl_sgl_acct_num_bak := v_sgl_acct_num;
2144 v_sgl_acct_num := vl_acct_num ;
2145 va_public_law_code_val := NULL;
2146 v_record_category := 'PUBLIC_LAW_NOT_DEFINED' ;
2147 IF ( FND_LOG.LEVEL_STATEMENT >=
2148 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2149 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2150 l_module_name,'Creating exception :'||
2151 v_record_category);
2152 END IF;
2153 create_facts_record ;
2154 v_record_category := 'REPORTED';
2155 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2156 vl_exception_cat := 1;
2157 WHEN INVALID_NUMBER THEN
2158 v_amount := vl_legis_amount ;
2159 v_period_activity := vl_legis_amount ;
2160 vl_sgl_acct_num_bak := v_sgl_acct_num;
2161 v_sgl_acct_num := vl_acct_num ;
2162 va_public_law_code_val := NULL;
2163 v_record_category := 'PUBLIC_LAW_NOT_DEFINED' ;
2164 IF ( FND_LOG.LEVEL_STATEMENT >=
2165 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2166 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2167 l_module_name,'Creating exception :'||
2168 v_record_category);
2169 END IF;
2170 create_facts_record ;
2171 v_record_category := 'REPORTED';
2172 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2173 vl_exception_cat := 1;
2174 END ;
2175
2176 ELSE -- vl_legis_ref IS NULL
2177
2178 -- If an attribute column is setup but
2179 -- the journal line does not contain a value, then
2180 -- create an exception.
2181 IF va_pl_code_col IS NOT NULL THEN
2182 IF vl_pl_code IS NULL THEN
2183 v_amount := vl_legis_amount ;
2184 v_period_activity := vl_legis_amount ;
2185 vl_sgl_acct_num_bak := v_sgl_acct_num;
2186 v_sgl_acct_num := vl_acct_num ;
2187 va_public_law_code_val := NULL;
2188 v_record_category := 'PUBLIC_LAW_NOT_DEFINED' ;
2189 IF ( FND_LOG.LEVEL_STATEMENT >=
2190 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2191 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2192 l_module_name,'Creating exception :'||
2193 v_record_category);
2194 END IF;
2195 create_facts_record ;
2196 v_record_category := 'REPORTED';
2197 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2198 vl_exception_cat := 1;
2199 ELSE
2200 va_public_law_code_val := SUBSTR(vl_pl_code,1,7);
2201 END IF;
2202 END IF;
2203 END IF;
2204
2205 END IF;
2206 --------------------------------------------------------------------------------
2207 --- Legislative Indicator specific processing --+
2208 IF va_legis_ind_flag = 'Y' THEN
2209 -- Get the Transaction Type Value
2210 BEGIN
2211 SELECT transaction_type_id
2212 INTO vl_tran_type
2213 FROM fv_be_trx_dtls
2214 WHERE transaction_id = vl_legis_ref
2215 AND set_of_books_id = vp_set_of_books_id ;
2216 -- Get the Legislation Indicator Value
2217 -- from fv_be_transaction_types table.
2218 SELECT legislative_indicator
2219 INTO va_legis_ind_val
2220 FROM fv_be_transaction_types
2221 WHERE be_tt_id = vl_tran_type
2222 AND set_of_books_id = vp_set_of_books_id ;
2223
2224 IF ( FND_LOG.LEVEL_STATEMENT >=
2225 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2226 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2227 l_module_name,
2228 'Legislation Indicator-'||
2229 NVL(va_legis_ind_val,'Legis Null')) ;
2230 END IF ;
2231 EXCEPTION
2232 WHEN NO_DATA_FOUND THEN
2233 -- Cannot derive legislation indicator. Create
2234 -- Exception Record
2235 vl_sgl_acct_num_bak := v_sgl_acct_num;
2236 v_sgl_acct_num := vl_sgl_acct_num ;
2237 v_amount := vl_legis_amount ;
2238
2239 IF NOT v_year_gtn2001 THEN
2240 v_record_category := 'NO_LEGIS_INDICATOR' ;
2241 IF ( FND_LOG.LEVEL_STATEMENT >=
2242 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2243 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2244 l_module_name,
2245 'Creating Exception: '||v_record_category) ;
2246 END IF ;
2247 create_facts_record ;
2248 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2249 END IF;
2250 v_record_category := 'REPORTED';
2251 -- Also set the Legislation Indicator to
2252 -- default value 'A'
2253 va_legis_ind_val := 'A' ;
2254 vl_exception_cat := 1;
2255 WHEN INVALID_NUMBER THEN
2256 -- Cannot derive legislation indicator. Create
2257 -- Exception Record
2258
2259 vl_sgl_acct_num_bak := v_sgl_acct_num;
2260 v_sgl_acct_num := vl_sgl_acct_num ;
2261 v_amount := vl_legis_amount ;
2262 IF NOT v_year_gtn2001 THEN
2263 v_record_category := 'NO_LEGIS_INDICATOR' ;
2264 IF ( FND_LOG.LEVEL_STATEMENT >=
2265 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2266 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2267 l_module_name,
2268 'Creating Exception: '||v_record_category) ;
2269 END IF ;
2270 create_facts_record ;
2271 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2272 END IF;
2273 v_record_category := 'REPORTED';
2274 -- Also set the Legislation Indicator to
2275 -- default value 'A'
2276 va_legis_ind_val := 'A' ;
2277 vl_exception_cat := 1;
2278 END ;
2279 END IF;
2280 --------------------------------------------------------------------------------
2281 --- Advance Type specific processing --+
2282 -- If the advance type is required then check the journal source.
2283 -- If the journal source is YE Close and Budgetary Transaction
2284 -- then get the advance type from BE details table. If the
2285 -- journal source is not these two, then get the advance type
2286 -- from the corresponding attribute fields on the je line.
2287 IF va_advance_flag = 'Y' THEN
2288
2289 IF vl_legis_ref IS NOT NULL THEN
2290 BEGIN
2291 SELECT advance_type
2292 INTO va_advance_type_val
2293 FROM fv_be_trx_dtls
2294 WHERE transaction_id = vl_legis_ref
2295 AND set_of_books_id = vp_set_of_books_id ;
2296 IF ( FND_LOG.LEVEL_STATEMENT >=
2297 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2298 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2299 l_module_name,'Advance Type - '||
2300 NVL(va_advance_type_val, 'Advance Type Null')) ;
2301 END IF ;
2302 -- If the advance_type value is null then set it to 'X'
2303 IF va_advance_type_val IS NULL THEN
2304 va_advance_type_val := 'X';
2305 END IF;
2306 EXCEPTION
2307 WHEN OTHERS THEN
2308 va_advance_type_val := 'X';
2309 END;
2310
2311 ELSE
2312 -- vl_legis_ref is null
2313 -- If an attribute column is not set up for advance type
2314 -- then report blank. If a column is setup but
2315 -- the journal line does not contain a value, then
2316 -- report 'X'
2317 IF va_advance_type_col IS NULL THEN
2318 va_advance_type_val := 'X';
2319 ELSE
2320 IF vl_advance_type IS NULL THEN
2321 va_advance_type_val := 'X';
2322 ELSE
2323 va_advance_type_val := SUBSTR(vl_advance_type,1,1);
2324 END IF;
2325 END IF;
2326
2327 END IF;
2328
2329 END IF;
2330 --------------------------------------------------------------------------------
2331 -- Transfer Acct specific processing --+
2332 -- If the transfer info is required then check the journal source.
2333 -- If the journal source is YE Close and Budgetary Transaction then
2334 -- get the transfer info from BE details table. If the journal
2335 -- source is not these two, then get the transfer info from the
2336 -- corresponding attribute fields on the je line.
2337 IF va_transfer_ind = 'Y' THEN
2338
2339 IF vl_legis_ref IS NOT NULL THEN
2340 BEGIN
2341 SELECT dept_id,
2342 main_account
2343 INTO va_transfer_dept_id,
2344 va_transfer_main_acct
2345 FROM fv_be_trx_dtls
2346 WHERE transaction_id = vl_legis_ref
2347 AND set_of_books_id = vp_set_of_books_id ;
2348 IF ( FND_LOG.LEVEL_STATEMENT >=
2349 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2350 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2351 l_module_name,'Transfer Dept ID- '||
2352 NVL(va_transfer_dept_id, 'Transfer Dept ID Null'));
2353 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2354 l_module_name,'Transfer Main Acct-'||
2355 NVL(va_transfer_main_acct,
2356 'Transfer Main Acct Null'));
2357 END IF ;
2358 -- If the Transfer values are null then set default
2359 -- values Since both dept_id and main_acct are null
2360 -- or both have values, test if one of them is null
2361 IF va_transfer_dept_id IS NULL THEN
2362 v_amount := vl_legis_amount ;
2363 v_period_activity := vl_legis_amount ;
2364 vl_sgl_acct_num_bak := v_sgl_acct_num;
2365 v_sgl_acct_num := vl_acct_num ;
2366 va_transfer_dept_id := NULL;
2367 va_transfer_main_acct := NULL;
2368 v_record_category := 'TRANSFER_DTLS_NOT_DEFINED' ;
2369 IF ( FND_LOG.LEVEL_STATEMENT >=
2370 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2371 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2372 l_module_name,'Creating exception :'||
2373 v_record_category);
2374 END IF;
2375 create_facts_record ;
2376 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2377 vl_exception_cat := 1;
2378 END IF;
2379 EXCEPTION
2380 WHEN OTHERS THEN
2381 v_amount := vl_legis_amount ;
2382 v_period_activity := vl_legis_amount ;
2383 vl_sgl_acct_num_bak := v_sgl_acct_num;
2384 v_sgl_acct_num := vl_acct_num ;
2385 va_transfer_dept_id := NULL;
2386 va_transfer_main_acct := NULL;
2387 v_record_category := 'TRANSFER_INFO_NOT_DEFINED' ;
2388 IF ( FND_LOG.LEVEL_STATEMENT >=
2389 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2390 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2391 l_module_name,'Creating exception :'||
2392 v_record_category);
2393 END IF;
2394 create_facts_record ;
2395 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2396 vl_exception_cat := 1;
2397 END;
2398
2399 ELSE
2400 -- vl_legis_ref is null
2401 -- If an attribute column is setup but
2402 -- the journal line does not contain a value, then
2403 -- create an exception.
2404 IF va_tr_main_acct_col IS NOT NULL THEN
2405 IF (vl_tr_main_acct IS NULL OR
2406 vl_tr_dept_id IS NULL)
2407 THEN
2408 v_amount := vl_legis_amount ;
2409 v_period_activity := vl_legis_amount ;
2410 vl_sgl_acct_num_bak := v_sgl_acct_num;
2411 v_sgl_acct_num := vl_acct_num ;
2412 va_transfer_main_acct := NULL;
2413 va_transfer_dept_id := NULL;
2414 v_record_category := 'TRANSFER_INFO_NOT_DEFINED' ;
2415 IF ( FND_LOG.LEVEL_STATEMENT >=
2416 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2417 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2418 l_module_name,'Creating exception :'||
2419 v_record_category);
2420 END IF;
2421 create_facts_record ;
2422 v_record_category := 'REPORTED';
2423 v_sgl_acct_num := vl_sgl_acct_num_bak ;
2424 vl_exception_cat := 1;
2425 ELSE
2426 va_transfer_main_acct := SUBSTR(vl_tr_main_acct,1,4);
2427 va_transfer_dept_id := SUBSTR(vl_tr_dept_id,1,2);
2428 END IF;
2429 END IF;
2430 END IF;
2431
2432 END IF;
2433 --------------------------------------------------------------------------------
2434 -- Update the Temp table
2435 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2436 THEN
2437 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2438 l_module_name,' Acct - '||vl_acct_num) ;
2439 END IF;
2440
2441 IF vl_exception_cat = 0 THEN
2442 v_record_category := 'REPORTED' ;
2443 v_amount := vl_legis_amount ;
2444 v_period_activity := vl_legis_amount;
2445 IF ( FND_LOG.LEVEL_STATEMENT >=
2446 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2447 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2448 l_module_name,
2449 'Created facts record in ' || vl_period_name);
2450 END IF;
2451 create_facts_record ;
2452 IF vp_retcode <> 0 THEN
2453 RETURN ;
2454 END IF ;
2455 END IF;
2456
2457 END LOOP;
2458 -- Close the Legislative Indicator Cursor
2459 BEGIN
2460 dbms_sql.close_cursor(vl_legis_cursor);
2461 EXCEPTION
2462 WHEN OTHERS THEN
2463 vp_retcode := sqlcode ;
2464 VP_ERRBUF := sqlerrm ;
2465 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2466 l_module_name||'.close_cursor_vl_legis_cursor',
2467 vp_errbuf);
2468 RETURN ;
2469 END ;
2470 -- Once the Legislative Indicator or Public Law code
2471 -- is processesed, no need to proceed further for this
2472 -- acct/fund combination. Going to the Next Account
2473 EXIT ;
2474
2475 EXCEPTION
2476 -- Process any Exceptions in Legislative Indicator
2477 -- Processing
2478 WHEN OTHERS THEN
2479 vp_retcode := sqlcode ;
2480 vp_errbuf := sqlerrm ||
2481 ' [ PROCESS_FACTS_TRANSCTIONS-LEGIS IND ] ' ;
2482 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2483 l_module_name||'message1', vp_errbuf) ;
2484 RETURN ;
2485 END; -- Legislative processing
2486
2487 -------------- Apportionment Category Processing Starts ----------------
2488 ELSIF (va_appor_cat_flag = 'Y' ) THEN
2489 -- Derive the Apportionment Category
2490 -- Apportionment Category Processing done only for FACTS II
2491 --Bug3376230 to include va_appor_cat_val = 'A' too
2492 -- 2005 FACTS II Enhancemnt to include category C
2493
2494 IF va_appor_cat_val = 'C' THEN
2495 va_appor_cat_b_dtl := '000';
2496 va_appor_cat_b_txt := '';
2497 va_prn_num := '000';
2498 va_prn_txt := '';
2499
2500 END IF;
2501
2502 IF va_appor_cat_val IN ('A', 'B') THEN
2503 IF ( FND_LOG.LEVEL_STATEMENT
2504 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2505 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2506 '++ Apportionment Category Processing ++') ;
2507 END IF ;
2508
2509 -- Get the Program segment name for the current fund value
2510 get_program_segment (vl_fund_value) ;
2511
2512 IF ( FND_LOG.LEVEL_STATEMENT >=
2513 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2514 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2515 l_module_name, 'Fund: '||vl_fund_value||
2516 ' Cat B Prog Seg: ' ||v_catb_prg_seg_name||
2517 ' PRN Prog Seg: ' || v_prn_prg_seg_name) ;
2518 End If ;
2519
2520
2521 IF ((v_catb_prg_seg_name IS NOT NULL AND
2522 va_appor_cat_val = 'B' ) OR
2523 (v_catb_prg_seg_name IS NULL
2524 AND va_appor_cat_val = 'A')) AND
2525 V_PRN_PRG_SEG_NAME IS not null THEN
2526 BEGIN
2527 vl_appor_cursor := DBMS_SQL.OPEN_CURSOR ;
2528 EXCEPTION
2529 WHEN OTHERS THEN
2530 vp_retcode := sqlcode ;
2531 vp_errbuf := sqlerrm ;
2532 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2533 l_module_name||'.open_vl_appor_cursor',
2534 vp_errbuf) ;
2535 RETURN ;
2536 END ;
2537 -- Dynamic SQL to group the amount by Fund, Acct
2538 -- and Program for the Beginning Balance
2539 -- Processing Apportionment Category for Beginning Balance
2540 va_balance_type_val := 'B' ;
2541
2542 vl_appor_period := ' AND glb.period_num = :begin_period_num
2543 AND glb.period_year = :report_fiscal_yr ';
2544
2545 build_appor_select(vl_acct_num,
2546 vl_fund_value,
2547 v_fiscal_yr,
2548 vl_appor_period,
2549 vl_appor_select) ;
2550 BEGIN
2551 dbms_sql.parse(vl_appor_cursor,vl_appor_select,
2552 DBMS_SQL.V7);
2553 EXCEPTION
2554 WHEN OTHERS THEN
2555 vp_retcode := sqlcode ;
2556 vp_errbuf := sqlerrm || ' [MAIN - APPOR]' ;
2557 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2558 l_module_name||'.parse_vl_appor_cursor', vp_errbuf) ;
2559 RETURN ;
2560 END ;
2561
2562 -- Bind the variables
2563 dbms_sql.bind_variable(vl_appor_cursor, ':actual_flag', 'A');
2564 dbms_sql.bind_variable(vl_appor_cursor, ':fund_value',
2565 vl_fund_value);
2566 dbms_sql.bind_variable(vl_appor_cursor, ':acct_number',
2567 vl_acct_num);
2568 dbms_sql.bind_variable(vl_appor_cursor, ':fiscal_year',
2569 v_fiscal_yr);
2570 dbms_sql.bind_variable(vl_appor_cursor, ':begin_period_num',
2571 v_begin_period_num);
2572 dbms_sql.bind_variable(vl_appor_cursor, ':report_fiscal_yr',
2573 vp_report_fiscal_yr);
2574 dbms_sql.bind_variable(vl_appor_cursor, ':set_of_books_id',
2575 vp_set_of_books_id);
2576 dbms_sql.bind_variable(vl_appor_cursor, ':currency_code',
2577 vp_currency_code);
2578
2579
2580 dbms_sql.define_column(vl_appor_cursor,1,vl_acct_num,25);
2581 dbms_sql.define_column(vl_appor_cursor,2,vl_fund_value,25);
2582 vl_count := 3;
2583 IF v_catb_prg_seg_name IS NOT NULL THEN
2584 dbms_sql.define_column(vl_appor_cursor,vl_count,vl_catb_program,25);
2585 vl_count := vl_count+1 ;
2586 END IF;
2587
2588 dbms_sql.define_column(vl_appor_cursor,vl_count,vl_prn_program,25);
2589 vl_count := vl_count+1 ;
2590
2591 dbms_sql.define_column(vl_appor_cursor,vl_count,v_amount);
2592
2593 IF v_cohort_Seg_name IS NOT NULL THEN
2594 vl_count := vl_count+1 ;
2595 dbms_sql.define_column(vl_appor_cursor,vl_count,vl_cohort_yr,25);
2596 END IF ;
2597
2598 BEGIN
2599 vl_exec_ret := dbms_sql.execute(vl_appor_cursor);
2600 EXCEPTION
2601 WHEN OTHERS THEN
2602 vp_retcode := sqlcode ;
2603 vp_errbuf := sqlerrm ;
2604 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2605 l_module_name||'.execute_vl_appor_cursor',
2606 vp_errbuf) ;
2607 RETURN ;
2608 END ;
2609 -- Reset the counter for apportionment cat b Dtl
2610 -- vl_appor_ctr := 0 ;
2611 LOOP
2612 IF dbms_sql.fetch_rows(vl_appor_cursor) = 0 THEN
2613 EXIT;
2614 ELSE
2615 -- Fetch the Records into Variables
2616 dbms_sql.column_value(vl_appor_cursor,1,
2617 vl_acct_num);
2618 dbms_sql.column_value(vl_appor_cursor,2,
2619 vl_fund_value);
2620 vl_count := 3;
2621
2622 IF v_catb_prg_seg_name IS NOT NULL THEN
2623 dbms_sql.column_value(vl_appor_cursor,vl_count,
2624 vl_catb_program);
2625 vl_count := vl_count+1 ;
2626 END IF;
2627 dbms_sql.column_value(vl_appor_cursor,vl_count,
2628 vl_prn_program);
2629 vl_count := vl_count+1 ;
2630 -- v_amount holds beginning balance.
2631 dbms_sql.column_value(vl_appor_cursor,vl_count,
2632 v_amount);
2633 IF v_cohort_Seg_name IS NOT NULL THEN
2634 vl_count := vl_count+1 ;
2635 dbms_sql.column_value(vl_appor_cursor,vl_count,
2636 vl_cohort_yr);
2637 END IF ;
2638
2639 -- vl_appor_ctr := vl_appor_ctr + 1 ;
2640 -- get_appor_cat_b_text(vl_program) ;
2641 IF ( FND_LOG.LEVEL_STATEMENT >=
2642 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2643 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2644 l_module_name,'Appor Beg-->
2645 Acct: '||vl_acct_num||
2646 ' Fund: '||vl_fund_value||
2647 ' Cat B Prgm: '||vl_catb_program ||
2648 ' PRN Prgm: '||vl_prn_program ||
2649 ' Amt: '||v_amount) ;
2650 END IF ;
2651 IF vp_retcode <> 0 THEN
2652 RETURN ;
2653 END IF ;
2654
2655 --Bug#3376230
2656 v_record_category := 'REPORTED' ;
2657 -- IF va_appor_cat_val = 'A' THEN
2658 get_prc_val(vl_catb_program,
2659 vl_catb_rc_val, vl_catb_pgm_desc,
2660 vl_catb_exception,
2661 vl_prn_program,
2662 vl_prn_rc_val, vl_prn_pgm_desc,
2663 vl_prn_exception);
2664
2665 IF vp_retcode <> 0 THEN
2666 RETURN ;
2667 END IF ;
2668
2669 va_appor_cat_b_dtl := vl_catb_rc_val;
2670 va_appor_cat_b_txt := vl_catb_pgm_desc;
2671
2672 IF ( FND_LOG.LEVEL_STATEMENT >=
2673 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2674 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2675 l_module_name,
2676 'Cat B RC Val: '||vl_catb_rc_val||
2677 'Cat B PGM Desc: '||vl_catb_pgm_desc||
2678 'PRN RC Val: '||vl_prn_rc_val||
2679 'PRN PGM Desc: '||vl_prn_pgm_desc);
2680 END IF;
2681
2682 IF vl_catb_exception = 1 THEN
2683 v_record_category := 'VALID_CATB_CODE_NOT_FOUND';
2684 vp_errbuf := 'Valid Category B Code not found '||
2685 'for program value: '||vl_catb_program;
2686 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2687 l_module_name||'.VALID_CAT_CODE_NOT_FOUND', vp_errbuf) ;
2688
2689 va_appor_cat_b_txt := vl_catb_program;
2690 v_tbal_fund_value := vl_fund_value;
2691
2692 create_facts_record ;
2693 IF vp_retcode <> 0 THEN
2694 RETURN ;
2695 END IF ;
2696 END IF;
2697 IF vl_prn_exception = 1 THEN
2698 v_record_category := 'VALID_PRN_CODE_NOT_FOUND';
2699 vp_errbuf := 'Valid Program Reporting Number code '
2700 ||'not found '||
2701 'for program value: '||vl_prn_program;
2702 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2703 l_module_name||'.VALID_PRN_CODE_NOT_FOUND'
2704 , vp_errbuf) ;
2705
2706 va_appor_cat_b_txt := NULL;
2707 va_prn_txt := vl_prn_program;
2708 v_tbal_fund_value := vl_fund_value;
2709
2710 create_facts_record ;
2711 IF vp_retcode <> 0 THEN
2712 RETURN ;
2713 END IF ;
2714 END IF;
2715
2716 -- for bug 5065974 by Adi
2717 -- Moved AND to OR Condition
2718
2719 IF (vl_catb_exception = 0 OR
2720 vl_prn_exception = 0 ) THEN
2721 v_record_category := 'REPORTED' ;
2722 va_appor_cat_b_dtl := vl_catb_rc_val;
2723 va_appor_cat_b_txt := vl_catb_pgm_desc;
2724 va_prn_num := vl_prn_rc_val;
2725 va_prn_txt := vl_prn_pgm_desc;
2726 v_tbal_fund_value := vl_fund_value;
2727 v_catb_program_value := vl_catb_program;
2728 v_prn_program_value := vl_prn_program;
2729 create_facts_record ;
2730 IF vp_retcode <> 0 THEN
2731 RETURN ;
2732 END IF ;
2733 END IF;
2734
2735 END IF ;
2736
2737 END LOOP ;
2738 -- Close the Apportionment Category Cursor
2739 BEGIN
2740 dbms_sql.close_cursor(vl_appor_cursor);
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 vp_retcode := sqlcode ;
2744 vp_errbuf := sqlerrm ;
2745 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2746 l_module_name||'.close_vl_appor_cursor',vp_errbuf) ;
2747 RETURN ;
2748 END ;
2749
2750 -- Processing Apportionment Category for Ending Balance
2751 BEGIN
2752 vl_appor_cursor := DBMS_SQL.OPEN_CURSOR ;
2753 EXCEPTION
2754 WHEN OTHERS THEN
2755 vp_retcode := sqlcode ;
2756 vp_errbuf := sqlerrm ;
2757 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2758 l_module_name||'.open_vl_appor_cursor',
2759 vp_errbuf) ;
2760 RETURN ;
2761 END ;
2762
2763 va_balance_type_val := 'E' ;
2764
2765 vl_appor_period := ' AND GLB.PERIOD_NUM = :period_num
2766 AND GLB.PERIOD_YEAR = :report_fiscal_yr ' ;
2767
2768 build_appor_select(vl_acct_num,
2769 vl_fund_value,
2770 v_fiscal_yr,
2771 vl_appor_period,
2772 vl_appor_select) ;
2773
2774 BEGIN
2775 dbms_sql.parse(vl_appor_cursor,vl_appor_select,
2776 DBMS_SQL.V7);
2777 EXCEPTION
2778 WHEN OTHERS THEN
2779 vp_retcode := sqlcode ;
2780 vp_errbuf := sqlerrm || ' [MAIN - APPOR]' ;
2781 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2782 l_module_name||'.parse_vl_appor_cursor',
2783 vp_errbuf) ;
2784 RETURN ;
2785 END ;
2786
2787 -- Bind the variables
2788 dbms_sql.bind_variable(vl_appor_cursor, ':actual_flag', 'A');
2789 dbms_sql.bind_variable(vl_appor_cursor, ':fund_value',
2790 vl_fund_value);
2791 dbms_sql.bind_variable(vl_appor_cursor, ':acct_number',
2792 vl_acct_num);
2793 dbms_sql.bind_variable(vl_appor_cursor, ':fiscal_year',
2794 v_fiscal_yr);
2795 dbms_sql.bind_variable(vl_appor_cursor, ':period_num',
2796 v_period_num);
2797 dbms_sql.bind_variable(vl_appor_cursor,':report_fiscal_yr',
2798 vp_report_fiscal_yr);
2799 dbms_sql.bind_variable(vl_appor_cursor,':set_of_books_id',
2800 vp_set_of_books_id);
2801 dbms_sql.bind_variable(vl_appor_cursor, ':currency_code',
2802 vp_currency_code);
2803
2804
2805 dbms_sql.define_column(vl_appor_cursor,1,vl_acct_num,25);
2806 dbms_sql.define_column(vl_appor_cursor,2,vl_fund_value,25);
2807 vl_count := 3;
2808 IF v_catb_prg_seg_name IS NOT NULL THEN
2809 dbms_sql.define_column(vl_appor_cursor,3,vl_catb_program,25);
2810 vl_count := vl_count+1 ;
2811 END IF ;
2812 dbms_sql.define_column(vl_appor_cursor,vl_count,vl_prn_program,25);
2813 vl_count := vl_count+1 ;
2814 dbms_sql.define_column(vl_appor_cursor,vl_count,v_amount);
2815 IF v_cohort_seg_name IS NOT NULL THEN
2816 vl_count := vl_count+1 ;
2817 dbms_sql.define_column(vl_appor_cursor,vl_count,vl_cohort_yr, 25);
2818 END IF ;
2819 BEGIN
2820 vl_exec_ret := dbms_sql.execute(vl_appor_cursor);
2821 EXCEPTION
2822 WHEN OTHERS THEN
2823 vp_retcode := sqlcode ;
2824 vp_errbuf := sqlerrm ;
2825 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2826 l_module_name||
2827 '.execute_vl_appor_cursor',
2828 vp_errbuf) ;
2829 RETURN ;
2830 END ;
2831
2832 -- Reset the counter for apportionment cat b Dtl
2833 -- vl_appor_ctr := 0 ;
2834 LOOP
2835 IF dbms_sql.fetch_rows(vl_appor_cursor) = 0 THEN
2836 EXIT;
2837 ELSE
2838 -- Fetch the Records into Variables
2839 dbms_sql.column_value(vl_appor_cursor,1,
2840 vl_acct_num);
2841 dbms_sql.column_value(vl_appor_cursor,2,
2842 vl_fund_value);
2843 vl_count := 3;
2844 IF v_catb_prg_seg_name IS NOT NULL THEN
2845 dbms_sql.column_value(vl_appor_cursor,vl_count,
2846 vl_catb_program);
2847 vl_count := vl_count+1 ;
2848 END IF;
2849 dbms_sql.column_value(vl_appor_cursor,vl_count,
2850 vl_prn_program);
2851 vl_count := vl_count+1 ;
2852 -- v_amount holds Balance of the transaction
2853 dbms_sql.column_value(vl_appor_cursor,vl_count,v_amount);
2854 IF v_cohort_Seg_name IS NOT NULL THEN
2855 vl_count := vl_count+1 ;
2856 dbms_sql.column_value(vl_appor_cursor, vl_count,
2857 vl_cohort_yr);
2858 END IF ;
2859 -- vl_appor_ctr := vl_appor_ctr + 1 ;
2860
2861 -- get_appor_cat_b_text(vl_program) ;
2862
2863 IF vp_retcode <> 0 THEN
2864 RETURN ;
2865 END IF ;
2866 IF ( FND_LOG.LEVEL_STATEMENT >=
2867 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2868 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2869 'Appor End --> Acct - '||vl_acct_num||
2870 ' Fund >>>> - '||vl_fund_value||
2871 ' Cat B Prgm >>>> - '||vl_catb_program||
2872 ' PRN Prgm >>>> - '||vl_prn_program||
2873 ' Amt >>>> - '||v_amount) ;
2874 END IF ;
2875
2876
2877 v_record_category := 'REPORTED' ;
2878 get_prc_val(vl_catb_program,
2879 vl_catb_rc_val, vl_catb_pgm_desc,
2880 vl_catb_exception,
2881 vl_prn_program,
2882 vl_prn_rc_val, vl_prn_pgm_desc,
2883 vl_prn_exception);
2884
2885 IF vp_retcode <> 0 THEN
2886 RETURN ;
2887 END IF ;
2888
2889 va_appor_cat_b_dtl := vl_catb_rc_val;
2890 va_appor_cat_b_txt := vl_catb_pgm_desc;
2891 va_prn_num := vl_prn_rc_val;
2892 va_prn_txt := vl_prn_pgm_desc;
2893 IF ( FND_LOG.LEVEL_STATEMENT >=
2894 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2895 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2896 l_module_name,
2897 'Cat B RC Val: '||vl_catb_rc_val||
2898 'Cat B PGM Desc: '||vl_catb_pgm_desc ||
2899 'PRN RC Val: '||vl_catb_rc_val||
2900 'PRN PGM Desc: '||vl_catb_pgm_desc);
2901 END IF;
2902
2903 IF vl_catb_exception <> 0 THEN
2904
2905 IF vl_catb_exception = 1 THEN
2906 v_record_category := 'VALID_CATB_CODE_NOT_FOUND';
2907 vp_errbuf := 'Valid Category B Code not found '||
2908 'for program value: '||vl_catb_program;
2909 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2910 l_module_name||'.VALID_CAT_CODE_NOT_FOUND', vp_errbuf) ;
2911
2912
2913 va_appor_cat_b_txt := vl_catb_program;
2914 v_tbal_fund_value := vl_fund_value;
2915 create_facts_record ;
2916 IF vp_retcode <> 0 THEN
2917 RETURN ;
2918 END IF ;
2919 END IF;
2920 END IF;
2921
2922 IF vl_prn_exception <> 0 THEN
2923 IF vl_prn_exception = 1 THEN
2924 v_record_category := 'VALID_PRN_CODE_NOT_FOUND';
2925 vp_errbuf := 'Valid Program Reporting Number Code not found '||
2926 'for program value: '||vl_prn_program;
2927 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2928 l_module_name||'.VALID_PRN_CODE_NOT_FOUND', vp_errbuf) ;
2929
2930
2931 va_prn_txt := vl_prn_program;
2932 v_tbal_fund_value := vl_fund_value;
2933 create_facts_record ;
2934 IF vp_retcode <> 0 THEN
2935 RETURN ;
2936 END IF ;
2937 END IF;
2938 END IF;
2939
2940
2941 IF (vl_catb_exception = 0 OR
2942 vl_prn_exception = 0) THEN
2943 v_record_category := 'REPORTED' ;
2944 va_appor_cat_b_dtl := vl_catb_rc_val;
2945 va_appor_cat_b_txt := vl_catb_pgm_desc;
2946 v_tbal_fund_value := vl_fund_value;
2947 v_catb_program_value := vl_catb_program;
2948 va_prn_num := vl_prn_rc_val;
2949 va_prn_txt := vl_prn_pgm_desc;
2950 v_prn_program_value := vl_prn_program;
2951 create_facts_record ;
2952 IF vp_retcode <> 0 THEN
2953 RETURN ;
2954 END IF ;
2955 END IF;
2956
2957 END IF ;
2958 END LOOP ;
2959 -- Close the Apportionment Category Cursor
2960 BEGIN
2961 dbms_sql.close_cursor(vl_appor_cursor);
2962 EXCEPTION
2963 WHEN OTHERS THEN
2964 vp_retcode := sqlcode ;
2965 vp_errbuf := sqlerrm ;
2966 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2967 l_module_name||'.close_vl_appor_cursor',
2968 vp_errbuf) ;
2969 RETURN ;
2970 END ;
2971 -- Apportionment Category B processing completed
2972 -- successfully, no need to proceed further for this
2973 -- acct/fund combination. Going to the Next Account
2974 EXIT ;
2975
2976 ELSE -- if program segment is null
2977 -- do default processing
2978 -- v_amount := vl_amount ;
2979 v_amount := ve_amount ; -- bug 5065974
2980 v_sgl_acct_num := vl_acct_num ;
2981
2982 IF ( FND_LOG.LEVEL_EXCEPTION >=
2983 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2984 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EXCEPTION,
2985 l_module_name, 'Program segment Not '||
2986 'defined Or null,so doing the default processing');
2987 END IF;
2988
2989 default_processing (vl_fund_value,vl_acct_num,'E',vb_amount,ve_amount);
2990
2991 EXIT; -- continue with the next account
2992 END IF ; /* Program segment not null */
2993
2994 END IF ; /* Apportionment Category B */
2995 END IF ; /* va_apportionment_category_flag */
2996
2997 --- If neither legislative nor apportionment processing
2998 --- is done then do default processing
2999 --default_processing (vl_fund_value,vl_acct_num);
3000 default_processing (vl_fund_value,vl_acct_num,NULL,vb_amount,ve_amount);
3001
3002 -- Exit to end the Dummy Loop
3003 EXIT ;
3004 END LOOP ; /* for dummy Loop */
3005 -- Exit the Main loop in case no end of the cursor is reached
3006 IF vl_main_fetch = 0 THEN
3007 EXIT ;
3008 END IF ;
3009 END LOOP ; /* For the Main Cursor */
3010
3011 -- Close the Main Cursor
3012 BEGIN
3013 dbms_sql.Close_Cursor(vl_main_cursor);
3014 EXCEPTION
3015 WHEN OTHERS THEN
3016 vp_retcode := sqlcode ;
3017 vp_errbuf := sqlerrm ;
3018 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3019 l_module_name||'.close_vl_main_cursor', vp_errbuf) ;
3020 RETURN ;
3021 END ;
3022 -- Rolling up the Inserted Data into Account Number
3023 -- Fund Value is also added in the roll up to accomodate ATB Process.
3024 -- tbal_Fund_value column will be blank for FACTS II processing.
3025 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3026 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3027 'Rolling up to Account Number');
3028 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3029 'tbal fund value '||v_tbal_fund_value);
3030 END IF ;
3031
3032 /* Procedure to rollup the records */
3033 facts_rollup_records;
3034
3035 -- Submit edit check process
3036 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3037 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3038 'Submitting Edit Check Process.....') ;
3039 END IF;
3040
3041 fv_facts_edit_check.perform_edit_checks
3042 (p_treasury_symbol_id => v_treasury_symbol_id ,
3043 p_facts_run_quarter => vp_report_qtr ,
3044 p_rep_fiscal_yr => vp_report_fiscal_yr ,
3045 retcode => vl_ec_retcode ,
3046 errbuf => vl_ec_errbuf,
3047 p_ledger_id => vp_set_of_books_id) ;
3048
3049
3050 -- Deleting zero amount records after edit check process
3051 DELETE FROM FV_FACTS_TEMP
3052 WHERE (fct_int_record_category = 'REPORTED' OR
3053 fct_int_record_category = 'REPORTED_NEW')
3054 AND amount = 0
3055 AND sgl_acct_number like '4%'
3056 AND treasury_symbol_id = v_treasury_symbol_id ;
3057
3058
3059 -- Setting the Error Code based on the Edit Check Process
3060 v_g_edit_check_code := vl_ec_retcode;
3061 IF vl_ec_retcode IN (1, 2) THEN
3062 -- Set the Edit check return code to 'Warning' status for errors
3063 -- in edit check process (Soft errors - 1, Hard Errors - 2)
3064 v_edit_check_code := 1 ;
3065 END IF ;
3066
3067 -- Submitting Edit Check Report
3068 vl_req_id := FND_REQUEST.SUBMIT_REQUEST
3069 ('FV','FVFCCHKR','','',FALSE, vp_set_of_books_id, v_treasury_symbol_id, v_period_name,
3070 vp_currency_code ) ;
3071
3072 commit;
3073 IF vl_req_id = 0 THEN
3074 vp_errbuf := 'Error Priniting Edit Check Report' ;
3075 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3076 END IF;
3077
3078 l_call_status := fnd_concurrent.wait_for_request(
3079 vl_req_id,
3080 0,
3081 0,
3082 rphase,
3083 rstatus,
3084 dphase,
3085 dstatus,
3086 message);
3087
3088 IF l_call_status = FALSE THEN
3089 vp_errbuf := 'Can not wait for the status of Edit Check Report';
3090 vp_retcode := '2';
3091 END IF;
3092
3093
3094 -- Generate other files only when edit check succeeds
3095 IF vl_ec_retcode IN (0, 1) THEN
3096 /* Process only if Edit Check is valid or run mode is Final */
3097 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3098 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3099 'Edit Check Process Completed Succesfully ');
3100 END IF;
3101 -- Generate the MAF_Sequence_Number based on the Run Mode
3102 DECLARE
3103 CURSOR c_maf_seq IS
3104 SELECT TO_CHAR(DECODE(vp_run_mode,'F',
3105 (DECODE(MAX(maf_seq_num), NULL, 0,MAX(maf_seq_num)+1)),'P',
3106 (NVL(MAX(maf_seq_num), 0))))
3107 FROM fv_facts_run
3108 WHERE treasury_symbol = vp_treasury_symbol
3109 AND treasury_symbol_id = v_treasury_symbol_id
3110 AND facts_run_period = v_period_num
3111 AND facts_run_year = vp_report_fiscal_yr ;
3112 BEGIN
3113 OPEN c_maf_seq;
3114 FETCH c_maf_seq INTO vc_maf_seq_num;
3115 IF c_maf_seq%NOTFOUND THEN
3116 vc_maf_seq_num := 1;
3117 END IF;
3118 CLOSE c_maf_seq;
3119 END;
3120 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3121 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3122 'Creating FACTS Detail Record.....');
3123 END IF;
3124
3125 -- Create Concatenated FACTS Record in the Temp table Column
3126
3127 UPDATE fv_facts_temp
3128 SET facts_report_info =
3129 vc_dept_regular || vc_dept_transfer || vc_fiscal_yr ||
3130 vc_main_account|| vc_sub_acct_symbol||lpad(vc_maf_seq_num,3,'0')||
3131 RPAD(NVL(program_rpt_cat_num, ' '), 3, ' ') ||
3132 RPAD(NVL(program_rpt_cat_txt, ' '), 25, ' ') ||
3133 LPAD(sgl_acct_number,4) || vc_record_indicator ||
3134 cohort || begin_end || indef_def_flag ||
3135 RPAD(NVL(appor_cat_b_dtl,' '),3,' ') ||
3136 RPAD(NVL(appor_cat_b_txt, ' '), 25, ' ') ||
3137 rpad(NVL(public_law, ' '),7,' ') ||
3138 appor_cat_code ||
3139 authority_type || transaction_partner || transfer_dept_id ||
3140 transfer_main_acct || vc_transfer_to_from || reimburseable_flag||
3141 RPAD(year_budget_auth,4) || bea_category || borrowing_source ||
3142 advance_flag ||vc_current_permanent_flag|| def_liquid_flag||
3143 deficiency_flag || availability_flag || legislation_flag ||
3144 RPAD(NVL(budget_function,' '),3) ||
3145 LPAD(DECODE(INSTR(TO_CHAR(ABS(amount)),'.',1,1), 0,
3146 TO_CHAR(ABS(amount))||'00',(SUBSTR(TO_CHAR(ABS(amount))
3147 , 1, instr(to_char(abs(amount)),'.',1,1) - 1) ||
3148 RPAD(substr(to_char(abs(amount)), instr(to_char(abs
3149 (amount)), '.',1,1) + 1, 2),2,'0'))), 17, '0') ||
3150 debit_credit || RPAD(' ', 258)
3151 WHERE fct_int_record_category = 'REPORTED_NEW'
3152 AND treasury_symbol_id = v_treasury_symbol_id ;
3153
3154 -- Create Contact Information and Request Record Header
3155 -- and its concatenated output format in the Temp table
3156 -- Record Category is set to CNT_HDR to distinguish from
3157 -- detail records
3158 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3159 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3160 'Creating Contact Header Record.....FACTS II ') ;
3161 END IF;
3162 INSERT INTO FV_FACTS_TEMP
3163 (treasury_symbol_id,
3164 fct_int_record_category,
3165 fct_int_record_type,
3166 facts_report_info)
3167 VALUES ( v_treasury_symbol_id ,
3168 'REPORTED_NEW',
3169 'CNT_HDR',
3170 vc_rpt_fiscal_yr || vc_rpt_fiscal_month ||
3171 vp_contact_fname || vp_contact_lname || vp_contact_phone ||
3172 vp_contact_extn || vp_agency_name_1 || vp_agency_name_2 ||
3173 vp_address_1 || vp_address_2 || vp_city || vp_state || vp_zip||
3174 vp_supervisor_name || vp_supervisor_phone||vp_supervisor_extn||
3175 vp_contact_email || vp_contact_fax || vp_contact_maiden ) ;
3176
3177 -- Insert a new row in FV_FACTS_RUN Table based on the run mode
3178 IF vp_run_mode = 'F' Then
3179 UPDATE fv_facts_run
3180 SET maf_seq_num = to_number(vc_maf_seq_num),
3181 last_run_date = sysdate
3182 WHERE treasury_symbol_id = v_treasury_symbol_id ;
3183 IF SQL%NOTFOUND THEN
3184 BEGIN
3185 INSERT INTO fv_facts_run
3186 (treasury_symbol_id,
3187 treasury_symbol,
3188 facts_run_period,
3189 facts_run_year,
3190 maf_seq_num,
3191 last_run_date)
3192 VALUES
3193 (v_treasury_symbol_id,
3194 vp_treasury_symbol,
3195 v_period_num,
3196 vp_report_fiscal_yr,
3197 to_number(vc_maf_seq_num),
3198 sysdate) ;
3199 EXCEPTION
3200 WHEN OTHERS THEN
3201 vp_retcode := sqlcode ;
3202 vp_errbuf := sqlerrm || '[FACTS RUN UPDATE]' ;
3203 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3204 l_module_name||'.insert_fv_facts_run', vp_errbuf) ;
3205 RETURN ;
3206 END ;
3207 END IF ;
3208 END IF ;
3209 END IF; -- vl_ec_retcode IN (0, 1) THEN
3210
3211 -- Create Detail Footnote Records
3212 FOR footnote_rec IN footnote_select(v_treasury_symbol_id)
3213 LOOP
3214 SELECT count(*)
3215 INTO footnote_count
3216 FROM fv_facts_footnote_hdr ffh,
3217 fv_facts_footnote_lines ffl
3218 WHERE ffh.treasury_symbol_id = footnote_rec.treasury_symbol_id
3219 AND ffh.sgl_acct_number = footnote_rec.sgl_acct_number
3220 AND ffh.footnote_header_id = ffl.footnote_header_id ;
3221 IF footnote_count = 0 THEN
3222 IF ( FND_LOG.LEVEL_STATEMENT >=
3223 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3224 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3225 l_module_name,
3226 'Creating Footnote Records.....');
3227 END IF;
3228 INSERT INTO fv_facts_footnote_hdr
3229 (footnote_header_id,
3230 treasury_symbol_id,
3231 sgl_acct_number)
3232 VALUES
3233 (fv_facts_footnote_hdr_s.nextval,
3234 footnote_rec.treasury_symbol_id,
3235 footnote_rec.sgl_acct_number);
3236
3237 INSERT INTO fv_facts_footnote_lines
3238 (footnote_header_id,
3239 footnote_line_id,
3240 footnote_seq_number,
3241 footnote_text)
3242 VALUES
3243 (fv_facts_footnote_hdr_s.currval,
3244 fv_facts_footnote_lines_s.nextval,
3245 1,
3246 'Footnotes should be entered in FACTS II '||
3247 'Online system');
3248 END IF;
3249 END LOOP;
3250
3251 -- Submitting FACTS Adjusted Trial Balance Report
3252 vl_req_id := FND_REQUEST.SUBMIT_REQUEST ('FV','FVFCTRBR','','',FALSE,
3253 vp_set_of_books_id, v_acc_val_set_id, v_period_num, vp_report_fiscal_yr,
3254 vp_treasury_symbol, v_treasury_symbol_id,
3255 vp_currency_code ) ;
3256 commit;
3257 -- if concurrent request submission failed then abort process
3258 IF vl_req_id = 0 THEN
3259 vp_errbuf := 'Error submitting FACTS ATB Report ';
3260 vp_retcode := -1 ;
3261 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3262 ELSE
3263 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3264 THEN
3265 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3266 'Concurrent Request Id For FACTS ATB Report - ' || vl_req_id);
3267 END IF;
3268
3269 l_call_status := fnd_concurrent.wait_for_request(
3270 vl_req_id,
3271 0,
3272 0,
3273 rphase,
3274 rstatus,
3275 dphase,
3276 dstatus,
3277 message);
3278
3279 IF l_call_status = FALSE THEN
3280 vp_errbuf := 'Can not wait for the status of FACTS ATB Report';
3281 vp_retcode := '2';
3282 END IF;
3283 END IF ;
3284
3285 -- Submitting FACTS Exception Report
3286 vl_req_id := FND_REQUEST.SUBMIT_REQUEST ('FV','FVFCTEXR','','',FALSE,
3287 vp_set_of_books_id, v_period_num, vp_report_fiscal_yr,
3288 vp_treasury_symbol, v_treasury_symbol_id,
3289 vp_currency_code ) ;
3290 commit;
3291 -- if concurrent request submission failed then abort process
3292 IF vl_req_id = 0 THEN
3293 vp_errbuf := 'Error submitting FACTS Exception Report Process';
3294 vp_retcode := -1 ;
3295 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
3296 vp_errbuf) ;
3297 ELSE
3298 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3299 THEN
3300 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3301 'Concurrent Request Id for FACTS Exception Report - '||vl_req_id);
3302 END IF;
3303 l_call_status := fnd_concurrent.wait_for_request(
3304 vl_req_id,
3305 0,
3306 0,
3307 rphase,
3308 rstatus,
3309 dphase,
3310 dstatus,
3311 message);
3312
3313 IF l_call_status = FALSE THEN
3314 vp_errbuf := 'Can not wait for the status of '||
3315 'FACTS Exception Report';
3316 vp_retcode := '2';
3317 END IF;
3318
3319 END IF;
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 vp_retcode := sqlcode ;
3323 vp_errbuf := sqlerrm ;
3324 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3325 l_module_name||'.final_exception', vp_errbuf) ;
3326
3327 END process_facts_transactions;
3328 --------------------------------------------------------------------------------
3329 -- Processes exception records based on the exception category.
3330 --------------------------------------------------------------------------------
3331 PROCEDURE get_ussgl_acct_num
3332 (acct_num IN VARCHAR2,
3333 sgl_acct_num OUT NOCOPY VARCHAR2,
3334 exception_category OUT NOCOPY VARCHAR2)
3335 IS
3336 l_module_name VARCHAR2(200);
3337 vl_ussgl_acct_num Varchar2(25) ;
3338 vl_acct_type Varchar2(1) ;
3339 vl_parent Varchar2(60) ;
3340 vl_ussgl_enabled Varchar2(1) ;
3341 vl_reporting_type Varchar2(1) ;
3342 vl_exists Varchar2(1) ;
3343 BEGIN
3344 l_module_name := g_module_name || 'get_ussgl_acct_num';
3345 -- Validate the Account number and return the corresponding SGL
3346 -- number or parent for getting attributes.
3347 -- Verify whether the account number exists in FV_FACTS_ATTRIBUTES table
3348 -- Validate the USSGL Account Number
3349 -- Reset Account Attributes Flag
3350 v_acct_attr_flag := 'N' ;
3351 get_ussgl_info(acct_num,
3352 vl_ussgl_enabled,
3353 vl_reporting_type) ;
3354 IF vp_retcode <> 0 THEN
3355 RETURN ;
3356 END IF ;
3357 IF vl_ussgl_enabled IS NOT NULL THEN -- Main acct No Validation
3358 IF vl_ussgl_enabled = 'N' THEN
3359 -- Generate the Exception 'USSGL_DISABLED'
3360 sgl_acct_num := acct_num ;
3361 exception_category := 'USSGL_DISABLED' ;
3362 RETURN ;
3363 END IF ;
3364 IF vl_reporting_type = '1' THEN
3365 -- Account Number is not a valid FACTS II Account
3366 -- skip the transaction and go ahead with the next.
3367 sgl_acct_num := acct_num ;
3368 exception_category := 'NON_FACTSII' ;
3369 RETURN ;
3370 END IF ;
3371 BEGIN -- checking Account in fv_facts_attributes table
3372 SELECT 'X'
3373 INTO vl_exists
3374 FROM fv_facts_attributes
3375 WHERE facts_acct_number = acct_num
3376 AND set_of_books_id = vp_set_of_books_id ;
3377 -- Account is a valid USSGL account and exists
3378 -- in Attributes table. It is a valid account
3379 -- and no further validation is done to find
3380 -- its account type.
3381 exception_category := NULL ;
3382 sgl_acct_num := acct_num ;
3383 RETURN ;
3384 EXCEPTION -- checking Account in Fv_Facts_attributes table
3385 WHEN NO_DATA_FOUND THEN
3386 -- Account doesn't exist in Attributes table
3387 -- Exceptions will be raised based on the Account
3388 -- type.
3389 get_account_type(acct_num, vl_acct_type) ;
3390 IF vp_retcode <> 0 THEN
3391 RETURN ;
3392 END IF ;
3393 IF vl_acct_type IN ('D', 'C') THEN
3394 -- Budgetary Acct for with no attributes
3395 sgl_acct_num := acct_num ;
3396 exception_category :='BUD_ACCT_NOT_SETUP' ;
3397 RETURN;
3398 ELSE
3399 -- Account is a Proprietary acct and no
3400 -- reportable exception or further processing
3401 -- is required.
3402 sgl_acct_num := acct_num ;
3403 exception_category := 'NON_BUD_ACCT' ;
3404 RETURN ;
3405 END IF ;
3406 END ; -- checking Account in fv_facts_attributes table
3407 ELSE -- Main acct No Validation -- when vl_ussgl_enabled is null
3408 -- Reset the holder variable
3409 vl_exists := NULL ;
3410 BEGIN
3411 SELECT 'X'
3412 INTO vl_exists
3413 FROM FV_FACTS_ATTRIBUTES
3414 WHERE facts_acct_number = acct_num
3415 AND set_of_books_id = vp_set_of_books_id ;
3416 v_acct_attr_flag := 'Y' ;
3417 EXCEPTION
3418 WHEN NO_DATA_FOUND THEN
3419 NULL ;
3420 END ;
3421 -- Finding the parent of the Account Number in GL
3422 BEGIN -- Finding Parent From GL
3423 -- Finding the parent
3424 SELECT parent_flex_value
3425 INTO vl_ussgl_acct_num
3426 FROM fnd_flex_value_hierarchies
3427 WHERE (acct_num Between child_flex_value_low
3428 AND child_flex_value_high)
3429 AND flex_value_set_id = v_acc_val_set_id
3430 AND parent_flex_value <> 'T'
3431 AND parent_flex_value in
3432 (SELECT ussgl_account
3433 FROM fv_facts_ussgl_accounts
3434 WHERE ussgl_account = parent_flex_value) ;
3435 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3436 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3437 'Parent in GL - ' || vl_ussgl_acct_num) ;
3438 END IF ;
3439 -- Parent Found. Perform Validations
3440 get_ussgl_info( vl_ussgl_acct_num,
3441 vl_ussgl_enabled,
3442 vl_reporting_type) ;
3443 IF vp_retcode <> 0 THEN
3444 RETURN ;
3445 END IF ;
3446 IF vl_ussgl_enabled IS NOT NULL THEN
3447 IF vl_ussgl_enabled = 'N' THEN
3448 -- Generate the Exception 'USSGL_DISABLED'
3449 sgl_acct_num := vl_ussgl_acct_num ;
3450 exception_category := 'USSGL_DISABLED' ;
3451 RETURN ;
3452 END IF ;
3453 IF vl_reporting_type = '1' THEN
3454 -- Account Number is not a valid candidate for FACTS II
3455 -- reporting. Transaction is skipped with no Exception
3456 sgl_acct_num := vl_ussgl_acct_num ;
3457 exception_category := 'NON_FACTSII' ;
3458 RETURN ;
3459 END IF ;
3460 IF vl_exists IS NOT NULL THEN
3461 IF ( FND_LOG.LEVEL_STATEMENT >=
3462 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3463 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3464 l_module_name,
3465 'Valid Parent [vl_exists] ') ;
3466 END IF ;
3467 -- Parent is a Valid USSGL Account
3468 exception_category := NULL ;
3469 sgl_acct_num := vl_ussgl_acct_num ;
3470 RETURN ;
3471 ELSE
3472 BEGIN
3473 SELECT 'X'
3474 INTO vl_exists
3475 FROM fv_facts_attributes
3476 WHERE facts_acct_number = vl_ussgl_acct_num
3477 AND set_of_books_id = vp_set_of_books_id ;
3478 -- Parent is a valid USSGL account and exists
3479 -- in Attributes table. It is a valid account
3480 -- and no further validation is done to find
3481 -- its account type.
3482 exception_category := NULL ;
3483 SGL_ACCT_NUM := vl_ussgl_acct_num ;
3484 RETURN ;
3485 EXCEPTION WHEN NO_DATA_FOUND THEN
3486 -- Parent doesn't exist in Attributes table
3487 -- Exceptions will be raised based on the Account
3488 -- type.
3489 get_account_type(vl_ussgl_acct_num, vl_acct_type) ;
3490 IF vp_retcode <> 0 THEN
3491 RETURN ;
3492 END IF ;
3493 IF vl_acct_type IN ('D', 'C') THEN
3494 -- Budgetary Acct for with no attributes
3495 sgl_acct_num := vl_ussgl_acct_num ;
3496 exception_category :='BUD_ACCT_NOT_SETUP' ;
3497 RETURN ;
3498 ELSE
3499 -- Account is a Proprietary acct and no
3500 -- reportable exception or further processing
3501 -- is required.
3502 sgl_acct_num := vl_ussgl_acct_num ;
3503 exception_category := 'NON_BUD_ACCT' ;
3504 RETURN ;
3505 END IF ;
3506 END ;
3507 END IF ;
3508 ELSE
3509 -- Parent not exist in FV_FACTS_USSGL_ACCOUNTS table.
3510 get_account_type(vl_ussgl_acct_num, vl_acct_type) ;
3511 IF vp_retcode <> 0 THEN
3512 RETURN ;
3513 END IF ;
3514 IF vl_acct_type IN ('D', 'C') THEN
3515 -- Budgetary Acct for with no attributes
3516 sgl_acct_num := vl_ussgl_acct_num ;
3517 exception_category :='BUD_ACCT_NOT_SETUP' ;
3518 RETURN ;
3519 ELSE
3520 -- Account is a Proprietary acct and no
3521 -- reportable exception or further processing
3522 -- is required.
3523 sgl_acct_num := vl_ussgl_acct_num ;
3524 Exception_category := 'NON_BUD_ACCT' ;
3525 RETURN ;
3526 END IF ;
3527 END IF ;
3528 EXCEPTION /* Finding Parent From GL */
3529 WHEN NO_DATA_FOUND THEN
3530 get_account_type(acct_num, vl_acct_type) ;
3531 IF vp_retcode <> 0 THEN
3532 RETURN ;
3533 END IF ;
3534 IF vl_acct_type IN ('D', 'C') THEN
3535 -- Budgetary Acct for with no attributes
3536 sgl_acct_num := acct_num ;
3537 exception_category :='BUD_ACCT_NOT_SETUP' ;
3538 RETURN ;
3539 ELSE
3540 -- Account is a Proprietary acct and no
3541 -- reportable exception or further processing
3542 -- is required.
3543 sgl_acct_num := acct_num ;
3544 exception_category := 'NON_BUD_ACCT' ;
3545 RETURN ;
3546 END IF ;
3547 WHEN TOO_MANY_ROWS THEN
3548 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
3549 'More then one Parent for '||acct_num) ;
3550 -- Too Many Parents. Process Exception
3551 sgl_acct_num := acct_num ;
3552 exception_category := 'USSGL_MULTIPLE_PARENTS' ;
3553 RETURN ;
3554 END ; /* Finding Parent From GL */
3555 END IF ; /* Main acct No Validation */
3556 EXCEPTION
3557 WHEN OTHERS THEN
3558 vp_retcode := sqlcode ;
3559 vp_errbuf := sqlerrm || ' [GET_USSGL_ACCOUNT_NUM] ' ;
3560 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3561 l_module_name||'.final_exception', vp_errbuf) ;
3562 RETURN;
3563 END get_ussgl_acct_num ;
3564 --------------------------------------------------------------------------------
3565 -- Inserts a new record into FV_FACTS_TEMP table with the current
3566 -- values from the global variables.
3567 --------------------------------------------------------------------------------
3568 PROCEDURE create_facts_record
3569 IS
3570 l_module_name VARCHAR2(200);
3571 vl_parent_sgl_acct_num fv_facts_temp.parent_sgl_acct_number%TYPE;
3572 l_year_budget_auth VARCHAR2(3);
3573 BEGIN
3574 l_module_name := g_module_name || 'create_facts_record';
3575 IF v_year_gtn2001
3576 THEN va_legis_ind_val := ' ';
3577 END IF;
3578
3579 IF v_amount = 0 THEN
3580 l_year_budget_auth := ' ';
3581 ELSE
3582 l_year_budget_auth := v_year_budget_auth;
3583 END IF;
3584
3585 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3586 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3587 'Creating FACTS Record') ;
3588 END IF ;
3589
3590 INSERT INTO FV_FACTS_TEMP
3591 (SGL_ACCT_NUMBER ,
3592 COHORT ,
3593 BEGIN_END ,
3594 INDEF_DEF_FLAG ,
3595 APPOR_CAT_B_DTL ,
3596 APPOR_CAT_B_TXT ,
3597 PUBLIC_LAW ,
3598 APPOR_CAT_CODE ,
3599 AUTHORITY_TYPE ,
3600 TRANSACTION_PARTNER ,
3601 REIMBURSEABLE_FLAG ,
3602 BEA_CATEGORY ,
3603 BORROWING_SOURCE ,
3604 DEF_LIQUID_FLAG ,
3605 DEFICIENCY_FLAG ,
3606 AVAILABILITY_FLAG ,
3607 LEGISLATION_FLAG ,
3608 AMOUNT ,
3609 DEBIT_CREDIT ,
3610 TREASURY_SYMBOL_ID ,
3611 FCT_INT_RECORD_CATEGORY ,
3612 FCT_INT_RECORD_TYPE ,
3613 FACTS_REPORT_INFO ,
3614 TBAL_FUND_VALUE ,
3615 TBAL_INDICATOR ,
3616 BUDGET_FUNCTION ,
3617 ADVANCE_FLAG ,
3618 TRANSFER_DEPT_ID ,
3619 TRANSFER_MAIN_ACCT ,
3620 YEAR_BUDGET_AUTH ,
3621 period_activity ,
3622 parent_sgl_acct_number,
3623 PROGRAM_RPT_CAT_NUM,
3624 PROGRAM_RPT_CAT_TXT,
3625 SEGMENT1,
3626 SEGMENT2)
3627 VALUES (v_sgl_acct_num ,
3628 va_cohort ,
3629 va_balance_type_val ,
3630 va_def_indef_val ,
3631 va_appor_cat_b_dtl ,
3632 va_appor_cat_b_txt ,
3633 va_public_law_code_val ,
3634 va_appor_cat_val ,
3635 va_authority_type_val ,
3636 va_transaction_partner_val,
3637 va_reimburseable_val ,
3638 va_bea_category_val ,
3639 va_borrowing_source_val ,
3640 va_def_liquid_flag ,
3641 va_deficiency_val ,
3642 va_availability_flag ,
3643 va_legis_ind_val ,
3644 v_amount ,
3645 NULL ,
3646 v_treasury_symbol_id ,
3647 v_record_category ,
3648 'BLK_DTL' ,
3649 NULL ,
3650 v_tbal_fund_value ,
3651 v_tbal_indicator ,
3652 va_budget_function ,
3653 va_advance_type_val ,
3654 va_transfer_dept_id ,
3655 va_transfer_main_acct ,
3656 l_year_budget_auth ,
3657 v_period_activity ,
3658 vl_parent_sgl_acct_num,
3659 va_prn_num,
3660 va_prn_txt,
3661 v_catb_program_value,
3662 v_prn_program_value) ;
3663 EXCEPTION
3664 WHEN OTHERS THEN
3665 vp_retcode := sqlcode ;
3666 vp_errbuf := sqlerrm || ' [CREATE_FACTS_RECORD] ' ;
3667 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3668 l_module_name||'.final_exception', vp_errbuf) ;
3669 RETURN;
3670 END create_facts_record ;
3671 --------------------------------------------------------------------------------
3672 -- This procedure selects the attributes for the Account number
3673 -- segment from FV_FACTS_ATTRIBUTES table and load them into global
3674 -- variables for usage in the FACTS Main process. It also calculates
3675 -- one time pull up values for the account number that does not
3676 -- require drill down into GL transactions.
3677 --------------------------------------------------------------------------------
3678 PROCEDURE load_facts_attributes (acct_num Varchar2,
3679 fund_val Varchar2,
3680 ve_amount number)
3681 IS
3682 l_module_name VARCHAR2(200);
3683 vl_financing_acct_flag VARCHAR2(1) ;
3684 vl_established_fy NUMBER ;
3685 vl_resource_type VARCHAR2(80) ;
3686 vl_fund_category VARCHAR2(1) ;
3687 -- Back up for the global variabe v_sgl_acct_num
3688 vl_sgl_acct_num VARCHAR2(25) ;
3689 vl_dummy NUMBER;
3690 BEGIN
3691 l_module_name := g_module_name || 'load_facts_attributes';
3692 BEGIN
3693 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3694 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3695 'LOAD - Acct Num -> '||acct_num) ;
3696 END IF;
3697 SELECT balance_type,
3698 public_law_code,
3699 reimburseable_flag,
3700 DECODE(availability_time, 'N', ' ', availability_time),
3701 bea_category,
3702 apportionment_category,
3703 DECODE(substr(transaction_partner,1,1),'N',' ',
3704 substr(transaction_partner,1,1)),
3705 borrowing_source,
3706 definite_indefinite_flag,
3707 legislative_indicator,
3708 authority_type,
3709 deficiency_flag,
3710 function_flag,
3711 advance_flag,
3712 transfer_flag
3713 INTO va_balance_type_flag,
3714 va_public_law_code_flag,
3715 va_reimburseable_flag,
3716 va_availability_flag,
3717 va_bea_category_flag,
3718 va_appor_cat_flag,
3719 va_transaction_partner_val,
3720 va_borrowing_source_flag,
3721 va_def_indef_flag,
3722 va_legis_ind_flag,
3723 va_authority_type_flag,
3724 va_deficiency_flag,
3725 va_function_flag,
3726 va_advance_flag,
3727 va_transfer_ind
3728 FROM fv_facts_attributes
3729 WHERE facts_acct_number = acct_num
3730 AND set_of_books_id = vp_set_of_books_id ;
3731 IF NOT v_year_gtn2001 THEN
3732 va_advance_flag := ' ';
3733 va_transfer_ind := ' ';
3734 END IF;
3735 EXCEPTION
3736 WHEN NO_DATA_FOUND THEN
3737 vp_retcode := -1 ;
3738 vp_errbuf := 'Error! No Attributes Definied for the Account - ' ||
3739 v_acct_num || ' [LOAD_FACTS_ATTRIBURES]' ;
3740 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
3741 vp_errbuf) ;
3742 RETURN;
3743 WHEN OTHERS THEN
3744 vp_retcode := sqlcode ;
3745 vp_errbuf := sqlerrm ;
3746 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3747 vp_errbuf) ;
3748 RETURN;
3749 END ;
3750
3751 --------------------------------------------------------------------------------
3752 -- Get the attribute column names for public_law_code and other
3753 -- values
3754
3755 BEGIN
3756
3757 SELECT factsII_pub_law_code_attribute,
3758 factsII_advance_type_attribute,
3759 factsII_tr_main_acct_attribute,
3760 factsII_tr_dept_id_attribute
3761 INTO va_pl_code_col, va_advance_type_col,
3762 va_tr_main_acct_col, va_tr_dept_id_col
3763 FROM fv_system_parameters;
3764
3765
3766 IF (va_pl_code_col IS NULL OR
3767 va_advance_type_col IS NULL OR
3768 va_tr_main_acct_col IS NULL OR
3769 va_tr_dept_id_col IS NULL)
3770 THEN
3771 v_facts_attributes_setup := FALSE;
3772 ELSE
3773 v_facts_attributes_setup := TRUE;
3774 END IF;
3775
3776 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3777 WHEN OTHERS THEN
3778 vp_retcode := sqlcode ;
3779 vp_errbuf := sqlerrm ;
3780 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3781 l_module_name||'.select1', vp_errbuf) ;
3782 RETURN;
3783 END;
3784 --------------------------------------------------------------------------------
3785 -- Getting the One time Pull up Values
3786 BEGIN
3787 SELECT UPPER(fts.resource_type),
3788 def_indef_flag,
3789 ffp.fund_category
3790 INTO vl_resource_type,
3791 va_def_indef_val,
3792 vl_fund_category
3793 FROM fv_treasury_symbols fts,
3794 fv_fund_parameters ffp
3795 WHERE ffp.treasury_symbol_id = fts.treasury_symbol_id
3796 AND ffp.fund_value = fund_val
3797 AND fts.treasury_symbol = vp_treasury_symbol
3798 AND fts.set_of_books_id = vp_set_of_books_id
3799 AND ffp.set_of_books_id = vp_set_of_books_id ;
3800 EXCEPTION
3801 WHEN NO_DATA_FOUND THEN
3802 vp_retcode := -1 ;
3803 vp_errbuf := 'Error getting Fund Category value for the fund - '||
3804 fund_val || ' [LOAD_FACTS_ATTRIBURES]' ;
3805 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3806 l_module_name||'.select2', vp_errbuf) ;
3807 RETURN;
3808 WHEN OTHERS THEN
3809 vp_retcode := sqlcode ;
3810 vp_errbuf := sqlerrm || ' [LOAD_FACTS_ATTRIBURES]' ;
3811 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3812 l_module_name||'.select2', vp_errbuf) ;
3813 RETURN;
3814 END ;
3815
3816 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3817 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3818 'Get ending balance to report amount '||
3819 'for exception records.');
3820 END IF;
3821
3822 /* ---- for bug 5065974
3823 calc_balance (fund_val,
3824 acct_num,
3825 v_period_num,
3826 vp_report_fiscal_yr,
3827 'E',
3828 v_fiscal_yr,
3829 v_amount,
3830 vl_dummy);
3831
3832 ----------------------------------------- */
3833
3834 v_amount := ve_amount; -- now amount passed as parameter , bug 5065974
3835
3836 -- Deriving Indefinite Definite Flag
3837 IF va_def_indef_flag <> 'Y' THEN
3838 va_def_indef_val := ' ' ;
3839 ELSE
3840 IF va_def_indef_val is NULL OR
3841 LTRIM(RTRIM(va_def_indef_val)) = '' THEN
3842 -- Create Exception
3843 vl_sgl_acct_num := v_sgl_acct_num ;
3844 v_sgl_acct_num := acct_num ;
3845 v_record_category := 'INDEF_DEF_NOT_DEFINED' ;
3846 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3847 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3848 'Creating Exception: '||v_record_category);
3849 END IF;
3850 create_facts_record ;
3851 -- Reset the value back to v_sgl_acct_number
3852 v_sgl_acct_num := vl_sgl_acct_num ;
3853 END IF ;
3854 END IF ;
3855
3856 -- Deriving Public Law Code Flag
3857 IF va_public_law_code_flag = 'N' THEN
3858 va_public_law_code_val := ' ' ;
3859 END IF ;
3860
3861 -- Deriving Apportionment Category Code
3862 IF va_appor_cat_flag = 'Y' THEN
3863 IF vl_fund_category IN ('A','S') THEN
3864 va_appor_cat_val := 'A' ;
3865 ELSIF vl_fund_category IN ('B','T') THEN
3866 va_appor_cat_val := 'B' ;
3867 ELSIF vl_fund_category IN ('C','R') THEN
3868 va_appor_cat_val := 'C' ;
3869 ELSE
3870 va_appor_cat_val := ' ' ;
3871 END IF ;
3872 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3873 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3874 'Acct - ' || acct_num ||
3875 ' Fund cat - ' || vl_fund_category || ' Appr Cat - ' ||
3876 va_appor_cat_val || ' Flag - ' || va_appor_cat_flag) ;
3877 END IF ;
3878 ELSE
3879 va_appor_cat_val := ' ' ;
3880 END IF ;
3881 -- Default the Reporting Codes when the
3882 -- Apportionment Category is unchecked
3883
3884 IF NVL(va_appor_cat_flag,'N') = 'N' THEN
3885 IF vl_fund_category IN ('A','B','C','R','S','T') THEN
3886 va_appor_cat_b_dtl := '000';
3887 va_appor_cat_b_txt := '';
3888 va_prn_num := '000';
3889 va_prn_txt := '';
3890
3891 END IF;
3892
3893 IF ( FND_LOG.LEVEL_STATEMENT >=
3894 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3895 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3896 l_module_name, 'Defaulting the Reporting'
3897 ||'codes as the apportionment '
3898 ||'Category flag is N ') ;
3899 End If ;
3900 END IF;
3901
3902
3903 -- Deriving Authority Type
3904 IF va_authority_type_flag = 'N' THEN
3905 va_authority_type_val := ' ' ;
3906 ELSE
3907 va_authority_type_val := va_authority_type_flag ;
3908 END IF ;
3909
3910 -- Deriving Reimburseable Flag Value
3911 IF va_reimburseable_flag = 'Y' THEN
3912 IF vl_fund_category IN ('A', 'B', 'C') THEN
3913 va_reimburseable_val := 'D' ;
3914 ELSIF vl_fund_category IN ('R', 'S', 'T') THEN
3915 va_reimburseable_val := 'R' ;
3916 ELSE
3917 va_reimburseable_val := ' ' ;
3918 END IF ;
3919 ELSE
3920 va_reimburseable_val := ' ' ;
3921 END IF ;
3922
3923 -- Deriving BEA Category
3924 IF va_bea_category_flag = 'Y' THEN
3925
3926 -- by ks for bug 6409180
3927 BEGIN
3928 SELECT RPAD(substr(bea_category,1,5), 5)
3929 INTO va_bea_category_val
3930 from fv_fund_parameters
3931 where fund_value = fund_val
3932 AND set_of_books_id = vp_set_of_books_id;
3933
3934 /*
3935 FROM fv_facts_budget_accounts ffba,
3936 fv_facts_federal_accounts fffa,
3937 fv_treasury_symbols fts ,
3938 fv_facts_bud_fed_accts ffbfa
3939 WHERE fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
3940 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
3941 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
3942 AND fts.treasury_symbol = vp_treasury_symbol
3943 AND fts.set_of_books_id = vp_set_of_books_id
3944 AND fffa.set_of_books_id = vp_set_of_books_id
3945 AND ffbfa.set_of_books_id = vp_set_of_books_id
3946 AND ffba.set_of_books_id = vp_set_of_books_id ;
3947 */
3948
3949 IF va_bea_category_val IS NULL THEN
3950 -- Create Exception Record for BEA Category
3951 v_record_category := 'BEA_CATEGORY_NOT_DEFINED' ;
3952 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3953 THEN
3954 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3955 'Creating Exception: '||
3956 v_record_category);
3957 END IF;
3958 create_facts_record ;
3959 END IF ;
3960 EXCEPTION
3961 WHEN NO_DATA_FOUND THEN
3962 -- Create Exception Record for BEA Category
3963 v_record_category := 'BEA_CATEGORY_NOT_DEFINED' ;
3964 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3965 THEN
3966 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3967 'Creating Exception: '||
3968 v_record_category);
3969 END IF;
3970 create_facts_record ;
3971 END ;
3972 ELSE
3973 va_bea_category_val := RPAD(' ', 5);
3974 END IF ;
3975
3976 -- Deriving Budget Function
3977 IF va_function_flag = 'Y' THEN
3978 BEGIN
3979 SELECT RPAD(substr(ffba.budget_function,1,3), 3)
3980 INTO va_budget_function
3981 FROM fv_facts_budget_accounts ffba,
3982 fv_facts_federal_accounts fffa,
3983 fv_treasury_symbols fts ,
3984 fv_facts_bud_fed_accts ffbfa
3985 WHERE fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
3986 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
3987 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
3988 AND fts.treasury_symbol = vp_treasury_symbol
3989 AND fts.set_of_books_id = vp_set_of_books_id
3990 AND fffa.set_of_books_id = vp_set_of_books_id
3991 AND ffbfa.set_of_books_id = vp_set_of_books_id
3992 AND ffba.set_of_books_id = vp_set_of_books_id ;
3993
3994 IF va_budget_function IS NULL THEN
3995 -- Create Exception Record for Budget Function
3996 v_record_category := 'BUDGET_FNCTN_NOT_DEFINED' ;
3997 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3998 THEN
3999 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4000 'Creating Exception: '||
4001 v_record_category);
4002 END IF;
4003 create_facts_record ;
4004 END IF ;
4005 EXCEPTION
4006 WHEN NO_DATA_FOUND THEN
4007 -- Create Exception Record for Budget Function
4008 v_record_category := 'BUDGET_FNCTN_NOT_DEFINED' ;
4009 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4010 THEN
4011 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
4012 l_module_name,'Creating Exception: '||
4013 v_record_category);
4014 END IF;
4015 create_facts_record ;
4016 END ;
4017 ELSE
4018 va_budget_function := RPAD(' ', 3);
4019 END IF ;
4020
4021 -- Deriving Borrowing Source
4022 IF va_borrowing_source_flag = 'Y' THEN
4023 BEGIN
4024 SELECT RPAD(substr(ffba.borrowing_source,1,6), 6)
4025 INTO va_borrowing_source_val
4026 FROM fv_facts_budget_accounts ffba,
4027 fv_facts_federal_accounts fffa,
4028 fv_treasury_symbols fts ,
4029 fv_facts_bud_fed_accts ffbfa
4030 WHERE fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
4031 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
4032 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
4033 AND fts.treasury_symbol = vp_treasury_symbol
4034 AND fts.set_of_books_id = vp_set_of_books_id
4035 AND fffa.set_of_books_id = vp_set_of_books_id
4036 AND ffbfa.set_of_books_id = vp_set_of_books_id
4037 AND ffba.set_of_books_id = vp_set_of_books_id ;
4038
4039 IF va_borrowing_source_val IS NULL THEN
4040 -- Create Exception Record for Borrowing Source
4041 v_record_category := 'B_SOURCE_NOT_DEFINED' ;
4042 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4043 THEN
4044 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
4045 l_module_name,'Creating Exception: '||
4046 v_record_category);
4047 END IF;
4048 create_facts_record ;
4049 END IF ;
4050 EXCEPTION
4051 WHEN NO_DATA_FOUND THEN
4052 -- Create Exception Record for Borrowing Source
4053 v_record_category := 'B_SOURCE_NOT_DEFINED' ;
4054 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4055 THEN
4056 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
4057 l_module_name,'Creating Exception: '||
4058 v_record_category);
4059 END IF;
4060 create_facts_record ;
4061 END ;
4062 ELSE
4063 va_borrowing_source_val := RPAD(' ', 6);
4064 END IF ;
4065 va_def_liquid_flag := ' ' ;
4066 va_deficiency_val := ' ' ;
4067 -- reset amount
4068 v_amount := 0;
4069 EXCEPTION
4070 WHEN OTHERS THEN
4071 vp_retcode := sqlcode ;
4072 vp_errbuf := sqlerrm || ' [LOAD_FACTS_ATTRIBUTES]' ;
4073 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
4074 '.final_exception', vp_errbuf) ;
4075 END load_facts_attributes ;
4076 --------------------------------------------------------------------------------
4077 -- Gets the information like enabled flag and reporting type
4078 -- for the passed account number.
4079 --------------------------------------------------------------------------------
4080 PROCEDURE get_ussgl_info (ussgl_acct_num Varchar2,
4081 enabled_flag OUT NOCOPY Varchar2,
4082 reporting_type OUT NOCOPY Varchar2)
4083 IS
4084 l_module_name VARCHAR2(200);
4085 BEGIN
4086 l_module_name := g_module_name || 'get_ussgl_info';
4087 SELECT ussgl_enabled_flag,
4088 reporting_type
4089 INTO enabled_flag,
4090 reporting_type
4091 FROM fv_facts_ussgl_accounts
4092 WHERE ussgl_account = ussgl_acct_num ;
4093 EXCEPTION
4094 WHEN NO_DATA_FOUND Then
4095 -- Account Number not found in FV_FACTS_USSGL_ACCOUNTS table.
4096 -- Return Nulls.
4097 enabled_flag := NULL ;
4098 reporting_type := NULL ;
4099 WHEN OTHERS THEN
4100 vp_retcode := sqlcode ;
4101 vp_errbuf := sqlerrm ;
4102 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
4103 '.final_exception', vp_errbuf) ;
4104 RETURN ;
4105 END get_ussgl_info ;
4106 --------------------------------------------------------------------------------
4107 -- Gets the Account Type Value for the passed Account Number.
4108 --------------------------------------------------------------------------------
4109 PROCEDURE get_account_type (acct_num VARCHAR2,
4110 acct_type OUT NOCOPY VARCHAR2)
4111 IS
4112 l_module_name VARCHAR2(200);
4113 BEGIN
4114 l_module_name := g_module_name || 'get_account_type';
4115 -- Get the Account Type from fnd Tables
4116 SELECT substr(compiled_value_attributes, 5, 1)
4117 INTO acct_type
4118 FROM fnd_flex_values
4119 WHERE flex_value_set_id = v_acc_val_set_id
4120 AND flex_value = acct_num ;
4121 IF acct_type IS NULL THEN
4122 -- Process Null Account Types
4123 vp_retcode := -1 ;
4124 vp_errbuf := 'Account Type found null for the for the
4125 Account Number - ' || acct_num ;
4126 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',
4127 vp_errbuf) ;
4128 RETURN ;
4129 END IF ;
4130 EXCEPTION
4131 WHEN NO_DATA_FOUND THEN
4132 vp_retcode := -1 ;
4133 vp_errbuf := 'Account Type Cannot be derived for the Account Number - '
4134 || acct_num ;
4135 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4136 l_module_name||'.exception1', vp_errbuf) ;
4137 RETURN ;
4138 WHEN OTHERS THEN
4139 vp_errbuf := SQLERRM;
4140 vp_retcode := -1;
4141 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
4142 RAISE;
4143 END get_account_type ;
4144 --------------------------------------------------------------------------------
4145 -- Gets the SGL Parent Account for the passed account number
4146 --------------------------------------------------------------------------------
4147 PROCEDURE get_sgl_parent(acct_num VARCHAR2,
4148 sgl_acct_num OUT NOCOPY VARCHAR2)
4149 IS
4150 l_module_name VARCHAR2(200);
4151 l_errbuf VARCHAR2(1024);
4152 vl_exists VARCHAR2(1) ;
4153 BEGIN
4154 l_module_name := g_module_name || 'get_sgl_parent';
4155 -- Finding the parent of the Account Number in GL
4156 BEGIN /* Finding Parent From GL */
4157 SELECT parent_flex_value
4158 INTO sgl_acct_num
4159 FROM fnd_flex_value_hierarchies
4160 WHERE (acct_num Between child_flex_value_low
4161 AND child_flex_value_high)
4162 AND flex_value_set_id = v_acc_val_set_id
4163 AND parent_flex_value <> 'T'
4164 AND parent_flex_value IN
4165 (SELECT ussgl_account
4166 FROM fv_facts_ussgl_accounts
4167 WHERE ussgl_account = parent_flex_value) ;
4168 BEGIN
4169 -- Look for parent in FV_FACTS_ATTRIBUTES table
4170 SELECT 'X'
4171 INTO vl_exists
4172 FROM fv_facts_attributes
4173 WHERE facts_acct_number = sgl_acct_num
4174 AND set_of_books_id = vp_set_of_books_id ;
4175 -- Return the account Number
4176 RETURN ;
4177 EXCEPTION
4178 WHEN NO_DATA_FOUND THEN
4179 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
4180 'Look for parent in FV_FACTS_ATTRIBUTES');
4181 sgl_acct_num := NULL ;
4182 RETURN ;
4183 END ;
4184 EXCEPTION
4185 WHEN NO_DATA_FOUND THEN
4186 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
4187 'No Parent Exists ' );
4188 --Fix for bug # 2450918 . Included the 'RETURN' statement below
4189 RETURN;
4190 WHEN TOO_MANY_ROWS Then
4191 -- Too Many Parents. Return Nulls
4192 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
4193 'Too Many Parents');
4194 RETURN ;
4195 END ;
4196 EXCEPTION
4197 WHEN OTHERS THEN
4198 l_errbuf := SQLERRM;
4199 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4200 l_module_name||'.final_exception',l_errbuf);
4201 RAISE;
4202 END get_sgl_parent ;
4203 --------------------------------------------------------------------------------
4204 -- This procedure Calculates the balance for the passed
4205 -- Acct_segment, Fund Value and Period Nnumber .
4206 --------------------------------------------------------------------------------
4207 PROCEDURE calc_balance (fund_value VARCHAR2,
4208 acct_num VARCHAR2,
4209 period_num NUMBER,
4210 period_year NUMBER,
4211 balance_type VARCHAR2,
4212 fiscal_year VARCHAR2,
4213 amount OUT NOCOPY NUMBER,
4214 period_activity OUT NOCOPY NUMBER)
4215 IS
4216 l_module_name VARCHAR2(200);
4217 -- Variables for Dynamic SQL
4218 vl_ret_val BOOLEAN := TRUE ;
4219 vl_exec_ret INTEGER ;
4220 vl_bal_cursor INTEGER ;
4221 vl_bal_select VARCHAR2(2000) ;
4222 BEGIN
4223 l_module_name := g_module_name || 'calc_balance';
4224 BEGIN
4225 vl_bal_cursor := DBMS_SQL.OPEN_CURSOR ;
4226 EXCEPTION
4227 WHEN OTHERS THEN
4228 vp_retcode := sqlcode ;
4229 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Open Cursor] ' ;
4230 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4231 l_module_name||'.open_vl_bal_cursor', vp_errbuf) ;
4232 RETURN;
4233 END ;
4234
4235 vl_bal_select :=
4236 'SELECT NVL(DECODE(' || '''' || balance_type || '''' ||
4237 ',' || '''' || 'B' || '''' ||
4238 ', SUM(glb.begin_balance_dr - glb.begin_balance_cr), ' ||
4239 '''' || 'E' || '''' || ', SUM((glb.begin_balance_dr -
4240 glb.begin_balance_cr) + (glb.period_net_dr - period_net_cr ))),0),
4241 SUM(glb.period_net_dr - glb.period_net_cr)
4242 FROM gl_balances glb,
4243 gl_code_combinations glcc
4244 WHERE glb.code_combination_id = glcc.code_combination_id ';
4245
4246 vl_bal_select :=
4247 vl_bal_select ||' AND glb.actual_flag = :actual_flag
4248 AND GLCC.' || v_bal_seg_name || ' = :fund_value
4249 AND GLCC.' || v_acc_seg_name || ' = :acct_num
4250 AND GLCC.' || v_fyr_segment_name || ' = :fiscal_year '||
4251 v_cohort_where ||' AND GLB.ledger_id = :set_of_books_id
4252 AND GLB.PERIOD_NUM = :period_num
4253 AND GLB.PERIOD_YEAR = :period_year
4254 AND glb.currency_code = :currency_code ';
4255
4256 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4257 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4258 'Calc bal: '||vl_bal_select) ;
4259 END IF;
4260
4261 BEGIN
4262 dbms_sql.parse(vl_bal_cursor, vl_bal_select, DBMS_SQL.V7) ;
4263 EXCEPTION
4264 WHEN OTHERS THEN
4265 vp_retcode := sqlcode ;
4266 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Parse] ' ;
4267 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4268 l_module_name||'.parse_vl_bal_cursor', vp_errbuf) ;
4269 RETURN;
4270 END ;
4271
4272 -- Bind the variables
4273 dbms_sql.bind_variable(vl_bal_cursor,':actual_flag', 'A');
4274 dbms_sql.bind_variable(vl_bal_cursor,':fund_value', fund_value);
4275 dbms_sql.bind_variable(vl_bal_cursor,':acct_num', acct_num);
4276 dbms_sql.bind_variable(vl_bal_cursor,':fiscal_year', fiscal_year);
4277 dbms_sql.bind_variable(vl_bal_cursor,':set_of_books_id',
4278 vp_set_of_books_id);
4279 dbms_sql.bind_variable(vl_bal_cursor,':period_num', period_num);
4280 dbms_sql.bind_variable(vl_bal_cursor,':period_year', period_year);
4281 dbms_sql.bind_variable(vl_bal_cursor,':currency_code', vp_currency_code);
4282
4283 dbms_sql.define_column(vl_bal_cursor, 1, amount);
4284 dbms_sql.define_column(vl_bal_cursor, 2, period_activity);
4285 BEGIN
4286 vl_exec_ret := dbms_sql.execute(vl_bal_cursor);
4287 EXCEPTION
4288 WHEN OTHERS THEN
4289 vp_retcode := sqlcode ;
4290 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Execute Cursor] ' ;
4291 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4292 l_module_name||'.execute_vl_bal_cursor', vp_errbuf) ;
4293 END ;
4294 LOOP
4295 IF dbms_sql.fetch_rows(vl_bal_cursor) = 0 THEN
4296 EXIT;
4297 ELSE
4298 -- Fetch the Records into Variables
4299 dbms_sql.column_value(vl_bal_cursor, 1, amount);
4300 dbms_sql.column_value(vl_bal_cursor, 2, period_activity);
4301 END IF;
4302 END LOOP ;
4303 -- Close the Balance Cursor
4304 BEGIN
4305 dbms_sql.Close_Cursor(vl_bal_cursor);
4306 EXCEPTION
4307 WHEN OTHERS THEN
4308 vp_retcode := sqlcode ;
4309 vp_errbuf := sqlerrm ;
4310 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4311 l_module_name||'.close_vl_bal_cursor', vp_errbuf) ;
4312 RETURN ;
4313 END ;
4314 EXCEPTION
4315 WHEN OTHERS THEN
4316 vp_retcode := sqlcode ;
4317 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Others]' ;
4318 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, vp_errbuf);
4319 RETURN;
4320 END calc_balance;
4321 --------------------------------------------------------------------------------
4322 -- Gets the Program segment name and prc_mapping_flag from v_segs_array and
4323 -- v_prc_flag_array respectively, which in turn is derived from
4324 -- FV_FACTS_PRC_HDR table.
4325 --------------------------------------------------------------------------------
4326 PROCEDURE get_program_segment(v_fund_value VARCHAR2)
4327 IS
4328 l_module_name VARCHAR2(200);
4329 vl_prg_seg_name VARCHAR2(30);
4330 vl_prg_val_set_id NUMBER(15);
4331
4332 BEGIN
4333 l_module_name := g_module_name || '.get_program_segment';
4334
4335 --Initialize both the segments with null
4336 v_prn_prg_seg_name := NULL ;
4337 v_catb_prg_seg_name := NULL;
4338
4339 FOR i IN 1..v_funds_count
4340 LOOP
4341
4342 IF v_segs_array(i).fund_value = v_fund_value THEN
4343
4344 -- Get the value set id for the program segment
4345 BEGIN
4346 -- Getting the Value set Id for finding hierarchies
4347 SELECT flex_value_set_id
4348 INTO vl_prg_val_set_id
4349 FROM fnd_id_flex_segments
4350 WHERE application_column_name = v_segs_array(i).segment
4351 AND application_id = 101
4352 AND id_flex_code = 'GL#'
4353 AND id_flex_num = v_chart_of_accounts_id;
4354 EXCEPTION
4355 WHEN NO_DATA_FOUND THEN
4356 vp_retcode := -1 ;
4357 vp_errbuf := 'Error getting Value Set Id for segment'
4358 ||v_segs_array(i).segment||' [GET_PROGRAM_SEGMENT]' ;
4359 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
4360 WHEN TOO_MANY_ROWS THEN
4361 -- Too many value set ids returned for the program segment.
4362 vp_retcode := -1 ;
4363 vp_errbuf := 'Program Segment - ' || v_segs_array(i).segment || ' returned
4364 more than one Value Sets !! '||'[ GET_PROGRAM_SEGMENT ]' ;
4365 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
4366 END ;
4367
4368 IF v_segs_array(i).code_type = 'B' THEN
4369 v_catb_prg_seg_name := v_segs_array(i).segment;
4370 v_catb_rc_flag := v_segs_array(i).prc_flag;
4371 v_catb_rc_header_id := v_segs_array(i).prc_header_id;
4372 v_catb_prg_val_set_id := vl_prg_val_set_id;
4373
4374 ELSIF v_segs_array(i).code_type = 'N' THEN
4375 v_prn_prg_seg_name := v_segs_array(i).segment;
4376 v_prn_rc_flag := v_segs_array(i).prc_flag;
4377 v_prn_rc_header_id := v_segs_array(i).prc_header_id;
4378 v_prn_prg_val_set_id := vl_prg_val_set_id;
4379 END IF;
4380
4381 END IF;
4382
4383 END LOOP;
4384 -- ADI
4385 IF v_catb_prg_seg_name is NULL AND v_prn_prg_seg_name IS NULL THEN
4386 RETURN ;
4387 END IF;
4388
4389 EXCEPTION
4390 WHEN NO_DATA_FOUND THEN
4391 -- Fund Value not found in FV_BUDGET_DISTRIBUTION_HDR table.
4392 v_prn_prg_seg_name := NULL ;
4393 v_catb_prg_seg_name := NULL;
4394 WHEN TOO_MANY_ROWS THEN
4395 -- Fund Value not found in FV_BUDGET_DISTRIBUTION_HDR table.
4396 vp_retcode := -1 ;
4397 vp_errbuf := 'Fund Value - ' || v_fund_value || ' returned more
4398 than one program segment value !! '||'[GET_PROGRAM_SEGMENT]' ;
4399 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4400 l_module_name||'.exception1', vp_errbuf) ;
4401 RETURN;
4402 WHEN OTHERS THEN
4403 vp_errbuf := SQLERRM;
4404 vp_retcode := -1;
4405 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
4406 '.final_exception',vp_errbuf);
4407 RAISE;
4408
4409 END get_program_segment ;
4410 --------------------------------------------------------------------------------
4411 -- Builds the SQL Statement for the apportionment Category B Processing.
4412 --------------------------------------------------------------------------------
4413 PROCEDURE build_appor_select (acct_number VARCHAR2,
4414 fund_value VARCHAR2,
4415 fiscal_year VARCHAR2,
4416 appor_period VARCHAR2,
4417 select_stmt OUT NOCOPY VARCHAR2)
4418 IS
4419 l_module_name VARCHAR2(200);
4420 l_errbuf VARCHAR2(1024);
4421 BEGIN
4422 l_module_name := g_module_name || 'build_appor_select';
4423
4424 select_stmt :=
4425 'Select GLCC.' || v_acc_seg_name ||
4426 ', GLCC.' || v_bal_seg_name ;
4427
4428 IF v_catb_prg_seg_name IS NOT NULL THEN
4429 select_stmt := select_stmt ||
4430 ', GLCC.' || v_catb_prg_seg_name ;
4431 END IF;
4432
4433 IF v_prn_prg_seg_name IS NOT NULL THEN
4434 select_stmt := select_stmt ||
4435 ', GLCC.' || v_prn_prg_seg_name ;
4436 END IF;
4437
4438 select_stmt := select_stmt ||
4439 ', nvl(DECODE(' || '''' || va_balance_type_val || '''' ||
4440 ',' || '''' || 'B' || '''' ||
4441 ', SUM(glb.begin_balance_dr - glb.begin_balance_cr), ' ||
4442 '''' || 'E' || '''' || ', SUM((glb.begin_balance_dr -
4443 glb.begin_balance_cr) + (glb.period_net_dr - period_net_cr ))),0) '||
4444 v_cohort_select ||
4445 ' FROM gl_balances glb,
4446 gl_code_combinations glcc
4447 WHERE glb.code_combination_id = GLCC.code_combination_id
4448 AND '||'glb.actual_flag = :actual_flag
4449 AND GLCC.'|| v_bal_seg_name ||' = :fund_value
4450 AND GLCC.' || v_acc_seg_name ||' = :acct_number
4451 AND GLCC.' || v_fyr_segment_name ||' = :fiscal_year '||
4452 appor_period || v_cohort_where ||
4453 ' AND glb.ledger_id = :set_of_books_id
4454 AND glb.currency_code = :currency_code
4455 GROUP BY GLCC.' || v_acc_seg_name ||
4456 ', GLCC.' || v_bal_seg_name ;
4457
4458 IF v_catb_prg_seg_name IS NOT NULL THEN
4459 select_stmt := select_stmt ||
4460 ', GLCC.' || v_catb_prg_seg_name ;
4461 END IF;
4462
4463 IF v_prn_prg_seg_name IS NOT NULL THEN
4464 select_stmt := select_stmt ||
4465 ', GLCC.' || v_prn_prg_seg_name ;
4466 END IF;
4467
4468 select_stmt := select_stmt ||
4469 ', GLCC.' || v_fyr_segment_name || v_cohort_select;
4470 EXCEPTION
4471 WHEN OTHERS THEN
4472 l_errbuf := SQLERRM;
4473 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4474 l_module_name||'.final_exception',l_errbuf);
4475 RAISE;
4476 END build_appor_select ;
4477 --------------------------------------------------------------------------------
4478 -- Gets the Apportionment Category B Detail and Text Information. Program
4479 -- segment value is passed to get the Text information and Counter value
4480 -- passed to get the converted text value (For Example when the appor_cnt
4481 -- value passed is 3 then the value returned is '003'
4482 --------------------------------------------------------------------------------
4483 PROCEDURE get_segment_text(p_program IN VARCHAR2,
4484 p_prg_val_set_id IN NUMBER,
4485 p_seg_txt OUT NOCOPY VARCHAR2)
4486 IS
4487 l_module_name VARCHAR2(200);
4488 BEGIN
4489 l_module_name := g_module_name || 'get_segment_text';
4490 -- Get the Apportionment Category B Text
4491 -- SELECT DECODE(ffvl.description,NULL,RPAD(' ',25,' '),
4492 -- RPAD(ffvl.description,25,' '))
4493 SELECT DECODE(ffvl.description,NULL,RPAD(' ',25,' '),
4494 RPAD(SUBSTR(ffvl.description,1,25),25,' '))
4495 INTO p_seg_txt
4496 FROM fnd_flex_values_tl ffvl,
4497 fnd_flex_values ffv
4498 WHERE ffvl.flex_value_id = ffv.flex_value_id
4499 AND ffv.flex_value_set_id = p_prg_val_set_id
4500 AND ffv.flex_value = p_program
4501 AND ffvl.language = userenv('LANG');
4502
4503 EXCEPTION
4504 WHEN NO_DATA_FOUND THEN
4505 vp_retcode := -1 ;
4506 vp_errbuf := 'Cannot Find Apportionment Category B Text for
4507 the Program ' || p_program||' [GET_SEGMENT_TEXT] ';
4508 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4509 l_module_name||'.exception1', vp_errbuf) ;
4510 RETURN;
4511 WHEN TOO_MANY_ROWS THEN
4512 vp_retcode := -1 ;
4513 vp_errbuf := 'More then one Apportionment Category B Text found for
4514 the Program '||p_program||' [GET_SEGMENT_TEXT]';
4515 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4516 l_module_name||'.exception2', vp_errbuf) ;
4517 RETURN;
4518 WHEN OTHERS THEN
4519 vp_errbuf := SQLERRM;
4520 vp_retcode := -1;
4521 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4522 l_module_name||'.final_exception3',vp_errbuf);
4523 RAISE;
4524 END ;
4525 --------------------------------------------------------------------------------
4526 PROCEDURE default_processing(vl_fund_value varchar2,
4527 vl_acct_num varchar2,
4528 rec_cat varchar2 := 'R',
4529 vb_amount number,
4530 ve_amount number)
4531 IS
4532 l_module_name VARCHAR2(200);
4533 l_errbuf VARCHAR2(1024);
4534 vl_amount number(25,2);
4535 vl_period_activity number(25,2);
4536 BEGIN
4537 l_module_name := g_module_name || 'default_processing';
4538 -------------- Normal Processing ----------------
4539 -- Only done on the following conditions:
4540 -- No Apportionment category B Processing or
4541 -- Legislation Indicator processing is done.
4542 -- Program segment cannot be found for Apportionment
4543 -- Category B Processing
4544 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4545 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4546 'Normal Processing ') ;
4547 END IF ;
4548 IF va_balance_type_flag IN ('S', 'E') THEN
4549 va_balance_type_val := 'E' ;
4550 v_record_category := 'REPORTED' ;
4551 /* ---------------- commtned out for bug 5065974
4552 calc_balance (vl_fund_value,
4553 vl_acct_num,
4554 v_period_num,
4555 vp_report_fiscal_yr,
4556 'E',
4557 v_fiscal_yr,
4558 vl_amount,
4559 vl_period_activity) ;
4560 v_amount := vl_amount ;
4561 v_period_activity := vl_period_activity;
4562 ------------------------------------------------- */
4563 v_amount := ve_amount;
4564 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4565 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4566 'Ending Balance(Normal) -> '||v_amount);
4567 END IF;
4568
4569 create_facts_record ;
4570
4571 IF vp_retcode <> 0 THEN
4572 RETURN ;
4573 END IF ;
4574 IF (rec_cat = 'E' ) THEN
4575 v_record_category := 'PROGRAM_SEGMENT_NOT_DEF' ;
4576 v_tbal_fund_value := vl_fund_value ;
4577 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4578 THEN
4579 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4580 'Creating Exception: '||
4581 v_record_category);
4582 END IF;
4583 create_facts_record ;
4584 END IF ;
4585 END IF;
4586
4587 IF va_balance_type_flag IN ('S', 'B') THEN
4588
4589 /* ----- Commted out for bug 5065974
4590 calc_balance (vl_fund_value,
4591 vl_acct_num,
4592 v_begin_period_num,
4593 vp_report_fiscal_yr,
4594 'B',
4595 v_fiscal_yr,
4596 v_begin_amount,
4597 vl_period_activity) ;
4598
4599 ---------------------------------------- */
4600
4601 v_begin_amount := vb_amount;
4602
4603 IF vp_retcode <> 0 THEN
4604 RETURN ;
4605 END IF ;
4606 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4607 THEN
4608 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4609 'Beginning Balance(Normal) -> '||v_begin_amount);
4610 END IF ;
4611 va_balance_type_val := 'B' ;
4612 v_record_category := 'REPORTED' ;
4613 v_amount := v_begin_amount ;
4614 v_period_activity := 0; -- vl_period_activity;
4615
4616 create_facts_record ;
4617
4618 IF vp_retcode <> 0 THEN
4619 RETURN ;
4620 END IF ;
4621 END IF ;
4622 EXCEPTION
4623 WHEN OTHERS THEN
4624 l_errbuf := SQLERRM;
4625 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4626 l_module_name||'.final_exception',l_errbuf);
4627 RAISE;
4628 END default_processing;
4629 --------------------------------------------------------------------------------------
4630 PROCEDURE facts_rollup_records
4631 IS
4632 l_module_name VARCHAR2(200);
4633 l_errbuf VARCHAR2(1024);
4634
4635 l_count NUMBER;
4636
4637 CURSOR facts_temp IS
4638 SELECT rowid, sgl_acct_number, tbal_fund_value,
4639 fct_int_record_category, begin_end,
4640 treasury_symbol_id
4641 FROM fv_facts_temp
4642 WHERE (fct_int_record_category = 'REPORTED'
4643 OR fct_int_record_category = 'REPORTED_NEW')
4644 AND AMOUNT = 0
4645 AND NVL(PERIOD_ACTIVITY,0) = 0
4646 AND treasury_symbol_id = v_treasury_symbol_id ;
4647
4648 l_account_type VARCHAR2(1);
4649
4650 BEGIN
4651 l_module_name := g_module_name || 'facts_rollup_records';
4652 INSERT INTO FV_FACTS_TEMP
4653 (TREASURY_SYMBOL_ID ,
4654 SGL_ACCT_NUMBER ,
4655 PARENT_SGL_ACCT_NUMBER ,
4656 COHORT ,
4657 BEGIN_END ,
4658 INDEF_DEF_FLAG ,
4659 APPOR_CAT_B_DTL ,
4660 APPOR_CAT_B_TXT ,
4661 PUBLIC_LAW ,
4662 APPOR_CAT_CODE ,
4663 AUTHORITY_TYPE ,
4664 TRANSACTION_PARTNER ,
4665 REIMBURSEABLE_FLAG ,
4666 BEA_CATEGORY ,
4667 BORROWING_SOURCE ,
4668 DEF_LIQUID_FLAG ,
4669 DEFICIENCY_FLAG ,
4670 AVAILABILITY_FLAG ,
4671 LEGISLATION_FLAG ,
4672 AMOUNT ,
4673 DEBIT_CREDIT ,
4674 FCT_INT_RECORD_CATEGORY ,
4675 FCT_INT_RECORD_TYPE ,
4676 FACTS_REPORT_INFO ,
4677 --TBAL_FUND_VALUE , --Bug#4515907
4678 TBAL_ACCT_NUM ,
4679 TBAL_INDICATOR ,
4680 YEAR_BUDGET_AUTH ,
4681 BUDGET_FUNCTION ,
4682 ADVANCE_FLAG ,
4683 TRANSFER_DEPT_ID ,
4684 TRANSFER_MAIN_ACCT ,
4685 PERIOD_ACTIVITY ,
4686 PROGRAM_RPT_CAT_NUM,
4687 PROGRAM_RPT_CAT_TXT
4688 -- SEGMENT1,
4689 -- SEGMENT2
4690 )
4691 SELECT
4692 TREASURY_SYMBOL_ID,
4693 SGL_ACCT_NUMBER,
4694 PARENT_SGL_ACCT_NUMBER,
4695 COHORT,
4696 BEGIN_END,
4697 INDEF_DEF_FLAG,
4698 APPOR_CAT_B_DTL,
4699 APPOR_CAT_B_TXT,
4700 PUBLIC_LAW,
4701 APPOR_CAT_CODE,
4702 AUTHORITY_TYPE,
4703 TRANSACTION_PARTNER,
4704 REIMBURSEABLE_FLAG,
4705 BEA_CATEGORY,
4706 BORROWING_SOURCE,
4707 DEF_LIQUID_FLAG,
4708 DEFICIENCY_FLAG,
4709 AVAILABILITY_FLAG,
4710 LEGISLATION_FLAG,
4711 SUM(AMOUNT),
4712 NULL,
4713 'REPORTED_NEW',
4714 'BLK_DTL',
4715 NULL ,
4716 -- DECODE(v_tbal_run_flag, 'Y', v_tbal_fund_value, NULL),
4717 --tbal_fund_value, --Bug#4515907
4718 NULL ,
4719 TBAL_INDICATOR ,
4720 YEAR_BUDGET_AUTH,
4721 BUDGET_FUNCTION ,
4722 ADVANCE_FLAG ,
4723 TRANSFER_DEPT_ID,
4724 TRANSFER_MAIN_ACCT,
4725 --SUM(period_activity),
4726 0, -- bug 5065974 (as period acitivity not needed for ATB)
4727 PROGRAM_RPT_CAT_NUM,
4728 PROGRAM_RPT_CAT_TXT
4729 -- SEGMENT1,
4730 -- SEGMENT2
4731 FROM fv_facts_temp
4732 WHERE fct_int_record_category = 'REPORTED'
4733 AND fct_int_record_type = 'BLK_DTL'
4734 AND treasury_symbol_id = v_treasury_symbol_id
4735 GROUP BY TREASURY_SYMBOL_ID,
4736 SGL_ACCT_NUMBER,
4737 PARENT_SGL_ACCT_NUMBER,
4738 COHORT,
4739 BEGIN_END,
4740 INDEF_DEF_FLAG,
4741 APPOR_CAT_B_DTL,
4742 APPOR_CAT_B_TXT,
4743 PUBLIC_LAW,
4744 APPOR_CAT_CODE,
4745 AUTHORITY_TYPE,
4746 TRANSACTION_PARTNER,
4747 REIMBURSEABLE_FLAG,
4748 BEA_CATEGORY,
4749 BORROWING_SOURCE,
4750 DEF_LIQUID_FLAG,
4751 DEFICIENCY_FLAG,
4752 AVAILABILITY_FLAG,
4753 LEGISLATION_FLAG ,
4754 --TBAL_FUND_VALUE , --Bug#4515907
4755 TBAL_INDICATOR ,
4756 YEAR_BUDGET_AUTH,
4757 BUDGET_FUNCTION ,
4758 ADVANCE_FLAG ,
4759 TRANSFER_DEPT_ID,
4760 TRANSFER_MAIN_ACCT,
4761 PROGRAM_RPT_CAT_NUM,
4762 PROGRAM_RPT_CAT_TXT;
4763 -- SEGMENT1,
4764 -- SEGMENT2;
4765
4766
4767 /*
4768 FOR facts_temp_rec IN facts_temp
4769 LOOP
4770 get_account_type(facts_temp_rec.sgl_acct_number,
4771 l_account_type);
4772 IF l_account_type IN ('D', 'C')
4773 THEN
4774 IF (facts_temp_rec.fct_int_record_category = 'REPORTED_NEW'
4775 AND facts_temp_rec.begin_end = 'E') THEN
4776 SELECT count(*) INTO l_count
4777 FROM fv_facts_temp
4778 WHERE begin_end = 'B'
4779 AND sgl_acct_number = facts_temp_rec.sgl_acct_number
4780 AND tbal_fund_value = facts_temp_rec.tbal_fund_value
4781 AND treasury_symbol_id = facts_temp_rec.treasury_symbol_id
4782 AND fct_int_record_category = 'REPORTED_NEW'
4783 AND amount <> 0;
4784
4785 IF l_count = 0 THEN
4786 DELETE FROM fv_facts_temp
4787 WHERE rowid = facts_temp_rec.rowid;
4788 END IF;
4789 END IF;
4790
4791 END IF;
4792 END LOOP;
4793 */
4794
4795 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4796 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4797 'Setting up Debit/Credit Indicator') ;
4798 END IF;
4799
4800 UPDATE fv_facts_temp
4801 SET debit_credit = 'C'
4802 WHERE amount < 0
4803 AND fct_int_record_category = 'REPORTED_NEW'
4804 AND treasury_symbol_id = v_treasury_symbol_id ;
4805
4806 UPDATE fv_facts_temp
4807 SET debit_credit = 'D'
4808 WHERE amount >= 0
4809 AND fct_int_record_category = 'REPORTED_NEW'
4810 AND treasury_symbol_id = v_treasury_symbol_id ;
4811 EXCEPTION
4812 WHEN OTHERS THEN
4813 l_errbuf := SQLERRM;
4814 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4815 l_module_name||'.final_exception',l_errbuf);
4816 RAISE;
4817
4818
4819 END facts_rollup_records;
4820 --------------------------------------------------------------------------------
4821 PROCEDURE check_prc_map_seg(p_treasury_symbol_id IN NUMBER,
4822 p_sob_id IN NUMBER,
4823 p_fund_value OUT NOCOPY VARCHAR2,
4824 p_catb_status OUT NOCOPY VARCHAR2,
4825 p_prn_status OUT NOCOPY VARCHAR2)
4826 IS
4827
4828 l_module_name VARCHAR2(200);
4829
4830 CURSOR fund_cur(cv_ts_id IN NUMBER,
4831 cv_sob_id IN NUMBER) IS
4832 SELECT fund_value,
4833 DECODE(fund_category,'S','A','T','B',fund_category) fund_category
4834 FROM fv_fund_parameters ffp
4835 WHERE ffp.treasury_symbol_id = cv_ts_id
4836 AND ffp.set_of_books_id = cv_sob_id
4837 AND ffp.fund_category IN ('A', 'B', 'S', 'T');
4838
4839 vl_ts_id NUMBER;
4840 vl_fund_value fv_fund_parameters.fund_value%TYPE;
4841 vl_prg_seg fv_facts_prc_hdr.program_segment%TYPE;
4842 vl_prc_header_id NUMBER;
4843 vl_prc_flag fv_facts_prc_hdr.prc_mapping_flag%TYPE;
4844 vl_code_type fv_facts_prc_hdr.code_type%TYPE;
4845 vl_status VARCHAR2(15);
4846
4847 BEGIN
4848 l_module_name := g_module_name || 'check_prc_map_seg';
4849
4850 v_funds_count := 0;
4851
4852 FOR fund_rec IN fund_cur(p_treasury_symbol_id,
4853 p_sob_id)
4854 LOOP
4855
4856 FOR type in 1..2
4857 LOOP
4858 IF type = 1 THEN
4859 vl_code_type := 'B';
4860 ELSE
4861 vl_code_type := 'N';
4862 END IF;
4863 vl_status := '';
4864 LOOP
4865
4866 vl_prg_seg := NULL;
4867 vl_prc_flag := NULL;
4868 vl_prc_header_id := NULL;
4869 p_fund_value := fund_rec.fund_value;
4870
4871 BEGIN
4872 SELECT program_segment,
4873 prc_mapping_flag, prc_header_id
4874 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4875 FROM fv_facts_prc_hdr ffh
4876 WHERE ffh.treasury_symbol_id = p_treasury_symbol_id
4877 AND ffh.code_type = vl_code_type
4878 AND ffh.set_of_books_id = p_sob_id
4879 AND ffh.fund_value = fund_rec.fund_value;
4880 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4881 END;
4882
4883 IF vl_prg_seg IS NOT NULL THEN
4884 vl_status := 'pass'; EXIT; END IF;
4885
4886 BEGIN
4887 SELECT program_segment,
4888 prc_mapping_flag, prc_header_id
4889 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4890 FROM fv_facts_prc_hdr ffh
4891 WHERE ffh.treasury_symbol_id = p_treasury_symbol_id
4892 AND ffh.code_type = vl_code_type
4893 AND ffh.set_of_books_id = p_sob_id
4894 AND ffh.fund_value = 'ALL-A'
4895 AND fund_rec.fund_category = 'A';
4896 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4897 END;
4898
4899 IF vl_prg_seg IS NOT NULL THEN
4900 vl_status := 'pass';EXIT; END IF;
4901
4902 BEGIN
4903 SELECT program_segment,
4904 prc_mapping_flag, prc_header_id
4905 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4906 FROM fv_facts_prc_hdr ffh
4907 WHERE ffh.treasury_symbol_id = p_treasury_symbol_id
4908 AND ffh.code_type = vl_code_type
4909 AND ffh.set_of_books_id = p_sob_id
4910 AND ffh.fund_value = 'ALL-B'
4911 AND fund_rec.fund_category = 'B';
4912 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4913 END;
4914
4915 IF vl_prg_seg IS NOT NULL THEN
4916 vl_status := 'pass'; EXIT; END IF;
4917
4918 BEGIN
4919 SELECT program_segment,
4920 prc_mapping_flag, prc_header_id
4921 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4922 FROM fv_facts_prc_hdr ffh
4923 WHERE ffh.treasury_symbol_id = p_treasury_symbol_id
4924 AND ffh.code_type = vl_code_type
4925 AND ffh.set_of_books_id = p_sob_id
4926 AND ffh.fund_value = 'ALL-FUNDS';
4927 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4928 END;
4929
4930 IF vl_prg_seg IS NOT NULL THEN
4931 vl_status := 'pass'; EXIT; END IF;
4932
4933 BEGIN
4934 SELECT program_segment,
4935 prc_mapping_flag, prc_header_id
4936 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4937 FROM fv_facts_prc_hdr ffh
4938 WHERE ffh.treasury_symbol_id = -1
4939 AND ffh.code_type = vl_code_type
4940 AND ffh.set_of_books_id = p_sob_id
4941 AND ffh.fund_value = 'ALL-A'
4942 AND fund_rec.fund_category = 'A';
4943 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4944 END;
4945 IF vl_prg_seg IS NOT NULL THEN
4946 vl_status := 'pass';
4947 EXIT;
4948 END IF;
4949
4950 BEGIN
4951 SELECT program_segment,
4952 prc_mapping_flag, prc_header_id
4953 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4954 FROM fv_facts_prc_hdr ffh
4955 WHERE ffh.treasury_symbol_id = -1
4956 AND ffh.code_type = vl_code_type
4957 AND ffh.set_of_books_id = p_sob_id
4958 AND ffh.fund_value = 'ALL-B'
4959 AND fund_rec.fund_category = 'B';
4960 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4961 END;
4962
4963 IF vl_prg_seg IS NOT NULL THEN
4964 vl_status := 'pass'; EXIT; END IF;
4965
4966 BEGIN
4967 SELECT program_segment,
4968 prc_mapping_flag, prc_header_id
4969 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4970 FROM fv_facts_prc_hdr ffh
4971 WHERE ffh.treasury_symbol_id = -1
4972 AND ffh.set_of_books_id = p_sob_id
4973 AND ffh.code_type = vl_code_type
4974 AND ffh.fund_value = 'ALL-FUNDS';
4975 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4976 END;
4977
4978
4979 IF vl_prg_seg IS NOT NULL THEN
4980 vl_status := 'pass'; EXIT; END IF;
4981
4982 vl_status := 'FAIL';
4983
4984 IF vl_code_type = 'B' AND fund_rec.fund_category = 'B' THEN
4985 p_catb_status := 'FAIL' ;
4986 EXIT;
4987 ELSIF vl_code_type = 'N' THEN
4988 p_prn_status := 'FAIL' ;
4989 EXIT;
4990 ELSE
4991 EXIT;
4992 END IF;
4993
4994 END LOOP;
4995
4996 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4997 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4998 'fund_value :'|| fund_rec.fund_value);
4999 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5000 'prg segment :'|| vl_prg_seg);
5001 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5002 'prc flag :'|| vl_prc_flag);
5003 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5004 'prc header id :'||vl_prc_header_id);
5005 END IF;
5006
5007
5008 IF vl_status = 'pass' THEN
5009
5010 v_funds_count := v_funds_count + 1;
5011 v_segs_array(v_funds_count).fund_value := fund_rec.fund_value;
5012 v_segs_array(v_funds_count).segment := vl_prg_seg;
5013 v_segs_array(v_funds_count).prc_flag := vl_prc_flag;
5014 v_segs_array(v_funds_count).prc_header_id := vl_prc_header_id;
5015 v_segs_array(v_funds_count).code_type := vl_code_type;
5016 END IF;
5017 END LOOP;
5018 END LOOP;
5019
5020 EXCEPTION
5021 WHEN OTHERS THEN
5022 vp_errbuf := SQLERRM;
5023 vp_retcode := -1;
5024 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5025 l_module_name||'.final_exception',vp_errbuf);
5026
5027 END check_prc_map_seg;
5028 --------------------------------------------------------------------------------
5029 PROCEDURE get_prc_val(p_catb_program_val IN VARCHAR2,
5030 p_catb_rc_val OUT NOCOPY VARCHAR2,
5031 p_catb_rc_desc OUT NOCOPY VARCHAR2,
5032 p_catb_exception OUT NOCOPY NUMBER,
5033 p_prn_program_val IN VARCHAR2,
5034 p_prn_rc_val OUT NOCOPY VARCHAR2,
5035 p_prn_rc_desc OUT NOCOPY VARCHAR2,
5036 p_prn_exception OUT NOCOPY NUMBER)
5037 IS
5038
5039 l_module_name VARCHAR2(200);
5040
5041 vl_prc_found VARCHAR2(1);
5042 vl_prc_val VARCHAR2(10);
5043 vl_prc_desc VARCHAR2(100);
5044 vl_program_val VARCHAR2(50);
5045 vl_prc_header_id NUMBER(15);
5046 vl_prc_flag VARCHAR2(1);
5047 l_prc_count NUMBER;
5048 vl_exception NUMBER;
5049 vl_seg_txt VARCHAR2(100);
5050 vl_prg_val_set_id NUMBER(15);
5051 vl_segment VARCHAR2(50);
5052 BEGIN
5053
5054 l_module_name := g_module_name || 'get_prc_val';
5055
5056 For I in 1..2
5057 Loop
5058 IF I = 1 THEN
5059 vl_prc_header_id := v_catb_rc_header_id ;
5060 vl_program_val := p_catb_program_val;
5061 vl_prc_flag := v_catb_rc_flag;
5062 vl_prg_val_set_id := v_catb_prg_val_set_id;
5063 vl_segment := v_catb_prg_seg_name;
5064 ELSE
5065 vl_prc_header_id := v_prn_rc_header_id ;
5066 vl_program_val := p_prn_program_val;
5067 vl_prc_flag := v_prn_rc_flag;
5068 vl_prg_val_set_id := v_prn_prg_val_set_id;
5069 vl_segment := v_prn_prg_seg_name;
5070
5071
5072 END IF;
5073
5074 vl_prc_found := 'N';
5075 vl_exception := 0;
5076
5077 IF vl_prc_flag = 'Y' THEN
5078
5079 BEGIN
5080
5081 SELECT LPAD(TO_CHAR(reporting_code), 3, '0'), reporting_desc
5082 INTO vl_prc_val, vl_prc_desc
5083 FROM fv_facts_prc_dtl
5084 WHERE prc_header_id = vl_prc_header_id
5085 AND program_value = vl_program_val
5086 AND set_of_books_id = vp_set_of_books_id;
5087
5088 vl_prc_found := 'Y';
5089
5090 EXCEPTION
5091 WHEN NO_DATA_FOUND THEN NULL;
5092 END;
5093
5094 IF vl_prc_found = 'N' THEN
5095 BEGIN
5096
5097 SELECT LPAD(TO_CHAR(reporting_code), 3, '0'), reporting_desc
5098 INTO vl_prc_val, vl_prc_desc
5099 FROM fv_facts_prc_dtl
5100 WHERE prc_header_id = vl_prc_header_id
5101 AND program_value = 'ALL'
5102 AND set_of_books_id = vp_set_of_books_id;
5103
5104 vl_prc_found := 'Y';
5105
5106 EXCEPTION
5107 WHEN NO_DATA_FOUND THEN NULL;
5108 END;
5109 END IF;
5110
5111 END IF;
5112
5113
5114
5115 IF ((I = 1 AND va_appor_cat_val = 'B' ) OR
5116 i = 2 ) THEN
5117 IF (vl_prc_flag = 'N' OR
5118 vl_prc_found = 'N') THEN
5119 BEGIN
5120 IF LENGTH(vl_program_val) > 3 THEN
5121 vl_exception := 1;
5122 vl_prc_val := NULL;
5123 vl_prc_desc := NULL;
5124
5125 ELSIF
5126 (vl_prc_flag = 'Y' AND
5127 vl_prc_found = 'N' AND I = 2 ) THEN
5128 vl_exception := 1;
5129 vl_prc_val := NULL;
5130 vl_prc_desc := NULL;
5131
5132 ELSE
5133 vl_prc_val := LPAD(TO_CHAR(TO_NUMBER(vl_program_val)),3,'0');
5134 get_segment_text(vl_program_val,
5135 vl_prg_val_set_id,
5136 vl_seg_txt );
5137 IF vp_retcode <> 0 THEN
5138 RETURN ;
5139 END IF ;
5140 vl_prc_desc := vl_seg_txt;
5141 -- If no prc found in the mapping table, then an exception
5142 -- is created.
5143 END IF;
5144
5145 EXCEPTION
5146 WHEN OTHERS THEN
5147 -- If p_program_val is non-numeric, an exception is created.
5148 vl_exception := 1;
5149 END;
5150 END IF;
5151 END IF;
5152
5153
5154
5155 IF I = 1 THEN
5156 IF va_appor_cat_val = 'B' THEN
5157 p_catb_exception := vl_exception;
5158 IF vl_prc_found = 'Y' THEN
5159 p_catb_rc_desc := vl_prc_desc;
5160 p_catb_rc_val := vl_prc_val;
5161 ELSIF (vl_prc_flag = 'N' OR
5162 vl_prc_found = 'N') THEN
5163 p_catb_rc_desc := vl_seg_txt;
5164 p_catb_rc_val := vl_prc_val;
5165 END IF;
5166 ELSE
5167 p_catb_rc_desc := '';
5168 p_catb_rc_val := '000';
5169
5170 END IF;
5171 ELSE
5172 p_prn_exception := vl_exception;
5173 IF vl_prc_found = 'Y' THEN
5174 p_prn_rc_desc := vl_prc_desc;
5175 p_prn_rc_val := vl_prc_val;
5176 ELSIF (vl_prc_flag = 'N' AND
5177 vl_prc_found = 'N') THEN
5178 p_prn_rc_desc := vl_seg_txt;
5179 p_prn_rc_val := vl_prc_val;
5180 ELSIF (vl_prc_flag = 'Y' AND
5181 vl_prc_found = 'N') THEN
5182 p_prn_exception := 0;
5183 p_prn_rc_desc := 'PRC not Assigned';
5184 p_prn_rc_val := '099';
5185 END IF;
5186 END IF;
5187
5188
5189 END LOOP;
5190
5191 EXCEPTION
5192 WHEN OTHERS THEN
5193 vp_errbuf := 'GET_PRC_VAL.'||SQLERRM;
5194 vp_retcode := -1;
5195 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5196 l_module_name||'.final_exception',vp_errbuf);
5197 RAISE;
5198
5199 END get_prc_val;
5200 --------------------------------------------------------------------------------
5201 PROCEDURE create_bulk_file(errbuf OUT NOCOPY VARCHAR2,
5202 retcode OUT NOCOPY NUMBER,
5203 p_ledger_id IN NUMBER)
5204 IS
5205
5206 -- Submits concurrent request FVFCTTRC
5207 l_module_name VARCHAR2(200);
5208 rphase VARCHAR2(80);
5209 rstatus VARCHAR2(80);
5210 dphase VARCHAR2(80);
5211 dstatus VARCHAR2(80);
5212 message VARCHAR2(80);
5213 call_status BOOLEAN;
5214 v1_req_id NUMBER(15);
5215 v2_req_id NUMBER(15);
5216 v3_req_id NUMBER(15);
5217 sob NUMBER(15);
5218
5219 BEGIN
5220 l_module_name := g_module_name || 'create_bulk_file';
5221 sob := p_ledger_id;
5222 -- get the sequence number
5223 SELECT fv_facts_submission_s.nextval
5224 INTO v3_req_id
5225 FROM DUAL;
5226
5227 UPDATE fv_facts_submission
5228 SET bulk_file_sub_id = v3_req_id,
5229 submitted_by = fnd_global.user_name,
5230 facts2_status = 'CREATING BULK FILE'
5231 WHERE bulk_flag = 'Y';
5232 -- Commented as part of the 2005 FACTS II Enhancement
5233 /*
5234 -- Submitting Contact File Generation Process
5235 v1_req_id := FND_REQUEST.SUBMIT_REQUEST(
5236 'FV','FVFCTHRC','','',FALSE,
5237 'FVFCTHRC', v3_req_id ,SOB) ;
5238 COMMIT;
5239
5240 call_status := Fnd_concurrent.Wait_for_request(v1_req_id, 20, 0, rphase,
5241 rstatus, dphase, dstatus, message);
5242 */
5243 -- Submitting FACTS Bulk Detail file Generation Process
5244 -- IF dstatus <> 'ERROR' THEN
5245 v2_req_id := fnd_request.submit_request
5246 ('FV','FVFCTDRC','','',FALSE, 'FVFCTDRC', v3_req_id,SOB) ;
5247 COMMIT;
5248 call_status := fnd_concurrent.wait_for_request(v2_req_id, 0, 0, rphase,
5249 rstatus, dphase, dstatus, message);
5250 IF (upper(dstatus) <> 'ERROR') THEN
5251 UPDATE fv_facts_submission
5252 SET run_mode = 'P',
5253 submitted_by = NULL,
5254 submitted_id = NULL,
5255 facts2_status = NULL,
5256 bulk_flag = 'N'
5257 WHERE bulk_file_sub_id = v3_req_id ;
5258 COMMIT;
5259 END IF;
5260 -- END IF;
5261 EXCEPTION
5262 WHEN OTHERS THEN
5263 errbuf := SQLERRM;
5264 retcode := -1;
5265 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5266 l_module_name||'.final_exception',errbuf);
5267 RAISE;
5268
5269
5270 END create_bulk_file;
5271
5272
5273 --------------------------------------------------------------------------------
5274 BEGIN
5275 g_module_name := 'fv.plsql.FV_FACTS_TRANSACTIONS.';
5276 vc_dept_transfer := ' ' ;
5277 vc_atb_seq_num := '000' ;
5278 vc_record_indicator := 'D' ;
5279 vc_transfer_to_from := ' ' ;
5280 vc_current_permanent_flag := ' ' ;
5281 END;