CREATE OR REPLACE
FORCE EDITIONABLE VIEW "RESERVAPED" ("NRORG",
                                            "CDFILIAL",
                                            "CDPRODUTO",
                                            "CDALMOXARIFE",
                                            "CDLOCALESTOQ",
                                            "NRLOTEESTQ",
                                            "NRSUBLOTE",
                                            "QTTOTALESTOQ",
                                            "QTRESPRODPED",
                                            "QTDISPONIVEL",
                                            "CDFILIALDEP") AS
SELECT NVL(PO.NRORG, RE.NRORG) NRORG,
       NVL(PO.CDFILIAL, RE.CDFILIAL) CDFILIAL,
       NVL(PO.CDPRODUTO, RE.CDPRODUTO) CDPRODUTO,
       NVL(PO.CDALMOXARIFE, RE.CDALMOXARIFE) CDALMOXARIFE,
       NVL(PO.CDLOCALESTOQ, RE.CDLOCALESTOQ) CDLOCALESTOQ,
       NVL(PO.NRLOTEESTQ, RE.NRLOTEESTQ) NRLOTEESTQ,
       NVL(PO.NRSUBLOTE, RE.NRSUBLOTE) NRSUBLOTE,
       PO.QTTOTALESTOQ,
       RE.QTRESPRODPED,
       NVL(PO.QTTOTALESTOQ, 0) - NVL(RE.QTRESPRODARR, 0) QTDISPONIVEL,
       NVL(RE.CDFILIALDEP, NVL(PO.CDFILIAL, RE.CDFILIAL)) CDFILIALDEP
FROM POSESTATU PO,
     PARAMFILIAL PF,

  (SELECT NRORG,
          NVL(CDFILIALDEP, CDFILIAL)CDFILIALDEP,
          CDFILIAL,
          CDPRODUTO,
          CDALMOXARIFE,
          CDLOCALESTOQ,
          NRLOTEESTQ,
          NRSUBLOTE,
          SUM(QTRESPRODARR) QTRESPRODARR,
          SUM(QTRESPRODPED) QTRESPRODPED
   FROM RESERPRODPED
   GROUP BY NRORG,
            NVL(CDFILIALDEP, CDFILIAL),
            CDFILIAL,
            CDPRODUTO,
            CDALMOXARIFE,
            CDLOCALESTOQ,
            NRLOTEESTQ,
            NRSUBLOTE) RE
WHERE PO.NRORG = RE.NRORG (+)
  AND PO.CDFILIAL = RE.CDFILIALDEP (+)
  AND PO.CDPRODUTO = RE.CDPRODUTO (+)
  AND PO.CDALMOXARIFE = RE.CDALMOXARIFE(+)
  AND PO.CDLOCALESTOQ = RE.CDLOCALESTOQ(+)
  AND PO.NRLOTEESTQ = RE.NRLOTEESTQ (+)
  AND PO.NRSUBLOTE = RE.NRSUBLOTE (+)
  AND PF.CDFILIAL = PO.CDFILIAL
  AND PF.NRORG = PO.NRORG
  AND PO.CDALMOXARIFE = DECODE(PF.IDUTILALMOX, 'S', PO.CDALMOXARIFE, ' ')
  AND PO.CDLOCALESTOQ = DECODE(PF.IDUTILLCESTQ, 'S', PO.CDLOCALESTOQ, ' ')
  AND PO.NRLOTEESTQ = DECODE(PF.IDUTILLOTE, 'S', PO.NRLOTEESTQ, ' ');