问题：做销售单的时候录入会员卡号提示“VIP卡适用范围不正确”
解决方法：先选择仓库再输入会员卡号就可以了。
问题：新服装普及版零售单不能取价格
解决方法：1.做好软件的数据备份
2.执行下列补丁
补丁内容：SET QUOTED_IDENTIFIER off
GO
SET ANSI_NULLS off
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FZGetPrice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FZGetPrice]
GO
-- ********************************************************************************************
-- ||
-- || 过程名称：F_D_GetPrice
-- || 过程功能：读取商品的各种价格（零售价、进货价、促销价、折扣、折让等）录单时用
-- ||=========================================================================================
-- || 参数说明： 参数名称 类型 意义 输入输出
-- || -----------------------------------------------------------------------------
-- || @Ptypeid varchar(50), 商品编码
-- || @Ktypeid varchar(50), 仓库编号
-- || @Vipid varchar(18), vip号
-- || @Btypeid varchar(50), 往来单位
-- || @szBlockno varchar(20),--带批次商品时用
-- || @szProdate varchar(13),--带批次商品时用
-- || @SalePrice numeric(24,10) output,--销售单价
-- || @BuyPrice numeric(24,10) output,--进货单价
-- || @discount int output,--销售折扣
-- || @buydiscount int output,--进货折扣
-- || @convert numeric(24,10) output,--折让
-- || @MarketDiscount numeric(24,10) output, --商场扣率
-- || @retailPrice numeric(24,10) output--零售价
-- || @costprice numeric(24,10) output 成本价
-- || @recPrice numeric(24,10) output 最近进价
-- ||=========================================================================================
-- || 返回值： 值 说明
-- || ----------------------------------------------------------------------------------------
-- || 0 执行成功
-- || －1 执行失败
-- || －2 输入的商品不存在
-- || －3 该商品已经删除
-- ||==========================================================================================
CREATE PROCEDURE FZGetPrice
( @Ptypeid varchar(50),
@Branchid varchar(50),
@Ktypeid varchar(50),
@Vipid int,
@Btypeid varchar(50),
@szBlockno varchar(20),--带批次商品时用
@szProdate varchar(13),--带批次商品时用
@SalePrice numeric(24,10) output,--销售单价
@BuyPrice numeric(24,10) output,--进货单价
@discount numeric(24,10) output,--销售折扣
@buydiscount numeric(24,10) output,--进货折扣
@convert numeric(24,10) output,--折让
@MarketDiscount numeric(24,10) output,--商场扣率
@retailPrice numeric(24,10) output,--零售价
@costprice numeric(24,10) output,--成本价
@recPrice numeric(24,10) output,--最进进价
[email=--@VipPrice]--@VipPrice[/email] numeric(24,10) output,--会员价
@Vipdiscount numeric(24,10) output,
@CXPrice numeric(24,10) output,
@Vchtype int,
@integral varchar(1) ='0' output,--,
@colorid int =0,
@type TINYINT =0, --0:非零售单取价，1：零售单取价
@Tax NUMERIC(24,10)=0 output-- 商场专柜专用（税率）
)
AS
Declare @Vchcode int --促销设置索引id编号
Declare @billCompset int --商品组合促销设置0：促销价和促销折扣同时使用 1:不同时使用促销价有效2：不同时使用促销折扣有效
Declare @szVip1 int ,@szVip2 int [email=--@szVip1]--@szVip1[/email]会员价卡设置：1会员价优先，2促销价优先，@szVip2会员折扣卡设置：1:会员卡不参与，2:参与不同时使用，3:参与同时使用
Declare @ifVip bit
Declare @ifgz int--1:采用价格跟踪，0:不采用
Declare @ifgzDiscount int--1:采用折扣跟踪，0:不采用
Declare @Maxdate varchar(10),@MaxdateBuy varchar(10)
Declare @ifzjjj int --1:取最近进价
Declare @ifyssj int --1:取预设售价price1
Declare @projectID int
Declare @VchcodeFA int-- 促销方案单据号
Declare @Date varchar(10)
Declare @Time varchar(10)
Declare @PriceChoose varchar(15)
Declare @discountChoose varchar(15)
Declare @SQL varchar(1000)
Declare @VipDiscount1 numeric(24,10)
Declare @CardTypeID int --1:会员价卡，2：商品折扣卡
Declare @VipPrice1 numeric(24,10)
Declare @VipTotal numeric(24,10)--之前vip 卡的总消费金额
Declare @IfVipDiscount int
Declare @VipdiscountNew numeric(24,10)
Declare @CanuseVip bit
Declare @VipChoose varchar(100)
Declare @CardTypeID1 INT
declare @record BIT --判断记录是否存在
declare @iniover BIT --判断是否为期初
set @record=0
set @iniover=1
select @iniover =SubValue from sysdata where SubName='iniover'
Select @Date =Convert(char(10),getdate(),120)
Select @Time= Convert(varchar(10),getdate(),108)
Select @Convert=0
--判断
--读取vip的有关信息
--if left(@Btypeid,5)='00006' Select @Btypeid=@ktypeid
if @Vchtype=70
begin
select top 1 @costprice=price from dbo.GoodsstockBackup where [email=ktypeid=@Ktypeid]ktypeid=@Ktypeid[/email] and [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email]
goto Success00
end
if exists(Select * from VIPCards where [email=CardID=@VIpID]CardID=@VIpID[/email])
Begin
Select @VipTotal =a.AllConsume,@CardTypeID=b.type From VipCards a
left join VIPCardTypes b on a.CardTypeID=b.OrderId where [email=a.Cardid=@VipID]a.Cardid=@VipID[/email]
-- Select @CardTypeID=type From vipcardtypes where [email=orderid=@CardTypeID]orderid=@CardTypeID[/email]
Select @VipDiscount1=[percent] From VIPCardExpand where ExpandType=1 and LimiteUp>=@VipTotal
and LimiteDown0 and @VchType in(200,201,226,12,100)
Begin
-----折扣商品
select @canusevip=CanUseVip,@integral=integral from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where [email=ProjectId=@ProjectId]ProjectId=@ProjectId[/email] and Vchtypeid in (4,8))
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
if @CanUseVip=0 --vip折上折无效
Begin
if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
select @Vipdiscount=Vipdiscount from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where [email=ProjectId=@ProjectId]ProjectId=@ProjectId[/email] and Vchtypeid in (4,8))
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
select @discount=discount from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where [email=ProjectId=@ProjectId]ProjectId=@ProjectId[/email] and Vchtypeid in (4,8))
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
else if @CanUseVip=1
Begin
if @discount=null select @discount=100
if @vipdiscount in(0,100) or @vipdiscount =null
Begin
if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
select @Vipdiscount=Vipdiscount from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where [email=ProjectId=@ProjectId]ProjectId=@ProjectId[/email] and Vchtypeid in (4,8))
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
--if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
select @discount=discount from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where [email=ProjectId=@ProjectId]ProjectId=@ProjectId[/email] and Vchtypeid in (4,8))
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
------促销价商品
Begin
--print @CanUseVIp_CXJ
Declare @CanUseVIp_CXJ bit
Select @CanUseVIp_CXJ=CanUseVip,@integral=integral from DlySaleP_CXD where Vchcode in
(Select vchcode From DlysaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
if @CanUseVIp_CXJ=0--
Begin
if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
Select @Vipdiscount=VipDiscount from DlySaleP_CXD where Vchcode in
(Select vchcode From DlysaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
if @CXPrice=0 or @CXPrice =null--取促销价
Select @CXPrice=SalePromPrice from DlySaleP_CXD where Vchcode in
(Select vchcode From DlysaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
-------2007-03-06 add
if @CanUseVip_CXj=1
Begin
--此处应该vip的预设折扣
if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
Select @VipDiscount=price From ptypePrice where [email=ptypeid=@Ptypeid]ptypeid=@Ptypeid[/email] and [email=priceTypeid=@VipChoose]priceTypeid=@VipChoose[/email]
if @vipdiscount in(0,100) or @vipdiscount =null
Begin
if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
Select @Vipdiscount=VipDiscount from DlySaleP_CXD where Vchcode in
(Select vchcode From DlysaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
if @CXPrice=0 or @CXPrice =null
Select @CXPrice=SalePromPrice from DlySaleP_CXD where Vchcode in
(Select vchcode From DlysaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
-------2007-03-06 add
-- if @CanUseVIp_CXJ=null and @CanUseVip=1
-- Select @CXPrice=0
End
End
if @discount=null or @discount=0 or @discount=100
Begin
Select @discount =price From ptypePrice Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=priceTypeid=@discountChoose]priceTypeid=@discountChoose[/email]
-------2007-03-06 add
if (@discount=0 or @discount =null) and @VchType in(200,201,226,12,100)
Begin
if exists(Select CardTypeID From VipCards where [email=Cardid=@VipID]Cardid=@VipID[/email])
select @Vipdiscount=Vipdiscount from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
select @discount=discount from DlySalePromNo3 where DLySalePromID in
( Select vchcode From DlySaleProm where vchtypeid in (4,8)and [email=ProjectID=@ProjectId]ProjectID=@ProjectId[/email])
and Ptypeid [email==@Ptypeid]=@Ptypeid[/email] and [email=Colorid=@Colorid]Colorid=@Colorid[/email]
End
-------2007-03-06 add
ENd
Goto Getprice
End
Getprice: --取售价
Begin
if exists(Select * from Vipcards where [email=CardID=@Vipid]CardID=@Vipid[/email]) and @CardTypeID=1
begin
Select @SalePrice=@Vipprice1
if @ifgz=1 and @vchtype not in(25,26,27,28,30,31,50,51) --启用价格跟踪
Begin
if exists (Select * From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email]) --跟踪表中有取跟踪表中的最近销售折扣
Begin
Select @Maxdate= Max(Saledate) From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
if @type=0 --零售类单据不考虑价格折扣跟踪
begin
Select @SalePrice=SalePrice From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=Saledate=@Maxdate]Saledate=@Maxdate[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
end
Select @retailPrice=price From Ptypeprice where [email=Ptypeid=@ptypeid]Ptypeid=@ptypeid[/email] and pricetypeid='LSSJ1'
if @SalePrice=0 or @SalePrice=null
Begin
goto GetpriceChoose
End
end
else
Begin
if @SalePrice=0 or @SalePrice=null
goto GetpriceChoose
End
End
else if @ifgz1
Begin
goto GetpriceChoose
End
End
else
Begin
if @ifgz=1 and @vchtype not in(25,26,27,28,30,31,50,51)--启用价格跟踪
Begin
if exists (Select * From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email]) --跟踪表中有取跟踪表中的最近销售折扣
Begin
Select @Maxdate= Max(Saledate) From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
if @type=0
begin
Select @SalePrice=SalePrice From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=Saledate=@Maxdate]Saledate=@Maxdate[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
end
Select @retailPrice=price From Ptypeprice where [email=Ptypeid=@ptypeid]Ptypeid=@ptypeid[/email] and pricetypeid='LSSJ1'
if @SalePrice=0 or @SalePrice=null
Begin
goto GetpriceChoose
End
end
else
Begin
goto GetpriceChoose
End
End
else if @ifgz1
Begin
goto GetpriceChoose
End
End
Goto GetToother
End
GetpriceChoose:
Begin
--if @vchtype not in(25,26,27,28,30,31,50,51)
begin
--print @priceChoose
Select @SalePrice=price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and pricetypeid= @priceChoose
-- Print 'ok'
-- print @SalePrice
-- print @priceChoose
Select @RetailPrice=price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and pricetypeid='LSSJ1'
End
Goto GetToOther
End
--取进货价格
-----------------------取进货价格时不考虑促销的情况，判断是否启用”进货(退货)的单价从系统中读最近进价（进价折扣）“的配置，如果启用，则从price中取
-----------------------否则不取值
GetToOther:
Begin
if @ifgz=1 and @vchtype not in(25,26,27,28,30,31,50,51)
begin
Select @Maxdate= Max(Buydate) From PriceTrace Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
Select @Buyprice =CostPrice From PriceTrace Where [email=Buydate=@Maxdate]Buydate=@Maxdate[/email] and [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
Select @BuyDiscount=BuyDiscount From PriceTrace Where [email=Buydate=@Maxdate]Buydate=@Maxdate[/email] and [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=btypeid=@Btypeid]btypeid=@Btypeid[/email]
if @ifzjjj=1 and (@Buyprice=0 or @Buyprice=null)
Begin
Select @Buyprice=RecPrice1 From Ptype where [email=Typeid=@ptypeid]Typeid=@ptypeid[/email]
if @Buyprice=0 or @Buyprice=null
Select @Buyprice=Price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and [email=PriceTypeid=@pricechoose]PriceTypeid=@pricechoose[/email]
End
if @BuyDiscount=null or @BuyDiscount=0
Begin
Select @BuyDiscount=Price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and [email=pricetypeid=@discountchoose]pricetypeid=@discountchoose[/email]
End
End
else if @vchtype not in(25,26,27,28,30,31,50,51)
Begin
if @ifzjjj=1
Begin
Select @Buyprice=recprice1 From Ptype where [email=Typeid=@ptypeid]Typeid=@ptypeid[/email]
if @Buyprice=0 or @Buyprice=null
Select @Buyprice=price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and [email=pricetypeid=@pricechoose]pricetypeid=@pricechoose[/email]
end
else
Begin
Select @Buyprice=price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and [email=pricetypeid=@pricechoose]pricetypeid=@pricechoose[/email]
end
Select @BuyDiscount=price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and [email=pricetypeid=@discountchoose]pricetypeid=@discountchoose[/email]
End
ENd
--取成本价
Declare @costMode int
Select @CostMode=costmode From ptype where [email=typeid=@ptypeid]typeid=@ptypeid[/email]
if @iniover=1
begin
if exists(select 1 from goodsstocks where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email]) set @record=1
if @CostMode 3
Begin
if @colorid0
Begin
Select Top 1 @CostPrice =(Total/qty) From GoodsStocks
Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and qty0
and orderid in (Select g.orderid From GoodsstockDetail gg,Goodsstocks g
where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and gg.orderid=g.orderid and [email=gg.colorid=@colorid]gg.colorid=@colorid[/email])
order by orderid asc
End
else
Begin
Select top 1 @CostPrice =(Total/qty) From GoodsStocks
Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and qty 0 --and [email=jobnumber=@szBlockno]jobnumber=@szBlockno[/email] and [email=OutFactoryDate=@szProdate]OutFactoryDate=@szProdate[/email]
order by orderid asc
End
End
else
Select top 1 @CostPrice =(Total/qty) From GoodsStocks
Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and qty0
order by orderid asc
end
else
begin
if exists(select 1 from dbo.IniGoodsStocks where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email]) set @record=1
--if @Costprice=0 or @Costprice=null
--Begin
if @CostMode 3
Begin
if @colorid0
Select Top 1 @CostPrice =(Total/qty) From iniGoodsStocks
Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and qty0
and orderid in (Select g.orderid From iniGoodsDetail gg,iniGoodsStocks g
where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and gg.orderid=g.orderid and [email=gg.colorid=@colorid]gg.colorid=@colorid[/email])
order by orderid asc
else
Select top 1 @Costprice=(Total/qty) From iniGoodsStocks
Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and qty0 --and [email=jobnumber=@szBlockno]jobnumber=@szBlockno[/email] and [email=OutFactoryDate=@szProdate]OutFactoryDate=@szProdate[/email]
order by orderid asc
End
else
Select @Costprice=(Total/qty) From iniGoodsStocks
Where [email=ptypeid=@ptypeid]ptypeid=@ptypeid[/email] and [email=ktypeid=@ktypeid]ktypeid=@ktypeid[/email] and qty0
order by orderid asc
--end
END
if (@CostPrice=null or @CostPrice=0) and @record=0
Select @CostPrice=recprice From ptype where [email=typeid=@ptypeid]typeid=@ptypeid[/email]
Select @CostPrice=isnull(@CostPrice,0)
Select @recPrice=RecPrice From ptype where [email=typeid=@ptypeid]typeid=@ptypeid[/email]
Select @recPrice=isnull(@recPrice,0)
select @Tax=TaxRate from BtypeDistribute where [email=Btypeid=@Btypeid]Btypeid=@Btypeid[/email]
Select @MarketDiscount=p.price From BtypeDistribute b,ptypeprice p
where [email=Btypeid=@Btypeid]Btypeid=@Btypeid[/email] and p.PriceTypeid =b.MarketDiscountId and [email=p.ptypeid=@ptypeid]p.ptypeid=@ptypeid[/email]
IF @Vipprice10 AND @Vipprice1null and @CardTypeID1
Begin
Set @buyprice=@Vipprice1
End
Goto Success00
Success00:
if @RetailPrice =0 or @RetailPrice is null
Begin
Select @RetailPrice=price From Ptypeprice Where [email=Ptypeid=@Ptypeid]Ptypeid=@Ptypeid[/email] and pricetypeid='LSSJ1'
End
if @discount=0 set @discount=100
if @buydiscount=0 set @buydiscount=100
if @VipDiscount=0 set @VipDiscount=100
set @CXPRice=isnull(@CXPRice,0)
set @SalePrice=isnull(@SalePrice,0)
set @BuyPrice=isnull(@BuyPrice,0)
set @discount=isnull(@discount,100)--销售折扣
set @buydiscount=isnull(@buydiscount,100)--进货折扣
set @convert=isnull(@convert,0)
set @MarketDiscount=isnull(@MarketDiscount,0)
set @retailPrice=isnull(@retailPrice,0)
set @VipDiscount=isnull(@VipDiscount,100)
set @integral=isnull(@integral,0)
Return 0
Error00:
Return -1
Error01:
Return -2
Error02:
Return -3
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO |
