[Home] [Help]
PACKAGE BODY: APPS.XLA_SEQUENCE_DATAFIX_PKG
Source
1 PACKAGE BODY XLA_SEQUENCE_DATAFIX_PKG AS
2 -- $Header: xlaseqdf.pkb 120.3 2006/05/04 22:48:39 masada ship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | XLA_SEQUENCE_DATAFIX_PKG |
10 | |
11 | DESCRIPTION |
12 | Package body for accounting sequence datafix |
13 | |
14 | HISTORY |
15 | 07/06/2004 W. Shen Created |
16 +===========================================================================*/
17
18 --=============================================================================
19 -- **************** declaraions ********************
20 --=============================================================================
21
22
23 C_PERIOD_STATUS_C CONSTANT VARCHAR2(1) :='C'; --closed
24 C_PERIOD_STATUS_O CONSTANT VARCHAR2(1) :='O'; --open
25 C_PERIOD_STATUS_F CONSTANT VARCHAR2(1) :='F'; --future entry
26 C_PERIOD_STATUS_N CONSTANT VARCHAR2(1) :='N'; --Never opened
27 C_PERIOD_STATUS_P CONSTANT VARCHAR2(1) :='P'; --Permanently closed
28
29
30 --=============================================================================
31 -- *********** Local Trace Routine **********
32 --=============================================================================
33 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
34 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
36 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
37 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
38 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
39
40 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
41 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.XLA_SEQUENCE_DATAFIX_PKG';
42
43 g_log_level NUMBER;
44 g_log_enabled BOOLEAN;
45
46 PROCEDURE reset_accounting_seq_num(p_application_id IN NUMBER
47 , p_ledger_id IN NUMBER
48 , p_start_date IN DATE
49 , p_seq_ver_id IN NUMBER);
50
51 PROCEDURE trace
52 (p_msg IN VARCHAR2
53 ,p_level IN NUMBER
54 ,p_module IN VARCHAR2) IS
55 BEGIN
56 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
57 fnd_log.message(p_level, p_module);
58 ELSIF p_level >= g_log_level THEN
59 fnd_log.string(p_level, p_module, p_msg);
60 END IF;
61
62 EXCEPTION
63 WHEN xla_exceptions_pkg.application_exception THEN
64 RAISE;
65 WHEN OTHERS THEN
66 xla_exceptions_pkg.raise_message
67 (p_location => 'XLA_SEQUENCE_DATAFIX_PKG.trace');
68 END trace;
69
70
71 PROCEDURE resequence_acct_seq(p_errbuf OUT NOCOPY VARCHAR2
72 , p_retcode OUT NOCOPY NUMBER
73 , p_application_id IN NUMBER
74 , p_ledger_id IN NUMBER
75 , p_start_date IN DATE
76 , p_ae_header_id IN NUMBER
77 , p_period_name IN VARCHAR2
78 , p_seq_ver_id IN NUMBER) is
79 l_seq_ver_id NUMBER;
80 l_entry_status_code xla_ae_headers.accounting_entry_status_code%TYPE;
81 l_start_date DATE := null;
82 l_result NUMBER:=0;
83 l_request_id NUMBER;
84 l_seq_context_value fun_seq_batch.context_value_tbl_type;
85 l_seq_status varchar2(30);
86 l_seq_context_id NUMBER;
87 l_log_module VARCHAR2(240);
88
89 BEGIN
90 IF g_log_enabled THEN
91 l_log_module := C_DEFAULT_MODULE||'.resequence_acct_seq';
92 END IF;
93 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
94 trace
95 (p_msg => 'BEGIN of procedure resequence_acct_seq'
96 ,p_level => C_LEVEL_PROCEDURE
97 ,p_module => l_log_module);
98 END IF;
99
100 p_retcode := 0;
101
102 -- validate parameter
103 IF(p_start_date is null and p_ae_header_id is null and p_period_name is null) THEN
104 p_errbuf := 'start_date and ae_header_id and period_name can not be all null';
105 p_retcode := 2;
106 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
107 trace
108 (p_msg => 'end of procedure resequence_acct_seq'
109 ,p_level => C_LEVEL_PROCEDURE
110 ,p_module => l_log_module);
111 trace
112 (p_msg => p_errbuf
113 ,p_level => C_LEVEL_PROCEDURE
114 ,p_module => l_log_module);
115 END IF;
116 RETURN;
117 END IF;
118
119 IF(p_ae_header_id is not null) THEN
120 SELECT accounting_entry_status_code, completion_acct_seq_version_id, completed_date
121 INTO l_entry_status_code, l_seq_ver_id, l_start_date
122 FROM xla_ae_headers
123 WHERE ae_header_id = p_ae_header_id
124 AND application_id = p_application_id;
125
126 IF (l_entry_status_code <> 'F') THEN
127 p_errbuf := 'The entry with the id has not been finally accounted';
128 p_retcode := 2;
129 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
130 trace
131 (p_msg => 'end of procedure resequence_acct_seq'
132 ,p_level => C_LEVEL_PROCEDURE
133 ,p_module => l_log_module);
134 trace
135 (p_msg => p_errbuf
136 ,p_level => C_LEVEL_PROCEDURE
137 ,p_module => l_log_module);
138 END IF;
139 RETURN;
140 END IF;
141
142 IF(p_seq_ver_id is not null
143 AND p_seq_ver_id <> nvl(l_seq_ver_id, p_seq_ver_id - 1)) THEN
144 p_errbuf := 'ae_header_id and p_seq_ver_id does not match';
145 p_retcode := 2;
146 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
147 trace
148 (p_msg => 'end of procedure resequence_acct_seq'
149 ,p_level => C_LEVEL_PROCEDURE
150 ,p_module => l_log_module);
151 trace
152 (p_msg => p_errbuf
153 ,p_level => C_LEVEL_PROCEDURE
154 ,p_module => l_log_module);
155 END IF;
156 RETURN;
157 END IF;
158 END IF;
159
160 if(l_seq_ver_id is null) THEN
161 l_seq_ver_id :=p_seq_ver_id;
162 END IF;
163
164 l_start_date :=nvl(p_start_date, l_start_date);
165
166 IF(l_start_date is null) THEN
167 SELECT start_date
168 INTO l_start_date
169 FROM gl_period_statuses
170 WHERE ledger_id=p_ledger_id
171 AND application_id = 101
172 AND period_name = p_period_name;
173 END IF;
174
175 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
176 trace
177 (p_msg => 'start date is:'||to_char(l_start_date)
178 ,p_level => C_LEVEL_STATEMENT
179 ,p_module => l_log_module);
180 END IF;
181
182 l_request_id := fnd_global.conc_request_id;
183
184 l_seq_context_value(1) := p_ledger_id;
185 fun_seq_batch.Batch_init(p_application_id => 602
186 ,p_table_name => 'XLA_AE_HEADERS'
187 ,p_event_code => 'COMPLETION'
188 ,p_context_type => 'LEDGER_AND_CURRENCY'
189 ,p_context_value_tbl =>l_seq_context_value
190 ,p_request_id => l_request_id
191 ,x_status => l_seq_status
192 ,x_seq_context_id => l_seq_context_id);
193
194 IF(l_seq_status = 'NO_SEQUENCING') THEN
195 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
196 trace
197 (p_msg => 'END of procedure resequence_acct_seq, no sequencing'
198 ,p_level => C_LEVEL_PROCEDURE
199 ,p_module => l_log_module);
200 END IF;
201
202 RETURN;
203 ELSE
204 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
205 trace
206 (p_msg => 'fun_seq_batch.Batch_init executed, status success, id:'||to_char(l_seq_context_id)
207 ,p_level => C_LEVEL_STATEMENT
208 ,p_module => l_log_module);
209 END IF;
210 END IF;
211
212 IF(l_seq_ver_id is not null) THEN
213 INSERT INTO xla_events_gt
214 (event_id
215 ,application_id
216 ,ledger_id
217 ,entity_code
218 ,event_type_code
219 ,event_date
220 ,event_status_code
221 )
222 SELECT DISTINCT
223 event_id
224 ,p_application_id
225 ,p_ledger_id
226 ,'a'
227 ,'a'
228 ,sysdate
229 ,'U'
230 FROM xla_ae_headers
231 WHERE completion_acct_seq_version_id = l_seq_ver_id
232 AND ledger_id = p_ledger_id
233 AND application_id = p_application_id
234 AND accounting_entry_status_code = 'F'
235 AND completed_date >= l_start_date;
236 l_result := SQL%ROWCOUNT;
237 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
238 trace
239 (p_msg => ' ver_id is not null, # of rows inserted:'||to_char(l_result)
240 ,p_level => C_LEVEL_STATEMENT
241 ,p_module => l_log_module);
242 END IF;
243
244 ELSE
245 INSERT INTO xla_events_gt
246 (event_id
247 ,application_id
248 ,ledger_id
249 ,entity_code
250 ,event_type_code
251 ,event_date
252 ,event_status_code
253 )
254 SELECT DISTINCT
255 event_id
256 ,p_application_id
257 ,p_ledger_id
258 ,'a'
259 ,'a'
260 ,sysdate
261 ,'U'
262 FROM xla_ae_headers
263 WHERE ledger_id = p_ledger_id
264 AND application_id = p_application_id
265 AND accounting_entry_status_code = 'F'
266 AND (completed_date >= l_start_date or completed_date is null);
267 --AND completion_acct_seq_version_id is not null;
268 l_result := SQL%ROWCOUNT ;
269 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
270 trace
271 (p_msg => ' ver_id is null, # of rows inserted:'||to_char(l_result)
272 ,p_level => C_LEVEL_STATEMENT
273 ,p_module => l_log_module);
274 END IF;
275 END IF;
276 IF(l_result>0) THEN
277 reset_accounting_seq_num(p_application_id => p_application_id
278 ,p_ledger_id => p_ledger_id
279 ,p_start_date => l_start_date
280 ,p_seq_ver_id => l_seq_ver_id);
281 fun_seq_batch.populate_acct_seq_info
282 (p_calling_program => 'ACCOUNTING'
283 ,p_request_id => l_request_id);
284 END IF;
285
286 fun_seq_batch.batch_exit
287 (p_request_id => l_request_id
288 ,x_status => l_seq_status);
289 IF (C_LEVEL_EVENT >= g_log_level) THEN
290 trace
291 (p_msg => 'Procedure FUN_SEQ_BATCH.BATCH_EXIT executed'
292 ,p_level => C_LEVEL_EVENT
293 ,p_module => l_log_module);
294 END IF;
295
296 IF (C_LEVEL_EVENT>= g_log_level) THEN
297 trace
298 (p_msg => 'l_seq_status = '||l_seq_status
299 ,p_level => C_LEVEL_STATEMENT
300 ,p_module => l_log_module);
301 END IF;
302
303 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
304 trace
305 (p_msg => 'END of resequence_acct_seq'
306 ,p_level => C_LEVEL_PROCEDURE
307 ,p_module => l_log_module);
308 END IF;
309
310 EXCEPTION
311 WHEN xla_exceptions_pkg.application_exception THEN
312 p_retcode := 2;
313 p_errbuf := xla_messages_pkg.get_message;
314 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
315 trace
316 (p_msg => 'p_retcode = '||p_retcode
317 ,p_level => C_LEVEL_PROCEDURE
318 ,p_module => l_log_module);
319 trace
323 trace
320 (p_msg => 'p_errbuf = '||p_errbuf
321 ,p_level => C_LEVEL_PROCEDURE
322 ,p_module => l_log_module);
324 (p_msg => 'END of procedure resequence_acct_seq'
325 ,p_level => C_LEVEL_PROCEDURE
326 ,p_module => l_log_module);
327 END IF;
328
329 WHEN OTHERS THEN
330 p_retcode := 2;
331 p_errbuf := sqlerrm;
332 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
333 trace
334 (p_msg => 'p_retcode = '||p_retcode
335 ,p_level => C_LEVEL_PROCEDURE
336 ,p_module => l_log_module);
337 trace
338 (p_msg => 'p_errbuf = '||p_errbuf
339 ,p_level => C_LEVEL_PROCEDURE
340 ,p_module => l_log_module);
341 trace
342 (p_msg => 'END of procedure resequence_acct_seq'
343 ,p_level => C_LEVEL_PROCEDURE
344 ,p_module => l_log_module);
345 END IF;
346
347
348 END resequence_acct_seq;
349
350
351 PROCEDURE reset_accounting_seq_num(p_application_id IN NUMBER
352 , p_ledger_id IN NUMBER
353 , p_start_date IN DATE
354 , p_seq_ver_id IN NUMBER) is
355 l_seq_value NUMBER:=null;
356 l_seq_ver_id NUMBER:=null;
357
358 cursor c_seq_ver is
359 SELECT min(completion_acct_seq_value), completion_acct_seq_version_id
360 FROM xla_ae_headers
361 WHERE ledger_id = p_ledger_id
362 AND application_id = p_application_id
363 AND (completed_date >= p_start_date or completed_date is null)
364 AND accounting_entry_status_code = 'F'
365 --AND completion_acct_seq_version_id is not null
366 GROUP BY completion_acct_seq_version_id;
367
368 l_entry_status_code xla_ae_headers.accounting_entry_status_code%TYPE;
369 l_start_date DATE := null;
370 l_log_module VARCHAR2(240);
371
372 BEGIN
373
374 IF g_log_enabled THEN
375 l_log_module := C_DEFAULT_MODULE||'.reset_accounting_seq_num';
376 END IF;
377 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
378 trace
379 (p_msg => 'BEGIN of procedure reset_accounting_seq_num'
380 ,p_level => C_LEVEL_PROCEDURE
381 ,p_module => l_log_module);
382 END IF;
383
384
385 IF(p_seq_ver_id is not null) THEN
386 SELECT min(completion_acct_seq_value)
387 INTO l_seq_value
388 FROM xla_ae_headers
389 WHERE ledger_id = p_ledger_id
390 AND application_id = p_application_id
391 AND accounting_entry_status_code = 'F'
392 AND completed_date >= p_start_date
393 AND completion_acct_seq_version_id = p_seq_ver_id;
394
395 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
396 trace
397 (p_msg => 'p_seq_ver_id:'||to_char(p_seq_ver_id) || ' value:'||to_char(l_seq_value)
398 ,p_level => C_LEVEL_STATEMENT
399 ,p_module => l_log_module);
400 END IF;
401
402
403 IF(l_seq_value is not null) THEN
404 fun_seq.reset(p_seq_version_id => p_seq_ver_id
405 ,p_sequence_number => l_seq_value - 1 );
406 UPDATE xla_ae_headers
407 SET completion_acct_seq_version_id = null
408 ,completion_acct_seq_value = null
409 ,completion_acct_seq_assign_id = null
410 ,completed_date = null
411 WHERE ledger_id = p_ledger_id
412 AND application_id = p_application_id
413 AND completed_date >= p_start_date
414 AND accounting_entry_status_code = 'F'
415 AND completion_acct_seq_version_id = p_seq_ver_id;
416 END IF;
417 ELSE
418 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
419 trace
420 (p_msg => 'begin the loop to reset the seq'
421 ,p_level => C_LEVEL_STATEMENT
422 ,p_module => l_log_module);
423 END IF;
424 OPEN c_seq_ver;
425 LOOP
426 FETCH c_seq_ver into l_seq_value, l_seq_ver_id;
427 EXIT WHEN c_seq_ver%NOTFOUND;
428 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
429 trace
430 (p_msg => 'l_seq_ver_id:'||to_char(l_seq_ver_id) || ' value:'||to_char(l_seq_value)
431 ,p_level => C_LEVEL_STATEMENT
432 ,p_module => l_log_module);
433 END IF;
434 IF(l_seq_ver_id is not null) THEN
435 fun_seq.reset(p_seq_version_id => l_seq_ver_id
436 ,p_sequence_number => l_seq_value - 1);
437 END IF;
438 END LOOP;
439 CLOSE c_seq_ver;
440 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
441 trace
442 (p_msg => 'end the loop to reset the seq'
443 ,p_level => C_LEVEL_STATEMENT
444 ,p_module => l_log_module);
445 END IF;
446 UPDATE xla_ae_headers
447 SET completion_acct_seq_version_id = null
448 ,completion_acct_seq_value = null
449 ,completion_acct_seq_assign_id = null
450 ,completed_date = null
451 WHERE ledger_id = p_ledger_id
452 AND application_id = p_application_id
453 AND accounting_entry_status_code = 'F'
454 AND (completed_date >= p_start_date or completed_date is null);
455 -- AND completion_acct_seq_version_id is not null;
456 END IF;
457
458 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
459 trace
460 (p_msg => 'END of procedure reset_accounting_seq_num'
461 ,p_level => C_LEVEL_PROCEDURE
462 ,p_module => l_log_module);
463 END IF;
464
465 END reset_accounting_seq_num;
466
467 --=============================================================================
468 -- *********** Initialization routine **********
469 --=============================================================================
470
471 --=============================================================================
472 --
473 --
474 --
475 --
476 --
477 --
478 --
479 --
480 --
481 --
482 -- Following code is executed when the package body is referenced for the first
483 -- time
484 --
485 --
486 --
487 --
488 --
489 --
490 --
491 --
492 --
493 --
494 --
495 --
496 --=============================================================================
497
498 BEGIN
499 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
500 g_log_enabled := fnd_log.test
501 (log_level => g_log_level
502 ,module => C_DEFAULT_MODULE);
503
504 IF NOT g_log_enabled THEN
505 g_log_level := C_LEVEL_LOG_DISABLED;
506 END IF;
507
508 END XLA_SEQUENCE_DATAFIX_PKG;