ALTER proc [dbo].[CS_ShipQuery]
@通知單號 nvarchar(200),
@JobNum nvarchar(200),
@PartNum nvarchar(40),
@Date nvarchar(200),
@company nvarchar(20)
as
begin
declare @sql nvarchar(2000);
if object_id(N'#处理毛净重信息',N'U') is not null
begin
drop table #处理毛净重信息;
end
select row_number() over (order by t1.通知单号,t2.项次) ID,t1.通知单号,t2.项次,t2.报关单号,t1.走货日期,t1.口岸,
t1.口岸描述,t1.口岸说明,t1.审核人 as SH审核人,t1.审核日期时间 as SH审核日期时间,t2.订单号,t2.订单项次,
t3.JobNum 工单号,t2.产品编号,t9.RevisionNum,t2.实际出货数量,
t2.总箱数,t1.运输公司,t1.运输公司描述,t2.总体积,t1.柜号,t5.CustID,t5.[Name],t2.目的国家,t2.国别名称
,t1.柜型号,t1.审核码,t1.交运方式,t1.交运方式描述,t1.出货地点,t1.出货地点描述,t1.运输地点1,t1.运输地点1描述
,t1.船单号码,t4.RequestDate 约定交货日,t7.卡板类型,t7.实际装车板数,t7.实际装车总板重,t7.合箱标记,cast('' as varchar(10)) 每箱净重,
cast('' as varchar(10)) 每箱毛重,cast('' as varchar(18)) 总净重,cast('' as varchar(18)) 总毛重,cast('''' as varchar(18)) 总皮重,
t4.sales_danbie_c 单别,t6.declare_elements_c 申报要素,
t6.declare2_c 补充申报要素,t7.实际装车箱数,t8.审核人 as ST审核人,t8.审核日期 as ST审核日期时间,cast('''' as varchar(18)) 箱数状态,cast('' as datetime) 重量时间,
cast('' as varchar(50)) 单重说明,cast(null as datetime) 最后修改时间
into #处理毛净重信息
from shipheader t1
left join shipdetail t2 on t1.通知单号=t2.通知单号 and t1.company=t2.company
left join erp.JobProd t3 on t2.订单号=t3.OrderNum and t2.订单项次=t3.OrderLine and t2.company=t3.Company
left join OrderHed t4 on t2.订单号=t4.OrderNum and t2.company=t4.company
left join erp.Customer t5 on t4.CustNum=t5.CustNum and t4.company=t5.company
left join Part t6 on t2.产品编号=t6.PartNum and t2.company=t6.company
left join CustomerMaintenanceDetail t7 on t2.通知单号=t7.通知单号 and t2.项次=t7.项次 and t2.company=t7.company
left join CustomerMaintenanceHeader t8 on t1.通知单号=t8.通知单号 and t1.company=t8.company
left join erp.ShipDtl t9 on t2.通知单号=t9.PackNum and t2.项次=t9.PackLine and t2.company=t9.company
where 1=2;
set @sql=N'select row_number() over (order by t1.通知单号,t2.项次) ID,t1.通知单号,t2.项次,t2.报关单号,t1.走货日期,t1.口岸,
t1.口岸描述,t1.口岸说明,t1.审核人 as SH审核人,t1.审核日期时间 as SH审核日期时间,t2.订单号,t2.订单项次,
t3.JobNum 工单号,t2.产品编号,t9.RevisionNum,t2.实际出货数量,
t2.总箱数,t1.运输公司,t1.运输公司描述,t2.总体积,t1.柜号,t5.CustID,t5.[Name],t2.目的国家,t2.国别名称
,t1.柜型号,t1.审核码,t1.交运方式,t1.交运方式描述,t1.出货地点,t1.出货地点描述,t1.运输地点1,t1.运输地点1描述
,t1.船单号码,t4.RequestDate 约定交货日,t7.卡板类型,t7.实际装车板数,t7.实际装车总板重,t7.合箱标记,cast('''' as varchar(10)) 每箱净重,
cast('''' as varchar(10)) 每箱毛重,cast('''' as varchar(18)) 总净重,cast('''' as varchar(18)) 总毛重,cast('''' as varchar(18)) 总皮重,
t4.sales_danbie_c 单别,t6.declare_elements_c 申报要素,
t6.declare2_c 补充申报要素,t7.实际装车箱数,t8.审核人 as ST审核人,t8.审核日期 as ST审核日期时间,cast('''' as varchar(18)) 箱数状态,cast('''' as datetime) 重量时间,
cast('''' as varchar(50)) 单重说明,cast(null as datetime) 最后修改时间
from shipheader t1
left join shipdetail t2 on t1.通知单号=t2.通知单号 and t1.company=t2.company
left join erp.JobProd t3 on t2.订单号=t3.OrderNum and t2.订单项次=t3.OrderLine and t2.company=t3.Company
left join OrderHed t4 on t2.订单号=t4.OrderNum and t2.company=t4.company
left join erp.Customer t5 on t4.CustNum=t5.CustNum and t4.company=t5.company
left join Part t6 on t2.产品编号=t6.PartNum and t2.company=t6.company
left join CustomerMaintenanceDetail t7 on t2.通知单号=t7.通知单号 and t2.项次=t7.项次 and t2.company=t7.company
left join CustomerMaintenanceHeader t8 on t1.通知单号=t8.通知单号 and t1.company=t8.company
left join erp.ShipDtl t9 on t2.通知单号=t9.PackNum and t2.项次=t9.PackLine and t2.company=t9.company'
if(LEN(@通知單號)>2)
begin
set @sql=@sql+N' where t1.company='+@company+N' and '+@通知單號;
end
else if(LEN(@JobNum)>2)
begin
print @JobNum+cast(LEN(@JobNum) as nvarchar);
set @sql=@sql+N' where t1.company='+@company+N' and '+@JobNum;
end
else if(LEN(@PartNum)>2)
begin
--print @PartNum+cast(LEN(@PartNum) as nvarchar);
set @sql=@sql+N' where t1.company='+@company+N' and t2.产品编号='''+@PartNum+'''';
end
else if(LEN(@Date)>0)
begin
set @sql=@sql+N' where t1.company='+@company+N' and '+@Date;
end
print @sql;
insert into #处理毛净重信息 EXEC (@sql);
--select * from #处理毛净重信息;
declare @n int;
declare @i int;
declare @单别 nvarchar(40);
declare @工单号 nvarchar(40);
declare @走货日期 date;
declare @NetWeight varchar(10);
declare @GrossWeight varchar(10);
declare @SingleWeightDescription varchar(50);
declare @LastWeightModifyTime datetime;
declare @LastModifyTime datetime;
declare @SH审核日期时间 datetime;
declare @ST审核日期时间 datetime;
select @n=count(*) from #处理毛净重信息;
set @i=1;
while (@i<=@n)
begin
set @单别='';
set @工单号='';
set @走货日期='';
set @NetWeight=0;
set @GrossWeight=0;
set @SingleWeightDescription='';
set @LastWeightModifyTime='';
set @LastModifyTime='';
set @SH审核日期时间='';
set @ST审核日期时间='';
select @单别=单别,@工单号=工单号,@走货日期=走货日期 from #处理毛净重信息 where ID=@i;
--print @单别+'-'+@工单号;
if(@单别=N'大貨單') --汉字的地方要加N
begin
--拿到工單的毛淨重
select @NetWeight=NetWeight,@GrossWeight=GrossWeight,@LastWeightModifyTime=ConfirmDate,
@SingleWeightDescription=(case when len(rtrim(ltrim(SingleWeightDescription)))>0 then SingleWeightDescription
else ConfirmUser end)
from FGWeightInput where AutoID in (select max(AutoID) from FGWeightInput
where WorkNum=@工单号 and ConfirmDate<@走货日期);
--從工單更新毛淨重
update #处理毛净重信息 set 每箱净重=@NetWeight,每箱毛重=@GrossWeight where ID=@i;
--更新毛淨重，如果貨倉有錄入實際裝車箱數，就依照貨倉的箱數，沒有就依照算出來的箱數
update #处理毛净重信息 set 总净重=cast(Convert(decimal(18,6),每箱净重*isnull(实际装车箱数,总箱数)) as varchar(18)),
总毛重=cast(Convert(decimal(18,6),每箱毛重*isnull(实际装车箱数,总箱数)) as varchar(18)),重量时间=@LastWeightModifyTime,
总皮重=cast(Convert(decimal(18,6),每箱毛重*isnull(实际装车箱数,总箱数)+isnull(实际装车总板重,0)) as varchar(18)),
箱数状态=isnull(ST审核人,SH审核人),
单重说明=@SingleWeightDescription
where ID=@i;
--拿出幾個時間
select @SH审核日期时间=SH审核日期时间 from #处理毛净重信息 where ID=@i;
select @ST审核日期时间=ST审核日期时间 from #处理毛净重信息 where ID=@i;
--比較時間最大值
select @LastModifyTime=max(d) from
(
values
(@SH审核日期时间),
(@ST审核日期时间),
(@LastWeightModifyTime)
)
as T(d)
update #处理毛净重信息 set 最后修改时间=@LastModifyTime where ID=@i;
--print @LastModifyTime;
end
else if(@单别=N'仕啤單')
begin
select @NetWeight=NetWeight,@GrossWeight=GrossWeight,@LastModifyTime=ConfirmDate
from FGWeightInput where AutoID in (select max(AutoID) from SPWeightInput where WorkNum=@工单号 and ConfirmDate<@走货日期);
update #处理毛净重信息 set 每箱净重=@NetWeight,每箱毛重=@GrossWeight
where ID=@i;
update #处理毛净重信息 set 总净重=cast(Convert(decimal(18,6),每箱净重*isnull(实际装车箱数,总箱数)) as varchar(18)),
总毛重=cast(Convert(decimal(18,6),每箱毛重*isnull(实际装车箱数,总箱数)) as varchar(18)),重量时间=@LastWeightModifyTime,
总皮重=cast(Convert(decimal(18,6),每箱毛重*isnull(实际装车箱数,总箱数)+isnull(实际装车总板重,0)) as varchar(18)),
箱数状态=isnull(ST审核人,SH审核人)
where ID=@i;
--拿出幾個時間
select @SH审核日期时间=SH审核日期时间 from #处理毛净重信息 where ID=@i;
select @ST审核日期时间=ST审核日期时间 from #处理毛净重信息 where ID=@i;
--比較時間最大值
select @LastModifyTime=max(d) from
(
values
(@SH审核日期时间),
(@ST审核日期时间),
(@LastWeightModifyTime)
)
as T(d)
update #处理毛净重信息 set 最后修改时间=@LastModifyTime where ID=@i;
end
set @i=@i+1;
end
select * from #处理毛净重信息;
end
GO
版权声明：本文为qq_34677276原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。