保存成unicode...eg: USE [MES] GO /****** Object: StoredProcedure [dbo].[GetStdHrReport] Script Date: 09/02/2010 15:16:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER PROCEDURE [dbo].[GetStdHrReport] @Typ int, @lh varchar(30), @cbzx varchar(10), @ismt int AS BEGIN SET NOCOUNT ON; declare @asmtxz varchar(10),@bsmtxz varchar(10),@izx int,@ilh int if @ismt=1 begin set @asmtxz='' set @bsmtxz='' end else begin set @asmtxz='1022751' set @bsmtxz='1022851' end if(@lh is null)or(@lh='') set @ilh=1 else set @ilh=0 if(@cbzx is null)or(@cbzx='') set @izx=1 else set @izx=0 if(@Typ=0)--工時明細 begin With PItem(PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,Sublevel) As ( Select PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,0 From RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null) Union All Select PItem.PARN_LITM,RES_BOM.CHLD_LITM,RES_BOM.CHLD_DSC,RES_BOM.BP_NO,RES_BOM.CHLD_STK_TYP,Sublevel+1 From PItem Inner Join RES_BOM On PItem.CHLD_LITM = RES_BOM.PARN_LITM and (EFT_TO is null) ) select a.PARN_LITM as '母階料號',a.CHLD_LITM as '子階料號',a.BP_NO as'BP',a.CHLD_DSC as'說明',b.CBZX as'成本中心',b.GX as'工序',b.HR as'當月工時' from( Select PARN_LITM,CHLD_LITM,BP_NO,CHLD_DSC From PItem where CHLD_STK_TYP='M' union select PARN_LITM,PARN_LITM,max(BP_NO),max(PARN_DSC) from RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null) group by PARN_LITM)a left join PRO_ERPHR b on a.CHLD_LITM=b.LH and a.BP_NO=b.BP where (1=@ilh or a.PARN_LITM=@lh) and (1=@izx or b.CBZX=@cbzx) and (b.CBZX not IN(@asmtxz,@bsmtxz)) and a.CHLD_LITM in(select LH from SC_XLRBB where CONVERT(varchar(7),RQ,111)=CONVERT(varchar(7),GETDATE(),111)) order by a.PARN_LITM,a.CHLD_LITM,a.BP_NO,b.GX end else if(@Typ=1) begin declare @stdTb table(lh varchar(20),bp varchar(20),hr numeric(10,4)) With PItem(PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,Sublevel) As ( Select PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,0 From RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null) Union All Select PItem.PARN_LITM,RES_BOM.CHLD_LITM,RES_BOM.CHLD_DSC,RES_BOM.BP_NO,RES_BOM.CHLD_STK_TYP,Sublevel+1 From PItem Inner Join RES_BOM On PItem.CHLD_LITM = RES_BOM.PARN_LITM and (EFT_TO is null) ) insert into @stdTb(lh,bp,hr) select a.PARN_LITM,a.BP_NO,sum(isnull(b.HR,0)) from( Select PARN_LITM,CHLD_LITM,BP_NO,CHLD_DSC From PItem where CHLD_STK_TYP='M' union select PARN_LITM,PARN_LITM,max(BP_NO),max(PARN_DSC) from RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null) group by PARN_LITM)a left join PRO_ERPHR b on a.CHLD_LITM=b.LH and a.BP_NO=b.BP where (1=@ilh or a.PARN_LITM=@lh) and (1=@izx or b.CBZX=@cbzx) and (b.CBZX not IN(@asmtxz,@bsmtxz)) group by a.PARN_LITM,a.BP_NO
select PARN_LITM as'母階料號',max(PARN_DSC) as'機種說明',BP_NO as'BP',max(b.hr) as'當月總工時' from RES_BOM a left join @stdTb b on a.PARN_LITM=b.lh and a.BP_NO=b.bp where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null)and(1=@ilh or a.PARN_LITM=@lh)and a.PARN_LITM in(select LH from SC_XLRBB where CONVERT(varchar(7),RQ,111)=CONVERT(varchar(7),GETDATE(),111)) group by PARN_LITM,a.BP_NO end ENDGO
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[GetStdHrReport] Script Date: 09/02/2010 15:16:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[GetStdHrReport]
@Typ int,
@lh varchar(30),
@cbzx varchar(10),
@ismt int
AS
BEGIN
SET NOCOUNT ON;
declare @asmtxz varchar(10),@bsmtxz varchar(10),@izx int,@ilh int
if @ismt=1
begin
set @asmtxz=''
set @bsmtxz=''
end else
begin
set @asmtxz='1022751'
set @bsmtxz='1022851'
end
if(@lh is null)or(@lh='')
set @ilh=1
else
set @ilh=0
if(@cbzx is null)or(@cbzx='')
set @izx=1
else
set @izx=0
if(@Typ=0)--工時明細
begin
With PItem(PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,Sublevel)
As
(
Select PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,0 From RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null)
Union All
Select PItem.PARN_LITM,RES_BOM.CHLD_LITM,RES_BOM.CHLD_DSC,RES_BOM.BP_NO,RES_BOM.CHLD_STK_TYP,Sublevel+1 From PItem
Inner Join RES_BOM
On PItem.CHLD_LITM = RES_BOM.PARN_LITM and (EFT_TO is null)
)
select a.PARN_LITM as '母階料號',a.CHLD_LITM as '子階料號',a.BP_NO as'BP',a.CHLD_DSC as'說明',b.CBZX as'成本中心',b.GX as'工序',b.HR as'當月工時' from(
Select PARN_LITM,CHLD_LITM,BP_NO,CHLD_DSC From PItem where CHLD_STK_TYP='M'
union
select PARN_LITM,PARN_LITM,max(BP_NO),max(PARN_DSC) from RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null) group by PARN_LITM)a
left join PRO_ERPHR b on a.CHLD_LITM=b.LH and a.BP_NO=b.BP where (1=@ilh or a.PARN_LITM=@lh) and (1=@izx or b.CBZX=@cbzx) and (b.CBZX not IN(@asmtxz,@bsmtxz)) and a.CHLD_LITM in(select LH from SC_XLRBB where CONVERT(varchar(7),RQ,111)=CONVERT(varchar(7),GETDATE(),111)) order by a.PARN_LITM,a.CHLD_LITM,a.BP_NO,b.GX
end else
if(@Typ=1)
begin
declare @stdTb table(lh varchar(20),bp varchar(20),hr numeric(10,4))
With PItem(PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,Sublevel)
As
(
Select PARN_LITM,CHLD_LITM,CHLD_DSC,BP_NO,CHLD_STK_TYP,0 From RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null)
Union All
Select PItem.PARN_LITM,RES_BOM.CHLD_LITM,RES_BOM.CHLD_DSC,RES_BOM.BP_NO,RES_BOM.CHLD_STK_TYP,Sublevel+1 From PItem
Inner Join RES_BOM
On PItem.CHLD_LITM = RES_BOM.PARN_LITM and (EFT_TO is null)
)
insert into @stdTb(lh,bp,hr)
select a.PARN_LITM,a.BP_NO,sum(isnull(b.HR,0)) from(
Select PARN_LITM,CHLD_LITM,BP_NO,CHLD_DSC From PItem where CHLD_STK_TYP='M'
union
select PARN_LITM,PARN_LITM,max(BP_NO),max(PARN_DSC) from RES_BOM where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null) group by PARN_LITM)a
left join PRO_ERPHR b on a.CHLD_LITM=b.LH and a.BP_NO=b.BP where (1=@ilh or a.PARN_LITM=@lh) and (1=@izx or b.CBZX=@cbzx) and (b.CBZX not IN(@asmtxz,@bsmtxz)) group by a.PARN_LITM,a.BP_NO
select PARN_LITM as'母階料號',max(PARN_DSC) as'機種說明',BP_NO as'BP',max(b.hr) as'當月總工時' from RES_BOM a left join @stdTb b on a.PARN_LITM=b.lh and a.BP_NO=b.bp where SUBSTRING(PARN_LITM,1,1)='3' and (EFT_TO is null)and(1=@ilh or a.PARN_LITM=@lh)and a.PARN_LITM in(select LH from SC_XLRBB where CONVERT(varchar(7),RQ,111)=CONVERT(varchar(7),GETDATE(),111)) group by PARN_LITM,a.BP_NO
end
ENDGO