[Home] [Help]
PACKAGE BODY: APPS.FV_SF133_NOYEAR
Source
1 PACKAGE BODY FV_SF133_NOYEAR AS
2 --$Header: FV133NYB.pls 120.47 2010/07/19 05:53:36 amaddula ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLQAG'),'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 c_acct_number fv_sf133_definitions_accts.acct_number%TYPE;
83 c_direct_or_reimb_code fv_sf133_definitions_accts.direct_or_reimb_code%TYPE;
84 c_apportionment_category_code fv_sf133_definitions_accts.apportionment_category_code%TYPE;
85 c_category_b_code fv_sf133_definitions_accts.category_b_code%TYPE;
86 c_prc_code fv_sf133_definitions_accts. prc_code%TYPE;
87 c_advance_code fv_sf133_definitions_accts.advance_code%TYPE;
88 c_availability_time fv_sf133_definitions_accts.availability_time%TYPE;
89 c_bea_category_code fv_sf133_definitions_accts.bea_category_code%TYPE;
90 c_borrowing_source_code fv_sf133_definitions_accts.borrowing_source_code%TYPE;
91 c_transaction_partner fv_sf133_definitions_accts.transaction_partner%TYPE;
92 c_year_of_budget_authority fv_sf133_definitions_accts.year_of_budget_authority%TYPE;
93 c_prior_year_adjustment fv_sf133_definitions_accts.prior_year_adjustment%TYPE;
94 c_authority_type fv_sf133_definitions_accts.authority_type%TYPE;
95 c_tafs_status fv_sf133_definitions_accts.tafs_status%TYPE;
96 c_availability_type fv_sf133_definitions_accts.availability_type%TYPE;
97 c_expiration_flag fv_sf133_definitions_accts.expiration_flag%TYPE;
98 c_fund_type fv_sf133_definitions_accts.fund_type%TYPE;
99 c_financing_account_code fv_sf133_definitions_accts.financing_account_code%TYPE;
100
101 exp_date date;
102 beg_date date;
103 close_date date;
104 whether_Exp varchar2(1);
105 report_period_num NUMBER ;
106
107 whether_Exp_SameYear varchar2(1);
108 expiring_year number;
109
110
111
112 -- New variables declared by Narsimha.
113
114 c_sf133_apportion_amt number;
115 c_sf133_additional_info fv_sf133_definitions_accts
116 .sf133_additional_info%TYPE;
117
118 -- ---------- Treasury Symbol Accummulation Cursor Vaiables ---------
119 c_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
120 c_sf133_amount_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
121 --
122 -- ---------- Output Report Line Column Data -------------
123 o_sf133_ts_value fv_sf133_definitions_cols_temp.sf133_fund_value%TYPE;
124 o_sf133_line_id fv_sf133_definitions_cols_temp.sf133_line_id%TYPE;
125 o_sf133_column_number fv_sf133_definitions_cols_temp.sf133_column_number%TYPE;
126 o_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
127 o_sf133_amt_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
128
129 -- New variable declared by pkpatel to fix Bug 1575992
130 o_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
131
132 --New variables
133 v_select VARCHAR2(8600);
134 v_cursor_id INTEGER;
135 v_cursor_id_ind INTEGER;
136 parm_tsymbol_id NUMBER;
137
138
139 DSum_E NUMBER;
140 CSum_E NUMBER;
141 DSum_B NUMBER;
142 CSum_B NUMBER;
143 e_bal_indicator VARCHAR2(1);
144 b_bal_indicator VARCHAR2(1);
145 -- ---------- Define Segment Name Cursor -----------------
146 CURSOR flex_field_column_name_cursor
147 IS
148 SELECT UPPER(glflex.segment_name) segment_name,
149 UPPER(glflex.application_column_name) flex_column_name
150 FROM fnd_id_flex_segments glflex
151 WHERE glflex.application_id = 101
152 AND glflex.id_flex_num = g_chart_of_accounts_id
153 AND glflex.id_flex_code = 'GL#'
154 ORDER BY glflex.application_column_name;
155 --
156 -- ---------- Define Report Treasury Symbol Line Cursor -------------
157 -- Modified by Surya on 1/25/99 to add a join for sob between FTS and line tables.
158 -- Removed the fv_fund_parameters from the query
159
160 CURSOR ts_report_line_cursor
161 IS
162 SELECT DISTINCT
163 FTS.treasury_symbol sf133_ts_value,
164 FTS.treasury_symbol_id sf133_treasury_symbol_id, --Added for Bug 1575992
165 line.sf133_line_id sf133_line_id,
166 line.sf133_line_number sf133_line_number,
167 line.sf133_line_type_code sf133_line_type_code,
168 line.sf133_natural_balance_type sf133_natural_balance_type,
169 line.sf133_fund_category sf133_line_category,
170 line.sf133_report_line_number sf133_report_line_number
171 FROM fv_sf133_definitions_lines line,
172 FV_TREASURY_SYMBOLS FTS
173 WHERE FTS.SET_OF_BOOKS_ID = LINE.SET_OF_BOOKS_ID
174 AND FTS.Treasury_symbol = parm_treasury_value_r1
175 AND FTS.set_of_books_id = parm_set_of_books_id
176 AND (line.sf133_line_type_code) IN ('T', 'D', 'D2')
177 ORDER BY line.sf133_line_number;
178 --
179 -- ---------- Determine Balance Type of Acct -------------
180 --
181 CURSOR balance_type_cursor
182 IS
183 SELECT sf133_line_acct_id,
184 sf133_balance_type,
185 acct_number,
186 direct_or_reimb_code,
187 apportionment_category_code,
188 category_b_code,
189 prc_code,
190 advance_code,
191 availability_time,
192 bea_category_code,
193 borrowing_source_code,
194 transaction_partner,
195 year_of_budget_authority,
196 prior_year_adjustment,
197 authority_type,
198 tafs_status,
199 availability_type,
200 expiration_flag,
201 fund_type,
202 financing_account_code
203 FROM fv_sf133_definitions_accts
204 WHERE sf133_line_id = c_sf133_line_id ;
205
206 --Cursor to find all the Treasury symbols in the input range.
207 --Added a s part of Enh #2129123
208 --
209 CURSOR ts_range_cursor(tsymbol_r1 VARCHAR2, tsymbol_r2 VARCHAR2) IS
210 SELECT treasury_symbol, treasury_symbol_id
211 FROM fv_treasury_symbols
212 WHERE TIME_FRAME IN ('X','M')--CGAC
213 AND (FUND_GROUP_CODE NOT BETWEEN '3800' AND '3899')
214 AND (FUND_GROUP_CODE NOT BETWEEN '6001' AND '6999')
215 AND treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
216 AND set_of_books_id = parm_set_of_books_id
217 ORDER BY treasury_symbol;
218 --
219 --
220 PROCEDURE determine_acct_flex_segments;
221 --
222 PROCEDURE purge_temp_table;
223 --
224 PROCEDURE build_report_lines;
225 PROCEDURE build_fiscal_line_columns;
226 PROCEDURE build_total_line_columns;
227 --
228 PROCEDURE populate_temp_table;
229 PROCEDURE populate_gtt_with_ccid ( p_treasury_symbol_id number );
230 PROCEDURE GET_BAL_TYPE;
231 PROCEDURE process_total_line;
232 --
233 abort_error EXCEPTION;
234 --
235 -- ---------- End of Package Level Declaritives -----------------------------
236 --
237 -- ------------------------------------------------------------------
238 PROCEDURE main
239 (errbuf OUT NOCOPY VARCHAR2,
240 retcode OUT NOCOPY NUMBER,
241 run_mode IN VARCHAR2,
242 set_of_books_id IN NUMBER,
243 gl_period_year IN NUMBER,
244 gl_period_name IN VARCHAR2,
245 treasury_symbol_r1 IN VARCHAR2,
246 treasury_symbol_r2 IN VARCHAR2)
247 --
248 IS
249 l_module_name VARCHAR2(200) ;
250 l_req_id NUMBER := 0;
251
252 errbuf_facts VARCHAR2(1000);
253 retcode_facts NUMBER;
254 run_mode_fact VARCHAR2(15);
255 contact_fname VARCHAR2(15) ;
256 contact_lname VARCHAR2(15);
257 contact_phone NUMBER ;
258 contact_extn NUMBER ;
259 contact_email VARCHAR2(15);
260 contact_fax NUMBER;
261 contact_maiden VARCHAR2(15);
262 supervisor_name VARCHAR2(15);
263 supervisor_phone NUMBER ;
264 supervisor_extn NUMBER ;
265 agency_name_1 VARCHAR2(15);
266 agency_name_2 VARCHAR2(15);
267 address_1 VARCHAR2(15);
268 address_2 VARCHAR2(15);
269 city VARCHAR2(15);
270 state VARCHAR2(15);
271 zip VARCHAR2(15);
272 --
273 BEGIN
274
275 l_module_name := g_module_name || 'main';
276 --
277 -- ------------------------------------
278 -- Store Input Parameters in Global Variables
279 -- ------------------------------------
280 parm_application_id := '101';
281 parm_set_of_books_id := set_of_books_id;
282 parm_gl_period_year := gl_period_year;
283 parm_gl_period_name := gl_period_name;
284 parm_run_mode := UPPER(run_mode);
285
286 --Added for Bug No. 1553099
287
288 select currency_code,
289 chart_of_accounts_id
290 into g_currency_code,
291 g_chart_of_accounts_id
292 from gl_ledgers
293 where ledger_id = parm_set_of_books_id;
294
295 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- APID('||NVL(PARM_APPLICATION_ID,0) ||')'
297 ||' SoB('||NVL(parm_set_of_books_id,0) ||')'
298 ||' Year('||NVL(parm_gl_period_year,0) ||')'
299 ||' Period('||NVL(parm_gl_period_num,0) ||')'
300 ||' Fund Code('||NVL(parm_treasury_value_r1,'Null')
301 ||')');
302 END IF;
303
304 -- ----------------------------------------
305 -- Initialize Program Row Counts and Variables
306 -- ----------------------------------------
307 g_insert_count := 0;
308 g_error_code := 0;
309 g_error_message := NULL;
310 retcode := 0;
311 errbuf := '';
312 --
313 IF g_error_code = 0 THEN
314 -- ------------------------------------
315 -- Delete All Entries from Report Temp Table
316 -- ------------------------------------
317 purge_temp_table;
318 END IF;
319
320 IF g_error_code = 0 THEN
321 -- ----------------------------------------
322 -- Build Report Lines
323 -- ----------------------------------------
324 sf133_runmode := 'YES';
325 FOR ts_rec IN ts_range_cursor(treasury_symbol_r1, treasury_symbol_r2)
326 LOOP
327 parm_treasury_value_r1 := ts_rec.treasury_symbol;
328 parm_tsymbol_id := ts_rec.treasury_symbol_id;
329
330 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
331 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING TREASURY SYMBOL... '||PARM_TREASURY_VALUE_R1) ;
332 END IF;
333
334 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
335 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING FACTS II process FOR TS.....'||PARM_TREASURY_VALUE_R1);
336 END IF;
337
338 select period_num
339 into report_period_num
340 from gl_period_statuses
341 where application_id = parm_application_id
342 and set_of_books_id = parm_set_of_books_id
343 and period_name = parm_gl_period_name
344 and period_year = parm_gl_period_year;
345
346 FV_FACTS_TRANSACTIONS.main(errbuf_facts, retcode_facts, set_of_books_id,
347 parm_treasury_value_r1, parm_gl_period_year, report_period_num,
348 run_mode_fact, contact_fname, contact_lname, contact_phone,
349 contact_extn, contact_email, contact_fax, contact_maiden, supervisor_name,
350 supervisor_phone, supervisor_extn, agency_name_1,
351 agency_name_2, address_1, address_2, city, state, zip, g_currency_code);
352
353 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
354 'retcode_facts is '||retcode_facts|| 'FV_FACTS_TRANSACTIONS.v_g_edit_check_code' || FV_FACTS_TRANSACTIONS.v_g_edit_check_code);
355 if (retcode_facts <> 0 )then
356 if (retcode_facts =1 )then
357 if (FV_FACTS_TRANSACTIONS.v_g_edit_check_code = 2)then
358 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
359 'Required Edits failed for the Treasury Symbol...'||
360 PARM_TREASURY_VALUE_R1||errbuf_facts);
361 retcode :=1;
362 --return; bug 9191060; if edits fail for one process, the other processes should continue
363 END IF;
364 end if;
365
366 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
367 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
368 'Unable to process FACTS II in SF133 for TS ...'||
369 PARM_TREASURY_VALUE_R1||errbuf_facts);
370 END IF;
371 end if; -- end for if retcode_facts <> 0
372
373 if (FV_FACTS_TRANSACTIONS.v_g_edit_check_code <> 2) then
374 --if (retcode_facts <> 2) then
375 build_report_lines;
376 IF g_error_code <> 0 THEN
377 errbuf := errbuf || 'Processing for Treasury Symbol '||
378 parm_treasury_value_r1||' FAILED'|| g_error_message;
379 ELSE
380 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
381 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
382 'SUBMITTING SF133 REPORT FOR TS.....'||PARM_TREASURY_VALUE_R1);
383 END IF;
384
385 l_req_id :=
386 FND_REQUEST.SUBMIT_REQUEST ('FV','FVXBGLPN','','',FALSE,parm_set_of_books_id,g_chart_of_accounts_id,
387 parm_gl_period_year,parm_gl_period_name, parm_treasury_value_r1 );
388
389 IF l_req_id = 0 THEN
390 errbuf := 'Error submitting SF133 Report for Treasury Symbol ' || parm_treasury_value_r1;
391 retcode := -1 ;
392 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,ERRBUF) ;
393 return;
394 ELSE
395 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
396 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
397 'CONCURRENT REQUEST ID FOR SF133 REPORT - ' || l_req_id);
398 END IF;
399 END IF; -- end of IF l_req_id = 0 THEN
400 END IF; -- end of IF g_error_code <> 0
401 COMMIT;
402 end if ; -- end of if (retcode_facts = 0) then
403 END LOOP;
404 --
405 END IF;
406 --
407 IF g_error_code <> 0 THEN
408 RAISE abort_error;
409 END IF;
410 --
411 IF errbuf IS NULL THEN
412 errbuf := 'Normal End of FVSF133 Package';
413 END IF;
414 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
415 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,ERRBUF);
416 END IF;
417
418 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
419 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- INSERT COUNT('||G_INSERT_COUNT||')');
420 END IF;
421 if retcode <>1 then
422 retcode := g_error_code;
423 end if; -- if hard edits fail for atleast one process, the SF133 process should end in warning
424 errbuf := 'Normal End of FVSF133 Package';
425 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
426 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,ERRBUF);
427 END IF;
428 sf133_runmode := 'NO';
429 --
430 -- ------------------------------------
431 -- Exceptions
432 -- ------------------------------------
433 EXCEPTION
434 --
435
436 WHEN abort_error THEN
437 retcode := g_error_code;
438 errbuf := g_error_message;
439 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,ERRBUF);
440 --
441 WHEN OTHERS THEN
442 retcode := SQLCODE;
443 errbuf := SQLERRM;
444 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,errbuf);
445 RAISE_APPLICATION_ERROR(-20222,
446 'FVSF133 Exception-'||SQLERRM);
447 --
448
449 END main;
450 -- ------------------------------------------------------------------
451 -- --------------------------------------------------------
452
453 -- ------------------------------------------------------------------
454 -- --------------------------------------------------------
455 PROCEDURE determine_acct_flex_segments
456 --
457 AS
458 l_module_name VARCHAR2(200) ;
459
460 -- for data access security
461 das_id NUMBER;
462 das_where VARCHAR2(600);
463 --
464 BEGIN
465 l_module_name := g_module_name || 'determine_acct_flex_segments';
466 --
467 IF parm_run_mode = 'T' THEN
468 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
469 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START DETERMINE_ACCT_FLEX_SEGMENTS');
470 END IF;
471 END IF;
472 --
473 -- -------------------------------------
474 -- Store SoB's Chart of Accounts Id
475 -- -------------------------------------
476 SELECT chart_of_accounts_id
477 INTO g_chart_of_accounts_id
478 FROM gl_ledgers
479 WHERE ledger_id = parm_set_of_books_id;
480 --
481 -- -------------------------------------
482 -- Store Flex Segment Names in Table
483 -- -------------------------------------
484 SELECT application_column_name
485 INTO v_balance_column_name
486 FROM fnd_segment_attribute_values
487 WHERE application_id = 101
488 AND id_flex_code = 'GL#'
489 AND id_flex_num = g_chart_of_accounts_id
490 AND segment_attribute_type = 'GL_BALANCING'
491 AND attribute_value = 'Y';
492
493
494 /* Used dynamic SQL instead of balance_cursor to improve performance */
495
496 /* v_select := 'SELECT decode(:cv_balance_type, ' ||
497 ''''|| 'B' || '''' || ',' || '
498 ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
499 NVL(glbal.begin_balance_cr,0)
500 ),0),2),' ||
501 ''''|| 'E' || '''' || ',' || '
502 ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
503 NVL(glbal.begin_balance_cr,0))
504 + (NVL(glbal.period_net_dr,0) -
505 NVL(glbal.period_net_cr,0))),0),2),'||
506 ''''|| 'P' || '''' || ',' || '
507 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
508 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),-1,0,
509 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
510 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),'||
511 ''''|| 'N' || '''' || ',' || '
512 DECODE(SIGN(ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
513 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2)),1,0,
514 ROUND(NVL(SUM((NVL(glbal.period_net_dr,0)-NVL(glbal.period_net_cr,0))
515 + (NVL(glbal.begin_balance_dr,0)-NVL(glbal.begin_balance_cr,0))),0),2))) '|| '
516 FROM gl_balances glbal,
517 gl_code_combinations glcc,
518 fv_sf133_definitions_accts acct,
519 fv_sf133_ccids_gt fscg,
520 fv_fund_parameters FFP
521 WHERE glbal.ledger_id = :cv_sob_id
522 AND glbal.period_year = :cv_period_year
523 AND glbal.period_num = :cv_period
524 AND glbal.currency_code = :cv_currency_code
525 AND glbal.actual_flag = '||''''||'A'||''''||'
526 AND glcc.chart_of_accounts_id = :cv_coa_id
527 AND glbal.code_combination_id = glcc.code_combination_id
528 AND acct.sf133_line_id = :cv_sf133_line_id
529 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
530 AND glcc.template_id is null
531 AND fscg.ccid = glcc.code_combination_id
532 AND fscg.sf133_line_acct_id = acct.sf133_line_acct_id
533 AND glcc.' || v_balance_column_name ||' = FFP.fund_value
534 AND FFP.treasury_symbol_id = :cv_treasury_symbol_id
535 AND FFP.set_of_books_id = :cv_sob_id
536 AND fund_category like nvl(:cv_sf133_line_category, ' || '''' ||'%' || ''''||')';*/
537
538
539 v_select:= 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
540 where fac.treasury_symbol_id = :cv_treasury_symbol_id
541 AND acct.sf133_line_id = :cv_sf133_line_id
542 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
543 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
544 AND begin_end = :cv_balance_type and debit_credit is null';
545
546
547 -- Data Access Security:
548 /*das_id := fnd_profile.value('GL_ACCESS_SET_ID');
549 das_where := gl_access_set_security_pkg.get_security_clause
550 (das_id, gl_access_set_security_pkg.READ_ONLY_ACCESS,
551 gl_access_set_security_pkg.CHECK_LEDGER_ID,
552 to_char(parm_set_of_books_id), null,
553 gl_access_set_security_pkg.CHECK_SEGVALS,
554 null, 'glcc', null);
555 IF (das_where IS NOT NULL) THEN
556 v_select := v_select || '
557 AND ' || das_where;
558 END IF;*/
559
560 /*
561 FOR flex_field_column_name_entry IN flex_field_column_name_cursor LOOP
562 EXIT WHEN flex_field_column_name_cursor%NOTFOUND;
563 c_segment_name := flex_field_column_name_entry.segment_name;
564 c_flex_column_name := flex_field_column_name_entry.flex_column_name;
565
566 BEGIN
567 SELECT flex_value_set_id
568 INTO g_seg_value_set_id
569 FROM fnd_id_flex_segments
570 WHERE application_column_name = c_flex_column_name
571 AND application_id = 101
572 AND id_flex_code = 'GL#'
573 AND id_flex_num = g_chart_of_accounts_id;
574 EXCEPTION
575 WHEN OTHERS THEN
576 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Error in getting the Value set attched '
577 || ' to the segemnt => ' || c_flex_column_name);
578 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' SQLCODE => ' || SQLCODE ||
579 ' SQLERRM => ' || SQLERRM);
580 RAISE;
581 END;
582 -- + Rollup for the amount is the segment is a parent segment +
583 v_select := v_select || '
584 AND ( NVL(glcc.'|| c_flex_column_name ||
585 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
586 ||',NVL(glcc.'||c_flex_column_name ||
587 ','||''''||'-1'||''''||')) ' || '
588 OR glcc.'||c_flex_column_name ||' IN (SELECT flex_value '||
589 'FROM fnd_flex_values ffv, fnd_flex_value_hierarchies ffvh '||
590 'WHERE ffv.flex_value BETWEEN ffvh.child_flex_value_low
591 AND ffvh.child_flex_value_high
592 AND ffv.flex_value_set_id = ' || g_seg_value_set_id ||
593 ' AND ffv.flex_value_set_id = ffvh.flex_value_set_id'||
594 ' AND parent_flex_value = acct.' || c_flex_column_name || '))';
595 -- + commented the below code to roll up the amount for all segments +
596 v_select := v_select || '
597 AND NVL(glcc.'|| c_flex_column_name ||
598 ',' || '''' || '-1' || '''' || ') = ' || 'NVL(acct.' || c_flex_column_name
599 ||',NVL(glcc.'||c_flex_column_name ||
600 ','||''''||'-1'||''''||'))';
601
602 IF c_flex_column_name = v_balance_column_name THEN
603 -- the segment application_column_name being processed = the balancing
604 -- segment application_column_name.
605 g_fund_segment_name := c_flex_column_name;
606 END IF;
607 --
608 END LOOP;
609 */
610
611 v_cursor_id := dbms_sql.open_cursor;
612 dbms_sql.parse(v_cursor_id, v_select, dbms_sql.v7);
613 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
614
615 /*dbms_sql.bind_variable(v_cursor_id, ':cv_sob_id', parm_set_of_books_id);
616 dbms_sql.bind_variable(v_cursor_id, ':cv_period_year', parm_gl_period_year);
617 dbms_sql.bind_variable(v_cursor_id, ':cv_currency_code', g_currency_code);
618 dbms_sql.bind_variable(v_cursor_id, ':cv_coa_id', g_chart_of_accounts_id);*/
619
620 --
621
622 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
623 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,V_SELECT);
624 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133',V_SELECT);
625 END IF;
626 --
627 -- ------------------------------------
628 -- Exceptions
629 -- ------------------------------------
630 EXCEPTION
631 --
632 WHEN OTHERS THEN
633 IF flex_field_column_name_cursor%ISOPEN THEN
634 close flex_field_column_name_cursor;
635 END IF;
636 g_error_code := SQLCODE;
637 g_error_message := 'determine_acct_flex_segments/'||SQLERRM;
638 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
639 --
640 END determine_acct_flex_segments;
641 -- --------------------------------------------------------
642 -- --------------------------------------------------------
643 PROCEDURE purge_temp_table
644 --
645 IS
646 l_module_name VARCHAR2(200) ;
647 --
648 BEGIN
649 l_module_name := g_module_name || 'purge_temp_table';
650 --
651 IF parm_run_mode = 'T' THEN
652 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START PURGE_TEMP_TABLE');
654 END IF;
655 END IF;
656 --
657 DELETE
658 FROM fv_sf133_definitions_cols_temp
659 WHERE (sf133_line_id)
660 IN
661 (SELECT sf133_line_id
662 FROM fv_sf133_definitions_lines
663 WHERE set_of_books_id = parm_set_of_books_id);
664 --
665 COMMIT;
666 --
667 -- ------------------------------------
668 -- Exceptions
669 -- ------------------------------------
670 EXCEPTION
671 --
672 WHEN NO_DATA_FOUND THEN
673 NULL;
674 --
675 WHEN OTHERS THEN
676 g_error_code := SQLCODE;
677 g_error_message := 'purge_temp_table/'||SQLERRM;
678 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
679 --
680 END purge_temp_table;
681 -- --------------------------------------------------------
682 -- --------------------------------------------------------
683 PROCEDURE build_report_lines
684 --
685 AS
686 l_module_name VARCHAR2(200) ;
687 l_line_cnt NUMBER;
688 --
689 -- ----------------------------------------
690 BEGIN
691 l_module_name := g_module_name || 'build_report_lines';
692 --
693 IF parm_run_mode = 'T' THEN
694 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
695 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_REPORT_LINES');
696 END IF;
697 END IF;
698 --
699 -- ----------------------------------------
700 -- Find first period_number that is not an adjusting period
701 -- ----------------------------------------
702 --
703 --
704 SELECT min(period_num)
705 INTO g_period_num
706 FROM gl_period_statuses
707 WHERE ledger_id = parm_set_of_books_id
708 AND period_year = parm_gl_period_year
709 AND adjustment_period_flag = 'N'
710 AND application_id = '101' ;
711
712
713 -- Added on 4/28/98 by Surya Padmanabhan
714 -- Get the Period Number For the Quarter
715 SELECT PERIOD_NUM
716 INTO parm_gl_period_num
717 FROM GL_PERIOD_STATUSES
718 WHERE LEDGER_ID = parm_set_of_books_id AND
719 PERIOD_YEAR = parm_gl_period_year AND
720 APPLICATION_ID = '101' AND
721 CLOSING_STATUS in ('O','C') AND
722 PERIOD_NAME = parm_gl_period_name;
723
724 -- ----------------------------------------------------
725 -- Get Next SF133 Treasury Symbol Line from Cursor
726 -- ----------------------------------------------------
727 --
728 g_ts_value_in_process := NULL;
729 --
730 FOR ts_report_line_entry IN ts_report_line_cursor LOOP
731 --
732 c_sf133_ts_value := ts_report_line_entry.sf133_ts_value;
733 c_sf133_line_id := ts_report_line_entry.sf133_line_id;
734 c_sf133_line_number := ts_report_line_entry.sf133_line_number;
735 c_sf133_line_type_code := ts_report_line_entry.sf133_line_type_code;
736 c_sf133_natural_bal_type := ts_report_line_entry.sf133_natural_balance_type;
737 --c_sf133_line_category := ts_report_line_entry.sf133_line_category;
738 c_sf133_report_line_number := ts_report_line_entry.sf133_report_line_number;
739 c_sf133_treasury_symbol_id := ts_report_line_entry.sf133_treasury_symbol_id; --added for 1575992
740
741 IF g_error_code = 0 THEN
742 IF c_sf133_line_type_code = 'D' or c_sf133_line_type_code = 'D2' THEN
743 g_column_number := 1;
744 build_fiscal_line_columns;
745 ELSIF c_sf133_line_type_code = 'T' THEN
746 SELECT count(*)
747 INTO l_line_cnt
748 FROM fv_sf133_rep_line_calc
749 WHERE line_id = c_sf133_line_id;
750 IF l_line_cnt = 0 THEN
751 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error','Total line does not contain calculations. SEED Data not properly Loaded. Please Verify and reinvoke the Process.');
752 RETURN;
753 END IF;
754 process_total_line;
755 END IF; -- end of IF c_sf133_line_type_code = 'D' or c_sf133_line_type_code = 'D2' THEN
756 END IF;
757 --
758 END LOOP;
759 --
760 -- ------------------------------------
761 -- Exceptions
762 -- ------------------------------------
763 EXCEPTION
764 --
765 WHEN OTHERS THEN
766 IF ts_report_line_cursor%ISOPEN THEN
767 close ts_report_line_cursor;
768 END IF;
769 g_error_code := SQLCODE;
770 g_error_message := SQLERRM;
771 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_REPORT_LINES');
772 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
773 --
774 END build_report_lines;
775 -- --------------------------------------------------------
776 -- ----------------------------------------------
777 PROCEDURE build_fiscal_line_columns
778 --
779 IS
780 l_module_name VARCHAR2(200);
781 --
782 -- ----------------------------------------------
783 l_ignore INTEGER;
784 query_fetch_bal VARCHAR2(8600);
785 where_clause VARCHAR2(8600);
786
787 --CGAC
788 financing_account_treas fv_treasury_symbols.financing_account%TYPE;
789 availability_type_treas fv_treasury_symbols.availability_type_code%TYPE;
790 fund_type_treas fv_treasury_symbols.fund_type%TYPE;
791 BEGIN
792 l_module_name := g_module_name || 'build_fiscal_line_columns';
793
794 --
795 IF parm_run_mode = 'T' THEN
796 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
797 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'START BUILD_FISCAL_LINE_COLUMNS');
798 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- LINE('||C_SF133_LINE_NUMBER||')'
799 || ' Tresury Symbol('||c_sf133_ts_value ||')'
800 || ' '||to_char(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
801 END IF;
802 END IF;
803 --
804 -- ----------------------------------------
805 -- Get Fund Accummulation
806 -- ----------------------------------------
807 c_total_balance := 0;
808 c_sf133_amount_not_shown := 0;
809 c_begin_balance := 0;
810 c_ending_balance := 0;
811
812 c_begin_period := g_period_num;
813 c_end_period := parm_gl_period_num;
814 CSum_E :=0;
815 DSum_E :=0;
816 CSum_B :=0;
817 CSum_B :=0;
818
819
820 -- for the line find all accounts and sum
821
822 FOR balance_type_rec in balance_type_cursor LOOP
823 c_sf133_line_acct_id := balance_type_rec.sf133_line_acct_id;
824 c_sf133_balance_type := balance_type_rec.sf133_balance_type;
825 c_acct_number :=balance_type_rec.acct_number;
826 c_direct_or_reimb_code := balance_type_rec.direct_or_reimb_code;
827 c_apportionment_category_code := balance_type_rec.apportionment_category_code;
828 c_category_b_code:= balance_type_rec.category_b_code;
829 c_prc_code:= balance_type_rec. prc_code;
830 c_advance_code:= balance_type_rec.advance_code;
831 c_availability_time:= balance_type_rec.availability_time;
832 c_bea_category_code:= balance_type_rec.bea_category_code;
833 c_borrowing_source_code:= balance_type_rec.borrowing_source_code;
834 c_transaction_partner:= balance_type_rec.transaction_partner;
835 c_year_of_budget_authority:= balance_type_rec.year_of_budget_authority;
836 c_prior_year_adjustment:= balance_type_rec.prior_year_adjustment;
837 c_authority_type:= balance_type_rec.authority_type;
838 c_tafs_status:= balance_type_rec.tafs_status;
839 c_availability_type:= balance_type_rec.availability_type;
840 c_expiration_flag:= balance_type_rec.expiration_flag;
841 c_fund_type:= balance_type_rec.fund_type;
842 c_financing_account_code:= balance_type_rec.financing_account_code;
843
844 -- New code by Narsimha for rescission.
845
846 c_rescission_flag := 'FALSE';
847 IF upper(c_sf133_additional_info) = 'RESCISSION' THEN
848 select upper(resource_type) into c_resource_type
849 from fv_treasury_symbols
850 where treasury_symbol = parm_treasury_value_r1
851 and set_of_books_id = parm_set_of_books_id;
852 IF c_resource_type like '%APPROPRIATION%' THEN
853 IF ltrim(rtrim(c_sf133_report_line_number)) = '1A' THEN
854 c_rescission_flag := 'TRUE';
855 ELSE
856 c_rescission_flag := 'FALSE';
857 END IF;
858 ELSIF c_resource_type like '%BORROWING%' THEN
859 IF ltrim(rtrim(c_sf133_report_line_number)) = '1B' THEN
860 c_rescission_flag := 'TRUE';
861 ELSE
862 c_rescission_flag := 'FALSE';
863 END IF;
864 ELSIF c_resource_type like '%CONTRACT%' THEN
865 IF ltrim(rtrim(c_sf133_report_line_number)) = '1C' THEN
866 c_rescission_flag := 'TRUE';
867 ELSE
868 c_rescission_flag := 'FALSE';
869 END IF;
870 END IF;
871 ELSE
872 c_rescission_flag := 'TRUE';
873 END IF;
874 IF c_rescission_flag = 'TRUE' THEN
875
876 SELECT start_date,
877 end_date
878 INTO beg_date,
879 close_date
880 FROM gl_period_statuses
881 WHERE period_year = parm_gl_period_year
882 AND period_num = report_period_num
883 AND application_id = 101
884 AND set_of_books_id = parm_set_of_books_id;
885
886 --CGAC
887 SELECT availability_type_code, fund_type, expiration_Date
888 INTO availability_type_treas, fund_type_treas, exp_date
889 FROM fv_treasury_symbols
890 WHERE treasury_symbol_id=parm_tsymbol_id
891 AND set_of_books_id = parm_set_of_books_id;
892
893 -- Extract expiration date of treasury symbol and determine if the TS expired
894 -- or will it expire in the year for which the process is run
895 -- Bug9415373.
896 IF(exp_date < close_date ) THEN
897 whether_Exp := 'E';
898 ELSE
899 whether_Exp := 'U';
900 END IF;
901
902 if (exp_date is null) then
903 whether_Exp := 'U';
904 whether_Exp_SameYear := 'N';
905 end if;
906
907 IF (exp_date is not null) then
908 select extract ( year from expiration_date)into expiring_year from fv_treasury_symbols where treasury_symbol_id=parm_tsymbol_id;
909 if (expiring_year is not null and expiring_year = parm_gl_period_year) then
910 whether_Exp_SameYear := 'Y';
911 elsif ( expiring_year > parm_gl_period_year) then
912 whether_Exp_SameYear := 'N';
913 end if;
914 end if;
915
916 --CGAC
917 SELECT financing_account
918 INTO financing_account_treas
919 FROM fv_treasury_symbols
920 WHERE treasury_symbol_id = parm_tsymbol_id
921 AND set_of_books_id = parm_set_of_books_id;
922
923
924 query_fetch_bal:=null;
925
926 where_clause := ' ';
927
928 if (c_direct_or_reimb_code is not null) then
929 where_clause:= where_clause||' '||' and trim(reimburseable_flag) = '''||c_direct_or_reimb_code|| ''' ';
930
931 end if;
932
933 if (c_apportionment_category_code is not null) then
934 where_clause:= where_clause||' '||' and trim(appor_cat_code) = '''||c_apportionment_category_code|| ''' ';
935
936 end if;
937
938 IF (c_category_b_code IS NOT NULL) THEN
939 where_clause:= where_clause||' '||' and trim(appor_cat_b_dtl) = '''||c_category_b_code|| ''' ';
940
941 END IF;
942
943 IF (c_advance_code IS NOT NULL) THEN
944 where_clause:= where_clause||' '||' and trim(advance_flag) = '''||c_advance_code|| ''' ';
945
946 END IF;
947
948 IF (c_availability_time IS NOT NULL) THEN
949 where_clause:= where_clause||' '||' and trim(availability_flag) = '''||c_availability_time|| ''' ';
950
951 END IF;
952
953 IF (c_bea_category_code IS NOT NULL) THEN
954 where_clause:= where_clause||' '||' and trim(bea_category) = '''||c_bea_category_code|| ''' ';
955
956 END IF;
957
958 IF (c_borrowing_source_code IS NOT NULL) THEN
959 where_clause:= where_clause||' '||' and trim(borrowing_source) = '''||c_borrowing_source_code|| ''' ';
960
961 END IF;
962
963 IF (c_transaction_partner IS NOT NULL) THEN
964 where_clause:= where_clause||' '||' and trim(fac.transaction_partner) = '''||c_transaction_partner|| ''' ';
965
966 END IF;
967
968 IF (c_year_of_budget_authority IS NOT NULL) THEN
969 where_clause:= where_clause||' '||' and trim(year_budget_auth) = '''||c_year_of_budget_authority|| ''' ';
970
971 END IF;
972
973 IF (c_prior_year_adjustment IS NOT NULL) THEN
974 where_clause:= where_clause||' '||' and trim(pya_flag) = '''||c_prior_year_adjustment|| ''' ';
975
976 END IF;
977
978 IF (c_prc_code IS NOT NULL) THEN
979 where_clause:= where_clause||' '||' and trim(PROGRAM_RPT_CAT_NUM) = '''||c_prc_code|| ''' ';
980
981 END IF;
982
983 IF (c_authority_type IS NOT NULL) THEN
984 where_clause:= where_clause||' '||' and trim(fac.authority_type) = '''||c_authority_type|| ''' ';
985
986 END IF;
987
988
989 if (c_tafs_status is not null) then
990 where_clause:= where_clause||' '||'and trim(tafs_status) = '''||whether_Exp|| ''' ';
991
992 end if;
993 if (c_availability_type is not null and c_availability_type ='X' ) then
994 where_clause:= where_clause||' '||'and trim(availability_type) = '''||availability_type_treas||''' ';
995
996 end if;
997
998 if (c_fund_type is not null ) then
999 where_clause:= where_clause||' '||'and trim(fund_type) = '''||fund_type_treas||''' ';
1000
1001 end if;
1002
1003 if (c_financing_account_code is not null ) then
1004 where_clause:= where_clause||' '||'and trim(financing_account_code) = '''||financing_account_treas||''' ';
1005
1006 end if;
1007
1008 if (c_expiration_flag is not null ) then
1009 where_clause:= where_clause||' '||'and expiration_flag = '''||whether_Exp_SameYear||''' ';
1010
1011 end if;
1012
1013 if( c_sf133_balance_type = 'B' OR c_sf133_balance_type = 'E') then
1014
1015 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D')
1016 or ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C'))then
1017 query_fetch_bal := 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
1018 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1019 AND acct.sf133_line_id = :cv_sf133_line_id
1020 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1021 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1022 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
1023 AND begin_end = '''||c_sf133_balance_type||'''';
1024
1025 elsif (( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1026 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D') ) then
1027 query_fetch_bal := 'select sum(nvl(amount,0)*(-1)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
1028 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1029 AND acct.sf133_line_id = :cv_sf133_line_id
1030 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1031 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1032 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
1033 AND begin_end = '''||c_sf133_balance_type||'''';
1034 end if;
1035
1036 if (query_fetch_bal is not null) then
1037
1038 v_cursor_id := dbms_sql.open_cursor;
1039 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1040 -- print query
1041 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1042
1043 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1044 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1045 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1046 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_tsymbol_id);
1047 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1048
1049 --print bind variables
1050 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1051 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||parm_tsymbol_id);
1052 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1053 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1054
1055
1056 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1057
1058 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1059 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1060 END IF;
1061
1062 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1063 dbms_sql.close_cursor(v_cursor_id);
1064 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1065 end if;
1066
1067 -- End the code for bal type beginning and ending
1068
1069 elsif c_sf133_balance_type = 'E-B' then -- balance type is end-begin
1070
1071 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1072 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C') ) then
1073
1074 query_fetch_bal := ' select
1075 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )
1076 from fv_facts_temp fac, fv_sf133_definitions_accts acct
1077 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1078 AND acct.sf133_line_id = :cv_sf133_line_id
1079 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1080 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1081 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL'' ';
1082
1083 v_cursor_id := dbms_sql.open_cursor;
1084
1085 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1086
1087 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1088 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1089 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1090 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_tsymbol_id);
1091 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1092 -- print query
1093 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1094
1095 --print bind variables
1096 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1097 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||parm_tsymbol_id);
1098 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1099 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1100
1101 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1102
1103 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1105 END IF;
1106
1107 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1108 dbms_sql.close_cursor(v_cursor_id);
1109 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1110
1111 elsif (( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1112 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D'))then
1113
1114 query_fetch_bal := ' select
1115 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )*(-1)
1116 from fv_facts_temp fac, fv_sf133_definitions_accts acct
1117 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1118 AND acct.sf133_line_id = :cv_sf133_line_id
1119 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1120 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1121 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL'' ';
1122
1123 v_cursor_id := dbms_sql.open_cursor;
1124 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1125
1126 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1127 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1128 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1129 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_tsymbol_id);
1130 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1131 -- print query
1132 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1133
1134 --print bind variables
1135 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1136 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||parm_tsymbol_id);
1137 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1138 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1139
1140 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1141
1142 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1143 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1144 END IF;
1145
1146 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1147 dbms_sql.close_cursor(v_cursor_id);
1148 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1149 END IF;
1150
1151 elsif (( c_sf133_balance_type= 'ED') or( c_sf133_balance_type= 'EC')) then -- bal type is ending debit or ending credit only
1152
1153 query_fetch_bal := 'select sum(nvl(amount,0)) from fv_facts_temp fac, fv_sf133_definitions_accts acct
1154 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1155 AND acct.sf133_line_id = :cv_sf133_line_id
1156 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1157 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1158 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
1159 AND begin_end = ''E''';
1160
1161 if (query_fetch_bal is not null) then
1162 v_cursor_id := dbms_sql.open_cursor;
1163 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1164
1165 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1166 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1167 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1168 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_tsymbol_id);
1169 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1170
1171 -- print query
1172 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1173 --print bind variables
1174 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1175 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'parm_tsymbol_id:'||parm_tsymbol_id);
1176 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1177 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1178
1179 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1180
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,'l_ignore := '||l_ignore);
1183 END IF;
1184
1185 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1186 dbms_sql.close_cursor(v_cursor_id);
1187 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1188
1189 if ( c_sf133_balance_type = 'ED')then
1190 if (c_total_balance < 0) then
1191 c_total_balance := 0;
1192 end if;
1193 elsif ( c_sf133_balance_type = 'EC')then
1194 if (c_total_balance > 0) then
1195 c_total_balance := 0;
1196 end if;
1197 end if;
1198 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1199 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C'))then
1200 c_total_balance := c_total_balance;
1201 end if;
1202
1203 if (( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1204 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D')) then
1205 c_total_balance := c_total_balance*(-1);
1206 end if;
1207
1208 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance after modification:'||c_total_balance);
1209 end if;
1210
1211 elsif( (c_sf133_balance_type= 'E-BD') or (c_sf133_balance_type='E-BC')) then -- bal type is end begin debit only
1212
1213 query_fetch_bal := ' select
1214 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )
1215 from fv_facts_temp fac, fv_sf133_definitions_accts acct
1216 where fac.treasury_symbol_id = :cv_treasury_symbol_id
1217 AND acct.sf133_line_id = :cv_sf133_line_id
1218 AND acct.sf133_line_acct_id = :cv_sf133_line_acct_id
1219 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
1220 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL'' ';
1221
1222 v_cursor_id := dbms_sql.open_cursor;
1223
1224 query_fetch_bal := query_fetch_bal ||' '|| where_clause;
1225
1226 dbms_sql.parse(v_cursor_id, query_fetch_bal, dbms_sql.v7);
1227 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
1228 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_acct_id',c_sf133_line_acct_id);
1229 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_tsymbol_id);
1230 dbms_sql.bind_variable(v_cursor_id,':cv_sf133_line_id',c_sf133_line_id);
1231 -- print query
1232 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal);
1233
1234 --print bind variables
1235 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_balance_type:'||c_sf133_balance_type);
1236 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||parm_tsymbol_id);
1237 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sf133_line_acct_id:'||c_sf133_line_acct_id);
1238 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sf133_line_id:'||c_sf133_line_id);
1239
1240 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
1241
1242 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1243 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
1244 END IF;
1245
1246 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
1247 dbms_sql.close_cursor(v_cursor_id);
1248 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
1249
1250 if (c_sf133_balance_type= 'E-BD') then
1251 if (c_total_balance > 0) then
1252 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1253 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C') ) then
1254 c_total_balance :=c_total_balance;
1255 end if;
1256
1257 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1258 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D') ) then
1259 c_total_balance :=c_total_balance*-1;
1260 end if;
1261 else
1262 c_total_balance :=0; -- consider the balance only if E-B is positive
1263 end if;
1264 end if; -- end for if (c_sf133_balance_type= 'E-BD') then
1265
1266
1267 if (c_sf133_balance_type= 'E-BC') then
1268 if (c_total_balance < 0) then
1269 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='D') or
1270 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='C') ) then
1271 c_total_balance :=c_total_balance;
1272 end if;
1273
1274 if(( c_sf133_line_type_code = 'D' AND c_sf133_natural_bal_type ='C') or
1275 ( c_sf133_line_type_code = 'D2' AND c_sf133_natural_bal_type ='D') ) then
1276 c_total_balance :=c_total_balance*-1;
1277 end if;
1278 else
1279 c_total_balance :=0; -- consider the balance only if E-B is negative
1280 end if;
1281 end if;
1282
1283 END IF; -- end checking for balance types
1284 end if; -- end for if rescission condition
1285
1286 -- sum the line amount
1287 if (c_total_balance is null) then
1288 c_total_balance :=0;
1289 end if;
1290
1291 c_sf133_amount_not_shown := c_sf133_amount_not_shown + c_total_balance;
1292
1293 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1294 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMT NOT SHOWN = '||C_SF133_AMOUNT_NOT_SHOWN);
1295 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance = '||c_total_balance);
1296 END IF;
1297
1298
1299 END LOOP;
1300 --
1301 -- set up correct display sign
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,'NATURAL BAL TYPE = '||C_SF133_NATURAL_BAL_TYPE);
1305 END IF;
1306 /* IF c_sf133_natural_bal_type = 'C' THEN
1307 -- Credit so display opposite
1308 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
1309
1310 ELSIF c_sf133_natural_bal_type = 'D' THEN
1311 -- Debit so display as is
1312 c_sf133_column_amount := c_sf133_amount_not_shown;
1313
1314 ELSIF c_sf133_natural_bal_type = 'A' THEN
1315 -- Display the absolute value
1316 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
1317
1318 ELSIF c_sf133_natural_bal_type = 'N' THEN
1319 -- Display as negative
1320 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
1321
1322 END IF;*/
1323
1324 --
1325 o_sf133_ts_value := c_sf133_ts_value;
1326 o_sf133_line_id := c_sf133_line_id;
1327 o_sf133_column_number := g_column_number;
1328 o_sf133_column_amount := c_sf133_amount_not_shown;
1329 o_sf133_amt_not_shown := c_sf133_amount_not_shown;
1330 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --added for 1575992
1331
1332 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1333 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COL AMT ='||O_SF133_COLUMN_AMOUNT);
1334 END IF;
1335 populate_temp_table;
1336 --
1337 -- ------------------------------------
1338 -- Exceptions
1339 -- ------------------------------------
1340 EXCEPTION
1341 --
1342 --
1343 WHEN OTHERS THEN
1344
1345 g_error_code := SQLCODE;
1346 g_error_message := SQLERRM;
1347
1348 IF balance_type_cursor%ISOPEN THEN
1349 close balance_type_cursor;
1350 ELSIF dbms_sql.is_open(v_cursor_id) THEN
1351 dbms_sql.close_cursor(v_cursor_id);
1352 END IF;
1353
1354 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_FISCAL_LINE_COLUMNS:'||G_ERROR_MESSAGE);
1355 --
1356 END build_fiscal_line_columns;
1357 -- ----------------------------------------------
1358 -- ----------------------------------------------
1359 PROCEDURE build_total_line_columns
1360 --
1361 IS
1362 l_module_name VARCHAR2(200) ;
1363 --
1364 -- ----------------------------------------------
1365 BEGIN
1366 l_module_name := g_module_name || 'build_total_line_columns';
1367 --
1368 IF parm_run_mode = 'T' THEN
1369 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1370 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_TOTAL_LINE_COLUMNS');
1371 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-- LINE='||C_SF133_LINE_NUMBER
1372 ||' Start Total Line('||g_total_start_line_number||')'
1373 || ' Treasury Symbol('||c_sf133_ts_value||')');
1374 END IF;
1375 END IF;
1376 --
1377 -- ----------------------------------------
1378 -- Get Treasury Symbol Accummulation for Total using column with true sign.
1379 -- ----------------------------------------
1380 SELECT NVL(SUM(NVL(sf133_amount_not_shown,0)),0)
1381 INTO c_sf133_amount_not_shown
1382 FROM fv_sf133_definitions_cols_temp
1383 WHERE sf133_column_number = g_column_number
1384 AND sf133_fund_value = c_sf133_ts_value
1385 AND (sf133_line_id)
1386 IN
1387 (SELECT sf133_line_id
1388 FROM fv_sf133_definitions_lines
1389 WHERE set_of_books_id = parm_set_of_books_id
1390 AND sf133_line_number >
1391 DECODE(c_sf133_line_type_code, 'T', g_total_start_line_number, g_subtotal_start_line_number)
1392 AND sf133_line_number < c_sf133_line_number);
1393 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1394 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SRART ' || G_TOTAL_START_LINE_NUMBER);
1395 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END: ' || C_SF133_LINE_NUMBER);
1396 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMTNS: ' || C_SF133_AMOUNT_NOT_SHOWN);
1397 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NATURAL BAL TYPE = '||C_SF133_NATURAL_BAL_TYPE);
1398 END IF;
1399
1400 IF c_sf133_natural_bal_type = 'C' THEN
1401 -- Credit so display opposite
1402 c_sf133_column_amount := c_sf133_amount_not_shown * -1;
1403
1404 ELSIF c_sf133_natural_bal_type = 'D' THEN
1405 -- Debit so display as is
1406 c_sf133_column_amount := c_sf133_amount_not_shown;
1407
1408 ELSIF c_sf133_natural_bal_type = 'A' THEN
1409 -- Display the absolute value
1410 c_sf133_column_amount := ABS(c_sf133_amount_not_shown);
1411
1412 ELSIF c_sf133_natural_bal_type = 'N' THEN
1413 -- Display as negative
1414 c_sf133_column_amount := '-'||ABS(c_sf133_amount_not_shown);
1415
1416 END IF;
1417
1418 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1419 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BALT: ' || C_SF133_NATURAL_BAL_TYPE);
1420 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMT: ' || C_SF133_COLUMN_AMOUNT);
1421 END IF;
1422
1423 --
1424 -- ------------------------------------
1425 -- Insert Report Column
1426 -- ------------------------------------
1427 o_sf133_ts_value := c_sf133_ts_value;
1428 o_sf133_line_id := c_sf133_line_id;
1429 o_sf133_column_number := g_column_number;
1430 o_sf133_column_amount := c_sf133_column_amount;
1431 o_sf133_amt_not_shown := c_sf133_amount_not_shown; -- Bug # 2896450
1432 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --added for 1575992
1433 populate_temp_table;
1434 --
1435 -- ------------------------------------
1436 -- Exceptions
1437 -- ------------------------------------
1438 EXCEPTION
1439 --
1440 WHEN OTHERS THEN
1441 g_error_code := SQLCODE;
1442 g_error_message := SQLERRM;
1443 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_TOTAL_LINE_COLUMNS');
1444 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message );
1445 --
1446 END build_total_line_columns;
1447 -- ----------------------------------------------
1448 -- --------------------------------------------------------
1449 PROCEDURE populate_temp_table
1450 --
1451 IS
1452 l_module_name VARCHAR2(200) ;
1453 --
1454 -- ----------------------------------------------
1455 BEGIN
1456 l_module_name := g_module_name || 'populate_temp_table';
1457 --
1458 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133','START POPULATE_TEMP_TABLE');
1459 IF parm_run_mode = 'T' THEN
1460 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1461 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START POPULATE_TEMP_TABLE');
1462 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- '||C_SF133_LINE_NUMBER
1463 ||' ('||o_sf133_column_number||')'
1464 ||' ('||o_sf133_column_amount||')'
1465 ||' ('||o_sf133_amt_not_shown||')');
1466 END IF;
1467 END IF;
1468
1469 --
1470 -- ------------------------------------
1471 -- Insert into Line Column Table
1472 -- ------------------------------------
1473 -- pkpatel :Modify for 1575992
1474 INSERT
1475 INTO fv_sf133_definitions_cols_temp
1476 (sf133_fund_value,
1477 treasury_symbol_id,
1478 sf133_line_id,
1479 sf133_column_number,
1480 sf133_column_amount,
1481 sf133_amount_not_shown)
1482 VALUES(o_sf133_ts_value,
1483 o_sf133_treasury_symbol_id,
1484 o_sf133_line_id,
1485 o_sf133_column_number,
1486 o_sf133_column_amount,
1487 o_sf133_amt_not_shown);
1488 --
1489 g_insert_count := g_insert_count + 1;
1490 --
1491 -- ------------------------------------
1492 -- Exceptions
1493 -- ------------------------------------
1494 EXCEPTION
1495 --
1496 WHEN OTHERS THEN
1497 g_error_code := SQLCODE;
1498 g_error_message := SQLERRM;
1499 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',G_ERROR_MESSAGE);
1500 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','-- POPULATE_TEMP_TABLE');
1501 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','---- TREASURY SYMBOL:'||O_SF133_TS_VALUE
1502 ||' Line Id:'||o_sf133_line_id
1503 ||' Col:' ||o_sf133_column_number
1504 ||' Amt:' ||o_sf133_column_amount);
1505 --
1506 END populate_temp_table;
1507 -- --------------------------------------------------------
1508 -- --------------------------------------------------------
1509
1510
1511 PROCEDURE populate_gtt_with_ccid
1512 (
1513 p_treasury_symbol_id NUMBER
1514 )
1515 IS
1516 l_module_name VARCHAR2(200);
1517
1518 TYPE t_seg_str_table IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
1519 TYPE t_seg_name_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1520
1521 v_seg t_seg_name_table;
1522 v_seg_str t_seg_str_table;
1523 v_statement VARCHAR2(25000);
1524 v_insert_statement VARCHAR2(32000);
1525
1526 CURSOR crec_cursor
1527 (
1528 p_sobid NUMBER
1529 ) IS
1530 SELECT fsda.sf133_line_acct_id,
1531 fsda.sf133_line_id,
1532 fsdl.sf133_fund_category,
1533 fsda.segment1,
1534 fsda.segment2,
1535 fsda.segment3,
1536 fsda.segment4,
1537 fsda.segment5,
1538 fsda.segment6,
1539 fsda.segment7,
1540 fsda.segment8,
1541 fsda.segment9,
1542 fsda.segment10,
1543 fsda.segment11,
1544 fsda.segment12,
1545 fsda.segment13,
1546 fsda.segment14,
1547 fsda.segment15,
1548 fsda.segment16,
1549 fsda.segment17,
1550 fsda.segment18,
1551 fsda.segment19,
1552 fsda.segment20,
1553 fsda.segment21,
1554 fsda.segment22,
1555 fsda.segment23,
1556 fsda.segment24,
1557 fsda.segment25,
1558 fsda.segment26,
1559 fsda.segment27,
1560 fsda.segment28,
1561 fsda.segment29,
1562 fsda.segment30
1563 FROM fv_sf133_definitions_accts fsda,
1564 fv_sf133_definitions_lines fsdl
1565 WHERE fsdl.sf133_line_id = fsda.sf133_line_id
1566 AND fsdl.set_of_books_id=p_sobid
1567
1568 ORDER BY 2,1;
1569
1570
1571 CURSOR flex_cursor
1572 (
1573 p_chart_of_accounts_id NUMBER
1574 )
1575 IS
1576 SELECT application_column_name ,
1577 flex_value_set_id
1578 FROM fnd_id_flex_segments
1579 WHERE id_flex_code = 'GL#'
1580 AND id_flex_num = p_chart_of_accounts_id;
1581
1582 CURSOR child_value_cursor
1583 (
1584 p_seg VARCHAR2,
1585 p_sid NUMBER
1586 ) IS
1587 SELECT child_flex_value_low,
1588 child_flex_value_high
1589 FROM fnd_flex_value_hierarchies
1590 WHERE parent_FLEX_value = p_seg
1591 AND flex_value_set_id = p_sid;
1592
1593 child_rec child_value_cursor%ROWTYPE;
1594
1595 l_and VARCHAR2(5);
1596 l_child VARCHAR2(32000);
1597 l_no_of_child NUMBER;
1598 l_no_of_seg NUMBER;
1599 l_segno NUMBER;
1600 l_cnt NUMBER;
1601
1602 BEGIN
1603 l_module_name := g_module_name || 'populate_gtt_with_ccid';
1604
1605 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1606 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Entering Module '||l_module_name);
1607 END IF;
1608
1609 FOR crec_rec IN crec_cursor (parm_set_of_books_id) LOOP
1610
1611 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1612 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'sf133_line_acct_id = '||crec_rec.sf133_line_acct_id);
1613 END IF;
1614
1615 v_seg(1) := crec_rec.segment1;
1616 v_seg(2) := crec_rec.segment2;
1617 v_seg(3) := crec_rec.segment3;
1618 v_seg(4) := crec_rec.segment4;
1619 v_seg(5) := crec_rec.segment5;
1620 v_seg(6) := crec_rec.segment6;
1621 v_seg(7) := crec_rec.segment7;
1622 v_seg(8) := crec_rec.segment8;
1623 v_seg(9) := crec_rec.segment9;
1624 v_seg(10) := crec_rec.segment10;
1625 v_seg(11) := crec_rec.segment11;
1626 v_seg(12) := crec_rec.segment12;
1627 v_seg(13) := crec_rec.segment13;
1628 v_seg(14) := crec_rec.segment14;
1629 v_seg(15) := crec_rec.segment15;
1630 v_seg(16) := crec_rec.segment16;
1631 v_seg(17) := crec_rec.segment17;
1632 v_seg(18) := crec_rec.segment18;
1633 v_seg(19) := crec_rec.segment19;
1634 v_seg(20) := crec_rec.segment20;
1635 v_seg(21) := crec_rec.segment21;
1636 v_seg(22) := crec_rec.segment22;
1637 v_seg(23) := crec_rec.segment23;
1638 v_seg(24) := crec_rec.segment24;
1639 v_seg(25) := crec_rec.segment25;
1640 v_seg(26) := crec_rec.segment26;
1641 v_seg(27) := crec_rec.segment27;
1642 v_seg(28) := crec_rec.segment28;
1643 v_seg(29) := crec_rec.segment29;
1644 v_seg(30) := crec_rec.segment30;
1645
1646 v_statement := NULL;
1647
1648 FOR i IN 1 ..30 LOOP
1649 v_seg_str(i) := NULL;
1650 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1651 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_seg('||i||')='||v_seg(i));
1652 END IF;
1653 END LOOP;
1654
1655 l_no_of_seg := 0;
1656
1657 FOR flex_rec IN flex_cursor (g_chart_of_accounts_id) LOOP
1658 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1659 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'application_column_name = '||flex_rec.application_column_name);
1660 END IF;
1661 l_no_of_child := 0;
1662 l_and := NULL;
1663
1664 /* check the segment values is parent */
1665 l_segno := SUBSTR(flex_rec.application_column_name,8,2);
1666 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1667 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_segno = '||l_segno);
1668 END IF;
1669
1670 IF (v_seg(l_segno) IS NOT NULL) THEN
1671 SELECT COUNT(*)
1672 INTO l_cnt
1673 FROM fnd_flex_value_hierarchies
1674 WHERE parent_flex_value = v_seg(l_segno)
1675 AND flex_value_set_id = flex_rec.flex_value_set_id;
1676
1677 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1678 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt = '||l_cnt);
1679 END IF;
1680
1681 OPEN child_value_cursor(v_seg(l_segno) , flex_rec.flex_value_set_id);
1682
1683 IF (l_cnt > 0) THEN
1684 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1685 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt > 0');
1686 END IF;
1687
1688 l_and := NULL;
1689
1690 IF (l_no_of_seg > 0) THEN
1691 l_and := ' AND ';
1692 END IF;
1693
1694 l_child := l_and || ' ( ';
1695
1696 LOOP
1697 FETCH child_value_cursor INTO child_rec;
1698 EXIT WHEN child_value_cursor%NOTFOUND ;
1699
1700 IF (l_no_of_child > 0) THEN
1701 l_child := l_child || ' OR ';
1702 END IF;
1703
1704 l_child := l_child ||
1705 flex_rec.application_column_name ||
1706 ' between '||
1707 '''' ||
1708 child_rec.child_flex_value_low ||
1709 ''' and ''' ||
1710 child_rec.child_flex_value_high ||
1711 '''' ||
1712 fnd_global.local_chr(10);
1713 l_no_of_child := l_no_of_child + 1;
1714 END LOOP;
1715
1716 l_child := l_child || ' )' ;
1717 l_and := NULL;
1718 v_statement := v_statement || l_and || l_child || fnd_global.local_chr(10);
1719
1720 ELSE
1721 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1722 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_cnt not > 0');
1723 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_seg='||l_no_of_seg);
1724 END IF;
1725 IF (l_no_of_seg > 0) THEN
1726 l_and := ' AND ';
1727 END IF;
1728 v_statement := v_statement || l_and ||
1729 flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
1730 END IF; --cnt > 0
1731
1732
1733 CLOSE child_value_cursor;
1734 l_no_of_seg := l_no_of_seg + 1;
1735
1736 END IF; --v_seg(l_segno) IS NOT NULL
1737
1738 END LOOP; --FLEX_CURSOR
1739
1740 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1741 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_statement = '||v_statement);
1742 END IF;
1743
1744 IF (v_statement IS NOT NULL) THEN
1745 v_insert_statement := 'INSERT INTO fv_sf133_ccids_gt
1746 (
1747 sf133_line_acct_id,
1748 ccid
1749 )
1750 SELECT :b_sf133_line_acct_id,
1751 gcc.code_combination_id
1752 FROM gl_code_combinations gcc,
1753 fv_fund_parameters FFP
1754 WHERE gcc.' || v_balance_column_name ||' = ffp.fund_value
1755 AND ffp.treasury_symbol_id = :b_treasury_symbol_id
1756 AND ffp.set_of_books_id = :b_set_of_books_id
1757 AND fund_category like nvl(:b_sf133_line_category, ' || '''' ||'%' || ''''||')
1758 AND '|| v_statement || '
1759 AND gcc.template_id is null
1760 AND gcc.chart_of_accounts_id = :b_chart_of_accounts_id
1761 AND NOT EXISTS (SELECT 1
1762 FROM fv_sf133_ccids_gt fct
1763 WHERE fct.sf133_line_acct_id =:b_sf133_line_acct_id
1764 AND fct.ccid = gcc.code_combination_id)';
1765
1766 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1767 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'v_insert_statement = '||v_insert_statement);
1768 END IF;
1769
1770 EXECUTE IMMEDIATE v_insert_statement
1771 USING crec_rec.sf133_line_acct_id,
1772 p_treasury_symbol_id,
1773 parm_set_of_books_id,
1774 crec_rec.sf133_fund_category,
1775 g_chart_of_accounts_id,
1776 crec_rec.sf133_line_acct_id;
1777 END IF;
1778 END LOOP; --crec_cursor
1779
1780 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1781 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Exiting Module = '||l_module_name);
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 g_error_code := SQLCODE;
1787 g_error_message := SQLERRM;
1788 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception',g_error_message);
1789 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.exception','-- populate_gtt_with_ccid');
1790 END;
1791
1792
1793
1794 /*Procedure to fetch balance type for accounts which contain ending and/or begining balances
1795 of type either credit or debit or both*/
1796 PROCEDURE GET_BAL_TYPE
1797 IS
1798 query_Ending_Indicator VARCHAR2(8600);
1799 query_Beg_Indicator VARCHAR2(8600);
1800 l_ignore1 INTEGER;
1801 BEGIN
1802 -- get the bal indicator of all E records if there are records of multiple bal types
1803 query_Ending_Indicator := 'select sum(decode(facE.debit_credit,''D'',amount)),
1804 sum(decode(facE.debit_credit,''C'',amount)) from
1805 fv_facts_temp facE, fv_sf133_definitions_accts acct
1806 where facE.treasury_symbol_id = '||parm_tsymbol_id||
1807 'AND acct.sf133_line_id = '||c_sf133_line_id||
1808 'AND acct.sf133_line_acct_id = '||c_sf133_line_acct_id||
1809 'and acct_number like facE.sgl_acct_number||''%'' and facE.sgl_acct_number is not null
1810 and facE.begin_end=''E''' ;
1811
1812 v_cursor_id_ind := dbms_sql.open_cursor;
1813 dbms_sql.parse(v_cursor_id_ind, query_Ending_Indicator, dbms_sql.v7);
1814 dbms_sql.define_column(v_cursor_id_ind, 1,DSum_E);
1815 dbms_sql.define_column(v_cursor_id_ind, 2,CSum_E);
1816
1817 l_ignore1 := dbms_sql.execute_and_fetch(v_cursor_id_ind);
1818 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1819 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133','l_ignore1 := '||l_ignore1);
1820 END IF;
1821 dbms_sql.column_value(v_cursor_id_ind, 1, DSum_E);
1822 dbms_sql.column_value(v_cursor_id_ind, 2, CSum_E);
1823 -- dbms_sql.close_cursor(v_cursor_id_ind);
1824
1825 if DSum_E >= CSum_E then
1826 e_bal_indicator:='D';
1827 else
1828 e_bal_indicator:='C';
1829 end if;
1830
1831 -- get the bal indicator of all E records if there are records of multiple bal types
1832 query_Beg_Indicator:= 'select sum(decode(facB.debit_credit,''D'',amount)) ,
1833 sum(decode(facB.debit_credit,''C'',amount)) from
1834 fv_facts_temp facB, fv_sf133_definitions_accts acct
1835 where facB.treasury_symbol_id = '||parm_tsymbol_id||
1836 'AND acct.sf133_line_id = '||c_sf133_line_id||
1837 'AND acct.sf133_line_acct_id = '||c_sf133_line_acct_id||
1838 'and acct_number like facB.sgl_acct_number||''%'' and facB.sgl_acct_number is not null
1839 and facB.begin_end=''B''' ;
1840
1841 v_cursor_id_ind := dbms_sql.open_cursor;
1842 dbms_sql.parse(v_cursor_id_ind, query_Beg_Indicator, dbms_sql.v7);
1843 dbms_sql.define_column(v_cursor_id_ind, 1,DSum_B);
1844 dbms_sql.define_column(v_cursor_id_ind, 2,CSum_B);
1845
1846 l_ignore1 := dbms_sql.execute_and_fetch(v_cursor_id_ind);
1847 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1848 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, 'testsf133','l_ignore1 := '||l_ignore1);
1849 END IF;
1850 dbms_sql.column_value(v_cursor_id_ind, 1, DSum_B);
1851 dbms_sql.column_value(v_cursor_id_ind, 2, CSum_B);
1852 --dbms_sql.close_cursor(v_cursor_id_ind);
1853
1854 if DSum_B >= CSum_B then
1855 b_bal_indicator:='D';
1856 else
1857 b_bal_indicator:='C';
1858 end if;
1859 DBMS_SQL.CLOSE_CURSOR(v_cursor_id_ind);
1860 EXCEPTION
1861 WHEN OTHERS THEN
1862 g_error_code := SQLCODE;
1863 g_error_message := SQLERRM;
1864 fv_utility.log_mesg(fnd_log.level_unexpected, 'testsf133'||'.exception',g_error_message);
1865 fv_utility.log_mesg(fnd_log.level_unexpected, 'testsf133'||'.exception','-- get_bal_type');
1866 END;
1867
1868
1869 PROCEDURE process_total_line
1870 IS
1871 l_module_name VARCHAR2(200) := g_module_name || 'process_total_line';
1872
1873 CURSOR fv_sf133_calc_cur IS
1874 SELECT calc_sequence_number, line_low, line_high, line_low_type, line_high_type,
1875 operator
1876 FROM fv_sf133_rep_line_calc
1877 WHERE line_id = c_sf133_line_id
1878 ORDER BY calc_sequence_number;
1879
1880 CURSOR fv_sf133_temp_cur (p_line_id NUMBER) IS
1881 SELECT sf133_column_amount
1882 FROM fv_sf133_definitions_cols_temp
1883 WHERE sf133_line_id = p_line_id and
1884 treasury_symbol_id = c_sf133_treasury_symbol_id;
1885
1886 -- Bug 9191098
1887 CURSOR fv_cfs_lines_cur(p_lineid_1 NUMBER, p_lineid_2 NUMBER) IS
1888 SELECT sf133_line_id
1889 FROM fv_sf133_definitions_lines
1890 WHERE sf133_line_number >=
1891 (SELECT sf133_line_number FROM fv_sf133_definitions_lines
1892 WHERE sf133_line_id = p_lineid_1 )
1893 AND sf133_line_number <=
1894 (SELECT sf133_line_number FROM fv_sf133_definitions_lines
1895 WHERE sf133_line_id = p_lineid_2 );
1896
1897 l_line_id fv_cfs_rep_lines.line_id%TYPE;
1898 temp_amt_low fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE DEFAULT 0;
1899 temp_amt_high fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE DEFAULT 0;
1900
1901
1902 TYPE amt_rec IS RECORD (
1903 calc_sequence fv_sf133_rep_line_calc.calc_sequence_number%TYPE,
1904 col_1_amt fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE DEFAULT 0);
1905
1906
1907 TYPE amt_table IS TABLE OF amt_rec
1908 INDEX BY BINARY_INTEGER;
1909
1910 amt_array amt_table;
1911 amt_array_cnt BINARY_INTEGER DEFAULT 1;
1912 v_col_1_amt fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
1913
1914
1915 BEGIN
1916 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'Inside process_total_line');
1917
1918 FOR calc_rec IN fv_sf133_calc_cur
1919 LOOP
1920 amt_array(amt_array_cnt).calc_sequence := calc_rec.calc_sequence_number;
1921
1922 IF calc_rec.line_low_type = 'L' AND calc_rec.operator IN ('+','-') THEN
1923 l_line_id := calc_rec.line_low;
1924 OPEN fv_sf133_temp_cur(l_line_id);
1925 FETCH fv_sf133_temp_cur
1926 INTO temp_amt_low;
1927 CLOSE fv_sf133_temp_cur;
1928 ELSIF calc_rec.line_low_type = 'C' AND calc_rec.operator IN ('+','-') THEN
1929 FOR i IN 1..amt_array_cnt
1930 LOOP
1931 IF amt_array(i).calc_sequence = calc_rec.line_low THEN
1932 temp_amt_low := amt_array(i).col_1_amt;
1933 END IF;
1934 END LOOP;
1935 END IF;
1936
1937 IF calc_rec.line_high_type = 'L' AND calc_rec.operator IN ('+','-') THEN
1938 l_line_id := calc_rec.line_high;
1939 OPEN fv_sf133_temp_cur(l_line_id);
1940 FETCH fv_sf133_temp_cur
1941 INTO temp_amt_high;
1942 CLOSE fv_sf133_temp_cur;
1943 ELSIF calc_rec.line_high_type = 'C' AND calc_rec.operator IN ('+','-') THEN
1944 FOR i IN 1..amt_array_cnt - 1
1945 LOOP
1946 IF amt_array(i).calc_sequence = calc_rec.line_high THEN
1947 temp_amt_high := amt_array(i).col_1_amt;
1948 END IF;
1949 END LOOP;
1950 END IF;
1951
1952 IF calc_rec.operator = '+' THEN
1953 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low, 0) + NVL(temp_amt_high, 0);
1954 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,calc_rec.operator||amt_array(amt_array_cnt).col_1_amt);
1955
1956 ELSIF calc_rec.operator = '-' THEN
1957 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low, 0) - NVL(temp_amt_high, 0);
1958 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,calc_rec.operator||amt_array(amt_array_cnt).col_1_amt);
1959 ELSE
1960 IF calc_rec.line_low_type = 'L' THEN
1961 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'inside for loop, range:'||calc_rec.line_low||calc_rec.line_high);
1962 FOR lines_rec IN fv_cfs_lines_cur(calc_rec.line_low, calc_rec.line_high)
1963 LOOP
1964 FOR fv_sf133_temp_cur_rec IN fv_sf133_temp_cur(lines_rec.sf133_line_id)
1965 LOOP
1966 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(fv_sf133_temp_cur_rec.sf133_column_amount, 0);
1967 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'inside for loop, value for line'||amt_array(amt_array_cnt).col_1_amt);
1968 END LOOP;
1969 END LOOP;
1970 ELSIF calc_rec.line_low_type = 'C' THEN
1971 FOR i IN 1..amt_array_cnt - 1
1972 LOOP
1973 IF amt_array(i).calc_sequence >= calc_rec.line_low
1974 AND amt_array(i).calc_sequence <= calc_rec.line_high THEN
1975 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(amt_array(i).col_1_amt, 0);
1976 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'inside for loop, value for calc sequence'||amt_array(amt_array_cnt).col_1_amt);
1977 END IF;
1978 END LOOP;
1979 END IF;
1980 END IF;
1981
1982 amt_array_cnt := amt_array_cnt + 1;
1983 END LOOP;
1984
1985 v_col_1_amt := amt_array(amt_array_cnt - 1).col_1_amt;
1986
1987 o_sf133_ts_value := c_sf133_ts_value;
1988 o_sf133_line_id := c_sf133_line_id;
1989 o_sf133_column_number := g_column_number;
1990 o_sf133_column_amount := v_col_1_amt;
1991 o_sf133_amt_not_shown := v_col_1_amt; -- Bug # 2896450
1992 o_sf133_treasury_symbol_id := c_sf133_treasury_symbol_id; --added for 1575992
1993 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'end of process_total_line: '||o_sf133_ts_value||o_sf133_column_amount);
1994
1995 populate_temp_table;
1996
1997 EXCEPTION
1998 WHEN OTHERS THEN
1999 g_error_code := SQLCODE ;
2000 g_error_message := SQLERRM || ' [PROCESS_TOTAL_LINE] ' ;
2001 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
2002 RETURN;
2003 END process_total_line;
2004
2005 -- --------------------------------------------------------
2006 -- --------------------------------------------------------
2007 BEGIN
2008 g_module_name := 'fv.plsql.FV_SF133_NOYEAR.';
2009
2010
2011 END FV_SF133_NOYEAR ;
2012