· 7 years ago · Nov 17, 2018, 03:12 PM
1USE [QUN]
2GO
3/****** Object: StoredProcedure [dbo].[Archive_AutoArchivePatients] Script Date: 09/10/2012 10:47:30 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [dbo].[Archive_AutoArchivePatients]
9AS
10
11DECLARE @Setting_PaymentDateEnabled as tinyint
12DECLARE @Setting_ChargeDateEnabled as tinyint
13DECLARE @Setting_VisitDateEnabled as tinyint
14DECLARE @Setting_DaysBackToArchive as numeric
15DECLARE @Calculated_LastDate as varchar(100)
16DECLARE @Loop_Row as numeric
17DECLARE @Loop_Patid as numeric
18DECLARE @Loop_RowCount as numeric
19
20SET XACT_ABORT ON
21SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
22
23SET NOCOUNT ON
24
25-- CWK 08/19/2008 Fixed last Date setting
26-- 08/19/2008 - Check if the visit date is not a date if so then set to today so it wont archive
27-- 09/11/2008 - Make sure their is at least a visit on the account prior to archiving, so manual entries aren't immediately pushed to archiving
28-- 02/26/2009 - Ben Grauer - T# 18822 Added condition for PatDownloadTmp - It could potentially move to Patient History after archive call - throwing off non-post.
29-- 11/04/2011 - Will Huck - T# 41990 - Do not archive when an unposted ERS Payment is found.
30-- 06/12/2012 - Charles Kauffman - T# 48320 - Tuning -- Use Temp Table; Remove Unnecessary Casing on Setting pulls; Remove Duplicate patdownloadtmp check; remove ABS; Remove IN; Remove unnecessary CONVERTS
31
32SET @Setting_PaymentDateEnabled = 0
33SET @Setting_ChargeDateEnabled = 0
34SET @Setting_VisitDateEnabled = 0
35SET @Setting_DaysBackToArchive = 365
36SET @Calculated_LastDate = '01/01/1900'
37SET @Loop_Row = 0
38SET @Loop_Patid = 0
39SET @Loop_RowCount = 0
40
41-- Drop the temp table
42IF EXISTS (SELECT top 1 1 FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID('tempdb.dbo.#Temp_ArchiveTable'))
43 DROP TABLE #Temp_ArchiveTable
44
45-- TEMP TABLE
46CREATE TABLE #Temp_ArchiveTable (
47 RowID INT IDENTITY(1, 1) primary key clustered,
48 patID numeric)
49
50IF EXISTS(SELECT * FROM dbo.Setting WHERE Setting = 'AutoArchive_Enabled' AND valueString = 'TRUE')
51BEGIN
52
53 IF EXISTS(SELECT * FROM dbo.Setting WHERE Setting = 'AutoArchive_PMTDateEnabled' AND valueString = 'TRUE')
54 BEGIN
55 SET @Setting_PaymentDateEnabled = 1
56 END
57
58 IF EXISTS(SELECT * FROM dbo.Setting WHERE Setting = 'AutoArchive_CHGDateEnabled' AND valueString = 'TRUE')
59 BEGIN
60 SET @Setting_ChargeDateEnabled = 1
61 END
62
63 IF EXISTS(SELECT * FROM dbo.Setting WHERE Setting = 'AutoArchive_VSTDateEnabled' AND valueString = 'TRUE')
64 BEGIN
65 SET @Setting_VisitDateEnabled = 1
66 END
67
68 IF EXISTS(SELECT * FROM dbo.Setting WHERE Setting = 'AutoArchive_Days' AND IsNumeric(valueString) = 1)
69 BEGIN
70 SELECT @Setting_DaysBackToArchive = Cast(ValueString as numeric) FROM dbo.Setting WHERE Setting = 'AutoArchive_Days'
71 END
72
73 -- Handle Just IN case
74 IF NOT (@Setting_DaysBackToArchive <= 0)
75 BEGIN
76
77 SELECT @Calculated_LastDate = Cast(Convert(varchar(10),GetDate() - @Setting_DaysBackToArchive,101) as datetime)
78
79 -- SELECT 1000 to not bog down the billing engine \ the system will catch up as time progresses
80 INSERT #Temp_ArchiveTable
81 SELECT TOP 1000 patID FROM dbo.Patients WHERE
82 patid IN
83 (
84 -- Visit must be present
85 SELECT patid FROM dbo.patienthistory
86 ) AND
87 patID NOT IN
88 (
89 -- NO BALANCE > 0 or < 0
90 SELECT patID FROM dbo.patientProcedure WHERE (patBalance > 0 OR patBalance < 0)
91 )
92 AND
93 patid NOT IN
94 (
95 -- NOT IN Download Queue
96 SELECT patID from patDownloadTmp
97 )
98 AND
99 patid NOT IN
100 (
101 -- NOT recently reinstated
102 SELECT
103 patID
104 from
105 dbo.notes
106 where
107 notes = 'Patient Account Reinstated From Archive' AND
108 dateCreated > @Calculated_LastDate
109 )
110 AND
111 patid NOT IN
112 (
113 -- NOT IN Charge Central
114 SELECT patID from DownloadPostCharge
115 )
116 AND
117 patid NOT IN
118 (
119 -- NOT Recently Downloaded
120 SELECT da_patid FROM dbo.Download_Audit WHERE da_DateStamp > @Calculated_LastDate
121 )
122 AND
123 CAST(patid AS VARCHAR(20)) NOT IN
124 (
125 -- NO Pending ERS Payment
126 SELECT Patacct FROM dbo.ERSDetail WHERE (PostFlag = 'ERS' OR PostFlag = 'EXCEPTION')
127 )
128 AND
129 (
130 -- Payment Date Not Greater than given Date
131 @Setting_PaymentDateEnabled = 0
132 OR
133 (@Setting_PaymentDateEnabled = 1 AND patID NOT IN
134 (
135 SELECT patid FROM dbo.patPayment WHERE PostDate > @Calculated_LastDate OR
136 PaymentDate > @Calculated_LastDate
137 ))
138 )
139 AND
140 (
141 -- Charge Date Not Greater than given Date
142 @Setting_ChargeDateEnabled = 0
143 OR
144 (@Setting_ChargeDateEnabled = 1 AND patID NOT IN
145 (
146 SELECT patid FROM dbo.patientProcedure WHERE patPostDate > @Calculated_LastDate
147 ))
148 )
149 AND
150 (
151 -- Charge Date Not Greater than given Date
152 @Setting_VisitDateEnabled = 0
153 OR
154 (@Setting_VisitDateEnabled = 1 AND patID NOT IN
155 (
156 SELECT patid FROM dbo.patientHistory WHERE Cast(CASE WHEN isDate(patVisitDate) = 0 THEN GETDATE() ELSE patvisitDate END as datetime) > @Calculated_LastDate
157 ))
158 )
159
160 SELECT @Loop_RowCount = @@ROWCOUNT
161
162 SET @Loop_Row = 1
163
164 WHILE @Loop_Row <= @Loop_RowCount
165 BEGIN
166
167 SET @Loop_Patid = 0
168
169 SELECT
170 @Loop_Patid = Patid
171 FROM
172 #Temp_ArchiveTable
173 WHERE
174 RowID = @Loop_Row
175
176 EXEC dbo.Archive_ArchivePatient @Loop_Patid, 'Billing Engine'
177
178 SET @Loop_Row = @Loop_Row + 1
179 END
180
181 END
182
183END
184
185--GO