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