關帳存儲過程(寫了一天,還是有問題) |
尚未結案
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
倉庫管理系統(delphi7+MS sql2000)采用加權平均算法計算成本,即月底一次以(某物料本月的sum(進倉數量*單價*匯率)+加上上月結存的數量*單價)/(上月結存+sum(本月進倉數量)作為本月結存單價及本月所有此物料發貨成本。
可照以下的設計,還是不正確。請前輩指點!
create procedure sp_monthdata @area varchar(7) with encryption as --月結關帳存儲過程
begin
declare @mater_no varchar(14),@quantity real,@price real,
@mater_no0 varchar(14),@quantity0 real,@price0 real,@area0 varchar(7),
@rate real,
@day datetime , @day2 datetime --一個月的第一天和最後一天
set @day=@area+'-01'
set @day2=@area+'-'+str(day(dateadd(month,1,@day)-day(dateadd(month,1,@day))),2,0)
if month(dateadd(month,-1,@day2))>=10
set @area0=str(year(dateadd(month,-1,@day2)),4,0)+'-'+str(month(dateadd(month,-1,@day2)),2,0)
if month(dateadd(month,-1,@day2))<10
set @area0=str(year(dateadd(month,-1,@day2)),4,0) '-0' str(month(dateadd(month,-1,@day2)),1,0) --若月份小於10,則在前面加0
declare cur_month0 scroll cursor
for select remainquantity,price from monthdata where area=@area0
declare cur_month scroll cursor
for select a.mater_no,a.quantity,a.price,a.rate from storein a inner join storeinno b on a.in_no=b.in_no where b.in_date>=@day and b.in_date<=@day2
open Cur_month
fetch next from cur_month into @mater_no,@quantity,@price
open cur_month0
fetch next from cur_month0 into @mater_no0,@quantity0,@price0
while @@fetch_status=0
begin
update monthdata set price=(@quantity0*@price0 sum(@quantity*@price*rate))/(@quantity0 sum(quantity)) from monthdata where @mater_no=@mater_no0
update storeout set storeout.price=b.price from storeout a,monthdata b,outno c on a.out_no=c.out_no where b.mater_no=a.mater_no and c.out_date>=@day and b.in_date<=@day2
end
fetch next from cur_month into @mater_no,@quantity,@price
fetch next from cur_month0 into @mater_no0,@quantity0,@price0
end
close cur_month0
close cur_month
deallocate cur_month
deallocate cur_month0
return
end 附數據表結構:
create table monthdata --月結情況表
(sn_no int identity(100,1) constraint monthdataPK primary key not null,
area varchar(7) constraint monthareaFK foreign key references accountstate(area) not null,
mater_no varchar(14) constraint monthmaterFK foreign key references mater(mater_no) not null,
costquantity real not null,
remainquantity real not null,--實際剩余數量(下月初庫存)
checklost real default 0 not null,
price real not null)
go
create table storeinno --入庫單表
(in_no varchar (10) constraint innoPK primary key not null,
recieve varchar (8) ,--收貨人
mark int default 0,
note varchar(50),
in_date datetime not null,
store_girl varchar(8), --倉管
sn_no int IDENTITY (10000, 1),
pro_no varchar(8) constraint pronoFK references provider(pro_no) not null)
go
alter table storeinno add constraint inproFK foreign key (pro_no) references provider (pro_no) create table storein --入庫明細表
([sn_no] [int] IDENTITY (10000, 1) NOT NULL ,
in_no varchar(10) constraint PK_storein primary key not null,
pro_no varchar(8) not null,
price real not null,
mater_no varchar(14) not null,
in_date datetime not null,
quantity real,
po_no varchar(12),
rate real default (1),
type varchar(10),
mark int default 0,
note varchar(50) constraint storeFk foreign key (mater_no) references mater (mater_no)
)
go Create table outno
(out_no varchar(12) constraint outPK primary key,
out_man varchar(8),--領料人
out_girl varchar(8), --倉管
out_time datetime not null,
mold_no varchar(12) not null,
mark int default 0 not null,
)
go create table storeout
([sn_no] [int] IDENTITY (10000, 1) constraint storeoutPk primary key NOT NULL,
out_no varchar(12) not null ,
quantity real not null,
price real not null,
rate real default 1 not null,
mold_no varchar (12) constraint outmoldFk foreign key references mold(mold_no) not null,
mater_no varchar(12) constraint outmaterFk foreign key references mater(mater_no) not null,
)
go 努力过,还有什么遗憾呢!
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |