This topic contains instructions for a job that finds overdue receivables
and sends out e-mails to financial managers of debtors.
First, global variables are declared:
declare
@cSubjekt char(40), -- subject ID
@cImePriimek varchar(255), -- full name of financial manager
@cEmail varchar(255), -- e-mail address
@cVezniDok varchar(20), -- linked document (invoice number)
@dDatumVal datetime, -- due date
@dDatumDok datetime, -- document date
@mDebet money, -- debit amount
@mKredit money, -- credit amount
@mSaldo money, -- balance
@mGrandTotal money, -- total balance of subject
@cNaslovEmaila varchar(255), -- e-mail subject
@cSeznam varchar(8000) -- list of overdue invoices
The following piece of code opens the recordset with names, subject IDs
and e-mail addresses. Because e-mails are sent out only to those that have
an e-mail address, a normal join suffices, instead of a right/left
join:
-- prepare recordset with all financial managers
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 -- loop through all financial managers
-- !!! INSERT SOMETHING USEFUL HERE !!! --
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
end
close crDolzniki
deallocate crDolzniki
The following piece finds all overdue invoices of a given subject and
puts them in a recordset.
-- za vsakega naredi recordset z zapadlimi postavkami
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
Now you have to put data from that recordset into the e-mail message body (variable @cSeznam).
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
Check if the list is not empty and send the message.
if @cSeznam <> ''
begin
set @cNaslovEmaila = 'List of overdue items for ' + @cSubjekt + ' as of: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Linked document Due date Doc. date Debit Credit Balance' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Total:.....................................................................' + Convert(char(19),@mGrandTotal,1)
-- send e-mail
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSeznam,
@subject = @cNaslovEmaila
end
Add the complete query to SQL Server jobs,
define a schedule and it's ready to go.
 |
One step of an SQL Server job can have at most 7800
characters.
The query below is without comments and unnecessary spaces to
obey this limitation.
|
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 = 'List of overdue items for ' + @cSubjekt + ' as of: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Linked document Due date Doc. date Debit Credit Balance' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Total:.....................................................................' + 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