create or replace
PROCEDURE PLA_ANALISE_CUSTO_CARDAPIO (
   P_CDFILIAL IN VARCHAR2,
   P_CDSERVFILI IN VARCHAR2,
   P_DTINICIAL IN DATE,
   P_DTFINAL IN DATE,
   P_IDARQUIVO IN VARCHAR2,
   P_IDPRODUTOS IN VARCHAR2,
   P_IDSEPPRATO IN VARCHAR2,
   P_PEACRESCIMO IN NUMBER,
   P_IDARREDOND IN VARCHAR2,
   P_NRCONSPLAN IN VARCHAR2,
   P_CDPRODUTOINI IN VARCHAR2,
   P_CDPRODUTOFIN IN VARCHAR2,
   P_CDOPERADOR IN VARCHAR2,
   P_CDFILICALCCUSTO IN VARCHAR2,
   P_NRTIPOCUSTOPLA IN NUMBER,
   P_NRTIPOCUSTOREAL IN NUMBER,
   P_DTREFECUSTO IN DATE,
   P_NRORG IN NUMBER,
   P_IDEXCLUI_CONSPLAN IN VARCHAR2 DEFAULT 'S'
)
IS
   V_VRCUSTOACU FLOAT;
   V_QTCOMESACU FLOAT;
   V_VRCUSTMATACU FLOAT;
   V_VRCUSTOCONSACU FLOAT;
   V_VRCUSTORATACU FLOAT;
   V_VRCUSTPADR FLOAT;
   V_QTCOMACUACOMP FLOAT;
   V_VRCUSACUACOMP FLOAT;
   V_IDCOMCUSPERC VARCHAR2(01);
   V_IDPRECODESATU VARCHAR2(01);
   V_IDUTILDTPROC VARCHAR2(01);
   V_IDCUSTREFANA VARCHAR2(01);
   V_DTPROCESSA DATE;
BEGIN
   BEGIN
      SELECT IDUTILDTPROC
        INTO V_IDUTILDTPROC
        FROM PARAMGERAL
       WHERE NRORG = P_NRORG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDUTILDTPROC := 'N';
   END;
   BEGIN
      SELECT IDCOMCUSPERC, DECODE(V_IDUTILDTPROC, 'S', DTPROCESSA, SYSDATE)
        INTO V_IDCOMCUSPERC, V_DTPROCESSA
        FROM PARAMFILIAL
       WHERE CDFILIAL = P_CDFILIAL
         AND NRORG = P_NRORG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDCOMCUSPERC := 'C';
         V_DTPROCESSA := SYSDATE;
   END;
   DELETE
     FROM CONSPLAN
    WHERE NRCONSPLAN = P_NRCONSPLAN
      AND CDFILICPLA = P_CDFILIAL
      AND NRORG = P_NRORG;
   CONSUMO_PLANEJADO_PLA (
      P_CDFILIAL,
      P_CDSERVFILI,
      P_DTINICIAL,
      P_DTFINAL,
      P_IDARQUIVO,
      P_IDPRODUTOS,
      P_IDSEPPRATO,
      P_PEACRESCIMO,
      P_IDARREDOND,
      P_NRCONSPLAN,
      P_CDPRODUTOINI,
      P_CDPRODUTOFIN,
      P_CDOPERADOR,
      '_'||TO_CHAR(P_DTINICIAL, 'YYYYMMDD')||'_',
      '_'||TO_CHAR(P_DTFINAL, 'YYYYMMDD')||'_'
   );
   V_VRCUSTOACU := 0;
   V_QTCOMESACU := 0;
   V_VRCUSTMATACU := 0;
   V_VRCUSTOCONSACU := 0;
   V_VRCUSTORATACU := 0;
   V_QTCOMACUACOMP := 0;
   V_VRCUSACUACOMP := 0;
   DELETE
     FROM PLA_CUSTOCARDAPIO
    WHERE NRCONSPLAN = P_NRCONSPLAN
       AND CDFILCUSPLA = P_CDFILIAL
      AND NRORG = P_NRORG;
   FOR CR IN (
      SELECT DATA.DTDATA,
             CARD.DTCARDAPIO,
             CPAD.VRCUSTPADR,
             DECODE(V_IDCOMCUSPERC, 'C', COME.QTCLIEFETCAR, COME.QTCOMEFETCAR) AS QTCOMEFETCAR,
             SUM(CASE WHEN ILAN.IDEVOLESTOQ = 'S' THEN IRET.QTRETIRADA * PCMP.VRFATOCONV ELSE 0 END) AS QTRETIRADA,
             SUM(NVL(CUSTO_PRODUTO(P_CDFILICALCCUSTO, PEST.CDPRODUTO, P_NRTIPOCUSTOREAL, NVL(P_DTREFECUSTO, CARD.DTCARDAPIO), SYSDATE), 0) * (CASE WHEN ILAN.IDEVOLESTOQ = 'S' THEN IRET.QTRETIRADA * PCMP.VRFATOCONV ELSE 0 END)) AS CUSTO
        FROM DATA
        LEFT JOIN
             CARDADIA CARD
          ON CARD.CDFILIAL = P_CDFILIAL
         AND CARD.CDSERVFILI = P_CDSERVFILI
         AND CARD.DTCARDAPIO = DATA.DTDATA
         AND CARD.NRORG = P_NRORG
        LEFT JOIN
             RETIRADA RETI
          ON RETI.CDFILIAL = CARD.CDFILIAL
         AND RETI.NRCARDAPIO = CARD.NRCARDAPIO
         AND RETI.NRORG = CARD.NRORG
        LEFT JOIN
             ITEMRETI IRET
          ON IRET.CDFILIAL = RETI.CDFILIAL
         AND IRET.NRCARDAPIO = RETI.NRCARDAPIO
         AND IRET.NRRETIRADA = RETI.NRRETIRADA
         AND IRET.NRORG = RETI.NRORG
        LEFT JOIN
             ITLANCTOEST ILAN
          ON ILAN.CDFILIAL = IRET.CDFIITLANEST
         AND ILAN.NRLANCESTQ = IRET.NRLANCESTQ
         AND ILAN.NRSEQUITEM = IRET.NRSEQUITEM
         AND ILAN.NRORG = IRET.NRORG
        LEFT JOIN
             PRODUTO PCMP /* PRODUTO DE COMPRA */
          ON PCMP.CDPRODUTO = IRET.CDPRODUTO
         AND PCMP.NRORG = IRET.NRORG
        LEFT JOIN
             PRODUTO PEST /* PRODUTO DE ESTOQUE */
          ON PEST.CDPRODUTO = PCMP.CDPRODESTO
         AND PEST.NRORG = PCMP.NRORG
        LEFT JOIN
             CUSTPADR CPAD
          ON CPAD.CDFILIAL = P_CDFILIAL
         AND CPAD.CDSERVFILI = P_CDSERVFILI
         AND CPAD.DTREFCUSPADR = DATA.DTMES
         AND CPAD.NRORG = P_NRORG
        LEFT JOIN (
             SELECT DTCARDAPIO,
                    SUM(QTCLIEFETCAR) AS QTCLIEFETCAR,
                    SUM(QTCOMEFETCAR) AS QTCOMEFETCAR
               FROM CARDADIA
              WHERE CDFILIAL = P_CDFILIAL
                AND CDSERVFILI = P_CDSERVFILI
                AND DTMESCARD BETWEEN P_DTINICIAL AND P_DTFINAL
                AND NRORG = P_NRORG
              GROUP BY
                    DTCARDAPIO
             ) COME
          ON COME.DTCARDAPIO = DATA.DTDATA
       WHERE DATA.DTDATA BETWEEN P_DTINICIAL AND P_DTFINAL
       GROUP BY
             DATA.DTDATA,
             CARD.DTCARDAPIO,
             CPAD.VRCUSTPADR,
             DECODE(V_IDCOMCUSPERC, 'C', COME.QTCLIEFETCAR, COME.QTCOMEFETCAR)
       ORDER BY
             CARD.DTCARDAPIO
   )
   LOOP
      V_VRCUSTOACU := V_VRCUSTOACU + NVL(CR.CUSTO, 0);
      V_QTCOMESACU := V_QTCOMESACU + NVL(CR.QTCOMEFETCAR, 0);
      IF CR.DTDATA < V_DTPROCESSA THEN
         V_IDCUSTREFANA := 'S';
         V_QTCOMACUACOMP := V_QTCOMACUACOMP + NVL(CR.QTCOMEFETCAR, 0);
         V_VRCUSACUACOMP := V_VRCUSACUACOMP + NVL(CR.CUSTO, 0);
      ELSE
         V_IDCUSTREFANA := 'N';
      END IF;
      INSERT
        INTO PLA_CUSTOCARDAPIO (
             NRCONSPLAN,
             CDFILCUSPLA,
             CDSERVCUSPLA,
             DTCUSCARDPLA,
             VRCUSTPRODMAT,
             VRCUSTOCONS,
             VRCUSTORAT,
             QTCOMESTIPLA,
             VRCUSTOTCPLA,
             VRCUSTACUCAR,
             QTCOMESTACU,
             VRCUSTMATACU,
             VRCUSTOCONSACU,
             VRCUSTORATACU,
             VRCUSTPADRCPLA,
             IDPRECODESATU,
             IDTIPOCUSTOPLA,
             IDCUSTREFANA,
             QTCOMACUACOMP,
             VRCUSACUACOMP,
             NRORG
      )
      VALUES (
             P_NRCONSPLAN,
             P_CDFILIAL,
             P_CDSERVFILI,
             CR.DTDATA,
             0,
             0,
             0,
             ROUND(NVL(CR.QTCOMEFETCAR, 0), 5),
             ROUND(NVL(CR.CUSTO, 0), 5),
             ROUND(V_VRCUSTOACU, 5),
             ROUND(V_QTCOMESACU, 5),
             0,
             0,
             0,
             ROUND(NVL(CR.VRCUSTPADR, 0), 5),
             'N',
             'R',
             V_IDCUSTREFANA,
             ROUND(V_QTCOMACUACOMP, 5),
             ROUND(V_VRCUSACUACOMP, 5),
             P_NRORG
      );
   END LOOP;
   V_VRCUSTOACU := 0;
   V_QTCOMESACU := 0;
   FOR CP IN (
      SELECT DATA.DTDATA,
             MATP.VRCUSTPRODMAT,
             CONS.VRCUSTOCONS,
             RATE.VRCUSTORAT,
             CPAD.VRCUSTPADR,
             REPLACE(MATP.IDPRECODESATUMP||CONS.IDPRECODESATUCO||RATE.IDPRECODESATUNE, 'N', '') AS IDPRECODESATU,
             SUM(DECODE(V_IDCOMCUSPERC, 'C', CARD.QTCLIESTICAR, CARD.QTCOMESTICAR)) AS QTCOMESTIPLA
        FROM DATA
        LEFT JOIN
             CARDADIA CARD
          ON CARD.CDFILIAL = P_CDFILIAL
         AND CARD.CDSERVFILI = P_CDSERVFILI
         AND CARD.DTCARDAPIO = DATA.DTDATA
         AND CARD.NRORG = P_NRORG
        LEFT JOIN
             CUSTPADR CPAD
          ON CPAD.CDFILIAL = P_CDFILIAL
         AND CPAD.CDSERVFILI = P_CDSERVFILI
         AND CPAD.DTREFCUSPADR = DATA.DTMES
         AND CPAD.NRORG = P_NRORG
        LEFT JOIN (
             SELECT CAR.DTCARDAPIO AS DTCONSPLAN,
                    NVL(SUM(ROUND(CUS.VRPLANNESTO * (RAT.PERATENESTO/100) * (CAR.QTCOMESTICAR/COM.TOTCOMEN), 5)), 0) AS VRCUSTORAT,
                    MAX(CUSTO_PROD_LISTADESATU(P_CDFILICALCCUSTO, RAT.CDPRODUTO, P_NRTIPOCUSTOPLA, NVL(P_DTREFECUSTO, CAR.DTCARDAPIO))) AS IDPRECODESATUNE
               FROM RATENESTO RAT, CUSTNESTO CUS, CARDADIA CAR,
                    (SELECT CDFILIAL,
                            CDSERVFILI,
                            DTMESCARD,
                            NRORG,
                            SUM(QTCOMESTICAR) AS TOTCOMEN
                       FROM CARDADIA
                      WHERE CDFILIAL = P_CDFILIAL
                        AND CDSERVFILI = P_CDSERVFILI
                        AND DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
                        AND NRORG = P_NRORG
                      GROUP BY
                            CDFILIAL,
                            CDSERVFILI,
                            DTMESCARD,
                            NRORG) COM
              WHERE CAR.CDFILIAL = COM.CDFILIAL
                AND CAR.CDSERVFILI = COM.CDSERVFILI
                AND CAR.DTMESCARD = COM.DTMESCARD
                AND CAR.NRORG = COM.NRORG
                AND NOT COM.TOTCOMEN IS NULL
                AND COM.TOTCOMEN <> 0
                AND CUS.CDFILIAL = COM.CDFILIAL
                AND CUS.DTCUSTNESTO = COM.DTMESCARD
                AND CUS.NRORG = COM.NRORG
                AND RAT.CDFILIAL = CUS.CDFILIAL
                AND RAT.CDPRODUTO = CUS.CDPRODUTO
                AND RAT.CDSERVFILI = COM.CDSERVFILI
                AND RAT.NRORG = COM.NRORG
              GROUP BY
                    CAR.DTCARDAPIO
             ) RATE
          ON RATE.DTCONSPLAN = DATA.DTDATA
        LEFT JOIN (
             SELECT DTCONSPLAN,
                    NVL(SUM(CUSTO_PRODUTO(NVL(P_CDFILICALCCUSTO, P_CDFILIAL), CDPRODCPLA, P_NRTIPOCUSTOPLA, NVL(P_DTREFECUSTO, DTCONSPLAN), SYSDATE) * QTCONSPLAN), 0) AS VRCUSTOCONS,
                    MAX(CUSTO_PROD_LISTADESATU(P_CDFILICALCCUSTO, CDPRODCPLA, P_NRTIPOCUSTOPLA, NVL(P_DTREFECUSTO, DTCONSPLAN))) AS IDPRECODESATUCO
               FROM CONSPLAN
              WHERE NRCONSPLAN = P_NRCONSPLAN
                AND CDFILICPLA = P_CDFILIAL
                AND CDSERVCPLA = P_CDSERVFILI
                AND IDCONSPLAN IN ('P', 'F')
                AND NRORG = P_NRORG
              GROUP BY
                    DTCONSPLAN
             ) CONS
          ON CONS.DTCONSPLAN = DATA.DTDATA
        LEFT JOIN (
             SELECT DTCONSPLAN,
                    NVL(SUM(CUSTO_PRODUTO(NVL(P_CDFILICALCCUSTO, P_CDFILIAL), CDPRODCPLA, P_NRTIPOCUSTOPLA, NVL(P_DTREFECUSTO, DTCONSPLAN), SYSDATE) * QTCONSPLAN), 0) AS VRCUSTPRODMAT,
                    MAX(CUSTO_PROD_LISTADESATU(P_CDFILICALCCUSTO, CDPRODCPLA, P_NRTIPOCUSTOPLA, NVL(P_DTREFECUSTO, DTCONSPLAN))) AS IDPRECODESATUMP
               FROM CONSPLAN
              WHERE NRCONSPLAN = P_NRCONSPLAN
                AND CDFILICPLA = P_CDFILIAL
                AND CDSERVCPLA = P_CDSERVFILI
                AND IDCONSPLAN = 'C'
                AND NRORG = P_NRORG
              GROUP BY
                    DTCONSPLAN
             ) MATP
          ON MATP.DTCONSPLAN = DATA.DTDATA
       WHERE DATA.DTDATA BETWEEN P_DTINICIAL AND P_DTFINAL
       GROUP BY
             DATA.DTDATA,
             MATP.VRCUSTPRODMAT,
             CONS.VRCUSTOCONS,
             RATE.VRCUSTORAT,
             CPAD.VRCUSTPADR,
             REPLACE(MATP.IDPRECODESATUMP||CONS.IDPRECODESATUCO||RATE.IDPRECODESATUNE, 'N', '')
       ORDER BY
             DATA.DTDATA
   ) LOOP
      V_VRCUSTOACU := V_VRCUSTOACU + NVL(CP.VRCUSTPRODMAT, 0) + NVL(CP.VRCUSTOCONS, 0) + NVL(CP.VRCUSTORAT, 0);
      V_QTCOMESACU := V_QTCOMESACU + NVL(CP.QTCOMESTIPLA, 0);
      V_VRCUSTMATACU := V_VRCUSTMATACU + NVL(CP.VRCUSTPRODMAT, 0);
      V_VRCUSTOCONSACU := V_VRCUSTOCONSACU + NVL(CP.VRCUSTOCONS, 0);
      V_VRCUSTORATACU := V_VRCUSTORATACU + NVL(CP.VRCUSTORAT, 0);
      IF CP.DTDATA >= V_DTPROCESSA THEN
         V_IDCUSTREFANA := 'S';
         V_QTCOMACUACOMP := V_QTCOMACUACOMP + NVL(CP.QTCOMESTIPLA, 0);
         V_VRCUSACUACOMP := V_VRCUSACUACOMP + NVL(CP.VRCUSTPRODMAT, 0) + NVL(CP.VRCUSTOCONS, 0) + NVL(CP.VRCUSTORAT, 0);
      ELSE
         V_IDCUSTREFANA := 'N';
      END IF;

      V_IDPRECODESATU := 'N';
      IF (INSTR(CP.IDPRECODESATU, 'S') > 0) THEN
         V_IDPRECODESATU := 'S';
      END IF;

      INSERT
        INTO PLA_CUSTOCARDAPIO (
             NRCONSPLAN,
             CDFILCUSPLA,
             CDSERVCUSPLA,
             DTCUSCARDPLA,
             VRCUSTPRODMAT,
             VRCUSTOCONS,
             VRCUSTORAT,
             QTCOMESTIPLA,
             VRCUSTOTCPLA,
             VRCUSTACUCAR,
             QTCOMESTACU,
             VRCUSTMATACU,
             VRCUSTOCONSACU,
             VRCUSTORATACU,
             VRCUSTPADRCPLA,
             IDPRECODESATU,
             IDTIPOCUSTOPLA,
             IDCUSTREFANA,
             QTCOMACUACOMP,
             VRCUSACUACOMP,
             NRORG
      )
      VALUES (
             P_NRCONSPLAN,
             P_CDFILIAL,
             P_CDSERVFILI,
             CP.DTDATA,
             ROUND(NVL(CP.VRCUSTPRODMAT, 0), 5),
             ROUND(NVL(CP.VRCUSTOCONS, 0), 5),
             ROUND(NVL(CP.VRCUSTORAT, 0), 5),
             ROUND(NVL(CP.QTCOMESTIPLA, 0), 5),
             ROUND(NVL(CP.VRCUSTPRODMAT, 0) + NVL(CP.VRCUSTOCONS, 0) + NVL(CP.VRCUSTORAT, 0), 5),
             ROUND(V_VRCUSTOACU, 5),
             ROUND(V_QTCOMESACU, 5),
             ROUND(V_VRCUSTMATACU, 5),
             ROUND(V_VRCUSTOCONSACU, 5),
             ROUND(V_VRCUSTORATACU, 5),
             ROUND(NVL(CP.VRCUSTPADR, 0), 5),
             NVL(V_IDPRECODESATU, 'N'),
             'P',
             V_IDCUSTREFANA,
             ROUND(V_QTCOMACUACOMP, 5),
             ROUND(V_VRCUSACUACOMP, 5),
             P_NRORG
      );
   END LOOP;
   IF P_IDEXCLUI_CONSPLAN = 'S' THEN
      DELETE
        FROM CONSPLAN
       WHERE NRCONSPLAN = P_NRCONSPLAN
         AND NRORG = P_NRORG;
   END IF;      
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('PLA_ANALISE_CUSTO_CARDAPIO','5.585.xxx'); END;
/
