CREATE OR REPLACE FORCE VIEW V_PREVISAO_COMPRA ("CDFILIAL", "CDPRODUTO", "QTPREVCONS", "DTPREVCONS") AS
  SELECT PC.CDFILIAL, PC.CDPRODUTO, PC.QTPREVCONS, TRUNC(PC.DTPREVCONS) AS DTPREVCONS
  FROM PREVCONS PC
 WHERE NOT EXISTS (SELECT 1
                     FROM PREVCONSTURNO
                    WHERE PREVCONSTURNO.CDFILIAL   = PC.CDFILIAL
                      AND PREVCONSTURNO.CDPRODUTO  = PC.CDPRODUTO
                      AND PREVCONSTURNO.DTPREVCONS = PC.DTPREVCONS)
    AND PC.DTPREVCONS > SYSDATE -365
  UNION ALL
  SELECT PC_PDV.CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (PC_PDV.QTPRODCONPDVDIA*P.VRFATOCONV) AS QTPREVCONS, TRUNC(PC_PDV.DTPRODCONPDV) AS DTPREVCONS
  FROM PREVCONSPDVDIA PC_PDV, PRODUTO P
  WHERE  PC_PDV.CDPRODUTO = P.CDPRODUTO
    AND NOT EXISTS (SELECT 1 FROM ROTFABRICA WHERE ROTFABRICA.CDPRODUTO = PC_PDV.CDPRODUTO)
    AND NOT EXISTS (SELECT 1
                    FROM ROTFABLOCAL
                    WHERE ROTFABLOCAL.CDPRODUTO = PC_PDV.CDPRODUTO
                      AND ROTFABLOCAL.CDFILIAL = PC_PDV.CDFILIAL)
    AND EXISTS (SELECT 1
                  FROM PREVCONSPDV PC
                 WHERE PC.CDFILIAL        = PC_PDV.CDFILIAL
                   AND PC.NRSEQPREVPDV    = PC_PDV.NRSEQPREVPDV
                   AND PC.CDPRODUTO       = PC_PDV.CDPRODUTO
                   AND PC.NRPRODCONPDV    = PC_PDV.NRPRODCONPDV
                   AND PC.IDAPROVPREVCONS = 'S')
    AND PC_PDV.DTPRODCONPDV > SYSDATE -365
  UNION ALL
  SELECT PC_PDV.CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (ROT.QTSUBPRODROT*PC_PDV.QTPRODCONPDVDIA*P.VRFATOCONV) AS QTPREVCONS, TRUNC(PC_PDV.DTPRODCONPDV) AS DTPREVCONS
  FROM PREVCONSPDVDIA PC_PDV, PRODUTO P, ROTFABRICA ROT
  WHERE  ROT.CDSUBPRODUTO = P.CDPRODUTO
    AND ROT.CDPRODUTO = PC_PDV.CDPRODUTO
    AND NOT EXISTS (SELECT 1
                    FROM ROTFABLOCAL
                    WHERE ROTFABLOCAL.CDFILIAL = PC_PDV.CDFILIAL
                      AND ROTFABLOCAL.CDPRODUTO = PC_PDV.CDPRODUTO)
    AND EXISTS (SELECT 1
                  FROM PREVCONSPDV PC
                 WHERE PC.CDFILIAL        = PC_PDV.CDFILIAL
                   AND PC.NRSEQPREVPDV    = PC_PDV.NRSEQPREVPDV
                   AND PC.CDPRODUTO       = PC_PDV.CDPRODUTO
                   AND PC.NRPRODCONPDV    = PC_PDV.NRPRODCONPDV
                   AND PC.IDAPROVPREVCONS = 'S')
    AND PC_PDV.DTPRODCONPDV > SYSDATE -365
  UNION ALL
  SELECT PC_PDV.CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (ROT.QTSUBPRODLOC*PC_PDV.QTPRODCONPDVDIA*P.VRFATOCONV) AS QTPREVCONS, TRUNC(PC_PDV.DTPRODCONPDV) AS DTPREVCONS
  FROM PREVCONSPDVDIA PC_PDV, PRODUTO P, ROTFABLOCAL ROT
  WHERE  ROT.CDSUBPRODLOC = P.CDPRODUTO
     AND ROT.CDPRODUTO = PC_PDV.CDPRODUTO
     AND ROT.CDFILIAL  = PC_PDV.CDFILIAL
     AND EXISTS (SELECT 1
                   FROM PREVCONSPDV PC
                  WHERE PC.CDFILIAL        = PC_PDV.CDFILIAL
                    AND PC.NRSEQPREVPDV    = PC_PDV.NRSEQPREVPDV
                    AND PC.CDPRODUTO       = PC_PDV.CDPRODUTO
                    AND PC.NRPRODCONPDV    = PC_PDV.NRPRODCONPDV
                    AND PC.IDAPROVPREVCONS = 'S')
     AND PC_PDV.DTPRODCONPDV > SYSDATE -365
  UNION ALL
  SELECT CDFILIALCLIE AS CDFILIAL, CDPRODUTO, QTSOLCONVMN AS QTPREVCONS, TRUNC(DTPREVENDVMN) AS DTPREVCONS
  FROM PREVCONSVMN
 WHERE DTPREVENDVMN > SYSDATE -365
  UNION ALL
  SELECT CDFILIAL, CDPRODUTO, QTPREVCONPME AS QTPREVCONS, TRUNC(DTPREVCONPME) AS DTPREVCONS
  FROM PREVCONSPME
 WHERE DTPREVCONPME > SYSDATE -365
  UNION ALL
  SELECT CDFILIAL, CDPRODUTO, QTPRODCOMP AS QTPREVCONS, TRUNC(DTPREVCOMP) AS DTPREVCONS
  FROM PREVCOMPRA
 WHERE DTPREVCOMP > SYSDATE -365
  UNION ALL
  SELECT CDFILIAL, CDPRODUTO, QTPREVCOMP AS QTPREVCONS, TRUNC(DTENTPRECOMP) AS DTPREVCONS
  FROM ITPREVCOMP
  WHERE ITPREVCOMP.IDSTITPRCOMP = 'A'
    AND ITPREVCOMP.DTENTPRECOMP > SYSDATE -365
  UNION ALL
  SELECT F.CDFILCENTPME AS CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (QTSOLIPROD*VRFATOCONV) AS QTPREVCONS, TRUNC(DTPROGENTR) AS DTPREVCONS
  FROM ITEMSOLI, FILIAL F, PRODUTO P
  WHERE /*NVL(ITEMSOLI.IDITMCONS, 'N')   = 'N'
    AND */NVL(ITEMSOLI.IDSOLIAPROV, 'S') = 'S'
    AND NVL(F.IDTPFILIALCENT, 'X') <> 'C'
    AND F.CDFILCENTPME                 IS NOT NULL
    AND ITEMSOLI.CDFILSOLI             = F.CDFILIAL
    AND ITEMSOLI.CDPRODUTO             = P.CDPRODUTO
    AND NOT EXISTS (SELECT CMPCENTENDIR.CDFILIAL, CMPCENTENDIR.CDPRODUTO
                    FROM PRODUTO PC, CMPCENTENDIR
                    WHERE CMPCENTENDIR.CDFILIAL  = ITEMSOLI.CDFILSOLI
                      AND CMPCENTENDIR.CDPRODUTO = PC.CDPRODUTO
                      AND P.CDARVPROD           LIKE PC.CDARVPROD || '%')
    AND ITEMSOLI.DTPROGENTR > SYSDATE -365
  UNION ALL
  SELECT CDFILIAL, CDPRODUTO, SUM(QTPREVCONS)AS QTPREVCONS,  DTPREVCONS
  FROM PREVCONSCOC
  WHERE IDPREVCONSCOC = 'A'
    AND DTPREVCONS > SYSDATE -365
  GROUP BY CDFILIAL, CDPRODUTO,DTPREVCONS
  UNION ALL
  SELECT CDFILIAL, CDPRODUTO, SUM(QTPREVCONS)AS QTPREVCONS,  DTPREVCONS
  FROM PREVCONSTURNO
 WHERE DTPREVCONS > SYSDATE -365
  GROUP BY CDFILIAL, CDPRODUTO,DTPREVCONS
/
