ERP BI SQL query for custom import response or callback

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:

1. xla_transaction_lines
2. xla_events
3. 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