· 8 years ago · Jul 11, 2018, 09:40 PM
1--fnGetNumberofTiersinGroup
2
3declare
4 @businessUnitID int = 173,
5 @episodeID int = 86815,
6 @serviceTypeID int = 62
7
8
9 /*
10
11 Updated: 2017-08-21 by Asmitto
12 Description: Add the IsRequestOfNeed flag
13
14 */
15
16 BEGIN
17 DECLARE @businessUnitStartTime datetime;
18 declare @IsAuthorizationRounding bit;
19 declare @MinutesToRoundUpAfter int;
20 declare @NumberOfMinutes int;
21 declare @EquivalentNumberOfUnits int;
22
23 SELECT @businessUnitStartTime = StartTimeOfDay
24 FROM dbo.BusinessUnits (nolock)
25 WHERE BusinessUnitID = @businessUnitID;
26
27
28 --get rounding configuration for episode + service type
29 select
30 gbr.IsAuthorizationRounding,
31 gu.MinutesToRoundUpAfter,
32 gu.NumberOfMinutes,
33 gu.EquivalentNumberOfUnits
34 --,gph.EffectiveBeginDate
35 --,gph.EffectiveEndDate
36 into #tmpBillingConfigurationForAuth
37 FROM
38 EpisodeServices es INNER JOIN
39 GroupsInPlans gip ON es.PlanGroupID = gip.PlanGroupID inner join
40 GroupBillingRules gbr (nolock) ON
41 gbr.BusinessUnitID = es.BusinessUnitID
42 AND gbr.PlanGroupID = es.PlanGroupID
43 AND gbr.IsInValid = 0
44 and gbr.EffectiveEndDate is null inner join
45 GroupServiceTypes gst (nolock) ON
46 es.BusinessUnitID = gst.BusinessUnitID
47 AND es.PlanGroupID = gst.PlanGroupID
48 AND es.ServiceTypeID = gst.ServiceTypeID INNER JOIN
49 GroupPropertiesHistory gph (nolock) ON
50 gst.BusinessUnitID = gph.BusinessUnitID
51 AND gst.GroupServiceTypeID = gph.GroupServiceTypeID
52 and gph.isinitialvalue = 0
53 and gph.isinvalid = 0 inner join
54 Groupunits gu on
55 gu.GroupPropertiesSetID = gph.GroupPropertiesSetID
56
57 WHERE
58 es.EpisodeID = @episodeID
59 AND es.ServiceTypeID = @serviceTypeID
60
61 --if there's more than one value, then @IsAuthorizationRounding = 0
62
63 --if (select count(*) from #tmpBillingConfigurationForAuth group by IsAuthorizationRounding, MinutesToRoundUpAfter, NumberOfMinutes, EquivalentNumberOfUnits) > 1
64 if
65 (
66 select count (*) from
67 (
68 select IsAuthorizationRounding, MinutesToRoundUpAfter, NumberOfMinutes, EquivalentNumberOfUnits
69 from #tmpBillingConfigurationForAuth
70 group by IsAuthorizationRounding, MinutesToRoundUpAfter, NumberOfMinutes, EquivalentNumberOfUnits
71 ) as x
72 ) > 1
73 begin
74 select @IsAuthorizationRounding = 0
75 end
76 else
77 begin
78 select
79 @IsAuthorizationRounding = IsAuthorizationRounding,
80 @MinutesToRoundUpAfter = MinutesToRoundUpAfter,
81 @NumberOfMinutes = NumberOfMinutes,
82 @EquivalentNumberOfUnits = EquivalentNumberOfUnits
83 from
84 #tmpBillingConfigurationForAuth
85 end
86
87 DECLARE @authGroupIDs TABLE(PlanGroupAuthorizationGroupID int, PlanGroupID int);
88
89 --Get all the Authorization Groups, for the episodeid and ServiceTypeID combo
90 INSERT @authGroupIDs
91 SELECT DISTINCT gst.PlanGroupAuthorizationGroupID,
92 g.PlanGroupID
93 FROM PlanGroupAuthGroupServiceTypes gst INNER JOIN
94 dbo.PlanGroupAuthorizationGroups g (nolock) ON g.PlanGroupAuthorizationGroupID = gst.PlanGroupAuthorizationGroupID
95 AND g.IsDeleted = 0 INNER JOIN
96 dbo.EpisodeServices es (nolock) ON es.PlanGroupID = g.PlanGroupID
97 AND gst.ServiceTypeID = es.ServiceTypeID INNER JOIN
98 dbo.EpisodePropertiesHistory eph (nolock) ON eph.EpisodePropertySetID = es.EpisodePropertySetID
99 AND eph.IsInvalid = 0
100 WHERE es.EpisodeID = @episodeID
101 AND es.ServiceTypeID = @serviceTypeID
102 AND gst.IsDeleted = 0
103 AND es.GroupResponsibilityLevel != 'Secondary';
104
105 DECLARE @AuthGroupExists int;
106 SELECT @AuthGroupExists = IIF(COUNT(1) > 0, 1, 0) FROM @authGroupIDs;
107
108 -- If Authorization Group exists, get all the ServiceTypeIDs for the Authorizaion Group
109 DECLARE @serviceTypeIDs TABLE(ServiceTypeID int);
110
111 INSERT @serviceTypeIDs
112 SELECT DISTINCT ServiceTypeID
113 FROM dbo.PlanGroupAuthGroupServiceTypes st (nolock) INNER JOIN
114 @authGroupIDs ag ON st.PlanGroupAuthorizationGroupID = ag.PlanGroupAuthorizationGroupID
115 WHERE st.IsDeleted = 0;
116
117 CREATE TABLE #ScheduleAuths
118 (
119 ScheduleAuthID int NOT NULL IDENTITY(1, 1),
120 ScheduleServiceID int,
121 BusinessUnitID int,
122 ServiceTypeID int,
123 IsVisit bit,
124 EpisodeID int,
125 PayRate decimal (18, 2),
126 BillRate decimal (18, 2),
127 VerifiedTimeIn datetime,
128 VerifiedTimeOut datetime,
129 IsOutOfAuthorization bit,
130 IsPartiallyOutOfAuth bit,
131 VersionStamp binary(8),
132 OrderOfInsert int,
133 IsPendingAuth bit,
134 OutofAuthTierID int,
135 CanBeCancelled bit,
136 POCManualTravelTime bit,
137 IsRequestOfNeed bit
138 );
139
140 -- 1. Posted Records Should Come First
141 -- 2. Processed Records i.e verified schedules with PPG set
142 -- 3. Verified Records should come next i.e verified schedules with no PPG
143 -- 4. Schedules other than cancelled, Posted, Processed & Verified should come last
144 INSERT INTO #ScheduleAuths
145 (
146 ScheduleServiceID,
147 BusinessUnitID,
148 ServiceTypeID,
149 IsVisit,
150 EpisodeID,
151 PayRate,
152 BillRate,
153 VerifiedTimeIn,
154 VerifiedTimeOut,
155 IsOutOfAuthorization,
156 IsPartiallyOutOfAuth,
157 VersionStamp,
158 OrderOfInsert,
159 IsPendingAuth,
160 OutofAuthTierID,
161 CanBeCancelled,
162 POCManualTravelTime,
163 IsRequestOfNeed
164 )
165 SELECT ss.ScheduleServiceID,
166 st.BusinessUnitID,
167 st.ServiceTypeID,
168 st.IsVisit,
169 ss.EpisodeID,
170 ss.PayRate,
171 ss.BillRate,
172 ss.VerifiedTimeIn,
173 ss.VerifiedTimeOut,
174 ss.IsOutOfAuthorization,
175 ss.IsPartiallyOutOfAuth,
176 ss.VersionStamp,
177 OrderOfInsert = case
178 when sch.ScheduleStatus = 'Processed' then 1
179 when sch.ScheduleStatus = 'ProcessedBNP' then 1
180 when sch.ScheduleStatus = 'ProcessedPNB' then 2
181 when sch.ScheduleStatus = 'Verified' AND PayrollProcessGroupID > 0 then 3
182 when sch.ScheduleStatus = 'Verified' then 4
183 else 5 end,
184 ss.IsPendingAuth,
185 ss.OutofAuthTierID,
186 CanBeCancelled = case
187 when ss.IsVNHardStopNotReq = 1 then 0
188 when sch.ScheduleStatus = 'Confirmed' then 1
189 when sch.ScheduleStatus = 'Need' then 1
190 when sch.ScheduleStatus = 'Schedule' then 1
191 when sch.ScheduleStatus = 'PotentialNeed' then 1
192 when sch.ScheduleStatus = 'PotentialSchedule' then 1
193 when sch.ScheduleStatus = 'PotentialConfirmed' then 1
194 when sch.ScheduleStatus = 'Claimed' then 1
195 else 0 end,
196 SS.POCManualTravelTime,
197 ss.IsRequestOfNeed
198 FROM dbo.ScheduleServices ss (nolock) INNER JOIN
199 dbo.ServiceTypes st (nolock) ON ss.BusinessUnitID = st.BusinessUnitID
200 AND ss.ServiceTypeID = st.ServiceTypeID INNER JOIN
201 dbo.ScheduleStatuses sch (nolock) ON ss.BusinessUnitID = sch.BusinessUnitID
202 AND ss.ScheduleStatusID = sch.ScheduleStatusesID
203 WHERE ss.BusinessUnitID = @businessUnitID
204 AND case
205 when @AuthGroupExists = 0 AND ss.ServiceTypeID = @serviceTypeID then 1
206 when @AuthGroupExists != 0 AND ss.ServiceTypeID IN (
207 SELECT ServiceTypeID FROM @serviceTypeIDs) then 1
208 end = 1
209 AND ss.EpisodeID = @episodeID
210 AND st.IsSpecialServiceType = 0
211 AND sch.ScheduleStatus != 'Cancelled'
212 ORDER BY OrderOfInsert, VerifiedTimeIn;
213
214
215 --update timeOut units
216 if @IsAuthorizationRounding = 1
217 begin
218 update
219 #ScheduleAuths
220 set
221 VerifiedTimeOut = dateadd(minute, floor(datediff(minute, VerifiedTimeIn, VerifiedTimeOut) / @NumberOfMinutes) * @EquivalentNumberOfUnits * @NumberOfMinutes +
222 case
223 when datediff(minute, VerifiedTimeIn, VerifiedTimeOut) % @NumberOfMinutes > @MinutesToRoundUpAfter then @NumberOfMinutes
224 else 0
225 end, VerifiedTimeIn)
226 where
227 IsVisit = 0
228 end
229
230 -- Schedule Services for Admission
231 SELECT ScheduleAuthID,
232 ScheduleServiceID,
233 BusinessUnitID,
234 ServiceTypeID,
235 IsVisit,
236 EpisodeID,
237 PayRate,
238 BillRate,
239 VerifiedTimeIn,
240 VerifiedTimeOut,
241 IsOutOfAuthorization,
242 IsPartiallyOutOfAuth,
243 VersionStamp,
244 IsPendingAuth,
245 OutofAuthTierID,
246 CanBeCancelled,
247 0 AS IsInfoRecord,
248 POCManualTravelTime,
249 IsRequestOfNeed
250 FROM #ScheduleAuths
251 ORDER BY ScheduleAuthID;
252
253 DECLARE @PlanGroupID TABLE(PlanGroupID int);
254
255 -- Get all the possible PayerIDs for the episodeid and ServiceTypeID combination.
256 INSERT @PlanGroupID
257 SELECT DISTINCT gip.PlanGroupID
258 FROM dbo.EpisodeServices es (nolock) INNER JOIN
259 dbo.EpisodePropertiesHistory eph (nolock) ON eph.EpisodePropertySetID = es.EpisodePropertySetID INNER JOIN
260 dbo.GroupsInPlans gip (nolock) ON es.PlanGroupID = gip.PlanGroupID
261 WHERE es.EpisodeID = @episodeID
262 AND es.ServiceTypeID = @serviceTypeID
263 AND eph.IsInvalid = 0;
264
265 -- Admission Authorization details
266 -- If an Authorization Group exists, get the episodeauthdetails information
267 if @AuthGroupExists= 0
268 begin
269 SELECT EpisodeAuthDetailID,
270 BusinessUnitID,
271 EpisodeID,
272 ead.ServiceTypeID,
273 ead.PlanGroupID,
274 NoOfMinutes,
275 NoOfVisits,
276 DollarAmount,
277 NoOfMinutesUsed,
278 NoOfVisitsUsed,
279 DollarAmountUsed,
280 AuthorizationNumber,
281 AuthorizationStartDate,
282 AuthorizationEndDate,
283 VersionStamp,
284 IsPeriodicCal,
285 MinNoOfMinutes,
286 IsPendingAuth,
287 SelfPayID,
288 TierID,
289 IsAdvanced,
290 EpisodeAuthorizationID,
291 IsSpecificTime,
292 AuthorizationStartDateforSpecificTime,
293 AuthorizationEndDateforSpecificTime,
294 NumOfTiers,
295 0 AS IsInfoRecord
296 FROM dbo.EpisodeAuthDetails ead (nolock)
297 WHERE EpisodeID = @episodeID
298 AND ServiceTypeID = @serviceTypeID
299 AND BusinessUnitID = @businessUnitID
300 end
301 else
302 begin
303 -- If an Authorization Group does not exist,
304 --1. Get episodeauthdetail info for the payer that is associated to the service type, for all the service types in the authorization group
305 SELECT ead.EpisodeAuthDetailID,
306 ead.BusinessUnitID,
307 ead.EpisodeID,
308 ead.ServiceTypeID,
309 ead.PlanGroupID,
310 ead.NoOfMinutes,
311 ead.NoOfVisits,
312 ead.DollarAmount,
313 ead.NoOfMinutesUsed,
314 ead.NoOfVisitsUsed,
315 ead.DollarAmountUsed,
316 ead.AuthorizationNumber,
317 ead.AuthorizationStartDate,
318 ead.AuthorizationEndDate,
319 ead.VersionStamp,
320 ead.IsPeriodicCal,
321 ead.MinNoOfMinutes,
322 ead.IsPendingAuth,
323 ead.SelfPayID,
324 ead.TierID,
325 ead.IsAdvanced,
326 ead.EpisodeAuthorizationID,
327 ead.IsSpecificTime,
328 ead.AuthorizationStartDateforSpecificTime,
329 ead.AuthorizationEndDateforSpecificTime,
330 ead.NumOfTiers,
331 0 AS IsInfoRecord
332 FROM dbo.EpisodeAuthDetails ead (nolock) INNER JOIN (
333 SELECT DISTINCT es.EpisodeID,
334 es.PlanGroupID,
335 gst.ServiceTypeID
336 FROM dbo.EpisodeServices es (nolock) INNER JOIN
337 dbo.EpisodePropertiesHistory eph (nolock) ON eph.EpisodePropertySetID = es.EpisodePropertySetID
338 AND eph.IsInvalid = 0 INNER JOIN
339 dbo.GroupsInPlans gip (nolock) ON es.PlanGroupID = gip.PlanGroupID INNER JOIN
340 dbo.PlanGroupAuthorizationGroups g (nolock) ON g.PlanGroupID = gip.PlanGroupID INNER JOIN
341 dbo.PlanGroupAuthGroupServiceTypes gst (nolock) ON gst.PlanGroupAuthorizationGroupID = g.PlanGroupAuthorizationGroupID
342 AND gst.ServiceTypeID = es.ServiceTypeID INNER JOIN
343 @PlanGroupID ids ON g.PlanGroupID = ids.PlanGroupID
344 WHERE es.EpisodeID = @episodeID
345 AND eph.IsInvalid = 0
346 ) b ON ead.EpisodeID = b.EpisodeID INNER JOIN
347 @serviceTypeIDs ids ON ead.ServiceTypeID = ids.ServiceTypeID
348 AND ead.ServiceTypeID = b.ServiceTypeID
349 AND ead.PlanGroupID = b.PlanGroupID
350 UNION -- Get episode auth detail information, for the service type id that is not associated to the payer id which has the authorization group
351 SELECT EpisodeAuthDetailID,
352 ead.BusinessUnitID,
353 ead.EpisodeID,
354 ead.ServiceTypeID,
355 ead.PlanGroupID,
356 NoOfMinutes,
357 NoOfVisits,
358 DollarAmount,
359 NoOfMinutesUsed,
360 NoOfVisitsUsed,
361 DollarAmountUsed,
362 AuthorizationNumber,
363 AuthorizationStartDate,
364 AuthorizationEndDate,
365 ead.VersionStamp,
366 IsPeriodicCal,
367 MinNoOfMinutes,
368 IsPendingAuth,
369 SelfPayID,
370 TierID,
371 IsAdvanced,
372 EpisodeAuthorizationID,
373 IsSpecificTime,
374 AuthorizationStartDateforSpecificTime,
375 AuthorizationEndDateforSpecificTime,
376 NumOfTiers,
377 0 AS IsInfoRecord
378 FROM dbo.EpisodeAuthDetails ead (nolock) INNER JOIN
379 dbo.GroupsInPlans gip (nolock) ON ead.PlanGroupID = gip.PlanGroupID
380 AND ead.BusinessUnitID = gip.BusinessUnitID
381 WHERE ead.EpisodeID = @episodeID
382 AND ead.ServiceTypeID in (
383 SELECT ServiceTypeID FROM @serviceTypeIDs)
384 AND gip.PlanGroupID NOT IN (
385 SELECT PlanGroupID FROM @authGroupIDs)
386 UNION
387 SELECT ead.EpisodeAuthDetailID,
388 ead.BusinessUnitID,
389 ead.EpisodeID,
390 ead.ServiceTypeID,
391 ead.PlanGroupID,
392 ead.NoOfMinutes,
393 ead.NoOfVisits,
394 ead.DollarAmount,
395 ead.NoOfMinutesUsed,
396 ead.NoOfVisitsUsed,
397 ead.DollarAmountUsed,
398 ead.AuthorizationNumber,
399 ead.AuthorizationStartDate,
400 ead.AuthorizationEndDate,
401 ead.VersionStamp,
402 ead.IsPeriodicCal,
403 ead.MinNoOfMinutes,
404 ead.IsPendingAuth,
405 ead.SelfPayID,
406 ead.TierID,
407 ead.IsAdvanced,
408 ead.EpisodeAuthorizationID,
409 ead.IsSpecificTime,
410 ead.AuthorizationStartDateforSpecificTime,
411 ead.AuthorizationEndDateforSpecificTime,
412 ead.NumOfTiers,
413 0 AS IsInfoRecord
414 FROM dbo.EpisodeAuthDetails ead (nolock) INNER JOIN
415 @serviceTypeIDs ids ON ead.ServiceTypeID = ids.ServiceTypeID
416 WHERE ead.EpisodeID = @episodeID
417 AND ead.BusinessUnitID = @businessUnitID
418 AND ead.SelfPayID IS NOT NULL
419 end
420
421 -- Admission Authorization time limits
422 SELECT DISTINCT ac.BusinessUnitID,
423 ac.AuthCalendarID,
424 ac.EpisodeID,
425 ac.ServiceTypeID,
426 ac.PlanGroupID,
427 ac.AuthorizationNumber,
428 ac.StartTime,
429 ac.EndTime,
430 CONVERT(varchar, ac.StartTime, 101) StartDate,
431 CONVERT(varchar, ac.EndTime, 101) EndDate
432 FROM dbo.AuthCalendar ac
433 WHERE BusinessUnitID = @businessUnitID
434 AND EpisodeID = @episodeID
435 AND case
436 when @AuthGroupExists = 0 AND ServiceTypeID = @serviceTypeID then 1
437 when @AuthGroupExists != 0 AND ServiceTypeID IN (
438 SELECT ServiceTypeID FROM @serviceTypeIDs) then 1
439 end = 1
440 AND IsInValid = 0;
441
442 --Payer Details
443 -- Get all the payer detail information
444 SELECT
445 ss.ScheduleServiceID,
446 ss.BusinessUnitID,
447 ss.ServiceTypeID,
448 ss.EpisodeID,
449 ss.VerifiedTimeIn,
450 ss.VerifiedTimeOut,
451 es.PlanGroupID,
452 es.SelfPayID,
453 es.GroupResponsibilityLevel,
454 es.OrderOfBilling,
455 ISNULL(arg.IsAuthRequired, 0) as IsAuthRequired,
456 ISNULL(gbr.IsAuthorizationRounding, 0) as IsAuthRounding,
457 ISNULL(IIF(es.PlanGroupID IS NOT NULL, gbr.PerBusinessDay, 0), 0) as IsBusinessDay,
458 @businessUnitStartTime as BusinessUnitStartTime,
459
460 NoOfTiers =
461 CASE
462 WHEN es.SelfPayID is not null THEN dbo.fnGetNumberofTiersinSelfPay(@BusinessUnitID, @EpisodeID, ss.serviceTypeID, ss.SelfPayID, VerifiedTimeIn)
463 --ELSE dbo.fnGetNumberofTiersinGroup(@BusinessUnitID, @EpisodeID, ServiceTypeID, PlanGroupID, VerifiedTimeIn)
464 else null
465 END,
466
467 IsHardStopAuth = ISNULL(case
468 when ISNULL(st.IsAssessment, 0) = 1 then 0
469 when es.PlanGroupID IS NOT NULL then rd.IsHardStopAuth
470 else null
471 --else dbo.fnGetNumberOfTiersInGroup(@businessUnitID, @episodeID, ss.ServiceTypeID, es.PlanGroupID, ss.VerifiedTimeIn)
472 end, 0) ,
473 --cast (0 as int) as NoOfTiers,
474 --cast (0 as bit) as IsHardStopAuth,
475
476 0 AS IsInfoRecord,
477 ss.IsRequestOfNeed
478 into #tmpSandwich
479 FROM dbo.EpisodePropertiesHistory eph (nolock) INNER JOIN
480 dbo.ScheduleServices ss (nolock) ON eph.BusinessUnitID = ss.BusinessUnitID
481 AND eph.EpisodeID = ss.EpisodeID
482 AND eph.ServiceTypeID = ss.ServiceTypeID INNER MERGE JOIN
483 dbo.EpisodeServices es (nolock) ON eph.BusinessUnitID = es.BusinessUnitID
484 AND eph.EpisodePropertySetID = es.EpisodePropertySetID
485 AND es.GroupResponsibilityLevel != 'Secondary' INNER JOIN
486 dbo.ScheduleStatuses sch (nolock) ON ss.BusinessUnitID = sch.BusinessUnitID
487 AND ss.ScheduleStatusID = sch.ScheduleStatusesID
488 AND sch.ScheduleStatus != 'Cancelled'
489 AND sch.ScheduleStatus != 'PotentialCancelled' LEFT JOIN
490 dbo.ServiceTypes st (nolock) ON ss.ServiceTypeID = st.ServiceTypeID
491 AND ss.BusinessUnitID = st.BusinessUnitID LEFT MERGE JOIN
492 dbo.GroupBillingRules gbr (nolock) ON gbr.BusinessUnitID = es.BusinessUnitID
493 AND gbr.PlanGroupID = es.PlanGroupID
494 AND gbr.IsInValid = 0
495 AND case
496 when gbr.PerBusinessDay = 1
497 AND DATEADD(day, DATEDIFF(day, 0, gbr.EffectiveBeginDate),
498 CONVERT(varchar(8), CONVERT(time, @businessUnitStartTime))) <= ss.ServiceStartDateTime then 1
499 when gbr.PerBusinessDay = 0
500 AND CONVERT(varchar, gbr.EffectiveBeginDate, 112) <= CONVERT(varchar,ss.ServiceStartDateTime, 112) then 1
501 else 0 end = 1
502 AND (gbr.EffectiveEndDate IS NULL
503 OR (case
504 when gbr.PerBusinessDay = 1
505 AND DATEADD(day, DATEDIFF(day, 0, gbr.EffectiveEndDate), CONVERT(varchar(8),
506 CONVERT(time, @businessUnitStartTime))) > ss.ServiceStartDateTime then 1
507 when gbr.PerBusinessDay = 0
508 AND CONVERT(varchar, gbr.EffectiveEndDate, 112) > CONVERT(varchar, ss.ServiceStartDateTime, 112) then 1
509 else 0 end) = 1
510 ) LEFT OUTER JOIN
511 dbo.GroupBillingRuleDetails rd (nolock) ON rd.BusinessUnitID = gbr.BusinessUnitID
512 AND rd.GroupBillingRuleID = gbr.GroupBillingRuleID LEFT OUTER JOIN ( -- fnCheckIfAuthReqdByGroup
513 SELECT ISNULL(~ga.NoAuthRequired, 1) as IsAuthRequired,
514 eph.BusinessUnitID, eph.EpisodeID, eph.ServiceTypeID, es.PlanGroupID,
515 eph.EffectiveBeginDate as EphEffectiveBeginDate, eph.EffectiveEndDate as EphEffectiveEndDate,
516 gph.EffectiveBeginDate as GphEffectiveBeginDate, gph.EffectiveEndDate as GphEffectiveEndDate
517 FROM dbo.EpisodePropertiesHistory eph (nolock) INNER JOIN
518 dbo.EpisodeServices es (nolock) ON es.BusinessUnitID = eph.BusinessUnitID
519 AND es.EpisodePropertySetID = eph.EpisodePropertySetID INNER JOIN
520 dbo.GroupServiceTypes gst (nolock) ON es.BusinessUnitID = gst.BusinessUnitID
521 AND es.PlanGroupID = gst.PlanGroupID
522 AND es.ServiceTypeID = gst.ServiceTypeID INNER JOIN
523 dbo.GroupPropertiesHistory gph (nolock) ON gst.BusinessUnitID = gph.BusinessUnitID
524 AND gst.GroupServiceTypeID = gph.GroupServiceTypeID INNER JOIN
525 dbo.GroupAuthorization ga (nolock) ON gph.BusinessUnitID = ga.BusinessUnitID
526 AND gph.GroupPropertiesSetID = ga.GroupPropertiesSetID
527 WHERE eph.IsInvalid = 0
528 AND gph.IsInValid = 0
529 AND gph.IsInitialValue = 0
530 ) arg ON es.BusinessUnitID = arg.BusinessUnitID
531 AND eph.EpisodeID = arg.EpisodeID
532 AND ISNULL(es.PlanGroupID, es.SelfPayID) = arg.PlanGroupID
533 AND eph.ServiceTypeID = arg.ServiceTypeID
534 AND es.PlanGroupID = arg.PlanGroupID
535 AND dbo.fnGetConvertDate(arg.EphEffectiveBeginDate) <= dbo.fnGetConvertDate(ss.VerifiedTimeIn)
536 AND dbo.fnGetConvertDate(ISNULL(arg.EphEffectiveEndDate, ss.VerifiedTimeIn)) >= dbo.fnGetConvertDate(ss.VerifiedTimeIn)
537 AND dbo.fnGetConvertDate(arg.GphEffectiveBeginDate) <= dbo.fnGetConvertDate(ss.VerifiedTimeIn)
538 AND dbo.fnGetConvertDate(ISNULL(arg.GphEffectiveEndDate, ss.VerifiedTimeIn)) >= dbo.fnGetConvertDate(ss.VerifiedTimeIn)
539 WHERE eph.BusinessUnitID = @businessUnitID
540 AND eph.IsInValid = 0
541 AND eph.EpisodeID = @episodeID
542 AND case
543 when @AuthGroupExists = 0 AND eph.ServiceTypeID = @serviceTypeID then 1
544 when @AuthGroupExists != 0 AND eph.ServiceTypeID IN (
545 SELECT ServiceTypeID FROM @serviceTypeIDs) then 1
546 end = 1
547 AND CONVERT(varchar, eph.EffectiveBeginDate, 112) <= CONVERT(varchar, ss.VerifiedTimeIn, 112)
548 AND (eph.EffectiveEndDate IS NULL
549 OR CONVERT(varchar, eph.EffectiveEndDate, 112) > CONVERT(varchar, ss.VerifiedTimeIn, 112))
550 ORDER BY ss.ScheduleServiceID ASC, eph.EpisodePropertySetID DESC;
551
552 update #tmpSandwich
553 set
554 NoOfTiers =
555 CASE
556 WHEN SelfPayID is not null THEN NoOfTiers
557 ELSE
558 --dbo.fnGetNumberofTiersinGroup(@BusinessUnitID, @EpisodeID, ServiceTypeID, PlanGroupID, VerifiedTimeIn)
559 (
560 SELECT
561 top 1 GroupAuthorization.NumofTiers
562 FROM
563 dbo.EpisodePropertiesHistory
564 INNER JOIN
565 dbo.EpisodeServices
566 ON
567 EpisodeServices.BusinessUnitID = EpisodePropertiesHistory.BusinessUnitID
568 AND
569 EpisodeServices.EpisodePropertySetID = EpisodePropertiesHistory.EpisodePropertySetID
570 INNER JOIN
571 GroupServiceTypes
572 ON
573 EpisodeServices.BusinessUnitID = GroupServiceTypes.BusinessUnitID
574 AND
575 EpisodeServices.PlanGroupID = GroupServiceTypes.PlanGroupID
576 AND
577 EpisodeServices.ServiceTypeID = GroupServiceTypes.ServiceTypeID
578 INNER JOIN
579 dbo.GroupPropertiesHistory
580 ON
581 GroupServiceTypes.BusinessUnitID = GroupPropertiesHistory.BusinessUnitID
582 AND
583 GroupServiceTypes.GroupServiceTypeID = GroupPropertiesHistory.GroupServiceTypeID
584 INNER JOIN
585 dbo.GroupAuthorization
586 ON
587 GroupPropertiesHistory.BusinessUnitID = GroupAuthorization.BusinessUnitID
588 AND
589 GroupPropertiesHistory.GroupPropertiesSetID = GroupAuthorization.GroupPropertiesSetID
590 WHERE
591 EpisodePropertiesHistory.BusinessUnitID = @BusinessUnitID
592 AND
593 EpisodePropertiesHistory.EpisodeID = @EpisodeID
594 AND
595 EpisodePropertiesHistory.ServiceTypeID = @ServiceTypeID
596 AND
597 EpisodeServices.PlanGroupID = #tmpSandwich.PlanGroupID
598 AND
599 EpisodePropertiesHistory.IsInvalid = 0
600 AND
601 dbo.fnGetConvertDate(EpisodePropertiesHistory.EffectiveBeginDate) <= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
602 AND
603 (
604 EpisodePropertiesHistory.EffectiveEndDate IS NULL
605 OR
606 dbo.fnGetConvertDate(EpisodePropertiesHistory.EffectiveEndDate) >= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
607 )
608
609 AND
610 dbo.fnGetConvertDate(GroupPropertiesHistory.EffectiveBeginDate) <= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
611 AND
612 (
613 GroupPropertiesHistory.EffectiveEndDate IS NULL
614 OR
615 dbo.fnGetConvertDate(GroupPropertiesHistory.EffectiveEndDate) >= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
616 )
617 AND
618 GroupPropertiesHistory.IsInValid = 0
619 AND
620 GroupPropertiesHistory.IsInitialValue = 0
621 )
622 END,
623
624 IsHardStopAuth = ISNULL(case
625 when IsHardStopAuth = 0 then 0
626 when PlanGroupID IS NOT NULL then IsHardStopAuth
627 else
628 (
629 SELECT
630 top 1 GroupAuthorization.NumofTiers
631 FROM
632 dbo.EpisodePropertiesHistory
633 INNER JOIN
634 dbo.EpisodeServices
635 ON
636 EpisodeServices.BusinessUnitID = EpisodePropertiesHistory.BusinessUnitID
637 AND
638 EpisodeServices.EpisodePropertySetID = EpisodePropertiesHistory.EpisodePropertySetID
639 INNER JOIN
640 GroupServiceTypes
641 ON
642 EpisodeServices.BusinessUnitID = GroupServiceTypes.BusinessUnitID
643 AND
644 EpisodeServices.PlanGroupID = GroupServiceTypes.PlanGroupID
645 AND
646 EpisodeServices.ServiceTypeID = GroupServiceTypes.ServiceTypeID
647 INNER JOIN
648 dbo.GroupPropertiesHistory
649 ON
650 GroupServiceTypes.BusinessUnitID = GroupPropertiesHistory.BusinessUnitID
651 AND
652 GroupServiceTypes.GroupServiceTypeID = GroupPropertiesHistory.GroupServiceTypeID
653 INNER JOIN
654 dbo.GroupAuthorization
655 ON
656 GroupPropertiesHistory.BusinessUnitID = GroupAuthorization.BusinessUnitID
657 AND
658 GroupPropertiesHistory.GroupPropertiesSetID = GroupAuthorization.GroupPropertiesSetID
659 WHERE
660 EpisodePropertiesHistory.BusinessUnitID = @BusinessUnitID
661 AND
662 EpisodePropertiesHistory.EpisodeID = @EpisodeID
663 AND
664 EpisodePropertiesHistory.ServiceTypeID = @ServiceTypeID
665 AND
666 EpisodeServices.PlanGroupID = #tmpSandwich.PlanGroupID
667 AND
668 EpisodePropertiesHistory.IsInvalid = 0
669 AND
670 dbo.fnGetConvertDate(EpisodePropertiesHistory.EffectiveBeginDate) <= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
671 AND
672 (
673 EpisodePropertiesHistory.EffectiveEndDate IS NULL
674 OR
675 dbo.fnGetConvertDate(EpisodePropertiesHistory.EffectiveEndDate) >= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
676 )
677
678 AND
679 dbo.fnGetConvertDate(GroupPropertiesHistory.EffectiveBeginDate) <= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
680 AND
681 (
682 GroupPropertiesHistory.EffectiveEndDate IS NULL
683 OR
684 dbo.fnGetConvertDate(GroupPropertiesHistory.EffectiveEndDate) >= dbo.fnGetConvertDate(#tmpSandwich.VerifiedTimeIn)
685 )
686 AND
687 GroupPropertiesHistory.IsInValid = 0
688 AND
689 GroupPropertiesHistory.IsInitialValue = 0
690 )
691 end, 0)
692
693
694
695 select * from #tmpSandwich
696
697 SELECT sa.BusinessUnitID,
698 sa.ScheduleServiceID,
699 sa.EpisodeID,
700 sa.ServiceTypeID,
701 PlanGroupID,
702 SelfPayID,
703 AuthorizationNumber,
704 Duration,
705 sa.IsPendingAuth,
706 0 AS IsInfoRecord
707
708 FROM dbo.ScheduleAuths sa (nolock) INNER JOIN
709 #ScheduleAuths SchedulesFetched ON sa.ScheduleServiceID = SchedulesFetched.ScheduleServiceID
710 WHERE IsInValid = 0
711 AND case
712 when @AuthGroupExists = 0 AND sa.ServiceTypeID = @serviceTypeID then 1
713 when @AuthGroupExists != 0 AND sa.ServiceTypeID IN (
714 SELECT ServiceTypeID FROM @serviceTypeIDs) then 1
715 end = 1;
716
717 DROP TABLE #ScheduleAuths;
718 DROP TABLE #tmpSandwich;
719 drop table #tmpBillingConfigurationForAuth;
720 END
721
722
723
724
725
726
727
728
729
730
731
732
733
734convert(datetime, convert(varchar, EpisodePropertiesHistory.EffectiveBeginDate, 112), 112)
735 <=
736 convert(datetime, convert(varchar, #tmpSandwich.VerifiedTimeIn, 112), 112)