CREATE OR REPLACE VIEW BLOCOK_PRODUCAO_K230
(CDEMPRESA, CDINSCESTA, CDFILIAL, DTINICIOOP, DTFINALOP, NRORDPROD, CDPRODUTO, CDARVPROD, QTPRACABADO, 
 DTPRODACABADO, CDPRODINSUMO, CDARVPRODINSU, QTRETINSUMO, NRLANCESTQSAID, 
 DTSAIDAEST, CDINSUMOSUBST)
AS
SELECT CDEMPRESA, CDINSCESTA, CDFILIAL, DTHRINICIAOP, DTHRFIMREAOP, NRORDPROD,
       PRODACABADO, CDARVPRODACAB, QTPRACABADO, 
       DTLANCMOVI, PRODINSUMO, CDARVPRODINSU, 
       CASE WHEN QTRETPROD > 0 THEN QTRETPROD ELSE 0 END AS QTRETPROD,
       LANCESTSAID, DTLANCRETDEV, CDPRSUBSTITUIDO
FROM (
SELECT CDEMPRESA, CDINSCESTA, CDFILIAL, DTHRINICIAOP, DTHRFIMREAOP, NRORDPROD,
       PRODACABADO, CDARVPRODACAB, SUM(QTPRACABADO) AS QTPRACABADO, 
       DTLANCMOVI, PRODINSUMO, CDARVPRODINSU, 
       SUM(QTRETPROD) - SUM((SELECT NVL(SUM(QTRETPROD),0)
                            FROM  RETIRADAESTO R, ITEMRETIEST I, LOTITRETIEST LI
                          WHERE LI.CDFILIAL     = A.CDFILIAL 
                            AND LI.IDTPRETEST   = 'D'
                            AND LI.CDPRODUTO    = A.PRODINSUMO
                            AND I.CDFILIAL      = R.CDFILIAL
                            AND I.NRRETEST      = R.NRRETEST
                            AND LI.CDFILIAL     = I.CDFILIAL
                            AND LI.NRRETEST     = I.NRRETEST
                            AND LI.CDPRODUTO    = I.CDPRODUTO
                            AND R.NRORDPROD     = A.NRORDPROD
                            AND R.NRSUBORDPROD  = A.NRSUBORDPROD
                            AND TO_CHAR(LI.DTLANCRETDEV,'MM/YYYY') = TO_CHAR(A.DTLANCRETDEV,'MM/YYYY'))) AS QTRETPROD,
       LANCESTSAID, DTLANCRETDEV, CDPRSUBSTITUIDO
  FROM (SELECT F.CDEMPRESA, F.CDINSCESTA, O.DTHRINICIAOP, O.DTHRFIMREAOP,
               O.NRORDPROD, O.NRSUBORDPROD, O.CDPRODUTO AS PRODACABADO, P.CDARVPROD AS CDARVPRODACAB, 
               NVL(PA.QTLIQACUM, PA.QTPRACABADO) QTPRACABADO, L.DTLANCMOVI,
               '' AS PRODINSUMO, '' AS CDARVPRODINSU, 0 AS QTRETPROD, 
               '' AS LANCESTSAID, TO_DATE('') AS DTLANCRETDEV, '' AS CDPRSUBSTITUIDO,
               F.CDFILIAL, '' AS NRRETEST
          FROM ORDPRODUCAO O, FILIAL F, PRODACABADO PA, ITLANCTOEST L, PRODUTO P
         WHERE O.CDFILIAL      = F.CDFILIAL
           AND O.CDFILIAL      = PA.CDFILIAL
           AND O.NRORDPROD     = PA.NRORDPROD
           AND O.NRSUBORDPROD  = PA.NRSUBORDPROD
           AND PA.NRLANCESTQ   = L.NRLANCESTQ
           AND L.CDFILIAL      = O.CDFILIAL
           AND P.CDPRODUTO     = O.CDPRODUTO
         UNION ALL
        SELECT F.CDEMPRESA, F.CDINSCESTA, O.DTHRINICIAOP, O.DTHRFIMREAOP,
               O.NRORDPROD, O.NRSUBORDPROD, O.CDPRODUTO AS PRODACABADO, '' AS CDARVPRODACAB, 
               0 AS QTPRACABADO, TO_DATE('') AS DTLANCMOVI,
               I.CDPRODUTO AS PRODINSUMO, P.CDARVPROD AS CDARVPRODINSU, LI.QTRETPROD, 
               LI.NRLANCESTQ AS LANCESTSAID, LI.DTLANCRETDEV, I.CDPRSUBSTITUIDO,
               LI.CDFILIAL, LI.NRRETEST
          FROM ORDPRODUCAO O, FILIAL F, RETIRADAESTO R, ITEMRETIEST I, LOTITRETIEST LI, PRODUTO P
         WHERE O.CDFILIAL      = F.CDFILIAL
           AND R.NRORDPROD     = O.NRORDPROD
           AND R.NRSUBORDPROD  = O.NRSUBORDPROD
           AND R.CDFILIAL      = O.CDFILIAL
           AND I.CDFILIAL      = R.CDFILIAL
           AND I.NRRETEST      = R.NRRETEST
           AND LI.CDFILIAL     = I.CDFILIAL
           AND LI.NRRETEST     = I.NRRETEST
           AND P.CDPRODUTO     = I.CDPRODUTO
           AND LI.CDPRODUTO    = P.CDPRODUTO
           AND LI.IDTPRETEST   = 'R') A
 GROUP BY CDEMPRESA, CDINSCESTA, CDFILIAL, DTHRINICIAOP, DTHRFIMREAOP, NRORDPROD,
       PRODACABADO, CDARVPRODACAB, DTLANCMOVI, PRODINSUMO, CDARVPRODINSU,
       LANCESTSAID, DTLANCRETDEV, CDPRSUBSTITUIDO)
WHERE QTRETPROD > 0 OR PRODINSUMO IS NULL
/

-- VERSAO --
BEGIN ATUVEROBJ('BLOCOK_PRODUCAO_K230','6.45.030'); END;
/
