[Home] [Help]
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:
1781: rmth.name CAT_CODE ,
1782: te.entity_id ENTITY_ID
1783: FROM ar_cash_receipts_all cr,
1784: --ar_system_parameters_all sys,
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
1784: --ar_system_parameters_all sys,
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'
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,
1887: --ar_system_parameters_all sys,
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'
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,
1986: FROM ar_cash_receipts_all cr,
1987: --ar_system_parameters_all sys,
1988: ar_receivable_applications_all app,
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
1987: --ar_system_parameters_all sys,
1988: ar_receivable_applications_all app,
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
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,
2296: hdr.accounting_date accounting_date,
2297: hdr.ledger_id ledger_id,
2298: 1 ln_order
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,
2298: 1 ln_order
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
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',
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,
2403: ar_distributions_all ard
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,
2403: ar_distributions_all ard
2404: where cr.rowid >= l_start_rowid
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,
2471: 1 ln_order
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
3208: when others then
3209: l_accounting_method := 'ACCRUAL';
3210: END;
3211:
3212: -- Update gl_sl_link_id for AR_CASH_RECEIPT_HISTORY_ALL data
3213:
3214: If (l_accounting_method = 'CASH') Then
3215:
3216: update /*+ rowid(gir) */ gl_import_references gir
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,
3225: xla_upgrade_dates gps,
3226: ar_distributions_all ard,
3227: gl_je_lines gjl
3228: where xte.source_id_int_1 = cr.cash_receipt_id
3258: ),
3259: gir.gl_sl_link_table = 'XLAJEL',
3260: gir.last_update_date = sysdate
3261: where rowid between l_start_rowid and l_end_rowid
3262: and gir.reference_10 = 'AR_CASH_RECEIPT_HISTORY'
3263: and gir.gl_sl_link_id is null;
3264:
3265: -- Update gl_sl_link_id for AR_RECEIVABLE_APPLICATIONS_ALL UNAPP data
3266:
3410: THEN 'RA_CUST_TRX_LINE_GL_DIST_ALL'
3411: WHEN GIMP.REFERENCE_10 IN
3412: ('AR_TRANSACTION_HISTORY','AR_ADJUSTMENTS',
3413: 'AR_MISC_CASH_DISTRIBUTIONS',
3414: 'AR_RECEIVABLE_APPLICATIONS', 'AR_CASH_RECEIPT_HISTORY')
3415: THEN 'AR_DISTRIBUTIONS_ALL'
3416: ELSE NULL
3417: END )
3418: AND LN.APPLICATION_ID = 222
3422: )
3423: WHERE ROWID BETWEEN l_start_rowid and l_end_rowid
3424: AND GIMP.REFERENCE_10 IN
3425: ('AR_TRANSACTION_HISTORY','AR_ADJUSTMENTS','AR_MISC_CASH_DISTRIBUTIONS',
3426: 'AR_RECEIVABLE_APPLICATIONS','AR_CASH_RECEIPT_HISTORY','RA_CUST_TRX_LINE_GL_DIST')
3427: AND GIMP.GL_SL_LINK_ID IS NULL;
3428:
3429:
3430: l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
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',
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,
3740: xla_events ev,
3741: xla_ae_headers hdr,
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,
3740: xla_events ev,
3741: xla_ae_headers hdr,
3742: ar_cash_basis_dists_all cbs,
4058: rmth.name CAT_CODE ,
4059: te.entity_id ENTITY_ID
4060: FROM ar_cash_receipts_all cr,
4061: --ar_system_parameters_all sys,
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
4061: --ar_system_parameters_all sys,
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
4182: --ar_system_parameters_all sys,
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
4299: FROM ar_cash_receipts_all cr,
4300: -- ar_system_parameters_all sys,
4301: ar_receivable_applications_all app,
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'
4300: -- ar_system_parameters_all sys,
4301: ar_receivable_applications_all app,
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
4614: hdr.ledger_id ledger_id,
4615: 1 ln_order,
4616: 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
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,
4616: 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
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
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,
4730: ar_distributions_all ard
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,
4730: ar_distributions_all ard
4731: where nvl(cr.ax_accounted_flag,'N') = 'N'
4807: 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
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
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',
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,
5087: xla_events ev,
5088: xla_ae_headers hdr,
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,
5087: xla_events ev,
5088: xla_ae_headers hdr,
5089: ar_cash_basis_dists_all cbs,
5167:
5168: UPDATE ar_receivable_applications ra
5169: SET ra.cash_receipt_history_id =
5170: ( SELECT MAX(crh.cash_receipt_history_id)
5171: FROM ar_cash_receipt_history crh
5172: WHERE crh.cash_receipt_id = ra.cash_receipt_id
5173: AND crh.creation_date = (SELECT MAX(creation_date)
5174: FROM ar_cash_receipt_history
5175: WHERE cash_receipt_id = crh.cash_receipt_id
5170: ( SELECT MAX(crh.cash_receipt_history_id)
5171: FROM ar_cash_receipt_history crh
5172: WHERE crh.cash_receipt_id = ra.cash_receipt_id
5173: AND crh.creation_date = (SELECT MAX(creation_date)
5174: FROM ar_cash_receipt_history
5175: WHERE cash_receipt_id = crh.cash_receipt_id
5176: AND creation_date <= ra.creation_date
5177: group by cash_receipt_id)
5178: ),