[Home] [Help]
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
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,
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
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,
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
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,
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
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,
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,
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
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,
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: --
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,
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
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
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,
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
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,
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
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,
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
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,
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,
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
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,
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
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,
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
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)
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,
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
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,
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,
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
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
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,
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,
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: --
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,
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,
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: --