CREATE OR REPLACE PROCEDURE APURA_FCI(
   P_CDFILIAL   IN MAPAFCI.CDFILIALFCI%TYPE,
   P_NRMAPAFCI  IN MAPAFCI.NRMAPAFCI%TYPE)

IS
   V_DTINIPERIODO MAPAFCI.DTINIPERIODO%TYPE;
   V_DTFIMPERIODO MAPAFCI.DTFIMPERIODO%TYPE;
   V_INIPENULTIMOMES MAPAFCI.DTINIPERIODO%TYPE;
   V_FIMPENULTIMOMES MAPAFCI.DTINIPERIODO%TYPE;
   V_NRSEQITMAPAFCI ITMAPAFCI.NRSEQITMAPAFCI%TYPE;
   V_VRVENDAIE ITMAPAFCI.VRVENDAIE%TYPE;
BEGIN

   DELETE FROM ITMAPAFCI
         WHERE CDFILIALFCI = P_CDFILIAL
           AND NRMAPAFCI = P_NRMAPAFCI;

   SELECT MAX(DTINIPERIODO) , MAX(DTFIMPERIODO), TRUNC(ADD_MONTHS(MAX(DTINIPERIODO), -2), 'MM') AS INIPENULTIMOMES , LAST_DAY(ADD_MONTHS(MAX(DTINIPERIODO), -2)) AS FIMPENULTIMOMES
     INTO V_DTINIPERIODO , V_DTFIMPERIODO, V_INIPENULTIMOMES, V_FIMPENULTIMOMES
     FROM MAPAFCI
    WHERE CDFILIALFCI = P_CDFILIAL
      AND NRMAPAFCI = P_NRMAPAFCI;

   FOR C_ITENS IN (SELECT PA.CDSUBPRODETA AS CDPRODUTOOP, PR1.CDARVPROD, PR1.NMPRODUTO,
                          PR1.SGUNIDADE, OP.NRORDPROD, OP.NRSUBORDPROD, PA.QTPRACABADO AS QTPRODUZIDA,
                          LO.CDPRODUTO AS CDPRODINSUMO, PR.CDARVPROD AS CDARVINSUMO, PR.NMPRODUTO AS NMPRODINSUMO,
                          LO.NRLOTEESTQ, LO.NRSUBLOTE, IT.NRLANCESTQ, IT.DTLANCMOVI, L.DSLANCESTQ,
                          IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, IP.VRUNITITEM
                     FROM ORDPRODUCAO OP INNER JOIN PRODACABADO PA
                                                 ON OP.CDFILIAL = PA.CDFILIAL
                                                AND OP.NRORDPROD = PA.NRORDPROD
                                                AND OP.NRSUBORDPROD = PA.NRSUBORDPROD
                                         INNER JOIN PRODUTO PR1
                                                 ON PA.CDSUBPRODETA = PR1.CDPRODUTO
                                         INNER JOIN RETIRADAESTO R
                                                 ON OP.CDFILIAL = R.CDFILIAL
                                                AND OP.NRORDPROD = R.NRORDPROD
                                                AND OP.NRSUBORDPROD = R.NRSUBORDPROD
                                                AND R.NRLANCESTQ IS NOT NULL
                                         INNER JOIN ITEMRETIEST I
                                                 ON R.CDFILIAL = I.CDFILIAL
                                                AND R.NRRETEST = I.NRRETEST
                                         INNER JOIN LOTITRETIEST LO
                                                 ON I.CDFILIAL = LO.CDFILIAL
                                                AND I.NRRETEST = LO.NRRETEST
                                                AND I.CDPRODUTO = LO.CDPRODUTO
                                                AND I.NRSEQRETPROD = LO.NRSEQRETPROD
                                                AND LO.IDTPRETEST = 'R'
                                         INNER JOIN PRODUTO PR
                                                 ON LO.CDPRODUTO = PR.CDPRODUTO
                                         INNER JOIN ITLANCTOEST IT
                                                 ON LO.CDFILIAL = IT.CDFILIMOVI
                                                AND LO.CDPRODUTO = IT.CDPRODMOVI
                                                AND LO.NRLOTEESTQ = IT.NRLOTEESTQ
                                                AND LO.NRSUBLOTE = IT.NRSUBLOTE
                                         INNER JOIN LANCTOESTOQ L
                                                 ON IT.CDFILIAL = L.CDFILIAL
                                                AND IT.NRLANCESTQ = L.NRLANCESTQ
                                         INNER JOIN NOTAFISCAL NF
                                                 ON L.CDFILIAL = NF.CDFILIAL
                                                AND L.NRLANCTONF = NF.NRLANCTONF
                                         INNER JOIN ITEMPRODUTO IP
                                                 ON NF.CDFILIAL = IP.CDFILIAL
                                                AND NF.NRLANCTONF = IP.NRLANCTONF
                                                AND IT.CDPRODMOVI = IP.CDPRODMOVI
                                                AND IT.NRLOTEESTQ = IP.NRLOTEESTQ
                                                AND IT.NRSUBLOTE = IP.NRSUBLOTE,
                          (SELECT DIGITO1CST FROM DIGITOCST WHERE IDCSTIFCI = 'S') CST
                    WHERE OP.CDFILIAL = P_CDFILIAL
                      AND TO_DATE(OP.DTHRFIMREAOP) BETWEEN TO_DATE(V_DTINIPERIODO) AND TO_DATE(V_DTFIMPERIODO)
                      AND OP.IDSITORDPROD = '3'
                      AND IT.IDTIPOMOVI = '2'
                      AND SUBSTR(IP.CDSITUTRIBPR, 1, 1) = CST.DIGITO1CST
                   ORDER BY PA.CDSUBPRODETA, OP.NRORDPROD, OP.NRSUBORDPROD, LO.CDPRODUTO)
   LOOP
      SELECT NVL(MAX(NRSEQITMAPAFCI),0)+1
        INTO V_NRSEQITMAPAFCI
        FROM ITMAPAFCI
       WHERE CDFILIALFCI = P_CDFILIAL
         AND NRMAPAFCI = P_NRMAPAFCI;

      INSERT INTO ITMAPAFCI
     (CDFILIALFCI, NRMAPAFCI, NRSEQITMAPAFCI, CDPRODUTOOP, NRORDPROD, NRSUBORDPROD, QTPRODUZIDA,
      CDPRODINSUMO, NRLOTEESTQ, NRSUBLOTE,
      CDFILIAL, NRLANCTONF, NRSEQITPROD, VRUNITITEM)
      VALUES
     (P_CDFILIAL, P_NRMAPAFCI, V_NRSEQITMAPAFCI, C_ITENS.CDPRODUTOOP, C_ITENS.NRORDPROD, C_ITENS.NRSUBORDPROD, C_ITENS.QTPRODUZIDA,
      C_ITENS.CDPRODINSUMO, C_ITENS.NRLOTEESTQ, C_ITENS.NRSUBLOTE, C_ITENS.CDFILIAL, C_ITENS.NRLANCTONF, C_ITENS.NRSEQITPROD, C_ITENS.VRUNITITEM);

   END LOOP;

   FOR C_VRIMP IN (SELECT CDFILIALFCI, NRMAPAFCI, CDPRODUTOOP,  AVG(VRUNITITEM) AS VRPARCIMPO
                     FROM ITMAPAFCI
                    WHERE CDFILIAL = P_CDFILIAL
                      AND NRMAPAFCI = P_NRMAPAFCI
                   GROUP BY CDFILIALFCI, NRMAPAFCI,  CDPRODUTOOP)
   LOOP
      SELECT ROUND(AVG(VRUNITITEM), 2) AS VRVENDAIE
        INTO V_VRVENDAIE
        FROM (SELECT IP.CDFILIAL, IP.NRLANCTONF, P.CDARVPROD, P.NMPRODUTO, IP.CDSITUTRIBPR,
                     IP.CDFISOPEITEM, P.SGUNIDADE, SUM(IP.QTITEMPROD) AS QTITEMPROD,
                     IP.VRUNITITEM, SUM(IP.VRTOTAITEM) AS VRTOTAITEM,
                     SUM(IP.VRDESCITEM) AS VRDESCITEM, NF.NRNOTAFISC, NF.DTEMISSAO
                FROM ITEMPRODUTO IP, PRODUTO P, NOTAFISCAL NF, TIPOOPERAC TP
               WHERE IP.CDPRODUTO = C_VRIMP.CDPRODUTOOP
                 AND IP.CDPRODUTO = P.CDPRODUTO
                 AND SUBSTR(IP.CDFISOPEITEM, 1, 1) = '6'
                 AND IP.CDFISOPEITEM <> '6910'
                 AND IP.CDFISOPEITEM <> '6911'
                 AND NVL(IP.IDPRODBONIF, 'N') = 'N'
                 AND NF.CDFILIAL = IP.CDFILIAL
                 AND NF.NRLANCTONF = IP.NRLANCTONF
                 AND NF.IDENTRSAIDOP = TP.IDENTRSAIDOP
                 AND NF.CDTIPOOPER = TP.CDTIPOOPER
                 AND NVL(TP.IDNFAJUSTE, 'N') = 'N'
                 AND NVL(TP.IDNOTADEVO, 'N') = 'N'
                 AND NVL(TP.IDNFESTCANC, 'N') = 'N'
                 AND NF.IDORIGDESTNF = 'C'
                 AND NF.CDFILIEMIT = P_CDFILIAL
                 AND NF.DTENTRSAID BETWEEN V_INIPENULTIMOMES AND V_FIMPENULTIMOMES
              GROUP BY IP.CDFILIAL, IP.NRLANCTONF, P.CDARVPROD, P.NMPRODUTO, IP.CDSITUTRIBPR,
                       IP.CDFISOPEITEM, P.SGUNIDADE, IP.VRUNITITEM, NF.NRNOTAFISC, NF.DTEMISSAO
              ORDER BY NF.DTEMISSAO);
      IF (V_VRVENDAIE IS NULL) OR (V_VRVENDAIE = 0) THEN
         UPDATE ITMAPAFCI
            SET VRPARCIMPO = ROUND(C_VRIMP.VRPARCIMPO,2),
                VRVENDAIE = V_VRVENDAIE
          WHERE CDFILIAL = P_CDFILIAL
            AND NRMAPAFCI = P_NRMAPAFCI
            AND CDPRODUTOOP = C_VRIMP.CDPRODUTOOP;
      ELSE
         UPDATE ITMAPAFCI
            SET VRPARCIMPO = ROUND(C_VRIMP.VRPARCIMPO,2),
                VRVENDAIE = ROUND(V_VRVENDAIE,2),
                VRFCI = ROUND(((ROUND(C_VRIMP.VRPARCIMPO,2)/ROUND(V_VRVENDAIE,2))*100),2)
          WHERE CDFILIAL = P_CDFILIAL
            AND NRMAPAFCI = P_NRMAPAFCI
            AND CDPRODUTOOP = C_VRIMP.CDPRODUTOOP;
     END IF;
   END LOOP;

   UPDATE MAPAFCI
      SET IDSITMAPAFCI = 'A'
    WHERE CDFILIALFCI = P_CDFILIAL
     AND NRMAPAFCI = P_NRMAPAFCI;
END;
/