DROP TYPE APURA_NEGOC_ULTCOMP_TABLE
/

DROP TYPE APURA_NEGOC_ULTCOMP_TYPE
/

CREATE TYPE APURA_NEGOC_ULTCOMP_TYPE AS
OBJECT( CDFORNITMAP VARCHAR2(21),
        NMFANTFORN  VARCHAR2(40),
        NRSEQITMAP  VARCHAR2(10),
        CDCENTITMAP VARCHAR2(4),
        CDREGITMAP  VARCHAR2(3),
        NRCOLEITMAP VARCHAR2(10),
        CDPRODITMAP VARCHAR2(10),
        CDARVPROD   VARCHAR2(20),
        CDMARCITMAP VARCHAR2(5),
        NMMARCA     VARCHAR2(20),
        IDITMSELAPUR VARCHAR2(1),
        SGUNIDADE   VARCHAR2(10),
        IDQTMINFORN VARCHAR2(1),
        IDVRMINFORN VARCHAR2(1),
        VRPRECUNIAP FLOAT,
        PRECOECON   FLOAT,
        PRECOECONPI FLOAT,
        QTACOMP     FLOAT,
        ORDENA      FLOAT,
        QTMINAUTFOT FLOAT,
        VRMINAUTFOR FLOAT,
        VRFATOCONV  FLOAT,
        PRODUNDIVFATCO FLOAT,
        VRULTCOTA   FLOAT,
        VRULTVENDA  FLOAT,
        VRPRECTOTAP FLOAT)
/

CREATE TYPE APURA_NEGOC_ULTCOMP_TABLE AS TABLE OF APURA_NEGOC_ULTCOMP_TYPE
/

CREATE OR REPLACE FUNCTION APURA_NEGOC_ULTCOMP (P_CDCENTCMP IN VARCHAR2,
                                                P_NRSEQAPUR IN VARCHAR2) RETURN APURA_NEGOC_ULTCOMP_TABLE PIPELINED
IS
   V_VRCOTACAO FLOAT;
   V_VRPREUNIAF FLOAT;
   OUT_RECORD APURA_NEGOC_ULTCOMP_TYPE := APURA_NEGOC_ULTCOMP_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                                                    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                                                    NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
  FOR C_ITEM IN ( SELECT ITMAPURPREC.CDFORNITMAP, FORNECEDOR.NMFANTFORN, ITMAPURPREC.NRSEQITMAP,
                         ITMAPURPREC.CDCENTITMAP, ITMAPURPREC.CDREGITMAP, ITMAPURPREC.NRCOLEITMAP,
                         ITMAPURPREC.CDPRODITMAP, PRODUTO.CDARVPROD, ITMAPURPREC.CDMARCITMAP,
                         MARCA.NMMARCA,
                         0 AS PRECOECON, 0 AS PRECOECONPI,
                         MIN(DECODE(NVL(PARASUPRI.IDTIPOAPUR, '0'), '0', ITMAPURPREC.VRPRECUNIAP, '1', ITMAPURPREC.VRPRECECOAP, ITMAPURPREC.VRPRECONPIAP)) VRPRECUNIAP, 
                         FORNECEDOR.SGUNIDADE, NVL(MINFORNREG.QTMINFORNREG, FORNECEDOR.QTMINAUTFOT) AS QTMINAUTFOT,
                         DECODE(MINFORNREG.QTMINFORNREG, NULL, 'F', 'R') AS IDQTMINFORN,
                         NVL(MINFORNREG.VRMINFORNREG, FORNECEDOR.VRMINAUTFOR) AS VRMINAUTFOR,
                         DECODE(MINFORNREG.VRMINFORNREG, NULL, 'F', 'R') AS IDVRMINFORN,
                         PRODUTO.VRFATOCONV, ITMAPURPREC.VRPRECUNIAP/PRODUTO.VRFATOCONV AS PRODUNDIVFATCO,
                         SUM(ITMAPURPREC.QTSOLACOMP) AS QTACOMP,
                         MAX(ITMAPURPREC.IDITMSELAPUR) AS IDITMSELAPUR,
                         NVL(PARASUPRI.IDTIPOAPUR, '0') IDTIPOAPUR, 
                         SUM((SELECT SUM(I.QTSOLACOMP)
                                FROM ITMAPURPREC I
                               WHERE I.CDCENTITMAP  = ITMAPURPREC.CDCENTITMAP
                                 AND I.NRSEQITMAP   = ITMAPURPREC.NRSEQITMAP
                                 AND I.CDFORNITMAP  = ITMAPURPREC.CDFORNITMAP
                                 AND I.CDPRODITMAP  = ITMAPURPREC.CDPRODITMAP
                                 AND I.CDMARCITMAP  = ITMAPURPREC.CDMARCITMAP
                                 AND NVL(I.IDITMSELAPUR, 'N') = 'S'
                                 AND I.CDREGITMAP   = ITMAPURPREC.CDREGITMAP
                                 AND I.NRCOLEITMAP  = ITMAPURPREC.NRCOLEITMAP
                                 AND I.DTENTRAPUT   = ITMAPURPREC.DTENTRAPUT
                                 AND I.CDFILIAP     = ITMAPURPREC.CDFILIAP)) VRPRECTOTAP
                    FROM ITMAPURPREC, PRODUTO, FORNECEDOR, MINFORNREG, MARCA, PARASUPRI
                   WHERE (ITMAPURPREC.CDCENTITMAP = P_CDCENTCMP)             AND
                         (ITMAPURPREC.NRSEQITMAP  = P_NRSEQAPUR)             AND
                         (ITMAPURPREC.CDMARCITMAP = MARCA.CDMARCA)            AND
                         (ITMAPURPREC.CDFORNITMAP = FORNECEDOR.CDFORNECED)    AND
                         (ITMAPURPREC.CDREGITMAP  = MINFORNREG.CDREGIAO(+))   AND
                         (ITMAPURPREC.CDFORNITMAP = MINFORNREG.CDFORNECED(+)) AND
                         (ITMAPURPREC.CDPRODITMAP = PRODUTO.CDPRODUTO)        AND
                         (PARASUPRI.CDFILIAL      = ITMAPURPREC.CDCENTITMAP)
                   GROUP BY ITMAPURPREC.CDFORNITMAP, FORNECEDOR.NMFANTFORN, ITMAPURPREC.NRSEQITMAP,
                         ITMAPURPREC.CDCENTITMAP, ITMAPURPREC.CDREGITMAP, ITMAPURPREC.NRCOLEITMAP,
                         ITMAPURPREC.CDPRODITMAP, PRODUTO.CDARVPROD, ITMAPURPREC.CDMARCITMAP,
                         MARCA.NMMARCA,
                         ITMAPURPREC.VRPRECUNIAP,
                         --DECODE(NVL(PARASUPRI.IDTIPOAPUR, '0'), '0', ITMAPURPREC.VRPRECUNIAP, '1', ITMAPURPREC.VRPRECECOAP, ITMAPURPREC.VRPRECONPIAP),
                         FORNECEDOR.SGUNIDADE, NVL(MINFORNREG.QTMINFORNREG, FORNECEDOR.QTMINAUTFOT),
                         DECODE(MINFORNREG.QTMINFORNREG, NULL, 'F', 'R'),
                         NVL(MINFORNREG.VRMINFORNREG, FORNECEDOR.VRMINAUTFOR),
                         DECODE(MINFORNREG.VRMINFORNREG, NULL, 'F', 'R'),
                         PRODUTO.VRFATOCONV, ITMAPURPREC.VRPRECUNIAP/PRODUTO.VRFATOCONV,
                         NVL(PARASUPRI.IDTIPOAPUR, '0')
                   ORDER BY PRODUTO.CDARVPROD,
                         --DECODE(NVL(PARASUPRI.IDTIPOAPUR, '0'), '0', ITMAPURPREC.VRPRECUNIAP, '1', ITMAPURPREC.VRPRECECOAP, ITMAPURPREC.VRPRECONPIAP),
                         ITMAPURPREC.CDREGITMAP, ITMAPURPREC.NRCOLEITMAP, FORNECEDOR.NMFANTFORN)
  LOOP
    --Ultima cotacao
    BEGIN
      SELECT NVL(MIN(DECODE(C_ITEM.IDTIPOAPUR, '0', ITEMCOLE.VRPRECUNIITM, '1',
             ITEMCOLE.VRPRECECOITM, ITEMCOLE.VRPRECOITMPI)), 0) AS VRCOTACAO
        INTO V_VRCOTACAO
        FROM ITEMCOLE
       WHERE ITEMCOLE.CDREGIAO    = C_ITEM.CDREGITMAP
         AND ITEMCOLE.CDPRODUTO   = C_ITEM.CDPRODITMAP
         AND ITEMCOLE.CDMARCA     = C_ITEM.CDMARCITMAP
         AND ITEMCOLE.CDFILIAL    = C_ITEM.CDCENTITMAP
         AND ITEMCOLE.NRCOLEPRECO = (SELECT MAX(NRCOLEPRECO)
                                       FROM ITEMCOLE I
                                      WHERE I.CDFILIAL     = ITEMCOLE.CDFILIAL
                                        AND I.NRCOLEPRECO  = ITEMCOLE.NRCOLEPRECO
                                        AND I.CDMARCA      = ITEMCOLE.CDMARCA
                                        AND I.CDREGIAO     = ITEMCOLE.CDREGIAO
                                        AND I.CDFORNECED   = ITEMCOLE.CDFORNECED
                                        AND I.CDPRODUTO    = ITEMCOLE.CDPRODUTO
                                        AND I.NRCOLEPRECO  <> C_ITEM.NRCOLEITMAP
                                        AND I.CDREGIAO     = C_ITEM.CDREGITMAP
                                        AND I.CDPRODUTO    = C_ITEM.CDPRODITMAP
                                        AND I.CDMARCA      = C_ITEM.CDMARCITMAP
                                        AND I.CDFILIAL     = C_ITEM.CDCENTITMAP);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRCOTACAO := 0;
    END;

    --Ultima AF
    BEGIN
      SELECT
         MIN(ITEMAUTF.VRPREUNIAF)
       INTO
          V_VRPREUNIAF
       FROM
         ITEMAUTF, FILIAL
       WHERE
         ITEMAUTF.CDFILDEST   = FILIAL.CDFILIAL   AND
         ITEMAUTF.CDMARCA     = C_ITEM.CDMARCITMAP  AND
         ITEMAUTF.CDPRODUTO   = C_ITEM.CDPRODITMAP  AND
         FILIAL.CDREGIAO      = C_ITEM.CDREGITMAP   AND
         NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)   = C_ITEM.CDCENTITMAP AND
         ITEMAUTF.DTPROGENTAF = (SELECT MAX(I.DTPROGENTAF)
                                   FROM ITEMAUTF I
                                  WHERE I.CDFILDEST     = ITEMAUTF.CDFILDEST         AND
                                        I.CDFILLANC     = ITEMAUTF.CDFILLANC         AND
                                        I.NRAUTOFORN    = ITEMAUTF.NRAUTOFORN        AND
                                        I.CDMARCA       = ITEMAUTF.CDMARCA           AND
                                        I.CDPRODUTO     = ITEMAUTF.CDPRODUTO         AND
                                        I.DTPROGENTAF   = ITEMAUTF.DTPROGENTAF       AND
                                        I.CDMARCA       = C_ITEM.CDMARCITMAP         AND
                                        I.CDPRODUTO     = C_ITEM.CDPRODITMAP         AND
                                        FILIAL.CDREGIAO = C_ITEM.CDREGITMAP          AND
                                        I.DTPROGENTAF   <= SYSDATE+366);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRPREUNIAF := 0;
    END;

      OUT_RECORD.VRULTCOTA      := V_VRCOTACAO;
      OUT_RECORD.VRULTVENDA     := V_VRPREUNIAF;
      OUT_RECORD.CDFORNITMAP    := C_ITEM.CDFORNITMAP;
      OUT_RECORD.NMFANTFORN     := C_ITEM.NMFANTFORN;
      OUT_RECORD.NRSEQITMAP     := C_ITEM.NRSEQITMAP;
      OUT_RECORD.CDCENTITMAP    := C_ITEM.CDCENTITMAP;
      OUT_RECORD.CDREGITMAP     := C_ITEM.CDREGITMAP;
      OUT_RECORD.NRCOLEITMAP    := C_ITEM.NRCOLEITMAP;
      OUT_RECORD.CDPRODITMAP    := C_ITEM.CDPRODITMAP;
      OUT_RECORD.CDARVPROD      := C_ITEM.CDARVPROD;
      OUT_RECORD.CDMARCITMAP    := C_ITEM.CDMARCITMAP;
      OUT_RECORD.NMMARCA        := C_ITEM.NMMARCA;
      OUT_RECORD.IDITMSELAPUR   := C_ITEM.IDITMSELAPUR;
      OUT_RECORD.SGUNIDADE      := C_ITEM.SGUNIDADE;
      OUT_RECORD.IDQTMINFORN    := C_ITEM.IDQTMINFORN;
      OUT_RECORD.IDVRMINFORN    := C_ITEM.IDVRMINFORN;
      OUT_RECORD.VRPRECUNIAP    := C_ITEM.VRPRECUNIAP;
      OUT_RECORD.PRECOECON      := C_ITEM.PRECOECON;
      OUT_RECORD.PRECOECONPI    := C_ITEM.PRECOECONPI;
      OUT_RECORD.QTACOMP        := C_ITEM.QTACOMP;
      OUT_RECORD.ORDENA         := C_ITEM.VRPRECUNIAP;
      OUT_RECORD.QTMINAUTFOT    := C_ITEM.QTMINAUTFOT;
      OUT_RECORD.VRMINAUTFOR    := C_ITEM.VRMINAUTFOR;
      OUT_RECORD.VRFATOCONV     := C_ITEM.VRFATOCONV;
      OUT_RECORD.PRODUNDIVFATCO := C_ITEM.PRODUNDIVFATCO;
      OUT_RECORD.VRPRECTOTAP    := NVL(C_ITEM.VRPRECTOTAP, 0)*NVL(C_ITEM.VRPRECUNIAP, 0);
      PIPE ROW (OUT_RECORD);
  END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('APURA_NEGOC_ULTCOMP','6.39.010'); END;
/
