Here, we are using a long sql query to fetch all the parameters respect to import callback which we will use to validate later.
Tables used:
- xla_transaction_lines
- xla_events
- xla_accounting_errors
How the tables look like:
Xla_events:
Xla_transaction_lines:
Xla_accounting_errors:
How the import callback output looks:
BI report SQL Query:
Select 'Source: '|| LISTAGG(a.event_type_code, ':') within group ( order by a.event_type_code) as column1
From (Select distinct xe.event_type_code from xla_transaction_lines xtl, xla_events xe
Where xtl.event_id = xe.event_id
And xe.data_set_name = 'XLA_LOAD_' || :p_import_request_id
And xe.event_type_code is not null) a
//this code is used to get the event type code for the matched import request id. The event type code can have multiple values so used LISTAGG to make a row of the even types.
Union all
Select 'Total number of transactions reveived' || NVL((select received_rec.received_count
From (select count(1) as received_count from xla_transaction_lines xtl where 1=1 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) received_rec
Where received_rec.received_count <> 0),0)
As count_rcv from dual
Union all
Select 'Total number of debit transactions reveived' || NVL((select debit_rec.debit_count
From (select count(1) as debit_count from xla_transaction_lines xtl where 1=1 and xtl.number1 >=0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec
Where debit_rec.debit_count <> 0),0)
As debit_count_rcv from dual
Union all
Select 'Total number of credit transactions reveived' || NVL((select credit_rec.credit_count
From (select count(1) as credit_count from xla_transaction_lines xtl where 1=1 and xtl.number1 <0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec
Where credit_rec.credit_count <> 0),0)
As credit_count_rcv from dual
Union all
Select 'Total amount of debit transactions reveived' || NVL((select round(debit_rec.debit_rev_amount,2)
From (select NVL(sum(xtl.number1),0)as debit_rcv_amount from xla_transaction_lines xtl where 1=1 and xtl.number1 > 0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec
Where debit_rec.debit_rcv_amount <> 0),0)
As debit_amount_rcv from dual
Union all
Select 'Total amount of credit transactions reveived' || NVL((select round(credit_rec.credit_rev_amount,2)
From (select NVL(sum(xtl.number1),0)as credit_rcv_amount from xla_transaction_lines xtl where 1=1 and xtl.number1 < 0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec
Where credit_rec.credit_rcv_amount <> 0),0)
As credit_amount_rcv from dual
Union all
Select 'Total No of Transactions Processed' ||
NVL((select process_rec.process_count process_trans
From (select count(1) as process_count
From xla_transaction_lines xtl where 1=1 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code='P'
And xe.process_status_code='P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) process_rec
Where process_rec.process_count <> 0),0)
As tot_process_count from dual
Union all
Select 'Total No of Debit Transactions Processed' ||
NVL((select debit_rec.debit_count
From (select count(1) as debit_count
From xla_transaction_lines xtl where 1=1 and xtl.number1>=0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code='P'
And xe.process_status_code='P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec
Where debit_rec.debit_count <> 0),0)
As tot_debit_count from dual
Union all
Select 'Total Amount of Debit Transactions Processed' ||
NVL((select round( debit_amt_rec.debit_line_amount,2) debit_line_amount
From (select NVL(SUM(xtl.number1),0) as debit_line_amount
From xla_transaction_lines xtl where 1=1 and xtl.number1>0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code='P'
And xe.process_status_code='P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_amt_rec
Where debit_amt_rec.debit_line_amount<> 0),0)
As tot_debit_amount from dual
Union all
Select 'Total No of credit Transactions Processed' ||
NVL((select credit_rec.credit_count
From (select count(1) as credit_count
From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code='P'
And xe.process_status_code='P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec
Where credit_rec.credit_count <> 0),0)
As tot_credit_count from dual
Union all
Select 'Total Amount of Credit Transactions Processed' ||
NVL((select round( credit_amt_rec.credit_line_amount,2) credit_line_amount
From (select NVL(SUM(xtl.number1),0) as credit_line_amount
From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code='P'
And xe.process_status_code='P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_amt_rec
Where credit_amt_rec.credit_line_amount<> 0),0)
As tot_credit_amount from dual
Union all
Select 'Total No of Debit Transactions Not Processed' ||
NVL((select debit_rec.debit_count
From (select count(1) as debit_count
From xla_transaction_lines xtl where 1=1 and xtl.number1>=0 and exists (select 1 from xla_events xe
where xtl.event_id = xe.event_id
And xe.event_status_code<>'P'
And xe.process_status_code<>'P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_rec
Where debit_rec.debit_count <> 0),0)
As tot_debit_count from dual
Union all
Select 'Total Amount of Debit Transactions Not Processed' ||
NVL((select round( debit_amt_rec.debit_line_amount,2) debit_line_amount
From (select NVL(SUM(xtl.number1),0) as debit_line_amount
From xla_transaction_lines xtl where 1=1 and xtl.number1>0 and exists (select 1 from xla_events xe
where xtl.event_id = xe.event_id
And xe.event_status_code<>'P'
And xe.process_status_code<>'P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) debit_amt_rec
Where debit_amt_rec.debit_line_amount<> 0),0)
As tot_debit_amount from dual
Union all
Select 'Total No of credit Transactions Not Processed' ||
NVL((select credit_rec.credit_count
From (select count(1) as credit_count
From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code<>'P'
And xe.process_status_code<>'P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_rec
Where credit_rec.credit_count <> 0),0)
As tot_credit_count from dual
Union all
Select 'Total Amount of Credit Transactions Not Processed' ||
NVL((select round( credit_amt_rec.credit_line_amount,2) credit_line_amount
From (select NVL(SUM(xtl.number1),0) as credit_line_amount
From xla_transaction_lines xtl where 1=1 and xtl.number1<0 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id
And xe.event_status_code<>'P'
And xe.process_status_code<>'P'
xe.data_set_name='XLA_LOAD_' || :p_import_request_id)) credit_amt_rec
Where credit_amt_rec.credit_line_amount<> 0),0)
As tot_credit_amount from dual
Union all
Select 'Total number of Errored transactions:' || NVL((select error_rec.error_count
From (select count(1) as error_count from xla_transaction_lines xtl where 1=1 and exists (select 1 from xla_events xe where xtl.event_id = xe.event_id and xe.data_set_name='XLA_LOAD_' || :p_import_request_id) and exists (select 1 from xla_accounting_errors xae where xae.event_id=xtl.event_id) ) error_rec
Where error_rec.error_count <> 0),0)
As error_count from dual
