存储过程示例(拷贝数据表--游标使用--更新数据-日期函数的使用)

CREATE procedure UF_Po_ScheduleSp
as

declare @CurrDate NVARCHAR(10)
set @CurrDate=convert(varchar(10),getdate(),120)

/*
* delete today
*/
delete
from Uf_Po_Schedule
where convert(varchar(10),schedule_date,120)=@CurrDate

-- and item='LW-01000080'
/*
* 拷贝计划明细表数据到Uf_Po_Schedule
*/
--SET FORCEPLAN OFF

insert Uf_Po_Schedule(
schedule_date
,item
,due_date
,ExceptMessage
,mrp_ref
,lead_time
,Shrink_fact --物料损耗因子
,Ori_Qty_On_Hand --预计在库量
,Ori_Qty_Req
,Ori_qty_recv
,Qty_On_Hand --现有在库量
,ref_num
,ref_linesuf
,ordtype)
select
getdate()
,p.Item
,p.DueDateDay
,p.ExceptMessage
,p.MrpRef
,p.lead_time
,i.Shrink_fact
,p.QtyOnHand
,p.QtyReq
,p.QtyRecv
,p.ItemQtyOnHand
,p.RefNum
,p.RefLineSuf
,p.OrdType
from PlanningDetailsView AS P
left join item i on i.item=p.item
where i.p_m_t_code='P' and isnull(P.MrpRef,'X')<>'BALANCE ON HAND' and isnull(P.MrpRef,'X')<>'现有量余额'
-- and p.item='LW-01000080'

/*
* 更改移进/移出的到期日期
*/
------------------------------------------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule
set due_date=CAST(SUBSTRING(ExceptMessage, LEN(ExceptMessage) - 3, 4)+'-'+SUBSTRING(ExceptMessage, LEN(ExceptMessage) - 9, 2)+'-'+SUBSTRING(ExceptMessage, LEN(ExceptMessage) - 6, 2) AS datetime)
where (substring(ExceptMessage,1,4)='move' or substring(ExceptMessage,1,1)='移' or substring(ExceptMessage,1,2)='移')
and convert(varchar(10),schedule_date,120)=@CurrDate
------------------------------------------------------------------------------------------------------------------------------------------

--2008-9-26
--将PO调整为相应需求数------------开始----------------

--加入损耗因子
------------------------------------------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule set Qty_Req=Ori_Qty_Req/(1-Shrink_fact),qty_recv=Ori_qty_recv/(1-Shrink_fact),qtyonhand=Ori_qty_on_hand/(1-Shrink_fact)
where convert(varchar(10),schedule_date,120)=@CurrDate
------------------------------------------------------------------------------------------------------------------------------------------

--将计划员工作台资料更新过来
---------------------------------------------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule
set Uf_Po_Schedule.vend_num=mrp_wb.process_vend_num,Uf_Po_Schedule.qty_recv=mrp_wb.reqd_qty,Uf_Po_Schedule.due_date=mrp_wb.due_date
from Uf_Po_Schedule,mrp_wb
where substring(Uf_Po_Schedule.mrp_ref,1,3)='PLN' and convert(varchar(10),schedule_date,120)=@CurrDate
and Uf_Po_Schedule.ordtype=mrp_wb.ref_type and Uf_Po_Schedule.ref_num=mrp_wb.ref_num and Uf_Po_Schedule.ref_linesuf=mrp_wb.ref_line_suf
----------------------------------------------------------------------------------------------------------------------------------------------

--以未完PO来更新PO的供应数
----------------------------------------------------------------------------------------------------------------------------------------------
/*
update Uf_Po_Schedule
set Uf_Po_Schedule.qty_po_os=(poitem.qty_ordered-poitem.qty_received),Uf_Po_Schedule.qty_recv=(poitem.qty_ordered-poitem.qty_received)
from Uf_Po_Schedule,poitem
where Uf_Po_Schedule.ref_num=poitem.po_num and Uf_Po_Schedule.ref_linesuf=poitem.po_line
and substring(Uf_Po_Schedule.mrp_ref,1,2)='PO' and convert(varchar(10),schedule_date,120)=@CurrDate
*/
--因更新时计划明细考虑的供应的损耗因子
update Uf_Po_Schedule
set Uf_Po_Schedule.qty_po_os=(poitem.qty_ordered-poitem.qty_received)
from Uf_Po_Schedule,poitem
where Uf_Po_Schedule.ref_num=poitem.po_num and Uf_Po_Schedule.ref_linesuf=poitem.po_line
and substring(Uf_Po_Schedule.mrp_ref,1,2)='PO' and convert(varchar(10),schedule_date,120)=@CurrDate

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

declare @item varchar(30),
@hand decimal(38,8), -- 作中间变量,记录上行预计在库量
@due_date datetime,
@QtyOnHand decimal(38,8) ,
@Qty_Req decimal(20,8) ,
@Qty_Recv decimal(20,8) ,
@Mrp_Ref varchar (30),
@ref_num varchar(20),
@ref_linesuf int

,@Shrink_fact decimal(9,8)

,@ref_num_t varchar(20) --记录需更改的PO号
,@ref_linesuf_t int
,@PoRemaind_Qty decimal(20,8)

,@date datetime --记录当前行日期

set @ref_num_t='XXXX'
set @ref_linesuf_t=0
set @PoRemaind_Qty=0

declare Item_t cursor static local for
select distinct(item) from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate and ordtype='P'
and isnull(exceptmessage,'x')<>'Receipt Not Needed' and isnull(exceptmessage,'x')<>'不需要接收'
-- and item='LW-01000080'

open Item_t
while 1=1
begin
fetch Item_t into
@item
if @@fetch_status<>0
break

SET @PoRemaind_Qty=0

select @Shrink_fact=Shrink_fact from item where item=@item
select top 1 @date=due_date,@hand=Qty_On_Hand/(1-isnull(@Shrink_fact,0)) from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item order by due_date,Qty_Req --取第一行日期、当前在库量
/*
select top 1 @date=due_date,@hand=Qty_On_Hand from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item order by due_date,Qty_Req --取第一行日期、当前在库量
*/
declare PlanD cursor static local for
select due_date,QtyOnHand,Qty_Req,Qty_Recv,mrp_ref,ref_num,ref_linesuf from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate and item=@item
and isnull(exceptmessage,'X')<>'Receipt Not Needed' and isnull(exceptmessage,'x')<>'不需要接收'
order by due_date,Qty_Req
open PlanD
while 1=1
begin
fetch PlanD into
@due_date ,
@QtyOnHand ,
@Qty_Req ,
@Qty_Recv ,
@Mrp_Ref ,
@ref_num ,
@ref_linesuf

set @QtyOnHand=0

if @@fetch_status<>0
begin
--有PO则更新PO为po-@hand,记录PO号及其多余数@hand;无PO则将@PoRemaind_Qty作不需要接收
if exists(select top 1 item from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@date and substring(mrp_ref,1,2)='PO'
and isnull(exceptmessage,'X')<>'Receipt Not Needed' and isnull(exceptmessage,'x')<>'不需要接收')
begin
if @PoRemaind_Qty>0 --将上日期余下PO分配完
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@PoRemaind_Qty,@date,@ref_num_t,@ref_linesuf_t,'P')
end
--重计当前日期PO
select top 1 @ref_num_t=ref_num,@ref_linesuf_t=ref_linesuf from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@date and substring(mrp_ref,1,2)='PO'
and isnull(exceptmessage,'X')<>'Receipt Not Needed' and isnull(exceptmessage,'x')<>'不需要接收' order by qty_recv DESC
update Uf_Po_Schedule set Qty_Recv=Qty_Recv-@hand where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@date and ref_num=@ref_num_t and ref_linesuf=@ref_linesuf_t and substring(mrp_ref,1,2)='PO'
--记录最后剩余PO为不需要接收
-- if @hand>0 --将上日期余下PO分配完
insert Uf_Po_Schedule(schedule_date,item,qty_not_need,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@hand ,@date,@ref_num_t,@ref_linesuf_t,'P')

end
else
begin
if @PoRemaind_Qty>0 --将上日期余下PO分配完
insert Uf_Po_Schedule(schedule_date,item,qty_not_need,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@PoRemaind_Qty ,@date,@ref_num_t,@ref_linesuf_t,'P')
end
break
end

set @QtyOnHand=@hand+@Qty_Recv-@Qty_Req

update Uf_Po_Schedule set QtyOnHand=@QtyOnHand where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@due_date and ref_num=@ref_num and ref_linesuf=@ref_linesuf

/*
print @due_date
print @QtyOnHand
print@Qty_Req
print@Qty_Recv
print@Mrp_Ref
print @ref_num
print @ref_linesuf
print @hand
print @date
*/

if @date<>@due_date --则为下一日期，则将上行预计在库量与0对比；相同日期，则不理
begin
if @hand>0 --不可能<0；=0可；大于0则查当前日期有无Po
begin
--有PO则更新PO为po-@hand,记录PO号及其多余数@hand
if exists(select top 1 item from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@date and substring(mrp_ref,1,2)='PO'
and isnull(exceptmessage,'X')<>'Receipt Not Needed' and isnull(exceptmessage,'x')<>'不需要接收')
begin
if @PoRemaind_Qty>0 --将上日期余下PO分配完
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@PoRemaind_Qty,@date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=0
set @ref_num_t='XXXX'
set @ref_linesuf_t=0
end
--重计当前日期PO

select top 1 @ref_num_t=ref_num,@ref_linesuf_t=ref_linesuf from Uf_Po_Schedule where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@date and substring(mrp_ref,1,2)='PO'
and isnull(exceptmessage,'X')<>'Receipt Not Needed' and isnull(exceptmessage,'x')<>'不需要接收' order by qty_recv DESC
update Uf_Po_Schedule set Qty_Recv=Qty_Recv-@hand where convert(varchar(10),schedule_date,120)=@CurrDate
and item=@item and due_date=@date and ref_num=@ref_num_t and ref_linesuf=@ref_linesuf_t and substring(mrp_ref,1,2)='PO'
set @PoRemaind_Qty=@hand

--为PO则不理；为需求则需要插入
if left(@Mrp_Ref,2)='CO' or left(@Mrp_Ref,4)='FCST' or left(@Mrp_Ref,4)='TRNS' or left(@Mrp_Ref,4)='PROJ' or left(@Mrp_Ref,3)='SSD'
or left(@Mrp_Ref,4)='PJOB' or left(@Mrp_Ref,4)='PMPS' or left(@Mrp_Ref,4)='PPLN' or left(@Mrp_Ref,3)='PPS' or left(@Mrp_Ref,4)='TPLN'
begin
if @PoRemaind_Qty>=@Qty_Req --不可能小于需求
begin

insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(@CurrDate,@item,@Qty_Req,@due_date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=@PoRemaind_Qty-@Qty_Req
end
end

end
else --无PO，判断当前行是否为PO，为PO则不理，不为PO则为需求，需要检查PO变量中有无值（上行多余PO），有值则insert PO,无则为库存，可不理
begin
if isnull(left(@Mrp_Ref,2),'x')<>'PO' --等于PO则不理
if @PoRemaind_Qty>0 --小于/=0可不理
if @PoRemaind_Qty>=@Qty_Req --大于当前需求，则inset PO=@Qty_Req,更新PO变量值；不可能<需求，因预计在库量不会为负
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@Qty_Req,@due_date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=@PoRemaind_Qty-@Qty_Req
end
else
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@PoRemaind_Qty,@due_date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=0
set @ref_num_t='XXXX'
set @ref_linesuf_t=0

end
end
end
else
begin
if @PoRemaind_Qty>0
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@PoRemaind_Qty,@due_date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=0
set @ref_num_t='XXXX '
set @ref_linesuf_t=0
end
end

end
else --DATE相同
if @PoRemaind_Qty>0
--print 'xx'
begin
if left(@Mrp_Ref,2)='CO' or left(@Mrp_Ref,4)='FCST' or left(@Mrp_Ref,4)='TRNS' or left(@Mrp_Ref,4)='PROJ' or left(@Mrp_Ref,3)='SSD'
or left(@Mrp_Ref,4)='PJOB' or left(@Mrp_Ref,4)='PMPS' or left(@Mrp_Ref,4)='PPLN' or left(@Mrp_Ref,3)='PPS' or left(@Mrp_Ref,4)='TPLN'
begin

if @PoRemaind_Qty>=@Qty_Req --不可能小于需求
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(@CurrDate,@item,@Qty_Req,@due_date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=@PoRemaind_Qty-@Qty_Req
end
else
begin
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(@CurrDate,@item,@PoRemaind_Qty,@due_date,@ref_num_t,@ref_linesuf_t,'P')
SET @PoRemaind_Qty=0
set @ref_num_t='XXXX'
set @ref_linesuf_t=0
end
end
end

set @hand=@QtyOnHand
set @date=@due_date

if substring(@Mrp_Ref,1,3)='PLN'
begin
if @PoRemaind_Qty>0 --小于/=0可不理
insert Uf_Po_Schedule(schedule_date,item,Qty_Recv,due_date,ref_num,ref_linesuf,ordtype)
values(getdate(),@item,@PoRemaind_Qty,@due_date,@ref_num_t,@ref_linesuf_t,'P')
break
end

--PRINT 'end'
--PRINT @QTY_RECV

end
close PlanD
DEALLOCATE PlanD

end

close Item_t
DEALLOCATE Item_t

--select * from Uf_Po_Schedule where item='LW-01000080' and convert(varchar(10),schedule_date,120)=convert(varchar(10),getdate(),120) order by due_date,qty_req
--将PO调整为相应需求数------------完----------------

-------------------------------------------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule
set qty_not_need=qty_recv
where convert(varchar(10),schedule_date,120)=@CurrDate and (isnull(exceptmessage,'x')='Receipt Not Needed' or isnull(exceptmessage,'x')='不需要接收')

update Uf_Po_Schedule
set qty_recv=0
where convert(varchar(10),schedule_date,120)=@CurrDate and (isnull(exceptmessage,'x')='Receipt Not Needed' or isnull(exceptmessage,'x')='不需要接收')
-------------------------------------------------------------------------------------------------------------------------------------------
/*
* 到期日期为今天以前的为急需
*/
---------------------------------------------------------------------------------------------------------
declare @pagetime int
set @pagetime=1
update Uf_Po_Schedule
set due_date_po=qty_recv
where due_date<convert(varchar(10),dateadd(day,@pagetime+1,getdate()),120)
and convert(varchar(10),schedule_date,120)=@CurrDate

/*
* 到期日期在第一周内的供应量按天分配
*/
----------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule
set day_1=qty_recv
where datediff(day,dateadd(day,@pagetime+1,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set day_2=qty_recv
where datediff(day,dateadd(day,@pagetime+2,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set day_3=qty_recv
where datediff(day,dateadd(day,@pagetime+3,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set day_4=qty_recv
where datediff(day,dateadd(day,@pagetime+4,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set day_5=qty_recv
where datediff(day,dateadd(day,@pagetime+5,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set day_6=qty_recv
where datediff(day,dateadd(day,@pagetime+6,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set day_7=qty_recv
where datediff(day,dateadd(day,@pagetime+7,getdate()),due_date)=0
and convert(varchar(10),schedule_date,120)=@CurrDate
/*
* 到期日期在三个月的二周至十二周，分配
*/
----------------------------------------------------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule
set week_2=qty_recv
where datediff(day,dateadd(day,@pagetime+14,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+14,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate
update Uf_Po_Schedule
set week_3=qty_recv
where datediff(day,dateadd(day,@pagetime+21,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+21,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate
update Uf_Po_Schedule
set week_4=qty_recv
where datediff(day,dateadd(day,@pagetime+28,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+28,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_5=qty_recv
where datediff(day,dateadd(day,@pagetime+35,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+35,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_6=qty_recv
where datediff(day,dateadd(day,@pagetime+42,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+42,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_7=qty_recv
where datediff(day,dateadd(day,@pagetime+49,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+49,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_8=qty_recv
where datediff(day,dateadd(day,@pagetime+56,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+56,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_9=qty_recv
where datediff(day,dateadd(day,@pagetime+63,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+63,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_10=qty_recv
where datediff(day,dateadd(day,@pagetime+70,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+70,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_11=qty_recv
where datediff(day,dateadd(day,@pagetime+77,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+77,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set week_12=qty_recv
where datediff(day,dateadd(day,@pagetime+84,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+84,getdate()),due_date)>-7
and convert(varchar(10),schedule_date,120)=@CurrDate
/*
* 到期日期在后三个月内按月分配
*/
-----------------------------------------------------------------------------------------------------------------------------------------------------

update Uf_Po_Schedule
set month_1=qty_recv
where datediff(day,dateadd(day,@pagetime+112,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+112,getdate()),due_date)>-28
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set month_2=qty_recv
where datediff(day,dateadd(day,@pagetime+140,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+140,getdate()),due_date)>-28
and convert(varchar(10),schedule_date,120)=@CurrDate

update Uf_Po_Schedule
set month_3=qty_recv
where datediff(day,dateadd(day,@pagetime+168,getdate()),due_date)<=0 and datediff(day,dateadd(day,@pagetime+168,getdate()),due_date)>-28
and convert(varchar(10),schedule_date,120)=@CurrDate
/*
* update vend_num
*/
-----------------------------------------------------------------------------------------------------------------------------------------------------
update Uf_Po_Schedule
set Uf_Po_Schedule.vend_num=po.vend_num
from Uf_Po_Schedule,po
where Uf_Po_Schedule.ref_num=po.po_num and convert(varchar(10),schedule_date,120)=@CurrDate

--update Uf_Po_Schedule
--set Uf_Po_Schedule.vend_num=itemvend.vend_num
--from Uf_Po_Schedule,itemvend
--where substring(Uf_Po_Schedule.mrp_ref,1,3)='PLN' and Uf_Po_Schedule.item=itemvend.item and itemvend.rank='1'

-----------------------------------------------------------------------------------------------------------------------------------------------------
select due_date, mrp_ref, exceptmessage,Ori_qty_on_hand, Ori_Qty_Req, Ori_qty_recv, Qty_Req,
qty_recv, ref_num, qtyONHAND
from Uf_Po_Schedule
where convert(varchar(10),schedule_date,120)=@CurrDate
order by item,due_date,qty_req

GO