[Home] [Help]
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: |
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: *=======================================================================*/
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,
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
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,
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: --
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,
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
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,
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: --
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,
649: ra_cust_trx_line_gl_dist_all gld,
650: xla_upgrade_dates gps,
651: ra_mc_trx_line_gl_dist ctlgd,
652: xla_distribution_links dl,
653: xla_ae_headers hdr,
654: xla_ae_headers hdr1
655: --
656: where ct.rowid >= l_start_rowid
657: and ct.rowid <= l_end_rowid
650: xla_upgrade_dates gps,
651: ra_mc_trx_line_gl_dist ctlgd,
652: xla_distribution_links dl,
653: xla_ae_headers hdr,
654: xla_ae_headers hdr1
655: --
656: where ct.rowid >= l_start_rowid
657: and ct.rowid <= l_end_rowid
658: --
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,
747: xla_upgrade_dates gps,
748: ar_mc_receivable_apps app,
749: ar_mc_distributions_all dist,
750: xla_distribution_links dl,
751: xla_ae_headers hdr,
752: xla_ae_headers hdr1
753:
754: --
755: where ct.rowid >= l_start_rowid
748: ar_mc_receivable_apps app,
749: ar_mc_distributions_all dist,
750: xla_distribution_links dl,
751: xla_ae_headers hdr,
752: xla_ae_headers hdr1
753:
754: --
755: where ct.rowid >= l_start_rowid
756: and ct.rowid <= l_end_rowid
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,
837: xla_upgrade_dates gps,
838: ar_mc_transaction_history trh,
839: ar_mc_distributions_all dist,
840: xla_distribution_links dl,
841: xla_ae_headers hdr,
842: xla_ae_headers hdr1
843: --
844: where ct.rowid >= l_start_rowid
845: and ct.rowid <= l_end_rowid
838: ar_mc_transaction_history trh,
839: ar_mc_distributions_all dist,
840: xla_distribution_links dl,
841: xla_ae_headers hdr,
842: xla_ae_headers hdr1
843: --
844: where ct.rowid >= l_start_rowid
845: and ct.rowid <= l_end_rowid
846: --
928: /*========================================================================
929: | PUBLIC PROCEDURE UPGRADE_MC_RECEIPTS
930: |
931: | DESCRIPTION
932: | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
933: | XLA_DISTRIBUTION_LINKS for records related to receipts.
934: |
935: | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
936: |
947: | Date Author Description of Changes
948: | 03-JUL-2005 JVARKEY Created
949: | 30-AUG-2005 JVARKEY Modified the flow
950: | 20-SEP-2005 JVARKEY Detached the insert into
951: | xla_ae_headers into seperate
952: | insert statement
953: *=======================================================================*/
954:
955: PROCEDURE UPGRADE_MC_RECEIPTS(
999: -----------------------
1000:
1001: INSERT ALL
1002: WHEN 1 = 1 THEN
1003: INTO XLA_AE_HEADERS
1004: (upg_batch_id,
1005: upg_source_application_id,
1006: application_id,
1007: amb_context_code,
1054: 'DEFAULT', --amb context code
1055: entity_id,
1056: event_id,
1057: override_event,
1058: xla_ae_headers_s.nextval,
1059: sob_id,
1060: gl_date,
1061: period_name,
1062: '', --reference date global acct eng
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,
1130: xla_upgrade_dates gps,
1131: ar_mc_cash_receipt_hist mccrh,
1132: ar_mc_distributions_all dist,
1133: xla_distribution_links dl,
1134: xla_ae_headers hdr
1135: --
1136: where cr.rowid >= l_start_rowid
1137: and cr.rowid <= l_end_rowid
1138: --
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,
1199: xla_upgrade_dates gps,
1200: ar_mc_receivable_apps app,
1201: ar_mc_distributions_all dist,
1202: xla_distribution_links dl,
1203: xla_ae_headers hdr
1204: --
1205: where cr.rowid >= l_start_rowid
1206: and cr.rowid <= l_end_rowid
1207: --
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,
1267: xla_upgrade_dates gps,
1268: ar_mc_misc_cash_dists mcmcd,
1269: ar_mc_distributions_all dist,
1270: xla_distribution_links dl,
1271: xla_ae_headers hdr
1272: --
1273: where cr.rowid >= l_start_rowid
1274: and cr.rowid <= l_end_rowid
1275: --
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,
1535: xla_upgrade_dates gps,
1536: ar_mc_cash_receipt_hist mccrh,
1537: ar_mc_distributions_all dist,
1538: xla_distribution_links dl,
1539: xla_ae_headers hdr,
1540: xla_ae_headers hdr1
1541: --
1542: where cr.rowid >= l_start_rowid
1543: and cr.rowid <= l_end_rowid
1536: ar_mc_cash_receipt_hist mccrh,
1537: ar_mc_distributions_all dist,
1538: xla_distribution_links dl,
1539: xla_ae_headers hdr,
1540: xla_ae_headers hdr1
1541: --
1542: where cr.rowid >= l_start_rowid
1543: and cr.rowid <= l_end_rowid
1544: --
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,
1650: xla_upgrade_dates gps,
1651: ar_mc_receivable_apps app,
1652: ar_mc_distributions_all dist,
1653: xla_distribution_links dl,
1654: xla_ae_headers hdr,
1655: xla_ae_headers hdr1
1656: --
1657: where cr.rowid >= l_start_rowid
1658: and cr.rowid <= l_end_rowid
1651: ar_mc_receivable_apps app,
1652: ar_mc_distributions_all dist,
1653: xla_distribution_links dl,
1654: xla_ae_headers hdr,
1655: xla_ae_headers hdr1
1656: --
1657: where cr.rowid >= l_start_rowid
1658: and cr.rowid <= l_end_rowid
1659: --
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,
1736: xla_upgrade_dates gps,
1737: ar_mc_misc_cash_dists mcmcd,
1738: ar_mc_distributions_all dist,
1739: xla_distribution_links dl,
1740: xla_ae_headers hdr,
1741: xla_ae_headers hdr1
1742: --
1743: where cr.rowid >= l_start_rowid
1744: and cr.rowid <= l_end_rowid
1737: ar_mc_misc_cash_dists mcmcd,
1738: ar_mc_distributions_all dist,
1739: xla_distribution_links dl,
1740: xla_ae_headers hdr,
1741: xla_ae_headers hdr1
1742: --
1743: where cr.rowid >= l_start_rowid
1744: and cr.rowid <= l_end_rowid
1745: --
1826: /*========================================================================
1827: | PUBLIC PROCEDURE UPGRADE_MC_ADJUSTMENTS
1828: |
1829: | DESCRIPTION
1830: | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
1831: | XLA_DISTRIBUTION_LINKS for records related to adjustments.
1832: |
1833: | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1834: |
1844: | Date Author Description of Changes
1845: | 03-JUL-2005 JVARKEY Created
1846: | 30-AUG-2005 JVARKEY Modified the flow
1847: | 20-SEP-2005 JVARKEY Detached the insert into
1848: | xla_ae_headers into seperate
1849: | insert statement
1850: *=======================================================================*/
1851:
1852: PROCEDURE UPGRADE_MC_ADJUSTMENTS(
1896: -----------------------
1897:
1898: INSERT ALL
1899: WHEN 1 = 1 THEN
1900: INTO XLA_AE_HEADERS
1901: (upg_batch_id,
1902: upg_source_application_id,
1903: application_id,
1904: amb_context_code,
1951: 'DEFAULT', --amb context code
1952: entity_id,
1953: event_id,
1954: override_event,
1955: xla_ae_headers_s.nextval,
1956: sob_id,
1957: gl_date,
1958: period_name,
1959: '', --reference date global acct eng
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,
2024: ar_adjustments_all adjt,
2025: xla_upgrade_dates gps,
2026: ar_mc_distributions_all dist,
2027: xla_distribution_links dl,
2028: xla_ae_headers hdr
2029: --
2030: where adj.rowid >= l_start_rowid
2031: and adj.rowid <= l_end_rowid
2032: and adj.posting_control_id <> -3
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,
2284: ar_adjustments_all adjt,
2285: xla_upgrade_dates gps,
2286: ar_mc_distributions_all dist,
2287: xla_distribution_links dl,
2288: xla_ae_headers hdr,
2289: xla_ae_headers hdr1
2290: --
2291: where adj.rowid >= l_start_rowid
2292: and adj.rowid <= l_end_rowid
2285: xla_upgrade_dates gps,
2286: ar_mc_distributions_all dist,
2287: xla_distribution_links dl,
2288: xla_ae_headers hdr,
2289: xla_ae_headers hdr1
2290: --
2291: where adj.rowid >= l_start_rowid
2292: and adj.rowid <= l_end_rowid
2293: and adj.posting_control_id <> -3
2363: /*========================================================================
2364: | PRIVATE PROCEDURE UPGRADE_MC_GAIN_LOSS
2365: |
2366: | DESCRIPTION
2367: | Will create the records in XLA_AE_HEADERS, XLA_AE_LINES and
2368: | XLA_DISTRIBUTION_LINKS for records related to exchange_gain/loss
2369: | which doesnt have any parent record in AR and exist in MRC.
2370: |
2371: | CALLED FROM PROCEDURES/FUNCTIONS
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,
2622: ar_mc_distributions_all dist1,
2623: xla_distribution_links dl,
2624: xla_ae_lines lin,
2625: xla_ae_lines lin1,
2626: xla_ae_headers hdr
2627: --
2628: where cr.rowid >= l_start_rowid
2629: and cr.rowid <= l_end_rowid
2630: --
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'
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,
2920: ar_mc_distributions_all dist1,
2921: xla_distribution_links dl,
2922: xla_ae_lines lin,
2923: xla_ae_lines lin1,
2924: xla_ae_headers hdr
2925: --
2926: where ct.rowid >= l_start_rowid
2927: and ct.rowid <= l_end_rowid
2928: --
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'