Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement