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.
299 lines
26 KiB
299 lines
26 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_UpdateBDTRate_FromXE] Script Date: 9/27/2019 1:30:14 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
--exec proc_UpdateBDTRate_FromXE @FromCurr='usd',@toCurr='bdt',@LiveRate='84.8',@flag = 'P'
|
|
|
|
|
|
CREATE proc [dbo].[proc_UpdateBDTRate_FromXE]
|
|
@FromCurr VARCHAR(5) = NULL,
|
|
@toCurr VARCHAR(5) = NULL,
|
|
@LiveRate FLOAT = NULL,
|
|
@requestXml VARCHAR(MAX) = NULL,
|
|
@responseXml VARCHAR(MAX) = NULL,
|
|
@flag varchar(10)
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON
|
|
--select @FromCurr,@toCurr,@cRate
|
|
--return
|
|
|
|
BEGIN TRY
|
|
declare @defExRateId int ,@user varchar(50) = 'system',@msg varchar(max) = ''
|
|
|
|
EXEC proc_errorHandler 0, 'Record updated successfully.', 0
|
|
RETURN
|
|
|
|
--IF (CAST(GETDATE() AS DATE) <'2019-08-19')
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 0, 'Record updated successfully.', 0
|
|
-- RETURN
|
|
--END
|
|
INSERT INTO tlbExrateApilogs(requestXml,responseXml,createdBy)
|
|
SELECT @requestXml,@responseXml,@user
|
|
|
|
IF @FromCurr = 'error'
|
|
BEGIN
|
|
EXEC proc_errorHandler 1,@responseXml, NULL
|
|
----## SEND SMS THROUGH KT NETWORK
|
|
INSERT INTO KT_SMS.dbo.SDK_SMS_SEND ( USER_ID, SCHEDULE_TYPE, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_INFO)
|
|
select 'globalmoney',0,'XE',left(@responseXml,90),format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01030436864'
|
|
union all
|
|
select 'globalmoney',0,'XE',left(@responseXml,90),format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01074518940'
|
|
|
|
RETURN
|
|
END
|
|
|
|
|
|
IF ISNULL(@LiveRate, 0) = 0
|
|
BEGIN
|
|
|
|
SET @MSG = 'Sorry 0 ex rate for ' + @toCurr + ' can not be updated';
|
|
----## SEND SMS THROUGH KT NETWORK
|
|
INSERT INTO KT_SMS.dbo.SDK_SMS_SEND ( USER_ID, SCHEDULE_TYPE, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_INFO)
|
|
select 'globalmoney',0,'XE','Sorry 0 ex rate can not be updated',format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','1^01030436864'
|
|
union all
|
|
select 'globalmoney',0,'XE',@MSG,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','1^01074518940'
|
|
|
|
EXEC proc_errorHandler 1, 'Sorry 0 ex rate can not be updated.', NULL
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @pRate FLOAT
|
|
DECLARE @tolCMax FLOAT, @tolCMin FLOAT, @tolPMax FLOAT, @tolPMin FLOAT, @errorMsg VARCHAR(200), @id INT
|
|
|
|
SELECT defExRateId = defExRateId,currency = currency,cRate = cRate, cMargin = ISNULL(cMargin,0),pRate = ISNULL(pRate,0) ,pMargin = ISNULL(pMargin,0)
|
|
INTO #tempDefRate
|
|
FROM defExRate WITH(NOLOCK)
|
|
WHERE currency = @toCurr and baseCurrency = @FromCurr
|
|
|
|
IF NOT EXISTS(SELECT 'A' FROM #tempDefRate)
|
|
BEGIN
|
|
set @msg = 'No cost rate setup found for currency '+@FromCurr+' and '+@toCurr
|
|
EXEC proc_errorHandler 1, @msg, NULL
|
|
----## SEND SMS THROUGH KT NETWORK
|
|
INSERT INTO KT_SMS.dbo.SDK_SMS_SEND ( USER_ID, SCHEDULE_TYPE, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_INFO)
|
|
SELECT 'globalmoney',0,'XE',@msg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01030436864'
|
|
union all
|
|
SELECT 'globalmoney',0,'XE',@msg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01074518940'
|
|
|
|
RETURN
|
|
END
|
|
|
|
SELECT
|
|
@tolCMax = ISNULL(cMax, 0.0)
|
|
,@tolCMin = ISNULL(cMin, 0.0)
|
|
,@tolPMax = ISNULL(pMax, 0.0)
|
|
,@tolPMin = ISNULL(pMin, 0.0)
|
|
FROM rateMask WITH(NOLOCK) WHERE currency = @toCurr AND baseCurrency = @FromCurr
|
|
|
|
IF NOT EXISTS(SELECT 'X' FROM rateMask WITH(NOLOCK) WHERE currency = @toCurr)
|
|
BEGIN
|
|
SET @msg = 'Please define rate mask for currency ' + @toCurr
|
|
EXEC proc_errorHandler 1, @msg, NULL
|
|
|
|
----## SEND SMS THROUGH KT NETWORK
|
|
INSERT INTO KT_SMS.dbo.SDK_SMS_SEND ( USER_ID, SCHEDULE_TYPE, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_INFO)
|
|
select 'globalmoney',0,'XE',@msg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01030436864'
|
|
union all
|
|
select 'globalmoney',0,'XE',@msg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01074518940'
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'P'
|
|
BEGIN
|
|
--Collection
|
|
SET @pRate = ROUND(@LiveRate,2) -0.05
|
|
|
|
IF EXISTS (SELECT 'A' FROM #tempDefRate WHERE @pRate - ISNULL(pMargin, 0) > @tolPMax)
|
|
BEGIN
|
|
SET @errorMsg = 'BDT Max tolerance exceeds.Rate must lie between ' + CAST(@tolPMin AS VARCHAR) + ' AND ' + CAST(@tolPMax AS VARCHAR)
|
|
EXEC proc_errorHandler 1, @errorMsg, NULL
|
|
|
|
----## SEND SMS THROUGH KT NETWORK
|
|
INSERT INTO KT_SMS.dbo.SDK_SMS_SEND ( USER_ID, SCHEDULE_TYPE, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_INFO)
|
|
select 'globalmoney',0,'XE',@errorMsg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01030436864'
|
|
union all
|
|
select 'globalmoney',0,'XE',@errorMsg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01074518940'
|
|
|
|
RETURN
|
|
END
|
|
IF EXISTS (SELECT 'A' FROM #tempDefRate WHERE @pRate - ISNULL(pMargin, 0) < @tolPMin)
|
|
BEGIN
|
|
SET @errorMsg = 'BDT Min tolerance exceeds.Rate must lie between ' + CAST(@tolPMin AS VARCHAR) + ' AND ' + CAST(@tolPMax AS VARCHAR)
|
|
EXEC proc_errorHandler 1, @errorMsg, NULL
|
|
|
|
----## SEND SMS THROUGH KT NETWORK
|
|
INSERT INTO KT_SMS.dbo.SDK_SMS_SEND ( USER_ID, SCHEDULE_TYPE, SUBJECT, SMS_MSG, NOW_DATE, SEND_DATE, CALLBACK, DEST_INFO)
|
|
SELECT 'globalmoney',0,'XE',@errorMsg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01030436864'
|
|
UNION All
|
|
SELECT 'globalmoney',0,'XE',@errorMsg,format(getdate(),'yyyyMMddHHmmss'),format(getdate(),'yyyyMMddHHmmss'),'1588-6864','gme^01074518940'
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
|
|
CREATE TABLE #exRateIdTempMain(exRateTreasuryId INT)
|
|
CREATE TABLE #exRateIdTempMod(exRateTreasuryId INT)
|
|
|
|
BEGIN TRANSACTION
|
|
--Currency Rate/ Agent Rate Update------------------------------------------------------------------------------------
|
|
UPDATE D SET
|
|
pRate = @pRate
|
|
,modifiedBy = @user
|
|
,modifiedDate = GETDATE()
|
|
FROM defExRate D
|
|
INNER JOIN #tempDefRate T ON T.defExRateId = D.defExRateId
|
|
|
|
--Change Record History-----------------------------------------------------------------------------------------------
|
|
INSERT INTO defExRateHistory(
|
|
defExRateId
|
|
,setupType
|
|
,currency,country,agent,baseCurrency,tranType,factor
|
|
,cRate,cMargin,cMax,cMin
|
|
,pRate,pMargin,pMax,pMin
|
|
,isEnable,createdBy,createdDate,approvedBy,approvedDate,modType
|
|
)
|
|
SELECT
|
|
main.defExRateId
|
|
,main.setupType
|
|
,main.currency,main.country,main.agent,main.baseCurrency,main.tranType,factor
|
|
,T.cRate,T.cMargin,cMax,cMin
|
|
,main.pRate,T.pMargin,pMax,pMin
|
|
,isEnable,@user,GETDATE(),@user,GETDATE(),'U'
|
|
FROM defExRate main WITH(NOLOCK)
|
|
INNER JOIN #tempDefRate T ON T.defExRateId = main.defExRateId
|
|
--FROM defExRate main WITH(NOLOCK) WHERE defExRateId = @defExRateId
|
|
|
|
IF @flag = 'P'
|
|
BEGIN
|
|
--1. Get All Corridor records affected by receive cost rate change
|
|
DELETE FROM #exRateIdTempMain
|
|
INSERT INTO #exRateIdTempMain
|
|
SELECT exRateTreasuryId FROM exRateTreasury e WITH(NOLOCK)
|
|
INNER JOIN #tempDefRate R ON R.defExRateId = E.pRateId
|
|
|
|
--2. Update Records in Mod Table if data already exist in mod table
|
|
IF EXISTS(SELECT 'X' FROM exRateTreasuryMod mode WITH(NOLOCK) INNER JOIN #exRateIdTempMain temp ON mode.exRateTreasuryId = temp.exRateTreasuryId)
|
|
BEGIN
|
|
|
|
DELETE FROM #exRateIdTempMod
|
|
INSERT INTO #exRateIdTempMod
|
|
SELECT mode.exRateTreasuryId FROM exRateTreasuryMod mode WITH(NOLOCK)
|
|
INNER JOIN #exRateIdTempMain temp ON mode.exRateTreasuryId = temp.exRateTreasuryId
|
|
|
|
UPDATE ert SET
|
|
pRate = @pRate
|
|
--,pMargin = @pMargin
|
|
,maxCrossRate = ROUND(@pRate/def.cRate, dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
,crossRate = ROUND((@pRate - ert.pMargin - pHoMargin)/(def.cRate + def.cMargin + cHoMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
,customerRate = CASE WHEN ISNULL(toleranceOn, '') IN ('S', 'P', '') THEN ROUND((@pRate - ert.pMargin - pHoMargin - pAgentMargin)/(def.cRate + def.cMargin + cHoMargin + cAgentMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
WHEN ISNULL(toleranceOn, '') IN ('C') THEN ROUND((@pRate - ert.pMargin - pHoMargin)/(def.cRate + def.cMargin + cHoMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency)) - ISNULL(agentCrossRateMargin, 0) END
|
|
,createdBy = @user
|
|
,createdDate = GETDATE()
|
|
FROM exRateTreasuryMod ert
|
|
INNER JOIN #tempDefRate def ON ert.cRateId = def.defExRateId
|
|
INNER JOIN #exRateIdTempMod temp ON ert.exRateTreasuryId = temp.exRateTreasuryId
|
|
END
|
|
|
|
--3. Update Record in main table for modType Insert.
|
|
UPDATE ert SET
|
|
pRate = @pRate
|
|
--,pMargin = @pMargin
|
|
,maxCrossRate = ROUND(@pRate/def.cRate, dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
,crossRate = ROUND((@pRate - tef.pMargin - pHoMargin)/(def.cRate + def.cMargin + cHoMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
--,customerRate = ROUND((@pRate - @pMargin - pHoMargin - pAgentMargin)/(def.cRate + def.cMargin + cHoMargin + cAgentMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
,customerRate = CASE WHEN ISNULL(toleranceOn, '') IN ('S', 'P', '') THEN ROUND((@pRate - tef.pMargin - pHoMargin - pAgentMargin)/(def.cRate + def.cMargin + cHoMargin + cAgentMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
WHEN ISNULL(toleranceOn, '') IN ('C') THEN ROUND((@pRate - tef.pMargin - pHoMargin)/(def.cRate + def.cMargin + cHoMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency)) - ISNULL(agentCrossRateMargin, 0) END
|
|
,createdBy = @user
|
|
,createdDate = GETDATE()
|
|
FROM exRateTreasury ert
|
|
INNER JOIN defExRate def ON ert.cRateId = def.defExRateId
|
|
INNER JOIN #tempDefRate tef ON ert.pRateId = tef.defExRateId
|
|
WHERE ert.approvedBy IS NULL
|
|
|
|
|
|
INSERT INTO exRateTreasuryMod(
|
|
exRateTreasuryId
|
|
,cRateId,cCurrency,cCountry,cAgent,cRateFactor,cRate,cMargin,cHoMargin,cAgentMargin
|
|
,pRateId,pCurrency,pCountry,pAgent,pRateFactor,pRate,pMargin,pHoMargin,pAgentMargin
|
|
,sharingType
|
|
,sharingValue
|
|
,toleranceOn
|
|
,agentTolMin
|
|
,agentTolMax
|
|
,customerTolMin
|
|
,customerTolMax
|
|
,crossRate
|
|
,customerRate
|
|
,maxCrossRate
|
|
,agentCrossRateMargin
|
|
,tolerance
|
|
,crossRateFactor
|
|
,isActive
|
|
,modType,createdBy,createdDate
|
|
)
|
|
SELECT
|
|
exRateTreasuryId
|
|
,ert.cRateId,ert.cCurrency,cCountry,cAgent,cRateFactor,def.cRate,def.cMargin,cHoMargin,cAgentMargin
|
|
,ert.pRateId,ert.pCurrency,pCountry,pAgent,pRateFactor,@pRate,tef.pMargin,pHoMargin,pAgentMargin
|
|
,ert.sharingType
|
|
,ert.sharingValue
|
|
,ert.toleranceOn
|
|
,ert.agentTolMin
|
|
,ert.agentTolMax
|
|
,ert.customerTolMin
|
|
,ert.customerTolMax
|
|
,ROUND((@pRate - tef.pMargin - pHoMargin)/(def.cRate + def.cMargin + cHoMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
,CASE WHEN ISNULL(toleranceOn, '') IN ('S', 'P', '') THEN ROUND((@pRate - tef.pMargin - pHoMargin - pAgentMargin)/(def.cRate + def.cMargin + cHoMargin + cAgentMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
WHEN ISNULL(toleranceOn, '') IN ('C') THEN ROUND((@pRate - tef.pMargin - pHoMargin)/(def.cRate + def.cMargin + cHoMargin), dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency)) - ISNULL(ert.agentCrossRateMargin, 0) END
|
|
,ROUND(@pRate/def.cRate, dbo.FNAGetCrossRateDecimalMask(ert.cCurrency, ert.pCurrency))
|
|
,ert.agentCrossRateMargin
|
|
,tolerance
|
|
,crossRateFactor
|
|
,ert.isActive
|
|
,'U',@user,GETDATE()
|
|
FROM exRateTreasury ert WITH(NOLOCK)
|
|
INNER JOIN defExRate def WITH(NOLOCK) ON ert.cRateId = def.defExRateId
|
|
INNER JOIN #tempDefRate tef WITH(NOLOCK) ON ert.pRateId = tef.defExRateId
|
|
WHERE ert.approvedBy IS NOT NULL AND ISNULL(ert.isActive, 'N') = 'Y'
|
|
AND ert.exRateTreasuryId NOT IN (SELECT exRateTreasuryId FROM #exRateIdTempMod)
|
|
|
|
UPDATE ert SET
|
|
ert.isUpdated = 'Y'
|
|
FROM exRateTreasury ert
|
|
INNER JOIN #tempDefRate tef WITH(NOLOCK) ON ert.pRateId = tef.defExRateId
|
|
WHERE approvedBy IS NOT NULL AND ISNULL(isActive, 'N') = 'Y'
|
|
|
|
END
|
|
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION
|
|
EXEC proc_errorHandler 0, 'Record updated successfully.', @defExRateId
|
|
|
|
declare @exRateTreasuryIds varchar(max)=''
|
|
|
|
select @exRateTreasuryIds = @exRateTreasuryIds+cast(exRateTreasuryId as varchar)+',' from exRateTreasuryMod(nolock) where createdBy='system'
|
|
|
|
set @exRateTreasuryIds = left(@exRateTreasuryIds,len(@exRateTreasuryIds)-1)
|
|
|
|
EXEC proc_exRateTreasury @flag = 'approve' , @user ='system',@exRateTreasuryIds = @exRateTreasuryIds
|
|
|
|
end try
|
|
begin catch
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
set @errorMsg = error_Message()
|
|
EXEC proc_errorHandler 1,@errorMsg, @defExRateId
|
|
|
|
end catch
|
|
|
|
|
|
GO
|