二次开发项目
? 相对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