[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,
648: ra_customer_trx_all trx,
649: ra_cust_trx_line_gl_dist_all gld,
650: xla_upgrade_dates gps,
651: ra_mc_trx_line_gl_dist ctlgd,
652: xla_distribution_links dl,
653: xla_ae_headers hdr,
654: xla_ae_headers hdr1
655: --
656: where ct.rowid >= l_start_rowid
697:
698:
699: UNION /* CM applications */
700: select /*+ ordered rowid(ct) use_nl(trx,ra,app,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
701: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
702: hdr.ae_header_id ae_header_id,
703: hdr1.ae_header_id header_id,
704: hdr.event_id event_id,
705: dist.set_of_books_id sob_id,
746: gl_sets_of_books gsb,
747: xla_upgrade_dates gps,
748: ar_mc_receivable_apps app,
749: ar_mc_distributions_all dist,
750: xla_distribution_links dl,
751: xla_ae_headers hdr,
752: xla_ae_headers hdr1
753:
754: --
799: -- and hdr1.doc_category_code = hdr.doc_category_code
800:
801: UNION /* Bills Receivable */
802: select /*+ ordered rowid(ct) use_nl(trx,th,trh,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
803: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
804: hdr.ae_header_id ae_header_id,
805: hdr1.ae_header_id header_id,
806: hdr.event_id event_id,
807: dist.set_of_books_id sob_id,
836: ar_transaction_history_all th,
837: xla_upgrade_dates gps,
838: ar_mc_transaction_history trh,
839: ar_mc_distributions_all dist,
840: xla_distribution_links dl,
841: xla_ae_headers hdr,
842: xla_ae_headers hdr1
843: --
844: where ct.rowid >= l_start_rowid
929: | PUBLIC PROCEDURE UPGRADE_MC_RECEIPTS
930: |
931: | DESCRIPTION
932: | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
933: | XLA_DISTRIBUTION_LINKS for records related to receipts.
934: |
935: | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
936: |
937: | CALLS PROCEDURES/FUNCTIONS
1109: cat_code AS cat_code
1110:
1111: FROM
1112: (select /*+ ordered rowid(cr) use_nl(rec,crh,mccrh,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1113: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1114: hdr.ae_header_id ae_header_id,
1115: hdr.entity_id entity_id,
1116: hdr.event_id event_id,
1117: hdr.event_type_code override_event,
1129: ar_cash_receipt_history_all crh,
1130: xla_upgrade_dates gps,
1131: ar_mc_cash_receipt_hist mccrh,
1132: ar_mc_distributions_all dist,
1133: xla_distribution_links dl,
1134: xla_ae_headers hdr
1135: --
1136: where cr.rowid >= l_start_rowid
1137: and cr.rowid <= l_end_rowid
1178: dist.set_of_books_id
1179:
1180: UNION /* Receipt applications */
1181: select /*+ ordered rowid(cr) use_nl(rec,ra,app,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1182: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1183: hdr.ae_header_id ae_header_id,
1184: hdr.entity_id entity_id,
1185: hdr.event_id event_id,
1186: hdr.event_type_code override_event,
1198: ar_receivable_applications_all ra,
1199: xla_upgrade_dates gps,
1200: ar_mc_receivable_apps app,
1201: ar_mc_distributions_all dist,
1202: xla_distribution_links dl,
1203: xla_ae_headers hdr
1204: --
1205: where cr.rowid >= l_start_rowid
1206: and cr.rowid <= l_end_rowid
1246: dist.set_of_books_id
1247:
1248: UNION /* Misc Receipt */
1249: select /*+ ordered rowid(cr) use_nl(rec,mcd,mcmcd,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
1250: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
1251: hdr.ae_header_id ae_header_id,
1252: hdr.entity_id entity_id,
1253: hdr.event_id event_id,
1254: hdr.event_type_code override_event,
1266: ar_misc_cash_distributions_all mcd,
1267: xla_upgrade_dates gps,
1268: ar_mc_misc_cash_dists mcmcd,
1269: ar_mc_distributions_all dist,
1270: xla_distribution_links dl,
1271: xla_ae_headers hdr
1272: --
1273: where cr.rowid >= l_start_rowid
1274: and cr.rowid <= l_end_rowid
1403: gain_loss_flag,
1404: accounting_date,
1405: ledger_id)
1406: WHEN 1 = 1 THEN
1407: INTO XLA_DISTRIBUTION_LINKS
1408: (APPLICATION_ID,
1409: EVENT_ID,
1410: AE_HEADER_ID,
1411: AE_LINE_NUM,
1501: RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
1502: ORDER BY line_id, ln_order) AS line_num
1503: FROM
1504: (select /*+ ordered rowid(cr) use_nl(rec,crh,mccrh,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
1505: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
1506: hdr.ae_header_id ae_header_id,
1507: hdr1.ae_header_id header_id,
1508: hdr.event_id event_id,
1509: dist.set_of_books_id sob_id,
1534: ar_cash_receipt_history_all crh,
1535: xla_upgrade_dates gps,
1536: ar_mc_cash_receipt_hist mccrh,
1537: ar_mc_distributions_all dist,
1538: xla_distribution_links dl,
1539: xla_ae_headers hdr,
1540: xla_ae_headers hdr1
1541: --
1542: where cr.rowid >= l_start_rowid
1585: -- and hdr1.doc_category_code = hdr.doc_category_code
1586:
1587: UNION /* Receipt applications */
1588: select /*+ ordered rowid(cr) use_nl(rec,ra,app,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
1589: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
1590: hdr.ae_header_id ae_header_id,
1591: hdr1.ae_header_id header_id,
1592: hdr.event_id event_id,
1593: dist.set_of_books_id sob_id,
1649: gl_sets_of_books gsb,
1650: xla_upgrade_dates gps,
1651: ar_mc_receivable_apps app,
1652: ar_mc_distributions_all dist,
1653: xla_distribution_links dl,
1654: xla_ae_headers hdr,
1655: xla_ae_headers hdr1
1656: --
1657: where cr.rowid >= l_start_rowid
1701: -- and hdr1.doc_category_code = hdr.doc_category_code
1702:
1703: UNION /* Misc Receipt */
1704: select /*+ ordered rowid(cr) use_nl(rec,mcd,mcmcd,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
1705: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
1706: hdr.ae_header_id ae_header_id,
1707: hdr1.ae_header_id header_id,
1708: hdr.event_id event_id,
1709: dist.set_of_books_id sob_id,
1735: ar_misc_cash_distributions_all mcd,
1736: xla_upgrade_dates gps,
1737: ar_mc_misc_cash_dists mcmcd,
1738: ar_mc_distributions_all dist,
1739: xla_distribution_links dl,
1740: xla_ae_headers hdr,
1741: xla_ae_headers hdr1
1742: --
1743: where cr.rowid >= l_start_rowid
1827: | PUBLIC PROCEDURE UPGRADE_MC_ADJUSTMENTS
1828: |
1829: | DESCRIPTION
1830: | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
1831: | XLA_DISTRIBUTION_LINKS for records related to adjustments.
1832: |
1833: | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1834: |
1835: | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2005: doc_seq_value AS doc_seq_value,
2006: cat_code AS cat_code
2007: FROM
2008: (select /*+ ordered rowid(adj) use_nl(adjt,dist,dl,hdr) use_hash(gps) swap_join_inputs(gps)
2009: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
2010: hdr.ae_header_id ae_header_id,
2011: hdr.entity_id entity_id,
2012: hdr.event_id event_id,
2013: hdr.event_type_code override_event,
2023: from ar_mc_adjustments adj,
2024: ar_adjustments_all adjt,
2025: xla_upgrade_dates gps,
2026: ar_mc_distributions_all dist,
2027: xla_distribution_links dl,
2028: xla_ae_headers hdr
2029: --
2030: where adj.rowid >= l_start_rowid
2031: and adj.rowid <= l_end_rowid
2155: 'N',
2156: accounting_date,
2157: ledger_id)
2158: WHEN 1 = 1 THEN
2159: INTO XLA_DISTRIBUTION_LINKS
2160: (APPLICATION_ID,
2161: EVENT_ID,
2162: AE_HEADER_ID,
2163: AE_LINE_NUM,
2252: RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
2253: ORDER BY line_id, ln_order) AS line_num
2254: FROM
2255: (select /*+ ordered rowid(adj) use_nl(adjt,dist,dl,hdr,hdr1) use_hash(gps) swap_join_inputs(gps)
2256: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
2257: hdr.ae_header_id ae_header_id,
2258: hdr1.ae_header_id header_id,
2259: hdr.event_id event_id,
2260: dist.set_of_books_id sob_id,
2283: from ar_mc_adjustments adj,
2284: ar_adjustments_all adjt,
2285: xla_upgrade_dates gps,
2286: ar_mc_distributions_all dist,
2287: xla_distribution_links dl,
2288: xla_ae_headers hdr,
2289: xla_ae_headers hdr1
2290: --
2291: where adj.rowid >= l_start_rowid
2364: | PRIVATE PROCEDURE UPGRADE_MC_GAIN_LOSS
2365: |
2366: | DESCRIPTION
2367: | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
2368: | XLA_DISTRIBUTION_LINKS for records related to exchange_gain/loss
2369: | which doesnt have any parent record in AR and exist in MRC.
2370: |
2371: | CALLED FROM PROCEDURES/FUNCTIONS
2372: | UPGRADE_MC_TRANSACTIONS
2481: 'Y',
2482: accounting_date,
2483: ledger_id)
2484: WHEN 1 = 1 THEN
2485: INTO XLA_DISTRIBUTION_LINKS
2486: (APPLICATION_ID,
2487: EVENT_ID,
2488: AE_HEADER_ID,
2489: AE_LINE_NUM,
2580: RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
2581: ORDER BY line_id, ln_order) AS line_num
2582: FROM
2583: (select /*+ ordered rowid(cr) use_nl(rec,app,ra,dist,dist1,dl,lin,lin1,hdr) use_hash(gps) swap_join_inputs(gps)
2584: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(LIN,XLA_AE_LINES_U1) index(LIN1,XLA_AE_LINES_U1) index(HDR,XLA_AE_HEADERS_U1) */
2585: hdr.ae_header_id ae_header_id,
2586: hdr.event_id event_id,
2587: dist.set_of_books_id sob_id,
2588: dist.source_type account_class,
2619: ar_mc_receivable_apps ra,
2620: gl_sets_of_books gsb,
2621: ar_mc_distributions_all dist,
2622: ar_mc_distributions_all dist1,
2623: xla_distribution_links dl,
2624: xla_ae_lines lin,
2625: xla_ae_lines lin1,
2626: xla_ae_headers hdr
2627: --
2653: where source_id = dist.source_id
2654: and source_table = 'RA'
2655: and source_type = dist.source_type) */
2656: and not exists (select 'X'
2657: from xla_distribution_links xdl, xla_ae_headers xah
2658: where xdl.ae_header_id = xah.ae_header_id
2659: and xdl.application_id = 222
2660: and xah.application_id = 222
2661: and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2779: 'Y',
2780: accounting_date,
2781: ledger_id)
2782: WHEN 1 = 1 THEN
2783: INTO XLA_DISTRIBUTION_LINKS
2784: (APPLICATION_ID,
2785: EVENT_ID,
2786: AE_HEADER_ID,
2787: AE_LINE_NUM,
2878: RANK() OVER (PARTITION BY event_id, ae_header_id, sob_id
2879: ORDER BY line_id, ln_order) AS line_num
2880: FROM
2881: (select /*+ ordered rowid(ct) use_nl(trx,app,ra,dist,dist1,dl,lin,lin1,hdr) use_hash(gps) swap_join_inputs(gps)
2882: index(DL,XLA_DISTRIBUTION_LINKS_N1) index(LIN,XLA_AE_LINES_U1) index(LIN1,XLA_AE_LINES_U1) index(HDR,XLA_AE_HEADERS_U1) */
2883: hdr.ae_header_id ae_header_id,
2884: hdr.event_id event_id,
2885: dist.set_of_books_id sob_id,
2886: dist.source_type account_class,
2917: xla_upgrade_dates gps,
2918: ar_mc_receivable_apps ra,
2919: ar_mc_distributions_all dist,
2920: ar_mc_distributions_all dist1,
2921: xla_distribution_links dl,
2922: xla_ae_lines lin,
2923: xla_ae_lines lin1,
2924: xla_ae_headers hdr
2925: --
2951: where source_id = dist.source_id
2952: and source_table = 'RA'
2953: and source_type = dist.source_type) */
2954: and not exists (select 'X'
2955: from xla_distribution_links xdl, xla_ae_headers xah
2956: where xdl.ae_header_id = xah.ae_header_id
2957: and xdl.application_id = 222
2958: and xah.application_id = 222
2959: and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'