CREATE OR REPLACE PROCEDURE GERA_GUIA_ICMS_ST(
   P_NRSEQUMAPAPU IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_IDTIPOAPURA  IN MAPAAPURAC.IDBLOQMAPAPU%TYPE DEFAULT 'D')
AS
   V_VRIMPORECO    NUMBER;
   V_VRSALDCRED    NUMBER;
   V_NRSEQMAPAEST  NUMBER;
   V_SGESTADO      VARCHAR2(02);
   V_VRTOTADEBI    NUMBER;
   V_VRTOTACRED    NUMBER;
BEGIN

   /******** Deleta os registros na tabela, devido a regeracao da guia ********/
   DELETE FROM MAPAAPURESTADO
    WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU;

   V_VRIMPORECO := 0;
   V_VRSALDCRED := 0;
   V_SGESTADO   := '';
   V_VRTOTADEBI := 0;
   V_VRTOTACRED := 0;

   /******** Busca os valores para geracao da Guia por Estado - Para o Mobile (P_IDTIPOAPURA = M) as informacoes sao
             quebradas por inscricao do substituto tributario tambem, no Delphi (P_IDTIPOAPURA = D) apenas por estado ********/
   FOR C_DADOS IN (SELECT REG.CDPAIS, REG.SGESTADO, DECODE(P_IDTIPOAPURA,'D','','M',REG.CDINSCESTAST) AS CDINSCESTAST,
                          SUM(DECODE(REG.IDENTRSAIDOP,'S',ROUND(VRCONTABILNF,2),0)) AS VRCTBLDEB,
                          SUM(DECODE(REG.IDENTRSAIDOP,'S',ROUND(VRBASECALC,2),0)) AS VRBASECALCDEB,
                          SUM(DECODE(REG.IDENTRSAIDOP,'E',ROUND(VRCONTABILNF,2),0)) AS VRCTBLCRED,
                          SUM(DECODE(REG.IDENTRSAIDOP,'E',ROUND(VRBASECALC,2),0)) AS VRBASECALCCRED,
                          SUM(DECODE(REG.IDENTRSAIDOP,'S',ROUND(VRIMPOSTONF,2),0)) AS VRDEBITO,
                          SUM(DECODE(E.IDREGIMEICMSST,'1',DECODE(REG.IDENTRSAIDOP,'E',ROUND(VRCREDITO,  2),0),0)) AS VRCREDITO,
                          SUM(DECODE(E.IDREGIMEICMSST,'1',DECODE(REG.IDENTRSAIDOP,'E',ROUND(VRDEVMAPA,  2),0),0)) AS VRDEVMAPA,
                          SUM(DECODE(E.IDREGIMEICMSST,'1',DECODE(REG.IDENTRSAIDOP,'E',ROUND(VRRESSAMAPA,2),0),0)) AS VRRESSAMAPA
                     FROM EMPRESA E,
                          (SELECT DECODE(NF.IDORIGDESTNF,'L',EL.CDPAIS,  'C',EC.CDPAIS,  'F',EF.CDPAIS)   AS CDPAIS,
                                  DECODE(NF.IDORIGDESTNF,'L',EL.SGESTADO,'C',EC.SGESTADO,'F',EF.SGESTADO) AS SGESTADO,
                                  NF.IDENTRSAIDOP, NF.CDEMPRESA, NF.CDINSCESTAST,  IP.VRCONTABILNF AS VRCONTABILNF,
                                  DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0) AS VRBASECALC,
                                  CASE WHEN CDFISCOPERNF NOT IN ('1410','1411','1414','1415','1660','1661','1662','2410','2411','2414','2415','2660','2661','2662','1603','2603')
                                       THEN DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF + NVL(IP.VRADICFECP,0),0) ELSE 0 END AS VRCREDITO,
                                  CASE WHEN CDFISCOPERNF NOT IN ('1401','1406','1407','1410','1411','1414','1415','1660','1661','1662','2401','2406','2407','2410','2411','2414','2415','2660','2661','2662')
                                       THEN DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF + NVL(IP.VRADICFECP,0),0) ELSE 0 END AS VRIMPOSTONF,
                                  CASE WHEN CDFISCOPERNF IN ('1410','1411','1414','1415','1660','1661','1662','2410','2411','2414','2415','2660','2661','2662')
                                       THEN DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF + NVL(IP.VRADICFECP,0),0) ELSE 0 END AS VRDEVMAPA,
                                  CASE WHEN CDFISCOPERNF IN ('1603', '2603')
                                       THEN DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF + NVL(IP.VRADICFECP,0),0) ELSE 0 END AS VRRESSAMAPA
                             FROM IMPOSTONF IP, NOTAFISCAL NF, ENDECLIE EC, ENDEFORN EF, ENDEFILI EL
                            WHERE IP.NRSEQUMAPAPU    = P_NRSEQUMAPAPU
                              AND NF.CDFILIAL        = IP.CDFILIAL
                              AND NF.NRLANCTONF      = IP.NRLANCTONF
                              AND EC.CDCLIENTE(+)    = NF.CDCLIENTE
                              AND EC.IDTPENDECLIE(+) = 'P'
                              AND EF.CDFORNECED(+)   = NF.CDFORNECED
                              AND EF.IDTPENDEFORN(+) = 'P'
                              AND EL.CDFILIAL(+)     = DECODE(NF.IDENTRSAIDOP,'E',NF.CDFILIEMIT,NF.CDFILIRECE)
                              AND EL.IDTPENDEFILI(+) = 'P') REG
                    WHERE E.CDEMPRESA    = REG.CDEMPRESA
                    GROUP BY REG.CDPAIS, REG.SGESTADO, DECODE(P_IDTIPOAPURA,'D','','M',REG.CDINSCESTAST)
                    ORDER BY REG.SGESTADO) LOOP

      V_VRTOTADEBI := NVL(C_DADOS.VRDEBITO,0);
      V_VRTOTACRED := NVL(C_DADOS.VRCREDITO,0) + NVL(C_DADOS.VRRESSAMAPA,0) + NVL(C_DADOS.VRDEVMAPA,0);

      V_VRIMPORECO := NVL(V_VRTOTADEBI,0) - NVL(V_VRTOTACRED,0);

      IF V_VRIMPORECO < 0 THEN
         V_VRSALDCRED := ABS(V_VRIMPORECO);
         V_VRIMPORECO := 0;
      ELSE
         V_VRSALDCRED := 0;
      END IF;

      IF NVL(V_SGESTADO,' ') <> C_DADOS.SGESTADO THEN
         V_NRSEQMAPAEST := 1;
      END IF;

      /******** Insere o item ********/
      INSERT INTO MAPAAPURESTADO
         (NRSEQUMAPAPU, NRSEQMAPAAPUREST, CDPAIS, SGESTADO, CDINSCESTAST, CDRECESUBTRIB, DTVENCSUBTRIB,
          VRCTBLDEB, VRBASECALCDEB, VRDEBITONORMAL, VROUTROSDEBITOS, VRTOTADEBI,
          VRCTBLCRED, VRBASECALCCRED, VRCREDITONORMAL, VRDEVMAPA, VRRESSAMAPA, VROUTROSCREDITOS, VRTOTACRED,
          VRCREDANTERIOR, VRIMPARECOLHER, VRSALDOCREDOR)
      VALUES
         (P_NRSEQUMAPAPU, V_NRSEQMAPAEST, C_DADOS.CDPAIS, C_DADOS.SGESTADO, C_DADOS.CDINSCESTAST, '', '',
          C_DADOS.VRCTBLDEB, C_DADOS.VRBASECALCDEB, C_DADOS.VRDEBITO, 0, V_VRTOTADEBI,
          C_DADOS.VRCTBLCRED, C_DADOS.VRBASECALCCRED, C_DADOS.VRCREDITO, C_DADOS.VRDEVMAPA, C_DADOS.VRRESSAMAPA, 0, V_VRTOTACRED,
          0, V_VRIMPORECO, V_VRSALDCRED);

      V_SGESTADO     := C_DADOS.SGESTADO;
      V_NRSEQMAPAEST := V_NRSEQMAPAEST + 1;

   END LOOP;

   /******** Para os estados que o cliente possui inscricao do Substituto Tributario deve ser informada apuracao,
             mesmo que nao haja movimento ********/
   FOR C_INSCR IN (SELECT IE.CDPAIS, IE.SGESTADO, IE.CDINSCESTAST
                     FROM MAPAAPURAC MP, INSCRESTADST IE
                    WHERE MP.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                      AND MP.CDEMPRESA    = IE.CDEMPRESA
                      AND MP.CDINSCESTA   = IE.CDINSCESTA
                      AND NOT EXISTS (SELECT CDPAIS, SGESTADO
                                           FROM MAPAAPURESTADO M
                                          WHERE M.CDPAIS       = IE.CDPAIS
                                            AND M.SGESTADO     = IE.SGESTADO
                                            AND M.NRSEQUMAPAPU = P_NRSEQUMAPAPU)) LOOP
      /******** Insere o item ********/
      INSERT INTO MAPAAPURESTADO
         (NRSEQUMAPAPU, NRSEQMAPAAPUREST, CDPAIS, SGESTADO, CDINSCESTAST, CDRECESUBTRIB, DTVENCSUBTRIB,
          VRCTBLDEB, VRBASECALCDEB, VRDEBITONORMAL, VROUTROSDEBITOS, VRTOTADEBI,
          VRCTBLCRED, VRBASECALCCRED, VRCREDITONORMAL, VRDEVMAPA, VRRESSAMAPA, VROUTROSCREDITOS, VRTOTACRED,
          VRCREDANTERIOR, VRIMPARECOLHER, VRSALDOCREDOR)
      VALUES
         (P_NRSEQUMAPAPU, 1, C_INSCR.CDPAIS, C_INSCR.SGESTADO, C_INSCR.CDINSCESTAST,
          '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

   END LOOP;

   /******** Para o estado da inscricao estadual do cliente deve ser informada apuracao, mesmo que nao haja movimento ********/
   FOR C_IE IN (SELECT IE.CDPAIS, IE.SGESTADO
                  FROM MAPAAPURAC MP, INSCRESTAD IE
                 WHERE MP.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                   AND MP.CDEMPRESA    = IE.CDEMPRESA
                   AND MP.CDINSCESTA   = IE.CDINSCESTA
                   AND NOT EXISTS (SELECT M.CDPAIS, M.SGESTADO
                                     FROM MAPAAPURESTADO M
                                    WHERE M.CDPAIS       = IE.CDPAIS
                                      AND M.SGESTADO     = IE.SGESTADO
                                      AND M.NRSEQUMAPAPU = P_NRSEQUMAPAPU)) LOOP
      /******** Insere o item ********/
      INSERT INTO MAPAAPURESTADO
         (NRSEQUMAPAPU, NRSEQMAPAAPUREST, CDPAIS, SGESTADO, CDINSCESTAST, CDRECESUBTRIB, DTVENCSUBTRIB,
          VRCTBLDEB, VRBASECALCDEB, VRDEBITONORMAL, VROUTROSDEBITOS, VRTOTADEBI,
          VRCTBLCRED, VRBASECALCCRED, VRCREDITONORMAL, VRDEVMAPA, VRRESSAMAPA, VROUTROSCREDITOS, VRTOTACRED,
          VRCREDANTERIOR, VRIMPARECOLHER, VRSALDOCREDOR)
      VALUES
         (P_NRSEQUMAPAPU, 1, C_IE.CDPAIS, C_IE.SGESTADO, '',
          '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
   END LOOP;

   /******** Apaga o detalhamento por UF dos Estados que deixaram de existir apos uma nova apuracao do Mapa ********/
   DELETE FROM DETAUFMAPAEST
    WHERE NOT NRSEQUMAPAPU||CDPAIS||SGESTADO IN (SELECT NRSEQUMAPAPU||CDPAIS||SGESTADO
                                                   FROM MAPAAPURESTADO
                                                  WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU)
      AND NRSEQUMAPAPU = P_NRSEQUMAPAPU;
END;