Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Galera salve salve a todos,
Estou com um problema em um select e preciso da ajuda de vocês.
O select se refere a calculo de faturamentos os quais calculam os itens cobrados na NF.
Eu preciso gerar um relatório destes lançamentos e com isso criei o select abaixo.
select bo.lager,
bo.id_os,
k.id_klient,
k.suchbegriff cnpj_cpf,
k.name,
bi.div_1 lote,
bi.div_10 di_dde,
(select distinct dis.id_dispatcher
from fiscal.dochd dc, wms_eadi.dispatcher dis
where dc.dochd_doc_id = bo.nr_nf
and dc.dochd_rps_desp = dis.suchbegriff) cod_comissaria,
(select distinct dis.bez
from fiscal.dochd dc, wms_eadi.dispatcher dis
where dc.dochd_doc_id = bo.nr_nf
and dc.dochd_rps_desp = dis.suchbegriff) comissaria,
bo.nr_nf,
bo.date_bill,
bo.date_emiss,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'ARMAZ')) vlr_armaz,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'GER.RISCO')) vlr_ger_risco,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'MOVIMENT')) vlr_movimentacao,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'DESUNIT')) vlr_desunit,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'PESAGEM')) vlr_pesagem,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'AVERBAÇÃO')) vlr_averbacao,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and wer.wert = 'FAT.MINIMO.')) vlr_fat_minimo,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and bll.billite not in ('21.02', '21.03')
and wer.wert = 'OUT.SERV.')) vlr_outros_serv,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and bll.billite = '21.02'
and wer.wert = 'OUT.SERV.')) transf_in,
(select sum(round(osi.preis, 2))
from bill_os_item osi
where osi.lager = bo.lager
and osi.id_klient = k.id_klient
and osi.id_os = bo.id_os
and osi.billite = bi.billite
and osi.div_1 = bi.div_1
and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
and osi.billite in
(select bll.billite
from bill_item bll, werte wer
where bll.tipo_fat = wer.wert
and wer.werte_ber = 'ITENSF'
and bll.billite = '21.03'
and wer.wert = 'OUT.SERV.')) transf_out
from bill_os bo, bill_os_item bi, klienten k
where bo.lager = bi.lager
and bo.id_klient = bi.id_klient
and bo.id_os = bi.id_os
and bo.lager = k.lager
and bo.id_klient = k.id_klient
and bo.status = '90'
and k.id_klient = '2464'
--and trunc(bo.date_bill) between '$inicio' and '$fim'
group by bo.lager,
bo.id_os,
k.id_klient,
k.suchbegriff,
k.name,
bo.date_emiss,
bo.nr_nf,
bo.date_bill,
bi.billite,
bi.div_1,
bi.div_10
Ele me traz os resultados por linhas de cada tipo de cobrança.
Segue imagem do resultado.
Eu preciso que os resultados viessem em apenas uma linha, estou quebrando a cabeça com isso.
o resultado tem que ser assim:
Carregando comentários...