DROP FUNCTION CONSREAL_X_PLANPME
/

DROP TYPE CONSREAL_X_PLANPME_TABLE
/

DROP TYPE CONSREAL_X_PLANPME_TYPE
/

CREATE TYPE CONSREAL_X_PLANPME_TYPE AS
OBJECT (NRCONSREAL_X_PLANPME NUMBER,
        CDFILIAL             VARCHAR2(04),
        NMFILIAL             VARCHAR2(30),
        CDSERVFILI           VARCHAR2(05),
        NMSERVICO            VARCHAR2(40),
        CDAGRUPROD           VARCHAR2(20),
        NMAGRUPROD           VARCHAR2(50),
        GRUPO                VARCHAR2(50),
        DTCARDAPIO           DATE,
        CUSTOPLAN            FLOAT(126),
        QTCONSPLAN           FLOAT(126),
        QTCOMESTICAR         FLOAT(126),
        QTCLIESTICAR         FLOAT(126),
        CUSTOREAL            FLOAT(126),
        QTRETIRADA           FLOAT(126),
        QTCOMEFETCAR         FLOAT(126),
        QTCLIEFETCAR         FLOAT(126),        
        NIVEL                VARCHAR2(50),
        CDTIPOCARD           VARCHAR2(10),
        NMTIPOCARD           VARCHAR2(100),        
        NRORG                NUMBER)
/

CREATE TYPE CONSREAL_X_PLANPME_TABLE AS
TABLE OF CONSREAL_X_PLANPME_TYPE
/

CREATE OR REPLACE FUNCTION CONSREAL_X_PLANPME
(
   P_CDFILCENTPME   IN VARCHAR2,
   P_NRCONSPLAN     IN VARCHAR2,
   P_NRLISTAFILI    IN VARCHAR2,
   P_LISTASERVICO   IN VARCHAR2,
   P_LISTATIPOCARD  IN VARCHAR2,
   P_DTMESINIC      IN DATE,
   P_DTMESFINA      IN DATE,
   P_DTINICIAL      IN DATE,
   P_DTFINAL        IN DATE,
   P_NRTIPOCUSTO    IN NUMBER,
   P_NRTAMACNIVEL   IN NUMBER,  
   P_NIVEL          IN VARCHAR2,
   P_IDESTOCAVEIS   IN VARCHAR2,
   P_IDNESTOCAVEIS  IN VARCHAR2
) RETURN CONSREAL_X_PLANPME_TABLE PIPELINED
IS
  V_SEQ          NUMBER;
  V_NMAGRUPPROD  PRODUTO.NMPRODUTO%TYPE;
  OUT_RECORD CONSREAL_X_PLANPME_TYPE := CONSREAL_X_PLANPME_TYPE(NULL, NULL, NULL, NULL, NULL, NULL,
                                                                NULL, NULL, NULL, NULL, NULL, NULL,
                                                                NULL, NULL, NULL, NULL, NULL, NULL,
                                                                NULL, NULL, NULL);
BEGIN                      
  FOR C_SERVICO IN (SELECT F.CDFILIAL, F.NMFILIAL, SE.CDSERVFILI, SE.NMSERVICO, F.NRORG
                      FROM FILIAL F INNER JOIN LISTA L ON L.CDLISTA = F.CDFILIAL
                                    INNER JOIN SERVICO SE ON SE.CDFILIAL = F.CDFILIAL
                                    INNER JOIN LISTA_SERVICO LS ON LS.CDFILIAL = SE.CDFILIAL
                                                               AND LS.CDSERVFILI = SE.CDSERVFILI
                     WHERE LS.NRLISTA = P_LISTASERVICO
                       AND L.NRLISTA = P_NRLISTAFILI
                     ORDER BY SE.CDSERVFILI) LOOP
     
     FOR C_TIPOCARD IN (SELECT TC.CDFILCENTPME, TC.CDTIPOCARD, TC.NMTIPOCARD
                          FROM TIPOCARD TC INNER JOIN LISTA_TIPOCARD LTC ON LTC.CDTIPOCARD = TC.CDTIPOCARD
                         WHERE LTC.NRLISTA = P_LISTATIPOCARD
                           AND TC.CDFILCENTPME = P_CDFILCENTPME) LOOP
                       
        FOR C_CARDAPIO IN (SELECT CD.DTCARDIAPME DTCARDAPIO, 
                                  SUM(CD.QTCOMESTCPME) QTCOMESTICAR, SUM(CD.QTCLIESTCPME) QTCLIESTICAR, SUM(CD.QTCOMEFECPME) QTCOMEFETCAR, SUM(CD.QTCLIEFECPME) QTCLIEFETCAR
                             FROM CARDAPIOPME CP, CARDIAPME CD
                            WHERE CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                              AND CP.CDFILCENTPME = CD.CDFILCENTPME
                              AND CP.NRCARDPME = CD.NRCARDPME
                              AND CD.DTCARDIAPME BETWEEN P_DTINICIAL AND P_DTFINAL
                              AND CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                              AND CP.CDTIPOCARD = C_TIPOCARD.CDTIPOCARD
                              AND CD.CDFILIAL = C_SERVICO.CDFILIAL
                              AND CD.CDSERVFILI = C_SERVICO.CDSERVFILI
                            GROUP BY CD.DTCARDIAPME) LOOP
                            
           V_SEQ := 0;
           
           FOR C_CONSPLAN IN (SELECT SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, P_NRTIPOCUSTO, CP.DTCONSPLAN, SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN,
                                     0 AS CUSTOREAL, CP.CDPRODCPLA CDPRODUTO, SUM(CP.QTCONSPLAN) QTCONSPLAN, 0 AS QTRETIRADA,
                                     PD.CDARVPROD, PD.NMPRODUTO, PD.SGUNIDADE
                                FROM CONSPLAN CP, PRODUTO PD
                               WHERE P_IDESTOCAVEIS = 'S'
                                 AND CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                                 AND CP.NRCONSPLAN = P_NRCONSPLAN
                                 AND CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                                 AND CP.CDTPCARDPME  = C_TIPOCARD.CDTIPOCARD
                                 AND CP.CDPRODCPLA = PD.CDPRODUTO
                                 AND CP.CDFILICPLA = C_SERVICO.CDFILIAL
                                 AND CP.CDSERVCPLA = C_SERVICO.CDSERVFILI
                                 AND CP.DTCONSPLAN = C_CARDAPIO.DTCARDAPIO
                                GROUP BY CP.CDPRODCPLA, PD.CDARVPROD, PD.NMPRODUTO, PD.SGUNIDADE) LOOP
              
              V_SEQ := V_SEQ + 1;
              OUT_RECORD.NRCONSREAL_X_PLANPME := V_SEQ;
              OUT_RECORD.CDFILIAL             := C_SERVICO.CDFILIAL;
              OUT_RECORD.NMFILIAL             := C_SERVICO.NMFILIAL;
              OUT_RECORD.CDSERVFILI           := C_SERVICO.CDSERVFILI;
              OUT_RECORD.NMSERVICO            := C_SERVICO.NMSERVICO;
              OUT_RECORD.CDAGRUPROD           := SUBSTR(C_CONSPLAN.CDARVPROD,1,P_NRTAMACNIVEL);
              SELECT MAX(NMPRODUTO)
                INTO V_NMAGRUPPROD
                FROM PRODUTO
               WHERE CDARVPROD = SUBSTR(C_CONSPLAN.CDARVPROD,1,P_NRTAMACNIVEL);
              OUT_RECORD.NMAGRUPROD           := V_NMAGRUPPROD;
              OUT_RECORD.GRUPO                := CASE WHEN SUBSTR(C_CONSPLAN.CDARVPROD, 1, P_NRTAMACNIVEL) = C_CONSPLAN.CDARVPROD
                                                      THEN C_CONSPLAN.NMPRODUTO
                                                      ELSE SUBSTR(C_CONSPLAN.CDARVPROD, 1, P_NRTAMACNIVEL)
                                                 END;
              OUT_RECORD.DTCARDAPIO           := C_CARDAPIO.DTCARDAPIO;
              OUT_RECORD.CUSTOPLAN            := C_CONSPLAN.CUSTOPLAN;
              OUT_RECORD.QTCONSPLAN           := C_CONSPLAN.QTCONSPLAN;
              OUT_RECORD.QTCOMESTICAR         := C_CARDAPIO.QTCOMESTICAR;
              OUT_RECORD.QTCLIESTICAR         := C_CARDAPIO.QTCLIESTICAR;
              OUT_RECORD.CUSTOREAL            := C_CONSPLAN.CUSTOREAL;
              OUT_RECORD.QTRETIRADA           := C_CONSPLAN.QTRETIRADA;
              OUT_RECORD.QTCOMEFETCAR         := C_CARDAPIO.QTCOMEFETCAR;
              OUT_RECORD.QTCLIEFETCAR         := C_CARDAPIO.QTCLIEFETCAR;        
              OUT_RECORD.NIVEL                := RPAD(P_NIVEL, 1,' ');
              OUT_RECORD.CDTIPOCARD           := C_TIPOCARD.CDTIPOCARD;
              OUT_RECORD.NMTIPOCARD           := C_TIPOCARD.NMTIPOCARD;
              OUT_RECORD.NRORG                := C_SERVICO.NRORG;
              PIPE ROW (OUT_RECORD);
           END LOOP;
           
           FOR C_CONSPLAN IN ( SELECT SUM(ROUND(CNE.VRPLANNESTO * (RNE.PERATENESTO/100) * (CD.QTCOMESTCPME/H.TOTCOMEN), 5)) AS CUSTOPLAN,
                                      0 AS CUSTOREAL, CNE.CDPRODUTO AS CDPRODUTO,
                                      0 AS QTCONSPLAN, 0 AS QTRETIRADA, PDA.CDARVPROD, PDA.NMPRODUTO, PDA.SGUNIDADE
                                 FROM (SELECT CD.DTCARDIAPME - TO_CHAR(CD.DTCARDIAPME, 'DD') + 1 DTMESCARD,
                                              CD.CDSERVFILI, CP.CDTIPOCARD, SUM(CD.QTCOMESTCPME) AS TOTCOMEN
                                         FROM CARDAPIOPME CP, CARDIAPME CD
                                        WHERE CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                                          AND CP.CDFILCENTPME = CD.CDFILCENTPME
                                          AND CP.NRCARDPME = CD.NRCARDPME
                                          AND CD.DTCARDIAPME = C_CARDAPIO.DTCARDAPIO
                                          AND CP.CDTIPOCARD = C_TIPOCARD.CDTIPOCARD
                                          AND CD.CDFILIAL = C_SERVICO.CDFILIAL
                                          AND CD.CDSERVFILI = C_SERVICO.CDSERVFILI
                                        GROUP BY CD.DTCARDIAPME, CD.CDSERVFILI, CP.CDTIPOCARD) H,
                                      CARDAPIOPME CP, CARDIAPME CD, RATENESTO RNE, CUSTNESTO CNE,
                                      PRODUTO PDA, DATA DT
                                WHERE P_IDNESTOCAVEIS = 'S'
                                  AND DT.DTDATA = C_CARDAPIO.DTCARDAPIO                                
                                  AND TO_CHAR(DT.DTDATA, 'MMYYYY') = TO_CHAR(CNE.DTCUSTNESTO, 'MMYYYY')
                                  AND CNE.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                                  AND CP.CDTIPOCARD = C_TIPOCARD.CDTIPOCARD
                                  AND RNE.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND RNE.CDSERVFILI = C_SERVICO.CDSERVFILI
                                  AND CP.CDFILCENTPME = CD.CDFILCENTPME
                                  AND CP.NRCARDPME = CD.NRCARDPME
                                  AND CNE.CDPRODUTO = RNE.CDPRODUTO
                                  AND CD.CDFILIAL = RNE.CDFILIAL
                                  AND CD.CDSERVFILI = RNE.CDSERVFILI
                                  AND CD.DTCARDIAPME = DT.DTDATA
                                  AND H.DTMESCARD = CD.DTCARDIAPME - TO_CHAR(CD.DTCARDIAPME, 'DD') + 1
                                  AND H.CDSERVFILI = RNE.CDSERVFILI
                                  AND NOT H.TOTCOMEN IS NULL AND H.TOTCOMEN <> 0
                                  AND PDA.CDPRODUTO = CNE.CDPRODUTO
                                GROUP BY CNE.CDPRODUTO, PDA.CDARVPROD, PDA.NMPRODUTO, PDA.SGUNIDADE) LOOP
              
              V_SEQ := V_SEQ + 1;
              OUT_RECORD.NRCONSREAL_X_PLANPME := V_SEQ;
              OUT_RECORD.CDFILIAL             := C_SERVICO.CDFILIAL;
              OUT_RECORD.NMFILIAL             := C_SERVICO.NMFILIAL;
              OUT_RECORD.CDSERVFILI           := C_SERVICO.CDSERVFILI;
              OUT_RECORD.NMSERVICO            := C_SERVICO.NMSERVICO;
              OUT_RECORD.CDAGRUPROD           := SUBSTR(C_CONSPLAN.CDARVPROD,1,P_NRTAMACNIVEL);
              SELECT MAX(NMPRODUTO)
                INTO V_NMAGRUPPROD
                FROM PRODUTO
               WHERE CDARVPROD = SUBSTR(C_CONSPLAN.CDARVPROD,1,P_NRTAMACNIVEL);
              OUT_RECORD.NMAGRUPROD           := V_NMAGRUPPROD;
              OUT_RECORD.GRUPO                := CASE WHEN SUBSTR(C_CONSPLAN.CDARVPROD, 1, P_NRTAMACNIVEL) = C_CONSPLAN.CDARVPROD
                                                      THEN C_CONSPLAN.NMPRODUTO
                                                      ELSE SUBSTR(C_CONSPLAN.CDARVPROD, 1, P_NRTAMACNIVEL)
                                                 END;
              OUT_RECORD.DTCARDAPIO           := C_CARDAPIO.DTCARDAPIO;
              OUT_RECORD.CUSTOPLAN            := C_CONSPLAN.CUSTOPLAN;
              OUT_RECORD.QTCONSPLAN           := C_CONSPLAN.QTCONSPLAN;
              OUT_RECORD.QTCOMESTICAR         := C_CARDAPIO.QTCOMESTICAR;
              OUT_RECORD.QTCLIESTICAR         := C_CARDAPIO.QTCLIESTICAR;
              OUT_RECORD.CUSTOREAL            := C_CONSPLAN.CUSTOREAL;
              OUT_RECORD.QTRETIRADA           := C_CONSPLAN.QTRETIRADA;
              OUT_RECORD.QTCOMEFETCAR         := C_CARDAPIO.QTCOMEFETCAR;
              OUT_RECORD.QTCLIEFETCAR         := C_CARDAPIO.QTCLIEFETCAR;        
              OUT_RECORD.NIVEL                := RPAD(P_NIVEL, 1,' ');
              OUT_RECORD.CDTIPOCARD           := C_TIPOCARD.CDTIPOCARD;
              OUT_RECORD.NMTIPOCARD           := C_TIPOCARD.NMTIPOCARD;
              OUT_RECORD.NRORG                := C_SERVICO.NRORG;
              PIPE ROW (OUT_RECORD);
           END LOOP;
           
           FOR C_CUSTOREAL IN (SELECT 0 CUSTOPLAN, SUM(C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, P_NRTIPOCUSTO, C.DTCARDAPIO, SYSDATE)) AS CUSTOREAL,
                                      C.CDPRODUTO, 0 QTCONSPLAN, SUM(C.QTRETIRADA) QTRETIRADA, C.CDARVPROD, C.NMPRODUTO, C.SGUNIDADE, NULL AS CDTIPOCARD
                                 FROM (SELECT CA.CDFILIAL, CA.CDSERVFILI, CA.DTCARDAPIO, SUM(IR.QTRETIRADA * PRA.VRFATOCONV) AS QTRETIRADA,
                                              PRB.CDPRODUTO, PRB.CDARVPROD, PRB.NMPRODUTO, PRB.SGUNIDADE
                                         FROM CARDADIA CA, PRODUTO PRA, PRODUTO PRB, ITEMRETI IR, ITLANCTOEST IL
                                        WHERE P_IDESTOCAVEIS = 'S'
                                          AND PRB.CDPRODUTO = PRA.CDPRODESTO
                                          AND PRA.CDPRODUTO = IR.CDPRODUTO
                                          AND CA.CDFILIAL = IR.CDFILIAL
                                          AND (NOT IR.QTRETIRADA IS NULL AND IR.QTRETIRADA <> 0)
                                          AND IL.CDFILIAL = IR.CDFIITLANEST
                                          AND IL.NRLANCESTQ = IR.NRLANCESTQ
                                          AND IL.NRSEQUITEM = IR.NRSEQUITEM
                                          AND IL.IDEVOLESTOQ = 'S'
                                          AND CA.DTCARDAPIO = C_CARDAPIO.DTCARDAPIO
                                          AND CA.NRCARDAPIO = IR.NRCARDAPIO
                                          AND CA.CDFILIAL = C_SERVICO.CDFILIAL
                                          AND CA.CDSERVFILI = C_SERVICO.CDSERVFILI
                                        GROUP BY CA.CDFILIAL, CA.CDSERVFILI, CA.DTCARDAPIO, PRB.CDPRODUTO, PRB.CDARVPROD, PRB.NMPRODUTO, PRB.SGUNIDADE) C
                                GROUP BY CDPRODUTO, CDARVPROD, NMPRODUTO, SGUNIDADE) LOOP
              
              V_SEQ := V_SEQ + 1;
              OUT_RECORD.NRCONSREAL_X_PLANPME := V_SEQ;
              OUT_RECORD.CDFILIAL             := C_SERVICO.CDFILIAL;
              OUT_RECORD.NMFILIAL             := C_SERVICO.NMFILIAL;
              OUT_RECORD.CDSERVFILI           := C_SERVICO.CDSERVFILI;
              OUT_RECORD.NMSERVICO            := C_SERVICO.NMSERVICO;
              OUT_RECORD.CDAGRUPROD           := SUBSTR(C_CUSTOREAL.CDARVPROD,1,P_NRTAMACNIVEL);
              SELECT MAX(NMPRODUTO)
                INTO V_NMAGRUPPROD
                FROM PRODUTO
               WHERE CDARVPROD = SUBSTR(C_CUSTOREAL.CDARVPROD,1,P_NRTAMACNIVEL);
              OUT_RECORD.NMAGRUPROD           := V_NMAGRUPPROD;
              OUT_RECORD.GRUPO                := CASE WHEN SUBSTR(C_CUSTOREAL.CDARVPROD, 1, P_NRTAMACNIVEL) = C_CUSTOREAL.CDARVPROD
                                                      THEN C_CUSTOREAL.NMPRODUTO
                                                      ELSE SUBSTR(C_CUSTOREAL.CDARVPROD, 1, P_NRTAMACNIVEL)
                                                 END;
              OUT_RECORD.DTCARDAPIO           := C_CARDAPIO.DTCARDAPIO;
              OUT_RECORD.CUSTOPLAN            := C_CUSTOREAL.CUSTOPLAN;
              OUT_RECORD.QTCONSPLAN           := C_CUSTOREAL.QTCONSPLAN;
              OUT_RECORD.QTCOMESTICAR         := C_CARDAPIO.QTCOMESTICAR;
              OUT_RECORD.QTCLIESTICAR         := C_CARDAPIO.QTCLIESTICAR;
              OUT_RECORD.CUSTOREAL            := C_CUSTOREAL.CUSTOREAL;
              OUT_RECORD.QTRETIRADA           := C_CUSTOREAL.QTRETIRADA;
              OUT_RECORD.QTCOMEFETCAR         := C_CARDAPIO.QTCOMEFETCAR;
              OUT_RECORD.QTCLIEFETCAR         := C_CARDAPIO.QTCLIEFETCAR;        
              OUT_RECORD.NIVEL                := RPAD(P_NIVEL, 1,' ');
              OUT_RECORD.CDTIPOCARD           := C_TIPOCARD.CDTIPOCARD;
              OUT_RECORD.NMTIPOCARD           := C_TIPOCARD.NMTIPOCARD;
              OUT_RECORD.NRORG                := C_SERVICO.NRORG;
              PIPE ROW (OUT_RECORD);
           END LOOP;
           
           FOR C_CUSTOREAL IN (SELECT 0 AS CUSTOPLAN, SUM(ROUND(CNE.VRREALNESTO * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL,
                                      CNE.CDPRODUTO AS CDPRODUTO, 0 AS QTCONSPLAN, 0 AS QTRETIRADA, PDB.CDARVPROD, PDB.NMPRODUTO, PDB.SGUNIDADE, NULL AS CDTIPOCARD
                                 FROM (SELECT CARDADIA.DTMESCARD, CARDADIA.CDSERVFILI, SUM(CARDADIA.QTCOMEFETCAR) AS TOTCOMEN
                                         FROM CARDADIA
                                        WHERE CARDADIA.DTCARDAPIO BETWEEN P_DTMESINIC AND P_DTMESFINA
                                          AND CARDADIA.CDFILIAL = C_SERVICO.CDFILIAL
                                          AND CARDADIA.CDSERVFILI = C_SERVICO.CDSERVFILI
                                        GROUP BY CARDADIA.DTMESCARD, CARDADIA.CDSERVFILI) TC, PRODUTO PDB, CARDADIA  CA, RATENESTO RNE, CUSTNESTO CNE, DATA DT
                                WHERE P_IDNESTOCAVEIS = 'S'
                                  AND DT.DTDATA = C_CARDAPIO.DTCARDAPIO
                                  AND TO_CHAR(DT.DTDATA, 'MMYYYY') = TO_CHAR(CNE.DTCUSTNESTO, 'MMYYYY')
                                  AND NOT CNE.VRREALNESTO IS NULL
                                  AND CNE.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND CA.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND RNE.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND RNE.CDSERVFILI = C_SERVICO.CDSERVFILI
                                  AND RNE.CDPRODUTO = CNE.CDPRODUTO
                                  AND CA.CDSERVFILI = RNE.CDSERVFILI
                                  AND CA.DTCARDAPIO = DT.DTDATA
                                  AND TC.DTMESCARD = CA.DTMESCARD
                                  AND TC.CDSERVFILI = CA.CDSERVFILI
                                  AND (NOT TC.TOTCOMEN IS NULL AND TC.TOTCOMEN <> 0)
                                  AND PDB.CDPRODUTO = CNE.CDPRODUTO
                                GROUP BY CNE.CDPRODUTO, PDB.CDARVPROD, PDB.NMPRODUTO, PDB.SGUNIDADE) LOOP
              
              V_SEQ := V_SEQ + 1;
              OUT_RECORD.NRCONSREAL_X_PLANPME := V_SEQ;
              OUT_RECORD.CDFILIAL             := C_SERVICO.CDFILIAL;
              OUT_RECORD.NMFILIAL             := C_SERVICO.NMFILIAL;
              OUT_RECORD.CDSERVFILI           := C_SERVICO.CDSERVFILI;
              OUT_RECORD.NMSERVICO            := C_SERVICO.NMSERVICO;
              OUT_RECORD.CDAGRUPROD           := SUBSTR(C_CUSTOREAL.CDARVPROD,1,P_NRTAMACNIVEL);
              SELECT MAX(NMPRODUTO)
                INTO V_NMAGRUPPROD
                FROM PRODUTO
               WHERE CDARVPROD = SUBSTR(C_CUSTOREAL.CDARVPROD,1,P_NRTAMACNIVEL);
              OUT_RECORD.NMAGRUPROD           := V_NMAGRUPPROD;
              OUT_RECORD.GRUPO                := CASE WHEN SUBSTR(C_CUSTOREAL.CDARVPROD, 1, P_NRTAMACNIVEL) = C_CUSTOREAL.CDARVPROD
                                                      THEN C_CUSTOREAL.NMPRODUTO
                                                      ELSE SUBSTR(C_CUSTOREAL.CDARVPROD, 1, P_NRTAMACNIVEL)
                                                 END;
              OUT_RECORD.DTCARDAPIO           := C_CARDAPIO.DTCARDAPIO;
              OUT_RECORD.CUSTOPLAN            := C_CUSTOREAL.CUSTOPLAN;
              OUT_RECORD.QTCONSPLAN           := C_CUSTOREAL.QTCONSPLAN;
              OUT_RECORD.QTCOMESTICAR         := C_CARDAPIO.QTCOMESTICAR;
              OUT_RECORD.QTCLIESTICAR         := C_CARDAPIO.QTCLIESTICAR;
              OUT_RECORD.CUSTOREAL            := C_CUSTOREAL.CUSTOREAL;
              OUT_RECORD.QTRETIRADA           := C_CUSTOREAL.QTRETIRADA;
              OUT_RECORD.QTCOMEFETCAR         := C_CARDAPIO.QTCOMEFETCAR;
              OUT_RECORD.QTCLIEFETCAR         := C_CARDAPIO.QTCLIEFETCAR;        
              OUT_RECORD.NIVEL                := RPAD(P_NIVEL, 1,' ');
              OUT_RECORD.CDTIPOCARD           := C_TIPOCARD.CDTIPOCARD;
              OUT_RECORD.NMTIPOCARD           := C_TIPOCARD.NMTIPOCARD;
              OUT_RECORD.NRORG                := C_SERVICO.NRORG;
              PIPE ROW (OUT_RECORD);
           END LOOP;
           
           FOR C_CUSTOREAL IN (SELECT 0 AS CUSTOPLAN,
                                      SUM(ROUND(TCN.VRTOTAITEM * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL,
                                      TCN.CDPRODUTO CDPRODUTO, 0 AS QTCONSPLAN, 0 AS QTRETIRADA, PDC.CDARVPROD,
                                      PDC.NMPRODUTO, PDC.SGUNIDADE, NULL AS CDTIPOCARD
                                 FROM DATA DT, RATENESTO RNE, CARDADIA CA, PRODUTO PDC, 
                                      (SELECT CARDADIA.DTMESCARD, CARDADIA.CDSERVFILI, SUM(CARDADIA.QTCOMEFETCAR) AS TOTCOMEN
                                         FROM CARDADIA
                                        WHERE CARDADIA.DTCARDAPIO BETWEEN P_DTMESINIC AND P_DTMESFINA
                                          AND CARDADIA.CDFILIAL = C_SERVICO.CDFILIAL
                                          AND CARDADIA.CDFILIAL = C_SERVICO.CDFILIAL
                                          AND CARDADIA.CDSERVFILI = C_SERVICO.CDSERVFILI
                                        GROUP BY CARDADIA.DTMESCARD, CARDADIA.CDSERVFILI) TC,
                                      (SELECT TO_CHAR(NF.DTENTRSAID, 'MMYYYY') AS DTMESNF, IP.CDPRODUTO, SUM(IP.VRTOTAITEM) AS VRTOTAITEM
                                         FROM NOTAFISCAL NF, ITEMPRODUTO IP
                                        WHERE IP.CDFILIAL = NF.CDFILIAL
                                          AND IP.NRLANCTONF = NF.NRLANCTONF
                                          AND NF.IDENTRSAIDOP = 'E'
                                          AND NF.DTENTRSAID BETWEEN P_DTMESINIC AND P_DTMESFINA
                                          AND NF.CDFILIRECE = C_SERVICO.CDFILIAL
                                        GROUP BY TO_CHAR(NF.DTENTRSAID, 'MMYYYY'), IP.CDPRODUTO) TCN
                                WHERE TCN.CDPRODUTO = PDC.CDPRODUTO
                                  AND P_IDNESTOCAVEIS = 'S'
                                  AND DT.DTDATA = C_CARDAPIO.DTCARDAPIO
                                  AND TO_CHAR(DT.DTDATA, 'MMYYYY') = TCN.DTMESNF
                                  AND RNE.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND RNE.CDSERVFILI = C_SERVICO.CDSERVFILI
                                  AND CA.CDFILIAL = C_SERVICO.CDFILIAL
                                  AND CA.CDSERVFILI = RNE.CDSERVFILI
                                  AND CA.DTCARDAPIO = DT.DTDATA
                                  AND TCN.CDPRODUTO = RNE.CDPRODUTO
                                  AND TC.DTMESCARD = CA.DTMESCARD
                                  AND TC.CDSERVFILI = CA.CDSERVFILI
                                  AND (NOT TC.TOTCOMEN IS NULL AND TC.TOTCOMEN <> 0)
                                  AND (NOT EXISTS (SELECT CDFILIAL
                                                     FROM CUSTNESTO
                                                    WHERE DTCUSTNESTO = DT.DTDATA
                                                      AND CDPRODUTO = RNE.CDPRODUTO
                                                      AND NOT VRREALNESTO IS NULL
                                                      AND CUSTNESTO.CDFILIAL = C_SERVICO.CDFILIAL))
                                GROUP BY DT.DTDATA, TCN.CDPRODUTO, PDC.CDARVPROD, PDC.NMPRODUTO, PDC.SGUNIDADE) LOOP
              
              V_SEQ := V_SEQ + 1;
              OUT_RECORD.NRCONSREAL_X_PLANPME := V_SEQ;
              OUT_RECORD.CDFILIAL             := C_SERVICO.CDFILIAL;
              OUT_RECORD.NMFILIAL             := C_SERVICO.NMFILIAL;
              OUT_RECORD.CDSERVFILI           := C_SERVICO.CDSERVFILI;
              OUT_RECORD.NMSERVICO            := C_SERVICO.NMSERVICO;
              OUT_RECORD.CDAGRUPROD           := SUBSTR(C_CUSTOREAL.CDARVPROD,1,P_NRTAMACNIVEL);
              SELECT MAX(NMPRODUTO)
                INTO V_NMAGRUPPROD
                FROM PRODUTO
               WHERE CDARVPROD = SUBSTR(C_CUSTOREAL.CDARVPROD,1,P_NRTAMACNIVEL);
              OUT_RECORD.NMAGRUPROD           := V_NMAGRUPPROD;
              OUT_RECORD.GRUPO                := CASE WHEN SUBSTR(C_CUSTOREAL.CDARVPROD, 1, P_NRTAMACNIVEL) = C_CUSTOREAL.CDARVPROD
                                                      THEN C_CUSTOREAL.NMPRODUTO
                                                      ELSE SUBSTR(C_CUSTOREAL.CDARVPROD, 1, P_NRTAMACNIVEL)
                                                 END;
              OUT_RECORD.DTCARDAPIO           := C_CARDAPIO.DTCARDAPIO;
              OUT_RECORD.CUSTOPLAN            := C_CUSTOREAL.CUSTOPLAN;
              OUT_RECORD.QTCONSPLAN           := C_CUSTOREAL.QTCONSPLAN;
              OUT_RECORD.QTCOMESTICAR         := C_CARDAPIO.QTCOMESTICAR;
              OUT_RECORD.QTCLIESTICAR         := C_CARDAPIO.QTCLIESTICAR;
              OUT_RECORD.CUSTOREAL            := C_CUSTOREAL.CUSTOREAL;
              OUT_RECORD.QTRETIRADA           := C_CUSTOREAL.QTRETIRADA;
              OUT_RECORD.QTCOMEFETCAR         := C_CARDAPIO.QTCOMEFETCAR;
              OUT_RECORD.QTCLIEFETCAR         := C_CARDAPIO.QTCLIEFETCAR;        
              OUT_RECORD.NIVEL                := RPAD(P_NIVEL, 1,' ');
              OUT_RECORD.CDTIPOCARD           := C_TIPOCARD.CDTIPOCARD;
              OUT_RECORD.NMTIPOCARD           := C_TIPOCARD.NMTIPOCARD;
              OUT_RECORD.NRORG                := C_SERVICO.NRORG;
              PIPE ROW (OUT_RECORD);
           END LOOP;
        END LOOP;
     END LOOP;
  END LOOP;
END;
/ 

-- VERSAO --
BEGIN ATUVEROBJ('CONSREAL_X_PLANPME','6.45.028'); END;
/

