E-Mail-Erinnerungen für überfällige Forderungen
Dieses Thema enthält Anweisungen zum Erstellen eines Jobs, der überfällige Forderungen identifiziert und E-Mail-Erinnerungen an die Finanzverantwortlichen der Schuldner sendet.
Globale Variablen deklarieren
Deklarieren Sie zunächst die globalen Variablen:
declare
@cSubjekt char(40), -- Betreff-ID
@cImePriimek varchar(255), -- vollständiger Name des Finanzverantwortlichen
@cEmail varchar(255), -- E-Mail-Adresse
@cVezniDok varchar(20), -- verknüpftes Dokument (Rechnungsnummer)
@dDatumVal datetime, -- Fälligkeitsdatum
@dDatumDok datetime, -- Dokumentdatum
@mDebet money, -- Sollbetrag
@mKredit money, -- Habenbetrag
@mSaldo money, -- Saldo
@mGrandTotal money, -- Gesamtsaldo des Betreffs
@cNaslovEmaila varchar(255), -- E-Mail-Betreff
@cSeznam varchar(8000) -- Liste der überfälligen Rechnungen
Finanzverantwortliche abrufen
Der folgende Code öffnet ein Recordset mit den Namen, Betreff-IDs und E-Mail-Adressen der Finanzverantwortlichen.
Da E-Mails nur an Kontakte mit gültiger E-Mail-Adresse gesendet werden, ist ein Standard JOIN ausreichend, und es ist nicht notwendig, einen LEFT JOIN oder RIGHT JOINzu verwenden.
-- Recordset mit allen Finanzverantwortlichen vorbereiten
declare crDolzniki cursor local fast_forward for
select RTrim(C.IME)+' '+RTrim(C.PRIIMEK) as IMEPRIIMEK,C.SUBJEKT, A.TEL
from CONTACTS C
join CONTADDRESS A on C.SUBJEKT = A.SUBJEKT and C.POZ = A.POZ and TIP = 'E'
where C.VLOGA like 'Financial manager'
open crDolzniki
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
while @@fetch_status = 0
begin -- Schleife durch alle Finanzverantwortlichen
-- !!! HIER ETWAS NÜTZLICHES EINFÜGEN !!! --
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
end
close crDolzniki
deallocate crDolzniki
Überfällige Rechnungen abrufen
Die folgende Abfrage ruft alle überfälligen Rechnungen für einen bestimmten Betreff ab und speichert sie in einem Recordset.
-- Recordset mit überfälligen Posten für jeden Betreff erstellen
declare crOdprte cursor local fast_forward for
select
P.VEZNIDOK,
(select top 1 X.DATUMVAL
from TEMEPOZ X
where X.SUBJEKT = P.SUBJEKT
and X.KONTO = P.KONTO
and X.VEZNIDOK = P.VEZNIDOK
and X.DEBET <> 0
order by X.DATUMVAL) as DATUMVAL,
(select top 1 X.DATUMDOK
from TEMEPOZ X
where X.SUBJEKT = P.SUBJEKT
and X.KONTO = P.KONTO
and X.VEZNIDOK = P.VEZNIDOK
and X.DEBET <> 0
order by X.DATUMVAL) as DATUMDOK,
Sum(P.DEBET) as DEBET,
Sum(P.KREDIT) as KREDIT,
Sum(P.DEBET - P.KREDIT) as Saldo
from TEME G
join TEMEPOZ P on P.KLJUC = G.KLJUC
and P.KONTO = '1200'
and (P.DATUMVAL <= GetDate())
where P.SUBJEKT = @cSubjekt
group by P.VEZNIDOK, P.SUBJEKT, P.KONTO
having (Sum(P.DEBET - P.KREDIT) <> 0)
order by P.VEZNIDOK
E-Mail-Nachricht erstellen
Verwenden Sie den folgenden Code, um Daten aus dem Recordset in den E-Mail-Nachrichtentext (@cSeznam) zu übertragen.
set @cSeznam = ''
set @mGrandTotal = 0
open crOdprte
fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
while @@fetch_status = 0
begin
set @cSeznam = @cSeznam + @cVezniDok + Convert(char(12),@dDatumVal,104) + Convert(char(12),@dDatumDok,104)
+ Convert(char(17),@mDebet,1) + Convert(char(17),@mKredit,1) + Convert(char(17),@mSaldo,1)
+ Char(13) + Char(10)
set @mGrandTotal = @mGrandTotal + @mSaldo
fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
end
close crOdprte
deallocate crOdprte
E-Mail senden
Prüfen Sie, ob die Liste überfällige Posten enthält, und senden Sie die E-Mail-Nachricht.
if @cSeznam <> ''
begin
set @cNaslovEmaila = 'Liste der überfälligen Posten für ' + @cSubjekt + ' zum: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Verknüpftes Dokument Fälligkeitsdatum Dokumentdatum Soll Haben Saldo' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Gesamt:.....................................................................' + Convert(char(19),@mGrandTotal,1)
-- E-Mail senden
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSeznam,
@subject = @cNaslovEmaila
end
Job planen
Fügen Sie die vollständige Abfrage einem SQL Server-Job hinzu, definieren Sie einen Ausführungsplan, und der Prozess ist bereit für die automatische Ausführung.
Hinweis: Ein SQL Server-Jobschritt kann maximal 7.800 Zeichen enthalten.
Die untenstehende Abfrage wird ohne Kommentare und unnötige Leerzeichen bereitgestellt, um diese Einschränkung einzuhalten.
declare
@cSubjekt char(40),
@cImePriimek varchar(255),
@cEmail varchar(255),
@cVezniDok varchar(20),
@dDatumVal datetime,
@dDatumDok datetime,
@mDebet money,
@mKredit money,
@mSaldo money,
@mGrandTotal money,
@cNaslovEmaila varchar(255),
@cSeznam varchar(8000)
declare crDolzniki cursor local fast_forward for
select RTrim(C.IME)+' '+RTrim(C.PRIIMEK) as IMEPRIIMEK,C.SUBJEKT, A.TEL
from CONTACTS C
join CONTADDRESS A on C.SUBJEKT = A.SUBJEKT and C.POZ = A.POZ and TIP = 'E'
where C.VLOGA like 'Finančni direktor'
open crDolzniki
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
while @@fetch_status = 0
begin
declare crOdprte cursor local fast_forward for
select
P.VEZNIDOK,
(select top 1 X.DATUMVAL
from TEMEPOZ X
where X.SUBJEKT = P.SUBJEKT
and X.KONTO = P.KONTO
and X.VEZNIDOK = P.VEZNIDOK
and X.DEBET <> 0
order by X.DATUMVAL) as DATUMVAL,
(select top 1 X.DATUMDOK
from TEMEPOZ X
where X.SUBJEKT = P.SUBJEKT
and X.KONTO = P.KONTO
and X.VEZNIDOK = P.VEZNIDOK
and X.DEBET <> 0
order by X.DATUMVAL) as DATUMDOK,
Sum(P.DEBET) as DEBET,
Sum(P.KREDIT) as KREDIT,
Sum(P.DEBET - P.KREDIT) as Saldo
from TEME G
join TEMEPOZ P on P.KLJUC = G.KLJUC
and P.KONTO = '1200'
and (P.DATUMVAL <= GetDate())
where P.SUBJEKT = @cSubjekt
group by P.VEZNIDOK, P.SUBJEKT, P.KONTO
having (Sum(P.DEBET - P.KREDIT) <> 0)
order by P.VEZNIDOK
set @cSeznam = ''
set @mGrandTotal = 0
open crOdprte
fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
while @@fetch_status = 0
begin
set @cSeznam = @cSeznam + @cVezniDok + Convert(char(12),@dDatumVal,104) + Convert(char(12),@dDatumDok,104)
+ Convert(char(17),@mDebet,1) + Convert(char(17),@mKredit,1) + Convert(char(17),@mSaldo,1) + Char(13) + Char(10)
set @mGrandTotal = @mGrandTotal + @mSaldo
fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
end
close crOdprte
deallocate crOdprte
if @cSeznam <> ''
begin
set @cNaslovEmaila = 'Liste der überfälligen Posten für ' + @cSubjekt + ' zum: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Verknüpftes Dokument Fälligkeitsdatum Dokumentdatum Soll Haben Saldo' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Gesamt:.....................................................................' + Convert(char(19),@mGrandTotal,1)
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSeznam,
@subject = @cNaslovEmaila
end
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
end
close crDolzniki
deallocate crDolzniki