· 9 years ago · Dec 28, 2016, 05:26 PM
1UPDATE o
2SET o.EffectiveDate = '1/1/1900'
3 , o.EffectiveEnd = '9/9/9999'
4FROM dbo.BillingCPTcode AS o
5WHERE o.EffectiveDate IS NULL AND o.EffectiveEnd IS null
6
7
8UPDATE o
9SET o.EffectiveEnd = '12/31/2016'
10FROM dbo.BillingCPTcode AS o
11WHERE o.Code IN ('97001', '97003')
12
13INSERT INTO dbo.BillingCPTcode
14 ( ID
15 , Code
16 , Minutes
17 , Service
18 , DateModified
19 , ModifiedBy
20 , PT
21 , OT
22 , ST
23 , ValidCPT
24 , CPTName
25 , ReductionEligible
26 , Eval
27 , HH
28 , GroupTx
29 , ReEval
30 , HasModifier
31 , Modifier
32 , TLCEfficiencyUnits
33 , EffectiveDate
34 , EffectiveEnd
35 )
36SELECT NEWID()
37 , c.Code
38 , o.Minutes
39 , o.Service
40 , GETDATE()
41 , '9207'
42 , o.PT
43 , o.OT
44 , o.ST
45 , CASE WHEN c.Code IN ('97161', '97165') THEN 1 ELSE 0 end
46 , c.Stmt
47 , o.ReductionEligible
48 , o.Eval
49 , o.HH
50 , o.GroupTx
51 , o.ReEval
52 , o.HasModifier
53 , o.Modifier
54 , o.TLCEfficiencyUnits
55 , '1/1/2017'
56 , '9/9/9999'
57FROM dbo.BillingCPTcode AS o
58CROSS APPLY (
59SELECT '97161' AS Code, 'Physical therapy evaluation: low complexity' AS Stmt
60WHERE o.Code = '97001'
61 UNION ALL
62SELECT '97162', 'Physical therapy evaluation: moderate complexity'
63WHERE o.Code = '97001'
64 UNION ALL
65SELECT '97163', 'Physical therapy evaluation: high complexity'
66WHERE o.Code = '97001'
67 UNION ALL
68SELECT '97165', 'Occupational therapy evaluation: low complexity'
69WHERE o.Code = '97003'
70 UNION ALL
71SELECT '97166', 'Occupational therapy evaluation: moderate complexity'
72WHERE o.Code = '97003'
73 UNION ALL
74SELECT '97167', 'Occupational therapy evaluation: high complexity'
75WHERE o.Code = '97003'
76) c
77WHERE o.Code IN ('97001', '97003')
78
79GO
80
81
82PRINT 'modify the POC populator (used during SOC, ICD10 control on documentation/cases/the uc "poc2")'
83SET QUOTED_IDENTIFIER OFF
84SET ANSI_NULLS OFF
85GO
86-- =============================================
87-- Author: Tony, Anthony
88-- Create date: 4/8/2016
89-- Description: Gets the list of allowable CPTs;
90-- this got super complicated over the years so
91-- the latest iteration takes a stab at reducing
92-- the complexity. The cte named "allowableCPTs"
93-- is a sleak inline drilldown of cpt "exceptions".
94-- The new structure of the table lends itself
95-- well to the logic flow of determination, reducing
96-- the overhead of an otherwise brute-force scan
97-- by about 20%, while the table itself is roughly
98-- 5% of the original's size.
99-- =============================================
100ALTER FUNCTION [Billing].[fnCaseValidCPTsWithoutPOC]
101(
102 @FacID varchar(3),
103 @DARCaseID uniqueidentifier,
104 @DARDate datetime
105)
106RETURNS
107@CPTs TABLE
108(
109 CPT varchar(10),
110 [Minutes] int,
111 Service bit,
112 Eval bit,
113 ReEval bit,
114 HH bit,
115 HasModifier bit,
116 Modifier varchar(10)
117)
118AS
119BEGIN
120 -- Fill the table variable with the rows for your result set
121
122 DECLARE @CrosswalkName varchar(100) = (select Billing.fnCrosswalkName(@FacID,@DARCaseID,@DARDate));
123 --Abandon hope, all ye who enter here.
124 with crosswalkGroups as
125 (
126 SELECT cw.[Reversed], g.CrosswalkGroupID, cw.EffectiveDate, cw.EffectiveEnd, g.IsICD10
127
128 FROM Billing.Crosswalk cw
129 INNER JOIN Billing.CrosswalkGroup g ON g.CrosswalkID = cw.CrosswalkID
130 INNER JOIN DARCaseID c ON c.DARCaseID = @DARCaseID AND c.SOCDate BETWEEN cw.EffectiveDate AND cw.EffectiveEnd
131 AND g.IsICD10 = CASE WHEN c.SOCDate >= dbo.fnGetGlobalConstantDate('ICD10Start') THEN 1 ELSE 0 END
132 WHERE cw.Intermediary = @CrosswalkName
133
134 ),
135 --I'm hoping instead of an insane exists list, I can instead manufacture an exception list using rules rather than brute force
136 --Subsequently, I'm hoping this is faster than bruting through 2 million rows.
137 allowableCPTs AS (
138 SELECT o.*
139
140 --start with all the codes
141 FROM dbo.BillingCPTcode o
142 WHERE NOT EXISTS (SELECT * FROM CrosswalkGroups g
143 INNER JOIN Billing.CrosswalkCPT cp ON cp.CrosswalkGroupID = g.CrosswalkGroupID
144 AND cp.CrosswalkCPT = o.Code)
145 AND @DARDate BETWEEN o.EffectiveDate AND o.EffectiveEnd
146 UNION
147 --i9s
148 SELECT o.*
149 FROM CrosswalkGroups g
150 INNER JOIN Billing.CrosswalkICD i ON i.CrosswalkGroupID = g.CrosswalkGroupID
151 INNER JOIN Billing.CrosswalkCPT u ON u.CrosswalkGroupID = g.CrosswalkGroupID
152 INNER JOIN BillingCPTCode o ON o.code = u.CrosswalkCPT
153 WHERE g.Reversed = 0 AND g.IsICD10 = 0 and EXISTS (SELECT * FROM CaseICD9 i9 WHERE i9.CaseID = @DARCaseID AND i9.ICD = i.CrosswalkICD)
154 AND @DARDate BETWEEN o.EffectiveDate AND o.EffectiveEnd
155 UNION
156 --i10s
157 SELECT o.*
158 FROM CrosswalkGroups g
159 INNER JOIN Billing.CrosswalkICD i ON i.CrosswalkGroupID = g.CrosswalkGroupID
160 INNER JOIN Billing.CrosswalkCPT u ON u.CrosswalkGroupID = g.CrosswalkGroupID
161 INNER JOIN BillingCPTCode o ON o.code = u.CrosswalkCPT
162 WHERE g.Reversed = 0 AND g.IsICD10 = 1 AND EXISTS (SELECT * FROM CaseICD10 i10 WHERE i10.CaseID = @DARCaseID AND i10.ICD = i.CrosswalkICD)
163 AND @DARDate BETWEEN o.EffectiveDate AND o.EffectiveEnd
164 UNION
165 --i9s reverse
166 SELECT o.*
167 FROM CrosswalkGroups g
168 INNER JOIN Billing.CrosswalkICD i ON i.CrosswalkGroupID = g.CrosswalkGroupID
169 INNER JOIN Billing.CrosswalkCPT u ON u.CrosswalkGroupID = g.CrosswalkGroupID
170 INNER JOIN BillingCPTCode o ON o.code = u.CrosswalkCPT
171 WHERE g.Reversed = 1 AND g.IsICD10 = 0 and NOT EXISTS (SELECT * FROM CaseICD9 i9 WHERE i9.CaseID = @DARCaseID AND i9.ICD = i.CrosswalkICD)
172 AND @DARDate BETWEEN o.EffectiveDate AND o.EffectiveEnd
173 UNION
174 --i10s reverse
175 SELECT o.*
176 FROM CrosswalkGroups g
177 INNER JOIN Billing.CrosswalkICD i ON i.CrosswalkGroupID = g.CrosswalkGroupID
178 INNER JOIN Billing.CrosswalkCPT u ON u.CrosswalkGroupID = g.CrosswalkGroupID
179 INNER JOIN BillingCPTCode o ON o.code = u.CrosswalkCPT
180 WHERE g.Reversed = 1 AND g.IsICD10 = 1 AND NOT EXISTS (SELECT * FROM CaseICD10 i10 WHERE i10.CaseID = @DARCaseID AND i10.ICD = i.CrosswalkICD)
181 AND @DARDate BETWEEN o.EffectiveDate AND o.EffectiveEnd
182 )
183
184
185
186 INSERT INTO @CPTs
187 SELECT
188 --c.DARCaseID,
189 --added to fuel some of the logic in the next query
190 --c.Discipline,
191 --c.Payor,
192 --end addition
193 cpt.Code as CPT,
194 cpt.[Minutes],
195 cpt.Service,
196 cpt.Eval,
197 cpt.ReEval,
198 cpt.HH,
199 cpt.HasModifier,
200 cpt.Modifier
201 -- a lot of logic from this query moved up into the "allowableCPTs" block, which should hopefully speed things up some.
202 FROM allowableCPTs cpt
203 inner join DARCaseID c on c.DARCaseID = @DARCaseID
204 outer apply
205 (
206 select top 1 *
207 from PtInsurance i
208 where
209 i.PatientID = c.DARPtID
210 and i.InsuranceLevel = 'Primary'
211 and i.EffectiveDate <= @DARDate
212 order by i.EffectiveDate desc
213 ) as i
214 inner join Facility f on f.FacilityID = @FacID
215 where c.DARCaseID = @DARCaseID
216 and ((c.Discipline = 'PT' and cpt.PT = 1) or (c.Discipline = 'OT' and cpt.OT = 1) or (c.Discipline = 'ST' and cpt.ST = 1))
217 --this is some hard coded bunk right here
218 and (cpt.Code not in ('97150','92508') or c.Payor in ('B','Part B Outpatient') or f.FacilityType in ('TLC','ALF'))
219 --all this jazz excludes a bunch of stuff - such as CPTs with zero rates, and CPTS that have been charged more than the limit
220 --specified in the CrosswalkCPTLimit table, which surprisingly, doesn't have a lot to do with the crosswalk
221 --tony figured half this shit out, it makes my head hurt.
222 and not exists
223 (
224 select
225 u.CPT
226 from DARTx t
227 inner join DARcpt u on u.DARTxID = t.DARTxID and u.CPT = cpt.Code
228 inner join CrosswalkCPTLimit limit on limit.CPT = cpt.Code and limit.Intermediary = @CrosswalkName
229 where t.DARCaseID = c.DARCaseID
230 group by u.CPT,limit.CaseLimit,limit.WeeklyLimit,limit.VisitLimit
231 having
232 SUM(u.units) >= limit.CaseLimit --or
233 or SUM(case when t.TxDate between DATEADD(DAY, -1 * (DATEPART(dw,@DARDate) - 1), @DARDate) and DATEADD(DAY, (DATEPART(dw,@DARDate) - 1), @DARDate) then u.Units else 0 end) >= limit.WeeklyLimit-- or
234 or COUNT(distinct t.TxDate) >= limit.VisitLimit
235 union
236 --check for zero rate, which we do not apply to HH cpt codes - the HH codes get filtered by insurance
237 select top 1 i.CPT
238 from
239 dbo.fnMedFeeSchedFacility(@FacID,@DARDate) s
240 inner join MedFeeSchedItems i on i.CPT = cpt.Code and i.MedFeeScheduleID = s.MedFeeSchedID
241 where (i.Rate = 0 and c.Payor not like 'HH%' and f.FacilityType not in ('TLC','ALF'))
242 union
243 --MOST RECENT ADDITION 6/10/2014
244 --This ensures that for TMC, if a payer source is listed as CPT Specific, that it must have a rate row
245 --specifically, this seems to affect HH payers in TMC, and would otherwise give them a bigger list than they need.
246 select top 1 cpt.Code
247 from billingContract bc
248 cross apply (select top 1 * from BillingPayerSource s where s.Name = c.Payor and bc.ID = s.ContractID and s.EffectiveDate <= @DARDate order by s.EffectiveDate desc) as s
249 where bc.Name = f.CurrentContract and f.FacilityType not in ('TLC','ALF') and s.method = 'CPT Specific'
250 and not exists(select * from BillingCPTRate r where r.PayerSourceID = s.ID and r.CPTCode = cpt.Code)
251 )
252 and not exists (
253 select * from Billing.MasterPayorBlockedCPT bi where bi.CPT = cpt.Code and bi.MasterPayorID = i.MasterPayorID
254 )
255 RETURN
256END
257
258
259GO
260
261
262
263PRINT 'modify the non-POC CPT filter (the one you get normally)'
264SET QUOTED_IDENTIFIER OFF
265SET ANSI_NULLS OFF
266GO
267-- =============================================
268-- Author: <Author,,Name>
269-- Create date: <Create Date,,>
270-- Description: <Description,,>
271-- =============================================
272ALTER FUNCTION [Billing].[fnCaseValidCPTs]
273(
274 @FacID varchar(3),
275 @DARCaseID uniqueidentifier,
276 @DARDate datetime
277)
278RETURNS TABLE
279AS
280RETURN
281(
282 WITH q as (
283 SELECT
284 c.DARCaseID,
285 --added to fuel some of the logic in the next query
286 c.Discipline,
287 c.Payor,
288 i.InsuranceName,
289 --end addition
290 cpt.Code as CPT,
291 cpt.[Minutes],
292 cpt.Service,
293 cpt.Eval,
294 cpt.ReEval,
295 cpt.HH,
296 cpt.HasModifier,
297 cpt.Modifier
298 FROM DARCaseID c
299 inner join DARPatient p on p.DARPtID = c.DARPtID
300 outer apply
301 (
302 select top 1 i.MasterPayorID, i.InsuranceName
303 from PtInsurance i
304 where
305 i.PatientID = c.DARPtID
306 and i.InsuranceLevel = 'Primary'
307 and i.EffectiveDate <= @DARDate
308 order by i.EffectiveDate desc
309 ) as i
310 inner join dPOC poc on poc.CaseID = c.DARCaseID
311 inner join BillingCPTcode cpt on cpt.Code = poc.CPT
312 --note here, with a poc, we're looking to make sure the dar date is in the cpt window, new as of 12/13/2016
313 AND @DARDate BETWEEN cpt.EffectiveDate AND cpt.EffectiveEnd
314 where
315 c.DARCaseID = @DARCaseID
316 and @DARDate between poc.EffDate and poc.EndDate
317 and ((c.Discipline = 'PT' and cpt.PT = 1) or (c.Discipline = 'OT' and cpt.OT = 1) or (c.Discipline = 'ST' and cpt.ST = 1))
318 and not exists (select 1 from Billing.MasterPayorBlockedCPT bi where bi.CPT = cpt.Code and bi.MasterPayorID = i.MasterPayorID)
319 )
320 SELECT
321 distinct
322 q.CPT,
323 q.Minutes,
324 q.Service,
325 q.Eval,
326 q.ReEval,
327 q.HH,
328 q.HasModifier,
329 q.Modifier
330 FROM q
331 --all this jazz excludes a bunch of stuff - such as CPTs with zero rates, and CPTS that have been charged more than the limit
332 --specified in the CrosswalkCPTLimit table, which surprisingly, doesn't have a lot to do with the crosswalk
333 --tony figured half this shit out, it makes my head hurt.
334 where not exists
335 (
336 select
337 u.CPT
338 from DARTx t
339 inner join DARcpt u on u.DARTxID = t.DARTxID and u.CPT = q.CPT
340 inner join Facility f on f.FacilityID = @FacID
341 --added to join against existing crosswalks so it will exclude application of CPT Limit
342 --when crosswalk for that payer/discipline/intermediary combination doesn't exist
343 inner join billingContract bc on bc.Name = f.CurrentContract
344 cross apply (select top 1 case when q.Discipline = 'PT' then s.PTCrosswalk
345 when q.Discipline = 'OT' then s.OTCrosswalk
346 else s.STCrosswalk END AS Intermediary
347 from BillingPayerSource s where (s.Name = q.Payor or (f.FacilityType in ('TLC','ALF') and LEFT(q.InsuranceName,8) = 'Medicare' and s.Name = 'B'))and bc.ID = s.ContractID and s.EffectiveDate <= @DARDate order by s.EffectiveDate desc) as s
348 inner join CrosswalkCPTLimit limit on limit.CPT = q.CPT and limit.Intermediary = s.Intermediary
349 where t.DARCaseID = q.DARCaseID
350 group by u.CPT,limit.CaseLimit,limit.WeeklyLimit,limit.VisitLimit, limit.DailyLimit
351 having
352 SUM(u.units) >= limit.CaseLimit --or
353 or SUM(case when t.TxDate between DATEADD(DAY, -1 * (DATEPART(dw,@DARDate) - 1), @DARDate) and DATEADD(DAY, (DATEPART(dw,@DARDate) - 1), @DARDate) then u.Units else 0 end) >= limit.WeeklyLimit-- or
354 or COUNT(distinct t.TxDate) >= limit.VisitLimit
355 --added 8/18/2015, where has this been?? - Tony
356 OR SUM(CASE WHEN t.txDate = @DARDate THEN u.units ELSE 0 END) >= limit.DailyLimit
357 union
358 select top 1 i.CPT
359 from
360 dbo.fnMedFeeSchedFacility(@FacID,@DARDate) s
361 inner join MedFeeSchedItems i on i.CPT = q.CPT and i.MedFeeScheduleID = s.MedFeeSchedID
362 inner join Facility f on f.FacilityID = @FacID
363 where (i.Rate = 0 and q.Payor not like 'HH%' and f.FacilityType not in ('TLC','ALF') )
364 )
365
366)
367GO