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