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.
301 lines
10 KiB
301 lines
10 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[EXCHANGE_PROC_CURRBUYSELLRATE] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[EXCHANGE_PROC_CURRBUYSELLRATE]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[EXCHANGE_PROC_CURRBUYSELLRATE] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROCEDURE [dbo].[EXCHANGE_PROC_CURRBUYSELLRATE]
|
|
@FLAG VARCHAR(20),
|
|
@BRANCH_ID VARCHAR(30) = NULL,
|
|
@CURR VARCHAR(5) = NULL,
|
|
@USER VARCHAR(50) = NULL,
|
|
@date VARCHAR(10) = NULL,
|
|
@sortBy VARCHAR(50) = NULL,
|
|
@sortOrder VARCHAR(5) = NULL,
|
|
@pageSize INT = NULL,
|
|
@pageNumber INT = NULL
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
----SELECT @BRANCH_ID = BRANCH_ID FROM BRANCHES WITH(NOLOCK) WHERE COMPANY_ID = @BRANCH_ID
|
|
DECLARE @CurrencyName VARCHAR(100)= 'KRW'
|
|
|
|
DECLARE @remarks VARCHAR(200),@BAL DECIMAL(10,2),@lblCurr varchar(5),@unit DECIMAL(10,2)
|
|
|
|
IF @FLAG = 'S'
|
|
BEGIN
|
|
SELECT e.SaleRate rate,e.Saletoleranceplus tolPlus,e.Saletoleranceminus tolMinus
|
|
,remarks = 'Max Rate: '+CAST((E.SaleRate) + (Saletoleranceplus) AS VARCHAR)
|
|
+ ' /Min Rate: '+CAST((E.SaleRate) - (Saletoleranceminus) AS VARCHAR)
|
|
,isnull(e.FixedCurUnit,1) unit,ISNULL(usd_amt,0)*-1 bal, ISNULL(usd_amt,0)*-1 WONBAL,e.baseCurrency curr
|
|
--,@afterDecimal afterDecimal
|
|
FROM FastMoneyPro_Account.dbo.ac_master a with (nolock)
|
|
INNER JOIN FastMoneyPro_Remit.dbo.EXCHANGE_CURRENCYRATE_SETUP e with (nolock) on e.baseCurrency = a.ac_currency
|
|
--INNER JOIN (SELECT cashId FROM Branches WHERE BRANCH_ID=@BRANCH_ID) X ON X.cashId=E.RateCode
|
|
WHERE ac_currency = @CURR AND e.Active='y' and
|
|
acct_rpt_code='MoneyEx' AND a.agent_id = @BRANCH_ID
|
|
|
|
|
|
RETURN
|
|
END
|
|
IF @FLAG = 'B'
|
|
BEGIN
|
|
|
|
|
|
SELECT SUM(X.rate) rate,SUM(Buytoleranceplus) tolPlus ,SUM(Buytoleranceminus) tolMinus
|
|
--,@remarks remarks,ISNULL(@BAL,0.00) bal
|
|
,remarks = 'Max Rate: '+CAST(SUM(X.rate) + SUM(Buytoleranceplus) AS VARCHAR)
|
|
+ ' /Min Rate: '+CAST(SUM(X.rate) - SUM(Buytoleranceminus) AS VARCHAR)
|
|
,SUM(X.unit) unit,@CurrencyName curr
|
|
, bal = (SELECT SUM(ISNULL(CLR_BAL_AMT,0))*-1 WONBAL FROM FastMoneyPro_Account.dbo.ac_master WHERE branch_id = @BRANCH_ID AND ac_currency = @CURR AND acct_rpt_code='MoneyEx')
|
|
FROM (
|
|
SELECT 0 rate,0 Buytoleranceplus, 0 Buytoleranceminus,0 unit UNION ALL
|
|
SELECT TOP 1 e.BuyRate rate,e.Buytoleranceplus,e.Buytoleranceminus,e.FixedCurUnit unit
|
|
FROM FastMoneyPro_Account.dbo.ac_master a with (nolock)
|
|
INNER JOIN EXCHANGE_CURRENCYRATE_SETUP e with (nolock) on e.baseCurrency = a.ac_currency
|
|
WHERE ac_currency = @CURR AND e.Active='y' and
|
|
acct_rpt_code = 'MoneyEx' AND a.agent_id = @BRANCH_ID
|
|
)X
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF @FLAG='bBal' ---->> branch balance on WON FOR BUY OPERATION
|
|
BEGIN
|
|
select TOP 1 cast(isnull(e.FixedCurUnit,1) as varchar)+' '+e.baseCurrency+' = '+cast(cast(e.BuyRate as float) as varchar)+' '+e.quoteCurrency [remarks]
|
|
,isnull(a.usd_amt*-1,0) [AMT]
|
|
,(SELECT ISNULL(CLR_BAL_AMT,0)*-1 WONBAL FROM FastMoneyPro_Account.dbo.ac_master WHERE branch_id = @BRANCH_ID AND ac_currency = @CurrencyName AND acct_rpt_code=@CurrencyName) WONBAL
|
|
,isnull(e.FixedCurUnit,1) unit,@CurrencyName curr
|
|
FROM FastMoneyPro_Account.dbo.ac_master a with (nolock) inner join EXCHANGE_CURRENCYRATE_SETUP e with (nolock) on e.baseCurrency = a.ac_currency
|
|
WHERE ac_currency = @CURR AND acct_rpt_code= @CURR
|
|
AND a.branch_id = @BRANCH_ID and e.Active='y' ORDER BY e.currencyRateId DESC
|
|
RETURN
|
|
END
|
|
ELSE IF @FLAG='sBal' ---->> branch balance on RESPECTIVE CURR FOR SELL OPERATION
|
|
BEGIN
|
|
select TOP 1 cast(isnull(e.FixedCurUnit,1) as varchar)+' '+e.baseCurrency+' = '+cast(cast(e.SaleRate as float) as varchar)+' '+e.baseCurrency [remarks]
|
|
,isnull(a.usd_amt*-1,0) [AMT],ISNULL(CLR_BAL_AMT,0)*-1 WONBAL,isnull(e.FixedCurUnit,1) [unit],e.baseCurrency curr
|
|
FROM FastMoneyPro_Account.dbo.ac_master a with (nolock)
|
|
inner join EXCHANGE_CURRENCYRATE_SETUP e with (nolock) on e.baseCurrency = a.ac_currency
|
|
--INNER JOIN Branches B ON B.cashId = E.RateCode WHERE ac_currency = @CURR AND acct_rpt_code= @CURR
|
|
AND A.branch_id = @BRANCH_ID AND e.Active='y' ORDER BY e.currencyRateId DESC
|
|
RETURN
|
|
END
|
|
|
|
ELSE IF @FLAG = 'tRate'-->> TODAYS RATE FOR BRANCH
|
|
BEGIN
|
|
DECLARE @table VARCHAR(MAX),@sql_filter VARCHAR(100),@select_field_list VARCHAR(MAX),@extra_field_list VARCHAR(10)
|
|
IF @sortBy IS NULL
|
|
SET @sortBy = 'CurFixed'
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'ASC'
|
|
|
|
SET @table = '(Select
|
|
B.BRANCH_NAME,B.BRANCH_ID,E.CurFixed,E.BuyRate,E.Buytoleranceplus,E.Buytoleranceminus
|
|
,E.SaleRate,E.Saletoleranceplus,E.Saletoleranceminus
|
|
,E.RevRate,E.CreatedDate
|
|
from EXCHANGE_CURRENCYRATE_SETUP E
|
|
Inner join RateCodeTable R on R.ratecodeId = E.RateCode
|
|
Inner join Branches B on B.cashId = E.RateCode
|
|
where E.CurFixed = ISNULL(CurFixed,E.CurFixed) and e.active =''y'' and r.active =''y''
|
|
) x'
|
|
|
|
SET @sql_filter = ''
|
|
IF @BRANCH_ID IS NOT NULL
|
|
SET @sql_filter +=' AND BRANCH_ID = '''+@BRANCH_ID+''''
|
|
IF @CURR IS NOT NULL
|
|
SET @sql_filter = @sql_filter + ' AND CurFixed = '''+@CURR+''''
|
|
|
|
SET @select_field_list ='
|
|
|
|
BRANCH_NAME
|
|
,BRANCH_ID
|
|
,CurFixed
|
|
,BuyRate
|
|
,Buytoleranceplus
|
|
,Buytoleranceminus
|
|
,SaleRate
|
|
,Saletoleranceplus
|
|
,Saletoleranceminus
|
|
,RevRate
|
|
,CreatedDate'
|
|
|
|
EXEC dbo.proc_paging
|
|
@table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber
|
|
END
|
|
|
|
ELSE IF @FLAG = 'RateDis' -->> BRANCH WISERATE DISPLAY
|
|
BEGIN
|
|
Select
|
|
'' BRANCH_NAME,E.baseCurrency,E.BuyRate,E.Buytoleranceplus,E.Buytoleranceminus
|
|
,E.SaleRate,E.Saletoleranceplus,E.Saletoleranceminus
|
|
,E.RevRate,E.CreatedDate ,E.FixedCurUnit
|
|
,c.curr_desc
|
|
,buyRateDecimals = CASE Charindex('.', E.BuyRate)
|
|
WHEN 0 THEN 0
|
|
ELSE
|
|
Len (
|
|
Cast(
|
|
Cast(
|
|
Reverse(CONVERT(VARCHAR(50), E.BuyRate, 128)) AS FLOAT
|
|
) AS BIGINT
|
|
)
|
|
)
|
|
END
|
|
,saleRateDecimals = CASE Charindex('.', E.SaleRate)
|
|
WHEN 0 THEN 0
|
|
ELSE
|
|
Len (
|
|
Cast(
|
|
Cast(
|
|
Reverse(CONVERT(VARCHAR(50), E.SaleRate, 128)) AS FLOAT
|
|
) AS BIGINT
|
|
)
|
|
)
|
|
END
|
|
from EXCHANGE_CURRENCYRATE_SETUP E
|
|
-- Inner join RateCodeTable R on R.ratecodeId = E.RateCode
|
|
-- Inner join Branches B on B.cashId = E.RateCode
|
|
INNER JOIN dbo.currency_setup c ON c.curr_code = e.baseCurrency
|
|
where --B.BRANCH_ID = @BRANCH_ID AND
|
|
E.baseCurrency = ISNULL(E.baseCurrency,E.baseCurrency) and E.Active='Y'-- AND R.active='Y'
|
|
ORDER BY E.FixedCurUnit, E.baseCurrency
|
|
END
|
|
|
|
ELSE IF @FLAG = 'RateEdit'-->> TODAYS RATE FOR RATE MAINTENANCE
|
|
BEGIN
|
|
IF @sortBy IS NULL
|
|
SET @sortBy = 'CurFixed'
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'ASC'
|
|
|
|
SET @table = '(SELECT E.exrateId,E.CurFixed,E.CurVariable,E.FixedCurUnit,E.RevRate,E.BuyRate,E.SaleRate
|
|
,CAST(ISNULL(SaleRate,0)-ISNULL(BuyRate,0) AS Decimal(20,6)) as MARGIN,ISNULL(e.ModifiedDate,E.CREATEDDATE) [moddate]
|
|
,ISNULL(e.ModifiedBy,E.CreatedBy) ModifiedBy FROM EXCHANGE_CURRENCYRATE_SETUP E WITH(NOLOCK) INNER JOIN RateCodeTable R WITH (NOLOCK)
|
|
ON E.RateCode=R.ratecodeId INNER JOIN Branches B ON B.cashId = E.RateCode
|
|
where b.BRANCH_ID = '''+@BRANCH_ID+''' and E.Active=''Y'' AND R.active=''Y''
|
|
) x'
|
|
|
|
SET @sql_filter = ''
|
|
IF @CURR IS NOT NULL
|
|
SET @sql_filter = @sql_filter + ' AND CurFixed = '''+@CURR+''''
|
|
|
|
SET @select_field_list ='
|
|
exrateId
|
|
,CurFixed
|
|
,CurVariable
|
|
,FixedCurUnit
|
|
,RevRate
|
|
,BuyRate
|
|
,SaleRate
|
|
,MARGIN
|
|
,moddate
|
|
,ModifiedBy'
|
|
|
|
EXEC dbo.proc_paging
|
|
@table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber
|
|
END
|
|
|
|
ELSE IF @FLAG = 'RateHistory'-->> RATE HISTORY
|
|
BEGIN
|
|
--IF @sortBy IS NULL
|
|
SET @sortBy = 'Branch_name'
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'ASC'
|
|
|
|
SET @table = '(SELECT E.*,b.BRANCH_ID as BranchId,b.Branch_name
|
|
FROM ExchangeRateTable_History E WITH(NOLOCK)
|
|
INNER JOIN Branches B ON E.RateCode = B.cashId
|
|
WHERE 1=1
|
|
) x'
|
|
|
|
SET @sql_filter = ''
|
|
IF @BRANCH_ID IS NOT NULL
|
|
SET @sql_filter +=' AND BRANCH_ID = '''+@BRANCH_ID+''''
|
|
IF @CURR IS NOT NULL
|
|
SET @sql_filter = @sql_filter + ' AND CurFixed = '''+@CURR+''''
|
|
IF @date IS NOT NULL
|
|
SET @sql_filter = @sql_filter +' AND CAST(ModifiedDate AS DATE) = '''+@date+''''
|
|
|
|
SET @select_field_list ='
|
|
exrateId
|
|
,BRANCH_ID
|
|
,Branch_name
|
|
,CurFixed
|
|
,CurVariable
|
|
,FixedCurUnit
|
|
,RevRate
|
|
,BuyRate
|
|
,Buytoleranceplus
|
|
,Buytoleranceminus
|
|
,CreatedDate
|
|
,ModifiedDate
|
|
,Saletoleranceplus
|
|
,Saletoleranceminus
|
|
,SaleRate'
|
|
|
|
EXEC dbo.proc_paging
|
|
@table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber
|
|
END
|
|
|
|
IF @FLAG ='operationDDL' -->> ONLY FOR BUY,SELL,CROSS OPERATION DO NOT CHANGE THE QUERY
|
|
BEGIN
|
|
--CURRENCY DDL
|
|
IF @BRANCH_ID IS NOT NULL
|
|
BEGIN
|
|
select c.currencyCode+'('+c.currencyName+')' [curr_name],c.currencyCode AS curr_code
|
|
from dbo.EXCHANGE_COSTRATE_SETUP b WITH(NOLOCK)
|
|
INNER JOIN currencyMaster c WITH(NOLOCK) on b.baseCurrency=c.currencyCode
|
|
where b.agentId = @BRANCH_ID
|
|
ORDER BY C.currencyCode
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
select currencyCode+'('+currencyName+')' [curr_name],currencyName from dbo.currencyMaster WHERE 1=2
|
|
END
|
|
--COUNTRY DDL
|
|
SELECT id refid,ref_code FROM FastMoneyPro_Account.dbo.StaticDataDetail WITH(NOLOCK) WHERE type_id = 1 ORDER BY ref_code
|
|
|
|
-->> Customer Type
|
|
SELECT valueId refid,detailTitle ref_code FROM dbo.staticDataValue WITH(NOLOCK) WHERE typeID=7005 order by valueId
|
|
-->> Purpose of Txn
|
|
SELECT valueId refid,detailTitle ref_code FROM dbo.staticDataValue WITH(NOLOCK) WHERE typeID=7003 order by valueId
|
|
-->> ID Type
|
|
SELECT valueId refid,detailTitle ref_code FROM dbo.staticDataValue WITH(NOLOCK) WHERE typeID=7002 order by valueId
|
|
-->> Occupation
|
|
SELECT valueId refid,detailTitle ref_code FROM dbo.staticDataValue WITH(NOLOCK) WHERE typeID=2000 order by valueId
|
|
-->> Source of Money
|
|
SELECT valueId refid,detailTitle ref_code FROM dbo.staticDataValue WITH(NOLOCK) WHERE typeID=3900 order by valueId
|
|
--Source of FCY
|
|
SELECT valueId refid,detailTitle ref_code FROM dbo.staticDataValue WITH(NOLOCK) WHERE typeID=7004 order by valueId
|
|
-- STR REASON
|
|
select id refid,ref_code from FastMoneyPro_Account.dbo.StaticDataDetail WITH(NOLOCK) where type_id=109 order by ref_code
|
|
|
|
END
|
|
|
|
GO
|