脚本涉及的SQL方法: 1)拆BOM; 2)PO待交货数量分配; 3)MOQ的数量分配; 以下为SQL脚本: -------------------------------------------------------------------------- -- Author |Joe -- Date |2010-05-21 -- | -------------------------------------------------------------------------- IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#FC')) Drop Table #FC IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#MPS')) Drop Table #MPS IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#MaterialDetails')) Drop Table #MaterialDetails IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#result')) Drop Table #result IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#calcStock')) Drop Table #calcStock IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#podeliveryDetails')) Drop Table #podeliveryDetails if exists (select [name] from tempdb.dbo.sysobjects where id=object_id('tempdb..#materialflow')) drop table #materialflow if exists (select [name] from tempdb.dbo.sysobjects where id=object_id('tempdb..#vendor')) drop table #vendor if exists (select [name] from tempdb.dbo.sysobjects where id=object_id('tempdb..#required')) drop table #required -- qr2 parameter -------------------------------------- declare @NoFC bit declare @SODueDate bit set @NoFC= :SelNoForecastRiskyOrder set @soduedate= :SelSODueDate -------------------------------------- create table #MaterialDetails (fldSOType varchar(3) collate Chinese_Taiwan_Stroke_CI_AS, fldSalesOrderNo varchar(15) collate Chinese_Taiwan_Stroke_CI_AS, fldProductCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldMasterProductionScheduleNo varchar(15) collate Chinese_Taiwan_Stroke_CI_AS, fldManufacturingOrderNo varchar(15) collate Chinese_Taiwan_Stroke_CI_AS, fldMaterialCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldStartDate datetime, fldRequiredQty numeric(18,6)) create table #result (fldMaterialCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldFullName varchar(200) collate Chinese_Taiwan_Stroke_CI_AS, fldBasicUOMCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldLastCost numeric(18,6), RO numeric(18,6), FC numeric(18,6), ESB numeric(18,6), SO numeric(18,6), PoBalance numeric(18,6), fldType varchar(20), fldCalcStock numeric(18,6), fldSafelyQty numeric(18,6), fldMonth0 numeric(18,6), fldMonth1 numeric(18,6), fldMonth2 numeric(18,6), fldMonth3 numeric(18,6), fldMonth4 numeric(18,6), fldMonth5 numeric(18,6), fldLastVendor varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldMPQ numeric(18,6), fldLeadTime int, fldMaterialFlow numeric(18,6) ) --------------------------------------- if @NoFC = 0 begin Select --distinct cast((case left(a.fldsalesorderno,2) when 'ro' then 'RO' else 'FC' end) as varchar(3)) as fldSoType, b.fldsalesorderno, b.flditemcode, b.fldqty as fldSOqty, c.flddeliverydate, c.flddeliveryqty Into #FC From tblsalesorder a Left join tblsalesorderdetails b On a.fldsalesorderno = b.fldsalesorderno Left join tblsalesorderdeliverydetails c On b.fldsalesorderno = c.fldsalesorderno and b.flditemcode = c.flditemcode and b.fldseqno = c.fldseqno Where c.flddeliverydate >= getdate() and a.fldtype = 1 and b.fldqty > 5 and ( a.fldcomplete <> 1 and a.fldmanualsettle <>1) -- bom breakdown ------------------------------------------------------ if object_id('tempdb..#ProductList') is not null drop table #ProductList if object_id('tempdb..#BomBreak') is not null drop table #BomBreak if object_id('tempdb..#Injection') is not null drop table #Injection create table #BomBreak( fldProductCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldBomCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, fldNextCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS, --fldUsage numeric(18,6), fldUsageW numeric(18,6), fldLevelNumber int) -- input product for breakdown create table #ProductList(fldItemCode varchar(20) collate Chinese_Taiwan_Stroke_CI_AS,fldQty int) insert into #ProductList select distinct fldItemCode,1 from #FC -- injection select A.flditemcode, A.fldProductItemCode, B.fldMaterialCode, c.fldQuantityCycle, --'fldUsage' = A.fldGrossWeight / A.fldQty 'fldUsageW' = A.fldGrossWeight / A.fldQty * (100 + B.fldEngineerWastage ) / 100.00 into #Injection from tblInjectionMouldingProduceItemDetails A left join tblInjectionMouldingMaterialDetails B on A.flditemcode = B.flditemcode left join tblInjectionMoulding C on C.flditemcode = A.flditemcode where a.fldItemType <> 2 and not exists(select 1 from tblInjectionMouldingProduceItemDetails b where b.fldProductItemCode = a.fldProductItemCode and a.fldItemCode < b.fldItemCode) insert into #BomBreak select c.fldItemCode, a.fldBomCode, a.fldNextCode, --cast(c.fldQty * a.fldRequiredQty / b.fldQuantityCycle as numeric(18,6)), cast(c.fldQty * a.fldRequiredQty * (100 + a.fldengineerwastage)/ 100.00 / b.fldQuantityCycle as numeric(18,6)), 1 from #ProductList c left join tblbomdetails a on c.fldItemCode = a.fldBomCode left join tblbom b on a.fldBomCode = b.fldBomCode where a.fldbomcode is not null order by a.fldBomCode,a.fldNextCode declare @i int set @i = 1 while exists(select 1 from #BomBreak where fldLevelNumber = @i) begin insert into #BomBreak select c.fldProductCode, a.fldBomCode, a.fldNextCode, --cast(c.fldUsage * a.fldRequiredQty / b.fldQuantityCycle as numeric(18,6)), cast(c.fldUsageW * a.fldRequiredQty * (100 + a.fldengineerwastage)/ 100.00 / b.fldQuantityCycle as numeric(18,6)), @i + 1 from #BomBreak c join tblbomdetails a on c.fldNextCode = a.fldBomCode collate Chinese_Taiwan_Stroke_CI_AS left join tblbom b on a.fldBomCode = b.fldBomCode collate Chinese_Taiwan_Stroke_CI_AS where c.fldLevelNumber = @i and a.fldbomcode is not null order by a.fldBomCode,a.fldNextCode set @i = @i + 1 end -- add part of injection Insert into #BomBreak Select distinct a.fldProductCode, b.fldProductItemCode, b.fldMaterialCode, --b.fldUsage, b.fldUsageW, a.fldLevelNumber + 1 from #BomBreak a join #Injection b on a.fldNextCode = b.fldProductItemCode collate Chinese_Taiwan_Stroke_CI_AS --end of bom breakdown -------------------------------------- -- FC material required ------------------------------------------------- insert into #MaterialDetails select a.fldSOType, a.fldSalesOrderNo, a.flditemcode as fldProductCode, '', '', b.fldnextcode, a.flddeliverydate, a.flddeliveryqty * b.fldUsageW from #fc a left join #BomBreak b on a.flditemcode=b.fldproductcode where b.fldnextcode not in (select fldbomcode from #BomBreak) and b.fldnextcode is not null end--no fc ro -- mps ---------------------- select a.fldmasterproductionscheduleno, b.fldsalesorderno, a.fldItemCode, case when @soduedate = 0 and c.fldAssemblyStartdate is not null then dateadd(wk,-1,c.fldAssemblyStartdate) else dateadd(wk,-3,b.flddeliverydate) end as flddeliverydate into #mps from tblmasterproductionschedule a left join (select fldmasterproductionscheduleno,fldsalesorderno,min(flddeliverydate) as flddeliverydate from tblmasterproductionscheduledetails where flddeliverydate is not null group by fldmasterproductionscheduleno,fldsalesorderno) b on a.fldmasterproductionscheduleno = b.fldmasterproductionscheduleno left join (select fldmasterproductionscheduleno,fldestimatestartdate as fldAssemblyStartdate from tblmasterproductionschedulestagedetails kk where kk.fldstagecode='assembly' and not exists (select 1 from tblmasterproductionschedulestagedetails tt where tt.fldmasterproductionscheduleno=kk.fldmasterproductionscheduleno and tt.fldstagecode='assembly' and kk.fldseqno > tt.fldseqno)) c on a.fldmasterproductionscheduleno = c.fldmasterproductionscheduleno left join tblsalesorder d on b.fldsalesorderno = d.fldsalesorderno where a.fldcomplete = 0 and a.fldstatus >= 3 and d.fldcomplete = 0 and d.fldmanualsettle = 0 and b.flddeliverydate is not null and a.fldmasterproductionscheduleno in (select fldmasterproductionscheduleno from tblmanufacturingorder) -- and (@noesb=0 or (@noesb=1 and left(f.fldsalesorderno,3)<>'esb')) -- mps material required details ------------------------------------------------------------------- insert into #MaterialDetails select case when left(a.fldsalesorderno,3) = 'esb' then 'ESB' else 'SO' end, a.fldsalesorderno, a.fldItemCode, a.fldmasterproductionscheduleno, b.fldManufacturingorderno, c.fldItemCode, a.flddeliverydate, c.fldrequiredqty + c.fldCompensateQty + fldReturnWithIssuedQty - c.fldissuedqty from #mps a left join tblmanufacturingorder b on a.fldmasterproductionscheduleno = b.fldmasterproductionscheduleno left join tblmanufacturingordermaterialdetails c on b.fldManufacturingorderno = c.fldManufacturingorderno left join tblItem d on c.flditemcode = d.flditemcode where b.fldfinish = 0 and b.fldcomplete = 0 and d.fldsource <> 2 and c.fldrequiredqty + c.fldCompensateQty + fldReturnWithIssuedQty - c.fldissuedqty > 0 --====================================== -- check all material details -- select * from #MaterialDetails order by fldMaterialCode,fldstartdate --====================================== -- calc stock --------------------------------------------- select a.flditemcode, sum(a.fldopenqty + a.fldinqty - a.fldoutqty + a.fldadjustmentqty) as fldcalcstock into #calcstock from tblstockbalance a left join tbllocation b on a.fldlocationcode = b.fldlocationcode where not exists(select 1 from tblstockbalance b where a.flditemcode = b.flditemcode and a.fldlocationcode = b.fldlocationcode and a.fldbincode = b.fldbincode and a.fldfromdate < b.fldfromdate) and ((b.fldcalcmaterialneed = 1 or a.fldlocationcode = 'iqc01') and a.fldbincode <> 'cr03') and a.fldopenqty + a.fldinqty - a.fldoutqty + a.fldadjustmentqty > 0.0001 and a.flditemcode collate Chinese_Taiwan_Stroke_CI_AS in (select fldmaterialcode from #MaterialDetails) group by a.flditemcode -- required group ------------------------------------------------------------------- select -- fldMaterialCode, RO, FC, ESB, SO, fldRMonth0, fldRMonth1, fldRMonth2, fldRMonth3, fldRMonth4, fldRMonth5, fldSMonth0, fldSMonth1, fldSMonth2, fldSMonth3, fldSMonth4, fldSMonth5 into #required from (select fldmaterialcode, sum(case fldsotype when 'RO' then fldRequiredQty else 0 end) as RO, sum(case fldsotype when 'FC' then fldRequiredQty else 0 end) as FC, sum(case fldsotype when 'ESB' then fldRequiredQty else 0 end) as ESB, sum(case fldsotype when 'SO' then fldRequiredQty else 0 end) as SO, sum(case when fldsotype = 'RO' and convert(varchar(7),fldstartdate,120) <= convert(varchar(7),getdate(),120) then fldRequiredQty else 0 end) as fldRMonth0, sum(case when fldsotype = 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,1,getdate()),120) then fldRequiredQty else 0 end) as fldRMonth1, sum(case when fldsotype = 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,2,getdate()),120) then fldRequiredQty else 0 end) as fldRMonth2, sum(case when fldsotype = 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,3,getdate()),120) then fldRequiredQty else 0 end) as fldRMonth3, sum(case when fldsotype = 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,4,getdate()),120) then fldRequiredQty else 0 end) as fldRMonth4, sum(case when fldsotype = 'RO' and convert(varchar(7),fldstartdate,120) >= convert(varchar(7),dateadd(mm,5,getdate()),120) then fldRequiredQty else 0 end) as fldRMonth5, sum(case when fldsotype <> 'RO' and convert(varchar(7),fldstartdate,120) <= convert(varchar(7),getdate(),120) then fldRequiredQty else 0 end) as fldSMonth0, sum(case when fldsotype <> 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,1,getdate()),120) then fldRequiredQty else 0 end) as fldSMonth1, sum(case when fldsotype <> 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,2,getdate()),120) then fldRequiredQty else 0 end) as fldSMonth2, sum(case when fldsotype <> 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,3,getdate()),120) then fldRequiredQty else 0 end) as fldSMonth3, sum(case when fldsotype <> 'RO' and convert(varchar(7),fldstartdate,120) = convert(varchar(7),dateadd(mm,4,getdate()),120) then fldRequiredQty else 0 end) as fldSMonth4, sum(case when fldsotype <> 'RO' and convert(varchar(7),fldstartdate,120) >= convert(varchar(7),dateadd(mm,5,getdate()),120) then fldRequiredQty else 0 end) as fldSMonth5 from #materialdetails group by fldmaterialcode) a left join tblItem b on a.fldmaterialcode = b.flditemcode left join #calcstock c on a.fldmaterialcode = c.flditemcode where c.fldcalcstock < (b.fldSafetyQty + a.RO + a.FC + a.SO + a.ESB) --ro insert into #result select a.fldMaterialCode, '', '', 0, a.RO, cast(0 as numeric(18,6)) as FC, cast(0 as numeric(18,6)) as ESB, cast(0 as numeric(18,6)) as SO, cast(0 as numeric(18,6)) as PoBalance, cast('1.RoRequired' as varchar(20)) as fldType, cast(0 as numeric(18,6)) as fldCalcStock, cast(0 as numeric(18,6)) as fldSafelyQty, a.fldRMonth0 as fldMonth0, a.fldRMonth1 as fldMonth1, a.fldRMonth2 as fldMonth2, a.fldRMonth3 as fldMonth3, a.fldRMonth4 as fldMonth4, a.fldRMonth5 as fldMonth5, cast('' as varchar(20)) as fldLastVendor, cast(0 as numeric(18,6)) as fldMPQ, 0, cast(0 as numeric(18,6)) as fldMaterialFlow from #required a left join tblItem b on a.fldmaterialcode = b.flditemcode where @nofc = 0 -- so insert into #result select a.fldMaterialCode, '', '', 0, 0 as RO, a.FC, a.ESB, a.SO, 0, '2.SoRequired', 0, b.fldSafetyQty, a.fldSMonth0, a.fldSMonth1, a.fldSMonth2, a.fldSMonth3, a.fldSMonth4, a.fldSMonth5, '', 0, 0, 0 from #required a left join tblItem b on a.fldmaterialcode = b.flditemcode -- PO delivery --------------------------------------------- select a.fldItemCode, c.fldPurchaseOrderNo, b.fldSeqNo, b.fldQty, b.fldqty + b.fldreturnwithdeliveryqty - b.fldgoodsreceivedqty as fldOutstandingQty, a.fldReceivedDate as fldDeliveryDate, a.fldReceivedQty as fldDeliveryQty into #podeliveryDetails from tblPurchaseOrderDeliveryDetails a left join tblPurchaseOrderDetails b on a.fldPurchaseOrderNo = b.fldPurchaseOrderNo and a.fldSeqNo = b.fldSeqNo and a.fldItemCode = b.fldItemCode left join tblPurchaseOrder c on a.fldPurchaseOrderNo = c.fldPurchaseOrderNo where c.fldPOKind = 1 and b.fldSeqNo is not null and c.fldStatus >= 3 and c.fldstatus <> 5 and c.fldManualSettle = 0 and c.fldComplete = 0 and b.fldqty + b.fldreturnwithdeliveryqty - b.fldgoodsreceivedqty > 0 and a.fldItemCode in (select fldmaterialcode from #result) -- BPO delivery ---------------------------------------------- insert into #podeliveryDetails select a.fldItemCode, c.fldPurchaseOrderNo, f.fldSeqNo, f.fldQty, f.fldqty + f.fldreturnwithdeliveryqty - f.fldgoodsreceivedqty, a.fldReceivedDate, a.fldReceivedQty from tblPurchaseOrderDeliveryInstructionDetails a left join tblPurchaseOrderDeliveryInstruction b on a.fldReferenceNo = b.fldReferenceNo and a.fldPurchaseOrderNo = b.fldPurchaseOrderNo left join tblPurchaseOrderDetails f on a.fldPurchaseOrderNo = f.fldPurchaseOrderNo and a.fldItemCode = f.fldItemCode and a.fldPOSeqNo = f.fldSeqNo left join tblPurchaseOrder c on a.fldPurchaseOrderNo = c.fldPurchaseOrderNo where c.fldPOKind = 2 and c.fldStatus >= 3 and c.fldstatus <> 5 and c.fldManualSettle = 0 and c.fldComplete = 0 and f.fldqty + f.fldreturnwithdeliveryqty - f.fldgoodsreceivedqty > 0 and a.fldReceivedQty > 0 and f.fldSeqNo is not null and a.fldItemCode in (select fldmaterialcode from #result) -- BPO no any release ------------------------------------------ insert into #podeliveryDetails select f.fldItemCode, f.fldPurchaseOrderNo, f.fldSeqNo, f.fldQty, f.fldqty + f.fldreturnwithdeliveryqty - f.fldgoodsreceivedqty , dateadd(mm,6,getdate()), f.fldqty + f.fldreturnwithdeliveryqty - f.fldgoodsreceivedqty from tblPurchaseOrderDetails f left join tblPurchaseOrder c on f.fldPurchaseOrderNo = c.fldPurchaseOrderNo where c.fldPOKind = 2 and not exists(select 1 from #podeliveryDetails a where f.fldItemCode = a.fldItemCode and f.fldPurchaseOrderNo = a.fldPurchaseOrderNo and f.fldSeqNo = a.fldSeqNo) and c.fldStatus >= 3 and c.fldstatus <> 5 and c.fldManualSettle = 0 and c.fldComplete = 0 and f.fldqty + f.fldreturnwithdeliveryqty - f.fldgoodsreceivedqty > 0 and f.fldSeqNo is not null and f.fldItemCode in (select fldmaterialcode from #result) -- no delivery date ------------------------------- insert into #podeliveryDetails select a.fldItemCode, a.fldPurchaseOrderNo, a.fldSeqNo, a.fldQty, a.fldOutstandingQty, dateadd(mm,6,getdate()), a.fldNoDateQty from (select flditemcode,fldPurchaseOrderNo,fldSeqNo,fldQty,fldOutstandingQty, fldqty - sum(flddeliveryqty) as fldNoDateQty from #podeliveryDetails group by flditemcode,fldPurchaseOrderNo,fldSeqNo,fldqty,fldOutstandingQty having fldqty > sum(flddeliveryqty)) a --====================================== -- check all PO delivery details -- select * from #podeliveryDetails order by flditemcode --====================================== -- outstanding qty allocate update a set fldDeliveryQty = case when a.fldoutstandingqty < (SELECT SUM(fldDeliveryQty) FROM #podeliveryDetails WHERE flditemcode=a.flditemcode and fldPurchaseorderno=a.fldPurchaseorderno and fldDeliverydate >= a.fldDeliverydate ) THEN case when exists(SELECT 1 FROM #podeliveryDetails WHERE flditemcode=a.flditemcode and fldPurchaseorderno=a.fldPurchaseorderno and fldDeliverydate > a.fldDeliverydate) then a.fldoutstandingqty - (SELECT SUM(fldDeliveryQty) FROM #podeliveryDetails WHERE flditemcode=a.flditemcode and fldPurchaseorderno=a.fldPurchaseorderno and fldDeliverydate > a.fldDeliverydate) else a.fldoutstandingqty end Else a.fldDeliveryQty END from #podeliveryDetails a delete from #podeliveryDetails where fldDeliveryQty <= 0 -- material flow --------------------------------------------- select MID.fldMaterialissuedno, MID.flditemcode, mid.fldissuedqty into #materialflow from tblmaterialissueddetails MID left join tblmaterialissued MI on MID.fldmaterialissuedno = MI.fldmaterialissuedno left join tblManufacturingOrder MO on mo.fldmanufacturingorderno = mi.fldmanufacturingorderno left join tblMasterProductionScheduleDetails mpsd on mpsd.fldMasterProductionScheduleNo= MO.fldMasterProductionScheduleNo where MI.fldstatus = 3 AND MI.fldmaterialissueddate >=dateadd(mm,-6,getdate()) and mid.flditemcode in (select fldmaterialcode from #result) insert into #materialflow select b.fldGoodsTransferNo, b.fldItemCode, b.fldTransferQty from dbo.tblGoodsTransferOut a left join dbo.tblGoodsTransferOutStockDetails b on a.fldGoodsTransferNo=b.fldGoodsTransferNo where (fldFromBinCode='CH01' or fldFromBinCode='INSZ') and (fldToBinCode='RMLADJ' or fldToBinCode='RMLREW' or fldToBinCode='MS02') and a.fldGoodsTransferDate >=dateadd(mm,-6,getdate()) and b.flditemcode in (select fldmaterialcode from #result) -- last vender ---------------------------------------------- select b.flditemcode, a.fldvendorcode, a.fldcreatetime, isnull(c.fldordermultipleqty,0) as fldordermultipleqty, isnull(c.fldLeadTime,0) as fldLeadTime into #vendor from tblpurchaseorder a left join tblpurchaseorderdetails b on a.fldpurchaseorderno = b.fldpurchaseorderno left join tblItemPurchaseVendorDetails c on b.flditemCode = c.fldItemCode and a.fldvendorcode = c.fldVendorCode where a.fldstatus>=2 and a.fldstatus<>5 and a.fldmanualsettle=0 and b.flditemcode in (select fldmaterialcode from #result) -- delivery group ---------------------------------------------- insert into #result select a.fldMaterialCode, '', '', 0, 0, 0, 0, 0, isnull(b.PoBalance,0), '3.PoAllocate', c.fldCalcStock, 0, isnull(b.fldMonth0,0), isnull(b.fldMonth1,0), isnull(b.fldMonth2,0), isnull(b.fldMonth3,0), isnull(b.fldMonth4,0), isnull(b.fldMonth5,0), '', 0, 0, 0 from (select distinct fldMaterialCode from #result) a left join (select flditemcode, sum(fldDeliveryQty) as PoBalance, sum(case when convert(varchar(7),flddeliverydate,120) <= convert(varchar(7),getdate(),120) then fldDeliveryQty else 0 end) as fldMonth0, sum(case when convert(varchar(7),flddeliverydate,120) = convert(varchar(7),dateadd(mm,1,getdate()),120) then fldDeliveryQty else 0 end) as fldMonth1, sum(case when convert(varchar(7),flddeliverydate,120) = convert(varchar(7),dateadd(mm,2,getdate()),120) then fldDeliveryQty else 0 end) as fldMonth2, sum(case when convert(varchar(7),flddeliverydate,120) = convert(varchar(7),dateadd(mm,3,getdate()),120) then fldDeliveryQty else 0 end) as fldMonth3, sum(case when convert(varchar(7),flddeliverydate,120) = convert(varchar(7),dateadd(mm,4,getdate()),120) then fldDeliveryQty else 0 end) as fldMonth4, sum(case when convert(varchar(7),flddeliverydate,120) >= convert(varchar(7),dateadd(mm,5,getdate()),120) then fldDeliveryQty else 0 end) as fldMonth5 from #podeliveryDetails group by flditemcode) b on a.fldMaterialCode = b.fldItemCode left join #calcstock c on a.fldMaterialCode = c.flditemcode insert into #result select a.fldMaterialCode, d.fldfullname11 + (case rtrim(isnull(d.fldfullname21,'')) when '' then '' else char(13) + d.fldfullname21 end) + (case rtrim(isnull(d.fldfullname31,'')) when '' then '' else char(13) + d.fldfullname31 end) + (case rtrim(isnull(d.fldfullname41,'')) when '' then '' else char(13) + d.fldfullname41 end) + (case rtrim(isnull(d.fldfullname51,'')) when '' then '' else char(13) + d.fldfullname51 end), d.fldBasicUOMCode, d.fldLastCost, b.RO, b.FC, b.ESB, b.SO, a.PoBalance, '4.PoSuggest', a.fldCalcStock, d.fldSafetyQty, /* case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 <= 0 then -(a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 > 0 then a.fldCalcStock + a.fldMonth0 - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 else 0 end) + (a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1) <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 > 0 then a.fldCalcStock + a.fldMonth0 - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 else 0 end) + (a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1)) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 else 0 end) + (a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2) <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 else 0 end) + (a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2)) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 else 0 end) + (a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3) <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 else 0 end) + (a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3)) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 else 0 end) + (a.fldMonth4 /*- b.fldRMonth4*/ - b.fldSMonth4) <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 else 0 end) + (a.fldMonth4 /*- b.fldRMonth4*/ - b.fldSMonth4)) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 + a.fldMonth4 /*- b.fldRMonth4*/ - b.fldSMonth4 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 + a.fldMonth4 /*- b.fldRMonth4*/ - b.fldSMonth4 else 0 end) + (a.fldMonth5 /*- b.fldRMonth5*/ - b.fldSMonth5) <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 + a.fldMonth4 /*- b.fldRMonth4*/ - b.fldSMonth4 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 + a.fldMonth1 /*- b.fldRMonth1*/ - b.fldSMonth1 + a.fldMonth2 /*- b.fldRMonth2*/ - b.fldSMonth2 + a.fldMonth3 /*- b.fldRMonth3*/ - b.fldSMonth3 + a.fldMonth4 /*- b.fldRMonth4*/ - b.fldSMonth4 else 0 end) + (a.fldMonth5 /*- b.fldRMonth5*/ - b.fldSMonth5)) else 0 end, */ case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 <= 0 then -(a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 > 0 then a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 else 0 end)- b.fldRMonth1 - b.fldSMonth1 <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 > 0 then a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 else 0 end)- b.fldRMonth1 - b.fldSMonth1) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 > 0 then a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 else 0 end) - b.fldRMonth2 - b.fldSMonth2 <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 > 0 then a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 else 0 end) - b.fldRMonth2 - b.fldSMonth2) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 else 0 end) - b.fldRMonth3 - b.fldSMonth3 <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 else 0 end) - b.fldRMonth3 - b.fldSMonth3) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 else 0 end) - b.fldRMonth4 - b.fldSMonth4 <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 else 0 end) - b.fldRMonth4 - b.fldSMonth4) else 0 end, case when (case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 - b.fldRMonth4 - b.fldSMonth4 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 - b.fldRMonth4 - b.fldSMonth4 else 0 end) - b.fldRMonth5 - b.fldSMonth5 <= 0 then -((case when a.fldCalcStock - d.fldSafetyQty - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 - b.fldRMonth4 - b.fldSMonth4 > 0 then a.fldCalcStock - d.fldSafetyQty + a.fldMonth0 - b.fldRMonth0 - b.fldSMonth0 - b.fldRMonth1 - b.fldSMonth1 - b.fldRMonth2 - b.fldSMonth2 - b.fldRMonth3 - b.fldSMonth3 - b.fldRMonth4 - b.fldSMonth4 else 0 end) - b.fldRMonth5 - b.fldSMonth5) else 0 end, f.fldvendorcode, case when f.fldordermultipleqty <> 0 then f.fldordermultipleqty else c.fldordermultipleqty end, case when f.fldLeadTime <> 0 then f.fldLeadTime else c.fldLeadTime end, e.fldMaterialFlow / 6 as fldAverageMaterialFlow from (select * from #result where fldtype = '3.PoAllocate') a left join #required b on a.fldmaterialcode = b.fldmaterialcode left join tblItemPurchaseVendordetails c on a.fldmaterialcode = c.flditemcode and c.flddefault = 1 left join tblItem d on a.fldmaterialcode = d.flditemcode left join (select flditemcode,sum(fldissuedqty) as fldMaterialFlow from #MaterialFlow group by flditemcode) e on a.fldmaterialcode = e.flditemcode left join (select distinct a.flditemcode, a.fldvendorcode, a.fldordermultipleqty, a.fldLeadTime from #vendor a where not exists(select 1 from #vendor b where a.flditemcode = b.flditemcode and a.fldcreatetime < b.fldcreatetime)) f on a.fldMaterialCode = f.flditemcode select fldMaterialCode as MaterialCode, fldFullName as FullName, fldBasicUOMCode as UOMCode, fldLastCost as LastCost, RO, FC, ESB, SO, PoBalance, fldType as Type, fldCalcStock as CalcStock, fldSafelyQty as SafelyQty, case when fldMPQ > 0 then case when fldmonth0 > 0 then (floor(fldmonth0 / fldMPQ) + 1) * fldMPQ else 0 end else fldmonth0 end as Month0, case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 - (case when fldmonth0 > 0 then (floor(fldmonth0 / fldMPQ) + 1) * fldMPQ else 0 end)) > 0 then (floor((fldmonth0 + fldmonth1 - (case when fldmonth0 > 0 then (floor(fldmonth0/ fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth1 end as Month1, case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 - (case when fldmonth0 + fldMonth1 > 0 then (floor((fldmonth0 + fldMonth1)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0 then (floor((fldmonth0 + fldmonth1 + fldMonth2 - (case when fldmonth0 + fldMonth1 > 0 then (floor((fldmonth0 + fldMonth1) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth2 end as Month2, case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 - (case when fldmonth0 + fldMonth1 + fldMonth2 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0 then (floor((fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 - (case when fldmonth0 + fldMonth1 + fldMonth2 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth3 end as Month3, case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0 then (floor((fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth4 end as Month4, case when fldMPQ > 0 then case when (fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 + fldMonth5 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4)/ fldMPQ) + 1) * fldMPQ else 0 end)) > 0 then (floor((fldmonth0 + fldmonth1 + fldMonth2 + fldMonth3 + fldMonth4 + fldMonth5 - (case when fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4 > 0 then (floor((fldmonth0 + fldMonth1 + fldMonth2 + fldMonth3 + fldMonth4) / fldMPQ) + 1) * fldMPQ else 0 end)) / fldMPQ) + 1 ) * fldMPQ else 0 end else fldmonth5 end as Month5, fldLastVendor as LastVendor, fldMPQ as MPQ, fldLeadTime as LeadTime, fldMaterialFlow as AverageMaterialFlow, convert(varchar(7),getdate(),120) + (case @nofc when 1 then ' NoForecastRiskyOrder' else '' end) as Remarks from #result order by fldmaterialcode,fldtype IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#FC')) Drop Table #FC IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#MPS')) Drop Table #MPS IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#MaterialDetails')) Drop Table #MaterialDetails IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#result')) Drop Table #result IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#calcStock')) Drop Table #calcStock IF Exists (Select [Name] From Tempdb.dbo.Sysobjects Where Id=Object_id('Tempdb..#podeliveryDetails')) Drop Table #podeliveryDetails if exists (select [name] from tempdb.dbo.sysobjects where id=object_id('tempdb..#materialflow')) drop table #materialflow if exists (select [name] from tempdb.dbo.sysobjects where id=object_id('tempdb..#vendor')) drop table #vendor if exists (select [name] from tempdb.dbo.sysobjects where id=object_id('tempdb..#required')) drop table #required