[Home] [Help]
54: IF NVL(l_entity_type,'E') = 'E' THEN
55:
56: INSERT ALL
57: WHEN 1 = 1 THEN
58: INTO XLA_EVENTS
59: (upg_batch_id,
60: upg_source_application_id,
61: application_id,
62: reference_num_1,
101: 0, --program_id
102: 222,
103: '',
104: entity_id,
105: xla_events_s.nextval,
106: 'Y', --upgrade flag
107: trx_date
108: )
109: WHEN PST_ID <> -3 THEN
158: 222,
159: 222,
160: 'DEFAULT', --amb context code
161: entity_id,
162: xla_events_s.nextval,
163: override_event,
164: xla_ae_headers_s.nextval,
165: sob_id,
166: gl_date,
223: ev.ENTITY_ID AS ENTITY_ID,
224: map.PERIOD_NAME AS PERIOD_NAME,
225: decode(l_action_flag,'D',0,
226: (select nvl(max(in_ev.event_number),0)
227: from xla_events in_ev /*bug 5867069*/
228: where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
229: ORDER BY decode(ev.OVERRIDE_EVENT,
230: ev.TRX_TYPE||'_CREATE',1,
231: ev.TRX_TYPE||'_UPDATE',2,
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',
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
732: and ct.rowid <= l_end_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,
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
788: and ct.rowid <= l_end_rowid
890: IF NVL(l_entity_type,'E') = 'E' THEN
891:
892: INSERT ALL
893: WHEN 1 = 1 THEN
894: INTO XLA_EVENTS
895: (upg_batch_id,
896: upg_source_application_id,
897: application_id,
898: reference_num_1,
937: 0,
938: 222,
939: '',
940: entity_id,
941: xla_events_s.nextval,
942: 'Y', --upgrade flag
943: trx_date
944: )
945: WHEN PST_ID <> -3 THEN
994: 222,
995: 222,
996: 'DEFAULT', --amb context code
997: entity_id,
998: xla_events_s.nextval,
999: override_event,
1000: xla_ae_headers_s.nextval,
1001: sob_id,
1002: gl_date,
1059: ev.ENTITY_ID AS ENTITY_ID,
1060: map.PERIOD_NAME AS PERIOD_NAME,
1061: decode(l_action_flag,'D',0,
1062: (select nvl(max(in_ev.event_number),0)
1063: from xla_events in_ev /*bug 5867069*/
1064: where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
1065: ORDER BY decode(ev.OVERRIDE_EVENT,
1066: ev.TRX_TYPE||'_CREATE',1,
1067: ev.TRX_TYPE||'_UPDATE',2,
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',
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
1441: and ct.rowid <= l_end_rowid
1554: IF NVL(l_entity_type,'E') = 'E' THEN
1555:
1556: INSERT ALL
1557: WHEN 1 = 1 THEN
1558: INTO XLA_EVENTS
1559: (upg_batch_id,
1560: upg_source_application_id,
1561: application_id,
1562: reference_num_1,
1601: 0,
1602: 222,
1603: '',
1604: entity_id,
1605: xla_events_s.nextval,
1606: 'Y', --upgrade flag
1607: trx_date
1608: )
1609: WHEN PST_ID <> -3 THEN
1658: 222,
1659: 222,
1660: 'DEFAULT', --amb context code
1661: entity_id,
1662: xla_events_s.nextval,
1663: override_event,
1664: xla_ae_headers_s.nextval,
1665: sob_id,
1666: gl_date,
1724: ev.ENTITY_ID AS ENTITY_ID,
1725: map.PERIOD_NAME AS PERIOD_NAME,
1726: decode(l_action_flag,'D',0,
1727: (select nvl(max(in_ev.event_number),0)
1728: from xla_events in_ev /*bug5867069*/
1729: where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
1730: ORDER BY decode(ev.OVERRIDE_EVENT,
1731: 'RECP_CREATE' ,1,
1732: 'RECP_UPDATE' ,2,
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,
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
2308: and cr.rowid <= l_end_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',
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
2405: and cr.rowid <= l_end_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,
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
2481: and cr.rowid <= l_end_rowid
2596: IF NVL(l_entity_type,'E') = 'E' THEN
2597:
2598: INSERT ALL
2599: WHEN 1 = 1 THEN
2600: INTO XLA_EVENTS
2601: (upg_batch_id,
2602: upg_source_application_id,
2603: application_id,
2604: reference_num_1,
2643: 0,
2644: 222,
2645: '',
2646: entity_id,
2647: xla_events_s.nextval,
2648: 'Y', --upgrade flag
2649: trx_date
2650: )
2651: WHEN PST_ID <> -3 THEN
2701: 222,
2702: 222,
2703: 'DEFAULT', --amb context code
2704: entity_id,
2705: xla_events_s.nextval,
2706: override_event,
2707: xla_ae_headers_s.nextval,
2708: sob_id,
2709: gl_date,
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,
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
3060: and adj.rowid <= l_end_rowid
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',
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,
3743: ra_cust_trx_line_gl_dist_all ctlgd
3744: where app.rowid >= l_start_rowid
3832: BEGIN
3833:
3834: INSERT ALL
3835: WHEN 1 = 1 THEN
3836: INTO XLA_EVENTS
3837: (upg_batch_id,
3838: upg_source_application_id,
3839: application_id,
3840: reference_num_1,
3879: 0,
3880: 222,
3881: '',
3882: entity_id,
3883: xla_events_s.nextval,
3884: 'Y', --upgrade flag
3885: trx_date
3886: )
3887: WHEN PST_ID <> -3 THEN
3936: 222,
3937: 222,
3938: 'DEFAULT', --amb context code
3939: entity_id,
3940: xla_events_s.nextval,
3941: override_event,
3942: xla_ae_headers_s.nextval,
3943: sob_id,
3944: gl_date,
4002: ev.ENTITY_ID AS ENTITY_ID,
4003: map.PERIOD_NAME AS PERIOD_NAME,
4004: decode('P','D',0, --
4005: (select nvl(max(in_ev.event_number),0)
4006: from xla_events in_ev /*bug5867069*/
4007: where in_ev.entity_id = ev.entity_id and in_ev.application_id=222)) + RANK() OVER (PARTITION BY ev.ENTITY_ID
4008: ORDER BY decode(ev.OVERRIDE_EVENT,
4009: 'RECP_CREATE' ,1,
4010: 'RECP_UPDATE' ,2,
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
4071: from xla_events xei, xla_ae_headers xahi
4072: where xei.application_id = 222
4073: and xei.application_id = xahi.application_id
4074: and xei.entity_id = xahi.entity_id
4075: and xei.reference_num_1 = crh.posting_control_id
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
4192: and not exists (select 1
4193: from xla_events xei, xla_ae_headers xahi
4194: where xei.application_id = 222
4195: and xei.application_id = xahi.application_id
4196: and xei.entity_id = xahi.entity_id
4197: and xei.reference_num_1 = mcd.posting_control_id
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
4311: from xla_events xei, xla_ae_headers xahi
4312: where xei.application_id = 222
4313: and xei.application_id = xahi.application_id
4314: and xei.entity_id = xahi.entity_id
4315: and xei.reference_num_1 = app.posting_control_id
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'
4626: and cr.cash_receipt_id = crh.cash_receipt_id
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'
4732: and nvl(sys.org_id,-9999) = nvl(cr.org_id, -9999)
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'
4817: and cr.cash_receipt_id = mcd.cash_receipt_id
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',
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,
5090: ra_cust_trx_line_gl_dist_all ctlgd
5091: where nvl(app.postable,'Y') = 'Y'