-- =============================================
-- Author:		alessandro Tringali
-- Create date: 16 ottobre 2010
-- Description:	estrae la lista dei clienti per fase Ii
-- =============================================
-- exec [C6MartPeriodico].[SRV_Verifica_DISALLINEAMENTI_MONIT] 'F','BLFLSU65M55A470K'
CREATE procedure [C6MartPeriodico].[SRV_Verifica_DISALLINEAMENTI_MONIT]
	@rete char(1), 
	@CodiceFiscale varchar(16)
AS
BEGIN
--DROP TABLE #TEMP_PATRIMONIO_BF_AGGR
SELECT 
RETE,
COD_FISCALE,
SUBSTRING(CASE WHEN ID_AREA='EXT' THEN 'Exr' ELSE ID_AREA END,0,4) AS ID_AREA,
isnull(ORDINAMENTO_PROGETTO,0) as ordinamento_progetto,
SUM(CTV) AS SUM_CTV
INTO #TEMP_PATRIMONIO_BF_AGGR
FROM
C6MARTPERIODICO.PATRIMONIO_BF
WHERE ID_AREA NOT IN ('CC','NA')
AND REND_NON_RAPPR=0
and rete=@Rete and  COD_FISCALE =@CodiceFiscale
GROUP BY RETE,
COD_FISCALE,
SUBSTRING(CASE WHEN ID_AREA='EXT' THEN 'Exr' ELSE ID_AREA END,0,4),
isnull(ORDINAMENTO_PROGETTO,0)
SELECT * INTO #TANAB FROM C6STAGINGPERIODICO.TANAB
WHERE CODFIS IN 
(SELECT DISTINCT COD_FISCALE FROM #TEMP_PATRIMONIO_BF_AGGR)
SELECT *
FROM
(
SELECT 
P.RETE,
P.COD_FISCALE,
P.ID_AREA,
m.codarea as mon_area,
P.ORDINAMENTO_PROGETTO as bf_ordprorg,
M.CTV CTV_MONIT,
P.SUM_CTV,
(isnull(sum_ctv,0) - isnull(m.ctv,0)) as delta,
m.codprog as mon_codprog,
'Disallineati Inv' as descContr
FROM 
#TANAB M,
#TEMP_PATRIMONIO_BF_AGGR P
WHERE
M.RETE = P.RETE 
AND case when len(m.CODFIS) =4 then 'FF@'+codfis else codfis end = P.COD_FISCALE
AND M.CODAREA = P.ID_AREA
AND DTFINE ='99991231'
AND M.CTV <> P.SUM_CTV
and m.ctv > 0
AND M.CODPROG = isnull(P.ORDINAMENTO_PROGETTO,0)
and id_area ='Inv'
union all
SELECT 
P.RETE,
P.COD_FISCALE,
P.ID_AREA,
m.codarea as mon_area,
P.ORDINAMENTO_PROGETTO,
M.CTV CTV_MONIT,
P.SUM_CTV,
(isnull(sum_ctv,0) - isnull(m.ctv,0)) as delta,
m.codprog,
'Disallineati non Inv' as descContr
FROM 
#TANAB M,
#TEMP_PATRIMONIO_BF_AGGR P
WHERE
M.RETE = P.RETE 
AND case when len(m.CODFIS) =4 then 'FF@'+codfis else codfis end = P.COD_FISCALE
AND M.CODAREA = P.ID_AREA
AND DTFINE ='99991231'
AND M.CTV <> P.SUM_CTV
and m.ctv > 0
and id_area <>'Inv'
union all
--------------------------------------------
-- SOLO MONITORAGGIO
--------------------------------------------
SELECT 
m.RETE,
case when len(m.CODFIS) =4 then 'FF@'+codfis else codfis end as cod_fiscale,
P.ID_AREA,
m.codarea as mon_area,
P.ORDINAMENTO_PROGETTO,
M.CTV CTV_MONIT,
P.SUM_CTV,
(isnull(sum_ctv,0) - isnull(m.ctv,0)) as delta,
m.codprog,
'Disalinneati Solo Monit Inv' as descContr
FROM 
#TANAB M
left outer join 
#TEMP_PATRIMONIO_BF_AGGR P
on
M.RETE = P.RETE 
AND case when len(m.CODFIS) =4 then 'FF@'+codfis else codfis end  = P.COD_FISCALE
AND M.CODAREA = P.ID_AREA
AND DTFINE ='99991231'
where
--AND M.CTV <> P.SUM_CTV
--and m.dtrif= c6martperiodico.gettrimestre2(getdate(),0)
m.ctv > 0
and p.cod_fiscale is null
AND M.CODPROG = isnull(P.ORDINAMENTO_PROGETTO,0)
and id_area ='Inv'
union all
SELECT 
m.RETE,
case when len(m.CODFIS) =4 then 'FF@'+codfis else codfis end as cod_fiscale,
p.ID_AREA,
m.codarea as mon_area,
P.ORDINAMENTO_PROGETTO,
M.CTV CTV_MONIT,
P.SUM_CTV,
(isnull(sum_ctv,0) - isnull(m.ctv,0)) as delta,
m.codprog,
'Disallineati Solo Monit non Inv' as descContr
FROM 
#TANAB M 
left outer join
#TEMP_PATRIMONIO_BF_AGGR P
on
M.RETE = P.RETE 
AND M.CODFIS = P.COD_FISCALE
AND M.CODAREA = P.ID_AREA
where
DTFINE ='99991231'
-- M.CTV <> P.SUM_CTV
and  m.ctv > 0
and p.cod_fiscale is null
and codarea <>'Inv'
) k
order by 2,1
end