USE [ILS]
GO

/****** Object:  StoredProcedure [dbo].[KC_EXP]    Script Date: 3/20/2017 6:22:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: vbelko
-- Create date: 22.12.2016
-- Description:
-- Moderate:
-- EXEC KC_EXP
-- =============================================
ALTER PROCEDURE [dbo].[KC_EXP]
    -- Add the parameters for the stored procedure here
AS

BEGIN TRY
    begin transaction t1
        --создание временной таблицы для отображения смены статуса в TRANSACTION_HISTORY
        create table ##tblEXPR (
             id smallint identity(1, 1) primary key
            ,lpn nvarchar(25) null
            ,inventory_sts nvarchar(50) null
            ,inventory_sts_after nvarchar(50) null
        );

        --создание временной таблицы для отображения смены склада в TRANSACTION_HISTORY
        create table ##tblEXPR_ERP (
             id smallint identity(1, 1) primary key
            ,lpn nvarchar(25) null
            ,plant_before nvarchar(50) null
            ,plant_after nvarchar(50)
        );
        if exists (
            select logistics_unit
            from location_inventory li
                join KC_LPN kc on li.logistics_unit=kc.lpn
            where
                li.on_hand_qty > 0
                and li.allocated_qty = 0
                and li.in_transit_qty = 0
        )
            update li
            set INVENTORY_STS = (
                select top 1 gc.USER3VALUE
                from LOCATION_INVENTORY li
                    left join location lc on li.location = lc.LOCATION
                    left join KC_LPN kc on li.logistics_unit = kc.lpn
                    left join ITEM i on li.item = i.ITEM
                    left join KC_MOVE_TYPE kcm on li.INVENTORY_STS = kcm.STS_BEFORE and kc.ERP_PLANT = kcm.WHS_BEFORE
                    inner join GENERIC_CONFIG_DETAIL gc on kcm.STS_AFTER = gc.USER3VALUE and i.ITEM_CATEGORY8 = gc.USER1VALUE
                where
                    gc.RECORD_TYPE = 'KC_EXPIRATION_LIST'
                    and li.USER_DEF1 <= gc.USER2VALUE
                    and lc.LOCATION_CLASS = 'Inventory'
                order by gc.USER2VALUE asc
            )
            output deleted.logistics_unit, deleted.INVENTORY_STS, inserted.INVENTORY_STS INTO ##tblEXPR
            from LOCATION_INVENTORY li

        --Объявление переменных для записи изменений в transaction_history
        declare
             @stBeforeSts nvarchar(50)
            ,@stTransType nvarchar(25)
            ,@stWhs nvarchar (25)
            ,@stAfterSts nvarchar(50)
            ,@stContId nvarchar(50)
            ,@stItem nvarchar(50)
            ,@stLot nvarchar(25)
            ,@stReferenceType nvarchar(50)
            ,@stCompany nvarchar(25)
            ,@stUserDef3 nvarchar(50)

        set @stTransType = '50';
        set @stReferenceType = N'Смена статуса';

        set @stBeforeSts = (
            select li.INVENTORY_STS
            from LOCATION_INVENTORY as li, ##tblEXPR as temp
            where li.INTERNAL_LOCATION_INV = temp.id
        );
        set @stWhs = (
            select WAREHOUSE
            from LOCATION_INVENTORY as li
                 join KC_LPN kc on li.logistics_unit = kc.lpn
        );
        set @stAfterSts = (
            select inventory_sts_after
            from LOCATION_INVENTORY AS li, ##tblEXPR AS temp
            where li.INTERNAL_LOCATION_INV = temp.id
        );
        set @stContId = (
            select li.LOGISTICS_UNIT
            from location_inventory as li, ##tblEXPR as temp
            where li.INTERNAL_LOCATION_INV = temp.id
        );
        set @stItem = (
            select li.item
            from LOCATION_INVENTORY as li, ##tblEXPR as temp
            where li.LOGISTICS_UNIT = temp.LPN
        );
        set @stLot = (
            select LOT
            from LOCATION_INVENTORY as li, ##tblEXPR as temp
            where li.logistics_unit = temp.LPN
        );
        set @stCompany = (
            select li.company
            from LOCATION_INVENTORY as li, ##tblEXPR as temp
            where li.logistics_unit = temp.LPN
        );

        --переменная из временной таблицы #2 при смене учетных складов
        set @stUserDef3 = (
            select PLANT_BEFORE
            from KC_LPN AS kc, ##tblEXPR_ERP AS temp_erp
            where kc.LPN = temp_erp.LPN
        );

        --Обновление учетного склада
        update KC_LPN
        set
             PROCESS_STAMP = 'KC_EXP'
            ,ERP_PLANT = (
                select distinct gc.USER4VALUE
                from location_inventory li
                     left join KC_LPN kc on li.logistics_unit=kc.lpn
                     left join KC_MOVE_TYPE kcm on li.INVENTORY_STS=kcm.STS_AFTER
                     inner join GENERIC_CONFIG_DETAIL gc on kcm.STS_AFTER = gc.USER3VALUE
                where gc.RECORD_TYPE = 'KC_EXPIRATION_LIST' and li.USER_DEF1 <= gc.USER2VALUE
            )

        output deleted.LPN, deleted.ERP_PLANT, inserted.ERP_PLANT into ##tblEXPR_ERP
        from KC_LPN kc

        select * from ##tblEXPR --? зачем тут селект ?

        --переменная из временной таблицы #2 при смене учетных складов
        declare @stUserDef4 nvarchar(50)
        set @stUserDef4 = (
            select PLANT_AFTER
            from KC_LPN AS kc, ##tblEXPR_ERP AS temp_erp
            where kc.LPN = temp_erp.LPN
        );

        --Создание записи в transaction_history используя стандартную процедуру SCALE
        EXEC HIST_SaveTransHist
            0.00000, 0.00000, 0.00000, @stAfterSts,
            '0.00000','0.00000', '0.00000','0.00000',@stBeforeSts,'0.00000',@stCompany,@stContId,null,null, null,@stItem,null,@stLot,
            'KC_EXP',null,null,null,null,@stReferenceType,null,null,@stTransType,null,null,@stUserDef3,@stUserDef4,null,null,null,
            null,null,@stWHs,null,null,null,null,null ,null,null,null,'Y', null

    commit transaction t1
end try
begin catch
    if @@error != 0
        rollback transaction t1
end catch
go