[Home] [Help]
PACKAGE BODY: APPS.ARP_XLA_EVENTS
Source
1 PACKAGE BODY ARP_XLA_EVENTS AS
2 /* $Header: ARXLAEVB.pls 120.85.12020000.4 2012/11/06 10:03:27 dgaurab ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7
8 /*-----------------------------------------------------------------------+
9 | Globle Variable Declarations and initializations |
10 +-----------------------------------------------------------------------*/
11
12 /*-----------------------------------------------------------------------+
13 | Built Event structure |
14 +-----------------------------------------------------------------------*/
15 TYPE bld_ev_type IS RECORD (
16 bld_dml_flag DBMS_SQL.VARCHAR2_TABLE,--insert,update,delete
17 bld_temp_event_id DBMS_SQL.VARCHAR2_TABLE
18 );
19
20 TYPE line_tbl_type IS TABLE OF ra_customer_trx_lines.customer_trx_line_id%TYPE
21 INDEX BY BINARY_INTEGER;
22
23 /*-----------------------------------------------------------------------+
24 | Default bulk fetch size, and starting index |
25 +-----------------------------------------------------------------------*/
26 MAX_ARRAY_SIZE BINARY_INTEGER := 1000 ;
27 STARTING_INDEX CONSTANT BINARY_INTEGER := 1;
28
29 line_tbl line_tbl_type;
30 g_prev_trx_id NUMBER := -9999;
31 g_rule_prev_trx_id NUMBER := -9999;
32 g_first_crh_status VARCHAR2(30) := 'X';
33 g_xla_user VARCHAR2(30);
34 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
35 g_call_number NUMBER := 1;
36 /*========================================================================
37 | Prototype Declarations Procedures
38 *=======================================================================*/
39 PROCEDURE define_arrays( p_select_c IN INTEGER,
40 p_xla_ev_rec IN xla_events_type,
41 p_ev_rec IN ev_rec_type,
42 p_call_point IN NUMBER);
43
44 PROCEDURE get_column_values(p_select_c IN INTEGER,
45 p_xla_ev_rec IN xla_events_type,
46 p_call_point IN NUMBER,
47 p_ev_rec OUT NOCOPY ev_rec_type);
48
49 PROCEDURE Build_Stmt(p_xla_ev_rec IN xla_events_type,
50 p_call_point IN NUMBER,
51 p_stmt OUT NOCOPY VARCHAR2 );
52
53 PROCEDURE Create_All_Events(p_xla_ev_rec IN xla_events_type);
54
55 PROCEDURE dump_ev_rec(p_ev_rec IN OUT NOCOPY ev_rec_type,
56 p_i IN BINARY_INTEGER);
57
58 PROCEDURE dump_bld_rec(p_bld_rec IN OUT NOCOPY bld_ev_type,
59 p_i IN BINARY_INTEGER,
60 p_tag IN VARCHAR2);
61
62 PROCEDURE dump_event_info
63 (p_ev_info_tab IN OUT NOCOPY xla_events_pub_pkg.t_array_entity_event_info_s,
64 p_i IN BINARY_INTEGER ,
65 p_tag IN VARCHAR2 );
66
67 PROCEDURE Upd_Dist(p_xla_ev_rec IN xla_events_type);
68
69 PROCEDURE un_denormalize_posting_entity
70 ( p_xla_doc IN VARCHAR2,
71 p_event_id IN NUMBER );
72
73 PROCEDURE dump_event_source_info
74 (x_ev_source_info IN OUT NOCOPY xla_events_pub_pkg.t_event_Source_info);
75
76 FUNCTION entity_code( p_doc_table IN VARCHAR2)
77 RETURN VARCHAR2;
78
79 /*========================================================================
80 | Prototype Declarations Functions
81 *=======================================================================*/
82 FUNCTION Get_Select_Cursor(p_xla_ev_rec IN xla_events_type,
83 p_call_point IN NUMBER)
84 RETURN INTEGER;
85
86 FUNCTION Change_Matrix(
87 trx_status IN VARCHAR2 ,
88 dist_gl_date IN DATE ,
89 ev_match_gl_date IN DATE ,
90 ev_match_status IN xla_events.event_status_code%TYPE,
91 posttogl IN VARCHAR2) RETURN VARCHAR2;
92
93 FUNCTION is_one_acct_asg_on_ctlgd
94 (p_invoice_id IN NUMBER,
95 p_posting_entity IN VARCHAR2 DEFAULT 'CTLGD',
96 p_mode IN VARCHAR2 DEFAULT 'O') RETURN VARCHAR2;
97
98
99 PROCEDURE log(
100 message IN VARCHAR2,
101 newline IN BOOLEAN DEFAULT TRUE
102 ) IS
103 BEGIN
104 IF PG_DEBUG = 'Y' THEN
105 arp_standard.debug(message);
106 END IF;
107 END log;
108
109
110
111 FUNCTION xla_user RETURN VARCHAR2
112 IS
113 l_status VARCHAR2(30);
114 l_industry VARCHAR2(30);
115 l_schema VARCHAR2(30);
116 l_res BOOLEAN;
117 BEGIN
118 IF g_xla_user IS NULL THEN
119 IF fnd_installation.get_app_info(
120 application_short_name=>'XLA'
121 , status => l_status
122 , industry => l_industry
123 , oracle_schema => l_schema)
124 THEN
125 g_xla_user := l_schema;
126 ELSE
127 g_xla_user := 'XLA';
128 END IF;
129 END IF;
130 RETURN g_xla_user;
131 END;
132
133 PROCEDURE get_existing_event
134 (p_event_id IN NUMBER,
135 x_event_id OUT NOCOPY NUMBER,
136 x_event_date OUT NOCOPY DATE,
137 x_event_status_code OUT NOCOPY VARCHAR2,
138 x_event_type_code OUT NOCOPY VARCHAR2)
139 IS
140 l_c INTEGER;
141 l_exec INTEGER;
142 l_fetch_row INTEGER;
143 l_stmt VARCHAR2(2000);
144 l_xla_user VARCHAR2(30);
145 BEGIN
146 log('get_existing_event +');
147 l_xla_user := xla_user;
148 log(' l_xla_user :'||l_xla_user);
149 l_stmt :=
150 'SELECT ae.event_id,
151 ae.event_date,
152 ae.event_status_code,
153 ae.event_type_code
154 FROM '||xla_user||'.xla_events ae
155 WHERE ae.event_id = :dist_event_id
156 AND ae.application_id = 222';
157
158 log(' l_stmt :'||l_stmt);
159
160 l_c := DBMS_SQL.OPEN_CURSOR;
161 DBMS_SQL.PARSE(l_c, l_stmt, DBMS_SQL.NATIVE);
162 DBMS_SQL.BIND_VARIABLE(l_c,':dist_event_id',p_event_id);
163 DBMS_SQL.DEFINE_COLUMN(l_c,1,x_event_id);
164 DBMS_SQL.DEFINE_COLUMN(l_c,2,x_event_date);
165 DBMS_SQL.DEFINE_COLUMN(l_c,3,x_event_status_code,30);
166 DBMS_SQL.DEFINE_COLUMN(l_c,4,x_event_type_code,30);
167 l_exec := DBMS_SQL.EXECUTE(l_c);
168 l_fetch_row := DBMS_SQL.FETCH_ROWS(l_c);
169 DBMS_SQL.COLUMN_VALUE(l_c, 1, x_event_id);
170 DBMS_SQL.COLUMN_VALUE(l_c, 2, x_event_date);
171 DBMS_SQL.COLUMN_VALUE(l_c, 3, x_event_status_code);
172 DBMS_SQL.COLUMN_VALUE(l_c, 4, x_event_type_code);
173 DBMS_SQL.CLOSE_CURSOR(l_c);
174
175
176 log('x_event_id:'||x_event_id);
177 log('x_event_date:'||x_event_date);
178 log(' x_event_status_code:'|| x_event_status_code);
179 log(' x_event_type_code:'|| x_event_type_code);
180
181 log('get_existing_event -');
182
183 END;
184
185 PROCEDURE get_best_existing_event
186 (p_trx_id IN NUMBER,
187 p_gl_date IN DATE,
188 p_override_event IN VARCHAR2,
189 x_match_event_id OUT NOCOPY NUMBER,
190 x_match_gl_date OUT NOCOPY DATE,
191 x_match_status OUT NOCOPY VARCHAR2,
192 x_match_type OUT NOCOPY VARCHAR2)
193 IS
194 l_c INTEGER;
195 l_exec INTEGER;
196 l_fetch_row INTEGER;
197 l_stmt VARCHAR2(2000);
198 l_xla_user VARCHAR2(30);
199 BEGIN
200 log('get_best_existing_event +');
201 l_xla_user := xla_user;
202 l_stmt :=
203 ' select ae.event_id ,
204 ae.event_date ,
205 ae.event_status_code,
206 ae.event_type_code
207 from xla_events ae,
208 xla_transaction_entities_upg xt
209 where xt.source_id_int_1 = :trx_id
210 and xt.entity_id = ae.entity_id
211 and nvl(ae.event_date,
212 to_date(''01-01-1900'',''DD-MM-YYYY'')) = :dist_gl_date
213 and ae.event_status_code <> ''P''
214 and ae.event_type_code = :override_event
215 and ae.application_id = 222
216 and xt.application_id = 222';
217
218 log('l_stmt :'||l_stmt);
219
220 l_c := DBMS_SQL.OPEN_CURSOR;
221 DBMS_SQL.PARSE(l_c, l_stmt, DBMS_SQL.NATIVE);
222 DBMS_SQL.BIND_VARIABLE(l_c,':trx_id',p_trx_id);
223 DBMS_SQL.BIND_VARIABLE(l_c,':dist_gl_date',p_gl_date);
224 DBMS_SQL.BIND_VARIABLE(l_c,':override_event',p_override_event);
225
226 DBMS_SQL.DEFINE_COLUMN(l_c,1,x_match_event_id);
227 DBMS_SQL.DEFINE_COLUMN(l_c,2,x_match_gl_date);
228 DBMS_SQL.DEFINE_COLUMN(l_c,3,x_match_status,30);
229 DBMS_SQL.DEFINE_COLUMN(l_c,4,x_match_type,30);
230 l_exec := DBMS_SQL.EXECUTE(l_c);
231 l_fetch_row := DBMS_SQL.FETCH_ROWS(l_c);
232 DBMS_SQL.COLUMN_VALUE(l_c, 1, x_match_event_id);
233 DBMS_SQL.COLUMN_VALUE(l_c, 2, x_match_gl_date);
234 DBMS_SQL.COLUMN_VALUE(l_c, 3, x_match_status);
235 DBMS_SQL.COLUMN_VALUE(l_c, 4, x_match_type);
236 DBMS_SQL.CLOSE_CURSOR(l_c);
237
238 IF x_match_gl_date IS NULL THEN x_match_gl_date := TO_DATE('01-01-1900','DD-MM-YYYY'); END IF;
239 IF x_match_status IS NULL THEN x_match_status := 'X'; END IF;
240
241 log('get_best_existing_event -');
242 END;
243
244 --}
245
246
247
248 /*========================================================================
249 | PUBLIC PROCEDURE Create_Events
250 |
251 | DESCRIPTION
252 | Main routine which forks processing based on input parameters
253 | and creates events for a given Document.
254 |
255 | This procedure does the following calls the create events routine
256 | for :
257 | a) Transactions
258 | b) Bills Receivable
259 | c) Receipts
260 | d) Adjustments
261 | e) Receipt/CM applications
262 |
263 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
264 |
265 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
266 | a) Create_All_Events
267 |
268 | PARAMETERS p_ev_rec which contains
269 | 1) xla_from_doc_id IN NUMBER --document id from
270 | 2) xla_to_doc_id IN NUMBER --document id to
271 | 3) xla_req_id IN NUMBER --request id batch processing
272 | 4) xla_dist_id IN NUMBER --distribution id
273 | 5) xla_doc_table IN VARCHAR2 --document table OLTP
274 | CT - Transactions
275 | CTCMAPP - Transactions and Credit Memo Applications
276 | ADJ - Adjustments
277 | CRH - Cash Receipt History
278 | CR - Cash Receipt History and Misc Cash or Applications
279 | MCD -Misc Cash Distributions
280 | APP -Applications for Receipts
281 | CMAPP -Applications for CM
282 | TRH - Transaction history for Bills
283 | 6) xla_doc_event IN VARCHAR2 --document business event OLTP
284 | 7) xla_mode IN VARCHAR2
285 | U-upgrade
286 | O-oltp
287 | B-batch mode
288 | 8) xla_call IN VARCHAR2
289 | C - Create events only
290 | D - Denormalize events only
291 | B - Create and Denormalize events on distributions
292 | 9) xla_fetch_size IN NUMBER --Bulk fetch size
293 |
294 | KNOWN ISSUES
295 |
296 | NOTES
297 |
298 | MODIFICATION HISTORY
299 | Date Author Description of Changes
300 | 21-AUG-2001 Vikram Ahluwalia Created
301 | 23-JUN-2005 Herve Yu Ledger Id and Transaction Date
302 *=======================================================================*/
303 PROCEDURE Create_Events(p_xla_ev_rec IN OUT NOCOPY xla_events_type ) IS
304
305 /*-----------------------------------------------------------------------+
306 | Local Variable Declarations and initializations |
307 +-----------------------------------------------------------------------*/
308 l_event_source_info xla_events_pub_pkg.t_event_source_info;
309 l_event_id NUMBER;
310 l_security xla_events_pub_pkg.t_security;
311
312 BEGIN
313
314 log('ARP_XLA_EVENTS.Create_Events()+');
315
316 log('xla_from_doc_id :'
317 || p_xla_ev_rec.xla_from_doc_id);
318
319 log('xla_to_doc_id :'
320 || p_xla_ev_rec.xla_to_doc_id);
321
322 log('p_xla_req_id :'
323 || p_xla_ev_rec.xla_req_id);
324
325 log('p_xla_dist_id :'
326 || p_xla_ev_rec.xla_dist_id);
327
328 log('p_xla_doc_table :'
329 || p_xla_ev_rec.xla_doc_table);
330
331 log('p_xla_doc_event :'
332 || p_xla_ev_rec.xla_doc_event);
333
334 log('p_xla_mode :'
335 || p_xla_ev_rec.xla_mode);
336
337 log('p_xla_call :'
338 || p_xla_ev_rec.xla_call);
339
340 log('p_xla_fetch_size:'
341 || p_xla_ev_rec.xla_fetch_size);
342
343 /*-----------------------------------------------------------------------+
344 | Create Events for documents |
345 +-----------------------------------------------------------------------*/
346 Create_All_Events(p_xla_ev_rec => p_xla_ev_rec);
347
348 /*-----------------------------------------------------------------------+
349 | Denormalize event ids on distributions |
350 +-----------------------------------------------------------------------*/
351 -- bug 5965006
352 g_call_number := 1;
353
354 IF p_xla_ev_rec.xla_doc_table IN ('CTCMAPP') THEN
355 g_call_number := 2;
356 END IF;
357
358 Upd_Dist(p_xla_ev_rec => p_xla_ev_rec);
359
360 IF p_xla_ev_rec.xla_doc_table IN ('CTCMAPP') THEN
361 g_call_number := 1;
362 Upd_Dist(p_xla_ev_rec => p_xla_ev_rec);
363 END IF;
364
365
366 log('ARP_XLA_EVENTS.Create_Events()-');
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 log('NO_DATA_FOUND EXCEPTION: ARP_XLA_EVENTS.Create_Events');
371 RAISE;
372
373 WHEN OTHERS THEN
374 log('OTHERS EXCEPTION: ARP_XLA_EVENTS.Create_Events');
375 RAISE;
376
377 END Create_Events;
378
379 /*========================================================================
380 | PUBLIC PROCEDURE Create_Events_doc
381 |
382 | DESCRIPTION
383 | Overload structure on the top of Create_events.
384 | This procedure is introduced to avoid record type structure.
385 | It is for the execution with document ids.
386 |
387 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
388 |
389 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
390 | a) Create_Events
391 |
392 | PARAMETERS
393 | 1) p_document_id IN NUMBER --document id from
394 | 2) p_doc_table IN VARCHAR2 --document table OLTP
395 | CT - Transactions
396 | ADJ - Adjustments
397 | CRH - Cash Receipt History
398 | CR - Cash Receipt History and Misc Cash
399 | Distributions
400 | MCD -Misc Cash Distributions
401 | APP -Applications for Receipts
402 | CMAPP -Applications for CM
403 | TRH - Transaction history for Bills
404 | 3) p_mode IN VARCHAR2
405 | U-upgrade
406 | O-oltp
407 | B-batch mode
408 | 4) p_call IN VARCHAR2
409 | C - Create events only
410 | D - Denormalize events only
411 | B - Create and Denormalize events on distributions
412 |
413 | KNOWN ISSUES
414 |
415 | NOTES
416 |
417 | MODIFICATION HISTORY
418 | Date Author Description of Changes
419 | 03-OCT-2002 Herve Yu Created
420 *=======================================================================*/
421 PROCEDURE create_events_doc( p_document_id IN NUMBER,
422 p_doc_table IN VARCHAR2,
423 p_mode IN VARCHAR2,
424 p_call IN VARCHAR2)
425 IS
426 l_xla_ev_rec arp_xla_events.xla_events_type;
427 BEGIN
428 log('arp_xla_events.create_events_doc ()+');
429 l_xla_ev_rec.xla_doc_table := p_doc_table;
430 l_xla_ev_rec.xla_from_doc_id := p_document_id;
431 l_xla_ev_rec.xla_to_doc_id := p_document_id;
432 l_xla_ev_rec.xla_mode := p_mode;
433 l_xla_ev_rec.xla_call := p_call;
434 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
435 log('arp_xla_events.create_events_doc ()-');
436 EXCEPTION
437 WHEN OTHERS THEN
438 log('EXCEPTION: arp_xla_events.create_events_doc');
439 RAISE;
440 END;
441
442
443 /*========================================================================
444 | PUBLIC PROCEDURE Create_Events_req
445 |
446 | DESCRIPTION
447 | Overload structure on the top of Create_events.
448 | This procedure is introduced to avoid record type structure.
449 | It is for the execution with request ids.
450 |
451 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
452 |
453 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
454 | a) Create_Events
455 |
456 | PARAMETERS
457 | 1) p_request_id IN NUMBER --request id from
458 | 2) p_doc_table IN VARCHAR2 --document table OLTP
459 | CT - Transactions
460 | ADJ - Adjustments
461 | CRH - Cash Receipt History
462 | CR - Cash Receipt History and Misc Cash
463 | Distributions
464 | MCD -Misc Cash Distributions
465 | APP -Applications for Receipts
466 | CMAPP -Applications for CM
467 | TRH - Transaction history for Bills
468 | 3) p_mode IN VARCHAR2
469 | U-upgrade
470 | O-oltp
471 | B-batch mode
472 | 4) p_call IN VARCHAR2
473 | C - Create events only
474 | D - Denormalize events only
475 | B - Create and Denormalize events on distributions
476 |
477 | KNOWN ISSUES
478 |
479 | NOTES
480 |
481 | MODIFICATION HISTORY
482 | Date Author Description of Changes
483 | 03-OCT-2002 Herve Yu Created
484 *=======================================================================*/
485 PROCEDURE create_events_req( p_request_id IN NUMBER,
486 p_doc_table IN VARCHAR2,
487 p_mode IN VARCHAR2,
488 p_call IN VARCHAR2)
489 IS
490 l_xla_ev_rec arp_xla_events.xla_events_type;
491 BEGIN
492 log('arp_xla_events.create_events_req ()+');
493 l_xla_ev_rec.xla_doc_table := p_doc_table;
494 l_xla_ev_rec.xla_req_id := p_request_id;
495 l_xla_ev_rec.xla_mode := p_mode;
496 l_xla_ev_rec.xla_call := p_call;
497 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
498 log('arp_xla_events.create_events_req ()-');
499 EXCEPTION
500 WHEN OTHERS THEN
501 log('EXCEPTION: arp_xla_events.create_events_req');
502 RAISE;
503 END;
504
505 /*========================================================================
506 | PUBLIC PROCEDURE Build_Stmt
507 |
508 | DESCRIPTION
509 | Build the dynamic SQL for creation of events or denormalization of
510 | events based on input parameter values.
511 |
512 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
513 | Get_Select_Cursor
514 |
515 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
516 |
517 | PARAMETERS p_ev_rec IN Event input parameter record
518 | p_stmt OUT Build dynamic SQL statement buffer
519 |
520 | KNOWN ISSUES
521 |
522 | NOTES
523 |
524 | MODIFICATION HISTORY
525 | Date Author Description of Changes
526 | 07-OCT-2002 Herve Add the bind_variable b_xla_mode.
527 |
528 | Need to differentiate the SQL statement by execution mode.
529 |
530 | For example in Upgrade mode or in OLTP, for a postable document,
531 | distribution stamped with a event_id,
532 | trx_status complete,
533 | no status for the event
534 | exist_dist_gl_date null is abnormal.
535 |
536 | * In OLTP, the OLTP sql will populate the event status.
537 | * In Upgrade mode the distribution stamped with a event_id is impossible.
538 | * But in Batch mode this situation can happen, when a receipt is created
539 | RECP_CREATE, then submit autoreceipt process in batch mode to create the
540 | the RECEIPT REMITTANCE record with the RECP_REMIT event.
541 | The previous RECP_CREATE event causes the situation described happen.
542 | - distribution is stamped True.
543 | - trx_complete
544 | - no status event and exist gl date is null because OLTP sql not executed.
545 | So by adding the clause based on :b_xla_mode, avoidance to retrieve the existing
546 | RECP_CREATE is accomplished so that the same situation of Upgrade happens.
547 |
548 *=======================================================================*/
549 PROCEDURE Build_Stmt(p_xla_ev_rec IN xla_events_type,
550 p_call_point IN NUMBER,
551 p_stmt OUT NOCOPY VARCHAR2) IS
552
553 l_select_clause VARCHAR2(10000);
554 l_from_clause VARCHAR2(2000);
555 l_where_parm_clause VARCHAR2(2000);
556 l_where_parm_clause_crh VARCHAR2(2000);
557 l_where_clause VARCHAR2(5000);
558 l_order_by_clause VARCHAR2(2000);
559 l_group_by_clause VARCHAR2(5000);
560 l_all_clause VARCHAR2(4) ;
561 l_union VARCHAR2(10) ;
562 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
563
564 BEGIN
565 log('ARP_XLA_EVENTS.Build_Stmt ()+');
566
567
568 IF p_xla_ev_rec.xla_doc_table IN ('CRHMCD','CRHAPP','CTCMAPP') THEN
569 l_union := ' UNION ';
570 END IF;
571
572 ------------------------------------------------------------------
573 --Set the bulk fetch size
574 ------------------------------------------------------------------
575 IF p_xla_ev_rec.xla_fetch_size IS NOT NULL THEN
576 MAX_ARRAY_SIZE := p_xla_ev_rec.xla_fetch_size;
577 END IF;
578
579 -------------------------------------------------------------------
580 --Set the all clause to access base tables
581 -------------------------------------------------------------------
582 IF p_xla_ev_rec.xla_mode = 'U' THEN
583 l_all_clause := '_all';
584 END IF;
585
586 -------------------------------------------------------------------
587 --Build Generic select fragments
588 -------------------------------------------------------------------
589
590 -------------------------------------------------------------------
591 -- Build statement for Transactions event creation
592 -------------------------------------------------------------------
593 IF (p_xla_ev_rec.xla_doc_table IN ('CT', 'CTCMAPP','CTNORCM')) THEN
594
595 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
596 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
597 --{BUG#5561163
598 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
599 l_where_parm_clause :=
600 ' AND ctlgd.customer_trx_id = :b_xla_from_doc_id
601 AND ctlgd.customer_trx_id = :b_xla_to_doc_id ' || CRLF;
602 ELSE
603 l_where_parm_clause :=
604 ' AND ctlgd.customer_trx_id >= :b_xla_from_doc_id
605 AND ctlgd.customer_trx_id <= :b_xla_to_doc_id ' || CRLF;
606 END IF;
607 --}
608 END IF;
609
610 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
611 l_where_parm_clause := l_where_parm_clause
612 || ' AND ctlgd.request_id = :b_xla_req_id ' || CRLF;
613 END IF;
614
615 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
616 l_where_parm_clause := l_where_parm_clause
617 || ' AND ctlgd.cust_trx_line_gl_dist_id = :b_xla_dist_id ' || CRLF;
618 END IF;
619
620 ------------------------------------------------------------------
621 -- Build the clause for transactions create events stmt
622 ------------------------------------------------------------------
623 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
624
625 l_select_clause :=
626 ' select
627 tty.post_to_gl POSTTOGL ,
628 tty.type TRX_TYPE ,
629 decode(ct.complete_flag,
630 ''Y'',''C'',
631 ''I'') COMP_FLAG ,
632 ctlgd.customer_trx_id TRX_ID ,
633 ct.trx_number TRX_NUMBER ,
634 ct.org_id ORG_ID ,
635 decode(nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
636 nvl(ctlgd1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
637 decode(ctlgd.posting_control_id,
638 ctlgd1.posting_control_id, tty.type || ''_CREATE'',
639 tty.type || ''_UPDATE''),
640 tty.type || ''_UPDATE'') OVERRIDE_EVENT ,
641 ctlgd.posting_control_id PSTID ,
642 nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
643 ctlgd.event_id EXIST_EVENT ,
644 '''' EVENT_ID ,
645 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
646 '''' EVENT_TYPE ,
647 ''X'' EVENT_STATUS ,
648 ct.trx_date TRANSACTION_DATE,
649 ct.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
650
651 l_from_clause :=
652 ' FROM ra_cust_trx_types' || l_all_clause || ' tty, ' || CRLF ||
653 ' ra_customer_trx' || l_all_clause || ' ct, ' || CRLF ||
654 ' ra_cust_trx_line_gl_dist' || l_all_clause || ' ctlgd1, ' || CRLF ||
655 ' ra_cust_trx_line_gl_dist' || l_all_clause || ' ctlgd ' || CRLF;
656
657 --note that the ctlgd1 fragment can be made dynamic for batch processes
658 --since none of the distributions will be posted.
659
660 l_where_clause :=
661 ' WHERE decode(ctlgd.account_class,
662 ''REC'',ctlgd.latest_rec_flag,
663 ''Y'') = ''Y''
664 AND DECODE(ctlgd.account_set_flag,
665 ''N'',''N'',
666 ''Y'', decode(ctlgd.account_class,
667 ''REC'',''N'',
668 ''Y'')
669 ) = ''N''
670 AND decode(ctlgd.event_id,
671 '''', ''Y'',
672 decode(:b_xla_mode, ''O'',''Y'',
673 ''N'')) = ''Y''
674 AND ctlgd.customer_trx_id = ct.customer_trx_id
675 AND ctlgd1.customer_trx_id = ct.customer_trx_id
676 AND ctlgd1.latest_rec_flag = ''Y''
677 AND ct.cust_trx_type_id = tty.cust_trx_type_id
678 AND nvl(tty.org_id,-9999) = nvl(ct.org_id,-9999) ' || CRLF;
679
680 --{CTCMAPP should pick only the regular CM
681 IF (p_xla_ev_rec.xla_doc_table IN ('CTCMAPP')) THEN
682 l_where_clause := l_where_clause ||
683 ' AND ct.PREVIOUS_CUSTOMER_TRX_ID IS NOT NULL
684 AND tty.type = ''CM'' ' || CRLF;
685 END IF;
686 --}
687
688 -- bug 5965006
689 IF (p_xla_ev_rec.xla_doc_table IN ('CTNORCM')) THEN
690 l_where_clause := l_where_clause ||
691 ' AND decode(tty.type, ''CM'',
692 decode(nvl(ct.PREVIOUS_CUSTOMER_TRX_ID,0),0,''Y'',
693 ''N''), ''Y'') = ''Y'' ' || CRLF;
694 END IF;
695
696 l_group_by_clause :=
697 ' GROUP BY
698 tty.post_to_gl,
699 tty.type,
700 decode(ct.complete_flag,
701 ''Y'',''C'',
702 ''I''),
703 ctlgd.customer_trx_id,
704 ct.trx_number,
705 ct.org_id,
706 decode(nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
707 nvl(ctlgd1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
708 decode(ctlgd.posting_control_id,
709 ctlgd1.posting_control_id, tty.type || ''_CREATE'',
710 tty.type || ''_UPDATE''),
711 tty.type || ''_UPDATE'') ,
712 ctlgd.posting_control_id,
713 nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
714 ctlgd.event_id ,
715 ct.trx_date,
716 ct.legal_entity_id'|| CRLF;
717
718 IF p_xla_ev_rec.xla_doc_table IN ('CT','CTNORCM') THEN
719 l_order_by_clause :=
720 'ORDER BY TRX_ID, OVERRIDE_EVENT, GL_DATE, PSTID DESC ';
721 END IF;
722
723 --------------------------------------------------------------------
724 -- Build the clause for Transactions denormalize events stmt
725 --------------------------------------------------------------------
726 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
727
728 log('Building Denormalize Transactions statement ' );
729
730 l_select_clause :=
731 --BUG#5415512
732 ' SELECT /*+ leading(ctlgd,evn,ev1) use_nl(evn,ev1) */
733 ctlgd.rowid,
734 ev1.event_id ';
735
736 l_from_clause :=
737 ' FROM xla_events' || ' ev1, ' || CRLF ||
738 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
739 ' ra_cust_trx_line_gl_dist' || l_all_clause || ' ctlgd ' || CRLF;
740
741 --{BUG#5131345 suggested by perf team
742
743 l_where_clause :=
744 ' WHERE ctlgd.account_set_flag = ''N''
745 AND ctlgd.event_id IS NULL
746 AND evn.entity_code = ''TRANSACTIONS''
747 AND evn.application_id = 222
748 AND ev1.entity_id = evn.entity_id
749 AND evn.application_id = 222
750 AND nvl(evn.source_id_int_1,-99) = ctlgd.customer_trx_id
751 AND evn.ledger_id = ctlgd.set_of_books_id
752 AND ev1.application_id = 222
753 AND ctlgd.posting_control_id = nvl(ev1.reference_num_1,-3)
754 AND nvl(ev1.event_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
755 = nvl(ctlgd.gl_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
756 AND (((ctlgd.posting_control_id = -3)
757 AND (ev1.event_status_code <> ''P''))
758 OR ((ctlgd.posting_control_id <> -3)
759 AND (ev1.event_status_code = ''P'' ))) ' || CRLF;
760
761 l_group_by_clause := '';
762
763 l_order_by_clause := '';
764
765 log('End Building Denormalize Transactions statement ' );
766 END IF; --create or update mode
767
768 END IF; --transaction
769
770 -------------------------------------------------------------------
771 -- Build statement for Adjustments event creation
772 -------------------------------------------------------------------
773 IF (p_xla_ev_rec.xla_doc_table = 'ADJ') THEN
774
775 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
776 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
777
778 --{BUG#5561163
779 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
780 l_where_parm_clause :=
781 ' AND adj.adjustment_id = :b_xla_from_doc_id
782 AND adj.adjustment_id = :b_xla_to_doc_id' || CRLF;
783 ELSE
784 l_where_parm_clause :=
785 ' AND adj.adjustment_id >= :b_xla_from_doc_id
786 AND adj.adjustment_id <= :b_xla_to_doc_id ' || CRLF;
787 END IF;
788 --}
789
790 END IF;
791
792 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
793 l_where_parm_clause := l_where_parm_clause
794 || ' AND adj.request_id = :b_xla_req_id ' || CRLF;
795 END IF;
796
797 --Not applicable to adjustment document
798 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
799 l_where_parm_clause := l_where_parm_clause ||
800 'AND adj.adjustment_id = :b_xla_dist_id ' || CRLF;
801 END IF;
802
803 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
804
805 --HYU trsnaction_date,legal_entity_id
806 l_select_clause :=
807 ' select
808 decode(tty.post_to_gl,''Y'',tty.post_to_gl,nvl(tty.adj_post_to_gl,''N'')) POSTTOGL ,
809 ''ADJ'' TRX_TYPE ,
810 decode(adj.status,
811 ''A'',''C'',
812 ''I'') COMP_FLAG ,
813 adj.adjustment_id TRX_ID ,
814 adj.adjustment_number TRX_NUMBER ,
815 adj.org_id ORG_ID,
816 ''ADJ_CREATE'' OVERRIDE_EVENT ,
817 adj.posting_control_id PSTID ,
818 nvl(adj.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
819 adj.event_id EXIST_EVENT ,
820 '''' EVENT_ID ,
821 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
822 '''' EVENT_TYPE ,
823 ''X'' EVENT_STATUS ,
824 ct.trx_date TRANSACTION_DATE,
825 ct.legal_entity_id LEGAL_ENTITY_ID' || CRLF;
826
827 l_from_clause :=
828 ' FROM ra_cust_trx_types' || l_all_clause || ' tty, ' || CRLF ||
829 ' ra_customer_trx' || l_all_clause || ' ct, ' || CRLF ||
830 ' ar_adjustments' || l_all_clause || ' adj ' || CRLF;
831
832 ----------------------------------------------------------------------------------
833 --A script to denormalize the BR_ADJUSTMENT_ID on adjustment table may be written
834 --Open issue - Should Invoice against Deposit, Guarantee, shadow adjustments for
835 --Bills Receivable and chargeback adjustments be created as a seperate adjustment
836 --event or tracked as Create Invoice, Create Bills Receivable, Create Chargeback
837 --events respectively. As of now for simplicity purposes seperate events are
838 --retained.
839 --Note : If treated as main transaction events then if gl date is different from
840 --Invoice GL date for Create Invoice event, should the next event by Modify Invoice
841 --or a new event called Adjust Invoice , or should we always have an adjust Invoice
842 --event for shuch mergable evcent cases. Adjustments -> ADJ_CREATE, OR INV_ADJUST
843 --Should a check be made to create events based on whether the adjustment has a
844 --document sequence number ?
845 ----------------------------------------------------------------------------------
846 l_where_clause :=
847 ' WHERE adj.customer_trx_id = ct.customer_trx_id
848 AND ct.cust_trx_type_id = tty.cust_trx_type_id
849 AND decode(adj.event_id,
850 '''', ''Y'',
851 decode(:b_xla_mode, ''O'',''Y'',
852 ''N'')) = ''Y''
853 AND nvl(tty.org_id,-9999) = nvl(ct.org_id,-9999) ' || CRLF;
854
855 ----------------------------------------------------------------------------------
856 --Open issue whether adjustments require to have a single event by adjusted document
857 --because they have document sequencing, or do we need to create a seperate event
858 --as INVOICE_ADJUST ? The group by clause will ascertain the uniqueness of the
859 --created events. The solution may be to create the event as adjustment if document
860 --sequence is populated else as an adjust transaction type event
861 ----------------------------------------------------------------------------------
862 l_group_by_clause := '';
863
864 l_order_by_clause := '';
865
866
867 ------------------------------------------------------------------
868 -- Build the clause for adjustments denormalize events stmt
869 ------------------------------------------------------------------
870 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
871
872 log('Building Denormalize Adjustments statement ' );
873 l_select_clause :=
874 ' SELECT adj.rowid,
875 ev1.event_id ';
876
877 l_from_clause :=
878 ' FROM xla_events' || ' ev1, ' || CRLF ||
879 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
880 ' ar_adjustments' || l_all_clause || ' adj ' || CRLF;
881
882 l_where_clause :=
883 ' WHERE adj.event_id IS NULL
884 AND evn.entity_code = ''ADJUSTMENTS''
885 AND evn.application_id = 222
886 AND ev1.entity_id = evn.entity_id
887 AND NVL(evn.source_id_int_1,-99) = adj.adjustment_id
888 AND evn.ledger_id = adj.set_of_books_id
889 AND ev1.application_id = 222
890 AND adj.posting_control_id = nvl(ev1.reference_num_1,-3)
891 AND (((adj.posting_control_id = -3)
892 AND (ev1.event_status_code <> ''P''))
893 OR ((adj.posting_control_id <> -3)
894 AND (ev1.event_status_code = ''P'' ))) ' || CRLF;
895
896
897 l_group_by_clause := '';
898
899 l_order_by_clause := '';
900
901 log('End Building Denormalize Adjustments statement ' );
902
903 END IF; --Create event or denormalize
904
905 END IF; --build adjustment event
906
907 -------------------------------------------------------------------
908 -- Build statement for Cash Receipts event creation
909 -------------------------------------------------------------------
910 IF (p_xla_ev_rec.xla_doc_table IN ('CRH','CRHMCD','CRHAPP')) THEN
911
912 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
913 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
914
915 --{BUG#5561163
916 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
917 l_where_parm_clause :=
918 ' AND crh.cash_receipt_id = :b_xla_from_doc_id
919 AND crh.cash_receipt_id = :b_xla_to_doc_id ' || CRLF;
920 ELSE
921 l_where_parm_clause :=
922 ' AND crh.cash_receipt_id >= :b_xla_from_doc_id
923 AND crh.cash_receipt_id <= :b_xla_to_doc_id ' || CRLF;
924 END IF;
925 --}
926 END IF;
927
928 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
929 l_where_parm_clause := l_where_parm_clause
930 || ' AND crh.request_id = :b_xla_req_id ' || CRLF;
931 END IF;
932
933 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
934 l_where_parm_clause := l_where_parm_clause
935 || ' AND crh.cash_receipt_history_id = :b_xla_dist_id ' || CRLF;
936 END IF;
937
938 ------------------------------------------------------------------
939 -- Build the clause for cash receipt history create events stmt
940 -- In Reality there should be a Union between APP and CRH
941 --Note for CRH for APPROVED status 1952 has been seeded as the
942 --gl date, this is defaulted to 1900 to enable change matrix
943 --processing, in the event record a null gl date will exist
944 ------------------------------------------------------------------
945 -- This code is missing the following business rule :
946 -- If the previous CRH record is posted then no matter of status
947 -- event is RECP_CREATE.
948 -- For example,
949 -- 1) we create a receipt with the status REMITTED
950 -- it will first create a RECP_CREATE event because its first posted flag is Y
951 -- 2) we posted the receipt.
952 -- 3) we increase the amount of the receipt, AR will create another CRH with the
953 -- status REMITTED with the first posted rec flag <> Y
954 -- So it will generate a RECP_REMIT event. Which is incorrect we need to create
955 -- another RECP_CREATE.
956 ------------------------------------------------------------------
957
958 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
959
960 l_select_clause :=
961 ' select
962 crh.postable_flag POSTTOGL ,
963 ''RECP'' TRX_TYPE ,
964 decode(crh.status,
965 ''APPROVED'', ''I'',
966 ''C'') COMP_FLAG ,
967 crh.cash_receipt_id TRX_ID ,
968 cr.receipt_number TRX_NUMBER ,
969 cr.org_id ORG_ID,
970 decode(cr.type,
971 ''MISC'',''MISC_'',
972 '''') ||
973 decode(crh.created_from,
974 ''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
975 decode(crh.status,
976 ''REVERSED'',''RECP_REVERSE'',
977 decode(crh1.first_posted_record_flag,
978 '''', ''RECP_CREATE'',
979 decode(decode(crh.postable_flag,
980 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
981 nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
982 nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
983 decode(crh.posting_control_id,
984 crh1.posting_control_id, ''RECP_CREATE'',
985 ''RECP_UPDATE''),
986 ''RECP_UPDATE'')))) OVERRIDE_EVENT,
987 crh.posting_control_id PSTID ,
988 decode(crh.postable_flag,
989 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
990 nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))) GL_DATE,
991 crh.event_id EXIST_EVENT,
992 '''' EVENT_ID ,
993 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
994 '''' EVENT_TYPE ,
995 ''X'' EVENT_STATUS ,
996 cr.receipt_date TRANSACTION_DATE,
997 cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
998
999 l_from_clause :=
1000 ' FROM ar_cash_receipts' || l_all_clause || ' cr, ' || CRLF ||
1001 ' ar_cash_receipt_history' || l_all_clause || ' crh1, ' || CRLF ||
1002 ' ar_cash_receipt_history' || l_all_clause || ' crh ' || CRLF;
1003
1004 l_where_clause := ' WHERE crh.cash_receipt_id = cr.cash_receipt_id ' || CRLF ||
1005 ' AND cr.cash_receipt_id = crh1.cash_receipt_id (+) ' || CRLF ||
1006 ' AND ''Y'' = crh1.first_posted_record_flag (+) ' || CRLF ||
1007 ' AND decode(crh.event_id,
1008 '''', ''Y'',
1009 decode(:b_xla_mode, ''O'',''Y'',
1010 ''N'')) = ''Y'' ' || CRLF ||
1011 ' AND decode(crh.postable_flag, ''Y'',''Y'', ' || CRLF ||
1012 ' decode(crh.status, ''APPROVED'', ' || CRLF ||
1013 ' decode(crh1.first_posted_record_flag, '''',''Y'', ' || CRLF ||
1014 ' ''N''), ' || CRLF ||
1015 ' ''N'')) = ''Y'' ' || CRLF ;
1016
1017 IF p_xla_ev_rec.xla_doc_table IN ('CRHMCD','CRHAPP') THEN
1018 l_where_clause := l_where_clause || l_where_parm_clause;
1019 END IF;
1020
1021 l_group_by_clause :=
1022 ' GROUP BY
1023 crh.cash_receipt_id,
1024 cr.receipt_number,
1025 cr.org_id,
1026 decode(crh.status,
1027 ''APPROVED'', ''I'',
1028 ''C''),
1029 crh.postable_flag,
1030 crh.posting_control_id,
1031 decode(cr.type,
1032 ''MISC'',''MISC_'',
1033 '''') ||
1034 decode(crh.created_from,
1035 ''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
1036 decode(crh.status,
1037 ''REVERSED'',''RECP_REVERSE'',
1038 decode(crh1.first_posted_record_flag,
1039 '''', ''RECP_CREATE'',
1040 decode(decode(crh.postable_flag,
1041 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1042 nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
1043 nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1044 decode(crh.posting_control_id,
1045 crh1.posting_control_id, ''RECP_CREATE'',
1046 ''RECP_UPDATE''),
1047 ''RECP_UPDATE'')))),
1048 decode(crh.postable_flag,
1049 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1050 nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
1051 crh.event_id,
1052 cr.receipt_date,
1053 cr.legal_entity_id' || CRLF;
1054
1055 /*---------------------------------------------------------------------+
1056 |The where clause below is appended to the Select which pulls data |
1057 |from the inline query. Hence it appears odd that order by should have|
1058 |a where but nevertheless it is required |
1059 +---------------------------------------------------------------------*/
1060 /** BUG 6660834
1061 We can directly use the field OVERRIDE_EVENT in order by clause, s the value
1062 itself will maintain the order we are expecting in all the cases except in case
1063 where we have both RECP_UPDATE and RECP_REVERSE in the same call,this
1064 is only possible in case of an upgrade and this is not used for upgrade*/
1065 /*l_order_by_clause := l_order_by_clause ||
1066 'ORDER BY TRX_ID,
1067 decode(OVERRIDE_EVENT,
1068 ''RECP_CREATE'' ,1,
1069 ''RECP_UPDATE'' ,2,
1070 ''RECP_RATE_ADJUST'' ,3,
1071 ''RECP_REVERSE'' ,6,
1072 ''MISC_RECP_CREATE'' ,1,
1073 ''MISC_RECP_UPDATE'' ,2,
1074 ''MISC_RECP_RATE_ADJUST'' ,3,
1075 ''MISC_RECP_REVERSE'', 6,
1076 7),
1077 GL_DATE,
1078 PSTID desc ' || CRLF;*/
1079
1080 l_order_by_clause := l_order_by_clause || 'ORDER BY TRX_ID,OVERRIDE_EVENT,GL_DATE, PSTID desc ' || CRLF;
1081
1082 log('l_select_clause = ' || l_select_clause);
1083 log('l_order_by_clause = ' || l_order_by_clause);
1084
1085 ---------------------------------------------------------------------
1086 -- Build the clause for Cash Receipt History denormalize events stmt
1087 ---------------------------------------------------------------------
1088 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1089
1090 log('Building Denormalize Cash Receipt History statement ' );
1091
1092 l_select_clause :=
1093 ' SELECT crh.rowid,
1094 ev1.event_id ';
1095
1096 l_from_clause :=
1097 ' FROM xla_events' || ' ev1, ' || CRLF ||
1098 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1099 ' ar_cash_receipts' || l_all_clause || ' cr, ' || CRLF ||
1100 ' ar_cash_receipt_history' || l_all_clause || ' crh ' || CRLF ;
1101
1102 l_where_clause :=
1103 ' WHERE
1104 decode(crh.created_from,
1105 ''RATE ADJUSTMENT TRIGGER'',
1106 decode(cr.type,''MISC'',''MISC_'','''') || ''RECP_RATE_ADJUST'',
1107 decode(crh.status,
1108 ''REVERSED'',
1109 decode(cr.type,''MISC'',''MISC_'','''') || ''RECP_REVERSE'',
1110 ev1.event_type_code)) = ev1.event_type_code
1111 AND crh.event_id IS NULL
1112 AND ev1.entity_id = evn.entity_id
1113 AND ev1.application_id = 222
1114 AND evn.application_id = 222
1115 AND evn.entity_code = ''RECEIPTS''
1116 AND crh.cash_receipt_id = NVL(evn.source_id_int_1,-99)
1117 AND evn.ledger_id = cr.set_of_books_id
1118 AND crh.cash_receipt_id = cr.cash_receipt_id
1119 AND crh.posting_control_id = nvl(ev1.reference_num_1,-3)
1120 AND decode(crh.postable_flag,
1121 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1122 nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')))
1123 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1124 AND (((crh.posting_control_id = -3)
1125 AND (ev1.event_status_code <> ''P''))
1126 OR ((crh.posting_control_id <> -3)
1127 AND (ev1.event_status_code = ''P'' ))) ' || CRLF;
1128
1129 l_group_by_clause := '';
1130
1131 l_order_by_clause := '';
1132
1133 log('End Building Denormalize Cash Receipt History statement ' );
1134
1135 END IF; --create or update mode
1136
1137 END IF; --transaction
1138
1139 -------------------------------------------------------------------
1140 -- Build statement for Misc Cash Distributions events creation
1141 -------------------------------------------------------------------
1142 IF p_xla_ev_rec.xla_doc_table IN ('MCD', 'CRHMCD') THEN
1143
1144 IF p_xla_ev_rec.xla_call IN ('C', 'B')
1145 AND p_call_point = 1 AND p_xla_ev_rec.xla_doc_table = 'CRHMCD' THEN
1146
1147 l_where_parm_clause_crh := l_where_parm_clause;
1148 l_where_parm_clause := '';
1149
1150 END IF;
1151
1152 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
1153 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
1154
1155
1156 --{BUG#5561163
1157 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
1158 l_where_parm_clause :=
1159 ' AND mcd.cash_receipt_id = :b_xla_from_doc_id
1160 AND mcd.cash_receipt_id = :b_xla_to_doc_id ' || CRLF;
1161 ELSE
1162 l_where_parm_clause :=
1163 ' AND mcd.cash_receipt_id >= :b_xla_from_doc_id
1164 AND mcd.cash_receipt_id <= :b_xla_to_doc_id ' || CRLF;
1165 END IF;
1166 --}
1167 END IF;
1168
1169 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
1170 l_where_parm_clause := l_where_parm_clause
1171 || ' AND mcd.request_id = :b_xla_req_id ' || CRLF;
1172 END IF;
1173
1174 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
1175 l_where_parm_clause := l_where_parm_clause
1176 || ' AND mcd.misc_cash_distribution_id = :b_xla_dist_id ' || CRLF;
1177 END IF;
1178
1179 ------------------------------------------------------------------
1180 -- Build the clause for Misc cash receipt create events stmt
1181 -- In Reality there should be a Union between APP and CRH
1182 --Note for CRH for APPROVED status 1952 has been seeded as the
1183 --gl date, this is defaulted to 1900 to enable change matrix
1184 --processing, in the event record a null gl date will exist
1185 ------------------------------------------------------------------
1186 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1187
1188 IF p_xla_ev_rec.xla_doc_table = 'CRHMCD' THEN
1189 l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
1190 l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
1191 END IF;
1192
1193 --HYU transaction_date,legal_entity_id
1194 l_select_clause := l_select_clause ||
1195 ' select
1196 ''Y'' POSTTOGL ,
1197 ''RECP'' TRX_TYPE ,
1198 ''C'' COMP_FLAG ,
1199 mcd.cash_receipt_id TRX_ID ,
1200 cr.receipt_number TRX_NUMBER ,
1201 cr.org_id ORG_ID,
1202 decode(mcd.created_from,
1203 ''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
1204 decode(SUBSTRB(mcd.created_from,1,19),
1205 ''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
1206 decode(nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1207 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1208 decode(crh.posting_control_id,
1209 mcd.posting_control_id, ''MISC_RECP_CREATE'',
1210 ''MISC_RECP_UPDATE''),
1211 ''MISC_RECP_UPDATE''))) OVERRIDE_EVENT,
1212 mcd.posting_control_id PSTID ,
1213 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
1214 mcd.event_id EXIST_EVENT,
1215 '''' EVENT_ID ,
1216 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1217 '''' EVENT_TYPE ,
1218 ''X'' EVENT_STATUS,
1219 cr.receipt_date TRANSACTION_DATE,
1220 cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
1221
1222 log('l_select_clause ' || l_select_clause);
1223
1224 l_from_clause :=
1225 ' FROM ar_misc_cash_distributions' || l_all_clause || ' mcd, ' || CRLF ||
1226 ' ar_cash_receipts' || l_all_clause || ' cr, ' || CRLF ||
1227 ' ar_cash_receipt_history' || l_all_clause || ' crh ' || CRLF;
1228
1229 log('l_from_clause ' || l_from_clause);
1230
1231 l_where_clause := ' WHERE 1 = 1 ' || CRLF ||
1232 ' AND mcd.cash_receipt_id = cr.cash_receipt_id ' || CRLF ||
1233 ' AND mcd.cash_receipt_id = crh.cash_receipt_id '|| CRLF ||
1234 ' AND crh.first_posted_record_flag = ''Y'' ' || CRLF;
1235
1236 l_group_by_clause :=
1237 ' GROUP BY mcd.cash_receipt_id,
1238 cr.receipt_number,
1239 cr.org_id,
1240 mcd.posting_control_id,
1241 decode(mcd.created_from,
1242 ''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
1243 decode(SUBSTRB(mcd.created_from,1,19),
1244 ''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
1245 decode(nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1246 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1247 decode(crh.posting_control_id,
1248 mcd.posting_control_id, ''MISC_RECP_CREATE'',
1249 ''MISC_RECP_UPDATE''),
1250 ''MISC_RECP_UPDATE''))),
1251 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1252 mcd.event_id ,
1253 cr.receipt_date,
1254 cr.legal_entity_id ' || CRLF;
1255
1256 IF p_xla_ev_rec.xla_doc_table = 'MCD' THEN
1257
1258 l_order_by_clause :=
1259 'ORDER BY TRX_ID,
1260 decode(OVERRIDE_EVENT,
1261 ''MISC_RECP_CREATE'' ,1,
1262 ''MISC_RECP_UPDATE'' ,2,
1263 ''MISC_RECP_RATE_ADJUST'',3,
1264 ''MISC_RECP_REVERSE'' ,6,
1265 7),
1266 GL_DATE,
1267 PSTID desc ';
1268 END IF;
1269
1270 log('l_select_clause = ' || l_select_clause);
1271 log('l_where_clause = ' || l_where_clause);
1272 log('l_group_by_clause = ' || l_group_by_clause);
1273 log('l_order_by_clause = ' || l_order_by_clause);
1274
1275 ------------------------------------------------------------------
1276 -- Build the clause for Misc Cash Receipts denormalize events stmt
1277 ------------------------------------------------------------------
1278 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1279
1280 log('Building Denormalize Misc Cash Distributions statement ' );
1281 /* Bug 6747662 JVARKEY Modified to check for the rate adjustment event
1282 from the last record of CRH rather than MCD. The reasom being if the
1283 receipt is cleared with different rate then a new event won't be
1284 created but MCD will say creted from Rate Adjustment. So we should
1285 check created from from CRH */
1286 l_select_clause :=
1287 ' SELECT mcd.rowid,
1288 ev1.event_id ';
1289
1290 l_from_clause :=
1291 ' FROM xla_events' || ' ev1, ' || CRLF ||
1292 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1293 ' ar_cash_receipt_history' || l_all_clause || ' crh, ' || CRLF ||
1294 ' ar_misc_cash_distributions' || l_all_clause || ' mcd ' || CRLF ;
1295
1296 l_where_clause :=
1297 ' WHERE decode(crh.created_from,
1298 ''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
1299 decode(SUBSTRB(mcd.created_from,1,19),
1300 ''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
1301 ev1.event_type_code)) = ev1.event_type_code
1302 AND crh.cash_receipt_id = mcd.cash_receipt_id
1303 AND crh.current_record_flag = ''Y''
1304 AND mcd.event_id IS NULL
1305 AND ev1.entity_id = evn.entity_id
1306 AND evn.application_id = 222
1307 AND ev1.application_id = 222
1308 AND evn.entity_code = ''RECEIPTS''
1309 AND mcd.cash_receipt_id = NVL(evn.source_id_int_1,-99)
1310 AND evn.ledger_id = mcd.set_of_books_id
1311 AND nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
1312 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1313 AND mcd.posting_control_id = nvl(ev1.reference_num_1,-3)
1314 AND decode(mcd.posting_control_id,
1315 -3, ev1.event_status_code,
1316 ''P'') = ev1.event_status_code' || CRLF;
1317
1318 l_group_by_clause := '';
1319
1320 l_order_by_clause := '';
1321
1322 log('End Building Denormalize Misc Cash Distributions statement ' );
1323 END IF; --create or update mode
1324
1325 END IF; --create and denormalize events Misc Cash Receipts
1326
1327 -------------------------------------------------------------------
1328 -- Build parameter clause for Applications Receipts and CM
1329 -------------------------------------------------------------------
1330 IF p_xla_ev_rec.xla_doc_table IN ('APP','CRHAPP','CMAPP','CTCMAPP') THEN
1331
1332 IF p_xla_ev_rec.xla_call IN ('C', 'B')
1333 AND p_call_point = 1 AND p_xla_ev_rec.xla_doc_table IN ('CRHAPP','CTCMAPP') THEN
1334
1335 l_where_parm_clause_crh := l_where_parm_clause;
1336 l_where_parm_clause := '';
1337
1338 END IF;
1339
1340 -- bug 5965006
1341 IF p_xla_ev_rec.xla_doc_table IN ('CTCMAPP') AND p_call_point = 2 AND g_call_number = 1 THEN
1342 l_where_parm_clause := '';
1343 END IF;
1344
1345 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
1346 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
1347
1348 --{BUG#5561163
1349 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
1350 l_where_parm_clause :=
1351 ' AND app.receivable_application_id = :b_xla_from_doc_id
1352 AND app.receivable_application_id = :b_xla_to_doc_id ' || CRLF;
1353 ELSE
1354 l_where_parm_clause :=
1355 ' AND app.receivable_application_id >= :b_xla_from_doc_id
1356 AND app.receivable_application_id <= :b_xla_to_doc_id ' || CRLF;
1357 END IF;
1358 --}
1359 END IF;
1360 -- bug 5965006
1361 IF p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND p_call_point = 2 AND g_call_number = 2 THEN
1362 NULL;
1363 ELSE
1364 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
1365 l_where_parm_clause := l_where_parm_clause
1366 || ' AND app.request_id = :b_xla_req_id ' || CRLF;
1367 END IF;
1368 END IF;
1369 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
1370 l_where_parm_clause := l_where_parm_clause
1371 || ' AND app.receivable_application_id = :b_xla_dist_id ' || CRLF;
1372 END IF;
1373
1374 END IF; --App and CM app parameter construction section
1375
1376 -------------------------------------------------------------------
1377 -- Build statement for Receivable Applications events creation
1378 -------------------------------------------------------------------
1379 IF p_xla_ev_rec.xla_doc_table IN ('APP', 'CRHAPP') THEN
1380
1381 ------------------------------------------------------------------
1382 -- Build the clause for Misc cash receipt create events stmt
1383 -- In Reality there should be a Union between APP and CRH
1384 --Note for CRH for APPROVED status 1952 has been seeded as the
1385 --gl date, this is defaulted to 1900 to enable change matrix
1386 --processing, in the event record a null gl date will exist
1387 --
1388 --Resolved Issues
1389 -----------------
1390 --I -It is not possible to accurately identify the
1391 --Unapply receipt application event, because positive and negative
1392 --application amounts can be created in Receivables to bump up
1393 --or down the from document (Receipt) and two document balcnce,
1394 --
1395 --II - A Receipt application reversed due to Receipt reversal is tagged
1396 --under the Reverse Receipt event as against the traditionaly event
1397 --apply receipt.
1398 --
1399 ------------------------------------------------------------------------
1400 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1401
1402 IF p_xla_ev_rec.xla_doc_table = 'CRHAPP' THEN
1403 l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
1404 l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
1405 END IF;
1406
1407 /* Bug 9761480 : Incorrect OVERRIDE_EVENT 'RECP_RATE_ADJUST' for application records */
1408 l_select_clause := l_select_clause ||
1409 ' select
1410 decode(app.postable,
1411 ''N'',''N'',
1412 ''Y'') POSTTOGL ,
1413 ''RECP'' TRX_TYPE ,
1414 decode(NVL(app.confirmed_flag,''Y''),
1415 ''Y'',''C'',
1416 ''N'') COMP_FLAG ,
1417 cr.cash_receipt_id TRX_ID , --BUG#3554871
1418 cr.receipt_number TRX_NUMBER ,
1419 cr.org_id ORG_ID ,
1420 decode(crh.created_from,
1421 ''RATE ADJUSTMENT TRIGGER'',
1422 decode(crh.posting_control_id, -3,
1423 decode(crh.gl_date,app.gl_date,''RECP_RATE_ADJUST'',''RECP_UPDATE''), ''RECP_UPDATE''),
1424 decode(crh.status,
1425 ''REVERSED'',''RECP_REVERSE'',
1426 decode(crh1.first_posted_record_flag,
1427 '''', ''RECP_CREATE'',
1428 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1429 nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1430 decode(app.posting_control_id,
1431 crh1.posting_control_id, ''RECP_CREATE'',
1432 ''RECP_UPDATE''),
1433 ''RECP_UPDATE'')))) OVERRIDE_EVENT,
1434 app.posting_control_id PSTID ,
1435 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
1436 app.event_id EXIST_EVENT ,
1437 '''' EVENT_ID ,
1438 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1439 '''' EVENT_TYPE ,
1440 ''X'' EVENT_STATUS ,
1441 cr.receipt_date TRANSACTION_DATE,
1442 cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
1443
1444 l_from_clause :=
1445 ' FROM ar_receivable_applications' || l_all_clause || ' app, ' || CRLF ||
1446 ' ar_cash_receipt_history' || l_all_clause || ' crh, ' || CRLF ||
1447 ' ar_cash_receipt_history' || l_all_clause || ' crh1, ' || CRLF ||
1448 ' ar_cash_receipts' || l_all_clause || ' cr ' || CRLF ;
1449
1450 --{
1451 -- The join to AND app.status IN (''APP'',''ACTIVITY'',''OTHER ACC'',''ACC'',''UNID'')
1452 -- has been removed in the denormalisation for APPLICATION
1453 --}
1454 l_where_clause :=
1455 ' WHERE app.application_type = ''CASH''
1456 AND app.cash_receipt_history_id = crh.cash_receipt_history_id
1457 AND app.cash_receipt_id = cr.cash_receipt_id
1458 AND cr.cash_receipt_id = crh1.cash_receipt_id (+)
1459 AND ''Y'' = crh1.first_posted_record_flag (+)
1460 AND decode(app.event_id,
1461 '''', ''Y'',
1462 decode(:b_xla_mode, ''O'',''Y'',
1463 ''N'')) = ''Y'' '|| CRLF ;
1464
1465 l_group_by_clause :=
1466 ' GROUP BY
1467 cr.cash_receipt_id,
1468 cr.receipt_number,
1469 cr.org_id,
1470 decode(NVL(app.confirmed_flag,''Y''),
1471 ''Y'',''C'',
1472 ''N''),
1473 app.posting_control_id,
1474 decode(app.postable,
1475 ''N'',''N'',
1476 ''Y''),
1477 decode(crh.created_from,
1478 ''RATE ADJUSTMENT TRIGGER'',
1479 decode(crh.posting_control_id, -3,
1480 decode(crh.gl_date,app.gl_date,''RECP_RATE_ADJUST'',''RECP_UPDATE''), ''RECP_UPDATE''),
1481 decode(crh.status,
1482 ''REVERSED'',''RECP_REVERSE'',
1483 decode(crh1.first_posted_record_flag,
1484 '''', ''RECP_CREATE'',
1485 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1486 nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1487 decode(app.posting_control_id,
1488 crh1.posting_control_id, ''RECP_CREATE'',
1489 ''RECP_UPDATE''),
1490 ''RECP_UPDATE'')))),
1491 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1492 app.event_id ,
1493 cr.receipt_date,
1494 cr.legal_entity_id' || CRLF;
1495
1496 IF p_xla_ev_rec.xla_doc_table = 'APP' THEN
1497
1498 l_order_by_clause :=
1499 'ORDER BY TRX_ID,
1500 decode(OVERRIDE_EVENT,
1501 ''RECP_CREATE'' ,1,
1502 ''RECP_UPDATE'' ,2,
1503 ''RECP_RATE_ADJUST'',3,
1504 ''RECP_REVERSE'' ,6,
1505 7),
1506 GL_DATE,
1507 PSTID desc ';
1508 END IF;
1509
1510 --------------------------------------------------------------------
1511 -- Build the clause for Receipts applications denormalize event stmt
1512 --------------------------------------------------------------------
1513 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1514
1515 log('Building Denormalize Receivable Apps statement ' );
1516
1517 l_select_clause :=
1518 ' SELECT app.rowid,
1519 ev1.event_id ';
1520
1521 log('l_select_clause ' || l_select_clause);
1522
1523 l_from_clause :=
1524 ' FROM xla_events' || ' ev1, ' || CRLF ||
1525 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1526 ' ar_receivable_applications' || l_all_clause || ' app, ' || CRLF ||
1527 ' ar_cash_receipt_history' || l_all_clause || ' crh ' || CRLF ;
1528
1529 log('l_from_clause ' || l_from_clause);
1530 --{
1531 -- The join to AND app.status IN (''APP'',''ACTIVITY'',''OTHER ACC'',''ACC'', ''UNID'')
1532 -- has been removed in the denormalisation for APPLICATION
1533 -- Use xla_transaction_entity_n1
1534 --}
1535 /* Bug 9761480 : Incorrect RATE ADJUSTMENT TRIGGER event_type_code stamped on application record */
1536 l_where_clause :=
1537 ' WHERE app.application_type = ''CASH''
1538 AND app.event_id IS NULL
1539 AND app.cash_receipt_history_id = crh.cash_receipt_history_id
1540 AND decode(crh.created_from,
1541 ''RATE ADJUSTMENT TRIGGER'', decode(crh.posting_control_id, -3,
1542 decode(crh.gl_date,app.gl_date,''RECP_RATE_ADJUST'',''RECP_UPDATE''), ''RECP_UPDATE''),
1543 decode(crh.status,
1544 ''REVERSED'', ''RECP_REVERSE'',
1545 ev1.event_type_code)) = ev1.event_type_code
1546 AND app.posting_control_id = nvl(ev1.reference_num_1,-3)
1547 AND evn.entity_code = ''RECEIPTS''
1548 AND ev1.entity_id = evn.entity_id
1549 AND evn.application_id = 222
1550 AND ev1.application_id = 222
1551 AND app.cash_receipt_id = NVL(evn.source_id_int_1,-99)
1552 AND evn.ledger_id = app.set_of_books_id
1553 AND nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
1554 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1555 AND (((app.posting_control_id = -3)
1556 AND (ev1.event_status_code <> ''P''))
1557 OR ((app.posting_control_id <> -3)
1558 AND (ev1.event_status_code = ''P'' ))) ' || CRLF;
1559
1560
1561
1562 log('l_where_clause' || l_from_clause);
1563
1564 l_group_by_clause := '';
1565
1566 l_order_by_clause := '';
1567
1568 log('End Building Denormalize Receivable Apps statement ' );
1569
1570 END IF; --create or update mode
1571
1572 END IF; --create and denormalize events Receivable applications
1573
1574 -------------------------------------------------------------------
1575 -- Build statement for Receivable Applications events creation
1576 -------------------------------------------------------------------
1577 IF p_xla_ev_rec.xla_doc_table IN ('CMAPP', 'CTCMAPP') THEN
1578
1579 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1580
1581 IF p_xla_ev_rec.xla_doc_table = 'CTCMAPP' THEN
1582 l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
1583 l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
1584 END IF;
1585
1586 l_select_clause := l_select_clause ||
1587 ' select
1588 decode(app.postable,
1589 ''N'',''N'',
1590 ''Y'') POSTTOGL ,
1591 ''CM'' TRX_TYPE ,
1592 decode(NVL(app.confirmed_flag,''Y''),
1593 ''Y'',''C'',
1594 ''N'') COMP_FLAG ,
1595 ctlgd.customer_trx_id TRX_ID, --BUG#3554871
1596 ct.trx_number TRX_NUMBER ,
1597 app.org_id ORG_ID ,
1598 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1599 nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1600 decode(app.posting_control_id,
1601 ctlgd.posting_control_id, ''CM_CREATE'',
1602 ''CM_UPDATE''),
1603 ''CM_UPDATE'') OVERRIDE_EVENT ,
1604 app.posting_control_id PSTID ,
1605 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
1606 app.event_id EXIST_EVENT ,
1607 '''' EVENT_ID ,
1608 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1609 '''' EVENT_TYPE ,
1610 ''X'' EVENT_STATUS ,
1611 ct.trx_date TRANSACTION_DATE,
1612 ct.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
1613
1614 l_from_clause :=
1615 ' FROM ar_receivable_applications' || l_all_clause || ' app, ' ||
1616 ' ra_cust_trx_line_gl_dist' || l_all_clause || ' ctlgd, ' ||
1617 ' ra_customer_trx' || l_all_clause || ' ct ' || CRLF ;
1618
1619 l_where_clause :=
1620 ' WHERE app.application_type = ''CM''
1621 AND app.status IN (''APP'',''ACTIVITY'') --HYU
1622 AND ctlgd.customer_trx_id = app.customer_trx_id
1623 AND ctlgd.latest_rec_flag = ''Y''
1624 AND ctlgd.customer_trx_id = ct.customer_trx_id
1625 AND decode(app.event_id,
1626 '''', ''Y'',
1627 decode(:b_xla_mode, ''O'',''Y'',
1628 ''N'')) = ''Y'' '|| CRLF;
1629
1630 l_group_by_clause :=
1631 ' GROUP BY ctlgd.customer_trx_id,
1632 ct.trx_number,
1633 app.org_id,
1634 decode(NVL(app.confirmed_flag,''Y''),
1635 ''Y'',''C'',
1636 ''N''),
1637 app.posting_control_id,
1638 decode(app.postable,
1639 ''N'',''N'',
1640 ''Y''),
1641 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1642 nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1643 decode(app.posting_control_id,
1644 ctlgd.posting_control_id, ''CM_CREATE'',
1645 ''CM_UPDATE''),
1646 ''CM_UPDATE''),
1647 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1648 app.event_id,
1649 ct.trx_date,
1650 ct.legal_entity_id ' || CRLF;
1651
1652 IF p_xla_ev_rec.xla_doc_table = 'CMAPP' THEN
1653
1654 l_order_by_clause :=
1655 'ORDER BY TRX_ID,
1656 OVERRIDE_EVENT,
1657 GL_DATE,
1658 PSTID desc ';
1659
1660 END IF;
1661
1662 ------------------------------------------------------------------
1663 -- Build the clause for CM Applications denormalize events stmt
1664 ------------------------------------------------------------------
1665 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1666 -- bug 5965006
1667 IF p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND g_call_number = 2 THEN
1668 NULL;
1669 ELSE
1670 l_select_clause :=
1671 ' SELECT app.rowid,
1672 ev1.event_id ';
1673
1674 l_from_clause :=
1675 ' FROM xla_events' || ' ev1, ' || CRLF ||
1676 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1677 ' ar_receivable_applications' || l_all_clause || ' app ' || CRLF ;
1678
1679
1680 --Use xla_transaction_entity_n1
1681 l_where_clause :=
1682 ' WHERE app.application_type = ''CM''
1683 AND app.event_id IS NULL
1684 AND app.status IN (''APP'',''ACTIVITY'') --HYU
1685 AND ev1.entity_id = evn.entity_id
1686 AND evn.application_id = 222
1687 AND ev1.application_id = 222
1688 AND app.customer_trx_id = NVL(evn.source_id_int_1,-99)
1689 AND evn.ledger_id = app.set_of_books_id
1690 AND evn.entity_code = ''TRANSACTIONS''
1691 AND app.posting_control_id = nvl(ev1.reference_num_1,-3)
1692 AND nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
1693 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1694 AND (((app.posting_control_id = -3)
1695 AND (ev1.event_status_code <> ''P''))
1696 OR ((app.posting_control_id <> -3)
1697 AND (ev1.event_status_code = ''P'' ))) ' || CRLF;
1698
1699
1700 l_group_by_clause := '';
1701
1702 l_order_by_clause := '';
1703 END IF;
1704 END IF; --create or update mode
1705
1706 END IF; --create and denormalize events Misc Cash Receipts
1707
1708 -------------------------------------------------------------------
1709 -- Build statement for Bills Receivable event creation
1710 -------------------------------------------------------------------
1711 IF p_xla_ev_rec.xla_doc_table = 'TRH' THEN
1712
1713 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
1714 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
1715
1716 /*-----------------------------------------------------+
1717 | The document id for BR is a transaction history id |
1718 | instead of customer_trx_id. The customer_trx_id is |
1719 | only used for storing the header info BR, all the |
1720 | accounting is driven by TRH. So the ARP_ACCT_MAIN |
1721 | package which call ARP_XLA_EVENTS to create events |
1722 | along with the accounting entries required a TRH_ID |
1723 | instead of a customer_trx_id. |
1724 | This change will not impact the upgrade mode neither|
1725 | the batch mode for they do not use document id |
1726 | replace trh.customer_trx_id by |
1727 | trh.transaction_history_id. |
1728 +-----------------------------------------------------*/
1729
1730 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
1731 l_where_parm_clause :=
1732 ' AND trh.transaction_history_id = :b_xla_from_doc_id
1733 AND trh.transaction_history_id = :b_xla_to_doc_id ' || CRLF;
1734 ELSE
1735 l_where_parm_clause :=
1736 ' AND trh.transaction_history_id >= :b_xla_from_doc_id
1737 AND trh.transaction_history_id <= :b_xla_to_doc_id ' || CRLF;
1738 END IF;
1739 END IF;
1740
1741
1742 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
1743 l_where_parm_clause := l_where_parm_clause
1744 || ' AND trh.request_id = :b_xla_req_id ' || CRLF;
1745 END IF;
1746
1747 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
1748 l_where_parm_clause := l_where_parm_clause
1749 || ' AND trh.transaction_history_id = :b_xla_dist_id ' || CRLF;
1750 END IF;
1751
1752 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1753
1754 log('Start Building Bills Receivable statement ' );
1755
1756 l_select_clause :=
1757 ' select
1758 tty.post_to_gl POSTTOGL ,
1759 ''BILL'' TRX_TYPE ,
1760 decode(trh.status,
1761 ''INCOMPLETE'', ''I'',
1762 ''PENDING_ACCEPTANCE'',''I'',
1763 ''C'') COMP_FLAG ,
1764 trh.customer_trx_id TRX_ID ,
1765 ct.trx_number TRX_NUMBER ,
1766 ct.org_id ORG_ID,
1767 decode(trh.event,
1768 ''INCOMPLETE'' , ''BILL_CREATE'',
1769 ''ACCEPTED'' , ''BILL_CREATE'',
1770 ''COMPLETED'' , decode(trh.status,
1771 ''PENDING_ACCEPTANCE'', ''BILL_CREATE'',
1772 ''PENDING_REMITTANCE'', ''BILL_CREATE'',
1773 ''NO_EVENT''),
1774 ''CANCELLED'' , ''BILL_REVERSE'',
1775 decode(trh1.first_posted_record_flag,
1776 '''', ''BILL_CREATE'',
1777 decode(nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1778 nvl(trh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1779 decode(trh.posting_control_id,
1780 trh1.posting_control_id, ''BILL_CREATE'',
1781 ''BILL_UPDATE''),
1782 ''BILL_UPDATE''))) OVERRIDE_EVENT,
1783 trh.posting_control_id PSTID ,
1784 decode(tty.post_to_gl,
1785 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1786 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))) GL_DATE,
1787 trh.event_id EXIST_EVENT ,
1788 '''' EVENT_ID ,
1789 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1790 '''' EVENT_TYPE ,
1791 ''X'' EVENT_STATUS ,
1792 ct.trx_date TRANSACTION_DATE,
1793 ct.legal_entity_id LEGAL_ENTITY_ID ';
1794
1795 l_from_clause :=
1796 ' FROM ar_transaction_history' || l_all_clause || ' trh, ' || CRLF ||
1797 ' ar_transaction_history' || l_all_clause || ' trh1, ' || CRLF ||
1798 ' ra_customer_trx' || l_all_clause || ' ct, ' || CRLF ||
1799 ' ra_cust_trx_types' || l_all_clause || ' tty ' || CRLF;
1800
1801 l_where_clause := ' where ct.customer_trx_id = trh.customer_trx_id ' || CRLF ||
1802 ' and ct.cust_trx_type_id = tty.cust_trx_type_id ' || CRLF ||
1803 ' and ct.org_id = tty.org_id ' || CRLF ||
1804 ' and ct.customer_trx_id = trh1.customer_trx_id (+) ' || CRLF ||
1805 ' and ''Y'' = trh1.first_posted_record_flag (+) ' || CRLF ||
1806 ' and ' || CRLF ||
1807 ' decode(trh.event, ' || CRLF ||
1808 ' ''INCOMPLETE'', ' || CRLF ||
1809 ' decode(trh1.first_posted_record_flag,'''',''Y'', ' || CRLF ||
1810 ' ''Y'', ''Y'', ''N''), ' || CRLF ||
1811 ' ''COMPLETED'', ' || CRLF ||
1812 ' decode(trh.status, ' || CRLF ||
1813 ' ''PENDING_ACCEPTANCE'', ' || CRLF ||
1814 ' decode(trh1.first_posted_record_flag, ' || CRLF ||
1815 ' '''', ''Y'', ''Y'', ''Y'', ' || CRLF ||
1816 ' ''N''), ' || CRLF ||
1817 ' trh.postable_flag), ' || CRLF ||
1818 ' trh.postable_flag) = ''Y'' ' || CRLF ||
1819 ' AND decode(trh.event_id, ' || CRLF ||
1820 ' '''', ''Y'', ' || CRLF ||
1821 ' decode(:b_xla_mode, ''O'',''Y'', ' || CRLF ||
1822 ' ''N'')) = ''Y'' ' || CRLF ;
1823
1824 ---
1825 ---Note the above decode contains events whose values are equated to postable flag
1826 ---These will always be non accounting events and have been coded to ensure completeness
1827 ---and catch potential data issues if any.
1828 ---
1829 l_group_by_clause :=
1830 ' GROUP BY trh.customer_trx_id,
1831 ct.trx_number,
1832 ct.org_id,
1833 decode(trh.status,
1834 ''INCOMPLETE'', ''I'',
1835 ''PENDING_ACCEPTANCE'',''I'',
1836 ''C''),
1837 tty.post_to_gl,
1838 trh.posting_control_id,
1839 decode(trh.event,
1840 ''INCOMPLETE'' , ''BILL_CREATE'',
1841 ''ACCEPTED'' , ''BILL_CREATE'',
1842 ''COMPLETED'' , decode(trh.status,
1843 ''PENDING_ACCEPTANCE'', ''BILL_CREATE'',
1844 ''PENDING_REMITTANCE'', ''BILL_CREATE'',
1845 ''NO_EVENT''),
1846 ''CANCELLED'' , ''BILL_REVERSE'',
1847 decode(trh1.first_posted_record_flag,
1848 '''', ''BILL_CREATE'',
1849 decode(nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1850 nvl(trh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1851 decode(trh.posting_control_id,
1852 trh1.posting_control_id, ''BILL_CREATE'',
1853 ''BILL_UPDATE''),
1854 ''BILL_UPDATE''))),
1855 decode(tty.post_to_gl,
1856 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1857 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
1858 trh.event_id ,
1859 ct.trx_date,
1860 ct.legal_entity_id ' || CRLF;
1861
1862
1863 l_order_by_clause :=
1864 'ORDER BY TRX_ID ,
1865 GL_DATE ,
1866 PSTID desc ';
1867
1868 log('End Building Bills Receivable statement ' );
1869 ---------------------------------------------------------------------
1870 -- Build the clause for Transaction History denormalize events stmt
1871 ---------------------------------------------------------------------
1872 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1873
1874 log('Building Denormalize Bills Receivable statement ' );
1875
1876 l_select_clause :=
1877 ' SELECT trh.rowid,
1878 ev1.event_id ';
1879
1880 --use xla_transaction_entity_n1
1881
1882 l_from_clause :=
1883 ' FROM xla_events' || ' ev1, ' || CRLF ||
1884 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1885 ' ar_transaction_history' || l_all_clause || ' trh, ' || CRLF ||
1886 ' ra_customer_trx' || l_all_clause || ' ct, ' || CRLF ||
1887 ' ra_cust_trx_types' || l_all_clause || ' tty ' || CRLF;
1888
1889 l_where_clause :=
1890 ' WHERE ct.customer_trx_id = trh.customer_trx_id ' || CRLF ||
1891 ' AND trh.event_id IS NULL ' || CRLF ||
1892 ' and ct.cust_trx_type_id = tty.cust_trx_type_id ' || CRLF ||
1893 ' and ct.org_id = tty.org_id ' || CRLF ||
1894 ' and decode(trh.event,
1895 ''CANCELLED'' , ''BILL_REVERSE'',
1896 ev1.event_type_code) = ev1.event_type_code
1897 AND ev1.entity_id = evn.entity_id
1898 AND ev1.application_id = 222
1899 AND evn.entity_code = ''BILLS_RECEIVABLE''
1900 AND evn.application_id = 222
1901 AND trh.posting_control_id = nvl(ev1.reference_num_1,-3)
1902 AND trh.customer_trx_id = NVL(evn.source_id_int_1,-99)
1903 AND evn.ledger_id = ct.set_of_books_id
1904 AND decode(tty.post_to_gl,
1905 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1906 decode(trh.event,
1907 ''INCOMPLETE'', nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY'')),
1908 ''COMPLETED'',
1909 decode(trh.status,
1910 ''PENDING_ACCEPTANCE'',
1911 nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY'')),
1912 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
1913 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')))) =
1914 nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1915 AND decode(trh.event,
1916 ''INCOMPLETE'', ''Y'',
1917 ''COMPLETED'', decode(trh.status,
1918 ''PENDING_ACCEPTANCE'',''Y'',
1919 trh.postable_flag),
1920 trh.postable_flag) = ''Y''
1921 AND (((trh.posting_control_id = -3)
1922 AND (ev1.event_status_code <> ''P''))
1923 OR ((trh.posting_control_id <> -3)
1924 AND (ev1.event_status_code = ''P'' ))) ' || CRLF;
1925
1926
1927 l_group_by_clause := '';
1928
1929 l_order_by_clause := '';
1930
1931 log('End Building Denormalize Bills Receivable statement ' );
1932 END IF; --create or update mode
1933
1934 END IF; --transaction
1935
1936 p_stmt := l_select_clause ||
1937 l_from_clause ||
1938 l_where_clause ||
1939 l_where_parm_clause ||
1940 l_group_by_clause ||
1941 l_order_by_clause ;
1942
1943 log('p_stmt ' || SUBSTRB(p_stmt,1,3980));
1944
1945 log('ARP_XLA_EVENTS.Build_Stmt ()-');
1946 EXCEPTION
1947 WHEN OTHERS THEN
1948 log('EXCEPTION: ARP_XLA_EVENTS.Build_Stmt');
1949 RAISE;
1950
1951 END Build_Stmt;
1952
1953 /*========================================================================
1954 | PUBLIC PROCEDURE Define Array
1955 |
1956 | DESCRIPTION
1957 | Define positional place holders in the select list
1958 |
1959 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1960 | Get_Select_Cursor
1961 |
1962 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1963 |
1964 | PARAMETERS p_select_c IN Cursor handle
1965 | p_xla_ev_rec IN Events parameter record
1966 | p_ev_rec IN Events record
1967 |
1968 | KNOWN ISSUES
1969 |
1970 | NOTES
1971 |
1972 | MODIFICATION HISTORY
1973 | Date Author Description of Changes
1974 *=======================================================================*/
1975 PROCEDURE define_arrays( p_select_c IN INTEGER,
1976 p_xla_ev_rec IN xla_events_type,
1977 p_ev_rec IN ev_rec_type,
1978 p_call_point IN NUMBER) IS
1979 BEGIN
1980 log( 'ARP_XLA_EVENTS.define_arrays()+' );
1981
1982 IF p_xla_ev_rec.xla_call IN ('C','B') AND p_call_point = 1 THEN
1983
1984 dbms_sql.define_array(p_select_c, 1 , p_ev_rec.posttogl
1985 , MAX_ARRAY_SIZE, STARTING_INDEX );
1986 dbms_sql.define_array(p_select_c, 2 , p_ev_rec.trx_type
1987 , MAX_ARRAY_SIZE, STARTING_INDEX );
1988 dbms_sql.define_array(p_select_c, 3 , p_ev_rec.trx_status
1989 , MAX_ARRAY_SIZE, STARTING_INDEX );
1990 dbms_sql.define_array(p_select_c, 4 , p_ev_rec.trx_id
1991 , MAX_ARRAY_SIZE, STARTING_INDEX );
1992 dbms_sql.define_array(p_select_c, 5 , p_ev_rec.trx_number
1993 , MAX_ARRAY_SIZE, STARTING_INDEX );
1994 dbms_sql.define_array(p_select_c, 6 , p_ev_rec.org_id
1995 , MAX_ARRAY_SIZE, STARTING_INDEX );
1996 dbms_sql.define_array(p_select_c, 7 , p_ev_rec.override_event
1997 , MAX_ARRAY_SIZE, STARTING_INDEX );
1998 dbms_sql.define_array(p_select_c, 8 , p_ev_rec.pstid
1999 , MAX_ARRAY_SIZE, STARTING_INDEX );
2000 dbms_sql.define_array(p_select_c, 9 , p_ev_rec.dist_gl_date
2001 , MAX_ARRAY_SIZE, STARTING_INDEX );
2002 dbms_sql.define_array(p_select_c, 10, p_ev_rec.ev_match_event_id
2003 , MAX_ARRAY_SIZE, STARTING_INDEX );
2004 dbms_sql.define_array(p_select_c, 11 , p_ev_rec.dist_event_id
2005 , MAX_ARRAY_SIZE, STARTING_INDEX );
2006 dbms_sql.define_array(p_select_c, 12, p_ev_rec.ev_match_gl_date
2007 , MAX_ARRAY_SIZE, STARTING_INDEX );
2008 dbms_sql.define_array(p_select_c, 13, p_ev_rec.ev_match_type
2009 , MAX_ARRAY_SIZE, STARTING_INDEX );
2010 dbms_sql.define_array(p_select_c, 14, p_ev_rec.ev_match_status
2011 , MAX_ARRAY_SIZE, STARTING_INDEX );
2012 --{HYU transaction_date,legal_entity_id
2013 dbms_sql.define_array(p_select_c, 15, p_ev_rec.transaction_date
2014 , MAX_ARRAY_SIZE, STARTING_INDEX );
2015 dbms_sql.define_array(p_select_c, 16, p_ev_rec.legal_entity_id
2016 , MAX_ARRAY_SIZE, STARTING_INDEX );
2017 --}
2018
2019 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
2020 dbms_sql.define_array(p_select_c, 1 , p_ev_rec.dist_row_id
2021 , MAX_ARRAY_SIZE, STARTING_INDEX );
2022 dbms_sql.define_array(p_select_c, 2 , p_ev_rec.dist_event_id
2023 , MAX_ARRAY_SIZE, STARTING_INDEX );
2024 END IF;
2025
2026 log( 'ARP_XLA_EVENTS.define_arrays()-' );
2027
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 log('EXCEPTION: ARP_XLA_EVENTS.define_arrays');
2031 RAISE;
2032
2033 END define_arrays;
2034
2035 /*========================================================================
2036 | PUBLIC PROCEDURE Get_Column_Values
2037 |
2038 | DESCRIPTION
2039 | Gets the values in select list and stores them in the target
2040 | event record table variable
2041 |
2042 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2043 | Get_Column_Values
2044 | Upd_Dist
2045 |
2046 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2047 |
2048 | PARAMETERS p_select_c IN Cursor handle
2049 | p_xla_ev_rec IN Events parameter record
2050 | p_ev_rec OUT Events record
2051 |
2052 | KNOWN ISSUES
2053 |
2054 | NOTES
2055 |
2056 | MODIFICATION HISTORY
2057 | Date Author Description of Changes
2058 *=======================================================================*/
2059 PROCEDURE get_column_values(p_select_c IN INTEGER,
2060 p_xla_ev_rec IN xla_events_type,
2061 p_call_point IN NUMBER,
2062 p_ev_rec OUT NOCOPY ev_rec_type) IS
2063 BEGIN
2064 log('ARP_XLA_EVENTS.get_column_values (+)');
2065
2066 IF p_xla_ev_rec.xla_call IN ('C','B') AND p_call_point = 1 THEN
2067 dbms_sql.column_value(p_select_c, 1 , p_ev_rec.posttogl);
2068 dbms_sql.column_value(p_select_c, 2 , p_ev_rec.trx_type);
2069 dbms_sql.column_value(p_select_c, 3 , p_ev_rec.trx_status);
2070 dbms_sql.column_value(p_select_c, 4 , p_ev_rec.trx_id);
2071 dbms_sql.column_value(p_select_c, 5 , p_ev_rec.trx_number);
2072 dbms_sql.column_value(p_select_c, 6 , p_ev_rec.org_id);
2073 dbms_sql.column_value(p_select_c, 7 , p_ev_rec.override_event);
2074 dbms_sql.column_value(p_select_c, 8 , p_ev_rec.pstid);
2075 dbms_sql.column_value(p_select_c, 9 , p_ev_rec.dist_gl_date);
2076 dbms_sql.column_value(p_select_c, 10 , p_ev_rec.dist_event_id);
2077 dbms_sql.column_value(p_select_c, 11, p_ev_rec.ev_match_event_id);
2078 dbms_sql.column_value(p_select_c, 12, p_ev_rec.ev_match_gl_date);
2079 dbms_sql.column_value(p_select_c, 13, p_ev_rec.ev_match_type);
2080 dbms_sql.column_value(p_select_c, 14, p_ev_rec.ev_match_status);
2081 --{HYU transaction_date,legal_entity_id
2082 dbms_sql.column_value(p_select_c, 15, p_ev_rec.transaction_date);
2083 dbms_sql.column_value(p_select_c, 16, p_ev_rec.legal_entity_id);
2084 --}
2085 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
2086 dbms_sql.column_value(p_select_c, 1 , p_ev_rec.dist_row_id);
2087 dbms_sql.column_value(p_select_c, 2 , p_ev_rec.dist_event_id);
2088 END IF;
2089
2090 log('ARP_XLA_EVENTS.get_column_values (-)');
2091
2092 END get_column_values;
2093
2094 /*========================================================================
2095 | PUBLIC PROCEDURE Get_Select_Cursor
2096 |
2097 | DESCRIPTION
2098 | Builds Select statement, opens cursor, parses it, defines place
2099 | holders for select list, binds variables and returns a cursor
2100 | handle.
2101 |
2102 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2103 | Upd_Dist
2104 |
2105 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2106 |
2107 | PARAMETERS p_xla_ev_rec IN Events parameter record
2108 |
2109 | KNOWN ISSUES
2110 |
2111 | NOTES
2112 |
2113 | MODIFICATION HISTORY
2114 | Date Author Description of Changes
2115 *=======================================================================*/
2116 FUNCTION Get_Select_Cursor(p_xla_ev_rec IN xla_events_type,
2117 p_call_point IN NUMBER) RETURN INTEGER IS
2118
2119 l_select_c INTEGER;
2120 l_stmt VARCHAR2(22000);
2121 l_ev_rec ev_rec_type;
2122
2123 BEGIN
2124
2125 log('ARP_XLA_EVENTS.Get_Select_Cursor ()+');
2126
2127 Build_Stmt(p_xla_ev_rec => p_xla_ev_rec,
2128 p_stmt => l_stmt,
2129 p_call_point => p_call_point);
2130
2131 log('Opening cursor, to give cursor handle');
2132
2133 l_select_c := dbms_sql.open_cursor;
2134
2135 log('Parsing select stmt');
2136
2137 dbms_sql.parse(l_select_c, l_stmt, dbms_sql.v7);
2138
2139 ------------------------------------------------------------
2140 -- Define Column Arrays
2141 ------------------------------------------------------------
2142
2143 define_arrays(p_select_c => l_select_c,
2144 p_xla_ev_rec => p_xla_ev_rec,
2145 p_ev_rec => l_ev_rec,
2146 p_call_point => p_call_point);
2147
2148 ------------------------------------------------------------
2149 -- Bind Variables
2150 ------------------------------------------------------------
2151 IF p_call_point = 1 THEN
2152 log('p_xla_ev_rec.xla_mode ' || p_xla_ev_rec.xla_mode);
2153 dbms_sql.bind_variable(l_select_c, ':b_xla_mode', p_xla_ev_rec.xla_mode);
2154 END IF;
2155
2156 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL THEN
2157 log('p_xla_ev_rec.xla_from_doc_id ' || p_xla_ev_rec.xla_from_doc_id);
2158 dbms_sql.bind_variable(l_select_c, ':b_xla_from_doc_id', p_xla_ev_rec.xla_from_doc_id);
2159 END IF;
2160
2161 IF p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
2162 log('p_xla_ev_rec.xla_to_doc_id ' || p_xla_ev_rec.xla_to_doc_id);
2163 dbms_sql.bind_variable(l_select_c, ':b_xla_to_doc_id', p_xla_ev_rec.xla_to_doc_id);
2164 END IF;
2165
2166 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
2167 log('p_xla_ev_rec.xla_req_id ' || p_xla_ev_rec.xla_req_id);
2168 dbms_sql.bind_variable(l_select_c, ':b_xla_req_id', p_xla_ev_rec.xla_req_id);
2169 END IF;
2170
2171 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
2172 log('p_xla_ev_rec.xla_dist_id ' || p_xla_ev_rec.xla_dist_id);
2173 dbms_sql.bind_variable(l_select_c, ':b_xla_dist_id', p_xla_ev_rec.xla_dist_id);
2174 END IF;
2175
2176 log('ARP_XLA_EVENTS.Get_Select_Cursor (-)');
2177
2178 return(l_select_c);
2179
2180 EXCEPTION
2181 WHEN OTHERS THEN
2182 log('EXCEPTION: ARP_XLA_EVENTS.Get_Select_Cursor:'||SQLERRM);
2183 RAISE;
2184
2185 END Get_Select_Cursor;
2186
2187 /*========================================================================
2188 | PUBLIC PROCEDURE Create_All_Events
2189 |
2190 | DESCRIPTION
2191 | Creates, updates and deletes events for the transactions
2192 | INV, DM, CM, CB, GUAR, DEP
2193 |
2194 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2195 | Execute
2196 |
2197 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2198 |
2199 | PARAMETERS
2200 | p_xla_ev_rec IN xla_events_type
2201 |
2202 | KNOWN ISSUES
2203 |
2204 | NOTES
2205 |
2206 | MODIFICATION HISTORY
2207 | Date Author Description of Changes
2208 | 10-SEP-2003 Herve Yu
2209 | Use the set_of_books_id for now as the ledger_id bug#3135769
2210 | we might need to come back on this point later after the uptake
2211 | of ledger architecture project.
2212 *=======================================================================*/
2213 PROCEDURE Create_All_Events(p_xla_ev_rec IN xla_events_type) IS
2214
2215 /*---------------------------------------------------------------------+
2216 | Main cursor which gets transaction data, and event data for decision|
2217 | making on which events require to be created, updated or deleted. |
2218 +---------------------------------------------------------------------*/
2219 TYPE get_tran_data_type IS REF CURSOR;
2220
2221 get_tran_data get_tran_data_type;
2222
2223 l_select_c BINARY_INTEGER;
2224
2225 /*-----------------------------------------------------------------------+
2226 | Local Variable Declarations and initializations |
2227 +-----------------------------------------------------------------------*/
2228 ev_rec ev_rec_type ;
2229 empty_ev_rec ev_rec_type ;
2230
2231 bld_ev_rec bld_ev_type ;
2232 empty_bld_ev_rec bld_ev_type ;
2233
2234 --cache_ev_rec bld_ev_type ;
2235
2236 bld_ev_ent xla_events_pub_pkg.t_array_entity_event_info_s;
2237 empty_bld_ev_ent xla_events_pub_pkg.t_array_entity_event_info_s;
2238
2239 prev_distid VARCHAR2(50) := '';
2240
2241 l_cached BOOLEAN;
2242 l_cached2 BOOLEAN;
2243 ev_match_flg BOOLEAN;
2244 l_last_fetch BOOLEAN;
2245 l_match_event_in_cache BOOLEAN;
2246
2247 event_trx_gl_date_mismatch EXCEPTION;
2248 abnormal_cond EXCEPTION;
2249
2250 i BINARY_INTEGER := 0;
2251 j BINARY_INTEGER := 0;
2252 k BINARY_INTEGER := 0;
2253 l BINARY_INTEGER := 0;
2254 m BINARY_INTEGER := 0;
2255 n BINARY_INTEGER := 0;
2256 p BINARY_INTEGER := 0;
2257 q BINARY_INTEGER := 0;
2258 r BINARY_INTEGER := 0;
2259
2260 l_change_matrix VARCHAR2(30);
2261
2262 temp_event_id BINARY_INTEGER := 0;
2263
2264 prev_trx_id ra_customer_trx.customer_trx_id%TYPE;
2265
2266 l_type VARCHAR2(30);
2267 l_build_ctr BINARY_INTEGER;
2268 l_ev_type VARCHAR2(20);
2269 l_ignore INTEGER;
2270 l_rows_fetched VARCHAR2(10);
2271 l_low INTEGER:=0;
2272 l_high INTEGER:=0;
2273 lnb NUMBER;
2274 --xla event api declarations
2275 l_event_source_info xla_events_pub_pkg.t_event_source_info;
2276 l_reference_info xla_events_pub_pkg.t_event_reference_info;
2277 l_security_context xla_events_pub_pkg.t_security;
2278 l_entity_event_info_tbl xla_events_pub_pkg.t_array_entity_event_info_s;
2279 keep_flag BOOLEAN;
2280 l_revrun VARCHAR2(5);
2281 l_entity_code VARCHAR2(20);
2282 z NUMBER :=0;
2283 cnt INTEGER;
2284 trxn_number VARCHAR2(240);
2285 CURSOR for_batch IS
2286 SELECT 'X'
2287 FROM xla_events_int_gt
2288 WHERE entity_id IS NULL;
2289 l_test VARCHAR2(1);
2290
2291
2292 BEGIN
2293 log('ARP_XLA_EVENTS.Create_All_Events ()+');
2294
2295 --Bug#3135769
2296 --{
2297 IF arp_standard.sysparm.set_of_books_id IS NULL THEN
2298 arp_standard.init_standard;
2299 END IF;
2300 --}
2301
2302 IF p_xla_ev_rec.xla_call IN ('C','B') THEN
2303 null;
2304 ELSE
2305 goto endlabel;
2306 END IF;
2307
2308 -----------------------------------------------------------
2309 --Get the transaction entity
2310 -----------------------------------------------------------
2311 l_entity_code := entity_code( p_xla_ev_rec.xla_doc_table);
2312
2313 ------------------------------------------------------------
2314 -- Build select statement and get cursor handle
2315 ------------------------------------------------------------
2316 l_select_c := Get_Select_Cursor(p_xla_ev_rec => p_xla_ev_rec,
2317 p_call_point => 1);
2318
2319 l_ignore := dbms_sql.execute( l_select_c );
2320
2321 log( 'Fetching select stmt');
2322
2323 LOOP -- Main Cursor Loop
2324
2325 log( 'reinitialize the build events cache');
2326 bld_ev_rec.bld_dml_flag := empty_bld_ev_rec.bld_dml_flag;
2327 bld_ev_rec.bld_temp_event_id := empty_bld_ev_rec.bld_temp_event_id;
2328
2329 --initialize the collection of records containing event information
2330 bld_ev_ent := empty_bld_ev_ent;
2331
2332 log( 'reinitialize the events cache');
2333
2334 ev_rec.posttogl := empty_ev_rec.posttogl;
2335 ev_rec.trx_type := empty_ev_rec.trx_type;
2336 ev_rec.trx_status := empty_ev_rec.trx_status;
2337 ev_rec.trx_id := empty_ev_rec.trx_id;
2338 ev_rec.pstid := empty_ev_rec.pstid;
2339 ev_rec.dist_gl_date := empty_ev_rec.dist_gl_date;
2340 ev_rec.override_event := empty_ev_rec.override_event;
2341 ev_rec.dist_event_id := empty_ev_rec.dist_event_id;
2342 ev_rec.ev_match_event_id := empty_ev_rec.ev_match_event_id;
2343 ev_rec.ev_match_gl_date := empty_ev_rec.ev_match_gl_date;
2344 ev_rec.ev_match_type := empty_ev_rec.ev_match_type;
2345 ev_rec.ev_match_status := empty_ev_rec.ev_match_status;
2346 --{HYU transaction_date,legal_entity_id
2347 ev_rec.transaction_date := empty_ev_rec.transaction_date;
2348 ev_rec.legal_entity_id := empty_ev_rec.legal_entity_id;
2349 --}
2350 j := 0;
2351
2352 l_rows_fetched := dbms_sql.fetch_rows(l_select_c);
2353
2354 log('Rows Fetched are ' || l_rows_fetched);
2355
2356 l_low := l_high + 1;
2357 l_high:= l_high + l_rows_fetched;
2358
2359 IF l_rows_fetched > 0 THEN
2360
2361 log('Fetched a row ');
2362 log('l_low ' || l_low);
2363 log('l_high ' || l_high);
2364
2365 get_column_values(p_select_c => l_select_c,
2366 p_xla_ev_rec => p_xla_ev_rec,
2367 p_ev_rec => ev_rec,
2368 p_call_point => 1);
2369
2370 -- no more rows to fetch
2371 IF l_rows_fetched < MAX_ARRAY_SIZE THEN
2372 log('Done fetching 1');
2373
2374 IF( dbms_sql.is_open( l_select_c) ) THEN
2375 dbms_sql.close_cursor( l_select_c );
2376 END IF;
2377
2378 END IF; --no more rows to fetch
2379
2380 ELSE --if rows fetched = 0
2381 log('Done fetching 2');
2382
2383 IF( dbms_sql.is_open( l_select_c ) ) THEN
2384 dbms_sql.close_cursor( l_select_c );
2385 END IF;
2386
2387 EXIT;
2388
2389 END IF; --rows fetched greater than 0
2390
2391 /*--------------------------------------------------------+
2392 |Set the event id to the existing event id which is not |
2393 |accounted, since for the current distribution gl date |
2394 |another event could not be found, however there exists |
2395 |another event which is not accounted which has a |
2396 |different gl date, set values for decision making. |
2397 +--------------------------------------------------------*/
2398 log('Bef Loop ');
2399 log('Number of rows selected : '|| ev_rec.trx_id.COUNT);
2400 FOR i IN ev_rec.trx_id.FIRST .. ev_rec.trx_id.LAST LOOP
2401
2402 log('Processing Transactions Events using Change Matrix');
2403
2404 --Initialize variables when new transaction is being processed
2405 IF NVL(prev_trx_id,-999) <> ev_rec.trx_id(i) THEN
2406
2407 log('Initialization Tasks ');
2408
2409 END IF; --reinitialize trx cache
2410
2411 /*----------------------------------------------------------+
2412 |Processing required in OLTP mode only |
2413 +----------------------------------------------------------*/
2414 IF p_xla_ev_rec.xla_mode = 'O' THEN
2415 -- In batch mode, ar should also verify the existance of events
2416 -- IF p_xla_ev_rec.xla_mode IN ('O','B') THEN
2417
2418 /*-------------------------------------------------------------------------+
2419 |Get the data associated with the existing event to enable decision making|
2420 |Note : There must be an existing event - otherwise a no data found excep |
2421 | will be raised |
2422 +-------------------------------------------------------------------------*/
2423 IF ev_rec.dist_event_id(i) IS NOT NULL THEN
2424
2425 /*
2426 get_existing_event
2427 (p_event_id => ev_rec.dist_event_id(i),
2428 x_event_id => ev_rec.ev_match_event_id(i),
2429 x_event_date => ev_rec.ev_match_gl_date(i),
2430 x_event_status_code => ev_rec.ev_match_status(i),
2431 x_event_type_code => ev_rec.ev_match_type(i));
2432 */
2433
2434 select ae.event_id ,
2435 ae.event_date ,
2436 ae.event_status_code,
2437 ae.event_type_code
2438 into ev_rec.ev_match_event_id(i) ,
2439 ev_rec.ev_match_gl_date(i) ,
2440 ev_rec.ev_match_status(i) ,
2441 ev_rec.ev_match_type(i)
2442 from xla_events ae
2443 where ev_rec.dist_event_id(i) = ae.event_id
2444 and ae.application_id = 222;
2445
2446
2447 /*-------------------------------------------------------------------------+
2448 |Find an existing event to which the current distribution can latch on to |
2449 |and use in decision making - best match. As of a given gl date, there |
2450 |can exists only one unposted event type matching the dist event type for |
2451 |the dist gl date for a document id (even if document ids overlapp, since |
2452 |event types are unique for existing events and cannot overlapp across |
2453 |different document entities. |
2454 +-------------------------------------------------------------------------*/
2455 ELSIF ev_rec.dist_event_id(i) IS NULL THEN
2456 --
2457 -- BEGIN
2458 --log('ev_rec.trx_id(i):'||ev_rec.trx_id(i));
2459 --log('ev_rec.dist_gl_date(i):'||ev_rec.dist_gl_date(i));
2460 --log('ev_rec.override_event(i):'||ev_rec.override_event(i));
2461 /*
2462 get_best_existing_event
2463 (p_trx_id => ev_rec.trx_id(i),
2464 p_gl_date => ev_rec.dist_gl_date(i),
2465 p_override_event => ev_rec.override_event(i),
2466 x_match_event_id => ev_rec.ev_match_event_id(i),
2467 x_match_gl_date => ev_rec.ev_match_gl_date(i),
2468 x_match_status => ev_rec.ev_match_status(i),
2469 x_match_type => ev_rec.ev_match_type(i));
2470 */
2471
2472 --{BUG#5347627
2473 BEGIN
2474 IF ev_rec.override_event(i) IN
2475 ('INV_CREATE','DM_CREATE','DEP_CREATE','CB_CREATE','CM_CREATE','GUAR_CREATE',
2476 'INV_UPDATE','DM_UPDATE','DEP_UPDATE','CB_UPDATE','CM_UPDATE','GUAR_UPDATE')
2477 THEN
2478 select ae2.event_id ,
2479 ae2.event_date ,
2480 ae2.event_status_code,
2481 ae2.event_type_code
2482 into ev_rec.ev_match_event_id(i) ,
2483 ev_rec.ev_match_gl_date(i) ,
2484 ev_rec.ev_match_status(i) ,
2485 ev_rec.ev_match_type(i)
2486 from xla_events ae2
2487 where ae2.application_id = 222
2488 and ae2.event_id IN
2489 ( select MAX( ae.event_id )
2490 from xla_events ae,
2491 xla_transaction_entities_upg xt,
2492 ra_customer_trx_all trx
2493 where trx.customer_trx_id = ev_rec.trx_id(i)
2494 and NVL(xt.source_id_int_1,-99) = trx.customer_trx_id
2495 and xt.entity_code = 'TRANSACTIONS'
2496 and xt.ledger_id = trx.set_of_books_id
2497 and xt.entity_id = ae.entity_id
2498 and xt.application_id = 222
2499 and ae.application_id = 222
2500 and nvl(ae.event_date,
2501 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2502 and ae.event_status_code <> 'P'
2503 and ae.event_type_code IN
2504 ('INV_CREATE','DM_CREATE','DEP_CREATE','CB_CREATE','CM_CREATE','GUAR_CREATE',
2505 'INV_UPDATE','DM_UPDATE','DEP_UPDATE','CB_UPDATE','CM_UPDATE','GUAR_UPDATE')
2506 )
2507 FOR UPDATE OF ae2.event_id NOWAIT;
2508
2509 ELSIF ev_rec.override_event(i) IN
2510 ('BILL_CREATE','BILL_UPDATE','BILL_REVERSE')
2511 THEN
2512 select ae2.event_id ,
2513 ae2.event_date ,
2514 ae2.event_status_code,
2515 ae2.event_type_code
2516 into ev_rec.ev_match_event_id(i) ,
2517 ev_rec.ev_match_gl_date(i) ,
2518 ev_rec.ev_match_status(i) ,
2519 ev_rec.ev_match_type(i)
2520 from xla_events ae2
2521 where ae2.application_id = 222
2522 and ae2.event_id IN
2523 ( select MAX( ae.event_id )
2524 from xla_events ae,
2525 xla_transaction_entities_upg xt,
2526 ra_customer_trx_all trx
2527 where trx.customer_trx_id = ev_rec.trx_id(i)
2528 and NVL(xt.source_id_int_1,-99) = trx.customer_trx_id
2529 and xt.entity_code = 'BILLS_RECEIVABLE'
2530 and xt.ledger_id = trx.set_of_books_id
2531 and xt.entity_id = ae.entity_id
2532 and xt.application_id = 222
2533 and ae.application_id = 222
2534 and nvl(ae.event_date,
2535 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2536 and ae.event_status_code <> 'P'
2537 and ev_rec.override_event(i) = ae.event_type_code )
2538 FOR UPDATE OF ae2.event_id NOWAIT;
2539
2540
2541 ELSIF ev_rec.override_event(i) IN
2542 ('RECP_CREATE','RECP_RATE_ADJUST','RECP_UPDATE','RECP_REVERSE',
2543 'MISC_RECP_CREATE','MISC_RECP_RATE_ADJUST','MISC_RECP_UPDATE','MISC_RECP_REVERSE')
2544 THEN
2545 select ae2.event_id ,
2546 ae2.event_date ,
2547 ae2.event_status_code,
2548 ae2.event_type_code
2549 into ev_rec.ev_match_event_id(i) ,
2550 ev_rec.ev_match_gl_date(i) ,
2551 ev_rec.ev_match_status(i) ,
2552 ev_rec.ev_match_type(i)
2553 from xla_events ae2
2554 where ae2.application_id = 222
2555 and ae2.event_id IN
2556 ( select MAX( ae.event_id )
2557 from xla_events ae,
2558 xla_transaction_entities_upg xt,
2559 ar_Cash_receipts_all cr
2560 where cr.cash_receipt_id = ev_rec.trx_id(i)
2561 and NVL(xt.source_id_int_1,-99) = cr.cash_receipt_id
2562 and xt.entity_code = 'RECEIPTS'
2563 and xt.ledger_id = cr.set_of_books_id
2564 and xt.entity_id = ae.entity_id
2565 and xt.application_id = 222
2566 and ae.application_id = 222
2567 and nvl(ae.event_date,
2568 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2569 and ae.event_status_code <> 'P'
2570 and DECODE(ev_rec.override_event(i),
2571 'RECP_CREATE', ae.event_type_code,
2572 'MISC_RECP_CREATE', ae.event_type_code,
2573 ev_rec.override_event(i) ) = ae.event_type_code )
2574 FOR UPDATE OF ae2.event_id NOWAIT;
2575
2576 ELSIF ev_rec.override_event(i) IN ('ADJ_CREATE') THEN
2577 select ae2.event_id ,
2578 ae2.event_date ,
2579 ae2.event_status_code,
2580 ae2.event_type_code
2581 into ev_rec.ev_match_event_id(i) ,
2582 ev_rec.ev_match_gl_date(i) ,
2583 ev_rec.ev_match_status(i) ,
2584 ev_rec.ev_match_type(i)
2585 from xla_events ae2
2586 where ae2.application_id = 222
2587 and ae2.event_id IN
2588 ( select MAX( ae.event_id )
2589 from xla_events ae,
2590 xla_transaction_entities_upg xt,
2591 ar_adjustments_all adj
2592 where adj.adjustment_id = ev_rec.trx_id(i)
2593 and NVL(xt.source_id_int_1,-99) = adj.adjustment_id
2594 and xt.entity_code = 'ADJUSTMENTS'
2595 and xt.ledger_id = adj.set_of_books_id
2596 and xt.entity_id = ae.entity_id
2597 and xt.application_id = 222
2598 and ae.application_id = 222
2599 and nvl(ae.event_date,
2600 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2601 and ae.event_status_code <> 'P'
2602 and ev_rec.override_event(i) = ae.event_type_code )
2603 FOR UPDATE OF ae2.event_id NOWAIT;
2604 END IF;
2605
2606 EXCEPTION
2607 WHEN NO_DATA_FOUND THEN
2608 --------------------------------------------------------------+
2609 --|Since a match could not be found - ascertain whether there |
2610 --|is an existing event (Typically for Modify document event type|
2611 --|Add condition on trx_type to avoid the undesirable latching of|
2612 --|a RECP event on a previous one. For example : |
2613 --| * Create of a Receipt, RECP_CREATE. |
2614 --| * Reverse of the Receipt, RECP_REVERSE but if we do not add |
2615 --| the condition to avoid the association between the reversal|
2616 --| and the creation of the receipt, the RECP_REVERSE event is |
2617 --| not created. |
2618 --+--------------------------------------------------------------
2619 -- Processing below is not required since a Create or Update event will get created
2620 -- if ther is no matching event - the SQL for each entity would have taken care of
2621 -- building the correct event type. We no longer need to verify for existing events
2622 -- since we look ate the first posted record flag.
2623 NULL;
2624 WHEN OTHERS THEN
2625 /*ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2626 The error indicates that there exist a matching event to latch on to,however
2627 it is currently locked by some other process.The action would be to let the
2628 process continue and create a new event of type UPDATE.
2629
2630 This code flow gets triggered only in cases where the concerned document already
2631 has an event of type CREATE,will explictly override the event_type_code with
2632 that of event type UPDATE to ensure that there exist only one event of type CREATE
2633 for given document*/
2634 IF SQLCODE = -54 THEN
2635 ev_rec.override_event(i) :=
2636 CASE ev_rec.override_event(i)
2637 WHEN 'INV_CREATE' THEN 'INV_UPDATE'
2638 WHEN 'DM_CREATE' THEN 'DM_UPDATE'
2639 WHEN 'DEP_CREATE' THEN 'DEP_UPDATE'
2640 WHEN 'CB_CREATE' THEN 'CB_UPDATE'
2641 WHEN 'CM_CREATE' THEN 'CM_UPDATE'
2642 WHEN 'GUAR_CREATE' THEN 'GUAR_UPDATE'
2643 WHEN 'RECP_CREATE' THEN 'RECP_UPDATE'
2644 WHEN 'MISC_RECP_CREATE' THEN 'MISC_RECP_UPDATE'
2645 ELSE ev_rec.override_event(i)
2646 END;
2647 IF PG_DEBUG = 'Y' THEN
2648 arp_standard.debug(' ORA-00054 occurred,ev_rec.override_event set to '||
2649 ev_rec.override_event(i) );
2650 END IF;
2651
2652 ELSE
2653 RAISE;
2654 END IF;
2655 END; --distribution is not marked with an event
2656
2657 END IF; --distribution is marked with an event if construct
2658
2659 END IF; --processing for event creation in Oltp mode
2660
2661 --Current distribution does not have an event
2662 IF ev_rec.dist_event_id(i) IS NULL THEN
2663 ev_match_flg := FALSE;
2664 log(' ev_match_flg ' || 'FALSE');
2665 ELSE
2666 log(' ev_match_flg ' || 'TRUE');
2667 ev_match_flg := TRUE;
2668 END IF;
2669
2670 dump_ev_rec(p_ev_rec => ev_rec, p_i => i);
2671
2672 /*------------------------------------------------------------------+
2673 |Since the main cursor gets one row for creation of each new event |
2674 |hence there is no need to have an events cache. The only time we |
2675 |need to figure out what the previous event insert did is to make |
2676 |a decision as regards the construction of the event type as Create|
2677 |or modify. |
2678 +------------------------------------------------------------------*/
2679
2680 l_change_matrix := Change_Matrix(
2681 ev_rec.trx_status(i) ,
2682 ev_rec.dist_gl_date(i) ,
2683 ev_rec.ev_match_gl_date(i) ,
2684 ev_rec.ev_match_status(i) ,
2685 ev_rec.posttogl(i));
2686
2687 log('l_change_matrix ' || l_change_matrix);
2688 l_type := 'NONE';
2689
2690 /*------------------------------------------------------------------+
2691 |This routine cannot create events for posted transactions as XLA |
2692 |apis do not allow it. Hence in oltp mode we set the event match |
2693 |flag to true. This results in the distribution which is posted |
2694 |to get skipped which is okay because : |
2695 |1) The downtime upgrade would have created the event if in range |
2696 |OR 2) The post upgrade would have done so. |
2697 |In either situation we dont really care as the distribution is |
2698 |posted. Bug 5600736 related change. |
2699 +------------------------------------------------------------------*/
2700 IF p_xla_ev_rec.xla_mode = 'O' AND ev_rec.pstid(i) <> -3
2701 AND l_change_matrix = '1.12' THEN
2702
2703 log(' Override ev_match_flg ' || 'TRUE');
2704 ev_match_flg := TRUE;
2705
2706 END IF;
2707
2708 /*-----------------------------------------------------------+
2709 |Latch on to the existing event where gldate matches. |
2710 |Event id will have a value only if an event exists in the |
2711 |database, otherwise it will be null and a negative event id|
2712 |seeded in the cache which will be used as a primary key for|
2713 |mapping the built event to the dist which maps to this |
2714 |event, latching is implicit to these values and is done |
2715 |After the Bulk Insert and Updates to Events table. |
2716 |Latching occurs in the end. |
2717 +-----------------------------------------------------------*/
2718 /* IF ((NOT ev_match_flg)
2719 AND (l_change_matrix IN (1.01,1.03,1.07,1.09,1.13,1.14,
2720 1.16,1.17))) THEN */
2721 /*------------------------------------------------+
2722 |Latching is implicit to ev_rec.ev_event_id for |
2723 |Update or ev_rec.ev_temp_event_id for Insert |
2724 |for all above change matrix values not in the |
2725 |IF construct below. For others explicit latching|
2726 +------------------------------------------------*/
2727 /* IF (l_change_matrix IN (1.01, 1.09, 1.13, 1.17)) THEN
2728 ev_rec.dist_event_id(i) := ev_rec.ev_match_event_id(i);
2729 END IF; --latch on to matching event
2730
2731 END IF; */ --Latch on to existing event check
2732
2733
2734 /*---------------------------------------------------------+
2735 | If Change_Matrix returns 1.02 1.04 1.08 1.10 |
2736 | and the mode of execution is OLTP |
2737 | Then |
2738 | Need to check if the REV_RECOGNITION program has run on |
2739 | that invoice. Because if not then distributions are only|
2740 | modal, therefore they are not stamped with the event_id.|
2741 | Nevertheless, no new event should be created, only the |
2742 | GLDate needs to be updated on that event. |
2743 | Typically when a invoice with rules arrear has some |
2744 | new lines entered then the transaction only has modal |
2745 | REC distributions but the GLDate will be set the last |
2746 | FORECASt REV_RECOGNITION date. |
2747 +---------------------------------------------------------*/
2748 l_revrun := 'X';
2749
2750 IF ev_rec.trx_type(i) = 'INV' AND p_xla_ev_rec.xla_mode = 'O' THEN
2751 log('arp_xla_events.is_one_acct_asg_on_ctlgd()+');
2752 log('The customer_trx_id :'||ev_rec.trx_id(i));
2753
2754 l_revrun := is_one_acct_asg_on_ctlgd(ev_rec.trx_id(i));
2755
2756 log('l_revrun : '||l_revrun);
2757 log('arp_xla_events.is_one_acct_asg_on_ctlgd()-');
2758 END IF;
2759
2760 /*---------------------------------------------------------+
2761 |Update existing Event |
2762 |Matrix - Dist - Description |
2763 | Event |
2764 |1.02 - T - Update event gl date |
2765 |1.03 - T, F - Update Status = Incomplete |
2766 |1.04 - T - Update gl date, Status = Incomplete |
2767 |1.07 - T,F - Update Status = Unprocessed |
2768 |1.08 - T - Update gl date, Status = Unprocessed |
2769 |1.10 - T - Update gl date |
2770 |1.14 - T,F - Status = Incomplete |
2771 |1.16 - T,F - Status = Unprocessed |
2772 |1.08 - F - Update gl date, Status = Unprocesse |
2773 | if Acct_asg created for one line |
2774 |1.02 - F - Update gl date if ACT_ASG created |
2775 |1.04 - F - Update gl date if ACT_ASG created |
2776 |1.10 - F - Update gl date if ACT_ASG created |
2777 |1.09 - T - For the case of cleaning events |
2778 | when update REV_REC event GL Dates. |
2779 |1.23 -T,F - Update of a postable trx to be |
2780 | unpostable Bug#3320427 |
2781 |1.22 -T,F - Update of a unpostable trx to be |
2782 | postable Bug#3320427 |
2783 +---------------------------------------------------------*/
2784 --BUG#3999572
2785 IF (l_change_matrix IN ('1.13', '1.14', '1.15', '1.16', '1.17', '1.18', '1.19', '1.20', '1.21', '1.23'))
2786 THEN NULL;
2787 --}
2788 ELSIF (((ev_match_flg)
2789 AND ((l_change_matrix IN ('1.02','1.03','1.04','1.07',
2790 '1.08','1.10'
2791 --,1.14,1.16,1.23
2792 ,'1.22'
2793 )) OR ( (p_xla_ev_rec.xla_doc_table <> 'CRH') AND (l_change_matrix IN ('1.09')) )))
2794 OR ((NOT ev_match_flg)
2795 AND (l_change_matrix IN ('1.03','1.07'
2796 --,1.14,1.16,1.23
2797 ,'1.22'
2798 )))
2799 OR ((NOT ev_match_flg)
2800 AND (l_change_matrix IN ('1.02','1.04','1.08','1.10'))
2801 AND (l_revrun = 'N'))
2802 AND p_xla_ev_rec.xla_mode = 'O')
2803 THEN
2804
2805 log('Entered Update Built event construct ');
2806
2807 IF l_change_matrix IN ('1.02','1.04','1.08','1.10','1.22') THEN
2808 ev_rec.ev_match_gl_date(i) := ev_rec.dist_gl_date(i);
2809 --introduce validation to make sure that another dist
2810 --with the same GL Date does not exist
2811 --{BUG#3320427
2812 ELSIF l_change_matrix IN ('1.23') THEN
2813 ev_rec.ev_match_gl_date(i) := TO_DATE('01-01-1900','DD-MM-YYYY');
2814 --}
2815 END IF;
2816 --set the gl date
2817
2818 IF l_change_matrix IN ('1.03', '1.04', '1.14','1.22') THEN
2819 ev_rec.ev_match_status(i) := 'I';
2820 ELSIF l_change_matrix IN ('1.07','1.08','1.16') THEN
2821 ev_rec.ev_match_status(i) := 'U';
2822 --{BUG#3320427
2823 --{BUG#3999572
2824 -- ELSIF l_change_matrix IN (1.23) THEN
2825 -- ev_rec.ev_match_status(i) := 'N';
2826 --}
2827 --}
2828 END IF; --set the status
2829
2830 /*--------------------------------------------------------------------+
2831 | Need to avoid the existance of multiple events of the same type |
2832 | and the same GLDate for the same document in XLA_EVENTS table. |
2833 | Typically this can happens when user update the GL_Date on a inv |
2834 | with rules on which REV_RECOGNITION has run. In this case the |
2835 | the xla_events table contains multiple REV_RECOGNITION events with |
2836 | different GLDate, when user updates the GL Date on the header |
2837 | of the document, the GLDates for all the distributions related |
2838 | to that document are updated to the new GLDate. |
2839 | In this case, we need : |
2840 | Reset the event_id of the distributions to NULL. |
2841 | Conserve only one event of one type and a GLDate. |
2842 | The denormalise mode should restamped the distributions with the |
2843 | correct event_id. |
2844 +--------------------------------------------------------------------*/
2845 keep_flag := TRUE;
2846
2847 --This can happen only real time OLTP
2848 IF p_xla_ev_rec.xla_mode = 'O' THEN
2849
2850 IF bld_ev_ent.COUNT > 0 THEN
2851
2852 FOR indx IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
2853
2854 IF bld_ev_ent(indx).event_type_code = ev_rec.ev_match_type(i) AND
2855 bld_ev_ent(indx).transaction_number= ev_rec.trx_number(i) AND
2856 bld_ev_ent(indx).security_id_int_1 = ev_rec.org_id(i) AND
2857 bld_ev_ent(indx).event_date = ev_rec.ev_match_gl_date(i) AND
2858 bld_ev_ent(indx).event_status_code = ev_rec.ev_match_status(i) AND
2859 bld_ev_ent(indx).event_id <> ev_rec.ev_match_event_id(i)
2860 THEN
2861 log('clean events (event_id) : '||ev_rec.ev_match_event_id(i));
2862 log('clean events (event_type_code) : '||ev_rec.ev_match_type(i));
2863 log('clean events (transaction_number) : '||ev_rec.trx_number(i));
2864
2865 ------------------
2866 -- unset_event_ids
2867 ------------------
2868 un_denormalize_posting_entity(p_xla_doc => p_xla_ev_rec.xla_doc_table,
2869 p_event_id => ev_rec.ev_match_event_id(i));
2870
2871 ----------------
2872 -- delete events
2873 ----------------
2874
2875 -- Set source_info
2876 l_event_source_info.application_id := 222;
2877 l_event_source_info.legal_entity_id := ev_rec.legal_entity_id(i); --to be set
2878 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
2879
2880 /* Bug 6932145: Modified l_event_source_info.entity_type_code = NULL to l_entity_code */
2881 l_event_source_info.entity_type_code := l_entity_code; -- '';
2882 l_event_source_info.transaction_number := ev_rec.trx_number(i);
2883 l_event_source_info.source_id_int_1 := ev_rec.trx_id(i);
2884
2885 -- Set security_context
2886 l_security_context.security_id_int_1 := ev_rec.org_id(i);
2887
2888 -- Delete the event
2889 xla_events_pub_pkg.delete_event
2890 ( p_event_source_info => l_event_source_info,
2891 p_event_id => ev_rec.ev_match_event_id(i),
2892 p_valuation_method => NULL,
2893 p_security_context => l_security_context);
2894
2895 keep_flag := FALSE;
2896
2897 EXIT;
2898
2899 END IF; --event needs to be deleted
2900
2901 END LOOP; --loop through built events
2902
2903 END IF; --built event exists
2904
2905 END IF;--if OLTP then check whether existing events need to be deleted
2906
2907 IF keep_flag THEN
2908
2909 j := j + 1;
2910 l_type := 'BUILT_AN_EVENT';
2911 l_build_ctr := j;
2912 log('l_type ' || l_type);
2913
2914 --Build the event record for update. It is possible for two dists
2915 --to have the same matching event, so for the same event two Update
2916 --dml statements will be introduced in OLTP only, the overhead is
2917 --acceptable versus unnecessarily sweeping the build cache table
2918 --which we intend to use for bulk operations only
2919 bld_ev_rec.bld_dml_flag(l_build_ctr) := 'U';
2920 bld_ev_rec.bld_temp_event_id(l_build_ctr) := ev_rec.ev_match_event_id(i);
2921 bld_ev_ent(l_build_ctr).transaction_number:= ev_rec.trx_number(i);
2922 bld_ev_ent(l_build_ctr).security_id_int_1 := ev_rec.org_id(i);
2923 bld_ev_ent(l_build_ctr).event_date := ev_rec.ev_match_gl_date(i);
2924 bld_ev_ent(l_build_ctr).event_status_code := ev_rec.ev_match_status(i);
2925 bld_ev_ent(l_build_ctr).event_type_code := ev_rec.ev_match_type(i);
2926 bld_ev_ent(l_build_ctr).event_id := ev_rec.ev_match_event_id(i);
2927 bld_ev_ent(l_build_ctr).source_id_int_1 := ev_rec.trx_id(i);
2928 --{HYU transaction_date,legal_entity_id
2929 bld_ev_ent(l_build_ctr).transaction_date := ev_rec.transaction_date(i);
2930 bld_ev_ent(l_build_ctr).reference_num_1 := ev_rec.legal_entity_id(i);
2931 --}
2932
2933 dump_bld_rec(p_bld_rec => bld_ev_rec ,
2934 p_i => l_build_ctr,
2935 p_tag => 'bld_ev_rec' );
2936
2937
2938 dump_event_info(p_ev_info_tab => bld_ev_ent,
2939 p_i => l_build_ctr,
2940 p_tag => 'bld_ev_ent');
2941
2942
2943 END IF;
2944
2945 END IF; --Update event condition
2946
2947 /*-----------------------------------------------------------------+
2948 | Create Events for distributions which do not have an existing |
2949 | event to latch on to. |
2950 | |
2951 | For 1.02 1.04 1.08 1.10 need to check if REVENUE RECOGNITION |
2952 | has run on the invoice. |
2953 +-----------------------------------------------------------------*/
2954 IF (l_change_matrix IN ('1.05','1.06','1.11','1.12')
2955 --{BUG#3999572
2956 --,1.15,1.18,1.19,1.20)
2957 --}
2958 AND (NOT ev_match_flg))
2959 OR
2960 (l_change_matrix IN ('1.02','1.04','1.08','1.10') AND (l_revrun = 'Y')
2961 AND (NOT ev_match_flg)) THEN
2962
2963 log('Building an Event in Insert new' ||
2964 ' event construct ');
2965
2966 --increment the event build table cell counter
2967 j := j + 1;
2968 l_type := 'BUILT_AN_EVENT';
2969 l_build_ctr := j;
2970 log('l_type ' || l_type);
2971
2972 /*----------------------------------------------------+
2973 |Create event - construct event attributes |
2974 +----------------------------------------------------*/
2975 bld_ev_rec.bld_dml_flag(l_build_ctr) := 'I';
2976 bld_ev_ent(l_build_ctr).source_id_int_1 := ev_rec.trx_id(i);
2977 bld_ev_ent(l_build_ctr).transaction_number := ev_rec.trx_number(i);
2978 bld_ev_ent(l_build_ctr).security_id_int_1 := ev_rec.org_id(i);
2979 --{HYU transaction_date,legal_entity_id
2980 bld_ev_ent(l_build_ctr).transaction_date := ev_rec.transaction_date(i);
2981 bld_ev_ent(l_build_ctr).reference_num_1 := ev_rec.legal_entity_id(i);
2982 --}
2983 /*----------------------------------------------------+
2984 |Set the event GL Date |
2985 +----------------------------------------------------*/
2986 log('Set the event GL Date');
2987 IF (ev_rec.posttogl(i) = 'Y') THEN
2988 IF to_char(ev_rec.dist_gl_date(i),'DD-MM-YYYY')= '01-01-1900' THEN
2989 bld_ev_ent(l_build_ctr).event_date := '';
2990 ELSE
2991 bld_ev_ent(l_build_ctr).event_date := ev_rec.dist_gl_date(i);
2992 END IF;
2993 ELSIF (ev_rec.posttogl(i) = 'N') THEN
2994 IF to_char(ev_rec.dist_gl_date(i),'DD-MM-YYYY') = '01-01-1900' THEN
2995 --{Bug#3320427 None postable trx event_date should be 01-01-1900
2996 -- event date is mandatory for XLA
2997 bld_ev_ent(l_build_ctr).event_date := TO_DATE('01-01-1900','DD-MM-YYYY');
2998 --}
2999 ELSE
3000 bld_ev_ent(l_build_ctr).event_date := ev_rec.dist_gl_date(i);
3001 END IF;
3002 END IF; --post to gl condition to set gl date
3003
3004 /*----------------------------------------------------+
3005 |Set the event Status |
3006 +----------------------------------------------------*/
3007 IF ev_rec.pstid(i) <> -3 THEN
3008 -- P : PROCESSED
3009 bld_ev_ent(l_build_ctr).event_status_code := 'P';
3010
3011 ELSIF l_change_matrix IN ('1.02','1.04','1.05','1.06') THEN
3012 --Bug#3320427 exclude 1.15 for none postable trx creation)
3013 -- I : INCOMPLETE
3014 bld_ev_ent(l_build_ctr).event_status_code := 'I';
3015
3016 ELSIF l_change_matrix IN ('1.18','1.19','1.20','1.15') THEN
3017 --Bug#3320427 include 1.15 to create No Active event
3018 --for none postable transaction
3019 -- N : NOACTION
3020 bld_ev_ent(l_build_ctr).event_status_code := 'N';
3021
3022 ELSIF l_change_matrix IN ('1.08','1.10','1.11','1.12') THEN
3023 -- U : UNPROCESSED
3024 bld_ev_ent(l_build_ctr).event_status_code := 'U';
3025
3026 END IF; --set the event status
3027
3028 /*---------------------------------------------------+
3029 |Set the event Type |
3030 +---------------------------------------------------*/
3031 bld_ev_ent(l_build_ctr).event_type_code := ev_rec.override_event(i);
3032
3033 /*---------------------------------------------------+
3034 |Set the event Id to the temp internal ID, bld id |
3035 |cell needs to be populated for update, so make sure|
3036 |contiguous null cell is created. |
3037 +---------------------------------------------------*/
3038 bld_ev_ent(l_build_ctr).event_id := '';
3039
3040 dump_bld_rec(p_bld_rec => bld_ev_rec,
3041 p_i => l_build_ctr ,
3042 p_tag => 'bld_ev_rec');
3043
3044 dump_event_info(p_ev_info_tab => bld_ev_ent,
3045 p_i => l_build_ctr,
3046 p_tag => 'bld_ev_ent');
3047
3048 /*-----------------------------------------------------+
3049 |Override the current distributions matching event id |
3050 |and match temp event id with the actual event/temp id|
3051 +-----------------------------------------------------*/
3052
3053 END IF; --Insert event condition
3054
3055 /*---------------------------------------------------------------+
3056 |Abnormal conditions raise user defined exception. Typically |
3057 |the current trx should be skipped in upgrade mode. |
3058 | Remove : 1.11 When adding distributions to a posted document |
3059 | is situation can happen |
3060 | the document status is C |
3061 | the existing event status is P |
3062 | the match_flg is TRUE |
3063 +---------------------------------------------------------------*/
3064 IF (l_change_matrix IN ('1.05','1.06'
3065 --{BUG#399572
3066 --,1.15,1.18,1.21
3067 --}
3068 ,'1.12')
3069 AND (ev_match_flg)) THEN
3070
3071 --Bug 5600736 added the block below
3072 IF p_xla_ev_rec.xla_mode = 'O' AND ev_rec.pstid(i) <> -3
3073 AND l_change_matrix = '1.12' THEN
3074 null; --skip the distribution as distribution is posted
3075 ELSE
3076 RAISE abnormal_cond;
3077 END IF;
3078
3079 END IF; --abnormal condition
3080
3081 --{BUG#4414585 - dont do anything
3082 IF l_change_matrix IN ('3.01') THEN
3083 NULL;
3084 END IF;
3085 --}
3086
3087 /*----------------------------------------------------+
3088 |Sweep Trx Cache to raise validation if other dist's |
3089 |with the different gl dates exist for the same event|
3090 |Applicable to events 1.2, 1.4, 1.6, 1.10 for OLTP an|
3091 |SQL may require to be added. Row based operations. |
3092 |May be in update construct above. |
3093 +----------------------------------------------------*/
3094
3095 /*---------------------------------------------------------+
3096 |Add an event to the events cache table for the current |
3097 |transaction being processed. Get the hash index using |
3098 |hash function and ascertain if the event has been cached |
3099 +---------------------------------------------------------*/
3100
3101 /*-----------------------------------------------------------+
3102 |Set the previous row id of the distribution and trx id |
3103 |used to reinitialize caches or skip processing duplicate |
3104 |rows. |
3105 +-----------------------------------------------------------*/
3106 --prev_distid := ev_rec.dist_id(i); same dist is not reprocessed
3107 prev_trx_id := ev_rec.trx_id(i); --reinitalize trx cache
3108
3109 log('prev_trx_id = ' || prev_trx_id);
3110
3111 END LOOP; --process distributions
3112
3113 /*---------------------------------------------------------------------+
3114 |Call the xla events api passing it the tables for Bulk Insert, Update|
3115 |On return for inserted rows, the event_id will be returned and the |
3116 |distributions will be updated with this event id using the temp event|
3117 |id which will ascertain the mapping of internal id's to actual ids |
3118 |for a distribution.To be replaced by call to xla events api commit |
3119 |issued by owning product. |
3120 +---------------------------------------------------------------------*/
3121
3122 /*-------------------------------------------------------------+
3123 | Insert into Events table, to be replaced with XLA apis |
3124 +-------------------------------------------------------------*/
3125
3126 IF p_xla_ev_rec.xla_mode IN ('U','B') AND test_flag = 'N' THEN
3127
3128 log('xla_events_pub_pkg.create_bulk_events xla_mode IN (U,B)');
3129
3130 /*
3131 bld_ev_ent := xla_events_pub_pkg.create_bulk_events(
3132 p_application_id => 222 ,
3133 p_legal_entity_id => '1' ,-- to be set later
3134 p_ledger_id => arp_standard.sysparm.set_of_books_id ,-- to be set later
3135 p_entity_type_code => l_entity_code ,-- to be set later
3136 p_array_entity_event_info => bld_ev_ent);
3137 -- p_valuation_method => '' ,
3138 -- p_security_context => l_security_context);
3139 */
3140 /* As this call is not suitable to AR, the bug for XLA spi enhancement has been logged
3141 BUG#4448003 for now converting this call to single event call
3142 xla_events_pub_pkg.create_bulk_events(
3143 p_source_application_id => NULL ,
3144 p_application_id => 222 ,
3145 p_legal_entity_id => '1' ,-- to be set later
3146 p_ledger_id => arp_standard.sysparm.set_of_books_id ,-- to be set later
3147 p_entity_type_code => l_entity_code);
3148 */
3149 cnt := bld_ev_ent.COUNT;
3150 IF cnt > 0 THEN
3151 FOR m IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
3152 INSERT INTO xla_events_int_gt (
3153 APPLICATION_ID
3154 , LEGAL_ENTITY_ID
3155 , LEDGER_ID
3156 , ENTITY_CODE
3157 , TRANSACTION_NUMBER
3158 , SOURCE_ID_INT_1
3159 , EVENT_TYPE_CODE
3160 , EVENT_STATUS_CODE
3161 , EVENT_DATE
3162 , SECURITY_ID_INT_1
3163 , TRANSACTION_DATE )
3164 VALUES (
3165 222
3166 , bld_ev_ent(m).reference_num_1 -- LEGAL_ENTITY_ID
3167 , arp_standard.sysparm.set_of_books_id -- LEDGER_ID
3168 , l_entity_code -- ENTITY_CODE
3169 , bld_ev_ent(m).transaction_number -- TRANSACTION_NUMBER
3170 , bld_ev_ent(m).source_id_int_1 -- SOURCE_ID_INT_1
3171 , bld_ev_ent(m).event_type_code -- EVENT_TYPE_CODE
3172 , bld_ev_ent(m).event_status_code -- EVENT_STATUS_CODE
3173 , bld_ev_ent(m).event_date -- EVENT_DATE
3174 , bld_ev_ent(m).security_id_int_1 -- SECURITY_ID_INT_1
3175 , bld_ev_ent(m).transaction_date); -- TRANSACTION_DATE
3176 END LOOP;
3177 END IF;
3178
3179 log('Bulk Mode not ready - using OLTP');
3180
3181 END IF;
3182
3183 /*--------------------------------------------------------------+
3184 |Bulk update the distributions already existing in the Database|
3185 |with the modified gl date or status, if unchanged these should|
3186 |retain their default original values in the assignments below.|
3187 +--------------------------------------------------------------*/
3188
3189 IF p_xla_ev_rec.xla_mode = 'O'
3190 --{Work around waiting for bulk mode
3191 -- OR p_xla_ev_rec.xla_mode = 'B'
3192 --}
3193 THEN
3194
3195 IF bld_ev_ent.COUNT > 0 THEN
3196
3197 FOR m IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
3198
3199 /*----------------------------------------------------------+
3200 |Set the event source details |
3201 +----------------------------------------------------------*/
3202 l_event_source_info.application_id := 222;
3203 --{HYU transaction_date,legal_entity_id
3204 l_event_source_info.legal_entity_id := bld_ev_ent(l_build_ctr).reference_num_1; -- --to be set
3205 --}
3206 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
3207 l_event_source_info.entity_type_code := l_entity_code ;
3208 l_event_source_info.transaction_number := bld_ev_ent(m).transaction_number;
3209 l_event_source_info.source_id_int_1 := bld_ev_ent(m).source_id_int_1;
3210
3211 dump_event_source_info
3212 (x_ev_source_info => l_event_source_info);
3213
3214 /*----------------------------------------------------------+
3215 |Set the security details |
3216 +----------------------------------------------------------*/
3217 l_security_context.security_id_int_1 := bld_ev_ent(m).security_id_int_1;
3218 /*----------------------------------------------------------+
3219 |Set the event reference details |
3220 +----------------------------------------------------------*/
3221 --to be ascertained after events template is filled
3222
3223 IF bld_ev_rec.bld_dml_flag(m) = 'I' AND test_flag = 'N' THEN
3224
3225 log('XLA_EVENTS_PUB_PKG.create_event');
3226
3227 z := z + 1;
3228 log('hyu calling create event for zth time :'||z);
3229
3230 lnb := XLA_EVENTS_PUB_PKG.create_event(
3231 p_event_source_info => l_event_source_info ,
3232 p_event_type_code => bld_ev_ent(m).event_type_code ,
3233 p_event_date => bld_ev_ent(m).event_date ,
3234 p_event_status_code => bld_ev_ent(m).event_status_code ,
3235 p_event_number => NULL ,
3236 p_reference_info => l_reference_info ,
3237 p_valuation_method => '' ,
3238 --{HYU transaction_date
3239 p_transaction_date => bld_ev_ent(m).transaction_date ,
3240 --}
3241 p_security_context => l_security_context );
3242
3243 ELSIF bld_ev_rec.bld_dml_flag(m) = 'U' AND test_flag = 'N' THEN
3244
3245 log('XLA_EVENTS_PUB_PKG.update_event');
3246
3247 log('bld_ev_ent(m).event_id :'||bld_ev_ent(m).event_id );
3248 log('bld_ev_ent(m).event_type_code :'||bld_ev_ent(m).event_type_code );
3249 log(' bld_ev_ent(m).event_date :'|| bld_ev_ent(m).event_date );
3250 log(' bld_ev_ent(m).event_status_code :'|| bld_ev_ent(m).event_status_code);
3251 log(' p_security_context.security_id_int_1 :'|| l_security_context.security_id_int_1 );
3252
3253 XLA_EVENTS_PUB_PKG.update_event(
3254 p_event_source_info => l_event_source_info ,
3255 p_event_id => bld_ev_ent(m).event_id ,
3256 p_event_type_code => bld_ev_ent(m).event_type_code ,
3257 p_event_date => bld_ev_ent(m).event_date ,
3258 p_event_status_code => bld_ev_ent(m).event_status_code ,
3259 p_valuation_method => '' ,
3260 p_security_context => l_security_context );
3261
3262 -- Checking change of transaction number for Copy Document Sequence Feature
3263
3264 IF NVL(l_entity_code,' ') = 'TRANSACTIONS' THEN
3265
3266 log(' Checking if Trxn number has changed by Copy Document Sequence Feature');
3267
3268 BEGIN
3269 SELECT a.transaction_number INTO trxn_number
3270 FROM xla_transaction_entities_upg a,
3271 xla_events b
3272 WHERE NVL(a.source_id_int_1,-99) = bld_ev_ent(m).source_id_int_1
3273 AND b.event_id = bld_ev_ent(m).event_id
3274 AND a.entity_id = b.entity_id
3275 AND a.security_id_int_1 = bld_ev_ent(m).security_id_int_1
3276 AND a.application_id = 222;
3277
3278 EXCEPTION
3279 WHEN OTHERS THEN
3280 log('EXCEPTION: XLA TRANSACTION NUMBER UPDATE');
3281 log('SQLERRM ' || SQLERRM);
3282 RAISE;
3283 END;
3284
3285 IF NVL(trxn_number,-99) <> bld_ev_ent(m).transaction_number THEN
3286 XLA_EVENTS_PUB_PKG.UPDATE_TRANSACTION_NUMBER(
3287 p_event_source_info => l_event_source_info,
3288 p_transaction_number => bld_ev_ent(m).transaction_number,
3289 p_valuation_method => '',
3290 p_security_context => l_security_context ,
3291 p_event_id => bld_ev_ent(m).event_id );
3292 END IF;
3293 END IF; -- end checking change of trxn number for transactions
3294 END IF; --end insert or update events in OLTP mode
3295
3296 END LOOP;
3297
3298 END IF;
3299
3300 END IF;
3301
3302 /*----------------------------------------------------------+
3303 |Denormalize the event id which has been inserted into the |
3304 |database, and update the event id column in the dist table|
3305 |This denormalization is used by the extract process. |
3306 |The internal negative id i.e. temp_event_id is used to |
3307 |ascertain as to which event. |
3308 +----------------------------------------------------------*/
3309 --Only used for upgrade
3310 IF p_xla_ev_rec.xla_mode = 'U' THEN
3311 Commit;
3312 END IF; --mode is Upgrade, Oltp or Batch
3313
3314 -- Exit from the loop if no. of rows fetched < array size
3315 EXIT WHEN l_rows_fetched < MAX_ARRAY_SIZE;
3316
3317 END LOOP; --Array (Bulk) Fetch
3318
3319 --{XLA BULK API
3320 IF p_xla_ev_rec.xla_mode = 'B' THEN
3321 OPEN for_batch;
3322 FETCH for_batch INTO l_test;
3323 IF for_batch%FOUND THEN
3324 log('Calling xla_events_pub_pkg.create_bulk_events +');
3325 xla_events_pub_pkg.create_bulk_events
3326 (p_application_id => 222,
3327 p_ledger_id => arp_standard.sysparm.set_of_books_id,
3328 p_entity_type_code => l_entity_code);
3329 --avoid recreation of successfully events
3330 DELETE from xla_events_int_gt WHERE entity_id IS NOT NULL;
3331 log('Calling xla_events_pub_pkg.create_bulk_events -');
3332 END IF;
3333 CLOSE for_batch;
3334 END IF;
3335 --}
3336 /*--------------------------------------------------------------+
3337 |Bulk update the distributions event ids with the newly created|
3338 |event ids as part of the mark transaction data associated with|
3339 |the event. |
3340 +--------------------------------------------------------------*/
3341
3342 <<endlabel>>
3343 log('ARP_XLA_EVENTS.Create_All_Events ()-');
3344
3345 EXCEPTION
3346 WHEN OTHERS THEN
3347 log('EXCEPTION: ARP_XLA_EVENTS.Create_All_Events ');
3348 log('SQLERRM ' || SQLERRM);
3349 log('EXCEPTION: ARP_XLA_EVENTS.Create_All_Events ');
3350 log('SQLERRM ' || SQLERRM);
3351 RAISE;
3352 END Create_All_Events;
3353
3354 /*========================================================================
3355 | PRIVATE PROCEDURE un_denormalize_posting_entity
3356 |
3357 | DESCRIPTION
3358 | Purpose : Erase the event_id on the distributions.
3359 | It determines the posting entity on each the event_id should be erase.
3360 |
3361 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3362 | Create_all_events
3363 |
3364 | CALLS PROCEDURES/FUNCTIONS
3365 |
3366 | PARAMETERS p_override_event generated by SQL dynamic
3367 | p_trx_type transaction type
3368 | p_exist_event_type existing event type
3369 | p_event_id event_id to be erase on distributions
3370 |
3371 | KNOWN ISSUES
3372 |
3373 | NOTES
3374 |
3375 | MODIFICATION HISTORY
3376 | Date Author Description of Changes
3377 | 02-OCT-2002 H. Yu
3378 *=======================================================================*/
3379 PROCEDURE un_denormalize_posting_entity
3380 ( p_xla_doc IN VARCHAR2,
3381 p_event_id IN NUMBER ) IS
3382
3383 BEGIN
3384 log('arp_xla_events.un_denormalize_posting_entity()+');
3385
3386 IF test_flag = 'Y' THEN
3387 GOTO endlabel;
3388 END IF;
3389
3390 IF p_xla_doc IN ('CT','CTCMAPP','CTNORCM') THEN
3391
3392 UPDATE ra_cust_trx_line_gl_dist
3393 SET event_id = NULL
3394 WHERE event_id = p_event_id;
3395
3396 ELSIF p_xla_doc IN ('CRH', 'CRHMCD', 'CRHAPP') THEN
3397
3398 UPDATE ar_cash_receipt_history
3399 SET event_id = NULL
3400 WHERE event_id = p_event_id;
3401
3402 ELSIF p_xla_doc = 'ADJ' THEN
3403
3404 UPDATE ar_adjustments
3405 SET event_id = NULL
3406 WHERE event_id = p_event_id;
3407
3408 ELSIF p_xla_doc IN ('CRHAPP', 'APP') THEN
3409
3410 UPDATE ar_receivable_applications
3411 SET event_id = NULL
3412 WHERE event_id = p_event_id;
3413
3414 ELSIF p_xla_doc IN ('CRHMCD', 'MCD') THEN
3415
3416 UPDATE ar_misc_cash_distributions
3417 SET event_id = NULL
3418 WHERE event_id = p_event_id;
3419
3420 ELSIF p_xla_doc = 'TRH' THEN
3421
3422 UPDATE ar_transaction_history
3423 SET event_id = NULL
3424 WHERE event_id = p_event_id;
3425
3426 END IF;
3427
3428 <<endlabel>>
3429
3430 log('arp_xla_events.un_denormalize_posting_entity()-');
3431
3432 EXCEPTION
3433 WHEN OTHERS THEN
3434 log('EXCEPTION: arp_xla_events.un_denormalize_posting_entity');
3435 log('SQLERRM '||sqlerrm);
3436 RAISE;
3437
3438 END un_denormalize_posting_entity;
3439
3440 /*========================================================================
3441 | Private Function: is_one_acct_asg_on_ctlgd
3442 |
3443 | Description :
3444 | Function only work for OLTP mode because it needs a org context.
3445 | If necessary it is extensible for other mode
3446 | Return
3447 | + 'Y' if at least one invoice line has its account assignment created.
3448 | + 'N' if no line has its account assignment created - Typically when a
3449 | invoice with rules is completed without having been submitted to
3450 | the revenue recognition process.
3451 | + 'X' if the invoice p_posting_entity is not CTLGD means the accounting
3452 | model does not use CTLGD.
3453 |
3454 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3455 | Create_all_events
3456 |
3457 | CALLS PROCEDURES/FUNCTIONS
3458 |
3459 | Parameters :
3460 | 1) p_invoice_id Customer_trx_id
3461 | 2) p_posting_entity Transaction_type
3462 | 3) p_mode 'O' OLTP only
3463 |
3464 | KNOWN ISSUES
3465 |
3466 | NOTES
3467 |
3468 | MODIFICATION HISTORY
3469 | Date Author Description of Changes
3470 | 02-OCT-2002 H. Yu
3471 *=======================================================================*/
3472 FUNCTION is_one_acct_asg_on_ctlgd
3473 (p_invoice_id IN NUMBER,
3474 p_posting_entity IN VARCHAR2 DEFAULT 'CTLGD',
3475 p_mode IN VARCHAR2 DEFAULT 'O') RETURN VARCHAR2
3476 IS
3477
3478 CURSOR cu_is_rev_rec_run IS
3479 SELECT 'Y'
3480 FROM ra_customer_trx_lines ctl
3481 WHERE ctl.customer_trx_id = p_invoice_id
3482 AND ctl.line_type = 'LINE'
3483 AND NVL(ctl.autorule_complete_flag,'Y') <> 'N';
3484
3485 ltab DBMS_SQL.VARCHAR2_TABLE;
3486 lres VARCHAR2(1);
3487
3488 BEGIN
3489 IF p_posting_entity <> 'CTLGD' THEN
3490 lres := 'X';
3491 ELSE
3492 OPEN cu_is_rev_rec_run;
3493 FETCH cu_is_rev_rec_run INTO lres;
3494 IF cu_is_rev_rec_run%NOTFOUND THEN
3495 -- None line has its account assignments created
3496 lres := 'N';
3497 -- Otherwise at least one line has its account
3498 -- assignments created the result will be 'Y'
3499 END IF;
3500 CLOSE cu_is_rev_rec_run;
3501 END IF;
3502 RETURN lres;
3503
3504 EXCEPTION
3505 WHEN OTHERS THEN
3506 IF cu_is_rev_rec_run%ISOPEN THEN CLOSE cu_is_rev_rec_run; END IF;
3507 RAISE;
3508
3509 END;
3510
3511
3512 /*========================================================================
3513 | PUBLIC PROCEDURE Upd_Dist
3514 |
3515 | DESCRIPTION
3516 | Denormalizes the event id for Receivables documents
3517 |
3518 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3519 | Execute
3520 |
3521 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3522 |
3523 | PARAMETERS
3524 | p_xla_ev_rec IN xla_events_type
3525 |
3526 | KNOWN ISSUES
3527 |
3528 | NOTES
3529 |
3530 | MODIFICATION HISTORY
3531 | Date Author Description of Changes
3532 *=======================================================================*/
3533 PROCEDURE Upd_Dist(p_xla_ev_rec IN xla_events_type) IS
3534
3535 type l_rowid_type IS TABLE OF VARCHAR2(50)
3536 INDEX BY BINARY_INTEGER;
3537 type l_event_id_type IS TABLE OF NUMBER(15)
3538 INDEX BY BINARY_INTEGER;
3539
3540 ev_rec ev_rec_type;
3541 empty_ev_rec ev_rec_type;
3542
3543 l_rowid l_rowid_type;
3544 l_event_id l_event_id_type;
3545
3546 l_last_fetch BOOLEAN := FALSE;
3547
3548 l_select_c INTEGER;
3549
3550 l_ignore INTEGER;
3551 l_rows_fetched VARCHAR2(10);
3552 l_low INTEGER:=0;
3553 l_high INTEGER:=0;
3554 --6785758
3555 l_last_updated_by NUMBER := arp_standard.profile.user_id;
3556 l_last_update_login NUMBER := arp_standard.profile.last_update_login;
3557 BEGIN
3558
3559 log('ARP_XLA_EVENTS.Upd_Dist()+');
3560
3561 IF p_xla_ev_rec.xla_call IN ('D','B') THEN
3562 null;
3563 ELSE goto endlabel;
3564 END IF;
3565
3566 l_select_c := Get_Select_Cursor(p_xla_ev_rec => p_xla_ev_rec,
3567 p_call_point => 2);
3568
3569 l_ignore := dbms_sql.execute(l_select_c);
3570
3571 log( 'Fetching select stmt');
3572
3573 LOOP -- Main Cursor Loop
3574
3575 ev_rec.dist_row_id := empty_ev_rec.dist_row_id;
3576 ev_rec.dist_event_id := empty_ev_rec.dist_event_id;
3577
3578 l_rows_fetched := dbms_sql.fetch_rows(l_select_c);
3579
3580 log('Rows Fetched are ' || l_rows_fetched);
3581
3582 l_low := l_high + 1;
3583 l_high:= l_high + l_rows_fetched;
3584
3585 IF l_rows_fetched > 0 THEN
3586
3587 log('Fetched a row ');
3588 log('l_low ' || l_low);
3589 log('l_high ' || l_high);
3590
3591 get_column_values(p_select_c => l_select_c,
3592 p_xla_ev_rec => p_xla_ev_rec,
3593 p_ev_rec => ev_rec,
3594 p_call_point => 2);
3595
3596 -- no more rows to fetch
3597 IF l_rows_fetched < MAX_ARRAY_SIZE THEN
3598 log('Done fetching 3');
3599
3600 IF( dbms_sql.is_open( l_select_c) ) THEN
3601 dbms_sql.close_cursor( l_select_c );
3602 END IF;
3603
3604 END IF; --no more rows to fetch
3605
3606 ELSE --if rows fetched = 0
3607 log('Done fetching 4');
3608
3609 IF( dbms_sql.is_open( l_select_c ) ) THEN
3610 dbms_sql.close_cursor( l_select_c );
3611 END IF;
3612
3613 EXIT;
3614
3615 END IF; --rows fetched greater than 0
3616
3617 log('Commence bulk update processing');
3618
3619 IF (p_xla_ev_rec.xla_doc_table IN ('CT','CTNORCM')
3620 OR (p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND g_call_number = 2))
3621 AND test_flag = 'N' THEN
3622
3623 log('Bulk Updating Transactions');
3624
3625 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3626 --6785758
3627 UPDATE ra_cust_trx_line_gl_dist_all ctlgd
3628 SET ctlgd.event_id = ev_rec.dist_event_id(m),
3629 ctlgd.last_update_date = SYSDATE,
3630 ctlgd.last_update_login = l_last_update_login,
3631 ctlgd.last_updated_by = l_last_updated_by
3632 WHERE ctlgd.rowid = ev_rec.dist_row_id(m);
3633
3634 END IF;
3635
3636 IF p_xla_ev_rec.xla_doc_table = 'ADJ' AND test_flag = 'N' THEN
3637 log('Bulk Updating Adjustments ');
3638 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3639 UPDATE ar_adjustments_all adj
3640 SET adj.event_id = ev_rec.dist_event_id(m),
3641 adj.last_update_date = SYSDATE,
3642 adj.last_update_login = l_last_update_login,
3643 adj.last_updated_by = l_last_updated_by
3644 WHERE adj.rowid = ev_rec.dist_row_id(m);
3645 END IF;
3646
3647 IF p_xla_ev_rec.xla_doc_table = 'CRH' AND test_flag = 'N' THEN
3648 log('Bulk Updating Cash Receipt History');
3649 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3650 --6785758
3651 UPDATE ar_cash_receipt_history_all crh
3652 SET crh.event_id = ev_rec.dist_event_id(m),
3653 crh.last_update_date = SYSDATE,
3654 crh.last_update_login = l_last_update_login,
3655 crh.last_updated_by = l_last_updated_by
3656 WHERE crh.rowid = ev_rec.dist_row_id(m);
3657 END IF;
3658
3659 IF p_xla_ev_rec.xla_doc_table = 'MCD' AND test_flag = 'N' THEN
3660 log('Bulk Updating misc cash distributions');
3661 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3662 --6785758
3663 UPDATE ar_misc_cash_distributions_all mcd
3664 SET mcd.event_id = ev_rec.dist_event_id(m),
3665 mcd.last_update_date = SYSDATE,
3666 mcd.last_update_login = l_last_update_login,
3667 mcd.last_updated_by = l_last_updated_by
3668 WHERE mcd.rowid = ev_rec.dist_row_id(m);
3669 END IF;
3670
3671 IF (p_xla_ev_rec.xla_doc_table IN ('APP', 'CMAPP')
3672 OR ( p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND g_call_number = 1))
3673 AND test_flag = 'N' THEN
3674 log('Bulk Updating receivable applications');
3675 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3676 --6785758
3677 UPDATE ar_receivable_applications_all app
3678 SET app.event_id = ev_rec.dist_event_id(m),
3679 app.last_update_date = SYSDATE,
3680 app.last_update_login = l_last_update_login,
3681 app.last_updated_by = l_last_updated_by
3682 WHERE app.rowid = ev_rec.dist_row_id(m);
3683 END IF;
3684
3685 IF p_xla_ev_rec.xla_doc_table = 'TRH' AND test_flag = 'N' THEN
3686 log('Bulk Updating Bills Receivable transaction history ');
3687 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3688 --6785758
3689 UPDATE ar_transaction_history_all trh
3690 SET trh.event_id = ev_rec.dist_event_id(m),
3691 trh.last_update_date = SYSDATE,
3692 trh.last_update_login = l_last_update_login,
3693 trh.last_updated_by = l_last_updated_by
3694 WHERE trh.rowid = ev_rec.dist_row_id(m);
3695 END IF;
3696
3697 --Only used for upgrade
3698 IF p_xla_ev_rec.xla_mode = 'U' THEN
3699 Commit;
3700 END IF; --mode is Upgrade, Oltp or Batch
3701
3702 --Exit from the loop if no. of rows fetched < array size
3703 EXIT WHEN l_rows_fetched < MAX_ARRAY_SIZE;
3704
3705 END LOOP; --Array (Bulk) Fetch
3706
3707 <<endlabel>>
3708 log('ARP_XLA_EVENTS.Upd_Dist()-');
3709
3710 EXCEPTION
3711 WHEN OTHERS THEN
3712 log('EXCEPTION: ARP_XLA_EVENTS.Upd_Dist');
3713 RAISE;
3714
3715 END Upd_Dist;
3716
3717 /*========================================================================
3718 | PUBLIC PROCEDURE dump_ev_rec
3719 |
3720 | DESCRIPTION
3721 | Dumps the event record fetched for creation of events
3722 |
3723 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3724 | Create_All_Events
3725 |
3726 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3727 |
3728 | PARAMETERS
3729 | p_xla_ev_rec IN event record
3730 | p_i IN index
3731 |
3732 | KNOWN ISSUES
3733 |
3734 | NOTES
3735 |
3736 | MODIFICATION HISTORY
3737 | Date Author Description of Changes
3738 *=======================================================================*/
3739 PROCEDURE dump_ev_rec(p_ev_rec IN OUT NOCOPY ev_rec_type,
3740 p_i IN BINARY_INTEGER) IS
3741
3742 BEGIN
3743
3744 log(' ');
3745 log('ARP_XLA_EVENTS.dump_ev_rec()+');
3746
3747 IF p_ev_rec.trx_status.EXISTS(p_i) THEN
3748
3749 log('p_ev_rec.trx_status(' || p_i || ') = '
3750 || p_ev_rec.trx_status(p_i));
3751 log('p_ev_rec.trx_id(' || p_i || ') = '
3752 || p_ev_rec.trx_id(p_i));
3753 log('p_ev_rec.dist_event_id(' || p_i || ') = '
3754 || p_ev_rec.dist_event_id(p_i));
3755 log('p_ev_rec.dist_gl_date(' || p_i || ') = '
3756 || p_ev_rec.dist_gl_date(p_i));
3757 log('p_ev_rec.trx_type(' || p_i || ') = '
3758 || p_ev_rec.trx_type(p_i));
3759 log('p_ev_rec.posttogl(' || p_i || ') = '
3760 || p_ev_rec.posttogl(p_i));
3761 log('p_ev_rec.ev_match_event_id(' || p_i || ') = '
3762 || p_ev_rec.ev_match_event_id(p_i));
3763 log('p_ev_rec.ev_match_gl_date(' || p_i || ') = '
3764 || p_ev_rec.ev_match_gl_date(p_i));
3765 log('p_ev_rec.ev_match_status(' || p_i || ') = '
3766 || p_ev_rec.ev_match_status(p_i));
3767 log('p_ev_rec.ev_match_type(' || p_i || ') = '
3768 || p_ev_rec.ev_match_type(p_i));
3769 END IF;
3770
3771 log('ARP_XLA_EVENTS.dump_ev_rec()-');
3772 log(' ');
3773
3774 EXCEPTION
3775 WHEN OTHERS THEN
3776 log('EXCEPTION: ARP_XLA_EVENTS.dump_ev_rec');
3777 RAISE;
3778 END dump_ev_rec;
3779
3780
3781 --HYU
3782 PROCEDURE dump_event_source_info
3783 (x_ev_source_info IN OUT NOCOPY xla_events_pub_pkg.t_event_Source_info)
3784 IS
3785 BEGIN
3786
3787 log(' ');
3788 log('ARP_XLA_EVENTS.dump_event_source_info()+');
3789
3790 log('x_ev_source_info.application_id = '
3791 || x_ev_source_info.application_id);
3792 log('x_ev_source_info.legal_entity_id = '
3793 || x_ev_source_info.legal_entity_id);
3794 log('x_ev_source_info.ledger_id = '
3795 || x_ev_source_info.ledger_id);
3796 log('x_ev_source_info.entity_type_code = '
3797 || x_ev_source_info.entity_type_code);
3798 log('x_ev_source_info.transaction_number = '
3799 || x_ev_source_info.transaction_number);
3800 log('x_ev_source_info.source_id_int_1 = '
3801 || x_ev_source_info.source_id_int_1);
3802 log('x_ev_source_info.source_id_int_2 = '
3803 || x_ev_source_info.source_id_int_2);
3804 log('x_ev_source_info.source_id_int_3 = '
3805 || x_ev_source_info.source_id_int_3);
3806 log('x_ev_source_info.source_id_int_4 = '
3807 || x_ev_source_info.source_id_int_4);
3808 log('x_ev_source_info.source_id_char_1 = '
3809 || x_ev_source_info.source_id_char_1);
3810 log('x_ev_source_info.source_id_char_2 = '
3811 || x_ev_source_info.source_id_char_2);
3812 log('x_ev_source_info.source_id_char_3 = '
3813 || x_ev_source_info.source_id_char_3);
3814 log('x_ev_source_info.source_id_char_4 = '
3815 || x_ev_source_info.source_id_char_4);
3816 log('x_ev_source_info.legal_entity_id = '
3817 || x_ev_source_info.legal_entity_id);
3818 log('ARP_XLA_EVENTS.dump_event_source_info()-');
3819 log(' ');
3820
3821 EXCEPTION
3822 WHEN OTHERS THEN
3823 log('EXCEPTION: ARP_XLA_EVENTS.dump_event_source_info');
3824 RAISE;
3825 END dump_event_source_info;
3826
3827 /*========================================================================
3828 | PUBLIC FUNCTION Change_Matrix
3829 |
3830 | DESCRIPTION
3831 | Decision matix which returns a number stating whether an update,
3832 | insert or latch to an event is required.
3833 |
3834 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3835 | Execute
3836 |
3837 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3838 |
3839 | PARAMETERS
3840 | trx_status IN Transaction status
3841 | dist_gl_date IN gldate of distribution
3842 | ev_match_gl_date IN matching or existing event accountin date
3843 | ev_match_status IN event status
3844 | post_to_gl IN post to Gl
3845 |
3846 | KNOWN ISSUES
3847 |
3848 | NOTES
3849 |
3850 | MODIFICATION HISTORY
3851 | Date Author Description of Changes
3852 |
3853 *=======================================================================*/
3854 FUNCTION Change_Matrix(trx_status IN VARCHAR2 ,
3855 dist_gl_date IN DATE ,
3856 ev_match_gl_date IN DATE ,
3857 ev_match_status IN xla_events.event_status_code%TYPE,
3858 posttogl IN VARCHAR2) RETURN VARCHAR2 IS
3859
3860 l_change_matrix VARCHAR2(30);
3861
3862 BEGIN
3863
3864 log('ARP_XLA_EVENTS.Change_Matrix()+');
3865 log('trx_status :'||trx_status);
3866 log('dist_gl_date :'||dist_gl_date);
3867 log('ev_match_gl_date :'||ev_match_gl_date);
3868 log('ev_match_status :'||ev_match_status);
3869 log('posttogl :'||posttogl);
3870
3871 IF posttogl = 'Y' THEN
3872 log('posttogl' || posttogl);
3873 /*-------------------------------------------------------+
3874 |1.01 - Current trx status = Incomplete |
3875 | Current Gl date = Existing Event Gl Date |
3876 | Existing Event Status = Incomplete |
3877 +-------------------------------------------------------*/
3878 IF trx_status = 'I'
3879 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3880 AND ev_match_status = 'I' THEN
3881 l_change_matrix := '1.01';
3882
3883 /*------------------------------------------------------+
3884 |1.02 - Current trx status = Incomplete |
3885 | Current Gl date <> Existing Event Gl Date|
3886 | Existing Event Status = Incomplete |
3887 +------------------------------------------------------*/
3888 ELSIF trx_status = 'I'
3889 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3890 AND ev_match_status = 'I' THEN
3891 l_change_matrix := '1.02';
3892
3893 /*-------------------------------------------------------+
3894 |1.03 - Current trx status = Incomplete |
3895 | Current Gl date = Existing Event Gl Date |
3896 | Existing Event Status = Unprocessed |
3897 +-------------------------------------------------------*/
3898 ELSIF trx_status = 'I'
3899 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3900 AND ev_match_status = 'U' THEN
3901 l_change_matrix := '1.03';
3902
3903 /*-------------------------------------------------------+
3904 |1.04 - Current trx status = Incomplete |
3905 | Current Gl date <> Existing Event Gl Date |
3906 | Existing Event Status = Unprocessed |
3907 +-------------------------------------------------------*/
3908 ELSIF trx_status = 'I'
3909 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3910 AND ev_match_status = 'U' THEN
3911 l_change_matrix := '1.04';
3912
3913 /*-------------------------------------------------------+
3914 |1.05 - Current trx status = Incomplete |
3915 | Existing Event Status = Processed |
3916 +-------------------------------------------------------*/
3917 ELSIF trx_status = 'I'
3918 AND ev_match_status = 'P' THEN
3919 l_change_matrix := '1.05';
3920
3921 /*-------------------------------------------------------+
3922 |1.06 - Current trx status = Incomplete |
3923 | Existing Event Gl Date = NULL |
3924 | Existing Event Status = NULL |
3925 +-------------------------------------------------------*/
3926 ELSIF trx_status = 'I'
3927 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
3928 --{BUG#4414585 -- Meaning the GL_DATE has been defined
3929 -- because in the case of signed BR a postable transaction can have no
3930 -- gl_date defined at creation time but xla_event creation the gl_date is
3931 -- required column, hence 1.06 for event creation should only be possible
3932 -- if the gl_date has been provided
3933 AND to_char(dist_gl_date,'DD-MM-YYYY') <> '01-01-1900'
3934 AND ev_match_status = 'X' THEN
3935 l_change_matrix := '1.06';
3936
3937 -- In the case a postable transaction with gl_date is created, there are 2 options
3938 -- either not create any event or create a event with a dummy gl date
3939 -- testing not to create any event change matrix value is 3.01
3940 ELSIF trx_status = 'I'
3941 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
3942 AND to_char(dist_gl_date,'DD-MM-YYYY') = '01-01-1900'
3943 AND ev_match_status = 'X' THEN
3944 l_change_matrix := '3.01';
3945 --}
3946 /*-------------------------------------------------------+
3947 |1.06 - Current trx status = Incomplete |
3948 | Existing Event Gl Date = NULL |
3949 | Existing Event Status = NULL |
3950 +-------------------------------------------------------*/
3951
3952
3953
3954 /*-------------------------------------------------------+
3955 |1.07 - Current trx status = Complete |
3956 | Current Gl date = Existing Event Gl Date |
3957 | Existing Event Status = Incomplete |
3958 +-------------------------------------------------------*/
3959 ELSIF trx_status = 'C'
3960 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3961 AND ev_match_status = 'I' THEN
3962 l_change_matrix := '1.07';
3963
3964 /*-------------------------------------------------------+
3965 |1.08 - Current trx status = Complete |
3966 | Current Gl date <> Existing Event Gl Date |
3967 | Existing Event Status = Incomplete |
3968 +-------------------------------------------------------*/
3969 ELSIF trx_status = 'C'
3970 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3971 AND ev_match_status = 'I' THEN
3972 l_change_matrix := '1.08';
3973
3974 /*-------------------------------------------------------+
3975 |1.09 - Current trx status = Complete |
3976 | Current Gl date = Existing Event Gl Date |
3977 | Existing Event Status = Unprocessed |
3978 +-------------------------------------------------------*/
3979 ELSIF trx_status = 'C'
3980 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3981 AND ev_match_status = 'U' THEN
3982 l_change_matrix := '1.09';
3983
3984 /*------------------------------------------------------+
3985 |1.10- Current trx status = Complete |
3986 | Current Gl date <> Existing Event Gl Date |
3987 | Existing Event Status = Unprocessed |
3988 +------------------------------------------------------*/
3989 ELSIF trx_status = 'C'
3990 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3991 AND ev_match_status = 'U' THEN
3992 l_change_matrix := '1.10';
3993
3994 /*------------------------------------------------------+
3995 |1.11- Current trx status = Complete |
3996 | Existing Event Status = Processed |
3997 +------------------------------------------------------*/
3998 ELSIF trx_status = 'C'
3999 AND ev_match_status = 'P' THEN
4000 l_change_matrix := '1.11';
4001
4002 /*------------------------------------------------------+
4003 |1.12- Current trx status = Complete |
4004 | Existing Event Gl Date is null |
4005 | Existing Event Status is null |
4006 +------------------------------------------------------*/
4007 ELSIF trx_status = 'C'
4008 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4009 AND ev_match_status = 'X' THEN
4010 l_change_matrix := '1.12';
4011
4012 --{BUG#3320427
4013 /*----------------------------------------------------+
4014 | 1.22 - Current trx status = Incomplete |
4015 | Current CL date is NOT NULL or <> (01-01-1900)|
4016 | Exist Event date is NULL or (01-01-1900) |
4017 | Existing Event Status = No Action |
4018 +----------------------------------------------------*/
4019 ELSIF trx_status = 'I'
4020 AND ev_match_status = 'N'
4021 AND to_char(dist_gl_date, 'DD-MM-YYYY') <> '01-01-1900'
4022 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4023 THEN
4024 l_change_matrix := '1.22';
4025 --}
4026 END IF; --change matrix setup for postable distributions
4027
4028 ELSIF posttogl = 'N' THEN
4029 /*-------------------------------------------------------+
4030 |1.13 - Current trx status = Incomplete |
4031 | Current Gl date = null = Existing Event Gl Date|
4032 | Existing Event Status = Incomplete |
4033 +-------------------------------------------------------*/
4034 IF trx_status = 'I'
4035 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4036 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4037 AND ev_match_status = 'I' THEN
4038 l_change_matrix := '1.13';
4039
4040 /*-------------------------------------------------------+
4041 |1.14 - Current trx status = Incomplete |
4042 | Current Gl date = null = Existing Event Gl Date|
4043 | Existing Event Status = Noaction |
4044 +-------------------------------------------------------*/
4045 ELSIF trx_status = 'I'
4046 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4047 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4048 AND ev_match_status = 'N' THEN
4049 l_change_matrix := '1.14';
4050
4051 /*-------------------------------------------------------+
4052 |1.15 - Current trx status = Incomplete |
4053 | Current Gl date = null = Existing Event Gl Date|
4054 | Existing Event Status = null |
4055 +-------------------------------------------------------*/
4056 ELSIF trx_status = 'I'
4057 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4058 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4059 AND ev_match_status = 'X' THEN
4060 l_change_matrix := '1.15';
4061
4062 /*------------------------------------------------------+
4063 |1.16 - Current trx status = Complete |
4064 | Current Gl date = null = Existing Event Gl Date|
4065 | Existing Event Status = Incomplete |
4066 +------------------------------------------------------*/
4067 ELSIF trx_status = 'C'
4068 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4069 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4070 AND ev_match_status = 'I' THEN
4071 l_change_matrix := '1.16';
4072
4073 /*------------------------------------------------------+
4074 |1.17 - Current trx status = Complete |
4075 | Current Gl date = null = Existing Event Gl Date|
4076 | Existing Event Status = Noaction |
4077 +------------------------------------------------------*/
4078 ELSIF trx_status = 'C'
4079 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4080 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4081 AND ev_match_status = 'N' THEN
4082 l_change_matrix := '1.17';
4083
4084 /*------------------------------------------------------+
4085 |1.18 - Current trx status = Complete |
4086 | Current Gl date = null = Existing Event Gl Date|
4087 | Existing Event Status = NULL |
4088 +------------------------------------------------------*/
4089 ELSIF trx_status = 'C'
4090 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4091 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4092 AND ev_match_status = 'X' THEN
4093 l_change_matrix := '1.18';
4094
4095 /*------------------------------------------------------+
4096 |1.19 - Current trx status = Complete |
4097 | Current Gl date <> Existing Event Gl Date |
4098 | Existing Event Status = NULL |
4099 +------------------------------------------------------*/
4100 ELSIF trx_status = 'C'
4101 AND dist_gl_date <> NVL(ev_match_gl_date ,TO_DATE('01-01-1900','DD-MM-YYYY'))
4102 AND ev_match_status = 'X' THEN
4103 l_change_matrix := '1.19';
4104
4105 /*------------------------------------------------------+
4106 |1.20 - Current trx status = Incomplete |
4107 | Current Gl date <> Existing Event Gl Date |
4108 | Existing Event Status = NULL |
4109 +------------------------------------------------------*/
4110 ELSIF trx_status = 'I'
4111 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
4112 AND ev_match_status = 'X' THEN
4113 l_change_matrix := '1.20';
4114
4115 /*------------------------------------------------------+
4116 |1.21 - Current trx status = Complete |
4117 | Current Gl date = Existing Event Gl Date |
4118 | Existing Event Status = Incomplete |
4119 +------------------------------------------------------*/
4120 ELSIF trx_status = 'C'
4121 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
4122 AND ev_match_status = 'I' THEN
4123 l_change_matrix := '1.21';
4124
4125 --{BUG#3320427
4126 /*----------------------------------------------------+
4127 | 1.23 - Current trx status = Incomplete |
4128 | Current CL date is NULL or (01-01-1900) |
4129 | Exist Event date <> NULL or (01-01-1900) |
4130 | Existing Event Status = Incomplete |
4131 +----------------------------------------------------*/
4132 ELSIF trx_status = 'I'
4133 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4134 AND ev_match_status = 'I'
4135 AND to_char(ev_match_gl_date,'DD-MM-YYYY') <> '01-01-1900'
4136 THEN
4137 l_change_matrix := '1.23';
4138 --}
4139
4140 END IF; --change matrix setup for non postable distributions
4141
4142 END IF; --distributions cannot be posted
4143
4144 RETURN(l_change_matrix);
4145
4146 log('ARP_XLA_EVENTS.Change_Matrix()-');
4147
4148 EXCEPTION
4149 WHEN OTHERS THEN
4150 log('EXCEPTION: ARP_XLA_EVENTS.dump_bld_rec');
4151 RAISE;
4152 END Change_Matrix;
4153
4154 /*========================================================================
4155 | PUBLIC FUNCTION dump_bld_rec
4156 |
4157 | DESCRIPTION
4158 | Dump build record
4159 |
4160 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4161 |
4162 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4163 |
4164 | PARAMETERS
4165 | p_bld_rec Build Record
4166 | p_i Index
4167 | p_tag table name tag
4168 |
4169 | KNOWN ISSUES
4170 |
4171 | NOTES
4172 |
4173 | MODIFICATION HISTORY
4174 | Date Author Description of Changes
4175 *=======================================================================*/
4176 PROCEDURE dump_bld_rec(p_bld_rec IN OUT NOCOPY bld_ev_type,
4177 p_i IN BINARY_INTEGER ,
4178 p_tag IN VARCHAR2 ) IS
4179 BEGIN
4180
4181 log('ARP_XLA_EVENTS.dump_bld_rec()+');
4182
4183 IF p_bld_rec.bld_dml_flag.EXISTS(p_i) THEN
4184
4185 log(p_tag ||'.bld_dml_flag('||p_i||') = '
4186 || p_bld_rec.bld_dml_flag(p_i));
4187 END IF;
4188
4189 log('ARP_XLA_EVENTS.dump_bld_rec()-');
4190 log(' ');
4191
4192 EXCEPTION
4193 WHEN OTHERS THEN
4194 log('EXCEPTION: ARP_XLA_EVENTS.dump_bld_rec');
4195 RAISE;
4196
4197 END dump_bld_rec;
4198
4199 /*========================================================================
4200 | PUBLIC PROCEDURE dump_event_info
4201 |
4202 | DESCRIPTION
4203 | Dump event info record
4204 |
4205 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4206 |
4207 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4208 |
4209 | PARAMETERS
4210 | p_ev_info_tab Build Record
4211 | p_i Index
4212 | p_tag table name tag
4213 |
4214 | KNOWN ISSUES
4215 |
4216 | NOTES
4217 |
4218 | MODIFICATION HISTORY
4219 | Date Author Description of Changes
4220 *=======================================================================*/
4221 PROCEDURE dump_event_info
4222 (p_ev_info_tab IN OUT NOCOPY xla_events_pub_pkg.t_array_entity_event_info_s,
4223 p_i IN BINARY_INTEGER ,
4224 p_tag IN VARCHAR2 ) IS
4225 BEGIN
4226
4227 log('ARP_XLA_EVENTS.dump_event_info()+');
4228
4229 IF p_ev_info_tab.EXISTS(p_i) THEN
4230
4231 log(p_tag ||'.p_ev_info_tab('||p_i||').event_id = '||
4232 p_ev_info_tab(p_i).event_id );
4233
4234 log(p_tag ||'.p_ev_info_tab('||p_i||').security_id_int_1 = '||
4235 TO_CHAR(p_ev_info_tab(p_i).security_id_int_1));
4236
4237 log(p_tag ||'.p_ev_info_tab('||p_i||').event_date = '||
4238 p_ev_info_tab(p_i).event_date );
4239
4240 log(p_tag ||'.p_ev_info_tab('||p_i||').event_type_code = '||
4241 p_ev_info_tab(p_i).event_type_code);
4242
4243 log(p_tag ||'.p_ev_info_tab('||p_i||').event_status_code = '||
4244 p_ev_info_tab(p_i).event_status_code);
4245
4246 log(p_tag ||'.p_ev_info_tab('||p_i||').transaction_number = '||
4247 p_ev_info_tab(p_i).transaction_number );
4248
4249 END IF;
4250
4251 log('ARP_XLA_EVENTS.dump_event_info()-');
4252 log(' ');
4253
4254 EXCEPTION
4255 WHEN OTHERS THEN
4256 log('EXCEPTION: ARP_XLA_EVENTS.dump_event_info');
4257 RAISE;
4258
4259 END dump_event_info;
4260
4261 /*========================================================================
4262 | PUBLIC FUNCTION delete_event
4263 |
4264 | DESCRIPTION
4265 | This procedure is a wrapper on the top of XLA delete_event API
4266 |
4267 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4268 |
4269 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4270 |
4271 | PARAMETERS
4272 | p_document_id document identifier
4273 | p_doc_table CT, APP, CMAPP, CRH, CR, ADJ, TRH
4274 | KNOWN ISSUES
4275 |
4276 | NOTES
4277 |
4278 | MODIFICATION HISTORY
4279 | Date Author Description of Changes
4280 | 14-JAN-2003 Herve Yu Create
4281 *=======================================================================*/
4282 PROCEDURE delete_event( p_document_id IN NUMBER,
4283 p_doc_table IN VARCHAR2)
4284 IS
4285 l_event_source_info xla_events_pub_pkg.t_event_source_info;
4286 l_security xla_events_pub_pkg.t_security;
4287 l_event_id NUMBER;
4288 /*7229913 */
4289 l_invoicing_rule_id NUMBER;
4290 l_document_id NUMBER;
4291
4292 /*7229913 */
4293 CURSOR c_ct_rules is
4294 select xe.event_id event_id from
4295 ra_customer_trx ct,
4296 xla_transaction_entities_upg xte,
4297 xla_events xe
4298 where ct.customer_trx_id = p_document_id
4299 and ct.invoicing_rule_id in (-2,-3)
4300 and ct.set_of_books_id = xte.ledger_id
4301 and nvl(xte.source_id_int_1,-99) = ct.customer_trx_id
4302 and xte.entity_code = 'TRANSACTIONS'
4303 and xte.application_id = 222
4304 and xte.entity_id = xe.entity_id
4305 and xe.application_id = 222
4306 and xe.event_status_code = 'I' ;
4307
4308 CURSOR c_ct IS
4309 SELECT event_id
4310 FROM ra_cust_trx_line_gl_dist
4311 WHERE customer_trx_id = p_document_id;
4312
4313 CURSOR c_app IS
4314 SELECT event_id
4315 FROM ar_receivable_applications
4316 WHERE receivable_application_id = p_document_id;
4317
4318 CURSOR c_adj IS
4319 SELECT event_id
4320 FROM ar_adjustments
4321 WHERE adjustment_id = p_document_id;
4322
4323 CURSOR c_crh IS
4324 SELECT event_id
4325 FROM ar_cash_receipt_history
4326 WHERE cash_receipt_id = p_document_id;
4327
4328 CURSOR c_trh IS
4329 SELECT event_id, customer_trx_id
4330 FROM ar_transaction_history
4331 WHERE transaction_history_id = p_document_id;
4332
4333 BEGIN
4334 log('arp_xla_events.delete_event ()+');
4335
4336 IF p_doc_table = 'CT' THEN
4337 OPEN c_ct;
4338 FETCH c_ct INTO l_event_id;
4339 CLOSE c_ct;
4340
4341 /*7229913*/
4342 select invoicing_rule_id into l_invoicing_rule_id from ra_customer_trx
4343 where customer_trx_id = p_document_id;
4344
4345 IF l_invoicing_rule_id in (-2,-3) then
4346 OPEN c_ct_rules ;
4347 FETCH c_ct_rules INTO l_event_id;
4348 CLOSE c_ct_rules;
4349 END IF;
4350
4351 ELSIF p_doc_table = 'ADJ' THEN
4352 OPEN c_adj;
4353 FETCH c_adj INTO l_event_id;
4354 CLOSE c_adj;
4355 ELSIF p_doc_table IN ('APP','CMAPP') THEN
4356 OPEN c_app;
4357 FETCH c_app INTO l_event_id;
4358 CLOSE c_app;
4359 ELSIF p_doc_table IN ('CR','CRH') THEN
4360 OPEN c_crh;
4361 FETCH c_crh INTO l_event_id;
4362 CLOSE c_crh;
4363 ELSIF p_doc_table = 'TRH' THEN
4364 OPEN c_trh;
4365 FETCH c_trh INTO l_event_id, l_document_id;
4366 CLOSE c_trh;
4367 END IF;
4368
4369 IF l_event_id IS NOT NULL THEN
4370 l_event_source_info.entity_type_code:= entity_code(p_doc_table => p_doc_table);
4371 l_security.security_id_int_1 := arp_global.sysparam.org_id;
4372 l_event_source_info.application_id := 222;
4373 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
4374 l_event_source_info.source_id_int_1 := NVL(l_document_id, p_document_id);
4375
4376 xla_events_pub_pkg.delete_event
4377 ( p_event_source_info => l_event_source_info,
4378 p_event_id => l_event_id,
4379 p_valuation_method => NULL,
4380 p_security_context => l_security);
4381 END IF;
4382
4383 log('arp_xla_events.delete_event ()-');
4384 EXCEPTION
4385 WHEN OTHERS THEN
4386 log('EXCEPTION: arp_xla_events.delete_event'||SQLERRM);
4387 RAISE;
4388 END delete_event;
4389
4390
4391 /*========================================================================
4392 | PUBLIC FUNCTION delete_reverse_revrec_event
4393 |
4394 | DESCRIPTION
4395 | This procedure is a wrapper on the top of XLA delete_event API.
4396 | This procedure is used to delete the events from xla_events
4397 | other than the REC event when a transaction with rule is incompleted.
4398 |
4399 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4400 |
4401 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4402 |
4403 | PARAMETERS
4404 | p_document_id document identifier
4405 | p_doc_table CT, APP, CMAPP, CRH, CR, ADJ, TRH
4406 | KNOWN ISSUES
4407 |
4408 | NOTES
4409 |
4410 | MODIFICATION HISTORY
4411 | Date Author Description of Changes
4412 | 07-Mar-2008 Bharani Suri Create
4413 *=======================================================================*/
4414
4415
4416 PROCEDURE delete_reverse_revrec_event( p_document_id IN NUMBER,
4417 p_doc_table IN VARCHAR2)
4418 IS
4419
4420 l_event_source_info xla_events_pub_pkg.t_event_source_info;
4421 l_security xla_events_pub_pkg.t_security;
4422 l_event_id NUMBER;
4423
4424
4425 CURSOR c_ct IS
4426 SELECT distinct event_id event_id
4427 FROM ra_cust_trx_line_gl_dist gld
4428 WHERE customer_trx_id = p_document_id
4429 and account_set_flag = 'N'
4430 AND event_id is not null
4431 and EXISTS
4432 ( select 'x' FROM ra_cust_trx_line_gl_dist gldin
4433 WHERE customer_trx_id = p_document_id
4434 and account_class='REC'
4435 and latest_rec_flag='Y'
4436 AND event_id IS NOT NULL
4437 AND event_id <> gld.event_id
4438 );
4439
4440
4441 BEGIN
4442 log('arp_xla_events.delete_reverse_revrec_event ()+');
4443
4444 FOR c IN c_ct loop
4445
4446 l_event_id := c.event_id;
4447
4448 l_event_source_info.entity_type_code:= entity_code(p_doc_table => p_doc_table);
4449 l_security.security_id_int_1 := arp_global.sysparam.org_id;
4450 l_event_source_info.application_id := 222;
4451 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
4452 l_event_source_info.source_id_int_1 := p_document_id;
4453
4454 xla_events_pub_pkg.delete_event
4455 ( p_event_source_info => l_event_source_info,
4456 p_event_id => l_event_id,
4457 p_valuation_method => NULL,
4458 p_security_context => l_security);
4459
4460 END loop;
4461
4462 log('arp_xla_events.delete_reverse_revrec_event ()-');
4463 EXCEPTION
4464 WHEN OTHERS THEN
4465 log('EXCEPTION: arp_xla_events.delete_reverse_revrec_event'||SQLERRM);
4466 RAISE;
4467 END delete_reverse_revrec_event;
4468
4469
4470 /*========================================================================
4471 | PUBLIC PROCEDURE ar_xla_period_close
4472 |
4473 | DESCRIPTION
4474 | Procedure to check any event records in XLA such that either
4475 | headers are not transferred to GL, or events are Invalid
4476 | or events are Incomplete and they do not belong to any Incomplete
4477 | transactions in AR. In all such cases, user will not be allowed to
4478 | close the AR Period.
4479 | The exception here is if there are Incomplete transactions with
4480 | Incomplete Events in XLA, user will be allowed to close the period
4481 | and a warning notification will be shown to the user.
4482 |
4483 | CALLED FROM PROCEDURES/FUNCTIONS
4484 |
4485 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4486 |
4487 | PARAMETERS
4488 | 1) p_application_id IN NUMBER -- default 222
4489 | 2) p_ledger_id IN NUMBER
4490 | 3) p_period_name IN VARCHAR2
4491 | 4) p_cannot_close_period OUT BOOLEAN
4492 | 5) p_incomplete_events OUT BOOLEAN
4493 |
4494 | KNOWN ISSUES
4495 |
4496 | NOTES
4497 |
4498 | MODIFICATION HISTORY
4499 | Date Author Description of Changes
4500 | 23-JAN-2009 Anshu Kaushal Created
4501 *=======================================================================*/
4502
4503
4504 PROCEDURE ar_xla_period_close (p_application_id NUMBER DEFAULT 222,
4505 p_ledger_id NUMBER,
4506 p_period_name VARCHAR2,
4507 p_cannot_close_period OUT NOCOPY BOOLEAN ,
4508 p_incomplete_events OUT NOCOPY BOOLEAN )
4509 IS
4510
4511 -- Declare a record of XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_header%ROWTYPE as we need to fetch a single record
4512 ar_xla_period_close_header_rec XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_header%ROWTYPE;
4513
4514 -- Declare a pl/sql table of XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_evt%ROWTYPE as we need to loop through the data
4515 TYPE ar_xla_period_close_evt_tab IS TABLE OF XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_evt%ROWTYPE
4516 INDEX BY BINARY_INTEGER;
4517 l_ar_xla_period_close_evt_tab ar_xla_period_close_evt_tab;
4518
4519 i NUMBER := 0;
4520 l_trx_cnt Number := 0;
4521
4522 BEGIN
4523 log('ARP_XLA_EVENTS.ar_xla_period_close ()+');
4524
4525 p_cannot_close_period := FALSE;
4526 p_incomplete_events := FALSE;
4527
4528 -- If application security context is not set, then xla cursors will not give data
4529 xla_security_pkg.set_security_context(p_application_id);
4530
4531 -- If there are any headers in XLA which are not transferred to GL, user can not close the period
4532 OPEN xla_period_close_exp_pkg.period_close_cur_header(p_application_id,p_ledger_id,p_period_name);
4533 FETCH xla_period_close_exp_pkg.period_close_cur_header INTO ar_xla_period_close_header_rec;
4534
4535
4536 IF xla_period_close_exp_pkg.period_close_cur_header%FOUND THEN
4537 p_cannot_close_period := TRUE;
4538 RETURN;
4539 END IF;
4540 CLOSE xla_period_close_exp_pkg.period_close_cur_header;
4541 /* Bug 14325948 change the order of fetch and loop to correct the looping and fetching of records */
4542 OPEN XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_evt(p_application_id,p_ledger_id,p_period_name);
4543 LOOP
4544 FETCH XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_evt BULK COLLECT INTO l_ar_xla_period_close_evt_tab LIMIT MAX_ARRAY_SIZE;
4545 -- LOOP
4546 IF l_ar_xla_period_close_evt_tab.count = 0 THEN
4547 EXIT;
4548 END IF;
4549
4550 FOR i IN l_ar_xla_period_close_evt_tab.FIRST..l_ar_xla_period_close_evt_tab.LAST
4551 LOOP
4552 /*If there are Incomplete Events associated to Incomplete Transactions, user should be allowed to
4553 close the period with a warning issued. */
4554 IF l_ar_xla_period_close_evt_tab(i).EVENT_STATUS_CODE = 'I'
4555 AND l_ar_xla_period_close_evt_tab(i).PROCESS_STATUS_CODE = 'U'
4556 AND l_ar_xla_period_close_evt_tab(i).ENTITY_CODE IN ('TRANSACTIONS', 'BILLS_RECEIVABLE') THEN
4557 BEGIN
4558 SELECT
4559 count(*) into l_trx_cnt
4560 FROM ra_customer_trx_all
4561 WHERE customer_trx_id = l_ar_xla_period_close_evt_tab(i).SOURCE_ID_INT_1 /* The trx id stored in XLA entities table */
4562 AND org_id = l_ar_xla_period_close_evt_tab(i).SECURITY_ID_INT_1 /* The Org_ID stored in XLA entities table */
4563 AND complete_flag = 'N'; /* Incomplete Transaction */
4564
4565 IF l_trx_cnt = 1 THEN
4566 p_cannot_close_period := FALSE;
4567 p_incomplete_events := TRUE;
4568 ELSE
4569 p_cannot_close_period := TRUE; /* Since the invoice is either not incomplete or the event is orphaned in XLA */
4570 EXIT; /* Exit the loop altogeather */
4571 END IF;
4572
4573 EXCEPTION
4574 WHEN OTHERS THEN
4575 log('OTHERS EXCEPTION: ARP_XLA_EVENTS.ar_xla_period_close');
4576 RAISE;
4577 END;
4578
4579 ELSE /* Some other period exception raised by XLA hence period cannot be closed*/
4580 p_cannot_close_period := TRUE;
4581 EXIT;
4582 END IF;
4583
4584 END LOOP;
4585 IF l_ar_xla_period_close_evt_tab.count < MAX_ARRAY_SIZE THEN
4586 EXIT;
4587 END IF;
4588
4589 END LOOP;
4590 CLOSE XLA_PERIOD_CLOSE_EXP_PKG.period_close_cur_evt;
4591
4592 log('ARP_XLA_EVENTS.ar_xla_period_close ()-');
4593 EXCEPTION
4594 WHEN OTHERS THEN
4595 log('OTHERS EXCEPTION: ARP_XLA_EVENTS.ar_xla_period_close');
4596 RAISE;
4597 END ar_xla_period_close;
4598
4599
4600 FUNCTION entity_code( p_doc_table IN VARCHAR2)
4601 RETURN VARCHAR2
4602 IS
4603 l_entity_code VARCHAR2(20);
4604 BEGIN
4605 IF p_doc_table IN ('CT','CMAPP', 'CTCMAPP','CTNORCM') THEN
4606 l_entity_code := 'TRANSACTIONS';
4607
4608 ELSIF p_doc_table IN ('CRH','APP','CRHMCD','CRHAPP') THEN
4609 l_entity_code := 'RECEIPTS';
4610
4611 ELSIF p_doc_table = 'ADJ' THEN
4612 l_entity_code := 'ADJUSTMENTS';
4613
4614 ELSIF p_doc_table = 'TRH' THEN
4615 l_entity_code := 'BILLS_RECEIVABLE';
4616 END IF;
4617 RETURN l_entity_code;
4618 END entity_code;
4619
4620 /*
4621 PROCEDURE auto_invoice_events
4622 (p_request_id IN NUMBER,
4623 p_code IN VARCHAR2)
4624 IS
4625 BEGIN
4626 IF p_code = 'CTADJ' THEN
4627 -- arp_xla_events.create_events(p_request_id, 'CT');
4628 -- arp_xla_events.create_events(p_request_id, 'ADJ');
4629 ELSE
4630 -- arp_xla_events.create_events(p_request_id, 'CTCMAPP');
4631 END IF;
4632
4633 IF p_code = 'CTADJ' THEN
4634 INSERT INTO RA_INTERFACE_ERRORS
4635 ( interface_line_id,
4636 message_text,
4637 org_id )
4638 select l.interface_line_id,
4639 xgt.error_msg,
4640 l.org_id
4641 from ra_interface_lines_gt l,
4642 xla_events_gt xgt
4643 where l.request_id = p_request_id
4644 and l.customer_trx_id = xgt.source_id_int_1
4645 and l.event_id = -9999
4646 and xgt.event_class_code in ('INV_CREATE','DM_CREATE','CM_CREATE','ADJ_CREATE');
4647 ELSE
4648 INSERT INTO RA_INTERFACE_ERRORS
4649 ( interface_line_id,
4650 message_text,
4651 org_id )
4652 select l.interface_line_id,
4653 xgt.error_msg,
4654 l.org_id
4655 from ra_interface_lines_gt l,
4656 xla_events_gt xgt
4657 where l.request_id = p_request_id
4658 and l.customer_trx_id = xgt.source_id_int_1
4659 and l.event_id = -9999
4660 and xgt.event_class_code in ('CM_CREATE')
4661 and l.interface_line_id NOT IN (SELECT interface_line_id FROM RA_INTERFACE_ERRORS);
4662 END IF;
4663 END;
4664 */
4665
4666 END ARP_XLA_EVENTS;