库位实现方案更新.docx
- 文档编号:15567131
- 上传时间:2023-07-05
- 格式:DOCX
- 页数:17
- 大小:44.30KB
库位实现方案更新.docx
《库位实现方案更新.docx》由会员分享,可在线阅读,更多相关《库位实现方案更新.docx(17页珍藏版)》请在冰点文库上搜索。
库位实现方案更新
简单的动态库位管理解决方案
第一步:
在仓库主数据上添加自定义字段:
CH_WhSh,描述:
“需要库位管理”。
第二步:
在营销凭证-行里面,添加两个自定义字段:
CH_FrSh,CH_ToSh,如下图:
第三步:
打开公司数据库,新建一个查询,复制入下列代码并运行
createtable[@CHSH]
(TransNumintPRIMARYKEY,WhShelfnvarchar(20))
第四步:
打开公司数据库,新建一个查询,复制入下列代码并运行:
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURECH_AddShInfo
@object_typenvarchar(25),
@list_of_cols_val_tab_delnvarchar(255)
AS
BEGIN
declare@sql_strnvarchar(2000)
declare@strnvarchar(20)
if@object_type=20set@str='PDN1'
if@object_type=18set@str='PCH1'
if@object_type=59set@str='IGN1'
if@object_type=16set@str='RDN1'
if@object_type=14set@str='RIN1'
if@object_type=15set@str='DLN1'
if@object_type=13set@str='INV1'
if@object_type=60set@str='IGE1'
if@object_type=21set@str='RPD1'
if@object_type=19set@str='RPC1'
if@object_type=67set@str='WTR1'
set@sql_str='INSERTINTO[@CHSH](TransNum,WhShelf)SELECTOINM.TransNum,CASEWHENoinm.inqty>0
THENt1.U_CH_toshELSEt1.u_ch_frshENDASshFROMOINMLEFTOUTERJOIN
'+@str+'ASt1ONOINM.DocLineNum=t1.LineNumANDOINM.CreatedBy=t1.DocEntry
WHERE(OINM.TransType='+@object_type+')AND(OINM.InQty>0)AND(OINM.CreatedBy=
'+@list_of_cols_val_tab_del+')AND(OINM.WarehouseIN(SELECTWhsCodeFROMOWHS
WHERE(U_CH_WhSh=1)))OR(OINM.TransType='+@object_type+')AND(OINM.CreatedBy=
'+@list_of_cols_val_tab_del+')AND(OINM.OutQty>0)AND(OINM.WarehouseIN
(SELECTWhsCodeFROMOWHSASOWHS_1WHERE(U_CH_WhSh=1)))'
exec(@sql_str)
END
GO
第五步:
打开公司数据库,新建一个查询,复制入下列代码,并运行:
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEFUNCTIONCH_InquireQty
(
@ItemCodenvarchar(20),
@DocDatedatetime,
@WhsCodenvarchar(8),
@WhShelfnvarchar(20)
)
RETURNSnumeric(19,6)
AS
BEGIN
DECLARE@qtynumeric(19,6)
ifisnull(@DocDate,'')=''set@DocDate=getdate()
select@qty=SUM(OINM.InQty-OINM.OutQty)FROM[@CHSH]RIGHTOUTERJOIN
OINMON[@CHSH].TransNum=OINM.TransNumWHERE(OINM.ItemCode=@ItemCode)AND(OINM.DocDate<=
@DocDate)AND(OINM.Warehouse=@WhsCode)AND([@CHSH].WhShelf=@WhShelf)
if@qtyisnullset@qty=0
RETURN@qty
END
GO
第六步:
打开公司数据库,新建一个查询,复制入如下代码,并运行:
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
CreateFUNCTION[dbo].[CH_CheckError]
(
@object_typenvarchar(25),
@list_of_cols_val_tab_delnvarchar(255)
)
RETURNSint
AS
BEGIN
declare@ch_getvalueint
declare@WhsCodenvarchar(8)
declare@ItemCodenvarchar(20)
declare@DocDatedatetime
declare@Shnvarchar(20)
declare@qtynumeric(19,6)
declare@LineNumint
set@ch_getvalue=0
if@object_type<>67--不是库存转储时,代码基本相同
begin
if@object_type=20
begin
SELECTtop
(1)@ch_getvalue=LineNum+1FROMPDN1WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)AND(U_CH_ToShISNULL)
gotoendlabel
end
if@object_type=18
begin
SELECTtop
(1)@ch_getvalue=LineNum+1FROMPCH1WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)AND(U_CH_ToShISNULL)
gotoendlabel
end
if@object_type=59
begin
SELECTtop
(1)@ch_getvalue=LineNum+1FROMIGN1WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)AND(U_CH_ToShISNULL)
gotoendlabel
end
if@object_type=16
begin
SELECTtop
(1)@ch_getvalue=LineNum+1FROMRDN1WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)AND(U_CH_ToShISNULL)
gotoendlabel
end
if@object_type=14
begin
SELECTtop
(1)@ch_getvalue=LineNum+1FROMRIN1WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)AND(U_CH_ToShISNULL)
gotoendlabel
end
if@object_type=15
begin
declarenew_Doccursorfor
selectlinenum,whscode,ItemCode,docdate,U_CH_FrSh,quantityfromDLN1
WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)
end
if@object_type=13
begin
declarenew_Doccursorfor
selectlinenum,whscode,ItemCode,docdate,U_CH_FrSh,quantityfromINV1
WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)
end
if@object_type=60
begin
declarenew_Doccursorfor
selectlinenum,whscode,ItemCode,docdate,U_CH_FrSh,quantityfromIGE1
WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)
end
if@object_type=21
begin
declarenew_Doccursorfor
selectlinenum,whscode,ItemCode,docdate,U_CH_FrSh,quantityfromRPD1
WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)
end
if@object_type=19
begin
declarenew_Doccursorfor
selectlinenum,whscode,ItemCode,docdate,U_CH_FrSh,quantityfromRPC1
WHERE(WhsCodeIN(SELECTWhsCodeFROMOWHSWHERE(U_CH_WhSh=1)))AND(DocEntry=@list_of_cols_val_tab_del)
end
opennew_doc
fetchnextfromnew_doc
into@LineNum,@WhsCode,@ItemCode,@DocDate,@Sh,@qty
WHILE@@FETCH_STATUS=0
Begin
ifisnull(@DocDate,'')=''set@DocDate=getdate()
iflen(@sh)>0
begin
if@qty>dbo.CH_InquireQty(@ItemCode,@DocDate,@WhsCode,@sh)
begin
set@ch_getvalue=@LineNum+1
gotoendlabel
end
end
else
begin
set@ch_getvalue=@LineNum+1
gotoendlabel
end
fetchnextfromnew_doc
into@LineNum,@WhsCode,@ItemCode,@DocDate,@Sh,@qty
end
closenew_doc
DEALLOCATEnew_doc
end
else--库存转储的情形
begin
declare@fillernvarchar(8)
declare@wh1int
declare@wh2int
declare@sh2nvarchar(20)
declarenew_doccursorfor
SELECTWTR1.LineNum,OWTR.DocDate,OWTR.Filler,WTR1.Quantity,WTR1.WhsCode,WTR1.ItemCode,OWHS.U_CH_WhSh,
OWHS_1.U_CH_WhShASFrWh,WTR1.U_CH_FrSh,WTR1.U_CH_ToShFROMOWHSASOWHS_1RIGHTOUTERJOIN
OWTRONOWHS_1.WhsCode=OWTR.FillerRIGHTOUTERJOINWTR1INNERJOIN
OWHSONWTR1.WhsCode=OWHS.WhsCodeONOWTR.DocEntry=WTR1.DocEntryWHERE(WTR1.DocEntry=@list_of_cols_val_tab_del)
opennew_doc
fetchnextfromnew_doc
into@LineNum,@DocDate,@filler,@qty,@WhsCode,@ItemCode,@wh1,@wh2,@Sh,@sh2
WHILE@@FETCH_STATUS=0
begin
if@wh2=1
begin
iflen(@sh)>0
begin
if@qty>dbo.CH_InquireQty(@ItemCode,@DocDate,@filler,@sh)
begin
set@ch_getvalue=@LineNum+1
gotoendlabel
end
end
else
begin
set@ch_getvalue=@LineNum+1
gotoendlabel
end
end
if@wh1=1and((@sh2isnull)orlen(@sh2)=0)
begin
set@ch_getvalue=@LineNum+1
gotoendlabel
end
fetchnextfromnew_doc
into@LineNum,@DocDate,@filler,@qty,@WhsCode,@ItemCode,@wh1,@wh2,@Sh,@sh2
end
closenew_doc
DEALLOCATEnew_doc
end
endlabel:
return@ch_getvalue
END
第七步:
打开系统的存储过程:
SBO_SP_TransactionNotification,把下面蓝色字体的部分插入存储过程中,并运行:
---以下为库位管理所需代码,如果有其它限定条件,则把此段代码放在其它限定条件之后!
----
declare@ch_getvalueint
if(@object_type='20'or@object_type='18'or@object_type='59'or@object_type='16'or@object_type='14'or@object_type='67'
or@object_type='15'or@object_type='13'or@object_type='60'or@object_type='21'or@object_type='19')and@transaction_type='A'
begin
set@ch_getvalue=dbo.CH_CheckError(@object_type,@list_of_cols_val_tab_del)
if@ch_getvalue>0
begin
set@error=1
set@error_message=N'第'+cast(@ch_getvalueasnvarchar(20))+N'行必需填写有效的库位信息,该仓库需要库位管理,或该库位已经无余额!
'
end
if@error=0
execCH_AddShInfo@object_type,@list_of_cols_val_tab_del
end
---以上为库位管理所需代码-------------------------------------------------------------
第八步:
准备一些报表,供客户查询有关的库位信息。
(把蓝色字体部分复制到查询管理器中)
1、查询物料存放库位实时信息:
declare@intasint
declare@itemasnvarchar(20)
declare@whasnvarchar(8)
select@int=count(*)frompdn1t2wheret2.itemcode='[%0]'andt2.whscode='[%1]'
set@item='[%0]'
set@wh='[%1]'
SELECTOWHS.WhsCodeas仓库代码,OWHS.WhsNameas仓库名称,derivedtbl_1.ItemCodeas物料号,OITM.ItemNameas物料名称,derivedtbl_1.WhShelfAS库位,derivedtbl_1.qtyAS结存数量
FROM(SELECTt0.Warehouse,t0.ItemCode,t1.WhShelf,SUM(t0.InQty-t0.OutQty)ASqty
FROM[@CHSH]ASt1RIGHTOUTERJOIN
OINMASt0ONt1.TransNum=t0.TransNum
WHERE(t0.TransNumIN
(SELECTDISTINCTTransNum
FROM[@CHSH]))
GROUPBYt0.Warehouse,t0.ItemCode,t1.WhShelf
HAVING(t0.ItemCode=@itemOR@item='')AND(t0.Warehouse=@whor@wh='')AND(SUM(t0.InQty-t0.OutQty)>0))ASderivedtbl_1LEFTOUTERJOIN
OWHSONderivedtbl_1.Warehouse=OWHS.WhsCodeLEFTOUTERJOIN
OITMONderivedtbl_1.ItemCode=OITM.ItemCode
2、物料库位过账明细清单
declare@intasint
declare@itemasnvarchar(20)
declare@whasnvarchar(8)
declare@dte1asdatetime
declare@dte2asdatetime
declare@shasnvarchar(20)
select@int=count(*)frompdn1t4wheret4.itemcode='[%0]'andt4.whscode='[%1]'andt4.docdate>='[%2]'andt4.docdate<='[%3]'andt4.u_ch_frsh='[%4]'
set@item='[%0]'
set@wh='[%1]'
set@dte1='[%2]'
set@dte2='[%3]'
set@sh='[%4]'
SELECTt0.TransNumas序号,caset0.TransTypewhen20thenN'收货采购订单'when18thenN'应付发票'
when59thenN'库存生产收货'when16thenN'销售退货'when14thenN'应收贷项凭证'when15thenN'销售交货'
when13thenN'应收发票'when60thenN'库存生产发货'when21thenN'采购退货'when19thenN'应付贷项凭证'
when67thenN'库存转储'endas交易种类,t0.BASE_REFas单据号码,t1.WhsCodeas仓库代码,t1.WhsNameas仓库名称,t3.WhShelfAS库位,t0.InQtyas收货数量,t0.OutQtyas发出数量,t0.ItemCodeas物料代码,t0.Dscriptionas物料名称
FROMOWHSASt1RIGHTOUTERJOIN
OINMASt0ONt1.WhsCode=t0.WarehouseLEFTOUTERJOIN
[@CHSH]ASt3ONt0.TransNum=t3.TransNum
WHERE(t0.TransNumIN
(SELECTTransNum
FROM[@CHSH]AS[@CHSH_1]))AND(t0.ItemCode=@itemor@item='')AND(t0.DocDate>=@dte1or@dte1='')AND
(t0.DocDate<=@dte2or@dte2='')AND(t0.Warehouse=@whor@wh='')and(t3.WhShelf=@shor@sh='')
3、物料库位
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实现 方案 更新