/****** Object:  StoredProcedure [dbo].[P_PDV_VD_DEV_NCC_ANALISE_STATUS]    Script Date: 02/11/2014 18:34:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PDV_VD_DEV_NCC_ANALISE_STATUS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PDV_VD_DEV_NCC_ANALISE_STATUS]
GO
GO 
--EXEC P_PDV_VD_DEV_NCC_ANALISE_STATUS '2001-01-01','2017-10-10',1
CREATE PROCEDURE P_PDV_VD_DEV_NCC_ANALISE_STATUS @DT_INI DATETIME, 
												 @DT_FIM DATETIME,
												 @FLAG_CONFERIDO INT=0
AS
CREATE TABLE #RsNCC (CD_CTR_NCC INT, 
					 STS_NCC INT, 
					 DS_STS_NCC VARCHAR(200), 
					 DT_CAD DATETIME,
					 CONFERIDO VARCHAR(10))

INSERT INTO #RsNCC
SELECT
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.STS_NCC, 
	V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC,
	PDV_VD_DEV_NCC.DT_CAD,
	'SIM'
FROM          
	PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_PDV_VD ON 
    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC 
	INNER JOIN PDV_VD AS PDV_VD_NOVO ON
    PDV_VD_NOVO.CD_VD = PDV_VD_DEV_NCC_PDV_VD.CD_VD AND 
    PDV_VD_NOVO.CD_FILIAL = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL AND 
    PDV_VD_NOVO.CD_EMP = PDV_VD_DEV_NCC_PDV_VD.CD_EMP  
	INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON
	PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
WHERE     
	PDV_VD_DEV_NCC.STS_NCC IN (1,7)

INSERT INTO #RsNCC
SELECT
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.STS_NCC, 
	V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC,
	PDV_VD_DEV_NCC.DT_CAD,
	'SIM'
FROM          
	PDV_VD_DEV_NCC INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON
	PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
WHERE     
	PDV_VD_DEV_NCC.STS_NCC IN (0,2,3)

INSERT INTO #RsNCC
SELECT
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.STS_NCC, 
	V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC,
	PDV_VD_DEV_NCC.DT_CAD,
	'SIM'
FROM          
	PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_LANC_CPL AS NCC_CPL ON 
	PDV_VD_DEV_NCC.CD_CTR_NCC = NCC_CPL.CD_CTR_NCC 
	INNER JOIN PDV_VD_DEV_NCC_LANC AS LANC ON 
	NCC_CPL.CD_CTR_LANC_NCC = LANC.CD_CTR_LANC_NCC 
	INNER JOIN PDV_VD_DEV_NCC_LANC_RC_CREDIARIO_PARC_CRED AS RC_CRED ON 
	LANC.CD_CTR_LANC_NCC = RC_CRED.CD_CTR_LANC_NCC 
	INNER JOIN RC_CREDIARIO_PARC_CRED ON 
	RC_CRED.CD_CTR_CRED = RC_CREDIARIO_PARC_CRED.CD_CTR_CRED AND 
    RC_CRED.CD_CTR = RC_CREDIARIO_PARC_CRED.CD_CTR 
	INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON
	PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
WHERE
	PDV_VD_DEV_NCC.STS_NCC = 10 

INSERT INTO #RsNCC
SELECT
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.STS_NCC, 
	V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC,
	PDV_VD_DEV_NCC.DT_CAD,
	'SIM'
FROM          
	PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_LANC_CPL LANC_CPL ON 
	PDV_VD_DEV_NCC.CD_CTR_NCC = LANC_CPL.CD_CTR_NCC 
	INNER JOIN PDV_VD_DEV_NCC_LANC LANC ON 
	LANC.CD_CTR_LANC_NCC = LANC_CPL.CD_CTR_LANC_NCC 
	INNER JOIN PDV_VD_DEV_NCC_LANC_RC_CRED NCC_CRED ON 
	LANC.CD_CTR_LANC_NCC = NCC_CRED.CD_CTR_LANC_NCC 
	INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON
	PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
WHERE
	PDV_VD_DEV_NCC.STS_NCC = 4

INSERT INTO #RsNCC
SELECT
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.STS_NCC, 
	V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC,
	PDV_VD_DEV_NCC.DT_CAD,
	'SIM'
FROM                  
	PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_LANC_CPL AS NCC_CPL ON 
	PDV_VD_DEV_NCC.CD_CTR_NCC = NCC_CPL.CD_CTR_NCC 
	INNER JOIN PDV_VD_DEV_NCC_LANC AS LANC ON 
	NCC_CPL.CD_CTR_LANC_NCC = LANC.CD_CTR_LANC_NCC 
	INNER JOIN PDV_VD_DEV_NCC_LANC_RC_VD_CONV_CRED AS RC_CRED ON 
	LANC.CD_CTR_LANC_NCC=RC_CRED.CD_CTR_LANC_NCC 
	INNER JOIN RC_VD_CONV_CRED ON
	RC_CRED.CD_CTR_CRED=RC_VD_CONV_CRED.CD_CTR_CRED AND
	RC_CRED.CD_CTR=RC_VD_CONV_CRED.CD_CTR 
	INNER JOIN RC_VD_CONV ON 
	RC_CRED.CD_CTR=RC_VD_CONV.CD_CTR 
	INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON
	PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
WHERE
	PDV_VD_DEV_NCC.STS_NCC = 8

INSERT INTO #RsNCC
SELECT
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.STS_NCC, 
	V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC,
	PDV_VD_DEV_NCC.DT_CAD,
	'NAO' 
FROM
	PDV_VD_DEV_NCC LEFT OUTER JOIN #RsNCC ncc ON
	PDV_VD_DEV_NCC.CD_CTR_NCC = ncc.CD_CTR_NCC
	INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON
	PDV_VD_DEV_NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC 
WHERE
	ncc.CD_CTR_NCC IS NULL

SELECT
	*
FROM
	#RsNCC
WHERE
	CONFERIDO = CASE WHEN @FLAG_CONFERIDO=0 THEN 
					CONFERIDO
				WHEN @FLAG_CONFERIDO=1 THEN
					'SIM'
				WHEN @FLAG_CONFERIDO=2 THEN
					'NAO'
				END	
ORDER BY
	DT_CAD
DROP TABLE #RsNCC