CREATE OR REPLACE PROCEDURE INSERE_COMITEMEXP(
  P_CDFILIAL     IN EXPEDICAO.CDFILIAL%TYPE,
  P_NREXPEDICAO  IN EXPEDICAO.NREXPEDICAO%TYPE,
  P_NRPEDIDO     IN ITEMPEDIDO.NRPEDIDO%TYPE,
  P_NRITEMPED    IN ITEMPEDIDO.NRITEMPED%TYPE,
  P_CDPRODUTO    IN ITEMPEDIDO.CDPRODUTO%TYPE,
  P_QTITEMPED    IN ITEMPEDIDO.QTITEMPED%TYPE,
  P_IDUTCOMP OUT PAREXPEDICAO.IDGERAFINANC%TYPE

 )
IS
   V_IDTPCOMPPROD     PARAMPEDIDO.IDTPCOMPPROD%TYPE;
   V_IDCTRLESTQ       PARAMFILIAL.IDCTRLESTQ%TYPE;
   V_IDUTILALMOX      PARAMFILIAL.IDUTILALMOX%TYPE;
   V_CDALMOXARIFE     PARAMFILIAL.CDALMOXARIFE%TYPE;
   V_CDALMOXEXP       ALMOXARIFADO.CDALMOXARIFE%TYPE;
   V_IDUTILLCESTQ     PARAMFILIAL.IDUTILLCESTQ%TYPE;
   V_CDLOCALESTOQ     PARAMFILIAL.CDLOCALESTOQ%TYPE;
   V_CDLOCALEXP       LOCALESTOQUE.CDLOCALESTOQ%TYPE;
   V_IDCNTRESTOQ      PRODFILI.IDCNTRESTOQ%TYPE;
   V_CDALMOXPRCLI     PRODFILI.CDALMOXPRCLI%TYPE;
   V_CDLOCALPRCLI     PRODFILI.CDLOCALPRCLI%TYPE;
   V_IDUTCTRLLOTE     PRODFILI.IDUTCTRLLOTE%TYPE;
   V_EXISTE_PROD      INTEGER;
   V_EXISTECOMPOSICAO INTEGER;
   V_IDUTCOMP         PAREXPEDICAO.IDGERAFINANC%TYPE;
BEGIN
   BEGIN
      SELECT NVL(IDTPCOMPPROD,'F')
        INTO V_IDTPCOMPPROD
        FROM PARAMPEDIDO
       WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDTPCOMPPROD := 'F';
      END;
   END;
   BEGIN
      SELECT NVL(IDCTRLESTQ,'S'),   NVL(IDUTILALMOX,'N'),   CDALMOXARIFE,   NVL(IDUTILLCESTQ,'N'),   CDLOCALESTOQ
        INTO V_IDCTRLESTQ, V_IDUTILALMOX, V_CDALMOXARIFE, V_IDUTILLCESTQ, V_CDLOCALESTOQ
        FROM PARAMFILIAL
       WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDCTRLESTQ := 'S';
         V_IDUTILALMOX := 'N';
         V_CDALMOXARIFE := NULL;
         V_IDUTILLCESTQ := 'N';
         V_CDLOCALESTOQ := NULL;
      END;
   END;
   V_EXISTECOMPOSICAO := 0;
   IF V_IDTPCOMPPROD = 'F' THEN
      FOR C_FABLOCAL IN (SELECT R.CDFILIAL, R.CDPRODUTO, R.CDSUBPRODLOC, PR.CDPRODESTO, (R.QTSUBPRODLOC * PR.VRFATOCONV) QTSUBPRODLOC
                           FROM ROTFABLOCAL R JOIN PRODFILI P
                                                ON R.CDFILIAL      =  P.CDFILIAL
                                               AND R.CDSUBPRODLOC  =  P.CDPRODUTO
                                              JOIN PRODUTO PR
                                                ON R.CDSUBPRODLOC = PR.CDPRODUTO
                          WHERE R.CDFILIAL =  P_CDFILIAL
                            AND R.CDPRODUTO =  P_CDPRODUTO
                            AND NVL(P.IDCNTRESTOQ,'S') = 'S'
                            AND ((TRUNC(SYSDATE) >= NVL(TRUNC(R.DTINIVGPRODLOC),TRUNC(SYSDATE))) AND (TRUNC(SYSDATE) <= NVL(TRUNC(R.DTFINVGPRODLOC),TRUNC(SYSDATE))))
      ) LOOP
         V_EXISTE_PROD := 0;
         V_EXISTECOMPOSICAO := 1;
         BEGIN
            SELECT COUNT(*)
              INTO V_EXISTE_PROD
              FROM ROTFABLOCAL
             WHERE CDFILIAL = C_FABLOCAL.CDFILIAL
               AND CDPRODUTO = C_FABLOCAL.CDSUBPRODLOC
               AND ((TRUNC(SYSDATE) >= NVL(TRUNC(DTINIVGPRODLOC),TRUNC(SYSDATE))) AND (TRUNC(SYSDATE) <= NVL(TRUNC(DTFINVGPRODLOC),TRUNC(SYSDATE))));
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_EXISTE_PROD := 0;
            END;
         END;
         IF (V_EXISTE_PROD = 0) THEN
            BEGIN
               SELECT COUNT(*)
                 INTO V_EXISTE_PROD
                 FROM ROTFABRICA
                WHERE CDPRODUTO = C_FABLOCAL.CDSUBPRODLOC
                  AND ((TRUNC(SYSDATE) >= NVL(TRUNC(DTINIVGPRODROT),TRUNC(SYSDATE))) AND (TRUNC(SYSDATE) <= NVL(TRUNC(DTFINVGPRODROT),TRUNC(SYSDATE))));
            EXCEPTION
               WHEN NO_DATA_FOUND THEN BEGIN
                  V_EXISTE_PROD := 0;
               END;
            END;
         END IF;
         IF (V_EXISTE_PROD = 0) THEN
            BEGIN
               SELECT NVL(IDCNTRESTOQ,'S'),   CDALMOXPRCLI,   CDLOCALPRCLI, NVL(IDUTCTRLLOTE, 'S')
                 INTO V_IDCNTRESTOQ, V_CDALMOXPRCLI, V_CDLOCALPRCLI, V_IDUTCTRLLOTE
                 FROM PRODFILI
                WHERE CDFILIAL  = C_FABLOCAL.CDFILIAL
                  AND CDPRODUTO = C_FABLOCAL.CDPRODESTO;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN BEGIN
                  V_IDCNTRESTOQ  := 'S';
                  V_CDALMOXPRCLI := NULL;
                  V_CDLOCALPRCLI := NULL;
                  V_IDUTCTRLLOTE := 'S';
               END;
            END;
            IF (V_IDCTRLESTQ = 'S') THEN
               IF (V_IDCNTRESTOQ = 'S') THEN
                  IF (V_IDUTILALMOX = 'S') THEN
                     V_CDALMOXEXP := V_CDALMOXPRCLI;
                     IF (V_CDALMOXEXP IS NULL) THEN
                        V_CDALMOXEXP := V_CDALMOXARIFE;
                     END IF;
                  ELSE
                     V_CDALMOXEXP := ' ';
                  END IF;
                  IF (V_IDUTILLCESTQ = 'S') THEN
                     V_CDLOCALEXP := V_CDLOCALPRCLI;
                     IF (V_CDLOCALEXP IS NULL) THEN
                        V_CDLOCALEXP := V_CDLOCALESTOQ;
                     END IF;
                  ELSE
                     V_CDLOCALEXP := ' ';
                  END IF;
               END IF;
            ELSE
               V_CDALMOXEXP := ' ';
               V_CDLOCALEXP := ' ';
            END IF;
            INSERT INTO COMPITEMEXP
            (CDFILIAL, NREXPEDICAO, NRPEDIDO, NRITEMPED, CDPRODUTO, IDTPCOMPPROD, CDSUBPROD, QTSUBPROD,
             CDSUBPRALMOX, CDSUBPRLOCAL, IDSUBPRCTRLEST, IDSUBPRUTALMOX, IDSUBPRUTLCEST, IDSUBPRCTRLLOTE)
            VALUES
            (P_CDFILIAL, P_NREXPEDICAO, P_NRPEDIDO, P_NRITEMPED, C_FABLOCAL.CDPRODUTO, 'L', C_FABLOCAL.CDSUBPRODLOC, (P_QTITEMPED * C_FABLOCAL.QTSUBPRODLOC),
             V_CDALMOXEXP, V_CDLOCALEXP, V_IDCNTRESTOQ, V_IDUTILALMOX, V_IDUTILLCESTQ, V_IDUTCTRLLOTE);
         ELSE
            INSERE_COMITEMEXP(P_CDFILIAL, P_NREXPEDICAO, P_NRPEDIDO, P_NRITEMPED, C_FABLOCAL.CDSUBPRODLOC, (P_QTITEMPED * C_FABLOCAL.QTSUBPRODLOC), V_IDUTCOMP);
         END IF;
      END LOOP;
      IF (V_EXISTECOMPOSICAO = 0) THEN
         FOR C_ROTFABRICA IN (SELECT P.CDFILIAL, R.CDPRODUTO, R.CDSUBPRODUTO, PR.CDPRODESTO, (R.QTSUBPRODROT * PR.VRFATOCONV) QTSUBPRODROT
                                FROM ROTFABRICA R JOIN PRODFILI P
                                                    ON P.CDFILIAL =  P_CDFILIAL
                                                   AND R.CDSUBPRODUTO =  P.CDPRODUTO
                                                  JOIN PRODUTO PR
                                                    ON R.CDSUBPRODUTO = PR.CDPRODUTO
                               WHERE R.CDPRODUTO = P_CDPRODUTO
                                 AND NVL(P.IDCNTRESTOQ, 'S')  =  'S'
                                 AND ((TRUNC(SYSDATE) >= NVL(TRUNC(R.DTINIVGPRODROT),TRUNC(SYSDATE))) AND (TRUNC(SYSDATE) <= NVL(TRUNC(R.DTFINVGPRODROT),TRUNC(SYSDATE))))
         ) LOOP
            V_EXISTE_PROD := 0;
            V_EXISTECOMPOSICAO := 1;
            BEGIN
               SELECT COUNT(*)
                 INTO V_EXISTE_PROD
                 FROM ROTFABLOCAL
                WHERE CDFILIAL = C_ROTFABRICA.CDFILIAL
                  AND CDPRODUTO = C_ROTFABRICA.CDSUBPRODUTO
                  AND ((TRUNC(SYSDATE) >= NVL(TRUNC(DTINIVGPRODLOC),TRUNC(SYSDATE))) AND (TRUNC(SYSDATE) <= NVL(TRUNC(DTFINVGPRODLOC),TRUNC(SYSDATE))));
            EXCEPTION
               WHEN NO_DATA_FOUND THEN BEGIN
                  V_EXISTE_PROD := 0;
               END;
            END;
            IF (V_EXISTE_PROD = 0) THEN
               BEGIN
                  SELECT COUNT(*)
                    INTO V_EXISTE_PROD
                    FROM ROTFABRICA
                   WHERE CDPRODUTO = C_ROTFABRICA.CDSUBPRODUTO
                     AND ((TRUNC(SYSDATE) >= NVL(TRUNC(DTINIVGPRODROT),TRUNC(SYSDATE))) AND (TRUNC(SYSDATE) <= NVL(TRUNC(DTFINVGPRODROT),TRUNC(SYSDATE))));
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_EXISTE_PROD := 0;
                  END;
               END;
            END IF;
            IF (V_EXISTE_PROD = 0) THEN
               BEGIN
                  SELECT NVL(IDCNTRESTOQ,'S'),   CDALMOXPRCLI,   CDLOCALPRCLI, NVL(IDUTCTRLLOTE, 'S')
                    INTO V_IDCNTRESTOQ, V_CDALMOXPRCLI, V_CDLOCALPRCLI, V_IDUTCTRLLOTE
                    FROM PRODFILI
                   WHERE CDFILIAL  = C_ROTFABRICA.CDFILIAL
                     AND CDPRODUTO = C_ROTFABRICA.CDPRODESTO;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_IDCNTRESTOQ  := 'S';
                     V_CDALMOXPRCLI := NULL;
                     V_CDLOCALPRCLI := NULL;
                     V_IDUTCTRLLOTE := 'S';
                  END;
               END;
               IF (V_IDCTRLESTQ = 'S') THEN
                  IF (V_IDCNTRESTOQ = 'S') THEN
                     IF (V_IDUTILALMOX = 'S') THEN
                        V_CDALMOXEXP := V_CDALMOXPRCLI;
                        IF (V_CDALMOXEXP IS NULL) THEN
                           V_CDALMOXEXP := V_CDALMOXARIFE;
                        END IF;
                     ELSE
                        V_CDALMOXEXP := ' ';
                     END IF;
                     IF (V_IDUTILLCESTQ = 'S') THEN
                        V_CDLOCALEXP := V_CDLOCALPRCLI;
                        IF (V_CDLOCALEXP IS NULL) THEN
                           V_CDLOCALEXP := V_CDLOCALESTOQ;
                        END IF;
                     ELSE
                        V_CDLOCALEXP := ' ';
                     END IF;
                  END IF;
               ELSE
                  V_CDALMOXEXP := ' ';
                  V_CDLOCALEXP := ' ';
               END IF;
               INSERT INTO COMPITEMEXP
               (CDFILIAL, NREXPEDICAO, NRPEDIDO, NRITEMPED, CDPRODUTO, IDTPCOMPPROD, CDSUBPROD, QTSUBPROD,
                CDSUBPRALMOX, CDSUBPRLOCAL, IDSUBPRCTRLEST, IDSUBPRUTALMOX, IDSUBPRUTLCEST, IDSUBPRCTRLLOTE)
               VALUES
               (P_CDFILIAL, P_NREXPEDICAO, P_NRPEDIDO, P_NRITEMPED, C_ROTFABRICA.CDPRODUTO, 'P', C_ROTFABRICA.CDSUBPRODUTO, (P_QTITEMPED * C_ROTFABRICA.QTSUBPRODROT),
                V_CDALMOXEXP, V_CDLOCALEXP, V_IDCNTRESTOQ, V_IDUTILALMOX, V_IDUTILLCESTQ, V_IDUTCTRLLOTE);
            ELSE
               INSERE_COMITEMEXP(P_CDFILIAL, P_NREXPEDICAO, P_NRPEDIDO, P_NRITEMPED, C_ROTFABRICA.CDSUBPRODUTO, (P_QTITEMPED * C_ROTFABRICA.QTSUBPRODROT), V_IDUTCOMP);
            END IF;
         END LOOP;
      END IF;
   ELSE
      V_EXISTECOMPOSICAO := 0;
      FOR C_COMPITEMPED IN (SELECT C.CDFILIAL, C.CDPRODUTO, C.CDPRODCOMPED, PR.CDPRODESTO, (SUM(C.QTITCOMPPROD)*PR.VRFATOCONV) AS QTITCOMPPROD
                              FROM COMPITEMPED C JOIN PRODFILI P
                                                   ON C.CDFILIAL = P.CDFILIAL
                                                  AND C.CDPRODCOMPED = P.CDPRODUTO
                                                 JOIN PRODUTO PR
                                                   ON C.CDPRODCOMPED = PR.CDPRODUTO
                             WHERE C.CDFILIAL  = P_CDFILIAL
                               AND C.NRPEDIDO  =  P_NRPEDIDO
                               AND C.NRITEMPED =  P_NRITEMPED
                               AND NVL(P.IDCNTRESTOQ, 'S') = 'S'
                            GROUP BY C.CDFILIAL, C.CDPRODUTO, C.CDPRODCOMPED, PR.CDPRODESTO, PR.VRFATOCONV
                            ORDER BY C.CDPRODCOMPED
      ) LOOP
         V_EXISTECOMPOSICAO := 1;
         BEGIN
            SELECT NVL(IDCNTRESTOQ,'S'),   CDALMOXPRCLI,   CDLOCALPRCLI, NVL(IDUTCTRLLOTE, 'S')
              INTO V_IDCNTRESTOQ, V_CDALMOXPRCLI, V_CDLOCALPRCLI, V_IDUTCTRLLOTE
              FROM PRODFILI
             WHERE CDFILIAL  = C_COMPITEMPED.CDFILIAL
               AND CDPRODUTO = C_COMPITEMPED.CDPRODESTO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_IDCNTRESTOQ  := 'S';
               V_CDALMOXPRCLI := NULL;
               V_CDLOCALPRCLI := NULL;
               V_IDUTCTRLLOTE := 'S';
            END;
         END;
         IF (V_IDCTRLESTQ = 'S') THEN
            IF (V_IDCNTRESTOQ = 'S') THEN
               IF (V_IDUTILALMOX = 'S') THEN
                  V_CDALMOXEXP := V_CDALMOXPRCLI;
                  IF (V_CDALMOXEXP IS NULL) THEN
                     V_CDALMOXEXP := V_CDALMOXARIFE;
                  END IF;
               ELSE
                  V_CDALMOXEXP := ' ';
               END IF;
               IF (V_IDUTILLCESTQ = 'S') THEN
                  V_CDLOCALEXP := V_CDLOCALPRCLI;
                  IF (V_CDLOCALEXP IS NULL) THEN
                     V_CDLOCALEXP := V_CDLOCALESTOQ;
                  END IF;
               ELSE
                  V_CDLOCALEXP := ' ';
               END IF;
            END IF;
         ELSE
            V_CDALMOXEXP := ' ';
            V_CDLOCALEXP := ' ';
         END IF;
         INSERT INTO COMPITEMEXP
         (CDFILIAL, NREXPEDICAO, NRPEDIDO, NRITEMPED, CDPRODUTO, IDTPCOMPPROD, CDSUBPROD, QTSUBPROD,
          CDSUBPRALMOX, CDSUBPRLOCAL, IDSUBPRCTRLEST, IDSUBPRUTALMOX, IDSUBPRUTLCEST, IDSUBPRCTRLLOTE)
         VALUES
         (P_CDFILIAL, P_NREXPEDICAO, P_NRPEDIDO, P_NRITEMPED, C_COMPITEMPED.CDPRODUTO, 'C', C_COMPITEMPED.CDPRODCOMPED, (P_QTITEMPED * C_COMPITEMPED.QTITCOMPPROD),
          V_CDALMOXEXP, V_CDLOCALEXP, V_IDCNTRESTOQ, V_IDUTILALMOX, V_IDUTILLCESTQ, V_IDUTCTRLLOTE);
      END LOOP;
   END IF;
   IF (V_EXISTECOMPOSICAO = 0) THEN
      P_IDUTCOMP := 'N';
   ELSE
      P_IDUTCOMP := 'S';
   END IF;
END;
/
