Ledger Nano X - The secure hardware wallet
SHARE
TWEET

Untitled

a guest Apr 9th, 2020 165 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [efs]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[USP_REPORT_RF_RECIPE_COUNT_R34]    Script Date: 09.04.2020 13:16:22 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[USP_REPORT_RF_RECIPE_COUNT_R34](
  9.     @DATE_BEGIN DATE,
  10.     @DATE_END DATE,
  11.     @USE_HIERARCHY BIT
  12. ) AS
  13.  
  14. declare @ALL_LPU bit = IsNull((select top 1 0 from #LPU_PARAM), 1)
  15.  
  16. DECLARE @PARAMS UDT_REPORT_UTIL_PARAM_LIST; INSERT INTO @PARAMS EXEC [USP_REPORT_UTIL_PARAM_LIST]
  17.  
  18. DECLARE @HOME_REGION varchar(8) = (SELECT REGION_CODE FROM OKATO WHERE IS_HOME_REGION = 1)
  19.  
  20. DECLARE @RECIPE_SERIA_NOT_BLANK TABLE
  21. (
  22.     NAME varchar(20)
  23. )
  24.  
  25. IF @HOME_REGION = '34'
  26. BEGIN
  27.     INSERT INTO @RECIPE_SERIA_NOT_BLANK (NAME)
  28.     SELECT DISTINCT
  29.         NAME
  30.     FROM RECIPE_SERIA RS
  31.     WHERE
  32.         --rs.DELETED is NULL AND
  33.         ISNUMERIC(SUBSTRING(RS.NAME, 4, 1)) = 1 AND
  34.         ISNUMERIC(SUBSTRING(RS.NAME, 5, 1)) = 1
  35. END
  36. ELSE
  37. BEGIN
  38.     INSERT INTO @RECIPE_SERIA_NOT_BLANK (NAME)
  39.     SELECT DISTINCT
  40.         NAME
  41.     FROM RECIPE_SERIA RS
  42.     WHERE
  43.         RS.DELETED is NULL AND
  44.         RS.IS_BLANK = 0
  45. END
  46.  
  47. ;with _PARENTS as
  48. (
  49.     select
  50.         c.GUID,
  51.         PARENT_GUID = c.GUID,
  52.         LVL = 1
  53.     from CONTRACTOR (nolock) c
  54.  
  55.     union all
  56.  
  57.     select
  58.         p.GUID,
  59.         c.PARENT_GUID,
  60.         LVL = p.LVL + 1
  61.     from _PARENTS p
  62.     join CONTRACTOR (nolock) c on c.GUID = p.PARENT_GUID
  63.     where
  64.         c.PARENT_GUID is not NULL
  65. ),
  66. _TAB as
  67. (
  68.     select
  69.         p.GUID,
  70.         p.PARENT_GUID,
  71.         p.LVL,
  72.         NEXT_GUID = lead(GUID) over (order by p.GUID, p.LVL)
  73.     from _PARENTS p
  74.     where
  75.         (
  76.             @ALL_LPU = 1
  77.             or
  78.             p.GUID in (select VALUE from #LPU_PARAM)
  79.             or
  80.             p.PARENT_GUID in (select VALUE from #LPU_PARAM)
  81.         )
  82. ),
  83. _CONTRACTORS as
  84. (
  85.     select
  86.         c.GUID,
  87.         c.PARENT_GUID
  88.     from _TAB c
  89.     where
  90.         NEXT_GUID is NULL
  91.         or
  92.         NEXT_GUID <> GUID
  93. ),
  94. _DATA as
  95. (
  96.     select
  97.         cr.RECIPE_SERIA,
  98.         PARENT_MO_GUID = c.PARENT_GUID,
  99.         MO_GUID = case when @USE_HIERARCHY = 0 then cr.LPU_GUID end,
  100.         CNT = count(*)
  101.     from CHACHE_RECIPES (nolock) cr
  102.     join _CONTRACTORS c on c.GUID = cr.LPU_GUID
  103.     where
  104.         cr.DOC_STATUS not in ('SAVE', 'DEL', 'ANNULLED') and
  105.         cast(cr.DATE_ISSUE as date) between @DATE_BEGIN and @DATE_END
  106.     group by
  107.         cr.RECIPE_SERIA,
  108.         c.PARENT_GUID,
  109.         case when @USE_HIERARCHY = 0 then cr.LPU_GUID end
  110. )
  111. select
  112.     PARENT_MO_CODE = pmo.MCOD,
  113.     PARENT_MO_NAME = pmo.NAME,
  114.     MO_CODE = mo.MCOD,
  115.     MO_NAME = mo.FULLNAME,
  116.     CNT_ALL = sum(d.CNT),
  117.     CNT_PC = sum(case when rs.NAME is not NULL then d.CNT else 0 end)
  118. from _DATA d
  119. join CONTRACTOR pmo (nolock) on pmo.GUID = d.PARENT_MO_GUID
  120. left join CONTRACTOR (nolock) mo on mo.GUID = d.MO_GUID
  121. left join @RECIPE_SERIA_NOT_BLANK rs on rs.NAME = d.RECIPE_SERIA
  122. group by
  123.     pmo.MCOD,
  124.     pmo.NAME,
  125.     mo.MCOD,
  126.     mo.FULLNAME
  127. order by
  128.     pmo.NAME,
  129.     mo.FULLNAME
  130.  
  131. SELECT
  132.     [LPU] = [dbo].[FN_REPORT_UTIL_PARAM_DISPLAY]('LPU_PARAM', @PARAMS),
  133.     [DATE] = [dbo].[FN_FORMAT_DATE_RANGE](@DATE_BEGIN,@DATE_END,NULL,0),
  134.     [USE_HIERARCHY] = @USE_HIERARCHY
  135.  
  136. RETURN 0
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top