/****** Object:  View [dbo].[V_EST_PED_VD]    Script Date: 03/04/2014 13:52:02 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PED_VD]'))
DROP VIEW [dbo].[V_EST_PED_VD]
GO
GO
GO
--SELECT * FROM V_EST_PED_VD WHERE STS_PED = 'PENDENTE'
--Versao 2885
CREATE VIEW [dbo].[V_EST_PED_VD] AS
SELECT     
	EST_PED_VD.CD_EMP, 
	EST_PED_VD.CD_FILIAL, 
	EST_PED_VD.CD_PED, 
	EST_PED_VD.CD_CLI,
	EST_PED_VD.DT_EMI_PED,
    EST_PED_VD.VLR_TOT_PED,
	NM_FANT =  CASE WHEN DEST_ORIG = 0  THEN
						(SELECT     
							RZ_CLI
						FROM      
							RC_CLI
						WHERE      
							RC_CLI.CD_EMP = EST_PED_VD.CD_EMP AND 
							RC_CLI.CD_CLI = EST_PED_VD.CD_CLI) 
				WHEN DEST_ORIG = 1 THEN
                          (SELECT     
								RZ_FORN
                            FROM          
								PG_FORN
                            WHERE      
								PG_FORN.CD_EMP = EST_PED_VD.CD_EMP AND 
								PG_FORN.CD_FORN = EST_PED_VD.CD_CLI) 
                WHEN DEST_ORIG = 2 THEN
                          (SELECT     
										NM_FANT
                            FROM          
										PRC_FILIAL
                            WHERE      
								PRC_FILIAL.CD_EMP = EST_PED_VD.CD_EMP AND 
								PRC_FILIAL.CD_FILIAL = EST_PED_VD.CD_CLI) 
                ELSE 
						'DESTINATRIO SEM CLASSIFICACAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICACAO DO MESMO'		
				END, 
	STS_PED = CASE WHEN ISNULL(EST_PED_VD_PENDENCIA.CD_PED,0) >0 THEN
							'PENDENTE'
						WHEN EST_PED_VD.STS_PED IN(1,4) THEN	
							'FATURADO'
						WHEN EST_PED_VD.STS_PED = 2 THEN
							'CANCELADO'
						WHEN EST_PED_VD.STS_PED = 3 THEN
							'CONSIGNADO'
						END,
	CD_FIL_DEST = CASE WHEN DEST_ORIG = 0 AND TP_PRECO IN (1,4) THEN
						(SELECT   
							PRC_FILIAL.CD_FILIAL
						FROM         
							PRC_FILIAL, 
							PRC_FILIAL_RC_CLI 
						WHERE
							PRC_FILIAL.CD_EMP = PRC_FILIAL_RC_CLI.CD_EMP AND 
							PRC_FILIAL.CD_FILIAL = PRC_FILIAL_RC_CLI.CD_FILIAL AND
							PRC_FILIAL_RC_CLI.CD_EMP = EST_PED_VD.CD_EMP AND 
							PRC_FILIAL_RC_CLI.CD_CLI = EST_PED_VD.CD_CLI) 
							 WHEN DEST_ORIG = 0 AND TP_PRECO = 3 THEN
								(SELECT   
									PRC_FILIAL_RC_CLI.CD_FILIAL
								FROM         
									PRC_FILIAL_RC_CLI 
								WHERE
									PRC_FILIAL_RC_CLI.CD_EMP = EST_PED_VD.CD_EMP AND 
									PRC_FILIAL_RC_CLI.CD_CLI = EST_PED_VD.CD_CLI) 
                WHEN DEST_ORIG = 2 THEN
                          (SELECT     
										PRC_FILIAL.CD_FILIAL
                            FROM          
										PRC_FILIAL
                            WHERE      
								PRC_FILIAL.CD_EMP = EST_PED_VD.CD_EMP AND 
								PRC_FILIAL.CD_FILIAL = EST_PED_VD.CD_CLI) 
                ELSE 
						0
				END,
	CNPJ = 	CASE WHEN DEST_ORIG = 0  THEN
						(SELECT     
							CGC_CPF
						FROM      
							RC_CLI
						WHERE      
							RC_CLI.CD_EMP = EST_PED_VD.CD_EMP AND 
							RC_CLI.CD_CLI = EST_PED_VD.CD_CLI) 
				WHEN DEST_ORIG = 1 THEN
                          (SELECT     
								CGC_CPF
                            FROM          
								PG_FORN
                            WHERE      
								PG_FORN.CD_EMP = EST_PED_VD.CD_EMP AND 
								PG_FORN.CD_FORN = EST_PED_VD.CD_CLI) 
                WHEN DEST_ORIG = 2 THEN
                          (SELECT     
										CGC
                            FROM          
										PRC_FILIAL
                            WHERE      
								PRC_FILIAL.CD_EMP = EST_PED_VD.CD_EMP AND 
								PRC_FILIAL.CD_FILIAL = EST_PED_VD.CD_CLI) 
                ELSE 
						''		
				END,
	ISNULL(EST_PROD_TRANSF_PED_VD.CD_CTR,0) AS CD_RESSUP,
	ISNULL(ERROS_VALIDACAO,'') AS ERROS_VALIDACAO,
	STS_PED_ERRO_FAT = CASE WHEN ISNULL(EST_PED_VD_ERROS_VALIDACAO.CD_PED,0) >0 THEN
							'ERRO FATURAMENTO'
						ELSE 
							''
						END,
	EST_PED_VD.DT_VALID,
	EST_PED_VD.CD_USU,
	DEST_ORIG,
    ISNULL(EST_PED_VD_PENDENCIA.FLAG_REALIZAR_FAT, 0) FLAG_REALIZAR_FAT
FROM         
	EST_PED_VD INNER JOIN GLB_MOV_OP ON 
	EST_PED_VD.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	LEFT OUTER JOIN EST_PED_VD_ERROS_VALIDACAO ON
	EST_PED_VD.CD_EMP = EST_PED_VD_ERROS_VALIDACAO.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PED_VD_ERROS_VALIDACAO.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PED_VD_ERROS_VALIDACAO.CD_PED 
	LEFT OUTER JOIN EST_NF_SAI_PED_VD  ON
	EST_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED 
	LEFT OUTER JOIN EST_PROD_TRANSF_PED_VD  ON
	EST_PED_VD.CD_EMP = EST_PROD_TRANSF_PED_VD.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PROD_TRANSF_PED_VD.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PROD_TRANSF_PED_VD.CD_PED
	LEFT OUTER JOIN EST_PED_VD_PENDENCIA  ON
	EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP AND
	EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL AND
	EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED  
