create or replace FUNCTION CUSTO_SERVICO_CARDAPIO(
    P_CDFILIAL      IN   VARCHAR,
    P_CDSERVFILI    IN   VARCHAR,
    P_NRCONSPLAN    IN   VARCHAR,
    P_DATAINI       IN   DATE,
    P_DATAFIN       IN   DATE,
    P_CDTIPOCUSTO   IN   VARCHAR DEFAULT '04',
    P_TIPORETORNO   IN   VARCHAR DEFAULT 'PLANEJADO'
)RETURN FLOAT IS
    V_CUSTOREALIZADO   FLOAT;
    V_CUSTOPLANEJADO   FLOAT;
    
BEGIN

    SELECT NVL(SUM((CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, 4 ,CP.DTCONSPLAN, SYSDATE) * CP.QTCONSPLAN)),0) AS CUSTOPLAN
    INTO V_CUSTOPLANEJADO    
    FROM  
     (SELECT
          CDFILIAL,
          CDSERVFILI,
          DTCARDAPIO,
          SUM(QTCOMESTICAR) QTCOMESTICAR,
          SUM(QTCLIESTICAR) QTCLIESTICAR,
          SUM(QTCOMEFETCAR) QTCOMEFETCAR,
          SUM(QTCLIEFETCAR) QTCLIEFETCAR
        FROM
          CARDADIA
        WHERE
           CDFILIAL = P_CDFILIAL  AND
           (P_CDSERVFILI = CDSERVFILI) AND /*L_SERVICO*/
           (DTCARDAPIO BETWEEN P_DATAINI  AND P_DATAFIN )
        GROUP BY
          CDFILIAL,
          CDSERVFILI,
          DTCARDAPIO) CD,
          
          (SELECT * 
             FROM CONSPLAN
            WHERE CDFILICPLA = P_CDFILIAL  AND 
                  CDSERVCPLA = P_CDSERVFILI AND
                  DTCONSPLAN BETWEEN P_DATAINI  AND P_DATAFIN AND 
                  NRCONSPLAN = P_NRCONSPLAN) CP
    
    WHERE 
       CD.CDFILIAL   = CP.CDFILICPLA AND
       CD.CDSERVFILI = CP.CDSERVCPLA AND
       CD.DTCARDAPIO = CP.DTCONSPLAN;
      
   RETURN V_CUSTOPLANEJADO;

END;
/
-- VERSAO --
BEGIN ATUVEROBJ('CUSTO_SERVICO_CARDAPIO','6.35.025'); END;
/
