· 5 years ago · Feb 20, 2020, 01:04 PM
1
2
3
4
5/****** Object: UserDefinedFunction [dbo].[MemberHasDebtsCount] Script Date: 24/01/2017 11:33:22 ******/
6SET ANSI_NULLS ON
7GO
8
9SET QUOTED_IDENTIFIER ON
10GO
11
12
13
14
15IF EXISTS (SELECT * FROM SysObjects WHERE Name = 'MemberHasDebtsCount' AND xType = 'FN')
16BEGIN
17
18 drop function MemberHasDebtsCount
19
20END
21GO
22
23
24
25
26CREATE FUNCTION [dbo].[MemberHasDebtsCount] (@MemberNo INTEGER,@IncludedLinked BIT)
27
28RETURNS INT AS
29
30
31BEGIN
32
33
34 DECLARE @ReturnValue INT
35 DECLARE @MyPK INT
36
37
38 SELECT @MyPK = personPK FROM TRP_PE_Detail WHERE personno = @MemberNo
39
40 SELECT @ReturnValue = 0
41
42 --own payschedule
43 SELECT @ReturnValue = Count(*)
44 FROM dbo.TRP_PE_PaySchedule PAY
45 WHERE (PAY.PersonFK = @MyPK)
46 AND PAY.Amount <> 0 AND PAY.[Status] IN ('N','R','I')
47 AND DATEDIFF(DAY, pay.Period,getdate()) >= 0
48 AND pay.Deleted = 0
49
50
51 SELECT @ReturnValue = @ReturnValue+ COUNT(*)
52 FROM Bookings
53 INNER JOIN BookingGroups on bkgVenue = bkdVenue and bkgReference = bkdReference
54 INNER JOIN venue ON bkdVenue = venVenue
55 WHERE bkdPaidSoFar < bkdActCost
56 AND datediff(hh,getdate(),bkdBookDate + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':')) < (venPayWithinMemb )
57 AND bkdResourceType IN ('B')
58 AND bkgMembNo = @MemberNo
59
60 SELECT @ReturnValue = @ReturnValue+ COUNT(*)
61 FROM Enrolments
62 INNER JOIN Courses ON cenVenue = crsVenue AND cenGroupCode = crsGroupCode AND cenCode = crsCode
63 WHERE cenPaid < cenCost
64 AND crsCancelled = ''
65 AND ((DATEDIFF(MINUTE, cenEnrolDate, GETDATE())>0 AND cenCourseOrSes = 'S')
66 OR (DATEDIFF(MINUTE, crsFirstSession, GETDATE())>0 AND cenCourseOrSes = 'C'))
67 AND cenMembNo = @MemberNo
68
69 SELECT @ReturnValue = @ReturnValue+ COUNT(*) FROM Penalty.Header WHERE PersonNo = @MemberNo and PersonType = 'M' AND PaymentTransactionFK IS NULL AND IsWaived = 0
70
71
72
73 IF @IncludedLinked =1
74 BEGIN
75 SELECT @ReturnValue = @ReturnValue+ COUNT(*)
76 FROM dbo.TRP_PE_PaySchedule PAY
77 INNER JOIN TRP_PE_Detail DET ON DET.PersonPK = PAY.PersonFK
78
79 WHERE DET.PrimeReference = @MyPK
80 AND PAY.Amount <> 0 AND PAY.[Status] IN ('N','R','I')
81 AND DATEDIFF(DAY, pay.Period,getdate()) >= 0
82 AND pay.Deleted = 0
83
84
85 SELECT @ReturnValue = @ReturnValue+ COUNT(*)
86 FROM Bookings
87 INNER JOIN BookingGroups on bkgVenue = bkdVenue and bkgReference = bkdReference
88 INNER JOIN venue ON bkdVenue = venVenue
89 INNER JOIN Member ON memMemberNo = bkgMembNo
90 WHERE bkdPaidSoFar < bkdActCost
91 AND datediff(hh,getdate(),bkdBookDate + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':')) < (venPayWithinMemb )
92 AND bkdResourceType IN ('B')
93 AND memPrimeReference = @MemberNo
94
95
96 SELECT @ReturnValue = @ReturnValue+ COUNT(*)
97 FROM Enrolments
98 INNER JOIN Courses ON cenVenue = crsVenue AND cenGroupCode = crsGroupCode AND cenCode = crsCode
99 INNER JOIN member ON memMemberNo = cenMembNo
100 WHERE cenPaid < cenCost
101 AND crsCancelled = ''
102 AND ((DATEDIFF(MINUTE, cenEnrolDate, GETDATE())>0 AND cenCourseOrSes = 'S')
103 OR (DATEDIFF(MINUTE, crsFirstSession, GETDATE())>0 AND cenCourseOrSes = 'C'))
104 AND memPrimeReference = @MemberNo
105
106 SELECT @ReturnValue = @ReturnValue + count(*) FROM Penalty.Header
107 INNER JOIN member ON memMemberNo = PersonNo AND PersonType = 'M'
108 WHERE memPrimeReference = @MemberNo AND PaymentTransactionFK IS NULL AND IsWaived = 0
109
110
111 END
112
113
114 RETURN @ReturnValue
115END
116
117
118
119
120
121GO
122
123
124
125
126/****** Object: UserDefinedFunction [dbo].[MemberHasDebts] Script Date: 24/01/2017 11:33:42 ******/
127SET ANSI_NULLS ON
128GO
129
130SET QUOTED_IDENTIFIER ON
131GO
132
133
134
135IF EXISTS (SELECT * FROM SysObjects WHERE Name = 'MemberHasDebts' AND xType = 'FN')
136BEGIN
137
138 drop function MemberHasDebts
139
140END
141GO
142
143
144
145
146CREATE FUNCTION [dbo].[MemberHasDebts] (@MemberNo INTEGER,@IncludedLinked BIT)
147
148RETURNS BIT AS
149
150
151BEGIN
152
153
154 DECLARE @ReturnValue BIT
155 DECLARE @MyPK INT
156 SELECT @ReturnValue = 0
157
158 SELECT @MyPK = personPK FROM TRP_PE_Detail WHERE personno = @MemberNo
159
160 --own payschedule
161 IF EXISTS(
162 SELECT *
163 FROM dbo.TRP_PE_PaySchedule PAY
164 WHERE (PAY.PersonFK = @MyPK)
165 AND PAY.Amount <> 0 AND PAY.[Status] IN ('N','R','I')
166 AND DATEDIFF(DAY, pay.Period,getdate()) >= 0
167 AND pay.Deleted = 0)
168 BEGIN
169 SELECT @ReturnValue = 1
170 END
171
172 IF @ReturnValue = 0
173 BEGIN
174
175 IF EXISTS(
176 SELECT *
177 FROM Bookings
178 INNER JOIN BookingGroups on bkgVenue = bkdVenue and bkgReference = bkdReference
179 INNER JOIN venue ON bkdVenue = venVenue
180 WHERE bkdPaidSoFar < bkdActCost
181 AND datediff(hh,getdate(),bkdBookDate + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':')) < (venPayWithinMemb )
182 AND bkdResourceType IN ('B')
183 AND bkgMembNo = @MemberNo)
184 BEGIN
185 SELECT @ReturnValue = 1
186 END
187 END
188
189
190 IF @ReturnValue = 0
191 BEGIN
192
193 IF EXISTS(
194 SELECT *
195 FROM Enrolments
196 INNER JOIN Courses ON cenVenue = crsVenue AND cenGroupCode = crsGroupCode AND cenCode = crsCode
197 WHERE cenPaid < cenCost
198 AND crsCancelled = ''
199 AND ((DATEDIFF(MINUTE, cenEnrolDate, GETDATE())>0 AND cenCourseOrSes = 'S')
200 OR (DATEDIFF(MINUTE, crsFirstSession, GETDATE())>0 AND cenCourseOrSes = 'C'))
201 AND cenMembNo = @MemberNo)
202 BEGIN
203 SELECT @ReturnValue = 1
204 END
205 END
206
207 IF @ReturnValue = 0
208 BEGIN
209 IF EXISTS (SELECT * FROM Penalty.Header WHERE PersonNo = @MemberNo and PersonType = 'M' AND PaymentTransactionFK IS NULL AND IsWaived = 0)
210 BEGIN
211 SELECT @ReturnValue = 1
212 END
213 END
214
215
216
217
218 IF @IncludedLinked =1
219 BEGIN
220 --also included members we are prime refeernce for
221 --own payschedule
222 IF EXISTS(
223 SELECT *
224 FROM dbo.TRP_PE_PaySchedule PAY
225 INNER JOIN TRP_PE_Detail DET ON DET.PersonPK = PAY.PersonFK
226
227 WHERE DET.PrimeReference = @MyPK
228 AND PAY.Amount <> 0 AND PAY.[Status] IN ('N','R','I')
229 AND DATEDIFF(DAY, pay.Period,getdate()) >= 0
230 AND pay.Deleted = 0)
231 BEGIN
232 SELECT @ReturnValue = 1
233 END
234 IF @ReturnValue = 0
235 BEGIN
236
237 IF EXISTS(
238 SELECT *
239 FROM Bookings
240 INNER JOIN BookingGroups on bkgVenue = bkdVenue and bkgReference = bkdReference
241 INNER JOIN venue ON bkdVenue = venVenue
242 INNER JOIN Member ON memMemberNo = bkgMembNo
243 WHERE bkdPaidSoFar < bkdActCost
244 AND datediff(hh,getdate(),bkdBookDate + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':')) < (venPayWithinMemb )
245 AND bkdResourceType IN ('B')
246 AND memPrimeReference = @MemberNo)
247 BEGIN
248 SELECT @ReturnValue = 1
249 END
250 END
251
252 IF @ReturnValue = 0
253 BEGIN
254 IF EXISTS(
255 SELECT *
256 FROM Enrolments
257 INNER JOIN Courses ON cenVenue = crsVenue AND cenGroupCode = crsGroupCode AND cenCode = crsCode
258 INNER JOIN member ON memMemberNo = cenMembNo
259 WHERE cenPaid < cenCost
260 AND crsCancelled = ''
261 AND ((DATEDIFF(MINUTE, cenEnrolDate, GETDATE())>0 AND cenCourseOrSes = 'S')
262 OR (DATEDIFF(MINUTE, crsFirstSession, GETDATE())>0 AND cenCourseOrSes = 'C'))
263 AND memPrimeReference = @MemberNo)
264 BEGIN
265 SELECT @ReturnValue = 1
266 END
267 END
268
269 IF @ReturnValue = 0
270 BEGIN
271 IF EXISTS
272 (SELECT * FROM Penalty.Header
273 INNER JOIN member ON memMemberNo = PersonNo AND PersonType = 'M'
274
275 WHERE memPrimeReference = @MemberNo AND PaymentTransactionFK IS NULL AND IsWaived = 0)
276 BEGIN
277 SELECT @ReturnValue = 1
278 END
279 END
280
281 END
282
283
284 RETURN @ReturnValue
285END
286
287
288
289
290
291GO
292
293
294
295IF EXISTS (SELECT * FROM SysObjects WHERE Name = 'SPU_PE_GetMemberDebts' AND xType = 'P')
296BEGIN
297
298DROP PROCEDURE SPU_PE_GetMemberDebts
299
300END
301GO
302
303
304
305
306CREATE PROCEDURE [dbo].[SPU_PE_GetMemberDebts]
307/*
308returns all outstanding payments for a member
309JTP 9/3/2009 4211 added others sites implemenation to membership debts
310and @AllowCrossCentrePayments
311JTP 4545 book ahead policy does not appy to enrolments
312JTP 26/1/2012 external source and identifier information
313*/
314
315 @Site smallint,
316 @Member int,
317 @OtherSites bit =0,
318 @AllowCrossCentrePayments bit = 0
319
320AS
321
322-- Temporary table for linked members
323SELECT PRIME.PersonPK, PRIME.PersonNo, LINKED.PersonPK AS LinkedPersonPK, LINKED.PersonNo AS LinkedPersonNo
324INTO #LinkedMembers
325FROM TRP_PE_Detail PRIME JOIN TRP_PE_Detail LINKED
326ON LINKED.PrimeReference = PRIME.PersonPK
327WHERE PRIME.PersonNo = @Member
328
329
330--temporary table for all debts
331
332CREATE TABLE #Debts
333 (
334 --display columns
335 Pay varchar(13) NOT NULL,
336 [Debt On] varchar(50) NOT NULL,
337 DueDate datetime NOT NULL,
338 [Due] decimal(10,2) NOT NULL,
339 Paid decimal(10,2) NOT NULL,
340 Person int NOT NULL,
341 Description varchar(250) NOT NULL,
342
343 --columns to identify the three debt types
344 SiteNo smallint not null,
345 --pay schedule
346 PysSeqNo int ,
347 PysAddSeqNo int ,
348 pysItemSeqNo int ,
349 --bookings
350 bkdReference int,
351 bkdSequence smallint ,
352
353 --enrolments
354 cenGroupCode char(4) ,
355 cenCode char(8),
356 CenCourseOrSes char(1) ,
357 cenEnrolDate datetime,
358 SiteName varchar(60),
359 ExternalSource VARCHAR(2),
360 ExternalIdentifier INT,
361 penaltyPK int,
362 EnrolmentNo SMALLINT
363
364 ) ON [PRIMARY]
365
366
367--membership payments
368
369INSERT INTO #Debts
370SELECT CASE WHEN SiteNo <> @Site AND @AllowCrossCentrePayments = 0 THEN 'TLMS_NO_ENTRY' ELSE 'FALSE' END, --pay
371'Membership', --debt on
372 Period, --due date
373 CONVERT(DECIMAL(10,2), PAY.Amount), --due
374 CONVERT(DECIMAL(10,2), PAY.Original - PAY.Amount), --paid
375DET.PersonNo, --person
376CASE WHEN SiteNo <> @Site THEN LKP.[Description] + ' (' + SIT.Name + ')' ELSE LKP.[Description] END + ' - ' + ISNULL(PEL.ExtDescription,''), --description
377SiteNo, --siteno
378 MEM.SeqNo, --pysseqno
379 MEM.AddSeqNo, --pysaddseqno
380 PAY.ItemSeqNo, --pysitemseqno
381NULL, --bkdrefernce
382NULL,--bkdsequence
383 NULL, --cenGroupCode
384 NULL, --cenCode
385 NULL,--cenCourseOrSes
386 NULL, --cenEnrolDate
387SIT.Name, --sitename
388ISNULL(PEL.ExtSource,''), --externalsource
389ISNULL(PEL.extIdentifier,0), --external identifier
390NULL, --PenaltyPK
391NULL -- enrolmentNo
392FROM TRP_PE_Detail DET JOIN TRP_PE_PaySchedule PAY
393ON DET.PersonPK = PAY.PersonFK JOIN TRP_SD_Sites SIT
394ON DET.SiteFK = SIT.SitePK JOIN TRP_PE_Membership MEM
395ON PAY.PersonMembershipFK = MEM.PersonMembershipPK JOIN TRP_ME_PriceStructure PRI
396ON MEM.MembershipPriceStructureFK = PRI.MembershipPriceStructurePK JOIN TRP_ME_Lookup LKP
397ON PRI.MembershipLookupFK = LKP.MembershipLookupPK
398 LEFT JOIN PayscheduleExternalLink PEL
399 ON SIT.SiteNo = PEL.PaySiteNo AND DET.PersonNo = PEL.PayMemberNo
400 AND mem.SeqNo = PEL.PaySeqNo AND mem.AddSeqNo = PEL.PayAddSeqNo AND pay.ItemSeqNo = PEL.PayItemSeqNo
401WHERE (DET.PersonNo = @Member OR PersonNo IN (SELECT LinkedPersonNo FROM #LinkedMembers))
402AND PAY.Amount <> 0 AND PAY.[Status] IN ('N','P','R','I')
403AND (SiteNo = @Site Or @OtherSites = 1)
404AND PAY.Deleted = 0
405
406
407
408--INSERT INTO #Debts
409--SELECT
410-- pay = CASE WHEN pysSiteNo <> @Site AND @AllowCrossCentrePayments = 0
411-- THEN 'TLMS_NO_ENTRY'
412-- ELSE 'FALSE'
413-- END ,
414-- 'Membership',
415-- payschedule.pysPeriod,
416-- convert(decimal(10,2),payschedule.pysAmount),
417-- convert(decimal(10,2), payschedule.pysOriginal - payschedule.pysAmount),
418-- payschedule.pysMemberNo,
419-- Description = CASE WHEN pysSiteNo <> @Site
420-- THEN
421-- membershipitems.miMembershipItemDescr + ' (' + sdtName + ')'
422-- ELSE
423-- membershipitems.miMembershipItemDescr
424-- END + ' - ' + ISNULL(PEL.ExtDescription,'')
425
426-- ,
427-- --lookup columns
428-- pysSiteNo,
429-- pysSeqNo,PysAddSeqno,pysItemSeqNo,
430-- null,null,
431-- null,null,null,null,
432-- sdtName,
433-- ISNULL(PEL.ExtSource,''),
434-- ISNULL(PEL.extIdentifier,0),
435-- NULL, --PenaltyPK
436-- NULL -- enrolmentNo
437
438-- FROM
439-- payschedule
440-- INNER JOIN membershipitems ON payschedule.pysMembershipType = membershipitems.miMembershipItemCode
441-- INNER JOIN SiteDetails ON pysSiteNo = sdtSiteNo
442-- LEFT JOIN PayscheduleExternalLink PEL
443-- ON payschedule.pysSiteNo = PEL.PaySiteNo AND pysMemberNo = PEL.PayMemberNo
444-- AND pysSeqNo = PEL.PaySeqNo AND pysAddSeqNo = PEL.PayAddSeqNo AND pysItemSeqNo = PEL.PayItemSeqNo
445-- WHERE (pysMemberno = @Member OR pysMemberNo in (SELECT MemMemberNo FROM Member WHERE memPrimeReference = @Member))
446-- and pysAmount<>0 and pysstatus in ('N','P','R','I')
447-- and (pysSiteno = @Site Or @OtherSites = 1)
448
449--bookings
450INSERT INTO #Debts
451SELECT
452 pay = CASE WHEN bkdVenue <> @Site
453 THEN 'TLMS_NO_ENTRY'
454 ELSE 'FALSE'
455 END ,
456
457 'Booking',
458 Convert(char(17),dateadd(hh,-venPayWithinMemb,bkdBookDate + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':')),113) ,
459 CONVERT(decimal(9,2),bkdActCost - bkdPaidSoFar),
460 CONVERT(decimal(9,2),bkdPaidSoFar),
461 bkgMembNo,
462 [description] = CASE WHEN bkdVenue <> @Site
463 THEN
464 case bkdResourceType
465 when 'B' then lluDesc + isnull(' - ' + actDesc, ' ') + ' - ' + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':') + ' ' + Convert(char(12),bkdBookDate,113) + ' (' + sdtName + ')'
466 when 'H' then assResDesc +' (' + sdtName + ')'
467 when 'X' then sunDesc + ' (' + sdtName + ')'
468 when 'R' then stkDesc + ' (' + sdtName + ')'
469 when 'I' then RTRIM(criTitle) + ' ' + criForenames + ' ' + criSurname + ' (' + sdtName + ')'
470 END
471 ELSE
472 case bkdResourceType
473 when 'B' then lluDesc + isnull(' - ' + actDesc, ' ') + ' - ' + stuff(bkdstarttime,len(bkdstarttime) - 1,0,':') + ' ' + Convert(char(12),bkdBookDate,113)
474 when 'H' then assResDesc
475 when 'X' then sunDesc
476 when 'R' then stkDesc
477 when 'I' then RTRIM(criTitle) + ' ' + criForenames + ' ' + criSurname
478 END
479
480 END ,
481 --lookup columns
482 bkdVenue,
483 null,null,null,
484 bkdReference,bkdSequence,
485 null, null,null,null,
486 sdtName,
487 '',0,
488 NULL, --penaltyPK
489 NULL -- enrolmentNo
490
491 From Bookings
492 Inner join BookingGroups on
493 bkgVenue = bkdVenue and bkgReference = bkdReference
494 INNER JOIN SiteDetails on bkgVenue = sdtSiteNo
495 Inner join Venue on
496 venVenue = bkdVenue
497 left outer join locationsLookup on bkdVenue = lluVenue AND bkdLocation = lluCode
498 left outer join activity on bkdActivity = actCode
499 left outer join assets on bkdVenue = assVenue AND bkdResCode = assResNo
500 left outer join Sundry on bkdVenue = sunVenue AND bkdResCode = sunCode
501 left outer join Stock on bkdVenue = stkVenue AND bkdResCode = stkCode
502 left outer join CrsInstructor on bkdVenue = criVenue AND bkdResCode = criInstructor
503 where (bkdPaidSoFar < bkdActCost)
504 and bkdResourceType IN ('B')
505 and (bkgMembNo = @Member
506 or bkgMembNo IN (SELECT MemMemberNo FROM Member WHERE memPrimeReference = @Member)
507 )
508 and (bkdVenue = @Site Or @OtherSites = 1)
509
510--enrolments
511INSERT INTO #Debts
512SELECT
513 pay = CASE WHEN cenVenue <> @Site
514 THEN 'TLMS_NO_ENTRY'
515 ELSE 'FALSE'
516 END ,
517 case cenCourseOrSes
518 when 'C' then 'Course'
519 when 'S' then 'Session' end,
520 case cenCourseOrSes
521 --JTP 4545 book ahead policy does not appy to enrolments
522 when 'C' then crsFirstSession--Convert(char(12),dateadd(hh,-venPayWithinMemb,crsFirstSession),113)
523 when 'S' then cenEnrolDate--Convert(char(12),dateadd(hh,-venPayWithinMemb,cenEnrolDate),113)
524 end,
525 [Amount Due] = cenCost - cenPaid,
526 [Amount Paid] = cenPaid,
527 cenMembNo,
528
529 [description] = CASE WHEN cenVenue <> @Site
530 THEN
531 case cenCourseOrSes
532 when 'C' then left(Convert(Varchar, crsFirstSession, 3), 5) + ' ' + left(Convert(Varchar, crsFirstSession, 8), 5) + ' - ' + rtrim(crsBriefDesc) + ' (' + sdtName + ')'
533 when 'S' then left(Convert(Varchar, cenEnrolDate, 3), 5) + ' ' + left(Convert(Varchar, cenEnrolDate, 8), 5) + ' - ' + rtrim(crsBriefDesc) + ' (' + sdtName + ')'
534 END
535 ELSE
536 case cenCourseOrSes
537 when 'C' then left(Convert(Varchar, crsFirstSession, 3), 5) + ' ' + left(Convert(Varchar, crsFirstSession, 8), 5) + ' - ' + crsBriefDesc
538 when 'S' then left(Convert(Varchar, cenEnrolDate, 3), 5) + ' ' + left(Convert(Varchar, cenEnrolDate, 8), 5) + ' - ' + crsBriefDesc
539 end
540 END,
541
542 --lookup columns
543 cenVenue,
544 null,null,null,
545 null,null,
546 cenGroupCode,cenCode,CenCourseOrSes,cenEnrolDate,
547 sdtName,
548 '',0,
549 NULL, --IsPenalty
550 cenEnrolmentNo -- enrolmentNo
551 From Enrolments
552 Inner join Courses on crsVenue = cenVenue and crsGroupCode = cenGroupCode and crsCode = cenCode and crsCourseOrSes = cenCourseOrSes
553 Inner join Venue on venVenue = cenVenue
554 inner join SiteDetails on sdtSiteNo = cenVenue
555 where cenPaid < cenCost and (cenMembNo = @Member
556 OR cenMembNo in (SELECT memMemberNo From Member Where memPrimeReference = @Member)
557 )
558 and (cenVenue = @Site Or @OtherSites = 1)
559
560
561 --add penalties
562 INSERT INTO #debts EXEC SPU_PE_GetMemberPenalties @Site,@Member,@OtherSites ,@AllowCrossCentrePayments
563
564SELECT
565 Pay ,
566 [Status] =
567 CASE
568 WHEN datediff(MINUTE,dueDate ,getdate()) > 0 --due date has passed
569 THEN 'TLMS_TRAFFIC_LIGHT_3_RED'
570 WHEN datediff(DAY,dueDate ,getdate()) = 0 --due today
571 THEN 'TLMS_TRAFFIC_LIGHT_2_AMBER'
572 ELSE
573 'TLMS_TRAFFIC_LIGHT_1_GREEN' --future payment
574 END ,
575 [Debt On],
576 convert(char(10),DueDate,103) as [Due Date],
577 [Due],
578 Paid ,
579 Person,
580 [Description],
581
582 SiteNo,
583 PysSeqNo ,
584 PysAddSeqNo ,
585 pysItemSeqNo ,
586 bkdReference ,
587 bkdSequence ,
588
589 cenGroupCode ,
590 cenCode,
591 CenCourseOrSes ,
592 cenEnrolDate ,
593 SiteName,
594 ExternalSource,
595 ExternalIdentifier,
596 penaltyPK,
597 EnrolmentNo
598 from #Debts ORDER BY duedate ASC
599drop table #Debts
600
601
602
603
604
605GO