[Home] [Help]
PACKAGE BODY: APPS.ARP_XLA_UPGRADE
Source
1 PACKAGE BODY ARP_XLA_UPGRADE AS
2 /* $Header: ARXLAUPB.pls 120.41.12020000.3 2013/03/03 17:20:10 naneja 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,'XXX') 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,'XXX') 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,'XXX') 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) + 5000 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' or ( sys.accounting_method = 'CASH' and app.status in ('UNAPP', 'UNID') ) ) /* to work for cash basis accounting as well */
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,'XXX') 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 l_accounting_method varchar2(10) := 'ACCRUAL';
3127
3128 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
3129 l_sl_id_tab DBMS_SQL.NUMBER_TABLE;
3130 g_bulk_fetch_rows NUMBER := 10000;
3131 l_last_fetch BOOLEAN := FALSE;
3132
3133 BEGIN
3134
3135 /* ------ Initialize the rowid ranges ------ */
3136 ad_parallel_updates_pkg.initialize_rowid_range(
3137 ad_parallel_updates_pkg.ROWID_RANGE,
3138 l_table_owner,
3139 l_table_name,
3140 l_script_name,
3141 l_worker_id,
3142 l_num_workers,
3143 l_batch_size, 0);
3144
3145 /* ------ Get rowid ranges ------ */
3146 ad_parallel_updates_pkg.get_rowid_range(
3147 l_start_rowid,
3148 l_end_rowid,
3149 l_any_rows_to_process,
3150 l_batch_size,
3151 TRUE);
3152
3153 -- Added for bug 6673937 ( pref. issue)
3154
3155 BEGIN
3156 insert into ar120gir_periods(period_name)
3157 select
3158 distinct period_name
3159 from gl_periods p
3160 where start_date >= (select min(start_date) from XLA_UPGRADE_DATES)
3161 and end_date <= (select max(end_date) from XLA_UPGRADE_DATES);
3162 EXCEPTION
3163 WHEN NO_DATA_FOUND THEN
3164 -- arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3165 -- arp_standard.debug('NO_DATA_FOUND EXCEPTION: Insert into ar120gir_periods');
3166 RAISE;
3167
3168 WHEN OTHERS THEN
3169 -- arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3170 -- arp_standard.debug('NO_DATA_FOUND EXCEPTION: Insert into ar120gir_periods');
3171 RAISE;
3172 END;
3173
3174
3175 WHILE ( l_any_rows_to_process = TRUE )
3176 LOOP
3177
3178 l_rows_processed := 0;
3179
3180 -------------------------------------------------------------------
3181 -- Create the transaction entities
3182 -- Created by arglslalink.sql
3183 -------------------------------------------------------------------
3184
3185
3186 -- bug 13626520 : Update gl_sl_link_id for cash basis accounting >>>
3187 -- This will update gl_sl_link_id for cash basis accounting of receipt.
3188 -- Added 3 update statements for CRH, RA, CBD
3189 -- Needed this due to GL_IMPORT_REFERENCES reference columns data model for
3190 -- cash basis accounting. This part should run only if any upgraded ledger_id
3191 -- has cash basis accounting method
3192
3193 begin
3194
3195 select distinct 'CASH'
3196 INTO l_accounting_method
3197 FROM dual
3198 WHERE exists (SELECT 1
3199 FROM ar_system_parameters_all
3200 WHERE accounting_method = 'CASH'
3201 and set_of_books_id in (select ledger_id from xla_upgrade_dates)
3202 );
3203
3204
3205 exception
3206 when no_data_found then
3207 l_accounting_method := 'ACCRUAL';
3208 when others then
3209 l_accounting_method := 'ACCRUAL';
3210 END;
3211
3212 -- Update gl_sl_link_id for AR_CASH_RECEIPT_HISTORY_ALL data
3213
3214 If (l_accounting_method = 'CASH') Then
3215
3216 update /*+ rowid(gir) */ gl_import_references gir
3217 set gir.gl_sl_link_id = (select min(xal.gl_sl_link_id)
3218 from xla_ae_lines xal,
3219 xla_ae_headers xah,
3220 xla_transaction_entities_upg xte,
3221 ar_cash_receipts_all cr,
3222 ar_system_parameters_all asp,
3223 xla_distribution_links xdl,
3224 ar_cash_receipt_history_all crh,
3225 xla_upgrade_dates gps,
3226 ar_distributions_all ard,
3227 gl_je_lines gjl
3228 where xte.source_id_int_1 = cr.cash_receipt_id
3229 AND xah.accounting_date BETWEEN GPS.START_DATE AND GPS.END_DATE
3230 and xte.upg_batch_id is not null
3231 and xte.ledger_id = cr.set_of_books_id
3232 and xte.upg_batch_id is not null
3233 and xte.application_id = 222
3234 and xte.ledger_id = xah.ledger_id
3235 and xte.application_id = xah.application_id
3236 and xte.entity_id = xah.entity_id
3237 and xah.ae_header_id = xal.ae_header_id
3238 and xah.application_id = xal.application_id
3239 and xal.ae_header_id = xdl.ae_header_id
3240 and xal.ae_line_num = xdl.ae_line_num
3241 and xal.application_id = xdl.application_id
3242 and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3243 and asp.set_of_books_id = cr.set_of_books_id
3244 and asp.accounting_method = 'CASH'
3245 and cr.cash_receipt_id = crh.cash_receipt_id
3246 and ard.source_id = crh.cash_receipt_history_id
3247 and ard.source_table = 'CRH'
3248 and ard.line_id = xdl.source_distribution_id_num_1
3249 and gjl.je_header_id = gir.je_header_id
3250 and gjl.je_line_num = gir.je_line_num
3251 and gjl.reference_2 = gir.reference_2
3252 and gjl.reference_3 = gir.reference_3
3253 and gjl.code_combination_id = ard.code_combination_id
3254 and cr.cash_receipt_id = to_number(decode(rtrim(translate(gir.reference_2,'0123456789',' ')), null, gir.reference_2, -99999))
3255 and crh.cash_receipt_history_id = to_number(decode(rtrim(translate(gir.reference_3,'0123456789',' ')), null, gir.reference_3, -99999))
3256 /* and to_char(cr.cash_receipt_id) = gir.reference_2
3257 and to_char(crh.cash_receipt_history_id) = gir.reference_3 */
3258 ),
3259 gir.gl_sl_link_table = 'XLAJEL',
3260 gir.last_update_date = sysdate
3261 where rowid between l_start_rowid and l_end_rowid
3262 and gir.reference_10 = 'AR_CASH_RECEIPT_HISTORY'
3263 and gir.gl_sl_link_id is null;
3264
3265 -- Update gl_sl_link_id for AR_RECEIVABLE_APPLICATIONS_ALL UNAPP data
3266
3267 update /*+ rowid(gir) */ gl_import_references gir
3268 set gir.gl_sl_link_id = (select min(xal.gl_sl_link_id)
3269 from xla_ae_lines xal,
3270 xla_ae_headers xah,
3271 xla_transaction_entities_upg xte,
3272 ar_cash_receipts_all cr,
3273 ar_system_parameters_all asp,
3274 xla_distribution_links xdl,
3275 ar_receivable_applications_all unapp,
3276 xla_upgrade_dates gps,
3277 ar_distributions_all ard,
3278 gl_je_lines gjl
3279 where xte.source_id_int_1 = cr.cash_receipt_id
3280 AND xah.accounting_date BETWEEN GPS.START_DATE AND GPS.END_DATE
3281 and xte.upg_batch_id is not null
3282 and xte.ledger_id = cr.set_of_books_id
3283 and xte.upg_batch_id is not null
3284 and xte.application_id = 222
3285 and xte.ledger_id = xah.ledger_id
3286 and xte.application_id = xah.application_id
3287 and xte.entity_id = xah.entity_id
3288 and xah.ae_header_id = xal.ae_header_id
3289 and xah.application_id = xal.application_id
3290 and xal.ae_header_id = xdl.ae_header_id
3291 and xal.ae_line_num = xdl.ae_line_num
3292 and xal.application_id = xdl.application_id
3293 and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3294 and asp.set_of_books_id = cr.set_of_books_id
3295 and asp.accounting_method = 'CASH'
3296 and cr.cash_receipt_id = unapp.cash_receipt_id
3297 and ard.source_id = unapp.receivable_application_id
3298 and ard.source_table = 'RA'
3299 and unapp.status = 'UNAPP'
3300 and ard.line_id = xdl.source_distribution_id_num_1
3301 and gjl.je_header_id = gir.je_header_id
3302 and gjl.je_line_num = gir.je_line_num
3303 and gjl.reference_2 = gir.reference_2
3304 and gjl.reference_3 = gir.reference_3
3305 and gjl.code_combination_id = ard.code_combination_id
3306 and cr.cash_receipt_id = to_number(decode(rtrim(translate(gir.reference_2,'0123456789',' ')), null, gir.reference_2, -99999))
3307 and unapp.receivable_application_id = to_number(decode(rtrim(translate(gir.reference_3,'0123456789',' ')), null, gir.reference_3, -99999))
3308 /* and to_char(cr.cash_receipt_id) = gir.reference_2
3309 and to_char(unapp.receivable_application_id) = gir.reference_3 */
3310 ),
3311 gir.gl_sl_link_table = 'XLAJEL',
3312 gir.last_update_date = sysdate
3313 where rowid between l_start_rowid and l_end_rowid
3314 and gir.reference_10 = 'AR_RECEIVABLE_APPLICATIONS'
3315 and gir.gl_sl_link_id is null;
3316
3317 -- Update gl_sl_link_id for AR_CASH_BASIS_DISTS_ALL data
3318
3319 UPDATE /*+ rowid(gimp) */
3320 GL_IMPORT_REFERENCES GIMP
3321 SET
3322 (gl_sl_link_id,
3323 gl_sl_link_table) =
3324 (SELECT /*+
3325 NO_EXPAND leading(ghd,periods,gld,gps,lnk,ln)
3326 use_nl(ghd,gld,gps)
3327 USE_NL_WITH_INDEX(ln XLA_AE_LINES_U1)
3328 USE_NL_WITH_INDEX(lnk XLA_DISTRIBUTION_LINKS_N1)
3329 */
3330 LN.GL_SL_LINK_ID, 'XLAJEL'
3331 FROM GL_JE_HEADERS GHD,
3332 GL_JE_LINES GLD,
3333 XLA_UPGRADE_DATES GPS,
3334 XLA_DISTRIBUTION_LINKS LNK,
3335 XLA_AE_LINES LN
3336 WHERE EXISTS
3337 (select /*+ PUSH_SUBQ */ null
3338 from ar120gir_periods periods
3339 where periods.period_name = GHD.period_name
3340 )
3341 AND GIMP.JE_HEADER_ID = GHD.JE_HEADER_ID
3342 AND GHD.JE_SOURCE = 'Receivables'
3343 AND GHD.JE_CATEGORY IN ('Adjustment','Chargebacks',
3344 'Credit Memo Applications',
3345 'Credit Memos','Debit Memos','Misc Receipts',
3346 'Rate Adjustments', 'Sales Invoices',
3347 'Trade Receipts',
3348 'Cross Currency', 'Bills Receivable')
3349 AND GHD.JE_HEADER_ID = GLD.JE_HEADER_ID
3350 AND GLD.EFFECTIVE_DATE BETWEEN GPS.START_DATE AND GPS.END_DATE
3351 AND GLD.LEDGER_ID = GPS.LEDGER_ID
3352 AND GLD.JE_HEADER_ID = GIMP.JE_HEADER_ID
3353 AND GLD.JE_LINE_NUM = GIMP.JE_LINE_NUM
3354 AND LNK.APPLICATION_ID = 222
3355 AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = GIMP.REFERENCE_3
3356 AND LNK.SOURCE_DISTRIBUTION_TYPE = 'AR_CASH_BASIS_DISTRIBUTIONS'
3357 AND LN.APPLICATION_ID = 222
3358 AND LNK.AE_HEADER_ID = LN.AE_HEADER_ID
3359 AND LNK.AE_LINE_NUM = LN.AE_LINE_NUM
3360 AND LN.LEDGER_ID = GLD.LEDGER_ID
3361 )
3362 WHERE ROWID BETWEEN l_start_rowid and l_end_rowid
3363 AND GIMP.REFERENCE_10 = 'AR_CASH_BASIS_DISTRIBUTIONS'
3364 AND GIMP.GL_SL_LINK_ID IS NULL;
3365
3366 End If;
3367
3368 -- bug 13626520 end <<<
3369
3370 -- Added for bug 6673937 ( pref. issue)
3371 -- Combined the bulk select and update under single go
3372
3373 UPDATE /*+ rowid(gimp) */
3374 GL_IMPORT_REFERENCES GIMP
3375 SET
3376 (gl_sl_link_id,
3377 gl_sl_link_table) =
3378 (SELECT /*+
3379 NO_EXPAND leading(ghd,periods,gld,gps,lnk,ln)
3380 use_nl(ghd,gld,gps)
3381 USE_NL_WITH_INDEX(ln XLA_AE_LINES_U1)
3382 USE_NL_WITH_INDEX(lnk XLA_DISTRIBUTION_LINKS_N1)
3383 */
3384 LN.GL_SL_LINK_ID, 'XLAJEL'
3385 FROM GL_JE_HEADERS GHD,
3386 GL_JE_LINES GLD,
3387 XLA_UPGRADE_DATES GPS,
3388 XLA_DISTRIBUTION_LINKS LNK,
3389 XLA_AE_LINES LN
3390 WHERE EXISTS
3391 (select /*+ PUSH_SUBQ */ null
3392 from ar120gir_periods periods
3393 where periods.period_name = GHD.period_name
3394 )
3395 AND GIMP.JE_HEADER_ID = GHD.JE_HEADER_ID
3396 AND GHD.JE_SOURCE = 'Receivables'
3397 AND GHD.JE_CATEGORY IN ('Adjustment','Chargebacks','Credit Memo Applications',
3398 'Credit Memos','Debit Memos','Misc Receipts',
3399 'Rate Adjustments', 'Sales Invoices','Trade Receipts',
3400 'Cross Currency', 'Bills Receivable')
3401 AND GHD.JE_HEADER_ID = GLD.JE_HEADER_ID
3402 AND GLD.EFFECTIVE_DATE BETWEEN GPS.START_DATE AND GPS.END_DATE
3403 AND GLD.LEDGER_ID = GPS.LEDGER_ID
3404 AND GLD.JE_HEADER_ID = GIMP.JE_HEADER_ID
3405 AND GLD.JE_LINE_NUM = GIMP.JE_LINE_NUM
3406 AND LNK.APPLICATION_ID = 222
3407 AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = GIMP.REFERENCE_3
3408 AND LNK.SOURCE_DISTRIBUTION_TYPE =
3409 (CASE WHEN GIMP.REFERENCE_10 = 'RA_CUST_TRX_LINE_GL_DIST'
3410 THEN 'RA_CUST_TRX_LINE_GL_DIST_ALL'
3411 WHEN GIMP.REFERENCE_10 IN
3412 ('AR_TRANSACTION_HISTORY','AR_ADJUSTMENTS',
3413 'AR_MISC_CASH_DISTRIBUTIONS',
3414 'AR_RECEIVABLE_APPLICATIONS', 'AR_CASH_RECEIPT_HISTORY')
3415 THEN 'AR_DISTRIBUTIONS_ALL'
3416 ELSE NULL
3417 END )
3418 AND LN.APPLICATION_ID = 222
3419 AND LNK.AE_HEADER_ID = LN.AE_HEADER_ID
3420 AND LNK.AE_LINE_NUM = LN.AE_LINE_NUM
3421 AND LN.LEDGER_ID = GLD.LEDGER_ID -- bug 8351855
3422 )
3423 WHERE ROWID BETWEEN l_start_rowid and l_end_rowid
3424 AND GIMP.REFERENCE_10 IN
3425 ('AR_TRANSACTION_HISTORY','AR_ADJUSTMENTS','AR_MISC_CASH_DISTRIBUTIONS',
3426 'AR_RECEIVABLE_APPLICATIONS','AR_CASH_RECEIPT_HISTORY','RA_CUST_TRX_LINE_GL_DIST')
3427 AND GIMP.GL_SL_LINK_ID IS NULL;
3428
3429
3430 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
3431
3432 ad_parallel_updates_pkg.processed_rowid_range(
3433 l_rows_processed,
3434 l_end_rowid);
3435
3436 commit;
3437
3438 ad_parallel_updates_pkg.get_rowid_range(
3439 l_start_rowid,
3440 l_end_rowid,
3441 l_any_rows_to_process,
3442 l_batch_size,
3443 FALSE);
3444
3445 l_rows_processed := 0 ;
3446
3447 END LOOP ; /* end of WHILE loop */
3448
3449 EXCEPTION
3450 WHEN NO_DATA_FOUND THEN
3451 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3452 RAISE;
3453
3454 WHEN OTHERS THEN
3455 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.update_gl_sla_link');
3456 RAISE;
3457
3458 END update_gl_sla_link;
3459 --}
3460
3461 PROCEDURE UPGRADE_CASH_DIST(
3462 l_table_owner IN VARCHAR2,
3463 l_table_name IN VARCHAR2,
3464 l_script_name IN VARCHAR2,
3465 l_worker_id IN VARCHAR2,
3466 l_num_workers IN VARCHAR2,
3467 l_batch_size IN VARCHAR2,
3468 l_batch_id IN NUMBER,
3469 l_action_flag IN VARCHAR2,
3470 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
3471
3472 l_start_rowid rowid;
3473 l_end_rowid rowid;
3474 l_any_rows_to_process boolean;
3475 l_rows_processed number := 0;
3476
3477 BEGIN
3478
3479 /* ------ Initialize the rowid ranges ------ */
3480 ad_parallel_updates_pkg.initialize_rowid_range(
3481 ad_parallel_updates_pkg.ROWID_RANGE,
3482 l_table_owner,
3483 l_table_name,
3484 l_script_name,
3485 l_worker_id,
3486 l_num_workers,
3487 l_batch_size, 0);
3488
3489 /* ------ Get rowid ranges ------ */
3490 ad_parallel_updates_pkg.get_rowid_range(
3491 l_start_rowid,
3492 l_end_rowid,
3493 l_any_rows_to_process,
3494 l_batch_size,
3495 TRUE);
3496
3497 WHILE ( l_any_rows_to_process = TRUE )
3498 LOOP
3499
3500 l_rows_processed := 0;
3501
3502 -------------------------------------------------------------------
3503 -- Create the Journal Entry Lines
3504 -- gl_transfer_mode_code is a flag indicating whether distributions
3505 -- from AR to subledger tables are in detail or summary. This is
3506 -- different from the standard post to GL summary or detail. So
3507 --from an upgrade perspective for AR this is in detail always
3508 --as AR stores in detailed accounting for historical data.
3509 -------------------------------------------------------------------
3510 INSERT ALL
3511 WHEN 1 = 1 THEN
3512 INTO XLA_AE_LINES
3513 (upg_batch_id,
3514 ae_header_id,
3515 ae_line_num,
3516 application_id,
3517 code_combination_id,
3518 gl_transfer_mode_code,
3519 accounted_dr,
3520 accounted_cr,
3521 currency_code,
3522 currency_conversion_date,
3523 currency_conversion_rate,
3524 currency_conversion_type,
3525 entered_dr,
3526 entered_cr,
3527 description,
3528 accounting_class_code,
3529 gl_sl_link_id,
3530 gl_sl_link_table,
3531 party_type_code,
3532 party_id,
3533 party_site_id,
3534 statistical_amount,
3535 ussgl_transaction_code,
3536 jgzz_recon_ref,
3537 control_balance_flag,
3538 analytical_balance_flag,
3539 creation_date,
3540 created_by,
3541 last_update_date,
3542 last_updated_by,
3543 last_update_login,
3544 program_update_date,
3545 program_id,
3546 program_application_id,
3547 request_id,
3548 gain_or_loss_flag,
3549 accounting_date,
3550 ledger_id
3551 )
3552 VALUES
3553 ( batch_id,
3554 ae_header_id,
3555 line_num,
3556 222,
3557 code_combination_id,
3558 'D', --gl transfer mode Summary or detail
3559 acctd_amount_dr,
3560 acctd_amount_cr,
3561 currency_code,
3562 exchange_date,
3563 exchange_rate,
3564 exchange_type,
3565 amount_dr,
3566 amount_cr,
3567 '', --description TBD
3568 account_class, --accounting class code
3569 xla_gl_sl_link_id_s.nextval, --gl sl link id
3570 'XLAJEL', --gl sl link table
3571 DECODE(third_party_id, NULL, NULL,'C'), --party type code
3572 third_party_id, --party id
3573 third_party_sub_id, --third party site
3574 '', --statistical amount
3575 '', --ussgl trx code
3576 '', --jgzz recon ref
3577 '', --control balance flag
3578 '', --analytical balance
3579 sysdate, --row who columns
3580 0,
3581 sysdate,
3582 0,
3583 0,
3584 sysdate,
3585 0, --program id
3586 222,
3587 '', --request id
3588 'N',
3589 accounting_date,
3590 ledger_id)
3591 WHEN 1 = 1 THEN
3592 INTO XLA_DISTRIBUTION_LINKS
3593 (APPLICATION_ID,
3594 EVENT_ID,
3595 AE_HEADER_ID,
3596 AE_LINE_NUM,
3597 ACCOUNTING_LINE_CODE,
3598 ACCOUNTING_LINE_TYPE_CODE,
3599 REF_AE_HEADER_ID,
3600 SOURCE_DISTRIBUTION_TYPE,
3601 SOURCE_DISTRIBUTION_ID_CHAR_1,
3602 SOURCE_DISTRIBUTION_ID_CHAR_2,
3603 SOURCE_DISTRIBUTION_ID_CHAR_3,
3604 SOURCE_DISTRIBUTION_ID_CHAR_4,
3605 SOURCE_DISTRIBUTION_ID_CHAR_5,
3606 SOURCE_DISTRIBUTION_ID_NUM_1,
3607 SOURCE_DISTRIBUTION_ID_NUM_2,
3608 SOURCE_DISTRIBUTION_ID_NUM_3,
3609 SOURCE_DISTRIBUTION_ID_NUM_4,
3610 SOURCE_DISTRIBUTION_ID_NUM_5,
3611 UNROUNDED_ENTERED_DR,
3612 UNROUNDED_ENTERED_CR,
3613 UNROUNDED_ACCOUNTED_DR,
3614 UNROUNDED_ACCOUNTED_CR,
3615 MERGE_DUPLICATE_CODE,
3616 TAX_LINE_REF_ID,
3617 TAX_SUMMARY_LINE_REF_ID,
3618 TAX_REC_NREC_DIST_REF_ID,
3619 STATISTICAL_AMOUNT,
3620 TEMP_LINE_NUM,
3621 EVENT_TYPE_CODE,
3622 EVENT_CLASS_CODE,
3623 REF_EVENT_ID,
3624 UPG_BATCH_ID)
3625 VALUES
3626 (222,
3627 event_id,
3628 ae_header_id,
3629 line_num,
3630 account_class,
3631 'C', --accounting line code customer
3632 ae_header_id, --reference header id
3633 source_table,
3634 '', --src dist id char
3635 '',
3636 '',
3637 '',
3638 '',
3639 line_id, --src dist id num
3640 '',
3641 '',
3642 '',
3643 '',
3644 amount_dr,
3645 amount_cr,
3646 acctd_amount_dr,
3647 acctd_amount_cr,
3648 'N', --merge dup code
3649 tax_line_id, --tax_line_ref_id
3650 '', --tax_summary_line_ref_id
3651 '', --tax_rec_nrec_dist_ref_id
3652 '', --statistical amount
3653 line_num, --temp_line_num
3654 event_type_code, --event type
3655 'RECEIPT', --event class code
3656 '', --ref_event_id,
3657 batch_id) --upgrade batch id
3658 select
3659 l_batch_id AS batch_id,
3660 ae_header_id AS ae_header_id,
3661 line_id AS line_id,
3662 event_id AS event_id,
3663 event_type_code AS event_type_code,
3664 account_class AS account_class,
3665 source_table AS source_table,
3666 code_combination_id AS code_combination_id,
3667 amount_dr AS amount_dr,
3668 amount_cr AS amount_cr,
3669 acctd_amount_dr AS acctd_amount_dr,
3670 acctd_amount_cr AS acctd_amount_cr,
3671 nvl(currency_code,'XXXX') AS currency_code,
3672 third_party_id AS third_party_id,
3673 third_party_sub_id AS third_party_sub_id,
3674 exchange_date AS exchange_date,
3675 exchange_rate AS exchange_rate,
3676 exchange_type AS exchange_type,
3677 tax_line_id AS tax_line_id,
3678 accounting_date AS accounting_date,
3679 ledger_id AS ledger_id,
3680 RANK() OVER (PARTITION BY event_id, ae_header_id
3681 ORDER BY line_id, ln_order) + max_line_num AS line_num
3682 FROM
3683 ( /* On Account CM and receipt applications */
3684 select /*+ ordered rowid(app) use_nl(ctcm,crh1,crh,cr,ent,ev,hdr,cbs,ctlgd) 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) */
3685 hdr.ae_header_id ae_header_id,
3686 decode(cbs.source,
3687 'GL', ctlgd.account_class,
3688 'ADJ', 'ADJ',
3689 'UNA', 'UNA',
3690 cbs.type) account_class,
3691 'AR_CASH_BASIS_DISTRIBUTIONS' source_table,
3692 cbs.code_combination_id code_combination_id,
3693 decode(sign(cbs.amount),
3694 -1, abs(cbs.amount),
3695 '') amount_dr,
3696 decode(sign(cbs.amount),
3697 1,abs(cbs.amount),
3698 0,abs(cbs.amount),
3699 '') amount_cr,
3700 decode(sign(cbs.acctd_amount),
3701 -1, abs(cbs.acctd_amount),
3702 '') acctd_amount_dr,
3703 decode(sign(cbs.acctd_amount),
3704 1,abs(cbs.acctd_amount),
3705 0,abs(cbs.acctd_amount),
3706 '') acctd_amount_cr,
3707 cbs.currency_code currency_code,
3708 decode(app.application_type,
3709 'CM', ctcm.bill_to_customer_id,
3710 cr.pay_from_customer) third_party_id,
3711 decode(app.application_type,
3712 'CM', ctcm.bill_to_site_use_id,
3713 cr.customer_site_use_id) third_party_sub_id,
3714 decode(app.application_type,
3715 'CM', ctcm.exchange_date,
3716 crh.exchange_date) exchange_date,
3717 decode(app.application_type,
3718 'CM', NVL(ctcm.exchange_rate,1),
3719 NVL(crh.exchange_rate,1) *
3720 NVL(app.trans_to_receipt_rate, 1)) exchange_rate,
3721 decode(app.application_type,
3722 'CM', NVL(ctcm.exchange_rate_type,1),
3723 NVL(crh.exchange_rate_type,1)) exchange_type,
3724 cbs.cash_basis_distribution_id line_id,
3725 ev.event_id event_id,
3726 ev.event_type_code event_type_code,
3727 null tax_line_id,
3728 hdr.accounting_date accounting_date,
3729 hdr.ledger_id AS ledger_id,
3730 1 ln_order,
3731 nvl((select max(ae_line_num) from xla_ae_lines ael
3732 where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
3733 from ar_receivable_applications_all app,
3734 xla_upgrade_dates gps,
3735 xla_transaction_entities_upg ent,
3736 ra_customer_trx_all ctcm,
3737 ar_cash_receipt_history_all crh1,
3738 ar_cash_receipt_history_all crh,
3739 ar_cash_receipts_all cr,
3740 xla_events ev,
3741 xla_ae_headers hdr,
3742 ar_cash_basis_dists_all cbs,
3743 ra_cust_trx_line_gl_dist_all ctlgd
3744 where app.rowid >= l_start_rowid
3745 and app.rowid <= l_end_rowid
3746 and nvl(app.postable,'Y') = 'Y'
3747 and nvl(app.confirmed_flag,'Y') = 'Y'
3748 and app.status = 'APP'
3749 and trunc(app.gl_date) between gps.start_date and gps.end_date
3750 and gps.ledger_id = app.set_of_books_id
3751 and app.customer_trx_id = ctcm.customer_trx_id (+)
3752 and app.cash_receipt_id = cr.cash_receipt_id (+)
3753 and app.cash_receipt_id = crh1.cash_receipt_id (+)
3754 and 'Y' = crh1.first_posted_record_flag (+)
3755 and app.cash_receipt_history_id = crh.cash_receipt_history_id (+)
3756 AND app.posting_control_id <> -3
3757 and ent.application_id = 222
3758 and ent.ledger_id = app.set_of_books_id
3759 and ent.entity_code = decode(app.customer_trx_id,
3760 '', 'RECEIPTS',
3761 'TRANSACTIONS')
3762 and nvl(ent.source_id_int_1,-99) = nvl(app.customer_trx_id, app.cash_receipt_id)
3763 and ent.entity_id = ev.entity_id
3764 and ev.upg_batch_id = l_batch_id
3765 and hdr.application_id = 222
3766 and app.set_of_books_id = hdr.ledger_id
3767 and hdr.event_id = ev.event_id
3768 and app.posting_control_id = ev.reference_num_1
3769 and nvl(trunc(app.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
3770 and decode(app.customer_trx_id,
3771 '', decode(crh.created_from,
3772 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
3773 decode(crh.status,
3774 'REVERSED','RECP_REVERSE',
3775 decode(crh1.first_posted_record_flag,
3776 '', 'RECP_CREATE',
3777 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
3778 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
3779 decode(app.posting_control_id,
3780 crh1.posting_control_id, 'RECP_CREATE',
3781 'RECP_UPDATE'),
3782 'RECP_UPDATE')))),
3783 ev.event_type_code) = ev.event_type_code
3784 and decode(app.customer_trx_id,
3785 '', decode(crh.postable_flag, 'Y','Y',
3786 decode(crh.status, 'APPROVED',
3787 decode(crh1.first_posted_record_flag, '','Y',
3788 'N'),
3789 'N')),
3790 decode(ctcm.previous_customer_trx_id,
3791 '','Y',
3792 'N')) = 'Y'
3793 and cbs.receivable_application_id = app.receivable_application_id
3794 and cbs.source_id = ctlgd.cust_trx_line_gl_dist_id (+));
3795 --order by entity_id, ae_header_id, line_num;
3796
3797 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
3798
3799 ad_parallel_updates_pkg.processed_rowid_range(
3800 l_rows_processed,
3801 l_end_rowid);
3802
3803 commit;
3804
3805 ad_parallel_updates_pkg.get_rowid_range(
3806 l_start_rowid,
3807 l_end_rowid,
3808 l_any_rows_to_process,
3809 l_batch_size,
3810 FALSE);
3811
3812 l_rows_processed := 0 ;
3813
3814 END LOOP ; /* end of WHILE loop */
3815
3816 EXCEPTION
3817 WHEN NO_DATA_FOUND THEN
3818 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_XLA_UPGRADE.upgrade_cash_dist');
3819 RAISE;
3820
3821 WHEN OTHERS THEN
3822 --arp_standard.debug('OTHERS EXCEPTION: ARP_XLA_UPGRADE.upgrade_cash_dist');
3823 RAISE;
3824
3825 END UPGRADE_CASH_DIST;
3826
3827
3828 /* Changes for BUG 15921888 */
3829 PROCEDURE UPGRADE_CASH_SINGLE(
3830 p_cash_receipt_id IN NUMBER,
3831 p_action_flag IN Varchar2) IS
3832 BEGIN
3833
3834 INSERT ALL
3835 WHEN 1 = 1 THEN
3836 INTO XLA_EVENTS
3837 (upg_batch_id,
3838 upg_source_application_id,
3839 application_id,
3840 reference_num_1,
3841 reference_num_2,
3842 event_type_code,
3843 event_number,
3844 event_status_code,
3845 process_status_code,
3846 on_hold_flag,
3847 event_date,
3848 creation_date,
3849 created_by,
3850 last_update_date,
3851 last_updated_by,
3852 last_update_login,
3853 program_update_date,
3854 program_id,
3855 program_application_id,
3856 request_id,
3857 entity_id,
3858 event_id,
3859 upg_valid_flag,
3860 transaction_date)
3861 VALUES
3862 (batch_id,
3863 222,
3864 222,
3865 pst_id, --reference num 1
3866 trx_id, --reference num 2
3867 override_event, --event type
3868 line_num,
3869 trx_status, --event status code I, U, N, P
3870 pstd_flg, --process status
3871 'N',
3872 gl_date, --event date
3873 sysdate,
3874 0,
3875 sysdate,
3876 0,
3877 0,
3878 sysdate,
3879 0,
3880 222,
3881 '',
3882 entity_id,
3883 xla_events_s.nextval,
3884 'Y', --upgrade flag
3885 trx_date
3886 )
3887 WHEN PST_ID <> -3 THEN
3888 INTO XLA_AE_HEADERS
3889 (upg_batch_id,
3890 upg_source_application_id,
3891 application_id,
3892 amb_context_code,
3893 entity_id,
3894 event_id,
3895 event_type_code,
3896 ae_header_id,
3897 ledger_id,
3898 accounting_date,
3899 period_name,
3900 reference_date,
3901 balance_type_code,
3902 je_category_name,
3903 gl_transfer_status_code,
3904 gl_transfer_date,
3905 accounting_entry_status_code,
3906 accounting_entry_type_code,
3907 description,
3908 budget_version_id,
3909 funds_status_code,
3910 encumbrance_type_id,
3911 completed_date,
3912 doc_sequence_id,
3913 doc_sequence_value,
3914 doc_category_code,
3915 packet_id,
3916 group_id,
3917 creation_date,
3918 created_by,
3919 last_update_date,
3920 last_updated_by,
3921 last_update_login,
3922 program_update_date,
3923 program_id,
3924 program_application_id,
3925 request_id,
3926 close_acct_seq_assign_id,
3927 close_acct_seq_version_id,
3928 close_acct_seq_value,
3929 completion_acct_seq_assign_id,
3930 completion_acct_seq_version_id,
3931 completion_acct_seq_value,
3932 upg_valid_flag
3933 )
3934 VALUES
3935 (batch_id,
3936 222,
3937 222,
3938 'DEFAULT', --amb context code
3939 entity_id,
3940 xla_events_s.nextval,
3941 override_event,
3942 xla_ae_headers_s.nextval,
3943 sob_id,
3944 gl_date,
3945 period_name,
3946 '', --reference date global acct eng
3947 'A', --balance type Actual
3948 category, --category
3949 'Y', --gl transfer status
3950 gl_posted_date, --gl transfer date
3951 'F', --acct entry status code final
3952 'STANDARD', --acct entry type code
3953 '', --description TBD
3954 '', --budget version id
3955 '', --funds status code
3956 '', --encumbrance type id
3957 '', --completed date
3958 doc_seq_id,
3959 doc_seq_val,
3960 cat_code,
3961 '', --packet id
3962 '', --group id
3963 sysdate, --row who creation date
3964 0,
3965 sysdate,
3966 0,
3967 0,
3968 sysdate,
3969 0, --program id
3970 222,
3971 '', --request id
3972 '', --AX columns start
3973 '',
3974 '',
3975 '',
3976 '',
3977 '',
3978 '' --upg valid flag
3979 --''
3980 )
3981 select /*+ use_nl(lgr,map) */
3982 1 AS BATCH_ID,
3983 decode(trx_type,
3984 'CASH' , 'Trade Receipts',
3985 --'CROSS_CURR' , 'Cross Currency',
3986 'MISC' , 'Misc Receipts',
3987 'RATE_ADJUST', 'Rate Adjustments',
3988 trx_type) AS CATEGORY,
3989 ev.TRX_ID AS TRX_ID,
3990 ev.TRX_DATE AS TRX_DATE,
3991 ev.SOB_ID AS SOB_ID,
3992 ev.CAT_CODE AS CAT_CODE,
3993 ev.TRX_TYPE AS TRX_TYPE,
3994 ev.TRX_STATUS AS TRX_STATUS,
3995 ev.OVERRIDE_EVENT AS OVERRIDE_EVENT,
3996 ev.PSTD_FLG AS PSTD_FLG,
3997 ev.PST_ID AS PST_ID,
3998 ev.GL_DATE AS GL_DATE,
3999 max(ev.GL_POSTED_DATE) AS GL_POSTED_DATE,
4000 ev.DOC_SEQ_ID AS DOC_SEQ_ID,
4001 ev.DOC_SEQ_VAL AS DOC_SEQ_VAL,
4002 ev.ENTITY_ID AS ENTITY_ID,
4003 map.PERIOD_NAME AS PERIOD_NAME,
4004 decode('P','D',0, --
4005 (select nvl(max(in_ev.event_number),0)
4006 from xla_events in_ev /*bug5867069*/
4007 where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
4008 ORDER BY decode(ev.OVERRIDE_EVENT,
4009 'RECP_CREATE' ,1,
4010 'RECP_UPDATE' ,2,
4011 'RECP_RATE_ADJUST' ,3,
4012 'RECP_REVERSE' ,6,
4013 'MISC_RECP_CREATE' ,7,
4014 'MISC_RECP_UPDATE' ,8,
4015 'MISC_RECP_RATE_ADJUST',9,
4016 'MISC_RECP_REVERSE' ,12,
4017 13), EV.GL_DATE, decode(EV.PST_ID,
4018 -3, 2,
4019 1), EV.PST_ID) LINE_NUM
4020 FROM
4021 (select
4022 cr.cash_receipt_id TRX_ID ,
4023 cr.receipt_date TRX_DATE ,
4024 cr.set_of_books_id SOB_ID ,
4025 decode(crh.created_from,
4026 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
4027 cr.type) TRX_TYPE ,
4028 decode(crh.status,
4029 'APPROVED', 'I',
4030 decode(crh.posting_control_id,
4031 -3, 'U',
4032 'P')) TRX_STATUS ,
4033 decode(cr.type,
4034 'MISC', 'MISC_',
4035 '') ||
4036 decode(crh.created_from,
4037 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
4038 decode(crh.status,
4039 'REVERSED','RECP_REVERSE',
4040 decode(crh1.first_posted_record_flag,
4041 '', 'RECP_CREATE',
4042 decode(decode(crh.postable_flag,
4043 'N', to_date('01-01-1900','DD-MM-YYYY'),
4044 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY'))),
4045 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4046 decode(crh.posting_control_id,
4047 crh1.posting_control_id, 'RECP_CREATE',
4048 'RECP_UPDATE'),
4049 'RECP_UPDATE')))) OVERRIDE_EVENT,
4050 decode(crh.posting_control_id,
4051 -3, 'U',
4052 'P') PSTD_FLG ,
4053 crh.posting_control_id PST_ID ,
4054 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
4055 nvl(trunc(max(crh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
4056 cr.doc_sequence_id DOC_SEQ_ID ,
4057 cr.doc_sequence_value DOC_SEQ_VAL ,
4058 rmth.name CAT_CODE ,
4059 te.entity_id ENTITY_ID
4060 FROM ar_cash_receipts_all cr,
4061 --ar_system_parameters_all sys,
4062 ar_cash_receipt_history_all crh,
4063 --xla_upgrade_dates gps,
4064 ar_receipt_methods rmth,
4065 ar_cash_receipt_history_all crh1,
4066 xla_transaction_entities_upg te
4067 WHERE NVL(cr.ax_accounted_flag,'N') = 'N'
4068 AND crh.cash_receipt_id = cr.cash_receipt_id
4069 and cr.cash_receipt_id = p_cash_receipt_id
4070 and not exists (select 1
4071 from xla_events xei, xla_ae_headers xahi
4072 where xei.application_id = 222
4073 and xei.application_id = xahi.application_id
4074 and xei.entity_id = xahi.entity_id
4075 and xei.reference_num_1 = crh.posting_control_id
4076 and trunc(xei.event_date) = trunc(crh.gl_date)
4077 and trunc(xei.event_date) = trunc(xahi.accounting_date)
4078 and xei.entity_id in
4079 (select xte.entity_id
4080 from xla_transaction_entities xte,
4081 ar_cash_receipts_all cri
4082 where nvl(xte.SOURCE_ID_INT_1,-99) = cri.cash_receipt_id
4083 and xte.entity_code in ('RECEIPTS')
4084 and xte.application_id = 222
4085 and xte.ledger_id = cri.set_of_books_id
4086 and cri.cash_receipt_id = cr.cash_receipt_id
4087 )
4088 )
4089 and crh.event_id is null
4090 --and trunc(crh.gl_date) between gps.start_date and gps.end_date
4091 --and gps.ledger_id = cr.set_of_books_id
4092 and decode(crh.posting_control_id,
4093 -3, decode('P', --l_action_flag
4094 'D','P',
4095 'P'), --
4096 'P') = 'P'
4097 AND cr.receipt_method_id = rmth.receipt_method_id
4098 AND cr.cash_receipt_id = crh1.cash_receipt_id (+)
4099 AND 'Y' = crh1.first_posted_record_flag (+)
4100 AND te.application_id = 222
4101 AND te.ledger_id = cr.set_of_books_id
4102 AND te.entity_code = 'RECEIPTS'
4103 AND nvl(te.source_id_int_1,-99) = cr.cash_receipt_id
4104 AND decode(crh.postable_flag, 'Y','Y',
4105 decode(crh.status, 'APPROVED',
4106 decode(crh1.first_posted_record_flag, '','Y',
4107 'N'),
4108 'N')) = 'Y'
4109 AND te.upg_batch_id > 0
4110 --AND nvl(sys.org_id,-9999) = nvl(ct.org_id, -9999)
4111 --AND sys.accounting_method = 'ACCRUAL'
4112 GROUP BY cr.cash_receipt_id,
4113 cr.receipt_date,
4114 cr.set_of_books_id,
4115 te.entity_id,
4116 crh.postable_flag,
4117 decode(crh.created_from,
4118 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
4119 cr.type),
4120 decode(crh.status,
4121 'APPROVED', 'I',
4122 decode(crh.posting_control_id,
4123 -3, 'U',
4124 'P')),
4125 cr.org_id,
4126 decode(cr.type,
4127 'MISC', 'MISC_',
4128 '') || decode(crh.created_from,
4129 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
4130 decode(crh.status,
4131 'REVERSED','RECP_REVERSE',
4132 decode(crh1.first_posted_record_flag,
4133 '', 'RECP_CREATE',
4134 decode(decode(crh.postable_flag,
4135 'N', to_date('01-01-1900','DD-MM-YYYY'),
4136 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY'))),
4137 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4138 decode(crh.posting_control_id,
4139 crh1.posting_control_id, 'RECP_CREATE',
4140 'RECP_UPDATE'),
4141 'RECP_UPDATE')))),
4142 decode(crh.posting_control_id,
4143 -3, 'U',
4144 'P') ,
4145 crh.posting_control_id,
4146 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4147 cr.doc_sequence_id,
4148 cr.doc_sequence_value,
4149 rmth.name
4150 UNION
4151 select
4152 mcd.cash_receipt_id TRX_ID ,
4153 cr.receipt_date TRX_DATE ,
4154 cr.set_of_books_id SOB_ID ,
4155 decode(mcd.created_from,
4156 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
4157 cr.type) TRX_TYPE ,
4158 decode(mcd.posting_control_id,
4159 -3, 'U',
4160 'P') TRX_STATUS ,
4161 decode(mcd.created_from,
4162 'RATE ADJUSTMENT TRIGGER', 'MISC_RECP_RATE_ADJUST',
4163 decode(SUBSTRB(mcd.created_from,1,19),
4164 'ARP_REVERSE_RECEIPT','MISC_RECP_REVERSE',
4165 decode(nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4166 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4167 decode(crh.posting_control_id,
4168 mcd.posting_control_id, 'MISC_RECP_CREATE',
4169 'MISC_RECP_UPDATE'),
4170 'MISC_RECP_UPDATE'))) OVERRIDE_EVENT,
4171 decode(mcd.posting_control_id,
4172 -3, 'U',
4173 'P') PSTD_FLG ,
4174 mcd.posting_control_id PST_ID ,
4175 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
4176 nvl(trunc(max(mcd.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')) GL_POSTED_DATE ,
4177 cr.doc_sequence_id DOC_SEQ_ID ,
4178 cr.doc_sequence_value DOC_SEQ_VAL ,
4179 rmth.name CAT_CODE ,
4180 te.entity_id ENTITY_ID
4181 FROM ar_cash_receipts_all cr,
4182 --ar_system_parameters_all sys,
4183 ar_misc_cash_distributions_all mcd,
4184 --xla_upgrade_dates gps,
4185 ar_receipt_methods rmth,
4186 ar_cash_receipt_history_all crh,
4187 xla_transaction_entities_upg te
4188 WHERE NVL(cr.ax_accounted_flag,'N') = 'N'
4189 AND cr.type='MISC'
4190 AND mcd.cash_receipt_id = cr.cash_receipt_id
4191 and cr.cash_receipt_id = p_cash_receipt_id
4192 and not exists (select 1
4193 from xla_events xei, xla_ae_headers xahi
4194 where xei.application_id = 222
4195 and xei.application_id = xahi.application_id
4196 and xei.entity_id = xahi.entity_id
4197 and xei.reference_num_1 = mcd.posting_control_id
4198 and trunc(xei.event_date) = trunc(mcd.gl_date)
4199 and trunc(xei.event_date) = trunc(xahi.accounting_date)
4200 and xei.entity_id in
4201 (select xte.entity_id
4202 from xla_transaction_entities xte,
4203 ar_cash_receipts_all cri
4204 where nvl(xte.SOURCE_ID_INT_1,-99) = cri.cash_receipt_id
4205 and xte.entity_code in ('RECEIPTS')
4206 and xte.application_id = 222
4207 and xte.ledger_id = cri.set_of_books_id
4208 and cri.cash_receipt_id = cr.cash_receipt_id
4209 )
4210 )
4211 --and trunc(mcd.gl_date) between gps.start_date and gps.end_date
4212 and mcd.event_id is null
4213 -- and gps.ledger_id = cr.set_of_books_id
4214 and decode(mcd.posting_control_id,
4215 -3, decode('P', --
4216 'D','P',
4217 'P'), --
4218 'P') = 'P'
4219 AND cr.receipt_method_id = rmth.receipt_method_id
4220 AND cr.cash_receipt_id = crh.cash_receipt_id
4221 AND crh.first_posted_record_flag = 'Y'
4222 AND te.application_id = 222
4223 AND te.ledger_id = cr.set_of_books_id
4224 AND te.entity_code = 'RECEIPTS'
4225 AND nvl(te.source_id_int_1,-99) = cr.cash_receipt_id
4226 AND te.upg_batch_id > 0
4227 --AND nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
4228 --AND sys.accounting_method = 'ACCRUAL'
4229 GROUP BY mcd.cash_receipt_id,
4230 cr.receipt_date,
4231 cr.set_of_books_id,
4232 te.entity_id,
4233 'Y',
4234 decode(mcd.created_from,
4235 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
4236 cr.type),
4237 decode(mcd.posting_control_id,
4238 -3, 'U',
4239 'P'),
4240 mcd.org_id,
4241 decode(mcd.created_from,
4242 'RATE ADJUSTMENT TRIGGER', 'MISC_RECP_RATE_ADJUST',
4243 decode(SUBSTRB(mcd.created_from,1,19),
4244 'ARP_REVERSE_RECEIPT','MISC_RECP_REVERSE',
4245 decode(nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4246 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4247 decode(crh.posting_control_id,
4248 mcd.posting_control_id, 'MISC_RECP_CREATE',
4249 'MISC_RECP_UPDATE'),
4250 'MISC_RECP_UPDATE'))),
4251 decode(mcd.posting_control_id,
4252 -3, 'U',
4253 'P') ,
4254 mcd.posting_control_id,
4255 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4256 cr.doc_sequence_id,
4257 cr.doc_sequence_value,
4258 rmth.name
4259 UNION
4260 select
4261 cr.cash_receipt_id TRX_ID ,
4262 cr.receipt_date TRX_DATE ,
4263 cr.set_of_books_id SOB_ID ,
4264 decode(crh.created_from,
4265 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
4266 cr.type) TRX_TYPE ,
4267 decode(NVL(app.confirmed_flag,'Y'),
4268 'Y', decode(app.posting_control_id,
4269 -3, 'U',
4270 'P'),
4271 'I') TRX_STATUS ,
4272 decode(crh.created_from,
4273 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
4274 decode(crh.status,
4275 'REVERSED','RECP_REVERSE',
4276 decode(crh1.first_posted_record_flag,
4277 '', 'RECP_CREATE',
4278 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4279 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4280 decode(app.posting_control_id,
4281 crh1.posting_control_id, 'RECP_CREATE',
4282 'RECP_UPDATE'),
4283 'RECP_UPDATE')))) OVERRIDE_EVENT,
4284 decode(app.posting_control_id,
4285 -3, 'U',
4286 'P') PSTD_FLG ,
4287 app.posting_control_id PST_ID ,
4288 nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')) GL_DATE,
4289 max(decode(crh.created_from,
4290 'RATE ADJUSTMENT TRIGGER',
4291 nvl(trunc((crh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')),
4292 decode(crh.status,
4293 'REVERSED', nvl(trunc((crh.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY')),
4294 nvl(trunc((app.gl_posted_date)),to_date('01-01-1900','DD-MM-YYYY'))))) GL_POSTED_DATE ,
4295 cr.doc_sequence_id DOC_SEQ_ID ,
4296 cr.doc_sequence_value DOC_SEQ_VAL ,
4297 rmth.name CAT_CODE ,
4298 te.entity_id ENTITY_ID
4299 FROM ar_cash_receipts_all cr,
4300 -- ar_system_parameters_all sys,
4301 ar_receivable_applications_all app,
4302 -- xla_upgrade_dates gps,
4303 ar_cash_receipt_history_all crh,
4304 ar_cash_receipt_history_all crh1,
4305 ar_receipt_methods rmth,
4306 xla_transaction_entities_upg te
4307 WHERE NVL(cr.ax_accounted_flag,'N') = 'N'
4308 AND app.cash_receipt_id = cr.cash_receipt_id
4309 and cr.cash_receipt_id = p_cash_receipt_id
4310 and not exists (select 1
4311 from xla_events xei, xla_ae_headers xahi
4312 where xei.application_id = 222
4313 and xei.application_id = xahi.application_id
4314 and xei.entity_id = xahi.entity_id
4315 and xei.reference_num_1 = app.posting_control_id
4316 and trunc(xei.event_date) = trunc(app.gl_date)
4317 and trunc(xei.event_date) = trunc(xahi.accounting_date)
4318 and xei.entity_id in
4319 (select xte.entity_id
4320 from xla_transaction_entities xte,
4321 ar_cash_receipts_all cri
4322 where nvl(xte.SOURCE_ID_INT_1,-99) = cri.cash_receipt_id
4323 and xte.entity_code in ('RECEIPTS')
4324 and xte.application_id = 222
4325 and xte.ledger_id = cri.set_of_books_id
4326 and cri.cash_receipt_id = cr.cash_receipt_id
4327 )
4328 )
4329 AND app.application_type = 'CASH'
4330 and app.event_id is null
4331 --and trunc(app.gl_date) between gps.start_date and gps.end_date
4332 --and gps.ledger_id = cr.set_of_books_id
4333 and decode(app.posting_control_id,
4334 -3, decode('P', --
4335 'D','P',
4336 'P'), --
4337 'P') = 'P'
4338 AND app.cash_receipt_history_id = crh.cash_receipt_history_id
4339 AND cr.cash_receipt_id = crh1.cash_receipt_id (+)
4340 AND 'Y' = crh1.first_posted_record_flag (+)
4341 AND decode(crh.postable_flag, 'Y','Y',
4342 decode(crh.status, 'APPROVED',
4343 decode(crh1.first_posted_record_flag, '','Y',
4344 'N'),
4345 'N')) = 'Y'
4346 AND cr.receipt_method_id = rmth.receipt_method_id
4347 AND te.application_id = 222
4348 AND te.ledger_id = cr.set_of_books_id
4349 AND te.entity_code = 'RECEIPTS'
4350 AND nvl(te.source_id_int_1,-99) = cr.cash_receipt_id
4351 AND te.upg_batch_id > 0
4352 --AND nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
4353 --AND sys.accounting_method = 'ACCRUAL'
4354 GROUP BY cr.cash_receipt_id,
4355 cr.receipt_date,
4356 cr.set_of_books_id,
4357 te.entity_id,
4358 decode(NVL(app.confirmed_flag,'Y'),
4359 'Y', decode(app.posting_control_id,
4360 -3, 'U',
4361 'P'),
4362 'I'),
4363 decode(crh.created_from,
4364 'RATE ADJUSTMENT TRIGGER', 'RATE_ADJUST',
4365 cr.type),
4366 cr.org_id,
4367 decode(crh.created_from,
4368 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
4369 decode(crh.status,
4370 'REVERSED','RECP_REVERSE',
4371 decode(crh1.first_posted_record_flag,
4372 '', 'RECP_CREATE',
4373 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4374 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4375 decode(app.posting_control_id,
4376 crh1.posting_control_id, 'RECP_CREATE',
4377 'RECP_UPDATE'),
4378 'RECP_UPDATE')))),
4379 decode(app.posting_control_id,
4380 -3, 'U',
4381 'P') ,
4382 app.posting_control_id ,
4383 nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4384 cr.doc_sequence_id,
4385 cr.doc_sequence_value,
4386 rmth.name) ev,
4387 gl_ledgers lgr,
4388 gl_date_period_map map
4389 where ev.sob_id = lgr.ledger_id
4390 and map.period_set_name = lgr.period_set_name
4391 and map.period_type = lgr.accounted_period_type
4392 and map.accounting_date = ev.gl_date
4393 group by
4394 decode(trx_type,
4395 'CASH' , 'Trade Receipts',
4396 --'CROSS_CURR' , 'Cross Currency',
4397 'MISC' , 'Misc Receipts',
4398 'RATE_ADJUST', 'Rate Adjustments',
4399 trx_type) ,
4400 ev.TRX_ID ,
4401 ev.TRX_DATE ,
4402 ev.SOB_ID ,
4403 ev.CAT_CODE ,
4404 ev.TRX_TYPE ,
4405 ev.TRX_STATUS ,
4406 ev.OVERRIDE_EVENT ,
4407 ev.PSTD_FLG ,
4408 ev.PST_ID ,
4409 ev.GL_DATE ,
4410 ev.DOC_SEQ_ID ,
4411 ev.DOC_SEQ_VAL ,
4412 ev.ENTITY_ID ,
4413 map.period_name ;
4414
4415 INSERT ALL
4416 WHEN 1 = 1 THEN
4417 INTO XLA_AE_LINES
4418 (upg_batch_id,
4419 ae_header_id,
4420 ae_line_num,
4421 application_id,
4422 code_combination_id,
4423 gl_transfer_mode_code,
4424 accounted_dr,
4425 accounted_cr,
4426 currency_code,
4427 currency_conversion_date,
4428 currency_conversion_rate,
4429 currency_conversion_type,
4430 entered_dr,
4431 entered_cr,
4432 description,
4433 accounting_class_code,
4434 gl_sl_link_id,
4435 gl_sl_link_table,
4436 party_type_code,
4437 party_id,
4438 party_site_id,
4439 statistical_amount,
4440 ussgl_transaction_code,
4441 jgzz_recon_ref,
4442 control_balance_flag,
4443 analytical_balance_flag,
4444 creation_date,
4445 created_by,
4446 last_update_date,
4447 last_updated_by,
4448 last_update_login,
4449 program_update_date,
4450 program_id,
4451 program_application_id,
4452 request_id,
4453 gain_or_loss_flag,
4454 accounting_date,
4455 ledger_id
4456 )
4457 VALUES
4458 ( batch_id,
4459 ae_header_id,
4460 line_num,
4461 222,
4462 code_combination_id,
4463 'D', --gl transfer mode Summary or detail
4464 acctd_amount_dr,
4465 acctd_amount_cr,
4466 currency_code,
4467 exchange_date,
4468 exchange_rate,
4469 exchange_type,
4470 amount_dr,
4471 amount_cr,
4472 '', --description TBD
4473 nvl(account_class,'XXXX'), --accounting class code
4474 xla_gl_sl_link_id_s.nextval, --gl sl link id
4475 'XLAJEL', --gl sl link table
4476 DECODE(third_party_id, NULL, NULL,'C'), --party type code
4477 third_party_id, --party id
4478 third_party_sub_id, --third party site
4479 '', --statistical amount
4480 '', --ussgl trx code
4481 '', --jgzz recon ref
4482 '', --control balance flag
4483 '', --analytical balance
4484 sysdate, --row who columns
4485 0,
4486 sysdate,
4487 0,
4488 0,
4489 sysdate,
4490 0, --program id
4491 222,
4492 '', --request id
4493 gain_or_loss_flag,
4494 accounting_date,
4495 ledger_id)
4496 WHEN 1 = 1 THEN
4497 INTO XLA_DISTRIBUTION_LINKS
4498 (APPLICATION_ID,
4499 EVENT_ID,
4500 AE_HEADER_ID,
4501 AE_LINE_NUM,
4502 ACCOUNTING_LINE_CODE,
4503 ACCOUNTING_LINE_TYPE_CODE,
4504 REF_AE_HEADER_ID,
4505 SOURCE_DISTRIBUTION_TYPE,
4506 SOURCE_DISTRIBUTION_ID_CHAR_1,
4507 SOURCE_DISTRIBUTION_ID_CHAR_2,
4508 SOURCE_DISTRIBUTION_ID_CHAR_3,
4509 SOURCE_DISTRIBUTION_ID_CHAR_4,
4510 SOURCE_DISTRIBUTION_ID_CHAR_5,
4511 SOURCE_DISTRIBUTION_ID_NUM_1,
4512 SOURCE_DISTRIBUTION_ID_NUM_2,
4513 SOURCE_DISTRIBUTION_ID_NUM_3,
4514 SOURCE_DISTRIBUTION_ID_NUM_4,
4515 SOURCE_DISTRIBUTION_ID_NUM_5,
4516 UNROUNDED_ENTERED_DR,
4517 UNROUNDED_ENTERED_CR,
4518 UNROUNDED_ACCOUNTED_DR,
4519 UNROUNDED_ACCOUNTED_CR,
4520 MERGE_DUPLICATE_CODE,
4521 TAX_LINE_REF_ID,
4522 TAX_SUMMARY_LINE_REF_ID,
4523 TAX_REC_NREC_DIST_REF_ID,
4524 STATISTICAL_AMOUNT,
4525 TEMP_LINE_NUM,
4526 EVENT_TYPE_CODE,
4527 EVENT_CLASS_CODE,
4528 REF_EVENT_ID,
4529 UPG_BATCH_ID)
4530 VALUES
4531 (222,
4532 event_id,
4533 ae_header_id,
4534 line_num,
4535 account_class,
4536 'C', --accounting line code customer
4537 ae_header_id, --reference header id
4538 source_table,
4539 '', --src dist id char
4540 '',
4541 '',
4542 '',
4543 '',
4544 line_id, --src dist id num
4545 '',
4546 '',
4547 '',
4548 '',
4549 amount_dr,
4550 amount_cr,
4551 acctd_amount_dr,
4552 acctd_amount_cr,
4553 'N', --merge dup code
4554 '', --tax_line_ref_id
4555 '', --tax_summary_line_ref_id
4556 '', --tax_rec_nrec_dist_ref_id
4557 '', --statistical amount
4558 line_num, --temp_line_num
4559 event_type_code, --event_type_code
4560 event_class_code, --event class code
4561 '', --ref_event_id,
4562 batch_id) --upgrade batch id
4563 select
4564 1 AS batch_id,
4565 ae_header_id AS ae_header_id,
4566 line_id AS line_id,
4567 event_id AS event_id,
4568 account_class AS account_class,
4569 gain_or_loss_flag AS gain_or_loss_flag,
4570 source_table AS source_table,
4571 code_combination_id AS code_combination_id,
4572 amount_dr AS amount_dr,
4573 amount_cr AS amount_cr,
4574 acctd_amount_dr AS acctd_amount_dr,
4575 acctd_amount_cr AS acctd_amount_cr,
4576 nvl(currency_code,'XXX') AS currency_code,
4577 third_party_id AS third_party_id,
4578 third_party_sub_id AS third_party_sub_id,
4579 exchange_date AS exchange_date,
4580 exchange_rate AS exchange_rate,
4581 exchange_type AS exchange_type,
4582 event_type_code AS event_type_code,
4583 event_class_code AS event_class_code,
4584 accounting_date AS accounting_date,
4585 ledger_id AS ledger_id,
4586 RANK() OVER (PARTITION BY event_id, ae_header_id
4587 ORDER BY line_id, ln_order) + max_line_num AS line_num
4588 FROM
4589 ( select
4590 hdr.ae_header_id ae_header_id,
4591 decode(ard.source_type, 'BANK_CHARGES', 'BANK_CHG',
4592 ard.source_type) account_class,
4593 'AR_DISTRIBUTIONS_ALL' source_table,
4594 ard.code_combination_id code_combination_id,
4595 ard.amount_dr amount_dr,
4596 ard.amount_cr amount_cr,
4597 ard.acctd_amount_dr acctd_amount_dr,
4598 ard.acctd_amount_cr acctd_amount_cr,
4599 ard.currency_code currency_code,
4600 ard.third_party_id third_party_id,
4601 ard.third_party_sub_id third_party_sub_id,
4602 ard.currency_conversion_date exchange_date,
4603 ard.currency_conversion_rate exchange_rate,
4604 ard.currency_conversion_type exchange_type,
4605 ard.line_id line_id,
4606 ev.event_id event_id,
4607 ev.event_type_code event_type_code,
4608 decode(cr.type,
4609 'CASH','RECEIPT',
4610 'MISC','MISC_RECEIPT',
4611 'RECEIPT') event_class_code,
4612 'N' gain_or_loss_flag,
4613 hdr.accounting_date accounting_date,
4614 hdr.ledger_id ledger_id,
4615 1 ln_order,
4616 nvl((select max(ae_line_num) from xla_ae_lines ael where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
4617 from ar_cash_receipts_all cr,
4618 ar_cash_receipt_history_all crh,
4619 -- xla_upgrade_dates gps,
4620 ar_cash_receipt_history_all crh1,
4621 xla_transaction_entities_upg ent,
4622 xla_events ev,
4623 xla_ae_headers hdr,
4624 ar_distributions_all ard
4625 where nvl(cr.ax_accounted_flag,'N') = 'N'
4626 and cr.cash_receipt_id = crh.cash_receipt_id
4627 and cr.cash_receipt_id = p_cash_receipt_id
4628 and not exists (
4629 select 'x' from XLA_DISTRIBUTION_LINKS xdl
4630 where xdl.application_id = 222
4631 and xdl.source_distribution_id_num_1 = ard.line_id
4632 and xdl.ae_header_id = hdr.ae_header_id
4633 and xdl.event_id = hdr.event_id
4634 and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
4635 )
4636 -- and trunc(crh.gl_date) between gps.start_date and gps.end_date
4637 -- and gps.ledger_id = cr.set_of_books_id
4638 and cr.cash_receipt_id = crh1.cash_receipt_id (+)
4639 and 'Y' = crh1.first_posted_record_flag (+)
4640 and ent.application_id = 222
4641 and ent.ledger_id = cr.set_of_books_id
4642 and ent.entity_code = 'RECEIPTS'
4643 and nvl(ent.source_id_int_1,-99) = cr.cash_receipt_id
4644 and ent.entity_id = ev.entity_id
4645 and ev.application_id = 222
4646 and ent.upg_batch_id > 0
4647 and ev.upg_batch_id > 0
4648 and crh.posting_control_id = ev.reference_num_1
4649 and nvl(trunc(crh.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
4650 and decode(cr.type,
4651 'MISC','MISC_',
4652 '') ||
4653 decode(crh.created_from,
4654 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
4655 decode(crh.status,
4656 'REVERSED','RECP_REVERSE',
4657 decode(crh1.first_posted_record_flag,
4658 '', 'RECP_CREATE',
4659 decode(decode(crh.postable_flag,
4660 'N', to_date('01-01-1900','DD-MM-YYYY'),
4661 nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY'))),
4662 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4663 decode(crh.posting_control_id,
4664 crh1.posting_control_id, 'RECP_CREATE',
4665 'RECP_UPDATE'),
4666 'RECP_UPDATE')))) = ev.event_type_code
4667 and decode(crh.postable_flag, 'Y','Y',
4668 decode(crh.status, 'APPROVED',
4669 decode(crh1.first_posted_record_flag, '','Y',
4670 'N'),
4671 'N')) = 'Y'
4672 and hdr.application_id = 222
4673 and cr.set_of_books_id = hdr.ledger_id
4674 and hdr.event_id = ev.event_id
4675 and ard.source_id = crh.cash_receipt_history_id
4676 and ard.source_table = 'CRH'
4677 UNION ALL /* Receipt applications */
4678 select
4679 hdr.ae_header_id ae_header_id,
4680 DECODE(ard.source_type, 'REC', 'RECEIVABLE',
4681 'CURR_ROUND', 'ROUNDING',
4682 'EXCH_GAIN', 'GAIN',
4683 'EXCH_LOSS', 'LOSS',
4684 'OTHER ACC',
4685 DECODE(app.applied_payment_schedule_id,
4686 -1,'ACC',
4687 -2,'SHORT_TERM_DEBT',
4688 -3,'WRITE_OFF',
4689 -4,'CLAIM',
4690 -5,'CHARGEBACK',
4691 -6,'REFUND',
4692 -7,'PREPAY',
4693 -8,'REFUND',
4694 -9,'CHARGEBACK',
4695 ard.source_type),
4696 ard.source_type) account_class,
4697 'AR_DISTRIBUTIONS_ALL' source_table,
4698 ard.code_combination_id code_combination_id,
4699 ard.amount_dr amount_dr,
4700 ard.amount_cr amount_cr,
4701 ard.acctd_amount_dr acctd_amount_dr,
4702 ard.acctd_amount_cr acctd_amount_cr,
4703 ard.currency_code currency_code,
4704 ard.third_party_id third_party_id,
4705 ard.third_party_sub_id third_party_sub_id,
4706 ard.currency_conversion_date exchange_date,
4707 ard.currency_conversion_rate exchange_rate,
4708 ard.currency_conversion_type exchange_type,
4709 ard.line_id line_id,
4710 ev.event_id event_id,
4711 ev.event_type_code event_type_code,
4712 'RECEIPT' event_class_code,
4713 decode(ard.source_type,
4714 'EXCH_GAIN','Y',
4715 'EXCH_LOSS','Y',
4716 'N') gain_or_loss_flag,
4717 hdr.accounting_date accounting_date,
4718 hdr.ledger_id ledger_id,
4719 2 ln_order,
4720 nvl((select max(ae_line_num) from xla_ae_lines ael where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
4721 from ar_cash_receipts_all cr,
4722 ar_system_parameters_all sys,
4723 ar_receivable_applications_all app,
4724 -- xla_upgrade_dates gps,
4725 xla_transaction_entities_upg ent,
4726 ar_cash_receipt_history_all crh,
4727 ar_cash_receipt_history_all crh1,
4728 xla_events ev,
4729 xla_ae_headers hdr,
4730 ar_distributions_all ard
4731 where nvl(cr.ax_accounted_flag,'N') = 'N'
4732 and nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
4733 and ( sys.accounting_method = 'ACCRUAL' or ( sys.accounting_method = 'CASH' and app.status in ('UNAPP', 'UNID', 'ACC') ) )
4734 and cr.cash_receipt_id = app.cash_receipt_id
4735 and cr.cash_receipt_id = p_cash_receipt_id
4736 and not exists (
4737 select 'x' from XLA_DISTRIBUTION_LINKS xdl
4738 where xdl.application_id = 222
4739 and xdl.source_distribution_id_num_1 = ard.line_id
4740 and xdl.ae_header_id = hdr.ae_header_id
4741 and xdl.event_id = hdr.event_id
4742 and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
4743 )
4744 and app.application_type = 'CASH'
4745 -- and trunc(app.gl_date) between gps.start_date and gps.end_date
4746 -- and gps.ledger_id = cr.set_of_books_id
4747 and app.cash_receipt_history_id = crh.cash_receipt_history_id
4748 and cr.cash_receipt_id = crh1.cash_receipt_id (+)
4749 and 'Y' = crh1.first_posted_record_flag (+)
4750 and ent.application_id = 222
4751 and ent.ledger_id = cr.set_of_books_id
4752 and ent.entity_code = 'RECEIPTS'
4753 and nvl(ent.source_id_int_1,-99) = cr.cash_receipt_id
4754 and ent.upg_batch_id > 0
4755 and ev.upg_batch_id > 0
4756 and ent.entity_id = ev.entity_id
4757 and ev.application_id = 222
4758 and app.posting_control_id = ev.reference_num_1
4759 and nvl(trunc(app.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
4760 and decode(crh.created_from,
4761 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
4762 decode(crh.status,
4763 'REVERSED','RECP_REVERSE',
4764 decode(crh1.first_posted_record_flag,
4765 '', 'RECP_CREATE',
4766 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4767 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4768 decode(app.posting_control_id,
4769 crh1.posting_control_id, 'RECP_CREATE',
4770 'RECP_UPDATE'),
4771 'RECP_UPDATE')))) = ev.event_type_code
4772 and decode(crh.postable_flag, 'Y','Y',
4773 decode(crh.status, 'APPROVED',
4774 decode(crh1.first_posted_record_flag, '','Y',
4775 'N'),
4776 'N')) = 'Y'
4777 and cr.set_of_books_id = hdr.ledger_id
4778 and hdr.event_id = ev.event_id
4779 and hdr.application_id = 222
4780 and ard.source_id = app.receivable_application_id
4781 and ard.source_table = 'RA'
4782 UNION ALL /* Misc Cash Dist */
4783 select
4784 hdr.ae_header_id ae_header_id,
4785 DECODE(ard.source_type, 'MISCCASH', 'MISC_CASH',
4786 ard.source_type) account_class,
4787 'AR_DISTRIBUTIONS_ALL' source_table,
4788 ard.code_combination_id code_combination_id,
4789 ard.amount_dr amount_dr,
4790 ard.amount_cr amount_cr,
4791 ard.acctd_amount_dr acctd_amount_dr,
4792 ard.acctd_amount_cr acctd_amount_cr,
4793 ard.currency_code currency_code,
4794 ard.third_party_id third_party_id,
4795 ard.third_party_sub_id third_party_sub_id,
4796 ard.currency_conversion_date exchange_date,
4797 ard.currency_conversion_rate exchange_rate,
4798 ard.currency_conversion_type exchange_type,
4799 ard.line_id line_id,
4800 ev.event_id event_id,
4801 ev.event_type_code event_type_code,
4802 'MISC_RECEIPT' event_class_code,
4803 'N' gain_or_loss_flag,
4804 hdr.accounting_date accounting_date,
4805 hdr.ledger_id ledger_id,
4806 1 ln_order,
4807 nvl((select max(ae_line_num) from xla_ae_lines ael where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
4808 from ar_cash_receipts_all cr,
4809 ar_misc_cash_distributions_all mcd,
4810 -- xla_upgrade_dates gps,
4811 ar_cash_receipt_history_all crh,
4812 xla_transaction_entities_upg ent,
4813 xla_events ev,
4814 xla_ae_headers hdr,
4815 ar_distributions_all ard
4816 where nvl(cr.ax_accounted_flag,'N') = 'N'
4817 and cr.cash_receipt_id = mcd.cash_receipt_id
4818 and cr.cash_receipt_id = p_cash_receipt_id
4819 -- and trunc(mcd.gl_date) between gps.start_date and gps.end_date
4820 -- and gps.ledger_id = cr.set_of_books_id
4821 and cr.cash_receipt_id = crh.cash_receipt_id
4822 and not exists (
4823 select 'x' from XLA_DISTRIBUTION_LINKS xdl
4824 where xdl.application_id = 222
4825 and xdl.source_distribution_id_num_1 = ard.line_id
4826 and xdl.ae_header_id = hdr.ae_header_id
4827 and xdl.event_id = hdr.event_id
4828 and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
4829 )
4830 and crh.first_posted_record_flag = 'Y'
4831 and ent.application_id = 222
4832 and ent.ledger_id = cr.set_of_books_id
4833 and ent.entity_code = 'RECEIPTS'
4834 and nvl(ent.source_id_int_1,-99) = cr.cash_receipt_id
4835 and ent.entity_id = ev.entity_id
4836 and ev.application_id = 222
4837 and ent.upg_batch_id > 0
4838 and ev.upg_batch_id > 0
4839 and mcd.posting_control_id = ev.reference_num_1
4840 and nvl(trunc(mcd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
4841 and decode(mcd.created_from,
4842 'RATE ADJUSTMENT TRIGGER', 'MISC_RECP_RATE_ADJUST',
4843 decode(SUBSTRB(mcd.created_from,1,19),
4844 'ARP_REVERSE_RECEIPT','MISC_RECP_REVERSE',
4845 decode(nvl(trunc(crh.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4846 nvl(trunc(mcd.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
4847 decode(crh.posting_control_id,
4848 mcd.posting_control_id, 'MISC_RECP_CREATE',
4849 'MISC_RECP_UPDATE'),
4850 'MISC_RECP_UPDATE'))) = ev.event_type_code
4851 and cr.set_of_books_id = hdr.ledger_id
4852 and hdr.event_id = ev.event_id
4853 and hdr.application_id = 222
4854 and ard.source_id = mcd.misc_cash_distribution_id
4855 and ard.source_table = 'MCD');
4856
4857 INSERT ALL
4858 WHEN 1 = 1 THEN
4859 INTO XLA_AE_LINES
4860 (upg_batch_id,
4861 ae_header_id,
4862 ae_line_num,
4863 application_id,
4864 code_combination_id,
4865 gl_transfer_mode_code,
4866 accounted_dr,
4867 accounted_cr,
4868 currency_code,
4869 currency_conversion_date,
4870 currency_conversion_rate,
4871 currency_conversion_type,
4872 entered_dr,
4873 entered_cr,
4874 description,
4875 accounting_class_code,
4876 gl_sl_link_id,
4877 gl_sl_link_table,
4878 party_type_code,
4879 party_id,
4880 party_site_id,
4881 statistical_amount,
4882 ussgl_transaction_code,
4883 jgzz_recon_ref,
4884 control_balance_flag,
4885 analytical_balance_flag,
4886 creation_date,
4887 created_by,
4888 last_update_date,
4889 last_updated_by,
4890 last_update_login,
4891 program_update_date,
4892 program_id,
4893 program_application_id,
4894 request_id,
4895 gain_or_loss_flag,
4896 accounting_date,
4897 ledger_id
4898 )
4899 VALUES
4900 ( batch_id,
4901 ae_header_id,
4902 line_num,
4903 222,
4904 code_combination_id,
4905 'D', --gl transfer mode Summary or detail
4906 acctd_amount_dr,
4907 acctd_amount_cr,
4908 currency_code,
4909 exchange_date,
4910 exchange_rate,
4911 exchange_type,
4912 amount_dr,
4913 amount_cr,
4914 '', --description TBD
4915 account_class, --accounting class code
4916 xla_gl_sl_link_id_s.nextval, --gl sl link id
4917 'XLAJEL', --gl sl link table
4918 DECODE(third_party_id, NULL, NULL,'C'), --party type code
4919 third_party_id, --party id
4920 third_party_sub_id, --third party site
4921 '', --statistical amount
4922 '', --ussgl trx code
4923 '', --jgzz recon ref
4924 '', --control balance flag
4925 '', --analytical balance
4926 sysdate, --row who columns
4927 0,
4928 sysdate,
4929 0,
4930 0,
4931 sysdate,
4932 0, --program id
4933 222,
4934 '', --request id
4935 'N',
4936 accounting_date,
4937 ledger_id)
4938 WHEN 1 = 1 THEN
4939 INTO XLA_DISTRIBUTION_LINKS
4940 (APPLICATION_ID,
4941 EVENT_ID,
4942 AE_HEADER_ID,
4943 AE_LINE_NUM,
4944 ACCOUNTING_LINE_CODE,
4945 ACCOUNTING_LINE_TYPE_CODE,
4946 REF_AE_HEADER_ID,
4947 SOURCE_DISTRIBUTION_TYPE,
4948 SOURCE_DISTRIBUTION_ID_CHAR_1,
4949 SOURCE_DISTRIBUTION_ID_CHAR_2,
4950 SOURCE_DISTRIBUTION_ID_CHAR_3,
4951 SOURCE_DISTRIBUTION_ID_CHAR_4,
4952 SOURCE_DISTRIBUTION_ID_CHAR_5,
4953 SOURCE_DISTRIBUTION_ID_NUM_1,
4954 SOURCE_DISTRIBUTION_ID_NUM_2,
4955 SOURCE_DISTRIBUTION_ID_NUM_3,
4956 SOURCE_DISTRIBUTION_ID_NUM_4,
4957 SOURCE_DISTRIBUTION_ID_NUM_5,
4958 UNROUNDED_ENTERED_DR,
4959 UNROUNDED_ENTERED_CR,
4960 UNROUNDED_ACCOUNTED_DR,
4961 UNROUNDED_ACCOUNTED_CR,
4962 MERGE_DUPLICATE_CODE,
4963 TAX_LINE_REF_ID,
4964 TAX_SUMMARY_LINE_REF_ID,
4965 TAX_REC_NREC_DIST_REF_ID,
4966 STATISTICAL_AMOUNT,
4967 TEMP_LINE_NUM,
4968 EVENT_TYPE_CODE,
4969 EVENT_CLASS_CODE,
4970 REF_EVENT_ID,
4971 UPG_BATCH_ID)
4972 VALUES
4973 (222,
4974 event_id,
4975 ae_header_id,
4976 line_num,
4977 account_class,
4978 'C', --accounting line code customer
4979 ae_header_id, --reference header id
4980 source_table,
4981 '', --src dist id char
4982 '',
4983 '',
4984 '',
4985 '',
4986 line_id, --src dist id num
4987 '',
4988 '',
4989 '',
4990 '',
4991 amount_dr,
4992 amount_cr,
4993 acctd_amount_dr,
4994 acctd_amount_cr,
4995 'N', --merge dup code
4996 tax_line_id, --tax_line_ref_id
4997 '', --tax_summary_line_ref_id
4998 '', --tax_rec_nrec_dist_ref_id
4999 '', --statistical amount
5000 line_num, --temp_line_num
5001 event_type_code, --event type
5002 'RECEIPT', --event class code
5003 '', --ref_event_id,
5004 batch_id) --upgrade batch id
5005 select
5006 1 AS batch_id,
5007 ae_header_id AS ae_header_id,
5008 line_id AS line_id,
5009 event_id AS event_id,
5010 event_type_code AS event_type_code,
5011 account_class AS account_class,
5012 source_table AS source_table,
5013 code_combination_id AS code_combination_id,
5014 amount_dr AS amount_dr,
5015 amount_cr AS amount_cr,
5016 acctd_amount_dr AS acctd_amount_dr,
5017 acctd_amount_cr AS acctd_amount_cr,
5018 nvl(currency_code,'XXXX') AS currency_code,
5019 third_party_id AS third_party_id,
5020 third_party_sub_id AS third_party_sub_id,
5021 exchange_date AS exchange_date,
5022 exchange_rate AS exchange_rate,
5023 exchange_type AS exchange_type,
5024 tax_line_id AS tax_line_id,
5025 accounting_date AS accounting_date,
5026 ledger_id AS ledger_id,
5027 RANK() OVER (PARTITION BY event_id, ae_header_id
5028 ORDER BY line_id, ln_order) + max_line_num AS line_num
5029 FROM
5030 ( /* On Account CM and receipt applications */
5031 select /*+ ordered rowid(app) use_nl(ctcm,crh1,crh,cr,ent,ev,hdr,cbs,ctlgd) use_hash(gps) swap_join_inputs(gps) INDEX(ent XLA_TRANSACTION_ENTITIES_N1) INDEX(ev XLA_EVENTS_U3) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
5032 hdr.ae_header_id ae_header_id,
5033 decode(cbs.source,
5034 'GL', ctlgd.account_class,
5035 'ADJ', 'ADJ',
5036 'UNA', 'UNA',
5037 cbs.type) account_class,
5038 'AR_CASH_BASIS_DISTRIBUTIONS' source_table,
5039 cbs.code_combination_id code_combination_id,
5040 decode(sign(cbs.amount),
5041 -1, abs(cbs.amount),
5042 '') amount_dr,
5043 decode(sign(cbs.amount),
5044 1,abs(cbs.amount),
5045 0,abs(cbs.amount),
5046 '') amount_cr,
5047 decode(sign(cbs.acctd_amount),
5048 -1, abs(cbs.acctd_amount),
5049 '') acctd_amount_dr,
5050 decode(sign(cbs.acctd_amount),
5051 1,abs(cbs.acctd_amount),
5052 0,abs(cbs.acctd_amount),
5053 '') acctd_amount_cr,
5054 cbs.currency_code currency_code,
5055 decode(app.application_type,
5056 'CM', ctcm.bill_to_customer_id,
5057 cr.pay_from_customer) third_party_id,
5058 decode(app.application_type,
5059 'CM', ctcm.bill_to_site_use_id,
5060 cr.customer_site_use_id) third_party_sub_id,
5061 decode(app.application_type,
5062 'CM', ctcm.exchange_date,
5063 crh.exchange_date) exchange_date,
5064 decode(app.application_type,
5065 'CM', NVL(ctcm.exchange_rate,1),
5066 NVL(crh.exchange_rate,1) *
5067 NVL(app.trans_to_receipt_rate, 1)) exchange_rate,
5068 decode(app.application_type,
5069 'CM', NVL(ctcm.exchange_rate_type,1),
5070 NVL(crh.exchange_rate_type,1)) exchange_type,
5071 cbs.cash_basis_distribution_id line_id,
5072 ev.event_id event_id,
5073 ev.event_type_code event_type_code,
5074 null tax_line_id,
5075 hdr.accounting_date accounting_date,
5076 hdr.ledger_id AS ledger_id,
5077 1 ln_order,
5078 nvl((select max(ae_line_num) from xla_ae_lines ael where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
5079 from ar_receivable_applications_all app,
5080 ar_system_parameters_all sys,
5081 xla_upgrade_dates gps,
5082 xla_transaction_entities_upg ent,
5083 ra_customer_trx_all ctcm,
5084 ar_cash_receipt_history_all crh1,
5085 ar_cash_receipt_history_all crh,
5086 ar_cash_receipts_all cr,
5087 xla_events ev,
5088 xla_ae_headers hdr,
5089 ar_cash_basis_dists_all cbs,
5090 ra_cust_trx_line_gl_dist_all ctlgd
5091 where nvl(app.postable,'Y') = 'Y'
5092 and cr.cash_receipt_id = p_cash_receipt_id
5093 and nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
5094 and sys.accounting_method = 'CASH'
5095 and nvl(app.confirmed_flag,'Y') = 'Y'
5096 and app.status = 'APP'
5097 and trunc(app.gl_date) between gps.start_date and gps.end_date
5098 and gps.ledger_id = app.set_of_books_id
5099 and app.customer_trx_id = ctcm.customer_trx_id (+)
5100 and app.cash_receipt_id = cr.cash_receipt_id (+)
5101 and app.cash_receipt_id = crh1.cash_receipt_id (+)
5102 and 'Y' = crh1.first_posted_record_flag (+)
5103 and app.cash_receipt_history_id = crh.cash_receipt_history_id (+)
5104 AND app.posting_control_id <> -3
5105 and ent.application_id = 222
5106 and ent.ledger_id = app.set_of_books_id
5107 and ent.entity_code = decode(app.customer_trx_id,
5108 '', 'RECEIPTS',
5109 'TRANSACTIONS')
5110 and nvl(ent.source_id_int_1,-99) = nvl(app.customer_trx_id, app.cash_receipt_id)
5111 and ent.entity_id = ev.entity_id
5112 and ev.upg_batch_id > 0
5113 and ev.upg_batch_id in (select batch_id from ar_upg_120_control
5114 --where action_flag = 'D' and script_name = 'ar120dwnt.sql'
5115 )
5116 and hdr.application_id = 222
5117 and app.set_of_books_id = hdr.ledger_id
5118 and hdr.event_id = ev.event_id
5119 and app.posting_control_id = ev.reference_num_1
5120 and nvl(trunc(app.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
5121 and decode(app.customer_trx_id,
5122 '', decode(crh.created_from,
5123 'RATE ADJUSTMENT TRIGGER', 'RECP_RATE_ADJUST',
5124 decode(crh.status,
5125 'REVERSED','RECP_REVERSE',
5126 decode(crh1.first_posted_record_flag,
5127 '', 'RECP_CREATE',
5128 decode(nvl(trunc(app.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
5129 nvl(trunc(crh1.gl_date),to_date('01-01-1900','DD-MM-YYYY')),
5130 decode(app.posting_control_id,
5131 crh1.posting_control_id, 'RECP_CREATE',
5132 'RECP_UPDATE'),
5133 'RECP_UPDATE')))),
5134 ev.event_type_code) = ev.event_type_code
5135 and decode(app.customer_trx_id,
5136 '', decode(crh.postable_flag, 'Y','Y',
5137 decode(crh.status, 'APPROVED',
5138 decode(crh1.first_posted_record_flag, '','Y',
5139 'N'),
5140 'N')),
5141 decode(ctcm.previous_customer_trx_id,
5142 '','Y',
5143 'N')) = 'Y'
5144 and cbs.receivable_application_id = app.receivable_application_id
5145 and cbs.source_id = ctlgd.cust_trx_line_gl_dist_id (+)
5146 and cr.cash_receipt_id = p_cash_receipt_id
5147 and not exists (
5148 select 'x' from xla_distribution_links xdl
5149 where xdl.application_id = 222
5150 and xdl.source_distribution_id_num_1 = cbs.cash_basis_distribution_id
5151 and xdl.ae_header_id = hdr.ae_header_id
5152 and xdl.event_id = hdr.event_id
5153 and xdl.SOURCE_DISTRIBUTION_TYPE = 'AR_CASH_BASIS_DISTRIBUTIONS'
5154 )
5155 );
5156
5157 EXCEPTION
5158 WHEN OTHERS THEN
5159 RAISE;
5160 END UPGRADE_CASH_SINGLE;
5161
5162
5163 /* Changes for BUG 15921888 */
5164 PROCEDURE UPGRADE_CASH_RA_DATA(
5165 p_cash_receipt_id IN NUMBER) IS
5166 BEGIN
5167
5168 UPDATE ar_receivable_applications ra
5169 SET ra.cash_receipt_history_id =
5170 ( SELECT MAX(crh.cash_receipt_history_id)
5171 FROM ar_cash_receipt_history crh
5172 WHERE crh.cash_receipt_id = ra.cash_receipt_id
5173 AND crh.creation_date = (SELECT MAX(creation_date)
5174 FROM ar_cash_receipt_history
5175 WHERE cash_receipt_id = crh.cash_receipt_id
5176 AND creation_date <= ra.creation_date
5177 group by cash_receipt_id)
5178 ),
5179 ra.last_update_date = sysdate
5180 WHERE cash_receipt_id = p_cash_receipt_id
5181 AND ra.application_type = 'CASH'
5182 AND ra.cash_receipt_history_id IS NULL;
5183
5184 EXCEPTION
5185 WHEN OTHERS THEN
5186 RAISE;
5187 END UPGRADE_CASH_RA_DATA;
5188
5189
5190 END ARP_XLA_UPGRADE;