CREATE procedure [dbo].[SRV_ESTRAZIONE_ULTIMO_INVIO_REPORT] AS
SELECT
  BF.RETE,
  BF.COD_FISCALE,
  --Dbo.ToShortDateString( 
( SELECT CONVERT(VARCHAR(10),DATA_CONGELAMENTO - 1 , 103) FROM C6MartPeriodico.CONGELAMENTO_SEI) AS DataFase,
  --PatrimonioNonAssociato AS RisorseNonAllocate,
  ctv - contoCorrente as cvtRisorseFinanziarie,
  ContoCorrente,
  PartiteViaggiantiDisinv + isnull(pv.importo,0) AS Partita_viaggiante,
  CTV + PartiteViaggiantiDisinv + isnull(pv.importo,0) as totalePatrimonio,
  CR.MAX_VAR AS VarMassimoProfiloFinanziario,
  --V
  CASE WHEN RA.VAR_PERC_PTF IS NULL THEN 0.00
    ELSE ROUND(RA.VAR_PERC_PTF,2)
  END  AS VarComplessivo,
  isnull(RA.COPERTURA,0) AS Copertura,
-- TRINGALI MODIFICARE
        mifid.riskclass as RISKCLASSMAX,
  case when ra.creditrisk is null then 'n.a.'
        else cast(ra.creditrisk as varchar) end   as RiskClass
 FROM
        C6MartPeriodico.vPatrimonioBFAggregato AS BF
 INNER JOIN C6MartPeriodico.MIFID AS MIFID
  ON  BF.RETE = MIFID.RETE
  AND BF.COD_FISCALE = MIFID.COD_FISCALE
 INNER JOIN C6MartPeriodico.CODIFICA_RISCHIO AS CR
  ON MIFID.PROFILO_ASS = CR.PROFILO
 LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AS RA
  ON  RA.RETE = BF.RETE
  AND RA.COD_FISCALE = BF.COD_FISCALE
  AND RA.COD_AGGREG = 'COMPLESSIVO|BF'
left outer join  C6MartPeriodico.PARTITE_VIAGGIANTI pv
 on bf.rete=pv.rete
 and bf.cod_fiscale=pv.cod_fiscale
order by 1,2