daily pastebin goal
42%
SHARE
TWEET

Untitled

a guest Nov 22nd, 2017 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create or replace PROCEDURE "SP_INFORME_FOLIO_COD_VISACION" (
  2. IN_CD IN NUMBER,
  3. IN_LOCAL IN NUMBER,
  4. IN_INICIO IN  VARCHAR2,
  5. IN_FIN IN   VARCHAR2,
  6. cv_1 OUT SYS_REFCURSOR)
  7. AS
  8. V_INICIO1 VARCHAR2(30);
  9. V_FIN1 VARCHAR2(30);
  10. V_INICIO2 TIMESTAMP;
  11. V_FIN2 TIMESTAMP;
  12. V_LOCAL NUMBER(5);
  13. BEGIN
  14.  IF IN_LOCAL=0 THEN
  15.   V_LOCAL := null;
  16.  ELSE
  17.   V_LOCAL:=IN_LOCAL;
  18.  END IF;
  19.  
  20.  IF IN_INICIO IS NOT NULL THEN
  21.   V_INICIO2 := TO_TIMESTAMP(CONCAT(IN_INICIO, ' 00:00:00'), 'dd/MM/yy
  22.   HH24:MI:SS');
  23.  END IF;
  24.  
  25.  IF IN_FIN IS NOT NULL THEN
  26.   V_FIN2 := TO_TIMESTAMP(CONCAT(IN_FIN, ' 23:59:59'), 'dd/MM/yy HH24:M
  27.   I:SS');
  28.  END IF;
  29.  
  30. OPEN cv_1 FOR
  31. SELECT TB_LOG_EJECUCION.FECHORA_FLAG02 AS fecha,
  32. TB_HEAD_PPL.GLS_STORE
  33. || '-'
  34. || TB_STORE_INFO.STORE_NAME               AS CD,
  35. TB_HEAD_PPL.FOLIO_PPL_SII                 AS guia,
  36. TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION AS cod_visacion
  37. FROM TB_HEAD_PPL
  38. INNER JOIN TB_LOG_EJECUCION ON TB_HEAD_PPL.ID_HEAD_PPL =  
  39. TB_LOG_EJECUCION.ID_HEAD_PPL AND TB_HEAD_PPL.ID_CD_NBR  =
  40. TB_LOG_EJECUCION.ID_CD_NBR
  41. LEFT JOIN TB_FOLIO_PPL_COD_VISACION ON TB_HEAD_PPL.FOLIO_PPL_SII =
  42. TB_FOLIO_PPL_COD_VISACION.FOLIO_SII
  43. INNER JOIN TB_STORE_INFO ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBR
  44. LEFT JOIN TB_DETAIL_PPL D ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPL
  45. LEFT JOIN tb_item i ON D.GLS_ITEM_NBR = i.ITEM_NBR
  46. where TB_LOG_EJECUCION.FECHORA_FLAG02 BETWEEN NVL(V_INICIO2,
  47. TB_LOG_EJECUCION.FECHORA_FLAG02) AND NVL(V_FIN2,
  48. TB_LOG_EJECUCION.FECHORA_FLAG02)
  49. AND TB_HEAD_PPL.ID_CD_NBR = IN_CD AND TB_HEAD_PPL.GLS_STORE = NVL(V_LOCAL,
  50. TB_HEAD_PPL.GLS_STORE)
  51. AND i.ITEM_DEPT_NBR = 83 and D.GLS_PRODUCTION_LOT_NUMBER is not null
  52. GROUP BY TB_LOG_EJECUCION.FECHORA_FLAG02,
  53. TB_HEAD_PPL.GLS_STORE
  54. || '-'
  55. ||  TB_STORE_INFO.STORE_NAME,
  56. TB_HEAD_PPL.FOLIO_PPL_SII,
  57. TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION
  58. ORDER BY fecha;
  59. END;
  60.    
  61. if object_id('"SP_INFORME_FOLIO_COD_VISACION"', 'P') is not null
  62.   drop procedure "SP_INFORME_FOLIO_COD_VISACION";
  63. go
  64.  
  65. create PROCEDURE "SP_INFORME_FOLIO_COD_VISACION" (
  66. @IN_CD FLOAT,
  67. @IN_LOCAL FLOAT,
  68. @IN_INICIO  VARCHAR(4000),
  69. @IN_FIN   VARCHAR(4000)
  70. )
  71. AS
  72. BEGIN
  73. DECLARE @V_INICIO1 VARCHAR(30);
  74. DECLARE @V_FIN1 VARCHAR(30);
  75. DECLARE @V_INICIO2 DATETIME2(6);
  76. DECLARE @V_FIN2 DATETIME2(6);
  77. DECLARE @V_LOCAL INT;
  78.  
  79. SET NOCOUNT ON;
  80.  IF @IN_LOCAL=0 BEGIN
  81.   SET @V_LOCAL = null;
  82.  END
  83.  ELSE BEGIN
  84.   SET @V_LOCAL=@IN_LOCAL;
  85.  END
  86.  
  87.  IF @IN_INICIO IS NOT NULL BEGIN
  88.   SET @V_INICIO2 = CONVERT(VARCHAR(10), @IN_INICIO, 104) + ' ' + CONVERT(VARCHAR(8), @IN_INICIO, 108);
  89.  END
  90.  
  91.  IF @IN_FIN IS NOT NULL BEGIN
  92.   SET @V_FIN2 = CONVERT(VARCHAR(10), @IN_FIN, 104) + ' ' + CONVERT(VARCHAR(8), @IN_FIN, 108);
  93.  END
  94.  
  95. SELECT TB_LOG_EJECUCION.FECHORA_FLAG02 AS fecha,
  96. ISNULL(TB_HEAD_PPL.GLS_STORE, '')+ '-'+ ISNULL(TB_STORE_INFO.STORE_NAME, '') AS CD,
  97. TB_HEAD_PPL.FOLIO_PPL_SII   AS guia,
  98. TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION AS cod_visacion
  99. FROM TB_HEAD_PPL
  100. INNER JOIN TB_LOG_EJECUCION
  101.     ON TB_HEAD_PPL.ID_HEAD_PPL =  TB_LOG_EJECUCION.ID_HEAD_PPL AND TB_HEAD_PPL.ID_CD_NBR  = TB_LOG_EJECUCION.ID_CD_NBR
  102. LEFT JOIN TB_FOLIO_PPL_COD_VISACION
  103.     ON TB_HEAD_PPL.FOLIO_PPL_SII = TB_FOLIO_PPL_COD_VISACION.FOLIO_SII
  104. INNER JOIN TB_STORE_INFO
  105.     ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBR
  106. LEFT JOIN TB_DETAIL_PPL D
  107.     ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPL
  108. LEFT JOIN tb_item i
  109.     ON D.GLS_ITEM_NBR = i.ITEM_NBR
  110. where TB_LOG_EJECUCION.FECHORA_FLAG02 BETWEEN ISNULL(@V_INICIO2, TB_LOG_EJECUCION.FECHORA_FLAG02) AND ISNULL(@V_FIN2, TB_LOG_EJECUCION.FECHORA_FLAG02)
  111.     AND TB_HEAD_PPL.ID_CD_NBR = @IN_CD AND TB_HEAD_PPL.GLS_STORE = ISNULL(@V_LOCAL, TB_HEAD_PPL.GLS_STORE)
  112. AND i.ITEM_DEPT_NBR = 83 and D.GLS_PRODUCTION_LOT_NUMBER is not null
  113. GROUP BY TB_LOG_EJECUCION.FECHORA_FLAG02, ISNULL(TB_HEAD_PPL.GLS_STORE, '')+ '-'+  ISNULL(TB_STORE_INFO.STORE_NAME, ''),
  114. TB_HEAD_PPL.FOLIO_PPL_SII, TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION
  115. ORDER BY fecha;
  116. END;
RAW Paste Data
Top