[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_TBAL_TRX
Source
1 PACKAGE BODY FV_FACTS_TBAL_TRX AS
2 /* $Header: FVFCTBPB.pls 120.23 2011/04/23 12:23:45 amaddula ship $ */
3 -- ======================================================================
4 -- Variable Naming Conventions
5 -- ======================================================================
6 -- Parameter variables have the format "vp_<Variable Name>"
7 -- FACTS Attribute Flags have the format "va_<Variable Name>_flag"
8 -- FACTS Attribute values have the format "va_<Variable Name>_val"
9 -- Constant values for the FACTS record
10 -- have the format "vc_<Variable Name>"
11 -- Other Global Variables have the format "v_<Variable_Name>"
12 -- Procedure Level local variables have
13 -- the format "vl_<Variable_Name>"
14 --
15 -- ======================================================================
16 -- Parameters
17 -- ======================================================================
18 g_module_name VARCHAR2(100) := 'fv.plsql.FV_FACTS_TBAL_TRX.';
19 vp_errbuf Varchar2(1000) ;
20 vp_retcode number ;
21 /*
22 Commented by 7324248
23 vp_preparer_id Varchar2(8) ;
24 vp_certifier_id Varchar2(8) ;
25 vp_report_qtr number(1);
26 vp_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE ;
27 vp_summary_type varchar2(1) ;
28 */
29
30 vp_report_fiscal_yr number(4) ;
31
32 vp_currency_code Varchar2(15) ;
33 vp_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE ;
34
35 vp_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE ;
36 vp_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE ;
37
38 vp_fund_low fv_fund_parameters.fund_value%type ;
39 vp_fund_high fv_fund_parameters.fund_value%type ;
40 vp_period_name gl_period_statuses.period_name%type ;
41 vp_report_id number;
42 vp_output_format varchar2(30);
43 vp_attribute_set varchar2(80);
44
45 -- ======================================================================
46 -- FACTS Attributes
47 -- ======================================================================
48 va_balance_type_flag Varchar2(1) ;
49 va_public_law_code_flag Varchar2(1) ;
50 va_reimburseable_flag Varchar2(1) ;
51 va_bea_category_flag Varchar2(1) ;
52 va_appor_cat_flag Varchar2(1) ;
53 va_borrowing_source_flag Varchar2(1) ;
54 va_def_indef_flag Varchar2(1) ;
55 va_legis_ind_flag Varchar2(1) ;
56 va_pya_flag Varchar2(1) ;
57 va_authority_type_flag Varchar2(1) ;
58 va_function_flag Varchar2(1) ;
59 va_availability_flag Varchar2(1) ;
60 va_def_liquid_flag Varchar2(1) ;
61 va_deficiency_flag Varchar2(1) ;
62 va_transaction_partner_val Varchar2(1) ;
63 va_cohort Varchar2(2) ;
64 va_def_indef_val Varchar2(1) ;
65 va_appor_cat_b_dtl Varchar2(3) ;
66 va_appor_cat_b_txt Varchar2(25) ;
67 va_public_law_code_val Varchar2(7) ;
68 va_appor_cat_val Varchar2(1) ;
69 va_authority_type_val Varchar2(1) ;
70 va_reimburseable_val Varchar2(1) ;
71 va_bea_category_val Varchar2(5) ;
72 va_borrowing_source_val Varchar2(6) ;
73 va_deficiency_val Varchar2(1) ;
74 va_legis_ind_val Varchar2(1) ;
75 va_pya_val Varchar2(1) ;
76 va_balance_type_val Varchar2(1) ;
77 va_budget_function VARCHAR2(3) ;
78 va_advance_flag VARCHAR2(1) ;
79 va_transfer_ind VARCHAR2(1) ;
80 va_advance_type_val VARCHAR2(1) ;
81 va_transfer_dept_id VARCHAR2(2) ;
82 va_transfer_main_acct VARCHAR2(4) ;
83 va_account_ctr NUMBER:=0;
84
85 va_pl_code_col VARCHAR2(25);
86 va_advance_type_col VARCHAR2(25);
87 va_tr_dept_id_col VARCHAR2(25);
88 va_tr_main_acct_col VARCHAR2(25);
89
90 va_prn_num VARCHAR2(3);
91 va_prn_txt VARCHAR2(25);
92
93 -- ======================================================================
94 -- FACTS File Constants
95 -- ======================================================================
96 vc_fiscal_yr Varchar2(4) ;
97 vc_dept_regular Varchar2(2) ;
98 -- Bug 10258061: Increased width to 3
99 vc_dept_transfer Varchar2(3) := ' ' ;
100 vc_main_account Varchar2(4) ;
101 vc_sub_acct_symbol Varchar2(3) ;
102 vc_acct_split_seq_num Varchar2(3) ;
103 /*
104 Commented by 7324248
105 vc_maf_seq_num Varchar2(3) ;
106 vc_atb_seq_num Varchar2(3) := '000' ;
107 vc_record_indicator Varchar2(1) := 'D' ;
108 vc_transfer_to_from Varchar2(1) := ' ' ;
109 vc_current_permanent_flag Varchar2(1) := ' ' ;
110 */
111
112 vc_rpt_fiscal_yr Varchar2(4) ;
113 vc_rpt_fiscal_month Varchar2(2) ;
114
115 -- ======================================================================
116 -- Other GLOBAL Variables
117 -- ======================================================================
118 -- ------------------------------
119 -- Period Declarations
120 -- -----------------------------
121 v_begin_period_name gl_period_statuses.period_name%TYPE ;
122 v_begin_period_start_dt date ;
123 v_begin_period_end_dt date ;
124 v_begin_period_num gl_period_statuses.period_num%TYPE ;
125 v_period_name gl_period_statuses.period_name%TYPE ;
126 v_period_start_dt date ;
127 v_period_end_dt date ;
128 v_period_num gl_period_statuses.period_num%TYPE ;
129 v_bal_seg_name Varchar2(20) ;
130 v_acc_seg_name Varchar2(20) ;
131 v_catb_prg_seg_name Varchar2(20) ;
132 v_prn_prg_seg_name Varchar2(20) ;
133 v_cohort_seg_name Varchar2(20) ;
134 v_fyr_segment_name varchar2(20);
135 v_acc_val_set_id fnd_flex_value_sets.flex_value_set_id%TYPE ;
136 v_catb_prg_val_set_id fnd_flex_value_sets.flex_value_set_id%TYPE ;
137 v_prn_prg_val_set_id fnd_flex_value_sets.flex_value_set_id%TYPE ;
138 v_cohort_select Varchar2(20) ;
139 v_cohort_where Varchar2(120) ;
140 v_chart_of_accounts_id gl_code_combinations.chart_of_accounts_id%TYPE ;
141 /*
142 Commented by 7324248
143 v_acct_num fv_Facts_attributes.facts_acct_number%TYPE ;
144 v_g_edit_check_code number(15);
145 v_acct_attr_flag Varchar2(1) ;
146 */
147 v_sgl_acct_num fv_facts_ussgl_accounts.ussgl_account%TYPE ;
148 v_ccid number;
149 vl_ccid number;
150
151 v_amount Number ;
152 v_period_cr Number ;
153 v_period_dr Number ;
154 vl_retcode Number ;
155 v_begin_amount number ;
156 v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE ;
157 v_record_category fv_facts_temp.fct_int_record_category%TYPE ;
158 v_fiscal_yr Varchar2(25);
159 v_segment varchar2(30);
160 v_year_gtn2001 BOOLEAN ;
161 v_time_frame fv_treasury_symbols.time_frame%TYPE ;
162 v_financing_acct fv_facts_federal_accounts.financing_account%TYPE ;
163 v_year_budget_auth VARCHAR2(3);
164
165 /*
166 Commented as not used
167 v_tbal_run_flag Varchar2(1) ;
168 v_tbal_indicator FV_FACTS_TEMP.TBAL_INDICATOR%TYPE ;
169 v_edit_check_code Number ;
170 v_debug varchar2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
171 v_vl_main_cursor_found varchar2(1) := 'N' ;
172 v_code_combination_id gl_code_combinations.code_combination_id%TYPE;
173 */
174
175 v_tbal_fund_value FV_FUND_PARAMETERS.FUND_VALUE%TYPE ;
176 v_tbal_acct_num varchar2(25);
177
178 v_fund_value FV_FUND_PARAMETERS.FUND_VALUE%TYPE ;
179 v_rec_count number(3);
180 vl_pagebreak varchar2(30);
181 v_fund_count number(3);
182 vg_amount NUMBER;
183 v_dummy_cohort VARCHAR2(25);
184
185 v_period_activity NUMBER;
186
187 v_facts_attributes_setup BOOLEAN ;
188
189 v_catb_rc_flag VARCHAR2(1);
190 v_catb_rc_header_id NUMBER;
191 v_prn_rc_flag VARCHAR2(1);
192 v_prn_rc_header_id NUMBER;
193
194 /*
195 * Added for 7324248
196 */
197 g_reimb_agree_seg_name VARCHAR2(25);
198 v_reimb_agree_select VARCHAR2(25);
199
200
201 error_code BOOLEAN;
202 error_message VARCHAR2(600);
203
204 -- PROCEDURE process_cat_b_seq(reported_type IN VARCHAR2);
205 PROCEDURE get_prc_val(p_catb_program_val IN VARCHAR2,
206 p_catb_rc_val OUT NOCOPY VARCHAR2,
207 p_catb_rc_desc OUT NOCOPY VARCHAR2,
208 p_prn_program_val IN VARCHAR2,
209 p_prn_rc_val OUT NOCOPY VARCHAR2,
210 p_prn_rc_desc OUT NOCOPY VARCHAR2);
211
212 /*
213 * Added by 7324248
214 */
215 PROCEDURE get_trx_part_from_reimb
216 (p_reimb_agree_seg_val IN VARCHAR2);
217
218 -- ====================================================================================================
219 PROCEDURE select_group_by_columns(x_report_id IN number,
220 x_attribute_set IN VARCHAR2,
221 x_group_by out NOCOPY varchar2)
222 is
223 l_module_name VARCHAR2(200) := g_module_name || 'select_group_by_columns';
224
225 cursor c_group IS SELECT COLUMN_NAME
226 from fa_rx_rep_columns_b
227 WHERE REPORT_id = x_report_id
228 and attribute_set = x_attribute_set
229 AND BREAK = 'Y';
230 begin
231
232 for crec in c_group
233 Loop
234 if crec.column_name like 'SEGMENT%'
235 then
236 if x_group_by is not null
237 then
238 x_group_by := x_group_by || ',' ;
239 End if;
240 x_group_by := x_group_by || 'glcc.' || crec.column_name;
241 End if;
242
243 end loop;
244
245 if x_group_by is not null
246 then
247 x_group_by := ',' || x_group_by;
248 end if;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 vp_retcode := sqlcode ;
253 vp_errbuf := sqlerrm ;
254 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
255 RAISE;
256
257 End;
258
259
260 -- -----------------------------------------------------------------------------------
261 -- Procedure Definitions
262 -- -----------------------------------------------------------------------------------
263
264 Procedure GET_SGL_PARENT(
265 Acct_num Varchar2,
266 parent_ac OUT NOCOPY Varchar2,
267 sgl_acct_num OUT NOCOPY Varchar2) ;
268
269 -- Gets all information related to the current and beginning period.
270 -- (Period Number, Start Date, End Date and Year Start Date Etc.
271 Procedure GET_PERIOD_INFO ;
272
273 -- Gets all values that remain constant throughout the FACTS output file.
274 Procedure GET_TREASURY_SYMBOL_INFO ;
275
276 -- Processes FACTS Transactions
277 Procedure PROCESS_FACTS_TRANSACTIONS ;
278
279
280 -- Gets all the FACTS attributes and direct pull up values for the passed
281 -- account number
282 Procedure LOAD_FACTS_ATTRIBUTES (Acct_num Varchar2,
283 Fund_val Varchar2,
284 v_retcode out NOCOPY number) ;
285
286 -- Creates a FACTS Temp table record with the current values from the
287 -- variables, based on the balance type.(B-Beginning, E-Ending)
288 Procedure CREATE_FACTS_RECORD ;
289
290 -- Get the Program segment name for the current fund value
291 Procedure GET_PROGRAM_SEGMENT (v_fund_value Varchar2) ;
292
293 -- Get the Apportionment Category B Information
294 -- PROCEDURE GET_APPOR_CAT_B_TEXT(program Varchar2) ;
295
296 PROCEDURE get_segment_text(p_program IN VARCHAR2,
297 p_prg_val_set_id IN NUMBER,
298 p_seg_txt OUT NOCOPY VARCHAR2);
299
300 -- Calculates the Balance of the passed period for the current account
301 -- number and Fund Value and cohort segment (if required) combinations.
302
303 Procedure CALC_BALANCE (ccid NUMBER,
304 Fund_value Varchar2,
305 acct_num Varchar2,
306 period_num Number,
307 period_year NUMBER,
308 Balance_Type Varchar2,
309 fiscal_year VARCHAR2,
310 amount OUT NOCOPY Number,
311 period_activity OUT NOCOPY NUMBER,
312 pagebreak VARCHAR2 DEFAULT NULL);
313
314 -- Build the Select stmt for Apportionment Category Processing
315 -- based on the values in the varuables
316 Procedure Build_Appor_select (ccid NUMBER,
317 Acct_number Varchar2,
318 Fund_Value Varchar2,
319 fiscal_year Varchar2,
320 Appor_period Varchar2,
321 select_stmt OUT NOCOPY Varchar2) ;
322
323 --Loads the Treasury Symbol_id into the global variable
324 Procedure Load_Treasury_Symbol_Id(p_flex_value VARCHAR2) ;
325
326 --- Rolling up the records
327 Procedure FACTS_ROLLUP_RECORDS ;
328 -- This procedure is called to execute the trial balance process
329 -- based on the range of funds (fund_low and fund_high parameters)
330 -- that are passed.
331 Procedure PROCESS_BY_FUND_RANGE ;
332
333 -- This procedure does the processing for each fund within the
334 -- the range of funds (fund_low and fund_high parameters)
335 -- that are passed.
336 Procedure PROCESS_EACH_FUND ;
337
338
339 -- ==================================================================================================
340 procedure DEFAULT_PROCESSING(vl_ccid number,
341 vl_fund_value varchar2,
342 vl_acct_num varchar2,
343 rec_cat varchar2 := 'R',
344 pagebreak varchar2 := '')
345 is
346 l_module_name VARCHAR2(200) := g_module_name || 'DEFAULT_PROCESSING';
347 vl_amount number(25,2);
348 vl_period_activity number(25,2);
349 begin
350 -------------- Normal Processing ----------------
351 -- Only done on the following conditions
352 -- IF FACTS is run and no Apportionment category B Processing or
353 -- Legislation Indicator processing is done.
354 -- If FACTS is run and program segment cannot be found for Apportionment
355 -- Category B Processing
356
357 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Normal Processing ') ;
359 End If ;
360 va_balance_type_val := 'B' ;
361 v_record_category := 'REPORTED' ;
362 CALC_BALANCE (vl_ccid,
363 vl_fund_value,
364 vl_acct_num,
365 v_period_num,
366 vp_report_fiscal_yr,
367 'B',
368 v_fiscal_yr,
369 vl_amount,
370 vl_period_activity,
371 pagebreak) ;
372 v_amount := vl_amount ;
373 v_period_activity := vl_period_activity;
374 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
375 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Ending Balance(Normal) -> '||v_amount);
376 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'period_activity --> '||v_period_activity);
377 END IF;
378 v_record_category := 'REPORTED';
379 v_tbal_fund_value := vl_fund_value ;
380 Create_Facts_Record ;
381 If vp_retcode <> 0 Then
382 Return ;
383 End If ;
384 EXCEPTION
385 WHEN OTHERS THEN
386 vp_retcode := sqlcode ;
387 vp_errbuf := sqlerrm ;
388 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
389 RAISE;
390
391 End;
392 -- ------------------------------------------------------------------
393 -- PROCEDURE MAIN
394 -- ----------------------------------------------------------------------------
395 -- Main procedure that is called to execute Trial Balance process.This calls
396 -- all subsequent procedures that are part of the Trial balance process.
397 -- ----------------------------------------------------------------------------
398 Procedure MAIN(
399 Errbuf OUT NOCOPY Varchar2,
400 retcode OUT NOCOPY Varchar2,
401 Set_Of_Books_Id Number,
402 COA_Id Number,
403 Fund_Low Varchar2,
404 Fund_High Varchar2,
405 currency_code Varchar2,
406 Period Varchar2,
407 report_id number,
408 attribute_set varchar2,
409 output_format varchar2)
410
411 IS
412 l_module_name VARCHAR2(200) := g_module_name || 'MAIN';
413 BEGIN
414 -- Modified the code for the bug 1399282
415 -- Load FACTS Parameters into Global Variables
416 vp_set_of_books_id := set_of_books_id ;
417 vp_coa_id := coa_id ;
418 vp_fund_low := fund_low ;
419 vp_fund_high := fund_high ;
420 vp_period_name := period ;
421 vp_currency_code := currency_code ;
422 vp_report_id := report_id;
423 vp_attribute_set := attribute_set;
424 vp_output_format := output_format;
425 vp_retcode := 0 ;
426 /*
427 * Added by 7324248
428 */
429
430 fv_utility.log_mesg('Parameters:');
431 fv_utility.log_mesg('Set_Of_Books_Id:'||Set_Of_Books_Id);
432 fv_utility.log_mesg('COA_Id:'||COA_Id);
433 fv_utility.log_mesg('Fund_Low:'||Fund_Low);
434 fv_utility.log_mesg('Fund_High:'||Fund_High);
435 fv_utility.log_mesg('currency_code:'||currency_code);
436 fv_utility.log_mesg('Period:'||Period);
437
438
439 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
440 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
441 'Running Trial balance by fund fund range ' ||
442 vp_fund_low || ' ' || vp_fund_high) ;
443 End If ;
444
445 fv_utility.log_mesg('Before deleting from FV_FACTS_TEMP ');
446 DELETE FROM fv_facts_temp
447 WHERE fct_int_record_type = 'TB';
448 COMMIT;
449 fv_utility.log_mesg('After deleting from FV_FACTS_TEMP AND BEFORE PROCESS_BY_FUND_RANGE ');
450 PROCESS_BY_FUND_RANGE ;
451 fv_utility.log_mesg('After PROCESS_BY_FUND_RANGE ');
452 fv_utility.log_mesg('vp_retcode :: '||vp_retcode);
453
454 If vp_retcode = 0 Then
455
456 IF NOT v_facts_attributes_setup THEN
457 retcode := 1 ;
458 errbuf :=
459 'Trial Balance by Fund Range Process completed with warning because
460 the Public Law, Advance, and Transfer attribute columns are not
461 established on the Define System Parameters Form.';
462 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
463 'Trial Balance by Fund Range Process completed with warning because
464 the Public Law, Advance, and Transfer attribute columns are not
465 established on the Define System Parameters Form.');
466 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf);
467 ELSE
468 errbuf := 'Trial Balance By Fund Range Process Completed Successfully' ;
469 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
470 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, errbuf);
471 END IF ;
472 END IF;
473 fv_utility.log_mesg('errbuf :: '||errbuf);
474 COMMIT ;
475 ELSE
476 retcode := vp_retcode ;
477 errbuf := vp_errbuf ;
478 fv_utility.log_mesg('retcode :: errbuf :: '||retcode||'::'||errbuf);
479 Rollback ;
480 End If ;
481 EXCEPTION
482 -- Exception Processing
483 When Others Then
484 vp_retcode := sqlcode ;
485 vp_errbuf := sqlerrm || ' [TRIAL_BALANCE_MAIN] ' ;
486 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
487 '.final_exception',vp_errbuf);
488 END MAIN ;
489
490 -- -------------------------------------------------------------------
491 -- PROCEDURE GET_PERIOD_INFO
492 -- -------------------------------------------------------------------
493 -- Gets the Period infomation like Period Number, Period_year,
494 -- quarter number and other corresponding period information based on
495 -- the quarter number passed to the Main Procedure
496 -- ------------------------------------------------------------------
497 Procedure GET_PERIOD_INFO
498 IS
499 l_module_name VARCHAR2(200) := g_module_name || 'GET_PERIOD_INFO';
500 BEGIN
501 -- Modified the code for the bug 1399282
502 -- When called from Trial Balance process, the parameter passed is period name.
503 v_period_name := vp_period_name;
504 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
505 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
506 'period name '||vp_period_name) ;
507 END IF;
508 Begin
509 Select period_year,period_num,start_date,end_date
510 Into vp_report_fiscal_yr,v_period_num,v_period_start_dt,v_period_end_dt
511 From gl_period_statuses
512 Where ledger_id = vp_set_of_books_id
513 And application_id = 101
514 And period_name = vp_period_name;
515 Exception
516 When OTHERS then
517 vp_retcode := -1 ;
518 vp_errbuf := 'Error Getting Period Year and Period Number for the passed
519 Period [GET_PERIOD_INFO]' ;
520 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
521 '.select1', vp_errbuf) ;
522 End;
523 Begin
524 -- Select Period Information for Beginning Period
525 Select period_name,
526 start_date,
527 end_date,
528 period_num
529 Into v_begin_period_name,
530 v_begin_period_start_dt,
531 v_begin_period_end_dt,
532 v_begin_period_num
533 from gl_period_statuses
534 where (start_date,period_num) IN (Select MIN(year_start_date),MIN(period_num)
535 from gl_period_statuses
536 where period_year = vp_report_fiscal_yr
537 and ledger_id = vp_set_of_books_id)
538 and application_id = 101
539 and ledger_id = vp_set_of_books_id ;
540 Exception
541 When NO_DATA_FOUND Then
542 vp_retcode := -1 ;
543 vp_errbuf := 'Error Getting Beginning Period Information
544 [GET_PERIOD_INFO]' ;
545 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
546 Return ;
547 When TOO_MANY_ROWS Then
548 vp_retcode := -1 ;
549 vp_errbuf := 'More than one Beginning Period Returned !!
550 [GET_PERIOD_INFO]' ;
551 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, vp_errbuf) ;
552 Return ;
553 End ;
554 EXCEPTION
555 -- Exception Processing
556 When Others Then
557 vp_retcode := sqlcode ;
558 vp_errbuf := sqlerrm || ' [GET_PERIOD_INFO] ' ;
559 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
560 Return ;
561 END GET_PERIOD_INFO ;
562 -- -------------------------------------------------------------------
563 -- PROCEDURE GET_TREASURY_SYMBOL_INFO
564 -- -------------------------------------------------------------------
565 -- Gets all the information that remains contant throughout the
566 -- FACTS output file. These Information include :
567 --
568 -- DEPT_REGULAR DEPT_TRANSFER, FISCAL_YEAR,
569 -- MAIN_ACCOUNT SUB_ACCT_SYMBOL ACCT_SPLIT_SEQ_NUM
570 -- MAF_SPLIT_SEQ_NUM ATB_SEQ_NUM PREPARER_ID,
571 -- CERTIFIER_ID RPT_FISCAL_YEAR RPT_FISCAL_MONTH
572 -- RECORD_INDICATOR TRANSFER_AGENCY TRANSFER_ACCT
573 -- TRANSFER_TO_FROM YEAR_BUDGET_AUTH ADVANCE_FLAG
574 -- CURRENT_PERMANENT_FLAG FUNCTION
575 --
576 -- ------------------------------------------------------------------
577 Procedure GET_TREASURY_SYMBOL_INFO
578 IS
579 l_module_name VARCHAR2(200) := g_module_name || 'GET_TREASURY_SYMBOL_INFO';
580 -- Commented bY 7324248
581 --vl_fund_category Varchar2(1) ;
582 vl_resource_type Varchar2(80) ;
583 vl_time_frame Varchar2(25) ;
584 vl_established_fy Number ;
585 vl_financing_acct Varchar2(1) ;
586 vl_years_available Number ;
587 vl_fiscal_month_count NUMBER ;
588 BEGIN
589
590 /*Populating the Cohort Segment Name */
591 SELECT cohort_segment_name
592 INTO v_cohort_seg_name
593 FROM fv_pya_fiscalyear_segment
594 WHERE set_of_books_id = vp_set_of_books_id;
595
596 Select
597 FTS.resource_type,
598 RPAD(FFFA.Treasury_dept_code, 2),
599 FTS.Time_Frame,
600 FTS.Established_Fiscal_yr,
601 FTS.financing_account,
602 -- FTS.cohort_segment_name,
603 RPAD(FFFA.Treasury_acct_code, 4),
604 NVL(LPAD(FTS.Tafs_sub_acct,3, '0'),'000'),
605 NVL(LPAD(FTS.Tafs_split_code, 3, '0'),'000'),
606 FTS.years_available,
607 fts.dept_transfer
608 Into
609 vl_resource_type,
610 vc_dept_regular,
611 vl_time_frame,
612 vl_established_fy,
613 vl_financing_acct,
614 -- v_cohort_seg_name,
615 vc_main_account,
616 vc_sub_acct_symbol,
617 vc_acct_split_seq_num,
618 vl_years_available,
619 vc_dept_transfer
620 From
621 FV_FACTS_FEDERAL_ACCOUNTS FFFA,
622 FV_TREASURY_SYMBOLS FTS
623 Where FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
624 AND (fts.FACTS_REPORTABLE_INDICATOR like 'II' or fts.FACTS_REPORTABLE_INDICATOR like 'I and II')
625 AND FTS.treasury_symbol = vp_treasury_symbol
626 AND FTS.set_of_books_id = vp_set_of_books_id
627 AND FFFA.set_of_books_id = vp_set_of_books_id ;
628 --
629 v_time_frame := vl_time_frame;
630 v_financing_acct := vl_financing_acct;
631 IF v_year_gtn2001 THEN
632 vc_acct_split_seq_num := '000';
633 END IF;
634 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Financing Acct >>> - ' ||
636 vl_financing_acct || ' >>>> - Cohort Seg Name - ' ||
637 v_cohort_seg_name) ;
638 End If ;
639 ------------------------------------------------
640 -- Deriving COHORT Value
641 ------------------------------------------------
642 If vl_financing_acct NOT IN ('D', 'G') Then
643 -- Consider COHORT value only for 'D' and 'G' financing Accounts
644 v_cohort_seg_name := NULL ;
645 End If ;
646 -- Deriving FISCAL_YEAR
647 If vl_time_frame IN ('SINGLE','A') Then
648 vc_fiscal_yr := ' ' || substr(to_char(vl_established_fy), 3, 2) ;
649 ElsIf vl_time_frame IN ('NO_YEAR', 'REVOLVING','X') Then
650 vc_fiscal_yr := ' X' ;
651 ElsIf vl_time_frame IN ('MULTIPLE','M') Then
652 vc_fiscal_yr := substr(to_char(vl_established_fy), 3,2) ||
653 substr(to_char(vl_established_fy + vl_years_available - 1),3,2) ;
654 End If ;
655 -- Preparer Id and Certifier Id and rpt_fiscal_yr
656 -- are derived from Parameters
657 vc_rpt_fiscal_yr := LPAD(to_char(vp_report_fiscal_yr), 4) ;
658 -- vc_rpt_fiscal_month := ltrim(to_char(v_period_num,'09')) ;
659 -- Bug 2774542
660
661 SELECT to_char(count(*) , '09')
662 INTO vl_fiscal_month_count
663 FROM gl_period_statuses
664 WHERE ledger_id = vp_set_of_books_id
665 AND application_id = 101
666 AND period_year = vp_report_fiscal_yr
667 AND adjustment_period_flag = 'N'
668 AND period_num <= v_period_num ;
669
670 vc_rpt_fiscal_month := ltrim(to_char(vl_fiscal_month_count,'09')) ;
671
672 -- Year Budget Auth is derived from the parameters
673 --
674 -- vc_year_budget_auth := vc_rpt_fiscal_yr ;
675 EXCEPTION
676 When NO_DATA_FOUND Then
677 vp_retcode := -1 ;
678 vp_errbuf := 'Error Getting Treasury Symbol related Information
679 for the passed Treasury Symbol [GET_TREASURY_SYMBOL_INFO] ' ;
680 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1', vp_errbuf) ;
681 When TOO_MANY_ROWS Then
682 vp_retcode := -1 ;
683 vp_errbuf := 'More than one set of information returned for the
684 passed Treasury Symbol [GET_TREASURY_SYMBOL_INFO]' ;
685 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception2', vp_errbuf) ;
686 WHEN OTHERS THEN
687 vp_retcode := sqlcode ;
688 vp_errbuf := sqlerrm ;
689 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
690 RAISE;
691 END GET_TREASURY_SYMBOL_INFO ;
692 -- -------------------------------------------------------------------
693 -- PROCEDURE PROCESS_FACTS_TRANSACTIONS
694 -- -------------------------------------------------------------------
695 -- This procedure selets all the transactions that needs to be
696 -- analyzed for reporting in the FACTS output file. After getting the
697 -- list of trasnactions that needs to be reported, it applies all the
698 -- FACTS attributes for the account number and perform further
699 -- processing for Legislative Indicator and Apportionment Category.
700 -- It populates the table FV_FACTS_TEMP for edit check process to
701 -- perform edit checks.
702 -- ------------------------------------------------------------------
703 PROCEDURE PROCESS_FACTS_TRANSACTIONS
704 IS
705 l_module_name VARCHAR2(200) := g_module_name || 'PROCESS_FACTS_TRANSACTIONS';
706 /*
707 * Commented by 7324248
708 vl_ret_val Boolean := TRUE ;
709 vl_appor_ctr Number ;
710 vl_sgl_acct_num Varchar2(25) ;
711 vl_sgl_acct_num_bak Varchar2(25);
712 vl_period_net Number ;
713 -- vl_count Varchar2(10) ;
714 vl_old_exception Varchar2(30) := ' ' ;
715 vl_old_acct_num Varchar2(25) := ' ' ;
716 vl_tran_type Varchar2(25) ;
717 vl_exception Varchar2(30) ;
718 vl_cohort_select Varchar2(25) ;
719 vl_cohort_group Varchar2(25) ;
720 vl_req_id Number ;
721 vl_exists Varchar2(1) ;
722 vl_type Varchar2(3) ;
723 vl_code_combination_id VARCHAR2(25);
724 vl_pub_ctrl NUMBER(15):=0;
725 vl_segment varchar2(30);
726 */
727
728 vl_exec_ret Integer ;
729 vl_main_cursor Integer ;
730 vl_main_select Varchar2(6000) ;
731 vl_main_fetch Integer ;
732 vl_legis_cursor Integer ;
733 vl_legis_select Varchar2(6000) ;
734 vl_legis_ref Varchar2(240) ;
735 vl_legis_amount Number := 0 ;
736 vl_effective_date DATE;
737 vl_appor_cursor Integer ;
738 vl_appor_select Varchar2(6000) ;
739 vl_appor_period varchar2(100) ;
740
741 vl_fund_value Varchar2(25) ;
742 vl_acct_num Varchar2(25) ;
743
744 vl_catb_program Varchar2(25) ;
745 vl_prn_program varchar2(25) ;
746
747 vl_cohort_yr Varchar2(25) ;
748
749 vl_amount Number ;
750
751 vl_row_count Number := 0 ;
752
753 vl_period_name gl_je_lines.period_name%TYPE;
754 vl_adj_flag VARCHAR2(1);
755 vl_adj_num NUMBER;
756 vl_attributes_found varchar2(1) ;
757 vl_period_activity NUMBER;
758
759 vl_parent_ac varchar2(60);
760
761
762 vl_exception_cat NUMBER := 0;
763
764 vl_je_source gl_je_headers.je_source%TYPE;
765 vl_pl_code VARCHAR2(150);
766 vl_tr_main_acct VARCHAR2(150);
767 vl_tr_dept_id VARCHAR2(150);
768 vl_advance_type VARCHAR2(150);
769 vl_count NUMBER;
770
771 vl_catb_rc_val VARCHAR2(3);
772 vl_catb_pgm_desc VARCHAR2(25);
773 vl_prn_rc_val VARCHAR2(3);
774 vl_prn_pgm_desc VARCHAR2(25);
775 vl_counter NUMBER;
776 vb_balance_amount NUMBER;
777 das_id NUMBER;
778 das_where VARCHAR2(600);
779 vl_je_batch_id number(15);
780 vl_je_header_id number(15);
781 vl_je_line_num number(15);
782 vl_je_sla_flag varchar2(1);
783 /*
784 * Added by 7324248
785 */
786 vl_reimb_agree_val VARCHAR2(25);
787 l_counter NUMBER;
788
789 --- added for bug 6409180
790 cursor be_cur is
791 select vl_legis_ref transaction_id, vl_legis_amount amount
792 from dual
793 where nvl(vl_je_sla_flag ,'N') = 'N'
794 union all
795 SELECT to_char(xd.source_distribution_id_num_1) transaction_id,
796 (NVL(xd.unrounded_accounted_dr,0) -
797 NVL(xd.unrounded_accounted_cr,0)) amount
798 FROM gl_import_references gli,
799 xla_ae_lines xl,
800 xla_ae_headers xh,
801 xla_distribution_links xd
802 WHERE gli.je_batch_id = vl_je_batch_id
803 AND gli.je_header_id = vl_je_header_id
804 AND gli.je_line_num = vl_je_line_num
805 AND xl.gl_sl_link_id = gli.gl_sl_link_id
806 AND xl.application_id = 8901
807 AND xh.ae_header_id = xl.ae_header_id
808 AND xl.ledger_id = vp_set_of_books_id
809 AND xd.event_id = xh.event_id
810 and xd.ae_header_id = xh.ae_header_id
811 and xd.ae_line_num = xl.ae_line_num
812 and nvl(vl_je_sla_flag ,'N') = 'Y';
813 BEGIN
814 -- Get all the transaction balances for the combinations that have
815 -- fund values which are associated with the passed Treasury
816 -- Symbol. Sum all the amounts and group the data by Account Number
817 -- and Fund Value.
818 -- Dynamic SQL is used for declaring the following cursor and to
819 -- fetch the values.
820 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
821 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Selecting FACTS Transactions.....') ;
822 END IF;
823 Begin
824 vl_main_cursor := DBMS_SQL.OPEN_CURSOR ;
825 Exception
826 When Others Then
827 vp_retcode := sqlcode ;
828 VP_ERRBUF := sqlerrm ;
829 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.open_vl_main_cursor', vp_errbuf) ;
830 Return ;
831 End ;
832 If v_cohort_seg_name IS NOT NULL Then
833 v_cohort_select := ', GLCC.' || v_cohort_seg_name ;
834 Else
835 v_cohort_select := ' ' ;
836 End If ;
837
838 v_segment := ' ';
839
840 vl_main_select :=
841 'Select
842 GLCC.code_combination_id , GLCC.' || v_acc_seg_name ||
843 ', GLCC.' || v_bal_seg_name ||
844 ', GLCC.' || v_fyr_segment_name ||
845 ', SUM((glb.begin_balance_dr - glb.begin_balance_cr) +
846 (glb.period_net_dr - period_net_cr)) '||
847 v_segment ||
848 v_cohort_select ||
849 v_reimb_agree_select ||
850 ' From GL_BALANCES GLB,
851 GL_CODE_COMBINATIONS GLCC
852 WHERE GLB.code_combination_id = GLCC.code_combination_id ';
853
854 fv_utility.log_mesg('v_reimb_agree_select ::'||v_reimb_agree_select);
855
856 -- Data Access Security
857 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
858 das_where := gl_access_set_security_pkg.get_security_clause
859 (das_id,
860 gl_access_set_security_pkg.READ_ONLY_ACCESS,
861 gl_access_set_security_pkg.CHECK_LEDGER_ID,
862 to_char(vp_set_of_books_id), 'GLB',
863 gl_access_set_security_pkg.CHECK_SEGVALS,
864 null, 'GLCC', null);
865 IF (das_where IS NOT NULL) THEN
866 vl_main_select := vl_main_select || 'AND ' || das_where;
867 END IF;
868
869
870 vl_main_select := vl_main_select ||
871 ' AND glb.actual_flag = :actual_flag
872 AND GLB.TEMPLATE_ID IS NULL
873 AND GLCC.' || v_bal_seg_name || ' = :fund_value
874 AND GLB.ledger_id = :set_of_books_id
875 AND GLB.PERIOD_YEAR = :report_fiscal_yr
876 AND glb.currency_code = :currency_code
877 GROUP BY GLCC.code_combination_id ,
878 GLCC.' || v_acc_seg_name ||
879 ', GLCC.' || v_bal_seg_name ||
880 ', GLCC.' || v_fyr_segment_name
881 || v_segment ||v_cohort_select ||
882 ' ORDER BY GLCC.' || v_acc_seg_name ;
883
884 Begin
885 dbms_sql.parse(vl_main_cursor, vl_main_select, DBMS_SQL.V7) ;
886 Exception
887 When Others Then
888 vp_retcode := sqlcode ;
889 VP_ERRBUF := sqlerrm ;
890 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
891 '.parse_vl_main_cursor', vp_errbuf) ;
892 Return ;
893 End ;
894
895 -- Bind the variables
896 dbms_sql.bind_variable(vl_main_cursor,':actual_flag', 'A');
897 dbms_sql.bind_variable(vl_main_cursor,':fund_value', v_fund_value);
898 dbms_sql.bind_variable(vl_main_cursor,':set_of_books_id', vp_set_of_books_id);
899 dbms_sql.bind_variable(vl_main_cursor,':report_fiscal_yr', vp_report_fiscal_yr);
900 dbms_sql.bind_variable(vl_main_cursor,':currency_code', vp_currency_code);
901
902 fv_utility.log_mesg('fund_value :: vp_set_of_books_id::vp_report_fiscal_yr::'||v_fund_value||'|'||vp_set_of_books_id||'|'||vp_report_fiscal_yr);
903 dbms_sql.define_column(vl_main_cursor, 1, vl_ccid);
904 dbms_sql.define_column(vl_main_cursor, 2, vl_acct_num, 25);
905 dbms_sql.define_column(vl_main_cursor, 3, vl_fund_value, 25);
906 dbms_sql.define_column(vl_main_cursor, 4, v_fiscal_yr, 25);
907 dbms_sql.define_column(vl_main_cursor, 5, vg_amount);
908 -- Added by 7324248
909 l_counter := 6;
910
911 IF v_cohort_seg_name IS NOT NULL THEN
912 dbms_sql.define_column(vl_main_cursor, l_counter, vl_cohort_yr, 25);
913 l_counter:=l_counter+1;
914 END IF;
915
916 Begin
917 vl_exec_ret := dbms_sql.execute(vl_main_cursor);
918 Exception
919 When Others Then
920 vp_retcode := sqlcode ;
921 VP_ERRBUF := sqlerrm ;
922 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
923 '.execute_vl_main_cursor', vp_errbuf) ;
924 Return ;
925 End ;
926
927 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
928 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
929 'Processing FACTS Transactions starts.....');
930 END IF;
931 LOOP
932 -- This is a Dummy Loop since we have no command in PL/SQL to skip
933 -- the Loop in the middle and continue with the next iteration.
934 LOOP /* Dummy */
935 -- Reseting all the Variables before fetching the Next Row
936 va_transaction_partner_val := ' ' ;
937 va_cohort := ' ' ;
938 va_def_indef_val := ' ' ;
939 va_appor_cat_b_dtl := ' ' ;
940 va_appor_cat_b_txt := LPAD(' ',25) ;
941 va_prn_num := ' ' ;
942 va_prn_txt := LPAD(' ',25) ;
943 va_public_law_code_val := ' ' ;
944 va_appor_cat_val := ' ' ;
945 va_authority_type_val := ' ' ;
946 va_reimburseable_val := ' ' ;
947 va_bea_category_val := ' ' ;
948 va_borrowing_source_val := ' ' ;
949 va_legis_ind_val := ' ' ;
950 va_pya_val := ' ' ;
951 va_balance_type_val := ' ' ;
952 va_availability_flag := ' ';
953 va_function_flag := ' ';
954 va_budget_function := ' ';
955 va_advance_type_val := ' ';
956 va_transfer_dept_id := ' ';
957 va_transfer_main_acct := ' ';
958 va_account_ctr := 0;
959 vl_ccid := NULL;
960 vg_amount := 0;
961 v_dummy_cohort := NULL;
962 vl_pagebreak := NULL;
963 vl_cohort_yr := NULL;
964 v_cohort_where := NULL;
965
966 v_period_dr := 0;
967 v_period_cr := 0;
968
969 vl_main_fetch := dbms_sql.fetch_rows(vl_main_cursor) ;
970
971 IF (VL_MAIN_FETCH = 0) then
972 exit;
973 End if;
974
975
976 -- Increase the counter for number of records
977 vl_row_count := vl_row_count + 1 ;
978
979 -- Fetch the Records into Variables
980 dbms_sql.column_value(vl_main_cursor, 1, vl_ccid);
981 dbms_sql.column_value(vl_main_cursor, 2, vl_acct_num);
982 dbms_sql.column_value(vl_main_cursor, 3, vl_fund_value);
983 dbms_sql.column_value(vl_main_cursor, 4, v_fiscal_yr);
984 dbms_sql.column_value(vl_main_cursor, 5, vg_amount);
985
986 -- Added by 7324248
987 l_counter := 6;
988
989 IF v_cohort_seg_name IS NOT NULL THEN
990 dbms_sql.column_value(vl_main_cursor, l_counter, vl_cohort_yr);
991 l_counter:=l_counter+1;
992 END IF;
993
994 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
995 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
996 '==========================================================');
997 END IF;
998
999 -- Fix for bug 2798371
1000 IF vl_cohort_yr IS NOT NULL THEN
1001 BEGIN
1002 SELECT TO_NUMBER(vl_cohort_yr)
1003 INTO v_dummy_cohort
1004 FROM DUAL;
1005
1006 IF LENGTH(v_dummy_cohort) = 1 THEN
1007 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1008 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1009 'Cohort value: '||vl_cohort_yr||' is a single digit!');
1010 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1011 'Taking Cohort value from report parameter.');
1012 END IF;
1013 v_dummy_cohort := vp_report_fiscal_yr;
1014 END if;
1015
1016 EXCEPTION WHEN INVALID_NUMBER THEN
1017 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1018 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1019 l_module_name, 'Cohort value: '||vl_cohort_yr
1020 ||' is non-numeric!');
1021 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1022 l_module_name, 'Taking Cohort value from report parameter.');
1023 END IF;
1024 v_dummy_cohort := vp_report_fiscal_yr;
1025 END;
1026 END IF;
1027
1028 -- va_cohort := NVL(LPAD(substr(vl_cohort_yr, 3, 2), 2, ' '), ' ') ;
1029 va_cohort := NVL(LPAD(substr(v_dummy_cohort,
1030 LENGTH(v_dummy_cohort)-1, 2), 2, ' '), ' ') ;
1031
1032 -- Acct Number Validation based on type of Processing(FACTSII or TBal)
1033
1034 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1036 'Processing for >>>> Acct -> '||vl_acct_num||
1037 ' >>>> Fund -> '||vl_fund_value||
1038 ' Cohort >>>> -> ' ||vl_cohort_yr||
1039 ' >>>> Amt -> ' ||
1040 to_char(vl_amount) );
1041 End If ;
1042
1043 -- Set the global variables
1044 v_ccid := vl_ccid;
1045 v_record_category := 'REPORTED' ;
1046 v_tbal_fund_value := vl_fund_value ;
1047 v_tbal_acct_num := vl_acct_num ;
1048
1049 /* Getting parent a/c */
1050
1051 vl_attributes_found := 'N' ;
1052
1053 GET_SGL_PARENT(vl_acct_num, vl_parent_ac , v_sgl_acct_num) ;
1054
1055 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1056 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1057 l_module_name, 'Parent A/c : '||vl_parent_ac||
1058 ' USSGL : '||v_sgl_acct_num);
1059 END IF;
1060
1061 LOAD_FACTS_ATTRIBUTES (vl_acct_num, vl_fund_value,vl_retcode) ;
1062
1063 IF vl_retcode = -1 then
1064 IF vl_parent_ac is not null then
1065 LOAD_FACTS_ATTRIBUTES(vl_parent_ac, vl_fund_value,vl_retcode) ;
1066 End if;
1067 ELSE
1068 vl_attributes_found := 'Y';
1069 END IF;
1070
1071 if vl_retcode = -1 then
1072 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1073 'No attributes defined '||vl_acct_num||' and '||vl_parent_ac);
1074 else
1075 vl_attributes_found := 'Y' ;
1076 End if;
1077
1078 -- In case no attributes are found then insert beginning and ending
1079 -- balance records
1080
1081 If vl_attributes_found = 'N' then
1082 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'Attributes not found ') ;
1083
1084 -- Get the Beginning Balance
1085 CALC_BALANCE (
1086 vl_ccid,
1087 vl_fund_value,
1088 vl_acct_num,
1089 v_period_num,
1090 vp_report_fiscal_yr,
1091 'B',
1092 v_fiscal_yr,
1093 v_begin_amount,
1094 vl_period_activity,
1095 vl_pagebreak) ;
1096
1097 v_amount := v_begin_amount ;
1098 v_period_activity := vl_period_activity ;
1099 va_balance_type_val := 'B' ;
1100 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1101 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1102 'begin Balance for >>>> - ' || v_begin_amount);
1103 END IF;
1104 v_tbal_fund_value := vl_fund_value ;
1105 create_facts_record ;
1106 If vp_retcode <> 0 Then
1107 Return ;
1108 End If ;
1109 -- Exit the Loop to continue with the next Acct Number
1110 Exit ;
1111
1112 End If ; /* attributes not found */
1113
1114
1115 /* ----------------------------------------------------------------
1116 --Bug 7324248
1117 --Derive Transaction Partner attribute
1118 --using the Reimbursable Agreement segment value,
1119 --if the segment has been setup or has a value,
1120 --else default to 0.
1121 ----------------------------------------------------------------
1122
1123 fv_utility.log_mesg('va_transaction_partner_val:'||va_transaction_partner_val);
1124 fv_utility.log_mesg('vl_reimb_agree_val:'||vl_reimb_agree_val);
1125 fv_utility.log_mesg('vl_acct_num:'||vl_acct_num);
1126
1127 IF va_transaction_partner_val <> 'N' THEN
1128 IF g_reimb_agree_seg_name IS NOT NULL THEN
1129 IF vl_reimb_agree_val IS NOT NULL THEN
1130 get_trx_part_from_reimb(vl_reimb_agree_val);
1131 ELSE
1132 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1133 'Reimbursable Agreement value is null!!' ||
1134 ' Setting transaction partner value to 0.');
1135 END IF;
1136 ELSE
1137 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1138 'Reimbursable Agreement segment is not defined!!' ||
1139 ' Setting transaction partner value to 0.');
1140 END IF;
1141 END IF;
1142
1143 */
1144
1145 -- Cohort where clause is set to a global variable to use in
1146 -- CALC_BALANCE Procedure and futher in the process
1147 If v_cohort_seg_name IS NOT NULL Then
1148 v_cohort_where := ' AND GLCC.' || v_cohort_seg_name || ' = ' ||
1149 '''' || vl_cohort_yr || '''' ;
1150 Else
1151 v_cohort_where := ' ' ;
1152 End If ;
1153
1154
1155 -------------- Legislation Indicator Processing Starts ----------------
1156 If va_public_law_code_flag = 'Y' OR va_advance_flag = 'Y' OR va_transfer_ind = 'Y' Then
1157 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1158 If va_legis_ind_flag = 'Y' and
1159 va_public_law_code_flag = 'N' then
1160 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1161 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1162 ' ++++++++ Leg Ind Processing ++++++++') ;
1163 END IF;
1164 Elsif va_legis_ind_flag = 'N' and
1165 va_public_law_code_flag = 'Y' then
1166 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1167 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1168 ' ++++++++ Pub Law Processing ++++++++') ;
1169 END IF;
1170 End If ;
1171 --
1172 IF va_advance_flag = 'Y' THEN
1173 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1174 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1175 ' ++++++++ Advance Type Processing ++++++++') ;
1176 END IF;
1177 END IF;
1178 IF va_transfer_ind = 'Y' THEN
1179 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1180 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1181 ' ++++++++ Transfer Acct Processing ++++++++') ;
1182 END IF;
1183 END IF;
1184
1185 End If ;
1186
1187 BEGIN
1188 -- Calculate the Beginning balance for the current account
1189 -- and fund value combination and create record in temp
1190 -- table for Legislative Indicator 'A' and Balance Type 'B'
1191 -- Default Public Law Code values for beginning and
1192 -- ending balances
1193 If va_public_law_code_flag = 'Y' then
1194 --Bug#3219532
1195 --va_public_law_code_val := '000-000' ;
1196 va_public_law_code_val := ' ' ;
1197 End If ;
1198
1199 --
1200 -- Advance Type values for beginning and ending balances
1201 If va_advance_flag = 'Y' then
1202 va_advance_type_val := 'X' ;
1203 End If ;
1204 -- Transfer values for beginning and ending balances
1205 IF va_transfer_ind = 'Y' THEN
1206 va_transfer_dept_id := ' ' ;
1207 va_transfer_main_acct := ' ' ;
1208 END IF ;
1209 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1210 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1211 'Period number '||v_begin_period_num) ;
1212 END IF;
1213 CALC_BALANCE (
1214 vl_ccid,
1215 vl_fund_value,
1216 vl_acct_num,
1217 v_begin_period_num,
1218 vp_report_fiscal_yr,
1219 'B',
1220 v_fiscal_yr,
1221 v_begin_amount,
1222 vl_period_activity,
1223 vl_pagebreak) ;
1224
1225 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1226 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1227 ' Legis Ind Begin Balance -> ' || v_begin_amount) ;
1228 End If ;
1229 If vp_retcode <> 0 Then
1230 Return ;
1231 End If ;
1232
1233 vb_balance_amount := v_begin_amount;
1234 FOR begin_balance_rec IN (SELECT SUM(NVL(f.ending_balance_dr, 0) - NVL(f.ending_balance_cr, 0)) amount,
1235 f.public_law,
1236 f.advance_flag,
1237 f.transfer_dept_id,
1238 f.transfer_main_acct
1239 FROM fv_factsii_ending_balances f
1240 WHERE f.set_of_books_id = vp_set_of_books_id
1241 AND f.fiscal_year = vp_report_fiscal_yr-1
1242 AND f.ccid = vl_ccid
1243 GROUP BY f.public_law,
1244 f.advance_flag,
1245 f.transfer_dept_id,
1246 f.transfer_main_acct) LOOP
1247 v_amount := begin_balance_rec.amount;
1248 vb_balance_amount := vb_balance_amount - v_amount;
1249 v_record_category := 'REPORTED';
1250 va_public_law_code_val := RTRIM(begin_balance_rec.public_law);
1251 va_advance_type_val := begin_balance_rec.advance_flag;
1252 va_transfer_dept_id := begin_balance_rec.transfer_dept_id;
1253 va_transfer_main_acct := begin_balance_rec.transfer_main_acct;
1254 v_period_activity := 0;
1255 va_balance_type_val := 'B';
1256 v_period_dr := 0;
1257 v_period_cr := 0;
1258 create_facts_record;
1259 END LOOP;
1260
1261 IF (vb_balance_amount <> 0) THEN
1262 va_public_law_code_val := NULL;
1263 va_advance_type_val := NULL;
1264 va_transfer_dept_id := NULL;
1265 va_transfer_main_acct := NULL;
1266
1267 If va_public_law_code_flag = 'Y' then
1268 va_public_law_code_val := ' ' ;
1269 End If ;
1270 If va_advance_flag = 'Y' then
1271 va_advance_type_val := 'X' ;
1272 End If ;
1273 IF va_transfer_ind = 'Y' THEN
1274 va_transfer_dept_id := ' ' ;
1275 va_transfer_main_acct := ' ' ;
1276 END IF ;
1277
1278 va_balance_type_val := 'B' ;
1279 v_record_category := 'REPORTED' ;
1280 v_amount := vb_balance_amount ;
1281 v_period_activity := 0 ; --Bug 2577862
1282 v_period_dr := 0;
1283 v_period_cr := 0;
1284 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1285 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1286 'begin Balance >>>> - ' || to_char(v_begin_amount)) ;
1287 END IF;
1288 if v_amount > 0 then
1289 CREATE_FACTS_RECORD ;
1290 If vp_retcode <> 0 Then
1291 Return ;
1292 End If ;
1293 End if;
1294 END IF;
1295 -- Select the records for other Legislative Indicator values,
1296
1297 -- derived from Budget Execution tables and store them in a
1298 -- cursor. Then roll them up and insert the summarized record
1299 -- into the temp table. Dynamic SQL used for implementation.
1300 Begin
1301 vl_legis_cursor := DBMS_SQL.OPEN_CURSOR ;
1302 Exception
1303 When Others Then
1304 vp_retcode := sqlcode ;
1305 VP_ERRBUF := sqlerrm ;
1306 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1307 '.open_vl_legis_cursor', vp_errbuf) ;
1308 Return ;
1309 End ;
1310
1311
1312 IF va_pl_code_col IS NOT NULL THEN
1313 va_pl_code_col := ', gjl.'||va_pl_code_col;
1314 END IF;
1315
1316 IF va_tr_main_acct_col IS NOT NULL THEN
1317 va_tr_main_acct_col := ', gjl.'||va_tr_main_acct_col;
1318 END IF;
1319
1320 IF va_tr_dept_id_col IS NOT NULL THEN
1321 va_tr_dept_id_col := ', gjl.'||va_tr_dept_id_col;
1322 END IF;
1323
1324 IF va_advance_type_col IS NOT NULL THEN
1325 va_advance_type_col := ', gjl.'||va_advance_type_col;
1326 END IF;
1327
1328
1329 -- Get the transactions for the account Number and Fund (and
1330 -- cohort segment, if required)
1331
1332 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1333 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'vl_legis_Select') ;
1334 END IF;
1335
1336 vl_legis_select :=
1337 'Select gjl.reference_1,
1338 Nvl(gjl.entered_dr, 0) - Nvl(gjl.entered_cr, 0),
1339 gjl.effective_date , gjl.period_name,
1340 Nvl(gjl.entered_dr, 0) period_dr , Nvl(gjl.entered_cr, 0) period_cr,
1341 gjh.je_source ,gjh.je_header_id , gjl.je_line_num , gjh.je_batch_id,je_from_sla_flag '||
1342 va_pl_code_col || va_tr_main_acct_col || va_tr_dept_id_col ||
1343 va_advance_type_col ||
1344 ' From gl_je_lines gjl,
1345 gl_code_combinations glcc,
1346 gl_je_headers gjh
1347 Where gjl.code_combination_id = glcc.code_combination_id
1348 AND glcc.code_combination_id = :ccid ';
1349
1350 vl_legis_select := vl_legis_select ||
1351 ' AND gjl.status = :je_status
1352 AND (gjl.effective_date between
1353 :begin_period_start_dt
1354 AND :period_end_dt)
1355 AND gjl.ledger_id = :set_of_books_id
1356 AND glcc.' || v_acc_seg_name || ' = :acct_num
1357 AND Nvl(gjl.entered_dr, 0) - Nvl(gjl.entered_cr, 0) <> 0
1358 AND glcc.' || v_bal_seg_name || ' = :fund_value ' ||
1359 v_cohort_where ||
1360 ' AND glcc.'||v_fyr_segment_name || ' = :fiscal_yr
1361 AND gjh.je_header_id = gjl.je_header_id
1362 AND gjh.currency_code = :currency_code
1363 AND NOT EXISTS
1364 (SELECT ''x''
1365 FROM gl_period_statuses glp
1366 WHERE glp.ledger_id = :set_of_books_id
1367 AND glp.application_id = 101
1368 AND glp.period_name = gjl.period_name
1369 AND glp.period_year = :report_fiscal_yr
1370 AND glp.period_num > :period_num) ';
1371
1372
1373 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1374 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, vl_legis_select) ;
1375 END IF;
1376
1377
1378 Begin
1379 dbms_sql.parse(vl_legis_cursor,vl_legis_select,DBMS_SQL.V7);
1380 Exception
1381 When Others Then
1382 vp_retcode := sqlcode ;
1383 VP_ERRBUF := sqlerrm ;
1384 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1385 '.parse_vl_legis_cursor', vp_errbuf) ;
1386 Return ;
1387 End ;
1388
1389 -- Bind the variables
1390 dbms_sql.bind_variable(vl_legis_cursor,':ccid', vl_ccid);
1391 dbms_sql.bind_variable(vl_legis_cursor,':je_status', 'P');
1392 dbms_sql.bind_variable(vl_legis_cursor,':begin_period_start_dt',
1393 v_begin_period_start_dt);
1394 dbms_sql.bind_variable(vl_legis_cursor,':period_end_dt',
1395 v_period_end_dt);
1396 dbms_sql.bind_variable(vl_legis_cursor,':set_of_books_id',
1397 vp_set_of_books_id);
1398 dbms_sql.bind_variable(vl_legis_cursor,':acct_num', vl_acct_num);
1399 dbms_sql.bind_variable(vl_legis_cursor,':fund_value', vl_fund_value);
1400 dbms_sql.bind_variable(vl_legis_cursor,':fiscal_yr', v_fiscal_yr);
1401 dbms_sql.bind_variable(vl_legis_cursor,':currency_code', vp_currency_code);
1402 dbms_sql.bind_variable(vl_legis_cursor,':set_of_books_id',
1403 vp_set_of_books_id);
1404 dbms_sql.bind_variable(vl_legis_cursor,':report_fiscal_yr',
1405 vp_report_fiscal_yr);
1406 dbms_sql.bind_variable(vl_legis_cursor,':period_num', v_period_num);
1407
1408
1409 dbms_sql.define_column(vl_legis_cursor, 1, vl_legis_ref, 240);
1410 dbms_sql.define_column(vl_legis_cursor, 2, vl_legis_amount );
1411 dbms_sql.define_column(vl_legis_cursor, 3, vl_effective_date );
1412 dbms_sql.define_column(vl_legis_cursor, 4, vl_period_name, 15 );
1413 dbms_sql.define_column(vl_legis_cursor, 5, v_period_dr );
1414 dbms_sql.define_column(vl_legis_cursor, 6, v_period_cr );
1415 dbms_sql.define_column(vl_legis_cursor, 7, vl_je_source, 25 );
1416 dbms_sql.define_column(vl_legis_cursor, 8, vl_je_header_id );
1417 dbms_sql.define_column(vl_legis_cursor, 9, vl_je_line_num );
1418 dbms_sql.define_column(vl_legis_cursor, 10, vl_je_batch_id );
1419 dbms_sql.define_column(vl_legis_cursor, 11, vl_je_sla_flag,1 );
1420
1421 vl_count := 12;
1422
1423 IF va_pl_code_col IS NOT NULL THEN
1424 dbms_sql.define_column(vl_legis_cursor, vl_count, vl_pl_code, 150);
1425 vl_count := vl_count + 1;
1426 END IF;
1427
1428 IF va_tr_main_acct_col IS NOT NULL THEN
1429 dbms_sql.define_column(vl_legis_cursor, vl_count, vl_tr_main_acct, 150);
1430 vl_count := vl_count + 1;
1431 END IF;
1432
1433 IF va_tr_dept_id_col IS NOT NULL THEN
1434 dbms_sql.define_column(vl_legis_cursor, vl_count, vl_tr_dept_id, 150);
1435 vl_count := vl_count + 1;
1436 END IF;
1437
1438 IF va_advance_type_col IS NOT NULL THEN
1439 dbms_sql.define_column(vl_legis_cursor, vl_count, vl_advance_type, 150);
1440 END IF;
1441
1442 Begin
1443 vl_exec_ret := dbms_sql.execute(vl_legis_cursor);
1444 Exception
1445 When Others Then
1446 vp_retcode := sqlcode ;
1447 VP_ERRBUF := sqlerrm ;
1448 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1449 '.execute_vl_legis_cursor', vp_errbuf) ;
1450 Return ;
1451 End ;
1452 va_account_ctr := 0;
1453 Loop
1454 vl_exception_cat := 0;
1455 if dbms_sql.fetch_rows(vl_legis_cursor) = 0 then
1456 exit;
1457 End if;
1458
1459 -- Fetch the Records into Variables
1460 dbms_sql.column_value(vl_legis_cursor,1,vl_legis_ref);
1461 dbms_sql.column_value(vl_legis_cursor,2,vl_legis_amount);
1462 dbms_sql.column_value(vl_legis_cursor,3,vl_effective_date);
1463 dbms_sql.column_value(vl_legis_cursor,4,vl_period_name);
1464 dbms_sql.column_value(vl_legis_cursor,5,v_period_dr);
1465 dbms_sql.column_value(vl_legis_cursor,6,v_period_cr);
1466 dbms_sql.column_value(vl_legis_cursor,7,vl_je_source);
1467
1468 dbms_sql.column_value(vl_legis_cursor, 8, vl_je_header_id );
1469 dbms_sql.column_value(vl_legis_cursor, 9, vl_je_line_num );
1470 dbms_sql.column_value(vl_legis_cursor, 10, vl_je_batch_id );
1471 dbms_sql.column_value(vl_legis_cursor, 11, vl_je_sla_flag );
1472
1473 vl_count := 12;
1474
1475 IF va_pl_code_col IS NOT NULL THEN
1476 dbms_sql.column_value(vl_legis_cursor, vl_count, vl_pl_code);
1477 vl_count := vl_count + 1;
1478 END IF;
1479
1480 IF va_tr_main_acct_col IS NOT NULL THEN
1481 dbms_sql.column_value(vl_legis_cursor, vl_count, vl_tr_main_acct);
1482 vl_count := vl_count + 1;
1483 END IF;
1484
1485 IF va_tr_dept_id_col IS NOT NULL THEN
1486 dbms_sql.column_value(vl_legis_cursor, vl_count, vl_tr_dept_id);
1487 vl_count := vl_count + 1;
1488 END IF;
1489
1490 IF va_advance_type_col IS NOT NULL THEN
1491 dbms_sql.column_value(vl_legis_cursor, vl_count, vl_advance_type);
1492 END IF;
1493
1494 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1495 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1496 'Ref 1 - '||nvl(vl_legis_ref,'Ref Null'));
1497 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1498 'Amt - '|| nvl(to_char(vl_legis_amount),
1499 'Amt Null')) ;
1500 End If ;
1501
1502
1503 SELECT adjustment_period_flag, period_num
1504 INTO vl_adj_flag , vl_adj_num
1505 FROM gl_period_statuses
1506 WHERE ledger_id = vp_set_of_books_id
1507 AND application_id = 101
1508 AND period_name = vl_period_name;
1509
1510 va_balance_type_val := 'C';
1511 IF vl_adj_num < v_period_num THEN
1512 va_balance_type_val := 'P';
1513 END IF;
1514 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1515 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1516 'vl_period ' || vl_period_name);
1517 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1518 'vl_adj_flag ' || vl_adj_flag);
1519 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1520 'vl_balance_flag ' || va_balance_type_val);
1521 END IF;
1522
1523 -----------------------------------------------------------------------
1524 -- Public Law Processing
1525 -- If the public law code is required then check the journal source.
1526 -- If the journal source is YE Close and Budgetary Transaction then
1527 -- get the public law code from BE details table. If the journal
1528 -- source is not these two, then get the public law code from the
1529 -- corresponding attribute field on the je line.
1530
1531 -----------------------------------------------------------------------
1532
1533 --CURSOR be_cursor IS
1534 for be_rec in be_cur
1535
1536 loop
1537 vl_legis_amount := be_rec.amount;
1538 vl_legis_ref := be_rec.transaction_id;
1539
1540 IF va_public_law_code_flag = 'N' then
1541 va_public_law_code_val := ' ' ;
1542 Else
1543 IF vl_legis_ref IS NOT NULL THEN
1544
1545 BEGIN
1546 SELECT public_law_code
1547 INTO va_public_law_code_val
1548 FROM fv_be_trx_dtls
1549 WHERE transaction_id = vl_legis_ref
1550 AND set_of_books_id = vp_set_of_books_id ;
1551 If va_public_law_code_val is NULL Then
1552 -- Create Exception
1553 v_ccid := vl_ccid;
1554 --Bug#3219532
1555 --va_public_law_code_val := '000-000' ;
1556 va_public_law_code_val := ' ' ;
1557 v_record_category := 'REPORTED';
1558 End If ;
1559
1560 EXCEPTION
1561 WHEN NO_DATA_FOUND THEN
1562 v_ccid := vl_ccid;
1563 --Bug#3219532
1564 --va_public_law_code_val := '000-000' ;
1565 va_public_law_code_val := ' ' ;
1566 v_record_category := 'REPORTED';
1567 WHEN INVALID_NUMBER THEN
1568 v_record_category := 'REPORTED';
1569 --Bug#3219532
1570 --va_public_law_code_val := '000-000' ;
1571 va_public_law_code_val := ' ' ;
1572 END ;
1573
1574 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1575 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'P Law-'||
1576 nvl(va_public_law_code_val,'P Law Null'));
1577 END IF;
1578
1579 ELSE -- vl_legis_ref is null
1580 IF va_pl_code_col IS NULL THEN
1581 va_public_law_code_val := ' ' ;
1582 ELSE
1583 va_public_law_code_val := SUBSTR(vl_pl_code,1,7);
1584 END IF;
1585
1586 END IF;
1587
1588 End If ; /* va_public_law_code */
1589
1590 -- Advance Type specific processing
1591 IF va_advance_flag = 'Y' THEN
1592 IF vl_legis_ref IS NOT NULL THEN
1593 BEGIN
1594 SELECT advance_type
1595 INTO va_advance_type_val
1596 FROM fv_be_trx_dtls
1597 WHERE transaction_id = vl_legis_ref
1598 AND set_of_books_id = vp_set_of_books_id ;
1599 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1601 'Advance Type - '||
1602 nvl(va_advance_type_val, 'Advance Type Null')) ;
1603 END IF ;
1604 -- If the advance_type value is null then set it to 'X'
1605 IF va_advance_type_val IS NULL THEN
1606 va_advance_type_val := 'X';
1607 END IF;
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610 va_advance_type_val := 'X';
1611 vl_exception_cat := 1;
1612 WHEN INVALID_NUMBER THEN
1613 va_advance_type_val := 'X';
1614 END;
1615 ELSE -- vl_legis_ref is null
1616 IF va_advance_type_col IS NULL THEN
1617 --Bug#3219532
1618 va_advance_type_val := 'X';
1619 --va_advance_type_val := ' ';
1620 ELSE
1621 va_advance_type_val := SUBSTR(NVL(vl_advance_type, 'X'),1,1);
1622 END IF;
1623 END IF;
1624 END IF;
1625
1626 -- Transfer Acct specific processing
1627 IF va_transfer_ind = 'Y' THEN
1628 IF vl_legis_ref IS NOT NULL THEN
1629 BEGIN
1630 SELECT dept_id, main_account
1631 INTO va_transfer_dept_id, va_transfer_main_acct
1632 FROM fv_be_trx_dtls
1633 WHERE transaction_id = vl_legis_ref
1634 AND set_of_books_id = vp_set_of_books_id ;
1635 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1636 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1637 'Transfer Dept ID - '||
1638 nvl(va_transfer_dept_id, 'Transfer Dept ID Null')) ;
1639 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1640 'Transfer Main Acct - '||
1641 nvl(va_transfer_main_acct, 'Transfer Main Acct Null')) ;
1642 END IF ;
1643
1644 -- If the Transfer values are null then set default values
1645 -- Since both dept_id and main_acct are null or both have
1646 IF va_transfer_dept_id IS NULL THEN
1647 va_transfer_dept_id := ' ';
1648 va_transfer_main_acct := ' ';
1649 END IF;
1650 EXCEPTION
1651 WHEN NO_DATA_FOUND THEN
1652 va_transfer_dept_id := ' ';
1653 va_transfer_main_acct := ' ';
1654 WHEN INVALID_NUMBER THEN
1655 va_transfer_dept_id := ' ';
1656 va_transfer_main_acct := ' ';
1657 END;
1658 ELSE -- vl_legis_ref is null
1659 IF va_tr_main_acct_col IS NULL THEN
1660 va_transfer_main_acct := ' ';
1661 va_transfer_dept_id := ' ';
1662 ELSE
1663 va_transfer_main_acct := SUBSTR(vl_tr_main_acct,1,4);
1664 va_transfer_dept_id := SUBSTR(vl_tr_dept_id,1,2);
1665 END IF;
1666 END IF;
1667 END IF;
1668
1669 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1670 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1671 ' Acct - '||vl_acct_num) ;
1672 END IF;
1673 v_amount := 0;
1674 v_period_activity := 0;
1675 if va_balance_type_val = 'P' then
1676 v_amount := vl_legis_amount ;
1677 v_period_dr := 0;
1678 v_period_cr := 0;
1679 else
1680 v_period_activity := vl_legis_amount;
1681 End if;
1682
1683 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1684 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1685 'period_net_dr - '|| v_period_dr) ;
1686 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1687 'period_net_cr - '|| v_period_cr) ;
1688 END IF;
1689 CREATE_FACTS_RECORD ;
1690 If vp_retcode <> 0 Then
1691 Return ;
1692 End If ;
1693 End Loop; --sla cursor;
1694 End Loop; -- legis cur ;
1695
1696 -- Close the Legislative Indicator Cursor
1697 Begin
1698 dbms_sql.Close_Cursor(vl_legis_cursor);
1699 Exception
1700 When Others Then
1701 vp_retcode := sqlcode ;
1702 VP_ERRBUF := sqlerrm ;
1703 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1704 '.close_vl_legis_cursor', vp_errbuf) ;
1705 Return ;
1706 End ;
1707
1708 -- Once the Legislative Indicator or Public Law code
1709 -- is processesed, no need to proceed further for this
1710 -- acct/fund combination. Going to the Next Account
1711 Exit ;
1712 EXCEPTION
1713 -- Process any Exceptions in Legislative Indicator
1714 -- Processing
1715 When Others Then
1716 vp_retcode := sqlcode ;
1717 vp_errbuf := sqlerrm ||
1718 ' [ PROCESS_FACTS_TRANSCTIONS-LEGIS IND ] ' ;
1719 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1720 '.exception_1', vp_errbuf) ;
1721 Return ;
1722 END ;
1723 -------------- Apportionment Category Processing Starts ----------------
1724 Elsif (va_appor_cat_flag = 'Y' ) then
1725 -- Derive the Apportionment Category
1726 -- Apportionment Category Processing done only for FACTS II
1727 --Bug#3376230 to include va_appor_cat_val = 'A' too
1728 /* -- 2005 FACTS II Enhancemnt to include category C
1729
1730 IF va_appor_cat_val = 'C' THEN
1731 va_appor_cat_b_dtl := '000';
1732 va_appor_cat_b_txt := 'Default Cat B Code';
1733 va_prn_num := '000';
1734 va_prn_txt := 'Default PRN Code';
1735
1736 END IF; */
1737
1738 If va_appor_cat_val IN ('A', 'B') then
1739 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1740 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1741 ' ++++++++ Apportionment Category Processing ++++++++++') ;
1742 End If ;
1743 -- Get the Program segment name for the current fund value
1744 GET_PROGRAM_SEGMENT (vl_fund_value) ;
1745
1746 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1747 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1748 'Fund - '||vl_fund_value||' > CAT B Prog Seg - '||v_catb_prg_seg_name|| ' > PRN Prog Seg - '||v_prn_prg_seg_name);
1749 End If ;
1750
1751 If v_catb_prg_seg_name IS NOT NULL OR
1752 v_prn_prg_seg_name IS NOT NULL Then
1753 Begin
1754 vl_appor_cursor := DBMS_SQL.OPEN_CURSOR ;
1755 Exception
1756 When Others Then
1757 vp_retcode := sqlcode ;
1758 VP_ERRBUF := sqlerrm ;
1759 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1760 '.open_vl_appor_cursor', vp_errbuf) ;
1761 Return ;
1762 End ;
1763 -- Dynamic SQL to group the amount by Fund, Acct
1764 -- and Program for the Beginning Balance
1765 -- Processing Apportionment Category for Beginning Balance
1766
1767 va_balance_type_val := 'B' ;
1768 vl_appor_period := ' AND GLB.PERIOD_NUM = :period_num
1769 AND GLB.PERIOD_YEAR = :report_fiscal_yr ';
1770 Build_Appor_Select(vl_ccid,
1771 vl_acct_num,
1772 vl_fund_value,
1773 v_fiscal_yr,
1774 vl_appor_period,
1775 vl_appor_select) ;
1776 Begin
1777 dbms_sql.parse(vl_appor_cursor,vl_appor_select,
1778 DBMS_SQL.V7);
1779 Exception
1780 When Others Then
1781 vp_retcode := sqlcode ;
1782 vp_errbuf := sqlerrm || ' [MAIN - APPOR]' ;
1783 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1784 '.parse_vl_appor_cursor', vp_errbuf) ;
1785 Return ;
1786 End ;
1787
1788 -- Bind the variables
1789 dbms_sql.bind_variable(vl_appor_cursor, ':ccid', vl_ccid);
1790 dbms_sql.bind_variable(vl_appor_cursor, ':actual_flag', 'A');
1791 dbms_sql.bind_variable(vl_appor_cursor, ':fund_value', vl_fund_value);
1792 dbms_sql.bind_variable(vl_appor_cursor, ':acct_number', vl_acct_num);
1793 dbms_sql.bind_variable(vl_appor_cursor, ':fiscal_year', v_fiscal_yr);
1794 dbms_sql.bind_variable(vl_appor_cursor, ':period_num', v_period_num);
1795 dbms_sql.bind_variable(vl_appor_cursor, ':report_fiscal_yr',
1796 vp_report_fiscal_yr);
1797 dbms_sql.bind_variable(vl_appor_cursor, ':set_of_books_id',
1798 vp_set_of_books_id);
1799 dbms_sql.bind_variable(vl_appor_cursor, ':currency_code',
1800 vp_currency_code);
1801
1802
1803 dbms_sql.define_column(vl_appor_cursor,1,vl_acct_num,25);
1804 dbms_sql.define_column(vl_appor_cursor,2,vl_fund_value,25);
1805 vl_counter := 3;
1806
1807 IF v_catb_prg_seg_name IS NOT NULL THEN
1808 dbms_sql.define_column(vl_appor_cursor,vl_counter,vl_catb_program,25);
1809 vl_counter := vl_counter + 1;
1810 END IF;
1811
1812 IF v_prn_prg_seg_name IS NOT NULL THEN
1813 dbms_sql.define_column(vl_appor_cursor,vl_counter,vl_prn_program,25);
1814 vl_counter := vl_counter + 1;
1815 END IF;
1816
1817 dbms_sql.define_column(vl_appor_cursor,vl_counter,v_amount);
1818 vl_counter := vl_counter + 1;
1819 dbms_sql.define_column(vl_appor_cursor,vl_counter,vl_period_activity);
1820 vl_counter := vl_counter + 1;
1821 dbms_sql.define_column(vl_appor_cursor,vl_counter,v_period_dr);
1822 vl_counter := vl_counter + 1;
1823 dbms_sql.define_column(vl_appor_cursor,vl_counter,v_period_cr);
1824 vl_counter := vl_counter + 1;
1825
1826 If v_cohort_Seg_name is not null Then
1827 dbms_sql.define_column(vl_appor_cursor, vl_counter, vl_cohort_yr, 25);
1828 end If ;
1829
1830 Begin
1831 vl_exec_ret := dbms_sql.execute(vl_appor_cursor);
1832 Exception
1833 When Others Then
1834 vp_retcode := sqlcode ;
1835 vp_errbuf := sqlerrm||'[execute_vl_appor_cursor]' ;
1836 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1837 '.execute_vl_appor_cursor', vp_errbuf) ;
1838 Return ;
1839 End ;
1840
1841 --------------------------------------------------------------------------
1842 -- Reset the counter for apportionment cat b Dtl
1843 -- vl_appor_ctr := 0 ;
1844 LOOP
1845 if dbms_sql.fetch_rows(vl_appor_cursor) = 0 then
1846 exit;
1847 else
1848 -- Fetch the Records into Variables
1849 dbms_sql.column_value(vl_appor_cursor,1,vl_acct_num);
1850 dbms_sql.column_value(vl_appor_cursor,2,vl_fund_value);
1851
1852 vl_counter := 3;
1853 IF v_catb_prg_seg_name IS NOT NULL THEN
1854 dbms_sql.column_value(vl_appor_cursor,vl_counter,vl_catb_program);
1855 vl_counter := vl_counter + 1;
1856 END IF;
1857
1858 IF v_prn_prg_seg_name IS NOT NULL THEN
1859 dbms_sql.column_value(vl_appor_cursor,vl_counter,vl_prn_program);
1860 vl_counter := vl_counter + 1;
1861 END IF;
1862
1863 dbms_sql.column_value(vl_appor_cursor,vl_counter,v_amount);
1864 vl_counter := vl_counter + 1;
1865
1866 dbms_sql.column_value(vl_appor_cursor,vl_counter,v_period_activity);
1867 vl_counter := vl_counter + 1;
1868
1869 dbms_sql.column_value(vl_appor_cursor,vl_counter,v_period_dr);
1870 vl_counter := vl_counter + 1;
1871
1872 dbms_sql.column_value(vl_appor_cursor,vl_counter,v_period_cr);
1873 vl_counter := vl_counter + 1;
1874
1875 If v_cohort_Seg_name is not null Then
1876 -- vl_counter := vl_counter + 1;
1877 dbms_sql.column_value(vl_appor_cursor,vl_counter, vl_cohort_yr);
1878 end If ;
1879 -- vl_appor_ctr := vl_appor_ctr + 1 ;
1880 -- Get_Appor_Cat_B_Text(vl_program) ;
1881
1882 get_prc_val(vl_catb_program, vl_catb_rc_val, vl_catb_pgm_desc,
1883 vl_prn_program, vl_prn_rc_val, vl_prn_pgm_desc);
1884 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1885 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1886 'Appor Beg --> Acct - '||vl_acct_num||
1887 ' Fund >>>> - '||vl_fund_value ||
1888 ' CAT B Prgm >>>> - '||vl_catb_program ||
1889 ' PRN Prgm >>>> - '||vl_prn_program ||
1890 ' Amt >>>> - '||v_amount ||
1891 ' Text >>>> - ' ||va_appor_cat_b_txt) ;
1892 End If ;
1893
1894 If vp_retcode <> 0 Then
1895 Return ;
1896 End If ;
1897
1898 va_appor_cat_b_dtl := vl_catb_rc_val;
1899 va_appor_cat_b_txt := vl_catb_pgm_desc;
1900 va_prn_num := vl_prn_rc_val;
1901 va_prn_txt := vl_prn_pgm_desc;
1902
1903
1904 --Bug#3376230
1905 /*
1906 IF va_appor_cat_val = 'A' THEN
1907 va_appor_cat_b_dtl := LPAD(SUBSTR(vl_program, 1, 3), 3, '0');
1908 ELSE
1909 va_appor_cat_b_dtl := LPAD(to_char(vl_appor_ctr), 3, '0') ;
1910 END IF;
1911 */
1912 v_record_category := 'REPORTED' ;
1913
1914 -- added the foll line to populate fund value
1915 -- to facilitate getting cat b sequence values
1916 v_tbal_fund_value := vl_fund_value;
1917
1918 CREATE_FACTS_RECORD ;
1919 If vp_retcode <> 0 Then
1920 Return ;
1921 End If ;
1922 End If ;
1923 End Loop ;
1924 -------------------------------------------------------------------
1925
1926 -- Close the Apportionment Category Cursor
1927 Begin
1928 dbms_sql.Close_Cursor(vl_appor_cursor);
1929 Exception
1930 When Others Then
1931 vp_retcode := sqlcode ;
1932 VP_ERRBUF := sqlerrm||'[vl_appor_cursor]' ;
1933 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1934 '.close_vl_appor_cursor', vp_errbuf) ;
1935 Return ;
1936 End ;
1937
1938 -- Apportionment Category B processing completed
1939 -- successfully, no need to proceed further for this
1940 -- acct/fund combination. Going to the Next Account
1941 Exit ;
1942
1943 End If ; /* Program segment not null */
1944 END IF; /* va_appor_cat_val = 'B' */
1945 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'Apportion category is not B or
1946 Program Segment Not defined Or Null');
1947 v_amount := vl_amount ;
1948 v_ccid := vl_ccid;
1949 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1950 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EXCEPTION, l_module_name,
1951 ' So calling the default processing') ;
1952 END IF;
1953 DEFAULT_PROCESSING (vl_ccid,vl_fund_value,vl_acct_num,'E');
1954 EXIT; -- continue with the next account
1955 Else
1956 --- Default processing
1957 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1958 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EXCEPTION, l_module_name,
1959 'No special attributes defined , doing Normal processing');
1960 END IF;
1961 DEFAULT_PROCESSING (vl_ccid,vl_fund_value,vl_acct_num,'R',vl_pagebreak);
1962 -- Exit to end the Dummy Loop
1963 Exit ;
1964 End If ; /* va_apportionment_category_flag */
1965 End Loop ; /* for dummy Loop */
1966
1967
1968 -- Exit the Main loop in case no end of the cursor is reached
1969 If vl_main_fetch = 0 Then
1970 Exit ;
1971 End If ;
1972 END LOOP ; /* For the Main Cursor */
1973
1974
1975 -- Close the Main Cursor
1976 Begin
1977 dbms_sql.Close_Cursor(vl_main_cursor);
1978 Exception
1979 When Others Then
1980 vp_retcode := sqlcode ;
1981 VP_ERRBUF := sqlerrm||'[vl_main_cursor]' ;
1982 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1983 '.close_vl_main_cursor', vp_errbuf) ;
1984 Return ;
1985 End ;
1986
1987 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1988 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1989 'Calling Rollup process '|| v_tbal_fund_value);
1990 END IF;
1991
1992 IF (vl_row_count > 0) then
1993 FACTS_ROLLUP_RECORDS;
1994 -- process_cat_b_seq('REPORTED');
1995 v_fund_count := v_fund_count + 1;
1996 END IF;
1997 EXCEPTION
1998 WHEN OTHERS THEN
1999 vp_retcode := sqlcode ;
2000 vp_errbuf := sqlerrm ;
2001 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
2002 RAISE;
2003 END PROCESS_FACTS_TRANSACTIONS ;
2004 -- -------------------------------------------------------------------
2005 -- PROCEDURE LOAD_FACTS_ATTRIBUTES
2006 -- -------------------------------------------------------------------
2007 -- This procedure selects the attributes for the Account number
2008 -- segment from FV_FACTS_ATTRIBUTES table and load them into global
2009 -- variables for usage in the FACTS Main process. It also calculates
2010 -- one time pull up values for the account number that does not
2011 -- require drill down into GL transactions.
2012 -- ------------------------------------------------------------------
2013 PROCEDURE LOAD_FACTS_ATTRIBUTES (acct_num Varchar2,
2014 fund_val Varchar2,
2015 v_retcode OUT NOCOPY number)
2016 IS
2017 l_module_name VARCHAR2(200) := g_module_name || 'LOAD_FACTS_ATTRIBUTES';
2018 /*
2019 Commented by 7324248
2020 vl_financing_acct_flag Varchar2(1) ;
2021 vl_established_fy number ;
2022 */
2023 vl_resource_type Varchar2(80) ;
2024 vl_fund_category Varchar2(1) ;
2025 vl_direct_or_reimb_code VARCHAR2(1);
2026 BEGIN
2027
2028 Begin
2029 v_retcode := 0;
2030 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2032 'LOAD - Acct Num -> ' || acct_num || ' sob -> '
2033 || vp_set_of_books_id ) ;
2034 END IF;
2035 SELECT balance_type,
2036 public_law_code,
2037 reimburseable_flag,
2038 Decode(availability_time, 'N', ' ', availability_time),
2039 bea_category,
2040 apportionment_category,
2041 -- Decode(substr(transaction_partner,1,1),'N',' ',
2042 -- substr(transaction_partner,1,1)),
2043 substr(transaction_partner,1,1),
2044 borrowing_source,
2045 definite_indefinite_flag,
2046 legislative_indicator,
2047 pya_flag,
2048 authority_type,
2049 deficiency_flag,
2050 function_flag,
2051 advance_flag,
2052 transfer_flag
2053 INTO
2054 va_balance_type_flag,
2055 va_public_law_code_flag,
2056 va_reimburseable_flag,
2057 va_availability_flag,
2058 va_bea_category_flag,
2059 va_appor_cat_flag,
2060 va_transaction_partner_val,
2061 va_borrowing_source_flag,
2062 va_def_indef_flag,
2063 va_legis_ind_flag,
2064 va_pya_flag,
2065 va_authority_type_flag,
2066 va_deficiency_flag,
2067 va_function_flag,
2068 va_advance_flag,
2069 va_transfer_ind
2070 FROM FV_FACTS_ATTRIBUTES
2071 WHERE Facts_Acct_Number = acct_num
2072 and set_of_books_id = vp_set_of_books_id ;
2073
2074 IF NOT v_year_gtn2001 THEN
2075 va_advance_flag := ' ';
2076 va_transfer_ind := ' ';
2077 END IF;
2078
2079 Exception
2080 When NO_DATA_FOUND Then
2081 v_retcode := -1 ;
2082 return;
2083 When Others Then
2084 vp_retcode := sqlcode ;
2085 vp_errbuf := sqlerrm ;
2086 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_1', vp_errbuf) ;
2087 return;
2088 End ;
2089 --------------------------------------------------------------------------------
2090 -- Get the attribute column names for public_law_code and other
2091 -- attributes
2092 BEGIN
2093
2094 SELECT factsII_pub_law_code_attribute,
2095 factsII_advance_type_attribute,
2096 factsII_tr_main_acct_attribute,
2097 factsII_tr_dept_id_attribute
2098 INTO va_pl_code_col, va_advance_type_col,
2099 va_tr_main_acct_col, va_tr_dept_id_col
2100 FROM fv_system_parameters;
2101
2102 -- Set this global variable to true if facts attribute columns
2103 -- have been defined in Federal System Parameters form. If it is false
2104 -- then it means that the columns have not been setup, in which case
2105 -- process should end with a warning
2106 IF (va_pl_code_col IS NULL OR
2107 va_advance_type_col IS NULL OR
2108 va_tr_main_acct_col IS NULL OR
2109 va_tr_dept_id_col IS NULL)
2110 THEN
2111 v_facts_attributes_setup := FALSE ;
2112 ELSE
2113 v_facts_attributes_setup := TRUE ;
2114 END IF;
2115
2116 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2117 WHEN OTHERS THEN
2118 vp_retcode := sqlcode ;
2119 vp_errbuf := sqlerrm ;
2120 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found', vp_errbuf) ;
2121 RETURN;
2122 END;
2123 --------------------------------------------------------------------------------
2124 -- Getting the One time Pull up Values
2125 Begin
2126 Select UPPER(fts.resource_type),
2127 ffp.def_indef_flag,
2128 ffp.fund_category,
2129 RPAD(substr(bea_category,1,5), 5),
2130 ffp.direct_or_reimb_code
2131 INTO vl_resource_type,
2132 va_def_indef_val,
2133 vl_fund_category,
2134 va_bea_category_val,
2135 vl_direct_or_reimb_code
2136 From fv_treasury_symbols fts,
2137 fv_fund_parameters ffp
2138 WHERE ffp.treasury_symbol_id = fts.treasury_symbol_id
2139 AND (fts.FACTS_REPORTABLE_INDICATOR like 'II' or fts.FACTS_REPORTABLE_INDICATOR like 'I and II')
2140 AND ffp.fund_value = fund_val
2141 AND fts.treasury_symbol = vp_treasury_symbol
2142 AND fts.set_of_books_id = vp_set_of_books_id
2143 AND ffp.set_of_books_id = vp_set_of_books_id ;
2144 Exception
2145 When NO_DATA_FOUND Then
2146 vp_retcode := -1 ;
2147 vp_errbuf := 'Error getting Fund Category value for the fund - '||
2148 fund_val || ' [LOAD_FACTS_ATTRIBURES]' ;
2149 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found1', vp_errbuf) ;
2150 return;
2151 When Others Then
2152 vp_retcode := sqlcode ;
2153 vp_errbuf := sqlerrm || ' [LOAD_FACTS_ATTRIBURES]' ;
2154 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_2', vp_errbuf) ;
2155 return;
2156 End ;
2157 ------------------------------------------------
2158 -- Deriving Indefinite Definite Flag
2159 ------------------------------------------------
2160 If va_def_indef_flag <> 'Y' Then
2161 va_def_indef_val := ' ' ;
2162 End if;
2163
2164 ------------------------------------------------
2165 -- Deriving Public Law Code Flag
2166 ------------------------------------------------
2167 If va_public_law_code_flag = 'N' Then
2168 va_public_law_code_val := ' ' ;
2169 End If ;
2170 ------------------------------------------------
2171 -- Deriving Apportionment Category Code
2172 ------------------------------------------------
2173 If va_appor_cat_flag = 'Y' Then
2174 If vl_fund_category IN ('A','S') Then
2175 va_appor_cat_val := 'A' ;
2176 ElsIf vl_fund_category IN ('B','T') Then
2177 va_appor_cat_val := 'B' ;
2178 ElsIf vl_fund_category IN ('C','R') Then
2179 va_appor_cat_val := 'C' ;
2180 Else
2181 va_appor_cat_val := ' ' ;
2182 End If ;
2183 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2184 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Acct - ' || acct_num ||
2185 ' Fund cat - ' || vl_fund_category || ' Appr Cat - ' ||
2186 va_appor_cat_val || ' Flag - ' || va_appor_cat_flag) ;
2187 End If ;
2188 Else
2189 va_appor_cat_val := ' ' ;
2190 End If ;
2191
2192 /* ----------------------------------------
2193 -- Default the Reporting Codes when the
2194 -- Apportionment Category is unchecked
2195 ----------------------------------------
2196
2197 IF NVL(va_appor_cat_flag,'N') = 'N' THEN
2198 IF vl_fund_category IN ('A','B','C','R','S','T') THEN
2199 va_appor_cat_b_dtl := '000';
2200 va_appor_cat_b_txt := 'Default Cat B Code';
2201 va_prn_num := '000';
2202 va_prn_txt := 'Default PRN Code';
2203
2204 END IF;
2205
2206 IF ( FND_LOG.LEVEL_STATEMENT >=
2207 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2208 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2209 l_module_name, 'Defaulting the Reporting'
2210 ||'codes as the apportionment '
2211 ||'Category flag is N ') ;
2212 End If ;
2213 END IF; */
2214
2215 ------------------------------------------------
2216 -- Deriving Authority Type
2217 ------------------------------------------------
2218 If va_authority_type_flag = 'N' then
2219 va_authority_type_val := ' ' ;
2220 Else
2221 va_authority_type_val := va_authority_type_flag ;
2222 End If ;
2223 --------------------------------------------------------------------
2224 -- Transaction Partner Value derived from FV_FACTS_ATTRIBUTES table
2225 --------------------------------------------------------------------
2226 --------------------------------------------------------------------
2227 --Commented for CGAC
2228 -- Deriving Reimburseable Flag Value
2229 --------------------------------------------------------------------
2230 If va_reimburseable_flag = 'Y' Then
2231 va_reimburseable_val := vl_direct_or_reimb_code;
2232 Else
2233 va_reimburseable_val := ' ' ;
2234 End If ;
2235 --------------------------------------------------------------------
2236 -- Deriving BEA Category
2237 --------------------------------------------------------------------
2238 If va_bea_category_flag <> 'Y' then
2239
2240 /* -- now bea category deived from fv_fund_parameters
2241 Begin
2242 Select RPAD(substr(ffba.bea_category,1,5), 5)
2243 Into va_bea_category_val
2244 from fv_fund_parameters_all
2245 where fund_value = vl_fund_value
2246 and set_of_books_id = vp_set_of_books_id;
2247
2248 From fv_facts_budget_accounts ffba,
2249
2250 fv_facts_federal_accounts fffa,
2251 fv_treasury_symbols fts ,
2252 fv_facts_bud_fed_accts ffbfa
2253 Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
2254 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
2255 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
2256 AND fts.treasury_symbol = vp_treasury_symbol
2257 AND fts.set_of_books_id = vp_set_of_books_id
2258 AND fffa.set_of_books_id = vp_set_of_books_id
2259 AND ffbfa.set_of_books_id = vp_set_of_books_id
2260 AND ffba.set_of_books_id = vp_set_of_books_id ;
2261 Exception
2262 When NO_DATA_FOUND then
2263 va_bea_category_val := RPAD(' ', 5);
2264 End ;
2265
2266 Else
2267 */
2268 va_bea_category_val := RPAD(' ', 5);
2269 End If ;
2270
2271 --------------------------------------------------------------------
2272 -- Deriving Budget Function
2273 --------------------------------------------------------------------
2274 If va_function_flag = 'Y' then
2275 Begin
2276 Select RPAD(substr(ffba.budget_function,1,3), 3)
2277 Into va_budget_function
2278 From fv_facts_budget_accounts ffba,
2279 fv_facts_federal_accounts fffa,
2280 fv_treasury_symbols fts ,
2281 fv_facts_bud_fed_accts ffbfa
2282 Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
2283 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
2284 AND (fts.FACTS_REPORTABLE_INDICATOR like 'II' or fts.FACTS_REPORTABLE_INDICATOR like 'I and II')
2285 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
2286 AND fts.treasury_symbol = vp_treasury_symbol
2287 AND fts.set_of_books_id = vp_set_of_books_id
2288 AND fffa.set_of_books_id = vp_set_of_books_id
2289 AND ffbfa.set_of_books_id = vp_set_of_books_id
2290 AND ffba.set_of_books_id = vp_set_of_books_id ;
2291 -- Check the value of Budget Function
2292 Exception
2293 When NO_DATA_FOUND then
2294 va_budget_function := RPAD(' ', 3);
2295 End ;
2296 Else
2297 va_budget_function := RPAD(' ', 3);
2298 End If ;
2299 --------------------------------------------------------------------
2300 -- Deriving Borrowing Source
2301 --------------------------------------------------------------------
2302 If va_borrowing_source_flag = 'Y' then
2303 Begin
2304 Select RPAD(substr(borrowing_source_code,1,6), 6)
2305 Into va_borrowing_source_val
2306 from fv_fund_parameters
2307 where fund_value = fund_val
2308 and set_of_books_id = vp_set_of_books_id;
2309
2310
2311 /*
2312 Commented for CGAC changes
2313 From fv_facts_budget_accounts ffba,
2314 fv_facts_federal_accounts fffa,
2315 fv_treasury_symbols fts ,
2316 fv_facts_bud_fed_accts ffbfa
2317 Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
2318 AND (fts.FACTS_REPORTABLE_INDICATOR like 'II' or fts.FACTS_REPORTABLE_INDICATOR like 'I')
2319 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
2320 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
2321 AND fts.treasury_symbol = vp_treasury_symbol
2322 AND fts.set_of_books_id = vp_set_of_books_id
2323 AND fffa.set_of_books_id = vp_set_of_books_id
2324 AND ffbfa.set_of_books_id = vp_set_of_books_id
2325 AND ffba.set_of_books_id = vp_set_of_books_id ;*/
2326 -- Check the value of Borrowing Source
2327 Exception
2328 When NO_DATA_FOUND then
2329 va_borrowing_source_val := RPAD(' ', 6);
2330 End ;
2331 Else
2332 va_borrowing_source_val := RPAD(' ', 6);
2333 End If ;
2334
2335 va_def_liquid_flag := ' ' ;
2336 va_deficiency_val := ' ' ;
2337 EXCEPTION
2338 When Others Then
2339 vp_retcode := sqlcode ;
2340 vp_errbuf := sqlerrm || ' [LOAD_FACTS_ATTRIBUTES]' ;
2341 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
2342 END LOAD_FACTS_ATTRIBUTES ;
2343 -- -------------------------------------------------------------------
2344 -- PROCEDURE CALC_BALANCE
2345 -- -------------------------------------------------------------------
2346 -- This procedure Calculates the balance for the passed
2347 -- Acct_segment, Fund Value and Period Nnumber .
2348 -- ------------------------------------------------------------------
2349 Procedure CALC_BALANCE (ccid number,
2350 Fund_value Varchar2,
2351 acct_num Varchar2,
2352 period_num Number,
2353 period_year NUMBER,
2354 Balance_Type Varchar2,
2355 fiscal_year VARCHAR2,
2356 amount OUT NOCOPY Number,
2357 period_activity OUT NOCOPY NUMBER,
2358 pagebreak varchar2 )
2359 IS
2360 l_module_name VARCHAR2(200) := g_module_name || 'CALC_BALANCE';
2361 -- Variables for Dynamic SQL
2362 --vl_ret_val Boolean := TRUE ;
2363 vl_exec_ret Integer ;
2364 vl_bal_cursor Integer ;
2365 vl_bal_select Varchar2(2000) ;
2366 -- for data access security
2367 das_id NUMBER;
2368 das_where VARCHAR2(600);
2369
2370 BEGIN
2371 Begin
2372 vl_bal_cursor := DBMS_SQL.OPEN_CURSOR ;
2373 Exception
2374 When Others Then
2375 vp_retcode := sqlcode ;
2376 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Open Cursor] ' ;
2377 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.open_vl_bal_cursor', vp_errbuf) ;
2378 return;
2379 End ;
2380 -- Get the Balance
2381 vl_bal_select :=
2382 'Select Nvl(Decode(' || '''' || Balance_type || '''' ||
2383 ',' || '''' || 'B' || '''' ||
2384 ', SUM(GLB.BEGIN_BALANCE_DR - GLB.BEGIN_BALANCE_CR), ' ||
2385 '''' || 'E' || '''' || ', SUM((GLB.BEGIN_BALANCE_DR -
2386 GLB.BEGIN_BALANCE_CR) + (GLB.PERIOD_NET_DR - PERIOD_NET_CR ))),0),
2387 SUM(glb.period_net_dr - glb.period_net_cr) ,
2388 SUM(glb.period_net_dr) , sum(glb.period_net_cr)
2389 From GL_BALANCES GLB,
2390 GL_CODE_COMBINATIONS GLCC
2391 WHERE GLB.code_combination_id = GLCC.code_combination_id ';
2392
2393 -- Data Access Security
2394 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
2395 das_where := gl_access_set_security_pkg.get_security_clause
2396 (das_id,
2397 gl_access_set_security_pkg.READ_ONLY_ACCESS,
2398 gl_access_set_security_pkg.CHECK_LEDGER_ID,
2399 to_char(vp_set_of_books_id), 'GLB',
2400 gl_access_set_security_pkg.CHECK_SEGVALS,
2401 null, 'GLCC', null);
2402 IF (das_where IS NOT NULL) THEN
2403 vl_bal_select := vl_bal_select || 'AND ' || das_where;
2404 END IF;
2405
2406
2407 vl_bal_select := vl_bal_select || 'AND glcc.code_combination_id = to_char(:ccid) ';
2408
2409 vl_bal_select := vl_bal_select ||' AND glb.actual_flag =:actual_flag
2410 AND GLCC.' || v_bal_seg_name || ' = :fund_value
2411 AND GLCC.' || v_acc_seg_name || ' = :acct_num
2412 AND GLCC.' || v_fyr_segment_name || ' = :fiscal_year '||
2413 v_cohort_where ||
2414 ' AND GLB.ledger_id = :set_of_books_id
2415 AND GLB.PERIOD_NUM = :period_num
2416 AND GLB.PERIOD_YEAR = :period_year
2417 AND glb.currency_code = :currency_code ' ;
2418
2419 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2420 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'mg calc '||vl_bal_select) ;
2421 END IF;
2422
2423 Begin
2424 dbms_sql.parse(vl_bal_cursor, vl_bal_select, DBMS_SQL.V7) ;
2425 Exception
2426 When Others Then
2427 vp_retcode := sqlcode ;
2428 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Parse] ' ;
2429 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.parse_vl_bal_cursor', vp_errbuf) ;
2430 return;
2431 End ;
2432
2433 -- Bind the variables
2434 dbms_sql.bind_variable(vl_bal_cursor,':ccid', ccid);
2435 dbms_sql.bind_variable(vl_bal_cursor,':actual_flag', 'A');
2436 dbms_sql.bind_variable(vl_bal_cursor,':fund_value', fund_value);
2437 dbms_sql.bind_variable(vl_bal_cursor,':acct_num', acct_num);
2438 dbms_sql.bind_variable(vl_bal_cursor,':fiscal_year', fiscal_year);
2439 dbms_sql.bind_variable(vl_bal_cursor,':set_of_books_id', vp_set_of_books_id);
2440 dbms_sql.bind_variable(vl_bal_cursor,':period_num', period_num);
2441 dbms_sql.bind_variable(vl_bal_cursor,':period_year', period_year);
2442 dbms_sql.bind_variable(vl_bal_cursor,':currency_code', vp_currency_code);
2443
2444
2445
2446 dbms_sql.define_column(vl_bal_cursor, 1, amount);
2447 dbms_sql.define_column(vl_bal_cursor, 2, period_activity);
2448 dbms_sql.define_column(vl_bal_cursor, 3, v_period_dr);
2449 dbms_sql.define_column(vl_bal_cursor, 4, v_period_cr);
2450 Begin
2451 vl_exec_ret := dbms_sql.execute(vl_bal_cursor);
2452 Exception
2453 When Others Then
2454 vp_retcode := sqlcode ;
2455 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Execute Cursor] ' ;
2456 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.execute_vl_bal_cursor', vp_errbuf) ;
2457 End ;
2458 Loop
2459 if dbms_sql.fetch_rows(vl_bal_cursor) = 0 then
2460 exit;
2461 else
2462 -- Fetch the Records into Variables
2463 dbms_sql.column_value(vl_bal_cursor, 1, amount);
2464 dbms_sql.column_value(vl_bal_cursor, 2, period_activity);
2465 dbms_sql.column_value(vl_bal_cursor, 3, v_period_dr);
2466 dbms_sql.column_value(vl_bal_cursor, 4, v_period_cr);
2467 end if;
2468 End Loop ;
2469 -- Close the Balance Cursor
2470 Begin
2471 dbms_sql.Close_Cursor(vl_bal_cursor);
2472 Exception
2473 When Others Then
2474 vp_retcode := sqlcode ;
2475 VP_ERRBUF := sqlerrm ;
2476 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.close_vl_bal_cursor', vp_errbuf) ;
2477 Return ;
2478 End ;
2479 EXCEPTION
2480 When Others Then
2481 vp_retcode := sqlcode ;
2482 vp_errbuf := sqlerrm || ' [CALC_BALANCE - Others]' ;
2483 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
2484 return;
2485 END CALC_BALANCE ;
2486 -- -------------------------------------------------------------------
2487 -- PROCEDURE CREATE_FACTS_RECORD
2488 -- -------------------------------------------------------------------
2489 -- Inserts a new record into FV_FACTS_TEMP table with the current
2490 -- values from the global variables.
2491 -- ------------------------------------------------------------------
2492 PROCEDURE CREATE_FACTS_RECORD
2493 IS
2494 --
2495 l_module_name VARCHAR2(200) := g_module_name || 'CREATE_FACTS_RECORD';
2496 vl_disbursements_flag VARCHAR2(1);
2497 /*
2498 * Commented bY 7324248
2499 vl_exists Varchar2(1) ;
2500 v_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
2501 v_excptn_cat fv_facts_temp.fct_int_record_category%TYPE;
2502 vl_enabled_flag fv_facts_ussgl_accounts.ussgl_enabled_flag%TYPE;
2503 vl_reporting_type fv_facts_ussgl_accounts.reporting_type%TYPE;
2504 */
2505 vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
2506 vl_parent_sgl_acct_num fv_facts_temp.parent_sgl_acct_number%TYPE;
2507 vl_reimb_agree_sel VARCHAR2(250);
2508 vl_reimb_agree_seg_val VARCHAR2(30);
2509
2510 --Modifed for FV ER bug 8760767
2511
2512 /* cursor vl_pya_cursor is SELECT decode(pya_flag,'Y','X',' ')
2513 FROM fv_facts_attributes
2514 WHERE ussgl_acct_number = v_sgl_acct_num;
2515 */
2516 /* Modified for CGAC */
2517 cursor vl_pya_cursor is SELECT decode(pya_flag,'N',' ',pya_flag)
2518 FROM fv_facts_attributes
2519 WHERE ussgl_acct_number = v_sgl_acct_num
2520 AND set_of_books_id=vp_set_of_books_id;
2521
2522 BEGIN
2523 va_legis_ind_val := ' ';
2524 va_pya_val := ' ';
2525 v_year_budget_auth := ' ';
2526
2527 Begin
2528 open vl_pya_cursor;
2529 fetch vl_pya_cursor into va_pya_val;
2530 close vl_pya_cursor;
2531 exception
2532 when no_data_found then
2533 null;
2534 End ;
2535
2536 Begin
2537 SELECT disbursements_flag INTO vl_disbursements_flag
2538 FROM fv_facts_ussgl_accounts
2539 WHERE ussgl_account = v_sgl_acct_num;
2540 exception
2541 when no_data_found then
2542 null;
2543 End ;
2544
2545 IF (v_time_frame IN ('NO_YEAR','X') AND v_financing_acct = 'N'
2546 AND vl_disbursements_flag = 'Y' AND (v_amount <> 0 OR
2547 v_period_dr <> 0 OR
2548 v_period_cr <> 0 )) THEN
2549
2550 SELECT fyr_segment_value INTO vl_fyr_segment_value
2551 FROM fv_pya_fiscalyear_map
2552 WHERE period_year = vp_report_fiscal_yr
2553 AND set_of_books_id = vp_set_of_books_id;
2554
2555 IF vl_fyr_segment_value IS NOT NULL THEN
2556 IF vl_fyr_segment_value = v_fiscal_yr THEN
2557 v_year_budget_auth := 'NEW';
2558 ELSE
2559 v_year_budget_auth := 'BAL';
2560 END IF;
2561 END IF;
2562 END IF;
2563 /*
2564 Commented for bug 8824283
2565 ----------------------------------------------------------------
2566 --Bug 7324248
2567 --Derive Transaction Partner attribute
2568 --using the Reimbursable Agreement segment value,
2569 --if the segment has been setup or has a value,
2570 --else default to 0.
2571 ----------------------------------------------------------------
2572 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2573 'va_transaction_partner_val ::g_reimb_agree_seg_name ::'
2574 ||va_transaction_partner_val||'::'||g_reimb_agree_seg_name);
2575
2576 IF va_transaction_partner_val <> 'N' THEN
2577 IF g_reimb_agree_seg_name IS NOT NULL THEN
2578
2579 --get the reimb agree seg value from the ccid
2580 vl_reimb_agree_sel :=
2581 ' SELECT glcc.'||g_reimb_agree_seg_name||
2582 ' FROM gl_code_combinations glcc
2583 WHERE glcc.code_combination_id = :ccid
2584 AND chart_of_accounts_id = '||vp_coa_id;
2585
2586 EXECUTE IMMEDIATE vl_reimb_agree_sel INTO
2587 vl_reimb_agree_seg_val USING vl_ccid;
2588
2589 IF vl_reimb_agree_seg_val IS NOT NULL THEN
2590 get_trx_part_from_reimb(vl_reimb_agree_seg_val);
2591 IF vp_retcode <> 0 THEN
2592 RETURN;
2593 END IF;
2594 ELSE
2595 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2596 'Reimbursable Agreement value is null!!' ||
2597 ' Setting transaction partner value to 0.');
2598 va_transaction_partner_val := 0;
2599 END IF;
2600 ELSE
2601 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2602 'Reimbursable Agreement segment is not defined!!' ||
2603 ' Setting transaction partner value to 0.');
2604 va_transaction_partner_val := 0;
2605 END IF;
2606 END IF;
2607 */
2608
2609 INSERT INTO FV_FACTS_TEMP
2610 (code_combination_id,
2611 SGL_ACCT_NUMBER ,
2612 COHORT ,
2613 BEGIN_END ,
2614 INDEF_DEF_FLAG ,
2615 APPOR_CAT_B_DTL ,
2616 APPOR_CAT_B_TXT ,
2617 PUBLIC_LAW ,
2618 APPOR_CAT_CODE ,
2619 AUTHORITY_TYPE ,
2620 TRANSACTION_PARTNER ,
2621 REIMBURSEABLE_FLAG ,
2622 BEA_CATEGORY ,
2623 BORROWING_SOURCE ,
2624 DEF_LIQUID_FLAG ,
2625 DEFICIENCY_FLAG ,
2626 AVAILABILITY_FLAG ,
2627 LEGISLATION_FLAG ,
2628 PYA_FLAG ,
2629 AMOUNT ,
2630 DEBIT_CREDIT ,
2631 TREASURY_SYMBOL_ID ,
2632 FCT_INT_RECORD_CATEGORY ,
2633 FCT_INT_RECORD_TYPE ,
2634 TBAL_FUND_VALUE ,
2635 TBAL_ACCT_NUM ,
2636 BUDGET_FUNCTION ,
2637 ADVANCE_FLAG ,
2638 TRANSFER_DEPT_ID ,
2639 TRANSFER_MAIN_ACCT ,
2640 YEAR_BUDGET_AUTH ,
2641 period_activity ,
2642 amount1 ,
2643 amount2 ,
2644 parent_sgl_acct_number ,
2645 PROGRAM_RPT_CAT_NUM,
2646 PROGRAM_RPT_CAT_TXT)
2647 Values (vl_ccid ,
2648 v_sgl_acct_num ,
2649 va_cohort ,
2650 va_balance_type_val ,
2651 va_def_indef_val ,
2652 va_appor_cat_b_dtl ,
2653 va_appor_cat_b_txt ,
2654 va_public_law_code_val ,
2655 va_appor_cat_val ,
2656 va_authority_type_val ,
2657 --va_transaction_partner_val,
2658 DECODE(va_transaction_partner_val,'N',NULL,
2659 va_transaction_partner_val),
2660 va_reimburseable_val ,
2661 va_bea_category_val ,
2662 va_borrowing_source_val ,
2663 va_def_liquid_flag ,
2664 va_deficiency_val ,
2665 va_availability_flag ,
2666 va_legis_ind_val ,
2667 va_pya_val ,
2668 v_amount ,
2669 NULL ,
2670 v_treasury_symbol_id ,
2671 v_record_category ,
2672 'TB' ,
2673 v_tbal_fund_value ,
2674 v_tbal_acct_num,
2675 va_budget_function ,
2676 va_advance_type_val ,
2677 va_transfer_dept_id ,
2678 va_transfer_main_acct ,
2679 v_year_budget_auth ,
2680 v_period_activity ,
2681 v_period_dr ,
2682 v_period_cr ,
2683 vl_parent_sgl_acct_num,
2684 va_prn_num,
2685 va_prn_txt) ;
2686
2687
2688 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2689 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Created FACTS Record') ;
2690 End If ;
2691 EXCEPTION
2692 When Others Then
2693 vp_retcode := sqlcode ;
2694 vp_errbuf := sqlerrm || ' [CREATE_FACTS_RECORD] ' ;
2695 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
2696 return;
2697 END CREATE_FACTS_RECORD ;
2698 -- -------------------------------------------------------------------
2699 -- PROCEDURE GET_PROGRAM_SEGMENT
2700 -- -------------------------------------------------------------------
2701 -- Gets the Program segment name from FV_FACTS_PRC_HDR table
2702 -- -------------------------------------------------------------------
2703 PROCEDURE GET_PROGRAM_SEGMENT(v_fund_value Varchar2)
2704 IS
2705 l_module_name VARCHAR2(200) := g_module_name || 'GET_PROGRAM_SEGMENT';
2706 vl_seg_found VARCHAR2(1) := 'N';
2707 vl_prg_seg_name fv_facts_prc_hdr.program_segment%TYPE;
2708 vl_prc_header_id NUMBER(15);
2709 vl_prc_flag fv_facts_prc_hdr.prc_mapping_flag%TYPE;
2710 vl_code_type fv_facts_prc_hdr.code_type%TYPE;
2711 vl_prg_val_set_id NUMBER(15);
2712
2713 BEGIN
2714
2715 -- INITIALIZE ALL VARIABLES
2716
2717 v_prn_prg_seg_name := NULL ;
2718 v_catb_prg_seg_name := NULL;
2719
2720 FOR type in 1..2
2721 LOOP
2722 IF type = 1 THEN
2723 vl_code_type := 'B';
2724 ELSE
2725 vl_code_type := 'N';
2726 END IF;
2727 vl_prg_seg_name := NULL;
2728 vl_prc_flag := NULL;
2729 vl_prc_header_id := NULL;
2730 vl_seg_found := 'N';
2731
2732
2733 BEGIN
2734
2735 SELECT program_segment, prc_mapping_flag,
2736 prc_header_id
2737 INTO vl_prg_seg_name, vl_prc_flag,
2738 vl_prc_header_id
2739 FROM fv_facts_prc_hdr
2740 WHERE treasury_symbol_id = v_treasury_symbol_id
2741 AND code_type = vl_code_type
2742 AND fund_value = v_fund_value
2743 AND set_of_books_id = vp_set_of_books_id;
2744
2745 vl_seg_found := 'Y';
2746 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2747 END;
2748
2749 IF vl_seg_found = 'N' THEN
2750 BEGIN
2751
2752 SELECT program_segment, prc_mapping_flag,
2753 prc_header_id
2754 INTO vl_prg_seg_name, vl_prc_flag,
2755 vl_prc_header_id
2756 FROM fv_facts_prc_hdr
2757 WHERE treasury_symbol_id = v_treasury_symbol_id
2758 AND fund_value = 'ALL-A'
2759 AND code_type = vl_code_type
2760 AND va_appor_cat_val = 'A'
2761 AND set_of_books_id = vp_set_of_books_id;
2762
2763 vl_seg_found := 'Y';
2764 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2765 END;
2766 END IF;
2767
2768 IF vl_seg_found = 'N' THEN
2769 BEGIN
2770
2771 SELECT program_segment, prc_mapping_flag,
2772 prc_header_id
2773 INTO vl_prg_seg_name, vl_prc_flag,
2774 vl_prc_header_id
2775 FROM fv_facts_prc_hdr
2776 WHERE treasury_symbol_id = v_treasury_symbol_id
2777 AND fund_value = 'ALL-B'
2778 AND code_type = vl_code_type
2779 AND va_appor_cat_val = 'B'
2780 AND set_of_books_id = vp_set_of_books_id;
2781
2782 vl_seg_found := 'Y';
2783 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2784 END;
2785 END IF;
2786
2787 IF vl_seg_found = 'N' THEN
2788 BEGIN
2789
2790 SELECT program_segment, prc_mapping_flag,
2791 prc_header_id
2792 INTO vl_prg_seg_name, vl_prc_flag,
2793 vl_prc_header_id
2794 FROM fv_facts_prc_hdr
2795 WHERE treasury_symbol_id = v_treasury_symbol_id
2796 AND code_type = vl_code_type
2797 AND fund_value = 'ALL-FUNDS'
2798 AND set_of_books_id = vp_set_of_books_id;
2799
2800 vl_seg_found := 'Y';
2801 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2802 END;
2803 END IF;
2804
2805 IF vl_seg_found = 'N' THEN
2806 BEGIN
2807
2808 SELECT program_segment, prc_mapping_flag,
2809 prc_header_id
2810 INTO vl_prg_seg_name, vl_prc_flag,
2811 vl_prc_header_id
2812 FROM fv_facts_prc_hdr
2813 WHERE treasury_symbol_id = -1
2814 AND code_type = vl_code_type
2815 AND fund_value = 'ALL-A'
2816 AND va_appor_cat_val = 'A'
2817 AND set_of_books_id = vp_set_of_books_id;
2818
2819 vl_seg_found := 'Y';
2820 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2821 END;
2822 END IF;
2823
2824 IF vl_seg_found = 'N' THEN
2825 BEGIN
2826
2827 SELECT program_segment, prc_mapping_flag,
2828 prc_header_id
2829 INTO vl_prg_seg_name, vl_prc_flag,
2830 vl_prc_header_id
2831 FROM fv_facts_prc_hdr
2832 WHERE treasury_symbol_id = -1
2833 AND fund_value = 'ALL-B'
2834 AND code_type = vl_code_type
2835 AND va_appor_cat_val = 'B'
2836 AND set_of_books_id = vp_set_of_books_id;
2837
2838 vl_seg_found := 'Y';
2839 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2840 END;
2841 END IF;
2842
2843
2844 IF vl_seg_found = 'N' THEN
2845 BEGIN
2846
2847 SELECT program_segment, prc_mapping_flag,
2848 prc_header_id
2849 INTO vl_prg_seg_name, vl_prc_flag,
2850 vl_prc_header_id
2851 FROM fv_facts_prc_hdr
2852 WHERE treasury_symbol_id = -1
2853 AND fund_value = 'ALL-FUNDS'
2854 AND code_type = vl_code_type
2855 AND set_of_books_id = vp_set_of_books_id;
2856
2857 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2858 END;
2859 END IF;
2860
2861 If vl_prg_seg_name is NOT NULL AND vl_prc_flag = 'N' THEN
2862
2863 -- Get the value set id for the program segment
2864 Begin
2865 -- Getting the Value set Id for finding hierarchies
2866 select flex_value_set_id
2867 into vl_prg_val_set_id
2868 from fnd_id_flex_segments
2869 where application_column_name = vl_prg_seg_name
2870 and application_id = 101
2871 and id_flex_code = 'GL#'
2872 and id_flex_num = vp_coa_id ;
2873 Exception
2874 When NO_DATA_FOUND Then
2875 vp_retcode := -1 ;
2876 vp_errbuf := 'Error getting Value Set Id for segment'
2877 ||vl_prg_seg_name||' [GET_PROGRAM_SEGMENT]' ;
2878 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||
2879 '.no_data_found', vp_errbuf) ;
2880 When TOO_MANY_ROWS Then
2881 -- Too many value set ids returned for the program segment.
2882 vp_retcode := -1 ;
2883 vp_errbuf := 'Program Segment - '||vl_prg_seg_name||
2884 ' returned
2885 more than one Value Set !! '||'[ GET_PROGRAM_SEGMENT ]' ;
2886 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
2887 '.exception_1', vp_errbuf) ;
2888 End ;
2889 END IF;
2890
2891 IF type = 1 THEN
2892 IF va_appor_cat_val = 'B' THEN
2893 v_catb_prg_seg_name := vl_prg_seg_name;
2894 v_catb_rc_flag := vl_prc_flag;
2895 v_catb_rc_header_id := vl_prc_header_id;
2896 v_catb_prg_val_set_id := vl_prg_val_set_id;
2897 ELSIF va_appor_cat_val = 'A' THEN
2898 v_catb_prg_seg_name := NULL;
2899 v_catb_rc_flag := NULL;
2900
2901 END IF;
2902 ELSE
2903 v_prn_prg_seg_name := vl_prg_seg_name;
2904 v_prn_rc_flag := vl_prc_flag;
2905 v_prn_rc_header_id := vl_prc_header_id;
2906 v_prn_prg_val_set_id := vl_prg_val_set_id;
2907 END IF;
2908
2909 END LOOP;
2910 EXCEPTION
2911 When TOO_MANY_ROWS Then
2912 -- Fund Value not found in FV_BUDGET_DISTRIBUTION_HDR table.
2913 vp_retcode := -1 ;
2914 vp_errbuf := 'Fund Value - ' || v_fund_value || ' returned more
2915 than one program segment value !! ' ||
2916 '[ GET_PROGRAM_SEGMENT ]' ;
2917 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
2918 return;
2919 WHEN OTHERS THEN
2920 vp_retcode := sqlcode ;
2921 vp_errbuf := sqlerrm ;
2922 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
2923 RAISE;
2924 END GET_PROGRAM_SEGMENT ;
2925 -- -------------------------------------------------------------------
2926 -- PROCEDURE GET_APPOR_CAT_B_TEXT
2927 -- -------------------------------------------------------------------
2928 -- Gets the Apportionment Category B Detail and Text Information. Program
2929 -- segment value is passed to get the Text information and Counter value
2930 -- passed to get the converted text value (For Example when the appor_cnt
2931 -- value passed is 3 then the value returned is '003'
2932 -- -------------------------------------------------------------------
2933 PROCEDURE get_segment_text(p_program IN VARCHAR2,
2934 p_prg_val_set_id IN NUMBER,
2935 p_seg_txt OUT NOCOPY VARCHAR2)
2936 IS
2937 l_module_name VARCHAR2(200) := g_module_name || 'GET_APPOR_CAT_B_TEXT';
2938 --vl_prg_val_set_id NUMBER(15);
2939 Begin
2940 -- Get the Apportionment Category B Text
2941 Select Decode(ffvl.Description,
2942 NULL, RPAD(' ',25,' '), RPAD(ffvl.Description,25,' '))
2943 Into p_seg_txt
2944 From fnd_flex_values_tl ffvl,
2945 fnd_flex_values ffv
2946 where ffvl.flex_value_id = ffv.flex_value_id
2947 AND ffv.flex_value_set_id = p_prg_val_set_id
2948 AND ffv.flex_value = p_program
2949 AND ffvl.language = userenv('LANG');
2950 Exception
2951 When NO_DATA_FOUND Then
2952 vp_retcode := -1 ;
2953 vp_errbuf := 'Cannot Find Apportionment Category B Text for
2954 the Program ' || p_program||' [GET_SEGMENT_TEXT] ';
2955 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
2956 return;
2957 When TOO_MANY_ROWS Then
2958 vp_retcode := -1 ;
2959 vp_errbuf := 'More then one Apportionment Category B Text found for
2960 the Program '||p_program||' [GET_SEGMENT_TEXT]';
2961 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
2962 return;
2963 WHEN OTHERS THEN
2964 vp_retcode := sqlcode ;
2965 vp_errbuf := sqlerrm ;
2966 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
2967 RAISE;
2968 End ;
2969 -- -------------------------------------------------------------------
2970 -- PROCEDURE GET_ACCOUNT_TYPE
2971 -- -------------------------------------------------------------------
2972 -- Gets the Account Type Value for the passed Account Number.
2973 -- -------------------------------------------------------------------
2974 PROCEDURE GET_ACCOUNT_TYPE (acct_num Varchar2,
2975 acct_type OUT NOCOPY Varchar2)
2976 IS
2977 l_module_name VARCHAR2(200) := g_module_name || 'GET_ACCOUNT_TYPE';
2978 Begin
2979
2980 -- Get the Account Type from fnd Tables
2981 Select substr(compiled_value_attributes, 5, 1)
2982 Into acct_type
2983 From fnd_flex_values
2984 where flex_value_set_id = v_acc_val_set_id
2985 and flex_value = acct_num ;
2986
2987 If acct_type IS NULL Then
2988 -- Process Null Account Types
2989 vp_retcode := -1 ;
2990 vp_errbuf := 'Account Type found null for the for the
2991 Account Number - ' || acct_num ;
2992 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1', vp_errbuf) ;
2993 Return ;
2994 End If ;
2995 Exception
2996 When No_Data_Found Then
2997 vp_retcode := -1 ;
2998 vp_errbuf := 'Account Type Cannot be derived for the Account Number - '
2999 || acct_num ;
3000 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3001 Return ;
3002 WHEN OTHERS THEN
3003 vp_retcode := sqlcode ;
3004 vp_errbuf := sqlerrm ;
3005 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
3006 RAISE;
3007 End GET_ACCOUNT_TYPE ;
3008 -- -------------------------------------------------------------------
3009 -- PROCEDURE GET_SGL_PARENT
3010 -- -------------------------------------------------------------------
3011 -- Gets the SGL Parent Account for the passed account number
3012 -- ------------------------------------------------------------------
3013 Procedure GET_SGL_PARENT(
3014 Acct_num Varchar2,
3015 parent_ac OUT NOCOPY Varchar2,
3016 sgl_acct_num OUT NOCOPY Varchar2)
3017 IS
3018 l_module_name VARCHAR2(200) := g_module_name || 'GET_SGL_PARENT';
3019 --vl_exists varchar2(1) ;
3020
3021 BEGIN
3022
3023 /* Check the a/c itself a USSGL a/c */
3024
3025 BEGIN
3026
3027 SELECT parent_flex_value
3028 INTO parent_ac
3029 FROM fnd_flex_value_hierarchies
3030 WHERE (acct_num Between child_flex_value_low
3031 and child_flex_value_high)
3032 AND flex_value_set_id = v_acc_val_set_id
3033 AND parent_flex_value <> 'T'
3034 AND parent_flex_value IN
3035 (SELECT ussgl_account
3036 FROM fv_facts_ussgl_accounts
3037 WHERE ussgl_account = parent_flex_value);
3038
3039 EXCEPTION
3040 WHEN NO_DATA_FOUND THEN
3041 parent_ac := NULL;
3042
3043 WHEN OTHERS THEN
3044 vp_retcode := sqlcode ;
3045 vp_errbuf := sqlerrm ;
3046 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3047 '.first_exception',vp_errbuf);
3048 RETURN;
3049 END;
3050
3051 SELECT ussgl_account
3052 INTO sgl_acct_num
3053 FROM fv_facts_ussgl_accounts
3054 WHERE ussgl_account = acct_num ;
3055
3056 EXCEPTION
3057 WHEN NO_DATA_FOUND THEN
3058 BEGIN
3059 SELECT ussgl_account
3060 INTO sgl_acct_num
3061 FROM fv_facts_ussgl_accounts
3062 WHERE ussgl_account = parent_ac ;
3063 EXCEPTION
3064 WHEN NO_DATA_FOUND THEN
3065 sgl_acct_num := NULL ;
3066 END;
3067
3068 RETURN ;
3069 WHEN OTHERS THEN
3070 vp_retcode := sqlcode ;
3071 vp_errbuf := sqlerrm ;
3072 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3073 '.final_exception',vp_errbuf);
3074 RAISE;
3075 END GET_SGL_PARENT ;
3076 -- -------------------------------------------------------------------
3077 -- PROCEDURE BUILD_APPOR_SELECT
3078 -- -------------------------------------------------------------------
3079 -- Builds the SQL Statement for the apportionment Category B Processing.
3080 -- -------------------------------------------------------------------
3081 Procedure Build_Appor_select ( ccid number,
3082 Acct_number Varchar2,
3083 Fund_Value Varchar2,
3084 fiscal_year Varchar2,
3085 appor_period Varchar2,
3086 select_stmt OUT NOCOPY Varchar2)
3087 IS
3088 l_module_name VARCHAR2(200) := g_module_name || 'Build_Appor_select';
3089 -- for data access security
3090 das_id NUMBER;
3091 das_where VARCHAR2(600);
3092 Begin
3093 select_stmt :=
3094 'Select GLCC.' || v_acc_seg_name ||
3095 ', GLCC.' || v_bal_seg_name;
3096
3097 IF v_catb_prg_seg_name IS NOT NULL THEN
3098 select_stmt := select_stmt ||
3099 ', GLCC.' || v_catb_prg_seg_name ;
3100 END IF;
3101
3102 IF v_prn_prg_seg_name IS NOT NULL THEN
3103 select_stmt := select_stmt ||
3104 ', GLCC.' || v_prn_prg_seg_name ;
3105 END IF;
3106
3107 select_stmt := select_stmt ||
3108 ', SUM(GLB.BEGIN_BALANCE_DR - GLB.BEGIN_BALANCE_CR), ' ||
3109 ' SUM(GLB.PERIOD_NET_DR - PERIOD_NET_CR ), '||
3110 ' SUM(GLB.PERIOD_NET_DR) period_dr , sum( PERIOD_NET_CR ) period_cr '||
3111 v_cohort_select ||
3112 ' From GL_BALANCES GLB,
3113 GL_CODE_COMBINATIONS GLCC
3114 WHERE GLB.code_combination_id = GLCC.code_combination_id
3115 AND glcc.code_combination_id = :ccid
3116 AND glb.actual_flag = :actual_flag
3117 AND GLCC.'|| v_bal_seg_name ||' = :Fund_Value
3118 AND GLCC.' || v_acc_seg_name ||' = :acct_number
3119 AND GLCC.' || v_fyr_segment_name ||' = :fiscal_year '||
3120 appor_period || v_cohort_where ||
3121 ' AND GLB.ledger_id = :set_of_books_id
3122 AND glb.currency_code = :currency_code ';
3123
3124 -- Data Access Security
3125 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
3126 das_where := gl_access_set_security_pkg.get_security_clause
3127 (das_id,
3128 gl_access_set_security_pkg.READ_ONLY_ACCESS,
3129 gl_access_set_security_pkg.CHECK_LEDGER_ID,
3130 to_char(vp_set_of_books_id), 'GLB',
3131 gl_access_set_security_pkg.CHECK_SEGVALS,
3132 null, 'GLCC', null);
3133 IF (das_where IS NOT NULL) THEN
3134 select_stmt := select_stmt || 'AND ' || das_where;
3135 END IF;
3136
3137
3138 select_stmt := select_stmt || 'GROUP BY GLCC.' || v_acc_seg_name ||
3139 ', GLCC.' || v_bal_seg_name;
3140
3141
3142 IF v_prn_prg_seg_name IS NOT NULL THEN
3143 select_stmt := select_stmt ||
3144 ', GLCC.' || v_prn_prg_seg_name ;
3145 END IF;
3146
3147 select_stmt := select_stmt ||
3148 ', GLCC.' || v_fyr_segment_name || v_cohort_select;
3149
3150 -- Bug 12382016: Modified group by clause to prevent ORA-00979: NOT A GROUP BY EXPRESSION
3151 -- error
3152 select_stmt := select_stmt ||
3153 ', GLB.BEGIN_BALANCE_DR, GLB.BEGIN_BALANCE_CR, GLB.PERIOD_NET_DR, PERIOD_NET_CR';
3154
3155 EXCEPTION
3156 WHEN OTHERS THEN
3157 vp_retcode := sqlcode ;
3158 vp_errbuf := sqlerrm ||'[ build_appor_select]';
3159 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3160 '.final_exception',vp_errbuf);
3161 RAISE;
3162
3163 End build_appor_select ;
3164 -- -------------------------------------------------------------------
3165 -- PROCEDURE LOAD_TREASURY_SYMBOL_ID
3166 -- -------------------------------------------------------------------
3167 -- Gets Treasury Symbol Id for the passed Treasury Symbol.
3168 -- -------------------------------------------------------------------
3169 Procedure Load_Treasury_Symbol_Id(p_flex_value VARCHAR2)
3170 IS
3171 l_module_name VARCHAR2(200) := g_module_name || 'Load_Treasury_Symbol_Id';
3172 Begin
3173
3174 IF vp_treasury_symbol IS NULL THEN
3175 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3176 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3177 'No FACTS II or FACTS I and II reportable Treasury Account Symbols exist for fund '||p_flex_value);
3178 END IF;
3179 RETURN;
3180 END IF;
3181 Select Treasury_Symbol_id
3182 Into v_treasury_symbol_id
3183 From fv_treasury_symbols
3184 where treasury_symbol = vp_treasury_symbol
3185 AND (FACTS_REPORTABLE_INDICATOR like 'II' or FACTS_REPORTABLE_INDICATOR like 'I and II')
3186 and set_of_books_id = vp_set_of_books_id ;
3187
3188 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3189 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3190 'Treas Symb id:'||v_treasury_symbol_id);
3191 END IF;
3192
3193 Exception
3194 WHEN NO_DATA_FOUND Then
3195 vp_retcode := -1 ;
3196 vp_errbuf := 'Treasury Symbol Id cannot be found for the Treasury
3197 Symbol - '||vp_treasury_symbol||' [ GET_TREASURY_SYMBOL_ID ] ' ;
3198 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3199 Return ;
3200 WHEN TOO_MANY_ROWS Then
3201 vp_retcode := -1 ;
3202 vp_errbuf := 'More than one Treasury Symbol Id found for the Treasury
3203 Symbol - '||vp_treasury_symbol||' [ GET_TREASURY_SYMBOL_ID ] ' ;
3204 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3205 WHEN OTHERS THEN
3206 vp_retcode := sqlcode ;
3207 vp_errbuf := sqlerrm ;
3208 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3209 '.final_exception',vp_errbuf);
3210 RAISE;
3211 End Load_Treasury_symbol_id;
3212 -------------------------------------------------------------------
3213 --------------------------------------------------------------------------------
3214 procedure FACTS_ROLLUP_RECORDS is
3215 l_module_name VARCHAR2(200) := g_module_name || 'FACTS_ROLLUP_RECORDS';
3216 vl_group_by varchar2(5000);
3217 vl_rollup varchar2(15000);
3218 vl_rollup_cursor Integer ;
3219 vl_exec_ret Integer ;
3220
3221 BEGIN
3222 select_group_by_columns(vp_report_id,vp_attribute_set,vl_group_by);
3223 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3224 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3225 'Group by ' || vl_group_by);
3226 END IF;
3227
3228 vl_rollup_cursor := DBMS_SQL.OPEN_CURSOR;
3229
3230 vl_rollup := '
3231 INSERT INTO FV_FACTS_TEMP
3232 (TREASURY_SYMBOL_ID ,
3233 SGL_ACCT_NUMBER ,
3234 COHORT ,
3235 INDEF_DEF_FLAG ,
3236 APPOR_CAT_B_DTL ,
3237 APPOR_CAT_B_TXT ,
3238 PROGRAM_RPT_CAT_NUM,
3239 PROGRAM_RPT_CAT_TXT,
3240 PUBLIC_LAW ,
3241 APPOR_CAT_CODE ,
3242 AUTHORITY_TYPE ,
3243 TRANSACTION_PARTNER ,
3244 REIMBURSEABLE_FLAG ,
3245 BEA_CATEGORY ,
3246 BORROWING_SOURCE ,
3247 DEF_LIQUID_FLAG ,
3248 DEFICIENCY_FLAG ,
3249 AVAILABILITY_FLAG ,
3250 LEGISLATION_FLAG ,
3251 PYA_FLAG ,
3252 AMOUNT ,
3253 TBAL_FUND_VALUE ,
3254 TBAL_ACCT_NUM ,
3255 fct_int_record_category,
3256 fct_int_record_type,
3257 YEAR_BUDGET_AUTH ,
3258 BUDGET_FUNCTION ,
3259 ADVANCE_FLAG ,
3260 TRANSFER_DEPT_ID ,
3261 TRANSFER_MAIN_ACCT ,
3262 AMOUNT1,
3263 AMOUNT2,
3264 period_activity ' ||
3265 replace(vl_group_by ,'glcc.' ) || ')' ||
3266 ' SELECT
3267 TREASURY_SYMBOL_ID,
3268 SGL_ACCT_NUMBER,
3269 COHORT,
3270 INDEF_DEF_FLAG,
3271 APPOR_CAT_B_DTL,
3272 APPOR_CAT_B_TXT,
3273 PROGRAM_RPT_CAT_NUM,
3274 PROGRAM_RPT_CAT_TXT,
3275 PUBLIC_LAW,
3276 APPOR_CAT_CODE,
3277 AUTHORITY_TYPE,
3278 TRANSACTION_PARTNER,
3279 REIMBURSEABLE_FLAG,
3280 BEA_CATEGORY,
3281 BORROWING_SOURCE,
3282 DEF_LIQUID_FLAG,
3283 DEFICIENCY_FLAG,
3284 AVAILABILITY_FLAG,
3285 LEGISLATION_FLAG,
3286 PYA_FLAG,
3287 SUM(decode(begin_end , ''P'', AMOUNT+PERIOD_ACTIVITY , AMOUNT)),
3288 tbal_fund_value,
3289 tbal_acct_num,
3290 ''REPORTED_NEW'',
3291 ''TB'',
3292 YEAR_BUDGET_AUTH,
3293 BUDGET_FUNCTION ,
3294 ADVANCE_FLAG ,
3295 TRANSFER_DEPT_ID,
3296 TRANSFER_MAIN_ACCT,
3297 SUM(AMOUNT1),
3298 SUM(AMOUNT2),
3299 SUM(decode(begin_end , ''P'' , 0 , period_activity )) '
3300 || vl_group_by ||
3301 ' From FV_FACTS_TEMP fvt, gl_code_combinations glcc
3302 WHERE fct_int_record_category = ''REPORTED''
3303 AND fct_int_record_type = ''TB''
3304 AND tbal_fund_value = ' || '''' || v_fund_value || ''''
3305 || ' and glcc.code_combination_id = fvt.code_combination_id
3306 GROUP BY TREASURY_SYMBOL_ID,
3307 SGL_ACCT_NUMBER,
3308 COHORT,
3309 INDEF_DEF_FLAG,
3310 APPOR_CAT_B_DTL,
3311 APPOR_CAT_B_TXT,
3312 PROGRAM_RPT_CAT_NUM,
3313 PROGRAM_RPT_CAT_TXT,
3314 PUBLIC_LAW,
3315 APPOR_CAT_CODE,
3316 AUTHORITY_TYPE,
3317 TRANSACTION_PARTNER,
3318 REIMBURSEABLE_FLAG,
3319 BEA_CATEGORY,
3320 BORROWING_SOURCE,
3321 DEF_LIQUID_FLAG,
3322 DEFICIENCY_FLAG,
3323 AVAILABILITY_FLAG,
3324 LEGISLATION_FLAG ,
3325 PYA_FLAG ,
3326 TBAL_FUND_VALUE ,
3327 TBAL_ACCT_NUM,
3328 YEAR_BUDGET_AUTH,
3329 BUDGET_FUNCTION ,
3330 ADVANCE_FLAG ,
3331 TRANSFER_DEPT_ID,
3332 TRANSFER_MAIN_ACCT ' || vl_group_by ;
3333
3334 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3335 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, vl_rollup);
3336 END IF;
3337 dbms_sql.parse(vl_rollup_cursor, vl_rollup, DBMS_SQL.V7) ;
3338 vl_exec_ret := dbms_sql.execute(vl_rollup_cursor);
3339 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3340 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' No of records rolled up '
3341 || vl_exec_ret);
3342 END IF;
3343
3344 -- Delete the Detail Records that are used in rollup process
3345 /*
3346 DELETE FROM FV_FACTS_TEMP
3347 WHERE (fct_int_record_category = 'REPORTED'
3348 -- OR fct_int_record_category = 'REPORTED_NEW' )
3349 AND AMOUNT = 0 AND NVL(PERIOD_ACTIVITY,0) = 0
3350 AND treasury_symbol_id = v_treasury_symbol_id ) ;
3351 */
3352
3353 --Bug 7324248
3354 --Delete rows which contain no amounts or 0 amounts
3355 DELETE FROM FV_FACTS_TEMP
3356 WHERE fct_int_record_category = 'REPORTED_NEW'
3357 AND (NVL(amount,0) = 0 AND
3358 NVL(period_activity,0) = 0 AND
3359 NVL(amount1,0) = 0 AND
3360 NVL(amount2,0) = 0)
3361 AND treasury_symbol_id = v_treasury_symbol_id;
3362
3363 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'NO OF ROWS DELETED FROM FV_FACTS_TEMP '||SQL%ROWCOUNT) ;
3364
3365 -- Set up Debit/Credit Indicator
3366 EXCEPTION
3367 WHEN OTHERS THEN
3368 vp_retcode := sqlcode ;
3369 vp_errbuf := sqlerrm ;
3370 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3371 '.final_exception',vp_errbuf);
3372 RAISE;
3373 END FACTS_ROLLUP_RECORDS;
3374
3375 ---------------------------------------------------------------------------
3376 -- PROCEDURE PROCESS_BY_FUND_RANGE
3377 -- -------------------------------------------------------------------
3378 -- This procedure is called to execute the trial balance process
3379 -- based on the range of funds (fund_low and fund_high parameters)
3380 -- that are passed. This calls all the subsequent procedures
3381 -- required for trial balance process.
3382 -- Added this procedure for the bug 1399282.
3383 -- ------------------------------------------------------------------
3384 Procedure PROCESS_BY_FUND_RANGE
3385 IS
3386 l_module_name VARCHAR2(200) := g_module_name || 'PROCESS_BY_FUND_RANGE';
3387 BEGIN
3388 fv_utility.log_mesg('PROCESS_BY_FUND_RANGE :: ');
3389 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3390 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3391 'Within the process_by_fund_range...') ;
3392 END IF;
3393
3394 If vp_retcode = 0 Then
3395 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3396 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3397 'Deriving Qualifier Segments.....') ;
3398 END IF;
3399
3400 -- Getting the Chart of Accounts Id
3401 BEGIN
3402 SELECT chart_of_accounts_id
3403 INTO v_chart_of_accounts_id
3404 FROM gl_ledgers_public_v
3405 WHERE ledger_id = vp_set_of_books_id;
3406
3407 fv_utility.log_mesg('v_chart_of_accounts_id :: '||v_chart_of_accounts_id);
3408 EXCEPTION
3409 WHEN NO_DATA_FOUND THEN
3410 vp_retcode := -1 ;
3411 vp_errbuf := 'Error getting Chart of Accounts Id for ledger id '
3412 ||vp_set_of_books_id;
3413 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf);
3414 RETURN;
3415 END;
3416 -- Getting the Account and Balancing segments' application column names
3417 BEGIN
3418 FV_UTILITY.get_segment_col_names(v_chart_of_accounts_id,
3419 v_acc_seg_name,
3420 v_bal_seg_name,
3421 error_code,
3422 error_message);
3423 fv_utility.log_mesg('v_acc_seg_name :: v_bal_seg_name::error_code::'||v_acc_seg_name||'|'||v_bal_seg_name||
3424 '|'||error_message);
3425 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3426 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3427 'Acc segment:'||v_acc_seg_name||' Bal Segment: '||v_bal_seg_name);
3428 END IF;
3429
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 vp_retcode := -1;
3433 vp_errbuf := error_message;
3434 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf);
3435 RETURN;
3436 END;
3437
3438 /*
3439 Commented for bug 8824283
3440 BEGIN
3441 -- Added for Bug 7324248. Get the Reimbursable Agreement segment
3442 SELECT application_column_name
3443 INTO g_reimb_agree_seg_name
3444 FROM FND_ID_FLEX_SEGMENTS_VL
3445 WHERE application_id = 101
3446 AND id_flex_code = 'GL#'
3447 AND id_flex_num = vp_coa_id
3448 AND enabled_flag = 'Y'
3449 AND segment_name like 'Reimbursable Agreement';
3450
3451 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3452 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3453 ' Reimbursable Agreement SEGMENT IS'||
3454 to_char(g_reimb_agree_seg_name));
3455 END IF;
3456 EXCEPTION
3457 WHEN NO_DATA_FOUND THEN
3458 vp_retcode := 2 ;
3459 vp_errbuf := 'GET QUALIFIER SEGMENTS - Reimbursable Agreement SEGMENT IS NOT FOUND';
3460 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf);
3461 RETURN;
3462 WHEN TOO_MANY_ROWS THEN
3463 vp_retcode := 2 ;
3464 vp_errbuf := 'GET QUALIFIER SEGMENTS - More than one ' ||
3465 'row returned while getting Reimbursable Agreement SEGMENT';
3466 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf);
3467 RETURN;
3468 WHEN OTHERS THEN
3469 vp_retcode := SQLCODE;
3470 vp_errbuf := SQLERRM ||
3471 '-- GET QUALIFIER SEGMENTS Error '||
3472 'when getting Reimbursable Agreement SEGMENT';
3473 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3474 vp_errbuf);
3475 RETURN;
3476 END;
3477 */
3478
3479
3480 /* Get fiscal year, account,balance segment names , Value set_id, */
3481
3482 BEGIN
3483
3484 SELECT application_column_name
3485 INTO v_fyr_segment_name
3486 FROM fv_pya_fiscalyear_segment
3487 WHERE set_of_books_id = vp_set_of_books_id;
3488
3489
3490 SELECT flex_value_set_id
3491 INTO v_acc_val_set_id
3492 FROM fnd_id_flex_segments
3493 WHERE application_column_name = v_acc_seg_name
3494 AND application_id = 101
3495 AND id_flex_code = 'GL#'
3496 AND id_flex_num = vp_coa_id;
3497
3498 fv_utility.log_mesg('v_fyr_segment_name :: v_acc_val_set_id::'||v_fyr_segment_name||'|'||v_acc_val_set_id);
3499 EXCEPTION
3500 when no_data_found then
3501 vp_retcode := -1 ;
3502 vp_errbuf := 'Error getting acc_value_set_id or
3503 coa or Fiscal year segment name';
3504 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf);
3505 RETURN;
3506 END;
3507
3508 End If ;
3509
3510 If vp_retcode = 0 Then
3511 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3512 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3513 l_module_name, 'Deriving Period information.....') ;
3514 END IF;
3515
3516 GET_PERIOD_INFO ;
3517 End If ;
3518 If vp_retcode = 0 Then
3519 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3520 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3521 'Processing for each Fund.....');
3522 END IF;
3523 PROCESS_EACH_FUND ;
3524 End If ;
3525 EXCEPTION
3526 -- Exception Processing
3527 WHEN OTHERS THEN
3528 vp_retcode := sqlcode ;
3529 vp_errbuf := sqlerrm ;
3530 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3531 '.final_exception',vp_errbuf);
3532 RETURN;
3533 END PROCESS_BY_FUND_RANGE;
3534 -- -------------------------------------------------------------------
3535 -- PROCEDURE PROCESS_EACH_FUND
3536 -- -------------------------------------------------------------------
3537 -- This procedure does the processing for each fund within the
3538 -- the range of funds (fund_low and fund_high parameters)
3539 -- that are passed. This calls all the subsequent procedures
3540 -- required for trial balance process.
3541 -- Added this procedure for the bug 1399282.
3542 -- ------------------------------------------------------------------
3543 Procedure PROCESS_EACH_FUND
3544 IS
3545 l_module_name VARCHAR2(200) := g_module_name || 'PROCESS_EACH_FUND';
3546 vl_bal_flex_id fnd_id_flex_segments.flex_value_set_id%type;
3547 CURSOR C_Get_Fund_Values
3548 IS
3549 SELECT flex_value
3550 FROM fnd_flex_values_vl
3551 WHERE flex_value_set_id = vl_bal_flex_id
3552 AND flex_value between vp_fund_low and vp_fund_high
3553 AND summary_flag = 'N';
3554
3555 CURSOR C_Get_Rec_Count
3556 IS
3557 SELECT count(*) cnt
3558 FROM fnd_flex_values_vl
3559 WHERE flex_value_set_id = vl_bal_flex_id
3560 AND flex_value between vp_fund_low and vp_fund_high
3561 AND summary_flag = 'N';
3562
3563 vl_req_id number;
3564 l_sob_name gl_ledgers.name%TYPE;
3565
3566
3567 BEGIN
3568
3569 BEGIN
3570 -- Getting the value set id for the Balancing Segment
3571 Select flex_value_set_id
3572 Into vl_bal_flex_id
3573 From fnd_id_flex_segments
3574 Where application_id = 101
3575 And application_column_name = v_bal_seg_name
3576 And id_flex_code = 'GL#'
3577 And id_flex_num = vp_coa_id;
3578 EXCEPTION
3579 When NO_DATA_FOUND Then
3580 vp_retcode := -1 ;
3581 vp_errbuf := 'Error getting Value Set Id for balancing segment'
3582 ||v_bal_seg_name||' [PROCESS_EACH_FUND]' ;
3583 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3584 return;
3585 END;
3586 -- Get the maximum number of records within the fund range.
3587 -- This is useful in submitting the ATB report.
3588 open C_Get_Rec_Count;
3589 fetch C_Get_Rec_Count into v_rec_count;
3590 close C_Get_Rec_Count;
3591
3592
3593 if v_rec_count = 0 then
3594 vp_retcode := -1 ;
3595 fnd_message.set_name('FV','FV_FACTS_FVALUE_NOT_FOUND');
3596 vp_errbuf :=fnd_message.get ;
3597 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf);
3598 return ;
3599 end if;
3600
3601
3602 v_fund_count := 0;
3603 FOR C_Get_Fund_Values_Rec IN C_Get_Fund_Values
3604 LOOP
3605 vp_retcode := 0;
3606 v_fund_value := C_Get_Fund_Values_Rec.flex_value;
3607 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3608 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3609 'Purging FACTS Temp....') ;
3610 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3611 'Fund Vlaue : ' || v_fund_value) ;
3612 END IF;
3613 -- Increment the counter for fund
3614
3615 BEGIN
3616 --Getting the Treasury Symbol value
3617 Select treasury_symbol
3618 Into vp_treasury_symbol
3619 From fv_treasury_symbols
3620 Where treasury_symbol_id = (Select treasury_symbol_id
3621 From fv_fund_parameters
3622 Where fund_value = C_Get_Fund_Values_Rec.flex_value
3623 And set_of_books_id = vp_set_of_books_id)
3624 AND (FACTS_REPORTABLE_INDICATOR like 'II' or FACTS_REPORTABLE_INDICATOR like 'I and II');
3625
3626 -- Getting the treasury_symbol_id
3627 Load_Treasury_Symbol_Id(C_Get_Fund_Values_Rec.flex_value);
3628
3629 -- Getting the Treasury Symbol information
3630 If vp_retcode = 0 Then
3631 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3632 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3633 'Deriving Treasury Symbol information.....');
3634 END IF;
3635 GET_TREASURY_SYMBOL_INFO ;
3636 End if;
3637
3638
3639 EXCEPTION
3640 When NO_DATA_FOUND Then
3641 vp_errbuf := 'No treasury symbol found for the fund '
3642 ||C_Get_Fund_Values_Rec.flex_value||' [PROCESS_EACH_FUND]' ;
3643 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3644 When OTHERS then
3645 vp_errbuf := 'Error populating the treasury symbol for the fund '
3646 ||C_Get_Fund_Values_Rec.flex_value||' [PROCESS_EACH_FUND]'||SQLERRM ;
3647 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, vp_errbuf) ;
3648 END;
3649
3650 --Bug No # 2450918
3651 If vp_retcode = 0 Then
3652 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3653 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3654 'Starting TB Main Process.....') ;
3655 END IF;
3656 PROCESS_FACTS_TRANSACTIONS ;
3657 End If ;
3658
3659 END LOOP;
3660
3661 --Added to get sob name since
3662 --profile was getting the inappropriate name
3663 SELECT name
3664 INTO l_sob_name
3665 FROM gl_ledgers
3666 WHERE ledger_id = vp_set_of_books_id
3667 AND currency_code = 'USD';
3668
3669
3670 -- Submitting TB Report
3671 -----------------------------------------------------------------
3672 -- Bug 9031886
3673 vl_req_id :=
3674 FND_REQUEST.SUBMIT_REQUEST ('FV','RXFVFCTB','','',FALSE,
3675 'DIRECT',
3676 vp_report_id,
3677 vp_attribute_set,
3678 vp_output_format,
3679 vp_set_of_books_id,
3680 vp_fund_low,
3681 vp_fund_high,
3682 vp_currency_code,
3683 vp_period_name);
3684 COMMIT;
3685
3686 if vl_req_id = 0 then
3687 vp_errbuf := 'Error submitting RX Report ';
3688 vp_retcode := -1 ;
3689 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, vp_errbuf) ;
3690 return;
3691 Else
3692 -- if concurrent request submission failed then abort process
3693 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3694 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3695 'Concurrent Request Id for RX Report - ' ||vl_req_id);
3696 END IF;
3697 end if;
3698
3699 ---------------------------------------------------------------
3700 EXCEPTION
3701 WHEN OTHERS THEN
3702 vp_retcode := sqlcode ;
3703 vp_errbuf := sqlerrm ;
3704 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
3705 '.final_exception',vp_errbuf);
3706 RETURN;
3707 END PROCESS_EACH_FUND;
3708 --------------------------------------------------------------------------------
3709 PROCEDURE get_prc_val(p_catb_program_val IN VARCHAR2,
3710 p_catb_rc_val OUT NOCOPY VARCHAR2,
3711 p_catb_rc_desc OUT NOCOPY VARCHAR2,
3712 p_prn_program_val IN VARCHAR2,
3713 p_prn_rc_val OUT NOCOPY VARCHAR2,
3714 p_prn_rc_desc OUT NOCOPY VARCHAR2)
3715 IS
3716
3717 l_module_name VARCHAR2(200) := g_module_name || 'get_prc_val';
3718 vl_prc_found VARCHAR2(1) := 'N';
3719 vl_prc_header_id NUMBER(15);
3720 vl_prc_val VARCHAR2(10);
3721 vl_prc_desc VARCHAR2(100);
3722 vl_program_val VARCHAR2(50);
3723 vl_prc_flag VARCHAR2(1);
3724 vl_prc_count NUMBER;
3725 vl_prg_val_set_id NUMBER(15);
3726 vl_seg_txt VARCHAR2(100);
3727 vl_segment VARCHAR2(50);
3728 BEGIN
3729 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3730 'get_prc_val:'||p_catb_program_val);
3731 For I in 1..2
3732 Loop
3733 IF I = 1 THEN
3734 vl_prc_header_id := v_catb_rc_header_id ;
3735 vl_program_val := p_catb_program_val;
3736 vl_prc_flag := v_catb_rc_flag;
3737 vl_prg_val_set_id := v_catb_prg_val_set_id;
3738 vl_segment := v_catb_prg_seg_name;
3739
3740 ELSE
3741 vl_prc_header_id := v_prn_rc_header_id ;
3742 vl_program_val := p_prn_program_val;
3743 vl_prc_flag := v_prn_rc_flag;
3744 vl_prg_val_set_id := v_prn_prg_val_set_id;
3745 vl_segment := v_prn_prg_seg_name;
3746 END IF;
3747
3748 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3749 'vl_prc_header_id:vl_program_val:vl_prc_flag:vl_prg_val_set_id:vl_segment:'||vl_prc_header_id
3750 ||'|'||vl_program_val||'|'||vl_prc_flag||'|'||vl_prg_val_set_id||'|'||vl_segment);
3751
3752 vl_prc_found := 'N';
3753
3754 IF vl_prc_flag = 'Y' THEN
3755
3756 BEGIN
3757 SELECT reporting_code, reporting_desc
3758 INTO vl_prc_val, vl_prc_desc
3759 FROM fv_facts_prc_dtl
3760 WHERE prc_header_id = vl_prc_header_id
3761 AND program_value = vl_program_val
3762 AND set_of_books_id = vp_set_of_books_id;
3763
3764 vl_prc_found := 'Y';
3765
3766 EXCEPTION
3767 WHEN NO_DATA_FOUND THEN NULL;
3768 WHEN OTHERS THEN
3769 vp_errbuf := SQLERRM;
3770 vp_retcode := -1;
3771 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3772 l_module_name||'.exception1',vp_errbuf);
3773
3774 END;
3775
3776 IF vl_prc_found = 'N' THEN
3777 BEGIN
3778 SELECT reporting_code, reporting_desc
3779 INTO vl_prc_val, vl_prc_desc
3780 FROM fv_facts_prc_dtl
3781 WHERE prc_header_id = vl_prc_header_id
3782 AND program_value = 'ALL'
3783 AND set_of_books_id = vp_set_of_books_id;
3784
3785 vl_prc_found := 'Y';
3786
3787 EXCEPTION
3788 WHEN NO_DATA_FOUND THEN NULL;
3789 WHEN OTHERS THEN
3790 vp_errbuf := SQLERRM;
3791 vp_retcode := -1;
3792 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3793 l_module_name||'.exception2',vp_errbuf);
3794 END;
3795 END IF;
3796
3797 END IF;
3798
3799
3800 IF (vl_prc_flag = 'N' )
3801 THEN
3802 BEGIN
3803 vl_prc_val := LPAD(TO_CHAR(TO_NUMBER(vl_program_val)),3,'0');
3804 EXCEPTION
3805 WHEN OTHERS THEN
3806 vp_errbuf := 'The Reporting Code mapping segment value '||
3807 'should '||
3808 'be a Numeric Value.';
3809 vp_retcode := -1;
3810 vl_prc_val := NULL;
3811 vl_prc_desc := NULL;
3812
3813 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3814 l_module_name||'.exceptionx3',vp_errbuf);
3815
3816 END;
3817 get_segment_text(vl_program_val,
3818 vl_prg_val_set_id,
3819 vl_seg_txt ) ;
3820 IF vp_retcode <> 0 THEN
3821 RETURN ;
3822 END IF ;
3823 vl_prc_desc := vl_seg_txt;
3824
3825
3826 ELSIF vl_prc_flag = 'Y' AND vl_prc_found = 'N' THEN
3827
3828 vl_prc_val := NULL;
3829 vl_prc_desc := NULL;
3830
3831 IF I = 2 THEN
3832 vl_prc_val := '099';
3833 vl_prc_desc := 'PRC not Assigned';
3834 END IF;
3835
3836 ELSIF vl_prc_found = 'Y' THEN
3837 vl_prc_val := LPAD(TO_CHAR(TO_NUMBER(vl_prc_val)),3,'0');
3838 END IF;
3839
3840
3841 IF I = 1 THEN
3842 IF va_appor_cat_val = 'A' THEN
3843 p_catb_rc_desc := 'Default CAT B Code';
3844 --p_catb_rc_val := '000';
3845
3846
3847 ELSE
3848 p_catb_rc_desc := vl_prc_desc;
3849 p_catb_rc_val := vl_prc_val;
3850 END IF;
3851 ELSE
3852 p_prn_rc_desc := vl_prc_desc;
3853 p_prn_rc_val := vl_prc_val;
3854
3855 END IF;
3856 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3857 'p_catb_rc_val:p_catb_rc_desc:p_prn_rc_desc:p_prn_rc_val:reporting_code:vl_prc_desc::'
3858 ||p_catb_rc_val||'|'||p_catb_rc_desc||'|'||p_prn_rc_desc||'|'||p_prn_rc_val||'|'||vl_prc_val||'|'||vl_prc_desc);
3859
3860 END LOOP;
3861
3862 IF va_appor_cat_val = 'A' THEN
3863
3864 p_catb_rc_desc := 'Default Cat B Code';
3865 -- p_catb_rc_val := '000';
3866
3867
3868 END IF;
3869
3870 EXCEPTION
3871 WHEN OTHERS THEN
3872 vp_errbuf := SQLERRM ||'[GET_PRC_VAL]';
3873 vp_retcode := -1;
3874 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3875 l_module_name||'.final_exception',vp_errbuf);
3876 RAISE;
3877
3878 END get_prc_val;
3879 --------------------------------------------------------------------------------
3880 /*
3881 PROCEDURE process_cat_b_seq(reported_type IN VARCHAR2) IS
3882 l_module_name VARCHAR2(200) := g_module_name || 'process_cat_b_seq';
3883
3884 CURSOR cat_b_cur(reported_type VARCHAR2) IS
3885 SELECT rowid, tbal_fund_value, sgl_acct_number, appor_cat_b_txt
3886 FROM fv_facts_temp
3887 WHERE fct_int_record_category = reported_type
3888 AND appor_cat_code = 'B'
3889 AND TRIM(appor_cat_b_txt) IS NOT NULL
3890 ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3891
3892 l_seq NUMBER;
3893 l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3894 l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3895 l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3896 l_count NUMBER;
3897
3898 BEGIN
3899
3900 l_count := 1;
3901
3902 FOR cat_b_rec IN cat_b_cur(reported_type)
3903 LOOP
3904 IF l_count = 1 THEN
3905 l_seq := 1;
3906 ELSIF
3907 (l_old_fund = cat_b_rec.tbal_fund_value
3908 AND l_old_account = cat_b_rec.sgl_acct_number
3909 AND l_old_cat_b_txt = cat_b_rec.appor_cat_b_txt)
3910 THEN NULL;
3911 ELSIF
3912 (l_old_fund = cat_b_rec.tbal_fund_value
3913 AND l_old_account = cat_b_rec.sgl_acct_number
3914 AND l_old_cat_b_txt <> cat_b_rec.appor_cat_b_txt)
3915 THEN l_seq := l_seq + 1;
3916 ELSE
3917 l_seq := 1;
3918 END IF;
3919
3920 UPDATE fv_facts_temp
3921 SET appor_cat_b_dtl = LPAD(to_char(l_seq), 3, '0')
3922 WHERE rowid = cat_b_rec.rowid;
3923
3924 l_old_fund := cat_b_rec.tbal_fund_value;
3925 l_old_account := cat_b_rec.sgl_acct_number;
3926 l_old_cat_b_txt := cat_b_rec.appor_cat_b_txt;
3927
3928 l_count := 99;
3929 END LOOP;
3930
3931 EXCEPTION
3932 WHEN OTHERS THEN
3933 vp_errbuf := SQLERRM;
3934 vp_retcode := -1;
3935 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf);
3936 RAISE;
3937
3938 END process_cat_b_seq;
3939 */
3940
3941 -- -------------------------------------------------------------------
3942 PROCEDURE get_trx_part_from_reimb
3943 (p_reimb_agree_seg_val IN VARCHAR2) IS
3944
3945 l_module_name VARCHAR2(200) := g_module_name || 'get_trx_part_from_reimb';
3946 l_cust_class_code VARCHAR2(25);
3947 BEGIN
3948 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3949 'p_reimb_agree_seg_val:'||p_reimb_agree_seg_val);
3950 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3951 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3952 'BEGIN '||l_module_name);
3953 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3954 'p_reimb_agree_seg_val:'||p_reimb_agree_seg_val);
3955 END IF;
3956 SELECT hzca.customer_class_code
3957 INTO l_cust_class_code
3958 FROM ra_customer_trx_all rct,
3959 hz_cust_accounts_all hzca
3960 WHERE rct.trx_number = p_reimb_agree_seg_val
3961 AND rct.set_of_books_id = vp_set_of_books_id
3962 AND hzca.cust_account_id = rct.bill_to_customer_id;
3963
3964 IF l_cust_class_code = 'FEDERAL' THEN
3965 va_transaction_partner_val := 'F';
3966 ELSIF l_cust_class_code <> 'FEDERAL' THEN
3967 va_transaction_partner_val := 'X';
3968 END IF;
3969
3970 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3971 'va_transaction_partner_val:'||va_transaction_partner_val);
3972
3973 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3974 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3975 'g_transaction_partner_val:'||va_transaction_partner_val);
3976 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3977 'END '||l_module_name);
3978 END IF;
3979 EXCEPTION
3980 WHEN NO_DATA_FOUND THEN
3981 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3982 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3983 'No record found for trx number: '||p_reimb_agree_seg_val);
3984 END IF;
3985 WHEN OTHERS THEN
3986 vp_retcode := -1;
3987 vp_errbuf := SQLERRM;
3988 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3989 l_module_name||'.final_exception',vp_errbuf);
3990 END get_trx_part_from_reimb;
3991 --------------------------------------------------------------------------------
3992 BEGIN
3993 g_module_name := 'fv.plsql.FV_FACTS_TBAL_TRX.';
3994 -- -------------------------------------------------------------------
3995
3996 -- -------------------------------------------------------------------
3997 -- End Of the Package Body
3998 -- -------------------------------------------------------------------
3999 END FV_FACTS_TBAL_TRX;
4000