[Home] [Help]
PACKAGE BODY: APPS.FUN_XLA_TAB_PKG
Source
1 PACKAGE BODY FUN_XLA_TAB_PKG AS
2 /* $Header: funxlatabb.pls 120.5 2010/09/21 11:25:38 srampure ship $ */
3 /*======================================================================+
4 | Copyright (c) 2004 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | FUN_XLA_TAB_PKG |
10 | |
11 | DESCRIPTION |
12 | |
13 | Transaction Account Builder API. |
14 | |
15 | Package generated by Oracle Subledger Accounting for |
16 | |
17 | Financials Common Modules |
18 | (application_id: 435) |
19 | |
20 | ATTENTION: |
21 | This package has been automatically generated by the |
22 | Oracle Subledger Accounting Compiler. You should not modify its |
23 | content manually. |
24 | This package has been generated according to the Transaction |
25 | Account Types setup for this application. |
26 | In case of issues independent of the setup (e.g. GSCC errors) |
27 | please log a bug against Oracle Subledger Accounting. |
28 | |
29 | |
30 | HISTORY |
31 | 20-SEP-10 XLA Generated by Oracle Subledger Accounting Compiler |
32 | |
33 +=======================================================================*/
34 --Private exceptions
35 le_fatal_error EXCEPTION;
36 --Private constants
37 C_API_VERSION CONSTANT NUMBER(1) := 1;
38 C_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'FUN_XLA_TAB_PKG';
39
40
41 --=============================================================================
42 -- *********** Local Trace Routine **********
43 --=============================================================================
44 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
45 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
46 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
47 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
48 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
49 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
50
51 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
52 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'fun.plsql.fun_xla_tab_pkg';
53
54 g_log_level NUMBER;
55 g_log_enabled BOOLEAN;
56
57 PROCEDURE trace
58 ( p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE
59 ,p_msg IN VARCHAR2
60 ,p_level IN NUMBER
61 )
62 IS
63 BEGIN
64 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
65 fnd_log.message(p_level, p_module);
66 ELSIF p_level >= g_log_level THEN
67 fnd_log.string(p_level, p_module, p_msg);
68 END IF;
69
70 EXCEPTION
71 WHEN app_exceptions.application_exception THEN
72 RAISE;
73 WHEN OTHERS THEN
74 fnd_message.set_name('XLA', 'XLA_TAB_UNHANDLED_EXCEPTION');
75 fnd_message.set_token( 'PROCEDURE'
76 ,'fun_xla_tab_pkg.trace');
77 RAISE;
78 END trace;
79
80 --Private procedure
81 PROCEDURE reset_online_interface
82 IS
83 l_log_module VARCHAR2 (2000);
84 BEGIN
85 IF g_log_enabled THEN
86 l_log_module := C_DEFAULT_MODULE||'.reset_online_interface';
87 END IF;
88
89 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
90 trace
91 ( p_module => l_log_module
92 ,p_msg => 'BEGIN ' || l_log_module
93 ,p_level => C_LEVEL_PROCEDURE);
94 END IF;
95
96 --Remove all the elements from the PLSQL tables
97 g_array_xla_tab.DELETE;
98
99
100 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
101 trace
102 (p_msg => 'END ' || l_log_module
103 ,p_level => C_LEVEL_PROCEDURE);
104 END IF;
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 RAISE;
109 END reset_online_interface;
110
111
112
113 --Public procedures
114 PROCEDURE run
115 (
116 p_api_version IN NUMBER
117 ,p_account_definition_type_code IN VARCHAR2
118 ,p_account_definition_code IN VARCHAR2
119 ,p_transaction_coa_id IN NUMBER
120 ,p_mode IN VARCHAR2
121 ,x_return_status OUT NOCOPY VARCHAR2
122 ,x_msg_count OUT NOCOPY NUMBER
123 ,x_msg_data OUT NOCOPY VARCHAR2
124 )
125 IS
126 l_return_status VARCHAR2(1);
127 l_return_msg_name VARCHAR2(30);
128 l_msg_count NUMBER;
129 l_msg_data VARCHAR2(2000);
130 l_log_module VARCHAR2 (2000);
131 BEGIN
132 IF g_log_enabled THEN
133 l_log_module := C_DEFAULT_MODULE||'.run';
134 END IF;
135
136 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
137 trace
138 ( p_module => l_log_module
139 ,p_msg => 'BEGIN ' || l_log_module
140 ,p_level => C_LEVEL_PROCEDURE);
141 END IF;
142
143 --Initialize the global message table
144 FND_MSG_PUB.Initialize;
145
146 xla_tab_pub_pkg.run
147 (
148 p_api_version => p_api_version
149 ,p_application_id => 435
150 ,p_account_definition_type_code => p_account_definition_type_code
151 ,p_account_definition_code => p_account_definition_code
152 ,p_transaction_coa_id => p_transaction_coa_id
153 ,p_mode => p_mode
154 ,x_return_status => l_return_status
155 ,x_msg_count => l_msg_count
156 ,x_msg_data => l_msg_data
157 );
158
159 IF l_return_status <> C_RET_STS_SUCCESS
160 THEN
161 --Push the error message again so that it does not get lost
162 IF l_msg_data IS NOT NULL
163 THEN
164 fnd_msg_pub.initialize;
165
166 fnd_message.set_encoded
167 (
168 encoded_message => l_msg_data
169 );
170
171 --Add it to the message table
172 fnd_msg_pub.add;
173
174 --Reset single message variables
175 l_msg_count := NULL;
176 l_msg_data := NULL;
177 END IF;
178 RAISE le_fatal_error;
179 END IF;
180
181 --Assign out parameters
182 x_msg_count := NVL(l_msg_count, 0);
183 x_msg_data := l_msg_data;
184 x_return_status := C_RET_STS_SUCCESS;
185
186 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
187 trace
188 ( p_module => l_log_module
189 ,p_msg => 'END ' || l_log_module
190 ,p_level => C_LEVEL_PROCEDURE);
191 END IF;
192
193 EXCEPTION
194 WHEN le_fatal_error THEN
195 --Remove all the elements from the PLSQL table
196 reset_online_interface;
197 --If there is a no token message to log
198 --Set the failure message on the stack
199 fnd_message.set_name
200 (
201 application => 'XLA'
202 ,name => 'XLA_TAB_RUN_FAILED'
203 );
204 fnd_message.set_token( 'FUNCTION_NAME'
205 ,'fun_xla_tab_pkg.run');
206
207 --Add it to the message table
208 fnd_msg_pub.add;
209
210 --If there is only one message retrieve it
211 fnd_msg_pub.Count_And_Get
212 (
213 p_count => l_msg_count
214 ,p_data => l_msg_data
215 );
216 --Put the message on the stack to ensure old Forms detect the error
217 fnd_message.set_encoded
218 (
219 encoded_message => l_msg_data
220 );
221 --Assign out parameters
222 x_msg_count := l_msg_count;
223 x_msg_data := l_msg_data;
224 IF l_return_status IS NOT NULL
225 THEN
226 x_return_status := l_return_status;
227 ELSE
228 x_return_status := C_RET_STS_UNEXP_ERROR;
229 END IF;
230 WHEN OTHERS THEN
231 --Remove all the elements from the PLSQL table
232 reset_online_interface;
233 --Add the standard unexpected error message
234 fnd_msg_pub.Add_Exc_Msg
235 ( p_pkg_name => C_PACKAGE_NAME
236 ,p_procedure_name => 'run'
237 );
238 --If there is only one message retrieve it
239 fnd_msg_pub.Count_And_Get
240 (
241 p_count => l_msg_count
242 ,p_data => l_msg_data
243 );
244 --Put the message on the stack to ensure all Forms detect the error
245 fnd_message.set_encoded
246 (
247 encoded_message => l_msg_data
248 );
249 --Assign out parameters
250 x_msg_count := l_msg_count;
251 x_msg_data := l_msg_data;
252 x_return_status := C_RET_STS_UNEXP_ERROR;
253 END run;
254
255
256 PROCEDURE reset_online_interface
257 (
258 p_api_version IN NUMBER
259 ,x_return_status OUT NOCOPY VARCHAR2
260 ,x_msg_count OUT NOCOPY NUMBER
261 ,x_msg_data OUT NOCOPY VARCHAR2
262 )
263 IS
264 l_return_status VARCHAR2(1);
265 l_return_msg_name VARCHAR2(30);
266
267 l_msg_count NUMBER;
268 l_msg_data VARCHAR2(2000);
269
270 l_log_module VARCHAR2 (2000);
271
272 BEGIN
273 IF g_log_enabled THEN
274 l_log_module := C_DEFAULT_MODULE||'.reset_online_interface';
275 END IF;
276
277 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
278 trace
279 ( p_module => l_log_module
280 ,p_msg => 'BEGIN ' || l_log_module
281 ,p_level => C_LEVEL_PROCEDURE);
282 END IF;
283
284 --Initialize the global message table
285 FND_MSG_PUB.Initialize;
286
287 --Initialize return status and message local variables
288 l_return_msg_name:= NULL;
289 l_return_status := NULL;
290
291 IF NOT FND_API.Compatible_API_Call
292 (
293 p_current_version_number => C_API_VERSION
294 ,p_caller_version_number => p_api_version
295 ,p_api_name => 'reset_online_interface'
296 ,p_pkg_name => C_PACKAGE_NAME
297 )
298 THEN
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END IF;
301
302 --Remove all the elements from the PLSQL tables
303 reset_online_interface;
304
305 --Assign out parameters
306 x_msg_count := l_msg_count;
307 x_msg_data := l_msg_data;
308
309 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
310 trace
311 ( p_module => l_log_module
312 ,p_msg => 'END ' || l_log_module
313 ,p_level => C_LEVEL_PROCEDURE);
314 END IF;
315
316 EXCEPTION
317 WHEN le_fatal_error THEN
318 --If there is a no token message to log
319 IF l_return_msg_name IS NOT NULL
320 THEN
321 --Set it on the stack
322 fnd_message.set_name
323 (
324 application => 'XLA'
325 ,name => l_return_msg_name
326 );
327 --Add it to the message table
328 fnd_msg_pub.add;
329 END IF;
330 --If there is only one message retrieve it
331 fnd_msg_pub.Count_And_Get
332 (
333 p_count => l_msg_count
334 ,p_data => l_msg_data
335 );
336 --Put the message on the stack to ensure old Forms detect the error
337 fnd_message.set_encoded
338 (
339 encoded_message => l_msg_data
340 );
341 --Assign out parameters
342 x_msg_count := l_msg_count;
343 x_msg_data := l_msg_data;
344 IF l_return_status IS NOT NULL
345 THEN
346 x_return_status := l_return_status;
347 ELSE
348 x_return_status := C_RET_STS_UNEXP_ERROR;
349 END IF;
350 WHEN OTHERS THEN
351 --Add the standard unexpected error message
352 fnd_msg_pub.Add_Exc_Msg
353 ( p_pkg_name => C_PACKAGE_NAME
354 ,p_procedure_name => 'reset_online_interface'
355 );
356 --If there is only one message retrieve it
357 fnd_msg_pub.Count_And_Get
358 (
359 p_count => l_msg_count
360 ,p_data => l_msg_data
361 );
362 --Put the message on the stack to ensure all Forms detect the error
363 fnd_message.set_encoded
364 (
365 encoded_message => l_msg_data
366 );
367 --Assign out parameters
368 x_msg_count := l_msg_count;
369 x_msg_data := l_msg_data;
370 x_return_status := C_RET_STS_UNEXP_ERROR;
371 END reset_online_interface;
372
373
374 PROCEDURE write_online_tab
375 (
376 p_api_version IN NUMBER --INPUT NOT NULL
377 ,p_source_distrib_id_num_1 IN NUMBER --INPUT
378 ,p_source_distrib_id_num_2 IN NUMBER --INPUT
379 ,p_source_distrib_id_num_3 IN NUMBER --INPUT
380 ,p_source_distrib_id_num_4 IN NUMBER --INPUT
381 ,p_source_distrib_id_num_5 IN NUMBER --INPUT
382 ,p_account_type_code IN VARCHAR2 --INPUT NOT NULL
383 --START of source list
384 ,ftb_attribute1 IN VARCHAR2 --INPUT
385 ,ftb_attribute10 IN VARCHAR2 --INPUT
386 ,ftb_attribute11 IN VARCHAR2 --INPUT
387 ,ftb_attribute12 IN VARCHAR2 --INPUT
388 ,ftb_attribute13 IN VARCHAR2 --INPUT
389 ,ftb_attribute14 IN VARCHAR2 --INPUT
390 ,ftb_attribute15 IN VARCHAR2 --INPUT
391 ,ftb_attribute2 IN VARCHAR2 --INPUT
392 ,ftb_attribute3 IN VARCHAR2 --INPUT
393 ,ftb_attribute4 IN VARCHAR2 --INPUT
394 ,ftb_attribute5 IN VARCHAR2 --INPUT
395 ,ftb_attribute6 IN VARCHAR2 --INPUT
396 ,ftb_attribute7 IN VARCHAR2 --INPUT
397 ,ftb_attribute8 IN VARCHAR2 --INPUT
398 ,ftb_attribute9 IN VARCHAR2 --INPUT
399 ,ftb_category_code IN VARCHAR2 --INPUT
400 ,ftb_currency_code IN VARCHAR2 --INPUT
401 ,ftb_from_ledger_id IN NUMBER --INPUT
402 ,ftb_from_le_id IN NUMBER --INPUT
403 ,ftb_gl_date IN DATE --INPUT
404 ,ftb_initiator_bsv IN VARCHAR2 --INPUT
405 ,ftb_initiator_id IN NUMBER --INPUT
406 ,ftd_initiator_ccid IN NUMBER --INPUT
407 ,ftd_initiator_clearing_ccid IN NUMBER --INPUT
408 ,fth_attribute1 IN VARCHAR2 --INPUT
409 ,fth_attribute10 IN VARCHAR2 --INPUT
410 ,fth_attribute11 IN VARCHAR2 --INPUT
411 ,fth_attribute12 IN VARCHAR2 --INPUT
412 ,fth_attribute13 IN VARCHAR2 --INPUT
413 ,fth_attribute14 IN VARCHAR2 --INPUT
414 ,fth_attribute15 IN VARCHAR2 --INPUT
415 ,fth_attribute2 IN VARCHAR2 --INPUT
416 ,fth_attribute3 IN VARCHAR2 --INPUT
417 ,fth_attribute4 IN VARCHAR2 --INPUT
418 ,fth_attribute5 IN VARCHAR2 --INPUT
419 ,fth_attribute6 IN VARCHAR2 --INPUT
420 ,fth_attribute7 IN VARCHAR2 --INPUT
421 ,fth_attribute8 IN VARCHAR2 --INPUT
422 ,fth_attribute9 IN VARCHAR2 --INPUT
423 ,fth_attribute_category IN VARCHAR2 --INPUT
424 ,fth_recipient_bsv IN VARCHAR2 --INPUT
425 ,fth_recipient_id IN NUMBER --INPUT
426 ,fth_to_ledger_id IN NUMBER --INPUT
427 ,fth_to_le_id IN NUMBER --INPUT
428 ,ftt_allow_interest_accr_flag IN VARCHAR2 --INPUT
429 ,ftt_allow_invoicing_flag IN VARCHAR2 --INPUT
430 ,ftt_attribute1 IN VARCHAR2 --INPUT
431 ,ftt_attribute10 IN VARCHAR2 --INPUT
432 ,ftt_attribute11 IN VARCHAR2 --INPUT
433 ,ftt_attribute12 IN VARCHAR2 --INPUT
434 ,ftt_attribute13 IN VARCHAR2 --INPUT
435 ,ftt_attribute14 IN VARCHAR2 --INPUT
436 ,ftt_attribute15 IN VARCHAR2 --INPUT
437 ,ftt_attribute2 IN VARCHAR2 --INPUT
438 ,ftt_attribute3 IN VARCHAR2 --INPUT
439 ,ftt_attribute4 IN VARCHAR2 --INPUT
440 ,ftt_attribute5 IN VARCHAR2 --INPUT
441 ,ftt_attribute6 IN VARCHAR2 --INPUT
442 ,ftt_attribute7 IN VARCHAR2 --INPUT
443 ,ftt_attribute8 IN VARCHAR2 --INPUT
444 ,ftt_attribute9 IN VARCHAR2 --INPUT
445 ,ftt_attribute_category IN VARCHAR2 --INPUT
446 ,ftt_manual_approve_flag IN VARCHAR2 --INPUT
447 ,ftt_trx_type_id IN NUMBER --INPUT
448 ,ftt_vat_taxable_flag IN VARCHAR2 --INPUT
449 --END of source list
450 ,x_return_status OUT NOCOPY VARCHAR2 --OUTPUT
451 ,x_msg_count OUT NOCOPY NUMBER --OUTPUT
452 ,x_msg_data OUT NOCOPY VARCHAR2 --OUTPUT
453 )
454 IS
455 l_return_status VARCHAR2(1);
456 l_return_msg_name VARCHAR2(30);
457 l_msg_count NUMBER;
458 l_msg_data VARCHAR2(2000);
459 l_watermark NUMBER;
460 l_start NUMBER;
461 l_new_idx NUMBER;
462
463 l_log_module VARCHAR2 (2000);
464 BEGIN
465 IF g_log_enabled THEN
466 l_log_module := C_DEFAULT_MODULE||'write_online_tab ';
467 END IF;
468
469 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
470 trace
471 ( p_module => l_log_module
472 ,p_msg => 'BEGIN ' || l_log_module
473 ,p_level => C_LEVEL_PROCEDURE);
474 END IF;
475
476 --Initialize the global message table
477 FND_MSG_PUB.Initialize;
478
479 --Initialize return status and message local variables
480 l_return_msg_name := NULL;
481 l_return_status := NULL;
482
483 IF NOT FND_API.Compatible_API_Call
484 (
485 p_current_version_number => C_API_VERSION
486 ,p_caller_version_number => p_api_version
487 ,p_api_name => 'write_online_tab '
488 ,p_pkg_name => C_PACKAGE_NAME
489 )
490 THEN
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493
494 --p_account_type_code cannot be NULL
495 IF p_account_type_code IS NULL
496 THEN
497 --Assign an error message and a return code of FAILURE
498 l_return_msg_name := 'XLA_TAB_WR_ROW_ACCT_TYPE_NULL';
499 RAISE le_fatal_error;
500 END IF;
501
502 --Get the highest index of the PL/SQL table
503 --Cannot use COUNT since some elements might have been
504 --collected and deleted
505 l_watermark := NVL(g_array_xla_tab .LAST, 1);
506 l_start := NVL(g_array_xla_tab .FIRST, 1);
507
508 --Loop on all the rows of the PL/SQL table
509 FOR i IN l_start..l_watermark
510 LOOP
511 --If the current row has the same key of the new row
512 IF g_array_xla_tab .EXISTS(i)
513 AND ( g_array_xla_tab (i).source_distribution_id_num_1 = p_source_distrib_id_num_1
514 OR g_array_xla_tab (i).source_distribution_id_num_1 IS NULL AND p_source_distrib_id_num_1 IS NULL
515 )
516 AND ( g_array_xla_tab (i).source_distribution_id_num_2 = p_source_distrib_id_num_2
517 OR g_array_xla_tab (i).source_distribution_id_num_2 IS NULL AND p_source_distrib_id_num_2 IS NULL
518 )
519 AND ( g_array_xla_tab (i).source_distribution_id_num_3 = p_source_distrib_id_num_3
520 OR g_array_xla_tab (i).source_distribution_id_num_3 IS NULL AND p_source_distrib_id_num_3 IS NULL
521 )
522 AND ( g_array_xla_tab (i).source_distribution_id_num_4 = p_source_distrib_id_num_4
523 OR g_array_xla_tab (i).source_distribution_id_num_4 IS NULL AND p_source_distrib_id_num_4 IS NULL
524 )
525 AND ( g_array_xla_tab (i).source_distribution_id_num_5 = p_source_distrib_id_num_5
526 OR g_array_xla_tab (i).source_distribution_id_num_5 IS NULL AND p_source_distrib_id_num_5 IS NULL
527 )
528 AND g_array_xla_tab (i).account_type_code = p_account_type_code
529 THEN
530 --Assign a return code of FAILURE
531 l_return_status := C_RET_STS_UNEXP_ERROR;
532 --Set the TAB message onto the message stack
533 FND_MESSAGE.SET_NAME
534 (
535 application => 'XLA'
536 ,name => 'XLA_TAB_WR_ROW_DUPLICATE'
537 );
538
539 --Replace the token for the flex message retrieved above
540 FND_MESSAGE.SET_TOKEN
541 (
542 token => 'TRX_ACCT_TYPE_CODE'
543 ,value => g_array_xla_tab (i).account_type_code
544 );
545 --Replace the token for the flex message retrieved above
546 FND_MESSAGE.SET_TOKEN
547 (
548 token => 'SOURCE_DIST_ID_NUM1'
549 ,value => g_array_xla_tab (i).source_distribution_id_num_1
550 );
551 --Replace the token for the flex message retrieved above
552 FND_MESSAGE.SET_TOKEN
553 (
554 token => 'SOURCE_DIST_ID_NUM2'
555 ,value => g_array_xla_tab (i).source_distribution_id_num_2
556 );
557 --Replace the token for the flex message retrieved above
558 FND_MESSAGE.SET_TOKEN
559 (
560 token => 'SOURCE_DIST_ID_NUM3'
561 ,value => g_array_xla_tab (i).source_distribution_id_num_3
562 );
563 --Replace the token for the flex message retrieved above
564 FND_MESSAGE.SET_TOKEN
565 (
566 token => 'SOURCE_DIST_ID_NUM4'
567 ,value => g_array_xla_tab (i).source_distribution_id_num_4
568 );
569 --Replace the token for the flex message retrieved above
570 FND_MESSAGE.SET_TOKEN
571 (
572 token => 'SOURCE_DIST_ID_NUM5'
573 ,value => g_array_xla_tab (i).source_distribution_id_num_5
574 );
575
576 fnd_msg_pub.add;
577
578 --Raise a local exception
579 RAISE le_fatal_error;
580 --Elsif the current row has already been processed
581 ELSIF g_array_xla_tab .EXISTS(i)
582 AND g_array_xla_tab (i).target_ccid IS NOT NULL
583 AND g_array_xla_tab (i).msg_data IS NOT NULL
584 THEN
585 --It means the caller has previously uploaded the interface and run
586 --the processing but has not collected all the results.
587 --Assign an error message and a return code of FAILURE
588 l_return_msg_name := 'XLA_TAB_WR_ROW_PROCESSED';
589 l_return_status := C_RET_STS_UNEXP_ERROR;
590 --Raise a local exception
591 RAISE le_fatal_error;
592 END IF;
593 END LOOP;
594
595 --Get the index of the new row
596 l_new_idx := g_array_xla_tab .COUNT + 1;
597 --Assign the values to the new row
598 g_array_xla_tab (l_new_idx).source_distribution_id_num_1 := p_source_distrib_id_num_1;
599 g_array_xla_tab (l_new_idx).source_distribution_id_num_2 := p_source_distrib_id_num_2;
600 g_array_xla_tab (l_new_idx).source_distribution_id_num_3 := p_source_distrib_id_num_3;
601 g_array_xla_tab (l_new_idx).source_distribution_id_num_4 := p_source_distrib_id_num_4;
602 g_array_xla_tab (l_new_idx).source_distribution_id_num_5 := p_source_distrib_id_num_5;
603 g_array_xla_tab (l_new_idx).account_type_code := p_account_type_code;
604 --START of source list
605 g_array_xla_tab (l_new_idx).ftb_attribute1 := ftb_attribute1;
606 g_array_xla_tab (l_new_idx).ftb_attribute10 := ftb_attribute10;
607 g_array_xla_tab (l_new_idx).ftb_attribute11 := ftb_attribute11;
608 g_array_xla_tab (l_new_idx).ftb_attribute12 := ftb_attribute12;
609 g_array_xla_tab (l_new_idx).ftb_attribute13 := ftb_attribute13;
610 g_array_xla_tab (l_new_idx).ftb_attribute14 := ftb_attribute14;
611 g_array_xla_tab (l_new_idx).ftb_attribute15 := ftb_attribute15;
612 g_array_xla_tab (l_new_idx).ftb_attribute2 := ftb_attribute2;
613 g_array_xla_tab (l_new_idx).ftb_attribute3 := ftb_attribute3;
614 g_array_xla_tab (l_new_idx).ftb_attribute4 := ftb_attribute4;
615 g_array_xla_tab (l_new_idx).ftb_attribute5 := ftb_attribute5;
616 g_array_xla_tab (l_new_idx).ftb_attribute6 := ftb_attribute6;
617 g_array_xla_tab (l_new_idx).ftb_attribute7 := ftb_attribute7;
618 g_array_xla_tab (l_new_idx).ftb_attribute8 := ftb_attribute8;
619 g_array_xla_tab (l_new_idx).ftb_attribute9 := ftb_attribute9;
620 g_array_xla_tab (l_new_idx).ftb_category_code := ftb_category_code;
621 g_array_xla_tab (l_new_idx).ftb_currency_code := ftb_currency_code;
622 g_array_xla_tab (l_new_idx).ftb_from_ledger_id := ftb_from_ledger_id;
623 g_array_xla_tab (l_new_idx).ftb_from_le_id := ftb_from_le_id;
624 g_array_xla_tab (l_new_idx).ftb_gl_date := ftb_gl_date;
625 g_array_xla_tab (l_new_idx).ftb_initiator_bsv := ftb_initiator_bsv;
626 g_array_xla_tab (l_new_idx).ftb_initiator_id := ftb_initiator_id;
627 g_array_xla_tab (l_new_idx).ftd_initiator_ccid := ftd_initiator_ccid;
628 g_array_xla_tab (l_new_idx).ftd_initiator_clearing_ccid := ftd_initiator_clearing_ccid;
629 g_array_xla_tab (l_new_idx).fth_attribute1 := fth_attribute1;
630 g_array_xla_tab (l_new_idx).fth_attribute10 := fth_attribute10;
631 g_array_xla_tab (l_new_idx).fth_attribute11 := fth_attribute11;
632 g_array_xla_tab (l_new_idx).fth_attribute12 := fth_attribute12;
633 g_array_xla_tab (l_new_idx).fth_attribute13 := fth_attribute13;
634 g_array_xla_tab (l_new_idx).fth_attribute14 := fth_attribute14;
635 g_array_xla_tab (l_new_idx).fth_attribute15 := fth_attribute15;
636 g_array_xla_tab (l_new_idx).fth_attribute2 := fth_attribute2;
637 g_array_xla_tab (l_new_idx).fth_attribute3 := fth_attribute3;
638 g_array_xla_tab (l_new_idx).fth_attribute4 := fth_attribute4;
639 g_array_xla_tab (l_new_idx).fth_attribute5 := fth_attribute5;
640 g_array_xla_tab (l_new_idx).fth_attribute6 := fth_attribute6;
641 g_array_xla_tab (l_new_idx).fth_attribute7 := fth_attribute7;
642 g_array_xla_tab (l_new_idx).fth_attribute8 := fth_attribute8;
643 g_array_xla_tab (l_new_idx).fth_attribute9 := fth_attribute9;
644 g_array_xla_tab (l_new_idx).fth_attribute_category := fth_attribute_category;
645 g_array_xla_tab (l_new_idx).fth_recipient_bsv := fth_recipient_bsv;
646 g_array_xla_tab (l_new_idx).fth_recipient_id := fth_recipient_id;
647 g_array_xla_tab (l_new_idx).fth_to_ledger_id := fth_to_ledger_id;
648 g_array_xla_tab (l_new_idx).fth_to_le_id := fth_to_le_id;
649 g_array_xla_tab (l_new_idx).ftt_allow_interest_accr_flag := ftt_allow_interest_accr_flag;
650 g_array_xla_tab (l_new_idx).ftt_allow_invoicing_flag := ftt_allow_invoicing_flag;
651 g_array_xla_tab (l_new_idx).ftt_attribute1 := ftt_attribute1;
652 g_array_xla_tab (l_new_idx).ftt_attribute10 := ftt_attribute10;
653 g_array_xla_tab (l_new_idx).ftt_attribute11 := ftt_attribute11;
654 g_array_xla_tab (l_new_idx).ftt_attribute12 := ftt_attribute12;
655 g_array_xla_tab (l_new_idx).ftt_attribute13 := ftt_attribute13;
656 g_array_xla_tab (l_new_idx).ftt_attribute14 := ftt_attribute14;
657 g_array_xla_tab (l_new_idx).ftt_attribute15 := ftt_attribute15;
658 g_array_xla_tab (l_new_idx).ftt_attribute2 := ftt_attribute2;
659 g_array_xla_tab (l_new_idx).ftt_attribute3 := ftt_attribute3;
660 g_array_xla_tab (l_new_idx).ftt_attribute4 := ftt_attribute4;
661 g_array_xla_tab (l_new_idx).ftt_attribute5 := ftt_attribute5;
662 g_array_xla_tab (l_new_idx).ftt_attribute6 := ftt_attribute6;
663 g_array_xla_tab (l_new_idx).ftt_attribute7 := ftt_attribute7;
664 g_array_xla_tab (l_new_idx).ftt_attribute8 := ftt_attribute8;
665 g_array_xla_tab (l_new_idx).ftt_attribute9 := ftt_attribute9;
666 g_array_xla_tab (l_new_idx).ftt_attribute_category := ftt_attribute_category;
667 g_array_xla_tab (l_new_idx).ftt_manual_approve_flag := ftt_manual_approve_flag;
668 g_array_xla_tab (l_new_idx).ftt_trx_type_id := ftt_trx_type_id;
669 g_array_xla_tab (l_new_idx).ftt_vat_taxable_flag := ftt_vat_taxable_flag;
670 --END of source list
671 g_array_xla_tab (l_new_idx).target_ccid := NULL;
672 g_array_xla_tab (l_new_idx).msg_data := NULL;
673
674 --Assign out parameters
675 x_return_status := C_RET_STS_SUCCESS;
676 x_msg_data := NULL;
677 x_msg_count := 0;
678
679
680 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
681 trace
682 ( p_module => l_log_module
683 ,p_msg => 'END ' || l_log_module
684 ,p_level => C_LEVEL_PROCEDURE);
685 END IF;
686
687 EXCEPTION
688 WHEN le_fatal_error THEN
689 --Remove all the elements from the PLSQL table
690 g_array_xla_tab .DELETE;
691 --If there is a no token message to log
692 IF l_return_msg_name IS NOT NULL
693 THEN
694 --Set it on the stack
695 fnd_message.set_name
696 (
697 application => 'XLA'
698 ,name => l_return_msg_name
699 );
700 --Add it to the message table
701 fnd_msg_pub.add;
702 END IF;
703 --If there is only one message retrieve it
704 fnd_msg_pub.Count_And_Get
705 (
706 p_count => l_msg_count
707 ,p_data => l_msg_data
708 );
709 --Put the message on the stack to ensure old Forms detect the error
710 fnd_message.set_encoded
711 (
712 encoded_message => l_msg_data
713 );
714 --Assign out parameters
715 x_msg_count := l_msg_count;
716 x_msg_data := l_msg_data;
717 IF l_return_status IS NOT NULL
718 THEN
719 x_return_status := l_return_status;
720 ELSE
721 x_return_status := C_RET_STS_UNEXP_ERROR;
722 END IF;
723 WHEN OTHERS THEN
724 --Remove all the elements from the PLSQL table
725 g_array_xla_tab .DELETE;
726 --Add the standard unexpected error message
727 fnd_msg_pub.Add_Exc_Msg
728 ( p_pkg_name => C_PACKAGE_NAME
729 ,p_procedure_name => 'write_online_tab '
730 );
731 --If there is only one message retrieve it
732 fnd_msg_pub.Count_And_Get
733 (
734 p_count => l_msg_count
735 ,p_data => l_msg_data
736 );
737 --Put the message on the stack to ensure all Forms detect the error
738 fnd_message.set_encoded
739 (
740 encoded_message => l_msg_data
741 );
742 --Assign out parameters
743 x_msg_count := l_msg_count;
744 x_msg_data := l_msg_data;
745 x_return_status := C_RET_STS_UNEXP_ERROR;
746 END write_online_tab ;
747
748
749 PROCEDURE read_online_tab
750 (
751 p_api_version IN NUMBER
752 ,p_source_distrib_id_num_1 IN NUMBER
753 ,p_source_distrib_id_num_2 IN NUMBER
754 ,p_source_distrib_id_num_3 IN NUMBER
755 ,p_source_distrib_id_num_4 IN NUMBER
756 ,p_source_distrib_id_num_5 IN NUMBER
757 ,p_account_type_code IN VARCHAR2
758 ,x_target_ccid OUT NOCOPY NUMBER
759 ,x_concatenated_segments OUT NOCOPY VARCHAR2
760 ,x_return_status OUT NOCOPY VARCHAR2
761 ,x_msg_count OUT NOCOPY NUMBER
762 ,x_msg_data OUT NOCOPY VARCHAR2
763 )
764 IS
765 TYPE lt_table_V2000 IS TABLE OF VARCHAR2(2000);
766
767 l_return_status VARCHAR2(1);
768 l_return_msg_name VARCHAR2(30);
769
770 l_msg_count NUMBER;
771 l_msg_data VARCHAR2(2000);
772
773 l_found BOOLEAN;
774 l_watermark NUMBER;
775 l_start NUMBER;
776 l_row_base_rowid UROWID;
777 l_row_target_ccid NUMBER;
778 l_row_concatenated_segments VARCHAR2(2000);
779 l_row_msg_count NUMBER;
780 l_row_msg_data VARCHAR2(2000);
781 l_table_of_row_errors lt_table_V2000;
782
783 l_log_module VARCHAR2 (2000);
784 BEGIN
785 IF g_log_enabled THEN
786 l_log_module := C_DEFAULT_MODULE||'read_online_tab ';
787 END IF;
788
789 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
790 trace
791 ( p_module => l_log_module
792 ,p_msg => 'BEGIN ' || l_log_module
793 ,p_level => C_LEVEL_PROCEDURE);
794 END IF;
795
796 --Initialize the global message table
797 FND_MSG_PUB.Initialize;
798
799 --Initialize return status and message local variables
800 l_return_msg_name:= NULL;
801 l_return_status := NULL;
802
803 IF NOT FND_API.Compatible_API_Call
804 (
805 p_current_version_number => C_API_VERSION
806 ,p_caller_version_number => p_api_version
807 ,p_api_name => 'read_online_tab '
808 ,p_pkg_name => C_PACKAGE_NAME
809 )
810 THEN
811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
812 END IF;
813
814 --Start the actual logic
815
816 --p_account_type_code cannot be NULL
817 IF p_account_type_code IS NULL
818 THEN
819 --Assign an error message and a return code of FAILURE
820 l_return_msg_name := 'XLA_TAB_RD_ROW_ACCT_TYPE_NULL';
821 RAISE le_fatal_error;
822 END IF;
823
824 l_found := FALSE;
825
826 --Get the highest index of the PL/SQL table
827 --Cannot use COUNT since some elements might have been
828 --collected and deleted
829 l_watermark := NVL(g_array_xla_tab .LAST, 1);
830 l_start := NVL(g_array_xla_tab .FIRST, 1);
831
832 --Loop on all the rows of the PL/SQL table
833 FOR i IN l_start..l_watermark
834 LOOP
835 --If the current row identifiers correspond to the IN parameters
836 IF g_array_xla_tab .EXISTS(i)
837 AND ( g_array_xla_tab (i).source_distribution_id_num_1 = p_source_distrib_id_num_1
838 OR g_array_xla_tab (i).source_distribution_id_num_1 IS NULL AND p_source_distrib_id_num_1 IS NULL
839 )
840 AND ( g_array_xla_tab (i).source_distribution_id_num_2 = p_source_distrib_id_num_2
841 OR g_array_xla_tab (i).source_distribution_id_num_2 IS NULL AND p_source_distrib_id_num_2 IS NULL
842 )
843 AND ( g_array_xla_tab (i).source_distribution_id_num_3 = p_source_distrib_id_num_3
844 OR g_array_xla_tab (i).source_distribution_id_num_3 IS NULL AND p_source_distrib_id_num_3 IS NULL
845 )
846 AND ( g_array_xla_tab (i).source_distribution_id_num_4 = p_source_distrib_id_num_4
847 OR g_array_xla_tab (i).source_distribution_id_num_4 IS NULL AND p_source_distrib_id_num_4 IS NULL
848 )
849 AND ( g_array_xla_tab (i).source_distribution_id_num_5 = p_source_distrib_id_num_5
850 OR g_array_xla_tab (i).source_distribution_id_num_5 IS NULL AND p_source_distrib_id_num_5 IS NULL
851 )
852 AND g_array_xla_tab (i).account_type_code = p_account_type_code
853 THEN
854 --Set the element found flag
855 l_found := TRUE;
856
857 --Assign the target ccid and encoded message to local variables
858 l_row_base_rowid :=
859 g_array_xla_tab (i).base_rowid;
860
861 l_row_target_ccid :=
862 g_array_xla_tab (i).target_ccid;
863
864 l_row_concatenated_segments :=
865 g_array_xla_tab (i).concatenated_segments;
866
867 l_row_msg_count :=
868 NVL(g_array_xla_tab (i).msg_count, 0);
869
870 l_row_msg_data :=
871 g_array_xla_tab (i).msg_data;
872
873 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
874 trace
875 (p_module => l_log_module
876 ,p_msg => 'l_row_base_rowid = ' || l_row_base_rowid
877 ,p_level => C_LEVEL_STATEMENT);
878 trace
879 (p_module => l_log_module
880 ,p_msg => 'l_row_target_ccid = ' || l_row_target_ccid
881 ,p_level => C_LEVEL_STATEMENT);
882 trace
883 (p_module => l_log_module
884 ,p_msg => 'l_row_msg_count = ' || l_row_msg_count
885 ,p_level => C_LEVEL_STATEMENT);
886 trace
887 (p_module => l_log_module
888 ,p_msg => 'l_row_msg_data = ' || l_row_msg_data
889 ,p_level => C_LEVEL_STATEMENT);
890 END IF;
891
892 --If the line has not been processed raise an error
893 IF l_row_target_ccid IS NULL
894 AND l_row_msg_count = 0
895 THEN
896 l_return_msg_name := 'XLA_TBA_RD_ROW_UNPROCESSED';
897 --Raise a local exception
898 RAISE le_fatal_error;
899 END IF;
900
901 --remove the element
902 g_array_xla_tab .DELETE(i);
903
904 --exit the loop
905 EXIT;
906 END IF;
907 END LOOP;
908
909 --If no match found raise an error
910 IF NOT l_found
911 THEN
912 l_return_msg_name := 'XLA_TBA_RD_ROW_NOT_FOUND';
913 RAISE le_fatal_error;
914 END IF;
915
916 --If the row has only one error push it on the stack
917 IF l_row_msg_count = 1
918 THEN
919 --push it on the message stack
920 fnd_message.set_encoded
921 (
922 encoded_message => l_row_msg_data
923 );
924 --Add it to the message table
925 fnd_msg_pub.add;
926 --If there is only one message retrieve it
927 fnd_msg_pub.count_and_get
928 (
929 p_count => l_msg_count
930 ,p_data => l_msg_data
931 );
932 --Put the message on the stack to ensure old Forms detect the error
933 fnd_message.set_encoded
934 (
935 encoded_message => l_msg_data
936 );
937 --Set the return values
938 l_msg_count := l_msg_count;
939 l_msg_data := l_msg_data;
940 --Set return status
941 l_return_status := C_RET_STS_ERROR;
942 --If the row has more than one error fetch them from the error table
943 ELSIF l_row_msg_count > 1
944 THEN
945 --Read the errors from XLA_TAB_ERRORS_GT and push them on the stack
946 SELECT xte.msg_data
947 BULK COLLECT
948 INTO l_table_of_row_errors
949 FROM xla_tab_errors_gt xte
950 WHERE xte.base_rowid = l_row_base_rowid;
951 --Loop on the errors and push them on the stack
952 FOR i IN l_table_of_row_errors.FIRST .. l_table_of_row_errors.LAST
953 LOOP
954 --Push the current message on the stack
955 fnd_message.set_encoded
956 (
957 encoded_message => l_table_of_row_errors(i)
958 );
959 --Add the stacked message to the table
960 fnd_msg_pub.add;
961 END LOOP;
962 --Set the return values
963 l_msg_count := l_row_msg_count;
964 l_msg_data := NULL;
965 l_return_status := C_RET_STS_ERROR;
966 ELSE
967 --The row has been found and has no errors
968 l_msg_count := 0;
969 l_msg_data := NULL;
970 l_return_status := C_RET_STS_SUCCESS;
971 END IF;
972
973 --Assign out parameters
974 x_target_ccid := l_row_target_ccid;
975 x_concatenated_segments := l_row_concatenated_segments;
976 x_msg_count := l_msg_count;
977 x_msg_data := l_msg_data;
978
979 IF l_return_status IS NOT NULL
980 THEN
981 x_return_status := l_return_status;
982 ELSE
983 x_return_status := C_RET_STS_UNEXP_ERROR;
984 END IF;
985
986 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
987 trace
988 ( p_module => l_log_module
989 ,p_msg => 'END ' || l_log_module
990 ,p_level => C_LEVEL_PROCEDURE);
991 END IF;
992
993 EXCEPTION
994 WHEN le_fatal_error THEN
995 --Remove all the elements from the PLSQL table
996 g_array_xla_tab .DELETE;
997 --If there is a no token message to log
998 IF l_return_msg_name IS NOT NULL
999 THEN
1000 --Set it on the stack
1001 fnd_message.set_name
1002 (
1003 application => 'XLA'
1004 ,name => l_return_msg_name
1005 );
1006 --Add it to the message table
1007 fnd_msg_pub.add;
1008 END IF;
1009 --If there is only one message retrieve it
1010 fnd_msg_pub.Count_And_Get
1011 (
1012 p_count => l_msg_count
1013 ,p_data => l_msg_data
1014 );
1015 --Put the message on the stack to ensure old Forms detect the error
1016 fnd_message.set_encoded
1017 (
1018 encoded_message => l_msg_data
1019 );
1020 --Assign out parameters
1021 x_msg_count := l_msg_count;
1022 x_msg_data := l_msg_data;
1023 IF l_return_status IS NOT NULL
1024 THEN
1025 x_return_status := l_return_status;
1026 ELSE
1027 x_return_status := C_RET_STS_UNEXP_ERROR;
1028 END IF;
1029 WHEN OTHERS THEN
1030 --Remove all the elements from the PLSQL table
1031 g_array_xla_tab .DELETE;
1032 --Add the standard unexpected error message
1033 fnd_msg_pub.Add_Exc_Msg
1034 ( p_pkg_name => C_PACKAGE_NAME
1035 ,p_procedure_name => 'read_online_tab '
1036 );
1037 --If there is only one message retrieve it
1038 fnd_msg_pub.Count_And_Get
1039 (
1040 p_count => l_msg_count
1041 ,p_data => l_msg_data
1042 );
1043 --Put the message on the stack to ensure all Forms detect the error
1044 fnd_message.set_encoded
1045 (
1046 encoded_message => l_msg_data
1047 );
1048 --Assign out parameters
1049 x_msg_count := l_msg_count;
1050 x_msg_data := l_msg_data;
1051 x_return_status := C_RET_STS_UNEXP_ERROR;
1052 END read_online_tab ;
1053
1054
1055
1056 --Trace initialization
1057 BEGIN
1058 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1059 g_log_enabled := fnd_log.test
1060 (log_level => g_log_level
1061 ,module => C_DEFAULT_MODULE);
1062
1063 IF NOT g_log_enabled THEN
1064 g_log_level := C_LEVEL_LOG_DISABLED;
1065 END IF;
1066
1067
1068 END FUN_XLA_TAB_PKG;