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.
586 lines
45 KiB
586 lines
45 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_REMIT_INBOUND_TRANSACTION_SEND] Script Date: 4/20/2024 5:26:21 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
ALTER PROC [dbo].[PROC_REMIT_INBOUND_TRANSACTION_SEND]
|
|
(
|
|
@Flag VARCHAR(20)
|
|
, @UserName VARCHAR(80) = NULL
|
|
, @SendingCountryCode VARCHAR(10) = NULL
|
|
, @PayoutCountryCode VARCHAR(10) = NULL
|
|
, @PayoutTypeCode VARCHAR(10) = NULL
|
|
, @PayoutBankCode VARCHAR(10) = NULL
|
|
, @PayoutBankBranchCode VARCHAR(10) = NULL
|
|
, @PayoutCurrencyCode VARCHAR(10) = NULL
|
|
, @SendingCurrencyCode VARCHAR(10) = NULL
|
|
, @CollectionAmount MONEY = NULL
|
|
, @TransferAmount MONEY = NULL
|
|
, @PayoutAmount MONEY = NULL
|
|
, @ServiceCharge MONEY = NULL
|
|
, @ExchangeRate FLOAT = NULL
|
|
, @RemittancePurpose INT = NULL
|
|
, @SourceOfFund INT = NULL
|
|
, @Relationship INT = NULL
|
|
, @Occupation INT = NULL
|
|
, @PartnerTransactionId VARCHAR(40) = NULL
|
|
, @CalcBy CHAR(1) = NULL
|
|
, @AgentId INT = NULL
|
|
--SENDER DETAILS
|
|
, @SenderFirstName VARCHAR(60) = NULL
|
|
, @SenderMiddleName VARCHAR(60) = NULL
|
|
, @SenderLastName VARCHAR(40) = NULL
|
|
, @SenderIdType INT = NULL
|
|
, @SenderIdNo VARCHAR(20) = NULL
|
|
, @SenderIdIssuedDate VARCHAR(10) = NULL
|
|
, @SenderIdValidDate VARCHAR(10) = NULL
|
|
, @SenderMobile VARCHAR(15) = NULL
|
|
, @SenderNativeCountryCode VARCHAR(10) = NULL
|
|
, @SenderCity VARCHAR(80) = NULL
|
|
, @SenderAddress VARCHAR(150) = NULL
|
|
, @SenderEmail VARCHAR(150) = NULL
|
|
, @SenderGender VARCHAR(10) = NULL
|
|
, @SenderDOB VARCHAR(10) = NULL
|
|
--RECEIVER DETAILS
|
|
, @ReceiverFirstName VARCHAR(60) = NULL
|
|
, @ReceiverMiddleName VARCHAR(60) = NULL
|
|
, @ReceiverLastName VARCHAR(60) = NULL
|
|
, @ReceiverIdType INT = NULL
|
|
, @ReceiverIdNo VARCHAR(20) = NULL
|
|
, @ReceiverIdIssuedDate VARCHAR(10) = NULL
|
|
, @ReceiverIdValidDate VARCHAR(10) = NULL
|
|
, @ReceiverMobile VARCHAR(15) = NULL
|
|
, @ReceiverCity VARCHAR(80) = NULL
|
|
, @ReceiverAddress VARCHAR(150) = NULL
|
|
, @ReceiverEmail VARCHAR(150) = NULL
|
|
, @ReceiverAccountNo VARCHAR(40) = NULL
|
|
, @IpAddress VARCHAR(20) = NULL
|
|
, @CheckOfacFlag CHAR(1) = NULL
|
|
)
|
|
AS
|
|
;
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRY
|
|
BEGIN
|
|
DECLARE @ErrorMsg VARCHAR(MAX) = NULL
|
|
IF @Flag = 'SEND'
|
|
BEGIN
|
|
IF ISNULL(@CheckOfacFlag, '') = ''
|
|
SET @CheckOfacFlag = 'N'
|
|
IF EXISTS(SELECT TOP 1 1 FROM remitTranTemp (NOLOCK) WHERE controlNo2 = dbo.fnaencryptstring(@PartnerTransactionId)) OR EXISTS (SELECT TOP 1 1 FROM remitTran (NOLOCK) WHERE controlNo2 = dbo.fnaencryptstring(@PartnerTransactionId))
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Duplicate partner transaction id.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @PCountryId INT, @DeliveryMethodId INT, @PayoutPartner INT, @ExRateCalcByPartner BIT, @ScountryId INT
|
|
DECLARE @SSuperAgentId INT, @PAgent INT, @SAgentId INT = @AgentId, @PCountryName VARCHAR(80), @SCountryName VARCHAR(80)
|
|
|
|
SELECT @PCountryId = countryId, @PCountryName = countryName
|
|
FROM COUNTRYMASTER (NOLOCK)
|
|
WHERE countryCode = @PayoutCountryCode
|
|
|
|
IF ISNULL(@PCountryId, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid PayoutCountryCode, contact Head Office.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @ScountryId = countryId, @SCountryName = countryName
|
|
FROM COUNTRYMASTER (NOLOCK)
|
|
WHERE countryCode = @SendingCountryCode
|
|
|
|
IF ISNULL(@ScountryId, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid SendingCountryCode, contact Head Office.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @DeliveryMethodName VARCHAR(40)
|
|
SELECT @DeliveryMethodId = serviceTypeId, @DeliveryMethodName = typeTitle
|
|
FROM SERVICETYPEMASTER (NOLOCK)
|
|
WHERE serviceCode = @PayoutTypeCode
|
|
|
|
IF ISNULL(@DeliveryMethodId, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid PaymentTypeCode, contact Head Office.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @PayoutPartner = AGENTID
|
|
,@ExRateCalcByPartner = ISNULL(exRateCalByPartner, 0)
|
|
FROM TblPartnerwiseCountry(NOLOCK)
|
|
WHERE CountryId = @PCountryId
|
|
AND IsActive = 1
|
|
AND ISNULL(PaymentMethod, @DeliveryMethodId) = @DeliveryMethodId
|
|
|
|
IF ISNULL(@PayoutPartner, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Pyout partner not defined for selected Payout Country, contact Head Office.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @PAgent = AGENTID
|
|
FROM AGENTMASTER(NOLOCK)
|
|
WHERE PARENTID = @PayoutPartner
|
|
AND ISNULL(ISSETTLINGAGENT, 'N') = 'Y';
|
|
|
|
DECLARE @PSuperAgent INT, @PSuperAgentName VARCHAR(150), @PAgentName VARCHAR(150), @PBranch INT, @PBranchName VARCHAR(150)
|
|
SELECT @PSuperAgent = sSuperAgent
|
|
,@PSuperAgentName = sSuperAgentName
|
|
,@PAgent = sAgent
|
|
,@PAgentName = sAgentName
|
|
,@PBranch = sBranch
|
|
,@PBranchName = sBranchName
|
|
FROM dbo.FNAGetBranchFullDetails(@PAGENT)
|
|
|
|
DECLARE @PCurrencyCode VARCHAR(10)
|
|
SELECT @PCurrencyCode = pCurrency
|
|
FROM dbo.exRateTreasury WITH (NOLOCK)
|
|
WHERE pCountry = @PCountryId
|
|
AND pAgent = @PAgent
|
|
|
|
IF @PCurrencyCode IS NULL
|
|
SELECT @PCurrencyCode = pCurrency
|
|
FROM dbo.exRateTreasury WITH (NOLOCK)
|
|
WHERE pCountry = @PCountryId
|
|
AND pAgent IS NULL
|
|
|
|
IF @PCurrencyCode IS NULL
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Currency not been defined yet for receiving country, contact Head Office.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
IF @PCurrencyCode <> @PayoutCurrencyCode
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid PayoutCurrencyCode, contact Head Office.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @Place INT, @CurrDecimal INT
|
|
SELECT @Place = place
|
|
,@CurrDecimal = currDecimal
|
|
FROM currencyPayoutRound WITH (NOLOCK)
|
|
WHERE ISNULL(isDeleted, 'N') = 'N'
|
|
AND currency = @PCurrencyCode
|
|
AND ISNULL(tranType, @DeliveryMethodId) = @DeliveryMethodId
|
|
|
|
SET @currDecimal = ISNULL(@currDecimal, 0)
|
|
|
|
|
|
DECLARE @ExRateCalculated FLOAT, @sCurrCostRate FLOAT, @sCurrHoMargin FLOAT, @sCurrAgentMargin FLOAT
|
|
, @pCurrCostRate FLOAT, @pCurrHoMargin FLOAT, @pCurrAgentMargin FLOAT, @agentCrossSettRate FLOAT
|
|
|
|
SELECT @ExRateCalculated = customerRate
|
|
,@sCurrCostRate = sCurrCostRate
|
|
,@sCurrHoMargin = sCurrHoMargin
|
|
,@sCurrAgentMargin = sCurrAgentMargin
|
|
,@pCurrCostRate = pCurrCostRate
|
|
,@pCurrHoMargin = pCurrHoMargin
|
|
,@pCurrAgentMargin = pCurrAgentMargin
|
|
,@agentCrossSettRate = agentCrossSettRate
|
|
FROM dbo.FNAGetExRate(@ScountryId, @SAgentId, NULL, @SendingCurrencyCode, @pCountryId, @pAgent, @PayoutCurrencyCode, @DeliveryMethodId)
|
|
|
|
IF ISNULL(@ExRateCalculated, 0) = 0
|
|
BEGIN
|
|
SET @ErrorMsg = 'Exchange rate not defined yet for receiving currency (' + @PCurrencyCode + ')'
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF ISNULL(@ExRateCalculated, 0) <> ISNULL(@ExchangeRate, -1)
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid exchange rate passed or exchange rate changed in the system, recalculate the amount details and try again!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @ServiceChargeCalculated MONEY, @TransferAmountCalculated MONEY, @PayoutAmountCalculated MONEY, @CollectionAmountCalculated MONEY
|
|
DECLARE @SSuperAgentName VARCHAR(150), @SAgentName VARCHAR(150), @SBranch INT, @SBranchName VARCHAR(150),@SendingCurrCodeSC varchar(3)
|
|
SELECT @SSuperAgentId = sSuperAgent
|
|
,@SSuperAgentName = sSuperAgentName
|
|
,@SAgentName = sAgentName
|
|
,@SBranch = sBranch
|
|
,@SBranchName = sBranchName
|
|
FROM dbo.FNAGetBranchFullDetails(@SAgentId)
|
|
|
|
IF ISNULL(@CalcBy, '') = ''
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Calc by can not be empty!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @CalcBy NOT IN ('C', 'P')
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Calc by can not be other than ''C'' or ''P''!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @CalcBy = 'C' AND ISNULL(@CollectionAmount, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Collection amount can not be 0 or empty for Calc By ''C''!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @CalcBy = 'P' AND ISNULL(@PayoutAmount, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Payout amount can not be 0 or empty for Calc By ''P''!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
IF @CalcBy = 'C'
|
|
BEGIN
|
|
SELECT @ServiceChargeCalculated = amount
|
|
FROM [dbo].FNAGetServiceCharge(@ScountryId, @SSuperAgentId, @SAgentId, NULL, @PCountryId, @PSuperAgent, @PAgent, @PBranch, @DeliveryMethodId, @CollectionAmount, @SendingCurrencyCode)
|
|
|
|
IF ISNULL(@ServiceChargeCalculated, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Service charge not defined yet for receiving country', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SET @TransferAmountCalculated = @CollectionAmount - @ServiceChargeCalculated
|
|
SET @PayoutAmountCalculated = @TransferAmountCalculated * @ExRateCalculated
|
|
SET @PayoutAmountCalculated = FLOOR(@PayoutAmountCalculated)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT TOP 1 @SendingCurrCodeSC= currency
|
|
FROM sendTranLimit WITH ( NOLOCK )
|
|
WHERE countryId = @ScountryId order by stlid desc
|
|
SET @TransferAmountCalculated = CEILING(@PayoutAmount / (@ExRateCalculated))
|
|
|
|
SELECT @ServiceChargeCalculated = amount
|
|
FROM [dbo].FNAGetServiceCharge(@ScountryId, @SSuperAgentId, @SAgentId, NULL, @PCountryId, @PSuperAgent, @PAgent, @PBranch, @DeliveryMethodId, @PayoutAmount, ISNULL( @SendingCurrCodeSC,@SendingCurrencyCode))
|
|
|
|
IF ISNULL(@ServiceChargeCalculated, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Service charge not defined yet for receiving country', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SET @CollectionAmountCalculated = @TransferAmountCalculated + @ServiceChargeCalculated
|
|
SET @CollectionAmountCalculated = CEILING(@CollectionAmountCalculated)
|
|
END
|
|
|
|
IF @CollectionAmountCalculated <= 0 OR @PayoutAmountCalculated <= 0 OR @TransferAmountCalculated <= 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid payout/transfer amount/collect amount found!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
IF @CalcBy = 'C'
|
|
BEGIN
|
|
IF ISNULL(@PayoutAmount, 0) <> ISNULL(@PayoutAmountCalculated, -1)
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Payout Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF ISNULL(@TransferAmount, 0) <> ISNULL(@TransferAmountCalculated, -1)
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Payout Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
END
|
|
--ELSE
|
|
--BEGIN
|
|
-- IF ISNULL(@CollectionAmount, 0) <> ISNULL(@CollectionAmountCalculated, -1)
|
|
-- BEGIN
|
|
-- EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Collection Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
|
|
-- RETURN;
|
|
-- END
|
|
-- IF ISNULL(@TransferAmount, 0) <> ISNULL(@TransferAmountCalculated, -1)
|
|
-- BEGIN
|
|
-- EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Payout Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
|
|
-- RETURN;
|
|
-- END
|
|
--END
|
|
|
|
DECLARE @ErrorCode VARCHAR(10)
|
|
--EXEC PROC_CHECKCOUNTRYLIMIT @flag = 's-limit'
|
|
-- ,@cAmt = @CollectionAmount
|
|
-- ,@pAmt = @PayoutAmount
|
|
-- ,@sCountryId = @sCountryId
|
|
-- ,@collMode = NULL
|
|
-- ,@deliveryMethod = @DeliveryMethodId
|
|
-- ,@sendingCustType = NULL
|
|
-- ,@pCountryId = @PCountryId
|
|
-- ,@pCurr = @PayoutCurrencyCode
|
|
-- ,@collCurr = @SendingCurrencyCode
|
|
-- ,@pAgent = @PAgent
|
|
-- ,@sAgent = @SAgentId
|
|
-- ,@sBranch = NULL
|
|
-- ,@msg = @ErrorMsg OUT
|
|
-- ,@errorCode = @ErrorCode OUT
|
|
|
|
--IF @ErrorCode <> '0'
|
|
--BEGIN
|
|
-- EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
|
|
-- RETURN;
|
|
--END
|
|
|
|
EXEC PROC_CHECKCOUNTRYLIMIT @flag = 'r-limit'
|
|
,@cAmt = @CollectionAmount
|
|
,@pAmt = @PayoutAmount
|
|
,@sCountryId = @sCountryId
|
|
,@collMode = NULL
|
|
,@deliveryMethod = @DeliveryMethodId
|
|
,@sendingCustType = NULL
|
|
,@pCountryId = @PCountryId
|
|
,@pCurr = @PayoutCurrencyCode
|
|
,@collCurr = @SendingCurrencyCode
|
|
,@pAgent = @PAgent
|
|
,@sAgent = @SAgentId
|
|
,@sBranch = NULL
|
|
,@msg = @ErrorMsg OUT
|
|
,@errorCode = @ErrorCode OUT
|
|
|
|
IF @ErrorCode <> '0'
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @PBankId INT, @PBankName VARCHAR(100), @PBankBranchId INT, @PBankBranchName VARCHAR(100)
|
|
IF @PayoutTypeCode = 'BD'
|
|
BEGIN
|
|
IF @PayoutBankCode IS NULL
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Payout Bank can not be null for transaction type Bank Deposit!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @ReceiverAccountNo IS NULL
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Receiver Account Number can not be null for transaction type Bank Deposit!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @PBankId = BANK_ID, @PBankName = BANK_NAME
|
|
FROM API_BANK_LIST (NOLOCK)
|
|
WHERE JME_BANK_CODE = @PayoutBankCode
|
|
AND API_PARTNER_ID = @PayoutPartner
|
|
|
|
IF ISNULL(@PBankId, 0) = 0
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid bank details!!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @PBankBranchId = BRANCH_ID, @PBankBranchName = BRANCH_NAME
|
|
FROM API_BANK_BRANCH_LIST (NOLOCK)
|
|
WHERE BRANCH_ID = @PayoutBankBranchCode
|
|
END
|
|
|
|
|
|
--START OFAC Checking
|
|
DECLARE @ReceiverFullName VARCHAR(200)
|
|
, @SenderFullName VARCHAR(200)
|
|
, @OfacReason VARCHAR(MAX)
|
|
, @OfacReasonReceiver VARCHAR(MAX)
|
|
, @OfacReasonFull VARCHAR(MAX)
|
|
, @OfacErrorMessage VARCHAR(200) = ''
|
|
|
|
SET @ReceiverFullName = @ReceiverFirstName + ISNULL(' ' + @ReceiverMiddleName, '') + @ReceiverLastName
|
|
SET @SenderFullName = @SenderFirstName + ISNULL(' ' + @SenderMiddleName, '') + @SenderLastName
|
|
|
|
IF ISNULL(@CheckOfacFlag, 'N') = 'Y'
|
|
BEGIN
|
|
EXEC proc_ofacTracker @flag = 't'
|
|
,@name = @SenderFullName
|
|
,@Result = @OfacReason OUTPUT
|
|
|
|
SET @OfacReasonFull = @OfacReason
|
|
IF @SenderFullName = @ReceiverFullName
|
|
BEGIN
|
|
SET @OfacReasonReceiver = @OfacReason
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
EXEC proc_ofacTracker @flag = 't'
|
|
,@name = @ReceiverFullName
|
|
,@Result = @OfacReasonReceiver OUTPUT
|
|
|
|
SET @OfacReasonFull = @OfacReasonFull + ISNULL(',' + @OfacReasonReceiver, '')
|
|
END
|
|
|
|
IF ISNULL(@OfacReason, '') <> ''
|
|
BEGIN
|
|
SET @OfacErrorMessage = 'OFAC matched by Sender Name'
|
|
END
|
|
IF ISNULL(@OfacReasonReceiver, '') <> ''
|
|
BEGIN
|
|
SET @OfacErrorMessage = 'OFAC matched by Receiver Name'
|
|
END
|
|
|
|
IF ISNULL(@OfacReason, '') <> '' AND ISNULL(@OfacReasonReceiver, '') <> ''
|
|
BEGIN
|
|
SET @OfacErrorMessage = 'OFAC matched by both Sender Name and Receiver Name'
|
|
END
|
|
END
|
|
|
|
DECLARE @SendingAgentCommission MONEY, @PayoutAgentCommission MONEY
|
|
DECLARE @PAgentCommCurrency VARCHAR(5), @sAgentCommCurrency VARCHAR(5)
|
|
SELECT @pAgentCommCurrency = DBO.FNAGetPayCommCurrency(@SSuperAgentId, @SAgentId, NULL, @ScountryId, @PSuperAgent, @PBranch, @PCountryId)
|
|
|
|
SELECT @PayoutAgentCommission = amount
|
|
FROM dbo.FNAGetPayComm(@SAgentId, @ScountryId, NULL, NULL, @PCountryId, NULL, @PAgent, @PAgentCommCurrency, @DeliveryMethodId, @CollectionAmount, @PayoutAmount, @ServiceCharge, @TransferAmount, NULL)
|
|
|
|
DECLARE @SSettlementRate FLOAT, @PSettlementRate FLOAT
|
|
SET @SSettlementRate = @SCurrCostRate + @SCurrHoMargin
|
|
SET @PSettlementRate = @PCurrCostRate - @PCurrHoMargin
|
|
|
|
SELECT @SendingAgentCommission = amount, @sAgentCommCurrency = commissionCurrency FROM dbo.FNAGetSendComm(
|
|
@ScountryId, @SSuperAgentId, @SAgentId, NULL,
|
|
@pCountryId, @pSuperAgent, @pAgent, NULL,
|
|
@SendingCurrencyCode, @deliveryMethodId, @CollectionAmount, @PayoutAmount, @ServiceCharge, NULL, NULL,
|
|
@SSettlementRate, @PSettlementRate)
|
|
|
|
DECLARE @ControlNo VARCHAR(20) = '779' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 9)
|
|
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM controlNoList WITH (NOLOCK)
|
|
WHERE controlNo = @ControlNo
|
|
)
|
|
BEGIN
|
|
SET @ControlNo = '779' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 9)
|
|
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM controlNoList WITH (NOLOCK)
|
|
WHERE controlNo = @ControlNo
|
|
)
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Technical error occurred. Please try again!', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
DECLARE @ControlNoEncrypted VARCHAR(20) = dbo.FNAEncryptString(@ControlNo)
|
|
DECLARE @RelationshipName VARCHAR(150), @RemittancePurposeName VARCHAR(150), @SourceOfFundName VARCHAR(150)
|
|
|
|
SELECT @RelationshipName = detailTitle
|
|
FROM staticDataValue (NOLOCK)
|
|
WHERE valueId = @Relationship
|
|
|
|
SELECT @RemittancePurposeName = detailTitle
|
|
FROM staticDataValue (NOLOCK)
|
|
WHERE valueId = @RemittancePurpose
|
|
|
|
SELECT @SourceOfFundName = detailTitle
|
|
FROM staticDataValue (NOLOCK)
|
|
WHERE valueId = @SourceOfFund
|
|
|
|
INSERT INTO remitTranTemp (controlNo, sCurrCostRate, sCurrHoMargin, sCurrAgentMargin, pCurrCostRate, pCurrHoMargin, pCurrAgentMargin, agentCrossSettRate
|
|
, customerRate, serviceCharge, sAgentComm, sAgentCommCurrency, pAgentComm, pAgentCommCurrency, sSuperAgent, sSuperAgentName, sAgent
|
|
, sAgentName, sBranch, sBranchName, sCountry, pSuperAgent, pSuperAgentName, pAgent, pAgentName, pBranch, pBranchName
|
|
, pCountry, paymentMethod, pBank, pBankName, pBankBranch, pBankBranchName, accountNo, collCurr, tAmt, cAmt
|
|
, pAmt, payoutCurr, relWithSender, purposeOfRemit, sourceOfFund, tranStatus, payStatus, createdDate, createdDateLocal, createdBy
|
|
, tranType, senderName, receiverName, controlNo2, sRouteId)
|
|
|
|
SELECT @controlNoEncrypted, @sCurrCostRate, @sCurrHoMargin, @sCurrAgentMargin, @pCurrCostRate, @pCurrHoMargin, @pCurrAgentMargin, @agentCrossSettRate
|
|
, @ExRateCalculated, @serviceCharge, @SendingAgentCommission, @sAgentCommCurrency, @PayoutAgentCommission, @PAgentCommCurrency ,@SSuperAgentId, @SSuperAgentName, @SAgentId
|
|
, @SAgentName, @SBranch, @SBranchName, @SCountryName, @PSuperAgent, @PSuperAgentName, @PAgent, @PAgentName, @PBranch, @PBranchName
|
|
, @PCountryName, @DeliveryMethodName, @PBankId, @PBankName, @PBankBranchId, @PBankBranchName, @ReceiverAccountNo, @SendingCurrencyCode, @TransferAmount, @CollectionAmount
|
|
, @PayoutAmount, @PayoutCurrencyCode, @RelationshipName, @RemittancePurposeName, @SourceOfFundName, 'Hold', 'Unpaid', GETDATE(), GETDATE(), @UserName
|
|
, 'I', @SenderFullName, @ReceiverFullName, dbo.FNAEncryptString(@PartnerTransactionId), 'IN'
|
|
|
|
DECLARE @Id BIGINT = @@IDENTITY
|
|
|
|
INSERT INTO controlNoList (
|
|
controlNo
|
|
, createdby
|
|
)
|
|
SELECT @ControlNo
|
|
, @UserName
|
|
|
|
DECLARE @SenderNativeCountryName VARCHAR(80), @SenderIdTypeName VARCHAR(150), @OccupationName VARCHAR(150)
|
|
|
|
SELECT @SenderNativeCountryName = CountryName
|
|
FROM COUNTRYMASTER (NOLOCK)
|
|
WHERE countryCode = @SenderNativeCountryCode
|
|
|
|
SELECT @SenderIdTypeName = detailTitle
|
|
FROM staticDataValue (NOLOCK)
|
|
WHERE valueId = @SenderIdType
|
|
|
|
SELECT @OccupationName = detailTitle
|
|
FROM staticDataValue (NOLOCK)
|
|
WHERE valueId = @Occupation
|
|
|
|
INSERT INTO tranSendersTemp (tranId, customerId, membershipId, firstName, middleName, lastName1, fullName, country, [address], city, email, mobile
|
|
, nativeCountry, dob, idType, idNumber, gender, issuedDate, validDate, occupation, ipAddress)
|
|
SELECT @Id, 0, NULL, @SenderFirstName, @SenderMiddleName, @SenderLastName, @SenderFullName, @SCountryName, @SenderAddress, @SenderCity, @SenderEmail, @SenderMobile
|
|
, @SenderNativeCountryName, @SenderDOB, @SenderIdTypeName, @SenderIdNo, @SenderGender, @SenderIdIssuedDate, @SenderIdValidDate, @OccupationName, @IpAddress
|
|
|
|
DECLARE @ReceiverIdTypeName VARCHAR(150)
|
|
|
|
SELECT @ReceiverIdTypeName = detailTitle
|
|
FROM staticDataValue (NOLOCK)
|
|
WHERE valueId = @ReceiverIdType
|
|
|
|
INSERT INTO tranReceiversTemp (tranId, customerId, membershipId, firstName, middleName, lastName1, fullName, country, [address], city, email, mobile
|
|
, idType, idNumber, issuedDate, validDate, accountNo)
|
|
SELECT @id, 0, '', @ReceiverFirstName, @ReceiverMiddleName, @ReceiverLastName, @ReceiverFullName, @PCountryName, @ReceiverAddress, @ReceiverCity, @ReceiverEmail, @ReceiverMobile
|
|
, @ReceiverIdType, @ReceiverIdNo, @ReceiverIdIssuedDate, @ReceiverIdValidDate, @ReceiverAccountNo
|
|
|
|
IF ISNULL(@CheckOfacFlag, 'N') = 'Y'
|
|
BEGIN
|
|
IF (ISNULL(@OfacReasonFull, '') <> '')
|
|
BEGIN
|
|
CREATE TABLE #temp (sno INT, rmrks NVARCHAR(MAX))
|
|
|
|
INSERT INTO #temp (sno, rmrks)
|
|
EXEC proc_sendPageLoadData @flag = 'ofac'
|
|
,@user = @UserName
|
|
,@blackListIds = @OfacReasonFull
|
|
|
|
DECLARE @OfacResultJson NVARCHAR(MAX)
|
|
|
|
SET @OfacResultJson = (SELECT *
|
|
FROM #temp
|
|
FOR JSON AUTO)
|
|
|
|
INSERT remitTranOfac (
|
|
TranId
|
|
,blackListId
|
|
,reason
|
|
,flag
|
|
,TRACK_BY
|
|
,ofacDetail
|
|
)
|
|
SELECT @id
|
|
,@OfacReasonFull
|
|
,@OfacErrorMessage
|
|
,dbo.FNAGetOFAC_Flag(@OfacReasonFull)
|
|
,'OFAC'
|
|
,@OfacResultJson
|
|
|
|
UPDATE remitTranTemp
|
|
SET tranStatus = 'OFAC Hold'
|
|
WHERE id = @Id
|
|
END
|
|
END
|
|
|
|
SELECT ErrorCode = 100
|
|
, ErrorMessage = CASE WHEN tranStatus = 'OFAC Hold' THEN 'Transaction is successful but is in OFAC hold status and will be manually verified!' ELSE 'Transaction is successful!' END
|
|
, IMEControlNo = @ControlNo
|
|
, PartnerTransactionId = @PartnerTransactionId
|
|
, TranStatus = tranStatus
|
|
, ExRate = customerRate
|
|
--, SendingCommission = sAgentComm
|
|
--, SendingCommissionCurrency = sAgentCommCurrency
|
|
, CollectionAmount = cAmt
|
|
, TransferAmount = tAmt
|
|
, PayoutAmount = pAmt
|
|
FROM remitTranTemp (NOLOCK)
|
|
WHERE id = @Id;
|
|
END
|
|
END
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT>0
|
|
ROLLBACK TRANSACTION
|
|
|
|
SET @ErrorMsg = 'Exception executing SP: ' + ERROR_MESSAGE()
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
|
|
END CATCH
|