Advertisement
Guest User

Untitled

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