[Home] [Help]
PACKAGE BODY: APPS.ARP_XLA_EVENTS
Source
1 PACKAGE BODY ARP_XLA_EVENTS AS
2 /* $Header: ARXLAEVB.pls 120.56.12010000.8 2008/12/30 08:52:41 nproddut 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 decode(crh.posting_control_id,
1125 -3, ev1.event_status_code,
1126 ''P'') = ev1.event_status_code '||CRLF;
1127
1128 l_group_by_clause := '';
1129
1130 l_order_by_clause := '';
1131
1132 log('End Building Denormalize Cash Receipt History statement ' );
1133
1134 END IF; --create or update mode
1135
1136 END IF; --transaction
1137
1138 -------------------------------------------------------------------
1139 -- Build statement for Misc Cash Distributions events creation
1140 -------------------------------------------------------------------
1141 IF p_xla_ev_rec.xla_doc_table IN ('MCD', 'CRHMCD') THEN
1142
1143 IF p_xla_ev_rec.xla_call IN ('C', 'B')
1144 AND p_call_point = 1 AND p_xla_ev_rec.xla_doc_table = 'CRHMCD' THEN
1145
1146 l_where_parm_clause_crh := l_where_parm_clause;
1147 l_where_parm_clause := '';
1148
1149 END IF;
1150
1151 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
1152 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
1153
1154
1155 --{BUG#5561163
1156 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
1157 l_where_parm_clause :=
1158 ' AND mcd.cash_receipt_id = :b_xla_from_doc_id
1159 AND mcd.cash_receipt_id = :b_xla_to_doc_id ' || CRLF;
1160 ELSE
1161 l_where_parm_clause :=
1162 ' AND mcd.cash_receipt_id >= :b_xla_from_doc_id
1163 AND mcd.cash_receipt_id <= :b_xla_to_doc_id ' || CRLF;
1164 END IF;
1165 --}
1166 END IF;
1167
1168 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
1169 l_where_parm_clause := l_where_parm_clause
1170 || ' AND mcd.request_id = :b_xla_req_id ' || CRLF;
1171 END IF;
1172
1173 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
1174 l_where_parm_clause := l_where_parm_clause
1175 || ' AND mcd.misc_cash_distribution_id = :b_xla_dist_id ' || CRLF;
1176 END IF;
1177
1178 ------------------------------------------------------------------
1179 -- Build the clause for Misc cash receipt create events stmt
1180 -- In Reality there should be a Union between APP and CRH
1181 --Note for CRH for APPROVED status 1952 has been seeded as the
1182 --gl date, this is defaulted to 1900 to enable change matrix
1183 --processing, in the event record a null gl date will exist
1184 ------------------------------------------------------------------
1185 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1186
1187 IF p_xla_ev_rec.xla_doc_table = 'CRHMCD' THEN
1188 l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
1189 l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
1190 END IF;
1191
1192 --HYU transaction_date,legal_entity_id
1193 l_select_clause := l_select_clause ||
1194 ' select
1195 ''Y'' POSTTOGL ,
1196 ''RECP'' TRX_TYPE ,
1197 ''C'' COMP_FLAG ,
1198 mcd.cash_receipt_id TRX_ID ,
1199 cr.receipt_number TRX_NUMBER ,
1200 cr.org_id ORG_ID,
1201 decode(mcd.created_from,
1202 ''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
1203 decode(SUBSTRB(mcd.created_from,1,19),
1204 ''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
1205 decode(nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1206 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1207 decode(crh.posting_control_id,
1208 mcd.posting_control_id, ''MISC_RECP_CREATE'',
1209 ''MISC_RECP_UPDATE''),
1210 ''MISC_RECP_UPDATE''))) OVERRIDE_EVENT,
1211 mcd.posting_control_id PSTID ,
1212 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
1213 mcd.event_id EXIST_EVENT,
1214 '''' EVENT_ID ,
1215 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1216 '''' EVENT_TYPE ,
1217 ''X'' EVENT_STATUS,
1218 cr.receipt_date TRANSACTION_DATE,
1219 cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
1220
1221 log('l_select_clause ' || l_select_clause);
1222
1223 l_from_clause :=
1224 ' FROM ar_misc_cash_distributions' || l_all_clause || ' mcd, ' || CRLF ||
1225 ' ar_cash_receipts' || l_all_clause || ' cr, ' || CRLF ||
1226 ' ar_cash_receipt_history' || l_all_clause || ' crh ' || CRLF;
1227
1228 log('l_from_clause ' || l_from_clause);
1229
1230 l_where_clause := ' WHERE 1 = 1 ' || CRLF ||
1231 ' AND mcd.cash_receipt_id = cr.cash_receipt_id ' || CRLF ||
1232 ' AND mcd.cash_receipt_id = crh.cash_receipt_id '|| CRLF ||
1233 ' AND crh.first_posted_record_flag = ''Y'' ' || CRLF;
1234
1235 l_group_by_clause :=
1236 ' GROUP BY mcd.cash_receipt_id,
1237 cr.receipt_number,
1238 cr.org_id,
1239 mcd.posting_control_id,
1240 decode(mcd.created_from,
1241 ''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
1242 decode(SUBSTRB(mcd.created_from,1,19),
1243 ''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
1244 decode(nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1245 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1246 decode(crh.posting_control_id,
1247 mcd.posting_control_id, ''MISC_RECP_CREATE'',
1248 ''MISC_RECP_UPDATE''),
1249 ''MISC_RECP_UPDATE''))),
1250 nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1251 mcd.event_id ,
1252 cr.receipt_date,
1253 cr.legal_entity_id ' || CRLF;
1254
1255 IF p_xla_ev_rec.xla_doc_table = 'MCD' THEN
1256
1257 l_order_by_clause :=
1258 'ORDER BY TRX_ID,
1259 decode(OVERRIDE_EVENT,
1260 ''MISC_RECP_CREATE'' ,1,
1261 ''MISC_RECP_UPDATE'' ,2,
1262 ''MISC_RECP_RATE_ADJUST'',3,
1263 ''MISC_RECP_REVERSE'' ,6,
1264 7),
1265 GL_DATE,
1266 PSTID desc ';
1267 END IF;
1268
1269 log('l_select_clause = ' || l_select_clause);
1270 log('l_where_clause = ' || l_where_clause);
1271 log('l_group_by_clause = ' || l_group_by_clause);
1272 log('l_order_by_clause = ' || l_order_by_clause);
1273
1274 ------------------------------------------------------------------
1275 -- Build the clause for Misc Cash Receipts denormalize events stmt
1276 ------------------------------------------------------------------
1277 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1278
1279 log('Building Denormalize Misc Cash Distributions statement ' );
1280 /* Bug 6747662 JVARKEY Modified to check for the rate adjustment event
1281 from the last record of CRH rather than MCD. The reasom being if the
1282 receipt is cleared with different rate then a new event won't be
1283 created but MCD will say creted from Rate Adjustment. So we should
1284 check created from from CRH */
1285 l_select_clause :=
1286 ' SELECT mcd.rowid,
1287 ev1.event_id ';
1288
1289 l_from_clause :=
1290 ' FROM xla_events' || ' ev1, ' || CRLF ||
1291 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1292 ' ar_cash_receipt_history' || l_all_clause || ' crh, ' || CRLF ||
1293 ' ar_misc_cash_distributions' || l_all_clause || ' mcd ' || CRLF ;
1294
1295 l_where_clause :=
1296 ' WHERE decode(crh.created_from,
1297 ''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
1298 decode(SUBSTRB(mcd.created_from,1,19),
1299 ''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
1300 ev1.event_type_code)) = ev1.event_type_code
1301 AND crh.cash_receipt_id = mcd.cash_receipt_id
1302 AND crh.current_record_flag = ''Y''
1303 AND mcd.event_id IS NULL
1304 AND ev1.entity_id = evn.entity_id
1305 AND evn.application_id = 222
1306 AND ev1.application_id = 222
1307 AND evn.entity_code = ''RECEIPTS''
1308 AND mcd.cash_receipt_id = NVL(evn.source_id_int_1,-99)
1309 AND evn.ledger_id = mcd.set_of_books_id
1310 AND nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
1311 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1312 AND mcd.posting_control_id = nvl(ev1.reference_num_1,-3)
1313 AND decode(mcd.posting_control_id,
1314 -3, ev1.event_status_code,
1315 ''P'') = ev1.event_status_code' || CRLF;
1316
1317 l_group_by_clause := '';
1318
1319 l_order_by_clause := '';
1320
1321 log('End Building Denormalize Misc Cash Distributions statement ' );
1322 END IF; --create or update mode
1323
1324 END IF; --create and denormalize events Misc Cash Receipts
1325
1326 -------------------------------------------------------------------
1327 -- Build parameter clause for Applications Receipts and CM
1328 -------------------------------------------------------------------
1329 IF p_xla_ev_rec.xla_doc_table IN ('APP','CRHAPP','CMAPP','CTCMAPP') THEN
1330
1331 IF p_xla_ev_rec.xla_call IN ('C', 'B')
1332 AND p_call_point = 1 AND p_xla_ev_rec.xla_doc_table IN ('CRHAPP','CTCMAPP') THEN
1333
1334 l_where_parm_clause_crh := l_where_parm_clause;
1335 l_where_parm_clause := '';
1336
1337 END IF;
1338
1339 -- bug 5965006
1340 IF p_xla_ev_rec.xla_doc_table IN ('CTCMAPP') AND p_call_point = 2 AND g_call_number = 1 THEN
1341 l_where_parm_clause := '';
1342 END IF;
1343
1344 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
1345 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
1346
1347 --{BUG#5561163
1348 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
1349 l_where_parm_clause :=
1350 ' AND app.receivable_application_id = :b_xla_from_doc_id
1351 AND app.receivable_application_id = :b_xla_to_doc_id ' || CRLF;
1352 ELSE
1353 l_where_parm_clause :=
1354 ' AND app.receivable_application_id >= :b_xla_from_doc_id
1355 AND app.receivable_application_id <= :b_xla_to_doc_id ' || CRLF;
1356 END IF;
1357 --}
1358 END IF;
1359 -- bug 5965006
1360 IF p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND p_call_point = 2 AND g_call_number = 2 THEN
1361 NULL;
1362 ELSE
1363 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
1364 l_where_parm_clause := l_where_parm_clause
1365 || ' AND app.request_id = :b_xla_req_id ' || CRLF;
1366 END IF;
1367 END IF;
1368 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
1369 l_where_parm_clause := l_where_parm_clause
1370 || ' AND app.receivable_application_id = :b_xla_dist_id ' || CRLF;
1371 END IF;
1372
1373 END IF; --App and CM app parameter construction section
1374
1375 -------------------------------------------------------------------
1376 -- Build statement for Receivable Applications events creation
1377 -------------------------------------------------------------------
1378 IF p_xla_ev_rec.xla_doc_table IN ('APP', 'CRHAPP') THEN
1379
1380 ------------------------------------------------------------------
1381 -- Build the clause for Misc cash receipt create events stmt
1382 -- In Reality there should be a Union between APP and CRH
1383 --Note for CRH for APPROVED status 1952 has been seeded as the
1384 --gl date, this is defaulted to 1900 to enable change matrix
1385 --processing, in the event record a null gl date will exist
1386 --
1387 --Resolved Issues
1388 -----------------
1389 --I -It is not possible to accurately identify the
1390 --Unapply receipt application event, because positive and negative
1391 --application amounts can be created in Receivables to bump up
1392 --or down the from document (Receipt) and two document balcnce,
1393 --
1394 --II - A Receipt application reversed due to Receipt reversal is tagged
1395 --under the Reverse Receipt event as against the traditionaly event
1396 --apply receipt.
1397 --
1398 ------------------------------------------------------------------------
1399 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1400
1401 IF p_xla_ev_rec.xla_doc_table = 'CRHAPP' THEN
1402 l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
1403 l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
1404 END IF;
1405
1406
1407 l_select_clause := l_select_clause ||
1408 ' select
1409 decode(app.postable,
1410 ''N'',''N'',
1411 ''Y'') POSTTOGL ,
1412 ''RECP'' TRX_TYPE ,
1413 decode(NVL(app.confirmed_flag,''Y''),
1414 ''Y'',''C'',
1415 ''N'') COMP_FLAG ,
1416 cr.cash_receipt_id TRX_ID , --BUG#3554871
1417 cr.receipt_number TRX_NUMBER ,
1418 cr.org_id ORG_ID ,
1419 decode(crh.created_from,
1420 ''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
1421 decode(crh.status,
1422 ''REVERSED'',''RECP_REVERSE'',
1423 decode(crh1.first_posted_record_flag,
1424 '''', ''RECP_CREATE'',
1425 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1426 nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1427 decode(app.posting_control_id,
1428 crh1.posting_control_id, ''RECP_CREATE'',
1429 ''RECP_UPDATE''),
1430 ''RECP_UPDATE'')))) OVERRIDE_EVENT,
1431 app.posting_control_id PSTID ,
1432 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
1433 app.event_id EXIST_EVENT ,
1434 '''' EVENT_ID ,
1435 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1436 '''' EVENT_TYPE ,
1437 ''X'' EVENT_STATUS ,
1438 cr.receipt_date TRANSACTION_DATE,
1439 cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
1440
1441 l_from_clause :=
1442 ' FROM ar_receivable_applications' || l_all_clause || ' app, ' || CRLF ||
1443 ' ar_cash_receipt_history' || l_all_clause || ' crh, ' || CRLF ||
1444 ' ar_cash_receipt_history' || l_all_clause || ' crh1, ' || CRLF ||
1445 ' ar_cash_receipts' || l_all_clause || ' cr ' || CRLF ;
1446
1447 --{
1448 -- The join to AND app.status IN (''APP'',''ACTIVITY'',''OTHER ACC'',''ACC'',''UNID'')
1449 -- has been removed in the denormalisation for APPLICATION
1450 --}
1451 l_where_clause :=
1452 ' WHERE app.application_type = ''CASH''
1453 AND app.cash_receipt_history_id = crh.cash_receipt_history_id
1454 AND app.cash_receipt_id = cr.cash_receipt_id
1455 AND cr.cash_receipt_id = crh1.cash_receipt_id (+)
1456 AND ''Y'' = crh1.first_posted_record_flag (+)
1457 AND decode(app.event_id,
1458 '''', ''Y'',
1459 decode(:b_xla_mode, ''O'',''Y'',
1460 ''N'')) = ''Y'' '|| CRLF ;
1461
1462 l_group_by_clause :=
1463 ' GROUP BY
1464 cr.cash_receipt_id,
1465 cr.receipt_number,
1466 cr.org_id,
1467 decode(NVL(app.confirmed_flag,''Y''),
1468 ''Y'',''C'',
1469 ''N''),
1470 app.posting_control_id,
1471 decode(app.postable,
1472 ''N'',''N'',
1473 ''Y''),
1474 decode(crh.created_from,
1475 ''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
1476 decode(crh.status,
1477 ''REVERSED'',''RECP_REVERSE'',
1478 decode(crh1.first_posted_record_flag,
1479 '''', ''RECP_CREATE'',
1480 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1481 nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1482 decode(app.posting_control_id,
1483 crh1.posting_control_id, ''RECP_CREATE'',
1484 ''RECP_UPDATE''),
1485 ''RECP_UPDATE'')))),
1486 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1487 app.event_id ,
1488 cr.receipt_date,
1489 cr.legal_entity_id' || CRLF;
1490
1491 IF p_xla_ev_rec.xla_doc_table = 'APP' THEN
1492
1493 l_order_by_clause :=
1494 'ORDER BY TRX_ID,
1495 decode(OVERRIDE_EVENT,
1496 ''RECP_CREATE'' ,1,
1497 ''RECP_UPDATE'' ,2,
1498 ''RECP_RATE_ADJUST'',3,
1499 ''RECP_REVERSE'' ,6,
1500 7),
1501 GL_DATE,
1502 PSTID desc ';
1503 END IF;
1504
1505 --------------------------------------------------------------------
1506 -- Build the clause for Receipts applications denormalize event stmt
1507 --------------------------------------------------------------------
1508 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1509
1510 log('Building Denormalize Receivable Apps statement ' );
1511
1512 l_select_clause :=
1513 ' SELECT app.rowid,
1514 ev1.event_id ';
1515
1516 log('l_select_clause ' || l_select_clause);
1517
1518 l_from_clause :=
1519 ' FROM xla_events' || ' ev1, ' || CRLF ||
1520 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1521 ' ar_receivable_applications' || l_all_clause || ' app, ' || CRLF ||
1522 ' ar_cash_receipt_history' || l_all_clause || ' crh ' || CRLF ;
1523
1524 log('l_from_clause ' || l_from_clause);
1525 --{
1526 -- The join to AND app.status IN (''APP'',''ACTIVITY'',''OTHER ACC'',''ACC'', ''UNID'')
1527 -- has been removed in the denormalisation for APPLICATION
1528 -- Use xla_transaction_entity_n1
1529 --}
1530 l_where_clause :=
1531 ' WHERE app.application_type = ''CASH''
1532 AND app.event_id IS NULL
1533 AND app.cash_receipt_history_id = crh.cash_receipt_history_id
1534 AND decode(crh.created_from,
1535 ''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
1536 decode(crh.status,
1537 ''REVERSED'', ''RECP_REVERSE'',
1538 ev1.event_type_code)) = ev1.event_type_code
1539 AND app.posting_control_id = nvl(ev1.reference_num_1,-3)
1540 AND evn.entity_code = ''RECEIPTS''
1541 AND ev1.entity_id = evn.entity_id
1542 AND evn.application_id = 222
1543 AND ev1.application_id = 222
1544 AND app.cash_receipt_id = NVL(evn.source_id_int_1,-99)
1545 AND evn.ledger_id = app.set_of_books_id
1546 AND nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
1547 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1548 AND decode(app.posting_control_id,
1549 -3, ev1.event_status_code,
1550 ''P'') = ev1.event_status_code ' || CRLF;
1551
1552
1553
1554 log('l_where_clause' || l_from_clause);
1555
1556 l_group_by_clause := '';
1557
1558 l_order_by_clause := '';
1559
1560 log('End Building Denormalize Receivable Apps statement ' );
1561
1562 END IF; --create or update mode
1563
1564 END IF; --create and denormalize events Receivable applications
1565
1566 -------------------------------------------------------------------
1567 -- Build statement for Receivable Applications events creation
1568 -------------------------------------------------------------------
1569 IF p_xla_ev_rec.xla_doc_table IN ('CMAPP', 'CTCMAPP') THEN
1570
1571 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1572
1573 IF p_xla_ev_rec.xla_doc_table = 'CTCMAPP' THEN
1574 l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
1575 l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
1576 END IF;
1577
1578 l_select_clause := l_select_clause ||
1579 ' select
1580 decode(app.postable,
1581 ''N'',''N'',
1582 ''Y'') POSTTOGL ,
1583 ''CM'' TRX_TYPE ,
1584 decode(NVL(app.confirmed_flag,''Y''),
1585 ''Y'',''C'',
1586 ''N'') COMP_FLAG ,
1587 ctlgd.customer_trx_id TRX_ID, --BUG#3554871
1588 ct.trx_number TRX_NUMBER ,
1589 app.org_id ORG_ID ,
1590 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1591 nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1592 decode(app.posting_control_id,
1593 ctlgd.posting_control_id, ''CM_CREATE'',
1594 ''CM_UPDATE''),
1595 ''CM_UPDATE'') OVERRIDE_EVENT ,
1596 app.posting_control_id PSTID ,
1597 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
1598 app.event_id EXIST_EVENT ,
1599 '''' EVENT_ID ,
1600 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1601 '''' EVENT_TYPE ,
1602 ''X'' EVENT_STATUS ,
1603 ct.trx_date TRANSACTION_DATE,
1604 ct.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
1605
1606 l_from_clause :=
1607 ' FROM ar_receivable_applications' || l_all_clause || ' app, ' ||
1608 ' ra_cust_trx_line_gl_dist' || l_all_clause || ' ctlgd, ' ||
1609 ' ra_customer_trx' || l_all_clause || ' ct ' || CRLF ;
1610
1611 l_where_clause :=
1612 ' WHERE app.application_type = ''CM''
1613 AND app.status IN (''APP'',''ACTIVITY'') --HYU
1614 AND ctlgd.customer_trx_id = app.customer_trx_id
1615 AND ctlgd.latest_rec_flag = ''Y''
1616 AND ctlgd.customer_trx_id = ct.customer_trx_id
1617 AND decode(app.event_id,
1618 '''', ''Y'',
1619 decode(:b_xla_mode, ''O'',''Y'',
1620 ''N'')) = ''Y'' '|| CRLF;
1621
1622 l_group_by_clause :=
1623 ' GROUP BY ctlgd.customer_trx_id,
1624 ct.trx_number,
1625 app.org_id,
1626 decode(NVL(app.confirmed_flag,''Y''),
1627 ''Y'',''C'',
1628 ''N''),
1629 app.posting_control_id,
1630 decode(app.postable,
1631 ''N'',''N'',
1632 ''Y''),
1633 decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1634 nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1635 decode(app.posting_control_id,
1636 ctlgd.posting_control_id, ''CM_CREATE'',
1637 ''CM_UPDATE''),
1638 ''CM_UPDATE''),
1639 nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1640 app.event_id,
1641 ct.trx_date,
1642 ct.legal_entity_id ' || CRLF;
1643
1644 IF p_xla_ev_rec.xla_doc_table = 'CMAPP' THEN
1645
1646 l_order_by_clause :=
1647 'ORDER BY TRX_ID,
1648 OVERRIDE_EVENT,
1649 GL_DATE,
1650 PSTID desc ';
1651
1652 END IF;
1653
1654 ------------------------------------------------------------------
1655 -- Build the clause for CM Applications denormalize events stmt
1656 ------------------------------------------------------------------
1657 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1658 -- bug 5965006
1659 IF p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND g_call_number = 2 THEN
1660 NULL;
1661 ELSE
1662 l_select_clause :=
1663 ' SELECT app.rowid,
1664 ev1.event_id ';
1665
1666 l_from_clause :=
1667 ' FROM xla_events' || ' ev1, ' || CRLF ||
1668 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1669 ' ar_receivable_applications' || l_all_clause || ' app ' || CRLF ;
1670
1671
1672 --Use xla_transaction_entity_n1
1673 l_where_clause :=
1674 ' WHERE app.application_type = ''CM''
1675 AND app.event_id IS NULL
1676 AND app.status IN (''APP'',''ACTIVITY'') --HYU
1677 AND ev1.entity_id = evn.entity_id
1678 AND evn.application_id = 222
1679 AND ev1.application_id = 222
1680 AND app.customer_trx_id = NVL(evn.source_id_int_1,-99)
1681 AND evn.ledger_id = app.set_of_books_id
1682 AND evn.entity_code = ''TRANSACTIONS''
1683 AND app.posting_control_id = nvl(ev1.reference_num_1,-3)
1684 AND nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))
1685 = nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1686 AND decode(app.posting_control_id,
1687 -3, ev1.event_status_code,
1688 ''P'') = ev1.event_status_code ' || CRLF;
1689
1690
1691 l_group_by_clause := '';
1692
1693 l_order_by_clause := '';
1694 END IF;
1695 END IF; --create or update mode
1696
1697 END IF; --create and denormalize events Misc Cash Receipts
1698
1699 -------------------------------------------------------------------
1700 -- Build statement for Bills Receivable event creation
1701 -------------------------------------------------------------------
1702 IF p_xla_ev_rec.xla_doc_table = 'TRH' THEN
1703
1704 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL
1705 AND p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
1706
1707 /*-----------------------------------------------------+
1708 | The document id for BR is a transaction history id |
1709 | instead of customer_trx_id. The customer_trx_id is |
1710 | only used for storing the header info BR, all the |
1711 | accounting is driven by TRH. So the ARP_ACCT_MAIN |
1712 | package which call ARP_XLA_EVENTS to create events |
1713 | along with the accounting entries required a TRH_ID |
1714 | instead of a customer_trx_id. |
1715 | This change will not impact the upgrade mode neither|
1716 | the batch mode for they do not use document id |
1717 | replace trh.customer_trx_id by |
1718 | trh.transaction_history_id. |
1719 +-----------------------------------------------------*/
1720
1721 IF p_xla_ev_rec.xla_from_doc_id = p_xla_ev_rec.xla_to_doc_id THEN
1722 l_where_parm_clause :=
1723 ' AND trh.transaction_history_id = :b_xla_from_doc_id
1724 AND trh.transaction_history_id = :b_xla_to_doc_id ' || CRLF;
1725 ELSE
1726 l_where_parm_clause :=
1727 ' AND trh.transaction_history_id >= :b_xla_from_doc_id
1728 AND trh.transaction_history_id <= :b_xla_to_doc_id ' || CRLF;
1729 END IF;
1730 END IF;
1731
1732
1733 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
1734 l_where_parm_clause := l_where_parm_clause
1735 || ' AND trh.request_id = :b_xla_req_id ' || CRLF;
1736 END IF;
1737
1738 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
1739 l_where_parm_clause := l_where_parm_clause
1740 || ' AND trh.transaction_history_id = :b_xla_dist_id ' || CRLF;
1741 END IF;
1742
1743 IF p_xla_ev_rec.xla_call IN ('C', 'B') AND p_call_point = 1 THEN
1744
1745 log('Start Building Bills Receivable statement ' );
1746
1747 l_select_clause :=
1748 ' select
1749 tty.post_to_gl POSTTOGL ,
1750 ''BILL'' TRX_TYPE ,
1751 decode(trh.status,
1752 ''INCOMPLETE'', ''I'',
1753 ''PENDING_ACCEPTANCE'',''I'',
1754 ''C'') COMP_FLAG ,
1755 trh.customer_trx_id TRX_ID ,
1756 ct.trx_number TRX_NUMBER ,
1757 ct.org_id ORG_ID,
1758 decode(trh.event,
1759 ''INCOMPLETE'' , ''BILL_CREATE'',
1760 ''ACCEPTED'' , ''BILL_CREATE'',
1761 ''COMPLETED'' , decode(trh.status,
1762 ''PENDING_ACCEPTANCE'', ''BILL_CREATE'',
1763 ''PENDING_REMITTANCE'', ''BILL_CREATE'',
1764 ''NO_EVENT''),
1765 ''CANCELLED'' , ''BILL_REVERSE'',
1766 decode(trh1.first_posted_record_flag,
1767 '''', ''BILL_CREATE'',
1768 decode(nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1769 nvl(trh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1770 decode(trh.posting_control_id,
1771 trh1.posting_control_id, ''BILL_CREATE'',
1772 ''BILL_UPDATE''),
1773 ''BILL_UPDATE''))) OVERRIDE_EVENT,
1774 trh.posting_control_id PSTID ,
1775 decode(tty.post_to_gl,
1776 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1777 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))) GL_DATE,
1778 trh.event_id EXIST_EVENT ,
1779 '''' EVENT_ID ,
1780 to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
1781 '''' EVENT_TYPE ,
1782 ''X'' EVENT_STATUS ,
1783 ct.trx_date TRANSACTION_DATE,
1784 ct.legal_entity_id LEGAL_ENTITY_ID ';
1785
1786 l_from_clause :=
1787 ' FROM ar_transaction_history' || l_all_clause || ' trh, ' || CRLF ||
1788 ' ar_transaction_history' || l_all_clause || ' trh1, ' || CRLF ||
1789 ' ra_customer_trx' || l_all_clause || ' ct, ' || CRLF ||
1790 ' ra_cust_trx_types' || l_all_clause || ' tty ' || CRLF;
1791
1792 l_where_clause := ' where ct.customer_trx_id = trh.customer_trx_id ' || CRLF ||
1793 ' and ct.cust_trx_type_id = tty.cust_trx_type_id ' || CRLF ||
1794 ' and ct.org_id = tty.org_id ' || CRLF ||
1795 ' and ct.customer_trx_id = trh1.customer_trx_id (+) ' || CRLF ||
1796 ' and ''Y'' = trh1.first_posted_record_flag (+) ' || CRLF ||
1797 ' and ' || CRLF ||
1798 ' decode(trh.event, ' || CRLF ||
1799 ' ''INCOMPLETE'', ' || CRLF ||
1800 ' decode(trh1.first_posted_record_flag,'''',''Y'', ' || CRLF ||
1801 ' ''Y'', ''Y'', ''N''), ' || CRLF ||
1802 ' ''COMPLETED'', ' || CRLF ||
1803 ' decode(trh.status, ' || CRLF ||
1804 ' ''PENDING_ACCEPTANCE'', ' || CRLF ||
1805 ' decode(trh1.first_posted_record_flag, ' || CRLF ||
1806 ' '''', ''Y'', ''Y'', ''Y'', ' || CRLF ||
1807 ' ''N''), ' || CRLF ||
1808 ' trh.postable_flag), ' || CRLF ||
1809 ' trh.postable_flag) = ''Y'' ' || CRLF ||
1810 ' AND decode(trh.event_id, ' || CRLF ||
1811 ' '''', ''Y'', ' || CRLF ||
1812 ' decode(:b_xla_mode, ''O'',''Y'', ' || CRLF ||
1813 ' ''N'')) = ''Y'' ' || CRLF ;
1814
1815 ---
1816 ---Note the above decode contains events whose values are equated to postable flag
1817 ---These will always be non accounting events and have been coded to ensure completeness
1818 ---and catch potential data issues if any.
1819 ---
1820 l_group_by_clause :=
1821 ' GROUP BY trh.customer_trx_id,
1822 ct.trx_number,
1823 ct.org_id,
1824 decode(trh.status,
1825 ''INCOMPLETE'', ''I'',
1826 ''PENDING_ACCEPTANCE'',''I'',
1827 ''C''),
1828 tty.post_to_gl,
1829 trh.posting_control_id,
1830 decode(trh.event,
1831 ''INCOMPLETE'' , ''BILL_CREATE'',
1832 ''ACCEPTED'' , ''BILL_CREATE'',
1833 ''COMPLETED'' , decode(trh.status,
1834 ''PENDING_ACCEPTANCE'', ''BILL_CREATE'',
1835 ''PENDING_REMITTANCE'', ''BILL_CREATE'',
1836 ''NO_EVENT''),
1837 ''CANCELLED'' , ''BILL_REVERSE'',
1838 decode(trh1.first_posted_record_flag,
1839 '''', ''BILL_CREATE'',
1840 decode(nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1841 nvl(trh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
1842 decode(trh.posting_control_id,
1843 trh1.posting_control_id, ''BILL_CREATE'',
1844 ''BILL_UPDATE''),
1845 ''BILL_UPDATE''))),
1846 decode(tty.post_to_gl,
1847 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1848 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
1849 trh.event_id ,
1850 ct.trx_date,
1851 ct.legal_entity_id ' || CRLF;
1852
1853
1854 l_order_by_clause :=
1855 'ORDER BY TRX_ID ,
1856 GL_DATE ,
1857 PSTID desc ';
1858
1859 log('End Building Bills Receivable statement ' );
1860 ---------------------------------------------------------------------
1861 -- Build the clause for Transaction History denormalize events stmt
1862 ---------------------------------------------------------------------
1863 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
1864
1865 log('Building Denormalize Bills Receivable statement ' );
1866
1867 l_select_clause :=
1868 ' SELECT trh.rowid,
1869 ev1.event_id ';
1870
1871 --use xla_transaction_entity_n1
1872
1873 l_from_clause :=
1874 ' FROM xla_events' || ' ev1, ' || CRLF ||
1875 ' xla_transaction_entities_upg' || ' evn, ' || CRLF ||
1876 ' ar_transaction_history' || l_all_clause || ' trh, ' || CRLF ||
1877 ' ra_customer_trx' || l_all_clause || ' ct, ' || CRLF ||
1878 ' ra_cust_trx_types' || l_all_clause || ' tty ' || CRLF;
1879
1880 l_where_clause :=
1881 ' WHERE ct.customer_trx_id = trh.customer_trx_id ' || CRLF ||
1882 ' AND trh.event_id IS NULL ' || CRLF ||
1883 ' and ct.cust_trx_type_id = tty.cust_trx_type_id ' || CRLF ||
1884 ' and ct.org_id = tty.org_id ' || CRLF ||
1885 ' and decode(trh.event,
1886 ''CANCELLED'' , ''BILL_REVERSE'',
1887 ev1.event_type_code) = ev1.event_type_code
1888 AND ev1.entity_id = evn.entity_id
1889 AND ev1.application_id = 222
1890 AND evn.entity_code = ''BILLS_RECEIVABLE''
1891 AND evn.application_id = 222
1892 AND trh.posting_control_id = nvl(ev1.reference_num_1,-3)
1893 AND trh.customer_trx_id = NVL(evn.source_id_int_1,-99)
1894 AND evn.ledger_id = ct.set_of_books_id
1895 AND decode(tty.post_to_gl,
1896 ''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
1897 decode(trh.event,
1898 ''INCOMPLETE'', nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY'')),
1899 ''COMPLETED'',
1900 decode(trh.status,
1901 ''PENDING_ACCEPTANCE'',
1902 nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY'')),
1903 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
1904 nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')))) =
1905 nvl(ev1.event_date, to_date(''01-01-1900'',''DD-MM-YYYY''))
1906 AND decode(trh.event,
1907 ''INCOMPLETE'', ''Y'',
1908 ''COMPLETED'', decode(trh.status,
1909 ''PENDING_ACCEPTANCE'',''Y'',
1910 trh.postable_flag),
1911 trh.postable_flag) = ''Y''
1912 AND decode(trh.posting_control_id,
1913 -3, ev1.event_status_code,
1914 ''P'') = ev1.event_status_code ' || CRLF;
1915
1916
1917 l_group_by_clause := '';
1918
1919 l_order_by_clause := '';
1920
1921 log('End Building Denormalize Bills Receivable statement ' );
1922 END IF; --create or update mode
1923
1924 END IF; --transaction
1925
1926 p_stmt := l_select_clause ||
1927 l_from_clause ||
1928 l_where_clause ||
1929 l_where_parm_clause ||
1930 l_group_by_clause ||
1931 l_order_by_clause ;
1932
1933 log('p_stmt ' || SUBSTRB(p_stmt,1,3980));
1934
1935 log('ARP_XLA_EVENTS.Build_Stmt ()-');
1936 EXCEPTION
1937 WHEN OTHERS THEN
1938 log('EXCEPTION: ARP_XLA_EVENTS.Build_Stmt');
1939 RAISE;
1940
1941 END Build_Stmt;
1942
1943 /*========================================================================
1944 | PUBLIC PROCEDURE Define Array
1945 |
1946 | DESCRIPTION
1947 | Define positional place holders in the select list
1948 |
1949 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1950 | Get_Select_Cursor
1951 |
1952 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1953 |
1954 | PARAMETERS p_select_c IN Cursor handle
1955 | p_xla_ev_rec IN Events parameter record
1956 | p_ev_rec IN Events record
1957 |
1958 | KNOWN ISSUES
1959 |
1960 | NOTES
1961 |
1962 | MODIFICATION HISTORY
1963 | Date Author Description of Changes
1964 *=======================================================================*/
1965 PROCEDURE define_arrays( p_select_c IN INTEGER,
1966 p_xla_ev_rec IN xla_events_type,
1967 p_ev_rec IN ev_rec_type,
1968 p_call_point IN NUMBER) IS
1969 BEGIN
1970 log( 'ARP_XLA_EVENTS.define_arrays()+' );
1971
1972 IF p_xla_ev_rec.xla_call IN ('C','B') AND p_call_point = 1 THEN
1973
1974 dbms_sql.define_array(p_select_c, 1 , p_ev_rec.posttogl
1975 , MAX_ARRAY_SIZE, STARTING_INDEX );
1976 dbms_sql.define_array(p_select_c, 2 , p_ev_rec.trx_type
1977 , MAX_ARRAY_SIZE, STARTING_INDEX );
1978 dbms_sql.define_array(p_select_c, 3 , p_ev_rec.trx_status
1979 , MAX_ARRAY_SIZE, STARTING_INDEX );
1980 dbms_sql.define_array(p_select_c, 4 , p_ev_rec.trx_id
1981 , MAX_ARRAY_SIZE, STARTING_INDEX );
1982 dbms_sql.define_array(p_select_c, 5 , p_ev_rec.trx_number
1983 , MAX_ARRAY_SIZE, STARTING_INDEX );
1984 dbms_sql.define_array(p_select_c, 6 , p_ev_rec.org_id
1985 , MAX_ARRAY_SIZE, STARTING_INDEX );
1986 dbms_sql.define_array(p_select_c, 7 , p_ev_rec.override_event
1987 , MAX_ARRAY_SIZE, STARTING_INDEX );
1988 dbms_sql.define_array(p_select_c, 8 , p_ev_rec.pstid
1989 , MAX_ARRAY_SIZE, STARTING_INDEX );
1990 dbms_sql.define_array(p_select_c, 9 , p_ev_rec.dist_gl_date
1991 , MAX_ARRAY_SIZE, STARTING_INDEX );
1992 dbms_sql.define_array(p_select_c, 10, p_ev_rec.ev_match_event_id
1993 , MAX_ARRAY_SIZE, STARTING_INDEX );
1994 dbms_sql.define_array(p_select_c, 11 , p_ev_rec.dist_event_id
1995 , MAX_ARRAY_SIZE, STARTING_INDEX );
1996 dbms_sql.define_array(p_select_c, 12, p_ev_rec.ev_match_gl_date
1997 , MAX_ARRAY_SIZE, STARTING_INDEX );
1998 dbms_sql.define_array(p_select_c, 13, p_ev_rec.ev_match_type
1999 , MAX_ARRAY_SIZE, STARTING_INDEX );
2000 dbms_sql.define_array(p_select_c, 14, p_ev_rec.ev_match_status
2001 , MAX_ARRAY_SIZE, STARTING_INDEX );
2002 --{HYU transaction_date,legal_entity_id
2003 dbms_sql.define_array(p_select_c, 15, p_ev_rec.transaction_date
2004 , MAX_ARRAY_SIZE, STARTING_INDEX );
2005 dbms_sql.define_array(p_select_c, 16, p_ev_rec.legal_entity_id
2006 , MAX_ARRAY_SIZE, STARTING_INDEX );
2007 --}
2008
2009 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
2010 dbms_sql.define_array(p_select_c, 1 , p_ev_rec.dist_row_id
2011 , MAX_ARRAY_SIZE, STARTING_INDEX );
2012 dbms_sql.define_array(p_select_c, 2 , p_ev_rec.dist_event_id
2013 , MAX_ARRAY_SIZE, STARTING_INDEX );
2014 END IF;
2015
2016 log( 'ARP_XLA_EVENTS.define_arrays()-' );
2017
2018 EXCEPTION
2019 WHEN OTHERS THEN
2020 log('EXCEPTION: ARP_XLA_EVENTS.define_arrays');
2021 RAISE;
2022
2023 END define_arrays;
2024
2025 /*========================================================================
2026 | PUBLIC PROCEDURE Get_Column_Values
2027 |
2028 | DESCRIPTION
2029 | Gets the values in select list and stores them in the target
2030 | event record table variable
2031 |
2032 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2033 | Get_Column_Values
2034 | Upd_Dist
2035 |
2036 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2037 |
2038 | PARAMETERS p_select_c IN Cursor handle
2039 | p_xla_ev_rec IN Events parameter record
2040 | p_ev_rec OUT Events record
2041 |
2042 | KNOWN ISSUES
2043 |
2044 | NOTES
2045 |
2046 | MODIFICATION HISTORY
2047 | Date Author Description of Changes
2048 *=======================================================================*/
2049 PROCEDURE get_column_values(p_select_c IN INTEGER,
2050 p_xla_ev_rec IN xla_events_type,
2051 p_call_point IN NUMBER,
2052 p_ev_rec OUT NOCOPY ev_rec_type) IS
2053 BEGIN
2054 log('ARP_XLA_EVENTS.get_column_values (+)');
2055
2056 IF p_xla_ev_rec.xla_call IN ('C','B') AND p_call_point = 1 THEN
2057 dbms_sql.column_value(p_select_c, 1 , p_ev_rec.posttogl);
2058 dbms_sql.column_value(p_select_c, 2 , p_ev_rec.trx_type);
2059 dbms_sql.column_value(p_select_c, 3 , p_ev_rec.trx_status);
2060 dbms_sql.column_value(p_select_c, 4 , p_ev_rec.trx_id);
2061 dbms_sql.column_value(p_select_c, 5 , p_ev_rec.trx_number);
2062 dbms_sql.column_value(p_select_c, 6 , p_ev_rec.org_id);
2063 dbms_sql.column_value(p_select_c, 7 , p_ev_rec.override_event);
2064 dbms_sql.column_value(p_select_c, 8 , p_ev_rec.pstid);
2065 dbms_sql.column_value(p_select_c, 9 , p_ev_rec.dist_gl_date);
2066 dbms_sql.column_value(p_select_c, 10 , p_ev_rec.dist_event_id);
2067 dbms_sql.column_value(p_select_c, 11, p_ev_rec.ev_match_event_id);
2068 dbms_sql.column_value(p_select_c, 12, p_ev_rec.ev_match_gl_date);
2069 dbms_sql.column_value(p_select_c, 13, p_ev_rec.ev_match_type);
2070 dbms_sql.column_value(p_select_c, 14, p_ev_rec.ev_match_status);
2071 --{HYU transaction_date,legal_entity_id
2072 dbms_sql.column_value(p_select_c, 15, p_ev_rec.transaction_date);
2073 dbms_sql.column_value(p_select_c, 16, p_ev_rec.legal_entity_id);
2074 --}
2075 ELSIF p_xla_ev_rec.xla_call IN ('D','B') AND p_call_point = 2 THEN
2076 dbms_sql.column_value(p_select_c, 1 , p_ev_rec.dist_row_id);
2077 dbms_sql.column_value(p_select_c, 2 , p_ev_rec.dist_event_id);
2078 END IF;
2079
2080 log('ARP_XLA_EVENTS.get_column_values (-)');
2081
2082 END get_column_values;
2083
2084 /*========================================================================
2085 | PUBLIC PROCEDURE Get_Select_Cursor
2086 |
2087 | DESCRIPTION
2088 | Builds Select statement, opens cursor, parses it, defines place
2089 | holders for select list, binds variables and returns a cursor
2090 | handle.
2091 |
2092 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2093 | Upd_Dist
2094 |
2095 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2096 |
2097 | PARAMETERS p_xla_ev_rec IN Events parameter record
2098 |
2099 | KNOWN ISSUES
2100 |
2101 | NOTES
2102 |
2103 | MODIFICATION HISTORY
2104 | Date Author Description of Changes
2105 *=======================================================================*/
2106 FUNCTION Get_Select_Cursor(p_xla_ev_rec IN xla_events_type,
2107 p_call_point IN NUMBER) RETURN INTEGER IS
2108
2109 l_select_c INTEGER;
2110 l_stmt VARCHAR2(22000);
2111 l_ev_rec ev_rec_type;
2112
2113 BEGIN
2114
2115 log('ARP_XLA_EVENTS.Get_Select_Cursor ()+');
2116
2117 Build_Stmt(p_xla_ev_rec => p_xla_ev_rec,
2118 p_stmt => l_stmt,
2119 p_call_point => p_call_point);
2120
2121 log('Opening cursor, to give cursor handle');
2122
2123 l_select_c := dbms_sql.open_cursor;
2124
2125 log('Parsing select stmt');
2126
2127 dbms_sql.parse(l_select_c, l_stmt, dbms_sql.v7);
2128
2129 ------------------------------------------------------------
2130 -- Define Column Arrays
2131 ------------------------------------------------------------
2132
2133 define_arrays(p_select_c => l_select_c,
2134 p_xla_ev_rec => p_xla_ev_rec,
2135 p_ev_rec => l_ev_rec,
2136 p_call_point => p_call_point);
2137
2138 ------------------------------------------------------------
2139 -- Bind Variables
2140 ------------------------------------------------------------
2141 IF p_call_point = 1 THEN
2142 log('p_xla_ev_rec.xla_mode ' || p_xla_ev_rec.xla_mode);
2143 dbms_sql.bind_variable(l_select_c, ':b_xla_mode', p_xla_ev_rec.xla_mode);
2144 END IF;
2145
2146 IF p_xla_ev_rec.xla_from_doc_id IS NOT NULL THEN
2147 log('p_xla_ev_rec.xla_from_doc_id ' || p_xla_ev_rec.xla_from_doc_id);
2148 dbms_sql.bind_variable(l_select_c, ':b_xla_from_doc_id', p_xla_ev_rec.xla_from_doc_id);
2149 END IF;
2150
2151 IF p_xla_ev_rec.xla_to_doc_id IS NOT NULL THEN
2152 log('p_xla_ev_rec.xla_to_doc_id ' || p_xla_ev_rec.xla_to_doc_id);
2153 dbms_sql.bind_variable(l_select_c, ':b_xla_to_doc_id', p_xla_ev_rec.xla_to_doc_id);
2154 END IF;
2155
2156 IF p_xla_ev_rec.xla_req_id IS NOT NULL THEN
2157 log('p_xla_ev_rec.xla_req_id ' || p_xla_ev_rec.xla_req_id);
2158 dbms_sql.bind_variable(l_select_c, ':b_xla_req_id', p_xla_ev_rec.xla_req_id);
2159 END IF;
2160
2161 IF p_xla_ev_rec.xla_dist_id IS NOT NULL THEN
2162 log('p_xla_ev_rec.xla_dist_id ' || p_xla_ev_rec.xla_dist_id);
2163 dbms_sql.bind_variable(l_select_c, ':b_xla_dist_id', p_xla_ev_rec.xla_dist_id);
2164 END IF;
2165
2166 log('ARP_XLA_EVENTS.Get_Select_Cursor (-)');
2167
2168 return(l_select_c);
2169
2170 EXCEPTION
2171 WHEN OTHERS THEN
2172 log('EXCEPTION: ARP_XLA_EVENTS.Get_Select_Cursor:'||SQLERRM);
2173 RAISE;
2174
2175 END Get_Select_Cursor;
2176
2177 /*========================================================================
2178 | PUBLIC PROCEDURE Create_All_Events
2179 |
2180 | DESCRIPTION
2181 | Creates, updates and deletes events for the transactions
2182 | INV, DM, CM, CB, GUAR, DEP
2183 |
2184 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2185 | Execute
2186 |
2187 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2188 |
2189 | PARAMETERS
2190 | p_xla_ev_rec IN xla_events_type
2191 |
2192 | KNOWN ISSUES
2193 |
2194 | NOTES
2195 |
2196 | MODIFICATION HISTORY
2197 | Date Author Description of Changes
2198 | 10-SEP-2003 Herve Yu
2199 | Use the set_of_books_id for now as the ledger_id bug#3135769
2200 | we might need to come back on this point later after the uptake
2201 | of ledger architecture project.
2202 *=======================================================================*/
2203 PROCEDURE Create_All_Events(p_xla_ev_rec IN xla_events_type) IS
2204
2205 /*---------------------------------------------------------------------+
2206 | Main cursor which gets transaction data, and event data for decision|
2207 | making on which events require to be created, updated or deleted. |
2208 +---------------------------------------------------------------------*/
2209 TYPE get_tran_data_type IS REF CURSOR;
2210
2211 get_tran_data get_tran_data_type;
2212
2213 l_select_c BINARY_INTEGER;
2214
2215 /*-----------------------------------------------------------------------+
2216 | Local Variable Declarations and initializations |
2217 +-----------------------------------------------------------------------*/
2218 ev_rec ev_rec_type ;
2219 empty_ev_rec ev_rec_type ;
2220
2221 bld_ev_rec bld_ev_type ;
2222 empty_bld_ev_rec bld_ev_type ;
2223
2224 --cache_ev_rec bld_ev_type ;
2225
2226 bld_ev_ent xla_events_pub_pkg.t_array_entity_event_info_s;
2227 empty_bld_ev_ent xla_events_pub_pkg.t_array_entity_event_info_s;
2228
2229 prev_distid VARCHAR2(50) := '';
2230
2231 l_cached BOOLEAN;
2232 l_cached2 BOOLEAN;
2233 ev_match_flg BOOLEAN;
2234 l_last_fetch BOOLEAN;
2235 l_match_event_in_cache BOOLEAN;
2236
2237 event_trx_gl_date_mismatch EXCEPTION;
2238 abnormal_cond EXCEPTION;
2239
2240 i BINARY_INTEGER := 0;
2241 j BINARY_INTEGER := 0;
2242 k BINARY_INTEGER := 0;
2243 l BINARY_INTEGER := 0;
2244 m BINARY_INTEGER := 0;
2245 n BINARY_INTEGER := 0;
2246 p BINARY_INTEGER := 0;
2247 q BINARY_INTEGER := 0;
2248 r BINARY_INTEGER := 0;
2249
2250 l_change_matrix VARCHAR2(30);
2251
2252 temp_event_id BINARY_INTEGER := 0;
2253
2254 prev_trx_id ra_customer_trx.customer_trx_id%TYPE;
2255
2256 l_type VARCHAR2(30);
2257 l_build_ctr BINARY_INTEGER;
2258 l_ev_type VARCHAR2(20);
2259 l_ignore INTEGER;
2260 l_rows_fetched VARCHAR2(10);
2261 l_low INTEGER:=0;
2262 l_high INTEGER:=0;
2263 lnb NUMBER;
2264 --xla event api declarations
2265 l_event_source_info xla_events_pub_pkg.t_event_source_info;
2266 l_reference_info xla_events_pub_pkg.t_event_reference_info;
2267 l_security_context xla_events_pub_pkg.t_security;
2268 l_entity_event_info_tbl xla_events_pub_pkg.t_array_entity_event_info_s;
2269 keep_flag BOOLEAN;
2270 l_revrun VARCHAR2(5);
2271 l_entity_code VARCHAR2(20);
2272 z NUMBER :=0;
2273 cnt INTEGER;
2274 trxn_number VARCHAR2(240);
2275 CURSOR for_batch IS
2276 SELECT 'X'
2277 FROM xla_events_int_gt
2278 WHERE entity_id IS NULL;
2279 l_test VARCHAR2(1);
2280
2281
2282 BEGIN
2283 log('ARP_XLA_EVENTS.Create_All_Events ()+');
2284
2285 --Bug#3135769
2286 --{
2287 IF arp_standard.sysparm.set_of_books_id IS NULL THEN
2288 arp_standard.init_standard;
2289 END IF;
2290 --}
2291
2292 IF p_xla_ev_rec.xla_call IN ('C','B') THEN
2293 null;
2294 ELSE
2295 goto endlabel;
2296 END IF;
2297
2298 -----------------------------------------------------------
2299 --Get the transaction entity
2300 -----------------------------------------------------------
2301 l_entity_code := entity_code( p_xla_ev_rec.xla_doc_table);
2302
2303 ------------------------------------------------------------
2304 -- Build select statement and get cursor handle
2305 ------------------------------------------------------------
2306 l_select_c := Get_Select_Cursor(p_xla_ev_rec => p_xla_ev_rec,
2307 p_call_point => 1);
2308
2309 l_ignore := dbms_sql.execute( l_select_c );
2310
2311 log( 'Fetching select stmt');
2312
2313 LOOP -- Main Cursor Loop
2314
2315 log( 'reinitialize the build events cache');
2316 bld_ev_rec.bld_dml_flag := empty_bld_ev_rec.bld_dml_flag;
2317 bld_ev_rec.bld_temp_event_id := empty_bld_ev_rec.bld_temp_event_id;
2318
2319 --initialize the collection of records containing event information
2320 bld_ev_ent := empty_bld_ev_ent;
2321
2322 log( 'reinitialize the events cache');
2323
2324 ev_rec.posttogl := empty_ev_rec.posttogl;
2325 ev_rec.trx_type := empty_ev_rec.trx_type;
2326 ev_rec.trx_status := empty_ev_rec.trx_status;
2327 ev_rec.trx_id := empty_ev_rec.trx_id;
2328 ev_rec.pstid := empty_ev_rec.pstid;
2329 ev_rec.dist_gl_date := empty_ev_rec.dist_gl_date;
2330 ev_rec.override_event := empty_ev_rec.override_event;
2331 ev_rec.dist_event_id := empty_ev_rec.dist_event_id;
2332 ev_rec.ev_match_event_id := empty_ev_rec.ev_match_event_id;
2333 ev_rec.ev_match_gl_date := empty_ev_rec.ev_match_gl_date;
2334 ev_rec.ev_match_type := empty_ev_rec.ev_match_type;
2335 ev_rec.ev_match_status := empty_ev_rec.ev_match_status;
2336 --{HYU transaction_date,legal_entity_id
2337 ev_rec.transaction_date := empty_ev_rec.transaction_date;
2338 ev_rec.legal_entity_id := empty_ev_rec.legal_entity_id;
2339 --}
2340 j := 0;
2341
2342 l_rows_fetched := dbms_sql.fetch_rows(l_select_c);
2343
2344 log('Rows Fetched are ' || l_rows_fetched);
2345
2346 l_low := l_high + 1;
2347 l_high:= l_high + l_rows_fetched;
2348
2349 IF l_rows_fetched > 0 THEN
2350
2351 log('Fetched a row ');
2352 log('l_low ' || l_low);
2353 log('l_high ' || l_high);
2354
2355 get_column_values(p_select_c => l_select_c,
2356 p_xla_ev_rec => p_xla_ev_rec,
2357 p_ev_rec => ev_rec,
2358 p_call_point => 1);
2359
2360 -- no more rows to fetch
2361 IF l_rows_fetched < MAX_ARRAY_SIZE THEN
2362 log('Done fetching 1');
2363
2364 IF( dbms_sql.is_open( l_select_c) ) THEN
2365 dbms_sql.close_cursor( l_select_c );
2366 END IF;
2367
2368 END IF; --no more rows to fetch
2369
2370 ELSE --if rows fetched = 0
2371 log('Done fetching 2');
2372
2373 IF( dbms_sql.is_open( l_select_c ) ) THEN
2374 dbms_sql.close_cursor( l_select_c );
2375 END IF;
2376
2377 EXIT;
2378
2379 END IF; --rows fetched greater than 0
2380
2381 /*--------------------------------------------------------+
2382 |Set the event id to the existing event id which is not |
2383 |accounted, since for the current distribution gl date |
2384 |another event could not be found, however there exists |
2385 |another event which is not accounted which has a |
2386 |different gl date, set values for decision making. |
2387 +--------------------------------------------------------*/
2388 log('Bef Loop ');
2389 log('Number of rows selected : '|| ev_rec.trx_id.COUNT);
2390 FOR i IN ev_rec.trx_id.FIRST .. ev_rec.trx_id.LAST LOOP
2391
2392 log('Processing Transactions Events using Change Matrix');
2393
2394 --Initialize variables when new transaction is being processed
2395 IF NVL(prev_trx_id,-999) <> ev_rec.trx_id(i) THEN
2396
2397 log('Initialization Tasks ');
2398
2399 END IF; --reinitialize trx cache
2400
2401 /*----------------------------------------------------------+
2402 |Processing required in OLTP mode only |
2403 +----------------------------------------------------------*/
2404 IF p_xla_ev_rec.xla_mode = 'O' THEN
2405 -- In batch mode, ar should also verify the existance of events
2406 -- IF p_xla_ev_rec.xla_mode IN ('O','B') THEN
2407
2408 /*-------------------------------------------------------------------------+
2409 |Get the data associated with the existing event to enable decision making|
2410 |Note : There must be an existing event - otherwise a no data found excep |
2411 | will be raised |
2412 +-------------------------------------------------------------------------*/
2413 IF ev_rec.dist_event_id(i) IS NOT NULL THEN
2414
2415 /*
2416 get_existing_event
2417 (p_event_id => ev_rec.dist_event_id(i),
2418 x_event_id => ev_rec.ev_match_event_id(i),
2419 x_event_date => ev_rec.ev_match_gl_date(i),
2420 x_event_status_code => ev_rec.ev_match_status(i),
2421 x_event_type_code => ev_rec.ev_match_type(i));
2422 */
2423
2424 select ae.event_id ,
2425 ae.event_date ,
2426 ae.event_status_code,
2427 ae.event_type_code
2428 into ev_rec.ev_match_event_id(i) ,
2429 ev_rec.ev_match_gl_date(i) ,
2430 ev_rec.ev_match_status(i) ,
2431 ev_rec.ev_match_type(i)
2432 from xla_events ae
2433 where ev_rec.dist_event_id(i) = ae.event_id;
2434
2435
2436 /*-------------------------------------------------------------------------+
2437 |Find an existing event to which the current distribution can latch on to |
2438 |and use in decision making - best match. As of a given gl date, there |
2439 |can exists only one unposted event type matching the dist event type for |
2440 |the dist gl date for a document id (even if document ids overlapp, since |
2441 |event types are unique for existing events and cannot overlapp across |
2442 |different document entities. |
2443 +-------------------------------------------------------------------------*/
2444 ELSIF ev_rec.dist_event_id(i) IS NULL THEN
2445 --
2446 -- BEGIN
2447 --log('ev_rec.trx_id(i):'||ev_rec.trx_id(i));
2448 --log('ev_rec.dist_gl_date(i):'||ev_rec.dist_gl_date(i));
2449 --log('ev_rec.override_event(i):'||ev_rec.override_event(i));
2450 /*
2451 get_best_existing_event
2452 (p_trx_id => ev_rec.trx_id(i),
2453 p_gl_date => ev_rec.dist_gl_date(i),
2454 p_override_event => ev_rec.override_event(i),
2455 x_match_event_id => ev_rec.ev_match_event_id(i),
2456 x_match_gl_date => ev_rec.ev_match_gl_date(i),
2457 x_match_status => ev_rec.ev_match_status(i),
2458 x_match_type => ev_rec.ev_match_type(i));
2459 */
2460
2461 --{BUG#5347627
2462 BEGIN
2463 IF ev_rec.override_event(i) IN
2464 ('INV_CREATE','DM_CREATE','DEP_CREATE','CB_CREATE','CM_CREATE','GUAR_CREATE',
2465 'INV_UPDATE','DM_UPDATE','DEP_UPDATE','CB_UPDATE','CM_UPDATE','GUAR_UPDATE')
2466 THEN
2467 select ae2.event_id ,
2468 ae2.event_date ,
2469 ae2.event_status_code,
2470 ae2.event_type_code
2471 into ev_rec.ev_match_event_id(i) ,
2472 ev_rec.ev_match_gl_date(i) ,
2473 ev_rec.ev_match_status(i) ,
2474 ev_rec.ev_match_type(i)
2475 from xla_events ae2
2476 where ae2.application_id = 222
2477 and ae2.event_id IN
2478 ( select MAX( ae.event_id )
2479 from xla_events ae,
2480 xla_transaction_entities_upg xt,
2481 ra_customer_trx_all trx
2482 where trx.customer_trx_id = ev_rec.trx_id(i)
2483 and NVL(xt.source_id_int_1,-99) = trx.customer_trx_id
2484 and xt.entity_code = 'TRANSACTIONS'
2485 and xt.ledger_id = trx.set_of_books_id
2486 and xt.entity_id = ae.entity_id
2487 and xt.application_id = 222
2488 and ae.application_id = 222
2489 and nvl(ae.event_date,
2490 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2491 and ae.event_status_code <> 'P'
2492 and ae.event_type_code IN
2493 ('INV_CREATE','DM_CREATE','DEP_CREATE','CB_CREATE','CM_CREATE','GUAR_CREATE',
2494 'INV_UPDATE','DM_UPDATE','DEP_UPDATE','CB_UPDATE','CM_UPDATE','GUAR_UPDATE')
2495 )
2496 FOR UPDATE OF ae2.event_id NOWAIT;
2497
2498 ELSIF ev_rec.override_event(i) IN
2499 ('BILL_CREATE','BILL_UPDATE','BILL_REVERSE')
2500 THEN
2501 select ae2.event_id ,
2502 ae2.event_date ,
2503 ae2.event_status_code,
2504 ae2.event_type_code
2505 into ev_rec.ev_match_event_id(i) ,
2506 ev_rec.ev_match_gl_date(i) ,
2507 ev_rec.ev_match_status(i) ,
2508 ev_rec.ev_match_type(i)
2509 from xla_events ae2
2510 where ae2.application_id = 222
2511 and ae2.event_id IN
2512 ( select MAX( ae.event_id )
2513 from xla_events ae,
2514 xla_transaction_entities_upg xt,
2515 ra_customer_trx_all trx
2516 where trx.customer_trx_id = ev_rec.trx_id(i)
2517 and NVL(xt.source_id_int_1,-99) = trx.customer_trx_id
2518 and xt.entity_code = 'BILLS_RECEIVABLE'
2519 and xt.ledger_id = trx.set_of_books_id
2520 and xt.entity_id = ae.entity_id
2521 and xt.application_id = 222
2522 and ae.application_id = 222
2523 and nvl(ae.event_date,
2524 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2525 and ae.event_status_code <> 'P'
2526 and ev_rec.override_event(i) = ae.event_type_code )
2527 FOR UPDATE OF ae2.event_id NOWAIT;
2528
2529
2530 ELSIF ev_rec.override_event(i) IN
2531 ('RECP_CREATE','RECP_RATE_ADJUST','RECP_UPDATE','RECP_REVERSE',
2532 'MISC_RECP_CREATE','MISC_RECP_RATE_ADJUST','MISC_RECP_UPDATE','MISC_RECP_REVERSE')
2533 THEN
2534 select ae2.event_id ,
2535 ae2.event_date ,
2536 ae2.event_status_code,
2537 ae2.event_type_code
2538 into ev_rec.ev_match_event_id(i) ,
2539 ev_rec.ev_match_gl_date(i) ,
2540 ev_rec.ev_match_status(i) ,
2541 ev_rec.ev_match_type(i)
2542 from xla_events ae2
2543 where ae2.application_id = 222
2544 and ae2.event_id IN
2545 ( select MAX( ae.event_id )
2546 from xla_events ae,
2547 xla_transaction_entities_upg xt,
2548 ar_Cash_receipts_all cr
2549 where cr.cash_receipt_id = ev_rec.trx_id(i)
2550 and NVL(xt.source_id_int_1,-99) = cr.cash_receipt_id
2551 and xt.entity_code = 'RECEIPTS'
2552 and xt.ledger_id = cr.set_of_books_id
2553 and xt.entity_id = ae.entity_id
2554 and xt.application_id = 222
2555 and ae.application_id = 222
2556 and nvl(ae.event_date,
2557 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2558 and ae.event_status_code <> 'P'
2559 and DECODE(ev_rec.override_event(i),
2560 'RECP_CREATE', ae.event_type_code,
2561 'MISC_RECP_CREATE', ae.event_type_code,
2562 ev_rec.override_event(i) ) = ae.event_type_code )
2563 FOR UPDATE OF ae2.event_id NOWAIT;
2564
2565 ELSIF ev_rec.override_event(i) IN ('ADJ_CREATE') THEN
2566 select ae2.event_id ,
2567 ae2.event_date ,
2568 ae2.event_status_code,
2569 ae2.event_type_code
2570 into ev_rec.ev_match_event_id(i) ,
2571 ev_rec.ev_match_gl_date(i) ,
2572 ev_rec.ev_match_status(i) ,
2573 ev_rec.ev_match_type(i)
2574 from xla_events ae2
2575 where ae2.application_id = 222
2576 and ae2.event_id IN
2577 ( select MAX( ae.event_id )
2578 from xla_events ae,
2579 xla_transaction_entities_upg xt,
2580 ar_adjustments_all adj
2581 where adj.adjustment_id = ev_rec.trx_id(i)
2582 and NVL(xt.source_id_int_1,-99) = adj.adjustment_id
2583 and xt.entity_code = 'ADJUSTMENTS'
2584 and xt.ledger_id = adj.set_of_books_id
2585 and xt.entity_id = ae.entity_id
2586 and xt.application_id = 222
2587 and ae.application_id = 222
2588 and nvl(ae.event_date,
2589 to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
2590 and ae.event_status_code <> 'P'
2591 and ev_rec.override_event(i) = ae.event_type_code )
2592 FOR UPDATE OF ae2.event_id NOWAIT;
2593 END IF;
2594
2595 EXCEPTION
2596 WHEN NO_DATA_FOUND THEN
2597 --------------------------------------------------------------+
2598 --|Since a match could not be found - ascertain whether there |
2599 --|is an existing event (Typically for Modify document event type|
2600 --|Add condition on trx_type to avoid the undesirable latching of|
2601 --|a RECP event on a previous one. For example : |
2602 --| * Create of a Receipt, RECP_CREATE. |
2603 --| * Reverse of the Receipt, RECP_REVERSE but if we do not add |
2604 --| the condition to avoid the association between the reversal|
2605 --| and the creation of the receipt, the RECP_REVERSE event is |
2606 --| not created. |
2607 --+--------------------------------------------------------------
2608 -- Processing below is not required since a Create or Update event will get created
2609 -- if ther is no matching event - the SQL for each entity would have taken care of
2610 -- building the correct event type. We no longer need to verify for existing events
2611 -- since we look ate the first posted record flag.
2612 NULL;
2613 WHEN OTHERS THEN
2614 /*ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2615 The error indicates that there exist a matching event to latch on to,however
2616 it is currently locked by some other process.The action would be to let the
2617 process continue and create a new event of type UPDATE.
2618
2619 This code flow gets triggered only in cases where the concerned document already
2620 has an event of type CREATE,will explictly override the event_type_code with
2621 that of event type UPDATE to ensure that there exist only one event of type CREATE
2622 for given document*/
2623 IF SQLCODE = -54 THEN
2624 ev_rec.override_event(i) :=
2625 CASE ev_rec.override_event(i)
2626 WHEN 'INV_CREATE' THEN 'INV_UPDATE'
2627 WHEN 'DM_CREATE' THEN 'DM_UPDATE'
2628 WHEN 'DEP_CREATE' THEN 'DEP_UPDATE'
2629 WHEN 'CB_CREATE' THEN 'CB_UPDATE'
2630 WHEN 'CM_CREATE' THEN 'CM_UPDATE'
2631 WHEN 'GUAR_CREATE' THEN 'GUAR_UPDATE'
2632 WHEN 'RECP_CREATE' THEN 'RECP_UPDATE'
2633 WHEN 'MISC_RECP_CREATE' THEN 'MISC_RECP_UPDATE'
2634 END;
2635 IF PG_DEBUG = 'Y' THEN
2636 arp_standard.debug(' ORA-00054 occurred,ev_rec.override_event set to '||
2637 ev_rec.override_event(i) );
2638 END IF;
2639
2640 ELSE
2641 RAISE;
2642 END IF;
2643 END; --distribution is not marked with an event
2644
2645 END IF; --distribution is marked with an event if construct
2646
2647 END IF; --processing for event creation in Oltp mode
2648
2649 --Current distribution does not have an event
2650 IF ev_rec.dist_event_id(i) IS NULL THEN
2651 ev_match_flg := FALSE;
2652 log(' ev_match_flg ' || 'FALSE');
2653 ELSE
2654 log(' ev_match_flg ' || 'TRUE');
2655 ev_match_flg := TRUE;
2656 END IF;
2657
2658 dump_ev_rec(p_ev_rec => ev_rec, p_i => i);
2659
2660 /*------------------------------------------------------------------+
2661 |Since the main cursor gets one row for creation of each new event |
2662 |hence there is no need to have an events cache. The only time we |
2663 |need to figure out what the previous event insert did is to make |
2664 |a decision as regards the construction of the event type as Create|
2665 |or modify. |
2666 +------------------------------------------------------------------*/
2667
2668 l_change_matrix := Change_Matrix(
2669 ev_rec.trx_status(i) ,
2670 ev_rec.dist_gl_date(i) ,
2671 ev_rec.ev_match_gl_date(i) ,
2672 ev_rec.ev_match_status(i) ,
2673 ev_rec.posttogl(i));
2674
2675 log('l_change_matrix ' || l_change_matrix);
2676 l_type := 'NONE';
2677
2678 /*------------------------------------------------------------------+
2679 |This routine cannot create events for posted transactions as XLA |
2680 |apis do not allow it. Hence in oltp mode we set the event match |
2681 |flag to true. This results in the distribution which is posted |
2682 |to get skipped which is okay because : |
2683 |1) The downtime upgrade would have created the event if in range |
2684 |OR 2) The post upgrade would have done so. |
2685 |In either situation we dont really care as the distribution is |
2686 |posted. Bug 5600736 related change. |
2687 +------------------------------------------------------------------*/
2688 IF p_xla_ev_rec.xla_mode = 'O' AND ev_rec.pstid(i) <> -3
2689 AND l_change_matrix = '1.12' THEN
2690
2691 log(' Override ev_match_flg ' || 'TRUE');
2692 ev_match_flg := TRUE;
2693
2694 END IF;
2695
2696 /*-----------------------------------------------------------+
2697 |Latch on to the existing event where gldate matches. |
2698 |Event id will have a value only if an event exists in the |
2699 |database, otherwise it will be null and a negative event id|
2700 |seeded in the cache which will be used as a primary key for|
2701 |mapping the built event to the dist which maps to this |
2702 |event, latching is implicit to these values and is done |
2703 |After the Bulk Insert and Updates to Events table. |
2704 |Latching occurs in the end. |
2705 +-----------------------------------------------------------*/
2706 /* IF ((NOT ev_match_flg)
2707 AND (l_change_matrix IN (1.01,1.03,1.07,1.09,1.13,1.14,
2708 1.16,1.17))) THEN */
2709 /*------------------------------------------------+
2710 |Latching is implicit to ev_rec.ev_event_id for |
2711 |Update or ev_rec.ev_temp_event_id for Insert |
2712 |for all above change matrix values not in the |
2713 |IF construct below. For others explicit latching|
2714 +------------------------------------------------*/
2715 /* IF (l_change_matrix IN (1.01, 1.09, 1.13, 1.17)) THEN
2716 ev_rec.dist_event_id(i) := ev_rec.ev_match_event_id(i);
2717 END IF; --latch on to matching event
2718
2719 END IF; */ --Latch on to existing event check
2720
2721
2722 /*---------------------------------------------------------+
2723 | If Change_Matrix returns 1.02 1.04 1.08 1.10 |
2724 | and the mode of execution is OLTP |
2725 | Then |
2726 | Need to check if the REV_RECOGNITION program has run on |
2727 | that invoice. Because if not then distributions are only|
2728 | modal, therefore they are not stamped with the event_id.|
2729 | Nevertheless, no new event should be created, only the |
2730 | GLDate needs to be updated on that event. |
2731 | Typically when a invoice with rules arrear has some |
2732 | new lines entered then the transaction only has modal |
2733 | REC distributions but the GLDate will be set the last |
2734 | FORECASt REV_RECOGNITION date. |
2735 +---------------------------------------------------------*/
2736 l_revrun := 'X';
2737
2738 IF ev_rec.trx_type(i) = 'INV' AND p_xla_ev_rec.xla_mode = 'O' THEN
2739 log('arp_xla_events.is_one_acct_asg_on_ctlgd()+');
2740 log('The customer_trx_id :'||ev_rec.trx_id(i));
2741
2742 l_revrun := is_one_acct_asg_on_ctlgd(ev_rec.trx_id(i));
2743
2744 log('l_revrun : '||l_revrun);
2745 log('arp_xla_events.is_one_acct_asg_on_ctlgd()-');
2746 END IF;
2747
2748 /*---------------------------------------------------------+
2749 |Update existing Event |
2750 |Matrix - Dist - Description |
2751 | Event |
2752 |1.02 - T - Update event gl date |
2753 |1.03 - T, F - Update Status = Incomplete |
2754 |1.04 - T - Update gl date, Status = Incomplete |
2755 |1.07 - T,F - Update Status = Unprocessed |
2756 |1.08 - T - Update gl date, Status = Unprocessed |
2757 |1.10 - T - Update gl date |
2758 |1.14 - T,F - Status = Incomplete |
2759 |1.16 - T,F - Status = Unprocessed |
2760 |1.08 - F - Update gl date, Status = Unprocesse |
2761 | if Acct_asg created for one line |
2762 |1.02 - F - Update gl date if ACT_ASG created |
2763 |1.04 - F - Update gl date if ACT_ASG created |
2764 |1.10 - F - Update gl date if ACT_ASG created |
2765 |1.09 - T - For the case of cleaning events |
2766 | when update REV_REC event GL Dates. |
2767 |1.23 -T,F - Update of a postable trx to be |
2768 | unpostable Bug#3320427 |
2769 |1.22 -T,F - Update of a unpostable trx to be |
2770 | postable Bug#3320427 |
2771 +---------------------------------------------------------*/
2772 --BUG#3999572
2773 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'))
2774 THEN NULL;
2775 --}
2776 ELSIF (((ev_match_flg)
2777 AND (l_change_matrix IN ('1.02','1.03','1.04','1.07','1.09',
2778 '1.08','1.10'
2779 --,1.14,1.16,1.23
2780 ,'1.22'
2781 )))
2782 OR ((NOT ev_match_flg)
2783 AND (l_change_matrix IN ('1.03','1.07'
2784 --,1.14,1.16,1.23
2785 ,'1.22'
2786 )))
2787 OR ((NOT ev_match_flg)
2788 AND (l_change_matrix IN ('1.02','1.04','1.08','1.10'))
2789 AND (l_revrun = 'N'))
2790 AND p_xla_ev_rec.xla_mode = 'O')
2791 THEN
2792
2793 log('Entered Update Built event construct ');
2794
2795 IF l_change_matrix IN ('1.02','1.04','1.08','1.10','1.22') THEN
2796 ev_rec.ev_match_gl_date(i) := ev_rec.dist_gl_date(i);
2797 --introduce validation to make sure that another dist
2798 --with the same GL Date does not exist
2799 --{BUG#3320427
2800 ELSIF l_change_matrix IN ('1.23') THEN
2801 ev_rec.ev_match_gl_date(i) := TO_DATE('01-01-1900','DD-MM-YYYY');
2802 --}
2803 END IF;
2804 --set the gl date
2805
2806 IF l_change_matrix IN ('1.03', '1.04', '1.14','1.22') THEN
2807 ev_rec.ev_match_status(i) := 'I';
2808 ELSIF l_change_matrix IN ('1.07','1.08','1.16') THEN
2809 ev_rec.ev_match_status(i) := 'U';
2810 --{BUG#3320427
2811 --{BUG#3999572
2812 -- ELSIF l_change_matrix IN (1.23) THEN
2813 -- ev_rec.ev_match_status(i) := 'N';
2814 --}
2815 --}
2816 END IF; --set the status
2817
2818 /*--------------------------------------------------------------------+
2819 | Need to avoid the existance of multiple events of the same type |
2820 | and the same GLDate for the same document in XLA_EVENTS table. |
2821 | Typically this can happens when user update the GL_Date on a inv |
2822 | with rules on which REV_RECOGNITION has run. In this case the |
2823 | the xla_events table contains multiple REV_RECOGNITION events with |
2824 | different GLDate, when user updates the GL Date on the header |
2825 | of the document, the GLDates for all the distributions related |
2826 | to that document are updated to the new GLDate. |
2827 | In this case, we need : |
2828 | Reset the event_id of the distributions to NULL. |
2829 | Conserve only one event of one type and a GLDate. |
2830 | The denormalise mode should restamped the distributions with the |
2831 | correct event_id. |
2832 +--------------------------------------------------------------------*/
2833 keep_flag := TRUE;
2834
2835 --This can happen only real time OLTP
2836 IF p_xla_ev_rec.xla_mode = 'O' THEN
2837
2838 IF bld_ev_ent.COUNT > 0 THEN
2839
2840 FOR indx IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
2841
2842 IF bld_ev_ent(indx).event_type_code = ev_rec.ev_match_type(i) AND
2843 bld_ev_ent(indx).transaction_number= ev_rec.trx_number(i) AND
2844 bld_ev_ent(indx).security_id_int_1 = ev_rec.org_id(i) AND
2845 bld_ev_ent(indx).event_date = ev_rec.ev_match_gl_date(i) AND
2846 bld_ev_ent(indx).event_status_code = ev_rec.ev_match_status(i) AND
2847 bld_ev_ent(indx).event_id <> ev_rec.ev_match_event_id(i)
2848 THEN
2849 log('clean events (event_id) : '||ev_rec.ev_match_event_id(i));
2850 log('clean events (event_type_code) : '||ev_rec.ev_match_type(i));
2851 log('clean events (transaction_number) : '||ev_rec.trx_number(i));
2852
2853 ------------------
2854 -- unset_event_ids
2855 ------------------
2856 un_denormalize_posting_entity(p_xla_doc => p_xla_ev_rec.xla_doc_table,
2857 p_event_id => ev_rec.ev_match_event_id(i));
2858
2859 ----------------
2860 -- delete events
2861 ----------------
2862
2863 -- Set source_info
2864 l_event_source_info.application_id := 222;
2865 l_event_source_info.legal_entity_id := ev_rec.legal_entity_id(i); --to be set
2866 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
2867
2868 /* Bug 6932145: Modified l_event_source_info.entity_type_code = NULL to l_entity_code */
2869 l_event_source_info.entity_type_code := l_entity_code; -- '';
2870 l_event_source_info.transaction_number := ev_rec.trx_number(i);
2871 l_event_source_info.source_id_int_1 := ev_rec.trx_id(i);
2872
2873 -- Set security_context
2874 l_security_context.security_id_int_1 := ev_rec.org_id(i);
2875
2876 -- Delete the event
2877 xla_events_pub_pkg.delete_event
2878 ( p_event_source_info => l_event_source_info,
2879 p_event_id => ev_rec.ev_match_event_id(i),
2880 p_valuation_method => NULL,
2881 p_security_context => l_security_context);
2882
2883 keep_flag := FALSE;
2884
2885 EXIT;
2886
2887 END IF; --event needs to be deleted
2888
2889 END LOOP; --loop through built events
2890
2891 END IF; --built event exists
2892
2893 END IF;--if OLTP then check whether existing events need to be deleted
2894
2895 IF keep_flag THEN
2896
2897 j := j + 1;
2898 l_type := 'BUILT_AN_EVENT';
2899 l_build_ctr := j;
2900 log('l_type ' || l_type);
2901
2902 --Build the event record for update. It is possible for two dists
2903 --to have the same matching event, so for the same event two Update
2904 --dml statements will be introduced in OLTP only, the overhead is
2905 --acceptable versus unnecessarily sweeping the build cache table
2906 --which we intend to use for bulk operations only
2907 bld_ev_rec.bld_dml_flag(l_build_ctr) := 'U';
2908 bld_ev_rec.bld_temp_event_id(l_build_ctr) := ev_rec.ev_match_event_id(i);
2909 bld_ev_ent(l_build_ctr).transaction_number:= ev_rec.trx_number(i);
2910 bld_ev_ent(l_build_ctr).security_id_int_1 := ev_rec.org_id(i);
2911 bld_ev_ent(l_build_ctr).event_date := ev_rec.ev_match_gl_date(i);
2912 bld_ev_ent(l_build_ctr).event_status_code := ev_rec.ev_match_status(i);
2913 bld_ev_ent(l_build_ctr).event_type_code := ev_rec.ev_match_type(i);
2914 bld_ev_ent(l_build_ctr).event_id := ev_rec.ev_match_event_id(i);
2915 bld_ev_ent(l_build_ctr).source_id_int_1 := ev_rec.trx_id(i);
2916 --{HYU transaction_date,legal_entity_id
2917 bld_ev_ent(l_build_ctr).transaction_date := ev_rec.transaction_date(i);
2918 bld_ev_ent(l_build_ctr).reference_num_1 := ev_rec.legal_entity_id(i);
2919 --}
2920
2921 dump_bld_rec(p_bld_rec => bld_ev_rec ,
2922 p_i => l_build_ctr,
2923 p_tag => 'bld_ev_rec' );
2924
2925
2926 dump_event_info(p_ev_info_tab => bld_ev_ent,
2927 p_i => l_build_ctr,
2928 p_tag => 'bld_ev_ent');
2929
2930
2931 END IF;
2932
2933 END IF; --Update event condition
2934
2935 /*-----------------------------------------------------------------+
2936 | Create Events for distributions which do not have an existing |
2937 | event to latch on to. |
2938 | |
2939 | For 1.02 1.04 1.08 1.10 need to check if REVENUE RECOGNITION |
2940 | has run on the invoice. |
2941 +-----------------------------------------------------------------*/
2942 IF (l_change_matrix IN ('1.05','1.06','1.11','1.12')
2943 --{BUG#3999572
2944 --,1.15,1.18,1.19,1.20)
2945 --}
2946 AND (NOT ev_match_flg))
2947 OR
2948 (l_change_matrix IN ('1.02','1.04','1.08','1.10') AND (l_revrun = 'Y')
2949 AND (NOT ev_match_flg)) THEN
2950
2951 log('Building an Event in Insert new' ||
2952 ' event construct ');
2953
2954 --increment the event build table cell counter
2955 j := j + 1;
2956 l_type := 'BUILT_AN_EVENT';
2957 l_build_ctr := j;
2958 log('l_type ' || l_type);
2959
2960 /*----------------------------------------------------+
2961 |Create event - construct event attributes |
2962 +----------------------------------------------------*/
2963 bld_ev_rec.bld_dml_flag(l_build_ctr) := 'I';
2964 bld_ev_ent(l_build_ctr).source_id_int_1 := ev_rec.trx_id(i);
2965 bld_ev_ent(l_build_ctr).transaction_number := ev_rec.trx_number(i);
2966 bld_ev_ent(l_build_ctr).security_id_int_1 := ev_rec.org_id(i);
2967 --{HYU transaction_date,legal_entity_id
2968 bld_ev_ent(l_build_ctr).transaction_date := ev_rec.transaction_date(i);
2969 bld_ev_ent(l_build_ctr).reference_num_1 := ev_rec.legal_entity_id(i);
2970 --}
2971 /*----------------------------------------------------+
2972 |Set the event GL Date |
2973 +----------------------------------------------------*/
2974 log('Set the event GL Date');
2975 IF (ev_rec.posttogl(i) = 'Y') THEN
2976 IF to_char(ev_rec.dist_gl_date(i),'DD-MM-YYYY')= '01-01-1900' THEN
2977 bld_ev_ent(l_build_ctr).event_date := '';
2978 ELSE
2979 bld_ev_ent(l_build_ctr).event_date := ev_rec.dist_gl_date(i);
2980 END IF;
2981 ELSIF (ev_rec.posttogl(i) = 'N') THEN
2982 IF to_char(ev_rec.dist_gl_date(i),'DD-MM-YYYY') = '01-01-1900' THEN
2983 --{Bug#3320427 None postable trx event_date should be 01-01-1900
2984 -- event date is mandatory for XLA
2985 bld_ev_ent(l_build_ctr).event_date := TO_DATE('01-01-1900','DD-MM-YYYY');
2986 --}
2987 ELSE
2988 bld_ev_ent(l_build_ctr).event_date := ev_rec.dist_gl_date(i);
2989 END IF;
2990 END IF; --post to gl condition to set gl date
2991
2992 /*----------------------------------------------------+
2993 |Set the event Status |
2994 +----------------------------------------------------*/
2995 IF ev_rec.pstid(i) <> -3 THEN
2996 -- P : PROCESSED
2997 bld_ev_ent(l_build_ctr).event_status_code := 'P';
2998
2999 ELSIF l_change_matrix IN ('1.02','1.04','1.05','1.06') THEN
3000 --Bug#3320427 exclude 1.15 for none postable trx creation)
3001 -- I : INCOMPLETE
3002 bld_ev_ent(l_build_ctr).event_status_code := 'I';
3003
3004 ELSIF l_change_matrix IN ('1.18','1.19','1.20','1.15') THEN
3005 --Bug#3320427 include 1.15 to create No Active event
3006 --for none postable transaction
3007 -- N : NOACTION
3008 bld_ev_ent(l_build_ctr).event_status_code := 'N';
3009
3010 ELSIF l_change_matrix IN ('1.08','1.10','1.11','1.12') THEN
3011 -- U : UNPROCESSED
3012 bld_ev_ent(l_build_ctr).event_status_code := 'U';
3013
3014 END IF; --set the event status
3015
3016 /*---------------------------------------------------+
3017 |Set the event Type |
3018 +---------------------------------------------------*/
3019 bld_ev_ent(l_build_ctr).event_type_code := ev_rec.override_event(i);
3020
3021 /*---------------------------------------------------+
3022 |Set the event Id to the temp internal ID, bld id |
3023 |cell needs to be populated for update, so make sure|
3024 |contiguous null cell is created. |
3025 +---------------------------------------------------*/
3026 bld_ev_ent(l_build_ctr).event_id := '';
3027
3028 dump_bld_rec(p_bld_rec => bld_ev_rec,
3029 p_i => l_build_ctr ,
3030 p_tag => 'bld_ev_rec');
3031
3032 dump_event_info(p_ev_info_tab => bld_ev_ent,
3033 p_i => l_build_ctr,
3034 p_tag => 'bld_ev_ent');
3035
3036 /*-----------------------------------------------------+
3037 |Override the current distributions matching event id |
3038 |and match temp event id with the actual event/temp id|
3039 +-----------------------------------------------------*/
3040
3041 END IF; --Insert event condition
3042
3043 /*---------------------------------------------------------------+
3044 |Abnormal conditions raise user defined exception. Typically |
3045 |the current trx should be skipped in upgrade mode. |
3046 | Remove : 1.11 When adding distributions to a posted document |
3047 | is situation can happen |
3048 | the document status is C |
3049 | the existing event status is P |
3050 | the match_flg is TRUE |
3051 +---------------------------------------------------------------*/
3052 IF (l_change_matrix IN ('1.05','1.06'
3053 --{BUG#399572
3054 --,1.15,1.18,1.21
3055 --}
3056 ,'1.12')
3057 AND (ev_match_flg)) THEN
3058
3059 --Bug 5600736 added the block below
3060 IF p_xla_ev_rec.xla_mode = 'O' AND ev_rec.pstid(i) <> -3
3061 AND l_change_matrix = '1.12' THEN
3062 null; --skip the distribution as distribution is posted
3063 ELSE
3064 RAISE abnormal_cond;
3065 END IF;
3066
3067 END IF; --abnormal condition
3068
3069 --{BUG#4414585 - dont do anything
3070 IF l_change_matrix IN ('3.01') THEN
3071 NULL;
3072 END IF;
3073 --}
3074
3075 /*----------------------------------------------------+
3076 |Sweep Trx Cache to raise validation if other dist's |
3077 |with the different gl dates exist for the same event|
3078 |Applicable to events 1.2, 1.4, 1.6, 1.10 for OLTP an|
3079 |SQL may require to be added. Row based operations. |
3080 |May be in update construct above. |
3081 +----------------------------------------------------*/
3082
3083 /*---------------------------------------------------------+
3084 |Add an event to the events cache table for the current |
3085 |transaction being processed. Get the hash index using |
3086 |hash function and ascertain if the event has been cached |
3087 +---------------------------------------------------------*/
3088
3089 /*-----------------------------------------------------------+
3090 |Set the previous row id of the distribution and trx id |
3091 |used to reinitialize caches or skip processing duplicate |
3092 |rows. |
3093 +-----------------------------------------------------------*/
3094 --prev_distid := ev_rec.dist_id(i); same dist is not reprocessed
3095 prev_trx_id := ev_rec.trx_id(i); --reinitalize trx cache
3096
3097 log('prev_trx_id = ' || prev_trx_id);
3098
3099 END LOOP; --process distributions
3100
3101 /*---------------------------------------------------------------------+
3102 |Call the xla events api passing it the tables for Bulk Insert, Update|
3103 |On return for inserted rows, the event_id will be returned and the |
3104 |distributions will be updated with this event id using the temp event|
3105 |id which will ascertain the mapping of internal id's to actual ids |
3106 |for a distribution.To be replaced by call to xla events api commit |
3107 |issued by owning product. |
3108 +---------------------------------------------------------------------*/
3109
3110 /*-------------------------------------------------------------+
3111 | Insert into Events table, to be replaced with XLA apis |
3112 +-------------------------------------------------------------*/
3113
3114 IF p_xla_ev_rec.xla_mode IN ('U','B') AND test_flag = 'N' THEN
3115
3116 log('xla_events_pub_pkg.create_bulk_events xla_mode IN (U,B)');
3117
3118 /*
3119 bld_ev_ent := xla_events_pub_pkg.create_bulk_events(
3120 p_application_id => 222 ,
3121 p_legal_entity_id => '1' ,-- to be set later
3122 p_ledger_id => arp_standard.sysparm.set_of_books_id ,-- to be set later
3123 p_entity_type_code => l_entity_code ,-- to be set later
3124 p_array_entity_event_info => bld_ev_ent);
3125 -- p_valuation_method => '' ,
3126 -- p_security_context => l_security_context);
3127 */
3128 /* As this call is not suitable to AR, the bug for XLA spi enhancement has been logged
3129 BUG#4448003 for now converting this call to single event call
3130 xla_events_pub_pkg.create_bulk_events(
3131 p_source_application_id => NULL ,
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);
3136 */
3137 cnt := bld_ev_ent.COUNT;
3138 IF cnt > 0 THEN
3139 FOR m IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
3140 INSERT INTO xla_events_int_gt (
3141 APPLICATION_ID
3142 , LEGAL_ENTITY_ID
3143 , LEDGER_ID
3144 , ENTITY_CODE
3145 , TRANSACTION_NUMBER
3146 , SOURCE_ID_INT_1
3147 , EVENT_TYPE_CODE
3148 , EVENT_STATUS_CODE
3149 , EVENT_DATE
3150 , SECURITY_ID_INT_1
3151 , TRANSACTION_DATE )
3152 VALUES (
3153 222
3154 , bld_ev_ent(m).reference_num_1 -- LEGAL_ENTITY_ID
3155 , arp_standard.sysparm.set_of_books_id -- LEDGER_ID
3156 , l_entity_code -- ENTITY_CODE
3157 , bld_ev_ent(m).transaction_number -- TRANSACTION_NUMBER
3158 , bld_ev_ent(m).source_id_int_1 -- SOURCE_ID_INT_1
3159 , bld_ev_ent(m).event_type_code -- EVENT_TYPE_CODE
3160 , bld_ev_ent(m).event_status_code -- EVENT_STATUS_CODE
3161 , bld_ev_ent(m).event_date -- EVENT_DATE
3162 , bld_ev_ent(m).security_id_int_1 -- SECURITY_ID_INT_1
3163 , bld_ev_ent(m).transaction_date); -- TRANSACTION_DATE
3164 END LOOP;
3165 END IF;
3166
3167 log('Bulk Mode not ready - using OLTP');
3168
3169 END IF;
3170
3171 /*--------------------------------------------------------------+
3172 |Bulk update the distributions already existing in the Database|
3173 |with the modified gl date or status, if unchanged these should|
3174 |retain their default original values in the assignments below.|
3175 +--------------------------------------------------------------*/
3176
3177 IF p_xla_ev_rec.xla_mode = 'O'
3178 --{Work around waiting for bulk mode
3179 -- OR p_xla_ev_rec.xla_mode = 'B'
3180 --}
3181 THEN
3182
3183 IF bld_ev_ent.COUNT > 0 THEN
3184
3185 FOR m IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
3186
3187 /*----------------------------------------------------------+
3188 |Set the event source details |
3189 +----------------------------------------------------------*/
3190 l_event_source_info.application_id := 222;
3191 --{HYU transaction_date,legal_entity_id
3192 l_event_source_info.legal_entity_id := bld_ev_ent(l_build_ctr).reference_num_1; -- --to be set
3193 --}
3194 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
3195 l_event_source_info.entity_type_code := l_entity_code ;
3196 l_event_source_info.transaction_number := bld_ev_ent(m).transaction_number;
3197 l_event_source_info.source_id_int_1 := bld_ev_ent(m).source_id_int_1;
3198
3199 dump_event_source_info
3200 (x_ev_source_info => l_event_source_info);
3201
3202 /*----------------------------------------------------------+
3203 |Set the security details |
3204 +----------------------------------------------------------*/
3205 l_security_context.security_id_int_1 := bld_ev_ent(m).security_id_int_1;
3206 /*----------------------------------------------------------+
3207 |Set the event reference details |
3208 +----------------------------------------------------------*/
3209 --to be ascertained after events template is filled
3210
3211 IF bld_ev_rec.bld_dml_flag(m) = 'I' AND test_flag = 'N' THEN
3212
3213 log('XLA_EVENTS_PUB_PKG.create_event');
3214
3215 z := z + 1;
3216 log('hyu calling create event for zth time :'||z);
3217
3218 lnb := XLA_EVENTS_PUB_PKG.create_event(
3219 p_event_source_info => l_event_source_info ,
3220 p_event_type_code => bld_ev_ent(m).event_type_code ,
3221 p_event_date => bld_ev_ent(m).event_date ,
3222 p_event_status_code => bld_ev_ent(m).event_status_code ,
3223 p_event_number => NULL ,
3224 p_reference_info => l_reference_info ,
3225 p_valuation_method => '' ,
3226 --{HYU transaction_date
3227 p_transaction_date => bld_ev_ent(m).transaction_date ,
3228 --}
3229 p_security_context => l_security_context );
3230
3231 ELSIF bld_ev_rec.bld_dml_flag(m) = 'U' AND test_flag = 'N' THEN
3232
3233 log('XLA_EVENTS_PUB_PKG.update_event');
3234
3235 log('bld_ev_ent(m).event_id :'||bld_ev_ent(m).event_id );
3236 log('bld_ev_ent(m).event_type_code :'||bld_ev_ent(m).event_type_code );
3237 log(' bld_ev_ent(m).event_date :'|| bld_ev_ent(m).event_date );
3238 log(' bld_ev_ent(m).event_status_code :'|| bld_ev_ent(m).event_status_code);
3239 log(' p_security_context.security_id_int_1 :'|| l_security_context.security_id_int_1 );
3240
3241 XLA_EVENTS_PUB_PKG.update_event(
3242 p_event_source_info => l_event_source_info ,
3243 p_event_id => bld_ev_ent(m).event_id ,
3244 p_event_type_code => bld_ev_ent(m).event_type_code ,
3245 p_event_date => bld_ev_ent(m).event_date ,
3246 p_event_status_code => bld_ev_ent(m).event_status_code ,
3247 p_valuation_method => '' ,
3248 p_security_context => l_security_context );
3249
3250 -- Checking change of transaction number for Copy Document Sequence Feature
3251
3252 IF NVL(l_entity_code,' ') = 'TRANSACTIONS' THEN
3253
3254 log(' Checking if Trxn number has changed by Copy Document Sequence Feature');
3255
3256 BEGIN
3257 SELECT a.transaction_number INTO trxn_number
3258 FROM xla_transaction_entities_upg a,
3259 xla_events b
3260 WHERE NVL(a.source_id_int_1,-99) = bld_ev_ent(m).source_id_int_1
3261 AND b.event_id = bld_ev_ent(m).event_id
3262 AND a.entity_id = b.entity_id
3263 AND a.security_id_int_1 = bld_ev_ent(m).security_id_int_1
3264 AND a.application_id = 222;
3265
3266 EXCEPTION
3267 WHEN OTHERS THEN
3268 log('EXCEPTION: XLA TRANSACTION NUMBER UPDATE');
3269 log('SQLERRM ' || SQLERRM);
3270 RAISE;
3271 END;
3272
3273 IF NVL(trxn_number,-99) <> bld_ev_ent(m).transaction_number THEN
3274 XLA_EVENTS_PUB_PKG.UPDATE_TRANSACTION_NUMBER(
3275 p_event_source_info => l_event_source_info,
3276 p_transaction_number => bld_ev_ent(m).transaction_number,
3277 p_valuation_method => '',
3278 p_security_context => l_security_context ,
3279 p_event_id => bld_ev_ent(m).event_id );
3280 END IF;
3281 END IF; -- end checking change of trxn number for transactions
3282 END IF; --end insert or update events in OLTP mode
3283
3284 END LOOP;
3285
3286 END IF;
3287
3288 END IF;
3289
3290 /*----------------------------------------------------------+
3291 |Denormalize the event id which has been inserted into the |
3292 |database, and update the event id column in the dist table|
3293 |This denormalization is used by the extract process. |
3294 |The internal negative id i.e. temp_event_id is used to |
3295 |ascertain as to which event. |
3296 +----------------------------------------------------------*/
3297 --Only used for upgrade
3298 IF p_xla_ev_rec.xla_mode = 'U' THEN
3299 Commit;
3300 END IF; --mode is Upgrade, Oltp or Batch
3301
3302 -- Exit from the loop if no. of rows fetched < array size
3303 EXIT WHEN l_rows_fetched < MAX_ARRAY_SIZE;
3304
3305 END LOOP; --Array (Bulk) Fetch
3306
3307 --{XLA BULK API
3308 IF p_xla_ev_rec.xla_mode = 'B' THEN
3309 OPEN for_batch;
3310 FETCH for_batch INTO l_test;
3311 IF for_batch%FOUND THEN
3312 log('Calling xla_events_pub_pkg.create_bulk_events +');
3313 xla_events_pub_pkg.create_bulk_events
3314 (p_application_id => 222,
3315 p_ledger_id => arp_standard.sysparm.set_of_books_id,
3316 p_entity_type_code => l_entity_code);
3317 --avoid recreation of successfully events
3318 DELETE from xla_events_int_gt WHERE entity_id IS NOT NULL;
3319 log('Calling xla_events_pub_pkg.create_bulk_events -');
3320 END IF;
3321 CLOSE for_batch;
3322 END IF;
3323 --}
3324 /*--------------------------------------------------------------+
3325 |Bulk update the distributions event ids with the newly created|
3326 |event ids as part of the mark transaction data associated with|
3327 |the event. |
3328 +--------------------------------------------------------------*/
3329
3330 <<endlabel>>
3331 log('ARP_XLA_EVENTS.Create_All_Events ()-');
3332
3333 EXCEPTION
3334 WHEN OTHERS THEN
3335 log('EXCEPTION: ARP_XLA_EVENTS.Create_All_Events ');
3336 log('SQLERRM ' || SQLERRM);
3337 log('EXCEPTION: ARP_XLA_EVENTS.Create_All_Events ');
3338 log('SQLERRM ' || SQLERRM);
3339 RAISE;
3340 END Create_All_Events;
3341
3342 /*========================================================================
3343 | PRIVATE PROCEDURE un_denormalize_posting_entity
3344 |
3345 | DESCRIPTION
3346 | Purpose : Erase the event_id on the distributions.
3347 | It determines the posting entity on each the event_id should be erase.
3348 |
3349 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3350 | Create_all_events
3351 |
3352 | CALLS PROCEDURES/FUNCTIONS
3353 |
3354 | PARAMETERS p_override_event generated by SQL dynamic
3355 | p_trx_type transaction type
3356 | p_exist_event_type existing event type
3357 | p_event_id event_id to be erase on distributions
3358 |
3359 | KNOWN ISSUES
3360 |
3361 | NOTES
3362 |
3363 | MODIFICATION HISTORY
3364 | Date Author Description of Changes
3365 | 02-OCT-2002 H. Yu
3366 *=======================================================================*/
3367 PROCEDURE un_denormalize_posting_entity
3368 ( p_xla_doc IN VARCHAR2,
3369 p_event_id IN NUMBER ) IS
3370
3371 BEGIN
3372 log('arp_xla_events.un_denormalize_posting_entity()+');
3373
3374 IF test_flag = 'Y' THEN
3375 GOTO endlabel;
3376 END IF;
3377
3378 IF p_xla_doc IN ('CT','CTCMAPP','CTNORCM') THEN
3379
3380 UPDATE ra_cust_trx_line_gl_dist
3381 SET event_id = NULL
3382 WHERE event_id = p_event_id;
3383
3384 ELSIF p_xla_doc IN ('CRH', 'CRHMCD', 'CRHAPP') THEN
3385
3386 UPDATE ar_cash_receipt_history
3387 SET event_id = NULL
3388 WHERE event_id = p_event_id;
3389
3390 ELSIF p_xla_doc = 'ADJ' THEN
3391
3392 UPDATE ar_adjustments
3393 SET event_id = NULL
3394 WHERE event_id = p_event_id;
3395
3396 ELSIF p_xla_doc IN ('CRHAPP', 'APP') THEN
3397
3398 UPDATE ar_receivable_applications
3399 SET event_id = NULL
3400 WHERE event_id = p_event_id;
3401
3402 ELSIF p_xla_doc IN ('CRHMCD', 'MCD') THEN
3403
3404 UPDATE ar_misc_cash_distributions
3405 SET event_id = NULL
3406 WHERE event_id = p_event_id;
3407
3408 ELSIF p_xla_doc = 'TRH' THEN
3409
3410 UPDATE ar_transaction_history
3411 SET event_id = NULL
3412 WHERE event_id = p_event_id;
3413
3414 END IF;
3415
3416 <<endlabel>>
3417
3418 log('arp_xla_events.un_denormalize_posting_entity()-');
3419
3420 EXCEPTION
3421 WHEN OTHERS THEN
3422 log('EXCEPTION: arp_xla_events.un_denormalize_posting_entity');
3423 log('SQLERRM '||sqlerrm);
3424 RAISE;
3425
3426 END un_denormalize_posting_entity;
3427
3428 /*========================================================================
3429 | Private Function: is_one_acct_asg_on_ctlgd
3430 |
3431 | Description :
3432 | Function only work for OLTP mode because it needs a org context.
3433 | If necessary it is extensible for other mode
3434 | Return
3435 | + 'Y' if at least one invoice line has its account assignment created.
3436 | + 'N' if no line has its account assignment created - Typically when a
3437 | invoice with rules is completed without having been submitted to
3438 | the revenue recognition process.
3439 | + 'X' if the invoice p_posting_entity is not CTLGD means the accounting
3440 | model does not use CTLGD.
3441 |
3442 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3443 | Create_all_events
3444 |
3445 | CALLS PROCEDURES/FUNCTIONS
3446 |
3447 | Parameters :
3448 | 1) p_invoice_id Customer_trx_id
3449 | 2) p_posting_entity Transaction_type
3450 | 3) p_mode 'O' OLTP only
3451 |
3452 | KNOWN ISSUES
3453 |
3454 | NOTES
3455 |
3456 | MODIFICATION HISTORY
3457 | Date Author Description of Changes
3458 | 02-OCT-2002 H. Yu
3459 *=======================================================================*/
3460 FUNCTION is_one_acct_asg_on_ctlgd
3461 (p_invoice_id IN NUMBER,
3462 p_posting_entity IN VARCHAR2 DEFAULT 'CTLGD',
3463 p_mode IN VARCHAR2 DEFAULT 'O') RETURN VARCHAR2
3464 IS
3465
3466 CURSOR cu_is_rev_rec_run IS
3467 SELECT 'Y'
3468 FROM ra_customer_trx_lines ctl
3469 WHERE ctl.customer_trx_id = p_invoice_id
3470 AND ctl.line_type = 'LINE'
3471 AND NVL(ctl.autorule_complete_flag,'Y') <> 'N';
3472
3473 ltab DBMS_SQL.VARCHAR2_TABLE;
3474 lres VARCHAR2(1);
3475
3476 BEGIN
3477 IF p_posting_entity <> 'CTLGD' THEN
3478 lres := 'X';
3479 ELSE
3480 OPEN cu_is_rev_rec_run;
3481 FETCH cu_is_rev_rec_run INTO lres;
3482 IF cu_is_rev_rec_run%NOTFOUND THEN
3483 -- None line has its account assignments created
3484 lres := 'N';
3485 -- Otherwise at least one line has its account
3486 -- assignments created the result will be 'Y'
3487 END IF;
3488 CLOSE cu_is_rev_rec_run;
3489 END IF;
3490 RETURN lres;
3491
3492 EXCEPTION
3493 WHEN OTHERS THEN
3494 IF cu_is_rev_rec_run%ISOPEN THEN CLOSE cu_is_rev_rec_run; END IF;
3495 RAISE;
3496
3497 END;
3498
3499
3500 /*========================================================================
3501 | PUBLIC PROCEDURE Upd_Dist
3502 |
3503 | DESCRIPTION
3504 | Denormalizes the event id for Receivables documents
3505 |
3506 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3507 | Execute
3508 |
3509 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3510 |
3511 | PARAMETERS
3512 | p_xla_ev_rec IN xla_events_type
3513 |
3514 | KNOWN ISSUES
3515 |
3516 | NOTES
3517 |
3518 | MODIFICATION HISTORY
3519 | Date Author Description of Changes
3520 *=======================================================================*/
3521 PROCEDURE Upd_Dist(p_xla_ev_rec IN xla_events_type) IS
3522
3523 type l_rowid_type IS TABLE OF VARCHAR2(50)
3524 INDEX BY BINARY_INTEGER;
3525 type l_event_id_type IS TABLE OF NUMBER(15)
3526 INDEX BY BINARY_INTEGER;
3527
3528 ev_rec ev_rec_type;
3529 empty_ev_rec ev_rec_type;
3530
3531 l_rowid l_rowid_type;
3532 l_event_id l_event_id_type;
3533
3534 l_last_fetch BOOLEAN := FALSE;
3535
3536 l_select_c INTEGER;
3537
3538 l_ignore INTEGER;
3539 l_rows_fetched VARCHAR2(10);
3540 l_low INTEGER:=0;
3541 l_high INTEGER:=0;
3542 --6785758
3543 l_last_updated_by NUMBER := arp_standard.profile.user_id;
3544 l_last_update_login NUMBER := arp_standard.profile.last_update_login;
3545 BEGIN
3546
3547 log('ARP_XLA_EVENTS.Upd_Dist()+');
3548
3549 IF p_xla_ev_rec.xla_call IN ('D','B') THEN
3550 null;
3551 ELSE goto endlabel;
3552 END IF;
3553
3554 l_select_c := Get_Select_Cursor(p_xla_ev_rec => p_xla_ev_rec,
3555 p_call_point => 2);
3556
3557 l_ignore := dbms_sql.execute(l_select_c);
3558
3559 log( 'Fetching select stmt');
3560
3561 LOOP -- Main Cursor Loop
3562
3563 ev_rec.dist_row_id := empty_ev_rec.dist_row_id;
3564 ev_rec.dist_event_id := empty_ev_rec.dist_event_id;
3565
3566 l_rows_fetched := dbms_sql.fetch_rows(l_select_c);
3567
3568 log('Rows Fetched are ' || l_rows_fetched);
3569
3570 l_low := l_high + 1;
3571 l_high:= l_high + l_rows_fetched;
3572
3573 IF l_rows_fetched > 0 THEN
3574
3575 log('Fetched a row ');
3576 log('l_low ' || l_low);
3577 log('l_high ' || l_high);
3578
3579 get_column_values(p_select_c => l_select_c,
3580 p_xla_ev_rec => p_xla_ev_rec,
3581 p_ev_rec => ev_rec,
3582 p_call_point => 2);
3583
3584 -- no more rows to fetch
3585 IF l_rows_fetched < MAX_ARRAY_SIZE THEN
3586 log('Done fetching 3');
3587
3588 IF( dbms_sql.is_open( l_select_c) ) THEN
3589 dbms_sql.close_cursor( l_select_c );
3590 END IF;
3591
3592 END IF; --no more rows to fetch
3593
3594 ELSE --if rows fetched = 0
3595 log('Done fetching 4');
3596
3597 IF( dbms_sql.is_open( l_select_c ) ) THEN
3598 dbms_sql.close_cursor( l_select_c );
3599 END IF;
3600
3601 EXIT;
3602
3603 END IF; --rows fetched greater than 0
3604
3605 log('Commence bulk update processing');
3606
3607 IF (p_xla_ev_rec.xla_doc_table IN ('CT','CTNORCM')
3608 OR (p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND g_call_number = 2))
3609 AND test_flag = 'N' THEN
3610
3611 log('Bulk Updating Transactions');
3612
3613 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3614 --6785758
3615 UPDATE ra_cust_trx_line_gl_dist_all ctlgd
3616 SET ctlgd.event_id = ev_rec.dist_event_id(m),
3617 ctlgd.last_update_date = TRUNC(SYSDATE),
3618 ctlgd.last_update_login = l_last_update_login,
3619 ctlgd.last_updated_by = l_last_updated_by
3620 WHERE ctlgd.rowid = ev_rec.dist_row_id(m);
3621
3622 END IF;
3623
3624 IF p_xla_ev_rec.xla_doc_table = 'ADJ' AND test_flag = 'N' THEN
3625 log('Bulk Updating Adjustments ');
3626 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3627 UPDATE ar_adjustments_all adj
3628 SET adj.event_id = ev_rec.dist_event_id(m),
3629 adj.last_update_date = TRUNC(SYSDATE),
3630 adj.last_update_login = l_last_update_login,
3631 adj.last_updated_by = l_last_updated_by
3632 WHERE adj.rowid = ev_rec.dist_row_id(m);
3633 END IF;
3634
3635 IF p_xla_ev_rec.xla_doc_table = 'CRH' AND test_flag = 'N' THEN
3636 log('Bulk Updating Cash Receipt History');
3637 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3638 --6785758
3639 UPDATE ar_cash_receipt_history_all crh
3640 SET crh.event_id = ev_rec.dist_event_id(m),
3641 crh.last_update_date = TRUNC(SYSDATE),
3642 crh.last_update_login = l_last_update_login,
3643 crh.last_updated_by = l_last_updated_by
3644 WHERE crh.rowid = ev_rec.dist_row_id(m);
3645 END IF;
3646
3647 IF p_xla_ev_rec.xla_doc_table = 'MCD' AND test_flag = 'N' THEN
3648 log('Bulk Updating misc cash distributions');
3649 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3650 --6785758
3651 UPDATE ar_misc_cash_distributions_all mcd
3652 SET mcd.event_id = ev_rec.dist_event_id(m),
3653 mcd.last_update_date = TRUNC(SYSDATE),
3654 mcd.last_update_login = l_last_update_login,
3655 mcd.last_updated_by = l_last_updated_by
3656 WHERE mcd.rowid = ev_rec.dist_row_id(m);
3657 END IF;
3658
3659 IF (p_xla_ev_rec.xla_doc_table IN ('APP', 'CMAPP')
3660 OR ( p_xla_ev_rec.xla_doc_table = 'CTCMAPP' AND g_call_number = 1))
3661 AND test_flag = 'N' THEN
3662 log('Bulk Updating receivable applications');
3663 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3664 --6785758
3665 UPDATE ar_receivable_applications_all app
3666 SET app.event_id = ev_rec.dist_event_id(m),
3667 app.last_update_date = TRUNC(SYSDATE),
3668 app.last_update_login = l_last_update_login,
3669 app.last_updated_by = l_last_updated_by
3670 WHERE app.rowid = ev_rec.dist_row_id(m);
3671 END IF;
3672
3673 IF p_xla_ev_rec.xla_doc_table = 'TRH' AND test_flag = 'N' THEN
3674 log('Bulk Updating Bills Receivable transaction history ');
3675 FORALL m IN ev_rec.dist_row_id.FIRST .. ev_rec.dist_row_id.LAST
3676 --6785758
3677 UPDATE ar_transaction_history_all trh
3678 SET trh.event_id = ev_rec.dist_event_id(m),
3679 trh.last_update_date = TRUNC(SYSDATE),
3680 trh.last_update_login = l_last_update_login,
3681 trh.last_updated_by = l_last_updated_by
3682 WHERE trh.rowid = ev_rec.dist_row_id(m);
3683 END IF;
3684
3685 --Only used for upgrade
3686 IF p_xla_ev_rec.xla_mode = 'U' THEN
3687 Commit;
3688 END IF; --mode is Upgrade, Oltp or Batch
3689
3690 --Exit from the loop if no. of rows fetched < array size
3691 EXIT WHEN l_rows_fetched < MAX_ARRAY_SIZE;
3692
3693 END LOOP; --Array (Bulk) Fetch
3694
3695 <<endlabel>>
3696 log('ARP_XLA_EVENTS.Upd_Dist()-');
3697
3698 EXCEPTION
3699 WHEN OTHERS THEN
3700 log('EXCEPTION: ARP_XLA_EVENTS.Upd_Dist');
3701 RAISE;
3702
3703 END Upd_Dist;
3704
3705 /*========================================================================
3706 | PUBLIC PROCEDURE dump_ev_rec
3707 |
3708 | DESCRIPTION
3709 | Dumps the event record fetched for creation of events
3710 |
3711 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3712 | Create_All_Events
3713 |
3714 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3715 |
3716 | PARAMETERS
3717 | p_xla_ev_rec IN event record
3718 | p_i IN index
3719 |
3720 | KNOWN ISSUES
3721 |
3722 | NOTES
3723 |
3724 | MODIFICATION HISTORY
3725 | Date Author Description of Changes
3726 *=======================================================================*/
3727 PROCEDURE dump_ev_rec(p_ev_rec IN OUT NOCOPY ev_rec_type,
3728 p_i IN BINARY_INTEGER) IS
3729
3730 BEGIN
3731
3732 log(' ');
3733 log('ARP_XLA_EVENTS.dump_ev_rec()+');
3734
3735 IF p_ev_rec.trx_status.EXISTS(p_i) THEN
3736
3737 log('p_ev_rec.trx_status(' || p_i || ') = '
3738 || p_ev_rec.trx_status(p_i));
3739 log('p_ev_rec.trx_id(' || p_i || ') = '
3740 || p_ev_rec.trx_id(p_i));
3741 log('p_ev_rec.dist_event_id(' || p_i || ') = '
3742 || p_ev_rec.dist_event_id(p_i));
3743 log('p_ev_rec.dist_gl_date(' || p_i || ') = '
3744 || p_ev_rec.dist_gl_date(p_i));
3745 log('p_ev_rec.trx_type(' || p_i || ') = '
3746 || p_ev_rec.trx_type(p_i));
3747 log('p_ev_rec.posttogl(' || p_i || ') = '
3748 || p_ev_rec.posttogl(p_i));
3749 log('p_ev_rec.ev_match_event_id(' || p_i || ') = '
3750 || p_ev_rec.ev_match_event_id(p_i));
3751 log('p_ev_rec.ev_match_gl_date(' || p_i || ') = '
3752 || p_ev_rec.ev_match_gl_date(p_i));
3753 log('p_ev_rec.ev_match_status(' || p_i || ') = '
3754 || p_ev_rec.ev_match_status(p_i));
3755 log('p_ev_rec.ev_match_type(' || p_i || ') = '
3756 || p_ev_rec.ev_match_type(p_i));
3757 END IF;
3758
3759 log('ARP_XLA_EVENTS.dump_ev_rec()-');
3760 log(' ');
3761
3762 EXCEPTION
3763 WHEN OTHERS THEN
3764 log('EXCEPTION: ARP_XLA_EVENTS.dump_ev_rec');
3765 RAISE;
3766 END dump_ev_rec;
3767
3768
3769 --HYU
3770 PROCEDURE dump_event_source_info
3771 (x_ev_source_info IN OUT NOCOPY xla_events_pub_pkg.t_event_Source_info)
3772 IS
3773 BEGIN
3774
3775 log(' ');
3776 log('ARP_XLA_EVENTS.dump_event_source_info()+');
3777
3778 log('x_ev_source_info.application_id = '
3779 || x_ev_source_info.application_id);
3780 log('x_ev_source_info.legal_entity_id = '
3781 || x_ev_source_info.legal_entity_id);
3782 log('x_ev_source_info.ledger_id = '
3783 || x_ev_source_info.ledger_id);
3784 log('x_ev_source_info.entity_type_code = '
3785 || x_ev_source_info.entity_type_code);
3786 log('x_ev_source_info.transaction_number = '
3787 || x_ev_source_info.transaction_number);
3788 log('x_ev_source_info.source_id_int_1 = '
3789 || x_ev_source_info.source_id_int_1);
3790 log('x_ev_source_info.source_id_int_2 = '
3791 || x_ev_source_info.source_id_int_2);
3792 log('x_ev_source_info.source_id_int_3 = '
3793 || x_ev_source_info.source_id_int_3);
3794 log('x_ev_source_info.source_id_int_4 = '
3795 || x_ev_source_info.source_id_int_4);
3796 log('x_ev_source_info.source_id_char_1 = '
3797 || x_ev_source_info.source_id_char_1);
3798 log('x_ev_source_info.source_id_char_2 = '
3799 || x_ev_source_info.source_id_char_2);
3800 log('x_ev_source_info.source_id_char_3 = '
3801 || x_ev_source_info.source_id_char_3);
3802 log('x_ev_source_info.source_id_char_4 = '
3803 || x_ev_source_info.source_id_char_4);
3804 log('x_ev_source_info.legal_entity_id = '
3805 || x_ev_source_info.legal_entity_id);
3806 log('ARP_XLA_EVENTS.dump_event_source_info()-');
3807 log(' ');
3808
3809 EXCEPTION
3810 WHEN OTHERS THEN
3811 log('EXCEPTION: ARP_XLA_EVENTS.dump_event_source_info');
3812 RAISE;
3813 END dump_event_source_info;
3814
3815 /*========================================================================
3816 | PUBLIC FUNCTION Change_Matrix
3817 |
3818 | DESCRIPTION
3819 | Decision matix which returns a number stating whether an update,
3820 | insert or latch to an event is required.
3821 |
3822 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3823 | Execute
3824 |
3825 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3826 |
3827 | PARAMETERS
3828 | trx_status IN Transaction status
3829 | dist_gl_date IN gldate of distribution
3830 | ev_match_gl_date IN matching or existing event accountin date
3831 | ev_match_status IN event status
3832 | post_to_gl IN post to Gl
3833 |
3834 | KNOWN ISSUES
3835 |
3836 | NOTES
3837 |
3838 | MODIFICATION HISTORY
3839 | Date Author Description of Changes
3840 |
3841 *=======================================================================*/
3842 FUNCTION Change_Matrix(trx_status IN VARCHAR2 ,
3843 dist_gl_date IN DATE ,
3844 ev_match_gl_date IN DATE ,
3845 ev_match_status IN xla_events.event_status_code%TYPE,
3846 posttogl IN VARCHAR2) RETURN VARCHAR2 IS
3847
3848 l_change_matrix VARCHAR2(30);
3849
3850 BEGIN
3851
3852 log('ARP_XLA_EVENTS.Change_Matrix()+');
3853 log('trx_status :'||trx_status);
3854 log('dist_gl_date :'||dist_gl_date);
3855 log('ev_match_gl_date :'||ev_match_gl_date);
3856 log('ev_match_status :'||ev_match_status);
3857 log('posttogl :'||posttogl);
3858
3859 IF posttogl = 'Y' THEN
3860 log('posttogl' || posttogl);
3861 /*-------------------------------------------------------+
3862 |1.01 - Current trx status = Incomplete |
3863 | Current Gl date = Existing Event Gl Date |
3864 | Existing Event Status = Incomplete |
3865 +-------------------------------------------------------*/
3866 IF trx_status = 'I'
3867 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3868 AND ev_match_status = 'I' THEN
3869 l_change_matrix := '1.01';
3870
3871 /*------------------------------------------------------+
3872 |1.02 - Current trx status = Incomplete |
3873 | Current Gl date <> Existing Event Gl Date|
3874 | Existing Event Status = Incomplete |
3875 +------------------------------------------------------*/
3876 ELSIF trx_status = 'I'
3877 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3878 AND ev_match_status = 'I' THEN
3879 l_change_matrix := '1.02';
3880
3881 /*-------------------------------------------------------+
3882 |1.03 - Current trx status = Incomplete |
3883 | Current Gl date = Existing Event Gl Date |
3884 | Existing Event Status = Unprocessed |
3885 +-------------------------------------------------------*/
3886 ELSIF trx_status = 'I'
3887 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3888 AND ev_match_status = 'U' THEN
3889 l_change_matrix := '1.03';
3890
3891 /*-------------------------------------------------------+
3892 |1.04 - Current trx status = Incomplete |
3893 | Current Gl date <> Existing Event Gl Date |
3894 | Existing Event Status = Unprocessed |
3895 +-------------------------------------------------------*/
3896 ELSIF trx_status = 'I'
3897 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3898 AND ev_match_status = 'U' THEN
3899 l_change_matrix := '1.04';
3900
3901 /*-------------------------------------------------------+
3902 |1.05 - Current trx status = Incomplete |
3903 | Existing Event Status = Processed |
3904 +-------------------------------------------------------*/
3905 ELSIF trx_status = 'I'
3906 AND ev_match_status = 'P' THEN
3907 l_change_matrix := '1.05';
3908
3909 /*-------------------------------------------------------+
3910 |1.06 - Current trx status = Incomplete |
3911 | Existing Event Gl Date = NULL |
3912 | Existing Event Status = NULL |
3913 +-------------------------------------------------------*/
3914 ELSIF trx_status = 'I'
3915 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
3916 --{BUG#4414585 -- Meaning the GL_DATE has been defined
3917 -- because in the case of signed BR a postable transaction can have no
3918 -- gl_date defined at creation time but xla_event creation the gl_date is
3919 -- required column, hence 1.06 for event creation should only be possible
3920 -- if the gl_date has been provided
3921 AND to_char(dist_gl_date,'DD-MM-YYYY') <> '01-01-1900'
3922 AND ev_match_status = 'X' THEN
3923 l_change_matrix := '1.06';
3924
3925 -- In the case a postable transaction with gl_date is created, there are 2 options
3926 -- either not create any event or create a event with a dummy gl date
3927 -- testing not to create any event change matrix value is 3.01
3928 ELSIF trx_status = 'I'
3929 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
3930 AND to_char(dist_gl_date,'DD-MM-YYYY') = '01-01-1900'
3931 AND ev_match_status = 'X' THEN
3932 l_change_matrix := '3.01';
3933 --}
3934 /*-------------------------------------------------------+
3935 |1.06 - Current trx status = Incomplete |
3936 | Existing Event Gl Date = NULL |
3937 | Existing Event Status = NULL |
3938 +-------------------------------------------------------*/
3939
3940
3941
3942 /*-------------------------------------------------------+
3943 |1.07 - Current trx status = Complete |
3944 | Current Gl date = Existing Event Gl Date |
3945 | Existing Event Status = Incomplete |
3946 +-------------------------------------------------------*/
3947 ELSIF trx_status = 'C'
3948 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3949 AND ev_match_status = 'I' THEN
3950 l_change_matrix := '1.07';
3951
3952 /*-------------------------------------------------------+
3953 |1.08 - Current trx status = Complete |
3954 | Current Gl date <> Existing Event Gl Date |
3955 | Existing Event Status = Incomplete |
3956 +-------------------------------------------------------*/
3957 ELSIF trx_status = 'C'
3958 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3959 AND ev_match_status = 'I' THEN
3960 l_change_matrix := '1.08';
3961
3962 /*-------------------------------------------------------+
3963 |1.09 - Current trx status = Complete |
3964 | Current Gl date = Existing Event Gl Date |
3965 | Existing Event Status = Unprocessed |
3966 +-------------------------------------------------------*/
3967 ELSIF trx_status = 'C'
3968 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3969 AND ev_match_status = 'U' THEN
3970 l_change_matrix := '1.09';
3971
3972 /*------------------------------------------------------+
3973 |1.10- Current trx status = Complete |
3974 | Current Gl date <> Existing Event Gl Date |
3975 | Existing Event Status = Unprocessed |
3976 +------------------------------------------------------*/
3977 ELSIF trx_status = 'C'
3978 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
3979 AND ev_match_status = 'U' THEN
3980 l_change_matrix := '1.10';
3981
3982 /*------------------------------------------------------+
3983 |1.11- Current trx status = Complete |
3984 | Existing Event Status = Processed |
3985 +------------------------------------------------------*/
3986 ELSIF trx_status = 'C'
3987 AND ev_match_status = 'P' THEN
3988 l_change_matrix := '1.11';
3989
3990 /*------------------------------------------------------+
3991 |1.12- Current trx status = Complete |
3992 | Existing Event Gl Date is null |
3993 | Existing Event Status is null |
3994 +------------------------------------------------------*/
3995 ELSIF trx_status = 'C'
3996 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
3997 AND ev_match_status = 'X' THEN
3998 l_change_matrix := '1.12';
3999
4000 --{BUG#3320427
4001 /*----------------------------------------------------+
4002 | 1.22 - Current trx status = Incomplete |
4003 | Current CL date is NOT NULL or <> (01-01-1900)|
4004 | Exist Event date is NULL or (01-01-1900) |
4005 | Existing Event Status = No Action |
4006 +----------------------------------------------------*/
4007 ELSIF trx_status = 'I'
4008 AND ev_match_status = 'N'
4009 AND to_char(dist_gl_date, 'DD-MM-YYYY') <> '01-01-1900'
4010 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4011 THEN
4012 l_change_matrix := '1.22';
4013 --}
4014 END IF; --change matrix setup for postable distributions
4015
4016 ELSIF posttogl = 'N' THEN
4017 /*-------------------------------------------------------+
4018 |1.13 - Current trx status = Incomplete |
4019 | Current Gl date = null = Existing Event Gl Date|
4020 | Existing Event Status = Incomplete |
4021 +-------------------------------------------------------*/
4022 IF trx_status = 'I'
4023 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4024 AND (to_char(ev_match_gl_date,'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4025 AND ev_match_status = 'I' THEN
4026 l_change_matrix := '1.13';
4027
4028 /*-------------------------------------------------------+
4029 |1.14 - Current trx status = Incomplete |
4030 | Current Gl date = null = Existing Event Gl Date|
4031 | Existing Event Status = Noaction |
4032 +-------------------------------------------------------*/
4033 ELSIF trx_status = 'I'
4034 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4035 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4036 AND ev_match_status = 'N' THEN
4037 l_change_matrix := '1.14';
4038
4039 /*-------------------------------------------------------+
4040 |1.15 - Current trx status = Incomplete |
4041 | Current Gl date = null = Existing Event Gl Date|
4042 | Existing Event Status = null |
4043 +-------------------------------------------------------*/
4044 ELSIF trx_status = 'I'
4045 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4046 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4047 AND ev_match_status = 'X' THEN
4048 l_change_matrix := '1.15';
4049
4050 /*------------------------------------------------------+
4051 |1.16 - Current trx status = Complete |
4052 | Current Gl date = null = Existing Event Gl Date|
4053 | Existing Event Status = Incomplete |
4054 +------------------------------------------------------*/
4055 ELSIF trx_status = 'C'
4056 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4057 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4058 AND ev_match_status = 'I' THEN
4059 l_change_matrix := '1.16';
4060
4061 /*------------------------------------------------------+
4062 |1.17 - Current trx status = Complete |
4063 | Current Gl date = null = Existing Event Gl Date|
4064 | Existing Event Status = Noaction |
4065 +------------------------------------------------------*/
4066 ELSIF trx_status = 'C'
4067 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4068 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4069 AND ev_match_status = 'N' THEN
4070 l_change_matrix := '1.17';
4071
4072 /*------------------------------------------------------+
4073 |1.18 - Current trx status = Complete |
4074 | Current Gl date = null = Existing Event Gl Date|
4075 | Existing Event Status = NULL |
4076 +------------------------------------------------------*/
4077 ELSIF trx_status = 'C'
4078 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4079 AND (to_char(ev_match_gl_date, 'DD-MM-YYYY') = '01-01-1900' OR ev_match_gl_date IS NULL)
4080 AND ev_match_status = 'X' THEN
4081 l_change_matrix := '1.18';
4082
4083 /*------------------------------------------------------+
4084 |1.19 - Current trx status = Complete |
4085 | Current Gl date <> Existing Event Gl Date |
4086 | Existing Event Status = NULL |
4087 +------------------------------------------------------*/
4088 ELSIF trx_status = 'C'
4089 AND dist_gl_date <> NVL(ev_match_gl_date ,TO_DATE('01-01-1900','DD-MM-YYYY'))
4090 AND ev_match_status = 'X' THEN
4091 l_change_matrix := '1.19';
4092
4093 /*------------------------------------------------------+
4094 |1.20 - Current trx status = Incomplete |
4095 | Current Gl date <> Existing Event Gl Date |
4096 | Existing Event Status = NULL |
4097 +------------------------------------------------------*/
4098 ELSIF trx_status = 'I'
4099 AND dist_gl_date <> NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
4100 AND ev_match_status = 'X' THEN
4101 l_change_matrix := '1.20';
4102
4103 /*------------------------------------------------------+
4104 |1.21 - Current trx status = Complete |
4105 | Current Gl date = Existing Event Gl Date |
4106 | Existing Event Status = Incomplete |
4107 +------------------------------------------------------*/
4108 ELSIF trx_status = 'C'
4109 AND dist_gl_date = NVL(ev_match_gl_date,TO_DATE('01-01-1900','DD-MM-YYYY'))
4110 AND ev_match_status = 'I' THEN
4111 l_change_matrix := '1.21';
4112
4113 --{BUG#3320427
4114 /*----------------------------------------------------+
4115 | 1.23 - Current trx status = Incomplete |
4116 | Current CL date is NULL or (01-01-1900) |
4117 | Exist Event date <> NULL or (01-01-1900) |
4118 | Existing Event Status = Incomplete |
4119 +----------------------------------------------------*/
4120 ELSIF trx_status = 'I'
4121 AND to_char(dist_gl_date, 'DD-MM-YYYY') = '01-01-1900'
4122 AND ev_match_status = 'I'
4123 AND to_char(ev_match_gl_date,'DD-MM-YYYY') <> '01-01-1900'
4124 THEN
4125 l_change_matrix := '1.23';
4126 --}
4127
4128 END IF; --change matrix setup for non postable distributions
4129
4130 END IF; --distributions cannot be posted
4131
4132 RETURN(l_change_matrix);
4133
4134 log('ARP_XLA_EVENTS.Change_Matrix()-');
4135
4136 EXCEPTION
4137 WHEN OTHERS THEN
4138 log('EXCEPTION: ARP_XLA_EVENTS.dump_bld_rec');
4139 RAISE;
4140 END Change_Matrix;
4141
4142 /*========================================================================
4143 | PUBLIC FUNCTION dump_bld_rec
4144 |
4145 | DESCRIPTION
4146 | Dump build record
4147 |
4148 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4149 |
4150 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4151 |
4152 | PARAMETERS
4153 | p_bld_rec Build Record
4154 | p_i Index
4155 | p_tag table name tag
4156 |
4157 | KNOWN ISSUES
4158 |
4159 | NOTES
4160 |
4161 | MODIFICATION HISTORY
4162 | Date Author Description of Changes
4163 *=======================================================================*/
4164 PROCEDURE dump_bld_rec(p_bld_rec IN OUT NOCOPY bld_ev_type,
4165 p_i IN BINARY_INTEGER ,
4166 p_tag IN VARCHAR2 ) IS
4167 BEGIN
4168
4169 log('ARP_XLA_EVENTS.dump_bld_rec()+');
4170
4171 IF p_bld_rec.bld_dml_flag.EXISTS(p_i) THEN
4172
4173 log(p_tag ||'.bld_dml_flag('||p_i||') = '
4174 || p_bld_rec.bld_dml_flag(p_i));
4175 END IF;
4176
4177 log('ARP_XLA_EVENTS.dump_bld_rec()-');
4178 log(' ');
4179
4180 EXCEPTION
4181 WHEN OTHERS THEN
4182 log('EXCEPTION: ARP_XLA_EVENTS.dump_bld_rec');
4183 RAISE;
4184
4185 END dump_bld_rec;
4186
4187 /*========================================================================
4188 | PUBLIC PROCEDURE dump_event_info
4189 |
4190 | DESCRIPTION
4191 | Dump event info record
4192 |
4193 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4194 |
4195 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4196 |
4197 | PARAMETERS
4198 | p_ev_info_tab Build Record
4199 | p_i Index
4200 | p_tag table name tag
4201 |
4202 | KNOWN ISSUES
4203 |
4204 | NOTES
4205 |
4206 | MODIFICATION HISTORY
4207 | Date Author Description of Changes
4208 *=======================================================================*/
4209 PROCEDURE dump_event_info
4210 (p_ev_info_tab IN OUT NOCOPY xla_events_pub_pkg.t_array_entity_event_info_s,
4211 p_i IN BINARY_INTEGER ,
4212 p_tag IN VARCHAR2 ) IS
4213 BEGIN
4214
4215 log('ARP_XLA_EVENTS.dump_event_info()+');
4216
4217 IF p_ev_info_tab.EXISTS(p_i) THEN
4218
4219 log(p_tag ||'.p_ev_info_tab('||p_i||').event_id = '||
4220 p_ev_info_tab(p_i).event_id );
4221
4222 log(p_tag ||'.p_ev_info_tab('||p_i||').security_id_int_1 = '||
4223 TO_CHAR(p_ev_info_tab(p_i).security_id_int_1));
4224
4225 log(p_tag ||'.p_ev_info_tab('||p_i||').event_date = '||
4226 p_ev_info_tab(p_i).event_date );
4227
4228 log(p_tag ||'.p_ev_info_tab('||p_i||').event_type_code = '||
4229 p_ev_info_tab(p_i).event_type_code);
4230
4231 log(p_tag ||'.p_ev_info_tab('||p_i||').event_status_code = '||
4232 p_ev_info_tab(p_i).event_status_code);
4233
4234 log(p_tag ||'.p_ev_info_tab('||p_i||').transaction_number = '||
4235 p_ev_info_tab(p_i).transaction_number );
4236
4237 END IF;
4238
4239 log('ARP_XLA_EVENTS.dump_event_info()-');
4240 log(' ');
4241
4242 EXCEPTION
4243 WHEN OTHERS THEN
4244 log('EXCEPTION: ARP_XLA_EVENTS.dump_event_info');
4245 RAISE;
4246
4247 END dump_event_info;
4248
4249 /*========================================================================
4250 | PUBLIC FUNCTION delete_event
4251 |
4252 | DESCRIPTION
4253 | This procedure is a wrapper on the top of XLA delete_event API
4254 |
4255 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4256 |
4257 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4258 |
4259 | PARAMETERS
4260 | p_document_id document identifier
4261 | p_doc_table CT, APP, CMAPP, CRH, CR, ADJ, TRH
4262 | KNOWN ISSUES
4263 |
4264 | NOTES
4265 |
4266 | MODIFICATION HISTORY
4267 | Date Author Description of Changes
4268 | 14-JAN-2003 Herve Yu Create
4269 *=======================================================================*/
4270 PROCEDURE delete_event( p_document_id IN NUMBER,
4271 p_doc_table IN VARCHAR2)
4272 IS
4273 l_event_source_info xla_events_pub_pkg.t_event_source_info;
4274 l_security xla_events_pub_pkg.t_security;
4275 l_event_id NUMBER;
4276 /*7229913 */
4277 l_invoicing_rule_id NUMBER;
4278 l_document_id NUMBER;
4279
4280 /*7229913 */
4281 CURSOR c_ct_rules is
4282 select xe.event_id event_id from
4283 ra_customer_trx ct,
4284 xla_transaction_entities_upg xte,
4285 xla_events xe
4286 where ct.customer_trx_id = p_document_id
4287 and ct.invoicing_rule_id in (-2,-3)
4288 and ct.set_of_books_id = xte.ledger_id
4289 and nvl(xte.source_id_int_1,-99) = ct.customer_trx_id
4290 and xte.entity_code = 'TRANSACTIONS'
4291 and xte.application_id = 222
4292 and xte.entity_id = xe.entity_id
4293 and xe.application_id = 222
4294 and xe.event_status_code = 'I' ;
4295
4296 CURSOR c_ct IS
4297 SELECT event_id
4298 FROM ra_cust_trx_line_gl_dist
4299 WHERE customer_trx_id = p_document_id;
4300
4301 CURSOR c_app IS
4302 SELECT event_id
4303 FROM ar_receivable_applications
4304 WHERE receivable_application_id = p_document_id;
4305
4306 CURSOR c_adj IS
4307 SELECT event_id
4308 FROM ar_adjustments
4309 WHERE adjustment_id = p_document_id;
4310
4311 CURSOR c_crh IS
4312 SELECT event_id
4313 FROM ar_cash_receipt_history
4314 WHERE cash_receipt_id = p_document_id;
4315
4316 CURSOR c_trh IS
4317 SELECT event_id, customer_trx_id
4318 FROM ar_transaction_history
4319 WHERE transaction_history_id = p_document_id;
4320
4321 BEGIN
4322 log('arp_xla_events.delete_event ()+');
4323
4324 IF p_doc_table = 'CT' THEN
4325 OPEN c_ct;
4326 FETCH c_ct INTO l_event_id;
4327 CLOSE c_ct;
4328
4329 /*7229913*/
4330 select invoicing_rule_id into l_invoicing_rule_id from ra_customer_trx
4331 where customer_trx_id = p_document_id;
4332
4333 IF l_invoicing_rule_id in (-2,-3) then
4334 OPEN c_ct_rules ;
4335 FETCH c_ct_rules INTO l_event_id;
4336 CLOSE c_ct_rules;
4337 END IF;
4338
4339 ELSIF p_doc_table = 'ADJ' THEN
4340 OPEN c_adj;
4341 FETCH c_adj INTO l_event_id;
4342 CLOSE c_adj;
4343 ELSIF p_doc_table IN ('APP','CMAPP') THEN
4344 OPEN c_app;
4345 FETCH c_app INTO l_event_id;
4346 CLOSE c_app;
4347 ELSIF p_doc_table IN ('CR','CRH') THEN
4348 OPEN c_crh;
4349 FETCH c_crh INTO l_event_id;
4350 CLOSE c_crh;
4351 ELSIF p_doc_table = 'TRH' THEN
4352 OPEN c_trh;
4353 FETCH c_trh INTO l_event_id, l_document_id;
4354 CLOSE c_trh;
4355 END IF;
4356
4357 IF l_event_id IS NOT NULL THEN
4358 l_event_source_info.entity_type_code:= entity_code(p_doc_table => p_doc_table);
4359 l_security.security_id_int_1 := arp_global.sysparam.org_id;
4360 l_event_source_info.application_id := 222;
4361 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
4362 l_event_source_info.source_id_int_1 := NVL(l_document_id, p_document_id);
4363
4364 xla_events_pub_pkg.delete_event
4365 ( p_event_source_info => l_event_source_info,
4366 p_event_id => l_event_id,
4367 p_valuation_method => NULL,
4368 p_security_context => l_security);
4369 END IF;
4370
4371 log('arp_xla_events.delete_event ()-');
4372 EXCEPTION
4373 WHEN OTHERS THEN
4374 log('EXCEPTION: arp_xla_events.delete_event'||SQLERRM);
4375 RAISE;
4376 END delete_event;
4377
4378
4379 /*========================================================================
4380 | PUBLIC FUNCTION delete_reverse_revrec_event
4381 |
4382 | DESCRIPTION
4383 | This procedure is a wrapper on the top of XLA delete_event API.
4384 | This procedure is used to delete the events from xla_events
4385 | other than the REC event when a transaction with rule is incompleted.
4386 |
4387 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4388 |
4389 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4390 |
4391 | PARAMETERS
4392 | p_document_id document identifier
4393 | p_doc_table CT, APP, CMAPP, CRH, CR, ADJ, TRH
4394 | KNOWN ISSUES
4395 |
4396 | NOTES
4397 |
4398 | MODIFICATION HISTORY
4399 | Date Author Description of Changes
4400 | 07-Mar-2008 Bharani Suri Create
4401 *=======================================================================*/
4402
4403
4404 PROCEDURE delete_reverse_revrec_event( p_document_id IN NUMBER,
4405 p_doc_table IN VARCHAR2)
4406 IS
4407
4408 l_event_source_info xla_events_pub_pkg.t_event_source_info;
4409 l_security xla_events_pub_pkg.t_security;
4410 l_event_id NUMBER;
4411
4412
4413 CURSOR c_ct IS
4414 SELECT distinct event_id event_id
4415 FROM ra_cust_trx_line_gl_dist gld
4416 WHERE customer_trx_id = p_document_id
4417 and account_set_flag = 'N'
4418 AND event_id is not null
4419 and EXISTS
4420 ( select 'x' FROM ra_cust_trx_line_gl_dist gldin
4421 WHERE customer_trx_id = p_document_id
4422 and account_class='REC'
4423 and latest_rec_flag='Y'
4424 AND event_id IS NOT NULL
4425 AND event_id <> gld.event_id
4426 );
4427
4428
4429 BEGIN
4430 log('arp_xla_events.delete_reverse_revrec_event ()+');
4431
4432 FOR c IN c_ct loop
4433
4434 l_event_id := c.event_id;
4435
4436 l_event_source_info.entity_type_code:= entity_code(p_doc_table => p_doc_table);
4437 l_security.security_id_int_1 := arp_global.sysparam.org_id;
4438 l_event_source_info.application_id := 222;
4439 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
4440 l_event_source_info.source_id_int_1 := p_document_id;
4441
4442 xla_events_pub_pkg.delete_event
4443 ( p_event_source_info => l_event_source_info,
4444 p_event_id => l_event_id,
4445 p_valuation_method => NULL,
4446 p_security_context => l_security);
4447
4448 END loop;
4449
4450 log('arp_xla_events.delete_reverse_revrec_event ()-');
4451 EXCEPTION
4452 WHEN OTHERS THEN
4453 log('EXCEPTION: arp_xla_events.delete_reverse_revrec_event'||SQLERRM);
4454 RAISE;
4455 END delete_reverse_revrec_event;
4456
4457
4458 FUNCTION entity_code( p_doc_table IN VARCHAR2)
4459 RETURN VARCHAR2
4460 IS
4461 l_entity_code VARCHAR2(20);
4462 BEGIN
4463 IF p_doc_table IN ('CT','CMAPP', 'CTCMAPP','CTNORCM') THEN
4464 l_entity_code := 'TRANSACTIONS';
4465
4466 ELSIF p_doc_table IN ('CRH','APP','CRHMCD','CRHAPP') THEN
4467 l_entity_code := 'RECEIPTS';
4468
4469 ELSIF p_doc_table = 'ADJ' THEN
4470 l_entity_code := 'ADJUSTMENTS';
4471
4472 ELSIF p_doc_table = 'TRH' THEN
4473 l_entity_code := 'BILLS_RECEIVABLE';
4474 END IF;
4475 RETURN l_entity_code;
4476 END entity_code;
4477
4478 /*
4479 PROCEDURE auto_invoice_events
4480 (p_request_id IN NUMBER,
4481 p_code IN VARCHAR2)
4482 IS
4483 BEGIN
4484 IF p_code = 'CTADJ' THEN
4485 -- arp_xla_events.create_events(p_request_id, 'CT');
4486 -- arp_xla_events.create_events(p_request_id, 'ADJ');
4487 ELSE
4488 -- arp_xla_events.create_events(p_request_id, 'CTCMAPP');
4489 END IF;
4490
4491 IF p_code = 'CTADJ' THEN
4492 INSERT INTO RA_INTERFACE_ERRORS
4493 ( interface_line_id,
4494 message_text,
4495 org_id )
4496 select l.interface_line_id,
4497 xgt.error_msg,
4498 l.org_id
4499 from ra_interface_lines_gt l,
4500 xla_events_gt xgt
4501 where l.request_id = p_request_id
4502 and l.customer_trx_id = xgt.source_id_int_1
4503 and l.event_id = -9999
4504 and xgt.event_class_code in ('INV_CREATE','DM_CREATE','CM_CREATE','ADJ_CREATE');
4505 ELSE
4506 INSERT INTO RA_INTERFACE_ERRORS
4507 ( interface_line_id,
4508 message_text,
4509 org_id )
4510 select l.interface_line_id,
4511 xgt.error_msg,
4512 l.org_id
4513 from ra_interface_lines_gt l,
4514 xla_events_gt xgt
4515 where l.request_id = p_request_id
4516 and l.customer_trx_id = xgt.source_id_int_1
4517 and l.event_id = -9999
4518 and xgt.event_class_code in ('CM_CREATE')
4519 and l.interface_line_id NOT IN (SELECT interface_line_id FROM RA_INTERFACE_ERRORS);
4520 END IF;
4521 END;
4522 */
4523
4524 END ARP_XLA_EVENTS;