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.
86 lines
3.0 KiB
86 lines
3.0 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[proc_MoneyInformation] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROCEDURE [dbo].[proc_MoneyInformation]
|
|
(
|
|
@flag VARCHAR(20) = 'boardinfo'
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
IF @flag = 'boardinfo'
|
|
BEGIN
|
|
|
|
DECLARE @pcurrCostRate FLOAT
|
|
DECLARE @DISPLAYBOARD TABLE(countryName VARCHAR(50),pCountryId int,isoAlpha3 VARCHAR(3),crossRate FLOAT,pCurrency VARCHAR(3),payoutPartner BIGINT,isActive CHAR(1),callAPI char(1),PAYMENTMETHOD int)
|
|
|
|
INSERT INTO @DISPLAYBOARD
|
|
SELECT DISTINCT CM.countryName,CM.countryId,CM.isoAlpha3,ET.crossRate, ET.pCurrency,ET.pAgent,ET.isActive,'N',ET.tranType
|
|
FROM exRateTreasury ET(NOLOCK)
|
|
INNER JOIN (
|
|
SELECT DISTINCT CountryId FROM TblPartnerwiseCountry(nolock) WHERE GetRateFrom = 'LOCAL' AND IsActive = 1
|
|
)P ON P.CountryId = ET.pCountry
|
|
INNER JOIN countryMaster CM(nolock) ON ET.pCountry = CM.countryId
|
|
WHERE ET.isActive = 'Y' AND pAgent IS NULL
|
|
|
|
UPDATE @DISPLAYBOARD SET callAPI ='Y',PAYMENTMETHOD ='1' WHERE pCurrency ='MNT'
|
|
|
|
SELECT @pCurrCostRate = CurrCostRate FROM TBL_PARTNER_COST_RATE(NOLOCK) WHERE GETDATE() BETWEEN EffectiveFrom AND EffectiveTo
|
|
|
|
INSERT INTO @DISPLAYBOARD
|
|
SELECT DISTINCT CM.countryName,CM.countryId,CM.isoAlpha3,crossRate = @pCurrCostRate/(ET.cRate + ET.cMargin + ISNULL(ET.cHoMargin,0)), ET.pCurrency,ET.pAgent,ET.isActive,'N',ET.tranType
|
|
FROM exRateTreasury ET(NOLOCK)
|
|
INNER JOIN (
|
|
SELECT DISTINCT CountryId FROM TblPartnerwiseCountry(nolock) WHERE GetRateFrom = 'LOG' AND IsActive = 1
|
|
)P ON P.CountryId = ET.pCountry
|
|
INNER JOIN countryMaster CM(nolock) ON ET.pCountry = CM.countryId
|
|
WHERE ET.isActive = 'Y' AND pAgent IS NULL
|
|
|
|
INSERT INTO @DISPLAYBOARD
|
|
SELECT C.countryName,C.countryId,c.isoAlpha3,0 as crossRate,X.currencyCode,AGENTID,'Y' isActive,'Y' as callAPI,PAYMENTMETHOD
|
|
FROM TBLPARTNERWISECOUNTRY (NOLOCK) p
|
|
INNER JOIN countryMaster(NOLOCK) C ON C.countryId = P.CountryId
|
|
INNER JOIN(
|
|
SELECT countryId,currencyCode FROM countryCurrency(NOLOCK) CC
|
|
INNER JOIN currencyMaster(NOLOCK) CM ON CM.currencyId = CC.currencyId
|
|
WHERE CC.spFlag = 'R'
|
|
)X ON P.CountryId = X.countryId
|
|
WHERE GetRateFrom = 'LIVE' AND P.IsActive = 1
|
|
|
|
--SELECT COUNT(1),countryName,pCurrency FROM @DISPLAYBOARD
|
|
--GROUP BY countryName,pCurrency
|
|
--HAVING COUNT(1)>1
|
|
|
|
DELETE FROM @DISPLAYBOARD WHERE countryName IN(
|
|
SELECT countryName FROM @DISPLAYBOARD
|
|
GROUP BY countryName,pCurrency
|
|
HAVING COUNT(1)>1
|
|
) AND ISNULL(PAYMENTMETHOD,'') <> 2
|
|
|
|
DELETE FROM @DISPLAYBOARD WHERE countryName='Sri Lanka' AND pCurrency='USD'
|
|
|
|
SELECT * FROM @DISPLAYBOARD
|
|
|
|
RETURN
|
|
|
|
END
|
|
|
|
ELSE IF @flag = 'rateinfo'
|
|
BEGIN
|
|
SELECT DISTINCT ET.crossRate, ET.pCurrency
|
|
FROM exRateTreasury ET(NOLOCK)
|
|
END
|
|
ELSE IF @flag = 'ServiceInfo'
|
|
BEGIN
|
|
SELECT P.rCountry AS pCountryId,P.tranType AS PAYMENTMETHOD,C.minAmt AS CHARGE
|
|
FROM sscMaster P(NOLOCK)
|
|
INNER JOIN sscDetail C(NOLOCK) ON C.sscMasterId = P.sscMasterId
|
|
WHERE P.isActive = 'Y' AND C.isActive='Y'
|
|
END
|
|
GO
|