You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
156 lines
6.5 KiB
Plaintext
156 lines
6.5 KiB
Plaintext
alter table t_ch_fee
|
|
add 会计月份 varchar(7)
|
|
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[p_exec_accmonth]
|
|
@con_no varchar(100)
|
|
AS
|
|
DECLARE
|
|
@Eta datetime,
|
|
@CloseDate datetime,
|
|
@FeeStatus varchar(8)
|
|
|
|
|
|
SELECT
|
|
@Eta=预抵日期,
|
|
@CloseDate=费用封帐日期,
|
|
@FeeStatus=费用状态
|
|
FROM t_op_seae
|
|
WHERE 编号=@con_no
|
|
|
|
|
|
if (@Eta=null) return;
|
|
|
|
if (@FeeStatus='费用封帐' and @CloseDate!=NULL) BEGIN
|
|
|
|
|
|
UPDATE T_CH_FEE SET 会计月份=CASE WHEN DAY(@Eta)>25 and MONTH(@Eta) not in (3,6,9,12) THEN SUBSTRING(CONVERT(char(15),dateadd(month,1,convert(datetime,SUBSTRING(CONVERT(char(15),@Eta,120),1,4)+'-'+SUBSTRING(CONVERT(char(15),@Eta,120),6,2)+'-01')),120),1,7) ELSE SUBSTRING(CONVERT(char(15),@Eta,120),1,7) END
|
|
where 编号=@con_no and 录入日期<=@CloseDate
|
|
UPDATE T_CH_FEE SET 会计月份=CASE WHEN DAY(录入日期)>25 and MONTH(录入日期) not in (3,6,9,12) THEN SUBSTRING(CONVERT(char(15),dateadd(month,1,convert(datetime,SUBSTRING(CONVERT(char(15),录入日期,120),1,4)+'-'+SUBSTRING(CONVERT(char(15),录入日期,120),6,2)+'-01')),120),1,7) ELSE SUBSTRING(CONVERT(char(15),录入日期,120),1,7) END
|
|
where 编号=@con_no and 录入日期>@CloseDate
|
|
|
|
|
|
|
|
END ELSE BEGIN
|
|
|
|
UPDATE T_CH_FEE SET 会计月份=CASE WHEN DAY(@Eta)>25 and MONTH(@Eta) not in (3,6,9,12) THEN SUBSTRING(CONVERT(char(15),dateadd(month,1,convert(datetime,SUBSTRING(CONVERT(char(15),@Eta,120),1,4)+'-'+SUBSTRING(CONVERT(char(15),@Eta,120),6,2)+'-01')),120),1,7) ELSE SUBSTRING(CONVERT(char(15),@Eta,120),1,7) END
|
|
where 编号=@con_no
|
|
|
|
|
|
END
|
|
|
|
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER TRIGGER [dbo].[r_ch_fee_update] ON [dbo].[t_ch_fee]
|
|
FOR UPDATE
|
|
AS
|
|
if
|
|
(SELECT del.费用状态 FROM deleted del)<>'录入状态' and (update(币别) or update(金额) or update(汇率) or update(结算金额)) or
|
|
(update(费用状态) and (SELECT del.费用状态 FROM deleted del)='录入状态' and (SELECT ins.费用状态 FROM inserted ins)<>'录入状态')or
|
|
(update(费用状态) and (SELECT del.费用状态 FROM deleted del)='提交审核' and (SELECT ins.费用状态 FROM inserted ins)='录入状态')
|
|
begin
|
|
DECLARE @bianhao varchar(12)
|
|
select @bianhao=(SELECT del.编号 FROM deleted del)
|
|
EXEC p_op_gain @con_no =@bianhao
|
|
exec p_exec_accmonth @bianhao
|
|
end
|
|
|
|
|
|
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[v_fee_do_seae]
|
|
AS
|
|
SELECT dbo.t_ch_fee_do.DO_ID, dbo.t_ch_fee_do.金额, dbo.t_ch_fee.CH_ID, dbo.t_ch_fee.会计月份,
|
|
dbo.t_ch_fee_do.原始金额, dbo.t_ch_fee_do.工作编号, dbo.t_ch_fee.费用名称,
|
|
dbo.t_ch_fee.币别 AS 原始币别, dbo.t_op_seae.编号, dbo.t_op_seae.业务状态,
|
|
dbo.t_op_seae.费用状态, dbo.t_op_seae.业务编号, dbo.t_op_seae.会计期间,
|
|
dbo.t_op_seae.主提单号, dbo.t_op_seae.分提单号, dbo.t_op_seae.委托编号, dbo.t_op_seae.真提单号,
|
|
dbo.t_op_seae.装运方式, dbo.t_op_seae.委托单位, dbo.t_op_seae.代理,
|
|
dbo.t_op_seae.场站, dbo.t_op_seae.船名, dbo.t_op_seae.航次,
|
|
dbo.t_op_seae.开船日期,dbo.t_op_seae.预抵日期, dbo.t_op_seae.装货港, dbo.t_op_seae.卸货港,
|
|
dbo.t_op_seae.件数, dbo.t_op_seae.包装, dbo.t_op_seae.重量, dbo.t_op_seae.尺码,
|
|
dbo.t_op_seae.集装箱, dbo.t_op_seae.录入人, dbo.t_op_seae.录入日期,
|
|
dbo.t_op_seae.操作员, dbo.t_op_seae.揽货人, dbo.t_op_seae.客服员,
|
|
dbo.t_op_seae.航线, dbo.t_op_seae.船公司, dbo.t_op_seae.货代公司,
|
|
dbo.t_op_seae.报关行, dbo.t_op_seae.承运车队,dbo.t_ch_fee.类型,ltrim(STR((CASE WHEN dbo.t_ch_fee_do.原始金额=0 THEN 0 ELSE
|
|
(dbo.t_ch_fee_do.金额/dbo.t_ch_fee_do.原始金额) END),10,4)) AS 折算汇率,dbo.t_ch_fee.汇率 as 原始汇率,(CASE WHEN (dbo.t_ch_fee.币别<>dbo.t_ch_fee_do.币别) and (dbo.t_ch_fee.币别='USD') THEN (dbo.t_ch_fee_do.金额-(dbo.t_ch_fee_do.原始金额*dbo.t_ch_fee.汇率)) ELSE
|
|
0 END) AS 结算差额
|
|
FROM dbo.t_ch_fee_do INNER JOIN
|
|
dbo.t_ch_fee ON dbo.t_ch_fee_do.CH_ID = dbo.t_ch_fee.CH_ID INNER JOIN
|
|
dbo.t_op_seae ON dbo.t_ch_fee_do.业务编号 = dbo.t_op_seae.编号
|
|
GO
|
|
|
|
declare @bsno varchar(12)
|
|
declare CurPlu cursor for
|
|
select 编号 BSNO
|
|
from t_op_seae
|
|
open CurPlu
|
|
fetch next from CurPlu into @bsno
|
|
while @@Fetch_Status=0
|
|
begin
|
|
exec p_exec_accmonth @bsno
|
|
|
|
fetch next from CurPlu into @bsno
|
|
end
|
|
|
|
|
|
close CurPlu
|
|
dealLocate CurPlu
|
|
|
|
GO
|
|
|
|
ALTER VIEW v_op_gain
|
|
as
|
|
SELECT 编号,会计月份,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN (CASE 币别 WHEN 'USD' THEN ISNULL(金额,0) ELSE 0 END)
|
|
ELSE 0 END),0) 应收USD,
|
|
isnull(SUM(CASE 类型 WHEN '付' THEN (CASE 币别 WHEN 'USD' THEN ISNULL(金额,0) ELSE 0 END)
|
|
ELSE 0 END),0) 应付USD,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN (CASE 币别 WHEN 'USD' THEN ISNULL(金额,0) ELSE 0 END)
|
|
ELSE -(CASE 币别 WHEN 'USD' THEN ISNULL(金额,0) ELSE 0 END) END),0) 利润USD,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN (CASE 币别 WHEN 'USD' THEN ISNULL(结算金额,0) ELSE 0 END)
|
|
ELSE 0 END),0) 已收USD,
|
|
isnull(SUM(CASE 类型 WHEN '付' THEN (CASE 币别 WHEN 'USD' THEN ISNULL(结算金额,0) ELSE 0 END)
|
|
ELSE 0 END),0) 已付USD,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN (CASE 币别 WHEN 'USD' THEN 0 ELSE ISNULL(金额,0) * ISNULL(汇率,0) END)
|
|
ELSE 0 END),0) 应收RMB,
|
|
isnull(SUM(CASE 类型 WHEN '付' THEN (CASE 币别 WHEN 'USD' THEN 0 ELSE ISNULL(金额,0)* ISNULL(汇率,0) END)
|
|
ELSE 0 END),0) 应付RMB,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN (CASE 币别 WHEN 'USD' THEN 0 ELSE ISNULL(金额,0) * ISNULL(汇率,0) END)
|
|
ELSE -(CASE 币别 WHEN 'USD' THEN 0 ELSE ISNULL(金额,0) * ISNULL(汇率,0) END) END),0) 利润RMB,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN (CASE 币别 WHEN 'USD' THEN 0 ELSE ISNULL(结算金额,0) * ISNULL(汇率,0) END)
|
|
ELSE 0 END),0) 已收RMB,
|
|
isnull(SUM(CASE 类型 WHEN '付' THEN (CASE 币别 WHEN 'USD' THEN 0 ELSE ISNULL(结算金额,0) * ISNULL(汇率,0) END)
|
|
ELSE 0 END),0) 已付RMB,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN ISNULL(金额,0) * ISNULL(汇率,0) ELSE 0 END),0) 合计应收,
|
|
isnull(SUM(CASE 类型 WHEN '付' THEN ISNULL(金额,0) *ISNULL( 汇率,0) ELSE 0 END),0) 合计应付,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN ISNULL(金额,0) * ISNULL(汇率,0) ELSE -(ISNULL(金额,0) * ISNULL(汇率,0)) END),0) 毛利润,
|
|
isnull(SUM(CASE 类型 WHEN '收' THEN ISNULL(结算金额,0) * ISNULL(汇率,0) ELSE 0 END),0) 合计已收,
|
|
isnull(SUM(CASE 类型 WHEN '付' THEN ISNULL(结算金额,0) *ISNULL( 汇率,0) ELSE 0 END),0) 合计已付,
|
|
(CASE WHEN isnull(SUM(CASE 类型 WHEN '收' THEN ISNULL(金额,0) * ISNULL(汇率,0) ELSE 0 END),0)= 0 THEN '无法计算' ELSE ltrim(str(isnull(SUM(CASE 类型 WHEN '收' THEN ISNULL(金额,0) * ISNULL(汇率,0) ELSE -(ISNULL(金额,0) * ISNULL(汇率,0)) END),0)
|
|
/ isnull(SUM(CASE 类型 WHEN '收' THEN ISNULL(金额,0) * ISNULL(汇率,0) ELSE 0 END),0) * 100, 10, 2)) + '%' END) AS 利润率
|
|
FROM t_ch_fee
|
|
WHERE (费用状态 <> '录入状态')
|
|
GROUP BY 编号,会计月份
|
|
|
|
|
|
GO
|
|
|
|
ALTER TABLE t_seafee_bill_items
|
|
add [操作员] [varchar](10) NULL |