写的几个存储过程，复习下了

jessen163

浏览: 465608 次
性别:
来自: 潘多拉

最近访客更多访客>>

huanggua12353719

leimingchao

hxd1220

chiqiansunny

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

存储过程

ALTER proc [dbo].[proc_jiancebiao]

@zhiling varchar(1000)

AS

/***************************

名稱: [proc_jiancebiao]

作用: 製令多階物料用量表（檢測表）

參數：@zhiling,制令號，用","分隔開如:611603084,611000922

作者：kk

時間：-6-1

測試: exec proc_jiancebiao '611603084,611000922'

****************************/

SET NOCOUNT ON

declare @t_level table(itemcode varchar(20),child_itemcode varchar(20),level int,qty int)

declare @level int

declare @temp1 table(itemid varchar(20),itemname nvarchar(100),standards nvarchar(100),unit nvarchar(10),qty int)

declare @temp2 table(itemid varchar(20),storeQty int)

declare @temp3 table(itemid varchar(20),notGetQty int)

declare @temp4 table(itemid varchar(20),notReachQty int)

set @level=1

insert @t_level select ib01,ib03,@level,ib04*num from bomib t1

join (select mb04 as itemcode,mb06 as num from mocmb where ','+@zhiling+',' like '%'+mb01+mb02+'%') t2 on t1.ib01=t2.itemcode

while @@rowcount>0

begin

    set @level=@level+1

    insert @t_level select t1.ib01,t1.ib03,@level,t1.ib04*t2.qty from bomib t1

    inner join @t_level t2 on t1.ib01=t2.child_itemcode

    where t2.level=@level-1

end

--基本資料

insert @temp1 select child_itemcode as itemid,t2.ma02 as itemname,t2.ma03 as standards,t2.ma05 as unit,sum(qty) as qty from @t_level t1

join invma t2 on t1.child_itemcode = t2.ma01

group by child_itemcode,t2.ma02,t2.ma03,t2.ma05

--庫存數量

insert @temp2 select * from openrowset('MSDASQL',

'srsdb';'';'',

'select mb01 as itemid,sum(mb04) as store_num from invmb group by mb01')

--下單未領料

insert @temp3 select me04 as itemid,sum(me07) as notGetQty from mocme

where me10='N' and isnull(rtrim(me11),'')<>'' and dbo.changeDXDate(me11)>DATEADD(mm,-4,getdate()) and me01 in ('631','633','651','652')

group by me04

--在途量

insert @temp4 select tb04 as itemid,sum(isnull(tb06,0))-sum(isnull(tb07,0)) as notReachQty from purtb

where tb14='N' and isnull(rtrim(tb13),'')<>'' and dbo.changeDXDate(tb13)>DATEADD(mm,-4,getdate())

group by tb04

--查詢結果

select '制令別' as itemid,'制令號' as itemname,'品號' as standards,'數量' as unit,qty=null,storeqty=null,notgetqty=null,notreachqty=null

union all select mb01,mb02,mb04,cast(mb06 as varchar(15)),null,null,null,null from mocmb where ','+@zhiling+',' like '%'+mb01+mb02+'%'

union all select '------------','------------','-------------','------------',null,null,null,null

union all

select a.*,b.storeQty,isnull(c.notGetQty,0)as notGetQty,isnull(d.notReachQty,0) as notReachQty

from @temp1 a

left join @temp2 b on a.itemid=b.itemid

left join @temp3 c on a.itemid=c.itemid

left join @temp4 d on a.itemid=d.itemid

-------------------------------------------------------------------------

ALTER proc [dbo].[p_useThisItemProduct]

@itemcode varchar(20)

AS

/***************************

名稱:[p_useThisItemProduct]

作用:所有用到此物料的成品及其成本分析

參數：@itemcode ,物料品號

作者：kk

時間：-6-1

測試: exec p_useThisItemProduct 'PBXSEBIR6027AW2'

****************************/

declare @t_level table(itemcode varchar(20),level int)

declare @level int

set @level=1

--查找所有用到此物料的成品及半成品

insert @t_level select ib01,@level from bomib where ib03=@itemcode

while @@rowcount>0

begin

    set @level=@level+1

    insert @t_level select t1.ib01,@level from bomib t1

    inner join @t_level t2 on t1.ib03=t2.itemcode

    where t2.level=@level-1

end

--只保留成品

delete from @t_level where left(ltrim(itemcode),1)<>'F'

--展開物料清單，計算成本,使用自定義函數dbo.fun_costAnalyse()

select t1.itemcode,t2.ma02 as itemname,t2.ma03 as standards,dbo.fun_costAnalyse(t1.itemcode) as cost from @t_level t1

left join invma t2 on t1.itemcode = t2.ma01

--------------------------------------------------------

ALTER proc [dbo].[po_bomZ]

@start_date varchar(6),

@end_date varchar(6)

AS

/***************************

名稱:po_bomz

作用:未出貨訂單多階物料需求用量表

參數：@start_date,開始日期，@end_date,結束日期

作者：kk

時間：-6-1

測試: exec po_bomz '060530','060531'

****************************/

declare @t table(itemcode varchar(20),num int)

declare @t_level table(itemcode varchar(20),child_itemcode varchar(20),level int,qty int)

declare @level int

declare @temp1 table(itemid varchar(20),itemname nvarchar(100),standards nvarchar(100),unit nvarchar(10),qty int)

declare @temp2 table(itemid varchar(20),storeQty int)

declare @temp3 table(itemid varchar(20),notGetQty int)

declare @temp4 table(itemid varchar(20),notReachQty int)

set @level=1

insert @t select tc04 as itemcode,sum(isnull(tc06,0))-sum(isnull(tc07,0)) as num from coptb t1

left join coptc t2 on t1.tb01+t1.tb02=t2.tc01+t2.tc02

where t1.tb03 >= @start_date and t1.tb03<'1000000' and (t1.tb03<= @end_date or @end_date='') and tc11='N'

group by tc04

insert @t_level select ib01,ib03,@level,ib04*num from bomib t1

inner join @t t2 on t1.ib01=t2.itemcode

while @@rowcount>0

begin

    set @level=@level+1

    insert @t_level select t1.ib01,t1.ib03,@level,t1.ib04*t2.qty from bomib t1

    inner join @t_level t2 on t1.ib01=t2.child_itemcode

    where t2.level=@level-1

end

insert @temp1 select child_itemcode as itemid,t2.ma02 as itemname,t2.ma03 as standards,t2.ma05 as unit,sum(qty) as qty from @t_level t1

join invma t2 on t1.child_itemcode = t2.ma01

group by child_itemcode,t2.ma02,t2.ma03,t2.ma05

insert @temp2 select mb01 as itemid,sum(mb04) as store_num from invmb group by mb01

insert @temp3 select me04 as itemid,sum(me07) as notGetQty from mocme

where me10='N' and isnull(rtrim(me11),'')<>'' and dbo.changeDXDate(me11)>DATEADD(mm,-4,getdate()) and me01 in ('631','633','651','652')

group by me04

insert @temp4 select tb04 as itemid,sum(isnull(tb06,0))-sum(isnull(tb07,0)) as notReachQty from purtb

where tb14='N' and isnull(rtrim(tb13),'')<>'' and dbo.changeDXDate(tb13)>DATEADD(mm,-4,getdate())

group by tb04

select a.*,b.storeQty,isnull(c.notGetQty,0)as notGetQty,isnull(d.notReachQty,0) as notReachQty

from @temp1 a

left join @temp2 b on a.itemid=b.itemid

left join @temp3 c on a.itemid=c.itemid

left join @temp4 d on a.itemid=d.itemid

--未出貨訂單

union all

select '----------------','------------------','------------------','-------------------',null,null,null,null

union all

select '單別','單號','訂單日期','品號',null,null,null,null

union all

select t1.tb01,t1.tb02,t1.tb03,tc04 as itemcode,sum(isnull(tc06,0))-sum(isnull(tc07,0)) as num,null,null,null from coptb t1

left join coptc t2 on t1.tb01+t1.tb02=t2.tc01+t2.tc02

where t1.tb03 >= @start_date and t1.tb03<'1000000' and (t1.tb03<= @end_date or @end_date='') and tc11='N'

group by t1.tb01,t1.tb02,t1.tb03,t2.tc04

分享到：