[Home] [Help]
249:
250: -- Delete from xla_distribution_links
251: DELETE FROM xla_distribution_links
252: WHERE ae_header_id IN
253: (SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
254: FROM xla_events_gt xe,
255: xla_ae_headers xh
256: WHERE xe.reference_char_1 = 'E'
257: AND xh.application_id = xe.application_id
251: DELETE FROM xla_distribution_links
252: WHERE ae_header_id IN
253: (SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
254: FROM xla_events_gt xe,
255: xla_ae_headers xh
256: WHERE xe.reference_char_1 = 'E'
257: AND xh.application_id = xe.application_id
258: AND xh.event_id = xe.reference_num_2
259: AND xh.accounting_entry_status_code <> 'F'
272: -- Delete from xla_ae_segment_values
273: DELETE /*+ index(XLA_AE_SEGMENT_VALUES, XLA_AE_SEGMENT_VALUES_U1) */
274: FROM xla_ae_segment_values
275: WHERE ae_header_id IN
276: (SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
277: FROM xla_events_gt xe,
278: xla_ae_headers xh
279: WHERE xe.reference_char_1 = 'E'
280: AND xh.application_id = xe.application_id
274: FROM xla_ae_segment_values
275: WHERE ae_header_id IN
276: (SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
277: FROM xla_events_gt xe,
278: xla_ae_headers xh
279: WHERE xe.reference_char_1 = 'E'
280: AND xh.application_id = xe.application_id
281: AND xh.event_id = xe.reference_num_2
282: AND xh.accounting_entry_status_code <> 'F'
294: -- Delete from xla_ae_line_acs
295: DELETE /*+ index(xal_acs,XLA_AE_LINE_ACS_U1) */
296: FROM xla_ae_line_acs xal_acs
297: WHERE xal_acs.ae_header_id IN
298: (SELECT/*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
299: FROM xla_events_gt evt
300: ,xla_ae_headers aeh
301: WHERE evt.reference_char_1 = 'E'
302: AND aeh.application_id = evt.application_id
296: FROM xla_ae_line_acs xal_acs
297: WHERE xal_acs.ae_header_id IN
298: (SELECT/*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
299: FROM xla_events_gt evt
300: ,xla_ae_headers aeh
301: WHERE evt.reference_char_1 = 'E'
302: AND aeh.application_id = evt.application_id
303: AND aeh.event_id = evt.reference_num_2
304: AND aeh.accounting_entry_status_code <> 'F'
315:
316: -- Delete from xla_ae_header_acs
317: DELETE FROM xla_ae_header_acs
318: WHERE ae_header_id IN
319: (SELECT /*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
320: FROM xla_events_gt evt
321: ,xla_ae_headers aeh
322: WHERE evt.reference_char_1 = 'E'
323: AND aeh.application_id = evt.application_id
317: DELETE FROM xla_ae_header_acs
318: WHERE ae_header_id IN
319: (SELECT /*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
320: FROM xla_events_gt evt
321: ,xla_ae_headers aeh
322: WHERE evt.reference_char_1 = 'E'
323: AND aeh.application_id = evt.application_id
324: AND aeh.event_id = evt.reference_num_2
325: AND aeh.accounting_entry_status_code <> 'F'
337: -- Delete from xla_ae_lines
338: DELETE FROM xla_ae_lines
339: WHERE application_id = g_application_id
340: AND ae_header_id IN
341: ( SELECT /*+ leading(xe) use_nl(xh) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
342: FROM xla_events_gt xe,
343: xla_ae_headers xh
344: WHERE xe.reference_char_1 = 'E'
345: AND xh.application_id = xe.application_id
339: WHERE application_id = g_application_id
340: AND ae_header_id IN
341: ( SELECT /*+ leading(xe) use_nl(xh) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
342: FROM xla_events_gt xe,
343: xla_ae_headers xh
344: WHERE xe.reference_char_1 = 'E'
345: AND xh.application_id = xe.application_id
346: AND xh.event_id = xe.reference_num_2
347: AND xh.accounting_entry_status_code <> 'F'
355: ,p_level => C_LEVEL_STATEMENT
356: ,p_module => l_log_module);
357: END IF;
358:
359: -- Delete from xla_ae_headers
360: DELETE /*+ index(aeh, xla_ae_headers_n2) */
361: FROM xla_ae_headers aeh
362: WHERE application_id = g_application_id
363: AND event_id IN (SELECT reference_num_2
356: ,p_module => l_log_module);
357: END IF;
358:
359: -- Delete from xla_ae_headers
360: DELETE /*+ index(aeh, xla_ae_headers_n2) */
361: FROM xla_ae_headers aeh
362: WHERE application_id = g_application_id
363: AND event_id IN (SELECT reference_num_2
364: FROM xla_events_gt
357: END IF;
358:
359: -- Delete from xla_ae_headers
360: DELETE /*+ index(aeh, xla_ae_headers_n2) */
361: FROM xla_ae_headers aeh
362: WHERE application_id = g_application_id
363: AND event_id IN (SELECT reference_num_2
364: FROM xla_events_gt
365: WHERE reference_char_1 = 'E'
401: PROCEDURE validate_events ( x_return_status OUT NOCOPY VARCHAR2)
402: IS
403:
404: CURSOR c_errors IS
405: SELECT /*+ leading(evt) use_nl(evt,xah,xte) INDEX(xah,xla_ae_headers_n2)INDEX(xte,XLA_TRANSACTION_ENTITIES_U1) */
406: DISTINCT xte.ledger_id trx_ledger_id ,
407: xte.entity_code trx_entity_code ,
408: xah.gl_transfer_status_code ,
409: evt.*
407: xte.entity_code trx_entity_code ,
408: xah.gl_transfer_status_code ,
409: evt.*
410: FROM xla_events_gt evt ,
411: xla_ae_headers xah ,
412: XLA_TRANSACTION_ENTITIES_UPG xte
413: WHERE xah.application_id(+) = evt.application_id
414: AND xah.event_id(+) = evt.event_number
415: AND xte.APPLICATION_ID(+) = xah.application_id
672: -- Data is inserted into Headers_GT to maintain the relationship between
673: -- new_header_id and original_header_id from which reversal_accounting is created
674: -- PARENT_HEADER_ID contains the ORIGINAL_HEADER_ID
675: -- EVENT_NUMBER contains the ORIGINAL_EVENT_ID corresponding to ORIGINAL_HEADER_ID
676: INSERT INTO XLA_AE_HEADERS_GT
677: ( AE_HEADER_ID
678: ,LEDGER_ID
679: ,ENTITY_ID
680: ,EVENT_ID
694: ,PARENT_HEADER_ID
695: ,ACCRUAL_REVERSAL_FLAG
696: ,EVENT_NUMBER
697: )
698: SELECT /*+ leading(xe) use_nl(xe,xah) INDEX(xah,XLA_AE_HEADERS_N2) */
699: XLA_AE_HEADERS_S.NEXTVAL
700: ,xah.LEDGER_ID
701: ,xe.ENTITY_ID
702: ,xe.EVENT_ID
695: ,ACCRUAL_REVERSAL_FLAG
696: ,EVENT_NUMBER
697: )
698: SELECT /*+ leading(xe) use_nl(xe,xah) INDEX(xah,XLA_AE_HEADERS_N2) */
699: XLA_AE_HEADERS_S.NEXTVAL
700: ,xah.LEDGER_ID
701: ,xe.ENTITY_ID
702: ,xe.EVENT_ID
703: ,xe.EVENT_TYPE_CODE
722: ,xah.DOC_CATEGORY_CODE
723: ,xah.AE_HEADER_ID
724: ,'N'
725: ,xah.event_id
726: FROM XLA_AE_HEADERS xah
727: ,XLA_EVENTS_GT xe
728: WHERE xe.application_id = xah.application_id
729: AND xe.event_number = xah.event_id
730: AND xah.application_id = g_application_id
753: ,p_module =>l_log_module);
754:
755: END IF;
756:
757: INSERT INTO XLA_AE_HEADERS
758: ( APPLICATION_ID
759: ,AE_HEADER_ID
760: ,LEDGER_ID
761: ,ENTITY_ID
807: ,sysdate
808: ,g_last_updated_by
809: ,g_last_update_login
810: ,-9999
811: FROM XLA_AE_HEADERS_GT
812: ;
813:
814: IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
815: trace
841: ,xah_acs.ac4
842: ,xah_acs.ac5
843: ,1
844: FROM xla_ae_header_acs xah_acs ,
845: xla_ae_headers_gt xah
846: WHERE xah_acs.ae_header_id = xah.parent_header_id;
847:
848: IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
849: trace
985: ) CONTROL_BALANCE_FLAG
986: ,DECODE(NVL(xal.analytical_balance_flag ,'N'),'N',NULL,'P')
987: , -9999
988: FROM xla_ae_lines xal ,
989: xla_ae_headers_gt xah ,
990: xla_ledger_options xlo ,
991: gl_ledgers gl
992: WHERE xal.application_id = g_application_id
993: AND xal.ae_header_id = xah.parent_header_id
1029: ,xal_acs.ac4
1030: ,xal_acs.ac5
1031: ,1
1032: FROM xla_ae_line_acs xal_acs
1033: , xla_ae_headers_gt xah
1034: WHERE xal_acs.ae_header_id = xah.parent_header_id ;
1035:
1036: IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1037: trace
1078: ,xal.unrounded_entered_cr
1079: ,xal.unrounded_accounted_dr
1080: ,xal.unrounded_accounted_cr
1081: ,-9999
1082: FROM xla_ae_headers_gt xah
1083: ,xla_ae_lines xal
1084: WHERE 1 = 1
1085: AND xal.application_id = g_application_id
1086: AND xal.ae_header_id = xah.ae_header_id;
1112: --+==========================================================================+
1113: --| PRIVATE PROCEDURE |
1114: --| Complete_Reversal_Entries |
1115: --| Local Procedure to mark success/failure of the reversal accounting of |
1116: --| each event in INTERFACE table/XLA_EVENTS/XLA_AE_HEADERS. |
1117: --+==========================================================================+
1118: PROCEDURE complete_reversal_entries
1119: IS
1120: l_log_module VARCHAR2(240);
1170: ,p_level => C_LEVEL_PROCEDURE
1171: ,p_module =>l_log_module);
1172: END IF;
1173:
1174: MERGE /*+ INDEX(xah,XLA_AE_HEADERS_N2) */ INTO xla_ae_headers xah
1175: USING XLA_EVENTS_GT evt
1176: ON ( evt.event_id = xah.event_id
1177: AND evt.application_id = xah.application_id
1178: AND xah.accounting_batch_id = g_accounting_batch_id
1183: WHERE xah.accounting_entry_status_code <> 'F' ;
1184:
1185: IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1186: trace
1187: ( p_msg => 'Number of Rows Updated in XLA_AE_HEADERS ' || SQL%ROWCOUNT
1188: ,p_level => C_LEVEL_PROCEDURE
1189: ,p_module =>l_log_module);
1190: END IF;
1191:
2015: WHERE reference_char_1 = 'E'
2016: AND reference_num_2 IS NOT NULL )
2017: AND NOT EXISTS
2018: ( SELECT 1
2019: FROM xla_ae_headers xah
2020: WHERE xah.application_id = xe.application_id
2021: AND xah.event_id = xe.event_id
2022: ) ;
2023: