
/****** OBJECT:  VIEW [DBO].[V_BC_LANC_CNAB_LOTE]    SCRIPT DATE: 05/21/2013 15:19:04 ******/
IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_BC_LANC_CNAB_LOTE]'))
DROP VIEW [DBO].[V_BC_LANC_CNAB_LOTE]
GO

/****** OBJECT:  VIEW [DBO].[V_BC_LANC_CNAB_LOTE]    SCRIPT DATE: 05/21/2013 15:19:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW 
	[DBO].[V_BC_LANC_CNAB_LOTE] AS 
SELECT DISTINCT
	BANC.CD_EMP,
	PG_TIT.CD_FILIAL,
	PG_TIT.CD_PG_CRED,
	BANC.CD_BC_LANC AS NR_LANC,
	BANC.DT_LANCTO,
	BANC.VLR_LANC,
	BANC.HIST_LANC,
	FORN.RZ_FORN,
	FORN.CD_FORN,
	FORN.CGC_CPF,
	ISNULL(FORN_CNT.CD_BC,0) AS CD_BC,
	ISNULL(FORN_CNT.NR_CNT,0) AS NR_CNT,
	ISNULL(FORN_CNT.AG_CNT,0) AS AG_CNT,
	ISNULL(FORN_CNT.DIG_AG_FORN,0) AS DIG_AG_FORN,
	PG_TIT.DT_VENCTO,
	FORN_CNT.DIG_CNT,
	CNT.NR_CONV,
	FORN_CNT.FLAG_CNT_PD_DOC_TED,
	PG_TIT.CD_CTR,
	CNT.CD_CNT

FROM
	BC_LANC BANC
	INNER JOIN BC_LANC_CNAB_PEND PEND ON
		BANC.CD_EMP = PEND.CD_EMP
		AND BANC.CD_BC_LANC = PEND.CD_BC_LANC

	INNER JOIN BC_CONTAS CNT ON
		BANC.CD_EMP = CNT.CD_EMP
		AND BANC.CD_CNT = CNT.CD_CNT

	INNER JOIN PG_DEB_LOTE_BC_LANC VINC ON
		BANC.CD_EMP = VINC.CD_EMP
		AND BANC.CD_BC_LANC = VINC.CD_BC_LANC

	INNER JOIN PG_DEB_LOTE DEB_LOTE ON
		VINC.CD_EMP = DEB_LOTE.CD_EMP
		AND VINC.CD_PG_DEB_LOTE = DEB_LOTE.CD_PG_DEB_LOTE

	INNER JOIN PG_DEB_LOTE_PG_DEB VINC_LOTE ON
		DEB_LOTE.CD_EMP = VINC_LOTE.CD_EMP
		AND DEB_LOTE.CD_PG_DEB_LOTE = VINC_LOTE.CD_PG_DEB_LOTE

	INNER JOIN PG_DEB PAG ON
		VINC_LOTE.CD_EMP = PAG.CD_EMP
		AND VINC_LOTE.CD_FILIAL = PAG.CD_FILIAL
		AND VINC_LOTE.CD_PG_CRED = PAG.CD_PG_CRED

	INNER JOIN PG_CRED PG_TIT ON
		PAG.CD_EMP = PG_TIT.CD_EMP
		AND PAG.CD_FILIAL = PG_TIT.CD_FILIAL
		AND PAG.CD_PG_CRED = PG_TIT.CD_PG_CRED

	INNER JOIN PG_FORN FORN ON
		PG_TIT.CD_EMP = FORN.CD_EMP
		AND PG_TIT.CD_FORN = FORN.CD_FORN

	LEFT JOIN PG_FORN_CNT FORN_CNT ON
		FORN.CD_EMP = FORN_CNT.CD_EMP
		AND FORN.CD_FORN = FORN_CNT.CD_FORN


UNION

SELECT 
	BANC.CD_EMP,
	PG_TIT.CD_FILIAL,
	PG_TIT.CD_PG_CRED,
	BANC.CD_BC_LANC AS NR_LANC,
	BANC.DT_LANCTO,
	BANC.VLR_LANC,
	BANC.HIST_LANC,
	FORN.RZ_FORN,
	FORN.CD_FORN,
	FORN.CGC_CPF,
	ISNULL(FORN_CNT.CD_BC,0) AS CD_BC,
	ISNULL(FORN_CNT.NR_CNT,0) AS NR_CNT,
	ISNULL(FORN_CNT.AG_CNT,0) AS AG_CNT,
	ISNULL(FORN_CNT.DIG_AG_FORN,0) AS DIG_AG_FORN,
	PG_TIT.DT_VENCTO,
	FORN_CNT.DIG_CNT,
	CNT.NR_CONV,
	FORN_CNT.FLAG_CNT_PD_DOC_TED,
	PG_TIT.CD_CTR,
	CNT.CD_CNT
FROM
	BC_LANC	BANC
	INNER JOIN BC_LANC_CNAB_PEND PEND ON
		BANC.CD_EMP = PEND.CD_EMP
		AND BANC.CD_BC_LANC = PEND.CD_BC_LANC
	INNER JOIN BC_CONTAS CNT ON
		BANC.CD_EMP = CNT.CD_EMP
		AND BANC.CD_CNT = CNT.CD_CNT
	INNER JOIN PG_DEB_BC_LANC VINC ON
		BANC.CD_EMP = VINC.CD_EMP
		AND BANC.CD_BC_LANC = VINC.CD_BC_LANC
	INNER JOIN PG_DEB PAG ON
		VINC.CD_EMP = PAG.CD_EMP
		AND VINC.CD_FILIAL = PAG.CD_FILIAL
		AND VINC.CD_PG_CRED = PAG.CD_PG_CRED
	INNER JOIN PG_CRED PG_TIT ON
		PAG.CD_EMP = PG_TIT.CD_EMP
		AND PAG.CD_FILIAL = PG_TIT.CD_FILIAL
		AND PAG.CD_PG_CRED = PG_TIT.CD_PG_CRED
	INNER JOIN PG_FORN FORN ON
		PG_TIT.CD_EMP = FORN.CD_EMP
		AND PG_TIT.CD_FORN = FORN.CD_FORN
	LEFT JOIN  PG_FORN_CNT FORN_CNT ON
		FORN.CD_EMP = FORN_CNT.CD_EMP
		AND FORN.CD_FORN = FORN_CNT.CD_FORN


