» » Nebim v3 Mobil - Rezerve ve Sevk Emri Düşülmüş Envanter Gösterme

Nebim v3 Mobil - Rezerve ve Sevk Emri Düşülmüş Envanter Gösterme

Sql üzerinde ItemInventoryForProcess fonksiyonu üzerinde değişiklik yapıyoruz.

Eski fonksiyonumuzu drop and create to diyerek önceklikle yedekliyoruz.

Sonra 1. Satırdaki [VERİTABANI] yazan yere kendi bilgilerimizi giriyoruz ve çalıştırıyoruz.

Artık el terminalimizde Envanter seçilen depo için Gerçek Envater = Envanter - (Rezerve Miktarı + Sevk emri miktarı ) formuluyle çalışıyor.




USE [VERITABANI]
GO


DROP FUNCTION [dbo].[ItemInventoryForProcess]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[ItemInventoryForProcess]
    (  
        @WarehouseCode                        Char10 
      , @StoreCode                            Char30  
      , @InventoryOfWarehouse                bit
      , @ItemTypeCode                        tinyint
      , @ItemCode                            Char30
      , @ColorCode                            Char10     = N''
      , @ItemDim1Code                        Char10     = N''
      , @ItemDim2Code                        Char10     = N''
      , @ItemDim3Code                        Char10     = N''
      , @InventoryDate                        Date     = NULL 
      , @CurrAccTypeCode                    tinyint = 0
      , @CurrAccCode                        Char30    = N''
      , @SubCurrAccID                        uniqueidentifier = NULL
      , @ProcessFlowCode                    tinyint = 0
      , @ColorDimDetail                        bit    = 0
      , @AddRemainingOrdersOnInventory        bit = 0
    ) 
RETURNS TABLE

AS RETURN
(
    SELECT ColorCode , ItemDim1Code , ItemDim2Code , ItemDim3Code
         , In_Qty1                            = SUM(In_Qty1)
         , Out_Qty1                            = SUM(Out_Qty1)
         , InventoryQty1                    = SUM(    (In_Qty1 - Out_Qty1) - 
                                                                                                (    TotalRemainingReserveQty1 + 
                                                                                                    TotalRemainingDispOrderQty1 + 
                                                                                                    CurrAccRemainingDispOrderQty1 + 
                                                                                                    TotalRemainingPickingQty1 + 
                                                                                                    CurrAccRemainingPickingQty1
                                                                                                )
                                                                                            ) 
         , TotalRemainingReserveQty1        = SUM(TotalRemainingReserveQty1 + TotalRemainingDispOrderQty1)
         , CurrAccRemainingReserveQty1        = SUM(CurrAccRemainingReserveQty1 + CurrAccRemainingDispOrderQty1)
         , TotalRemainingPickingQty1        = SUM(TotalRemainingPickingQty1)
         , CurrAccRemainingPickingQty1        = SUM(CurrAccRemainingPickingQty1)
         , AvailableInventoryQty1            = CASE  
                                                    WHEN @ProcessFlowCode = 3        THEN SUM(    (In_Qty1 - Out_Qty1) - 
                                                                                                (    TotalRemainingReserveQty1 + 
                                                                                                    CurrAccRemainingReserveQty1 + 
                                                                                                    TotalRemainingDispOrderQty1 + 
                                                                                                    CurrAccRemainingDispOrderQty1 + 
                                                                                                    TotalRemainingPickingQty1 + 
                                                                                                    CurrAccRemainingPickingQty1
                                                                                                )
                                                                                            ) 
                                                    WHEN @ProcessFlowCode = 4        THEN SUM(    (In_Qty1 - Out_Qty1) - 
                                                                                                (    TotalRemainingReserveQty1 + 
                                                                                                    TotalRemainingDispOrderQty1 + 
                                                                                                    CurrAccRemainingDispOrderQty1 + 
                                                                                                    TotalRemainingPickingQty1 + 
                                                                                                    CurrAccRemainingPickingQty1
                                                                                                )
                                                                                            ) 
                                                    WHEN @ProcessFlowCode = 5        THEN SUM(    (In_Qty1 - Out_Qty1) - 
                                                                                                (    TotalRemainingReserveQty1 + 
                                                                                                    TotalRemainingDispOrderQty1 + 
                                                                                                    TotalRemainingPickingQty1 + 
                                                                                                    CurrAccRemainingPickingQty1
                                                                                                )
                                                                                            ) 
                                                    WHEN @ProcessFlowCode IN(0,6,7)    THEN SUM(    (In_Qty1 - Out_Qty1) - 
                                                                                                (    TotalRemainingReserveQty1 + 
                                                                                                    TotalRemainingDispOrderQty1 + 
                                                                                                    TotalRemainingPickingQty1 
                                                                                                )
                                                                                            ) 
                                                    ELSE SUM(    (In_Qty1 - Out_Qty1) - 
                                                                                                (    TotalRemainingReserveQty1 + 
                                                                                                    TotalRemainingDispOrderQty1 + 
                                                                                                    CurrAccRemainingDispOrderQty1 + 
                                                                                                    TotalRemainingPickingQty1 + 
                                                                                                    CurrAccRemainingPickingQty1
                                                                                                )
                                                                                            )
                                            END    
    FROM
    (
    
    SELECT ColorCode    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ColorCode    END
         , ItemDim1Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim1Code END
         , ItemDim2Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim2Code END
         , ItemDim3Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim3Code END
         , In_Qty1                            = SUM(In_Qty1)
         , Out_Qty1                            = SUM(Out_Qty1)
         , InventoryQty1                    = SUM(In_Qty1 - Out_Qty1) 
         , TotalRemainingReserveQty1        = 0
         , CurrAccRemainingReserveQty1        = 0
         , TotalRemainingDispOrderQty1        = 0
         , CurrAccRemainingDispOrderQty1    = 0
         , TotalRemainingPickingQty1        = 0
         , CurrAccRemainingPickingQty1        = 0
        
        FROM trStock WITH(NOLOCK)
        WHERE 
            
                CASE @InventoryOfWarehouse WHEN 1 THEN trStock.WarehouseCode    ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 1 THEN @WarehouseCode ELSE SPACE(0)    END
            AND CASE @InventoryOfWarehouse WHEN 0 THEN trStock.StoreCode        ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 0 THEN @StoreCode        ELSE SPACE(0)    END
            AND trStock.StoreTypeCode    = 5
            AND trStock.ItemTypeCode    = @ItemTypeCode
            AND trStock.ItemCode        = @ItemCode
            AND CASE @ColorCode        WHEN SPACE(0) THEN SPACE(0) ELSE trStock.ColorCode        END    = @ColorCode        
            AND CASE @ItemDim1Code    WHEN SPACE(0) THEN SPACE(0) ELSE trStock.ItemDim1Code    END = @ItemDim1Code
            AND CASE @ItemDim2Code    WHEN SPACE(0) THEN SPACE(0) ELSE trStock.ItemDim2Code    END = @ItemDim2Code
            AND CASE @ItemDim3Code    WHEN SPACE(0) THEN SPACE(0) ELSE trStock.ItemDim3Code    END = @ItemDim3Code
            AND CASE WHEN @InventoryDate IS NULL  THEN SPACE(0) ELSE trStock.OperationDate  END <= CASE WHEN @InventoryDate IS NULL        THEN SPACE(0) ELSE @InventoryDate    END 
        GROUP BY CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ColorCode    END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim1Code END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim2Code END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim3Code END
    UNION ALL    
    SELECT ColorCode    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ColorCode    END
         , ItemDim1Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim1Code END
         , ItemDim2Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim2Code END
         , ItemDim3Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim3Code END
         , In_Qty1                            = SUM(ISNULL(stOrder.Qty1, 0))
         , Out_Qty1                            = 0
         , InventoryQty1                    = SUM(ISNULL(stOrder.Qty1, 0))
         , TotalRemainingReserveQty1        = 0
         , CurrAccRemainingReserveQty1        = 0
         , TotalRemainingDispOrderQty1        = 0
         , CurrAccRemainingDispOrderQty1    = 0
         , TotalRemainingPickingQty1        = 0
         , CurrAccRemainingPickingQty1        = 0
        
        FROM  AllOrderLines 
            , (              SELECT OrderLineID, Qty1 FROM stOrder 
                UNION ALL SELECT OrderLineID, Qty1 FROM stOrderAsn
             ) AS stOrder
        WHERE @AddRemainingOrdersOnInventory    = 1
            AND AllOrderLines.OrderTypeCode        = 3                  
            AND AllOrderLines.OrderLineID        = stOrder.OrderLineID
            
            AND    CASE @InventoryOfWarehouse WHEN 1 THEN AllOrderLines.WarehouseCode    ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 1 THEN @WarehouseCode ELSE SPACE(0)    END
            AND CASE @InventoryOfWarehouse WHEN 0 THEN AllOrderLines.StoreCode        ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 0 THEN @StoreCode        ELSE SPACE(0)    END
            AND AllOrderLines.StoreTypeCode    = 5
            AND AllOrderLines.ItemTypeCode    = @ItemTypeCode
            AND AllOrderLines.ItemCode        = @ItemCode
            AND CASE @ColorCode        WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ColorCode        END    = @ColorCode        
            AND CASE @ItemDim1Code    WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ItemDim1Code        END = @ItemDim1Code
            AND CASE @ItemDim2Code    WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ItemDim2Code        END = @ItemDim2Code
            AND CASE @ItemDim3Code    WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ItemDim3Code        END = @ItemDim3Code            
        GROUP BY CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ColorCode    END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim1Code END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim2Code END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim3Code END
    UNION ALL
    --reserve kalani, disporder kalani, pick kalan 
    SELECT ColorCode    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ColorCode    END
         , ItemDim1Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim1Code END
         , ItemDim2Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim2Code END
         , ItemDim3Code    = CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim3Code END
         , In_Qty1                            = 0
         , Out_Qty1                            = 0
         , InventoryQty1                    = 0
         , TotalRemainingReserveQty1        = SUM(CASE WHEN  NOT (    AllOrderLines.CurrAccTypeCode = @CurrAccTypeCode 
                                                                AND AllOrderLines.CurrAccCode = @CurrAccCode 
                                                                AND ISNULL(AllOrderLines.SubCurrAccID, '00000000-0000-0000-0000-000000000000') = ISNULL(@SubCurrAccID, '00000000-0000-0000-0000-000000000000'))
                                                      THEN ISNULL(CurrAccRemainingReserves.Qty1 , 0) ELSE 0 END)
         , CurrAccRemainingReserveQty1        = SUM(CASE WHEN        (    AllOrderLines.CurrAccTypeCode = @CurrAccTypeCode 
                                                                AND AllOrderLines.CurrAccCode = @CurrAccCode 
                                                                AND ISNULL(AllOrderLines.SubCurrAccID, '00000000-0000-0000-0000-000000000000') = ISNULL(@SubCurrAccID, '00000000-0000-0000-0000-000000000000'))
                                                      THEN ISNULL(CurrAccRemainingReserves.Qty1 , 0) ELSE 0 END)
         , TotalRemainingDispOrderQty1        = SUM(CASE WHEN  NOT (    AllOrderLines.CurrAccTypeCode = @CurrAccTypeCode 
                                                                AND AllOrderLines.CurrAccCode = @CurrAccCode 
                                                                AND ISNULL(AllOrderLines.SubCurrAccID, '00000000-0000-0000-0000-000000000000') = ISNULL(@SubCurrAccID, '00000000-0000-0000-0000-000000000000'))
                                                      THEN ISNULL(CurrAccRemainingDispOrders.Qty1 , 0) ELSE 0 END)
         , CurrAccRemainingDispOrderQty1    = SUM(CASE WHEN         (    AllOrderLines.CurrAccTypeCode = @CurrAccTypeCode 
                                                                AND AllOrderLines.CurrAccCode = @CurrAccCode 
                                                                AND ISNULL(AllOrderLines.SubCurrAccID, '00000000-0000-0000-0000-000000000000') = ISNULL(@SubCurrAccID, '00000000-0000-0000-0000-000000000000'))
                                                      THEN ISNULL(CurrAccRemainingDispOrders.Qty1 , 0) ELSE 0 END)
         , TotalRemainingPickingQty1        = SUM(CASE WHEN  NOT (    AllOrderLines.CurrAccTypeCode = @CurrAccTypeCode 
                                                                AND AllOrderLines.CurrAccCode = @CurrAccCode 
                                                                AND ISNULL(AllOrderLines.SubCurrAccID, '00000000-0000-0000-0000-000000000000') = ISNULL(@SubCurrAccID, '00000000-0000-0000-0000-000000000000'))
                                                      THEN ISNULL(CurrAccRemainingPickings.Qty1 , 0) ELSE 0 END)
         , CurrAccRemainingPickingQty1    = SUM(CASE WHEN         (    AllOrderLines.CurrAccTypeCode = @CurrAccTypeCode 
                                                                AND AllOrderLines.CurrAccCode = @CurrAccCode 
                                                                AND ISNULL(AllOrderLines.SubCurrAccID, '00000000-0000-0000-0000-000000000000') = ISNULL(@SubCurrAccID, '00000000-0000-0000-0000-000000000000'))
                                                      THEN ISNULL(CurrAccRemainingPickings.Qty1 , 0) ELSE 0 END)

        FROM AllOrderLines
            LEFT OUTER JOIN (SELECT stReserve.OrderLineID , Qty1 = SUM(stReserve.Qty1)
                                FROM stReserve WITH(NOLOCK) , AllReserveLines
                                WHERE stReserve.ReserveLineID = AllReserveLines.ReserveLineID
                                    AND CASE @InventoryOfWarehouse WHEN 1 THEN AllReserveLines.WarehouseCode    ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 1 THEN @WarehouseCode ELSE SPACE(0)    END
                                    AND CASE @InventoryOfWarehouse WHEN 0 THEN AllReserveLines.StoreCode        ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 0 THEN @StoreCode        ELSE SPACE(0)    END
                                    AND AllReserveLines.StoreTypeCode    = 5        
                                GROUP BY stReserve.OrderLineID                             
                            ) AS CurrAccRemainingReserves
            ON CurrAccRemainingReserves.OrderLineID = AllOrderLines.OrderLineID

            LEFT OUTER JOIN (SELECT stDispOrder.OrderLineID , Qty1 = SUM(stDispOrder.Qty1)
                                FROM stDispOrder WITH(NOLOCK) , AllDispOrderLines
                                WHERE stDispOrder.DispOrderLineID = AllDispOrderLines.DispOrderLineID
                                    AND CASE @InventoryOfWarehouse WHEN 1 THEN AllDispOrderLines.WarehouseCode    ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 1 THEN @WarehouseCode ELSE SPACE(0)    END
                                    AND CASE @InventoryOfWarehouse WHEN 0 THEN AllDispOrderLines.StoreCode        ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 0 THEN @StoreCode        ELSE SPACE(0)    END
                                    AND AllDispOrderLines.StoreTypeCode    = 5        
                                GROUP BY stDispOrder.OrderLineID                             
                            ) AS CurrAccRemainingDispOrders
            ON CurrAccRemainingDispOrders.OrderLineID = AllOrderLines.OrderLineID

            LEFT OUTER JOIN (SELECT stPicking.OrderLineID , Qty1 = SUM(stPicking.Qty1)
                                FROM stPicking WITH(NOLOCK) , AllPickingLines
                                WHERE stPicking.PickingLineID = AllPickingLines.PickingLineID
                                    AND CASE @InventoryOfWarehouse WHEN 1 THEN AllPickingLines.WarehouseCode    ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 1 THEN @WarehouseCode ELSE SPACE(0)    END
                                    AND CASE @InventoryOfWarehouse WHEN 0 THEN AllPickingLines.StoreCode        ELSE SPACE(0)    END = CASE @InventoryOfWarehouse WHEN 0 THEN @StoreCode        ELSE SPACE(0)    END
                                    AND AllPickingLines.StoreTypeCode    = 5        
                                GROUP BY stPicking.OrderLineID                             
                            ) AS CurrAccRemainingPickings
            ON CurrAccRemainingPickings.OrderLineID = AllOrderLines.OrderLineID

        WHERE AllOrderLines.ItemTypeCode        = @ItemTypeCode
            AND AllOrderLines.ItemCode            = @ItemCode
            AND CASE @ColorCode        WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ColorCode    END    = @ColorCode        
            AND CASE @ItemDim1Code    WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ItemDim1Code    END = @ItemDim1Code
            AND CASE @ItemDim2Code    WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ItemDim2Code    END = @ItemDim2Code
            AND CASE @ItemDim3Code    WHEN SPACE(0) THEN SPACE(0) ELSE AllOrderLines.ItemDim3Code    END = @ItemDim3Code
            
        GROUP BY CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ColorCode    END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim1Code END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim2Code END
               , CASE WHEN @ColorDimDetail = 0 THEN SPACE(0) ELSE ItemDim3Code END
    ) AS Inventory    
    GROUP BY ColorCode , ItemDim1Code , ItemDim2Code , ItemDim3Code
    
)            


GO
Sayın ziyaretçi, siteyi kayıtsız bir kullanıcı olarak görüntülüyorsunuz. Siteye kayıt olarak gezinti deneyiminizi olumlu yönde arttırabilirsiniz.
Adınız: *
E-Posta adresiniz: *
Kod: Okunamayan kodu yenilemek için resmin üstüne tıklayınız
Kodu girin: