· 9 years ago · Dec 07, 2016, 04:59 PM
1
2
3SET QUOTED_IDENTIFIER ON
4go
5SET ANSI_NULLS ON
6go
7IF OBJECT_ID('dbo.usp_bilAnsiEf') IS NOT NULL
8BEGIN
9 DROP PROCEDURE dbo.usp_bilAnsiEf
10 IF OBJECT_ID('dbo.usp_bilAnsiEf') IS NOT NULL
11 RAISERROR ('<<< FAILED DROPPING PROCEDURE dbo.usp_bilAnsiEf >>>',0,0)
12 ELSE
13 RAISERROR ('<<< DROPPED PROCEDURE dbo.usp_bilAnsiEf >>>',0,0)
14END
15go
16
17CREATE proc dbo.usp_bilAnsiEf
18(
19 @EFType char(10),
20 @EFBatch_Number int,
21 @MedicalFacility_ID varchar(10),
22 @ClearingHouseName varchar(100),
23 @SecurityInformation varchar(10),
24 @ReceiverID varchar(15),
25 @ApplicationSenderCode varchar(15),
26 @ApplicationReceiverCode varchar(15),
27 @SubmitterID varchar(80),
28 @PurposeCode varchar(2),
29 @IsBillingService char(1),
30 @TransmisstionType char(1), -- Piloting='D', Production=''
31 @Type varchar(10),
32 @NoCredit char(1),
33 @ClearingHouse_ID varchar(10),
34 @AcknowledgmentRequest char(1),
35 @WithProviderLoop char(1),
36 @LineStatus char(2),
37 @AnsiFormat char(1) = '4', -- '4' for 4010, '5' for 5010 ANSI Format
38 @SortByRVU bit = 0
39)
40as
41begin
42
43
44declare @invoice_ID varchar(10)
45
46CREATE TABLE #BILL_InvoiceCPT
47 (
48 InvoiceCPT_ID char(10) not null primary key ,
49 InvoiceCPT_RevenueCode varchar(4) ,
50 InvoiceCPT_RecordState tinyint ,
51 Invoice_ID char(10) ,
52 InvoiceCPT_ICDCode1 varchar(9) ,
53 InvoiceCPT_ICDCode2 varchar(9) ,
54 InvoiceCPT_ICDCode3 varchar(9) ,
55 InvoiceCPT_Cost money ,
56 InvoiceCPT_Unit decimal(10, 2) ,
57 InvoiceCPT_UnitFee money ,
58 InvoiceCPT_FeeAmount money ,
59 InvoiceCPT_RVU decimal(10, 2) ,
60 InvoiceCPT_Dosage varchar(30) ,
61 InvoiceCPT_StartSerDate datetime ,
62 InvoiceCPT_EndSerDate datetime ,
63 InvoiceCPT_Code varchar(10) ,
64 InvoiceCPT_StartTime char(10) ,
65 InvoiceCPT_EndTime char(10) ,
66 InvoiceCPT_PurchasingInd char(1) ,
67 InvoiceCPT_Narrative varchar(280) ,
68 InvoiceCPT_AllowedFee money ,
69 InvoiceCPT_AllowedUnitFee money ,
70 InvoiceCPT_TypeOfServiceCode char(2) ,
71 InvoiceCPT_PlaceOfServiceCode char(2) ,
72 InvoiceCPT_ModifierCode1 char(2) ,
73 InvoiceCPT_ModifierCode4 char(2) ,
74 InvoiceCPT_ModifierCode3 char(2) ,
75 InvoiceCPT_ModifierCode2 char(2) ,
76 InvoiceCPT_ICDCode4 varchar(9) ,
77 InvoiceCPT_Sequence tinyint ,
78 InvoiceCPT_UnitCode char(2) ,
79 InvoiceCPT_CodeType tinyint ,
80 InvoiceCPT_CodeBillDescription varchar(60) ,
81 InvoiceCPT_IsBillable bit ,
82 InvoiceCPT_AllowOverwrite bit ,
83 InvoiceCPT_IsProcessed bit ,
84 MedicalFacility_ID char(10) ,
85 InvoiceCPT_FileStatus char(2) ,
86 InvoiceCPT_RollupRevenueCode bit ,
87 InvoiceCPT_Claim bit ,
88 InvoiceCPT_BundleCPTCode varchar(7) ,
89 InvoiceCPT_Taxable bit ,
90 InvoiceCPT_Capitated bit ,
91 InvoiceCPT_GPrintClaim bit ,
92 InvoiceCPT_CoInsDue bit ,
93 CPTFeeSchdDetail_ID char(10) ,
94 HCPCSFeeSchdDetail_ID char(10) ,
95 InvoiceCPT_IsICD10 bit
96);
97
98
99-- BILL_EFBatch temp table
100create table #BILL_EFBatch
101(
102EFBatch_ID char(10) not null primary key,
103EFBatch_RecordState tinyint,
104EFBatch_Number int,
105EFBatch_ItemNum int,
106Patient_ID char(10),
107Provider_ID char(10),
108InsuranceCompany_ID char(10),
109EFBatch_ErrorNum char(25),
110EFBatch_ProcessedFG char(1),
111EFBatch_FileStatus char(2),
112EFBatch_BatchDate datetime,
113EFBatch_InsAsgn int,
114FinancialGroup_ID char(10),
115Invoice_ID char(10),
116EFBatch_SupervisorHealthcare_ID char(10),
117Guarantor_ID char(10),
118EFBatch_PageCount int,
119EFBatch_UseX12nV5010 bit,
120InvoiceIns_InsuranceClassICD10StartDate datetime,
121Invoice_MedicalFacility_ID char(10),
122Invoice_Specialty_ID char(10),
123Invoice_SupervisorHealthcare_ID char(10),
124ExceptionCPTFeeSchedule_ID char(10) default '0000000000',
125DefaultFeeSchedule_ID char(10) default '0000000000',
126PayorType_ID char(10)
127);
128
129
130
131create table #TMP_EFW_EFLineItem
132 (
133 EFLineItem_ID int IDENTITY(1,1) primary key ,
134 EFLineItem_BatchNo int ,
135 InvoiceCPT_ID char(10) ,
136 EFLineItem_RevenueCode char(4) ,
137 Invoice_ID char(10) ,
138 EFLineItem_ICDCode1 varchar(9) ,
139 EFLineItem_ICDCode2 varchar(9) ,
140 EFLineItem_ICDCode3 varchar(9) ,
141 EFLineItem_ICDCode4 varchar(9) ,
142 EFLineItem_Cost money ,
143 EFLineItem_Unit decimal(18, 2) ,
144 EFLineItem_UnitFee money ,
145 EFLineItem_FeeAmount money ,
146 EFLineItem_RVU decimal(18, 2) ,
147 EFLineItem_Dosage varchar(30) ,
148 EFLineItem_StartSerDate datetime ,
149 EFLineItem_EndSerDate datetime ,
150 EFLineItem_Code varchar(10) ,
151 EFLineItem_StartTime char(10) ,
152 EFLineItem_EndTime char(10) ,
153 EFLineItem_PurchasingInd char(1) ,
154 EFLineItem_Narrative varchar(280) ,
155 EFLineItem_AllowedFee money ,
156 EFLineItem_AllowedUnitFee money ,
157 EFLineItem_PlaceOfServiceCode char(2) ,
158 EFLineItem_TypeOfServiceCode char(2) ,
159 EFLineItem_ModifierCode1 char(2) ,
160 EFLineItem_ModifierCode2 char(2) ,
161 EFLineItem_ModifierCode3 char(2) ,
162 EFLineItem_ModifierCode4 char(2) ,
163 EFLineItem_Sequence tinyint ,
164 EFLineItem_UnitCode char(2) ,
165 EFLineItem_CodeType char(2) ,
166 EFLineItem_CodeBillDescription varchar(60) ,
167 EFLineItem_RevenueRollUp bit
168 )
169
170
171create table #CPT_CPTFeeSchdDetail
172(
173CPTFeeSchdDetail_ID char(10) not null primary key,
174CPTFeeSchdDetail_RecordState tinyint,
175RollupCPTModifier_ID char(10),
176CPTFeeSchdDetail_RollupRevenueCode bit,
177CPT_ID3 char(10),
178RollupCPT_ID char(10)
179);
180
181
182create table #HCPC_HCPCSFeeSchdDetail
183(
184HCPCSFeeSchdDetail_ID char(10) not null primary key,
185HCPCSFeeSchdDetail_RecordState tinyint,
186RollupCPTModifier_ID char(10),
187HCPCSFeeSchdDetail_RollupRevenueCode bit,
188HCPCS_ID3 char(10),
189RollupCPT_ID char(10)
190);
191
192-- get all batch records from #BILL_EFBatch by @EFBatch_Number
193
194insert into #BILL_EFBatch
195 (
196 EFBatch_ID,
197 EFBatch_RecordState,
198 EFBatch_Number,
199 EFBatch_ItemNum,
200 Patient_ID,
201 Provider_ID,
202 InsuranceCompany_ID,
203 EFBatch_ErrorNum,
204 EFBatch_ProcessedFG,
205 EFBatch_FileStatus,
206 EFBatch_BatchDate,
207 EFBatch_InsAsgn,
208 FinancialGroup_ID,
209 Invoice_ID,
210 EFBatch_SupervisorHealthcare_ID,
211 Guarantor_ID,
212 EFBatch_PageCount,
213 EFBatch_UseX12nV5010,
214 InvoiceIns_InsuranceClassICD10StartDate,
215 Invoice_MedicalFacility_ID,
216 Invoice_SupervisorHealthcare_ID,
217 PayorType_ID
218 )
219select a.EFBatch_ID,
220 a.EFBatch_RecordState,
221 a.EFBatch_Number,
222 a.EFBatch_ItemNum,
223 a.Patient_ID,
224 a.Provider_ID,
225 a.InsuranceCompany_ID,
226 a.EFBatch_ErrorNum,
227 a.EFBatch_ProcessedFG,
228 a.EFBatch_FileStatus,
229 a.EFBatch_BatchDate,
230 a.EFBatch_InsAsgn,
231 a.FinancialGroup_ID,
232 a.Invoice_ID,
233 a.EFBatch_SupervisorHealthcare_ID,
234 b.Guarantor_ID,
235 a.EFBatch_PageCount,
236 a.EFBatch_UseX12nV5010,
237 b.InvoiceIns_InsuranceClassICD10StartDate,
238 bi.MedicalFacility_ID,
239 bi.Invoice_SupervisorHealthcare_ID,
240 c.PayorType_ID
241from BILL_EFBatch a
242 join
243 Bill_Invoice bi on a.Invoice_ID = bi.Invoice_ID
244 join ENTY_InsuranceCompany c on c.InsuranceCompany_ID = a.InsuranceCompany_ID
245 left join
246 BILL_InvoiceIns b on a.Invoice_ID = b.Invoice_ID and a.InsuranceCompany_ID = b.InsuranceCompany_ID and a.EFBatch_InsAsgn = b.InvoiceIns_InsAsgn
247where a.EFBatch_Number = @EFBatch_Number
248and bi.Invoice_RecordState <> 1;
249
250
251
252update a
253set a.Invoice_Specialty_ID = b.Specialty_ID
254from #BILL_EFBatch a
255 join Link_Provider_Specialty b on a.Provider_ID = b.Provider_ID
256where b.Link_Default = '1';
257
258update a
259set a.ExceptionCPTFeeSchedule_ID = b.Exception_CPTFeeSchedule_ID
260from #BILL_EFBatch a
261 join Enty_InsuranceCompany b on a.InsuranceCompany_ID = b.InsuranceCompany_ID
262where b.InsuranceCompany_RecordState <> 1;
263
264
265update a
266set a.DefaultFeeSchedule_ID = b.CPTFeeSchedule_ID
267from #BILL_EFBatch a
268 join Bill_BillOption b on a.Invoice_MedicalFacility_ID = b.MedicalFacility_ID
269where b.BillOption_RecordState <> 1;
270
271
272with AllowedFeeSchedule
273as
274(
275select a.InvoiceCPT_ID, a.InvoiceCPT_Code,
276 a.InvoiceCPT_ModifierCode1, a.InvoiceCPT_ModifierCode2, a.InvoiceCPT_ModifierCode3,
277 a.InvoiceCPT_ModifierCode4, a.InvoiceCPT_PlaceOfServiceCode, a.InvoiceCPT_TypeOfServiceCode, ExceptionCPTFeeSchedule_ID, DefaultFeeSchedule_ID,
278 FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(a.InvoiceCPT_Code, a.InvoiceCPT_ModifierCode1,
279 a.InvoiceCPT_ModifierCode2, a.InvoiceCPT_ModifierCode3, a.InvoiceCPT_ModifierCode4,
280 a.InvoiceCPT_PlaceOfServiceCode, a.InvoiceCPT_TypeOfServiceCode, b.Invoice_MedicalFacility_ID,
281 b.Provider_ID, b.Invoice_Specialty_ID, a.Invoice_ID, b.InsuranceCompany_ID, a.InvoiceCPT_ID)
282 from #BILL_EFBatch b
283 join Bill_InvoiceCPT a on a.Invoice_ID = b.Invoice_ID
284
285 where a.InvoiceCPT_RecordState <> 1
286)
287update a
288set a.CPTFeeSchdDetail_ID = case when b.ExceptionCPTFeeSchedule_ID = '0000000000' and b.DefaultFeeSchedule_ID = '0000000000' then '0000000000' else substring(b.FeeScheduleDetailID, 1, 10) end,
289 a.HCPCSFeeSchdDetail_ID = case when b.ExceptionCPTFeeSchedule_ID = '0000000000' and b.DefaultFeeSchedule_ID = '0000000000' then '0000000000' else substring(b.FeeScheduleDetailID, 12, 21) end
290from Bill_InvoiceCPT a
291 join AllowedFeeSchedule b on a.InvoiceCPT_ID = b.InvoiceCPT_ID;
292
293
294 -- start: execute dbo.usp_bilAnsiEf_UpdateBatch @EFBatch_Number
295
296-- Update Guarantor_ID of BILL_EFBatch
297
298update a
299set a.Guarantor_ID = b.Guarantor_ID
300from BILL_EFBatch a
301join #BILL_EFBatch b on a.EFBatch_ID = b.EFBatch_ID;
302
303 -- end: execute dbo.usp_bilAnsiEf_UpdateBatch @EFBatch_Number
304
305 ---- LastFileDate
306 --update
307 -- BILL_InvoiceIns
308 --set
309 -- InvoiceIns_LastFileDate = getdate()
310 --from
311 -- BILL_InvoiceIns ii
312 --join
313 -- BILL_EFBatch eb on (eb.Invoice_ID = ii.Invoice_ID and eb.EFBatch_InsAsgn = ii.InvoiceIns_InsAsgn)
314 --where
315 -- eb.EFBatch_Number = @EFBatch_Number
316
317 ---- FirstFileDate
318 --update
319 -- BILL_InvoiceIns
320 --set
321 -- InvoiceIns_FirstFileDate = getdate()
322 --from
323 -- BILL_InvoiceIns ii
324 --join
325 -- BILL_EFBatch eb on (eb.Invoice_ID = ii.Invoice_ID and eb.EFBatch_InsAsgn = ii.InvoiceIns_InsAsgn)
326 --where
327 -- eb.EFBatch_Number = @EFBatch_Number and
328 -- ii.InvoiceIns_FirstFileDate is null
329
330-- LastFileDate and FirstFileDate
331 update
332 BILL_InvoiceIns
333 set
334 InvoiceIns_LastFileDate = getdate(),
335 InvoiceIns_FirstFileDate = case when InvoiceIns_FirstFileDate is null then getdate() else NULL end
336 from
337 BILL_InvoiceIns ii
338 join
339 BILL_EFBatch eb on (eb.Invoice_ID = ii.Invoice_ID and eb.EFBatch_InsAsgn = ii.InvoiceIns_InsAsgn)
340 where
341 eb.EFBatch_Number = @EFBatch_Number
342
343
344--if @EFType = 'P'
345--exec usp_bilANSILineItem @EFBatch_Number, @LineStatus, 0, @SortByRVU
346--else
347--exec usp_bilANSILineItem @EFBatch_Number, @LineStatus, 1, @SortByRVU
348
349
350
351-- replaced SP usp_bilANSILineItem parameters with this logic
352declare
353 @BatchNo int ,
354 @RollupRev bit;
355
356
357set @BatchNo = @EFBatch_Number;
358set @RollupRev = case when @EFType = 'P' then 0 else 1 end;
359
360
361-- get records from BILL_InvoiceCPT
362
363insert into #BILL_InvoiceCPT
364(
365
366InvoiceCPT_ID,
367InvoiceCPT_RevenueCode,
368InvoiceCPT_RecordState,
369Invoice_ID,
370InvoiceCPT_ICDCode1,
371InvoiceCPT_ICDCode2,
372InvoiceCPT_ICDCode3,
373InvoiceCPT_Cost,
374InvoiceCPT_Unit,
375InvoiceCPT_UnitFee,
376InvoiceCPT_FeeAmount,
377InvoiceCPT_RVU,
378InvoiceCPT_Dosage,
379InvoiceCPT_StartSerDate,
380InvoiceCPT_EndSerDate,
381InvoiceCPT_Code,
382InvoiceCPT_StartTime,
383InvoiceCPT_EndTime,
384InvoiceCPT_PurchasingInd,
385InvoiceCPT_Narrative,
386InvoiceCPT_AllowedFee,
387InvoiceCPT_AllowedUnitFee,
388InvoiceCPT_TypeOfServiceCode,
389InvoiceCPT_PlaceOfServiceCode,
390InvoiceCPT_ModifierCode1,
391InvoiceCPT_ModifierCode4,
392InvoiceCPT_ModifierCode3,
393InvoiceCPT_ModifierCode2,
394InvoiceCPT_ICDCode4,
395 InvoiceCPT_Sequence,
396 InvoiceCPT_UnitCode,
397 InvoiceCPT_CodeType,
398 InvoiceCPT_CodeBillDescription,
399 InvoiceCPT_IsBillable,
400 InvoiceCPT_AllowOverwrite,
401 InvoiceCPT_IsProcessed,
402 MedicalFacility_ID,
403 InvoiceCPT_FileStatus,
404InvoiceCPT_RollupRevenueCode,
405InvoiceCPT_Claim,
406InvoiceCPT_BundleCPTCode,
407InvoiceCPT_Taxable,
408InvoiceCPT_Capitated,
409InvoiceCPT_GPrintClaim,
410InvoiceCPT_CoInsDue,
411CPTFeeSchdDetail_ID,
412HCPCSFeeSchdDetail_ID,
413InvoiceCPT_IsICD10
414)
415select
416
417InvoiceCPT_ID,
418InvoiceCPT_RevenueCode,
419InvoiceCPT_RecordState,
420Invoice_ID,
421InvoiceCPT_ICDCode1,
422InvoiceCPT_ICDCode2,
423InvoiceCPT_ICDCode3,
424InvoiceCPT_Cost,
425InvoiceCPT_Unit,
426InvoiceCPT_UnitFee,
427InvoiceCPT_FeeAmount,
428InvoiceCPT_RVU,
429InvoiceCPT_Dosage,
430InvoiceCPT_StartSerDate,
431InvoiceCPT_EndSerDate,
432InvoiceCPT_Code,
433InvoiceCPT_StartTime,
434InvoiceCPT_EndTime,
435InvoiceCPT_PurchasingInd,
436InvoiceCPT_Narrative,
437InvoiceCPT_AllowedFee,
438InvoiceCPT_AllowedUnitFee,
439InvoiceCPT_TypeOfServiceCode,
440InvoiceCPT_PlaceOfServiceCode,
441InvoiceCPT_ModifierCode1,
442InvoiceCPT_ModifierCode4,
443InvoiceCPT_ModifierCode3,
444InvoiceCPT_ModifierCode2,
445InvoiceCPT_ICDCode4,
446 InvoiceCPT_Sequence,
447 InvoiceCPT_UnitCode,
448 InvoiceCPT_CodeType,
449 InvoiceCPT_CodeBillDescription,
450 InvoiceCPT_IsBillable,
451 InvoiceCPT_AllowOverwrite,
452 InvoiceCPT_IsProcessed,
453 MedicalFacility_ID,
454 InvoiceCPT_FileStatus,
455InvoiceCPT_RollupRevenueCode,
456InvoiceCPT_Claim,
457InvoiceCPT_BundleCPTCode,
458InvoiceCPT_Taxable,
459InvoiceCPT_Capitated,
460InvoiceCPT_GPrintClaim,
461InvoiceCPT_CoInsDue,
462CPTFeeSchdDetail_ID,
463HCPCSFeeSchdDetail_ID,
464InvoiceCPT_IsICD10
465from BILL_InvoiceCPT a
466where exists (select invoice_id from #BILL_EFBatch b where a.invoice_id = b.invoice_id)
467and a.InvoiceCPT_RecordState <> 1 ;
468
469
470insert into #CPT_CPTFeeSchdDetail
471(
472CPTFeeSchdDetail_ID,
473CPTFeeSchdDetail_RecordState,
474RollupCPTModifier_ID,
475CPTFeeSchdDetail_RollupRevenueCode,
476CPT_ID3,
477RollupCPT_ID
478)
479select
480a.CPTFeeSchdDetail_ID,
481a.CPTFeeSchdDetail_RecordState,
482a.RollupCPTModifier_ID,
483a.CPTFeeSchdDetail_RollupRevenueCode,
484a.CPT_ID3,
485a.RollupCPT_ID
486from CPT_CPTFeeSchdDetail a
487where exists (select b.CPTFeeSchdDetail_ID from #BILL_InvoiceCPT b where a.CPTFeeSchdDetail_ID = b.CPTFeeSchdDetail_ID);
488--where a.CPTFeeSchdDetail_RecordState <> 1;
489-- removed this since queries using this table filter by recordstate
490
491insert into #HCPC_HCPCSFeeSchdDetail
492(
493HCPCSFeeSchdDetail_ID,
494HCPCSFeeSchdDetail_RecordState,
495RollupCPTModifier_ID,
496HCPCSFeeSchdDetail_RollupRevenueCode,
497HCPCS_ID3,
498RollupCPT_ID
499)
500select
501a.HCPCSFeeSchdDetail_ID,
502a.HCPCSFeeSchdDetail_RecordState,
503a.RollupCPTModifier_ID,
504a.HCPCSFeeSchdDetail_RollupRevenueCode,
505a.HCPCS_ID3,
506a.RollupCPT_ID
507from HCPC_HCPCSFeeSchdDetail a
508where exists (select b.HCPCSFeeSchdDetail_ID from #BILL_InvoiceCPT b where a.HCPCSFeeSchdDetail_ID = b.HCPCSFeeSchdDetail_ID);
509--where a.HCPCSFeeSchdDetail_RecordState <> 1;
510-- removed this since queries using this table filter by recordstate
511
512
513 --fix on 6/16/2008
514 -- declare @Invoice_ID char(10)
515 --declare @InvICD1 varchar(9);
516 --declare @InvICD2 varchar(9);
517 --declare @InvICD3 varchar(9);
518 --declare @InvICD4 varchar(9);
519 --declare @TempInvICD1 varchar(9);
520 --declare @TempInvICD2 varchar(9);
521 --declare @TempInvICD3 varchar(9);
522 --declare @TempInvICD4 varchar(9);
523 --declare @BundleCode varchar(8);
524 --declare @RevenueCode varchar(8);
525 --declare @SequenceNum tinyint;
526 --declare @InvoiceCPT_ID char(10);
527 --declare @IsPrimary tinyint;
528
529 --Mantis # 21856 Start
530 declare @InsuranceCompany_ID char(10);
531 declare @CurrInvoiceCPT_ID char(10);
532 declare @PrevInvoiceCPT_ID char(10);
533 declare @CurrInvoiceCPT_RevenueCode varchar(4);
534 declare @CurrRollupCPT_ID char(10);
535 declare @CurrRollupCPTModifier_ID char(10);
536 declare @PrevInvoice_ID char(10);
537 declare @Prev_RevenueCode varchar(4);
538 declare @PrevCPT_ID char(10);
539 declare @PrevCPTModifier_ID char(10);
540 declare @CheckFlag int;
541 declare @Units float;
542 declare @Cost T_money;
543 declare @UnitFee T_money;
544 declare @FeeAmount T_money;
545 declare @AllowedUnitFee T_money;
546 declare @AllowedFee T_money;
547 declare @CurrInvoiceCPT_StartSerDate datetime;
548 declare @CurrInvoiceCPT_EndSerDate datetime;
549 declare @TempFeeAmount T_Money;
550 declare @TempAllowedFee T_Money;
551 declare @TempLineItem_ID int;
552 declare @CurrBundle_ID char(10);
553 declare @PrevBundle_ID char(10);
554 declare @BundleCount int;
555 declare @SequenceCount int; --Mantis # 24023 and 24024
556 declare @LineItem_ID int; --Mantis # 24023 and 24024
557 declare @Invoive_LineItem char(10); --Mantis # 24023 and 24024
558 declare @InvoiceCPT_Code varchar(10);
559 declare @InvoiceCPT_ModifierCode1 char(10);
560 declare @InvoiceCPT_ModifierCode2 char(10);
561 declare @InvoiceCPT_ModifierCode3 char(10);
562 declare @InvoiceCPT_ModifierCode4 char(10);
563 declare @InvoiceCPT_PlaceOfServiceCode char(10);
564 declare @InvoiceCPT_TypeOfServiceCode char(10);
565 declare @Invoice_MedicalFacility_ID char(10);
566 declare @Invoice_Provider_ID char(10);
567 declare @Invoice_Specialty_ID char(10);
568 declare @FeeScheduleDetailID varchar(21);
569 declare @CPTFeeScheduleDetail_ID char(10);
570 declare @HCPCSFeeScheduleDetail_ID char(10);
571 declare @PrimaryCPT_ID char(10); --Mantis # 24988 & 24891
572 declare @ExceptionCPTFeeSchedule_ID char(10);
573 declare @DefaultFeeSchedule_ID char(10);
574 set @CheckFlag = 0;
575 set @BundleCount = 0;
576
577
578if (@RollupRev = 1)
579begin
580
581 -- insert the non-rollup items
582
583 insert into #TMP_EFW_EFLineItem (
584 EfLineItem_BatchNo, InvoiceCPT_ID, EFLineItem_RevenueCode, Invoice_ID, EFLineItem_ICDCode1, EFLineItem_ICDCode2, EFLineItem_ICDCode3, EFLineItem_ICDCode4,
585 EFLineItem_Cost, EFLineItem_Unit, EFLineItem_UnitFee, EFLineItem_FeeAmount, EFLineItem_RVU, EFLineItem_Dosage, EFLineItem_StartSerDate, EFLineItem_EndSerDate,
586 EFLineItem_Code, EFLineItem_StartTime, EFLineItem_EndTime, EFLineItem_PurchasingInd, EFLineItem_Narrative, EFLineItem_AllowedFee, EFLineItem_AllowedUnitFee,
587 EFLineItem_PlaceOfServiceCode, EFLineItem_TypeOfServiceCode, EFLineItem_ModifierCode1, EFLineItem_ModifierCode2, EFLineItem_ModifierCode3, EFLineItem_ModifierCode4,
588 EFLineItem_Sequence, EFLineItem_UnitCode, EFLineItem_CodeType, EFLineItem_CodeBillDescription, EFLineItem_RevenueRollup)
589 select distinct @BatchNo,
590 ic.InvoiceCPT_ID,
591 ic.InvoiceCPT_RevenueCode,
592 ic.Invoice_ID,
593 ic.InvoiceCPT_ICDCode1,
594 ic.InvoiceCPT_ICDCode2,
595 ic.InvoiceCPT_ICDCode3,
596 ic.InvoiceCPT_ICDCode4,
597 ic.InvoiceCPT_Cost,
598 ic.InvoiceCPT_Unit,
599 ic.InvoiceCPT_UnitFee,
600 ic.InvoiceCPT_FeeAmount,
601 ic.InvoiceCPT_RVU,
602 ic.InvoiceCPT_Dosage,
603 ic.InvoiceCPT_StartSerDate,
604 ic.InvoiceCPT_EndSerDate,
605 ic.InvoiceCPT_Code,
606 ic.InvoiceCPT_StartTime,
607 ic.InvoiceCPT_EndTime,
608 ic.InvoiceCPT_PurchasingInd,
609 ic.InvoiceCPT_Narrative,
610 ic.InvoiceCPT_AllowedFee,
611 ic.InvoiceCPT_AllowedUnitFee,
612 ic.InvoiceCPT_PlaceOfServiceCode,
613 ic.InvoiceCPT_TypeOfServiceCode,
614 ic.InvoiceCPT_ModifierCode1,
615 ic.InvoiceCPT_ModifierCode2,
616 ic.InvoiceCPT_ModifierCode3,
617 ic.InvoiceCPT_ModifierCode4,
618 ic.InvoiceCPT_Sequence,
619 ic.InvoiceCPT_UnitCode,
620 ic.InvoiceCPT_CodeType,
621 ic.InvoiceCPT_CodeBillDescription,
622 0 as EFLineItem_RevenueRollup
623/*
624 from #Bill_InvoiceCPT ic
625 join (select * from #CPT_CPTFeeSchdDetail union all select * from #HCPC_HCPCSFeeSchdDetail) cd
626 on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID and ic.InvoiceCPT_CodeType <> 2 and ic.CPTFeeSchdDetail_ID <> '0000000000') or
627 (cd.CPTFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID and ic.InvoiceCPT_CodeType = 2 and ic.HCPCSFeeSchdDetail_ID <> '0000000000') or
628 --if both cpt and hcpcs fee schedule detail id of invoiceCPT is empty record id then it is not in rollup
629 (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID and ic.CPTFeeSchdDetail_ID = '0000000000' and ic.HCPCSFeeSchdDetail_ID = '0000000000')
630 where 1=1
631 and (cd.CPTFeeSchdDetail_RecordState <> 1)
632 and (ic.InvoiceCPT_Claim = 1)
633 and (ic.InvoiceCPT_RecordState <> 1)
634 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
635 and (cd.CPTFeeSchdDetail_RollupRevenueCode = 0)
636*/
637 from #Bill_InvoiceCPT ic
638 join
639 (
640 select CPTFeeSchdDetail_ID, convert(varchar(10),'BLANK') HCPCSFeeSchdDetail_ID
641 from #CPT_CPTFeeSchdDetail c
642 where CPTFeeSchdDetail_ID <> '0000000000'
643 and CPTFeeSchdDetail_Recordstate <> 1
644 and CPTFeeSchdDetail_RollupRevenueCode = 0
645 union
646 select 'BLANK' CPTFeeSchdDetail_ID, HCPCSFeeSchdDetail_ID
647 from #HCPC_HCPCSFeeSchdDetail h
648 where HCPCSFeeSchdDetail_ID <> '0000000000'
649 and HCPCSFeeSchdDetail_Recordstate <> 1
650 and HCPCSFeeSchdDetail_RollupRevenueCode = 0
651 union
652 select '0000000000', '0000000000'
653 ) fee
654 on (
655 (ic.CPTFeeSchdDetail_ID = fee.CPTFeeSchdDetail_ID and ic.CPTFeeSchdDetail_ID <> '0000000000')
656 or
657 (ic.HCPCSFeeSchdDetail_ID = fee.HCPCSFeeSchdDetail_ID and ic.HCPCSFeeSchdDetail_ID <> '0000000000')
658 or
659 (ic.CPTFeeSchdDetail_ID = fee.CPTFeeSchdDetail_ID and ic.HCPCSFeeSchdDetail_ID = fee.HCPCSFeeSchdDetail_ID)
660 )
661 where 1=1
662 -- this line is redundant to the join condition above; leaving it in for readability purposes.
663 -- we only want rows with a valid fee schedule id, whether cpt or hcpc.
664 and (ic.InvoiceCPT_Claim = 1)
665-- and (ic.InvoiceCPT_RecordState <> 1)
666 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
667
668
669 -- insert the rollup items
670
671 insert into #TMP_EFW_EFLineItem (
672 EfLineItem_BatchNo, InvoiceCPT_ID, EFLineItem_RevenueCode, Invoice_ID, EFLineItem_ICDCode1, EFLineItem_ICDCode2, EFLineItem_ICDCode3, EFLineItem_ICDCode4,
673 EFLineItem_Cost, EFLineItem_Unit, EFLineItem_UnitFee, EFLineItem_FeeAmount, EFLineItem_RVU, EFLineItem_Dosage, EFLineItem_StartSerDate, EFLineItem_EndSerDate,
674 EFLineItem_Code, EFLineItem_StartTime, EFLineItem_EndTime, EFLineItem_PurchasingInd, EFLineItem_Narrative, EFLineItem_AllowedFee, EFLineItem_AllowedUnitFee,
675 EFLineItem_PlaceOfServiceCode, EFLineItem_TypeOfServiceCode, EFLineItem_ModifierCode1, EFLineItem_ModifierCode2, EFLineItem_ModifierCode3, EFLineItem_ModifierCode4,
676 EFLineItem_Sequence, EFLineItem_UnitCode, EFLineItem_CodeType, EFLineItem_CodeBillDescription, EFLineItem_RevenueRollup)
677 select @BatchNo,
678 ic.InvoiceCPT_ID,
679 roll.InvoiceCPT_RevenueCode,
680 roll.Invoice_ID,
681 ic.InvoiceCPT_ICDCode1,
682 ic.InvoiceCPT_ICDCode2,
683 ic.InvoiceCPT_ICDCode3,
684 ic.InvoiceCPT_ICDCode4,
685 roll.EFLineItem_Cost,
686 ic.InvoiceCPT_Unit,
687 roll.EFLineItem_UnitFee,
688 roll.EFLineItem_FeeAmount,
689 roll.EFLineItem_RVU,
690 ic.InvoiceCPT_Dosage,
691 roll.EFLineItem_StartSerDate,
692 roll.EFLineItem_EndSerDate,
693 Roll.RollupCPT_Code,
694 ic.InvoiceCPT_StartTime,
695 ic.InvoiceCPT_EndTime,
696 ic.InvoiceCPT_PurchasingInd,
697 ic.InvoiceCPT_Narrative,
698 roll.EFLineItem_AllowedFee,
699 roll.EFLineItem_AllowedUnitFee,
700 ic.InvoiceCPT_PlaceOfServiceCode,
701 ic.InvoiceCPT_TypeOfServiceCode,
702 roll.RollupCPTModifier_Code,
703 ic.InvoiceCPT_ModifierCode2,
704 ic.InvoiceCPT_ModifierCode3,
705 ic.InvoiceCPT_ModifierCode4,
706 1 as InvoiceCPT_Sequence,
707 ic.InvoiceCPT_UnitCode,
708 ic.InvoiceCPT_CodeType,
709 roll.RollupCPT_BillDescription,
710 1 as EFLineItem_RevenueRollup
711 from #Bill_InvoiceCPT ic
712 join (
713 select
714 Invoice_ID
715 , InvoiceCPT_RevenueCode
716 , RollupCPT_ID
717 , cc.CPT_Code as RollupCPT_Code
718 , cc.CPT_BillDescription as RollupCPT_BillDescription
719 , RollupCPTModifier_ID
720 , cm.CPTModifier_Code as RollupCPTModifier_Code
721 , EFLineItem_Cost = sum(InvoiceCPT_Cost)
722 , EFLineItem_UnitFee = sum(InvoiceCPT_FeeAmount)
723 , EFLineItem_FeeAmount = sum(InvoiceCPT_FeeAmount)
724 , EFLineItem_RVU = sum(InvoiceCPT_RVU)
725 , EFLineItem_AllowedFee = sum(InvoiceCPT_AllowedFee)
726 , EFLineItem_AllowedUnitFee = sum(InvoiceCPT_AllowedFee)
727 , EFLineItem_StartSerDate = min(InvoiceCPT_StartSerDate)
728 , EFLineItem_EndSerDate = min(InvoiceCPT_EndSerDate)
729 from #Bill_InvoiceCPT ic
730/* join (select * from #CPT_CPTFeeSchdDetail union all select * from #HCPC_HCPCSFeeSchdDetail) cd
731 -- InvoiceCPT_CodeType value is 2 for HCPCS code and 1 for CPT code.
732 on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID and ic.InvoiceCPT_CodeType <> 2 and ic.CPTFeeSchdDetail_ID <> '0000000000') or
733 (cd.CPTFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID and ic.InvoiceCPT_CodeType = 2 and ic.HCPCSFeeSchdDetail_ID <> '0000000000')
734*/
735 join
736 (
737 select CPTFeeSchdDetail_ID, convert(varchar(10),'BLANK') HCPCSFeeSchdDetail_ID,
738 RollupCPTModifier_ID,
739 CPTFeeSchdDetail_RollupRevenueCode,
740 CPT_ID3,
741 RollupCPT_ID
742 from #CPT_CPTFeeSchdDetail c
743 where CPTFeeSchdDetail_ID <> '0000000000'
744 and CPTFeeSchdDetail_Recordstate <> 1
745 and CPTFeeSchdDetail_RollupRevenueCode = 1
746 union
747 select 'BLANK' CPTFeeSchdDetail_ID, HCPCSFeeSchdDetail_ID,
748 RollupCPTModifier_ID,
749 HCPCSFeeSchdDetail_RollupRevenueCode,
750 HCPCS_ID3,
751 RollupCPT_ID
752 from #HCPC_HCPCSFeeSchdDetail h
753 where HCPCSFeeSchdDetail_ID <> '0000000000'
754 and HCPCSFeeSchdDetail_Recordstate <> 1
755 and HCPCSFeeSchdDetail_RollupRevenueCode = 1
756 ) fee
757 on (
758 (ic.CPTFeeSchdDetail_ID = fee.CPTFeeSchdDetail_ID and ic.InvoiceCPT_CodeType <> 2 and ic.CPTFeeSchdDetail_ID <> '0000000000')
759 or
760 (ic.HCPCSFeeSchdDetail_ID = fee.HCPCSFeeSchdDetail_ID and ic.InvoiceCPT_CodeType = 2 and ic.HCPCSFeeSchdDetail_ID <> '0000000000')
761 )
762
763 join CPT_CPT cc on (cc.CPT_ID = fee.RollupCPT_ID)
764 join CPT_CPTModifier cm on (cm.CPTModifier_ID = fee.RollupCPTModifier_ID)
765 where 1=1
766 and ((ic.CPTFeeSchdDetail_ID <> '0000000000') or (ic.HCPCSFeeSchdDetail_ID <> '0000000000'))
767 and (ic.InvoiceCPT_Claim = 1)
768-- and (ic.InvoiceCPT_RecordState <> 1)
769 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
770 and (fee.CPTFeeSchdDetail_RollupRevenueCode = 1)
771 group by Invoice_ID, InvoiceCPT_RevenueCode, RollupCPT_ID, cc.CPT_Code, cc.CPT_BillDescription, RollupCPTModifier_ID, cm.CPTModifier_Code
772 ) roll
773 on ic.Invoice_ID = roll.Invoice_ID
774 and ic.InvoiceCPT_Sequence = 1
775end -- end @RollupRev = 1
776
777else if @RollupRev = 0
778begin -- begin if (@RollupRev = 0)
779
780 --Inserting all the invoice cpt records whose fee schedule detail is '0000000000'
781 --insert into
782 insert into
783 #TMP_EFW_EFLineItem
784 (
785 EfLineItem_BatchNo,
786 InvoiceCPT_ID,
787 EFLineItem_RevenueCode,
788 Invoice_ID,
789 EFLineItem_ICDCode1,
790 EFLineItem_ICDCode2,
791 EFLineItem_ICDCode3,
792 EFLineItem_ICDCode4,
793 EFLineItem_Cost,
794 EFLineItem_Unit,
795 EFLineItem_UnitFee,
796 EFLineItem_FeeAmount,
797 EFLineItem_RVU,
798 EFLineItem_Dosage,
799 EFLineItem_StartSerDate,
800 EFLineItem_EndSerDate,
801 EFLineItem_Code,
802 EFLineItem_StartTime,
803 EFLineItem_EndTime,
804 EFLineItem_PurchasingInd,
805 EFLineItem_Narrative,
806 EFLineItem_AllowedFee,
807 EFLineItem_AllowedUnitFee,
808 EFLineItem_PlaceOfServiceCode,
809 EFLineItem_TypeOfServiceCode,
810 EFLineItem_ModifierCode1,
811 EFLineItem_ModifierCode2,
812 EFLineItem_ModifierCode3,
813 EFLineItem_ModifierCode4,
814 EFLineItem_Sequence,
815 EFLineItem_UnitCode,
816 EFLineItem_CodeType,
817 EFLineItem_CodeBillDescription,
818 EFLineItem_RevenueRollup
819 )
820
821 Select
822 @BatchNo, --Batch No
823 ic.InvoiceCPT_ID, --InvCPT_ID
824 ic.InvoiceCPT_RevenueCode, --RevenueCode
825 --@Invoice_ID, --Invoice_ID
826 ic.Invoice_ID,
827 ic.InvoiceCPT_ICDCode1 , --ICDCode1
828 ic.InvoiceCPT_ICDCode2 , --ICDCode2
829 ic.InvoiceCPT_ICDCode3 , --ICDCode3
830 ic.InvoiceCPT_ICDCode4 , --ICDCode4
831 ic.InvoiceCPT_Cost, --Cost
832 ic.InvoiceCPT_Unit, --Unit
833 ic.InvoiceCPT_UnitFee, --UnitFee
834 ic.InvoiceCPT_FeeAmount, --FeeAmount
835 ic.InvoiceCPT_RVU, --RVU
836 ic.InvoiceCPT_Dosage, --Dosage
837 ic.InvoiceCPT_StartSerDate, --StartSerDate
838 ic.InvoiceCPT_EndSerDate, --EndSerDate
839 ic.InvoiceCPT_Code, --EFLineItem_Code
840 ic.InvoiceCPT_StartTime, --StartTime
841 ic.InvoiceCPT_EndTime, --EndTime
842 ic.InvoiceCPT_PurchasingInd, --Purch
843 ic.InvoiceCPT_Narrative, --Narrative
844 ic.InvoiceCPT_AllowedFee, --AllowedFee
845 ic.InvoiceCPT_AllowedUnitFee, --AllowedUnitFee
846 ic.InvoiceCPT_PlaceOfServiceCode, --POS
847 ic.InvoiceCPT_TypeOfServiceCode, --TOS
848 ic.InvoiceCPT_ModifierCode1, --Mod1
849 ic.InvoiceCPT_ModifierCode2, --Mod2
850 ic.InvoiceCPT_ModifierCode3, --Mod3
851 ic.InvoiceCPT_ModifierCode4, --Mod4
852 ic.InvoiceCPT_Sequence, --Sequence
853 ic.InvoiceCPT_UnitCode, --UnitCode
854 ic.InvoiceCPT_CodeType, --CodeType
855 ic.InvoiceCPT_CodeBillDescription, --Description
856 0 --RollupRevenueCode 0 for non roll up CPTs
857 from #Bill_InvoiceCPT ic
858 join
859 #CPT_CPTFeeSchdDetail cd on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID)
860 join
861 #HCPC_HCPCSFeeSchdDetail hd on (hd.HCPCSFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID)
862 --Added additional join conditions for the Bundle Codes while insertion
863 join
864 CPT_CPT cc on (cc.CPT_ID = cd.CPT_ID3)
865 join
866 HCPC_HCPCS hh on (hh.HCPCS_ID = hd.HCPCS_ID3)
867 where (ic.CPTFeeSchdDetail_ID = '0000000000' and ic.HCPCSFeeSchdDetail_ID = '0000000000')
868 and ic.InvoiceCPT_Claim = 1
869 --and ic.InvoiceCPT_RecordState <> 1
870 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
871 --and ic.Invoice_ID = @Invoice_ID
872 and (cd.CPTFeeSchdDetail_RecordState <> 1 or hd.HCPCSFeeSchdDetail_RecordState <> 1)
873
874 UNION ALL
875 -- inserting records for bundle invoice CPT's into temporary table
876 Select
877 @BatchNo, --Batch No
878 ic.InvoiceCPT_ID, --InvCPT_ID
879 ic.InvoiceCPT_RevenueCode, --RevenueCode
880 --@Invoice_ID, --Invoice_ID
881 ic.Invoice_ID,
882 ic.InvoiceCPT_ICDCode1 , --ICDCode1
883 ic.InvoiceCPT_ICDCode2 , --ICDCode2
884 ic.InvoiceCPT_ICDCode3 , --ICDCode3
885 ic.InvoiceCPT_ICDCode4 , --ICDCode4
886 ic.InvoiceCPT_Cost, --Cost
887 ic.InvoiceCPT_Unit, --Unit
888 ic.InvoiceCPT_UnitFee, --UnitFee
889 ic.InvoiceCPT_FeeAmount, --FeeAmount
890 ic.InvoiceCPT_RVU, --RVU
891 ic.InvoiceCPT_Dosage, --Dosage
892 ic.InvoiceCPT_StartSerDate, --StartSerDate
893 ic.InvoiceCPT_EndSerDate, --EndSerDate
894 ic.InvoiceCPT_Code, --EFLineItem_Code
895 ic.InvoiceCPT_StartTime, --StartTime
896 ic.InvoiceCPT_EndTime, --EndTime
897 ic.InvoiceCPT_PurchasingInd, --Purch
898 ic.InvoiceCPT_Narrative, --Narrative
899 ic.InvoiceCPT_AllowedFee, --AllowedFee
900 ic.InvoiceCPT_AllowedUnitFee, --AllowedUnitFee
901 ic.InvoiceCPT_PlaceOfServiceCode, --POS
902 ic.InvoiceCPT_TypeOfServiceCode, --TOS
903 ic.InvoiceCPT_ModifierCode1, --Mod1
904 ic.InvoiceCPT_ModifierCode2, --Mod2
905 ic.InvoiceCPT_ModifierCode3, --Mod3
906 ic.InvoiceCPT_ModifierCode4, --Mod4
907 ic.InvoiceCPT_Sequence, --Sequence
908 ic.InvoiceCPT_UnitCode, --UnitCode
909 ic.InvoiceCPT_CodeType, --CodeType
910 ic.InvoiceCPT_CodeBillDescription, --Description
911 0 --RollupRevenueCode 0 for non roll up CPTs
912 from #Bill_InvoiceCPT ic
913 join
914 #CPT_CPTFeeSchdDetail cd on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID)
915 join
916 #HCPC_HCPCSFeeSchdDetail hd on (hd.HCPCSFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID)
917 --Added additional join conditions for the Bundle Codes while insertion
918 join
919 CPT_CPT cc on (cc.CPT_ID = cd.CPT_ID3)
920 join
921 HCPC_HCPCS hh on (hh.HCPCS_ID = hd.HCPCS_ID3)
922 where (NOT(ic.CPTFeeSchdDetail_ID = '0000000000' and ic.HCPCSFeeSchdDetail_ID = '0000000000'))
923 and ic.InvoiceCPT_Claim = 1
924 --and ic.InvoiceCPT_RecordState <> 1
925 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
926 --and ic.Invoice_ID = @Invoice_ID
927 and (cd.CPTFeeSchdDetail_RecordState <> 1 or hd.HCPCSFeeSchdDetail_RecordState <> 1) --Added record state condition for CPT & HCPCS
928 and (cd.CPT_ID3 = '0000000000' and hd.HCPCS_ID3 = '0000000000');--Added the condition for null bundle code
929
930
931 Select ic.InvoiceCPT_ID, cd.CPT_ID3 as Bundle_ID, ic.InvoiceCPT_Cost, ic.InvoiceCPT_FeeAmount, --Extracting the bundle ID
932 ic.InvoiceCPT_RVU, ic.InvoiceCPT_AllowedFee, ic.InvoiceCPT_StartSerDate, ic.InvoiceCPT_EndSerDate
933 into #TempCPTBundle_Records
934 from #Bill_InvoiceCPT ic
935 join
936 #CPT_CPTFeeSchdDetail cd on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID)
937 where (ic.CPTFeeSchdDetail_ID <> '0000000000') and ic.InvoiceCPT_Claim = 1
938 --and ic.InvoiceCPT_RecordState <> 1
939 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
940 --and ic.Invoice_ID = @Invoice_ID
941 and cd.CPTFeeSchdDetail_RecordState <> 1 and
942 cd.CPT_ID3 <> '0000000000';
943 --order by Bundle_ID
944
945 --Insert and Update the costs for bundle HCPCS records into the temporary table
946
947 Select ic.InvoiceCPT_ID, hd.HCPCS_ID3 as Bundle_ID, ic.InvoiceCPT_Cost, ic.InvoiceCPT_FeeAmount,
948 ic.InvoiceCPT_RVU, ic.InvoiceCPT_AllowedFee, ic.InvoiceCPT_StartSerDate, ic.InvoiceCPT_EndSerDate
949 into #TempHCPCSBundle_Records
950 from #Bill_InvoiceCPT ic
951 join
952 #HCPC_HCPCSFeeSchdDetail hd on (hd.HCPCSFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID)
953 where (ic.HCPCSFeeSchdDetail_ID <> '0000000000') and ic.InvoiceCPT_Claim = 1
954 --and ic.InvoiceCPT_RecordState <> 1
955 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
956 --and ic.Invoice_ID = @Invoice_ID
957 and hd.HCPCSFeeSchdDetail_RecordState <> 1 and
958 hd.HCPCS_ID3 <> '0000000000' ;
959 --order by Bundle_ID --Checked the condition for not null bundle code records
960
961
962 declare crTemp cursor for
963 Select InvoiceCPT_ID, Bundle_ID from #TempCPTBundle_Records order by Bundle_ID
964
965 open crTemp
966 fetch next from crTemp into @CurrInvoiceCPT_ID, @CurrBundle_ID
967
968 while @@fetch_status = 0
969 begin
970 if (@CheckFlag = 1)
971 begin
972 if (@CurrBundle_ID = @PrevBundle_ID)
973 set @BundleCount = 2
974 else
975 set @BundleCount = 1
976 end
977
978 if (@BundleCount = 1) or ((Select count(*) from #TempCPTBundle_Records) = 1) or ((select count(*) from #TempCPTBundle_Records where Bundle_ID = @CurrBundle_ID) = 1)
979 begin
980 insert into
981 #TMP_EFW_EFLineItem
982 (
983 EfLineItem_BatchNo,
984 InvoiceCPT_ID,
985 EFLineItem_RevenueCode,
986 Invoice_ID,
987 EFLineItem_ICDCode1,
988 EFLineItem_ICDCode2,
989 EFLineItem_ICDCode3,
990 EFLineItem_ICDCode4,
991 EFLineItem_Cost,
992 EFLineItem_Unit,
993 EFLineItem_UnitFee,
994 EFLineItem_FeeAmount,
995 EFLineItem_RVU,
996 EFLineItem_Dosage,
997 EFLineItem_StartSerDate,
998 EFLineItem_EndSerDate,
999 EFLineItem_Code,
1000 EFLineItem_StartTime,
1001 EFLineItem_EndTime,
1002 EFLineItem_PurchasingInd,
1003 EFLineItem_Narrative,
1004 EFLineItem_AllowedFee,
1005 EFLineItem_AllowedUnitFee,
1006 EFLineItem_PlaceOfServiceCode,
1007 EFLineItem_TypeOfServiceCode,
1008 EFLineItem_ModifierCode1,
1009 EFLineItem_ModifierCode2,
1010 EFLineItem_ModifierCode3,
1011 EFLineItem_ModifierCode4,
1012 EFLineItem_Sequence,
1013 EFLineItem_UnitCode,
1014 EFLineItem_CodeType,
1015 EFLineItem_CodeBillDescription,
1016 EFLineItem_RevenueRollup
1017 )
1018
1019 Select
1020 @BatchNo, --Batch No
1021 ic.InvoiceCPT_ID, --InvCPT_ID
1022 ic.InvoiceCPT_RevenueCode, --RevenueCode
1023 ic.Invoice_ID, --Invoice_ID -- Task # 56151
1024 ic.InvoiceCPT_ICDCode1 , --ICDCode1
1025 ic.InvoiceCPT_ICDCode2 , --ICDCode2
1026 ic.InvoiceCPT_ICDCode3 , --ICDCode3
1027 ic.InvoiceCPT_ICDCode4 , --ICDCode4
1028 ic.InvoiceCPT_Cost, --Cost
1029 ic.InvoiceCPT_Unit, --Unit
1030 ic.InvoiceCPT_UnitFee, --UnitFee
1031 ic.InvoiceCPT_FeeAmount, --FeeAmount
1032 ic.InvoiceCPT_RVU, --RVU
1033 ic.InvoiceCPT_Dosage, --Dosage
1034 ic.InvoiceCPT_StartSerDate, --StartSerDate
1035 ic.InvoiceCPT_EndSerDate, --EndSerDate
1036 ic.InvoiceCPT_Code, --EFLineItem_Code
1037 ic.InvoiceCPT_StartTime, --StartTime
1038 ic.InvoiceCPT_EndTime, --EndTime
1039 ic.InvoiceCPT_PurchasingInd, --Purch
1040 ic.InvoiceCPT_Narrative, --Narrative
1041 ic.InvoiceCPT_AllowedFee, --AllowedFee
1042 ic.InvoiceCPT_AllowedUnitFee, --AllowedUnitFee
1043 ic.InvoiceCPT_PlaceOfServiceCode, --POS
1044 ic.InvoiceCPT_TypeOfServiceCode, --TOS
1045 ic.InvoiceCPT_ModifierCode1, --Mod1
1046 ic.InvoiceCPT_ModifierCode2, --Mod2
1047 ic.InvoiceCPT_ModifierCode3, --Mod3
1048 ic.InvoiceCPT_ModifierCode4, --Mod4
1049 ic.InvoiceCPT_Sequence, --Sequence
1050 ic.InvoiceCPT_UnitCode, --UnitCode
1051 ic.InvoiceCPT_CodeType, --CodeType
1052 ic.InvoiceCPT_CodeBillDescription, --Description
1053 0 --RollupRevenueCode 0 for non roll up CPTs
1054 from #Bill_InvoiceCPT ic
1055 join
1056 #CPT_CPTFeeSchdDetail cd on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID)
1057 --Added additional join conditions for the Bundle Codes while insertion
1058 join
1059 CPT_CPT cc on (cc.CPT_ID = cd.CPT_ID3)
1060 where ic.CPTFeeSchdDetail_ID <> '0000000000' and ic.InvoiceCPT_Claim = 1 --and ic.InvoiceCPT_RecordState <> 1
1061 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0) --and ic.Invoice_ID = @Invoice_ID --Task # 56151
1062 and cd.CPTFeeSchdDetail_RecordState <> 1
1063 and cd.CPT_ID3 <> '0000000000' and ic.InvoiceCPT_ID = @CurrInvoiceCPT_ID
1064
1065 set @PrevInvoiceCPT_ID = @CurrInvoiceCPT_ID
1066 set @PrevBundle_ID = @CurrBundle_ID
1067 end
1068 else
1069 begin
1070 if (@CheckFlag = 0) or (@PrevBundle_ID <> @CurrBundle_ID)
1071 begin
1072 insert into
1073 #TMP_EFW_EFLineItem
1074 (
1075 EfLineItem_BatchNo,
1076 InvoiceCPT_ID,
1077 EFLineItem_RevenueCode,
1078 Invoice_ID,
1079 EFLineItem_ICDCode1,
1080 EFLineItem_ICDCode2,
1081 EFLineItem_ICDCode3,
1082 EFLineItem_ICDCode4,
1083 EFLineItem_Cost,
1084 EFLineItem_Unit,
1085 EFLineItem_UnitFee,
1086 EFLineItem_FeeAmount,
1087 EFLineItem_RVU,
1088 EFLineItem_Dosage,
1089 EFLineItem_StartSerDate,
1090 EFLineItem_EndSerDate,
1091 EFLineItem_Code,
1092 EFLineItem_StartTime,
1093 EFLineItem_EndTime,
1094 EFLineItem_PurchasingInd,
1095 EFLineItem_Narrative,
1096 EFLineItem_AllowedFee,
1097 EFLineItem_AllowedUnitFee,
1098 EFLineItem_PlaceOfServiceCode,
1099 EFLineItem_TypeOfServiceCode,
1100 EFLineItem_ModifierCode1,
1101 EFLineItem_ModifierCode2,
1102 EFLineItem_ModifierCode3,
1103 EFLineItem_ModifierCode4,
1104 EFLineItem_Sequence,
1105 EFLineItem_UnitCode,
1106 EFLineItem_CodeType,
1107 EFLineItem_CodeBillDescription,
1108 EFLineItem_RevenueRollup
1109 )
1110 Select
1111 @BatchNo, --Batch No
1112 ic.InvoiceCPT_ID, --InvCPT_ID
1113 ic.InvoiceCPT_RevenueCode, --RevenueCode
1114 ic.Invoice_ID, --Task # 56151 --Invoice_ID
1115 ic.InvoiceCPT_ICDCode1, --ICDCode1 --Mantis # 24343 Section #1.7 837P Loop 2400 of claims using bundle codes are missing Diagnosis pointers in field SV107
1116 ic.InvoiceCPT_ICDCode2, --ICDCode2
1117 ic.InvoiceCPT_ICDCode3, --ICDCode3
1118 ic.InvoiceCPT_ICDCode4, --ICDCode4
1119 0, --Cost
1120 1, --Unit
1121 0, --UnitFee
1122 0, --FeeAmount
1123 0, --RVU
1124 '', --Dosage
1125 ic.InvoiceCPT_StartSerDate, --StartSerDate
1126 ic.InvoiceCPT_EndSerDate, --EndSerDate
1127 (Select IsNull((Select CPT_Code from CPT_CPT where CPT_ID = @CurrBundle_ID), '')), --EFLineItem_Code
1128 ic.InvoiceCPT_StartTime, --StartTime
1129 ic.InvoiceCPT_EndTime, --EndTime
1130 ic.InvoiceCPT_PurchasingInd, --Purch
1131 ic.InvoiceCPT_Narrative, --Narrative
1132 0, --AllowedFee
1133 0, --AllowedUnitFee
1134 ic.InvoiceCPT_PlaceOfServiceCode, --POS
1135 ic.InvoiceCPT_TypeOfServiceCode, --TOS
1136 '', --Mod1
1137 '', --Mod2
1138 '', --Mod3
1139 '', --Mod4
1140 ic.InvoiceCPT_Sequence, --Sequence
1141 ic.InvoiceCPT_UnitCode, --UnitCode
1142 ic.InvoiceCPT_CodeType, --CodeType
1143 (Select IsNull((Select CPT_BillDescription from CPT_CPT where CPT_ID = @CurrBundle_ID), '')), --Description
1144 1 --RollupRevenueCode 1 for all roll up CPTs
1145 from #Bill_InvoiceCPT ic
1146 join
1147 #CPT_CPTFeeSchdDetail cd on (cd.CPTFeeSchdDetail_ID = ic.CPTFeeSchdDetail_ID)
1148 --Joined on CPT_CPT to match on Bundle codes
1149 join
1150 CPT_CPT cc on (cc.CPT_ID = cd.CPT_ID3)
1151 where (ic.CPTFeeSchdDetail_ID <> '0000000000') and ic.InvoiceCPT_Claim = 1 --and ic.InvoiceCPT_RecordState <> 1
1152 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
1153 --and ic.Invoice_ID = @Invoice_ID --Task # 56151
1154 and cd.CPTFeeSchdDetail_RecordState <> 1 and ic.InvoiceCPT_ID = @CurrInvoiceCPT_ID
1155 and cd.CPT_ID3 <> '0000000000' order by cd.CPT_ID3 --Checked the condition for not null bundle code records
1156
1157 --Making the current bundle id as previous id
1158 set @PrevInvoiceCPT_ID = @CurrInvoiceCPT_ID
1159 set @PrevBundle_ID = @CurrBundle_ID
1160 set @CheckFlag = 1
1161 end
1162 else if (@PrevBundle_ID = @CurrBundle_ID)
1163 begin
1164 fetch next from crTemp into @CurrInvoiceCPT_ID, @CurrBundle_ID
1165 if (@@fetch_status <> 0) or (@PrevBundle_ID <> @CurrBundle_ID)
1166 begin
1167 Select @InvoiceCPT_Code = CPT_Code from CPT_CPT where CPT_ID = @PrevBundle_ID
1168
1169 Select @Invoice_ID = Invoice_ID from #Bill_InvoiceCPT where InvoiceCPT_ID = @PrevInvoiceCPT_ID --Task # 56151
1170
1171 Select @FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(@InvoiceCPT_Code, '',
1172 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1173 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID, @PrevInvoiceCPT_ID) --Mantis # 28772
1174
1175 set @CPTFeeScheduleDetail_ID = substring(@FeeScheduleDetailID, 1, 10)
1176
1177 if @CPTFeeScheduleDetail_ID <> '0000000000'
1178 select top 1 @InvoiceCPT_ModifierCode1 = cm.CPTModifier_Code from CPT_CPTModifier cm
1179 join CPT_CPTFeeSchdDetail cd on (cd.CPTModifier_ID1 = cm.CPTModifier_ID)
1180 where cd.CPTFeeSchdDetail_ID = @CPTFeeScheduleDetail_ID
1181 else
1182 set @InvoiceCPT_ModifierCode1 = ''
1183
1184 --Mantis # 24073 Start
1185 --Determing the Fee Schedule Detail ID from the billed fee schedule for calculating the unit fee
1186 if @InvoiceCPT_ModifierCode1 <> ''
1187 Select @FeeScheduleDetailID = dbo.udf_GetBilledFeeSchedule(@InvoiceCPT_Code, @InvoiceCPT_ModifierCode1,
1188 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1189 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID)
1190 else
1191 Select @FeeScheduleDetailID = dbo.udf_GetBilledFeeSchedule(@InvoiceCPT_Code, '',
1192 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1193 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID)
1194
1195 set @CPTFeeScheduleDetail_ID = substring(@FeeScheduleDetailID, 1, 10)
1196
1197 if @CPTFeeScheduleDetail_ID <> '0000000000'
1198 select @UnitFee = CPTFeeSchdDetail_Fee from CPT_CPTFeeSchdDetail where CPTFeeSchdDetail_ID = @CPTFeeScheduleDetail_ID
1199 else
1200 select top 1 @UnitFee = cd.CPTDetail_PayPrivate from CPT_CPTDetail cd join CPT_CPT cc on (cd.CPT_ID = cc.CPT_ID)
1201 where cc.CPT_ID = @PrevBundle_ID
1202
1203 --Determing the Fee Schedule Detail ID from the allowed fee schedule for calculating the other costs
1204 if @InvoiceCPT_ModifierCode1 <> ''
1205 Select @FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(@InvoiceCPT_Code, @InvoiceCPT_ModifierCode1,
1206 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1207 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID, @PrevInvoiceCPT_ID) --Mantis # 28772
1208 else
1209 Select @FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(@InvoiceCPT_Code, '',
1210 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1211 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID, @PrevInvoiceCPT_ID) --Mantis # 28772
1212
1213 set @CPTFeeScheduleDetail_ID = substring(@FeeScheduleDetailID, 1, 10)
1214
1215 if @CPTFeeScheduleDetail_ID <> '0000000000'
1216 select @FeeAmount = CPTFeeSchdDetail_Unit * @UnitFee, @AllowedUnitFee = CPTFeeSchdDetail_Fee, @AllowedFee = CPTFeeSchdDetail_Unit * CPTFeeSchdDetail_Fee,
1217 @Cost = CPTFeeSchdDetail_Cost from CPT_CPTFeeSchdDetail where CPTFeeSchdDetail_ID = @CPTFeeScheduleDetail_ID
1218 else
1219 begin
1220 select top 1 @AllowedUnitFee = cd.CPTDetail_PayPrivate, @AllowedFee = cd.CPTDetail_PayPrivate,
1221 @Cost = cd.CPTDetail_Cost from CPT_CPTDetail cd join CPT_CPT cc on (cd.CPT_ID = cc.CPT_ID)
1222 where cc.CPT_ID = @PrevBundle_ID
1223 set @FeeAmount = @UnitFee
1224 end
1225 --Mantis # 24073 End
1226
1227 select top 1 @TempLineItem_ID = EFLineItem_ID from #TMP_EFW_EFLineItem order by EFLineItem_ID desc
1228
1229 Update
1230 #TMP_EFW_EFLineItem
1231 set
1232 EFLineItem_Cost = @Cost,
1233 EFLineItem_UnitFee = @UnitFee,
1234 EFLineItem_FeeAmount = @FeeAmount,
1235 EFLineItem_RVU = 0,
1236 EFLineItem_AllowedFee = @AllowedFee,
1237 EFLineItem_AllowedUnitFee = @AllowedUnitFee,
1238 EFLineItem_StartSerDate = (Select min(InvoiceCPT_StartSerDate) from #TempCPTBundle_Records where Bundle_ID = @PrevBundle_ID),
1239 EFLineItem_EndSerDate = (Select min(InvoiceCPT_EndSerDate) from #TempCPTBundle_Records where Bundle_ID = @PrevBundle_ID)
1240 where EFLineItem_ID = @TempLineItem_ID
1241 end
1242 continue;
1243 end
1244 end
1245 fetch next from crTemp into @CurrInvoiceCPT_ID, @CurrBundle_ID
1246 end
1247 close crTemp
1248 deallocate crTemp
1249 set @CheckFlag = 0
1250 set @BundleCount = 0
1251 --drop table #TempCPTBundle_Records
1252
1253 ----Insert and Update the costs for bundle HCPCS records into the temporary table
1254
1255 --Select ic.InvoiceCPT_ID, hd.HCPCS_ID3 as Bundle_ID, ic.InvoiceCPT_Cost, ic.InvoiceCPT_FeeAmount,
1256 --ic.InvoiceCPT_RVU, ic.InvoiceCPT_AllowedFee, ic.InvoiceCPT_StartSerDate, ic.InvoiceCPT_EndSerDate
1257 --into #TempHCPCSBundle_Records from Bill_InvoiceCPT ic
1258 --join
1259 -- HCPC_HCPCSFeeSchdDetail hd on (hd.HCPCSFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID)
1260 --where (ic.HCPCSFeeSchdDetail_ID <> '0000000000') and ic.InvoiceCPT_Claim = 1 and
1261 --ic.InvoiceCPT_RecordState <> 1 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0) and
1262 --ic.Invoice_ID = @Invoice_ID and hd.HCPCSFeeSchdDetail_RecordState <> 1 and
1263 --hd.HCPCS_ID3 <> '0000000000' order by Bundle_ID --Checked the condition for not null bundle code records
1264
1265 declare crTemp cursor for
1266 Select InvoiceCPT_ID, Bundle_ID from #TempHCPCSBundle_Records order by Bundle_ID
1267
1268 open crTemp
1269 fetch next from crTemp into @CurrInvoiceCPT_ID, @CurrBundle_ID
1270
1271 while @@fetch_status = 0
1272 begin
1273 if (@CheckFlag = 1)
1274 begin
1275 if (@CurrBundle_ID = @PrevBundle_ID)
1276 set @BundleCount = 2
1277 else
1278 set @BundleCount = 1
1279 end
1280
1281 if (@BundleCount = 1) or ((Select count(*) from #TempHCPCSBundle_Records) = 1) or ((select count(*) from #TempHCPCSBundle_Records where Bundle_ID = @CurrBundle_ID) = 1)
1282 begin
1283 insert into
1284 #TMP_EFW_EFLineItem
1285 (
1286 EfLineItem_BatchNo,
1287 InvoiceCPT_ID,
1288 EFLineItem_RevenueCode,
1289 Invoice_ID,
1290 EFLineItem_ICDCode1,
1291 EFLineItem_ICDCode2,
1292 EFLineItem_ICDCode3,
1293 EFLineItem_ICDCode4,
1294 EFLineItem_Cost,
1295 EFLineItem_Unit,
1296 EFLineItem_UnitFee,
1297 EFLineItem_FeeAmount,
1298 EFLineItem_RVU,
1299 EFLineItem_Dosage,
1300 EFLineItem_StartSerDate,
1301 EFLineItem_EndSerDate,
1302 EFLineItem_Code,
1303 EFLineItem_StartTime,
1304 EFLineItem_EndTime,
1305 EFLineItem_PurchasingInd,
1306 EFLineItem_Narrative,
1307 EFLineItem_AllowedFee,
1308 EFLineItem_AllowedUnitFee,
1309 EFLineItem_PlaceOfServiceCode,
1310 EFLineItem_TypeOfServiceCode,
1311 EFLineItem_ModifierCode1,
1312 EFLineItem_ModifierCode2,
1313 EFLineItem_ModifierCode3,
1314 EFLineItem_ModifierCode4,
1315 EFLineItem_Sequence,
1316 EFLineItem_UnitCode,
1317 EFLineItem_CodeType,
1318 EFLineItem_CodeBillDescription,
1319 EFLineItem_RevenueRollup
1320 )
1321
1322 Select
1323 @BatchNo, --Batch No
1324 ic.InvoiceCPT_ID, --InvCPT_ID
1325 ic.InvoiceCPT_RevenueCode, --RevenueCode
1326 ic.Invoice_ID, --Task # 56151 --Invoice_ID
1327 ic.InvoiceCPT_ICDCode1 , --ICDCode1
1328 ic.InvoiceCPT_ICDCode2 , --ICDCode2
1329 ic.InvoiceCPT_ICDCode3 , --ICDCode3
1330 ic.InvoiceCPT_ICDCode4 , --ICDCode4
1331 ic.InvoiceCPT_Cost, --Cost
1332 ic.InvoiceCPT_Unit, --Unit
1333 ic.InvoiceCPT_UnitFee, --UnitFee
1334 ic.InvoiceCPT_FeeAmount, --FeeAmount
1335 ic.InvoiceCPT_RVU, --RVU
1336 ic.InvoiceCPT_Dosage, --Dosage
1337 ic.InvoiceCPT_StartSerDate, --StartSerDate
1338 ic.InvoiceCPT_EndSerDate, --EndSerDate
1339 ic.InvoiceCPT_Code, --EFLineItem_Code
1340 ic.InvoiceCPT_StartTime, --StartTime
1341 ic.InvoiceCPT_EndTime, --EndTime
1342 ic.InvoiceCPT_PurchasingInd, --Purch
1343 ic.InvoiceCPT_Narrative, --Narrative
1344 ic.InvoiceCPT_AllowedFee, --AllowedFee
1345 ic.InvoiceCPT_AllowedUnitFee, --AllowedUnitFee
1346 ic.InvoiceCPT_PlaceOfServiceCode, --POS
1347 ic.InvoiceCPT_TypeOfServiceCode, --TOS
1348 ic.InvoiceCPT_ModifierCode1, --Mod1
1349 ic.InvoiceCPT_ModifierCode2, --Mod2
1350 ic.InvoiceCPT_ModifierCode3, --Mod3
1351 ic.InvoiceCPT_ModifierCode4, --Mod4
1352 ic.InvoiceCPT_Sequence, --Sequence
1353 ic.InvoiceCPT_UnitCode, --UnitCode
1354 ic.InvoiceCPT_CodeType, --CodeType
1355 ic.InvoiceCPT_CodeBillDescription, --Description
1356 0 --RollupRevenueCode 0 for non roll up CPTs
1357 from #Bill_InvoiceCPT ic
1358 join
1359 #HCPC_HCPCSFeeSchdDetail hd on (hd.HCPCSFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID)
1360 --Added additional join conditions for the Bundle Codes while insertion
1361 join
1362 HCPC_HCPCS hh on (hh.HCPCS_ID = hd.HCPCS_ID3)
1363 where ic.HCPCSFeeSchdDetail_ID <> '0000000000' and ic.InvoiceCPT_Claim = 1 --and ic.InvoiceCPT_RecordState <> 1
1364 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
1365 --and ic.Invoice_ID = @Invoice_ID --Task # 56151
1366 and hd.HCPCSFeeSchdDetail_RecordState <> 1
1367 and hd.HCPCS_ID3 <> '0000000000' and ic.InvoiceCPT_ID = @CurrInvoiceCPT_ID
1368
1369 set @PrevInvoiceCPT_ID = @CurrInvoiceCPT_ID
1370 set @PrevBundle_ID = @CurrBundle_ID
1371 end
1372 else
1373 begin
1374 if (@CheckFlag = 0) or (@PrevBundle_ID <> @CurrBundle_ID)
1375 begin
1376 insert into
1377 #TMP_EFW_EFLineItem
1378 (
1379 EfLineItem_BatchNo,
1380 InvoiceCPT_ID,
1381 EFLineItem_RevenueCode,
1382 Invoice_ID,
1383 EFLineItem_ICDCode1,
1384 EFLineItem_ICDCode2,
1385 EFLineItem_ICDCode3,
1386 EFLineItem_ICDCode4,
1387 EFLineItem_Cost,
1388 EFLineItem_Unit,
1389 EFLineItem_UnitFee,
1390 EFLineItem_FeeAmount,
1391 EFLineItem_RVU,
1392 EFLineItem_Dosage,
1393 EFLineItem_StartSerDate,
1394 EFLineItem_EndSerDate,
1395 EFLineItem_Code,
1396 EFLineItem_StartTime,
1397 EFLineItem_EndTime,
1398 EFLineItem_PurchasingInd,
1399 EFLineItem_Narrative,
1400 EFLineItem_AllowedFee,
1401 EFLineItem_AllowedUnitFee,
1402 EFLineItem_PlaceOfServiceCode,
1403 EFLineItem_TypeOfServiceCode,
1404 EFLineItem_ModifierCode1,
1405 EFLineItem_ModifierCode2,
1406 EFLineItem_ModifierCode3,
1407 EFLineItem_ModifierCode4,
1408 EFLineItem_Sequence,
1409 EFLineItem_UnitCode,
1410 EFLineItem_CodeType,
1411 EFLineItem_CodeBillDescription,
1412 EFLineItem_RevenueRollup
1413 )
1414 Select
1415 @BatchNo, --Batch No
1416 ic.InvoiceCPT_ID, --InvCPT_ID
1417 ic.InvoiceCPT_RevenueCode, --RevenueCode
1418 ic.Invoice_ID, --Task # 56151 --Invoice_ID
1419 ic.InvoiceCPT_ICDCode1, --ICDCode1 --Mantis # 24343 Section #1.7 837P Loop 2400 of claims using bundle codes are missing Diagnosis pointers in field SV107
1420 ic.InvoiceCPT_ICDCode2, --ICDCode2
1421 ic.InvoiceCPT_ICDCode3, --ICDCode3
1422 ic.InvoiceCPT_ICDCode4, --ICDCode4
1423 0, --Cost
1424 1, --Unit
1425 0, --UnitFee
1426 0, --FeeAmount
1427 0, --RVU
1428 '', --Dosage
1429 ic.InvoiceCPT_StartSerDate, --StartSerDate
1430 ic.InvoiceCPT_EndSerDate, --EndSerDate
1431 (Select IsNull((Select HCPCS_Code from HCPC_HCPCS where HCPCS_ID = @CurrBundle_ID), '')), --EFLineItem_Code
1432 ic.InvoiceCPT_StartTime, --StartTime
1433 ic.InvoiceCPT_EndTime, --EndTime
1434 ic.InvoiceCPT_PurchasingInd, --Purch
1435 ic.InvoiceCPT_Narrative, --Narrative
1436 0, --AllowedFee
1437 0, --AllowedUnitFee
1438 ic.InvoiceCPT_PlaceOfServiceCode, --POS
1439 ic.InvoiceCPT_TypeOfServiceCode, --TOS
1440 '', --Mod1
1441 '', --Mod2
1442 '', --Mod3
1443 '', --Mod4
1444 ic.InvoiceCPT_Sequence, --Sequence
1445 ic.InvoiceCPT_UnitCode, --UnitCode
1446 ic.InvoiceCPT_CodeType, --CodeType
1447 (Select IsNull((Select HCPCS_BillDescription from HCPC_HCPCS where HCPCS_ID = @CurrBundle_ID), '')), --Description
1448 1 --RollupRevenueCode 1 for all roll up HCPCS
1449 from #Bill_InvoiceCPT ic
1450 join
1451 #HCPC_HCPCSFeeSchdDetail hd on (hd.HCPCSFeeSchdDetail_ID = ic.HCPCSFeeSchdDetail_ID)
1452 --Joined on HCPC_HCPCS to match on Bundle codes
1453 join
1454 HCPC_HCPCS hh on (hh.HCPCS_ID = hd.HCPCS_ID3)
1455 where (ic.HCPCSFeeSchdDetail_ID <> '0000000000') and (hd.HCPCSFeeSchdDetail_RollupRevenueCode = 1)
1456 and (ltrim(rtrim(ic.InvoiceCPT_RevenueCode)) <> '') and ic.InvoiceCPT_Claim = 1 --and ic.InvoiceCPT_RecordState <> 1
1457 and (charindex(ic.InvoiceCPT_FileStatus, @LineStatus) > 0)
1458 --and ic.Invoice_ID = @Invoice_ID --Task # 56151
1459 and hd.HCPCSFeeSchdDetail_RecordState <> 1 and ic.InvoiceCPT_ID = @CurrInvoiceCPT_ID
1460 and hd.HCPCS_ID3 <> '0000000000'
1461 --order by hd.HCPCS_ID3 --Checked the condition for not null bundle code records
1462
1463
1464 --Making the current bundle id as previous bundle id
1465 set @PrevInvoiceCPT_ID = @CurrInvoiceCPT_ID
1466 set @PrevBundle_ID = @CurrBundle_ID
1467 set @CheckFlag = 1
1468 end
1469 else if (@PrevBundle_ID = @CurrBundle_ID)
1470 begin
1471 fetch next from crTemp into @CurrInvoiceCPT_ID, @CurrBundle_ID
1472 if (@@fetch_status <> 0) or (@PrevBundle_ID <> @CurrBundle_ID)
1473 begin
1474 Select @InvoiceCPT_Code = HCPCS_Code from hcpc_hcpcs where HCPCS_ID = @PrevBundle_ID
1475
1476 Select @Invoice_ID = Invoice_ID from #Bill_InvoiceCPT where InvoiceCPT_ID = @PrevInvoiceCPT_ID --Task # 56151
1477
1478 Select @FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(@InvoiceCPT_Code, '',
1479 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1480 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID, @PrevInvoiceCPT_ID) --Mantis # 28772
1481
1482 set @HCPCSFeeScheduleDetail_ID = substring(@FeeScheduleDetailID, 12, 21)
1483
1484 if @HCPCSFeeScheduleDetail_ID <> '0000000000'
1485 select top 1 @InvoiceCPT_ModifierCode1 = hm.HCPCSModifier_Code from hcpc_hcpcsmodifier hm
1486 join hcpc_hcpcsfeeschddetail hd on (hd.HCPCSModifier_ID1 = hm.HCPCSModifier_ID)
1487 where hd.HCPCSFeeSchdDetail_ID = @HCPCSFeeScheduleDetail_ID
1488 else
1489 set @InvoiceCPT_ModifierCode1 = ''
1490
1491 --Mantis # 24073 Start
1492 --Determing the Fee Schedule Detail ID from the billed fee schedule for calculating the unit fee
1493 if @InvoiceCPT_ModifierCode1 <> ''
1494 Select @FeeScheduleDetailID = dbo.udf_GetBilledFeeSchedule(@InvoiceCPT_Code, @InvoiceCPT_ModifierCode1,
1495 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1496 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID)
1497 else
1498 Select @FeeScheduleDetailID = dbo.udf_GetBilledFeeSchedule(@InvoiceCPT_Code, '',
1499 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1500 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID)
1501
1502 set @HCPCSFeeScheduleDetail_ID = substring(@FeeScheduleDetailID, 12, 21)
1503
1504 if @HCPCSFeeScheduleDetail_ID <> '0000000000'
1505 select @UnitFee = HCPCSFeeSchdDetail_Fee from hcpc_hcpcsfeeschddetail where HCPCSFeeSchdDetail_ID = @HCPCSFeeScheduleDetail_ID
1506 else
1507 select top 1 @UnitFee = hd.HCPCSDetail_PayPrivate from hcpc_hcpcsdetail hd join hcpc_hcpcs hh on (hd.HCPCS_ID = hh.HCPCS_ID)
1508 where hh.HCPCS_ID = @PrevBundle_ID
1509
1510 --Determing the Fee Schedule Detail ID from the allowed fee schedule for calculating the other costs
1511 if @InvoiceCPT_ModifierCode1 <> ''
1512 Select @FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(@InvoiceCPT_Code, @InvoiceCPT_ModifierCode1,
1513 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1514 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID, @PrevInvoiceCPT_ID) --Mantis # 28772
1515 else
1516 Select @FeeScheduleDetailID = dbo.udf_GetAllowedFeeSchedule(@InvoiceCPT_Code, '',
1517 '', '', '', '', '', @Invoice_MedicalFacility_ID, @Invoice_Provider_ID,
1518 @Invoice_Specialty_ID, @Invoice_ID, @InsuranceCompany_ID, @PrevInvoiceCPT_ID) --Mantis # 28772
1519
1520 set @HCPCSFeeScheduleDetail_ID = substring(@FeeScheduleDetailID, 12, 21)
1521
1522 if @HCPCSFeeScheduleDetail_ID <> '0000000000'
1523 select @FeeAmount = HCPCSFeeSchdDetail_Unit * @UnitFee, @AllowedUnitFee = HCPCSFeeSchdDetail_Fee, @AllowedFee = HCPCSFeeSchdDetail_Unit * HCPCSFeeSchdDetail_Fee,
1524 @Cost = HCPCSFeeSchdDetail_Cost from hcpc_hcpcsfeeschddetail where HCPCSFeeSchdDetail_ID = @HCPCSFeeScheduleDetail_ID
1525 else
1526 begin
1527 select top 1 @AllowedUnitFee = hd.HCPCSDetail_PayPrivate, @AllowedFee = hd.HCPCSDetail_PayPrivate,
1528 @Cost = hd.HCPCSDetail_Cost from hcpc_hcpcsdetail hd join hcpc_hcpcs hh on (hd.HCPCS_ID = hh.HCPCS_ID)
1529 where hh.HCPCS_ID = @PrevBundle_ID
1530 set @FeeAmount = @UnitFee
1531 end
1532 --Mantis # 24073 End
1533
1534 select top 1 @TempLineItem_ID = EFLineItem_ID from #TMP_EFW_EFLineItem order by EFLineItem_ID desc
1535
1536 Update
1537 #TMP_EFW_EFLineItem
1538 set
1539 EFLineItem_Cost = @Cost,
1540 EFLineItem_UnitFee = @UnitFee,
1541 EFLineItem_FeeAmount = @FeeAmount,
1542 EFLineItem_RVU = 0,
1543 EFLineItem_AllowedFee = @AllowedFee,
1544 EFLineItem_AllowedUnitFee = @AllowedUnitFee,
1545 EFLineItem_StartSerDate = (Select min(InvoiceCPT_StartSerDate) from #TempHCPCSBundle_Records where Bundle_ID = @PrevBundle_ID),
1546 EFLineItem_EndSerDate = (Select min(InvoiceCPT_EndSerDate) from #TempHCPCSBundle_Records where Bundle_ID = @PrevBundle_ID)
1547 where EFLineItem_ID = @TempLineItem_ID
1548 end
1549 continue;
1550 end
1551 end
1552 fetch next from crTemp into @CurrInvoiceCPT_ID, @CurrBundle_ID
1553 end
1554 close crTemp
1555 deallocate crTemp
1556
1557
1558end; -- end if (@RollupRev = 0)
1559
1560
1561
1562/*------------------------------------------------------------------------------*/
1563-- 2014/05/08-David Reed/Arul Prakash: mods for ICD-10 project to
1564-- remove cpt rows which do not match the active ICD family for an invoice.
1565/*------------------------------------------------------------------------------*/
1566
1567 declare @nonedate datetime
1568
1569 select @nonedate = isnull(InsuranceClass_ICD10StartDate,'10/01/2015')
1570 from LKUP_InsuranceClass
1571 where InsuranceClass_ID = '0000000000'
1572
1573 delete a
1574 from #TMP_EFW_EFLineItem a
1575 join
1576 #BILL_EFBatch b on a.Invoice_ID = b.Invoice_ID
1577 join
1578 #BILL_InvoiceCPT d on a.InvoiceCPT_ID = d.InvoiceCPT_ID
1579 --join BILL_EFBatch b
1580 --on a.EFLineItem_BatchNo = b.EFBatch_Number
1581
1582 --join BILL_InvoiceIns c
1583 -- on b.InsuranceCompany_ID = c.InsuranceCompany_ID
1584 -- and c.Invoice_ID = a.Invoice_ID
1585
1586 --join BILL_InvoiceCPT d
1587 -- on a.InvoiceCPT_ID = d.InvoiceCPT_ID
1588
1589 --where EFLineItem_BatchNo = @BatchNo
1590 --and (
1591 -- ((InvoiceCPT_IsICD10 = 0) and (EfLineItem_EndSerDate >= isnull(c.InvoiceIns_InsuranceClassICD10StartDate,@nonedate)))
1592 -- or
1593 -- ((InvoiceCPT_IsICD10 = 1) and (EfLineItem_EndSerDate < isnull(c.InvoiceIns_InsuranceClassICD10StartDate,@nonedate)))
1594 -- )
1595
1596 where
1597 ((d.InvoiceCPT_IsICD10 = 0) and (a.EfLineItem_EndSerDate >= isnull(b.InvoiceIns_InsuranceClassICD10StartDate,@nonedate)))
1598 or
1599 ((d.InvoiceCPT_IsICD10 = 1) and (a.EfLineItem_EndSerDate < isnull(b.InvoiceIns_InsuranceClassICD10StartDate,@nonedate)))
1600
1601
1602/*------------------------------------------------------------------------------*/
1603/*------------------------------------------------------------------------------*/
1604
1605
1606 --Mantis # 24023 and 24024 Start
1607 declare crInvoice Cursor FOR
1608 --select Invoice_ID from BILL_EFBatch where EFBatch_Number = @BatchNo
1609 select Invoice_ID from #BILL_EFBatch
1610 open crInvoice
1611 fetch next from crInvoice into @Invoive_LineItem
1612 while @@fetch_status = 0
1613 begin
1614 set @SequenceCount = 1
1615 --Mantis # 24988 & 24891
1616 set @PrimaryCPT_ID = dbo.udf_GetPrimaryCPT(@Invoive_LineItem)
1617 if (Select UPPER(SubString(InvoiceCPT_Code,1,1)) from #Bill_InvoiceCPT where InvoiceCPT_ID = @PrimaryCPT_ID and InvoiceCPT_RecordState <> 1) = 'T'
1618 begin
1619 declare crLineItem cursor for
1620 Select EFLineItem_ID from #TMP_EFW_EFLineItem where Invoice_ID = @Invoive_LineItem and
1621 EFLineItem_BatchNo = @BatchNo
1622 order by
1623 case when @SortByRVU = 1 then EFLineItem_RVU end desc,
1624 case
1625 when InvoiceCPT_ID = @PrimaryCPT_ID then
1626 0
1627 else
1628 --ROW_NUMBER() OVER (ORDER BY InvoiceCPT_ID)
1629 case when @SortByRVU = 1 then ROW_NUMBER() OVER (ORDER BY InvoiceCPT_ID) else EFLineItem_Sequence end --TFS # 43506 Fixed CPT Order
1630 end
1631 end
1632 else
1633 begin
1634 declare crLineItem cursor for
1635 Select EFLineItem_ID from #TMP_EFW_EFLineItem where Invoice_ID = @Invoive_LineItem and
1636 EFLineItem_BatchNo = @BatchNo
1637 order by
1638 case when @SortByRVU = 1 then EFLineItem_RVU end desc,
1639 --InvoiceCPT_ID
1640 case when @SortByRVU = 1 then ROW_NUMBER() OVER (ORDER BY InvoiceCPT_ID) else EFLineItem_Sequence end --TFS # 43506 Fixed CPT Order
1641 end
1642 open crLineItem
1643 fetch next from crLineItem into @LineItem_ID
1644 while @@fetch_status = 0
1645 begin
1646 update #TMP_EFW_EFLineItem set EFLineItem_Sequence = @SequenceCount
1647 where EFLineItem_ID = @LineItem_ID
1648 set @SequenceCount = @SequenceCount + 1
1649 fetch next from crLineItem into @LineItem_ID
1650 end
1651 close crLineItem
1652 deallocate crLineItem
1653 fetch next from crInvoice into @Invoive_LineItem
1654 end
1655 close crInvoice
1656 deallocate crInvoice
1657 --Mantis # 24023 and 24024 End
1658 --set nocount off
1659 --Mantis # 21856 End
1660
1661
1662--User Story 59792 - Begin
1663declare @PayorType_ID varchar(10)
1664declare @Date_MedicareRHCChangeApply datetime
1665declare @Date_MedicareRHC_CG_ChangeApply datetime
1666
1667set @Date_MedicareRHCChangeApply = '04/01/2016'
1668set @Date_MedicareRHC_CG_ChangeApply = '10/01/2016'
1669
1670select @PayorType_ID = PayorType_ID from LKUP_PayorType where PayorType_Code = 'MA' and PayorType_RecordState <> 1
1671
1672
1673if (@EFType = 'I')
1674 and (@PayorType_ID is not null)
1675 and Exists( select 1 from #BILL_EFBatch where PayorType_ID = @PayorType_ID )
1676begin
1677 --declare @Qualifed_Str varchar(max)
1678 --declare @Preventive_Str varchar(max)
1679
1680 declare @Qualified_CodeModifier char(02)
1681 declare @LKUP_QualifiedList table(QualifiedCode varchar(10))
1682 declare @LKUP_PreventiveList table(PreventiveCode varchar(10))
1683
1684 set @Qualified_CodeModifier = 'CG'
1685
1686 create table #InvoicePrimaryCPT
1687 (
1688 InvoiceCPT_ID char(10) not null primary key,
1689 BatchNo int,
1690 Invoice_ID char(10) ,
1691 InvoiceCPT_Code varchar(10),
1692 InvoiceCPT_FeeAmount money,
1693 PayorType_ID varchar(10)
1694 )
1695
1696 create table #InvoicePreventedCPT
1697 (
1698 InvoiceCPT_ID char(10) not null primary key,
1699 BatchNo int,
1700 Invoice_ID char(10) ,
1701 InvoiceCPT_Code varchar(10),
1702 InvoiceCPT_FeeAmount money,
1703 PayorType_ID varchar(10)
1704 )
1705
1706 /*
1707 set @Qualifed_str = 'G0101,G0102,G0117,G0118,G0296,G0402,G0436,G0437,G0438,G0439,G0442,G0443,G0444,G0445,G0446,G0447,92002,92004,92012,92014,99201,99202,99203,99204,99205,99212,99213,99214,99215,99304,99305,99306,99307,99308,99309,99310,99315,99316,99318,99324,99325,99326,99327,99328,99334,99335,99336,99337,99341,99342,99343,99344,99345,99347,99348,99349,99350,99495,99496,99497,90791,90792,90832,90834,90837,90839,90845,10040,10060,10061,10080,10081,10120,10121,10140,10160,11000,11010,11011,11042,11055,11056,11057,11100,11200,11300,11301,11302,11303,11305,11306,11307,11308,11310,11311,11312,11313,11400,11401,11402,11403,11404,11406,11420,11421,11422,11423,11424,11426,11440,11441,11442,11443,11444,11446,11450,11600,11601,11602,11603,11604,11606,11620,11621,11622,11623,11624,11626,11640,11641,11642,11643,11644,11646,11719,11720,11721,11730,11740,11750,11752,11755,11760,11762,11765,12001,12002,12004,12005,12006,12007,12011,12013,12014,12015,12016,12017,12018,12020,12021,12031,12032,12034,12035,12036,12037,12041,12042,12044,12045,12046,12047,12051,12052,12053,12054,12055,12056,12057,17000,17003,17004,17106,17107,17108,17110,17111,17250,17260,17261,17262,17263,17264,17266,17270,17271,17272,17273,17274,17276,17280,17281,17282,17283,17284,17286,20500,20501,20520,20525,20526,20527,20550,20551,20552,20553,20600,20604,20605,20606,20610,20611,20612,20615,97597,97602,98940,98941,98942,Q0091'
1708 set @Preventive_Str = 'G0402,G0438,G0439,G0101,G0102,G0117,G0118,Q0091,G0442,G0443,G0444,G0445,G0446,G0447,G0436,G0437,G0296'
1709
1710
1711 insert into @LKUP_QualifiedList
1712 select Parsed_Value from dbo.udf_SplitFormattedString(@Qualifed_str, ',')
1713
1714 insert into @LKUP_PreventiveList
1715 select Parsed_Value from dbo.udf_SplitFormattedString(@Preventive_Str, ',')
1716*/
1717 insert into @LKUP_QualifiedList
1718 Values
1719 ('G0101'),('G0102'),('G0117'),('G0118'),('G0296'),('G0402'),('G0436'),('G0437'),('G0438'),('G0439'),('G0442'),('G0443'),
1720 ('G0444'),('G0445'),('G0446'),('G0447'),('92002'),('92004'),('92012'),('92014'),('99201'),('99202'),('99203'),('99204'),
1721 ('99205'),('99212'),('99213'),('99214'),('99215'),('99304'),('99305'),('99306'),('99307'),('99308'),('99309'),('99310'),
1722 ('99315'),('99316'),('99318'),('99324'),('99325'),('99326'),('99327'),('99328'),('99334'),('99335'),('99336'),('99337'),
1723 ('99341'),('99342'),('99343'),('99344'),('99345'),('99347'),('99348'),('99349'),('99350'),('99495'),('99496'),('99497'),
1724 ('90791'),('90792'),('90832'),('90834'),('90837'),('90839'),('90845'),('10040'),('10060'),('10061'),('10080'),('10081'),
1725 ('10120'),('10121'),('10140'),('10160'),('11000'),('11010'),('11011'),('11042'),('11055'),('11056'),('11057'),('11100'),
1726 ('11200'),('11300'),('11301'),('11302'),('11303'),('11305'),('11306'),('11307'),('11308'),('11310'),('11311'),('11312'),
1727 ('11313'),('11400'),('11401'),('11402'),('11403'),('11404'),('11406'),('11420'),('11421'),('11422'),('11423'),('11424'),
1728 ('11426'),('11440'),('11441'),('11442'),('11443'),('11444'),('11446'),('11450'),('11600'),('11601'),('11602'),('11603'),
1729 ('11604'),('11606'),('11620'),('11621'),('11622'),('11623'),('11624'),('11626'),('11640'),('11641'),('11642'),('11643'),
1730 ('11644'),('11646'),('11719'),('11720'),('11721'),('11730'),('11740'),('11750'),('11752'),('11755'),('11760'),('11762'),
1731 ('11765'),('12001'),('12002'),('12004'),('12005'),('12006'),('12007'),('12011'),('12013'),('12014'),('12015'),('12016'),
1732 ('12017'),('12018'),('12020'),('12021'),('12031'),('12032'),('12034'),('12035'),('12036'),('12037'),('12041'),('12042'),
1733 ('12044'),('12045'),('12046'),('12047'),('12051'),('12052'),('12053'),('12054'),('12055'),('12056'),('12057'),('17000'),
1734 ('17003'),('17004'),('17106'),('17107'),('17108'),('17110'),('17111'),('17250'),('17260'),('17261'),('17262'),('17263'),
1735 ('17264'),('17266'),('17270'),('17271'),('17272'),('17273'),('17274'),('17276'),('17280'),('17281'),('17282'),('17283'),
1736 ('17284'),('17286'),('20500'),('20501'),('20520'),('20525'),('20526'),('20527'),('20550'),('20551'),('20552'),('20553'),
1737 ('20600'),('20604'),('20605'),('20606'),('20610'),('20611'),('20612'),('20615'),('97597'),('97602'),('98940'),('98941'),
1738 ('98942'),('Q0091')
1739
1740 insert into @LKUP_PreventiveList
1741 Values
1742 ('G0402'),('G0438'),('G0439'),('G0101'),('G0102'),('G0117'),('G0118'),('Q0091'),('G0442'),('G0443'),('G0444'),('G0445'),('G0446'),('G0447'),('G0436'),('G0437'),('G0296')
1743
1744 Insert into #InvoicePrimaryCPT
1745 select i.InvoiceCPT_ID, i.EFLineItem_BatchNo, i.Invoice_ID, i.EFLineItem_Code, i.EFLineItem_FeeAmount, b.PayorType_ID
1746 from #TMP_EFW_EFLineItem i
1747 join(
1748 select t.EFBatch_number, t.Invoice_ID, s.InvoiceCPT_ID , t.PayorType_ID
1749 from #BILL_EFBatch t
1750 cross apply dbo.udf_itv_GetPrimaryCPT(t.Invoice_ID) s
1751 where t.PayorType_ID = @PayorType_ID
1752 ) b on b.EFBatch_number = i.EFLineItem_BatchNo and b.Invoice_ID = i.Invoice_ID and b.InvoiceCPT_ID = i.InvoiceCPT_ID and b.InvoiceCPT_ID <> '0000000000'
1753 join @LKUP_QualifiedList q on q.QualifiedCode = i.EFLineItem_Code
1754 where i.EFLineItem_EndSerDate >= @Date_MedicareRHCChangeApply and i.EFLineItem_EndSerDate < @Date_MedicareRHC_CG_ChangeApply
1755 union all
1756 -- Insert into #InvoicePrimaryCPT
1757 select i.InvoiceCPT_ID, i.EFLineItem_BatchNo, i.Invoice_ID, i.EFLineItem_Code, i.EFLineItem_FeeAmount, b.PayorType_ID
1758 from #TMP_EFW_EFLineItem i
1759 join(
1760 select t.EFBatch_number, t.Invoice_ID, s.InvoiceCPT_ID , t.PayorType_ID
1761 from #BILL_EFBatch t
1762 cross apply dbo.udf_itv_GetPrimaryCPT(t.Invoice_ID) s
1763 where t.PayorType_ID = @PayorType_ID
1764 ) b on b.EFBatch_number = i.EFLineItem_BatchNo and b.Invoice_ID = i.Invoice_ID and b.InvoiceCPT_ID = i.InvoiceCPT_ID and b.InvoiceCPT_ID <> '0000000000'
1765 where i.EFLineItem_EndSerDate >= @Date_MedicareRHC_CG_ChangeApply
1766 and (i.EFLineItem_ModifierCode1 = @Qualified_CodeModifier
1767 or i.EFLineItem_ModifierCode2 = @Qualified_CodeModifier
1768 or i.EFLineItem_ModifierCode3 = @Qualified_CodeModifier
1769 or i.EFLineItem_ModifierCode4 = @Qualified_CodeModifier)
1770
1771
1772 insert into #InvoicePreventedCPT
1773 select distinct i.InvoiceCPT_ID, i.EFLineItem_BatchNo, i.Invoice_ID, i.EFLineItem_Code, i.EFLineItem_FeeAmount, pc.PayorType_ID
1774 from #TMP_EFW_EFLineItem i
1775 join #InvoicePrimaryCPT pc on pc.BatchNo = i.EFLineItem_BatchNo and pc.Invoice_ID = i.Invoice_ID and pc.InvoiceCPT_ID <> i.InvoiceCPT_ID and pc.InvoiceCPT_ID <> '0000000000' and pc.PayorType_ID = @PayorType_ID
1776 where i.EFLineItem_EndSerDate >= @Date_MedicareRHCChangeApply and i.EFLineItem_Code not in (select PreventiveCode from @LKUP_PreventiveList )
1777
1778
1779 update p set p.InvoiceCPT_FeeAmount = x.FeeAmountSum
1780 from #InvoicePrimaryCPT p
1781 join
1782 ( select p.BatchNo, p.Invoice_ID, p.InvoiceCPT_FeeAmount + SUM(x.InvoiceCPT_FeeAmount) FeeAmountSum
1783 from #InvoicePrimaryCPT p
1784 join #InvoicePreventedCPT x on x.BatchNo = p.BatchNo and x.Invoice_ID = p.Invoice_ID
1785 group by p.BatchNo, p.Invoice_ID, p.InvoiceCPT_FeeAmount
1786 ) x on x.BatchNo = p.BatchNo and x.Invoice_ID = p.Invoice_ID
1787
1788 update t set t.EFLineItem_FeeAmount = pryc.InvoiceCPT_FeeAmount
1789 from #TMP_EFW_EFLineItem t
1790 join #InvoicePrimaryCPT pryc on t.EFLineItem_BatchNo = pryc.BatchNo and t.Invoice_ID = pryc.Invoice_ID and pryc.InvoiceCPT_ID = t.InvoiceCPT_ID
1791
1792end --if
1793
1794--User Story 59792 - end
1795
1796delete from TMP_EFW_EFLineItem where EFLineItem_BatchNo = @EFBatch_Number --Task 56151
1797
1798insert into TMP_EFW_EFLineItem --Task 56151
1799(
1800 EfLineItem_BatchNo,
1801 InvoiceCPT_ID,
1802 EFLineItem_RevenueCode,
1803 Invoice_ID,
1804 EFLineItem_ICDCode1,
1805 EFLineItem_ICDCode2,
1806 EFLineItem_ICDCode3,
1807 EFLineItem_ICDCode4,
1808 EFLineItem_Cost,
1809 EFLineItem_Unit,
1810 EFLineItem_UnitFee,
1811 EFLineItem_FeeAmount,
1812 EFLineItem_RVU,
1813 EFLineItem_Dosage,
1814 EFLineItem_StartSerDate,
1815 EFLineItem_EndSerDate,
1816 EFLineItem_Code,
1817 EFLineItem_StartTime,
1818 EFLineItem_EndTime,
1819 EFLineItem_PurchasingInd,
1820 EFLineItem_Narrative,
1821 EFLineItem_AllowedFee,
1822 EFLineItem_AllowedUnitFee,
1823 EFLineItem_PlaceOfServiceCode,
1824 EFLineItem_TypeOfServiceCode,
1825 EFLineItem_ModifierCode1,
1826 EFLineItem_ModifierCode2,
1827 EFLineItem_ModifierCode3,
1828 EFLineItem_ModifierCode4,
1829 EFLineItem_Sequence,
1830 EFLineItem_UnitCode,
1831 EFLineItem_CodeType,
1832 EFLineItem_CodeBillDescription,
1833 EFLineItem_RevenueRollup
1834)
1835select EfLineItem_BatchNo,
1836 InvoiceCPT_ID,
1837 EFLineItem_RevenueCode,
1838 Invoice_ID,
1839 EFLineItem_ICDCode1,
1840 EFLineItem_ICDCode2,
1841 EFLineItem_ICDCode3,
1842 EFLineItem_ICDCode4,
1843 EFLineItem_Cost,
1844 EFLineItem_Unit,
1845 EFLineItem_UnitFee,
1846 EFLineItem_FeeAmount,
1847 EFLineItem_RVU,
1848 EFLineItem_Dosage,
1849 EFLineItem_StartSerDate,
1850 EFLineItem_EndSerDate,
1851 EFLineItem_Code,
1852 EFLineItem_StartTime,
1853 EFLineItem_EndTime,
1854 EFLineItem_PurchasingInd,
1855 EFLineItem_Narrative,
1856 EFLineItem_AllowedFee,
1857 EFLineItem_AllowedUnitFee,
1858 EFLineItem_PlaceOfServiceCode,
1859 EFLineItem_TypeOfServiceCode,
1860 EFLineItem_ModifierCode1,
1861 EFLineItem_ModifierCode2,
1862 EFLineItem_ModifierCode3,
1863 EFLineItem_ModifierCode4,
1864 EFLineItem_Sequence,
1865 EFLineItem_UnitCode,
1866 EFLineItem_CodeType,
1867 EFLineItem_CodeBillDescription,
1868 EFLineItem_RevenueRollup
1869from #TMP_EFW_EFLineItem
1870-- end call to usp_bilANSILineItem
1871
1872 -- ANSI Professional
1873 if @EFType = 'P'
1874 begin
1875 if @AnsiFormat = '4'
1876 begin
1877 if (@WithProviderLoop = 'Y')
1878 select
1879 LineString, LineNumber
1880 from
1881 dbo.udf_AnsiEf_Prof_WithProviderLoop(
1882 @EFBatch_Number,
1883 @MedicalFacility_ID,
1884 @ClearingHouseName,
1885 @SecurityInformation,
1886 @ReceiverID,
1887 @ApplicationSenderCode,
1888 @ApplicationReceiverCode,
1889 @SubmitterID,
1890 @PurposeCode,
1891 @IsBillingService,
1892 @TransmisstionType,
1893 @Type,
1894 @NoCredit,
1895 @ClearingHouse_ID,
1896 @AcknowledgmentRequest)
1897 order by LineNumber
1898 else
1899 select
1900 LineString, LineNumber
1901 from
1902 dbo.udf_AnsiEf_Prof_WithoutProviderLoop(
1903 @EFBatch_Number,
1904 @MedicalFacility_ID,
1905 @ClearingHouseName,
1906 @SecurityInformation,
1907 @ReceiverID,
1908 @ApplicationSenderCode,
1909 @ApplicationReceiverCode,
1910 @SubmitterID,
1911 @PurposeCode,
1912 @IsBillingService,
1913 @TransmisstionType,
1914 @Type,
1915 @NoCredit,
1916 @ClearingHouse_ID,
1917 @AcknowledgmentRequest)
1918 order by
1919 LineNumber
1920 end -- @AnsiFormat = 4
1921 else -- @AnsiFormat = 5
1922 begin
1923 if (@WithProviderLoop = 'Y')
1924 select
1925 LineString, LineNumber
1926 from
1927 dbo.udf_5010_AnsiEf_Prof_WithProviderLoop(
1928 @EFBatch_Number,
1929 @MedicalFacility_ID,
1930 @ClearingHouseName,
1931 @SecurityInformation,
1932 @ReceiverID,
1933 @ApplicationSenderCode,
1934 @ApplicationReceiverCode,
1935 @SubmitterID,
1936 @PurposeCode,
1937 @IsBillingService,
1938 @TransmisstionType,
1939 @Type,
1940 @NoCredit,
1941 @ClearingHouse_ID,
1942 @AcknowledgmentRequest)
1943 order by LineNumber
1944 else
1945 select
1946 LineString, LineNumber
1947 from
1948 dbo.udf_5010_AnsiEf_Prof_WithoutProviderLoop(
1949 @EFBatch_Number,
1950 @MedicalFacility_ID,
1951 @ClearingHouseName,
1952 @SecurityInformation,
1953 @ReceiverID,
1954 @ApplicationSenderCode,
1955 @ApplicationReceiverCode,
1956 @SubmitterID,
1957 @PurposeCode,
1958 @IsBillingService,
1959 @TransmisstionType,
1960 @Type,
1961 @NoCredit,
1962 @ClearingHouse_ID,
1963 @AcknowledgmentRequest)
1964 order by
1965 LineNumber
1966
1967 end -- @AnsiFormat = 5
1968
1969 end
1970 else -- ANSI Institutional
1971 begin
1972 if @AnsiFormat = '4'
1973 begin
1974
1975 if (@WithProviderLoop = 'Y')
1976 select
1977 LineString, Linenumber
1978 from
1979 dbo.udf_AnsiEf_Inst_WithProviderLoop(
1980 @EFBatch_Number,
1981 @MedicalFacility_ID,
1982 @ClearingHouseName,
1983 @SecurityInformation,
1984 @ReceiverID,
1985 @ApplicationSenderCode,
1986 @ApplicationReceiverCode,
1987 @SubmitterID,
1988 @PurposeCode,
1989 @IsBillingService,
1990 @TransmisstionType,
1991 @Type,
1992 @NoCredit,
1993 @ClearingHouse_ID,
1994 @AcknowledgmentRequest)
1995 order by
1996 LineNumber
1997 else
1998 select
1999 LineString, LineNumber
2000 from
2001 dbo.udf_AnsiEf_Inst_WithoutProviderLoop(
2002 @EFBatch_Number,
2003 @MedicalFacility_ID,
2004 @ClearingHouseName,
2005 @SecurityInformation,
2006 @ReceiverID,
2007 @ApplicationSenderCode,
2008 @ApplicationReceiverCode,
2009 @SubmitterID,
2010 @PurposeCode,
2011 @IsBillingService,
2012 @TransmisstionType,
2013 @Type,
2014 @NoCredit,
2015 @ClearingHouse_ID,
2016 @AcknowledgmentRequest)
2017 order by
2018 LineNumber
2019 end -- @AnsiFormat = '4'
2020 else -- -- @AnsiFormat = '5'
2021 begin
2022 if (@WithProviderLoop = 'Y')
2023 select
2024 LineString, Linenumber
2025 from
2026 dbo.udf_5010_AnsiEf_Inst_WithProviderLoop(
2027 @EFBatch_Number,
2028 @MedicalFacility_ID,
2029 @ClearingHouseName,
2030 @SecurityInformation,
2031 @ReceiverID,
2032 @ApplicationSenderCode,
2033 @ApplicationReceiverCode,
2034 @SubmitterID,
2035 @PurposeCode,
2036 @IsBillingService,
2037 @TransmisstionType,
2038 @Type,
2039 @NoCredit,
2040 @ClearingHouse_ID,
2041 @AcknowledgmentRequest)
2042 order by
2043 LineNumber
2044 else
2045 select
2046 LineString, LineNumber
2047 from
2048 dbo.udf_5010_AnsiEf_Inst_WithoutProviderLoop(
2049 @EFBatch_Number,
2050 @MedicalFacility_ID,
2051 @ClearingHouseName,
2052 @SecurityInformation,
2053 @ReceiverID,
2054 @ApplicationSenderCode,
2055 @ApplicationReceiverCode,
2056 @SubmitterID,
2057 @PurposeCode,
2058 @IsBillingService,
2059 @TransmisstionType,
2060 @Type,
2061 @NoCredit,
2062 @ClearingHouse_ID,
2063 @AcknowledgmentRequest)
2064 order by
2065 LineNumber
2066 end -- @AnsiFormat = '5'
2067 end
2068
2069delete from TMP_EFW_EFLineItem
2070where EFLineItem_BatchNo = @EFBatch_Number
2071
2072end
2073go
2074
2075IF OBJECT_ID('dbo.usp_bilAnsiEf') IS NOT NULL
2076 RAISERROR ('<<< CREATED PROCEDURE dbo.usp_bilAnsiEf >>>',0,0)
2077ELSE
2078 RAISERROR ('<<< FAILED CREATING PROCEDURE dbo.usp_bilAnsiEf >>>',0,0)
2079go
2080SET ANSI_NULLS OFF
2081go
2082SET QUOTED_IDENTIFIER OFF
2083go