MRP SQL

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

 

 

 

 


/****************************************************************
*                                                                                                                             *
*                           系統  :生產管理系統                                                                     *
*                           功能  :MRP計算                                                                           *
*                                                                                                                              *      
*                             時間  :2005-10-10                                                                   *
*                                                                                                                              *
****************************************************************/
--   Exec   Sp_RunningMRP  '2005/11/16','20051130004','',0,''
ALTER                             PROCEDURE  [dbo].[Sp_RunningMRP]
 @StartDateTime DateTime,
 @CalcuNO Varchar(15),
 @Runer Varchar(20),
 @Returnid Int output,
 @ErrMsg Varchar(100) Output
As
Begin Transaction

Declare   @MaxEndDate DateTime
Declare   @Count  Int
Declare   @Year    Int
Declare   @Month Int
Declare   @FirstDate Datetime

          Set   @Returnid = 0
          Set   @ErrMsg = ''

          Select   @Count = Count(*),@MaxEndDate = Max(EndDate)
          From   B_TimeCircle
          If  @Count = 0
          Begin
                      Set    @Returnid = 0
                      Set    @ErrMsg = '沒有進行計划展望維護!'
                      GoTo ExitLable                    
          End       
          Select   @Count = Count(*)
          From   B_TimeArea
          If  @Count = 0
          Begin
                      Set    @Returnid = 0
                      Set    @ErrMsg = '沒有進行計划周期維護!'
                      GoTo ExitLable                    
          End      

         If Month(@Startdatetime) = 1
         Begin
                     Set     @Year = Year(@StartDateTime) - 1
                     Set     @Month = 12
         End
         Else
         Begin
                    Set      @Year = Year(@StartDateTime)
                    Set      @Month = Month(@StartDateTime) - 1
        End
        Select   @FirstDate = Cast(Convert(Char(10), @Startdatetime, 121) As Datetime) - Day(@Startdatetime) + 1
--調入PO欠數  (#T1)
Select Itemno, Syspono,IsNull(Poqs,0)As Poqs,DelyDate
Into #TA1
From(
             Select AA.*,BB.Delyqty,IsNull(AA.Qty,0) - IsNull(BB.Delyqty,0) As Poqs  from
                        (Select m.Syspono,ItemNo,Sum(qty) As qty, 
                          Case When D.DelyDate Is Null Then M.DelyDate  Else D.DelyDate End As DelyDate
                                  From (   Select Syspono, DelyDate From O_POMas 
                                                  Where  IsStopDely = 0  And  IsPauseDely = 0
                                                  And IsDirectExport  = 0  And  MRPClosed = 0
                                                  And Issure = 1 And poDate <= @Startdatetime 
                                                  Group By  Syspono, DelyDate
                                                 ) M
                                                  Inner Join O_PODet D On M.SysPoNo = D.SysPoNo
                                                  Where  D.IsNeedDely = 1 
                                                  Group By M.Syspono,ItemNo,D.DelyDate,M.DelyDate
                            ) AA
                                 Left join  (Select D.Syspono,D.ItemNo,IsNull(Sum(qty),0) As delyqty
                                                   From O_DLMas M       
                                                   Inner Join O_DLDet  D On M.SysDelyNo = D.SysDelyNo
                                                  Group By D.Syspono,D.ItemNo
                          ) BB  On AA.Syspono = BB.Syspono and AA.ItemNo = BB.ItemNo
                               Where  IsNull(AA.Qty,0) <> IsNull(BB.Delyqty,0)
          ) TT 
Order by Itemno

Select itemno,Sum(IsNull(Poqs,0)) As Poqs ,DelyDate
Into #T1
From   #TA1
Group By Itemno,DelyDate 
Order by  Itemno
----------------------------------------------------------------------------------------------------------------------
/*存倉數=車間存數+貨倉庫存數 */
-----------------------------------------------------------------------------------------------------------------------------
--車間存數 (#T3)
Select Partno Into #A1
From VB_Purgoods
Where Len(Partno) > 1 
Group By Partno
-- 注意﹕ 查詢O_AssReportMas,O_AssReportDet是裝配線上的數
Select  Itemno ,Stockqty
Into #T3
From (
            Select BB.*,C.itemno
             From (
                        Select A.Bompartno,A.Stockqty
                         From O_AssReportMas B  Inner Join O_AssReportDet A  On A.sysNO = B.SysNO 
                        Where  B.[Date] = @StartDateTime
                        And  A.Bompartno   Not In (Select*From #A1)
                        ) BB      Inner Join VB_part C  On C.partno = BB.bompartno
               UniOn All
               Select BB.*,C.itemno
                From (
                           Select A.Bompartno,A.Stockqty
                           From O_AssReportMas B  Inner Join O_AssReportDet A   On A.sysNO = B.SysNO 
                          Where  B.[Date] = @StartDateTime  And  A.Bompartno  In (Select*From #A1)
                          ) BB     Inner Join VB_Purgoods C  On C.partno = BB.Bompartno                         
            ) CC Where  Stockqty > 0
Group By  Itemno ,Stockqty
Drop table #A1
------------------------------------------
--貨倉庫存數 (#T4)   注﹕其實當天的庫存已經載總數里包括了
 Select ItemNO,Sum(InStockQty) As InStockQty,Sum(OutStockQty) As OutStockQty,
  Sum(CSQty) + Sum(InStockTQty) - Sum(OutStockTQty)  As StockQty    --   StockQty是負數系統暫當零考慮
  Into  #T4
  From(
    --庫存數量總數
     Select D.ItemNO,  InStockQty  = 0,  OutStockQty = 0,
                  InStockTQty  = Case When B.IsRDFlag = '1' Then Sum(D.Qty) Else 0 End, --入庫總數
                 OutStockTQty  = Case When B.IsRDFlag = '0' Then Sum(D.Qty) Else 0 End, CSQty = 0
     From   O_StockMas M Inner Join O_StockDet  D On M.SysStockBillNO=D.SysStockBillNO
     Inner Join B_StockRD_Type B On M.RDID = B.RDID Inner Join VB_Items S On S.ItemNO = D.ItemNO
     Where     M.StockDate >= @FirstDate  And     M.StockDate < @StartDateTime + 1  
     Group By  B.IsRDFlag,M.RDID,D.ItemNO
     UNION ALL
     --清倉數 
     Select  D.ItemNO, InStockQty  = 0,   OutStockQty = 0,    InStockTQty = 0,  OutStockTQty = 0,Sum(D.Qty) As CSQty
     From  O_CheckStockMas M Inner  Join  O_CheckStockDet  D On M.SysCheckStockNo = D.SysCheckStockNo
     Inner join VB_Items I ON D.ItemNO = I.ItemNO
     Where M.[Year] = @Year AND M.[Month] = @Month   Group By D.ItemNO )  As F
    Group By ItemNO
-----------------------------------------------------------------------------------------------------------------------------
--毛需求=調入ForeCast總數+PO欠數(PO欠數﹐PO追加) 
Select  ItemNo,SumFCQty,DelyDate
Into     #T5
From (
            Select  ItemNo,Poqs As SumFCQty,DelyDate
            From #T1
            Union All
            Select d.itemno,Sum(IsNull(Qty,0)) As SumFCQty,D.NeedDate  as DelyDate
            From W_SalesForeCastMas M  Inner Join W_SalesForeCastDet D On m.Sysbillno = d.Sysbillno
            Where M.Issure = 1
            And D.Havemrp = 1
            And M.MRPClosed = 0
            And D.MRPClose = 0
            And D.NeedDate <= @MaxEndDate
            Group By  D.Itemno, D.NeedDate
           )N Order by DelyDate
----------------------------------------------------------------------------------------------------------------------------
--調入生產BOM資料
 Select M.Itemno As Fitemno, D.Itemno As CItemno,D.Qty,B.Source
 Into     #T6
 From B_ItemBomMas M
 Inner Join B_ItemBomDet D  On M.Sysbillno = D.Sysbillno
 Inner Join VB_Items B On B.Itemno = D.Itemno
 Where   M.Bomtype = 0  And M.Issure = 1
-----------------------------------------------------------------------------------------------------------------------------
--MRP計算開始
Declare @Itemno Varchar(30)
Declare @SumFCQty  Decimal(20,5) --  總數
Declare @BomDetItemno Varchar(30)
Declare @BomDetQty Decimal(20,5)
Declare @FCusPoqs Decimal(20,5)--  客戶欠數  @CusPoqs Decimal(20,5),
Declare @VenPoqs Decimal(20,5),@FvenPoqs Decimal(20,5) --  供應商欠數
Declare @PdQty  Decimal(20,5),@FPDQty Decimal(20,5) --  盤點數
Declare @CcQty Decimal(20,5),@FHCQty Decimal(20,5)--  存倉數
Declare @FAllQty Decimal(20,5) --  總數
Declare @CAllQty Decimal(20,5)
Declare @FLeadTime  Int   --  父提前期
Declare @LeadTime    Int   --   本節點提前期
Declare @Source Varchar(10)--  料品來源
Declare @NeedDate DateTime
Declare @Foot Int
Declare @CNeedQty  Decimal(20,5)
Declare @CcNeedQty  Decimal(20,5)
Declare @CCount       Int
Declare @FNQty Decimal(20,5)  --  父毛需求
Declare @CnQty Decimal(20,5)  --  子毛需求

Set @Foot=0
            Declare Table_FC Cursor for Select itemno,SumFCQty,DelyDate From #T5
            Open Table_FC
            Fetch Next From Table_FC Into @Itemno,@SumFCQty,@NeedDate
            While (@@Fetch_Status = 0)
            Begin 
--判斷在BOM表中是否存在
                      Select @Count = Count(*) From B_ItemBomMas  Where Itemno = @Itemno And BomType = 0 And Issure = 1
                      If  @Count = 0
                      Begin
                                 Insert Into B_ItemNotBom(itemno,idkey,needDate) Values (@Itemno,1,@NeedDate)
                                 Fetch Next From Table_FC Into @Itemno,@SumFCQty,@NeedDate  
                      End  
                      Else  Begin
                      Set  @FvenPoqs = 0
                      Set  @FPDQty = 0
                      Set  @FHcQty = 0
                      Set  @FcusPoqs = 0     
                      Set  @FNQty = 0
-- 父提前期計算
                               Select  @FLeadTime = Ceiling(IsNull(I.LeadTime,0)/Case When IsNull(A.aDate,0) = 0 Then 1 Else IsNull( A.aDate,0) End)
                               From VB_Items I,(Select Top 1 aDate From B_TimeArea)A  Where ItemNo = @Itemno
--車間存數 --初始庫存 
                               Select @FPDQty = IsNull(stockqty,0) From #T3 Where Itemno = @Itemno
--貨倉庫存     --初始庫存
                               Select @FHCQty = IsNull(StockQty,0) From #T4 Where ItemNO = @Itemno
-- 類別
                               Select  @Source = Source  From  VB_Items   Where ItemNo = @Itemno
--- 加入父節點﹐目的是在生成自制單時包含父節點
                     Select       @FAllQty = IsNull(@SumFCQty,0) - IsNull(@FPDQty,0) - IsNull(@FHCQty,0)
                     Select       @FNQty = IsNull(@SumFCQty,0)

                     Select    @CCount  = Count(*)   From W_MRPResultDetail  Where CalcuNo = @CalcuNo  And CItemno = @Itemno-- And CNeedQty<>0 
                     If   @CCount = 0
                     Begin
                                  Insert Into W_MRPResultDetail(CalcuNo,LeadTime,NeedDate,RunDate,FItemNO,FNeedQty,FPurPoqs,FCusPoqs,
                                                      FHCQty,FPDQty,CItemno,CNeedQty,CUnitQty,CPurPoqs,CCusPoqs,CHCQty,CPDQty,CSource,
                                                      IsRunOver,foot,FQty,CQty)
                                  Values(@CalcuNo,@FLeadTime, @NeedDate,GetDate(),@Itemno,IsNull(@FAllQty,0),IsNull(@FvenPoqs,0),0,IsNull(@FHCQty,0),
                                                     IsNull(@FPDQty,0), @Itemno,IsNull(@FAllQty,0),1,IsNull(@FvenPoqs,0),IsNull(@FcusPoqs,0),
                                                     IsNull(@FHCQty,0),IsNull(@FPDQty,0),@Source,0,@Foot,IsNull(@FNQty,0),IsNull(@FNQty,0))                                                       

                                  If   @@Error <> 0
                                 Begin
                                            Set   @Returnid = 0
                                            Set   @ErrMsg = '插入數據出錯﹐請與電腦部聯系!'
                                            GoTo ExitLable         
                                 End
                     End
                     Else
                     Begin
                                  Select  Top 1 @CNeedQty = CNeedQty  From  W_MRPResultDetail
                                  Where   CalcuNo = @CalcuNo  And  CItemno = @Itemno 
                                  Order by RunDate Desc
                                  Insert Into W_MRPResultDetail(CalcuNo,LeadTime,NeedDate,RunDate,FItemNO,FNeedQty,FPurPoqs,FCusPoqs,
                                                      FHCQty,FPDQty, CItemno,CNeedQty,CUnitQty,CPurPoqs,CCusPoqs,CHCQty,CPDQty,CSource,
                                                      IsRunOver,foot,FQty,CQty)
                                  Select      @CalcuNo,@FLeadTime, @NeedDate,GetDate(),@Itemno,
                                                     Case  When  @CNeedQty>=0 Then  IsNull(@FNQty,0)  Else    @FNQty+@CNeedQty End,
                                                     IsNull(@FvenPoqs,0),IsNull(@FCusPoqs,0),IsNull(@FHCQty,0),IsNull(@FPDQty,0), @Itemno,
                                                     Case  When  @CNeedQty>=0 Then  IsNull(@FNQty,0)  Else    @FNQty+@CNeedQty End,
                                                     @SumFCQty,IsNull(@FvenPoqs,0),IsNull(@FcusPoqs,0),
                                                     0,0,@Source,0,@Foot,IsNull(@FNQty,0),IsNull(@FNQty,0)

                                  If   @@Error <> 0
                                 Begin
                                             Set   @Returnid = 0
                                             Set   @ErrMsg = '插入數據出錯﹐請與電腦部聯系!'
                                             GoTo ExitLable         
                                 End
                     End
--展開下一層
                     Declare Table_BOM Cursor for   Select Citemno,Qty,Source From #T6  Where  Fitemno=@Itemno
                     Open Table_BOM
                     Fetch Next From Table_BOM Into @BOMDetItemno,@BomDetQty,@Source
                     While (@@Fetch_Status = 0)
                     Begin   
                              Set  @VenPoqs = 0
                              Set  @PdQty = 0
                              Set  @CcQty = 0
                              Set  @CAllQty = 0
--                              Set  @CusPoqs = 0      
                              Set  @CnQty = 0
-- 本節點提前期
                             Select  @LeadTime = @FLeadTime + Ceiling(IsNull(I.LeadTime,0)/Case When IsNull(A.aDate,0) = 0 Then 1 Else IsNull(A.aDate,0) End)
                             From VB_Items I,(Select Top 1 aDate From B_TimeArea)A Where ItemNo = @BOMDetItemno
--車間存數
                             Select @PdQty = IsNull(StockQty,0)  From #T3 Where ItemNo = @BOMDetItemno
--貨倉庫存
                             Select @CcQty = IsNull(StockQty,0)  From #T4 Where ItemNO = @BOMDetItemno
-- 類型
                             Select @Source = Source   From  VB_Items  Where ItemNo = @BOMDetItemno
--計算總數
                             Select @CAllQty = (Case When @CCount <= 0 Then Case When @FAllQty > 0  Then @FAllQty Else 0 End
                                                         Else Case  When  @CNeedQty >= 0 Then  IsNull(@FNQty,0)
                                                         Else IsNull(@FNQty,0) + IsNull(@CNeedQty,0) End End) * IsNull(@BomDetQty,0) - @PdQty - @CcQty
                             Select  @CnQty = (Case When @CCount <= 0 Then Case When IsNull(@FAllQty,0) > 0  Then IsNull(@FAllQty,0)
                                                                Else 0 End Else    Case  When  IsNull(@CNeedQty,0) >= 0 Then  IsNull(@FNQty,0)
                                                                             Else   IsNull(@FNQty,0) + IsNull(@CNeedQty,0) End End) * IsNull(@BomDetQty,0)
--如果毛需求小于等于0﹐則不用考慮本節點需求
                If      @CnQty <= 0
                     Fetch Next From Table_BOM Into @BOMDetItemno,@BomDetQty,@Source    
                Else  Begin
--單層展開   
                     Select    @Count  = Count(*)   From W_MRPResultDetail  Where CalcuNo = @CalcuNo And  CItemno = @BOMDetItemno --And CNeedQty<>0
                     If   @Count = 0
                     Begin
                             Insert Into W_MRPResultDetail (CalcuNo,LeadTime,NeedDate,RunDate,FItemNO,FNeedQty,FPurPoqs,FCusPoqs,
                                             FHCQty,FPDQty, CItemno,CNeedQty,CUnitQty,CPurPoqs,CCusPoqs,CHCQty,CPDQty,CSource,
                                             IsRunOver,foot,FQty,CQty)
                             Values(@CalcuNo,@LeadTime,@NeedDate, GetDate(),@Itemno,(Case When @CCount <= 0 Then  IsNull(@FAllQty,0)
                                              Else  Case  When  @CNeedQty >= 0 Then  IsNull(@FNQty,0) 
                                                                      Else    IsNull(@FNQty,0) + IsNull(@CNeedQty,0) End End),
                                             IsNull(@FvenPoqs,0),0,IsNull(@FHCQty,0),
                                             IsNull(@FPDQty,0), @BOMDetItemno,IsNull(@CAllQty,0),@BomDetQty,IsNull(@VenPoqs,0),0,
                                             IsNull(@CcQty,0),IsNull(@PdQty,0),@Source,0,@Foot,IsNull(@FNQty,0),IsNull(@CnQty,0))

                            If @@ROWCount = 0
                            Begin
                                        Set    @Returnid = 0
                                        Set    @ErrMsg = 'MRP計算出錯,請與電腦部聯系!'
                                        GoTo ExitLable    
                            End   
                       End
                       Else
                       Begin
                             Select  Top 1 @CcNeedQty = IsNull(CNeedQty,0)  From  W_MRPResultDetail
                             Where  CalcuNo = @CalcuNo And CItemno = @BOMDetItemno
                             Order by RunDate Desc
                             Insert Into W_MRPResultDetail (CalcuNo,LeadTime,NeedDate,RunDate,FItemNO,FNeedQty,FPurPoqs,FCusPoqs,
                                                 FHCQty,FPDQty,CItemno,CNeedQty,CUnitQty,CPurPoqs,CCusPoqs,CHCQty,CPDQty,CSource,
                                                 IsRunOver,foot,FQty,CQty)
                            Select  @CalcuNo,@LeadTime,@NeedDate, GetDate(),@Itemno,(Case When @CCount <= 0 Then  IsNull(@FAllQty,0)
                                              Else Case  When  IsNull(@CNeedQty,0) >= 0 Then  IsNull(@FNQty,0) 
                                                                     Else    IsNull(@FNQty,0) + IsNull(@CNeedQty,0) End End),
                             IsNull(@FvenPoqs,0),0,IsNull(@FHCQty,0), IsNull(@FPDQty,0), @BOMDetItemno,
                             Case When IsNull(@CcNeedQty,0) >= 0 Then IsNull(@CnQty,0)
                                         Else IsNull(@CnQty,0) + IsNull(@CcNeedQty,0) End ,
                             @BomDetQty,IsNull(@VenPoqs,0),0,0,0,@Source,0,@Foot,IsNull(@FNQty,0),IsNull(@CnQty,0)

                              If   @@Error <> 0
                              Begin
                                          Set   @Returnid = 0
                                          Set   @ErrMsg = '插入數據出錯﹐請與電腦部聯系!'
                                          GoTo ExitLable         
                              End
                       End
                      Fetch Next From Table_BOM Into @BOMDetItemno,@BomDetQty,@Source
                      End

             End
             Close Table_BOM
             DeAllocate Table_BOM

             Update  W_MRPResultDetail Set  IsRunOver = 1 Where CalcuNo = @CalcuNo And FItemNo = @ItemNo And CItemNo = @Itemno
             If @@ROWCount = 0
             Begin
                          Set    @Returnid = 0
                          Set    @ErrMsg = 'MRP計算插入數據出錯,請與電腦部聯系!'
                          GoTo ExitLable    
             End 
             Set  @SumFCQty = 0
             Fetch Next From Table_FC Into @Itemno,@SumFCQty, @NeedDate
             End
          End
          Close Table_FC
          DeAllocate Table_FC  

Declare @CItemNO Varchar(30)
Declare @CQty  Decimal(20,5)
Declare @NdDate DateTime
Declare @dNeedQty   Decimal(20,5)
Declare @Sysno Int

Set @Foot=1
--進行多次展開的循環過程      
BOMLable:
Begin
             Set @Count = 0
             Select @Count = Count(*)  From W_MRPResultDetail
             Where CalcuNo = @CalcuNo And CSource = '自制'
             And IsRunOver = 0 
             And  CNeedQty > 0              -- 考慮要自制的來進行展開

             If @Count > 0
             Begin
                           Select Sysno,CItemno,CNeedQty,NeedDate,FHCQty,FPDQty
                            Into   #ABC
                            From W_MRPResultDetail
                            Where CalcuNo = @CalcuNo And CSource = '自制'
                            And IsRunOver = 0
                            And  CNeedQty > 0 

             Declare Table_BOMAgain Cursor for Select  Sysno,CItemno,CNeedQty,NeedDate,FHCQty,FPDQty  From #ABC
             Open Table_BOMAgain
             Fetch Next From Table_BOMAgain  Into @Sysno,@CItemNO,@CQty,@NdDate,@FHCQty,@FPDQty
             While (@@Fetch_Status = 0)
             Begin
---記錄沒有BOM資料的ItemNO
                        Select   @Count = Count(*)
                        From B_ItemBomMas
                        Where Itemno = @CItemNO And Issure = 1 And Bomtype = 0

                        If   @Count = 0
                        Begin
                                   Update  W_MRPResultDetail Set  IsRunOver = 2 Where Sysno = @Sysno
                                   Insert Into B_ItemNotBom(Itemno,Idkey,NeedDate) Values (@CItemNO,2,@NdDate)
                       End
                       Else
                       Begin
-- @FLeadTime 父提前期
                                   Select  @FLeadTime = IsNull(LeadTime,0)  From W_MRPResultDetail
                                   Where CalcuNo = @CalcuNo  And CItemNo = @CItemNO And sysno = @Sysno

                                  Declare Table_BOM Cursor for    Select Citemno,Qty,Source From #T6  Where  Fitemno = @CItemNO 
                                  Open Table_BOM
                                  Fetch Next From Table_BOM Into @BOMDetItemno,@BomDetQty,@Source
                                  While (@@Fetch_Status = 0)
                                  Begin
                                              Set  @VenPoqs = 0
                                              Set  @PdQty = 0
                                              Set  @CcQty = 0
                                              Set  @FAllQty = 0
                                              Set  @CAllQty = 0
--                                              Set  @CusPoqs = 0
                                              Set  @CnQty = 0
-- @LeadTime 本節點的提前期
                                             Select  @LeadTime = @FLeadTime  + Ceiling( IsNull( I.LeadTime ,0)/Case When IsNull(A.aDate,0)=0 Then 1 Else IsNull(A.aDate,0) End)
                                             From VB_Items I,(Select Top 1 aDate From B_TimeArea)A
                                             Where  ItemNo = @BOMDetItemno
--車間存數
                                             Select @PdQty = IsNull(stockqty,0)  From #T3 Where Itemno = @BOMDetItemno 
--貨倉庫存
                                             Select @CcQty = IsNull(StockQty,0)  From #T4 Where ItemNO = @BOMDetItemno
--計算總數
                                            Select  @Count = Count(*)  From   W_MRPResultDetail 
                                            Where  CalcuNo = @CalcuNo And CItemNo = @BOMDetItemno

                                            If   @Count = 0
                                            Begin
                                                       Select     @CAllQty = IsNull(@CQty,0) * IsNull(@BomDetQty,0) - IsNull(@PdQty,0) - IsNull(@CcQty,0)                                           
                                                       Select     @CnQty = IsNull(@CQty,0) * IsNull(@BomDetQty,0)  
                                            End
                                            Else
                                            Begin
                                                       Select  Top 1  @dNeedQty = IsNull(CNeedQty,0)  From   W_MRPResultDetail 
                                                       Where  CalcuNo = @CalcuNo And  CItemNo = @BOMDetItemno
                                                       Order by RunDate Desc
                                                       Select   @CAllQty = Case When  IsNull(@dNeedQty,0) >= 0
                                                                                                          Then   IsNull(@CQty,0)*IsNull(@BomDetQty,0) 
                                                                                               Else IsNull(@CQty,0)*IsNull(@BomDetQty,0)+IsNull(@dNeedQty,0)  End
                                                       Select   @CnQty  =   IsNull(@CQty,0)*IsNull(@BomDetQty,0)
                                            End
-- 如果本節點毛需求小于等于0﹐不用考慮本節點
                                            If      @CnQty <= 0
                                              Fetch Next From Table_BOM Into @BOMDetItemno,@BomDetQty,@Source
--BOM展開   20051101   不用再考慮PO欠數﹐但是要考慮車間和庫存量
                                           Else   Begin
                                            Insert Into W_MRPResultDetail (CalcuNo,LeadTime,NeedDate,RunDate,FItemNO,FNeedQty,FPurPoqs,
                                                                FCusPoqs,FHCQty,FPDQty, CItemno,CNeedQty,CUnitQty,CPurPoqs,CCusPoqs,
                                                                CHCQty,CPDQty,CSource,IsRunOver,Foot,FQty,CQty)
                                            Values(@CalcuNo,@LeadTime,@NdDate,GetDate(),@CItemNO,IsNull(@CQty,0),0,0, IsNull(@FHCQty,0),
                                                              IsNull(@FPDQty,0), @BOMDetItemno,@CAllQty,@BomDetQty,IsNull(@VenPoqs,0),0,
                                                              Case When @Count  > 0 Then 0 Else IsNull(@CcQty,0) End,
                                                              Case When @Count  > 0 Then 0 Else IsNull(@PdQty,0) End,
                                                               @Source,0,@Foot,IsNull(@CQty,0),IsNull(@CnQty,0))  
   
                                            If @@ROWCount = 0
                                            Begin
                                                        Set    @Returnid = 0
                                                        Set    @ErrMsg = 'MRP計算插入數據出錯,請與電腦部聯系!'
                                                        GoTo ExitLable    
                                           End

                                           Update  W_MRPResultDetail Set  IsRunOver = 1 Where sysno = @sysno
                                           If @@ROWCount  = 0
                                           Begin
                                                       Set    @Returnid = 0
                                                       Set    @ErrMsg = 'MRP計算插入數據出錯,請與電腦部聯系!'
                                                       GoTo ExitLable    
                                           End 
                                           Fetch Next From Table_BOM Into @BOMDetItemno,@BomDetQty,@Source
                                          End
                                 End
                                 Close Table_BOM
                                 DeAllocate Table_BOM   
                         End
                        Fetch Next From Table_BOMAgain Into @Sysno,@CItemNO,@CQty,@NdDate,@FHCQty,@FPDQty    
                End
               Close Table_BOMAgain
               DeAllocate Table_BOMAgain  
               Drop table #ABC
   End
End
--/再次判斷     如果還有的話則進行bom展開﹐如果沒有的話則要開始進行時間的循環
  Select @Count = 0
  Select @Count = Count(*)
  From W_MRPResultDetail
  Where CalcuNo = @CalcuNo And  CSource = '自制'
   And   IsRunOver = 0
   And  CNeedQty > 0

   If @Count  >  0
   Begin
              Select     @Foot = 1 + @Foot
              GoTo BOMLable--循環計算
   End   Else
   Begin     
--把BOM結構中屬于的采購的拿出來准備做采購單 ; 已訂未送在按時段循環的時候計算
              Select Citemno As ItemNo,Sum(Cqty)  As MaoQty,Sum(Case When CNeedQty < 0 Then 0 Else CNeedQty End) As JNeedQty ,
                            0 As CCusPoqs,0 As vPoqs,LeadTime, NeedDate,  Sum(CHCQty) As HCQty,Sum(CPDQty) As PDQty
              Into #Result1 
              From W_MRPResultDetail
              Where CalcuNo = @CalcuNo And  CSource = '采購'
              Group By Citemno,LeadTime,NeedDate
--              Having Sum(CNeedQty)>0 
              Order By Citemno

              Select       @Count = 0
              Select @Count = Count(*)
              From #Result1

              If   @Count = 0
              Begin
                        Set    @Returnid = 0
                        Set    @ErrMsg = 'MRP運算完畢,本次計算沒有需要采購的物料!'
                        GoTo ExitLable
               End

               Select R.*,B.partno,B.Partname,B.Model,B.Vendorno,B.Vendor
               Into #Result2
               From #Result1 R   Inner Join VB_Items  B On  B.itemno = R.itemno
               Where B.Issure = 1
--運算所有的自制FOR廠內加工    此處只考慮采購和加工兩種情況﹐虛擬件等未考慮在內!!!
             Select  Citemno As ItemNo,Sum(Cqty) As MaoQty,Sum(Case When CNeedQty < 0 Then 0 Else CNeedQty End) As JNeedQty ,
                            0 As CCusPoqs,0 As vPoqs,LeadTime, NeedDate, Sum(CHCQty) As HCQty,Sum(CPDQty) As PDQty
              Into #SelfMasterResult1 
              From W_MRPResultDetail
              Where CalcuNo = @CalcuNo  And  CSource <>  '采購' 
              Group By Citemno,LeadTime,NeedDate
              Order By Citemno

              Select       @Count = 0
              Select @Count = Count(*) From #SelfMasterResult1
              If @Count = 0
              Begin
                        Set    @Returnid = 0
                        Set    @ErrMsg = 'MRP運算完畢﹐本次計算沒有要加工的物料!'
                        GoTo ExitLable
              End

              Select R.*,B.Partno,B.Partname,B.Model,B.Vendorno,B.Vendor,GetDate() as RunDate
              Into #SelfMasterResult2
              From #SelfMasterResult1 R   Inner Join VB_Items  B On  B.Itemno = R.Itemno
              Where B.Issure = 1

              Insert Into W_MRPResultForSelf(CalcuNo,LeadTime,NeedDate,Itemno,Partno,Partname,Model,Vendorno,Vendor,
                                                                               Maoqty,VPoqs,Hcqty,Pdqty,Jneedqty,RunDate)
              Select  @CalcuNO, LeadTime ,NeedDate,Itemno,Partno,Partname,Model,Vendorno,Vendor,
                             Maoqty,VPoqs,Hcqty,Pdqty,Jneedqty,RunDate
              From #SelfMasterResult2

              If @@Error <> 0
              Begin
                        Set @Returnid = 0
                        Set @ErrMsg = '沒有生成加工單,請與電腦部聯系!'
                       GoTo ExitLable    
               End  
Declare  @RItemno         Varchar(30)
Declare  @RPartno          Varchar(30)
Declare  @Rpartname    Varchar(100)
Declare  @Rmodel          Varchar(100)
Declare  @RVendor        Varchar(50)
Declare  @RVendorno   Varchar(20)
Declare  @RmaoQty        Decimal(20,5)
Declare  @RVPoqs        Decimal(20,5)
Declare  @RHCQty           Decimal(20,5)
Declare  @RPDQty           Decimal(20,5)
Declare  @RJNeedQty    Decimal(20,5)
Declare  @NDate               DateTime
Declare  @LdTime            Int                        --提前期
Declare  @ID                       Int

          Declare Talbe_Result Cursor  for Select LeadTime,NeedDate,Itemno,Partno,Partname,Model,Vendorno,Vendor,Maoqty,
                                                                                         VPoqs,Hcqty,Pdqty,Jneedqty
          From #Result2
          Open Talbe_Result
          Fetch Next From Talbe_Result  Into  @LdTime,@NDate,@RItemno,@RPartno,@Rpartname,@Rmodel,@RVendorno,
                                                                                @RVendor,@RmaoQty,@RVPoqs,@RHCQty,@RPDQty,@RJNeedQty
          While (@@Fetch_Status = 0)
          Begin
                     Insert Into W_MRPResultForPo (LeadTime,NeedDate,CalcuNo,Itemno,Partno,Partname,Model,Vendorno,Vendor,
                                                                                     Maoqty,VPoqs,Hcqty,Pdqty,Jneedqty,RunDate)
                     Values  (@LdTime,@NDate, @CalcuNO,@RItemno,@RPartno,@Rpartname,@Rmodel,@RVendorno,@RVendor,
                                      @RmaoQty, @RVPoqs,@RHCQty,@RPDQty,@RJNeedQty,GetDate())

                     If @@ROWCount = 0
                     Begin
                                Set    @Returnid = 0
                                Set    @ErrMsg = 'MRP分配供應商出錯,請與電腦部聯系!'
                                GoTo ExitLable    
                    End

                Fetch Next From Talbe_Result  Into @LdTime,@NDate,@RItemno,@RPartno,@Rpartname,@Rmodel,@RVendorno,
                                                                                          @RVendor,@RmaoQty,@RVPoqs,@RHCQty,@RPDQty,@RJNeedQty
          End
          Close  Talbe_Result
          DeAllocate Talbe_Result
End
--關閉所有已經運算過的預測單
     Update W_SalesForeCastMas Set MRPClosed = 1
     From W_SalesForeCastMas M, W_SalesForeCastDet D
     Where  D.Sysbillno = M.Sysbillno 
     And  D.NeedDate <= @MaxEndDate
     And M.Issure = 1 
     And M.MRPClosed = 0
     And D.Havemrp = 1

      If @@ROWCount = 0
     Begin
                   Set @Returnid = 0
                   Set @ErrMsg = '更新預測單表頭出錯!'
                   GoTo ExitLable    
     End

     Update W_SalesForeCastdet Set MRPClose = 1
     From W_SalesForeCastMas M, W_SalesForeCastDet D
     Where D.Sysbillno = M.Sysbillno
     And  D.NeedDate <= @MaxEndDate 
     And M.Issure = 1
     And D.MRPClose = 0
     And D.Havemrp = 1

     If @@ROWCount = 0
     Begin
                  Set    @Returnid = 0
                  Set    @ErrMsg = '更新預測單明細出錯!'
                  GoTo ExitLable    
      End
--關閉所有已經運算過的訂單
     Update O_POMas Set MRPClosed = 1
     Where   poDate <= @MaxEndDate 
      And Issure = 1  And IsDirectExport = 0
      And  Syspono Not In (Select  Syspono From #TA1)

      If @@ROWCount = 0
     Begin
                   Set @Returnid = 0
                   Set @ErrMsg = '更新訂單表頭出錯!'
                   GoTo ExitLable    
     End

     Update O_PODet Set MRPClosed = 1
     Where  Syspono  In (Select  Syspono From O_POMas Where MRPClosed = 1)

     If @@ROWCount = 0
     Begin
                  Set    @Returnid = 0
                  Set    @ErrMsg = '更新訂單明細出錯!'
                  GoTo ExitLable    
      End
---------------------------------------------------------------------------------------------------------------------------------------------------
--- 進行按時間區段循環
Declare    @FirstI               Int     --用于判斷是否第一個時間區段﹐因為這個時間段的庫存要特殊處理
Declare    @aStartDate   DateTime
Declare    @aEndDate    DateTime
Declare    @TimeArea    Int    -- 時間區段個數
Declare    @aCount          Int
          Select           @FirstI = 0
-----   采購PO預計入庫(供應商欠數),已訂未送 
--注意﹕有消息說把預計入庫都放在第一個區段內﹐不用考慮在別的區段的情況。消息有待確認!!!!!
          Select  B.Itemno, A.Poqs as SumFCQty,A.Purpodate as DelyDate  
          Into #T2
          From (
                       Select Syspartno, Purpodate,Sum(IsNull(Poqs,0)) As Poqs
                       From (
                                    Select aa.*, bb.Delyqty,IsNull(aa.Qty,0)-IsNull(bb.Delyqty,0) As Poqs
                                    From  (
                                                 Select  Purpono,Purpodate ,Syspartno,Sum(Qty) As Qty
                                                 From (
                                                             Select Syspurpono,Purpono,Case When IsDate(DelyDate)=1 Then  Convert(DateTime,DelyDate,120)  Else  PurPoDate  End  Purpodate
                                                             From O_PurPOMas       --下面的 purpodate以后換成O_purPODet中的delydate
                                                             Where Isstop=0 And Issure=1 And Purpodate<=@MaxEndDate
                                                             ) m   Inner Join O_PurPODet d On m.Syspurpono=d.Syspurpono
                                                              Group By Purpono,m.Purpodate,Syspartno
                                                    )  aa    
                                                        Left Join  (
                                                                           Select d.Pono,d.Syspartno,IsNull(Sum(Qty),0) As Delyqty
                                                                           From O_VendorDelyMas m
                                                                           Inner Join O_VendorDelyDet  d On m.SysDelyNo=d.SysDelyNo
                                                                          Group By d.Pono,d.Syspartno
                                                                           ) bb  On aa.Purpono=bb.Pono And aa.Syspartno=bb.Syspartno
                                                           Where  IsNull(aa.Qty,0)<>IsNull(bb.Delyqty,0) 
                                          )  TT  Group By Syspartno,Purpodate
                        ) A Inner Join
                           (Select Syspartno,Itemno From VB_Purgoods Group By Syspartno,Itemno ) B On A.Syspartno=B.Syspartno
--需求的全部物料號碼 For  Purchase          
          Select  Distinct  CalcuNo, ItemNO,PartNo,PartName,Model,VendorNo, Vendor
          Into         #ItemCount
          From     W_MRPResultForPo  
          Where  CalcuNo = @CalcuNo
--需求的全部自制號碼 For  Self-Make
         Select Distinct CalcuNo,ItemNo,PartNo,PartName,Model
         Into         #SelfCount
         From W_MRPResultForself
          Where  CalcuNo = @CalcuNo
--*************** ***********************************************  開始時間循環
          Declare Time_Result Cursor  for Select   StartDate,EndDate   From  B_TimeCircle
          Open Time_Result
          Fetch Next From Time_Result   Into @aStartDate,  @aEndDate
          While (@@Fetch_Status = 0)
          Begin
-----------------------------------------------------------------------------------------------------------   
                  If  @FirstI = 0
                 Begin
--第一時區的預計入庫計算For Purchase
                       Select   Sum(SumFCQty)  As VPoqs,ItemNo 
                       Into     #Futureqty1
                       From  #T2  
                       Where  DelyDate <  @aEndDate + 1
                       Group By  ItemNo  

                       Select  ItemNo, Sum(IsNull(VPoqs,0))VPoqs,  Sum(IsNull(HCQty,0))HCQty , Sum(IsNull(PDQty,0))PDQty
                       Into   #KuCunQty
                       From  W_MRPResultForPo     Where     CalcuNO = @CalcuNO
                       Group By  ItemNo
                       Select  R.ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Sum(Maoqty)Maoqty,Sum(JNeedQty) JNeedQty,
                                     Sum(R.VPoqs)VPoqs,P.HCQty As HCQty,P.PDQty As PDQty,Max(NeedDate) NeedDate,
                                     Max(IsNull(LeadTime,0)) LeadTime

                       Into   #Nextqty1               
                       From  W_MRPResultForPo  R  Left Join #KuCunQty P On R.ItemNo = P.ItemNo
                       Where   NeedDate < @aEndDate + 1
                       And  CalcuNO = @CalcuNO
                      Group By  R.ItemNo,PartNo,PartName,Model,VendorNo,Vendor ,P.HCQty,  P.PDQty
--第一時間區段的毛需求 For   Self-Make      
                       Select  ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Sum(Maoqty)Maoqty,Sum(JNeedQty) JNeedQty,
                                     Sum(IsNull(HCQty,0)) + Sum(IsNull(PDQty,0))  As StockQty,--Sum(IsNull(VPoqs,0))+Sum(IsNull(HCQty,0))+Sum(IsNull(PDQty,0))-Sum(Maoqty)
                                     Sum(IsNull(VPoqs,0))VPoqs,Sum(IsNull(HCQty,0))HCQty,Sum(IsNull(PDQty,0))PDQty,
                                     Max(NeedDate) As NeedDate,Max(IsNull(LeadTime,0)) LeadTime
                       Into   #NextqtyA1               
                       From  W_MRPResultForSelf
                       Where   NeedDate < @aEndDate + 1
                       And  CalcuNO = @CalcuNO
                      Group By  ItemNo,PartNo,PartName,Model,VendorNo,Vendor   
--------------------------------- 考慮安全庫存﹐最小批量和批量增量
                       Select  @CalcuNO As CalcuNo,  1 As TimeArea,N.ItemNO,N.PartNo,N.PartName,N.Model,N.VendorNo, N.Vendor,
                                      IsNull(N.Maoqty,0) As Maoqty,                                     
                                      Case When (( IsNull(N.HCQty,0) + IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) ) < 0 Then
                                                           (Case  When   IsNull(I.BatchAppendQty,0) = 0 Then 
                                                                                       (Case When ((0 - (( IsNull(N.HCQty,0) + IsNull(N.PDQty,0) + IsNull(F.VPoqs,0))*1.0 - IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                IsNull(I.MinQty,0)
                                                                                                   Else ((0 - (( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                        End) 
                                                                           Else  Case When    (Case When ((0 - (( IsNull(N.HCQty,0) + IsNull(N.PDQty,0) + IsNull(F.VPoqs,0))*1.0 - IsNull(N.Maoqty,0) ) + IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 ) <= IsNull(I.MinQty,0) Then
                                                                                                                                             IsNull(I.MinQty,0)
                                                                                                                                Else ((0 - (( IsNull(N.HCQty,0) + IsNull(N.PDQty,0) + IsNull(F.VPoqs,0))*1.0 - IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                   End) <= IsNull(I.BatchAppendQty,0) Then    IsNull(I.BatchAppendQty,0)
                                                                                       Else      
                                                                                                Case When ( Convert(int,(Case When ((0 - (( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ( (IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End))% Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End))=0   Then
                                                                                                                              (Case When ((0 - (( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - (( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)
                                                                                                                Else
                                                                                                                          ( ( (Case When ((0 - (( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - (( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0) )+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)/Case When IsNull( I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)+1)* IsNull(I.BatchAppendQty,0)
                                                                                                       End
                                                                                           End
                                                                      End)*IsNull(D.Yield,0)/100  + ( IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0)-IsNull(N.Maoqty,0) )
                                      Else
                                                IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0)-IsNull(N.Maoqty,0)
                                      End as Stockqty,
                                      IsNull(F.VPoqs,0) As PlanIn,0  As PlanNum ,N.NeedDate,N.LeadTime,
                                      Case When ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))< 0 Then
                                                              (0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))
                                                   Else   0    
                                      End As Needqty,
                                      Case When ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))< 0 Then
                                                           (Case  When   IsNull(I.BatchAppendQty,0)=0 Then 
                                                                                       (Case When ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then

                                                                                                                IsNull(I.MinQty,0)
                                                                                                   Else ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                        End) 
                                                                           Else  Case When    (Case When ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                             IsNull(I.MinQty,0)
                                                                                                                                Else ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                   End)<= IsNull(I.BatchAppendQty,0) Then    IsNull(I.BatchAppendQty,0)
                                                                                       Else      
                                                                                                Case When (Convert(int, (Case When ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End))%Convert(int, Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End))=0   Then
                                                                                                                              (Case When ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)
                                                                                                                Else
                                                                                                                          ( ( (Case When ((0 - ((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 -((IsNull(N.HCQty,0)+IsNull(N.PDQty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)/ Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)+1)* IsNull(I.BatchAppendQty,0)
                                                                                                       End
                                                                                           End
                                                                      End)  
                                                    Else  0 
                                      End  As PlanPoNum

                       Into  #TimeArea1
                       From  #Nextqty1  N  Left  Join  #Futureqty1 F On  N.ItemNO = F.ItemNO
                       Left  Join  VB_Items  I  On  N.ItemNo = I.ItemNo
                       Left  Join (Select Distinct  M.Yield,D.ItemNo From B_ItemBomMas M Inner Join B_ItemBomDet D On M.sysbillNo = D.sysbillNo) D On N.ItemNo = D.ItemNo
                  End
--其他時間區段For Purchase
                  Else  Begin
--不是第一時區的預計入庫計算For Purchase
                       Select   Sum(SumFCQty)  As VPoqs,ItemNo 
                       Into   #Futureqty
                       From  #T2
                       Where  DelyDate >= @aStartDate  And DelyDate < @aEndDate+1
                       Group By  ItemNo

                       Select  ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Sum(Maoqty)Maoqty,Sum( VPoqs) VPoqs,
                                    0 As HCQty,0 As PDQty,Max(NeedDate) NeedDate,Max(IsNull(LeadTime,0)) LeadTime
                        Into   #Nextqty               
                        From  W_MRPResultForPo
                        Where NeedDate >= @aStartDate
                        And  NeedDate < @aEndDate + 1
                        And  CalcuNo = @CalcuNo
                        Group By  ItemNo,PartNo,PartName,Model,VendorNo,Vendor 

--其他時間區段For Self-Mastery      
                        Select  ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Sum(Maoqty)Maoqty,Sum(JNeedQty) NeedQty,
                                    Max(NeedDate) NeedDate,Max(IsNull(LeadTime,0)) LeadTime
                        Into   #NextqtyA               
                        From  W_MRPResultForSelf
                        Where NeedDate >= @aStartDate
                        And  NeedDate < @aEndDate + 1
                        And  CalcuNo = @CalcuNo
                        Group By  ItemNo,PartNo,PartName,Model,VendorNo,Vendor 
--------------------------------------
                       Select  @CalcuNO As CalcuNo,IsNull(@TimeArea,0)+1 As TimeArea, N.ItemNO,N.PartNo,N.PartName,N.Model,N.VendorNo,
                                      N.Vendor,IsNull(N.Maoqty,0) As Maoqty,
                                      Case When (IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0)-IsNull(N.Maoqty,0))< 0 Then
                                                              (0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))
                                                   Else   0    
                                      End As Needqty,
                                      Case When ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))< 0 Then
                                                           (Case  When   IsNull(I.BatchAppendQty,0)=0 Then 
                                                                                       (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                IsNull(I.MinQty,0)
                                                                                                   Else ((0 -( (IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                        End) 
                                                                           Else  Case When    (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                             IsNull(I.MinQty,0)
                                                                                                                                Else ((0 -( (IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                   End)<= IsNull(I.BatchAppendQty,0) Then    IsNull(I.BatchAppendQty,0)
                                                                                       Else      
                                                                                                Case When ( Convert(int,(Case When ((0 -( (IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End))% Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End))=0   Then
                                                                                                                              (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)
                                                                                                                Else
                                                                                                                          ( ( (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)/ Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)+1)* IsNull(I.BatchAppendQty,0)

                                                                                                       End
                                                                                           End
                                                                      End)*IsNull(D.Yield,0)/100  + ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))
                                      Else
                                              ( IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0)
                                      End as Stockqty,
                                      IsNull(F.VPoqs,0) As PlanIn,0  As PlanNum ,
                                      Case When ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))< 0 Then
                                                           (Case  When   IsNull(I.BatchAppendQty,0)=0 Then 
                                                                                       (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                IsNull(I.MinQty,0)
                                                                                                   Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                        End) 
                                                                           Else  Case When    (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                             IsNull(I.MinQty,0)
                                                                                                                                Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )

                                                                                                                   End)<= IsNull(I.BatchAppendQty,0) Then    IsNull(I.BatchAppendQty,0)
                                                                                       Else      
                                                                                                Case When ( Convert(int,(Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End))% Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End))=0   Then
                                                                                                                              (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)
                                                                                                                Else
                                                                                                                          ( ( (Case When ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )<=IsNull(I.MinQty,0) Then
                                                                                                                                                    IsNull(I.MinQty,0)
                                                                                                                                        Else ((0 - ((IsNull(P.Stockqty,0)+IsNull(F.VPoqs,0))*1.0-IsNull(N.Maoqty,0))+IsNull(I.SecInv,0))*IsNull(D.Yield,0)/100 )
                                                                                                                               End)/Case When IsNull( I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)+1)* IsNull(I.BatchAppendQty,0)
                                                                                                       End
                                                                                           End
                                                                      End)  
                                                    Else  0 
                                      End  As PlanPoNum,
                                      N.NeedDate,N.LeadTime

                       Into   #TimeArea2   
                       From  #Nextqty  N  Left  Join  #Futureqty F On  N.ItemNO = F.ItemNO
                       Left Join (Select  * From  W_MRPData
                                          Where  CalcuNo = @CalcuNO And TimeArea = @FirstI  And Flag = 0 ) P On N.ItemNO = P.ItemNO   
                       Left Join VB_Items I On N.ItemNo = I.ItemNo
                       Left  Join (Select Distinct  M.Yield,D.ItemNo From B_ItemBomMas M
                                           Inner Join B_ItemBomDet D On M.sysbillNo=D.sysbillNo) D On N.ItemNo = D.ItemNo
                    End
---------------------------------------------------------------------------------------------------------------
--按時間循環 開始 
                       If   @FirstI = 0    --第一時間區段    
                       Begin     
-- 先插入自制記錄  For Self-make
                                    Select  @Count = 0
                                    Select  @aCount = 0

                                    Select  @Count =  Count(*)
                                    From  #SelfCount
                                    Select  @aCount = Count(*)
                                    From  #NextqtyA1

                                    If   @Count <> @aCount
                                    Begin
                                               Insert  W_MRPData(CalcuNo,TimeArea,S.ItemNo,PartNo,PartName,Model, VendorNo, Vendor,
                                                           Maoqty,Stockqty,PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                              Select    @CalcuNo,1 As TimeArea,S.ItemNo,PartNo,PartName,Model,'' As VendorNo,'' As Vendor,
                                                             0 As Maoqty,IsNull(F.HCQty,0)+IsNull(F.PDQty,0) As Stockqty,0 As PlanIn,0 As NeedQty,
                                                             0 As PlanNum,0 As PlanPoNum, @aStartDate ,0 As LeadTime,1 As Flag
                                              From   #SelfCount  S Left  Join   (Select  Top 1 ItemNO,HCQty,PDQty From W_MRPResultForSelf
                                                                                                             Where CalcuNO=@CalcuNo  And HCQty<>0 Or (PDQty<>0))F
                                                          On S.ItemNo = F.ItemNo 

                                              If   @@Error <> 0
                                              Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                              End   

                                             Update W_MRPData   Set Maoqty=IsNull(N.Maoqty,0),LeadTime = IsNull(N.LeadTime,0),
                                                             Stockqty=Case When (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0))>0 Then
                                                                                      ( Case When IsNull(I.BatchAppendQty,0)=0 Then   
                                                                                                 Case When  (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End
                                                                                        Else Case  When  (Case When  (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0)-IsNull(N.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End)<=IsNull(I.BatchAppendQty,0) Then
                                                                                                             IsNull(I.BatchAppendQty,0)  Else  Case When  Convert(int,(Case When  (N.Maoqty-N.Stockqty+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End))%Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)=0
                                                                                                             Then   (Case When  (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End)
                                                                                                    Else   ( (Case When  (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(N.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End)/Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End +1)*IsNull(I.BatchAppendQty,0)
                                                                                          End  End End)*IsNull(D.Yield,100)/100+IsNull(N.StockQty,0) - IsNull(N.Maoqty,0)
                                                                                  Else    (IsNull(N.Stockqty,0) - IsNull(N.Maoqty,0))  End,                                                                                         
                                                             NeedQty=Case When (IsNull(N.Maoqty,0)-IsNull(N.Stockqty,0))>0 Then (IsNull(N.Maoqty,0)-IsNull(N.Stockqty,0)+IsNull(I.SecInv,0))
                                                                                              Else 0  End                                                             
                                             From     W_MRPData M Inner Join (Select ItemNo,Maoqty,JNeedQty,StockQty,LeadTime
                                                                                                                  From #NextqtyA1)N On M.ItemNo = N.ItemNo
                                            Left Join  VB_Items I  On M.ItemNo = I.ItemNo
                                            Left Join (Select  Distinct  Yield,ItemNo From B_ItemBomMas ) D On N.ItemNo = D.ItemNo
                                             Where  CalcuNo = @CalcuNo And Flag = 1 And TimeArea = 1 

                                             If   @@Error <> 0
                                             Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算修改數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                              End
                                    End
                                    Else
                                    Begin
                                                 Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                                        Stockqty, PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                 Select   @CalcuNo,@FirstI + 1  As TimeArea,N.ItemNO,PartNo,PartName,Model,VendorNo, Vendor,
                                                                 Maoqty,Case When (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))) > 0 Then
                                                                                       (Case When IsNull(I.BatchAppendQty,0) = 0 Then   
                                                                                                 Case When  (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End
                                                                                        Else Case  When  (Case When  (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End)<=IsNull(I.BatchAppendQty,0) Then
                                                                                                             IsNull(I.BatchAppendQty,0)  Else  Case When  Convert(int,(Case When  (N.Maoqty-(VPoqs+HCQty+PDQty)+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End))%Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)=0
                                                                                                             Then   (Case When  (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0)) + IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End)
                                                                                                    Else   ( (Case When  (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0))+IsNull(I.SecInv,0))*100/IsNull(D.Yield,100) End)/Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End+1)*IsNull(I.BatchAppendQty,0)
                                                                                          End  End End)*IsNull(D.Yield,100)/100+(IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0)) - IsNull(Maoqty,0)
                                                                                  Else    (IsNull(VPoqs,0) + IsNull(HCQty,0) + IsNull(PDQty,0) - IsNull(N.Maoqty,0))  End   As Stockqty,   
                                                                                 0 As PlanIn,
                                                                 Case When (IsNull(Maoqty,0)-(IsNull(VPoqs,0)+IsNull(HCQty,0)+IsNull(PDQty,0)))>0 Then  (IsNull(Maoqty,0)-(IsNull(VPoqs,0)+IsNull(HCQty,0)+IsNull(PDQty,0)))+IsNull(I.SecInv,0)
                                                                             Else 0 End  As Needqty,0 As  PlanNum ,
                                                                 0 As  PlanPoNum,NeedDate,LeadTime,1 As Flag
                                                 From  #NextqtyA1 N Left Join (Select  ItemNo,SecInv,BatchAppendQty,MinQty From VB_Items) I On N.ItemNo=I.ItemNo
                                                 Left Join (Select  Distinct  Yield,ItemNo From B_ItemBomMas) D On N.ItemNo=D.ItemNo

                                                 If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End
                                    End
-- 插入采購記錄  For Purchse
                                    Select  @Count = 0
                                    Select  @aCount = 0

                                    Select  @Count =  Count(*)
                                    From  #TimeArea1
                                    Select  @aCount = Count(*)
                                    From  #ItemCount

                                    If   @Count <> @aCount
                                    Begin
                                                 Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                      Stockqty,PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                 Select   @CalcuNo,1 As TimeArea,M.ItemNO,PartNo,PartName,Model,VendorNo, Vendor,0 As Maoqty,
                                                                 ( IsNull(F.VPoqs,0)+IsNull(D.HCQty,0)+IsNull(D.PDQty,0))  As Stockqty ,
                                                                  IsNull(F.VPoqs,0) As PlanIn,0 As Needqty,0 As PlanNum ,0 As PlanPoNum,
                                                                 @aStartDate, 0,0 As Flag
                                                 From  #ItemCount   M Left  Join #Futureqty1 F  On M.ItemNo = F.ItemNo
                                                 Left Join  #KuCunQty  D  On M.ItemNo=D.ItemNo

                                                  If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End

                                                 Update   W_MRPData  Set  Maoqty = N.Maoqty,Needqty = N.Needqty,PlanPoNum = M.PlanPoNum + N.PlanPoNum,
                                                                    LeadTime = N.LeadTime,Stockqty = N.Stockqty,PlanIn = N.PlanIn
                                                  From W_MRPData M  Inner Join
                                                                                             (Select  ItemNo, Maoqty,Needqty,Stockqty,PlanIn,LeadTime,PlanPoNum
                                                                                               From #TimeArea1 )N On  M.ItemNo = N.ItemNo  
                                                  Where  CalcuNo = @CalcuNo  And TimeArea = @FirstI + 1 And Flag = 0

                                                  If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算修改數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End
                                    End
                                    Else
                                    Begin
                                                 Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                                         Stockqty, PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                 Select   CalcuNo,TimeArea,ItemNO,PartNo,PartName,Model,VendorNo, Vendor,Maoqty,
                                                                 Case When IsNull(Stockqty,0)<=0 Then 0 Else IsNull(Stockqty,0)  End ,PlanIn,Needqty,
                                                                 PlanNum , PlanPoNum,NeedDate,LeadTime,0 As Flag
                                                 From  #TimeArea1

                                                 If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End
                                    End      
                                    Drop table  #Futureqty1 
                                    Drop table  #Nextqty1   
                                    Drop table  #KuCunQty
                                    Drop table  #NextqtyA1          
                                    Drop table  #TimeArea1           

                       End
                       Else     --其他時間區段   
                       Begin
--  For  Self-Make
                                    Select  @Count = 0
                                    Select  @aCount = 0

                                    Select  @Count =  Count(*)
                                    From  #SelfCount
                                    Select  @aCount = Count(*)
                                    From  #NextqtyA

                                    If    @Count <> @aCount
                                    Begin
                                                 Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                                        Stockqty, PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                 Select    @CalcuNo,@FirstI + 1 As TimeArea,M.ItemNo,M.PartNo,M.PartName,M.Model,
                                                                  '' As VendorNo,'' As Vendor, 0 As Maoqty,D.Stockqty,0 As PlanIn,0 As Needqty,
                                                                  0 As PlanNum,0 As PlanPoNum,@aStartDate, 0 As LeadTime,1 As Flag
                                                 From    #SelfCount  M
                                                 Left  Join (Select  ItemNo,Stockqty From  W_MRPData   Where CalcuNo = @CalcuNo  And Flag = 1
                                                                    And   TimeArea = @FirstI)  D On  M.ItemNo = D.ItemNo

                                                 If   @@Error <> 0
                                                 Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                 End   

                                                 Update  W_MRPData  Set  Maoqty =IsNull( N.Maoqty,0),Needqty = Case When  (IsNull(N.Maoqty,0)-IsNull(D.Stockqty,0))<=0 
                                                                                               Then  0   Else (IsNull(N.Maoqty,0)-IsNull(D.Stockqty,0))+IsNull(I.SecInv,0)  End,
                                                                 Stockqty = Case When (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0))>0 Then
                                                                                              ( Case When IsNull(I.BatchAppendQty,0)=0 Then   
                                                                                                     Case When  (N.Maoqty-D.Stockqty+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End
                                                                                                Else Case  When  (Case When  (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0) +IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0) +IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End)<=IsNull(I.BatchAppendQty,0) Then
                                                                                                             IsNull(I.BatchAppendQty,0)  Else  Case When  Convert(int,(Case When  (IsNull(N.Maoqty,0)-IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End))%Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)=0
                                                                                                             Then   (Case When  (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End)
                                                                                                    Else   ( (Case When  (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(N.Maoqty,0) - IsNull(D.Stockqty,0) +IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End)/Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End+1)*IsNull(I.BatchAppendQty,0)
                                                                                                   End  End End)*IsNull(O.Yield,100)/100+IsNull(D.Stockqty,0) - IsNull(N.Maoqty,0)
                                                                                    Else    (IsNull(D.Stockqty,0)-IsNull(N.Maoqty,0))  End,
                                                                                               NeedDate=Case   When  (@FirstI+1-M.LeadTime)<=0 then @aStartDate
                                                                                               Else (Select StartDate From B_TimeCircle Where Sn=@FirstI+1-M.LeadTime)
                                                                                               End                                                                                                      
                                                From W_MRPData M   Inner Join (Select  ItemNo, Maoqty,NeedQty,LeadTime
                                                                                                                   From #NextqtyA )N  On  M.ItemNo = N.ItemNo 
                                                 Left Join (Select  ItemNo,Stockqty  From  W_MRPData  Where CalcuNo = @CalcuNo And Flag = 1
                                                                                                                   And TimeArea=@FirstI)D On M.ItemNo=D.ItemNo
                                                 Left Join (Select ItemNo,MinQty,BatchAppendQty,SecInv From VB_Items) I  On M.ItemNo = I.ItemNo
                                                 Left Join (Select  Distinct Yield,ItemNo From B_ItemBomMas) O On M.ItemNo = O.ItemNo
                                                 Where CalcuNo = @CalcuNo And TimeArea = @FirstI + 1   And  Flag = 1

                                                  If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算修改數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End
                                    End
                                    Else
                                    Begin
                                                 Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                  Stockqty, PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                 Select    @CalcuNo,@FirstI+1 As TimeArea,M.ItemNo,M.PartNo,M.PartName,M.Model,M.VendorNo,
                                                                  M.Vendor,IsNull(M.Maoqty,0)As Maoqty,Case When (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0))>0 Then
                                                                                      ( Case When IsNull(I.BatchAppendQty,0)=0 Then   
                                                                                                 Case When  (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else  (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End
                                                                                        Else Case  When  (Case When  (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End)<=IsNull(I.BatchAppendQty,0) Then
                                                                                                             IsNull(I.BatchAppendQty,0)  Else  Case When Convert(int, (Case When  (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End))%Convert(int,Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End)=0
                                                                                                             Then   (Case When  (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0) +IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0)+IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End)
                                                                                                    Else   ( (Case When  (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100)<=IsNull(I.MinQty,0) Then
                                                                                                             IsNull(I.MinQty,0) Else   (IsNull(M.Maoqty,0) - IsNull(D.Stockqty,0) + IsNull(I.SecInv,0))*100/IsNull(O.Yield,100) End)/Case When IsNull(I.BatchAppendQty,0)=0 Then 1 Else IsNull(I.BatchAppendQty,0) End+1)*IsNull(I.BatchAppendQty,0)
                                                                                          End  End End)*IsNull(O.Yield,100) + IsNull(D.Stockqty,0) - IsNull(M.Maoqty,0)
                                                                                  Else    (IsNull(D.Stockqty,0) - IsNull(M.Maoqty,0))  End  As Stockqty, 0 As PlanIn,
                                                                  Case When (IsNull(M.Maoqty,0)-IsNull(D.Stockqty,0))>0 Then   (IsNull(M.Maoqty,0)-IsNull(D.Stockqty,0))+IsNull(I.SecInv,0)
                                                                  Else 0  End  As  NeedQty,
                                                                  0 As PlanNum,0 As PlanPoNum,
                                                                  Case   When  (@FirstI+1-M.LeadTime)<=0 then @aStartDate
                                                                                 Else (Select StartDate From B_TimeCircle Where Sn=@FirstI+1-M.LeadTime)
                                                                    End ,M.LeadTime,1 As Flag
                                                 From    #NextqtyA   M   Left  Join
                                                              (Select  ItemNo,Stockqty From  W_MRPData   Where CalcuNo = @CalcuNo   And  Flag = 1
                                                                                                                And   TimeArea=@FirstI)  D On  M.ItemNo=D.ItemNo
                                                 Left Join (Select  ItemNo,MinQty,BatchAppendQty,SecInv From VB_Items) I On M.ItemNo = I.ItemNo
                                                 Left Join (Select  Distinct  Yield,ItemNo From B_ItemBomMas) O On M.ItemNo = O.ItemNo
                                                 Where  M.NeedDate >= @aStartDate  And  M.NeedDate <= @aEndDate

                                                 If   @@Error <> 0
                                                 Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                 End   
                                    End
--  For  Purchase
                                    Select  @Count = 0
                                    Select  @aCount = 0
  
                                    Select  @Count =  Count(*)
                                    From  #TimeArea2
                                    Select   @aCount = Count(*)
                                    From #ItemCount
                                    If   @Count <> @aCount
                                    Begin
                                                 Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                 Stockqty,PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                 Select   @CalcuNo,(@FirstI+1) As TimeArea,M.ItemNO,M.PartNo,M.PartName,M.Model,M.VendorNo,
                                                                 M.Vendor,0 As Maoqty,Case When (IsNull(D.Stockqty,0)+IsNull(F.VPoqs,0))>0 Then (IsNull(D.Stockqty,0)+IsNull(F.VPoqs,0)) Else 0 End Stockqty ,
                                                                 IsNull(F.VPoqs,0) As PlanIn,0 As Needqty,0 As PlanNum ,0 As PlanPoNum,@aStartDate, 0,0 As Flag 
                                                 From  #ItemCount   M  Left  Join W_MRPData  D 
                                                 On (M.CalcuNo=D.CalcuNo And M.ItemNo=D.ItemNo And D.TimeArea=@FirstI   And  D.Flag = 0)
                                                 Left  Join #Futureqty  F  On M.ItemNo=F.ItemNo

                                                  If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End

                                                 Update   W_MRPData  Set  Maoqty = IsNull(N.Maoqty,0),Needqty=IsNull(N.Needqty,0),Stockqty=IsNull(N.Stockqty,0),
                                                                                                        LeadTime=IsNull(N.LeadTime,0), PlanIn = IsNull(N.PlanIn,0),PlanPoNum=IsNull(N.PlanPoNum,0)
                                                 From W_MRPData M   Inner Join(Select  ItemNo, Maoqty,Needqty,Stockqty,PlanIn,LeadTime,
                                                                                              PlanPoNum From #TimeArea2 )N On  M.ItemNo = N.ItemNo 
                                                 Where CalcuNo = @CalcuNo And TimeArea = @FirstI + 1   And  Flag = 0

                                                  If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算修改數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End
                                   End
                                   Else
                                   Begin
                                                  Insert W_MRPData(CalcuNo,TimeArea,ItemNo,PartNo,PartName,Model,VendorNo,Vendor,Maoqty,
                                                                  Stockqty,PlanIn,Needqty,PlanNum,PlanPoNum,NeedDate,LeadTime,Flag)
                                                  Select    CalcuNo, (@FirstI + 1) As TimeArea, ItemNO,PartNo,PartName,Model,VendorNo,Vendor,
                                                                   Maoqty, Case  When IsNull(Stockqty,0)<=0 Then 0 Else IsNull(Stockqty,0) End ,
                                                                   PlanIn, Needqty,0  As PlanNum,PlanPoNum,NeedDate,LeadTime, 0 As Flag
                                                  From   #TimeArea2

                                                  If   @@Error <> 0
                                                  Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                                  End
                                    End   
                                   Drop table  #NextqtyA     
                                   Drop table  #Nextqty       
                                   Drop table  #Futureqty 
                                   Drop table  #TimeArea2                  
                       End
                       Select  @FirstI = @FirstI + 1
                       Fetch Next From Time_Result   Into @aStartDate,  @aEndDate
          End  
          Close  Time_Result
          DeAllocate Time_Result
          Drop table  #T2
          Drop table #ItemCount
          Drop table #SelfCount
--取得淨需求量﹐計划訂貨量﹐計划訂單下達
Declare    @aTimeArea  Int
Declare    @aItemNo       Varchar(30)
Declare    @Needqty       Decimal(20,5)
Declare    @aLeadTime Int
Declare    @bCount Int

     Select   @bCount = Count(*)  
     From  W_MRPData
     Where  CalcuNo = @CalcuNo
     And PlanPoNum > 0   And  Flag = 0

     If  @bCount = 0  
     Begin
                Set    @Returnid = 1
                Set    @ErrMsg = 'MRP運算完畢!'
                GoTo ExitLable
     End
     Else
     Begin
                   Select  TimeArea,ItemNo,PlanPoNum,Convert(Int,LeadTime) LeadTime 
                   Into   #T8  
                   From  W_MRPData
                   Where  CalcuNo = @CalcuNo
                   And PlanPoNum > 0   And  Flag = 0
                   Order by ItemNo,TimeArea

                               Declare  Update_table   Cursor    For   Select   TimeArea,ItemNo,PlanPoNum,LeadTime  From #T8
                               Open  Update_table
                               Fetch Next From Update_table   Into @aTimeArea,  @aItemNo  ,@Needqty,@aLeadTime
                               While @@Fetch_Status =  0
                               Begin
                                              Select  @bCount = Count( *)  From W_MRPData
                                              Where CalcuNo = @CalcuNo
                                              And  ItemNo = @aItemNo
                                              And  TimeArea  = @aTimeArea  -  @aLeadTime
                                              And  Flag = 0

                                              If   @bCount  > 0 
                                               Begin                                                             
                                                            If  (@aLeadTime <> 0) 
                                                            Begin
                                                                        Update  W_MRPData  Set  PlanPoNum = IsNull(PlanPoNum,0) + IsNull(@Needqty,0) 
                                                                        Where  CalcuNo = @CalcuNo
                                                                        And  ItemNo = @aItemNo
                                                                        And  TimeArea = @aTimeArea - @aLeadTime 
                                                                        And  Flag = 0

                                                                        Update  W_MRPData  Set  PlanPoNum =  0 
                                                                        Where  CalcuNo = @CalcuNo
                                                                        And  ItemNo = @aItemNo
                                                                        And  TimeArea = @aTimeArea
                                                                        And  Flag = 0
                                                           End
                                               End
                                               Else
                                               Begin
                                                            If   @aTimeArea<>1
                                                            Begin
                                                                        Update  W_MRPData  Set  PlanPoNum = IsNull(PlanPoNum,0) + IsNull(@Needqty,0) 
                                                                        Where  CalcuNo = @CalcuNo
                                                                        And  ItemNo = @aItemNo
                                                                        And  TimeArea = 1
                                                                        And  Flag = 0

                                                                        Update  W_MRPData  Set  PlanPoNum =  0 
                                                                        Where  CalcuNo = @CalcuNo
                                                                        And  ItemNo = @aItemNo
                                                                        And  TimeArea = @aTimeArea
                                                                        And  Flag = 0                                                                      
                                                            End
                                               End

                                               If   @@Error <> 0
                                               Begin
                                                             Set   @Returnid = 0
                                                             Set   @ErrMsg = 'MRP計算插入數據出錯﹐請與電腦部聯系!'
                                                             GoTo ExitLable         
                                               End
                                             Fetch Next From Update_table   Into  @aTimeArea,  @aItemNo  ,@Needqty,@aLeadTime
                               End
                               Close  Update_table
                               DeAllocate  Update_table       
                     
                   Set    @Returnid = 1
                   Set    @ErrMsg = 'MRP運算完畢!'
                   GoTo ExitLable
     End
-----------------------------------------------------------------------------------------------------------------------------
ExitLable:
 If @Returnid = 0   
 Begin
     ROLLBACK TRANSACTION    
 End
 Else
 Begin
             COMMIT TRANSACTION     
             Drop table #TA1
             Drop table #T5
             Drop table #T6
             Drop table #Result1
             Drop table #Result2
             Drop table #SelfMasterResult1
             Drop table #SelfMasterResult2
  End

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值