64 lines
3.1 KiB
Transact-SQL
64 lines
3.1 KiB
Transact-SQL
 
|
SET ANSI_NULLS ON
|
||
|
|
GO
|
||
|
|
SET QUOTED_IDENTIFIER ON
|
||
|
|
GO
|
||
|
|
|
||
|
|
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'my_ImpDM_Src' AND user_name(uid) = 'CDN')
|
||
|
|
DROP PROCEDURE [CDN].my_ImpDM_Src
|
||
|
|
GO
|
||
|
|
|
||
|
|
CREATE PROCEDURE [CDN].my_ImpDM_Src
|
||
|
|
(
|
||
|
|
@SP int, -- 1 - uwzglêdniaæ WZ, WZE i ich korekty
|
||
|
|
@ZAK int, -- 1 - uwzglêdniaæ PZ, PZI i ich korekty
|
||
|
|
@WEW int, -- 1 - uwzglêdniaæ RW, PW i ich korekty
|
||
|
|
@RM int, -- TrN_RokMiesiac
|
||
|
|
@Rok int, -- Rok
|
||
|
|
@Miesiac int -- Miesi¹c
|
||
|
|
)
|
||
|
|
AS
|
||
|
|
SET NOCOUNT ON;
|
||
|
|
|
||
|
|
declare @TN table(Dokument varchar(255), RokMiesiac int, TrN_GIDTyp int, TrN_GIDNumer int, TrN_Zaksiegowano int, DataDok int)
|
||
|
|
declare @DEB table(DT_GIDNumer int, DT_GIDLp int, DT_DC int, DT_Konto varchar(50), DT_Kwota decimal(21,2), DT_DataCentrali int)
|
||
|
|
declare @CRE table(DT_GIDNumer int, DT_GIDLp int, DT_DC int, DT_Konto varchar(50), DT_Kwota decimal(21,2), DT_DataCentrali int)
|
||
|
|
insert into @TN
|
||
|
|
select CDN.NazwaObiektu(TrN_GIDTyp, TrN_GIDNumer, 0, 2) as Dokument, TrN_RokMiesiac as RokMiesiac, TrN_GIDTyp, TrN_GIDNumer, TrN_Zaksiegowano, TrN_Data3
|
||
|
|
from (select TrN_GIDTyp, TrN_GIDNumer, TrN_RokMiesiac, TrN_Zaksiegowano, TrN_Data3 from CDN.TraNag
|
||
|
|
where TrN_Zaksiegowano > 0 and TrN_RokMiesiac = @RM and
|
||
|
|
( case when @WEW > 0 then case TrN_GIDTyp when 1616 then 1 when 1624 then 1 when 1617 then 1 when 1625 then 1 else 0 end else 0 end > 0 or
|
||
|
|
case when @ZAK > 0 then case TrN_GIDTyp when 1489 then 1 when 1497 then 1 when 1490 then 1 when 1498 then 1 else 0 end else 0 end > 0 or
|
||
|
|
case when @SP > 0 then case TrN_GIDTyp when 2001 then 1 when 2009 then 1 when 2015 then 1 when 2013 then 1 else 0 end else 0 end > 0)) TTT
|
||
|
|
insert into @DEB
|
||
|
|
select DT_GIDNumer, DT_GIDLp, DT_DC, DT_Konto, DT_Kwota, DT_DataCentrali from CDN.Dekrety where DT_DC = 1 and DT_Rok = @Rok and DT_Miesiac = @Miesiac
|
||
|
|
insert into @CRE
|
||
|
|
select DT_GIDNumer, DT_GIDLp, DT_DC, DT_Konto, DT_Kwota, DT_DataCentrali from CDN.Dekrety where DT_DC = 2 and DT_Rok = @Rok and DT_Miesiac = @Miesiac
|
||
|
|
|
||
|
|
select Lp = ROW_NUMBER() OVER(ORDER BY Dokument, deb.DT_Konto, cre.DT_Konto),
|
||
|
|
Dokument,
|
||
|
|
DEL_Opis,
|
||
|
|
RokMiesiac,
|
||
|
|
DataWyst = CDN.TsToDate(CDN.Data(DZK_Rok, DZK_Miesiac, DZK_Dzien), 0),
|
||
|
|
DataOper = CDN.TsToDate(DataDok, 0),
|
||
|
|
DokZr = DZK_DokumentZrodlowy,
|
||
|
|
TrN_GIDNumer,
|
||
|
|
Dekret = DZK_GIDNumer,
|
||
|
|
DZK_Dziennik,
|
||
|
|
Konto_Wn = deb.DT_Konto,
|
||
|
|
Kwota_Wn = deb.DT_Kwota,
|
||
|
|
Konto_Ma = cre.DT_Konto,
|
||
|
|
Kwota_Ma = cre.DT_Kwota,
|
||
|
|
DataImportu = CDN.TsToDate(deb.DT_DataCentrali, 1),
|
||
|
|
DZK_SumaCT,
|
||
|
|
DZK_SumaDT
|
||
|
|
from @TN inner join CDN.Dziennik on substring(DZK_Opis, 1, len(Dokument)) = Dokument and DZK_Rok = @Rok and DZK_Miesiac = @Miesiac
|
||
|
|
inner join CDN.DziennikElem on DZK_GIDNumer = DEL_GIDNumer
|
||
|
|
inner join @DEB deb ON deb.DT_GIDNumer = DEL_GIDNumer and deb.DT_GIDLp = DEL_GIDLp
|
||
|
|
inner join @CRE cre ON cre.DT_GIDNumer = DEL_GIDNumer and cre.DT_GIDLp = DEL_GIDLp
|
||
|
|
order by 1
|
||
|
|
|
||
|
|
|
||
|
|
GO
|
||
|
|
|
||
|
|
GRANT execute ON CDN.my_ImpDM_Src TO CDNRaport
|
||
|
|
GO
|