I can run this query from toad sql server.
from my application in
java, it generated error as the statement did not return resultset. i tried many things , but i could not solve it. Thank you for your help.
result from toad sql server is like below.
Unvan |Ulkesi |VergiKimlikNo| TCKimlikNo| BelgeSayisi|Toplam| CariKod
customer a | 052 | 19697583840 | 1 | 2323,00 | HT00084
customer b | 052 | | 2 | 2111,00 | HT01022
stored procedure is like below (partial of sp , it is long )
and this is the error information
and this is full stored procedure (below)
/****** Object: Procedure [dbo].[msp_FormBsBa_yeni] Script Date: 18.04.2015 13:43:44 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.msp_FormBsBa_yeni
@vFirmaNo as integer,
@IlkTarih as datetime,
@SonTarih as datetime,
@MinTutar as float,
@Bs_Ba_tip as bit,
@BirlestimeTuru as tinyint,
@SonradanMuhasebelesenSeriDahilEdilmesin_fl as bit,
@SonradanMuhasebelesenSeriStr nvarchar(MAX),
@Aylik_BsBa_fl as bit,
@PerakendeCariKodu AS nvarchar(25),
@EvrakDetayliRapor_fl as bit
AS
BEGIN /*dbo.fn_GetByteParam(1048)=1 ÖTV stok maliyetine eklensinmi*/
Declare @otv_vergino as tinyint
set @otv_vergino = dbo.fn_GetByteParam(855) /*ÖTV vergi tipi*/
Declare @otv_kdv_orani as FLOAT
set @otv_kdv_orani = 0.0
if @otv_vergino between 1 and 10
set @otv_kdv_orani = dbo.fn_VergiYuzde(@otv_vergino)
Declare @kontrol_belge_tarihinden as integer
set @kontrol_belge_tarihinden = dbo.fn_GetByteParam(4173) /*BsBa_Kontrol_Belge_tarihinden_fl*/
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaEvrakDetayliTablo%') Drop Table dbo.#BsBaEvrakDetayliTablo
select
TABLONO,
MIN(CHRECNO) AS CHRECNO,
CARI,
MAX(TARIH) AS TARIH,
TIP,
SERI,
SIRA,
MAX(CINS)AS CINS,
MAX(BELNO)AS BELNO,
MAX(BELTAR)AS BELTAR,
sum(BS_BA_TUTAR) AS BS_BA_TUTAR,
cast(1 as integer) AS BELGE_SAYISI
INTO dbo.#BsBaEvrakDetayliTablo
from
(SELECT
cast (51 as integer) AS TABLONO,
cha_RECno AS CHRECNO,
case when ISNULL(cha_tpoz,0)=0 then ISNULL(cha_kod,'') else ISNULL(cha_ciro_cari_kodu,'') end as CARI,
cha_tarihi AS TARIH,
cha_evrak_tip as TIP,
cha_evrakno_seri AS SERI,
cha_evrakno_sira AS SIRA,
cha_satir_no AS SATIR,
cha_cinsi AS CINS,
cha_belge_no AS BELNO,
cha_belge_tarih AS BELTAR,
case
when cha_evrak_tip=61 /*gider pusulası*/
then ISNULL(cha_aratoplam,0.0) * ISNULL(cha_d_kur,1.0)
when (NOT cha_cinsi IN (12,24,35))AND /*fason,hal,kabzımal faturaları aşağıda okunsun*/
(NOT (cha_evrak_tip=0 and dbo.fn_malfaturasimi (cha_cinsi,cha_kasa_hizmet)=1 and cha_cinsi=29))AND /*ithalat faturaları aşağıda okunsun*/
((dbo.fn_malfaturasimi (cha_cinsi,cha_kasa_hizmet)=0)OR (ISNULL(cha_otvtutari,0.0)=0.0)) /*ÖTV li stok faturaları aşağıda okunsun*/
then
(ISNULL(cha_meblag,0.0) -
(ISNULL(cha_vergi1,0.0)+ISNULL(cha_vergi2,0.0)+ISNULL(cha_vergi3,0.0)+ISNULL(cha_vergi4,0.0)+ISNULL(cha_vergi5,0.0)+
ISNULL(cha_vergi6,0.0)+ISNULL(cha_vergi7,0.0)+ISNULL(cha_vergi8,0.0)+ISNULL(cha_vergi9,0.0)+ISNULL(cha_vergi10,0.0)+
ISNULL(cha_HalHamaliyeKdv,0.0)+ISNULL(cha_HalKomisyonu,0.0)+ISNULL(cha_HalRehinFuture/*navlun kdvsi*/,0.0)+ISNULL(cha_HalSandikKDVTutari,0.0))+ /*bunlar kabzımal faturasındaki masraflara ait vergiler*/
(ISNULL(cha_stopaj,0.0) + ISNULL(cha_savsandesfonu,0.0) + ISNULL(cha_mustahsil_borsa,0.0) + ISNULL(cha_mustahsil_bagkur,0.0) + ISNULL(cha_mustahsil_diger,0.0)/* - cha_yuvarlama*/)-
(ISNULL(cha_otvtutari,0.0) * case when cha_cinsi=14 and @otv_kdv_orani>0.0 then 0.0 else 1.0 end)- /*demirbaş için ÖTV vergi konusu olmuş ise matrahtan düşsün*/
-- (ISNULL(cha_oivtutari,0.0) * case when @Bs_Ba_tip=1 then 0.0 else 1.0 end)+ /*ÖİV Bs de matrahtan düşsün, Ba da eklensin*/
(ISNULL(cha_oivtutari,0.0))+ /*ÖİV daima matrahtan düşsün*/
ISNULL(dbo.fn_ToplamTevkifat(cha_RECno),0.0))*ISNULL(cha_d_kur,1.0)
when @Bs_Ba_tip=0 AND cha_evrak_tip=0 AND cha_cinsi=24 -- bs için alis_faturasi,Hal_fat içindeki komisyon
then ISNULL(cha_HalKomisyon,0.0) * ISNULL(cha_d_kur,1.0)
else
( SELECT SUM(((ISNULL(sth_tutar,0.0) * case when sth_cins=8 AND sth_tip=0 AND sth_normal_iade=0 then 0.0 else 1.0 end)- /*Fasondan malzeme maliyetini düşelim*/
(ISNULL(sth_iskonto1,0.0)+ISNULL(sth_iskonto2,0.0)+ISNULL(sth_iskonto3,0.0)+ISNULL(sth_iskonto4,0.0)+ISNULL(sth_iskonto5,0.0)+ISNULL(sth_iskonto6,0.0))+
(ISNULL(sth_masraf1,0.0)+ISNULL(sth_masraf2,0.0)+ISNULL(sth_masraf3,0.0)+ISNULL(sth_masraf4,0.0))+
(ISNULL(sth_otvtutari,0.0) * case when ISNULL(sth_otv_vergi,0.0) > 0.0 then 1.0 else 0.0 end)+ /*ÖTV vergi konusu olmuş ise matraha eklensin*/
-- (ISNULL(sth_oivtutari,0.0) * case when @Bs_Ba_tip=1 then 1.0 else 0.0 end)+ /*ÖİV Ba da matraha eklensin, Bs de eklenmesin*/
(ISNULL(sth_halrehfiyati,0.0) * ISNULL(sth_halrehmiktari,0.0) * case when sth_cins in(13,16) then 1.0 else 0.0 end)+
(ISNULL(sth_halsandikfiyati,0.0) * ISNULL(sth_halsandikmiktari,0.0) * case when sth_cins in(13,16) then 1.0 else 0.0 end)
)* ISNULL(sth_har_doviz_kuru,1.0))+
(case when cha_cinsi=24 and ISNULL(MAX(sth_RECno),0)>0 /*aşağıdaki where e uyan kayıt varsa bu değeleri toplasın*/
then ((ISNULL(cha_HalHamaliye,0.0)+ISNULL(cha_HalStopaj,0.0)+ISNULL(cha_HalKomisyonu,0.0)+ISNULL(cha_HalRusum,0.0)+
ISNULL(cha_HalNavlunTut,0.0)+ISNULL(cha_mustahsil_bagkur,0.0))*ISNULL(cha_d_kur,1.0))
else 0.0 end)
FROM dbo.STOK_HAREKETLERI With (NOLOCK, INDEX=NDX_STOK_HAREKETLERI_07)
LEFT OUTER JOIN dbo.DEPOLAR on (dep_no=sth_giris_depo_no)
WHERE (sth_fat_recid_dbcno = cha_RECid_DBCno) AND
(sth_fat_recid_recno = cha_RECid_RECno) AND
((cha_evrak_tip=0 and cha_cinsi=29 and dep_tipi<>5) OR /*ithalat girişi antrepo depoya giriş değilse faturanın kendisini okuyalım. Antrepo ise aşağıda antrepodan millileştirmeleri okuyalım*/
((not (cha_evrak_tip=0 and cha_cinsi=29))and
( (cha_cinsi<>24)OR
((@Bs_Ba_tip=0 AND cha_evrak_tip=63 AND ISNULL(sth_karsikons_recid_recno,0)=0)OR -- karşı konsinye olarak satılanlar, çiftçinin satışı olduğundan firmanın kendi satışı olarak değerlendirilmiyor.
(@Bs_Ba_tip=1 AND cha_evrak_tip=0 AND ISNULL(sth_HalSatisRecid_recno,0)=0)) -- çiftçinin satışlarından oluşturulan alış faturası ise, firmanın kendi alışı olarak değerlendirilmiyor.
)
)
)
)
end AS BS_BA_TUTAR
FROM dbo.CARI_HESAP_HAREKETLERI WITH (NOLOCK)
WHERE (cha_firmano=@vFirmaNo) AND
(
((@kontrol_belge_tarihinden=0) and (cha_tarihi between @IlkTarih AND @SonTarih)) or
((@kontrol_belge_tarihinden=1) and (cha_belge_tarih between @IlkTarih AND @SonTarih))
)AND
((cha_tpoz=0 AND cha_cari_cins=0)or(cha_tpoz=1 AND cha_ciro_cari_kodu<>''))AND
((@Bs_Ba_tip=0 and (cha_evrak_tip in (63,88) or (cha_evrak_tip = 0 and cha_cinsi=24 and ISNULL(cha_HalKomisyon,0.0) > 0.0)))or-- satis_faturasi,Satis_serbest_meslek_makbuzu , alis_faturasi,Hal_fat
(@Bs_Ba_tip=1 and cha_evrak_tip in (0,55,56,61))) AND--alis_faturasi,Giris_gider_makbuzu,Giris_serbest_meslek_makbuzu,Stok_gider_pusulasi
(@SonradanMuhasebelesenSeriDahilEdilmesin_fl=0 or dbo.fn_str_ok(cha_evrakno_seri,@SonradanMuhasebelesenSeriStr)=0)
UNION
SELECT
cast (16 as integer) AS TABLONO,
sth_RECno*-1 AS CHRECNO,
ISNULL(ith_satici,'') as CARI,
sth_tarih AS TARIH,
sth_evraktip as TIP,
sth_evrakno_seri AS SERI,
sth_evrakno_sira AS SIRA,
sth_satirno AS SATIR,
sth_cins AS CINS,
sth_belge_no AS BELNO,
sth_belge_tarih AS BELTAR,
ISNULL(sth_tutar,0.0) * ISNULL(sth_har_doviz_kuru,1.0) AS BS_BA_TUTAR
FROM dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX=NDX_STOK_HAREKETLERI_05)
LEFT OUTER JOIN dbo.ITHALAT_DOSYALARI on (ith_kodu=sth_exim_kodu)
LEFT OUTER JOIN dbo.DEPOLAR on (dep_no=sth_cikis_depo_no)
WHERE (@Bs_Ba_tip=1) AND
(sth_evraktip=10) AND /*Ba için antrepodan mal millileştirme*/
(sth_firmano=@vFirmaNo) AND
(
((@kontrol_belge_tarihinden=0) and (sth_tarih between @IlkTarih AND @SonTarih)) or
((@kontrol_belge_tarihinden=1) and (sth_belge_tarih between @IlkTarih AND @SonTarih))
)AND
(dep_tipi=5) AND /*antrepodan kendi depomuza aldığımız mal ise*/
(@SonradanMuhasebelesenSeriDahilEdilmesin_fl=0 or dbo.fn_str_ok(sth_evrakno_seri,@SonradanMuhasebelesenSeriStr)=0)
)as DETAYSIZ
WHERE ISNULL(BS_BA_TUTAR,0.0) >= 0.0
group by CARI,TABLONO,TIP,SERI,SIRA
IF @EvrakDetayliRapor_fl=1
BEGIN
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaEvraklar%') Drop Table dbo.#BsBaEvraklar
select
case when @BirlestimeTuru =2 then [msg_S_1259]+' - '+[msg_S_1949]
when @BirlestimeTuru =1 then [msg_S_1259]+' - '+[msg_S_1949]+' - '+ [msg_S_0905]
else [msg_S_0200]
end as [msg_S_3103], /*TC.KİM. - VER.KİM. - ÜLKE - GENEL CARİ İSMİ*/
*
into dbo.#BsBaEvraklar
from(
select
CARI AS [msg_S_0200], /*CARİ KODU*/
case when cari_unvan2<>'' then substring(cari_unvan1+' '+cari_unvan2,1,60) else cari_unvan1 end AS [msg_S_0201],/*CARİ İSMİ*/
case when len(REPLACE(cari_vdaire_no,' ','')) < 11 then '' else REPLACE(cari_vdaire_no,' ','') end AS [msg_S_1259] /* TC KIMLIK NO */,
case when len(REPLACE(cari_vdaire_no,' ','')) < 11 then REPLACE(cari_vdaire_no,' ','') else REPLACE(cari_VergiKimlikNo,' ','') end AS [msg_S_1949] /*VERGİ KİMLİK NO */,
dbo.fn_FormBsUlkeKodu(cari_kod,cari_fatura_adres_no) AS [msg_S_0905] /* ÜLKE KODU */,
TARIH AS [msg_S_0089], -- TARİH
CASE WHEN TABLONO=51 THEN dbo.fn_CariHarEvrTipUzun(TIP) ELSE dbo.fn_StokHarEvrTip(TIP) END AS [msg_S_0094], -- EVRAK TİPİ,
SERI AS [msg_S_0090], -- SERİ
SIRA AS [msg_S_0091], -- SIRA
CASE WHEN TABLONO=51 THEN dbo.fn_CariHarCins(CINS) ELSE dbo.fn_StokHarCins(CINS) END AS [msg_S_0003], -- CİNSİ
BELNO AS [msg_S_0093], -- BELGE NO
BELTAR AS [msg_S_0092], -- BELGE TARİHİ
BS_BA_TUTAR AS [msg_S_1951] /* BS-BA YA KONU TUTAR */,
CHRECNO AS [#msg_S_0777] /* PKAYIT NO */
from dbo.#BsBaEvrakDetayliTablo
LEFT OUTER JOIN dbo.CARI_HESAPLAR ON cari_kod=CARI
)as HT
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaToplamlar%') Drop Table dbo.#BsBaToplamlar
SELECT [msg_S_3103]AS TVU,MAX([msg_S_0201])AS UNVAN
INTO dbo.#BsBaToplamlar
FROM dbo.#BsBaEvraklar
GROUP BY [msg_S_3103]
HAVING sum([msg_S_1951]) >= @MinTutar
select
[#msg_S_0777] as [#msg_S_0088],
[msg_S_3103]+ case when @BirlestimeTuru > 0 then ' - '+ UNVAN else '' end as [msg_S_3103\G], /*TC.KİM. - VER.KİM. - ÜLKE - GENEL CARİ İSMİ*/
[msg_S_0200], /*CARİ KODU*/
[msg_S_0201], /*CARİ İSMİ*/
[msg_S_1259], /* TC KIMLIK NO */
[msg_S_1949], /*VERGİ KİMLİK NO */
[msg_S_0905], /* ÜLKE KODU */
[msg_S_0089], /* TARİH */
[msg_S_0094] AS [msg_S_0094\S], /* EVRAK TİPİ*/
[msg_S_0090], /* SERİ */
[msg_S_0091], /* SIRA */
[msg_S_0003], /* CİNSİ */
[msg_S_0093], /* BELGE NO */
[msg_S_0092], /* BELGE TARİHİ */
[msg_S_1951] AS [msg_S_1951\T] /* BS-BA YA KONU TUTAR */
from dbo.#BsBaEvraklar
LEFT OUTER JOIN dbo.#BsBaToplamlar ON TVU=[msg_S_3103]
WHERE ISNULL(TVU,'')<>''
ORDER BY [msg_S_3103\G]
END
ELSE
BEGIN
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaTablosu%') Drop Table dbo.#BsBaTablosu
select
*,
cari_RECno AS RECNO,
case when cari_unvan2<>'' then substring(cari_unvan1+' '+cari_unvan2,1,60) else cari_unvan1 end AS UNVAN,
REPLACE(cari_vdaire_no,' ','') as TCNO,
REPLACE(cari_VergiKimlikNo,' ','') as VKNO,
dbo.fn_FormBsUlkeKodu(cari_kod,cari_fatura_adres_no) as ULKE
into dbo.#BsBaTablosu
from
( select
CARI AS CARI,
sum(BS_BA_TUTAR) AS BS_BA_TUTAR,
sum(BELGE_SAYISI)AS BELGE_SAYISI,
CASE WHEN (@PerakendeCariKodu<>'' AND CARI like @PerakendeCariKodu)THEN 1 ELSE 0 END AS PERAKENDE
from dbo.#BsBaEvrakDetayliTablo
group by CARI
)as CARIGRUPLU
LEFT OUTER JOIN dbo.CARI_HESAPLAR ON cari_kod=CARI
/*Vergi numarası olmayan kayıtları birleştirmeyelim*/
DECLARE @tanimsizno as nvarchar(5)
set @tanimsizno = '#@#'
UPDATE dbo.#BsBaTablosu SET TCNO = @tanimsizno+CONVERT(NVARCHAR(12),RECNO) WHERE TCNO=''
UPDATE dbo.#BsBaTablosu SET VKNO = @tanimsizno+SUBSTRING(CONVERT(NVARCHAR(12),RECNO),1,7) WHERE VKNO=''
if exists (select * from tempdb..sysobjects where name LIKE '#FormBsBa%') Drop Table dbo.#FormBsBa
CREATE Table dbo.#FormBsBa
(Unvan nvarchar(60) COLLATE database_default,
Ulkesi nvarchar(10) COLLATE database_default,
VergiKimlikNo nvarchar(10) COLLATE database_default,
TCKimlikNo nvarchar(15) COLLATE database_default,
BelgeSayisi integer,
Toplam float,
CariKod nvarchar(25) COLLATE database_default)
if (@BirlestimeTuru =2)
INSERT INTO dbo.#FormBsBa SELECT MAX(UNVAN),MAX(ULKE),MAX(VKNO),TCNO,SUM(BELGE_SAYISI),sum(BS_BA_TUTAR),MAX(CARI)
FROM dbo.#BsBaTablosu
WHERE PERAKENDE=0
GROUP BY TCNO
ELSE
if (@BirlestimeTuru =1)
INSERT INTO dbo.#FormBsBa SELECT MAX(UNVAN),ULKE,MAX(VKNO),TCNO,SUM(BELGE_SAYISI),sum(BS_BA_TUTAR),MAX(CARI)
FROM dbo.#BsBaTablosu
WHERE PERAKENDE=0
GROUP BY TCNO,ULKE
ELSE
INSERT INTO dbo.#FormBsBa SELECT MAX(UNVAN),ULKE,MAX(VKNO),TCNO,SUM(BELGE_SAYISI),sum(BS_BA_TUTAR),CARI
FROM dbo.#BsBaTablosu
WHERE PERAKENDE=0
GROUP BY CARI,TCNO,ULKE
UPDATE dbo.#FormBsBa SET TCKimlikNo = '' WHERE TCKimlikNo LIKE @tanimsizno+'%'
UPDATE dbo.#FormBsBa SET VergiKimlikNo = '' WHERE VergiKimlikNo LIKE @tanimsizno+'%'
UPDATE dbo.#FormBsBa SET VergiKimlikNo = TCKimlikNo WHERE LEN(TCKimlikNo)<11
UPDATE dbo.#FormBsBa SET TCKimlikNo = '' WHERE LEN(TCKimlikNo)<11
IF @Bs_Ba_tip = 0
UPDATE dbo.#FormBsBa SET VergiKimlikNo = '2222222222',TCKimlikNo = '22222222222' WHERE (ISNUMERIC(Ulkesi)=1)AND (CAST(Ulkesi as integer) <>52)
ELSE
UPDATE dbo.#FormBsBa SET VergiKimlikNo = '1111111111',TCKimlikNo = '11111111111' WHERE (ISNUMERIC(Ulkesi)=1)AND (CAST(Ulkesi as integer) <>52)
IF @Aylik_BsBa_fl=1
BEGIN
declare @perakendetoplam as float
select @perakendetoplam = sum(BS_BA_TUTAR) FROM dbo.#BsBaTablosu WHERE PERAKENDE=1
if isnull(@perakendetoplam,0.0) > 0.0
INSERT INTO dbo.#FormBsBa SELECT '---','---','---','---',0,@perakendetoplam,'---'
END
SELECT * FROM dbo.#FormBsBa
WHERE (Toplam>=@MinTutar)OR(TCKimlikNo='---')
ORDER BY VergiKimlikNo,TCKimlikNo,Unvan,Ulkesi
END
END
GO