CREATE OR REPLACE VIEW ESTSEMIACABADO
AS
SELECT P.CDFILIAL,
       P.CDPRODUTO,
       P.CDALMOXARIFE,
       P.CDLOCALESTOQ,
       P.NRLOTEESTQ,
       P.NRSUBLOTE,
       P.QTTOTALESTOQ,
       L.VRAPROVPROD,
       L.DTVALILOTE,
       PROD.CDALMOXPRACA,
       PROD.CDLOCALPRACA,
       PROD.VRCUSTMEDATU
FROM LOTE L,PRODFILI PROD, POSESTATU P, PARAMFILIAL PA
WHERE (PROD.CDFILIAL     = P.CDFILIAL)     AND
      (PROD.CDPRODUTO    = P.CDPRODUTO)    AND
      (PROD.CDALMOXPRSEMACA = P.CDALMOXARIFE) AND
      (PROD.CDLOCALPRSEMACA = P.CDLOCALESTOQ) AND
      (P.CDFILIAL        = L.CDFILIAL)     AND
      (P.NRLOTEESTQ      = L.NRLOTEESTQ)   AND
      (P.NRSUBLOTE       = L.NRSUBLOTE)    AND
      (P.CDPRODUTO       = L.CDPRODUTO)    AND
      (P.QTTOTALESTOQ > 0)                 AND
      (PA.CDFILIAL       = P.CDFILIAL)     AND
      (PA.IDUTILALMOX    = 'S')

UNION

SELECT P.CDFILIAL,
       P.CDPRODUTO,
       P.CDALMOXARIFE,
       P.CDLOCALESTOQ,
       P.NRLOTEESTQ,
       P.NRSUBLOTE,
       P.QTTOTALESTOQ,
       L.VRAPROVPROD,
       L.DTVALILOTE,
       ' ' AS CDALMOXPRACA,
       ' ' AS CDLOCALPRACA,
       0 AS VRCUSTMEDATU
FROM LOTE L, POSESTATU P, PARAMFILIAL PA
WHERE (P.CDFILIAL        = L.CDFILIAL)   AND
      (P.NRLOTEESTQ      = L.NRLOTEESTQ) AND
      (P.NRSUBLOTE       = L.NRSUBLOTE)  AND
      (P.CDPRODUTO       = L.CDPRODUTO)  AND
      (P.QTTOTALESTOQ > 0)               AND
      (PA.CDFILIAL       = P.CDFILIAL)   AND
      (PA.IDUTILALMOX    <> 'S')
/

-- VERSAO --
BEGIN ATUVEROBJ('ESTSEMIACABADO','5.07.004'); END;
/

