CREATE OR REPLACE FUNCTION DES_ISS_BH_R(
   P_CDEMPRESA      IN NOTAFISCAL.CDEMPRESA%TYPE,
   P_CDINSCMUNI     IN NOTAFISCAL.CDINSCMUNI%TYPE,
   P_DTINICIAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL        IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_VERSAO         VARCHAR2,
   P_NRSEQARQMAG    IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CDIMPOSTO      IN VARCHAR2,
   P_NRINSJUREMPR   IN VARCHAR2) RETURN NUMBER
   AS
   V_REG_S          NUMBER;
   V_CONTADOR       NUMBER;
   V_ISSQN_E        VARCHAR2(2);
   V_ISSQN_RET_E    VARCHAR2(2);
   V_CDSITUCRT      VARCHAR2(1);
   V_CONTREGA       NUMBER;
   
BEGIN
   V_ISSQN_E     := '30';
   V_ISSQN_RET_E := '35';
   V_CONTREGA    := 0;
   BEGIN
      SELECT NVL(MAX(CDSITUCRT), '3') AS CDSITUCRT
        INTO V_CDSITUCRT
        FROM PARAMEMPRESA
       WHERE CDEMPRESA = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
           V_CDSITUCRT := '3';
   END;
   /*GERA REGISTRO ISS SERVICOS TOMADOS (TIPO - R) */
   FOR C_ISS_R IN (SELECT  'R'                                                                               ||'|'||   /*TIPO*/
                           TO_CHAR(LAST_DAY(DTPAGA),'ddmmyyyy')                                                        ||'|'||   /*DATA DE PAGAMENTO*/
                           TO_CHAR(DTEMISSAO,'ddmmyyyy')                                                     ||'|'||   /*DATA DE EMISSAO*/
                           CDMODDOCFISC                                                                      ||'|'||   /*MODELO DE DOCUMENTO DES*/
                           SERIE                                                                             ||'|'||   /*SERIE DES*/
                           SUBSERIE                                                                          ||'|'||   /*SUBSERIE*/
                           SITU_ESP                                                                          ||'|'||   /*SITUACAO ESPECIAL*/
                           IDMTVNAORET                                                                       ||'|'||   /*MOTIVO DE NAO RETENCAO*/
                           MUNICIPIOIBGE                                                                     ||'|'||   /*MUNICIPIO IBGE*/
                           DECODE(VR_RET, 0, '2', '1')                                                       ||'|'||   /*VALOR RETIDO*/
                           NUM_DOC                                                                           ||'|'||   /*NUMERO DOCUMENTO*/
                           LTRIM(TO_CHAR(VRNOTAFISC, '999999990.00'))                                        ||'|'||   /*VALOR DA NOTA*/
                           LTRIM(TO_CHAR(VRPRODUTOS, '999999990.00'))                                        ||'|'||   /*VALOR DOS SERVICOS*/
                           DECODE(IDTPFORNE, '1', ALIQUOTA, (CASE WHEN IDTPFORNE IN ('1','2') AND VR_RET > 0 THEN ALIQUOTA ELSE 0.00 END))  ||'|'|| /*ALIQUOTA*/
                           DECODE(IDTPFORNE, '1', '1', '2', '3', '2')                                        ||'|'||   /*TIPO DO FORNECEDOR*/
                           REPLACE(REPLACE(REPLACE(INSCMUNI,'-',''),'/',''),'.','')                          ||'|'||   /*INSCRICAO MUNICIPAL*/
                           CNPJ                                                                              ||'|'||   /*INSCRICAO JURIDICA*/
                           CPF                                                                               ||'|'||   /*CPF*/
                           RAZSOC                                                                            ||'|'||   /*RAZAO SOCIAL*/
                           LOGRADOURO                                                                        ||'|'||   /*ENDERECO*/
                           TRIM(NUMERO)                                                                      ||'|'||   /*NUMERO*/
                           TRIM(COMPLEMENTO)                                                                 ||'|'||   /*COMPLEMENTO*/
                           TRIM(BAIRRO)                                                                      ||'|'||   /*BAIRRO*/
                           TRIM(MUNICIPREST)                                                                 ||'|'||   /*MUNICIPO DO PRESTADOR*/
                           TRIM(PAISPREST)                                                                   ||'|'||   /*PAIS DO PRESTADOR*/
                           TRIM(REPLACE(CEP_PREST,'-',''))                                                   ||'|'||   /*CEP DO PRESTADOR*/
                           TRIM(TEL_PREST)                                                                   ||'|'||   /*TELEFONE DO PRESTADOR*/
                           TRIM(EMAIL_PREST)                                                                 ||'|'||   /*EMAIL DO PRESTADOR*/
                           INS_MUNI_TERC                                                                     ||'|'||   /*INSCRICAO DE TERCEIROS*/
                           CNPJ_TERC                                                                         ||'|'||   /*INSCRICAO JURIDICA DE TERCEIROS*/
                           CPF_TERC                                                                          ||'|'||   /*CPF DE TERCEIROS*/
                           RAZ_SOC_TERC                                                                      ||'|'||   /*RAZAO SOCIAL DE TERCEIROS*/
                           LOGR_TERC                                                                         ||'|'||   /*LOGRADOURO DE TERCEIROS*/
                           NUM_TERC                                                                          ||'|'||   /*NUMERO DE TERCEIROS*/
                           COMP_END_TERC                                                                     ||'|'||   /*COMPLEMENTO DE TERCEIROS*/
                           BAI_TERC                                                                          ||'|'||   /*BAIRRO DE TERCEIROS*/
                           MUNIC_TERC                                                                        ||'|'||   /*MUNICIPIO DD TERCEIROS*/
                           PAIS_TERC                                                                         ||'|'||   /*PAIS DE TERCEIROS*/
                           CEP_TERC                                                                          ||'|'||   /*CEP DE TERCEIROS*/
                           TEL_TERC                                                                          ||'|'||   /*TELEFONE DE TERCEIROS*/
                           EMAIL_TERC                                                                        ||'|'||   /*EMAIL DE TERCEIROS*/
                           MUNIC_PREST                                                                       ||'|'||   /*MUNICIPIO DO PRESTADOR*/
                           PAIS_PREST                                                                        ||'|'||   /*PAIS PRESTADOR*/
                           DESC_EVEN                                                                         ||'|'||   /*DESCRICAO DO EVENTO*/
                           DT_EVEN                                                                           ||'|' AS REG_ISS_R /*DATA DO EVENTO*/
                     FROM(SELECT
                                NF.DTEMISSAO AS DTPAGA,
                                NF.DTEMISSAO,
                                MO.CDMODDOCFISC,
                                DES_ISS_SERIE(P_CDEMPRESA, P_CDINSCMUNI, NF.CDSERIE) AS SERIE,
                                '' AS SUBSERIE,
                                DES_ISS_SITU_ESP(P_CDEMPRESA, P_CDINSCMUNI, PR.CDARVPROD, NF.CDFILIAL, NF.NRLANCTONF) AS SITU_ESP,
                                PF.IDMTVNAORET,
                                MU.CDMUNICIBGE AS MUNICIPIOIBGE,
                                '' AS REG_ESP_TRIB,
                                SUM(NVL(DECODE(II.CDIMPOSTONFE,V_ISSQN_RET_E,DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0),0),0)) AS VR_RET,
                                NF.NRNOTAFISC AS NUM_DOC,
                                NF.VRNOTAFISC,
                                NF.VRPRODUTOS,
                                PF.IDTPFORNE,
                                DECODE(IT.CDFISCALIT, '1', IT.VRALIQITEM , 0) AS ALIQUOTA,
                                DECODE(NF.IDSTATUS,'A','1','C','2','3'),
                                DECODE(MU.CDMUNICIBGE, '3106200', FO.NRINSMUNFORN, '') AS INSCMUNI,
                                DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'-',''),'/',''),'.','')),14,FO.NRINSJURFORN,' ') AS CNPJ,
                                DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'-',''),'/',''),'.','')),11,FO.NRINSJURFORN,' ') AS CPF,
                                NVL(FO.NMRAZSOCFORN,'DIVERSOS') AS RAZSOC,
                                EF.DSENDEFORN AS LOGRADOURO,
                                SUBSTR(EF.DSENDEFORN,INSTR(EF.DSENDEFORN,' ', '-1')) AS NUMERO,
                                EF.DSCOMPENDFOR AS COMPLEMENTO,
                                EF.NMBAIRFORN AS BAIRRO,
                                MU.CDMUNICIBGE AS MUNICIPREST,
                                PS.CDPAISBACEN AS PAISPREST,
                                DECODE(PS.CDPAISBACEN, '1058', EF.NRCEPFORN, '' ) AS CEP_PREST,
                                EF.NRTELEFORN AS TEL_PREST,
                                EF.DSEMAILFORN AS EMAIL_PREST,
                                '' AS INS_MUNI_TERC,
                                '' AS CNPJ_TERC,
                                '' AS CPF_TERC,
                                '' AS RAZ_SOC_TERC,
                                '' AS LOGR_TERC,
                                '' AS NUM_TERC,
                                '' AS COMP_END_TERC,
                                '' AS BAI_TERC,
                                '' AS MUNIC_TERC,
                                '' AS PAIS_TERC,
                                '' AS CEP_TERC,
                                '' AS TEL_TERC,
                                '' AS EMAIL_TERC,
                                '' AS MUNIC_PREST,
                                NVL(PS.CDPAISBACEN, '1058') AS PAIS_PREST,
                                '' AS DESC_EVEN,
                                '' AS DT_EVEN
                            FROM NOTAFISCAL NF, ITEMPROIMPOS IT, IMPOSTO II, ITEMPRODUTO IP, FORNECEDOR FO, ENDEFORN EF, MUNICIPIO MU,
                                 PAIS PS, MUNICIPIO MS, INSCRMUNICIP INS, PARAMMUNIFORNE PF,
                                 PRODUTO PR, MODDOCFISCMUNI MO, PARAMMUNITPOPER PA, (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                                                                            FROM IMPOSTONF I, MAPAAPURAC M
                                                                                           WHERE I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                                                                                             AND M.DTINIPERIODO = P_DTINICIAL
                                                                                             AND M.DTFIMPERIODO = P_DTFINAL
                                                                                             AND M.CDEMPRESA    = P_CDEMPRESA
                                                                                             AND M.CDINSCMUNI   = P_CDINSCMUNI
                                                                                             AND M.CDIMPOSTO    IN (V_ISSQN_E, V_ISSQN_RET_E)) IM
                           WHERE NF.CDFILIAL     = IT.CDFILIAL
                             AND NF.NRLANCTONF   = IT.NRLANCTONF
                             AND NF.CDSERIEENTR  = PA.CDSERIENF
                             AND II.CDIMPOSTO    = IT.CDIMPOSTO
                             AND II.CDIMPOSTONFE IN (V_ISSQN_E, V_ISSQN_RET_E)
                             AND NF.CDFILIAL     = IM.CDFILIAL
                             AND NF.NRLANCTONF   = IM.NRLANCTONF
                             AND NF.CDINSCMUNI   = P_CDINSCMUNI
                             AND NF.CDEMPRESA    = P_CDEMPRESA
                             AND IP.CDFILIAL     = IT.CDFILIAL
                             AND IP.NRLANCTONF   = IT.NRLANCTONF
                             AND IP.NRSEQITPROD  = IT.NRSEQITPROD
                             AND IP.CDPRODUTO    = PR.CDPRODUTO
                             AND MO.IDDECLARACAO = 'DS'
                             AND PA.IDDECLARACAO = PF.IDDECLARACAO
                             AND MO.IDDECLARACAO = PA.IDDECLARACAO
                             AND PA.CDEMPRESA    = P_CDEMPRESA
                             AND PA.CDINSCMUNI   = P_CDINSCMUNI
                             AND PA.IDENTRSAIDOP = NF.IDENTRSAIDOP
                             AND NF.IDENTRSAIDOP = 'E'
                             AND PA.CDTIPOOPER   = NF.CDTIPOOPER
                             AND MO.CDMODDOCFISC = PA.CDMODDOCMUNI
                             AND FO.CDFORNECED   = NF.CDFORNECED
                             AND FO.CDFORNECED   = EF.CDFORNECED
                             AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO
                             AND EF.SGESTADO     = MU.SGESTADO
                             AND EF.CDPAIS       = PS.CDPAIS
                             AND MU.CDPAIS       = PS.CDPAIS
                             AND EF.IDTPENDEFORN = 'P'
                             AND INS.CDINSCMUNI  = P_CDINSCMUNI
                             AND INS.CDEMPRESA   = P_CDEMPRESA
                             AND INS.CDMUNICIPIO = MS.CDMUNICIPIO
                             AND INS.CDPAIS      = MS.CDPAIS
                             AND INS.SGESTADO    = MS.SGESTADO
                             AND NF.CDFORNECED   = PF.CDFORNECED
                             AND PF.CDEMPRESA    = P_CDEMPRESA
                             AND PF.CDINSCMUNI   = P_CDINSCMUNI
                           GROUP BY NF.DTEMISSAO,
                                 DES_ISS_SERIE(P_CDEMPRESA, P_CDINSCMUNI, NF.CDSERIE),
                                 NF.CDINSCMUNI,
                                 MO.CDMODDOCFISC,
                                 MU.CDMUNICIBGE,
                                 NF.NRNOTAFISC,
                                 NF.VRNOTAFISC,
                                 NF.VRPRODUTOS,
                                 EF.DSENDEFORN,
                                 EF.DSCOMPENDFOR,
                                 EF.NMBAIRFORN,
                                 MU.CDMUNICIBGE,
                                 PS.CDPAISBACEN,
                                 DECODE(IT.CDFISCALIT, '1', IT.VRALIQITEM , 0),
                                 DECODE(NF.IDSTATUS,'A','1','C','2','3'),
                                 DECODE(MU.CDMUNICIBGE, '3106200', FO.NRINSMUNFORN, ''),
                                 DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'-',''),'/',''),'.','')),14,FO.NRINSJURFORN,' '),
                                 DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'-',''),'/',''),'.','')),11,FO.NRINSJURFORN,' '),
                                 NVL(FO.NMRAZSOCFORN,'DIVERSOS'),
                                 DECODE(PS.CDPAISBACEN, '1058', EF.NRCEPFORN, '' ),
                                 NVL(PS.CDPAISBACEN, '1058'),
                                 EF.NRTELEFORN,
                                 EF.DSEMAILFORN,
                                 DECODE(PS.CDPAISBACEN, '1058', EF.NRCEPFORN, '' ),
                                 DES_ISS_SITU_ESP(P_CDEMPRESA, P_CDINSCMUNI, PR.CDARVPROD, NF.CDFILIAL, NF.NRLANCTONF),
                                 PF.IDMTVNAORET,
                                 PF.IDTPFORNE))LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('02'||LPAD(RTRIM(V_CONTREGA), 8, '0'), C_ISS_R.REG_ISS_R , P_NRSEQARQMAG);
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
   END LOOP;
   RETURN V_REG_S;
END;
/