IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_DEB_HIST]'))
DROP VIEW [dbo].[V_PG_DEB_HIST]
GO
CREATE VIEW [dbo].[V_PG_DEB_HIST]  
AS  
SELECT       
	PG_DEB.CD_EMP,   
	PG_DEB.CD_FILIAL,   
	PG_DEB.CD_PG_CRED,   
	PG_DEB.CD_DP_LANC,   
	PG_DEB.DT_PAG,   
	PG_DEB.VL_JUR AS VL_JUR,   
	PG_DEB.VL_DESC,   
	PG_DEB.VL_IMP,   
	PG_DEB.VLR_DEVOLUCAO,  
	PG_DEB.VL_PGTO AS VL_PGTO,   
	PG_DEB.HIST AS HIST,   
	BC_CONTAS.NR_CNT,   
	BC_CONTAS.CD_BC,   
	0 AS TP_BAIXA  ,
	AUTENTICACAO
FROM           
	PG_DEB,   
	PG_DEB_BC_LANC,   
	BC_LANC,   
	BC_CONTAS  
WHERE       
	PG_DEB.CD_EMP = PG_DEB_BC_LANC.CD_EMP AND   
	PG_DEB.CD_FILIAL = PG_DEB_BC_LANC.CD_FILIAL AND   
	PG_DEB.CD_PG_CRED = PG_DEB_BC_LANC.CD_PG_CRED AND   
	PG_DEB.CD_DP_LANC = PG_DEB_BC_LANC.CD_DP_LANC AND   
	PG_DEB_BC_LANC.CD_EMP = BC_LANC.CD_EMP AND   
	PG_DEB_BC_LANC.CD_BC_LANC = BC_LANC.CD_BC_LANC AND   
	BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND   
	BC_LANC.CD_CNT = BC_CONTAS.CD_CNT  
  
UNION  
  
SELECT       
	PG_DEB.CD_EMP,   
	PG_DEB.CD_FILIAL,   
	PG_DEB.CD_PG_CRED,   
	CD_DP_LANC,   
	DT_PAG,   
	VL_JUR AS VL_JUR,   
	VL_DESC,   
	VL_IMP,  
	PG_DEB.VLR_DEVOLUCAO,   
	VL_PGTO AS VL_PGTO,   
	HIST AS HIST,   
	'' AS NR_CNT,   
	0 AS CD_BC,   
	1 AS TP_BAIXA  ,
	AUTENTICACAO 
FROM           
	PG_DEB  
WHERE      
 NOT EXISTS(SELECT       
     CD_PG_CRED  
              FROM            
     PG_DEB_BC_LANC  
              WHERE        
     PG_DEB.CD_EMP = PG_DEB_BC_LANC.CD_EMP AND   
     PG_DEB.CD_FILIAL = PG_DEB_BC_LANC.CD_FILIAL AND   
     PG_DEB.CD_PG_CRED = PG_DEB_BC_LANC.CD_PG_CRED AND   
     PG_DEB.CD_DP_LANC = PG_DEB_BC_LANC.CD_DP_LANC)   
 AND NOT EXISTS (SELECT       
      CD_PG_CRED  
                    FROM            
      BC_CHQ_LANC_CPL  
                    WHERE        
      PG_DEB.CD_EMP = BC_CHQ_LANC_CPL.CD_EMP AND   
      PG_DEB.CD_FILIAL = BC_CHQ_LANC_CPL.CD_FILIAL AND   
                        PG_DEB.CD_PG_CRED = BC_CHQ_LANC_CPL.CD_PG_CRED AND   
      PG_DEB.CD_DP_LANC = BC_CHQ_LANC_CPL.CD_DP_LANC)  
 AND NOT EXISTS (SELECT       
      CD_PG_CRED  
                    FROM            
      PG_DEB_LOTE_PG_DEB  
                    WHERE        
      PG_DEB.CD_EMP = PG_DEB_LOTE_PG_DEB.CD_EMP AND   
      PG_DEB.CD_FILIAL = PG_DEB_LOTE_PG_DEB.CD_FILIAL AND   
                        PG_DEB.CD_PG_CRED = PG_DEB_LOTE_PG_DEB.CD_PG_CRED AND   
      PG_DEB.CD_DP_LANC = PG_DEB_LOTE_PG_DEB.CD_DP_LANC)  
UNION  
  
SELECT       
	PG_DEB.CD_EMP,   
	PG_DEB.CD_FILIAL,   
	PG_DEB.CD_PG_CRED,   
	PG_DEB.CD_DP_LANC,   
	PG_DEB.DT_PAG,   
	PG_DEB.VL_JUR AS VL_JUR,   
	PG_DEB.VL_DESC,   
	PG_DEB.VL_IMP,  
	PG_DEB.VLR_DEVOLUCAO,   
	PG_DEB.VL_PGTO AS VL_PGTO,   
	PG_DEB.HIST AS HIST,   
	'' AS NR_CNT,   
	0 AS CD_BC,   
	1 AS TP_BAIXA  ,
	AUTENTICACAO 
FROM           
	PG_DEB INNER JOIN PG_DEB_LOTE_PG_DEB ON
    PG_DEB.CD_EMP = PG_DEB_LOTE_PG_DEB.CD_EMP AND   
    PG_DEB.CD_FILIAL = PG_DEB_LOTE_PG_DEB.CD_FILIAL AND   
    PG_DEB.CD_PG_CRED = PG_DEB_LOTE_PG_DEB.CD_PG_CRED AND   
    PG_DEB.CD_DP_LANC = PG_DEB_LOTE_PG_DEB.CD_DP_LANC
WHERE      
	PG_DEB.CD_CNT=0
UNION  
SELECT       
	PG_DEB.CD_EMP,   
	PG_DEB.CD_FILIAL,   
	PG_DEB.CD_PG_CRED,   
	PG_DEB.CD_DP_LANC,   
	PG_DEB.DT_PAG,   
	PG_DEB.VL_JUR,   
	PG_DEB.VL_DESC,   
	PG_DEB.VL_IMP,   
	PG_DEB.VLR_DEVOLUCAO,  
	PG_DEB.VL_PGTO,   
	PG_DEB.HIST,   
	BC_CONTAS.NR_CNT,   
	BC_CONTAS.CD_BC,   
	0 AS TP_BAIXA  ,
	AUTENTICACAO 
FROM           
	PG_DEB INNER JOIN PG_DEB_LOTE_PG_DEB ON   
	PG_DEB.CD_EMP = PG_DEB_LOTE_PG_DEB.CD_EMP AND   
	PG_DEB.CD_FILIAL = PG_DEB_LOTE_PG_DEB.CD_FILIAL AND   
	PG_DEB.CD_PG_CRED = PG_DEB_LOTE_PG_DEB.CD_PG_CRED AND   
	PG_DEB.CD_DP_LANC = PG_DEB_LOTE_PG_DEB.CD_DP_LANC   
	INNER JOIN PG_DEB_LOTE_BC_LANC ON   
	PG_DEB_LOTE_PG_DEB.CD_EMP = PG_DEB_LOTE_BC_LANC.CD_EMP AND   
	PG_DEB_LOTE_PG_DEB.CD_PG_DEB_LOTE = PG_DEB_LOTE_BC_LANC.CD_PG_DEB_LOTE   
	INNER JOIN BC_LANC ON   
	PG_DEB_LOTE_BC_LANC.CD_EMP = BC_LANC.CD_EMP AND   
	PG_DEB_LOTE_BC_LANC.CD_BC_LANC = BC_LANC.CD_BC_LANC   
	INNER JOIN BC_CONTAS ON   
	BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND   
	BC_LANC.CD_CNT = BC_CONTAS.CD_CNT   
UNION  
SELECT       
	 PG_DEB.CD_EMP,   
	 PG_DEB.CD_FILIAL,   
	 PG_DEB.CD_PG_CRED,   
	 PG_DEB.CD_DP_LANC,   
	 PG_DEB.DT_PAG,   
	 PG_DEB.VL_JUR,   
	 PG_DEB.VL_DESC,   
	 PG_DEB.VL_IMP,   
	 PG_DEB.VLR_DEVOLUCAO,  
	 PG_DEB.VL_PGTO,   
	 PG_DEB.HIST,   
	 BC_CONTAS.NR_CNT,   
	 BC_CONTAS.CD_BC,   
	 0 AS TP_BAIXA  ,
	 AUTENTICACAO 
FROM           
 PG_DEB,   
 BC_CHQ_LANC_CPL,   
 BC_CHQ_LANC,   
 BC_CONTAS  
WHERE       
	PG_DEB.CD_EMP = BC_CHQ_LANC_CPL.CD_EMP AND   
	PG_DEB.CD_FILIAL = BC_CHQ_LANC_CPL.CD_FILIAL AND   
	PG_DEB.CD_PG_CRED = BC_CHQ_LANC_CPL.CD_PG_CRED AND   
	PG_DEB.CD_DP_LANC = BC_CHQ_LANC_CPL.CD_DP_LANC AND   
	BC_CHQ_LANC_CPL.CD_EMP = BC_CHQ_LANC.CD_EMP AND   
	BC_CHQ_LANC_CPL.CD_CHQ_LANC = BC_CHQ_LANC.CD_CHQ_LANC AND   
	BC_CHQ_LANC.CD_EMP = BC_CONTAS.CD_EMP AND   
	BC_CHQ_LANC.CD_CNT = BC_CONTAS.CD_CNT  
UNION  
SELECT       
	PG_DEB.CD_EMP,   
	PG_DEB.CD_FILIAL,   
	PG_DEB.CD_PG_CRED,   
	PG_DEB.CD_DP_LANC,   
	PG_DEB.DT_PAG,   
	PG_DEB.VL_JUR,   
	PG_DEB.VL_DESC,   
	PG_DEB.VL_IMP,   
	PG_DEB.VLR_DEVOLUCAO,  
	PG_DEB.VL_PGTO,   
	PG_DEB.HIST,   
	'VINCULO COM DEVOLUCAO DE COMPRAS'NR_CNT,   
	'0' AS CD_BC,   
	0 AS TP_BAIXA  ,
	AUTENTICACAO 
FROM           
	PG_DEB INNER JOIN PG_DEB_LOTE_PG_DEB ON   
	PG_DEB.CD_EMP = PG_DEB_LOTE_PG_DEB.CD_EMP AND   
	PG_DEB.CD_FILIAL = PG_DEB_LOTE_PG_DEB.CD_FILIAL AND   
	PG_DEB.CD_PG_CRED = PG_DEB_LOTE_PG_DEB.CD_PG_CRED AND   
	PG_DEB.CD_DP_LANC = PG_DEB_LOTE_PG_DEB.CD_DP_LANC   
    INNER JOIN PG_DEB_FORN_PG_DEB ON 
    PG_DEB_LOTE_PG_DEB.CD_EMP = PG_DEB_FORN_PG_DEB.CD_EMP AND
    PG_DEB_LOTE_PG_DEB.CD_PG_DEB_LOTE = PG_DEB_FORN_PG_DEB.CD_PG_DEB_LOTE 