SHARE
TWEET

Untitled

a guest Mar 16th, 2017 61 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.  
  80. with
  81.     sbor as (
  82.         select
  83.              l.row_num
  84.             ,li.LOGISTICS_UNIT lpn
  85.             ,li.allocated_qty
  86.             ,li.on_hand_qty
  87.             ,li.in_transit_qty
  88.             ,loc.location_class
  89.             ,loc.active
  90.             ,wi.condition
  91.             ,wi.LOGISTICS_UNIT wi_lpn
  92.             ,loc.location_sts
  93.             ,li.SUSPENSE_QTY
  94.         from @__t l
  95.              join LOCATION_INVENTORY li on l.IntLocNum = li.INTERNAL_LOCATION_INV
  96.                 and li.on_hand_qty = l.on_hand_qty
  97.                 and li.allocated_qty = l.allocated_qty
  98.                 and li.in_transit_qty = l.in_transit_qty
  99.                 and li.SUSPENSE_QTY = l.suspense_qty
  100.                 and li.LOCATION = l.Location
  101.                 and li.LOGISTICS_UNIT = l.LPN
  102.             join KC_LPN kc on li.LOGISTICS_UNIT = kc.LPN
  103.                 and kc.ERP_PLANT = l.PLANT
  104.             join [location] loc on loc.LOCATION = li.LOCATION
  105.             left join work_instruction wi on li.logistics_unit = wi.logistics_unit
  106.  
  107.     ) select * into ##sbor from sbor
  108.  
  109.  
  110. --заполнение таблицы с ошибками @error_table
  111. WHILE @i !> @max
  112. BEGIN
  113.     IF EXISTS ( select lpn from ##sbor where row_num = @i )
  114.     begin
  115.         --1
  116.         if @TransType in (N'S', N'W')
  117.             and exists ( select top 1 1 from ##sbor where row_num = @i )
  118.         begin
  119.             set @msg = null
  120.             insert into @error_table(row_num, error_msg) values (@i, @msg)
  121.             set @i += 1
  122.         end
  123.         --2
  124.         else if @TransType = N'S' or @TransType = 'W' and exists (
  125.                 select top 1 1 from ##sbor
  126.                 where row_num = @i  and allocated_qty > 0
  127.                     and LOCATION_CLASS='Inventory' and ACTIVE = 'Y'
  128.                     and LOCATION_STS != 'Frozen'
  129.         )
  130.         begin
  131.             set @msg = N'ERR01'
  132.             insert @error_table values (@i, @msg)
  133.             set @i += 1        
  134.         end
  135.         --3
  136.         else if @TransType = N'S' or @TransType = 'W' and exists (
  137.             select top 1 1 from ##sbor
  138.             where row_num = @i and SUSPENSE_QTY > 0
  139.                 and LOCATION_CLASS ='Inventory'
  140.                 and ACTIVE = 'Y' and LOCATION_STS != 'Frozen'
  141.         )
  142.         begin
  143.             set @msg = 'ERR02'
  144.             insert into @error_table(row_num, error_msg)  values (@i, @msg)
  145.             set @i += 1            
  146.         end
  147.         --4
  148.         else if (@TransType = N'S' or @TransType = 'W') and exists (
  149.             select top 1 1
  150.             from ##sbor
  151.             where row_num = @i and ON_HAND_QTY = 0
  152.                 and LOCATION_CLASS = 'Inventory' and ACTIVE= 'Y'
  153.                 and LOCATION_STS != 'Frozen'
  154.         )
  155.         begin
  156.             set @msg = 'ERR03'
  157.             insert into @error_table(row_num, error_msg)  values (@i, @msg)
  158.             set @i += 1
  159.         end
  160.         --4
  161.         else if @TransType = N'S' or @TransType = 'W' and exists (
  162.             select top 1 1
  163.             from ##sbor
  164.             where row_num = @i and ON_HAND_QTY = 0 and ALLOCATED_QTY = 0      
  165.                 and IN_TRANSIT_QTY = 0 and SUSPENSE_QTY = 0
  166.                 and LOCATION_CLASS = 'Inventory' and ACTIVE = 'Y'
  167.                 and LOCATION_STS != 'Frozen'
  168.         )
  169.         begin
  170.             set @msg = 'ERR04'
  171.             insert @error_table values (@i, @msg)
  172.             set @i += 1        
  173.         end
  174.         --5
  175.         else if @TransType = 'T' and exists (
  176.             select top 1 1 from ##sbor
  177.             where row_num = @i and on_hand_qty > 0
  178.                 and allocated_qty = 0 and in_transit_qty = 0
  179.                 and LOCATION_CLASS != 'Receiving Dock' and LOCATION_CLASS != 'P&D'
  180.                 and ACTIVE = 'Y' and LOCATION_STS != 'Frozen'
  181.                 and CONDITION in (N'Open', N'In Process')
  182.                 and (wi_lpn is not null and wi_lpn = LPN)
  183.             )
  184.         begin
  185.             set @msg = null
  186.             insert into @error_table(row_num, error_msg) values (@i, @msg)
  187.             set @i += 1    
  188.         end
  189.         --6
  190.         else if @TransType = N'T' and exists (
  191.             select top 1 1 from ##sbor
  192.             where row_num = @i
  193.                 and allocated_qty > 0 and LOCATION_CLASS != 'Receiving Dock'
  194.                 and LOCATION_CLASS != 'P&D' and ACTIVE = 'Y'
  195.                 and LOCATION_STS != 'Frozen'
  196.         )
  197.         begin
  198.             set @msg = N'ERR01'
  199.             insert @error_table values (@i, @msg)
  200.             set @i += 1
  201.         end
  202.         --7
  203.         else if @TransType = 'T' and exists (
  204.             select top 1 1 from ##sbor
  205.             where row_num = @i
  206.                 and SUSPENSE_QTY > 0 and LOCATION_CLASS <> 'Receiving Dock'
  207.                 and LOCATION_CLASS != 'P&D' and ACTIVE = 'Y'
  208.                 and LOCATION_STS != 'Frozen'               
  209.         )
  210.         begin
  211.             set @msg = 'ERR02'
  212.             insert @error_table values (@i, @msg)
  213.             set @i += 1
  214.         end
  215.         --8
  216.         else if @TransType = 'T' and exists (
  217.             select top 1 1 from ##sbor
  218.             where row_num = @i
  219.                 and ON_HAND_QTY = 0 and LOCATION_CLASS != 'Receiving Dock'
  220.                 and LOCATION_CLASS != 'P&D' and ACTIVE = 'Y'
  221.                 and LOCATION_STS != 'Frozen'
  222.         )
  223.         begin
  224.             set @msg = 'ERR03'
  225.             insert @error_table values (@i, @msg)
  226.             set @i += 1
  227.         end
  228.         --9
  229.         else if @TransType = 'T' and exists (
  230.             select top 1 1 from ##sbor
  231.             where row_num = @i and ON_HAND_QTY = 0  and ALLOCATED_QTY = 0      
  232.                 and IN_TRANSIT_QTY = 0 and SUSPENSE_QTY = 0
  233.                 and LOCATION_CLASS != 'Receiving Dock'
  234.                 and LOCATION_CLASS != 'P&D' and ACTIVE = 'Y' and LOCATION_STS != 'Frozen'
  235.         )
  236.         begin
  237.             set @msg = 'ERR04'
  238.             insert @error_table values (@i, @msg)
  239.             set @i += 1
  240.         end
  241.         --10
  242.         else if @TransType = N'Q' and exists (
  243.             select top 1 1 from ##sbor
  244.             where row_num = @i and on_hand_qty > 0 and allocated_qty = 0
  245.                 and in_transit_qty = 0 and LOCATION_CLASS = 'Inventory'        
  246.         )
  247.         begin
  248.             set @msg = null
  249.             insert @error_table values (@i, @msg)
  250.             set @i += 1
  251.         end
  252.         --11
  253.         else if @TransType = N'Q' and exists (
  254.             select top 1 1 from ##sbor
  255.             where row_num = @i and allocated_qty > 0 and LOCATION_CLASS = 'Inventory'
  256.         )
  257.         begin
  258.             set @msg = N'ERR01'
  259.             insert @error_table values (@i, @msg)
  260.             set @i += 1
  261.         end
  262.         --12
  263.         else if @TransType = 'Q' and exists (
  264.             select top 1 1 from ##sbor
  265.             where row_num = @i and SUSPENSE_QTY > 0 and LOCATION_CLASS = 'Inventory'
  266.         )
  267.         begin
  268.             set @msg = 'ERR02'
  269.             insert @error_table values (@i, @msg)
  270.             set @i += 1
  271.         end
  272.         --13
  273.         else if @TransType = 'Q' and exists (
  274.             select top 1 1 from ##sbor
  275.             where row_num = @i and ON_HAND_QTY = 0 and LOCATION_CLASS = 'Inventory'
  276.         )
  277.         begin
  278.             set @msg = 'ERR03'
  279.             insert @error_table values (@i, @msg)
  280.             set @i += 1
  281.         end
  282.         --14
  283.         else if @TransType = 'Q' and exists (
  284.             select top 1 1 from ##sbor
  285.             where row_num = @i
  286.                 and ON_HAND_QTY = 0 and ALLOCATED_QTY = 0      
  287.                 and IN_TRANSIT_QTY = 0 and SUSPENSE_QTY = 0    
  288.         )
  289.         begin
  290.             set @msg = 'ERR04'
  291.             insert @error_table values (@i, @msg)
  292.             set @i += 1
  293.         end
  294.  
  295.  
  296.         else
  297.             set @msg = null;
  298.             insert into @t (
  299.                 row_num, IntLocNum, LPN, Location, error_msg
  300.             )
  301.             select
  302.                 t.row_num, t.IntLocNum, t.LPN, t.Location, e.error_msg
  303.             from @__t t
  304.                  join (
  305.                     select row_num, error_msg from @error_table
  306.                  )e on e.row_num = t.row_num
  307.         end
  308.     ELSE
  309.         set @msg = 'ERR06';
  310.         insert into @t (row_num, IntLocNum, LPN, Location, error_msg)
  311.         select 
  312.             t.row_num, t.IntLocNum, t.LPN, t.Location, e.error_msg
  313.         from @__t t
  314.              join (
  315.                 select row_num, error_msg from @error_table
  316.             )e on e.row_num = t.row_num
  317.  
  318. END --WHILE
  319. RETURN
  320. END
RAW Paste Data
Top