金蝶常用功能常见故障解决方法

一、金蝶即时库存查询报错不能将值NULL插入列‘FProperty‘，表‘tempdb.dbo.#TempInventory

 使用SELECT * from ICInventory where FStockID not in (select FItemID from t_Stock) 

二、反结账与反过账

反结账

反过账

财务会计-->凭证处理-->右键-->反过账

发票删除

发票序时簿-->反勾稽-->反审批-->删除

三、修改外购入库单、外购发票以及地磅的单价

--SELECT * FROM ICStockBillEntry WHERE FInterID in (SELECT FInterID FROM ICStockBill WHERE FTranType=1 AND SUBSTRING(convert(varchar(10),FDate,120),1,7) = '2022-10' and FBillNo IN  ())

declare @rightprice decimal(20,2)
set @rightprice = 55

declare @error int 

IF EXISTS(Select 1 From Sysobjects Where Name='#tempLMSFBillnoforPriceChanging') DROP table #tempLMSFBillnoforPriceChanging

CREATE TABLE #tempLMSFBillnoforPriceChanging (LMS单据编号 nvarchar(100))

Insert into #tempLMSFBillnoforPriceChanging(LMS单据编号) values 
('CG012074')
set @error=0

  --定义游标

declare demo_cursor cursor

for (SELECT LMS单据编号 from #tempLMSFBillnoforPriceChanging)

  --打开游标--

  	open demo_cursor

  --开始循环游标变量--
	declare @LMSFBillno varchar(50)

  	fetch next from demo_cursor into @LMSFBillno

  	while @@FETCH_STATUS = 0  --返回被 FETCH语句执行的最后游标的状态--

    begin    

/*以下是修改入库单价格以及金额*/

			--USE [KingdeeNow]  
			--UPDATE ICStockBillEntry SET FAuxPrice=@rightprice,FAmount=FQty*@rightprice, FAllHookAmount = FQty*@rightprice,FQtyInvoice=0,FAllHookQTY=0,FCurrentHookAmount=0,FCurrentHookQTY=0,FAuxQtyInvoice=0,FCheckAmount=0
			--WHERE FInterID =(SELECT FInterID FROM ICStockBill 	WHERE FTranType=1 AND FHeadSelfA0141 =@LMSFBillno)

/*以下是修改入库单生成的发票单价以及金额*/

			USE [KingdeeNow]
			UPDATE ICPurchaseEntry SET FPrice =  @rightprice, FAuxPrice =  @rightprice, FTaxPrice= @rightprice, FAuxTaxPrice= @rightprice,FPriceDiscount= @rightprice, FAuxPriceDiscount= @rightprice, FAmount =  @rightprice*FQty, FStdAmount =  @rightprice*FQty,FAmountincludetax =  @rightprice*FQty, FStdAmountincludetax =  @rightprice*FQty, FAllHookAmount =  @rightprice*FQty, FStdAllHookAmount =  @rightprice*FQty WHERE FSourceBillNo = (SELECT FBillNo FROM ICStockBill WHERE FHeadSelfA0141 = @LMSFBillno)

/*以下是修改地磅系统的单价及金额*/

			--USE [LMS]

			--UPDATE QH_PurchCalculateContract SET  FPrice=@rightprice, FDeductAmount=FDeductNum*@rightprice WHERE FID =	(SELECT FID FROM QH_PurchCalculate WHERE FBillNo =@LMSFBillno				)

      		set @error= @error + @@ERROR  --记录每次运行sql后是否正确，0正确

      		fetch next from demo_cursor into @LMSFBillno  --转到下一个游标，没有会死循环

    end  

  close demo_cursor --关闭游标

  deallocate demo_cursor  --释放游标

drop table #tempLMSFBillnoforPriceChanging

四、金蝶按LMS单号查询采购发票以及采购凭证的方法

SELECT 
KINGDEENOW.dbo.ICStockBill.FRelateInvoiceID	关联发票号
,KINGDEENOW.dbo.ICStockBill.FVchInterID	凭证内码
,KINGDEENOW.dbo.ICStockBill.FHeadSelfA0141
,KINGDEENOW.dbo.ICStockBill.FBillNo
,KINGDEENOW.dbo.ICPurchase.FBillNo
,CONCAT(KingdeeNow.dBO.t_Voucher.FYear,'-',KingdeeNow.dBO.t_Voucher.FPeriod,'-',cast(KingdeeNow.dBO.t_Voucher.FNumber as varchar)) 凭证号
FROM KINGDEENOW.dbo.ICStockBill

LEFT JOIN
KINGDEENOW.dbo.ICStockBillEntry
on KINGDEENOW.dbo.ICStockBill.FinterID =  KINGDEENOW.dbo.ICStockBillEntry.FinterID
LEFT JOIN
KINGDEENOW.dbo.ICPurchase
ON 
KINGDEENOW.dbo.ICPurchase.FInterID = KINGDEENOW.dbo.ICStockBill.FRelateInvoiceID
LEFT join 
KingdeeNow.dbo.t_Voucher
on KINGDEENOW.dbo.ICStockBill.FVchInterID = KingdeeNow.dbo.t_Voucher.FVoucherID


WHERE KINGDEENOW.dbo.ICStockBill.FTranType = 1 AND KINGDEENOW.dbo.ICStockBill.FSelTranType = 0
AND 
KINGDEENOW.dbo.ICStockBill.FHeadSelfA0141 in
('CG016552','CG016564','CG016570','CG016572','CG016610','CG017000','CG017026','CG017027','CG017042','CG017060','CG017061','CG017548','CG017549','CG017550','CG017733','CG017734','CG017736','CG017754','CG017768','CG018607','CG018608','CG018638','CG018683','CG018700','CG018701')

输出

五、地磅金蝶系统中计重以及单价差异提取

declare @startdate datetime
set @startdate = '2022-01-01'
SELECT
	LMS.DBO.QH_PurchCalculate.FSecondTime 出厂时间,
    LMS.DBO.QH_PurchCalculate.FBillNo LMS编号
	,KINGDEENOW.dbo.ICStockBill.FBillNo 金蝶编号 
	,KINGDEENOW.dbo.ICPurchase.FBillNo 外购入库发票号
	,CONCAT(KingdeeNow.dBO.t_Voucher.FYear,'-',KingdeeNow.dBO.t_Voucher.FPeriod,'-',cast(KingdeeNow.dBO.t_Voucher.FNumber as varchar)) 凭证号

	,convert(nvarchar(64),LMS.DBO.QH_PurchCalculate.FCarNo) 车辆,
	convert(nvarchar(64),LMS.DBO.QH_Supplier.FName) AS [供应商名称],
	LMS.DBO.QH_Supplier.FBillNo AS [供应商编码],
    convert(nvarchar(64),LMS.DBO.QH_RawMaterial.FName) AS [物料名称],
	LMS.DBO.QH_PurchCalculateContract.FPrice AS 地磅单价,
	KingdeeNow.dbo.ICStockBillEntry.FAuxPrice AS 金蝶入库单价,
	--KINGDEENOW.dbo.ICPurchaseEntry.fprice 金蝶发票单价,
    CASE 
        WHEN LMS.DBO.QH_RawMaterial.FName LIKE '%自提%'
        THEN LMS.DBO.QH_PurchCalculate.FNumPlan
        ELSE LMS.DBO.QH_PurchCalculate.FNetWeight
    END AS [地磅计重],
	KingdeeNow.dbo.ICStockBillEntry.FQTY AS 金蝶入库计重
	--,KINGDEENOW.dbo.ICPurchaseEntry.FQTY AS 金蝶发票计重


    --CASE 
    --    WHEN LMS.DBO.QH_RawMaterial.FName LIKE '%自提%'
    --    THEN LMS.DBO.QH_PurchCalculate.FNumPlan*LMS.DBO.QH_PurchCalculateContract.FPrice
    --    ELSE LMS.DBO.QH_PurchCalculate.FNetWeight*LMS.DBO.QH_PurchCalculateContract.FPrice
    --END AS [金额]
	--,KINGDEENOW.dbo.ICStockBill.FRelateInvoiceID	关联发票号
	--,KINGDEENOW.dbo.ICStockBill.FVchInterID	凭证内码
	--,KINGDEENOW.dbo.ICStockBill.FHeadSelfA0141

FROM    LMS.DBO.QH_Supplier
	LEFT JOIN LMS.DBO.QH_PurchCalculate ON LMS.DBO.QH_Supplier.FID = LMS.DBO.QH_PurchCalculate.FSupplierID
	LEFT JOIN LMS.DBO.QH_PurchCalculateContract on LMS.DBO.QH_PurchCalculateContract.FID = LMS.DBO.QH_PurchCalculate.FID 
	LEFT JOIN LMS.DBO.QH_RawMaterial ON LMS.DBO.QH_RawMaterial.FID = LMS.DBO.QH_PurchCalculate.FItemID
	LEFT JOIN KINGDEENOW.dbo.ICStockBill ON LMS.DBO.QH_PurchCalculate.FBillNo= KINGDEENOW.dbo.ICStockBill.FHeadSelfA0141
	LEFT JOIN KINGDEENOW.dbo.ICStockBillEntry on KINGDEENOW.dbo.ICStockBill.FinterID =  KINGDEENOW.dbo.ICStockBillEntry.FinterID
	LEFT JOIN KINGDEENOW.dbo.ICPurchase ON KINGDEENOW.dbo.ICPurchase.FInterID = KINGDEENOW.dbo.ICStockBill.FRelateInvoiceID
	LEFT join KingdeeNow.dbo.t_Voucher on KINGDEENOW.dbo.ICStockBill.FVchInterID = KingdeeNow.dbo.t_Voucher.FVoucherID
	--INNER JOIN KINGDEENOW.dbo.ICPurchaseEntry ON KINGDEENOW.dbo.ICPurchase.FInterID= KINGDEENOW.dbo.ICPurchaseEntry.FInterID


WHERE
    LMS.DBO.QH_PurchCalculate.FCancel <>1 AND 
    LMS.DBO.QH_PurchCalculate.FDeleted <> 1 AND 
    LMS.DBO.QH_Supplier.FBillNo NOT IN ('00000') AND
	LMS.DBO.QH_PurchCalculate.FType <> 1 AND
    LMS.DBO.QH_PurchCalculate.FSecondTime >= @startdate AND 
	(
	LMS.DBO.QH_PurchCalculateContract.FPrice <> KingdeeNow.dbo.ICStockBillEntry.FAuxPrice OR 

	CASE  WHEN LMS.DBO.QH_RawMaterial.FName LIKE '%自提%'   THEN LMS.DBO.QH_PurchCalculate.FNumPlan     ELSE LMS.DBO.QH_PurchCalculate.FNetWeight END   <> KingdeeNow.dbo.ICStockBillEntry.FQTY 
	)

ORDER BY 
LMS.DBO.QH_PurchCalculate.FSecondTime DESC

六、计提折旧生成的凭证如何删除？