[Home] [Help]
PACKAGE BODY: APPS.ARP_MRC_XLA_UPGRADE
Source
1 PACKAGE BODY ARP_MRC_XLA_UPGRADE AS
2 /* $Header: ARMXLAUB.pls 120.14 2006/12/16 00:27:15 jvarkey noship $ */
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,'XXXX') 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 ctlgd.account_class account_class,
594 'RA_CUST_TRX_LINE_GL_DIST_ALL' source_table,
595 gld.code_combination_id code_combination_id,
596 decode(ctlgd.account_class,
597 'REC', decode(sign(ctlgd.amount),
598 1, abs(ctlgd.amount),
599 0, abs(ctlgd.amount),
600 ''),
601 decode(sign(ctlgd.amount),
602 -1, abs(ctlgd.amount),
603 '')) amount_dr,
604 decode(ctlgd.account_class,
605 'REC', decode(sign(ctlgd.amount),
606 -1, abs(ctlgd.amount),
607 ''),
608 decode(sign(ctlgd.amount),
609 1, abs(ctlgd.amount),
610 0, abs(ctlgd.amount),
611 '')) amount_cr,
612 decode(ctlgd.account_class,
613 'REC', decode(sign(ctlgd.acctd_amount),
614 1, abs(ctlgd.acctd_amount),
615 0, abs(ctlgd.acctd_amount),
616 ''),
617 decode(sign(ctlgd.acctd_amount),
618 -1, abs(ctlgd.acctd_amount),
619 '')) acctd_amount_dr,
620 decode(ctlgd.account_class,
621 'REC', decode(sign(ctlgd.acctd_amount),
622 -1, abs(ctlgd.acctd_amount),
623 ''),
624 decode(sign(ctlgd.acctd_amount),
625 1, abs(ctlgd.acctd_amount),
626 0, abs(ctlgd.acctd_amount),
627 '')) acctd_amount_cr,
628 trx.invoice_currency_code currency_code,
629 trx.bill_to_customer_id third_party_id,
630 trx.bill_to_site_use_id third_party_sub_id,
631 ct.exchange_date exchange_date,
632 ct.exchange_rate exchange_rate,
633 ct.exchange_rate_type exchange_type,
634 ctlgd.cust_trx_line_gl_dist_id line_id,
635 dl.tax_line_ref_id tax_line_id,
636 'N' gain_loss_flag,
637 dl.event_type_code event_type_code,
638 dl.event_class_code event_class_code,
639 hdr.accounting_date accounting_date,
640 hdr1.ledger_id ledger_id,
641 1 ln_order
642 --
643 from ra_mc_customer_trx ct,
644 ra_customer_trx_all trx,
645 ra_cust_trx_line_gl_dist_all gld,
646 xla_upgrade_dates gps,
647 ra_mc_trx_line_gl_dist ctlgd,
648 xla_distribution_links dl,
649 xla_ae_headers hdr,
650 xla_ae_headers hdr1
651 --
652 where ct.rowid >= l_start_rowid
653 and ct.rowid <= l_end_rowid
654 --
655 and trx.customer_trx_id = ct.customer_trx_id
656 and NVL(trx.ax_accounted_flag,'N') = 'N'
657 --
658 and gld.customer_trx_id = ct.customer_trx_id
659 and gld.account_set_flag = 'N'
660 --
661 and trunc(gld.gl_date) between gps.start_date and gps.end_date
662 and gps.ledger_id = ct.set_of_books_id
663 --
664 and ctlgd.cust_trx_line_gl_dist_id = gld.cust_trx_line_gl_dist_id
665 and ctlgd.customer_trx_id = trx.customer_trx_id
666 and ctlgd.posting_control_id <> -3
667 and ctlgd.set_of_books_id = ct.set_of_books_id
668 --
669 and dl.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
670 and dl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
671 and dl.application_id = 222
672 and dl.upg_batch_id = l_batch_id
673 --
674 and hdr.ae_header_id = dl.ae_header_id
675 and hdr.application_id = 222
676 and hdr.upg_batch_id = l_batch_id
677 and hdr.ledger_id = trx.set_of_books_id
678 --
679 and hdr1.application_id = 222
680 and hdr1.upg_batch_id = l_batch_id
681 and hdr1.ae_header_id <> hdr.ae_header_id
682 and hdr1.ledger_id = ctlgd.set_of_books_id
683 and hdr1.entity_id = hdr.entity_id
684 and hdr1.event_id = hdr.event_id
685 and hdr1.event_type_code = hdr.event_type_code
686 and hdr1.accounting_date = hdr.accounting_date
687 and hdr1.period_name = hdr.period_name
688 and hdr1.je_category_name = hdr.je_category_name
689 and hdr1.gl_transfer_date = hdr.gl_transfer_date
690 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
691 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
692 -- and hdr1.doc_category_code = hdr.doc_category_code
693
694
695 UNION /* CM applications */
696 select /*+ ordered rowid(ct) use_nl(trx,ra,app,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
697 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
698 hdr.ae_header_id ae_header_id,
699 hdr1.ae_header_id header_id,
700 hdr.event_id event_id,
701 dist.set_of_books_id sob_id,
702 dist.source_type account_class,
703 'AR_DISTRIBUTIONS_ALL' source_table,
704 dist.code_combination_id code_combination_id,
705 dist.amount_dr amount_dr,
706 dist.amount_cr amount_cr,
707 dist.acctd_amount_dr acctd_amount_dr,
708 dist.acctd_amount_cr acctd_amount_cr,
709 dist.currency_code currency_code,
710 dist.third_party_id third_party_id,
711 dist.third_party_sub_id third_party_sub_id,
712 dist.currency_conversion_date exchange_date,
713 dist.currency_conversion_rate exchange_rate,
714 dist.currency_conversion_type exchange_type,
715 dist.line_id line_id,
716 null tax_line_id,
717 decode(dist.source_type,
718 'EXCH_GAIN','Y',
719 'EXCH_LOSS','Y',
720 'CURR_ROUND','Y',
721 'N') gain_loss_flag,
722 dl.event_type_code event_type_code,
723 dl.event_class_code event_class_code,
724 hdr.accounting_date accounting_date,
725 hdr1.ledger_id ledger_id,
726 2 ln_order
727 --
728 from ra_mc_customer_trx ct,
729 ra_customer_trx_all trx,
730 ar_receivable_applications_all ra,
731 xla_upgrade_dates gps,
732 ar_mc_receivable_apps app,
733 ar_mc_distributions_all dist,
734 xla_distribution_links dl,
735 xla_ae_headers hdr,
736 xla_ae_headers hdr1
737
738 --
739 where ct.rowid >= l_start_rowid
740 and ct.rowid <= l_end_rowid
741 --
742 and trx.customer_trx_id = ct.customer_trx_id
743 and NVL(trx.ax_accounted_flag,'N') = 'N'
744 --
745 and ra.customer_trx_id = ct.customer_trx_id
746 --
747 and trunc(ra.gl_date) between gps.start_date and gps.end_date
748 and gps.ledger_id = ct.set_of_books_id
749 --
750 and app.receivable_application_id = ra.receivable_application_id
751 and app.posting_control_id <> -3
752 and app.set_of_books_id = ct.set_of_books_id
753 --
754 and dist.source_id = app.receivable_application_id
755 and dist.set_of_books_id = app.set_of_books_id
756 and dist.source_table = 'RA'
757 --
758 and dl.source_distribution_id_num_1 = dist.line_id
759 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
760 and dl.application_id = 222
761 and dl.upg_batch_id = l_batch_id
762 --
763 and hdr.ae_header_id = dl.ae_header_id
764 and hdr.application_id = 222
765 and hdr.upg_batch_id = l_batch_id
766 and hdr.ledger_id = trx.set_of_books_id
767 --
768 and hdr1.application_id = 222
769 and hdr1.upg_batch_id = l_batch_id
770 and hdr1.ae_header_id <> hdr.ae_header_id
771 and hdr1.ledger_id = dist.set_of_books_id
772 and hdr1.entity_id = hdr.entity_id
773 and hdr1.event_id = hdr.event_id
774 and hdr1.event_type_code = hdr.event_type_code
775 and hdr1.accounting_date = hdr.accounting_date
776 and hdr1.period_name = hdr.period_name
777 and hdr1.je_category_name = hdr.je_category_name
778 and hdr1.gl_transfer_date = hdr.gl_transfer_date
779 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
780 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
781 -- and hdr1.doc_category_code = hdr.doc_category_code
782
783 UNION /* Bills Receivable */
784 select /*+ ordered rowid(ct) use_nl(trx,th,trh,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
785 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
786 hdr.ae_header_id ae_header_id,
787 hdr1.ae_header_id header_id,
788 hdr.event_id event_id,
789 dist.set_of_books_id sob_id,
790 dist.source_type account_class,
791 'AR_DISTRIBUTIONS_ALL' source_table,
792 dist.code_combination_id code_combination_id,
793 dist.amount_dr amount_dr,
794 dist.amount_cr amount_cr,
795 dist.acctd_amount_dr acctd_amount_dr,
796 dist.acctd_amount_cr acctd_amount_cr,
797 dist.currency_code currency_code,
798 dist.third_party_id third_party_id,
799 dist.third_party_sub_id third_party_sub_id,
800 dist.currency_conversion_date exchange_date,
801 dist.currency_conversion_rate exchange_rate,
802 dist.currency_conversion_type exchange_type,
803 dist.line_id line_id,
804 null tax_line_id,
805 'N' gain_loss_flag,
806 dl.event_type_code event_type_code,
807 dl.event_class_code event_class_code,
808 hdr.accounting_date accounting_date,
809 hdr1.ledger_id ledger_id,
810 3 ln_order
811 --
812 from ra_mc_customer_trx ct,
813 ra_customer_trx_all trx,
814 ar_transaction_history_all th,
815 xla_upgrade_dates gps,
816 ar_mc_transaction_history trh,
817 ar_mc_distributions_all dist,
818 xla_distribution_links dl,
819 xla_ae_headers hdr,
820 xla_ae_headers hdr1
821 --
822 where ct.rowid >= l_start_rowid
823 and ct.rowid <= l_end_rowid
824 --
825 and trx.customer_trx_id = ct.customer_trx_id
826 and NVL(trx.ax_accounted_flag,'N') = 'N'
827 --
828 and th.customer_trx_id = ct.customer_trx_id
829 and th.postable_flag = 'Y'
830 --
831 and trunc(th.gl_date) between gps.start_date and gps.end_date
832 and gps.ledger_id = ct.set_of_books_id
833 --
834 and trh.transaction_history_id = th.transaction_history_id
835 and trh.posting_control_id <> -3
836 and trh.set_of_books_id = ct.set_of_books_id
837 --
838 and dist.source_id = trh.transaction_history_id
839 and dist.source_table = 'TH'
840 and dist.set_of_books_id = trh.set_of_books_id
841 --
842 and dl.source_distribution_id_num_1 = dist.line_id
843 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
844 and dl.application_id = 222
845 and dl.upg_batch_id = l_batch_id
846 --
847 and hdr.ae_header_id = dl.ae_header_id
848 and hdr.application_id = 222
849 and hdr.upg_batch_id = l_batch_id
850 and hdr.ledger_id = trx.set_of_books_id
851 --
852 and hdr1.application_id = 222
853 and hdr1.upg_batch_id = l_batch_id
854 and hdr1.ae_header_id <> hdr.ae_header_id
855 and hdr1.ledger_id = dist.set_of_books_id
856 and hdr1.entity_id = hdr.entity_id
857 and hdr1.event_id = hdr.event_id
858 and hdr1.event_type_code = hdr.event_type_code
859 and hdr1.accounting_date = hdr.accounting_date
860 and hdr1.period_name = hdr.period_name
861 and hdr1.je_category_name = hdr.je_category_name
862 and hdr1.gl_transfer_date = hdr.gl_transfer_date
863 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
864 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
865 -- and hdr1.doc_category_code = hdr.doc_category_code
866 );
867
868 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
869
870 UPGRADE_MC_GAIN_LOSS(
871 l_start_rowid,
872 l_end_rowid,
873 l_table_name,
874 l_batch_id);
875
876 END IF; --NVL(l_entity_type,'L') = 'L'
877
878 ad_parallel_updates_pkg.processed_rowid_range(
879 l_rows_processed,
880 l_end_rowid);
881
882 commit;
883
884 ad_parallel_updates_pkg.get_rowid_range(
885 l_start_rowid,
886 l_end_rowid,
887 l_any_rows_to_process,
888 l_batch_size,
889 FALSE);
890
891 l_rows_processed := 0 ;
892
893 END LOOP ; /* end of WHILE loop */
894
895 EXCEPTION
896 WHEN NO_DATA_FOUND THEN
897 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_transactions');
898 RAISE;
899
900 WHEN OTHERS THEN
901 --arp_standard.debug('OTHERS EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_transactions');
902 RAISE;
903
904 END UPGRADE_MC_TRANSACTIONS;
905
906 /*========================================================================
907 | PUBLIC PROCEDURE UPGRADE_MC_RECEIPTS
908 |
909 | DESCRIPTION
910 | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
911 | XLA_DISTRIBUTION_LINKS for records related to receipts.
912 |
913 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
914 |
915 | CALLS PROCEDURES/FUNCTIONS
916 | UPGRADE_MC_GAIN_LOSS
917 |
918 | PARAMETERS
919 |
920 | KNOWN ISSUES
921 |
922 | NOTES
923 |
924 | MODIFICATION HISTORY
925 | Date Author Description of Changes
926 | 03-JUL-2005 JVARKEY Created
927 | 30-AUG-2005 JVARKEY Modified the flow
928 | 20-SEP-2005 JVARKEY Detached the insert into
929 | xla_ae_headers into seperate
930 | insert statement
931 *=======================================================================*/
932
933 PROCEDURE UPGRADE_MC_RECEIPTS(
934 l_table_owner IN VARCHAR2,
935 l_table_name IN VARCHAR2,
936 l_script_name IN VARCHAR2,
937 l_worker_id IN VARCHAR2,
938 l_num_workers IN VARCHAR2,
939 l_batch_size IN VARCHAR2,
940 l_batch_id IN NUMBER,
941 l_action_flag IN VARCHAR2,
942 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
943
944 l_start_rowid rowid;
945 l_end_rowid rowid;
946 l_any_rows_to_process boolean;
947 l_rows_processed number := 0;
948
949 BEGIN
950
951 /* ------ Initialize the rowid ranges ------ */
952 ad_parallel_updates_pkg.initialize_rowid_range(
953 ad_parallel_updates_pkg.ROWID_RANGE,
954 l_table_owner,
955 l_table_name,
956 l_script_name,
957 l_worker_id,
958 l_num_workers,
959 l_batch_size, 0);
960
961 /* ------ Get rowid ranges ------ */
962 ad_parallel_updates_pkg.get_rowid_range(
963 l_start_rowid,
964 l_end_rowid,
965 l_any_rows_to_process,
966 l_batch_size,
967 TRUE);
968
969 WHILE ( l_any_rows_to_process = TRUE )
970 LOOP
971
972 l_rows_processed := 0;
973
974 IF NVL(l_entity_type,'H') = 'H' THEN
975 -----------------------
976 -- Inserting headers --
977 -----------------------
978
979 INSERT ALL
980 WHEN 1 = 1 THEN
981 INTO XLA_AE_HEADERS
982 (upg_batch_id,
983 upg_source_application_id,
984 application_id,
985 amb_context_code,
986 entity_id,
987 event_id,
988 event_type_code,
989 ae_header_id,
990 ledger_id,
991 accounting_date,
992 period_name,
993 reference_date,
994 balance_type_code,
995 je_category_name,
996 gl_transfer_status_code,
997 gl_transfer_date,
998 accounting_entry_status_code,
999 accounting_entry_type_code,
1000 description,
1001 budget_version_id,
1002 funds_status_code,
1003 -- encumbrance_type_id,
1004 completed_date,
1005 doc_sequence_id,
1006 doc_sequence_value,
1007 doc_category_code,
1008 packet_id,
1009 group_id,
1010 creation_date,
1011 created_by,
1012 last_update_date,
1013 last_updated_by,
1014 last_update_login,
1015 program_update_date,
1016 program_id,
1017 program_application_id,
1018 request_id,
1019 close_acct_seq_assign_id,
1020 close_acct_seq_version_id,
1021 close_acct_seq_value,
1022 completion_acct_seq_assign_id,
1023 completion_acct_seq_version_id,
1024 completion_acct_seq_value,
1025 upg_valid_flag
1026 --upg_worker_id
1027 )
1028 VALUES
1029 (batch_id,
1030 222,
1031 222,
1032 'DEFAULT', --amb context code
1033 entity_id,
1034 event_id,
1035 override_event,
1036 xla_ae_headers_s.nextval,
1037 sob_id,
1038 gl_date,
1039 period_name,
1040 '', --reference date global acct eng
1041 'A', --balance type Actual
1042 category, --category
1043 'Y', --gl transfer status
1044 gl_posted_date, --gl transfer date
1045 'F', --acct entry status code final
1046 'STANDARD', --acct entry type code
1047 '', --description TBD
1048 '', --budget version id
1049 '', --funds status code
1050 -- '', --encumbrance type id
1051 '', --completed date
1052 doc_seq_id,
1053 doc_seq_value,
1054 cat_code,
1055 '', --packet id
1056 '', --group id
1057 sysdate, --row who creation date
1058 -2005,
1059 sysdate,
1060 -2005,
1061 -2005,
1062 sysdate,
1063 -2005, --program id
1064 222,
1065 '', --request id
1066 '', --AX columns start
1067 '',
1068 '',
1069 '',
1070 '',
1071 '',
1072 '' --upg valid flag
1073 --''
1074 )
1075 select
1076 l_batch_id AS batch_id,
1077 event_id AS event_id,
1078 entity_id AS entity_id,
1079 override_event AS override_event,
1080 sob_id AS sob_id,
1081 gl_date AS gl_date,
1082 period_name AS period_name,
1083 category AS category,
1084 gl_posted_date AS gl_posted_date,
1085 doc_seq_id AS doc_seq_id,
1086 doc_seq_value AS doc_seq_value,
1087 cat_code AS cat_code
1088
1089 FROM
1090 (select /*+ ordered rowid(cr) use_nl(rec,crh,mccrh,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1091 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1092 hdr.ae_header_id ae_header_id,
1093 hdr.entity_id entity_id,
1094 hdr.event_id event_id,
1095 hdr.event_type_code override_event,
1096 hdr.accounting_date gl_date,
1097 hdr.period_name period_name,
1098 hdr.je_category_name category,
1099 hdr.gl_transfer_date gl_posted_date,
1100 hdr.doc_sequence_id doc_seq_id,
1101 hdr.doc_sequence_value doc_seq_value,
1102 hdr.doc_category_code cat_code,
1103 dist.set_of_books_id sob_id
1104 --
1105 from ar_mc_cash_receipts cr,
1106 ar_cash_receipts_all rec,
1107 ar_cash_receipt_history_all crh,
1108 xla_upgrade_dates gps,
1109 ar_mc_cash_receipt_hist mccrh,
1110 ar_mc_distributions_all dist,
1111 xla_distribution_links dl,
1112 xla_ae_headers hdr
1113 --
1114 where cr.rowid >= l_start_rowid
1115 and cr.rowid <= l_end_rowid
1116 --
1117 and rec.cash_receipt_id = cr.cash_receipt_id
1118 and NVL(rec.ax_accounted_flag,'N') = 'N'
1119 --
1120 and crh.cash_receipt_id = cr.cash_receipt_id
1121 and crh.postable_flag = 'Y'
1122 --
1123 and trunc(crh.gl_date) between gps.start_date and gps.end_date
1124 and gps.ledger_id = cr.set_of_books_id
1125 --
1126 and mccrh.cash_receipt_history_id = crh.cash_receipt_history_id
1127 and mccrh.posting_control_id <> -3
1128 and mccrh.set_of_books_id = cr.set_of_books_id
1129 --
1130 and dist.source_id = crh.cash_receipt_history_id
1131 and dist.source_table = 'CRH'
1132 and dist.set_of_books_id = mccrh.set_of_books_id
1133 --
1134 and dl.source_distribution_id_num_1 = dist.line_id
1135 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1136 and dl.application_id = 222
1137 and dl.upg_batch_id = l_batch_id
1138 --
1139 and hdr.ae_header_id = dl.ae_header_id
1140 and hdr.application_id = 222
1141 and hdr.upg_batch_id = l_batch_id
1142 and hdr.ledger_id = rec.set_of_books_id
1143 --
1144 group by
1145 hdr.ae_header_id,
1146 hdr.entity_id,
1147 hdr.event_id,
1148 hdr.event_type_code,
1149 hdr.accounting_date,
1150 hdr.period_name,
1151 hdr.je_category_name,
1152 hdr.gl_transfer_date,
1153 hdr.doc_sequence_id,
1154 hdr.doc_sequence_value,
1155 hdr.doc_category_code,
1156 dist.set_of_books_id
1157
1158 UNION /* Receipt applications */
1159 select /*+ ordered rowid(cr) use_nl(rec,ra,app,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1160 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1161 hdr.ae_header_id ae_header_id,
1162 hdr.entity_id entity_id,
1163 hdr.event_id event_id,
1164 hdr.event_type_code override_event,
1165 hdr.accounting_date gl_date,
1166 hdr.period_name period_name,
1167 hdr.je_category_name category,
1168 hdr.gl_transfer_date gl_posted_date,
1169 hdr.doc_sequence_id doc_seq_id,
1170 hdr.doc_sequence_value doc_seq_value,
1171 hdr.doc_category_code cat_code,
1172 dist.set_of_books_id sob_id
1173 --
1174 from ar_mc_cash_receipts cr,
1175 ar_cash_receipts_all rec,
1176 ar_receivable_applications_all ra,
1177 xla_upgrade_dates gps,
1178 ar_mc_receivable_apps app,
1179 ar_mc_distributions_all dist,
1180 xla_distribution_links dl,
1181 xla_ae_headers hdr
1182 --
1183 where cr.rowid >= l_start_rowid
1184 and cr.rowid <= l_end_rowid
1185 --
1186 and rec.cash_receipt_id = cr.cash_receipt_id
1187 and NVL(rec.ax_accounted_flag,'N') = 'N'
1188 --
1189 and ra.cash_receipt_id = cr.cash_receipt_id
1190 --
1191 and trunc(ra.gl_date) between gps.start_date and gps.end_date
1192 and gps.ledger_id = cr.set_of_books_id
1193 --
1194 and app.receivable_application_id = ra.receivable_application_id
1195 and app.posting_control_id <> -3
1196 and app.set_of_books_id = cr.set_of_books_id
1197 --
1198 and dist.source_id = ra.receivable_application_id
1199 and dist.source_table = 'RA'
1200 and dist.set_of_books_id = app.set_of_books_id
1201 --
1202 and dl.source_distribution_id_num_1 = dist.line_id
1203 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1204 and dl.application_id = 222
1205 and dl.upg_batch_id = l_batch_id
1206 --
1207 and hdr.ae_header_id = dl.ae_header_id
1208 and hdr.application_id = 222
1209 and hdr.upg_batch_id = l_batch_id
1210 and hdr.ledger_id = rec.set_of_books_id
1211 --
1212 group by
1213 hdr.ae_header_id,
1214 hdr.entity_id,
1215 hdr.event_id,
1216 hdr.event_type_code,
1217 hdr.accounting_date,
1218 hdr.period_name,
1219 hdr.je_category_name,
1220 hdr.gl_transfer_date,
1221 hdr.doc_sequence_id,
1222 hdr.doc_sequence_value,
1223 hdr.doc_category_code,
1224 dist.set_of_books_id
1225
1226 UNION /* Misc Receipt */
1227 select /*+ ordered rowid(cr) use_nl(rec,mcd,mcmcd,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1228 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1229 hdr.ae_header_id ae_header_id,
1230 hdr.entity_id entity_id,
1231 hdr.event_id event_id,
1232 hdr.event_type_code override_event,
1233 hdr.accounting_date gl_date,
1234 hdr.period_name period_name,
1235 hdr.je_category_name category,
1236 hdr.gl_transfer_date gl_posted_date,
1237 hdr.doc_sequence_id doc_seq_id,
1238 hdr.doc_sequence_value doc_seq_value,
1239 hdr.doc_category_code cat_code,
1240 dist.set_of_books_id sob_id
1241 --
1242 from ar_mc_cash_receipts cr,
1243 ar_cash_receipts_all rec,
1244 ar_misc_cash_distributions_all mcd,
1245 xla_upgrade_dates gps,
1246 ar_mc_misc_cash_dists mcmcd,
1247 ar_mc_distributions_all dist,
1248 xla_distribution_links dl,
1249 xla_ae_headers hdr
1250 --
1251 where cr.rowid >= l_start_rowid
1252 and cr.rowid <= l_end_rowid
1253 --
1254 and rec.cash_receipt_id = cr.cash_receipt_id
1255 and NVL(rec.ax_accounted_flag,'N') = 'N'
1256 --
1257 and mcd.cash_receipt_id = cr.cash_receipt_id
1258 --
1259 and trunc(mcd.gl_date) between gps.start_date and gps.end_date
1260 and gps.ledger_id = cr.set_of_books_id
1261 --
1262 and mcmcd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
1263 and mcmcd.posting_control_id <> -3
1264 and mcmcd.set_of_books_id = cr.set_of_books_id
1265 --
1266 and dist.source_id = mcd.misc_cash_distribution_id
1267 and dist.source_table = 'MCD'
1268 and dist.set_of_books_id = mcmcd.set_of_books_id
1269 --
1270 and dl.source_distribution_id_num_1 = dist.line_id
1271 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1272 and dl.application_id = 222
1273 and dl.upg_batch_id = l_batch_id
1274 --
1275 and hdr.ae_header_id = dl.ae_header_id
1276 and hdr.application_id = 222
1277 and hdr.upg_batch_id = l_batch_id
1278 and hdr.ledger_id = rec.set_of_books_id
1279 --
1280 group by
1281 hdr.ae_header_id,
1282 hdr.entity_id,
1283 hdr.event_id,
1284 hdr.event_type_code,
1285 hdr.accounting_date,
1286 hdr.period_name,
1287 hdr.je_category_name,
1288 hdr.gl_transfer_date,
1289 hdr.doc_sequence_id,
1290 hdr.doc_sequence_value,
1291 hdr.doc_category_code,
1292 dist.set_of_books_id
1293 );
1294 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1295
1296 END IF; --NVL(l_entity_type,'H') = 'H'
1297
1298 IF NVL(l_entity_type,'L') = 'L' THEN
1299 --------------------------------------------
1300 -- Inserting lines and distribution links --
1301 --------------------------------------------
1302
1303 INSERT ALL
1304 WHEN 1 = 1 THEN
1305 INTO XLA_AE_LINES
1306 (upg_batch_id,
1307 ae_header_id,
1308 ae_line_num,
1309 application_id,
1310 code_combination_id,
1311 gl_transfer_mode_code,
1312 accounted_dr,
1313 accounted_cr,
1314 currency_code,
1315 currency_conversion_date,
1316 currency_conversion_rate,
1317 currency_conversion_type,
1318 entered_dr,
1319 entered_cr,
1320 description,
1321 accounting_class_code,
1322 gl_sl_link_id,
1323 gl_sl_link_table,
1324 party_type_code,
1325 party_id,
1326 party_site_id,
1327 statistical_amount,
1328 ussgl_transaction_code,
1329 jgzz_recon_ref,
1330 control_balance_flag,
1331 analytical_balance_flag,
1332 creation_date,
1333 created_by,
1334 last_update_date,
1335 last_updated_by,
1336 last_update_login,
1337 program_update_date,
1338 program_id,
1339 program_application_id,
1340 request_id,
1341 gain_or_loss_flag,
1342 accounting_date,
1343 ledger_id
1344 )
1345 VALUES
1346 ( batch_id,
1347 header_id,
1348 line_num,
1349 222,
1350 code_combination_id,
1351 'D', --gl transfer mode Summary or detail
1352 acctd_amount_dr,
1353 acctd_amount_cr,
1354 currency_code,
1355 exchange_date,
1356 exchange_rate,
1357 exchange_type,
1358 amount_dr,
1359 amount_cr,
1360 '', --description TBD
1361 nvl(account_class,'XXXX'), --accounting class code
1362 xla_gl_sl_link_id_s.nextval, --gl sl link id
1363 'XLAJEL', --gl sl link table
1364 DECODE(third_party_id, NULL, NULL, 'C'), --party type code
1365 third_party_id, --party id
1366 third_party_sub_id, --third party site
1367 '', --statistical amount
1368 '', --ussgl trx code
1369 '', --jgzz recon ref
1370 '', --control balance flag
1371 '', --analytical balance
1372 sysdate, --row who columns
1373 -2005,
1374 sysdate,
1375 -2005,
1376 -2005,
1377 sysdate,
1378 -2005, --program id
1379 222,
1380 '', --request id
1381 gain_loss_flag,
1382 accounting_date,
1383 ledger_id)
1384 WHEN 1 = 1 THEN
1385 INTO XLA_DISTRIBUTION_LINKS
1386 (APPLICATION_ID,
1387 EVENT_ID,
1388 AE_HEADER_ID,
1389 AE_LINE_NUM,
1390 ACCOUNTING_LINE_CODE,
1391 ACCOUNTING_LINE_TYPE_CODE,
1392 REF_AE_HEADER_ID,
1393 -- REF_AE_LINE_NUM,
1394 SOURCE_DISTRIBUTION_TYPE,
1395 SOURCE_DISTRIBUTION_ID_CHAR_1,
1396 SOURCE_DISTRIBUTION_ID_CHAR_2,
1397 SOURCE_DISTRIBUTION_ID_CHAR_3,
1398 SOURCE_DISTRIBUTION_ID_CHAR_4,
1399 SOURCE_DISTRIBUTION_ID_CHAR_5,
1400 SOURCE_DISTRIBUTION_ID_NUM_1,
1401 SOURCE_DISTRIBUTION_ID_NUM_2,
1402 SOURCE_DISTRIBUTION_ID_NUM_3,
1403 SOURCE_DISTRIBUTION_ID_NUM_4,
1404 SOURCE_DISTRIBUTION_ID_NUM_5,
1405 UNROUNDED_ENTERED_DR,
1406 UNROUNDED_ENTERED_CR,
1407 UNROUNDED_ACCOUNTED_DR,
1408 UNROUNDED_ACCOUNTED_CR,
1409 MERGE_DUPLICATE_CODE,
1410 TAX_LINE_REF_ID,
1411 TAX_SUMMARY_LINE_REF_ID,
1412 TAX_REC_NREC_DIST_REF_ID,
1413 STATISTICAL_AMOUNT,
1414 TEMP_LINE_NUM,
1415 EVENT_TYPE_CODE,
1416 EVENT_CLASS_CODE,
1417 REF_EVENT_ID,
1418 UPG_BATCH_ID)
1419 VALUES
1420 (222,
1421 event_id,
1422 header_id,
1423 line_num,
1424 account_class,
1425 'C', --accounting line code customer
1426 ae_header_id, --reference header id
1427 -- '', --reference line number
1428 source_table,
1429 '', --src dist id char
1430 '',
1431 '',
1432 '',
1433 '',
1434 line_id, --src dist id num
1435 '',
1436 '',
1437 '',
1438 '',
1439 amount_dr,
1440 amount_cr,
1441 acctd_amount_dr,
1442 acctd_amount_cr,
1443 'N', --merge dup code
1444 tax_line_id, --tax_line_ref_id
1445 '', --tax_summary_line_ref_id
1446 '', --tax_rec_nrec_dist_ref_id
1447 '', --statistical amount
1448 line_num, --temp_line_num
1449 event_type_code, --event_type_code
1450 event_class_code, --event class code
1451 '', --ref_event_id,
1452 batch_id) --upgrade batch id
1453 select
1454 l_batch_id AS batch_id,
1455 header_id AS header_id,
1456 ae_header_id AS ae_header_id,
1457 line_id AS line_id,
1458 event_id AS event_id,
1459 account_class AS account_class,
1460 source_table AS source_table,
1461 code_combination_id AS code_combination_id,
1462 amount_dr AS amount_dr,
1463 amount_cr AS amount_cr,
1464 acctd_amount_dr AS acctd_amount_dr,
1465 acctd_amount_cr AS acctd_amount_cr,
1466 nvl(currency_code,'XXXX') AS currency_code,
1467 third_party_id AS third_party_id,
1468 third_party_sub_id AS third_party_sub_id,
1469 exchange_date AS exchange_date,
1470 exchange_rate AS exchange_rate,
1471 exchange_type AS exchange_type,
1472 tax_line_id AS tax_line_id,
1473 sob_id AS sob_id,
1474 gain_loss_flag AS gain_loss_flag,
1475 event_type_code AS event_type_code,
1476 event_class_code AS event_class_code,
1477 accounting_date AS accounting_date,
1478 ledger_id AS ledger_id,
1479 RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
1480 ORDER BY line_id, ln_order) AS line_num
1481 FROM
1482 (select /*+ ordered rowid(cr) use_nl(rec,crh,mccrh,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
1483 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
1484 hdr.ae_header_id ae_header_id,
1485 hdr1.ae_header_id header_id,
1486 hdr.event_id event_id,
1487 dist.set_of_books_id sob_id,
1488 dist.source_type account_class,
1489 'AR_DISTRIBUTIONS_ALL' source_table,
1490 dist.code_combination_id code_combination_id,
1491 dist.amount_dr amount_dr,
1492 dist.amount_cr amount_cr,
1493 dist.acctd_amount_dr acctd_amount_dr,
1494 dist.acctd_amount_cr acctd_amount_cr,
1495 dist.currency_code currency_code,
1496 dist.third_party_id third_party_id,
1497 dist.third_party_sub_id third_party_sub_id,
1498 dist.currency_conversion_date exchange_date,
1499 dist.currency_conversion_rate exchange_rate,
1500 dist.currency_conversion_type exchange_type,
1501 dist.line_id line_id,
1502 null tax_line_id,
1503 'N' gain_loss_flag,
1504 dl.event_type_code event_type_code,
1505 dl.event_class_code event_class_code,
1506 hdr.accounting_date accounting_date,
1507 hdr1.ledger_id ledger_id,
1508 1 ln_order
1509 --
1510 from ar_mc_cash_receipts cr,
1511 ar_cash_receipts_all rec,
1512 ar_cash_receipt_history_all crh,
1513 xla_upgrade_dates gps,
1514 ar_mc_cash_receipt_hist mccrh,
1515 ar_mc_distributions_all dist,
1516 xla_distribution_links dl,
1517 xla_ae_headers hdr,
1518 xla_ae_headers hdr1
1519 --
1520 where cr.rowid >= l_start_rowid
1521 and cr.rowid <= l_end_rowid
1522 --
1523 and rec.cash_receipt_id = cr.cash_receipt_id
1524 and NVL(rec.ax_accounted_flag,'N') = 'N'
1525 --
1526 and crh.cash_receipt_id = cr.cash_receipt_id
1527 and crh.postable_flag = 'Y'
1528 --
1529 and trunc(crh.gl_date) between gps.start_date and gps.end_date
1530 and gps.ledger_id = cr.set_of_books_id
1531 --
1532 and mccrh.cash_receipt_history_id = crh.cash_receipt_history_id
1533 and mccrh.posting_control_id <> -3
1534 and mccrh.set_of_books_id = cr.set_of_books_id
1535 --
1536 and dist.source_id = crh.cash_receipt_history_id
1537 and dist.source_table = 'CRH'
1538 and dist.set_of_books_id = mccrh.set_of_books_id
1539 --
1540 and dl.source_distribution_id_num_1 = dist.line_id
1541 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1542 and dl.application_id = 222
1543 and dl.upg_batch_id = l_batch_id
1544 --
1545 and hdr.ae_header_id = dl.ae_header_id
1546 and hdr.application_id = 222
1547 and hdr.upg_batch_id = l_batch_id
1548 and hdr.ledger_id = rec.set_of_books_id
1549 --
1550 and hdr1.application_id = 222
1551 and hdr1.upg_batch_id = l_batch_id
1552 and hdr1.ae_header_id <> hdr.ae_header_id
1553 and hdr1.ledger_id = dist.set_of_books_id
1554 and hdr1.entity_id = hdr.entity_id
1555 and hdr1.event_id = hdr.event_id
1556 and hdr1.event_type_code = hdr.event_type_code
1557 and hdr1.accounting_date = hdr.accounting_date
1558 and hdr1.period_name = hdr.period_name
1559 and hdr1.je_category_name = hdr.je_category_name
1560 and hdr1.gl_transfer_date = hdr.gl_transfer_date
1561 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
1562 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
1563 -- and hdr1.doc_category_code = hdr.doc_category_code
1564
1565 UNION /* Receipt applications */
1566 select /*+ ordered rowid(cr) use_nl(rec,ra,app,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
1567 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
1568 hdr.ae_header_id ae_header_id,
1569 hdr1.ae_header_id header_id,
1570 hdr.event_id event_id,
1571 dist.set_of_books_id sob_id,
1572 dist.source_type account_class,
1573 'AR_DISTRIBUTIONS_ALL' source_table,
1574 dist.code_combination_id code_combination_id,
1575 dist.amount_dr amount_dr,
1576 dist.amount_cr amount_cr,
1577 dist.acctd_amount_dr acctd_amount_dr,
1578 dist.acctd_amount_cr acctd_amount_cr,
1579 dist.currency_code currency_code,
1580 dist.third_party_id third_party_id,
1581 dist.third_party_sub_id third_party_sub_id,
1582 dist.currency_conversion_date exchange_date,
1583 dist.currency_conversion_rate exchange_rate,
1584 dist.currency_conversion_type exchange_type,
1585 dist.line_id line_id,
1586 null tax_line_id,
1587 decode(dist.source_type,
1588 'EXCH_GAIN','Y',
1589 'EXCH_LOSS','Y',
1590 'CURR_ROUND','Y',
1591 'N') gain_loss_flag,
1592 dl.event_type_code event_type_code,
1593 dl.event_class_code event_class_code,
1594 hdr.accounting_date accounting_date,
1595 hdr1.ledger_id ledger_id,
1596 2 ln_order
1597 --
1598 from ar_mc_cash_receipts cr,
1599 ar_cash_receipts_all rec,
1600 ar_receivable_applications_all ra,
1601 xla_upgrade_dates gps,
1602 ar_mc_receivable_apps app,
1603 ar_mc_distributions_all dist,
1604 xla_distribution_links dl,
1605 xla_ae_headers hdr,
1606 xla_ae_headers hdr1
1607 --
1608 where cr.rowid >= l_start_rowid
1609 and cr.rowid <= l_end_rowid
1610 --
1611 and rec.cash_receipt_id = cr.cash_receipt_id
1612 and NVL(rec.ax_accounted_flag,'N') = 'N'
1613 --
1614 and ra.cash_receipt_id = cr.cash_receipt_id
1615 --
1616 and trunc(ra.gl_date) between gps.start_date and gps.end_date
1617 and gps.ledger_id = cr.set_of_books_id
1618 --
1619 and app.receivable_application_id = ra.receivable_application_id
1620 and app.posting_control_id <> -3
1621 and app.set_of_books_id = cr.set_of_books_id
1622 --
1623 and dist.source_id = ra.receivable_application_id
1624 and dist.source_table = 'RA'
1625 and dist.set_of_books_id = app.set_of_books_id
1626 --
1627 and dl.source_distribution_id_num_1 = dist.line_id
1628 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1629 and dl.application_id = 222
1630 and dl.upg_batch_id = l_batch_id
1631 --
1632 and hdr.ae_header_id = dl.ae_header_id
1633 and hdr.application_id = 222
1634 and hdr.upg_batch_id = l_batch_id
1635 and hdr.ledger_id = rec.set_of_books_id
1636 --
1637 and hdr1.application_id = 222
1638 and hdr1.upg_batch_id = l_batch_id
1639 and hdr1.ae_header_id <> hdr.ae_header_id
1640 and hdr1.ledger_id = dist.set_of_books_id
1641 and hdr1.entity_id = hdr.entity_id
1642 and hdr1.event_id = hdr.event_id
1643 and hdr1.event_type_code = hdr.event_type_code
1644 and hdr1.accounting_date = hdr.accounting_date
1645 and hdr1.period_name = hdr.period_name
1646 and hdr1.je_category_name = hdr.je_category_name
1647 and hdr1.gl_transfer_date = hdr.gl_transfer_date
1648 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
1649 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
1650 -- and hdr1.doc_category_code = hdr.doc_category_code
1651
1652 UNION /* Misc Receipt */
1653 select /*+ ordered rowid(cr) use_nl(rec,mcd,mcmcd,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
1654 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
1655 hdr.ae_header_id ae_header_id,
1656 hdr1.ae_header_id header_id,
1657 hdr.event_id event_id,
1658 dist.set_of_books_id sob_id,
1659 dist.source_type account_class,
1660 'AR_DISTRIBUTIONS_ALL' source_table,
1661 dist.code_combination_id code_combination_id,
1662 dist.amount_dr amount_dr,
1663 dist.amount_cr amount_cr,
1664 dist.acctd_amount_dr acctd_amount_dr,
1665 dist.acctd_amount_cr acctd_amount_cr,
1666 dist.currency_code currency_code,
1667 dist.third_party_id third_party_id,
1668 dist.third_party_sub_id third_party_sub_id,
1669 dist.currency_conversion_date exchange_date,
1670 dist.currency_conversion_rate exchange_rate,
1671 dist.currency_conversion_type exchange_type,
1672 dist.line_id line_id,
1673 null tax_line_id,
1674 'N' gain_loss_flag,
1675 dl.event_type_code event_type_code,
1676 dl.event_class_code event_class_code,
1677 hdr.accounting_date accounting_date,
1678 hdr.ledger_id ledger_id,
1679 3 ln_order
1680 --
1681 from ar_mc_cash_receipts cr,
1682 ar_cash_receipts_all rec,
1683 ar_misc_cash_distributions_all mcd,
1684 xla_upgrade_dates gps,
1685 ar_mc_misc_cash_dists mcmcd,
1686 ar_mc_distributions_all dist,
1687 xla_distribution_links dl,
1688 xla_ae_headers hdr,
1689 xla_ae_headers hdr1
1690 --
1691 where cr.rowid >= l_start_rowid
1692 and cr.rowid <= l_end_rowid
1693 --
1694 and rec.cash_receipt_id = cr.cash_receipt_id
1695 and NVL(rec.ax_accounted_flag,'N') = 'N'
1696 --
1697 and mcd.cash_receipt_id = cr.cash_receipt_id
1698 --
1699 and trunc(mcd.gl_date) between gps.start_date and gps.end_date
1700 and gps.ledger_id = cr.set_of_books_id
1701 --
1702 and mcmcd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
1703 and mcmcd.posting_control_id <> -3
1704 and mcmcd.set_of_books_id = cr.set_of_books_id
1705 --
1706 and dist.source_id = mcd.misc_cash_distribution_id
1707 and dist.source_table = 'MCD'
1708 and dist.set_of_books_id = mcmcd.set_of_books_id
1709 --
1710 and dl.source_distribution_id_num_1 = dist.line_id
1711 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1712 and dl.application_id = 222
1713 and dl.upg_batch_id = l_batch_id
1714 --
1715 and hdr.ae_header_id = dl.ae_header_id
1716 and hdr.application_id = 222
1717 and hdr.upg_batch_id = l_batch_id
1718 and hdr.ledger_id = rec.set_of_books_id
1719 --
1720 and hdr1.application_id = 222
1721 and hdr1.upg_batch_id = l_batch_id
1722 and hdr1.ae_header_id <> hdr.ae_header_id
1723 and hdr1.ledger_id = dist.set_of_books_id
1724 and hdr1.entity_id = hdr.entity_id
1725 and hdr1.event_id = hdr.event_id
1726 and hdr1.event_type_code = hdr.event_type_code
1727 and hdr1.accounting_date = hdr.accounting_date
1728 and hdr1.period_name = hdr.period_name
1729 and hdr1.je_category_name = hdr.je_category_name
1730 and hdr1.gl_transfer_date = hdr.gl_transfer_date
1731 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
1732 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
1733 -- and hdr1.doc_category_code = hdr.doc_category_code
1734 );
1735
1736 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1737
1738 UPGRADE_MC_GAIN_LOSS(
1739 l_start_rowid,
1740 l_end_rowid,
1741 l_table_name,
1742 l_batch_id);
1743
1744 END IF; --NVL(l_entity_type,'L') = 'L'
1745
1746 ad_parallel_updates_pkg.processed_rowid_range(
1747 l_rows_processed,
1748 l_end_rowid);
1749
1750 commit;
1751
1752 ad_parallel_updates_pkg.get_rowid_range(
1753 l_start_rowid,
1754 l_end_rowid,
1755 l_any_rows_to_process,
1756 l_batch_size,
1757 FALSE);
1758
1759 l_rows_processed := 0 ;
1760
1761 END LOOP ; /* end of WHILE loop */
1762
1763 EXCEPTION
1764 WHEN NO_DATA_FOUND THEN
1765 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_receipts');
1766 RAISE;
1767
1768 WHEN OTHERS THEN
1769 --arp_standard.debug('OTHERS EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_receipts');
1770 RAISE;
1771
1772 END UPGRADE_MC_RECEIPTS;
1773
1774 /*========================================================================
1775 | PUBLIC PROCEDURE UPGRADE_MC_ADJUSTMENTS
1776 |
1777 | DESCRIPTION
1778 | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
1779 | XLA_DISTRIBUTION_LINKS for records related to adjustments.
1780 |
1781 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1782 |
1783 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1784 |
1785 | PARAMETERS
1786 |
1787 | KNOWN ISSUES
1788 |
1789 | NOTES
1790 |
1791 | MODIFICATION HISTORY
1792 | Date Author Description of Changes
1793 | 03-JUL-2005 JVARKEY Created
1794 | 30-AUG-2005 JVARKEY Modified the flow
1795 | 20-SEP-2005 JVARKEY Detached the insert into
1796 | xla_ae_headers into seperate
1797 | insert statement
1798 *=======================================================================*/
1799
1800 PROCEDURE UPGRADE_MC_ADJUSTMENTS(
1801 l_table_owner IN VARCHAR2,
1802 l_table_name IN VARCHAR2,
1803 l_script_name IN VARCHAR2,
1804 l_worker_id IN VARCHAR2,
1805 l_num_workers IN VARCHAR2,
1806 l_batch_size IN VARCHAR2,
1807 l_batch_id IN NUMBER,
1808 l_action_flag IN VARCHAR2,
1809 l_entity_type IN VARCHAR2 DEFAULT NULL) IS
1810
1811 l_start_rowid rowid;
1812 l_end_rowid rowid;
1813 l_any_rows_to_process boolean;
1814 l_rows_processed number := 0;
1815
1816 BEGIN
1817
1818 /* ------ Initialize the rowid ranges ------ */
1819 ad_parallel_updates_pkg.initialize_rowid_range(
1820 ad_parallel_updates_pkg.ROWID_RANGE,
1821 l_table_owner,
1822 l_table_name,
1823 l_script_name,
1824 l_worker_id,
1825 l_num_workers,
1826 l_batch_size, 0);
1827
1828 /* ------ Get rowid ranges ------ */
1829 ad_parallel_updates_pkg.get_rowid_range(
1830 l_start_rowid,
1831 l_end_rowid,
1832 l_any_rows_to_process,
1833 l_batch_size,
1834 TRUE);
1835
1836 WHILE ( l_any_rows_to_process = TRUE )
1837 LOOP
1838
1839 l_rows_processed := 0;
1840
1841 IF NVL(l_entity_type,'H') = 'H' THEN
1842 -----------------------
1843 -- Inserting headers --
1844 -----------------------
1845
1846 INSERT ALL
1847 WHEN 1 = 1 THEN
1848 INTO XLA_AE_HEADERS
1849 (upg_batch_id,
1850 upg_source_application_id,
1851 application_id,
1852 amb_context_code,
1853 entity_id,
1854 event_id,
1855 event_type_code,
1856 ae_header_id,
1857 ledger_id,
1858 accounting_date,
1859 period_name,
1860 reference_date,
1861 balance_type_code,
1862 je_category_name,
1863 gl_transfer_status_code,
1864 gl_transfer_date,
1865 accounting_entry_status_code,
1866 accounting_entry_type_code,
1867 description,
1868 budget_version_id,
1869 funds_status_code,
1870 -- encumbrance_type_id,
1871 completed_date,
1872 doc_sequence_id,
1873 doc_sequence_value,
1874 doc_category_code,
1875 packet_id,
1876 group_id,
1877 creation_date,
1878 created_by,
1879 last_update_date,
1880 last_updated_by,
1881 last_update_login,
1882 program_update_date,
1883 program_id,
1884 program_application_id,
1885 request_id,
1886 close_acct_seq_assign_id,
1887 close_acct_seq_version_id,
1888 close_acct_seq_value,
1889 completion_acct_seq_assign_id,
1890 completion_acct_seq_version_id,
1891 completion_acct_seq_value,
1892 upg_valid_flag
1893 --upg_worker_id
1894 )
1895 VALUES
1896 (batch_id,
1897 222,
1898 222,
1899 'DEFAULT', --amb context code
1900 entity_id,
1901 event_id,
1902 override_event,
1903 xla_ae_headers_s.nextval,
1904 sob_id,
1905 gl_date,
1906 period_name,
1907 '', --reference date global acct eng
1908 'A', --balance type Actual
1909 category, --category
1910 'Y', --gl transfer status
1911 gl_posted_date, --gl transfer date
1912 'F', --acct entry status code final
1913 'STANDARD', --acct entry type code
1914 '', --description TBD
1915 '', --budget version id
1916 '', --funds status code
1917 -- '', --encumbrance type id
1918 '', --completed date
1919 doc_seq_id,
1920 doc_seq_value,
1921 cat_code,
1922 '', --packet id
1923 '', --group id
1924 sysdate, --row who creation date
1925 -2005,
1926 sysdate,
1927 -2005,
1928 -2005,
1929 sysdate,
1930 -2005, --program id
1931 222,
1932 '', --request id
1933 '', --AX columns start
1934 '',
1935 '',
1936 '',
1937 '',
1938 '',
1939 '' --upg valid flag
1940 --''
1941 )
1942 select
1943 l_batch_id AS batch_id,
1944 event_id AS event_id,
1945 entity_id AS entity_id,
1946 override_event AS override_event,
1947 sob_id AS sob_id,
1948 gl_date AS gl_date,
1949 period_name AS period_name,
1950 category AS category,
1951 gl_posted_date AS gl_posted_date,
1952 doc_seq_id AS doc_seq_id,
1953 doc_seq_value AS doc_seq_value,
1954 cat_code AS cat_code
1955 FROM
1956 (select /*+ ordered rowid(adj) use_nl(adjt,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1957 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1958 hdr.ae_header_id ae_header_id,
1959 hdr.entity_id entity_id,
1960 hdr.event_id event_id,
1961 hdr.event_type_code override_event,
1962 hdr.accounting_date gl_date,
1963 hdr.period_name period_name,
1964 hdr.je_category_name category,
1965 hdr.gl_transfer_date gl_posted_date,
1966 hdr.doc_sequence_id doc_seq_id,
1967 hdr.doc_sequence_value doc_seq_value,
1968 hdr.doc_category_code cat_code,
1969 dist.set_of_books_id sob_id
1970 --
1971 from ar_mc_adjustments adj,
1972 ar_adjustments_all adjt,
1973 xla_upgrade_dates gps,
1974 ar_mc_distributions_all dist,
1975 xla_distribution_links dl,
1976 xla_ae_headers hdr
1977 --
1978 where adj.rowid >= l_start_rowid
1979 and adj.rowid <= l_end_rowid
1980 and adj.posting_control_id <> -3
1981 --
1982 and adjt.adjustment_id = adj.adjustment_id
1983 and NVL(adjt.ax_accounted_flag,'N') = 'N'
1984 --
1985 and trunc(adjt.gl_date) between gps.start_date and gps.end_date
1986 and gps.ledger_id = adj.set_of_books_id
1987 --
1988 and dist.source_id = adjt.adjustment_id
1989 and dist.source_table = 'ADJ'
1990 and dist.set_of_books_id = adj.set_of_books_id
1991 --
1992 and dl.source_distribution_id_num_1 = dist.line_id
1993 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1994 and dl.application_id = 222
1995 and dl.upg_batch_id = l_batch_id
1996 --
1997 and hdr.ae_header_id = dl.ae_header_id
1998 and hdr.application_id = 222
1999 and hdr.upg_batch_id = l_batch_id
2000 and hdr.ledger_id = adjt.set_of_books_id
2001 --
2002 group by
2003 hdr.ae_header_id,
2004 hdr.entity_id,
2005 hdr.event_id,
2006 hdr.event_type_code,
2007 hdr.accounting_date,
2008 hdr.period_name,
2009 hdr.je_category_name,
2010 hdr.gl_transfer_date,
2011 hdr.doc_sequence_id,
2012 hdr.doc_sequence_value,
2013 hdr.doc_category_code,
2014 dist.set_of_books_id
2015 );
2016 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2017
2018 END IF; --NVL(l_entity_type,'H') = 'H'
2019
2020 IF NVL(l_entity_type,'L') = 'L' THEN
2021 --------------------------------------------
2022 -- Inserting lines and distribution links --
2023 --------------------------------------------
2024
2025 INSERT ALL
2026 WHEN 1 = 1 THEN
2027 INTO XLA_AE_LINES
2028 (upg_batch_id,
2029 ae_header_id,
2030 ae_line_num,
2031 application_id,
2032 code_combination_id,
2033 gl_transfer_mode_code,
2034 accounted_dr,
2035 accounted_cr,
2036 currency_code,
2037 currency_conversion_date,
2038 currency_conversion_rate,
2039 currency_conversion_type,
2040 entered_dr,
2041 entered_cr,
2042 description,
2043 accounting_class_code,
2044 gl_sl_link_id,
2045 gl_sl_link_table,
2046 party_type_code,
2047 party_id,
2048 party_site_id,
2049 statistical_amount,
2050 ussgl_transaction_code,
2051 jgzz_recon_ref,
2052 control_balance_flag,
2053 analytical_balance_flag,
2054 creation_date,
2055 created_by,
2056 last_update_date,
2057 last_updated_by,
2058 last_update_login,
2059 program_update_date,
2060 program_id,
2061 program_application_id,
2062 request_id,
2063 gain_or_loss_flag,
2064 accounting_date,
2065 ledger_id
2066 )
2067 VALUES
2068 ( batch_id,
2069 header_id,
2070 line_num,
2071 222,
2072 code_combination_id,
2073 'D', --gl transfer mode Summary or detail
2074 acctd_amount_dr,
2075 acctd_amount_cr,
2076 currency_code,
2077 exchange_date,
2078 exchange_rate,
2079 exchange_type,
2080 amount_dr,
2081 amount_cr,
2082 '', --description TBD
2083 nvl(account_class,'XXXX'), --accounting class code
2084 xla_gl_sl_link_id_s.nextval, --gl sl link id
2085 'XLAJEL', --gl sl link table
2086 DECODE(third_party_id, NULL, NULL, 'C'), --party type code
2087 third_party_id, --party id
2088 third_party_sub_id, --third party site
2089 '', --statistical amount
2090 '', --ussgl trx code
2091 '', --jgzz recon ref
2092 '', --control balance flag
2093 '', --analytical balance
2094 sysdate, --row who columns
2095 -2005,
2096 sysdate,
2097 -2005,
2098 -2005,
2099 sysdate,
2100 -2005, --program id
2101 222,
2102 '', --request id
2103 'N',
2104 accounting_date,
2105 ledger_id)
2106 WHEN 1 = 1 THEN
2107 INTO XLA_DISTRIBUTION_LINKS
2108 (APPLICATION_ID,
2109 EVENT_ID,
2110 AE_HEADER_ID,
2111 AE_LINE_NUM,
2112 ACCOUNTING_LINE_CODE,
2113 ACCOUNTING_LINE_TYPE_CODE,
2114 REF_AE_HEADER_ID,
2115 -- REF_AE_LINE_NUM,
2116 SOURCE_DISTRIBUTION_TYPE,
2117 SOURCE_DISTRIBUTION_ID_CHAR_1,
2118 SOURCE_DISTRIBUTION_ID_CHAR_2,
2119 SOURCE_DISTRIBUTION_ID_CHAR_3,
2120 SOURCE_DISTRIBUTION_ID_CHAR_4,
2121 SOURCE_DISTRIBUTION_ID_CHAR_5,
2122 SOURCE_DISTRIBUTION_ID_NUM_1,
2123 SOURCE_DISTRIBUTION_ID_NUM_2,
2124 SOURCE_DISTRIBUTION_ID_NUM_3,
2125 SOURCE_DISTRIBUTION_ID_NUM_4,
2126 SOURCE_DISTRIBUTION_ID_NUM_5,
2127 UNROUNDED_ENTERED_DR,
2128 UNROUNDED_ENTERED_CR,
2129 UNROUNDED_ACCOUNTED_DR,
2130 UNROUNDED_ACCOUNTED_CR,
2131 MERGE_DUPLICATE_CODE,
2132 TAX_LINE_REF_ID,
2133 TAX_SUMMARY_LINE_REF_ID,
2134 TAX_REC_NREC_DIST_REF_ID,
2135 STATISTICAL_AMOUNT,
2136 TEMP_LINE_NUM,
2137 EVENT_TYPE_CODE,
2138 EVENT_CLASS_CODE,
2139 REF_EVENT_ID,
2140 UPG_BATCH_ID)
2141 VALUES
2142 (222,
2143 event_id,
2144 header_id,
2145 line_num,
2146 account_class,
2147 'C', --accounting line code customer
2148 ae_header_id, --reference header id
2149 -- '', --reference line number
2150 source_table,
2151 '', --src dist id char
2152 '',
2153 '',
2154 '',
2155 '',
2156 line_id, --src dist id num
2157 '',
2158 '',
2159 '',
2160 '',
2161 amount_dr,
2162 amount_cr,
2163 acctd_amount_dr,
2164 acctd_amount_cr,
2165 'N', --merge dup code
2166 tax_line_id, --tax_line_ref_id
2167 '', --tax_summary_line_ref_id
2168 '', --tax_rec_nrec_dist_ref_id
2169 '', --statistical amount
2170 line_num, --temp_line_num
2171 event_type_code, --event_type_code
2172 event_class_code, --event class code
2173 '', --ref_event_id,
2174 batch_id) --upgrade batch id
2175 select
2176 l_batch_id AS batch_id,
2177 header_id AS header_id,
2178 ae_header_id AS ae_header_id,
2179 line_id AS line_id,
2180 event_id AS event_id,
2181 account_class AS account_class,
2182 source_table AS source_table,
2183 code_combination_id AS code_combination_id,
2184 amount_dr AS amount_dr,
2185 amount_cr AS amount_cr,
2186 acctd_amount_dr AS acctd_amount_dr,
2187 acctd_amount_cr AS acctd_amount_cr,
2188 nvl(currency_code,'XXXX') AS currency_code,
2189 third_party_id AS third_party_id,
2190 third_party_sub_id AS third_party_sub_id,
2191 exchange_date AS exchange_date,
2192 exchange_rate AS exchange_rate,
2193 exchange_type AS exchange_type,
2194 tax_line_id AS tax_line_id,
2195 sob_id AS sob_id,
2196 event_type_code AS event_type_code,
2197 event_class_code AS event_class_code,
2198 accounting_date AS accounting_date,
2199 ledger_id AS ledger_id,
2200 RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
2201 ORDER BY line_id, ln_order) AS line_num
2202 FROM
2203 (select /*+ ordered rowid(adj) use_nl(adjt,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
2204 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
2205 hdr.ae_header_id ae_header_id,
2206 hdr1.ae_header_id header_id,
2207 hdr.event_id event_id,
2208 dist.set_of_books_id sob_id,
2209 dist.source_type account_class,
2210 'AR_DISTRIBUTIONS_ALL' source_table,
2211 dist.code_combination_id code_combination_id,
2212 dist.amount_dr amount_dr,
2213 dist.amount_cr amount_cr,
2214 dist.acctd_amount_dr acctd_amount_dr,
2215 dist.acctd_amount_cr acctd_amount_cr,
2216 dist.currency_code currency_code,
2217 dist.third_party_id third_party_id,
2218 dist.third_party_sub_id third_party_sub_id,
2219 dist.currency_conversion_date exchange_date,
2220 dist.currency_conversion_rate exchange_rate,
2221 dist.currency_conversion_type exchange_type,
2222 dist.line_id line_id,
2223 null tax_line_id,
2224 dl.event_type_code event_type_code,
2225 dl.event_class_code event_class_code,
2226 hdr.accounting_date accounting_date,
2227 hdr1.ledger_id ledger_id,
2228 1 ln_order
2229 --
2230 from ar_mc_adjustments adj,
2231 ar_adjustments_all adjt,
2232 xla_upgrade_dates gps,
2233 ar_mc_distributions_all dist,
2234 xla_distribution_links dl,
2235 xla_ae_headers hdr,
2236 xla_ae_headers hdr1
2237 --
2238 where adj.rowid >= l_start_rowid
2239 and adj.rowid <= l_end_rowid
2240 and adj.posting_control_id <> -3
2241 --
2242 and adjt.adjustment_id = adj.adjustment_id
2243 and NVL(adjt.ax_accounted_flag,'N') = 'N'
2244 --
2245 and trunc(adjt.gl_date) between gps.start_date and gps.end_date
2246 and gps.ledger_id = adj.set_of_books_id
2247 --
2248 and dist.source_id = adjt.adjustment_id
2249 and dist.source_table = 'ADJ'
2250 and dist.set_of_books_id = adj.set_of_books_id
2251 --
2252 and dl.source_distribution_id_num_1 = dist.line_id
2253 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2254 and dl.application_id = 222
2255 and dl.upg_batch_id = l_batch_id
2256 --
2257 and hdr.ae_header_id = dl.ae_header_id
2258 and hdr.application_id = 222
2259 and hdr.upg_batch_id = l_batch_id
2260 and hdr.ledger_id = adjt.set_of_books_id
2261 --
2262 and hdr1.application_id = 222
2263 and hdr1.upg_batch_id = l_batch_id
2264 and hdr1.ae_header_id <> hdr.ae_header_id
2265 and hdr1.ledger_id = dist.set_of_books_id
2266 and hdr1.entity_id = hdr.entity_id
2267 and hdr1.event_id = hdr.event_id
2268 and hdr1.event_type_code = hdr.event_type_code
2269 and hdr1.accounting_date = hdr.accounting_date
2270 and hdr1.period_name = hdr.period_name
2271 and hdr1.je_category_name = hdr.je_category_name
2272 and hdr1.gl_transfer_date = hdr.gl_transfer_date
2273 -- and hdr1.doc_sequence_id = hdr.doc_sequence_id
2274 -- and hdr1.doc_sequence_value = hdr.doc_sequence_value
2275 -- and hdr1.doc_category_code = hdr.doc_category_code
2276 );
2277
2278 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2279
2280 END IF; --NVL(l_entity_type,'L') = 'L'
2281
2282 ad_parallel_updates_pkg.processed_rowid_range(
2283 l_rows_processed,
2284 l_end_rowid);
2285
2286 commit;
2287
2288 ad_parallel_updates_pkg.get_rowid_range(
2289 l_start_rowid,
2290 l_end_rowid,
2291 l_any_rows_to_process,
2292 l_batch_size,
2293 FALSE);
2294
2295 l_rows_processed := 0 ;
2296
2297 END LOOP ; /* end of WHILE loop */
2298
2299 EXCEPTION
2300 WHEN NO_DATA_FOUND THEN
2301 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_adjustments');
2302 RAISE;
2303
2304 WHEN OTHERS THEN
2305 --arp_standard.debug('OTHERS EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_adjustments');
2306 RAISE;
2307
2308 END UPGRADE_MC_ADJUSTMENTS;
2309
2310 /*========================================================================
2311 | PRIVATE PROCEDURE UPGRADE_MC_GAIN_LOSS
2312 |
2313 | DESCRIPTION
2314 | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
2315 | XLA_DISTRIBUTION_LINKS for records related to exchange_gain/loss
2316 | which doesnt have any parent record in AR and exist in MRC.
2317 |
2318 | CALLED FROM PROCEDURES/FUNCTIONS
2319 | UPGRADE_MC_TRANSACTIONS
2320 | UPGRADE_MC_RECEIPTS
2321 |
2322 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2323 |
2324 | PARAMETERS
2325 |
2326 | KNOWN ISSUES
2327 |
2328 | NOTES
2329 |
2330 | MODIFICATION HISTORY
2331 | Date Author Description of Changes
2332 | 01-SEP-2005 JVARKEY Created
2333 | 08-SEP-2005 JVARKEY Changed the query for mass insert
2334 *=======================================================================*/
2335
2336 PROCEDURE UPGRADE_MC_GAIN_LOSS(
2337 l_start_rowid IN ROWID,
2338 l_end_rowid IN ROWID,
2339 l_table_name IN VARCHAR2,
2340 l_batch_id IN NUMBER) IS
2341
2342 l_rows_processed number := 0;
2343
2344 BEGIN
2345
2346 l_rows_processed := 0;
2347
2348 IF (l_table_name = 'AR_MC_CASH_RECEIPTS') THEN
2349
2350 INSERT ALL
2351 WHEN 1 = 1 THEN
2352 INTO XLA_AE_LINES
2353 (upg_batch_id,
2354 ae_header_id,
2355 ae_line_num,
2356 application_id,
2357 code_combination_id,
2358 gl_transfer_mode_code,
2359 accounted_dr,
2360 accounted_cr,
2361 currency_code,
2362 currency_conversion_date,
2363 currency_conversion_rate,
2364 currency_conversion_type,
2365 entered_dr,
2366 entered_cr,
2367 description,
2368 accounting_class_code,
2369 gl_sl_link_id,
2370 gl_sl_link_table,
2371 party_type_code,
2372 party_id,
2373 party_site_id,
2374 statistical_amount,
2375 ussgl_transaction_code,
2376 jgzz_recon_ref,
2377 control_balance_flag,
2378 analytical_balance_flag,
2379 creation_date,
2380 created_by,
2381 last_update_date,
2382 last_updated_by,
2383 last_update_login,
2384 program_update_date,
2385 program_id,
2386 program_application_id,
2387 request_id,
2388 gain_or_loss_flag,
2389 accounting_date,
2390 ledger_id
2391 )
2392 VALUES
2393 ( batch_id,
2394 ae_header_id,
2395 line_num+max_line_num,
2396 222,
2397 code_combination_id,
2398 'D', --gl transfer mode Summary or detail
2399 acctd_amount_dr,
2400 acctd_amount_cr,
2401 currency_code,
2402 exchange_date,
2403 exchange_rate,
2404 exchange_type,
2405 amount_dr,
2406 amount_cr,
2407 '', --description TBD
2408 nvl(account_class,'XXXX'), --accounting class code
2409 sl_link_id, --gl sl link id
2410 'XLAJEL', --gl sl link table
2411 DECODE(third_party_id, NULL, NULL, 'C'), --party type code
2412 third_party_id, --party id
2413 third_party_sub_id, --third party site
2414 '', --statistical amount
2415 '', --ussgl trx code
2416 '', --jgzz recon ref
2417 '', --control balance flag
2418 '', --analytical balance
2419 sysdate, --row who columns
2420 -2005,
2421 sysdate,
2422 -2005,
2423 -2005,
2424 sysdate,
2425 -2005, --program id
2426 222,
2427 '', --request id
2428 'Y',
2429 accounting_date,
2430 ledger_id)
2431 WHEN 1 = 1 THEN
2432 INTO XLA_DISTRIBUTION_LINKS
2433 (APPLICATION_ID,
2434 EVENT_ID,
2435 AE_HEADER_ID,
2436 AE_LINE_NUM,
2437 ACCOUNTING_LINE_CODE,
2438 ACCOUNTING_LINE_TYPE_CODE,
2439 REF_AE_HEADER_ID,
2440 -- REF_AE_LINE_NUM,
2441 SOURCE_DISTRIBUTION_TYPE,
2442 SOURCE_DISTRIBUTION_ID_CHAR_1,
2443 SOURCE_DISTRIBUTION_ID_CHAR_2,
2444 SOURCE_DISTRIBUTION_ID_CHAR_3,
2445 SOURCE_DISTRIBUTION_ID_CHAR_4,
2446 SOURCE_DISTRIBUTION_ID_CHAR_5,
2447 SOURCE_DISTRIBUTION_ID_NUM_1,
2448 SOURCE_DISTRIBUTION_ID_NUM_2,
2449 SOURCE_DISTRIBUTION_ID_NUM_3,
2450 SOURCE_DISTRIBUTION_ID_NUM_4,
2451 SOURCE_DISTRIBUTION_ID_NUM_5,
2452 UNROUNDED_ENTERED_DR,
2453 UNROUNDED_ENTERED_CR,
2454 UNROUNDED_ACCOUNTED_DR,
2455 UNROUNDED_ACCOUNTED_CR,
2456 MERGE_DUPLICATE_CODE,
2457 TAX_LINE_REF_ID,
2458 TAX_SUMMARY_LINE_REF_ID,
2459 TAX_REC_NREC_DIST_REF_ID,
2460 STATISTICAL_AMOUNT,
2461 TEMP_LINE_NUM,
2462 EVENT_TYPE_CODE,
2463 EVENT_CLASS_CODE,
2464 REF_EVENT_ID,
2465 UPG_BATCH_ID)
2466 VALUES
2467 (222,
2468 event_id,
2469 ae_header_id,
2470 line_num+max_line_num,
2471 account_class,
2472 'C', --accounting line code customer
2473 ref_header_id, --reference header id
2474 -- '', --reference line number
2475 source_table,
2476 '', --src dist id char
2477 '',
2478 '',
2479 '',
2480 '',
2481 line_id, --src dist id num
2482 '',
2483 '',
2484 '',
2485 '',
2486 amount_dr,
2487 amount_cr,
2488 acctd_amount_dr,
2489 acctd_amount_cr,
2490 'N', --merge dup code
2491 tax_line_id, --tax_line_ref_id
2492 '', --tax_summary_line_ref_id
2493 '', --tax_rec_nrec_dist_ref_id
2494 '', --statistical amount
2495 line_num+max_line_num, --temp_line_num
2496 event_type_code, --event_type_code
2497 event_class_code, --event class code
2498 '', --ref_event_id,
2499 batch_id) --upgrade batch id
2500 select
2501 l_batch_id AS batch_id,
2502 ae_header_id AS ae_header_id,
2503 line_id AS line_id,
2504 event_id AS event_id,
2505 account_class AS account_class,
2506 source_table AS source_table,
2507 code_combination_id AS code_combination_id,
2508 amount_dr AS amount_dr,
2509 amount_cr AS amount_cr,
2510 acctd_amount_dr AS acctd_amount_dr,
2511 acctd_amount_cr AS acctd_amount_cr,
2512 nvl(currency_code,'XXXX') AS currency_code,
2513 third_party_id AS third_party_id,
2514 third_party_sub_id AS third_party_sub_id,
2515 exchange_date AS exchange_date,
2516 exchange_rate AS exchange_rate,
2517 exchange_type AS exchange_type,
2518 tax_line_id AS tax_line_id,
2519 sob_id AS sob_id,
2520 event_type_code AS event_type_code,
2521 event_class_code AS event_class_code,
2522 sl_link_id AS sl_link_id,
2523 ref_header_id AS ref_header_id,
2524 max_line_num AS max_line_num,
2525 accounting_date AS accounting_date,
2526 ledger_id AS ledger_id,
2527 RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
2528 ORDER BY line_id, ln_order) AS line_num
2529 FROM
2530 (select /*+ ordered rowid(cr) use_nl(rec,app,ra,dist,dist1,dl,lin,lin1,hdr) use_hash(gps) swap_join_inputs(gps)
2531 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(LIN,XLA_AE_LINES_U1) index(LIN1,XLA_AE_LINES_U1) index(HDR,XLA_AE_HEADERS_U1) */
2532 hdr.ae_header_id ae_header_id,
2533 hdr.event_id event_id,
2534 dist.set_of_books_id sob_id,
2535 dist.source_type account_class,
2536 'AR_DISTRIBUTIONS_ALL' source_table,
2537 dist.code_combination_id code_combination_id,
2538 dist.amount_dr amount_dr,
2539 dist.amount_cr amount_cr,
2540 dist.acctd_amount_dr acctd_amount_dr,
2541 dist.acctd_amount_cr acctd_amount_cr,
2542 dist.currency_code currency_code,
2543 dist.third_party_id third_party_id,
2544 dist.third_party_sub_id third_party_sub_id,
2545 dist.currency_conversion_date exchange_date,
2546 dist.currency_conversion_rate exchange_rate,
2547 dist.currency_conversion_type exchange_type,
2548 dist.line_id line_id,
2549 null tax_line_id,
2550 dl.event_type_code event_type_code,
2551 dl.event_class_code event_class_code,
2552 lin.gl_sl_link_id sl_link_id,
2553 dl.ref_ae_header_id ref_header_id,
2554 lin1.ae_line_num max_line_num,
2555 hdr.accounting_date accounting_date,
2556 hdr.ledger_id ledger_id,
2557 1 ln_order
2558 --
2559 from ar_mc_cash_receipts cr,
2560 ar_cash_receipts_all rec,
2561 ar_receivable_applications_all app,
2562 xla_upgrade_dates gps,
2563 ar_mc_receivable_apps ra,
2564 ar_mc_distributions_all dist,
2565 ar_mc_distributions_all dist1,
2566 xla_distribution_links dl,
2567 xla_ae_lines lin,
2568 xla_ae_lines lin1,
2569 xla_ae_headers hdr
2570 --
2571 where cr.rowid >= l_start_rowid
2572 and cr.rowid <= l_end_rowid
2573 --
2574 and rec.cash_receipt_id = cr.cash_receipt_id
2575 and NVL(rec.ax_accounted_flag,'N') = 'N'
2576 --
2577 and app.cash_receipt_id = cr.cash_receipt_id
2578 and app.application_type = 'CASH'
2579 and app.status = 'APP'
2580 --
2581 and trunc(app.gl_date) between gps.start_date and gps.end_date
2582 and gps.ledger_id = cr.set_of_books_id
2583 --
2584 and ra.receivable_application_id = app.receivable_application_id
2585 and ra.posting_control_id <> -3
2586 and ra.set_of_books_id = cr.set_of_books_id
2587 --
2588 and dist.source_id = ra.receivable_application_id
2589 and dist.set_of_books_id = ra.set_of_books_id
2590 and dist.source_table = 'RA'
2591 and dist.source_type in ('EXCH_GAIN','EXCH_LOSS','CURR_ROUND')
2592 and not exists (select 'X'
2593 from ar_distributions_all
2594 where source_id = dist.source_id
2595 and source_table = 'RA'
2596 and source_type = dist.source_type)
2597 --
2598 and dist1.source_id = dist.source_id
2599 and dist1.set_of_books_id = dist.set_of_books_id
2600 and dist1.source_table = 'RA'
2601 and dist1.source_type = 'REC'
2602 --
2603 and dl.source_distribution_id_num_1 = dist1.line_id
2604 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2605 and dl.application_id = 222
2606 and dl.upg_batch_id = l_batch_id
2607 and dl.accounting_line_code = 'REC'
2608 --
2609 and lin.ae_header_id = dl.ae_header_id
2610 and lin.ae_line_num = dl.ae_line_num
2611 and lin.application_id = 222
2612 and lin.upg_batch_id = l_batch_id
2613 --
2614 and lin1.ae_header_id = lin.ae_header_id
2615 and lin1.ae_line_num = (select max(ae_line_num)
2616 from xla_ae_lines
2617 where ae_header_id = lin1.ae_header_id
2618 and application_id = 222
2619 and upg_batch_id = l_batch_id)
2620 and lin1.application_id = 222
2621 and lin1.upg_batch_id = l_batch_id
2622 --
2623 and hdr.ae_header_id = lin.ae_header_id
2624 and hdr.application_id = 222
2625 and hdr.upg_batch_id = l_batch_id
2626 and hdr.ledger_id = dist.set_of_books_id
2627 );
2628
2629 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2630
2631 ELSIF (l_table_name = 'RA_MC_CUSTOMER_TRX') THEN
2632
2633 INSERT ALL
2634 WHEN 1 = 1 THEN
2635 INTO XLA_AE_LINES
2636 (upg_batch_id,
2637 ae_header_id,
2638 ae_line_num,
2639 application_id,
2640 code_combination_id,
2641 gl_transfer_mode_code,
2642 accounted_dr,
2643 accounted_cr,
2644 currency_code,
2645 currency_conversion_date,
2646 currency_conversion_rate,
2647 currency_conversion_type,
2648 entered_dr,
2649 entered_cr,
2650 description,
2651 accounting_class_code,
2652 gl_sl_link_id,
2653 gl_sl_link_table,
2654 party_type_code,
2655 party_id,
2656 party_site_id,
2657 statistical_amount,
2658 ussgl_transaction_code,
2659 jgzz_recon_ref,
2660 control_balance_flag,
2661 analytical_balance_flag,
2662 creation_date,
2663 created_by,
2664 last_update_date,
2665 last_updated_by,
2666 last_update_login,
2667 program_update_date,
2668 program_id,
2669 program_application_id,
2670 request_id,
2671 gain_or_loss_flag,
2672 accounting_date,
2673 ledger_id
2674 )
2675 VALUES
2676 ( batch_id,
2677 ae_header_id,
2678 line_num+max_line_num,
2679 222,
2680 code_combination_id,
2681 'D', --gl transfer mode Summary or detail
2682 acctd_amount_dr,
2683 acctd_amount_cr,
2684 currency_code,
2685 exchange_date,
2686 exchange_rate,
2687 exchange_type,
2688 amount_dr,
2689 amount_cr,
2690 '', --description TBD
2691 nvl(account_class,'XXXX'), --accounting class code
2692 sl_link_id, --gl sl link id
2693 'XLAJEL', --gl sl link table
2694 DECODE(third_party_id, NULL, NULL, 'C'), --party type code
2695 third_party_id, --party id
2696 third_party_sub_id, --third party site
2697 '', --statistical amount
2698 '', --ussgl trx code
2699 '', --jgzz recon ref
2700 '', --control balance flag
2701 '', --analytical balance
2702 sysdate, --row who columns
2703 -2005,
2704 sysdate,
2705 -2005,
2706 -2005,
2707 sysdate,
2708 -2005, --program id
2709 222,
2710 '', --request id
2711 'Y',
2712 accounting_date,
2713 ledger_id)
2714 WHEN 1 = 1 THEN
2715 INTO XLA_DISTRIBUTION_LINKS
2716 (APPLICATION_ID,
2717 EVENT_ID,
2718 AE_HEADER_ID,
2719 AE_LINE_NUM,
2720 ACCOUNTING_LINE_CODE,
2721 ACCOUNTING_LINE_TYPE_CODE,
2722 REF_AE_HEADER_ID,
2723 -- REF_AE_LINE_NUM,
2724 SOURCE_DISTRIBUTION_TYPE,
2725 SOURCE_DISTRIBUTION_ID_CHAR_1,
2726 SOURCE_DISTRIBUTION_ID_CHAR_2,
2727 SOURCE_DISTRIBUTION_ID_CHAR_3,
2728 SOURCE_DISTRIBUTION_ID_CHAR_4,
2729 SOURCE_DISTRIBUTION_ID_CHAR_5,
2730 SOURCE_DISTRIBUTION_ID_NUM_1,
2731 SOURCE_DISTRIBUTION_ID_NUM_2,
2732 SOURCE_DISTRIBUTION_ID_NUM_3,
2733 SOURCE_DISTRIBUTION_ID_NUM_4,
2734 SOURCE_DISTRIBUTION_ID_NUM_5,
2735 UNROUNDED_ENTERED_DR,
2736 UNROUNDED_ENTERED_CR,
2737 UNROUNDED_ACCOUNTED_DR,
2738 UNROUNDED_ACCOUNTED_CR,
2739 MERGE_DUPLICATE_CODE,
2740 TAX_LINE_REF_ID,
2741 TAX_SUMMARY_LINE_REF_ID,
2742 TAX_REC_NREC_DIST_REF_ID,
2743 STATISTICAL_AMOUNT,
2744 TEMP_LINE_NUM,
2745 EVENT_TYPE_CODE,
2746 EVENT_CLASS_CODE,
2747 REF_EVENT_ID,
2748 UPG_BATCH_ID)
2749 VALUES
2750 (222,
2751 event_id,
2752 ae_header_id,
2753 line_num+max_line_num,
2754 account_class,
2755 'C', --accounting line code customer
2756 ref_header_id, --reference header id
2757 -- '', --reference line number
2758 source_table,
2759 '', --src dist id char
2760 '',
2761 '',
2762 '',
2763 '',
2764 line_id, --src dist id num
2765 '',
2766 '',
2767 '',
2768 '',
2769 amount_dr,
2770 amount_cr,
2771 acctd_amount_dr,
2772 acctd_amount_cr,
2773 'N', --merge dup code
2774 tax_line_id, --tax_line_ref_id
2775 '', --tax_summary_line_ref_id
2776 '', --tax_rec_nrec_dist_ref_id
2777 '', --statistical amount
2778 line_num+max_line_num, --temp_line_num
2779 event_type_code, --event_type_code
2780 event_class_code, --event class code
2781 '', --ref_event_id,
2782 batch_id) --upgrade batch id
2783 select
2784 l_batch_id AS batch_id,
2785 ae_header_id AS ae_header_id,
2786 line_id AS line_id,
2787 event_id AS event_id,
2788 account_class AS account_class,
2789 source_table AS source_table,
2790 code_combination_id AS code_combination_id,
2791 amount_dr AS amount_dr,
2792 amount_cr AS amount_cr,
2793 acctd_amount_dr AS acctd_amount_dr,
2794 acctd_amount_cr AS acctd_amount_cr,
2795 nvl(currency_code,'XXXX') AS currency_code,
2796 third_party_id AS third_party_id,
2797 third_party_sub_id AS third_party_sub_id,
2798 exchange_date AS exchange_date,
2799 exchange_rate AS exchange_rate,
2800 exchange_type AS exchange_type,
2801 tax_line_id AS tax_line_id,
2802 sob_id AS sob_id,
2803 event_type_code AS event_type_code,
2804 event_class_code AS event_class_code,
2805 sl_link_id AS sl_link_id,
2806 ref_header_id AS ref_header_id,
2807 max_line_num AS max_line_num,
2808 accounting_date AS accounting_date,
2809 ledger_id AS ledger_id,
2810 RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
2811 ORDER BY line_id, ln_order) AS line_num
2812 FROM
2813 (select /*+ ordered rowid(ct) use_nl(trx,app,ra,dist,dist1,dl,lin,lin1,hdr) use_hash(gps) swap_join_inputs(gps)
2814 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(LIN,XLA_AE_LINES_U1) index(LIN1,XLA_AE_LINES_U1) index(HDR,XLA_AE_HEADERS_U1) */
2815 hdr.ae_header_id ae_header_id,
2816 hdr.event_id event_id,
2817 dist.set_of_books_id sob_id,
2818 dist.source_type account_class,
2819 'AR_DISTRIBUTIONS_ALL' source_table,
2820 dist.code_combination_id code_combination_id,
2821 dist.amount_dr amount_dr,
2822 dist.amount_cr amount_cr,
2823 dist.acctd_amount_dr acctd_amount_dr,
2824 dist.acctd_amount_cr acctd_amount_cr,
2825 dist.currency_code currency_code,
2826 dist.third_party_id third_party_id,
2827 dist.third_party_sub_id third_party_sub_id,
2828 dist.currency_conversion_date exchange_date,
2829 dist.currency_conversion_rate exchange_rate,
2830 dist.currency_conversion_type exchange_type,
2831 dist.line_id line_id,
2832 null tax_line_id,
2833 dl.event_type_code event_type_code,
2834 dl.event_class_code event_class_code,
2835 lin.gl_sl_link_id sl_link_id,
2836 dl.ref_ae_header_id ref_header_id,
2837 lin1.ae_line_num max_line_num,
2838 hdr.accounting_date accounting_date,
2839 hdr.ledger_id ledger_id,
2840 1 ln_order
2841 --
2842 from ra_mc_customer_trx ct,
2843 ra_customer_trx_all trx,
2844 ar_receivable_applications_all app,
2845 xla_upgrade_dates gps,
2846 ar_mc_receivable_apps ra,
2847 ar_mc_distributions_all dist,
2848 ar_mc_distributions_all dist1,
2849 xla_distribution_links dl,
2850 xla_ae_lines lin,
2851 xla_ae_lines lin1,
2852 xla_ae_headers hdr
2853 --
2854 where ct.rowid >= l_start_rowid
2855 and ct.rowid <= l_end_rowid
2856 --
2857 and trx.customer_trx_id = ct.customer_trx_id
2858 and NVL(trx.ax_accounted_flag,'N') = 'N'
2859 --
2860 and app.customer_trx_id = ct.customer_trx_id
2861 and app.application_type = 'CM'
2862 and app.status = 'APP'
2863 --
2864 and trunc(app.gl_date) between gps.start_date and gps.end_date
2865 and gps.ledger_id = ct.set_of_books_id
2866 --
2867 and ra.receivable_application_id = app.receivable_application_id
2868 and ra.posting_control_id <> -3
2869 and ra.set_of_books_id = ct.set_of_books_id
2870 --
2871 and dist.source_id = ra.receivable_application_id
2872 and dist.set_of_books_id = ra.set_of_books_id
2873 and dist.source_table = 'RA'
2874 and dist.source_type in ('EXCH_GAIN','EXCH_LOSS','CURR_ROUND')
2875 and not exists (select 'X'
2876 from ar_distributions_all
2877 where source_id = dist.source_id
2878 and source_table = 'RA'
2879 and source_type = dist.source_type)
2880 --
2881 and dist1.source_id = dist.source_id
2882 and dist1.set_of_books_id = dist.set_of_books_id
2883 and dist1.source_table = 'RA'
2884 and dist1.source_type = 'REC'
2885 and dist1.amount_dr is null
2886 --
2887 and dl.source_distribution_id_num_1 = dist1.line_id
2888 and dl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2889 and dl.application_id = 222
2890 and dl.upg_batch_id = l_batch_id
2891 and dl.accounting_line_code = 'REC'
2892 --
2893 and lin.ae_header_id = dl.ae_header_id
2894 and lin.ae_line_num = dl.ae_line_num
2895 and lin.application_id = 222
2896 and lin.upg_batch_id = l_batch_id
2897 --
2898 and lin1.ae_header_id = lin.ae_header_id
2899 and lin1.ae_line_num = (select max(ae_line_num)
2900 from xla_ae_lines
2901 where ae_header_id = lin1.ae_header_id
2902 and application_id = 222
2903 and upg_batch_id = l_batch_id)
2904 and lin1.application_id = 222
2905 and lin1.upg_batch_id = l_batch_id
2906 --
2907 and hdr.ae_header_id = lin.ae_header_id
2908 and hdr.application_id = 222
2909 and hdr.upg_batch_id = l_batch_id
2910 and hdr.ledger_id = dist.set_of_books_id
2911 );
2912
2913 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2914
2915 commit;
2916
2917 END IF; /* If l_table */
2918
2919 EXCEPTION
2920 WHEN NO_DATA_FOUND THEN
2921 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_gain_loss');
2922 RAISE;
2923
2924 WHEN OTHERS THEN
2925 --arp_standard.debug('OTHERS EXCEPTION: ARP_MRC_XLA_UPGRADE.upgrade_mc_gain_loss');
2926 RAISE;
2927
2928 END UPGRADE_MC_GAIN_LOSS;
2929
2930 END ARP_MRC_XLA_UPGRADE;