Fórum Stored Procedure #59012

CREATE PROCEDURE P_RETORNA_PROPOSTAS_APROVADAS ( COD_VENDEDOR CHAR(2), COD_EMPRESA CHAR(3), COD_FILIAL CHAR(4), DT_INICIAL DATE, DT_FINAL DATE) RETURNS ( PS_MES NUMERIC(2,2), PS_ANO NUMERIC(4,2), PS_VENDEDOR CHAR(2), PS_VLR_PRODUTOS NUMERIC(11,2), PS_VLR_SERVICO NUMERIC(11,2), PS_VLR_PROPOSTA NUMERIC(11,2), PS_VLR_TOTAL NUMERIC(11,2)) AS begin for select extract(month from b.DT_APROVA) as ´Mês´,extract(year from b.DT_APROVA) as ´Ano´,a.CD_VEND as ´Cod. Vendedor´,a.nm_vend as ´Nome do Vendedor´,(b.VL_PECAS + b.VL_ACESS + b.VL_OUTRO) as ´Valor Produtos´, b.VL_SERV as ´Valor Serviço´,(b.VL_PECAS + b.VL_ACESS + b.VL_OUTRO + b.VL_SERV) as ´Valor proposta´ from tab_vend a, tab_prpt b where a.CD_VEND = :COD_VENDEDOR and a.CD_VEND = b.CD_VEND and b.CD_EMPRESA = :COD_EMPRESA and b.CD_FILIAL = :COD_FILIAL and b.DT_APROVA = :DT_INICIAL and b.DT_APROVA = :DT_FINAL union select cast(0 as numeric(2,2)),cast(0 as numeric(4,2)),cast(´T´ as char(2)),cast(´´ as varchar(30)),(select sum(b.VL_PECAS + b.VL_ACESS + b.VL_OUTRO) from tab_prpt b),(select sum(b.VL_SERV) from tab_prpt b), (select sum(b.VL_PECAS + b.VL_ACESS + b.VL_OUTRO + b.VL_SERV) from tab_prpt b) from tab_vend a, tab_prpt b where a.CD_VEND = :COD_VENDEDOR and a.CD_VEND = b.CD_VEND and b.CD_EMPRESA = :COD_EMPRESA and b.CD_FILIAL = :COD_FILIAL and b.DT_APROVA = :DT_INICIAL and b.DT_APROVA = :DT_INICIAL into :PS_MES,:PS_ANO,:PS_VENDEDOR,:PS_VLR_PRODUTOS,:PS_VLR_SERVICO,:PS_VLR_PROPOSTA,:PS_VLR_TOTAL suspend; end