[Home] [Help]
PACKAGE BODY: APPS.XLA_CA_BALANCES_PKG
Source
1 PACKAGE BODY xla_ca_balances_pkg AS
2 /* $Header: xlacainbal.pkb 120.13 2011/07/22 05:12:12 vdamerla noship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_ca_balances_pkg |
10 | |
11 | DESCRIPTION |
12 | This package is base for Import Control Account Initial Balances |
13 | program which is used to populate initial balance for all control |
14 | account while upgrading to Fusion |
15 | |
16 | HISTORY |
17 | 24-Dec-10 VDamerla Created |
18 | |
19 +======================================================================*/
20
21 --Generic Procedure/Function template
22 /*======================================================================+
23 | |
24 | Public Function |
25 | 1. before_report |
26 | This procedure is called from Import Control account initial |
27 | balance program to populate initial balance in |
28 | xla_control_balances table |
29 | |
30 | 2. after_report |
31 | This procedure is called from Import Control account initial |
32 | balance program to purge interface table |
33 | 3. Get_concatenated_value |
34 | This function is to reuturn concatenated segment value for given|
35 | code combination id |
36 | |
37 | Private Procedure |
38 | 1. Validate |
39 | This procedure validate the data in interface table and mark |
40 | them with corresponding error message code incase of any invalid|
41 | data. |
42 | |
43 | |
44 | |
45 +======================================================================*/
46
47
48 TYPE t_array_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
49 TYPE t_array_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
50 TYPE t_array_char IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
51
52 --
53 -- Global variables
54 --
55 g_user_id VARCHAR2(256);
56 g_login_id VARCHAR2(128);
57 g_date DATE;
58 g_req_id INTEGER;
59
60 g_prog_appl_id INTEGER;
61 g_prog_id INTEGER;
62
63
64 --=============================================================================
65 -- *********** Local Trace Routine **********
66 --=============================================================================
67 --
68 -- Logging levels for R12 compatibility. (DO NOT USE FOR NEW PLSQL CODE)
69 --
70 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
71 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
72 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
73 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
74 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
75 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
76
77 --
78 -- Fusion Logging levels
79 --
80 /*
81 C_LEVEL_SEVERE CONSTANT NUMBER := FND_LOG.LEVEL_SEVERE;
82 C_LEVEL_WARNING CONSTANT NUMBER := FND_LOG.LEVEL_WARNING;
83 C_LEVEL_INFO CONSTANT NUMBER := FND_LOG.LEVEL_INFO;
84 C_LEVEL_CONFIG CONSTANT NUMBER := FND_LOG.LEVEL_CONFIG;
85 C_LEVEL_FINE CONSTANT NUMBER := FND_LOG.LEVEL_FINE;
86 C_LEVEL_FINER CONSTANT NUMBER := FND_LOG.LEVEL_FINER;
87 C_LEVEL_FINEST CONSTANT NUMBER := FND_LOG.LEVEL_FINEST; */
88
89 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 1000000000;
90 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_ca_balances_pkg';
91
92 g_log_level NUMBER;
93 g_log_enabled BOOLEAN;
94
95 PROCEDURE trace
96 (p_msg IN VARCHAR2
97 ,p_level IN NUMBER
98 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
99
100 BEGIN
101 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
102 fnd_log.message(p_level, p_module);
103 ELSIF p_level >= g_log_level THEN
104 fnd_log.string(p_level, p_module, p_msg);
105 END IF;
106
107 EXCEPTION
108 WHEN xla_exceptions_pkg.application_exception THEN
109 RAISE;
110 WHEN OTHERS THEN
111 xla_exceptions_pkg.raise_message
112 (p_location => 'xla_ca_balances_pkg.trace');
113 END trace;
114
115
116 --=============================================================================
117 -- *********** Forward declartion for Private Routine **********
118 --=============================================================================
119
120
121 PROCEDURE validate;
122
123
124 --=============================================================================
125 -- *********** Body for Private Routine **********
126 --=============================================================================
127
128 FUNCTION call_update_balances RETURN BOOLEAN IS
129
130 l_int_count NUMBER;
131 l_log_module VARCHAR2(80);
132 l_array_rowid t_array_rowid;
133 l_array_ccid t_array_num;
134
135 l_array_appl_id t_array_num;
136 l_array_ledger_id t_array_num;
137 l_array_party_type_code t_array_char;
138 l_array_party_id t_array_num;
139 l_array_party_site_id t_array_num;
140 l_array_period_year t_array_num;
141 l_array_new_dr t_array_num;
142 l_array_new_cr t_array_num;
143 l_array_period_num t_array_num;
144
145
146 BEGIN
147 --
148 --
149 IF g_log_enabled THEN
150 l_log_module := C_DEFAULT_MODULE||'.call_update_balances';
151 END IF;
152
153 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
154 trace
155 (p_module => l_log_module
156 ,p_msg => 'BEGIN ' || l_log_module
157 ,p_level => C_LEVEL_PROCEDURE);
158 END IF;
159
160 --
161 -- Locks the table
162 --
163
164 LOCK TABLE xla_control_balances IN EXCLUSIVE MODE;
165
166
167 -- Set run date which will be used by report
168
169 p_run_date := 'to_date('''||to_char(g_date,'DD-MON-YYYY HH24:MI:SS')||''',''DD-MON-YYYY HH24:MI:SS'')';
170
171 --
172 -- Populate code_combination_id based on segments value where CCID is NULL
173 --
174
175 UPDATE xla_ctrl_balances_int xib SET code_combination_id =
176 (SELECT
177 gcc.code_Combination_id
178 FROM gl_code_combinations gcc
179 ,gl_ledgers gll
180 WHERE xib.ledger_id = gll.ledger_id
181 AND gll.chart_of_accounts_id = gcc.chart_of_accounts_id
182 AND NVL(xib.segment1,'X') = NVL(gcc.segment1,'X')
183 AND NVL(xib.segment2,'X') = NVL(gcc.segment2,'X')
184 AND NVL(xib.segment3,'X') = NVL(gcc.segment3,'X')
185 AND NVL(xib.segment4,'X') = NVL(gcc.segment4,'X')
186 AND NVL(xib.segment5,'X') = NVL(gcc.segment5,'X')
187 AND NVL(xib.segment6,'X') = NVL(gcc.segment6,'X')
188 AND NVL(xib.segment7,'X') = NVL(gcc.segment7,'X')
189 AND NVL(xib.segment8,'X') = NVL(gcc.segment8,'X')
190 AND NVL(xib.segment9,'X') = NVL(gcc.segment9,'X')
191 AND NVL(xib.segment10,'X') = NVL(gcc.segment10,'X')
192 AND NVL(xib.segment11,'X') = NVL(gcc.segment11,'X')
193 AND NVL(xib.segment12,'X') = NVL(gcc.segment12,'X')
194 AND NVL(xib.segment13,'X') = NVL(gcc.segment13,'X')
195 AND NVL(xib.segment14,'X') = NVL(gcc.segment14,'X')
196 AND NVL(xib.segment15,'X') = NVL(gcc.segment15,'X')
197 AND NVL(xib.segment16,'X') = NVL(gcc.segment16,'X')
198 AND NVL(xib.segment17,'X') = NVL(gcc.segment17,'X')
199 AND NVL(xib.segment18,'X') = NVL(gcc.segment18,'X')
200 AND NVL(xib.segment19,'X') = NVL(gcc.segment19,'X')
201 AND NVL(xib.segment20,'X') = NVL(gcc.segment20,'X')
202 AND NVL(xib.segment21,'X') = NVL(gcc.segment21,'X')
203 AND NVL(xib.segment22,'X') = NVL(gcc.segment22,'X')
204 AND NVL(xib.segment23,'X') = NVL(gcc.segment23,'X')
205 AND NVL(xib.segment24,'X') = NVL(gcc.segment24,'X')
206 AND NVL(xib.segment25,'X') = NVL(gcc.segment25,'X')
207 AND NVL(xib.segment26,'X') = NVL(gcc.segment26,'X')
208 AND NVL(xib.segment27,'X') = NVL(gcc.segment27,'X')
209 AND NVL(xib.segment28,'X') = NVL(gcc.segment28,'X')
210 AND NVL(xib.segment29,'X') = NVL(gcc.segment29,'X')
211 AND NVL(xib.segment30,'X') = NVL(gcc.segment30,'X')
212 ) WHERE XIB.CODE_COMBINATION_ID IS NULL;
213
214 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
215 trace
216 (p_module => l_log_module
217 ,p_msg => '# of rows in Interface table updated with ccid ' || SQL%ROWCOUNT
218 ,p_level => C_LEVEL_PROCEDURE);
219 END IF;
220 --
221 -- Validate the data in Interface Table
222 --
223
224 Validate;
225
226 -- Begin Bug 12673025
227 --
228 -- set the initial_balance_flag and first_period_flag if they are not set
229 --
230
231 --
232 -- set the initial_balance_flag='Y' for the minimum period rows for the account groups
233 -- which are being initialized
234 --
235
236 update /*+ index(xcb1,xla_control_balances_N99) */ xla_control_balances xcb1 set initial_balance_flag='Y'
237 where initial_balance_flag<> 'Y'
238 and (xcb1.application_id , xcb1.ledger_id , xcb1.code_combination_id , xcb1.party_id
239 , NVL (xcb1.party_site_id, -9999) , NVL (xcb1.party_type_code, ' ') , xcb1.effective_period_num)
240 in
241 (select xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id
242 , NVL (xcb.party_site_id, -9999)
243 , NVL (xcb.party_type_code, ' ')
244 ,min(xcb.effective_period_num)
245 from xla_control_balances xcb,
246 (select application_id, ledger_id, code_combination_id, party_id, party_site_id, party_type_code
247 from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL) stmp
248 where stmp.application_id = xcb.application_id
249 AND stmp.ledger_id = xcb.ledger_id
250 AND stmp.code_combination_id = xcb.code_combination_id
251 AND stmp.party_id = xcb.party_id
252 AND NVL (stmp.party_site_id, -9999) = NVL (xcb.party_site_id, -9999)
253 AND NVL (stmp.party_type_code, ' ') = NVL (xcb.party_type_code, ' ')
254 group by xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id
255 , xcb.party_site_id, xcb.party_type_code);
256
257 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
258 trace
259 (p_module => l_log_module
260 ,p_msg => '# of rows in xla_control_balance table updated with initial_balance_flag (Y)' || SQL%ROWCOUNT
261 ,p_level => C_LEVEL_PROCEDURE);
262 END IF;
263
264 --
265 -- reset the initial_balance_flag='N' for the previously minimum period rows
266 --
267
268 update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.initial_balance_flag ='N' where b.initial_balance_flag ='Y'
269 and ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') )
270 in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
271 from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
272 and effective_period_num
273 >
274 (select min(effective_period_num) from xla_control_balances xcb
275 where b.application_id = xcb.application_id
276 AND b.ledger_id = xcb.ledger_id
277 AND b.code_combination_id = xcb.code_combination_id
278 AND b.party_id = xcb.party_id
279 AND NVL (b.party_site_id, -9999) = NVL (xcb.party_site_id, -9999)
280 AND NVL (b.party_type_code, ' ') = NVL (xcb.party_type_code, ' '));
281
282
283 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
284 trace
285 (p_module => l_log_module
286 ,p_msg => '# of rows in xla_control_balance table updated with initial_balance_flag (N)' || SQL%ROWCOUNT
287 ,p_level => C_LEVEL_PROCEDURE);
288 END IF;
289
290 --
291 -- Fix any worngly flagged first_period_flag
292 --
293 update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.first_period_flag='N'
294 where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
295 in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
296 , NVL (xcb.party_type_code, ' '),xcb.effective_period_num
297 from
298 xla_control_balances xcb
299 , gl_period_statuses gps
300 where
301 gps.effective_period_num = xcb.effective_period_num
302 and gps.application_id = 101
303 and gps.ledger_id = xcb.ledger_id
304 and gps.period_num > 1
305 and xcb.first_period_flag = 'Y'
306 and (xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id , NVL (xcb.party_site_id, -9999) , NVL (xcb.party_type_code, ' ') )
307 in (select application_id, ledger_id, code_combination_id, party_id, nvl(party_site_id,-9999), nvl(party_type_code,' ')
308 from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL));
309
310
311 --
312 -- set the fist_period_flag='Y' for rows whose period_num=1
313 --
314
315 update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.first_period_flag='Y'
316 where ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') ,b.effective_period_num )
317 in (select xcb.application_id , xcb.ledger_id , xcb.code_combination_id , xcb.party_id, NVL (xcb.party_site_id, -9999)
318 , NVL (xcb.party_type_code, ' '),xcb.effective_period_num
319 from
320 xla_control_balances xcb
321 , gl_period_statuses gps
322 where
323 gps.effective_period_num = xcb.effective_period_num
324 and gps.application_id = 101
325 and gps.ledger_id = xcb.ledger_id
326 and gps.period_num = 1
327 and (xcb.application_id, xcb.ledger_id, xcb.code_combination_id, xcb.party_id , NVL (xcb.party_site_id, -9999) , NVL (xcb.party_type_code, ' ') )
328 in (select application_id, ledger_id, code_combination_id, party_id, nvl(party_site_id,-9999), nvl(party_type_code,' ')
329 from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL));
330
331 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
332 trace
333 (p_module => l_log_module
334 ,p_msg => '# of rows in xla_control_balance table updated with initial_balance_flag (N)' || SQL%ROWCOUNT
335 ,p_level => C_LEVEL_PROCEDURE);
336 END IF;
337 --
338 -- End Bug 12673025
339 --
340 -- Populate the existing balance from xla_control_balance to interface table
341 --
342
343 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
344 trace
345 (p_module => l_log_module
346 ,p_msg => 'Update existing Initial Balances ' || l_log_module
347 ,p_level => C_LEVEL_PROCEDURE);
348 END IF;
349 --
350 --
351
352 --
353 -- Populate existing initial balance (if interface period_name data exists in xla_control_balances for the account group)
354 --
355
356 UPDATE xla_ctrl_balances_int xib
357 SET(existing_init_balance_dr
358 ,existing_init_balance_cr
359 ,existing_effective_period_num) =
360 (SELECT beginning_balance_dr
361 ,beginning_balance_cr
362 ,glp.effective_period_num
363 FROM xla_control_balances xcb
364 ,gl_period_statuses glp
365 WHERE xcb.period_name = glp.period_name
366 AND xcb.ledger_id = glp.ledger_id
367 AND glp.application_id = 101
368 AND xcb.ledger_id = glp.ledger_id
369 AND xcb.application_id = xib.application_id
370 AND xcb.ledger_id = xib.ledger_id
371 AND xcb.code_combination_id = xib.code_combination_id
372 AND xcb.party_type_code = xib.party_type_code
373 AND xcb.party_id = xib.party_id
374 AND NVL(xcb.party_site_id,-999) = NVL(xib.party_site_id,-999)
375 AND xcb.initial_balance_flag = 'Y'
376 )
377 WHERE message_codes IS NULL
378 AND status IS NULL;
379
380 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
381 trace
382 (p_module => l_log_module
383 ,p_msg => '# rows in interface table updated with existing_initial_balance -1 : ' || SQL%ROWCOUNT
384 ,p_level => C_LEVEL_PROCEDURE);
385 END IF;
386
387
388 --
389 -- Get miniMum effective period num existing in xla_control_balances (if initializing row period_name does not exists
390 -- but periods > initializing row period_name exists in xla_control_balance table then get the min of those periods)
391 --
392
393 UPDATE xla_ctrl_balances_int xib
394 SET existing_effective_period_num =
395 ( SELECT min(glp.effective_period_num) effective_period_num
396 FROM xla_control_balances xcb
397 ,gl_period_statuses glp
398 WHERE xcb.period_name = glp.period_name
399 AND xcb.ledger_id = glp.set_of_books_id
400 AND glp.application_id = 101
401 AND xib.application_id = xcb.application_id
402 AND xib.ledger_id = xcb.ledger_id
403 AND xib.code_combination_id = xcb.code_Combination_id
404 And xib.party_type_code = xcb.party_type_code
405 ANd xib.party_id = xcb.party_id
406 AND NVL(xib.party_site_id,-999) = NVL(xcb.party_site_id,-999)
407 GROUP BY
408 xcb.application_id
409 ,xcb.ledger_id
410 ,xcb.code_combination_id
411 ,xcb.party_type_code
412 ,xcb.party_id
413 ,xcb.party_site_id)
414 WHERE message_codes IS NULL
415 AND status IS NULL
416 AND existing_effective_period_num is null;
417
418 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
419 trace
420 (p_module => l_log_module
421 ,p_msg => '# rows in interface table updated with existing_initial_balance -2 : ' || SQL%ROWCOUNT
422 ,p_level => C_LEVEL_PROCEDURE);
423 END IF;
424
425 -- Begin Bug 12673025
426 --
427 -- Populate effective_period_num for new and valid account groups which are not in xla_control_balances
428 --
429
430
431 UPDATE xla_ctrl_balances_int xib
432 SET(existing_init_balance_dr
433 ,existing_init_balance_cr
434 ,existing_effective_period_num) =
435 ( SELECT 0,
436 0,
437 max(glp.effective_period_num)
438 FROM gl_period_statuses glp
439 WHERE xib.ledger_id = glp.set_of_books_id
440 AND glp.application_id = 101
441 AND glp.adjustment_period_flag = 'N')
442 WHERE message_codes IS NULL
443 AND status IS NULL
444 AND existing_effective_period_num is null;
445
446
447 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
448 trace
449 (p_module => l_log_module
450 ,p_msg => '# rows in interface table updated with effective period num (new account groups) : ' || SQL%ROWCOUNT
451 ,p_level => C_LEVEL_PROCEDURE);
452 END IF;
453
454
455 -- End Bug 12673025
456
457
458
459 --
460 --
461 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
462 trace ( p_module => l_log_module
463 ,p_msg => 'Number of Rows update for existing balance:'||SQL%ROWCOUNT
464 ,p_level => C_LEVEL_STATEMENT );
465 END IF;
466 --
467 -- Populate row for Initial Balance in XLA_CONTROL_BALANCES
468 --
469
470 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
471 trace
472 (p_module => l_log_module
473 ,p_msg => 'Populating Initial Balances '
474 ,p_level => C_LEVEL_PROCEDURE);
475 END IF;
476
477 /* MERGE statement will first check for existing initial balance, in case
478 initial balance exist then difference of existing initial balance and new
479 initial balannce is added to Begninning Balance amount. Otherwise a new line
480 with amount equal to new initial balances is inserted into xla_control_balances */
481
482 MERGE INTO xla_control_balances xba
483 USING (SELECT xin.application_id
484 ,xin.ledger_id
485 ,xin.code_combination_id
486 ,xin.party_type_code
487 ,xin.party_id
488 ,xin.party_site_id
489 ,glp.period_name
490 ,xin.init_balance_dr
491 ,xin.init_balance_cr
492 ,xin.existing_init_balance_dr
493 ,xin.existing_init_balance_cr
494 ,glp.period_year
495 ,glp.period_num
496 ,glp.effective_period_num
497 FROM xla_ctrl_balances_int xin
498 ,gl_period_statuses glp
499 WHERE xin.ledger_id = glp.set_of_books_id
500 AND xin.period_name = glp.period_name
501 AND glp.application_id = 101
502 AND xin.message_codes IS NULL
503 AND xin.status IS NULL ) xib
504 ON (xba.application_id = xib.application_id
505 AND xba.ledger_id = xib.ledger_id
506 AND xba.period_name = xib.period_name
507 AND xba.code_combination_id = xib.code_combination_id
508 AND xba.party_type_code = xib.party_type_code
509 AND xba.party_id = xib.party_id
510 AND NVL(xba.party_site_id,-999) = NVL(xib.party_site_id,-999) )
511 WHEN MATCHED THEN
512 UPDATE
513 SET xba.beginning_balance_dr = nvl(xib.init_balance_dr,0)+ nvl(xba.beginning_balance_dr,0) - nvl(xib.existing_init_balance_dr,0)
514 ,xba.beginning_balance_cr = nvl(xib.init_balance_cr,0)+ nvl(xba.beginning_balance_cr,0) - nvl(xib.existing_init_balance_cr,0)
515 ,xba.initial_balance_flag = 'Y'
516 ,xba.first_period_flag = 'Y' ---fix to set first_period_flag issue
517 ,xba.last_update_date = g_date
518 ,xba.last_updated_by = g_user_id
519 ,xba.last_update_login = g_login_id
520 ,xba.request_id = g_req_id
521 ,xba.PROGRAM_ID = g_prog_id
522 ,xba.PROGRAM_APPLICATION_ID = g_prog_appl_id
523
524 WHEN NOT MATCHED THEN
525 INSERT(application_id
526 ,ledger_id
527 ,code_combination_id
528 ,party_type_code
529 ,party_id
530 ,party_site_id
531 ,period_name
532 ,beginning_balance_dr
533 ,beginning_balance_cr
534 ,period_balance_dr
535 ,period_balance_cr
536 ,initial_balance_flag
537 ,first_period_flag
538 ,period_year
539 ,creation_date
540 ,created_by
541 ,last_update_date
542 ,last_updated_by
543 ,last_update_login
544 ,request_id
545 ,PROGRAM_ID
546 ,PROGRAM_APPLICATION_ID
547 ,effective_period_num
548 )
549 VALUES
550 (xib.application_id
551 ,xib.ledger_id
552 ,xib.code_combination_id
553 ,xib.party_type_code
554 ,xib.party_id
555 ,xib.party_site_id
556 ,xib.period_name
557 ,xib.init_balance_dr
558 ,xib.init_balance_cr
559 ,0
560 ,0
561 ,DECODE(nvl(xib.init_balance_dr,0) - nvl(xib.init_balance_cr,0) ,0 ,'N','Y')
562 ,'Y'
563 ,xib.period_year
564 ,g_date
565 ,g_user_id
566 ,g_date
567 ,g_user_id
568 ,g_login_id
569 ,g_req_id
570 ,g_prog_id
571 ,g_prog_appl_id
572 ,xib.effective_period_num);
573
574 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
575 trace
576 (p_module => l_log_module
577 ,p_msg => '# rows merged with xla_control_balances : '||SQL%ROWCOUNT
578 ,p_level => C_LEVEL_PROCEDURE);
579 END IF;
580
581 --
582 -- reset the initial_balance_flag='N' for the previously minimum period rows
583 --
584
585 update /*+ index(b,xla_control_balances_N99) */ xla_control_balances b set b.initial_balance_flag ='N' where b.initial_balance_flag ='Y'
586 and ( b.application_id , b.ledger_id , b.code_combination_id , b.party_id, NVL (b.party_site_id, -9999) , NVL (b.party_type_code, ' ') )
587 in (select application_id, ledger_id, code_combination_id, party_id , NVL (party_site_id, -9999) , NVL (party_type_code, ' ')
588 from xla_ctrl_balances_int WHERE message_codes IS NULL AND status IS NULL)
589 and effective_period_num
590 >
591 (select min(effective_period_num) from xla_control_balances xcb
592 where b.application_id = xcb.application_id
593 AND b.ledger_id = xcb.ledger_id
594 AND b.code_combination_id = xcb.code_combination_id
595 AND b.party_id = xcb.party_id
596 AND NVL (b.party_site_id, -9999) = NVL (xcb.party_site_id, -9999)
597 AND NVL (b.party_type_code, ' ') = NVL (xcb.party_type_code, ' '));
598
599 --
600 -- Carry Forward the Initial balance to all existing balances
601 --
602 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
603 trace
604 (p_module => l_log_module
605 ,p_msg => 'Carry forwarding Initial Balances '
606 ,p_level => C_LEVEL_PROCEDURE);
607 END IF;
608
609 /* Logic used to carryforward initial balance in same year and next years
610 1. Select statement will fetch the net initial dr and cr amount for each
611 year first period ( incase of year in which initial balance is populated
612 then initial balance period will be treated as first period) for each
613 set of application_id,ledger_id,code_combination_id,party_type_code,
614 party_id, party_site_id (this combination will refer as party set info)
615
616 xla_ctrl_balances_int.existing_period_num store the minimum period
617 which exist in xla_control_balance for each party set info
618
619 In below select statement Decode(xcb.period_year,round(xib.existing_period_num
620 will identify the row whether it belong to same period in which initial balance
621 is populated or not (already we have updated and inserted the row for new initial
622 balances, refer merge statement to populate initial balance)
623
624 In below statement while calculating new Dr and Cr at the beginning we
625 have - ( beginning amount) , this is done to reverse the existing begninnig
626 balance amount and replace it with new one. This is done in next update statement
627 where the new balance amount is added to existing beginning balance. Logically
628 negative beginning balance in select statement revert the beginning balance
629 in update statement and we are left with only new value
630
631 Finally the new inital balance is calculated as difference of New initial
632 balance populated in xla_ctrl_balances_int and existing initial balance
633 in xla_control_balance (if any)
634
635 SIGN function is use to identify whether the net amount is going to be added
636 as Dr or Cr based on differenct of Dr- Cr
637
638 2.Update the xla_control_balance by adding new dr and cr amount to existing
639 beginning balance Dr and Cr
640 */
641
642
643 SELECT xcb.application_id
644 ,xcb.ledger_id
645 ,xcb.code_combination_id
646 ,xcb.party_type_code
647 ,xcb.party_id
648 ,NVL(xcb.party_site_id,-999)
649 ,xcb.period_year
650 ,(-nvl(beginning_balance_dr,0)
651 + DECODE(xcb.period_year
652 ,round(xib.existing_effective_period_num/10000)
653 ,( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
654 - nvl(existing_init_balance_dr,0)
655 )
656 ,DECODE(SIGN( ( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
657 - nvl(existing_init_balance_dr,0)
658 )
659 -( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
660 - nvl(existing_init_balance_cr,0)
661 )
662 )
663 ,1
664 ,( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
665 - nvl(existing_init_balance_dr,0)
666 )
667 -( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
668 - nvl(existing_init_balance_cr,0)
669 )
670 ,0)
671 )
672 ) new_dr
673
674 ,(-nvl(beginning_balance_cr,0)
675 + DECODE(xcb.period_year
676 ,round(xib.existing_effective_period_num/10000)
677 ,( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
678 - nvl(existing_init_balance_cr,0)
679 )
680 ,DECODE(SIGN( ( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
681 - nvl(existing_init_balance_dr,0)
682 )
683 -( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
684 - nvl(existing_init_balance_cr,0)
685 )
686 )
687 ,-1
688 ,-(( nvl(beginning_balance_dr,0) + nvl(init_balance_dr,0)
689 - nvl(existing_init_balance_dr,0)
690 )
691 -( nvl(beginning_balance_cr,0) + nvl(init_balance_cr,0)
692 - nvl(existing_init_balance_cr,0)
693 )
694 )
695 ,0
696 )
697 )
698 ) new_Cr
699
700 BULK COLLECT INTO l_array_appl_id
701 ,l_array_ledger_id
702 ,l_array_ccid
703 ,l_array_party_type_code
704 ,l_array_party_id
705 ,l_array_party_site_id
706 ,l_array_period_year
707 ,l_array_new_dr
708 ,l_array_new_cr
709 FROM xla_control_balances xcb
710 ,xla_ctrl_balances_int xib
711 WHERE xib.application_id = xcb.application_id
712 AND xib.ledger_id = xcb.ledger_id
713 AND xib.code_combination_id = xcb.code_combination_id
714 AND xib.party_type_code =xcb.party_type_code
715 AND xib.party_id = xcb.party_id
716 AND xib.party_site_id = xcb.party_site_id
717 AND xib.message_codes IS NULL
718 AND xib.status IS NULL
719 AND (xcb.first_period_flag = 'Y' OR xcb.initial_balance_flag = 'Y');
720
721 FORALL i IN 1..l_array_appl_id.count
722 UPDATE xla_control_balances
723 SET beginning_balance_dr = NVL(beginning_balance_dr,0)+ l_array_new_dr(i)
724 ,beginning_balance_cr = NVL(beginning_balance_cr,0) + l_array_new_cr(i)
725 WHERE application_id = l_array_appl_id(i)
726 AND ledger_id = l_array_ledger_id(i)
727 AND code_combination_id = l_array_ccid(i)
728 AND party_type_code = l_array_party_type_code(i)
729 AND party_id = l_array_party_id(i)
730 AND NVL(party_site_id,-999) = l_array_party_site_id(i)
731 AND period_year = l_array_period_year(i)
732 AND initial_balance_flag = 'N';
733
734 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
735 trace
736 (p_module => l_log_module
737 ,p_msg => '# rows updated with new Beginning balance : '||SQL%ROWCOUNT
738 ,p_level => C_LEVEL_PROCEDURE);
739 END IF;
740
741 --
742 -- Insert rows for carry forwarding Initial balance from Initial period to
743 -- existing first period (for already existing account groups)
744 --
745 INSERT INTO xla_control_balances
746 (application_id
747 ,ledger_id
748 ,code_combination_id
749 ,party_type_code
750 ,party_id
751 ,party_site_id
752 ,period_name
753 ,beginning_balance_dr
754 ,beginning_balance_cr
755 ,period_balance_dr
756 ,period_balance_cr
757 ,initial_balance_flag
758 ,first_period_flag
759 ,period_year
760 ,creation_date
761 ,created_by
762 ,last_update_date
763 ,last_updated_by
764 ,last_update_login
765 ,request_id
766 ,PROGRAM_ID
767 ,PROGRAM_APPLICATION_ID
768 ,effective_period_num)
769 SELECT application_id
770 ,ledger_id
771 ,code_combination_id
772 ,party_type_code
773 ,party_id
774 ,party_site_id
775 ,period_name
776 ,beginning_balance_dr
777 ,beginning_balance_cr
778 ,period_balance_dr
779 ,period_balance_cr
780 ,initial_balance_flag
781 ,first_period_flag
782 ,period_year
783 ,g_date
784 ,g_user_id
785 ,g_date
786 ,g_user_id
787 ,g_login_id
788 ,g_req_id
789 ,g_prog_id
790 ,g_prog_appl_id
791 ,effective_period_num
792 from
793 (
794 SELECT xcb.application_id
795 ,xcb.ledger_id
796 ,xcb.code_combination_id
797 ,xcb.party_type_code
798 ,xcb.party_id
799 ,xcb.party_site_id
800 ,glp1.period_name
801 ,xcb.beginning_balance_dr
802 ,xcb.beginning_balance_cr
803 ,xcb.period_balance_dr
804 ,xcb.period_balance_cr
805 ,'N' initial_balance_flag
806 ,DECODE(glp1.period_num,1,'Y','N') first_period_flag
807 ,glp1.period_year
808 ,glp1.effective_period_num
809 FROM xla_ctrl_balances_int xin
810 ,gl_period_statuses glp1
811 ,xla_control_balances xcb
812 where
813 xcb.application_id = xin.application_id
814 AND xcb.ledger_id = xin.ledger_id
815 AND xcb.code_combination_id = xin.code_combination_id
816 AND xcb.party_type_code = xcb.party_type_code
817 AND xcb.party_id = xin.party_id
818 AND NVL(xcb.party_site_id,-999) = NVL(xin.party_site_id,-999)
819 AND xin.STATUS is null
820 AND xcb.initial_balance_flag='Y'
821 AND glp1.effective_period_num between xcb.effective_period_num and xin.existing_effective_period_num
822 AND glp1.ledger_id = xin.ledger_id
823 AND glp1.application_id = 101
824 AND glp1.closing_status IN ('O', 'C', 'P')
825 AND glp1.adjustment_period_flag = 'N'
826 ) b
827 -- begin Bug12655377
828 where not exists
829 (
830 select 'x' from xla_control_balances xcb1 where
831 xcb1.application_id = b.application_id
832 AND xcb1.ledger_id = b.ledger_id
833 AND xcb1.code_combination_id = b.code_combination_id
834 AND nvl(xcb1.party_type_code,' ') = nvl(b.party_type_code,' ')
835 AND xcb1.party_id = b.party_id
836 AND NVL(xcb1.party_site_id,-999) = NVL(b.party_site_id,-999)
837 AND xcb1.effective_period_num = b.effective_period_num
838 );
839
840 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
841 trace
842 (p_module => l_log_module
843 ,p_msg => '# New balance records created : '||SQL%ROWCOUNT
844 ,p_level => C_LEVEL_PROCEDURE);
845 END IF;
846
847
848 --
849 -- Delete rows from xla_control_balance with zero balance when initial balance set to zero
850 --
851 SELECT xcb.application_id
852 ,xcb.ledger_id
853 ,xcb.code_combination_id
854 ,xcb.party_type_code
855 ,xcb.party_id
856 ,nvl(xcb.party_site_id,-999)
857 ,min(glp.effective_period_num)
858 BULK COLLECT INTO
859 l_array_appl_id
860 ,l_array_ledger_id
861 ,l_array_ccid
862 ,l_array_party_type_code
863 ,l_array_party_id
864 ,l_array_party_site_id
865 ,l_array_period_num
866 FROM xla_ctrl_balances_int xib
867 ,xla_control_balances xcb
868 ,gl_period_statuses glp
869 WHERE xib.application_id = xcb.application_id
870 AND xib.ledger_id = xcb.ledger_id
871 AND xib.code_combination_id = xcb.code_combination_id
872 AND xib.party_type_code = xcb.party_type_code
873 AND xib.party_id = xcb.party_id
874 AND nvl(xib.party_site_id ,-999) = nvl(xcb.party_site_id,-999)
875 AND xib.message_codes IS NULL
876 AND xib.status IS NULL
877 AND xcb.ledger_id = glp.set_of_books_id
878 AND glp.application_id = 101
879 AND xcb.period_name = glp.period_name
880 AND (nvl(xcb.period_balance_dr,0) <> 0 OR nvl(xcb.period_balance_cr,0) <> 0)
881 AND xib.init_balance_dr = 0 AND xib.init_balance_cr = 0
882 GROUP BY xcb.application_id
883 ,xcb.ledger_id
884 ,xcb.code_combination_id
885 ,xcb.party_type_code
886 ,xcb.party_id
887 ,nvl(xcb.party_site_id,-999);
888
889 FORALL i in 1..l_array_appl_id.count
890 DELETE FROM xla_control_balances
891 WHERE application_id = l_array_appl_id(i)
892 AND ledger_id = l_array_ledger_id(i)
893 AND code_combination_id = l_array_ccid(i)
894 AND party_type_code = l_array_party_type_code(i)
895 AND party_id = l_array_party_id(i)
896 AND NVL(party_site_id,-999) = l_array_party_site_id(i)
897 AND period_name IN (SELECT period_name
898 FROM gl_period_statuses
899 WHERE set_of_books_id = l_array_ledger_id(i)
900 AND application_id =101
901 AND effective_period_num < l_array_period_num(i))
902 AND beginning_balance_dr = 0
903 AND beginning_balance_cr = 0 ;
904 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
905 trace
906 (p_module => l_log_module
907 ,p_msg => '# rows with 0 initial balance deleted : '||SQL%ROWCOUNT
908 ,p_level => C_LEVEL_PROCEDURE);
909 END IF;
910
911 FORALL i in 1..l_array_appl_id.count
912 UPDATE xla_control_balances
913 SET first_period_flag = 'Y'
914 WHERE application_id = l_array_appl_id(i)
915 AND ledger_id = l_array_ledger_id(i)
916 AND code_combination_id = l_array_ccid(i)
917 AND party_type_code = l_array_party_type_code(i)
918 AND party_id = l_array_party_id(i)
919 AND NVL(party_site_id,-999) = l_array_party_site_id(i)
920 AND effective_period_num = l_array_period_num(i);
921 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
922 trace
923 (p_module => l_log_module
924 ,p_msg => '# rows updated with first_period_flag = Y after deleting records with 0 initial balance : '||SQL%ROWCOUNT
925 ,p_level => C_LEVEL_PROCEDURE);
926 END IF;
927 --
928 -- Update status of rows in interface table
929 --
930 UPDATE xla_ctrl_balances_int
931 SET status = 'IMPORTED'
932 ,last_update_date = g_date
933 ,last_updated_by = g_user_id
934 ,last_update_login = g_login_id
935 WHERE message_codes IS NULL
936 AND status IS NULL;
937 --
938 --
939 --
940 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
941 trace
942 (p_module => l_log_module
943 ,p_msg => 'Successful Rows Imported = ' ||SQL%ROWCOUNT
944 ,p_level => C_LEVEL_PROCEDURE);
945 END IF;
946
947
948
949 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
950 trace
951 (p_module => l_log_module
952 ,p_msg => 'End ' || l_log_module
953 ,p_level => C_LEVEL_PROCEDURE);
954 END IF;
955 RETURN TRUE;
956 EXCEPTION
957 WHEN OTHERS THEN
958 xla_exceptions_pkg.raise_message
959 (p_location => 'xla_ca_balances_pkg.call_update_balances');
960 END call_update_balances;
961
962
963 PROCEDURE validate IS
964 l_log_module VARCHAR2(80);
965
966 BEGIN
967
968 IF g_log_enabled THEN
969 l_log_module := C_DEFAULT_MODULE||'.validate';
970 END IF;
971
972 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
973 trace
974 (p_module => l_log_module
975 ,p_msg => 'BEGIN ' || l_log_module
976 ,p_level => C_LEVEL_PROCEDURE);
977 END IF;
978
979 --
980 -- Updating the invalid rows in interface table
981 --
982
983 UPDATE xla_ctrl_balances_int xin
984 SET message_codes =
985 ( SELECT NVL2(IB001,IB001||',',NULL)||
986 NVL2(IB002,IB002||',',NULL)||
987 NVL2(IB003,IB003||',',NULL)||
988 NVL2(IB004,IB004||',',NULL)||
989 NVL2(IB005,IB005||',',NULL)||
990 NVL2(IB006,IB006||',',NULL)||
991 NVL2(IB007,IB007||',',NULL)||
992 NVL2(IB008,IB008||',',NULL)||
993 NVL2(IB009,IB009||',',NULL)||
994 NVL2(IB010,IB010||',',NULL)||
995 NVL2(IB011,IB011||',',NULL)||
996 NVL2(IB012,IB012||',',NULL)||
997 NVL2( IB013 ,IB013||',' ,NVL2( IB014 ,IB014||',' ,NULL))||
998 NVL2(IB015,IB015||',',NULL)||
999 NVL2(IB016,IB016||',',NULL)||
1000 NVL2(IB017,IB017||',',NULL)||
1001 NVL2(IB018,IB018||',',NULL)||
1002 NVL2(IB019,IB019||',',NULL)||
1003 NVL2(IB020,IB020||',',NULL)||
1004 NVL2(IB021,IB021||',',NULL)||
1005 NVL2(IB023,IB023||',',NULL)||
1006 NVL2(IB024,IB024||',',NULL)|| -- error cot below but not updated, hence bug12674354
1007 NVL2(IB025,IB025||',',NULL)|| -- bug 12674383
1008 NVL2(IB026,IB026||',',NULL)||
1009 NVL2(IB027,IB027||',',NULL)
1010 FROM (
1011 SELECT xib.rowid row_id
1012 ,xib.party_id
1013 ,NVL2(xls.application_id,NULL,'IB001') IB001
1014 ,NVL2(xls.application_id,DECODE(NVL(xls.control_account_type_code,'X'),'X','IB002'
1015 ,'N','IB002'),NULL) IB002
1016 ,NVL2(gll.ledger_id,NULL,'IB003') IB003
1017 ,DECODE(gll.ledger_category_code,'PRIMARY',NULL,
1018 DECODE(NVL(glr.relationship_type_code,'N'),'SUBLEDGER',NULL,'IB004')) IB004
1019 ,NVL2(gcc.code_combination_id,NULL,'IB005') IB005
1020 ,DECODE(gll.chart_of_accounts_id,gcc.chart_of_accounts_id,NULL,'IB006') IB006
1021 ,DECODE(xla_balances_calc_pkg.is_control_account
1022 (nvl(xib.code_combination_id,0)
1023 ,NULL
1024 ,nvl(xib.ledger_id,0)
1025 ,nvl(xib.application_id,0))
1026 ,0,NULL,'IB007') IB007
1027 ,DECODE(xib.party_type_code,'S',NULL,'C',NULL,'IB008') IB008
1028 ,DECODE(xib.party_type_code,'S',NVL2(hzp.party_name,NULL,'IB009')
1029 ,'C',NVL2(hca.account_number,NULL,'IB009')) IB009
1030 ,DECODE(xib.party_type_code,'S',NVL2(apsa.vendor_site_code,NULL,'IB010')
1031 ,'C',NVL2(civ.site_use_code,NULL,'IB010')) IB010
1032 ,NVL2(glp.period_name,NULL,'IB011') IB011
1033 ,DECODE(SIGN(glp.effective_period_num-NVL(xlp.min_effect_period_num,glp.effective_period_num+1)),1,'IB012',NULL) IB012
1034 ,NVL2(xib.init_balance_dr,NULL,NVL2(xib.init_balance_cr,NULL,'IB013')) IB013
1035 ,DECODE(SIGN(xib.init_balance_dr),-1,'IB014',DECODE(SIGN(xib.init_balance_cr),-1,'IB014')) IB014
1036 ,DECODE(xib.MESSAGE_CODES,NULL,NULL,'IB015') IB015
1037 ,DECODE(xib.EXISTING_INIT_BALANCE_DR,NULL,NULL,'IB016') IB016
1038 ,DECODE(xib.EXISTING_INIT_BALANCE_CR,NULL,NULL,'IB017') IB017
1039 ,DECODE(xib.EXISTING_EFFECTIVE_PERIOD_NUM,NULL,NULL,'IB018') IB018
1040 ,DECODE(xib.REQUEST_ID ,NULL,NULL,'IB019') IB019
1041 ,DECODE(xib.PROGRAM_APPLICATION_ID ,NULL,NULL,'IB020') IB020
1042 ,DECODE(xib.PROGRAM_ID ,NULL,NULL,'IB021') IB021
1043 ,DECODE(ABS(SIGN(xib.init_balance_dr))+ABS(SIGN(xib.init_balance_cr)),2,'IB023',NULL) IB023
1044 ,DECODE(glp.period_num,1,NULL,'IB024') IB024
1045 ,DECODE(glp.closing_status,'C',NULL,'P',NULL,'IB025') IB025
1046 ,DECODE(glp.adjustment_period_flag ,'Y','IB026',NULL) IB026
1047 ,DECODE(glp.period_num,1,decode(glp2.period_num,1,decode(sign(glp.effective_period_num-xlp.min_effect_period_num),0,NULL,'IB027'),NULL),NULL) IB027
1048 FROM xla_ctrl_balances_int xib
1049 ,xla_subledgers xls
1050 ,gl_ledgers gll
1051 ,gl_ledger_relationships glr
1052 ,gl_code_combinations gcc
1053 ,ap_suppliers aps
1054 ,ap_supplier_sites_all apsa
1055 ,hz_parties hzp
1056 ,hz_cust_accounts hca
1057 ,gl_period_statuses glp
1058 ,gl_period_statuses glp2
1059 ,(SELECT hcs.site_use_id party_site_id
1060 ,hca.cust_account_id party_id
1061 ,hcs.site_use_code site_use_code
1062 FROM hz_cust_site_uses_all hcs
1063 ,hz_cust_acct_sites_all hcas
1064 ,hz_cust_accounts hca
1065 WHERE hcs.cust_acct_site_id = hcas.cust_acct_site_id
1066 AND hcas.cust_account_id = hca.cust_account_id) civ
1067 ,(
1068 SELECT cslp.application_id
1069 ,cslp.ledger_id
1070 ,cslp.code_combination_id
1071 ,cslp.party_type_code
1072 ,cslp.party_id
1073 ,cslp.party_site_id
1074 ,MIN(cslp.effective_period_num) min_effect_period_num
1075 FROM
1076 (SELECT xah.application_id
1077 ,xah.ledger_id
1078 ,xal.code_combination_id
1079 ,xal.party_type_code
1080 ,xal.party_id
1081 ,xal.party_site_id
1082 ,MIN(glp.effective_period_num) effective_period_num
1083 FROM xla_ae_headers xah
1084 ,xla_ae_lines xal
1085 ,gl_period_statuses glp
1086 ,xla_ctrl_balances_int xib
1087 WHERE xah.application_id = xal.application_id
1088 AND xah.ae_header_id = xal.ae_header_id
1089 AND glp.period_name = xah.period_name
1090 AND glp.application_id = 101
1091 AND glp.set_of_books_id = xah.ledger_id
1092 AND xah.application_id = xib.application_id
1093 AND xah.ledger_id = xib.ledger_id
1094 AND xal.code_combination_id = xib.code_combination_id
1095 AND xal.party_type_code = xib.party_type_code
1096 AND xal.party_id = xib.party_id
1097 AND NVL(xal.party_site_id,-999) = NVL(xib.party_site_id,-999)
1098 AND xal.control_balance_flag = 'Y'
1099 GROUP BY xah.application_id
1100 ,xah.ledger_id
1101 ,xal.code_combination_id
1102 ,xal.party_type_code
1103 ,xal.party_id
1104 ,xal.party_site_id
1105 UNION ALL
1106 SELECT xcb.application_id
1107 ,xcb.ledger_id
1108 ,xcb.code_combination_id
1109 ,xcb.party_type_code
1110 ,xcb.party_id
1111 ,xcb.party_site_id
1112 ,MIN(xcb.effective_period_num) effective_period_num
1113 FROM xla_control_balances xcb
1114 ,xla_ctrl_balances_int xib
1115 WHERE xcb.application_id = xib.application_id
1116 AND xcb.ledger_id = xib.ledger_id
1117 AND xcb.code_combination_id = xib.code_combination_id
1118 AND xcb.party_type_code = xib.party_type_code
1119 AND xcb.party_id = xib.party_id
1120 AND NVL(xcb.party_site_id,-999) = NVL(xib.party_site_id,-999)
1121 GROUP BY
1122 xcb.application_id
1123 ,xcb.ledger_id
1124 ,xcb.code_combination_id
1125 ,xcb.party_type_code
1126 ,xcb.party_id
1127 ,xcb.party_site_id) cslp
1128 group by cslp.application_id
1129 , cslp.ledger_id
1130 , cslp.code_combination_id
1131 , cslp.party_type_code
1132 , cslp.party_id
1133 , cslp.party_site_id ) xlp
1134 WHERE xib.application_id = xls.application_Id (+)
1135 AND xib.ledger_Id = gll.ledger_id(+)
1136 AND xib.ledger_id = glr.target_ledger_id (+)
1137 AND 'SUBLEDGER' = glr.relationship_type_code(+)
1138 AND 101 = glr.application_id(+)
1139 AND xib.code_combination_id = gcc.code_combination_id(+)
1140 AND xib.party_id = aps.vendor_id(+)
1141 AND xib.party_site_id = apsa.vendor_site_id(+)
1142 AND aps.party_id = hzp.party_id(+)
1143 AND xib.party_id = apsa.vendor_id(+)
1144 AND xib.party_id = hca.cust_account_id(+)
1145 AND xib.party_site_id = civ.party_site_id(+)
1146 AND xib.party_id = civ.party_id(+)
1147 AND xib.ledger_id = glp.set_of_books_id(+)
1148 AND xib.period_name = glp.period_name(+)
1149 AND 101 = glp.application_id(+)
1150 AND xlp.ledger_id = glp2.set_of_books_id
1151 AND xlp.min_effect_period_num= glp2.effective_period_num
1152 AND 101 = glp2.application_id
1153 AND xib.application_id = xlp.application_id(+)
1154 AND xib.ledger_id = xlp.ledger_id(+)
1155 AND xib.code_combination_id = xlp.code_combination_id(+)
1156 AND xib.party_type_code = xlp.party_type_code(+)
1157 AND xib.party_id = xlp.party_id(+)
1158 AND xib.party_site_id = xlp.party_site_id(+)
1159 AND nvl(xib.status,' ') <> 'IMPORTED' ) xtp
1160 WHERE xtp.row_id = xin.rowid );
1161 --
1162 --
1163 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1164 trace
1165 (p_module => l_log_module
1166 ,p_msg => '# rows :First set of Validation: '||SQL%ROWCOUNT
1167 ,p_level => C_LEVEL_PROCEDURE);
1168 END IF;
1169
1170
1171 --
1172 -- Marking error for multiple Initial balances of same third party information
1173 --
1174
1175 UPDATE xla_ctrl_balances_int
1176 SET message_codes = message_codes||'IB022'
1177 WHERE(application_id ,ledger_id ,code_combination_id ,party_type_code ,party_id ,nvl(party_site_id,-999))
1178 IN (SELECT application_id ,ledger_id ,code_combination_id ,party_type_code ,party_id ,nvl(party_site_id,-999)
1179 FROM xla_ctrl_balances_int
1180 WHERE nvl(status, ' ') <> 'IMPORTED'
1181 GROUP BY application_id
1182 ,ledger_id
1183 ,code_combination_id
1184 ,party_type_code
1185 ,party_id
1186 ,party_site_id
1187 HAVING COUNT(*) >1);
1188 --
1189 --
1190
1191 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1192 trace
1193 (p_module => l_log_module
1194 ,p_msg => '# rows :Second set of Validation: '||SQL%ROWCOUNT
1195 ,p_level => C_LEVEL_PROCEDURE);
1196 END IF;
1197
1198 --
1199 -- Marking Error status
1200 --
1201 UPDATE xla_ctrl_balances_int
1202 SET status = 'ERROR'
1203 ,last_update_date =g_date
1204 ,last_updated_by = g_user_id
1205 ,last_update_login = g_login_id
1206 WHERE message_codes IS NOT NULL
1207 AND status IS NULL;
1208
1209
1210 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1211 trace
1212 (p_module => l_log_module
1213 ,p_msg => 'END ' || l_log_module
1214 ,p_level => C_LEVEL_PROCEDURE);
1215 END IF;
1216
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 xla_exceptions_pkg.raise_message
1221 (p_location => 'xla_ca_balances_pkg.validate');
1222
1223 END validate;
1224
1225 FUNCTION after_report RETURN BOOLEAN
1226 IS
1227 l_log_module VARCHAR2(80);
1228
1229 BEGIN
1230
1231 IF g_log_enabled THEN
1232 l_log_module := C_DEFAULT_MODULE||'.after_report';
1233 END IF;
1234
1235 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1236 trace
1237 (p_module => l_log_module
1238 ,p_msg => 'BEGIN ' || l_log_module
1239 ,p_level => C_LEVEL_PROCEDURE);
1240 END IF;
1241
1242 --
1243 -- Logic for puging data from Interface table
1244 --
1245 CASE
1246 WHEN p_purge_option = 'S' THEN
1247 DELETE FROM xla_ctrl_balances_int
1248 WHERE message_codes IS NULL
1249 AND nvl(status,' ') = 'IMPORTED';
1250 WHEN p_purge_option = 'A' THEN
1251
1252 DELETE FROM xla_ctrl_balances_int;
1253
1254 ELSE
1255 NULL;
1256 END CASE;
1257
1258 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1259 trace ( p_module => l_log_module
1260 ,p_msg => 'Number of rows purged which are succesfully imported :'
1261 ||SQL%ROWCOUNT
1262 ,p_level => C_LEVEL_STATEMENT );
1263 END IF;
1264
1265 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1266 trace
1267 (p_module => l_log_module
1268 ,p_msg => 'END ' || l_log_module
1269 ,p_level => C_LEVEL_PROCEDURE);
1270 END IF;
1271 RETURN TRUE;
1272
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 xla_exceptions_pkg.raise_message
1276 (p_location => 'xla_ca_balances_pkg.after_report');
1277 END after_report;
1278 --=============================================================================
1279 --
1280 --
1281 --
1282 --
1283 --
1284 --
1285 --
1286 --
1287 --
1288 --
1289 -- Following code is executed when the package body is referenced for the first
1290 -- time
1291 --
1292 --
1293 --
1294 --
1295 --
1296 --
1297 --
1298 --
1299 --
1300 --
1301 --
1302 --
1303 --=============================================================================
1304 BEGIN
1305
1306 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1307 g_log_enabled := fnd_log.test
1308 (log_level => g_log_level
1309 ,module => C_DEFAULT_MODULE);
1310
1311 IF NOT g_log_enabled THEN
1312 g_log_level := C_LEVEL_LOG_DISABLED;
1313 END IF;
1314
1315 g_user_id := xla_environment_pkg.g_usr_id;
1316 g_login_id := xla_environment_pkg.g_login_id;
1317 g_date := SYSDATE;
1318 g_req_id := NVL(xla_environment_pkg.g_req_id, -1);
1319 g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
1320 g_prog_id := xla_environment_pkg.g_prog_id;
1321
1322
1323 END xla_ca_balances_pkg;