QUERIES
Re-open Disputes
UPDATE[dcir_db].[dbo].[backoffice_dispute_transactions]
set status = 'PENDING', resolution_status = 'PENDING', tat_expiry_date = '2023-06-12 17:15:05.970', status_log_info = ''
where log_code in ('ABN|20220830|0105')
and resolution_status='DECLINED'
Bulk summary report
/****** Script for SelectTopNRows command from SSMS ******/
SELECT bulk_settlement_key as 'BULK ID',transaction_time_begin as 'TRANSACTION START TIME',transaction_time_end as 'TRANSACTION END TIME',transaction_total_amount/100 as 'TOTAL AMOUNT',transaction_merchant_amount/100 as 'SETTLEMENT AMOUNT', transaction_charge_amount/100 as 'TOTAL CHARGE'
FROM [dcir_db].[dbo].[bulk_settlement_records]
Where created_at >= '2023-05-13 00:00:00.000' and created_at <'2023-05-14 00:00:00.000'
--id in(33944,33943,33942)
--bulk_settlement_key in ('221002020000a6eb2210041014420024','2210020300007c942210041014420026','221002050000a2792210041014420030')
Backoffice record report
SELECT [masked_pan], [rrn],stan,terminal_id,
cast (a.[transaction_amount] as money)/100 as Transaction_amount, cast(a.[charge_amount] as money)/100 AS CHARGE_AMOUNT,a.transaction_time,
card_acceptor_id,card_acceptor_location,
bsr.bulk_settlement_key FROM [dcir_db].[dbo].[backoffice_transaction_records] a inner join
bulk_settlement_records bsr on a.bulk_settlement_record_id=bsr.id
WHERE settlement_time >='2023-05-13 00:00:00.000' and settlement_time<'2023-05-14 01:00:00.000' and response_code='00'
Transactional report including bulk and line items
select sum(ba.[transaction_amount]) as amount,
ba.bulk_settlement_record_id, COUNT(ba.bulk_settlement_record_id) AS BULK_COUNT,sum(ba.charge_amount)as charge_amount, ((sum(ba.[transaction_amount]))-(sum(ba.charge_amount)) ) FROM [dcir_db].[dbo].[backoffice_transaction_records] ba
inner join bulk_settlement_records bsr on bsr.id=ba.bulk_settlement_record_id
where bsr.status='failed'
--settlement_time >='2023-03-12 02:00:00.000' and settlement_time<'2023-03-13 02:00:00.000'
group by bulk_settlement_record_id
order by bulk_settlement_record_id DESC
select transaction_total_amount,ID, transaction_count,transaction_time_begin,transaction_time_end FROM [dcir_db].[dbo].[bulk_settlement_records]
where transaction_time_begin >= '2023-03-12 02:00:00.000' and transaction_time_begin<'2023-03-13 02:00:00.000'
ORDER BY transaction_time_begin DESC
Dispute fetch by status
SELECT MASKED_PAN,RRN,STAN,terminal_id,
BDT.transaction_search_key,
(CAST (transaction_amount AS MONEY)/100) AS AMOUNT,
card_acceptor_id,response_code,transaction_time,
transaction_response_code,log_code,
BDT.status,resolution_status,DRT.STATUS AS REFUND_STATUS,customer_account_number,
customer_account_name,logged_by,resolved_by,comment
,BDT.created_on AS LOG_DATE,tat_expiry_date
FROM [dcir_db].[dbo].[backoffice_dispute_transactions] BDT
LEFT JOIN dispute_transaction_details DTD
ON DTD.transaction_search_key=bdt.transaction_search_key LEFT JOIN dispute_refund_transactions DRT ON BDT.BACKOFFICE_TRANSACTION_ID=DRT.backoffice_transaction_id
where BDT.status='PENDING'
--and created_on > '2023-03-22 00:00:00.000'
Transaction failure/success check per minute
select COUNT(*),DATEPART(MINUTE, request_time ) AS DATEPARTING,
sum(case when response_code in ('91','06','96') then 1 else 0 end) as failed,
sum(case when response_code not in ('91','06','96') then 1 else 0 end) as success
from [aptent].[dbo].[transaction_record]
where REQUEST_TIME >='2023-05-27 08:00:00' AND REQUEST_TIME <='2023-05-27 09:00:00'
GROUP BY DATEPART(MINUTE, request_time )
ORDER BY DATEPARTING asc
Transaction Migration
INSERT INTO dcir_db.dbo.backoffice_transaction_records(
transaction_amount,
terminal_id,
masked_pan,
card_acceptor_id,
response_code,
stan,
transaction_time,
request_time,
aptent_id,
transaction_search_key,
rrn,
hashed_pan,
processing_code,
card_acceptor_location,
transaction_currency_code,
transaction_date
)
SELECT t.request_amount, t.terminal_id,
t.masked_pan,t.card_acceptor_id,CASE WHEN t.response_code ='09' THEN '91' ELSE T.RESPONSE_CODE END, t.stan,
CASE WHEN t.response_time IS NULL THEN T.REQUEST_TIME ELSE T.RESPONSE_TIME END ,t.request_time,t.id,t.transaction_search_key,
retrieval_reference_number,hashed_pan,processing_code,card_acceptor_location,
transaction_currency_code,transaction_date
FROM aptent_db.dbo.transaction_record t
WHERE T.transaction_search_key NOT IN(SELECT transaction_search_key FROM backoffice_transaction_records) AND T.REQUEST_TIME <'2023-08-11 00:00:00'
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000)
[request_time]
,[response_time]
,[masked_pan]
,[stan]
,[message_name]
,[response_code]
,[request_amount]
,[retrieval_reference_number]
,[card_acceptor_id]
,[terminal_id]
,[card_acceptor_location]
,[mti]
FROM [aptent_hsm].[dbo].[transaction_record] WHERE RESPONSE_CODE='91' AND REQUEST_SENT=1 ORDER BY ID DESC
--AND '2023-07-19 16:30:00'
List of merchants tied to ptsps
select merchant_name,case when settlement_participant_id in(4) then 'Itex' when settlement_participant_id in(6) then 'Global accelerex' end as PTSP
from merchants m inner join [merchants_settlement_participants] msp on m.id=msp.merchant_id where settlement_participant_id in(4,6)
Participant amount for merchant
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000)cast( [amount] as money)/100 as amount
,[debit_account]
,[credit_account]
,[bulk_settlement_record_id]
,spr.[status]
,spr.[created_at]
,spr.[completed_at]
,[narration]
,[credit_account_name]
,[debit_account_name], bsr.bulk_settlement_key
FROM [dcir_db].[dbo].[settlement_payment_requests] spr inner join bulk_settlement_records bsr on bsr.id=spr.bulk_settlement_record_id where settlement_participant_id=3 and bulk_settlement_record_id in(SELECT id
FROM [dcir_db].[dbo].[bulk_settlement_records]
where settlement_type!='HOURLY' and created_at >= '2023-03-30 00:00:00.000' and created_at< '2023-03-31 00:00:00.000')
Pending transactions script
SELECT
[masked_pan]
,[rrn]
,[stan]
,[terminal_id]
,bdta.[transaction_time]
,(cast ([amount] as money)/100) as amount
,[search_key]
,[account_to_be_credited]
,[dispute_log_code], [dispute_creation_status]
,[creation_message],
bdt.status,bdt.resolution_status,bdt.resolved_by
,[batch_id]
FROM [dcir_db].[dbo].[bulk_dispute_transactions] bdta left join backoffice_dispute_transactions bdt on bdta.dispute_log_code=bdt.log_code
where batch_id=’51c83f’ –c85f02
Archiving Scripts
SELECT * INTO [aptent_hsm].[dbo].[transaction_record_BKUP_25112023]
FROM [aptent_hsm].[dbo].[transaction_record]
WHERE REQUEST_TIME < DATEADD(DAY,-1,CAST (GETDATE() AS DATE))
---Archive transaction reversal attempt table and delete values archived on reversal attempt TABLE first due to foreign key constraint on transaction record table
SELECT * INTO [aptent_hsm].[dbo].[transaction_reversal_attempt_bkup_25_11_2023]
FROM [aptent_hsm].[dbo].[transaction_reversal_attempt]
WHERE transaction_record_id in(SELECT id
FROM [aptent_hsm].[dbo].[transaction_record]
WHERE REQUEST_TIME < DATEADD(DAY,-1,CAST (GETDATE() AS DATE)) )
-----delete archived records from transaction_reversal table--
delete FROM [aptent_hsm].[dbo].[transaction_reversal_attempt]
WHERE transaction_record_id in(SELECT id
FROM [aptent_hsm].[dbo].[transaction_record]
WHERE REQUEST_TIME < DATEADD(DAY,-1,CAST (GETDATE() AS DATE)) )
----delete archived records from transaction_records table
DELETE FROM [aptent_hsm].[dbo].[transaction_record]
WHERE REQUEST_TIME <DATEADD(DAY,-1,CAST (GETDATE() AS DATE))
----archive card account table and delete the records after archiving---
SELECT * INTO [aptent_hsm].[dbo].[CARD_ACCOUNT_BKUP_25112023]
FROM [aptent_hsm].[dbo].[CARD_ACCOUNT]
WHERE CREATED_ON < DATEADD(DAY,-6,CAST (GETDATE() AS DATE))
DELETE FROM [aptent_hsm].[dbo].[CARD_ACCOUNT]
WHERE CREATED_ON <DATEADD(DAY,-6,CAST (GETDATE() AS DATE))