/****** Object:  View [dbo].[V_EST_PED_CP]    Script Date: 06/20/2013 20:08:31 ******/
IF EXISTS
(
    SELECT
		 *
    FROM sys.views
    WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PED_CP]')
)
DROP VIEW
	[dbo].[V_EST_PED_CP];
GO
--versao 2694
--SELECT * FROM V_EST_PED_CP WHERE CD_EMP =1 AND CD_FILIAL = 2 AND DT_EMI >='2018-01-01'
CREATE VIEW [dbo].[V_EST_PED_CP]
AS
	SELECT
		  dbo.EST_PROD_CPL.CD_EMP
		, dbo.EST_PROD_CPL.CD_FILIAL
		, dbo.EST_PED_CP.CD_PED
		, 0 AS CD_NF
		, dbo.EST_PED_CP.CD_FORN
		, dbo.EST_PED_CP.CD_NT_MOV
		, dbo.GLB_MOV_OP.DS_MOV_OP
		,EST_PED_CP.VLR_PED 
		, dbo.EST_PED_CP.VLR_ICMS
		, dbo.EST_PED_CP.VLR_ENCG_FIN
		, dbo.EST_PED_CP.PERC_ICMS_ENCG_FIN
		, dbo.EST_PED_CP.VLR_DESC
		, dbo.EST_PED_CP.VLR_IPI
		, dbo.EST_PED_CP.VLR_BASE_SUBS
		, dbo.EST_PED_CP.VLR_ICMS_SUBS
		, dbo.EST_PED_CP.VLR_BASE_ICMS
		, dbo.EST_PED_CP.VLR_FRETE
		, EST_PED_CP.CALC_SUBS_PMC
		, EST_PED_CP.PERC_REDU_SUBS_PMC
		, dbo.EST_PED_CP.NF_IPI_BASE_CALC
		, dbo.EST_PED_CP.STS_PED
		, dbo.EST_PED_CP_IT.CD_PROD
		, dbo.EST_PROD.DS_PROD
		, dbo.EST_PROD.CD_UN_CP
		, dbo.EST_PROD.CTR_LOTE
		, dbo.EST_PED_CP_IT.QT_IT
		, dbo.EST_PED_CP_IT.VLR_IT
		, dbo.EST_PED_CP_IT.ALIQ_ICMS
		, dbo.EST_PED_CP_IT.PERC_IPI
		, dbo.EST_PED_CP_IT.PERC_IPI_CUSTO
		, dbo.EST_PED_CP_IT.VLR_DESC AS VLR_DESC_IT
		, dbo.EST_PED_CP_IT.PERC_DESC
		, dbo.EST_PED_CP_IT.CD_TRIB
		, dbo.GLB_MOV_OP.TP_MOV
		, dbo.EST_PED_CP_IT.CD_NT_OP
		, dbo.PG_FORN.RZ_FORN
		,PG_FORN.NM_FANT AS NM_FANT_FORN
		,PG_FORN.END_FORN
		,PG_FORN.BAIRRO_FORN 
		,GLB_CID.DS_CID
		,GLB_CID.UF
		,PG_FORN.CEP
		,PG_FORN.TEL
		,PG_FORN.EMAIL 
		,EST_PED_CP.UUID 
		, dbo.EST_PED_CP_IT.VLR_BASE_SUBS AS VLR_BASE_SUBS_IT
		, dbo.EST_PED_CP_IT.VLR_ICMS_SUBS AS VLR_ICMS_SUBS_IT
		, dbo.EST_PED_CP_IT.VLR_BASE_ICMS AS VLR_BASE_ICMS_IT
		, dbo.EST_PED_CP.DT_EMI
		, dbo.EST_PED_CP.DT_PREV_ENT
		, dbo.EST_PED_CP.DT_CAD
		, dbo.EST_PED_CP.DT_ULT_ATU
		, dbo.EST_PED_CP.CD_USU
		, dbo.EST_PED_CP_IT.SEQ_ITEM
		, dbo.EST_PED_CP_IT.VLR_CUSTO
		, dbo.EST_PED_CP_IT.PERC_IVA
		, dbo.EST_PED_CP_IT.QT_EMB
		, dbo.EST_PED_CP.DT_CANCEL
		, dbo.EST_PED_CP.CD_USU_CANCEL
		, dbo.EST_PED_CP.NR_REQ_FORN
		, dbo.EST_PED_CP_IT.VLR_IPI AS VLR_IPI_IT
		, ISNULL(dbo.V_EST_PED_CP_QT_ENT.QT_NF_ENT, 0) AS QT_NF_ENT
		, ufd.QT_SLD / CASE
					    WHEN ISNULL(dbo.EST_PED_CP_IT.QT_EMB, 0) = 0
					    THEN 1
					    ELSE dbo.EST_PED_CP_IT.QT_EMB
					END AS SLD_PROD
		, dbo.GLB_MOV_OP.DS_MOV_OP_USUAL
		, dbo.EST_PED_CP.TP_FRETE
		, dbo.EST_PED_CP.CONHECIMENTO
		, ISNULL(EST_PROD.PROD_LIBERADO, 0) AS PROD_LIBERADO
		, EST_PED_CP.CD_PED_FORN
		, ISNULL(EST_PED_CP.FLAG_GERAR_CROSS, 0) AS FLAG_GERAR_CROSS
		, ISNULL(EST_PED_CP.CD_COND_PGTO, 0) AS CD_COND_PGTO
		, case WHEN EST_PROD_CPL.CD_SIGLA =1 THEN 'A'
			  WHEN EST_PROD_CPL.CD_SIGLA =2 THEN 'B'
			  WHEN EST_PROD_CPL.CD_SIGLA =3 THEN 'C'
			  WHEN EST_PROD_CPL.CD_SIGLA =5 THEN 'AA'
			 ELSE 'D' END		DS_SIGLA
		, EST_PROD.CD_FABRIC
		, ISNULL(EST_PED_CP_IT.PERC_DESC_FINAN, 0) AS PERC_DESC_FINAN
		, GLB_NT_OP.CD_NT_OP_FC
		, ISNULL(EST_TRIB.CD_TRIB_FC, 0) AS CD_TRIB_FC
		, EST_PROD_UN_MED.DS_UN
		, EST_PED_CP.DT_FATURAMENTO
		, EST_PED_CP.DT_HR_CAD
	    ,DBO.TST_EST_PED_CP_STATUS(EST_PED_CP.CD_EMP, EST_PED_CP.CD_FILIAL, EST_PED_CP.CD_PED) AS STATUS_PED,
		PRC_FILIAL.NM_FANT AS NM_FANT_FILIAL,
		ISNULL(GLB_COND_PGTO.DS_COND_PGTO,'') AS DS_COND_PGTO,
		ISNULL(PG_FORN.CGC_CPF,'') CGC_CPF_FORN,
		ISNULL(PG_FORN.TIPO_FRETE,0) TIPO_FRETE,
		GLB_MOV_OP.ATU_EST
	FROM
		dbo.EST_PED_CP
		INNER JOIN dbo.EST_PED_CP_IT
		ON dbo.EST_PED_CP.CD_EMP = dbo.EST_PED_CP_IT.CD_EMP
		   AND dbo.EST_PED_CP.CD_FILIAL = dbo.EST_PED_CP_IT.CD_FILIAL
		   AND dbo.EST_PED_CP.CD_PED = dbo.EST_PED_CP_IT.CD_PED
		
		INNER JOIN dbo.EST_PROD_CPL
		ON dbo.EST_PROD_CPL.CD_EMP	    = dbo.EST_PED_CP_IT.CD_EMP
		   AND dbo.EST_PROD_CPL.CD_FILIAL = dbo.EST_PED_CP_IT.CD_FILIAL
		   AND dbo.EST_PROD_CPL.CD_PROD   = dbo.EST_PED_CP_IT.CD_PROD
		CROSS APPLY
		  (SELECT dbo.UFD_EST_PED_CP_IT_SLD (EST_PROD_CPL.CD_EMP, EST_PROD_CPL.CD_FILIAL, EST_PED_CP.CD_PED, EST_PROD_CPL.CD_PROD ) QT_SLD ) ufd

		INNER JOIN dbo.EST_PROD
		ON dbo.EST_PROD_CPL.CD_EMP = dbo.EST_PROD.CD_EMP
		   AND dbo.EST_PROD_CPL.CD_PROD = dbo.EST_PROD.CD_PROD

		INNER JOIN dbo.EST_PROD_UN_MED
		ON dbo.EST_PROD.CD_EMP = dbo.EST_PROD_UN_MED.CD_EMP
		   AND dbo.EST_PROD.CD_EMP = dbo.EST_PROD_UN_MED.CD_EMP
		   AND dbo.EST_PROD.CD_UN_CP = dbo.EST_PROD_UN_MED.CD_CTR_UN

		INNER JOIN dbo.GLB_MOV_OP
		ON dbo.EST_PED_CP.CD_NT_MOV = dbo.GLB_MOV_OP.CD_MOV_OP
		
		INNER JOIN dbo.PG_FORN
		ON dbo.EST_PED_CP.CD_EMP = dbo.PG_FORN.CD_EMP
		   AND dbo.EST_PED_CP.CD_FORN = dbo.PG_FORN.CD_FORN

		LEFT OUTER JOIN dbo.V_EST_PED_CP_QT_ENT
		ON dbo.EST_PED_CP.CD_EMP = dbo.V_EST_PED_CP_QT_ENT.CD_EMP
		   AND dbo.EST_PED_CP.CD_FILIAL = dbo.V_EST_PED_CP_QT_ENT.CD_FILIAL
		   AND dbo.EST_PED_CP.CD_PED = dbo.V_EST_PED_CP_QT_ENT.CD_PED

		LEFT OUTER JOIN GLB_NT_OP
		ON EST_PED_CP_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP
		LEFT OUTER JOIN EST_TRIB
		ON EST_PED_CP_IT.CD_TRIB = EST_TRIB.CD_TRIB
		INNER JOIN PRC_FILIAL ON
		EST_PED_CP.CD_EMP = PRC_FILIAL.CD_EMP AND
		EST_PED_CP.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
		INNER JOIN GLB_COND_PGTO ON
		EST_PED_CP.CD_EMP = GLB_COND_PGTO.CD_EMP AND
		EST_PED_CP.CD_COND_PGTO = GLB_COND_PGTO.CD_COND_PGTO
		INNER JOIN GLB_CID ON
		PG_FORN.CD_CID = GLB_CID.CD_CID