DROP FUNCTION CONSUMOD_ROP
/

DROP TYPE CONSUMOD_ROP_TABLE
/

DROP TYPE CONSUMOD_ROP_TYPE
/

CREATE TYPE CONSUMOD_ROP_TYPE AS
OBJECT (NRCONSUMOD_ROP     NUMBER,
        NMFILIAL           VARCHAR2(30),
        NMSUBGRPROD        VARCHAR2(100),
        CDSUBGRPROD        VARCHAR2(10),
        CDGRUPROD          VARCHAR2(10),
        VRCONSUMO          FLOAT(126),
        CDFILIAL           VARCHAR2(04),
        QTESTOQUE          FLOAT(126),
        NRORG              NUMBER,
        DTPROCESSA         DATE)
/

CREATE TYPE CONSUMOD_ROP_TABLE AS
TABLE OF CONSUMOD_ROP_TYPE
/

CREATE OR REPLACE FUNCTION CONSUMOD_ROP
 (P_CDFILIAL  IN VARCHAR2,
  P_DATAINI   IN DATE,
  P_DATAFIN   IN DATE,
  P_MOBILE    IN VARCHAR2 DEFAULT 'N',
  P_OPERADOR  IN VARCHAR2 DEFAULT '') RETURN CONSUMOD_ROP_TABLE PIPELINED
IS
  V_CDFILIAL         VARCHAR2(04);
  V_NRORG            NUMBER;      
  V_AUX              FLOAT;
  V_INVFINAL         FLOAT;
  V_INVFINALTOT      FLOAT;
  V_VRCONSUMOTOT     FLOAT;
  V_SEQ              NUMBER;  
  V_IDNRDIASUTEST    VARCHAR2(01);
  V_IDRETIRADAROP    VARCHAR2(01);
  V_ENTRADACOMPRAS   FLOAT;
  V_OUTRASENTRADAS   FLOAT;
  V_IMPENTCOMP       FLOAT;
  V_IMPENTOUTR       FLOAT;
  V_IMPSAIDEV        FLOAT;
  V_IMPSAIOUTR       FLOAT;
  V_SAIDASDEV        FLOAT;
  V_OUTRASSAIDAS     FLOAT;
  V_VRTOTINVINIC     FLOAT;
  V_VRTOTINVFINAL    FLOAT;
  V_VRTOTRETIP       FLOAT;
  V_VRTOTRETIE       FLOAT;
  V_VRCONSUMO        FLOAT;
  V_OPERSEMCUSTOTOT  FLOAT;
  V_OPERSEMCUSTO     FLOAT;
  V_DATAFINAL        DATE;
  V_DIASREALIZADOS   NUMBER;
  V_NMFILIAL         VARCHAR2(100);

  OUT_RECORD CONSUMOD_ROP_TYPE := CONSUMOD_ROP_TYPE(NULL, NULL, NULL, NULL, NULL, NULL,
                                                    NULL, NULL, NULL, NULL);
  
  FUNCTION ULT_APURACAO(
    P_CDFILIAL    FILIAL.CDFILIAL%TYPE,
    P_DATAREF     IN DATE,
    P_DATAPROCMOD IN DATE) RETURN DATE
  IS
    V_DATA  DATE;
  BEGIN
    V_DATA := TRUNC(CASE WHEN NVL(P_DATAREF, SYSDATE-1) < (P_DATAPROCMOD)
                         THEN NVL(P_DATAREF, SYSDATE-1)
                         ELSE (P_DATAPROCMOD)
                    END);
    SELECT TRUNC(MAX(DTLANCROP))
      INTO V_DATA
      FROM LANCROPFILIAL
     WHERE CDFILIAL = P_CDFILIAL
       AND IDTPLANCROP = 'IF'
       AND TRUNC(DTLANCROP) <= V_DATA;
                    
    RETURN V_DATA;
  END;
BEGIN
   V_INVFINALTOT  := 0;
   V_VRCONSUMOTOT := 0;
   V_OPERSEMCUSTOTOT := 0;

   IF P_MOBILE = 'N' THEN
       V_CDFILIAL := P_CDFILIAL;
       FOR C_FILIAL IN (SELECT TRUNC(D.DTPROCESSA) DTPROCESSA, TRUNC(NVL(P_DATAINI,FIRST_DAY(D.DTPROCESSA-1))) DTINICOMP, D.NMFILIAL, D.CDFILIAL, D.CDGRUPPROD,
                               D.IDPERCONSROP, D.NRMESCONSROP, D.IDNRDIASUTEST
                          FROM (SELECT PF.CDFILIAL, CASE WHEN TO_CHAR(CASE WHEN NVL(G.IDUTILDTPROC,'N') = 'S' THEN NVL(PF.DTPROCESSA,SYSDATE) ELSE SYSDATE END,'DD') = '01'
                                                         THEN CASE WHEN NVL(G.IDUTILDTPROC,'N') = 'S' THEN NVL(PF.DTPROCESSA,SYSDATE) ELSE SYSDATE END
                                                         ELSE CASE WHEN NVL(G.IDUTILDTPROC,'N') = 'S' THEN NVL(PF.DTPROCESSA,SYSDATE) ELSE SYSDATE END-1
                                                    END AS DTPROCESSA, TM.DT, FI.NMFILIAL, PR.CDGRUPPROD,
                                       NVL(PR.IDPERCONSROP,'A') IDPERCONSROP, NVL(PR.NRMESCONSROP, 0) NRMESCONSROP, NVL(PR.IDNRDIASUTEST,'N') IDNRDIASUTEST
                                  FROM PARAMFILIAL PF CROSS JOIN PARAMGERAL G
                                                      CROSS JOIN (SELECT CASE WHEN TO_CHAR(SYSDATE-1,'MMYYYY') = TO_CHAR(SYSDATE, 'MMYYYY')
                                                                              THEN SYSDATE-1
                                                                              ELSE NULL
                                                                         END DT
                                                                    FROM DUAL) TM
                                                      INNER JOIN (SELECT F.CDFILIAL, F.NMFILIAL
                                                                    FROM FILIAL F
                                                                   WHERE F.IDCNTRRESC = 'N'
                                                                     AND ((F.CDFILIAL = P_CDFILIAL) OR ('T' = P_CDFILIAL))
                                                                   GROUP BY F.CDFILIAL, F.NMFILIAL) FI ON PF.CDFILIAL = FI.CDFILIAL
                                                      INNER JOIN PARFILIALROP PR ON PR.CDFILIAL = TIPOPARAM_DRO(FI.CDFILIAL)) D) LOOP
          
          V_DATAFINAL := ULT_APURACAO(C_FILIAL.CDFILIAL, P_DATAFIN, C_FILIAL.DTPROCESSA);
         
          V_DIASREALIZADOS := RETORNA_DIAS_REALIZADOS(C_FILIAL.CDFILIAL, NVL(P_DATAINI,FIRST_DAY(C_FILIAL.DTPROCESSA)), V_DATAFINAL);
          
          SELECT MAX(P.IDRETIRADAROP)
            INTO V_IDRETIRADAROP
            FROM PARFILIALROP P
           WHERE P.CDFILIAL IN (SELECT DISTINCT TIPOPARAM_DRO(FL.CDFILIAL)
                                  FROM FILGRUPFILI FG, FILIAL FL
                                 WHERE FL.CDFILIAL = C_FILIAL.CDFILIAL
                                   AND FL.CDFILIAL = FG.CDFILIAL);
          
          IF V_IDRETIRADAROP IS NOT NULL THEN                
              V_INVFINALTOT  := 0;
              V_INVFINAL := 0;
              V_VRCONSUMOTOT := 0;
                                                                       
              FOR C_SUBGRUP IN (SELECT S.CDSUBGRPROD, S.CDGRUPPROD, S.NMSUBGRPROD
                                  FROM GRUPPROD G INNER JOIN SUBGRUPPROD S ON G.CDGRUPPROD = S.CDGRUPPROD
                                 WHERE G.CDGRUPPROD = C_FILIAL.CDGRUPPROD) LOOP
                                 
                 V_ENTRADACOMPRAS := 0;
                 V_OUTRASENTRADAS := 0;
                 V_IMPENTCOMP := 0;
                 V_IMPENTOUTR := 0;
                 V_IMPSAIDEV := 0;
                 V_IMPSAIOUTR := 0;
                 V_SAIDASDEV := 0;
                 V_OUTRASSAIDAS := 0;
                 V_VRTOTINVINIC := 0;
                 V_VRTOTINVFINAL := 0;
                 V_VRTOTRETIP := 0;
                 V_VRTOTRETIE := 0;
                 V_VRCONSUMO := 0;
                 V_OPERSEMCUSTO := 0;
                 
                 V_SEQ := V_SEQ + 1;
                
                 /* Entradas, Outras Entradas */ 
                 SELECT SUM(NVL(DECODE(IDTIPOENTRADA, 'A',VRTOTCONSENTR,0),0)),
                        SUM(NVL(DECODE(IDTIPOENTRADA, 'B',VRTOTCONSENTR,0),0))
                   INTO V_ENTRADACOMPRAS, V_OUTRASENTRADAS
                   FROM (SELECT NVL((SELECT 'A'
                                       FROM TIPOOPERACROP T
                                      WHERE A2.CDTIPOOPERROP = T.CDTIPOOPER
                                        AND CDFILIAL = TIPOPARAM_DRO(A2.CDFILIAL)
                                        AND IDENTRSAIDOP = 'E'
                                        AND IDTPOPERACROP = 'CC'),'B') IDTIPOENTRADA, A2.VRTOTCONSENTR,A2.CDFILIAL, CDGRUPPRODROP, CDSUBGRPRODROP
                           FROM (SELECT SUM(VRITEMLANCROP) VRTOTCONSENTR,L.CDFILIAL,L.CDTIPOOPERROP, CDGRUPPRODROP, CDSUBGRPRODROP
                                   FROM LANCROPFILIAL L, ITLANCROPFILIAL IT, TIPOOPERAC T
                                  WHERE L.CDFILIAL IN (SELECT DISTINCT FL.CDFILIAL
                                                         FROM FILGRUPFILI FG, FILIAL FL
                                                        WHERE (FL.CDFILIAL = C_FILIAL.CDFILIAL) 
                                                          AND (FL.CDFILIAL = FG.CDFILIAL))
                                    AND L.IDTPLANCROP  = 'CC'
                                    AND TRUNC(L.DTLANCROP) BETWEEN C_FILIAL.DTINICOMP AND V_DATAFINAL
                                    AND L.CDFILIAL = IT.CDFILIAL
                                    AND L.NRROPFILIAL = IT.NRROPFILIAL
                                    AND L.NRLANCROP = IT.NRLANCROP
                                    AND L.CDTIPOOPERROP =  T.CDTIPOOPER
                                    AND T.IDENTRSAIDOP = 'E'
                                    AND IT.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                                    AND IT.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD
                                  GROUP BY L.CDFILIAL,L.CDTIPOOPERROP, CDGRUPPRODROP, CDSUBGRPRODROP) A2) A1;
                                  
                 /* Impostos */
                 SELECT SUM(IMPENTCOMP), SUM(IMPENTOUTR), SUM(IMPSAIDEV), SUM(IMPSAIOUTR)
                   INTO V_IMPENTCOMP, V_IMPENTOUTR, V_IMPSAIDEV, V_IMPSAIOUTR
                   FROM (SELECT CDFILIAL, NVL(DECODE(IDTIPOENTRADA, 'A',IMPOSTOENT,0),0) AS IMPENTCOMP, 
                                NVL(DECODE(IDTIPOENTRADA, 'B',IMPOSTOENT,0),0) AS IMPENTOUTR,
                                0 AS IMPSAIDEV, 0 AS IMPSAIOUTR,
                                CDGRUPPRODROP, CDSUBGRPRODROP
                           FROM (SELECT A2.CDFILIAL, NVL((SELECT 'A'
                                                            FROM TIPOOPERACROP T
                                                           WHERE A2.CDTIPOOPERROP = T.CDTIPOOPER
                                                             AND CDFILIAL = TIPOPARAM_DRO(A2.CDFILIAL)
                                                             AND IDENTRSAIDOP = 'E'
                                                             AND IDTPOPERACROP = 'CC'),'B') IDTIPOENTRADA,
                                        DECODE(A2.IDTRATIMPROP, 'D', NVL(VRTOTRELITROP,0) * -1, DECODE(A2.IDTRATIMPROP,'V',0,NVL(VRTOTRELITROP,0))) AS IMPOSTOENT,
                                        CDGRUPPRODROP, CDSUBGRPRODROP
                                   FROM (SELECT L.CDFILIAL, CDITRELITROP, L.CDTIPOOPERROP,
                                                SUM(DECODE(L.IDTPLANCROP, 'CC',NVL(VRITRELITROP,0),0)) VRTOTRELITROP, R.IDTRATIMPROP,
                                                CDGRUPPRODROP, CDSUBGRPRODROP
                                           FROM LANCROPFILIAL L, TIPOOPERAC T, ITLANCROPFILIAL IT, ITRELACITROP IR, IMPOSTOROP R
                                          WHERE (L.CDFILIAL IN (SELECT DISTINCT FL.CDFILIAL
                                                                  FROM FILGRUPFILI FG, FILIAL FL
                                                                 WHERE FL.CDFILIAL = C_FILIAL.CDFILIAL
                                                                   AND FL.CDFILIAL = FG.CDFILIAL)) 
                                            AND L.IDTPLANCROP  = 'CC'
                                            AND TRUNC(L.DTLANCROP) BETWEEN C_FILIAL.DTINICOMP AND V_DATAFINAL
                                            AND L.CDFILIAL = IT.CDFILIAL
                                            AND L.NRROPFILIAL = IT.NRROPFILIAL
                                            AND L.NRLANCROP = IT.NRLANCROP
                                            AND IT.CDFILIAL = IR.CDFILIAL
                                            AND IT.NRROPFILIAL = IR.NRROPFILIAL
                                            AND IT.NRLANCROP = IR.NRLANCROP
                                            AND IT.NRITLANCROP = IR.NRITLANCROP
                                            AND L.CDTIPOOPERROP =  T.CDTIPOOPER
                                            AND R.CDFILIAL = L.CDFILIAL
                                            AND R.IDTPIMPOSTOROP = L.IDTPLANCROP
                                            AND R.CDIMPOSTO = CDITRELITROP
                                            AND T.IDENTRSAIDOP  = 'E'
                                            AND IT.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                                            AND IT.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD
                                          GROUP BY L.CDFILIAL, CDITRELITROP, L.CDTIPOOPERROP, R.IDTRATIMPROP, CDGRUPPRODROP, CDSUBGRPRODROP) A2) IMP
        
                                          UNION ALL
        
                                         SELECT CDFILIAL, 0 AS IMPENTCOMP, 0 AS IMPENTOUTR, NVL(DECODE(IDTIPOENTRADA, 'A',IMPOSTOSAI,0),0) AS IMPSAIDEV, 
                                                NVL(DECODE(IDTIPOENTRADA, 'B',IMPOSTOSAI,0),0) AS IMPSAIOUTR, CDGRUPPRODROP, CDSUBGRPRODROP
                                           FROM (SELECT A2.CDFILIAL, NVL((SELECT 'A'
                                                                            FROM TIPOOPERACROP T
                                                                           WHERE A2.CDTIPOOPERROP = T.CDTIPOOPER
                                                                             AND CDFILIAL = TIPOPARAM_DRO(A2.CDFILIAL)
                                                                             AND IDENTRSAIDOP = 'E'
                                                                             AND IDTPOPERACROP = 'CC'),'B') IDTIPOENTRADA,
                                                        DECODE(A2.IDTRATIMPROP, 'D', NVL(VRTOTRELITROP,0) * -1, DECODE(A2.IDTRATIMPROP,'V',0,NVL(VRTOTRELITROP,0))) AS IMPOSTOSAI,
                                                        CDGRUPPRODROP, CDSUBGRPRODROP
                                                   FROM (SELECT L.CDFILIAL, CDITRELITROP, L.CDTIPOOPERROP,
                                                                SUM(DECODE(L.IDTPLANCROP, 'CS',NVL(VRITRELITROP,0),0)) VRTOTRELITROP, R.IDTRATIMPROP,
                                                                CDGRUPPRODROP, CDSUBGRPRODROP
                                                           FROM LANCROPFILIAL L, TIPOOPERAC T, ITLANCROPFILIAL IT, ITRELACITROP IR, IMPOSTOROP R
                                                          WHERE (L.CDFILIAL IN (SELECT DISTINCT FL.CDFILIAL
                                                                                  FROM FILGRUPFILI FG, FILIAL FL
                                                                                 WHERE FL.CDFILIAL = C_FILIAL.CDFILIAL
                                                                                   AND FL.CDFILIAL = FG.CDFILIAL)) 
                                                            AND L.IDTPLANCROP  = 'CS'
                                                            AND TRUNC(L.DTLANCROP)  BETWEEN C_FILIAL.DTINICOMP AND V_DATAFINAL
                                                            AND L.CDFILIAL = IT.CDFILIAL
                                                            AND L.NRROPFILIAL = IT.NRROPFILIAL
                                                            AND L.NRLANCROP = IT.NRLANCROP
                                                            AND IT.CDFILIAL = IR.CDFILIAL
                                                            AND IT.NRROPFILIAL = IR.NRROPFILIAL
                                                            AND IT.NRLANCROP = IR.NRLANCROP
                                                            AND IT.NRITLANCROP = IR.NRITLANCROP
                                                            AND L.CDTIPOOPERROP = T.CDTIPOOPER
                                                            AND R.CDFILIAL = L.CDFILIAL
                                                            AND R.IDTPIMPOSTOROP = L.IDTPLANCROP
                                                            AND R.CDIMPOSTO = CDITRELITROP
                                                            AND T.IDENTRSAIDOP = 'S'                                                    
                                                            AND IT.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                                                            AND IT.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD
                                                          GROUP BY L.CDFILIAL, CDITRELITROP, L.CDTIPOOPERROP, R.IDTRATIMPROP, CDGRUPPRODROP, CDSUBGRPRODROP) A2) IMP);
        
                 /* Sadas, Outras Sadas */
                 SELECT SUM(NVL(DECODE(IDTIPOSAIDA, 'A',VRTOTCONSSAI,0),0)),
                        SUM(NVL(DECODE(IDTIPOSAIDA, 'B',VRTOTCONSSAI,0),0))
                   INTO V_SAIDASDEV, V_OUTRASSAIDAS
                   FROM (SELECT NVL((SELECT 'A'
                                       FROM TIPOOPERACROP T
                                      WHERE A2.CDTIPOOPERROP = T.CDTIPOOPER
                                        AND CDFILIAL = TIPOPARAM_DRO(A2.CDFILIAL)
                                        AND IDENTRSAIDOP = 'S'
                                        AND IDTPOPERACROP = 'CS'),'B') IDTIPOSAIDA, A2.VRTOTCONSSAI, A2.CDFILIAL, CDGRUPPRODROP, CDSUBGRPRODROP
                           FROM (SELECT SUM(VRITEMLANCROP) VRTOTCONSSAI,L.CDFILIAL,L.CDTIPOOPERROP, CDGRUPPRODROP, CDSUBGRPRODROP
                                   FROM LANCROPFILIAL L, ITLANCROPFILIAL IT, TIPOOPERAC T
                                  WHERE L.CDFILIAL IN (SELECT DISTINCT FL.CDFILIAL
                                                         FROM FILGRUPFILI FG, FILIAL FL
                                                        WHERE FL.CDFILIAL = C_FILIAL.CDFILIAL
                                                          AND FL.CDFILIAL = FG.CDFILIAL)
                                    AND L.IDTPLANCROP = 'CS'
                                    AND TRUNC(L.DTLANCROP) BETWEEN C_FILIAL.DTINICOMP AND V_DATAFINAL
                                    AND L.CDFILIAL = IT.CDFILIAL
                                    AND L.NRROPFILIAL = IT.NRROPFILIAL
                                    AND L.NRLANCROP = IT.NRLANCROP
                                    AND L.CDTIPOOPERROP = T.CDTIPOOPER
                                    AND T.IDENTRSAIDOP = 'S'                                 
                                    AND IT.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                                    AND IT.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD
                                  GROUP BY L.CDFILIAL, L.CDTIPOOPERROP, CDGRUPPRODROP, CDSUBGRPRODROP) A2) A1;
                                                    
                 SELECT SUM(DECODE(IDTPLANCROP,'II',VRITEMLANCROP,0)),
                        SUM(DECODE(IDTPLANCROP,'IF',VRITEMLANCROP,0))
                   INTO V_VRTOTINVINIC, V_VRTOTINVFINAL
                   FROM LANCROPFILIAL L, ITLANCROPFILIAL IT
                  WHERE L.CDFILIAL IN (SELECT DISTINCT FL.CDFILIAL
                                         FROM FILGRUPFILI FG, FILIAL FL
                                        WHERE FL.CDFILIAL = C_FILIAL.CDFILIAL
                                          AND FL.CDFILIAL = FG.CDFILIAL)
                    AND ((TRUNC(L.DTLANCROP) = C_FILIAL.DTINICOMP AND IDTPLANCROP = 'II') OR (TRUNC(L.DTLANCROP) = V_DATAFINAL AND IDTPLANCROP = 'IF'))
                    AND L.CDFILIAL = IT.CDFILIAL
                    AND L.NRROPFILIAL = IT.NRROPFILIAL
                    AND L.NRLANCROP = IT.NRLANCROP
                    AND IT.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                    AND IT.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD;
                    
                 SELECT SUM(DECODE(IDTPLANCROP,'RP',VRITEMLANCROP,0)),
                        SUM(DECODE(IDTPLANCROP,'RE',VRITEMLANCROP,0))
                   INTO V_VRTOTRETIP, V_VRTOTRETIE
                   FROM ITLANCROPFILIAL IT, LANCROPFILIAL L
                  WHERE L.CDFILIAL IN (SELECT DISTINCT FL.CDFILIAL
                                         FROM FILGRUPFILI FG, FILIAL FL
                                        WHERE FL.CDFILIAL = C_FILIAL.CDFILIAL
                                          AND FL.CDFILIAL = FG.CDFILIAL)
                    AND L.IDTPLANCROP IN ('RE','RP')
                    AND TRUNC(L.DTLANCROP) BETWEEN C_FILIAL.DTINICOMP AND V_DATAFINAL
                    AND L.CDFILIAL = IT.CDFILIAL
                    AND L.NRROPFILIAL = IT.NRROPFILIAL
                    AND L.NRLANCROP = IT.NRLANCROP
                    AND IT.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                    AND IT.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD;
                    
                SELECT SUM(CASE WHEN O.IDENTRSAID = 'E' THEN I.VRITEMLANCROP*(-1)
                                ELSE I.VRITEMLANCROP
                           END) VRITEMLANCROP
                  INTO V_OPERSEMCUSTO
                  FROM LANCROPFILIAL L INNER JOIN ITLANCROPFILIAL I ON L.CDFILIAL = I.CDFILIAL
                                                                   AND L.NRROPFILIAL = I.NRROPFILIAL
                                                                   AND L.NRLANCROP = I.NRLANCROP
                                       INNER JOIN OPERSEMCUSTOROP O ON L.CDTIPOOPERROP = O.CDTIPOOPER                                     
                 WHERE L.IDTPLANCROP IN ('CS','CC')
                   AND TRUNC(L.DTLANCROP) BETWEEN C_FILIAL.DTINICOMP AND V_DATAFINAL
                   AND L.CDFILIAL = C_FILIAL.CDFILIAL
                   AND ((O.IDENTRSAID = 'S' AND L.IDTPLANCROP = 'CS') OR (O.IDENTRSAID = 'E' AND L.IDTPLANCROP = 'CC'))
                   AND I.CDSUBGRPRODROP = C_SUBGRUP.CDSUBGRPROD
                   AND I.CDGRUPPRODROP = C_SUBGRUP.CDGRUPPROD;
    
                V_VRCONSUMO := CASE V_IDRETIRADAROP WHEN 'C' THEN NVL(V_ENTRADACOMPRAS,0) + NVL(V_IMPENTCOMP,0) + NVL(V_OUTRASENTRADAS,0) + NVL(V_IMPENTOUTR,0)
                                                    WHEN 'E' THEN NVL(V_VRTOTRETIE,0)
                                                    WHEN 'P' THEN NVL(V_VRTOTRETIP,0)
                                                    WHEN 'A' THEN NVL(V_VRTOTRETIP,0) + NVL(V_VRTOTRETIE,0)
                                                    ELSE (NVL(V_VRTOTINVINIC,0) + NVL(V_ENTRADACOMPRAS,0) + NVL(V_IMPENTCOMP,0) + NVL(V_OUTRASENTRADAS,0) + NVL(V_IMPENTOUTR,0) -
                                                         (NVL(V_SAIDASDEV,0) + NVL(V_IMPSAIDEV,0)) - (NVL(V_OUTRASSAIDAS,0) + NVL(V_IMPSAIOUTR,0)) - NVL(V_VRTOTINVFINAL,0) +
                                                          NVL(V_OPERSEMCUSTO,0))
                               END;
                    
                V_INVFINALTOT     := NVL(V_INVFINALTOT,0) +  NVL(V_VRTOTINVFINAL,0);
                V_VRCONSUMOTOT    := NVL(V_VRCONSUMOTOT,0) + NVL(V_VRCONSUMO,0);
                V_OPERSEMCUSTOTOT := NVL(V_OPERSEMCUSTOTOT,0) + NVL(V_OPERSEMCUSTO,0);
                
                OUT_RECORD.NRCONSUMOD_ROP := V_SEQ;
                OUT_RECORD.NMFILIAL       := C_FILIAL.NMFILIAL;
                OUT_RECORD.NMSUBGRPROD    := C_SUBGRUP.NMSUBGRPROD;
                OUT_RECORD.CDSUBGRPROD    := C_SUBGRUP.CDSUBGRPROD;
                OUT_RECORD.CDGRUPROD      := C_SUBGRUP.CDGRUPPROD;
                OUT_RECORD.VRCONSUMO      := V_VRCONSUMO;
                IF V_VRCONSUMO <> 0 THEN
                   OUT_RECORD.QTESTOQUE   := NVL(ROUND(ROUND(V_VRTOTINVFINAL,2)/(CASE WHEN ROUND(V_VRCONSUMO,2)=0 THEN 1 ELSE ROUND(V_VRCONSUMO,2) END /
                                                   CASE WHEN C_FILIAL.IDNRDIASUTEST = 'S'
                                                        THEN CASE WHEN V_DIASREALIZADOS = 0 THEN 1 ELSE V_DIASREALIZADOS END
                                                        ELSE V_DATAFINAL - NVL(P_DATAINI,FIRST_DAY(C_FILIAL.DTPROCESSA)) --30
                                                   END),2),0);
                ELSE   
                   OUT_RECORD.QTESTOQUE := 0;
                END IF;
                OUT_RECORD.CDFILIAL       := C_FILIAL.CDFILIAL;
                OUT_RECORD.NRORG          := V_NRORG;
                PIPE ROW (OUT_RECORD);
             END LOOP;
             OUT_RECORD.NRCONSUMOD_ROP := V_SEQ;
             OUT_RECORD.NMFILIAL       := C_FILIAL.NMFILIAL;
             OUT_RECORD.NMSUBGRPROD    := 'TOTAL';
             OUT_RECORD.CDSUBGRPROD    := '9999999999';
             OUT_RECORD.CDGRUPROD      := C_FILIAL.CDGRUPPROD;
             OUT_RECORD.VRCONSUMO      := V_VRCONSUMOTOT;
             IF V_VRCONSUMOTOT <> 0 THEN
                OUT_RECORD.QTESTOQUE   := NVL(ROUND(ROUND(V_INVFINALTOT,2)/(CASE WHEN ROUND(V_VRCONSUMOTOT,2)=0 THEN 1 ELSE ROUND(V_VRCONSUMOTOT,2) END /
                                             CASE WHEN C_FILIAL.IDNRDIASUTEST = 'S'
                                                  THEN CASE WHEN V_DIASREALIZADOS = 0 THEN 1 ELSE V_DIASREALIZADOS END
                                                  ELSE V_DATAFINAL - NVL(P_DATAINI,FIRST_DAY(C_FILIAL.DTPROCESSA)) --30
                                             END),2),0);
             ELSE
                OUT_RECORD.QTESTOQUE   := 0;
             END IF;
             OUT_RECORD.CDFILIAL       := C_FILIAL.CDFILIAL;
             OUT_RECORD.NRORG          := V_NRORG;
             PIPE ROW (OUT_RECORD);
          END IF;
       END LOOP;
    ELSE
       V_SEQ := 0;
       
       FOR C_UNIDADES IN (SELECT F.CDFILIAL, F.NMFILIAL, P_DATAINI DATAINIINF, CASE WHEN NVL(G.IDUTILDTPROC,'N') = 'S' 
                                                                                    THEN NVL(PF.DTPROCESSA,SYSDATE) 
                                                                                    ELSE SYSDATE 
                                                                               END-1 DATAPROCM1,
                                 P_DATAFIN DATAFININF, F.NRORG, PR.IDNRDIASUTEST
                            FROM FILIAL F, PARAMFILIAL PF, PARAMGERAL G, PARFILIALROP PR, FILIOPER FO
                           WHERE F.CDFILIAL = PF.CDFILIAL
                             AND F.IDCNTRRESC = 'N'
                             AND F.CDFILIAL = FO.CDFILIAL
                             AND ((F.CDFILIAL = P_CDFILIAL) OR (P_CDFILIAL = 'T'))
                             AND FO.CDOPERADOR = P_OPERADOR
                             AND PR.CDFILIAL = TIPOPARAM_DRO(PF.CDFILIAL)
                           ORDER BY F.CDFILIAL) LOOP
                             
          V_INVFINALTOT    := 0;
          V_VRCONSUMOTOT   := 0;
                             
          V_DIASREALIZADOS := RETORNA_DIAS_REALIZADOS(C_UNIDADES.CDFILIAL, TRUNC(NVL(C_UNIDADES.DATAINIINF,C_UNIDADES.DATAPROCM1),'MM'), CASE WHEN NVL(C_UNIDADES.DATAFININF,C_UNIDADES.DATAPROCM1) < C_UNIDADES.DATAPROCM1
                                                                                                                                              THEN C_UNIDADES.DATAFININF
                                                                                                                                              ELSE C_UNIDADES.DATAPROCM1
                                                                                                                                         END);
          V_DIASREALIZADOS := CASE WHEN C_UNIDADES.IDNRDIASUTEST = 'S'
                                   THEN CASE WHEN V_DIASREALIZADOS = 0
                                             THEN 1
                                             ELSE V_DIASREALIZADOS
                                        END
                                   ELSE C_UNIDADES.DATAPROCM1 - NVL(C_UNIDADES.DATAINIINF, FIRST_DAY(C_UNIDADES.DATAPROCM1))
                              END;
                                                                                                                                         
          FOR C_DIASEST IN (SELECT MAX(DM.DTRESUMO) DTRESUMO, SUM(DM.VRCONSUMO) VRCONSUMO,
                                   SUM(CASE WHEN DTRESUMO = (SELECT MAX(D.DTRESUMO) DTRESUMO
                                                               FROM DM_RESULT_OPERACIONAL D
                                                              WHERE D.CDFILIAL = DM.CDFILIAL
                                                                AND D.IDTPLANCROP = DM.IDTPLANCROP
                                                                AND D.CDGRUPPROD = DM.CDGRUPPROD
                                                                AND D.CDSUBGRUPPROD = DM.CDSUBGRUPPROD
                                                                AND D.DTRESUMO BETWEEN NVL(C_UNIDADES.DATAINIINF, TRUNC(C_UNIDADES.DATAPROCM1,'MM'))
                                                                                   AND CASE WHEN NVL(C_UNIDADES.DATAFININF,C_UNIDADES.DATAPROCM1) < C_UNIDADES.DATAPROCM1
                                                                                            THEN C_UNIDADES.DATAFININF
                                                                                            ELSE C_UNIDADES.DATAPROCM1
                                                                                       END)
                                         THEN NVL(DM.VRESTOQFINAL, 0)
                                         ELSE 0
                                    END) VRESTOQFINAL, DM.CDSUBGRUPPROD, DM.NMSUBGRUPPROD
                              FROM DM_RESULT_OPERACIONAL DM
                             WHERE DM.IDTPLANCROP = 'CONSUMO'
                               AND DM.CDFILIAL = C_UNIDADES.CDFILIAL
                               AND DM.DTRESUMO BETWEEN NVL(C_UNIDADES.DATAINIINF, TRUNC(C_UNIDADES.DATAPROCM1,'MM'))
                                                   AND CASE WHEN NVL(C_UNIDADES.DATAFININF,C_UNIDADES.DATAPROCM1) < C_UNIDADES.DATAPROCM1
                                                            THEN C_UNIDADES.DATAFININF
                                                            ELSE C_UNIDADES.DATAPROCM1
                                                       END
                             GROUP BY DM.CDSUBGRUPPROD, DM.NMSUBGRUPPROD
                             ORDER BY DM.CDSUBGRUPPROD) LOOP
                             
             V_INVFINALTOT     := NVL(V_INVFINALTOT,0) +  NVL(C_DIASEST.VRESTOQFINAL,0);
             V_VRCONSUMOTOT    := NVL(V_VRCONSUMOTOT,0) + NVL(C_DIASEST.VRCONSUMO,0);
             V_SEQ := V_SEQ + 1;
             
             OUT_RECORD.NRCONSUMOD_ROP := V_SEQ;
             OUT_RECORD.NMFILIAL       := C_UNIDADES.NMFILIAL;
             OUT_RECORD.NMSUBGRPROD    := C_DIASEST.NMSUBGRUPPROD;
             OUT_RECORD.CDSUBGRPROD    := C_DIASEST.CDSUBGRUPPROD;
             OUT_RECORD.CDGRUPROD      := NULL;
             OUT_RECORD.VRCONSUMO      := C_DIASEST.VRCONSUMO;
             IF (C_DIASEST.VRCONSUMO <> 0) AND (V_DIASREALIZADOS <> 0) THEN
                OUT_RECORD.QTESTOQUE   := C_DIASEST.VRESTOQFINAL/(C_DIASEST.VRCONSUMO/V_DIASREALIZADOS);
             ELSE
                OUT_RECORD.QTESTOQUE   := 0;
             END IF;
             OUT_RECORD.CDFILIAL       := C_UNIDADES.CDFILIAL;
             OUT_RECORD.NRORG          := C_UNIDADES.NRORG;
             OUT_RECORD.DTPROCESSA     := C_UNIDADES.DATAPROCM1+1;
             PIPE ROW (OUT_RECORD);
          END LOOP;
          V_SEQ := V_SEQ + 1;
        
          OUT_RECORD.NRCONSUMOD_ROP := V_SEQ;
          OUT_RECORD.NMFILIAL       := C_UNIDADES.NMFILIAL;
          OUT_RECORD.NMSUBGRPROD    := 'Valor Total';
          OUT_RECORD.CDSUBGRPROD    := 'TTTTTTTTTT';
          OUT_RECORD.CDGRUPROD      := NULL;
          OUT_RECORD.VRCONSUMO      := V_VRCONSUMOTOT;
          IF (V_VRCONSUMOTOT <> 0) AND (V_DIASREALIZADOS <> 0) THEN
             OUT_RECORD.QTESTOQUE   := V_INVFINALTOT/(V_VRCONSUMOTOT/V_DIASREALIZADOS);
          ELSE
             OUT_RECORD.QTESTOQUE   := 0;
          END IF;
          OUT_RECORD.CDFILIAL       := C_UNIDADES.CDFILIAL;
          OUT_RECORD.NRORG          := C_UNIDADES.NRORG;
          OUT_RECORD.DTPROCESSA     := C_UNIDADES.DATAPROCM1+1;
          PIPE ROW (OUT_RECORD);
       END LOOP;
    END IF;
END;
/