strSQL2 = "SELECT distinct CH.SEQ_CLAIM_ID, CD.LINE_NUMBER, mm.SUBSCRIBER_ID, " & _
"ch.line_of_business, CH.MEMBER_AGE, CH.MEMBER_GENDER, CH.PLAN_CODE, CH.SEQ_PROV_ID, " & _
"pm.SHORT_NAME, ps.PRIMARY_SPECIALTY, ps.SPECIALTY_TYPE, CH.Primary_Svc_Date, CH.CLAIM_THRU_DATE, CD.BILLED_AMT, " & _
"CD.ALLOWED_AMT, rc.REASON_CODE, CD.NOT_COVERED_REASON, CD.NOT_COVERED_AMT, ch.EVAL_AUDIT_STATUS, " & _
"CD.NET_AMT, CD.DETAIL_SVC_DATE, CH.PLACE_OF_SERVICE_1, CH.DIAGNOSIS_1, cl.PROVC_CPD_PCT_OF_BILLED, cl.PROVC_CPD_PCT_WITHHOLD, " & _
"CH.DIAGNOSIS_2, cd.max_units, CD.PROCEDURE_MODIFIER, CD.QUANTITY , CD.COPAYMENT_1_AMT, cl.PRULE_PRICE_RULE, cl.PRULE_DESCRIPTION, " & _
"CD.DEDUCTIBLE_AMT, CD.PROCEDURE_CODE, CD.MED_DEF_CODE, md.Description, md.SECURITY_CODE, CH.AUTH_NUMBER, cd.ADJUDICATION_METHOD, " & _
"cl.MEDEF_CODE, cl.MEDEF_SHORT_DESC, cl.MEDEF_ORDER, cl.MEDEF_SEARCH_SEQ, cl.PROVIDER_NPI, cl.BENEF_PROC_ORDER_ID, cl.BENEF_APPLY_PAT_LIAB_TO, cl.BENEF_COPAY_RESTRIC_PCT, cl.BENEF_PATLIAB_COMP, " & _
"cl.PROVC_CPD_CONTRACT_TYPE, cl.PROVC_CPD_PRICE_RULE_1, cl.PROVC_CPD_PRICE_RULE_2, cl.PROVC_CPD_PRICE_REGION, cl.PROVC_CPD_SERVICE_REGION, cl.PROVC_CPD_PCT_ALLOWED, cl.PROVC_CPD_PCT_OF_BILLED, cl.PROVC_CPD_PCT_WITHHOLD, cl.PROVC_CPD_PRICE_SCH, " & _
"cl.PROVC_CPD_PRICE_SCH, le.procedure_code, le.adjusted_Charges, le.adjusted_units, le.ADJUSTED_MODIFIER1, am.Auth_Type, am.SEQ_PROV_ID AM_PROV_ID, am.ADMIT_PRIMARY_DATE, am.DIAGNOSIS_1 am_DIAGNOSIS_1, am.DIAGNOSIS_2 am_DIAGNOSIS_2, am.SURG_PROCEDURE_1, am.SURG_PROCEDURE_2, am.PLACE_OF_SERVICE, am.OVERALL_STATUS, am.HOLD_REASON, am.HOLD_Date, am.DENIED_DATE, am.AUTHORIZED_DAYS_VIS, am.DENIED_DAYS_VIS " & _
"FROM DORIS.PROFSVC_CLAIM_HEADER ch, DORIS.PROFSVC_CLAIM_DETAIL cd, doris.claim_deny_reason dr, DORIS.REASON_CODE_MASTER rc, DORIS.Member_Master mm " & _
", DORIS.PROV_SPECIALTY ps, DORIS.MED_DEFN_CODE md, DORIS.PROV_MASTER pm, DORIS.clpra_master cl, DORIS.ICES_LINE_EDIT le, Doris.Auth_Master am " & _
"Where (CH.seq_claim_id = cd.seq_claim_id) " & _
"and (ch.SEQ_MEMB_ID = mm.SEQ_MEMB_ID) and (ch.Auth_Number = am.Auth_Number (+)) " & _
"and (cd.seq_claim_id = dr.seq_claim_id (+)) and (cd.line_number = dr.line_number (+)) " & _
"and cd.seq_claim_id = cl.seq_claim_id (+) and cd.line_number = cl.line_number (+) " & _
"and (cd.seq_claim_id = le.seq_claim_id (+)) " & _
"and (cd.line_number = le.line_number (+)) " & _
"and (dr.REASON_CODE = rc.REASON_CODE (+)) " & _
"and pm.SEQ_PROV_ID = ps.SEQ_PROV_ID " & _
"and ch.SEQ_PROV_ID = pm.SEQ_PROV_ID " & _
"and (cd.MED_DEF_CODE = md.MED_DEF_CODE (+)) " & _
"and CH.SEQ_CLAIM_ID='" & claimnbr & "'" & _
"and ps.PRIMARY_SPECIALTY= 'Y' " & _
"order by cd.line_number "
claimtype1 = "PROF"
Else
MsgBox "Claim type must be PROF, P, INST or I. Process stopped without execution."
GoTo Subexit
End If
' MsgBox claimtype1
Set conn = CreateObject("ADODB.connection")
If Environment = "HSDREPT" Then
dbName = "hsdrept"
ElseIf Environment = "BWAUAT3" Then
dbName = "bwauat3"
ElseIf Environment = "DIAMUAT2" Then
dbName = "DIAMUAT2"
ElseIf Environment = "DIAMUAT3" Then
dbName = "DIAMUAT3"
ElseIf Environment = "DIAMUPA1" Then
dbName = "DIAMUPA1"
ElseIf Environment = "DIAMUFTS" Then
dbName = "DIAMUFTS"
ElseIf Environment = "DIAMUSTS" Then
dbName = "DIAMUSTS"
ElseIf Environment = "DIAMUPA2" Then
dbName = "DIAMUPA2"
ElseIf Environment = "DIAMDLTS" Then
dbName = "DIAMDLTS"
ElseIf Environment = "DIAMUPA2" Then
dbName = "DIAMUPA2"
Else
MsgBox "Environment not setup. Contact system administrator."
GoTo Subexit
End If
dbUser = "boaks"
dbPass = "well85care"
conn.Open "Provider=OraOLEDB.Oracle;Data Source=" & dbName & ";User Id=" & dbUser & ";Password=" & dbPass & ";"
Set rs = conn.Execute(strSQL2)
MsgBox "Processing Claim: " & rs.Fields("SEQ_CLAIM_ID")
Do While Not rs.EOF
If DCount("[Test Detail ID]", "[00200 - TestDetailTbl]", "[Test Detail ID] = " & currdetailid & "") > 0 Then
'no issue. Records found.
Else
MsgBox "Test Detail Record could not be found. Process stopped"
GoTo Subexit:
End If
'***PREP for SQL CALLS *************************************
SEQ_CLAIM_ID1 = rs.Fields("SEQ_CLAIM_ID")
If IsNull(SEQ_CLAIM_ID1) Then
MsgBox "Claim number can not be blank"
GoTo Subexit
End If
lineid1 = Int(rs.Fields("Line_Number"))
If IsNull(lineid1) Then
MsgBox "Line number can not be blank. Enter line number then press Get Claim"
GoTo Subexit
End If
subscriberID1 = Int(rs.Fields("Subscriber_ID"))
lob1 = rs.Fields("line_of_business")
MemberAge1 = rs.Fields("Member_Age")
Gender1 = rs.Fields("Member_Gender")
PlanCode1 = rs.Fields("Plan_Code")
ProviderID1 = rs.Fields("SEQ_PROV_ID")
ProviderName1 = rs.Fields("Short_name")
ProviderSpecialty1 = rs.Fields("SPECIALTY_TYPE")
EvalAuditStatus1 = rs.Fields("EVAL_AUDIT_STATUS")
'MsgBox "Here"
Dim admitdate1 As Date
Dim ADMITPRIMARYDATE1 As Date
Dim DETAILSVCDATE1 As Date
Dim HoldDate1 As Date
Dim DENIEDDATE1 As Date
If ClaimType = "PROF" Or ClaimType = "P" Then
admitdate1 = rs.Fields("Primary_Svc_date")
LOS1 = rs.Fields("claim_THRU_DATE") - rs.Fields("DETAIL_SVC_DATE")
DETAILSVCDATE1 = rs.Fields("DETAIL_SVC_DATE")
THRUDOS1 = rs.Fields("claim_THRU_DATE")
FromDOS1 = rs.Fields("DETAIL_SVC_DATE")
Else
admitdate1 = rs.Fields("Primary_Svc_date")
LOS1 = rs.Fields("Detail_THRU_DATE") - rs.Fields("DETAIL_SVC_DATE")
DETAILSVCDATE1 = rs.Fields("DETAIL_SVC_DATE")
THRUDOS1 = rs.Fields("Detail_THRU_DATE")
FromDOS1 = rs.Fields("DETAIL_SVC_DATE")
End If
Billedamt1 = rs.Fields("BILLED_AMT")
Allowedamt1 = rs.Fields("ALLOWED_AMT")
REASONCODE1 = rs.Fields("REASON_CODE")
NOTCOVEREDREASON1 = rs.Fields("NOT_COVERED_REASON")
NOTCOVEREDAMT1 = rs.Fields("NOT_COVERED_AMT")
NetAmt1 = rs.Fields("NET_AMT")
POS1 = rs.Fields("PLACE_OF_SERVICE_1")
DIAGNOSIS1 = rs.Fields("DIAGNOSIS_1")
DIAGNOSIS2 = rs.Fields("DIAGNOSIS_2")
If ClaimType = "PROF" Or ClaimType = "P" Then
DRG1 = Null
Else
DRG1 = rs.Fields("DRG_CODE")
End If
Modifier1 = rs.Fields("PROCEDURE_MODIFIER")
QUANTITY1 = rs.Fields("QUANTITY")
COPAYMENT1 = rs.Fields("COPAYMENT_1_AMT")
Deductible1 = rs.Fields("DEDUCTIBLE_AMT")
ProcedureCode1 = rs.Fields("PROCEDURE_CODE")
MEDDEFCODE1 = rs.Fields("MED_DEF_CODE")
Description1 = rs.Fields("Description")
'SearchSquence1 = rs.Fields("SECURITY_CODE")
AUTHNUMBER1 = rs.Fields("AUTH_NUMBER")
If IsNull(AUTHNUMBER1) Then
AUTHNUMBER1 = 0
End If
AdjudicationMethod1 = rs.Fields("Adjudication_Method")
MMEDEFCODE1 = rs.Fields("MEDEF_CODE")
MEDEFSHORTDESC1 = rs.Fields("MEDEF_SHORT_DESC")
MEDEFOrder1 = rs.Fields("MEDEF_order")
MEDEFSEARCHSEQ1 = rs.Fields("MEDEF_SEARCH_SEQ")
ProviderNPI1 = rs.Fields("PROVIDER_NPI")
BENEFProcOrderId1 = rs.Fields("BENEF_PROC_ORDER_ID")
BENEFAPPLYPATLIABTO1 = rs.Fields("BENEF_APPLY_PAT_LIAB_TO")
BENEFCOPAYRESTRICPCT1 = rs.Fields("BENEF_COPAY_RESTRIC_PCT")
BENEFPATLIABCOMP1 = rs.Fields("BENEF_PATLIAB_COMP")
PROVCCPDCONTRACTTYPE1 = rs.Fields("PROVC_CPD_CONTRACT_TYPE")
PROVCCPDPRICERULE1 = rs.Fields("PROVC_CPD_PRICE_RULE_1")
PROVCCPDPRICERULE2 = rs.Fields("PROVC_CPD_PRICE_RULE_2")
PROVCCPDPRICEREGION1 = rs.Fields("PROVC_CPD_PRICE_REGION")
PROVCCPDSERVICEREGION1 = rs.Fields("PROVC_CPD_SERVICE_REGION")
PROVCCPDPCTALLOWED1 = rs.Fields("PROVC_CPD_PCT_ALLOWED")
If IsNull(PROVCCPDPCTALLOWED1) Or Nz(PROVCCPDPCTALLOWED1) Then
PROVCCPDPCTALLOWED1 = 0
End If
PRULEPRICERULE1 = rs.Fields("PRULE_PRICE_RULE")
PRULEDESCRIPTION1 = rs.Fields("PRULE_DESCRIPTION")
PROVCCPDPCTOFBILLED1 = rs.Fields("PROVC_CPD_PCT_OF_BILLED")
If IsNull(PROVCCPDPCTOFBILLED1) Or Nz(PROVCCPDPCTOFBILLED1) Then
PROVCCPDPCTOFBILLED1 = 0
End If
PROVCCPDPCTWITHHOLD1 = rs.Fields("PROVC_CPD_PCT_WITHHOLD")
If IsNull(PROVCCPDPCTWITHHOLD1) Or Nz(PROVCCPDPCTWITHHOLD1) Then
PROVCCPDPCTWITHHOLD1 = 0
End If
PROVCCPDPRICESCH1 = rs.Fields("PROVC_CPD_PRICE_SCH")
ProcedureCode2 = rs.Fields("procedure_code")
AdjustedCharges1 = rs.Fields("adjusted_Charges")
If IsNull(AdjustedCharges1) Then
AdjustedCharges1 = 0
End If
AdjustedUnits1 = rs.Fields("adjusted_units")
If IsNull(AdjustedUnits1) Then
AdjustedUnits1 = 0
End If
ADJUSTEDMODIFIER11 = rs.Fields("ADJUSTED_MODIFIER1")
'Auth fields
AuthType1 = rs.Fields("Auth_Type")
AMPROVID1 = rs.Fields("AM_PROV_ID")
ADMITPRIMARYDATE1 = rs.Fields("ADMIT_PRIMARY_DATE")
amDIAGNOSIS1 = rs.Fields("am_DIAGNOSIS_1")
amDIAGNOSIS2 = rs.Fields("am_DIAGNOSIS_2")
SURGPROCEDURE1 = rs.Fields("SURG_PROCEDURE_1")
SURGPROCEDURE2 = rs.Fields("SURG_PROCEDURE_2")
PLACEOFSERVICE1 = rs.Fields("PLACE_OF_SERVICE")
OVERALLSTATUS1 = rs.Fields("OVERALL_STATUS")
HOLDREASON1 = rs.Fields("HOLD_REASON")
HoldDate1 = rs.Fields("HOLD_Date")
'If (Not IsDate(rs.Fields("HOLD_Date"))) Then
' HoldDate1 = Null
'Else
' HoldDate1 = "'" & rs.Fields("HOLD_Date") & "'"
'End If
'MsgBox "here"
DENIEDDATE1 = rs.Fields("DENIED_DATE")
'If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
' DENIEDDATE1 = Null
'Else
' DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
'End If
'DENIEDDATE1 = rs.Fields("DENIED_DATE")
AUTHORIZEDDAYSVIS1 = rs.Fields("AUTHORIZED_DAYS_VIS")
If IsNull(AUTHORIZEDDAYSVIS1) Then
AUTHORIZEDDAYSVIS1 = 0
End If
DENIEDDAYSVIS1 = rs.Fields("DENIED_DAYS_VIS")
If IsNull(DENIEDDAYSVIS1) Then
DENIEDDAYSVIS1 = 0
End If
Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection
'It's possible that someone added or deleted records at the Detail level. It could happen within the Access Database or within Xcelys.
'Therefore, need to see what is currently in the database. If it exists update it, otherwise Insert the new record.
'Also, claims are constantly re-used within one environment. It's important to look at the test detail id, test claim id and environment when updating.
strSQL = "SELECT [00100 - TestHeadertbl].[Test Header ID], [00200 - TestDetailTbl].[Test Detail ID], [00400 - LineDetailTbl].[Line ID], " & _
"[00100 - TestHeadertbl].Environment, [00200 - TestDetailTbl].[Test Claim ID] " & _
"FROM ([00100 - TestHeadertbl] INNER JOIN [00150 - TestScenarioTbl] ON [00100 - TestHeadertbl].[Test Header ID] = [00150 - TestScenarioTbl].[Test Header ID]) INNER JOIN ([00200 - TestDetailTbl] LEFT JOIN [00400 - LineDetailTbl] ON [00200 - TestDetailTbl].[Test Detail ID] = [00400 - LineDetailTbl].[Test Detail ID]) ON [00150 - TestScenarioTbl].[Scenario ID] = [00200 - TestDetailTbl].[Scenario ID] " & _
"WHERE [00200 - TestDetailTbl].[Test Detail ID]= " & currdetailid & " AND [00200 - TestDetailTbl].[Test Claim ID]= '" & SEQ_CLAIM_ID1 & "' and [00100 - TestHeadertbl].[environment]= '" & Environment & "' " & _
"Order by [00400 - LineDetailTbl].[Line ID];"
With rst
.Open strSQL, cnn, adOpenKeyset, adLockBatchOptimistic
TotalRecords = rst.RecordCount
FoundClaim = False
FoundLine = False
Do While Not rst.EOF 'These are the records currently in the database
Set rst2 = New ADODB.Recordset
Set cnn2 = CurrentProject.Connection
Set rst3 = New ADODB.Recordset
Set cnn3 = CurrentProject.Connection
testClaimID2 = ![Test Claim ID]
lineid2 = ![Line ID]
testdetailid2 = ![Test Detail ID]
environment2 = ![Environment]
' SEQ_CLAIM_ID2 = "'" & SEQ_CLAIM_ID1 & "'"
If environment2 = Environment And testdetailid2 = currdetailid Then 'don't do anything if the environment or the test detail id's are different is different
If testClaimID2 = CStr(SEQ_CLAIM_ID1) Then 'yes records are already in the database. Update current records
strSQL2 = "UPDATE [00200 - TestDetailTbl] SET " & _
"[00200 - TestDetailTbl].[Test Claim ID] = " & [SEQ_CLAIM_ID1] & ", [00200 - TestDetailTbl].[lob] = '" & [lob1] & "', [00200 - TestDetailTbl].[Member Age] = " & [MemberAge1] & ", [00200 - TestDetailTbl].[Gender] = '" & [Gender1] & "' " & _
", [00200 - TestDetailTbl].[Plan Code] = '" & [PlanCode1] & "', [00200 - TestDetailTbl].[ProviderID] = '" & [ProviderID1] & "' " & _
", [00200 - TestDetailTbl].[Provider Name] = '" & [ProviderName1] & "', [00200 - TestDetailTbl].[Provider Specialty] = '" & [ProviderSpecialty1] & "', [00200 - TestDetailTbl].[Eval_Audit_Status] = '" & [EvalAuditStatus1] & "' " & _
", [00200 - TestDetailTbl].[Admit Date] = '" & [admitdate1] & "', [00200 - TestDetailTbl].[Claim Type] = '" & [claimtype1] & "', [00200 - TestDetailTbl].[Auth ID] = " & [AUTHNUMBER1] & " " & _
", [00200 - TestDetailTbl].[Provider NPI] = '" & [ProviderNPI1] & "', [00200 - TestDetailTbl].[SubscriberID] = " & [subscriberID1] & ", [00200 - TestDetailTbl].[Length of Stay] = " & [LOS1] & " " & _
", [00200 - TestDetailTbl].[Auth Type] = '" & [AuthType1] & "', [00200 - TestDetailTbl].[Auth Provider ID] = '" & [AMPROVID1] & "' " & _
", [00200 - TestDetailTbl].[Auth Admit Date] = '" & [ADMITPRIMARYDATE1] & "', [00200 - TestDetailTbl].[Auth Diagnosis Code 1] = '" & [amDIAGNOSIS1] & "' " & _
", [00200 - TestDetailTbl].[Auth Diagnosis Code 2] = '" & [amDIAGNOSIS2] & "', [00200 - TestDetailTbl].[Auth Procedure Code 1] = '" & [SURGPROCEDURE1] & "' " & _
", [00200 - TestDetailTbl].[Auth Procedure Code 2] = '" & [SURGPROCEDURE2] & "', [00200 - TestDetailTbl].[Auth POS] = '" & [PLACEOFSERVICE1] & "' " & _
", [00200 - TestDetailTbl].[Auth Status] = '" & [OVERALLSTATUS1] & "', [00200 - TestDetailTbl].[Auth Hold Reason] = '" & [HOLDREASON1] & "' " & _
", [00200 - TestDetailTbl].[Authorized Days] = " & [AUTHORIZEDDAYSVIS1] & ", [00200 - TestDetailTbl].[Auth Denied Days] = " & [DENIEDDAYSVIS1] & " " & _
", [00200 - TestDetailTbl].[Auth Hold Date] = '" & [HoldDate1] & "', [00200 - TestDetailTbl].[Auth Denied Date] = '" & [DENIEDDATE1] & "' " & _
" WHERE [00200 - TestDetailTbl].[Test Detail ID] = " & [currdetailid] & " ;"
With rst2
.Open strSQL2, cnn2, adOpenKeyset, adLockBatchOptimistic
End With
FoundClaim = True