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.
1159 lines
56 KiB
1159 lines
56 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[proc_get_exRate_master] Script Date: 6/13/2024 9:12:32 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROC [dbo].[proc_get_exRate_master] (
|
|
@flag VARCHAR(20)
|
|
,@user VARCHAR(100) = NULL
|
|
,@sCountryId INT = NULL
|
|
,@sAgent INT = NULL
|
|
,@sSuperAgent INT = NULL
|
|
,@sBranch INT = NULL
|
|
,@senderId BIGINT = NULL
|
|
,@collCurr VARCHAR(5) = NULL
|
|
,@pCountryId INT = NULL
|
|
,@pCountry VARCHAR(50) = NULL
|
|
,@pAgent INT = NULL
|
|
,@pCurr VARCHAR(5) = NULL
|
|
,@deliveryMethodId INT = NULL
|
|
,@cAmt MONEY = NULL
|
|
,@pAmt MONEY = NULL
|
|
,@calBy CHAR(1) = NULL
|
|
,@couponCode VARCHAR(30) = NULL
|
|
,@schemeId VARCHAR(10) = NULL
|
|
,@payOutPartner INT = NULL
|
|
,@paymentType VARCHAR(50) = NULL
|
|
,@cardOnline VARCHAR(10) = NULL
|
|
,@tpExRate FLOAT = NULL
|
|
,@isManualSc CHAR(1) = NULL
|
|
,@manualSc MONEY = NULL
|
|
,@ProcessFor VARCHAR(20) = NULL
|
|
,@discountedFee VARCHAR(50) = NULL
|
|
,@isPromoCode CHAR(1) = NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
-----------------------------------------
|
|
--Sep 22 -> #109 ->Change the logic to choose highest Rate in case of Nepal
|
|
-- #101 , #361 - Mobile Changes for Multi-Lingual
|
|
-- #1526 - Post production fixes for Redeem & Earn
|
|
-- #1590 - Customer Loyalty
|
|
-- #5968 - sc revised
|
|
-- #18600 - change in @flag = 'false' for exRate
|
|
-- #27928 added validation for debit card payment
|
|
--------------------------------------------
|
|
BEGIN
|
|
DECLARE @scValue MONEY
|
|
,@scAction CHAR(2)
|
|
,@scOffer MONEY
|
|
,@exRateOffer FLOAT
|
|
,@scDiscount MONEY
|
|
DECLARE @place INT
|
|
,@currDecimal INT
|
|
,@collMode VARCHAR(30)
|
|
,@sendingCustType VARCHAR(30)
|
|
,@msg VARCHAR(150)
|
|
,@errorCode INT
|
|
DECLARE @exRateCalByPartner BIT
|
|
,@pSuperAgent INT
|
|
,@serviceCharge MONEY
|
|
,@serviceChargetmp MONEY
|
|
,@tAmt MONEY
|
|
,@pSuperAgentName VARCHAR(100)
|
|
,@pBranch INT
|
|
DECLARE @pAgentName VARCHAR(100)
|
|
,@pBranchName VARCHAR(100)
|
|
,@exRate FLOAT
|
|
,@pCurrHoMargin FLOAT
|
|
,@FOREX_SESSION_ID VARCHAR(40)
|
|
DECLARE @tranCount INT = 0
|
|
,@schemeCount INT = 0
|
|
,@schemeAppliedMsg VARCHAR(100) = ''
|
|
,@isFirstTran CHAR(1) = 'N'
|
|
,@createdFrom CHAR(1) = NULL
|
|
,@isEligible CHAR(1) = 'N'
|
|
,@introducer VARCHAR(25) = NULL
|
|
|
|
--IF @ProcessFor IN ('send')
|
|
--BEGIN
|
|
IF ISNULL(@user, 'onlinedefault') <> 'onlinedefault' --OR @user is not null
|
|
BEGIN
|
|
PRINT 'a'
|
|
|
|
IF @senderId IS NULL
|
|
OR @senderId = 0
|
|
BEGIN
|
|
SET @senderId = (
|
|
SELECT customerId
|
|
FROM customermaster(NOLOCK)
|
|
WHERE username = @user
|
|
)
|
|
END
|
|
|
|
--EXEC PROC_Customer_LoyaltyV2 @flag = 'check-eligible-v2'
|
|
-- ,@isEligible = @isEligible OUT
|
|
-- ,@referralCode = @introducer
|
|
-- ,@tranCount = @trancount OUT
|
|
-- ,@schemeCount = @schemeCount OUT
|
|
-- ,@customerId = @senderId
|
|
-- ,@createdFrom = 'M'
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM (
|
|
SELECT TOP 1 customerId
|
|
FROM TRANSENDERS TS(NOLOCK)
|
|
INNER JOIN remittran(NOLOCK) rt ON rt.id = ts.tranId
|
|
WHERE customerId = @SenderId
|
|
AND rt.tranStatus <> 'CANCEL' --AND rt.tranType='M'
|
|
|
|
UNION ALL
|
|
|
|
SELECT TOP 1 customerId
|
|
FROM TRANSENDERSTEMP TT(NOLOCK)
|
|
INNER JOIN remittrantemp(NOLOCK) rt ON rt.id = tt.tranId
|
|
WHERE customerId = @SenderId
|
|
AND rt.tranStatus <> 'CANCEL' --AND rt.tranType='M'
|
|
) a
|
|
WHERE customerId = @SenderId
|
|
)
|
|
BEGIN
|
|
SET @isFirstTran = 'Y'
|
|
END
|
|
END
|
|
|
|
PRINT '@isFirstTran' + '-> ' + @isFirstTran
|
|
|
|
SELECT @createdFrom = serviceUsedFor
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @senderId
|
|
|
|
PRINT '@senderId'
|
|
PRINT @senderId;
|
|
|
|
SET @sCountryId = 233
|
|
SET @sAgent = '394395';
|
|
|
|
IF ISNULL(@pCountryId, 0) = 0
|
|
SELECT @pCountryId = countryId
|
|
FROM countryMaster(NOLOCK)
|
|
WHERE COUNTRYNAME = @pCountry
|
|
|
|
IF @flag = 'False'
|
|
AND @ProcessFor IN ('dashboard')
|
|
BEGIN
|
|
SELECT @payoutPartner = AGENTID
|
|
,@exRateCalByPartner = ISNULL(exRateCalByPartner, 0)
|
|
FROM TblPartnerwiseCountry(NOLOCK)
|
|
WHERE CountryId = @pCountryId
|
|
AND IsActive = 1
|
|
AND ISNULL(IsMobileEnabled, 0) = 1
|
|
AND ISNULL(PaymentMethod, @deliveryMethodId) = @deliveryMethodId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @payoutPartner = AGENTID
|
|
,@exRateCalByPartner = ISNULL(exRateCalByPartner, 0)
|
|
FROM TblPartnerwiseCountry(NOLOCK)
|
|
WHERE CountryId = @pCountryId
|
|
AND IsActive = 1
|
|
AND ISNULL(PaymentMethod, @deliveryMethodId) = @deliveryMethodId
|
|
END
|
|
|
|
PRINT '@payoutPartner'
|
|
PRINT @payoutPartner;
|
|
|
|
IF @payoutPartner IS NOT NULL
|
|
BEGIN
|
|
--GET PAYOUT AGENT DETAILS
|
|
SELECT @PAGENT = AGENTID
|
|
FROM AGENTMASTER(NOLOCK)
|
|
WHERE PARENTID = @payoutPartner
|
|
AND ISNULL(ISSETTLINGAGENT, 'N') = 'Y';
|
|
|
|
SELECT @pSuperAgent = sSuperAgent
|
|
,@pSuperAgentName = sSuperAgentName
|
|
,@pAgent = sAgent
|
|
,@pAgentName = sAgentName
|
|
,@pBranch = sBranch
|
|
,@pBranchName = sBranchName
|
|
FROM dbo.FNAGetBranchFullDetails(@PAGENT)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Partner not yet mapped for the selected country!' Msg
|
|
,NULL id
|
|
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @rowId INT
|
|
|
|
SELECT @scValue = 0
|
|
,@scOffer = 0
|
|
,@exRateOffer = 0
|
|
,@scDiscount = 0
|
|
|
|
SELECT @place = place
|
|
,@currDecimal = currDecimal
|
|
FROM currencyPayoutRound WITH (NOLOCK)
|
|
WHERE ISNULL(isDeleted, 'N') = 'N'
|
|
AND currency = @pCurr
|
|
AND ISNULL(tranType, @deliveryMethodId) = @deliveryMethodId
|
|
|
|
SET @currDecimal = ISNULL(@currDecimal, 0)
|
|
|
|
IF @pCurr IS NULL
|
|
BEGIN
|
|
SELECT '2' ErrorCode
|
|
,'Currency not been defined yet for receiving country' Msg
|
|
,NULL id
|
|
|
|
RETURN
|
|
END
|
|
|
|
--pRINT @pAgent;
|
|
IF @flag = 'false'
|
|
BEGIN
|
|
IF @pCountryId = 151
|
|
AND @deliveryMethodId = 1
|
|
BEGIN
|
|
SELECT AgentId
|
|
INTO #AgentList
|
|
FROM agentMaster(NOLOCK)
|
|
WHERE extCode = 'NP ANYWHERE'
|
|
|
|
SELECT @exRate = customerRate
|
|
,@pCurrHoMargin = pCurrHoMargin
|
|
FROM (
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY CUSTOMERRATE DESC
|
|
) ROW_NUM
|
|
,CUSTOMERRATE
|
|
,pCurrHoMargin
|
|
FROM #AgentList A
|
|
CROSS APPLY dbo.FNAGetExRate(@sCountryId, @sAgent, @sBranch, @collCurr, @pCountryId, AgentId, @pCurr, @deliveryMethodId) FN
|
|
) X
|
|
WHERE X.ROW_NUM = 1
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'cnbvnxc'
|
|
PRINT @sCountryId;
|
|
PRINT '@sAgent';
|
|
PRINT @sAgent;
|
|
PRINT '@sBranch';
|
|
PRINT @sBranch;
|
|
PRINT @collCurr;
|
|
PRINT @pCountryId;
|
|
PRINT @pCurr;
|
|
PRINT @deliveryMethodId;
|
|
PRINT '@pAgent';
|
|
PRINT @pAgent;
|
|
PRINT @payoutPartner;
|
|
|
|
SELECT @exRate = customerRate
|
|
,@pCurrHoMargin = pCurrHoMargin
|
|
FROM dbo.FNAGetExRate(@sCountryId, @sAgent, @sBranch, @collCurr, @pCountryId, @pAgent, @pCurr, @deliveryMethodId)
|
|
END
|
|
|
|
IF ISNULL(@exRate, 0) = 0
|
|
BEGIN
|
|
SELECT '3' ErrorCode
|
|
,'Exchange rate not defined yet for receiving currency (' + @pCurr + ')' Msg
|
|
,NULL id
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @tpExRate > 0
|
|
AND @isFirstTran = 'N'
|
|
BEGIN
|
|
SET @exRate = @exRate - @tpExRate
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
DECLARE @rate FLOAT
|
|
,@promRate FLOAT
|
|
|
|
SELECT @rate = ISNULL(margin, 0)
|
|
,@promRate = ISNULL(purchaseRate, 0)
|
|
FROM countryWiseExchangeRate
|
|
WHERE depositType = 'ONLINE'
|
|
AND countryId = @pCountryId
|
|
|
|
PRINT ' @rate'
|
|
PRINT @rate
|
|
PRINT '@promRate'
|
|
PRINT @promRate
|
|
|
|
IF @isFirstTran = 'Y'
|
|
SET @exRate = @exRate - (ISNULL(@rate, 0) + ISNULL(@promRate, 0))
|
|
ELSE
|
|
SET @exRate = @exRate - ISNULL(@rate, 0)
|
|
END
|
|
|
|
--IF(ISNULL(@discountedFee,0)> 0)
|
|
--BEGIN
|
|
-- IF dbo.FNA_GET_AVAILABLE_BALANCE_POINTS(@senderId) < ISNULL(@discountedFee, 0)
|
|
-- BEGIN
|
|
-- SELECT '1' ErrorCode
|
|
-- ,'You do not have sufficient Reward Amount!' Msg
|
|
-- ,NULL id
|
|
-- RETURN
|
|
-- END
|
|
--END
|
|
DECLARE @ValidationResult TABLE (
|
|
IS_VALID VARCHAR(50)
|
|
,ROW_ID INT
|
|
,PROMOTIONAL_CODE VARCHAR(50)
|
|
,PROMOTIONAL_MSG VARCHAR(255)
|
|
,
|
|
-- PROMOTION_TYPE INT,
|
|
PROMOTION_VALUE MONEY
|
|
,MIN_SENDING_AMT MONEY
|
|
,START_DT DATETIME
|
|
,END_DT DATETIME
|
|
)
|
|
DECLARE @availableRewardAmt INT
|
|
,@row_id INT
|
|
,@promoValue MONEY
|
|
,@minSendAmt MONEY
|
|
,@startDate DATETIME
|
|
,@endDate DATETIME
|
|
,@errMsg VARCHAR(100)
|
|
,@promoCode VARCHAR(100)
|
|
|
|
SELECT @availableRewardAmt = CAST(dbo.[FNA_GET_AVAILABLE_BALANCE_POINTS](@senderId) AS INT)
|
|
|
|
DECLARE @minCollAmt MONEY
|
|
,@maxRewardAmt MONEY
|
|
|
|
SELECT @maxRewardAmt = CAST(detailDesc AS MONEY)
|
|
FROM staticDataValue
|
|
WHERE typeID = '10200'
|
|
AND detailTitle = 'MAX_REWARD_AMT'
|
|
|
|
SELECT @minCollAmt = CAST(detailDesc AS MONEY)
|
|
FROM staticDataValue
|
|
WHERE typeID = '10200'
|
|
AND detailTitle = 'MIN_COLL_AMT_FOR_REWARD'
|
|
|
|
IF ISNUMERIC(ISNULL(@discountedFee, 0)) = 1
|
|
BEGIN
|
|
SET @isPromoCode = 'N'
|
|
|
|
IF (ISNULL(@discountedFee, 0) > 0)
|
|
BEGIN
|
|
IF (@availableRewardAmt < ISNULL(@discountedFee, 0))
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'You do not have sufficient Rewards!'
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF (ISNULL(@discountedFee, 0) > ISNULL(@availableRewardAmt, 0))
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Please enter the valid point. The amount cannot be more than available Reward Amount!' Msg
|
|
,NULL id
|
|
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @discountedFeeMoney MONEY = CAST(@discountedFee AS MONEY)
|
|
|
|
IF (
|
|
@discountedFeeMoney > '0.00'
|
|
AND @cAmt < @minCollAmt
|
|
AND @discountedFeeMoney > @maxRewardAmt
|
|
)
|
|
BEGIN
|
|
SELECT '8' ErrorCode
|
|
,'The maximum reward amount per transaction is ' + CAST(@maxRewardAmt AS VARCHAR(20)) + ' Pounds and the minimum amount that can be sent for each transaction is ' + CAST(@minCollAmt AS VARCHAR(20)) + ' Pounds' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @isPromoCode = 'Y'
|
|
|
|
PRINT 'promo'
|
|
PRINT @pCountryId
|
|
|
|
INSERT INTO @ValidationResult (
|
|
IS_VALID
|
|
,ROW_ID
|
|
,PROMOTIONAL_CODE
|
|
,PROMOTIONAL_MSG
|
|
,PROMOTION_VALUE
|
|
,MIN_SENDING_AMT
|
|
,START_DT
|
|
,END_DT
|
|
)
|
|
EXEC proc_CheckPromoValidity @flag = 'chk-validity'
|
|
,@countryId = @pCountryId
|
|
,@pmode = @deliveryMethodId
|
|
,@promoCode = @discountedFee
|
|
|
|
--select * FROM @ValidationResult;
|
|
--RETURN;
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM @ValidationResult
|
|
WHERE IS_VALID = 'VALID'
|
|
)
|
|
BEGIN
|
|
SELECT @row_id = ROW_ID
|
|
,@promoValue = PROMOTION_VALUE
|
|
,@minSendAmt = MIN_SENDING_AMT
|
|
,@startDate = START_DT
|
|
,@endDate = END_DT
|
|
FROM @ValidationResult
|
|
WHERE IS_VALID = 'VALID'
|
|
|
|
SET @promoCode = @discountedFee
|
|
SET @discountedFee = @promoValue
|
|
|
|
IF (@cAmt < @minSendAmt)
|
|
BEGIN
|
|
SELECT '8' ErrorCode
|
|
,'The minimum amount per transaction should be ' + CAST(@minSendAmt AS VARCHAR(20)) + ' Pounds to use Promo Code' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @errMsg = PROMOTIONAL_MSG
|
|
FROM @ValidationResult
|
|
WHERE IS_VALID = 'INVALID'
|
|
|
|
SELECT '1' ErrorCode
|
|
,@errMsg Msg
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
|
|
--PRINT '@isFirstTran:' + CAST( @isFirstTran AS VARCHAR);
|
|
--PRINT '@isEligible:' + CAST( @isEligible AS VARCHAR);
|
|
-- PRINT '@tranCount:' + CAST( @tranCount AS VARCHAR);
|
|
-- PRINT '@@schemeCount:' + CAST( @schemeCount AS VARCHAR);
|
|
IF @calBy = 'C'
|
|
BEGIN
|
|
BEGIN
|
|
IF ISNULL(@isManualSc, 'N') = 'N'
|
|
BEGIN
|
|
PRINT '@serviceChargetmp';
|
|
|
|
SELECT @serviceChargetmp = isnull(amount, 0)
|
|
FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @cAmt, @collCurr)
|
|
|
|
PRINT @serviceChargetmp
|
|
|
|
IF (@discountedFee < '0.00')
|
|
--AND (@serviceChargetmp < @discountedFee)
|
|
BEGIN
|
|
SELECT '8' ErrorCode
|
|
,'Redeem Amount cannot be less than zero' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--SELECT @serviceCharge = @serviceChargetmp - ISNULL(@discountedFee, 0)
|
|
SELECT @serviceCharge = @serviceChargetmp
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @serviceCharge = ISNULL(@manualSc, 0)
|
|
END
|
|
|
|
IF @serviceCharge IS NULL
|
|
AND ISNULL(@isManualSc, 'N') = 'N'
|
|
BEGIN
|
|
SELECT '4' ErrorCode
|
|
,'Service charge not defined yet for receiving country' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @scAction = 'PD' -- Percent Discount
|
|
BEGIN
|
|
SET @scOffer = (@scValue / 100) * @serviceCharge
|
|
SET @scDiscount = (@scValue / 100) * @serviceCharge
|
|
END
|
|
ELSE IF @scAction = 'FD' -- Flat Discount
|
|
BEGIN
|
|
SET @scDiscount = @scValue
|
|
END
|
|
ELSE IF @scAction = 'FV' -- Fixed Value
|
|
BEGIN
|
|
SET @scOffer = @scValue
|
|
SET @scDiscount = @serviceCharge - @scValue
|
|
END
|
|
|
|
--IF (@discountedFee > @serviceCharge)
|
|
-- BEGIN
|
|
-- SELECT '8' ErrorCode
|
|
-- ,'Redeem Amount cannot be higher than Transfer fee.' Msg
|
|
-- ,NULL id
|
|
-- RETURN;
|
|
-- END
|
|
SET @tAmt = @cAmt - @serviceCharge + @scDiscount + ISNULL(@discountedFee, 0)
|
|
SET @pAmt = @tAmt * (@exRate + @exRateOffer)
|
|
--PRINT @pAmt;
|
|
--SET @pAmt = FLOOR(@pAmt)
|
|
SET @tAmt = Cast(Round(@tAmt, 2, 0) AS DECIMAL(18, 2));
|
|
SET @pAmt = Cast(Round(@pAmt, 2, 0) AS DECIMAL(18, 2));
|
|
--PRINT @pAmt;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--SET @tAmt = CEILING(@pAmt/(@exRate + @exRateOffer), 0)
|
|
--SET @tAmt = CEILING(@pAmt / (@exRate + @exRateOffer))
|
|
SET @tAmt = @pAmt / (@exRate + @exRateOffer);
|
|
|
|
BEGIN
|
|
IF ISNULL(@isManualSc, 'N') = 'N'
|
|
BEGIN
|
|
SELECT @serviceChargetmp = isnull(amount, 0)
|
|
FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @tAmt, @collCurr)
|
|
|
|
IF (@discountedFee < '0.00')
|
|
--AND (@serviceChargetmp < @discountedFee)
|
|
BEGIN
|
|
SELECT '8' ErrorCode
|
|
,'Redeem Amount cannot be less than zero' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @serviceCharge = @serviceChargetmp;
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @serviceCharge = ISNULL(@manualSc, 0)
|
|
END
|
|
|
|
IF @serviceCharge IS NULL
|
|
BEGIN
|
|
SELECT '4' ErrorCode
|
|
,'Service charge not defined yet for receiving country' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @scAction = 'PD'
|
|
BEGIN
|
|
SET @scOffer = (@scValue / 100) * @serviceCharge
|
|
SET @scDiscount = (@scValue / 100) * @serviceCharge
|
|
END
|
|
ELSE IF @scAction = 'FD'
|
|
BEGIN
|
|
SET @scDiscount = @scValue
|
|
END
|
|
ELSE IF @scAction = 'FV'
|
|
BEGIN
|
|
SET @scOffer = @scValue
|
|
SET @scDiscount = @serviceCharge - @scValue
|
|
END
|
|
|
|
PRINT '@discountedFee'
|
|
PRINT @discountedFee
|
|
PRINT @serviceCharge
|
|
|
|
--IF (@discountedFee > @serviceCharge)
|
|
-- BEGIN
|
|
-- SELECT '8' ErrorCode
|
|
-- ,'Redeem Amount cannot be higher than Transfer fee.' Msg
|
|
-- ,NULL id
|
|
-- RETURN;
|
|
-- END
|
|
SET @cAmt = (@tAmt + @serviceCharge - @scDiscount) - ISNULL(@discountedFee, 0)
|
|
SET @tAmt = Cast(Round(@tAmt, 2, 0) AS DECIMAL(18, 2));
|
|
SET @cAmt = Cast(Round(@cAmt, 2, 0) AS DECIMAL(18, 2))
|
|
--SET @cAmt = CEILING(@cAmt)
|
|
END
|
|
|
|
IF (
|
|
@discountedFeeMoney > '0.00'
|
|
AND @discountedFeeMoney > @maxRewardAmt
|
|
)
|
|
BEGIN
|
|
SELECT '8' ErrorCode
|
|
,'The maximum reward amount per transaction is ' + CAST(@maxRewardAmt AS VARCHAR(20)) + ' Pounds' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
|
|
IF (
|
|
@discountedFeeMoney > '0.00'
|
|
AND @cAmt < @minCollAmt
|
|
)
|
|
BEGIN
|
|
SELECT '8' ErrorCode
|
|
,'The minimum amount that can be sent for each transaction is ' + CAST(@minCollAmt AS VARCHAR(20)) + ' Pounds' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
|
|
--4. Validate Country Sending Limit
|
|
EXEC PROC_CHECKCOUNTRYLIMIT @flag = 's-limit'
|
|
,@cAmt = @cAmt
|
|
,@pAmt = @pAmt
|
|
,@sCountryId = @sCountryId
|
|
,@collMode = @collMode
|
|
,@deliveryMethod = @deliveryMethodId
|
|
,@sendingCustType = @sendingCustType
|
|
,@pCountryId = @pCountryId
|
|
,@pCurr = @pCurr
|
|
,@collCurr = @collCurr
|
|
,@pAgent = @pAgent
|
|
,@sAgent = @sAgent
|
|
,@sBranch = @sBranch
|
|
,@msg = @msg OUT
|
|
,@errorCode = @errorCode OUT
|
|
|
|
IF @errorCode <> '0'
|
|
BEGIN
|
|
SELECT @errorCode ErrorCode
|
|
,@msg Msg
|
|
|
|
RETURN;
|
|
END
|
|
|
|
--Validate Country Sending Limit END
|
|
--5. Validate Country Receiving Limit
|
|
EXEC PROC_CHECKCOUNTRYLIMIT @flag = 'r-limit'
|
|
,@cAmt = @cAmt
|
|
,@pAmt = @pAmt
|
|
,@sCountryId = @sCountryId
|
|
,@collMode = @collMode
|
|
,@deliveryMethod = @deliveryMethodId
|
|
,@sendingCustType = @sendingCustType
|
|
,@pCountryId = @pCountryId
|
|
,@pCurr = @pCurr
|
|
,@collCurr = @collCurr
|
|
,@pAgent = @pAgent
|
|
,@sAgent = @sAgent
|
|
,@sBranch = @sBranch
|
|
,@msg = @msg OUT
|
|
,@errorCode = @errorCode OUT
|
|
|
|
IF @errorCode <> '0'
|
|
BEGIN
|
|
SELECT @errorCode ErrorCode
|
|
,@msg Msg
|
|
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @maxAmt MONEY
|
|
|
|
SELECT @maxAmt = maxAmt
|
|
FROM countryWiseExchangeRate
|
|
WHERE depositType = 'DEBIT_CARD'
|
|
AND countryId = @pCountryId
|
|
|
|
IF (
|
|
@schemeId = 'DEBIT_CARD'
|
|
AND @cAmt > @maxAmt
|
|
)
|
|
BEGIN
|
|
SELECT '3' ErrorCode
|
|
,'You cannot send more than ' + CAST(@maxAmt AS VARCHAR) + ' GBP via Debit Card. Please select Online Banking to complete the transaction or we will automatically redirect to Bank Transfer.' Msg
|
|
,NULL id
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF ISNULL(@ProcessFor, '') = 'send'
|
|
BEGIN
|
|
SET @FOREX_SESSION_ID = NEWID()
|
|
|
|
----## lock ex rate for individual txn
|
|
UPDATE exRateCalcHistory
|
|
SET isExpired = 1
|
|
WHERE CUSTOMER_ID = @senderId
|
|
AND isExpired = 0
|
|
|
|
PRINT @serviceCharge;
|
|
|
|
INSERT INTO exRateCalcHistory (
|
|
CUSTOMER_ID
|
|
,[USER_ID]
|
|
,FOREX_SESSION_ID
|
|
,serviceCharge
|
|
,pAmt
|
|
,customerRate
|
|
,sCurrCostRate
|
|
,sCurrHoMargin
|
|
,sCurrAgentMargin
|
|
,pCurrCostRate
|
|
,pCurrHoMargin
|
|
,pCurrAgentMargin
|
|
,agentCrossSettRate
|
|
,createdDate
|
|
,isExpired
|
|
,tAmt
|
|
,schemeId
|
|
,discountedFee
|
|
,sharingValue
|
|
,customerPremium
|
|
,promoRowId
|
|
)
|
|
SELECT @senderId
|
|
,@user
|
|
,@FOREX_SESSION_ID
|
|
,@serviceCharge
|
|
,@pAmt
|
|
,@exRate
|
|
,1
|
|
,0
|
|
,0
|
|
,@pCurrHoMargin + @exRate
|
|
,@pCurrHoMargin
|
|
,0
|
|
,@exRate
|
|
,GETDATE()
|
|
,0
|
|
,@tAmt
|
|
,@schemeId
|
|
,ISNULL(@discountedFee, 0)
|
|
,@schemeCount
|
|
,@tpExRate
|
|
,@row_id
|
|
END
|
|
|
|
SET @msg = 'Success'
|
|
|
|
PRINT @promRate
|
|
PRINT @isFirstTran
|
|
PRINT '@promRate1'
|
|
|
|
IF (
|
|
@promRate > 0
|
|
AND @isFirstTran = 'Y'
|
|
)
|
|
SET @schemeAppliedMsg = 'Enjoy premium rate due to First Transaction!';
|
|
|
|
DECLARE @promotionalRate FLOAT
|
|
|
|
SELECT @rate = ISNULL(margin, 0)
|
|
,@promRate = ISNULL(purchaseRate, 0)
|
|
FROM countryWiseExchangeRate
|
|
WHERE depositType = ISNULL(@schemeId, 'ONLINE')
|
|
AND countryId = @pCountryId
|
|
|
|
SET @promotionalRate = @exRate - (ISNULL(@rate, 0) + ISNULL(@promRate, 0))
|
|
|
|
SELECT @errorCode ErrorCode
|
|
,@msg Msg
|
|
,scCharge = @serviceCharge
|
|
,exRate = CASE
|
|
WHEN @user = NULL
|
|
OR @user IS NULL
|
|
THEN @promotionalRate
|
|
ELSE @exRate
|
|
END
|
|
,place = @place
|
|
,pCurr = @pCurr
|
|
,currDecimal = @currDecimal
|
|
,pAmt = @pAmt
|
|
,sAmt = @tAmt
|
|
,place = @place
|
|
,disc = 0.00
|
|
,collAmt = @cAmt
|
|
,exRateOffer = @exRateOffer
|
|
,scOffer = @scDiscount
|
|
,scAction = @scAction
|
|
,scValue = @scValue
|
|
,scDiscount = @scDiscount
|
|
,tpExRate = 0
|
|
,amountLimitPerDay = 0
|
|
,amountLimitPerTran = 0
|
|
,customerTotalSentAmt = 0
|
|
,exRateDisplay = @exRate
|
|
,EXRATEID = @FOREX_SESSION_ID
|
|
,maxAmountLimitPerTran = 0
|
|
,minAmountLimitPerTran = 0
|
|
,PerTxnMinimumAmt = 0
|
|
,schemeAppliedMsg = @schemeAppliedMsg
|
|
,schemeId = @schemeId
|
|
,tpPCurr = @pCurr
|
|
,collCurr = @collCurr
|
|
,ForexSessionId = @FOREX_SESSION_ID
|
|
--,discountedFee = CASE WHEN @isPromoCode = 'N' THEN ISNULL(@discountedFee, 0)
|
|
-- WHEN @isPromoCode = 'Y' THEN @promoCode
|
|
-- ELSE ISNULL(@discountedFee, 0)
|
|
-- END
|
|
,discountedFee = ISNULL(@discountedFee, 0)
|
|
,isPromoCode = @isPromoCode
|
|
,promoRowId = @row_id
|
|
,promoCode = @promoCode
|
|
,@promotionalRate promotionalRate
|
|
END
|
|
ELSE IF @FLAG = 'true'
|
|
BEGIN
|
|
IF ISNULL(@tpExRate, 0) = 0
|
|
BEGIN
|
|
SELECT '5' ErrorCode
|
|
,'Third Party Exchange rate fetching error for currency (' + @pCurr + ')' Msg
|
|
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @exRateDonga FLOAT
|
|
,@exRateBRAC FLOAT
|
|
|
|
PRINT @sCountryId;
|
|
PRINT @sCountryId;
|
|
PRINT @sAgent;
|
|
PRINT @sBranch;
|
|
PRINT @collCurr;
|
|
PRINT @pCountryId;
|
|
PRINT @pAgent;
|
|
PRINT @pCurr;
|
|
PRINT @deliveryMethodId;
|
|
|
|
SELECT @pCurrHoMargin = pCurrHoMargin
|
|
FROM dbo.FNAGetExRate(@sCountryId, @sAgent, @sBranch, @collCurr, @pCountryId, @pAgent, @pCurr, @deliveryMethodId)
|
|
|
|
SELECT @exRate = @tpExRate - ISNULL(@pCurrHoMargin, 0)
|
|
|
|
IF ISNULL(@exRate, 0) = 0
|
|
BEGIN
|
|
SELECT '3' ErrorCode
|
|
,'Exchange rate not defined yet for receiving currency (' + @pCurr + ')' Msg
|
|
,NULL id
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @user = 'onlinedefault'
|
|
AND @pCountryId = 203
|
|
BEGIN
|
|
SELECT @exRateDonga = dbo.FNAGetCustomerRate(113, 0, 394395, 'JPY', 203, 394133, 'VND', 1)
|
|
|
|
IF @exRateDonga > @exRate
|
|
SET @exRate = @exRateDonga
|
|
END
|
|
|
|
IF @user = 'onlinedefault'
|
|
AND @pCountryId = 16
|
|
BEGIN
|
|
SELECT @exRateBRAC = dbo.FNAGetCustomerRate(113, 0, 394395, 'JPY', 16, 394414, 'BDT', 1)
|
|
|
|
IF @exRateBRAC > @exRate
|
|
SET @exRate = @exRateBRAC
|
|
END
|
|
|
|
IF @calBy = 'C'
|
|
BEGIN
|
|
IF (
|
|
@tranCount = @schemeCount
|
|
AND ISNULL(@user, 'onlinedefault') <> 'onlinedefault'
|
|
-- AND ISNULL(@ProcessFor, '') = 'send'
|
|
AND @isEligible = 'Y'
|
|
)
|
|
OR (@isFirstTran = 'Y')
|
|
BEGIN
|
|
SET @serviceCharge = 0
|
|
|
|
IF @isFirstTran = 'Y'
|
|
BEGIN
|
|
SET @schemeAppliedMsg = 'Enjoy zero (0) service charge due to First Transaction!';
|
|
END
|
|
ELSE
|
|
SET @schemeAppliedMsg = 'Enjoy zero (0) service charge due to Loyalty Scheme!';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
IF ISNULL(@isManualSc, 'N') = 'N'
|
|
BEGIN
|
|
SELECT @serviceChargetmp = ISNULL(amount, 0)
|
|
FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @cAmt, @collCurr)
|
|
--IF (@discountedFee > '0.00')
|
|
-- AND (@serviceChargetmp < @discountedFee)
|
|
--BEGIN
|
|
-- SELECT '8' ErrorCode
|
|
-- ,'Redeem points can not be more than service charge' Msg
|
|
-- ,NULL id
|
|
-- RETURN;
|
|
--END
|
|
--ELSE
|
|
--BEGIN
|
|
-- SELECT @serviceCharge = ISNULL(@serviceChargetmp, 0) - ISNULL(@discountedFee, 0)
|
|
--END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @serviceCharge = ISNULL(@manualSc, 0)
|
|
END
|
|
|
|
IF @serviceCharge IS NULL
|
|
AND ISNULL(@isManualSc, 'N') = 'N'
|
|
BEGIN
|
|
SELECT '4' ErrorCode
|
|
,'Service charge not defined yet for receiving country' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @scAction = 'PD' -- Percent Discount
|
|
BEGIN
|
|
SET @scOffer = (@scValue / 100) * @serviceCharge
|
|
SET @scDiscount = (@scValue / 100) * @serviceCharge
|
|
END
|
|
ELSE IF @scAction = 'FD' -- Flat Discount
|
|
BEGIN
|
|
SET @scDiscount = @scValue
|
|
END
|
|
ELSE IF @scAction = 'FV' -- Fixed Value
|
|
BEGIN
|
|
SET @scOffer = @scValue
|
|
SET @scDiscount = @serviceCharge - @scValue
|
|
END
|
|
|
|
SET @tAmt = @cAmt - @serviceCharge + @scDiscount + ISNULL(@discountedFee, 0)
|
|
SET @pAmt = @tAmt * (@exRate + @exRateOffer)
|
|
--SET @pAmt = FLOOR(@pAmt)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--SET @tAmt = CEILING(@pAmt / (@exRate + @exRateOffer))
|
|
SET @tAmt = @pAmt / (@exRate + @exRateOffer)
|
|
|
|
IF ISNULL(@isManualSc, 'N') = 'N'
|
|
BEGIN
|
|
SELECT @serviceChargetmp = isnull(amount, 0)
|
|
FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @tAmt, @collCurr)
|
|
--IF (@discountedFee > '0.00')
|
|
-- AND (@serviceCharge < @discountedFee)
|
|
--BEGIN
|
|
-- SELECT '8' ErrorCode
|
|
-- ,'Redeem points can not be more than service charge' Msg
|
|
-- ,NULL id
|
|
-- RETURN;
|
|
--END
|
|
--ELSE
|
|
--BEGIN
|
|
-- SELECT @serviceCharge = ISNULL(@serviceChargetmp, 0) - ISNULL(@discountedFee, 0)
|
|
--END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @serviceCharge = ISNULL(@manualSc, 0)
|
|
END
|
|
|
|
IF @serviceCharge IS NULL
|
|
BEGIN
|
|
SELECT '4' ErrorCode
|
|
,'Service charge not defined yet for receiving country' Msg
|
|
,NULL id
|
|
|
|
RETURN;
|
|
END
|
|
|
|
IF @scAction = 'PD'
|
|
BEGIN
|
|
SET @scOffer = (@scValue / 100) * @serviceCharge
|
|
SET @scDiscount = (@scValue / 100) * @serviceCharge
|
|
END
|
|
ELSE IF @scAction = 'FD'
|
|
BEGIN
|
|
SET @scDiscount = @scValue
|
|
END
|
|
ELSE IF @scAction = 'FV'
|
|
BEGIN
|
|
SET @scOffer = @scValue
|
|
SET @scDiscount = @serviceCharge - @scValue
|
|
END
|
|
|
|
SET @cAmt = (@tAmt + @serviceCharge - @scDiscount) + ISNULL(@discountedFee, 0)
|
|
--SET @cAmt = ROUND(@cAmt, @currDecimal)
|
|
--New logic for calculating new tAmt and pAmt after adding discount fee
|
|
SET @tAmt = @tAmt + ISNULL(@discountedFee, 0)
|
|
SET @pAmt = @tAmt * (@exRate + @exRateOffer)
|
|
--SET @pAmt = FLOOR(@pAmt)
|
|
END
|
|
|
|
--4. Validate Country Sending Limit
|
|
EXEC PROC_CHECKCOUNTRYLIMIT @flag = 's-limit'
|
|
,@cAmt = @cAmt
|
|
,@pAmt = @pAmt
|
|
,@sCountryId = @sCountryId
|
|
,@collMode = @collMode
|
|
,@deliveryMethod = @deliveryMethodId
|
|
,@sendingCustType = @sendingCustType
|
|
,@pCountryId = @pCountryId
|
|
,@pCurr = @pCurr
|
|
,@collCurr = @collCurr
|
|
,@pAgent = @pAgent
|
|
,@sAgent = @sAgent
|
|
,@sBranch = @sBranch
|
|
,@msg = @msg OUT
|
|
,@errorCode = @errorCode OUT
|
|
|
|
IF @errorCode <> '0'
|
|
BEGIN
|
|
SELECT @errorCode ErrorCode
|
|
,@msg Msg
|
|
|
|
RETURN;
|
|
END
|
|
|
|
--Validate Country Sending Limit END
|
|
--5. Validate Country Receiving Limit
|
|
EXEC PROC_CHECKCOUNTRYLIMIT @flag = 'r-limit'
|
|
,@cAmt = @cAmt
|
|
,@pAmt = @pAmt
|
|
,@sCountryId = @sCountryId
|
|
,@collMode = @collMode
|
|
,@deliveryMethod = @deliveryMethodId
|
|
,@sendingCustType = @sendingCustType
|
|
,@pCountryId = @pCountryId
|
|
,@pCurr = @pCurr
|
|
,@collCurr = @collCurr
|
|
,@pAgent = @pAgent
|
|
,@sAgent = @sAgent
|
|
,@sBranch = @sBranch
|
|
,@msg = @msg OUT
|
|
,@errorCode = @errorCode OUT
|
|
|
|
IF @errorCode <> '0'
|
|
BEGIN
|
|
SELECT @errorCode ErrorCode
|
|
,@msg Msg
|
|
|
|
RETURN;
|
|
END
|
|
|
|
--Validate Country Receiving Limit
|
|
IF ISNULL(@ProcessFor, '') = 'send'
|
|
BEGIN
|
|
SET @FOREX_SESSION_ID = NEWID()
|
|
|
|
----## lock ex rate for individual txn
|
|
UPDATE exRateCalcHistory
|
|
SET isExpired = 1
|
|
WHERE CUSTOMER_ID = @senderId
|
|
AND isExpired = 0
|
|
|
|
INSERT INTO exRateCalcHistory (
|
|
CUSTOMER_ID
|
|
,[USER_ID]
|
|
,FOREX_SESSION_ID
|
|
,serviceCharge
|
|
,pAmt
|
|
,customerRate
|
|
,sCurrCostRate
|
|
,sCurrHoMargin
|
|
,sCurrAgentMargin
|
|
,pCurrCostRate
|
|
,pCurrHoMargin
|
|
,pCurrAgentMargin
|
|
,agentCrossSettRate
|
|
,createdDate
|
|
,isExpired
|
|
,tAmt
|
|
,schemeId
|
|
,discountedFee
|
|
,sharingValue
|
|
)
|
|
SELECT @senderId
|
|
,@user
|
|
,@FOREX_SESSION_ID
|
|
,@serviceCharge
|
|
,@pAmt
|
|
,@exRate
|
|
,1
|
|
,0
|
|
,0
|
|
,@pCurrHoMargin + @exRate
|
|
,@pCurrHoMargin
|
|
,0
|
|
,@exRate
|
|
,GETDATE()
|
|
,0
|
|
,@tAmt
|
|
,@schemeId
|
|
,ISNULL(@discountedFee, 0)
|
|
,@schemeCount
|
|
END
|
|
|
|
SET @msg = 'Success'
|
|
|
|
PRINT @promRate
|
|
PRINT @isFirstTran
|
|
PRINT '@promRate1'
|
|
|
|
IF (
|
|
@promRate > 0
|
|
AND @isFirstTran = 'Y'
|
|
)
|
|
SET @schemeAppliedMsg = 'Enjoy premium rate due to First Transaction!';
|
|
|
|
SELECT @errorCode ErrorCode
|
|
,@msg Msg
|
|
,scCharge = @serviceCharge
|
|
,exRate = @exRate
|
|
,place = @place
|
|
,pCurr = @pCurr
|
|
,currDecimal = @currDecimal
|
|
,pAmt = @pAmt
|
|
,sAmt = @tAmt
|
|
,place = @place
|
|
,disc = 0.00
|
|
,collAmt = @cAmt
|
|
,exRateOffer = @exRateOffer
|
|
,scOffer = @scDiscount
|
|
,scAction = @scAction
|
|
,scValue = @scValue
|
|
,scDiscount = @scDiscount
|
|
,tpExRate = @tpExRate
|
|
,amountLimitPerDay = 0
|
|
,amountLimitPerTran = 0
|
|
,customerTotalSentAmt = 0
|
|
,exRateDisplay = @exRate
|
|
,EXRATEID = @FOREX_SESSION_ID
|
|
,maxAmountLimitPerTran = 0
|
|
,minAmountLimitPerTran = 0
|
|
,PerTxnMinimumAmt = 0
|
|
,schemeAppliedMsg = @schemeAppliedMsg
|
|
,schemeId = @schemeId
|
|
,tpPCurr = @pCurr
|
|
,collCurr = @collCurr
|
|
,ForexSessionId = @FOREX_SESSION_ID
|
|
,discountedFee = ISNULL(@discountedFee, 0)
|
|
END
|
|
END
|