DROP TYPE QUERY_ITEM_CMP99000_TABLE
/

DROP TYPE QUERY_ITEM_CMP99000_TYPE
/

CREATE TYPE QUERY_ITEM_CMP99000_TYPE AS
OBJECT(ID                VARCHAR2(100),
       IDCHECK           VARCHAR2(1),
       NRAPURPREC        VARCHAR2(10),
       CDFORNECED        VARCHAR2(21),
       CDFILSOLI         VARCHAR2(4),
       NMFILIAL          VARCHAR2(100),
       DSFILIALC         VARCHAR2(150),
       DSFILIAL          VARCHAR2(110),
       CDFILLAN          VARCHAR2(4),
       NRSOLICMP         VARCHAR2(10),
       CDPRODUTO         VARCHAR2(10),
       NMPRODUTO         VARCHAR2(50),
       CDARVPROD         VARCHAR2(20),
       SGUNIDADE         VARCHAR2(10),
       DSPRODUTO         VARCHAR2(80),
       CDMARCA           VARCHAR2(5),
       NMMARCA           VARCHAR2(20),
       DSMARCA           VARCHAR2(30),
       NRINSJURFORN      VARCHAR2(100),
       NMFANTFORN        VARCHAR2(50),
       CDEXPRODFORN      VARCHAR2(50),
       DSTIPO            VARCHAR2(15),
       NMPRODUTOOL       VARCHAR2(50),
       CDARVPRODOL       VARCHAR2(20),
       CDFILCENTCMP      VARCHAR2(10),
       SGESTADO          VARCHAR2(2),
       NMMUNICIPIO       VARCHAR2(30),
       IDITMSELPREVCMP   VARCHAR2(1),
       NMCOMPRZSFIL      VARCHAR2(200),
       DTPROGENTR        DATE,
       NRORG             NUMERIC,
       QTSOLIPROD        FLOAT,
       VRCUSTO           FLOAT,
       VRPRECUNIAP       FLOAT,
       QTDIAESTOQUE      FLOAT,
       QTCONSUMODIA      FLOAT,
       QTESTOQUE         FLOAT,
       QTDURACAEST       FLOAT,
       QTCONSUMODIASEST  FLOAT,
       QTITEMPED         FLOAT,
       QTPROGENTAF       FLOAT,
       QTENTRADAS        FLOAT,
       QTSAIDAS          FLOAT,
       VRCUSTOTOTAL      FLOAT,
       QTTOTAL           FLOAT,
       CONTADOR          FLOAT);
/

CREATE TYPE QUERY_ITEM_CMP99000_TABLE AS TABLE OF QUERY_ITEM_CMP99000_TYPE
/

CREATE OR REPLACE FUNCTION QUERY_ITEM_CMP99000(P_CDFILCENTCMP     IN VARCHAR2,
                                               P_NRAPURPREC       IN VARCHAR2,
                                               P_CDFORNECED       IN VARCHAR2,
                                               P_CDMARCA          IN VARCHAR2,
                                               P_CDFILIAL         IN VARCHAR2,
                                               P_CDPRODINI        IN VARCHAR2,
                                               P_CDPRODFIN        IN VARCHAR2,
                                               P_TIPOTELA         IN VARCHAR2,
                                               P_CDTIPOCUSTO      IN VARCHAR2
                                               )RETURN QUERY_ITEM_CMP99000_TABLE PIPELINED
IS
   OUT_RECORD QUERY_ITEM_CMP99000_TYPE := QUERY_ITEM_CMP99000_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, NULL, NULL, NULL, NULL,
                                                                    0, 0,0,0, 0, 0, 0, 0, 0, 0,
                                                                    0, 0, 0, 0, 0, 0);
   V_CONTADOR     NUMERIC := 0;
   V_NMPRODUTOOL  PRODUTO.NMPRODUTO%TYPE;
   V_CDARVPRODOL  PRODUTO.CDARVPROD%TYPE;
   V_CDPRODARVOL  RELACPRODOL.CDPRODARVOL%TYPE;
   V_VRPRECUNIAP  ITMAPURPREC.VRPRECUNIAP%TYPE;
   V_CHAVE_CDARV  VARCHAR2(30) := '_';
BEGIN
   FOR C_ITEM IN(SELECT CDCENTITMAP||NRSEQITMAP||CDREGITMAP||CDFORNITMAP||NRCOLEITMAP||
                        TO_CHAR(DTENTRAPUT, 'DD/MM/YYYY')||CDFILIAP||CDPRODITMAP||CDMARCITMAP ID,
                        ITMAPURPREC.CDFORNITMAP AS CDFORNECED, ITMAPURPREC.NRSEQITMAP AS NRAPURPREC,
                        'U' AS IDCHECK, FILIAL.CDFILIAL CDFILSOLI, FILIAL.NMFILIAL,
                        FILIAL.CDFILIAL || ' - ' || RPAD(SUBSTR(FILIAL.NMFILIAL, 1, 5), 5, ' ') DSFILIALC,
                        CASE P_TIPOTELA
                           WHEN 'G'
                           THEN FILIAL.CDFILIAL || ' - ' || FILIAL.NMFILIAL
                           ELSE CASE
                                   WHEN LENGTH(FILIAL.NMCOMPRZSFIL||FILIAL.SGFILIAL) <> 0
                                   THEN LPAD(NVL(NMCOMPRZSFIL, '0'), 2, '0') || ' - ' || RPAD(SUBSTR(NVL(FILIAL.SGFILIAL, '    '), 1, 4), 4, ' ')|| ' - ' || FILIAL.NMFILIAL
                                   ELSE FILIAL.CDFILIAL || ' - ' || FILIAL.NMFILIAL
                                END
                        END DSFILIAL, APURPRECO.CDFILLANC CDFILLAN,
                        NRSEQITMAP NRSOLICMP, PRODUTO.CDPRODUTO, DTENTRAPUT DTPROGENTR, QTACOMP QTSOLIPROD,
                        PRODUTO.NMPRODUTO, PRODUTO.CDARVPROD, PRODUTO.SGUNIDADE,
                        PRODUTO.CDARVPROD || ' - ' || PRODUTO.NMPRODUTO DSPRODUTO, MARCA.CDMARCA, MARCA.NMMARCA,
                        MARCA.CDMARCA || ' - ' || MARCA.NMMARCA DSMARCA, ITMAPURPREC.VRPRECUNIAP VRCUSTO,
                        (CUSTO_PRODUTO(CASE P_TIPOTELA
                                          WHEN 'G'
                                          THEN FILIAL.CDFILIAL
                                          ELSE P_CDFILCENTCMP
                                       END, PRODUTO.CDPRODESTO, P_CDTIPOCUSTO, SYSDATE, SYSDATE))*PRODUTO.VRFATOCONV VRPRECUNIAP,
                        ITMAPURPREC.NRORG, APURPRECO.QTDIAESTOQUE,
                        ITMAPURPREC.QTCONSUMODIA, ITMAPURPREC.QTESTOQUE, ITMAPURPREC.QTDURACAEST, ITMAPURPREC.QTCONSUMODIASEST,
                        ITMAPURPREC.QTITEMPED, ITMAPURPREC.QTPROGENTAF, ITMAPURPREC.QTENTRADAS, ITMAPURPREC.QTSAIDAS,
                        ITMAPURPREC.QTACOMP * VRPRECUNIAP VRCUSTOTOTAL, FORNECEDOR.NRINSJURFORN, FORNECEDOR.NMFANTFORN,
                        ITMAPURPREC.CDEXPRODFORN,
                        SUM(ITMAPURPREC.QTACOMP) OVER (PARTITION BY CDPRODITMAP, CDFORNITMAP) QTTOTAL,
                        FILIAL.IDFUNCAOFILI, ITMAPURPREC.CDFORNITMAP, ENDEFILI.SGESTADO, MUNICIPIO.NMMUNICIPIO,
                        NVL(ITMAPURPREC.IDITMSELPREVCMP, 'N') IDITMSELPREVCMP, FILIAL.NMCOMPRZSFIL,
                        PRODUTO.VRFATOCONV, PRODUTO.CDPRODESTO
                   FROM ITMAPURPREC, PRODUTO, FILIAL, MARCA, APURPRECO, FORNECEDOR, ENDEFILI, MUNICIPIO
                  WHERE ITMAPURPREC.CDCENTITMAP = APURPRECO.CDFILCENTCMP
                    AND ITMAPURPREC.NRSEQITMAP = APURPRECO.NRAPURPREC
                    AND ITMAPURPREC.CDCENTITMAP = P_CDFILCENTCMP
                    /*AND NVL(ITMAPURPREC.IDITMSELPREVCMP, 'N') = 'N'*/
                    AND ( ( INSTR(P_CDFORNECED, ITMAPURPREC.CDFORNITMAP) > 0 ) OR ( P_CDFORNECED = 'T' ) )
                    AND ITMAPURPREC.NRSEQITMAP = P_NRAPURPREC
                    AND ITMAPURPREC.CDFILIAP = FILIAL.CDFILIAL
                    AND ITMAPURPREC.CDPRODITMAP = PRODUTO.CDPRODUTO
                    AND ITMAPURPREC.CDMARCITMAP = MARCA.CDMARCA
                    AND ( ( INSTR(P_CDMARCA, ITMAPURPREC.CDMARCITMAP) > 0 ) OR ( P_CDMARCA = 'T' ) )
                    AND ( ( INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0 ) OR ( P_CDFILIAL = 'T' ) )
                    AND ITMAPURPREC.CDFORNITMAP = FORNECEDOR.CDFORNECED
                    AND ( PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND RPAD(P_CDPRODFIN, 20, 'Z') )
                    AND ENDEFILI.CDFILIAL = FILIAL.CDFILIAL
                    AND NVL(ENDEFILI.IDTPENDEFILI, 'T') = 'P'
                    AND ENDEFILI.CDPAIS = MUNICIPIO.CDPAIS
                    AND ENDEFILI.SGESTADO = MUNICIPIO.SGESTADO
                    AND ENDEFILI.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                  ORDER BY PRODUTO.CDARVPROD, CASE
                                                 WHEN P_TIPOTELA = 'G'
                                                 THEN FILIAL.CDFILIAL || ' - ' || FILIAL.NMFILIAL
                                                 ELSE CASE
                                                         WHEN LENGTH(FILIAL.NMCOMPRZSFIL||FILIAL.SGFILIAL) <> 0
                                                         THEN LPAD(NVL(NMCOMPRZSFIL, '0'), 2, '0') || ' - ' || RPAD(SUBSTR(NVL(FILIAL.SGFILIAL, '    '), 1, 4), 4, ' ')|| ' - ' || FILIAL.NMFILIAL
                                                         ELSE FILIAL.CDFILIAL || ' - ' || FILIAL.NMFILIAL
                                                      END
                                              END)
   LOOP
      V_NMPRODUTOOL  := NULL;
      V_CDARVPRODOL  := NULL;
      IF C_ITEM.IDFUNCAOFILI = 'O' THEN
         SELECT MAX(PRODUTOOL.NMPRODUTO), MAX(PRODUTOOL.CDARVPROD), MAX(RELACPRODOL.CDPRODARVOL)
           INTO V_NMPRODUTOOL, V_CDARVPRODOL, V_CDPRODARVOL
           FROM RELACPRODOL, PRODUTO PRODUTOOL
          WHERE RELACPRODOL.CDPRODARVOL = PRODUTOOL.CDPRODUTO
            AND RELACPRODOL.CDPRARVORIG = C_ITEM.CDPRODUTO
            AND RELACPRODOL.CDMARCA     = C_ITEM.CDMARCA;
      END IF;
      /*IF C_ITEM.IDFUNCAOFILI = 'O' THEN
         SELECT MAX(PRODUTOOL.NMPRODUTO), MAX(PRODUTOOL.CDARVPROD), MAX(RELACPRODOL.CDPRODARVOL)
           INTO V_NMPRODUTOOL, V_CDARVPRODOL, V_CDPRODARVOL
           FROM RELACPRODOL, PRODUTO PRODUTOOL
          WHERE RELACPRODOL.CDPRODARVOL = PRODUTOOL.CDPRODUTO
            AND RELACPRODOL.CDPRARVORIG = C_ITEM.CDPRODUTO
            AND RELACPRODOL.CDMARCA     = C_ITEM.CDMARCA;
         SELECT COUNT(1)
           INTO V_CONTADOR
           FROM AUTOFORNE, ITEMAUTF I, RELACPRODOL
          WHERE I.CDFILDEST             = AUTOFORNE.CDFILDEST
            AND I.CDFILLANC             = AUTOFORNE.CDFILLANC
            AND I.NRAUTOFORN            = AUTOFORNE.NRAUTOFORN
            AND RELACPRODOL.CDPRODARVOL = I.CDPRODUTO
            AND RELACPRODOL.CDPRARVORIG = C_ITEM.CDPRODUTO
            AND RELACPRODOL.CDMARCA     = C_ITEM.CDMARCA
            AND I.CDFILDEST             = C_ITEM.CDFILSOLI
            AND I.DTENTRITMSOL          = C_ITEM.DTPROGENTR
            AND I.CDMARCA               = C_ITEM.CDMARCA
            AND AUTOFORNE.NRSEQAPURAF   = C_ITEM.NRAPURPREC
            AND AUTOFORNE.CDFORNECED    = C_ITEM.CDFORNITMAP;
      ELSE
         SELECT COUNT(1)
           INTO V_CONTADOR
           FROM AUTOFORNE, ITEMAUTF I
          WHERE I.CDFILDEST    = AUTOFORNE.CDFILDEST
            AND I.CDFILLANC    = AUTOFORNE.CDFILLANC
            AND I.NRAUTOFORN   = AUTOFORNE.NRAUTOFORN
            AND I.CDFILDEST    = C_ITEM.CDFILSOLI
            AND I.DTENTRITMSOL = C_ITEM.DTPROGENTR
            AND I.CDPRODUTO    = C_ITEM.CDPRODUTO
            AND I.CDMARCA      = C_ITEM.CDMARCA
            AND AUTOFORNE.NRSEQAPURAF = C_ITEM.NRAPURPREC
            AND AUTOFORNE.CDFORNECED = C_ITEM.CDFORNITMAP;
      END IF;*/
      IF P_TIPOTELA = 'C' THEN
         IF V_CHAVE_CDARV <> C_ITEM.CDARVPROD THEN
            SELECT MIN((CUSTO_PRODUTO(FILIAL.CDFILIAL, C_ITEM.CDPRODESTO, P_CDTIPOCUSTO, SYSDATE, SYSDATE))*C_ITEM.VRFATOCONV) W
              INTO V_VRPRECUNIAP
              FROM FILIAL
             WHERE ( ( INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0 ) OR ( P_CDFILIAL = 'T' ) )
               AND CUSTO_PRODUTO(FILIAL.CDFILIAL, C_ITEM.CDPRODESTO, P_CDTIPOCUSTO, SYSDATE, SYSDATE) > 0;
            V_CHAVE_CDARV := C_ITEM.CDARVPROD;
         END IF;
      ELSE
         V_VRPRECUNIAP := C_ITEM.VRPRECUNIAP;
      END IF;
      V_CONTADOR := V_CONTADOR + 1;
      IF ((P_TIPOTELA = 'G') OR ((P_TIPOTELA = 'C') /*AND (V_CONTADOR <= 375)*/))THEN
         OUT_RECORD.ID               := C_ITEM.ID;
         OUT_RECORD.CDFORNECED       := C_ITEM.CDFORNECED;
         OUT_RECORD.NRAPURPREC       := C_ITEM.NRAPURPREC;
         OUT_RECORD.NRINSJURFORN     := C_ITEM.NRINSJURFORN;
         OUT_RECORD.NMFANTFORN       := C_ITEM.NMFANTFORN;
         OUT_RECORD.CDEXPRODFORN     := C_ITEM.CDEXPRODFORN;
         OUT_RECORD.IDCHECK          := C_ITEM.IDCHECK;
         OUT_RECORD.CDFILSOLI        := C_ITEM.CDFILSOLI;
         OUT_RECORD.NMFILIAL         := C_ITEM.NMFILIAL;
         OUT_RECORD.DSFILIALC        := C_ITEM.DSFILIALC;
         OUT_RECORD.DSTIPO           := 'CMP';
         OUT_RECORD.DSFILIAL         := C_ITEM.DSFILIAL;
         OUT_RECORD.CDFILLAN         := C_ITEM.CDFILLAN;
         OUT_RECORD.NRSOLICMP        := C_ITEM.NRSOLICMP;
         OUT_RECORD.CDPRODUTO        := C_ITEM.CDPRODUTO;
         OUT_RECORD.DTPROGENTR       := C_ITEM.DTPROGENTR;
         OUT_RECORD.NMPRODUTO        := C_ITEM.NMPRODUTO;
         OUT_RECORD.CDARVPROD        := C_ITEM.CDARVPROD;
         OUT_RECORD.SGUNIDADE        := C_ITEM.SGUNIDADE;
         OUT_RECORD.DSPRODUTO        := C_ITEM.DSPRODUTO;
         OUT_RECORD.CDMARCA          := C_ITEM.CDMARCA;
         OUT_RECORD.NMMARCA          := C_ITEM.NMMARCA;
         OUT_RECORD.DSMARCA          := C_ITEM.DSMARCA;
         OUT_RECORD.VRCUSTO          := C_ITEM.VRCUSTO;
         OUT_RECORD.QTSOLIPROD       := C_ITEM.QTSOLIPROD;
         OUT_RECORD.VRPRECUNIAP      := V_VRPRECUNIAP;
         OUT_RECORD.NRORG            := C_ITEM.NRORG;
         OUT_RECORD.QTDIAESTOQUE     := C_ITEM.QTDIAESTOQUE;
         OUT_RECORD.QTCONSUMODIA     := C_ITEM.QTCONSUMODIA;
         OUT_RECORD.QTESTOQUE        := C_ITEM.QTESTOQUE;
         OUT_RECORD.QTDURACAEST      := C_ITEM.QTDURACAEST;
         OUT_RECORD.QTCONSUMODIASEST := C_ITEM.QTCONSUMODIASEST;
         OUT_RECORD.QTITEMPED        := C_ITEM.QTITEMPED;
         OUT_RECORD.QTPROGENTAF      := C_ITEM.QTPROGENTAF;
         OUT_RECORD.QTENTRADAS       := C_ITEM.QTENTRADAS;
         OUT_RECORD.QTSAIDAS         := C_ITEM.QTSAIDAS;
         OUT_RECORD.VRCUSTOTOTAL     := C_ITEM.VRCUSTOTOTAL;
         OUT_RECORD.QTTOTAL          := C_ITEM.QTTOTAL;
         OUT_RECORD.NMPRODUTOOL      := V_NMPRODUTOOL;
         OUT_RECORD.CDARVPRODOL      := V_CDARVPRODOL;
         OUT_RECORD.CDFILCENTCMP     := P_CDFILCENTCMP;
         OUT_RECORD.SGESTADO         := C_ITEM.SGESTADO;
         OUT_RECORD.NMMUNICIPIO      := C_ITEM.NMMUNICIPIO;
         OUT_RECORD.CONTADOR         := V_CONTADOR;
         OUT_RECORD.IDITMSELPREVCMP  := C_ITEM.IDITMSELPREVCMP;
         OUT_RECORD.NMCOMPRZSFIL     := C_ITEM.NMCOMPRZSFIL;
         IF P_TIPOTELA  = 'C' THEN
            OUT_RECORD.QTESTOQUE     := TRUNC(C_ITEM.QTESTOQUE);
            OUT_RECORD.QTSOLIPROD    := TRUNC(C_ITEM.QTSOLIPROD);
            /*OUT_RECORD.NMPRODUTO     := RPAD(SUBSTR(C_ITEM.NMPRODUTO, 1, 20), 20, '. .');*/
            OUT_RECORD.NRINSJURFORN  := C_ITEM.NRINSJURFORN||' - '||C_ITEM.NMFANTFORN;
            IF NVL(C_ITEM.VRCUSTO, 0) = 0 THEN
               /*C_ITEM.VRCUSTO              := NULL;*/
               OUT_RECORD.NRINSJURFORN     := NULL;
               OUT_RECORD.NMFANTFORN       := NULL;
               OUT_RECORD.CDEXPRODFORN     := NULL;
               OUT_RECORD.CDFORNECED       := NULL;
            END IF;
         END IF;
         PIPE ROW (OUT_RECORD);
         IF P_TIPOTELA  = 'C' THEN
            OUT_RECORD.DSFILIALC     := C_ITEM.DSFILIALC;
            OUT_RECORD.DSTIPO        := 'EST';
            OUT_RECORD.QTESTOQUE     := TRUNC(C_ITEM.QTESTOQUE);
            OUT_RECORD.QTSOLIPROD    := TRUNC(C_ITEM.QTESTOQUE);
            PIPE ROW (OUT_RECORD);
         END IF;
      END IF;
   END LOOP;
END;
/