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