[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE psa_xla_accounting_errors p
WHERE p.entity_code = p_xla_transaction_entities.entity_code
AND NVL(p.source_id_int_1, -1) = NVL(p_xla_transaction_entities.source_id_int_1, -1)
AND NVL(p.source_id_int_2, -1) = NVL(p_xla_transaction_entities.source_id_int_2, -1)
AND NVL(p.source_id_int_3, -1) = NVL(p_xla_transaction_entities.source_id_int_3, -1)
AND NVL(p.source_id_int_4, -1) = NVL(p_xla_transaction_entities.source_id_int_4, -1)
AND NVL(p.source_id_char_1, ' ') = NVL(p_xla_transaction_entities.source_id_char_1, ' ')
AND NVL(p.source_id_char_2, ' ') = NVL(p_xla_transaction_entities.source_id_char_2, ' ')
AND NVL(p.source_id_char_3, ' ') = NVL(p_xla_transaction_entities.source_id_char_3, ' ')
AND NVL(p.source_id_char_4, ' ') = NVL(p_xla_transaction_entities.source_id_char_4, ' ');
FOR entity_rec IN (SELECT t.*
FROM xla_events e,
xla_transaction_entities t,
psa_bc_xla_events_gt p
WHERE p.event_id = e.event_id
AND e.entity_id = t.entity_id) LOOP
psa_xla_error_cleanup (entity_rec);
that inserts the PSA/XLA error into the table.
*/
PROCEDURE psa_xla_error
(
p_psa_xla_accounting_errors IN psa_xla_accounting_errors%ROWTYPE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT psa_xla_accounting_errors_s.nextval
INTO l_psa_xla_accounting_errors.accounting_error_id
FROM DUAL;
/*INSERT INTO psa_xla_accounting_errors
VALUES l_psa_xla_accounting_errors;*/
INSERT INTO psa_xla_accounting_errors
( ACCOUNTING_ERROR_ID,
APPLICATION_ID,
LEDGER_ID,
ENTITY_CODE,
ENTITY_ID,
EVENT_DATE ,
EVENT_ID,
TRANSACTION_NUMBER,
AE_HEADER_ID,
AE_LINE_NUM,
SOURCE_ID_INT_1,
SOURCE_ID_INT_2,
SOURCE_ID_INT_3,
SOURCE_ID_INT_4,
SOURCE_ID_CHAR_1,
SOURCE_ID_CHAR_2,
SOURCE_ID_CHAR_3,
SOURCE_ID_CHAR_4,
MESSAGE_CODE,
MESSAGE_NUM,
ENCODED_MSG,
AUDSID,
CREATION_DATE,
CREATED_BY
)
VALUES ( l_psa_xla_accounting_errors.ACCOUNTING_ERROR_ID,
l_psa_xla_accounting_errors.APPLICATION_ID ,
l_psa_xla_accounting_errors.LEDGER_ID,
l_psa_xla_accounting_errors.ENTITY_CODE,
l_psa_xla_accounting_errors.ENTITY_ID ,
l_psa_xla_accounting_errors.EVENT_DATE ,
l_psa_xla_accounting_errors.EVENT_ID,
l_psa_xla_accounting_errors.TRANSACTION_NUMBER,
l_psa_xla_accounting_errors.AE_HEADER_ID,
l_psa_xla_accounting_errors.AE_LINE_NUM ,
l_psa_xla_accounting_errors.SOURCE_ID_INT_1,
l_psa_xla_accounting_errors.SOURCE_ID_INT_2,
l_psa_xla_accounting_errors.SOURCE_ID_INT_3,
l_psa_xla_accounting_errors.SOURCE_ID_INT_4,
l_psa_xla_accounting_errors.SOURCE_ID_CHAR_1,
l_psa_xla_accounting_errors.SOURCE_ID_CHAR_2,
l_psa_xla_accounting_errors.SOURCE_ID_CHAR_3,
l_psa_xla_accounting_errors.SOURCE_ID_CHAR_4 ,
l_psa_xla_accounting_errors.MESSAGE_CODE ,
l_psa_xla_accounting_errors.MESSAGE_NUM,
l_psa_xla_accounting_errors.ENCODED_MSG,
l_psa_xla_accounting_errors.AUDSID,
l_psa_xla_accounting_errors.CREATION_DATE ,
l_psa_xla_accounting_errors.CREATED_BY);
FOR event_rec IN (SELECT e.event_id,
t.entity_id,
t.entity_code,
t.source_id_int_1,
t.source_id_int_2,
t.source_id_int_3,
t.source_id_int_4,
t.source_id_char_1,
t.source_id_char_2,
t.source_id_char_3,
t.source_id_char_4,
t.application_id,
t.transaction_number,
e.event_date,
t.ledger_id
FROM xla_events e,
xla_transaction_entities t,
psa_bc_xla_events_gt p
WHERE p.event_id = e.event_id
AND e.entity_id = t.entity_id
AND p.event_id = NVL(p_event_id, p.event_id)) LOOP
l_psa_xla_accounting_errors.message_code := p_message_code;
FOR error_rec IN (SELECT e.entity_id,
e.event_id,
e.application_id,
er.message_number,
er.encoded_msg,
t.source_id_int_1,
t.source_id_int_2,
t.source_id_int_3,
t.source_id_int_4,
t.source_id_char_1,
t.source_id_char_2,
t.source_id_char_3,
t.source_id_char_4,
t.entity_code,
t.transaction_number,
t.ledger_id,
e.event_date,
er.ae_header_id,
er.ae_line_num
FROM xla_accounting_errors er,
psa_bc_xla_events_gt p,
xla_events e,
xla_transaction_entities t
WHERE er.event_id = p.event_id
AND e.event_id = p.event_id
AND t.entity_id = e.entity_id) LOOP
p_error_found := 'Y';
FOR xla_rec IN (SELECT ru.compile_status_code,
e.entity_id,
e.event_id,
ru.name product_rule_name,
ru.product_rule_type_code product_rule_owner,
t.ledger_id
FROM xla_events e,
xla_transaction_entities t,
psa_bc_xla_events_gt p,
gl_ledgers g,
xla_acctg_methods_fvl m,
xla_acctg_method_rules_fvl r,
xla_product_rules_fvl ru
WHERE p.event_id = e.event_id
AND e.entity_id = t.entity_id
AND t.ledger_id = g.ledger_id
AND g.sla_accounting_method_code = m.accounting_method_code
AND g.sla_accounting_method_type = m.accounting_method_type_code
AND r.accounting_method_code = m.accounting_method_code
AND r.accounting_method_type_code = m.accounting_method_type_code
AND r.application_id = t.application_id
AND r.product_rule_code = ru.product_rule_code
AND r.product_rule_type_code = ru.product_rule_type_code
AND e.event_date BETWEEN r.start_date_active AND NVL(r.end_date_active, e.event_date+1)) LOOP
psa_utils.debug_other_string(g_state_level,l_path_name, 'compile_status_code='||xla_rec.compile_status_code);
PROCEDURE psa_acctg_errors_insert(psa_events IN psa_events_table, psa_acctg_errors IN psa_acctg_errors_table) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_path_name := g_path_name||'.psa_acctg_errors_insert';
psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure psa_acctg_errors_insert');
DELETE FROM psa_bc_accounting_errors
WHERE event_id = psa_events(i);
psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows deleted from psa_bc_accounting_erros: '||SQL%ROWCOUNT);
INSERT INTO psa_bc_accounting_errors VALUES psa_acctg_errors(j);
psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows inserted into psa_bc_accounting_erros: '||SQL%ROWCOUNT);
psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure psa_acctg_errors_insert');
END psa_acctg_errors_insert;
SELECT XE.entity_id entity_id
FROM XLA_ENTITY_EVENTS_V XE,PSA_BC_XLA_EVENTS_GT BCE
WHERE XE.event_id = BCE.event_id
AND XE.event_status_code <> 'P'
AND XE.application_id = p_application_id
GROUP BY xe.entity_id;
SELECT nvl(sum(decode(upper(result_code),'FATAL',1)),0) status_fatal_count,
sum(decode(upper(result_code),'XLA_ERROR',1)) status_xla_err_count,
sum(decode(upper(result_code),'FAIL',1)) status_fail_count,
sum(decode(upper(result_code),'PARTIAL',1)) status_partial_count,
sum(decode(upper(result_code),'ADVISORY',1)) status_advisory_count,
nvl(sum(decode(upper(result_code),'SUCCESS',1)),0) status_success_count,
nvl(sum(decode(upper(result_code),'XLA_NO_JOURNAL',1)),0) status_nojournal_count
FROM PSA_BC_XLA_EVENTS_GT;
SELECT 'Y'
FROM PSA_BC_XLA_EVENTS_GT a
WHERE not exists (SELECT 'x'
FROM xla_ae_headers b
WHERE b.event_id = a.event_id);
SELECT *
FROM psa_bc_xla_events_gt;
SELECT event_id
FROM psa_bc_xla_events_gt;
SELECT xla_evnt.EVENT_ID,
xla_evnt.ENTITY_ID,
xla_evnt.APPLICATION_ID,
xla_err.AE_HEADER_ID,
xla_err.AE_LINE_NUM,
xla_evnt.TRANSACTION_DATE,
fnd_mesg.MESSAGE_NUMBER,
fnd_mesg.MESSAGE_NAME,
xla_err.ENCODED_MSG,
xla_err.ERROR_SOURCE_CODE,
xla_evnt.LEDGER_ID,
xla_evnt.LEGAL_ENTITY_ID,
xla_evnt.transaction_number DOCUMENT_REFERENCE,
NULL BATCH_REFERENCE,
to_char(xla_evnt.event_id) LINE_REFERENCE,
SYSDATE CREATION_DATE,
'Y' XLA_ERROR_FLAG
FROM psa_bc_xla_events_gt psa_evnt,
xla_events_gt xla_evnt,
xla_accounting_errors xla_err,
fnd_new_messages fnd_mesg
WHERE psa_evnt.event_id = xla_evnt.event_id
AND xla_evnt.event_id = xla_err.event_id
AND fnd_mesg.application_id = 602
AND DECODE(xla_err.message_number, 0, -99, xla_err.message_number) = fnd_mesg.message_number (+)
AND userenv('LANG') = fnd_mesg.language_code (+);
SELECT count(*) INTO l_count
FROM psa_bc_xla_events_gt;
SELECT count(*) INTO l_count
FROM psa_bc_xla_events_gt;
UPDATE psa_bc_xla_events_gt
SET result_code = 'XLA_UNPROCESSED';
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
DELETE from XLA_ACCT_PROG_EVENTS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from XLA_ACCT_PROG_EVENTS_GT table before insertion: ' || SQL%ROWCOUNT );
DELETE from xla_ae_headers_gt;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_ae_headers_gt table before insertion: ' || SQL%ROWCOUNT);
DELETE from xla_ae_lines_gt;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_ae_lines_gt table before insertion: ' || SQL%ROWCOUNT);
DELETE from xla_validation_lines_gt;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_validation_lines_gt table before insertion: ' || SQL%ROWCOUNT);
DELETE from xla_evt_class_orders_gt;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_evt_class_orders_gt; table before insertion: ' || SQL%ROWCOUNT);
DELETE from psa_option_details_gt;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from psa_option_details_gt table before insertion: ' || SQL%ROWCOUNT);
DELETE from psa_bc_alloc_gt;
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from psa_bc_alloc_gt table before insertion: ' || SQL%ROWCOUNT);
INSERT into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
SELECT event_id FROM psa_bc_xla_events_gt;
psa_utils.debug_other_string(g_state_level,l_path_name, 'Number of rows in inserted into XLA_ACCT_PROG_EVENTS_GT table: ' || SQL%ROWCOUNT );
psa_acctg_errors_insert(l_psa_events,l_psa_acctg_errors);
UPDATE psa_bc_xla_events_gt
SET result_code = 'XLA_ERROR';
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
UPDATE psa_bc_xla_events_gt
SET result_code = 'XLA_NO_JOURNAL'
WHERE result_code = 'XLA_UNPROCESSED';
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
UPDATE psa_bc_xla_events_gt
SET result_code = 'XLA_ERROR';
psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
FOR x IN (SELECT * FROM psa_bc_xla_events_gt) LOOP
psa_utils.debug_other_string(g_state_level,l_path_name,
'PSA gt event_id'|| x.event_id || 'PSA gt result_code' || x.result_code);
FOR event_list_rec IN (SELECT *
FROM PSA_BC_XLA_EVENTS_GT
WHERE upper(result_code) = 'XLA_NO_JOURNAL') LOOP
fnd_message.set_name ('PSA','PSA_XLA_NO_JOURNAL');
SELECT '1'
FROM xla_transaction_entities xte,
xla_ae_headers xah,
xla_distribution_links xdl,
xla_events xe
WHERE xte.application_id = cp_appl_id
AND xte.entity_code = cp_entity_code
AND xte.source_id_int_1 = cp_source_id_int_1
AND xte.entity_id = xah.entity_id
AND xah.event_id = xdl.event_id
AND xdl.source_distribution_type = cp_source_dist_type
AND xdl.source_distribution_id_num_1 = cp_source_dist_id_num_1
AND xah.event_id = xe.event_id
AND xe.budgetary_control_flag = 'Y';
SELECT NVL(xdl.unrounded_accounted_dr, 0), NVL(xdl.unrounded_accounted_cr, 0),
xe.event_type_code
FROM xla_transaction_entities xte,
xla_ae_headers xah,
xla_events xe,
xla_distribution_links xdl
WHERE xte.application_id = cp_appl_id
AND xte.entity_code = cp_entity_code
AND xte.source_id_int_1 = cp_source_id_int_1
AND xte.entity_id = xah.entity_id
AND xah.event_id = xe.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xah.application_id = cp_appl_id
AND xe.budgetary_control_flag = 'Y'
AND xdl.source_distribution_type = cp_source_dist_type
AND xdl.source_distribution_id_num_1 = cp_source_dist_id_num_1
ORDER BY xe.event_id DESC;
SELECT min(creation_date) INTO l_dist_creation_date
FROM po_req_distributions_all
WHERE distribution_id = p_distribution_id;
SELECT min(creation_date) INTO l_dist_creation_date
FROM po_distributions_all
WHERE po_distribution_id = p_distribution_id;
SELECT min(creation_date) INTO l_dist_creation_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_distribution_id;
DELETE FROM XLA_AE_HEADERS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '1 XLA_AE_HEADERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_AE_LINES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '2 XLA_AE_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_VALIDATION_HDRS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '3 XLA_VALIDATION_HDRS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_VALIDATION_LINES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '4 XLA_VALIDATION_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_CTRL_CTRBS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '5 XLA_BAL_CTRL_CTRBS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_PERIOD_STATS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '6 XLA_BAL_PERIOD_STATS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_RECREATE_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '7 XLA_BAL_RECREATE_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_ANACRI_LINES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '8 XLA_BAL_ANACRI_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '9 XLA_BAL_ANACRI_CTRBS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_SYNCHRONIZE_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '10 XLA_BAL_SYNCHRONIZE_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_STATUSES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '11 XLA_BAL_STATUSES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_CTRL_LINES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '12 XLA_BAL_CTRL_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVENTS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '13 XLA_EVENTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVT_CLASS_SOURCES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '14 XLA_EVT_CLASS_SOURCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVT_CLASS_ORDERS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '15 XLA_EVT_CLASS_ORDERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TAB_ERRORS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '16 XLA_TAB_ERRORS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_SEQ_JE_HEADERS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '17 XLA_SEQ_JE_HEADERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TAB_NEW_CCIDS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '18 XLA_TAB_NEW_CCIDS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EXTRACT_OBJECTS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '19 XLA_EXTRACT_OBJECTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_REFERENCE_OBJECTS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '20 XLA_REFERENCE_OBJECTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TRANSACTION_ACCTS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '21 XLA_TRANSACTION_ACCTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_UPG_LINE_CRITERIA_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '22 XLA_UPG_LINE_CRITERIA_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TRIAL_BALANCES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '23 XLA_TRIAL_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_ACCT_PROG_EVENTS_GT; psa_utils.debug_other_string(g_state_level,l_path_name, '24 XLA_ACCT_PROG_EVENTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_ACCT_PROG_DOCS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '25 XLA_ACCT_PROG_DOCS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_MERGE_SEG_MAPS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '26 XLA_MERGE_SEG_MAPS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVENTS_INT_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '27 XLA_EVENTS_INT_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_REPORT_BALANCES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '28 XLA_REPORT_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TB_BALANCES_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '29 XLA_TB_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_AC_CTRBS_GT;
psa_utils.debug_other_string(g_state_level,l_path_name, '30 XLA_BAL_AC_CTRBS_GT : Deleted Row count :'||SQL%ROWCOUNT);