[Home] [Help]
PACKAGE BODY: APPS.FV_SF133_ONEYEAR
Source
1 PACKAGE BODY fv_sf133_oneyear AS
2 --$Header: FVSF133B.pls 120.28 2006/07/07 13:38:21 kthatava 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
82 -- new variabla declared by Narsimha.
83
84 c_sf133_report_line_number fv_sf133_definitions_lines.sf133_report_line_number%TYPE;
85 --
86 -- ---------- Balance Type Cursor Vaiables ---------
87 c_sf133_line_acct_id fv_sf133_definitions_accts.sf133_line_acct_id%TYPE;
88 c_sf133_balance_type fv_sf133_definitions_accts.sf133_balance_type%TYPE;
89
90 -- new variables declared by Narsimha.
91
92 -- c_sf133_apportion_amt number;
93 c_sf133_additional_info fv_sf133_definitions_accts.sf133_additional_info%TYPE;
94
95 -- ---------- Treasury Symbol Accummulation Cursor Vaiables ---------
96 c_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
97 c_sf133_amount_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
98 --
99 -- ---------- Output Report Line Column Data -------------
100 o_sf133_ts_value fv_sf133_definitions_cols_temp.sf133_fund_value%TYPE;
101 o_sf133_line_id fv_sf133_definitions_cols_temp.sf133_line_id%TYPE;
102 o_sf133_column_number fv_sf133_definitions_cols_temp.sf133_column_number%TYPE;
103 o_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
104 o_sf133_amt_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
105
106 -- New variable declared by pkpatel to fix Bug 1575992
107 o_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
108 -- New Variables for using dynamic SQL
109 v_select VARCHAR2(30000);
110 v_cursor_id INTEGER;
111
112 --
113 --Added ts_range_cursor as part of Enh #2129123
114 /* Cursor to select treasury symbols which fall in specified range */
115 CURSOR ts_range_cursor(tsymbol_r1 VARCHAR2,tsymbol_r2 VARCHAR2) IS
116 SELECT treasury_symbol,treasury_symbol_id
117 FROM fv_treasury_symbols
118 WHERE treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
119 AND time_frame ='SINGLE'
120 AND (fund_group_code NOT BETWEEN '3800' AND '3899')
121 AND (fund_group_code NOT BETWEEN '6001' AND '6999')
122 AND set_of_books_id = parm_set_of_books_id
123 ORDER BY treasury_symbol;
124
125 -- ---------- Define Segment Name Cursor -----------------
126 CURSOR flex_field_column_name_cursor
127 IS
128 SELECT UPPER(glflex.segment_name) segment_name,
129 UPPER(glflex.application_column_name) flex_column_name
130 FROM fnd_id_flex_segments glflex
131 WHERE glflex.application_id = 101
132 AND glflex.id_flex_num = g_chart_of_accounts_id
133 AND glflex.id_flex_code = 'GL#'
134 ORDER BY glflex.application_column_name;
135 --
136 -- ---------- Define Report Treasury Symbol Line Cursor -------------
137 -- MODIFIED BY SURYA ON 5/6/98 TO REPLACE FV_FUND_PARAMETERS WITH
138 -- FV_TREASURY_SYMBOLS
139
140 -- Modified by Surya on 1/20/99 to add another join for SOB to fix
141 -- data duplication
142 --pkpatel :Changed to fix Bug 1575992
143 CURSOR ts_report_line_cursor
144 IS
145 SELECT
146 FTS.treasury_symbol sf133_ts_value,
147 FTS.treasury_symbol_id sf133_treasury_symbol_id,
148 line.sf133_line_id sf133_line_id,
149 line.sf133_line_number sf133_line_number,
150 line.sf133_line_type_code sf133_line_type_code,
151 line.sf133_natural_balance_type sf133_natural_balance_type,
152 line.sf133_fund_category sf133_line_category,
153 line.sf133_report_line_number sf133_report_line_number
154
155 FROM fv_sf133_definitions_lines line,
156 FV_TREASURY_SYMBOLS FTS
157 WHERE FTS.Treasury_symbol = parm_treasury_value_r1
158 AND FTS.set_of_books_id = parm_set_of_books_id
159 AND (line.sf133_line_type_code) IN ('T', 'D', 'S', 'N')
160 AND line.set_of_books_id = FTS.set_of_books_id
161 ORDER BY FTS.treasury_symbol,
162 line.sf133_line_number ;
163 --
164 -- ---------- Determine Balance Type of Acct -------------
165 --
166 CURSOR balance_type_cursor
167 IS
168 SELECT sf133_line_acct_id,
169 sf133_balance_type,
170 sf133_additional_info
171 FROM fv_sf133_definitions_accts
172 WHERE sf133_line_id = c_sf133_line_id ;
173
174 PROCEDURE determine_acct_flex_segments;
175 PROCEDURE purge_temp_table;
176 PROCEDURE build_report_lines;
177 PROCEDURE build_fiscal_line_columns(c_begin_period Number,
178 c_end_period Number, c_fiscal_year Number);
179 PROCEDURE build_total_line_columns;
180 PROCEDURE populate_temp_table;
181 PROCEDURE populate_gtt_with_ccid
182 (
183 p_treasury_symbol_id NUMBER
184 );
185
186 --
187 -- Added by Surya on 05/08/98 to get beginning and ending periods
188 -- for a given Fiscal year.
189 PROCEDURE GET_BEGIN_ENDING_PERIODS( V_PROCESS_YEAR NUMBER,
190 V_BEGIN_PERIOD IN OUT NOCOPY NUMBER,
191 V_END_PERIOD IN OUT NOCOPY NUMBER ) ;
192 abort_error EXCEPTION ;
193 --
194 -- ---------- End of Package Level Declaritives -----------------------------
195 --
196 -- ------------------------------------------------------------------
197 PROCEDURE Main
198 (
199 errbuf OUT NOCOPY VARCHAR2,
200 retcode OUT NOCOPY NUMBER,
201 run_mode IN VARCHAR2,
202 set_of_books_id IN NUMBER,
203 gl_period_year IN NUMBER,
204 gl_period_name IN VARCHAR2,
205 treasury_symbol_r1 IN VARCHAR2,
206 treasury_symbol_r2 IN VARCHAR2)
207 --
208 IS
209 --
210 l_module_name VARCHAR2(200) ;
211 /*Variables used to store Request Details */
212 l_req_id NUMBER :=NULL;
213 --l_status VARCHAR2(30);
214 --l_phase VARCHAR2(30);
215 --l_devphase VARCHAR2(30);
216 --l_devstatus VARCHAR2(30);
217 --l_message VARCHAR2(300);
218 --l_boolean BOOLEAN;
219
220 BEGIN
221 l_module_name := g_module_name || 'Main';
222 --
223 -- ------------------------------------
224 -- Store Input Parameters in Global Variables
225 -- ------------------------------------
226 if v_debug then
227 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
228 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF PROGRAM');
229 END IF;
230 end if;
231 parm_application_id := '101';
232 parm_set_of_books_id := set_of_books_id;
233 parm_gl_period_year := gl_period_year;
234 parm_gl_period_name := gl_period_name;
235 parm_run_mode := UPPER(run_mode);
236
237
238
239 select currency_code,
240 chart_of_accounts_id
241 into g_currency_code,
242 g_chart_of_accounts_id
243 from gl_ledgers_public_v
244 where ledger_id = parm_set_of_books_id;
245 --Added for bug No. 1553099
246
247 -- ----------------------------------------
248 -- Display Program Initialization
249 -- ----------------------------------------
250
251 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
252 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FVSF133 STARTING, '
253 ||' Run Mode is '||parm_run_mode);
254 END IF;
255
256
257 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- APID('||NVL(PARM_APPLICATION_ID,0) ||')'
259 ||' SoB('||NVL(parm_set_of_books_id,0) ||')'
260 ||' Year('||NVL(parm_gl_period_year,0) ||')'
261 ||' Period('||NVL(parm_gl_period_num,0) ||')'
262 ||' Fund Code('||NVL(parm_treasury_value_r1,'Null')
263 ||')');
264 END IF;
265
266
267 --
268 -- ----------------------------------------
269 -- Initialize Program Row Counts and Variables
270 -- ----------------------------------------
271 g_insert_count := 0;
272 g_error_code := 0;
273 g_error_message := NULL;
274 retcode := 0;
275 errbuf := '';
276 --
277 IF g_error_code = 0 THEN
278 -- ------------------------------------
279 -- Delete All Entries from Report Temp Table
280 -- ------------------------------------
281 purge_temp_table;
282 END IF;
283
284 IF g_error_code = 0 THEN
285 determine_acct_flex_segments;
286 END IF;
287
288 IF g_error_code = 0 THEN
289 -- ----------------------------------------
290 -- Build Report Lines
291 -- ----------------------------------------
292 /* Processing for Treasury symbols done in a LOOP to handle Multiple Treasury symbols */
293 FOR ts_rec IN ts_range_cursor(treasury_symbol_r1,treasury_symbol_r2)
294 LOOP
295 -- New code added by Narsimha Balakkari to get the established year and
296 -- cancellation year for specific treasury symbol
297 parm_treasury_value_r1 := ts_rec.treasury_symbol;
298 parm_treasury_symbol_id := ts_rec.treasury_symbol_id;
299
300 populate_gtt_with_ccid (parm_treasury_symbol_id);
301
302 SELECT established_fiscal_yr, substr(cancellation_date,8,4)
303 INTO g_established_year, g_cancellation_year
304 FROM fv_treasury_symbols
305 WHERE treasury_symbol = parm_treasury_value_r1
306 AND set_of_books_id = parm_set_of_books_id ;
307 -- dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_treasury_symbol_id);
308 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
309 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'PROCESSING FOR TREASURY SYMBOL .......'|| PARM_TREASURY_VALUE_R1);
310 END IF;
311
312 --Fetch the Federal Acct Symbol Id for the TS
313
314 SELECT federal_acct_symbol_id
315 INTO g_federal_acct_symbol_id
316 FROM fv_treasury_symbols
317 WHERE set_of_books_id = parm_set_of_books_id
318 AND treasury_symbol_id = parm_treasury_symbol_id;
319
320 build_report_lines;
321 IF g_error_code <> 0 THEN
322 errbuf := errbuf || 'Processing for Treasury Symbol .......'|| parm_treasury_value_r1 || 'FAILED'|| g_error_message;
323 ELSE
324 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING SF133 REPORT FOR TREASURY SYMBOL......' || PARM_TREASURY_VALUE_R1);
326 END IF;
327 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV','FVXBEGLP','','',FALSE,parm_set_of_books_id,--g_chart_of_accounts_id,
328 parm_gl_period_year,parm_gl_period_name,parm_treasury_value_r1);
329 IF l_req_id = 0 THEN
330 errbuf := 'Error submitting SF133 Report for Treasury Symbol'|| parm_treasury_value_r1 ;
331 retcode := -1;
332 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1', errbuf) ;
333 return;
334 ELSE
335 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CONCURRENT REQUEST ID FOR SF133 REPORT - ' || L_REQ_ID);
337 END IF;
338 END IF;
339 END IF;
340
341 -- Committing here to avoid deleting the temporary table
342 COMMIT;
343
344 END LOOP;
345 END IF;
346
347 IF g_error_code <> 0 THEN
348 RAISE abort_error;
349 END IF;
350 --
351 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
352 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
353 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- INSERT COUNT('||G_INSERT_COUNT||')');
354 END IF;
355 IF errbuf IS NOT null THEN
356 errbuf := 'Normal End of FVSF133 package';
357 END IF;
358 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
359 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,ERRBUF);
360 END IF;
361 -- ------------------------------------
362 -- Exceptions
363 -- ------------------------------------
364 EXCEPTION
365 --
366 WHEN abort_error THEN
367 retcode := g_error_code;
368 errbuf := g_error_message;
369 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1', errbuf) ;
370 WHEN OTHERS THEN
371 g_error_code := SQLCODE;
372 g_error_message := SQLERRM;
373 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
374 RAISE_APPLICATION_ERROR(-20222,'FVSF133 Exception-'||SQLERRM);
375 END Main;
376 -- ------------------------------------------------------------------
377 -- --------------------------------------------------------
378 PROCEDURE determine_acct_flex_segments
379 --
380 AS
381 l_module_name VARCHAR2(200);
382
383 -- for data access security
384 das_id NUMBER;
385 das_where VARCHAR2(600);
386 --
387 BEGIN
388 l_module_name := g_module_name || 'determine_acct_flex_segments';
389 --
390 IF parm_run_mode = 'T' THEN
391 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
392 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START DETERMINE_ACCT_FLEX_SEGMENTS');
393 END IF;
394 END IF;
395 --
396 -- -------------------------------------
397 -- Store SoB's Chart of Accounts Id
398 -- -------------------------------------
399 SELECT chart_of_accounts_id
400 INTO g_chart_of_accounts_id
401 FROM gl_ledgers_public_v
402 WHERE ledger_id = parm_set_of_books_id;
403 --
404 /* SELECT statement brought OUT NOCOPY of the LOOP as it does nto use any of the loop variables */
405 -- find the balance segment (fund) application_column_name
406 SELECT application_column_name
407 INTO v_balance_column_name
408 FROM fnd_segment_attribute_values
409 WHERE application_id = 101
410 AND id_flex_code = 'GL#'
411 AND id_flex_num = g_chart_of_accounts_id
412 AND segment_attribute_type = 'GL_BALANCING'
413 AND attribute_value = 'Y';
414
415 /* Used dynamic SQL instead of balance_cursor to improve performance */
416 v_select := 'SELECT decode(:cv_balance_type, ' ||
417 ''''|| 'B' || '''' || ',' || '
418 ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
419 NVL(glbal.begin_balance_cr,0)
420 ),0),2),' ||
421 ''''|| 'E' || '''' || ',' || '
422 ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
423 NVL(glbal.begin_balance_cr,0))
424 + (NVL(glbal.period_net_dr,0) -
425 NVL(glbal.period_net_cr,0))),0),2),'||
426 ''''|| 'P' || '''' || ',' || '
427 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
428 +
429 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),-1,0,
430 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
431 +
432 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),'||
433 ''''|| 'N' || '''' || ',' || '
434 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
435 +
436 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),1,0,
437 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
438 +
439 (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2))) ' || '
440 FROM gl_balances glbal,
441 fv_sf133_definitions_accts acct,
442 fv_sf133_ccids_gt fscg
443 WHERE glbal.ledger_id = :cv_set_of_books_id
444 AND glbal.period_year = :cv_fiscal_year
445 AND glbal.period_num = :cv_period
446 AND glbal.currency_code = :cv_currency_code
447 AND glbal.actual_flag = '||''''||'A'||''''||'
448 AND glbal.code_combination_id = fscg.ccid
449 AND acct.sf133_line_id = :cv_sf133_line_id
450 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
451 AND fscg.sf133_line_acct_id = acct.sf133_line_acct_id';
452
453 -- Data Access Security:
454 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
455 das_where := gl_access_set_security_pkg.get_security_clause
456 (das_id, gl_access_set_security_pkg.READ_ONLY_ACCESS,
457 gl_access_set_security_pkg.CHECK_LEDGER_ID,
458 to_char(parm_set_of_books_id), null,
459 gl_access_set_security_pkg.CHECK_SEGVALS,
460 null, 'glcc', null);
461 IF (das_where IS NOT NULL) THEN
462 v_select := v_select || '
463 AND ' || das_where;
464 END IF;
465
466
467 /*
468 -- -------------------------------------
469 -- Store Flex Segment Names in Table
470 -- -------------------------------------
471 FOR flex_field_column_name_entry IN flex_field_column_name_cursor LOOP
472 EXIT WHEN flex_field_column_name_cursor%NOTFOUND;
473 c_segment_name := flex_field_column_name_entry.segment_name;
474 c_flex_column_name := flex_field_column_name_entry.flex_column_name;
475 --
476 -- t_segment_number := TO_NUMBER(SUBSTR(c_flex_column_name,08,02));
477 -- t_segment_name(t_segment_number) := c_flex_column_name;
478 --
479 BEGIN
480 SELECT flex_value_set_id
481 INTO g_seg_value_set_id
482 FROM fnd_id_flex_segments
483 WHERE application_column_name = c_flex_column_name
484 AND application_id = 101
485 AND id_flex_code = 'GL#'
486 AND id_flex_num = g_chart_of_accounts_id;
487 EXCEPTION
488 WHEN OTHERS THEN
489 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Error in getting the Value set attched ' ||
490 ' to the segemnt => ' || c_flex_column_name);
491 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' SQLCODE => ' || SQLCODE ||
492 ' SQLERRM => ' || SQLERRM);
493 RAISE;
494 END;
495 -- + Rollup for the amount is the segment is a parent segment +
496 v_select := v_select || '
497 AND ( NVL(glcc.'|| c_flex_column_name ||
498 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
499 ||',NVL(glcc.'||c_flex_column_name ||
500 ','||''''||'-1'||''''||')) ' || '
501 OR glcc.'||c_flex_column_name ||' IN (SELECT flex_value '||
502 'FROM fnd_flex_values ffv, fnd_flex_value_hierarchies ffvh '||
503 'WHERE ffv.flex_value BETWEEN ffvh.child_flex_value_low
504 AND ffvh.child_flex_value_high
505 AND ffv.flex_value_set_id = ' || g_seg_value_set_id ||
506 ' AND ffv.flex_value_set_id = ffvh.flex_value_set_id'||
507 ' AND parent_flex_value = acct.' || c_flex_column_name || '))';
508
509 -- + commented the below code to roll up the amount for all segments +
510 v_select := v_select || '
511 AND NVL(glcc.'|| c_flex_column_name ||
512 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
513 ||',NVL(glcc.'||c_flex_column_name ||
514 ','||''''||'-1'||''''||'))';
515
516 IF c_flex_column_name = v_balance_column_name THEN
517 -- the segment application_column_name being processed = the balancing
518 -- segment application_column_name.
519 g_fund_segment_name := c_flex_column_name;
520 END IF;
521 --
522 END LOOP;
523 */
524
525 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
526 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,V_SELECT);
527 END IF;
528 --
529 v_cursor_id := DBMS_SQL.OPEN_CURSOR();
530 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
531 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'T1');
532 END IF;
533
534 fnd_file.put_line (fnd_file.log, v_select);
535
536 dbms_sql.parse(v_cursor_id,v_select,dbms_sql.v7);
537
538 dbms_sql.bind_variable(v_cursor_id,':cv_set_of_books_id',parm_set_of_books_id);
539 -- dbms_sql.bind_variable(v_cursor_id,':cv_chart_of_accounts_id',g_chart_of_accounts_id);
540 dbms_sql.bind_variable(v_cursor_id,':cv_currency_code',g_currency_code);
541
542 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
543 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'T2');
544 END IF;
545 dbms_sql.define_column(v_cursor_id,1,c_total_balance);
546 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'T3');
548 END IF;
549
550
551 --
552 -- ------------------------------------
553 -- Exceptions
554 -- ------------------------------------
555 EXCEPTION
556 --
557 WHEN OTHERS THEN
558 g_error_code := SQLCODE;
559 g_error_message := 'determine_acct_flex_segments/'||SQLERRM;
560 IF flex_field_column_name_cursor%ISOPEN THEN
561 close flex_field_column_name_cursor;
562 END IF;
563 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
564 --
565 END determine_acct_flex_segments;
566 -- --------------------------------------------------------
567 -- --------------------------------------------------------
568 PROCEDURE purge_temp_table
569 --
570 IS
571 l_module_name VARCHAR2(200);
572 --
573 BEGIN
574 l_module_name := g_module_name || 'purge_temp_table';
575 --
576 IF parm_run_mode = 'T' THEN
577 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START PURGE_TEMP_TABLE');
579 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND SEGMENT ('||G_FUND_SEGMENT_NAME ||')');
580 END IF;
581 END IF;
582 --
583 DELETE
584 FROM fv_sf133_definitions_cols_temp
585 WHERE (sf133_line_id)
586 IN
587 (SELECT sf133_line_id
588 FROM fv_sf133_definitions_lines
589 WHERE set_of_books_id = parm_set_of_books_id);
590 --
591 COMMIT;
592 --
593 -- ------------------------------------
594 -- Exceptions
595 -- ------------------------------------
596 EXCEPTION
597 --
598 WHEN NO_DATA_FOUND THEN
599 NULL;
600 --
601 WHEN OTHERS THEN
602 g_error_code := SQLCODE;
603 g_error_message := SQLERRM;
604 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
605 --
606 END purge_temp_table;
607 -- --------------------------------------------------------
608 -- --------------------------------------------------------
609 PROCEDURE build_report_lines
610 --
611 AS
612 --
613 l_module_name VARCHAR2(200) ;
614 -- New Variables added by Surya on 04/07/98
615 l_year_counter Number ; -- FOR loop counter
616 l_process_year Number ; -- Process Year for Previous Years
617 L_BEG_PERIOD_PREV NUMBER ; -- Beginning Period-Previous Year
618 L_END_PERIOD_PREV NUMBER ; -- Ending period-previous year
619 L_LOOP_YEAR NUMBER;
620 l_federal_acct_symbol_id number(15);
621 -- l_treasury_symbol_id NUMBER(15);
622 l_sf133_ts_value fv_sf133_definitions_cols_temp.sf133_fund_value%TYPE;
623 -- ---------------------------------------------------------
624 BEGIN
625 l_module_name := g_module_name || 'build_report_lines';
626 --
627 IF parm_run_mode = 'T' THEN
628 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
629 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_REPORT_LINES');
630 END IF;
631 END IF;
632 --
633 -- ----------------------------------------------------------
634 -- Find period_number that is not an adjusting period
635 -- ----------------------------------------------------------
636 --
637 --
638 SELECT min(period_num)
639 INTO g_period_num
640 FROM gl_period_statuses
641 WHERE ledger_id = parm_set_of_books_id
642 AND adjustment_period_flag = 'N'
643 AND period_year = parm_gl_period_year
644 AND application_id = '101' ;
645
646
647 -- Added on 5/6/98 by Surya Padmanabhan to get the Period Number For
648 -- the Quarter.
649 SELECT PERIOD_NUM
650 INTO parm_gl_period_num
651 FROM GL_PERIOD_STATUSES
652 WHERE LEDGER_ID = parm_set_of_books_id AND
653 PERIOD_YEAR = parm_gl_period_year AND
654 APPLICATION_ID = '101' AND
655 CLOSING_STATUS in ('O','C') AND
656 PERIOD_NAME = parm_gl_period_name;
657 -- for bug 2642032
658 -- AND adjustment_period_flag = 'N' ;
659
660 -- ----------------------------------------------------
661 -- Get Next SF133 Treasury Symbol Line from Cursor
662 -- ----------------------------------------------------
663 --
664 g_ts_value_in_process := NULL;
665 --
666 FOR ts_report_line_entry IN ts_report_line_cursor LOOP
667 --
668 if v_debug then
669 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
670 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LG 3 INSIDE LOOP ') ;
671 END IF;
672 end if;
673
674 c_sf133_ts_value := ts_report_line_entry.sf133_ts_value;
675 c_sf133_line_id := ts_report_line_entry.sf133_line_id;
676 c_sf133_line_number := ts_report_line_entry.sf133_line_number;
677 c_sf133_line_type_code := ts_report_line_entry.sf133_line_type_code;
678 c_sf133_natural_bal_type
679 := ts_report_line_entry.sf133_natural_balance_type;
680 c_sf133_line_category := ts_report_line_entry.sf133_line_category;
681 c_sf133_report_line_number
682 := ts_report_line_entry.sf133_report_line_number;
683 c_sf133_treasury_symbol_id := ts_report_line_entry.sf133_treasury_symbol_id; --Bug 1575992
684 --
685 -- fv_utility.debug_mesg('c_sf133_line_category = '||
686 -- c_sf133_line_category);
687 -- dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',
688 -- parm_treasury_symbol_id);
689 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
690 -- dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_category',
691 -- c_sf133_line_category);
692 g_column_number := 1;
693 IF g_error_code = 0 THEN
694 IF c_sf133_line_type_code = 'D' THEN
695 g_column_number := 1;
696
697 /*********** Modifications Start *****************/
698
699 -- Get the Beginning and Ending Periods
700 -- L_PROCESS_YEAR := g_established_year;
701 L_PROCESS_YEAR := parm_gl_period_year;
702 IF g_established_year = parm_gl_period_year THEN
703 L_BEG_PERIOD_PREV := g_period_num;
704 L_END_PERIOD_PREV := parm_gl_period_num;
705 ELSE
706 GET_BEGIN_ENDING_PERIODS
707 (L_PROCESS_YEAR, L_BEG_PERIOD_PREV, L_END_PERIOD_PREV) ;
708 END IF;
709 -- Get the amount for the First Column.(Passed Quarter)
710 build_fiscal_line_columns
711 (L_BEG_PERIOD_PREV, L_END_PERIOD_PREV, L_PROCESS_YEAR) ;
712
713 -- Call Insert Procedure to insert the derived amount values
714 -- for the first column.
715 populate_temp_table;
716 -- Loop to Calculate amounts for next 5 years from established
717 -- year
718 --LGOEL: Fix for bug 1470537 decrement the loop year
719
720 --L_LOOP_YEAR := g_established_year + 1;
721 L_LOOP_YEAR := g_established_year - 1;
722
723 l_sf133_ts_value := c_sf133_ts_value;
724
725 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
726 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FEDERAL ACCT SYMBOL ID IS'|| TO_CHAR(L_FEDERAL_ACCT_SYMBOL_ID)) ;
727 END IF;
728
729 For l_year_counter IN 1..5 Loop
730
731 -- Determine the Previous Year
732
733 -- replaced L_PROCESS_YEAR := PARM_GL_PERIOD_YEAR -
734 -- l_year_counter statement
735 -- with L_PROCESS_YEAR := L_LOOP_YEAR by Narsimha Balakkari ;
736
737 /*1584188 :pkpatel - Do not decrement the Process Year */
738
739 -- L_PROCESS_YEAR := L_LOOP_YEAR ;
740 L_PROCESS_YEAR := PARM_GL_PERIOD_YEAR ;
741
742 -- Get the Beginning and Ending Periods
743 GET_BEGIN_ENDING_PERIODS
744 (L_PROCESS_YEAR, L_BEG_PERIOD_PREV, L_END_PERIOD_PREV) ;
745
746 IF L_PROCESS_YEAR = parm_gl_period_year THEN
747 L_BEG_PERIOD_PREV := g_period_num;
748 L_END_PERIOD_PREV := parm_gl_period_num;
749 END IF;
750
751 --LGOEL: Fetch the Treasury symbol for previous year
752 -- added check for established fiscal year - 1584188
753 -- added time frame condition - 1633861
754 begin
755 select treasury_symbol,treasury_symbol_id
756 into c_sf133_ts_value,g_treasury_symbol_id
757 from fv_treasury_symbols
758 WHERE set_of_books_id = parm_set_of_books_id
759 and federal_acct_symbol_id = g_federal_acct_symbol_id
760 and established_fiscal_yr = l_loop_year
761 and time_frame = 'SINGLE';
762
763
764 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
765 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TREASURY SYMBOL IS '||C_SF133_TS_VALUE );
766 END IF;
767 -- dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',
768 -- g_treasury_symbol_id);
769 -- Derive the Amount Values for the Previous Year
770 build_fiscal_line_columns
771 (L_BEG_PERIOD_PREV, L_END_PERIOD_PREV, L_PROCESS_YEAR) ;
772
773 exception when no_data_found then
774 o_sf133_column_amount := 0;
775 o_sf133_amt_not_shown := 0;
776
777
778 end;
779
780 --LGOEL: Restore the treasury symbol variable value
781 c_sf133_ts_value := l_sf133_ts_value;
782
783
784 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
785 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESS YEAR - ' || TO_CHAR(L_PROCESS_YEAR) ||
786 'Beginning Period - ' || to_char(l_beg_period_prev) ||
787 'Ending Period - ' || to_char(l_end_period_prev)) ;
788 END IF;
789
790 -- Update the Current Row with derived values.
791
792 -- Since Decode cannot be used in the left side of the
793 -- assignment after SET phrase, a litle round about way
794 -- is used by using Decode on the right side. Still one
795 -- SQL statement !!
796
797 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
798 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)) ;
799 END IF;
800 UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
801 SET
802 SF133_COLUMN_2_AMOUNT = DECODE(L_YEAR_COUNTER, 1,
803 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_2_AMOUNT),
804 SF133_COLUMN_3_AMOUNT = DECODE(L_YEAR_COUNTER, 2,
805 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_3_AMOUNT),
806 SF133_COLUMN_4_AMOUNT = DECODE(L_YEAR_COUNTER, 3,
807 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_4_AMOUNT),
808 SF133_COLUMN_5_AMOUNT = DECODE(L_YEAR_COUNTER, 4,
809 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_5_AMOUNT),
810 SF133_COLUMN_6_AMOUNT = DECODE(L_YEAR_COUNTER, 5,
811 O_SF133_COLUMN_AMOUNT, SF133_COLUMN_6_AMOUNT),
812
813 SF133_AMT_2_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 1,
814 O_SF133_AMT_NOT_SHOWN, SF133_AMT_2_NOT_SHOWN),
815 SF133_AMT_3_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 2,
816 O_SF133_AMT_NOT_SHOWN, SF133_AMT_3_NOT_SHOWN),
817 SF133_AMT_4_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 3,
818 O_SF133_AMT_NOT_SHOWN, SF133_AMT_4_NOT_SHOWN),
819 SF133_AMT_5_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 4,
820 O_SF133_AMT_NOT_SHOWN, SF133_AMT_5_NOT_SHOWN),
821 SF133_AMT_6_NOT_SHOWN = DECODE(L_YEAR_COUNTER, 5,
822 O_SF133_AMT_NOT_SHOWN, SF133_AMT_6_NOT_SHOWN)
823
824 WHERE
825 SF133_FUND_VALUE = L_SF133_TS_VALUE AND
826 SF133_LINE_ID = O_SF133_LINE_ID ;
827
828 L_LOOP_YEAR := L_LOOP_YEAR - 1;
829 End Loop ;
830
831 -- Update the Current Row with the total.
832 UPDATE FV_SF133_DEFINITIONS_COLS_TEMP
833 SET
834 SF133_AMT_TOTAL_NOT_SHOWN =
835 SF133_AMOUNT_NOT_SHOWN + SF133_AMT_2_NOT_SHOWN +
836 SF133_AMT_3_NOT_SHOWN + SF133_AMT_4_NOT_SHOWN +
837 SF133_AMT_5_NOT_SHOWN + SF133_AMT_6_NOT_SHOWN ,
838
839 SF133_COLUMN_TOTAL_AMT =
840 SF133_COLUMN_AMOUNT + SF133_COLUMN_2_AMOUNT +
841 SF133_COLUMN_3_AMOUNT + SF133_COLUMN_4_AMOUNT +
842 SF133_COLUMN_5_AMOUNT + SF133_COLUMN_6_AMOUNT
843
844 WHERE
845 SF133_FUND_VALUE = L_SF133_TS_VALUE AND
846 SF133_LINE_ID = O_SF133_LINE_ID ;
847
848 ELSE
849
850 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BUILDING TOTAL LINE COLUMNS') ;
852 END IF;
853 build_total_line_columns;
854 END IF;
855 --
856 IF g_error_code = 0 THEN
857 IF g_ts_value_in_process IS NULL
858 OR g_ts_value_in_process <> c_sf133_ts_value THEN
859 g_ts_value_in_process := c_sf133_ts_value;
860 g_total_start_line_number := 0;
861 g_subtotal_start_line_number := 0;
862 ELSE
863 IF c_sf133_line_type_code IN ('T', 'S', 'N') THEN
864 g_subtotal_start_line_number := c_sf133_line_number;
865 IF c_sf133_line_type_code = 'T' THEN
866 g_total_start_line_number := c_sf133_line_number;
867 ELSE
868 g_total_start_line_number := c_sf133_prev_line_number;
869 END IF;
870 END IF;
871 END IF;
872 END IF;
873 END IF;
874 c_sf133_prev_line_number := ts_report_line_entry.sf133_line_number;
875 --
876 END LOOP;
877 --
878 -- ------------------------------------
879 -- Exceptions
880 -- ------------------------------------
881 EXCEPTION
882 --
883 WHEN OTHERS THEN
884 g_error_code := SQLCODE;
885 g_error_message := SQLERRM;
886
887 IF ts_report_line_cursor%ISOPEN THEN
888 close ts_report_line_cursor;
889 END IF;
890 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
891 --
892 END build_report_lines;
893 -- --------------------------------------------------------
894 -- ----------------------------------------------
895 PROCEDURE build_fiscal_line_columns
896 (c_begin_period Number, c_end_period Number, c_fiscal_year Number)
897 --
898 IS
899 --
900 l_module_name VARCHAR2(200) ;
901 l_ignore INTEGER;
902 -- ----------------------------------------------
903 BEGIN
904 l_module_name := g_module_name || 'build_fiscal_line_columns';
905 --
906 IF parm_run_mode = 'T' THEN
907
908 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
909 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_FISCAL_LINE_COLUMNS');
910 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- LINE('||C_SF133_LINE_NUMBER||')'
911 || ' Tresury Symbol('||c_sf133_ts_value ||')'
912 || ' '||to_char(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
913 END IF;
914 END IF;
915 --
916 -- ----------------------------------------
917 -- Get Fund Accummulation
918 -- ----------------------------------------
919 c_total_balance := 0;
920 c_sf133_amount_not_shown := 0;
921 c_begin_balance := 0;
922 c_ending_balance := 0;
923
924
925 -- Removed the Following Statements, since the Beginning and Ending
926 -- periods are passed as parameters.
927 -- c_begin_period := g_period_num;
928 -- c_end_period := parm_gl_period_num;
929 --
930
931
932 -- for the line find all accounts and sum
933 FOR balance_type_rec in balance_type_cursor LOOP
934 c_sf133_line_acct_id := balance_type_rec.sf133_line_acct_id;
935 c_sf133_balance_type := balance_type_rec.sf133_balance_type;
936 c_sf133_additional_info := balance_type_rec.sf133_additional_info;
937 -- c_zero_drcr_flag := balance_type_rec.zero_drcr_flag;
938
939 -- fv_utility.debug_mesg('balance_type = '||c_sf133_balance_type);
940 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
941 -- New code added written by Narsimha Balakkari to solve the Rescission
942 -- problem.
943 c_rescission_flag := 'FALSE';
944 IF upper(c_sf133_additional_info) = 'RESCISSION' THEN
945
946 select upper(resource_type) into c_resource_type
947 from fv_treasury_symbols
948 where treasury_symbol = parm_treasury_value_r1
949 and set_of_books_id = parm_set_of_books_id;
950
951 IF c_resource_type like '%APPROPRIATION%' THEN
952 IF ltrim(rtrim(c_sf133_report_line_number)) = '1A' THEN
953 c_rescission_flag := 'TRUE';
954 ELSE
955 c_rescission_flag := 'FALSE';
956 END IF;
957 ELSIF c_resource_type like '%BORROWING%' THEN
958 IF c_sf133_report_line_number = '1B' THEN
959 c_rescission_flag := 'TRUE';
960 ELSE
961 c_rescission_flag := 'FALSE';
962 END IF;
963 ELSIF c_resource_type like '%CONTRACT%' THEN
964 IF c_sf133_report_line_number = '1C' THEN
965 c_rescission_flag := 'TRUE';
966 ELSE
967 c_rescission_flag := 'FALSE';
968 END IF;
969 END IF;
970 ELSE
971 c_rescission_flag := 'TRUE';
972 END IF;
973 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
974 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ADDITIONAL INFORMATION IS ' || UPPER(C_SF133_ADDITIONAL_INFO));
975 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LINE NUMBER IS ' || C_SF133_REPORT_LINE_NUMBER);
976 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RESOURCE TYPE IS ' || C_RESOURCE_TYPE);
977 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RESOURCE FLAG IS ' || C_RESCISSION_FLAG);
978 END IF;
979 IF c_rescission_flag = 'TRUE' THEN
980
981 IF c_sf133_balance_type = 'E' THEN
982 -- ending balance type
983
984 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
985 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LINE_ACCT_ID = '||C_SF133_LINE_ACCT_ID);
986 END IF;
987 /*Used Dynamice SQL instead of balance_cursor */
988 dbms_sql.bind_variable(v_cursor_id,':cv_balance_type','E');
989 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
990 dbms_sql.bind_variable(v_cursor_id,':cv_fiscal_year',c_fiscal_year);
991 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
992 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
993 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_IGNORE := '|| L_IGNORE);
994 END IF;
995 dbms_sql.column_value(v_cursor_id,1,c_total_balance);
996
997 ELSIF c_sf133_balance_type = 'B' THEN
998 -- beginning balance type
999 /*Used Dynamice SQL instead of balance_cursor */
1000 dbms_sql.bind_variable(v_cursor_id,':cv_balance_type','B');
1001 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_begin_period);
1002 dbms_sql.bind_variable(v_cursor_id,':cv_fiscal_year',c_fiscal_year);
1003 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1004 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1005 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_IGNORE := '|| L_IGNORE);
1006 END IF;
1007 dbms_sql.column_value(v_cursor_id,1,c_total_balance);
1008
1009
1010 ELSIF c_sf133_balance_type = 'P' THEN
1011
1012 /*Used Dynamice SQL instead of balance_cursor */
1013 dbms_sql.bind_variable(v_cursor_id,':cv_balance_type','P');
1014 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
1015 dbms_sql.bind_variable(v_cursor_id,':cv_fiscal_year',c_fiscal_year);
1016 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1017 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1018 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_IGNORE := '|| L_IGNORE);
1019 END IF;
1020 dbms_sql.column_value(v_cursor_id,1,c_total_balance);
1021
1022 ELSIF c_sf133_balance_type = 'N' THEN
1023
1024 /*Used Dynamice SQL instead of balance_cursor */
1025 dbms_sql.bind_variable(v_cursor_id,':cv_balance_type','N');
1026 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
1027 dbms_sql.bind_variable(v_cursor_id,':cv_fiscal_year',c_fiscal_year);
1028 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1029 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1030 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_IGNORE := '|| L_IGNORE);
1031 END IF;
1032 dbms_sql.column_value(v_cursor_id,1,c_total_balance);
1033
1034 ELSIF c_sf133_balance_type in ('D','S') THEN
1035 -- ending - beginning balance type = D (Difference)
1036 -- beginning - ending balance type = S (Subsequent)
1037
1038 /*Used Dynamice SQL instead of balance_cursor */
1039 dbms_sql.bind_variable(v_cursor_id,':cv_balance_type','E');
1040 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
1041 dbms_sql.bind_variable(v_cursor_id,':cv_fiscal_year',c_fiscal_year);
1042 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1043 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1044 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_IGNORE := '|| L_IGNORE);
1045 END IF;
1046 --Fixed bug # 2433012. Modified the following statement(dbms_sql.column_value) with column value c_ending_balance
1047 --in place of c_total_balance.
1048 dbms_sql.column_value(v_cursor_id,1,c_ending_balance);
1049 /*Used Dynamice SQL instead of balance_cursor */
1050 dbms_sql.bind_variable(v_cursor_id,':cv_balance_type','B');
1051 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_begin_period);
1052 dbms_sql.bind_variable(v_cursor_id,':cv_fiscal_year',c_fiscal_year);
1053 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1054 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1055 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_IGNORE := '|| L_IGNORE);
1056 END IF;
1057 --Fixed bug # 2433012. Modified the following statement(dbms_sql.column_value) with column value c_begin_balance
1058 --in place of c_total_balance.
1059 dbms_sql.column_value(v_cursor_id,1,c_begin_balance);
1060 IF c_sf133_balance_type = 'D' THEN
1061 c_total_balance := c_ending_balance - c_begin_balance;
1062 ELSIF c_sf133_balance_type = 'S' THEN
1063 c_total_balance := c_begin_balance - c_ending_balance;
1064 END IF;
1065 END IF;
1066
1067 -- sum the line amount
1068 c_sf133_amount_not_shown := c_sf133_amount_not_shown + c_total_balance;
1069 -- fv_utility.debug_mesg('amt not shown = '||c_sf133_amount_not_shown);
1070 END IF;
1071 END LOOP;
1072 --
1073 -- set up correct display sign
1074 --
1075 -- fv_utility.debug_mesg('natural bal type = '||c_sf133_natural_bal_type);
1076 IF c_sf133_natural_bal_type = 'C' THEN
1077 -- Credit so display opposite
1078 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
1079
1080 ELSIF c_sf133_natural_bal_type = 'D' THEN
1081 -- Debit so display as is
1082 c_sf133_column_amount := c_sf133_amount_not_shown;
1083
1084 ELSIF c_sf133_natural_bal_type = 'A' THEN
1085 -- Display the absolute value
1086 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
1087
1088 ELSIF c_sf133_natural_bal_type = 'N' THEN
1089 -- Display as negative
1090 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
1091
1092 END IF;
1093
1094 --
1095 o_sf133_ts_value := c_sf133_ts_value;
1096 o_sf133_line_id := c_sf133_line_id;
1097 o_sf133_column_number := g_column_number;
1098 o_sf133_column_amount := c_sf133_column_amount;
1099 o_sf133_amt_not_shown := c_sf133_amount_not_shown;
1100 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --Bug 1575992
1101
1102 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1103 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COL AMT ='||O_SF133_COLUMN_AMOUNT);
1104 END IF;
1105 --
1106 -- ------------------------------------
1107 -- Exceptions
1108 -- ------------------------------------
1109 EXCEPTION
1110 --
1111 --
1112 WHEN OTHERS THEN
1113
1114 g_error_code := SQLCODE;
1115 g_error_message := SQLERRM;
1116
1117 IF balance_type_cursor%ISOPEN THEN
1118 close balance_type_cursor;
1119 /* ELSIF balance_cursor%ISOPEN THEN
1120 close balance_cursor;*/
1121 END IF;
1122
1123 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1124 --
1125 END build_fiscal_line_columns;
1126 -- ----------------------------------------------
1127 -- ----------------------------------------------
1128 PROCEDURE build_total_line_columns
1129 --
1130 IS
1131 l_module_name VARCHAR2(200) ;
1132 -- Variables added by Surya to accomodate Previous Year Column totals
1133 c_sf133_amt2_not_shown Number ;
1134 c_sf133_amt3_not_shown Number ;
1135 c_sf133_amt4_not_shown Number ;
1136 c_sf133_amt5_not_shown Number ;
1137 c_sf133_amt6_not_shown Number ;
1138 c_sf133_amt_total_not_shown Number ;
1139
1140
1141 c_sf133_column_amount2 Number ;
1142 c_sf133_column_amount3 Number ;
1143 c_sf133_column_amount4 Number ;
1144 c_sf133_column_amount5 Number ;
1145 c_sf133_column_amount6 Number ;
1146 c_sf133_column_amount_total Number ;
1147
1148 --
1149 -- ----------------------------------------------
1150 BEGIN
1151 l_module_name := g_module_name || 'build_total_line_columns';
1152 --
1153 IF parm_run_mode = 'T' THEN
1154 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1155 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_TOTAL_LINE_COLUMNS');
1156 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- LINE='||C_SF133_LINE_NUMBER
1157 ||' Start Total Line('||g_total_start_line_number||')'
1158 || ' Treasury Symbol('||c_sf133_ts_value||')');
1159 END IF;
1160 END IF;
1161 --
1162 -- ----------------------------------------
1163 -- Get Treasury Symbol Accummulation for Total using column with true sign.
1164 -- ----------------------------------------
1165 -- Modified By Surya to get the total of Past Year Columns
1166
1167 SELECT NVL(SUM(NVL(sf133_amount_not_shown,0)),0),
1168 NVL(SUM(NVL(sf133_amt_2_not_shown,0)),0),
1169 NVL(SUM(NVL(sf133_amt_3_not_shown,0)),0),
1170 NVL(SUM(NVL(sf133_amt_4_not_shown,0)),0),
1171 NVL(SUM(NVL(sf133_amt_5_not_shown,0)),0),
1172 NVL(SUM(NVL(sf133_amt_6_not_shown,0)),0),
1173 NVL(SUM(NVL(sf133_amt_total_not_shown,0)),0)
1174
1175 INTO c_sf133_amount_not_shown,
1176 c_sf133_amt2_not_shown ,
1177 c_sf133_amt3_not_shown ,
1178 c_sf133_amt4_not_shown ,
1179 c_sf133_amt5_not_shown ,
1180 c_sf133_amt6_not_shown ,
1181 c_sf133_amt_total_not_shown
1182
1183 FROM fv_sf133_definitions_cols_temp
1184 WHERE sf133_column_number = g_column_number
1185 AND sf133_fund_value = c_sf133_ts_value
1186 AND (sf133_line_id)
1187 IN
1188 (SELECT sf133_line_id
1189 FROM fv_sf133_definitions_lines
1190 WHERE set_of_books_id = parm_set_of_books_id
1191 AND sf133_line_number >
1192 DECODE(c_sf133_line_type_code, 'T', g_total_start_line_number, g_subtotal_start_line_number)
1193 AND sf133_line_number < c_sf133_line_number);
1194
1195
1196 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1197 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NATURAL BAL TYPE = '||C_SF133_NATURAL_BAL_TYPE);
1198 END IF;
1199 IF c_sf133_natural_bal_type = 'C' THEN
1200
1201 -- Credit, so display opposite
1202 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
1203 c_sf133_column_amount2 := c_sf133_amt2_not_shown * -1;
1204 c_sf133_column_amount3 := c_sf133_amt3_not_shown * -1;
1205 c_sf133_column_amount4 := c_sf133_amt4_not_shown * -1;
1206 c_sf133_column_amount5 := c_sf133_amt5_not_shown * -1;
1207 c_sf133_column_amount6 := c_sf133_amt6_not_shown * -1;
1208 c_sf133_column_amount_total := c_sf133_amt_total_not_shown * -1;
1209
1210 ELSIF c_sf133_natural_bal_type = 'D' THEN
1211
1212 -- Debit so display as is
1213 c_sf133_column_amount := c_sf133_amount_not_shown;
1214 c_sf133_column_amount2 := c_sf133_amt2_not_shown ;
1215 c_sf133_column_amount3 := c_sf133_amt3_not_shown ;
1216 c_sf133_column_amount4 := c_sf133_amt4_not_shown ;
1217 c_sf133_column_amount5 := c_sf133_amt5_not_shown ;
1218 c_sf133_column_amount6 := c_sf133_amt6_not_shown ;
1219 c_sf133_column_amount_total := c_sf133_amt_total_not_shown ;
1220
1221 ELSIF c_sf133_natural_bal_type = 'A' THEN
1222
1223 -- Display the absolute value
1224 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
1225 c_sf133_column_amount2 := ABS(c_sf133_amt2_not_shown) ;
1226 c_sf133_column_amount3 := ABS(c_sf133_amt3_not_shown) ;
1227 c_sf133_column_amount4 := ABS(c_sf133_amt4_not_shown) ;
1228 c_sf133_column_amount5 := ABS(c_sf133_amt5_not_shown) ;
1229 c_sf133_column_amount6 := ABS(c_sf133_amt6_not_shown) ;
1230 c_sf133_column_amount_total := ABS(c_sf133_amt_total_not_shown) ;
1231
1232 ELSIF c_sf133_natural_bal_type = 'N' THEN
1233
1234 -- Display as negative
1235 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
1236 c_sf133_column_amount2 := '-'||ABS(c_sf133_amt2_not_shown) ;
1237 c_sf133_column_amount3 := '-'||ABS(c_sf133_amt3_not_shown) ;
1238 c_sf133_column_amount4 := '-'||ABS(c_sf133_amt4_not_shown) ;
1239 c_sf133_column_amount5 := '-'||ABS(c_sf133_amt5_not_shown) ;
1240 c_sf133_column_amount6 := '-'||ABS(c_sf133_amt6_not_shown) ;
1241 c_sf133_column_amount_total :=
1242 '-'||ABS(c_sf133_amt_total_not_shown) ;
1243
1244 END IF;
1245
1246 -- NOTE ----
1247 -- No Specific Output variables starting with 'O' are used for inserting
1248 -- data. Original variables are used instead.
1249 -- (Refer 'populate_temp_table' Procedure for Output variables)
1250
1251 -- Column amount and Column not shown has the same value in the table
1252
1253 -- ------------------------------------
1254 -- Insert the Values into Report
1255 -- ------------------------------------
1256 INSERT
1257 INTO fv_sf133_definitions_cols_temp
1258 ( sf133_fund_value,
1259 treasury_symbol_id,--Bug 1575992
1260 sf133_line_id,
1261 sf133_column_number,
1262 sf133_column_amount,
1263 sf133_amount_not_shown,
1264 SF133_COLUMN_2_AMOUNT ,
1265 SF133_AMT_2_NOT_SHOWN ,
1266 SF133_COLUMN_3_AMOUNT ,
1267 SF133_AMT_3_NOT_SHOWN ,
1268 SF133_COLUMN_4_AMOUNT ,
1269 SF133_AMT_4_NOT_SHOWN ,
1270 SF133_COLUMN_5_AMOUNT ,
1271 SF133_AMT_5_NOT_SHOWN ,
1272 SF133_COLUMN_6_AMOUNT ,
1273 SF133_AMT_6_NOT_SHOWN ,
1274 SF133_COLUMN_TOTAL_AMT,
1275 SF133_AMT_TOTAL_NOT_SHOWN )
1276
1277 VALUES(c_sf133_ts_value,
1278 c_sf133_treasury_symbol_id, --Bug 1575992
1279 c_sf133_line_id,
1280 g_column_number,
1281 c_sf133_column_amount,
1282 c_sf133_amount_not_shown,
1283 c_sf133_column_amount2,
1284 c_sf133_amt2_not_shown,
1285 c_sf133_column_amount3,
1286 c_sf133_amt3_not_shown,
1287 c_sf133_column_amount4,
1288 c_sf133_amt4_not_shown,
1289 c_sf133_column_amount5,
1290 c_sf133_amt5_not_shown,
1291 c_sf133_column_amount6,
1292 c_sf133_amt6_not_shown,
1293 c_sf133_column_amount_total,
1294 c_sf133_amt_total_not_shown);
1295
1296 --
1297 g_insert_count := g_insert_count + 1;
1298 --
1299
1300 -- ------------------------------------
1301 -- Exceptions
1302 -- ------------------------------------
1303 EXCEPTION
1304 --
1305 WHEN OTHERS THEN
1306 g_error_code := SQLCODE;
1307 g_error_message := SQLERRM;
1308 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1309 --
1310 END build_total_line_columns;
1311 -- ----------------------------------------------
1312 -- --------------------------------------------------------
1313 PROCEDURE populate_temp_table
1314 --
1315 IS
1316 --
1317 l_module_name VARCHAR2(200);
1318 -- ----------------------------------------------
1319 BEGIN
1320 l_module_name := g_module_name || 'populate_temp_table';
1321 --
1322 IF parm_run_mode = 'T' THEN
1323 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1324 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START POPULATE_TEMP_TABLE');
1325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- '||C_SF133_LINE_NUMBER
1326 ||' ('||o_sf133_column_number||')'
1327 ||' ('||o_sf133_column_amount||')'
1328 ||' ('||o_sf133_amt_not_shown||')');
1329 END IF;
1330 END IF;
1331 --
1332 -- ------------------------------------
1333 -- Insert into Line Column Table
1334 -- ------------------------------------
1335 INSERT
1336 INTO fv_sf133_definitions_cols_temp
1337 (sf133_fund_value,
1338 treasury_symbol_id, --Bug 1575992
1339 sf133_line_id,
1340 sf133_column_number,
1341 sf133_column_amount,
1342 sf133_amount_not_shown)
1343 VALUES(o_sf133_ts_value,
1344 o_sf133_treasury_symbol_id, --Bug 1575992
1345 o_sf133_line_id,
1346 o_sf133_column_number,
1347 o_sf133_column_amount,
1348 o_sf133_amt_not_shown);
1349 --
1350 g_insert_count := g_insert_count + 1;
1351 --
1352 -- ------------------------------------
1353 -- Exceptions
1354 -- ------------------------------------
1355 EXCEPTION
1356 --
1357 WHEN OTHERS THEN
1358 g_error_code := SQLCODE;
1359 g_error_message := SQLERRM;
1360
1361 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1362 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'-- POPULATE_TEMP_TABLE');
1363 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'---- TREASURY SYMBOL:'||O_SF133_TS_VALUE
1364 ||' Line Id:'||o_sf133_line_id
1365 ||' Col:' ||o_sf133_column_number
1366 ||' Amt:' ||o_sf133_column_amount);
1367 --
1368 END populate_temp_table;
1369 -- --------------------------------------------------------
1370
1371
1372 -- --------------------------------------------------------
1373
1374 PROCEDURE GET_BEGIN_ENDING_PERIODS( V_PROCESS_YEAR NUMBER,
1375 V_BEGIN_PERIOD IN OUT NOCOPY NUMBER,
1376 V_END_PERIOD IN OUT NOCOPY NUMBER )
1377 IS
1378 l_module_name VARCHAR2(200);
1379 BEGIN
1380 l_module_name := g_module_name || 'GET_BEGIN_ENDING_PERIODS';
1381
1382 SELECT MIN(PERIOD_NUM)
1383 INTO V_BEGIN_PERIOD
1384 FROM gl_period_statuses
1385 WHERE set_of_books_id = parm_set_of_books_id
1386 AND period_year = V_PROCESS_YEAR
1387 AND adjustment_period_flag = 'N'
1388 AND application_id = '101' ;
1389
1390 SELECT MAX(PERIOD_NUM)
1391 INTO V_END_PERIOD
1392 FROM gl_period_statuses
1393 WHERE set_of_books_id = parm_set_of_books_id
1394 AND period_year = V_PROCESS_YEAR
1395 AND closing_status in ('C','O')
1396 AND application_id = '101' ;
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 g_error_code := SQLCODE;
1400 g_error_message := SQLERRM;
1401 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', g_error_message) ;
1402 RAISE ;
1403
1404 END GET_BEGIN_ENDING_PERIODS ;
1405
1406 PROCEDURE populate_gtt_with_ccid
1407 (
1408 p_treasury_symbol_id NUMBER
1409 )
1410 IS
1411 l_module_name VARCHAR2(200);
1412
1413 TYPE t_seg_str_table IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
1414 TYPE t_seg_name_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1415
1416 v_seg t_seg_name_table;
1417 v_seg_str t_seg_str_table;
1418 v_statement VARCHAR2(25000);
1419 v_insert_statement VARCHAR2(30000);
1420
1421 CURSOR crec_cursor
1422 (
1423 p_sobid NUMBER
1424 ) IS
1425 SELECT fsda.sf133_line_acct_id,
1426 fsda.sf133_line_id,
1427 fsdl.sf133_fund_category,
1428 fsda.segment1,
1429 fsda.segment2,
1430 fsda.segment3,
1431 fsda.segment4,
1432 fsda.segment5,
1433 fsda.segment6,
1434 fsda.segment7,
1435 fsda.segment8,
1436 fsda.segment9,
1437 fsda.segment10,
1438 fsda.segment11,
1439 fsda.segment12,
1440 fsda.segment13,
1441 fsda.segment14,
1442 fsda.segment15,
1443 fsda.segment16,
1444 fsda.segment17,
1445 fsda.segment18,
1446 fsda.segment19,
1447 fsda.segment20,
1448 fsda.segment21,
1449 fsda.segment22,
1450 fsda.segment23,
1451 fsda.segment24,
1452 fsda.segment25,
1453 fsda.segment26,
1454 fsda.segment27,
1455 fsda.segment28,
1456 fsda.segment29,
1457 fsda.segment30
1458 FROM fv_sf133_definitions_accts fsda,
1459 fv_sf133_definitions_lines fsdl
1460 WHERE fsdl.sf133_line_id = fsda.sf133_line_id
1461 AND fsdl.set_of_books_id=p_sobid
1462 ORDER BY 2,1;
1463
1464 CURSOR flex_cursor
1465 (
1466 p_chart_of_accounts_id NUMBER
1467 )
1468 IS
1469 SELECT application_column_name ,
1470 flex_value_set_id
1471 FROM fnd_id_flex_segments
1472 WHERE id_flex_code = 'GL#'
1473 AND id_flex_num = p_chart_of_accounts_id;
1474
1475 CURSOR child_value_cursor
1476 (
1477 p_seg VARCHAR2,
1478 p_sid NUMBER
1479 ) IS
1480 SELECT child_flex_value_low,
1481 child_flex_value_high
1482 FROM fnd_flex_value_hierarchies
1483 WHERE parent_FLEX_value = p_seg
1484 AND flex_value_set_id = p_sid;
1485
1486 child_rec child_value_cursor%ROWTYPE;
1487
1488 l_and VARCHAR2(5);
1489 l_child VARCHAR2(32000);
1490 l_no_of_child NUMBER;
1491 l_no_of_seg NUMBER;
1492 l_segno NUMBER;
1493 l_cnt NUMBER;
1494
1495 BEGIN
1496 l_module_name := g_module_name || 'populate_gtt_with_ccid';
1497
1498 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1499 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Entering Module '||l_module_name);
1500 END IF;
1501
1502 FOR crec_rec IN crec_cursor (parm_set_of_books_id) LOOP
1503
1504 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1505 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'sf133_line_acct_id = '||crec_rec.sf133_line_acct_id);
1506 END IF;
1507
1508 v_seg(1) := crec_rec.segment1;
1509 v_seg(2) := crec_rec.segment2;
1510 v_seg(3) := crec_rec.segment3;
1511 v_seg(4) := crec_rec.segment4;
1512 v_seg(5) := crec_rec.segment5;
1513 v_seg(6) := crec_rec.segment6;
1514 v_seg(7) := crec_rec.segment7;
1515 v_seg(8) := crec_rec.segment8;
1516 v_seg(9) := crec_rec.segment9;
1517 v_seg(10) := crec_rec.segment10;
1518 v_seg(11) := crec_rec.segment11;
1519 v_seg(12) := crec_rec.segment12;
1520 v_seg(13) := crec_rec.segment13;
1521 v_seg(14) := crec_rec.segment14;
1522 v_seg(15) := crec_rec.segment15;
1523 v_seg(16) := crec_rec.segment16;
1524 v_seg(17) := crec_rec.segment17;
1525 v_seg(18) := crec_rec.segment18;
1526 v_seg(19) := crec_rec.segment19;
1527 v_seg(20) := crec_rec.segment20;
1528 v_seg(21) := crec_rec.segment21;
1529 v_seg(22) := crec_rec.segment22;
1530 v_seg(23) := crec_rec.segment23;
1531 v_seg(24) := crec_rec.segment24;
1532 v_seg(25) := crec_rec.segment25;
1533 v_seg(26) := crec_rec.segment26;
1534 v_seg(27) := crec_rec.segment27;
1535 v_seg(28) := crec_rec.segment28;
1536 v_seg(29) := crec_rec.segment29;
1537 v_seg(30) := crec_rec.segment30;
1538
1539 v_statement := NULL;
1540
1541 FOR i IN 1 ..30 LOOP
1542 v_seg_str(i) := NULL;
1543 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1544 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_seg('||i||')='||v_seg(i));
1545 END IF;
1546 END LOOP;
1547
1548 l_no_of_seg := 0;
1549
1550 FOR flex_rec IN flex_cursor (g_chart_of_accounts_id) LOOP
1551 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1552 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'application_column_name = '||flex_rec.application_column_name);
1553 END IF;
1554 l_no_of_child := 0;
1555 l_and := NULL;
1556
1557 /* check the segment values is parent */
1558 l_segno := SUBSTR(flex_rec.application_column_name,8,2);
1559 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1560 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_segno = '||l_segno);
1561 END IF;
1562
1563 IF (v_seg(l_segno) IS NOT NULL) THEN
1564 SELECT COUNT(*)
1565 INTO l_cnt
1566 FROM fnd_flex_value_hierarchies
1567 WHERE parent_flex_value = v_seg(l_segno)
1568 AND flex_value_set_id = flex_rec.flex_value_set_id;
1569
1570 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1571 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt = '||l_cnt);
1572 END IF;
1573
1574 OPEN child_value_cursor(v_seg(l_segno) , flex_rec.flex_value_set_id);
1575
1576 IF (l_cnt > 0) THEN
1577 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1578 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt > 0');
1579 END IF;
1580
1581 l_and := NULL;
1582
1583 IF (l_no_of_seg > 0) THEN
1584 l_and := ' AND ';
1585 END IF;
1586
1587 l_child := l_and || ' ( ';
1588
1589 LOOP
1590 FETCH child_value_cursor INTO child_rec;
1591 EXIT WHEN child_value_cursor%NOTFOUND ;
1592
1593 IF (l_no_of_child > 0) THEN
1594 l_child := l_child || ' OR ';
1595 END IF;
1596
1597 l_child := l_child ||
1598 flex_rec.application_column_name ||
1599 ' between '||
1600 '''' ||
1601 child_rec.child_flex_value_low ||
1602 ''' and ''' ||
1603 child_rec.child_flex_value_high ||
1604 '''' ||
1605 fnd_global.local_chr(10);
1606 l_no_of_child := l_no_of_child + 1;
1607 END LOOP;
1608
1609 l_child := l_child || ' )' ;
1610 l_and := NULL;
1611 v_statement := v_statement || l_and || l_child || fnd_global.local_chr(10);
1612
1613 ELSE
1614 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1615 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt not > 0');
1616 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_seg='||l_no_of_seg);
1617 END IF;
1618 IF (l_no_of_seg > 0) THEN
1619 l_and := ' AND ';
1620 END IF;
1621 v_statement := v_statement || l_and ||
1622 flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
1623 END IF; --cnt > 0
1624
1625
1626 CLOSE child_value_cursor;
1627 l_no_of_seg := l_no_of_seg + 1;
1628
1629 END IF; --v_seg(l_segno) IS NOT NULL
1630
1631 END LOOP; --FLEX_CURSOR
1632
1633 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1634 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_statement = '||v_statement);
1635 END IF;
1636
1637 IF (v_statement IS NOT NULL) THEN
1638 v_insert_statement := 'INSERT INTO fv_sf133_ccids_gt
1639 (
1640 sf133_line_acct_id,
1641 ccid
1642 )
1643 SELECT :b_sf133_line_acct_id,
1644 gcc.code_combination_id
1645 FROM gl_code_combinations gcc,
1646 fv_fund_parameters FFP
1647 WHERE gcc.' || v_balance_column_name ||' = ffp.fund_value
1648 AND ffp.treasury_symbol_id = :b_treasury_symbol_id
1649 AND ffp.set_of_books_id = :b_set_of_books_id
1650 AND fund_category like nvl(:b_sf133_line_category, ' || '''' ||'%' || ''''||')
1651 AND '|| v_statement || '
1652 AND gcc.template_id is null
1653 AND gcc.chart_of_accounts_id = :b_chart_of_accounts_id
1654 AND NOT EXISTS (SELECT 1
1655 FROM fv_sf133_ccids_gt fct
1656 WHERE fct.sf133_line_acct_id = :b_sf133_line_acct_id
1657 AND fct.ccid = gcc.code_combination_id)';
1658
1659
1660 EXECUTE IMMEDIATE v_insert_statement
1661 USING crec_rec.sf133_line_acct_id,
1662 p_treasury_symbol_id,
1663 parm_set_of_books_id,
1664 crec_rec.sf133_fund_category,
1665 g_chart_of_accounts_id,
1666 crec_rec.sf133_line_acct_id;
1667 END IF;
1668 END LOOP; --crec_cursor
1669
1670 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1671 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Exiting Module = '||l_module_name);
1672 END IF;
1673
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 g_error_code := SQLCODE;
1677 g_error_message := SQLERRM;
1678 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception',g_error_message);
1679 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception','-- populate_gtt_with_ccid');
1680 END;
1681 -- + Global Varibale Declaration +
1682 BEGIN
1683 g_module_name := 'fv.plsql.fv_sf133_oneyear.';
1684 END fv_sf133_oneyear;
1685