· 6 years ago · Apr 11, 2019, 06:24 PM
1USE [36_XpertEMR]
2GO
3/****** Object: StoredProcedure [dbo].[USP_EMR_Visited_Billing_Charges_Not_CreatedList_Insert_OnetoOneAppt] Script Date: 4/2/2019 12:59:57 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9/*
10Stored Procedure Name : USP_EMR_Visited_Billing_Charges_Not_CreatedList_Insert_OnetoOneAppt
11Author : Sainath.N
12Created On : Dec 23 2016
13To Execute : EXEC USP_EMR_Visited_Billing_Charges_Not_CreatedList_Insert_OnetoOneAppt
14 @LoggedUserID = 42,
15 @AppointmentID = '72178',
16 @IsCancelledAppt = 0
17SP Descrption : THIS SP IS USE DTO GET THE SHOW THE DATA FROM THE ONE TO ONE APPIONTMNTS INFORMATION,WHICH ARE NOT YET CREATED TO CLAIM.
18*/
19
20ALTER PROC [dbo].[USP_EMR_Visited_Billing_Charges_Not_CreatedList_Insert_OnetoOneAppt]
21(
22 @return INT = NULL OUTPUT,
23 @errorID INT = NULL OUTPUT,
24 @errorMessage VARCHAR(2048) = NULL OUTPUT,
25 @LoggedUserID INT,
26 @AppointmentID VARCHAR(8000),
27 @IsCancelledAppt BIT = 0,
28 @Billing_ChargeCaptured_Status_InfoID INT = NULL
29)
30AS
31/*SET NOCOUNT ON statement into store procedures can reduce network traffic,
32because client will not receive the message indicating the number of rows affected by T-SQL statement. */
33SET NOCOUNT ON
34BEGIN
35 --TRY…CATCH constructs catch unhandled errors from stored procedures executed by the code in the TRY block.Here Starts the try block
36 BEGIN TRY
37
38 BEGIN TRANSACTION
39
40 /**
41 DECLARE A TABLE VARIABLE TO STORE COMMA SEPARATED APPOINTMENT ID'S from @AppointmentID
42 */
43 DECLARE @tbl_Given_Appointments TABLE(ID INT IDENTITY(1,1),AppointmentID INT)
44
45 /********************************************************
46 * INSERT INTO TABLE VARIABLE START
47 **/
48
49 INSERT INTO @tbl_Given_Appointments
50 (
51 AppointmentID
52 )
53 SELECT
54 ID AS AppointmentID
55 FROM
56 DBO.udf_CSV_ToTable2(@AppointmentID,',')
57
58 /**
59 * INSERT INTO TABLE VARIABLE END
60 *******************************************************/
61
62 /*THIS VARAIBLE IS DECLARED TO GET THE CURRENT DATETIME TO AVOID THE FUNCTIONAL CALLINGS,WHICH REDUCES THE PERFORMANCE.*/
63 DECLARE
64 @GetDate DATETIME = DBO.GETEMRDATETIME(),
65 @ShowInChargeCaptureModule BIT,
66 @SystemDefinedInsuranceID_Patient INT,
67 @DischargeStatus INT,
68 @Appt_CancleStatus_ID INT,
69 @IsCancelFormat2 BIT
70
71 /***********************************************************************
72 * CHECK IF APPOINTMENT IS FROM CANCEL FORMAT 1 OR CANCEL FORMAT 2 START
73 */
74 IF EXISTS(SELECT 1 FROM DBO.tbl_Appointment_Setting_Fields_Customization WHERE Appointment_Setting_FieldID = 157 and Appointment_Setting_Fields_Customization_InActive = 0 )
75 BEGIN
76
77 SELECT @IsCancelFormat2 = 1
78
79 END
80 ELSE
81 BEGIN
82
83 SELECT @IsCancelFormat2 = 0
84
85 END
86 /**
87 * CHECK IF APPOINTMENT IS FROM CANCEL FORMAT 1 OR CANCEL FORMAT 2 END
88 **************************************************************************/
89
90 -- WHY THIS TABLE IS USED ???
91 -- SELECT * FROM tbl_Common_CPT_DxCodes_Navigations_ToBeConsider_LKP WHERE ShowInChargeCaptureModule = 1;
92
93 /***********************************************************************
94 * CHECK IF APPOINTMENT IS TRIGGER CUSTOMIZED OR NOT START
95 */
96
97 /*THIS SELECT QUERY IS USED TO GET THE FLAG THAT IS APPOINTMENTS MODULE IN CHARGE CAPTURED LIST OR NOT*/
98 SELECT
99 @ShowInChargeCaptureModule = LKP.ShowInChargeCaptureModule
100 FROM
101 dbo.tbl_Common_CPT_DxCodes_Navigations_ToBeConsider_LKP AS LKP WITH(NOLOCK) --ALL THE CHARGE CAPTURE MODULES LIST EXISTS IN THIS TABLE
102 WHERE
103 LKP.Common_CPT_DxCodes_Navigations_ToBeConsider_ID = 21 --APPOINTMENTS MODULE.
104
105 /**
106 * CHECK IF APPOINTMENT IS TRIGGER CUSTOMIZED OR NOT END
107 **************************************************************************/
108
109 IF(@ShowInChargeCaptureModule = 1)
110 BEGIN
111
112 CREATE TABLE #tbl_Physicians_QualificationCodes
113 (
114 Provider_QualificationCode VARCHAR(256),
115 PhysicianID INT,
116 BedOccupiedDayEncounterID INT
117 )
118
119 CREATE TABLE #tbl_LatestDxCodes
120 (
121 InPatient_Charges_Not_CreatedListID INT, DXCode VARCHAR(256),
122 DxType INT, Billing_Charges_Not_CreatedList_DxLinked_CreatedBy INT,
123 RecordType INT, EnrollmentID INT,
124 BedOccupiedDayEncounterID INT
125 )
126
127 /*THIS TABLE VARIBLE IS USED TO SAVE THE LATEST SAEV CHARGE NOT CREATED LATEST IDS.*/
128 DECLARE @tbl_LatestSaved_ChargeIDs TABLE
129 (
130 ChargesNotCreatedListID INT,
131 BedOccupiedDayEncounterID INT,
132 Recordtype INT
133 )
134
135 /*THIS TEMP TABLE IS USED TO CRAETE THE ALL THE LOCATION WHICH ARE GOING TO CAPTURE IN APPOINTMENTS MODULE*/
136 CREATE TABLE #tbl_ChargeCapture_Locations
137 (
138 Billing_Charge_Capture_EventTypeID INT
139 )
140
141 SELECT
142 @Appt_CancleStatus_ID = LKP.Appointment_Status_InfoID
143 FROM
144 dbo.tbl_Appointments_Status_LKP AS LKP WITH(NOLOCK)
145 WHERE
146 LKP.Appointment_Status_SystemDefined_TypeID = 4 --Cancle
147 AND LKP.Appointment_Status_Info_InActive = 0
148
149 INSERT INTO #tbl_ChargeCapture_Locations
150 (
151 Billing_Charge_Capture_EventTypeID
152 )
153 SELECT DISTINCT
154 CASE
155 WHEN LKP.Appointment_Status_SystemDefined_TypeID = 51 THEN @Appt_CancleStatus_ID
156 ELSE Cus.Billing_Charge_Capture_Location_Module_Or_FormID
157 END AS Billing_Charge_Capture_Location_Module_Or_FormID
158 FROM
159 tbl_Billing_Charge_Capture_Locations_Customization AS Cus WITH(NOLOCK)
160 INNER JOIN
161 dbo.tbl_Appointments_Status_LKP AS LKP WITH(NOLOCK) ON Cus.Billing_Charge_Capture_Location_Module_Or_FormID = LKP.Appointment_Status_InfoID
162 WHERE
163 Cus.Billing_Charge_Capture_Location_Module_Or_FormType = 21 --APPOINTMENTS MODULE
164 AND Cus.Billing_Charge_Capture_Location_CustomizationInfo_InActive = 0
165
166 ---THIS IS THE SYSTEM DEFINED PATIENTID..I.E WE CAN CREATE THE INSURANCE WITH THE NAME OF PATIENT IN HELATH PLAN WINDOW.
167 SELECT
168 @SystemDefinedInsuranceID_Patient = Ins.InsuranceId
169 FROM
170 dbo.tbl_Insurance AS Ins WITH(NOLOCK)
171 WHERE
172 Ins.[Name] = 'Patient'
173 AND Ins.InactiveDueToEditing =0
174 AND Ins.InactievDueToNoLongerUsing = 0
175
176 ---HERE WE ARE GETTING THE DATA IN ONE VARIBLE WHICH IS SAVED IN BILLING SETTINGS.
177 SELECT
178 @DischargeStatus = BS.UB04DischargeStatus
179 FROM
180 tbl_Billing_Settings AS BS WITH(NOLOCK)
181 WHERE
182 BS.Inactive = 0
183
184 /*
185 THIS IS THE TEMP TABLE WHICH USED TO STORE THE ALL THE INFORMATION OF GIVEN APPOINTMENTS WHICH WILL BE SAVED OR UPDATE
186 IN BELOW QUERY.
187 */
188 CREATE TABLE #tbl_Billing_Charges_Not_CreatedList
189 (
190 Patientid INT, EnrollmentID INT,
191 LevelofCare INT, Billing_ChargeMaster_RuleID INT,
192 Units DECIMAL(18, 2), Charge DECIMAL(18, 2),
193 BedOccupiedDayEncounterID INT, InPat_Adminission_BedOccupiedDate DATETIME,
194 PatientPrimaryInsPolicyID INT, HealthPlanId INT,
195 ActivityId INT, ActivityName VARCHAR(64),
196 ActivityInfo VARCHAR(256), FacilityId INT,
197 AuthNo VARCHAR(64), ReferalAuthID INT,
198 LastBilledDate DATETIME, TransactionID INT,
199 RecordType INT, Duration INT,
200 EMR_Billing_DoubleDollarInfo_ID INT, CPTCode VARCHAR(256),
201 RevenueCode VARCHAR(256), Modifier1 VARCHAR(32),
202 Modifier2 VARCHAR(32), DX1 VARCHAR(32),
203 DX2 VARCHAR(32), IsBillProfessionCharges BIT,
204 PhysicianID INT, ProviderComments VARCHAR(1024),
205 VisitTypeID INT, BillingComments VARCHAR(1024),
206 AuthRemainingVisits VARCHAR(256), AuthExpDate DATETIME,
207 DocStatus VARCHAR(32), ResourceType INT,
208 ReasonForVisit VARCHAR(1024), VisitType_Name VARCHAR(512),
209 ReferingProviderID INT, BillTo VARCHAR(512),
210 AuthExists VARCHAR(16), Appt_BillTo INT,
211 EpisodeNumber INT, FromDuration INT,
212 ToDuration INT, RemainingDuration INT,
213 AdmissionTime DATETIME, AdmissionType INT,
214 AdmissionTypeDescription VARCHAR(256), AdmissionSource VARCHAR(16),
215 AdmissionSourceDesc VARCHAR(256), DischargeTime DATETIME,
216 DischargeStatus INT, Billing_Claims_UB04_TypeOf_AdmissionID INT,
217 Age INT, ZipCode VARCHAR(256),
218 BillToID INT, Modality INT,
219 ProviderQualificationCode VARCHAR(64), CheckInTime DATETIME,
220 CheckOutTime DATETIME, Programs_Services_LinkedInfo_ID INT,
221 ProgramID INT, ServiceID INT,
222 ModalityDesc VARCHAR(256), AdmissionDate DATETIME,
223 BillingType INT, Appointment_Status_InfoID INT,
224 Appointment_Status_SystemDefined_TypeID INT, Billing_ReferalAuth_AllowedCptCodes_InfoID INT,
225 ApptCancelledType INT, AppointmentDuration INT,
226 CheckinCheckOutDuration INT , IsBillable BIT,
227 IsLinkInteractiveComplexity BIT, AdmittingProviderID INT ,
228 SlidingScale_IncomeRange_InfoID INT, Patient_State VARCHAR(16),
229 Patient_County VARCHAR(64), ProviderJobTitleID INT,
230 Billing_Insurance_Chargecapture_CategoryInfoID INT
231 )
232 INSERT INTO #tbl_Billing_Charges_Not_CreatedList
233 (
234 Patientid, EnrollmentID,
235 LevelofCare, BedOccupiedDayEncounterID,
236 InPat_Adminission_BedOccupiedDate, PatientPrimaryInsPolicyID,
237 HealthPlanId, FacilityId,
238 ReferalAuthID, RecordType,
239 Duration, PhysicianID,
240 ProviderComments, VisitTypeID,
241 ResourceType, ReasonForVisit,
242 ReferingProviderID, BillTo,
243 Appt_BillTo, EpisodeNumber,
244 AdmissionTime, DischargeTime,
245 Billing_Claims_UB04_TypeOf_AdmissionID, BillToID,
246 Modality, CheckInTime,
247 CheckOutTime, Programs_Services_LinkedInfo_ID,
248 AdmissionDate, Appointment_Status_InfoID,
249 Appointment_Status_SystemDefined_TypeID, Billing_ReferalAuth_AllowedCptCodes_InfoID,
250 ApptCancelledType, AppointmentDuration ,
251 CheckinCheckOutDuration , IsBillable,
252 IsLinkInteractiveComplexity, AdmittingProviderID
253 )
254 SELECT DISTINCT
255 Appt.PatientID, Appt.InPat_CareLevel_Event_Patient_InfoID,
256 Enrollment.LevelofCare, Appt.AppointmentID,
257 Appt.StartTime, Appt.PatientInsuranceID ,
258 POL.InsuranceID AS HealthPlanId, Appt.FacilityID,
259 Appt.ReferalAuthID, 3 AS RecordType,/*ONETOONE APPT*/
260 Appt.Duration, CASE
261 WHEN Appt.ResourceType = 1 THEN Appt.PhysicianID
262 ELSE Appt.ResourceLinkedPhysicianID
263 END PhysicianID,
264 APPT.Comment AS ProviderComments, Appt.VisitType AS VisitTypeID,
265 Appt.ResourceType, Appt.ReasonForVisit,
266 Appt.ReferingProviderID, CASE
267 WHEN Appt.Appt_BillTo = 1 THEN 'Insurance/Grant'
268 WHEN Appt.Appt_BillTo = 2 THEN 'Sliding Fee'
269 WHEN Appt.Appt_BillTo = 3 THEN 'Cash Pmt'
270 WHEN Appt.Appt_BillTo = 4 THEN 'Non Billable Visit'
271 ELSE NULL
272 END AS BillTo,
273
274 Appt.Appt_BillTo, Enrollment.EpisodeNumber,
275 Enrollment.InPatient_CareLevel_Events_Patients_StartTime, Enrollment.InPatient_CareLevel_Events_Patients_EndTime,
276 Enrollment.Billing_Claims_UB04_TypeOf_AdmissionID, Appt.Appt_BillTo,
277 Appt.AppointmentType, Appt.CheckInTime,
278 Appt.CheckOutTime, Appt.Programs_Services_LinkedInfo_ID,
279 Enrollment.InPat_CareLevel_Event_StartDate, Stat.Appointment_Status_InfoID,
280 LKP.Appointment_Status_SystemDefined_TypeID, Appt.Billing_ReferalAuth_AllowedCptCodes_InfoID,
281 Appt.ApptCancelledType, Appt.Duration,
282 DATEDIFF(MINUTE,Appt.CheckInTime,Appt.CheckOutTime),
283 CASE
284 WHEN Appointment_Status_SystemDefined_TypeID IN (4,3) AND CancelledRequestedBy = 1 THEN 1
285 ELSE 0
286 END AS IsBillable, Appt.IsLinkInteractiveComplexity,
287 Enrollment.ProviderID
288 FROM
289 dbo.tbl_Appointments AS Appt WITH(NOLOCK)
290 INNER JOIN
291 dbo.tbl_Appointments_Status_Linked AS Stat WITH(NOLOCK) ON Appt.AppointmentID = Stat.AppointmentID AND Stat.Appointment_Status_Linked_Info_InActive = 0
292 INNER JOIN
293 dbo.tbl_Appointments_Status_LKP AS LKP WITH(NOLOCK) ON STAT.Appointment_Status_InfoID = LKP.Appointment_Status_InfoID
294 INNER JOIN
295 #tbl_ChargeCapture_Locations AS Loc ON LKP.Appointment_Status_InfoID = Loc.Billing_Charge_Capture_EventTypeID
296 INNER JOIN
297 @tbl_Given_Appointments AS Tabl ON APPT.AppointmentID = Tabl.AppointmentID
298 LEFT JOIN
299 dbo.tbl_InPatient_CareLevel_Events_Patients_Info AS Enrollment WITH(NOLOCK)ON Enrollment.InPat_CareLevel_Event_Patient_InfoID = Appt.InPat_CareLevel_Event_Patient_InfoID AND Enrollment.InPat_CareLevel_Event_Patient_Info_InActive = 0
300 LEFT JOIN
301 dbo.tbl_Patient_Insurance as POL WITH(NOLOCK) ON Appt.PatientInsuranceId = POL.PatientInsuranceID AND POL.Inactive = 0
302 INNER JOIN
303 dbo.tbl_Person AS PER ON Appt.PatientID = per.PersonID AND Per.InActive = 0 AND PER.Person_Type = 2
304 WHERE
305 Appt.AppointmentModuleType <> 4
306
307 --************ UPDATING THE PROGRAMID AND SERVICEID FOR THE GIVEN APPOINTMENTS.
308 UPDATE Flat
309 SET Flat.ProgramID = Link.ProgramID,
310 Flat.ServiceID = Link.ServiceID
311 FROM
312 #tbl_Billing_Charges_Not_CreatedList AS Flat
313 INNER JOIN
314 dbo.tbl_Programs_Services_Linked AS Link WITH(NOLOCK) ON Flat.Programs_Services_LinkedInfo_ID = Link.Programs_Services_LinkedInfo_ID
315 WHERE
316 Link.Programs_Services_LinkedInfo_InActive = 0
317
318 INSERT INTO #tbl_Billing_Charges_Not_CreatedList
319 (
320 Patientid, EnrollmentID,
321 LevelofCare, BedOccupiedDayEncounterID,
322 InPat_Adminission_BedOccupiedDate, PatientPrimaryInsPolicyID,
323 HealthPlanId, FacilityId,
324 ReferalAuthID, RecordType,
325 Duration, PhysicianID,
326 ProviderComments, VisitTypeID,
327 ResourceType, ReasonForVisit,
328 ReferingProviderID, BillTo,
329 Appt_BillTo, EpisodeNumber,
330 AdmissionTime, DischargeTime,
331 Billing_Claims_UB04_TypeOf_AdmissionID, BillToID,
332 Modality, CheckInTime,
333 CheckOutTime, Programs_Services_LinkedInfo_ID,
334 AdmissionDate, Appointment_Status_InfoID,
335 Appointment_Status_SystemDefined_TypeID, Billing_ReferalAuth_AllowedCptCodes_InfoID,
336 ApptCancelledType, AppointmentDuration ,
337 CheckinCheckOutDuration , IsBillable,
338 AdmittingProviderID
339 )
340 SELECT
341 Patientid, EnrollmentID,
342 LevelofCare, BedOccupiedDayEncounterID,
343 InPat_Adminission_BedOccupiedDate, PatientPrimaryInsPolicyID,
344 HealthPlanId, FacilityId,
345 ReferalAuthID, 16 as RecordType,
346 Duration, PhysicianID,
347 ProviderComments, VisitTypeID,
348 ResourceType, ReasonForVisit,
349 ReferingProviderID, BillTo,
350 Appt_BillTo, EpisodeNumber,
351 AdmissionTime, DischargeTime,
352 Billing_Claims_UB04_TypeOf_AdmissionID, BillToID,
353 Modality, CheckInTime,
354 CheckOutTime, Link.Programs_Services_LinkedInfo_ID,
355 AdmissionDate, Appointment_Status_InfoID,
356 Appointment_Status_SystemDefined_TypeID, Billing_ReferalAuth_AllowedCptCodes_InfoID,
357 ApptCancelledType, AppointmentDuration ,
358 CheckinCheckOutDuration , IsBillable,
359 TEMP.AdmittingProviderID
360 FROM
361 #tbl_Billing_Charges_Not_CreatedList AS TEMP
362 INNER JOIN
363 dbo.tbl_Programs_Services_Linked AS Link ON TEMP.ProgramID = Link.ProgramID AND Link.Programs_Services_LinkedInfo_InActive = 0
364 INNER JOIN
365 DBO.tbl_InPatient_GroupTherapy_LKP AS LKP ON Link.ServiceID = lkp.InPat_GroupTherapyID AND LKP.InPat_GroupTherapy_InActive = 0
366 WHERE
367 LKP.DoNotBring_SyntheticServices = 2
368 AND TEMP.IsLinkInteractiveComplexity = 1
369
370 --************ UPDATING THE PROGRAMID AND SERVICEID FOR THE GIVEN APPOINTMENTS.
371 UPDATE Flat
372 SET Flat.ProgramID = Link.ProgramID,
373 Flat.ServiceID = Link.ServiceID
374 FROM
375 #tbl_Billing_Charges_Not_CreatedList AS Flat
376 INNER JOIN
377 dbo.tbl_Programs_Services_Linked AS Link WITH(NOLOCK) ON Flat.Programs_Services_LinkedInfo_ID = Link.Programs_Services_LinkedInfo_ID
378 WHERE
379 Link.Programs_Services_LinkedInfo_InActive = 0
380 AND ((ISNULL(Flat.ProgramID,0) = 0) OR (ISNULL(Flat.ServiceID,0) = 0))
381
382 --********** UPDATING THE MODALITY DESCRIPTION OF GIVEN APPOINTMENTS.
383 UPDATE Flat
384 SET Flat.ModalityDesc = LKP.Appointment_Encounter_Modality_Name
385 FROM
386 dbo.tbl_Appointment_Encounter_Modality_Lkp AS LKP WITH(NOLOCK)
387 INNER JOIN
388 #tbl_Billing_Charges_Not_CreatedList AS Flat ON LKP.Appointment_Encounter_Modality_Type = Flat.Modality
389 WHERE
390 LKP.Appointment_Encounter_Modality_InActive = 0
391 AND ISNULL(Flat.Modality,0) > 0
392
393
394 --************ UPDATING THE BILLING TYPE FOR THE GIVEN APPOINTMENTS.
395 UPDATE Flat
396 SET Flat.BillingType = LKP.BillingType
397 FROM
398 #tbl_Billing_Charges_Not_CreatedList AS Flat
399 INNER JOIN
400 dbo.tbl_InPatient_GroupTherapy_LKP AS LKP WITH(NOLOCK) ON Flat.ServiceID = LKP.InPat_GroupTherapyID
401 WHERE
402 ISNULL(Flat.ServiceID,0) > 0
403
404
405 --************ UPDATING THE ADDMISSION CODES AND ADMISSION DEFINATION FOR THE GIVEN APPOINTMENTS.
406 UPDATE CNC
407 SET CNC.AdmissionType = TAL.Billing_Claims_UB04_TypeOf_Admission_Code,
408 CNC.AdmissionTypeDescription = TAL.Billing_Claims_UB04_TypeOf_Admission_Definition
409 FROM
410 #tbl_Billing_Charges_Not_CreatedList CNC
411 INNER JOIN
412 tbl_Billing_Claims_UB04_TypeOf_Admission_LKP AS TAL WITH(NOLOCK) ON CNC.Billing_Claims_UB04_TypeOf_AdmissionID = TAL.Billing_Claims_UB04_TypeOf_Admission_Code
413 WHERE
414 TAL.Billing_Claims_UB04_TypeOf_Admission_InActive = 0
415 AND ISNULL(CNC.Billing_Claims_UB04_TypeOf_AdmissionID,0) > 0
416
417
418 --************ UPDATING THE ADDMISSION SOURCES AND ADMISSION SOURCES DEFINATION FOR THE GIVEN APPOINTMENTS.
419 UPDATE Flat
420 SET Flat.AdmissionSource = BS.UB04AdmissionSource,
421 Flat.AdmissionSourceDesc = ASL.Billing_Claims_UB04_Typeof_Admission_Source_Definition
422 FROM
423 #tbl_Billing_Charges_Not_CreatedList AS Flat
424 INNER JOIN
425 tbl_Billing_Settings AS BS WITH(NOLOCK) ON BS.UB04AdmissionType = Flat.AdmissionType
426 INNER JOIN
427 tbl_Billing_Claims_UB04_Admission_Source_LKP AS ASL WITH(NOLOCK) ON BS.UB04AdmissionSource = ASL.Billing_Claims_UB04_Admission_Source_Code
428 WHERE
429 BS.Inactive = 0
430 AND ASL.Billing_Claims_UB04_Typeof_Admission_Source_IsNewBorn = CASE WHEN Flat.AdmissionType = 4 THEN 1 ELSE 0 END
431 AND ISNULL(Flat.AdmissionType,0) > 0
432
433
434 --************ UPDATING THE DISCHARGE STATUS FOR THE GIVEN APPOINTMENTS.
435 UPDATE #tbl_Billing_Charges_Not_CreatedList
436 SET DischargeStatus = @DischargeStatus
437
438
439 --************ UPDATING THE HEALTHPLAN INFO FOR THE APPOINTMENTS WHICH ARE HAVING NO POLICY INFO FOR THE GIVEN APPOINTMENTS.
440 UPDATE #tbl_Billing_Charges_Not_CreatedList
441 SET HealthPlanId = @SystemDefinedInsuranceID_Patient
442 WHERE
443 ISNULL(HealthPlanId,0) = 0
444 AND ISNULL(BillTo,'') = ''
445
446
447 --************ UPDATING THE REFERAL AUTHRIZATION INFO FOR THE GIVEN APPOINTMENTS.
448 UPDATE FLAT
449 SET FLAT.ReferalAuthID = CASE
450 WHEN DATEDIFF(DD,FLAT.InPat_Adminission_BedOccupiedDate,ISNULL(DET.AuthorizationEndDate,DBO.GETEMRDATETIME())) > = 0 THEN FLAT.ReferalAuthID
451 ELSE NULL
452 END
453 FROM
454 #tbl_Billing_Charges_Not_CreatedList AS FLAT
455 INNER JOIN
456 DBO.tbl_Billing_Referal_Authorization_Details AS DET WITH(NOLOCK) ON FLAT.ReferalAuthID = DET.ReferalAuthID AND DET.InActive = 0
457 WHERE
458 ISNULL(FLAT.ReferalAuthID,0) > 0
459
460
461 --************ UPDATING THE REFERAL AUTHRIZATION INFO FOR THE GIVEN APPOINTMENTS.
462 UPDATE FLAT
463 SET FLAT.AuthNo = DET.AuthNo,
464 FLAT.AuthExpDate = DET.AuthorizationEndDate
465 FROM
466 #tbl_Billing_Charges_Not_CreatedList AS FLAT
467 LEFT JOIN
468 DBO.tbl_Billing_Referal_Authorization_Details AS DET WITH(NOLOCK) ON FLAT.ReferalAuthID = DET.ReferalAuthID AND DET.InActive = 0
469 WHERE
470 ISNULL(FLAT.ReferalAuthID,0) > 0
471
472
473 --************ UPDATING THE AGE FOR THE PATIENTS LINKED FOR THE GIVEN APPOINTMENTS.
474 UPDATE Flat
475 SET Flat.Age = DATEDIFF(yy, Per.DateofBirth, FLAT.InPat_Adminission_BedOccupiedDate) - CASE WHEN (MONTH(Per.DateofBirth) > MONTH(FLAT.InPat_Adminission_BedOccupiedDate)) OR (MONTH(PER.DateofBirth) = MONTH(FLAT.InPat_Adminission_BedOccupiedDate) AND DAY(PER.DateofBirth) > DAY(FLAT.InPat_Adminission_BedOccupiedDate)) THEN 1 ELSE 0 END
476 FROM
477 #tbl_Billing_Charges_Not_CreatedList AS Flat
478 INNER JOIN
479 dbo.tbl_Person AS PER WITH(NOLOCK) ON Flat.Patientid = PER.PersonID
480 WHERE
481 Per.InActive = 0
482
483 --********* UPDATING THE QUALIFICATION CODES OF PROVIDERS WHICH ARE LINKED TO THE GIVEN APPOINTMENTS.
484 INSERT INTO #tbl_Physicians_QualificationCodes
485 (
486 Provider_QualificationCode,
487 PhysicianID,
488 BedOccupiedDayEncounterID
489 )
490 SELECT DISTINCT --SAVING ALL THE PROVIDER QUALIFICATION CODES INFO IN ATEMP TABLE REDUCES THE FUNCTIONAL CALINGS.
491 LKP.Provider_QualificationCode,
492 Link.PhysicianID,
493 Flat.BedOccupiedDayEncounterID
494 FROM
495 dbo.tbl_Billing_Provider_Qualification_Codes_Linking AS Link WITH(NOLOCK)
496 INNER JOIN
497 dbo.tbl_Provider_QualificationCodes_LKP AS LKP WITH(NOLOCK) ON Link.Provider_QualificationCodeID = LKP.Provider_QualificationCodeID
498 AND LKP.Provider_QualificationCode_InActive = 0
499 INNER JOIN
500 #tbl_Billing_Charges_Not_CreatedList AS Flat ON Flat.PhysicianID = LINK.PhysicianID
501 WHERE
502 Link.Billing_Provider_Qualification_Code_InActive = 0
503 AND ISNULL(Flat.PhysicianID,0) > 0
504
505 ---UPDATING THE QULAIFICATION CODES IN TEMP TABLE.
506 UPDATE FLAT
507 SET FLAT.ProviderQualificationCode = STUFF((
508 SELECT
509 ', '+TEMP.Provider_QualificationCode
510 FROM
511 #tbl_Physicians_QualificationCodes AS TEMP
512 WHERE
513 FLAT.BedOccupiedDayEncounterID = TEMP.BedOccupiedDayEncounterID
514 FOR XML PATH('')),1,1,'')
515 FROM
516 #tbl_Billing_Charges_Not_CreatedList AS FLAT
517
518 --******** UPDATING THE PATIENTS ZIPCODE WHO ARE LINKED TO GIVEN APOINTMENTS.
519 UPDATE Flat
520 SET Flat.ZipCode = PA.ZipCode,
521 FLAT.Patient_State = PA.State,
522 FLAT.Patient_County = PA.County
523 FROM
524 #tbl_Billing_Charges_Not_CreatedList AS Flat
525 INNER JOIN
526 dbo.tbl_Person AS Per WITH(NOLOCK) ON Flat.Patientid = Per.PersonID
527 INNER JOIN
528 dbo.tbl_PersonAddress AS PA WITH(NOLOCK) ON PER.HomeAddressID = PA.AddressID
529 WHERE
530 Per.InActive = 0
531 /*################################################################*/
532
533 UPDATE FLAT
534 SET FLAT.Billing_Insurance_Chargecapture_CategoryInfoID = INS.Billing_Insurance_Chargecapture_CategoryInfoID
535 FROM
536 #tbl_Billing_Charges_Not_CreatedList AS FLAT
537 INNER JOIN
538 DBO.tbl_Insurance AS INS WITH(NOLOCK) ON FLAT.HealthPlanId = INS.InsuranceId
539
540 /*################################################################*/
541
542 UPDATE FLAT
543 SET FLAT.ProviderJobTitleID = Phy.Primary_Provider_JobTitleID
544 FROM
545 #tbl_Billing_Charges_Not_CreatedList AS FLAT
546 INNER JOIN
547 DBO.tbl_Physician AS Phy ON FLAT.PhysicianID = Phy.PhysicianId
548
549 --******** UPDATING THE VISIT TYPE FOR GIVEN APOINTMENTS.
550 UPDATE FLAT
551 SET FLAT.VisitType_Name = Visit.VisitType
552 FROM
553 #tbl_Billing_Charges_Not_CreatedList AS FLAT
554 INNER JOIN
555 dbo.tbl_Appointment_VisitType AS Visit WITH(NOLOCK) ON FLAT.VisitTypeID = Visit.VisitTypeID
556 WHERE
557 Visit.InActive = 0
558
559 UPDATE CNC
560 SET CNC.SlidingScale_IncomeRange_InfoID = Link.SlidingScale_IncomeRange_InfoID
561 FROM
562 #tbl_Billing_Charges_Not_CreatedList AS CNC
563 INNER JOIN
564 tbl_SlidingScale_IncomeRange_FeeScale_Patient_Linked AS Link ON CNC.Patientid = LINK.PatientID AND Link.SlidingScale_IncomeRange_FeeScale_Patient_InActive = 0
565 WHERE
566 CNC.InPat_Adminission_BedOccupiedDate BETWEEN LINK.SlidingScale_IncomeRange_FeeScale_Patient_StartDate AND COALESCE(LINK.SlidingScale_IncomeRange_FeeScale_Patient_EndDate,'12/31/9999')
567
568
569 ---UPDATING THE DURATION FOR THE APPOINTMENTS WHICH ARE HAVING THE CEHCKN TIME AND CHECKOUT TIME.
570 UPDATE Appt
571 SET Appt.Duration = DATEDIFF(MINUTE,Appt.CheckInTime,Appt.CheckOutTime)
572 FROM
573 #tbl_Billing_Charges_Not_CreatedList AS Appt
574 WHERE
575 (Appt.CheckInTime IS NOT NULL AND Appt.CheckOutTime IS NOT NULL)
576
577
578
579 --***************** CAHRGE CAPTURE DATA UPDATING FROM APPOINTMENTS NAVIGATION FOR LATEST APPOINTMENTS ***************************
580
581 UPDATE CNC
582 SET CNC.Patientid = Temp.Patientid,
583 CNC.EnrollmentID = Temp.EnrollmentID,
584 CNC.LevelofCare = Temp.LevelofCare,
585 CNC.Billing_ChargeMaster_RuleID = Temp.Billing_ChargeMaster_RuleID,
586 CNC.Units = Temp.Units,
587 CNC.Charge = Temp.Charge,
588 CNC.PatientPrimaryInsPolicyID = Temp.PatientPrimaryInsPolicyID,
589 CNC.HealthPlanId = Temp.HealthPlanId,
590 CNC.ActivityId = Temp.ActivityId,
591 CNC.ActivityName = Temp.ActivityName,
592 CNC.ActivityInfo = Temp.ActivityInfo,
593 CNC.FacilityId = Temp.FacilityId,
594 CNC.AuthNo = Temp.AuthNo,
595 CNC.ReferalAuthID = Temp.ReferalAuthID,
596 CNC.LastBilledDate = Temp.LastBilledDate,
597 CNC.TransactionID = Temp.TransactionID,
598 CNC.CPTCode = Temp.CPTCode,
599 CNC.RevenueCode = Temp.RevenueCode,
600 CNC.Modifier1 = Temp.Modifier1,
601 CNC.Modifier2 = Temp.Modifier2,
602 CNC.IsBillProfessionCharges = Temp.IsBillProfessionCharges,
603 CNC.PhysicianID = Temp.PhysicianID,
604 CNC.ResourceType = Temp.ResourceType,
605 CNC.BillingComments = Temp.BillingComments,
606 CNC.AuthRemainingVisits = Temp.AuthRemainingVisits,
607 CNC.AuthExpDate = Temp.AuthExpDate,
608 CNC.DocStatus = Temp.DocStatus,
609 CNC.ReferingProviderID = Temp.ReferingProviderID,
610 CNC.BillTo = Temp.BillTo,
611 CNC.DX1 = Temp.DX1,
612 CNC.DX2 = Temp.DX2,
613 CNC.EpisodeNumber = Temp.EpisodeNumber,
614 CNC.Duration = Temp.Duration,
615 CNC.AdmissionTime = Temp.AdmissionTime,
616 CNC.AdmissionType = Temp.AdmissionType,
617 CNC.AdmissionTypeDescription = Temp.AdmissionTypeDescription,
618 CNC.AdmissionSource = Temp.AdmissionSource,
619 CNC.AdmissionSourceDesc = Temp.AdmissionSourceDesc,
620 CNC.DischargeTime = Temp.DischargeTime,
621 CNC.DischargeStatus = Temp.DischargeStatus,
622 CNC.Billing_Claims_UB04_TypeOf_AdmissionID = Temp.Billing_Claims_UB04_TypeOf_AdmissionID,
623 CNC.Modality = Temp.ModalityDesc,
624 CNC.ProviderQualificationCode = Temp.ProviderQualificationCode,
625 CNC.VisitTypeID = Temp.VisitTypeID,
626 CNC.Programs_Services_LinkedInfo_ID = Temp.Programs_Services_LinkedInfo_ID,
627 CNC.Age = Temp.Age,
628 CNC.CheckInTime = Temp.CheckInTime,
629 CNC.CheckOutTime = Temp.CheckOutTime,
630 CNC.ProgramID = Temp.ProgramID,
631 CNC.ServiceID = Temp.ServiceID,
632 CNC.ZipCode = Temp.ZipCode,
633 CNC.AdmissionDate = Temp.AdmissionDate,
634 CNC.BillingType = Temp.BillingType,
635 CNC.InPatient_Charges_Not_CreatedList_ModifiedOn = @GetDate,
636 CNC.InPatient_Charges_Not_CreatedList_ModifiedBy = @LoggedUserID,
637 CNC.Is_Processed = 0,
638 CNC.Appointment_Status_InfoID = Temp.Appointment_Status_InfoID,
639 CNC.Appointment_Status_SystemDefined_TypeID = Temp.Appointment_Status_SystemDefined_TypeID,
640 CNC.BillToID = Temp.BillToID,
641 CNC.VisitTypeOrTherapy = Temp.VisitType_Name,
642 CNC.InPatient_Charges_Not_CreatedList_FrequencyID = NULL,
643 CNC.Billing_ReferalAuth_AllowedCptCodes_InfoID = Temp.Billing_ReferalAuth_AllowedCptCodes_InfoID,
644 CNC.ApptCancelledType = TEMP.ApptCancelledType,
645 CNC.AppointmentDuration = TEMP.AppointmentDuration,
646 CNC.CheckinCheckOutDuration = TEMP.CheckinCheckOutDuration,
647 CNC.IsBillable = TEMP.IsBillable,
648 CNC.AdmittingProviderID = TEMP.AdmittingProviderID,
649 CNC.SlidingScale_IncomeRange_InfoID = TEMP.SlidingScale_IncomeRange_InfoID,
650 CNC.Patient_State = Temp.Patient_State,
651 CNC.Patient_County = Temp.Patient_County,
652 CNC.ProviderJobTitleID = Temp.ProviderJobTitleID,
653 CNC.Billing_Insurance_Chargecapture_CategoryInfoID = Temp.Billing_Insurance_Chargecapture_CategoryInfoID
654 OUTPUT inserted.InPatient_Charges_Not_CreatedListID,inserted.BedOccupiedDayEncounterID,inserted.RecordType INTO @tbl_LatestSaved_ChargeIDs(ChargesNotCreatedListID,BedOccupiedDayEncounterID,RecordType)
655 FROM
656 tbl_Billing_Charges_Not_CreatedList AS CNC
657 INNER JOIN
658 #tbl_Billing_Charges_Not_CreatedList AS Temp ON CNC.BedOccupiedDayEncounterID = Temp.BedOccupiedDayEncounterID
659 AND CNC.RecordType = TEMP.RecordType
660 WHERE
661 CNC.TransactionID IS NULL ---WE WILL UPDATE ONLY CALIM NOT CRATED RECORDS.
662 AND CNC.InPatient_Charges_Not_CreatedList_InActive = 0
663
664 --***************** CAHRGE CAPTURE DATA SAVING FROM APPOINTMENTS NAVIGATION FOR LATEST APPOINTMENTS ***************************
665
666 INSERT INTO tbl_Billing_Charges_Not_CreatedList
667 (
668 Patientid, EnrollmentID,
669 LevelofCare, Billing_ChargeMaster_RuleID,
670 Units, Charge,
671 BedOccupiedDayEncounterID, DOS,
672 PatientPrimaryInsPolicyID, HealthPlanId,
673 ActivityId, ActivityName,
674 ActivityInfo, FacilityId,
675 AuthNo, ReferalAuthID,
676 LastBilledDate, TransactionID,
677 RecordType, InPatient_Charges_Not_CreatedList_CreatedBy ,
678 CPTCode, RevenueCode,
679 Modifier1, Modifier2,
680 IsBillProfessionCharges, PhysicianID,
681 ResourceType, VisitTypeOrTherapy,
682 BillingComments, AuthRemainingVisits,
683 AuthExpDate, DocStatus,
684 ReferingProviderID, BillTo,
685 DX1, DX2,
686 InPat_GroupTherapy_Session_Duration, EpisodeNumber,
687 Duration, AdmissionTime,
688 AdmissionType, AdmissionTypeDescription,
689 AdmissionSource, AdmissionSourceDesc,
690 DischargeTime, DischargeStatus,
691 Billing_Claims_UB04_TypeOf_AdmissionID, Modality,
692 ProviderQualificationCode, VisitTypeID,
693 Programs_Services_LinkedInfo_ID, Age,
694 CheckInTime, CheckOutTime,
695 ProgramID, ServiceID,
696 ZipCode, AdmissionDate,
697 BillingType, Appointment_Status_InfoID,
698 Appointment_Status_SystemDefined_TypeID, BillToID,
699 Billing_ReferalAuth_AllowedCptCodes_InfoID, ApptCancelledType,
700 AppointmentDuration, CheckinCheckOutDuration,
701 IsBillable, AdmittingProviderID,
702 SlidingScale_IncomeRange_InfoID,
703 Patient_State,
704 Patient_County ,
705 ProviderJobTitleID,
706 Billing_Insurance_Chargecapture_CategoryInfoID
707 )
708 OUTPUT inserted.InPatient_Charges_Not_CreatedListID,inserted.BedOccupiedDayEncounterID INTO @tbl_LatestSaved_ChargeIDs(ChargesNotCreatedListID,BedOccupiedDayEncounterID)
709 SELECT
710 FLAT.Patientid, FLAT.EnrollmentID,
711 FLAT.LevelofCare, FLAT.Billing_ChargeMaster_RuleID,
712 FLAT.Units, FLAT.Charge,
713 FLAT.BedOccupiedDayEncounterID, FLAT.InPat_Adminission_BedOccupiedDate,
714 FLAT.PatientPrimaryInsPolicyID, FLAT.HealthPlanId,
715 FLAT.ActivityId, FLAT.ActivityName,
716 FLAT.ActivityInfo, FLAT.FacilityId,
717 FLAT.AuthNo, FLAT.ReferalAuthID,
718 FLAT.LastBilledDate, FLAT.TransactionID,
719 FLAT.RecordType, @LoggedUserID ,
720 FLAT.CPTCode, FLAT.RevenueCode,
721 FLAT.Modifier1, FLAT.Modifier2,
722 FLAT.IsBillProfessionCharges, FLAT.PhysicianID,
723 FLAT.ResourceType, FLAT.VisitType_Name,
724 FLAT.BillingComments, FLAT.AuthRemainingVisits,
725 FLAT.AuthExpDate, FLAT.DocStatus,
726 FLAT.ReferingProviderID, FLAT.BillTo,
727 FLAT.DX1, FLAT.DX2,
728 FLAT.Duration, FLAT.EpisodeNumber,
729 FLAT.Duration, FLAT.AdmissionTime,
730 FLAT.AdmissionType, FLAT.AdmissionTypeDescription,
731 FLAT.AdmissionSource, FLAT.AdmissionSourceDesc,
732 FLAT.DischargeTime, FLAT.DischargeStatus,
733 FLAT.Billing_Claims_UB04_TypeOf_AdmissionID, FLAT.ModalityDesc,
734 FLAT.ProviderQualificationCode, FLAT.VisitTypeID,
735 FLAT.Programs_Services_LinkedInfo_ID, FLAT.Age,
736 FLAT.CheckInTime, FLAT.CheckOutTime,
737 FLAT.ProgramID, FLAT.ServiceID,
738 FLAT.ZipCode, FLAT.AdmissionDate,
739 FLAT.BillingType, FLAT.Appointment_Status_InfoID,
740 FLAT.Appointment_Status_SystemDefined_TypeID, FLAT.BillToID,
741 FLAT.Billing_ReferalAuth_AllowedCptCodes_InfoID, FLAT.ApptCancelledType,
742 FLAT.AppointmentDuration, FLAT.CheckinCheckOutDuration ,
743 FLAT.IsBillable, FLAT.AdmittingProviderID,
744 FLAT.SlidingScale_IncomeRange_InfoID,
745 FLAT.Patient_State,
746 FLAT.Patient_County ,
747 FLAT.ProviderJobTitleID,
748 FLAT.Billing_Insurance_Chargecapture_CategoryInfoID
749 FROM
750 #tbl_Billing_Charges_Not_CreatedList AS FLAT
751 LEFT JOIN
752 tbl_Billing_Charges_Not_CreatedList AS CNC WITH(NOLOCK )ON CNC.BedOccupiedDayEncounterID = FLAT.BedOccupiedDayEncounterID
753 AND CNC.RecordType = FLAT.RecordType
754 AND CNC.InPatient_Charges_Not_CreatedList_InActive = 0
755 WHERE
756 CNC.InPatient_Charges_Not_CreatedListID IS NULL
757
758 DECLARE
759 @BedOccupiedDayEncounterIDs VARCHAR(8000)
760
761 SELECT
762 @BedOccupiedDayEncounterIDs = COALESCE(@BedOccupiedDayEncounterIDs+',','')+CAST(CNC.BedOccupiedDayEncounterID AS VARCHAR(12))
763 FROM
764 tbl_Billing_Charges_Not_CreatedList AS CNC WITH(NOLOCK)
765 INNER JOIN
766 @tbl_Given_Appointments AS APPT ON CNC.BedOccupiedDayEncounterID = APPT.AppointmentID
767 LEFT JOIN
768 #tbl_Billing_Charges_Not_CreatedList AS TEMP ON CNC.BedOccupiedDayEncounterID = TEMP.BedOccupiedDayEncounterID
769 AND CNC.RecordType = TEMP.RecordType
770 AND ISNULL(CNC.Programs_Services_LinkedInfo_ID,0) = ISNULL(TEMP.Programs_Services_LinkedInfo_ID,0)
771 WHERE
772 TEMP.BedOccupiedDayEncounterID IS NULL
773 AND CNC.RecordType = 16
774 AND CNC.TransactionID IS NULL
775 AND CNC.Is_SuperBill_Created = 0
776
777 IF(ISNULL(@BedOccupiedDayEncounterIDs,'') <> '')
778 BEGIN
779
780 EXEC usp_Billing_Charges_Not_CreatedList_Common_Delete_Web NULL ,NULL,NULL,16,@BedOccupiedDayEncounterIDs,@LoggedUserID,0
781
782 END
783
784
785 --SAVING INTO THE TEMP TABLE FOR GOIVEN ENROOLLMENT LINKED DXCODES
786 INSERT INTO #tbl_LatestDxCodes
787 (
788 InPatient_Charges_Not_CreatedListID, DXCode,
789 DxType, Billing_Charges_Not_CreatedList_DxLinked_CreatedBy,
790 RecordType, EnrollmentID,
791 BedOccupiedDayEncounterID
792 )
793 SELECT
794 InPatient_Charges_Not_CreatedListID, DxCode,
795 DxType, @LoggedUserID,
796 RecordType, EnrollmentID,
797 BedOccupiedDayEncounterID
798 FROM
799 (
800 SELECT
801 TEMP.InPatient_Charges_Not_CreatedListID,
802 DIA.DxCode,
803 ISNULL(CSV.ID,0) AS DxType, --1.AdmitDx,2.OtherDX
804 TEMP.RecordType,
805 TEMP.EnrollmentID,
806 ROW_NUMBER()OVER(PARTITION BY TEMP.InPatient_Charges_Not_CreatedListID,DIA.DxCode,ISNULL(CSV.ID,0) ORDER BY DxLink.InPat_CareLevel_Event_Patient_Category_LinkedInfoID) AS RowNo,
807 InPat_CareLevel_Event_Patient_Category_LinkedInfoID,
808 TEMP.BedOccupiedDayEncounterID
809 FROM
810 dbo.tbl_InPatient_CareLevel_Events_Patients_Category_LinkedInfo AS DxLink WITH(NOLOCK)
811 CROSS APPLY
812 DBO.udf_CSV_ToTable2(ISNULL(DxLink.DxType,'0'),',') AS CSV
813 INNER JOIN
814 dbo.tbl_Programs_InOrganization AS PRGRM WITH(NOLOCK) ON DxLink.ProgramID = PRGRM.ProgramID
815 INNER JOIN
816 dbo.tbl_Diagnosis_BriefDes_Info_LKP AS BRIEF WITH(NOLOCK) ON PRGRM.DiagnosisBriefDescID = BRIEF.DiagnosisBriefDescID AND BRIEF.DiagnosisBriefDesc_InActive = 0
817 INNER JOIN
818 dbo.tbl_Diagnosis AS DIA ON BRIEF.DiagnosisID = DIA.DiagnosisId AND DIA.Inactive = 0
819 INNER JOIN
820 tbl_Billing_Charges_Not_CreatedList AS TEMP ON DxLink.InPat_CareLevel_Event_Patient_InfoID = TEMP.EnrollmentID
821 INNER JOIN
822 #tbl_Billing_Charges_Not_CreatedList AS NC ON NC.BedOccupiedDayEncounterID = TEMP.BedOccupiedDayEncounterID
823 WHERE
824 DxLink.InPat_CareLevel_Event_Patient_Category_LinkedInfo_InActive = 0
825 AND NC.RecordType = NC.RecordType
826 AND TEMP.InPatient_Charges_Not_CreatedList_InActive = 0
827
828 )X
829 WHERE
830 X.RowNo = 1
831 ORDER BY
832 X.InPat_CareLevel_Event_Patient_Category_LinkedInfoID
833
834 ----NEW BLOCK REGARDING APPT DXCODES
835 INSERT INTO #tbl_LatestDxCodes
836 (
837 InPatient_Charges_Not_CreatedListID, DXCode,
838 DxType, Billing_Charges_Not_CreatedList_DxLinked_CreatedBy,
839 RecordType, EnrollmentID,
840 BedOccupiedDayEncounterID
841 )
842 SELECT
843 NOTCRED.ChargesNotCreatedListID, APTDX.DxCode,
844 4, @LoggedUserID,
845 NOTCRED.Recordtype, NULL,
846 APTDX.AppointmentID
847 FROM
848 DBO.tbl_Appointments_ICDCodes_Linking AS APTDX WITH(NOLOCK)
849 INNER JOIN
850 @tbl_LatestSaved_ChargeIDs AS NOTCRED ON APTDX.AppointmentID = NOTCRED.BedOccupiedDayEncounterID
851 LEFT JOIN
852 #tbl_LatestDxCodes AS TEMP ON APTDX.AppointmentID = TEMP.BedOccupiedDayEncounterID AND TEMP.DxType = 2
853 WHERE
854 TEMP.DXCode IS NULL
855 AND APTDX.Appointments_ICDCodes_Linking_InActive = 0
856
857 UNION
858
859 SELECT
860 NOTCRED.ChargesNotCreatedListID, DIG.DxCode,
861 4, @LoggedUserID,
862 NOTCRED.Recordtype, NULL,
863 ADX.AppointmentID
864 FROM
865 @tbl_LatestSaved_ChargeIDs AS NOTCRED
866 INNER JOIN
867 tbl_Appointments_CheckOut_BillableDxCodes_Linked ADX WITH(NOLOCK) ON ADX.AppointmentID = NOTCRED.BedOccupiedDayEncounterID
868 INNER JOIN
869 tbl_problemlist PL WITH(NOLOCK) ON ADX.ProblemListID = PL.ProblemListID --AND PL.IsActive = 1
870 INNER JOIN
871 tbl_Diagnosis_BriefDes_Info_LKP DBI WITH(NOLOCK) ON PL.DiagnosisBriefDescID = DBI.DiagnosisBriefDescID AND DBI.DiagnosisBriefDesc_InActive = 0
872 INNER JOIN
873 tbl_Diagnosis Dig WITH(NOLOCK) ON DBI.DiagnosisId = DIG.DiagnosisId AND DIG.Inactive = 0
874 LEFT JOIN
875 #tbl_LatestDxCodes AS TEMP ON ADX.AppointmentID = TEMP.BedOccupiedDayEncounterID AND TEMP.DxType = 2
876 WHERE
877 Appt_CheckOut_BillableDxCodes_Linked_InActive = 0
878
879 --RENMOVING THE OLD DXCODES INFO
880 UPDATE Link
881 SET Link.Billing_Charges_Not_CreatedList_DxLinked_InActive = 1,
882 Link.Billing_Charges_Not_CreatedList_DxLinked_ModifiedOn = @GetDate,
883 Link.Billing_Charges_Not_CreatedList_DxLinked_ModifiedBy = @loggedUserID
884 FROM
885 tbl_Billing_Charges_Not_CreatedList_DxLinking_Info AS Link
886 INNER JOIN
887 @tbl_LatestSaved_ChargeIDs AS NOTCRED ON Link.InPatient_Charges_Not_CreatedListID = NOTCRED.ChargesNotCreatedListID
888 LEFT JOIN
889 #tbl_LatestDxCodes AS TEMP ON LINK.DXCode = TEMP.DxCode
890 AND LINK.DxType = TEMP.DxType
891 AND LINK.InPatient_Charges_Not_CreatedListID = TEMP.InPatient_Charges_Not_CreatedListID
892 WHERE
893 LINK.Billing_Charges_Not_CreatedList_DxLinked_InActive = 0
894 AND TEMP.InPatient_Charges_Not_CreatedListID IS NULL
895 AND LINK.RecordType = NOTCRED.Recordtype
896
897 --SAVING INTO THE LATEST DXCODES INFO
898 INSERT INTO tbl_Billing_Charges_Not_CreatedList_DxLinking_Info
899 (
900 EnrollmentID,
901 DXCode,
902 DxType,
903 Billing_Charges_Not_CreatedList_DxLinked_CreatedBy,
904 RecordType,
905 InPatient_Charges_Not_CreatedListID
906 )
907 SELECT
908 TEMP.EnrollmentID,
909 TEMP.DxCode,
910 ISNULL(TEMP.DxType,0) AS DxType,
911 @loggedUserID,
912 TEMP.RecordType,
913 TEMP.InPatient_Charges_Not_CreatedListID
914 FROM
915 #tbl_LatestDxCodes AS TEMP
916 LEFT JOIN
917 tbl_Billing_Charges_Not_CreatedList_DxLinking_Info AS DxLink ON TEMP.DxCode = DxLink.DXCode
918 AND TEMP.DxType = DxLink.DxType
919 AND TEMP.RecordType = TEMP.RecordType
920 AND TEMP.InPatient_Charges_Not_CreatedListID = DxLink.InPatient_Charges_Not_CreatedListID
921 AND DxLink.Billing_Charges_Not_CreatedList_DxLinked_InActive = 0
922 WHERE
923 DXLINK.Billing_Charges_Not_CreatedList_DxLinkedID IS NULL
924
925 --**************** THIS BLOCK IS USED TO UPDATE THE TRANSACMISIION DATE FOR SAVED RECORDS **************************
926
927 --DECLARAING THE VARIBLES TO GET THE LOOPID TO GENERATE THE LOOP.
928 DECLARE
929 @MinID INT,
930 @InPatient_Charges_Not_CreatedListID INT
931
932 --THIS SLECT ID IS USED TO LATEST LOOPID.
933 SELECT
934 @MinID = MIN(ChargesNotCreatedListID)
935 FROM
936 @tbl_LatestSaved_ChargeIDs
937
938
939 WHILE(@MinID IS NOT NULL)
940 BEGIN
941
942 SELECT
943 @InPatient_Charges_Not_CreatedListID = NULL
944
945 SELECT
946 @InPatient_Charges_Not_CreatedListID = @MinID
947
948 EXEC USP_Billing_Charges_Not_CreatedList_TransmissionDate_Update NULL,NULL,NULL,@InPatient_Charges_Not_CreatedListID,@LoggedUserID
949
950 SELECT
951 @MinID = MIN(ChargesNotCreatedListID)
952 FROM
953 @tbl_LatestSaved_ChargeIDs
954 WHERE
955 ChargesNotCreatedListID > @MinID
956
957 END
958
959 DECLARE
960 @LatestSavedIDs VARCHAR(4096)
961
962 SELECT
963 @LatestSavedIDs = COALESCE(@LatestSavedIDs+',','')+CAST(ChargesNotCreatedListID AS VARCHAR(16))
964 FROM
965 @tbl_LatestSaved_ChargeIDs
966
967 EXEC USP_Billing_Charge_Capture_CC1_InsertorUpdate_WEB NULL,NULL,NULL,@LatestSavedIDs,@LoggedUserID
968
969 ---DROOPING ALL THE TEP WHICH ARE USED IN ABOVE QUERY
970 IF OBJECT_ID('tempdb..#tbl_ChargeCapture_Locations') IS NOT NULL
971 DROP TABLE #tbl_ChargeCapture_Locations
972
973 IF OBJECT_ID('tempdb..#tbl_Billing_Charges_Not_CreatedList') IS NOT NULL
974 DROP TABLE #tbl_Billing_Charges_Not_CreatedList
975
976 IF OBJECT_ID('tempdb..#tbl_Parameters_Values') IS NOT NULL
977 TRUNCATE TABLE #tbl_Parameters_Values
978
979 IF OBJECT_ID('tempdb..#tbl_LatestDxCodes') IS NOT NULL
980 DROP TABLE #tbl_LatestDxCodes
981
982 IF OBJECT_ID('tempdb..#tbl_Physicians_QualificationCodes') IS NOT NULL
983 DROP TABLE #tbl_Physicians_QualificationCodes
984
985 END
986
987 --************************** UPDATING TEH OBOSLOATE INFO FOR GIVEN LATEST SAVED AND MODIFIED RECORDS ******************************
988 UPDATE CNC
989 SET CNC.Is_Processed = 2,
990 CNC.Discount = NULL
991 FROM
992 tbl_Billing_Charges_Not_CreatedList AS CNC
993 INNER JOIN
994 @tbl_Given_Appointments GAPT ON CNC.BedOccupiedDayEncounterID = GAPT.AppointmentID
995 INNER JOIN
996 dbo.tbl_Appointments apt ON GAPT.AppointmentID = APT.AppointmentID
997 LEFT JOIN
998 (
999 SELECT
1000 Cus.Billing_Charge_Capture_Location_CustomizationInfoID,
1001 CASE
1002 WHEN LKP.Appointment_Status_SystemDefined_TypeID = 51 THEN @Appt_CancleStatus_ID
1003 ELSE Billing_Charge_Capture_Location_Module_Or_FormID
1004 END AS Billing_Charge_Capture_Location_Module_Or_FormID,
1005 CUS.ProgramID,
1006 CUS.ServiceID,
1007 CUST.CancelledBy_UserType,
1008 CUST.Appts_CancellationCharges_Customization_InfoID,
1009 LKP.Appointment_Status_SystemDefined_TypeID
1010 FROM
1011 tbl_Billing_Charge_Capture_Locations_Customization AS Cus WITH(NOLOCK)
1012 INNER JOIN
1013 dbo.tbl_Appointments_Status_LKP AS LKP WITH(NOLOCK) ON Cus.Billing_Charge_Capture_Location_Module_Or_FormID = LKP.Appointment_Status_InfoID
1014 LEFT JOIN
1015 (
1016 SELECT
1017 Billing_Charge_Capture_Locations_CancelledTypes_Linking_InfoID,
1018 Billing_Charge_Capture_Location_CustomizationInfoID,
1019 Appts_CancellationCharges_Customization_InfoID,
1020 CancelledBy_UserType
1021 FROM
1022 tbl_Billing_Charge_Capture_Locations_CancelledTypes_Linking_Info
1023 WHERE
1024 Billing_Charge_Capture_Locations_CancelledTypes_Linking_Info_InActive = 0
1025 )CUST ON CUS.Billing_Charge_Capture_Location_CustomizationInfoID = CUST.Billing_Charge_Capture_Location_CustomizationInfoID
1026 WHERE
1027 Billing_Charge_Capture_Location_Module_Or_FormType = 21 --APPOINTMENTS MODULE
1028 AND Billing_Charge_Capture_Location_CustomizationInfo_InActive = 0
1029 )LOC ON CNC.Appointment_Status_InfoID = LOC.Billing_Charge_Capture_Location_Module_Or_FormID
1030 AND CNC.ProgramID = CASE WHEN LOC.ProgramID = -1 THEN CNC.ProgramID ELSE LOC.ProgramID END
1031 AND (CNC.ServiceID IS NULL OR (CNC.ServiceID IS NOT NULL AND CNC.ServiceID = CASE WHEN LOC.ServiceID = -1 THEN CNC.ServiceID ELSE LOC.ServiceID END))
1032 AND (
1033 (
1034 CNC.Appointment_Status_InfoID = 3
1035 --AND ISNULL(APT.CancelledRequestedBy,0) = ISNULL(LOC.CancelledBy_UserType,0)
1036 AND
1037 (
1038 (LOC.Appointment_Status_SystemDefined_TypeID IN (4,51) AND (ISNULL(APT.CancelledRequestedBy,0) = ISNULL(LOC.CancelledBy_UserType,0)))
1039 OR (LOC.Appointment_Status_SystemDefined_TypeID NOT IN (4,51))
1040 )
1041 AND (
1042 (@IsCancelFormat2 = 0 AND ISNULL(CNC.ApptCancelledType,0) = ISNULL(LOC.Appts_CancellationCharges_Customization_InfoID,0))
1043 OR (@IsCancelFormat2 = 1)
1044 )
1045 )
1046 OR CNC.Appointment_Status_InfoID <> 3
1047 )
1048 WHERE
1049 CNC.RecordType IN (3,16)
1050 AND LOC.Billing_Charge_Capture_Location_CustomizationInfoID IS NULL
1051 AND CNC.InPatient_Charges_Not_CreatedList_InActive = 0
1052 AND CNC.TransactionID IS NULL
1053
1054 UPDATE CNC
1055 SET CNC.Is_Processed = 2,
1056 CNC.DoNotConsiderForChargeCapture = 1,
1057 CNC.Discount = NULL
1058 FROM
1059 tbl_Billing_Charges_Not_CreatedList CNC
1060 INNER JOIN
1061 @tbl_Given_Appointments GAPT ON CNC.BedOccupiedDayEncounterID = GAPT.AppointmentID
1062 INNER JOIN
1063 dbo.QRY_InPatient_CareLevel_Events_Patients_Latest_Admission_Status PAS ON CNC.EnrollmentID = PAS.InPat_CareLevel_Event_Patient_InfoID
1064 WHERE
1065 RecordType IN (3,16)
1066 AND CNC.InPatient_Charges_Not_CreatedList_InActive = 0
1067
1068 UPDATE CC
1069 SET CC.BillToID = 3,
1070 CC.Billto = 'Cash Pmt'
1071 FROM
1072 tbl_Billing_Charges_Not_CreatedList AS CC
1073 INNER JOIN
1074 @tbl_Given_Appointments AS GAPT ON CC.BedOccupiedDayEncounterID = GAPT.AppointmentID
1075 WHERE
1076 RecordType IN (3,16)
1077 AND Appointment_Status_SystemDefined_TypeID IN (3,4)
1078 AND CC.InPatient_Charges_Not_CreatedList_InActive = 0
1079
1080 IF(@IsCancelledAppt = 1)
1081 BEGIN
1082
1083 DECLARE
1084 @Min_ApptID INT
1085
1086 SELECT
1087 @AppointmentID = NULL
1088
1089 SELECT @Min_ApptID = MIN(ID) FROM @tbl_Given_Appointments
1090
1091 WHILE(@Min_ApptID IS NOT NULL)
1092 BEGIN
1093
1094 SELECT
1095 @AppointmentID = AppointmentID
1096 FROM
1097 @tbl_Given_Appointments
1098 WHERE
1099 ID = @Min_ApptID
1100
1101 EXEC USP_EMR_Visited_Billing_Charges_Not_CreatedList_Insert_AllTabs NULL,NULL,NULL,@AppointmentID,@LoggedUserID
1102
1103 SELECT @Min_ApptID = MIN(ID) FROM @tbl_Given_Appointments WHERE ID > @Min_ApptID
1104
1105 END
1106
1107 END
1108
1109 IF(@Billing_ChargeCaptured_Status_InfoID IS NOT NULL)
1110 BEGIN
1111
1112 UPDATE tbl_Billing_ChargeCapture_Status_Info
1113 SET ActionStatus = 1,
1114 ModifiedOn = @GetDate,
1115 ModifiedBy = @LoggedUserID
1116 WHERE
1117 Billing_ChargeCaptured_Status_InfoID = @Billing_ChargeCaptured_Status_InfoID
1118
1119 END
1120
1121 COMMIT TRANSACTION
1122
1123 -- here ends the try block.
1124 END TRY
1125
1126 /*When an error condition is detected in a SQL statement contained in a TRY block,
1127 control is passed to a CATCH block where it can be processed. */
1128 BEGIN CATCH
1129
1130 IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
1131
1132 /*This Part of the SP is a Mandotary.
1133 Which makes the Application to know that the functionality of the sp is not fullfilled successfully as per the code in SP and the Input parameters
1134 The Reason may be some bug in the Developed logic or the functionality or even in the parameters.*/
1135 SELECT @return = -1,
1136 @errorID = ERROR_NUMBER(),
1137 @errorMessage = ERROR_MESSAGE() -- RETURNS the Error message
1138
1139 /*This Part of the SP is a Mandotary.
1140 Which makes the DBServer to know that the functionality of the sp is not fullfilled successfully as per the code in SP and the Input parameters
1141 The Reason may be some bug in the Developed logic or the functionality or even in the parameters.
1142 The Error is recorded in the database to rectify it if necessary,
1143 saving the error details to the table tbl_Common_errorLog*/
1144 EXEC usp_common_errorlog_insert @errorID, @errorMessage, 'USP_EMR_Visited_Billing_Charges_Not_CreatedList_Insert_OnetoOneAppt', @loggedUserID
1145
1146 RETURN -1
1147 --It will terminates from the SP
1148 --HERE ends the catch block.
1149 END CATCH
1150
1151 /*This Part of the SP is a Mandotary.
1152 Which makes the Application to know that the functionality of the sp is fullfilled successfully as per the code in SP and the Input parameters*/
1153 SELECT @return = 0,
1154 @errorid = 0,
1155 @errormessage = NULL
1156
1157END
1158-- Makes the number of row count enable
1159SET NOCOUNT OFF
1160
1161/*
1162VERSION DETAILS
1163--------------------------------------------------------------------------------
1164Version ModifiedDate ModifiedBy Purpose
1165-------------------------------------------------------------------------------
1166*/