CREATE OR REPLACE VIEW PRAZO_MEDIO_AF AS
SELECT
   CDFILDEST,
   CDFILLANC,
   NRAUTOFORN,
   DTPROGENTAF,
   CDPRODUTO,
   CDMARCA,
   VRPRAZOMED,
   IDTPFORPGTAF
FROM
(
SELECT
   ITEMAUTF.CDFILDEST,
   ITEMAUTF.CDFILLANC,
   ITEMAUTF.NRAUTOFORN,
   ITEMAUTF.DTPROGENTAF,
   ITEMAUTF.CDPRODUTO,
   ITEMAUTF.CDMARCA,
   SUM((PRAZOPGTAF.NRDIASPGTOAF * ((ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF) * (PRAZOPGTAF.VRPERCPGTOAF/100)))/ CASE (ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF)
                                                                                                                      WHEN 0
                                                                                                                      THEN 1
                                                                                                                      ELSE (ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF)
                                                                                                                   END) AS VRPRAZOMED,
   AUTOFORNE.IDTPFORPGTAF
FROM
   PRAZOPGTAF,
   AUTOFORNE,
   ITEMAUTF
WHERE
   PRAZOPGTAF.CDFILDEST  = ITEMAUTF.CDFILDEST AND
   PRAZOPGTAF.CDFILLANC  = ITEMAUTF.CDFILLANC AND
   PRAZOPGTAF.NRAUTOFORN = ITEMAUTF.NRAUTOFORN AND
   AUTOFORNE.CDFILDEST  = ITEMAUTF.CDFILDEST AND
   AUTOFORNE.CDFILLANC  = ITEMAUTF.CDFILLANC AND
   AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN AND
   AUTOFORNE.IDTPFORPGTAF = 'V' AND
   AUTOFORNE.IDAFFORALIM            = 'N' AND
   AUTOFORNE.IDSITUATUAAF IS NULL
GROUP BY
   ITEMAUTF.CDFILDEST,
   ITEMAUTF.CDFILLANC,
   ITEMAUTF.NRAUTOFORN,
   ITEMAUTF.DTPROGENTAF,
   ITEMAUTF.CDPRODUTO,
   ITEMAUTF.CDMARCA,
   AUTOFORNE.IDTPFORPGTAF
UNION ALL
SELECT
   ITEM.CDFILDEST,
   ITEM.CDFILLANC,
   ITEM.NRAUTOFORN,
   ITEM.DTPROGENTAF,
   ITEM.CDPRODUTO,
   ITEM.CDMARCA,
   AVG(ADD_MONTHS(TO_DATE(TO_DATE(LAST_DAY(ADD_MONTHS(ITEM.DTPROGENTAF, - 1)) + ITEM.NRDIAPGFIXAF)), TO_NUMBER(TO_CHAR(DTDATA, 'DD')) - 1) - ITEM.DTPROGENTAF) AS VRPRAZOMED,
   ITEM.IDTPFORPGTAF
FROM
   DATA,
(SELECT
   ITEMAUTF.CDFILDEST,
   ITEMAUTF.CDFILLANC,
   ITEMAUTF.NRAUTOFORN,
   ITEMAUTF.DTPROGENTAF,
   ITEMAUTF.CDPRODUTO,
   ITEMAUTF.CDMARCA,
   AUTOFORNE.NRPARCPGTOAF,
   AUTOFORNE.NRDIAPGFIXAF,
   AUTOFORNE.IDTPFORPGTAF
FROM
   AUTOFORNE,
   ITEMAUTF
WHERE
   (ITEMAUTF.CDFILDEST = AUTOFORNE.CDFILDEST) AND
   (ITEMAUTF.CDFILLANC = AUTOFORNE.CDFILLANC) AND
   (ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN) AND
   (AUTOFORNE.IDTPFORPGTAF = 'F') AND
   (AUTOFORNE.IDUTDIAPGTAF = 'I') AND
   (AUTOFORNE.IDAFFORALIM  = 'N') AND
   (AUTOFORNE.IDSITUATUAAF IS NULL)  AND
   (TO_DATE(LAST_DAY(ADD_MONTHS(ITEMAUTF.DTPROGENTAF, -1)) + AUTOFORNE.NRDIAPGFIXAF) > ITEMAUTF.DTPROGENTAF)
) ITEM
WHERE
   DATA.DTDATA BETWEEN TO_DATE('31/12/1999') + 1 AND TO_DATE('31/12/1999') + ITEM.NRPARCPGTOAF
GROUP BY
   ITEM.CDFILDEST,
   ITEM.CDFILLANC,
   ITEM.NRAUTOFORN,
   ITEM.DTPROGENTAF,
   ITEM.CDPRODUTO,
   ITEM.CDMARCA,
   ITEM.IDTPFORPGTAF
UNION ALL
SELECT
   ITEM.CDFILDEST,
   ITEM.CDFILLANC,
   ITEM.NRAUTOFORN,
   ITEM.DTPROGENTAF,
   ITEM.CDPRODUTO,
   ITEM.CDMARCA,
   AVG(ADD_MONTHS(TO_DATE(TO_DATE(LAST_DAY(ITEM.DTPROGENTAF) + ITEM.NRDIAPGFIXAF)), TO_NUMBER(TO_CHAR(DTDATA, 'DD')) - 1) - ITEM.DTPROGENTAF) AS VRPRAZOMED,
   ITEM.IDTPFORPGTAF
FROM
   DATA,
(SELECT
   ITEMAUTF.CDFILDEST,
   ITEMAUTF.CDFILLANC,
   ITEMAUTF.NRAUTOFORN,
   ITEMAUTF.DTPROGENTAF,
   ITEMAUTF.CDPRODUTO,
   ITEMAUTF.CDMARCA,
   AUTOFORNE.NRPARCPGTOAF,
   AUTOFORNE.NRDIAPGFIXAF,
   AUTOFORNE.IDTPFORPGTAF
FROM
   AUTOFORNE,
   ITEMAUTF
WHERE
   (ITEMAUTF.CDFILDEST = AUTOFORNE.CDFILDEST) AND
   (ITEMAUTF.CDFILLANC = AUTOFORNE.CDFILLANC) AND
   (ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN) AND
   (AUTOFORNE.IDTPFORPGTAF = 'F') AND
   (AUTOFORNE.IDUTDIAPGTAF = 'I') AND
   (AUTOFORNE.IDAFFORALIM  = 'N') AND
   (AUTOFORNE.IDSITUATUAAF IS NULL)  AND
   (TO_DATE(LAST_DAY(ADD_MONTHS(ITEMAUTF.DTPROGENTAF, -1)) + AUTOFORNE.NRDIAPGFIXAF) <= ITEMAUTF.DTPROGENTAF)
) ITEM
WHERE
   DATA.DTDATA BETWEEN TO_DATE('31/12/1999') + 1 AND TO_DATE('31/12/1999') + ITEM.NRPARCPGTOAF
GROUP BY
   ITEM.CDFILDEST,
   ITEM.CDFILLANC,
   ITEM.NRAUTOFORN,
   ITEM.DTPROGENTAF,
   ITEM.CDPRODUTO,
   ITEM.CDMARCA,
   ITEM.IDTPFORPGTAF
UNION ALL
SELECT
   ITEM.CDFILDEST,
   ITEM.CDFILLANC,
   ITEM.NRAUTOFORN,
   ITEM.DTPROGENTAF,
   ITEM.CDPRODUTO,
   ITEM.CDMARCA,
   AVG(ADD_MONTHS(TO_DATE(TO_DATE(LAST_DAY(ITEM.DTPROGENTAF) + ITEM.NRDIAPGFIXAF)), TO_NUMBER(TO_CHAR(DTDATA, 'DD')) - 1) - ITEM.DTPROGENTAF) AS VRPRAZOMED,
   ITEM.IDTPFORPGTAF
FROM
   DATA,
(SELECT
   ITEMAUTF.CDFILDEST,
   ITEMAUTF.CDFILLANC,
   ITEMAUTF.NRAUTOFORN,
   ITEMAUTF.DTPROGENTAF,
   ITEMAUTF.CDPRODUTO,
   ITEMAUTF.CDMARCA,
   AUTOFORNE.NRPARCPGTOAF,
   AUTOFORNE.NRDIAPGFIXAF,
   AUTOFORNE.IDTPFORPGTAF
FROM
   AUTOFORNE,
   ITEMAUTF
WHERE
   (ITEMAUTF.CDFILDEST = AUTOFORNE.CDFILDEST) AND
   (ITEMAUTF.CDFILLANC = AUTOFORNE.CDFILLANC) AND
   (ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN) AND
   (AUTOFORNE.IDTPFORPGTAF = 'F') AND
   (AUTOFORNE.IDUTDIAPGTAF = 'E') AND
   (AUTOFORNE.IDAFFORALIM  = 'N') AND
   (AUTOFORNE.IDSITUATUAAF IS NULL)
) ITEM
WHERE
   DATA.DTDATA BETWEEN TO_DATE('31/12/1999') + 1 AND TO_DATE('31/12/1999') + ITEM.NRPARCPGTOAF
GROUP BY
   ITEM.CDFILDEST,
   ITEM.CDFILLANC,
   ITEM.NRAUTOFORN,
   ITEM.DTPROGENTAF,
   ITEM.CDPRODUTO,
   ITEM.CDMARCA,
   ITEM.IDTPFORPGTAF
UNION ALL
SELECT
   ITEMAUTF.CDFILDEST,
   ITEMAUTF.CDFILLANC,
   ITEMAUTF.NRAUTOFORN,
   ITEMAUTF.DTPROGENTAF,
   ITEMAUTF.CDPRODUTO,
   ITEMAUTF.CDMARCA,
   0.0 AS VRPRAZOMED,
   AUTOFORNE.IDTPFORPGTAF
FROM
   AUTOFORNE,
   ITEMAUTF
WHERE
   (ITEMAUTF.CDFILDEST = AUTOFORNE.CDFILDEST) AND
   (ITEMAUTF.CDFILLANC = AUTOFORNE.CDFILLANC) AND
   (ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN) AND
   (AUTOFORNE.IDTPFORPGTAF IS NULL) AND
   (AUTOFORNE.IDAFFORALIM  = 'N') AND
   (AUTOFORNE.IDSITUATUAAF IS NULL)
)
/