|
|
SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONGOALTER PROCEDURE [dbo].[INBOUND_PROC_DO_TRANSACTION]( @Flag VARCHAR(30) = NULL ,@SenderFirstName VARCHAR(100)= NULL ,@SenderMiddleName VARCHAR(100)= NULL ,@SenderLastName VARCHAR(100)= NULL ,@SenderNativeCountry VARCHAR(100)= NULL ,@SenderCity VARCHAR(100)= NULL ,@SenderAddress VARCHAR(100)= NULL ,@SenderIdType VARCHAR(100)= NULL ,@SenderIdNumber VARCHAR(100)= NULL ,@SenderMobile VARCHAR(100)= NULL ,@SenderEmail VARCHAR(100)= NULL ,@SenderTransferReason VARCHAR(100)= NULL ,@SenderFundSource VARCHAR(100)= NULL
,@ReceiverFirstName VARCHAR(100)= NULL ,@ReceiverMiddleName VARCHAR(100)= NULL ,@ReceiverLastName VARCHAR(100)= NULL ,@ReceiverNativeCountry VARCHAR(100)= NULL ,@ReceiverCity VARCHAR(100)= NULL ,@ReceiverAddress VARCHAR(100)= NULL ,@ReceiverIdType VARCHAR(100)= NULL ,@ReceiverIdNumber VARCHAR(100)= NULL ,@ReceiverMobile VARCHAR(100)= NULL ,@ReceiverEmail VARCHAR(100)= NULL ,@Bank VARCHAR(100)= NULL ,@AccountNo VARCHAR(100)= NULL ,@Relation VARCHAR(100)= NULL
,@ForexSessionId VARCHAR(100)= NULL ,@PartnerTranNo VARCHAR(100)= NULL ,@PaymentMethod VARCHAR(100)= NULL ,@SendingAmount VARCHAR(100)= NULL ,@SendingCurrency VARCHAR(100)= NULL ,@SendingCountry VARCHAR(100)= NULL ,@ReceivingAmount VARCHAR(100)= NULL ,@ReceivingCurrency VARCHAR(100)= NULL ,@ReceivingCountry VARCHAR(100)= NULL ,@ExRate VARCHAR(100)= NULL ,@UsdAmount VARCHAR(100)= NULL
,@ControlNo VARCHAR(20) = NULL ,@UserName VARCHAR(20) = NULL ,@AgentCode VARCHAR(20) = NULL ,@ProcessIdentifier VARCHAR(100)= NULL
)ASBEGIN TRY DECLARE @pCurrCostRate Money,@pCurrHoMargin MONEY, @pCostRate MONEY, @tempReceivingAmt MONEY, @EncryptedControlNo VARCHAR(100) , @SenderName VARCHAR(100), @ReceiverName VARCHAR(100), @ServiceCharge MONEY,@sCountryId INT, @pCountryId INT=118 ,@sCountry VARCHAR(100), @pCountry VARCHAR(100),@pBank VARCHAR(10),@pBankName VARCHAR(100), @agentbalance MONEY=0.0 ,@settleAmt MONEY=2.5, @settleCurr VARCHAR(3), @tranId BIGINT,@id BIGINT,@sSettelingAgent BIGINT
IF @Flag='doTran' BEGIN SELECT @sCountryId=countryId,@sCountry=countryName FROM dbo.countryMaster(NOLOCK) WHERE countryCode=@SendingCountry SELECT @pCountryId=countryId,@pCountry=countryName FROM dbo.countryMaster(NOLOCK) WHERE countryCode=@ReceivingCountry SELECT @PaymentMethod= serviceTypeId FROM dbo.serviceTypeMaster(NOLOCK) WHERE serviceCode = @PaymentMethod SELECT @pBank=rowId,@pBankName=BankName FROM dbo.KoreanBankList(NOLOCK) WHERE bankCode=@Bank
DECLARE @sCurrCostRate FLOAT , @pAgentComm MONEY , @pAgentCommCurrency VARCHAR(3)
DECLARE @sAgent INT = NULL ,@sAgentName VARCHAR(100) = NULL ,@sSuperAgent INT = NULL ,@sSuperAgentName VARCHAR(100) = NULL
,@pAgent INT = NULL ,@pAgentName VARCHAR(100) = NULL ,@pSuperAgent INT = NULL ,@pSuperAgentName VARCHAR(100) = NULL SELECT @sAgent=agentId FROM dbo.applicationUsers(NOLOCK) WHERE userName=@UserName SET @SenderName = @SenderFirstName + ISNULL(' ' + @SenderMiddleName, '') + ISNULL( ' ' + @SenderLastName, '') SET @ReceiverName = @ReceiverFirstName + ISNULL(' ' + @ReceiverMiddleName, '') + ISNULL(' ' + @ReceiverLastName, '') IF NOT EXISTS(SELECT 'x' FROM dbo.INBOUND_EXRATE_CALC_HISTORY(NOLOCK) WHERE forexSessionId=@ForexSessionId AND ISNULL(isExpired,0) = 0) BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0001') Msg, @ForexSessionId Id RETURN END
IF NOT EXISTS(SELECT 'x' FROM dbo.KoreanBankList(NOLOCK) WHERE bankCode=@Bank) BEGIN SELECT '1' 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
SELECT @pCurrCostRate = pCurrCostRate ,@pCurrHoMargin = pCurrHoMargin FROM dbo.INBOUND_EXRATE_CALC_HISTORY(NOLOCK) WHERE forexSessionId=@ForexSessionId SET @pCostRate = ISNULL(@pCurrCostRate,0)-ISNULL(@pCurrHoMargin,0) IF(@pCostRate)<>@ExRate BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0002') Msg, @ForexSessionId Id RETURN END SET @tempReceivingAmt=@UsdAmount*@pCostRate IF(@tempReceivingAmt<>@ReceivingAmount) BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0003') Msg, @ForexSessionId Id RETURN END
--Get sending agent detail SELECT @sAgent = sAgent ,@sAgentName = sAgentName ,@sSuperAgent = sSuperAgent ,@sSuperAgentName = sSuperAgentName FROM dbo.FNAGetBranchFullDetails(@sAgent)
IF @pAgent IS NULL SET @pAgent=2080
--Get Receiving agent detail SELECT @pSuperAgent = sSuperAgent,@pSuperAgentName = sSuperAgentName, @pAgent = sAgent,@pAgentName = sAgentName FROM dbo.FNAGetBranchFullDetails(@pAgent)
IF EXISTS(SELECT 'x' FROM dbo.INBOUND_VIEW_REMIT_TRAN(NOLOCK) WHERE controlNo2=dbo.FNAEncryptString(@PartnerTranNo) AND sSuperAgent=@sSuperAgent) BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0012') Msg, NULL Id RETURN END
SELECT @ControlNo='80' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '000000000', 9)
SET @EncryptedControlNo=dbo.FNAEncryptString(@ControlNo)
IF NOT EXISTS(SELECT 'x' FROM dbo.INBOUND_PIN_QUEUE(NOLOCK) WHERE pin=@EncryptedControlNo) BEGIN INSERT INTO dbo.INBOUND_PIN_QUEUE(pin)SELECT @EncryptedControlNo END --Later get this from setting DECLARE @chargeCurrency VARCHAR(3) SELECT @chargeCurrency='USD'
--SELECT @sCountryId,@sSuperAgent,@sAgent,@pCountryId,@pSuperAgent,@pAgent,@PaymentMethod,@UsdAmount,'USD'
SELECT @ServiceCharge = dbo.FNA_INBOUND_GET_SERVICE_CAHARGE(@sCountryId,@sSuperAgent,@sAgent,@pCountryId,@pSuperAgent,@pAgent,@PaymentMethod,@UsdAmount,'USD') IF @ServiceCharge IS NULL BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0011') Msg, NULL Id RETURN END --Calculate settlement amount SET @settleAmt=@UsdAmount+@serviceCharge
SET @settleCurr='USD' SET @pAgentComm=0.0 SET @pAgentCommCurrency='KRW' PRINT 'fsadfa' SELECT @sSettelingAgent=agentId FROM dbo.agentMaster(NOLOCK) WHERE parentId=@sSuperAgent AND ISNULL(isSettlingAgent,'N')='Y' AND ISNULL(isApiPartner,0)=1 AND ISNULL(isActive,'N')='Y' AND ISNULL(isDeleted,'N')='N' PRINT 'afpple'
SELECT @agentbalance=dbo.FNAGetAvailableBalance(@sSettelingAgent)
IF(@agentbalance<@settleAmt) BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0004') Msg, NULL Id RETURN END
DECLARE @receiverOfacRes VARCHAR(MAX), @ofacRes VARCHAR(MAX), @ofacReason VARCHAR(200)
EXEC proc_ofacTracker @flag = 't', @name = @senderName, @Result = @ofacRes OUTPUT EXEC proc_ofacTracker @flag = 't', @name = @ReceiverName, @Result = @receiverOfacRes OUTPUT DECLARE @result VARCHAR(MAX) IF ISNULL(@ofacRes, '') <> '' BEGIN SET @ofacReason = 'Matched by sender name' END IF ISNULL(@receiverOfacRes, '') <> '' BEGIN --SET @ofacRes = @receiverOfacRes SET @ofacReason = 'Matched by receiver name' END IF ISNULL(@ofacRes, '') <> '' AND ISNULL(@receiverOfacRes, '') <> '' BEGIN SET @ofacReason = 'Matched by both sender name and receiver name' END
PRINT @ofacReason PRINT @ofacRes DECLARE @complianceRuleId VARCHAR(20) = NULL ,@complienceMessage varchar(1000) =NULL ,@shortMsg varchar(100) =NULL ,@complienceErrorCode TINYINT = NULL ,@complianceId BIGINT = NULL
EXEC INBOUND_PROC_COMPLIANCE_RULE_DETAIL @flag = 'core' ,@IdNumber = @SenderIdNumber ,@IdType = @SenderIdType ,@receiverName = @ReceiverName ,@UsdAmount = @UsdAmount ,@sCountryId = @sCountryId ,@receiverMobile = @ReceiverMobile ,@deliveryMethod = @PaymentMethod ,@message = @complienceMessage OUTPUT ,@shortMessage = @shortMsg OUTPUT ,@errCode = @complienceErrorCode OUTPUT ,@ruleId = @complianceRuleId OUTPUT DECLARE @tempTranStatus VARCHAR(100)='HOLD'
IF(@complienceErrorCode <> 0) BEGIN IF(@complienceErrorCode = 1) BEGIN SET @tempTranStatus='BLOCK' END ELSE IF(@complienceErrorCode = 2) BEGIN SET @tempTranStatus='COMPLIANCE HOLD' END
INSERT INTO dbo.INBOUND_COMPLIANCE_LOG(senderName, senderCountry, senderIdType, senderIdNumber, senderMobile, receiverName , receiverCountry,payOutAmt,complianceId,complianceReason,complainceDetailMessage,createdBy,createdDate,logType)
SELECT @senderName, @sCountry, @SenderIdType, @SenderIdNumber, @SenderMobile, @receiverName , @pCountry, @UsdAmount, @complianceRuleId, @shortMsg, @complienceMessage, @UserName, GETDATE(),'core-limit' SET @complianceId=@@IDENTITY END
IF @complienceErrorCode = 1 BEGIN EXEC proc_errorHandler 1, @shortMsg, NULL RETURN; END;
IF((ISNULL(@ofacRes, '') <> '' OR ISNULL(@receiverOfacRes, '') <> '')) BEGIN IF ISNULL(@ofacRes, '') <> '' INSERT INBOUND_TRAN_OFAC(TranId, blackListId, reason, flag) SELECT @tranId, @ofacRes, @ofacReason, dbo.FNAGetOFAC_Flag(@ofacRes) IF ISNULL(@receiverOfacRes, '') <> '' INSERT INBOUND_TRAN_OFAC(TranId, blackListId, reason, flag) SELECT @tranId, @receiverOfacRes, @ofacReason, dbo.FNAGetOFAC_Flag(@receiverOfacRes)
SET @tempTranStatus='OFAC HOLD'
IF @complienceErrorCode='2' SET @tempTranStatus='COMPLIANCE/OFAC HOLD' END
BEGIN TRAN
SET @sCurrCostRate = 1
INSERT INTO dbo.INBOUND_REMIT_TRAN_TEMP ( controlNo,sCurrCostRate,pCurrCostRate,pCurrHoMargin,serviceCharge,chargeCurrency,pAgentComm,pAgentCommCurrency, sCountry,sSuperAgent,sSuperAgentName,sAgent,sAgentName,pCountry,pSuperAgent,pSuperAgentName, pAgent,pAgentName,paymentMethod,pBank,pBankName,accountNo,UsdAmount,SettlementAmt,SettlementCurrency,collCurr,tAmt, pAmt,payoutCurr,relWithSender,purposeOfRemit,sourceOfFund,tranStatus,payStatus, createdDate,createdDateLocal,createdBy,controlNo2,senderName,receiverName )SELECT @EncryptedControlNo,@sCurrCostRate,@pCurrCostRate,@pCurrHoMargin,@serviceCharge,@chargeCurrency,@pAgentComm,@pAgentCommCurrency, @sCountry,@sSuperAgent,@sSuperAgentName,@sAgent,@sAgentName,@pCountry,@pSuperAgent,@pSuperAgentName, @pAgent,@pAgentName,@paymentMethod,@pBank,@pBankName,@accountNo,@UsdAmount,@settleAmt,@settleCurr,@SendingCurrency,@SendingAmount, @ReceivingAmount,@ReceivingCurrency,@Relation,@SenderTransferReason,@SenderFundSource,@tempTranStatus,'UNPAID', GETDATE(),GETUTCDATE(),@UserName,dbo.FNAEncryptString(@PartnerTranNo),@SenderName,@ReceiverName
SET @tranId=@@IDENTITY
INSERT INTO dbo.INBOUND_TRAN_RECEIVERS_TEMP ( tranId,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate,relationId,accountNo,notifySms )SELECT @tranId,NULL,@ReceiverFirstName,@ReceiverMiddleName,@ReceiverLastName,@ReceiverName,@ReceiverAddress,@ReceiverCity ,@ReceiverEmail,@ReceiverMobile,@ReceiverNativeCountry ,NULL,NULL,@ReceiverIdType,@ReceiverIdNumber,NULL,NULL,NULL,@Relation,@AccountNo,0
INSERT INTO dbo.INBOUND_TRAN_SENDERS_TEMP ( tranId,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate )SELECT @tranId,NULL,@SenderFirstName,@SenderMiddleName,@SenderLastName,@SenderName,@SenderAddress,@SenderCity ,@SenderEmail,@SenderMobile,@SenderNativeCountry ,NULL,NULL,@SenderIdType,@SenderIdNumber,NULL,NULL,NULL
UPDATE dbo.INBOUND_EXRATE_CALC_HISTORY SET controlNo = @ControlNo, isExpired = 1 WHERE forexSessionId = @ForexSessionId UPDATE dbo.INBOUND_COMPLIANCE_LOG SET holdTranId=@tranId WHERE complianceId=@complianceId COMMIT TRAN
IF @@TRANCOUNT=0 BEGIN SELECT '0' ErrorCode, dbo.GetMessage('en','IBST0005') Msg, @tranId Id, @ControlNo ControlNo RETURN END ELSE BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0006') Msg, @tranId Id, @ControlNo ControlNo RETURN END END IF @Flag='commitTran' BEGIN IF EXISTS(SELECT 'x' FROM INBOUND_REMIT_TRAN irt(NOLOCK) WHERE irt.controlNo = dbo.FNAEncryptString(@ControlNo) AND irt.controlNo2 = dbo.FNAEncryptString(@PartnerTranNo)) BEGIN EXEC dbo.proc_errorHandler '1', 'Transaction has already been committed.', NULL RETURN END
IF NOT EXISTS(SELECT 'x' FROM dbo.INBOUND_REMIT_TRAN_TEMP irt(NOLOCK) WHERE irt.controlNo = dbo.FNAEncryptString(@ControlNo) AND irt.controlNo2 = dbo.FNAEncryptString(@PartnerTranNo)) BEGIN EXEC dbo.proc_errorHandler '1', 'Wrong ControlNo/ PartnerTranNo', NULL RETURN END SELECT @tranId = id ,@sAgent = sSuperAgent ,@settleAmt = SettlementAmt FROM dbo.INBOUND_REMIT_TRAN_TEMP(NOLOCK) WHERE controlNo = dbo.FNAEncryptString(@ControlNo) AND controlNo2 = dbo.FNAEncryptString(@PartnerTranNo)
SELECT @sSettelingAgent=agentId FROM dbo.agentMaster(NOLOCK) WHERE parentId=@sAgent AND ISNULL(isSettlingAgent,'N')='Y' AND ISNULL(isApiPartner,0)=1 AND ISNULL(isActive,'N')='Y' AND ISNULL(isDeleted,'N')='N'
SELECT @agentbalance=dbo.FNAGetAvailableBalance(@sSettelingAgent)
IF(@agentbalance<@settleAmt) BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0004') Msg, NULL Id RETURN END
BEGIN TRAN INSERT INTO dbo.INBOUND_REMIT_TRAN( controlNo,sCurrCostRate,pCurrCostRate,pCurrHoMargin,serviceCharge,chargeCurrency,pAgentComm,pAgentCommCurrency, sCountry,sSuperAgent,sSuperAgentName,sAgent,sAgentName,pCountry,pSuperAgent,pSuperAgentName, pAgent,pAgentName,paymentMethod,pBank,pBankName,accountNo,UsdAmount,SettlementAmt,SettlementCurrency,collCurr,tAmt, pAmt,payoutCurr,relWithSender,purposeOfRemit,sourceOfFund,tranStatus,payStatus, createdDate,createdDateLocal,createdBy,controlNo2,senderName,receiverName,approvedDate,approvedDateLocal,approvedBy,holdTranId )SELECT controlNo,sCurrCostRate,pCurrCostRate,pCurrHoMargin,serviceCharge,chargeCurrency,pAgentComm,pAgentCommCurrency, sCountry,sSuperAgent,sSuperAgentName,sAgent,sAgentName,pCountry,pSuperAgent,pSuperAgentName, pAgent,pAgentName,paymentMethod,pBank,pBankName,accountNo,UsdAmount,SettlementAmt,SettlementCurrency,collCurr,tAmt, pAmt,payoutCurr,relWithSender,purposeOfRemit,sourceOfFund,CASE WHEN tranStatus='HOLD' THEN 'PAYMENT' ELSE tranStatus END,'UNPAID', createdDate,createdDateLocal,createdBy,controlNo2,senderName,receiverName,GETDATE(),GETUTCDATE(),@UserName,@tranId FROM dbo.INBOUND_REMIT_TRAN_TEMP(NOLOCK) WHERE id=@tranId
SET @id=@@IDENTITY
INSERT INTO dbo.INBOUND_TRAN_RECEIVERS ( tranId,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate,relationId,accountNo,notifySms )SELECT @id,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate,relationId,accountNo,notifySms FROM dbo.INBOUND_TRAN_RECEIVERS_TEMP(NOLOCK) WHERE tranId=@tranId
INSERT INTO dbo.INBOUND_TRAN_SENDERS ( tranId,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate )SELECT @id,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate FROM dbo.INBOUND_TRAN_SENDERS_TEMP(NOLOCK) WHERE tranId=@tranId
UPDATE dbo.creditLimit SET availableBalance=(availableBalance-@settleAmt) WHERE agentId=@sSettelingAgent ----## Deactivate the process identifier
UPDATE ai SET ai.isActive = 0 FROM dbo.INBOUND_APIUSER_IDENTIFIER ai WHERE ai.identifier = @ProcessIdentifier AND ai.username = @UserName
PRINT @tranId
DELETE FROM dbo.INBOUND_TRAN_SENDERS_TEMP WHERE tranId=@tranId DELETE FROM dbo.INBOUND_TRAN_RECEIVERS_TEMP Where tranId=@tranId DELETE FROM dbo.INBOUND_REMIT_TRAN_TEMP Where id=@tranId
COMMIT TRAN IF @@TRANCOUNT=0 BEGIN SELECT '0' ErrorCode, dbo.GetMessage('en','IBST0007') Msg, @id Id, @ControlNo ControlNo, FORMAT(GETDATE(),'yyyy-MM-dd') AS TranDate RETURN END ELSE BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0008') Msg, @id Id, @ControlNo ControlNo , NULL AS TranDate RETURN END END IF @Flag='rejectTran' BEGIN
IF EXISTS(SELECT 'x' FROM dbo.INBOUND_REJECT_TRAN irt(NOLOCK) WHERE irt.controlNo = dbo.FNAEncryptString(@ControlNo) AND irt.controlNo2 = dbo.FNAEncryptString(@PartnerTranNo)) BEGIN EXEC dbo.proc_errorHandler '1', 'Transaction has already been rejected.', NULL RETURN END
IF NOT EXISTS(SELECT 'x' FROM dbo.INBOUND_REMIT_TRAN_TEMP irt(NOLOCK) WHERE irt.controlNo = dbo.FNAEncryptString(@ControlNo) AND irt.controlNo2 = dbo.FNAEncryptString(@PartnerTranNo)) BEGIN EXEC dbo.proc_errorHandler '1', 'Wrong ControlNo/ PartnerTranNo', NULL RETURN END SELECT @tranId = id ,@sAgent = sAgent ,@settleAmt = SettlementAmt FROM dbo.INBOUND_REMIT_TRAN_TEMP(NOLOCK) WHERE controlNo = dbo.FNAEncryptString(@ControlNo) AND controlNo2 = dbo.FNAEncryptString(@PartnerTranNo) BEGIN TRAN INSERT INTO dbo.INBOUND_REJECT_TRAN( controlNo,sCurrCostRate,pCurrCostRate,pCurrHoMargin,serviceCharge,chargeCurrency,pAgentComm,pAgentCommCurrency, sCountry,sSuperAgent,sSuperAgentName,sAgent,sAgentName,pCountry,pSuperAgent,pSuperAgentName, pAgent,pAgentName,paymentMethod,pBank,pBankName,accountNo,UsdAmount,SettlementAmt,SettlementCurrency,collCurr,tAmt, pAmt,payoutCurr,relWithSender,purposeOfRemit,sourceOfFund,tranStatus,payStatus, createdDate,createdDateLocal,createdBy,controlNo2,senderName,receiverName,rejectDate,rejectDateLocal,rejectBy )SELECT controlNo,sCurrCostRate,pCurrCostRate,pCurrHoMargin,serviceCharge,chargeCurrency,pAgentComm,pAgentCommCurrency, sCountry,sSuperAgent,sSuperAgentName,sAgent,sAgentName,pCountry,pSuperAgent,pSuperAgentName, pAgent,pAgentName,paymentMethod,pBank,pBankName,accountNo,UsdAmount,SettlementAmt,SettlementCurrency,collCurr,tAmt, pAmt,payoutCurr,relWithSender,purposeOfRemit,sourceOfFund,tranStatus,payStatus, createdDate,createdDateLocal,createdBy,controlNo2,senderName,receiverName,GETDATE(),GETUTCDATE(),@UserName FROM dbo.INBOUND_REMIT_TRAN_TEMP(NOLOCK) WHERE id=@tranId
SET @id=@@IDENTITY
INSERT INTO dbo.INBOUND_REJECT_TRAN_RECEIVERS( tranId,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate,relationId,accountNo,notifySms )SELECT @id,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate,relationId,accountNo,notifySms FROM dbo.INBOUND_TRAN_RECEIVERS_TEMP(NOLOCK) WHERE tranId=@tranId
INSERT INTO dbo.INBOUND_REJECT_TRAN_SENDERS ( tranId,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate )SELECT @id,customerId,firstName,middleName,lastName,fullName,address,city ,email,mobile,nativeCountry ,dob,occupation,idType,idNumber,idPlaceOfIssue,issuedDate,validDate FROM dbo.INBOUND_TRAN_SENDERS_TEMP(NOLOCK) WHERE tranId=@tranId DELETE FROM dbo.INBOUND_TRAN_SENDERS_TEMP WHERE tranId=@tranId DELETE FROM dbo.INBOUND_TRAN_RECEIVERS_TEMP Where tranId=@tranId DELETE FROM dbo.INBOUND_REMIT_TRAN_TEMP Where id=@tranId
----## Deactivate the process identifier
UPDATE ai SET ai.isActive = 0 FROM dbo.INBOUND_APIUSER_IDENTIFIER ai WHERE ai.identifier = @ProcessIdentifier AND ai.username = @UserName
COMMIT TRAN IF @@TRANCOUNT=0 BEGIN SELECT '0' ErrorCode, dbo.GetMessage('en','IBST0009') Msg, NULL Id, @ControlNo ControlNo, FORMAT(GETDATE(),'yyyy-MM-dd') AS TranDate RETURN END ELSE BEGIN SELECT '1' ErrorCode, dbo.GetMessage('en','IBST0010') Msg, NULL Id, @ControlNo ControlNo , NULL AS TranDate RETURN END ENDEND TRYBEGIN CATCH IF @@TRANCOUNT>0 ROLLBACK TRAN
SELECT '999' ErrorCode, ERROR_MESSAGE() Msg,NULL Id, NULL ControlNo , NULL AS TranDate RETURN
END CATCH
GO
|
