Posiadam w XL BI domyślny raport Należności/Zobowiązania lecz bez pełnych danych kontrahenta. Jest wstanie ktoś podpowiedzieć jak poprawić go aby w BI wyświetlały się dodatkowe informacje takie jak:
Ulica – kod pocztowy – miasto – kraj Z KARTY KONTRAHENTA
Poniżej kod tego raportu. Z góry dziękuje za wszelkie odp.
Kod: Zaznacz cały
SET LANGUAGE Polish
IF OBJECT_ID(N'tempdb..#tmp_Regions',N'U') IS NOT NULL
DROP TABLE #tmp_Regions
IF OBJECT_ID(N'tempdb..#tmpKntGr',N'U') IS NOT NULL
DROP TABLE #tmpKntGr
------------------------------
-- DANE --
------------------------------
DECLARE @function varchar(max)
SET @function = ''
SET @function = @function + N'
CREATE FUNCTION [dbo].[NumerDokumentuFormatBI](
@Obszar INT,
@p_GidTyp VARCHAR(20),
@p_SpiTyp VARCHAR(20),
@p_TrnTyp VARCHAR(20),
@p_Numer VARCHAR(20),
@p_Rok VARCHAR(20),
@p_Seria VARCHAR(20),
@p_Miesiac VARCHAR(20),
@p_TypSkrot VARCHAR(20)
)
RETURNS VARCHAR(max) AS
BEGIN
DECLARE @formatDok VARCHAR(max)
DECLARE @gidTyp INT
DECLARE @Format VARCHAR(30)
DECLARE @I INT
DECLARE @Imax INT
DECLARE @Poprzedni VARCHAR(50)
DECLARE @formatOgolny VARCHAR(50)
DECLARE @sQl varchar(max)
SET @formatDok = '' ''
SET @SQL = '' ''
-- @Obszar = 1 - P3atnooci
-- @Obszar = 2 - ...
DECLARE Dokumenty CURSOR FOR
SELECT Ob_GidTyp FROM CDN.Obiekty WHERE
( @Obszar = 1 AND Ob_GidTyp in (784,4145,7691 ) )
OR
( @Obszar = 3 AND Ob_GidTyp in (434,2768,2832,4144,7680,7681,7684,7690,784) )
OPEN Dokumenty;
FETCH NEXT FROM Dokumenty INTO @gidTyp
IF @gidTyp IS NOT NULL SET @formatDok = ''CASE ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @formatDok = @formatDok + ''WHEN ''+@p_GidTyp+'' = '' + CAST(@gidTyp as varchar) + '' THEN ''
IF @gidTyp = 7691
SELECT @formatDok = @formatDok + ''( SELECT DPN_Symbol'' + ''+'' + ''''''/'''''' + ''right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) + ''''/'''' + cast(''+@p_rok+'' as varchar) + ''''/'''' + cast(''+@p_numer+'' as varchar)
FROM CDN.DefPodNag WHERE DPN_ID = ''+@p_SpiTyp+'')
''
ELSE IF @gidTyp = 2768
SET @formatDok = @formatDok +''''''VAT-7 '''''' + ''+'' + ''right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) + ''''/'''' + cast(''+@p_rok+'' as varchar)
''
ELSE IF @gidTyp = 7712
SET @formatDok = @formatDok +''''''VAT-7D '''''' + ''+'' + ''case ''+@p_miesiac+'' when 1 then ''''I/'''' + cast(''+@p_rok+'' as varchar)
when 2 then ''''II/'''' + cast(''+@p_rok+'' as varchar)
when 3 then ''''III/'''' + cast(''+@p_rok+'' as varchar)
when 4 then ''''IV/'''' + cast(''+@p_rok+'' as varchar) end
''
ELSE IF @gidTyp = 7696
SET @formatDok = @formatDok +''''''VAT-7K '''''' + ''+'' + ''case ''+@p_miesiac+'' when 1 then ''''I/'''' + cast(''+@p_rok+'' as varchar)
when 2 then ''''II/'''' + cast(''+@p_rok+'' as varchar)
when 3 then ''''III/'''' + cast(''+@p_rok+'' as varchar)
when 4 then ''''IV/'''' + cast(''+@p_rok+'' as varchar) end
''
ELSE IF @gidTyp = 6400 OR @gidTyp = 6401
SET @formatDok = @formatDok +''''''DRA '''''' + ''+'' + ''right(''''0''''+ ''+@p_seria+'',2) + ''''/'''' + right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) + ''''/'''' + cast(''+@p_rok+'' as varchar)
''
ELSE IF @gidTyp = 6272
SET @formatDok = @formatDok +''''''PIT-'''''' + ''+'' + ''CASE ''+@p_TrnTyp+'' WHEN 3 THEN ''''4'''' WHEN 6 THEN ''''8A'''' ELSE '''''''' END + '''' '''' + right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) + ''''/'''' + cast(''+@p_rok+'' as varchar)
''
ELSE IF @gidTyp = 2832
SET @formatDok = @formatDok + ''''''NO-'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),2) + ''''/'''' + rtrim(''+@p_seria+'') + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp = 2833
SET @formatDok = @formatDok +''''''UP-'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),2) + ''''/'''' + rtrim(''+@p_seria+'') + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + cast(''+@p_numer+'' as varchar)
''
ELSE
BEGIN
SELECT @formatDok = @formatDok + '''''''' + OB_Skrot + '''''''' FROM CDN.Obiekty WHERE OB_GIDTyp = @gidTyp;
IF @gidTyp IN (2048,2064,2080,2096,2112,2128,2144,2160,2176,2192,2224,2288)
SET @formatDok = @formatDok + ''+'' + '''''' '''''' + ''+'' + ''REPLACE(SPACE(5-LEN(cast(''+@p_Numer+'' as Varchar))) + cast(''+@p_Numer+'' as Varchar),'''' '''',''''0'''') +''''/'''' + cast(''+@p_Rok+'' as Varchar) + case when ''+@p_Seria+''<>'''''''' then ''''/'''' + ''+@p_Seria+'' else '''''''' end
''
ELSE IF @gidTyp IN (7680,7681,7682,7683)
SET @formatDok = @formatDok + ''+'' + ''''''/'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),2) +''''/''''+ cast(''+@p_numer+'' as varchar) + case when ''+@p_spityp+'' <> 0 then ''''/''''+ cast(''+@p_spityp+'' as varchar) else '''''''' end + case when ''+@p_trntyp+'' <> 0 then ''''/''''+ cast(''+@p_trntyp+'' as varchar) else '''''''' end
''
ELSE IF @gidTyp IN (7684,7690)
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),4) + ''''/''''+ rtrim(''+@p_seria+'') + ''''/'''' + cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp = 2832
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),2) + ''''/'''' + rtrim(''+@p_seria+'') + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp = 4144
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''rtrim(''+@p_seria+'') + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + cast(''+@p_rok+'' as varchar) +''''/''''+ right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) +''''/''''+ cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp = 4145
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''rtrim(''+@p_seria+'') + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + cast(''+@p_rok+'' as varchar) +''''/''''+ right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) +''''/''''+ cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp = 4146
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''rtrim(''+@p_seria+'') + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + cast(''+@p_rok+'' as varchar) +''''/''''+ right(''''0''''+cast(''+@p_miesiac+'' as varchar),2) +''''/''''+ cast(''+@p_numer+'' as varchar)''
ELSE IF @gidTyp = 784
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),2) + ''''/'''' + rtrim(''+@p_seria+'') + ''''/''''+ cast(''+@p_numer+'' as varchar) + case when ''+@p_TrnTyp+''=1 then ''''/RBO'''' else '''''''' end + ''''/'''' + cast(''+@p_miesiac+'' as varchar)
''
ELSE IF @gidTyp IN (2976,2977)
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''cast(''+@p_numer+'' as varchar) + '''' '''' + cast(''+@p_miesiac+'' as varchar) + ''''/'''' + cast(''+@p_rok+'' as varchar)
''
ELSE IF @gidTyp = 434
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''cast(''+@p_rok+'' as varchar) + ''''/'''' + case when rtrim(''+@p_seria+'')<>'''''''' then rtrim(''+@p_seria+'') + ''''/'''' else '''''''' end + cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp = 435
SET @formatDok = @formatDok + ''+'' + ''+'' + ''''''-'''''' + ''+'' + ''cast(''+@p_numer+'' as varchar) + ''''/'''' + RIGHT(cast(''+@p_Rok+'' as Varchar),2) + case when rtrim(''+@p_seria+'')<>'''''''' then ''''/'''' + rtrim(''+@p_seria+'') else '''''''' end
''
ELSE IF @gidTyp = 800
SET @formatDok = @formatDok +''''''RKB'''''' + ''+'' + ''right(cast(''+@p_rok+'' as varchar),2) + ''''/'''' + case when rtrim(''+@p_seria+'')<>'''''''' then rtrim(''+@p_seria+'') + ''''/'''' else '''''''' end + cast(''+@p_numer+'' as varchar)
''
ELSE IF @gidTyp in (2978,2979,2980,2981)
SET @formatDok = @formatDok + ''+'' + ''''''-'''''' + ''+'' + ''cast(''+@p_numer+'' as varchar) + ''''/'''' + right(cast(''+@p_rok+'' as varchar),2) + ''''/'''' + rtrim(''+@p_seria+'') + ''''/'''' + case when ''+@p_seria+''='''''''' then '''''''' else ''''/'''' end + right(''''0''''+cast(''+@p_miesiac+'' as varchar),2)
''
END
FETCH NEXT FROM Dokumenty INTO @gidTyp
END
CLOSE Dokumenty
DEALLOCATE Dokumenty
IF @gidTyp IS NOT NULL SET @formatDok = @formatDok + '' ELSE ''
IF @formatDok <> '''' SET @formatDok = @formatDok + '' ''
SET @formatDok = @formatDok + ''CASE
WHEN ''+@p_SpiTyp+''=0 AND ''+@p_GidTyp+'' IN (2033,2041,2036,2044,2037,2045,1520,1521,1528,1529) THEN ''''(S)''''
WHEN ''+@p_TrnTyp+'' IN (12,13) THEN ''''(A)'''' ELSE '''''''' END + ''+@p_TypSkrot+'' + ''''-'''' ''
SELECT @Format=Kon_wartosc, @I=1, @Imax=len(kon_wartosc) FROM cdn.konfig WHERE Kon_Numer=992
WHILE @I<=@Imax
BEGIN
SET @Poprzedni = @formatDok
SET @formatDok = @formatDok + ''+ '' + CASE SUBSTRING(@Format,@I,1)
WHEN ''1'' THEN ''cast(''+@p_Numer+'' as Varchar) ''
WHEN ''2'' THEN ''REPLACE(SPACE(8-LEN(cast(''+@p_Numer+'' as Varchar))) + cast(''+@p_Numer+'' as Varchar),'''' '''',''''0'''') ''
WHEN ''3'' THEN ''RIGHT(cast(''+@p_Rok+'' as Varchar),2) ''
WHEN ''4'' THEN ''cast(''+@p_Rok+'' as Varchar) ''
WHEN ''5'' THEN ''RTRIM(''+@p_Seria+'') ''
WHEN ''6'' THEN ''CASE WHEN ''+@p_Miesiac+'' = 0 THEN '''''''' ELSE cast(''+@p_miesiac+'' as Varchar) END ''
WHEN ''7'' THEN ''CASE WHEN ''+@p_Miesiac+'' = 0 THEN '''''''' WHEN ''+@p_Miesiac+''>= 10 THEN cast(''+@p_Miesiac+'' as Varchar) ELSE ''''0''''+cast(''+@p_Miesiac+'' as Varchar) END ''
END
IF (@Poprzedni<>@formatDok AND @I+2 <= @Imax) SET @formatDok=@formatDok+ ''+'' + '''''''' + SUBSTRING(@Format,@I+1,1) + '''''''' + '' ''
SET @I = @I+2
END
IF @gidTyp IS NOT NULL SET @formatDok = @formatDok + '' END''
RETURN @formatDok
END
'
IF OBJECT_ID('[dbo].[NumerDokumentuFormatBI]', 'FN') IS NULL
BEGIN
EXEC (@function)
END
------------------------------
-- KOREKTY --
IF (@czyKnt = 'Nie')
BEGIN
SET @KntNazwa = @czyKnt
SET @KntAkronim = @czyKnt
SET @KntOpiekun = @czyKnt
SET @KntRodzaj = @czyKnt
SET @KntRegion = @czyKnt
SET @KntGeografia = @czyKnt
SET @czyKntGr = @czyKnt
SET @czyRegGr = @czyKnt
END
IF (@czyKnd = 'Nie')
BEGIN
SET @KndNazwa = @czyKnd
SET @KndAkronim = @czyKnd
SET @KndGeografia = @czyKnd
END
IF (@czyKnp = 'Nie')
BEGIN
SET @KnpNazwa = @czyKnp
SET @KnpAkronim = @czyKnp
SET @KnpGeografia = @czyKnp
END
IF (@czyDok = 'Nie')
BEGIN
SET @DokTyp = @czyDok
SET @DokNumer = @czyDok
SET @DokWaluta = @czyDok
SET @DokSeria = @czyDok
SET @DokStatus = @czyDok
SET @DokControlling = @czyDok
END
IF (@KntNazwa = 'Nie' and @KntAkronim = 'Nie' and @KntOpiekun = 'Nie' and @KntRodzaj = 'Nie' and @KntRegion = 'Nie' and @KntGeografia = 'Nie' and @czyKntGR = 'Nie' and @czyRegGR = 'Nie') SET @czyKnt = 'Nie' ELSE SET @czyKnt = 'Tak'
IF (@KndNazwa = 'Nie' and @KndAkronim = 'Nie' and @KndGeografia = 'Nie') SET @czyKnd = 'Nie' ELSE SET @czyKnd = 'Tak'
IF (@KnpNazwa = 'Nie' and @KnpAkronim = 'Nie' and @KnpGeografia = 'Nie') SET @czyKnp = 'Nie' ELSE SET @czyKnp = 'Tak'
IF (@DokTyp = 'Nie' and @DokNumer = 'Nie' and @DokWaluta = 'Nie' and @DokSeria = 'Nie' and @DokControlling = 'Nie') SET @czyDok = 'Nie' ELSE SET @czyDok = 'Tak'
DECLARE @n char;
DECLARE @Korekty nvarchar(3);
DECLARE @RegionLevel Smallint;
SELECT @Korekty = ISNULL(CONVERT(nvarchar(3),Kon_Wartosc),'0') FROM CDN.Konfig WHERE Kon_Numer=2085;
DECLARE @poziom int
DECLARE @poziom_max int
DECLARE @sql nvarchar(max)
DECLARE @kolumnyRejony nvarchar(max)
DECLARE @kolumnyKntGrupy nvarchar(max)
DECLARE @i int
DECLARE @select1 varchar(max)
DECLARE @select2 varchar(max)
SET @select1 = ''
SET @select2 = ''
SET @n = char(10)
------------------------------
-- REJONY_GRUPY --
IF (@czyRegGr = 'Tak' and @czyKnt = 'Tak')
BEGIN
;with R (REJON_OrgId, REJON_ParOrgId, REJON_Nazwa, REJON_Poziom) as
( SELECT
REJ.Rej_Id AS REJON_OrgId,
REJ.Rej_OjciecId AS REJON_ParOrgId,
CONVERT(nvarchar,REJ.Rej_Nazwa) AS REJON_Nazwa,
0 AS REJON_Poziom
FROM cdn.Rejony REJ WHERE REJ_OjciecId=0
UNION ALL
SELECT
childREJ.Rej_Id AS REJON_OrgId,
childREJ.Rej_OjciecId AS REJON_ParOrgId,
CONVERT(nvarchar,childREJ.Rej_Nazwa) AS REJON_Nazwa,
R.REJON_Poziom+1 AS REJON_Poziom
from cdn.Rejony childREJ
INNER JOIN R ON childREJ.Rej_ojciecid = R.REJON_OrgId
)
SELECT * INTO #tmp_Regions FROM R order by REJON_OrgId
CREATE UNIQUE CLUSTERED INDEX UIX_1 on #tmp_Regions (REJON_OrgId)
SELECT @poziom_max = MAX(REJON_Poziom) FROM #tmp_Regions
SET @poziom = @poziom_max
SET @sql = N''
WHILE @poziom >= 0
BEGIN
SET @sql = N'ALTER TABLE #tmp_Regions ADD Poziom' + CAST(@poziom AS nvarchar) + N' nvarchar(40), PoziomID' + CAST(@poziom AS nvarchar) + N' nvarchar(40)'
EXEC(@sql)
IF (@poziom = @poziom_max)
BEGIN
SET @sql = N'UPDATE p
SET PoziomID'+ CAST(@poziom AS nvarchar) + N' = CASE WHEN REJON_Poziom ='+ CAST(@poziom AS nvarchar)
+ N' THEN REJON_ParOrgID ELSE REJON_OrgId END
FROM #tmp_Regions p'
EXEC (@sql)
SET @sql = N'UPDATE p
SET Poziom'+ CAST(@poziom AS nvarchar) + N' = p.REJON_Nazwa
FROM #tmp_Regions p'
EXEC (@sql)
END
ELSE
BEGIN
SET @sql = N'UPDATE p
SET PoziomID'+ CAST(@poziom AS nvarchar) + N' = CASE WHEN c.REJON_Poziom ='+ CAST(@poziom AS nvarchar)
+ N' THEN c.REJON_ParOrgID ELSE c.REJON_OrgId END
FROM #tmp_Regions p
LEFT OUTER JOIN #tmp_Regions c ON c.REJON_OrgID = p.PoziomID'+ CAST(@poziom+1 AS nvarchar)
EXEC (@sql)
SET @sql = N'UPDATE p
SET Poziom'+ CAST(@poziom AS nvarchar) + N' = c.REJON_Nazwa
FROM #tmp_Regions p
LEFT OUTER JOIN #tmp_Regions c ON c.REJON_OrgID = p.PoziomID'+ CAST(@poziom+1 AS nvarchar)
EXEC (@sql)
END
SET @poziom = @poziom - 1
END
set @kolumnyRejony = ''
set @i=0
while (@i<=@poziom_max)
begin
set @kolumnyRejony = @kolumnyRejony + ',"Rejon - Grupa Poziom ' + LTRIM(@i) + '" = CASE WHEN Rej.Poziom' +LTRIM(@i) + ' IS NULL THEN ''<nieznany>'' ELSE Rej.Poziom' + LTRIM(@i) + ' END'
set @i = @i + 1
end;
END
------------------------------
-- KNT_GRUPY --
IF (@czyKntGr = 'Tak' and @czyKnt = 'Tak')
BEGIN
;WITH K (GidTyp, ID, GrTyp, GrNazwa, ParID, Poziom)
AS
(
SELECT KGD_GidTyp, KGD_GIDNumer, KGD_GrOTyp, KGD_Kod, KGD_Gronumer , 0 as poziom
From cdn.kntgrupydom
where KGD_Gronumer = 0
union all
SELECT KGD_GidTyp, KGD_GIDNumer, KGD_GrOTyp, KGD_Kod, KGD_Gronumer , p.poziom +1 as poziom
From K p
JOIN cdn.kntgrupydom c ON c.KGD_GrONumer=p.ID and p.GidTyp = -32
where c.KGD_Gronumer <> 0
)
SELECT * INTO #tmpKntGr FROM K
CREATE UNIQUE CLUSTERED INDEX UIX_2 on #tmpKntGr (GidTyp, ID)
SELECT @poziom_max = MAX(poziom) FROM #tmpKntGr
SET @poziom = @poziom_max
SET @sql = N''
WHILE @poziom >= 0
BEGIN
SET @sql = N'ALTER TABLE #tmpKntGr ADD Poziom' + CAST(@poziom AS nvarchar) + N' nvarchar(40), PoziomID' + CAST(@poziom AS nvarchar) + N' nvarchar(40)'
EXEC(@sql)
IF @poziom = @poziom_max
BEGIN
SET @sql = N'UPDATE #tmpKntGr
SET PoziomID' + CAST(@poziom AS nvarchar) + '= ParID '
EXEC(@sql)
SET @sql = N'UPDATE #tmpKntGr
SET Poziom' + CAST(@poziom AS nvarchar) + ' = GrNazwa'
EXEC(@sql)
END
ELSE
BEGIN
SET @sql = N'UPDATE c
SET c.Poziom' + CAST(@poziom AS nvarchar) + N' = (
CASE WHEN c.poziom <=' + CAST(@poziom AS nvarchar) + N' THEN CAST(c.GrNazwa AS nvarchar)
ELSE CAST(p.GrNazwa AS nvarchar) END)
FROM #tmpKntGr c
LEFT JOIN #tmpKntGr p
ON c.PoziomID' + CAST(@poziom + 1 AS nvarchar) + '= p.ID '
EXEC(@sql)
SET @sql = N'UPDATE c
SET c.PoziomID' + CAST(@poziom AS nvarchar) + N' = (
CASE WHEN c.poziom <=' + CAST(@poziom AS nvarchar) + N' THEN CAST(c.ParID AS nvarchar)
ELSE CAST(p.ParID AS nvarchar) END)
FROM #tmpKntGr c
LEFT JOIN #tmpKntGr p
ON c.PoziomID' + CAST(@poziom + 1 AS nvarchar) + '= p.ID '
EXEC(@sql)
END
SET @poziom = @poziom - 1
END
set @kolumnyKntGrupy = ''
set @i=0
while (@i<=@poziom_max)
begin
set @kolumnyKntGrupy = @kolumnyKntGrupy + ',"Kontrahent - Grupa Poziom ' + LTRIM(@i) + '" = CASE WHEN KNTG.Poziom' +LTRIM(@i) + ' IS NULL THEN ''<nieznany>'' ELSE KNTG.Poziom' + LTRIM(@i) + ' END'
set @i = @i + 1
end
END
------------------------------
-- MAIN QUERY --
------------------------------
-------- WYMIARY -----------
SET @select1 = @select1 + N'SELECT ' +@n
IF (@czyDataDok = 'Tak') SET @select1 = @select1 +N'
[Data Dokumentu - Data] = CONVERT(NVARCHAR(10),DATEADD(d, PRLV_Data, CONVERT(DATETIME,''28-12-1800'', 105)),20),
[Data Dokumentu - Tydzień Roku] = CONVERT(nvarchar(2),(datepart(DY, datediff(d, 0, PRLV_Data) / 7 * 7 + 3)+6) / 7),
[Data Dokumentu - Miesiac] =(CASE WHEN PRLV_DataM <10 THEN ''0'' + cast(PRLV_DataM as varchar(3)) ELSE cast(PRLV_DataM as varchar(2)) END) + '' ''+
DATENAME(month,DATEADD(d, PRLV_Data, CONVERT(DATETIME,''28-12-1800'', 105))),
[Data Dokumentu - Kwartał] = DATENAME(quarter,DATEADD(d, PRLV_Data, CONVERT(DATETIME,''28-12-1800'', 105))),
[Data Dokumentu - Rok] = DATENAME(year,DATEADD(d, PRLV_Data, CONVERT(DATETIME,''28-12-1800'', 105))),' +@n
IF (@DokTyp = 'Tak') SET @select1 = @select1 +N'
[Dokument - Typ] = ISNULL(OBJ.OB_Skrot,''<nieznany>''),' +@n
IF (@DokNumer = 'Tak') SET @select1 = @select1 +N'
[Dokument - Numer] = ISNULL('+[dbo].[NumerDokumentuFormatBI](3,
'PRLV_Dok1','PRLV_Dok2','PRLV_Dok3','PRLV_Dok4','PRLV_Dok5','PRLV_Dok6','PRLV_Dok7', 'OB_Skrot')+
N',''<nieznany>''),'+@n
IF (@DokWaluta = 'Tak') SET @select1 = @select1 +N'
[Dokument - Waluta] = PRLV_Waluta,' +@n
IF (@DokSeria = 'Tak') SET @select1 = @select1 +N'
[Dokument - Seria] = ISNULL(CONVERT(varchar(10),CASE WHEN LEN(PRLV_Dok6) = 0 THEN NULL ELSE PRLV_Dok6 END),''<nieznany>''),' +@n
IF (@DokControlling = 'Tak') SET @select1 = @select1 +N'
[Dokument - Controlling] = case when isnull(own_iloscelementow,0) = 0 then ''Nieopisany'' else ''Opisany'' end,'+@n
IF (@DokStatus = 'Tak') SET @select1 = @select1 +N'
[Dokument - Status] = ISNULL(PRLV_DokStatus,''<nieznany>''),' +@n
IF (@czyDataPla = 'Tak') SET @select1 = @select1 +N'
[Termin Płatności - Data] = CONVERT(NVARCHAR(10),DATEADD(d, PRLV_Termin, CONVERT(DATETIME,''28-12-1800'', 105)),20),
[Termin Płatności - Tydzień Roku] = CONVERT(nvarchar(2),(datepart(DY, datediff(d, 0, PRLV_Termin) / 7 * 7 + 3)+6) / 7),
[Termin Płatności - Miesiac] = (CASE WHEN PRLV_TerminM <10 THEN ''0'' + cast(PRLV_TerminM as varchar(3)) ELSE cast(PRLV_TerminM as varchar(2)) END) + '' ''+
DATENAME(month,DATEADD(d, PRLV_Termin, CONVERT(DATETIME,''28-12-1800'', 105))),
[Termin Płatności - Kwartał] = DATENAME(quarter,DATEADD(d, PRLV_Termin, CONVERT(DATETIME,''28-12-1800'', 105))),
[Termin Płatności - Rok] = DATENAME(year,DATEADD(d, PRLV_Termin, CONVERT(DATETIME,''28-12-1800'', 105))),' +@n
IF (@czyKnt = 'Tak')
BEGIN
IF (@KntNazwa = 'Tak') SET @select1 = @select1 +N'[Kontrahent - Nazwa] = ISNULL(KNT.Knt_Nazwa1,''<nieznany>''),' +@n
IF (@KntAkronim = 'Tak') SET @select1 = @select1 +N'[Kontrahent - Akronim] = ISNULL(KNT.Knt_Akronim,''<nieznany>''),' +@n
IF (@KntOpiekun = 'Tak') SET @select1 = @select1 +N'[Kontrahent - Opiekun] = ISNULL(CASE WHEN len(PRC.PRC_Nazwisko) >1 THEN PRC.PRC_Imie1 + '' '' + PRC.PRC_Nazwisko ELSE NULL END,''<nieznany>''),' + @n
IF (@KntRodzaj = 'Tak') SET @select1 = @select1 +N'[Kontrahent - Rodzaj] = ISNULL(SLWR.SLW_WartoscS,''<nieznany>''),' + @n
IF (@KntRegion = 'Tak') SET @select1 = @select1 +N'[Rejon - Nazwa] = ISNULL(REJON.REJ_Nazwa,''<nieznany>''),' + @n
IF (@KnpGeografia = 'Tak') SET @select1 = @select1 +N'
[Kontrahent - Województwo] = ISNULL(CASE WHEN len(upper(KNT.Knt_Wojewodztwo)) > 1 THEN upper(KNT.Knt_Wojewodztwo) ELSE NULL END,''<nieznany>''),
[Kontrahent - Powiat] = ISNULL(CASE WHEN len(KNT.Knt_Powiat) > 1 THEN KNT.Knt_Powiat ELSE NULL END,''<nieznany>''),
[Kontrahent - Gmina] = ISNULL(CASE WHEN len(KNT.Knt_Gmina) > 1 THEN KNT.Knt_Gmina ELSE NULL END,''<nieznany>''),
[Kontrahent - Miasto] = ISNULL(CASE WHEN len(KNT.Knt_Miasto) > 1 THEN KNT.Knt_Miasto ELSE NULL END,''<nieznany>''),'+@n
END
IF (@czyKnp = 'Tak')
BEGIN
IF (@KnpNazwa = 'Tak') SET @select1 = @select1 +N'[Kontrahent Płatnik - Nazwa] = ISNULL(KNP.Knt_Nazwa1,''<nieznany>''),' +@n
IF (@KnpAkronim = 'Tak') SET @select1 = @select1 +N'[Kontrahent Płatnik - Akronim] = ISNULL(KNP.Knt_Akronim,''<nieznany>''),' +@n
IF (@KnpGeografia = 'Tak') SET @select1 = @select1 +N'
[Kontrahent Płatnik - Województwo] = ISNULL(CASE WHEN len(upper(KNP.Knt_Wojewodztwo)) > 1 THEN upper(KNP.Knt_Wojewodztwo) ELSE NULL END,''<nieznany>''),
[Kontrahent Płatnik - Powiat] = ISNULL(CASE WHEN len(KNP.Knt_Powiat) > 1 THEN KNP.Knt_Powiat ELSE NULL END,''<nieznany>''),
[Kontrahent Płatnik - Gmina] = ISNULL(CASE WHEN len(KNP.Knt_Gmina) > 1 THEN KNP.Knt_Gmina ELSE NULL END,''<nieznany>''),
[Kontrahent Płatnik - Miasto] = ISNULL(CASE WHEN len(KNP.Knt_Miasto) > 1 THEN KNP.Knt_Miasto ELSE NULL END,''<nieznany>''),'+@n
END
IF (@FormaPlat = 'Tak') SET @select1 = @select1 +N'[Forma Płatności - Nazwa] = ISNULL(Trp_FormaNazwa,''<nieznana>''),'+@n
IF (@StatusPlat = 'Tak') SET @select1 = @select1 +N'[Status Płatności] = CASE PRLV_Rozliczony WHEN 0 THEN ''Nierozliczona'' WHEN 1 THEN ''Rozliczona'' ELSE ''Nie Podlega'' END,'+@n
IF (@Rejestr = 'Tak') SET @select1 = @select1 +N'[Rejestr] = ISNULL(convert(nvarchar(10),PRLV_Rejestr),''<nieznany>''),'+@n
------------ MIARY -------------
IF (@czyNaleznościdok = 'Tak') SET @select1 = @select1 +N'
[Nalezności dokument] =
(case
when PRLV_NaleznosciZobowiazania = 1 then
case when ' + @Korekty + N' = 0 then PRLV_KwotaPLN
else (case when PRLV_GIDTYP in (1497,1529,1320,1498) then 0 else PRLV_KwotaPLN end)
end
when PRLV_NaleznosciZobowiazania = 2 then
case when ' + @Korekty + N' = 0 then 0
else (case when PRLV_GIDTYP in (2009,2013,2041,2045,2044,2042,2043,1625,1832) then -PRLV_KwotaPLN else 0 end)
end
when PRLV_NaleznosciZobowiazania = 0 then
case PRLV_PrzychodyRozchody
when 1 then (case when PRLV_GidTyp = 7684 then PRLV_KwotaPLN else -PRLV_KwotaPLN end)
else 0
end
end),'+@n
IF (@czyNaleznościdokwal = 'Tak') SET @select1 = @select1 +N'
[Nalezności dokument waluta] =
(case PRLV_NaleznosciZobowiazania
when 1 then
case when ' + @Korekty + N' = 0 then PRLV_Kwota
else (case when PRLV_GIDTYP in (1497,1529,1320,1498) then 0 else PRLV_Kwota end)
end
when 2 then
case when ' + @Korekty + N' = 0 then 0
else (case when PRLV_GIDTYP in (2009,2013,2041,2045,2044,2042,2043,1625,1832) then -PRLV_Kwota else 0 end)
end
when 0 then
case PRLV_PrzychodyRozchody
when 1 then (case when PRLV_GidTyp = 7684 then PRLV_Kwota else -PRLV_Kwota end)
else 0
end
end),'+@n
IF (@czyZobowiązaniadok = 'Tak') SET @select1 = @select1 +N'
[Zobowiązania dokument] =
(case
when PRLV_NaleznosciZobowiazania = 1 then
case when ' + @Korekty + N' = 0 then 0
else (case when PRLV_GIDTYP in (1497,1529,1320,1498) then -PRLV_KwotaPLN else 0 end)
end
when PRLV_NaleznosciZobowiazania = 2 then
case when ' + @Korekty + N' = 0 then PRLV_KwotaPLN
else (case when PRLV_GIDTYP in (2009,2013,2041,2045,2044,2042,2043,1625,1832) then 0 else PRLV_KwotaPLN end)
end
when PRLV_NaleznosciZobowiazania = 0 then
case PRLV_PrzychodyRozchody
when 2 then (case when PRLV_GidTyp = 7684 then PRLV_KwotaPLN else -PRLV_KwotaPLN end)
else 0
end
end),'+@n
IF (@czyZobowiązaniadokwal = 'Tak') SET @select1 = @select1 +N'
[Zobowiązania dokument waluta] =
(case PRLV_NaleznosciZobowiazania
when 1 then
case when ' + @Korekty + N' = 0 then 0
else (case when PRLV_GIDTYP in (1497,1529,1320,1498) then -PRLV_Kwota else 0 end)
end
when 2 then
case when ' + @Korekty + N' = 0 then PRLV_Kwota
else (case when PRLV_GIDTYP in (2009,2013,2041,2045,2044,2042,2043,1625,1832) then 0 else PRLV_Kwota end)
end
when 0 then
case PRLV_PrzychodyRozchody
when 2 then (case when PRLV_GidTyp = 7684 then PRLV_Kwota else -PRLV_Kwota end)
else 0
end
end),' +@n
IF (len(rtrim(ltrim(@select1))) <= 10) SET @select1 = N'SELECT
[Nalezności dokument] =
(case
when PRLV_NaleznosciZobowiazania = 1 then
case when ' + @Korekty + N' = 0 then prlv_pozostajePLNDzisiaj
else (case when PRLV_GIDTYP in (1497,1529,1320,1498) then 0 else prlv_pozostajePLNDzisiaj end)
end
when PRLV_NaleznosciZobowiazania = 2 then
case when ' + @Korekty + N' = 0 then 0
else (case when PRLV_GIDTYP in (2009,2013,2041,2045,2044,2042,2043,1625,1832) then -prlv_pozostajePLNDzisiaj else 0 end)
end
when PRLV_NaleznosciZobowiazania = 0 then
case PRLV_PrzychodyRozchody
when 1 then (case when PRLV_GidTyp = 7684 then prlv_pozostajePLNDzisiaj else -prlv_pozostajePLNDzisiaj end)
else 0
end
end),'+@n
IF (RIGHT(@select1,1) = ',') SET @select1 = Left(@select1, Len(@select1)-1)
IF (RIGHT(@select1,2) = ','+@n) SET @select1 = Left(@select1, Len(@select1)-2)
IF (@czyRegGr = 'Tak' and @czyKnt = 'Tak') SET @select1 = @select1 +@kolumnyRejony
IF (@czyKntGr = 'Tak' and @czyKnt = 'Tak') SET @select1 = @select1 +@kolumnyKntGrupy
SET @select2 = @select2 + N' FROM (
SELECT PRLV_Dok1, PRLV_Dok2, PRLV_Dok3, PRLV_Dok4, PRLV_Dok5, PRLV_Dok6, PRLV_Dok7, PRLV_GIDTyp, PRLV_GIDFirma, PRLV_GIDNumer, PRLV_GIDLp, PRLV_SpiTyp, PRLV_SpiFirma, PRLV_SpiNumer, PRLV_SpiLp, PRLV_Kwota, PRLV_KwotaPLN, PRLV_Pozostaje, PRLV_PozostajePLN, PRLV_PozostajePLNDzisiaj, PRLV_NaleznosciZobowiazania, PRLV_PrzychodyRozchody,
PRLV_Data, PRLV_DataM, PRLV_Termin, PRLV_TerminM, PRLV_SpodziewanyTermin, PRLV_Rozliczony, PRLV_Waluta, PRLV_Rejestr, PRLV_TypRejestru, PRLV_Zaksiegowano,
PRLV_KnpTyp, PRLV_KnpNumer, PRLV_Akronim, PRLV_Nazwa1, PRLV_Miasto, PRLV_Status, PRLV_Numer, PRLV_KntTyp, PRLV_KntNumer,
PRLV_Wynagrodzenie,PRLV_DokStatus
FROM(
SELECT
a.gidtypnag as PRLV_Dok1
,a.spityp as PRLV_Dok2
,a.trntyp as PRLV_Dok3
,a.numer as PRLV_Dok4
,a.rok as PRLV_Dok5
,a.seria as PRLV_Dok6
,a.miesiac as PRLV_Dok7
,a.gidtyp as PRLV_GIDTyp
, a.gidfirma as PRLV_GIDFirma
, a.gidnumer as PRLV_GIDNumer
, a.gidlp as PRLV_GIDLp
, a.spityp as PRLV_SpiTyp
, a.spifirma as PRLV_SpiFirma
, a.spinumer as PRLV_SpiNumer
, a.spilp as PRLV_SpiLp
, a.kwota as PRLV_Kwota
, a.kwotapln as PRLV_KwotaPLN
, a.pozostaje as PRLV_Pozostaje
, a.kwotapln - isnull(cdn.PlatnoscSumaRozliczenia(a.gidtyp, a.gidnumer, a.gidlp, 3, 0, 0),0) as PRLV_PozostajePLN
, --a.pozostajepln,
a.pozostaje*ISNULL((SELECT TOP 1 WaE_KursL/WaE_KursM FROM CDN.WalElem WHERE WAE_Symbol=a.waluta AND WAE_Lp=a.nrkursu AND WAE_KursTS<DATEDIFF(ss,''19900101'',GetDate()) ORDER BY WAE_KursTS DESC),1) as PRLV_PozostajePLNDzisiaj
,a.naleznosci as PRLV_NaleznosciZobowiazania
, a.przychody as PRLV_PrzychodyRozchody
,a.data as PRLV_Data
,Month(DATEADD(d, a.data, CONVERT(DATETIME,''28-12-1800'', 105))) as PRLV_DataM
, a.termin as PRLV_Termin
,Month(DATEADD(d, a.termin, CONVERT(DATETIME,''28-12-1800'', 105))) as PRLV_TerminM
, a.spodziewanytermin as PRLV_SpodziewanyTermin
, a.rozliczony as PRLV_Rozliczony
, a.waluta as PRLV_Waluta
, kar_seria as PRLV_Rejestr
, kar_typ as PRLV_TypRejestru
, a.zaksiegowano as PRLV_Zaksiegowano
,a.knptyp as PRLV_KnpTyp
, a.knpnumer as PRLV_KnpNumer
, podv_akronim as PRLV_Akronim
, podv_nazwa1 as PRLV_Nazwa1
, podv_miasto as PRLV_Miasto
, a.status as PRLV_Status
, a.numer as PRLV_Numer
, a.knttyp as PRLV_KntTyp
, a.kntnumer as PRLV_KntNumer
,a.Wynagrodzenie as PRLV_Wynagrodzenie
,a.DokStatus as PRLV_DokStatus
FROM(
SELECT kaz_gidtyp gidtyp, kaz_gidfirma gidfirma, kaz_gidnumer gidnumer, kaz_gidlp gidlp, kaz_gidtyp spityp, kaz_gidfirma spifirma, kaz_gidnumer spinumer, kaz_gidlp spilp,
kaz_numerdokumentu dokument,
kaz_kwotaroz kwota, KAZ_KwotaSys kwotapln, --case when kaz_waluta=kaz_walutaroz then kaz_kwotaroz * kaz_kursl / case kaz_kursm when 0 then 1 else kaz_kursm end else kaz_kwota end kwotapln,
kaz_pozostajeroz pozostaje, 0 pozostajepln, --case when kaz_waluta=kaz_walutaroz then kaz_pozostajeroz * kaz_kursl / case kaz_kursm when 0 then 1 else kaz_kursm end else kaz_pozostaje end pozostajepln,
kaz_datazapisu data, kaz_datazapisu termin, kaz_datazapisu spodziewanytermin,
kaz_raportbo trntyp, krp_numer numer, kaz_rok rok, kaz_seria seria, kaz_krplp miesiac, kaz_knttyp knptyp, kaz_kntfirma kntfirma, kaz_kntnumer knpnumer, kaz_rozliczony rozliczony,
0 naleznosci, case kaz_rp when 1 then 2 else 1 end przychody, kaz_walutaroz waluta, kar_gidnumer, kaz_zaksiegowano zaksiegowano,
kaz_nrkursu nrkursu, kaz_gidtyp gidtypnag, 3 status, kaz_knttyp knttyp, kaz_kntnumer kntnumer,
kaz_Wynagrodzenie Wynagrodzenie,
case when kaz_zaksiegowano = 1 then ''Zaksiegowane''
when krp_gidtyp is not NULL THEN ''Zatwierdzony'' else ''W Buforze'' end as DokStatus
FROM CDN.zapisy JOIN CDN.Raporty ON KRP_GIDTyp=KAZ_KRPTyp AND KRP_GIDNumer=KAZ_KRPNumer
JOIN CDN.Rejestry r1 ON KAZ_Seria=r1.KAR_Seria
WHERE kaz_Anulowany = 0
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, t2.trn_spityp, t1.trn_spifirma, t1.trn_spinumer, t1.trn_spilp, case when t2.trn_gidtyp in (2046,2041,1832) and t2.trn_gidtyp & 8>0 then t2.trn_nrkorekty else case when t2.trn_dokumentobcy = '''' then t2.trn_nrkorekty else t2.trn_dokumentobcy end end,
trp_kwota, trp_kwotasys,--trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
t2.trn_data2, trp_termin, trp_spodziewanytermin,
t2.trn_trntyp,t2.trn_trnnumer,t2.trn_trnrok,t2.trn_trnseria,t2.trn_trnmiesiac, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, t1.trn_zaksiegowano,
trp_nrkursu, t1.trn_gidtyp, trp_status, t2.trn_knttyp, t2.trn_kntnumer,
0, case when t1.trn_trnlp<127 then ''Anulowany'' else
case when t1.trn_stan < 3 then ''W Buforze''
when t1.trn_zaksiegowano = 1 then ''Zaksiegowany''
else ''Zatwierdzony'' end end as DokStatus
FROM CDN.traplat
JOIN CDN.tranag t1 ON t1.trn_gidtyp=trp_spityp and t1.trn_gidnumer=trp_spinumer
JOIN CDN.tranag t2 ON t2.trn_gidtyp=trp_gidtyp and t2.trn_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, imn_dokumentobcy,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0 , --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
imn_datawystawienia, trp_termin, trp_spodziewanytermin,
0,imn_imnnumer,imn_imnrok,imn_imnseria,imn_imnmiesiac, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, imn_zaksiegowano,
trp_nrkursu, imn_gidtyp, trp_status, imn_knttyp, imn_kntnumer,
0,case when imn_imnlp<127 then ''Anulowany'' else
case when imn_stan < 3 then ''W Buforze''
when imn_zaksiegowano = 1 then ''Zaksiegowany''
else ''Zatwierdzony'' end
end as DokStatus
FROM CDN.traplat JOIN CDN.impnag ON imn_gidtyp=trp_gidtyp and imn_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, men_numerdokumentu,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
men_datawprow, trp_termin, trp_spodziewanytermin,
0,men_numer,(men_rokmiesiac/100),men_seria,men_rokmiesiac%100, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, men_zaksiegowano,
trp_nrkursu, men_gidtyp, trp_status, case when isnull(Mdn_PodmiotMiejsce,0) = 1 then men_knttyp else trp_knttyp end, case when isnull(Mdn_PodmiotMiejsce,0) = 1 then men_kntnumer else trp_kntnumer end,
0, case when men_zaksiegowano = 1 then ''Zaksiegowany''
else ''Zatwierdzony'' end as DokStatus
FROM CDN.traplat JOIN CDN.memnag ON men_gidtyp=trp_gidtyp and men_gidnumer=trp_gidnumer
left JOIN cdn.MemDokNag on Men_MdnNumer = Mdn_GIDNumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, pin_identyfikator,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
pin_data, trp_termin, trp_spodziewanytermin,
0,pin_numer,pin_rok,'''',pin_miesiac, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, pin_zaksiegowano,
trp_nrkursu, pin_gidtyp, trp_status, trp_knttyp, trp_kntnumer,
0,case when pin_zaksiegowano = 1 then ''Zaksiegowany''
else ''Zatwierdzony'' end as DokStatus
FROM CDN.traplat JOIN CDN.piknag ON pin_gidtyp=trp_gidtyp and pin_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, san_numersad,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
san_datazgloszenia, trp_termin, trp_spodziewanytermin,
0,san_sannumer,san_sanrok,san_sanseria,0 , trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, san_zaksiegowano,
trp_nrkursu, san_gidtyp, trp_status, case when trp_gidtyp = 3376 then san_pdmtyp else san_knttyp end, case when trp_gidtyp = 3376 then san_pdmnumer else san_kntnumer end,
0,case when san_sanlp<127 then ''Anulowany'' else
case when san_stan < 3 then ''W Buforze''
when san_zaksiegowano = 1 then ''Zaksięgowany''
else ''Zatwierdzony'' end end as DokStatus
FROM CDN.traplat JOIN CDN.sadnag ON san_gidtyp=trp_gidtyp and san_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, '''',
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
upn_dataup, trp_termin, trp_spodziewanytermin,
0,upn_numer,upn_rok,upn_seria,0 , trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, upn_zaksiegowano,
trp_nrkursu, upn_gidtyp, trp_status, trp_knttyp, trp_kntnumer,
0, case when upn_zaksiegowano = 1 then ''Zaksięgowany''
else ''Zatwierdzony'' end as DokStatus
FROM CDN.traplat JOIN CDN.uponag ON upn_gidtyp=trp_gidtyp and upn_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, bos_gidlp, trp_spifirma, trp_spinumer, trp_spilp, bos_numerdokumentu,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
bos_data, trp_termin, trp_spodziewanytermin,
bos_subgidlp,bon_numer,bon_rokmiesiac/100,'''',bon_rokmiesiac%100, boe_knttyp, trp_kntfirma, boe_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, case when boe_status in(1,3) then 1 else 0 end,
trp_nrkursu, bon_gidtyp, trp_status, trp_knttyp, trp_kntnumer,
0, case when bon_status<3 then ''W Buforze'' else ''Zatwierdzony'' end as DokStatus
FROM CDN.traplat JOIN CDN.bilansotwarciaselem ON bos_trptyp=trp_gidtyp and bos_trpnumer=trp_gidnumer and bos_trplp=trp_gidlp
JOIN CDN.BilansOtwarciaElem ON BOE_GIDTyp=BOS_GIDTyp AND BOE_GIDNumer=BOS_GIDNumer AND BOE_GIDLp=BOS_GIDLp
JOIN CDN.BilansOtwarciaNag ON BON_GIDTyp=BOS_GIDTyp AND BON_GIDNumer=BOS_GIDNumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, pon_dpnid, trp_spifirma, trp_spinumer, trp_spilp, '''',
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
datediff( dd, ''18001228'', dateadd( ss, pon_tstamp, ''19900101'' ) ), trp_termin, trp_spodziewanytermin,
pon_typpodatku,pon_numer,pon_rok,substring(cast(pon_identyfikator as varchar(8)),1,case when pon_identyfikator>=10000000 then 2 else 1 end),pon_miesiac, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer, pon_zaksiegowano,
trp_nrkursu, pon_gidtyp, trp_status, trp_knttyp, trp_kntnumer,
0,case when pon_status = 3 then ''Anulowany'' else
case when pon_status = 0 then ''W Buforze''
when pon_zaksiegowano = 1 then ''Zaksiegowany''
else ''Zatwierdzony'' end end as DokStatus
FROM CDN.traplat JOIN CDN.PodNag ON pon_gidtyp=trp_gidtyp and pon_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, prl_dokumentobcy,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
prl_datawyst, trp_termin, trp_spodziewanytermin,
0,prl_numer,prl_rok,prl_seria,0, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case prl_gidtyp when 7684 then case trp_typ when 1 then 2 else 1 end else 0 end, case prl_gidtyp when 7690 then case trp_typ when 1 then 2 else 1 end else 0 end, trp_waluta, trp_karnumer, 0,
trp_nrkursu, prl_gidtyp, trp_status, prl_knttyp, prl_kntnumer,
0,null as DokStatus
FROM CDN.traplat JOIN CDN.Preliminarz ON prl_gidtyp=trp_gidtyp and prl_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, '''',
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
krp_datazamkniecia, trp_termin, trp_spodziewanytermin,
0,krp_numer,krp_rok,krp_seria,0, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona, --trp_typ,
case trp_typ when 1 then 2 else 1 end, trp_typ, trp_waluta, trp_karnumer, krp_zaksiegowano,
trp_nrkursu, krp_gidtyp, trp_status, trp_knttyp, trp_kntnumer,
0,null as DokStatus
FROM CDN.traplat JOIN CDN.Raporty ON krp_gidtyp=trp_gidtyp and krp_gidnumer=trp_gidnumer
UNION ALL
SELECT trp_gidtyp, trp_gidfirma, trp_gidnumer, trp_gidlp, trp_spityp, trp_spifirma, trp_spinumer, trp_spilp, lpl_DokumentObcy,
trp_kwota, trp_kwotasys, --trp_kwota * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
trp_pozostaje, 0, --trp_pozostaje * trp_kursl / case trp_kursm when 0 then 1 else trp_kursm end,
lpl_dataWyp, trp_termin, trp_spodziewanytermin,
0,lpl_numer,lpl_rok,lpl_Seria,lpl_miesiac, trp_knttyp, trp_kntfirma, trp_kntnumer, trp_rozliczona,
case trp_typ when 1 then 2 else 1 end, 0, trp_waluta, trp_karnumer,case when lpl_status=2 then 1 else 0 end,
trp_nrkursu, lpl_typ, trp_status, trp_knttyp, trp_kntnumer,
0,null as DokStatus
FROM CDN.traplat JOIN CDN.ListyPlac ON lpl_typ=trp_gidtyp join cdn.wyplaty on wpl_id=trp_gidnumer and WPL_LPLId = LPL_Id
) a
LEFT OUTER JOIN CDN.Rejestry r2 ON a.kar_gidnumer=r2.kar_gidnumer
LEFT OUTER JOIN CDN.PodmiotyView ON a.knptyp=podv_gidtyp and a.knpnumer=podv_gidnumer
WHERE (a.data BETWEEN '
SET @select2 = @select2 + convert(nvarchar,@DataOd)
SET @select2 = @select2 + N' AND '
SET @select2 = @select2 + convert(nvarchar,@DataDo)
SET @select2 = @select2 + N')
) as P
) as PreliminarzView
LEFT OUTER JOIN CDN.OpisWymNag ON prlv_gidtyp = own_gidtyp and prlv_gidnumer = own_gidnumer
LEFT JOIN CDN.TraPlat PLAT ON trp_gidtyp = prlv_gidtyp and trp_gidnumer = prlv_gidnumer and trp_gidlp = prlv_gidlp'+@n
IF (@czyDok = 'Tak') SET @select2 = @select2 + N' LEFT OUTER JOIN CDN.Obiekty OBJ ON OBJ.OB_GIDTyp = PRLV_GIDTyp ' +@n
IF (@czyKnt = 'Tak')
BEGIN
SET @select2 = @select2 + N' LEFT OUTER JOIN CDN.KntKarty KNT ON KNT.Knt_GIDNumer = prlv_kntnumer ' +@n
IF (@KntOpiekun = 'Tak') SET @select2 = @select2 + N' LEFT JOIN CDN.KnTOpiekun KTO ON KNT.Knt_GIDNumer=KTO.KtO_KntNumer and KTO.KtO_KntTyp=32
LEFT JOIN CDN.PrcKarty PRC ON PRC.Prc_GIDNumer=KTO.KtO_PrcNumer' +@n
IF (@KntRodzaj = 'Tak') SET @select2 = @select2 + N' LEFT OUTER JOIN CDN.Slowniki SLWR ON KNT.KNT_rodzaj = SLWR.slw_id ' +@n
IF (@KntRegion = 'Tak') SET @select2 = @select2 + N' LEFT OUTER JOIN CDN.Rejony REJON ON KNT.Knt_RegionCRM = REJON.REJ_Id ' + @n
IF (@czyRegGr = 'Tak') SET @select2 = @select2 + N' LEFT OUTER JOIN #tmp_Regions REJ ON KNT.Knt_RegionCRM = REJ.REJON_OrgId' + @n
IF (@czyKntGr = 'Tak') SET @select2 = @select2 + N' LEFT OUTER JOIN #tmpKntGr KNTG ON KNTG.GidTyp = 32 and KNTG.ID = KNT.Knt_GIDNumer' + @n
END
IF (@czyKnp = 'Tak') SET @select2 = @select2 + N' LEFT JOIN CDN.KntKarty KNP ON KNP.Knt_GIDNumer = prlv_knpnumer' +@n
EXEC (@Select1 + @Select2)
IF OBJECT_ID(N'tempdb..#tmp_Regions',N'U') IS NOT NULL
DROP TABLE #tmp_Regions
IF OBJECT_ID(N'tempdb..#tmpKntGr',N'U') IS NOT NULL
DROP TABLE #tmpKntGr
