The script below will give you the users that will be suspended tomorrow. If
you would like to see the day after tomorrow then just change
" select @BillingDate = @BillingDate + 1"
For
" select @BillingDate = @BillingDate + 2"
Hope this helps.
-carlo
-----------------------------------------
declare
@BillingDate smalldatetime,
@SuspCC smallint,
@SuspCheck smallint,
@SuspACH smallint,
@SuspLEC smallint,
@SuspCorpCheck smallint,
@PlanID int,
@CustomerID int,
@Proc int,
@nSuccess int,
@LatePaymentTypeID smallint
set @Proc = 0
select @BillingDate = BillingDate from CommonData
select @BillingDate = @BillingDate + 1
select @SuspCC = cast(Val as smallint) from SetupMisc where SID = 56
select @SuspCheck = cast(Val as smallint) from SetupMisc where SID = 57
select @SuspACH = cast(Val as smallint) from SetupMisc where SID = 58
select @SuspCorpCheck = cast(Val as smallint) from SetupMisc where SID = 48
select @SuspLEC = cast(Val as smallint) from SetupMisc where SID = 97
--declare crsUpdate insensitive cursor for
select distinct c.regnumber, c.firstname, c.lastname, c.company,
c.homephone, c.BusinessPhone, c.fax
from Plans as P
inner join Customers as C on C.CustomerID = P.CustomerID
inner join CustomerAccounts as CA on CA.CustomerID =
P.CustomerID
inner join DebitMovements as DM on DM.PlanID = P.PlanID
inner join PaymentTypes as PT on PT.PaymentTypeID =
C.PaymentTypeID
inner join PlanInfo as I on I.PlanInfoID = P.PlanInfoID
left outer join PaymentInstrBillingProfile as PIBP on
PIBP.BillingProfileID = I.BillingProfileID and PIBP.PaymentInstrumentID =
C.PaymentTypeID
where P.Closed = 0
and not exists (select * from PlansSuspendState as PSS where
PSS.PlanID = P.PlanID and PSS.SuspendTypeID = 2)
and DM.NotPaid = 1
and CA.DebitSum > CA.CreditSum + PT.Treshold -- double
check
and ((case
when C.PaymentTypeID = 1 then dateadd(day,
-isnull(PIBP.PastDueActivePeriod, @SuspACH), @BillingDate)
when C.PaymentTypeID = 2 then
(case
when C.IsCorporate = 1 then
dateadd(day, -isnull(PIBP.PastDueActivePeriod, @SuspCorpCheck),
@BillingDate)
else dateadd(day,
-isnull(PIBP.PastDueActivePeriod, @SuspCheck), @BillingDate)
end)
when C.PaymentTypeID = 3 then dateadd(day,
-isnull(PIBP.PastDueActivePeriod, @SuspCC), @BillingDate)
when C.PaymentTypeID = 4 then dateadd(day,
-isnull(PIBP.PastDueActivePeriod, @SuspLEC), @BillingDate)
end) > DM.BillDate
)
and ((C.SuspendDate is NULL) or (C.SuspendDate is not NULL
and @BillingDate >= C.SuspendDate))
and ((isnull(C.CreditLimit, 0) = 0) or (C.CreditLimit > 0
and C.CreditLimit <= CA.DebitSum - (CA.CreditSum + PT.Treshold)))
and ((P.CustomerID = @CustomerID) or (@CustomerID is NULL))
----------------------------------------------------------------------------
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of GlobalNet, LLC
Sent: Thursday, May 05, 2005 9:44 AM
To: [email protected]
Subject: [Rodopi] Telling who is going to lock for non payment
Any ideas to this question?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Global Net, LLC Noc
Sent: Tuesday, May 03, 2005 3:34 PM
To: [email protected]
Subject: [Rodopi] Telling who is going to lock for non payment
I have a question. Does anyone have a way in Rodopi 5.4 to be able to tell
who is going to lock for non payment ? Say the next
day(s) for example?
Jeff
---------------------
To Leave the Rodopi mail list send a message to [EMAIL PROTECTED]
with the word LEAVE as the message body.
Please also visit the Rodopi FAQ at http://www.rodopi-faq.com
---------------------
---------------------
To Leave the Rodopi mail list send a message to [EMAIL PROTECTED]
with the word LEAVE as the message body.
Please also visit the Rodopi FAQ at http://www.rodopi-faq.com
---------------------
---------------------
To Leave the Rodopi mail list send a message to [EMAIL PROTECTED]
with the word LEAVE as the message body.
Please also visit the Rodopi FAQ at http://www.rodopi-faq.com
---------------------
