Untitled
unknown
plain_text
a year ago
13 kB
3
Indexable
SELECT DISTINCT
A.ID as codg_chave_acesso_bpe,
CAST(B.TPAG AS INTEGER) AS codg_forma_pagamento_bilhete,
B.XPAG as desc_forma_outro_pagamento,
COALESCE(CAST(B.CARD_TPINTEGRA AS INTEGER),0) AS tipo_integracao,
COALESCE(CAST(B.CARD_CNPJ AS BIGINT),0) AS numr_cnpj_credencd,
COALESCE(CAST(B.CARD_TBAND AS INTEGER),0) AS codg_bandeira_operadora,
B.CARD_XBAND as desc_outra_operadora_cartao,
B.CARD_CAUT as numr_autoriz_operadora_cartao,
COALESCE(B.CARD_NSUTRANS,'0') as numr_nsu_transacao,
B.CARD_NSUHOST as numr_sequencial_host,
COALESCE(CAST(B.CARD_NPARCELAS AS INTEGER),0) AS qtde_parcela_pagamento,
B.CARD_INFADCARD as info_adicional_cartao,
-- CAST(B.NR_PAG AS BIGINT) AS numr_pagamento_bpe,
substr(B.id,46) as numr_pagamento_bpe,
CAST(LPAD(B.VPAG,16,'0') AS DECIMAL(15,2)) AS valr_pagamento_bpe,
348 as id_identidade_universo
FROM BPE.BPE_INFBPE A
INNER JOIN BPE.BPE_INFBPE_PAG B ON B.chbpe = A.ID AND B.ide_dhemi_nums = A.ide_dhemi_nums
where A.data_carga between unix_timestamp('2023-07-01') and unix_timestamp('2023-07-07 23:59:59') ----- DATA YYYY-MM-DD
;
SELECT DISTINCT
A.ID as codg_chave_acesso_bpe,
B.CPERCURSO as codg_percurso_viagem,
B.XPERCURSO as desc_percurso_viagem,
COALESCE(CAST(B.TPVIAGEM AS INTEGER),0) AS tipo_viagem,
COALESCE(CAST(B.TPSERV AS INTEGER),0) AS tipo_servico,
COALESCE(CAST(B.TPTRECHO AS INTEGER),0) AS tipo_trecho,
COALESCE(CAST(REPLACE(SUBSTR(B.DHVIAGEM,12,8),':','') AS INTEGER),0) AS hora_referencia_viagem,
COALESCE(CAST(REPLACE(SUBSTR(B.DHCONEXAO,12,8),':','') AS INTEGER),0) AS hora_conexao,
B.PREFIXO as nome_prefixo_linha,
COALESCE(CAST(B.POLTRONA AS INTEGER),0) AS numr_poltrona,
COALESCE(CAST(B.INFTRAVESSIA_TPVEICULO AS INTEGER),0) AS tipo_veiculo_transpdo,
COALESCE(CAST(REPLACE(SUBSTR(B.DHVIAGEM,1,10),'-','') AS INTEGER),0) AS numr_ano_mes_dia_referencia,
COALESCE(CAST(REPLACE(SUBSTR(B.DHCONEXAO,1,10),'-','') AS INTEGER),0) AS numr_ano_mes_dia_conexao,
347 as id_identidade_universo
FROM BPE.BPE_INFBPE A
INNER JOIN BPE.BPE_INFBPE_INFVIAGEM B ON B.ID = A.ID AND B.IDE_DHEMI_NUMS = A.IDE_DHEMI_NUMS
INNER JOIN BPE.BPE_INFBPE_PAG C ON C.CHBPE = A.ID AND C.IDE_DHEMI_NUMS = A.IDE_DHEMI_NUMS
where A.data_carga between unix_timestamp('2023-07-01') and unix_timestamp('2023-07-07 23:59:59');
--WHERE a.id = '52220960829264001237630010001621051845313199'
select
codg_chave_acesso_bpe,
valr_bilhete_passagem,
valr_desconto_bpe,
valr_pago_bpe,
valr_bc_icms_bpe,
valr_aliquota_icms_bpe,
valr_icms_bpe,
valr_perc_reducao_bc_icms_bpe,
valr_credito_outorg_presu_bpe,
valr_total_tributo_bpe,
valr_bc_icms_uf_final,
valr_perc_icms_fcp_uf_fim,
valr_aliq_interna_uf_fim_viage,
valr_aliq_interest_uf,
valr_icms_fcp_uf_fim_viagem,
valr_icms_partlh_uf_ini_viagem,
valr_icms_partlh_uf_fim_viagem,
codg_tipo_desconto,
numr_cpf_comprador_bilhete,
numr_cnpj_comprador_bilhete,
numr_cnpj_agencia_prep_terc,
COALESCE(numr_inscricao_emitente,'0') as numr_inscricao_emitente,
numr_inscricao_substituto_trib,
numr_inscricao_comprador,
codg_modelo_bilhete_passagem,
numr_ano_mes_dia_embarque,
codg_municipio_origem_viagem,
numr_ano_mes_dia_emissao,
numr_bilhete_passagem,
codg_uf,
codg_municipio_destino_viagem,
numr_serie_documento_fiscal,
346 as id_identidade_universo,
SUM(valr_tarifa) As valr_tarifa,
SUM(valr_pedagio) As valr_pedagio,
SUM(valr_taxa_embarque) As valr_taxa_embarque,
SUM(valr_seguro) As valr_seguro,
SUM(valr_taxa_manut_rodov) As valr_taxa_manut_rodov,
SUM(valr_serv_venda_intgda) As valr_serv_venda_intgda,
SUM(valr_outros) As valr_outros
From (
SELECT
A.ID as codg_chave_acesso_bpe,
CAST(LPAD(A.INFVALORBPE_VBP,16,'0') AS DECIMAL(15,2)) AS valr_bilhete_passagem,
CAST(LPAD(A.INFVALORBPE_VDESCONTO,16,'0') AS DECIMAL(15,2)) AS valr_desconto_bpe,
CAST(LPAD(A.INFVALORBPE_VPGTO,16,'0') AS DECIMAL(15,2)) AS valr_pago_bpe,
CAST(LPAD(A.IMP_ICMS_ICMS00_VBC,16,'0') AS DECIMAL(15,2)) AS valr_bc_icms_bpe,
CAST(LPAD(A.IMP_ICMS_ICMS00_PICMS,16,'0') AS DECIMAL(15,2)) AS valr_aliquota_icms_bpe,
CAST(LPAD(A.IMP_ICMS_ICMS00_VICMS,16,'0') AS DECIMAL(15,2)) AS valr_icms_bpe,
CAST(LPAD(A.IMP_ICMS_ICMS90_PREDBC,16,'0') AS DECIMAL(15,2)) AS valr_perc_reducao_bc_icms_bpe,
CAST(LPAD(A.IMP_ICMS_ICMS90_VCRED,16,'0') AS DECIMAL(15,2)) AS valr_credito_outorg_presu_bpe,
CAST(LPAD(A.IMP_VTOTTRIB,16,'0') AS DECIMAL(15,2)) AS valr_total_tributo_bpe,
CAST(LPAD(A.IMP_ICMSUFFIM_VBCUFFIM,16,'0') AS DECIMAL(15,2)) AS valr_bc_icms_uf_final,
CAST(LPAD(A.IMP_ICMSUFFIM_PFCPUFFIM,16,'0') AS DECIMAL(15,2)) AS valr_perc_icms_fcp_uf_fim,
CAST(LPAD(A.IMP_ICMSUFFIM_PICMSUFFIM,16,'0') AS DECIMAL(15,2)) AS valr_aliq_interna_uf_fim_viage,
CAST(LPAD(A.IMP_ICMSUFFIM_PICMSINTER,16,'0') AS DECIMAL(15,2)) AS valr_aliq_interest_uf,
CAST(LPAD(A.IMP_ICMSUFFIM_VFCPUFFIM,16,'0') AS DECIMAL(15,2)) AS valr_icms_fcp_uf_fim_viagem,
CAST(LPAD(A.IMP_ICMSUFFIM_VICMSUFINI,16,'0') AS DECIMAL(15,2)) AS valr_icms_partlh_uf_ini_viagem,
CAST(LPAD(A.IMP_ICMSUFFIM_VICMSUFFIM,16,'0') AS DECIMAL(15,2)) AS valr_icms_partlh_uf_fim_viagem,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 1 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_TARIFA,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 2 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_PEDAGIO,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 3 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_TAXA_EMBARQUE,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 4 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_SEGURO,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 5 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_TAXA_MANUT_RODOV,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 6 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_SERV_VENDA_INTGDA,
Case when CAST(LPAD(C.TPCOMP,2,'0') AS BIGINT) = 99 Then CAST(LPAD(C.VCOMP,16,'0') AS DECIMAL(15,2)) Else 0 End AS VALR_OUTROS,
CAST(LPAD(A.INFVALORBPE_TPDESCONTO,2,'0') AS BIGINT) AS codg_tipo_desconto,
CAST(LPAD(A.COMP_CPF,11,'0') AS BIGINT) AS numr_cpf_comprador_bilhete,
CAST(LPAD(A.COMP_CNPJ,14,'0') AS BIGINT) AS numr_cnpj_comprador_bilhete,
CAST(LPAD(A.AGENCIA_CNPJ,14,'0') AS BIGINT) AS numr_cnpj_agencia_prep_terc,
LPAD(A.EMIT_IE,9,'9') AS numr_inscricao_emitente,
LPAD(A.EMIT_IEST,9,'9') AS numr_inscricao_substituto_trib,
LPAD(A.COMP_IE,9,'9') AS numr_inscricao_comprador,
CAST(LPAD(A.IDE_MOD,2,'0') AS INTEGER) AS codg_modelo_bilhete_passagem,
COALESCE(CAST(REPLACE(SUBSTR(A.INFPASSAGEM_DHEMB,1,10),'-','') AS BIGINT)) AS numr_ano_mes_dia_embarque,
CAST(LPAD(A.IDE_CMUNINI,10,'0') AS BIGINT) AS codg_municipio_origem_viagem,
COALESCE(CAST(REPLACE(SUBSTR(A.IDE_DHEMI,1,10),'-','') AS BIGINT)) AS numr_ano_mes_dia_emissao,
CAST(LPAD(A.IDE_NBP,9,'0') AS BIGINT) AS numr_bilhete_passagem,
CAST(LPAD(A.IDE_CUF,2,'0') AS BIGINT) AS codg_uf,
CAST(LPAD(A.IDE_CMUNFIM,10,'0') AS BIGINT) AS codg_municipio_destino_viagem,
CAST(LPAD(A.IDE_SERIE,3,'0') AS INTEGER) AS numr_serie_documento_fiscal
FROM BPE.BPE_INFBPE A
INNER JOIN BPE.BPE_INFBPE_INFVALORBPE_COMP C ON C.CHBPE = A.ID AND C.IDE_DHEMI_NUMS = A.IDE_DHEMI_NUMS
where A.data_carga between unix_timestamp('2023-07-01') and unix_timestamp('2023-07-07 23:59:59') ----- DATA YYYY-MM-DD
) as a
Group By
codg_chave_acesso_bpe,
valr_bilhete_passagem,
valr_desconto_bpe,
valr_pago_bpe,
valr_bc_icms_bpe,
valr_aliquota_icms_bpe,
valr_icms_bpe,
valr_perc_reducao_bc_icms_bpe,
valr_credito_outorg_presu_bpe,
valr_total_tributo_bpe,
valr_bc_icms_uf_final,
valr_perc_icms_fcp_uf_fim,
valr_aliq_interna_uf_fim_viage,
valr_aliq_interest_uf,
valr_icms_fcp_uf_fim_viagem,
valr_icms_partlh_uf_ini_viagem,
valr_icms_partlh_uf_fim_viagem,
codg_tipo_desconto,
numr_cpf_comprador_bilhete,
numr_cnpj_comprador_bilhete,
numr_cnpj_agencia_prep_terc,
numr_inscricao_emitente,
numr_inscricao_substituto_trib,
numr_inscricao_comprador,
codg_modelo_bilhete_passagem,
numr_ano_mes_dia_embarque,
codg_municipio_origem_viagem,
numr_ano_mes_dia_emissao,
numr_bilhete_passagem,
codg_uf,
codg_municipio_destino_viagem,
numr_serie_documento_fiscal,
id_identidade_universo;
SELECT DISTINCT
A.ID as codg_chave_acesso_bpe,
CAST(LPAD(A.IDE_MOD,2,'0') AS INTEGER) AS codg_modelo_bilhete_passagem,
CAST(LPAD(A.IDE_SERIE,3,'0') AS INTEGER) AS numr_serie_documento_fiscal,
CAST(LPAD(A.IDE_NBP,9,'0') AS INTEGER) AS numr_bilhete_passagem,
CAST(LPAD(A.ide_cdv,2,'0') AS INTEGER) AS numr_dig_verif_chave_acesso,
CAST(LPAD(A.IDE_MODAL,1,'0') AS INTEGER) AS codg_modalidade_tranp,
COALESCE(CAST(REPLACE(SUBSTR(A.IDE_DHEMI,12,8),':','') AS INTEGER),0) AS hora_emissao_bilhete,
CAST(LPAD(A.TPEMIS,1,'0') AS INTEGER) AS codg_forma_emissao_bilhete,
CAST(LPAD(A.IDE_TPBPE,1,'0') AS INTEGER) AS tipo_bpe,
CAST(LPAD(A.IDE_INDPRES,1,'0') AS INTEGER) AS codg_compra_presencial,
A.INFBPESUB_CHBPE as codg_chave_bpe_substiuido,
CAST(LPAD(A.INFBPESUB_TPSUB,1,'0') AS INTEGER) AS tipo_subst,
A.IMP_INFADFISCO as info_adicional_fisco,
A.INFADIC_INFCPL as info_adicional_contrib,
A.INFPASSAGEM_CLOCORIG as codg_localidade_origem,
A.INFPASSAGEM_XLOCORIG as desc_localidado_origem,
A.INFPASSAGEM_CLOCDEST as codg_localidade_destino,
A.INFPASSAGEM_XLOCDEST as desc_localidade_destino,
COALESCE(CAST(REPLACE(SUBSTR(A.INFPASSAGEM_DHEMB,12,8),':','') AS INTEGER),0) AS hora_embarque,
COALESCE(CAST(REPLACE(SUBSTR(A.INFPASSAGEM_DHVALIDADE,12,8),':','') AS INTEGER),0) AS hora_validade_bpe,
CAST(LPAD(A.INFPASSAGEM_INFPASSAGEIRO_CPF,11,'0') AS BIGINT) AS numr_cpf_passageiro,
A.INFPASSAGEM_INFPASSAGEIRO_XNOME as nome_passageiro,
CAST(LPAD(A.INFRESPTEC_CNPJ,14,'0') AS BIGINT) AS numr_cnpj_resp_sistema,
COALESCE(CAST(REPLACE(SUBSTR(A.IDE_DHEMI,1,10),'-','') AS INTEGER),0) AS numr_ano_mes_dia_emissao,
COALESCE(CAST(REPLACE(SUBSTR(A.INFPASSAGEM_DHEMB,1,10),'-','') AS INTEGER),0) AS numr_ano_mes_dia_embarque,
CAST(LPAD(A.IDE_CMUNINI,10,'0') AS BIGINT) AS codg_municipio_origem_viagem,
CAST(LPAD(A.IDE_CMUNFIM,10,'0') AS BIGINT) AS codg_municipio_destino_viagem,
CAST(LPAD(A.IDE_CUF,2,'0') AS INTEGER) AS codg_uf,
COALESCE(CAST(REPLACE(SUBSTR(A.INFPASSAGEM_DHVALIDADE,1,10),'-','') AS INTEGER),0) AS numr_ano_mes_dia_validade
FROM BPE.BPE_INFBPE A
where A.data_carga between unix_timestamp(?) and unix_timestamp(?); ----- DATA YYYY-MM-DD
SELECT DISTINCT
COALESCE(CAST(COMP_CNPJ AS BIGINT),0) AS numr_cnpj_comprador_bilhete,
COALESCE(CAST(COMP_CPF AS BIGINT),0) AS numr_cpf_comprador_bilhete,
COMP_XNOME AS nome_razao_social_comrpador,
COMP_IE AS numr_inscricao_comprador,
COALESCE(CAST(COMP_IDESTRANGEIRO AS BIGINT),0) AS codg_comprador_estrang,
COALESCE(CAST(COMP_ENDERCOMP_CPAIS AS BIGINT),0) AS codg_pais_comprador_bilhete,
COMP_ENDERCOMP_XPAIS AS nome_pais_comprador_bilhete,
COALESCE(CAST(COMP_ENDERCOMP_CMUN AS BIGINT),0) AS codg_municipio
FROM BPE.BPE_INFBPE
where data_carga between unix_timestamp(?) and unix_timestamp(?) ----- DATA YYYY-MM-DD
AND COMP_XNOME <> '';
SELECT DISTINCT
A.EMIT_IE as numr_inscricao_emitente,
COALESCE(CAST(A.EMIT_CNPJ AS BIGINT),0) AS numr_cnpj_emitente_bilhete,
A.EMIT_XNOME as nome_razao_social_emitente,
A.EMIT_XFANT as nome_fantasia_emitente,
A.EMIT_TAR as codg_tar_emitente_bilhete,
COALESCE(CAST(A.EMIT_CRT AS BIGINT),0) AS codg_regime_tributario_emit,
COALESCE(CAST(A.EMIT_IEST AS BIGINT),0) AS numr_inscricao_substituto_trib,
COALESCE(CAST(A.IMP_ICMS_ICMSSN_INDSN AS BIGINT),0) AS indi_contrib_simples_emitente,
A.IMP_INFADFISCO as info_adicional_fisco_emitente,
COALESCE(CAST(A.EMIT_ENDEREMIT_CMUN AS BIGINT),0) AS codg_municipio
FROM BPE.BPE_INFBPE A
where A.data_carga between unix_timestamp(?) and unix_timestamp(?) ----- DATA YYYY-MM-DD
SELECT DISTINCT
COALESCE(CAST(A.AGENCIA_CNPJ AS BIGINT),0) AS numr_cnpj_agencia_prep_terc,
coalesce(A.AGENCIA_XNOME,'NAO INFORMADO') as nome_razao_social_agencia,
COALESCE(CAST(A.AGENCIA_ENDERAGENCIA_CPAIS AS BIGINT),0) AS codg_pais_agencia_prep_terc,
coalesce(a.AGENCIA_ENDERAGENCIA_XPAIS,'NAO INFORMADO') as nome_pais_agencia_prep_terc,
COALESCE(CAST(A.AGENCIA_ENDERAGENCIA_CMUN AS BIGINT),0) AS cmun_agencia_prep_terc
FROM BPE.BPE_INFBPE A
where A.data_carga between unix_timestamp(?) and unix_timestamp(?) ----- DATA YYYY-MM-DD
;