USE [ILS]
GO
/****** Object:  UserDefinedFunction [dbo].[KC_CHECK_APPLY]    Script Date: 3/16/2017 5:34:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <vbelko>
-- Create date: <01/03/17>
-- Description: <INVENTORY_ADJUSTMENT.Процедура запускаемая при нажатии "ОК" для выполнения корректировки.
-- Валидация LPN на доступное количество и что текущий INTERNAL_LOC_INV до сих пор соответствует переданному списку.
-- EXEC KC_CHECK_APPLY N'S',@list,1
--declare @list dbo.InvMgmtListGo
-- insert into @list (IntLocNum, Location, LPN,

-- allocated_qty, on_hand_qty, in_transit_qty
-- ,suspense_qty , inventory_sts , PLANT
-- ) values  ( 965,N'AA-09-34-7',N'AD_13_3_02','7','230','0','0','N','1000'  )


--select * from dbo.KC_CHECK_APPLY(N'S', @list, 1)
-- =============================================
ALTER function [dbo].[KC_CHECK_APPLY] (
   @TransType nvarchar(25)
  ,@list InvMgmtListGo READONLY
  ,@Num int
)
--таблица возвращаемая после всех обработок
returns @t table (
     row_num int
    ,IntLocNum int
    ,LPN nvarchar(50)
    ,Location nvarchar(25)
    ,error_msg nvarchar(25)
)
AS BEGIN
-- объявляем таблицу для записи ошибок

declare @error_table table (
     row_num int
    ,error_msg nvarchar(32)
)
 --объявляем таблицу в которую заносятся данные из @list


declare @__t table (
    row_num int, IntLocNum int, LPN nvarchar(50), Location nvarchar(25)
    ,allocated_qty numeric (19,5), on_hand_qty numeric (19,5), in_transit_qty numeric (19,5)
 ,suspense_qty numeric (19,5) ,inventory_sts nvarchar (25), PLANT nvarchar (25)
)
;with
     finish_table as (
        select
             ROW_NUMBER () over (order by IntLocNum) row_num
            ,IntLocNum, Location, LPN
            ,allocated_qty, on_hand_qty, in_transit_qty
            ,suspense_qty, inventory_sts, PLANT
        from @list
    )

insert into @__t (row_num, IntLocNum, LPN, Location,allocated_qty, on_hand_qty, in_transit_qty
            ,suspense_qty, inventory_sts, PLANT)
select row_num, IntLocNum, LPN, Location, allocated_qty, on_hand_qty, in_transit_qty
            , suspense_qty, inventory_sts, PLANT
from finish_table


--create error message table
declare
     @max int = (select max(row_num) from @__t)
    ,@i int = 1
    ,@msg nvarchar(25);
--заполнение таблицы с ошибками @error_table
while @i !> @max
begin

    if exists (
        select l.LPN
        from @__t l
             join LOCATION_INVENTORY li
                on l.IntLocNum = li.INTERNAL_LOCATION_INV
                and li.on_hand_qty = l.on_hand_qty
                and li.allocated_qty = l.allocated_qty
                and li.in_transit_qty = l.in_transit_qty
                and li.SUSPENSE_QTY = l.suspense_qty
                and li.LOCATION = l.Location
                and li.LOGISTICS_UNIT = l.LPN
            inner join KC_LPN kc
                on li.LOGISTICS_UNIT = kc.LPN
                and kc.ERP_PLANT = l.PLANT
        where l.row_num = @i
    )
    begin
        if (@TransType = N'S' or @TransType = 'W') and exists (
            select top 1 1
            from @__t l
                inner join location_inventory li on li.internal_location_inv = l.IntLocNum
                 join location loc on loc.LOCATION = li.LOCATION and li.LOCATION =  l.Location
                -- left join WORK_INSTRUCTION wi on li.LOGISTICS_UNIT = wi.LOGISTICS_UNIT

            where
                l.row_num = @i
                --and li.on_hand_qty > 0
                --and li.allocated_qty = 0
                --and li.in_transit_qty = 0
                --and loc.LOCATION_CLASS = 'Inventory'
                --and loc.ACTIVE = 'Y'
                --and loc.LOCATION_STS != 'Frozen'
                --and wi.CONDITION in (N'Open', N'In Process')
                --and (wi.LOGISTICS_UNIT is not null
                --and wi.LOGISTICS_UNIT = l.LPN)

        )
        begin
            set @msg = null
            insert into @error_table(row_num, error_msg) values (@i, @msg)
            set @i += 1
        end

        else if (@TransType = N'S' or @TransType = 'W') and exists (
            select top 1 1
            from @__t l
                 left join location_inventory li on l.IntLocNum=li.internal_location_inv
                 join location loc on loc.LOCATION = li.LOCATION
            where
                l.row_num = @i
                and li.allocated_qty > 0
                and loc.LOCATION_CLASS='Inventory'
                and loc.ACTIVE = 'Y'
                and loc.LOCATION_STS != 'Frozen'
                and li.LOCATION =  l.Location
    )
    begin
    set @msg = N'ERR01'
    insert into @error_table(row_num, error_msg) values (@i, @msg)
    set @i += 1

    end

else
    if (@TransType = N'S' or @TransType = 'W') and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        where
            l.row_num = @i
            and li.SUSPENSE_QTY > 0
            and loc.LOCATION_CLASS ='Inventory'
            and loc.ACTIVE = 'Y'
            and loc.LOCATION_STS != 'Frozen'
            and li.LOCATION =  l.Location
    )
            begin
            set @msg = 'ERR02'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1

            end
else
    if (@TransType = N'S' or @TransType = 'W') and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.ON_HAND_QTY = 0
        --  and loc.LOCATION_CLASS = 'Inventory'
        --  and loc.ACTIVE= 'Y'
        --  and loc.LOCATION_STS != 'Frozen'
        --  and li.LOCATION =  l.Location
    )
            begin
            set @msg = 'ERR03'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1

            end

else
    if (@TransType = N'S' or @TransType = 'W') and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.ON_HAND_QTY = 0
        --  and li.ALLOCATED_QTY = 0
        --  and li.IN_TRANSIT_QTY = 0
        --  and li.SUSPENSE_QTY = 0
        --  and loc.LOCATION_CLASS='Inventory'
        --  and loc.ACTIVE='Y'
        --  and loc.LOCATION_STS != 'Frozen'
        --  and li.LOCATION =  l.Location
    )
        begin
            set @msg = 'ERR04'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1

        end

if @TransType = 'T' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
             --left join WORK_INSTRUCTION wi on li.LOGISTICS_UNIT = wi.LOGISTICS_UNIT

        where
            l.row_num = @i
            and li.on_hand_qty > 0
            and li.allocated_qty = 0
            and li.in_transit_qty = 0
            and loc.LOCATION_CLASS <> 'Receiving Dock'
            --and loc.LOCATION_CLASS <> 'Shipping Dock'
            and loc.LOCATION_CLASS <> 'P&D'
            and loc.ACTIVE = 'Y'
            and loc.LOCATION_STS <> 'Frozen'
            and li.LOCATION =  l.Location
            --and wi.CONDITION in (N'Open', N'In Process')
            --and (wi.LOGISTICS_UNIT is not null
            --and wi.LOGISTICS_UNIT = l.LPN)
            --and loc.LOCATION=l.Location
        )
        begin
        set @msg = null
        insert into @error_table(row_num, error_msg) values (@i, @msg)
        set @i += 1

        end

else    if @TransType = N'T' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum=li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.allocated_qty > 0
        --  and loc.LOCATION_CLASS <> 'Receiving Dock'
        --  --and loc.LOCATION_CLASS <> 'Shipping Dock'
        --  and loc.LOCATION_CLASS <> 'P&D'
        --  and loc.ACTIVE = 'Y'
        --  and loc.LOCATION_STS <> 'Frozen'
        --  and li.LOCATION =  l.Location
    )
    begin
    set @msg = N'ERR01'
    insert into @error_table(row_num, error_msg) values (@i, @msg)
    set @i += 1

    end

else
    if @TransType = 'T' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.SUSPENSE_QTY > 0
        --  and loc.LOCATION_CLASS <> 'Receiving Dock'
        --  --and loc.LOCATION_CLASS <> 'Shipping Dock'
        --  and loc.LOCATION_CLASS <> 'P&D'
        --  and loc.ACTIVE = 'Y'
        --  and loc.LOCATION_STS <> 'Frozen'
        --  and li.LOCATION =  l.Location

    )
            begin
            set @msg = 'ERR02'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1

            end
else
    if @TransType = 'T' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.ON_HAND_QTY = 0
        --  and loc.LOCATION_CLASS <> 'Receiving Dock'
        --  --and loc.LOCATION_CLASS <> 'Shipping Dock'
        --  and loc.LOCATION_CLASS <> 'P&D'
        --  and loc.ACTIVE = 'Y'
        --  and loc.LOCATION_STS <> 'Frozen'
        --  and li.LOCATION =  l.Location
    )
            begin
            set @msg = 'ERR03'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1

            end

else
    if @TransType = 'T' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.ON_HAND_QTY = 0
        --  and li.ALLOCATED_QTY = 0
        --  and li.IN_TRANSIT_QTY = 0
        --  and li.SUSPENSE_QTY = 0
        --  and loc.LOCATION_CLASS <> 'Receiving Dock'
        --  --and loc.LOCATION_CLASS <> 'Shipping Dock'
        --  and loc.LOCATION_CLASS <> 'P&D'
        --  and loc.ACTIVE = 'Y'
        --  and loc.LOCATION_STS <> 'Frozen'
        --  and li.LOCATION =  l.Location
    )
        begin
            set @msg = 'ERR04'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1

        end

if @TransType = N'Q' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
             left join WORK_INSTRUCTION wi on li.LOGISTICS_UNIT = wi.LOGISTICS_UNIT

        --where
        --  l.row_num = @i
        --  and li.on_hand_qty > 0
        --  and li.allocated_qty = 0
        --  and li.in_transit_qty = 0
        --  and loc.LOCATION_CLASS = 'Inventory'
        --  and loc.LOCATION = l.Location

        )
        begin
        set @msg = null
        insert into @error_table(row_num, error_msg) values (@i, @msg)
        set @i += 1
        end
else
    if @TransType = N'Q' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum=li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.allocated_qty > 0
        --  and loc.LOCATION_CLASS = 'Inventory'
        --  and loc.LOCATION = l.Location
    )
    begin
    set @msg = N'ERR01'
    insert into @error_table(row_num, error_msg) values (@i, @msg)
    set @i += 1

    end

else
    if @TransType = 'Q' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.SUSPENSE_QTY > 0
        --  and li.LOCATION =  l.Location
        --  and loc.LOCATION_CLASS = 'Inventory'
        --  and loc.LOCATION = l.Location
    )
            begin
            set @msg = 'ERR02'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1
            end
else
    if @TransType = 'Q' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        where
            l.row_num = @i
            and li.ON_HAND_QTY = 0
            and loc.LOCATION_CLASS = 'Inventory'
            and loc.LOCATION = l.Location
    )
            begin
            set @msg = 'ERR03'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1
            end

else
    if @TransType = 'Q' and exists (
        select top 1 1
        from @__t l
             left join location_inventory li on l.IntLocNum = li.internal_location_inv
             join location loc on loc.LOCATION = li.LOCATION
        --where
        --  l.row_num = @i
        --  and li.ON_HAND_QTY = 0
        --  and li.ALLOCATED_QTY = 0
        --  and li.IN_TRANSIT_QTY = 0
        --  and li.SUSPENSE_QTY = 0
    )
        begin
            set @msg = 'ERR04'
            insert into @error_table(row_num, error_msg)  values (@i, @msg)
            set @i += 1
        end


else
set @msg = null
-- вставляем обработанные строки и возникшие для них ошибки
insert into @t (
     row_num
    ,IntLocNum
    ,LPN
    ,Location
    ,error_msg
)

select t.row_num, t.IntLocNum, t.LPN, t.Location, e.error_msg
from @__t t join (select row_num, error_msg from @error_table)e on e.row_num = t.row_num


end
-- else
--set @msg = 'ERR06'
-- insert into @t (
--   row_num
--  ,IntLocNum
--  ,LPN
--  ,Location
--  ,error_msg
--)

--select t.row_num, t.IntLocNum, t.LPN, t.Location, e.error_msg
--from @__t t join (select row_num, error_msg from @error_table)e on e.row_num = t.row_num

end



return
END