IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_LIQ_DUP]'))
	DROP VIEW [dbo].[V_PG_LIQ_DUP]
GO
  
CREATE VIEW 
	[dbo].[V_PG_LIQ_DUP] AS  		
	--RETORNAR AS DEVOLUÇÕES AO FORNECEDOR, SEM CONTA BANCARIA ASSOCIADA.
	-- CHAMADO 8510 - DROGA CLARA
	SELECT DISTINCT 
	V_PG_CRED.CD_EMP                                                                                       ,  
	V_PG_CRED.CD_FILIAL                                                                                    ,  
	CONVERT(VARCHAR(20),V_PG_CRED.CD_PG_CRED) + 'DP' CD_INDENT                                             ,  
	V_PG_CRED.NR_DP                                                                                        ,  
	CONVERT(VARCHAR(20),V_PG_CRED.NR_TIT) AS NR_TIT                                                        ,  
	V_PG_CRED.ANO                                                                                          ,  
	V_PG_CRED.PARC                                                                                         ,  
	V_PG_CRED.CD_FORN                                                                                      ,  
	V_PG_CRED.NM_FANT                                                                                      ,  
	V_PG_CRED.RZ_FORN                                                                                      ,  
	V_PG_CRED.VLR_DP                                                                                       ,  
	ISNULL(BC_CONTAS.CD_BC,'') AS CD_BC	                                                                                      ,  
	V_PG_CRED.DT_EMIS                                                                                      ,  
	V_PG_CRED.DT_VENCTO                                                                                    ,  
	V_PG_CRED.VLR_DP + V_PG_CRED.VLR_DESP_OUTR - PG_DEB.VL_PGTO AS SLD_DP                                  ,  
	V_PG_CRED.STS_DP                                                                                       ,  
	PG_DEB.DT_PAG                                                                                          ,  
	(PG_DEB.VL_PGTO + PG_DEB.VL_JUR) - (PG_DEB.VL_IMP + PG_DEB.VLR_DEVOLUCAO + PG_DEB.VL_DESC) AS VL_PGTO  ,  
	PG_DEB.VL_JUR                                                                              AS VLR_JUROS,  
	V_PG_CRED.VLR_DESP_OUTR                                                                                ,  
	PG_DEB.VL_DESC AS VLR_DESC                                                                             ,  
	PG_DEB.VL_IMP                                                                                          ,  
	PG_DEB.VLR_DEVOLUCAO                                                                                   ,  
	PG_DEB.CD_CNT                                                                                          ,  
	ISNULL(BC_CONTAS.NR_CNT,'') AS NR_CNT                                                                                      ,  
	''   AS NR_CHQ                                                                                           ,  
	''   AS SERIE_CHQ                                                                                        ,  
	0    AS ANO_CHQ                                                                                          ,  
	0    AS CD_CNT_CHQ                                                                                       ,  
	NULL AS DT_EMI_CHQ                                                                                       ,  
	0    AS VLR_CHQ                                                                                          ,  
	''   AS CNT_CONTABIL                                                                                     ,  
	GRP_ECONOMICO                                                                                            ,  
	V_PG_CRED.CD_TP_FORN                                                                                     ,  
	'CONTAS A PAGAR - DEV AO FORNECEDOR' AS TRANSACAO                                                                            ,  
	V_PG_CRED.TP_DP,  
	autenticacao,
	V_PG_CRED.HIST,
	0 AS CONSULTA,
	V_PG_CRED.CD_BARRAS_BOLETA
FROM
	V_PG_CRED
    INNER JOIN PG_DEB  ON
		V_PG_CRED.CD_EMP     = PG_DEB.CD_EMP AND  
        V_PG_CRED.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        V_PG_CRED.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        V_PG_CRED.CD_DP_LANC = PG_DEB.CD_DP_LANC AND  
        V_PG_CRED.DT_PAG     = PG_DEB.DT_PAG  
    LEFT OUTER JOIN BC_CONTAS ON
		PG_DEB.CD_EMP = BC_CONTAS.CD_EMP AND  
		PG_DEB.CD_CNT = BC_CONTAS.CD_CNT  
WHERE
	 EXISTS  
	 (SELECT 
		PG_DEB_LOTE_PG_DEB.CD_EMP    ,  
        PG_DEB_LOTE_PG_DEB.CD_FILIAL ,  
        PG_DEB_LOTE_PG_DEB.CD_PG_CRED,  
        PG_DEB_LOTE_PG_DEB.CD_DP_LANC  
    FROM
		PG_DEB_LOTE_PG_DEB
    WHERE
		PG_DEB_LOTE_PG_DEB.CD_EMP     = PG_DEB.CD_EMP AND  
        PG_DEB_LOTE_PG_DEB.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        PG_DEB_LOTE_PG_DEB.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        PG_DEB_LOTE_PG_DEB.CD_DP_LANC = PG_DEB.CD_DP_LANC)  
	AND  NOT EXISTS (
	SELECT 
		BC_CONTAS.CD_EMP,
		BC_CONTAS.CD_CNT  
	FROM 
		BC_CONTAS
	WHERE 
		PG_DEB.CD_EMP = BC_CONTAS.CD_EMP AND  
		PG_DEB.CD_CNT = BC_CONTAS.CD_CNT  
	)
	
	UNION ALL
	
SELECT DISTINCT 
	V_PG_CRED.CD_EMP                                                                                       ,  
	V_PG_CRED.CD_FILIAL                                                                                    ,  
	CONVERT(VARCHAR(20),V_PG_CRED.CD_PG_CRED) + 'DP' CD_INDENT                                             ,  
	V_PG_CRED.NR_DP                                                                                        ,  
	CONVERT(VARCHAR(20),V_PG_CRED.NR_TIT) AS NR_TIT                                                        ,  
	V_PG_CRED.ANO                                                                                          ,  
	V_PG_CRED.PARC                                                                                         ,  
	V_PG_CRED.CD_FORN                                                                                      ,  
	V_PG_CRED.NM_FANT                                                                                      ,  
	V_PG_CRED.RZ_FORN                                                                                      ,  
	V_PG_CRED.VLR_DP                                                                                       ,  
	ISNULL(BC_CONTAS.CD_BC,'') AS CD_BC	                                                                                      ,  
	V_PG_CRED.DT_EMIS                                                                                      ,  
	V_PG_CRED.DT_VENCTO                                                                                    ,  
	V_PG_CRED.VLR_DP + V_PG_CRED.VLR_DESP_OUTR - PG_DEB.VL_PGTO AS SLD_DP                                  ,  
	V_PG_CRED.STS_DP                                                                                       ,  
	PG_DEB.DT_PAG                                                                                          ,  
	(PG_DEB.VL_PGTO + PG_DEB.VL_JUR) - (PG_DEB.VL_IMP + PG_DEB.VLR_DEVOLUCAO + PG_DEB.VL_DESC) AS VL_PGTO  ,  
	PG_DEB.VL_JUR                                                                              AS VLR_JUROS,  
	V_PG_CRED.VLR_DESP_OUTR                                                                                ,  
	PG_DEB.VL_DESC AS VLR_DESC                                                                             ,  
	PG_DEB.VL_IMP                                                                                          ,  
	PG_DEB.VLR_DEVOLUCAO                                                                                   ,  
	PG_DEB.CD_CNT                                                                                          ,  
	ISNULL(BC_CONTAS.NR_CNT,'') AS NR_CNT                                                                                      ,  
	''   AS NR_CHQ                                                                                           ,  
	''   AS SERIE_CHQ                                                                                        ,  
	0    AS ANO_CHQ                                                                                          ,  
	0    AS CD_CNT_CHQ                                                                                       ,  
	NULL AS DT_EMI_CHQ                                                                                       ,  
	0    AS VLR_CHQ                                                                                          ,  
	''   AS CNT_CONTABIL                                                                                     ,  
	GRP_ECONOMICO                                                                                            ,  
	V_PG_CRED.CD_TP_FORN                                                                                     ,  
	'CONTAS A PAGAR' AS TRANSACAO                                                                            ,  
	V_PG_CRED.TP_DP,  
	autenticacao,
	V_PG_CRED.HIST,
	0 AS CONSULTA,
	V_PG_CRED.CD_BARRAS_BOLETA
FROM
	V_PG_CRED
    INNER JOIN PG_DEB  ON
		V_PG_CRED.CD_EMP     = PG_DEB.CD_EMP AND  
        V_PG_CRED.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        V_PG_CRED.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        V_PG_CRED.CD_DP_LANC = PG_DEB.CD_DP_LANC AND  
        V_PG_CRED.DT_PAG     = PG_DEB.DT_PAG  
    LEFT OUTER JOIN BC_CONTAS ON
		PG_DEB.CD_EMP = BC_CONTAS.CD_EMP AND  
		PG_DEB.CD_CNT = BC_CONTAS.CD_CNT  
WHERE
	NOT EXISTS  
    (SELECT 
		BC_CHQ_LANC_CPL.CD_EMP    ,  
        BC_CHQ_LANC_CPL.CD_FILIAL ,  
        BC_CHQ_LANC_CPL.CD_PG_CRED,  
        BC_CHQ_LANC_CPL.CD_DP_LANC  
    FROM
		BC_CHQ_LANC_CPL
    WHERE
		BC_CHQ_LANC_CPL.CD_EMP     = PG_DEB.CD_EMP AND  
        BC_CHQ_LANC_CPL.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        BC_CHQ_LANC_CPL.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        BC_CHQ_LANC_CPL.CD_DP_LANC = PG_DEB.CD_DP_LANC)  
    AND NOT EXISTS  
    (SELECT 
		PG_DEB_LOTE_PG_DEB.CD_EMP    ,  
        PG_DEB_LOTE_PG_DEB.CD_FILIAL ,  
        PG_DEB_LOTE_PG_DEB.CD_PG_CRED,  
        PG_DEB_LOTE_PG_DEB.CD_DP_LANC  
    FROM
		PG_DEB_LOTE_PG_DEB
    WHERE
		PG_DEB_LOTE_PG_DEB.CD_EMP     = PG_DEB.CD_EMP AND  
        PG_DEB_LOTE_PG_DEB.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        PG_DEB_LOTE_PG_DEB.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        PG_DEB_LOTE_PG_DEB.CD_DP_LANC = PG_DEB.CD_DP_LANC)  
              
UNION ALL  
              
SELECT
	V_PG_CRED.CD_EMP                                                                                       ,  
	V_PG_CRED.CD_FILIAL                                                                                    ,  
	CONVERT(VARCHAR(20),V_PG_CRED.CD_PG_CRED) + 'DP2'                                                      ,  
	V_PG_CRED.NR_DP                                                                                        ,  
	CONVERT(VARCHAR(20),V_PG_CRED.NR_TIT) AS NR_TIT                                                        ,  
	V_PG_CRED.ANO                                                                                          ,  
	V_PG_CRED.PARC                                                                                         ,  
	V_PG_CRED.CD_FORN                                                                                      ,  
	V_PG_CRED.NM_FANT                                                                                      ,  
	V_PG_CRED.RZ_FORN                                                                                      ,  
	V_PG_CRED.VLR_DP                                                                                       ,  
	ISNULL(BC_CONTAS.CD_BC,'') AS CD_BC                                               ,  
	V_PG_CRED.DT_EMIS                                                                                      ,  
	V_PG_CRED.DT_VENCTO                                                                                    ,  
	V_PG_CRED.VLR_SLD AS SLD_DP                                                                            ,  
	V_PG_CRED.STS_DP                                                                                       ,  
	PG_DEB.DT_PAG                                                                                          ,  
	(PG_DEB.VL_PGTO + PG_DEB.VL_JUR) - (PG_DEB.VL_IMP + PG_DEB.VLR_DEVOLUCAO + PG_DEB.VL_DESC) AS VL_PGTO  ,  
	PG_DEB.VL_JUR                                                                              AS VLR_JUROS,  
	V_PG_CRED.VLR_DESP_OUTR                                                                                ,  
	PG_DEB.VL_DESC AS VLR_DESC                                                                             ,  
	PG_DEB.VL_IMP                                                                                          ,  
	PG_DEB.VLR_DEVOLUCAO                                                                                   ,  
	PG_DEB.CD_CNT                                                                                          ,  
	ISNULL(BC_CONTAS.NR_CNT,'') AS NR_CNT                                                                                       ,  
	''                                                                                                     ,  
	''                                                                                                     ,  
	0                                                                                                      ,  
	0                                                                                                      ,  
	NULL                                                                                                   ,  
	0                                                                                                      ,  
	''                                                                                                     ,  
	GRP_ECONOMICO                                                                                          ,  
	V_PG_CRED.CD_TP_FORN                                                                                   ,  
	'CONTAS A PAGAR' AS TRANSACAO                                                                          ,  
	V_PG_CRED.TP_DP,  
	autenticacao,
	V_PG_CRED.HIST,
	1 AS CONSULTA,
	V_PG_CRED.CD_BARRAS_BOLETA
FROM
	V_PG_CRED  
    INNER JOIN PG_DEB ON
		V_PG_CRED.CD_EMP     = PG_DEB.CD_EMP AND  
        V_PG_CRED.CD_FILIAL  = PG_DEB.CD_FILIAL AND
        V_PG_CRED.CD_PG_CRED = PG_DEB.CD_PG_CRED AND
        V_PG_CRED.CD_DP_LANC = PG_DEB.CD_DP_LANC AND
        V_PG_CRED.DT_PAG     = PG_DEB.DT_PAG
    INNER JOIN BC_CONTAS ON
		PG_DEB.CD_EMP = BC_CONTAS.CD_EMP AND  
		PG_DEB.CD_CNT = BC_CONTAS.CD_CNT  
	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 ON
		PG_DEB_LOTE_PG_DEB.CD_EMP         = PG_DEB_LOTE.CD_EMP AND  
        PG_DEB_LOTE_PG_DEB.CD_PG_DEB_LOTE = PG_DEB_LOTE.CD_PG_DEB_LOTE  
    INNER JOIN PG_DEB_LOTE_BC_LANC ON
		PG_DEB_LOTE.CD_EMP         = PG_DEB_LOTE_BC_LANC.CD_EMP AND  
        PG_DEB_LOTE.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  
WHERE  
	PG_DEB.CD_CNT > 0 AND  
        NOT EXISTS  
        (SELECT 
			BC_CHQ_LANC_CPL.CD_EMP    ,  
            BC_CHQ_LANC_CPL.CD_FILIAL ,  
            BC_CHQ_LANC_CPL.CD_PG_CRED,  
            BC_CHQ_LANC_CPL.CD_DP_LANC  
        FROM
			BC_CHQ_LANC_CPL
        WHERE
			BC_CHQ_LANC_CPL.CD_EMP     = PG_DEB.CD_EMP AND  
            BC_CHQ_LANC_CPL.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
            BC_CHQ_LANC_CPL.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
            BC_CHQ_LANC_CPL.CD_DP_LANC = PG_DEB.CD_DP_LANC)  
             
UNION ALL  
             
SELECT
	V_PG_CRED.CD_EMP                                                                                       ,  
    V_PG_CRED.CD_FILIAL                                                                                    ,  
    CONVERT(VARCHAR(20),V_PG_CRED.CD_PG_CRED) + 'DP'                                                       ,  
    V_PG_CRED.NR_DP                                                                                        ,  
    CONVERT(VARCHAR(20),V_PG_CRED.NR_TIT) AS NR_TIT                                                        ,  
    V_PG_CRED.ANO                                                                                          ,  
    V_PG_CRED.PARC                                                                                         ,  
    V_PG_CRED.CD_FORN                                                                                      ,  
    V_PG_CRED.NM_FANT                                                                                      ,  
    V_PG_CRED.RZ_FORN                                                                                      ,  
    V_PG_CRED.VLR_DP                                                                                       ,  
    BC_CONTAS.CD_BC                                                                                        ,  
    V_PG_CRED.DT_EMIS                                                                                      ,  
    V_PG_CRED.DT_VENCTO                        ,  
    V_PG_CRED.VLR_SLD AS SLD_DP                                                                            ,  
    V_PG_CRED.STS_DP                                                                                       ,  
    PG_DEB.DT_PAG                                                                                          ,  
    (PG_DEB.VL_PGTO + PG_DEB.VL_JUR) - (PG_DEB.VL_IMP + PG_DEB.VLR_DEVOLUCAO + PG_DEB.VL_DESC) AS VL_PGTO  ,  
    PG_DEB.VL_JUR                                                                              AS VLR_JUROS,  
    V_PG_CRED.VLR_DESP_OUTR                                                                                ,  
    PG_DEB.VL_DESC AS VLR_DESC                                                                             ,  
    PG_DEB.VL_IMP                                                                                          ,  
    PG_DEB.VLR_DEVOLUCAO                                                                                   ,  
    PG_DEB.CD_CNT                                                                                          ,  
    BC_CONTAS.NR_CNT                                                                                       ,  
    CONVERT(VARCHAR(20),BC_CHQ_LANC.NR_CHQ) AS NR_CHQ                                                      ,  
    BC_CHQ_LANC.SERIE_CHQ                                                                                  ,  
    BC_CHQ_LANC.ANO_CHQ                                                                                    ,  
    BC_CHQ_LANC.CD_CNT AS CD_CNT_CHQ                                                                       ,  
    BC_CHQ_LANC.DT_EMI_CHQ                                                                                 ,  
    BC_CHQ_LANC.VLR_CHQ                                                                                    ,  
    ''                                                                                                     ,  
    GRP_ECONOMICO                                                                                          ,  
    V_PG_CRED.CD_TP_FORN                                                                                   ,  
    'CONTAS A PAGAR' AS TRANSACAO                                                                          ,  
    V_PG_CRED.TP_DP,  
    autenticacao,
	V_PG_CRED.HIST,
	3 AS CONSULTA,
	V_PG_CRED.CD_BARRAS_BOLETA
FROM
	V_PG_CRED  
    INNER JOIN PG_DEB ON
		V_PG_CRED.CD_EMP     = PG_DEB.CD_EMP AND  
        V_PG_CRED.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        V_PG_CRED.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        V_PG_CRED.CD_DP_LANC = PG_DEB.CD_DP_LANC AND  
        V_PG_CRED.DT_PAG     = PG_DEB.DT_PAG  
    INNER JOIN BC_CONTAS ON
		PG_DEB.CD_EMP = BC_CONTAS.CD_EMP AND  
		PG_DEB.CD_CNT = BC_CONTAS.CD_CNT  
    LEFT OUTER JOIN BC_CHQ_LANC_CPL ON
		PG_DEB.CD_DP_LANC = BC_CHQ_LANC_CPL.CD_DP_LANC AND  
        PG_DEB.CD_PG_CRED = BC_CHQ_LANC_CPL.CD_PG_CRED AND  
        PG_DEB.CD_FILIAL  = BC_CHQ_LANC_CPL.CD_FILIAL AND  
        PG_DEB.CD_EMP     = BC_CHQ_LANC_CPL.CD_EMP  
    INNER JOIN BC_CHQ_LANC ON
		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  
WHERE  
	NOT EXISTS  
    (SELECT 
		PG_DEB_LOTE_PG_DEB.CD_EMP  
    FROM    
		PG_DEB_LOTE_PG_DEB  
    WHERE   
		PG_DEB_LOTE_PG_DEB.CD_EMP     = PG_DEB.CD_EMP AND  
        PG_DEB_LOTE_PG_DEB.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        PG_DEB_LOTE_PG_DEB.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        PG_DEB_LOTE_PG_DEB.CD_DP_LANC = PG_DEB.CD_DP_LANC)  
            
UNION ALL  
            
SELECT 
	V_PG_CRED.CD_EMP                                                                                       ,  
    V_PG_CRED.CD_FILIAL                                                                                    ,  
    CONVERT(VARCHAR(20),V_PG_CRED.CD_PG_CRED) + 'DP'                                                       ,  
    V_PG_CRED.NR_DP                                                                                        ,  
    CONVERT(VARCHAR(20),V_PG_CRED.NR_TIT) AS NR_TIT                                                        ,  
    V_PG_CRED.ANO                                                                                          ,  
    V_PG_CRED.PARC                                                                                         ,  
    V_PG_CRED.CD_FORN                                                                                      ,  
    V_PG_CRED.NM_FANT                                                                                      ,  
    V_PG_CRED.RZ_FORN                                                                                      ,  
    V_PG_CRED.VLR_DP                                                                                       ,  
    BC_CONTAS.CD_BC                                                                                        ,  
    V_PG_CRED.DT_EMIS                                                                                      ,  
    V_PG_CRED.DT_VENCTO                                                                                    ,  
    V_PG_CRED.VLR_SLD AS SLD_DP                                                                            ,  
    V_PG_CRED.STS_DP                                                                                       ,  
    PG_DEB.DT_PAG                                                                                          ,  
    (PG_DEB.VL_PGTO + PG_DEB.VL_JUR) - (PG_DEB.VL_IMP + PG_DEB.VLR_DEVOLUCAO + PG_DEB.VL_DESC) AS VL_PGTO  ,  
    PG_DEB.VL_JUR                                                                              AS VLR_JUROS,  
    V_PG_CRED.VLR_DESP_OUTR                                                                                ,  
    PG_DEB.VL_DESC AS VLR_DESC                                                                             ,  
    PG_DEB.VL_IMP                                                                                          ,  
    PG_DEB.VLR_DEVOLUCAO                                                                                   ,  
    PG_DEB.CD_CNT                                                                                          ,  
    BC_CONTAS.NR_CNT                                                                                       ,  
    CONVERT(VARCHAR(20),BC_CHQ_LANC.NR_CHQ) AS NR_CHQ                                                      ,  
    BC_CHQ_LANC.SERIE_CHQ                                                                                  ,  
    BC_CHQ_LANC.ANO_CHQ                                                                                    ,  
    BC_CHQ_LANC.CD_CNT AS CD_CNT_CHQ                                                                       ,  
    BC_CHQ_LANC.DT_EMI_CHQ                        ,  
    BC_CHQ_LANC.VLR_CHQ                                                                                    ,  
    ''                                                                                                     ,  
    GRP_ECONOMICO                                                                                          ,  
    V_PG_CRED.CD_TP_FORN                                                                                   ,  
    'CONTAS A PAGAR' AS TRANSACAO                                                                          ,  
    V_PG_CRED.TP_DP,  
    autenticacao,
	V_PG_CRED.HIST,
	4 AS CONSULTA,
	V_PG_CRED.CD_BARRAS_BOLETA
FROM   
	PG_DEB_LOTE_BC_LANC  
    INNER JOIN PG_DEB_LOTE ON
		PG_DEB_LOTE_BC_LANC.CD_EMP         = PG_DEB_LOTE.CD_EMP AND  
        PG_DEB_LOTE_BC_LANC.CD_PG_DEB_LOTE = PG_DEB_LOTE.CD_PG_DEB_LOTE  
    INNER JOIN PG_DEB_LOTE_PG_DEB ON
		PG_DEB_LOTE.CD_EMP         = PG_DEB_LOTE_PG_DEB.CD_EMP AND  
        PG_DEB_LOTE.CD_PG_DEB_LOTE = PG_DEB_LOTE_PG_DEB.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 PG_DEB ON
		PG_DEB_LOTE_PG_DEB.CD_DP_LANC = PG_DEB.CD_DP_LANC AND  
        PG_DEB_LOTE_PG_DEB.CD_PG_CRED = PG_DEB.CD_PG_CRED AND  
        PG_DEB_LOTE_PG_DEB.CD_FILIAL  = PG_DEB.CD_FILIAL AND  
        PG_DEB_LOTE_PG_DEB.CD_EMP     = PG_DEB.CD_EMP  
    INNER JOIN V_PG_CRED ON
		PG_DEB.CD_EMP     = V_PG_CRED.CD_EMP AND  
        PG_DEB.CD_FILIAL  = V_PG_CRED.CD_FILIAL AND  
        PG_DEB.CD_PG_CRED = V_PG_CRED.CD_PG_CRED AND  
        PG_DEB.DT_PAG     = V_PG_CRED.DT_PAG  
    INNER JOIN PG_FORN ON
		V_PG_CRED.CD_EMP  = PG_FORN.CD_EMP AND  
        V_PG_CRED.CD_FORN = PG_FORN.CD_FORN  
    INNER JOIN BC_CONTAS ON
		PG_DEB.CD_EMP = BC_CONTAS.CD_EMP AND  
        PG_DEB.CD_CNT = BC_CONTAS.CD_CNT  
    INNER JOIN BC_CHQ_LANC_CPL ON
		PG_DEB.CD_DP_LANC = BC_CHQ_LANC_CPL.CD_DP_LANC AND  
        PG_DEB.CD_PG_CRED = BC_CHQ_LANC_CPL.CD_PG_CRED AND  
        PG_DEB.CD_FILIAL  = BC_CHQ_LANC_CPL.CD_FILIAL AND  
        PG_DEB.CD_EMP     = BC_CHQ_LANC_CPL.CD_EMP  
    INNER JOIN BC_CHQ_LANC ON
		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  
            
UNION ALL  
            
SELECT 
	BC_LANC.CD_EMP                                ,  
    PG_DESP_RAT.CD_FILIAL                         ,  
    CONVERT(VARCHAR(20),BC_LANC.CD_BC_LANC) + 'BC',  
    0 AS NR_DP                                    ,  
    CONVERT(VARCHAR(20),BC_LANC.CD_BC_LANC)       ,  
    0                 AS ANO                                      ,  
    0                 AS PARC                                     ,  
    0                 AS CD_FORN                                  ,  
    BC_LANC.HIST_LANC AS NM_FORN                                  ,  
    BC_LANC.HIST_LANC AS RZ_FORN                                  ,  
    BC_LANC.VLR_LANC  AS VLR_DP                                   ,  
    BC_CONTAS.CD_BC   AS CD_BANCO                                 ,  
    BC_LANC.DT_LANCTO AS DT_EMIS                                  ,  
    BC_LANC.DT_LANCTO AS DT_VENCTO                                ,  
	0 AS SLD_DP                                   ,  
    1                 AS STS_DP                                   ,  
    BC_LANC.DT_LANCTO AS DT_PAG                                   ,  
    BC_LANC.VLR_LANC  AS VL_PGTO                                  ,  
    0                 AS VL_JUR                                   ,  
    0                 AS VLR_DESP_OUTR                            ,  
    0                 AS VL_DESC                                  ,  
    0                 AS VL_IMPOSTO                               ,  
    0                 AS VLR_DEVOLUCAO                            ,  
    BC_LANC.CD_CNT                                                ,  
    BC_CONTAS.NR_CNT                                              ,  
    ''                           AS NR_CHQ                                                  ,  
    ''                           AS SERIE_CHQ                                               ,  
    0                            AS ANO_CHQ                                                 ,  
    0                            AS CD_CNT_CHQ                                              ,  
    NULL                         AS DT_EMI_CHQ                                              ,  
    0                            AS VLR_CHQ                                                 ,  
    GLB_DESP_REC.NR_CNT          AS CNT_CONTABIL                                            ,  
    0                            AS GRP_ECONOMICO                                           ,  
    RC_TP_LANC.CD_CLASS_FLUXO_CX    CD_TP_FORN                                              ,  
    'BANCARIO'                   AS TRANSACAO                                               ,  
    0                            AS TP_DP,  
    '' as autenticacao,
	'' AS HIST,
	5 AS CONSULTA,
	'' as CD_BARRAS_BOLETA
FROM   
	BC_LANC  
    INNER JOIN PG_DESP_RAT_BC_LANC ON
		BC_LANC.CD_EMP     = PG_DESP_RAT_BC_LANC.CD_EMP AND  
        BC_LANC.CD_BC_LANC = PG_DESP_RAT_BC_LANC.CD_BC_LANC  
    INNER JOIN PG_DESP_RAT ON
		PG_DESP_RAT_BC_LANC.CD_EMP      = PG_DESP_RAT.CD_EMP AND  
        PG_DESP_RAT_BC_LANC.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP  
    INNER JOIN BC_CONTAS ON
		BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND  
        BC_LANC.CD_CNT = BC_CONTAS.CD_CNT  
    INNER JOIN GLB_DESP_REC ON
		PG_DESP_RAT.CD_EMP      = GLB_DESP_REC.CD_EMP AND  
        PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC  
    INNER JOIN RC_TP_LANC ON
		BC_LANC.CD_EMP     = RC_TP_LANC.CD_EMP AND  
        BC_LANC.CD_TP_LANC = RC_TP_LANC.CD_TP_LANC  
GO


