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