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.
204 lines
16 KiB
204 lines
16 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[INBOUND_PROC_AMEND_TRANSACTION] Script Date: 9/27/2019 1:30:13 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE PROC [dbo].[INBOUND_PROC_AMEND_TRANSACTION](
|
|
@flag VARCHAR(20)
|
|
,@agentCode VARCHAR(20) = NULL
|
|
,@userName VARCHAR(20) = NULL
|
|
,@controlNo VARCHAR(50) = NULL
|
|
,@partnerTranNo VARCHAR(50) = NULL
|
|
,@TranId VARCHAR(50) = NULL
|
|
--,@SenderFirstName VARCHAR(100)= NULL
|
|
--,@SenderMiddleName VARCHAR(100)= NULL
|
|
--,@SenderLastName VARCHAR(100)= NULL
|
|
--,@SenderIdType VARCHAR(100)= NULL
|
|
--,@SenderIdNumber VARCHAR(100)= NULL
|
|
|
|
,@ReceiverFirstName VARCHAR(100)= NULL
|
|
,@ReceiverMiddleName VARCHAR(100)= NULL
|
|
,@ReceiverLastName VARCHAR(100)= NULL
|
|
,@ReceiverMobile VARCHAR(100)= NULL
|
|
,@Bank VARCHAR(100)= NULL
|
|
,@AccountNo VARCHAR(100)= NULL
|
|
,@ProcessIdentifier VARCHAR(100) = NULL
|
|
|
|
|
|
)AS
|
|
BEGIN TRY
|
|
IF @flag='amend-transaction'
|
|
BEGIN
|
|
|
|
DECLARE @agentId BIGINT
|
|
SELECT @agentId=agentId FROM dbo.agentMaster(NOLOCK) WHERE agentCode=@agentCode
|
|
|
|
|
|
SELECT @controlNo = dbo.FNAEncryptString(@controlNo), @partnerTranNo = dbo.FNAEncryptString(@partnerTranNo)
|
|
|
|
IF NOT EXISTS(SELECT 'x' FROM dbo.INBOUND_REMIT_TRAN(NOLOCK) irt WHERE irt.id = @TranId AND irt.controlNo = @controlNo AND irt.sAgent=@agentId and irt.controlNo2 = @partnerTranNo)
|
|
BEGIN
|
|
SELECT 'IB1045' ErrorCode, 'Wrong Control No/Transaction No./Partner Tranaction No' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
|
|
IF NOT EXISTS(SELECT 'x' FROM dbo.INBOUND_REMIT_TRAN(NOLOCK) irt WHERE irt.id = @TranId AND irt.sAgent=@agentId AND irt.payStatus= 'UNPAID' AND irt.tranStatus='PAYMENT')
|
|
BEGIN
|
|
SELECT '1' ErrorCode, 'Transaction is under process, cannot modify the transaction. ' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
IF NOT EXISTS(SELECT 'x' FROM dbo.KoreanBankList(NOLOCK) WHERE bankCode=@Bank)
|
|
BEGIN
|
|
SELECT 'IBST0013' ErrorCode, dbo.GetMessage('en','IBST0013') Msg, @Bank Id
|
|
RETURN
|
|
END
|
|
|
|
--IF NOT EXISTS(SELECT 'x' FROM staticDataValue(NOLOCK) WHERE typeID = 1300 AND valueId=@SenderIdType)
|
|
--BEGIN
|
|
-- SELECT 'IBST0014' ErrorCode, dbo.GetMessage('en','IBST0014') Msg, @SenderIdType Id
|
|
-- RETURN
|
|
--END
|
|
|
|
|
|
--DECLARE @oldSenderFirstName VARCHAR(100),@oldSenderMiddleName VARCHAR(100),@oldSenderLastName VARCHAR(100),@oldSenderIdType VARCHAR(100)
|
|
-- ,@oldSenderIdNumber VARCHAR(100),
|
|
DECLARE @oldReceiverFirstName VARCHAR(100),@oldReceiverMiddleName VARCHAR(100),@oldReceiverLastName VARCHAR(100)
|
|
,@oldReceiverMobile VARCHAR(100),@oldBank VARCHAR(100),@oldBankName VARCHAR(100),@oldAccountNo VARCHAR(100),@oldSenderIdTypeText VARCHAR(100)
|
|
,@newSenderIdTypeText VARCHAR(100),@newpBankName VARCHAR(100)
|
|
|
|
|
|
--SELECT @newSenderIdTypeText=detailTitle FROM dbo.staticDataValue(NOLOCK) WHERE valueId=@SenderIdType
|
|
SELECT @newpBankName=BankName FROM dbo.KoreanBankList(NOLOCK) WHERE bankCode=@Bank
|
|
|
|
SELECT
|
|
-- @oldSenderFirstName = ts.firstName
|
|
--,@oldSenderMiddleName = ts.middleName
|
|
--,@oldSenderLastName = ts.lastName
|
|
--,@oldSenderIdType = ts.idType
|
|
--,@oldSenderIdTypeText = ISNULL(sdv.detailTitle,ts.idType)
|
|
--(SELECT detailTitle FROM dbo.staticDataValue(NOLOCK) WHERE valueId=ts.idType)
|
|
--,@oldSenderIdNumber = ts.idNumber
|
|
@oldReceiverFirstName = tr.firstName
|
|
,@oldReceiverMiddleName = tr.middleName
|
|
,@oldReceiverLastName = tr.lastName
|
|
,@oldReceiverMobile = tr.mobile
|
|
,@oldBank = rt.pBank
|
|
,@oldBankName = rt.pBankName
|
|
,@oldAccountNo = tr.accountNo
|
|
FROM dbo.INBOUND_REMIT_TRAN(NOLOCK) rt
|
|
INNER JOIN dbo.INBOUND_TRAN_SENDERS(NOLOCK) ts ON rt.id=ts.tranId
|
|
INNER JOIN dbo.INBOUND_TRAN_RECEIVERS(NOLOCK) tr ON rt.id=tr.tranId
|
|
LEFT JOIN staticDataValue sdv WITH(NOLOCK) ON ts.idType=CAST(sdv.valueId AS VARCHAR)
|
|
WHERE rt.id=@TranId
|
|
|
|
|
|
--DECLARE @oldSenderName VARCHAR(100),@newSenderName varchar(100),
|
|
DECLARE @oldReceiverName VARCHAR(100), @newReceiverName VARCHAR(100)
|
|
|
|
--SET @oldSenderName=@oldSenderFirstName+ISNULL(' '+@oldSenderMiddleName,'')+ISNULL(' '+@oldSenderLastName,'')
|
|
--SET @newSenderName=@SenderFirstName+ISNULL(' '+@SenderMiddleName,'')+ISNULL(' '+@SenderLastName,'')
|
|
|
|
|
|
SET @oldReceiverName=@oldReceiverFirstName+ISNULL(' '+@oldReceiverMiddleName,'')+ISNULL(' '+@oldReceiverLastName,'')
|
|
SET @newReceiverName=@ReceiverFirstName+ISNULL(' '+@ReceiverMiddleName,'')+ISNULL(' '+@ReceiverLastName,'')
|
|
|
|
|
|
DECLARE @tempTable TABLE(Field VARCHAR(30),oldValue VARCHAR(100),newValue VARCHAR(100), ticket VARCHAR(100))
|
|
INSERT INTO @tempTable(
|
|
Field,oldValue,newValue,ticket
|
|
)SELECT x.Field,ISNULL(x.oldValue,''),ISNULL(x.newValue,''),x.tick FROM (
|
|
--SELECT 'Sender Name' AS Field,@oldSenderName AS oldValue,@newSenderName AS newValue,'Sender Name:'+@oldSenderName+' = '+'Sender Name:'+@newSenderName AS tick
|
|
--UNION ALL
|
|
SELECT 'Receiver Name' AS Field,@oldReceiverName AS oldValue,@newReceiverName AS newValue,'Receiver Name:'+@oldReceiverName+' = '+'Receiver Name:'+@newReceiverName AS tick
|
|
UNION ALL
|
|
--SELECT 'Sender IdType' AS Field,@oldSenderIdType AS oldValue,@SenderIdType AS newValue,'Sender IdType:'+@oldSenderIdTypeText+' = '+'Sender IdType:'+@newSenderIdTypeText
|
|
--UNION ALL
|
|
--SELECT 'Sender IdNumber' AS Field,@oldSenderIdNumber AS oldValue,@SenderIdNumber AS newValue,'Sender IdNumber:'+@oldSenderIdNumber+' = '+'Sender IdNumber:'+@SenderIdNumber
|
|
--UNION ALL
|
|
SELECT 'Receiver Mobile' AS Field,@oldReceiverMobile AS oldValue,@ReceiverMobile AS newValue,'Receiver Mobile:'+@oldReceiverMobile+' = '+'Receiver Mobile:'+@ReceiverMobile
|
|
UNION ALL
|
|
SELECT 'Receiver Bank' AS Field,@oldBank AS oldValue,@Bank AS newValue,'Receiver Bank:'+@oldBankName+' = '+'Receiver Bank:'+@newpBankName
|
|
UNION ALL
|
|
SELECT 'Receiver AccountNo' AS Field,@oldAccountNo AS oldValue,@AccountNo AS newValue,'Receiver AccountNo:'+@oldAccountNo+' = '+'Receiver Bank:'+@AccountNo
|
|
)x
|
|
|
|
Declare @message Varchar(MAX);
|
|
SELECT @message = COALESCE(@message + ', ' + ticket, ticket)
|
|
From @tempTable WHERE oldValue<>newValue
|
|
|
|
IF @message IS NULL
|
|
BEGIN
|
|
SELECT '0' ErrorCode,'Transaction Amend success.' Msg, @TranId Id, GETDATE() AS TranDate
|
|
RETURN
|
|
END
|
|
|
|
BEGIN TRAN
|
|
UPDATE dbo.INBOUND_REMIT_TRAN SET
|
|
--senderName=@newSenderName
|
|
receiverName=@newReceiverName
|
|
,pBank=@Bank
|
|
,pBankName=@newpBankName
|
|
,accountNo=@AccountNo
|
|
WHERE id=@TranId
|
|
|
|
|
|
--UPDATE dbo.INBOUND_TRAN_SENDERS SET
|
|
-- firstName=@SenderFirstName
|
|
-- ,middleName=@SenderMiddleName
|
|
-- ,lastName=@SenderLastName
|
|
-- ,fullName=@newSenderName
|
|
-- ,idType=@SenderIdType
|
|
-- ,idNumber=@SenderIdNumber
|
|
--WHERE tranId=@TranId
|
|
|
|
UPDATE dbo.INBOUND_TRAN_RECEIVERS SET
|
|
firstName=@ReceiverFirstName
|
|
,middleName=@ReceiverMiddleName
|
|
,lastName=@ReceiverLastName
|
|
,fullName=@newReceiverName
|
|
,mobile=@ReceiverMobile
|
|
,accountNo=@AccountNo
|
|
WHERE tranId=@TranId
|
|
|
|
INSERT INTO dbo.INBOUND_TROUBLE_TICKET(
|
|
tranId,controlNo,[message],msgType,[status],createdBy,createdDate
|
|
)SELECT
|
|
@TranId,@controlNo,@message,'M','Modified',@userName,GETDATE()
|
|
|
|
----## Deactivate the process identifier
|
|
UPDATE ai SET ai.isActive = 0 FROM dbo.INBOUND_APIUSER_IDENTIFIER ai WHERE identifier = @ProcessIdentifier AND username = @userName
|
|
|
|
COMMIT TRAN
|
|
|
|
IF @@TRANCOUNT=0
|
|
BEGIN
|
|
SELECT '0' ErrorCode,'Transaction Amend success.' Msg, @TranId Id, GETDATE() AS TranDate
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Transaction Amend failed.' Msg, @TranId Id, NULL AS TranDate
|
|
RETURN
|
|
END
|
|
|
|
END
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@ERROR>0
|
|
ROLLBACK TRAN
|
|
|
|
SELECT '999' ErrorCode, ERROR_MESSAGE() Msg, NULL Id, NULL TranDate
|
|
RETURN
|
|
END CATCH
|
|
|
|
|
|
|
|
|
|
|
|
GO
|