Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create or replace PROCEDURE "SP_INFORME_FOLIO_COD_VISACION" (
- IN_CD IN NUMBER,
- IN_LOCAL IN NUMBER,
- IN_INICIO IN VARCHAR2,
- IN_FIN IN VARCHAR2,
- cv_1 OUT SYS_REFCURSOR)
- AS
- V_INICIO1 VARCHAR2(30);
- V_FIN1 VARCHAR2(30);
- V_INICIO2 TIMESTAMP;
- V_FIN2 TIMESTAMP;
- V_LOCAL NUMBER(5);
- BEGIN
- IF IN_LOCAL=0 THEN
- V_LOCAL := null;
- ELSE
- V_LOCAL:=IN_LOCAL;
- END IF;
- IF IN_INICIO IS NOT NULL THEN
- V_INICIO2 := TO_TIMESTAMP(CONCAT(IN_INICIO, ' 00:00:00'), 'dd/MM/yy
- HH24:MI:SS');
- END IF;
- IF IN_FIN IS NOT NULL THEN
- V_FIN2 := TO_TIMESTAMP(CONCAT(IN_FIN, ' 23:59:59'), 'dd/MM/yy HH24:M
- I:SS');
- END IF;
- OPEN cv_1 FOR
- SELECT TB_LOG_EJECUCION.FECHORA_FLAG02 AS fecha,
- TB_HEAD_PPL.GLS_STORE
- || '-'
- || TB_STORE_INFO.STORE_NAME AS CD,
- TB_HEAD_PPL.FOLIO_PPL_SII AS guia,
- TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION AS cod_visacion
- FROM TB_HEAD_PPL
- INNER JOIN TB_LOG_EJECUCION 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
- LEFT JOIN TB_FOLIO_PPL_COD_VISACION ON TB_HEAD_PPL.FOLIO_PPL_SII =
- TB_FOLIO_PPL_COD_VISACION.FOLIO_SII
- INNER JOIN TB_STORE_INFO ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBR
- LEFT JOIN TB_DETAIL_PPL D ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPL
- LEFT JOIN tb_item i ON D.GLS_ITEM_NBR = i.ITEM_NBR
- where TB_LOG_EJECUCION.FECHORA_FLAG02 BETWEEN NVL(V_INICIO2,
- TB_LOG_EJECUCION.FECHORA_FLAG02) AND NVL(V_FIN2,
- TB_LOG_EJECUCION.FECHORA_FLAG02)
- AND TB_HEAD_PPL.ID_CD_NBR = IN_CD AND TB_HEAD_PPL.GLS_STORE = NVL(V_LOCAL,
- TB_HEAD_PPL.GLS_STORE)
- AND i.ITEM_DEPT_NBR = 83 and D.GLS_PRODUCTION_LOT_NUMBER is not null
- GROUP BY TB_LOG_EJECUCION.FECHORA_FLAG02,
- TB_HEAD_PPL.GLS_STORE
- || '-'
- || TB_STORE_INFO.STORE_NAME,
- TB_HEAD_PPL.FOLIO_PPL_SII,
- TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION
- ORDER BY fecha;
- END;
- if object_id('"SP_INFORME_FOLIO_COD_VISACION"', 'P') is not null
- drop procedure "SP_INFORME_FOLIO_COD_VISACION";
- go
- create PROCEDURE "SP_INFORME_FOLIO_COD_VISACION" (
- @IN_CD FLOAT,
- @IN_LOCAL FLOAT,
- @IN_INICIO VARCHAR(4000),
- @IN_FIN VARCHAR(4000)
- )
- AS
- BEGIN
- DECLARE @V_INICIO1 VARCHAR(30);
- DECLARE @V_FIN1 VARCHAR(30);
- DECLARE @V_INICIO2 DATETIME2(6);
- DECLARE @V_FIN2 DATETIME2(6);
- DECLARE @V_LOCAL INT;
- SET NOCOUNT ON;
- IF @IN_LOCAL=0 BEGIN
- SET @V_LOCAL = null;
- END
- ELSE BEGIN
- SET @V_LOCAL=@IN_LOCAL;
- END
- IF @IN_INICIO IS NOT NULL BEGIN
- SET @V_INICIO2 = CONVERT(VARCHAR(10), @IN_INICIO, 104) + ' ' + CONVERT(VARCHAR(8), @IN_INICIO, 108);
- END
- IF @IN_FIN IS NOT NULL BEGIN
- SET @V_FIN2 = CONVERT(VARCHAR(10), @IN_FIN, 104) + ' ' + CONVERT(VARCHAR(8), @IN_FIN, 108);
- END
- SELECT TB_LOG_EJECUCION.FECHORA_FLAG02 AS fecha,
- ISNULL(TB_HEAD_PPL.GLS_STORE, '')+ '-'+ ISNULL(TB_STORE_INFO.STORE_NAME, '') AS CD,
- TB_HEAD_PPL.FOLIO_PPL_SII AS guia,
- TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION AS cod_visacion
- FROM TB_HEAD_PPL
- INNER JOIN TB_LOG_EJECUCION
- 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
- LEFT JOIN TB_FOLIO_PPL_COD_VISACION
- ON TB_HEAD_PPL.FOLIO_PPL_SII = TB_FOLIO_PPL_COD_VISACION.FOLIO_SII
- INNER JOIN TB_STORE_INFO
- ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBR
- LEFT JOIN TB_DETAIL_PPL D
- ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPL
- LEFT JOIN tb_item i
- ON D.GLS_ITEM_NBR = i.ITEM_NBR
- where TB_LOG_EJECUCION.FECHORA_FLAG02 BETWEEN ISNULL(@V_INICIO2, TB_LOG_EJECUCION.FECHORA_FLAG02) AND ISNULL(@V_FIN2, TB_LOG_EJECUCION.FECHORA_FLAG02)
- AND TB_HEAD_PPL.ID_CD_NBR = @IN_CD AND TB_HEAD_PPL.GLS_STORE = ISNULL(@V_LOCAL, TB_HEAD_PPL.GLS_STORE)
- AND i.ITEM_DEPT_NBR = 83 and D.GLS_PRODUCTION_LOT_NUMBER is not null
- GROUP BY TB_LOG_EJECUCION.FECHORA_FLAG02, ISNULL(TB_HEAD_PPL.GLS_STORE, '')+ '-'+ ISNULL(TB_STORE_INFO.STORE_NAME, ''),
- TB_HEAD_PPL.FOLIO_PPL_SII, TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION
- ORDER BY fecha;
- END;
Add Comment
Please, Sign In to add comment