IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_PDV_VD_NFCE_PEND_VALID]') 
					AND TYPE IN (N'P', N'PC'))
	BEGIN
		DROP PROCEDURE [DBO].[P_PDV_VD_NFCE_PEND_VALID]
	END
GO
--P_PDV_VD_NFCE_PEND_VALID 1,1 , '2019-03-29', '2019-07-29'
CREATE PROCEDURE P_PDV_VD_NFCE_PEND_VALID(@CD_EMP INT = 1, @CD_FILIAL INT, @DATA_INI DATETIME, @DATA_FIM DATETIME=NULL,  @Top int = 100)
AS

SET NOCOUNT ON; 

IF @DATA_FIM  IS NULL
	SET @DATA_FIM = CONVERT(dATE, GETDATE())

CREATE TABLE #TmpPDV_NFCE(
			 CD_EMP		INT
		   , CD_FILIAL	INT
		   , CD_VD    	INT
		   , CD_NFCE	INT	);

insert into #TmpPDV_NFCE
SELECT 
	   A.CD_EMP
     , A.CD_FILIAL
     , A.CD_VD     
     , L.CD_NFCE
FROM
     PDV_VD(NOLOCK) A
     INNER JOIN PDV_VD_PDV_VD_NFCE(NOLOCK) L ON L.CD_EMP = A.CD_EMP
                                                AND L.CD_FILIAL = A.CD_FILIAL
                                                AND L.CD_VD = A.CD_VD     
WHERE A.CD_EMP = @CD_EMP
      AND A.CD_FILIAL = @CD_FILIAL
      AND A.DT_VD BETWEEN @DATA_INI AND @DATA_FIM
      AND A.ST_VD = 0
      AND A.FLAG_NFCE = 1;

if not exists (SELECT * FROM #TmpPDV_NFCE)
BEGIN
	SELECT 
       null CD_EMP
     , null CD_FILIAL
     , null CGC
     , null CD_NF
     , null CD_CX
     , null DT_VD
     , null CHV_NFCE
     , null AUT_NFCE
     , null STS_PEND
     , null CD_VD_GESTAO
     , null XML_DANFE
     , null DS_MOTIVO
     , null FLAG_CONSOLIDADO
     , null DT_CAD
     , null SEL
     , null NR_ECF
     , null VLR_VD
     , null CD_NFCE
     , null CD_NFCE
     , null CD_NF_SERIE

	RETURN;
END

DELETE FROM N
FROM 
	#TmpPDV_NFCE n 
	  LEFT JOIN PDV_VD_NFCE(NOLOCK) C ON 
			N.CD_NFCE = C.CD_NFCE
		AND C.STS_VALID IN (0,2)
		AND C.CD_EMP = @CD_EMP
		AND C.CD_FILIAL = @CD_FILIAL
		AND C.DT_VD BETWEEN @DATA_INI AND @DATA_FIM
WHERE
	C.CD_NFCE IS NULL

if not exists (SELECT * FROM #TmpPDV_NFCE)
BEGIN
	SELECT 
       null CD_EMP
     , null CD_FILIAL
     , null CGC
     , null CD_NF
     , null CD_CX
     , null DT_VD
     , null CHV_NFCE
     , null AUT_NFCE
     , null STS_PEND
     , null CD_VD_GESTAO
     , null XML_DANFE
     , null DS_MOTIVO
     , null FLAG_CONSOLIDADO
     , null DT_CAD
     , null SEL
     , null NR_ECF
     , null VLR_VD
     , null CD_NFCE
     , null CD_NFCE
     , null CD_NF_SERIE

	RETURN;
END
 
SELECT TOP (@Top)
       A.CD_EMP
     , A.CD_FILIAL
     , F.CGC
     , A.NR_ECF AS CD_NF
     , A.CD_CX
     , A.DT_VD
     , C.CH_NFE AS CHV_NFCE
     , C.NR_PROT AS AUT_NFCE
     , 0 AS STS_PEND
     , A.CD_VD AS CD_VD_GESTAO
     , CASE
           WHEN TRY_CAST(AR.XML_DANFE AS XML) IS NULL THEN AR.XML_DANFE
           ELSE TRY_CAST(AR.XML_DANFE AS XML)
       END AS XML_DANFE
     , C.DS_MOTIVO
     , 1 AS FLAG_CONSOLIDADO
     , A.DT_CAD
     , 1 AS SEL
     , A.NR_ECF
     , A.VLR_VD
     , C.CD_NFCE
     , C.CD_NFCE
     , P.SERIE AS CD_NF_SERIE
FROM
     PDV_VD(NOLOCK) A
     INNER JOIN #TmpPDV_NFCE (NOLOCK) L ON L.CD_EMP = A.CD_EMP
                                                AND L.CD_FILIAL = A.CD_FILIAL
                                                AND L.CD_VD = A.CD_VD
     INNER JOIN PDV_VD_NFCE(NOLOCK) C ON C.CD_NFCE = L.CD_NFCE
     INNER JOIN PDV_CX(NOLOCK) X ON X.CD_EMP = A.CD_EMP
                                    AND X.CD_FILIAL = A.CD_FILIAL
                                    AND X.CD_CX = A.CD_CX
     LEFT JOIN PDV_CX_EST_NF_SERIE(NOLOCK) S ON S.CD_EMP = X.CD_EMP
                                                AND S.CD_FILIAL = X.CD_FILIAL
                                                AND S.CD_CTR = X.CD_CTR_SERIE
     LEFT JOIN EST_NF_SERIE(NOLOCK) P ON P.CD_EMP = S.CD_EMP
                                         AND P.CD_NF_SERIE = S.CD_NF_SERIE
     INNER JOIN PRC_FILIAL(NOLOCK) F ON F.CD_EMP = A.CD_EMP
                                        AND F.CD_FILIAL = A.CD_FILIAL
     INNER JOIN PDV_VD_NFCE_ARQ_DANFE(NOLOCK) AR ON AR.CD_NFCE = L.CD_NFCE
WHERE A.CD_EMP = @CD_EMP
      AND A.CD_FILIAL = @CD_FILIAL
      AND A.DT_VD BETWEEN @DATA_INI AND @DATA_FIM
      AND A.ST_VD = 0
      AND A.FLAG_NFCE = 1
ORDER BY
         A.DT_CAD DESC;