Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET TERM ^ ;
- CREATE OR ALTER PROCEDURE RETORNASOMAFINALIZADORAS (
- PID INTEGER,
- PSITVENDA VARCHAR(35) CHARACTER SET WIN1252 COLLATE WIN_PTBR)
- RETURNS (
- DINHEIRO NUMERIC(18,2),
- CHEQUE NUMERIC(18,2),
- CARTAOCREDITO NUMERIC(15,2),
- CARTAODEBITO NUMERIC(15,2),
- CREDITOLOJA NUMERIC(15,2),
- VALEALIMENTACAO NUMERIC(15,2),
- VALEREFEICAO NUMERIC(15,2),
- VALEPRESENTE NUMERIC(15,2),
- VALECOMBUSTIVEL NUMERIC(15,2),
- OUTROS NUMERIC(15,2))
- AS
- BEGIN
- FOR
- SELECT
- MAX(DINHEIRO) DINHEIRO,
- MAX(CHEQUE) CHEQUE,
- MAX(CARTAOCREDITO) CARTAOCREDITO,
- MAX(CARTAODEBITO) CARTAODEBITO,
- MAX(CREDITOLOJA) CREDITOLOJA,
- MAX(VALEALIMENTACAO) VALEALIMENTACAO,
- MAX(VALEREFEICAO) VALEREFEICAO,
- MAX(VALEPRESENTE) VALEPRESENTE,
- MAX(VALECOMBUSTIVEL) VALECOMBUSTIVEL,
- MAX(OUTROS) OUTROS
- FROM
- (
- SELECT
- CASE WHEN ( PG.indice_sat = 0) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END DINHEIRO,
- CASE WHEN ( PG.indice_sat = 1) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END CHEQUE,
- CASE WHEN ( PG.indice_sat = 2) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END CARTAOCREDITO,
- CASE WHEN ( PG.indice_sat = 3)AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END CARTAODEBITO,
- CASE WHEN ( PG.indice_sat = 4)AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END CREDITOLOJA,
- CASE WHEN ( PG.indice_sat = 5) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END VALEALIMENTACAO,
- CASE WHEN ( PG.indice_sat = 6) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END VALEREFEICAO,
- CASE WHEN ( PG.indice_sat = 7) AND ( PG.situacao = :psitvenda )AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END VALEPRESENTE,
- CASE WHEN ( PG.indice_sat = 8 ) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM( PG.valor_recebido )
- ELSE
- 0
- END VALECOMBUSTIVEL,
- CASE WHEN ( PG.indice_sat = 9) AND ( PG.situacao = :psitvenda ) AND ( PG.idcaixa = :Pid )
- THEN
- SUM(PG.valor_recebido )
- ELSE
- 0
- END OUTROS
- FROM venda_pagfinalreply PG
- WHERE
- (
- ( PG.idcaixa = :Pid )
- )
- AND
- (
- ( PG.situacao = :psitvenda )
- )
- GROUP BY PG.indice_sat,PG.situacao,PG.idcaixa )
- INTO :DINHEIRO,
- :CHEQUE,
- :CARTAOCREDITO,
- :CARTAODEBITO,
- :CREDITOLOJA,
- :VALEALIMENTACAO,
- :VALEREFEICAO,
- :VALEPRESENTE,
- :VALECOMBUSTIVEL,
- :OUTROS
- DO
- BEGIN
- SUSPEND;
- END
- END^
- SET TERM ; ^
- /* Following GRANT statetements are generated automatically */
- GRANT SELECT ON VENDA_PAGFINALREPLY TO PROCEDURE RETORNASOMAFINALIZADORAS;
- /* Existing privileges on this procedure */
- GRANT EXECUTE ON PROCEDURE RETORNASOMAFINALIZADORAS TO SYSDBA;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement