[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,
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
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: --
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,
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
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
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,
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
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: --
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: |
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(
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,
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
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,
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: --
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,
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: --
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,
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: --
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,
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
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: --
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,
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
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: --
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,
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
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: --
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: |
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(
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,
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
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,
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
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,
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
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
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
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,
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: --
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,
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: --