Important Queries

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))

QUERY TO CHECK FOR DUPLICATE SETTLEMENT

  select * from bulk_settlement_records b where transaction_time_end in

  (select transaction_time_end from bulk_settlement_records a where  BULK_SETTLEMENT_KEY IN('221001200000a3402210041013260013',

'22100207000023242210041014460034',

'221002090000f8f52210041014530038',

'22100210000055cc2210041014580040',

'2210021100009a4f2210041015090042',

'22100212000026932210041015200044',

'221002130000fc9d2210041015320046',

'221002140000a8e42210041015420048',

'221002150000d4472210041015520050',

'221002160000ee8b2210041016070052',

'221002170000bab22210041016330054',

'221002180000f7352210041016450056',

'221002190000221c2210041016560058',

'221003090000fe2f2210041017190087',

'22100310000093242210041017200088',

'221003110000cb1d2210041017320090',

'221003120000c0692210041017470092',

'221003130000a2c12210041018010094',

'22100314000048162210041018120096',

'22100315000003a92210041018320098',

'221003160000c1c32210041018450100',

'22100317000096112210041018590102',

'221003190000f4a22210041019220106',

'2210032100001d602210041019370110',

'2210040700007f1f2210041019460131',

'221004080000312a2210041019520132')

 )

  and transaction_time_end<=transaction_time_end and transaction_time_begin>=

 dateadd(hour,-1,transaction_time_begin)

and transaction_count>31/** THIS LINE SHOULD BE INCLUDED TO REMOVE TRANSACTIONS MIGRATED FROM THE SAME TIME PERIOD BUT ON ANOTHER DAY**/

 --and cast (created_at as date) =cast(transaction_time_begin as date) 

  order by

  transaction_time_begin

QUERY TO CHECK FOR TRANSACTION STATUS AND CORRECT MASKED PAN FROM BANK_TRANSFER_TRANSACTION

SELECT
a.[request_time],
a.[response_time],
a.[masked_pan],
a.[stan],
a.[message_name],
a.[response_code],
cast(a.[request_amount] as money)/100 as request_amount,
a.[retrieval_reference_number],
a.[card_acceptor_id],
a.[terminal_id],
a.[card_acceptor_location],
a.[mti],
a.[transaction_search_key]

FROM
[aptent_db].[dbo].[transaction_record] a

INNER JOIN
[aptent_db].[dbo].[bank_transfer_transaction] b

ON
a.[id] = b.[transaction_record_id]

WHERE
concat(a.[terminal_id],a.[retrieval_reference_number]) IN ('2TFD0UU6000172503150')