DROP TYPE EMISSAO_OL_TABLE
/

DROP TYPE EMISSAO_OL_TYPE
/

CREATE TYPE EMISSAO_OL_TYPE AS
   OBJECT(CABECALHO_AF    VARCHAR2(2000),
          CDFILDEST       VARCHAR2(4),
          NRAUTOFORN      VARCHAR2(10),
          CDFILLANC       VARCHAR2(4),
          CDFILIOPELOG    VARCHAR2(4),
          DTPROGENTAF     DATE)
/

CREATE TYPE EMISSAO_OL_TABLE AS TABLE OF EMISSAO_OL_TYPE
/

CREATE OR REPLACE FUNCTION EMISSAO_OL(P_CDFILIAL          IN VARCHAR2,
                                      P_TCDFILIAL         IN VARCHAR2,
                                      P_DTENTRINI         IN DATE,
                                      P_DTENTRFIN         IN DATE,
                                      P_TDTENTR           IN VARCHAR2,
                                      P_CDPRODINI         IN VARCHAR2,
                                      P_CDPRODFIN         IN VARCHAR2,
                                      L_PRODUTO           IN VARCHAR2,
                                      P_CDFILIOL          IN VARCHAR2,
                                      P_TCDFILIOL         IN VARCHAR2,
                                      P_NRSEQRUPTURAOL    IN VARCHAR2,
                                      P_TNRSEQRUPTURAOL   IN VARCHAR2,
                                      P_IDSITUACAO        IN VARCHAR2,
                                      P_SUBGRUPO          IN VARCHAR2,
                                      P_CDGRUPPROD        IN VARCHAR2,
                                      P_ROTA              IN VARCHAR2,
                                      P_PEDEXOLEXTR       IN VARCHAR2 ) RETURN EMISSAO_OL_TABLE PIPELINED
IS
   OUT_RECORD EMISSAO_OL_TYPE := EMISSAO_OL_TYPE (NULL, NULL, NULL,
                                                 NULL, NULL, NULL);
   V_DTENTRINI  DATE;
   V_DTENTRFIN  DATE;
BEGIN
   IF NVL(P_TDTENTR, 'W') = 'T' THEN
      SELECT MAX(DTPROGENTAF), MIN(DTPROGENTAF)
        INTO V_DTENTRFIN, V_DTENTRINI  
        FROM ITEMAUTF
       WHERE NRSEQRUPTURAOL = P_NRSEQRUPTURAOL
         AND EXISTS(SELECT 1
                      FROM AUTOFORNE
                     WHERE AUTOFORNE.CDFILDEST = ITEMAUTF.CDFILDEST
                       AND AUTOFORNE.CDFILLANC = ITEMAUTF.CDFILLANC
                       AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
                       AND ((INSTR(P_CDFILIOL, AUTOFORNE.CDFILIOPELOG) > 0) OR (P_TCDFILIOL = 'T')));
   ELSE
      V_DTENTRINI  := P_DTENTRINI;
      V_DTENTRFIN  := P_DTENTRFIN;
   END IF;
   FOR C_FILIAL IN (SELECT TIPOOPEROPL.CDFILDEST, TIPOOPEROPL.CDFILIOPELOG
                      FROM TIPOOPEROPL
                     WHERE (INSTR(P_CDFILIOL, TIPOOPEROPL.CDFILIOPELOG) <> 0 OR P_TCDFILIOL = 'T')
                       AND (INSTR(P_CDFILIAL, TIPOOPEROPL.CDFILDEST) <> 0 OR P_TCDFILIAL = 'T'))
   LOOP
      FOR C_ITEM IN (SELECT ID||
                            CDFILIAL||
                            FILIAL||
                            ENDERECO||
                            BAIRRO||
                            CEP||
                            MUNICIPIO||
                            ESTADO||
                            AF||
                            ENTREGA||
                            LPAD(QTITENS, 4, '0')||
                            LPAD(TRUNC(TOTALAF, 2)*100, 13, '0')||
                            VRPESOLIQUPR||--TRIM(REPLACE(REPLACE(TO_CHAR(VRPESOLIQUPR,'0000000D000'),'.',''),',',''))||
                            LPAD(' ', 30, ' ')||
                            LPAD(' ', 10, ' ')||
                     --       LPAD(' ', 11, ' ')||
                            LPAD(NRINSJURFILI, 14, ' ')||
                            LPAD(' ',262,' ')|| --OUTROS CAMPOS
                            LPAD(CDINSCESTA, 14, ' ')||
                            TIPOFRETE||
                            NRINSJURTRAN||
                            USR_NMROTATRANS CABECALHO_AF,
                            CDFILDEST,
                            NRAUTOFORN,
                            DTPROGENTAF,
                            CDFILLANC,
                            CDFILIOPELOG
                     FROM( SELECT '2' ID,
                                  LPAD(A.CDFILDEST,10, '0') CDFILIAL,
                                  RPAD((translate(F.NMFILIAL,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')), 50, ' ') FILIAL,
                                  RPAD(NVL((translate(EF.DSENDEFILI,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')), ' '),50, ' ') ENDERECO,
                                  RPAD(NVL(SUBSTR((translate(EF.BAIRRO,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')),1,20), ' '),  20, ' ')BAIRRO,
                                  LPAD(NVL(REPLACE(REPLACE(REPLACE(EF.NRCEPFILI, '.', ''), '-', ''), '/', ''), ' '), 9, ' ') CEP,
                                  RPAD(NVL(SUBSTR((translate(EF.NMMUNICIPIO,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')),1, 25), ' '), 25, ' ') MUNICIPIO,
                                  LPAD(NVL(EF.SGESTADO, ' '), 2, ' ') ESTADO,
                                  LPAD(A.NRAUTOFORN, 10, '0') AF,
                                  TO_CHAR(I.DTPROGENTAF,'DDMMYY') ENTREGA,
                                  COUNT(I.CDPRODUTO) QTITENS,
                                  SUM(
                                       CASE
                                          WHEN ((PRO.VRARREDCMP < 1) AND (PRO.VRFATOCONV = 1))
                                          THEN CEIL(I.QTPROGENTAF)
                                          ELSE CEIL(I.QTPROGENTAF)
                                       END
                                       * I.VRPREUNIAF) TOTALAF,
                                  ' ' TIPOFRETE,
                                  RPAD(' ', 20, ' ') NRINSJURTRAN,
                                  RPAD(' ', 40, ' ') USR_NMROTATRANS,
                                  A.CDFILDEST,
                                  I.DTPROGENTAF,
                                  A.NRAUTOFORN,
                                  A.CDFILLANC,
                                  F.NRINSJURFILI,F.CDINSCESTA,A.CDFILIOPELOG,
                                  LPAD(SUM(( CASE
                                                     WHEN ((PRO.VRARREDCMP < 1) AND (PRO.VRFATOCONV = 1))
                                                     THEN ROUND(I.QTPROGENTAF*NVL(P.VRPESOBRUTPR, 1), 3)
                                                     ELSE ROUND(I.QTPROGENTAF*NVL(P.VRPESOBRUTPR, 1), 3)
                                                  END)
                                                 ) *1000, 10, '0') VRPESOBRUTPR,
                                  LPAD(SUM(( CASE
                                                     WHEN ((PRO.VRARREDCMP < 1) AND (PRO.VRFATOCONV = 1))
                                                     THEN ROUND(I.QTPROGENTAF*NVL(P.VRPESOLIQUPR, 1), 3)
                                                     ELSE ROUND(I.QTPROGENTAF*NVL(P.VRPESOLIQUPR, 1), 3)

                                                  END)
                                                 ) *1000, 10, '0') VRPESOLIQUPR
                             FROM AUTOFORNE A , RELACPRODOL R, FILIAL F, ITEMAUTF I, PRODUTO P, PRODUTO PRO, TIPOOPEROPL,
                                  (SELECT E.CDFILIAL,
                                          E.CDPAIS,
                                          E.SGESTADO,
                                          E.NMBAIRFILI,
                                          E.DSENDEFILI,
                                          E.NRCEPFILI,
                                          M.NMMUNICIPIO,
                                          E.NMBAIRFILI BAIRRO
                                     FROM ENDEFILI E, MUNICIPIO M
                                    WHERE (E.IDTPENDEFILI = 'P'   )
                                      AND (M.CDPAIS = E.CDPAIS    )
                                      AND (M.SGESTADO = E.SGESTADO)
                                      AND (M.CDMUNICIPIO = E.CDMUNICIPIO)) EF
                            WHERE (F.CDFILIAL = A.CDFILDEST)
                              AND A.CDFILDEST = C_FILIAL.CDFILDEST
                              AND (EF.CDFILIAL(+) = F.CDFILIAL)
                              AND A.CDFILDEST  = TIPOOPEROPL.CDFILDEST
                              AND A.CDFILIOPELOG =  TIPOOPEROPL.CDFILIOPELOG
                              AND (A.CDFILDEST  = I.CDFILDEST)
                              AND (A.CDFILLANC  = I.CDFILLANC)
                              AND (R.CDPRODARVOL  = P.CDPRODUTO)
                              AND ((I.DTPROGENTAF BETWEEN V_DTENTRINI AND V_DTENTRFIN))
                              AND (I.CDPRODUTO = PRO.CDPRODUTO)
                              AND ((PRO.CDARVPROD   BETWEEN NVL(P_CDPRODINI, '1')   AND NVL(P_CDPRODFIN, 'ZZZZZZZZZZZZZZZZZZZ')) )
                              AND (PRO.CDPRODUTO = I.CDPRODUTO)
                              AND (I.CDFILDEST  = A.CDFILDEST)
                              AND (I.CDFILLANC  = A.CDFILLANC)
                              AND (I.NRAUTOFORN  = A.NRAUTOFORN)
                              AND (R.CDPRARVORIG = I.CDPRODUTO)
                              AND (R.CDMARCA     = I.CDMARCA)
                              AND (R.CDPRODARVOL IS NOT NULL)
                              AND (I.IDBAIXITAFS IS NULL)
                              AND (I.IDBAIXITMAF IS NULL)
                              AND (NVL(A.IDBAIXAAF,'1') NOT IN ('2','3','4','5','6'))
                              AND (NVL(A.IDAFEXPIRADA,'N') = 'N')
                              AND (A.IDAFOPERLOG = '2')
                              AND NVL(A.CDFILIOPELOG, 'W') = C_FILIAL.CDFILIOPELOG

                             /* AND (AUTOFORNE_RUP.CDFILDEST      = I.CDFILDEST)
                              AND (AUTOFORNE_RUP.CDFILLANC      = I.CDFILLANC)
                              AND (AUTOFORNE_RUP.NRAUTOFORN     = I.NRAUTOFORN)
                              AND (AUTOFORNE_RUP.CDMARCA        = I.CDMARCA)
                              AND (AUTOFORNE_RUP.CDPRODUTO      = I.CDPRODUTO)
                              AND (AUTOFORNE_RUP.DTPROGENTAF    = I.DTPROGENTAF)

                              AND (AUTOFORNE_RUP.NRSEQRUPTURAOL = T.NRSEQRUPTURAOL(+))
                              AND (AUTOFORNE_RUP.CDFILIOPELOG   = T.CDFILIAL(+))*/

                              AND ((I.NRSEQRUPTURAOL = P_NRSEQRUPTURAOL) OR (P_TNRSEQRUPTURAOL = 'T'))
                              AND (I.QTATENDPEDOL IS NULL)
                              AND P_IDSITUACAO  = P_IDSITUACAO
                              AND ((NVL(P_TNRSEQRUPTURAOL, 'W') = 'T') OR ((NVL(I.IDTPGERARUP, 'Z') NOT IN ('T', 'U', 'P'))  AND (NVL(P_TNRSEQRUPTURAOL, 'W') = 'W')))
                              AND NVL(I.QTPROGENTAF, 0) > 0

                              AND ((P_SUBGRUPO  = 'N') OR
                                     (EXISTS(SELECT 1
                                               FROM PRSUBGRPROD
                                              WHERE PRSUBGRPROD.CDGRUPPROD  = P_CDGRUPPROD
                                                AND INSTR(P_SUBGRUPO , PRSUBGRPROD.CDSUBGRPROD) > 0
                                                AND PRSUBGRPROD.CDPRODUTO   = I.CDPRODUTO)))

                             AND ((P_ROTA = 'T') OR
                                  EXISTS (SELECT 1
                                            FROM FILIALROTA
                                           WHERE FILIALROTA.CDFILIROTA = I.CDFILDEST
                                             AND INSTR(P_ROTA, FILIALROTA.CDROTATRANS) > 0)
                                  )
                             AND ((A.IDAUTOFORNEX = P_PEDEXOLEXTR)  OR (P_PEDEXOLEXTR   = 'T'))
                            GROUP BY '2',
                                  LPAD(A.CDFILDEST,10, '0'),
                                  RPAD((translate(F.NMFILIAL,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')), 50, ' '),
                                  RPAD(NVL((translate(EF.DSENDEFILI,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')), ' '),50, ' '),
                                  RPAD(NVL(SUBSTR((translate(EF.BAIRRO,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')),1,20), ' '),  20, ' '),
                                  LPAD(NVL(REPLACE(REPLACE(REPLACE(EF.NRCEPFILI, '.', ''), '-', ''), '/', ''), ' '), 9, ' '),
                                  RPAD(NVL(SUBSTR((translate(EF.NMMUNICIPIO,'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')),1, 25), ' '), 25, ' '),
                                  LPAD(NVL(EF.SGESTADO, ' '), 2, ' '),
                                  LPAD(A.NRAUTOFORN, 10, '0'),
                                  TO_CHAR(I.DTPROGENTAF,'DDMMYY'),
                                  ' ',
                                  RPAD(' ', 20, ' '),
                                  RPAD(' ', 40, ' '),
                                  A.CDFILDEST,
                                  I.DTPROGENTAF,
                                  A.NRAUTOFORN,
                                  A.CDFILLANC,
                                  F.NRINSJURFILI,F.CDINSCESTA,A.CDFILIOPELOG
                            ORDER BY A.CDFILDEST, A.CDFILLANC, A.NRAUTOFORN, I.DTPROGENTAF
                     )) LOOP
         OUT_RECORD.CABECALHO_AF    := C_ITEM.CABECALHO_AF;
         OUT_RECORD.CDFILDEST       := C_ITEM.CDFILDEST;
         OUT_RECORD.NRAUTOFORN      := C_ITEM.NRAUTOFORN;
         OUT_RECORD.CDFILLANC       := C_ITEM.CDFILLANC;
         OUT_RECORD.CDFILIOPELOG    := C_ITEM.CDFILIOPELOG;
         OUT_RECORD.DTPROGENTAF     := C_ITEM.DTPROGENTAF;
         PIPE ROW (OUT_RECORD);
      END LOOP;
   END LOOP;
END;
/