IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qk_Verification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[qk_Verification]
GO
create proc qk_Verification
@list as nvarchar(max)--FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID | FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID....
as
begin
set nocount on
declare @VerGroup bigint--核销ID
declare @AmountSum money--本次核销的总金额(有时候,一个入库单要核销多个出库单,本变量保存所核销的多个出库单的总金额)
declare @sql nvarchar(max)
--1解析核销数据
create table #tempVer(FDInterID bigint, FDEntryID bigint, FSInterID bigint,FSEntryID bigint, FQty float, FAmount money, FDate datetime, FUserID bigint)
set @sql=REPLACE(@list,'|',' union all select ')
set @sql ='insert into #tempVer select ' +@sql
exec(@sql)
--2求核销总金额
select @AmountSum=isnull(SUM(famount),0) from #tempVer
begin try
begin tran
Update t_CheckLockTable Set FInterID=-FInterID--锁表,防止多人核销时公用一个核销ID的情况
SELECT @VerGroup=ISNULL(MAX(FVerGroup),0) FROM ICClientVer
set @VerGroup=@VerGroup+1
SET IDENTITY_INSERT ICCLIENTVER ON
INSERT INTO ICClientVer(FVerGroup,FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID)
select @VerGroup,FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID from #tempVer
Update t1 SET FCheckStatus=(CASE WHEN t1.FQty=t3.FQty THEN 1 ELSE 2 END)
From icstockbillEntry t1
Inner join icstockbill t2 on t1.FInterID=t2.FInterID
Inner join
(Select t4.FSInterID,t4.FSEntryID,Sum(t4.FQty) AS FQty From ICClientVer t4
Inner join (Select Distinct FSInterID,FSEntryID From ICClientVer Where FVerGroup=@VerGroup) t5 On t4.FSInterID=t5.FSInterID And t4.FSEntryID=t5.FSEntryID
Group by t4.FSInterID,t4.FSEntryID) t3 ON t1.FInterID=t3.FSInterID AND t1.FEntryID=t3.FSEntryID
WHERE t2.FTranType =28
Update t1 SET FCheckStatus=(CASE WHEN t1.FQty=t3.FQty THEN 1 ELSE 2 END)
From icstockbillEntry t1
Inner join icstockbill t2 on t1.FInterID=t2.FInterID
Inner join
(Select t4.FDInterID,t4.FDEntryID,Sum(t4.FQty) AS FQty From ICClientVer t4
Inner join (Select FDInterID,FDEntryID From ICClientVer Where FVerGroup=@VerGroup) t5 On t4.FDInterID=t5.FDInterID And t4.FDEntryID=t5.FDEntryID
Group by t4.FDInterID,t4.FDEntryID) t3 ON t1.FInterID=t3.FDInterID AND t1.FEntryID=t3.FDEntryID
WHERE t2.FTranType=5
CREATE TABLE #Temp (
FInterID INT,
FEntryID INT,
FMaterialCost DECIMAL(28,10))
INSERT INTO #Temp(FInterID,FEntryID,FMaterialCost)
select top 1 FDInterID,FDEntryID,@AmountSum from #tempVer
update t1 set t1.FMaterialCost=t2.FMaterialCost
FROM ICStockBillEntry t1
INNER JOIN #Temp t2 ON t1.FInterID=t2.FInterID AND t1.FEntryID=t2.FEntryID
update t1 set t1.FAmount=CASE WHEN (ISNULL(t3.FTranType,0) = 5 AND ISNULL(t3.FPurposeID,0) = 14191) THEN isnull(t1.FMaterialCost,0) ELSE isnull(t1.FProcessCost,0)+isnull(t1.FMaterialCost,0) END
FROM ICStockBillEntry t1
INNER JOIN #Temp t2 ON t1.FInterID=t2.FInterID AND t1.FEntryID=t2.FEntryID
INNER JOIN ICStockBill t3 ON t1.FInterID=t3.FInterID
update t1 set t1.FPrice=Round(t1.FAmount/t1.FQty,t2.FPriceDecimal)
,t1.FMaterialCostPrice=Round(t1.FMaterialCost/t1.FAuxQty,t2.FPriceDecimal)
, t1.FAuxPrice=Round(t1.FAmount/t1.FQty,t2.FPriceDecimal)*(isnull(t3.FCoefficient,1)+isnull(t3.FScale,0))
From IcStockbillEntry t1
Inner Join t_ICItem t2 On t1.FItemID=t2.FItemID
Inner Join t_MeasureUnit t3 On t3.FItemID=t1.FUnitID
INNER JOIN #Temp t4 ON t1.FInterID=t4.FInterID AND t1.FEntryID=t4.FEntryID
where t1.FQty<>0
DROP TABLE #Temp
SET IDENTITY_INSERT ICCLIENTVER OFF
commit
select fflag=1,fmsg='ok'
end try
begin catch
rollback
SET IDENTITY_INSERT ICCLIENTVER OFF
select fflag=-1,fmsg= ERROR_MESSAGE()
end catch
set nocount off
end
-------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qk_getVerOutBill]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[qk_getVerOutBill]
GO
create proc qk_getVerOutBill
@FOrderInterID bigint
,@FOrderEntryID int
as
begin
set nocount on
SELECT u1.FDetailID AS FListEntryID,0 AS FSel,t14.FName AS FPlanVchTplName,t15.FName AS FActualVchTplName,v1.FPlanVchTplID,v1.FActualVchTplID,v1.FVchInterID,v1.FTranType,v1.FInterID,u1.FEntryID,t4.FNumber AS FSupplyNumber,v1.Fdate,
case when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end AS FCheck,case when v1.FCancellation=1 then 'Y' else '' end AS FCancellation,t4.FName AS FSupplyIDName,
v1.FBillNo,t632.FName AS FPurposeID,tVMI.FName AS FIsVMI,tSup.FName AS FEntrySupplyName,t8.FName AS FDCStockIDName,t13.FShortNumber AS FItemIDName,
t13.FNumber AS FFullNumber,t13.Fname AS FItemName,t13.Fmodel AS FItemModel,t16.FName AS FUnitIDName,u1.FBatchNo,u1.Fauxqty,u1.Fauxprice,
u1.Famount,u1.FNote,v1.Fnote AS FNeed,t9.FName AS FSManagerIDName,t10.FName AS FFManagerIDName,t11.FName AS FuserName,
t24.FName AS FCheckerName,CASE WHEN v1.FVchInterID>0 THEN 'Y' WHEN v1.FVchInterID<0 THEN 'Y' ELSE '' END AS FVoucherStatus,
(SELECT (SELECT FName FROM t_VoucherGroup WHERE FGroupID=t_Voucher.FGroupID)+'-'+CONVERT(Varchar(30),FNumber)
FROM t_Voucher WHERE FVoucherid=v1.FVchInterID) AS FVoucherNumber,CASE WHEN v1.FHookInterID>0 THEN 'Y' WHEN v1.FHookInterID<0 THEN 'Y' ELSE '' END AS FHookStatus,u1.FOrderBillNo,u1.FSourceBillNo,
t70.FName AS FSourceTranType,Case u1.FCheckStatus when 1 then 'Y' when 2 then 'P' else 'N' end AS FArapStatus
,v1.FCheckDate, (CASE t103.FName WHEN '*' THEN '' ELSE t103.FName END) AS FAuxPropIDName,t103.FNumber AS FAuxPropIDNumber,t13.FQtyDecimal,
t13.FPriceDecimal,t30.FName AS FBaseUnitID,u1.FQty AS FBaseQty,u1.FQtyMust,u1.FAuxPlanPrice,u1.FPlanAmount
,Case WHEN t13.FStoreUnitID=0 THEN '' Else t500.FName end AS FCUUnitName
,Case WHEN t13.FStoreUnitID=0 THEN '' Else u1.FQty/t500.FCoefficient end AS FCUUnitQty,u1.FAuxQtyMust,
Case When v1.FCurrencyID is Null Or v1.FCurrencyID='' then (Select FScale From t_Currency Where FCurrencyID=1) else t503.FScale end AS FAmountDecimal,
(CASE t510.FName WHEN '*' THEN '' ELSE t510.FName END) AS FSPName,u1.FKFPeriod,u1.FKFDate,u1.FPeriodDate,
case when (v1.FROB <> 1) then 'Y' else '' end AS FRedFlag,u1.FMapName,u1.FMapNumber,t8.FNumber AS FDCStockIDNumber,t512.FName AS FSecUnitName,
u1.FSecCoefficient,u1.FSecQty,t621.FName AS FManageType,t123.FName AS FPlanMode,u1.FMTONo,t550.FName AS FRelateBrID,v1.FPOOrdBillNo,
t560.FName AS FBrID,CASE WHEN v1.FTranStatus=1 THEN 'Y' ELSE '' END AS FTranStatus,TStockBill.FBillNo AS FInStockID,
case when u1.FRowClosed=1 then 'Y' else '' end AS FRowClosed,u1.FPositionNO,u1.FItemSize,u1.FItemSuite,
CASE WHEN u1.FClosing=1 THEN 'Y' ELSE '' END AS FClosing,v1.FPrintCount,t1107.FName AS FHeadSelfB0836,
t1108.FName AS FEntrySelfB0850,u1.FOrderInterID,u1.FOrderEntryID,t200.FMRPClosed as ICMOStatus,t200.FItemID as FCastItemID,IDENTITY(INT) AS FRow,
u1.FItemID,u1.FPrice,u1.FQty-isNull(k1.FQty,0) as FPreQty,0.0 as FCurQty
,u1.FAmount-isNull(k1.FAmount,0) as FPreAmount,0.0 as FCurAmount into #temp
FROM ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
left outer join t_Supplier t4 on v1.FSupplyID=t4.FItemID
left outer join t_Stock t8 on u1.FDCStockID=t8.FItemID
left outer join t_Emp t9 on v1.FSManagerID=t9.FItemID
left outer join t_Emp t10 on v1.FFManagerID=t10.FItemID
left outer join t_User t11 on v1.FBillerID=t11.FUserID
Inner Join t_ICItem t13 on u1.FItemID=t13.FItemID
left outer join t_MeasureUnit t16 on u1.FUnitID=t16.FItemID
left outer join t_User t24 on v1.Fcheckerid=t24.FUserID
left outer join t_MeasureUnit t30 on t13.FUnitID=t30.FMeasureUnitID
left outer join v_ICTransType t70 on u1.FSourceTranType=t70.FID
left outer join ICVoucherTpl t14 on v1.FPlanVchTplID=t14.FInterID
left outer join ICVoucherTpl t15 on v1.FActualVchTplID=t15.FInterID
left outer join t_AuxItem t103 on u1.FAuxPropID=t103.FItemid
left outer join t_MeasureUnit t500 on t13.FStoreUnitID=t500.FItemID
left outer join t_Currency t503 on v1.FCurrencyID=t503.FCurrencyID
left outer join t_StockPlace t510 on u1.FDCSPID=t510.FSPID
left outer join t_MeasureUnit t512 on t13.FSecUnitID=t512.FItemID
left outer join t_BaseBondedManageType t621 on v1.FManageType=t621.FID
Inner Join t_SubMessage t123 on u1.FPlanMode=t123.FInterID
left outer join t_SonCompany t550 on v1.FRelateBrID=t550.FItemID
left outer join t_SonCompany t560 on v1.FBrID=t560.FItemID
left outer join t_Submessage t632 on v1.FPurposeID=t632.FInterID
left outer join ICStockBill TStockBill on u1.FInStockID=TStockBill.FInterID
left outer join t_SubMessage tVMI on u1.FIsVMI=tVMI.FInterID
left outer join t_Supplier tSup on u1.FEntrySupply=tSup.FItemID
left outer join t_SubMessage t1107 on v1.FHeadSelfB0836=t1107.FInterID
left outer join t_SubMessage t1108 on u1.FEntrySelfB0850=t1108.FInterID
left Join
(SELECT FSInterID,FSEntryID,Sum(FQty) as FQty,Sum(FAmount) as FAmount
FROM ICClientVer Group By FSInterID,FSEntryID) k1
ON (v1.FInterID=k1.FSInterID AND u1.FEntryID=k1.FSEntryID)
LEFT JOIN ICSubContractEntry t200 ON u1.FOrderInterID = t200.FInterID AND u1.FOrderEntryID = t200.FEntryID
WHERE 1=1 AND v1.FTranType=28 And (v1.FStatus>=1 AND u1.FRowClosed<>1) And v1.FHookInterID=0
--And v1.FDate<'2016/04/01'
and u1.FQty<>IsNull(k1.FQty,0) AND ISNull(u1.FOrderInterID,0)=@FOrderInterID AND ISNULL(u1.FOrderEntryID,0)=@FOrderEntryID And v1.FTranType=28
order by v1.FRob,v1.FDate ,v1.FBillNo,u1.FEntryID
select * from #temp
set nocount off
end
----
insert into t_MenuToolBar ( FToolID,FName,FCaption,FCaption_CHT,FCaption_EN,FImageName,FToolTip,FToolTip_CHT,FToolTip_EN,FControlType,FVisible,FEnable,FChecked,FShortCut,FCBList,FCBList_CHT,FCBList_EN,FCBStyle,FCBWidth,FIndex,FToolCaption,FToolCaption_CHT,FToolCaption_EN)
values (10002,'FGDMenuBatchVeri','批量核销','批量核销','批量核销','39','批量核销','批量核销','批量核销',0,0,1,0,0,'','','',0,0,0,'批量核销','批量核销','批量核销')
--执行下面的代码才能在菜单上看到我们需要的效果,要不然,菜单是隐藏状态的(如果里面有"|V",则只能在后面加菜单项),可支持多个自定义菜单
--iclisttemplate.fbilltemplateid=ictransactiontype.fid
--H:ModifyQty,PlanConfirmed,UnPlanConfirmed,OrderAffirm,ViewRefuseInfo,OrderRefuse,OrderAppend,ViewContract,HookBill,UnHookBill,ReHookBill,UnReHookBill,Union,MakeDown,ReMakeDown,Complete,ReComplete,MakeMaterialGet,MakeLowerBills,CheckBOM,ViewMaterialDiff,ViewMaterial,LockStock,UnLockStock,Diminution,UnDiminution,ATPQuery,LockQuery|V:ItemConsume,ItemConsumeChange,SubsVer,UndoSubsVer,VerType|FModule:1089|FModelDetail:3
--begin tran
Update IclistTemplate set FLogicStr='H:ModifyQty,PlanConfirmed,UnPlanConfirmed,OrderAffirm,ViewRefuseInfo,OrderRefuse,OrderAppend,ViewContract,HookBill,UnHookBill,ReHookBill,UnReHookBill,Union,MakeDown,ReMakeDown,Complete,ReComplete,MakeMaterialGet,MakeLowerBills,CheckBOM,ViewMaterialDiff,ViewMaterial,LockStock,UnLockStock,Diminution,UnDiminution,ATPQuery,LockQuery|V:ItemConsume,ItemConsumeChange,SubsVer,UndoSubsVer,VerType,FGDMenuBatchVeri|FModule:1089|FModelDetail:3'
where FMenuID =82 and FID=5
--commit
--加到菜单头上,可以不用的
--select * from t_BandToolMapping where fid=82 and ftoolid=10002--外购入库
--delete from t_BandToolMapping where fid=82 and ftoolid=10002
--select b.fname,fcaption,a.*--该语句反应外购入库单的序时薄菜单信息
--from t_BandToolMapping a inner join t_MenuToolBar b on a.ftoolid=b.ftoolid
--where fid=82 --and fcaption='序事簿菜单'--外购入口单叙事薄菜单
--begin tran
--update t_BandToolMapping set fbandid=2,fsubbandid=0,findex=17 where fid=82 and ftoolid=10002
--commit
--FBandID 是菜单的位置,1表示放到1级栏目下,2表示放在文件下面,3表示放在编辑下面,4表示放在查看下面,5表示放在格式下面,53表示放在工具栏上
--其他信息可以根据查询出来的数据进行判断
--insert into t_BandToolMapping(FID,FBandID,FToolID,FSubBandID,FIndex,FComName,FBeginGroup)
--values(82,1,10002,6,6,'|ICMOListBill.ListBill',0)
----可以加到多个位置
----加到文件菜单下面
--insert into t_BandToolMapping(FID,FBandID,FToolID,FSubBandID,FIndex,FComName,FBeginGroup)
--values(82,2,10002,0,17,'|ICMOListBill.ListBill',0) -
--加到工具栏下面 select * from t_BandToolMapping where fid=82 and fbandid=53 order by findex
insert into t_BandToolMapping(FID,FBandID,FToolID,FSubBandID,FIndex,FComName,FBeginGroup)
values(82,53,10002,0,192,'|GDBatchVerification.List',0)