CREATE OR REPLACE FORCE VIEW V_PREVISAO_COMPRA_FILIAL ("CDFILIAL", "CDPRODUTO", "QTPREVCONS", "DTPREVCONS") AS
  SELECT FN.CDFILIAL, PC.CDPRODUTO, PC.QTPREVCONS, TRUNC(PC.DTPREVCONS) AS DTPREVCONS
  FROM PREVCONS PC, FILIAL F, FILINECE FN
  WHERE NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC.CDFILIAL              = FN.CDFILINECCMP
    AND PC.DTPREVCONS > SYSDATE -365
    AND NOT EXISTS (SELECT 1
                      FROM PREVCONSTURNO
                     WHERE PREVCONSTURNO.CDFILIAL   = PC.CDFILIAL
                       AND PREVCONSTURNO.CDPRODUTO  = PC.CDPRODUTO
                       AND PREVCONSTURNO.DTPREVCONS = PC.DTPREVCONS)
  UNION ALL
  SELECT FN.CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (PC_PDV.QTPRODCONPDVDIA*P.VRFATOCONV) AS QTPREVCONS, TRUNC(PC_PDV.DTPRODCONPDV) AS DTPREVCONS
  FROM PREVCONSPDVDIA PC_PDV, FILIAL F, FILINECE FN, PRODUTO P
  WHERE PC_PDV.CDPRODUTO         = P.CDPRODUTO
    AND NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC_PDV.CDFILIAL          = FN.CDFILINECCMP
    AND PC_PDV.DTPRODCONPDV      > SYSDATE -365
    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')
  UNION ALL
  SELECT FN.CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (ROT.QTSUBPRODROT*PC_PDV.QTPRODCONPDVDIA*P.VRFATOCONV) AS QTPREVCONS, TRUNC(PC_PDV.DTPRODCONPDV) AS DTPREVCONS
  FROM PREVCONSPDVDIA PC_PDV, FILIAL F, FILINECE FN, PRODUTO P, ROTFABRICA ROT
  WHERE ROT.CDSUBPRODUTO = P.CDPRODUTO
    AND ROT.CDPRODUTO = PC_PDV.CDPRODUTO
    AND NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC_PDV.CDFILIAL          = FN.CDFILINECCMP
    AND PC_PDV.DTPRODCONPDV      > SYSDATE -365
    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')
  UNION ALL
  SELECT FN.CDFILIAL, P.CDPRODESTO AS CDPRODUTO, (ROT.QTSUBPRODLOC*PC_PDV.QTPRODCONPDVDIA*P.VRFATOCONV) AS QTPREVCONS, TRUNC(PC_PDV.DTPRODCONPDV) AS DTPREVCONS
  FROM PREVCONSPDVDIA PC_PDV, FILIAL F, FILINECE FN, PRODUTO P, ROTFABLOCAL ROT
  WHERE ROT.CDSUBPRODLOC = P.CDPRODUTO
    AND ROT.CDPRODUTO = PC_PDV.CDPRODUTO
    AND ROT.CDFILIAL  = PC_PDV.CDFILIAL
    AND NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC_PDV.CDFILIAL          = FN.CDFILINECCMP
    AND PC_PDV.DTPRODCONPDV      > SYSDATE -365
    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')
  UNION ALL
  SELECT FN.CDFILIAL, CDPRODUTO, QTSOLCONVMN AS QTPREVCONS, TRUNC(DTPREVENDVMN) AS DTPREVCONS
  FROM PREVCONSVMN PC, FILIAL F, FILINECE FN
  WHERE NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC.CDFILIALCLIE          = FN.CDFILINECCMP
    AND PC.DTPREVENDVMN          > SYSDATE -365
  UNION ALL
  SELECT FN.CDFILIAL, PC.CDPRODUTO, PC.QTPREVCONPME AS QTPREVCONS, PC.DTPREVCONPME AS DTPREVCONS
  FROM PREVCONSPME PC, FILIAL F, FILINECE FN
  WHERE NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC.CDFILIAL              = FN.CDFILINECCMP
    AND PC.DTPREVCONPME          > SYSDATE -365
  UNION ALL
  SELECT FN.CDFILIAL, PC.CDPRODUTO, PC.QTPRODCOMP AS QTPREVCONS, TRUNC(PC.DTPREVCOMP) AS DTPREVCONS
  FROM PREVCOMPRA PC, FILIAL F, FILINECE FN
  WHERE NVL(F.IDFILCENTPME, 'N') = 'N'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC.CDFILIAL              = FN.CDFILINECCMP
    AND PC.DTPREVCOMP            > SYSDATE -365
  UNION ALL
  SELECT FN.CDFILIAL, PC.CDPRODUTO, PC.QTPREVCOMP AS QTPREVCONS, TRUNC(PC.DTENTPRECOMP) AS DTPREVCONS
  FROM ITPREVCOMP PC, FILIAL F, FILINECE FN
  WHERE NVL(F.IDFILCENTPME, 'N') = 'N'
    AND PC.IDSTITPRCOMP          = 'A'
    AND FN.IDSOLIPREV            = 'P'
    AND FN.CDFILIAL              = F.CDFILIAL
    AND PC.CDFILIAL              = FN.CDFILINECCMP
    AND PC.DTENTPRECOMP          > SYSDATE -365
  UNION ALL
  SELECT FI.CDFILIAL, PR.CDPRODESTO AS CDPRODUTO, (IT.QTSOLIPROD*PR.VRFATOCONV) AS QTPREVCONS, IT.DTPROGENTR AS DTPREVCONS
  FROM ITEMSOLI IT, PRODUTO PR, FILINECE FI, FILIAL
  WHERE FI.CDFILIAL                    = FILIAL.CDFILIAL
    AND NVL(FILIAL.IDFILCENTPME, 'N')  = 'N'
    AND FI.CDFILINECCMP                = IT.CDFILSOLI
    AND FI.IDSOLIPREV                  = 'S'
    AND IT.CDPRODUTO                   = PR.CDPRODUTO
    AND NVL(FILIAL.IDTPFILIALCENT, 'X') <> 'C'
    AND IT.DTPROGENTR                  > SYSDATE -365
  UNION ALL
  SELECT FN.CDFILIAL, PR.CDPRODUTO, SUM(PR.QTPREVCONS)AS QTPREVCONS,  PR.DTPREVCONS
  FROM PREVCONSCOC PR, FILINECE FN,FILIAL F
  WHERE NVL(F.IDFILCENTPME, 'N') = 'N'
    AND PR.IDPREVCONSCOC         = 'A'
    AND FN.IDSOLIPREV            = 'P'
    AND F.CDFILIAL               = FN.CDFILIAL
    AND PR.CDFILIAL              = FN.CDFILINECCMP
    AND PR.DTPREVCONS            > SYSDATE -365
  GROUP BY FN.CDFILIAL, PR.CDPRODUTO,PR.DTPREVCONS
  UNION ALL
  SELECT CDFILIAL, CDPRODUTO, SUM(QTPREVCONS)AS QTPREVCONS,  DTPREVCONS
  FROM PREVCONSTURNO
 WHERE DTPREVCONS               > SYSDATE -365  
  GROUP BY CDFILIAL, CDPRODUTO, DTPREVCONS
/