Atacarejo

SET TERM ^ ;

CREATE OR ALTER PROCEDURE ATACADO_RETFROMDATA (
    PID INTEGER)
RETURNS (
    ID INTEGER,
    "PERC. DE DESCONTO %" NUMERIC(18,2),
    "QTD. MÍNIMA" NUMERIC(18,2),
    CONTROLE VARCHAR(37),
    "SITUAÇÃO DO PRAZO" CHAR(15),
    "VALOR ATACADO R$" NUMERIC(18,4),
    "VALOR DE VENDA ATUAL R$" NUMERIC(18,2),
    "MKP% ATUAL" NUMERIC(18,2),
    "MKP% ATACADO" NUMERIC(18,6),
    ATC007DFIM DATE,
    MSG VARCHAR(176))
AS
BEGIN
  FOR
    SELECT
       FIRST 1 C.ID,
        C.ATC005PDESC AS "PERC. DE DESCONTO %",
        C.ATC008QTDMIN AS "QTD. MÍNIMA",
       CASE  C.ATC009CTRDATA
       WHEN 0 THEN 'NAO CONTROLADO POR DATA'
       WHEN 1 THEN 'DATA INICIO '||C.ATC006DINI||' ATE '||C.ATC007DFIM
       END "CONTROLE",
       IIF( ( ATC009CTRDATA = 0 ), 'DENTRO DO PRAZO',
          IIF(  C.ATC007DFIM < CURRENT_DATE , 'PRAZO EXPIRADO','DENTRO DO PRAZO' ) )  AS "SITUAÇÃO DO PRAZO",
       SUM( P.SAID_VLVENDA - ( ( P.SAID_VLVENDA * C.ATC005PDESC ) / 100  ) ) AS "VALOR ATACADO R$",
       MAX(P.SAID_VLVENDA) AS "VALOR DE VENDA ATUAL R$",
       MAX( P.P27MKP ) AS "MKP% ATUAL",
       SUM(  (( P.SAID_VLVENDA - ( ( P.SAID_VLVENDA * C.ATC005PDESC ) / 100  ) )
       - P.P26VLCUSTO ) / P.P26VLCUSTO * 100  ) AS "MKP% ATACADO",
       C.ATC007DFIM,
     ( 'NAS COMPRAS A PARTIR DE  '||ATC008QTDMIN||' '||MAX(P.SAID_UNVENDA) ||' VOCE GANHA UM DESCONTO DE % '||CAST(ATC005PDESC AS NUMERIC(15,2) )
         ||'  - O VALOR PROMOCIONAL COM DESCONTO DESSE PRODUTO É '||CAST(SUM( P.SAID_VLVENDA - ( ( P.SAID_VLVENDA * C.ATC005PDESC ) / 100  ) )  AS NUMERIC(15,2))
         ||' PROMOCAO VÁLIDA ATÉ '|| EXTRACT(DAY FROM C.ATC007DFIM) ||'/'|| EXTRACT(MONTH FROM C.ATC007DFIM)||' DE '|| EXTRACT(YEAR FROM C.ATC007DFIM)  )AS MSG


    FROM CPRODATC001 C
     LEFT JOIN CADPRODUTOS P ON ( P.CAD_IDRELPROD = C.ATC004IDPROD )
     WHERE C.ATC004IDPROD = :PID
        AND ( C.ATC009CTRDATA = 1 )
        AND ( C.ATC006DINI < CURRENT_DATE AND C.ATC007DFIM > CURRENT_DATE )
     GROUP BY 1,2,3,4,5,10
       ORDER BY ATC007DFIM ASC
    INTO :ID,
         :"PERC. DE DESCONTO %",
         :"QTD. MÍNIMA",
         :CONTROLE,
         :"SITUAÇÃO DO PRAZO",
         :"VALOR ATACADO R$",
         :"VALOR DE VENDA ATUAL R$",
         :"MKP% ATUAL",
         :"MKP% ATACADO",
         :ATC007DFIM,
         :MSG
  DO
  BEGIN
    SUSPEND;
  END
END^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON CPRODATC001 TO PROCEDURE ATACADO_RETFROMDATA;
GRANT SELECT ON CADPRODUTOS TO PROCEDURE ATACADO_RETFROMDATA;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE ATACADO_RETFROMDATA TO SYSDBA;