[Home] [Help]
PACKAGE BODY: APPS.XLA_TACCOUNTS_DATA_PKG
Source
1 PACKAGE BODY XLA_TACCOUNTS_DATA_PKG AS
2 /* $Header: xlatacct.pkb 120.4 2005/04/28 18:45:37 masada ship $ */
3
4 /*===========================================================================+
5 -- Forward Declarations
6 +===========================================================================*/
7
8 -- Used for debugging. Prints the line type for a given line type code.
9 FUNCTION getlinetype (p_typeid BINARY_INTEGER ,
10 p_tatb VARCHAR2 DEFAULT 'ta') RETURN VARCHAR2;
11
12 FUNCTION getTotal( p_total_amount IN NUMBER
13 ,p_current_amount IN NUMBER
14 ) RETURN NUMBER;
15
16 FUNCTION getStatement (
17 p_Application_id IN NUMBER
18 ,p_Trx_Header_Table IN VARCHAR2
19 ,p_Trx_Header_ID IN NUMBER
20 ,p_Cost_Type_ID IN NUMBER
21 ,p_Organize_By IN VARCHAR2 -- ACCOUNT | SEGMENT
22 ,p_Segment1 IN NUMBER
23 ,p_Segment2 IN NUMBER
24 ,p_OverRidingWhereClause IN VARCHAR2
25 ,p_viewName IN VARCHAR2
26 ,p_add_col_name_1 IN VARCHAR2 DEFAULT NULL
27 ,p_add_col_value_1 IN VARCHAR2 DEFAULT NULL
28 ,p_add_col_name_2 IN VARCHAR2 DEFAULT NULL
29 ,p_add_col_value_2 IN VARCHAR2 DEFAULT NULL
30 )
31 RETURN VARCHAR2;
32
33
34 PROCEDURE getBalances(balanceDr OUT NOCOPY NUMBER
35 ,balanceCr OUT NOCOPY NUMBER
36 ,codeCombinationsID IN NUMBER
37 ,periodName IN VARCHAR2
38 ,accountType IN VARCHAR2);
39
40 PROCEDURE getAccountBalance( p_amount IN NUMBER
41 ,p_trx_hdr_id IN NUMBER
42 ,p_amount_dr OUT NOCOPY NUMBER
43 ,p_amount_cr OUT NOCOPY NUMBER
44 ,p_Ccid IN NUMBER
45 ,p_account_type IN VARCHAR2
46 );
47
48 PROCEDURE getNetBalance(p_AccountedDr IN OUT NOCOPY NUMBER
49 ,p_AccountedCr IN OUT NOCOPY NUMBER
50 ,p_AccountType IN VARCHAR2);
51
52
53 FUNCTION getReportingCurrency RETURN VARCHAR2;
54
55 FUNCTION secure( p_CCID IN NUMBER
56 ,p_TATB IN VARCHAR2)
57 RETURN BOOLEAN;
58
59
60 /*===========================================================================+
61 -- Private Data types
62 +===========================================================================*/
63
64 /*===========================================================================+
65 -- Private Variables
66 +===========================================================================*/
67 prv_OrganizeBy VARCHAR2(8);
68 prv_ChartOfAccountsID NUMBER;
69 prv_Segment1 NUMBER;
70 prv_Segment2 NUMBER;
71 prv_FlexDelimiter VARCHAR2(30);
72 prv_ApplicationId NUMBER;
73 prv_Trx_Hdr_id NUMBER;
74
75 prv_SetOfBooksID NUMBER;
76
77 c_ta NUMBER; -- Cursor for TAccounts
78 c_tb Number; -- Cursor for Trial Balance
79
80 -- Global variables for T-Accounts
81
82 -- Currency Totals
83 g_totalEnteredCurDr NUMBER := 0;
84 g_totalEnteredCurCr NUMBER := 0;
85 g_totalAccountedCurDr NUMBER := 0;
86 g_totalAccountedCurCr NUMBER := 0;
87 g_totalReportingCurDr NUMBER := 0;
88 g_totalReportingCurCr NUMBER := 0;
89
90 -- Account Totals
91 g_totalEntCcidDr NUMBER := 0;
92 g_totalEntCcidCr NUMBER := 0;
93 g_totalAcctCcidDr NUMBER := 0;
94 g_totalAcctCcidCr NUMBER := 0;
95 g_totalReportingCcidDr NUMBER := 0;
96 g_totalReportingCcidCr NUMBER := 0;
97
98 -- Report Totals
99 g_totalNetAccountedDr NUMBER := 0;
100 g_totalNetAccountedCr NUMBER := 0;
101 g_totalNetReportingDr NUMBER := 0;
102 g_totalNetReportingCr NUMBER := 0;
103
104 g_openingBalanceDr NUMBER := 0;
105 g_openingBalanceCr NUMBER := 0;
106 g_closingBalanceDr NUMBER := 0;
107 g_closingBalanceCr NUMBER := 0;
108
109 g_currency_cnt NUMBER := 0;
110 g_currency_code FND_CURRENCIES.currency_code%TYPE;
111 g_account_type GL_CODE_COMBINATIONS.account_type%TYPE;
112 g_period_name VARCHAR2(30);
113 g_current_account VARCHAR2(255);
114 g_current_Ccid NUMBER;
115 g_accountingCurrency FND_CURRENCIES.currency_code%TYPE;
116 g_reportingCurrency FND_CURRENCIES.currency_code%TYPE;
117
118 g_firstRow BOOLEAN;
119 g_validateflex BOOLEAN;
120 g_secure BOOLEAN;
121 g_disp_segments VARCHAR2(30);
122 g_segment_values VARCHAR2(1000);
123 g_segments_desc VARCHAR2(2000);
124 g_current_segment_values VARCHAR2(1000);
125 g_current_segments_desc VARCHAR2(2000);
126
127 -- Global variables for Trial Balance
128 -- Currency Totals
129 g_tb_totalEnteredCurDr NUMBER := 0;
130 g_tb_totalEnteredCurCr NUMBER := 0;
131 g_tb_totalAccountedCurDr NUMBER := 0;
132 g_tb_totalAccountedCurCr NUMBER := 0;
133 g_tb_totalReportingCurDr NUMBER := 0;
134 g_tb_totalReportingCurCr NUMBER := 0;
135
136 -- Account Totals
137 g_tb_totalEntCcidDr NUMBER := 0;
138 g_tb_totalEntCcidCr NUMBER := 0;
139 g_tb_totalAcctCcidDr NUMBER := 0;
140 g_tb_totalAcctCcidCr NUMBER := 0;
141 g_tb_totalReportingCcidDr NUMBER := 0;
142 g_tb_totalReportingCcidCr NUMBER := 0;
143
144 -- Report Totals
145 g_tb_totalNetAccountedDr NUMBER := 0;
146 g_tb_totalNetAccountedCr NUMBER := 0;
147 g_tb_totalNetReportingDr NUMBER := 0;
148 g_tb_totalNetReportingCr NUMBER := 0;
149
150 g_tb_openingBalanceDr NUMBER := 0;
151 g_tb_openingBalanceCr NUMBER := 0;
152 g_tb_closingBalanceDr NUMBER := 0;
153 g_tb_closingBalanceCr NUMBER := 0;
154
155 g_tb_currency_cnt NUMBER := 0;
156 g_tb_currency_code FND_CURRENCIES.currency_code%TYPE;
157 g_tb_account_type GL_CODE_COMBINATIONS.account_type%TYPE;
158 g_tb_period_name VARCHAR2(30);
159 g_tb_current_account VARCHAR2(255);
160 g_tb_current_Ccid NUMBER;
161
162 g_tb_firstRow BOOLEAN ;
163 g_tb_validateflex BOOLEAN;
164 g_tb_secure BOOLEAN;
165 g_tb_disp_segments VARCHAR2(30);
166 g_tb_segment_values VARCHAR2(1000);
167 g_tb_segments_desc VARCHAR2(2000);
168 g_tb_current_segment_values VARCHAR2(1000);
169 g_tb_current_segments_desc VARCHAR2(2000);
170 --=============================================================================
171 -- *********** Local Trace Routine **********
172 --=============================================================================
173 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
174 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
175 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
176 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
177 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
178 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
179
180 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
181
182 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.XLA_TACCOUNTS_DATA_PKG';
183
184 g_debug_flag VARCHAR2(1) := NVL(fnd_profile.value('XLA_DEBUG_TRACE'),'N');
185
186 --l_log_module VARCHAR2(240);
187 g_log_level NUMBER;
188 g_log_enabled BOOLEAN;
189
190 PROCEDURE trace
191 (p_msg IN VARCHAR2
192 ,p_level IN NUMBER
193 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
194 BEGIN
195 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
196 fnd_log.message(p_level, p_module);
197 ELSIF p_level >= g_log_level THEN
198 fnd_log.string(p_level, p_module, p_msg);
199 END IF;
200
201 EXCEPTION
202 WHEN xla_exceptions_pkg.application_exception THEN
203 RAISE;
204 WHEN OTHERS THEN
205 xla_exceptions_pkg.raise_message
206 (p_location => 'XLA_TACCOUNTS_DATA_PKG.trace');
207 END trace;
208
209 /*===========================================================================+
210 -- Procedure/functions
211 +===========================================================================*/
212
213 FUNCTION xla_supported(p_application_id in NUMBER)
214 RETURN NUMBER
215 is
216 l_log_module VARCHAR2(240);
217 temp number;
218 cursor c_xla_subledgers is
219 select application_id
220 from xla_subledgers
221 where application_id=p_application_id;
222 begin
223 IF g_log_enabled THEN
224 l_log_module := C_DEFAULT_MODULE||'.xla_supported';
225 END IF;
226 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
227 trace
228 (p_msg => 'BEGIN of procedure xla_supported'
229 ,p_level => C_LEVEL_PROCEDURE
230 ,p_module =>l_log_module);
231 trace
232 (p_msg => 'p_application_id = ' ||TO_CHAR(p_application_id)
233 ,p_level => C_LEVEL_PROCEDURE
234 ,p_module =>l_log_module);
235 END IF;
236
237
238 open c_xla_subledgers;
239 fetch c_xla_subledgers into temp;
240 if c_xla_subledgers%FOUND then
241 temp:=1;
242 else
243 temp:=0;
244 end if;
245 close c_xla_subledgers;
246
247 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
248 trace
249 (p_msg => 'return value. = '||TO_CHAR(temp)
250 ,p_level => C_LEVEL_PROCEDURE
251 ,p_module =>l_log_module);
252 trace
253 (p_msg => 'END of function xla_supported'
254 ,p_level => C_LEVEL_PROCEDURE
255 ,p_module =>l_log_module);
256 END IF;
257
258 return temp;
259
260 end xla_supported;
261
262 /*===========================================================================+
263 | PROCEDURE |
264 | Init |
265 | |
266 | DESCRIPTION |
267 | |
268 | SCOPE - PUBLIC |
269 | |
270 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
271 | |
272 | ARGUMENTS : IN: p_Application_Id -- E.g 222 for Receivables |
273 | |
274 | NOTES |
275 | |
276 | MODIFICATION HISTORY |
277 | 14-Sep-98 Dirk Stevens Created |
278 | 04-Aug-99 Mahesh Sabapthy Added parameter cost_type_id to |
279 | support Mfg. PAC transactions. |
280 +===========================================================================*/
281 PROCEDURE TA_Init (
282 p_Application_ID IN NUMBER
283 ,p_Trx_Header_Table IN VARCHAR2
284 ,p_Trx_Header_ID IN NUMBER
285 ,p_Cost_Type_ID IN NUMBER
286 ,p_Chart_Of_Accounts_Id IN NUMBER
287 ,p_Set_Of_Books_ID IN NUMBER
288 ,p_Organize_By IN VARCHAR2
289 ,p_Segment1 IN NUMBER
290 ,p_Segment2 IN NUMBER
291 ,p_OverRidingWhereClause IN VARCHAR2
292 ,p_viewName IN VARCHAR2
293 ,p_add_col_name_1 IN VARCHAR2 DEFAULT NULL
294 ,p_add_col_value_1 IN VARCHAR2 DEFAULT NULL
295 ,p_add_col_name_2 IN VARCHAR2 DEFAULT NULL
296 ,p_add_col_value_2 IN VARCHAR2 DEFAULT NULL
297 ) IS
298
299 rows INTEGER;
300 statement VARCHAR2(32000);
301
302 l_ccid NUMBER;
303 l_account VARCHAR2(255);
304 l_segment1_value VARCHAR2(255);
305 l_segment2_value VARCHAR2(255);
306 l_segment3_value VARCHAR2(255);
307 l_segment4_value VARCHAR2(255);
308 l_ae_line_ref VARCHAR2(1000);
309 l_ae_line_ref_int VARCHAR2(1000);
310 l_entered_currency VARCHAR2(15);
311 l_entered_dr NUMBER;
312 l_entered_cr NUMBER;
313 l_acctd_dr NUMBER;
314 l_acctd_cr NUMBER;
315 l_report_dr NUMBER;
316 l_report_cr NUMBER;
317 l_period_name VARCHAR2(15); -- Applicable only to GL
318 l_status VARCHAR2(1); -- Applicable only to GL
319 l_account_type VARCHAR2(1);
320 l_log_module VARCHAR2(240);
321
322 BEGIN
323 IF g_log_enabled THEN
324 l_log_module := C_DEFAULT_MODULE||'.TA_INIT';
325 END IF;
326 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
327 trace
328 (p_msg => 'BEGIN of procedure TA_INIT'
329 ,p_level => C_LEVEL_PROCEDURE
330 ,p_module =>l_log_module);
331 END IF;
332
333
334 /* Set all private globals */
335 prv_OrganizeBy := p_Organize_By;
336 prv_ApplicationId := p_Application_Id;
337 prv_Trx_Hdr_Id := p_Trx_Header_Id;
338 g_currency_cnt := 0;
339 g_firstRow := Null;
340 g_secure := FALSE;
341
342 -- Initialize totals
343
344 -- Currency Totals
345 g_totalEnteredCurDr := null;
346 g_totalEnteredCurCr := null;
347 g_totalAccountedCurDr := null;
348 g_totalAccountedCurCr := null;
349 g_totalReportingCurDr := null;
350 g_totalReportingCurCr := null;
351
352 -- Account Totals
353 g_totalEntCcidDr := null;
354 g_totalEntCcidCr := null;
355 g_totalAcctCcidDr := null;
356 g_totalAcctCcidCr := null;
357 g_totalReportingCcidDr := null;
358 g_totalReportingCcidCr := null;
359
360 -- Report Totals
361 g_totalNetAccountedDr := 0;
362 g_totalNetAccountedCr := 0;
363 g_totalNetReportingDr := 0;
364 g_totalNetReportingCr := 0;
365
366
367 g_currency_cnt := 0;
368
369 prv_ChartOfAccountsID := p_Chart_Of_Accounts_ID;
370 prv_Segment1 := p_Segment1;
371 prv_Segment2 := p_Segment2;
372
373 prv_SetOfBooksID := p_Set_Of_Books_ID;
374
375 g_accountingCurrency := getAccountingCurrency(prv_SetOfBooksID);
376 g_reportingCurrency := getReportingCurrency;
377
378 statement := getStatement( p_Application_ID
379 ,p_Trx_Header_Table
380 ,p_Trx_Header_ID
381 ,p_Cost_Type_ID
382 ,p_Organize_by
383 ,p_Segment1
384 ,p_Segment2
385 ,p_OverRidingWhereClause
386 ,p_viewName
387 ,p_add_col_name_1
388 ,p_add_col_value_1
389 ,p_add_col_name_2
390 ,p_add_col_value_2
391 );
392 c_ta := dbms_sql.open_cursor;
393 dbms_sql.parse(c_ta, statement, dbms_sql.native);
394
395 -- Generic bind
396 dbms_sql.bind_variable(c_ta, 'appl_id', p_application_id);
397 dbms_sql.bind_variable(c_ta, 'set_of_books_id', p_set_of_books_id);
398
399 -- Transaction specific bind
400 IF ( p_overRidingWhereClause IS NULL ) THEN
401 IF ( p_add_col_name_1 IS NOT NULL ) THEN
402 dbms_sql.bind_variable(c_ta, 'add_col_value_1', p_add_col_value_1);
403 END IF;
404
405 IF ( p_add_col_name_2 IS NOT NULL ) THEN
406 dbms_sql.bind_variable(c_ta, 'add_col_value_2', p_add_col_value_2);
407 END IF;
408
409 IF ( p_trx_header_table IS NOT NULL ) THEN
410 dbms_sql.bind_variable(c_ta, 'trx_header_table', p_trx_header_table);
411 END IF;
412
413 IF ( p_trx_header_id IS NOT NULL ) THEN
414 dbms_sql.bind_variable(c_ta, 'trx_header_id', p_trx_header_id);
415 END IF;
416
417 -- Mfg. PAC transactions support
418 IF ( p_Cost_Type_ID IS NOT NULL ) THEN
419 dbms_sql.bind_variable(c_ta, 'cost_type_id', p_cost_type_id);
420 END IF;
421
422 END IF;
423
424 -- Define columns
425 dbms_sql.define_column( c_ta, 1, l_ccid );
426 dbms_sql.define_column( c_ta, 2, l_segment1_value, 255 );
427 dbms_sql.define_column( c_ta, 3, l_segment2_value, 255 );
428 dbms_sql.define_column( c_ta, 4, l_segment3_value, 255 );
429 dbms_sql.define_column( c_ta, 5, l_segment4_value, 255 );
430 dbms_sql.define_column( c_ta, 6, l_ae_line_ref, 1000 );
431 dbms_sql.define_column( c_ta, 7, l_ae_line_ref_int, 1000 );
432 dbms_sql.define_column( c_ta, 8, l_entered_currency, 15 );
433 dbms_sql.define_column( c_ta, 9, l_entered_dr );
434 dbms_sql.define_column( c_ta, 10, l_entered_cr );
435 dbms_sql.define_column( c_ta, 11, l_acctd_dr );
436 dbms_sql.define_column( c_ta, 12, l_acctd_cr );
437 dbms_sql.define_column( c_ta, 13, l_report_dr );
438 dbms_sql.define_column( c_ta, 14, l_report_cr );
439 dbms_sql.define_column( c_ta, 15, l_period_name, 15 );
440 dbms_sql.define_column( c_ta, 16, l_status , 1 );
441 dbms_sql.define_column( c_ta, 17, l_account_type , 1 );
442
443 rows := dbms_sql.execute(c_ta);
444 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
445 trace
446 (p_msg => 'end of procedure TA_INIT'
447 ,p_level => C_LEVEL_PROCEDURE
448 ,p_module =>l_log_module);
449 END IF;
450
451
452 END TA_Init;
453
454 PROCEDURE TB_Init (
455 p_Application_ID IN NUMBER
456 ,p_Trx_Header_Table IN VARCHAR2
457 ,p_Trx_Header_ID IN NUMBER
458 ,p_Cost_Type_ID IN NUMBER
459 ,p_Chart_Of_Accounts_Id IN NUMBER
460 ,p_Set_Of_Books_ID IN NUMBER
461 ,p_Organize_By IN VARCHAR2
462 ,p_Segment1 IN NUMBER
463 ,p_Segment2 IN NUMBER
464 ,p_OverRidingWhereClause IN VARCHAR2
465 ,p_viewName IN VARCHAR2
466 ,p_add_col_name_1 IN VARCHAR2 DEFAULT NULL
467 ,p_add_col_value_1 IN VARCHAR2 DEFAULT NULL
468 ,p_add_col_name_2 IN VARCHAR2 DEFAULT NULL
469 ,p_add_col_value_2 IN VARCHAR2 DEFAULT NULL
470 ) IS
471
472 rows INTEGER;
473 statement VARCHAR2(32000);
474
475 l_ccid NUMBER;
476 l_account VARCHAR2(255);
477 l_segment1_value VARCHAR2(255);
478 l_segment2_value VARCHAR2(255);
479 l_segment3_value VARCHAR2(255);
480 l_segment4_value VARCHAR2(255);
481 l_ae_line_ref VARCHAR2(1000);
482 l_ae_line_ref_int VARCHAR2(1000);
483 l_entered_currency VARCHAR2(15);
484 l_entered_dr NUMBER;
485 l_entered_cr NUMBER;
486 l_acctd_dr NUMBER;
487 l_acctd_cr NUMBER;
488 l_report_dr NUMBER;
489 l_report_cr NUMBER;
490 l_period_name VARCHAR2(15); -- Applicable only to GL
491 l_status VARCHAR2(1); -- Applicable only to GL
492 l_account_type VARCHAR2(1);
493 l_log_module VARCHAR2(240);
494
495
496 BEGIN
497 IF g_log_enabled THEN
498 l_log_module := C_DEFAULT_MODULE||'.TB_INIT';
499 END IF;
500 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
501 trace
502 (p_msg => 'BEGIN of procedure TB_INIT'
503 ,p_level => C_LEVEL_PROCEDURE
504 ,p_module =>l_log_module);
505 END IF;
506 /* Set all private globals */
507 prv_OrganizeBy := p_Organize_By;
508 prv_ApplicationId := p_Application_Id;
509 prv_Trx_Hdr_Id := p_Trx_Header_Id;
510 g_tb_currency_cnt := 0;
511 g_tb_firstrow := NULL;
512 g_tb_secure := FALSE;
513
514
515 -- Initialize Varibles
516 g_tb_totalEnteredCurDr := null;
517 g_tb_totalEnteredCurCr := null;
518 g_tb_totalAccountedCurDr := null;
519 g_tb_totalAccountedCurCr := null;
520 g_tb_totalReportingCurDr := null;
521 g_tb_totalReportingCurCr := null;
522
523 -- Account Totals
524 g_tb_totalEntCcidDr := null;
525 g_tb_totalEntCcidCr := null;
526 g_tb_totalAcctCcidDr := null;
527 g_tb_totalAcctCcidCr := null;
528 g_tb_totalReportingCcidDr := null;
529 g_tb_totalReportingCcidCr := null;
530
531 -- Report Totals
532 g_tb_totalNetAccountedDr := 0;
533 g_tb_totalNetAccountedCr := 0;
534 g_tb_totalNetReportingDr := 0;
535 g_tb_totalNetReportingCr := 0;
536
537 g_tb_openingBalanceDr := 0;
538 g_tb_openingBalanceCr := 0;
539 g_tb_closingBalanceDr := 0;
540 g_tb_closingBalanceCr := 0;
541
542 g_tb_currency_cnt := 0;
543
544
545
546 prv_ChartOfAccountsID := p_Chart_Of_Accounts_ID;
547 prv_Segment1 := p_Segment1;
548 prv_Segment2 := p_Segment2;
549
550 prv_SetOfBooksID := p_Set_Of_Books_ID;
551
552 g_accountingCurrency := getAccountingCurrency(prv_SetOfBooksID);
553 g_reportingcurrency := getReportingCurrency;
554
555 statement := getStatement( p_Application_ID
556 ,p_Trx_Header_Table
557 ,p_Trx_Header_ID
558 ,p_Cost_Type_ID
559 ,p_Organize_by
560 ,p_Segment1
561 ,p_Segment2
562 ,p_OverRidingWhereClause
563 ,p_viewName
564 ,p_add_col_name_1
565 ,p_add_col_value_1
566 ,p_add_col_name_2
567 ,p_add_col_value_2
568 );
569 c_tb := dbms_sql.open_cursor;
570 dbms_sql.parse(c_tb, statement, dbms_sql.native);
571
572 -- Generic bind
573 dbms_sql.bind_variable(c_tb, 'appl_id', p_application_id);
574 dbms_sql.bind_variable(c_tb, 'set_of_books_id', p_set_of_books_id);
575
576 -- Transaction specific bind
577 IF ( p_overRidingWhereClause IS NULL )
578 THEN
579 IF ( p_add_col_name_1 IS NOT NULL ) THEN
580 dbms_sql.bind_variable(c_tb, 'add_col_value_1', p_add_col_value_1);
581 END IF;
582
583 IF ( p_add_col_name_2 IS NOT NULL ) THEN
584 dbms_sql.bind_variable(c_tb, 'add_col_value_2', p_add_col_value_2);
585 END IF;
586
587 IF ( p_trx_header_table IS NOT NULL ) THEN
588 dbms_sql.bind_variable(c_tb, 'trx_header_table', p_trx_header_table);
589 END IF;
590
591 IF ( p_trx_header_id IS NOT NULL ) THEN
592 dbms_sql.bind_variable(c_tb, 'trx_header_id', p_trx_header_id);
593 END IF;
594
595 -- Mfg. PAC transactions support
596 IF ( p_Cost_Type_ID IS NOT NULL ) THEN
597 dbms_sql.bind_variable(c_tb, 'cost_type_id', p_cost_type_id);
598 END IF;
599
600 END IF;
601
602 -- Define columns
603 dbms_sql.define_column( c_tb, 1, l_ccid );
604 dbms_sql.define_column( c_tb, 2, l_segment1_value, 255 );
605 dbms_sql.define_column( c_tb, 3, l_segment2_value, 255 );
606 dbms_sql.define_column( c_tb, 4, l_segment3_value, 255 );
607 dbms_sql.define_column( c_tb, 5, l_segment4_value, 255 );
608 dbms_sql.define_column( c_tb, 6, l_ae_line_ref, 1000 );
609 dbms_sql.define_column( c_tb, 7, l_ae_line_ref_int, 1000 );
610 dbms_sql.define_column( c_tb, 8, l_entered_currency, 15 );
611 dbms_sql.define_column( c_tb, 9, l_entered_dr );
612 dbms_sql.define_column( c_tb, 10, l_entered_cr );
613 dbms_sql.define_column( c_tb, 11, l_acctd_dr );
614 dbms_sql.define_column( c_tb, 12, l_acctd_cr );
615 dbms_sql.define_column( c_tb, 13, l_report_dr );
616 dbms_sql.define_column( c_tb, 14, l_report_cr );
617 dbms_sql.define_column( c_tb, 15, l_period_name, 15 );
618 dbms_sql.define_column( c_tb, 16, l_status , 1 );
619 dbms_sql.define_column( c_tb, 17, l_account_type , 1 );
620
621 rows := dbms_sql.execute(c_tb);
622 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
623 trace
624 (p_msg => 'end of procedure TB_INIT'
625 ,p_level => C_LEVEL_PROCEDURE
626 ,p_module =>l_log_module);
627 END IF;
628
629 END TB_Init;
630
631
632 /*===========================================================================+
633 | FUNCTION |
634 | getStatement |
635 | |
636 | DESCRIPTION |
637 | Get the SQL statement to execute based on the parameters passed. |
638 | |
639 | MODIFICATION HISTORY |
640 | 14-Sep-98 Dirk Stevens Created |
641 | 04-Aug-99 Mahesh Sabapthy Added parameter cost_type_id to |
642 | support Mfg. PAC transactions. |
643 +===========================================================================*/
644 FUNCTION getStatement (
645 p_Application_id IN NUMBER
646 ,p_Trx_Header_Table IN VARCHAR2
647 ,p_Trx_Header_ID IN NUMBER
648 ,p_Cost_Type_ID IN NUMBER
649 ,p_Organize_By IN VARCHAR2 -- ACCOUNT | SEGMENT
650 ,p_Segment1 IN NUMBER
651 ,p_Segment2 IN NUMBER
652 ,p_OverRidingWhereClause IN VARCHAR2
653 ,p_viewName IN VARCHAR2
654 ,p_add_col_name_1 IN VARCHAR2 DEFAULT NULL
655 ,p_add_col_value_1 IN VARCHAR2 DEFAULT NULL
656 ,p_add_col_name_2 IN VARCHAR2 DEFAULT NULL
657 ,p_add_col_value_2 IN VARCHAR2 DEFAULT NULL
658 ) RETURN VARCHAR2 IS
659
660 l_select_clause VARCHAR2(2000);
661 l_ordered_account VARCHAR2(2000);
662 l_from_clause VARCHAR2(2000);
663 l_where_clause VARCHAR2(32000);
664 l_group_by_clause VARCHAR2(2000);
665 l_order_by_clause VARCHAR2(2000);
666 l_statement VARCHAR2(32000);
667
668 l_flex_appl_id CONSTANT NUMBER := 101;
669 l_id_flex_code CONSTANT VARCHAR2(10) := 'GL#';
670 l_flex_delimiter VARCHAR2(10);
671 l_segment1_column VARCHAR2(30);
672 l_segment2_column VARCHAR2(30);
673 l_segment3_column VARCHAR2(30);
674 l_segment4_column VARCHAR2(30);
675 l_seg_name VARCHAR2(30);
676 l_prompt VARCHAR2(50);
677 l_value_set_name VARCHAR2(50);
678 l_dummy_flex_ret_value BOOLEAN;
679 l_parentseg_num NUMBER;
680 l_parentseg_column VARCHAR2(30);
681
682 l_segmentinfo xla_flex_util.t_segmentinfo;
683 l_log_module VARCHAR2(240);
684
685 BEGIN
686 IF g_log_enabled THEN
687 l_log_module := C_DEFAULT_MODULE||'.getStatement';
688 END IF;
689 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
690 trace
691 (p_msg => 'BEGIN of function getStatement'
692 ,p_level => C_LEVEL_PROCEDURE
693 ,p_module =>l_log_module);
694 END IF;
695
696 -- Build Select clause
697 IF p_organize_by = 'ACCOUNT' THEN
698 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
699 trace
700 (p_msg => 'By Account'
701 ,p_level => C_LEVEL_STATEMENT
702 ,p_module =>l_log_module);
703 END IF;
704
705 -- Get description for all segments
706 g_disp_segments := 'ALL';
707
708 IF ( p_Application_id <> 101 ) THEN
709
710 l_select_clause := 'SELECT
711 AEL_V.CODE_COMBINATION_ID CC_ID
712 ,TO_CHAR(AEL_V.CODE_COMBINATION_ID) SEGMENT1_VALUE
713 ,TO_CHAR(NULL) SEGMENT2_VALUE
714 ,TO_CHAR(NULL) SEGMENT3_VALUE
715 ,TO_CHAR(NULL) SEGMENT4_VALUE
716 ,AEL_V.AE_LINE_REFERENCE AE_LINE_REF
717 ,AEL_V.AE_LINE_REFERENCE_INTERNAL AE_LINE_REF_INT
718 ,AEL_V.CURRENCY_CODE CURRENCY_CODE
719 ,AEL_V.ENTERED_DR ENTERED_DR
720 ,AEL_V.ENTERED_CR ENTERED_CR
721 ,AEL_V.ACCOUNTED_DR ACCOUNTED_DR
722 ,AEL_V.ACCOUNTED_CR ACCOUNTED_CR
723 ,TO_NUMBER(NULL) REPORT_DR
724 ,TO_NUMBER(NULL) REPORT_CR
725 ,TO_CHAR(NULL) PERIOD_NAME
726 ,TO_CHAR(NULL) STATUS
727 ,GLCC.ACCOUNT_TYPE ACCOUNT_TYPE ';
728 ELSE
729
730 l_select_clause := 'SELECT
731 AEL_V.CODE_COMBINATION_ID CC_ID
732 ,TO_CHAR(AEL_V.CODE_COMBINATION_ID) SEGMENT1_VALUE
733 ,TO_CHAR(NULL) SEGMENT2_VALUE
734 ,TO_CHAR(NULL) SEGMENT3_VALUE
735 ,TO_CHAR(NULL) SEGMENT4_VALUE
736 ,AEL_V.AE_LINE_REFERENCE AE_LINE_REF
737 ,AEL_V.AE_LINE_REFERENCE_INTERNAL AE_LINE_REF_INT
738 ,AEL_V.CURRENCY_CODE CURRENCY_CODE
739 ,AEL_V.ENTERED_DR ENTERED_DR
740 ,AEL_V.ENTERED_CR ENTERED_CR
741 ,AEL_V.ACCOUNTED_DR ACCOUNTED_DR
742 ,AEL_V.ACCOUNTED_CR ACCOUNTED_CR
743 ,TO_NUMBER(NULL) REPORT_DR
744 ,TO_NUMBER(NULL) REPORT_CR
745 ,AEL_V.PERIOD_NAME PERIOD_NAME
746 ,AEL_V.STATUS STATUS
747 ,GLCC.ACCOUNT_TYPE ACCOUNT_TYPE ';
748 END IF;
749
750 ELSE
751
752 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
753 trace
754 (p_msg => 'By Segment'
755 ,p_level => C_LEVEL_STATEMENT
756 ,p_module =>l_log_module);
757 END IF;
758 -- Get Segment Delimiter
759 prv_FlexDelimiter := FND_FLEX_APIS.GET_SEGMENT_DELIMITER(
760 l_flex_appl_id
761 ,l_id_flex_code
762 ,prv_ChartOfAccountsID );
763
764 -- Get parent segment for first segment
765 xla_flex_util.get_parent_segment(l_flex_appl_id,
766 l_id_flex_code,
767 prv_chartofaccountsid,
768 p_segment1,
769 l_parentseg_num,
770 l_parentseg_column
771 );
772
773 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
774 trace
775 (p_msg => 'l_parentseg_num ' || l_parentseg_num
776 ,p_level => C_LEVEL_STATEMENT
777 ,p_module =>l_log_module);
778 END IF;
779 -- xla_util.debug('l_parentseg_num ' || l_parentseg_num);
780 IF l_parentseg_num IS NOT NULL THEN
781 l_dummy_flex_ret_value := FND_FLEX_APIS.GET_SEGMENT_INFO(
782 l_flex_appl_id
783 ,l_id_flex_code
784 ,prv_ChartOfAccountsID
785 ,l_parentseg_num
786 ,l_segment1_column
787 ,l_seg_name
788 ,l_prompt
789 ,l_value_set_name);
790 END IF;
791
792 -- Call API to get flexfield info
793 IF ( NOT XLA_FLEX_UTIL.getSegmentInfo(prv_chartofaccountsid,
794 l_segmentinfo)) THEN
795 APP_EXCEPTION.RAISE_EXCEPTION;
796 END IF;
797
798 IF p_segment1 = l_parentseg_num THEN
799 g_disp_segments := l_segmentInfo(l_parentseg_num).segment_ordernum;
800 ELSE
801 g_disp_segments := l_segmentInfo(l_parentseg_num).segment_ordernum || '\0' ||
802 l_segmentInfo(p_segment1).segment_ordernum;
803 END IF;
804
805
806 l_dummy_flex_ret_value := FND_FLEX_APIS.GET_SEGMENT_INFO(
807 l_flex_appl_id
808 ,l_id_flex_code
809 ,prv_ChartOfAccountsID
810 ,p_segment1
811 ,l_segment2_column
812 ,l_seg_name
813 ,l_prompt
814 ,l_value_set_name);
815
816 xla_flex_util.get_parent_segment(l_flex_appl_id,
817 l_id_flex_code,
818 prv_chartofaccountsid,
819 p_segment2,
820 l_parentseg_num,
821 l_parentseg_column
822 );
823 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
824 trace
825 (p_msg =>'p_segment2 ' || p_segment2
826 ,p_level => C_LEVEL_STATEMENT
827 ,p_module =>l_log_module);
828 END IF;
829 -- xla_util.debug('p_segment2 ' || p_segment2);
830 IF l_parentseg_num IS NOT NULL THEN
831 l_dummy_flex_ret_value := FND_FLEX_APIS.GET_SEGMENT_INFO(
832 l_flex_appl_id
833 ,l_id_flex_code
834 ,prv_ChartOfAccountsID
835 ,l_parentseg_num
836 ,l_segment3_column
837 ,l_seg_name
838 ,l_prompt
839 ,l_value_set_name);
840 END IF;
841
842 l_dummy_flex_ret_value := FND_FLEX_APIS.GET_SEGMENT_INFO(
843 l_flex_appl_id
844 ,l_id_flex_code
845 ,prv_ChartOfAccountsID
846 ,p_segment2
847 ,l_segment4_column
848 ,l_seg_name
849 ,l_prompt
850 ,l_value_set_name);
851
852 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
853 trace
854 (p_msg =>'segments ' || l_segment1_column || l_segment2_column || l_segment3_column || l_segment4_column
855 ,p_level => C_LEVEL_STATEMENT
856 ,p_module =>l_log_module);
857 END IF;
858 -- xla_util.debug('segments ' || l_segment1_column || l_segment2_column || l_segment3_column || l_segment4_column);
859
860 IF p_segment2 = l_parentseg_num THEN
861 IF l_segment3_column NOT IN (l_segment1_column, l_segment2_column) THEN
862 g_disp_segments := g_disp_segments || '\0'||
863 l_segmentInfo(l_parentseg_num).segment_ordernum;
864 END IF;
865 ELSE
866 IF l_segment4_column not in (l_segment1_column, l_segment2_column,l_segment3_column) THEN
867 g_disp_segments := g_disp_segments || '\0'||
868 l_segmentInfo(l_parentseg_num).segment_ordernum
869 || '\0' || l_segmentInfo(p_segment2).segment_ordernum;
870 END IF;
871 END IF;
872
873 -- Build Select Clause
874 IF ( p_application_id <> 101 ) THEN
875 l_select_clause := 'SELECT
876 AEL_V.CODE_COMBINATION_ID CC_ID
877 ,GLCC.'||l_segment1_column||' SEGMENT1_VALUE
878 ,GLCC.'||l_segment2_column||' SEGMENT2_VALUE
879 ,GLCC.'||l_segment3_column||' SEGMENT3_VALUE
880 ,GLCC.'||l_segment4_column||' SEGMENT4_VALUE
881 ,AEL_V.AE_LINE_REFERENCE AE_LINE_REF
882 ,AEL_V.AE_LINE_REFERENCE_INTERNAL AE_LINE_REF_INT
883 ,AEL_V.CURRENCY_CODE CURRENCY_CODE
884 ,AEL_V.ENTERED_DR ENTERED_DR
885 ,AEL_V.ENTERED_CR ENTERED_CR
886 ,AEL_V.ACCOUNTED_DR ACCOUNTED_DR
887 ,AEL_V.ACCOUNTED_CR ACCOUNTED_CR
888 ,TO_NUMBER(NULL) REPORT_DR
889 ,TO_NUMBER(NULL) REPORT_CR
890 ,TO_CHAR(NULL) PERIOD_NAME
891 ,TO_CHAR(NULL) STATUS
892 ,GLCC.ACCOUNT_TYPE ACCOUNT_TYPE ';
893 ELSE
894 l_select_clause := 'SELECT
895 AEL_V.CODE_COMBINATION_ID CC_ID
896 ,GLCC.'||l_segment1_column||' SEGMENT1_VALUE
897 ,GLCC.'||l_segment2_column||' SEGMENT2_VALUE
898 ,GLCC.'||l_segment3_column||' SEGMENT3_VALUE
899 ,GLCC.'||l_segment4_column||' SEGMENT4_VALUE
900 ,AEL_V.AE_LINE_REFERENCE AE_LINE_REF
901 ,AEL_V.AE_LINE_REFERENCE_INTERNAL AE_LINE_REF_INT
902 ,AEL_V.CURRENCY_CODE CURRENCY_CODE
903 ,AEL_V.ENTERED_DR ENTERED_DR
904 ,AEL_V.ENTERED_CR ENTERED_CR
905 ,AEL_V.ACCOUNTED_DR ACCOUNTED_DR
906 ,AEL_V.ACCOUNTED_CR ACCOUNTED_CR
907 ,TO_NUMBER(NULL) REPORT_DR
908 ,TO_NUMBER(NULL) REPORT_CR
909 ,AEL_V.PERIOD_NAME PERIOD_NAME
910 ,AEL_V.STATUS STATUS
911 ,GLCC.ACCOUNT_TYPE ACCOUNT_TYPE ';
912 END IF;
913
914 END IF; -- Organize By ACCOUNT?
915
916 -- Build From clause
917 IF (p_Application_Id <> 101 )
918 THEN
919 l_from_clause := 'FROM GL_CODE_COMBINATIONS GLCC,'||p_viewName||' AEL_V';
920 ELSIF ( p_Application_Id = 101 ) THEN
921 l_from_clause := 'FROM GL_CODE_COMBINATIONS GLCC, XLA_GL_JE_AEL_V AEL_V';
922 END IF;
923
924 -- Build Where clause
925
926 IF ( p_OverRidingWhereClause IS NOT NULL ) THEN
927
928 -- Bugfix 969109: The where clause should filter by SOB id.
929 l_where_clause := p_OverRidingWhereClause
930 ||' AND GLCC.CODE_COMBINATION_ID = AEL_V.CODE_COMBINATION_ID'
931 ||' AND AEL_V.APPLICATION_ID = :appl_id '
932 ||' AND AEL_V.SET_OF_BOOKS_ID = :set_of_books_id ';
933
934 ELSE
935 -- Build standard where clause
936 l_where_clause := 'WHERE GLCC.CODE_COMBINATION_ID = '
937 ||' AEL_V.CODE_COMBINATION_ID '
938 ||' AND AEL_V.APPLICATION_ID = :appl_id '
939 ||' AND AEL_V.SET_OF_BOOKS_ID = :set_of_books_id ';
940
941 IF ( p_add_col_name_1 IS NOT NULL ) THEN
942 l_where_clause := l_where_clause||' AND '
943 ||p_add_col_name_1||' = :add_col_value_1 ';
944 END IF;
945
946 IF ( p_add_col_name_2 IS NOT NULL ) THEN
947 l_where_clause := l_where_clause||' AND '||p_add_col_name_2||
948 ' = :add_col_value_2 ';
949 END IF;
950
951 IF ( p_trx_header_table IS NOT NULL ) THEN
952 l_where_clause := l_where_clause||
953 ' AND AEL_V.TRX_HDR_TABLE = :trx_header_table ';
954 END IF;
955
956 IF ( p_trx_header_id IS NOT NULL ) THEN
957 l_where_clause := l_where_clause||
958 ' AND AEL_V.TRX_HDR_ID = :trx_header_id ';
959 END IF;
960
961 -- Mfg. PAC transactions support
962 IF ( p_Cost_Type_ID IS NOT NULL ) THEN
963 l_where_clause := l_where_clause||
964 ' AND AEL_V.COST_TYPE_ID = :cost_type_id';
965 END IF;
966 END IF;
967
968 /*****
969 * Build Order By clause:
970 * a) If organized by ACCOUNT
971 * - Bal Segment, Nat Acct segment, Curr, Line Ref Internal
972 * b) If organized by SEGMENT
973 * - Segment1, Segment2, Segment3, Segment4, Curr, Line Ref Int, CCID
974 *****/
975
976 IF ( p_organize_by = 'ACCOUNT' ) THEN
977 l_ordered_account :=
978 XLA_FLEX_UTIL.get_ordered_account(prv_ChartOfAccountsID, 'GLCC');
979 l_order_by_clause := 'ORDER BY '||l_ordered_account||',8,7 ';
980 ELSE
981 l_order_by_clause := 'ORDER BY 2,3,4,5,8,7,1 ';
982 END IF;
983
984 l_statement := l_select_clause ||' '||l_from_clause||' '||
985 l_where_clause ||' '||l_group_by_clause||' '||
986 l_order_by_clause;
987
988 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
989 trace
990 (p_msg => 'END of function getStatement'
991 ,p_level => C_LEVEL_PROCEDURE
992 ,p_module =>l_log_module);
993 trace
994 (p_msg => 'return value is:'|| l_statement
995 ,p_level => C_LEVEL_PROCEDURE
996 ,p_module =>l_log_module);
997 END IF;
998
999 RETURN l_statement;
1000
1001 END getStatement;
1002
1003
1004 FUNCTION getReportingCurrency RETURN VARCHAR2 IS
1005 BEGIN
1006 RETURN 'USD';
1007 END;
1008
1009
1010 FUNCTION getAccountingCurrency( pSetOfBooksID IN NUMBER)
1011 RETURN VARCHAR2
1012 IS
1013
1014 CURSOR c_currencyCode(pSetOfBooksID NUMBER)
1015 IS
1016 SELECT CURRENCY_CODE
1017 FROM GL_LEDGERS
1018 WHERE ledger_id= pSetOfBooksID;
1019
1020 currencyCodeRecord c_currencyCode%ROWTYPE;
1021
1022 returnValue VARCHAR2(50);
1023 l_log_module VARCHAR2(240);
1024
1025 BEGIN
1026 IF g_log_enabled THEN
1027 l_log_module := C_DEFAULT_MODULE||'.getAccountingCurrency';
1028 END IF;
1029 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1030 trace
1031 (p_msg => 'BEGIN of function getAccountingCurrency'
1032 ,p_level => C_LEVEL_PROCEDURE
1033 ,p_module =>l_log_module);
1034 trace
1035 (p_msg => 'p_ledger_id= ' ||TO_CHAR(pSetOfBooksID)
1036 ,p_level => C_LEVEL_PROCEDURE
1037 ,p_module =>l_log_module);
1038 END IF;
1039
1040 OPEN c_currencyCode(pSetOfBooksID);
1041 FETCH c_currencyCode INTO currencyCodeRecord;
1042 returnValue := currencyCodeRecord.CURRENCY_CODE;
1043 CLOSE c_currencyCode;
1044 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1045 trace
1046 (p_msg => 'END of function getAccountingCurrency'
1047 ,p_level => C_LEVEL_PROCEDURE
1048 ,p_module =>l_log_module);
1049 trace
1050 (p_msg => 'return value is:'||to_char(returnValue)
1051 ,p_level => C_LEVEL_PROCEDURE
1052 ,p_module =>l_log_module);
1053 END IF;
1054
1055 RETURN returnValue;
1056
1057 END;
1058
1059 FUNCTION getReportingCurrency( pSetOfBooksID IN NUMBER)
1060 RETURN VARCHAR2 IS
1061 BEGIN
1062 RETURN getAccountingCurrency(pSetOfBooksID);
1063 END;
1064
1065 FUNCTION getChartOfAccountsID( pSetOfBooksID IN NUMBER)
1066 RETURN NUMBER IS
1067
1068 CURSOR c_chartOfAccountsID(pSetOfBooksID NUMBER) IS
1069 SELECT CHART_OF_ACCOUNTS_ID
1070 FROM GL_LEDGERS
1071 WHERE ledger_id= pSetOfBooksID;
1072
1073 chartOfAccountsIDRecord c_chartOfAccountsID%ROWTYPE;
1074
1075 returnValue NUMBER;
1076 l_log_module VARCHAR2(240);
1077
1078 BEGIN
1079 IF g_log_enabled THEN
1080 l_log_module := C_DEFAULT_MODULE||'.getChartOfAccountsID';
1081 END IF;
1082 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1083 trace
1084 (p_msg => 'BEGIN of function getChartOfAccountsID'
1085 ,p_level => C_LEVEL_PROCEDURE
1086 ,p_module =>l_log_module);
1087 trace
1088 (p_msg => 'p_ledger_id= ' ||TO_CHAR(pSetOfBooksID)
1089 ,p_level => C_LEVEL_PROCEDURE
1090 ,p_module =>l_log_module);
1091 END IF;
1092
1093 OPEN c_chartOfAccountsID(pSetOfBooksID);
1094 FETCH c_chartOfAccountsID INTO chartOfAccountsIDRecord;
1095 returnValue := chartOfAccountsIDRecord.CHART_OF_ACCOUNTS_ID;
1096 CLOSE c_chartOfAccountsID;
1097
1098 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1099 trace
1100 (p_msg => 'END of function getChartOfAccountsID'
1101 ,p_level => C_LEVEL_PROCEDURE
1102 ,p_module =>l_log_module);
1103 trace
1104 (p_msg => 'return value is:'||to_char(returnValue)
1105 ,p_level => C_LEVEL_PROCEDURE
1106 ,p_module =>l_log_module);
1107 END IF;
1108
1109 RETURN returnValue;
1110
1111 END;
1112
1113 FUNCTION secure(p_CCID IN NUMBER
1114 ,p_TATB IN VARCHAR2 ) RETURN BOOLEAN IS
1115 i BINARY_INTEGER := 1;
1116 l_secure_flag VARCHAR2(1);
1117 returnValue BOOLEAN := FALSE;
1118 l_log_module VARCHAR2(240);
1119 BEGIN
1120 IF g_log_enabled THEN
1121 l_log_module := C_DEFAULT_MODULE||'.secure';
1122 END IF;
1123 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1124 trace
1125 (p_msg => 'BEGIN of function secure'
1126 ,p_level => C_LEVEL_PROCEDURE
1127 ,p_module =>l_log_module);
1128 trace
1129 (p_msg => 'ccid= ' ||TO_CHAR(p_CCID)
1130 ,p_level => C_LEVEL_PROCEDURE
1131 ,p_module =>l_log_module);
1132 trace
1133 (p_msg => 'p_TATB= ' ||p_TATB
1134 ,p_level => C_LEVEL_PROCEDURE
1135 ,p_module =>l_log_module);
1136 END IF;
1137
1138 IF ( FND_FLEX_KEYVAL.VALIDATE_CCID(
1139 APPL_SHORT_NAME => 'SQLGL'
1140 ,KEY_FLEX_CODE => 'GL#'
1141 ,STRUCTURE_NUMBER => prv_ChartOfAccountsID
1142 ,COMBINATION_ID => p_CCID
1143 ,DISPLAYABLE => g_disp_segments
1144 ,SECURITY =>'CHECK')) THEN
1145
1146 IF FND_FLEX_KEYVAL.IS_SECURED THEN
1147 l_secure_flag := 'Y';
1148 ELSE
1149 l_secure_flag := 'N';
1150 END IF;
1151
1152 IF p_TATB = 'TA' THEN
1153 g_current_segment_values := FND_FLEX_KEYVAL.CONCATENATED_VALUES;
1154 g_current_segments_desc := FND_FLEX_KEYVAL.CONCATENATED_DESCRIPTIONS;
1155 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1156 trace
1157 (p_msg =>'g_ta_segment_values '|| p_ccid || ' ' || g_current_segment_values || l_secure_flag
1158 ,p_level => C_LEVEL_STATEMENT
1159 ,p_module =>l_log_module);
1160 END IF;
1161 -- xla_util.debug('g_ta_segment_values '|| p_ccid || ' ' || g_current_segment_values || l_secure_flag);
1162 ELSE
1163 g_tb_current_segment_values := FND_FLEX_KEYVAL.CONCATENATED_VALUES;
1164 g_tb_current_segments_desc := FND_FLEX_KEYVAL.CONCATENATED_DESCRIPTIONS;
1165 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1166 trace
1167 (p_msg =>'g_segment_values '|| p_ccid || ' ' || g_tb_current_segment_values || l_secure_flag
1168 ,p_level => C_LEVEL_STATEMENT
1169 ,p_module =>l_log_module);
1170 END IF;
1171 -- xla_util.debug('g_segment_values '|| p_ccid || ' ' || g_tb_current_segment_values || l_secure_flag);
1172 END IF;
1173 END IF; -- Flex valid?
1174
1175 IF ( l_secure_flag = 'Y' ) THEN
1176 returnValue := TRUE;
1177 ELSE
1178 returnValue := FALSE;
1179 END IF;
1180
1181 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1182 trace
1183 (p_msg => 'END of function secure'
1184 ,p_level => C_LEVEL_PROCEDURE
1185 ,p_module =>l_log_module);
1186 trace
1187 (p_msg => 'return value is:'||l_secure_flag
1188 ,p_level => C_LEVEL_PROCEDURE
1189 ,p_module =>l_log_module);
1190 END IF;
1191
1192 RETURN returnValue;
1193
1194 END secure;
1195
1196
1197 PROCEDURE getBalances(balanceDr OUT NOCOPY NUMBER
1198 ,balanceCr OUT NOCOPY NUMBER
1199 ,codeCombinationsID IN NUMBER
1200 ,periodName IN VARCHAR2
1201 ,accountType IN VARCHAR2 )
1202 IS
1203
1204 -- Account Balance for accounting currency, period
1205 CURSOR BALANCES_C( p_CODE_COMBINATION_ID NUMBER
1206 ,p_PERIOD_NAME VARCHAR2 )
1207 IS
1208 SELECT ( nvl(BA.begin_balance_dr,0) -
1209 nvl(BA.begin_balance_cr,0) ) +
1210 ( nvl(BA.period_net_dr,0) -
1211 nvl(BA.period_net_cr,0) ) FUNCTIONAL_YEAR_TO_DATE
1212 FROM GL_BALANCES BA
1213 WHERE BA.CODE_COMBINATION_ID = p_CODE_COMBINATION_ID
1214 AND BA.ledger_id = prv_SetOfBooksID -- updated by weshen
1215 AND BA.PERIOD_NAME = p_PERIOD_NAME
1216 AND BA.ACTUAL_FLAG = 'A'
1217 AND BA.CURRENCY_CODE = g_AccountingCurrency;
1218
1219 l_balances_c BALANCES_C%ROWTYPE;
1220 l_log_module VARCHAR2(240);
1221
1222 BEGIN
1223 IF g_log_enabled THEN
1224 l_log_module := C_DEFAULT_MODULE||'.getBalances';
1225 END IF;
1226 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1227 trace
1228 (p_msg => 'BEGIN of procedure getBalances'
1229 ,p_level => C_LEVEL_PROCEDURE
1230 ,p_module =>l_log_module);
1231 trace
1232 (p_msg => 'codeCombinationsId= ' ||TO_CHAR(codeCombinationsID)
1233 ,p_level => C_LEVEL_PROCEDURE
1234 ,p_module =>l_log_module);
1235 trace
1236 (p_msg => 'period name = ' || periodName
1237 ,p_level => C_LEVEL_PROCEDURE
1238 ,p_module =>l_log_module);
1239 trace
1240 (p_msg => 'account type= ' ||accountType
1241 ,p_level => C_LEVEL_PROCEDURE
1242 ,p_module =>l_log_module);
1243 END IF;
1244
1245 OPEN BALANCES_C( codeCombinationsID
1246 ,periodName );
1247 FETCH BALANCES_C INTO l_balances_c;
1248 IF ( BALANCES_C%FOUND )
1249 THEN
1250
1251 IF l_balances_c.functional_year_to_date > 0 THEN
1252 balanceDr := l_balances_c.functional_year_to_date;
1253 ELSIF l_balances_c.functional_year_to_date < 0 THEN
1254 balanceCr := -1*l_balances_c.functional_year_to_date;
1255 ELSE
1256 IF accountType IN ('A','E') THEN
1257 balanceDr := 0 ;
1258 ELSE
1259 balanceCr := 0 ;
1260 END IF;
1261 END IF;
1262
1263 /*
1264 balanceDr := l_balances_c.functional_year_to_date;
1265 balanceCr := 0;
1266 ELSE
1267 balanceDr := 0;
1268 balanceCr := 0;
1269 */
1270
1271 END IF;
1272
1273 CLOSE BALANCES_C;
1274 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1275 trace
1276 (p_msg => 'END of Procedure getBalances'
1277 ,p_level => C_LEVEL_PROCEDURE
1278 ,p_module =>l_log_module);
1279 END IF;
1280
1281 END getBalances;
1282
1283 PROCEDURE ta_fetch_rows ( p_rows IN NUMBER DEFAULT 50
1284 ,p_TALineDataArray OUT NOCOPY T_TALineDataArray
1285 ,p_eof OUT NOCOPY BOOLEAN ) IS
1286 l_ccid NUMBER;
1287 l_account VARCHAR2(255);
1288 l_segment1_value VARCHAR2(255);
1289 l_segment2_value VARCHAR2(255);
1290 l_segment3_value VARCHAR2(255);
1291 l_segment4_value VARCHAR2(255);
1292 l_ae_line_ref VARCHAR2(1000);
1293 l_ae_line_ref_int VARCHAR2(1000);
1294 l_entered_currency VARCHAR2(15);
1295 l_entered_dr NUMBER;
1296 l_entered_cr NUMBER;
1297 l_acctd_dr NUMBER;
1298 l_acctd_cr NUMBER;
1299 l_report_dr NUMBER;
1300 l_report_cr NUMBER;
1301 l_balance_dr NUMBER; -- Applicable only to GL
1302 l_balance_cr NUMBER; -- Applicable only to GL
1303 l_period_name VARCHAR2(15); -- Applicable only to GL
1304 l_status VARCHAR2(1); -- Applicable only to GL
1305 l_account_type VARCHAR2(1);
1306
1307 l_rowCnt BINARY_INTEGER := 0;
1308 l_balance NUMBER := 0;
1309
1310 TA_ACCOUNT CONSTANT BINARY_INTEGER := 0;
1311 TA_ACCOUNT_DESC CONSTANT BINARY_INTEGER := 1;
1312 TA_ALL_CURR CONSTANT BINARY_INTEGER := 2;
1313 TA_BALANCE_BEFORE CONSTANT BINARY_INTEGER := 3;
1314 TA_CURRENCY_TOTAL_MC CONSTANT BINARY_INTEGER := 4;
1315 TA_ACCOUNT_TOTAL_SC CONSTANT BINARY_INTEGER := 5;
1316 TA_ACCOUNT_TOTAL_MC CONSTANT BINARY_INTEGER := 6;
1317 TA_ACTIVITY CONSTANT BINARY_INTEGER := 7;
1318 TA_BALANCE_AFTER CONSTANT BINARY_INTEGER := 8;
1319 TA_TOT_ACT_FOR_ALL_ACCOUNTS CONSTANT BINARY_INTEGER := 9;
1320 TA_CURR_HEADER_MC CONSTANT BINARY_INTEGER := 10;
1321 TA_CURR_HEADER_BLANK_MC CONSTANT BINARY_INTEGER := 11;
1322 TA_DUMMY CONSTANT BINARY_INTEGER := 100;
1323 TA_EOF CONSTANT BINARY_INTEGER := -1;
1324
1325 l_TALineDataArray T_TALineDataArray;
1326 l_log_module VARCHAR2(240);
1327
1328 BEGIN
1329 IF g_log_enabled THEN
1330 l_log_module := C_DEFAULT_MODULE||'.ta_fetch_rows';
1331 END IF;
1332 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1333 trace
1334 (p_msg => 'BEGIN of procedure ta_fetch_rows'
1335 ,p_level => C_LEVEL_PROCEDURE
1336 ,p_module =>l_log_module);
1337 END IF;
1338
1339
1340 --xla_msg('inside ta_fetch');
1341 p_eof := FALSE;
1342
1343 -- Fetch Data and populate tables
1344 -- Exit if rowCnt > rows_requested or End_of_fetch.
1345 LOOP
1346 EXIT WHEN l_rowCnt > p_rows;
1347 IF ( dbms_sql.fetch_rows(c_ta) > 0 ) THEN
1348
1349 dbms_sql.column_value( c_ta, 1, l_ccid );
1350 dbms_sql.column_value( c_ta, 2, l_segment1_value );
1351 dbms_sql.column_value( c_ta, 3, l_segment2_value );
1352 dbms_sql.column_value( c_ta, 4, l_segment3_value );
1353 dbms_sql.column_value( c_ta, 5, l_segment4_value );
1354 dbms_sql.column_value( c_ta, 6, l_ae_line_ref );
1355 dbms_sql.column_value( c_ta, 7, l_ae_line_ref_int );
1356 dbms_sql.column_value( c_ta, 8, l_entered_currency );
1357 dbms_sql.column_value( c_ta, 9, l_entered_dr );
1358 dbms_sql.column_value( c_ta, 10, l_entered_cr );
1359 dbms_sql.column_value( c_ta, 11, l_acctd_dr );
1360 dbms_sql.column_value( c_ta, 12, l_acctd_cr );
1361 dbms_sql.column_value( c_ta, 13, l_report_dr );
1362 dbms_sql.column_value( c_ta, 14, l_report_cr );
1363 dbms_sql.column_value( c_ta, 15, l_period_name );
1364 dbms_sql.column_value( c_ta, 16, l_status );
1365 dbms_sql.column_value( c_ta, 17, l_account_type );
1366
1367 l_account := l_segment1_value||l_segment2_value||l_segment3_value||l_segment4_value;
1368
1369 IF g_firstRow is null THEN
1370 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1371 trace
1372 (p_msg =>'first row'
1373 ,p_level => C_LEVEL_STATEMENT
1374 ,p_module =>l_log_module);
1375 END IF;
1376 --g_validateflex := TRUE;
1377 g_secure := secure(l_ccid,'TA');
1378
1379 -- Set firstRow to TRUE when hit the first valid account.
1380 IF (NOT g_secure) then
1381 g_current_account := l_account;
1382 g_currency_code := l_entered_currency;
1383 g_current_Ccid := l_Ccid;
1384 g_account_type := l_account_type;
1385 g_period_name := l_period_name;
1386 g_firstrow := TRUE;
1387 END IF;
1388 g_segment_values := g_current_segment_values;
1389 g_segments_desc := g_current_segments_desc;
1390 ELSE
1391 g_firstRow := FALSE;
1392 IF l_ccid = g_current_ccid THEN
1393 g_validateflex := FALSE;
1394 ELSE
1395 g_validateflex := TRUE;
1396 END IF;
1397 END IF;
1398
1399 IF (g_validateflex) and (NOT g_firstrow) THEN
1400 g_secure := secure(l_ccid,'TA');
1401 g_segment_values := g_current_segment_values;
1402 g_segments_desc := g_current_segments_desc;
1403 END IF;
1404
1405 IF (NOT g_secure ) THEN
1406 IF g_firstRow THEN
1407 -- Add a row for Account
1408 l_rowCnt := l_rowCnt +1;
1409 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT;
1410 l_TALineDataArray(l_rowCnt).Account := g_segment_values;
1411 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1412 trace
1413 (p_msg =>g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).Account
1414 ,p_level => C_LEVEL_STATEMENT
1415 ,p_module =>l_log_module);
1416 END IF;
1417 -- xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1418 -- l_TALineDataArray(l_rowCnt).Account );
1419
1420 -- Add a row to populate Account Description
1421 l_rowCnt := l_rowCnt +1;
1422 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT_DESC;
1423 l_TALineDataArray(l_rowCnt).AccountDesc := g_segments_desc;
1424 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1425 trace
1426 (p_msg =>g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||l_TALineDataArray(l_rowCnt).AccountDesc
1427 ,p_level => C_LEVEL_STATEMENT
1428 ,p_module =>l_log_module);
1429 END IF;
1430 -- xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1431 -- l_TALineDataArray(l_rowCnt).AccountDesc );
1432
1433 -- Add a row for currency header
1434 l_rowCnt := l_rowCnt +1;
1435 l_TALineDataArray(l_rowCnt).lineType := TA_ALL_CURR;
1436 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1437 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1438 trace
1439 (p_msg =>g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).enteredCurrency
1440 ,p_level => C_LEVEL_STATEMENT
1441 ,p_module =>l_log_module);
1442 END IF;
1443
1444 -- Get an opening balance.
1445 IF (prv_OrganizeBy = 'ACCOUNT') AND
1446 (prv_ApplicationId = 101) THEN
1447
1448 -- Calcualte opening balance
1449 getBalances( g_openingBalanceDr
1450 ,g_openingBalanceCr
1451 ,l_Ccid
1452 ,l_period_name
1453 ,l_account_type );
1454
1455 -- If the entry is posted then calculate opening balance.
1456 IF l_status = 'P' THEN
1457 l_balance := nvl(g_openingBalanceDr,0) - nvl(g_openingBalanceCr,0);
1458 getAccountBalance(l_balance
1459 ,prv_Trx_Hdr_Id
1460 ,g_openingBalanceDr
1461 ,g_openingBalanceCr
1462 ,l_Ccid
1463 ,l_account_type);
1464 END IF;
1465
1466 -- Insert a row for an opening balance. Balances are displayed
1467 -- for accounted amounts only.
1468
1469 l_rowCnt := l_rowCnt +1;
1470 l_TALineDataArray(l_rowCnt).lineType := TA_BALANCE_BEFORE;
1471 l_TALineDataArray(l_rowCnt).Ccid := l_ccid;
1472 l_TALineDataArray(l_rowCnt).Account := l_Account;
1473 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1474 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_openingBalanceDr;
1475 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_openingBalanceCr;
1476
1477
1478 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1479 trace
1480 (p_msg =>g_current_account
1481 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1482 || ' |'
1483 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1484 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1485 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1486 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1487 ,p_level => C_LEVEL_STATEMENT
1488 ,p_module =>l_log_module);
1489 END IF;
1490 /*
1491 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1492 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1493 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1494 */
1495 END IF;
1496 END IF;
1497
1498 IF (g_currency_code = l_entered_currency) AND
1499 (g_current_account = l_Account) THEN
1500 -- Populate PLSQL table.
1501 l_rowCnt := l_rowCnt +1;
1502 l_TALineDataArray(l_rowCnt).lineType := TA_ACTIVITY;
1503 l_TALineDataArray(l_rowCnt).Ccid := l_ccid;
1504 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1505 l_TALineDataArray(l_rowCnt).lineReference := l_ae_line_ref;
1506 l_TALineDataArray(l_rowCnt).enteredAmountDr := l_entered_dr;
1507 l_TALineDataArray(l_rowCnt).enteredAmountCr := l_entered_cr;
1508 l_TALineDataArray(l_rowCnt).accountedAmountDr := l_acctd_dr;
1509 l_TALineDataArray(l_rowCnt).accountedAmountCr := l_acctd_cr;
1510 l_TALineDataArray(l_rowCnt).reportedAmountDr := l_report_dr;
1511 l_TALineDataArray(l_rowCnt).reportedAmountCr := l_report_cr;
1512
1513 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1514 trace
1515 (p_msg =>g_current_account
1516 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1517 || ' |'
1518 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1519 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1520 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1521 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1522 ,p_level => C_LEVEL_STATEMENT
1523 ,p_module =>l_log_module);
1524 END IF;
1525 /*
1526 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1527 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1528 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1529 */
1530
1531 -- Currency Totals
1532 g_totalEnteredCurDr := getTotal(g_totalEnteredCurDr, l_entered_dr);
1533 g_totalEnteredCurCr := getTotal(g_totalEnteredCurCr, l_entered_cr);
1534 g_totalAccountedCurDr := getTotal(g_totalAccountedCurDr, l_acctd_dr);
1535 g_totalAccountedCurCr := getTotal(g_totalAccountedCurCr, l_acctd_cr);
1536 g_totalReportingCurDr := getTotal(g_totalReportingCurDr, l_report_dr);
1537 g_totalReportingCurCr := getTotal(g_totalReportingCurCr, l_report_cr);
1538
1539 -- Account Totals
1540 g_totalEntCcidDr := getTotal(g_totalEntCcidDr, l_entered_dr);
1541 g_totalEntCcidCr := getTotal(g_totalEntCcidCr, l_entered_cr);
1542 g_totalAcctCcidDr := getTotal(g_totalAcctCcidDr, l_acctd_dr);
1543 g_totalAcctCcidCr := getTotal(g_totalAcctCcidCr, l_acctd_cr);
1544 g_totalReportingCcidDr := getTotal(g_totalReportingCcidDr, l_report_dr);
1545 g_totalReportingCcidCr := getTotal(g_totalReportingCcidCr, l_report_cr);
1546 ELSE
1547
1548 -- If the currency is different
1549 IF g_currency_code <> l_entered_currency THEN
1550 IF g_current_account = l_Account THEN
1551 g_currency_cnt := g_currency_cnt + 1;
1552
1553 -- Update totals for an account
1554 g_totalAcctCcidDr := getTotal(g_totalAcctCcidDr, l_acctd_dr);
1555 g_totalAcctCcidCr := getTotal(g_totalAcctCcidCr, l_acctd_cr);
1556 g_totalReportingCcidDr := getTotal(g_totalReportingCcidDr, l_report_dr);
1557 g_totalReportingCcidCr := getTotal(g_totalReportingCcidCr, l_report_cr);
1558
1559 l_rowCnt := l_rowCnt + 1;
1560 l_TALineDataArray(l_rowCnt).lineType := TA_CURRENCY_TOTAL_MC;
1561 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1562 l_TALineDataArray(l_rowCnt).enteredAmountDr := g_totalEnteredCurDr;
1563 l_TALineDataArray(l_rowCnt).enteredAmountCr := g_totalEnteredCurCr;
1564 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalAccountedCurDr;
1565 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalAccountedCurCr;
1566 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalReportingCurDr;
1567 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalReportingCurCr;
1568
1569 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1570 trace
1571 (p_msg =>g_current_account
1572 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1573 || ' |'
1574 || l_TALineDataArray(l_rowCnt).enteredCurrency
1575 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1576 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1577 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1578 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1579 ,p_level => C_LEVEL_STATEMENT
1580 ,p_module =>l_log_module);
1581 END IF;
1582 /*
1583 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1584 l_TALineDataArray(l_rowCnt).enteredCurrency ||
1585 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1586 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1587 */
1588
1589 l_rowCnt := l_rowCnt + 1;
1590 l_TALineDataArray(l_rowCnt).lineType := TA_CURR_HEADER_BLANK_MC;
1591
1592 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1593 trace
1594 (p_msg => g_current_account
1595 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1596 || ' |'
1597 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1598 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1599 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1600 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1601 ,p_level => C_LEVEL_STATEMENT
1602 ,p_module =>l_log_module);
1603 END IF;
1604 /*
1605 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1606 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1607 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1608 */
1609
1610 l_rowCnt := l_rowCnt + 1;
1611 l_TALineDataArray(l_rowCnt).lineType := TA_CURR_HEADER_MC;
1612 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1613
1614 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1615 trace
1616 (p_msg => g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).enteredCurrency
1617 ,p_level => C_LEVEL_STATEMENT
1618 ,p_module =>l_log_module);
1619 END IF;
1620
1621 /*
1622 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1623 l_TALineDataArray(l_rowCnt).enteredCurrency );
1624 */
1625
1626 END IF;
1627 END IF; -- g_currency_code <> l_entered_currency
1628
1629 -- If the Account Changes
1630 IF g_current_account <> l_Account THEN
1631 -- Net Activity Entered Currency
1632 getNetBalance(g_totalEntCcidDr
1633 ,g_totalEntCcidCr
1634 ,g_account_type);
1635
1636 -- Net Activity Accounting Currency
1637 getNetBalance(g_totalAcctCcidDr
1638 ,g_totalAcctCcidCr
1639 ,g_account_type);
1640
1641 IF g_currency_cnt > 0 THEN -- Multi Currency
1642
1643 -- Populate currency totals
1644 l_rowCnt := l_rowCnt + 1;
1645 l_TALineDataArray(l_rowCnt).lineType := TA_CURRENCY_TOTAL_MC;
1646 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1647 l_TALineDataArray(l_rowCnt).enteredAmountDr := g_totalEnteredCurDr;
1648 l_TALineDataArray(l_rowCnt).enteredAmountCr := g_totalEnteredCurCr;
1649 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalAccountedCurDr;
1650 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalAccountedCurCr;
1651 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalReportingCurDr;
1652 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalReportingCurCr;
1653
1654 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1655 trace
1656 (p_msg => g_current_account
1657 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1658 || ' |'
1659 || l_TALineDataArray(l_rowCnt).enteredCurrency
1660 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1661 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1662 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1663 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1664 ,p_level => C_LEVEL_STATEMENT
1665 ,p_module =>l_log_module);
1666 END IF;
1667 /*
1668 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1669 l_TALineDataArray(l_rowCnt).enteredCurrency ||
1670 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1671 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1672 */
1673
1674 -- Populate Account Totals
1675
1676 l_rowCnt := l_rowCnt + 1;
1677 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT_TOTAL_MC;
1678 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1679 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalAcctCcidDr;
1680 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalAcctCcidCr;
1681 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalReportingCcidDr;
1682 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalReportingCcidCr;
1683
1684
1685 ELSE
1686 l_rowCnt := l_rowCnt + 1;
1687 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT_TOTAL_SC;
1688 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1689 l_TALineDataArray(l_rowCnt).enteredAmountDr := g_totalEntCcidDr;
1690 l_TALineDataArray(l_rowCnt).enteredAmountCr := g_totalEntCcidCr;
1691 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalAcctCcidDr;
1692 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalAcctCcidCr;
1693 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalReportingCcidDr;
1694 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalReportingCcidCr;
1695 END IF; -- g_currency_cnt > 0
1696
1697 -- Report Totals
1698 g_totalNetAccountedDr := g_totalNetAccountedDr + nvl(g_totalAcctCcidDr,0);
1699 g_totalNetAccountedCr := g_totalNetAccountedCr + nvl(g_totalAcctCcidCr,0);
1700 g_totalNetReportingDr := g_totalNetReportingDr + nvl(g_totalReportingCcidDr,0);
1701 g_totalNetReportingCr := g_totalNetReportingCr + nvl(g_totalReportingCcidCr,0);
1702
1703 -- Reset currency counter
1704 g_currency_cnt := 0;
1705
1706 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1707 trace
1708 (p_msg => g_current_account
1709 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1710 || ' |'
1711 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1712 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1713 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1714 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1715 ,p_level => C_LEVEL_STATEMENT
1716 ,p_module =>l_log_module);
1717 END IF;
1718 /*
1719 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1720 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1721 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);*/
1722
1723 -- Add closing balance row for an Account
1724 IF (prv_OrganizeBy = 'ACCOUNT') AND
1725 (prv_ApplicationID = 101) THEN
1726
1727 g_closingBalanceDr := nvl(g_openingBalanceDr,0) + nvl(g_totalAcctCcidDr,0);
1728 g_closingBalanceCr := nvl(g_openingBalanceCr,0) + nvl(g_totalAcctCcidCr,0);
1729
1730 getNetBalance(g_closingBalanceDr,
1731 g_closingBalanceCr,
1732 g_Account_Type);
1733
1734 l_rowCnt := l_rowCnt +1;
1735 l_TALineDataArray(l_rowCnt).lineType := TA_BALANCE_AFTER;
1736 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1737 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_closingBalanceDr;
1738 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_closingBalanceCr;
1739
1740 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1741 trace
1742 (p_msg => g_current_account
1743 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1744 || ' |'
1745 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1746 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1747 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1748 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1749 ,p_level => C_LEVEL_STATEMENT
1750 ,p_module =>l_log_module);
1751 END IF;
1752 /*
1753 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1754 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1755 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1756 */
1757 END IF;
1758
1759 -- Insert Dummy Line
1760 l_rowCnt := l_rowCnt +1;
1761 l_TALineDataArray(l_rowCnt).lineType := TA_DUMMY;
1762
1763 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1764 trace
1765 (p_msg => g_current_account
1766 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1767 || ' |'
1768 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1769 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1770 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1771 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1772 ,p_level => C_LEVEL_STATEMENT
1773 ,p_module =>l_log_module);
1774 END IF;
1775 /*
1776 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1777 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1778 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1779 */
1780
1781 -- Reset Account totals
1782 g_totalEntCcidDr := l_entered_dr;
1783 g_totalEntCcidCr := l_entered_cr;
1784 g_totalAcctCcidDr := l_acctd_dr;
1785 g_totalAcctCcidCr := l_acctd_cr;
1786 g_totalReportingCcidDr := l_report_dr;
1787 g_totalReportingCcidCr := l_report_cr;
1788
1789 -- Add a row for an Account
1790 l_rowCnt := l_rowCnt +1;
1791 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT;
1792 l_TALineDataArray(l_rowCnt).Account := g_segment_values;
1793
1794 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1795 trace
1796 (p_msg => g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).Account
1797 ,p_level => C_LEVEL_STATEMENT
1798 ,p_module =>l_log_module);
1799 END IF;
1800 /*
1801 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1802 l_TALineDataArray(l_rowCnt).Account );
1803 */
1804
1805 -- Add a row to populate Account Description
1806 l_rowCnt := l_rowCnt +1;
1807 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT_DESC;
1808 l_TALineDataArray(l_rowCnt).AccountDesc := g_segments_desc;
1809
1810 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1811 trace
1812 (p_msg => g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).AccountDesc
1813 ,p_level => C_LEVEL_STATEMENT
1814 ,p_module =>l_log_module);
1815 END IF;
1816 /*
1817 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1818 l_TALineDataArray(l_rowCnt).AccountDesc );
1819 */
1820 -- Add a row for currency header
1821 l_rowCnt := l_rowCnt +1;
1822 l_TALineDataArray(l_rowCnt).lineType := TA_ALL_CURR;
1823 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1824
1825 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1826 trace
1827 (p_msg => g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).enteredCurrency
1828 ,p_level => C_LEVEL_STATEMENT
1829 ,p_module =>l_log_module);
1830 END IF;
1831 /*
1832 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1833 l_TALineDataArray(l_rowCnt).enteredCurrency );
1834 */
1835
1836 -- Add a row for opening balance.
1837 IF (prv_OrganizeBy = 'ACCOUNT') AND
1838 (prv_ApplicationId = 101) THEN
1839 getBalances( g_openingBalanceDr
1840 ,g_openingBalanceCr
1841 ,l_Ccid
1842 ,l_period_name
1843 ,l_account_type);
1844
1845 -- If the entry is posted then adjust opening balance.
1846 IF l_status = 'P' THEN
1847 l_balance := nvl(g_openingBalanceDr,0) - nvl(g_openingBalanceCr,0);
1848 getAccountBalance(l_balance
1849 ,prv_Trx_Hdr_Id
1850 ,g_openingBalanceDr
1851 ,g_openingBalanceCr
1852 ,l_Ccid
1853 ,l_account_type);
1854 END IF;
1855
1856 -- Insert a row for an opening balance. Balances are displayed
1857 -- for accounted amounts only.
1858
1859 l_rowCnt := l_rowCnt +1;
1860 l_TALineDataArray(l_rowCnt).lineType := TA_BALANCE_BEFORE;
1861 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1862 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_openingBalanceDr;
1863 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_openingBalanceCr;
1864
1865 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1866 trace
1867 (p_msg => g_current_account
1868 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1869 || ' |'
1870 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1871 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1872 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1873 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1874 ,p_level => C_LEVEL_STATEMENT
1875 ,p_module =>l_log_module);
1876 END IF;
1877 /*
1878 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1879 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1880 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1881 */
1882 END IF;
1883 END IF; -- g_current_account <> l_Account
1884 l_rowCnt := l_rowCnt + 1;
1885 l_TALineDataArray(l_rowCnt).lineType := TA_ACTIVITY;
1886 l_TALineDataArray(l_rowCnt).enteredCurrency := l_entered_currency;
1887 l_TALineDataArray(l_rowCnt).lineReference := l_ae_line_ref;
1888 l_TALineDataArray(l_rowCnt).enteredAmountDr := l_entered_dr;
1889 l_TALineDataArray(l_rowCnt).enteredAmountCr := l_entered_cr;
1890 l_TALineDataArray(l_rowCnt).accountedAmountDr := l_acctd_dr;
1891 l_TALineDataArray(l_rowCnt).accountedAmountCr := l_acctd_cr;
1892 l_TALineDataArray(l_rowCnt).reportedAmountDr := l_report_dr;
1893 l_TALineDataArray(l_rowCnt).reportedAmountCr := l_report_cr;
1894
1895 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1896 trace
1897 (p_msg => g_current_account
1898 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1899 || ' |'
1900 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1901 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1902 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1903 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1904 ,p_level => C_LEVEL_STATEMENT
1905 ,p_module =>l_log_module);
1906 END IF;
1907 /*
1908 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1909 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1910 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1911 */
1912 END IF;
1913
1914 g_current_account := l_Account;
1915 g_current_Ccid := l_Ccid;
1916 g_currency_code := l_entered_currency;
1917
1918 -- Reset Currency totals
1919 g_totalEnteredCurDr := l_entered_dr;
1920 g_totalEnteredCurCr := l_entered_cr;
1921 g_totalAccountedCurDr := l_acctd_dr;
1922 g_totalAccountedCurCr := l_acctd_cr;
1923 g_totalReportingCurDr := l_report_dr;
1924 g_totalReportingCurCr := l_report_cr;
1925 /*
1926 -- Report Totals
1927 g_totalNetAccountedDr := g_totalNetAccountedDr + nvl(l_acctd_dr,0);
1928 g_totalNetAccountedCr := g_totalNetAccountedCr + nvl(l_acctd_cr,0);
1929 g_totalNetReportingDr := g_totalNetReportingDr + nvl(l_report_dr,0);
1930 g_totalNetReportingCr := g_totalNetReportingCr + nvl(l_report_cr,0);
1931 */
1932 END IF; -- Not secured
1933
1934 ELSE -- No more rows
1935 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1936 trace
1937 (p_msg => 'no more rows'
1938 ,p_level => C_LEVEL_STATEMENT
1939 ,p_module =>l_log_module);
1940 END IF;
1941 IF (dbms_sql.IS_OPEN(c_ta)) THEN
1942 dbms_sql.close_cursor(c_ta);
1943 END IF;
1944 p_eof := TRUE;
1945
1946 getNetBalance(g_totalEntCcidDr
1947 ,g_totalEntCcidCr
1948 ,g_account_type);
1949
1950 getNetBalance(g_totalAcctCcidDr
1951 ,g_totalAcctCcidCr
1952 ,g_account_type);
1953
1954 --IF (l_RowCnt > 0 ) THEN
1955
1956 -- Multi Currency
1957 IF g_currency_cnt > 0 THEN
1958
1959 -- Populate Account Totals
1960 l_rowCnt := l_rowCnt + 1;
1961 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT_TOTAL_MC;
1962 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1963 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalAcctCcidDr;
1964 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalAcctCcidCr;
1965 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalReportingCcidDr;
1966 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalReportingCcidCr;
1967
1968 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1969 trace
1970 (p_msg => g_current_account
1971 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
1972 || ' |'
1973 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
1974 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
1975 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
1976 || l_TALineDataArray(l_rowCnt).accountedAmountCr
1977 ,p_level => C_LEVEL_STATEMENT
1978 ,p_module =>l_log_module);
1979 END IF;
1980 /*
1981 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
1982 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
1983 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
1984 */
1985 ELSE
1986 l_rowCnt := l_rowCnt + 1;
1987 l_TALineDataArray(l_rowCnt).lineType := TA_ACCOUNT_TOTAL_SC;
1988 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
1989 l_TALineDataArray(l_rowCnt).enteredAmountDr := g_totalEntCcidDr;
1990 l_TALineDataArray(l_rowCnt).enteredAmountCr := g_totalEntCcidCr;
1991 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalAcctCcidDr;
1992 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalAcctCcidCr;
1993 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalReportingCcidDr;
1994 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalReportingCcidCr;
1995
1996 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1997 trace
1998 (p_msg => g_current_account
1999 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
2000 || ' |'
2001 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
2002 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
2003 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
2004 || l_TALineDataArray(l_rowCnt).accountedAmountCr
2005 ,p_level => C_LEVEL_STATEMENT
2006 ,p_module =>l_log_module);
2007 END IF;
2008 /*
2009 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
2010 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2011 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
2012 */
2013 END IF;
2014
2015 -- Report Totals
2016 g_totalNetAccountedDr := g_totalNetAccountedDr + nvl(g_totalAcctCcidDr,0);
2017 g_totalNetAccountedCr := g_totalNetAccountedCr + nvl(g_totalAcctCcidCr,0);
2018 g_totalNetReportingDr := g_totalNetReportingDr + nvl(g_totalReportingCcidDr,0);
2019 g_totalNetReportingCr := g_totalNetReportingCr + nvl(g_totalReportingCcidCr,0);
2020
2021 -- Add closing balance row for an account.
2022 IF (prv_OrganizeBy = 'ACCOUNT') AND
2023 (prv_ApplicationID = 101) THEN
2024
2025 g_closingBalanceDr := Nvl(g_openingbalancedr,0) + Nvl(g_totalAcctCcidDr,0);
2026 g_closingBalanceCr := Nvl(g_openingBalanceCr,0) + Nvl(g_totalAcctCcidCr,0);
2027
2028 getNetBalance(g_closingBalanceDr,
2029 g_closingBalanceCr,
2030 g_Account_Type);
2031
2032 l_rowCnt := l_rowCnt +1;
2033 l_TALineDataArray(l_rowCnt).lineType := TA_BALANCE_AFTER;
2034 l_TALineDataArray(l_rowCnt).enteredCurrency := g_currency_code;
2035 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_closingBalanceDr;
2036 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_closingBalanceCr;
2037 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2038 trace
2039 (p_msg => g_current_account
2040 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
2041 || ' |'
2042 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
2043 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
2044 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
2045 || l_TALineDataArray(l_rowCnt).accountedAmountCr
2046 ,p_level => C_LEVEL_STATEMENT
2047 ,p_module =>l_log_module);
2048 END IF;
2049 /*
2050 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
2051 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2052 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
2053 */
2054 END IF;
2055
2056 -- Populate Report Totals
2057 l_rowCnt := l_rowCnt + 1;
2058 l_TALineDataArray(l_rowCnt).lineType := TA_DUMMY;
2059
2060 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2061 trace
2062 (p_msg => g_current_account
2063 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
2064 || ' |'
2065 || l_TALineDataArray(l_rowCnt).enteredAmountDr || '|'
2066 || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
2067 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
2068 || l_TALineDataArray(l_rowCnt).accountedAmountCr
2069 ,p_level => C_LEVEL_STATEMENT
2070 ,p_module =>l_log_module);
2071 END IF;
2072 /*
2073 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
2074 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2075 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
2076 */
2077
2078 getNetBalance(g_closingBalanceDr,
2079 g_closingBalanceCr,
2080 g_Account_Type);
2081
2082 l_rowCnt := l_rowCnt + 1;
2083 l_TALineDataArray(l_rowCnt).lineType := TA_TOT_ACT_FOR_ALL_ACCOUNTS;
2084 l_TALineDataArray(l_rowCnt).accountedAmountDr := g_totalNetAccountedDr;
2085 l_TALineDataArray(l_rowCnt).accountedAmountCr := g_totalNetAccountedCr;
2086 l_TALineDataArray(l_rowCnt).reportedAmountDr := g_totalNetReportingDr;
2087 l_TALineDataArray(l_rowCnt).reportedAmountCr := g_totalNetReportingCr;
2088
2089 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2090 trace
2091 (p_msg => g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr
2092 ,p_level => C_LEVEL_STATEMENT
2093 ,p_module =>l_log_module);
2094 END IF;
2095 /*
2096 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
2097 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
2098 */
2099
2100 -- Populate End Of File row.
2101 l_rowCnt := l_rowCnt +1;
2102 l_TALineDataArray(l_rowCnt).lineType := TA_EOF;
2103
2104 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2105 trace
2106 (p_msg => g_current_account
2107 || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta')
2108 || ' |' || l_TALineDataArray(l_rowCnt).enteredAmountDr
2109 || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|'
2110 || l_TALineDataArray(l_rowCnt).accountedAmountDr || '|'
2111 || l_TALineDataArray(l_rowCnt).accountedAmountCr
2112 ,p_level => C_LEVEL_STATEMENT
2113 ,p_module =>l_log_module);
2114 END IF;
2115 /*
2116 xla_util.debug(g_current_account || getlinetype(l_TALineDataArray(l_rowCnt).LineType,'ta') || ' |' ||
2117 l_TALineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TALineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2118 l_TALineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TALineDataArray(l_rowCnt).accountedAmountCr);
2119 */
2120 --END IF; -- Rowcnt > 0
2121 EXIT;
2122 END IF;
2123 END LOOP; -- fetch_rows
2124
2125 p_TALinedataarray := l_talinedataarray;
2126
2127 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2128 trace
2129 (p_msg => 'END of Procedure ta_fetch_rows'
2130 ,p_level => C_LEVEL_PROCEDURE
2131 ,p_module =>l_log_module);
2132 END IF;
2133
2134
2135 END ta_fetch_rows;
2136
2137 PROCEDURE tb_fetch_rows ( p_rows IN NUMBER DEFAULT 50
2138 ,p_TbLineDataArray OUT NOCOPY t_TBLineDataArray
2139 ,p_eof OUT NOCOPY BOOLEAN
2140 ) IS
2141 l_ccid NUMBER;
2142 l_account VARCHAR2(255);
2143 l_segment1_value VARCHAR2(255);
2144 l_segment2_value VARCHAR2(255);
2145 l_segment3_value VARCHAR2(255);
2146 l_segment4_value VARCHAR2(255);
2147 l_ae_line_ref VARCHAR2(1000);
2148 l_ae_line_ref_int VARCHAR2(1000);
2149 l_entered_currency VARCHAR2(15);
2150 l_entered_dr NUMBER;
2151 l_entered_cr NUMBER;
2152 l_acctd_dr NUMBER;
2153 l_acctd_cr NUMBER;
2154 l_report_dr NUMBER;
2155 l_report_cr NUMBER;
2156 l_period_name VARCHAR2(15); -- Applicable only to GL
2157 l_status VARCHAR2(1); -- Applicable only to GL
2158 l_account_type VARCHAR2(1);
2159
2160
2161 l_rowCnt NUMBER := 0;
2162 l_balance NUMBER := 0;
2163
2164 TB_LINE CONSTANT BINARY_INTEGER := 0;
2165 TB_LINE_MC CONSTANT BINARY_INTEGER := 1;
2166 TB_TOTAL_ACTIVITY_DRCR CONSTANT BINARY_INTEGER := 2;
2167 TB_TOTAL_ACT_DR CONSTANT BINARY_INTEGER := 3;
2168 TB_TOTAL_ACT_CR CONSTANT BINARY_INTEGER := 4;
2169 TB_LINE_TOTAL_MC CONSTANT BINARY_INTEGER := 5;
2170 TB_DUMMY CONSTANT BINARY_INTEGER := 100;
2171 TB_EOF CONSTANT BINARY_INTEGER := -1;
2172
2173 l_TBLineDataArray T_TBLineDataArray;
2174 l_temp NUMBER;
2175 l_log_module VARCHAR2(240);
2176
2177 BEGIN
2178 IF g_log_enabled THEN
2179 l_log_module := C_DEFAULT_MODULE||'.tb_fetch_rows';
2180 END IF;
2181 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2182 trace
2183 (p_msg => 'BEGIN of procedure tb_fetch_rows'
2184 ,p_level => C_LEVEL_PROCEDURE
2185 ,p_module =>l_log_module);
2186 END IF;
2187
2188 p_eof := FALSE;
2189
2190 -- Fetch Data and populate tables.
2191 -- Exit if rowCnt > rows_requested or End_of_fetch.
2192 LOOP
2193 EXIT WHEN l_rowCnt > p_rows;
2194 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2195 trace
2196 (p_msg => 'aaaa: one run of LOOP'
2197 ,p_level => C_LEVEL_STATEMENT
2198 ,p_module =>l_log_module);
2199 trace
2200 (p_msg => 'l_rowCnt is:'||to_char(l_rowCnt)
2201 ,p_level => C_LEVEL_STATEMENT
2202 ,p_module =>l_log_module);
2203 l_temp:=1;
2204 LOOP
2205 EXIT WHEN l_temp>l_rowCnt;
2206 trace
2207 (p_msg => 'row No is:'||to_char(l_temp)
2208 ,p_level => C_LEVEL_STATEMENT
2209 ,p_module =>l_log_module);
2210 trace
2211 (p_msg => 'lineType:'||getlinetype(l_TBLineDataArray(l_temp).LineType, 'tb')
2212 ,p_level => C_LEVEL_STATEMENT
2213 ,p_module =>l_log_module);
2214 trace
2215 (p_msg => 'enteredCurrency:'||l_TBLineDataArray(l_temp).enteredCurrency
2216 ,p_level => C_LEVEL_STATEMENT
2217 ,p_module =>l_log_module);
2218 trace
2219 (p_msg => 'account:'||l_TBLineDataArray(l_temp).account||l_TBLineDataArray(l_temp).accountdesc
2220 ,p_level => C_LEVEL_STATEMENT
2221 ,p_module =>l_log_module);
2222 trace
2223 (p_msg => 'accountedamountDr:'||l_TBLineDataArray(l_temp).accountedAmountDr
2224 ,p_level => C_LEVEL_STATEMENT
2225 ,p_module =>l_log_module);
2226 trace
2227 (p_msg => 'accountedamountCr:'||l_TBLineDataArray(l_temp).accountedAmountCr
2228 ,p_level => C_LEVEL_STATEMENT
2229 ,p_module =>l_log_module);
2230 trace
2231 (p_msg => 'accountedamountNet:'||l_TBLineDataArray(l_temp).accountedAmountNet
2232 ,p_level => C_LEVEL_STATEMENT
2233 ,p_module =>l_log_module);
2234 l_temp:=l_temp+1;
2235 end loop;
2236 END IF;
2237 IF ( dbms_sql.fetch_rows(c_tb) > 0 ) THEN
2238 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2239 trace
2240 (p_msg => 'dbms_sql.fetch_rows(c_tb) > 0'
2241 ,p_level => C_LEVEL_STATEMENT
2242 ,p_module =>l_log_module);
2243 END IF;
2244
2245 dbms_sql.column_value( c_tb, 1, l_ccid );
2246 dbms_sql.column_value( c_tb, 2, l_segment1_value );
2247 dbms_sql.column_value( c_tb, 3, l_segment2_value );
2248 dbms_sql.column_value( c_tb, 4, l_segment3_value );
2249 dbms_sql.column_value( c_tb, 5, l_segment4_value );
2250 dbms_sql.column_value( c_tb, 6, l_ae_line_ref );
2251 dbms_sql.column_value( c_tb, 7, l_ae_line_ref_int );
2252 dbms_sql.column_value( c_tb, 8, l_entered_currency );
2253 dbms_sql.column_value( c_tb, 9, l_entered_dr );
2254 dbms_sql.column_value( c_tb, 10, l_entered_cr );
2255 dbms_sql.column_value( c_tb, 11, l_acctd_dr );
2256 dbms_sql.column_value( c_tb, 12, l_acctd_cr );
2257 dbms_sql.column_value( c_tb, 13, l_report_dr );
2258 dbms_sql.column_value( c_tb, 14, l_report_cr );
2259 dbms_sql.column_value( c_tb, 15, l_period_name );
2260 dbms_sql.column_value( c_tb, 16, l_status );
2261 dbms_sql.column_value( c_tb, 17, l_account_type );
2262
2263 l_account := l_segment1_value||l_segment2_value||l_segment3_value||l_segment4_value;
2264
2265 IF g_tb_firstRow is null THEN
2266 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2267 trace
2268 (p_msg => 'first row'
2269 ,p_level => C_LEVEL_STATEMENT
2270 ,p_module =>l_log_module);
2271 END IF;
2272 --g_tb_firstrow := TRUE;
2273 g_tb_validateflex := TRUE;
2274 g_tb_secure := secure(l_ccid,'TB');
2275
2276 IF (NOT g_tb_secure) THEN
2277 g_tb_current_account := l_account;
2278 g_tb_currency_code := l_entered_currency;
2279 g_tb_current_Ccid := l_Ccid;
2280 g_tb_account_type := l_account_type;
2281 g_tb_period_name := l_period_name;
2282 g_tb_firstRow := TRUE;
2283 g_tb_segment_values := g_tb_current_segment_values;
2284 g_tb_segments_desc := g_tb_current_segments_desc;
2285 END IF;
2286 ELSE
2287 g_tb_firstRow := FALSE;
2288 IF l_ccid = g_tb_current_ccid THEN
2289 g_tb_validateflex := FALSE;
2290 ELSE
2291 g_tb_validateflex := TRUE;
2292 END IF;
2293 END IF;
2294
2295 IF (g_tb_validateflex) and (NOT g_tb_firstrow) THEN
2296 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2297 trace
2298 (p_msg =>'calling secure ' || l_ccid
2299 ,p_level => C_LEVEL_STATEMENT
2300 ,p_module =>l_log_module);
2301 END IF;
2302
2303 g_tb_secure := secure(l_ccid,'TB');
2304 END IF;
2305
2306 IF (NOT g_tb_secure ) THEN
2307
2308 IF (g_tb_currency_code = l_entered_currency) AND
2309 (g_tb_current_account = l_Account) THEN
2310
2311 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2312 trace
2313 (p_msg => ' (g_tb_currency_code = l_entered_currency) AND (g_tb_current_account = l_Account) THEN'
2314 ,p_level => C_LEVEL_STATEMENT
2315 ,p_module =>l_log_module);
2316 END IF;
2317 -- Update Totals
2318 g_tb_totalEnteredCurDr := getTotal(g_tb_totalEnteredCurDr ,l_entered_dr);
2319 g_tb_totalEnteredCurCr := getTotal(g_tb_totalEnteredCurCr ,l_entered_cr);
2320 g_tb_totalAccountedCurDr := getTotal(g_tb_totalAccountedCurDr ,l_acctd_dr);
2321 g_tb_totalAccountedCurCr := getTotal(g_tb_totalAccountedCurCr ,l_acctd_cr);
2322 g_tb_totalReportingCurDr := getTotal(g_tb_totalReportingCurDr ,l_report_dr);
2323 g_tb_totalReportingCurCr := getTotal(g_tb_totalReportingCurCr ,l_report_cr);
2324
2325 g_tb_totalEntCcidDr := getTotal(g_tb_totalEntCcidDr ,l_entered_dr);
2326 g_tb_totalEntCcidCr := getTotal(g_tb_totalEntCcidCr ,l_entered_cr);
2327 g_tb_totalAcctCcidDr := getTotal(g_tb_totalAcctCcidDr ,l_acctd_dr);
2328 g_tb_totalAcctCcidCr := getTotal(g_tb_totalAcctCcidCr ,l_acctd_cr);
2329 g_tb_totalReportingCcidDr := getTotal(g_tb_totalReportingCcidDr ,l_report_dr);
2330 g_tb_totalReportingCcidCr := getTotal(g_tb_totalReportingCcidCr ,l_report_cr);
2331
2332 ELSE
2333 IF g_tb_currency_code <> l_entered_currency THEN
2334 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2335 trace
2336 (p_msg => 'g_tb_currency_code <> l_entered_currency'
2337 ,p_level => C_LEVEL_STATEMENT
2338 ,p_module =>l_log_module);
2339 END IF;
2340 IF g_tb_current_account = l_Account THEN
2341 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2342 trace
2343 (p_msg => ' g_tb_current_account = l_Account'
2344 ,p_level => C_LEVEL_STATEMENT
2345 ,p_module =>l_log_module);
2346 END IF;
2347
2348 -- Update account totals
2349 g_tb_totalAcctCcidDr := getTotal(g_tb_totalAcctCcidDr ,l_acctd_dr);
2350 g_tb_totalAcctCcidCr := getTotal(g_tb_totalAcctCcidCr ,l_acctd_cr);
2351 g_tb_totalReportingCcidDr := getTotal(g_tb_totalReportingCcidDr ,l_report_dr);
2352 g_tb_totalReportingCcidCr := getTotal(g_tb_totalReportingCcidCr ,l_report_cr);
2353
2354 g_tb_currency_cnt := g_tb_currency_cnt + 1;
2355
2356 IF g_tb_currency_cnt = 1 THEN
2357 -- print multi currency totals
2358 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2359 trace
2360 (p_msg => ' g_tb_currency_cnt = 1'
2361 ,p_level => C_LEVEL_STATEMENT
2362 ,p_module =>l_log_module);
2363 END IF;
2364
2365 l_rowCnt := l_rowCnt + 1;
2366 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE_MC;
2367 l_TBLineDataArray(l_rowCnt).Ccid := g_tb_current_ccid;
2368 l_TBLineDataArray(l_rowCnt).Account := g_tb_segment_values;
2369 l_TBLineDataArray(l_rowCnt).AccountDesc := g_tb_segments_desc;
2370 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2371 l_TBLineDataArray(l_rowCnt).enteredAmountDr := g_tb_totalEnteredCurDr;
2372 l_TBLineDataArray(l_rowCnt).enteredAmountCr := g_tb_totalEnteredCurCr;
2373 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAccountedCurDr;
2374 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAccountedCurCr;
2375 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCurDr;
2376 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCurCr;
2377 l_TBLineDataArray(l_rowCnt).enteredAmountNet := nvl(g_tb_totalEnteredCurDr,0) - nvl(g_tb_totalEnteredCurCr,0);
2378 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAccountedCurDr,0) - nvl(g_tb_totalAccountedCurCr,0);
2379 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCurDr,0) - nvl(g_tb_totalReportingCurCr,0);
2380 ELSE
2381 --ELSIF g_tb_currency_cnt > 1 THEN
2382 -- Do not send account info if it's the second currency total line
2383 -- for same account
2384
2385 l_rowCnt := l_rowCnt + 1;
2386 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE_MC;
2387 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2388 l_TBLineDataArray(l_rowCnt).enteredAmountDr := g_tb_totalEnteredCurDr;
2389 l_TBLineDataArray(l_rowCnt).enteredAmountCr := g_tb_totalEnteredCurCr;
2390 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAccountedCurDr;
2391 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAccountedCurCr;
2392 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCurDr;
2393 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCurCr;
2394 l_TBLineDataArray(l_rowCnt).enteredAmountNet := nvl(g_tb_totalEnteredCurDr,0) - nvl(g_tb_totalEnteredCurCr,0);
2395 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAccountedCurDr,0) - nvl(g_tb_totalAccountedCurCr,0);
2396 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCurDr,0) - nvl(g_tb_totalReportingCurCr,0);
2397 END IF;
2398
2399 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2400 trace
2401 (p_msg =>'aa:'||g_tb_current_account
2402 || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb')
2403 || ' |'
2404 || l_TBLineDataArray(l_rowCnt).enteredCurrency
2405 || l_TBLineDataArray(l_rowcnt).account
2406 || l_TBLineDataArray(l_rowcnt).accountdesc
2407 || l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|'
2408 || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|'
2409 || l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|'
2410 || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|'
2411 || l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'
2412 || l_TBLineDataArray(l_rowCnt).accountedAmountNet
2413 ,p_level => C_LEVEL_STATEMENT
2414 ,p_module =>l_log_module);
2415 END IF;
2416 /*
2417 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2418 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2419 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2420 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2421 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2422 l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'|| l_TBLineDataArray(l_rowCnt).accountedAmountNet);
2423 */
2424 END IF;
2425 END IF;
2426
2427 IF g_tb_current_account <> l_Account THEN
2428 -- Get an opening balance.
2429 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2430 trace
2431 (p_msg => 'ee:g_tb_current_account <> l_Account'
2432 ,p_level => C_LEVEL_STATEMENT
2433 ,p_module =>l_log_module);
2434 END IF;
2435 IF (prv_OrganizeBy = 'ACCOUNT') AND
2436 (prv_ApplicationId = 101) THEN
2437 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2438 trace
2439 (p_msg => 'ef:prv_OrganizeBy =ACCOUNT and prv_ApplicationId = 101'
2440 ,p_level => C_LEVEL_STATEMENT
2441 ,p_module =>l_log_module);
2442 END IF;
2443 getBalances( g_tb_openingBalanceDr
2444 ,g_tb_openingBalanceCr
2445 ,g_tb_current_ccid
2446 ,g_tb_period_name
2447 ,g_tb_account_type );
2448
2449 -- If the entry is posted then calculate opening balance.
2450 IF l_status = 'P' THEN
2451 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2452 trace
2453 (p_msg => 'eg:l_status is P'
2454 ,p_level => C_LEVEL_STATEMENT
2455 ,p_module =>l_log_module);
2456 END IF;
2457 l_balance := nvl(g_tb_openingBalanceDr,0) - nvl(g_tb_openingBalanceCr,0);
2458 getAccountBalance( l_balance
2459 ,prv_Trx_Hdr_Id
2460 ,g_tb_openingBalanceDr
2461 ,g_tb_openingBalanceCr
2462 ,g_tb_current_Ccid
2463 ,g_tb_account_type);
2464 END IF;
2465
2466 -- Calculate closing Balance
2467 g_tb_closingBalanceDr := nvl(g_tb_openingBalanceDr,0) + nvl(g_tb_totalAcctCcidDr,0);
2468 g_tb_closingBalanceCr := nvl(g_tb_openingBalanceCr,0) + nvl(g_tb_totalAcctCcidCr,0);
2469
2470 getNetBalance(g_tb_closingBalanceDr,
2471 g_tb_closingBalanceCr,
2472 g_tb_account_type );
2473 END IF;
2474
2475 IF g_tb_currency_cnt > 0 THEN
2476
2477 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2478 trace
2479 (p_msg => 'eh:g_tb_currency_cnt > 0'
2480 ,p_level => C_LEVEL_STATEMENT
2481 ,p_module =>l_log_module);
2482 END IF;
2483 l_rowCnt := l_rowCnt + 1;
2484 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE_MC;
2485 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2486 l_TBLineDataArray(l_rowCnt).enteredAmountDr := g_tb_totalEnteredCurDr;
2487 l_TBLineDataArray(l_rowCnt).enteredAmountCr := g_tb_totalEnteredCurCr;
2488 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAccountedCurDr;
2489 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAccountedCurCr;
2490 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCurDr;
2491 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCurCr;
2492 l_TBLineDataArray(l_rowCnt).enteredAmountNet := nvl(g_tb_totalEnteredCurDr,0) - nvl(g_tb_totalEnteredCurCr,0);
2493 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAccountedCurDr,0) - nvl(g_tb_totalAccountedCurCr,0);
2494 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCurDr,0) - nvl(g_tb_totalReportingCurCr,0);
2495
2496 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2497 trace
2498 (p_msg =>'ab:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2499 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2500 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2501 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2502 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2503 l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'|| l_TBLineDataArray(l_rowCnt).accountedAmountNet
2504 ,p_level => C_LEVEL_STATEMENT
2505 ,p_module =>l_log_module);
2506 END IF;
2507 /*
2508 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2509 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2510 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2511 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2512 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2513 l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'|| l_TBLineDataArray(l_rowCnt).accountedAmountNet);
2514 */
2515 l_rowCnt := l_rowCnt + 1;
2516 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE_TOTAL_MC;
2517 l_TBLineDataArray(l_rowCnt).Ccid := g_tb_current_ccid;
2518 l_TBLineDataArray(l_rowCnt).Account := g_tb_segment_values;
2519 l_TBLineDataArray(l_rowCnt).AccountDesc := g_tb_segments_desc;
2520 l_TBLineDataArray(l_rowCnt).balancebeforeDr := g_tb_openingBalanceDr;
2521 l_TBLineDataArray(l_rowCnt).balancebeforeCr := g_tb_openingBalanceCr;
2522 l_TBLineDataArray(l_rowCnt).balancebeforeNet := nvl(g_tb_openingBalanceDr,0) - nvl(g_tb_openingBalanceCr,0) ;
2523 l_TBLineDataArray(l_rowCnt).balanceAfterDr := g_tb_closingBalanceDr;
2524 l_TBLineDataArray(l_rowCnt).balanceAfterCr := g_tb_closingBalanceCr;
2525 l_TBLineDataArray(l_rowCnt).balanceAfterNet := nvl(g_tb_closingBalanceDr,0) - nvl(g_tb_closingBalanceCr,0);
2526 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2527 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAcctCcidDr;
2528 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAcctCcidCr;
2529 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCcidDr;
2530 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCcidCr;
2531 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAcctCcidDr,0) - nvl(g_tb_totalAcctCcidCr,0);
2532 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCcidDr,0) - nvl(g_tb_totalReportingCcidCr,0);
2533
2534 getNetBalance(g_tb_totalAcctCcidDr,
2535 g_tb_totalAcctCcidCr,
2536 g_tb_account_type);
2537
2538 getNetBalance(g_tb_totalReportingCcidDr,
2539 g_tb_totalReportingCcidCr,
2540 g_tb_account_type);
2541
2542 -- Report Totals
2543 g_tb_totalNetAccountedDr := g_tb_totalNetAccountedDr + nvl(g_tb_totalAcctCcidDr,0);
2544 g_tb_totalNetAccountedCr := g_tb_totalNetAccountedCr + nvl(g_tb_totalAcctCcidCr,0);
2545 g_tb_totalNetReportingDr := g_tb_totalNetReportingDr + nvl(g_tb_totalReportingCcidDr,0);
2546 g_tb_totalNetReportingCr := g_tb_totalNetReportingCr + nvl(g_tb_totalReportingCcidCr,0);
2547 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2548 trace
2549 (p_msg => 'dd:totalnetaccounteddr'||g_tb_totalNetAccountedDr|| '*g_tb_totalNetAccountedCr:'||g_tb_totalNetAccountedCr
2550 ,p_level => C_LEVEL_STATEMENT
2551 ,p_module =>l_log_module);
2552 END IF;
2553
2554 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2555 trace
2556 (p_msg => 'ac:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2557 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2558 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc || '|' ||
2559 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' ||
2560 l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountNet
2561 ,p_level => C_LEVEL_STATEMENT);
2562 END IF;
2563 /*
2564 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2565 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2566 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc || '|' ||
2567 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' ||
2568 l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountNet);
2569 */
2570 ELSE
2571 l_rowCnt := l_rowCnt + 1;
2572 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE;
2573 l_TBLineDataArray(l_rowCnt).Ccid := g_tb_current_ccid;
2574 l_TBLineDataArray(l_rowCnt).Account := g_tb_segment_values;
2575 l_TBLineDataArray(l_rowCnt).AccountDesc := g_tb_segments_desc;
2576 l_TBLineDataArray(l_rowCnt).balancebeforeDr := g_tb_openingBalanceDr;
2577 l_TBLineDataArray(l_rowCnt).balancebeforeCr := g_tb_openingBalanceCr;
2578 l_TBLineDataArray(l_rowCnt).balancebeforeNet := nvl(g_tb_openingBalanceDr,0) - nvl(g_tb_openingBalanceCr,0) ;
2579 l_TBLineDataArray(l_rowCnt).balanceAfterDr := g_tb_closingBalanceDr;
2580 l_TBLineDataArray(l_rowCnt).balanceAfterCr := g_tb_closingBalanceCr;
2581 l_TBLineDataArray(l_rowCnt).balanceAfterNet := nvl(g_tb_closingBalanceDr,0) - nvl(g_tb_closingBalanceCr,0);
2582 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2583 l_TBLineDataArray(l_rowCnt).enteredAmountDr := g_tb_totalEntCcidDr;
2584 l_TBLineDataArray(l_rowCnt).enteredAmountCr := g_tb_totalEntCcidCr;
2585 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAcctCcidDr;
2586 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAcctCcidCr;
2587 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCcidDr;
2588 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCcidCr;
2589 l_TBLineDataArray(l_rowCnt).enteredAmountNet := nvl(g_tb_totalEntCcidDr,0) - nvl(g_tb_totalEntCcidCr,0);
2590 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAcctCcidDr,0) - nvl(g_tb_totalAcctCcidCr,0);
2591 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCcidDr,0) - nvl(g_tb_totalReportingCcidCr,0);
2592 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2593 trace
2594 (p_msg => 'ad:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2595 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2596 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2597 l_TBLineDataArray(l_rowCnt).balancebeforeDr|| l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' ||
2598 l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' ||
2599 l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'||
2600 l_TBLineDataArray(l_rowCnt).accountedAmountNet
2601 ,p_level => C_LEVEL_STATEMENT
2602 ,p_module =>l_log_module);
2603 END IF;
2604 /*
2605 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2606 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2607 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2608 l_TBLineDataArray(l_rowCnt).balancebeforeDr|| l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' ||
2609 l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' ||
2610 l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'||
2611 l_TBLineDataArray(l_rowCnt).accountedAmountNet);
2612 */
2613
2614 getNetBalance(g_tb_totalAcctCcidDr,
2615 g_tb_totalAcctCcidCr,
2616 g_tb_account_type);
2617
2618 getNetBalance(g_tb_totalReportingCcidDr,
2619 g_tb_totalReportingCcidCr,
2620 g_tb_account_type);
2621
2622 -- Report Totals
2623 g_tb_totalNetAccountedDr := g_tb_totalNetAccountedDr + nvl(g_tb_totalAcctCcidDr,0);
2624 g_tb_totalNetAccountedCr := g_tb_totalNetAccountedCr + nvl(g_tb_totalAcctCcidCr,0);
2625 g_tb_totalNetReportingDr := g_tb_totalNetReportingDr + nvl(g_tb_totalReportingCcidDr,0);
2626 g_tb_totalNetReportingCr := g_tb_totalNetReportingCr + nvl(g_tb_totalReportingCcidCr,0);
2627 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2628 trace
2629 (p_msg => 'dd:totalnetaccounteddr'||g_tb_totalNetAccountedDr|| '*g_tb_totalNetAccountedCr:'||g_tb_totalNetAccountedCr
2630 ,p_level => C_LEVEL_STATEMENT
2631 ,p_module =>l_log_module);
2632 END IF;
2633
2634 END IF;
2635
2636 g_tb_segment_values := g_tb_current_segment_values;
2637 g_tb_segments_desc := g_tb_current_segments_desc;
2638
2639
2640 g_tb_totalEntCcidDr := l_entered_dr;
2641 g_tb_totalEntCcidCr := l_entered_cr;
2642 g_tb_totalAcctCcidDr := l_acctd_dr;
2643 g_tb_totalAcctCcidCr := l_acctd_cr;
2644 g_tb_totalReportingCcidDr := l_report_dr;
2645 g_tb_totalReportingCcidCr := l_report_cr;
2646
2647 g_tb_currency_code := l_entered_currency;
2648 g_tb_currency_cnt := 0;
2649
2650 END IF;
2651 END IF;
2652
2653 g_tb_current_ccid := l_ccid;
2654 g_tb_current_account := l_account;
2655 g_tb_currency_code := l_entered_currency;
2656 g_tb_period_name := l_period_name;
2657 g_tb_account_type := l_account_type;
2658
2659 -- Currency totals
2660 g_tb_totalEnteredCurDr := l_entered_dr;
2661 g_tb_totalEnteredCurCr := l_entered_cr;
2662 g_tb_totalAccountedCurDr := l_acctd_dr;
2663 g_tb_totalAccountedCurCr := l_acctd_cr;
2664 g_tb_totalReportingCurDr := l_report_dr;
2665 g_tb_totalReportingCurCr := l_report_cr;
2666 /*
2667 -- Report Totals
2668 g_tb_totalNetAccountedDr := g_tb_totalNetAccountedDr + nvl(l_acctd_dr,0);
2669 g_tb_totalNetAccountedCr := g_tb_totalNetAccountedCr + nvl(l_acctd_cr,0);
2670 g_tb_totalNetReportingDr := g_tb_totalNetReportingDr + nvl(l_report_dr,0);
2671 g_tb_totalNetReportingCr := g_tb_totalNetReportingCr + nvl(l_report_cr,0);
2672 */
2673 END IF; -- not secured
2674
2675 ELSE -- No more rows
2676 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2677 trace
2678 (p_msg => 'no more rows'
2679 ,p_level => C_LEVEL_STATEMENT
2680 ,p_module =>l_log_module);
2681 END IF;
2682
2683 IF dbms_sql.IS_OPEN(c_tb) THEN
2684 dbms_sql.close_cursor(c_tb);
2685 END IF;
2686 p_eof := TRUE;
2687
2688 --IF (NOT g_tb_secure) THEN
2689 -- Print summary line
2690
2691 IF (prv_OrganizeBy = 'ACCOUNT') AND
2692 (prv_ApplicationId = 101) THEN
2693 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2694 trace
2695 (p_msg => 'da:'
2696 ,p_level => C_LEVEL_STATEMENT
2697 ,p_module =>l_log_module);
2698 END IF;
2699 getBalances( g_tb_openingBalanceDr
2700 ,g_tb_openingBalanceCr
2701 ,g_tb_current_ccid
2702 ,g_tb_period_name
2703 ,g_tb_account_type );
2704
2705 -- If the entry is posted then calculate opening balance.
2706 IF l_status = 'P' THEN
2707 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2708 trace
2709 (p_msg => 'db:'
2710 ,p_level => C_LEVEL_STATEMENT
2711 ,p_module =>l_log_module);
2712 END IF;
2713 l_balance := nvl(g_tb_openingBalanceDr,0) - nvl(g_tb_openingBalanceCr,0);
2714 getAccountBalance(l_balance
2715 ,prv_Trx_Hdr_Id
2716 ,g_tb_openingBalanceDr
2717 ,g_tb_openingBalanceCr
2718 ,g_tb_current_Ccid
2719 ,g_tb_account_type);
2720 END IF;
2721
2722 -- Calculate closing Balance
2723 g_tb_closingBalanceDr := nvl(g_tb_openingBalanceDr,0) + nvl(g_tb_totalAcctCcidDr,0);
2724 g_tb_closingBalanceCr := nvl(g_tb_openingBalanceCr,0) + nvl(g_tb_totalAcctCcidCr,0);
2725
2726 getNetBalance(g_tb_closingBalanceDr,
2727 g_tb_closingBalanceCr,
2728 g_tb_account_type );
2729 END IF;
2730
2731 IF g_tb_currency_cnt > 0 THEN
2732 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2733 trace
2734 (p_msg => 'dc:'
2735 ,p_level => C_LEVEL_STATEMENT
2736 ,p_module =>l_log_module);
2737 END IF;
2738 l_rowCnt := l_rowCnt + 1;
2739 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE_MC;
2740 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2741 l_TBLineDataArray(l_rowCnt).enteredAmountDr := g_tb_totalEnteredCurDr;
2742 l_TBLineDataArray(l_rowCnt).enteredAmountCr := g_tb_totalEnteredCurCr;
2743 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAccountedCurDr;
2744 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAccountedCurCr;
2745 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCurDr;
2746 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCurCr;
2747 l_TBLineDataArray(l_rowCnt).enteredAmountNet := nvl(g_tb_totalEnteredCurDr,0) - nvl(g_tb_totalEnteredCurCr,0);
2748 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAccountedCurDr,0) - nvl(g_tb_totalAccountedCurCr,0);
2749 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCurDr,0) - nvl(g_tb_totalReportingCurCr,0);
2750
2751 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2752 trace
2753 (p_msg => 'ae:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2754 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2755 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2756 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2757 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2758 l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'|| l_TBLineDataArray(l_rowCnt).accountedAmountNet
2759 ,p_level => C_LEVEL_STATEMENT
2760 ,p_module =>l_log_module);
2761 END IF;
2762 /*
2763 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2764 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2765 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2766 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2767 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2768 l_TBLineDataArray(l_rowCnt).enteredAmountNet || '|'|| l_TBLineDataArray(l_rowCnt).accountedAmountNet);
2769 */
2770
2771
2772 l_rowCnt := l_rowCnt + 1;
2773 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE_TOTAL_MC;
2774 l_TBLineDataArray(l_rowCnt).Account := g_tb_segment_values;
2775 l_TBLineDataArray(l_rowCnt).AccountDesc := g_tb_segments_desc;
2776 l_TBLineDataArray(l_rowCnt).balancebeforeDr := g_tb_openingBalanceDr;
2777 l_TBLineDataArray(l_rowCnt).balancebeforeCr := g_tb_openingBalanceCr;
2778 l_TBLineDataArray(l_rowCnt).balancebeforeNet := Nvl(g_tb_openingBalanceDr,0) - Nvl(g_tb_openingBalanceCr,0) ;
2779 l_TBLineDataArray(l_rowCnt).balanceAfterDr := g_tb_closingBalanceDr;
2780 l_TBLineDataArray(l_rowCnt).balanceAfterCr := g_tb_closingBalanceCr;
2781 l_TBLineDataArray(l_rowCnt).balanceAfterNet := Nvl(g_tb_closingBalanceDr,0) - Nvl(g_tb_closingBalanceCr,0);
2782 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2783 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAcctCcidDr;
2784 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAcctCcidCr;
2785 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCcidDr;
2786 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCcidCr;
2787 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAcctCcidDr,0) - nvl(g_tb_totalAcctCcidCr,0);
2788 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCcidDr,0) - nvl(g_tb_totalReportingCcidCr,0);
2789 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2790 trace
2791 (p_msg => 'af:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2792 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2793 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc || '|' ||
2794 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2795 l_TBLineDataArray(l_rowCnt).accountedAmountNet
2796 ,p_level => C_LEVEL_STATEMENT
2797 ,p_module =>l_log_module);
2798 END IF;
2799 /*
2800 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2801 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2802 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc || '|' ||
2803 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr || '|' ||
2804 l_TBLineDataArray(l_rowCnt).accountedAmountNet);
2805 */
2806 getNetBalance(g_tb_totalAcctCcidDr,
2807 g_tb_totalAcctCcidCr,
2808 g_tb_account_type);
2809
2810 getNetBalance(g_tb_totalReportingCcidDr,
2811 g_tb_totalReportingCcidCr,
2812 g_tb_account_type);
2813
2814 -- Report Totals
2815 g_tb_totalNetAccountedDr := g_tb_totalNetAccountedDr + nvl(g_tb_totalAcctCcidDr,0);
2816 g_tb_totalNetAccountedCr := g_tb_totalNetAccountedCr + nvl(g_tb_totalAcctCcidCr,0);
2817 g_tb_totalNetReportingDr := g_tb_totalNetReportingDr + nvl(g_tb_totalReportingCcidDr,0);
2818 g_tb_totalNetReportingCr := g_tb_totalNetReportingCr + nvl(g_tb_totalReportingCcidCr,0);
2819 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2820 trace
2821 (p_msg => 'dd:totalnetaccounteddr'||g_tb_totalNetAccountedDr|| '*g_tb_totalNetAccountedCr:'||g_tb_totalNetAccountedCr
2822 ,p_level => C_LEVEL_STATEMENT
2823 ,p_module =>l_log_module);
2824 END IF;
2825
2826 ELSE
2827 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2828 trace
2829 (p_msg => 'dd:'
2830 ,p_level => C_LEVEL_STATEMENT
2831 ,p_module =>l_log_module);
2832 END IF;
2833 l_rowCnt := l_rowCnt + 1;
2834 l_TBLineDataArray(l_rowCnt).lineType := TB_LINE;
2835 l_TBLineDataArray(l_rowCnt).Account := g_tb_segment_values;
2836 l_TBLineDataArray(l_rowCnt).AccountDesc := g_tb_segments_Desc;
2837 l_TBLineDataArray(l_rowCnt).balancebeforeDr := g_tb_openingBalanceDr;
2838 l_TBLineDataArray(l_rowCnt).balancebeforeCr := g_tb_openingBalanceCr;
2839 l_TBLineDataArray(l_rowCnt).balancebeforeNet := nvl(g_tb_openingBalanceDr,0) - nvl(g_tb_openingBalanceCr,0) ;
2840 l_TBLineDataArray(l_rowCnt).balanceAfterDr := g_tb_closingBalanceDr;
2841 l_TBLineDataArray(l_rowCnt).balanceAfterCr := g_tb_closingBalanceCr;
2842 l_TBLineDataArray(l_rowCnt).balanceAfterNet := nvl(g_tb_closingBalanceDr,0) - nvl(g_tb_closingBalanceCr,0);
2843 l_TBLineDataArray(l_rowCnt).enteredCurrency := g_tb_currency_code;
2844 l_TBLineDataArray(l_rowCnt).enteredAmountDr := g_tb_totalEntCcidDr;
2845 l_TBLineDataArray(l_rowCnt).enteredAmountCr := g_tb_totalEntCcidCr;
2846 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalAcctCcidDr;
2847 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalAcctCcidCr;
2848 l_TBLineDataArray(l_rowCnt).reportedAmountDr := g_tb_totalReportingCcidDr;
2849 l_TBLineDataArray(l_rowCnt).reportedAmountCr := g_tb_totalReportingCcidCr;
2850 l_TBLineDataArray(l_rowCnt).enteredAmountNet := nvl(g_tb_totalEntCcidDr,0) - nvl(g_tb_totalEntCcidCr,0);
2851 l_TBLineDataArray(l_rowCnt).accountedAmountNet := nvl(g_tb_totalAcctCcidDr,0) - nvl(g_tb_totalAcctCcidCr,0);
2852 l_TBLineDataArray(l_rowCnt).reportingAmountNet := nvl(g_tb_totalReportingCcidDr,0) - nvl(g_tb_totalReportingCcidCr,0);
2853
2854 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2855 trace
2856 (p_msg => 'ag:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2857 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2858 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2859 l_TBLineDataArray(l_rowCnt).balancebeforeDr || l_TBLineDataArray(l_rowCnt).balancebeforeCr ||
2860 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2861 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr
2862 ,p_level => C_LEVEL_STATEMENT
2863 ,p_module =>l_log_module);
2864 END IF;
2865 /*
2866 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2867 l_TBLineDataArray(l_rowCnt).enteredCurrency ||
2868 l_TBLineDataArray(l_rowcnt).account || l_TBLineDataArray(l_rowcnt).accountdesc ||
2869 l_TBLineDataArray(l_rowCnt).balancebeforeDr || l_TBLineDataArray(l_rowCnt).balancebeforeCr ||
2870 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2871 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr);
2872 */
2873 getNetBalance(g_tb_totalAcctCcidDr,
2874 g_tb_totalAcctCcidCr,
2875 g_tb_account_type);
2876
2877 getNetBalance(g_tb_totalReportingCcidDr,
2878 g_tb_totalReportingCcidCr,
2879 g_tb_account_type);
2880
2881 -- Report Totals
2882 g_tb_totalNetAccountedDr := g_tb_totalNetAccountedDr + nvl(g_tb_totalAcctCcidDr,0);
2883 g_tb_totalNetAccountedCr := g_tb_totalNetAccountedCr + nvl(g_tb_totalAcctCcidCr,0);
2884 g_tb_totalNetReportingDr := g_tb_totalNetReportingDr + nvl(g_tb_totalReportingCcidDr,0);
2885 g_tb_totalNetReportingCr := g_tb_totalNetReportingCr + nvl(g_tb_totalReportingCcidCr,0);
2886 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2887 trace
2888 (p_msg => 'dd:totalnetaccounteddr'||g_tb_totalNetAccountedDr|| '*g_tb_totalNetAccountedCr:'||g_tb_totalNetAccountedCr
2889 ,p_level => C_LEVEL_STATEMENT
2890 ,p_module =>l_log_module);
2891 END IF;
2892 END IF;
2893
2894
2895 -- Populate Report Totals
2896 l_rowCnt := l_rowCnt + 1;
2897 l_TBLineDataArray(l_rowCnt).lineType := TB_DUMMY;
2898
2899 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2900 trace
2901 (p_msg => 'ah:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2902 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2903 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr
2904 ,p_level => C_LEVEL_STATEMENT
2905 ,p_module =>l_log_module);
2906 END IF;
2907 /*
2908 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2909 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2910 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr);
2911 */
2912 l_rowCnt := l_rowCnt + 1;
2913 l_TBLineDataArray(l_rowCnt).lineType := TB_TOTAL_ACTIVITY_DRCR;
2914 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalNetAccountedDr;
2915 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalNetAccountedCr;
2916
2917 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2918 trace
2919 (p_msg => 'ai:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2920 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2921 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr
2922 ,p_level => C_LEVEL_STATEMENT
2923 ,p_module =>l_log_module);
2924 END IF;
2925 /*
2926 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2927 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2928 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr);
2929 */
2930
2931 l_rowCnt := l_rowCnt + 1;
2932 l_TBLineDataArray(l_rowCnt).lineType := TB_TOTAL_ACT_DR;
2933 l_TBLineDataArray(l_rowCnt).accountedAmountDr := g_tb_totalNetAccountedDr;
2934
2935 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2936 trace
2937 (p_msg => 'aj:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2938 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2939 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr
2940 ,p_level => C_LEVEL_STATEMENT
2941 ,p_module =>l_log_module);
2942 END IF;
2943
2944 /*
2945 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2946 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2947 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr);
2948 */
2949 l_rowCnt := l_rowCnt + 1;
2950 l_TBLineDataArray(l_rowCnt).lineType := TB_TOTAL_ACT_CR;
2951 l_TBLineDataArray(l_rowCnt).accountedAmountCr := g_tb_totalNetAccountedCr;
2952
2953 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2954 trace
2955 (p_msg => 'ak:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2956 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2957 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr
2958 ,p_level => C_LEVEL_STATEMENT
2959 ,p_module =>l_log_module);
2960 END IF;
2961
2962 /*
2963 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2964 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2965 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr);
2966 */
2967 -- Populate End Of File row.
2968 l_rowCnt := l_rowCnt +1;
2969 l_TBLineDataArray(l_rowCnt).lineType := TB_EOF;
2970
2971 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2972 trace
2973 (p_msg => 'al:'||g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2974 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2975 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr
2976 ,p_level => C_LEVEL_STATEMENT
2977 ,p_module =>l_log_module);
2978 END IF;
2979 /*
2980 xla_util.debug(g_tb_current_account || getlinetype(l_TBLineDataArray(l_rowCnt).LineType,'tb') || ' |' ||
2981 l_TBLineDataArray(l_rowCnt).enteredAmountDr || '|' || l_TBLineDataArray(l_rowCnt).enteredAmountCr || '|' ||
2982 l_TBLineDataArray(l_rowCnt).accountedAmountDr || '|' || l_TBLineDataArray(l_rowCnt).accountedAmountCr);
2983 */
2984 --END IF;
2985 EXIT;
2986 END IF;
2987 END LOOP; -- fetch_rows
2988
2989 -- Assign values to an out NOCOPY parameter
2990 p_TBLineDataArray := l_TBLineDataArray;
2991
2992 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2993 trace
2994 (p_msg => 'END of Procedure tb_fetch_rows'
2995 ,p_level => C_LEVEL_PROCEDURE
2996 ,p_module =>l_log_module);
2997 l_temp:=1;
2998 LOOP
2999 EXIT WHEN l_temp>l_rowCnt;
3000 trace
3001 (p_msg => 'row No is:'||to_char(l_temp)
3002 ,p_level => C_LEVEL_STATEMENT
3003 ,p_module =>l_log_module);
3004 trace
3005 (p_msg => 'lineType:'||getlinetype(l_TBLineDataArray(l_temp).LineType, 'tb')
3006 ,p_level => C_LEVEL_STATEMENT
3007 ,p_module =>l_log_module);
3008 trace
3009 (p_msg => 'enteredCurrency:'||l_TBLineDataArray(l_temp).enteredCurrency
3010 ,p_level => C_LEVEL_STATEMENT
3011 ,p_module =>l_log_module);
3012 trace
3013 (p_msg => 'account:'||l_TBLineDataArray(l_temp).account||l_TBLineDataArray(l_temp).accountdesc
3014 ,p_level => C_LEVEL_STATEMENT
3015 ,p_module =>l_log_module);
3016 trace
3017 (p_msg => 'accountedamountDr:'||l_TBLineDataArray(l_temp).accountedAmountDr
3018 ,p_level => C_LEVEL_STATEMENT
3019 ,p_module =>l_log_module);
3020 trace
3021 (p_msg => 'accountedamountCr:'||l_TBLineDataArray(l_temp).accountedAmountCr
3022 ,p_level => C_LEVEL_STATEMENT
3023 ,p_module =>l_log_module);
3024 trace
3025 (p_msg => 'accountedamountNet:'||l_TBLineDataArray(l_temp).accountedAmountNet
3026 ,p_level => C_LEVEL_STATEMENT
3027 ,p_module =>l_log_module);
3028 l_temp:=l_temp+1;
3029 end loop;
3030 END IF;
3031
3032 END tb_fetch_rows;
3033 /*
3034 Calculate Account Balance
3035 */
3036 PROCEDURE getAccountBalance( p_amount IN NUMBER
3037 ,p_trx_hdr_id IN NUMBER
3038 ,p_amount_dr OUT NOCOPY NUMBER
3039 ,p_amount_cr OUT NOCOPY NUMBER
3040 ,p_Ccid IN NUMBER
3041 ,p_account_type IN VARCHAR2
3042 )IS
3043 l_amount Number := 0;
3044 l_log_module VARCHAR2(240);
3045 BEGIN
3046
3047 IF g_log_enabled THEN
3048 l_log_module := C_DEFAULT_MODULE||'.getAccountBalance';
3049 END IF;
3050 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3051 trace
3052 (p_msg => 'BEGIN of procedure getAccountBalance'
3053 ,p_level => C_LEVEL_PROCEDURE
3054 ,p_module =>l_log_module);
3055 trace
3056 (p_msg => 'p_amount is:'||to_char(p_amount)
3057 ,p_level => C_LEVEL_PROCEDURE
3058 ,p_module =>l_log_module);
3059 trace
3060 (p_msg => 'p_trx_hdr_id is:'||to_char(p_trx_hdr_id)
3061 ,p_level => C_LEVEL_PROCEDURE
3062 ,p_module =>l_log_module);
3063 trace
3064 (p_msg => 'p_ccid is:'||to_char(p_Ccid)
3065 ,p_level => C_LEVEL_PROCEDURE
3066 ,p_module =>l_log_module);
3067 trace
3068 (p_msg => 'p_account type is:'||p_account_type
3069 ,p_level => C_LEVEL_PROCEDURE
3070 ,p_module =>l_log_module);
3071 END IF;
3072
3073 --xla_util.debug('p_trx_hdr_id ' || p_trx_hdr_id);
3074 --xla_util.debug('p_Ccid ' || p_Ccid);
3075
3076 SELECT SUM(Nvl(accounted_Dr,0))- SUM(Nvl(Accounted_Cr,0))
3077 INTO l_amount
3078 FROM gl_je_lines
3079 WHERE je_header_id = p_trx_hdr_id
3080 AND code_combination_id = p_Ccid;
3081
3082 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3083 trace
3084 (p_msg => 'l_amount ' || l_amount
3085 ,p_level => C_LEVEL_STATEMENT
3086 ,p_module =>l_log_module);
3087 END IF;
3088
3089 l_amount := nvl(p_amount,0) - l_amount;
3090
3091 IF l_amount > 0 THEN
3092 p_amount_dr := l_amount;
3093 ELSIF l_amount < 0 THEN
3094 p_amount_cr := -1*l_amount;
3095 ELSE
3096 IF p_account_type in ('A','E') THEN
3097 p_amount_dr := 0;
3098 ELSE
3099 -- Is it a bug? both are p_amount_dr--wei
3100 p_amount_dr := 0;
3101 END IF;
3102 END IF;
3103 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3104 trace
3105 (p_msg => 'END of Procedure getNetBalance'
3106 ,p_level => C_LEVEL_PROCEDURE
3107 ,p_module =>l_log_module);
3108 trace
3109 (p_msg => 'out var:p_amount_dr:'||to_char(p_amount_dr)
3110 ,p_level => C_LEVEL_PROCEDURE
3111 ,p_module =>l_log_module);
3112 trace
3113 (p_msg => 'out var:p_amount_cr:'||to_char(p_amount_cr)
3114 ,p_level => C_LEVEL_PROCEDURE
3115 ,p_module =>l_log_module);
3116 END IF;
3117
3118
3119 EXCEPTION
3120 WHEN OTHERS THEN
3121 p_amount_Dr := 0;
3122 p_amount_Cr := 0;
3123
3124 END getAccountBalance;
3125
3126 PROCEDURE getNetBalance( p_AccountedDr IN OUT NOCOPY NUMBER
3127 ,p_AccountedCr IN OUT NOCOPY NUMBER
3128 ,p_AccountType IN VARCHAR2) IS
3129 l_balance NUMBER;
3130 l_amount NUMBER;
3131 l_amount_dr NUMBER;
3132 l_amount_cr NUMBER;
3133 l_log_module VARCHAR2(240);
3134 BEGIN
3135 IF g_log_enabled THEN
3136 l_log_module := C_DEFAULT_MODULE||'.getNetBalance';
3137 END IF;
3138 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3139 trace
3140 (p_msg => 'BEGIN of procedure getNetBalance'
3141 ,p_level => C_LEVEL_PROCEDURE
3142 ,p_module =>l_log_module);
3143 trace
3144 (p_msg => 'p_accounteddr is:'||to_char(p_AccountedDr)
3145 ,p_level => C_LEVEL_PROCEDURE
3146 ,p_module =>l_log_module);
3147 trace
3148 (p_msg => 'p_accountedcr is:'||to_char(p_AccountedCr)
3149 ,p_level => C_LEVEL_PROCEDURE
3150 ,p_module =>l_log_module);
3151 trace
3152 (p_msg => 'p_account type is:'|| p_AccountType
3153 ,p_level => C_LEVEL_PROCEDURE
3154 ,p_module =>l_log_module);
3155 END IF;
3156
3157 /*
3158 xla_util.debug('p_AccountedDr = ' || p_AccountedDr);
3159 xla_util.debug('p_AccountedCr = ' || p_AccountedCr);
3160 */
3161
3162 l_amount_dr := nvl(p_accountedDr,0);
3163 l_amount_cr := nvl(p_accountedCr,0);
3164
3165 p_accountedDr := nvl(p_accountedDr,0);
3166 p_accountedCr := nvl(p_accountedCr,0);
3167
3168 IF p_accountedDr < 0 Then
3169 p_accountedDr := -1*p_accountedDr;
3170 END IF;
3171
3172 IF P_accountedCr < 0 Then
3173 p_accountedCr := -1*p_accountedCr;
3174 END IF;
3175
3176
3177 IF (p_AccountedDr > p_accountedCr) THEN
3178 p_AccountedDr := l_amount_dr - l_amount_cr;
3179 p_accountedCr := NULL;
3180 ELSIF (p_AccountedCr > p_accountedDr) THEN
3181 p_AccountedCr := l_amount_cr - l_amount_dr;
3182 p_accountedDr := NULL;
3183 ELSE
3184 IF p_accountType IN ('A','E') THEN
3185 p_AccountedDr := 0 ;
3186 p_AccountedCr := NULL ;
3187 ELSE
3188 p_AccountedDr := NULL ;
3189 p_AccountedCr := 0 ;
3190 END IF;
3191 END IF;
3192 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3193 trace
3194 (p_msg => 'END of Procedure getNetBalance'
3195 ,p_level => C_LEVEL_PROCEDURE
3196 ,p_module =>l_log_module);
3197 trace
3198 (p_msg => 'out var:p_AccountedDr:'||to_char(p_AccountedDr)
3199 ,p_level => C_LEVEL_PROCEDURE
3200 ,p_module =>l_log_module);
3201 trace
3202 (p_msg => 'out var:p_AccountedCr:'||to_char(p_AccountedCr)
3203 ,p_level => C_LEVEL_PROCEDURE
3204 ,p_module =>l_log_module);
3205 END IF;
3206
3207 END getNetBalance;
3208
3209 /* Close TA cursor */
3210 PROCEDURE ta_close IS
3211 l_log_module VARCHAR2(240);
3212 BEGIN
3213 IF g_log_enabled THEN
3214 l_log_module := C_DEFAULT_MODULE||'.ta_close';
3215 END IF;
3216 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3217 trace
3218 (p_msg => 'BEGIN of procedure ta_close'
3219 ,p_level => C_LEVEL_PROCEDURE
3220 ,p_module =>l_log_module);
3221 END IF;
3222
3223 IF dbms_sql.IS_OPEN(c_ta) THEN
3224 dbms_sql.close_cursor(c_ta);
3225 END IF;
3226 END ta_close;
3227
3228 /* Close TB cursor */
3229 PROCEDURE tb_close IS
3230 l_log_module VARCHAR2(240);
3231 BEGIN
3232 IF g_log_enabled THEN
3233 l_log_module := C_DEFAULT_MODULE||'.tb_close';
3234 END IF;
3235 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3236 trace
3237 (p_msg => 'BEGIN of procedure tb_close'
3238 ,p_level => C_LEVEL_PROCEDURE
3239 ,p_module =>l_log_module);
3240 END IF;
3241
3242 IF dbms_sql.IS_OPEN(c_tb) THEN
3243 dbms_sql.close_cursor(c_tb);
3244 END IF;
3245 END tb_close;
3246
3247 /* Returns the total */
3248 FUNCTION getTotal( p_total_amount IN NUMBER
3249 ,p_current_amount IN NUMBER
3250 ) RETURN NUMBER IS
3251 l_total_amount Number;
3252 l_log_module VARCHAR2(240);
3253 BEGIN
3254 IF g_log_enabled THEN
3255 l_log_module := C_DEFAULT_MODULE||'.getTotal';
3256 END IF;
3257 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3258 trace
3259 (p_msg => 'BEGIN of function getTotal'
3260 ,p_level => C_LEVEL_PROCEDURE
3261 ,p_module =>l_log_module);
3262 trace
3263 (p_msg => 'p_total_amount is:'||to_char(p_total_amount)
3264 ,p_level => C_LEVEL_PROCEDURE
3265 ,p_module =>l_log_module);
3266 trace
3267 (p_msg => 'p_current_amount is:'||to_char(p_current_amount)
3268 ,p_level => C_LEVEL_PROCEDURE
3269 ,p_module =>l_log_module);
3270 END IF;
3271
3272 IF p_current_amount IS NOT NULL THEN
3273 l_total_amount := Nvl(p_total_amount,0) + p_current_amount;
3274 ELSE
3275 l_total_amount := p_total_amount;
3276 END IF;
3277 RETURN l_total_amount;
3278 END getTotal;
3279
3280 -- Used for debugging purpose only.
3281 FUNCTION getLineType ( p_TypeId binary_integer,
3282 p_tatb varchar2 default 'ta' ) RETURN VARCHAR2 IS
3283 l_log_module VARCHAR2(240);
3284 BEGIN
3285 IF g_log_enabled THEN
3286 l_log_module := C_DEFAULT_MODULE||'.getLineType';
3287 END IF;
3288 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3289 trace
3290 (p_msg => 'BEGIN of function getLineType'
3291 ,p_level => C_LEVEL_PROCEDURE
3292 ,p_module =>l_log_module);
3293 trace
3294 (p_msg => 'p_typeid is:'||to_char(p_TypeId)
3295 ,p_level => C_LEVEL_PROCEDURE
3296 ,p_module =>l_log_module);
3297 trace
3298 (p_msg => 'p_tatb is:'||p_tatb
3299 ,p_level => C_LEVEL_PROCEDURE
3300 ,p_module =>l_log_module);
3301 END IF;
3302
3303 IF p_tatb = 'ta' THEN
3304 IF p_typeid = 0 THEN
3305 RETURN('TA_ACCOUNT');
3306 ELSIF p_typeid = 1 THEN
3307 RETURN ('TA_ACCOUNT_DESC');
3308 ELSIF p_typeid = 2 THEN
3309 RETURN ('TA_ALL_CURR');
3310 ELSIF p_typeid = 3 THEN
3311 RETURN ('TA_BALANCE_BEFORE');
3312 ELSIF p_typeid = 4 THEN
3313 RETURN('TA_CURRENCY_TOTAL_MC');
3314 ELSIF p_typeid = 5 THEN
3315 RETURN('TA_ACCOUNT_TOTAL_SC');
3316 ELSIF p_typeid = 6 THEN
3317 RETURN('TA_ACCOUNT_TOTAL_MC');
3318 ELSIF p_typeid = 7 THEN
3319 RETURN('TA_ACTIVITY');
3320 ELSIF p_typeid = 8 THEN
3321 RETURN('TA_BALANCE_AFTER');
3322 ELSIF p_typeid = 9 THEN
3323 RETURN('TA_TOT_ACT_FOR_ALL_ACCOUNTS');
3324 ELSIF p_typeid = 10 THEN
3325 RETURN('TA_CURR_HEADER_MC');
3326 ELSIF p_typeid = 11 THEN
3327 RETURN('TA_CURR_HEADER_BLANK_MC');
3328 ELSIF p_typeid = -1 THEN
3329 RETURN('TA_EOF');
3330 ELSIF p_typeid = 100 THEN
3331 RETURN('TA_DUMMY');
3332 ELSE
3333 RETURN('Invalid linetype = '|| p_typeid);
3334 END IF;
3335 ELSE
3336 IF p_typeid = 0 THEN
3337 RETURN('TB_LINE');
3338 ELSIF p_typeid = 1 THEN
3339 RETURN('TB_LINE_MC');
3340 ELSIF p_typeid = 2 THEN
3341 RETURN('TB_TOTAL_ACTIVITY_DRCR');
3342 ELSIF p_typeid = 3 THEN
3343 RETURN('TB_TOTAL_ACT_DR');
3344 ELSIF p_typeid = 4 THEN
3345 RETURN('TB_TOTAL_ACT_CR');
3346 ELSIF p_typeid = 5 THEN
3347 RETURN('TB_LINE_TOTAL_MC');
3348 ELSIF p_typeid = 100 THEN
3349 RETURN('TB_DUMMY');
3350 ELSIF p_typeid = -1 THEN
3351 RETURN('TB_EOF');
3352 ELSE
3353 RETURN('Invalid linetype = '|| p_typeid);
3354 END IF;
3355 END IF;
3356 END getLineType;
3357
3358 --=============================================================================
3359 -- *********** Initialization routine **********
3360 --=============================================================================
3361
3362 --=============================================================================
3363 --
3364 --
3365 --
3366 --
3367 --
3368 --
3369 --
3370 --
3371 --
3372 --
3373 -- Following code is executed when the package body is referenced for the first
3374 -- time
3375 --
3376 --
3377 --
3378 --
3379 --
3380 --
3381 --
3382 --
3383 --
3384 --
3385 --
3386 --
3387 --=============================================================================
3388
3389 BEGIN
3390 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3391 g_log_enabled := fnd_log.test
3392 (log_level => g_log_level
3393 ,module => C_DEFAULT_MODULE);
3394
3395 IF NOT g_log_enabled THEN
3396 g_log_level := C_LEVEL_LOG_DISABLED;
3397 END IF;
3398
3399 END XLA_TACCOUNTS_DATA_PKG;