|
问题：做销售单的时候录入会员卡号提示“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 |
0 条回帖
