CREATE OR REPLACE PROCEDURE APURAC_CONSUMOROP(
   P_CDFILIAL       FILIAL.CDFILIAL%TYPE,
   P_FILIALPAR      FILIAL.CDFILIAL%TYPE,
   P_DTINICIAL      IN DATE,
   P_DTFINAL        IN DATE,
   P_CDFILISUBGR    IN VARCHAR,
   P_NRROPFILIAL    IN VARCHAR)
IS
   V_NRSEQFATUROP    VARCHAR2(10);
   V_VRCUSTLOGLAN    FLOAT;
   V_VRSEGULOGLAN    FLOAT;
   V_CDFILCENTPME    VARCHAR2(04);
   V_NRSEQCONSUROP   NUMBER;
   V_NRSEQDEDUROP    NUMBER;
   V_NRTIPOCUSTO     NUMBER;
   V_CDFILISUBGR     VARCHAR2(04);
BEGIN
   BEGIN
     SELECT CASE WHEN IDFILCENTPME = 'S'
                 THEN CDFILIAL
                 ELSE CDFILCENTPME
            END
       INTO V_CDFILCENTPME
       FROM FILIAL
      WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       V_CDFILCENTPME := NULL;
   END;

   BEGIN
      SELECT TO_NUMBER(NVL(CDTIPCUSTEST, '3'))
        INTO V_NRTIPOCUSTO
        FROM PARAMFILIAL P
       WHERE P.CDFILIAL = P_CDFILIAL;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       V_NRTIPOCUSTO := '3';
   END;
   
   FOR C_PARFILIALROP IN (SELECT P.IDNFSAIDAROP, P.CDGRUPPROD CDGRUPPROD, P.IDCOMPRAROP, NVL(P.IDTPAPURACAO,'3') IDTPAPURACAO
                            FROM PARFILIALROP P
                           WHERE P.CDFILIAL = P_FILIALPAR
                             AND (NVL(P.IDTPAPURACAO,'3') <> '2')
                             AND P.CDGRUPPROD IS NOT NULL
                           UNION
                          SELECT P.IDNFSAIDAROP, P.CDGRUPPRODDRL CDGRUPPROD, P.IDCOMPRAROP, NVL(P.IDTPAPURACAO,'3') IDTPAPURACAO
                            FROM PARFILIALROP P
                           WHERE P.CDFILIAL = P_FILIALPAR
                             AND (NVL(P.IDTPAPURACAO,'3') <> '1')
                             AND P.CDGRUPPRODDRL IS NOT NULL) LOOP
                             
      SELECT NVL(MAX(X.CDFILIAL), MAX(X.CDFILIPADRAO)) CDFILISUBGR
        INTO V_CDFILISUBGR
        FROM (SELECT DISTINCT CDFILISUBGR AS CDFILIAL, '' AS CDFILIPADRAO
                FROM PRSUBGRPROD
               WHERE CDGRUPPROD  = C_PARFILIALROP.CDGRUPPROD
                 AND CDFILISUBGR = P_CDFILIAL
               UNION ALL
              SELECT DISTINCT '' AS CDFILIAL, CDFILISUBGR AS CDFILIPADRAO
                FROM PRSUBGRPROD
               WHERE CDGRUPPROD  = C_PARFILIALROP.CDGRUPPROD
                 AND CDFILISUBGR = 'P') X;

      IF C_PARFILIALROP.IDCOMPRAROP = 'S' THEN
         FOR C_CONSCOMPRA IN (SELECT N.CDTIPOOPER, N.NRNOTAFISC, N.CDFILIAL, CDFILIRECE,
                                     N.NRLANCTONF, DTENTRSAID,   N.CDSERIE, N.CDFORNECED,
                                     N.IDSTATUS,   N.DTINIREFEFAT, N.DTFINREFEFAT, P.NUMERORE,
                                     L.NRLANCESTQ, N.NRSEQCAPADOC, F.NMFANTFORN, NVL(TP.IDNFESTCANC,'N') IDNFESTCANC,
                                     NVL(TP.IDUTCUSORIGEST,'N') IDUTCUSORIGEST, NVL(TP.IDOPERDEVONF, 'N') IDOPERDEVONF
                                FROM NOTAFISCAL N INNER JOIN TIPOOPERAC TP ON TP.IDENTRSAIDOP = N.IDENTRSAIDOP
                                                                          AND TP.CDTIPOOPER = N.CDTIPOOPER
                                                                          AND TP.CDTIPOOPER NOT IN ('11')
                                                  LEFT JOIN LANCTOESTOQ L ON N.CDFILIAL = L.CDFILIAL
                                                                         AND N.NRLANCTONF = L.NRLANCTONF
                                                  LEFT  JOIN FORNECEDOR F ON N.CDFORNECED = F.CDFORNECED
                                                  LEFT  JOIN (SELECT NRNUMERNF AS NUMERORE, CDFILIAL, DTNUMERNF AS DATARE
                                                                FROM NUMERORNF
                                                               WHERE CDFILIAL = P_CDFILIAL
                                                                 AND IDENTRSAID = 'E'
                                                                 AND TRUNC(DTNUMERNF) = P_DTINICIAL) P
                                                          ON N.CDFILIAL = P.CDFILIAL
                                                         AND N.DTENTRSAID = P.DATARE
                                                         AND N.NRSEQCAPADOC = P.NUMERORE
                               WHERE N.CDFILIRECE = P_CDFILIAL
                                 AND N.IDENTRSAIDOP = 'E'
                                 AND TRUNC(N.DTENTRSAID) = P_DTINICIAL
                                 AND N.IDSTATUS <> 'C'
                                 AND (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)
                               ORDER BY DTENTRSAID) LOOP

            FOR C_ITCONSCOMPRA IN(SELECT CDSUBGRPROD, CDGRUPPROD, SUM(VALORTOTAL) VALORTOTAL, SUM(QUANTIDADE) QUANTIDADE
                                    FROM (SELECT AN.CDSUBGRPROD, AN.CDGRUPPROD,
                                                 /* SUM(DECODE(C_CONSCOMPRA.CDTIPOOPER, '03',I.VRCONTITEM/H.VRFATOCONV,
                                                 CASE V_NRTIPOCUSTO WHEN 4 THEN I.VRBRUTITEM
                                                                    WHEN 43 THEN I.VRBRUTITEM
                                                                    ELSE I.VRCONTITEM 
                                                 END)) AS VALORTOTAL, */
                                                 SUM(DECODE(C_CONSCOMPRA.CDTIPOOPER, '03',I.VRBRUTITEM/H.VRFATOCONV,I.VRBRUTITEM)) AS VALORTOTAL,
                                                 SUM(I.QTITEMPROD) AS QUANTIDADE
                                            FROM ITEMPRODUTO I INNER JOIN (SELECT B.CDPRODUTO, A.CDGRUPPROD, A.CDSUBGRPROD, A.CDFILISUBGR
                                                                             FROM PRODUTO B INNER JOIN PRODUTO C
                                                                                                    ON B.CDPRODUTO = C.CDPRODUTO
                                                                                            INNER JOIN (SELECT P.CDARVPROD AS CDPRODUTO,
                                                                                                               N.CDGRUPPROD, N.CDSUBGRPROD,
                                                                                                               N.CDFILISUBGR
                                                                                                          FROM PRODUTO P INNER JOIN NVSUBGRPROD N
                                                                                                                                 ON P.CDPRODUTO = N.CDPRODUTO
                                                                                                         WHERE N.CDFILISUBGR = V_CDFILISUBGR
                                                                                                           AND N.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD) A
                                                                                                    ON C.CDARVPROD LIKE A.CDPRODUTO||'%'
                                                                            WHERE A.CDFILISUBGR = V_CDFILISUBGR
                                                                              AND A.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                                                            ORDER BY B.CDARVPROD) AN ON I.CDPRODUTO = AN.CDPRODUTO
                                                               INNER JOIN SUBGRPRODFIL SG ON AN.CDFILISUBGR = SG.CDFILISUBGR
                                                                                         AND AN.CDSUBGRPROD = SG.CDSUBGRPROD
                                                                                         AND AN.CDGRUPPROD = SG.CDGRUPPROD
                                                               INNER JOIN PRODUTO H ON I.CDPRODUTO = H.CDPRODUTO
                                                               --INNER JOIN PRODFILI PR ON H.CDPRODESTO = PR.CDPRODUTO
                                           WHERE I.CDFILIAL = C_CONSCOMPRA.CDFILIAL
                                             AND I.NRLANCTONF = C_CONSCOMPRA.NRLANCTONF
                                             AND SG.CDFILISUBGR = V_CDFILISUBGR
                                             AND SG.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                             --AND PR.CDFILIAL = P_CDFILIAL
                                             --AND NVL(PR.IDCNTRESTOQ,'S') = 'S'
                                             AND EXISTS (SELECT PPR.CDFILIAL
                                                           FROM PRODUTOROP PPR, PRODUTO PP
                                                          WHERE PPR.IDTPPRODUTOROP = 'CO'
                                                            AND PPR.CDFILIAL = TIPOPARAM_DRO(P_CDFILIAL)
                                                            AND PPR.CDPRODUTO = PP.CDPRODUTO
                                                            AND H.CDARVPROD LIKE PP.CDARVPROD||'%')
                                             AND NOT ((C_CONSCOMPRA.IDUTCUSORIGEST = 'S') AND (C_CONSCOMPRA.IDOPERDEVONF = 'S'))
                                             /*AND EXISTS (SELECT 1
                                                           FROM ITLANCTOEST
                                                          WHERE NRLANCESTQ = C_CONSCOMPRA.NRLANCESTQ
                                                            AND CDFILIAL = C_CONSCOMPRA.CDFILIAL
                                                            AND NVL(IDEVOLESTOQ,'S') = 'S') */
                                           GROUP BY AN.CDSUBGRPROD, AN.CDGRUPPROD

                                           UNION ALL

                                          SELECT AN.CDSUBGRPROD, AN.CDGRUPPROD,
                                                 SUM(I.QTLANCTOEST*I.VRLANCTOEST) AS VALORTOTAL,
                                                 SUM(I.QTLANCTOEST) AS QUANTIDADE
                                            FROM ITLANCTOEST I INNER JOIN (SELECT B.CDPRODUTO,A.CDGRUPPROD,A.CDSUBGRPROD,A.CDFILISUBGR
                                                                             FROM PRODUTO B INNER JOIN PRODUTO C ON B.CDPRODUTO = C.CDPRODUTO
                                                                                            INNER JOIN (SELECT P.CDARVPROD AS CDPRODUTO,
                                                                                                               N.CDGRUPPROD,
                                                                                                               N.CDSUBGRPROD,
                                                                                                               N.CDFILISUBGR
                                                                                                          FROM PRODUTO P INNER JOIN NVSUBGRPROD N
                                                                                                                                 ON P.CDPRODUTO = N.CDPRODUTO
                                                                                                         WHERE N.CDFILISUBGR = V_CDFILISUBGR
                                                                                                           AND N.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD) A
                                                                                                    ON A.CDFILISUBGR = V_CDFILISUBGR
                                                                                                   AND A.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                                                            WHERE C.CDARVPROD LIKE A.CDPRODUTO||'%'
                                                                            ORDER BY B.CDARVPROD) AN ON I.CDPRODUTO = AN.CDPRODUTO
                                                               INNER JOIN SUBGRPRODFIL SG ON SG.CDFILISUBGR = AN.CDFILISUBGR
                                                                                         AND SG.CDSUBGRPROD = AN.CDSUBGRPROD
                                                                                         AND SG.CDGRUPPROD = AN.CDGRUPPROD
                                                               INNER JOIN PRODUTO H ON H.CDPRODUTO = I.CDPRODUTO
                                                               INNER JOIN PRODFILI PR ON PR.CDPRODUTO = H.CDPRODESTO
                                           WHERE I.CDFILIAL = C_CONSCOMPRA.CDFILIAL
                                             AND I.NRLANCESTQ = C_CONSCOMPRA.NRLANCESTQ
                                             AND SG.CDFILISUBGR = V_CDFILISUBGR
                                             AND SG.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                             AND PR.CDFILIAL = P_CDFILIAL
                                             --AND NVL(PR.IDCNTRESTOQ,'S') = 'S'                   
                                             AND EXISTS (SELECT PPR.CDFILIAL
                                                           FROM PRODUTOROP PPR, PRODUTO PP
                                                          WHERE PPR.IDTPPRODUTOROP = 'CO'
                                                            AND PPR.CDFILIAL = TIPOPARAM_DRO(P_CDFILIAL)
                                                            AND PPR.CDPRODUTO = PP.CDPRODUTO
                                                            AND H.CDARVPROD LIKE PP.CDARVPROD||'%')
                                             AND NVL(I.IDEVOLESTOQ, 'S') = 'S'
                                             AND C_CONSCOMPRA.IDUTCUSORIGEST = 'S'
                                             AND C_CONSCOMPRA.IDOPERDEVONF = 'S'
                                           GROUP BY AN.CDSUBGRPROD, AN.CDGRUPPROD) X
                                   GROUP BY X.CDSUBGRPROD, X.CDGRUPPROD
                                   ORDER BY X.CDSUBGRPROD, X.CDGRUPPROD) LOOP
   
               BEGIN
                  SELECT SUM(VRCUSTLOGLAN) VRCUSTLOGLAN, SUM(VRSEGULOGLAN) VRSEGULOGLAN
                    INTO V_VRCUSTLOGLAN, V_VRSEGULOGLAN
                    FROM ITLANCTOEST IT LEFT  JOIN LANCTOESTOQ L ON IT.CDFILIAL = L.CDFILIAL
                                                                AND IT.NRLANCESTQ = L.NRLANCESTQ
                                                                AND C_CONSCOMPRA.NRLANCTONF = L.NRLANCTONF
                                        INNER JOIN PRSUBGRPROD AN ON IT.CDPRODUTO = AN.CDPRODUTO
                                        INNER JOIN SUBGRUPPROD SG ON AN.CDGRUPPROD = SG.CDGRUPPROD
                                                                 AND AN.CDSUBGRPROD = SG.CDSUBGRPROD
                                        INNER JOIN GRUPPROD GR ON SG.CDGRUPPROD = GR.CDGRUPPROD
                                        INNER JOIN PARFILIALROP P ON GR.CDGRUPPROD = P.CDGRUPPROD
                                        INNER JOIN PRODFILI PR ON IT.CDPRODMOVI = PR.CDPRODUTO
                                        INNER JOIN PRODUTO H ON PR.CDPRODUTO = H.CDPRODUTO
                   WHERE IT.CDFILIAL = C_CONSCOMPRA.CDFILIAL
                     AND IT.NRLANCESTQ = C_CONSCOMPRA.NRLANCESTQ
                     AND P.CDFILIAL = P_FILIALPAR
                     AND CDFILISUBGR = V_CDFILISUBGR
                     AND AN.CDSUBGRPROD = C_ITCONSCOMPRA.CDSUBGRPROD
                     AND PR.CDFILIAL = P_CDFILIAL
                     --AND NVL(PR.IDCNTRESTOQ,'S') = 'S'
                     AND EXISTS (SELECT PPR.CDFILIAL
                                   FROM PRODUTOROP PPR, PRODUTO PP
                                  WHERE PPR.IDTPPRODUTOROP = 'CO'
                                    AND PPR.CDFILIAL = TIPOPARAM_DRO(P_CDFILIAL)
                                    AND PPR.CDPRODUTO = PP.CDPRODUTO
                                    AND H.CDARVPROD LIKE PP.CDARVPROD||'%')
                     AND NVL(IT.IDEVOLESTOQ, 'S') = 'S';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_VRCUSTLOGLAN   := 0;
                     V_VRSEGULOGLAN   := 0;
                  END;
               END;

               NOVO_CODIGO('GESCONSUMOROP', V_NRSEQCONSUROP);

               INSERT INTO CONSUMOROP (NRFILIALROP, NRCONSUMOROP, CDGRUPPROD, CDSUBGRPROD, IDTPLANCROP,
                                       DTLANCAMENTO, DTCONSUMROP, CDCLIENTEROP, CDTIPOOPERROP, NRNOTAFISCROP,
                                       CDSERIENFROP, CDFILIALNFROP, NRLANCTONFROP, CDFILCENTROP, IDSTATUSROP,
                                       DTINIREFEROP, DTFINREFEROP, NUMEROREROP, QTITEMLANCROP, VRITEMLANCROP,
                                       VRCUSTOLOGITROP, VRCUSTSEGPROD)
                    VALUES (P_NRROPFILIAL, V_NRSEQCONSUROP, C_ITCONSCOMPRA.CDGRUPPROD, C_ITCONSCOMPRA.CDSUBGRPROD, 'CONSUMO_ENTRADA',
                            SYSDATE, C_CONSCOMPRA.DTENTRSAID, C_CONSCOMPRA.CDFORNECED, C_CONSCOMPRA.CDTIPOOPER, C_CONSCOMPRA.NRNOTAFISC,
                            C_CONSCOMPRA.CDSERIE, C_CONSCOMPRA.CDFILIAL, C_CONSCOMPRA.NRLANCTONF, V_CDFILCENTPME, C_CONSCOMPRA.IDSTATUS,
                            C_CONSCOMPRA.DTINIREFEFAT, C_CONSCOMPRA.DTFINREFEFAT, C_CONSCOMPRA.NUMERORE, C_ITCONSCOMPRA.QUANTIDADE, C_ITCONSCOMPRA.VALORTOTAL,
                            V_VRCUSTLOGLAN, V_VRSEGULOGLAN);
            END LOOP;
            FOR C_IMPOSTOCC IN(SELECT CDIMPOSTO, VALORIMPOSTO, NMIMPOSTO
                                 FROM (SELECT R.CDIMPOSTO, SUM(VRIMPOSTOIT) AS VALORIMPOSTO, II.NMIMPOSTO
                                         FROM ITEMPRODUTO I INNER JOIN ITEMPROIMPOS T
                                                                    ON I.CDFILIAL = T.CDFILIAL
                                                                   AND I.NRLANCTONF = T.NRLANCTONF
                                                                   AND I.NRSEQITPROD = T.NRSEQITPROD
                                                            INNER JOIN IMPOSTOROP R
                                                                    ON T.CDIMPOSTO = R.CDIMPOSTO
                                                            INNER JOIN IMPOSTO II ON R.CDIMPOSTO = II.CDIMPOSTO
                                        WHERE I.CDFILIAL = C_CONSCOMPRA.CDFILIAL
                                          AND I.NRLANCTONF = C_CONSCOMPRA.NRLANCTONF
                                          AND T.NRITPROIMPOS = '1'
                                          AND R.CDFILIAL = P_FILIALPAR
                                          AND R.IDTPIMPOSTOROP IN ('CC','ST')
                                        GROUP BY R.CDIMPOSTO, II.NMIMPOSTO
                                        ORDER BY R.CDIMPOSTO)) LOOP
                  
               NOVO_CODIGO('GESDEDUCAOROP', V_NRSEQDEDUROP);

               INSERT INTO DEDUCAOROP
                         (NRFILIALROP, NRDEDUCAOROP, CDFILIAL, DTREFERENC, NRNOTAFISC,
                          CDIMPOSTO, CDENCARGO, DSDEDUCAO, CDCLIENTE, CDFILIALBENE,
                          CDFORNECED, DSBENEFICIARIO, CDFILIALLANC, NRLANCTONF, NRRECEXTRAFILI,
                          IDTPLANCROP, CDFILIALCENT, VRDEDUCAO, VRPERFIXCALCIMP)
                    VALUES
                         (P_NRROPFILIAL, V_NRSEQDEDUROP, P_CDFILIAL, P_DTINICIAL, C_CONSCOMPRA.NRNOTAFISC,
                          C_IMPOSTOCC.CDIMPOSTO, NULL, C_IMPOSTOCC.NMIMPOSTO, C_CONSCOMPRA.CDFORNECED, NULL,
                          C_CONSCOMPRA.CDFORNECED, C_CONSCOMPRA.NMFANTFORN, C_CONSCOMPRA.CDFILIAL, C_CONSCOMPRA.NRLANCTONF, NULL,
                          'CONSUMO_ENTRADA', V_CDFILCENTPME, C_IMPOSTOCC.VALORIMPOSTO, NULL);
            END LOOP;
         END LOOP;
      END IF;

      IF C_PARFILIALROP.IDNFSAIDAROP = 'S' THEN
         FOR C_CONSSAIDA IN (SELECT N.CDFILIRECE, N.CDFILIEMIT, N.CDCLIENTE, N.CDTIPOOPER, N.NRNOTAFISC, N.CDFILIAL,
                                    N.NRLANCTONF, DTENTRSAID, N.CDSERIE, N.CDFORNECED, N.IDSTATUS,
                                    N.DTINIREFEFAT, N.DTFINREFEFAT, P.NUMERORE, L.NRLANCESTQ, F.NMFANTFORN
                               FROM NOTAFISCAL N LEFT JOIN LANCTOESTOQ L ON N.CDFILIAL = L.CDFILIAL
                                                                        AND N.NRLANCTONF = L.NRLANCTONF
                                                 LEFT JOIN (SELECT NRNUMERNF AS NUMERORE, CDFILIAL, DTNUMERNF AS DATARE
                                                              FROM NUMERORNF
                                                             WHERE CDFILIAL = P_CDFILIAL
                                                               AND IDENTRSAID = 'S'
                                                               AND TRUNC(DTNUMERNF) = P_DTINICIAL) P ON N.CDFILIAL = P.CDFILIAL
                                                                                                    AND N.DTENTRSAID = P.DATARE
                                                                                                    AND N.NRSEQCAPADOC = P.NUMERORE
                                                 LEFT  JOIN FORNECEDOR F ON N.CDFORNECED = F.CDFORNECED
                              WHERE N.CDFILIEMIT = P_CDFILIAL
                                AND N.IDENTRSAIDOP = 'S'
                                AND TRUNC(N.DTENTRSAID) = P_DTINICIAL
                                AND N.IDSTATUS <> 'C'
                                AND (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)
                                AND NOT EXISTS (SELECT CDTIPOOPER
                                                  FROM TIPOOPERACROP
                                                 WHERE CDFILIAL = P_FILIALPAR
                                                   AND IDENTRSAIDOP = 'S'
                                                   AND IDTPOPERACROP ='FS'
                                                   AND CDTIPOOPER = N.CDTIPOOPER)) LOOP

            FOR C_ITCONSSAIDA IN (SELECT AN.CDSUBGRPROD, AN.CDGRUPPROD,
                                         /* SUM(CUSTO_PRODUTO_LCEST(I.CDFILIMOVI, I.CDPRODMOVI, 4, I.DTLANCMOVI, SYSDATE, I.CDALMOXARIFE,
                                         I.CDLOCALESTOQ, I.NRLOTEESTQ, I.NRSUBLOTE)*I.QTTOTLANCTO) AS VALORTOTAL, */
                                         SUM(I.VRTOTLANCTO) AS VALORTOTAL,
                                         SUM(I.QTTOTLANCTO) AS QUANTIDADE
                                    FROM LANCTOESTOQ L INNER JOIN ITLANCTOEST I ON L.CDFILIAL = I.CDFILIAL
                                                                               AND L.NRLANCESTQ = I.NRLANCESTQ
                                                       INNER JOIN PARAMFILIAL PF ON I.CDFILIMOVI = PF.CDFILIAL
                                                       INNER JOIN PRODUTO PP ON PP.CDPRODUTO = I.CDPRODMOVI,
                                                       SUBGRPRODFIL SG, (SELECT B.CDPRODUTO,A.CDGRUPPROD,A.CDSUBGRPROD,A.CDFILISUBGR
                                                                           FROM PRODUTO B, PRODUTO C,
                                                                                (SELECT P.CDARVPROD AS CDPRODUTO, N.CDGRUPPROD, N.CDSUBGRPROD, N.CDFILISUBGR
                                                                                   FROM PRODUTO P,NVSUBGRPROD N
                                                                                  WHERE P.CDPRODUTO = N.CDPRODUTO
                                                                                    AND N.CDFILISUBGR = V_CDFILISUBGR
                                                                                    AND N.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD) A
                                                                          WHERE B.CDPRODUTO = C.CDPRODUTO
                                                                            AND A.CDFILISUBGR = V_CDFILISUBGR
                                                                            AND A.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                                                            AND C.CDARVPROD LIKE A.CDPRODUTO||'%'
                                                                          ORDER BY B.CDARVPROD) AN
                                   WHERE L.CDFILIAL = C_CONSSAIDA.CDFILIAL
                                     AND L.NRLANCTONF = C_CONSSAIDA.NRLANCTONF
                                     AND SG.CDFILISUBGR = V_CDFILISUBGR
                                     AND SG.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                     AND AN.CDFILISUBGR = SG.CDFILISUBGR
                                     AND AN.CDGRUPPROD = SG.CDGRUPPROD
                                     AND AN.CDSUBGRPROD = SG.CDSUBGRPROD
                                     AND I.CDPRODMOVI = AN.CDPRODUTO
                                     AND NVL(I.IDEVOLESTOQ, 'S') = 'S'
                                   GROUP BY AN.CDSUBGRPROD, AN.CDGRUPPROD
                                   ORDER BY AN.CDSUBGRPROD, AN.CDGRUPPROD) LOOP

               NOVO_CODIGO('GESCONSUMOROP', V_NRSEQCONSUROP);

               INSERT INTO CONSUMOROP (NRFILIALROP, NRCONSUMOROP, CDGRUPPROD, CDSUBGRPROD, IDTPLANCROP,
                                       DTLANCAMENTO, DTCONSUMROP, CDCLIENTEROP, CDTIPOOPERROP, NRNOTAFISCROP,
                                       CDSERIENFROP, CDFILIALNFROP, NRLANCTONFROP, CDFILCENTROP, IDSTATUSROP,
                                       DTINIREFEROP, DTFINREFEROP, NUMEROREROP, QTITEMLANCROP, VRITEMLANCROP)
                    VALUES (P_NRROPFILIAL, V_NRSEQCONSUROP, C_ITCONSSAIDA.CDGRUPPROD, C_ITCONSSAIDA.CDSUBGRPROD, 'CONSUMO_SAIDA',
                            SYSDATE, C_CONSSAIDA.DTENTRSAID, C_CONSSAIDA.CDFORNECED, C_CONSSAIDA.CDTIPOOPER, C_CONSSAIDA.NRNOTAFISC,
                            C_CONSSAIDA.CDSERIE, C_CONSSAIDA.CDFILIAL, C_CONSSAIDA.NRLANCTONF, V_CDFILCENTPME, C_CONSSAIDA.IDSTATUS,
                            C_CONSSAIDA.DTINIREFEFAT, C_CONSSAIDA.DTFINREFEFAT, C_CONSSAIDA.NUMERORE, C_ITCONSSAIDA.QUANTIDADE, C_ITCONSSAIDA.VALORTOTAL);
            END LOOP;
            FOR C_IMPOSTOCS IN (SELECT CDIMPOSTO, VALORIMPOSTO, NMIMPOSTO
                                  FROM (SELECT R.CDIMPOSTO, SUM(VRIMPOSTOIT) AS VALORIMPOSTO, II.NMIMPOSTO
                                          FROM ITEMPRODUTO I INNER JOIN ITEMPROIMPOS T
                                                                     ON I.CDFILIAL = T.CDFILIAL
                                                                    AND I.NRLANCTONF = T.NRLANCTONF
                                                                    AND I.NRSEQITPROD = T.NRSEQITPROD
                                                                INNER JOIN IMPOSTOROP R
                                                                     ON T.CDIMPOSTO = R.CDIMPOSTO
                                                             INNER JOIN PRODUTO P
                                                                     ON I.CDPRODUTO = P.CDPRODUTO
                                                             INNER JOIN (SELECT A.CDFILISUBGR, A.CDSUBGRPROD,
                                                                                A.CDGRUPPROD, B.NMPRODUTO,
                                                                                B.CDARVPROD, B.CDPRODUTO
                                                                           FROM PRODUTO B INNER JOIN NVSUBGRPROD A
                                                                                                  ON B.CDPRODUTO = A.CDPRODUTO
                                                                          WHERE A.CDFILISUBGR = V_CDFILISUBGR
                                                                            AND A.CDGRUPPROD = C_PARFILIALROP.CDGRUPPROD
                                                                            AND NVL(B.IDPRODATIVPR,'S') = 'S') PROD
                                                                     ON P.CDARVPROD LIKE PROD.CDARVPROD||'%'
                                                             INNER JOIN IMPOSTO II ON R.CDIMPOSTO = II.CDIMPOSTO
                                         WHERE I.CDFILIAL = C_CONSSAIDA.CDFILIAL
                                           AND I.NRLANCTONF = C_CONSSAIDA.NRLANCTONF
                                           AND T.NRITPROIMPOS = '1'
                                           AND R.CDFILIAL = P_FILIALPAR
                                           AND R.IDTPIMPOSTOROP IN('CS','ST')
                                         GROUP BY R.CDIMPOSTO, II.NMIMPOSTO
                                         ORDER BY R.CDIMPOSTO)) LOOP
               
               NOVO_CODIGO('GESDEDUCAOROP', V_NRSEQDEDUROP);

               INSERT INTO DEDUCAOROP
                         (NRFILIALROP, NRDEDUCAOROP, CDFILIAL, DTREFERENC, NRNOTAFISC,
                          CDIMPOSTO, CDENCARGO, DSDEDUCAO, CDCLIENTE, CDFILIALBENE,
                          CDFORNECED, DSBENEFICIARIO, CDFILIALLANC, NRLANCTONF, NRRECEXTRAFILI,
                          IDTPLANCROP, CDFILIALCENT, VRDEDUCAO, VRPERFIXCALCIMP)
                    VALUES
                         (P_NRROPFILIAL, V_NRSEQDEDUROP, P_CDFILIAL, P_DTINICIAL, NULL,
                          C_IMPOSTOCS.CDIMPOSTO, NULL, C_IMPOSTOCS.NMIMPOSTO, C_CONSSAIDA.CDFORNECED, NULL,
                          NULL, C_CONSSAIDA.NMFANTFORN, NULL, NULL, NULL,
                          'CONSUMO_SAIDA', V_CDFILCENTPME, C_IMPOSTOCS.VALORIMPOSTO, NULL);
            END LOOP;
         END LOOP;
      END IF;
      
      IF C_PARFILIALROP.IDTPAPURACAO <> '1' THEN
         FOR C_CONSUMO IN(SELECT V.CDFILIAL, V.DTENTRVENDA,P.CDGRUPPROD,P.CDSUBGRPROD,
                                 SUM(I.QTPRODVEND) AS QTDETOTAL,
                                 SUM(CUSTO_COMPOSICAO(I.CDPRODUTO,V.CDFILIAL,V.CDFILIAL,V_NRTIPOCUSTO,I.QTPRODVEND,'',V.DTENTRVENDA)) AS VALORTOTAL
                            FROM VENDA V, ITEMVENDA I, PRSUBGRPROD P, PARFILIALROP PR
                           WHERE V.CDFILIAL = P_CDFILIAL
                             AND V.DTENTRVENDA = P_DTINICIAL
                             AND V.CDFILIAL = I.CDFILIAL
                             AND V.CDCAIXA = I.CDCAIXA
                             AND V.NRSEQVENDA = I.NRSEQVENDA
                             AND V.IDSITUVENDA = 'O'
                             AND PR.CDFILIAL = P_FILIALPAR
                             AND PR.CDGRUPPRODDRL = C_PARFILIALROP.CDGRUPPROD
                             AND P.CDFILISUBGR(+) = V_CDFILISUBGR
                             AND P.CDGRUPPROD(+) = C_PARFILIALROP.CDGRUPPROD
                             AND P.CDPRODUTO(+) = I.CDPRODUTO
                           GROUP BY V.CDFILIAL, V.DTENTRVENDA, P.CDGRUPPROD,P.CDSUBGRPROD) LOOP

               NOVO_CODIGO('GESCONSUMOROP', V_NRSEQCONSUROP);

               INSERT INTO CONSUMOROP (NRFILIALROP, NRCONSUMOROP, CDGRUPPROD, CDSUBGRPROD, IDTPLANCROP,
                                       DTLANCAMENTO, DTCONSUMROP, CDCLIENTEROP, CDTIPOOPERROP, NRNOTAFISCROP,
                                       CDSERIENFROP, CDFILIALNFROP, NRLANCTONFROP, CDFILCENTROP, IDSTATUSROP,
                                       DTINIREFEROP, DTFINREFEROP, NUMEROREROP, QTITEMLANCROP, VRITEMLANCROP)
                    VALUES (P_NRROPFILIAL, V_NRSEQCONSUROP, C_CONSUMO.CDGRUPPROD, C_CONSUMO.CDSUBGRPROD, 'CONSUMO_VENDA',
                            SYSDATE, P_DTINICIAL, NULL, NULL, NULL,
                            NULL, NULL, NULL, V_CDFILCENTPME, NULL,
                            NULL, NULL, NULL, C_CONSUMO.QTDETOTAL, C_CONSUMO.VALORTOTAL);
          END LOOP;
      END IF;
   END LOOP;
END;
/