-- =============================================
-- Author:		Marian Zaki
-- Create date: 29/11/2010
-- Description:	Checks for data validation 
-- =============================================
CREATE procedure [C6MartPeriodico].[SP_Patrimonio_BF_Percentuale_Piramide]
-- Add the parameters for the stored procedure here
AS
BEGIN
drop table if exists #liq
drop table if exists #ris
drop table if exists #pre
drop table if exists #inv
drop table if exists #ext
drop table if exists [C6MartPeriodico].[TB_Patrimonio_BF_Percentuale_Piramide]
	SELECT pmod.rete,pmod.COD_FISCALE,isnull(sum(pmod.Perc_Area),0.00) as PERC_LIQ
	into #liq
	FROM [C6StampeCentralizzate].[C6MartPeriodico].[PIRAMIDE_MOD] pmod
	inner join [C6StampeCentralizzate].[C6MartPeriodico].Area_Bisogno ar on pmod.id_area=ar.Id_area
	where pmod.rete+pmod.COD_FISCALE in (select distinct b.RETE+b.COD_FISCALE from C6MartPeriodico.PATRIMONIO_BF b)
	 and ar.Id_area in ('Liq')
	group by pmod.RETE,pmod.COD_FISCALE
	SELECT pmod.rete,pmod.COD_FISCALE,isnull(sum(pmod.Perc_Area),0.00) as PERC_RIS
	into #ris
	FROM [C6StampeCentralizzate].[C6MartPeriodico].[PIRAMIDE_MOD] pmod
	inner join [C6StampeCentralizzate].[C6MartPeriodico].Area_Bisogno ar on pmod.id_area=ar.Id_area
	where pmod.rete+pmod.COD_FISCALE in (select distinct RETE+COD_FISCALE from C6MartPeriodico.PATRIMONIO_BF)
	 and ar.Id_area in ('Ris')
	group by pmod.RETE,pmod.COD_FISCALE
		SELECT pmod.rete,pmod.COD_FISCALE,isnull(sum(pmod.Perc_Area),0.00) as PERC_PRE
	into #pre
	FROM [C6StampeCentralizzate].[C6MartPeriodico].[PIRAMIDE_MOD] pmod
	inner join [C6StampeCentralizzate].[C6MartPeriodico].Area_Bisogno ar on pmod.id_area=ar.Id_area
	where pmod.rete+pmod.COD_FISCALE in (select distinct RETE+COD_FISCALE from C6MartPeriodico.PATRIMONIO_BF)
	 and ar.Id_area in ('Pre')
	group by pmod.RETE,pmod.COD_FISCALE
		SELECT pmod.rete,pmod.COD_FISCALE,isnull(sum(pmod.Perc_Area),0.00) as PERC_INV
	into #inv
	FROM [C6StampeCentralizzate].[C6MartPeriodico].[PIRAMIDE_MOD] pmod
	inner join [C6StampeCentralizzate].[C6MartPeriodico].Area_Bisogno ar on pmod.id_area=ar.Id_area
	where pmod.rete+pmod.COD_FISCALE in (select distinct RETE+COD_FISCALE from C6MartPeriodico.PATRIMONIO_BF)
	 and ar.Id_area in ('Inv')
	group by pmod.RETE,pmod.COD_FISCALE
		SELECT pmod.rete,pmod.COD_FISCALE,isnull(sum(pmod.Perc_Area),0.000) as PERC_EXT
	into #ext
	FROM [C6StampeCentralizzate].[C6MartPeriodico].[PIRAMIDE_MOD] pmod
	inner join [C6StampeCentralizzate].[C6MartPeriodico].Area_Bisogno ar on pmod.id_area=ar.Id_area
	where pmod.rete+pmod.COD_FISCALE in (select distinct RETE+COD_FISCALE from C6MartPeriodico.PATRIMONIO_BF)
	 and ar.Id_area in ('Ext')
	group by pmod.RETE,pmod.COD_FISCALE
	select e.rete,e.cod_fiscale,e.PERC_EXT,i.PERC_INV,p.PERC_PRE,r.PERC_RIS,l.PERC_LIQ
	into [C6MartPeriodico].[TB_Patrimonio_BF_Percentuale_Piramide]
	from
	#ext e inner join #inv i on e.rete+e.COD_FISCALE=i.RETE+i.COD_FISCALE
	inner join #pre p on e.rete+e.COD_FISCALE=p.RETE+p.COD_FISCALE
	inner join #ris r on e.rete+e.COD_FISCALE=r.RETE+r.COD_FISCALE
	inner join #liq l on e.rete+e.COD_FISCALE=l.RETE+l.COD_FISCALE
	--select * from [C6MartPeriodico].[TB_Patrimonio_BF_Percentuale_Piramide]
	insert into [C6MartPeriodico].[TB_Patrimonio_BF_Percentuale_Piramide]
	select distinct rete,cod_fiscale,0.00 as PERC_EXT,0.00 as PERC_INV,0.00 as PERC_PRE,0.00 as PERC_RIS, 0.00 as PERC_LIQ from C6MartPeriodico.PATRIMONIO_BF
	except
	select rete,cod_fiscale,0.00 as PERC_EXT,0.00 as PERC_INV,0.00 as PERC_PRE,0.00 as PERC_RIS, 0.00 as PERC_LIQ from #liq
	--select distinct rete,cod_fiscale from c6martperiodico.PATRIMONIO_BF
END