You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
215 lines
7.0 KiB
215 lines
7.0 KiB
CREATE OR ALTER PROC [dbo].[proc_CancelTransaction] (
|
|
@flag VARCHAR(50)
|
|
,@controlNo VARCHAR(20) = NULL
|
|
,@user VARCHAR(30) = NULL
|
|
,@cancelReason VARCHAR(200) = NULL
|
|
,@apiSuccess CHAR(1) = NULL
|
|
,@payoutPartner VARCHAR(20) = NULL
|
|
)
|
|
AS
|
|
|
|
DECLARE
|
|
@tranStatus VARCHAR(20) = NULL,
|
|
@payStatus VARCHAR(50) = NULL,
|
|
@tranType CHAR(1) = NULL,
|
|
@isPatner CHAR(1) = NULL,
|
|
@message VARCHAR(200) = NULL,
|
|
@tranId INT = NULL
|
|
|
|
|
|
SET NOCOUNT ON
|
|
SET XACT_ABORT ON
|
|
|
|
|
|
DECLARE
|
|
@code VARCHAR(50)
|
|
,@userName VARCHAR(50)
|
|
,@password VARCHAR(50)
|
|
,@lockStatus VARCHAR(20)
|
|
|
|
DECLARE @controlNoEncrypted VARCHAR(20),@refund CHAR(1)
|
|
SELECT @controlNoEncrypted = dbo.FNAEncryptString(@controlNo)
|
|
/*
|
|
--393864 DUTCH-BANGLA BANK LTD.
|
|
--415208 Xpress Money Settling Agent
|
|
--393228 CONTACT Settling Agent
|
|
--393866 RIA Settling Agent
|
|
-- 590815 NIBANK
|
|
*/
|
|
SELECT
|
|
@tranStatus = tranStatus,
|
|
@payStatus = CASE WHEN Paystatus='Unpaid' AND tranStatus='Payment' THEN 'Cancel'
|
|
WHEN Paystatus='Post' AND tranStatus='Payment' THEN 'CancelRequest'
|
|
ELSE payStatus
|
|
END
|
|
,@tranId = id,
|
|
@lockStatus = lockStatus,
|
|
@isPatner = CASE WHEN pAgent IN(393864,415208,393228,393866,585120,590815) THEN 'Y' ELSE 'N' END,
|
|
@refund = CASE WHEN pAgent IN(393864,415208,393228,393866,585120,590815) THEN 'N' ELSE 'D' END
|
|
FROM remitTran WITH(NOLOCK)
|
|
WHERE controlNo = @controlNoEncrypted
|
|
|
|
IF @flag = 'cancelRequest'
|
|
BEGIN
|
|
|
|
IF @user IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Your session has expired. Cannot send cancel request', NULL
|
|
RETURN
|
|
END
|
|
|
|
IF (@tranStatus IS NULL)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction not found', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF (@tranStatus = 'Block')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is blocked. Please Contact HO', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Paid')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has already been paid', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@payStatus = 'Paid')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has already been paid', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Cancel')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction already been cancelled', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF (@tranStatus = 'CancelRequest')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Cancel Request has already been sent', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF NOT EXISTS(SELECT TOP(1) 'A' FROM FastMoneyPro_Account.DBO.tran_master(NOLOCK) WHERE field1=@controlNo AND field2='Remittance Voucher')
|
|
EXEC FastMoneyPro_Account.dbo.proc_transactionVoucherEntry @controlNo= @controlNo
|
|
|
|
UPDATE remitTran
|
|
SET tranStatus = 'CancelRequest'
|
|
,cancelRequestBy = @user
|
|
,cancelRequestDate = GETDATE()
|
|
,cancelRequestDateLocal = GETUTCDATE()
|
|
,cancelReason = @cancelReason
|
|
WHERE controlNo = @controlNoEncrypted
|
|
|
|
IF @payStatus = 'Cancel' AND @isPatner = 'Y'
|
|
BEGIN
|
|
SELECT '0' errorCode,'Unpost transaction cancelled successfully' Msg,@tranId id, @isPatner Extra
|
|
|
|
EXEC proc_cancelTran @flag = 'cancel', @user = @user, @controlNo = @controlNo, @cancelReason = 'Unpost transaction cancelled', @refund = @refund
|
|
INSERT INTO tranCancelrequest(tranId,controlNo,cancelReason,cancelStatus,createdBy,createdDate,tranStatus,approvedBy,approvedDate,isScRefund)
|
|
SELECT @tranId,@controlNoEncrypted,'Unpost transaction cancelled successfully','Cancel',@user,GETDATE(),@tranStatus,@user,GETDATE(),@refund
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO tranCancelrequest(tranId,controlNo,cancelReason,cancelStatus,createdBy,createdDate,tranStatus)
|
|
SELECT @tranId,@controlNoEncrypted,'Cancel requested successfully','CancelRequest',@user,GETDATE(),@tranStatus
|
|
|
|
|
|
SELECT '0' errorCode,'Cancel requested successfully' Msg,@tranId id, @isPatner Extra
|
|
END
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION
|
|
|
|
RETURN
|
|
END
|
|
IF @flag='details'
|
|
BEGIN
|
|
|
|
--Api based include all pAgents to use this methods like Moneygram, etc
|
|
IF EXISTS(SELECT 'A' FROM remitTran(nolock) where controlNo = @controlNoEncrypted AND tranStatus = 'CancelRequest' AND pAgent IN (601410))
|
|
BEGIN
|
|
SELECT
|
|
PartnerTranId = RT.ContNo
|
|
,DeliveryMethod = RT.PaymentMethod
|
|
,TranId = RT.id
|
|
,PartnerPin = dbo.FNADecryptString(rt.controlNo)
|
|
,ControlNo = dbo.FNADecryptString(rt.controlNo2)
|
|
,ContNo = rt.ContNo
|
|
,processId = rt.controlNo
|
|
,BaseUrl = p.BaseUrl
|
|
,[Authorization] = p.AuthKey
|
|
,payOutPartnerId = Rt.pAgent
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
INNER JOIN serviceTypeMaster M(NOLOCK) ON M.typeTitle = RT.paymentMethod
|
|
LEFT JOIN TblPartnerwiseCountry(NOLOCK) P ON P.AgentId = RT.pSuperAgent AND P.PaymentMethod = M.serviceTypeId
|
|
WHERE RT.tranStatus = 'CancelRequest' AND RT.payStatus = 'Post'
|
|
AND controlNo = @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS(SELECT 'A' FROM remitTran(nolock) where controlNo = @controlNoEncrypted AND tranStatus = 'CancelRequest' )
|
|
BEGIN
|
|
SELECT
|
|
TransactionId = RT.id
|
|
,GmeControlNo = CASE WHEN RT.pAgent = 393866 THEN dbo.FNADecryptString(RT.controlNo2) ELSE dbo.FNADecryptString(RT.controlNo) END
|
|
,PartnerPin = rt.ContNo
|
|
,DocId = rt.ContNo
|
|
,provideName = CASE WHEN RT.pAgent = 393228 THEN 'CONTACT'
|
|
WHEN RT.pAgent = 393864 THEN 'DBBL'
|
|
WHEN RT.pAgent = 415208 THEN 'XPRESS'
|
|
WHEN RT.pAgent = 393866 THEN 'RIA'
|
|
WHEN RT.pAgent = 585120 THEN 'KORONAPAY'
|
|
WHEN RT.pAgent = 590815 THEN 'NIBANK'
|
|
END
|
|
,payStatus
|
|
,cancelReason
|
|
,BaseUrl = p.BaseUrl
|
|
,[Authorization] = p.AuthKey
|
|
,PartnerId = Rt.pAgent
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
INNER JOIN serviceTypeMaster M(NOLOCK) ON M.typeTitle = RT.paymentMethod
|
|
LEFT JOIN TblPartnerwiseCountry(NOLOCK) P ON P.AgentId = RT.pSuperAgent AND P.PaymentMethod = M.serviceTypeId
|
|
WHERE RT.tranStatus = 'CancelRequest' AND RT.payStatus = 'Post'
|
|
AND controlNo = @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
END
|
|
IF @flag = 'statusUpdate'
|
|
BEGIN
|
|
IF @apiSuccess = 'Y'
|
|
BEGIN
|
|
SET @refund = CASE WHEN @payoutPartner IN ('CONTACT','RIA','KORONAPAY','NIBANK') THEN 'N' ELSE 'D' END
|
|
|
|
IF @payoutPartner IN('CONTACT','RIA','KORONAPAY','NIBANK')
|
|
BEGIN
|
|
UPDATE remitTran SET tranStatus = 'CancelRequested'
|
|
WHERE controlNo = @controlNoEncrypted
|
|
END
|
|
UPDATE tranCancelrequest SET
|
|
approvedDate = GETDATE()
|
|
,approvedBy = @user
|
|
,cancelStatus = 'approved'
|
|
WHERE controlNo = @controlNoEncrypted AND cancelStatus = 'CancelRequest'
|
|
|
|
EXEC proc_transactionLogs 'i', @user, @tranId, 'Cancel requested successfully', 'Cancel Request'
|
|
|
|
EXEC proc_errorHandler 0,'Cancel request success.',@tranId
|
|
RETURN;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-->> ## INSERTING REQUESTED LIST
|
|
INSERT INTO tranCancelrequest(tranId,controlNo,cancelReason,cancelStatus,createdBy,createdDate,tranStatus,approvedBy,approvedDate)
|
|
SELECT @tranId,@controlNoEncrypted,'Partner API Cancel request failed','Rejected',@user,GETDATE(),@tranStatus,@user,GETDATE()
|
|
|
|
EXEC proc_transactionLogs 'i', @user, @tranId, 'Cancel request failed from partner.', 'Cancel Request'
|
|
|
|
EXEC proc_errorHandler 1,'Cancel request failed from partner.',@tranId
|
|
RETURN;
|
|
END
|
|
|
|
END
|
|
|
|
|
|
|