/* Teknisa - Tecnologia em Informatica

Nome: 3934_CDPFUNO_ESTOQUE_DISPONIVEL.SQL

Descricao:
Funcao para retornar a quantidade de estoque de Mat.Prima/Insumos diponivel

@c:\web_619\Sql\End_Conv\Oracle\3934_CDPFUNO_ESTOQUE_DISPONIVEL.SQL
*/


CREATE OR REPLACE FUNCTION ESTOQUE_DISPONIVEL
   (P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
    P_CDPRODUTO    IN PRODUTO.CDPRODUTO%TYPE,
    P_CDPRODINSUMO IN PRODUTO.CDPRODUTO%TYPE,
    P_CDPROCFABRIC IN PROCFABRICA.CDPROCFABRIC%TYPE,
    P_NRLOTEESTQ   IN LOTE.NRLOTEESTQ%TYPE,
    P_NRSUBLOTE    IN LOTE.NRSUBLOTE%TYPE) RETURN FLOAT
IS
   V_VRTOTALEST   POSESTDIA.VRESTOQDIA%TYPE;
   V_IDSTATUSAUDI AUDITORIA.IDSTATUSAUDI%TYPE;
   V_IDSTATLOTAUD AUDITORIA.IDSTATUSAUDI%TYPE;
   V_QTPRERETPROD ITEMRETIEST.QTPRERETPROD%TYPE;
   V_IDUTLOCESTPRACA PARAMAN.IDUTLOCESTPRACA%TYPE;
   V_RESULT       POSESTDIA.VRESTOQDIA%TYPE;
   V_OPANDAMENTO  POSESTDIA.VRESTOQDIA%TYPE;
   V_TEOR         LOTE.VRAPROVPROD%TYPE;
   V_IDBLOQINIOPSANA	PARAMAN.IDBLOQINIOPSANA%TYPE;
   V_COUNTAUDIT FLOAT;
	V_IDUTILLOTE VARCHAR2(1);
   V_IDTPAUDITADO AUDITORIA.IDTPAUDITADO%TYPE;
   V_BOINSUMO BOOLEAN;
   V_NRAUDITORIA AUDITORIA.NRAUDITORIA%TYPE;
   V_CDFILIAL ORDPRODUCAO.CDFILIAL%TYPE;
   V_NRORDPROD ORDPRODUCAO.NRORDPROD%TYPE;
   V_NRSUBORDPROD ORDPRODUCAO.NRSUBORDPROD%TYPE;
   V_ANANCANC INTEGER;
BEGIN
   V_VRTOTALEST   := 0;
   V_IDSTATLOTAUD := 'X';
   V_OPANDAMENTO := 0;
   BEGIN
      SELECT IDUTLOCESTPRACA, IDBLOQINIOPSANA
        INTO V_IDUTLOCESTPRACA,V_IDBLOQINIOPSANA
        FROM PARAMAN
       WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDUTLOCESTPRACA := 'N';
         V_IDBLOQINIOPSANA := 'N';
   END;
   FOR C_SEMIACAB IN (
                      SELECT DISTINCT I.CDPRODINSUMO, NVL( IDSEMIACETAP, 'N') AS IDSEMIACETAP, I.IDUTAPRREQET,
                                      NVL(I.IDLOTEPRETAP, 'N') AS IDLOTEPRETAP
                        FROM INSUMETAPA I, PROCFABRICA P
                       WHERE
                               P.CDPRODUTO    = P_CDPRODUTO
                           AND I.CDPRODINSUMO = P_CDPRODINSUMO
                           AND P.CDFILIAL     = P_CDFILIAL
                           AND P.CDPROCFABRIC = P_CDPROCFABRIC
                           AND I.CDPRODUTO    = P.CDPRODUTO
                           AND I.CDPROCFABRIC = P.CDPROCFABRIC
                           AND I.CDFILIAL     = P.CDFILIAL
                      ) LOOP
      FOR C_LOTE IN (SELECT P.CDFILIAL, P.CDPRODUTO, P.NRLOTEESTQ, P.NRSUBLOTE, P.QTTOTALESTOQ, L.VRAPROVPROD
                     FROM POSESTATU P, PRODFILI F, LOTE L, PARAMFILIAL PF
                     WHERE P.CDFILIAL = P_CDFILIAL
                       AND P.QTTOTALESTOQ > 0
                       AND F.CDFILIAL = P.CDFILIAL
                       AND P.CDPRODUTO = P_CDPRODINSUMO
                       AND F.CDPRODUTO = P.CDPRODUTO
                       AND ((NVL(P.CDALMOXARIFE, ' ') = DECODE(PF.IDUTILALMOX,  'S', NVL(F.CDALMOXINSPR,   ' '), NVL(P.CDALMOXARIFE, ' ')) AND
                           NVL(P.CDLOCALESTOQ, ' ') = DECODE(PF.IDUTILLCESTQ, 'S', NVL(F.CDLOCALINSPR,   ' '), NVL(P.CDLOCALESTOQ, ' '))
                           AND (C_SEMIACAB.IDSEMIACETAP = 'N'))
                           OR
                           (NVL(P.CDALMOXARIFE, ' ') = DECODE(PF.IDUTILALMOX,  'S', NVL(F.CDALMOXPRSEMACA,   ' '), NVL(P.CDALMOXARIFE, ' ')) AND
                            NVL(P.CDLOCALESTOQ, ' ') = DECODE(PF.IDUTILLCESTQ, 'S', NVL(F.CDLOCALPRSEMACA,   ' '), NVL(P.CDLOCALESTOQ, ' '))
                           AND (C_SEMIACAB.IDSEMIACETAP = 'S')))
                       AND V_IDUTLOCESTPRACA = 'N'
                       AND P.CDFILIAL = L.CDFILIAL
                       AND P.NRLOTEESTQ = L.NRLOTEESTQ
                       AND P.NRSUBLOTE = L.NRSUBLOTE
                       AND P.CDPRODUTO = L.CDPRODUTO
                       AND P.CDFILIAL  = PF.CDFILIAL
                       AND (((TRUNC(NVL(L.DTVALILOTE, SYSDATE)) >= TRUNC(SYSDATE)) AND (F.IDUTCTRLLOTE = 'S')) OR
                            (F.IDUTCTRLLOTE <> 'S'))
                       AND (((C_SEMIACAB.IDLOTEPRETAP = 'S') AND (P.NRLOTEESTQ = P_NRLOTEESTQ) AND (P.NRSUBLOTE = P_NRSUBLOTE)) OR
                            (C_SEMIACAB.IDLOTEPRETAP = 'N'))
                     UNION
                     SELECT P.CDFILIAL, P.CDPRODUTO, P.NRLOTEESTQ, P.NRSUBLOTE, P.QTTOTALESTOQ, L.VRAPROVPROD
                     FROM POSESTATU P, PRODFILI F, LOTE L, PARAMFILIAL PF
                     WHERE P.CDFILIAL = P_CDFILIAL
                       AND P.QTTOTALESTOQ > 0
                       AND F.CDFILIAL = P.CDFILIAL
                       AND P.CDPRODUTO = P_CDPRODINSUMO
                       AND F.CDPRODUTO = P_CDPRODUTO
                       AND ((NVL(P.CDALMOXARIFE, ' ') = DECODE(PF.IDUTILALMOX,  'S', NVL(F.CDALMOXINSPR,   ' '), NVL(P.CDALMOXARIFE, ' ')) AND
                           NVL(P.CDLOCALESTOQ, ' ') = DECODE(PF.IDUTILLCESTQ, 'S', NVL(F.CDLOCALINSPR,   ' '), NVL(P.CDLOCALESTOQ, ' '))
                           AND (C_SEMIACAB.IDSEMIACETAP = 'N'))
                           OR
                           (NVL(P.CDALMOXARIFE, ' ') = DECODE(PF.IDUTILALMOX,  'S', NVL(F.CDALMOXPRSEMACA,   ' '), NVL(P.CDALMOXARIFE, ' ')) AND
                            NVL(P.CDLOCALESTOQ, ' ') = DECODE(PF.IDUTILLCESTQ, 'S', NVL(F.CDLOCALPRSEMACA,   ' '), NVL(P.CDLOCALESTOQ, ' '))
                           AND (C_SEMIACAB.IDSEMIACETAP = 'S')))
                       AND V_IDUTLOCESTPRACA = 'S'
                       AND P.CDFILIAL = L.CDFILIAL
                       AND P.NRLOTEESTQ = L.NRLOTEESTQ
                       AND P.NRSUBLOTE = L.NRSUBLOTE
                       AND P.CDPRODUTO = L.CDPRODUTO
                       AND P.CDFILIAL  = PF.CDFILIAL
                       AND (((TRUNC(NVL(L.DTVALILOTE, SYSDATE)) >= TRUNC(SYSDATE)) AND (F.IDUTCTRLLOTE = 'S')) OR
                            (F.IDUTCTRLLOTE <> 'S'))
                       AND (((C_SEMIACAB.IDLOTEPRETAP = 'S') AND (P.NRLOTEESTQ = P_NRLOTEESTQ) AND (P.NRSUBLOTE = P_NRSUBLOTE)) OR
                            (C_SEMIACAB.IDLOTEPRETAP = 'N'))
      ) LOOP
          IF V_IDSTATLOTAUD = 'X' THEN
             V_BOINSUMO := TRUE;
             SELECT MAX(IDTPAUDITADO)
               INTO V_IDTPAUDITADO
               FROM AUDITORIA
              WHERE IDORIGAUDIT  = 'ANA'
                AND CDFILIAL = C_LOTE.CDFILIAL
                AND NRAUDITORIA = (SELECT MAX(NRAUDITORIA)
                                     FROM AUDITORIA
                                    WHERE CDFILIAL = C_LOTE.CDFILIAL
                                      AND CDPRODUTO = C_LOTE.CDPRODUTO
                                      AND NVL(NRLOTEESTQ,' ') = NVL(C_LOTE.NRLOTEESTQ,' ')
                                      AND NVL(NRSUBLOTE,' ') = NVL(C_LOTE.NRSUBLOTE,' ')
                                      AND IDORIGAUDIT  = 'ANA');

             IF (V_IDTPAUDITADO IS NULL) THEN
                V_COUNTAUDIT := 0;
             ELSE
                IF (V_IDTPAUDITADO IN ('P', 'A')) THEN
                   V_BOINSUMO := FALSE;
                   SELECT MAX(NRAUDITORIA)
                     INTO V_NRAUDITORIA
                     FROM AUDITORIA
                    WHERE CDFILIAL = C_LOTE.CDFILIAL
                      AND CDPRODUTO = C_LOTE.CDPRODUTO
                      AND NVL(NRLOTEESTQ,' ') = NVL(C_LOTE.NRLOTEESTQ,' ')
                      AND NVL(NRSUBLOTE,' ') = NVL(C_LOTE.NRSUBLOTE,' ')
                      AND IDORIGAUDIT  = 'ANA'
                      AND IDTPAUDITADO IN ('P','A');

                   SELECT MAX(CDFILIAL), MAX(NRORDPROD), MAX(NRSUBORDPROD)
                     INTO V_CDFILIAL, V_NRORDPROD, V_NRSUBORDPROD
                     FROM AUDITORIA
                    WHERE CDFILIAL = C_LOTE.CDFILIAL
                      AND NRAUDITORIA = V_NRAUDITORIA;

                   IF (V_NRORDPROD IS NULL) THEN
                      V_BOINSUMO := TRUE;
                   ELSE

                      V_COUNTAUDIT := 0;
                      BEGIN
                         SELECT COUNT(1)
                         INTO V_COUNTAUDIT
                         FROM AUDITORIA
                         WHERE IDORIGAUDIT  = 'ANA'
                           AND IDTPAUDITADO IN ('P', 'A')
                           AND CDFILIAL = C_LOTE.CDFILIAL
                           AND NRORDPROD = V_NRORDPROD
                           AND NRSUBORDPROD = V_NRSUBORDPROD;
                      EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                         V_COUNTAUDIT := 0;
                      END;

                      V_ANANCANC := 0;
                      SELECT COUNT(1)
                        INTO V_ANANCANC
                        FROM AUDITORIA
                       WHERE IDORIGAUDIT  = 'ANA'
                         AND CDFILIAL = V_CDFILIAL
                         AND NRORDPROD = V_NRORDPROD
                         AND NRSUBORDPROD = V_NRSUBORDPROD
                         AND IDSTATUSAUDI <> '3';

                      IF V_ANANCANC > 0 THEN
                         SELECT MAX(IDSTATUSAUDI)
                           INTO V_IDSTATUSAUDI
                           FROM AUDITORIA
                          WHERE IDORIGAUDIT  = 'ANA'
                            AND CDFILIAL = V_CDFILIAL
                            AND NRORDPROD = V_NRORDPROD
                            AND NRSUBORDPROD = V_NRSUBORDPROD
                            AND IDSTATUSAUDI IN ('1', '2', '5');
                      ELSE
                         SELECT MAX(IDSTATUSAUDI)
                           INTO V_IDSTATUSAUDI
                           FROM AUDITORIA
                          WHERE IDORIGAUDIT  = 'ANA'
                            AND CDFILIAL = V_CDFILIAL
                            AND NRORDPROD = V_NRORDPROD
                            AND NRSUBORDPROD = V_NRSUBORDPROD
                            AND IDSTATUSAUDI = '3';
                      END IF;
                   END IF;
                END IF;
                IF V_BOINSUMO THEN

                   BEGIN
                      SELECT IDSTATUSAUDI
                      INTO V_IDSTATUSAUDI
                      FROM AUDITORIA
                      WHERE IDORIGAUDIT  = 'ANA'
                        AND CDFILIAL = C_LOTE.CDFILIAL
                        AND NRAUDITORIA = (SELECT MAX(NRAUDITORIA)
                                             FROM AUDITORIA
                                            WHERE CDFILIAL = C_LOTE.CDFILIAL
                                              AND CDPRODUTO = C_LOTE.CDPRODUTO
                                              AND NVL(NRLOTEESTQ,' ') = NVL(C_LOTE.NRLOTEESTQ,' ')
                                              AND NVL(NRSUBLOTE,' ') = NVL(C_LOTE.NRSUBLOTE,' ')
                                              AND IDORIGAUDIT  = 'ANA'
                                         )
                        AND IDSTATUSAUDI <> '4';
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                      V_IDSTATUSAUDI := NULL;
                   END;

                   V_COUNTAUDIT := 0;
                   BEGIN
                      SELECT COUNT(1)
                      INTO V_COUNTAUDIT
                      FROM AUDITORIA
                      WHERE IDORIGAUDIT  = 'ANA'
                        AND CDFILIAL = C_LOTE.CDFILIAL
                        AND NRAUDITORIA = (SELECT MAX(NRAUDITORIA)
                                             FROM AUDITORIA
                                            WHERE CDFILIAL = C_LOTE.CDFILIAL
                                              AND CDPRODUTO = C_LOTE.CDPRODUTO
                                              AND NVL(NRLOTEESTQ,' ') = NVL(C_LOTE.NRLOTEESTQ,' ')
                                              AND NVL(NRSUBLOTE,' ') = NVL(C_LOTE.NRSUBLOTE,' ')
                                              AND IDORIGAUDIT  = 'ANA');

                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                      V_COUNTAUDIT := 0;
                   END;
                END IF;
             END IF;
				BEGIN
					SELECT IDUTCTRLLOTE
					INTO 	V_IDUTILLOTE
					FROM PRODFILI
					WHERE CDFILIAL = P_CDFILIAL
					AND CDPRODUTO =  P_CDPRODINSUMO;
				EXCEPTION
               WHEN NO_DATA_FOUND THEN
               V_IDUTILLOTE := 'N';
            END;
				-- SE O PRODUTO CONTROLAR LOTE, DEVE EXISTIR ANALISE (A NAO SER QUE O PARAMETRO V_IDBLOQINIOPSANA = 'N') E NAFO PODE EXISTIR ANALISE <> CONCLUADA OK )
				iF (((V_COUNTAUDIT <> 0 OR V_IDBLOQINIOPSANA = 'N')  AND V_IDSTATUSAUDI IS NULL) OR (V_IDUTILLOTE = 'N')) THEN
               IF (C_SEMIACAB.IDUTAPRREQET = 'S') THEN
                  IF (NVL(C_LOTE.VRAPROVPROD,0) = 0) THEN
                     V_TEOR := 100;
                  ELSE
                     V_TEOR := C_LOTE.VRAPROVPROD;
                  END IF;
               ELSE
                  V_TEOR := 100;
               END IF;
               V_VRTOTALEST := (V_VRTOTALEST + ((C_LOTE.QTTOTALESTOQ *V_TEOR)/100));
               SELECT NVL(SUM(L.QTRETLIQPROD),0)
               INTO V_QTPRERETPROD
               FROM ORDPRODUCAO O, RETIRADAESTO R, ITEMRETIEST I, LOTITRETIEST L
               WHERE O.CDFILIAL = P_CDFILIAL
               AND O.IDSITORDPROD = '2'
               AND O.CDFILIAL  = R.CDFILIAL
               AND O.NRORDPROD = R.NRORDPROD
               AND O.NRSUBORDPROD = R.NRSUBORDPROD
               AND R.NRORDPROD IS NOT NULL
               AND R.NRLANCESTQ IS NULL
               AND R.CDFILIAL = I.CDFILIAL
               AND R.NRRETEST = I.NRRETEST
               AND I.CDPRODUTO = P_CDPRODINSUMO
               AND I.CDFILIAL  = L.CDFILIAL
               AND I.NRRETEST  = L.NRRETEST
               AND I.CDPRODUTO = L.CDPRODUTO
               AND I.NRSEQRETPROD = L.NRSEQRETPROD
               AND L.IDTPRETEST = 'R'
               AND NVL(L.NRLOTEESTQ,' ') = NVL(C_LOTE.NRLOTEESTQ,' ')
               AND NVL(L.NRSUBLOTE,' ')  = NVL(C_LOTE.NRSUBLOTE,' ');
               V_OPANDAMENTO := V_OPANDAMENTO + V_QTPRERETPROD;
            END IF;
          END IF;
      END LOOP;
      V_VRTOTALEST := V_VRTOTALEST - V_OPANDAMENTO;
   END LOOP;
   RETURN(V_VRTOTALEST);
END;
/