[Home] [Help]
PACKAGE BODY: APPS.FV_SF133_NOYEAR
Source
1 PACKAGE BODY FV_SF133_NOYEAR AS
2 --$Header: FV133NYB.pls 120.31 2006/07/07 13:37:12 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 parm_application_id NUMBER;
10 parm_set_of_books_id NUMBER;
11 parm_gl_period_year NUMBER;
12 parm_gl_period_num NUMBER;
13 parm_treasury_value_r1 VARCHAR2(35);
14 parm_run_mode VARCHAR2(10);
15
16 -- New Variable declared by Surya on 04/30/98 to receive the value of
17 -- the passed quarter number
18 parm_gl_period_name gl_period_statuses.period_name%TYPE;
19 -- ------------------------------------
20 -- All Pre-build Query Variables
21 -- ------------------------------------
22 g_chart_of_accounts_id gl_ledgers.chart_of_accounts_id%TYPE;
23 g_fund_segment_name VARCHAR2(10);
24 --
25 -- ------------------------------------
26 -- Stored Global Variables
27 -- ------------------------------------
28 g_insert_count NUMBER;
29 --
30 g_error_code NUMBER;
31 g_error_message VARCHAR2(80);
32 --
33 g_period_num NUMBER;
34 g_ts_value_in_process VARCHAR2(25);
35 g_total_start_line_number NUMBER;
36 g_subtotal_start_line_number NUMBER;
37 g_column_number NUMBER;
38 --Added for bug No. 1553099
39 g_currency_code VARCHAR2(15);
40
41 --
42 c_total_balance NUMBER;
43 c_ending_balance NUMBER;
44 c_begin_balance NUMBER;
45 c_begin_select VARCHAR2(200);
46 c_end_select VARCHAR2(200);
47 c_begin_period VARCHAR2(40);
48 c_end_period VARCHAR2(40);
49
50 -- New Variables declared by Narsimha Balakkari.
51
52 c_resource_type fv_treasury_symbols.resource_type%TYPE;
53 c_rescission_flag Varchar2(10);
54
55 -- ---------- Flex Segment Name Cursor Variables ---------
56 c_segment_name fnd_id_flex_segments.segment_name%TYPE;
57 c_flex_column_name fnd_id_flex_segments.application_column_name%TYPE;
58 --
59 v_balance_column_name fnd_id_flex_segments.application_column_name%TYPE;
60 g_seg_value_set_id FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID%TYPE;
61
62 -- ---------- Treasury Symbol Report Line Cursor Vaiables -----------
63 c_sf133_ts_value gl_code_combinations.segment1%TYPE;
64 c_sf133_line_id fv_sf133_definitions_lines.sf133_line_id%TYPE;
65 c_sf133_line_number fv_sf133_definitions_lines.sf133_line_number%TYPE;
66 c_sf133_prev_line_number fv_sf133_definitions_lines.sf133_line_number%TYPE;
67 c_sf133_line_type_code fv_sf133_definitions_lines.sf133_line_type_code%TYPE;
68 c_sf133_natural_bal_type fv_sf133_definitions_lines.sf133_natural_balance_type%TYPE;
69 c_sf133_line_category fv_sf133_definitions_lines.sf133_fund_category%TYPE;
70
71 -- New variable declared by pkpatel to fix Bug 1575992
72 c_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
73 --
74 -- New variable declared by Narsimha.
75 c_sf133_report_line_number fv_sf133_definitions_lines.
76 sf133_report_line_number%TYPE;
77
78 -- ---------- Balance Type Cursor Vaiables ---------
79 c_sf133_line_acct_id fv_sf133_definitions_accts.sf133_line_acct_id%TYPE;
80 c_sf133_balance_type fv_sf133_definitions_accts.sf133_balance_type%TYPE;
81
82 -- New variables declared by Narsimha.
83
84 c_sf133_apportion_amt number;
85 c_sf133_additional_info fv_sf133_definitions_accts
86 .sf133_additional_info%TYPE;
87
88 -- ---------- Treasury Symbol Accummulation Cursor Vaiables ---------
89 c_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
90 c_sf133_amount_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
91 --
92 -- ---------- Output Report Line Column Data -------------
93 o_sf133_ts_value fv_sf133_definitions_cols_temp.sf133_fund_value%TYPE;
94 o_sf133_line_id fv_sf133_definitions_cols_temp.sf133_line_id%TYPE;
95 o_sf133_column_number fv_sf133_definitions_cols_temp.sf133_column_number%TYPE;
96 o_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
97 o_sf133_amt_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
98
99 -- New variable declared by pkpatel to fix Bug 1575992
100 o_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
101
102 --New variables
103 v_select VARCHAR2(8600);
104 v_cursor_id INTEGER;
105 parm_tsymbol_id NUMBER;
106
107 --
108 -- ---------- Define Segment Name Cursor -----------------
109 CURSOR flex_field_column_name_cursor
110 IS
111 SELECT UPPER(glflex.segment_name) segment_name,
112 UPPER(glflex.application_column_name) flex_column_name
113 FROM fnd_id_flex_segments glflex
114 WHERE glflex.application_id = 101
115 AND glflex.id_flex_num = g_chart_of_accounts_id
116 AND glflex.id_flex_code = 'GL#'
117 ORDER BY glflex.application_column_name;
118 --
119 -- ---------- Define Report Treasury Symbol Line Cursor -------------
120 -- Modified by Surya on 1/25/99 to add a join for sob between FTS and line tables.
121 -- Removed the fv_fund_parameters from the query
122
123 CURSOR ts_report_line_cursor
124 IS
125 SELECT DISTINCT
126 FTS.treasury_symbol sf133_ts_value,
127 FTS.treasury_symbol_id sf133_treasury_symbol_id, --Added for Bug 1575992
128 line.sf133_line_id sf133_line_id,
129 line.sf133_line_number sf133_line_number,
130 line.sf133_line_type_code sf133_line_type_code,
131 line.sf133_natural_balance_type sf133_natural_balance_type,
132 line.sf133_fund_category sf133_line_category,
133 line.sf133_report_line_number sf133_report_line_number
134
135 FROM fv_sf133_definitions_lines line,
136 FV_TREASURY_SYMBOLS FTS
137
138 WHERE FTS.SET_OF_BOOKS_ID = LINE.SET_OF_BOOKS_ID
139 AND FTS.Treasury_symbol = parm_treasury_value_r1
140 AND FTS.set_of_books_id = parm_set_of_books_id
141 AND (line.sf133_line_type_code) IN ('T', 'D', 'S', 'N')
142
143 ORDER BY line.sf133_line_number;
144 --
145 -- ---------- Determine Balance Type of Acct -------------
146 --
147 CURSOR balance_type_cursor
148 IS
149 SELECT sf133_line_acct_id,
150 sf133_balance_type,
151 sf133_additional_info
152 FROM fv_sf133_definitions_accts
153 WHERE sf133_line_id = c_sf133_line_id ;
154
155 --Cursor to find all the Treasury symbols in the input range.
156 --Added a s part of Enh #2129123
157 --
158 CURSOR ts_range_cursor(tsymbol_r1 VARCHAR2, tsymbol_r2 VARCHAR2) IS
159 SELECT treasury_symbol, treasury_symbol_id
160 FROM fv_treasury_symbols
161 WHERE TIME_FRAME IN ('NO_YEAR','MULTIPLE','REVOLVING')
162 AND (FUND_GROUP_CODE NOT BETWEEN '3800' AND '3899')
163 AND (FUND_GROUP_CODE NOT BETWEEN '6001' AND '6999')
164 AND treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
165 AND set_of_books_id = parm_set_of_books_id
166 ORDER BY treasury_symbol;
167 --
168 --
169 PROCEDURE determine_acct_flex_segments;
170 --
171 PROCEDURE purge_temp_table;
172 --
173 PROCEDURE build_report_lines;
174 PROCEDURE build_fiscal_line_columns;
175 PROCEDURE build_total_line_columns;
176 --
177 PROCEDURE populate_temp_table;
178 PROCEDURE populate_gtt_with_ccid ( p_treasury_symbol_id number );
179 --
180 abort_error EXCEPTION;
181 --
182 -- ---------- End of Package Level Declaritives -----------------------------
183 --
184 -- ------------------------------------------------------------------
185 PROCEDURE main
186 (errbuf OUT NOCOPY VARCHAR2,
187 retcode OUT NOCOPY NUMBER,
188 run_mode IN VARCHAR2,
189 set_of_books_id IN NUMBER,
190 gl_period_year IN NUMBER,
191 gl_period_name IN VARCHAR2,
192 treasury_symbol_r1 IN VARCHAR2,
193 treasury_symbol_r2 IN VARCHAR2)
194 --
195 IS
196 l_module_name VARCHAR2(200) ;
197 l_req_id NUMBER := 0;
198
199 --
200 BEGIN
201
202 l_module_name := g_module_name || 'main';
203 --
204 -- ------------------------------------
205 -- Store Input Parameters in Global Variables
206 -- ------------------------------------
207 parm_application_id := '101';
208 parm_set_of_books_id := set_of_books_id;
209 parm_gl_period_year := gl_period_year;
210 parm_gl_period_name := gl_period_name;
211 parm_run_mode := UPPER(run_mode);
212
213 --Added for Bug No. 1553099
214
215 select currency_code,
216 chart_of_accounts_id
217 into g_currency_code,
218 g_chart_of_accounts_id
219 from gl_ledgers
220 where ledger_id = parm_set_of_books_id;
221
222 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
223 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- APID('||NVL(PARM_APPLICATION_ID,0) ||')'
224 ||' SoB('||NVL(parm_set_of_books_id,0) ||')'
225 ||' Year('||NVL(parm_gl_period_year,0) ||')'
226 ||' Period('||NVL(parm_gl_period_num,0) ||')'
227 ||' Fund Code('||NVL(parm_treasury_value_r1,'Null')
228 ||')');
229 END IF;
230
231 -- ----------------------------------------
232 -- Initialize Program Row Counts and Variables
233 -- ----------------------------------------
234 g_insert_count := 0;
235 g_error_code := 0;
236 g_error_message := NULL;
237 retcode := 0;
238 errbuf := '';
239 --
240 IF g_error_code = 0 THEN
241 -- ------------------------------------
242 -- Delete All Entries from Report Temp Table
243 -- ------------------------------------
244 purge_temp_table;
245 END IF;
246
247 /*IF g_error_code = 0 THEN
248 populate_gtt_with_ccid;
249 END IF; -- Bug 4655486*/
250
251 IF g_error_code = 0 THEN
252 determine_acct_flex_segments;
253 END IF;
254
255 IF g_error_code = 0 THEN
256 -- ----------------------------------------
257 -- Build Report Lines
258 -- ----------------------------------------
259 FOR ts_rec IN ts_range_cursor(treasury_symbol_r1, treasury_symbol_r2)
260 LOOP
261 parm_treasury_value_r1 := ts_rec.treasury_symbol;
262 parm_tsymbol_id := ts_rec.treasury_symbol_id;
263 populate_gtt_with_ccid(parm_tsymbol_id);
264 --determine_acct_flex_segments;
265
266 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_tsymbol_id);
267 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
268 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING TREASURY SYMBOL... '||PARM_TREASURY_VALUE_R1) ;
269 END IF;
270 build_report_lines;
271 IF g_error_code <> 0 THEN
272 errbuf := errbuf || '
273 Processing for Treasury Symbol '||parm_treasury_value_r1||' FAILED'||
274 g_error_message;
275 ELSE
276
277 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
278 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING SF133 REPORT FOR TS.....'||PARM_TREASURY_VALUE_R1);
279 END IF;
280
281 l_req_id :=
282 FND_REQUEST.SUBMIT_REQUEST ('FV','FVXBGLPN','','',FALSE,parm_set_of_books_id,g_chart_of_accounts_id,
283 parm_gl_period_year,parm_gl_period_name, parm_treasury_value_r1 );
284
285 IF l_req_id = 0 THEN
286 errbuf := 'Error submitting SF133 Report for Treasury Symbol ' || parm_treasury_value_r1;
287 retcode := -1 ;
288 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,ERRBUF) ;
289 return;
290 ELSE
291 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CONCURRENT REQUEST ID FOR SF133 REPORT - ' ||
293 l_req_id);
294 END IF;
295 END IF;
296
297 END IF;
298
299 COMMIT;
300
301 END LOOP;
302 --
303 END IF;
304 --
305 IF g_error_code <> 0 THEN
306 RAISE abort_error;
307 END IF;
308 --
309 IF errbuf IS NULL THEN
310 errbuf := 'Normal End of FVSF133 Package';
311 END IF;
312 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
313 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,ERRBUF);
314 END IF;
315
316 dbms_sql.close_cursor(v_cursor_id);
317 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
318 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- INSERT COUNT('||G_INSERT_COUNT||')');
319 END IF;
320 retcode := g_error_code;
321 errbuf := 'Normal End of FVSF133 Package';
322 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
323 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,ERRBUF);
324 END IF;
325 --
326 -- ------------------------------------
327 -- Exceptions
328 -- ------------------------------------
329 EXCEPTION
330 --
331 WHEN abort_error THEN
332 retcode := g_error_code;
333 errbuf := g_error_message;
334 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,ERRBUF);
335 --
336 WHEN OTHERS THEN
337 retcode := SQLCODE;
338 errbuf := SQLERRM;
339 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,errbuf);
340 RAISE_APPLICATION_ERROR(-20222,
341 'FVSF133 Exception-'||SQLERRM);
342 --
343 END main;
344 -- ------------------------------------------------------------------
345 -- --------------------------------------------------------
346
347 -- ------------------------------------------------------------------
348 -- --------------------------------------------------------
349 PROCEDURE determine_acct_flex_segments
350 --
351 AS
352 l_module_name VARCHAR2(200) ;
353
354 -- for data access security
355 das_id NUMBER;
356 das_where VARCHAR2(600);
357 --
358 BEGIN
359 l_module_name := g_module_name || 'determine_acct_flex_segments';
360 --
361 IF parm_run_mode = 'T' THEN
362 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
363 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START DETERMINE_ACCT_FLEX_SEGMENTS');
364 END IF;
365 END IF;
366 --
367 -- -------------------------------------
368 -- Store SoB's Chart of Accounts Id
369 -- -------------------------------------
370 SELECT chart_of_accounts_id
371 INTO g_chart_of_accounts_id
372 FROM gl_ledgers
373 WHERE ledger_id = parm_set_of_books_id;
374 --
375 -- -------------------------------------
376 -- Store Flex Segment Names in Table
377 -- -------------------------------------
378 SELECT application_column_name
379 INTO v_balance_column_name
380 FROM fnd_segment_attribute_values
381 WHERE application_id = 101
382 AND id_flex_code = 'GL#'
383 AND id_flex_num = g_chart_of_accounts_id
384 AND segment_attribute_type = 'GL_BALANCING'
385 AND attribute_value = 'Y';
386
387
388 /* Used dynamic SQL instead of balance_cursor to improve performance */
389
390 v_select := 'SELECT decode(:cv_balance_type, ' ||
391 ''''|| 'B' || '''' || ',' || '
392 ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
393 NVL(glbal.begin_balance_cr,0)
394 ),0),2),' ||
395 ''''|| 'E' || '''' || ',' || '
396 ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
397 NVL(glbal.begin_balance_cr,0))
398 + (NVL(glbal.period_net_dr,0) -
399 NVL(glbal.period_net_cr,0))),0),2),'||
400 ''''|| 'P' || '''' || ',' || '
401 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
402 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),-1,0,
403 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
404 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),'||
405 ''''|| 'N' || '''' || ',' || '
406 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
407 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),1,0,
408 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
409 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2))) '|| '
410 FROM gl_balances glbal,
411 gl_code_combinations glcc,
412 fv_sf133_definitions_accts acct,
413 fv_sf133_ccids_gt fscg,
414 fv_fund_parameters FFP
415 WHERE glbal.ledger_id = :cv_sob_id
416 AND glbal.period_year = :cv_period_year
417 AND glbal.period_num = :cv_period
418 AND glbal.currency_code = :cv_currency_code
419 AND glbal.actual_flag = '||''''||'A'||''''||'
420 AND glcc.chart_of_accounts_id = :cv_coa_id
421 AND glbal.code_combination_id = glcc.code_combination_id
422 AND acct.sf133_line_id = :cv_sf133_line_id
423 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
424 AND glcc.template_id is null
425 AND fscg.ccid = glcc.code_combination_id
426 AND fscg.sf133_line_acct_id = acct.sf133_line_acct_id
427 AND glcc.' || v_balance_column_name ||' = FFP.fund_value
428 AND FFP.treasury_symbol_id = :cv_treasury_symbol_id
429 AND FFP.set_of_books_id = :cv_sob_id
430 AND fund_category like nvl(:cv_sf133_line_category, ' || '''' ||'%' || ''''||')';
431
432 -- Data Access Security:
433 das_id := fnd_profile.value('GL_ACCESS_SET_ID');
434 das_where := gl_access_set_security_pkg.get_security_clause
435 (das_id, gl_access_set_security_pkg.READ_ONLY_ACCESS,
436 gl_access_set_security_pkg.CHECK_LEDGER_ID,
437 to_char(parm_set_of_books_id), null,
438 gl_access_set_security_pkg.CHECK_SEGVALS,
439 null, 'glcc', null);
440 IF (das_where IS NOT NULL) THEN
441 v_select := v_select || '
442 AND ' || das_where;
443 END IF;
444
445 /*
446 FOR flex_field_column_name_entry IN flex_field_column_name_cursor LOOP
447 EXIT WHEN flex_field_column_name_cursor%NOTFOUND;
448 c_segment_name := flex_field_column_name_entry.segment_name;
449 c_flex_column_name := flex_field_column_name_entry.flex_column_name;
450
451 BEGIN
452 SELECT flex_value_set_id
453 INTO g_seg_value_set_id
454 FROM fnd_id_flex_segments
455 WHERE application_column_name = c_flex_column_name
456 AND application_id = 101
457 AND id_flex_code = 'GL#'
458 AND id_flex_num = g_chart_of_accounts_id;
459 EXCEPTION
460 WHEN OTHERS THEN
461 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Error in getting the Value set attched '
462 || ' to the segemnt => ' || c_flex_column_name);
463 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' SQLCODE => ' || SQLCODE ||
464 ' SQLERRM => ' || SQLERRM);
465 RAISE;
466 END;
467 -- + Rollup for the amount is the segment is a parent segment +
468 v_select := v_select || '
469 AND ( NVL(glcc.'|| c_flex_column_name ||
470 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
471 ||',NVL(glcc.'||c_flex_column_name ||
472 ','||''''||'-1'||''''||')) ' || '
473 OR glcc.'||c_flex_column_name ||' IN (SELECT flex_value '||
474 'FROM fnd_flex_values ffv, fnd_flex_value_hierarchies ffvh '||
475 'WHERE ffv.flex_value BETWEEN ffvh.child_flex_value_low
476 AND ffvh.child_flex_value_high
477 AND ffv.flex_value_set_id = ' || g_seg_value_set_id ||
478 ' AND ffv.flex_value_set_id = ffvh.flex_value_set_id'||
479 ' AND parent_flex_value = acct.' || c_flex_column_name || '))';
480 -- + commented the below code to roll up the amount for all segments +
481 v_select := v_select || '
482 AND NVL(glcc.'|| c_flex_column_name ||
483 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
484 ||',NVL(glcc.'||c_flex_column_name ||
485 ','||''''||'-1'||''''||'))';
486
487 IF c_flex_column_name = v_balance_column_name THEN
488 -- the segment application_column_name being processed = the balancing
489 -- segment application_column_name.
490 g_fund_segment_name := c_flex_column_name;
491 END IF;
492 --
493 END LOOP;
494 */
495
496 v_cursor_id := dbms_sql.open_cursor;
497 dbms_sql.parse(v_cursor_id, v_select, dbms_sql.v7);
498 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
499
500 dbms_sql.bind_variable(v_cursor_id, ':cv_sob_id', parm_set_of_books_id);
501 dbms_sql.bind_variable(v_cursor_id, ':cv_period_year', parm_gl_period_year);
502 dbms_sql.bind_variable(v_cursor_id, ':cv_currency_code', g_currency_code);
503 dbms_sql.bind_variable(v_cursor_id, ':cv_coa_id', g_chart_of_accounts_id);
504
505 --
506
507 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
508 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,V_SELECT);
509 END IF;
510 --
511 -- ------------------------------------
512 -- Exceptions
513 -- ------------------------------------
514 EXCEPTION
515 --
516 WHEN OTHERS THEN
517 IF flex_field_column_name_cursor%ISOPEN THEN
518 close flex_field_column_name_cursor;
519 END IF;
520 g_error_code := SQLCODE;
521 g_error_message := 'determine_acct_flex_segments/'||SQLERRM;
522 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
523 --
524 END determine_acct_flex_segments;
525 -- --------------------------------------------------------
526 -- --------------------------------------------------------
527 PROCEDURE purge_temp_table
528 --
529 IS
530 l_module_name VARCHAR2(200) ;
531 --
532 BEGIN
533 l_module_name := g_module_name || 'purge_temp_table';
534 --
535 IF parm_run_mode = 'T' THEN
536 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
537 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START PURGE_TEMP_TABLE');
538 END IF;
539 END IF;
540 --
541 DELETE
542 FROM fv_sf133_definitions_cols_temp
543 WHERE (sf133_line_id)
544 IN
545 (SELECT sf133_line_id
546 FROM fv_sf133_definitions_lines
547 WHERE set_of_books_id = parm_set_of_books_id);
548 --
549 COMMIT;
550 --
551 -- ------------------------------------
552 -- Exceptions
553 -- ------------------------------------
554 EXCEPTION
555 --
556 WHEN NO_DATA_FOUND THEN
557 NULL;
558 --
559 WHEN OTHERS THEN
560 g_error_code := SQLCODE;
561 g_error_message := 'purge_temp_table/'||SQLERRM;
562 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
563 --
564 END purge_temp_table;
565 -- --------------------------------------------------------
566 -- --------------------------------------------------------
567 PROCEDURE build_report_lines
568 --
569 AS
570 l_module_name VARCHAR2(200) ;
571 --
572 -- ----------------------------------------
573 BEGIN
574 l_module_name := g_module_name || 'build_report_lines';
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 BUILD_REPORT_LINES');
579 END IF;
580 END IF;
581 --
582 -- ----------------------------------------
583 -- Find first period_number that is not an adjusting period
584 -- ----------------------------------------
585 --
586 --
587 SELECT min(period_num)
588 INTO g_period_num
589 FROM gl_period_statuses
590 WHERE ledger_id = parm_set_of_books_id
591 AND period_year = parm_gl_period_year
592 AND adjustment_period_flag = 'N'
593 AND application_id = '101' ;
594
595
596 -- Added on 4/28/98 by Surya Padmanabhan
597 -- Get the Period Number For the Quarter
598 SELECT PERIOD_NUM
599 INTO parm_gl_period_num
600 FROM GL_PERIOD_STATUSES
601 WHERE LEDGER_ID = parm_set_of_books_id AND
602 PERIOD_YEAR = parm_gl_period_year AND
603 APPLICATION_ID = '101' AND
604 CLOSING_STATUS in ('O','C') AND
605 PERIOD_NAME = parm_gl_period_name;
606
607 -- ----------------------------------------------------
608 -- Get Next SF133 Treasury Symbol Line from Cursor
609 -- ----------------------------------------------------
610 --
611 g_ts_value_in_process := NULL;
612 --
613 FOR ts_report_line_entry IN ts_report_line_cursor LOOP
614 --
615 c_sf133_ts_value := ts_report_line_entry.sf133_ts_value;
616 c_sf133_line_id := ts_report_line_entry.sf133_line_id;
617 c_sf133_line_number := ts_report_line_entry.sf133_line_number;
618 c_sf133_line_type_code := ts_report_line_entry.sf133_line_type_code;
619 c_sf133_natural_bal_type := ts_report_line_entry.sf133_natural_balance_type;
620 c_sf133_line_category := ts_report_line_entry.sf133_line_category;
621 c_sf133_report_line_number := ts_report_line_entry.sf133_report_line_number;
622 c_sf133_treasury_symbol_id := ts_report_line_entry.sf133_treasury_symbol_id; --added for 1575992
623
624 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
625 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_category',c_sf133_line_category);
626 --
627 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_SF133_LINE_CATEGORY = '||C_SF133_LINE_CATEGORY);
629 END IF;
630 IF g_error_code = 0 THEN
631 IF c_sf133_line_type_code = 'D' THEN
632 g_column_number := 1;
633 build_fiscal_line_columns;
634 ELSE
635 g_column_number := 1;
636 build_total_line_columns;
637 END IF;
638 --
639 IF g_error_code = 0 THEN
640 IF g_ts_value_in_process IS NULL
641 OR g_ts_value_in_process <> c_sf133_ts_value THEN
642 g_ts_value_in_process := c_sf133_ts_value;
643 g_total_start_line_number := 0;
644 g_subtotal_start_line_number := 0;
645 ELSE
646 IF c_sf133_line_type_code IN ('T', 'S', 'N') THEN
647 g_subtotal_start_line_number := c_sf133_line_number;
648 IF c_sf133_line_type_code = 'T' THEN
649 g_total_start_line_number := c_sf133_line_number;
650 ELSE
651 g_total_start_line_number := c_sf133_prev_line_number;
652 END IF;
653 END IF;
654 END IF;
655 END IF;
656 END IF;
657 c_sf133_prev_line_number := ts_report_line_entry.sf133_line_number;
658 --
659 END LOOP;
660 --
661 -- ------------------------------------
662 -- Exceptions
663 -- ------------------------------------
664 EXCEPTION
665 --
666 WHEN OTHERS THEN
667 IF ts_report_line_cursor%ISOPEN THEN
668 close ts_report_line_cursor;
669 END IF;
670 g_error_code := SQLCODE;
671 g_error_message := SQLERRM;
672 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_REPORT_LINES');
673 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
674 --
675 END build_report_lines;
676 -- --------------------------------------------------------
677 -- ----------------------------------------------
678 PROCEDURE build_fiscal_line_columns
679 --
680 IS
681 l_module_name VARCHAR2(200);
682 --
683 -- ----------------------------------------------
684 l_ignore INTEGER;
685
686 BEGIN
687 l_module_name := g_module_name || 'build_fiscal_line_columns';
688 --
689 IF parm_run_mode = 'T' THEN
690 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'START BUILD_FISCAL_LINE_COLUMNS');
692 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- LINE('||C_SF133_LINE_NUMBER||')'
693 || ' Tresury Symbol('||c_sf133_ts_value ||')'
694 || ' '||to_char(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
695 END IF;
696 END IF;
697 --
698 -- ----------------------------------------
699 -- Get Fund Accummulation
700 -- ----------------------------------------
701 c_total_balance := 0;
702 c_sf133_amount_not_shown := 0;
703 c_begin_balance := 0;
704 c_ending_balance := 0;
705
706 c_begin_period := g_period_num;
707 c_end_period := parm_gl_period_num;
708
709 -- for the line find all accounts and sum
710
711
712 FOR balance_type_rec in balance_type_cursor LOOP
713 c_sf133_line_acct_id := balance_type_rec.sf133_line_acct_id;
714 c_sf133_balance_type := balance_type_rec.sf133_balance_type;
715 c_sf133_additional_info := balance_type_rec.sf133_additional_info;
716
717 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
718
719 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
720 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BALANCE_TYPE = '||C_SF133_BALANCE_TYPE);
721 END IF;
722
723 -- New code by Narsimha for rescission.
724
725 c_rescission_flag := 'FALSE';
726 IF upper(c_sf133_additional_info) = 'RESCISSION' THEN
727 select upper(resource_type) into c_resource_type
728 from fv_treasury_symbols
729 where treasury_symbol = parm_treasury_value_r1
730 and set_of_books_id = parm_set_of_books_id;
731 IF c_resource_type like '%APPROPRIATION%' THEN
732 IF ltrim(rtrim(c_sf133_report_line_number)) = '1A' THEN
733 c_rescission_flag := 'TRUE';
734 ELSE
735 c_rescission_flag := 'FALSE';
736 END IF;
737 ELSIF c_resource_type like '%BORROWING%' THEN
738 IF ltrim(rtrim(c_sf133_report_line_number)) = '1B' THEN
739 c_rescission_flag := 'TRUE';
740 ELSE
741 c_rescission_flag := 'FALSE';
742 END IF;
743 ELSIF c_resource_type like '%CONTRACT%' THEN
744 IF ltrim(rtrim(c_sf133_report_line_number)) = '1C' THEN
745 c_rescission_flag := 'TRUE';
746 ELSE
747 c_rescission_flag := 'FALSE';
748 END IF;
749 END IF;
750 ELSE
751 c_rescission_flag := 'TRUE';
752 END IF;
753 IF c_rescission_flag = 'TRUE' THEN
754 IF c_sf133_balance_type = 'E' THEN
755 -- ending balance type
756 dbms_sql.bind_variable(v_cursor_id, ':cv_balance_type','E');
757 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
758 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
759 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
760 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
761 END IF;
762 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
763 ELSIF c_sf133_balance_type = 'B' THEN
764 -- beginning balance type
765 dbms_sql.bind_variable(v_cursor_id, ':cv_balance_type','B');
766 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_begin_period);
767 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
768 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
770 END IF;
771 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
772 ELSIF c_sf133_balance_type = 'P' THEN
773 dbms_sql.bind_variable(v_cursor_id, ':cv_balance_type','P');
774 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
775 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
776 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
777 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
778 END IF;
779 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
780 ELSIF c_sf133_balance_type = 'N' THEN
781 dbms_sql.bind_variable(v_cursor_id, ':cv_balance_type','N');
782 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
783 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
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, 'l_ignore := '||l_ignore);
786 END IF;
787 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
788 ELSIF c_sf133_balance_type in ('D','S') THEN
789 dbms_sql.bind_variable(v_cursor_id, ':cv_balance_type','E');
790 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_end_period);
791 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
792 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
793 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
794 END IF;
795 dbms_sql.column_value(v_cursor_id, 1, c_ending_balance);
796 dbms_sql.bind_variable(v_cursor_id, ':cv_balance_type','B');
797 dbms_sql.bind_variable(v_cursor_id,':cv_period',c_begin_period);
798 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
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,'l_ignore := '||l_ignore);
801 END IF;
802 dbms_sql.column_value(v_cursor_id, 1, c_begin_balance);
803
804 IF c_sf133_balance_type = 'D' THEN
805 c_total_balance := c_ending_balance - c_begin_balance;
806 ELSIF c_sf133_balance_type = 'S' THEN
807 c_total_balance := c_begin_balance - c_ending_balance;
808 END IF;
809 END IF;
810 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
811 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_TOTAL_BALANCE = '||C_TOTAL_BALANCE);
812 END IF;
813
814 END IF;
815 -- sum the line amount
816 c_sf133_amount_not_shown := c_sf133_amount_not_shown + c_total_balance;
817 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
818 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMT NOT SHOWN = '||C_SF133_AMOUNT_NOT_SHOWN);
819 END IF;
820
821 END LOOP;
822 --dbms_sql.close_cursor(v_cursor_id);
823 --
824 -- set up correct display sign
825 --
826 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
827 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NATURAL BAL TYPE = '||C_SF133_NATURAL_BAL_TYPE);
828 END IF;
829 IF c_sf133_natural_bal_type = 'C' THEN
830 -- Credit so display opposite
831 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
832
833 ELSIF c_sf133_natural_bal_type = 'D' THEN
834 -- Debit so display as is
835 c_sf133_column_amount := c_sf133_amount_not_shown;
836
837 ELSIF c_sf133_natural_bal_type = 'A' THEN
838 -- Display the absolute value
839 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
840
841 ELSIF c_sf133_natural_bal_type = 'N' THEN
842 -- Display as negative
843 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
844
845 END IF;
846
847 --
848 o_sf133_ts_value := c_sf133_ts_value;
849 o_sf133_line_id := c_sf133_line_id;
850 o_sf133_column_number := g_column_number;
851 o_sf133_column_amount := c_sf133_column_amount;
852 o_sf133_amt_not_shown := c_sf133_amount_not_shown;
853 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --added for 1575992
854
855 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
856 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COL AMT ='||O_SF133_COLUMN_AMOUNT);
857 END IF;
858
859
860 populate_temp_table;
861 --
862 -- ------------------------------------
863 -- Exceptions
864 -- ------------------------------------
865 EXCEPTION
866 --
867 --
868 WHEN OTHERS THEN
869
870 g_error_code := SQLCODE;
871 g_error_message := SQLERRM;
872
873 IF balance_type_cursor%ISOPEN THEN
874 close balance_type_cursor;
875 ELSIF dbms_sql.is_open(v_cursor_id) THEN
876 dbms_sql.close_cursor(v_cursor_id);
877 END IF;
878
879 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_FISCAL_LINE_COLUMNS:'||G_ERROR_MESSAGE);
880 --
881 END build_fiscal_line_columns;
882 -- ----------------------------------------------
883 -- ----------------------------------------------
884 PROCEDURE build_total_line_columns
885 --
886 IS
887 l_module_name VARCHAR2(200) ;
888 --
889 -- ----------------------------------------------
890 BEGIN
891 l_module_name := g_module_name || 'build_total_line_columns';
892 --
893 IF parm_run_mode = 'T' THEN
894 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
895 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_TOTAL_LINE_COLUMNS');
896 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- LINE='||C_SF133_LINE_NUMBER
897 ||' Start Total Line('||g_total_start_line_number||')'
898 || ' Treasury Symbol('||c_sf133_ts_value||')');
899 END IF;
900 END IF;
901 --
902 -- ----------------------------------------
903 -- Get Treasury Symbol Accummulation for Total using column with true sign.
904 -- ----------------------------------------
905 SELECT NVL(SUM(NVL(sf133_amount_not_shown,0)),0)
906 INTO c_sf133_amount_not_shown
907 FROM fv_sf133_definitions_cols_temp
908 WHERE sf133_column_number = g_column_number
909 AND sf133_fund_value = c_sf133_ts_value
910 AND (sf133_line_id)
911 IN
912 (SELECT sf133_line_id
913 FROM fv_sf133_definitions_lines
914 WHERE set_of_books_id = parm_set_of_books_id
915 AND sf133_line_number >
916 DECODE(c_sf133_line_type_code, 'T', g_total_start_line_number, g_subtotal_start_line_number)
917 AND sf133_line_number < c_sf133_line_number);
918 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
919 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SRART ' || G_TOTAL_START_LINE_NUMBER);
920 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END: ' || C_SF133_LINE_NUMBER);
921 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMTNS: ' || C_SF133_AMOUNT_NOT_SHOWN);
922 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NATURAL BAL TYPE = '||C_SF133_NATURAL_BAL_TYPE);
923 END IF;
924
925 IF c_sf133_natural_bal_type = 'C' THEN
926 -- Credit so display opposite
927 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
928
929 ELSIF c_sf133_natural_bal_type = 'D' THEN
930 -- Debit so display as is
931 c_sf133_column_amount := c_sf133_amount_not_shown;
932
933 ELSIF c_sf133_natural_bal_type = 'A' THEN
934 -- Display the absolute value
935 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
936
937 ELSIF c_sf133_natural_bal_type = 'N' THEN
938 -- Display as negative
939 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
940
941 END IF;
942
943 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
944 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BALT: ' || C_SF133_NATURAL_BAL_TYPE);
945 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMT: ' || C_SF133_COLUMN_AMOUNT);
946 END IF;
947
948 --
949 -- ------------------------------------
950 -- Insert Report Column
951 -- ------------------------------------
952 o_sf133_ts_value := c_sf133_ts_value;
953 o_sf133_line_id := c_sf133_line_id;
954 o_sf133_column_number := g_column_number;
955 o_sf133_column_amount := c_sf133_column_amount;
956 o_sf133_amt_not_shown := c_sf133_amount_not_shown; -- Bug # 2896450
957 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --added for 1575992
958 populate_temp_table;
959 --
960 -- ------------------------------------
961 -- Exceptions
962 -- ------------------------------------
963 EXCEPTION
964 --
965 WHEN OTHERS THEN
966 g_error_code := SQLCODE;
967 g_error_message := SQLERRM;
968 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_TOTAL_LINE_COLUMNS');
969 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message );
970 --
971 END build_total_line_columns;
972 -- ----------------------------------------------
973 -- --------------------------------------------------------
974 PROCEDURE populate_temp_table
975 --
976 IS
977 l_module_name VARCHAR2(200) ;
978 --
979 -- ----------------------------------------------
980 BEGIN
981 l_module_name := g_module_name || 'populate_temp_table';
982 --
983 IF parm_run_mode = 'T' THEN
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,'START POPULATE_TEMP_TABLE');
986 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- '||C_SF133_LINE_NUMBER
987 ||' ('||o_sf133_column_number||')'
988 ||' ('||o_sf133_column_amount||')'
989 ||' ('||o_sf133_amt_not_shown||')');
990 END IF;
991 END IF;
992
993 --
994 -- ------------------------------------
995 -- Insert into Line Column Table
996 -- ------------------------------------
997 -- pkpatel :Modify for 1575992
998 INSERT
999 INTO fv_sf133_definitions_cols_temp
1000 (sf133_fund_value,
1001 treasury_symbol_id,
1002 sf133_line_id,
1003 sf133_column_number,
1004 sf133_column_amount,
1005 sf133_amount_not_shown)
1006 VALUES(o_sf133_ts_value,
1007 o_sf133_treasury_symbol_id,
1008 o_sf133_line_id,
1009 o_sf133_column_number,
1010 o_sf133_column_amount,
1011 o_sf133_amt_not_shown);
1012 --
1013 g_insert_count := g_insert_count + 1;
1014 --
1015 -- ------------------------------------
1016 -- Exceptions
1017 -- ------------------------------------
1018 EXCEPTION
1019 --
1020 WHEN OTHERS THEN
1021 g_error_code := SQLCODE;
1022 g_error_message := SQLERRM;
1023 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',G_ERROR_MESSAGE);
1024 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','-- POPULATE_TEMP_TABLE');
1025 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','---- TREASURY SYMBOL:'||O_SF133_TS_VALUE
1026 ||' Line Id:'||o_sf133_line_id
1027 ||' Col:' ||o_sf133_column_number
1028 ||' Amt:' ||o_sf133_column_amount);
1029 --
1030 END populate_temp_table;
1031 -- --------------------------------------------------------
1032 -- --------------------------------------------------------
1033
1034 PROCEDURE populate_gtt_with_ccid
1035 (
1036 p_treasury_symbol_id NUMBER
1037 )
1038 IS
1039 l_module_name VARCHAR2(200);
1040
1041 TYPE t_seg_str_table IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
1042 TYPE t_seg_name_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1043
1044 v_seg t_seg_name_table;
1045 v_seg_str t_seg_str_table;
1046 v_statement VARCHAR2(25000);
1047 v_insert_statement VARCHAR2(32000);
1048
1049 CURSOR crec_cursor
1050 (
1051 p_sobid NUMBER
1052 ) IS
1053 SELECT fsda.sf133_line_acct_id,
1054 fsda.sf133_line_id,
1055 fsdl.sf133_fund_category,
1056 fsda.segment1,
1057 fsda.segment2,
1058 fsda.segment3,
1059 fsda.segment4,
1060 fsda.segment5,
1061 fsda.segment6,
1062 fsda.segment7,
1063 fsda.segment8,
1064 fsda.segment9,
1065 fsda.segment10,
1066 fsda.segment11,
1067 fsda.segment12,
1068 fsda.segment13,
1069 fsda.segment14,
1070 fsda.segment15,
1071 fsda.segment16,
1072 fsda.segment17,
1073 fsda.segment18,
1074 fsda.segment19,
1075 fsda.segment20,
1076 fsda.segment21,
1077 fsda.segment22,
1078 fsda.segment23,
1079 fsda.segment24,
1080 fsda.segment25,
1081 fsda.segment26,
1082 fsda.segment27,
1083 fsda.segment28,
1084 fsda.segment29,
1085 fsda.segment30
1086 FROM fv_sf133_definitions_accts fsda,
1087 fv_sf133_definitions_lines fsdl
1088 WHERE fsdl.sf133_line_id = fsda.sf133_line_id
1089 AND fsdl.set_of_books_id=p_sobid
1090
1091 ORDER BY 2,1;
1092
1093
1094 CURSOR flex_cursor
1095 (
1096 p_chart_of_accounts_id NUMBER
1097 )
1098 IS
1099 SELECT application_column_name ,
1100 flex_value_set_id
1101 FROM fnd_id_flex_segments
1102 WHERE id_flex_code = 'GL#'
1103 AND id_flex_num = p_chart_of_accounts_id;
1104
1105 CURSOR child_value_cursor
1106 (
1107 p_seg VARCHAR2,
1108 p_sid NUMBER
1109 ) IS
1110 SELECT child_flex_value_low,
1111 child_flex_value_high
1112 FROM fnd_flex_value_hierarchies
1113 WHERE parent_FLEX_value = p_seg
1114 AND flex_value_set_id = p_sid;
1115
1116 child_rec child_value_cursor%ROWTYPE;
1117
1118 l_and VARCHAR2(5);
1119 l_child VARCHAR2(32000);
1120 l_no_of_child NUMBER;
1121 l_no_of_seg NUMBER;
1122 l_segno NUMBER;
1123 l_cnt NUMBER;
1124
1125 BEGIN
1126 l_module_name := g_module_name || 'populate_gtt_with_ccid';
1127
1128 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1129 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Entering Module '||l_module_name);
1130 END IF;
1131
1132 FOR crec_rec IN crec_cursor (parm_set_of_books_id) LOOP
1133
1134 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1135 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'sf133_line_acct_id = '||crec_rec.sf133_line_acct_id);
1136 END IF;
1137
1138 v_seg(1) := crec_rec.segment1;
1139 v_seg(2) := crec_rec.segment2;
1140 v_seg(3) := crec_rec.segment3;
1141 v_seg(4) := crec_rec.segment4;
1142 v_seg(5) := crec_rec.segment5;
1143 v_seg(6) := crec_rec.segment6;
1144 v_seg(7) := crec_rec.segment7;
1145 v_seg(8) := crec_rec.segment8;
1146 v_seg(9) := crec_rec.segment9;
1147 v_seg(10) := crec_rec.segment10;
1148 v_seg(11) := crec_rec.segment11;
1149 v_seg(12) := crec_rec.segment12;
1150 v_seg(13) := crec_rec.segment13;
1151 v_seg(14) := crec_rec.segment14;
1152 v_seg(15) := crec_rec.segment15;
1153 v_seg(16) := crec_rec.segment16;
1154 v_seg(17) := crec_rec.segment17;
1155 v_seg(18) := crec_rec.segment18;
1156 v_seg(19) := crec_rec.segment19;
1157 v_seg(20) := crec_rec.segment20;
1158 v_seg(21) := crec_rec.segment21;
1159 v_seg(22) := crec_rec.segment22;
1160 v_seg(23) := crec_rec.segment23;
1161 v_seg(24) := crec_rec.segment24;
1162 v_seg(25) := crec_rec.segment25;
1163 v_seg(26) := crec_rec.segment26;
1164 v_seg(27) := crec_rec.segment27;
1165 v_seg(28) := crec_rec.segment28;
1166 v_seg(29) := crec_rec.segment29;
1167 v_seg(30) := crec_rec.segment30;
1168
1169 v_statement := NULL;
1170
1171 FOR i IN 1 ..30 LOOP
1172 v_seg_str(i) := NULL;
1173 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1174 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_seg('||i||')='||v_seg(i));
1175 END IF;
1176 END LOOP;
1177
1178 l_no_of_seg := 0;
1179
1180 FOR flex_rec IN flex_cursor (g_chart_of_accounts_id) LOOP
1181 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1182 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'application_column_name = '||flex_rec.application_column_name);
1183 END IF;
1184 l_no_of_child := 0;
1185 l_and := NULL;
1186
1187 /* check the segment values is parent */
1188 l_segno := SUBSTR(flex_rec.application_column_name,8,2);
1189 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1190 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_segno = '||l_segno);
1191 END IF;
1192
1193 IF (v_seg(l_segno) IS NOT NULL) THEN
1194 SELECT COUNT(*)
1195 INTO l_cnt
1196 FROM fnd_flex_value_hierarchies
1197 WHERE parent_flex_value = v_seg(l_segno)
1198 AND flex_value_set_id = flex_rec.flex_value_set_id;
1199
1200 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1201 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt = '||l_cnt);
1202 END IF;
1203
1204 OPEN child_value_cursor(v_seg(l_segno) , flex_rec.flex_value_set_id);
1205
1206 IF (l_cnt > 0) THEN
1207 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1208 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt > 0');
1209 END IF;
1210
1211 l_and := NULL;
1212
1213 IF (l_no_of_seg > 0) THEN
1214 l_and := ' AND ';
1215 END IF;
1216
1217 l_child := l_and || ' ( ';
1218
1219 LOOP
1220 FETCH child_value_cursor INTO child_rec;
1221 EXIT WHEN child_value_cursor%NOTFOUND ;
1222
1223 IF (l_no_of_child > 0) THEN
1224 l_child := l_child || ' OR ';
1225 END IF;
1226
1227 l_child := l_child ||
1228 flex_rec.application_column_name ||
1229 ' between '||
1230 '''' ||
1231 child_rec.child_flex_value_low ||
1232 ''' and ''' ||
1233 child_rec.child_flex_value_high ||
1234 '''' ||
1235 fnd_global.local_chr(10);
1236 l_no_of_child := l_no_of_child + 1;
1237 END LOOP;
1238
1239 l_child := l_child || ' )' ;
1240 l_and := NULL;
1241 v_statement := v_statement || l_and || l_child || fnd_global.local_chr(10);
1242
1243 ELSE
1244 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1245 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt not > 0');
1246 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_seg='||l_no_of_seg);
1247 END IF;
1248 IF (l_no_of_seg > 0) THEN
1249 l_and := ' AND ';
1250 END IF;
1251 v_statement := v_statement || l_and ||
1252 flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
1253 END IF; --cnt > 0
1254
1255
1256 CLOSE child_value_cursor;
1257 l_no_of_seg := l_no_of_seg + 1;
1258
1259 END IF; --v_seg(l_segno) IS NOT NULL
1260
1261 END LOOP; --FLEX_CURSOR
1262
1263 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1264 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_statement = '||v_statement);
1265 END IF;
1266
1267 IF (v_statement IS NOT NULL) THEN
1268 v_insert_statement := 'INSERT INTO fv_sf133_ccids_gt
1269 (
1270 sf133_line_acct_id,
1271 ccid
1272 )
1273 SELECT :b_sf133_line_acct_id,
1274 gcc.code_combination_id
1275 FROM gl_code_combinations gcc,
1276 fv_fund_parameters FFP
1277 WHERE gcc.' || v_balance_column_name ||' = ffp.fund_value
1278 AND ffp.treasury_symbol_id = :b_treasury_symbol_id
1279 AND ffp.set_of_books_id = :b_set_of_books_id
1280 AND fund_category like nvl(:b_sf133_line_category, ' || '''' ||'%' || ''''||')
1281 AND '|| v_statement || '
1282 AND gcc.template_id is null
1283 AND gcc.chart_of_accounts_id = :b_chart_of_accounts_id
1284 AND NOT EXISTS (SELECT 1
1285 FROM fv_sf133_ccids_gt fct
1286 WHERE fct.sf133_line_acct_id =:b_sf133_line_acct_id
1287 AND fct.ccid = gcc.code_combination_id)';
1288
1289 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1290 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_insert_statement = '||v_insert_statement);
1291 END IF;
1292
1293 EXECUTE IMMEDIATE v_insert_statement
1294 USING crec_rec.sf133_line_acct_id,
1295 p_treasury_symbol_id,
1296 parm_set_of_books_id,
1297 crec_rec.sf133_fund_category,
1298 g_chart_of_accounts_id,
1299 crec_rec.sf133_line_acct_id;
1300 END IF;
1301 END LOOP; --crec_cursor
1302
1303 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1304 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Exiting Module = '||l_module_name);
1305 END IF;
1306
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 g_error_code := SQLCODE;
1310 g_error_message := SQLERRM;
1311 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception',g_error_message);
1312 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception','-- populate_gtt_with_ccid');
1313 END;
1314 -- --------------------------------------------------------
1315 -- --------------------------------------------------------
1316 BEGIN
1317 g_module_name := 'fv.plsql.FV_SF133_NOYEAR.';
1318
1319
1320 END FV_SF133_NOYEAR ;