[Home] [Help]
PACKAGE BODY: APPS.XLA_THIRD_PARTY_MERGE
Source
1 PACKAGE BODY XLA_THIRD_PARTY_MERGE AS
2 -- $Header: xlamerge.pkb 120.25.12020000.6 2013/05/07 10:34:55 vkanteti ship $
3 /*===========================================================================+
4 | Copyright (c) 2005 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | FILENAME |
9 | xlamerge.pkb |
10 | |
11 | PACKAGE NAME |
12 | xla_third_party_merge |
13 | |
14 | DESCRIPTION |
15 | This is a XLA private package, which contains all the APIs required for |
16 | creating Third Party Merge events. |
17 | |
18 | The public wrapper called xla_third_party_merge_pub, is built based on |
19 | this package. |
20 | |
21 | Note: |
22 | - the APIs may perform ROLLBACK for what changes they have made |
23 | - these APIs are not supposed to raise any exception |
24 | |
25 | HISTORY |
26 | 08-Sep-05 L. Poon Created |
27 | 03-Mar-05 V. Kumar Bug 5041325 Populating GL_SL_LINK_ID in xla_ae_lines|
28 | 21-Jun-2006 A.Wan 5100860 Performance fix, see bug for detail |
29 | 01-Oct-2012 vkanteti 14773226 Performance saving receipt changing cust |
30 | 25-Apr-2013 vkanteti 16759107 Dynamic SQL correction and Debugs added |
31 +===========================================================================*/
32
33 -------------------------------------------------------------------------------
34 -- Private types
35 -------------------------------------------------------------------------------
36 TYPE t_number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
37 TYPE t_varchar30_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
38 TYPE t_varchar1_array IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
39 TYPE t_date_array IS TABLE OF DATE INDEX BY BINARY_INTEGER;
40 type t_rowid_array is table of rowid index by binary_integer;
41
42 --=============================================================================
43 -- *********** Local Trace and Log Routines **********
44 --=============================================================================
45 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
46 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
47 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
48 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
49 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
50 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
51 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
52 C_BULK_LIMIT CONSTANT NUMBER := 3000;
53 C_WORK_UNIT CONSTANT NUMBER := 2000;
54 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_third_party_merge';
55
56 C_CREATED CONSTANT VARCHAR2(8) :='CREATED';
57
58 g_debug_flag VARCHAR2(1) := NVL( fnd_profile.value('XLA_DEBUG_TRACE')
59 , 'N');
60 g_use_ledger_security VARCHAR2(1) := NVL( fnd_profile.value
61 ('XLA_USE_LEDGER_SECURITY')
62 , 'N');
63 g_access_set_id NUMBER(15) := NVL( fnd_profile.value('GL_ACCESS_SET_ID')
64 , -1);
65 g_sec_access_set_id NUMBER(15) := NVL( fnd_profile.value
66 ('XLA_GL_SECONDARY_ACCESS_SET_ID')
67 , -1);
68
69 g_log_level NUMBER;
70 g_log_enabled BOOLEAN;
71
72 ValidationError EXCEPTION;
73 NoAccountingDateError EXCEPTION;
74 AccountingError EXCEPTION;
75 MissingCCIDError EXCEPTION;
76 BalanceError EXCEPTION;
77 LastRequestRunning EXCEPTION;
78
79 PROCEDURE delete_je(
80 p_application_id IN INTEGER
81 , p_event_id IN INTEGER);
82
83 PROCEDURE process_accounting_mapping(
84 p_application_id IN NUMBER
85 ,p_event_id IN NUMBER);
86
87 PROCEDURE generate_headers(
88 p_application_id IN NUMBER
89 ,p_reverse_header_desc IN VARCHAR2
90 ,p_accounting_mode IN VARCHAR2
91 );
92 PROCEDURE process_incomplete_acct_map(
93 p_application_id IN NUMBER
94 ,p_event_id IN NUMBER
95 ,p_event_merge_option IN VARCHAR2
96 ,p_entity_id IN NUMBER
97 ,p_merge_date IN DATE
98 ,p_merge_type IN VARCHAR2
99 ,p_old_site_id IN NUMBER
100 ,p_old_party_id IN NUMBER
101 ,p_new_site_id IN NUMBER
102 ,p_new_party_id IN NUMBER
103 ,p_party_type IN VARCHAR2
104 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
105 ,p_array_ledger_category IN t_varchar30_array
106 ,p_array_reversal_option IN t_varchar30_array
107 ,p_array_merge_option IN t_varchar30_array);
108
109 PROCEDURE trace
110 ( p_msg IN VARCHAR2
111 , p_level IN NUMBER
112 , p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
113 BEGIN
114 IF (p_msg IS NULL AND p_level >= g_log_level)
115 THEN
116 fnd_log.message(p_level, p_module);
117 ELSIF p_level >= g_log_level
118 THEN
119 fnd_log.string(p_level, p_module, p_msg);
120 END IF;
121 END trace;
122
123 PROCEDURE user_log
124 (p_msg IN VARCHAR2) IS
125 BEGIN
126 fnd_file.put_line(fnd_file.log, p_msg);
127 END user_log;
128
129 --=============================================================================
130 -- *********** public procedures and functions **********
131 --=============================================================================
132
133 -- ----------------------------------------------------------------------------
134 -- Create third party merge accounting routine - called by SRS
135 -- ----------------------------------------------------------------------------
136 PROCEDURE third_party_merge
137 ( x_errbuf OUT NOCOPY VARCHAR2
138 , x_retcode OUT NOCOPY VARCHAR2
139 , x_event_ids OUT NOCOPY xla_third_party_merge_pub.t_event_ids
140 , x_request_id OUT NOCOPY INTEGER
141 , p_source_application_id IN INTEGER DEFAULT NULL
142 , p_application_id IN INTEGER
143 , p_ledger_id IN INTEGER DEFAULT NULL
144 , p_third_party_merge_date IN DATE
145 , p_third_party_type IN VARCHAR2
146 , p_original_third_party_id IN INTEGER
147 , p_original_site_id IN INTEGER DEFAULT NULL
148 , p_new_third_party_id IN INTEGER
149 , p_new_site_id IN INTEGER DEFAULT NULL
150 , p_type_of_third_party_merge IN VARCHAR2
151 , p_mapping_flag IN VARCHAR2
152 , p_execution_mode IN VARCHAR2
153 , p_accounting_mode IN VARCHAR2
154 , p_transfer_to_gl_flag IN VARCHAR2
155 , p_post_in_gl_flag IN VARCHAR2) IS
156
157 v_function VARCHAR2(240);
158 v_module VARCHAR2(240);
159 v_message VARCHAR2(2000);
160 v_dummy VARCHAR2(1);
161
162 v_application_name VARCHAR2(240);
163 v_valuation_method_flag VARCHAR2(1);
164 v_rollback_flag VARCHAR2(1);
165
166 CURSOR ledger_cur IS
167 SELECT DISTINCT opt.LEDGER_ID
168 FROM XLA_LEDGER_OPTIONS opt,
169 XLA_LEDGER_RELATIONSHIPS_V rs,
170 gl_ledgers gl
171 WHERE (p_ledger_id IS NULL OR opt.LEDGER_ID = p_ledger_id)
172 AND opt.APPLICATION_ID = p_application_id
173 AND opt.ENABLED_FLAG = 'Y'
174 AND rs.LEDGER_ID = opt.LEDGER_ID
175 AND ( rs.LEDGER_CATEGORY_CODE = 'PRIMARY'
176 OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY'
177 AND v_valuation_method_flag = 'Y'
178 AND opt.CAPTURE_EVENT_FLAG = 'Y'))
179 AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
180 AND rs.ledger_id = gl.ledger_id
181 AND gl.complete_flag = 'Y'
182 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL;
183
184 v_ledger_id NUMBER(15);
185 v_entity_id NUMBER(15);
186 v_merge_event_set_id NUMBER(15);
187 v_event_id NUMBER(15);
188 -- v_max_event_number NUMBER; commented for bug 9439643
189 v_mapping_flag VARCHAR2(1);
190 v_event_count NUMBER;
191 v_access_count NUMBER;
192 v_row_count NUMBER;
193
194 BEGIN
195
196 -- --------------------------
197 -- Initialize local variables
198 -- --------------------------
199 v_function := 'xla_third_party_merge.third_party_merge';
200 v_module := C_DEFAULT_MODULE||'.third_party_merge';
201 v_rollback_flag := 'N';
202 v_event_count := 0;
203 v_access_count := 0;
204
205 IF (C_LEVEL_PROCEDURE >= g_log_level)
206 THEN
207 -- Log the function entry
208 trace( p_msg => 'BEGIN - ' || v_function
209 , p_level => C_LEVEL_PROCEDURE
210 , p_module => v_module);
211 -- List the parameters and their passed values
212 trace( p_msg => 'p_source_application_id = ' || p_source_application_id
213 || ', p_applicaiton_id = ' || p_application_id
214 || ', p_ledger_id = ' || p_ledger_id
215 || ', p_third_party_merge_date = '
216 || p_third_party_merge_date
217 || ', p_third_party_type = ' || p_third_party_type
218 , p_level => C_LEVEL_PROCEDURE
219 , p_module => v_module);
220 trace( p_msg => 'p_original_third_party_id = '
221 || p_original_third_party_id
222 || ', p_original_site_id = ' || p_original_site_id
223 || ', p_new_third_party_id = ' || p_new_third_party_id
224 || ', p_new_site_id = ' || p_new_site_id
225 || ', p_type_of_third_party_merge = '
226 || p_type_of_third_party_merge
227 , p_level => C_LEVEL_PROCEDURE
228 , p_module => v_module);
229 trace( p_msg => 'p_mapping_flag = ' || p_mapping_flag
230 || ', p_execution_mode = ' || p_execution_mode
231 || ', p_accounting_mode = ' || p_accounting_mode
232 || ', p_transfer_to_gl_flag = '
233 || p_transfer_to_gl_flag
234 || ', p_post_in_gl_flag = ' || p_post_in_gl_flag
235 , p_level => C_LEVEL_PROCEDURE
236 , p_module => v_module);
237 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
238
239 -- -----------------------
240 -- Validate the parameters
241 -- -----------------------
242
243 -- Validate if the passed application is defined in XLA_SUBLEDGERS
244 BEGIN
245 SELECT f.APPLICATION_NAME, s.VALUATION_METHOD_FLAG
246 INTO v_application_name, v_valuation_method_flag
247 FROM XLA_SUBLEDGERS s, FND_APPLICATION_VL f
248 WHERE s.APPLICATION_ID = f.APPLICATION_ID
249 AND s.APPLICATION_ID = p_application_id;
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 v_message := xla_messages_pkg.get_message
253 ( p_appli_s_name => 'XLA'
254 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
255 , p_token_1 => 'PARAMETER_VALUE'
256 , p_value_1 => p_application_id
257 , p_token_2 => 'PARAMETER'
258 , p_value_2 => 'p_application_id');
259 RAISE ValidationError;
260 END;
261
262 -- Log values of v_valuation_method_flag, g_use_ledger_security,
263 -- g_access_set_id, and g_sec_access_set_id
264 trace( p_msg => 'v_valuation_method_flag = ' || v_valuation_method_flag
265 || ', g_use_ledger_security = ' || g_use_ledger_security
266 || ', g_access_set_id = ' || g_access_set_id
267 || ', g_sec_access_set_id = ' || g_sec_access_set_id
268 , p_level => C_LEVEL_STATEMENT
269 , p_module => v_module);
270
271 -- Validate the ledger if it is passed
272 IF (p_ledger_id IS NOT NULL)
273 THEN
274 BEGIN
275 SELECT 'X'
276 INTO v_dummy
277 FROM XLA_LEDGER_OPTIONS opt,
278 XLA_LEDGER_RELATIONSHIPS_V rs,
279 gl_ledgers gl
280 WHERE opt.LEDGER_ID = p_ledger_id
281 AND opt.APPLICATION_ID = p_application_id
282 AND opt.ENABLED_FLAG = 'Y'
283 AND rs.LEDGER_ID = opt.LEDGER_ID
284 AND ( rs.LEDGER_CATEGORY_CODE = 'PRIMARY'
285 OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY'
286 AND v_valuation_method_flag = 'Y'
287 AND opt.CAPTURE_EVENT_FLAG = 'Y'))
288 AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
289 AND rs.ledger_id = gl.ledger_id
290 AND gl.complete_flag = 'Y'
291 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL;
292 EXCEPTION
293 WHEN NO_DATA_FOUND THEN
294 v_message := xla_messages_pkg.get_message
295 ( p_appli_s_name => 'XLA'
296 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
297 , p_token_1 => 'PARAMETER_VALUE'
298 , p_value_1 => p_ledger_id
299 , p_token_2 => 'PARAMETER'
300 , p_value_2 => 'p_ledger_id');
301 RAISE ValidationError;
302 END;
303 END IF; -- IF (p_ledger_id IS NOT NULL)
304
305 -- Validate the third party merge date is passed
306 IF (p_third_party_merge_date IS NULL)
307 THEN
308 v_message := xla_messages_pkg.get_message
309 ( p_appli_s_name => 'XLA'
310 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
311 , p_token_1 => 'PARAMETER_VALUE'
312 , p_value_1 => ''''||p_third_party_merge_date||''''
313 , p_token_2 => 'PARAMETER'
314 , p_value_2 => 'p_third_party_merge_date');
315 RAISE ValidationError;
316 END IF; -- IF (p_third_party_merge_date IS NULL)
317
318 -- Validate the passed third party type
319 IF (p_third_party_type <> 'C' AND p_third_party_type <> 'S')
320 THEN
321 v_message := xla_messages_pkg.get_message
322 ( p_appli_s_name => 'XLA'
323 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
324 , p_token_1 => 'PARAMETER_VALUE'
325 , p_value_1 => ''''||p_third_party_type||''''
326 , p_token_2 => 'PARAMETER'
327 , p_value_2 => 'p_third_party_type');
328 RAISE ValidationError;
329 END IF; -- IF (p_third_party_type <> 'C' AND p_third_party_type <> 'S')
330
331 -- Validate the passed original third party
332 BEGIN
333 SELECT 'X'
334 INTO v_dummy
335 FROM XLA_THIRD_PARTIES_V
336 WHERE THIRD_PARTY_ID = p_original_third_party_id
337 AND THIRD_PARTY_TYPE = p_third_party_type;
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 v_message := xla_messages_pkg.get_message
341 ( p_appli_s_name => 'XLA'
342 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
343 , p_token_1 => 'PARAMETER_VALUE'
344 , p_value_1 => p_original_third_party_id
345 , p_token_2 => 'PARAMETER'
346 , p_value_2 => 'p_original_third_party_id');
347 RAISE ValidationError;
348 END;
349
350 -- Validate the passed new third party
351 BEGIN
352 SELECT 'X'
353 INTO v_dummy
354 FROM XLA_THIRD_PARTIES_V
355 WHERE THIRD_PARTY_ID = p_new_third_party_id
356 AND THIRD_PARTY_TYPE = p_third_party_type;
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 v_message := xla_messages_pkg.get_message
360 ( p_appli_s_name => 'XLA'
361 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
362 , p_token_1 => 'PARAMETER_VALUE'
363 , p_value_1 => p_new_third_party_id
364 , p_token_2 => 'PARAMETER'
365 , p_value_2 => 'p_new_third_party_id');
366 RAISE ValidationError;
367 END;
368
369 IF (p_original_site_id IS NOT NULL)
370 THEN
371 -- Validate the new site is passed if the original site is passed
372 IF (p_new_site_id IS NULL)
373 THEN
374 v_message := xla_messages_pkg.get_message
375 ( p_appli_s_name => 'XLA'
376 , p_msg_name => 'XLA_MERGE_SITE_ERR');
377 RAISE ValidationError;
378 END IF; -- IF (p_new_site_id IS NULL)
379
380 -- Validate the passed original third party site
381 BEGIN
382 SELECT 'X'
383 INTO v_dummy
384 FROM XLA_THIRD_PARTY_SITES_V
385 WHERE THIRD_PARTY_ID = p_original_third_party_id
386 AND THIRD_PARTY_SITE_ID = p_original_site_id
387 AND THIRD_PARTY_TYPE = p_third_party_type
388 AND ROWNUM = 1; -- May return multiple sites (e.g. different ship tos)
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 v_message := xla_messages_pkg.get_message
392 ( p_appli_s_name => 'XLA'
393 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
394 , p_token_1 => 'PARAMETER_VALUE'
395 , p_value_1 => p_original_site_id
396 , p_token_2 => 'PARAMETER'
397 , p_value_2 => 'p_original_site_id');
398 RAISE ValidationError;
399 END;
400
401 -- Validate the passed new third party site
402 BEGIN
403 SELECT 'X'
404 INTO v_dummy
405 FROM XLA_THIRD_PARTY_SITES_V
406 WHERE THIRD_PARTY_ID = p_new_third_party_id
407 AND THIRD_PARTY_SITE_ID = p_new_site_id
408 AND THIRD_PARTY_TYPE = p_third_party_type
409 AND ROWNUM = 1; -- May return multiple sites (e.g. different ship tos)
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 v_message := xla_messages_pkg.get_message
413 ( p_appli_s_name => 'XLA'
414 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
415 , p_token_1 => 'PARAMETER_VALUE'
416 , p_value_1 => p_new_site_id
417 , p_token_2 => 'PARAMETER'
418 , p_value_2 => 'p_new_site_id');
419 RAISE ValidationError;
420 END;
421
422 END IF; -- IF (p_original_site_id IS NOT NULL)
423
424 -- Validate the passed third party merge type
425 IF (p_type_of_third_party_merge <> 'FULL'
426 AND p_type_of_third_party_merge <> 'PARTIAL')
427 THEN
428 v_message := xla_messages_pkg.get_message
429 ( p_appli_s_name => 'XLA'
430 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
431 , p_token_1 => 'PARAMETER_VALUE'
432 , p_value_1 => ''''||p_type_of_third_party_merge||''''
433 , p_token_2 => 'PARAMETER'
434 , p_value_2 => 'p_type_of_third_party_merge');
435 RAISE ValidationError;
436 END IF; -- IF (p_type_of_third_party_merge <> 'FULL' AND ...
437
438 IF (p_mapping_flag = 'Y')
439 THEN
440 -- Validate only one segment code is provided per application/ledger/COA
441 BEGIN
442 SELECT 'Y'
443 INTO v_dummy
444 FROM XLA_MERGE_SEG_MAPS_GT
445 HAVING COUNT(DISTINCT SEGMENT_CODE) > 1
446 GROUP BY APPLICATION_ID, LEDGER_ID, CHART_OF_ACCOUNTS_ID;
447 EXCEPTION
448 WHEN NO_DATA_FOUND THEN
449 v_dummy := 'N';
450 END;
451
452 IF (v_dummy = 'Y')
453 THEN
454 -- The segment code is not unqiue per applicaiton/ledger
455 v_message := xla_messages_pkg.get_message
456 ( p_appli_s_name => 'XLA'
457 , p_msg_name => 'XLA_MERGE_MAPPING_ERR');
458 RAISE ValidationError;
459 END IF; -- IF (v_dummy = 'Y')
460
461 END IF; -- IF (p_mapping_flag = 'Y')
462
463 -- Validate the passed execution mode
464 IF (p_execution_mode <> 'ASYNC_NOREQ' AND p_execution_mode <> 'ASYNC_REQ'
465 AND p_execution_mode <> 'SYNC')
466 THEN
467 v_message := xla_messages_pkg.get_message
468 ( p_appli_s_name => 'XLA'
469 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
470 , p_token_1 => 'PARAMETER_VALUE'
471 , p_value_1 => ''''||p_execution_mode||''''
472 , p_token_2 => 'PARAMETER'
473 , p_value_2 => 'p_execution_mode');
474 RAISE ValidationError;
475 END IF; -- IF (p_execution_mode <> 'ASYNC_NOREQ' AND ...
476
477 -- Record the save point before creating events
478 SAVEPOINT BeforeLedgerCur;
479 -- Set v_rollback_flag to 'Y' i.e. indicating rolllback is needed
480 v_rollback_flag := 'Y';
481
482 -- ----------------------------------------------------------
483 -- Create third party merge event for each selected ledger(s)
484 -- ----------------------------------------------------------
485
486 -- Loop for each ledger to create third party merge event
487 OPEN ledger_cur;
488 LOOP
489 FETCH ledger_cur INTO v_ledger_id;
490 EXIT WHEN ledger_cur%NOTFOUND;
491
492 -- Log the value of v_ledger_id
493 trace( p_msg => 'v_ledger_id = ' || v_ledger_id
494 , p_level => C_LEVEL_STATEMENT
495 , p_module => v_module);
496
497 -- Find the Third Party Merge transaction entity for the passed
498 -- application/selected ledger
499 trace( p_msg => 'Find the Third Party Merge transaction entity'
500 , p_level => C_LEVEL_STATEMENT
501 , p_module => v_module);
502 v_dummy := 'N';
503 BEGIN
504 SELECT ENTITY_ID
505 INTO v_entity_id
506 FROM XLA_TRANSACTION_ENTITIES
507 WHERE APPLICATION_ID = p_application_id
508 AND LEDGER_ID = v_ledger_id
509 AND ENTITY_CODE = 'THIRD_PARTY_MERGE'
510 AND ROWNUM = 1;--added debug 9593919
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN
513 -- Set the dummy flag to 'Y' to indicate creating a new entity
514 v_dummy := 'Y';
515 END;
516
517 IF (v_dummy = 'Y')
518 THEN
519 -- We cannot find the entity for the passed application/selected ledger,
520 -- so create one.
521 trace( p_msg => 'Create a Third Party Merge transaction entity'
522 , p_level => C_LEVEL_STATEMENT
523 , p_module => v_module);
524 INSERT INTO XLA_TRANSACTION_ENTITIES
525 ( ENTITY_ID, APPLICATION_ID, LEDGER_ID, ENTITY_CODE,
526 SOURCE_APPLICATION_ID, CREATION_DATE, CREATED_BY,
527 LAST_UPDATE_DATE, LAST_UPDATED_BY,
528 LAST_UPDATE_LOGIN)
529 VALUES
530 (XLA_TRANSACTION_ENTITIES_S.nextval,
531 p_application_id,
532 v_ledger_id,
533 'THIRD_PARTY_MERGE',
534 NVL(p_source_application_id, p_application_id),
535 sysdate,
536 XLA_ENVIRONMENT_PKG.g_usr_id,
537 sysdate,
538 XLA_ENVIRONMENT_PKG.g_usr_id,
539 XLA_ENVIRONMENT_PKG.g_login_id)
540 RETURNING ENTITY_ID INTO v_entity_id;
541 -- It's a new transaciton entity, so the maximum event number must be 0
542 -- v_max_event_number := 0; commented for bug 9439643
543
544 ELSE
545 -- We do find the entity for the passed application/selected ledger,
546 -- so find its maximum event number
547 /* commented bug 9439643 fetch event number from event id sequence
548 trace( p_msg => 'Find the maximum event number'
549 , p_level => C_LEVEL_STATEMENT
550 , p_module => v_module);
551
552 SELECT max(EVENT_NUMBER)
553 INTO v_max_event_number
554 FROM XLA_EVENTS
555 WHERE ENTITY_ID = v_entity_id;
556
557 IF(v_max_event_number is null) THEN
558 v_max_event_number :=0;
559 END IF; */
560 null;
561
562 END IF; -- IF (v_dummy = 'Y')
563
564 -- Log the values of v_entity_id
565 trace( p_msg => 'v_entity_id = ' || v_entity_id
566 , p_level => C_LEVEL_STATEMENT
567 , p_module => v_module);
568
569 v_mapping_flag := 'N';
570 IF (p_mapping_flag = 'Y')
571 THEN
572 -- Check whether any mapping rows are inserted for the passed
573 -- application/selected ledger. Its associated secondary ledgers will be
574 -- checked if the valuation method flag is 'N' for the pased application.
575 trace( p_msg => 'Check the mapping rows'
576 , p_level => C_LEVEL_STATEMENT
577 , p_module => v_module);
578 BEGIN
579 SELECT 'Y'
580 INTO v_mapping_flag
581 FROM DUAL
582 WHERE EXISTS
583 (SELECT 'X'
584 FROM XLA_LEDGER_RELATIONSHIPS_V rs,
585 XLA_MERGE_SEG_MAPS_GT gt,
586 gl_ledgers gld
587 WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
588 AND rs.ledger_id = gld.ledger_id
589 AND gld.complete_flag = 'Y'
590 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
591 AND DECODE(v_valuation_method_flag
592 , 'N', rs.PRIMARY_LEDGER_ID, rs.LEDGER_ID) = v_ledger_id
593 AND rs.LEDGER_CATEGORY_CODE IN ('PRIMARY', 'SECONDARY')
594 AND gt.APPLICATION_ID = p_application_id
595 AND gt.LEDGER_ID = rs.LEDGER_ID);
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 v_mapping_flag := 'N';
599 END;
600
601 IF(v_mapping_flag = 'N') THEN
602 v_message := xla_messages_pkg.get_message
603 ( p_appli_s_name => 'XLA'
604 , p_msg_name => 'XLA_MERGE_NO_MAPPING');
605 RAISE ValidationError;
606 END IF;
607
608 -- Log the values of v_mapping_flag
609 trace( p_msg => 'v_mapping_flag = ' || v_mapping_flag
610 , p_level => C_LEVEL_STATEMENT
611 , p_module => v_module);
612
613 END IF; -- IF (p_mapping_flag = 'Y')
614
615 -- Create the Third Party Merge event for the passed application/selected
616 -- ledger
617 trace( p_msg => 'Create the Third Party Merge event'
618 , p_level => C_LEVEL_STATEMENT
619 , p_module => v_module);
620 INSERT INTO XLA_EVENTS
621 ( EVENT_ID, APPLICATION_ID, ENTITY_ID, EVENT_NUMBER,
622 EVENT_TYPE_CODE, EVENT_DATE, EVENT_STATUS_CODE,
623 PROCESS_STATUS_CODE, CREATION_DATE, CREATED_BY,
624 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
625 PROGRAM_UPDATE_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID,
626 REQUEST_ID, REFERENCE_NUM_1, REFERENCE_NUM_2, REFERENCE_NUM_3,
627 REFERENCE_NUM_4, REFERENCE_CHAR_1, REFERENCE_CHAR_2,
628 MERGE_EVENT_SET_ID, ON_HOLD_FLAG,
629 TRANSACTION_DATE)
630 VALUES
631 ( XLA_EVENTS_S.nextval,
632 p_application_id,
633 v_entity_id,
634 XLA_EVENTS_S.nextval , -- v_max_event_number + 1 commented for bug 9439643
635 p_type_of_third_party_merge||'_MERGE',
636 p_third_party_merge_date,
637 'U',
638 'U',
639 sysdate,
640 XLA_ENVIRONMENT_PKG.g_usr_id,
641 sysdate,
642 XLA_ENVIRONMENT_PKG.g_usr_id,
643 XLA_ENVIRONMENT_PKG.g_login_id,
644 sysdate,
645 XLA_ENVIRONMENT_PKG.g_prog_appl_id,
646 XLA_ENVIRONMENT_PKG.g_prog_id,
647 XLA_ENVIRONMENT_PKG.g_req_id,
648 p_original_third_party_id,
649 p_original_site_id,
650 p_new_third_party_id,
651 p_new_site_id,
652 p_third_party_type,
653 v_mapping_flag,
654 DECODE(v_event_count
655 , 0, NULL, TO_CHAR(v_merge_event_set_id)),
656 'N',
657 p_third_party_merge_date)
658 RETURNING EVENT_ID INTO v_event_id;
659
660 -- Log the values of v_event_id
661 trace( p_msg => 'v_event_id = ' || v_event_id
662 , p_level => C_LEVEL_STATEMENT
663 , p_module => v_module);
664
665
666
667 IF (v_mapping_flag = 'Y')
668 THEN
669 -- Populate the table XLA_MERGE_SEG_MAPS based on XLA_MERGE_SEG_MAPS_GT
670 -- for this current event/associated ledgers
671 trace( p_msg => 'Insert mapping rows'
672 , p_level => C_LEVEL_STATEMENT
673 , p_module => v_module);
674 INSERT INTO XLA_MERGE_SEG_MAPS
675 ( APPLICATION_ID, LEDGER_ID, SEGMENT_CODE, FROM_VALUE,
676 TO_VALUE, EVENT_ID, CHART_OF_ACCOUNTS_ID, CREATION_DATE,
677 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
678 LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE,
679 PROGRAM_APPLICATION_ID)
680 SELECT gt.APPLICATION_ID,
681 gt.LEDGER_ID,
682 gt.SEGMENT_CODE,
683 gt.FROM_VALUE,
684 gt.TO_VALUE,
685 v_event_id,
686 gt.CHART_OF_ACCOUNTS_ID,
687 sysdate,
688 XLA_ENVIRONMENT_PKG.g_usr_id,
689 sysdate,
690 XLA_ENVIRONMENT_PKG.g_usr_id,
691 XLA_ENVIRONMENT_PKG.g_login_id,
692 sysdate,
693 XLA_ENVIRONMENT_PKG.g_prog_appl_id
694 FROM XLA_LEDGER_RELATIONSHIPS_V rs,
695 XLA_MERGE_SEG_MAPS_GT gt,
696 GL_LEDGERS gl
697 WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
698 AND rs.ledger_id = gl.ledger_id
699 AND gl.complete_flag = 'Y'
700 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
701 AND DECODE(v_valuation_method_flag
702 , 'N', rs.PRIMARY_LEDGER_ID
703 , rs.LEDGER_ID) = v_ledger_id
704 AND rs.LEDGER_CATEGORY_CODE IN ('PRIMARY', 'SECONDARY')
705 AND gt.APPLICATION_ID = p_application_id
706 AND gt.LEDGER_ID = rs.LEDGER_ID;
707
708 -- Log the number of rows inserted
709 trace( p_msg => 'Insert ' || SQL%ROWCOUNT
710 || ' rows into XLA_MERGE_SEG_MAPS'
711 , p_level => C_LEVEL_STATEMENT
712 , p_module => v_module);
713
714 END IF; -- IF (v_mapping_flag = 'Y')
715
716 IF (p_type_of_third_party_merge = 'PARTIAL')
717 THEN
718 -- Populate the table XLA_PARTIAL_MERGE_TXNS based on XLA_EVENTS_GT for
719 -- this current event
720 trace( p_msg => 'Insert partial transactions'
721 , p_level => C_LEVEL_STATEMENT
722 , p_module => v_module);
723 INSERT INTO XLA_PARTIAL_MERGE_TXNS
724 ( APPLICATION_ID, MERGE_EVENT_ID, ENTITY_ID, ENTITY_CODE,
725 SOURCE_ID_INT_1, SOURCE_ID_INT_2, SOURCE_ID_INT_3,
726 SOURCE_ID_INT_4, SOURCE_ID_CHAR_1, SOURCE_ID_CHAR_2,
727 SOURCE_ID_CHAR_3, SOURCE_ID_CHAR_4, VALUATION_METHOD,
728 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
729 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE,
730 PROGRAM_APPLICATION_ID, PROGRAM_ID, REQUEST_ID)
731 SELECT DISTINCT
732 gt.APPLICATION_ID,
733 v_event_id,
734 ent.ENTITY_ID,
735 gt.ENTITY_CODE,
736 gt.SOURCE_ID_INT_1,
737 gt.SOURCE_ID_INT_2,
738 gt.SOURCE_ID_INT_3,
739 gt.SOURCE_ID_INT_4,
740 gt.SOURCE_ID_CHAR_1,
741 gt.SOURCE_ID_CHAR_2,
742 gt.SOURCE_ID_CHAR_3,
743 gt.SOURCE_ID_CHAR_4,
744 gt.VALUATION_METHOD,
745 sysdate,
746 XLA_ENVIRONMENT_PKG.g_usr_id,
747 sysdate,
748 XLA_ENVIRONMENT_PKG.g_usr_id,
749 XLA_ENVIRONMENT_PKG.g_login_id,
750 sysdate,
751 XLA_ENVIRONMENT_PKG.g_prog_appl_id,
752 XLA_ENVIRONMENT_PKG.g_prog_id,
753 XLA_ENVIRONMENT_PKG.g_req_id
754 FROM XLA_EVENTS_GT gt,
755 XLA_TRANSACTION_ENTITIES ent
756 WHERE gt.APPLICATION_ID = p_application_id
757 AND gt.LEDGER_ID = v_ledger_id
758 AND ent.APPLICATION_ID = gt.APPLICATION_ID
759 AND ent.LEDGER_ID = gt.LEDGER_ID
760 AND ent.ENTITY_CODE = gt.ENTITY_CODE
761 AND NVL(ent.VALUATION_METHOD,' ') = NVL(gt.VALUATION_METHOD,' ')
762 AND NVL(ent.SOURCE_ID_INT_1,-99) = NVL(gt.SOURCE_ID_INT_1,-99)
763 AND NVL(ent.SOURCE_ID_INT_2,-99) = NVL(gt.SOURCE_ID_INT_2,-99)
764 AND NVL(ent.SOURCE_ID_INT_3,-99) = NVL(gt.SOURCE_ID_INT_3,-99)
765 AND NVL(ent.SOURCE_ID_INT_4,-99) = NVL(gt.SOURCE_ID_INT_4,-99)
766 AND NVL(ent.SOURCE_ID_CHAR_1,' ') = NVL(gt.SOURCE_ID_CHAR_1,' ')
767 AND NVL(ent.SOURCE_ID_CHAR_2,' ') = NVL(gt.SOURCE_ID_CHAR_2,' ')
768 AND NVL(ent.SOURCE_ID_CHAR_3,' ') = NVL(gt.SOURCE_ID_CHAR_3,' ')
769 AND NVL(ent.SOURCE_ID_CHAR_4,' ') = NVL(gt.SOURCE_ID_CHAR_4,' ');
770
771 -- Log the number of rows inserted
772 v_row_count := SQL%ROWCOUNT;
773 trace( p_msg => 'Insert ' || to_char(v_row_count)
774 || ' rows into XLA_PARTIAL_MERGE_TXNS'
775 , p_level => C_LEVEL_STATEMENT
776 , p_module => v_module);
777 IF(v_row_count = 0) THEN
778 v_message := xla_messages_pkg.get_message
779 ( p_appli_s_name => 'XLA'
780 , p_msg_name => 'XLA_MERGE_NO_TRX_SET');
781 RAISE ValidationError;
782 END IF;
783
784 END IF; -- IF (p_type_of_third_party_merge = 'PARTIAL')
785
786 IF (p_execution_mode <> 'ASYNC_NOREQ')
787 THEN
788 IF (g_use_ledger_security = 'Y')
789 THEN
790 -- Check if the user has the access to create accounting for this event
791 trace( p_msg => 'Check user access'
792 , p_level => C_LEVEL_STATEMENT
793 , p_module => v_module);
794 BEGIN
795 SELECT 'Y'
796 INTO v_dummy
797 FROM DUAL
798 WHERE EXISTS
799 (SELECT 'Ledger without access'
800 FROM XLA_LEDGER_OPTIONS opt,
801 XLA_LEDGER_RELATIONSHIPS_V rs,
802 GL_LEDGERS gld
803 WHERE opt.APPLICATION_ID = p_application_id
804 AND opt.ENABLED_FLAG = 'Y'
805 AND opt.MERGE_ACCT_OPTION_CODE <> 'NONE'
806 AND DECODE(rs.LEDGER_CATEGORY_CODE
807 , 'ALC', rs.PRIMARY_LEDGER_ID
808 , rs.LEDGER_ID) = opt.LEDGER_ID
809 AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
810 AND rs.ledger_id = gld.ledger_id
811 AND gld.complete_flag = 'Y'
812 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
813 AND DECODE(v_valuation_method_flag
814 , 'N', rs.PRIMARY_LEDGER_ID
815 , DECODE(rs.LEDGER_CATEGORY_CODE
816 , 'ALC', rs.PRIMARY_LEDGER_ID
817 , rs.LEDGER_ID)) = v_ledger_id
818 AND rs.LEDGER_ID NOT IN
819 (SELECT asa.LEDGER_ID
820 FROM GL_ACCESS_SET_ASSIGNMENTS asa
821 WHERE asa.ACCESS_SET_ID
822 IN (g_access_set_id,
823 g_sec_access_set_id)));
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 -- Increment access count by 1
827 v_access_count := v_access_count + 1;
828 END;
829
830 ELSE
831 -- Increment access count by 1
832 v_access_count := v_access_count + 1;
833
834 END IF; -- IF (g_use_ledger_security = 'Y')
835
836 END IF; -- IF (p_execution_mode <> 'ASYNC_NOREQ')
837
838 -- Increment event count by 1
839 v_event_count := v_event_count + 1;
840
841 -- Store the just created event ID to event ID list
842 trace( p_msg => 'Store just created event ID to x_event_ids'
843 , p_level => C_LEVEL_STATEMENT
844 , p_module => v_module);
845 x_event_ids(v_event_count) := v_event_id;
846
847 -- Check if this is the first event created
848 IF (v_event_count = 1)
849 THEN
850 -- Set the merge event set ID as the first event ID
851 v_merge_event_set_id := v_event_id;
852 -- Log the value of v_ledger_id
853 trace( p_msg => 'v_merge_event_set_id = ' || v_merge_event_set_id
854 , p_level => C_LEVEL_STATEMENT
855 , p_module => v_module);
856
857 -- Populate the merge event set ID to the first event
858 trace( p_msg => 'Populate the merge event set ID to the first event'
859 , p_level => C_LEVEL_STATEMENT
860 , p_module => v_module);
861 UPDATE XLA_EVENTS
862 SET MERGE_EVENT_SET_ID = TO_CHAR(v_merge_event_set_id)
863 WHERE EVENT_ID = v_merge_event_set_id;
864
865 END IF; -- IF (v_event_count = 1)
866
867 END LOOP; -- End of ledger_cur loop
868
869 -- Log the values of v_access_count and v_event_count
870 trace( p_msg => 'v_access_count = ' || v_access_count
871 || ', v_event_count = ' || v_event_count
872 , p_level => C_LEVEL_STATEMENT
873 , p_module => v_module);
874
875 IF (v_event_count = 0)
876 THEN
877 -- No events are created
878 v_message := xla_messages_pkg.get_message
879 ( p_appli_s_name => 'XLA'
880 , p_msg_name => 'XLA_MERGE_NO_LG_ERR'
881 , p_token_1 => 'SUBLEDGER_APPLICATION_NAME'
882 , p_value_1 => v_application_name);
883 RAISE ValidationError;
884 END IF; -- IF (v_event_count = 0)
885
886 -- Check if we need to create accounting for just created events
887 IF (v_access_count > 0)
888 THEN
889
890 -- Set v_event_id to NULL if there are more than 1 events created
891 -- 14773226 - Added a bew event check to send event_id not null and merge_event_set_id as null, when only 1 event is created
892 IF (v_event_count = 1) THEN
893 v_merge_event_set_id := NULL;
894 ELSIF (v_merge_event_set_id IS NOT NULL)
895 THEN
896 v_event_id := NULL;
897 END IF; -- IF (v_merge_event_set_id IS NOT NULL)
898
899 IF (p_execution_mode = 'SYNC')
900 THEN
901 -- If the execution mode is 'SYNC', call API to create accounting for just
902 -- create third party merge events
903 trace( p_msg => 'Call xla_third_party_merge.create_accounting()'
904 , p_level => C_LEVEL_STATEMENT
905 , p_module => v_module);
906 xla_third_party_merge.create_accounting
907 ( x_errbuf => x_errbuf
908 , x_retcode => x_retcode
909 , p_application_id => p_application_id
910 , p_event_id => v_event_id
911 , p_accounting_mode => p_accounting_mode
912 , p_transfer_to_gl_flag => p_transfer_to_gl_flag
913 , p_post_in_gl_flag => p_post_in_gl_flag
914 , p_merge_event_set_id => v_merge_event_set_id
915 , p_srs_flag => 'N');
916
917 -- If the return code is 'E' or 'U', raise AccountingError exception
918 IF (x_retcode = xla_third_party_merge_pub.G_RET_STS_ERROR OR
919 x_retcode = xla_third_party_merge_pub.G_RET_STS_UNEXP_ERROR)
920 THEN
921 RAISE AccountingError;
922 END IF; -- IF (x_retcode = xla_third_party_merge_pub.G_RET_STS_ERROR ...
923
924 ELSIF (p_execution_mode = 'ASYNC_REQ')
925 THEN
926 -- If the execution mode is 'ASYNC_REQ', call API to submit the concurrent
927 -- program, Create Third Party Merge Accounting
928 trace( p_msg => 'Call fnd_request.submit_request()'
929 , p_level => C_LEVEL_STATEMENT
930 , p_module => v_module);
931 x_request_id := fnd_request.submit_request
932 ( 'XLA'
933 , 'XLAMERGEACCT'
934 , ''
935 , ''
936 , FALSE
937 , p_application_id
938 , v_event_id
939 , p_accounting_mode
940 , p_transfer_to_gl_flag
941 , p_post_in_gl_flag
942 , v_merge_event_set_id);
943 END IF; -- IF (p_execution_mode = 'SYNC')
944
945 END IF; -- IF (v_access_count > 0)
946
947 -- Set the return code to 'S'
948 x_retcode := xla_third_party_merge_pub.G_RET_STS_SUCCESS;
949
950 -- Log the out parameters, their returned values and the function exit
951 IF (C_LEVEL_PROCEDURE >= g_log_level)
952 THEN
953 trace( p_msg => 'x_retcode = ' || x_retcode
954 || ', x_errbuf = ' || x_errbuf
955 , p_level => C_LEVEL_PROCEDURE
956 , p_module => v_module);
957 trace( p_msg => 'x_request_id = ' || x_request_id
958 || ', x_event_ids.COUNT = ' || x_event_ids.COUNT
959 , p_level => C_LEVEL_PROCEDURE
960 , p_module => v_module);
961 -- Log the function exit
962 trace( p_msg => 'END - ' || v_function
963 , p_level => C_LEVEL_PROCEDURE
964 , p_module => v_module);
965
966 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
967
968 EXCEPTION
969 WHEN ValidationError THEN
970 -- Log the error message
971 trace( p_msg => v_message
972 , p_level => C_LEVEL_ERROR
973 , p_module => v_module);
974 -- Set the out parameters
975 x_errbuf := xla_messages_pkg.get_message
976 ( p_appli_s_name => 'XLA'
977 , p_msg_name => 'XLA_MERGE_FATAL_ERR'
978 , p_token_1 => 'FUNCTION'
979 , p_value_1 => v_function
980 , p_token_2 => 'ERROR'
981 , p_value_2 => v_message);
982 x_retcode := xla_third_party_merge_pub.G_RET_STS_ERROR;
983 -- Log the out parameters, their returned values and the function exit
984 IF (C_LEVEL_PROCEDURE >= g_log_level)
985 THEN
986 trace( p_msg => 'x_retcode = ' || x_retcode
987 || ', x_errbuf = ' || x_errbuf
988 , p_level => C_LEVEL_PROCEDURE
989 , p_module => v_module);
990 trace( p_msg => 'x_request_id = ' || x_request_id
991 || ', x_event_ids.COUNT = ' || x_event_ids.COUNT
992 , p_level => C_LEVEL_PROCEDURE
993 , p_module => v_module);
994 -- Log the function exit
995 trace( p_msg => 'EXIT with ERROR - ' || v_function
996 , p_level => C_LEVEL_PROCEDURE
997 , p_module => v_module);
998 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
999
1000 WHEN AccountingError THEN
1001 -- Rollback to the save point before creating events
1002 ROLLBACK TO BeforeLedgerCur;
1003 -- Log the out parameters, their returned values and the function exit
1004 IF (C_LEVEL_PROCEDURE >= g_log_level)
1005 THEN
1006 trace( p_msg => 'x_retcode = ' || x_retcode
1007 || ', x_errbuf = ' || x_errbuf
1008 , p_level => C_LEVEL_PROCEDURE
1009 , p_module => v_module);
1010 trace( p_msg => 'x_request_id = ' || x_request_id
1011 || ', x_event_ids.COUNT = ' || x_event_ids.COUNT
1012 , p_level => C_LEVEL_PROCEDURE
1013 , p_module => v_module);
1014 -- Log the function exit
1015 trace( p_msg => 'EXIT with ERROR - ' || v_function
1016 , p_level => C_LEVEL_PROCEDURE
1017 , p_module => v_module);
1018 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1019
1020 WHEN OTHERS THEN
1021 -- Rollback to the save point before creating events if necessary
1022 IF (v_rollback_flag = 'Y')
1023 THEN
1024 ROLLBACK to BeforeLedgerCur;
1025 END IF;
1026 -- Get and log the SQL error message
1027 v_message := SQLERRM;
1028 trace( p_msg => v_message
1029 , p_level => C_LEVEL_UNEXPECTED
1030 , p_module => v_module);
1031 -- Set the out parameters
1032 x_errbuf := xla_messages_pkg.get_message
1033 ( p_appli_s_name => 'XLA'
1034 , p_msg_name => 'XLA_MERGE_FATAL_ERR'
1035 , p_token_1 => 'FUNCTION'
1036 , p_value_1 => v_function
1037 , p_token_2 => 'ERROR'
1038 , p_value_2 => v_message);
1039 x_retcode := xla_third_party_merge_pub.G_RET_STS_UNEXP_ERROR;
1040 -- Log the out parameters, their returned values and the function exit
1041 IF (C_LEVEL_PROCEDURE >= g_log_level)
1042 THEN
1043 trace( p_msg => 'x_retcode = ' || x_retcode
1044 || ', x_errbuf = ' || x_errbuf
1045 , p_level => C_LEVEL_PROCEDURE
1046 , p_module => v_module);
1047 trace( p_msg => 'x_request_id = ' || x_request_id
1048 || ', x_event_ids.COUNT = ' || x_event_ids.COUNT
1049 , p_level => C_LEVEL_PROCEDURE
1050 , p_module => v_module);
1051 -- Log the function exit
1052 trace( p_msg => 'EXIT with ERROR - ' || v_function
1053 , p_level => C_LEVEL_PROCEDURE
1054 , p_module => v_module);
1055 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1056
1057 END third_party_merge;
1058
1059 PROCEDURE get_line_number(
1060 p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
1061 ,p_array_rounding_rule_code IN t_varchar30_array
1062 ,p_array_mau IN t_number_array
1063 ) IS
1064 --
1065 l_ae_line_num NUMBER;
1066 l_ae_header_id NUMBER;
1067 l_log_module VARCHAR2(240);
1068 --
1069 l_array_rowid t_rowid_array;
1070 l_array_rowid1 t_rowid_array;
1071 l_array_ae_line_num t_number_array;
1072 l_array_doc_rounding_amt1 t_number_array;
1073 l_array_rounding_entd_amt1 t_number_array;
1074 l_array_ledger_id xla_accounting_cache_pkg.t_array_ledger_id;
1075 l_array_header_id t_number_array;
1076
1077 l_rounding_rule_code VARCHAR2(30);
1078 l_array_rounding_rule_code t_varchar30_array;
1079 l_array_mau t_number_array;
1080 l_array_rounding_class_code t_varchar30_array;
1081 l_array_doc_rounding_level t_varchar30_array;
1082 l_array_unrounded_amount t_number_array;
1083 l_array_unrounded_entd_amount t_number_array;
1084 l_array_entd_mau t_number_array;
1085
1086 l_curr_rounding_class_code VARCHAR2(30);
1087 l_curr_doc_rounding_level VARCHAR2(30);
1088 l_curr_doc_rounding_amount NUMBER;
1089 l_curr_entd_rounding_amount NUMBER;
1090 l_curr_total_unrounded NUMBER;
1091 l_curr_total_rounded NUMBER;
1092 l_curr_entd_total_unrounded NUMBER;
1093 l_curr_entd_total_rounded NUMBER;
1094 l_curr_max_rowid ROWID;
1095 l_curr_max_amount NUMBER;
1096 l_curr_ledger_id NUMBER;
1097 l_curr_header_id NUMBER;
1098 l_curr_mau NUMBER;
1099 l_curr_entd_mau NUMBER;
1100 l_curr_rounding_rule_code VARCHAR2(30);
1101 j NUMBER;
1102 l_temp NUMBER;
1103
1104
1105 l_count NUMBER :=1;
1106
1107 CURSOR csr_set_linenum is
1108 select rowid, dense_rank() over (partition by ae_header_id
1109 order by line_hash_num, merge_index) ae_line_num
1110 from xla_ae_lines_gt;
1111
1112 CURSOR csr_rounding_lines is
1113 SELECT max(xalg.rowid)
1114 ,rounding_class_code
1115 ,document_rounding_level
1116 ,NVL(SUM(unrounded_accounted_cr), 0)
1117 - NVL(SUM(unrounded_accounted_dr), 0) unrounded_amount
1118 ,ledger_id
1119 ,ae_header_id
1120 ,NVL(SUM(unrounded_entered_cr), 0)
1121 - NVL(SUM(unrounded_entered_dr), 0) unrounded_entered_amount
1122 ,entered_currency_mau
1123 FROM xla_ae_lines_gt xalg
1124 WHERE temp_line_num <> 0
1125 GROUP BY ledger_id, event_id, ae_header_id,
1126 rounding_class_code, document_rounding_level, ae_line_num
1127 ,entered_currency_mau
1128 HAVING document_rounding_level is not null
1129 AND rounding_class_code is not null
1130 ORDER BY document_rounding_level, rounding_class_code;
1131
1132
1133 BEGIN
1134 IF g_log_enabled THEN
1135 l_log_module := C_DEFAULT_MODULE||'.get_line_number';
1136 END IF;
1137 --
1138 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1139
1140 trace
1141 (p_msg => 'BEGIN of get_line_number'
1142 ,p_level => C_LEVEL_PROCEDURE
1143 ,p_module => l_log_module);
1144 END IF;
1145
1146 For i in 1..p_array_ledger_id.COUNT LOOP
1147 l_array_mau(p_array_ledger_id(i)) := p_array_mau(i);
1148 l_array_rounding_rule_code(p_array_ledger_id(i)) := p_array_rounding_rule_code(i);
1149 END LOOP;
1150
1151 BEGIN
1152
1153 UPDATE xla_ae_lines_gt ael
1154 set line_hash_num =
1155 DBMS_UTILITY.GET_HASH_VALUE
1156 (ae_header_id
1157 ||accounting_class_code
1158 ||rounding_class_code
1159 ||document_rounding_level
1160 ||currency_code
1161 ||currency_conversion_type
1162 ||currency_conversion_date
1163 ||currency_conversion_rate
1164 ||party_id
1165 ||party_site_id
1166 ||party_type_code
1167 ||code_combination_id
1168 ||description
1169 ||jgzz_recon_ref
1170 ||ussgl_transaction_code
1171 ||merge_duplicate_code
1172 ||line_definition_owner_code --added for 12955823 as they are used in Lines Insert Group
1173 ||line_definition_code --added for 12955823 as they are used in Lines Insert Group
1174 ||business_class_code --added for 12955823 as they are used in Lines Insert Group
1175 ||mpa_accrual_entry_flag --added for 12955823 as they are used in Lines Insert Group
1176 ||encumbrance_type_id,
1177 1,
1178 1073741824)
1179 ,merge_index = CASE merge_duplicate_code
1180 WHEN 'A' THEN
1181 CASE switch_side_flag
1182 WHEN 'Y' THEN -1
1183 ELSE
1184 CASE
1185 WHEN accounted_cr is null THEN -2
1186 ELSE -3
1187 END
1188 END
1189 WHEN 'W' THEN
1190 CASE
1191 WHEN accounted_cr is null THEN -2
1192 ELSE -3
1193 END
1194 WHEN 'N' THEN temp_line_num
1195 END;
1196
1197 open csr_set_linenum;
1198 LOOP
1199 FETCH csr_set_linenum
1200 BULK COLLECT INTO l_array_rowid, l_array_ae_line_num
1201 LIMIT C_BULK_LIMIT;
1202
1203 IF(l_array_rowid.COUNT=0) THEN
1204 EXIT;
1205 END IF;
1206
1207 FORALL i IN 1..l_array_rowid.count
1208 UPDATE xla_ae_lines_gt
1209 SET ae_line_num = l_array_ae_line_num(i)
1210 WHERE rowid = l_array_rowid(i);
1211 END LOOP;
1212 Close csr_set_linenum;
1213
1214 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1215
1216 trace
1217 (p_msg => 'SQL - Update xla_ae_lines_gt 3'
1218 ,p_level => C_LEVEL_STATEMENT
1219 ,p_module => l_log_module);
1220
1221 END IF;
1222
1223 open csr_rounding_lines;
1224 j:=1;
1225 l_curr_rounding_class_code := null;
1226 l_curr_doc_rounding_level := null;
1227 l_curr_total_unrounded :=null;
1228 l_curr_total_rounded :=null;
1229 l_curr_max_rowid :=null;
1230 l_curr_max_amount := null;
1231 l_curr_ledger_id :=null;
1232 l_curr_header_id :=null;
1233 l_curr_mau := null;
1234 l_curr_entd_mau := null;
1235 l_curr_rounding_rule_code := null;
1236 l_curr_entd_rounding_amount := null;
1237 l_curr_entd_total_unrounded :=null;
1238 l_curr_entd_total_rounded :=null;
1239
1240 LOOP
1241 FETCH csr_rounding_lines
1242 BULK COLLECT INTO l_array_rowid
1243 ,l_array_rounding_class_code
1244 ,l_array_doc_rounding_level
1245 ,l_array_unrounded_amount
1246 ,l_array_ledger_id
1247 ,l_array_header_id
1248 ,l_array_unrounded_entd_amount
1249 ,l_array_entd_mau
1250 LIMIT C_BULK_LIMIT;
1251
1252 IF(l_array_rounding_class_code.COUNT=0) THEN
1253 EXIT;
1254 END IF;
1255 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1256 trace
1257 (p_msg => 'SQL - Update xla_ae_lines_gt 6'
1258 ,p_level => C_LEVEL_STATEMENT
1259 ,p_module => l_log_module);
1260 trace
1261 (p_msg => 'count:'||to_char(l_array_rounding_class_code.count)
1262 ,p_level => C_LEVEL_STATEMENT
1263 ,p_module => l_log_module);
1264 END IF;
1265
1266 FOR Idx IN l_array_rounding_class_code.FIRST .. l_array_rounding_class_code.LAST LOOP
1267 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1268 trace
1269 (p_msg => 'Ixd:'||to_char(Idx) ||' rounding class code:'||l_array_rounding_class_code(Idx) || ' rounding level:'||l_array_doc_rounding_level(Idx)
1270 || ' ledgerid:'||to_char(l_array_ledger_id(Idx))||' unrounded:'|| to_char(l_curr_total_unrounded)
1271 ||' rounded:'|| to_char(l_curr_total_rounded)
1272 ,p_level => C_LEVEL_STATEMENT
1273 ,p_module => l_log_module);
1274 trace
1275 (p_msg => 'amount:'||to_char(l_array_unrounded_amount(Idx))||'curr mau:'||to_char(l_curr_mau)||' curr rule code:'||l_curr_rounding_rule_code
1276 ,p_level => C_LEVEL_STATEMENT
1277 ,p_module => l_log_module);
1278 trace
1279 (p_msg => 'cur rounding class code:'||l_curr_rounding_class_code || ' rounding level:'||l_curr_doc_rounding_level || ' ledgerid:'||to_char(l_curr_ledger_id)
1280 ,p_level => C_LEVEL_STATEMENT
1281 ,p_module => l_log_module);
1282 trace
1283 (p_msg => ' unrounded entered:'|| to_char(l_curr_entd_total_unrounded)
1284 ||' rounded entered:'|| to_char(l_curr_entd_total_rounded)
1285 ||' amount:'|| to_char(l_array_unrounded_entd_amount(Idx))
1286 ||' mau:'|| to_char(l_array_entd_mau(Idx))
1287 ,p_level => C_LEVEL_STATEMENT
1288 ,p_module => l_log_module);
1289 END IF;
1290
1291 IF(l_array_rounding_class_code(Idx) = l_curr_rounding_class_code
1292 AND l_array_doc_rounding_level(Idx) = l_curr_doc_rounding_level
1293 AND l_array_header_id(Idx) = l_curr_header_id
1294 AND l_array_ledger_id(Idx) = l_curr_ledger_id) THEN
1295 l_curr_total_unrounded:= l_curr_total_unrounded + l_array_unrounded_amount(Idx);
1296 IF(l_curr_rounding_rule_code = 'UP') THEN
1297 l_temp := CEIL( l_array_unrounded_amount(Idx)/l_curr_mau);
1298 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1299 l_temp := FLOOR( l_array_unrounded_amount(Idx)/l_curr_mau);
1300 ELSE
1301 l_temp := ROUND( l_array_unrounded_amount(Idx)/l_curr_mau);
1302 END IF;
1303 l_curr_total_rounded:= l_curr_total_rounded +l_temp *l_curr_mau;
1304 l_curr_entd_total_unrounded:= l_curr_entd_total_unrounded + l_array_unrounded_entd_amount(Idx);
1305 IF(l_curr_rounding_rule_code = 'UP') THEN
1306 l_temp := CEIL(l_array_unrounded_entd_amount(Idx)/l_array_entd_mau(Idx));
1307 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1308 l_temp := FLOOR(l_array_unrounded_entd_amount(Idx)/l_array_entd_mau(Idx));
1309 ELSE
1310 l_temp := ROUND(l_array_unrounded_entd_amount(Idx)/l_array_entd_mau(Idx));
1311 END IF;
1312
1313 l_curr_entd_total_rounded:= l_curr_entd_total_rounded
1314 +l_temp *l_array_entd_mau(Idx);
1315 IF(l_curr_max_amount < ABS(l_array_unrounded_amount(Idx))) THEN
1316 l_curr_max_amount := ABS(l_array_unrounded_amount(Idx));
1317 l_curr_max_rowid := l_array_rowid(Idx);
1318 END IF;
1319 ELSE
1320 IF(l_curr_total_unrounded is not null) THEN
1321 IF(l_curr_rounding_rule_code = 'UP') THEN
1322 l_temp := CEIL(l_curr_total_unrounded/l_curr_mau);
1323 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1324 l_temp := FLOOR(l_curr_total_unrounded/l_curr_mau);
1325 ELSE
1326 l_temp := ROUND(l_curr_total_unrounded/l_curr_mau);
1327 END IF;
1328
1329 l_curr_doc_rounding_amount := l_temp *l_curr_mau -l_curr_total_rounded;
1330 IF(l_curr_rounding_rule_code = 'UP') THEN
1331 l_temp := CEIL(l_curr_entd_total_unrounded/l_curr_entd_mau);
1332 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1333 l_temp := FLOOR(l_curr_entd_total_unrounded/l_curr_entd_mau);
1334 ELSE
1335 l_temp := ROUND(l_curr_entd_total_unrounded/l_curr_entd_mau);
1336 END IF;
1337 l_curr_entd_rounding_amount := l_temp *l_curr_entd_mau
1338 -l_curr_entd_total_rounded;
1339 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1340 trace
1341 (p_msg => 'doc rounding is:'||to_char(l_curr_doc_rounding_amount)
1342 ||' unrounded:'|| to_char(l_curr_total_unrounded)
1343 ||' rounded:'|| to_char(l_curr_total_rounded)
1344 ,p_level => C_LEVEL_STATEMENT
1345 ,p_module => l_log_module);
1346 trace
1347 (p_msg => 'entd rounding is:'||to_char(l_curr_entd_rounding_amount)
1348 ||' unrounded:'|| to_char(l_curr_entd_total_unrounded)
1349 ||' rounded:'|| to_char(l_curr_entd_total_rounded)
1350 ,p_level => C_LEVEL_STATEMENT
1351 ,p_module => l_log_module);
1352 END IF;
1353 IF(l_curr_doc_rounding_amount <>0 or l_curr_entd_rounding_amount <> 0) THEN
1354 l_array_rowid1(j):= l_curr_max_rowid;
1355 l_array_doc_rounding_amt1(j) := l_curr_doc_rounding_amount;
1356 l_array_rounding_entd_amt1(j) := l_curr_entd_rounding_amount;
1357 j:= j+1;
1358 IF (j> C_BULK_LIMIT) THEN
1359 FORALL i in 1..j-1
1360 update xla_ae_lines_gt
1361 set doc_rounding_acctd_amt = l_array_doc_rounding_amt1(i)
1362 ,doc_rounding_entered_amt = l_array_rounding_entd_amt1(i)
1363 where rowid = l_array_rowid1(i);
1364 j:=1;
1365 END IF;
1366 END IF;
1367 END IF;
1368 IF(l_curr_ledger_id is null or
1369 l_curr_ledger_id <> l_array_ledger_id(Idx)) THEN
1370 l_curr_ledger_id :=l_array_ledger_id(Idx);
1371 l_curr_mau := l_array_mau(l_curr_ledger_id);
1372 l_curr_rounding_rule_code:= l_array_rounding_rule_code(l_curr_ledger_id);
1373 END IF;
1374 l_curr_entd_mau:=l_array_entd_mau(Idx);
1375 l_curr_header_id :=l_array_header_id(Idx);
1376 l_curr_rounding_class_code := l_array_rounding_class_code(Idx);
1377 l_curr_doc_rounding_level := l_array_doc_rounding_level(Idx);
1378 l_curr_total_unrounded:= l_array_unrounded_amount(Idx);
1379 IF(l_curr_rounding_rule_code = 'UP') THEN
1380 l_temp := CEIL(l_array_unrounded_amount(Idx)/l_curr_mau);
1381 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1382 l_temp := FLOOR(l_array_unrounded_amount(Idx)/l_curr_mau);
1383 ELSE
1384 l_temp := ROUND(l_array_unrounded_amount(Idx)/l_curr_mau);
1385 END IF;
1386
1387 l_curr_total_rounded:= l_temp *l_curr_mau;
1388 l_curr_entd_total_unrounded:= l_array_unrounded_entd_amount(Idx);
1389 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1390 trace
1391 (p_msg => '----l_curr_entd_total_rounded:'||to_char(l_curr_entd_total_rounded)
1392 ||' l_array_unrounded_entd_amount(Idx):'|| to_char(l_array_unrounded_entd_amount(Idx))
1393 ||' l_curr_entd_mau:'|| to_char(l_curr_entd_mau)
1394 ||'l_curr_rounding_rule_code:'|| l_curr_rounding_rule_code
1395 ,p_level => C_LEVEL_STATEMENT
1396 ,p_module => l_log_module);
1397 END IF;
1398 IF(l_curr_rounding_rule_code = 'UP') THEN
1399 l_temp := CEIL(l_array_unrounded_entd_amount(Idx)/l_curr_entd_mau);
1400 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1401 l_temp := FLOOR(l_array_unrounded_entd_amount(Idx)/l_curr_entd_mau);
1402 ELSE
1403 l_temp := ROUND(l_array_unrounded_entd_amount(Idx)/l_curr_entd_mau);
1404 END IF;
1405
1406 l_curr_entd_total_rounded:= l_temp *l_curr_entd_mau;
1407 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1408 trace
1409 (p_msg => '----l_curr_entd_total_rounded:'||to_char(l_curr_entd_total_rounded)
1410 ||' l_array_unrounded_entd_amount(Idx):'|| to_char(l_array_unrounded_entd_amount(Idx))
1411 ||' l_curr_entd_mau:'|| to_char(l_curr_entd_mau)
1412 ||'l_curr_rounding_rule_code:'|| l_curr_rounding_rule_code
1413 ,p_level => C_LEVEL_STATEMENT
1414 ,p_module => l_log_module);
1415 END IF;
1416 l_curr_max_rowid := l_array_rowid(Idx);
1417 l_curr_max_amount := ABS(l_array_unrounded_amount(Idx));
1418 END IF;
1419 END LOOP;
1420 END LOOP;
1421 -- process the last one
1422 IF(l_curr_total_unrounded is not null) THEN
1423 IF(l_curr_rounding_rule_code = 'UP') THEN
1424 l_temp := CEIL(l_curr_total_unrounded/l_curr_mau);
1425 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1426 l_temp := FLOOR(l_curr_total_unrounded/l_curr_mau);
1427 ELSE
1428 l_temp := ROUND(l_curr_total_unrounded/l_curr_mau);
1429 END IF;
1430 l_curr_doc_rounding_amount := l_temp
1431 *l_curr_mau -l_curr_total_rounded;
1432 IF(l_curr_rounding_rule_code = 'UP') THEN
1433 l_temp := CEIL(l_curr_entd_total_unrounded/l_curr_entd_mau);
1434 ELSIF (l_curr_rounding_rule_code = 'DOWN') THEN
1435 l_temp := FLOOR(l_curr_entd_total_unrounded/l_curr_entd_mau);
1436 ELSE
1437 l_temp := ROUND(l_curr_entd_total_unrounded/l_curr_entd_mau);
1438 END IF;
1439 l_curr_entd_rounding_amount := l_temp
1440 *l_curr_entd_mau -l_curr_entd_total_rounded;
1441 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1442 trace
1443 (p_msg => 'doc rounding is:'||to_char(l_curr_doc_rounding_amount)
1444 ||' unrounded:'|| to_char(l_curr_total_unrounded)
1445 ||' rounded:'|| to_char(l_curr_total_rounded)
1446 ,p_level => C_LEVEL_STATEMENT
1447 ,p_module => l_log_module);
1448 END IF;
1449 END IF;
1450 IF(l_curr_doc_rounding_amount <>0 or l_curr_entd_rounding_amount <> 0) THEN
1451 l_array_rowid1(j):= l_curr_max_rowid;
1452 l_array_doc_rounding_amt1(j) := l_curr_doc_rounding_amount;
1453 l_array_rounding_entd_amt1(j) := l_curr_entd_rounding_amount;
1454 j:= j+1;
1455 END IF;
1456
1457 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1458 trace
1459 (p_msg => 'SQL - Update xla_ae_lines_gt 7, j='||to_char(j)
1460 ,p_level => C_LEVEL_STATEMENT
1461 ,p_module => l_log_module);
1462 END IF;
1463
1464 IF j>1 THEN
1465 FORALL i in 1..j-1
1466 update xla_ae_lines_gt
1467 set doc_rounding_acctd_amt = l_array_doc_rounding_amt1(i)
1468 ,doc_rounding_entered_amt = l_array_rounding_entd_amt1(i)
1469 where rowid = l_array_rowid1(i);
1470 END IF;
1471
1472 EXCEPTION
1473 WHEN OTHERS THEN
1474
1475 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1476 trace
1477 (p_msg => 'ERROR: XLA_AP_CANNOT_INSERT_JE ='||sqlerrm
1478 ,p_level => C_LEVEL_EXCEPTION
1479 ,p_module => l_log_module);
1480 END IF;
1481
1482 xla_exceptions_pkg.raise_message (p_appli_s_name => 'XLA'
1483 ,p_msg_name => 'XLA_AP_CANNOT_INSERT_JE'
1484 ,p_token_1 => 'ERROR'
1485 ,p_value_1 => sqlerrm
1486 );
1487 END;
1488
1489 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1490
1491 trace
1492 (p_msg => 'END of get_line_number'
1493 ,p_level => C_LEVEL_PROCEDURE
1494 ,p_module => l_log_module);
1495 END IF;
1496
1497 EXCEPTION
1498 WHEN xla_exceptions_pkg.application_exception THEN
1499 RAISE;
1500 WHEN OTHERS THEN
1501 xla_exceptions_pkg.raise_message
1502 (p_location => 'XLA_THIRD_PARTY_MERGE.get_line_number');
1503 END get_line_number;
1504
1505 PROCEDURE insert_headers(
1506 p_batch_id IN NUMBER
1507 ,p_application_id IN NUMBER
1508 ,p_event_id IN NUMBER
1509 ,p_accounting_mode IN VARCHAR2)
1510 IS
1511 v_function VARCHAR2(240);
1512 v_module VARCHAR2(240);
1513 BEGIN
1514 v_function := 'xla_third_party_merge.insert_headers';
1515 v_module := C_DEFAULT_MODULE||'.insert_headers';
1516 IF (C_LEVEL_PROCEDURE >= g_log_level)
1517 THEN
1518 trace( p_msg => 'BEGIN - ' || v_function
1519 , p_level => C_LEVEL_PROCEDURE
1520 , p_module => v_module);
1521 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1522 INSERT INTO xla_ae_headers
1523 (
1524 ae_header_id
1525 , application_id
1526 , ledger_id
1527 , entity_id
1528 , event_id
1529 , event_type_code
1530 , accounting_date
1531 , gl_transfer_status_code
1532 , je_category_name
1533 , accounting_entry_status_code
1534 , accounting_entry_type_code
1535 , product_rule_type_code
1536 , product_rule_code
1537 , product_rule_version
1538 , description
1539 , creation_date
1540 , created_by
1541 , last_update_date
1542 , last_updated_by
1543 , last_update_login
1544 , doc_sequence_id
1545 , doc_sequence_value
1546 , doc_category_code
1547 , program_update_date
1548 , program_application_id
1549 , program_id
1550 , request_id
1551 , budget_version_id
1552 , balance_type_code
1553 , completed_date
1554 , period_name
1555 , accounting_batch_id
1556 , amb_context_code
1557 , zero_amount_flag
1558 , parent_ae_header_id -- 4262811
1559 , parent_ae_line_num -- 4262811
1560 , accrual_reversal_flag -- 4262811
1561 , merge_event_id
1562 )
1563 SELECT
1564 hed.ae_header_id
1565 , p_application_id
1566 , hed.ledger_id
1567 , hed.entity_id
1568 , hed.event_id
1569 , hed.event_type_code
1570 , hed.accounting_date
1571 , hed.gl_transfer_status_code
1572 , hed.je_category_name
1573 , hed.accounting_entry_status_code
1574 , hed.accounting_entry_type_code
1575 , hed.product_rule_type_code
1576 , hed.product_rule_code
1577 , hed.product_rule_version
1578 , hed.description
1579 , TRUNC(SYSDATE)
1580 , xla_environment_pkg.g_Usr_Id
1581 , TRUNC(SYSDATE)
1582 , xla_environment_pkg.g_Usr_Id
1583 , xla_environment_pkg.g_Login_Id
1584 , hed.doc_sequence_id
1585 , hed.doc_sequence_value
1586 , hed.doc_category_code
1587 , TRUNC(SYSDATE)
1588 , xla_environment_pkg.g_Prog_Appl_Id
1589 , xla_environment_pkg.g_Prog_Id
1590 , xla_environment_pkg.g_req_Id
1591 , CASE hed.balance_type_code
1592 WHEN 'B' THEN hed.budget_version_id
1593 ELSE NULL
1594 END
1595 , hed.balance_type_code
1596 , sysdate
1597 , hed.period_name
1598 , p_batch_id
1599 , hed.amb_context_code
1600 , 'N'
1601 , hed.parent_header_id -- 4262811
1602 , hed.parent_ae_line_num -- 4262811
1603 , hed.accrual_reversal_flag -- 4262811
1604 , p_event_id
1605 FROM xla_ae_headers_gt hed;
1606
1607 IF (C_LEVEL_PROCEDURE >= g_log_level)
1608 THEN
1609 trace( p_msg => 'END - ' || v_function
1610 , p_level => C_LEVEL_PROCEDURE
1611 , p_module => v_module);
1612 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1613
1614 END insert_headers;
1615
1616 PROCEDURE insert_links(p_application_id IN NUMBER)
1617
1618 IS
1619 v_function VARCHAR2(240);
1620 v_module VARCHAR2(240);
1621 BEGIN
1622 v_function := 'xla_third_party_merge.insert_links';
1623 v_module := C_DEFAULT_MODULE||'.insert_links';
1624 IF (C_LEVEL_PROCEDURE >= g_log_level)
1625 THEN
1626 trace( p_msg => 'BEGIN - ' || v_function
1627 , p_level => C_LEVEL_PROCEDURE
1628 , p_module => v_module);
1629 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1630
1631 INSERT INTO xla_distribution_links
1632 (
1633 application_id
1634 , event_id
1635 , source_distribution_id_char_1
1636 , source_distribution_id_char_2
1637 , source_distribution_id_char_3
1638 , source_distribution_id_char_4
1639 , source_distribution_id_char_5
1640 , source_distribution_id_num_1
1641 , source_distribution_id_num_2
1642 , source_distribution_id_num_3
1643 , source_distribution_id_num_4
1644 , source_distribution_id_num_5
1645 , source_distribution_type
1646 , unrounded_entered_cr
1647 , unrounded_entered_dr
1648 , unrounded_accounted_cr
1649 , unrounded_accounted_dr
1650 , ae_header_id
1651 , ae_line_num
1652 , temp_line_num
1653 , tax_line_ref_id
1654 , tax_summary_line_ref_id
1655 , tax_rec_nrec_dist_ref_id
1656 , statistical_amount
1657 , event_class_code
1658 , event_type_code
1659 , line_definition_owner_code
1660 , line_definition_code
1661 , accounting_line_type_code
1662 , accounting_line_code
1663 , ref_event_id
1664 , ref_ae_header_id
1665 , ref_temp_line_num
1666 , merge_duplicate_code
1667 , calculate_acctd_amts_flag
1668 , calculate_g_l_amts_flag
1669 , rounding_class_code
1670 , document_rounding_level
1671 , doc_rounding_acctd_amt
1672 , doc_rounding_entered_amt
1673 )
1674 SELECT
1675 p_application_id
1676 , event_id
1677 , source_distribution_id_char_1
1678 , source_distribution_id_char_2
1679 , source_distribution_id_char_3
1680 , source_distribution_id_char_4
1681 , source_distribution_id_char_5
1682 , source_distribution_id_num_1
1683 , source_distribution_id_num_2
1684 , source_distribution_id_num_3
1685 , source_distribution_id_num_4
1686 , source_distribution_id_num_5
1687 , source_distribution_type
1688 , unrounded_entered_cr
1689 , unrounded_entered_dr
1690 , unrounded_accounted_cr
1691 , unrounded_accounted_dr
1692 , ae_header_id
1693 , ae_line_num
1694 , temp_line_num
1695 , tax_line_ref_id
1696 , tax_summary_line_ref_id
1697 , tax_rec_nrec_dist_ref_id
1698 , statistical_amount
1699 , event_class_code
1700 , event_type_code
1701 , line_definition_owner_code
1702 , line_definition_code
1703 , accounting_line_type_code
1704 , accounting_line_code
1705 , ref_event_id
1706 , ref_ae_header_id
1707 , ref_temp_line_num
1708 , merge_duplicate_code
1709 , calculate_acctd_amts_flag
1710 , calculate_g_l_amts_flag
1711 , rounding_class_code
1712 , document_rounding_level
1713 , doc_rounding_acctd_amt
1714 , doc_rounding_entered_amt
1715 FROM xla_ae_lines_gt;
1716
1717 IF (C_LEVEL_PROCEDURE >= g_log_level)
1718 THEN
1719 trace( p_msg => 'END - ' || v_function
1720 , p_level => C_LEVEL_PROCEDURE
1721 , p_module => v_module);
1722 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1723
1724 END insert_links;
1725
1726 PROCEDURE insert_lines(p_application_id IN INTEGER
1727 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
1728 ,p_array_reversal_option IN t_varchar30_array
1729 ,p_array_mau IN t_number_array
1730 ,p_array_rounding_rule IN t_varchar30_array
1731 ) IS
1732 l_count number;
1733 v_query_str VARCHAR2(8000);
1734
1735 v_function VARCHAR2(240);
1736 v_module VARCHAR2(240);
1737 BEGIN
1738 v_function := 'xla_third_party_merge.insert_lines';
1739 v_module := C_DEFAULT_MODULE||'.insert_lines';
1740 IF (C_LEVEL_PROCEDURE >= g_log_level)
1741 THEN
1742 trace( p_msg => 'BEGIN - ' || v_function
1743 , p_level => C_LEVEL_PROCEDURE
1744 , p_module => v_module);
1745 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
1746
1747 FORALL i in 1..p_array_ledger_id.count
1748 INSERT INTO xla_ae_lines
1749 (
1750 ae_header_id
1751 , ae_line_num
1752 , displayed_line_number
1753 , code_combination_id
1754 , gl_transfer_mode_code
1755 , creation_date
1756 , accounted_cr
1757 , accounted_dr
1758 , unrounded_accounted_cr
1759 , unrounded_accounted_dr
1760 , gain_or_loss_flag
1761 , accounting_class_code
1762 , currency_code
1763 , currency_conversion_date
1764 , currency_conversion_rate
1765 , currency_conversion_type
1766 , description
1767 , entered_cr
1768 , entered_dr
1769 , unrounded_entered_cr
1770 , unrounded_entered_dr
1771 , last_update_date
1772 , last_update_login
1773 , party_id
1774 , party_site_id
1775 , party_type_code
1776 , statistical_amount
1777 , ussgl_transaction_code
1778 , created_by
1779 , last_updated_by
1780 , jgzz_recon_ref
1781 , program_update_date
1782 , program_application_id
1783 , program_id
1784 , application_id
1785 , request_id
1786 , gl_sl_link_table
1787 , business_class_code -- 4336173
1788 , mpa_accrual_entry_flag -- 4262811
1789 , encumbrance_type_id -- 4458381 Public Sector Enh
1790 , accounting_date
1791 , ledger_id
1792 , control_balance_flag
1793 , gl_sl_link_id --5041325
1794 )
1795 (SELECT
1796 ae_header_id
1797 , ae_line_num
1798 , displayed_line_number
1799 , code_combination_id
1800 , gl_transfer_mode_code
1801 , creation_date
1802 , accounted_cr
1803 , accounted_dr
1804 , unrounded_accounted_cr
1805 , unrounded_accounted_dr
1806 , gain_or_loss_flag
1807 , accounting_class_code
1808 , currency_code
1809 , currency_conversion_date
1810 , currency_conversion_rate
1811 , currency_conversion_type
1812 , description
1813 , entered_cr
1814 , entered_dr
1815 , unrounded_entered_cr
1816 , unrounded_entered_dr
1817 , last_update_date
1818 , last_update_login
1819 , party_id
1820 , party_site_id
1821 , party_type_code
1822 , statistical_amount
1823 , ussgl_transaction_code
1824 , created_by
1825 , last_updated_by
1826 , jgzz_recon_ref
1827 , program_update_date
1828 , program_application_id
1829 , program_id
1830 , application_id
1831 , request_id
1832 , gl_sl_link_table
1833 , business_class_code -- 4336173
1834 , mpa_accrual_entry_flag -- 4262811
1835 , encumbrance_type_id -- 4458381 Public Sector Enh
1836 , accounting_date
1837 , ledger_id
1838 , alt_segment1
1839 , Decode(accounting_entry_status_code,'F',xla_gl_sl_link_id_s.nextval,NULL)
1840 FROM
1841 (SELECT
1842 lin.ae_header_id ae_header_id
1843 , ae_line_num
1844 -- we always treat switch_side_flag as 'Y' since we can't get the original switch_side_flag any more
1845 ,
1846 ROW_NUMBER()
1847 over (PARTITION BY ae_header_id
1848 order by
1849 ABS (
1850 NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)+
1851 NVL(SUM(doc_rounding_acctd_amt), 0)
1852 )
1853 desc) displayed_line_number
1854 , code_combination_id
1855 , 'N' gl_transfer_mode_code
1856 , sysdate creation_date
1857 -- accounted_cr
1858 -- no need to take care of the case that both accounted dr and cr are null.
1859 -- this can't happen in third party merge
1860 ,
1861 CASE p_array_reversal_option(i)
1862 WHEN 'SIDE' THEN
1863 CASE SIGN(
1864 NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)+
1865 NVL(SUM(doc_rounding_acctd_amt), 0)
1866 )
1867 WHEN -1 THEN null
1868 WHEN 1 THEN
1869 DECODE(p_array_rounding_rule(i)
1870 ,'UP', CEIL((NVL(SUM(unrounded_accounted_cr),0)
1871 - NVL(SUM(unrounded_accounted_dr),0)
1872 + NVL(SUM(doc_rounding_acctd_amt), 0))
1873 /p_array_mau(i))
1874 ,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_cr),0)
1875 - NVL(SUM(unrounded_accounted_dr),0)
1876 + NVL(SUM(doc_rounding_acctd_amt), 0))
1877 /p_array_mau(i))
1878 ,ROUND((NVL(SUM(unrounded_accounted_cr),0)
1879 - NVL(SUM(unrounded_accounted_dr),0)
1880 + NVL(SUM(doc_rounding_acctd_amt), 0))
1881 /p_array_mau(i))
1882 )*p_array_mau(i)
1883 ELSE
1884 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
1885 NVL(SUM(doc_rounding_entered_amt), 0))
1886 WHEN -1 THEN null
1887 ELSE 0
1888 END
1889 END
1890 ELSE
1891 CASE SIGN(
1892 NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)+
1893 NVL(SUM(doc_rounding_acctd_amt), 0)
1894 )
1895 WHEN 1 THEN null
1896 WHEN -1 THEN
1897 DECODE(p_array_rounding_rule(i)
1898 ,'UP', CEIL((NVL(SUM(unrounded_accounted_cr),0)
1899 - NVL(SUM(unrounded_accounted_dr),0)
1900 + NVL(SUM(doc_rounding_acctd_amt), 0))
1901 /p_array_mau(i))
1902 ,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_cr),0)
1903 - NVL(SUM(unrounded_accounted_dr),0)
1904 + NVL(SUM(doc_rounding_acctd_amt), 0))
1905 /p_array_mau(i))
1906 ,ROUND((NVL(SUM(unrounded_accounted_cr),0)
1907 - NVL(SUM(unrounded_accounted_dr),0)
1908 + NVL(SUM(doc_rounding_acctd_amt), 0))
1909 /p_array_mau(i))
1910 )*p_array_mau(i)
1911 ELSE
1912 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
1913 NVL(SUM(doc_rounding_entered_amt), 0))
1914 WHEN 1 THEN null
1915 ELSE 0
1916 END
1917 END
1918 END
1919 accounted_cr
1920 -- accounted_dr
1921 ,
1922 CASE p_array_reversal_option(i)
1923 WHEN 'SIDE' THEN
1924 CASE SIGN(
1925 NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)-
1926 NVL(SUM(doc_rounding_acctd_amt), 0)
1927 )
1928 WHEN -1 THEN null
1929 WHEN 1 THEN
1930 DECODE(p_array_rounding_rule(i)
1931 ,'UP', CEIL((NVL(SUM(unrounded_accounted_dr),0)
1932 - NVL(SUM(unrounded_accounted_cr),0)
1933 - NVL(SUM(doc_rounding_acctd_amt), 0))
1934 /p_array_mau(i))
1935 ,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_dr),0)
1936 - NVL(SUM(unrounded_accounted_cr),0)
1937 - NVL(SUM(doc_rounding_acctd_amt), 0))
1938 /p_array_mau(i))
1939 ,ROUND((NVL(SUM(unrounded_accounted_dr),0)
1940 - NVL(SUM(unrounded_accounted_cr),0)
1941 - NVL(SUM(doc_rounding_acctd_amt), 0))
1942 /p_array_mau(i))
1943 )*p_array_mau(i)
1944 ELSE
1945 CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
1946 NVL(SUM(doc_rounding_entered_amt), 0))
1947 WHEN 1 THEN 0
1948 ELSE null
1949 END
1950 END
1951 ELSE
1952 CASE SIGN(
1953 NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)-
1954 NVL(SUM(doc_rounding_acctd_amt), 0)
1955 )
1956 WHEN 1 THEN null
1957 WHEN -1 THEN
1958 DECODE(p_array_rounding_rule(i)
1959 ,'UP', CEIL((NVL(SUM(unrounded_accounted_dr),0)
1960 - NVL(SUM(unrounded_accounted_cr),0)
1961 - NVL(SUM(doc_rounding_acctd_amt), 0))
1962 /p_array_mau(i))
1963 ,'DOWN', FLOOR((NVL(SUM(unrounded_accounted_dr),0)
1964 - NVL(SUM(unrounded_accounted_cr),0)
1965 - NVL(SUM(doc_rounding_acctd_amt), 0))
1966 /p_array_mau(i))
1967 ,ROUND((NVL(SUM(unrounded_accounted_dr),0)
1968 - NVL(SUM(unrounded_accounted_cr),0)
1969 - NVL(SUM(doc_rounding_acctd_amt), 0))
1970 /p_array_mau(i))
1971 )*p_array_mau(i)
1972 ELSE
1973 CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
1974 NVL(SUM(doc_rounding_entered_amt), 0))
1975 WHEN -1 THEN 0
1976 ELSE null
1977 END
1978 END
1979 END
1980 accounted_dr
1981 -- unrounded_accounted_cr
1982 ,
1983 CASE p_array_reversal_option(i)
1984 WHEN 'SIDE' THEN
1985 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
1986 WHEN -1 THEN null
1987 WHEN 1 THEN
1988 NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
1989 ELSE
1990 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
1991 WHEN -1 THEN null
1992 ELSE 0
1993 END
1994 END
1995 ELSE
1996 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
1997 WHEN 1 THEN null
1998 WHEN -1 THEN
1999 NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
2000 ELSE
2001 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
2002 WHEN 1 THEN null
2003 ELSE 0
2004 END
2005 END
2006 END
2007 unrounded_accounted_cr
2008 -- unrounded_accounted_dr
2009 ,
2010 CASE p_array_reversal_option(i)
2011 WHEN 'SIDE' THEN
2012 CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0))
2013 WHEN 1 THEN
2014 NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
2015 WHEN -1 THEN null
2016 ELSE
2017 CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0))
2018 WHEN 1 THEN 0
2019 ELSE null
2020 END
2021 END
2022 ELSE
2023 CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0))
2024 WHEN -1 THEN
2025 NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
2026 WHEN 1 THEN null
2027 ELSE
2028 CASE SIGN(NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0))
2029 WHEN -1 THEN 0
2030 ELSE null
2031 END
2032 END
2033 END
2034 unrounded_accounted_dr
2035 , gain_or_loss_flag
2036 , accounting_class_code
2037 , currency_code
2038 , currency_conversion_date
2039 , currency_conversion_rate
2040 , currency_conversion_type
2041 , lin.description description
2042 -- entered_cr
2043 ,
2044 CASE p_array_reversal_option(i)
2045 WHEN 'SIDE' THEN
2046 CASE SIGN(
2047 NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
2048 NVL(SUM(doc_rounding_entered_amt), 0)
2049 )
2050 WHEN -1 THEN null
2051 WHEN 1 THEN
2052 DECODE(p_array_rounding_rule(i)
2053 ,'UP', CEIL((NVL(SUM(unrounded_entered_cr),0)
2054 - NVL(SUM(unrounded_entered_dr),0)
2055 + NVL(SUM(doc_rounding_entered_amt), 0))
2056 /entered_currency_mau)
2057 ,'DOWN', FLOOR((NVL(SUM(unrounded_entered_cr),0)
2058 - NVL(SUM(unrounded_entered_dr),0)
2059 + NVL(SUM(doc_rounding_entered_amt), 0))
2060 /entered_currency_mau)
2061 ,ROUND((NVL(SUM(unrounded_entered_cr),0)
2062 - NVL(SUM(unrounded_entered_dr),0)
2063 + NVL(SUM(doc_rounding_entered_amt), 0))
2064 /entered_currency_mau)
2065 )*entered_currency_mau
2066 ELSE
2067 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
2068 +NVL(SUM(doc_rounding_acctd_amt), 0))
2069 WHEN -1 THEN null
2070 ELSE 0
2071 END
2072 END
2073 ELSE
2074 CASE SIGN(
2075 NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)+
2076 NVL(SUM(doc_rounding_entered_amt), 0)
2077 )
2078 WHEN 1 THEN null
2079 WHEN -1 THEN
2080 DECODE(p_array_rounding_rule(i)
2081 ,'UP', CEIL((NVL(SUM(unrounded_entered_cr),0)
2082 - NVL(SUM(unrounded_entered_dr),0)
2083 + NVL(SUM(doc_rounding_entered_amt), 0))
2084 /entered_currency_mau)
2085 ,'DOWN', FLOOR((NVL(SUM(unrounded_entered_cr),0)
2086 - NVL(SUM(unrounded_entered_dr),0)
2087 + NVL(SUM(doc_rounding_entered_amt), 0))
2088 /entered_currency_mau)
2089 ,ROUND((NVL(SUM(unrounded_entered_cr),0)
2090 - NVL(SUM(unrounded_entered_dr),0)
2091 + NVL(SUM(doc_rounding_entered_amt), 0))
2092 /entered_currency_mau)
2093 )*entered_currency_mau
2094 ELSE
2095 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0)
2096 +NVL(SUM(doc_rounding_acctd_amt), 0))
2097 WHEN 1 THEN null
2098 ELSE 0
2099 END
2100 END
2101 END
2102 entered_cr
2103 -- entered_dr
2104 ,
2105 CASE p_array_reversal_option(i)
2106 WHEN 'SIDE' THEN
2107 CASE SIGN(
2108 NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
2109 NVL(SUM(doc_rounding_entered_amt), 0)
2110 )
2111 WHEN -1 THEN null
2112 WHEN 1 THEN
2113 DECODE(p_array_rounding_rule(i)
2114 ,'UP', CEIL((NVL(SUM(unrounded_entered_dr),0)
2115 - NVL(SUM(unrounded_entered_cr),0)
2116 - NVL(SUM(doc_rounding_entered_amt), 0))
2117 /entered_currency_mau)
2118 ,'DOWN', FLOOR((NVL(SUM(unrounded_entered_dr),0)
2119 - NVL(SUM(unrounded_entered_cr),0)
2120 - NVL(SUM(doc_rounding_entered_amt), 0))
2121 /entered_currency_mau)
2122 ,ROUND((NVL(SUM(unrounded_entered_dr),0)
2123 - NVL(SUM(unrounded_entered_cr),0)
2124 - NVL(SUM(doc_rounding_entered_amt), 0))
2125 /entered_currency_mau)
2126 )*entered_currency_mau
2127 ELSE
2128 CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
2129 -NVL(SUM(doc_rounding_acctd_amt), 0))
2130 WHEN 1 THEN 0
2131 ELSE null
2132 END
2133 END
2134 ELSE
2135 CASE SIGN(
2136 NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)-
2137 NVL(SUM(doc_rounding_entered_amt), 0)
2138 )
2139 WHEN 1 THEN null
2140 WHEN -1 THEN
2141 DECODE(p_array_rounding_rule(i)
2142 ,'UP', CEIL((NVL(SUM(unrounded_entered_dr),0)
2143 - NVL(SUM(unrounded_entered_cr),0)
2144 - NVL(SUM(doc_rounding_entered_amt), 0))
2145 /entered_currency_mau)
2146 ,'DOWN', FLOOR((NVL(SUM(unrounded_entered_dr),0)
2147 - NVL(SUM(unrounded_entered_cr),0)
2148 - NVL(SUM(doc_rounding_entered_amt), 0))
2149 /entered_currency_mau)
2150 ,ROUND((NVL(SUM(unrounded_entered_dr),0)
2151 - NVL(SUM(unrounded_entered_cr),0)
2152 - NVL(SUM(doc_rounding_entered_amt), 0))
2153 /entered_currency_mau)
2154 )*entered_currency_mau
2155 ELSE
2156 CASE SIGN(NVL(SUM(unrounded_accounted_dr),0) - NVL(SUM(unrounded_accounted_cr),0)
2157 -NVL(SUM(doc_rounding_acctd_amt), 0))
2158 WHEN -1 THEN 0
2159 ELSE null
2160 END
2161 END
2162 END
2163 entered_dr
2164 -- unrounded_entered_cr
2165 ,
2166 CASE p_array_reversal_option(i)
2167 WHEN 'SIDE' THEN
2168 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
2169 WHEN -1 THEN null
2170 WHEN 1 THEN NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)
2171 ELSE
2172 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
2173 WHEN -1 THEN null
2174 ELSE 0
2175 END
2176 END
2177 ELSE
2178 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
2179 WHEN 1 THEN null
2180 WHEN -1 THEN NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0)
2181 ELSE
2182 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
2183 WHEN 1 THEN null
2184 ELSE 0
2185 END
2186 END
2187 END
2188 unrounded_entered_cr
2189 -- unrounded_entered_dr
2190 ,
2191 CASE p_array_reversal_option(i)
2192 WHEN 'SIDE' THEN
2193 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
2194 WHEN 1 THEN null
2195 WHEN -1 THEN NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)
2196 ELSE
2197 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
2198 WHEN -1 THEN 0
2199 ELSE null
2200 END
2201 END
2202 ELSE
2203 CASE SIGN(NVL(SUM(unrounded_entered_cr),0) - NVL(SUM(unrounded_entered_dr),0))
2204 WHEN -1 THEN null
2205 WHEN 1 THEN NVL(SUM(unrounded_entered_dr),0) - NVL(SUM(unrounded_entered_cr),0)
2206 ELSE
2207 CASE SIGN(NVL(SUM(unrounded_accounted_cr),0) - NVL(SUM(unrounded_accounted_dr),0))
2208 WHEN 1 THEN 0
2209 ELSE null
2210 END
2211 END
2212 END unrounded_entered_dr
2213 , sysdate last_update_date
2214 , XLA_ENVIRONMENT_PKG.g_login_id last_update_login
2215 , party_id
2216 , party_site_id
2217 , party_type_code
2218 , sum(statistical_amount) statistical_amount
2219 , ussgl_transaction_code
2220 , XLA_ENVIRONMENT_PKG.g_login_id created_by
2221 , XLA_ENVIRONMENT_PKG.g_login_id last_updated_by
2222 , jgzz_recon_ref
2223 , sysdate program_update_date
2224 , XLA_ENVIRONMENT_PKG.g_prog_appl_id program_application_id
2225 , XLA_ENVIRONMENT_PKG.g_prog_id program_id
2226 , p_application_id application_id
2227 , XLA_ENVIRONMENT_PKG.g_req_id request_id
2228 , 'XLAJEL' gl_sl_link_table
2229 , business_class_code -- 4336173
2230 , mpa_accrual_entry_flag -- 4262811
2231 , encumbrance_type_id -- 4458381 Public Sector Enh
2232 , accounting_date
2233 , ledger_id
2234 , alt_segment1
2235 , accounting_entry_status_code
2236 FROM xla_ae_lines_gt lin
2237 WHERE ledger_id = p_array_ledger_id(i)
2238 AND ae_line_num is not NULL
2239 GROUP BY lin.ae_header_id
2240 , ae_line_num
2241 , header_num -- 4262811c MPA reversal lines
2242 , sysdate
2243 , XLA_ENVIRONMENT_PKG.g_login_id
2244 , XLA_ENVIRONMENT_PKG.g_prog_appl_id
2245 , XLA_ENVIRONMENT_PKG.g_prog_id
2246 , XLA_ENVIRONMENT_PKG.g_req_id
2247 , p_application_id
2248 , accounting_class_code
2249 , event_class_code
2250 , event_type_code
2251 , line_definition_owner_code
2252 , line_definition_code
2253 , entered_currency_mau
2254 , currency_code
2255 , currency_conversion_type
2256 , currency_conversion_date
2257 , currency_conversion_rate
2258 , party_id
2259 , party_site_id
2260 , party_type_code
2261 , code_combination_id
2262 , code_combination_status_code
2263 , lin.description
2264 , jgzz_recon_ref
2265 , ussgl_transaction_code
2266 , merge_duplicate_code
2267 , switch_side_flag
2268 , gain_or_loss_flag
2269 , lin.business_class_code -- 4336173
2270 , lin.mpa_accrual_entry_flag -- 4262811
2271 , encumbrance_type_id -- 4458381 Public Sector Enh
2272 , accounting_date
2273 , ledger_id
2274 , alt_segment1
2275 , merge_index
2276 ,accounting_entry_status_code)
2277 );
2278
2279 l_count := SQL%ROWCOUNT;
2280 IF (C_LEVEL_EVENT >= g_log_level) THEN
2281 trace
2282 (p_msg => '# journal entry lines inserted into xla_ae_lines = '||to_char(l_count)
2283 ,p_level => C_LEVEL_EVENT
2284 ,p_module => v_module);
2285 END IF;
2286
2287 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2288
2289 trace
2290 (p_msg => 'return value. = '||TO_CHAR(l_count)
2291 ,p_level => C_LEVEL_PROCEDURE
2292 ,p_module => v_module);
2293
2294
2295 trace
2296 (p_msg => 'END of insert_lines'
2297 ,p_level => C_LEVEL_PROCEDURE
2298 ,p_module => v_module);
2299 END IF;
2300
2301 EXCEPTION
2302 WHEN xla_exceptions_pkg.application_exception THEN
2303 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2304 trace
2305 (p_msg => 'Error. = '||sqlerrm
2306 ,p_level => C_LEVEL_PROCEDURE
2307 ,p_module => v_module);
2308 END IF;
2309 RAISE;
2310 WHEN OTHERS THEN
2311 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2312 trace
2313 (p_msg => 'Error. = '||sqlerrm
2314 ,p_level => C_LEVEL_PROCEDURE
2315 ,p_module => v_module);
2316 END IF;
2317 xla_exceptions_pkg.raise_message
2318 (p_location => 'XLA_AE_JOURNAL_ENTRY_PKG.insert_lines');
2319 END insert_lines;
2320
2321
2322 PROCEDURE get_accounting_date(
2323 p_merge_date IN DATE
2324 ,p_primary_ledger_id IN NUMBER
2325 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
2326 ,p_array_ledger_category IN t_varchar30_array
2327 ,p_array_merge_option IN t_varchar30_array
2328 ,p_gl_date OUT NOCOPY t_date_array
2329 ,p_gl_period_name OUT NOCOPY t_varchar30_array
2330 ,p_entry_status OUT NOCOPY t_varchar1_array) IS
2331 v_function VARCHAR2(240);
2332 v_module VARCHAR2(240);
2333 v_gl_date date := null;
2334 v_gl_period_name VARCHAR2(30) := null;
2335 v_entry_status VARCHAR2(1) := 'F';
2336 BEGIN
2337 v_function := 'xla_third_party_merge.get_accounting_date';
2338 v_module := C_DEFAULT_MODULE||'.get_accounting_date';
2339 IF (C_LEVEL_PROCEDURE >= g_log_level)
2340 THEN
2341 trace( p_msg => 'BEGIN - ' || v_function
2342 , p_level => C_LEVEL_PROCEDURE
2343 , p_module => v_module);
2344 trace( p_msg => 'p_merge_date ' || to_char(p_merge_date)
2345 , p_level => C_LEVEL_PROCEDURE
2346 , p_module => v_module);
2347 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
2348
2349 FOR i in 1 .. p_array_merge_option.count LOOP
2350 IF(p_array_merge_option(i) = 'TRANSFER') THEN
2351 IF(p_array_ledger_category(i) in ('PRIMARY', 'SECONDARY')) THEN
2352 SELECT period_name, start_date, decode(closing_status, 'O', 'F', 'N', 'I', 'F', 'F')
2353 INTO p_gl_period_name(i), p_gl_date(i), p_entry_status(i)
2354 FROM gl_period_statuses
2355 WHERE ledger_id = p_array_ledger_id(i)
2356 AND application_id = 101
2357 AND end_date >= p_merge_date
2358 AND adjustment_period_flag = 'N' --Bug 12930775
2359 AND closing_status in ('O', 'F', 'N')
2360 AND start_date =
2361 (SELECT min(start_date)
2362 FROM gl_period_statuses
2363 WHERE ledger_id = p_array_ledger_id(i)
2364 AND application_id = 101
2365 AND end_date >= p_merge_date
2366 AND adjustment_period_flag = 'N' --Bug 12930775
2367 AND closing_status in ('O', 'F', 'N'));
2368 IF(p_merge_date > p_gl_date(i)) THEN
2369 p_gl_date(i) := p_merge_date;
2370 ELSIF(p_entry_status(i) = 'I') THEN
2371 RAISE NO_DATA_FOUND;
2372 END IF;
2373 IF(p_array_ledger_category(i) = 'PRIMARY') THEN
2374 v_gl_date := p_gl_date(i);
2375 v_gl_period_name :=p_gl_period_name(i);
2376 v_entry_status := p_entry_status(i);
2377 END IF;
2378 ELSE
2379 IF(v_gl_date is not null) THEN
2380 p_gl_date(i) := v_gl_date;
2381 p_gl_period_name(i) :=v_gl_period_name;
2382 p_entry_status(i) := v_entry_status;
2383 ELSE
2384 SELECT period_name, start_date, decode(closing_status, 'O', 'F', 'N', 'I', 'F', 'F')
2385 INTO p_gl_period_name(i), p_gl_date(i), p_entry_status(i)
2386 FROM gl_period_statuses
2387 WHERE ledger_id = p_array_ledger_id(i)
2388 AND application_id = 101
2389 AND end_date >= p_merge_date
2390 AND adjustment_period_flag = 'N' --Bug 12930775
2391 AND closing_status in ('O', 'F', 'N')
2392 AND start_date =
2393 (SELECT min(start_date)
2394 FROM gl_period_statuses
2395 WHERE ledger_id = p_array_ledger_id(i)
2396 AND application_id = 101
2397 AND end_date >= p_merge_date
2398 AND adjustment_period_flag = 'N' --Bug 12930775
2399 AND closing_status in ('O', 'F', 'N'));
2400 IF(p_merge_date > p_gl_date(i)) THEN
2401 p_gl_date(i) := p_merge_date;
2402 ELSIF(p_entry_status(i) = 'I') THEN
2403 RAISE NO_DATA_FOUND;
2404 END IF;
2405 v_gl_date := p_gl_date(i);
2406 v_gl_period_name :=p_gl_period_name(i);
2407 v_entry_status := p_entry_status(i);
2408 END IF;
2409 END IF;
2410 END IF;
2411 END LOOP;
2412
2413 IF (C_LEVEL_PROCEDURE >= g_log_level)
2414 THEN
2415 trace( p_msg => 'END - ' || v_function
2416 , p_level => C_LEVEL_PROCEDURE
2417 , p_module => v_module);
2418 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
2419
2420 EXCEPTION
2421 WHEN NO_DATA_FOUND THEN
2422 -- no accounting date is found
2423 raise;
2424 WHEN OTHERS THEN
2425 raise;
2426 END get_accounting_date;
2427
2428 PROCEDURE create_work_table(
2429 p_request_id IN NUMBER
2430 ,p_application_id IN NUMBER
2431 ,p_event_id IN NUMBER
2432 ,p_merge_date IN DATE
2433 ,p_merge_type IN VARCHAR2
2434 ,p_old_site_id IN NUMBER
2435 ,p_old_party_id IN NUMBER
2436 ,p_new_site_id IN NUMBER
2437 ,p_new_party_id IN NUMBER
2438 ,p_party_type IN VARCHAR2
2439 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
2440 ,p_array_merge_option IN t_varchar30_array
2441 ) is
2442 CURSOR c_lastRunningReq is
2443 SELECT xtw.request_id
2444 FROM XLA_TPM_WORKING_HDRS_T xtw
2445 , fnd_concurrent_requests fcr
2446 WHERE xtw.merge_event_id = p_event_id
2447 AND xtw.process_type_flag in ('B', 'R')
2448 AND xtw.request_id = fcr.request_id
2449 AND fcr.phase_code IN ('R','P','I');
2450 v_last_request_id NUMBER := null;
2451 v_function VARCHAR2(240);
2452 v_module VARCHAR2(240);
2453 begin
2454 v_function := 'xla_third_party_merge.create_work_table';
2455 v_module := C_DEFAULT_MODULE||'.create_work_table';
2456
2457 -- Log the function entry, the passed parameters and their values
2458 IF (C_LEVEL_PROCEDURE >= g_log_level)
2459 THEN
2460 trace( p_msg => 'BEGIN - ' || v_function
2461 , p_level => C_LEVEL_PROCEDURE
2462 , p_module => v_module);
2463 trace( p_msg => ', p_merge_type = ' || p_merge_type
2464 , p_level => C_LEVEL_PROCEDURE
2465 , p_module => v_module);
2466 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
2467
2468 open c_lastRunningReq;
2469 fetch c_lastRunningReq into v_last_request_id;
2470 close c_lastRunningReq;
2471
2472 IF(v_last_request_id is not null) THEN
2473 IF (C_LEVEL_STATEMENT>= g_log_level)
2474 THEN
2475 trace( p_msg => 'old request id is - ' || to_char(v_last_request_id)
2476 , p_level => C_LEVEL_STATEMENT
2477 , p_module => v_module);
2478 END IF;
2479 -- check if the request is still running, report error if it is
2480 raise LastRequestRunning;
2481 ELSE
2482 DELETE from XLA_TPM_WORKING_HDRS_T
2483 WHERE merge_event_id = p_event_id;
2484 COMMIT;
2485 END IF;
2486
2487 IF(p_merge_type = 'PARTIAL_MERGE') THEN
2488 FORALL i in 1..p_array_ledger_id.count
2489 INSERT INTO XLA_TPM_WORKING_HDRS_T
2490 ( request_id
2491 ,ae_header_id
2492 ,merge_event_id
2493 ,process_type_flag)
2494 SELECT
2495 p_request_id
2496 ,ae_header_id
2497 ,p_event_id
2498 ,'B'
2499 FROM xla_ae_headers aeh
2500 WHERE aeh.BALANCE_TYPE_CODE = 'A'
2501 AND aeh.LEDGER_ID = p_array_ledger_id(i)
2502 AND aeh.ACCOUNTING_DATE <= p_merge_date
2503 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
2504 AND 'TRANSFER' = p_array_merge_option(i)
2505 AND merge_event_id is null
2506 AND ae_header_id in
2507 (SELECT ael.ae_header_id
2508 FROM xla_ae_lines ael
2509 ,XLA_PARTIAL_MERGE_TXNS pmt
2510 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
2511 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
2512 = nvl(p_old_site_id, -1)
2513 and nvl(ael.party_type_code , p_party_type) = p_party_type
2514 and ael.currency_code <> 'STAT'
2515 AND ael.APPLICATION_ID = p_application_id
2516 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
2517 AND ael.control_balance_flag in ('P', 'Y')
2518 AND pmt.APPLICATION_ID = ael.application_id
2519 AND pmt.MERGE_EVENT_ID = p_event_id
2520 AND pmt.ENTITY_ID = aeh.ENTITY_ID);
2521 IF (C_LEVEL_STATEMENT>= g_log_level)
2522 THEN
2523 trace( p_msg => 'partial, balance, # inserted:'||to_char(SQL%ROWCOUNT)
2524 , p_level => C_LEVEL_STATEMENT
2525 , p_module => v_module);
2526 END IF;
2527
2528 commit;
2529
2530 FORALL i in 1..p_array_ledger_id.count
2531 INSERT INTO XLA_TPM_WORKING_HDRS_T
2532 ( request_id
2533 ,ae_header_id
2534 ,merge_event_id
2535 ,process_type_flag)
2536 SELECT
2537 p_request_id
2538 ,ae_header_id
2539 ,p_event_id
2540 ,'R'
2541 FROM xla_ae_headers aeh
2542 WHERE aeh.BALANCE_TYPE_CODE = 'A'
2543 AND aeh.APPLICATION_ID = p_application_id
2544 AND aeh.LEDGER_ID = p_array_ledger_id(i)
2545 AND aeh.ACCOUNTING_DATE > p_merge_date
2546 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
2547 AND 'TRANSFER' = p_array_merge_option(i)
2548 AND merge_event_id is null
2549 AND ae_header_id in
2550 (SELECT ael.ae_header_id
2551 FROM xla_ae_lines ael
2552 ,XLA_PARTIAL_MERGE_TXNS pmt
2553 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
2554 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
2555 = nvl(p_old_site_id, -1)
2556 and nvl(ael.party_type_code , p_party_type) = p_party_type
2557 and ael.currency_code <> 'STAT'
2558 AND ael.APPLICATION_ID = p_application_id
2559 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
2560 AND pmt.APPLICATION_ID = ael.application_id
2561 AND pmt.MERGE_EVENT_ID = p_event_id
2562 AND pmt.ENTITY_ID = aeh.ENTITY_ID);
2563 IF (C_LEVEL_STATEMENT>= g_log_level)
2564 THEN
2565 trace( p_msg => 'partial, reverse and rebooking# inserted:'||to_char(SQL%ROWCOUNT)
2566 , p_level => C_LEVEL_STATEMENT
2567 , p_module => v_module);
2568 END IF;
2569 commit;
2570 ELSE
2571 FORALL i in 1..p_array_ledger_id.count
2572 INSERT INTO XLA_TPM_WORKING_HDRS_T
2573 ( request_id
2574 ,ae_header_id
2575 ,merge_event_id
2576 ,process_type_flag)
2577 SELECT
2578 p_request_id
2579 ,ae_header_id
2580 ,p_event_id
2581 ,'B'
2582 FROM xla_ae_headers aeh
2583 WHERE aeh.BALANCE_TYPE_CODE = 'A'
2584 AND aeh.APPLICATION_ID = p_application_id
2585 AND aeh.LEDGER_ID = p_array_ledger_id(i)
2586 AND aeh.ACCOUNTING_DATE <= p_merge_date
2587 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
2588 AND 'TRANSFER' = p_array_merge_option(i)
2589 AND merge_event_id is null
2590 AND ae_header_id in
2591 (SELECT ael.ae_header_id
2592 FROM xla_ae_lines ael
2593 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
2594 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
2595 = nvl(p_old_site_id, -1)
2596 and nvl(ael.party_type_code , p_party_type) = p_party_type
2597 and ael.currency_code <> 'STAT'
2598 AND ael.APPLICATION_ID = p_application_id
2599 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
2600 AND ael.control_balance_flag in ('P', 'Y'));
2601 IF (C_LEVEL_STATEMENT>= g_log_level)
2602 THEN
2603 trace( p_msg => 'full, balance transfer # inserted:'||to_char(SQL%ROWCOUNT)
2604 , p_level => C_LEVEL_STATEMENT
2605 , p_module => v_module);
2606 END IF;
2607 commit;
2608
2609 FORALL i in 1..p_array_ledger_id.count
2610 INSERT INTO XLA_TPM_WORKING_HDRS_T
2611 ( request_id
2612 ,ae_header_id
2613 ,merge_event_id
2614 ,process_type_flag)
2615 SELECT
2616 p_request_id
2617 ,ae_header_id
2618 ,p_event_id
2619 ,'R'
2620 FROM xla_ae_headers aeh
2621 WHERE aeh.BALANCE_TYPE_CODE = 'A'
2622 AND aeh.APPLICATION_ID = p_application_id
2623 AND aeh.LEDGER_ID = p_array_ledger_id(i)
2624 AND aeh.ACCOUNTING_DATE > p_merge_date
2625 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE IN ('F','N')
2626 AND 'TRANSFER' = p_array_merge_option(i)
2627 AND merge_event_id is null
2628 AND ae_header_id in
2629 (SELECT ael.ae_header_id
2630 FROM xla_ae_lines ael
2631 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
2632 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
2633 = nvl(p_old_site_id, -1)
2634 and nvl(ael.party_type_code , p_party_type) = p_party_type
2635 and ael.currency_code <> 'STAT'
2636 AND ael.APPLICATION_ID = p_application_id
2637 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID);
2638 IF (C_LEVEL_STATEMENT>= g_log_level)
2639 THEN
2640 trace( p_msg => 'full, reverse and rebooking # inserted:'||to_char(SQL%ROWCOUNT)
2641 , p_level => C_LEVEL_STATEMENT
2642 , p_module => v_module);
2643 END IF;
2644 commit;
2645 END IF;
2646
2647 IF (C_LEVEL_PROCEDURE >= g_log_level)
2648 THEN
2649 trace( p_msg => 'end- ' || v_function
2650 , p_level => C_LEVEL_PROCEDURE
2651 , p_module => v_module);
2652 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
2653 END create_work_table;
2654
2655 FUNCTION create_balance_transfer_aes(
2656 p_application_id IN NUMBER
2657 ,p_accounting_mode IN VARCHAR2
2658 ,p_event_id IN NUMBER
2659 ,p_entity_id IN NUMBER
2660 ,p_event_ledger_id IN NUMBER
2661 ,p_merge_date IN DATE
2662 ,p_merge_type IN VARCHAR2
2663 ,p_old_site_id IN NUMBER
2664 ,p_old_party_id IN NUMBER
2665 ,p_new_site_id IN NUMBER
2666 ,p_new_party_id IN NUMBER
2667 ,p_party_type IN VARCHAR2
2668 ,p_balance_desc IN VARCHAR2
2669 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
2670 ,p_array_ledger_category IN t_varchar30_array
2671 ,p_array_reversal_option IN t_varchar30_array
2672 ,p_array_merge_option IN t_varchar30_array
2673 ,p_array_submit_transfer IN t_varchar1_array) RETURN NUMBER is
2674
2675 v_query varchar2(20000);
2676 v_function VARCHAR2(240);
2677 v_module VARCHAR2(240);
2678 v_gl_date t_date_array;
2679 v_gl_period_name t_varchar30_array;
2680 v_gl_entry_status t_varchar1_array;
2681 v_row_count INTEGER:=0;
2682 v_total_row_count INTEGER:=0;
2683 v_gl_date_flag VARCHAR2(1) :='N';
2684 begin
2685 v_function := 'xla_third_party_merge.create_balance_transfer_aes';
2686 v_module := C_DEFAULT_MODULE||'.create_balance_transfer_aes';
2687
2688 -- Log the function entry, the passed parameters and their values
2689 IF (C_LEVEL_PROCEDURE >= g_log_level)
2690 THEN
2691 trace( p_msg => 'BEGIN - ' || v_function
2692 , p_level => C_LEVEL_PROCEDURE
2693 , p_module => v_module);
2694 trace( p_msg => 'p_applicaiton_id = ' || p_application_id
2695 || ', p_event_id = ' || p_event_id
2696 , p_level => C_LEVEL_PROCEDURE
2697 , p_module => v_module);
2698 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
2699
2700 IF (C_LEVEL_STATEMENT>= g_log_level)
2701 THEN
2702 trace( p_msg => 'before inserting reverse sql'
2703 , p_level => C_LEVEL_STATEMENT
2704 , p_module => v_module);
2705 END IF;
2706
2707 FORALL i in 1..p_array_ledger_id.count
2708 INSERT INTO xla_ae_lines_gt
2709 (ae_header_id
2710 ,temp_line_num
2711 ,event_id
2712 ,ref_ae_header_id
2713 ,ref_ae_line_num
2714 ,ref_temp_line_num
2715 ,ref_event_id
2716 ,balance_type_code
2717 ,ledger_id
2718 ,accounting_class_code
2719 ,event_class_code
2720 ,event_type_code
2721 ,line_definition_owner_code
2722 ,line_definition_code
2723 ,accounting_line_type_code
2724 ,accounting_line_code
2725 ,code_combination_status_code
2726 ,code_combination_id
2727 ,description
2728 ,gl_transfer_mode_code
2729 ,merge_duplicate_code
2730 ,unrounded_entered_dr
2731 ,unrounded_entered_cr
2732 ,unrounded_accounted_dr
2733 ,unrounded_accounted_cr
2734 ,calculate_acctd_amts_flag
2735 ,calculate_g_l_amts_flag
2736 ,gain_or_loss_flag
2737 ,rounding_class_code
2738 ,document_rounding_level
2739 ,doc_rounding_acctd_amt
2740 ,doc_rounding_entered_amt
2741 ,entered_currency_mau
2742 ,currency_code
2743 ,currency_conversion_date
2744 ,currency_conversion_rate
2745 ,currency_conversion_type
2746 ,statistical_amount
2747 ,party_id
2748 ,party_site_id
2749 ,party_type_code
2750 ,source_distribution_type
2751 ,ussgl_transaction_code
2752 ,jgzz_recon_ref
2753 ,analytical_balance_flag
2754 ,reversal_code
2755 ,accounting_entry_status_code
2756 ,inherit_desc_flag
2757 ,header_num -- 5100860 assign value to avoid using function index
2758 ,alt_segment1
2759 ,encumbrance_type_id)
2760 SELECT
2761 p_event_id
2762 ,rownum
2763 ,p_event_id
2764 ,ael.ae_header_id
2765 ,ael.ae_line_num
2766 ,xdl.temp_line_num
2767 ,xdl.event_id
2768 ,aeh.balance_type_code
2769 ,aeh.ledger_id
2770 ,ael.accounting_class_code
2771 ,'MERGE' --xdl.event_class_code
2772 ,p_merge_type
2773 ,null --xdl.line_definition_owner_code
2774 ,xdl.line_definition_code
2775 ,xdl.accounting_line_type_code
2776 ,xdl.accounting_line_code
2777 ,'CREATED'-- code combination id status
2778 ,ael.code_combination_id
2779 ,p_balance_desc
2780 ,'N' --gl_transfer_mode_code
2781 ,xdl.merge_duplicate_code
2782 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_cr, 0 - xdl.unrounded_entered_dr)
2783 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_dr, 0 - xdl.unrounded_entered_cr)
2784 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_cr, 0 - xdl.unrounded_accounted_dr)
2785 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_dr, 0 - xdl.unrounded_accounted_cr)
2786 ,xdl.calculate_acctd_amts_flag
2787 ,xdl.calculate_g_l_amts_flag
2788 ,ael.gain_or_loss_flag
2789 ,xdl.rounding_class_code
2790 ,xdl.document_rounding_level
2791 ,xdl.doc_rounding_acctd_amt
2792 ,xdl.doc_rounding_entered_amt
2793 ,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
2794 ,ael.currency_code
2795 ,ael.currency_conversion_date
2796 ,ael.currency_conversion_rate
2797 ,ael.currency_conversion_type
2798 ,ael.statistical_amount
2799 ,nvl(ael.merge_party_id, ael.party_id)
2800 ,nvl(ael.merge_party_site_id, ael.party_site_id)
2801 ,ael.party_type_code
2802 ,xdl.source_distribution_type
2803 ,ael.ussgl_transaction_code
2804 ,ael.jgzz_recon_ref
2805 ,ael.analytical_balance_flag
2806 ,'REVERSE_BALANCE'
2807 ,'F'
2808 ,'N'
2809 ,0 -- 5100860 assign value to avoid using function index
2810 ,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
2811 ,ael.encumbrance_type_id
2812 FROM
2813 xla_ae_lines ael
2814 ,xla_ae_headers aeh
2815 ,xla_distribution_links xdl
2816 ,fnd_currencies fcu
2817 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
2818 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
2819 = nvl(p_old_site_id, -1)
2820 and nvl(ael.party_type_code , p_party_type) = p_party_type
2821 and ael.currency_code <> 'STAT'
2822 and ael.currency_code = fcu.currency_code
2823 AND aeh.ae_header_id = xdl.ae_header_id
2824 AND ael.ae_line_num = xdl.ae_line_num
2825 AND ael.APPLICATION_ID = p_application_id
2826 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
2827 AND aeh.BALANCE_TYPE_CODE = 'A'
2828 AND aeh.APPLICATION_ID = ael.application_id
2829 AND aeh.LEDGER_ID = p_array_ledger_id(i)
2830 AND aeh.ACCOUNTING_DATE <= p_merge_date
2831 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
2832 AND aeh.ae_header_id in (
2833 SELECT ae_header_id
2834 FROM XLA_TPM_WORKING_HDRS_T xtwh
2835 WHERE xtwh.merge_event_id = p_event_id
2836 AND xtwh.process_type_flag= 'B'
2837 AND rownum <= C_WORK_UNIT)
2838 AND aeh.merge_event_id is null
2839 /*
2840 AND NOT EXISTS (
2841 SELECT 1
2842 FROM xla_distribution_links
2843 WHERE ref_ae_header_id = xdl.ae_header_id
2844 AND ref_temp_line_num = xdl.temp_line_num
2845 -- means it is a third party merge line
2846 And ref_ae_header_id <>ae_header_id
2847 )
2848 */
2849 AND ael.control_balance_flag in ('Y', 'P');
2850
2851 v_row_count :=SQL%ROWCOUNT;
2852 IF (C_LEVEL_STATEMENT>= g_log_level)
2853 THEN
2854 trace( p_msg => '# inserted:'||to_char(v_row_count)
2855 , p_level => C_LEVEL_STATEMENT
2856 , p_module => v_module);
2857 trace( p_msg => 'before inserting transfer sql'
2858 , p_level => C_LEVEL_STATEMENT
2859 , p_module => v_module);
2860 END IF;
2861
2862 IF(v_row_count = 0) THEN
2863 IF (C_LEVEL_PROCEDURE >= g_log_level)
2864 THEN
2865 trace( p_msg => 'end- ' || v_function||' return 0'
2866 , p_level => C_LEVEL_PROCEDURE
2867 , p_module => v_module);
2868 END IF;
2869 return 0;
2870 END IF;
2871
2872
2873 FORALL i in 1..p_array_ledger_id.count
2874 INSERT INTO xla_ae_lines_gt
2875 (ae_header_id
2876 ,temp_line_num
2877 ,event_id
2878 ,ref_ae_header_id
2879 ,ref_ae_line_num
2880 ,ref_temp_line_num
2881 ,ref_event_id
2882 ,balance_type_code
2883 ,ledger_id
2884 ,accounting_class_code
2885 ,event_class_code
2886 ,event_type_code
2887 ,line_definition_owner_code
2888 ,line_definition_code
2889 ,accounting_line_type_code
2890 ,accounting_line_code
2891 ,code_combination_status_code
2892 ,code_combination_id
2893 ,description
2894 ,gl_transfer_mode_code
2895 ,merge_duplicate_code
2896 ,unrounded_entered_dr
2897 ,unrounded_entered_cr
2898 ,unrounded_accounted_dr
2899 ,unrounded_accounted_cr
2900 ,calculate_acctd_amts_flag
2901 ,calculate_g_l_amts_flag
2902 ,gain_or_loss_flag
2903 ,rounding_class_code
2904 ,document_rounding_level
2905 ,doc_rounding_acctd_amt
2906 ,doc_rounding_entered_amt
2907 ,entered_currency_mau
2908 ,currency_code
2909 ,currency_conversion_date
2910 ,currency_conversion_rate
2911 ,currency_conversion_type
2912 ,statistical_amount
2913 ,party_id
2914 ,party_site_id
2915 ,party_type_code
2916 ,source_distribution_type
2917 ,ussgl_transaction_code
2918 ,jgzz_recon_ref
2919 ,analytical_balance_flag
2920 ,reversal_code
2921 ,accounting_entry_status_code
2922 ,inherit_desc_flag
2923 ,header_num -- 5100860 assign value to avoid using function index
2924 ,alt_segment1
2925 ,encumbrance_type_id)
2926 SELECT
2927 p_event_id
2928 ,v_row_count+rownum
2929 ,p_event_id
2930 ,ael.ae_header_id
2931 ,ael.ae_line_num
2932 ,xdl.temp_line_num
2933 ,xdl.event_id
2934 ,aeh.balance_type_code
2935 ,aeh.ledger_id
2936 ,ael.accounting_class_code
2937 ,'MERGE' --xdl.event_class_code
2938 ,p_merge_type
2939 ,null --xdl.line_definition_owner_code
2940 ,xdl.line_definition_code
2941 ,xdl.accounting_line_type_code
2942 ,xdl.accounting_line_code
2943 ,'CREATED'-- code combination id status
2944 ,ael.code_combination_id
2945 ,p_balance_desc
2946 ,'N' --gl_transfer_mode_code
2947 ,xdl.merge_duplicate_code
2948 ,xdl.unrounded_entered_dr
2949 ,xdl.unrounded_entered_cr
2950 ,xdl.unrounded_accounted_dr
2951 ,xdl.unrounded_accounted_cr
2952 ,xdl.calculate_acctd_amts_flag
2953 ,xdl.calculate_g_l_amts_flag
2954 ,ael.gain_or_loss_flag
2955 ,xdl.rounding_class_code
2956 ,xdl.document_rounding_level
2957 ,xdl.doc_rounding_acctd_amt
2958 ,xdl.doc_rounding_entered_amt
2959 ,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
2960 ,ael.currency_code
2961 ,ael.currency_conversion_date
2962 ,ael.currency_conversion_rate
2963 ,ael.currency_conversion_type
2964 ,ael.statistical_amount
2965 ,p_new_party_id
2966 ,p_new_site_id
2967 ,ael.party_type_code
2968 ,xdl.source_distribution_type
2969 ,ael.ussgl_transaction_code
2970 ,ael.jgzz_recon_ref
2971 ,ael.analytical_balance_flag
2972 ,'TRANSFER_BALANCE'
2973 ,'F'
2974 ,'N'
2975 ,0 -- 5100860 assign value to avoid using function index
2976 ,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
2977 ,ael.encumbrance_type_id
2978 FROM
2979 xla_ae_lines ael
2980 ,xla_ae_headers aeh
2981 ,xla_distribution_links xdl
2982 ,fnd_currencies fcu
2983 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
2984 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
2985 = nvl(p_old_site_id, -1)
2986 and nvl(ael.party_type_code , p_party_type) = p_party_type
2987 and ael.currency_code <> 'STAT'
2988 and ael.currency_code = fcu.currency_code
2989 AND aeh.ae_header_id = xdl.ae_header_id
2990 AND ael.ae_line_num = xdl.ae_line_num
2991 AND ael.APPLICATION_ID = p_application_id
2992 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
2993 AND aeh.BALANCE_TYPE_CODE = 'A'
2994 AND aeh.APPLICATION_ID = ael.application_id
2995 AND aeh.LEDGER_ID = p_array_ledger_id(i)
2996 AND aeh.ACCOUNTING_DATE <= p_merge_date
2997 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
2998 AND aeh.ae_header_id in (
2999 SELECT ae_header_id
3000 FROM XLA_TPM_WORKING_HDRS_T xtwh
3001 WHERE xtwh.merge_event_id = p_event_id
3002 AND xtwh.process_type_flag= 'B'
3003 AND rownum <= C_WORK_UNIT)
3004 AND aeh.merge_event_id is null
3005 /*
3006 AND NOT EXISTS (
3007 SELECT 1
3008 FROM xla_distribution_links
3009 WHERE ref_ae_header_id = xdl.ae_header_id
3010 AND ref_temp_line_num = xdl.temp_line_num
3011 -- means it is a third party merge line
3012 And ref_ae_header_id <>ae_header_id
3013 )
3014 */
3015 AND ael.control_balance_flag in ('P', 'Y');
3016
3017 v_row_count :=v_row_count + SQL%ROWCOUNT;
3018 IF (C_LEVEL_STATEMENT>= g_log_level)
3019 THEN
3020 trace( p_msg => '# total rows inserted:'||to_char(v_row_count)
3021 , p_level => C_LEVEL_STATEMENT
3022 , p_module => v_module);
3023 END IF;
3024
3025 IF(v_row_count> 0 AND v_gl_date_flag = 'N') THEN
3026 IF (C_LEVEL_STATEMENT>= g_log_level)
3027 THEN
3028 trace( p_msg => 'calling the get_accounting_date api '
3029 , p_level => C_LEVEL_STATEMENT
3030 , p_module => v_module);
3031 END IF;
3032 BEGIN
3033 get_accounting_date(
3034 p_merge_date => p_merge_date
3035 ,p_primary_ledger_id => p_event_ledger_id
3036 ,p_array_ledger_id => p_array_ledger_id
3037 ,p_array_ledger_category => p_array_ledger_category
3038 ,p_array_merge_option => p_array_merge_option
3039 ,p_gl_date => v_gl_date
3040 ,p_gl_period_name => v_gl_period_name
3041 ,p_entry_status => v_gl_entry_status);
3042 v_gl_date_flag := 'Y';
3043 EXCEPTION
3044 WHEN NO_DATA_FOUND THEN
3045 raise NoAccountingDateError;
3046 END;
3047 IF (C_LEVEL_STATEMENT>= g_log_level)
3048 THEN
3049 trace( p_msg => 'after calling the get_accounting_date api '
3050 , p_level => C_LEVEL_STATEMENT
3051 , p_module => v_module);
3052 END IF;
3053 END IF;
3054
3055 IF(v_row_count>0) THEN
3056 IF (C_LEVEL_STATEMENT>= g_log_level)
3057 THEN
3058 trace( p_msg => 'before inserting header'
3059 , p_level => C_LEVEL_STATEMENT
3060 , p_module => v_module);
3061 END IF;
3062
3063 FORALL i in 1 .. p_array_ledger_id.count
3064 INSERT INTO xla_ae_headers_gt
3065 ( ae_header_id
3066 , accounting_entry_status_code
3067 , accounting_entry_type_code
3068 , ledger_id
3069 , entity_id
3070 , event_id
3071 , event_type_code
3072 , accounting_date
3073 , period_name
3074 , description
3075 , budget_version_id -- use this field to save merge_event_id
3076 , balance_type_code
3077 , amb_context_code
3078 , gl_transfer_status_code
3079 , je_category_name
3080 )
3081 select xla_ae_headers_s.nextval
3082 ,decode(p_accounting_mode, 'D', 'D', v_gl_entry_status(i))
3083 ,'MERGE'
3084 ,p_array_ledger_id(i)
3085 ,p_entity_id
3086 ,p_event_id
3087 ,p_merge_type
3088 ,v_gl_date(i)
3089 ,v_gl_period_name(i)
3090 ,p_balance_desc
3091 ,p_event_id
3092 ,'A'
3093 ,null
3094 ,'N'
3095 ,'Other'
3096 from dual
3097 where p_array_merge_option(i) = 'TRANSFER'
3098 AND p_array_ledger_id(i) in
3099 (select ledger_id from xla_ae_lines_gt);
3100
3101 IF (C_LEVEL_STATEMENT>= g_log_level)
3102 THEN
3103 trace( p_msg => 'Header inserted'
3104 , p_level => C_LEVEL_STATEMENT
3105 , p_module => v_module);
3106 END IF;
3107
3108 UPDATE xla_ae_lines_gt xal
3109 set (ae_header_id, accounting_date) =(
3110 select ae_header_id, accounting_date
3111 from xla_ae_headers_gt xah
3112 where xah.ledger_id = xal.ledger_id);
3113 END IF;
3114
3115 -- this is not needed since it is called in the caller procedure.
3116 -- process_accounting_mapping(p_application_id => p_application_id);
3117
3118 IF (C_LEVEL_PROCEDURE >= g_log_level)
3119 THEN
3120 trace( p_msg => 'end- ' || v_function || ' returning :'||to_char(v_row_count)
3121 , p_level => C_LEVEL_PROCEDURE
3122 , p_module => v_module);
3123 END IF;
3124 return v_row_count;
3125 END create_balance_transfer_aes;
3126
3127
3128 FUNCTION create_reverse_rebooking_aes(
3129 p_application_id IN NUMBER
3130 ,p_accounting_mode IN VARCHAR2
3131 ,p_event_id IN NUMBER
3132 ,p_entity_id IN NUMBER
3133 ,p_event_ledger_id IN NUMBER
3134 ,p_merge_date IN DATE
3135 ,p_merge_type IN VARCHAR2
3136 ,p_old_site_id IN NUMBER
3137 ,p_old_party_id IN NUMBER
3138 ,p_new_site_id IN NUMBER
3139 ,p_new_party_id IN NUMBER
3140 ,p_party_type IN VARCHAR2
3141 ,p_reverse_line_desc IN VARCHAR2
3142 ,p_rebooking_line_desc IN VARCHAR2
3143 ,p_reverse_header_desc IN VARCHAR2
3144 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
3145 ,p_array_ledger_category IN t_varchar30_array
3146 ,p_array_reversal_option IN t_varchar30_array
3147 ,p_array_merge_option IN t_varchar30_array
3148 ,p_array_submit_transfer IN t_varchar1_array) RETURN NUMBER is
3149
3150 v_query varchar2(20000);
3151 v_function VARCHAR2(240);
3152 v_module VARCHAR2(240);
3153 v_gl_date t_date_array;
3154 v_gl_period_name t_varchar30_array;
3155 v_row_count INTEGER:=0;
3156 v_total_row_count INTEGER:=0;
3157 v_gl_date_flag VARCHAR2(1) :='N';
3158 begin
3159 v_function := 'xla_third_party_merge.create_reverse_rebooking_aes';
3160 v_module := C_DEFAULT_MODULE||'.create_reverse_rebooking_aes';
3161
3162 -- Log the function entry, the passed parameters and their values
3163 IF (C_LEVEL_PROCEDURE >= g_log_level)
3164 THEN
3165 trace( p_msg => 'BEGIN - ' || v_function
3166 , p_level => C_LEVEL_PROCEDURE
3167 , p_module => v_module);
3168 trace( p_msg => 'p_applicaiton_id = ' || p_application_id
3169 || ', p_event_id = ' || p_event_id
3170 , p_level => C_LEVEL_PROCEDURE
3171 , p_module => v_module);
3172 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
3173
3174 IF (C_LEVEL_STATEMENT>= g_log_level)
3175 THEN
3176 trace( p_msg => 'before inserting reverse sql'
3177 , p_level => C_LEVEL_STATEMENT
3178 , p_module => v_module);
3179 END IF;
3180
3181 FORALL i in 1..p_array_ledger_id.count
3182 INSERT INTO xla_ae_lines_gt
3183 (ae_header_id
3184 ,temp_line_num
3185 ,event_id
3186 ,ref_ae_header_id
3187 ,ref_ae_line_num
3188 ,ref_temp_line_num
3189 ,ref_event_id
3190 ,balance_type_code
3191 ,ledger_id
3192 ,accounting_class_code
3193 ,event_class_code
3194 ,event_type_code
3195 ,line_definition_owner_code
3196 ,line_definition_code
3197 ,accounting_line_type_code
3198 ,accounting_line_code
3199 ,code_combination_status_code
3200 ,code_combination_id
3201 ,description
3202 ,gl_transfer_mode_code
3203 ,merge_duplicate_code
3204 ,unrounded_entered_dr
3205 ,unrounded_entered_cr
3206 ,unrounded_accounted_dr
3207 ,unrounded_accounted_cr
3208 ,calculate_acctd_amts_flag
3209 ,calculate_g_l_amts_flag
3210 ,gain_or_loss_flag
3211 ,rounding_class_code
3212 ,document_rounding_level
3213 ,doc_rounding_acctd_amt
3214 ,doc_rounding_entered_amt
3215 ,entered_currency_mau
3216 ,currency_code
3217 ,currency_conversion_date
3218 ,currency_conversion_rate
3219 ,currency_conversion_type
3220 ,statistical_amount
3221 ,party_id
3222 ,party_site_id
3223 ,party_type_code
3224 ,ussgl_transaction_code
3225 ,jgzz_recon_ref
3226 ,source_distribution_id_char_1
3227 ,source_distribution_id_char_2
3228 ,source_distribution_id_char_3
3229 ,source_distribution_id_char_4
3230 ,source_distribution_id_char_5
3231 ,source_distribution_id_num_1
3232 ,source_distribution_id_num_2
3233 ,source_distribution_id_num_3
3234 ,source_distribution_id_num_4
3235 ,source_distribution_id_num_5
3236 ,source_distribution_type
3237 ,analytical_balance_flag
3238 ,reversal_code
3239 ,accounting_entry_status_code
3240 ,inherit_desc_flag
3241 ,header_num -- 5100860 assign value to avoid using function index
3242 ,alt_segment1
3243 ,encumbrance_type_id)
3244 SELECT
3245 p_event_id
3246 ,rank() over(partition by xdl.ae_header_id order by xdl.temp_line_num)
3247 ,aeh.event_id
3248 ,ael.ae_header_id
3249 ,ael.ae_line_num
3250 ,xdl.temp_line_num
3251 ,xdl.event_id
3252 ,aeh.balance_type_code
3253 ,aeh.ledger_id
3254 ,ael.accounting_class_code
3255 ,xdl.event_class_code
3256 ,aeh.event_type_code --'MERGE' --merge_event_type_code
3257 ,null --xdl.line_definition_owner_code
3258 ,xdl.line_definition_code
3259 ,xdl.accounting_line_type_code
3260 ,xdl.accounting_line_code
3261 ,'CREATED'-- code combination id status
3262 ,ael.code_combination_id
3263 ,ael.description || p_reverse_line_desc
3264 ,'N' --gl_transfer_mode_code
3265 ,xdl.merge_duplicate_code
3266 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_cr, 0 - xdl.unrounded_entered_dr)
3267 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_entered_dr, 0 - xdl.unrounded_entered_cr)
3268 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_cr, 0 - xdl.unrounded_accounted_dr)
3269 ,decode(p_array_reversal_option(i), 'SIDE', xdl.unrounded_accounted_dr, 0 - xdl.unrounded_accounted_cr)
3270 ,xdl.calculate_acctd_amts_flag
3271 ,xdl.calculate_g_l_amts_flag
3272 ,ael.gain_or_loss_flag
3273 ,xdl.rounding_class_code
3274 ,xdl.document_rounding_level
3275 ,xdl.doc_rounding_acctd_amt
3276 ,xdl.doc_rounding_entered_amt
3277 ,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
3278 ,ael.currency_code
3279 ,ael.currency_conversion_date
3280 ,ael.currency_conversion_rate
3281 ,ael.currency_conversion_type
3282 ,ael.statistical_amount
3283 ,nvl(ael.merge_party_id, ael.party_id)
3284 ,nvl(ael.merge_party_site_id, ael.party_site_id)
3285 ,ael.party_type_code
3286 ,ael.ussgl_transaction_code
3287 ,ael.jgzz_recon_ref
3288 ,xdl.source_distribution_id_char_1
3289 ,xdl.source_distribution_id_char_2
3290 ,xdl.source_distribution_id_char_3
3291 ,xdl.source_distribution_id_char_4
3292 ,xdl.source_distribution_id_char_5
3293 ,xdl.source_distribution_id_num_1
3294 ,xdl.source_distribution_id_num_2
3295 ,xdl.source_distribution_id_num_3
3296 ,xdl.source_distribution_id_num_4
3297 ,xdl.source_distribution_id_num_5
3298 ,xdl.source_distribution_type
3299 ,ael.analytical_balance_flag
3300 ,'REVERSE'
3301 ,'F'
3302 ,'N'
3303 ,0 -- 5100860 assign value to avoid using function index
3304 ,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
3305 ,ael.encumbrance_type_id
3306 FROM
3307 xla_ae_lines ael
3308 ,xla_ae_headers aeh
3309 ,xla_distribution_links xdl
3310 ,fnd_currencies fcu
3311 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
3312 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
3313 = nvl(p_old_site_id, -1)
3314 and nvl(ael.party_type_code , p_party_type) = p_party_type
3315 and ael.currency_code <> 'STAT'
3316 and ael.currency_code = fcu.currency_code
3317 AND aeh.ae_header_id = xdl.ae_header_id
3318 AND ael.ae_line_num = xdl.ae_line_num
3319 AND ael.APPLICATION_ID = p_application_id
3320 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
3321 AND aeh.BALANCE_TYPE_CODE = 'A'
3322 AND aeh.APPLICATION_ID = ael.application_id
3323 AND aeh.LEDGER_ID = p_array_ledger_id(i)
3324 AND aeh.ACCOUNTING_DATE > p_merge_date
3325 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
3326 AND aeh.ae_header_id in (
3327 SELECT ae_header_id
3328 FROM XLA_TPM_WORKING_HDRS_T xtwh
3329 WHERE xtwh.merge_event_id = p_event_id
3330 AND xtwh.process_type_flag= 'R'
3331 AND rownum <= C_WORK_UNIT)
3332 AND aeh.merge_event_id is null
3333 /*
3334 AND NOT EXISTS (
3335 SELECT 1
3336 FROM xla_distribution_links
3337 WHERE ref_ae_header_id = xdl.ae_header_id
3338 AND ref_temp_line_num = xdl.temp_line_num
3339 -- means it is a third party merge line
3340 And ref_ae_header_id <>ae_header_id
3341 )
3342 */
3343 ;
3344
3345 v_row_count :=SQL%ROWCOUNT;
3346 IF (C_LEVEL_STATEMENT>= g_log_level)
3347 THEN
3348 trace( p_msg => '# inserted:'||to_char(v_row_count)
3349 , p_level => C_LEVEL_STATEMENT
3350 , p_module => v_module);
3351 trace( p_msg => 'before inserting rebooking sql'
3352 , p_level => C_LEVEL_STATEMENT
3353 , p_module => v_module);
3354 END IF;
3355
3356 IF(v_row_count = 0) THEN
3357 IF (C_LEVEL_PROCEDURE >= g_log_level)
3358 THEN
3359 trace( p_msg => 'end- ' || v_function||' return 0'
3360 , p_level => C_LEVEL_PROCEDURE
3361 , p_module => v_module);
3362 END IF;
3363 return 0;
3364 END IF;
3365
3366 FORALL i in 1..p_array_ledger_id.count
3367 INSERT INTO xla_ae_lines_gt
3368 (ae_header_id
3369 ,temp_line_num
3370 ,event_id
3371 ,ref_ae_header_id
3372 ,ref_ae_line_num
3373 ,ref_temp_line_num
3374 ,ref_event_id
3375 ,balance_type_code
3376 ,ledger_id
3377 ,accounting_class_code
3378 ,event_class_code
3379 ,event_type_code
3380 ,line_definition_owner_code
3381 ,line_definition_code
3382 ,accounting_line_type_code
3383 ,accounting_line_code
3384 ,code_combination_status_code
3385 ,code_combination_id
3386 ,description
3387 ,gl_transfer_mode_code
3388 ,merge_duplicate_code
3389 ,unrounded_entered_dr
3390 ,unrounded_entered_cr
3391 ,unrounded_accounted_dr
3392 ,unrounded_accounted_cr
3393 ,calculate_acctd_amts_flag
3394 ,calculate_g_l_amts_flag
3395 ,gain_or_loss_flag
3396 ,rounding_class_code
3397 ,document_rounding_level
3398 ,doc_rounding_acctd_amt
3399 ,doc_rounding_entered_amt
3400 ,entered_currency_mau
3401 ,currency_code
3402 ,currency_conversion_date
3403 ,currency_conversion_rate
3404 ,currency_conversion_type
3405 ,statistical_amount
3406 ,party_id
3407 ,party_site_id
3408 ,party_type_code
3409 ,ussgl_transaction_code
3410 ,jgzz_recon_ref
3411 ,source_distribution_id_char_1
3412 ,source_distribution_id_char_2
3413 ,source_distribution_id_char_3
3414 ,source_distribution_id_char_4
3415 ,source_distribution_id_char_5
3416 ,source_distribution_id_num_1
3417 ,source_distribution_id_num_2
3418 ,source_distribution_id_num_3
3419 ,source_distribution_id_num_4
3420 ,source_distribution_id_num_5
3421 ,source_distribution_type
3422 ,analytical_balance_flag
3423 ,reversal_code
3424 ,accounting_entry_status_code
3425 ,inherit_desc_flag
3426 ,header_num -- 5100860 assign value to avoid using function index
3427 ,alt_segment1
3428 ,encumbrance_type_id)
3429 SELECT
3430 p_event_id
3431 ,count(*) over(partition by xdl.ae_header_id) + rank() over(partition by xdl.ae_header_id order by xdl.temp_line_num)
3432 -- ,xdl.temp_line_num
3433 ,aeh.event_id
3434 ,ael.ae_header_id
3435 ,ael.ae_line_num
3436 ,xdl.temp_line_num
3437 ,xdl.event_id
3438 ,aeh.balance_type_code
3439 ,aeh.ledger_id
3440 ,ael.accounting_class_code
3441 ,xdl.event_class_code
3442 ,aeh.event_type_code --'MERGE' --merge_event_type_code
3443 ,null --xdl.line_definition_owner_code
3444 ,xdl.line_definition_code
3445 ,xdl.accounting_line_type_code
3446 ,xdl.accounting_line_code
3447 ,'CREATED'-- code combination id status
3448 ,ael.code_combination_id
3449 ,ael.description || p_rebooking_line_desc
3450 ,'N' --gl_transfer_mode_code
3451 ,xdl.merge_duplicate_code
3452 ,xdl.unrounded_entered_dr
3453 ,xdl.unrounded_entered_cr
3454 ,xdl.unrounded_accounted_dr
3455 ,xdl.unrounded_accounted_cr
3456 ,xdl.calculate_acctd_amts_flag
3457 ,xdl.calculate_g_l_amts_flag
3458 ,ael.gain_or_loss_flag
3459 ,xdl.rounding_class_code
3460 ,xdl.document_rounding_level
3461 ,xdl.doc_rounding_acctd_amt
3462 ,xdl.doc_rounding_entered_amt
3463 ,nvl(fcu.minimum_accountable_unit, power(10, -1*fcu.precision))
3464 ,ael.currency_code
3465 ,ael.currency_conversion_date
3466 ,ael.currency_conversion_rate
3467 ,ael.currency_conversion_type
3468 ,ael.statistical_amount
3469 ,p_new_party_id
3470 ,p_new_site_id
3471 ,ael.party_type_code
3472 ,ael.ussgl_transaction_code
3473 ,ael.jgzz_recon_ref
3474 ,xdl.source_distribution_id_char_1
3475 ,xdl.source_distribution_id_char_2
3476 ,xdl.source_distribution_id_char_3
3477 ,xdl.source_distribution_id_char_4
3478 ,xdl.source_distribution_id_char_5
3479 ,xdl.source_distribution_id_num_1
3480 ,xdl.source_distribution_id_num_2
3481 ,xdl.source_distribution_id_num_3
3482 ,xdl.source_distribution_id_num_4
3483 ,xdl.source_distribution_id_num_5
3484 ,xdl.source_distribution_type
3485 ,ael.analytical_balance_flag
3486 ,'REBOOKING'
3487 ,'F'
3488 ,'N'
3489 ,0 -- 5100860 assign value to avoid using function index
3490 ,decode(ael.control_balance_flag, 'Y', 'P', 'P', 'P', null)
3491 ,ael.encumbrance_type_id
3492 FROM
3493 xla_ae_lines ael
3494 ,xla_ae_headers aeh
3495 ,xla_distribution_links xdl
3496 ,fnd_currencies fcu
3497 WHERE nvl(ael.merge_party_id, ael.party_id) = p_old_party_id
3498 AND nvl(nvl(ael.merge_party_site_id, ael.party_site_id), -1)
3499 = nvl(p_old_site_id, -1)
3500 and nvl(ael.party_type_code , p_party_type) = p_party_type
3501 and ael.currency_code <> 'STAT'
3502 and ael.currency_code = fcu.currency_code
3503 AND aeh.ae_header_id = xdl.ae_header_id
3504 AND ael.ae_line_num = xdl.ae_line_num
3505 AND ael.APPLICATION_ID = p_application_id
3506 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
3507 AND aeh.BALANCE_TYPE_CODE = 'A'
3508 AND aeh.APPLICATION_ID = ael.application_id
3509 AND aeh.LEDGER_ID = p_array_ledger_id(i)
3510 AND aeh.ACCOUNTING_DATE > p_merge_date
3511 AND aeh.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
3512 AND aeh.ae_header_id in (
3513 SELECT ae_header_id
3514 FROM XLA_TPM_WORKING_HDRS_T xtwh
3515 WHERE xtwh.merge_event_id = p_event_id
3516 AND xtwh.process_type_flag= 'R'
3517 AND rownum <= C_WORK_UNIT)
3518 AND aeh.merge_event_id is null
3519 /*
3520 AND NOT EXISTS (
3521 SELECT 1
3522 FROM xla_distribution_links
3523 WHERE ref_ae_header_id = xdl.ae_header_id
3524 AND ref_temp_line_num = xdl.temp_line_num
3525 -- means it is a third party merge line
3526 And ref_ae_header_id <>ae_header_id
3527 )
3528 */
3529 ;
3530
3531 v_row_count :=v_row_count + SQL%ROWCOUNT;
3532 IF (C_LEVEL_STATEMENT>= g_log_level)
3533 THEN
3534 trace( p_msg => '# total rows inserted:'||to_char(v_row_count)
3535 , p_level => C_LEVEL_STATEMENT
3536 , p_module => v_module);
3537 END IF;
3538
3539
3540 IF(v_row_count>0) THEN
3541 IF (C_LEVEL_STATEMENT>= g_log_level)
3542 THEN
3543 trace( p_msg => 'before inserting header'
3544 , p_level => C_LEVEL_STATEMENT
3545 , p_module => v_module);
3546 END IF;
3547
3548 generate_headers( p_application_id => p_application_id
3549 ,p_reverse_header_desc => p_reverse_header_desc
3550 ,p_accounting_mode => p_accounting_mode
3551 );
3552
3553 IF (C_LEVEL_STATEMENT>= g_log_level)
3554 THEN
3555 trace( p_msg => 'return from generate_headers, Header inserted'
3556 , p_level => C_LEVEL_STATEMENT
3557 , p_module => v_module);
3558 END IF;
3559 END IF;
3560
3561
3562 IF (C_LEVEL_PROCEDURE >= g_log_level)
3563 THEN
3564 trace( p_msg => 'end- ' || v_function || ' returning :'||to_char(v_row_count)
3565 , p_level => C_LEVEL_PROCEDURE
3566 , p_module => v_module);
3567 END IF;
3568 return v_row_count;
3569 END create_reverse_rebooking_aes;
3570
3571
3572 PROCEDURE create_journal_entries(
3573 x_errbuf OUT NOCOPY VARCHAR2
3574 ,x_retcode OUT NOCOPY VARCHAR2
3575 ,p_application_id IN NUMBER
3576 ,p_accounting_mode IN VARCHAR2
3577 ,p_transfer_to_gl_flag IN VARCHAR2
3578 ,p_post_in_gl_flag IN VARCHAR2
3579 ,p_event_id IN NUMBER
3580 ,p_entity_id IN NUMBER
3581 ,p_mapping_flag IN VARCHAR2
3582 ,p_event_ledger_id IN NUMBER
3583 ,p_merge_date IN DATE
3584 ,p_merge_type IN VARCHAR2
3585 ,p_old_site_id IN NUMBER
3586 ,p_old_party_id IN NUMBER
3587 ,p_new_site_id IN NUMBER
3588 ,p_new_party_id IN NUMBER
3589 ,p_party_type IN VARCHAR2
3590 ,p_balance_desc IN VARCHAR2
3591 ,p_reverse_line_desc IN VARCHAR2
3592 ,p_rebooking_line_desc IN VARCHAR2
3593 ,p_reverse_header_desc IN VARCHAR2
3594 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
3595 ,p_array_ledger_category IN t_varchar30_array
3596 ,p_array_reversal_option IN t_varchar30_array
3597 ,p_array_rounding_rule_code IN t_varchar30_array
3598 ,p_array_mau IN t_number_array
3599 ,p_array_merge_option IN t_varchar30_array
3600 ,p_array_submit_transfer IN t_varchar1_array) is
3601
3602 v_query varchar2(20000);
3603 v_function VARCHAR2(240);
3604 v_module VARCHAR2(240);
3605 v_gl_date t_date_array;
3606 v_gl_period_name t_varchar30_array;
3607 v_row_count INTEGER:=0;
3608 v_row_count1 INTEGER:=0;
3609 v_status VARCHAR2(1) := 'B';
3610 v_batch_id NUMBER(15) := null;
3611 v_array_ledger_id xla_accounting_cache_pkg.t_array_ledger_id;
3612 v_array_ledger_category t_varchar30_array;
3613 v_array_reversal_option t_varchar30_array;
3614 v_array_rounding_rule_code t_varchar30_array;
3615 v_array_mau t_number_array;
3616 v_array_merge_option t_varchar30_array;
3617 v_array_submit_transfer t_varchar1_array;
3618 l_count NUMBER :=0;
3619 begin
3620 v_function := 'xla_third_party_merge.create_journal_entries';
3621 v_module := C_DEFAULT_MODULE||'.create_journal_entries';
3622
3623 -- Log the function entry, the passed parameters and their values
3624 IF (C_LEVEL_PROCEDURE >= g_log_level)
3625 THEN
3626 trace( p_msg => 'BEGIN - ' || v_function
3627 , p_level => C_LEVEL_PROCEDURE
3628 , p_module => v_module);
3629 trace( p_msg => 'p_applicaiton_id = ' || p_application_id
3630 || ', p_event_id = ' || p_event_id
3631 , p_level => C_LEVEL_PROCEDURE
3632 , p_module => v_module);
3633 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
3634
3635 FOR Idx IN p_array_ledger_id.FIRST .. p_array_ledger_id.LAST LOOP
3636 IF(p_array_merge_option(Idx) = 'TRANSFER') THEN
3637 l_count := l_count + 1;
3638 v_array_ledger_id(l_count) := p_array_ledger_id(Idx);
3639 v_array_ledger_category(l_count) := p_array_ledger_category(Idx);
3640 v_array_reversal_option(l_count) := p_array_reversal_option(Idx);
3641 v_array_rounding_rule_code(l_count) := p_array_rounding_rule_code(Idx);
3642 v_array_mau(l_count) := p_array_mau(Idx);
3643 v_array_merge_option(l_count) := p_array_merge_option(Idx);
3644 v_array_submit_transfer(l_count) := p_array_submit_transfer(Idx);
3645 END IF;
3646 END LOOP;
3647
3648 LOOP
3649 IF(v_status = 'B') THEN
3650 v_row_count := create_balance_transfer_aes(
3651 p_application_id => p_application_id
3652 ,p_accounting_mode => p_accounting_mode
3653 ,p_event_id => p_event_id
3654 ,p_entity_id => p_entity_id
3655 ,p_event_ledger_id => p_event_ledger_id
3656 ,p_merge_date => p_merge_date
3657 ,p_merge_type => p_merge_type
3658 ,p_old_site_id => p_old_site_id
3659 ,p_old_party_id => p_old_party_id
3660 ,p_new_site_id => p_new_site_id
3661 ,p_new_party_id => p_new_party_id
3662 ,p_party_type => p_party_type
3663 ,p_balance_desc => p_balance_desc
3664 ,p_array_ledger_id => v_array_ledger_id
3665 ,p_array_ledger_category => v_array_ledger_category
3666 ,p_array_reversal_option => v_array_reversal_option
3667 ,p_array_merge_option => v_array_merge_option
3668 ,p_array_submit_transfer => v_array_submit_transfer);
3669 ELSE
3670 v_row_count := create_reverse_rebooking_aes(
3671 p_application_id => p_application_id
3672 ,p_accounting_mode => p_accounting_mode
3673 ,p_event_id => p_event_id
3674 ,p_entity_id => p_entity_id
3675 ,p_event_ledger_id => p_event_ledger_id
3676 ,p_merge_date => p_merge_date
3677 ,p_merge_type => p_merge_type
3678 ,p_old_site_id => p_old_site_id
3679 ,p_old_party_id => p_old_party_id
3680 ,p_new_site_id => p_new_site_id
3681 ,p_new_party_id => p_new_party_id
3682 ,p_party_type => p_party_type
3683 ,p_reverse_line_desc => p_reverse_line_desc
3684 ,p_rebooking_line_desc => p_rebooking_line_desc
3685 ,p_reverse_header_desc => p_reverse_header_desc
3686 ,p_array_ledger_id => v_array_ledger_id
3687 ,p_array_ledger_category => v_array_ledger_category
3688 ,p_array_reversal_option => v_array_reversal_option
3689 ,p_array_merge_option => v_array_merge_option
3690 ,p_array_submit_transfer => v_array_submit_transfer);
3691 END IF;
3692
3693 IF(v_row_count > 0) THEN
3694 IF(p_mapping_flag = 'Y') THEN
3695 process_accounting_mapping(p_application_id => p_application_id
3696 ,p_event_id => p_event_id);
3697 END IF;
3698 get_line_number(
3699 p_array_ledger_id => v_array_ledger_id
3700 ,p_array_rounding_rule_code => v_array_rounding_rule_code
3701 ,p_array_mau => v_array_mau);
3702
3703 insert_lines(
3704 p_application_id => p_application_id
3705 ,p_array_ledger_id => v_array_ledger_id
3706 ,p_array_reversal_option => v_array_reversal_option
3707 ,p_array_mau => v_array_mau
3708 ,p_array_rounding_rule=> v_array_rounding_rule_code);
3709
3710 IF(p_accounting_mode = 'F' AND v_batch_id is null) THEN
3711 SELECT xla_accounting_batches_s.NEXTVAL INTO v_batch_id FROM DUAL;
3712 IF (C_LEVEL_STATEMENT>= g_log_level)
3713 THEN
3714 trace( p_msg => 'Getting the batch id:'||to_char(v_batch_id)
3715 , p_level => C_LEVEL_STATEMENT
3716 , p_module => v_module);
3717 END IF;
3718 END IF;
3719
3720 insert_headers(
3721 p_batch_id => v_batch_id
3722 ,p_application_id => p_application_id
3723 ,p_event_id => p_event_id
3724 ,p_accounting_mode => p_accounting_mode);
3725
3726 insert_links(
3727 p_application_id => p_application_id);
3728
3729
3730 IF(xla_je_validation_pkg.balance_tpm_amounts
3731 (p_application_id => p_application_id
3732 ,p_ledger_id => p_event_ledger_id
3733 ,p_ledger_array => v_array_ledger_id
3734 ,p_accounting_mode => p_accounting_mode) = 1) THEN
3735 raise BalanceError;
3736 END IF;
3737
3738 IF(p_accounting_mode <> 'D') THEN
3739 UPDATE xla_ae_lines xal
3740 SET (merge_party_id, merge_party_site_id, merge_code_combination_id)
3741 = (select party_id, party_site_id, code_combination_id
3742 from xla_ae_lines_gt xalg
3743 where xalg.ref_ae_header_id = xal.ae_header_id
3744 AND xalg.ref_ae_line_num = xal.ae_line_num
3745 AND xalg.reversal_code in ('REBOOKING', 'TRANSFER_BALANCE')
3746 and rownum = 1)
3747 WHERE xal.application_id = p_application_id
3748 AND (ae_header_id, ae_line_num) in
3749 (select xlg.ref_ae_header_id, xlg.ref_ae_line_num
3750 from xla_ae_lines_gt xlg
3751 ,xla_ae_headers xah
3752 where xlg.reversal_code in ('REBOOKING', 'TRANSFER_BALANCE')
3753 -- Bug 5103972 MPA / Reversal of incomplete JE
3754 -- should not be stamped with merge party informtion
3755 AND xlg.ref_ae_header_id = xah.ae_header_id
3756 AND xah.application_id = p_application_id
3757 AND (xah.parent_ae_header_id IS NULL
3758 OR xah.accounting_entry_status_code <> 'N')
3759 );
3760
3761 END IF;
3762 END IF;
3763
3764 v_row_count1 := 0 ;
3765 IF(v_status = 'B') THEN
3766 DELETE XLA_TPM_WORKING_HDRS_T xtwh
3767 WHERE xtwh.merge_event_id = p_event_id
3768 AND xtwh.process_type_flag= 'B'
3769 AND rownum <= C_WORK_UNIT;
3770 v_row_count1 :=SQL%ROWCOUNT;
3771 ELSE
3772 DELETE XLA_TPM_WORKING_HDRS_T xtwh
3773 WHERE xtwh.merge_event_id = p_event_id
3774 AND xtwh.process_type_flag= 'R'
3775 AND rownum <= C_WORK_UNIT;
3776 v_row_count1 :=SQL%ROWCOUNT;
3777 END IF;
3778
3779 IF(v_row_count > 0 AND v_row_count1 > 0) THEN
3780 COMMIT;
3781 END IF;
3782
3783 EXIT WHEN (v_row_count1 = 0 and v_status = 'R');
3784
3785 IF(v_row_count1 = 0 AND v_status = 'B') THEN
3786 v_status := 'R';
3787 END IF;
3788
3789 END LOOP;
3790
3791 /* Added by krsankar for bug 7457594 and RCA bug 8395892 */
3792
3793 IF v_batch_id IS NOT NULL
3794 THEN
3795
3796 IF(p_accounting_mode = 'F' AND p_transfer_to_gl_flag = 'Y') THEN
3797 IF (C_LEVEL_STATEMENT>= g_log_level)
3798 THEN
3799 trace( p_msg => 'before calling gl_transfer_main'
3800 , p_level => C_LEVEL_STATEMENT
3801 , p_module => v_module);
3802 END IF;
3803 xla_transfer_pkg.gl_transfer_main
3804 (p_application_id => p_application_id
3805 ,p_transfer_mode => 'COMBINED'
3806 ,p_ledger_id => p_event_ledger_id
3807 ,p_securiy_id_int_1 => null
3808 ,p_securiy_id_int_2 => null
3809 ,p_securiy_id_int_3 => null
3810 ,p_securiy_id_char_1 => null
3811 ,p_securiy_id_char_2 => null
3812 ,p_securiy_id_char_3 => null
3813 ,p_valuation_method => null
3814 ,p_process_category => null
3815 ,p_accounting_batch_id => v_batch_id
3816 ,p_entity_id => NULL
3817 ,p_batch_name => null
3818 ,p_end_date => null
3819 ,p_submit_gl_post => p_post_in_gl_flag
3820 ,p_caller => xla_transfer_pkg.C_TP_MERGE); -- Bug 5056632
3821
3822 IF (C_LEVEL_STATEMENT>= g_log_level)
3823 THEN
3824 trace( p_msg => 'after calling gl_transfer_main'
3825 , p_level => C_LEVEL_STATEMENT
3826 , p_module => v_module);
3827 END IF;
3828 END IF;
3829
3830 ELSE /* Else part of v_batch_id IS NOT NULL*/
3831 /* Added by krsankar for bug 7457594 and RCA bug 8395892 */
3832
3833 IF (C_LEVEL_STATEMENT>= g_log_level)
3834 THEN
3835 trace( p_msg => 'Else of v_batch_id : batch_id IS NULL'
3836 , p_level => C_LEVEL_STATEMENT
3837 , p_module => v_module);
3838 END IF;
3839
3840 IF (C_LEVEL_STATEMENT>= g_log_level)
3841 THEN
3842 trace( p_msg => 'Found no records to process.Returning back to create_accounting'
3843 , p_level => C_LEVEL_STATEMENT
3844 , p_module => v_module);
3845 END IF;
3846
3847 return;
3848
3849
3850 END IF;
3851
3852 IF (C_LEVEL_PROCEDURE >= g_log_level)
3853 THEN
3854 trace( p_msg => 'END - ' || v_function
3855 , p_level => C_LEVEL_PROCEDURE
3856 , p_module => v_module);
3857 END IF;
3858 end create_journal_entries;
3859
3860 PROCEDURE update_journal_entries(
3861 x_errbuf OUT NOCOPY VARCHAR2
3862 ,x_retcode OUT NOCOPY VARCHAR2
3863 ,p_application_id IN NUMBER
3864 ,p_event_id IN NUMBER
3865 ,p_event_merge_option IN VARCHAR2
3866 ,p_entity_id IN NUMBER
3867 ,p_mapping_flag IN VARCHAR2
3868 ,p_event_ledger_id IN NUMBER
3869 ,p_merge_date IN DATE
3870 ,p_merge_type IN VARCHAR2
3871 ,p_old_site_id IN NUMBER
3872 ,p_old_party_id IN NUMBER
3873 ,p_new_site_id IN NUMBER
3874 ,p_new_party_id IN NUMBER
3875 ,p_party_type IN VARCHAR2
3876 ,p_line_desc IN VARCHAR2
3877 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
3878 ,p_array_ledger_category IN t_varchar30_array
3879 ,p_array_reversal_option IN t_varchar30_array
3880 ,p_array_merge_option IN t_varchar30_array
3881 ,p_array_submit_transfer IN t_varchar1_array)
3882 is
3883
3884 v_function VARCHAR2(240);
3885 v_module VARCHAR2(240);
3886 v_gl_date t_date_array;
3887 v_gl_period_name t_varchar30_array;
3888 v_row_count INTEGER:=0;
3889 v_total_row_count INTEGER:=0;
3890
3891 v_aeh_desc VARCHAR2(1996);
3892
3893 begin
3894 v_function := 'xla_third_party_merge.update_journal_entries';
3895 v_module := C_DEFAULT_MODULE||'.update_journal_entries';
3896
3897 -- Log the function entry, the passed parameters and their values
3898 IF (C_LEVEL_PROCEDURE >= g_log_level)
3899 THEN
3900 trace( p_msg => 'BEGIN - ' || v_function
3901 , p_level => C_LEVEL_PROCEDURE
3902 , p_module => v_module);
3903 trace( p_msg => 'p_applicaiton_id = ' || p_application_id
3904 || ', p_event_id = ' || p_event_id
3905 , p_level => C_LEVEL_PROCEDURE
3906 , p_module => v_module);
3907 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
3908
3909 v_aeh_desc := xla_messages_pkg.get_message
3910 ( p_appli_s_name => 'XLA'
3911 , p_msg_name => 'XLA_MERGE_AEH_DESC'
3912 , p_token_1 => 'MERGE_DATE'
3913 , p_value_1 => p_merge_date);
3914
3915 IF(p_merge_type = 'PARTIAL_MERGE') THEN
3916 FORALL i IN 1..p_array_ledger_id.count
3917 UPDATE XLA_AE_HEADERS aeh
3918 SET DESCRIPTION
3919 = DECODE(DESCRIPTION
3920 , NULL, v_aeh_desc
3921 , SUBSTRB(DESCRIPTION, 0,
3922 1995 - LENGTHB(v_aeh_desc))
3923 || ' ' || v_aeh_desc),
3924 LAST_UPDATE_DATE = sysdate,
3925 LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
3926 LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
3927 PROGRAM_UPDATE_DATE = sysdate,
3928 PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
3929 PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
3930 REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
3931 WHERE aeh.APPLICATION_ID = p_application_id
3932 AND aeh.LEDGER_ID = p_array_ledger_id(i)
3933 AND ((aeh.accounting_entry_status_code = 'F' AND
3934 p_array_merge_option(i) = 'CHANGE')
3935 OR
3936 --
3937 -- Bug 5103972
3938 -- Should update party info for incomplete je for MPA
3939 -- even when the merge option is 'TRANSFER'
3940 --
3941 (p_array_merge_option(i) = 'TRANSFER' AND
3942 aeh.parent_ae_header_id IS NOT NULL AND
3943 aeh.accounting_entry_status_code = 'N'
3944 )
3945 )
3946 AND EXISTS
3947 (SELECT 'X'
3948 FROM XLA_AE_LINES ael
3949 WHERE ael.PARTY_ID = p_old_party_id
3950 AND ( p_old_site_id IS NULL
3951 OR ael.PARTY_SITE_ID = p_old_site_id)
3952 AND ael.PARTY_TYPE_CODE = p_party_type
3953 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
3954 AND aeh.APPLICATION_ID = ael.APPLICATION_ID)
3955 AND EXISTS
3956 (SELECT 'X'
3957 FROM XLA_PARTIAL_MERGE_TXNS pmt
3958 WHERE pmt.APPLICATION_ID = p_application_id
3959 AND pmt.MERGE_EVENT_ID = p_event_id
3960 AND pmt.ENTITY_ID = aeh.ENTITY_ID);
3961 ELSE
3962 FORALL i IN 1..p_array_ledger_id.count
3963 UPDATE XLA_AE_HEADERS aeh
3964 SET DESCRIPTION
3965 = DECODE(DESCRIPTION
3966 , NULL, v_aeh_desc
3967 , SUBSTRB(DESCRIPTION, 0,
3968 1995 - LENGTHB(v_aeh_desc))
3969 || ' ' || v_aeh_desc),
3970 LAST_UPDATE_DATE = sysdate,
3971 LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
3972 LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
3973 PROGRAM_UPDATE_DATE = sysdate,
3974 PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
3975 PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
3976 REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
3977 WHERE aeh.APPLICATION_ID = p_application_id
3978 AND aeh.LEDGER_ID = p_array_ledger_id(i)
3979 AND ((aeh.accounting_entry_status_code = 'F' AND
3980 p_array_merge_option(i) = 'CHANGE')
3981 OR
3982 --
3983 -- Bug 5103972
3984 -- Should update party info for incomplete je for MPA
3985 -- even when the merge option is 'TRANSFER'
3986 --
3987 (p_array_merge_option(i) = 'TRANSFER' AND
3988 aeh.parent_ae_header_id IS NOT NULL AND
3989 aeh.accounting_entry_status_code = 'N'
3990 )
3991 )
3992 AND EXISTS
3993 (SELECT 'X'
3994 FROM XLA_AE_LINES ael
3995 WHERE ael.PARTY_ID = p_old_party_id
3996 AND ( p_old_site_id IS NULL
3997 OR ael.PARTY_SITE_ID = p_old_site_id)
3998 AND ael.PARTY_TYPE_CODE = p_party_type
3999 AND aeh.AE_HEADER_ID = ael.AE_HEADER_ID
4000 AND aeh.APPLICATION_ID = ael.APPLICATION_ID);
4001 END IF;
4002
4003 v_row_count :=SQL%ROWCOUNT;
4004 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4005 trace( p_msg => '# of headers updated:'||to_char(v_row_count)
4006 , p_level => C_LEVEL_STATEMENT
4007 , p_module => v_module);
4008 END IF;
4009
4010 IF(v_row_count > 0) THEN
4011 -- need to apply segment mapping for transfer and incomplete entry
4012 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4013 trace( p_msg => 'update the line next '
4014 , p_level => C_LEVEL_STATEMENT
4015 , p_module => v_module);
4016 END IF;
4017
4018 IF(p_event_merge_option in ('BOTH', 'TRANSFER') AND p_mapping_flag = 'Y') THEN
4019 process_incomplete_acct_map(
4020 p_application_id => p_application_id
4021 ,p_event_id => p_event_id
4022 ,p_event_merge_option => p_event_merge_option
4023 ,p_entity_id => p_entity_id
4024 ,p_merge_date => p_merge_date
4025 ,p_merge_type => p_merge_type
4026 ,p_old_site_id => p_old_site_id
4027 ,p_old_party_id => p_old_party_id
4028 ,p_new_site_id => p_new_site_id
4029 ,p_new_party_id => p_new_party_id
4030 ,p_party_type => p_party_type
4031 ,p_array_ledger_id => p_array_ledger_id
4032 ,p_array_ledger_category => p_array_ledger_category
4033 ,p_array_reversal_option => p_array_reversal_option
4034 ,p_array_merge_option => p_array_merge_option);
4035
4036 END IF;
4037
4038 IF(p_merge_type = 'PARTIAL_MERGE') THEN
4039 FORALL i IN 1..p_array_ledger_id.count
4040 UPDATE XLA_AE_LINES ael
4041 SET PARTY_ID = p_new_party_id,
4042 PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
4043 DESCRIPTION
4044 = DECODE(DESCRIPTION
4045 , NULL, p_line_desc
4046 , SUBSTRB(DESCRIPTION, 0,
4047 1995 - LENGTHB(p_line_desc))
4048 || ' ' || p_line_desc),
4049 LAST_UPDATE_DATE = sysdate,
4050 LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
4051 LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
4052 PROGRAM_UPDATE_DATE = sysdate,
4053 PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
4054 PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
4055 REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
4056 WHERE ael.PARTY_ID = p_old_party_id
4057 AND ( p_old_site_id IS NULL
4058 OR ael.PARTY_SITE_ID = p_old_site_id)
4059 AND ael.PARTY_TYPE_CODE = p_party_type
4060 AND EXISTS
4061 (SELECT 'X'
4062 FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
4063 WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
4064 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
4065 AND aeh.APPLICATION_ID = p_application_id
4066 AND aeh.accounting_entry_status_code = 'F'
4067 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4068 AND p_array_merge_option(i) = 'CHANGE'
4069 AND pmt.APPLICATION_ID = p_application_id
4070 AND pmt.MERGE_EVENT_ID = p_event_id
4071 AND pmt.ENTITY_ID = aeh.ENTITY_ID
4072 UNION ALL
4073 --
4074 -- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
4075 --
4076 SELECT 'X'
4077 FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
4078 WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
4079 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
4080 AND aeh.APPLICATION_ID = p_application_id
4081 AND aeh.accounting_entry_status_code = 'N'
4082 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4083 AND aeh.parent_ae_header_id IS NOT NULL
4084 AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
4085 AND pmt.APPLICATION_ID = p_application_id
4086 AND pmt.MERGE_EVENT_ID = p_event_id
4087 AND pmt.ENTITY_ID = aeh.ENTITY_ID
4088 );
4089
4090 v_row_count :=SQL%ROWCOUNT;
4091 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4092 trace( p_msg => '# of lines updated:'||to_char(v_row_count)
4093 , p_level => C_LEVEL_STATEMENT
4094 , p_module => v_module);
4095 END IF;
4096
4097
4098 FORALL i IN 1..p_array_ledger_id.count
4099 UPDATE XLA_TRIAL_BALANCES tb
4100 SET PARTY_ID = p_new_party_id,
4101 PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
4102 LAST_UPDATE_DATE = sysdate,
4103 LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
4104 LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
4105 PROGRAM_UPDATE_DATE = sysdate,
4106 PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
4107 PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
4108 REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
4109 WHERE tb.PARTY_ID = p_old_party_id
4110 AND ( p_old_site_id IS NULL
4111 OR tb.PARTY_SITE_ID = p_old_site_id)
4112 AND tb.PARTY_TYPE_CODE = p_party_type
4113 AND EXISTS
4114 (SELECT 'X'
4115 FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
4116 WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
4117 AND aeh.APPLICATION_ID = p_application_id
4118 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4119 AND aeh.accounting_entry_status_code = 'F'
4120 AND p_array_merge_option(i) = 'CHANGE'
4121 AND pmt.APPLICATION_ID = p_application_id
4122 AND pmt.MERGE_EVENT_ID = p_event_id
4123 AND pmt.ENTITY_ID = aeh.ENTITY_ID
4124 UNION ALL
4125 --
4126 -- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
4127 --
4128 SELECT 'X'
4129 FROM XLA_AE_HEADERS aeh, XLA_PARTIAL_MERGE_TXNS pmt
4130 WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
4131 AND aeh.APPLICATION_ID = p_application_id
4132 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4133 AND aeh.parent_ae_header_id IS NOT NULL
4134 AND aeh.accounting_entry_status_code = 'N'
4135 AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
4136 AND pmt.APPLICATION_ID = p_application_id
4137 AND pmt.MERGE_EVENT_ID = p_event_id
4138 AND pmt.ENTITY_ID = aeh.ENTITY_ID);
4139
4140 v_row_count :=SQL%ROWCOUNT;
4141 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4142 trace( p_msg => '# of rows in xla_trial_balances table updated:'||to_char(v_row_count)
4143 , p_level => C_LEVEL_STATEMENT
4144 , p_module => v_module);
4145 END IF;
4146
4147 ELSE
4148 FORALL i IN 1..p_array_ledger_id.count
4149 UPDATE XLA_AE_LINES ael
4150 SET PARTY_ID = p_new_party_id,
4151 PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
4152 DESCRIPTION
4153 = DECODE(DESCRIPTION
4154 , NULL, p_line_desc
4155 , SUBSTRB(DESCRIPTION, 0,
4156 1995 - LENGTHB(p_line_desc))
4157 || ' ' || p_line_desc),
4158 LAST_UPDATE_DATE = sysdate,
4159 LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
4160 LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
4161 PROGRAM_UPDATE_DATE = sysdate,
4162 PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
4163 PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
4164 REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
4165 WHERE ael.PARTY_ID = p_old_party_id
4166 AND ( p_old_site_id IS NULL
4167 OR ael.PARTY_SITE_ID = p_old_site_id)
4168 AND ael.PARTY_TYPE_CODE = p_party_type
4169 AND EXISTS
4170 (SELECT 'X'
4171 FROM XLA_AE_HEADERS aeh
4172 WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
4173 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
4174 AND aeh.APPLICATION_ID = p_application_id
4175 AND aeh.accounting_entry_status_code = 'F'
4176 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4177 AND p_array_merge_option(i) = 'CHANGE'
4178 UNION ALL
4179 --
4180 -- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
4181 --
4182 SELECT 'X'
4183 FROM XLA_AE_HEADERS aeh
4184 WHERE ael.APPLICATION_ID = aeh.APPLICATION_ID
4185 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
4186 AND aeh.APPLICATION_ID = p_application_id
4187 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4188 AND aeh.accounting_entry_status_code = 'N'
4189 AND aeh.parent_ae_header_id IS NOT NULL
4190 AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
4191 )
4192 ;
4193
4194 v_row_count :=SQL%ROWCOUNT;
4195 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4196 trace( p_msg => '# of lines updated:'||to_char(v_row_count)
4197 , p_level => C_LEVEL_STATEMENT
4198 , p_module => v_module);
4199 END IF;
4200
4201 FORALL i IN 1..p_array_ledger_id.count
4202 UPDATE XLA_TRIAL_BALANCES tb
4203 SET PARTY_ID = p_new_party_id,
4204 PARTY_SITE_ID = NVL(p_new_site_id, PARTY_SITE_ID),
4205 LAST_UPDATE_DATE = sysdate,
4206 LAST_UPDATED_BY = XLA_ENVIRONMENT_PKG.g_usr_id,
4207 LAST_UPDATE_LOGIN = XLA_ENVIRONMENT_PKG.g_login_id,
4208 PROGRAM_UPDATE_DATE = sysdate,
4209 PROGRAM_APPLICATION_ID = XLA_ENVIRONMENT_PKG.g_prog_appl_id,
4210 PROGRAM_ID = XLA_ENVIRONMENT_PKG.g_prog_id,
4211 REQUEST_ID = XLA_ENVIRONMENT_PKG.g_req_id
4212 WHERE tb.PARTY_ID = p_old_party_id
4213 AND ( p_old_site_id IS NULL
4214 OR tb.PARTY_SITE_ID = p_old_site_id)
4215 AND tb.PARTY_TYPE_CODE = p_party_type
4216 AND EXISTS
4217 (SELECT 'X'
4218 FROM XLA_AE_HEADERS aeh
4219 WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
4220 AND aeh.APPLICATION_ID = p_application_id
4221 AND aeh.accounting_entry_status_code = 'F'
4222 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4223 AND p_array_merge_option(i) = 'CHANGE'
4224 UNION ALL
4225 --
4226 -- Incomplete lines from MPA / Accrual Reversal (5090223/5103972)
4227 --
4228 SELECT 'X'
4229 FROM XLA_AE_HEADERS aeh
4230 WHERE tb.AE_HEADER_ID = aeh.AE_HEADER_ID
4231 AND aeh.APPLICATION_ID = p_application_id
4232 AND aeh.accounting_entry_status_code = 'N'
4233 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4234 AND aeh.parent_ae_header_id IS NOT NULL
4235 AND p_array_merge_option(i) IN ('TRANSFER','CHANGE')
4236 );
4237
4238 v_row_count :=SQL%ROWCOUNT;
4239 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4240 trace( p_msg => '# of rows in xla_trial_balances table updated:'||to_char(v_row_count)
4241 , p_level => C_LEVEL_STATEMENT
4242 , p_module => v_module);
4243 END IF;
4244
4245 END IF;
4246
4247 END IF;
4248
4249 IF (C_LEVEL_PROCEDURE>= g_log_level)
4250 THEN
4251 trace( p_msg => 'End of '||v_function
4252 , p_level => C_LEVEL_PROCEDURE
4253 , p_module => v_module);
4254 END IF;
4255
4256 end update_journal_entries;
4257
4258
4259
4260 PROCEDURE populate_ccid_to_gt IS
4261 v_function VARCHAR2(240);
4262 v_module VARCHAR2(240);
4263 cursor c_null_ccid is
4264 select 1
4265 from xla_ae_lines_gt
4266 where code_combination_id is null;
4267 v_temp NUMBER;
4268 begin
4269 v_function := 'xla_third_party_merge.populate_ccid_to_gt';
4270 v_module := C_DEFAULT_MODULE||'.populate_ccid_to_gt';
4271
4272 -- Log the function entry, the passed parameters and their values
4273 IF (C_LEVEL_PROCEDURE >= g_log_level)
4274 THEN
4275 trace( p_msg => 'BEGIN - ' || v_function
4276 , p_level => C_LEVEL_PROCEDURE
4277 , p_module => v_module);
4278 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
4279
4280 update xla_ae_lines_gt xalg
4281 set code_combination_id =
4282 (select code_combination_id
4283 from gl_code_combinations gcc
4284 where gcc.chart_of_accounts_id = xalg.ccid_coa_id
4285 and gcc.template_id is null
4286 and (gcc.segment1= xalg.segment1 or (gcc.segment1 is null and xalg.segment1 is null))
4287 and (gcc.segment2= xalg.segment2 or (gcc.segment2 is null and xalg.segment2 is null))
4288 and (gcc.segment3= xalg.segment3 or (gcc.segment3 is null and xalg.segment3 is null))
4289 and (gcc.segment4= xalg.segment4 or (gcc.segment4 is null and xalg.segment4 is null))
4290 and (gcc.segment5= xalg.segment5 or (gcc.segment5 is null and xalg.segment5 is null))
4291 and (gcc.segment6= xalg.segment6 or (gcc.segment6 is null and xalg.segment6 is null))
4292 and (gcc.segment7= xalg.segment7 or (gcc.segment7 is null and xalg.segment7 is null))
4293 and (gcc.segment8= xalg.segment8 or (gcc.segment8 is null and xalg.segment8 is null))
4294 and (gcc.segment9= xalg.segment9 or (gcc.segment9 is null and xalg.segment9 is null))
4295 and (gcc.segment10= xalg.segment10 or (gcc.segment10 is null and xalg.segment10 is null))
4296 and (gcc.segment11= xalg.segment11 or (gcc.segment11 is null and xalg.segment11 is null))
4297 and (gcc.segment12= xalg.segment12 or (gcc.segment12 is null and xalg.segment12 is null))
4298 and (gcc.segment13= xalg.segment13 or (gcc.segment13 is null and xalg.segment13 is null))
4299 and (gcc.segment14= xalg.segment14 or (gcc.segment14 is null and xalg.segment14 is null))
4300 and (gcc.segment15= xalg.segment15 or (gcc.segment15 is null and xalg.segment15 is null))
4301 and (gcc.segment16= xalg.segment16 or (gcc.segment16 is null and xalg.segment16 is null))
4302 and (gcc.segment17= xalg.segment17 or (gcc.segment17 is null and xalg.segment17 is null))
4303 and (gcc.segment18= xalg.segment18 or (gcc.segment18 is null and xalg.segment18 is null))
4304 and (gcc.segment19= xalg.segment19 or (gcc.segment19 is null and xalg.segment19 is null))
4305 and (gcc.segment20= xalg.segment20 or (gcc.segment20 is null and xalg.segment20 is null))
4306 and (gcc.segment21= xalg.segment21 or (gcc.segment21 is null and xalg.segment21 is null))
4307 and (gcc.segment22= xalg.segment22 or (gcc.segment22 is null and xalg.segment22 is null))
4308 and (gcc.segment23= xalg.segment23 or (gcc.segment23 is null and xalg.segment23 is null))
4309 and (gcc.segment24= xalg.segment24 or (gcc.segment24 is null and xalg.segment24 is null))
4310 and (gcc.segment25= xalg.segment25 or (gcc.segment25 is null and xalg.segment25 is null))
4311 and (gcc.segment26= xalg.segment26 or (gcc.segment26 is null and xalg.segment26 is null))
4312 and (gcc.segment27= xalg.segment27 or (gcc.segment27 is null and xalg.segment27 is null))
4313 and (gcc.segment28= xalg.segment28 or (gcc.segment28 is null and xalg.segment28 is null))
4314 and (gcc.segment29= xalg.segment29 or (gcc.segment29 is null and xalg.segment29 is null))
4315 and (gcc.segment30= xalg.segment30 or (gcc.segment30 is null and xalg.segment30 is null)))
4316 WHERE code_combination_id is null;
4317
4318 IF (C_LEVEL_STATEMENT>= g_log_level)
4319 THEN
4320 trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
4321 , p_level => C_LEVEL_STATEMENT
4322 , p_module => v_module);
4323 END IF;
4324
4325 update xla_ae_lines_gt temp
4326 SET code_combination_id =
4327 xla_ae_code_combination_pkg.GetCcid(
4328 temp.segment1
4329 ,temp.segment2
4330 ,temp.segment3
4331 ,temp.segment4
4332 ,temp.segment5
4333 ,temp.segment6
4334 ,temp.segment7
4335 ,temp.segment8
4336 ,temp.segment9
4337 ,temp.segment10
4338 ,temp.segment11
4339 ,temp.segment12
4340 ,temp.segment13
4341 ,temp.segment14
4342 ,temp.segment15
4343 ,temp.segment16
4344 ,temp.segment17
4345 ,temp.segment18
4346 ,temp.segment19
4347 ,temp.segment20
4348 ,temp.segment21
4349 ,temp.segment22
4350 ,temp.segment23
4351 ,temp.segment24
4352 ,temp.segment25
4353 ,temp.segment26
4354 ,temp.segment27
4355 ,temp.segment28
4356 ,temp.segment29
4357 ,temp.segment30
4358 ,temp.ccid_coa_id
4359 )
4360 WHERE temp.code_combination_id IS NULL;
4361
4362 IF (C_LEVEL_STATEMENT>= g_log_level)
4363 THEN
4364 trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
4365 , p_level => C_LEVEL_STATEMENT
4366 , p_module => v_module);
4367 END IF;
4368
4369 OPEN c_null_ccid;
4370 fetch c_null_ccid into v_temp;
4371 CLOSE c_null_ccid;
4372
4373 IF(v_temp is not null) THEN
4374 IF (C_LEVEL_STATEMENT>= g_log_level)
4375 THEN
4376 trace( p_msg => 'raise MissingCCIDError'
4377 , p_level => C_LEVEL_STATEMENT
4378 , p_module => v_module);
4379 END IF;
4380 raise MissingCCIDError;
4381 END IF;
4382
4383 IF (C_LEVEL_PROCEDURE>= g_log_level)
4384 THEN
4385 trace( p_msg => 'End of '||v_function
4386 , p_level => C_LEVEL_PROCEDURE
4387 , p_module => v_module);
4388 END IF;
4389
4390 END populate_ccid_to_gt;
4391
4392 PROCEDURE process_accounting_mapping(
4393 p_application_id IN NUMBER
4394 ,p_event_id IN NUMBER) IS
4395 v_function VARCHAR2(240);
4396 v_module VARCHAR2(240);
4397 cursor c_null_ccid is
4398 select 1
4399 from xla_ae_lines_gt
4400 where code_combination_id is null;
4401 v_temp NUMBER;
4402 begin
4403 v_function := 'xla_third_party_merge.process_accounting_mapping';
4404 v_module := C_DEFAULT_MODULE||'.process_accounting_mapping';
4405
4406 -- Log the function entry, the passed parameters and their values
4407 IF (C_LEVEL_PROCEDURE >= g_log_level)
4408 THEN
4409 trace( p_msg => 'BEGIN - ' || v_function
4410 , p_level => C_LEVEL_PROCEDURE
4411 , p_module => v_module);
4412 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
4413
4414 update xla_ae_lines_gt xalg
4415 set ( code_combination_id
4416 ,ccid_coa_id
4417 ,segment1
4418 ,segment2
4419 ,segment3
4420 ,segment4
4421 ,segment5
4422 ,segment6
4423 ,segment7
4424 ,segment8
4425 ,segment9
4426 ,segment10
4427 ,segment11
4428 ,segment12
4429 ,segment13
4430 ,segment14
4431 ,segment15
4432 ,segment16
4433 ,segment17
4434 ,segment18
4435 ,segment19
4436 ,segment20
4437 ,segment21
4438 ,segment22
4439 ,segment23
4440 ,segment24
4441 ,segment25
4442 ,segment26
4443 ,segment27
4444 ,segment28
4445 ,segment29
4446 ,segment30) =
4447 (select null
4448 ,gcc.chart_of_accounts_id
4449 ,decode(segment_code, 'SEGMENT1', to_value, gcc.segment1)
4450 ,decode(segment_code, 'SEGMENT2', to_value, gcc.segment2)
4451 ,decode(segment_code, 'SEGMENT3', to_value, gcc.segment3)
4452 ,decode(segment_code, 'SEGMENT4', to_value, gcc.segment4)
4453 ,decode(segment_code, 'SEGMENT5', to_value, gcc.segment5)
4454 ,decode(segment_code, 'SEGMENT6', to_value, gcc.segment6)
4455 ,decode(segment_code, 'SEGMENT7', to_value, gcc.segment7)
4456 ,decode(segment_code, 'SEGMENT8', to_value, gcc.segment8)
4457 ,decode(segment_code, 'SEGMENT9', to_value, gcc.segment9)
4458 ,decode(segment_code, 'SEGMENT10', to_value, gcc.segment10)
4459 ,decode(segment_code, 'SEGMENT11', to_value, gcc.segment11)
4460 ,decode(segment_code, 'SEGMENT12', to_value, gcc.segment12)
4461 ,decode(segment_code, 'SEGMENT13', to_value, gcc.segment13)
4462 ,decode(segment_code, 'SEGMENT14', to_value, gcc.segment14)
4463 ,decode(segment_code, 'SEGMENT15', to_value, gcc.segment15)
4464 ,decode(segment_code, 'SEGMENT16', to_value, gcc.segment16)
4465 ,decode(segment_code, 'SEGMENT17', to_value, gcc.segment17)
4466 ,decode(segment_code, 'SEGMENT18', to_value, gcc.segment18)
4467 ,decode(segment_code, 'SEGMENT19', to_value, gcc.segment19)
4468 ,decode(segment_code, 'SEGMENT20', to_value, gcc.segment20)
4469 ,decode(segment_code, 'SEGMENT21', to_value, gcc.segment21)
4470 ,decode(segment_code, 'SEGMENT22', to_value, gcc.segment22)
4471 ,decode(segment_code, 'SEGMENT23', to_value, gcc.segment23)
4472 ,decode(segment_code, 'SEGMENT24', to_value, gcc.segment24)
4473 ,decode(segment_code, 'SEGMENT25', to_value, gcc.segment25)
4474 ,decode(segment_code, 'SEGMENT26', to_value, gcc.segment26)
4475 ,decode(segment_code, 'SEGMENT27', to_value, gcc.segment27)
4476 ,decode(segment_code, 'SEGMENT28', to_value, gcc.segment28)
4477 ,decode(segment_code, 'SEGMENT29', to_value, gcc.segment29)
4478 ,decode(segment_code, 'SEGMENT30', to_value, gcc.segment30)
4479 from xla_merge_seg_maps map
4480 ,gl_code_combinations gcc
4481 ,XLA_LEDGER_RELATIONSHIPS_V rs
4482 ,gl_ledgers gld
4483 where map.application_id = p_application_id
4484 and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
4485 AND rs.ledger_id = gld.ledger_id
4486 AND gld.complete_flag = 'Y'
4487 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
4488 AND rs.ledger_id = xalg.ledger_id
4489 AND DECODE(rs.LEDGER_CATEGORY_CODE
4490 , 'ALC', rs.PRIMARY_LEDGER_ID
4491 , rs.LEDGER_ID) = map.ledger_id
4492 and map.event_id = p_event_id
4493 AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
4494 ,'SEGMENT2', gcc.segment2
4495 ,'SEGMENT3', gcc.segment3
4496 ,'SEGMENT4', gcc.segment4
4497 ,'SEGMENT5', gcc.segment5
4498 ,'SEGMENT6', gcc.segment6
4499 ,'SEGMENT7', gcc.segment7
4500 ,'SEGMENT8', gcc.segment8
4501 ,'SEGMENT9', gcc.segment9
4502 ,'SEGMENT10', gcc.segment10
4503 ,'SEGMENT11', gcc.segment11
4504 ,'SEGMENT12', gcc.segment12
4505 ,'SEGMENT13', gcc.segment13
4506 ,'SEGMENT14', gcc.segment14
4507 ,'SEGMENT15', gcc.segment15
4508 ,'SEGMENT16', gcc.segment16
4509 ,'SEGMENT17', gcc.segment17
4510 ,'SEGMENT18', gcc.segment18
4511 ,'SEGMENT19', gcc.segment19
4512 ,'SEGMENT20', gcc.segment20
4513 ,'SEGMENT21', gcc.segment21
4514 ,'SEGMENT22', gcc.segment22
4515 ,'SEGMENT23', gcc.segment23
4516 ,'SEGMENT24', gcc.segment24
4517 ,'SEGMENT25', gcc.segment25
4518 ,'SEGMENT26', gcc.segment26
4519 ,'SEGMENT27', gcc.segment27
4520 ,'SEGMENT28', gcc.segment28
4521 ,'SEGMENT29', gcc.segment29
4522 ,'SEGMENT30', gcc.segment30)
4523 = map.FROM_VALUE
4524 and gcc.code_combination_id = xalg.code_combination_id
4525 )
4526 where reversal_code in ('REBOOKING', 'TRANSFER_BALANCE')
4527 AND exists
4528 (select 1
4529 from xla_merge_seg_maps map
4530 ,gl_code_combinations gcc
4531 ,XLA_LEDGER_RELATIONSHIPS_V rs
4532 ,gl_ledgers gld
4533 where map.application_id = p_application_id
4534 and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
4535 AND rs.ledger_id = gld.ledger_id
4536 AND gld.complete_flag = 'Y'
4537 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
4538 AND rs.ledger_id = xalg.ledger_id
4539 AND DECODE(rs.LEDGER_CATEGORY_CODE
4540 , 'ALC', rs.PRIMARY_LEDGER_ID
4541 , rs.LEDGER_ID) = map.ledger_id
4542 and map.event_id = p_event_id
4543 AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
4544 ,'SEGMENT2', gcc.segment2
4545 ,'SEGMENT3', gcc.segment3
4546 ,'SEGMENT4', gcc.segment4
4547 ,'SEGMENT5', gcc.segment5
4548 ,'SEGMENT6', gcc.segment6
4549 ,'SEGMENT7', gcc.segment7
4550 ,'SEGMENT8', gcc.segment8
4551 ,'SEGMENT9', gcc.segment9
4552 ,'SEGMENT10', gcc.segment10
4553 ,'SEGMENT11', gcc.segment11
4554 ,'SEGMENT12', gcc.segment12
4555 ,'SEGMENT13', gcc.segment13
4556 ,'SEGMENT14', gcc.segment14
4557 ,'SEGMENT15', gcc.segment15
4558 ,'SEGMENT16', gcc.segment16
4559 ,'SEGMENT17', gcc.segment17
4560 ,'SEGMENT18', gcc.segment18
4561 ,'SEGMENT19', gcc.segment19
4562 ,'SEGMENT20', gcc.segment20
4563 ,'SEGMENT21', gcc.segment21
4564 ,'SEGMENT22', gcc.segment22
4565 ,'SEGMENT23', gcc.segment23
4566 ,'SEGMENT24', gcc.segment24
4567 ,'SEGMENT25', gcc.segment25
4568 ,'SEGMENT26', gcc.segment26
4569 ,'SEGMENT27', gcc.segment27
4570 ,'SEGMENT28', gcc.segment28
4571 ,'SEGMENT29', gcc.segment29
4572 ,'SEGMENT30', gcc.segment30)
4573 = map.FROM_VALUE
4574 and gcc.code_combination_id = xalg.code_combination_id);
4575
4576
4577 IF (C_LEVEL_STATEMENT>= g_log_level)
4578 THEN
4579 trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
4580 , p_level => C_LEVEL_STATEMENT
4581 , p_module => v_module);
4582 END IF;
4583
4584 populate_ccid_to_gt;
4585
4586 IF (C_LEVEL_PROCEDURE>= g_log_level)
4587 THEN
4588 trace( p_msg => 'End of '||v_function
4589 , p_level => C_LEVEL_PROCEDURE
4590 , p_module => v_module);
4591 END IF;
4592
4593 END process_accounting_mapping;
4594
4595
4596 -- private procedure, populate xla_ae_lines_gt table
4597 -- for the purpose of account mapping
4598 FUNCTION populate_gt_for_mapping(
4599 p_application_id IN NUMBER
4600 ,p_event_id IN NUMBER
4601 ,p_merge_type IN VARCHAR2
4602 ,p_old_site_id IN NUMBER
4603 ,p_old_party_id IN NUMBER
4604 ,p_new_site_id IN NUMBER
4605 ,p_new_party_id IN NUMBER
4606 ,p_party_type IN VARCHAR2
4607 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
4608 ,p_array_reversal_option IN t_varchar30_array
4609 ,p_array_merge_option IN t_varchar30_array) RETURN NUMBER IS
4610 v_function VARCHAR2(240);
4611 v_module VARCHAR2(240);
4612
4613 v_row_count NUMBER;
4614
4615 BEGIN
4616
4617 v_function := 'xla_third_party_merge.populate_gt_for_mapping';
4618 v_module := C_DEFAULT_MODULE||'.populate_gt_for_mapping';
4619
4620 -- Log the function entry, the passed parameters and their values
4621 IF (C_LEVEL_PROCEDURE >= g_log_level)
4622 THEN
4623 trace( p_msg => 'BEGIN - ' || v_function
4624 , p_level => C_LEVEL_PROCEDURE
4625 , p_module => v_module);
4626 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
4627
4628
4629 IF(p_merge_type = 'PARTIAL_MERGE') THEN
4630 FORALL i IN 1..p_array_ledger_id.count
4631 INSERT INTO xla_ae_lines_gt (
4632 ae_header_id
4633 ,ae_line_num
4634 ,temp_line_num
4635 ,inherit_desc_flag
4636 ,header_num
4637 ,ledger_id
4638 ,ref_ae_header_id
4639 ,ccid_coa_id
4640 ,segment1
4641 ,segment2
4642 ,segment3
4643 ,segment4
4644 ,segment5
4645 ,segment6
4646 ,segment7
4647 ,segment8
4648 ,segment9
4649 ,segment10
4650 ,segment11
4651 ,segment12
4652 ,segment13
4653 ,segment14
4654 ,segment15
4655 ,segment16
4656 ,segment17
4657 ,segment18
4658 ,segment19
4659 ,segment20
4660 ,segment21
4661 ,segment22
4662 ,segment23
4663 ,segment24
4664 ,segment25
4665 ,segment26
4666 ,segment27
4667 ,segment28
4668 ,segment29
4669 ,segment30)
4670 (SELECT
4671 ael.ae_header_id
4672 ,ael.ae_line_num
4673 ,ael.ae_line_num
4674 ,'N'
4675 ,ael.ae_header_id
4676 ,ael.ledger_id
4677 ,ael.ae_header_id
4678 ,gcc.chart_of_accounts_id
4679 ,decode(segment_code, 'SEGMENT1', to_value, gcc.segment1)
4680 ,decode(segment_code, 'SEGMENT2', to_value, gcc.segment2)
4681 ,decode(segment_code, 'SEGMENT3', to_value, gcc.segment3)
4682 ,decode(segment_code, 'SEGMENT4', to_value, gcc.segment4)
4683 ,decode(segment_code, 'SEGMENT5', to_value, gcc.segment5)
4684 ,decode(segment_code, 'SEGMENT6', to_value, gcc.segment6)
4685 ,decode(segment_code, 'SEGMENT7', to_value, gcc.segment7)
4686 ,decode(segment_code, 'SEGMENT8', to_value, gcc.segment8)
4687 ,decode(segment_code, 'SEGMENT9', to_value, gcc.segment9)
4688 ,decode(segment_code, 'SEGMENT10', to_value, gcc.segment10)
4689 ,decode(segment_code, 'SEGMENT11', to_value, gcc.segment11)
4690 ,decode(segment_code, 'SEGMENT12', to_value, gcc.segment12)
4691 ,decode(segment_code, 'SEGMENT13', to_value, gcc.segment13)
4692 ,decode(segment_code, 'SEGMENT14', to_value, gcc.segment14)
4693 ,decode(segment_code, 'SEGMENT15', to_value, gcc.segment15)
4694 ,decode(segment_code, 'SEGMENT16', to_value, gcc.segment16)
4695 ,decode(segment_code, 'SEGMENT17', to_value, gcc.segment17)
4696 ,decode(segment_code, 'SEGMENT18', to_value, gcc.segment18)
4697 ,decode(segment_code, 'SEGMENT19', to_value, gcc.segment19)
4698 ,decode(segment_code, 'SEGMENT20', to_value, gcc.segment20)
4699 ,decode(segment_code, 'SEGMENT21', to_value, gcc.segment21)
4700 ,decode(segment_code, 'SEGMENT22', to_value, gcc.segment22)
4701 ,decode(segment_code, 'SEGMENT23', to_value, gcc.segment23)
4702 ,decode(segment_code, 'SEGMENT24', to_value, gcc.segment24)
4703 ,decode(segment_code, 'SEGMENT25', to_value, gcc.segment25)
4704 ,decode(segment_code, 'SEGMENT26', to_value, gcc.segment26)
4705 ,decode(segment_code, 'SEGMENT27', to_value, gcc.segment27)
4706 ,decode(segment_code, 'SEGMENT28', to_value, gcc.segment28)
4707 ,decode(segment_code, 'SEGMENT29', to_value, gcc.segment29)
4708 ,decode(segment_code, 'SEGMENT30', to_value, gcc.segment30)
4709 FROM xla_merge_seg_maps map
4710 ,gl_code_combinations gcc
4711 ,xla_ae_lines ael
4712 ,xla_ae_headers aeh
4713 ,XLA_PARTIAL_MERGE_TXNS xpmt
4714 ,XLA_LEDGER_RELATIONSHIPS_V rs
4715 ,gl_ledgers gld
4716 WHERE ael.PARTY_ID = p_old_party_id
4717 AND (p_old_site_id IS NULL
4718 OR ael.PARTY_SITE_ID = p_old_site_id)
4719 AND ael.PARTY_TYPE_CODE = p_party_type
4720 AND ael.APPLICATION_ID = aeh.APPLICATION_ID
4721 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
4722 AND aeh.APPLICATION_ID = p_application_id
4723 AND aeh.accounting_entry_status_code = 'N'
4724 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4725 AND aeh.parent_ae_header_id IS NOT NULL
4726 AND p_array_merge_option(i) = 'TRANSFER'
4727 AND xpmt.APPLICATION_ID = p_application_id
4728 AND xpmt.MERGE_EVENT_ID = p_event_id
4729 AND xpmt.ENTITY_ID = aeh.ENTITY_ID
4730 AND map.application_id = p_application_id
4731 and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
4732 AND rs.ledger_id = gld.ledger_id
4733 AND gld.complete_flag = 'Y'
4734 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
4735 AND rs.ledger_id = aeh.ledger_id
4736 AND DECODE(rs.LEDGER_CATEGORY_CODE
4737 , 'ALC', rs.PRIMARY_LEDGER_ID
4738 , rs.LEDGER_ID) = map.ledger_id
4739 and map.event_id = p_event_id
4740 AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
4741 ,'SEGMENT2', gcc.segment2
4742 ,'SEGMENT3', gcc.segment3
4743 ,'SEGMENT4', gcc.segment4
4744 ,'SEGMENT5', gcc.segment5
4745 ,'SEGMENT6', gcc.segment6
4746 ,'SEGMENT7', gcc.segment7
4747 ,'SEGMENT8', gcc.segment8
4748 ,'SEGMENT9', gcc.segment9
4749 ,'SEGMENT10', gcc.segment10
4750 ,'SEGMENT11', gcc.segment11
4751 ,'SEGMENT12', gcc.segment12
4752 ,'SEGMENT13', gcc.segment13
4753 ,'SEGMENT14', gcc.segment14
4754 ,'SEGMENT15', gcc.segment15
4755 ,'SEGMENT16', gcc.segment16
4756 ,'SEGMENT17', gcc.segment17
4757 ,'SEGMENT18', gcc.segment18
4758 ,'SEGMENT19', gcc.segment19
4759 ,'SEGMENT20', gcc.segment20
4760 ,'SEGMENT21', gcc.segment21
4761 ,'SEGMENT22', gcc.segment22
4762 ,'SEGMENT23', gcc.segment23
4763 ,'SEGMENT24', gcc.segment24
4764 ,'SEGMENT25', gcc.segment25
4765 ,'SEGMENT26', gcc.segment26
4766 ,'SEGMENT27', gcc.segment27
4767 ,'SEGMENT28', gcc.segment28
4768 ,'SEGMENT29', gcc.segment29
4769 ,'SEGMENT30', gcc.segment30)
4770 = map.FROM_VALUE
4771 and gcc.code_combination_id = ael.code_combination_id);
4772 v_row_count :=SQL%ROWCOUNT;
4773 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4774 trace( p_msg => '# of lines inserted for mapping change:'||to_char(v_row_count)
4775 , p_level => C_LEVEL_STATEMENT
4776 , p_module => v_module);
4777 END IF;
4778 ELSE
4779
4780 FORALL i IN 1..p_array_ledger_id.count
4781 INSERT INTO xla_ae_lines_gt (
4782 ae_header_id
4783 ,ae_line_num
4784 ,temp_line_num
4785 ,inherit_desc_flag
4786 ,header_num
4787 ,ledger_id
4788 ,ref_ae_header_id
4789 ,ccid_coa_id
4790 ,segment1
4791 ,segment2
4792 ,segment3
4793 ,segment4
4794 ,segment5
4795 ,segment6
4796 ,segment7
4797 ,segment8
4798 ,segment9
4799 ,segment10
4800 ,segment11
4801 ,segment12
4802 ,segment13
4803 ,segment14
4804 ,segment15
4805 ,segment16
4806 ,segment17
4807 ,segment18
4808 ,segment19
4809 ,segment20
4810 ,segment21
4811 ,segment22
4812 ,segment23
4813 ,segment24
4814 ,segment25
4815 ,segment26
4816 ,segment27
4817 ,segment28
4818 ,segment29
4819 ,segment30)
4820 (SELECT
4821 ael.ae_header_id
4822 ,ael.ae_line_num
4823 ,ael.ae_line_num
4824 ,'N'
4825 ,ael.ae_header_id
4826 ,ael.ledger_id
4827 ,ael.ae_header_id
4828 ,gcc.chart_of_accounts_id
4829 ,decode(segment_code, 'SEGMENT1', to_value, gcc.segment1)
4830 ,decode(segment_code, 'SEGMENT2', to_value, gcc.segment2)
4831 ,decode(segment_code, 'SEGMENT3', to_value, gcc.segment3)
4832 ,decode(segment_code, 'SEGMENT4', to_value, gcc.segment4)
4833 ,decode(segment_code, 'SEGMENT5', to_value, gcc.segment5)
4834 ,decode(segment_code, 'SEGMENT6', to_value, gcc.segment6)
4835 ,decode(segment_code, 'SEGMENT7', to_value, gcc.segment7)
4836 ,decode(segment_code, 'SEGMENT8', to_value, gcc.segment8)
4837 ,decode(segment_code, 'SEGMENT9', to_value, gcc.segment9)
4838 ,decode(segment_code, 'SEGMENT10', to_value, gcc.segment10)
4839 ,decode(segment_code, 'SEGMENT11', to_value, gcc.segment11)
4840 ,decode(segment_code, 'SEGMENT12', to_value, gcc.segment12)
4841 ,decode(segment_code, 'SEGMENT13', to_value, gcc.segment13)
4842 ,decode(segment_code, 'SEGMENT14', to_value, gcc.segment14)
4843 ,decode(segment_code, 'SEGMENT15', to_value, gcc.segment15)
4844 ,decode(segment_code, 'SEGMENT16', to_value, gcc.segment16)
4845 ,decode(segment_code, 'SEGMENT17', to_value, gcc.segment17)
4846 ,decode(segment_code, 'SEGMENT18', to_value, gcc.segment18)
4847 ,decode(segment_code, 'SEGMENT19', to_value, gcc.segment19)
4848 ,decode(segment_code, 'SEGMENT20', to_value, gcc.segment20)
4849 ,decode(segment_code, 'SEGMENT21', to_value, gcc.segment21)
4850 ,decode(segment_code, 'SEGMENT22', to_value, gcc.segment22)
4851 ,decode(segment_code, 'SEGMENT23', to_value, gcc.segment23)
4852 ,decode(segment_code, 'SEGMENT24', to_value, gcc.segment24)
4853 ,decode(segment_code, 'SEGMENT25', to_value, gcc.segment25)
4854 ,decode(segment_code, 'SEGMENT26', to_value, gcc.segment26)
4855 ,decode(segment_code, 'SEGMENT27', to_value, gcc.segment27)
4856 ,decode(segment_code, 'SEGMENT28', to_value, gcc.segment28)
4857 ,decode(segment_code, 'SEGMENT29', to_value, gcc.segment29)
4858 ,decode(segment_code, 'SEGMENT30', to_value, gcc.segment30)
4859 FROM xla_merge_seg_maps map
4860 ,gl_code_combinations gcc
4861 ,xla_ae_lines ael
4862 ,xla_ae_headers aeh
4863 ,XLA_LEDGER_RELATIONSHIPS_V rs
4864 ,gl_ledgers gld
4865 WHERE ael.PARTY_ID = p_old_party_id
4866 AND (p_old_site_id IS NULL
4867 OR ael.PARTY_SITE_ID = p_old_site_id)
4868 AND ael.PARTY_TYPE_CODE = p_party_type
4869 AND ael.APPLICATION_ID = aeh.APPLICATION_ID
4870 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
4871 AND aeh.APPLICATION_ID = p_application_id
4872 AND aeh.accounting_entry_status_code = 'N'
4873 AND aeh.LEDGER_ID = p_array_ledger_id(i)
4874 AND aeh.parent_ae_header_id IS NOT NULL
4875 AND p_array_merge_option(i) = 'TRANSFER'
4876 AND map.application_id = p_application_id
4877 and rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
4878 AND rs.ledger_id = gld.ledger_id
4879 AND gld.complete_flag = 'Y'
4880 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
4881 AND rs.ledger_id = aeh.ledger_id
4882 AND DECODE(rs.LEDGER_CATEGORY_CODE
4883 , 'ALC', rs.PRIMARY_LEDGER_ID
4884 , rs.LEDGER_ID) = map.ledger_id
4885 and map.event_id = p_event_id
4886 AND decode(map.segment_code, 'SEGMENT1', gcc.segment1
4887 ,'SEGMENT2', gcc.segment2
4888 ,'SEGMENT3', gcc.segment3
4889 ,'SEGMENT4', gcc.segment4
4890 ,'SEGMENT5', gcc.segment5
4891 ,'SEGMENT6', gcc.segment6
4892 ,'SEGMENT7', gcc.segment7
4893 ,'SEGMENT8', gcc.segment8
4894 ,'SEGMENT9', gcc.segment9
4895 ,'SEGMENT10', gcc.segment10
4896 ,'SEGMENT11', gcc.segment11
4897 ,'SEGMENT12', gcc.segment12
4898 ,'SEGMENT13', gcc.segment13
4899 ,'SEGMENT14', gcc.segment14
4900 ,'SEGMENT15', gcc.segment15
4901 ,'SEGMENT16', gcc.segment16
4902 ,'SEGMENT17', gcc.segment17
4903 ,'SEGMENT18', gcc.segment18
4904 ,'SEGMENT19', gcc.segment19
4905 ,'SEGMENT20', gcc.segment20
4906 ,'SEGMENT21', gcc.segment21
4907 ,'SEGMENT22', gcc.segment22
4908 ,'SEGMENT23', gcc.segment23
4909 ,'SEGMENT24', gcc.segment24
4910 ,'SEGMENT25', gcc.segment25
4911 ,'SEGMENT26', gcc.segment26
4912 ,'SEGMENT27', gcc.segment27
4913 ,'SEGMENT28', gcc.segment28
4914 ,'SEGMENT29', gcc.segment29
4915 ,'SEGMENT30', gcc.segment30)
4916 = map.FROM_VALUE
4917 and gcc.code_combination_id = ael.code_combination_id);
4918 v_row_count :=SQL%ROWCOUNT;
4919 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
4920 trace( p_msg => '# of lines inserted for mapping change:'||to_char(v_row_count)
4921 , p_level => C_LEVEL_STATEMENT
4922 , p_module => v_module);
4923 END IF;
4924 END IF;
4925
4926 RETURN v_row_count;
4927
4928 END populate_gt_for_mapping;
4929
4930
4931 -- this function process the account mapping for incomplete entries
4932 PROCEDURE process_incomplete_acct_map(
4933 p_application_id IN NUMBER
4934 ,p_event_id IN NUMBER
4935 ,p_event_merge_option IN VARCHAR2
4936 ,p_entity_id IN NUMBER
4937 ,p_merge_date IN DATE
4938 ,p_merge_type IN VARCHAR2
4939 ,p_old_site_id IN NUMBER
4940 ,p_old_party_id IN NUMBER
4941 ,p_new_site_id IN NUMBER
4942 ,p_new_party_id IN NUMBER
4943 ,p_party_type IN VARCHAR2
4944 ,p_array_ledger_id IN xla_accounting_cache_pkg.t_array_ledger_id
4945 ,p_array_ledger_category IN t_varchar30_array
4946 ,p_array_reversal_option IN t_varchar30_array
4947 ,p_array_merge_option IN t_varchar30_array) IS
4948 v_function VARCHAR2(240);
4949 v_module VARCHAR2(240);
4950 cursor c_null_ccid is
4951 select 1
4952 from xla_ae_lines_gt
4953 where code_combination_id is null;
4954 v_row_count NUMBER;
4955 begin
4956 v_function := 'xla_third_party_merge.process_incomplete_acct_map';
4957 v_module := C_DEFAULT_MODULE||'.process_incomplete_acct_map';
4958
4959 -- Log the function entry, the passed parameters and their values
4960 IF (C_LEVEL_PROCEDURE >= g_log_level)
4961 THEN
4962 trace( p_msg => 'BEGIN - ' || v_function
4963 , p_level => C_LEVEL_PROCEDURE
4964 , p_module => v_module);
4965 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
4966
4967 v_row_count := populate_gt_for_mapping(
4968 p_application_id => p_application_id
4969 ,p_event_id => p_event_id
4970 ,p_merge_type => p_merge_type
4971 ,p_old_site_id => p_old_site_id
4972 ,p_old_party_id => p_old_party_id
4973 ,p_new_site_id => p_new_site_id
4974 ,p_new_party_id => p_new_party_id
4975 ,p_party_type => p_party_type
4976 ,p_array_ledger_id => p_array_ledger_id
4977 ,p_array_reversal_option => p_array_reversal_option
4978 ,p_array_merge_option => p_array_merge_option);
4979
4980 IF (C_LEVEL_STATEMENT>= g_log_level)
4981 THEN
4982 trace( p_msg => 'function populate_gt_for_mapping returns: '||to_char(v_row_count)
4983 , p_level => C_LEVEL_STATEMENT
4984 , p_module => v_module);
4985 END IF;
4986
4987 IF(v_row_count = 0) THEN
4988 RETURN;
4989 -- there is no row that need account mapping
4990 END IF;
4991
4992 -- populate the ccid into gt table
4993 populate_ccid_to_gt;
4994
4995 IF (C_LEVEL_STATEMENT>= g_log_level)
4996 THEN
4997 trace( p_msg => 'after procedure populate_ccid_to_gt'
4998 , p_level => C_LEVEL_STATEMENT
4999 , p_module => v_module);
5000 END IF;
5001
5002 -- populate the ccid back to the lines table
5003
5004 UPDATE
5005 (SELECT xalg.code_combination_id
5006 , xal.code_combination_id code_combination_id1
5007 FROM xla_ae_lines_gt xalg
5008 , xla_ae_lines xal
5009 WHERE xalg.ae_header_id = xal.ae_header_id
5010 AND xalg.ae_line_num = xal.ae_line_num
5011 AND xal.application_id = p_application_id
5012 AND xalg.temp_line_num = xal.ae_line_num
5013 AND xalg.ref_ae_header_id = xal.ae_header_id
5014 AND xalg.ledger_id = xal.ledger_id
5015 AND xalg.header_num = xal.ae_header_id
5016 AND xalg.inherit_desc_flag = 'N')
5017 SET code_combination_id1 = code_combination_id;
5018
5019 IF (C_LEVEL_STATEMENT>= g_log_level)
5020 THEN
5021 trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
5022 , p_level => C_LEVEL_STATEMENT
5023 , p_module => v_module);
5024 END IF;
5025
5026 IF (C_LEVEL_PROCEDURE>= g_log_level)
5027 THEN
5028 trace( p_msg => 'End of '||v_function
5029 , p_level => C_LEVEL_PROCEDURE
5030 , p_module => v_module);
5031 END IF;
5032
5033 END process_incomplete_acct_map;
5034
5035
5036
5037
5038 PROCEDURE generate_headers(
5039 p_application_id IN NUMBER
5040 ,p_reverse_header_desc IN VARCHAR2
5041 ,p_accounting_mode IN VARCHAR2
5042 ) IS
5043 v_function VARCHAR2(240);
5044 v_module VARCHAR2(240);
5045 v_query_str VARCHAR2(2000);
5046 begin
5047 v_function := 'xla_third_party_merge.generate_headers';
5048 v_module := C_DEFAULT_MODULE||'.generate_headers';
5049
5050 -- Log the function entry, the passed parameters and their values
5051 IF (C_LEVEL_PROCEDURE >= g_log_level)
5052 THEN
5053 trace( p_msg => 'BEGIN - ' || v_function
5054 , p_level => C_LEVEL_PROCEDURE
5055 , p_module => v_module);
5056 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
5057
5058 INSERT INTO xla_ae_headers_gt
5059 ( ae_header_id
5060 , accounting_entry_status_code
5061 , accounting_entry_type_code
5062 , GL_TRANSFER_STATUS_CODE
5063 , ledger_id
5064 , entity_id
5065 , event_id
5066 , event_type_code
5067 , accounting_date
5068 , je_category_name
5069 , period_name
5070 , description
5071 , balance_type_code
5072 , amb_context_code
5073 , budget_version_id
5074 -- 5103972
5075 -- Used at the end of this procedure to find ae header ids
5076 -- to be stamped on xla_ae_lines_gt
5077 , parent_header_id
5078 )
5079 (select xla_ae_headers_s.nextval
5080 ,p_accounting_mode
5081 ,'MERGE'
5082 ,'N'
5083 ,ledger_id
5084 , entity_id
5085 , event_id
5086 , event_type_code
5087 , accounting_date
5088 , je_category_name
5089 , period_name
5090 , description || p_reverse_header_desc
5091 , balance_type_code
5092 , null
5093 , ae_header_id
5094 , ref_ae_header_id -- 5103972
5095 from
5096 (select distinct xah.ledger_id
5097 , xah.entity_id
5098 , xah.event_id
5099 , xah.event_type_code
5100 , xah.accounting_date
5101 , xah.je_category_name
5102 , xah.period_name
5103 , xah.description
5104 , xah.balance_type_code
5105 , xal.ae_header_id
5106 , xal.ref_ae_header_id -- 5103972
5107 from xla_ae_headers xah
5108 ,xla_ae_lines_gt xal
5109 where xah.application_id = p_application_id
5110 and xah.ae_header_id =xal.ref_ae_header_id
5111 and xal.reversal_code = 'REBOOKING'));
5112
5113 IF (C_LEVEL_STATEMENT>= g_log_level)
5114 THEN
5115 trace( p_msg => '# of header inserted:'||to_char(SQL%ROWCOUNT)
5116 , p_level => C_LEVEL_STATEMENT
5117 , p_module => v_module);
5118 END IF;
5119
5120 UPDATE xla_ae_headers_gt xah
5121 SET (accounting_date, period_name) =
5122 (SELECT start_date, period_name
5123 FROM gl_period_statuses
5124 WHERE ledger_id = xah.ledger_id
5125 AND application_id = 101
5126 AND adjustment_period_flag = 'N'
5127 AND closing_status in ('O', 'F')
5128 AND start_date =
5129 (SELECT min(gps.start_date)
5130 FROM gl_period_statuses gps
5131 WHERE ledger_id = xah.ledger_id
5132 AND application_id = 101
5133 AND adjustment_period_flag = 'N'
5134 AND start_date > xah.accounting_date
5135 AND closing_status in ('O', 'F')))
5136 WHERE period_name in
5137 (SELECT period_name
5138 FROM gl_period_statuses gps2
5139 WHERE gps2.ledger_id = xah.ledger_id
5140 AND gps2.adjustment_period_flag = 'N'
5141 AND gps2.closing_status = 'C'
5142 AND gps2.period_name = xah.period_name);
5143
5144 IF (C_LEVEL_STATEMENT>= g_log_level)
5145 THEN
5146 trace( p_msg => '# of header have gl date updated:'||to_char(SQL%ROWCOUNT)
5147 , p_level => C_LEVEL_STATEMENT
5148 , p_module => v_module);
5149 END IF;
5150
5151 UPDATE xla_ae_lines_gt xal
5152 SET (ae_header_id, accounting_date) =
5153 (SELECT ae_header_id, accounting_date
5154 FROM xla_ae_headers_gt xah
5155 WHERE xal.event_id = xah.event_id
5156 AND xal.ledger_id = xah.ledger_id
5157 -- 5103972
5158 -- Without the following line, this SQL fails as one event_id
5159 -- could have multiple ae headers (mpa).
5160 AND xal.ref_ae_header_id = xah.parent_header_id);
5161
5162 IF (C_LEVEL_STATEMENT>= g_log_level)
5163 THEN
5164 trace( p_msg => '# of lines updated:'||to_char(SQL%ROWCOUNT)
5165 , p_level => C_LEVEL_STATEMENT
5166 , p_module => v_module);
5167 END IF;
5168
5169 IF (C_LEVEL_PROCEDURE>= g_log_level)
5170 THEN
5171 trace( p_msg => 'End of '||v_function
5172 , p_level => C_LEVEL_PROCEDURE
5173 , p_module => v_module);
5174 END IF;
5175
5176 END generate_headers;
5177 -- ----------------------------------------------------------------------------
5178 -- Create third party merge accounting routine
5179 -- ----------------------------------------------------------------------------
5180 PROCEDURE create_accounting
5181 ( x_errbuf OUT NOCOPY VARCHAR2
5182 , x_retcode OUT NOCOPY VARCHAR2
5183 , p_application_id IN INTEGER
5184 , p_event_id IN INTEGER DEFAULT NULL
5185 , p_accounting_mode IN VARCHAR2
5186 , p_transfer_to_gl_flag IN VARCHAR2
5187 , p_post_in_gl_flag IN VARCHAR2
5188 , p_merge_event_set_id IN INTEGER DEFAULT NULL
5189 , p_srs_flag IN VARCHAR2 DEFAULT NULL) IS
5190
5191 v_function VARCHAR2(240);
5192 v_module VARCHAR2(240);
5193 v_message VARCHAR2(2000);
5194 v_dummy VARCHAR2(1);
5195
5196 v_application_name VARCHAR2(240);
5197 v_valuation_method_flag VARCHAR2(1);
5198
5199 -- Commented out for 14773226 , Using dynamic cursor for improving performance
5200 /*
5201 CURSOR mergeEvent_cur IS
5202 SELECT evt.event_id,
5203 evt.event_type_code,
5204 evt.event_date,
5205 evt.process_status_code,
5206 evt.reference_num_1 old_party_id,
5207 evt.reference_num_2 old_site_id,
5208 evt.reference_num_3 new_party_id,
5209 evt.reference_num_4 new_site_id,
5210 p1.third_party_number original_party_number,
5211 s1.third_party_site_code original_site_code,
5212 p2.third_party_number new_party_number,
5213 s2.third_party_site_code new_site_code,
5214 evt.reference_char_1 party_type,
5215 evt.reference_char_2 mapping_flag,
5216 ent.entity_id,
5217 ent.source_application_id,
5218 ent.ledger_id
5219 FROM xla_events evt,
5220 xla_third_parties_v p1,
5221 xla_third_parties_v p2,
5222 xla_third_party_sites_v s1,
5223 xla_third_party_sites_v s2,
5224 xla_transaction_entities ent,
5225 xla_ledger_options lgopt,
5226 xla_launch_options lnopt
5227 WHERE
5228 */ -- 14773226
5229 /*(p_event_id IS NULL OR evt.EVENT_ID = p_event_id)
5230 AND ( p_merge_event_set_id IS NULL
5231 OR evt.MERGE_EVENT_SET_ID = p_merge_event_set_id)*/
5232 /* -- 14773226
5233 evt.EVENT_ID = nvl(p_event_id,evt.EVENT_ID)
5234 AND evt.MERGE_EVENT_SET_ID = nvl(p_merge_event_set_id,evt.MERGE_EVENT_SET_ID)
5235 AND evt.APPLICATION_ID = p_application_id
5236 AND evt.EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE')
5237 AND evt.PROCESS_STATUS_CODE not in ('P','F') -- Modified by krsankar for RCA bug 8396757
5238 AND p1.THIRD_PARTY_ID = evt.REFERENCE_NUM_1
5239 AND p1.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
5240 AND p2.THIRD_PARTY_ID = evt.REFERENCE_NUM_3
5241 AND p2.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
5242 AND s1.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_1
5243 AND s1.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_2
5244 AND s1.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
5245 AND s2.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_3
5246 AND s2.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_4
5247 AND s2.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
5248 AND ent.APPLICATION_ID = evt.APPLICATION_ID
5249 AND ent.ENTITY_ID = evt.ENTITY_ID
5250 AND ent.ENTITY_CODE = 'THIRD_PARTY_MERGE'
5251 AND lgopt.APPLICATION_ID = ent.APPLICATION_ID
5252 AND lgopt.LEDGER_ID = ent.LEDGER_ID
5253 AND lgopt.ENABLED_FLAG = 'Y'
5254 AND lnopt.APPLICATION_ID = lgopt.APPLICATION_ID
5255 AND lnopt.LEDGER_ID = lgopt.LEDGER_ID
5256 AND ( lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = 'Y'
5257 OR (lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = 'N'
5258 AND lnopt.ACCOUNTING_MODE_CODE = p_accounting_mode))
5259 AND ( lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = 'Y'
5260 OR (lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = 'N'
5261 AND lnopt.SUBMIT_TRANSFER_TO_GL_FLAG
5262 = p_transfer_to_gl_flag))
5263 AND ( lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = 'Y'
5264 OR (lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = 'N'
5265 AND lnopt.SUBMIT_GL_POST_FLAG = p_post_in_gl_flag))
5266 AND ( g_use_ledger_security = 'N'
5267 OR (g_use_ledger_security = 'Y'
5268 AND NOT EXISTS
5269 (SELECT 'Ledger without access'
5270 FROM XLA_LEDGER_RELATIONSHIPS_V rs,
5271 XLA_LEDGER_OPTIONS lgopt2,
5272 gl_ledgers gld
5273 WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
5274 AND rs.ledger_id = gld.ledger_id
5275 AND gld.complete_flag = 'Y'
5276 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
5277 AND DECODE(v_valuation_method_flag
5278 , 'N', rs.PRIMARY_LEDGER_ID
5279 , DECODE(rs.LEDGER_CATEGORY_CODE
5280 , 'ALC', rs.PRIMARY_LEDGER_ID
5281 , rs.LEDGER_ID)) = lgopt.LEDGER_ID
5282 AND DECODE(rs.LEDGER_CATEGORY_CODE
5283 , 'ALC', rs.PRIMARY_LEDGER_ID
5284 , rs.LEDGER_ID) = lgopt2.LEDGER_ID
5285 AND lgopt2.APPLICATION_ID = lgopt.APPLICATION_ID
5286 AND lgopt2.ENABLED_FLAG = 'Y'
5287 AND lgopt2.MERGE_ACCT_OPTION_CODE <> 'NONE'
5288 AND rs.LEDGER_ID NOT IN
5289 (SELECT asa.LEDGER_ID
5290 FROM GL_ACCESS_SET_ASSIGNMENTS asa
5291 WHERE asa.ACCESS_SET_ID
5292 IN (g_access_set_id, g_sec_access_set_id)))))
5293 ORDER BY evt.EVENT_DATE, evt.EVENT_ID;
5294 */
5295
5296 v_event_id NUMBER(15);
5297 v_merge_type VARCHAR2(30);
5298 v_merge_date DATE;
5299 v_process_status VARCHAR2(1);
5300 v_old_party_id NUMBER(15);
5301 v_old_site_id NUMBER(15);
5302 v_new_party_id NUMBER(15);
5303 v_new_site_id NUMBER(15);
5304 -- v_original_desc_token VARCHAR2(1000);
5305 v_original_party_number xla_third_parties_v.third_party_number%TYPE;
5306 v_original_site_code xla_third_party_sites_v.third_party_site_code%TYPE;
5307 -- v_new_desc_token VARCHAR2(1000);
5308 v_new_party_number xla_third_parties_v.third_party_number%TYPE;
5309 v_new_site_code xla_third_party_sites_v.third_party_site_code%TYPE;
5310 v_party_type VARCHAR2(1);
5311 v_mapping_flag VARCHAR2(1);
5312 v_entity_id NUMBER(15);
5313 v_src_appl_id NUMBER(15);
5314 v_event_ledger_id NUMBER(15);
5315
5316 v_array_ledger_id xla_accounting_cache_pkg.t_array_ledger_id;
5317 v_array_ledger_category t_varchar30_array;
5318 v_array_rounding_rule_code t_varchar30_array;
5319 v_array_mau t_number_array;
5320 v_array_currency_code t_varchar30_array;
5321 v_array_reversal_option t_varchar30_array;
5322 v_array_merge_option t_varchar30_array;
5323 v_array_submit_transfer t_varchar1_array;
5324
5325 v_event_merge_option VARCHAR2(30);
5326 v_ael_desc1 VARCHAR2(1996);
5327 v_ael_desc2 VARCHAR2(1996);
5328 v_ael_desc3 VARCHAR2(1996);
5329 v_ael_desc4 VARCHAR2(1996);
5330 v_processed_event_count NUMBER;
5331
5332
5333 /* Added by krsankar for RCA bug 8396757 */
5334 v_ledger_id NUMBER;
5335 v_acctg_mode_code VARCHAR2(10);
5336
5337 /* Added by vkanteti for bug 14773226 */
5338 type mergeEvent_ref_cur is ref cursor;
5339 mergeEvent_cur mergeEvent_ref_cur;
5340 v_mergeEvent_sql varchar2(6000);
5341
5342 BEGIN
5343 -- --------------------------
5344 -- Initialize local variables
5345 -- --------------------------
5346 v_function := 'xla_third_party_merge.create_accounting';
5347 v_module := C_DEFAULT_MODULE||'.create_accounting';
5348 v_event_merge_option := 'NONE';
5349 v_processed_event_count := 0;
5350
5351 -- Log the function entry, the passed parameters and their values
5352 IF (C_LEVEL_PROCEDURE >= g_log_level)
5353 THEN
5354 trace( p_msg => 'BEGIN - ' || v_function
5355 , p_level => C_LEVEL_PROCEDURE
5356 , p_module => v_module);
5357 trace( p_msg => 'p_applicaiton_id = ' || p_application_id
5358 || ', p_event_id = ' || p_event_id
5359 || ', p_transfer_to_gl_flag = ' || p_transfer_to_gl_flag
5360 || ', p_post_in_gl_flag = ' || p_post_in_gl_flag
5361 || ', p_merge_event_set_id = ' || p_merge_event_set_id
5362 || ', p_srs_flag = ' || p_srs_flag
5363 , p_level => C_LEVEL_PROCEDURE
5364 , p_module => v_module);
5365 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
5366
5367 -- -----------------------
5368 -- Validate the parameters
5369 -- -----------------------
5370
5371 -- Validate if the passed application is defined in XLA_SUBLEDGERS
5372 BEGIN
5373 SELECT f.APPLICATION_NAME, s.VALUATION_METHOD_FLAG
5374 INTO v_application_name, v_valuation_method_flag
5375 FROM XLA_SUBLEDGERS s, FND_APPLICATION_VL f
5376 WHERE s.APPLICATION_ID = f.APPLICATION_ID
5377 AND s.APPLICATION_ID = p_application_id;
5378 EXCEPTION
5379 WHEN NO_DATA_FOUND THEN
5380 v_message := xla_messages_pkg.get_message
5381 ( p_appli_s_name => 'XLA'
5382 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
5383 , p_token_1 => 'PARAMETER_VALUE'
5384 , p_value_1 => p_application_id
5385 , p_token_2 => 'PARAMETER'
5386 , p_value_2 => 'p_application_id');
5387 RAISE ValidationError;
5388 END;
5389
5390 -- Log values of v_valuation_method_flag, g_use_ledger_security,
5391 -- g_access_set_id, and g_sec_access_set_id
5392 trace( p_msg => 'v_valuation_method_flag = ' || v_valuation_method_flag
5393 || ', g_use_ledger_security = ' || g_use_ledger_security
5394 || ', g_access_set_id = ' || g_access_set_id
5395 || ', g_sec_access_set_id = ' || g_sec_access_set_id
5396 , p_level => C_LEVEL_STATEMENT
5397 , p_module => v_module);
5398
5399 IF (p_merge_event_set_id IS NOT NULL)
5400 THEN
5401 -- Validate the passed merge event set ID
5402 BEGIN
5403 SELECT 'X'
5404 INTO v_dummy
5405 FROM XLA_EVENTS
5406 WHERE EVENT_ID = p_merge_event_set_id
5407 AND EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE');
5408 EXCEPTION
5409 WHEN NO_DATA_FOUND THEN
5410 v_message := xla_messages_pkg.get_message
5411 ( p_appli_s_name => 'XLA'
5412 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
5413 , p_token_1 => 'PARAMETER_VALUE'
5414 , p_value_1 => p_merge_event_set_id
5415 , p_token_2 => 'PARAMETER'
5416 , p_value_2 => 'p_merge_event_set_id');
5417 RAISE ValidationError;
5418 END;
5419 ELSIF (p_event_id IS NOT NULL)
5420 THEN
5421 -- Validate the passed event ID
5422 BEGIN
5423 SELECT 'X'
5424 INTO v_dummy
5425 FROM XLA_EVENTS
5426 WHERE EVENT_ID = p_event_id
5427 AND EVENT_TYPE_CODE IN ('PARTIAL_MERGE', 'FULL_MERGE');
5428 EXCEPTION
5429 WHEN NO_DATA_FOUND THEN
5430 v_message := xla_messages_pkg.get_message
5431 ( p_appli_s_name => 'XLA'
5432 , p_msg_name => 'XLA_MERGE_INVALID_PARAM'
5433 , p_token_1 => 'PARAMETER_VALUE'
5434 , p_value_1 => p_event_id
5435 , p_token_2 => 'PARAMETER'
5436 , p_value_2 => 'p_event_id');
5437 RAISE ValidationError;
5438 END;
5439 END IF; -- IF (p_merge_event_set_id IS NOT NULL)
5440
5441 -- ------------------------------------
5442 -- Process the third party merge events
5443 -- ------------------------------------
5444
5445 -- Changing the cursor sql to build query dynamically checking p_event_id and p_merge_event_set_id -- 14773226
5446 v_mergeEvent_sql := 'SELECT evt.event_id,
5447 evt.event_type_code,
5448 evt.event_date,
5449 evt.process_status_code,
5450 evt.reference_num_1 old_party_id,
5451 evt.reference_num_2 old_site_id,
5452 evt.reference_num_3 new_party_id,
5453 evt.reference_num_4 new_site_id,
5454 p1.third_party_number original_party_number,
5455 s1.third_party_site_code original_site_code,
5456 p2.third_party_number new_party_number,
5457 s2.third_party_site_code new_site_code,
5458 evt.reference_char_1 party_type,
5459 evt.reference_char_2 mapping_flag,
5460 ent.entity_id,
5461 ent.source_application_id,
5462 ent.ledger_id
5463 FROM xla_events evt,
5464 xla_third_parties_v p1,
5465 xla_third_parties_v p2,
5466 xla_third_party_sites_v s1,
5467 xla_third_party_sites_v s2,
5468 xla_transaction_entities ent,
5469 xla_ledger_options lgopt,
5470 xla_launch_options lnopt
5471 WHERE ';
5472 IF P_EVENT_ID IS NOT NULL THEN
5473 v_mergeEvent_sql := v_mergeEvent_sql || ' EVT.EVENT_ID = ' || P_EVENT_ID || ' AND ';
5474 END IF;
5475 IF P_MERGE_EVENT_SET_ID IS NOT NULL THEN
5476 v_mergeEvent_sql := v_mergeEvent_sql || ' EVT.MERGE_EVENT_SET_ID = ' || p_merge_event_set_id || ' AND ';
5477 END IF;
5478 -- 16713447 modified dynamic SQL
5479 v_mergeEvent_sql := v_mergeEvent_sql || ' evt.APPLICATION_ID = '||p_application_id||'
5480 AND evt.EVENT_TYPE_CODE IN (''PARTIAL_MERGE'', ''FULL_MERGE'')
5481 AND evt.PROCESS_STATUS_CODE not in (''P'',''F'') -- Modified by krsankar for RCA bug 8396757
5482 AND p1.THIRD_PARTY_ID = evt.REFERENCE_NUM_1
5483 AND p1.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
5484 AND p2.THIRD_PARTY_ID = evt.REFERENCE_NUM_3
5485 AND p2.THIRD_PARTY_TYPE = evt.REFERENCE_CHAR_1
5486 AND s1.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_1
5487 AND s1.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_2
5488 AND s1.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
5489 AND s2.THIRD_PARTY_ID (+) = evt.REFERENCE_NUM_3
5490 AND s2.THIRD_PARTY_SITE_ID (+) = evt.REFERENCE_NUM_4
5491 AND s2.THIRD_PARTY_TYPE (+) = evt.REFERENCE_CHAR_1
5492 AND ent.APPLICATION_ID = evt.APPLICATION_ID
5493 AND ent.ENTITY_ID = evt.ENTITY_ID
5494 AND ent.ENTITY_CODE = ''THIRD_PARTY_MERGE''
5495 AND lgopt.APPLICATION_ID = ent.APPLICATION_ID
5496 AND lgopt.LEDGER_ID = ent.LEDGER_ID
5497 AND lgopt.ENABLED_FLAG = ''Y''
5498 AND lnopt.APPLICATION_ID = lgopt.APPLICATION_ID
5499 AND lnopt.LEDGER_ID = lgopt.LEDGER_ID
5500 AND ( lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = ''Y''
5501 OR (lnopt.ACCOUNTING_MODE_OVERRIDE_FLAG = ''N''
5502 AND lnopt.ACCOUNTING_MODE_CODE = ''' || p_accounting_mode || '''))
5503 AND ( lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = ''Y''
5504 OR (lnopt.SUBMIT_TRANSFER_OVERRIDE_FLAG = ''N''
5505 AND lnopt.SUBMIT_TRANSFER_TO_GL_FLAG
5506 = ''' || p_transfer_to_gl_flag || '''))
5507 AND ( lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = ''Y''
5508 OR (lnopt.SUBMIT_GL_POST_OVERRIDE_FLAG = ''N''
5509 AND lnopt.SUBMIT_GL_POST_FLAG = ''' || p_post_in_gl_flag || '''))
5510 AND ( ''' || g_use_ledger_security || ''' = ''N''
5511 OR (''' || g_use_ledger_security || ''' =''Y''
5512 AND NOT EXISTS
5513 (SELECT ''Ledger without access''
5514 FROM XLA_LEDGER_RELATIONSHIPS_V rs,
5515 XLA_LEDGER_OPTIONS lgopt2,
5516 gl_ledgers gld
5517 WHERE rs.RELATIONSHIP_ENABLED_FLAG = ''Y''
5518 AND rs.ledger_id = gld.ledger_id
5519 AND gld.complete_flag = ''Y''
5520 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
5521 AND DECODE(''' || v_valuation_method_flag || '''
5522 , ''N'', rs.PRIMARY_LEDGER_ID
5523 , DECODE(rs.LEDGER_CATEGORY_CODE
5524 , ''ALC'', rs.PRIMARY_LEDGER_ID
5525 , rs.LEDGER_ID)) = lgopt.LEDGER_ID
5526 AND DECODE(rs.LEDGER_CATEGORY_CODE
5527 , ''ALC'', rs.PRIMARY_LEDGER_ID
5528 , rs.LEDGER_ID) = lgopt2.LEDGER_ID
5529 AND lgopt2.APPLICATION_ID = lgopt.APPLICATION_ID
5530 AND lgopt2.ENABLED_FLAG = ''Y''
5531 AND lgopt2.MERGE_ACCT_OPTION_CODE <> ''NONE''
5532 AND rs.LEDGER_ID NOT IN
5533 (SELECT asa.LEDGER_ID
5534 FROM GL_ACCESS_SET_ASSIGNMENTS asa
5535 WHERE asa.ACCESS_SET_ID
5536 IN (''' || G_ACCESS_SET_ID || '''
5537 ,''' || G_SEC_ACCESS_SET_ID || ''')))))
5538 ORDER BY evt.EVENT_DATE, evt.EVENT_ID';
5539
5540 -- 16713447 Added debug to capture query in log file
5541 USER_LOG (SUBSTR(V_MERGEEVENT_SQL,1,2000));
5542 USER_LOG (SUBSTR(V_MERGEEVENT_SQL,2001,2000));
5543 user_log (substr(v_mergeEvent_sql,4001,2000));
5544
5545
5546 -- Loop for each event to process
5547 -- OPEN mergeEvent_cur; -- 14773226
5548 OPEN mergeEvent_cur for v_mergeEvent_sql;
5549 LOOP
5550 FETCH mergeEvent_cur
5551 INTO v_event_id
5552 ,v_merge_type
5553 ,v_merge_date
5554 ,v_process_status
5555 ,v_old_party_id
5556 ,v_old_site_id
5557 ,v_new_party_id
5558 ,v_new_site_id
5559 ,v_original_party_number
5560 ,v_original_site_code
5561 ,v_new_party_number
5562 ,v_new_site_code
5563 ,v_party_type
5564 ,v_mapping_flag
5565 ,v_entity_id
5566 ,v_src_appl_id
5567 ,v_event_ledger_id;
5568 EXIT WHEN mergeEvent_cur%NOTFOUND;
5569
5570 IF (C_LEVEL_STATEMENT >= g_log_level)
5571 THEN
5572 -- Log the values retrieved from the cursor
5573 trace(p_msg => 'v_event_id = ' || v_event_id
5574 || ', v_merge_type = ' || v_merge_type
5575 || ', v_merge_date = ' || v_merge_date
5576 || ', v_process_status = ' || v_process_status
5577 || ', v_old_party_id = ' || v_old_party_id
5578 || ', v_old_site_id = ' || v_old_site_id
5579 , p_level => C_LEVEL_STATEMENT
5580 , p_module => v_module);
5581
5582 trace(p_msg => 'v_new_party_id = ' || v_new_party_id
5583 || ', v_new_site_id = ' || v_new_site_id
5584 || ', v_original_party_number = ' || v_original_party_number
5585 || ', v_original_site_code = ' || v_original_site_code
5586 || ', v_new_party_number = ' || v_new_party_number
5587 || ', v_new_site_code = ' || v_new_site_code
5588 , p_level => C_LEVEL_STATEMENT
5589 , p_module => v_module);
5590
5591 trace(p_msg => 'v_party_type = ' || v_party_type
5592 || ', v_mapping_flag = ' || v_mapping_flag
5593 || ', v_entity_id = ' || v_entity_id
5594 || ', v_src_appl_id = ' || v_src_appl_id
5595 || ', v_event_ledger_id = ' || v_event_ledger_id
5596 , p_level => C_LEVEL_STATEMENT
5597 , p_module => v_module);
5598 END IF; -- IF (C_LEVEL_STATEMENT >= g_log_level)
5599
5600 -- Cache the ledgers to be processed for the current merge event
5601 trace( p_msg => 'Cache the ledgers for the current merge event'
5602 , p_level => C_LEVEL_STATEMENT
5603 , p_module => v_module);
5604
5605 SELECT rs.LEDGER_ID,
5606 rs.LEDGER_CATEGORY_CODE,
5607 lgopt.ACCT_REVERSAL_OPTION_CODE,
5608 nvl(lgopt.MERGE_ACCT_OPTION_CODE, 'NONE'),
5609 lgopt.ROUNDING_RULE_CODE,
5610 rs.CURRENCY_CODE,
5611 nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)),
5612 'N'
5613 BULK COLLECT INTO
5614 v_array_ledger_id,
5615 v_array_ledger_category,
5616 v_array_reversal_option,
5617 v_array_merge_option,
5618 v_array_rounding_rule_code,
5619 v_array_currency_code,
5620 v_array_mau,
5621 v_array_submit_transfer
5622 FROM XLA_LEDGER_RELATIONSHIPS_V rs,
5623 XLA_LEDGER_OPTIONS lgopt,
5624 FND_CURRENCIES fcu,
5625 GL_LEDGERS gld
5626 WHERE rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
5627 AND rs.ledger_id = gld.ledger_id
5628 AND gld.complete_flag = 'Y'
5629 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
5630 AND DECODE(decode(v_valuation_method_flag, 'N', 'N', lgopt.capture_event_flag)
5631 , 'N', rs.PRIMARY_LEDGER_ID
5632 , DECODE(rs.LEDGER_CATEGORY_CODE
5633 , 'ALC', rs.PRIMARY_LEDGER_ID
5634 , rs.LEDGER_ID)) = v_event_ledger_id
5635 AND DECODE(rs.LEDGER_CATEGORY_CODE
5636 , 'ALC', rs.PRIMARY_LEDGER_ID
5637 , rs.LEDGER_ID) = lgopt.LEDGER_ID
5638 AND lgopt.APPLICATION_ID = p_application_id
5639 AND lgopt.ENABLED_FLAG = 'Y'
5640 and rs.currency_code = fcu.currency_code;
5641
5642 -- Loop for each ledger to log its attribute values and check the third
5643 -- party merge accounting option in order to set v_event_merge_option
5644 FOR i IN 1..v_array_ledger_id.COUNT
5645 LOOP
5646 -- Log the ledger attribute values
5647 trace( p_msg => 'v_array_ledger_id('||i||') = '
5648 || v_array_ledger_id(i)
5649 || ', v_array_ledger_category('||i||') = '
5650 || v_array_ledger_category(i)
5651 || ', v_array_reversal_option('||i||') = '
5652 || v_array_reversal_option(i)
5653 || ', v_array_merge_option('||i||') = '
5654 || v_array_merge_option(i)
5655 , p_level => C_LEVEL_STATEMENT
5656 , p_module => v_module);
5657
5658 IF (v_event_merge_option = 'NONE' AND v_array_merge_option(i) <> 'NONE')
5659 THEN
5660 -- Set the event merge option same as the merge option of current event
5661 v_event_merge_option := v_array_merge_option(i);
5662 ELSIF ( (v_event_merge_option = 'CHANGE'
5663 AND v_array_merge_option(i) = 'TRANSFER')
5664 OR (v_event_merge_option = 'TRANSFER'
5665 AND v_array_merge_option(i) = 'CHANGE'))
5666 THEN
5667 -- Set the event merge option to 'BOTH'
5668 v_event_merge_option := 'BOTH';
5669 END IF; -- IF (v_event_merge_option = 'NONE' AND ...
5670
5671 END LOOP;
5672
5673 -- Log the value of v_event_merge_option
5674 trace( p_msg => 'v_event_merge_option = ' || v_event_merge_option
5675 , p_level => C_LEVEL_STATEMENT
5676 , p_module => v_module);
5677
5678 IF (v_event_merge_option <> 'NONE') THEN
5679
5680 IF v_old_site_id IS NULL THEN
5681
5682 -- Get messages for AE header and lines descriptions
5683 -- this is the description for reverse and rebooking header
5684 -- and the line desc for 'change' option
5685 v_ael_desc1 := xla_messages_pkg.get_message
5686 ( p_appli_s_name => 'XLA'
5687 , p_msg_name => 'XLA_MERGE_AE_DESC1'
5688 , p_token_1 => 'ORIGINAL'
5689 , p_value_1 => v_original_party_number
5690 , p_token_2 => 'NEW'
5691 , p_value_2 => v_new_party_number);
5692
5693 -- this is the description for transfer balance, both line and header
5694 v_ael_desc2 := xla_messages_pkg.get_message
5695 ( p_appli_s_name => 'XLA'
5696 , p_msg_name => 'XLA_MERGE_AE_DESC2'
5697 , p_token_1 => 'ORIGINAL'
5698 , p_value_1 => v_original_party_number
5699 , p_token_2 => 'NEW'
5700 , p_value_2 => v_new_party_number);
5701
5702 -- this is the description for reverse lines
5703 v_ael_desc3 := xla_messages_pkg.get_message
5704 ( p_appli_s_name => 'XLA'
5705 , p_msg_name => 'XLA_MERGE_AE_DESC3'
5706 , p_token_1 => 'ORIGINAL'
5707 , p_value_1 => v_original_party_number
5708 , p_token_2 => 'NEW'
5709 , p_value_2 => v_new_party_number);
5710
5711 -- this is the description for rebooking lines
5712 v_ael_desc4 := xla_messages_pkg.get_message
5713 ( p_appli_s_name => 'XLA'
5714 , p_msg_name => 'XLA_MERGE_AE_DESC4'
5715 , p_token_1 => 'ORIGINAL'
5716 , p_value_1 => v_original_party_number
5717 , p_token_2 => 'NEW'
5718 , p_value_2 => v_new_party_number);
5719
5720 ELSE -- v_old_site_id IS NOT NULL
5721
5722 --
5723 -- Get messages for AE header and lines descriptions
5724 -- this is the description for reverse and rebooking header
5725 -- and the line desc for 'change' option
5726 --
5727 -- Use message XLA_MERGE_AE_DESC_SITE<N> as site code is populated
5728 --
5729 v_ael_desc1 := xla_messages_pkg.get_message
5730 ( p_appli_s_name => 'XLA'
5731 , p_msg_name => 'XLA_MERGE_AE_DESC_SITE1'
5732 , p_token_1 => 'ORIGINAL_PARTY_NUMBER'
5733 , p_value_1 => v_original_party_number
5734 , p_token_2 => 'ORIGINAL_PARTY_SITE_CODE'
5735 , p_value_2 => v_original_site_code
5736 , p_token_3 => 'NEW_PARTY_NUMBER'
5737 , p_value_3 => v_new_party_number
5738 , p_token_4 => 'NEW_PARTY_SITE_CODE'
5739 , p_value_4 => v_new_site_code);
5740
5741 -- this is the description for transfer balance, both line and header
5742 v_ael_desc2 := xla_messages_pkg.get_message
5743 ( p_appli_s_name => 'XLA'
5744 , p_msg_name => 'XLA_MERGE_AE_DESC_SITE2'
5745 , p_token_1 => 'ORIGINAL_PARTY_NUMBER'
5746 , p_value_1 => v_original_party_number
5747 , p_token_2 => 'ORIGINAL_PARTY_SITE_CODE'
5748 , p_value_2 => v_original_site_code
5749 , p_token_3 => 'NEW_PARTY_NUMBER'
5750 , p_value_3 => v_new_party_number
5751 , p_token_4 => 'NEW_PARTY_SITE_CODE'
5752 , p_value_4 => v_new_site_code);
5753
5754 -- this is the description for reverse lines
5755 v_ael_desc3 := xla_messages_pkg.get_message
5756 ( p_appli_s_name => 'XLA'
5757 , p_msg_name => 'XLA_MERGE_AE_DESC_SITE3'
5758 , p_token_1 => 'ORIGINAL_PARTY_NUMBER'
5759 , p_value_1 => v_original_party_number
5760 , p_token_2 => 'ORIGINAL_PARTY_SITE_CODE'
5761 , p_value_2 => v_original_site_code
5762 , p_token_3 => 'NEW_PARTY_NUMBER'
5763 , p_value_3 => v_new_party_number
5764 , p_token_4 => 'NEW_PARTY_SITE_CODE'
5765 , p_value_4 => v_new_site_code);
5766
5767 -- this is the description for rebooking lines
5768 v_ael_desc4 := xla_messages_pkg.get_message
5769 ( p_appli_s_name => 'XLA'
5770 , p_msg_name => 'XLA_MERGE_AE_DESC_SITE4'
5771 , p_token_1 => 'ORIGINAL_PARTY_NUMBER'
5772 , p_value_1 => v_original_party_number
5773 , p_token_2 => 'ORIGINAL_PARTY_SITE_CODE'
5774 , p_value_2 => v_original_site_code
5775 , p_token_3 => 'NEW_PARTY_NUMBER'
5776 , p_value_3 => v_new_party_number
5777 , p_token_4 => 'NEW_PARTY_SITE_CODE'
5778 , p_value_4 => v_new_site_code);
5779 END IF;
5780 END IF; -- IF (v_event_merge_option <> 'NONE')
5781
5782 IF (v_process_status = 'D') THEN
5783 -- ----------------------------------------------------------------
5784 -- Delete all the draft entries created for this merge event if the
5785 -- current event process status is 'D'
5786 -- ----------------------------------------------------------------
5787 trace( p_msg => 'Delete draft entries'
5788 , p_level => C_LEVEL_STATEMENT
5789 , p_module => v_module);
5790
5791 delete_je (
5792 p_application_id => p_application_id
5793 , p_event_id => v_event_id);
5794
5795 END IF;
5796
5797 IF (v_event_merge_option = 'TRANSFER' OR v_event_merge_option = 'BOTH')
5798 THEN
5799 -- ---------------------------------------------------------------------
5800 -- Transfer third party balances if the event merge option is 'TRANSFER'
5801 -- or 'BOTH'
5802 -- ---------------------------------------------------------------------
5803 trace( p_msg => 'Start to transfer third party balances'
5804 , p_level => C_LEVEL_STATEMENT
5805 , p_module => v_module);
5806
5807 -- ---------------------------------------------------------------------
5808 -- create journal entries for the event: v_event_id for all the ledgers
5809 -- ---------------------------------------------------------------------
5810 create_work_table(
5811 p_request_id => XLA_ENVIRONMENT_PKG.g_req_id
5812 ,p_application_id => p_application_id
5813 ,p_event_id => v_event_id
5814 ,p_merge_date => v_merge_date
5815 ,p_merge_type => v_merge_type
5816 ,p_old_site_id => v_old_site_id
5817 ,p_old_party_id => v_old_party_id
5818 ,p_new_site_id => v_new_site_id
5819 ,p_new_party_id => v_new_party_id
5820 ,p_party_type => v_party_type
5821 ,p_array_ledger_id => v_array_ledger_id
5822 ,p_array_merge_option => v_array_merge_option);
5823
5824 create_journal_entries(
5825 x_errbuf => x_errbuf
5826 ,x_retcode => x_retcode
5827 ,p_application_id => p_application_id
5828 ,p_accounting_mode => p_accounting_mode
5829 ,p_transfer_to_gl_flag => p_transfer_to_gl_flag
5830 ,p_post_in_gl_flag => p_post_in_gl_flag
5831 ,p_event_id => v_event_id
5832 ,p_entity_id => v_entity_id
5833 ,p_mapping_flag => v_mapping_flag
5834 ,p_event_ledger_id => v_event_ledger_id
5835 ,p_merge_date => v_merge_date
5836 ,p_merge_type => v_merge_type
5837 ,p_old_site_id => v_old_site_id
5838 ,p_old_party_id => v_old_party_id
5839 ,p_new_site_id => v_new_site_id
5840 ,p_new_party_id => v_new_party_id
5841 ,p_party_type => v_party_type
5842 ,p_balance_desc => v_ael_desc2
5843 ,p_reverse_line_desc => v_ael_desc3
5844 ,p_rebooking_line_desc => v_ael_desc4
5845 ,p_reverse_header_desc => v_ael_desc1
5846 ,p_array_ledger_id => v_array_ledger_id
5847 ,p_array_ledger_category => v_array_ledger_category
5848 ,p_array_reversal_option => v_array_reversal_option
5849 ,p_array_rounding_rule_code => v_array_rounding_rule_code
5850 ,p_array_mau => v_array_mau
5851 ,p_array_merge_option => v_array_merge_option
5852 ,p_array_submit_transfer => v_array_submit_transfer);
5853
5854 END IF; -- IF (v_event_merge_option = 'TRANSFER' OR ...
5855
5856 IF (v_event_merge_option = 'CHANGE' OR v_event_merge_option = 'BOTH'
5857 -- 5103972
5858 -- For incomplete JEs, need to update Third Paryt Information
5859 -- irrespective of merge options
5860 OR v_event_merge_option = 'TRANSFER')
5861 THEN
5862 -- --------------------------------------------------------------------
5863 -- Update third party information if the event merge option is 'CHANGE'
5864 -- or 'BOTH'
5865 -- --------------------------------------------------------------------
5866 trace( p_msg => 'Start to update third party information'
5867 , p_level => C_LEVEL_STATEMENT
5868 , p_module => v_module);
5869
5870 IF(p_accounting_mode = 'F') THEN
5871 update_journal_entries(
5872 x_errbuf => x_errbuf
5873 ,x_retcode => x_retcode
5874 ,p_application_id => p_application_id
5875 ,p_event_id => v_event_id
5876 ,p_event_merge_option => v_event_merge_option
5877 ,p_entity_id => v_entity_id
5878 ,p_mapping_flag => v_mapping_flag
5879 ,p_event_ledger_id => v_event_ledger_id
5880 ,p_merge_date => v_merge_date
5881 ,p_merge_type => v_merge_type
5882 ,p_old_site_id => v_old_site_id
5883 ,p_old_party_id => v_old_party_id
5884 ,p_new_site_id => v_new_site_id
5885 ,p_new_party_id => v_new_party_id
5886 ,p_party_type => v_party_type
5887 ,p_line_desc => v_ael_desc1
5888 ,p_array_ledger_id => v_array_ledger_id
5889 ,p_array_ledger_category => v_array_ledger_category
5890 ,p_array_reversal_option => v_array_reversal_option
5891 ,p_array_merge_option => v_array_merge_option
5892 ,p_array_submit_transfer => v_array_submit_transfer);
5893 END IF;
5894
5895 END IF; -- IF (v_event_merge_option = 'CHANGE' OR ...
5896
5897 -- Update the status of this current event
5898 trace( p_msg => 'Update the current event status'
5899 , p_level => C_LEVEL_STATEMENT
5900 , p_module => v_module);
5901
5902 /* Added by krsankar for RCA bug 8396757 */
5903
5904 IF p_accounting_mode IS NULL
5905 THEN
5906
5907 BEGIN
5908 trace( p_msg => 'p_accounting_mode is passed as NULL'
5909 , p_level => C_LEVEL_STATEMENT
5910 , p_module => v_module);
5911
5912 trace( p_msg => 'Retrieving p_accounting_mode from subledger options'
5913 , p_level => C_LEVEL_STATEMENT
5914 , p_module => v_module);
5915
5916 select ledger_id
5917 into v_ledger_id
5918 from xla_events xe,
5919 xla_transaction_entities xte
5920 where xe.entity_id = xte.entity_id
5921 and xe.application_id = xte.application_id
5922 and xe.event_id = v_event_id
5923 and xe.application_id = p_application_id;
5924
5925 trace( p_msg => 'Ledger id fetched for event_id '||v_event_id||' is : '||v_ledger_id
5926 , p_level => C_LEVEL_STATEMENT
5927 , p_module => v_module);
5928
5929 select accounting_mode_code
5930 into v_acctg_mode_code
5931 from xla_subledger_options_v
5932 where application_id = p_application_id
5933 and ledger_id = v_ledger_id;
5934
5935 trace( p_msg => 'Accounting mode code for ledger id '||v_ledger_id||' is : '||v_acctg_mode_code
5936 , p_level => C_LEVEL_STATEMENT
5937 , p_module => v_module);
5938
5939 EXCEPTION
5940 WHEN OTHERS THEN
5941
5942 trace( p_msg => 'Exception in fetching accounting_mode,ledger_id from Ledger setup for event_id : '||v_event_id
5943 , p_level => C_LEVEL_STATEMENT
5944 , p_module => v_module);
5945
5946 trace( p_msg => 'Exception is : '|| sqlerrm
5947 , p_level => C_LEVEL_STATEMENT
5948 , p_module => v_module);
5949
5950 END;
5951
5952 END IF;
5953
5954
5955 /* Based on the p_accounting_mode, if p_accounting_mode is NULL, then v_acctg_mode_code
5956 is used.v_acctg_mode_code is fetched from ledger setup, for that specific ledger, based
5957 on whether the ledger has FINAL or DRAFT as its accounting mode.
5958 So, if the accounting mode is FINAL, then event_status_code = 'P' and
5959 process_status_code = 'F'.If accounting_mode is DRAFT, then event_status_code = 'U' and
5960 process_status_code = 'D'. */
5961
5962
5963 UPDATE XLA_EVENTS
5964 SET EVENT_STATUS_CODE = DECODE(nvl(p_accounting_mode,v_acctg_mode_code)
5965 , 'F', 'P', EVENT_STATUS_CODE),
5966 PROCESS_STATUS_CODE = nvl(p_accounting_mode,v_acctg_mode_code) --Added by krsankar for RCA bug 8396757
5967 WHERE EVENT_ID = v_event_id;
5968
5969 -- Increment v_processed_event_count by 1
5970 v_processed_event_count := v_processed_event_count + 1;
5971 END LOOP;
5972
5973 -- Log the value of v_processed_event_count
5974 trace( p_msg => 'v_processed_event_count = ' || v_processed_event_count
5975 , p_level => C_LEVEL_STATEMENT
5976 , p_module => v_module);
5977
5978 IF (v_processed_event_count = 0)
5979 THEN
5980 -- No events are processed
5981 x_errbuf := xla_messages_pkg.get_message
5982 ( p_appli_s_name => 'XLA'
5983 , p_msg_name => 'XLA_MERGE_ACCT_NO_EVENT'
5984 , p_token_1 => 'SUBLEDGER_APPLICATION_NAME'
5985 , p_value_1 => v_application_name);
5986 -- Log the error message
5987 trace( p_msg => v_message
5988 , p_level => C_LEVEL_ERROR
5989 , p_module => v_module);
5990 -- Set return code to 'E'
5991
5992 /* Commented the ELSE part of the code below for bug 8472734.
5993 If the number of records are processed is 0, still the
5994 program should complete normally and should not error out.
5995 But in case there are no events processed, the message is
5996 printed in the log file saying that there are no third party
5997 events to be processed */
5998
5999
6000 /*x_retcode := xla_third_party_merge_pub.G_RET_STS_ERROR;
6001 ELSE*/
6002
6003 x_retcode := xla_third_party_merge_pub.G_RET_STS_SUCCESS;
6004
6005 trace( p_msg => 'RETURN SUCCESS'
6006 , p_level => C_LEVEL_ERROR
6007 , p_module => v_module);
6008
6009 END IF; -- IF (v_processed_event_count = 0)
6010
6011 -- Log the out parameters, their returned values and function exit
6012 IF (C_LEVEL_PROCEDURE >= g_log_level)
6013 THEN
6014 trace( p_msg => 'x_retcode = ' || x_retcode
6015 || ', x_errbuf = ' || x_errbuf
6016 , p_level => C_LEVEL_PROCEDURE
6017 , p_module => v_module);
6018 IF (x_retcode = xla_third_party_merge_pub.G_RET_STS_SUCCESS)
6019 THEN
6020 trace( p_msg => 'END - ' || v_function
6021 , p_level => C_LEVEL_PROCEDURE
6022 , p_module => v_module);
6023 -- Commenting out as wrong message is displayed in FND because of fix 8472734
6024 --ELSE
6025 --trace( p_msg => 'EXIT with ERROR - ' || v_function
6026 --, p_level => C_LEVEL_PROCEDURE
6027 --, p_module => v_module);
6028 END IF; -- IF (x_retcode = xla_third_party_merge_pub.G_RET_STS_SUCCESS)
6029
6030 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
6031
6032 EXCEPTION
6033 WHEN NoAccountingDateError THEN
6034 IF (C_LEVEL_PROCEDURE >= g_log_level)
6035 THEN
6036 trace( p_msg => 'x_retcode = ' || x_retcode
6037 || ', x_errbuf = ' || x_errbuf
6038 , p_level => C_LEVEL_PROCEDURE
6039 , p_module => v_module);
6040 trace( p_msg => 'EXIT with ERROR - ' || v_function
6041 , p_level => C_LEVEL_PROCEDURE
6042 , p_module => v_module);
6043 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
6044 x_errbuf := 'No accounting date can be found';
6045 x_retcode := xla_third_party_merge_pub.G_RET_STS_ERROR;
6046 WHEN ValidationError THEN
6047 -- Log the error message
6048 trace( p_msg => v_message
6049 , p_level => C_LEVEL_ERROR
6050 , p_module => v_module);
6051 -- Set the out parameters
6052 x_errbuf := xla_messages_pkg.get_message
6053 ( p_appli_s_name => 'XLA'
6054 , p_msg_name => 'XLA_MERGE_FATAL_ERR'
6055 , p_token_1 => 'FUNCTION'
6056 , p_value_1 => v_function
6057 , p_token_2 => 'ERROR'
6058 , p_value_2 => v_message);
6059 x_retcode := xla_third_party_merge_pub.G_RET_STS_ERROR;
6060 -- Log the out parameters, their returned values and function exit
6061 IF (C_LEVEL_PROCEDURE >= g_log_level)
6062 THEN
6063 trace( p_msg => 'x_retcode = ' || x_retcode
6064 || ', x_errbuf = ' || x_errbuf
6065 , p_level => C_LEVEL_PROCEDURE
6066 , p_module => v_module);
6067 trace( p_msg => 'EXIT with ERROR - ' || v_function
6068 , p_level => C_LEVEL_PROCEDURE
6069 , p_module => v_module);
6070 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
6071
6072 WHEN OTHERS THEN
6073 -- Get and log the SQL error message
6074 v_message := SQLERRM;
6075 trace( p_msg => v_message
6076 , p_level => C_LEVEL_UNEXPECTED
6077 , p_module => v_module);
6078 -- Set the out parameters
6079 x_errbuf := xla_messages_pkg.get_message
6080 ( p_appli_s_name => 'XLA'
6081 , p_msg_name => 'XLA_MERGE_FATAL_ERR'
6082 , p_token_1 => 'FUNCTION'
6083 , p_value_1 => v_function
6084 , p_token_2 => 'ERROR'
6085 , p_value_2 => v_message);
6086 -- Set the return code to 'W' if any event is processed and it doesn't have
6087 -- merge event set; else, set it to 'U'
6088 IF (p_merge_event_set_id IS NULL AND v_processed_event_count > 0)
6089 THEN
6090 x_retcode := xla_third_party_merge_pub.G_RET_STS_WARN;
6091 ELSE
6092 x_retcode := xla_third_party_merge_pub.G_RET_STS_UNEXP_ERROR;
6093 END IF; -- IF (p_merge_event_set_id IS NULL AND v_processed_event_count > 0)
6094 -- Log the out parameters, their returned values and function exit
6095 IF (C_LEVEL_PROCEDURE >= g_log_level)
6096 THEN
6097 trace( p_msg => 'x_retcode = ' || x_retcode
6098 || ', x_errbuf = ' || x_errbuf
6099 , p_level => C_LEVEL_PROCEDURE
6100 , p_module => v_module);
6101 IF (x_retcode = xla_third_party_merge_pub.G_RET_STS_UNEXP_ERROR)
6102 THEN
6103 trace( p_msg => 'EXIT with ERROR - ' || v_function
6104 , p_level => C_LEVEL_PROCEDURE
6105 , p_module => v_module);
6106 ELSE
6107 trace( p_msg => 'END - ' || v_function
6108 , p_level => C_LEVEL_PROCEDURE
6109 , p_module => v_module);
6110 END IF; -- IF (x_retcode = G_RET_STS_UNEXP_ERROR)
6111 END IF; -- IF (C_LEVEL_PROCEDURE >= g_log_level)
6112
6113 END create_accounting;
6114
6115
6116 PROCEDURE delete_je(
6117 p_application_id IN INTEGER
6118 , p_event_id IN INTEGER) IS
6119 l_log_module VARCHAR2(240);
6120 BEGIN
6121 IF g_log_enabled THEN
6122 l_log_module := C_DEFAULT_MODULE||'.delete_je';
6123 END IF;
6124 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6125 trace
6126 (p_msg => 'BEGIN of procedure DELETE_JE'
6127 ,p_level => C_LEVEL_PROCEDURE
6128 ,p_module => l_log_module);
6129 END IF;
6130
6131 /* no accounting error here
6132 DELETE FROM xla_accounting_errors
6133 WHERE event_id IN
6134 (SELECT event_id FROM xla_events
6135 WHERE application_id = g_application_id
6136 AND request_id = g_report_request_id);
6137 */
6138
6139
6140 DELETE FROM xla_distribution_links
6141 WHERE ae_header_id IN
6142 (SELECT ae_header_id FROM xla_ae_headers
6143 WHERE application_id = p_application_id
6144 AND merge_event_id = p_event_id);
6145
6146 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6147 trace
6148 (p_msg => 'Number of distribution links deleted = '||SQL%ROWCOUNT
6149 ,p_level => C_LEVEL_STATEMENT
6150 ,p_module => l_log_module);
6151 END IF;
6152
6153
6154 DELETE FROM xla_ae_segment_values
6155 WHERE ae_header_id IN
6156 (SELECT ae_header_id FROM xla_ae_headers
6157 WHERE application_id = p_application_id
6158 AND merge_event_id = p_event_id);
6159
6160 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6161 trace
6162 (p_msg => 'Number of segment values deleted = '||SQL%ROWCOUNT
6163 ,p_level => C_LEVEL_STATEMENT
6164 ,p_module => l_log_module);
6165 END IF;
6166
6167
6168 DELETE FROM xla_ae_line_details
6169 WHERE ae_header_id IN
6170 (SELECT ae_header_id FROM xla_ae_headers
6171 WHERE application_id = p_application_id
6172 AND merge_event_id = p_event_id);
6173
6174 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6175 trace
6176 (p_msg => 'Number of line details deleted = '||SQL%ROWCOUNT
6177 ,p_level => C_LEVEL_STATEMENT
6178 ,p_module => l_log_module);
6179 END IF;
6180
6181 DELETE FROM xla_ae_header_details
6182 WHERE ae_header_id IN
6183 (SELECT ae_header_id FROM xla_ae_headers
6184 WHERE application_id = p_application_id
6185 AND merge_event_id = p_event_id);
6186
6187 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6188 trace
6189 (p_msg => 'Number of header details deleted = '||SQL%ROWCOUNT
6190 ,p_level => C_LEVEL_STATEMENT
6191 ,p_module => l_log_module);
6192 END IF;
6193
6194 DELETE FROM xla_ae_lines
6195 WHERE application_id = p_application_id
6196 AND ae_header_id IN
6197 (SELECT ae_header_id FROM xla_ae_headers
6198 WHERE application_id = p_application_id
6199 AND merge_event_id = p_event_id);
6200
6201 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6202 trace
6203 (p_msg => 'Number of ae lines deleted = '||SQL%ROWCOUNT
6204 ,p_level => C_LEVEL_STATEMENT
6205 ,p_module => l_log_module);
6206 END IF;
6207
6208 DELETE FROM xla_ae_headers
6209 WHERE application_id = p_application_id
6210 AND merge_event_id = p_event_id;
6211
6212 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6213 trace
6214 (p_msg => 'Number of ae headers deleted = '||SQL%ROWCOUNT
6215 ,p_level => C_LEVEL_STATEMENT
6216 ,p_module => l_log_module);
6217 END IF;
6218
6219 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6220 trace
6221 (p_msg => 'END of procedure DELETE_JE'
6222 ,p_level => C_LEVEL_PROCEDURE
6223 ,p_module => l_log_module);
6224 END IF;
6225 EXCEPTION
6226 WHEN xla_exceptions_pkg.application_exception THEN
6227 RAISE;
6228 WHEN OTHERS THEN
6229 xla_exceptions_pkg.raise_message
6230 (p_location => 'xla_third_party_merge.delete_je');
6231 END delete_je;
6232
6233
6234
6235 --=============================================================================
6236 -- ******************* Initialization *********************
6237 --=============================================================================
6238 BEGIN
6239 g_log_level := fnd_log.G_CURRENT_RUNTIME_LEVEL;
6240 g_log_enabled := fnd_log.test( log_level => g_log_level
6241 , module => C_DEFAULT_MODULE);
6242
6243 IF NOT g_log_enabled
6244 THEN
6245 g_log_level := C_LEVEL_LOG_DISABLED;
6246 END IF;
6247
6248 END xla_third_party_merge;