DROP PROCEDURE IF EXISTS `proc_billing`;
delimiter ;;
CREATE PROCEDURE `proc_billing`(IN days INT)
BEGIN
  DECLARE cursor_done INT DEFAULT 0;
  DECLARE bye_record INT DEFAULT 0;
  DECLARE v_acc_id BIGINT unsigned DEFAULT 0;
  DECLARE call_time INT DEFAULT 0;
  DECLARE duration_block INT DEFAULT 0;
  DECLARE provider_duration_block INT DEFAULT 0;
  DECLARE rateplan_id INT;
  DECLARE rateplan_name VARCHAR(64);
  DECLARE rateplan_rateid INT;
  DECLARE rateplan_tx_conn INT DEFAULT 0;
  DECLARE rateplan_cadency INT DEFAULT 0;
  DECLARE rateplan_markup INT DEFAULT 0;
  DECLARE rateplan_prepaid INT DEFAULT 0;
  DECLARE rateplan_tx_delay INT DEFAULT 0;
  DECLARE rateplan_tx_discard INT DEFAULT 0;
  DECLARE rateplan_strategy VARCHAR(1) DEFAULT 'T';
  DECLARE rateplan_rounding VARCHAR(1) DEFAULT 'T';
  DECLARE rate_prefix VARCHAR(20) DEFAULT NULL;
  DECLARE rate_name VARCHAR(64) DEFAULT NULL;
  DECLARE rate_type VARCHAR(32) DEFAULT NULL;
  DECLARE rate_value DOUBLE DEFAULT 0;
  DECLARE rate_tx_conn INT DEFAULT NULL;
  DECLARE rate_cadency INT DEFAULT NULL;
  DECLARE rate_tx_delay INT DEFAULT NULL;
  DECLARE rate_tx_discard INT DEFAULT NULL;
  DECLARE call_price DOUBLE DEFAULT 0;
  DECLARE providerid INT DEFAULT 0;
  DECLARE provider_name VARCHAR(64);
  DECLARE provider_start_delay INT DEFAULT 0;
  DECLARE provider_tx_discard INT DEFAULT 0;
  DECLARE cost_tx_connection INT DEFAULT 0;
  DECLARE cost_cadency INT DEFAULT 0;
  DECLARE cost_rate DOUBLE DEFAULT 0;
  DECLARE cost_price DOUBLE DEFAULT 0;
  DECLARE v_src_uri,v_dst_uri,v_callid,v_from_tag,v_to_tag,v_caller_id,
          v_callee_id,v_caller_domain,v_callee_domain,v_rpid,
          v_service,v_src_ip,v_user_agent,v_profile,v_accountcode VARCHAR(128);
  DECLARE v_time, v_bye_time, v_bye_inv_time DATETIME;
  DECLARE v_time_microseconds, v_bye_time_microseconds, diff_microseconds INT DEFAULT 0;
  DECLARE v_domain_acc VARCHAR(128);
  DECLARE v_username_acc VARCHAR(64);
  DECLARE v_reverse_billing BOOLEAN DEFAULT false;
  DECLARE v_gwid INT DEFAULT 0;
  DECLARE gw_address VARCHAR(255);
  DECLARE gw_name VARCHAR(64);
  DECLARE gw_rateid INT;
  DECLARE v_rec INT(1) DEFAULT 0;
  DECLARE v_q850_code INT(11);
  DECLARE v_q850_reason VARCHAR(60);
  DECLARE v_cnla VARCHAR(5);
  DECLARE v_cnlb VARCHAR(5);
  DECLARE v_mesma_area TINYINT(1) DEFAULT 0;
  DECLARE v_tipo VARCHAR(3);
  DECLARE v_rn1 VARCHAR(5);
  DECLARE v_rule_id INT DEFAULT 0;
  DECLARE v_channels INT unsigned DEFAULT 0;
  DECLARE reseller_id INT DEFAULT 0;
  DECLARE reseller_name VARCHAR(64);
  DECLARE reseller_billing_type INT DEFAULT 0;
  DECLARE reseller_markup DOUBLE DEFAULT 0;
  DECLARE reseller_rateplan_id INT DEFAULT 0;
  DECLARE reseller_rateplan_name VARCHAR(64);
  DECLARE reseller_rateplan_rateid INT DEFAULT 0;
  DECLARE reseller_matched_prefix VARCHAR(20);
  DECLARE reseller_matched_prefix_name VARCHAR(32);
  DECLARE reseller_rate DOUBLE DEFAULT 0;
  DECLARE reseller_price DOUBLE DEFAULT 0;
  DECLARE timeout_profile_keepalive INT DEFAULT NULL;
  DECLARE daily_quota BOOLEAN DEFAULT false;
  DECLARE daily_quota_type CHAR(1) DEFAULT NULL;
  DECLARE monthly_quota BOOLEAN DEFAULT false;
  DECLARE monthly_quota_type CHAR(1) DEFAULT NULL;

-- Funcao responsavel pelo fechamento dos bilhetes do sistema.
-- Author: Ricardo Limonta
-- Ultima atualizacao: 13/06/2013 10:00
-- Lista de Parametros:
-- N/A

  -- recupera os blilhetes que ainda nao foram fechados
  DECLARE inv_cursor CURSOR FOR SELECT id,src_uri,dst_uri,time,callid,from_tag,to_tag,caller_id,callee_id,caller_domain,
                                       callee_domain,rpid,service,src_ip,user_agent,profile,accountcode,gwid,
                                       rec,q850_code,q850_reason,cnla,cnlb,mesma_area,tipo,microseconds,
                                       rn1,rule_id,channels
                                FROM sippulse_reports.acc_report
                                WHERE method='INVITE'
                                AND cdr_id = '0'
                                AND sip_code = '200'
                                AND service in ('pstn', '0800', 'did', 'net', 'media', 'local', 'acobrar')
                                AND accountcode <> 'system'
                                AND time > DATE_ADD(CURRENT_DATE, INTERVAL - days DAY);

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursor_done = 1;

  -- se ocorrer algum erro mostra quais foram juntamente com o id do registro que estava sendo processado
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SHOW ERRORS;
    SELECT v_acc_id as MSG;
  END;

  -- abre o cursor da consulta
  OPEN inv_cursor;
  REPEAT
  FETCH inv_cursor INTO v_acc_id, v_src_uri, v_dst_uri, v_time, v_callid, v_from_tag, v_to_tag, v_caller_id,
                        v_callee_id, v_caller_domain, v_callee_domain, v_rpid, v_service, v_src_ip,
                        v_user_agent, v_profile, v_accountcode, v_gwid, v_rec, v_q850_code, v_q850_reason,
                        v_cnla, v_cnlb, v_mesma_area, v_tipo, v_time_microseconds, v_rn1, v_rule_id, v_channels;

    IF NOT cursor_done THEN

      -- reseta a variavel de controle de busca dos byes
      SET bye_record = 0;

      -- para cada registro encontrado, procura pelo registro de fechamento da chamada
      SELECT 1, time, invtime, microseconds INTO bye_record, v_bye_time, v_bye_inv_time, v_bye_time_microseconds
      FROM sippulse_reports.acc_report
      WHERE callid = v_callid
      AND ((from_tag=v_from_tag AND to_tag=v_to_tag) OR (from_tag=v_to_tag AND to_tag=v_from_tag))
      AND method = 'BYE'
      ORDER BY id LIMIT 1;

      IF bye_record = 1 THEN
        SET call_time = 0;
        SET diff_microseconds = 0;
        SET duration_block = 0;
        SET provider_duration_block = 0;
        SET rateplan_id = null;
        SET rateplan_name = null;
        SET rateplan_rateid = null;
        SET rateplan_cadency = 0;
        SET rateplan_tx_conn = 0;
        SET rateplan_markup = 0;
        SET rateplan_prepaid = null;
        SET rateplan_tx_delay = 0;
        SET rateplan_tx_discard = 0;
        SET rateplan_strategy = 'T';
        SET rateplan_rounding = 'T';
        SET rate_prefix = NULL;
        SET rate_name = NULL;
        SET rate_type = NULL;
        SET rate_value = 0;
        SET rate_tx_conn = NULL;
        SET rate_cadency = NULL;
        SET rate_tx_delay = NULL;
        SET rate_tx_discard = NULL;
        SET call_price = 0;
        SET providerid = 0;
        SET provider_name = null;
        SET provider_start_delay = 0;
        SET provider_tx_discard = 0;
        SET cost_cadency = 0;
        SET cost_tx_connection = 0;
        SET cost_rate = 0;
        SET cost_price = 0;
        SET reseller_matched_prefix = null;
        SET reseller_matched_prefix_name = null;
        SET reseller_id = 0;
        SET reseller_name = null;
        SET reseller_billing_type = 0;
        SET reseller_markup = 0;
        SET reseller_rateplan_id = 0;
        SET reseller_rateplan_name = null;
        SET reseller_rateplan_rateid = 0;
        SET reseller_rate = 0;
        SET reseller_price = 0;
        SET gw_address = null;
        SET gw_name = null;
        SET gw_rateid = null;
        SET timeout_profile_keepalive = null;
        SET v_reverse_billing = false;
        SET daily_quota = false;
        SET daily_quota_type = null;
        SET monthly_quota = false;
        SET monthly_quota_type = null;

        -- --------------------------------------------
        -- RECUPERA OS USERNAME E DOMAIN DA CHAMADA
        -- --------------------------------------------
        SET v_domain_acc = SUBSTRING(v_accountcode, LOCATE('@', v_accountcode) + 1);
        SET v_username_acc = SUBSTRING(v_accountcode, 1, LOCATE('@', v_accountcode) -1);

        -- --------------------------------------------------------
        -- RECUPERA OS DADOS DE TARIFACAO E KEEPALIVE DO ASSINANTE
        -- --------------------------------------------------------
        SELECT rp.id,
               rp.name,
               rp.rateid,
               rp.tx_conn,
               rp.cadency,
               rp.markup,
               rp.tx_delay,
               rp.prepaid,
               rp.tx_discard,
               rp.strategy,
               rp.rounding,
               p.timeout_profile_keepalive
        INTO rateplan_id,
             rateplan_name,
             rateplan_rateid,
             rateplan_tx_conn,
             rateplan_cadency,
             rateplan_markup,
             rateplan_tx_delay,
             rateplan_prepaid,
             rateplan_tx_discard,
             rateplan_strategy,
             rateplan_rounding,
             timeout_profile_keepalive
        FROM sippulse.subscriber s,
             sippulse.rate_plan rp,
             sippulse.profile p
        WHERE s.username = v_rpid
        AND   s.domain = v_domain_acc
        AND   rp.id = s.rateplan_id
        AND   p.profile = s.profile
        AND   p.domain = s.domain;

        -- ---------------------------------------------------------------------
        -- RECUPERA OS DADOS DE QUOTA E REVENDA DA CONTA AGRUPADORA
        -- ---------------------------------------------------------------------
        SELECT s.reseller_id,
               s.reseller_billing_type,
               s.reseller_markup,
               s.reseller_rateplan_id,
               s.daily_quota,
               s.daily_quota_type,
               s.monthly_quota,
               s.monthly_quota_type
        INTO reseller_id,
             reseller_billing_type,
             reseller_markup,
             reseller_rateplan_id,
             daily_quota,
             daily_quota_type,
             monthly_quota,
             monthly_quota_type
        FROM sippulse.subscriber s
        WHERE s.username = v_username_acc
        AND   s.domain = v_domain_acc;

        -- -----------------------------------------------------------------------
        -- CALCULA O TEMPO DA CHAMADA BASEADO NA CONFIGURACAO DO PLANO DE TARIFAS
        -- -----------------------------------------------------------------------
        IF (rateplan_strategy = 'I') THEN
            -- I: INVITIME LEVA EM CONSIDERACAO O CAMPOR INVITIME DO BYE PARA CALCULO DA DURACAO
            SELECT (UNIX_TIMESTAMP(v_bye_inv_time)-UNIX_TIMESTAMP(v_time)) INTO call_time;
        ELSE
            -- T: TRANSACTION LEVA EM CONSIDERACAO O CAMPO TIME DO BYE PARA CALCULO DA DURACAO
            SELECT (UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_time)) INTO call_time;
        END IF;

        -- --------------------------------------------------------------------------------
        -- CALCULA O ARREDONDAMENTO DA CHAMADA BASEADO NA CONFIGURACAO DO PLANO DE TARIFAS
        -- --------------------------------------------------------------------------------
        IF (rateplan_rounding = 'A') THEN
            SET diff_microseconds = v_bye_time_microseconds - v_time_microseconds;
            IF (diff_microseconds >= 500000) THEN
                SET call_time = call_time + 1;
            ELSEIF (diff_microseconds < -500000) THEN
                SET call_time = call_time - 1;
            END IF;
        ELSEIF (rateplan_rounding = 'U') THEN
            SET diff_microseconds = v_bye_time_microseconds - v_time_microseconds;
            IF (diff_microseconds >= 1) THEN
                SET call_time = call_time + 1;
            END IF;
        END IF;

        -- verifica se a duracao eh negativa
        IF (call_time < 0) THEN
            SET call_time = 0;
        -- verifica se a chamada ultrapassou o tempo maximo configurado no profile e marca como timeout
        ELSEIF (call_time >= timeout_profile_keepalive) THEN
            SET v_service = 'timeout';
        ELSE
            -- verifica se a chamada eh a cobrar e desconta 6 segundos
            IF (v_service = 'acobrar') THEN
                IF (call_time < 6) THEN
                    SET call_time = 0;
                ELSE
                    SET call_time = call_time - 6;
                END IF;
            END IF;
        END IF;

        IF ((call_time > 0) &&
            (v_service <> 'timeout') &&
            (v_service <> 'net') &&
            (v_service <> 'media')) THEN

            -- em casos de chamada did, verifica se tarifacao reversa esta ativa
            IF ((v_service = 'did') || (v_service = '0800')) THEN
                SELECT d.reverse_billing
                FROM sippulse.did d
                WHERE d.alias_username = substring_index(substring(v_dst_uri, 5), '@', 1)
                INTO v_reverse_billing;
            END IF;

            -- --------------------------------------
            -- CALCULA O VALOR DE CUSTO DA CHAMADA
            -- --------------------------------------
            IF (v_gwid > 0) THEN
                -- recupera os dados do provedor utilizado para realizar a chamada
                SELECT pr.id,
                       pr.name,
                       pr.cadency,
                       pr.tx_conn,
                       pr.start_delay,
                       pr.tx_discard,
                       dg.address,
                       dg.description,
                       dg.rateid
                FROM sippulse.dr_gateways dg, sippulse.provider pr
                WHERE dg.id = v_gwid
                AND pr.id = dg.providerid
                INTO providerid,
                     provider_name,
                     cost_cadency,
                     cost_tx_connection,
                     provider_start_delay,
                     provider_tx_discard,
                     gw_address,
                     gw_name,
                     gw_rateid;

                IF ((gw_rateid IS NOT NULL) && (call_time > provider_tx_discard)) THEN
                    -- calcula o custo da chamada, de acordo com o provedor utilizado
                    -- para chamadas pstn, usa como base o numero de destino
                    -- chamadas did, 0800 e acobrar, usa o numero de origem (reverso)
                    IF ((v_service = 'acobrar') ||
                        ((v_service = '0800') && (v_reverse_billing)) ||
                        ((v_service = 'did') && (v_reverse_billing))) THEN

                        -- recupera o valor de custo da chamada com base no src_uri
                        SELECT sippulse.retrieve_call_cost(v_gwid, v_src_uri) INTO cost_rate;

                        -- calcula o valor de custo da chamada
                        IF (cost_rate IS NOT NULL) THEN
                            SELECT sippulse.calculate_call_price(cost_tx_connection,
                                                                 cost_cadency,
                                                                 provider_start_delay,
                                                                 call_time,
                                                                 cost_rate) INTO cost_price;
                        END IF;
                    ELSEIF ((v_service = 'pstn') || (v_service = 'local')) THEN

                        -- recupera o valor de custo da chamada com base no dst_uri
                        SELECT sippulse.retrieve_call_cost(v_gwid, v_dst_uri) INTO cost_rate;

                        -- calcula o valor de custo da chamada
                        IF (cost_rate IS NOT NULL) THEN
                            SELECT sippulse.calculate_call_price(cost_tx_connection,
                                                                 cost_cadency,
                                                                 provider_start_delay,
                                                                 call_time,
                                                                 cost_rate) INTO cost_price;
                        END IF;
                    END IF;

                    -- calcula o provider duration block
                    SELECT sippulse_reports.calculate_block_duration(cost_tx_connection,
                                                                     cost_cadency,
                                                                     provider_start_delay,
                                                                     call_time) INTO provider_duration_block;
                END IF;
            END IF;

            -- --------------------------------------
            -- CALCULA O VALOR DE VENDA DA CHAMADA
            -- --------------------------------------
            IF ((v_service = 'acobrar') ||
                ((v_service = '0800') && (v_reverse_billing)) ||
                ((v_service = 'did') && (v_reverse_billing))) THEN

                -- recupera o custo da chamada, baseando-se no originador (reverso)
                CALL sippulse.retrieve_call_rate(v_domain_acc,
                                                 rateplan_rateid,
                                                 rateplan_markup,
                                                 v_src_uri,
                                                 rate_prefix,
                                                 rate_name,
                                                 rate_type,
                                                 rate_value,
                                                 rate_tx_conn,
                                                 rate_cadency,
                                                 rate_tx_delay,
                                                 rate_tx_discard);

            ELSEIF ((v_service = 'pstn') || (v_service = 'local')) THEN
                -- calcula a tarifa de venda da chamada
                CALL sippulse.retrieve_call_rate(v_domain_acc,
                                                 rateplan_rateid,
                                                 rateplan_markup,
                                                 v_dst_uri,
                                                 rate_prefix,
                                                 rate_name,
                                                 rate_type,
                                                 rate_value,
                                                 rate_tx_conn,
                                                 rate_cadency,
                                                 rate_tx_delay,
                                                 rate_tx_discard);
            END IF;

            -- verifica se existe tx_discard na tarifa de venda
            IF (rate_tx_discard IS NOT NULL) THEN
                SET rateplan_tx_discard = rate_tx_discard;
            END IF;

            -- verifica se a duracao esta acima do tempo de descarte
            IF (call_time > rateplan_tx_discard) THEN

                -- verifica se existe tx_conn e cadencia na tarifa de venda
                IF ((rate_tx_conn IS NOT NULL) && (rate_cadency IS NOT NULL)) THEN
                    SET rateplan_tx_conn = rate_tx_conn;
                    SET rateplan_cadency = rate_cadency;
                END IF;

                -- verifica se existe tx_delay na tarifa de venda
                IF (rate_tx_delay IS NOT NULL) THEN
                    SET rateplan_tx_delay = rate_tx_delay;
                END IF;

                -- calcula o valor de custo da chamada
                SELECT sippulse.calculate_call_price(rateplan_tx_conn,
                                                     rateplan_cadency,
                                                     rateplan_tx_delay,
                                                     call_time,
                                                     rate_value) INTO call_price;

                -- calcula o bloco de duracao de venda
                SELECT calculate_block_duration(rateplan_tx_conn,
                                                rateplan_cadency,
                                                rateplan_tx_delay,
                                                call_time) INTO duration_block;

                -- --------------------------------------
                -- CALCULA O VALOR DA REVENDA DA CHAMADA
                -- --------------------------------------
                IF (reseller_id > 0) THEN
                    -- recupera o nome da revenda
                    SELECT r.name FROM sippulse.reseller r WHERE r.id = reseller_id INTO reseller_name;

                    -- atualiza o rate com base na modalidade de tarifacao da revenda
                    -- 0: markup
                    -- 1: plano de tarifas
                    IF (reseller_billing_type = 0) THEN
                        -- modalidade markup
                        SELECT sippulse.retrieve_reseller_markup(rate_value, reseller_markup) INTO reseller_rate;
                        SELECT sippulse.retrieve_reseller_markup(call_price, reseller_markup) INTO reseller_price;
                        SET reseller_matched_prefix = rate_prefix;
                        SET reseller_matched_prefix_name = rate_name;
                    ELSE
                        -- recupera a taxa de conexao e cadencia do Reseller Rate Plan
                        SELECT rrp.name, rrp.reseller_rate_id
                        FROM sippulse.reseller_rate_plan rrp
                        WHERE rrp.id = reseller_rateplan_id
                        INTO reseller_rateplan_name, reseller_rateplan_rateid;

                        IF ((v_service = 'acobrar') ||
                            ((v_service = '0800') && (v_reverse_billing)) ||
                            ((v_service = 'did') && (v_reverse_billing))) THEN
                            -- modalidade plano de tarifas, recupera a tarifa para a chamada
                            CALL sippulse.retrieve_reseller_call_rate(reseller_id,
                                                                      reseller_rateplan_rateid,
                                                                      v_src_uri,
                                                                      reseller_matched_prefix,
                                                                      reseller_matched_prefix_name,
                                                                      reseller_rate);
                        ELSEIF ((v_service = 'pstn') || (v_service = 'local')) THEN
                            CALL sippulse.retrieve_reseller_call_rate(reseller_id,
                                                                      reseller_rateplan_rateid,
                                                                      v_dst_uri,
                                                                      reseller_matched_prefix,
                                                                      reseller_matched_prefix_name,
                                                                      reseller_rate);
                        END IF;

                        -- recupera o valor da revenda
                        SELECT sippulse.calculate_call_price(rateplan_tx_conn,
                                                             rateplan_cadency,
                                                             rateplan_tx_delay,
                                                             call_time,
                                                             reseller_rate) INTO reseller_price;
                    END IF;
                END IF;

                -- --------------------------------------
                -- INCREMENTA O CONSUMO DA QUOTA DIARIA
                -- --------------------------------------
                IF (daily_quota) THEN
                    -- INCREMENTE O CONSUMO DA QUOTA EM MINUTOS
                    IF (daily_quota_type = 'M') THEN
                        update sippulse.subscriber s set s.daily_quota_consumed = (s.daily_quota_consumed + ROUND(call_time/60,2))
                        where s.username = v_username_acc
                        and s.domain = v_domain_acc;
                    -- INCREMENTE O CONSUMO DA QUOTA EM VALORES
                    ELSEIF (daily_quota_type = 'V') THEN
                        IF (reseller_id IS NULL) THEN
                            update sippulse.subscriber s set s.daily_quota_consumed = (s.daily_quota_consumed + call_price)
                            where s.username = v_username_acc
                            and s.domain = v_domain_acc;
                        ELSE
                            update sippulse.subscriber s set s.daily_quota_consumed = (s.daily_quota_consumed + reseller_price)
                            where s.username = v_username_acc
                            and s.domain = v_domain_acc;
                        END IF;
                    END IF;
                END IF;

                -- --------------------------------------
                -- INCREMENTA O CONSUMO DA QUOTA MENSAL
                -- --------------------------------------
                IF (monthly_quota) THEN
                    -- INCREMENTE O CONSUMO DA QUOTA EM MINUTOS
                    IF (monthly_quota_type = 'M') THEN
                        update sippulse.subscriber s set s.monthly_quota_consumed = (s.monthly_quota_consumed + ROUND(call_time/60,2))
                        where s.username = v_username_acc
                        and s.domain = v_domain_acc;
                    -- INCREMENTE O CONSUMO DA QUOTA EM VALORES
                    ELSEIF (monthly_quota_type = 'V') THEN
                        IF (reseller_id IS NULL) THEN
                            update sippulse.subscriber s set s.monthly_quota_consumed = (s.monthly_quota_consumed + call_price)
                            where s.username = v_username_acc
                            and s.domain = v_domain_acc;
                        ELSE
                            update sippulse.subscriber s set s.monthly_quota_consumed = (s.monthly_quota_consumed + reseller_price)
                            where s.username = v_username_acc
                            and s.domain = v_domain_acc;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;

        -- ------------------------------------------
        -- INSERE O BILHETE FECHADO NA TABELA CDRS
        -- ------------------------------------------
        INSERT INTO sippulse_reports.cdrs_report
                          (src_uri,
                           dst_uri,
                           call_start_time,
                           duration,
                           start_delay,
                           tx_discard,
                           sip_call_id,
                           sip_from_tag,
                           sip_to_tag,
                           created,
                           caller_id,
                           callee_id,
                           caller_domain,
                           callee_domain,
                           rpid,
                           cost,
                           cost_price,
                           cost_delay,
                           cost_discard,
                           service,
                           service_type,
                           src_ip,
                           user_agent,
                           profile,
                           rate,
                           price,
                           accountcode,
                           providerid,
                           provider_name,
                           provider_tx_conn,
                           provider_cadency,
                           provider_duration_block,
                           rateplan_id,
                           rateplan_name,
                           rateplan_rateid,
                           rateplan_tx_conn,
                           rateplan_cadency,
                           duration_block,
                           rateplan_markup,
                           rateplan_prepaid,
                           matched_prefix,
                           matched_prefix_name,
                           reseller_id,
                           reseller_name,
                           reseller_rate,
                           reseller_price,
                           reseller_rateplan_id,
                           reseller_rateplan_name,
                           reseller_rateplan_rateid,
                           reseller_markup,
                           reseller_matched_prefix,
                           reseller_matched_prefix_name,
                           gwid,
                           gw_address,
                           gw_name,
                           gw_rateid,
                           rec,
                           q850_code,
                           q850_reason,
                           cnla,
                           cnlb,
                           mesma_area,
                           tipo,
                           rn1,
                           rule_id,
                           channels)
        VALUES
                           (v_src_uri,
                            v_dst_uri,
                            v_time,
                            call_time,
                            rateplan_tx_delay,
                            rateplan_tx_discard,
                            v_callid,
                            v_from_tag,
                            v_to_tag,
                            NOW(),
                            v_caller_id,
                            v_callee_id,
                            v_caller_domain,
                            v_callee_domain,
                            v_rpid,
                            cost_rate,
                            cost_price,
                            provider_start_delay,
                            provider_tx_discard,
                            v_service,
                            rate_type,
                            v_src_ip,
                            v_user_agent,
                            v_profile,
                            rate_value,
                            call_price,
                            v_accountcode,
                            providerid,
                            provider_name,
                            cost_tx_connection,
                            cost_cadency,
                            provider_duration_block,
                            rateplan_id,
                            rateplan_name,
                            rateplan_rateid,
                            rateplan_tx_conn,
                            rateplan_cadency,
                            duration_block,
                            rateplan_markup,
                            rateplan_prepaid,
                            rate_prefix,
                            rate_name,
                            reseller_id,
                            reseller_name,
                            reseller_rate,
                            reseller_price,
                            reseller_rateplan_id,
                            reseller_rateplan_name,
                            reseller_rateplan_rateid,
                            reseller_markup,
                            reseller_matched_prefix,
                            reseller_matched_prefix_name,
                            v_gwid,
                            gw_address,
                            gw_name,
                            gw_rateid,
                            v_rec,
                            v_q850_code,
                            v_q850_reason,
                            v_cnla,
                            v_cnlb,
                            v_mesma_area,
                            v_tipo,
                            v_rn1,
                            v_rule_id,
                            v_channels);

        -- ------------------------------------------
        -- MARCA O REGISTRO COMO FECHADO NA TABELA ACC
        -- ------------------------------------------
        UPDATE sippulse_reports.acc_report SET cdr_id=last_insert_id() WHERE id = v_acc_id;
      END IF;
      SET cursor_done = 0;
    END IF;
  UNTIL cursor_done END REPEAT;
END
 ;;