public static List <HipaaXML> GetHipaaXMLForCAP0x001C7_P()
{
List <HipaaXML> result = null;
using (var context = new SubHistContext())
{
context.Database.CommandTimeout = 1800;
StringBuilder sb = new StringBuilder();
sb.Append("select c.clm01 as ClaimID,d.EncounterId,cast(transactiondocument as varchar(max)) as ClaimHipaaXML from Bizsqlb1_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join Bizsqlb1_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select parentid,clm01,row_number() over(partition by clm01 order by ordinal desc) rn from Bizsqlb1_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("outer apply (select top 1 encounterid from edireports.dbo.DHCS_Response_FlatList where encounterreferencenumber=c.clm01 order by encounterstatus,EncounterSubmissionDate desc) d ");
sb.Append("where c.clm01 in (select distinct encounterid from EDIReports.dbo.DHCS_CAP_Denials where DHCSErrorNum='0x001C7') ");
sb.Append("union all ");
sb.Append("select c.clm01 as ClaimID,d.EncounterId,cast(transactiondocument as varchar(max)) as ClaimHipaaXML from venus_bizprod_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join venus_bizprod_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select parentid,clm01,row_number() over(partition by clm01 order by ordinal desc) rn from venus_bizprod_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("outer apply (select top 1 encounterid from edireports.dbo.DHCS_Response_FlatList where encounterreferencenumber=c.clm01 order by encounterstatus,EncounterSubmissionDate desc) d ");
sb.Append("where c.clm01 in (select distinct encounterid from EDIReports.dbo.DHCS_CAP_Denials where DHCSErrorNum='0x001C7') ");
sb.Append("union all ");
sb.Append("select c.clm01 as ClaimID,d.EncounterId,cast(transactiondocument as varchar(max)) as ClaimHipaaXML from mars_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join mars_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select parentid,clm01,row_number() over(partition by clm01 order by ordinal desc) rn from mars_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("outer apply (select top 1 encounterid from edireports.dbo.DHCS_Response_FlatList where encounterreferencenumber=c.clm01 order by encounterstatus,EncounterSubmissionDate desc) d ");
sb.Append("where c.clm01 in (select distinct encounterid from EDIReports.dbo.DHCS_CAP_Denials where DHCSErrorNum='0x001C7')");
result = context.Database.SqlQuery <HipaaXML>(sb.ToString()).ToList();
}
return(result);
}
public static List <HipaaXML> GetHipaaXMLForCAPProcedureCode_P()
{
List <HipaaXML> result = null;
using (var context = new SubHistContext())
{
context.Database.CommandTimeout = 900;
StringBuilder sb = new StringBuilder();
sb.Append("select c.clm01 as ClaimID,d.EncounterId,cast(transactiondocument as varchar(max)) as ClaimHipaaXML from Bizsqlb1_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join Bizsqlb1_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select parentid,clm01,row_number() over(partition by clm01 order by ordinal desc) rn from Bizsqlb1_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("outer apply (select top 1 encounterid from edireports.dbo.DHCS_Response_FlatList where encounterreferencenumber=c.clm01 order by encounterstatus,EncounterSubmissionDate desc) d ");
sb.Append("where c.clm01 in (");
sb.Append("select distinct EncounterId from EDIReports.dbo.DHCS_CAP_ProcCode_DetailedError ");
sb.Append("where ErrorType ='HCPCS' ");
sb.Append("and ltrim(rtrim(ProcedureHCPCSCode)) ");
sb.Append("IN ('X3900','X3902','X3904','X3906','X3908','X3910','X3920','X3922','X3924','X3926',");
sb.Append("'X3930','X3936','X4100','X4102','X4104','X4106','X4108','X4110','X4112','X4118',");
sb.Append("'X4300','X4301','X4308','X4312','X4320','X4500','X4501','X4502','X4504','X4522',");
sb.Append("'X4526','X4544','Z0100','Z0102','Z0104','Z0106','Z0108','Z4302','Z4306','Z4310',");
sb.Append("'Z4311','Z4312','Z4315','Z5414','Z5802','Z5820','Z5900','Z5902','Z5904','Z5906',");
sb.Append("'Z5908','Z5910','Z5912','Z5914','Z5916','Z5918','Z5920','Z5922','Z5924','Z5940',");
sb.Append("'Z5942','Z5944','Z5946','Z5950','Z5956','Z5966','Z6004','Z6006','Z6020','Z6404',");
sb.Append("'Z9725','Z9726','Z9727'))");
result = context.Database.SqlQuery <HipaaXML>(sb.ToString()).ToList();
}
return(result);
}
public static string GetPrimaryDiagnosisCode_Bizsqlb1(string ClaimID)
{
string result = null;
using (var context = new SubHistContext())
{
string sqltext = "select a.c02202 from bizsqlb1_wpc_valid_837p.dbo.c022_healthcarecodeinformation a inner join bizsqlb1_wpc_valid_837p.dbo.hi b on a.parentid=b.id inner join bizsqlb1_wpc_valid_837p.dbo.loops c on b.parentid=c.id and c.loopName='2300' inner join bizsqlb1_wpc_valid_837p.dbo.clm d on d.parentid=c.id where d.CLM01='" + ClaimID + "'";
result = context.Database.SqlQuery <string>(sqltext).FirstOrDefault();
}
return(result);
}
public static BillingProviderAdditionalDataElements GetBillingProviderAdditionalDataElements(string ProviderNPI)
{
BillingProviderAdditionalDataElements result = null;
using (var context = new SubHistContext())
{
string sqltext = "select EIN,Prov1stPracticeAddress as ProviderAddress,ProvPracticeCity as ProviderCity,ProvPracticeState as ProviderState,ProvPracticeZip as ProviderZip,ProvPracticeCountry as ProviderCountry from bizsqlb1_edimanagement.dbo.NPI where NPI='" + ProviderNPI + "'";
result = context.Database.SqlQuery <BillingProviderAdditionalDataElements>(sqltext).FirstOrDefault();
}
return(result);
}
public static List <SubHist_Header> GetSubHistHeader(string Code)
{
List <SubHist_Header> result = null;
using (var context = new SubHistContext())
{
context.Database.CommandTimeout = 1800;
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb.Append("ProductionFlag,");
sb.Append("GroupControlNumber,");
sb.Append("ClaimNo,");
sb.Append("PlaceOfService,");
sb.Append("FacilityCode,");
sb.Append("ClaimFrequency,");
sb.Append("BillProvNPI,");
sb.Append("BillProvLast,");
sb.Append("BillProvFirst,");
sb.Append("BillProvSpecialty,");
sb.Append("BillProvZip,");
sb.Append("SubscriberLast,");
sb.Append("SubscriberFirst,");
sb.Append("SubscriberID,");
sb.Append("SubscriberZip,");
sb.Append("SubscriberDOB,");
sb.Append("SubscriberGender,");
sb.Append("SubscriberSSN,");
sb.Append("PatientLast,");
sb.Append("PatientFirst,");
sb.Append("PatientID,");
sb.Append("PatientSSN,");
sb.Append("PatientDOB,");
sb.Append("PatientGender,");
sb.Append("RendProvNPI,");
sb.Append("RendProvLast,");
sb.Append("RendProvFirst,");
sb.Append("PickUpAddress,");
sb.Append("PickUpAddress2,");
sb.Append("PickUpCity,");
sb.Append("PickUpState,");
sb.Append("PickUpZip,");
sb.Append("DropOffName,");
sb.Append("DropOffAddress,");
sb.Append("DropOffAddress2,");
sb.Append("DropOffCity,");
sb.Append("DropOffState,");
sb.Append("DropOffZip,");
sb.Append("BilledAmount,");
sb.Append("EstimatedAmountDue,");
sb.Append("ServiceDateFrom,");
sb.Append("ServiceDateTo,");
sb.Append("AdmissionDate,");
sb.Append("AdmissionType,");
sb.Append("AdmissionSource,");
sb.Append("DischargeDate,");
sb.Append("PatientStatus,");
sb.Append("ClearinghouseID,");
sb.Append("PayerClaimControlNumber,");
sb.Append("DemonstrationProjectID,");
sb.Append("ContractType,");
sb.Append("ContractAmount,");
sb.Append("PrincipalDiagnosis_Qual,");
sb.Append("PrincipalDiagnosis,");
sb.Append("Diag2_Qual,");
sb.Append("Diag2,");
sb.Append("Diag3_Qual,");
sb.Append("Diag3,");
sb.Append("Diag4_Qual,");
sb.Append("Diag4,");
sb.Append("Diag5_Qual,");
sb.Append("Diag5,");
sb.Append("Diag6_Qual,");
sb.Append("Diag6,");
sb.Append("Diag7_Qual,");
sb.Append("Diag7,");
sb.Append("Diag8_Qual,");
sb.Append("Diag8,");
sb.Append("Diag9_Qual,");
sb.Append("Diag9,");
sb.Append("Diag10_Qual,");
sb.Append("Diag10,");
sb.Append("Diag11_Qual,");
sb.Append("Diag11,");
sb.Append("Diag12_Qual,");
sb.Append("Diag12,");
sb.Append("Diag13_Qual,");
sb.Append("Diag13,");
sb.Append("Diag14_Qual,");
sb.Append("Diag14,");
sb.Append("Diag15_Qual,");
sb.Append("Diag15,");
sb.Append("Diag16_Qual,");
sb.Append("Diag16,");
sb.Append("Diag17_Qual,");
sb.Append("Diag17,");
sb.Append("Diag18_Qual,");
sb.Append("Diag18,");
sb.Append("Diag19_Qual,");
sb.Append("Diag19,");
sb.Append("Diag20_Qual,");
sb.Append("Diag20,");
sb.Append("Diag21_Qual,");
sb.Append("Diag21,");
sb.Append("Diag22_Qual,");
sb.Append("Diag22,");
sb.Append("Diag23_Qual,");
sb.Append("Diag23,");
sb.Append("Diag24_Qual,");
sb.Append("Diag24,");
sb.Append("Diag25_Qual,");
sb.Append("Diag25,");
sb.Append("AmbulanceTransportReasonCode,");
sb.Append("TransportDistance ");
sb.Append("from EncounterSubmissionHistory.dbo.SubmissionHistoryHeader a where a.ClaimNo in (");
sb.Append("select distinct IehpClaimId from edrs.dbo.DHCSDenialMaster a ");
sb.Append("inner join (select encounterreferencenumber,id,encounterstatus,transaction_id,row_number() over (partition by encounterreferencenumber order by id desc) as rn from IEHP_DHCSResponse.dbo.DHCSResponse_Encounter ");
sb.Append(") b on b.rn=1 and a.IEHPClaimId=b.EncounterReferenceNumber ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse_Transaction c on b.Transaction_ID=c.ID ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse d on c.File_ID=d.ID ");
sb.Append("inner join (select encounter_id,count(distinct issueid) as errorcounts from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Severity='Error' group by encounter_id) f on f.errorcounts=1 and f.Encounter_ID=b.ID ");
sb.Append("cross apply (select top 1 encounter_id,severity,issueid,issnip,description from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Encounter_ID=b.id and Severity='Error' order by id desc) e ");
sb.Append("where (d.ValidationStatus='Rejected' or (d.ValidationStatus='Accepted' and a.EncounterStatus<>'Accepted')) ");
sb.Append("and (e.IssueId='0x39395df' or e.IssueId='0x001C3') ");
sb.Append(") ");
switch (Code)
{
case "P305":
sb.Append("and claimtype='Professional' and right(rtrim(submitterid),3)='305'");
break;
case "I305":
sb.Append("and claimtype='Institutional' and right(rtrim(submitterid),3)='305'");
break;
case "P306":
sb.Append("and claimtype='Professional' and right(rtrim(submitterid),3)='306'");
break;
case "I306":
sb.Append("and claimtype='Institutional' and right(rtrim(submitterid),3)='306'");
break;
}
result = context.Database.SqlQuery <SubHist_Header>(sb.ToString()).ToList();
}
return(result);
}
public static List <SubHist_Line> GetSubHistLine(string Code)
{
List <SubHist_Line> result = null;
using (var context = new SubHistContext())
{
context.Database.CommandTimeout = 1800;
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb.Append("b.ClaimNo,");
sb.Append("ServiceLineNumber,");
sb.Append("[Service Date] as ServiceDate,");
sb.Append("ServiceIDQualifier,");
sb.Append("ServiceID1,");
sb.Append("ProcedureModifier1,");
sb.Append("ProcedureModifier2,");
sb.Append("ProcedureModifier3,");
sb.Append("ProcedureModifier4,");
sb.Append("Description,");
sb.Append("ServiceID2,");
sb.Append("ChargeAmount,");
sb.Append("DiagnosisCodePointer1 as DiagnosisPointer1,");
sb.Append("DiagnosisCodePointer2 as DiagnosisPointer2,");
sb.Append("DiagnosisCodePointer3 as DiagnosisPointer3,");
sb.Append("DiagnosisCodePointer4 as DiagnosisPointer4,");
sb.Append("PayedAmount,");
sb.Append("AdjustmentAmount as AdjustedAmount ");
sb.Append("from EncounterSubmissionHistory.dbo.SubmissionHistServiceLine a inner join ");
sb.Append("EncounterSubmissionHistory.dbo.SubmissionHistoryHeader b on a.SubHistID=b.SubHistoryID ");
sb.Append("where b.ClaimNo in (");
sb.Append("select distinct IehpClaimId from edrs.dbo.DHCSDenialMaster a ");
sb.Append("inner join (select encounterreferencenumber,id,encounterstatus,transaction_id,row_number() over (partition by encounterreferencenumber order by id desc) as rn from IEHP_DHCSResponse.dbo.DHCSResponse_Encounter ");
sb.Append(") b on b.rn=1 and a.IEHPClaimId=b.EncounterReferenceNumber ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse_Transaction c on b.Transaction_ID=c.ID ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse d on c.File_ID=d.ID ");
sb.Append("inner join (select encounter_id,count(distinct issueid) as errorcounts from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Severity='Error' group by encounter_id) f on f.errorcounts=1 and f.Encounter_ID=b.ID ");
sb.Append("cross apply (select top 1 encounter_id,severity,issueid,issnip,description from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Encounter_ID=b.id and Severity='Error' order by id desc) e ");
sb.Append("where (d.ValidationStatus='Rejected' or (d.ValidationStatus='Accepted' and a.EncounterStatus<>'Accepted')) ");
sb.Append("and (e.IssueId='0x39395df' or e.IssueId='0x001C3') ");
sb.Append(") ");
switch (Code)
{
case "P305":
sb.Append("and b.claimtype='Professional' and right(rtrim(submitterid),3)='305'");
break;
case "I305":
sb.Append("and b.claimtype='Institutional' and right(rtrim(submitterid),3)='305'");
break;
case "P306":
sb.Append("and b.claimtype='Professional' and right(rtrim(submitterid),3)='306'");
break;
case "I306":
sb.Append("and b.claimtype='Institutional' and right(rtrim(submitterid),3)='306'");
break;
}
result = context.Database.SqlQuery <SubHist_Line>(sb.ToString()).ToList();
}
return(result);
}
public static List <HipaaXML> GetClaimHipaaXML()
{
List <HipaaXML> result = null;
using (var context = new SubHistContext())
{
context.Database.CommandTimeout = 1800;
StringBuilder sb = new StringBuilder();
sb.Append("select distinct ClaimID,ClaimHipaaXML from (");
sb.Append("select c.clm01 as ClaimID,convert(varchar(max),a.transactionDocument) as ClaimHipaaXML from ");
sb.Append("venus_bizprod_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join venus_bizprod_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select clm01,parentid,row_number() over(partition by clm01 order by id desc) rn from venus_bizprod_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("where CLM01 in");
sb.Append("(");
sb.Append("select distinct IehpClaimId from edrs.dbo.DHCSDenialMaster a ");
sb.Append("inner join (select encounterreferencenumber,id,encounterstatus,transaction_id,row_number() over (partition by encounterreferencenumber order by id desc) as rn from IEHP_DHCSResponse.dbo.DHCSResponse_Encounter ");
sb.Append(") b on b.rn=1 and a.IEHPClaimId=b.EncounterReferenceNumber ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse_Transaction c on b.Transaction_ID=c.ID ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse d on c.File_ID=d.ID ");
sb.Append("inner join (select encounter_id,count(distinct issueid) as errorcounts from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Severity='Error' group by encounter_id) f on f.errorcounts=1 and f.Encounter_ID=b.ID ");
sb.Append("cross apply (select top 1 encounter_id,severity,issueid,issnip,description from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Encounter_ID=b.id and Severity='Error' order by id desc) e ");
sb.Append("where (d.ValidationStatus='Rejected' or (d.ValidationStatus='Accepted' and a.EncounterStatus<>'Accepted')) ");
sb.Append("and (e.IssueId='0x39395df' or e.IssueId='0x001C3') ");
sb.Append("and left(iehpclaimid,3)='306'");
sb.Append(") ");
sb.Append("union ");
sb.Append("select c.clm01 as ClaimID,convert(varchar(max),a.transactionDocument) as ClaimHipaaXML from ");
sb.Append("bizsqlb1_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join bizsqlb1_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select clm01,parentid,row_number() over(partition by clm01 order by id desc) rn from bizsqlb1_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("where CLM01 in");
sb.Append("(");
sb.Append("select distinct IehpClaimId from edrs.dbo.DHCSDenialMaster a ");
sb.Append("inner join (select encounterreferencenumber,id,encounterstatus,transaction_id,row_number() over (partition by encounterreferencenumber order by id desc) as rn from IEHP_DHCSResponse.dbo.DHCSResponse_Encounter ");
sb.Append(") b on b.rn=1 and a.IEHPClaimId=b.EncounterReferenceNumber ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse_Transaction c on b.Transaction_ID=c.ID ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse d on c.File_ID=d.ID ");
sb.Append("inner join (select encounter_id,count(distinct issueid) as errorcounts from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Severity='Error' group by encounter_id) f on f.errorcounts=1 and f.Encounter_ID=b.ID ");
sb.Append("cross apply (select top 1 encounter_id,severity,issueid,issnip,description from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Encounter_ID=b.id and Severity='Error' order by id desc) e ");
sb.Append("where (d.ValidationStatus='Rejected' or (d.ValidationStatus='Accepted' and a.EncounterStatus<>'Accepted')) ");
sb.Append("and (e.IssueId='0x39395df' or e.IssueId='0x001C3') ");
sb.Append("and left(iehpclaimid,3)='306'");
sb.Append(") ");
sb.Append("union ");
sb.Append("select c.clm01 as ClaimID,convert(varchar(max),a.transactionDocument) as ClaimHipaaXML from ");
sb.Append("mars_wpc_valid_837p.dbo.XMLDocument a ");
sb.Append("inner join mars_wpc_valid_837p.dbo.loops b on a.transactionid=b.transactionid and b.loopName='2300' ");
sb.Append("inner join (select clm01,parentid,row_number() over(partition by clm01 order by id desc) rn from mars_wpc_valid_837p.dbo.clm) c on c.rn=1 and c.parentid=b.id ");
sb.Append("where CLM01 in");
sb.Append("(");
sb.Append("select distinct IehpClaimId from edrs.dbo.DHCSDenialMaster a ");
sb.Append("inner join (select encounterreferencenumber,id,encounterstatus,transaction_id,row_number() over (partition by encounterreferencenumber order by id desc) as rn from IEHP_DHCSResponse.dbo.DHCSResponse_Encounter ");
sb.Append(") b on b.rn=1 and a.IEHPClaimId=b.EncounterReferenceNumber ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse_Transaction c on b.Transaction_ID=c.ID ");
sb.Append("inner join IEHP_DHCSResponse.dbo.DHCSResponse d on c.File_ID=d.ID ");
sb.Append("inner join (select encounter_id,count(distinct issueid) as errorcounts from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Severity='Error' group by encounter_id) f on f.errorcounts=1 and f.Encounter_ID=b.ID ");
sb.Append("cross apply (select top 1 encounter_id,severity,issueid,issnip,description from IEHP_DHCSResponse.dbo.DHCSResponse_EncounterResponse where Encounter_ID=b.id and Severity='Error' order by id desc) e ");
sb.Append("where (d.ValidationStatus='Rejected' or (d.ValidationStatus='Accepted' and a.EncounterStatus<>'Accepted')) ");
sb.Append("and (e.IssueId='0x39395df' or e.IssueId='0x001C3') ");
sb.Append("and left(iehpclaimid,3)='306'");
sb.Append(") ");
sb.Append(") t");
result = context.Database.SqlQuery <HipaaXML>(sb.ToString()).ToList();
}
return(result);
}