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

Browser not supported