43 lines
1.3 KiB
MySQL
43 lines
1.3 KiB
MySQL
|
|
||
|
|
-- 安全删除方式（推荐）
|
||
|
|
IF OBJECT_ID('dbo.ZZV_XiaoShouHuiLv', 'V') IS NOT NULL
|
||
|
|
DROP VIEW dbo.ZZV_XiaoShouHuiLv;
|
||
|
|
GO
|
||
|
|
|
||
|
|
CREATE VIEW ZZV_XiaoShouHuiLv AS
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
t1.FID 销售订单ID,
|
||
|
|
t1.FBILLNO 销售订单号,
|
||
|
|
t1.F_CONTRACTNUMBER 纸质合同号,
|
||
|
|
t1.FDATE 业务日期,
|
||
|
|
t1fin.FSETTLECURRID 结算币别,
|
||
|
|
t1fin.FLOCALCURRID 本位币,
|
||
|
|
t1fin.FEXCHANGERATE 销售订单汇率,
|
||
|
|
1 人民币ID,
|
||
|
|
ISNULL((
|
||
|
|
SELECT
|
||
|
|
TOP 1 ISNULL(t2.FEXCHANGERATE,1)
|
||
|
|
FROM
|
||
|
|
T_BD_RATE t2
|
||
|
|
INNER JOIN T_BD_RATETYPE t2type ON t2.FRATETYPEID = t2type.FRATETYPEID
|
||
|
|
WHERE
|
||
|
|
1 = 1
|
||
|
|
AND t2type.FNUMBER = 'HLTX01_SYS'
|
||
|
|
AND t2.FDOCUMENTSTATUS = 'C'
|
||
|
|
AND t2.FFORBIDSTATUS = 'A'
|
||
|
|
AND t2.FCYFORID = t1fin.FLOCALCURRID -- 原币 = 香港元
|
||
|
|
AND t2.FCYTOID = 1 -- 目标币 = 人民币
|
||
|
|
AND DATEADD (MONTH, DATEDIFF (MONTH, 0, t1.FDATE), 0) = t2.FBEGDATE
|
||
|
|
ORDER BY
|
||
|
|
t2.FENDDATE DESC
|
||
|
|
),1) 订单本位币兑人民币汇率 -- 直接汇率
|
||
|
|
FROM
|
||
|
|
T_SAL_ORDER t1
|
||
|
|
INNER JOIN T_SAL_ORDERFIN t1fin ON t1.FID = t1fin.FID
|
||
|
|
WHERE
|
||
|
|
t1.FDOCUMENTSTATUS='c'
|
||
|
|
--ORDER BY 业务日期
|
||
|
|
|
||
|
|
--SELECT
|
||
|
|
-- DATEADD (MONTH, DATEDIFF (MONTH, 0, GETDATE ()), 0) AS 本月第一天;
|