SHARE
TWEET

Untitled

a guest Mar 16th, 2017 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Vladimir_optimized
  2. USE [ILS]
  3. GO
  4. /****** Object:  UserDefinedFunction [dbo].[KC_CHECK_APPLY]    Script Date: 3/16/2017 5:34:06 PM ******/
  5. SET ANSI_NULLS ON
  6. GO
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  9.            
  10. -- =============================================
  11. -- Author:  <vbelko>
  12. -- Create date: <01/03/17>
  13. -- Description: <INVENTORY_ADJUSTMENT.Процедура запускаемая при нажатии "ОК" для выполнения корректировки.
  14. -- Валидация LPN на доступное количество и что текущий INTERNAL_LOC_INV до сих пор соответствует переданному списку.
  15. -- EXEC KC_CHECK_APPLY N'S',@list,1
  16. --declare @list dbo.InvMgmtListGo
  17. -- insert into @list (IntLocNum, Location, LPN,
  18.  
  19. -- allocated_qty, on_hand_qty, in_transit_qty
  20. -- ,suspense_qty , inventory_sts , PLANT
  21. -- ) values  ( 965,N'AA-09-34-7',N'AD_13_3_02','7','230','0','0','N','1000'  )
  22.  
  23.  
  24. --select * from dbo.KC_CHECK_APPLY(N'S', @list, 1)
  25. -- =============================================
  26. ALTER function [dbo].[KC_CHECK_APPLY] (
  27.    @TransType nvarchar(25)
  28.   ,@list InvMgmtListGo READONLY
  29.   ,@Num int
  30. )
  31.  
  32. --:return:    
  33. returns @t table (
  34.      row_num int
  35.     ,IntLocNum int
  36.     ,LPN nvarchar(50)
  37.     ,Location nvarchar(25)
  38.     ,error_msg nvarchar(25)
  39. )
  40. AS BEGIN
  41.  
  42.  
  43. -- объявляем таблицу для записи ошибок
  44. declare @error_table table (
  45.      row_num int
  46.     ,error_msg nvarchar(32)
  47. )
  48.  --объявляем таблицу в которую заносятся данные из @list              
  49. declare @__t table (
  50.     row_num int, IntLocNum int, LPN nvarchar(50), Location nvarchar(25)
  51.     ,allocated_qty numeric (19,5), on_hand_qty numeric (19,5), in_transit_qty numeric (19,5)
  52.  ,suspense_qty numeric (19,5) ,inventory_sts nvarchar (25), PLANT nvarchar (25)
  53. );
  54.  
  55.  
  56. --values for :return:
  57. with
  58.      finish_table as (
  59.         select
  60.              ROW_NUMBER () over (order by IntLocNum) row_num   
  61.             ,IntLocNum, Location, LPN
  62.             ,allocated_qty, on_hand_qty, in_transit_qty
  63.             ,suspense_qty, inventory_sts, PLANT
  64.         from @list
  65.     )
  66. insert into @__t (
  67.     row_num, IntLocNum, LPN, [Location], allocated_qty, on_hand_qty, in_transit_qty, suspense_qty, inventory_sts, PLANT
  68. )
  69. select
  70.     row_num, IntLocNum, LPN, [Location], allocated_qty, on_hand_qty, in_transit_qty, suspense_qty, inventory_sts, PLANT
  71. from finish_table
  72.  
  73.  
  74. --create error message table
  75. declare
  76.      @max int = (select max(row_num) from @__t)
  77.     ,@i int = 1
  78.     ,@msg nvarchar(25);
  79. declare @sbor table (
  80.      row_num int
  81.     ,lpn nvarchar(64)
  82.     ,allocated_qty NUMERIC(19, 5)
  83.     ,on_hand_qty NUMERIC(19, 5)
  84.     ,in_transit_qty NUMERIC(19, 5)
  85.     ,location_class nvarchar(32)
  86.     ,active nchar(1)
  87.     ,condition nvarchar(32)
  88.     ,location_sts nvarchar(32)
  89. );
  90. with
  91.     sbor as (
  92.         select
  93.              l.row_num
  94.             ,li.logistics_unin lpn
  95.             ,li.allocated_qty
  96.             ,li.on_hand_qty
  97.             ,li.in_transit_qty
  98.             ,loc.location_class
  99.             ,loc.active
  100.             ,wi.condition
  101.             ,loc.location_sts
  102.         from @__t l
  103.              join LOCATION_INVENTORY li on l.IntLocNum = li.INTERNAL_LOCATION_INV
  104.                 and li.on_hand_qty = l.on_hand_qty
  105.                 and li.allocated_qty = l.allocated_qty
  106.                 and li.in_transit_qty = l.in_transit_qty
  107.                 and li.SUSPENSE_QTY = l.suspense_qty
  108.                 and li.LOCATION = l.Location
  109.                 and li.LOGISTICS_UNIT = l.LPN
  110.             join KC_LPN kc on li.LOGISTICS_UNIT = kc.LPN
  111.                 and kc.ERP_PLANT = l.PLANT
  112.             join [location] loc on loc.LOCATION = li.LOCATION
  113.             left join work_instruction wi on li.logistics_unit = wi.logistics_unit
  114.  
  115.     )
  116.     insert into @sbor (
  117.         row_num
  118.         ,lpn
  119.         ,allocated_qty
  120.         ,on_hand_qty
  121.         ,in_transit_qty
  122.         ,location_class
  123.         ,active
  124.         ,condition
  125.         ,location_sts
  126.     )
  127.     select
  128.         row_num
  129.         ,lpn
  130.         ,allocated_qty
  131.         ,on_hand_qty
  132.         ,in_transit_qty
  133.         ,location_class
  134.         ,active
  135.         ,condition
  136.         ,location_sts
  137.     from sbor
  138.  
  139.  
  140. --заполнение таблицы с ошибками @error_table
  141. WHILE @i !> @max BEGIN
  142.     IF EXISTS ( select lpn from @sbor where l.row_num = @i ) begin
  143.             if @TransType in (N'S', N'W')
  144.             and exists ( select top 1 1 from @sbor where l.row_num = @i ) begin
  145.                     set @msg = null
  146.                     insert into @error_table(row_num, error_msg) values (@i, @msg)
  147.                     set @i += 1
  148.             end
  149.         end
  150.        
  151.     ELSE IF @TransType = N'S' or @TransType = 'W' and exists (
  152.             select top 1 1 from @sbor
  153.             where
  154.                 row_num = @i
  155.                 and allocated_qty > 0
  156.                 and LOCATION_CLASS='Inventory'
  157.                 and ACTIVE = 'Y'
  158.                 and LOCATION_STS != 'Frozen'
  159.                 and [LOCATION] =  l.Location
  160.     )
  161.     begin
  162.         set @msg = N'ERR01'
  163.         insert into @error_table(row_num, error_msg) values (@i, @msg)
  164.         set @i += 1        
  165.     end
  166.  
  167.     ELSE IF @TransType = N'S' or @TransType = 'W' and exists (
  168.         select top 1 1 from @sbor
  169.         where
  170.             l.row_num = @i
  171.             and li.SUSPENSE_QTY > 0
  172.             and loc.LOCATION_CLASS ='Inventory'
  173.             and loc.ACTIVE = 'Y'
  174.             and loc.LOCATION_STS != 'Frozen'
  175.             and li.LOCATION =  l.Location  
  176.     )
  177.     begin
  178.         set @msg = 'ERR02'
  179.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  180.         set @i += 1            
  181.     end
  182.        
  183.     ELSE IF (@TransType = N'S' or @TransType = 'W') and exists (
  184.         select top 1 1
  185.         from @sbor
  186.         where
  187.             l.row_num = @i
  188.             and li.ON_HAND_QTY = 0
  189.             and loc.LOCATION_CLASS = 'Inventory'
  190.             and loc.ACTIVE= 'Y'
  191.             and loc.LOCATION_STS != 'Frozen'
  192.             and li.LOCATION =  l.Location  
  193.     )
  194.     begin
  195.         set @msg = 'ERR03'
  196.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  197.         set @i += 1
  198.     end
  199.  
  200.     ELSE IF @TransType = N'S' or @TransType = 'W' and exists (
  201.         select top 1 1
  202.         from @sbor
  203.         where l.row_num = @i
  204.             and li.ON_HAND_QTY = 0 and li.ALLOCATED_QTY = 0      
  205.             and li.IN_TRANSIT_QTY = 0 and li.SUSPENSE_QTY = 0
  206.             and loc.LOCATION_CLASS='Inventory' and loc.ACTIVE='Y'
  207.             and loc.LOCATION_STS != 'Frozen' and li.LOCATION =  l.Location
  208.     )
  209.     begin
  210.         set @msg = 'ERR04'
  211.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  212.         set @i += 1        
  213.     end
  214.  
  215.     ELSE IF @TransType = 'T' and exists (
  216.         select top 1 1 from @__t l
  217.             left join location_inventory li on l.IntLocNum = li.internal_location_inv
  218.             join location loc on loc.LOCATION = li.LOCATION
  219.             --left join WORK_INSTRUCTION wi on li.LOGISTICS_UNIT = wi.LOGISTICS_UNIT
  220.  
  221.         where
  222.             l.row_num = @i
  223.             and li.on_hand_qty > 0
  224.             and li.allocated_qty = 0
  225.             and li.in_transit_qty = 0
  226.             and loc.LOCATION_CLASS != 'Receiving Dock'
  227.             and loc.LOCATION_CLASS != 'P&D'
  228.             and loc.ACTIVE = 'Y'
  229.             and loc.LOCATION_STS != 'Frozen'
  230.             and li.LOCATION =  l.Location
  231.             and wi.CONDITION in (N'Open', N'In Process')
  232.             and (wi.LOGISTICS_UNIT is not null
  233.             and wi.LOGISTICS_UNIT = l.LPN)
  234.         )
  235.     begin
  236.         set @msg = null
  237.         insert into @error_table(row_num, error_msg) values (@i, @msg)
  238.         set @i += 1    
  239.     end
  240.  
  241.     ELSE IF @TransType = N'T' and exists (
  242.         select top 1 1 from @sbor
  243.         where l.row_num = @i
  244.             and li.allocated_qty > 0 and loc.LOCATION_CLASS != 'Receiving Dock'
  245.             and loc.LOCATION_CLASS != 'P&D' and loc.ACTIVE = 'Y'
  246.             and loc.LOCATION_STS != 'Frozen' and li.LOCATION =  l.Location
  247.     )
  248.     begin
  249.         set @msg = N'ERR01'
  250.         insert into @error_table(row_num, error_msg) values (@i, @msg)
  251.         set @i += 1
  252.     end
  253.  
  254.     ELSE IF @TransType = 'T' and exists (
  255.         select top 1 1 from @sbor
  256.         where l.row_num = @i
  257.             and li.SUSPENSE_QTY > 0 and loc.LOCATION_CLASS <> 'Receiving Dock'
  258.             and loc.LOCATION_CLASS <> 'P&D' and loc.ACTIVE = 'Y'
  259.             and loc.LOCATION_STS <> 'Frozen' and li.LOCATION =  l.Location
  260.                
  261.     )
  262.     begin
  263.         set @msg = 'ERR02'
  264.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  265.         set @i += 1
  266.     end
  267.  
  268.     ELSE IF @TransType = 'T' and exists (
  269.         select top 1 1 from @sbor
  270.         where l.row_num = @i
  271.             and li.ON_HAND_QTY = 0 and loc.LOCATION_CLASS <> 'Receiving Dock'
  272.             and loc.LOCATION_CLASS <> 'P&D' and loc.ACTIVE = 'Y'
  273.             and loc.LOCATION_STS <> 'Frozen' and li.LOCATION =  l.Location 
  274.     )
  275.     begin
  276.         set @msg = 'ERR03'
  277.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  278.         set @i += 1
  279.     end
  280.  
  281.     ELSE IF @TransType = 'T' and exists (
  282.         select top 1 1 from @sbor
  283.         where l.row_num = @i
  284.             and li.ON_HAND_QTY = 0  and li.ALLOCATED_QTY = 0      
  285.             and li.IN_TRANSIT_QTY = 0 and li.SUSPENSE_QTY = 0  
  286.             and loc.LOCATION_CLASS != 'Receiving Dock'
  287.             and loc.LOCATION_CLASS != 'P&D' and loc.ACTIVE = 'Y'
  288.             and loc.LOCATION_STS != 'Frozen' and li.LOCATION =  l.Location 
  289.     )
  290.     begin
  291.         set @msg = 'ERR04'
  292.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  293.         set @i += 1
  294.     end
  295.  
  296.     ELSE IF @TransType = N'Q' and exists (
  297.         select top 1 1 from @sbor
  298.         where l.row_num = @i
  299.             and li.on_hand_qty > 0 and li.allocated_qty = 0
  300.             and li.in_transit_qty = 0 and loc.LOCATION_CLASS = 'Inventory'         
  301.     )
  302.     begin
  303.         set @msg = null
  304.         insert into @error_table(row_num, error_msg) values (@i, @msg)
  305.         set @i += 1
  306.     end
  307.  
  308.     ELSE IF @TransType = N'Q' and exists (
  309.         select top 1 1 from @sbor
  310.         where l.row_num = @i and li.allocated_qty > 0 and loc.LOCATION_CLASS = 'Inventory'
  311.     )
  312.     begin
  313.         set @msg = N'ERR01'
  314.         insert into @error_table(row_num, error_msg) values (@i, @msg)
  315.         set @i += 1
  316.     end
  317.  
  318.     ELSE IF @TransType = 'Q' and exists (
  319.         select top 1 1 from @sbor
  320.         where l.row_num = @i
  321.             and li.SUSPENSE_QTY > 0 and li.LOCATION =  l.Location
  322.             and loc.LOCATION_CLASS = 'Inventory' and loc.LOCATION = l.Location 
  323.     )
  324.     begin
  325.         set @msg = 'ERR02'
  326.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  327.         set @i += 1
  328.     end
  329.  
  330.     ELSE IF @TransType = 'Q' and exists (
  331.         select top 1 1 from @sbor
  332.         where l.row_num = @i and li.ON_HAND_QTY = 0 and loc.LOCATION_CLASS = 'Inventory' and loc.LOCATION = l.Location 
  333.     )
  334.     begin
  335.         set @msg = 'ERR03'
  336.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  337.         set @i += 1
  338.     end
  339.  
  340.     ELSE IF @TransType = 'Q' and exists (
  341.         select top 1 1 from @sbor
  342.         where l.row_num = @i
  343.             and li.ON_HAND_QTY = 0 and li.ALLOCATED_QTY = 0      
  344.             and li.IN_TRANSIT_QTY = 0 and li.SUSPENSE_QTY = 0      
  345.     )
  346.     begin
  347.         set @msg = 'ERR04'
  348.         insert into @error_table(row_num, error_msg)  values (@i, @msg)
  349.         set @i += 1
  350.     end
  351.  
  352.  
  353.     ELSE set @msg = null
  354.         insert into @t ( row_num, IntLocNum, LPN, Location, error_msg )
  355.     select t.row_num, t.IntLocNum, t.LPN, t.Location, e.error_msg
  356.     from @__t t
  357.          join (select row_num, error_msg from @error_table)e on e.row_num = t.row_num
  358.  
  359.  
  360. END --WHILE
  361.  
  362. else
  363. set @msg = 'ERR06'
  364. insert into @t (
  365.      row_num
  366.     ,IntLocNum
  367.     ,LPN
  368.     ,Location
  369.     ,error_msg
  370. )
  371.  
  372. select t.row_num, t.IntLocNum, t.LPN, t.Location, e.error_msg
  373. from @__t t join (select row_num, error_msg from @error_table)e on e.row_num = t.row_num
  374.  
  375.  
  376.    
  377.  
  378. return
  379. end
RAW Paste Data
Top