[Home] [Help]
PACKAGE BODY: APPS.ARP_XLA_UPGRADE
Source
1 PACKAGE BODY ARP_XLA_UPGRADE AS
2 /* $Header: ARXLAUPB.pls 120.34.12010000.4 2008/11/21 05:32:39 ankuagar ship $ */
3
4 PROCEDURE UPGRADE_TRANSACTIONS(
5 l_table_owner IN VARCHAR2,
6 l_table_name IN VARCHAR2,
7 l_script_name IN VARCHAR2,
8 l_worker_id IN VARCHAR2,
9 l_num_workers IN VARCHAR2,
10 l_batch_size IN VARCHAR2,
11 l_batch_id IN NUMBER,
12 l_action_flag IN VARCHAR2,
13 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
14
15 l_start_rowid rowid;
16 l_end_rowid rowid;
17 l_any_rows_to_process boolean;
18 l_rows_processed number := 0;
19
20 BEGIN
21
22 /* ------ Initialize the rowid ranges ------ */
23 ad_parallel_updates_pkg.initialize_rowid_range(
24 ad_parallel_updates_pkg.ROWID_RANGE,
25 l_table_owner,
26 l_table_name,
27 l_script_name,
28 l_worker_id,
29 l_num_workers,
30 l_batch_size, 0);
31
32 /* ------ Get rowid ranges ------ */
33 ad_parallel_updates_pkg.get_rowid_range(
34 l_start_rowid,
35 l_end_rowid,
36 l_any_rows_to_process,
37 l_batch_size,
38 TRUE);
39
40 WHILE ( l_any_rows_to_process = TRUE )
41 LOOP
42
43 l_rows_processed := 0;
44
45 -------------------------------------------------------------------
46 -- Create the transaction entities
47 -- Created by ar120ent.sql
48 -------------------------------------------------------------------
49
50 -------------------------------------------------------------------
51 -- Create the Journal Entry Events and Headers for transactions
52 -- category definitions can be found in argper.lpc function arguje
53 -------------------------------------------------------------------
54 IF NVL(l_entity_type,'E') = 'E' THEN
55
56 INSERT ALL
57 WHEN 1 = 1 THEN
58 INTO XLA_EVENTS
59 (upg_batch_id,
60 upg_source_application_id,
61 application_id,
62 reference_num_1,
63 reference_num_2,
64 event_type_code,
65 event_number,
66 event_status_code,
67 process_status_code,
68 on_hold_flag,
69 event_date,
70 creation_date,
71 created_by,
72 last_update_date,
73 last_updated_by,
74 last_update_login,
75 program_update_date,
76 program_id,
77 program_application_id,
78 request_id,
79 entity_id,
80 event_id,
81 upg_valid_flag,
82 transaction_date)
83 VALUES
84 (batch_id,
85 222,
86 222,
87 pst_id, --reference num 1
88 trx_id, --reference num 2
89 override_event, --event type
90 line_num,
91 trx_status, --event status code I, U, N, P
92 pstd_flg, --process status
93 'N',
94 gl_date, --event date
95 sysdate, --creation_date
96 0, --created_by
97 sysdate, --last_update_date
98 0, --last_updated_by
99 0, --last_updated_login
100 sysdate,
101 0, --program_id
102 222,
103 '',
104 entity_id,
105 xla_events_s.nextval,
106 'Y', --upgrade flag
107 trx_date
108 )
109 WHEN PST_ID <> -3 THEN
110 INTO XLA_AE_HEADERS
111 (upg_batch_id,
112 upg_source_application_id,
113 application_id,
114 amb_context_code,
115 entity_id,
116 event_id,
117 event_type_code,
118 ae_header_id,
119 ledger_id,
120 accounting_date,
121 period_name,
122 reference_date,
123 balance_type_code,
124 je_category_name,
125 gl_transfer_status_code,
126 gl_transfer_date,
127 accounting_entry_status_code,
128 accounting_entry_type_code,
129 description,
130 budget_version_id,
131 funds_status_code,
132 encumbrance_type_id,
133 completed_date,
134 doc_sequence_id,
135 doc_sequence_value,
136 doc_category_code,
137 packet_id,
138 group_id,
139 creation_date,
140 created_by,
141 last_update_date,
142 last_updated_by,
143 last_update_login,
144 program_update_date,
145 program_id,
146 program_application_id,
147 request_id,
148 close_acct_seq_assign_id,
149 close_acct_seq_version_id,
150 close_acct_seq_value,
151 completion_acct_seq_assign_id,
152 completion_acct_seq_version_id,
153 completion_acct_seq_value,
154 upg_valid_flag
155 )
156 VALUES
157 (batch_id,
158 222,
159 222,
160 'DEFAULT', --amb context code
161 entity_id,
162 xla_events_s.nextval,
163 override_event,
164 xla_ae_headers_s.nextval,
165 sob_id,
166 gl_date,
167 period_name,
168 '', --reference date global acct eng
169 'A', --balance type Actual
170 category, --category
171 'Y', --gl transfer status
172 gl_posted_date, --gl transfer date
173 'F', --acct entry status code final
174 'STANDARD', --acct entry type code
175 '', --description TBD
176 '', --budget version id
177 '', --funds status code
178 '', --encumbrance type id
179 '', --completed date
180 doc_seq_id,
181 doc_seq_val,
182 cat_code,
183 '', --packet id
184 '', --group id
185 sysdate, --row who creation date
186 0, --created_by
187 sysdate,
188 0,
189 0,
190 sysdate,
191 0, --program id
192 222,
193 '', --request id
194 '', --AX columns start
195 '',
196 '',
197 '',
198 '',
199 '',
200 '' --upg valid flag
201 --''
202 )
203 select /*+ use_nl(lgr,map) */
204 l_batch_id AS BATCH_ID,
205 decode(trx_type,
206 'CM', 'Credit Memos',
207 'DM', 'Debit Memos',
208 'CB', 'Chargebacks',
209 'Sales Invoices') AS CATEGORY,
210 ev.TRX_ID AS TRX_ID,
211 ev.TRX_DATE AS TRX_DATE,
212 ev.SOB_ID AS SOB_ID,
213 ev.CAT_CODE AS CAT_CODE,
214 ev.TRX_TYPE AS TRX_TYPE,
215 ev.TRX_STATUS AS TRX_STATUS,
216 ev.OVERRIDE_EVENT AS OVERRIDE_EVENT,
217 ev.PSTD_FLG AS PSTD_FLG,
218 ev.PST_ID AS PST_ID,
219 ev.GL_DATE AS GL_DATE,
220 max(ev.GL_POSTED_DATE) AS GL_POSTED_DATE,
221 ev.DOC_SEQ_ID AS DOC_SEQ_ID,
222 ev.DOC_SEQ_VAL AS DOC_SEQ_VAL,
223 ev.ENTITY_ID AS ENTITY_ID,
224 map.PERIOD_NAME AS PERIOD_NAME,
225 decode(l_action_flag,'D',0,
226 (select nvl(max(in_ev.event_number),0)
227 from xla_events in_ev /*bug 5867069*/
228 where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
229 ORDER BY decode(ev.OVERRIDE_EVENT,
230 ev.TRX_TYPE||'_CREATE',1,
231 ev.TRX_TYPE||'_UPDATE',2,
232 3), ev.GL_DATE, decode(EV.PST_ID,
233 -3, 2,
234 1), EV.PST_ID) AS LINE_NUM
235 FROM
236 (select /*+ ordered rowid(ct) use_nl(ctlgd,ctlgd1,te) use_hash(gps) swap_join_inputs(gps) use_hash(sys,tty) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
237 ct.customer_trx_id TRX_ID ,
238 ct.trx_date TRX_DATE ,
239 ct.set_of_books_id SOB_ID ,
240 tty.type TRX_TYPE ,
241 decode(sys.accounting_method,
242 'CASH', 'N',
243 decode(tty.post_to_gl,
244 'N', 'N',
245 decode(ct.complete_flag,
246 'Y',decode(ctlgd.posting_control_id,
247 -3, 'U',
248 'P'),
249 'I'))) TRX_STATUS ,
250 decode(nvl(trunc(ctlgd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
251 nvl(trunc(ctlgd1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
252 decode(ctlgd.posting_control_id,
253 ctlgd1.posting_control_id, tty.type || '_CREATE',
254 tty.type || '_UPDATE'),
255 tty.type || '_UPDATE') OVERRIDE_EVENT ,
256 decode(ctlgd.posting_control_id,
257 -3, 'U',
258 'P') PSTD_FLG ,
259 ctlgd.posting_control_id PST_ID ,
260 nvl(trunc(ctlgd.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE ,
261 nvl(trunc(max(ctlgd.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
262 ct.doc_sequence_id DOC_SEQ_ID ,
263 ct.doc_sequence_value DOC_SEQ_VAL ,
264 tty.name CAT_CODE ,
265 te.entity_id ENTITY_ID
266 FROM
267 ra_customer_trx_all ct,
268 ra_cust_trx_line_gl_dist_all ctlgd,
269 xla_upgrade_dates gps,
270 ar_system_parameters_all sys,
271 ra_cust_trx_types_all tty,
272 ra_cust_trx_line_gl_dist_all ctlgd1,
273 xla_transaction_entities_upg te
274 WHERE ct.rowid >= l_start_rowid
275 AND ct.rowid <= l_end_rowid
276 AND NVL(ct.ax_accounted_flag,'N') = 'N'
277 AND ctlgd.customer_trx_id = ct.customer_trx_id
278 and ctlgd.event_id is null
279 AND decode(ctlgd.account_class,
280 'REC',ctlgd.latest_rec_flag,
281 'Y') = 'Y'
282 AND DECODE(ctlgd.account_set_flag,
283 'N','N',
284 'Y', decode(ctlgd.account_class,
285 'REC','N',
286 'Y')
287 ) = 'N'
288 and trunc(ctlgd.gl_date) between gps.start_date and gps.end_date
289 and gps.ledger_id = ct.set_of_books_id
290 and decode(ctlgd.posting_control_id,
291 -3, decode(l_action_flag,
292 'D','P',
293 l_action_flag),
294 'P') = 'P'
295 AND sys.org_id = ct.org_id
296 AND ct.cust_trx_type_id = tty.cust_trx_type_id
297 AND tty.org_id = ct.org_id
298 AND ctlgd1.customer_trx_id = ct.customer_trx_id
299 AND ctlgd1.latest_rec_flag = 'Y'
300 AND ctlgd1.account_class = 'REC'
301 AND te.application_id = 222
302 AND te.ledger_id = ct.set_of_books_id
303 AND te.entity_code = 'TRANSACTIONS'
304 AND nvl(te.source_id_int_1,-99) = ct.customer_trx_id
305 --AND te.upg_batch_id = l_batch_id
306 GROUP BY
307 ct.customer_trx_id,
308 ct.trx_date,
309 ct.set_of_books_id,
310 te.entity_id,
311 tty.type,
312 decode(sys.accounting_method,
313 'CASH', 'N',
314 decode(tty.post_to_gl,
315 'N', 'N',
316 decode(ct.complete_flag,
317 'Y',decode(ctlgd.posting_control_id,
318 -3, 'U',
319 'P'),
320 'I'))),
321 ct.org_id,
322 decode(nvl(trunc(ctlgd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
323 nvl(trunc(ctlgd1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
324 decode(ctlgd.posting_control_id,
325 ctlgd1.posting_control_id, tty.type || '_CREATE',
326 tty.type || '_UPDATE'),
327 tty.type || '_UPDATE') ,
328 ctlgd.posting_control_id,
329 nvl(trunc(ctlgd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
330 ct.doc_sequence_id,
331 ct.doc_sequence_value,
332 tty.name
333 UNION
334 select /*+ ordered rowid(ct) use_nl(app,ctlgd,te) use_hash(gps) swap_join_inputs(gps) use_hash(sys,tty) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
335 ct.customer_trx_id TRX_ID ,
336 ct.trx_date TRX_DATE ,
337 ct.set_of_books_id SOB_ID ,
338 tty.type TRX_TYPE ,
339 decode(sys.accounting_method,
340 'CASH', decode(ct.previous_customer_trx_id,
341 '', decode(ct.complete_flag,
342 'Y',decode(app.posting_control_id,
343 -3, 'U',
344 'P'),
345 'I'),
346 'N'),
347 decode(tty.post_to_gl,
348 'N', 'N',
349 decode(ct.complete_flag,
350 'Y',decode(app.posting_control_id,
351 -3, 'U',
352 'P'),
353 'I'))) TRX_STATUS ,
354 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
355 nvl(trunc(ctlgd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
356 decode(sys.accounting_method,
357 'CASH', 'CM_UPDATE',
358 decode(app.posting_control_id,
359 ctlgd.posting_control_id, 'CM_CREATE',
360 'CM_UPDATE')),
361 'CM_UPDATE') OVERRIDE_EVENT ,
362 decode(app.posting_control_id,
363 -3, 'U',
364 'P') PSTD_FLG ,
365 app.posting_control_id PST_ID ,
366 nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE ,
367 nvl(trunc(max(app.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
368 ct.doc_sequence_id DOC_SEQ_ID ,
369 ct.doc_sequence_value DOC_SEQ_VAL ,
370 tty.name CAT_CODE ,
371 te.entity_id ENTITY_ID
372 FROM ra_customer_trx_all ct,
373 ar_receivable_applications_all app,
374 xla_upgrade_dates gps,
375 ar_system_parameters_all sys,
376 ra_cust_trx_types_all tty,
377 ra_cust_trx_line_gl_dist_all ctlgd,
378 xla_transaction_entities_upg te
379 WHERE ct.rowid >= l_start_rowid
380 AND ct.rowid <= l_end_rowid
381 AND NVL(ct.ax_accounted_flag,'N') = 'N'
382 AND app.application_type = 'CM'
383 AND app.status = 'APP'
384 AND app.customer_trx_id = ct.customer_trx_id
385 and app.event_id is null
386 and trunc(app.gl_date) between gps.start_date and gps.end_date
387 and gps.ledger_id = ct.set_of_books_id
388 and decode(app.posting_control_id,
389 -3, decode(l_action_flag,
390 'D','P',
391 l_action_flag),
392 'P') = 'P'
393 AND sys.org_id = ct.org_id
394 AND ct.cust_trx_type_id = tty.cust_trx_type_id
395 AND tty.org_id = ct.org_id
396 AND ctlgd.customer_trx_id = ct.customer_trx_id
397 AND ctlgd.latest_rec_flag = 'Y'
398 AND ctlgd.account_class = 'REC'
399 AND te.application_id = 222
400 AND te.ledger_id = ct.set_of_books_id
401 AND te.entity_code = 'TRANSACTIONS'
402 AND nvl(te.source_id_int_1,-99) = ct.customer_trx_id
403 --AND te.upg_batch_id = l_batch_id
404 GROUP BY
405 ct.customer_trx_id,
406 ct.trx_date,
407 ct.set_of_books_id,
408 te.entity_id,
409 tty.type,
410 decode(sys.accounting_method,
411 'CASH', decode(ct.previous_customer_trx_id,
412 '', decode(ct.complete_flag,
413 'Y',decode(app.posting_control_id,
414 -3, 'U',
415 'P'),
416 'I'),
417 'N'),
418 decode(tty.post_to_gl,
419 'N', 'N',
420 decode(ct.complete_flag,
421 'Y',decode(app.posting_control_id,
422 -3, 'U',
423 'P'),
424 'I'))),
425 ct.org_id,
426 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
427 nvl(trunc(ctlgd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
428 decode(sys.accounting_method,
429 'CASH', 'CM_UPDATE',
430 decode(app.posting_control_id,
431 ctlgd.posting_control_id, 'CM_CREATE',
432 'CM_UPDATE')),
433 'CM_UPDATE'),
434 app.posting_control_id,
435 nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
436 ct.doc_sequence_id,
437 ct.doc_sequence_value,
438 tty.name) ev,
439 gl_ledgers lgr,
440 gl_date_period_map map
441 where ev.sob_id = lgr.ledger_id
442 and map.period_set_name = lgr.period_set_name
443 and map.period_type = lgr.accounted_period_type
444 and map.accounting_date = ev.gl_date
445 --AND per.adjustment_period_flag = 'N'
446 group by decode(trx_type,
447 'CM', 'Credit Memos',
448 'DM', 'Debit Memos',
449 'CB', 'Chargebacks',
450 'Sales Invoices') ,
451 ev.TRX_ID ,
452 ev.TRX_DATE ,
453 ev.SOB_ID ,
454 ev.CAT_CODE ,
455 ev.TRX_TYPE ,
456 ev.TRX_STATUS ,
457 ev.OVERRIDE_EVENT ,
458 ev.PSTD_FLG ,
459 ev.PST_ID ,
460 ev.GL_DATE ,
461 ev.DOC_SEQ_ID ,
462 ev.DOC_SEQ_VAL ,
463 ev.ENTITY_ID ,
464 map.PERIOD_NAME ;
465
466 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
467
468 END IF; --create events
469
470 -------------------------------------------------------------------
471 -- Create the Journal Entry Lines
472 -- gl_transfer_mode_code is a flag indicating whether distributions
473 -- from AR to subledger tables are in detail or summary. This is
474 -- different from the standard post to GL summary or detail. So
475 --from an upgrade perspective for AR this is in detail always
476 --as AR stores in detailed accounting for historical data.
477 -------------------------------------------------------------------
478 IF NVL(l_entity_type,'L') = 'L' THEN
479
480 INSERT ALL
481 WHEN 1 = 1 THEN
482 INTO XLA_AE_LINES
483 (upg_batch_id,
484 ae_header_id,
485 ae_line_num,
486 application_id,
487 code_combination_id,
488 gl_transfer_mode_code,
489 accounted_dr,
490 accounted_cr,
491 currency_code,
492 currency_conversion_date,
493 currency_conversion_rate,
494 currency_conversion_type,
495 entered_dr,
496 entered_cr,
497 description,
498 accounting_class_code,
499 gl_sl_link_id,
500 gl_sl_link_table,
501 party_type_code,
502 party_id,
503 party_site_id,
504 statistical_amount,
505 ussgl_transaction_code,
506 jgzz_recon_ref,
507 control_balance_flag,
508 analytical_balance_flag,
509 creation_date,
510 created_by,
511 last_update_date,
512 last_updated_by,
513 last_update_login,
514 program_update_date,
515 program_id,
516 program_application_id,
517 request_id,
518 gain_or_loss_flag,
519 accounting_date,
520 ledger_id
521 )
522 VALUES
523 ( batch_id,
524 ae_header_id,
525 line_num,
526 222,
527 code_combination_id,
528 'D', --gl transfer mode Summary or detail
529 acctd_amount_dr,
530 acctd_amount_cr,
531 currency_code,
532 exchange_date,
533 exchange_rate,
534 exchange_type,
535 amount_dr,
536 amount_cr,
537 '', --description TBD
538 nvl(account_class,'XXXX'), --accounting class code
539 xla_gl_sl_link_id_s.nextval, --gl sl link id
540 'XLAJEL', --gl sl link table
541 DECODE(third_party_id, NULL, NULL,'C'), --party type code
542 third_party_id, --party id
543 third_party_sub_id, --third party site
544 '', --statistical amount
545 '', --ussgl trx code
546 '', --jgzz recon ref
547 '', --control balance flag
548 '', --analytical balance
549 sysdate, --row who columns
550 0,
551 sysdate,
552 0,
553 0,
554 sysdate,
555 0, --program id
556 222,
557 '', --request id
558 gain_or_loss_flag,
559 accounting_date,
560 ledger_id)
561 WHEN 1 = 1 THEN
562 INTO XLA_DISTRIBUTION_LINKS
563 (APPLICATION_ID,
564 EVENT_ID,
565 AE_HEADER_ID,
566 AE_LINE_NUM,
567 ACCOUNTING_LINE_CODE,
568 ACCOUNTING_LINE_TYPE_CODE,
569 REF_AE_HEADER_ID,
570 SOURCE_DISTRIBUTION_TYPE,
571 SOURCE_DISTRIBUTION_ID_CHAR_1,
572 SOURCE_DISTRIBUTION_ID_CHAR_2,
573 SOURCE_DISTRIBUTION_ID_CHAR_3,
574 SOURCE_DISTRIBUTION_ID_CHAR_4,
575 SOURCE_DISTRIBUTION_ID_CHAR_5,
576 SOURCE_DISTRIBUTION_ID_NUM_1,
577 SOURCE_DISTRIBUTION_ID_NUM_2,
578 SOURCE_DISTRIBUTION_ID_NUM_3,
579 SOURCE_DISTRIBUTION_ID_NUM_4,
580 SOURCE_DISTRIBUTION_ID_NUM_5,
581 UNROUNDED_ENTERED_DR,
582 UNROUNDED_ENTERED_CR,
583 UNROUNDED_ACCOUNTED_DR,
584 UNROUNDED_ACCOUNTED_CR,
585 MERGE_DUPLICATE_CODE,
586 TAX_LINE_REF_ID,
587 TAX_SUMMARY_LINE_REF_ID,
588 TAX_REC_NREC_DIST_REF_ID,
589 STATISTICAL_AMOUNT,
590 TEMP_LINE_NUM,
591 EVENT_TYPE_CODE,
592 EVENT_CLASS_CODE,
593 REF_EVENT_ID,
594 UPG_BATCH_ID)
595 VALUES
596 (222,
597 event_id,
598 ae_header_id,
599 line_num,
600 account_class,
601 'C', --accounting line code customer
602 ae_header_id, --reference header id
603 source_table,
604 '', --src dist id char
605 '',
606 '',
607 '',
608 '',
609 line_id, --src dist id num
610 '',
611 '',
612 '',
613 '',
614 amount_dr,
615 amount_cr,
616 acctd_amount_dr,
617 acctd_amount_cr,
618 'N', --merge dup code
619 tax_line_id, --tax_line_ref_id
620 '', --tax_summary_line_ref_id
621 '', --tax_rec_nrec_dist_ref_id
622 '', --statistical amount
623 line_num, --temp_line_num
624 event_type_code, --event_type_code
625 event_class_code, --event class code
626 '', --ref_event_id,
627 batch_id) --upgrade batch id
628 select
629 l_batch_id AS batch_id,
630 ae_header_id AS ae_header_id,
631 line_id AS line_id,
632 event_id AS event_id,
633 account_class AS account_class,
634 source_table AS source_table,
635 code_combination_id AS code_combination_id,
636 amount_dr AS amount_dr,
637 amount_cr AS amount_cr,
638 acctd_amount_dr AS acctd_amount_dr,
639 acctd_amount_cr AS acctd_amount_cr,
640 nvl(currency_code,'XXXX') AS currency_code,
641 third_party_id AS third_party_id,
642 third_party_sub_id AS third_party_sub_id,
643 exchange_date AS exchange_date,
644 exchange_rate AS exchange_rate,
645 exchange_type AS exchange_type,
646 tax_line_id AS tax_line_id,
647 gain_or_loss_flag AS gain_or_loss_flag,
648 event_type_code AS event_type_code,
649 event_class_code AS event_class_code,
650 accounting_date AS accounting_date,
651 ledger_id AS ledger_id,
652 RANK() OVER (PARTITION BY event_id, ae_header_id
653 ORDER BY line_id, ln_order) AS line_num
654 FROM
655 (select /*+ ordered rowid(ct) use_nl(ctlgd,ent,ev,hdr,ctl) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
656 hdr.ae_header_id ae_header_id,
657 decode(account_class, 'REC', 'RECEIVABLE',
658 'REV', 'REVENUE',
659 'UNEARN', 'UNEARNED_REVENUE',
660 'ROUND', 'ROUNDING',
661 ctlgd.account_class) account_class,
662 'RA_CUST_TRX_LINE_GL_DIST_ALL' source_table,
663 ctlgd.code_combination_id code_combination_id,
664 decode(ctlgd.account_class,
665 'REC', decode(sign(ctlgd.amount),
666 1, abs(ctlgd.amount),
667 0, abs(ctlgd.amount),
668 ''),
669 decode(sign(ctlgd.amount),
670 -1, abs(ctlgd.amount),
671 '')) amount_dr,
672 decode(ctlgd.account_class,
673 'REC', decode(sign(ctlgd.amount),
674 -1, abs(ctlgd.amount),
675 ''),
676 decode(sign(ctlgd.amount),
677 1, abs(ctlgd.amount),
678 0, abs(ctlgd.amount),
679 '')) amount_cr,
680 decode(ctlgd.account_class,
681 'REC', decode(sign(ctlgd.acctd_amount),
682 1, abs(ctlgd.acctd_amount),
683 0, abs(ctlgd.acctd_amount),
684 ''),
685 decode(sign(ctlgd.acctd_amount),
686 -1, abs(ctlgd.acctd_amount),
687 '')) acctd_amount_dr,
688 decode(ctlgd.account_class,
689 'REC', decode(sign(ctlgd.acctd_amount),
690 -1, abs(ctlgd.acctd_amount),
691 ''),
692 decode(sign(ctlgd.acctd_amount),
693 1, abs(ctlgd.acctd_amount),
694 0, abs(ctlgd.acctd_amount),
695 '')) acctd_amount_cr,
696 ct.invoice_currency_code currency_code,
697 ct.bill_to_customer_id third_party_id,
698 ct.bill_to_site_use_id third_party_sub_id,
699 ct.exchange_date exchange_date,
700 ct.exchange_rate exchange_rate,
701 ct.exchange_rate_type exchange_type,
702 ctlgd.cust_trx_line_gl_dist_id line_id,
703 ev.event_id event_id,
704 ev.event_type_code event_type_code,
705 decode(ev.event_type_code,
706 'INV_CREATE', 'INVOICE',
707 'INV_UPDATE', 'INVOICE',
708 'CM_CREATE' , 'CREDIT_MEMO',
709 'CM_UPDATE' , 'CREDIT_MEMO',
710 'DM_CREATE' , 'DEBIT_MEMO',
711 'DM_UPDATE' , 'DEBIT_MEMO',
712 'CB_CREATE' , 'CHARGEBACK',
713 'DEP_CREATE', 'DEPOSIT',
714 'DEP_UPDATE', 'DEPOSIT',
715 'GUAR_CREATE','GUARANTEE',
716 'GUAR_UPDATE','GUARANTEE',
717 'UNKNOWN') event_class_code,
718 ctl.tax_line_id tax_line_id,
719 'N' gain_or_loss_flag,
720 hdr.accounting_date accounting_date,
721 hdr.ledger_id ledger_id,
722 1 ln_order
723 from
724 ra_customer_trx_all ct,
725 ra_cust_trx_line_gl_dist_all ctlgd,
726 xla_upgrade_dates gps,
727 xla_transaction_entities_upg ent,
728 xla_events ev,
729 xla_ae_headers hdr,
730 ra_customer_trx_lines_all ctl
731 where ct.rowid >= l_start_rowid
732 and ct.rowid <= l_end_rowid
733 and NVL(ct.ax_accounted_flag,'N') = 'N'
734 and ct.customer_trx_id = ctlgd.customer_trx_id
735 and ctlgd.account_set_flag = 'N'
736 and trunc(ctlgd.gl_date) between gps.start_date and gps.end_date
737 and gps.ledger_id = ct.set_of_books_id
738 and ent.application_id = 222
739 and ent.ledger_id = ct.set_of_books_id
740 and ent.entity_code = 'TRANSACTIONS'
741 and nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
742 and ent.entity_id = ev.entity_id
743 and ev.application_id = 222
744 and ev.upg_batch_id = l_batch_id
745 and ctlgd.posting_control_id = ev.reference_num_1
746 and nvl(trunc(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
747 and hdr.application_id = 222
748 and hdr.event_id = ev.event_id
749 and ct.set_of_books_id = hdr.ledger_id
750 and ctlgd.customer_trx_line_id = ctl.customer_trx_line_id (+)
751 UNION ALL /* CM applications */
752 select /*+ ordered rowid(ct) use_nl(app,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
753 hdr.ae_header_id ae_header_id,
754 DECODE(ard.source_type, 'REC','RECEIVABLE',
755 ard.source_type) account_class,
756 'AR_DISTRIBUTIONS_ALL' source_table,
757 ard.code_combination_id code_combination_id,
758 ard.amount_dr amount_dr,
759 ard.amount_cr amount_cr,
760 ard.acctd_amount_dr acctd_amount_dr,
761 ard.acctd_amount_cr acctd_amount_cr,
762 ard.currency_code currency_code,
763 ard.third_party_id third_party_id,
764 ard.third_party_sub_id third_party_sub_id,
765 ard.currency_conversion_date exchange_date,
766 ard.currency_conversion_rate exchange_rate,
767 ard.currency_conversion_type exchange_type,
768 ard.line_id line_id,
769 ev.event_id event_id,
770 ev.event_type_code event_type_code,
771 'CREDIT_MEMO' event_class_code,
772 null tax_line_id,
773 decode(ard.source_type,
774 'EXCH_GAIN','Y',
775 'EXCH_LOSS','Y',
776 'N') gain_or_loss_flag,
777 hdr.accounting_date accounting_date,
778 hdr.ledger_id ledger_id,
779 2 ln_order
780 from ra_customer_trx_all ct,
781 ar_receivable_applications_all app,
782 xla_upgrade_dates gps,
783 xla_transaction_entities_upg ent,
784 xla_events ev,
785 xla_ae_headers hdr,
786 ar_distributions_all ard
787 where ct.rowid >= l_start_rowid
788 and ct.rowid <= l_end_rowid
789 and NVL(ct.ax_accounted_flag,'N') = 'N'
790 and ct.customer_trx_id = app.customer_trx_id
791 and trunc(app.gl_date) between gps.start_date and gps.end_date
792 and gps.ledger_id = ct.set_of_books_id
793 and ent.application_id = 222
794 and ent.ledger_id = ct.set_of_books_id
795 and ent.entity_code = 'TRANSACTIONS'
796 and nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
797 and ent.entity_id = ev.entity_id
798 and ev.application_id = 222
799 and ev.upg_batch_id = l_batch_id
800 and app.posting_control_id = ev.reference_num_1
801 and nvl(trunc(app.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
802 and hdr.application_id = 222
803 and ct.set_of_books_id = hdr.ledger_id
804 and hdr.event_id = ev.event_id
805 and ard.source_id = app.receivable_application_id
806 and ard.source_table = 'RA');
807 --order by entity_id, ae_header_id, line_num;
808
809 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
810
811 END IF; --create lines
812
813 ad_parallel_updates_pkg.processed_rowid_range(
814 l_rows_processed,
815 l_end_rowid);
816
817 commit;
818
819 ad_parallel_updates_pkg.get_rowid_range(
820 l_start_rowid,
821 l_end_rowid,
822 l_any_rows_to_process,
823 l_batch_size,
824 FALSE);
825
826 l_rows_processed := 0 ;
827
828 END LOOP ; /* end of WHILE loop */
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.upgrade_transactions');
833 RAISE;
834
835 WHEN OTHERS THEN
836 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.upgrade_transactions');
837 RAISE;
838
839 END UPGRADE_TRANSACTIONS;
840
841 PROCEDURE UPGRADE_BILLS_RECEIVABLE(
842 l_table_owner IN VARCHAR2,
843 l_table_name IN VARCHAR2,
844 l_script_name IN VARCHAR2,
845 l_worker_id IN VARCHAR2,
846 l_num_workers IN VARCHAR2,
847 l_batch_size IN VARCHAR2,
848 l_batch_id IN NUMBER,
849 l_action_flag IN VARCHAR2,
850 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
851
852 l_start_rowid rowid;
853 l_end_rowid rowid;
854 l_any_rows_to_process boolean;
855 l_rows_processed number := 0;
856
857 BEGIN
858
859 /* ------ Initialize the rowid ranges ------ */
860 ad_parallel_updates_pkg.initialize_rowid_range(
861 ad_parallel_updates_pkg.ROWID_RANGE,
862 l_table_owner,
863 l_table_name,
864 l_script_name,
865 l_worker_id,
866 l_num_workers,
867 l_batch_size, 0);
868
869 /* ------ Get rowid ranges ------ */
870 ad_parallel_updates_pkg.get_rowid_range(
871 l_start_rowid,
872 l_end_rowid,
873 l_any_rows_to_process,
874 l_batch_size,
875 TRUE);
876
877 WHILE ( l_any_rows_to_process = TRUE )
878 LOOP
879
880 l_rows_processed := 0;
881
882 -------------------------------------------------------------------
883 -- Create the br entities
884 -- Created by ar120ent.sql
885 -------------------------------------------------------------------
886
887 /*------------------------------------------------------------------------------+
888 | Create the BR events |
889 +------------------------------------------------------------------------------*/
890 IF NVL(l_entity_type,'E') = 'E' THEN
891
892 INSERT ALL
893 WHEN 1 = 1 THEN
894 INTO XLA_EVENTS
895 (upg_batch_id,
896 upg_source_application_id,
897 application_id,
898 reference_num_1,
899 reference_num_2,
900 event_type_code,
901 event_number,
902 event_status_code,
903 process_status_code,
904 on_hold_flag,
905 event_date,
906 creation_date,
907 created_by,
908 last_update_date,
909 last_updated_by,
910 last_update_login,
911 program_update_date,
912 program_id,
913 program_application_id,
914 request_id,
915 entity_id,
916 event_id,
917 upg_valid_flag,
918 transaction_date)
919 VALUES
920 (batch_id,
921 222,
922 222,
923 pst_id, --reference num 1
924 trx_id, --reference num 2
925 override_event, --event type
926 line_num,
927 trx_status, --event status code I, U, N, P
928 pstd_flg, --process status
929 'N',
930 gl_date, --event date
931 sysdate,
932 0,
933 sysdate,
934 0,
935 0,
936 sysdate,
937 0,
938 222,
939 '',
940 entity_id,
941 xla_events_s.nextval,
942 'Y', --upgrade flag
943 trx_date
944 )
945 WHEN PST_ID <> -3 THEN
946 INTO XLA_AE_HEADERS
947 (upg_batch_id,
948 upg_source_application_id,
949 application_id,
950 amb_context_code,
951 entity_id,
952 event_id,
953 event_type_code,
954 ae_header_id,
955 ledger_id,
956 accounting_date,
957 period_name,
958 reference_date,
959 balance_type_code,
960 je_category_name,
961 gl_transfer_status_code,
962 gl_transfer_date,
963 accounting_entry_status_code,
964 accounting_entry_type_code,
965 description,
966 budget_version_id,
967 funds_status_code,
968 encumbrance_type_id,
969 completed_date,
970 doc_sequence_id,
971 doc_sequence_value,
972 doc_category_code,
973 packet_id,
974 group_id,
975 creation_date,
976 created_by,
977 last_update_date,
978 last_updated_by,
979 last_update_login,
980 program_update_date,
981 program_id,
982 program_application_id,
983 request_id,
984 close_acct_seq_assign_id,
985 close_acct_seq_version_id,
986 close_acct_seq_value,
987 completion_acct_seq_assign_id,
988 completion_acct_seq_version_id,
989 completion_acct_seq_value,
990 upg_valid_flag
991 )
992 VALUES
993 (batch_id,
994 222,
995 222,
996 'DEFAULT', --amb context code
997 entity_id,
998 xla_events_s.nextval,
999 override_event,
1000 xla_ae_headers_s.nextval,
1001 sob_id,
1002 gl_date,
1003 period_name,
1004 '', --reference date global acct eng
1005 'A', --balance type Actual
1006 category, --category
1007 'Y', --gl transfer status
1008 gl_posted_date, --gl transfer date
1009 'F', --acct entry status code final
1010 'STANDARD', --acct entry type code
1011 '', --description TBD
1012 '', --budget version id
1013 '', --funds status code
1014 '', --encumbrance type id
1015 '', --completed date
1016 doc_seq_id,
1017 doc_seq_val,
1018 cat_code,
1019 '', --packet id
1020 '', --group id
1021 sysdate, --row who creation date
1022 0,
1023 sysdate,
1024 0,
1025 0,
1026 sysdate,
1027 0, --program id
1028 222,
1029 '', --request id
1030 '', --AX columns start
1031 '',
1032 '',
1033 '',
1034 '',
1035 '',
1036 '' --upg valid flag
1037 --''
1038 )
1039 select /*+ use_nl(lgr,map) */
1040 l_batch_id AS BATCH_ID,
1041 decode(trx_type,
1042 'CM', 'Credit Memos',
1043 'DM', 'Debit Memos',
1044 'CB', 'Chargebacks',
1045 'Sales Invoices') AS CATEGORY,
1046 ev.TRX_ID AS TRX_ID,
1047 ev.TRX_DATE AS TRX_DATE,
1048 ev.SOB_ID AS SOB_ID,
1049 ev.CAT_CODE AS CAT_CODE,
1050 ev.TRX_TYPE AS TRX_TYPE,
1051 ev.TRX_STATUS AS TRX_STATUS,
1052 ev.OVERRIDE_EVENT AS OVERRIDE_EVENT,
1053 ev.PSTD_FLG AS PSTD_FLG,
1054 ev.PST_ID AS PST_ID,
1055 ev.GL_DATE AS GL_DATE,
1056 max(ev.GL_POSTED_DATE) AS GL_POSTED_DATE,
1057 ev.DOC_SEQ_ID AS DOC_SEQ_ID,
1058 ev.DOC_SEQ_VAL AS DOC_SEQ_VAL,
1059 ev.ENTITY_ID AS ENTITY_ID,
1060 map.PERIOD_NAME AS PERIOD_NAME,
1061 decode(l_action_flag,'D',0,
1062 (select nvl(max(in_ev.event_number),0)
1063 from xla_events in_ev /*bug 5867069*/
1064 where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
1065 ORDER BY decode(ev.OVERRIDE_EVENT,
1066 ev.TRX_TYPE||'_CREATE',1,
1067 ev.TRX_TYPE||'_UPDATE',2,
1068 3), ev.GL_DATE, decode(EV.PST_ID,
1069 -3, 2,
1070 1), EV.PST_ID) AS LINE_NUM
1071 FROM
1072 (select /*+ ordered rowid(ct) use_nl(trh,trh1,te) use_hash(gps) swap_join_inputs(gps) use_hash(sys,tty) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
1073 ct.customer_trx_id TRX_ID ,
1074 ct.trx_date TRX_DATE ,
1075 ct.set_of_books_id SOB_ID ,
1076 tty.type TRX_TYPE ,
1077 decode(sys.accounting_method,
1078 'CASH', 'N',
1079 decode(tty.post_to_gl,
1080 'N', 'N',
1081 decode(ct.complete_flag,
1082 'Y',decode(trh.posting_control_id,
1083 -3,decode(trh.status,
1084 'INCOMPLETE', 'I',
1085 'PENDING_ACCEPTANCE','I',
1086 'U'),
1087 'P'),
1088 'I'))) TRX_STATUS ,
1089 decode(trh.event,
1090 'INCOMPLETE' , 'BILL_CREATE',
1091 'ACCEPTED' , 'BILL_CREATE',
1092 'COMPLETED' , decode(trh.status,
1093 'PENDING_ACCEPTANCE', 'BILL_CREATE',
1094 'PENDING_REMITTANCE', 'BILL_CREATE',
1095 'NO_EVENT'),
1096 'CANCELLED' , 'BILL_REVERSE',
1097 decode(trh1.first_posted_record_flag,
1098 '', 'BILL_CREATE',
1099 decode(nvl(trunc(trh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1100 nvl(trunc(trh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1101 decode(trh.posting_control_id,
1102 trh1.posting_control_id, 'BILL_CREATE',
1103 'BILL_UPDATE'),
1104 'BILL_UPDATE'))) OVERRIDE_EVENT,
1105 decode(trh.posting_control_id,
1106 -3, 'U',
1107 'P') PSTD_FLG ,
1108 trh.posting_control_id PST_ID ,
1109 nvl(trunc(trh.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE ,
1110 nvl(trunc(max(trh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
1111 ct.doc_sequence_id DOC_SEQ_ID ,
1112 ct.doc_sequence_value DOC_SEQ_VAL ,
1113 tty.name CAT_CODE ,
1114 te.entity_id ENTITY_ID
1115 FROM ra_customer_trx_all ct,
1116 ar_transaction_history_all trh,
1117 xla_upgrade_dates gps,
1118 ar_transaction_history_all trh1,
1119 ar_system_parameters_all sys,
1120 ra_cust_trx_types_all tty,
1121 xla_transaction_entities_upg te
1122 WHERE ct.rowid >= l_start_rowid
1123 AND ct.rowid <= l_end_rowid
1124 AND NVL(ct.ax_accounted_flag,'N') = 'N'
1125 AND ct.customer_trx_id = trh.customer_trx_id
1126 and trh.event_id is null
1127 and trunc(trh.gl_date) between gps.start_date and gps.end_date
1128 and gps.ledger_id = ct.set_of_books_id
1129 and decode(trh.posting_control_id,
1130 -3, decode(l_action_flag,
1131 'D','P',
1132 l_action_flag),
1133 'P') = 'P'
1134 AND ct.customer_trx_id = trh1.customer_trx_id (+)
1135 AND 'Y' = trh1.first_posted_record_flag (+)
1136 AND decode(trh.event,
1137 'INCOMPLETE', decode(trh1.first_posted_record_flag,'','Y',
1138 'N'),
1139 'COMPLETED', decode(trh.status,
1140 'PENDING_ACCEPTANCE',
1141 decode(trh1.first_posted_record_flag,
1142 '','Y',
1143 'N'),
1144 trh.postable_flag),
1145 trh.postable_flag) = 'Y'
1146 AND sys.org_id = ct.org_id
1147 AND ct.cust_trx_type_id = tty.cust_trx_type_id
1148 AND ct.org_id = tty.org_id
1149 AND te.application_id = 222
1150 AND te.ledger_id = ct.set_of_books_id
1151 AND te.entity_code = 'BILLS_RECEIVABLE'
1152 AND nvl(te.source_id_int_1,-99) = ct.customer_trx_id
1153 --AND te.upg_batch_id = l_batch_id
1154 GROUP BY
1155 ct.customer_trx_id,
1156 ct.trx_date,
1157 ct.set_of_books_id,
1158 te.entity_id,
1159 tty.type,
1160 decode(sys.accounting_method,
1161 'CASH', 'N',
1162 decode(tty.post_to_gl,
1163 'N', 'N',
1164 decode(ct.complete_flag,
1165 'Y',decode(trh.posting_control_id,
1166 -3,decode(trh.status,
1167 'INCOMPLETE', 'I',
1168 'PENDING_ACCEPTANCE','I',
1169 'U'),
1170 'P'),
1171 'I'))) ,
1172 ct.org_id,
1173 decode(trh.event,
1174 'INCOMPLETE' , 'BILL_CREATE',
1175 'ACCEPTED' , 'BILL_CREATE',
1176 'COMPLETED' , decode(trh.status,
1177 'PENDING_ACCEPTANCE', 'BILL_CREATE',
1178 'PENDING_REMITTANCE', 'BILL_CREATE',
1179 'NO_EVENT'),
1180 'CANCELLED' , 'BILL_REVERSE',
1181 decode(trh1.first_posted_record_flag,
1182 '', 'BILL_CREATE',
1183 decode(nvl(trunc(trh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1184 nvl(trunc(trh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1185 decode(trh.posting_control_id,
1186 trh1.posting_control_id, 'BILL_CREATE',
1187 'BILL_UPDATE'),
1188 'BILL_UPDATE'))),
1189 trh.posting_control_id,
1190 nvl(trunc(trh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1191 ct.doc_sequence_id,
1192 ct.doc_sequence_value,
1193 tty.name) ev,
1194 gl_ledgers lgr,
1195 gl_date_period_map map
1196 where ev.sob_id = lgr.ledger_id
1197 and map.period_set_name = lgr.period_set_name
1198 and map.period_type = lgr.accounted_period_type
1199 and map.accounting_date = ev.gl_date
1200 --AND per.adjustment_period_flag = 'N'
1201 group by decode(trx_type,
1202 'CM', 'Credit Memos',
1203 'DM', 'Debit Memos',
1204 'CB', 'Chargebacks',
1205 'Sales Invoices') ,
1206 ev.TRX_ID ,
1207 ev.TRX_DATE ,
1208 ev.SOB_ID ,
1209 ev.CAT_CODE ,
1210 ev.TRX_TYPE ,
1211 ev.TRX_STATUS ,
1212 ev.OVERRIDE_EVENT ,
1213 ev.PSTD_FLG ,
1214 ev.PST_ID ,
1215 ev.GL_DATE ,
1216 ev.DOC_SEQ_ID ,
1217 ev.DOC_SEQ_VAL ,
1218 ev.ENTITY_ID ,
1219 map.PERIOD_NAME ;
1220
1221 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1222
1223 END IF; --create events
1224
1225 /*--------------------------------------------------------------------------+
1226 | Insert the BR lines |
1227 +--------------------------------------------------------------------------*/
1228 IF NVL(l_entity_type,'L') = 'L' THEN
1229
1230 INSERT ALL
1231 WHEN 1 = 1 THEN
1232 INTO XLA_AE_LINES
1233 (upg_batch_id,
1234 ae_header_id,
1235 ae_line_num,
1236 application_id,
1237 code_combination_id,
1238 gl_transfer_mode_code,
1239 accounted_dr,
1240 accounted_cr,
1241 currency_code,
1242 currency_conversion_date,
1243 currency_conversion_rate,
1244 currency_conversion_type,
1245 entered_dr,
1246 entered_cr,
1247 description,
1248 accounting_class_code,
1249 gl_sl_link_id,
1250 gl_sl_link_table,
1251 party_type_code,
1252 party_id,
1253 party_site_id,
1254 statistical_amount,
1255 ussgl_transaction_code,
1256 jgzz_recon_ref,
1257 control_balance_flag,
1258 analytical_balance_flag,
1259 creation_date,
1260 created_by,
1261 last_update_date,
1262 last_updated_by,
1263 last_update_login,
1264 program_update_date,
1265 program_id,
1266 program_application_id,
1267 request_id,
1268 gain_or_loss_flag,
1269 accounting_date,
1270 ledger_id
1271 )
1272 VALUES
1273 ( batch_id,
1274 ae_header_id,
1275 line_num,
1276 222,
1277 code_combination_id,
1278 'D', --gl transfer mode Summary or detail
1279 acctd_amount_dr,
1280 acctd_amount_cr,
1281 currency_code,
1282 exchange_date,
1283 exchange_rate,
1284 exchange_type,
1285 amount_dr,
1286 amount_cr,
1287 '', --description TBD
1288 nvl(account_class,'XXXX'), --accounting class code
1289 xla_gl_sl_link_id_s.nextval, --gl sl link id
1290 'XLAJEL', --gl sl link table
1291 DECODE(third_party_id, NULL, NULL,'C'), --party type code
1292 third_party_id, --party id
1293 third_party_sub_id, --third party site
1294 '', --statistical amount
1295 '', --ussgl trx code
1296 '', --jgzz recon ref
1297 '', --control balance flag
1298 '', --analytical balance
1299 sysdate, --row who columns
1300 0,
1301 sysdate,
1302 0,
1303 0,
1304 sysdate,
1305 0, --program id
1306 222,
1307 '', --request id
1308 gain_or_loss_flag,
1309 accounting_date,
1310 ledger_id)
1311 WHEN 1 = 1 THEN
1312 INTO XLA_DISTRIBUTION_LINKS
1313 (APPLICATION_ID,
1314 EVENT_ID,
1315 AE_HEADER_ID,
1316 AE_LINE_NUM,
1317 ACCOUNTING_LINE_CODE,
1318 ACCOUNTING_LINE_TYPE_CODE,
1319 REF_AE_HEADER_ID,
1320 SOURCE_DISTRIBUTION_TYPE,
1321 SOURCE_DISTRIBUTION_ID_CHAR_1,
1322 SOURCE_DISTRIBUTION_ID_CHAR_2,
1323 SOURCE_DISTRIBUTION_ID_CHAR_3,
1324 SOURCE_DISTRIBUTION_ID_CHAR_4,
1325 SOURCE_DISTRIBUTION_ID_CHAR_5,
1326 SOURCE_DISTRIBUTION_ID_NUM_1,
1327 SOURCE_DISTRIBUTION_ID_NUM_2,
1328 SOURCE_DISTRIBUTION_ID_NUM_3,
1329 SOURCE_DISTRIBUTION_ID_NUM_4,
1330 SOURCE_DISTRIBUTION_ID_NUM_5,
1331 UNROUNDED_ENTERED_DR,
1332 UNROUNDED_ENTERED_CR,
1333 UNROUNDED_ACCOUNTED_DR,
1334 UNROUNDED_ACCOUNTED_CR,
1335 MERGE_DUPLICATE_CODE,
1336 TAX_LINE_REF_ID,
1337 TAX_SUMMARY_LINE_REF_ID,
1338 TAX_REC_NREC_DIST_REF_ID,
1339 STATISTICAL_AMOUNT,
1340 TEMP_LINE_NUM,
1341 EVENT_TYPE_CODE,
1342 EVENT_CLASS_CODE,
1343 REF_EVENT_ID,
1344 UPG_BATCH_ID)
1345 VALUES
1346 (222,
1347 event_id,
1348 ae_header_id,
1349 line_num,
1350 account_class,
1351 'C', --accounting line code customer
1352 ae_header_id, --reference header id
1353 source_table,
1354 '', --src dist id char
1355 '',
1356 '',
1357 '',
1358 '',
1359 line_id, --src dist id num
1360 '',
1361 '',
1362 '',
1363 '',
1364 amount_dr,
1365 amount_cr,
1366 acctd_amount_dr,
1367 acctd_amount_cr,
1368 'N', --merge dup code
1369 tax_line_id, --tax_line_ref_id
1370 '', --tax_summary_line_ref_id
1371 '', --tax_rec_nrec_dist_ref_id
1372 '', --statistical amount
1373 line_num, --temp_line_num
1374 event_type_code, --event_type_code
1375 event_class_code, --event class code
1376 '', --ref_event_id,
1377 batch_id) --upgrade batch id
1378 select
1379 l_batch_id AS batch_id,
1380 ae_header_id AS ae_header_id,
1381 line_id AS line_id,
1382 event_id AS event_id,
1383 account_class AS account_class,
1384 source_table AS source_table,
1385 code_combination_id AS code_combination_id,
1386 amount_dr AS amount_dr,
1387 amount_cr AS amount_cr,
1388 acctd_amount_dr AS acctd_amount_dr,
1389 acctd_amount_cr AS acctd_amount_cr,
1390 nvl(currency_code,'XXXX') AS currency_code,
1391 third_party_id AS third_party_id,
1392 third_party_sub_id AS third_party_sub_id,
1393 exchange_date AS exchange_date,
1394 exchange_rate AS exchange_rate,
1395 exchange_type AS exchange_type,
1396 tax_line_id AS tax_line_id,
1397 gain_or_loss_flag AS gain_or_loss_flag,
1398 event_type_code AS event_type_code,
1399 event_class_code AS event_class_code,
1400 accounting_date AS accounting_date,
1401 ledger_id AS ledger_id,
1402 RANK() OVER (PARTITION BY event_id, ae_header_id
1403 ORDER BY line_id, ln_order) AS line_num
1404 FROM
1405 ( select /*+ ordered rowid(ct) use_nl(trh,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
1406 hdr.ae_header_id ae_header_id,
1407 decode(ard.source_type, 'FACTOR', 'FAC_BR',
1408 'REMITTANCE','REM_BR',
1409 'REC', 'BILL_REC',
1410 'UNPAIDREC', 'UNPAID_BR',
1411 ard.source_type) account_class,
1412 'AR_DISTRIBUTIONS_ALL' source_table,
1413 ard.code_combination_id code_combination_id,
1414 ard.amount_dr amount_dr,
1415 ard.amount_cr amount_cr,
1416 ard.acctd_amount_dr acctd_amount_dr,
1417 ard.acctd_amount_cr acctd_amount_cr,
1418 ard.currency_code currency_code,
1419 ard.third_party_id third_party_id,
1420 ard.third_party_sub_id third_party_sub_id,
1421 ard.currency_conversion_date exchange_date,
1422 ard.currency_conversion_rate exchange_rate,
1423 ard.currency_conversion_type exchange_type,
1424 ard.line_id line_id,
1425 hdr.event_id event_id,
1426 ev.event_type_code event_type_code,
1427 'BILL' event_class_code,
1428 null tax_line_id,
1429 'N' gain_or_loss_flag,
1430 hdr.accounting_date accounting_date,
1431 hdr.ledger_id ledger_id,
1432 1 ln_order
1433 from ra_customer_trx_all ct,
1434 ar_transaction_history_all trh,
1435 xla_upgrade_dates gps,
1436 xla_transaction_entities_upg ent,
1437 xla_events ev,
1438 xla_ae_headers hdr,
1439 ar_distributions_all ard
1440 where ct.rowid >= l_start_rowid
1441 and ct.rowid <= l_end_rowid
1442 and NVL(ct.ax_accounted_flag,'N') = 'N'
1443 and ct.customer_trx_id = trh.customer_trx_id
1444 and trunc(trh.gl_date) between gps.start_date and gps.end_date
1445 and gps.ledger_id = ct.set_of_books_id
1446 and ent.application_id = 222
1447 and ent.ledger_id = ct.set_of_books_id
1448 and ent.entity_code = 'BILLS_RECEIVABLE'
1449 and nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
1450 and ent.entity_id = ev.entity_id
1451 and ev.application_id = 222
1452 and ev.upg_batch_id = l_batch_id
1453 and trh.posting_control_id = ev.reference_num_1
1454 and nvl(trunc(trh.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
1455 AND decode(trh.event,
1456 'INCOMPLETE', 'Y',
1457 'COMPLETED', decode(trh.status,
1458 'PENDING_ACCEPTANCE','Y',
1459 trh.postable_flag),
1460 trh.postable_flag) = 'Y'
1461 AND decode(trh.event,
1462 'CANCELLED', 'BILL_REVERSE',
1463 ev.event_type_code) = ev.event_type_code
1464 and hdr.application_id = 222
1465 and ct.set_of_books_id = hdr.ledger_id
1466 and hdr.event_id = ev.event_id
1467 and ard.source_id = trh.transaction_history_id
1468 and ard.source_table = 'TH');
1469 --order by entity_id, ae_header_id, line_num;
1470
1471 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1472
1473 END IF; --create lines
1474
1475 ad_parallel_updates_pkg.processed_rowid_range(
1476 l_rows_processed,
1477 l_end_rowid);
1478
1479 commit;
1480
1481 ad_parallel_updates_pkg.get_rowid_range(
1482 l_start_rowid,
1483 l_end_rowid,
1484 l_any_rows_to_process,
1485 l_batch_size,
1486 FALSE);
1487
1488 l_rows_processed := 0 ;
1489
1490 END LOOP ; /* end of WHILE loop */
1491
1492 EXCEPTION
1493 WHEN NO_DATA_FOUND THEN
1494 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.upgrade_bills_receivable');
1495 RAISE;
1496
1497 WHEN OTHERS THEN
1498 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.upgrade_bills_receivable');
1499 RAISE;
1500
1501 END UPGRADE_BILLS_RECEIVABLE;
1502
1503
1504 PROCEDURE UPGRADE_RECEIPTS(
1505 l_table_owner IN VARCHAR2,
1506 l_table_name IN VARCHAR2,
1507 l_script_name IN VARCHAR2,
1508 l_worker_id IN VARCHAR2,
1509 l_num_workers IN VARCHAR2,
1510 l_batch_size IN VARCHAR2,
1511 l_batch_id IN NUMBER,
1512 l_action_flag IN VARCHAR2,
1513 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
1514
1515 l_start_rowid rowid;
1516 l_end_rowid rowid;
1517 l_any_rows_to_process boolean;
1518 l_rows_processed number := 0;
1519
1520 BEGIN
1521
1522 /* ------ Initialize the rowid ranges ------ */
1523 ad_parallel_updates_pkg.initialize_rowid_range(
1524 ad_parallel_updates_pkg.ROWID_RANGE,
1525 l_table_owner,
1526 l_table_name,
1527 l_script_name,
1528 l_worker_id,
1529 l_num_workers,
1530 l_batch_size, 0);
1531
1532 /* ------ Get rowid ranges ------ */
1533 ad_parallel_updates_pkg.get_rowid_range(
1534 l_start_rowid,
1535 l_end_rowid,
1536 l_any_rows_to_process,
1537 l_batch_size,
1538 TRUE);
1539
1540 WHILE ( l_any_rows_to_process = TRUE )
1541 LOOP
1542
1543 l_rows_processed := 0;
1544
1545 -------------------------------------------------------------------
1546 -- Create the Entities
1547 -- Created by ar120recent.sql
1548 -------------------------------------------------------------------
1549
1550 -------------------------------------------------------------------
1551 -- Create the Journal Entry Events and Headers
1552 -- category definitions can be found in argper.lpc function arguje
1553 -------------------------------------------------------------------
1554 IF NVL(l_entity_type,'E') = 'E' THEN
1555
1556 INSERT ALL
1557 WHEN 1 = 1 THEN
1558 INTO XLA_EVENTS
1559 (upg_batch_id,
1560 upg_source_application_id,
1561 application_id,
1562 reference_num_1,
1563 reference_num_2,
1564 event_type_code,
1565 event_number,
1566 event_status_code,
1567 process_status_code,
1568 on_hold_flag,
1569 event_date,
1570 creation_date,
1571 created_by,
1572 last_update_date,
1573 last_updated_by,
1574 last_update_login,
1575 program_update_date,
1576 program_id,
1577 program_application_id,
1578 request_id,
1579 entity_id,
1580 event_id,
1581 upg_valid_flag,
1582 transaction_date)
1583 VALUES
1584 (batch_id,
1585 222,
1586 222,
1587 pst_id, --reference num 1
1588 trx_id, --reference num 2
1589 override_event, --event type
1590 line_num,
1591 trx_status, --event status code I, U, N, P
1592 pstd_flg, --process status
1593 'N',
1594 gl_date, --event date
1595 sysdate,
1596 0,
1597 sysdate,
1598 0,
1599 0,
1600 sysdate,
1601 0,
1602 222,
1603 '',
1604 entity_id,
1605 xla_events_s.nextval,
1606 'Y', --upgrade flag
1607 trx_date
1608 )
1609 WHEN PST_ID <> -3 THEN
1610 INTO XLA_AE_HEADERS
1611 (upg_batch_id,
1612 upg_source_application_id,
1613 application_id,
1614 amb_context_code,
1615 entity_id,
1616 event_id,
1617 event_type_code,
1618 ae_header_id,
1619 ledger_id,
1620 accounting_date,
1621 period_name,
1622 reference_date,
1623 balance_type_code,
1624 je_category_name,
1625 gl_transfer_status_code,
1626 gl_transfer_date,
1627 accounting_entry_status_code,
1628 accounting_entry_type_code,
1629 description,
1630 budget_version_id,
1631 funds_status_code,
1632 encumbrance_type_id,
1633 completed_date,
1634 doc_sequence_id,
1635 doc_sequence_value,
1636 doc_category_code,
1637 packet_id,
1638 group_id,
1639 creation_date,
1640 created_by,
1641 last_update_date,
1642 last_updated_by,
1643 last_update_login,
1644 program_update_date,
1645 program_id,
1646 program_application_id,
1647 request_id,
1648 close_acct_seq_assign_id,
1649 close_acct_seq_version_id,
1650 close_acct_seq_value,
1651 completion_acct_seq_assign_id,
1652 completion_acct_seq_version_id,
1653 completion_acct_seq_value,
1654 upg_valid_flag
1655 )
1656 VALUES
1657 (batch_id,
1658 222,
1659 222,
1660 'DEFAULT', --amb context code
1661 entity_id,
1662 xla_events_s.nextval,
1663 override_event,
1664 xla_ae_headers_s.nextval,
1665 sob_id,
1666 gl_date,
1667 period_name,
1668 '', --reference date global acct eng
1669 'A', --balance type Actual
1670 category, --category
1671 'Y', --gl transfer status
1672 gl_posted_date, --gl transfer date
1673 'F', --acct entry status code final
1674 'STANDARD', --acct entry type code
1675 '', --description TBD
1676 '', --budget version id
1677 '', --funds status code
1678 '', --encumbrance type id
1679 '', --completed date
1680 doc_seq_id,
1681 doc_seq_val,
1682 cat_code,
1683 '', --packet id
1684 '', --group id
1685 sysdate, --row who creation date
1686 0,
1687 sysdate,
1688 0,
1689 0,
1690 sysdate,
1691 0, --program id
1692 222,
1693 '', --request id
1694 '', --AX columns start
1695 '',
1696 '',
1697 '',
1698 '',
1699 '',
1700 '' --upg valid flag
1701 --''
1702 )
1703 select /*+ use_nl(lgr,map) */
1704 l_batch_id AS BATCH_ID,
1705 decode(trx_type,
1706 'CASH' , 'Trade Receipts',
1707 --'CROSS_CURR' , 'Cross Currency',
1708 'MISC' , 'Misc Receipts',
1709 'RATE_ADJUST', 'Rate Adjustments',
1710 trx_type) AS CATEGORY,
1711 ev.TRX_ID AS TRX_ID,
1712 ev.TRX_DATE AS TRX_DATE,
1713 ev.SOB_ID AS SOB_ID,
1714 ev.CAT_CODE AS CAT_CODE,
1715 ev.TRX_TYPE AS TRX_TYPE,
1716 ev.TRX_STATUS AS TRX_STATUS,
1717 ev.OVERRIDE_EVENT AS OVERRIDE_EVENT,
1718 ev.PSTD_FLG AS PSTD_FLG,
1719 ev.PST_ID AS PST_ID,
1720 ev.GL_DATE AS GL_DATE,
1721 max(ev.GL_POSTED_DATE) AS GL_POSTED_DATE,
1722 ev.DOC_SEQ_ID AS DOC_SEQ_ID,
1723 ev.DOC_SEQ_VAL AS DOC_SEQ_VAL,
1724 ev.ENTITY_ID AS ENTITY_ID,
1725 map.PERIOD_NAME AS PERIOD_NAME,
1726 decode(l_action_flag,'D',0,
1727 (select nvl(max(in_ev.event_number),0)
1728 from xla_events in_ev /*bug5867069*/
1729 where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
1730 ORDER BY decode(ev.OVERRIDE_EVENT,
1731 'RECP_CREATE' ,1,
1732 'RECP_UPDATE' ,2,
1733 'RECP_RATE_ADJUST' ,3,
1734 'RECP_REVERSE' ,6,
1735 'MISC_RECP_CREATE' ,7,
1736 'MISC_RECP_UPDATE' ,8,
1737 'MISC_RECP_RATE_ADJUST',9,
1738 'MISC_RECP_REVERSE' ,12,
1739 13), EV.GL_DATE, decode(EV.PST_ID,
1740 -3, 2,
1741 1), EV.PST_ID) LINE_NUM
1742 FROM
1743 (select /*+ ordered rowid(cr) use_nl(crh,rmth,crh1,te) use_hash(gps) swap_join_inputs(gps) INDEX(te xla_transaction_entities_N1) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
1744 cr.cash_receipt_id TRX_ID ,
1745 cr.receipt_date TRX_DATE ,
1746 cr.set_of_books_id SOB_ID ,
1747
1748 decode(crh.created_from,
1749 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
1750 cr.type) TRX_TYPE ,
1751 decode(crh.status,
1752 'APPROVED', 'I',
1753 decode(crh.posting_control_id,
1754 -3, 'U',
1755 'P')) TRX_STATUS ,
1756 decode(cr.type,
1757 'MISC', 'MISC_',
1758 '') ||
1759 decode(crh.created_from,
1760 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
1761 decode(crh.status,
1762 'REVERSED','RECP_REVERSE',
1763 decode(crh1.first_posted_record_flag,
1764 '', 'RECP_CREATE',
1765 decode(decode(crh.postable_flag,
1766 'N', to_date('01-01-1900','DD-MM-YYYY'),
1767 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY'))),
1768 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1769 decode(crh.posting_control_id,
1770 crh1.posting_control_id, 'RECP_CREATE',
1771 'RECP_UPDATE'),
1772 'RECP_UPDATE')))) OVERRIDE_EVENT,
1773 decode(crh.posting_control_id,
1774 -3, 'U',
1775 'P') PSTD_FLG ,
1776 crh.posting_control_id PST_ID ,
1777 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
1778 nvl(trunc(max(crh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
1779 cr.doc_sequence_id DOC_SEQ_ID ,
1780 cr.doc_sequence_value DOC_SEQ_VAL ,
1781 rmth.name CAT_CODE ,
1782 te.entity_id ENTITY_ID
1783 FROM ar_cash_receipts_all cr,
1784 --ar_system_parameters_all sys,
1785 ar_cash_receipt_history_all crh,
1786 xla_upgrade_dates gps,
1787 ar_receipt_methods rmth,
1788 ar_cash_receipt_history_all crh1,
1789 xla_transaction_entities_upg te
1790 WHERE cr.rowid >= l_start_rowid
1791 AND cr.rowid <= l_end_rowid
1792 AND NVL(cr.ax_accounted_flag,'N') = 'N'
1793 AND crh.cash_receipt_id = cr.cash_receipt_id
1794 and crh.event_id is null
1795 and trunc(crh.gl_date) between gps.start_date and gps.end_date
1796 and gps.ledger_id = cr.set_of_books_id
1797 and decode(crh.posting_control_id,
1798 -3, decode(l_action_flag,
1799 'D','P',
1800 l_action_flag),
1801 'P') = 'P'
1802 AND cr.receipt_method_id = rmth.receipt_method_id
1803 AND cr.cash_receipt_id = crh1.cash_receipt_id (+)
1804 AND 'Y' = crh1.first_posted_record_flag (+)
1805 AND te.application_id = 222
1806 AND te.ledger_id = cr.set_of_books_id
1807 AND te.entity_code = 'RECEIPTS'
1808 AND nvl(te.source_id_int_1,-99) = cr.cash_receipt_id
1809 AND decode(crh.postable_flag, 'Y','Y',
1810 decode(crh.status, 'APPROVED',
1811 decode(crh1.first_posted_record_flag, '','Y',
1812 'N'),
1813 'N')) = 'Y'
1814 --AND te.upg_batch_id = l_batch_id
1815 --AND nvl(sys.org_id,-9999) = nvl(ct.org_id, -9999)
1816 --AND sys.accounting_method = 'ACCRUAL'
1817 GROUP BY cr.cash_receipt_id,
1818 cr.receipt_date,
1819 cr.set_of_books_id,
1820 te.entity_id,
1821 crh.postable_flag,
1822 decode(crh.created_from,
1823 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
1824 cr.type),
1825 decode(crh.status,
1826 'APPROVED', 'I',
1827 decode(crh.posting_control_id,
1828 -3, 'U',
1829 'P')),
1830 cr.org_id,
1831 decode(cr.type,
1832 'MISC', 'MISC_',
1833 '') || decode(crh.created_from,
1834 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
1835 decode(crh.status,
1836 'REVERSED','RECP_REVERSE',
1837 decode(crh1.first_posted_record_flag,
1838 '', 'RECP_CREATE',
1839 decode(decode(crh.postable_flag,
1840 'N', to_date('01-01-1900','DD-MM-YYYY'),
1841 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY'))),
1842 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1843 decode(crh.posting_control_id,
1844 crh1.posting_control_id, 'RECP_CREATE',
1845 'RECP_UPDATE'),
1846 'RECP_UPDATE')))),
1847 decode(crh.posting_control_id,
1848 -3, 'U',
1849 'P') ,
1850 crh.posting_control_id,
1851 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1852 cr.doc_sequence_id,
1853 cr.doc_sequence_value,
1854 rmth.name
1855 UNION
1856 select /*+ ordered rowid(cr) use_nl(mcd,rmth,crh,te) use_hash(gps) swap_join_inputs(gps) INDEX(te xla_transaction_entities_N1) INDEX_SS(crh ar_cash_receipt_history_n1) */
1857 mcd.cash_receipt_id TRX_ID ,
1858 cr.receipt_date TRX_DATE ,
1859 cr.set_of_books_id SOB_ID ,
1860 decode(mcd.created_from,
1861 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
1862 cr.type) TRX_TYPE ,
1863 decode(mcd.posting_control_id,
1864 -3, 'U',
1865 'P') TRX_STATUS ,
1866 decode(mcd.created_from,
1867 'RATE ADJUSTMENT TRIGGER', 'MISC_RECP_RATE_ADJUST',
1868 decode(SUBSTRB(mcd.created_from,1,19),
1869 'ARP_REVERSE_RECEIPT','MISC_RECP_REVERSE',
1870 decode(nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1871 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1872 decode(crh.posting_control_id,
1873 mcd.posting_control_id, 'MISC_RECP_CREATE',
1874 'MISC_RECP_UPDATE'),
1875 'MISC_RECP_UPDATE'))) OVERRIDE_EVENT,
1876 decode(mcd.posting_control_id,
1877 -3, 'U',
1878 'P') PSTD_FLG ,
1879 mcd.posting_control_id PST_ID ,
1880 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
1881 nvl(trunc(max(mcd.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
1882 cr.doc_sequence_id DOC_SEQ_ID ,
1883 cr.doc_sequence_value DOC_SEQ_VAL ,
1884 rmth.name CAT_CODE ,
1885 te.entity_id ENTITY_ID
1886 FROM ar_cash_receipts_all cr,
1887 --ar_system_parameters_all sys,
1888 ar_misc_cash_distributions_all mcd,
1889 xla_upgrade_dates gps,
1890 ar_receipt_methods rmth,
1891 ar_cash_receipt_history_all crh,
1892 xla_transaction_entities_upg te
1893 WHERE cr.rowid >= l_start_rowid
1894 AND cr.rowid <= l_end_rowid
1895 AND NVL(cr.ax_accounted_flag,'N') = 'N'
1896 AND cr.type='MISC'
1897 AND mcd.cash_receipt_id = cr.cash_receipt_id
1898 and trunc(mcd.gl_date) between gps.start_date and gps.end_date
1899 and mcd.event_id is null
1900 and gps.ledger_id = cr.set_of_books_id
1901 and decode(mcd.posting_control_id,
1902 -3, decode(l_action_flag,
1903 'D','P',
1904 l_action_flag),
1905 'P') = 'P'
1906 AND cr.receipt_method_id = rmth.receipt_method_id
1907 AND cr.cash_receipt_id = crh.cash_receipt_id
1908 AND crh.first_posted_record_flag = 'Y'
1909 AND te.application_id = 222
1910 AND te.ledger_id = cr.set_of_books_id
1911 AND te.entity_code = 'RECEIPTS'
1912 AND nvl(te.source_id_int_1,-99) = cr.cash_receipt_id
1913 --AND te.upg_batch_id = l_batch_id
1914 --AND nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
1915 --AND sys.accounting_method = 'ACCRUAL'
1916 GROUP BY mcd.cash_receipt_id,
1917 cr.receipt_date,
1918 cr.set_of_books_id,
1919 te.entity_id,
1920 'Y',
1921 decode(mcd.created_from,
1922 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
1923 cr.type),
1924 decode(mcd.posting_control_id,
1925 -3, 'U',
1926 'P'),
1927 mcd.org_id,
1928 decode(mcd.created_from,
1929 'RATE ADJUSTMENT TRIGGER', 'MISC_RECP_RATE_ADJUST',
1930 decode(SUBSTRB(mcd.created_from,1,19),
1931 'ARP_REVERSE_RECEIPT','MISC_RECP_REVERSE',
1932 decode(nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1933 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1934 decode(crh.posting_control_id,
1935 mcd.posting_control_id, 'MISC_RECP_CREATE',
1936 'MISC_RECP_UPDATE'),
1937 'MISC_RECP_UPDATE'))),
1938 decode(mcd.posting_control_id,
1939 -3, 'U',
1940 'P') ,
1941 mcd.posting_control_id,
1942 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1943 cr.doc_sequence_id,
1944 cr.doc_sequence_value,
1945 rmth.name
1946 UNION
1947 select /*+ ordered rowid(cr) use_nl(app,crh,crh1,rmth,te) use_hash(gps) swap_join_inputs(gps) INDEX(te xla_transaction_entities_N1) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
1948 cr.cash_receipt_id TRX_ID ,
1949 cr.receipt_date TRX_DATE ,
1950 cr.set_of_books_id SOB_ID ,
1951 decode(crh.created_from,
1952 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
1953 cr.type) TRX_TYPE ,
1954 decode(NVL(app.confirmed_flag,'Y'),
1955 'Y', decode(app.posting_control_id,
1956 -3, 'U',
1957 'P'),
1958 'I') TRX_STATUS ,
1959 decode(crh.created_from,
1960 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
1961 decode(crh.status,
1962 'REVERSED','RECP_REVERSE',
1963 decode(crh1.first_posted_record_flag,
1964 '', 'RECP_CREATE',
1965 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1966 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
1967 decode(app.posting_control_id,
1968 crh1.posting_control_id, 'RECP_CREATE',
1969 'RECP_UPDATE'),
1970 'RECP_UPDATE')))) OVERRIDE_EVENT,
1971 decode(app.posting_control_id,
1972 -3, 'U',
1973 'P') PSTD_FLG ,
1974 app.posting_control_id PST_ID ,
1975 nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
1976 max(decode(crh.created_from,
1977 'RATE ADJUSTMENT TRIGGER',
1978 nvl(trunc((crh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')),
1979 decode(crh.status,
1980 'REVERSED', nvl(trunc((crh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')),
1981 nvl(trunc((app.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY'))))) GL_POSTED_DATE ,
1982 cr.doc_sequence_id DOC_SEQ_ID ,
1983 cr.doc_sequence_value DOC_SEQ_VAL ,
1984 rmth.name CAT_CODE ,
1985 te.entity_id ENTITY_ID
1986 FROM ar_cash_receipts_all cr,
1987 --ar_system_parameters_all sys,
1988 ar_receivable_applications_all app,
1989 xla_upgrade_dates gps,
1990 ar_cash_receipt_history_all crh,
1991 ar_cash_receipt_history_all crh1,
1992 ar_receipt_methods rmth,
1993 xla_transaction_entities_upg te
1994 WHERE cr.rowid >= l_start_rowid
1995 AND cr.rowid <= l_end_rowid
1996 AND NVL(cr.ax_accounted_flag,'N') = 'N'
1997 AND app.cash_receipt_id = cr.cash_receipt_id
1998 AND app.application_type = 'CASH'
1999 and app.event_id is null
2000 and trunc(app.gl_date) between gps.start_date and gps.end_date
2001 and gps.ledger_id = cr.set_of_books_id
2002 and decode(app.posting_control_id,
2003 -3, decode(l_action_flag,
2004 'D','P',
2005 l_action_flag),
2006 'P') = 'P'
2007 AND app.cash_receipt_history_id = crh.cash_receipt_history_id
2008 AND cr.cash_receipt_id = crh1.cash_receipt_id (+)
2009 AND 'Y' = crh1.first_posted_record_flag (+)
2010 AND decode(crh.postable_flag, 'Y','Y',
2011 decode(crh.status, 'APPROVED',
2012 decode(crh1.first_posted_record_flag, '','Y',
2013 'N'),
2014 'N')) = 'Y'
2015 AND cr.receipt_method_id = rmth.receipt_method_id
2016 AND te.application_id = 222
2017 AND te.ledger_id = cr.set_of_books_id
2018 AND te.entity_code = 'RECEIPTS'
2019 AND nvl(te.source_id_int_1,-99) = cr.cash_receipt_id
2020 --AND te.upg_batch_id = l_batch_id
2021 --AND nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
2022 --AND sys.accounting_method = 'ACCRUAL'
2023 GROUP BY cr.cash_receipt_id,
2024 cr.receipt_date,
2025 cr.set_of_books_id,
2026 te.entity_id,
2027 decode(NVL(app.confirmed_flag,'Y'),
2028 'Y', decode(app.posting_control_id,
2029 -3, 'U',
2030 'P'),
2031 'I'),
2032 decode(crh.created_from,
2033 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
2034 cr.type),
2035 cr.org_id,
2036 decode(crh.created_from,
2037 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
2038 decode(crh.status,
2039 'REVERSED','RECP_REVERSE',
2040 decode(crh1.first_posted_record_flag,
2041 '', 'RECP_CREATE',
2042 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2043 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2044 decode(app.posting_control_id,
2045 crh1.posting_control_id, 'RECP_CREATE',
2046 'RECP_UPDATE'),
2047 'RECP_UPDATE')))),
2048 decode(app.posting_control_id,
2049 -3, 'U',
2050 'P') ,
2051 app.posting_control_id ,
2052 nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2053 cr.doc_sequence_id,
2054 cr.doc_sequence_value,
2055 rmth.name) ev,
2056 gl_ledgers lgr,
2057 gl_date_period_map map
2058 where ev.sob_id = lgr.ledger_id
2059 and map.period_set_name = lgr.period_set_name
2060 and map.period_type = lgr.accounted_period_type
2061 and map.accounting_date = ev.gl_date
2062 group by
2063 decode(trx_type,
2064 'CASH' , 'Trade Receipts',
2065 --'CROSS_CURR' , 'Cross Currency',
2066 'MISC' , 'Misc Receipts',
2067 'RATE_ADJUST', 'Rate Adjustments',
2068 trx_type) ,
2069 ev.TRX_ID ,
2070 ev.TRX_DATE ,
2071 ev.SOB_ID ,
2072 ev.CAT_CODE ,
2073 ev.TRX_TYPE ,
2074 ev.TRX_STATUS ,
2075 ev.OVERRIDE_EVENT ,
2076 ev.PSTD_FLG ,
2077 ev.PST_ID ,
2078 ev.GL_DATE ,
2079 ev.DOC_SEQ_ID ,
2080 ev.DOC_SEQ_VAL ,
2081 ev.ENTITY_ID ,
2082 map.PERIOD_NAME ;
2083
2084 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2085
2086 END IF; --create events
2087
2088 -------------------------------------------------------------------
2089 -- Create the Journal Entry Lines
2090 -- gl_transfer_mode_code is a flag indicating whether distributions
2091 -- from AR to subledger tables are in detail or summary. This is
2092 -- different from the standard post to GL summary or detail. So
2093 --from an upgrade perspective for AR this is in detail always
2094 --as AR stores in detailed accounting for historical data.
2095 -------------------------------------------------------------------
2096 IF NVL(l_entity_type,'L') = 'L' THEN
2097
2098 INSERT ALL
2099 WHEN 1 = 1 THEN
2100 INTO XLA_AE_LINES
2101 (upg_batch_id,
2102 ae_header_id,
2103 ae_line_num,
2104 application_id,
2105 code_combination_id,
2106 gl_transfer_mode_code,
2107 accounted_dr,
2108 accounted_cr,
2109 currency_code,
2110 currency_conversion_date,
2111 currency_conversion_rate,
2112 currency_conversion_type,
2113 entered_dr,
2114 entered_cr,
2115 description,
2116 accounting_class_code,
2117 gl_sl_link_id,
2118 gl_sl_link_table,
2119 party_type_code,
2120 party_id,
2121 party_site_id,
2122 statistical_amount,
2123 ussgl_transaction_code,
2124 jgzz_recon_ref,
2125 control_balance_flag,
2126 analytical_balance_flag,
2127 creation_date,
2128 created_by,
2129 last_update_date,
2130 last_updated_by,
2131 last_update_login,
2132 program_update_date,
2133 program_id,
2134 program_application_id,
2135 request_id,
2136 gain_or_loss_flag,
2137 accounting_date,
2138 ledger_id
2139 )
2140 VALUES
2141 ( batch_id,
2142 ae_header_id,
2143 line_num,
2144 222,
2145 code_combination_id,
2146 'D', --gl transfer mode Summary or detail
2147 acctd_amount_dr,
2148 acctd_amount_cr,
2149 currency_code,
2150 exchange_date,
2151 exchange_rate,
2152 exchange_type,
2153 amount_dr,
2154 amount_cr,
2155 '', --description TBD
2156 nvl(account_class,'XXXX'), --accounting class code
2157 xla_gl_sl_link_id_s.nextval, --gl sl link id
2158 'XLAJEL', --gl sl link table
2159 DECODE(third_party_id, NULL, NULL,'C'), --party type code
2160 third_party_id, --party id
2161 third_party_sub_id, --third party site
2162 '', --statistical amount
2163 '', --ussgl trx code
2164 '', --jgzz recon ref
2165 '', --control balance flag
2166 '', --analytical balance
2167 sysdate, --row who columns
2168 0,
2169 sysdate,
2170 0,
2171 0,
2172 sysdate,
2173 0, --program id
2174 222,
2175 '', --request id
2176 gain_or_loss_flag,
2177 accounting_date,
2178 ledger_id)
2179 WHEN 1 = 1 THEN
2180 INTO XLA_DISTRIBUTION_LINKS
2181 (APPLICATION_ID,
2182 EVENT_ID,
2183 AE_HEADER_ID,
2184 AE_LINE_NUM,
2185 ACCOUNTING_LINE_CODE,
2186 ACCOUNTING_LINE_TYPE_CODE,
2187 REF_AE_HEADER_ID,
2188 SOURCE_DISTRIBUTION_TYPE,
2189 SOURCE_DISTRIBUTION_ID_CHAR_1,
2190 SOURCE_DISTRIBUTION_ID_CHAR_2,
2191 SOURCE_DISTRIBUTION_ID_CHAR_3,
2192 SOURCE_DISTRIBUTION_ID_CHAR_4,
2193 SOURCE_DISTRIBUTION_ID_CHAR_5,
2194 SOURCE_DISTRIBUTION_ID_NUM_1,
2195 SOURCE_DISTRIBUTION_ID_NUM_2,
2196 SOURCE_DISTRIBUTION_ID_NUM_3,
2197 SOURCE_DISTRIBUTION_ID_NUM_4,
2198 SOURCE_DISTRIBUTION_ID_NUM_5,
2199 UNROUNDED_ENTERED_DR,
2200 UNROUNDED_ENTERED_CR,
2201 UNROUNDED_ACCOUNTED_DR,
2202 UNROUNDED_ACCOUNTED_CR,
2203 MERGE_DUPLICATE_CODE,
2204 TAX_LINE_REF_ID,
2205 TAX_SUMMARY_LINE_REF_ID,
2206 TAX_REC_NREC_DIST_REF_ID,
2207 STATISTICAL_AMOUNT,
2208 TEMP_LINE_NUM,
2209 EVENT_TYPE_CODE,
2210 EVENT_CLASS_CODE,
2211 REF_EVENT_ID,
2212 UPG_BATCH_ID)
2213 VALUES
2214 (222,
2215 event_id,
2216 ae_header_id,
2217 line_num,
2218 account_class,
2219 'C', --accounting line code customer
2220 ae_header_id, --reference header id
2221 source_table,
2222 '', --src dist id char
2223 '',
2224 '',
2225 '',
2226 '',
2227 line_id, --src dist id num
2228 '',
2229 '',
2230 '',
2231 '',
2232 amount_dr,
2233 amount_cr,
2234 acctd_amount_dr,
2235 acctd_amount_cr,
2236 'N', --merge dup code
2237 '', --tax_line_ref_id
2238 '', --tax_summary_line_ref_id
2239 '', --tax_rec_nrec_dist_ref_id
2240 '', --statistical amount
2241 line_num, --temp_line_num
2242 event_type_code, --event_type_code
2243 event_class_code, --event class code
2244 '', --ref_event_id,
2245 batch_id) --upgrade batch id
2246 select
2247 l_batch_id AS batch_id,
2248 ae_header_id AS ae_header_id,
2249 line_id AS line_id,
2250 event_id AS event_id,
2251 account_class AS account_class,
2252 gain_or_loss_flag AS gain_or_loss_flag,
2253 source_table AS source_table,
2254 code_combination_id AS code_combination_id,
2255 amount_dr AS amount_dr,
2256 amount_cr AS amount_cr,
2257 acctd_amount_dr AS acctd_amount_dr,
2258 acctd_amount_cr AS acctd_amount_cr,
2259 nvl(currency_code,'XXXX') AS currency_code,
2260 third_party_id AS third_party_id,
2261 third_party_sub_id AS third_party_sub_id,
2262 exchange_date AS exchange_date,
2263 exchange_rate AS exchange_rate,
2264 exchange_type AS exchange_type,
2265 event_type_code AS event_type_code,
2266 event_class_code AS event_class_code,
2267 accounting_date AS accounting_date,
2268 ledger_id AS ledger_id,
2269 RANK() OVER (PARTITION BY event_id, ae_header_id
2270 ORDER BY line_id, ln_order) AS line_num
2271 FROM
2272 ( select /*+ ordered rowid(cr) use_nl(crh,crh1,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
2273 hdr.ae_header_id ae_header_id,
2274 decode(ard.source_type, 'BANK_CHARGES', 'BANK_CHG',
2275 ard.source_type) account_class,
2276 'AR_DISTRIBUTIONS_ALL' source_table,
2277 ard.code_combination_id code_combination_id,
2278 ard.amount_dr amount_dr,
2279 ard.amount_cr amount_cr,
2280 ard.acctd_amount_dr acctd_amount_dr,
2281 ard.acctd_amount_cr acctd_amount_cr,
2282 ard.currency_code currency_code,
2283 ard.third_party_id third_party_id,
2284 ard.third_party_sub_id third_party_sub_id,
2285 ard.currency_conversion_date exchange_date,
2286 ard.currency_conversion_rate exchange_rate,
2287 ard.currency_conversion_type exchange_type,
2288 ard.line_id line_id,
2289 ev.event_id event_id,
2290 ev.event_type_code event_type_code,
2291 decode(cr.type,
2292 'CASH','RECEIPT',
2293 'MISC','MISC_RECEIPT',
2294 'RECEIPT') event_class_code,
2295 'N' gain_or_loss_flag,
2296 hdr.accounting_date accounting_date,
2297 hdr.ledger_id ledger_id,
2298 1 ln_order
2299 from ar_cash_receipts_all cr,
2300 ar_cash_receipt_history_all crh,
2301 xla_upgrade_dates gps,
2302 ar_cash_receipt_history_all crh1,
2303 xla_transaction_entities_upg ent,
2304 xla_events ev,
2305 xla_ae_headers hdr,
2306 ar_distributions_all ard
2307 where cr.rowid >= l_start_rowid
2308 and cr.rowid <= l_end_rowid
2309 and nvl(cr.ax_accounted_flag,'N') = 'N'
2310 and cr.cash_receipt_id = crh.cash_receipt_id
2311 and trunc(crh.gl_date) between gps.start_date and gps.end_date
2312 and gps.ledger_id = cr.set_of_books_id
2313 and cr.cash_receipt_id = crh1.cash_receipt_id (+)
2314 and 'Y' = crh1.first_posted_record_flag (+)
2315 and ent.application_id = 222
2316 and ent.ledger_id = cr.set_of_books_id
2317 and ent.entity_code = 'RECEIPTS'
2318 and nvl(ent.source_id_int_1,-99) = cr.cash_receipt_id
2319 and ent.entity_id = ev.entity_id
2320 and ev.application_id = 222
2321 and ev.upg_batch_id = l_batch_id
2322 and crh.posting_control_id = ev.reference_num_1
2323 and nvl(trunc(crh.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
2324 and decode(cr.type,
2325 'MISC','MISC_',
2326 '') ||
2327 decode(crh.created_from,
2328 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
2329 decode(crh.status,
2330 'REVERSED','RECP_REVERSE',
2331 decode(crh1.first_posted_record_flag,
2332 '', 'RECP_CREATE',
2333 decode(decode(crh.postable_flag,
2334 'N', to_date('01-01-1900','DD-MM-YYYY'),
2335 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY'))),
2336 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2337 decode(crh.posting_control_id,
2338 crh1.posting_control_id, 'RECP_CREATE',
2339 'RECP_UPDATE'),
2340 'RECP_UPDATE')))) = ev.event_type_code
2341 and decode(crh.postable_flag, 'Y','Y',
2342 decode(crh.status, 'APPROVED',
2343 decode(crh1.first_posted_record_flag, '','Y',
2344 'N'),
2345 'N')) = 'Y'
2346 and hdr.application_id = 222
2347 and cr.set_of_books_id = hdr.ledger_id
2348 and hdr.event_id = ev.event_id
2349 and ard.source_id = crh.cash_receipt_history_id
2350 and ard.source_table = 'CRH'
2351 UNION ALL /* Receipt applications */
2352 select /*+ ordered rowid(cr) use_nl(sys,app,ent,crh,crh1,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
2353 hdr.ae_header_id ae_header_id,
2354 DECODE(ard.source_type, 'REC', 'RECEIVABLE',
2355 'CURR_ROUND', 'ROUNDING',
2356 'EXCH_GAIN', 'GAIN',
2357 'EXCH_LOSS', 'LOSS',
2358 'OTHER ACC',
2359 DECODE(app.applied_payment_schedule_id,
2360 -1,'ACC',
2361 -2,'SHORT_TERM_DEBT',
2362 -3,'WRITE_OFF',
2363 -4,'CLAIM',
2364 -5,'CHARGEBACK',
2365 -6,'REFUND',
2366 -7,'PREPAY',
2367 -8,'REFUND',
2368 -9,'CHARGEBACK',
2369 ard.source_type),
2370 ard.source_type) account_class,
2371 'AR_DISTRIBUTIONS_ALL' source_table,
2372 ard.code_combination_id code_combination_id,
2373 ard.amount_dr amount_dr,
2374 ard.amount_cr amount_cr,
2375 ard.acctd_amount_dr acctd_amount_dr,
2376 ard.acctd_amount_cr acctd_amount_cr,
2377 ard.currency_code currency_code,
2378 ard.third_party_id third_party_id,
2379 ard.third_party_sub_id third_party_sub_id,
2380 ard.currency_conversion_date exchange_date,
2381 ard.currency_conversion_rate exchange_rate,
2382 ard.currency_conversion_type exchange_type,
2383 ard.line_id line_id,
2384 ev.event_id event_id,
2385 ev.event_type_code event_type_code,
2386 'RECEIPT' event_class_code,
2387 decode(ard.source_type,
2388 'EXCH_GAIN','Y',
2389 'EXCH_LOSS','Y',
2390 'N') gain_or_loss_flag,
2391 hdr.accounting_date accounting_date,
2392 hdr.ledger_id ledger_id,
2393 2 ln_order
2394 from ar_cash_receipts_all cr,
2395 ar_system_parameters_all sys,
2396 ar_receivable_applications_all app,
2397 xla_upgrade_dates gps,
2398 xla_transaction_entities_upg ent,
2399 ar_cash_receipt_history_all crh,
2400 ar_cash_receipt_history_all crh1,
2401 xla_events ev,
2402 xla_ae_headers hdr,
2403 ar_distributions_all ard
2404 where cr.rowid >= l_start_rowid
2405 and cr.rowid <= l_end_rowid
2406 and nvl(cr.ax_accounted_flag,'N') = 'N'
2407 and nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
2408 and sys.accounting_method = 'ACCRUAL'
2409 and cr.cash_receipt_id = app.cash_receipt_id
2410 and app.application_type = 'CASH'
2411 and trunc(app.gl_date) between gps.start_date and gps.end_date
2412 and gps.ledger_id = cr.set_of_books_id
2413 and app.cash_receipt_history_id = crh.cash_receipt_history_id
2414 and cr.cash_receipt_id = crh1.cash_receipt_id (+)
2415 and 'Y' = crh1.first_posted_record_flag (+)
2416 and ent.application_id = 222
2417 and ent.ledger_id = cr.set_of_books_id
2418 and ent.entity_code = 'RECEIPTS'
2419 and nvl(ent.source_id_int_1,-99) = cr.cash_receipt_id
2420 and ev.upg_batch_id = l_batch_id
2421 and ent.entity_id = ev.entity_id
2422 and ev.application_id = 222
2423 and app.posting_control_id = ev.reference_num_1
2424 and nvl(trunc(app.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
2425 and decode(crh.created_from,
2426 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
2427 decode(crh.status,
2428 'REVERSED','RECP_REVERSE',
2429 decode(crh1.first_posted_record_flag,
2430 '', 'RECP_CREATE',
2431 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2432 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2433 decode(app.posting_control_id,
2434 crh1.posting_control_id, 'RECP_CREATE',
2435 'RECP_UPDATE'),
2436 'RECP_UPDATE')))) = ev.event_type_code
2437 and decode(crh.postable_flag, 'Y','Y',
2438 decode(crh.status, 'APPROVED',
2439 decode(crh1.first_posted_record_flag, '','Y',
2440 'N'),
2441 'N')) = 'Y'
2442 and cr.set_of_books_id = hdr.ledger_id
2443 and hdr.event_id = ev.event_id
2444 and hdr.application_id = 222
2445 and ard.source_id = app.receivable_application_id
2446 and ard.source_table = 'RA'
2447 UNION ALL /* Misc Cash Dist */
2448 select /*+ ordered rowid(cr) use_nl(mcd,crh,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh ar_cash_receipt_history_n1) */
2449 hdr.ae_header_id ae_header_id,
2450 DECODE(ard.source_type, 'MISCCASH', 'MISC_CASH',
2451 ard.source_type) account_class,
2452 'AR_DISTRIBUTIONS_ALL' source_table,
2453 ard.code_combination_id code_combination_id,
2454 ard.amount_dr amount_dr,
2455 ard.amount_cr amount_cr,
2456 ard.acctd_amount_dr acctd_amount_dr,
2457 ard.acctd_amount_cr acctd_amount_cr,
2458 ard.currency_code currency_code,
2459 ard.third_party_id third_party_id,
2460 ard.third_party_sub_id third_party_sub_id,
2461 ard.currency_conversion_date exchange_date,
2462 ard.currency_conversion_rate exchange_rate,
2463 ard.currency_conversion_type exchange_type,
2464 ard.line_id line_id,
2465 ev.event_id event_id,
2466 ev.event_type_code event_type_code,
2467 'MISC_RECEIPT' event_class_code,
2468 'N' gain_or_loss_flag,
2469 hdr.accounting_date accounting_date,
2470 hdr.ledger_id ledger_id,
2471 1 ln_order
2472 from ar_cash_receipts_all cr,
2473 ar_misc_cash_distributions_all mcd,
2474 xla_upgrade_dates gps,
2475 ar_cash_receipt_history_all crh,
2476 xla_transaction_entities_upg ent,
2477 xla_events ev,
2478 xla_ae_headers hdr,
2479 ar_distributions_all ard
2480 where cr.rowid >= l_start_rowid
2481 and cr.rowid <= l_end_rowid
2482 and nvl(cr.ax_accounted_flag,'N') = 'N'
2483 and cr.cash_receipt_id = mcd.cash_receipt_id
2484 and trunc(mcd.gl_date) between gps.start_date and gps.end_date
2485 and gps.ledger_id = cr.set_of_books_id
2486 and cr.cash_receipt_id = crh.cash_receipt_id
2487 and crh.first_posted_record_flag = 'Y'
2488 and ent.application_id = 222
2489 and ent.ledger_id = cr.set_of_books_id
2490 and ent.entity_code = 'RECEIPTS'
2491 and nvl(ent.source_id_int_1,-99) = cr.cash_receipt_id
2492 and ent.entity_id = ev.entity_id
2493 and ev.application_id = 222
2494 and ev.upg_batch_id = l_batch_id
2495 and mcd.posting_control_id = ev.reference_num_1
2496 and nvl(trunc(mcd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
2497 and decode(mcd.created_from,
2498 'RATE ADJUSTMENT TRIGGER', 'MISC_RECP_RATE_ADJUST',
2499 decode(SUBSTRB(mcd.created_from,1,19),
2500 'ARP_REVERSE_RECEIPT','MISC_RECP_REVERSE',
2501 decode(nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2502 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2503 decode(crh.posting_control_id,
2504 mcd.posting_control_id, 'MISC_RECP_CREATE',
2505 'MISC_RECP_UPDATE'),
2506 'MISC_RECP_UPDATE'))) = ev.event_type_code
2507 and cr.set_of_books_id = hdr.ledger_id
2508 and hdr.event_id = ev.event_id
2509 and hdr.application_id = 222
2510 and ard.source_id = mcd.misc_cash_distribution_id
2511 and ard.source_table = 'MCD');
2512 --order by entity_id, ae_header_id, line_num;
2513
2514 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2515
2516 END IF; --create lines
2517
2518 ad_parallel_updates_pkg.processed_rowid_range(
2519 l_rows_processed,
2520 l_end_rowid);
2521
2522 commit;
2523
2524 ad_parallel_updates_pkg.get_rowid_range(
2525 l_start_rowid,
2526 l_end_rowid,
2527 l_any_rows_to_process,
2528 l_batch_size,
2529 FALSE);
2530
2531 l_rows_processed := 0 ;
2532
2533 END LOOP ; /* end of WHILE loop */
2534
2535 EXCEPTION
2536 WHEN NO_DATA_FOUND THEN
2537 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.upgrade_receipts');
2538 RAISE;
2539
2540 WHEN OTHERS THEN
2541 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.upgrade_receipts');
2542 RAISE;
2543
2544 END UPGRADE_RECEIPTS;
2545
2546 PROCEDURE UPGRADE_ADJUSTMENTS(
2547 l_table_owner IN VARCHAR2,
2548 l_table_name IN VARCHAR2,
2549 l_script_name IN VARCHAR2,
2550 l_worker_id IN VARCHAR2,
2551 l_num_workers IN VARCHAR2,
2552 l_batch_size IN VARCHAR2,
2553 l_batch_id IN NUMBER,
2554 l_action_flag IN VARCHAR2,
2555 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
2556
2557 l_start_rowid rowid;
2558 l_end_rowid rowid;
2559 l_any_rows_to_process boolean;
2560 l_rows_processed number := 0;
2561
2562 BEGIN
2563
2564 /* ------ Initialize the rowid ranges ------ */
2565 ad_parallel_updates_pkg.initialize_rowid_range(
2566 ad_parallel_updates_pkg.ROWID_RANGE,
2567 l_table_owner,
2568 l_table_name,
2569 l_script_name,
2570 l_worker_id,
2571 l_num_workers,
2572 l_batch_size, 0);
2573
2574 /* ------ Get rowid ranges ------ */
2575 ad_parallel_updates_pkg.get_rowid_range(
2576 l_start_rowid,
2577 l_end_rowid,
2578 l_any_rows_to_process,
2579 l_batch_size,
2580 TRUE);
2581
2582 WHILE ( l_any_rows_to_process = TRUE )
2583 LOOP
2584
2585 l_rows_processed := 0;
2586
2587 -------------------------------------------------------------------
2588 -- Create the Event Entities
2589 -- Created by ar120adjent.sql
2590 -------------------------------------------------------------------
2591
2592 -------------------------------------------------------------------
2593 -- Create the Event Types and Journal Entry Headers
2594 -- category definitions can be found in argper.lpc function arguje
2595 -------------------------------------------------------------------
2596 IF NVL(l_entity_type,'E') = 'E' THEN
2597
2598 INSERT ALL
2599 WHEN 1 = 1 THEN
2600 INTO XLA_EVENTS
2601 (upg_batch_id,
2602 upg_source_application_id,
2603 application_id,
2604 reference_num_1,
2605 reference_num_2,
2606 event_type_code,
2607 event_number,
2608 event_status_code,
2609 process_status_code,
2610 on_hold_flag,
2611 event_date,
2612 creation_date,
2613 created_by,
2614 last_update_date,
2615 last_updated_by,
2616 last_update_login,
2617 program_update_date,
2618 program_id,
2619 program_application_id,
2620 request_id,
2621 entity_id,
2622 event_id,
2623 upg_valid_flag,
2624 transaction_date)
2625 VALUES
2626 (batch_id,
2627 222,
2628 222,
2629 pst_id, --reference num 1
2630 trx_id, --reference num 2
2631 override_event, --event type
2632 line_num,
2633 trx_status, --event status code I, U, N, P
2634 pstd_flg, --process status
2635 'N',
2636 gl_date, --event date
2637 sysdate,
2638 0,
2639 sysdate,
2640 0,
2641 0,
2642 sysdate,
2643 0,
2644 222,
2645 '',
2646 entity_id,
2647 xla_events_s.nextval,
2648 'Y', --upgrade flag
2649 trx_date
2650 )
2651 WHEN PST_ID <> -3 THEN
2652 INTO XLA_AE_HEADERS
2653 (upg_batch_id,
2654 upg_source_application_id,
2655 application_id,
2656 amb_context_code,
2657 entity_id,
2658 event_id,
2659 event_type_code,
2660 ae_header_id,
2661 ledger_id,
2662 accounting_date,
2663 period_name,
2664 reference_date,
2665 balance_type_code,
2666 je_category_name,
2667 gl_transfer_status_code,
2668 gl_transfer_date,
2669 accounting_entry_status_code,
2670 accounting_entry_type_code,
2671 description,
2672 budget_version_id,
2673 funds_status_code,
2674 encumbrance_type_id,
2675 completed_date,
2676 doc_sequence_id,
2677 doc_sequence_value,
2678 doc_category_code,
2679 packet_id,
2680 group_id,
2681 creation_date,
2682 created_by,
2683 last_update_date,
2684 last_updated_by,
2685 last_update_login,
2686 program_update_date,
2687 program_id,
2688 program_application_id,
2689 request_id,
2690 close_acct_seq_assign_id,
2691 close_acct_seq_version_id,
2692 close_acct_seq_value,
2693 completion_acct_seq_assign_id,
2694 completion_acct_seq_version_id,
2695 completion_acct_seq_value,
2696 upg_valid_flag
2697 --upg_worker_id
2698 )
2699 VALUES
2700 (batch_id,
2701 222,
2702 222,
2703 'DEFAULT', --amb context code
2704 entity_id,
2705 xla_events_s.nextval,
2706 override_event,
2707 xla_ae_headers_s.nextval,
2708 sob_id,
2709 gl_date,
2710 period_name,
2711 '', --reference date global acct eng
2712 'A', --balance type Actual
2713 category, --category
2714 'Y', --gl transfer status
2715 gl_posted_date, --gl transfer date
2716 'F', --acct entry status code final
2717 'STANDARD', --acct entry type code
2718 '', --description TBD
2719 '', --budget version id
2720 '', --funds status code
2721 '', --encumbrance type id
2722 '', --completed date
2723 doc_seq_id,
2724 doc_seq_val,
2725 cat_code,
2726 '', --packet id
2727 '', --group id
2728 sysdate, --row who creation date
2729 0,
2730 sysdate,
2731 0,
2732 0,
2733 sysdate,
2734 0, --program id
2735 222,
2736 '', --request id
2737 '', --AX columns start
2738 '',
2739 '',
2740 '',
2741 '',
2742 '',
2743 '' --upg valid flag
2744 --''
2745 )
2746 select /*+ use_nl(lgr,map) */
2747 l_batch_id AS BATCH_ID,
2748 'Adjustment' AS CATEGORY,
2749 ev.TRX_ID AS TRX_ID,
2750 ev.TRX_DATE AS TRX_DATE,
2751 ev.SOB_ID AS SOB_ID,
2752 ev.CAT_CODE AS CAT_CODE,
2753 ev.TRX_TYPE AS TRX_TYPE,
2754 ev.TRX_STATUS AS TRX_STATUS,
2755 ev.OVERRIDE_EVENT AS OVERRIDE_EVENT,
2756 ev.PSTD_FLG AS PSTD_FLG,
2757 ev.PST_ID AS PST_ID,
2758 ev.GL_DATE AS GL_DATE,
2759 ev.GL_POSTED_DATE AS GL_POSTED_DATE,
2760 ev.DOC_SEQ_ID AS DOC_SEQ_ID,
2761 ev.DOC_SEQ_VAL AS DOC_SEQ_VAL,
2762 ev.ENTITY_ID AS ENTITY_ID,
2763 map.PERIOD_NAME AS PERIOD_NAME,
2764 1 AS LINE_NUM
2765 FROM
2766 (select /*+ ordered rowid(adj) use_nl(ct,te) use_hash(sys,tty) use_hash(gps) swap_join_inputs(gps) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
2767 adj.adjustment_id TRX_ID ,
2768 ct.trx_date TRX_DATE ,
2769 adj.set_of_books_id SOB_ID ,
2770 'ADJ' TRX_TYPE ,
2771 decode(sys.accounting_method,
2772 'CASH', 'N',
2773 decode(adj.status,
2774 'A', decode(adj.posting_control_id,
2775 -3, 'U',
2776 'P'),
2777 'I')) TRX_STATUS ,
2778 'ADJ_CREATE' OVERRIDE_EVENT ,
2779 decode(adj.posting_control_id,
2780 -3, 'U',
2781 'P') PSTD_FLG ,
2782 adj.posting_control_id PST_ID ,
2783 nvl(trunc(adj.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
2784 nvl(trunc(max(adj.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
2785 adj.doc_sequence_id DOC_SEQ_ID ,
2786 adj.doc_sequence_value DOC_SEQ_VAL ,
2787 tty.name CAT_CODE ,
2788 te.entity_id ENTITY_ID
2789 FROM ar_adjustments_all adj,
2790 xla_upgrade_dates gps,
2791 ar_system_parameters_all sys,
2792 ra_customer_trx_all ct,
2793 ra_cust_trx_types_all tty,
2794 xla_transaction_entities_upg te
2795 WHERE adj.rowid >= l_start_rowid
2796 AND adj.rowid <= l_end_rowid
2797 AND NVL(adj.ax_accounted_flag,'N') = 'N'
2798 AND adj.customer_trx_id = ct.customer_trx_id
2799 and adj.event_id is null
2800 and trunc(adj.gl_date) between gps.start_date and gps.end_date
2801 and gps.ledger_id = adj.set_of_books_id
2802 and decode(adj.posting_control_id,
2803 -3, decode(l_action_flag,
2804 'D','P',
2805 l_action_flag),
2806 'P') = 'P'
2807 AND sys.org_id = adj.org_id
2808 AND ct.cust_trx_type_id = tty.cust_trx_type_id
2809 AND tty.org_id = ct.org_id
2810 AND te.application_id = 222
2811 AND te.ledger_id = adj.set_of_books_id
2812 AND te.entity_code = 'ADJUSTMENTS'
2813 AND nvl(te.source_id_int_1,-99) = adj.adjustment_id
2814 --AND te.upg_batch_id = l_batch_id
2815 GROUP BY adj.adjustment_id,
2816 ct.trx_date,
2817 adj.set_of_books_id,
2818 te.entity_id,
2819 decode(sys.accounting_method,
2820 'CASH', 'N',
2821 decode(adj.status,
2822 'A', decode(adj.posting_control_id,
2823 -3, 'U',
2824 'P'),
2825 'I')),
2826 adj.org_id,
2827 decode(adj.posting_control_id,
2828 -3, 'U',
2829 'P') ,
2830 adj.posting_control_id,
2831 nvl(trunc(adj.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
2832 adj.doc_sequence_id,
2833 adj.doc_sequence_value,
2834 tty.name) ev,
2835 gl_ledgers lgr,
2836 gl_date_period_map map
2837 where ev.sob_id = lgr.ledger_id
2838 and map.period_set_name = lgr.period_set_name
2839 and map.period_type = lgr.accounted_period_type
2840 and map.accounting_date = ev.gl_date;
2841 --ORDER BY TRX_ID, line_num;
2842
2843 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2844
2845 END IF; --create events
2846
2847 -------------------------------------------------------------------
2848 -- Create the Journal Entry Lines
2849 -- gl_transfer_mode_code is a flag indicating whether distributions
2850 -- from AR to subledger tables are in detail or summary. This is
2851 -- different from the standard post to GL summary or detail. So
2852 --from an upgrade perspective for AR this is in detail always
2853 --as AR stores in detailed accounting for historical data.
2854 -------------------------------------------------------------------
2855 IF NVL(l_entity_type,'L') = 'L' THEN
2856
2857 INSERT ALL
2858 WHEN 1 = 1 THEN
2859 INTO XLA_AE_LINES
2860 (upg_batch_id,
2861 ae_header_id,
2862 ae_line_num,
2863 application_id,
2864 code_combination_id,
2865 gl_transfer_mode_code,
2866 accounted_dr,
2867 accounted_cr,
2868 currency_code,
2869 currency_conversion_date,
2870 currency_conversion_rate,
2871 currency_conversion_type,
2872 entered_dr,
2873 entered_cr,
2874 description,
2875 accounting_class_code,
2876 gl_sl_link_id,
2877 gl_sl_link_table,
2878 party_type_code,
2879 party_id,
2880 party_site_id,
2881 statistical_amount,
2882 ussgl_transaction_code,
2883 jgzz_recon_ref,
2884 control_balance_flag,
2885 analytical_balance_flag,
2886 creation_date,
2887 created_by,
2888 last_update_date,
2889 last_updated_by,
2890 last_update_login,
2891 program_update_date,
2892 program_id,
2893 program_application_id,
2894 request_id,
2895 gain_or_loss_flag,
2896 accounting_date,
2897 ledger_id
2898 )
2899 VALUES
2900 ( batch_id,
2901 ae_header_id,
2902 line_num,
2903 222,
2904 code_combination_id,
2905 'D', --gl transfer mode Summary or detail
2906 acctd_amount_dr,
2907 acctd_amount_cr,
2908 currency_code,
2909 exchange_date,
2910 exchange_rate,
2911 exchange_type,
2912 amount_dr,
2913 amount_cr,
2914 '', --description TBD
2915 nvl(account_class,'XXXX'), --accounting class code
2916 xla_gl_sl_link_id_s.nextval, --gl sl link id
2917 'XLAJEL', --gl sl link table
2918 DECODE(third_party_id, NULL, NULL,'C'), --party type code
2919 third_party_id, --party id
2920 third_party_sub_id, --third party site
2921 '', --statistical amount
2922 '', --ussgl trx code
2923 '', --jgzz recon ref
2924 '', --control balance flag
2925 '', --analytical balance
2926 sysdate, --row who columns
2927 0,
2928 sysdate,
2929 0,
2930 0,
2931 sysdate,
2932 0, --program id
2933 222,
2934 '', --request id
2935 'N',
2936 accounting_date,
2937 ledger_id)
2938 WHEN 1 = 1 THEN
2939 INTO XLA_DISTRIBUTION_LINKS
2940 (APPLICATION_ID,
2941 EVENT_ID,
2942 AE_HEADER_ID,
2943 AE_LINE_NUM,
2944 ACCOUNTING_LINE_CODE,
2945 ACCOUNTING_LINE_TYPE_CODE,
2946 REF_AE_HEADER_ID,
2947 SOURCE_DISTRIBUTION_TYPE,
2948 SOURCE_DISTRIBUTION_ID_CHAR_1,
2949 SOURCE_DISTRIBUTION_ID_CHAR_2,
2950 SOURCE_DISTRIBUTION_ID_CHAR_3,
2951 SOURCE_DISTRIBUTION_ID_CHAR_4,
2952 SOURCE_DISTRIBUTION_ID_CHAR_5,
2953 SOURCE_DISTRIBUTION_ID_NUM_1,
2954 SOURCE_DISTRIBUTION_ID_NUM_2,
2955 SOURCE_DISTRIBUTION_ID_NUM_3,
2956 SOURCE_DISTRIBUTION_ID_NUM_4,
2957 SOURCE_DISTRIBUTION_ID_NUM_5,
2958 UNROUNDED_ENTERED_DR,
2959 UNROUNDED_ENTERED_CR,
2960 UNROUNDED_ACCOUNTED_DR,
2961 UNROUNDED_ACCOUNTED_CR,
2962 MERGE_DUPLICATE_CODE,
2963 TAX_LINE_REF_ID,
2964 TAX_SUMMARY_LINE_REF_ID,
2965 TAX_REC_NREC_DIST_REF_ID,
2966 STATISTICAL_AMOUNT,
2967 TEMP_LINE_NUM,
2968 EVENT_TYPE_CODE,
2969 EVENT_CLASS_CODE,
2970 REF_EVENT_ID,
2971 UPG_BATCH_ID)
2972 VALUES
2973 (222,
2974 event_id,
2975 ae_header_id,
2976 line_num,
2977 account_class,
2978 'C', --accounting line code customer
2979 ae_header_id, --reference header id
2980 source_table,
2981 '', --src dist id char
2982 '',
2983 '',
2984 '',
2985 '',
2986 line_id, --src dist id num
2987 '',
2988 '',
2989 '',
2990 '',
2991 amount_dr,
2992 amount_cr,
2993 acctd_amount_dr,
2994 acctd_amount_cr,
2995 'N', --merge dup code
2996 '', --tax_line_ref_id
2997 '', --tax_summary_line_ref_id
2998 '', --tax_rec_nrec_dist_ref_id
2999 '', --statistical amount
3000 line_num, --temp_line_num
3001 event_type_code, --event_type_code
3002 event_class_code, --event class code
3003 '', --ref_event_id,
3004 batch_id) --upgrade batch id
3005 select
3006 l_batch_id AS batch_id,
3007 ae_header_id AS ae_header_id,
3008 line_id AS line_id,
3009 event_id AS event_id,
3010 account_class AS account_class,
3011 source_table AS source_table,
3012 code_combination_id AS code_combination_id,
3013 amount_dr AS amount_dr,
3014 amount_cr AS amount_cr,
3015 acctd_amount_dr AS acctd_amount_dr,
3016 acctd_amount_cr AS acctd_amount_cr,
3017 nvl(currency_code,'XXXX') AS currency_code,
3018 third_party_id AS third_party_id,
3019 third_party_sub_id AS third_party_sub_id,
3020 exchange_date AS exchange_date,
3021 exchange_rate AS exchange_rate,
3022 exchange_type AS exchange_type,
3023 event_type_code AS event_type_code,
3024 event_class_code AS event_class_code,
3025 accounting_date AS accounting_date,
3026 ledger_id AS ledger_id,
3027 RANK() OVER (PARTITION BY event_id, ae_header_id
3028 ORDER BY line_id) AS line_num
3029 FROM
3030 ( select /*+ ordered rowid(adj) use_nl(ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
3031 hdr.ae_header_id ae_header_id,
3032 DECODE(ard.source_type,'REC','RECEIVABLE',
3033 ard.source_type) account_class,
3034 'AR_DISTRIBUTIONS_ALL' source_table,
3035 ard.code_combination_id code_combination_id,
3036 ard.amount_dr amount_dr,
3037 ard.amount_cr amount_cr,
3038 ard.acctd_amount_dr acctd_amount_dr,
3039 ard.acctd_amount_cr acctd_amount_cr,
3040 ard.currency_code currency_code,
3041 ard.third_party_id third_party_id,
3042 ard.third_party_sub_id third_party_sub_id,
3043 ard.currency_conversion_date exchange_date,
3044 ard.currency_conversion_rate exchange_rate,
3045 ard.currency_conversion_type exchange_type,
3046 ard.line_id line_id,
3047 ev.event_id event_id,
3048 ev.event_type_code event_type_code,
3049 'ADJUSTMENT' event_class_code,
3050 hdr.accounting_date accounting_date,
3051 hdr.ledger_id ledger_id,
3052 1 ln_order
3053 from ar_adjustments_all adj,
3054 xla_upgrade_dates gps,
3055 xla_transaction_entities_upg ent,
3056 xla_events ev,
3057 xla_ae_headers hdr,
3058 ar_distributions_all ard
3059 where adj.rowid >= l_start_rowid
3060 and adj.rowid <= l_end_rowid
3061 and nvl(adj.ax_accounted_flag,'N') = 'N'
3062 and trunc(adj.gl_date) between gps.start_date and gps.end_date
3063 and gps.ledger_id = adj.set_of_books_id
3064 and ent.application_id = 222
3065 and adj.set_of_books_id = ent.ledger_id
3066 and ent.entity_code = 'ADJUSTMENTS'
3067 and nvl(ent.source_id_int_1,-99) = adj.adjustment_id
3068 and ent.entity_id = ev.entity_id
3069 and ev.application_id = 222
3070 and ev.upg_batch_id = l_batch_id
3071 and adj.set_of_books_id = hdr.ledger_id
3072 and hdr.application_id = 222
3073 and hdr.event_id = ev.event_id
3074 and adj.posting_control_id = ev.reference_num_1
3075 and nvl(trunc(adj.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
3076 and ard.source_id = adj.adjustment_id
3077 and ard.source_table = 'ADJ');
3078
3079 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
3080
3081 END IF; --create lines
3082
3083 ad_parallel_updates_pkg.processed_rowid_range(
3084 l_rows_processed,
3085 l_end_rowid);
3086
3087 commit;
3088
3089 ad_parallel_updates_pkg.get_rowid_range(
3090 l_start_rowid,
3091 l_end_rowid,
3092 l_any_rows_to_process,
3093 l_batch_size,
3094 FALSE);
3095
3096 l_rows_processed := 0 ;
3097
3098 END LOOP ; /* end of WHILE loop */
3099
3100 EXCEPTION
3101 WHEN NO_DATA_FOUND THEN
3102 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.upgrade_adjustments');
3103 RAISE;
3104
3105 WHEN OTHERS THEN
3106 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.upgrade_adjustments');
3107 RAISE;
3108
3109 END UPGRADE_ADJUSTMENTS;
3110
3111 --{BUG#4748251 - Update gl_import_references gl_sl_link_id, gl_sl_link_table
3112 PROCEDURE update_gl_sla_link(
3113 l_table_owner IN VARCHAR2,
3114 l_table_name IN VARCHAR2,
3115 l_script_name IN VARCHAR2,
3116 l_worker_id IN VARCHAR2,
3117 l_num_workers IN VARCHAR2,
3118 l_batch_size IN VARCHAR2,
3119 l_batch_id IN NUMBER,
3120 l_action_flag IN VARCHAR2) IS
3121
3122 l_start_rowid rowid;
3123 l_end_rowid rowid;
3124 l_any_rows_to_process boolean;
3125 l_rows_processed number := 0;
3126
3127 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
3128 l_sl_id_tab DBMS_SQL.NUMBER_TABLE;
3129 g_bulk_fetch_rows NUMBER := 10000;
3130 l_last_fetch BOOLEAN := FALSE;
3131
3132 BEGIN
3133
3134 /* ------ Initialize the rowid ranges ------ */
3135 ad_parallel_updates_pkg.initialize_rowid_range(
3136 ad_parallel_updates_pkg.ROWID_RANGE,
3137 l_table_owner,
3138 l_table_name,
3139 l_script_name,
3140 l_worker_id,
3141 l_num_workers,
3142 l_batch_size, 0);
3143
3144 /* ------ Get rowid ranges ------ */
3145 ad_parallel_updates_pkg.get_rowid_range(
3146 l_start_rowid,
3147 l_end_rowid,
3148 l_any_rows_to_process,
3149 l_batch_size,
3150 TRUE);
3151
3152 -- Added for bug 6673937 ( pref. issue)
3153
3154 BEGIN
3155 insert into ar120gir_periods(period_name)
3156 select
3157 distinct period_name
3158 from gl_periods p
3159 where start_date >= (select min(start_date) from XLA_UPGRADE_DATES)
3160 and end_date <= (select max(end_date) from XLA_UPGRADE_DATES);
3161 EXCEPTION
3162 WHEN NO_DATA_FOUND THEN
3163 -- arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3164 -- arp_standard.debug('NO_DATA_FOUND EXCEPTION: Insert into ar120gir_periods');
3165 RAISE;
3166
3167 WHEN OTHERS THEN
3168 -- arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3169 -- arp_standard.debug('NO_DATA_FOUND EXCEPTION: Insert into ar120gir_periods');
3170 RAISE;
3171 END;
3172
3173
3174 WHILE ( l_any_rows_to_process = TRUE )
3175 LOOP
3176
3177 l_rows_processed := 0;
3178
3179 -------------------------------------------------------------------
3180 -- Create the transaction entities
3181 -- Created by arglslalink.sql
3182 -------------------------------------------------------------------
3183
3184 -- Added for bug 6673937 ( pref. issue)
3185 -- Combined the bulk select and update under single go
3186
3187 UPDATE /*+ rowid(gimp) */
3188 GL_IMPORT_REFERENCES GIMP
3189 SET
3190 (gl_sl_link_id,
3191 gl_sl_link_table) =
3192 (SELECT /*+
3193 NO_EXPAND leading(ghd,periods,gld,gps,lnk,ln)
3194 use_nl(ghd,gld,gps)
3195 USE_NL_WITH_INDEX(ln XLA_AE_LINES_U1)
3196 USE_NL_WITH_INDEX(lnk XLA_DISTRIBUTION_LINKS_N1)
3197 */
3198 LN.GL_SL_LINK_ID, 'XLAJEL'
3199 FROM GL_JE_HEADERS GHD,
3200 GL_JE_LINES GLD,
3201 XLA_UPGRADE_DATES GPS,
3202 XLA_DISTRIBUTION_LINKS LNK,
3203 XLA_AE_LINES LN
3204 WHERE EXISTS
3205 (select /*+ PUSH_SUBQ */ null
3206 from ar120gir_periods periods
3207 where periods.period_name = GHD.period_name
3208 )
3209 AND GIMP.JE_HEADER_ID = GHD.JE_HEADER_ID
3210 AND GHD.JE_SOURCE = 'Receivables'
3211 AND GHD.JE_CATEGORY IN ('Adjustment','Chargebacks','Credit Memo Applications',
3212 'Credit Memos','Debit Memos','Misc Receipts',
3213 'Rate Adjustments', 'Sales Invoices','Trade Receipts',
3214 'Cross Currency', 'Bills Receivable')
3215 AND GHD.JE_HEADER_ID = GLD.JE_HEADER_ID
3216 AND GLD.EFFECTIVE_DATE BETWEEN GPS.START_DATE AND GPS.END_DATE
3217 AND GLD.LEDGER_ID = GPS.LEDGER_ID
3218 AND GLD.JE_HEADER_ID = GIMP.JE_HEADER_ID
3219 AND GLD.JE_LINE_NUM = GIMP.JE_LINE_NUM
3220 AND LNK.APPLICATION_ID = 222
3221 AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = GIMP.REFERENCE_3
3222 AND LNK.SOURCE_DISTRIBUTION_TYPE =
3223 (CASE WHEN GIMP.REFERENCE_10 = 'RA_CUST_TRX_LINE_GL_DIST'
3224 THEN 'RA_CUST_TRX_LINE_GL_DIST_ALL'
3225 WHEN GIMP.REFERENCE_10 IN
3226 ('AR_TRANSACTION_HISTORY','AR_ADJUSTMENTS',
3227 'AR_MISC_CASH_DISTRIBUTIONS',
3228 'AR_RECEIVABLE_APPLICATIONS', 'AR_CASH_RECEIPT_HISTORY')
3229 THEN 'AR_DISTRIBUTIONS_ALL'
3230 ELSE NULL
3231 END )
3232 AND LN.APPLICATION_ID = 222
3233 AND LNK.AE_HEADER_ID = LN.AE_HEADER_ID
3234 AND LNK.AE_LINE_NUM = LN.AE_LINE_NUM
3235 )
3236 WHERE ROWID BETWEEN l_start_rowid and l_end_rowid
3237 AND GIMP.REFERENCE_10 IN
3238 ('AR_TRANSACTION_HISTORY','AR_ADJUSTMENTS','AR_MISC_CASH_DISTRIBUTIONS',
3239 'AR_RECEIVABLE_APPLICATIONS','AR_CASH_RECEIPT_HISTORY','RA_CUST_TRX_LINE_GL_DIST')
3240 AND GIMP.GL_SL_LINK_ID IS NULL;
3241
3242
3243 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
3244
3245 ad_parallel_updates_pkg.processed_rowid_range(
3246 l_rows_processed,
3247 l_end_rowid);
3248
3249 commit;
3250
3251 ad_parallel_updates_pkg.get_rowid_range(
3252 l_start_rowid,
3253 l_end_rowid,
3254 l_any_rows_to_process,
3255 l_batch_size,
3256 FALSE);
3257
3258 l_rows_processed := 0 ;
3259
3260 END LOOP ; /* end of WHILE loop */
3261
3262 EXCEPTION
3263 WHEN NO_DATA_FOUND THEN
3264 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3265 RAISE;
3266
3267 WHEN OTHERS THEN
3268 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3269 RAISE;
3270
3271 END update_gl_sla_link;
3272 --}
3273
3274
3275 END ARP_XLA_UPGRADE;