USE AM2012
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'tmpRestosAPagar')
  DROP VIEW tmpRestosAPagar
GO
CREATE VIEW tmpRestosAPagar
AS
  SELECT cdOrgao = substring(cdFuncionalProgramatica,1,2),
         cdUnidade = substring(cdFuncionalProgramatica,3,3),
         cdFuncao = substring(cdFuncionalProgramatica,6,2),
         cdSubFuncao = substring(cdFuncionalProgramatica,8,3),
         cdPrograma = substring(cdFuncionalProgramatica,11,4),
         cdProjAtiv = substring(cdFuncionalProgramatica,15,4),
         cdFonte, flRecursoVinculado,
         vlProcessado, vlNaoProcessado,
         vlRestosAPagar,
         vlConfere = vlRestosAPagar - (vlProcessado + vlNaoProcessado)
    FROM ammRestosAPagar
GO


SELECT

CONVERT (decimal(10,2),
(SELECT '000' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE ((cdFonte between 000 and 039) or
          (cdFonte between 041 and 067) or
          (cdFonte between 070 and 099)) and
         cdProjativ not between '5000' and '6999' and
         cdFuncao not in (10,12) )) as '000',
CONVERT (decimal(10,2),
( SELECT '201' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFonte = 000 and
         cdProjAtiv between '5000' and '6999' )) as '201',
CONVERT (decimal(10,2),
( SELECT '202' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFonte in (503,513,879,880) and
         cdProjAtiv between '5000' and '6999' )) as '202',
CONVERT (decimal(10,2),
( SELECT '301' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 12 and
         cdFonte = 000 )) as '301',
CONVERT (decimal(10,2),
( SELECT '302' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 12 and
         cdFonte in (103, 104) )) as '302',
CONVERT (decimal(10,2),
( SELECT '303' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 12 and
         cdFonte in (101,102) )) as '303',
CONVERT (decimal(10,2),
( SELECT '304' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 12 and
         ((cdFonte between 105 and 299) or
          (cdFonte between 671 and 685)) )) as '304',
CONVERT (decimal(10,2),
( SELECT '401' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 10 and
         cdFonte = 000 )) as '401',
CONVERT (decimal(10,2),
( SELECT '402' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 10 and
         cdFonte = 303 )) as '402',
CONVERT (decimal(10,2),
( SELECT '403' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao = 10 and
         ((cdFonte between 304 and 499) or
          (cdFonte between 686 and 699)) )) as '403',
CONVERT (decimal(10,2),
( SELECT '500' = isnull(sum(vlRestosAPagar), 0.00)
    FROM tmpRestosAPagar
   WHERE cdFuncao not in (10,12) and
         (cdProjAtiv not between 5000 and 6999) and
         ( cdFonte in (040, 068, 069, 100, 501, 502) or
          (cdFonte between 504 and 512) or
          (cdFonte between 514 and 670) or
          (cdFonte between 700 and 878) or
          (cdFonte between 881 and 998)) )) as '500'


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'tmpRestosAPagar')
  DROP VIEW tmpRestosAPagar