CREATE PROCEDURE CustomerTotal
@CustomerID int,
@BeginDate Datetime,
@EndDate Datetime
AS
Begin
Set NoCount On
Declare @CustomerStockIO Table --临时表
(
fDate Datetime,
fNote nvarchar(10),
fNO nvarchar(20),
fFlag int not null default 0,
fProductID int,
fQty numeric(10,2),
fUnitPrice numeric(10,2)
)
Insert Into @CustomerStockIO
Select a.fSubmitDate,'出货',a.fNO,0,b.fResID,b.fQty,b.fUnitPrice
from tSellOut a,tSellOutSub b
where a.fID=b.fID
and a.fCustomerID=@CustomerID
and a.fSubmitDate>=@BeginDate
and a.fSubmitDate<=@EndDate
Insert Into @CustomerStockIO
Select a.fSubmitDate,'退货',a.fNO,1,b.fResID,-b.fQty,b.fUnitPrice
from tSellBack a,tSellBackSub b
where a.fID=b.fID
and a.fCustomerID=@CustomerID
and a.fSubmitDate>=@BeginDate
and a.fSubmitDate<=@EndDate
Select fProductID,sum(fQty)as fQty,sum(fUnitPrice)as fUnitPrice, sum(fQty*fUnitPrice)as fSum
from @CustomerStockIO
group by fProductID
order by fProductID
Set NoCount OFF
END
***********************************************
得到用到当前物件的产品列表
作者：姜玉龙
日期：2002.06.13
***********************************************/
CREATE PROCEDURE pBomParentList @ProductID Int
AS
Begin
Declare @LevelCount Int --级数
Declare @Tmp Table --临时表
(
fResID int not null
)
Declare @TmpA Table --临时表A
(
fResID int not null
)
Declare @TmpB Table --临时表B
(
fResID int not null
)
Insert into @TmpA
Select fParentID from tBom where fPartID=@ProductID
While (Select Count(*) from @TmpA)>0
begin
If @LevelCount>20
Goto Out
Insert Into @Tmp Select * from @TmpA
Delete From @TmpB
Insert Into @TmpB Select * from @TmpA
Delete From @TmpA
Insert Into @TmpA
Select fParentID from tBom Where fPartID in (Select fResID from @TmpB)
Set @LevelCount=@LevelCount+1
end
Out:
Select Distinct fResID from @Tmp
End
***********************************************
检验BOM中子件的有效性
0，成功；
1，已存在该子件；
2，当前子件是当前父件的父类产品
作者：姜玉龙
日期：2002.06.10
***********************************************/
CREATE PROCEDURE pBomPartChedk
@ParentID Int, --父件ID
@PartID Int --子件ID
AS
Begin
Declare @LevelCount Int --级数
Declare @Tmp Table --临时表
(
fResID int not null
)
Declare @TmpA Table --临时表A
(
fResID int not null
)
Declare @TmpB Table --临时表B
(
fResID int not null
)
if exists(Select * from tBom where fParentID=@ParentID and fPartID=@PartID)
Return 1
Insert into @TmpA
Select fParentID from tBom where fPartID=@ParentID
While (Select Count(*) from @TmpA)>0
begin
If @LevelCount>20
Goto Out
Insert Into @Tmp Select * from @TmpA
if exists(Select * from @Tmp where fResID=@PartID)
Return 2
Delete From @TmpB
Insert Into @TmpB Select * from @TmpA
Delete From @TmpA
Insert Into @TmpA
Select fParentID from tBom Where fPartID in (Select fResID from @TmpB)
Set @LevelCount=@LevelCount+1
end
Out:
Return 0
/*
建立物料BOM清单
*/
CREATE PROCEDURE pBOMPartList(@intProdID int)
AS
Declare @LevCount Integer
set nocount on
Select @LevCount=1
select fPartID,fQty into #dBOMA from tBOM where fParentID=@intProdID
select fParentID,fPartID,fQty
into #dBOM from tBOM
where fParentID=@intProdID
Insert #dBOM(fParentID,fPartID,fQty) values(0,@IntProdID,1)-----将原产品加入
select fPartID=@intProdID into #dBOMB
while (select count(*) from #dBOMA)>0
begin
if (@LevCount>20)
begin
goto Out
end
Insert #dBOM(fParentID,fPartID,fQty)
select a.fParentID,a.fPartID,a.fQty*b.fQty
from tBOM a,#dBomA b
where a.fParentID=b.fPartID
delete from #dBOMB
insert #dBOMB select fPartID from #dBOMA
delete from #dBOMA
insert #dBOMA
select fPartID,fQty from #dBOM
where fParentID in (select fPartID from #dBOMB)
Delete from #dBOMB
Select @LevCount=@LevCount+1
end
out:
select fPartID as fID,sum(fQty) fQty from #dBOM group by fPartID
set nocount off
*
建立物料BOM清单
*/
CREATE PROCEDURE pBOMTreeView(@intProdID int)
AS
Begin
Declare @LevCount Integer
set nocount on
Select @LevCount=1
select fPartID into #dBOMA from tBOM where fParentID=@intProdID
select fParentID,fPartID,name=rtrim(ltrim(IsNull(tProduct.fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),fQty
into #dBOM from tBOM,tProduct
where fParentID=@intProdID and tProduct.fID=tBOM.fPartID
Insert #dBOM(fParentID,fPartID,name,fQty)-----将原产品加入
select fParentID=0,fPartID=@intProdID,name=rtrim(ltrim(IsNull(fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),1
from tProduct
where fID=@intProdID
select fPartID=@intProdID into #dBOMB
while (select count(*) from #dBOMA)>0
begin
if (@LevCount>20)
begin
goto Out
end
Insert #dBOM(fParentID,fPartID,name,fQty)
select fParentID,fPartID,name=rtrim(ltrim(IsNull(tProduct.fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),fQty
from tBOM,tProduct
where fParentID in (select fPartID from #dBOMA) and tBOM.fPartID=tProduct.fID
delete from #dBOMB
insert #dBOMB select fPartID from #dBOMA
delete from #dBOMA
insert #dBOMA
select fPartID from tBOM
where fParentID in (select fPartID from #dBOMB)
Delete from #dBOMB
Select @LevCount=@LevCount+1
end
out:
--select distinct * from #dBOM
select a.fParentID as PID,
a.fPartID as AID,a.Name as AName,a.fQty as AQty,
b.fPartID as BID,b.Name as BName,b.fQty as BQty,
c.fPartID as CID,c.Name as CName,c.fQty as CQty,
d.fPartID as DID,d.Name as DName,d.fQty as DQty,
e.fPartID as EID,e.Name as EName,e.fQty as EQty,
f.fPartID as FID,f.Name as FName,f.fQty as FQty,
g.fPartID as GID,g.Name as GName,g.fQty as GQty,
h.fPartID as HID,h.Name as HName,h.fQty as HQty
from #dBOM as a
left join #dBOM as b on a.fPartID=b.fParentID
left join #dBOM as c on b.fPartID=c.fParentID
left join #dBOM as d on c.fPartID=d.fParentID
left join #dBOM as e on d.fPartID=e.fParentID
left join #dBOM as f on e.fPartID=f.fParentID
left join #dBOM as g on e.fPartID=f.fParentID
left join #dBOM as h on e.fPartID=f.fParentID
where a.fParentID=@intProdID
set nocount off
End
/*
增加工厂日历
*/
CREATE PROCEDURE pCalendarEdit(
@intType int--增加类型。@intType=1批增；@intType=2年增；@intType=3批删。
,@datBeginDate datetime
,@datEndDate datetime
,@isSaturDay bit
,@isSunDay bit
,@numMaxTime numeric(8,2)
,@numWorkTime numeric(8,2))
AS
set NoCount on
begin tran
if @intType=1
begin
declare @datCurDate datetime
select @datCurDate =@datBeginDate
--删除已经存在的
Delete from tCalendar where fDay>=@datBeginDate and fDay<=@datEndDate
while @datCurDate<=@datEndDate
begin
insert into tCalendar(fDay,fYear,fMonth,fMaxTime,fWorkTime,fUnit,fActYear,fActMonth)
values(@datCurDate,Year(@datCurDate),Month(@datCurDate),@numMaxTime,@numWorkTime,'HRS',Year(@datCurDate),Month(@datCurDate))
select @datCurDate=@datCurDate+1
end
if @isSaturDay=1
begin
update tCalendar set fWorkTime=0 where fDay>=@datBeginDate and fDay<=@datEndDate and DATEPART(dw, fDay)=7
end
if @isSunDay=1
begin
update tCalendar set fWorkTime=0 where fDay>=@datBeginDate and fDay<=@datEndDate and DATEPART(dw,fDay)=1
end
end
else if @intType=2--年增
begin
declare @intYear int
--删除已经存在的
Delete from tCalendar where year(fDay)=year(@datEndDate)
select @intYear=Year(@datEndDate)-Year(@datBeginDate)
insert into tCalendar(fDay,fYear,fMonth,fMaxTime,fWorkTime,fUnit,fActYear,fActMonth)
select DateAdd(year,@intYear,fDay),fYear,fMonth,fMaxTime,fWorkTime,fUnit,fActYear,fActMonth from tCalendar where Year(fDay)=Year(@datBeginDate)
end
else if @intType=3
begin
Delete from tCalendar where fDay>=@datBeginDate and fDay<=@datEndDate
end
if @@error<>0
begin
rollback tran
return 1
end
else
begin
commit tran
return 0
end
set nocount off
/***********************************************
冲销单据
作者：姜玉龙
日期：2002.06.10
***********************************************/
CREATE Procedure pCounteractBill
@BillTypeID Int, --单据类型
@InBillID Int, --被冲销单内部ID
@CancelUser nvarchar(20),--冲销人
@OutBillID Int Output, --冲销单内部ID
@OutBillNO nvarchar(20) Output --冲销单外部ID
As
Begin
Declare @ErrorMsg nvarchar(200)
Declare @Date Datetime
Declare @PaySum Numeric(12,2),@Remain Numeric(12,2)
Set NoCount On
begin tran
Set @Date=Dbo.fUser_FormatDate(Getdate())
--得到冲销单的单号
exec pGetInNumber @BillTypeID,@OutBillID Output
exec pGetOutNumber @BillTypeID,@OutBillNO Output
--销售出货单
if @BillTypeID=2
Begin
--取供应商余额
Select @PaySum=a.fPaySum,@Remain=a.fRemain from tAccountC a,tSellOut b
where a.fCustomerID= b.fCustomerID and b.fID=@InBillID
--主表
Insert Into tSellOut
Select @OutBillID,fSOID,fCustomerID,@OutBillNO,fHandWorkNO,@PaySum,@Remain,fOutDepot,fOutType,fSellDep,
@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fShipper,fRemarks
from tSellOut Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tSellOutSub
Select @OutBillID,fResID,fSOQty,-fQty,fPrePrice,fUnitPrice
from tSellOutSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tSellOut Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--销售退货单
if @BillTypeID=3
Begin
--取供应商余额
Select @PaySum=a.fPaySum,@Remain=a.fRemain from tAccountC a,tSellOut b
where a.fCustomerID= b.fCustomerID and b.fID=@InBillID
--主表
Insert Into tSellBack
Select @OutBillID,fSOID,fCustomerID,@OutBillNO,fHandWorkNO,@PaySum,@Remain,fInDepot,fInType,fSellDep,
fPayType,@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fConsignee,fRemarks
from tSellBack Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tSellBackSub
Select @OutBillID,fResID,fSOQty,-fQty,fPrePrice,fUnitPrice
from tSellBackSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tSellBack Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--采购入库单
if @BillTypeID=5
Begin
--主表
Insert Into tPOIncoming
Select @OutBillID,fPOID,fProvideID,@OutBillNO,fHandWorkNO,fInDepot,fInType,
@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fConsignee,fRemarks
from tPOIncoming Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tPOIncomingSub
Select @OutBillID,fResID,fPOQty,-fQty,fUnitPrice
from tPOIncomingSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tPOIncoming Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--采购退货单
if @BillTypeID=6
Begin
--主表
Insert Into tPOBack
Select @OutBillID,fPOID,fProvideID,@OutBillNO,fHandWorkNO,fOutDepot,fOutType,
fPayType,@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fConsignee,fRemarks
from tPOBack Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tPOBackSub
Select @OutBillID,fResID,fPOQty,-fQty,fUnitPrice
from tPOBackSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tPOBack Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--移仓单
if @BillTypeID=8
Begin
--主表
Insert Into tTransfer
Select @OutBillID,@OutBillNO,fHandWorkNO,fOutDepot,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fConsignee,fRemarks
from tTransfer Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tTransferSub
Select @OutBillID,fResID,-fQty,fPrePrice
from tTransferSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tTransfer Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--形态转换单
if @BillTypeID=13
Begin
--主表
Insert Into tTransition
Select @OutBillID,@OutBillNO,fHandWorkNO,fOutDepot,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fConsignee,fRemarks
from tTransition Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tTransitionSubS
Select @OutBillID,fResID,-fQty,fPrePrice
from tTransitionSub where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tTransitionSubD
Select @OutBillID,fResID,-fQty,fPrePrice
from tTransitionSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tTransition Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--领料单/补料单
if (@BillTypeID=10) or (@BillTypeID=11)
Begin
--主表
Insert Into tTakeMateriel
Select @OutBillID,@OutBillNO,fHandWorkNO,fProduceID,fProductID,fQty,fDOID,fType,fOutDep,fOutDepot,fInDep,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fReceiver,fRemarks
from tTakeMateriel Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tTakeMaterielSub
Select @OutBillID,fResID,fTotalQty,-fQty,fPrePrice
from tTakeMaterielSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tTakeMateriel Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--回料单
if @BillTypeID=12
Begin
--主表
Insert Into tReturnMateriel
Select @OutBillID,@OutBillNO,fHandWorkNO,fProduceID,fProductID,fQty,fDOID,fType,fOutDep,fOutDepot,fInDep,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fReceiver,fRemarks
from tReturnMateriel Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tReturnMaterielSub
Select @OutBillID,fResID,fTotalQty,-fQty,fPrePrice
from tReturnMaterielSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tReturnMateriel Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
--生产入仓单
if @BillTypeID=18
Begin
--主表
Insert Into tProductIncome
Select @OutBillID,@OutBillNO,fHandWorkNO,fProduceID,fProductID,fQty,fDOID,fType,fOutDepot,fOutDep,fInDepot,fInDep,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fReceiver,fRemarks
from tProductIncome Where fID=@InBillID
if @@Error<>0
Goto Failed
--从表
Insert Into tProductIncomeSub
Select @OutBillID,fResID,fPOQty,-fQty,fPrePrice
from tProductIncomeSub where fID=@InBillID
if @@Error<>0
Goto Failed
--审核
Update tProductIncome Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End
Goto Succeed
Failed:
RaisError(@ErrorMsg,16,1)
Rollback Tran
Set NoCount Off
Return 1
Succeed:
Commit Tran
Set NoCount Off
Return 0
End