[Home] [Help]
232: 3), ev.GL_DATE, decode(EV.PST_ID,
233: -3, 2,
234: 1), EV.PST_ID) AS LINE_NUM
235: FROM
236: (select /*+ ordered rowid(ct) use_nl(ctlgd,ctlgd1,te) use_hash(gps) swap_join_inputs(gps) use_hash(sys,tty) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
237: ct.customer_trx_id TRX_ID ,
238: ct.trx_date TRX_DATE ,
239: ct.set_of_books_id SOB_ID ,
240: tty.type TRX_TYPE ,
269: xla_upgrade_dates gps,
270: ar_system_parameters_all sys,
271: ra_cust_trx_types_all tty,
272: ra_cust_trx_line_gl_dist_all ctlgd1,
273: xla_transaction_entities_upg te
274: WHERE ct.rowid >= l_start_rowid
275: AND ct.rowid <= l_end_rowid
276: AND NVL(ct.ax_accounted_flag,'N') = 'N'
277: AND ctlgd.customer_trx_id = ct.customer_trx_id
330: ct.doc_sequence_id,
331: ct.doc_sequence_value,
332: tty.name
333: UNION
334: select /*+ ordered rowid(ct) use_nl(app,ctlgd,te) use_hash(gps) swap_join_inputs(gps) use_hash(sys,tty) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
335: ct.customer_trx_id TRX_ID ,
336: ct.trx_date TRX_DATE ,
337: ct.set_of_books_id SOB_ID ,
338: tty.type TRX_TYPE ,
374: xla_upgrade_dates gps,
375: ar_system_parameters_all sys,
376: ra_cust_trx_types_all tty,
377: ra_cust_trx_line_gl_dist_all ctlgd,
378: xla_transaction_entities_upg te
379: WHERE ct.rowid >= l_start_rowid
380: AND ct.rowid <= l_end_rowid
381: AND NVL(ct.ax_accounted_flag,'N') = 'N'
382: AND app.application_type = 'CM'
651: ledger_id AS ledger_id,
652: RANK() OVER (PARTITION BY event_id, ae_header_id
653: ORDER BY line_id, ln_order) AS line_num
654: FROM
655: (select /*+ ordered rowid(ct) use_nl(ctlgd,ent,ev,hdr,ctl) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
656: hdr.ae_header_id ae_header_id,
657: decode(account_class, 'REC', 'RECEIVABLE',
658: 'REV', 'REVENUE',
659: 'UNEARN', 'UNEARNED_REVENUE',
723: from
724: ra_customer_trx_all ct,
725: ra_cust_trx_line_gl_dist_all ctlgd,
726: xla_upgrade_dates gps,
727: xla_transaction_entities_upg ent,
728: xla_events ev,
729: xla_ae_headers hdr,
730: ra_customer_trx_lines_all ctl
731: where ct.rowid >= l_start_rowid
748: and hdr.event_id = ev.event_id
749: and ct.set_of_books_id = hdr.ledger_id
750: and ctlgd.customer_trx_line_id = ctl.customer_trx_line_id (+)
751: UNION ALL /* CM applications */
752: select /*+ ordered rowid(ct) use_nl(app,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
753: hdr.ae_header_id ae_header_id,
754: DECODE(ard.source_type, 'REC','RECEIVABLE',
755: ard.source_type) account_class,
756: 'AR_DISTRIBUTIONS_ALL' source_table,
779: 2 ln_order
780: from ra_customer_trx_all ct,
781: ar_receivable_applications_all app,
782: xla_upgrade_dates gps,
783: xla_transaction_entities_upg ent,
784: xla_events ev,
785: xla_ae_headers hdr,
786: ar_distributions_all ard
787: where ct.rowid >= l_start_rowid
1068: 3), ev.GL_DATE, decode(EV.PST_ID,
1069: -3, 2,
1070: 1), EV.PST_ID) AS LINE_NUM
1071: FROM
1072: (select /*+ ordered rowid(ct) use_nl(trh,trh1,te) use_hash(gps) swap_join_inputs(gps) use_hash(sys,tty) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
1073: ct.customer_trx_id TRX_ID ,
1074: ct.trx_date TRX_DATE ,
1075: ct.set_of_books_id SOB_ID ,
1076: tty.type TRX_TYPE ,
1117: xla_upgrade_dates gps,
1118: ar_transaction_history_all trh1,
1119: ar_system_parameters_all sys,
1120: ra_cust_trx_types_all tty,
1121: xla_transaction_entities_upg te
1122: WHERE ct.rowid >= l_start_rowid
1123: AND ct.rowid <= l_end_rowid
1124: AND NVL(ct.ax_accounted_flag,'N') = 'N'
1125: AND ct.customer_trx_id = trh.customer_trx_id
1401: ledger_id AS ledger_id,
1402: RANK() OVER (PARTITION BY event_id, ae_header_id
1403: ORDER BY line_id, ln_order) AS line_num
1404: FROM
1405: ( select /*+ ordered rowid(ct) use_nl(trh,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
1406: hdr.ae_header_id ae_header_id,
1407: decode(ard.source_type, 'FACTOR', 'FAC_BR',
1408: 'REMITTANCE','REM_BR',
1409: 'REC', 'BILL_REC',
1432: 1 ln_order
1433: from ra_customer_trx_all ct,
1434: ar_transaction_history_all trh,
1435: xla_upgrade_dates gps,
1436: xla_transaction_entities_upg ent,
1437: xla_events ev,
1438: xla_ae_headers hdr,
1439: ar_distributions_all ard
1440: where ct.rowid >= l_start_rowid
1739: 13), EV.GL_DATE, decode(EV.PST_ID,
1740: -3, 2,
1741: 1), EV.PST_ID) LINE_NUM
1742: FROM
1743: (select /*+ ordered rowid(cr) use_nl(crh,rmth,crh1,te) use_hash(gps) swap_join_inputs(gps) INDEX(te xla_transaction_entities_N1) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
1744: cr.cash_receipt_id TRX_ID ,
1745: cr.receipt_date TRX_DATE ,
1746: cr.set_of_books_id SOB_ID ,
1747:
1785: ar_cash_receipt_history_all crh,
1786: xla_upgrade_dates gps,
1787: ar_receipt_methods rmth,
1788: ar_cash_receipt_history_all crh1,
1789: xla_transaction_entities_upg te
1790: WHERE cr.rowid >= l_start_rowid
1791: AND cr.rowid <= l_end_rowid
1792: AND NVL(cr.ax_accounted_flag,'N') = 'N'
1793: AND crh.cash_receipt_id = cr.cash_receipt_id
1852: cr.doc_sequence_id,
1853: cr.doc_sequence_value,
1854: rmth.name
1855: UNION
1856: select /*+ ordered rowid(cr) use_nl(mcd,rmth,crh,te) use_hash(gps) swap_join_inputs(gps) INDEX(te xla_transaction_entities_N1) INDEX_SS(crh ar_cash_receipt_history_n1) */
1857: mcd.cash_receipt_id TRX_ID ,
1858: cr.receipt_date TRX_DATE ,
1859: cr.set_of_books_id SOB_ID ,
1860: decode(mcd.created_from,
1888: ar_misc_cash_distributions_all mcd,
1889: xla_upgrade_dates gps,
1890: ar_receipt_methods rmth,
1891: ar_cash_receipt_history_all crh,
1892: xla_transaction_entities_upg te
1893: WHERE cr.rowid >= l_start_rowid
1894: AND cr.rowid <= l_end_rowid
1895: AND NVL(cr.ax_accounted_flag,'N') = 'N'
1896: AND cr.type='MISC'
1943: cr.doc_sequence_id,
1944: cr.doc_sequence_value,
1945: rmth.name
1946: UNION
1947: select /*+ ordered rowid(cr) use_nl(app,crh,crh1,rmth,te) use_hash(gps) swap_join_inputs(gps) INDEX(te xla_transaction_entities_N1) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
1948: cr.cash_receipt_id TRX_ID ,
1949: cr.receipt_date TRX_DATE ,
1950: cr.set_of_books_id SOB_ID ,
1951: decode(crh.created_from,
1989: xla_upgrade_dates gps,
1990: ar_cash_receipt_history_all crh,
1991: ar_cash_receipt_history_all crh1,
1992: ar_receipt_methods rmth,
1993: xla_transaction_entities_upg te
1994: WHERE cr.rowid >= l_start_rowid
1995: AND cr.rowid <= l_end_rowid
1996: AND NVL(cr.ax_accounted_flag,'N') = 'N'
1997: AND app.cash_receipt_id = cr.cash_receipt_id
2268: ledger_id AS ledger_id,
2269: RANK() OVER (PARTITION BY event_id, ae_header_id
2270: ORDER BY line_id, ln_order) + 5000 AS line_num
2271: FROM
2272: ( select /*+ ordered rowid(cr) use_nl(crh,crh1,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
2273: hdr.ae_header_id ae_header_id,
2274: decode(ard.source_type, 'BANK_CHARGES', 'BANK_CHG',
2275: ard.source_type) account_class,
2276: 'AR_DISTRIBUTIONS_ALL' source_table,
2299: from ar_cash_receipts_all cr,
2300: ar_cash_receipt_history_all crh,
2301: xla_upgrade_dates gps,
2302: ar_cash_receipt_history_all crh1,
2303: xla_transaction_entities_upg ent,
2304: xla_events ev,
2305: xla_ae_headers hdr,
2306: ar_distributions_all ard
2307: where cr.rowid >= l_start_rowid
2348: and hdr.event_id = ev.event_id
2349: and ard.source_id = crh.cash_receipt_history_id
2350: and ard.source_table = 'CRH'
2351: UNION ALL /* Receipt applications */
2352: select /*+ ordered rowid(cr) use_nl(sys,app,ent,crh,crh1,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
2353: hdr.ae_header_id ae_header_id,
2354: DECODE(ard.source_type, 'REC', 'RECEIVABLE',
2355: 'CURR_ROUND', 'ROUNDING',
2356: 'EXCH_GAIN', 'GAIN',
2394: from ar_cash_receipts_all cr,
2395: ar_system_parameters_all sys,
2396: ar_receivable_applications_all app,
2397: xla_upgrade_dates gps,
2398: xla_transaction_entities_upg ent,
2399: ar_cash_receipt_history_all crh,
2400: ar_cash_receipt_history_all crh1,
2401: xla_events ev,
2402: xla_ae_headers hdr,
2444: and hdr.application_id = 222
2445: and ard.source_id = app.receivable_application_id
2446: and ard.source_table = 'RA'
2447: UNION ALL /* Misc Cash Dist */
2448: select /*+ ordered rowid(cr) use_nl(mcd,crh,ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh ar_cash_receipt_history_n1) */
2449: hdr.ae_header_id ae_header_id,
2450: DECODE(ard.source_type, 'MISCCASH', 'MISC_CASH',
2451: ard.source_type) account_class,
2452: 'AR_DISTRIBUTIONS_ALL' source_table,
2472: from ar_cash_receipts_all cr,
2473: ar_misc_cash_distributions_all mcd,
2474: xla_upgrade_dates gps,
2475: ar_cash_receipt_history_all crh,
2476: xla_transaction_entities_upg ent,
2477: xla_events ev,
2478: xla_ae_headers hdr,
2479: ar_distributions_all ard
2480: where cr.rowid >= l_start_rowid
2762: ev.ENTITY_ID AS ENTITY_ID,
2763: map.PERIOD_NAME AS PERIOD_NAME,
2764: 1 AS LINE_NUM
2765: FROM
2766: (select /*+ ordered rowid(adj) use_nl(ct,te) use_hash(sys,tty) use_hash(gps) swap_join_inputs(gps) swap_join_inputs(tty) swap_join_inputs(sys) INDEX(te xla_transaction_entities_N1) */
2767: adj.adjustment_id TRX_ID ,
2768: ct.trx_date TRX_DATE ,
2769: adj.set_of_books_id SOB_ID ,
2770: 'ADJ' TRX_TYPE ,
2790: xla_upgrade_dates gps,
2791: ar_system_parameters_all sys,
2792: ra_customer_trx_all ct,
2793: ra_cust_trx_types_all tty,
2794: xla_transaction_entities_upg te
2795: WHERE adj.rowid >= l_start_rowid
2796: AND adj.rowid <= l_end_rowid
2797: AND NVL(adj.ax_accounted_flag,'N') = 'N'
2798: AND adj.customer_trx_id = ct.customer_trx_id
3026: ledger_id AS ledger_id,
3027: RANK() OVER (PARTITION BY event_id, ae_header_id
3028: ORDER BY line_id) AS line_num
3029: FROM
3030: ( select /*+ ordered rowid(adj) use_nl(ent,ev,hdr,ard) use_hash(gps) swap_join_inputs(gps) INDEX(ent xla_transaction_entities_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) */
3031: hdr.ae_header_id ae_header_id,
3032: DECODE(ard.source_type,'REC','RECEIVABLE',
3033: ard.source_type) account_class,
3034: 'AR_DISTRIBUTIONS_ALL' source_table,
3051: hdr.ledger_id ledger_id,
3052: 1 ln_order
3053: from ar_adjustments_all adj,
3054: xla_upgrade_dates gps,
3055: xla_transaction_entities_upg ent,
3056: xla_events ev,
3057: xla_ae_headers hdr,
3058: ar_distributions_all ard
3059: where adj.rowid >= l_start_rowid
3216: update /*+ rowid(gir) */ gl_import_references gir
3217: set gir.gl_sl_link_id = (select min(xal.gl_sl_link_id)
3218: from xla_ae_lines xal,
3219: xla_ae_headers xah,
3220: xla_transaction_entities_upg xte,
3221: ar_cash_receipts_all cr,
3222: ar_system_parameters_all asp,
3223: xla_distribution_links xdl,
3224: ar_cash_receipt_history_all crh,
3267: update /*+ rowid(gir) */ gl_import_references gir
3268: set gir.gl_sl_link_id = (select min(xal.gl_sl_link_id)
3269: from xla_ae_lines xal,
3270: xla_ae_headers xah,
3271: xla_transaction_entities_upg xte,
3272: ar_cash_receipts_all cr,
3273: ar_system_parameters_all asp,
3274: xla_distribution_links xdl,
3275: ar_receivable_applications_all unapp,
3680: RANK() OVER (PARTITION BY event_id, ae_header_id
3681: ORDER BY line_id, ln_order) + max_line_num AS line_num
3682: FROM
3683: ( /* On Account CM and receipt applications */
3684: select /*+ ordered rowid(app) use_nl(ctcm,crh1,crh,cr,ent,ev,hdr,cbs,ctlgd) use_hash(gps) swap_join_inputs(gps) INDEX(ent XLA_TRANSACTION_ENTITIES_N1) INDEX(ev XLA_EVENTS_U2) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
3685: hdr.ae_header_id ae_header_id,
3686: decode(cbs.source,
3687: 'GL', ctlgd.account_class,
3688: 'ADJ', 'ADJ',
3731: nvl((select max(ae_line_num) from xla_ae_lines ael
3732: where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
3733: from ar_receivable_applications_all app,
3734: xla_upgrade_dates gps,
3735: xla_transaction_entities_upg ent,
3736: ra_customer_trx_all ctcm,
3737: ar_cash_receipt_history_all crh1,
3738: ar_cash_receipt_history_all crh,
3739: ar_cash_receipts_all cr,
4062: ar_cash_receipt_history_all crh,
4063: --xla_upgrade_dates gps,
4064: ar_receipt_methods rmth,
4065: ar_cash_receipt_history_all crh1,
4066: xla_transaction_entities_upg te
4067: WHERE NVL(cr.ax_accounted_flag,'N') = 'N'
4068: AND crh.cash_receipt_id = cr.cash_receipt_id
4069: and cr.cash_receipt_id = p_cash_receipt_id
4070: and not exists (select 1
4076: and trunc(xei.event_date) = trunc(crh.gl_date)
4077: and trunc(xei.event_date) = trunc(xahi.accounting_date)
4078: and xei.entity_id in
4079: (select xte.entity_id
4080: from xla_transaction_entities xte,
4081: ar_cash_receipts_all cri
4082: where nvl(xte.SOURCE_ID_INT_1,-99) = cri.cash_receipt_id
4083: and xte.entity_code in ('RECEIPTS')
4084: and xte.application_id = 222
4183: ar_misc_cash_distributions_all mcd,
4184: --xla_upgrade_dates gps,
4185: ar_receipt_methods rmth,
4186: ar_cash_receipt_history_all crh,
4187: xla_transaction_entities_upg te
4188: WHERE NVL(cr.ax_accounted_flag,'N') = 'N'
4189: AND cr.type='MISC'
4190: AND mcd.cash_receipt_id = cr.cash_receipt_id
4191: and cr.cash_receipt_id = p_cash_receipt_id
4198: and trunc(xei.event_date) = trunc(mcd.gl_date)
4199: and trunc(xei.event_date) = trunc(xahi.accounting_date)
4200: and xei.entity_id in
4201: (select xte.entity_id
4202: from xla_transaction_entities xte,
4203: ar_cash_receipts_all cri
4204: where nvl(xte.SOURCE_ID_INT_1,-99) = cri.cash_receipt_id
4205: and xte.entity_code in ('RECEIPTS')
4206: and xte.application_id = 222
4302: -- xla_upgrade_dates gps,
4303: ar_cash_receipt_history_all crh,
4304: ar_cash_receipt_history_all crh1,
4305: ar_receipt_methods rmth,
4306: xla_transaction_entities_upg te
4307: WHERE NVL(cr.ax_accounted_flag,'N') = 'N'
4308: AND app.cash_receipt_id = cr.cash_receipt_id
4309: and cr.cash_receipt_id = p_cash_receipt_id
4310: and not exists (select 1
4316: and trunc(xei.event_date) = trunc(app.gl_date)
4317: and trunc(xei.event_date) = trunc(xahi.accounting_date)
4318: and xei.entity_id in
4319: (select xte.entity_id
4320: from xla_transaction_entities xte,
4321: ar_cash_receipts_all cri
4322: where nvl(xte.SOURCE_ID_INT_1,-99) = cri.cash_receipt_id
4323: and xte.entity_code in ('RECEIPTS')
4324: and xte.application_id = 222
4617: from ar_cash_receipts_all cr,
4618: ar_cash_receipt_history_all crh,
4619: -- xla_upgrade_dates gps,
4620: ar_cash_receipt_history_all crh1,
4621: xla_transaction_entities_upg ent,
4622: xla_events ev,
4623: xla_ae_headers hdr,
4624: ar_distributions_all ard
4625: where nvl(cr.ax_accounted_flag,'N') = 'N'
4721: from ar_cash_receipts_all cr,
4722: ar_system_parameters_all sys,
4723: ar_receivable_applications_all app,
4724: -- xla_upgrade_dates gps,
4725: xla_transaction_entities_upg ent,
4726: ar_cash_receipt_history_all crh,
4727: ar_cash_receipt_history_all crh1,
4728: xla_events ev,
4729: xla_ae_headers hdr,
4808: from ar_cash_receipts_all cr,
4809: ar_misc_cash_distributions_all mcd,
4810: -- xla_upgrade_dates gps,
4811: ar_cash_receipt_history_all crh,
4812: xla_transaction_entities_upg ent,
4813: xla_events ev,
4814: xla_ae_headers hdr,
4815: ar_distributions_all ard
4816: where nvl(cr.ax_accounted_flag,'N') = 'N'
5027: RANK() OVER (PARTITION BY event_id, ae_header_id
5028: ORDER BY line_id, ln_order) + max_line_num AS line_num
5029: FROM
5030: ( /* On Account CM and receipt applications */
5031: select /*+ ordered rowid(app) use_nl(ctcm,crh1,crh,cr,ent,ev,hdr,cbs,ctlgd) use_hash(gps) swap_join_inputs(gps) INDEX(ent XLA_TRANSACTION_ENTITIES_N1) INDEX(ev XLA_EVENTS_U3) INDEX(hdr XLA_AE_HEADERS_N2) INDEX_SS(crh1 ar_cash_receipt_history_n1) */
5032: hdr.ae_header_id ae_header_id,
5033: decode(cbs.source,
5034: 'GL', ctlgd.account_class,
5035: 'ADJ', 'ADJ',
5078: nvl((select max(ae_line_num) from xla_ae_lines ael where ael.ae_header_id=hdr.ae_header_id and application_id=222),1000) max_line_num
5079: from ar_receivable_applications_all app,
5080: ar_system_parameters_all sys,
5081: xla_upgrade_dates gps,
5082: xla_transaction_entities_upg ent,
5083: ra_customer_trx_all ctcm,
5084: ar_cash_receipt_history_all crh1,
5085: ar_cash_receipt_history_all crh,
5086: ar_cash_receipts_all cr,