/****** Object:  View [dbo].[V_PG_FORN_PG_CRED_SALDO_ABERTO]    Script Date: 12/10/2013 15:41:31 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_FORN_PG_CRED_SALDO_ABERTO]'))
DROP VIEW [dbo].[V_PG_FORN_PG_CRED_SALDO_ABERTO]
GO
GO
GO
--VERSAO 2426
--SELECT * FROM V_PG_FORN_PG_CRED_SALDO_ABERTO WHERE CD_EMP = 1 AND DT_MOV='2017-01-09'
CREATE VIEW [dbo].[V_PG_FORN_PG_CRED_SALDO_ABERTO] AS
SELECT
	Rs.CD_EMP,
	Rs.CD_BANDEIRA,
	PRC_BANDEIRA.NM_BANDEIRA,
	Rs.DT_MOV,
	Rs.CD_FORN,
	PG_FORN.NM_FANT,
	ISNULL(VLR_ORCAMENTO_COMPRAS,0) AS VLR_ORCAMENTO_COMPRAS,
	ISNULL(ap.VLR_ORCAMENTO_APORTE,0) as VLR_ORCAMENTO_APORTE,
	SUM(Rs.VLR_FINANCEIRO_PENDENTE) AS VLR_FINANCEIRO_PENDENTE,
	SUM(Rs.VLR_PEDIDO_PENDENTE) AS VLR_PEDIDO_PENDENTE
FROM
(
SELECT
	DT_MOV,
	CD_EMP,
	CD_FORN,
	VLR_EM_ABERTO AS VLR_FINANCEIRO_PENDENTE,
	0 AS VLR_PEDIDO_PENDENTE,
	CD_BANDEIRA
FROM
	PG_FORN_PG_CRED_SALDO_ABERTO
WHERE
	TP_TRANSACAO =0
UNION ALL
SELECT
	DT_MOV,
	CD_EMP,
	CD_FORN,
	0 AS VLR_FINANCEIRO_PENDENTE,
	VLR_EM_ABERTO AS VLR_PEDIDO_PENDENTE,
	CD_BANDEIRA
FROM
	PG_FORN_PG_CRED_SALDO_ABERTO
WHERE
	TP_TRANSACAO =1
	) Rs INNER JOIN PG_FORN ON
Rs.CD_EMP = PG_FORN.CD_EMP AND
Rs.CD_FORN = PG_FORN.CD_FORN
LEFT OUTER JOIN PG_FORN_ORCAMENTO_COMPRAS s ON
Rs.CD_EMP = s.CD_EMP AND
Rs.CD_FORN = s.CD_FORN
LEFT OUTER JOIN (SELECT
	CD_EMP,
	CD_FORN,
	CD_BANDEIRA,
	DT_MOV,
	SUM(VLR_ORCAMENTO_APORTE) AS VLR_ORCAMENTO_APORTE
FROM
	PG_FORN_ORCAMENTO_COMPRAS_APORTE
GROUP BY
	CD_EMP,
	CD_FORN,
	CD_BANDEIRA,
	DT_MOV) ap  ON
	Rs.CD_EMP = ap.CD_EMP AND
	Rs.CD_FORN = ap.CD_FORN AND
	Rs.CD_BANDEIRA = ap.CD_BANDEIRA AND
	Rs.DT_MOV = ap.DT_MOV
	INNER JOIN PRC_BANDEIRA ON
	Rs.CD_BANDEIRA = PRC_BANDEIRA.CD_BANDEIRA
GROUP BY
	Rs.CD_EMP,
	Rs.CD_BANDEIRA,
	Rs.DT_MOV,
	Rs.CD_FORN,
	PG_FORN.NM_FANT,
	s.VLR_ORCAMENTO_COMPRAS,
	ap.VLR_ORCAMENTO_APORTE,
	PRC_BANDEIRA.NM_BANDEIRA

