CREATE OR REPLACE FUNCTION SPED_BLOCOH(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_LIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_VERSAO       IN VARCHAR2,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_BLOCOH       IN NOTAFISCAL.IDSTATUS%TYPE,
   P_H020         IN NOTAFISCAL.IDSTATUS%TYPE,
   P_DTINIPER     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINPER     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_MOTINV       IN VARCHAR2,
   P_TOTALIZA     IN NUMBER) RETURN VARCHAR
AS
   V_CONTREGH     NUMBER;
   V_MOVIMENT     NUMBER;
   V_DADOS        VARCHAR2(01);
   V_PRODUTO      VARCHAR2(100);
   V_SEQUPROD     NUMBER;
   V_UNID         VARCHAR2(100);
   V_CONTCTBLPAD  VARCHAR2(20);
   V_COD_PAR      VARCHAR2(100);
   V_CONTCTBL     VARCHAR2(100);
   V_CDFILIAL     VARCHAR2(04);
   V_PARTICIPANTE VARCHAR2(50);
   V_H020         VARCHAR2(100);
   V_SGESTADO      VARCHAR2(02);
BEGIN
   V_DADOS := 'N';
   V_SEQUPROD := NVL(P_TOTALIZA,0);
   
   IF P_BLOCOH = 'S' THEN
      SELECT MAX(CDCONTFORN)
        INTO V_CONTCTBLPAD
        FROM PARAMCTBL;

      SELECT MAX(CDFILIAL)
        INTO V_CDFILIAL
        FROM FILIAL
       WHERE CDEMPRESA  = P_CDEMPRESA
         AND CDINSCESTA = P_CDINSCESTA;

      BEGIN
         SELECT SGESTADO
           INTO V_SGESTADO
           FROM INSCRESTAD
          WHERE CDEMPRESA  = P_CDEMPRESA
            AND CDINSCESTA = P_CDINSCESTA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SGESTADO := '';
      END;

      FOR C_REGH005 IN (SELECT ('|H005'                                                                                 || '|' ||
                               TO_CHAR(DTINVEFISCAL,'ddmmyyyy')                                                         || '|' ||
                               REPLACE(LTRIM(TO_CHAR(SUM(NVL(ROUND(VRTOTINVFISC,2),0)), '999999999990.00')), '.', ',')  || '|'  ) AS ARQ_H005,
                               TO_CHAR(DTINVEFISCAL,'ddmmyyyy') AS DTINVENTFISC, SUM(VRTOTINVFISC) AS VRTOTINVFISC
                          FROM INVENTFISC
                         WHERE CDEMPRESA     = P_CDEMPRESA
                           AND CDINSCESTA    = P_CDINSCESTA
                           AND DTINVEFISCAL BETWEEN P_DTINIPER AND P_DTFINPER
                         GROUP BY DTINVEFISCAL
                        UNION
                        /* GERA O H005 ZERADO MESMO NAO TENDO INVENTARIO QUANDO O CHECK DO BLOCO H E MARCADO NA TELA*/
                        SELECT ('|H005'                                                 || '|' ||
                               TO_CHAR(P_DTFINPER,'ddmmyyyy')                           || '|' ||
                               REPLACE(LTRIM(TO_CHAR(0, '999999999990.00')), '.', ',')  || '|' ) AS ARQ_H005,
                               TO_CHAR(P_DTFINPER,'ddmmyyyy') AS DTINVENTFISC, 0 AS VRTOTINVFISC
                          FROM DUAL
                         WHERE NOT EXISTS (SELECT * FROM INVENTFISC
                                            WHERE CDEMPRESA     = P_CDEMPRESA
                                              AND CDINSCESTA    = P_CDINSCESTA
                                              AND DTINVEFISCAL BETWEEN P_DTINIPER AND P_DTFINPER) ) LOOP
        V_DADOS := 'S';

        V_CONTREGH := NVL(V_CONTREGH,0) + 1;
        IF P_VERSAO NOT IN ('003','004','005') THEN
           INSERT INTO ARQMAGNETICO
             (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
           VALUES
             ('71'||LPAD(RTRIM(V_CONTREGH), 8, '0'), C_REGH005.ARQ_H005||P_MOTINV||'|', P_NRSEQARQMAG);
        ELSE
           INSERT INTO ARQMAGNETICO
             (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
           VALUES
             ('71'||LPAD(RTRIM(V_CONTREGH), 8, '0'), C_REGH005.ARQ_H005, P_NRSEQARQMAG);
        END IF;

        IF C_REGH005.VRTOTINVFISC > 0 THEN
           FOR C_REGH010 IN (SELECT '|H010'                                               || '|' ||
                                    NVL(CDPRODARQ,'')                                     || '|' ||
                                    NVL(UNIDARQ,'')                                       || '|' ||
                                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(QTD,3),0), '999999999990.000')), '.', ',')            || '|' ||
                                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTAL/QTD,6),0), '999999999990.000000')), '.', ',') || '|' ||
                                    REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTAL,2),0), '999999999990.00')), '.', ',')         || '|' ||
                                    CDFORN1                                               || '|' AS PARTE_1,
                                    --REPLACE(REPLACE(REPLACE(REPLACE(NVL(CDFORN2,''),'.'),'-'),'/'),' ') || '|' ||
                                    ''                                                    || '|' ||
                                    CDCONTCTBL                                            || '|' ||
                                    ''                                                    || '|' AS ARQ_H010,
                                    CDARVPROD, CDPRODUTO, UNID,
                                    CDCONTCTBL AS CDCONTCTBL, CDFORN2, CDCLASFISC, CDSITUTRIBIT, ROUND(VRTOTAL/QTD,2) AS VRUNIT
                               FROM (SELECT NVL(P.CDARVPROD,'') AS CDPRODARQ,
                                            NVL(P.SGUNIDADE,'') AS UNIDARQ,
                                            SUM(NVL(ROUND(I.QTESTINVFISC,3),0)) AS QTD,
                                            SUM(NVL(ROUND(I.VRTOTINVFISC,2),0)) AS VRTOTAL,
                                            DECODE(F.CDFORNAGETERC, '', I.IDPROPRIEDADE, '1') AS CDFORN1,
                                            CASE WHEN F.CDFORNAGETERC IS NOT NULL THEN 'F'||F.CDFORNAGETERC
                                                 WHEN I.CDCLIENTE     IS NOT NULL THEN 'C'||I.CDCLIENTE
                                                 WHEN I.CDFORNECED    IS NOT NULL THEN 'F'||I.CDFORNECED
                                                 ELSE '' END AS CDFORN2,
                                            NVL(P.CDARVPROD,'') AS CDARVPROD,
                                            NVL(P.CDPRODUTO,'') AS CDPRODUTO,
                                            NVL(P.SGUNIDADE,'') AS UNID,
                                            MAX(NVL(CONTCTBL_PRODUTO(P.CDPRODUTO,'E',F.CDFILIAL),V_CONTCTBLPAD)) AS CDCONTCTBL,
                                            I.CDCLASFISC AS CDCLASFISC, MAX(I.CDSITUTRIBIT) AS CDSITUTRIBIT
                                       FROM INVENTFISC I, PRODUTO P, FILIAL F
                                      WHERE I.CDEMPRESA    = P_CDEMPRESA
                                        AND I.CDINSCESTA   = P_CDINSCESTA
                                        AND I.CDEMPRESA    = F.CDEMPRESA
                                        AND I.CDFILIAL     = F.CDFILIAL
                                        AND I.CDINSCESTA   = F.CDINSCESTA
                                        AND I.DTINVEFISCAL = C_REGH005.DTINVENTFISC
                                        AND I.CDPRODUTO    = P.CDPRODUTO
                                        AND I.QTESTINVFISC <> 0
                                      GROUP BY NVL(P.CDARVPROD,''), NVL(P.SGUNIDADE,''), DECODE(F.CDFORNAGETERC, '', I.IDPROPRIEDADE, '1'),
                                            NVL(P.CDPRODUTO,''),
                                            CASE WHEN F.CDFORNAGETERC IS NOT NULL THEN 'F'||F.CDFORNAGETERC
                                                 WHEN I.CDCLIENTE     IS NOT NULL THEN 'C'||I.CDCLIENTE
                                                 WHEN I.CDFORNECED    IS NOT NULL THEN 'F'||I.CDFORNECED
                                                 ELSE '' END, I.CDCLASFISC
                                      UNION ALL
                                     SELECT NVL(P.CDARVPROD,'') AS CDPRODARQ,
                                             NVL(P.SGUNIDADE,'') AS UNIDARQ,
                                             SUM(NVL(ROUND(I.QTESTINVFISC,3),0)) AS QTD,
                                             SUM(NVL(ROUND(I.VRTOTINVFISC,2),0)) AS VRTOTAL,
                                             I.IDPROPRIEDADE AS CDFORN1,
                                             CASE WHEN I.CDCLIENTE     IS NOT NULL THEN 'C'||I.CDCLIENTE
                                                  WHEN I.CDFORNECED    IS NOT NULL THEN 'F'||I.CDFORNECED
                                                  ELSE '' END AS CDFORN2,
                                             NVL(P.CDARVPROD,'') AS CDARVPROD,
                                             NVL(P.CDPRODUTO,'') AS CDPRODUTO,
                                             NVL(P.SGUNIDADE,'') AS UNID,
                                             MAX(NVL(CONTCTBL_PRODUTO(P.CDPRODUTO,'E',V_CDFILIAL),V_CONTCTBLPAD)) AS CDCONTCTBL,
                                             I.CDCLASFISC AS CDCLASFISC, MAX(I.CDSITUTRIBIT) AS CDSITUTRIBIT
                                        FROM INVENTFISC I, PRODUTO P
                                       WHERE I.CDEMPRESA    = P_CDEMPRESA
                                         AND I.CDINSCESTA   = P_CDINSCESTA
                                         AND I.DTINVEFISCAL = C_REGH005.DTINVENTFISC
                                         AND I.CDPRODUTO    = P.CDPRODUTO
                                         AND I.CDFILIAL     IS NULL
                                         AND I.QTESTINVFISC <> 0
                                       GROUP BY NVL(P.CDARVPROD,''), NVL(P.SGUNIDADE,''), I.IDPROPRIEDADE,
                                             NVL(P.CDPRODUTO,''),
                                             CASE WHEN I.CDCLIENTE     IS NOT NULL THEN 'C'||I.CDCLIENTE
                                                  WHEN I.CDFORNECED    IS NOT NULL THEN 'F'||I.CDFORNECED
                                                  ELSE '' END, I.CDCLASFISC))LOOP

              V_PARTICIPANTE := '';
              /* CODIGO DO PARTICIPANTE */
              SELECT MAX(CDCHAVEREG)
                INTO V_COD_PAR
                FROM TMP_REGDADOSSPED
               WHERE CDCHAVEREG = C_REGH010.CDFORN2
                 AND CDREGISTRO = '0150';

              IF (V_COD_PAR IS NULL) AND (C_REGH010.CDFORN2 IS NOT NULL) THEN
                 V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGH010.CDFORN2);
                 IF V_PARTICIPANTE IS NULL THEN
                    V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                    INSERT INTO TMP_REGDADOSSPED
                      (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                    VALUES
                      ('0150', V_SEQUPROD, C_REGH010.CDFORN2);
                 END IF;
              END IF;
              V_COD_PAR := NULL;

              V_PARTICIPANTE := REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGH010.CDFORN2),'.'),'-'),'/'),' ')||'|';

              V_DADOS := 'S';
              V_CONTREGH := NVL(V_CONTREGH,0) + 1;
              INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
              VALUES
                 ('72'||LPAD(RTRIM(V_CONTREGH), 8, '0'), C_REGH010.PARTE_1||V_PARTICIPANTE||C_REGH010.ARQ_H010, P_NRSEQARQMAG);

              /* CODIGO DO PRODUTO */
              SELECT MAX(CDCHAVEREG)
                INTO V_PRODUTO
                FROM TMP_REGDADOSSPED
               WHERE CDCHAVEREG = C_REGH010.CDPRODUTO
                 AND CDREGISTRO = '0200';

              IF (V_PRODUTO IS NULL) AND (C_REGH010.CDPRODUTO IS NOT NULL) THEN
                 V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                 INSERT INTO TMP_REGDADOSSPED
                   (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO3)
                 VALUES
                   ('0200', V_SEQUPROD, C_REGH010.CDPRODUTO, C_REGH010.CDCLASFISC);
              END IF;
              V_PRODUTO := NULL;

              /* UNIDADE DE MEDIDA */
              SELECT MAX(CDCHAVEREG)
                INTO V_UNID
                FROM TMP_REGDADOSSPED
               WHERE CDCHAVEREG = C_REGH010.UNID
                 AND CDREGISTRO = '0190';

              IF (V_UNID IS NULL) AND (C_REGH010.UNID IS NOT NULL) THEN
                 V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                 INSERT INTO TMP_REGDADOSSPED
                   (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                 VALUES
                   ('0190', V_SEQUPROD, C_REGH010.UNID);
              END IF;
              V_UNID := null;

              /* PLANO DE CONTAS REFERENCIAL */
              SELECT MAX(CDCHAVEREG)
                INTO V_CONTCTBL
                FROM TMP_REGDADOSSPED
               WHERE CDCHAVEREG = C_REGH010.CDCONTCTBL
                 AND CDREGISTRO = '0500';

              IF (V_CONTCTBL IS NULL) AND (C_REGH010.CDCONTCTBL IS NOT NULL) THEN
                 V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                 INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                 VALUES
                    ('0500', V_SEQUPROD, C_REGH010.CDCONTCTBL);
              END IF;
              V_CONTCTBL := NULL;

              /*REGISTRO H020, obrigatorio quando o motivo do inventario for de ?02? a ?05?*/
              /*Para atender algumas excecoes a geracao do registro sera conforme parametro P_H020.*/
              IF P_H020 = 'S' THEN
                 V_H020 :=  '|H020' ||'|'||
                            NVL(C_REGH010.CDSITUTRIBIT,'090') ||'|'||
                            CASE WHEN NVL(C_REGH010.CDSITUTRIBIT,'090') IN ('000','010','020','070')
                                 THEN REPLACE(LTRIM(TO_CHAR(NVL(ROUND(C_REGH010.VRUNIT,2),0), '999999999990.00')), '.', ',')  ||'|'||
                                      REPLACE(LTRIM(TO_CHAR(NVL(ROUND(C_REGH010.VRUNIT*CONSULTA_QUADRO_ALIQUOTAS(V_SGESTADO,V_SGESTADO)/100,2),0), '999999999990.00')), '.', ',')
                                 ELSE '0'||'|'||'0' END ||'|';
                 V_CONTREGH := NVL(V_CONTREGH,0) + 1;
                 INSERT INTO ARQMAGNETICO                                 
                    (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                 VALUES
                    ('72'||LPAD(RTRIM(V_CONTREGH), 8, '0'), V_H020, P_NRSEQARQMAG);
              END IF;

          END LOOP;
        END IF;
      END LOOP;
   END IF;

   /* abertura do bloco H */
   IF ((V_CONTREGH <> 0) AND (V_DADOS <> 'N')) THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;
   V_CONTREGH := NVL(V_CONTREGH,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('70'||LPAD(RTRIM(V_CONTREGH), 8, '0'), '|H001' || '|' ||V_MOVIMENT  || '|' , P_NRSEQARQMAG);

   /* encerramento do bloco H */
   V_CONTREGH := NVL(V_CONTREGH,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG  )
   VALUES
     ('74'||LPAD(RTRIM(V_CONTREGH), 8, '0'), '|H990'|| '|' ||V_CONTREGH   || '|', P_NRSEQARQMAG);

   RETURN(LPAD(TO_CHAR(NVL(V_CONTREGH,0)),10, '0')||LPAD(TO_CHAR(NVL(V_SEQUPROD,0)),10, '0'));
END;
/