IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_PAINEL_NFCE_PDV_VENDAS]'))
DROP VIEW [dbo].[V_PAINEL_NFCE_PDV_VENDAS]
GO

/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 250411
'Job.................:  
'Nome................: [V_PAINEL_NFCE_PDV_VENDAS]
'Objetivo............: Usada pelo painel nfce para listar notas.
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 11/04/2025
'Criado por..........: Marcos Barros
'Alterado por........: Marcos Barros
'=======================================================================================
*/

CREATE   VIEW [dbo].[V_PAINEL_NFCE_PDV_VENDAS] AS
SELECT
   T.TP_EMIS,
   COALESCE(C.COD_STAT, 0) AS STATUS_XML,
   A.CD_EMP,
   A.CD_FILIAL,
   A.CD_VD,
   A.DT_VD,
   A.CD_NF,
   A.ST_VD,
   A.CD_VD AS CD_VD_GESTAO,
   A.NR_ECF,
   A.HR_VD,
   A.CD_CX,
   A.VLR_VD,
   A.VLR_DINH,
   A.VLR_CARTAO,
   A.VLR_CHQS,
   A.VLR_DESC,
   A.VLR_LIQ_VD,
   DATEPART(hour, A.HR_VD) AS HORALIMITE,
   A.DT_CAD,
   R.UF,
   C.NR_PROT AS AUT_NFCE,
   COALESCE(C.STS_VALID, 0) AS STS_VALID,
   C.NR_PROT,
   C.DS_MOTIVO,
   C.CD_NFCE,
   C.CH_NFE AS CHV_NFCE,
   COALESCE(C.QTD_ENVIO, 0) AS QTD_ENVIO,
   C.DT_ENVIO,
   COALESCE(C.COD_STAT, 0) AS COD_STAT,
   P.SERIE AS SERIE,
   D.XML_DANFE,
   0 AS STS_PEND,
   '' AS url_danfe_nfce,
   REPLACE(REPLACE(REPLACE(F.CGC, '.', ''),'/', ''), '-', '') AS CGC,
   T.NR_PROT AS NR_PROT_CANCEL,
   C.CH_NFE AS CHAVE_NFCE,
   T.X_MOTIVO,
   S.CD_NF_SERIE,
   CASE
       WHEN (A.ST_VD = 1) AND (T.TP_EMIS = 1) AND (C.COD_STAT <> 100)	AND (LEN(C.NR_PROT) < 4) AND (C.QTD_ENVIO <= 5) AND (C.COD_STAT <> 102)		THEN 'PENDENTE_INUTILIZACAO'
       WHEN (A.ST_VD = 1) AND (T.TP_EMIS = 1) AND (C.COD_STAT <> 100)	AND ((LEN(C.NR_PROT) > 4 OR TRY_CAST(T.NR_PROT AS BIGINT) > 4 )) AND (C.COD_STAT IN (102, 563))		THEN 'INUTILIZADO'
       WHEN (LEN(C.NR_PROT) > 4) AND (A.ST_VD = 1) AND (T.TP_EMIS = 0)	AND (C.COD_STAT = 135)														THEN 'CANCELADO'
       WHEN (A.ST_VD = 1) AND (C.COD_STAT = 100) AND (C.QTD_ENVIO <= 5) AND (A.DT_VD >= DATEADD(MINUTE, -30, GETDATE())) AND (C.COD_STAT <> 135)	THEN 'PENDENTE_CANCELAMENTO'
       WHEN (A.ST_VD = 1) AND (C.COD_STAT = 100) AND (C.QTD_ENVIO > 5)	AND (C.COD_STAT <> 135)														THEN 'PENDENTE_CANCELAMENTO_BLOQUEADO'
       WHEN (A.ST_VD = 0) AND (T.TP_EMIS = 1) AND (C.COD_STAT <> 100)	AND (LEN(C.NR_PROT) < 4) AND (C.QTD_ENVIO > 5) AND (C.COD_STAT <> 102)		THEN 'PENDENTE_INUTILIZACAO_BLOQUEADO'
       WHEN (A.ST_VD = 1) AND (C.COD_STAT = 100) AND (C.QTD_ENVIO <= 5) AND (A.DT_VD < DATEADD(MINUTE, -30, GETDATE())) AND (C.COD_STAT <> 135)		THEN 'EXPIRADO_CANCELAMENTO'
       WHEN (LEN(C.NR_PROT) < 4) AND (C.COD_STAT = 0) AND (C.QTD_ENVIO > 5)																			THEN 'CONTINGENCIA_BLOQUEADA'
       WHEN (A.ST_VD = 0) AND (L.CD_EMP IS NULL)																									THEN 'CUPONS_SEM_VINCULO'
       WHEN (LEN(C.NR_PROT) < 4) AND (C.COD_STAT = 0) AND (ST_VD <> 1) 		                                                                        THEN 'CONTINGENCIA'
       WHEN (LEN(C.NR_PROT) < 4) AND (C.COD_STAT > 0)																								THEN 'ERRO'
       WHEN (LEN(C.NR_PROT) > 4) AND (A.ST_VD = 0)																									THEN 'VALIDADO'
       ELSE 'DESCONHECIDO'
   END AS TIPO_NF
FROM PDV_VD  A
INNER JOIN PRC_FILIAL						F  ON F.CD_FILIAL	= A.CD_FILIAL		AND F.STS_FILIAL	= 0
INNER JOIN GLB_CID							R  ON R.CD_CID		= F.CD_CID
LEFT  JOIN PDV_VD_PDV_VD_NFCE				L  ON L.CD_EMP		= A.CD_EMP			AND L.CD_FILIAL		= A.CD_FILIAL	AND L.CD_VD		= A.CD_VD
LEFT  JOIN PDV_CX							X  ON X.CD_CX		= A.CD_CX			AND X.CD_EMP		= A.CD_EMP		AND X.CD_FILIAL = A.CD_FILIAL
LEFT  JOIN PDV_VD_NFCE						C  ON C.CD_NFCE		= L.CD_NFCE			AND C.CD_FILIAL		= L.CD_FILIAL
LEFT  JOIN PDV_CX_EST_NF_SERIE				S  ON S.CD_CTR		= X.CD_CTR_SERIE	AND S.CD_EMP		= X.CD_EMP		AND S.CD_FILIAL = X.CD_FILIAL
LEFT  JOIN EST_NF_SERIE						P  ON P.CD_EMP		= S.CD_EMP			AND P.CD_NF_SERIE	= S.CD_NF_SERIE
LEFT  JOIN PDV_VD_NFCE_CANCEL				T  ON T.CD_NFCE		= L.CD_NFCE
LEFT  JOIN PDV_VD_NFCE_ARQ_DANFE			D  ON D.CD_NFCE		= L.CD_NFCE

WHERE 
	A.CD_EMP = 1 
	AND A.FLAG_NFCE = 1


GO
