[Home] [Help]
PACKAGE BODY: APPS.FV_SF133_ONEYEAR
Source
1 PACKAGE BODY fv_sf133_oneyear AS
2 --$Header: FVSF133B.pls 120.41 2010/07/19 05:30:05 amaddula ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100) ;
5
6 -- ------------------------------------
7 -- Stored Input Parameters
8 -- ------------------------------------
9 v_debug BOOLEAN := TRUE;
10 parm_application_id NUMBER;
11 parm_set_of_books_id NUMBER;
12 parm_gl_period_year NUMBER;
13 parm_gl_period_num NUMBER;
14 parm_treasury_value_r1 VARCHAR2(35);
15 parm_run_mode VARCHAR2(10);
16
17 -- New Variable declared by Surya on 04/30/98 to receive the value of
18 -- the passed quarter number
19 parm_gl_period_name gl_period_statuses.period_name%TYPE;
20 parm_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
21
22 -- ------------------------------------
23 -- All Pre-build Query Variables
24 -- ------------------------------------
25 g_chart_of_accounts_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
26 g_fund_segment_name VARCHAR2(10);
27 --
28 -- ------------------------------------
29 -- Stored Global Variables
30 -- ------------------------------------
31 g_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
32 g_federal_acct_symbol_id number(15);
33 g_insert_count NUMBER;
34 g_error_code NUMBER;
35 g_error_message VARCHAR2(400);
36 --
37 g_period_num NUMBER;
38 g_ts_value_in_process VARCHAR2(25);
39 g_total_start_line_number NUMBER;
40 g_subtotal_start_line_number NUMBER;
41 g_column_number NUMBER;
42 g_currency_code VARCHAR2(15);
43 --g_currency_code added for bug No. 1553099
44
45 c_total_balance NUMBER;
46 c_ending_balance NUMBER;
47 c_begin_balance NUMBER;
48 -- c_begin_select VARCHAR2(200);
49 -- c_end_select VARCHAR2(200);
50 c_begin_period VARCHAR2(40);
51 c_end_period VARCHAR2(40);
52
53 -- New variables declared by Narsimha to get the resource type from fv_treasury_sybols.
54
55 c_resource_type fv_treasury_symbols.resource_type%TYPE;
56 c_rescission_flag varchar2(10);
57
58 -- New variables declared by Narsimha Balakkari on 04/07/99 to capture
59 -- Established Year and Cancellation Year for given treasury symbol
60
61 g_established_year NUMBER;
62 g_cancellation_year NUMBER;
63
64 -- ---------- Flex Segment Name Cursor Variables ---------
65 c_segment_name fnd_id_flex_segments.segment_name%TYPE;
66 c_flex_column_name fnd_id_flex_segments.application_column_name%TYPE;
67 --
68 v_balance_column_name fnd_id_flex_segments.application_column_name%TYPE;
69 g_seg_value_set_id FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID%TYPE;
70 -- ---------- Treasury Symbol Report Line Cursor Vaiables -----------
71 c_sf133_ts_value gl_code_combinations.segment1%TYPE;
72 c_sf133_line_id fv_sf133_definitions_lines.sf133_line_id%TYPE;
73 c_sf133_line_number fv_sf133_definitions_lines.sf133_line_number%TYPE;
74 c_sf133_prev_line_number fv_sf133_definitions_lines.sf133_line_number%TYPE;
75 c_sf133_line_type_code fv_sf133_definitions_lines.sf133_line_type_code%TYPE;
76 c_sf133_natural_bal_type fv_sf133_definitions_lines.sf133_natural_balance_type%TYPE;
77 c_sf133_line_category fv_sf133_definitions_lines.sf133_fund_category%TYPE;
78
79 -- New variable declared by pkpatel to fix Bug 1575992
80 c_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
81 --sf133 begin
82 c_acct_number fv_sf133_definitions_accts.acct_number%TYPE;
83 c_direct_or_reimb_code fv_sf133_definitions_accts.direct_or_reimb_code%TYPE;
84 c_apportionment_category_code fv_sf133_definitions_accts.apportionment_category_code%TYPE;
85 c_category_b_code fv_sf133_definitions_accts.category_b_code%TYPE;
86 c_prc_code fv_sf133_definitions_accts. prc_code%TYPE;
87 c_advance_code fv_sf133_definitions_accts.advance_code%TYPE;
88 c_availability_time fv_sf133_definitions_accts.availability_time%TYPE;
89 c_bea_category_code fv_sf133_definitions_accts.bea_category_code%TYPE;
90 c_borrowing_source_code fv_sf133_definitions_accts.borrowing_source_code%TYPE;
91 c_transaction_partner fv_sf133_definitions_accts.transaction_partner%TYPE;
92 c_year_of_budget_authority fv_sf133_definitions_accts.year_of_budget_authority%TYPE;
93 c_prior_year_adjustment fv_sf133_definitions_accts.prior_year_adjustment%TYPE;
94 c_authority_type fv_sf133_definitions_accts.authority_type%TYPE;
95 c_tafs_status fv_sf133_definitions_accts.tafs_status%TYPE;
96 c_availability_type fv_sf133_definitions_accts.availability_type%TYPE;
97 c_expiration_flag fv_sf133_definitions_accts.expiration_flag%TYPE;
98 c_fund_type fv_sf133_definitions_accts.fund_type%TYPE;
99 c_financing_account_code fv_sf133_definitions_accts.financing_account_code%TYPE;
100
101 exp_date date;
102 beg_date date;
103 close_date date;
104 whether_Exp varchar2(1);
105 report_period_num NUMBER ;
106
107 whether_Exp_SameYear varchar2(1);
108 expiring_year number;
109
110 errbuf_facts VARCHAR2(1000);
111 retcode_facts NUMBER;
112 p_ledger_id NUMBER;
113 treasury_symbol VARCHAR2(15);
114 report_fiscal_yr NUMBER ;
115
116 run_mode_fact VARCHAR2(15);
117 contact_fname VARCHAR2(15);
118 contact_lname VARCHAR2(15);
119 contact_phone NUMBER ;
120 contact_extn NUMBER ;
121 contact_email VARCHAR2(15);
122 contact_fax NUMBER;
123 contact_maiden VARCHAR2(15);
124 supervisor_name VARCHAR2(15);
125 supervisor_phone NUMBER ;
126 supervisor_extn NUMBER ;
127 agency_name_1 VARCHAR2(15);
128 agency_name_2 VARCHAR2(15);
129 address_1 VARCHAR2(15);
130 address_2 VARCHAR2(15);
131 city VARCHAR2(15);
132 state VARCHAR2(15);
133 zip VARCHAR2(15);
134 currency_code VARCHAR2(15);
135
136
137 l_year_counter Number ; -- FOR loop counter
138 l_process_year Number ; -- Process Year for Previous Years
139 L_BEG_PERIOD_PREV NUMBER ; -- Beginning Period-Previous Year
140 L_END_PERIOD_PREV NUMBER ; -- Ending period-previous year
141 L_LOOP_YEAR NUMBER;
142 l_federal_acct_symbol_id number(15);
143
144 -- new variabla declared by Narsimha.
145
146 c_sf133_report_line_number fv_sf133_definitions_lines.sf133_report_line_number%TYPE;
147 --
148 -- ---------- Balance Type Cursor Vaiables ---------
149 c_sf133_line_acct_id fv_sf133_definitions_accts.sf133_line_acct_id%TYPE;
150 c_sf133_balance_type fv_sf133_definitions_accts.sf133_balance_type%TYPE;
151
152 -- new variables declared by Narsimha.
153
154 -- c_sf133_apportion_amt number;
155 c_sf133_additional_info fv_sf133_definitions_accts.sf133_additional_info%TYPE;
156
157 -- ---------- Treasury Symbol Accummulation Cursor Vaiables ---------
158 c_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
159 c_sf133_amount_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
160 --
161 -- ---------- Output Report Line Column Data -------------
162 o_sf133_ts_value fv_sf133_definitions_cols_temp.sf133_fund_value%TYPE;
163 o_sf133_line_id fv_sf133_definitions_cols_temp.sf133_line_id%TYPE;
164 o_sf133_column_number fv_sf133_definitions_cols_temp.sf133_column_number%TYPE;
165 o_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
166 o_sf133_amt_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
167
168 -- New variable declared by pkpatel to fix Bug 1575992
169 o_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
170 -- New Variables for using dynamic SQL
171 v_select VARCHAR2(30000);
172 v_cursor_id INTEGER;
173 v_cursor_id_ind INTEGER;
174
175
176 c_sf133_amt2_not_shown Number ;
177 c_sf133_amt3_not_shown Number ;
178 c_sf133_amt4_not_shown Number ;
179 c_sf133_amt5_not_shown Number ;
180 c_sf133_amt6_not_shown Number ;
181
182 c_sf133_column_amount2 Number ;
183 c_sf133_column_amount3 Number ;
184 c_sf133_column_amount4 Number ;
185 c_sf133_column_amount5 Number ;
186 c_sf133_column_amount6 Number ;
187
188 --
189 --Added ts_range_cursor as part of Enh #2129123
190 /* Cursor to select treasury symbols which fall in specified range */
191 CURSOR ts_range_cursor(tsymbol_r1 VARCHAR2,tsymbol_r2 VARCHAR2) IS
192 SELECT treasury_symbol,treasury_symbol_id
193 FROM fv_treasury_symbols
194 WHERE treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
195 AND time_frame ='A' --CGAC
196 AND (fund_group_code NOT BETWEEN '3800' AND '3899')
197 AND (fund_group_code NOT BETWEEN '6001' AND '6999')
198 AND set_of_books_id = parm_set_of_books_id
199 ORDER BY treasury_symbol;
200
201 DSum_E NUMBER;
202 CSum_E NUMBER;
203 DSum_B NUMBER;
204 CSum_B NUMBER;
205 e_bal_indicator VARCHAR2(1);
206 b_bal_indicator VARCHAR2(1);
207
208 -- ---------- Define Segment Name Cursor -----------------
209 CURSOR flex_field_column_name_cursor
210 IS
211 SELECT UPPER(glflex.segment_name) segment_name,
212 UPPER(glflex.application_column_name) flex_column_name
213 FROM fnd_id_flex_segments glflex
214 WHERE glflex.application_id = 101
215 AND glflex.id_flex_num = g_chart_of_accounts_id
216 AND glflex.id_flex_code = 'GL#'
217 ORDER BY glflex.application_column_name;
218 --
219 -- ---------- Define Report Treasury Symbol Line Cursor -------------
220 -- MODIFIED BY SURYA ON 5/6/98 TO REPLACE FV_FUND_PARAMETERS WITH
221 -- FV_TREASURY_SYMBOLS
222
223 -- Modified by Surya on 1/20/99 to add another join for SOB to fix
224 -- data duplication
225 --pkpatel :Changed to fix Bug 1575992
226 CURSOR ts_report_line_cursor
227 IS
228 SELECT
229 FTS.treasury_symbol sf133_ts_value,
230 FTS.treasury_symbol_id sf133_treasury_symbol_id,
231 line.sf133_line_id sf133_line_id,
232 line.sf133_line_number sf133_line_number,
233 line.sf133_line_type_code sf133_line_type_code,
234 line.sf133_natural_balance_type sf133_natural_balance_type,
235 line.sf133_fund_category sf133_line_category,
236 line.sf133_report_line_number sf133_report_line_number
237 FROM fv_sf133_definitions_lines line,
238 FV_TREASURY_SYMBOLS FTS
239 WHERE FTS.Treasury_symbol = parm_treasury_value_r1
240 AND FTS.set_of_books_id = parm_set_of_books_id
241 AND (line.sf133_line_type_code) IN ('T', 'D', 'D2')
242 AND line.set_of_books_id = FTS.set_of_books_id
243 ORDER BY FTS.treasury_symbol,
244 line.sf133_line_number ;
245 --
246 -- ---------- Determine Balance Type of Acct -------------
247 --
248 CURSOR balance_type_cursor
249 IS
250 SELECT sf133_line_acct_id,
251 sf133_balance_type,
252 acct_number,
253 direct_or_reimb_code,
254 apportionment_category_code,
255 category_b_code,
256 prc_code,
257 advance_code,
258 availability_time,
259 bea_category_code,
260 borrowing_source_code,
261 transaction_partner,
262 year_of_budget_authority,
263 prior_year_adjustment,
264 authority_type,
265 tafs_status,
266 availability_type,
267 expiration_flag,
268 fund_type,
269 financing_account_code
270 FROM fv_sf133_definitions_accts
271 WHERE sf133_line_id = c_sf133_line_id ;
272
273 PROCEDURE determine_acct_flex_segments;
274 PROCEDURE purge_temp_table;
275 PROCEDURE build_report_lines;
276 PROCEDURE build_fiscal_line_columns(c_begin_period Number,
277 c_end_period Number, c_fiscal_year Number);
278 PROCEDURE build_total_line_columns;
279 PROCEDURE populate_temp_table;
280 PROCEDURE populate_gtt_with_ccid
281 (
282 p_treasury_symbol_id NUMBER
283 );
284
285 PROCEDURE GET_BAL_TYPE;
286 PROCEDURE process_total_line;
287
288 --
289 -- Added by Surya on 05/08/98 to get beginning and ending periods
290 -- for a given Fiscal year.
291 PROCEDURE GET_BEGIN_ENDING_PERIODS( V_PROCESS_YEAR NUMBER,
292 V_BEGIN_PERIOD IN OUT NOCOPY NUMBER,
293 V_END_PERIOD IN OUT NOCOPY NUMBER ) ;
294 abort_error EXCEPTION ;
295 --
296 -- ---------- End of Package Level Declaritives -----------------------------
297 --
298 -- ------------------------------------------------------------------
299 PROCEDURE Main
300 (
301 errbuf OUT NOCOPY VARCHAR2,
302 retcode OUT NOCOPY NUMBER,
303 run_mode IN VARCHAR2,
304 set_of_books_id IN NUMBER,
305 gl_period_year IN NUMBER,
306 gl_period_name IN VARCHAR2,
307 treasury_symbol_r1 IN VARCHAR2,
308 treasury_symbol_r2 IN VARCHAR2)
309 --
310 IS
311 --
312 l_module_name VARCHAR2(200) ;
313 /*Variables used to store Request Details */
314 l_req_id NUMBER :=NULL;
315 --l_status VARCHAR2(30);
316 --l_phase VARCHAR2(30);
317 --l_devphase VARCHAR2(30);
318 --l_devstatus VARCHAR2(30);
319 --l_message VARCHAR2(300);
320 --l_boolean BOOLEAN;
321
322
323 BEGIN
324 l_module_name := g_module_name || 'Main';
325 --
326
327 -- ------------------------------------
328 -- Store Input Parameters in Global Variables
329 -- ------------------------------------
330 if v_debug then
331 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
332 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF PROGRAM');
333 END IF;
334 end if;
335 parm_application_id := '101';
336 parm_set_of_books_id := set_of_books_id;
337 parm_gl_period_year := gl_period_year;
338 parm_gl_period_name := gl_period_name;
339 parm_run_mode := UPPER(run_mode);
340
341
342
343 select currency_code,
344 chart_of_accounts_id
345 into g_currency_code,
346 g_chart_of_accounts_id
347 from gl_ledgers_public_v
348 where ledger_id = parm_set_of_books_id;
349 --Added for bug No. 1553099
350
351 -- ----------------------------------------
352 -- Display Program Initialization
353 -- ----------------------------------------
354
355 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
356 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FVSF133 STARTING, '
357 ||' Run Mode is '||parm_run_mode);
358 END IF;
359
360
361 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
362 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- APID('||NVL(PARM_APPLICATION_ID,0) ||')'
363 ||' SoB('||NVL(parm_set_of_books_id,0) ||')'
364 ||' Year('||NVL(parm_gl_period_year,0) ||')'
365 ||' Period('||NVL(parm_gl_period_num,0) ||')'
366 ||' Fund Code('||NVL(parm_treasury_value_r1,'Null')
367 ||')');
368 END IF;
369
370
371 --
372 -- ----------------------------------------
373 -- Initialize Program Row Counts and Variables
374 -- ----------------------------------------
375 g_insert_count := 0;
376 g_error_code := 0;
377 g_error_message := NULL;
378 retcode := 0;
379 errbuf := '';
380 --
381 IF g_error_code = 0 THEN
382 -- ------------------------------------
383 -- Delete All Entries from Report Temp Table
384 -- ------------------------------------
385 purge_temp_table;
386 END IF;
387
388 IF g_error_code = 0 THEN
389 -- ----------------------------------------
390 -- Build Report Lines
391 -- ----------------------------------------
392 sf133_runmode := 'YES';
393
394 /* Processing for Treasury symbols done in a LOOP to handle Multiple Treasury symbols */
395 FOR ts_rec IN ts_range_cursor(treasury_symbol_r1,treasury_symbol_r2)
396 LOOP
397 -- New code added by Narsimha Balakkari to get the established year and
398 -- cancellation year for specific treasury symbol
399 parm_treasury_value_r1 := ts_rec.treasury_symbol;
400 parm_treasury_symbol_id := ts_rec.treasury_symbol_id;
401 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'parm_treasury_value_r1.......'|| parm_treasury_value_r1);
402
403 --populate_gtt_with_ccid (parm_treasury_symbol_id);
404
405 SELECT established_fiscal_yr, substr(cancellation_date,8,4)
406 INTO g_established_year, g_cancellation_year
407 FROM fv_treasury_symbols
408 WHERE treasury_symbol = parm_treasury_value_r1
409 AND set_of_books_id = parm_set_of_books_id ;
410
411 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
412 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'PROCESSING FOR TREASURY SYMBOL .......'|| PARM_TREASURY_VALUE_R1);
413 END IF;
414
415 --Fetch the Federal Acct Symbol Id for the TS
416
417 SELECT federal_acct_symbol_id
418 INTO g_federal_acct_symbol_id
419 FROM fv_treasury_symbols
420 WHERE set_of_books_id = parm_set_of_books_id
421 AND treasury_symbol_id = parm_treasury_symbol_id;
422 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'g_federal_acct_symbol_id.......'|| g_federal_acct_symbol_id);
423
424 -- SF133: check if the treasury symbols for the previous 5 years pass factsii edit checks
425
426 L_LOOP_YEAR := g_established_year ;
427
428 SELECT PERIOD_NUM
429 INTO parm_gl_period_num
430 FROM GL_PERIOD_STATUSES
431 WHERE LEDGER_ID = parm_set_of_books_id AND
432 PERIOD_YEAR = parm_gl_period_year AND
433 APPLICATION_ID = '101' AND
434 CLOSING_STATUS in ('O','C') AND
435 PERIOD_NAME = parm_gl_period_name;
436
437 For l_year_counter IN 1..6 Loop --run FACTS for current year + previous 5 years' treasury symbols
438 -- Determine the Previous Year
439 L_PROCESS_YEAR := PARM_GL_PERIOD_YEAR ;
440
441 --Fetch the Treasury symbol for previous year
442 begin
443 select treasury_symbol,treasury_symbol_id
444 into c_sf133_ts_value,g_treasury_symbol_id
445 from fv_treasury_symbols
446 WHERE set_of_books_id = parm_set_of_books_id
447 and federal_acct_symbol_id = g_federal_acct_symbol_id
448 and established_fiscal_yr = l_loop_year
449 and time_frame = 'A'; --CGAC
450
451 exception
452 when no_data_found then
453 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,SQLERRM);
454 exit;
455 end;
456
457 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
458 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TREASURY SYMBOL IS '||C_SF133_TS_VALUE );
459 --FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_BEG_PERIOD_PREV IS '||L_BEG_PERIOD_PREV );
460 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_LOOP_YEAR IS '||l_loop_year );
461 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PARM_GL_PERIOD_NUM IS '||parm_gl_period_num );
462 END IF;
463
464
465 FV_FACTS_TRANSACTIONS.main(errbuf_facts, retcode_facts, parm_set_of_books_id, c_sf133_ts_value, L_PROCESS_YEAR, parm_gl_period_num, run_mode_fact, contact_fname,
466 contact_lname, contact_phone, contact_extn, contact_email, contact_fax,
467 contact_maiden, supervisor_name, supervisor_phone, supervisor_extn, agency_name_1,
468 agency_name_2, address_1, address_2, city, state, zip, g_currency_code);
469
470 IF(retcode_facts <> 0 )then
471 IF(retcode_facts = 1 )then
472 if (FV_FACTS_TRANSACTIONS.v_g_edit_check_code = 2)then
473 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
474 'Required Edits failed for the Treasury Symbol...'|| PARM_TREASURY_VALUE_R1||errbuf_facts);
475 retcode :=1;
476 END IF;
477 END IF;
478 END IF;
479
480 exit when FV_FACTS_TRANSACTIONS.v_g_edit_check_code = 2; --hardedit check failed
481 L_LOOP_YEAR := L_LOOP_YEAR - 1;
482 end loop;
483
484 if( FV_FACTS_TRANSACTIONS.v_g_edit_check_code <> 2 ) then -- hard edit did not fail for all 6 years' treasury symbols
485 build_report_lines;
486 IF g_error_code <> 0 THEN
487 errbuf := errbuf || 'Processing for Treasury Symbol .......'|| parm_treasury_value_r1 || 'FAILED'|| g_error_message;
488 ELSE
489 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING SF133 REPORT FOR TREASURY SYMBOL......' || PARM_TREASURY_VALUE_R1);
491 END IF;
492 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV','FVXBEGLP','','',FALSE,parm_set_of_books_id,--g_chart_of_accounts_id,
493 parm_gl_period_year,parm_gl_period_name,parm_treasury_value_r1);
494 IF l_req_id = 0 THEN
495 errbuf := 'Error submitting SF133 Report for Treasury Symbol'|| parm_treasury_value_r1 ;
496 retcode := -1;
497 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1', errbuf) ;
498 return;
499 ELSE
500 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
501 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CONCURRENT REQUEST ID FOR SF133 REPORT - ' || L_REQ_ID);
502 END IF;
503 END IF;
504 END IF;
505 -- Committing here to avoid deleting the temporary table
506 COMMIT;
507 end if; --sf133; end for if( FV_FACTS_TRANSACTIONS.v_g_edit_check_code <> 2 )
508 END LOOP;
509
510 if ts_range_cursor%ISOPEN then
511 close ts_range_cursor;
512 end if;
513
514 END IF;
515
516 IF g_error_code <> 0 THEN
517 RAISE abort_error;
518 END IF;
519 --
520
521 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- INSERT COUNT('||G_INSERT_COUNT||')');
523 END IF;
524 IF errbuf IS NOT null THEN
525 errbuf := 'Normal End of FVSF133 package';
526 END IF;
527 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,ERRBUF);
529 END IF;
530 -- ------------------------------------
531 -- Exceptions
532 -- ------------------------------------
533 sf133_runmode := 'NO';
534
535 EXCEPTION
536 --
537 WHEN abort_error THEN
538 retcode := g_error_code;
539 errbuf := g_error_message;
540 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1', errbuf) ;
541 WHEN OTHERS THEN
542 g_error_code := SQLCODE;
543 g_error_message := SQLERRM;
544 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
545 RAISE_APPLICATION_ERROR(-20222,'FVSF133 Exception-'||SQLERRM);
546 END Main;
547 -- ------------------------------------------------------------------
548 -- --------------------------------------------------------
549 PROCEDURE determine_acct_flex_segments
550 --
551 AS
552 l_module_name VARCHAR2(200);
553
554 -- for data access security
555 das_id NUMBER;
556 das_where VARCHAR2(600);
557 --
558 BEGIN
559 l_module_name := g_module_name || 'determine_acct_flex_segments';
560 --
561 IF parm_run_mode = 'T' THEN
562 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START DETERMINE_ACCT_FLEX_SEGMENTS');
564 END IF;
565 END IF;
566 --
567 -- -------------------------------------
568 -- Store SoB's Chart of Accounts Id
569 -- -------------------------------------
570 SELECT chart_of_accounts_id
571 INTO g_chart_of_accounts_id
572 FROM gl_ledgers_public_v
573 WHERE ledger_id = parm_set_of_books_id;
574 --
575 /* SELECT statement brought OUT NOCOPY of the LOOP as it does nto use any of the loop variables */
576 -- find the balance segment (fund) application_column_name
577 SELECT application_column_name
578 INTO v_balance_column_name
579 FROM fnd_segment_attribute_values
580 WHERE application_id = 101
581 AND id_flex_code = 'GL#'
582 AND id_flex_num = g_chart_of_accounts_id
583 AND segment_attribute_type = 'GL_BALANCING'
584 AND attribute_value = 'Y';
585
586 /* Used dynamic SQL instead of balance_cursor to improve performance */
587 v_select := 'SELECT decode(:cv_balance_type, ' ||
588 ''''|| 'B' || '''' || ',' || '
589 ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
590 NVL(glbal.begin_balance_cr,0)
591 ),0),2),' ||
592 ''''|| 'E' || '''' || ',' || '
593 ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
594 NVL(glbal.begin_balance_cr,0))
595 + (NVL(glbal.period_net_dr,0) -
596 NVL(glbal.period_net_cr,0))),0),2),'||
597 ''''|| 'P' || '''' || ',' || '
598 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
599 +
600 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),-1,0,
601 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
602 +
603 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),'||
604 ''''|| 'N' || '''' || ',' || '
605 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
606 +
607 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),1,0,
608 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
609 +
610 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2))) ' || '
611 FROM gl_balances glbal,
612 fv_sf133_definitions_accts acct,
613 fv_sf133_ccids_gt fscg
614 WHERE glbal.ledger_id = :cv_set_of_books_id
615 AND glbal.period_year = :cv_fiscal_year
616 AND glbal.period_num = :cv_period
617 AND glbal.currency_code = :cv_currency_code
618 AND glbal.actual_flag = '||''''||'A'||''''||'
619 AND glbal.code_combination_id = fscg.ccid
620 AND acct.sf133_line_id = :cv_sf133_line_id
621 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
622 AND fscg.sf133_line_acct_id = acct.sf133_line_acct_id';
623
624 -- Data Access Security:
625 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
626 das_where := gl_access_set_security_pkg.get_security_clause
627 (das_id, gl_access_set_security_pkg.READ_ONLY_ACCESS,
628 gl_access_set_security_pkg.CHECK_LEDGER_ID,
629 to_char(parm_set_of_books_id), null,
630 gl_access_set_security_pkg.CHECK_SEGVALS,
631 null, 'glcc', null);
632 IF (das_where IS NOT NULL) THEN
633 v_select := v_select || '
634 AND ' || das_where;
635 END IF;
636
637
638 /*
639 -- -------------------------------------
640 -- Store Flex Segment Names in Table
641 -- -------------------------------------
642 FOR flex_field_column_name_entry IN flex_field_column_name_cursor LOOP
643 EXIT WHEN flex_field_column_name_cursor%NOTFOUND;
644 c_segment_name := flex_field_column_name_entry.segment_name;
645 c_flex_column_name := flex_field_column_name_entry.flex_column_name;
646 --
647 -- t_segment_number := TO_NUMBER(SUBSTR(c_flex_column_name,08,02));
648 -- t_segment_name(t_segment_number) := c_flex_column_name;
649 --
650 BEGIN
651 SELECT flex_value_set_id
652 INTO g_seg_value_set_id
653 FROM fnd_id_flex_segments
654 WHERE application_column_name = c_flex_column_name
655 AND application_id = 101
656 AND id_flex_code = 'GL#'
657 AND id_flex_num = g_chart_of_accounts_id;
658 EXCEPTION
659 WHEN OTHERS THEN
660 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Error in getting the Value set attched ' ||
661 ' to the segemnt => ' || c_flex_column_name);
662 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' SQLCODE => ' || SQLCODE ||
663 ' SQLERRM => ' || SQLERRM);
664 RAISE;
665 END;
666 -- + Rollup for the amount is the segment is a parent segment +
667 v_select := v_select || '
668 AND ( NVL(glcc.'|| c_flex_column_name ||
669 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
670 ||',NVL(glcc.'||c_flex_column_name ||
671 ','||''''||'-1'||''''||')) ' || '
672 OR glcc.'||c_flex_column_name ||' IN (SELECT flex_value '||
673 'FROM fnd_flex_values ffv, fnd_flex_value_hierarchies ffvh '||
674 'WHERE ffv.flex_value BETWEEN ffvh.child_flex_value_low
675 AND ffvh.child_flex_value_high
676 AND ffv.flex_value_set_id = ' || g_seg_value_set_id ||
677 ' AND ffv.flex_value_set_id = ffvh.flex_value_set_id'||
678 ' AND parent_flex_value = acct.' || c_flex_column_name || '))';
679
680 -- + commented the below code to roll up the amount for all segments +
681 v_select := v_select || '
682 AND NVL(glcc.'|| c_flex_column_name ||
683 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
684 ||',NVL(glcc.'||c_flex_column_name ||
685 ','||''''||'-1'||''''||'))';
686
687 IF c_flex_column_name = v_balance_column_name THEN
688 -- the segment application_column_name being processed = the balancing
689 -- segment application_column_name.
690 g_fund_segment_name := c_flex_column_name;
691 END IF;
692 --
693 END LOOP;
694 */
695
696 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
697 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,V_SELECT);
698 END IF;
699 --
700 v_cursor_id := DBMS_SQL.OPEN_CURSOR();
701 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
702 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'T1');
703 END IF;
704
705 fnd_file.put_line (fnd_file.log, v_select);
706
707 dbms_sql.parse(v_cursor_id,v_select,dbms_sql.v7);
708
709 dbms_sql.bind_variable(v_cursor_id,':cv_set_of_books_id',parm_set_of_books_id);
710 -- dbms_sql.bind_variable(v_cursor_id,':cv_chart_of_accounts_id',g_chart_of_accounts_id);
711 dbms_sql.bind_variable(v_cursor_id,':cv_currency_code',g_currency_code);
712
713 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
714 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'T2');
715 END IF;
716 dbms_sql.define_column(v_cursor_id,1,c_total_balance);
717 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
718 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'T3');
719 END IF;
720
721
722 --
723 -- ------------------------------------
724 -- Exceptions
725 -- ------------------------------------
726 EXCEPTION
727 --
728 WHEN OTHERS THEN
729 g_error_code := SQLCODE;
730 g_error_message := 'determine_acct_flex_segments/'||SQLERRM;
731 IF flex_field_column_name_cursor%ISOPEN THEN
732 close flex_field_column_name_cursor;
733 END IF;
734 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
735 --
736 END determine_acct_flex_segments;
737 -- --------------------------------------------------------
738 -- --------------------------------------------------------
739 PROCEDURE purge_temp_table
740 --
741 IS
742 l_module_name VARCHAR2(200);
743 --
744 BEGIN
745 l_module_name := g_module_name || 'purge_temp_table';
746 --
747 IF parm_run_mode = 'T' THEN
748 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
749 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START PURGE_TEMP_TABLE');
750 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND SEGMENT ('||G_FUND_SEGMENT_NAME ||')');
751 END IF;
752 END IF;
753 --
754 DELETE
755 FROM fv_sf133_definitions_cols_temp
756 WHERE (sf133_line_id)
757 IN
758 (SELECT sf133_line_id
759 FROM fv_sf133_definitions_lines
760 WHERE set_of_books_id = parm_set_of_books_id);
761 --
762 COMMIT;
763 --
764 -- ------------------------------------
765 -- Exceptions
766 -- ------------------------------------
767 EXCEPTION
768 --
769 WHEN NO_DATA_FOUND THEN
770 NULL;
771 --
772 WHEN OTHERS THEN
773 g_error_code := SQLCODE;
774 g_error_message := SQLERRM;
775 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
776 --
777 END purge_temp_table;
778 -- --------------------------------------------------------
779 -- --------------------------------------------------------
780 PROCEDURE build_report_lines
781 --
782 AS
783 --
784 l_module_name VARCHAR2(200) ;
785 -- New Variables added by Surya on 04/07/98
786 -- l_year_counter Number ; -- FOR loop counter
787 -- l_process_year Number ; -- Process Year for Previous Years
788 -- L_BEG_PERIOD_PREV NUMBER ; -- Beginning Period-Previous Year
789 -- L_END_PERIOD_PREV NUMBER ; -- Ending period-previous year
790 -- L_LOOP_YEAR NUMBER;
791 -- l_federal_acct_symbol_id number(15);
792 l_sf133_ts_value fv_sf133_definitions_cols_temp.sf133_fund_value%TYPE;
793 l_line_cnt number;
794 -- ---------------------------------------------------------
795 BEGIN
796 l_module_name := g_module_name || 'build_report_lines';
797 --
798 IF parm_run_mode = 'T' THEN
799 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_REPORT_LINES');
801 END IF;
802 END IF;
803 --
804 -- ----------------------------------------------------------
805 -- Find period_number that is not an adjusting period
806 -- ----------------------------------------------------------
807 --
808 --
809 SELECT min(period_num)
810 INTO g_period_num
811 FROM gl_period_statuses
812 WHERE ledger_id = parm_set_of_books_id
813 AND adjustment_period_flag = 'N'
814 AND period_year = parm_gl_period_year
815 AND application_id = '101' ;
816
817
818 -- Added on 5/6/98 by Surya Padmanabhan to get the Period Number For
819 -- the Quarter.
820 SELECT PERIOD_NUM
821 INTO parm_gl_period_num
822 FROM GL_PERIOD_STATUSES
823 WHERE LEDGER_ID = parm_set_of_books_id AND
824 PERIOD_YEAR = parm_gl_period_year AND
825 APPLICATION_ID = '101' AND
826 CLOSING_STATUS in ('O','C') AND
827 PERIOD_NAME = parm_gl_period_name;
828 -- for bug 2642032
829 -- AND adjustment_period_flag = 'N' ;
830
831 -- ----------------------------------------------------
832 -- Get Next SF133 Treasury Symbol Line from Cursor
833 -- ----------------------------------------------------
834 --
835 g_ts_value_in_process := NULL;
836 --
837 FOR ts_report_line_entry IN ts_report_line_cursor LOOP
838 --
839 if v_debug then
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,'LG 3 INSIDE LOOP ') ;
842 END IF;
843 end if;
844
845 c_sf133_ts_value := ts_report_line_entry.sf133_ts_value;
846 c_sf133_line_id := ts_report_line_entry.sf133_line_id;
847 c_sf133_line_number := ts_report_line_entry.sf133_line_number;
848 c_sf133_line_type_code := ts_report_line_entry.sf133_line_type_code;
849 c_sf133_natural_bal_type
850 := ts_report_line_entry.sf133_natural_balance_type;
851 c_sf133_line_category := ts_report_line_entry.sf133_line_category;
852 c_sf133_report_line_number
853 := ts_report_line_entry.sf133_report_line_number;
854 c_sf133_treasury_symbol_id := ts_report_line_entry.sf133_treasury_symbol_id; --Bug 1575992
855
856 g_column_number := 1;
857
858 IF g_error_code = 0 THEN
859 IF (c_sf133_line_type_code = 'D' or c_sf133_line_type_code = 'D2') THEN
860 g_column_number := 1;
861
862 /*********** Modifications Start *****************/
863
864 -- Get the Beginning and Ending Periods
865 L_PROCESS_YEAR := parm_gl_period_year;
866 IF g_established_year = parm_gl_period_year THEN
867 L_BEG_PERIOD_PREV := g_period_num;
868 L_END_PERIOD_PREV := parm_gl_period_num;
869 ELSE
870 GET_BEGIN_ENDING_PERIODS
871 (L_PROCESS_YEAR, L_BEG_PERIOD_PREV, L_END_PERIOD_PREV) ;
872 END IF;
873
874 -- Get the amount for the First Column.(Passed Quarter)
875 build_fiscal_line_columns(L_BEG_PERIOD_PREV, L_END_PERIOD_PREV, L_PROCESS_YEAR) ;
876
877 -- Call Insert Procedure to insert the derived amount values
878 -- for the first column.
879 populate_temp_table;
880 -- Loop to Calculate amounts for next 5 years from established year
881 --LGOEL: Fix for bug 1470537 decrement the loop year
882
883 --L_LOOP_YEAR := g_established_year + 1;
884 L_LOOP_YEAR := g_established_year - 1;
885
886 l_sf133_ts_value := c_sf133_ts_value;
887
888 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
889 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FEDERAL ACCT SYMBOL ID IS'|| TO_CHAR(L_FEDERAL_ACCT_SYMBOL_ID)) ;
890 END IF;
891
892 For l_year_counter IN 1..5 Loop
893
894 -- Determine the Previous Year
895
896 -- replaced L_PROCESS_YEAR := PARM_GL_PERIOD_YEAR -
897 -- l_year_counter statement
898 -- with L_PROCESS_YEAR := L_LOOP_YEAR by Narsimha Balakkari ;
899
900 /*1584188 :pkpatel - Do not decrement the Process Year */
901
902 -- L_PROCESS_YEAR := L_LOOP_YEAR ;
903 L_PROCESS_YEAR := PARM_GL_PERIOD_YEAR ;
904
905 -- Get the Beginning and Ending Periods
906 /*GET_BEGIN_ENDING_PERIODS
907 (L_PROCESS_YEAR, L_BEG_PERIOD_PREV, L_END_PERIOD_PREV) ;*/
908
909 IF L_PROCESS_YEAR = parm_gl_period_year THEN
910 L_BEG_PERIOD_PREV := g_period_num;
911 L_END_PERIOD_PREV := parm_gl_period_num;
912 END IF;
913
914 --LGOEL: Fetch the Treasury symbol for previous year
915 -- added check for established fiscal year - 1584188
916 -- added time frame condition - 1633861
917 begin
918 select treasury_symbol,treasury_symbol_id
919 into c_sf133_ts_value,g_treasury_symbol_id
920 from fv_treasury_symbols
921 WHERE set_of_books_id = parm_set_of_books_id
922 and federal_acct_symbol_id = g_federal_acct_symbol_id
923 and established_fiscal_yr = l_loop_year
924 and time_frame = 'A';--CGAC
925
926 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
927 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TREASURY SYMBOL IS '||C_SF133_TS_VALUE );
928 END IF;
929
930 c_sf133_treasury_symbol_id := g_treasury_symbol_id;
931 -- dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',
932 -- g_treasury_symbol_id);
933 -- Derive the Amount Values for the Previous Year
934 build_fiscal_line_columns
935 (L_BEG_PERIOD_PREV, L_END_PERIOD_PREV, L_PROCESS_YEAR) ;
936
937 exception when no_data_found then
938 o_sf133_column_amount := 0;
939 o_sf133_amt_not_shown := 0;
940 end;
941
942 --LGOEL: Restore the treasury symbol variable value
943 c_sf133_ts_value := l_sf133_ts_value;
944
945
946 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
947 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESS YEAR - ' || TO_CHAR(L_PROCESS_YEAR) ||
948 'Beginning Period - ' || to_char(l_beg_period_prev) ||
949 'Ending Period - ' || to_char(l_end_period_prev)) ;
950 END IF;
951
952 -- Update the Current Row with derived values.
953
954 -- Since Decode cannot be used in the left side of the
955 -- assignment after SET phrase, a litle round about way
956 -- is used by using Decode on the right side. Still one
957 -- SQL statement !!
958
959 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
960 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LOOP COUNTER ' || TO_CHAR(L_YEAR_COUNTER) || ' AMOUNT VALUE ' || TO_CHAR(O_SF133_COLUMN_AMOUNT)) ;
961 END IF;
962 UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
963 SET
964 SF133_COLUMN_2_AMOUNT = DECODE(L_YEAR_COUNTER, 1,
965 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_2_AMOUNT),
966 SF133_COLUMN_3_AMOUNT = DECODE(L_YEAR_COUNTER, 2,
967 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_3_AMOUNT),
968 SF133_COLUMN_4_AMOUNT = DECODE(L_YEAR_COUNTER, 3,
969 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_4_AMOUNT),
970 SF133_COLUMN_5_AMOUNT = DECODE(L_YEAR_COUNTER, 4,
971 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_5_AMOUNT),
972 SF133_COLUMN_6_AMOUNT = DECODE(L_YEAR_COUNTER, 5,
973 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_6_AMOUNT),
974
975 SF133_AMT_2_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 1,
976 O_SF133_AMT_NOT_SHOWN, SF133_AMT_2_NOT_SHOWN),
977 SF133_AMT_3_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 2,
978 O_SF133_AMT_NOT_SHOWN, SF133_AMT_3_NOT_SHOWN),
979 SF133_AMT_4_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 3,
980 O_SF133_AMT_NOT_SHOWN, SF133_AMT_4_NOT_SHOWN),
981 SF133_AMT_5_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 4,
982 O_SF133_AMT_NOT_SHOWN, SF133_AMT_5_NOT_SHOWN),
983 SF133_AMT_6_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 5,
984 O_SF133_AMT_NOT_SHOWN, SF133_AMT_6_NOT_SHOWN)
985
986 WHERE
987 SF133_FUND_VALUE = L_SF133_TS_VALUE AND
988 SF133_LINE_ID = O_SF133_LINE_ID ;
989
990 L_LOOP_YEAR := L_LOOP_YEAR - 1;
991
992 End Loop ;
993
994 -- Update the Current Row with the total.
995 UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
996 SET
997 SF133_AMT_TOTAL_NOT_SHOWN =
998 SF133_AMOUNT_NOT_SHOWN + SF133_AMT_2_NOT_SHOWN +
999 SF133_AMT_3_NOT_SHOWN + SF133_AMT_4_NOT_SHOWN +
1000 SF133_AMT_5_NOT_SHOWN + SF133_AMT_6_NOT_SHOWN ,
1001
1002 SF133_COLUMN_TOTAL_AMT =
1003 SF133_COLUMN_AMOUNT + SF133_COLUMN_2_AMOUNT +
1004 SF133_COLUMN_3_AMOUNT + SF133_COLUMN_4_AMOUNT +
1005 SF133_COLUMN_5_AMOUNT + SF133_COLUMN_6_AMOUNT
1006
1007 WHERE
1008 SF133_FUND_VALUE = L_SF133_TS_VALUE AND
1009 SF133_LINE_ID = O_SF133_LINE_ID ;
1010
1011 ELSIF c_sf133_line_type_code = 'T' THEN
1012 SELECT count(*)
1013 INTO l_line_cnt
1014 FROM fv_sf133_rep_line_calc
1015 WHERE line_id = c_sf133_line_id;
1016 IF l_line_cnt = 0 THEN
1017 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1','Total line does not contain calculations. SEED Data not properly Loaded. Please Verify and reinvoke the Process.');
1018 RETURN;
1019 END IF;
1020 process_total_line;
1021
1022 END IF;
1023 --
1024 END IF;
1025 --
1026 END LOOP;
1027 --
1028 -- ------------------------------------
1029 -- Exceptions
1030 -- ------------------------------------
1031 EXCEPTION
1032 --
1033 WHEN OTHERS THEN
1034 g_error_code := SQLCODE;
1035 g_error_message := SQLERRM;
1036
1037 IF ts_report_line_cursor%ISOPEN THEN
1038 close ts_report_line_cursor;
1039 END IF;
1040 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1041 --
1042 END build_report_lines;
1043 -- --------------------------------------------------------
1044 -- ----------------------------------------------
1045 PROCEDURE build_fiscal_line_columns
1046 (c_begin_period Number, c_end_period Number, c_fiscal_year Number)
1047 --
1048 IS
1049 --
1050 l_module_name VARCHAR2(200) ;
1051 l_ignore INTEGER;
1052 query_fetch_bal VARCHAR2(8600);
1053 where_clause VARCHAR2(8600);
1054
1055 --CGAC
1056 financing_account_treas fv_treasury_symbols.financing_account%TYPE;
1057 availability_type_treas fv_treasury_symbols.availability_type_code%TYPE;
1058 fund_type_treas fv_treasury_symbols.fund_type%TYPE;
1059
1060 -- ----------------------------------------------
1061 BEGIN
1062 l_module_name := g_module_name || 'build_fiscal_line_columns';
1063 --
1064 IF parm_run_mode = 'T' THEN
1065
1066 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1067 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_FISCAL_LINE_COLUMNS');
1068 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- LINE('||C_SF133_LINE_NUMBER||')'
1069 || ' Tresury Symbol('||c_sf133_ts_value ||')'
1070 || ' '||to_char(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
1071 END IF;
1072 END IF;
1073 --
1074 -- ----------------------------------------
1075 -- Get Fund Accummulation
1076 -- ----------------------------------------
1077 c_total_balance := 0;
1078 c_sf133_amount_not_shown := 0;
1079 c_begin_balance := 0;
1080 c_ending_balance := 0;
1081
1082
1083 -- Removed the Following Statements, since the Beginning and Ending
1084 -- periods are passed as parameters.
1085 -- c_begin_period := g_period_num;
1086 -- c_end_period := parm_gl_period_num;
1087 --
1088 CSum_E :=0;
1089 DSum_E :=0;
1090 CSum_B :=0;
1091 CSum_B :=0;
1092
1093
1094 -- for the line find all accounts and sum
1095 FOR balance_type_rec in balance_type_cursor LOOP
1096 c_sf133_line_acct_id := balance_type_rec.sf133_line_acct_id;
1097 c_sf133_balance_type := balance_type_rec.sf133_balance_type;
1098 c_acct_number :=balance_type_rec.acct_number;
1099 c_direct_or_reimb_code := balance_type_rec.direct_or_reimb_code;
1100 c_apportionment_category_code := balance_type_rec.apportionment_category_code;
1101 c_category_b_code:= balance_type_rec.category_b_code;
1102 c_prc_code:= balance_type_rec. prc_code;
1103 c_advance_code:= balance_type_rec.advance_code;
1104 c_availability_time:= balance_type_rec.availability_time;
1105 c_bea_category_code:= balance_type_rec.bea_category_code;
1106 c_borrowing_source_code:= balance_type_rec.borrowing_source_code;
1107 c_transaction_partner:= balance_type_rec. transaction_partner;
1108 c_year_of_budget_authority:= balance_type_rec.year_of_budget_authority;
1109 c_prior_year_adjustment:= balance_type_rec.prior_year_adjustment;
1110 c_authority_type:= balance_type_rec.authority_type;
1111 c_tafs_status:= balance_type_rec.tafs_status;
1112 c_availability_type:= balance_type_rec. availability_type;
1113 c_expiration_flag:= balance_type_rec.expiration_flag;
1114 c_fund_type:= balance_type_rec.fund_type;
1115 c_financing_account_code:= balance_type_rec.financing_account_code;
1116
1117 -- New code added written by Narsimha Balakkari to solve the Rescission
1118 -- problem.
1119 c_rescission_flag := 'FALSE';
1120 IF upper(c_sf133_additional_info) = 'RESCISSION' THEN
1121
1122 select upper(resource_type) into c_resource_type
1123 from fv_treasury_symbols
1124 where treasury_symbol = parm_treasury_value_r1
1125 and set_of_books_id = parm_set_of_books_id;
1126
1127 IF c_resource_type like '%APPROPRIATION%' THEN
1128 IF ltrim(rtrim(c_sf133_report_line_number)) = '1A' THEN
1129 c_rescission_flag := 'TRUE';
1130 ELSE
1131 c_rescission_flag := 'FALSE';
1132 END IF;
1133 ELSIF c_resource_type like '%BORROWING%' THEN
1134 IF c_sf133_report_line_number = '1B' THEN
1135 c_rescission_flag := 'TRUE';
1136 ELSE
1137 c_rescission_flag := 'FALSE';
1138 END IF;
1139 ELSIF c_resource_type like '%CONTRACT%' THEN
1140 IF c_sf133_report_line_number = '1C' THEN
1141 c_rescission_flag := 'TRUE';
1142 ELSE
1143 c_rescission_flag := 'FALSE';
1144 END IF;
1145 END IF;
1146 ELSE
1147 c_rescission_flag := 'TRUE';
1148 END IF;
1149 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1150
1151 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LINE NUMBER IS ' || C_SF133_REPORT_LINE_NUMBER);
1152 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RESOURCE TYPE IS ' || C_RESOURCE_TYPE);
1153 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RESOURCE FLAG IS ' || C_RESCISSION_FLAG);
1154 END IF;
1155 IF c_rescission_flag = 'TRUE' THEN
1156
1157 SELECT start_date,
1158 end_date
1159 INTO beg_date,
1160 close_date
1161 FROM gl_period_statuses
1162 WHERE period_year = parm_gl_period_year
1163 AND period_num = parm_gl_period_num
1164 AND application_id = 101
1165 AND set_of_books_id = parm_set_of_books_id;
1166
1167 --CGAC
1168 SELECT availability_type_code, fund_type, expiration_Date
1169 INTO availability_type_treas, fund_type_treas, exp_date
1170 FROM fv_treasury_symbols
1171 WHERE treasury_symbol_id = c_sf133_treasury_symbol_id
1172 AND set_of_books_id = parm_set_of_books_id;
1173
1174
1175 -- Extract expiration date of treasury symbol and determine if the TS expired
1176 -- or will it expire in the year for which the process is run
1177 -- Bug9415373.
1178 IF(exp_date < close_date ) THEN
1179 whether_Exp := 'E';
1180 ELSE
1181 whether_Exp := 'U';
1182 END IF;
1183
1184 if (exp_date is null) then
1185 whether_Exp := 'U';
1186 whether_Exp_SameYear := 'N';
1187 end if;
1188
1189 IF (exp_date is not null) then
1190 select extract ( year from expiration_date)into expiring_year
1191 from fv_treasury_symbols where treasury_symbol_id=c_sf133_treasury_symbol_id;--g_treasury_symbol_id;
1192 if (expiring_year is not null and expiring_year = parm_gl_period_year) then
1193 whether_Exp_SameYear := 'Y';
1194 elsif ( expiring_year > parm_gl_period_year) then
1195 whether_Exp_SameYear := 'N';
1196 end if;
1197 end if;
1198
1199 --CGAC
1200 SELECT financing_account
1201 INTO financing_account_treas
1202 FROM fv_treasury_symbols
1203 WHERE treasury_symbol_id = c_sf133_treasury_symbol_id
1204 AND set_of_books_id = parm_set_of_books_id;
1205
1206 query_fetch_bal:=null;
1207
1208 where_clause := ' ';
1209 if (c_direct_or_reimb_code is not null) then
1210 where_clause:= where_clause||' '||' and trim(reimburseable_flag) = '''||c_direct_or_reimb_code|| ''' ';
1211 end if;
1212
1213 if (c_apportionment_category_code is not null) then
1214 where_clause:= where_clause||' '||' and trim(appor_cat_code) = '''||c_apportionment_category_code|| ''' ';
1215 end if;
1216
1217 IF (c_category_b_code IS NOT NULL) THEN
1218 where_clause:= where_clause||' '||' and trim(appor_cat_b_dtl) = '''||c_category_b_code|| ''' ';
1219 END IF;
1220
1221 IF (c_advance_code IS NOT NULL) THEN
1222 where_clause:= where_clause||' '||' and trim(advance_flag) = '''||c_advance_code|| ''' ';
1223
1224 END IF;
1225
1226 IF (c_availability_time IS NOT NULL) THEN
1227 where_clause:= where_clause||' '||' and trim(availability_flag) = '''||c_availability_time|| ''' ';
1228
1229 END IF;
1230
1231 IF (c_bea_category_code IS NOT NULL) THEN
1232 where_clause:= where_clause||' '||' and trim(bea_category) = '''||c_bea_category_code|| ''' ';
1233
1234 END IF;
1235
1236 IF (c_borrowing_source_code IS NOT NULL) THEN
1237 where_clause:= where_clause||' '||' and trim(borrowing_source) = '''||c_borrowing_source_code|| ''' ';
1238
1239 END IF;
1240
1241 IF (c_transaction_partner IS NOT NULL) THEN
1242 where_clause:= where_clause||' '||' and trim(fac.transaction_partner) = '''||c_transaction_partner|| ''' ';
1243
1244 END IF;
1245
1246 IF (c_year_of_budget_authority IS NOT NULL) THEN
1247 where_clause:= where_clause||' '||' and trim(year_budget_auth) = '''||c_year_of_budget_authority|| ''' ';
1248
1249 END IF;
1250
1251 IF (c_prior_year_adjustment IS NOT NULL) THEN
1252 where_clause:= where_clause||' '||' and trim(pya_flag) = '''||c_prior_year_adjustment|| ''' ';
1253
1254 END IF;
1255
1256 IF (c_prc_code IS NOT NULL) THEN
1257 where_clause:= where_clause||' '||' and trim(PROGRAM_RPT_CAT_NUM) = '''||c_prc_code|| ''' ';
1258
1259 END IF;
1260
1261 IF (c_authority_type IS NOT NULL) THEN
1262 where_clause:= where_clause||' '||' and trim(fac.authority_type) = '''||c_authority_type|| ''' ';
1263
1264 END IF;
1265
1266 if (c_tafs_status is not null) then
1267 where_clause:= where_clause||' '||'and trim(tafs_status) = '''||whether_Exp|| ''' ';
1268
1269 end if;
1270 if (c_availability_type is not null and c_availability_type ='X' ) then
1271 where_clause:= where_clause||' '||'and trim(availability_type) = '''||availability_type_treas||''' ';
1272
1273 end if;
1274
1275 if (c_fund_type is not null ) then
1276 where_clause:= where_clause||' '||'and trim(fund_type) = '''||fund_type_treas||''' ';
1277
1278 end if;
1279
1280 if (c_financing_account_code is not null ) then
1281 where_clause:= where_clause||' '||'and trim(financing_account_code) = '''||financing_account_treas||''' ';
1282
1283 end if;
1284
1285 if (c_expiration_flag is not null ) then
1286 where_clause:= where_clause||' '||'and expiration_flag = '''||whether_Exp_SameYear||''' ';
1287
1288 end if;
1289
1290 if( c_sf133_balance_type = 'B' OR c_sf133_balance_type = 'E') then
1291
1292 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D')
1293 or ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C'))then
1294 query_fetch_bal := 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
1295 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1296 AND acct.sf133_line_id = :cv_sf133_line_id
1297 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1298 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1299 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
1300 AND begin_end = '''||c_sf133_balance_type||'''';
1301
1302 elsif (( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1303 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D') ) then
1304 query_fetch_bal := 'select sum(nvl(amount,0)*(-1)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
1305 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1306 AND acct.sf133_line_id = :cv_sf133_line_id
1307 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1308 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1309 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
1310 AND begin_end = '''||c_sf133_balance_type||'''';
1311 end if;
1312
1313 if (query_fetch_bal is not null) then
1314
1315 v_cursor_id := dbms_sql.open_cursor;
1316 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1317 -- print query
1318 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1319
1320 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1321 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1322 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1323 --dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',g_treasury_symbol_id);
1324 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sf133_treasury_symbol_id);
1325 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1326
1327 --print bind variables
1328 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1329 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sf133_treasury_symbol_id);
1330 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1331 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1332
1333
1334 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1335
1336 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1337 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1338 END IF;
1339
1340 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1341 dbms_sql.close_cursor(v_cursor_id);
1342 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1343 end if;
1344
1345 -- End the code for bal type beginning and ending
1346
1347 elsif c_sf133_balance_type = 'E-B' then -- balance type is end-begin
1348
1349 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1350 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C') ) then
1351
1352 query_fetch_bal := ' select
1353 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )
1354 from fv_facts_temp fac, fv_sf133_definitions_accts acct
1355 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1356 AND acct.sf133_line_id = :cv_sf133_line_id
1357 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1358 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1359 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL'' ';
1360
1361 v_cursor_id := dbms_sql.open_cursor;
1362
1363 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1364
1365 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1366 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1367 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1368 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sf133_treasury_symbol_id);
1369 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1370 -- print query
1371 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1372
1373 --print bind variables
1374 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1375 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sf133_treasury_symbol_id);
1376 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1377 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1378
1379 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1380
1381 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1382 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1383 END IF;
1384
1385 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1386 dbms_sql.close_cursor(v_cursor_id);
1387 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1388
1389 elsif (( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1390 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D'))then
1391
1392 query_fetch_bal := ' select
1393 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )*(-1)
1394 from fv_facts_temp fac, fv_sf133_definitions_accts acct
1395 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1396 AND acct.sf133_line_id = :cv_sf133_line_id
1397 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1398 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1399 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL'' ';
1400
1401 v_cursor_id := dbms_sql.open_cursor;
1402 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1403
1404 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1405 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1406 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1407 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sf133_treasury_symbol_id);
1408 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1409 -- print query
1410 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1411
1412 --print bind variables
1413 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1414 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sf133_treasury_symbol_id);
1415 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1416 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1417
1418 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1419
1420 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1421 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1422 END IF;
1423
1424 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1425 dbms_sql.close_cursor(v_cursor_id);
1426 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1427 END IF;
1428
1429 elsif (( c_sf133_balance_type= 'ED') or( c_sf133_balance_type= 'EC')) then -- bal type is ending debit or ending credit only
1430
1431 query_fetch_bal := 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
1432 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1433 AND acct.sf133_line_id = :cv_sf133_line_id
1434 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1435 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1436 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
1437 AND begin_end = ''E''';
1438
1439 if (query_fetch_bal is not null) then
1440 v_cursor_id := dbms_sql.open_cursor;
1441 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1442
1443 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1444 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1445 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1446 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sf133_treasury_symbol_id);
1447 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1448
1449 -- print query
1450 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1451 --print bind variables
1452 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1453 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'parm_tsymbol_id:'||c_sf133_treasury_symbol_id);
1454 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1455 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1456
1457 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1458
1459 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1460 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1461 END IF;
1462
1463 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1464 dbms_sql.close_cursor(v_cursor_id);
1465 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1466
1467 if ( c_sf133_balance_type = 'ED')then
1468 if (c_total_balance < 0) then
1469 c_total_balance := 0;
1470 end if;
1471 elsif ( c_sf133_balance_type = 'EC')then
1472 if (c_total_balance > 0) then
1473 c_total_balance := 0;
1474 end if;
1475 end if;
1476 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1477 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C'))then
1478 c_total_balance := c_total_balance;
1479 end if;
1480
1481 if (( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1482 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D')) then
1483 c_total_balance := c_total_balance*(-1);
1484 end if;
1485
1486 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance after modification:'||c_total_balance);
1487 end if;
1488
1489 elsif( (c_sf133_balance_type= 'E-BD') or (c_sf133_balance_type='E-BC')) then -- bal type is end begin debit only
1490
1491 query_fetch_bal := ' select
1492 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )
1493 from fv_facts_temp fac, fv_sf133_definitions_accts acct
1494 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1495 AND acct.sf133_line_id = :cv_sf133_line_id
1496 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1497 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1498 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL'' ';
1499
1500 v_cursor_id := dbms_sql.open_cursor;
1501
1502 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1503
1504 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1505 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1506 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1507 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sf133_treasury_symbol_id);
1508 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1509 -- print query
1510 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1511
1512 --print bind variables
1513 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1514 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sf133_treasury_symbol_id);
1515 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1516 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1517
1518 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1519
1520 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1522 END IF;
1523
1524 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1525 dbms_sql.close_cursor(v_cursor_id);
1526 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1527
1528 if (c_sf133_balance_type= 'E-BD') then
1529 if (c_total_balance > 0) then
1530 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1531 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C') ) then
1532 c_total_balance :=c_total_balance;
1533 end if;
1534
1535 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1536 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D') ) then
1537 c_total_balance :=c_total_balance*-1;
1538 end if;
1539 else
1540 c_total_balance :=0; -- consider the balance only if E-B is positive
1541 end if;
1542 end if; -- end for if (c_sf133_balance_type= 'E-BD') then
1543
1544
1545 if (c_sf133_balance_type= 'E-BC') then
1546 if (c_total_balance < 0) then
1547 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1548 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C') ) then
1549 c_total_balance :=c_total_balance;
1550 end if;
1551
1552 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1553 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D') ) then
1554 c_total_balance :=c_total_balance*-1;
1555 end if;
1556 else
1557 c_total_balance :=0; -- consider the balance only if E-B is negative
1558 end if;
1559 end if;
1560
1561 END IF; -- end checking for balance types
1562 end if; -- end for if rescission condition
1563
1564 -- sum the line amount
1565 if (c_total_balance is null) then
1566 c_total_balance :=0;
1567 end if;
1568 -- sum the line amount
1569 c_sf133_amount_not_shown := c_sf133_amount_not_shown + c_total_balance;
1570 -- fv_utility.debug_mesg('amt not shown = '||c_sf133_amount_not_shown);
1571
1572 END LOOP;
1573 --
1574 -- set up correct display sign
1575 --
1576 -- fv_utility.debug_mesg('natural bal type = '||c_sf133_natural_bal_type);
1577
1578 --
1579 o_sf133_ts_value := c_sf133_ts_value;
1580 o_sf133_line_id := c_sf133_line_id;
1581 o_sf133_column_number := g_column_number;
1582 o_sf133_column_amount := c_sf133_amount_not_shown;
1583 o_sf133_amt_not_shown := c_sf133_amount_not_shown;
1584 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --Bug 1575992
1585
1586 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1587 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COL AMT ='||O_SF133_COLUMN_AMOUNT);
1588 END IF;
1589 --
1590 -- ------------------------------------
1591 -- Exceptions
1592 -- ------------------------------------
1593 EXCEPTION
1594 --
1595 --
1596 WHEN OTHERS THEN
1597
1598 g_error_code := SQLCODE;
1599 g_error_message := SQLERRM;
1600
1601 IF balance_type_cursor%ISOPEN THEN
1602 close balance_type_cursor;
1603 /* ELSIF balance_cursor%ISOPEN THEN
1604 close balance_cursor;*/
1605 END IF;
1606
1607 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1608 --
1609 END build_fiscal_line_columns;
1610 -- ----------------------------------------------
1611 -- ----------------------------------------------
1612 PROCEDURE build_total_line_columns
1613 --
1614 IS
1615 l_module_name VARCHAR2(200) ;
1616 -- Variables added by Surya to accomodate Previous Year Column totals
1617
1618 c_sf133_amt2_not_shown Number ;
1619 c_sf133_amt3_not_shown Number ;
1620 c_sf133_amt4_not_shown Number ;
1621 c_sf133_amt5_not_shown Number ;
1622 c_sf133_amt6_not_shown Number ;
1623 c_sf133_amt_total_not_shown Number ;
1624
1625
1626 c_sf133_column_amount2 Number ;
1627 c_sf133_column_amount3 Number ;
1628 c_sf133_column_amount4 Number ;
1629 c_sf133_column_amount5 Number ;
1630 c_sf133_column_amount6 Number ;
1631 c_sf133_column_amount_total Number ;
1632
1633 --
1634 -- ----------------------------------------------
1635 BEGIN
1636 l_module_name := g_module_name || 'build_total_line_columns';
1637 --
1638 IF parm_run_mode = 'T' THEN
1639 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1640 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_TOTAL_LINE_COLUMNS');
1641 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- LINE='||C_SF133_LINE_NUMBER
1642 ||' Start Total Line('||g_total_start_line_number||')'
1643 || ' Treasury Symbol('||c_sf133_ts_value||')');
1644 END IF;
1645 END IF;
1646 --
1647 -- ----------------------------------------
1648 -- Get Treasury Symbol Accummulation for Total using column with true sign.
1649 -- ----------------------------------------
1650 -- Modified By Surya to get the total of Past Year Columns
1651
1652 SELECT NVL(SUM(NVL(sf133_amount_not_shown,0)),0),
1653 NVL(SUM(NVL(sf133_amt_2_not_shown,0)),0),
1654 NVL(SUM(NVL(sf133_amt_3_not_shown,0)),0),
1655 NVL(SUM(NVL(sf133_amt_4_not_shown,0)),0),
1656 NVL(SUM(NVL(sf133_amt_5_not_shown,0)),0),
1657 NVL(SUM(NVL(sf133_amt_6_not_shown,0)),0),
1658 NVL(SUM(NVL(sf133_amt_total_not_shown,0)),0)
1659
1660 INTO c_sf133_amount_not_shown,
1661 c_sf133_amt2_not_shown ,
1662 c_sf133_amt3_not_shown ,
1663 c_sf133_amt4_not_shown ,
1664 c_sf133_amt5_not_shown ,
1665 c_sf133_amt6_not_shown ,
1666 c_sf133_amt_total_not_shown
1667
1668 FROM fv_sf133_definitions_cols_temp
1669 WHERE sf133_column_number = g_column_number
1670 AND sf133_fund_value = c_sf133_ts_value
1671 AND (sf133_line_id)
1672 IN
1673 (SELECT sf133_line_id
1674 FROM fv_sf133_definitions_lines
1675 WHERE set_of_books_id = parm_set_of_books_id
1676 AND sf133_line_number >
1677 DECODE(c_sf133_line_type_code, 'T', g_total_start_line_number, g_subtotal_start_line_number)
1678 AND sf133_line_number < c_sf133_line_number);
1679
1680
1681 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1682 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NATURAL BAL TYPE = '||C_SF133_NATURAL_BAL_TYPE);
1683 END IF;
1684 /* IF c_sf133_natural_bal_type = 'C' THEN
1685
1686 -- Credit, so display opposite
1687 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
1688 c_sf133_column_amount2 := c_sf133_amt2_not_shown * -1;
1689 c_sf133_column_amount3 := c_sf133_amt3_not_shown * -1;
1690 c_sf133_column_amount4 := c_sf133_amt4_not_shown * -1;
1691 c_sf133_column_amount5 := c_sf133_amt5_not_shown * -1;
1692 c_sf133_column_amount6 := c_sf133_amt6_not_shown * -1;
1693 c_sf133_column_amount_total := c_sf133_amt_total_not_shown * -1;
1694
1695 ELSIF c_sf133_natural_bal_type = 'D' THEN
1696
1697 -- Debit so display as is
1698 c_sf133_column_amount := c_sf133_amount_not_shown;
1699 c_sf133_column_amount2 := c_sf133_amt2_not_shown ;
1700 c_sf133_column_amount3 := c_sf133_amt3_not_shown ;
1701 c_sf133_column_amount4 := c_sf133_amt4_not_shown ;
1702 c_sf133_column_amount5 := c_sf133_amt5_not_shown ;
1703 c_sf133_column_amount6 := c_sf133_amt6_not_shown ;
1704 c_sf133_column_amount_total := c_sf133_amt_total_not_shown ;
1705
1706 ELSIF c_sf133_natural_bal_type = 'A' THEN
1707
1708 -- Display the absolute value
1709 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
1710 c_sf133_column_amount2 := ABS(c_sf133_amt2_not_shown) ;
1711 c_sf133_column_amount3 := ABS(c_sf133_amt3_not_shown) ;
1712 c_sf133_column_amount4 := ABS(c_sf133_amt4_not_shown) ;
1713 c_sf133_column_amount5 := ABS(c_sf133_amt5_not_shown) ;
1714 c_sf133_column_amount6 := ABS(c_sf133_amt6_not_shown) ;
1715 c_sf133_column_amount_total := ABS(c_sf133_amt_total_not_shown) ;
1716
1717 ELSIF c_sf133_natural_bal_type = 'N' THEN
1718
1719 -- Display as negative
1720 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
1721 c_sf133_column_amount2 := '-'||ABS(c_sf133_amt2_not_shown) ;
1722 c_sf133_column_amount3 := '-'||ABS(c_sf133_amt3_not_shown) ;
1723 c_sf133_column_amount4 := '-'||ABS(c_sf133_amt4_not_shown) ;
1724 c_sf133_column_amount5 := '-'||ABS(c_sf133_amt5_not_shown) ;
1725 c_sf133_column_amount6 := '-'||ABS(c_sf133_amt6_not_shown) ;
1726 c_sf133_column_amount_total :=
1727 '-'||ABS(c_sf133_amt_total_not_shown) ;
1728
1729 END IF;*/
1730
1731 -- NOTE ----
1732 -- No Specific Output variables starting with 'O' are used for inserting
1733 -- data. Original variables are used instead.
1734 -- (Refer 'populate_temp_table' Procedure for Output variables)
1735
1736 -- Column amount and Column not shown has the same value in the table
1737
1738 -- ------------------------------------
1739 -- Insert the Values into Report
1740 -- ------------------------------------
1741 INSERT
1742 INTO fv_sf133_definitions_cols_temp
1743 ( sf133_fund_value,
1744 treasury_symbol_id,--Bug 1575992
1745 sf133_line_id,
1746 sf133_column_number,
1747 sf133_column_amount,
1748 sf133_amount_not_shown,
1749 SF133_COLUMN_2_AMOUNT ,
1750 SF133_AMT_2_NOT_SHOWN ,
1751 SF133_COLUMN_3_AMOUNT ,
1752 SF133_AMT_3_NOT_SHOWN ,
1753 SF133_COLUMN_4_AMOUNT ,
1754 SF133_AMT_4_NOT_SHOWN ,
1755 SF133_COLUMN_5_AMOUNT ,
1756 SF133_AMT_5_NOT_SHOWN ,
1757 SF133_COLUMN_6_AMOUNT ,
1758 SF133_AMT_6_NOT_SHOWN ,
1759 SF133_COLUMN_TOTAL_AMT,
1760 SF133_AMT_TOTAL_NOT_SHOWN )
1761
1762 VALUES(
1763 c_sf133_ts_value,
1764 c_sf133_treasury_symbol_id, --Bug 1575992
1765 c_sf133_line_id,
1766 g_column_number,
1767 c_sf133_amount_not_shown,
1768 c_sf133_amount_not_shown,
1769 c_sf133_amt2_not_shown,
1770 c_sf133_amt2_not_shown,
1771 c_sf133_amt3_not_shown,
1772 c_sf133_amt3_not_shown,
1773 c_sf133_amt4_not_shown,
1774 c_sf133_amt4_not_shown,
1775 c_sf133_amt5_not_shown,
1776 c_sf133_amt5_not_shown,
1777 c_sf133_amt6_not_shown,
1778 c_sf133_amt6_not_shown,
1779 c_sf133_amt_total_not_shown,
1780 c_sf133_amt_total_not_shown);
1781
1782 --
1783 g_insert_count := g_insert_count + 1;
1784 --
1785
1786 -- ------------------------------------
1787 -- Exceptions
1788 -- ------------------------------------
1789 EXCEPTION
1790 --
1791 WHEN OTHERS THEN
1792 g_error_code := SQLCODE;
1793 g_error_message := SQLERRM;
1794 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1795 --
1796 END build_total_line_columns;
1797 -- ----------------------------------------------
1798 -- --------------------------------------------------------
1799 PROCEDURE populate_temp_table
1800 --
1801 IS
1802 --
1803 l_module_name VARCHAR2(200);
1804 -- ----------------------------------------------
1805 BEGIN
1806 l_module_name := g_module_name || 'populate_temp_table';
1807 --
1808 IF parm_run_mode = 'T' THEN
1809 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1810 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START POPULATE_TEMP_TABLE');
1811 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- '||C_SF133_LINE_NUMBER
1812 ||' ('||o_sf133_column_number||')'
1813 ||' ('||o_sf133_column_amount||')'
1814 ||' ('||o_sf133_amt_not_shown||')');
1815 END IF;
1816 END IF;
1817
1818 -- ------------------------------------
1819 -- Insert into Line Column Table
1820 -- ------------------------------------
1821 INSERT
1822 INTO fv_sf133_definitions_cols_temp
1823 (sf133_fund_value,
1824 treasury_symbol_id, --Bug 1575992
1825 sf133_line_id,
1826 sf133_column_number,
1827 sf133_column_amount,
1828 sf133_amount_not_shown,
1829 sf133_column_2_amount,
1830 sf133_column_3_amount,
1831 sf133_column_4_amount,
1832 sf133_column_5_amount,
1833 sf133_column_6_amount
1834 )
1835 VALUES(o_sf133_ts_value,
1836 o_sf133_treasury_symbol_id, --Bug 1575992
1837 o_sf133_line_id,
1838 o_sf133_column_number,
1839 o_sf133_amt_not_shown,
1840 o_sf133_amt_not_shown,
1841 c_sf133_column_amount2,
1842 c_sf133_column_amount3,
1843 c_sf133_column_amount4,
1844 c_sf133_column_amount5,
1845 c_sf133_column_amount6
1846 );
1847 --
1848 g_insert_count := g_insert_count + 1;
1849 --
1850 -- ------------------------------------
1851 -- Exceptions
1852 -- ------------------------------------
1853 EXCEPTION
1854 --
1855 WHEN OTHERS THEN
1856 g_error_code := SQLCODE;
1857 g_error_message := SQLERRM;
1858
1859 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1860 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'-- POPULATE_TEMP_TABLE');
1861 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'---- TREASURY SYMBOL:'||O_SF133_TS_VALUE
1862 ||' Line Id:'||o_sf133_line_id
1863 ||' Col:' ||o_sf133_column_number
1864 ||' Amt:' ||o_sf133_column_amount);
1865 --
1866 END populate_temp_table;
1867 -- --------------------------------------------------------
1868
1869
1870 -- --------------------------------------------------------
1871
1872 PROCEDURE GET_BEGIN_ENDING_PERIODS( V_PROCESS_YEAR NUMBER,
1873 V_BEGIN_PERIOD IN OUT NOCOPY NUMBER,
1874 V_END_PERIOD IN OUT NOCOPY NUMBER )
1875 IS
1876 l_module_name VARCHAR2(200);
1877 BEGIN
1878 l_module_name := g_module_name || 'GET_BEGIN_ENDING_PERIODS';
1879
1880 SELECT MIN(PERIOD_NUM)
1881 INTO V_BEGIN_PERIOD
1882 FROM gl_period_statuses
1883 WHERE set_of_books_id = parm_set_of_books_id
1884 AND period_year = V_PROCESS_YEAR
1885 AND adjustment_period_flag = 'N'
1886 AND application_id = '101' ;
1887
1888 SELECT MAX(PERIOD_NUM)
1889 INTO V_END_PERIOD
1890 FROM gl_period_statuses
1891 WHERE set_of_books_id = parm_set_of_books_id
1892 AND period_year = V_PROCESS_YEAR
1893 AND closing_status in ('C','O')
1894 AND application_id = '101' ;
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 g_error_code := SQLCODE;
1898 g_error_message := SQLERRM;
1899 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1900 RAISE ;
1901
1902 END GET_BEGIN_ENDING_PERIODS ;
1903
1904 PROCEDURE populate_gtt_with_ccid
1905 (
1906 p_treasury_symbol_id NUMBER
1907 )
1908 IS
1909 l_module_name VARCHAR2(200);
1910
1911 TYPE t_seg_str_table IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
1912 TYPE t_seg_name_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1913
1914 v_seg t_seg_name_table;
1915 v_seg_str t_seg_str_table;
1916 v_statement VARCHAR2(25000);
1917 v_insert_statement VARCHAR2(30000);
1918
1919 CURSOR crec_cursor
1920 (
1921 p_sobid NUMBER
1922 ) IS
1923 SELECT fsda.sf133_line_acct_id,
1924 fsda.sf133_line_id,
1925 fsdl.sf133_fund_category,
1926 fsda.segment1,
1927 fsda.segment2,
1928 fsda.segment3,
1929 fsda.segment4,
1930 fsda.segment5,
1931 fsda.segment6,
1932 fsda.segment7,
1933 fsda.segment8,
1934 fsda.segment9,
1935 fsda.segment10,
1936 fsda.segment11,
1937 fsda.segment12,
1938 fsda.segment13,
1939 fsda.segment14,
1940 fsda.segment15,
1941 fsda.segment16,
1942 fsda.segment17,
1943 fsda.segment18,
1944 fsda.segment19,
1945 fsda.segment20,
1946 fsda.segment21,
1947 fsda.segment22,
1948 fsda.segment23,
1949 fsda.segment24,
1950 fsda.segment25,
1951 fsda.segment26,
1952 fsda.segment27,
1953 fsda.segment28,
1954 fsda.segment29,
1955 fsda.segment30
1956 FROM fv_sf133_definitions_accts fsda,
1957 fv_sf133_definitions_lines fsdl
1958 WHERE fsdl.sf133_line_id = fsda.sf133_line_id
1959 AND fsdl.set_of_books_id=p_sobid
1960 ORDER BY 2,1;
1961
1962 CURSOR flex_cursor
1963 (
1964 p_chart_of_accounts_id NUMBER
1965 )
1966 IS
1967 SELECT application_column_name ,
1968 flex_value_set_id
1969 FROM fnd_id_flex_segments
1970 WHERE id_flex_code = 'GL#'
1971 AND id_flex_num = p_chart_of_accounts_id;
1972
1973 CURSOR child_value_cursor
1974 (
1975 p_seg VARCHAR2,
1976 p_sid NUMBER
1977 ) IS
1978 SELECT child_flex_value_low,
1979 child_flex_value_high
1980 FROM fnd_flex_value_hierarchies
1981 WHERE parent_FLEX_value = p_seg
1982 AND flex_value_set_id = p_sid;
1983
1984 child_rec child_value_cursor%ROWTYPE;
1985
1986 l_and VARCHAR2(5);
1987 l_child VARCHAR2(32000);
1988 l_no_of_child NUMBER;
1989 l_no_of_seg NUMBER;
1990 l_segno NUMBER;
1991 l_cnt NUMBER;
1992
1993 BEGIN
1994 l_module_name := g_module_name || 'populate_gtt_with_ccid';
1995
1996 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1997 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Entering Module '||l_module_name);
1998 END IF;
1999
2000 FOR crec_rec IN crec_cursor (parm_set_of_books_id) LOOP
2001
2002 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2003 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'sf133_line_acct_id = '||crec_rec.sf133_line_acct_id);
2004 END IF;
2005
2006 v_seg(1) := crec_rec.segment1;
2007 v_seg(2) := crec_rec.segment2;
2008 v_seg(3) := crec_rec.segment3;
2009 v_seg(4) := crec_rec.segment4;
2010 v_seg(5) := crec_rec.segment5;
2011 v_seg(6) := crec_rec.segment6;
2012 v_seg(7) := crec_rec.segment7;
2013 v_seg(8) := crec_rec.segment8;
2014 v_seg(9) := crec_rec.segment9;
2015 v_seg(10) := crec_rec.segment10;
2016 v_seg(11) := crec_rec.segment11;
2017 v_seg(12) := crec_rec.segment12;
2018 v_seg(13) := crec_rec.segment13;
2019 v_seg(14) := crec_rec.segment14;
2020 v_seg(15) := crec_rec.segment15;
2021 v_seg(16) := crec_rec.segment16;
2022 v_seg(17) := crec_rec.segment17;
2023 v_seg(18) := crec_rec.segment18;
2024 v_seg(19) := crec_rec.segment19;
2025 v_seg(20) := crec_rec.segment20;
2026 v_seg(21) := crec_rec.segment21;
2027 v_seg(22) := crec_rec.segment22;
2028 v_seg(23) := crec_rec.segment23;
2029 v_seg(24) := crec_rec.segment24;
2030 v_seg(25) := crec_rec.segment25;
2031 v_seg(26) := crec_rec.segment26;
2032 v_seg(27) := crec_rec.segment27;
2033 v_seg(28) := crec_rec.segment28;
2034 v_seg(29) := crec_rec.segment29;
2035 v_seg(30) := crec_rec.segment30;
2036
2037 v_statement := NULL;
2038
2039 FOR i IN 1 ..30 LOOP
2040 v_seg_str(i) := NULL;
2041 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2042 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_seg('||i||')='||v_seg(i));
2043 END IF;
2044 END LOOP;
2045
2046 l_no_of_seg := 0;
2047
2048 FOR flex_rec IN flex_cursor (g_chart_of_accounts_id) LOOP
2049 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2050 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'application_column_name = '||flex_rec.application_column_name);
2051 END IF;
2052 l_no_of_child := 0;
2053 l_and := NULL;
2054
2055 /* check the segment values is parent */
2056 l_segno := SUBSTR(flex_rec.application_column_name,8,2);
2057 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2058 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_segno = '||l_segno);
2059 END IF;
2060
2061 IF (v_seg(l_segno) IS NOT NULL) THEN
2062 SELECT COUNT(*)
2063 INTO l_cnt
2064 FROM fnd_flex_value_hierarchies
2065 WHERE parent_flex_value = v_seg(l_segno)
2066 AND flex_value_set_id = flex_rec.flex_value_set_id;
2067
2068 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2069 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt = '||l_cnt);
2070 END IF;
2071
2072 OPEN child_value_cursor(v_seg(l_segno) , flex_rec.flex_value_set_id);
2073
2074 IF (l_cnt > 0) THEN
2075 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2076 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt > 0');
2077 END IF;
2078
2079 l_and := NULL;
2080
2081 IF (l_no_of_seg > 0) THEN
2082 l_and := ' AND ';
2083 END IF;
2084
2085 l_child := l_and || ' ( ';
2086
2087 LOOP
2088 FETCH child_value_cursor INTO child_rec;
2089 EXIT WHEN child_value_cursor%NOTFOUND ;
2090
2091 IF (l_no_of_child > 0) THEN
2092 l_child := l_child || ' OR ';
2093 END IF;
2094
2095 l_child := l_child ||
2096 flex_rec.application_column_name ||
2097 ' between '||
2098 '''' ||
2099 child_rec.child_flex_value_low ||
2100 ''' and ''' ||
2101 child_rec.child_flex_value_high ||
2102 '''' ||
2103 fnd_global.local_chr(10);
2104 l_no_of_child := l_no_of_child + 1;
2105 END LOOP;
2106
2107 l_child := l_child || ' )' ;
2108 l_and := NULL;
2109 v_statement := v_statement || l_and || l_child || fnd_global.local_chr(10);
2110
2111 ELSE
2112 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2113 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt not > 0');
2114 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_seg='||l_no_of_seg);
2115 END IF;
2116 IF (l_no_of_seg > 0) THEN
2117 l_and := ' AND ';
2118 END IF;
2119 v_statement := v_statement || l_and ||
2120 flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
2121 END IF; --cnt > 0
2122
2123
2124 CLOSE child_value_cursor;
2125 l_no_of_seg := l_no_of_seg + 1;
2126
2127 END IF; --v_seg(l_segno) IS NOT NULL
2128
2129 END LOOP; --FLEX_CURSOR
2130
2131 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2132 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_statement = '||v_statement);
2133 END IF;
2134
2135 IF (v_statement IS NOT NULL) THEN
2136 v_insert_statement := 'INSERT INTO fv_sf133_ccids_gt
2137 (
2138 sf133_line_acct_id,
2139 ccid
2140 )
2141 SELECT :b_sf133_line_acct_id,
2142 gcc.code_combination_id
2143 FROM gl_code_combinations gcc,
2144 fv_fund_parameters FFP
2145 WHERE gcc.' || v_balance_column_name ||' = ffp.fund_value
2146 AND ffp.treasury_symbol_id = :b_treasury_symbol_id
2147 AND ffp.set_of_books_id = :b_set_of_books_id
2148 AND fund_category like nvl(:b_sf133_line_category, ' || '''' ||'%' || ''''||')
2149 AND '|| v_statement || '
2150 AND gcc.template_id is null
2151 AND gcc.chart_of_accounts_id = :b_chart_of_accounts_id
2152 AND NOT EXISTS (SELECT 1
2153 FROM fv_sf133_ccids_gt fct
2154 WHERE fct.sf133_line_acct_id = :b_sf133_line_acct_id
2155 AND fct.ccid = gcc.code_combination_id)';
2156
2157
2158 EXECUTE IMMEDIATE v_insert_statement
2159 USING crec_rec.sf133_line_acct_id,
2160 p_treasury_symbol_id,
2161 parm_set_of_books_id,
2162 crec_rec.sf133_fund_category,
2163 g_chart_of_accounts_id,
2164 crec_rec.sf133_line_acct_id;
2165 END IF;
2166 END LOOP; --crec_cursor
2167
2168 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2169 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Exiting Module = '||l_module_name);
2170 END IF;
2171
2172 EXCEPTION
2173 WHEN OTHERS THEN
2174 g_error_code := SQLCODE;
2175 g_error_message := SQLERRM;
2176 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception',g_error_message);
2177 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception','-- populate_gtt_with_ccid');
2178 END;
2179 /*Procedure to fetch balance type for accounts which contain ending and/or begining balances
2180 of type either credit or debit or both*/
2181 PROCEDURE GET_BAL_TYPE
2182 IS
2183 query_Ending_Indicator VARCHAR2(8600);
2184 query_Beg_Indicator VARCHAR2(8600);
2185 l_ignore1 INTEGER;
2186 BEGIN
2187 -- get the bal indicator of all E records if there are records of multiple bal types
2188 query_Ending_Indicator := 'select sum(decode(facE.debit_credit,''D'',amount)),
2189 sum(decode(facE.debit_credit,''C'',amount)) from
2190 fv_facts_temp facE, fv_sf133_definitions_accts acct
2191 where facE.treasury_symbol_id = '||g_treasury_symbol_id||
2192 'AND acct.sf133_line_id = '||c_sf133_line_id||
2193 'AND acct.sf133_line_acct_id = '||c_sf133_line_acct_id||
2194 'and acct_number like facE.sgl_acct_number||''%'' and facE.sgl_acct_number is not null
2195 and facE.begin_end=''E''' ;
2196
2197 v_cursor_id_ind := dbms_sql.open_cursor;
2198 dbms_sql.parse(v_cursor_id_ind, query_Ending_Indicator, dbms_sql.v7);
2199 dbms_sql.define_column(v_cursor_id_ind, 1,DSum_E);
2200 dbms_sql.define_column(v_cursor_id_ind, 2,CSum_E);
2201
2202 l_ignore1 := dbms_sql.execute_and_fetch(v_cursor_id_ind);
2203 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2204 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133','l_ignore1 := '||l_ignore1);
2205 END IF;
2206 dbms_sql.column_value(v_cursor_id_ind, 1, DSum_E);
2207 dbms_sql.column_value(v_cursor_id_ind, 2, CSum_E);
2208 --dbms_sql.close_cursor(v_cursor_id_ind);
2209
2210 if DSum_E >= CSum_E then
2211 e_bal_indicator:='D';
2212 else
2213 e_bal_indicator:='C';
2214 end if;
2215
2216 -- get the bal indicator of all E records if there are records of multiple bal types
2217 query_Beg_Indicator:= 'select sum(decode(facB.debit_credit,''D'',amount)) ,
2218 sum(decode(facB.debit_credit,''C'',amount)) from
2219 fv_facts_temp facB, fv_sf133_definitions_accts acct
2220 where facB.treasury_symbol_id = '||g_treasury_symbol_id||
2221 'AND acct.sf133_line_id = '||c_sf133_line_id||
2222 'AND acct.sf133_line_acct_id = '||c_sf133_line_acct_id||
2223 'and acct_number like facB.sgl_acct_number||''%'' and facB.sgl_acct_number is not null
2224 and facB.begin_end=''B''' ;
2225
2226 v_cursor_id_ind := dbms_sql.open_cursor;
2227 dbms_sql.parse(v_cursor_id_ind, query_Beg_Indicator, dbms_sql.v7);
2228 dbms_sql.define_column(v_cursor_id_ind, 1,DSum_B);
2229 dbms_sql.define_column(v_cursor_id_ind, 2,CSum_B);
2230
2231 l_ignore1 := dbms_sql.execute_and_fetch(v_cursor_id_ind);
2232 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2233 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133','l_ignore1 := '||l_ignore1);
2234 END IF;
2235 dbms_sql.column_value(v_cursor_id_ind, 1, DSum_B);
2236 dbms_sql.column_value(v_cursor_id_ind, 2, CSum_B);
2237 --dbms_sql.close_cursor(v_cursor_id_ind);
2238
2239 if DSum_B >= CSum_B then
2240 b_bal_indicator:='D';
2241 else
2242 b_bal_indicator:='C';
2243 end if;
2244 DBMS_SQL.CLOSE_CURSOR(v_cursor_id_ind);
2245 EXCEPTION
2246 WHEN OTHERS THEN
2247 g_error_code := SQLCODE;
2248 g_error_message := SQLERRM;
2249 fv_utility.log_mesg(fnd_log.level_unexpected, 'testsf133'||'.exception',g_error_message);
2250 fv_utility.log_mesg(fnd_log.level_unexpected, 'testsf133'||'.exception','-- get_bal_type');
2251 END;
2252
2253
2254 PROCEDURE process_total_line
2255 IS
2256 l_module_name VARCHAR2(200) := g_module_name || 'process_total_line';
2257
2258 CURSOR fv_sf133_calc_cur IS
2259 SELECT calc_sequence_number, line_low, line_high, line_low_type, line_high_type,
2260 operator
2261 FROM fv_sf133_rep_line_calc
2262 WHERE line_id = c_sf133_line_id
2263 ORDER BY calc_sequence_number;
2264
2265 CURSOR fv_sf133_temp_cur (p_line_id NUMBER) IS
2266 SELECT sf133_column_amount, sf133_column_2_amount, sf133_column_3_amount, sf133_column_4_amount, sf133_column_5_amount,
2267 sf133_column_6_amount
2268 FROM fv_sf133_definitions_cols_temp
2269 WHERE sf133_line_id = p_line_id and
2270 treasury_symbol_id = c_sf133_treasury_symbol_id;
2271
2272 -- Bug 9183877
2273 CURSOR fv_cfs_lines_cur(p_lineid_1 NUMBER, p_lineid_2 NUMBER) IS
2274 SELECT sf133_line_id
2275 FROM fv_sf133_definitions_lines
2276 WHERE sf133_line_number >=
2277 (SELECT sf133_line_number FROM fv_sf133_definitions_lines
2278 WHERE sf133_line_id = p_lineid_1 )
2279 AND sf133_line_number <=
2280 (SELECT sf133_line_number FROM fv_sf133_definitions_lines
2281 WHERE sf133_line_id = p_lineid_2 );
2282
2283 l_line_id fv_cfs_rep_lines.line_id%TYPE;
2284 temp_amt_low fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE DEFAULT 0;
2285 temp_amt_high fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE DEFAULT 0;
2286 temp_amt_low_2 fv_sf133_definitions_cols_temp.sf133_column_2_amount%TYPE DEFAULT 0;
2287 temp_amt_high_2 fv_sf133_definitions_cols_temp.sf133_column_2_amount%TYPE DEFAULT 0;
2288 temp_amt_low_3 fv_sf133_definitions_cols_temp.sf133_column_3_amount%TYPE DEFAULT 0;
2289 temp_amt_high_3 fv_sf133_definitions_cols_temp.sf133_column_3_amount%TYPE DEFAULT 0;
2290 temp_amt_low_4 fv_sf133_definitions_cols_temp.sf133_column_4_amount%TYPE DEFAULT 0;
2291 temp_amt_high_4 fv_sf133_definitions_cols_temp.sf133_column_4_amount%TYPE DEFAULT 0;
2292 temp_amt_low_5 fv_sf133_definitions_cols_temp.sf133_column_5_amount%TYPE DEFAULT 0;
2293 temp_amt_high_5 fv_sf133_definitions_cols_temp.sf133_column_5_amount%TYPE DEFAULT 0;
2294 temp_amt_low_6 fv_sf133_definitions_cols_temp.sf133_column_6_amount%TYPE DEFAULT 0;
2295 temp_amt_high_6 fv_sf133_definitions_cols_temp.sf133_column_6_amount%TYPE DEFAULT 0;
2296 /*
2297 c_sf133_amt2_not_shown Number ;
2298 c_sf133_amt3_not_shown Number ;
2299 c_sf133_amt4_not_shown Number ;
2300 c_sf133_amt5_not_shown Number ;
2301 c_sf133_amt6_not_shown Number ;
2302
2303 c_sf133_column_amount2 Number ;
2304 c_sf133_column_amount3 Number ;
2305 c_sf133_column_amount4 Number ;
2306 c_sf133_column_amount5 Number ;
2307 c_sf133_column_amount6 Number ;
2308 */
2309 TYPE amt_rec IS RECORD (
2310 calc_sequence fv_sf133_rep_line_calc.calc_sequence_number%TYPE,
2311 col_1_amt fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE DEFAULT 0,
2312 col_2_amt fv_sf133_definitions_cols_temp.sf133_column_2_amount%TYPE DEFAULT 0,
2313 col_3_amt fv_sf133_definitions_cols_temp.sf133_column_3_amount%TYPE DEFAULT 0,
2314 col_4_amt fv_sf133_definitions_cols_temp.sf133_column_4_amount%TYPE DEFAULT 0,
2315 col_5_amt fv_sf133_definitions_cols_temp.sf133_column_5_amount%TYPE DEFAULT 0,
2316 col_6_amt fv_sf133_definitions_cols_temp.sf133_column_6_amount%TYPE DEFAULT 0);
2317
2318 TYPE amt_table IS TABLE OF amt_rec
2319 INDEX BY BINARY_INTEGER;
2320
2321 amt_array amt_table;
2322 amt_array_cnt BINARY_INTEGER DEFAULT 1;
2323 v_col_1_amt fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
2324 v_col_2_amt fv_sf133_definitions_cols_temp.sf133_column_2_amount%TYPE;
2325 v_col_3_amt fv_sf133_definitions_cols_temp.sf133_column_3_amount%TYPE;
2326 v_col_4_amt fv_sf133_definitions_cols_temp.sf133_column_4_amount%TYPE;
2327 v_col_5_amt fv_sf133_definitions_cols_temp.sf133_column_5_amount%TYPE;
2328 v_col_6_amt fv_sf133_definitions_cols_temp.sf133_column_6_amount%TYPE;
2329
2330
2331 BEGIN
2332 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'Inside process_total_line');
2333 FOR calc_rec IN fv_sf133_calc_cur
2334 LOOP
2335 amt_array(amt_array_cnt).calc_sequence := calc_rec.calc_sequence_number;
2336
2337 IF calc_rec.line_low_type = 'L' AND calc_rec.operator IN ('+','-') THEN
2338 l_line_id := calc_rec.line_low;
2339 OPEN fv_sf133_temp_cur(l_line_id);
2340 FETCH fv_sf133_temp_cur
2341 INTO temp_amt_low,temp_amt_low_2,temp_amt_low_3,temp_amt_low_4,temp_amt_low_5,temp_amt_low_6;
2342 CLOSE fv_sf133_temp_cur;
2343 ELSIF calc_rec.line_low_type = 'C' AND calc_rec.operator IN ('+','-') THEN
2344 FOR i IN 1..amt_array_cnt
2345 LOOP
2346 IF amt_array(i).calc_sequence = calc_rec.line_low THEN
2347 temp_amt_low := amt_array(i).col_1_amt;
2348 temp_amt_low_2 := amt_array(i).col_2_amt;
2349 temp_amt_low_3 := amt_array(i).col_3_amt;
2350 temp_amt_low_4 := amt_array(i).col_4_amt;
2351 temp_amt_low_5 := amt_array(i).col_5_amt;
2352 temp_amt_low_6 := amt_array(i).col_6_amt;
2353
2354 END IF;
2355 END LOOP;
2356 END IF;
2357
2358 IF calc_rec.line_high_type = 'L' AND calc_rec.operator IN ('+','-') THEN
2359 l_line_id := calc_rec.line_high;
2360 OPEN fv_sf133_temp_cur(l_line_id);
2361 FETCH fv_sf133_temp_cur
2362 INTO temp_amt_high,temp_amt_high_2,temp_amt_high_3,temp_amt_high_4,temp_amt_high_5,temp_amt_high_6;
2363 CLOSE fv_sf133_temp_cur;
2364 ELSIF calc_rec.line_high_type = 'C' AND calc_rec.operator IN ('+','-') THEN
2365 FOR i IN 1..amt_array_cnt - 1
2366 LOOP
2367 IF amt_array(i).calc_sequence = calc_rec.line_high THEN
2368 temp_amt_high := amt_array(i).col_1_amt;
2369 temp_amt_high_2 := amt_array(i).col_2_amt;
2370 temp_amt_high_3 := amt_array(i).col_3_amt;
2371 temp_amt_high_4 := amt_array(i).col_4_amt;
2372 temp_amt_high_5 := amt_array(i).col_5_amt;
2373 temp_amt_high_6 := amt_array(i).col_6_amt;
2374 END IF;
2375 END LOOP;
2376 END IF;
2377
2378 IF calc_rec.operator = '+' THEN
2379 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low, 0) + NVL(temp_amt_high, 0);
2380 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low_2, 0) + NVL(temp_amt_high_2, 0);
2381 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low_3, 0) + NVL(temp_amt_high_3, 0);
2382 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low_4, 0) + NVL(temp_amt_high_4, 0);
2383 amt_array(amt_array_cnt).col_5_amt := NVL(temp_amt_low_5, 0) + NVL(temp_amt_high_5, 0);
2384 amt_array(amt_array_cnt).col_6_amt := NVL(temp_amt_low_6, 0) + NVL(temp_amt_high_6, 0);
2385 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,calc_rec.operator||amt_array(amt_array_cnt).col_1_amt);
2386
2387 ELSIF calc_rec.operator = '-' THEN
2388 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low, 0) - NVL(temp_amt_high, 0);
2389 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low_2, 0) - NVL(temp_amt_high_2, 0);
2390 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low_3, 0) - NVL(temp_amt_high_3, 0);
2391 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low_4, 0) - NVL(temp_amt_high_4, 0);
2392 amt_array(amt_array_cnt).col_5_amt := NVL(temp_amt_low_5, 0) - NVL(temp_amt_high_5, 0);
2393 amt_array(amt_array_cnt).col_6_amt := NVL(temp_amt_low_6, 0) - NVL(temp_amt_high_6, 0);
2394 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,calc_rec.operator||amt_array(amt_array_cnt).col_1_amt);
2395 ELSE
2396 IF calc_rec.line_low_type = 'L' THEN
2397 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'inside for loop, range:'||calc_rec.line_low||calc_rec.line_high);
2398 FOR lines_rec IN fv_cfs_lines_cur(calc_rec.line_low, calc_rec.line_high)
2399 LOOP
2400 FOR fv_sf133_temp_cur_rec IN fv_sf133_temp_cur(lines_rec.sf133_line_id)
2401 LOOP
2402 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_amount, 0);
2403 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_2_amount, 0);
2404 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_3_amount, 0);
2405 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_4_amount, 0);
2406 amt_array(amt_array_cnt).col_5_amt := amt_array(amt_array_cnt).col_5_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_5_amount, 0);
2407 amt_array(amt_array_cnt).col_6_amt := amt_array(amt_array_cnt).col_6_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_6_amount, 0);
2408 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'inside for loop, value for line'||amt_array(amt_array_cnt).col_1_amt);
2409 END LOOP;
2410 END LOOP;
2411 ELSIF calc_rec.line_low_type = 'C' THEN
2412 FOR i IN 1..amt_array_cnt - 1
2413 LOOP
2414 IF amt_array(i).calc_sequence >= calc_rec.line_low
2415 AND amt_array(i).calc_sequence <= calc_rec.line_high THEN
2416 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(amt_array(i).col_1_amt, 0);
2417 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(amt_array(i).col_2_amt, 0);
2418 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(amt_array(i).col_3_amt, 0);
2419 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(amt_array(i).col_4_amt, 0);
2420 amt_array(amt_array_cnt).col_5_amt := amt_array(amt_array_cnt).col_5_amt + NVL(amt_array(i).col_5_amt, 0);
2421 amt_array(amt_array_cnt).col_6_amt := amt_array(amt_array_cnt).col_6_amt + NVL(amt_array(i).col_6_amt, 0);
2422 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'inside for loop, value for calc sequence'||amt_array(amt_array_cnt).col_1_amt);
2423 END IF;
2424 END LOOP;
2425 END IF;
2426 END IF;
2427
2428 amt_array_cnt := amt_array_cnt + 1;
2429 END LOOP;
2430
2431 v_col_1_amt := amt_array(amt_array_cnt - 1).col_1_amt;
2432 v_col_2_amt := amt_array(amt_array_cnt - 1).col_2_amt;
2433 v_col_3_amt := amt_array(amt_array_cnt - 1).col_3_amt;
2434 v_col_4_amt := amt_array(amt_array_cnt - 1).col_4_amt;
2435 v_col_5_amt := amt_array(amt_array_cnt - 1).col_5_amt;
2436 v_col_6_amt := amt_array(amt_array_cnt - 1).col_6_amt;
2437
2438 o_sf133_ts_value := c_sf133_ts_value;
2439 o_sf133_line_id := c_sf133_line_id;
2440 o_sf133_column_number := g_column_number;
2441 o_sf133_column_amount := v_col_1_amt;
2442 o_sf133_amt_not_shown := v_col_1_amt;
2443 c_sf133_column_amount2 := v_col_2_amt;
2444 c_sf133_amt2_not_shown := v_col_2_amt;
2445 c_sf133_column_amount3 := v_col_3_amt;
2446 c_sf133_amt3_not_shown := v_col_3_amt;
2447 c_sf133_column_amount4 := v_col_4_amt;
2448 c_sf133_amt4_not_shown := v_col_4_amt;
2449 c_sf133_column_amount5 := v_col_5_amt;
2450 c_sf133_amt5_not_shown := v_col_5_amt;
2451 c_sf133_column_amount6 := v_col_6_amt;
2452 c_sf133_amt6_not_shown := v_col_6_amt;
2453 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --added for 1575992
2454 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'end of process_total_line: '||o_sf133_ts_value||o_sf133_column_amount);
2455
2456 populate_temp_table;
2457
2458 -- Bug 9183877
2459 UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
2460 SET
2461 SF133_AMT_TOTAL_NOT_SHOWN =
2462 o_sf133_amt_not_shown + c_sf133_amt2_not_shown +
2463 c_sf133_amt3_not_shown + c_sf133_amt4_not_shown +
2464 c_sf133_amt5_not_shown + c_sf133_amt6_not_shown ,
2465 SF133_COLUMN_TOTAL_AMT =
2466 o_sf133_column_amount + c_sf133_column_amount2 +
2467 c_sf133_column_amount3 + c_sf133_column_amount4 +
2468 c_sf133_column_amount5 + c_sf133_column_amount6
2469 WHERE
2470 SF133_LINE_ID = c_sf133_line_id and
2471 SF133_FUND_VALUE = o_sf133_ts_value ;
2472
2473 if fv_sf133_calc_cur%ISOPEN then
2474 close fv_sf133_calc_cur;
2475 end if;
2476
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479 g_error_code := SQLCODE ;
2480 g_error_message := SQLERRM || ' [PROCESS_TOTAL_LINE] ' ;
2481 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
2482 RETURN;
2483 END process_total_line;
2484 -- + Global Varibale Declaration +
2485 BEGIN
2486 g_module_name := 'fv.plsql.fv_sf133_oneyear.';
2487 END fv_sf133_oneyear;
2488
2489
2490