[Home] [Help]
425:
426: IF (p_ae_header_id IS NULL) THEN
427: -- g_caller := C_CALLER_ACCT_PROGRAM; -- 4262811
428:
429: UPDATE xla_ae_headers_gt h
430: SET (period_year,period_closing_status) =
431: (SELECT period_year,closing_status
432: FROM gl_period_statuses gl
433: WHERE gl.period_name = h.period_name
1272: ,NULL -- substituted_ccid
1273: ,h.accounting_entry_status_code
1274: ,h.period_name
1275: ,l.gain_or_loss_flag
1276: FROM xla_ae_headers_gt h
1277: ,xla_ae_lines l
1278: ,gl_code_combinations ccid
1279: ,fnd_currencies fcu
1280: WHERE ccid.code_combination_id(+) = l.code_combination_id
1710: NVL(gsa1.code_combination_id,
1711: NVL(gsa2.code_combination_id, gsa3.code_combination_id)))
1712: ,h.accounting_entry_status_code
1713: ,h.period_name
1714: FROM xla_ae_headers_gt h
1715: ,xla_ae_lines l
1716: ,gl_code_combinations ccid
1717: ,gl_code_combinations ccid1
1718: ,gl_suspense_accounts gsa
2247: ELSE NULL END
2248: ,h.accounting_entry_status_code
2249: ,h.period_name
2250: ,l.gain_or_loss_flag
2251: FROM xla_ae_headers_gt h
2252: ,xla_ae_lines l
2253: ,gl_code_combinations ccid
2254: ,gl_code_combinations ccid1
2255: ,fnd_currencies fcu
2298: p_level => C_LEVEL_STATEMENT);
2299: END IF;
2300: END IF;
2301:
2302: UPDATE xla_ae_headers
2303: SET zero_amount_flag = 'Y'
2304: WHERE application_id = g_application_id and
2305: ae_header_id in
2306: (select ae_header_id
2589: or (g_ledger_coa_id <> ccid.chart_of_accounts_id)
2590: THEN 'Y'
2591: ELSE NULL
2592: END
2593: FROM xla_ae_headers h
2594: ,xla_ae_lines l
2595: ,gl_code_combinations ccid
2596: ,fnd_currencies fcu
2597: WHERE ccid.code_combination_id(+) = l.code_combination_id
2648: ,CASE WHEN h.doc_sequence_id IS NOT NULL AND doc.doc_sequence_id IS NULL
2649: THEN 'N'
2650: ELSE 'Y'
2651: END doc_sequence_id_valid_flag
2652: FROM xla_ae_headers h
2653: LEFT OUTER JOIN fnd_doc_sequence_categories cat
2654: ON cat.code = h.doc_category_code
2655: LEFT OUTER JOIN fnd_document_sequences doc
2656: ON doc.doc_sequence_id = h.doc_sequence_id
2672: ,CASE WHEN h.doc_sequence_id IS NOT NULL AND doc.doc_sequence_id IS NULL
2673: THEN 'N'
2674: ELSE 'Y'
2675: END doc_sequence_id_valid_flag
2676: FROM xla_ae_headers_gt h
2677: LEFT OUTER JOIN fnd_doc_sequence_categories cat
2678: ON cat.code = h.doc_category_code
2679: LEFT OUTER JOIN fnd_document_sequences doc
2680: ON doc.doc_sequence_id = h.doc_sequence_id
2957: ,CASE WHEN h.balance_type_code = 'B' AND
2958: gp.period_year > b.latest_opened_year
2959: THEN 'N'
2960: ELSE 'Y' END budget_period_valid_flag
2961: FROM xla_ae_headers h
2962: JOIN gl_period_statuses gp
2963: ON gp.period_name = h.period_name
2964: AND gp.ledger_id = g_ledger_id
2965: AND gp.application_id = C_GL_APPLICATION_ID
2987: ,CASE WHEN h.balance_type_code = 'B' AND
2988: h.period_year > b.latest_opened_year
2989: THEN 'N'
2990: ELSE 'Y' END budget_period_valid_flag
2991: FROM xla_ae_headers_gt h
2992: LEFT OUTER JOIN gl_budget_versions bv
2993: ON bv.budget_version_id = h.budget_version_id
2994: LEFT OUTER JOIN gl_budgets b
2995: ON b.budget_name = bv.budget_name
3169: SELECT xah.ae_header_id
3170: ,xah.event_id
3171: ,xah.entity_id
3172: ,xah.accounting_date
3173: FROM xla_ae_headers xah
3174: , gl_transaction_dates gtd
3175: WHERE xah.accounting_date = gtd.transaction_date
3176: AND gtd.transaction_calendar_id = g_transaction_calendar_id
3177: AND gtd.business_day_flag = 'N';
3306: ,NULL header_num -- 4262811
3307: ,NULL period_closing_status -- 4262811
3308: ,h.period_name period_name -- 5136994
3309: ,h.gl_transfer_status_code
3310: FROM xla_ae_headers h
3311: JOIN gl_ledgers g
3312: ON g.ledger_id = h.ledger_id
3313: JOIN gl_period_statuses gp
3314: ON gp.period_name = h.period_name
3351: ,NVL(h.header_num,0) header_num -- 4262811
3352: ,h.period_closing_status period_closing_status -- 4262811
3353: ,h.period_name period_name -- 5136994
3354: ,h.gl_transfer_status_code
3355: FROM xla_ae_headers_gt h
3356: WHERE h.ledger_id = g_ledger_id
3357: AND (h.accounting_date <= g_end_date OR h.period_closing_status IN ('P','C') -- 4262811
3358: OR h.period_name IS NULL) -- 5136994
3359: AND ((h.balance_type_code = 'B' AND h.budget_version_id IS NULL) OR
5870: FROM xla_product_rules_tl pr
5871: ,xla_event_classes_tl ec
5872: ,xla_event_types_tl et
5873: ,xla_lookups lk
5874: ,xla_ae_headers h
5875: WHERE lk.lookup_code = h.product_rule_type_code
5876: AND lk.lookup_type = 'XLA_OWNER_TYPE'
5877: AND pr.amb_context_code = g_amb_context_code
5878: AND pr.application_id = g_application_id
6531: , hdr.entity_id
6532: , hdr.event_id
6533: , hdr.ae_header_id
6534: FROM fun_bal_errors_gt err
6535: , xla_ae_headers_gt hdr
6536: , gl_je_categories je
6537: , xle_entity_profiles le1
6538: , xle_entity_profiles le2
6539: , xle_entity_profiles le3
6554: , hdr.entity_id
6555: , hdr.event_id
6556: , hdr.ae_header_id
6557: FROM fun_bal_errors_gt err
6558: , xla_ae_headers hdr
6559: , gl_je_categories je
6560: , xle_entity_profiles le1
6561: , xle_entity_profiles le2
6562: , xle_entity_profiles le3
8995: -- 4262811. Added c_caller_mpa_program
8996: IF (g_caller in (C_CALLER_MPA_PROGRAM, C_CALLER_THIRD_PARTY_MERGE)) THEN
8997: DELETE FROM xla_ae_segment_values
8998: WHERE ae_header_id in (SELECT /*+ UNNEST NO_SEMIJOIN */ ae_header_id -- 4752774
8999: FROM xla_ae_headers_gt
9000: WHERE ledger_id = g_ledger_id
9001: AND accounting_date <= NVL(g_end_date, accounting_date)); -- 4262811
9002: ELSIF g_caller = C_CALLER_ACCT_PROGRAM THEN
9003: NULL; -- bug 4883830
9242:
9243: ELSE
9244:
9245: --
9246: -- Possible values returned to xla_ae_headers_gt.funds_status_codes are:
9247: -- S - If all rows in the packet pass funds check (Success)
9248: -- A - If all rows in the packet pass funds check and some of the rows have advisory warnings (Advisory)
9249: -- F - If all rows in the packet fail funds check (Fail)
9250: -- P - If some rows in the packet pass while some fail (Partial)
9258: l_array_ae_header_id
9259: , l_array_hdr_funds_status_code
9260: , l_array_event_id
9261: , l_array_entity_id
9262: FROM xla_ae_headers_gt
9263: WHERE ledger_id = g_ledger_id;
9264:
9265: IF (C_LEVEL_STATEMENT >= g_log_level) THEN
9266: FOR i IN 1 .. l_array_ae_header_id.count LOOP
9272: END IF;
9273:
9274: -- Update header level status
9275: FORALL i IN 1 .. l_array_ae_header_id.count
9276: UPDATE xla_ae_headers
9277: SET funds_status_code = l_array_hdr_funds_status_code(i)
9278: , accounting_entry_status_code = CASE WHEN l_array_hdr_funds_status_code(i) = 'F' THEN
9279: 'I'
9280: WHEN l_array_hdr_funds_status_code(i) = 'T' THEN
9291: WHERE application_id = g_application_id
9292: AND ae_header_id = l_array_ae_header_id(i);
9293:
9294: IF (C_LEVEL_EVENT >= g_log_level) THEN
9295: trace(p_msg => '# row updated in xla_ae_headers = '||SQL%ROWCOUNT,
9296: p_module => l_log_module,
9297: p_level => C_LEVEL_EVENT);
9298: END IF;
9299:
9380: , l_array_unrounded_entered_dr
9381: , l_array_unrounded_accounted_cr
9382: , l_array_unrounded_accounted_dr
9383: FROM xla_validation_lines_gt xvl
9384: , xla_ae_headers xah
9385: , fnd_lookup_values flv
9386: WHERE xvl.ae_header_id = xah.ae_header_id
9387: AND xvl.accounting_class_code <> 'RFE'
9388: AND flv.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
9603: --------------------------------------------------------------------------------
9604: -- 4262811a note: Do not modify. This sets the status correctly on each MPA rows
9605: --------------------------------------------------------------------------------
9606: FORALL i IN 1..g_err_count
9607: UPDATE xla_ae_headers
9608: SET accounting_entry_status_code = C_AE_STATUS_INVALID
9609: -- Bug 5056632. updates group_id back to Null if je is invalid
9610: ,group_id = NULL
9611: WHERE ae_header_id = g_err_hdr_ids(i)
9611: WHERE ae_header_id = g_err_hdr_ids(i)
9612: AND application_id = g_application_id;
9613:
9614: IF (C_LEVEL_EVENT >= g_log_level) THEN
9615: trace(p_msg => '# xla_ae_headers updated to C_AE_STATUS_INVALID = '||SQL%ROWCOUNT,
9616: p_module => l_log_module,
9617: p_level => C_LEVEL_EVENT);
9618: END IF;
9619:
9620: IF g_caller = C_CALLER_ACCT_PROGRAM THEN -- 4262811a
9621:
9622: /*
9623: FORALL i IN 1..g_err_count
9624: UPDATE xla_ae_headers
9625: SET accounting_entry_status_code = C_AE_STATUS_RELATED
9626: WHERE accounting_entry_status_code <> C_AE_STATUS_INVALID
9627: AND event_id = g_err_event_ids(i)
9628: AND application_id = g_application_id;
9627: AND event_id = g_err_event_ids(i)
9628: AND application_id = g_application_id;
9629: */
9630: FORALL i IN 1..g_err_count
9631: UPDATE xla_ae_headers xah1 -- 4262811a
9632: SET accounting_entry_status_code = C_AE_STATUS_RELATED
9633: -- Bug 5056632. updates group_id back to Null if je is invalid
9634: ,group_id = NULL
9635: WHERE xah1.accounting_entry_status_code <> C_AE_STATUS_INVALID
9636: AND xah1.event_id = g_err_event_ids(i)
9637: AND xah1.application_id = g_application_id
9638: AND xah1.parent_ae_line_num IS NULL -- 4262811a Existing logic, and this works for Accrual Reversal.
9639: AND NOT EXISTS (SELECT 1 -- 4262811a Do not update MPA's original entry, it is set correctly above.
9640: FROM xla_ae_headers xah2
9641: WHERE xah2.event_id = xah1.event_id -- 5231063 g_err_event_ids(i)
9642: AND xah2.application_id = xah1.application_id -- 5231063 g_application_id
9643: AND xah2.ae_header_id = g_err_hdr_ids(i)
9644: AND xah2.parent_ae_line_num IS NOT NULL);
9643: AND xah2.ae_header_id = g_err_hdr_ids(i)
9644: AND xah2.parent_ae_line_num IS NOT NULL);
9645:
9646: IF (C_LEVEL_EVENT >= g_log_level) THEN
9647: trace(p_msg => '# xla_ae_headers updated to C_AE_STATUS_RELATED = '||SQL%ROWCOUNT,
9648: p_module => l_log_module,
9649: p_level => C_LEVEL_EVENT);
9650: END IF;
9651: --------------------------------------------------------------------------------------
9651: --------------------------------------------------------------------------------------
9652: -- 4262811a Update MPA rows ----------------------------------------------------------
9653: --------------------------------------------------------------------------------------
9654: FORALL i IN 1..g_err_count
9655: UPDATE xla_ae_headers xah1
9656: SET (accounting_entry_status_code, group_id) =
9657: (SELECT DECODE(xah2.accounting_entry_status_code
9658: ,'D',xah1.accounting_entry_status_code
9659: ,'F',xah1.accounting_entry_status_code
9660: ,C_AE_STATUS_RELATED)
9661: --
9662: -- Bug 5056632. updates group_id back to Null if je is invalid
9663: ,NULL
9664: FROM xla_ae_headers xah2
9665: WHERE xah2.event_id = g_err_event_ids(i)
9666: AND xah2.application_id = g_application_id
9667: AND xah2.ae_header_id = xah1.parent_ae_header_id
9668: AND xah2.parent_ae_line_num IS NULL)
9682: UPDATE xla_events_gt evt -- 4262811a
9683: SET process_status_code =
9684: (SELECT DECODE(xah2.parent_ae_line_num,NULL,'I' -- 4262811a Status of MPA rows does not affect event status
9685: , evt.process_status_code)
9686: FROM xla_ae_headers xah2
9687: WHERE xah2.event_id = g_err_event_ids(i)
9688: AND xah2.application_id = g_application_id
9689: AND xah2.ae_header_id = g_err_hdr_ids(i))
9690: WHERE event_id = g_err_event_ids(i)
9722: PROCEDURE undo_funds_reserve
9723: IS
9724: CURSOR c_failed_je IS
9725: SELECT distinct xah.ledger_id, xah.event_id
9726: FROM xla_ae_headers xah
9727: , xla_events_gt xeg
9728: WHERE xeg.event_id = xah.event_id
9729: AND xah.application_id = g_application_id
9730: AND xeg.process_status_code = 'I';