金蝶SQL二次开发(3)

二次开发项目

? 相对BOM的财务数据稽查

CREATE PROCEDURE [dbo].[A_ICBOM_temp] AS BEGIN

SET NOCOUNT ON

delete from ZRSC_temp_BOM0 delete from ZRSC_temp_BOM1

--一级物料

SELECT ICBomChild.FInterID, ICBomChild.FItemID as

FSubItemID,ICBomChild.FQty,(100+isnull(ICBomChild.fentryselfz0142,0)) as FSCRAP into #temp

FROM ICBomChild,t_ICItem t2

WHERe ICBomChild.FItemID=t2.FItemID CREATE INDEX #temp ON #Temp(FinterID)

--select count(*) from ICBom where FUseStatus = 1072 and FStatus = 1 and FItemID in ( select FSubItemID from #temp)

declare @count as int

select @count =count(*) from ICBom where FUseStatus = 1072 and FStatus = 1 and FItemID in ( select FSubItemID from #temp) --明细物料

- 11 – 4/16/2013

二次开发项目

while @count >0 begin

insert into ZRSC_temp_BOM0 select * from #temp where FSubItemID in (select Fitemid from ICBom where FUseStatus = 1072 and FStatus = 1 ) --select * from ZRSC_temp_BOM0 where fsubitemid=3704 insert into #temp select

t1.finterid,t3.fitemid,t1.Fqty*t3.Fqty,t1.FSCRAP*(100+isnull(t3.fentryselfz0142,0))/100 from #temp t1, ICBOM t2 ,ICBomChild t3 where t1.FSubItemID=t2.FItemid and t2.finterid=t3.finterid and t2.FUseStatus = 1072 and t2.FStatus = 1 --select * from #temp where finterid=7891

---delete from #temp where FSubItemid in (select FSubItemid from ZRSC_temp_BOM0 where fitemid=#temp.fsubitemid or finterid=#temp.finterid)

delete from #temp where exists (select a.FSubItemid from ZRSC_temp_BOM0 a,icbom b where a.finterid=b.finterid and (b.fitemid=#temp.fsubitemid or a.finterid=#temp.finterid)

and a.FSubItemid=#temp.FSubItemid)

--alter by long for BOM多级展开成本不准（外发，印刷塑件等级以上的BOM） select @count =count(*) from ICBom where FUseStatus = 1072 and FStatus = 1 and FItemID in ( select FSubItemID from #temp) end

insert into ZRSC_temp_BOM1

select a.*,isnull(b.fplanprice,0) as fprice,

isnull(b.fplanprice,0)*a.fqty*a.FSCRAP/100 as Famount, isnull(c.fprice,0)*1.17 as fprice1,

isnull(c.fprice,0)*1.17*a.fqty*a.FSCRAP/100 as Famount1 , isnull(d.fprice,0) as fprice2,

isnull(d.fprice,0)*a.fqty*a.FSCRAP/100 as Famount2, isnull(b.Fstandardcost,0) as fprice3,

isnull(b.Fstandardcost,0)*a.fqty*a.FSCRAP/100 as Famount3 from

#temp a left join

t_icitem b on a.FSubItemID=b.fitemid left join

(select fitemid, sum(fbegbal)/sum(fbegqty) as Fprice from icbal where Fyear in(select Fvalue from t_SystemProfile where FKEY='CurrentYear' and FCategory='IC' )

and FPeriod in(select Fvalue from t_SystemProfile where FKEY='CurrentPeriod' and FCategory='IC' )

group by fitemid having sum(fbegqty)<>0) c on a.FSubItemID=c.fitemid left join

(select fitemid,max(fprice) as fprice FROM t_SupplyEntry t1

where t1.fused=1 and t1.fdisabledate>getdate() and not Exists(select *

- 12 –

4/16/2013

二次开发项目

from t_SupplyEntry t2 where t2.fquotetime>t1.fquotetime and t2.fused=1 and t2.fdisabledate>getdate() and t2.fitemid=t1.fitemid )

group by fitemid ) d on a.FSubItemID=d.fitemid --加权平均加要含税的单价

update ZRSC_temp_BOM1 set Fprice1=a.fprice*1.17,Famount1 =isnull(a.fprice,0)*fqty*1.17*FSCRAP/100 from ZRSC_temp_BOM1,

(select t2.fitemid,t2.fprice from icstockbill t1 ,icstockbillentry t2 where t1.finterid=t2.finterid and t1.ftrantype=1 and t2.fitemid<>36439 and Not Exists(Select * From icstockbill a1 ,icstockbillentry a2 where a1.finterid=a2.finterid and a2.fitemid=t2.fitemid and a1.ftrantype=1 And a1.fdate > t1.fdate ) ) a

where a.fitemid=ZRSC_temp_BOM1.FSubItemID and Fprice1=0

--加权平均没有时取最新报价

update ZRSC_temp_BOM1 set Fprice1=Fprice2,Famount1 =Famount2 from ZRSC_temp_BOM1 where Fprice1=0

update ZRSC_temp_BOM1 set Famount=fprice*fqty*FSCRAP/100 from ZRSC_temp_BOM1 where Fprice is not null end

采购申请检验单

create proc sp_lyh_hq_puricqcpjbtest @fdates datetime,@fdatee datetime , @fitemnumbers varchar(255),@fitemnumbere varchar(255), @fsupnumbers varchar(255),@fsupnumbere varchar(255) as

SET NOCOUNT ON --创建单据

CREATE TABLE #QCData( Fdate datetime, FType INT, FItemID INT, FSupplyID INT,

FQCBillInterID INT,--检验单的内码

FPOInstockInterID INT DEFAULT 0,--申请单 FPOInstockEntryID INT DEFAULT 0,--申请单分录 FUnitID INT DEFAULT 0, FBSICQCQty DECIMAL(28,10), FSendUpQty DECIMAL(28,10),

FPOInstockQty DECIMAL(28,10) DEFAULT 0, FNotPassCount INT DEFAULT 0--不合格批次 )

- 13 – 4/16/2013

二次开发项目

CREATE TABLE #Result( fdate datetime, FItemID INT,

FName NVARCHAR(100), FSupplyID INT,

FBSPOInStockQty DECIMAL(28,10) DEFAULT 0, FPOInStockQty DECIMAL(28,10) DEFAULT 0, FCUPOInStockQty DECIMAL(28,10) DEFAULT 0, FBSICQCQty DECIMAL(28,10) DEFAULT 0, FICQCQty DECIMAL(28,10) DEFAULT 0, FCUICQCQty DECIMAL(28,10) DEFAULT 0, FCheckCount INT DEFAULT 0, FNotPassCount INT DEFAULT 0, FSumSort INT DEFAULT 100, FSort INT DEFAULT 1 )

INSERT INTO

#QCData(Fdate,FType,FItemID,FSupplyID,FQCBillInterID,FPOInstockInterID,FPOInstockEntryID,FBSICQCQty,FSendUpQty,FPOInstockQty,FNotPassCount) SELECT

q.fdate,0,q.FItemID,q.FSupplyID,q.FInterID,q.FInStockInterID,q.FSerialID,ISNULL(m.FCoefficient,1)*tp.FPassAuxQty,ISNULL(m.FCoefficient,1)*tp.FSendUpAuxQty,0,

CASE t.FInspectionLevel WHEN 353--抽检

THEN CASE q.FResult WHEN 287 THEN 1 ELSE 0 END WHEN 351--全检

THEN CASE WHEN 2*q.FNotPassQty>=q.FCheckQty THEN 1 ELSE 0 END--全检时，取检验单上（不合格数量≥检验数量/2）时,不合格批数+1 ELSE 0 END FROM ICQCBill q INNER JOIN ( SELECT

qc.FItemID,qc.FSupplyID,qc.FInterID,SUM((qc.FPassQty+ISNULL(con.FConPassAuxQty,0))) AS FPassAuxQty,SUM(qc.FSendUpQty) AS FSendUpAuxQty FROM ICQCBill qc LEFT JOIN (

SELECT v.FItemID,v.FCheckBillID AS

FQCBillInterID,v.FUnitID,SUM(u.FDefectQty) AS FConPassAuxQty FROM QMRejectEntry u

INNER JOIN QMReject v ON v.FID=u.FID

WHERE v.FCheckerID>0 AND u.FDefectHandlingID=1077

GROUP BY v.FItemID,v.FCheckBillID,v.FUnitID--统计检验单对应不良品处理单的让步接收数的合计

- 14 –

4/16/2013

二次开发项目

) con ON con.FItemID=qc.FItemID AND con.FQCBillInterID=qc.FInterID

INNER JOIN t_Supplier s ON s.FItemID=qc.FSupplyID

WHERE qc.FCheckerID>0 AND qc.FTranType=711 AND qc.FResult<>13556 AND qc.FDate>='2009-03-01' AND qc.FDate<='2009-03-30'--检验结果是保留的不统计 AND s.FNumber>='0' AND s.FNumber<='z'

GROUP BY qc.FItemID,qc.FSupplyID,qc.FInterID--统计某供应商 供某种 货物的合格数报检数

) tp ON tp.FInterID=q.FInterID

INNER JOIN t_ICItem t ON t.FItemID=q.FItemID

INNER JOIN t_MeaSureUnit m ON m.FMeasureUnitID=q.FUnitID WHERE q.FTranType=711

AND t.FNumber>='0' AND t.FNumber<='z'--@fitemnumbere

- 15 – 4/16/2013

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库金蝶SQL二次开发(3)在线全文阅读。