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.
528 lines
20 KiB
528 lines
20 KiB
SET QUOTED_IDENTIFIER ON
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
ALTER 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
|
|
|
|
)AS
|
|
BEGIN 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
|
|
END
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT>0
|
|
ROLLBACK TRAN
|
|
|
|
SELECT '999' ErrorCode, ERROR_MESSAGE() Msg,NULL Id, NULL ControlNo , NULL AS TranDate
|
|
RETURN
|
|
|
|
END CATCH
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|