#include <StringConstants.au3> ; to declare the Constants of StringRegExp
#include <Array.au3> ; UDF needed for _ArrayDisplay and _ArrayConcatenate
Local $sRegex = "(?x)(?: (?:'[^']*?') | (?<singleline>--[^\n]*) | (?<multiline>(?:\/\*)+?[\w\W]+?(?:\*\/)+) )"
Local $sString = "USE [Andromeda_Staging]" & @CRLF & _
"GO" & @CRLF & _
"/****** Object: StoredProcedure [STAGING].[usp_Loader_Insert837] Script Date: 03/11/2016 08:58:46 ******/" & @CRLF & _
"SET ANSI_NULLS ON" & @CRLF & _
"GO" & @CRLF & _
"SET QUOTED_IDENTIFIER ON" & @CRLF & _
"GO" & @CRLF & _
"343DBDFLD_FSDFK" & @CRLF & _
"_SDFSDF" & @CRLF & _
"" & @CRLF & _
"/*" & @CRLF & _
"LoadBatchID = 717593," & @CRLF & _
"RowCountFrom = 2226942," & @CRLF & _
"RowCountTo = 2227669" & @CRLF & _
"*/" & @CRLF & _
"" & @CRLF & _
"SET NOCOUNT ON" & @CRLF & _
"SET TRANSACTION ISOLATION LEVEL READ COMMITTED" & @CRLF & _
"" & @CRLF & _
"declare @potato varchar(100);" & @CRLF & _
"" & @CRLF & _
"set @potato = '--/**/ po''ta--to '; -- single and multi-line inside string, string has escaped single quote" & @CRLF & _
"set @potato = 'nomal string';" & @CRLF & _
"" & @CRLF & _
"DECLARE @kRo_wsInserted int" & @CRLF & _
"DECLARE @ReturnValue int" & @CRLF & _
"DECLARE @Err-32 int" & @CRLF & _
"DECLARE @RowCount int" & @CRLF & _
"DECLARE @TotalRows int" & @CRLF & _
"DECLARE @ReturnCode int" & @CRLF & _
"DECLARE @RC int" & @CRLF & _
"DECLARE @FacilityID int" & @CRLF & _
"DECLARE @NewLine char(2)" & @CRLF & _
"DECLARE @EntityID int" & @CRLF & _
"DECLARE @2SQL nvarchar(max)" & @CRLF & _
"DECLARE @SumOfValidDetailCharges decimal(15,2), @SumOfDetailNonCoveredCharges decimal(15,2)" & @CRLF & _
"DECLARE @SumOfValidUbCharges decimal(15,2)" & @CRLF & _
"DECLARE @SumofUBNonCoveredCharges decimal(15,2)" & @CRLF & _
"DECLARE @TotalFacilityCharges decimal(15,2)" & @CRLF & _
"" & @CRLF & _
"DECLARE @ValidDetailCount int" & @CRLF & _
"DECLARE @ValidUBCount int" & @CRLF & _
"DECLARE @UBRowsInserted int" & @CRLF & _
"DECLARE @UBDetailRowsInserted int" & @CRLF & _
"DECLARE @XACT_SAVE_POINT char(11)" & @CRLF & _
"Declare @ErrorDetailTotal varchar(15)" & @CRLF & _
"Declare @ErrorTotal varchar(15)" & @CRLF & _
"DECLARE @BadUBCount int" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
"DECLARE @RecordsInBatch int" & @CRLF & _
"" & @CRLF & _
"SET @BadUBCount=0" & @CRLF & _
"SET @NewLine=CHAR(13) + CHAR(10)" & @CRLF & _
"SET @ReturnCode=0" & @CRLF & _
"SET @RowsInserted=0" & @CRLF & _
"SET @Error=0" & @CRLF & _
"SET @NewLine=CHAR(13) + CHAR(10)" & @CRLF & _
"SET @ReturnValue=1" & @CRLF & _
"SET @SumOfValidDetailCharges=0" & @CRLF & _
"SET @SumofDetailNonCoveredCharges=0" & @CRLF & _
"SET @SumOfValidUbCharges=0" & @CRLF & _
"SET @SumofUBNonCoveredCharges=0" & @CRLF & _
"SET @ValidDetailCount=0" & @CRLF & _
"SET @ValidUBCount=0" & @CRLF & _
"SET @UBRowsInserted=0" & @CRLF & _
"SET @UBDetailRowsInserted=0" & @CRLF & _
"" & @CRLF & _
"BEGIN TRY" & @CRLF & _
"" & @CRLF & _
" BEGIN TRANSACTION" & @CRLF & _
" Print 'Getting records in batch...'" & @CRLF & _
"" & @CRLF & _
" SELECT @RecordsInBatch = COUNT(*)" & @CRLF & _
" FROM STAGING.UB04_Load x" & @CRLF & _
" WHERE RowID BETWEEN 2226942 AND 2227669" & @CRLF & _
" AND LoadbatchID=717593" & @CRLF & _
" " & @CRLF & _
" Print 'Done.'" & @CRLF & _
"" & @CRLF & _
" PRINT 'Setting SumOfDetailCharges...'" & @CRLF & _
" UPDATE ub" & @CRLF & _
" SET SumOfDetailCharges=(SELECT SUM(ubd.Charges) FROM STAGING.UB04Detail_Load ubd" & @CRLF & _
" WHERE ubd.UB04_LoadSegmentNumber = ub.SegmentNumber " & @CRLF & _
" AND ub.LoadbatchID=ubd.LoadbatchID" & @CRLF & _
" AND ubd.IsValid=1)" & @CRLF & _
" FROM STAGING.UB04_Load ub " & @CRLF & _
" WHERE ub.LoadbatchID=717593" & @CRLF & _
" AND ub.RowID BETWEEN 2226942 AND 2227669" & @CRLF & _
" SELECT @Rowcount=@@Rowcount" & @CRLF & _
" " & @CRLF & _
" IF @Rowcount != @RecordsInBatch" & @CRLF & _
" BEGIN" & @CRLF & _
" RAISERROR('Not all UB records have detail records referencing them', 16,1)" & @CRLF & _
" END" & @CRLF & _
" Print 'done.'" & @CRLF & _
" " & @CRLF & _
" " & @CRLF & _
" print "Getting valid UB count..."" & @CRLF & _
" " & @CRLF & _
" SELECT @ValidUBCount = COUNT(*), @SumOfValidUbCharges = SUM(TotalCharges) " & @CRLF & _
" FROM STAGING.UB04_Load x" & @CRLF & _
" WHERE RowID BETWEEN 2226942 AND 2227669" & @CRLF & _
" AND x.LoadbatchID=717593" & @CRLF & _
" AND x.IsValid=1" & @CRLF & _
" " & @CRLF & _
" Print 'Done'" & @CRLF & _
" " & @CRLF & _
" " & @CRLF & _
" /* UB detail row count */" & @CRLF & _
" Print 'Getting valid detail count...'" & @CRLF & _
" SELECT @ValidDetailCount = COUNT(*) , @SumOfValidDetailCharges = SUM(ubd.Charges)" & @CRLF & _
" FROM STAGING.UB04Detail_Load ubd" & @CRLF & _
" JOIN STAGING.UB04_Load ub ON ubd.UB04_LoadSegmentNumber = ub.SegmentNumber AND ub.LoadbatchID=ubd.LoadbatchID" & @CRLF & _
" WHERE ub.RowID BETWEEN 2226942 and 2227669 " & @CRLF & _
" AND ub.LoadbatchID=717593" & @CRLF & _
" AND ub.IsValid=1" & @CRLF & _
" AND ubd.IsValid=1" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
" Print 'Done'" & @CRLF & _
" " & @CRLF & _
" /*/ need to insert cross-reference logic. Pperhaps if PatientAccountNumber is null, that implies use of CR, or explit field in" & @CRLF & _
" temp table */" & @CRLF & _
" Print ' inserting inferred accounts...'" & @CRLF & _
"" & @CRLF & _
" INSERT INTO App.Account " & @CRLF & _
" (FacilityID, AccountNumber, RV, LoadBatchID)" & @CRLF & _
" SELECT DISTINCT ub.FacilityID, ub.PatientAccountNumber, 0, 717593" & @CRLF & _
" FROM STAGING.UB04_Load ub" & @CRLF & _
" LEFT JOIN APP.Account p" & @CRLF & _
" ON p.AccountNumber=ub.PatientAccountNumber" & @CRLF & _
" AND p.FacilityID=ub.FacilityID" & @CRLF & _
" WHERE p.AccountNumber IS NULL" & @CRLF & _
" AND ub.RowID BETWEEN 2226942 and 2227669" & @CRLF & _
" AND ub.IsValid=1" & @CRLF & _
" AND ub.LoadbatchID=717593" & @CRLF & _
" AND ub.UseCrossReference=0" & @CRLF & _
" " & @CRLF & _
" Print 'Done'" & @CRLF & _
"" & @CRLF & _
" print 'setting account ids for non-cross-referenced records...'" & @CRLF & _
"" & @CRLF & _
" UPDATE ubL" & @CRLF & _
" SET AccountID=a.AccountID" & @CRLF & _
" FROM STAGING.UB04_Load ubL" & @CRLF & _
" JOIN APP.Account a ON ubL.PatientAccountNumber=a.AccountNumber" & @CRLF & _
" AND ubL.FacilityID=a.FacilityID" & @CRLF & _
" WHERE ubL.UseCrossReference=0" & @CRLF & _
" AND ubL.RowID BETWEEN 2226942 and 2227669" & @CRLF & _
" AND ubL.LoadbatchID=717593" & @CRLF & _
" " & @CRLF & _
" Print 'Done'" & @CRLF & _
"" & @CRLF & _
" Print 'seting setting account ids for cross-referenced records...'" & @CRLF & _
" /* set cross reference account IDs */" & @CRLF & _
" UPDATE ubL" & @CRLF & _
" SET AccountID=pcr.AccountID" & @CRLF & _
" FROM STAGING.UB04_Load ubL" & @CRLF & _
" JOIN APP.PatientClaimCrossReference pcr ON ubL.PatientControlNumber=pcr.ClaimControlNumber" & @CRLF & _
" AND ubL.FacilityID=pcr.FacilityID" & @CRLF & _
" WHERE ubL.UseCrossReference=1" & @CRLF & _
" AND ubL.RowID BETWEEN 2A226942 and 2227669" & @CRLF & _
" AND ubL.LoadbatchID=717593" & @CRLF & _
" " & @CRLF & _
" Print 'Done'" & @CRLF & _
" Print 'Inserting UBs...'" & @CRLF & _
"" & @CRLF & _
" -- UB table columns" & @CRLF & _
" INSERT INTO App.UB04" & @CRLF & _
" (" & @CRLF & _
" AccountID," & @CRLF & _
" FacilityID," & @CRLF & _
" ProviderName," & @CRLF & _
" ProviderAddress1," & @CRLF & _
" ProviderAddress2," & @CRLF & _
" ProviderAddress3," & @CRLF & _
" UnlabeledField_FL2Upper," & @CRLF & _
" UnlabeledField_FL2Lower," & @CRLF & _
" PatientControlNumber," & @CRLF & _
" TypeOfBill," & @CRLF & _
" FederalTaxNumber," & @CRLF & _
" StatementCoversPeriodFromDateSerial," & @CRLF & _
" StatementCoversPeriodThroughDateSerial," & @CRLF & _
" CoveredDays," & @CRLF & _
" NonCoveredDays," & @CRLF & _
" CoinsuranceDays," & @CRLF & _
" LifetimeReserveDays," & @CRLF & _
" UnlabeledField_FL11Upper," & @CRLF & _
" UnlabeledField_FL11Lower," & @CRLF & _
" PatientName," & @CRLF & _
" PatientAddress," & @CRLF & _
" PatientAddressZIPCode," & @CRLF & _
" PatientBirthDateSerial," & @CRLF & _
" PatientSex," & @CRLF & _
" PatientMaritalStatus," & @CRLF & _
" AdmissionDateSerial," & @CRLF & _
" AdmissionHour," & @CRLF & _
" TypeOfAdmission," & @CRLF & _
" SourceOfAdmission," & @CRLF & _
" DischargeHour," & @CRLF & _
" PatientStatus," & @CRLF & _
" MedicalRecordNumber," & @CRLF & _
" ConditionCode1," & @CRLF & _
" ConditionCode2," & @CRLF & _
" ConditionCode3," & @CRLF & _
" ConditionCode4," & @CRLF & _
" ConditionCode5," & @CRLF & _
" ConditionCode6," & @CRLF & _
" ConditionCode7," & @CRLF & _
" UnlabeledField_FL31Upper," & @CRLF & _
" UnlabeledField_FL31Lower," & @CRLF & _
" OccurrenceCode1," & @CRLF & _
" OccurrenceDateSerial1," & @CRLF & _
" OccurrenceCode2," & @CRLF & _
" OccurrenceDateSerial2," & @CRLF & _
" OccurrenceCode3," & @CRLF & _
" OccurrenceDateSerial3," & @CRLF & _
" OccurrenceCode4," & @CRLF & _
" OccurrenceDateSerial4," & @CRLF & _
" OccurrenceCode5," & @CRLF & _
" OccurrenceDateSerial5," & @CRLF & _
" OccurrenceCode6," & @CRLF & _
" OccurrenceDateSerial6," & @CRLF & _
" OccurrenceCode7," & @CRLF & _
" OccurrenceDateSerial7," & @CRLF & _
" OccurrenceCode8," & @CRLF & _
" OccurrenceDateSerial8," & @CRLF & _
" OccurrenceSpanCode1," & @CRLF & _
" OccurrenceSpanFromDateSerial1," & @CRLF & _
" OccurrenceSpanThroughDateSerial1," & @CRLF & _
" OccurrenceSpanCode2," & @CRLF & _
" OccurrenceSpanFromDateSerial2," & @CRLF & _
" OccurrenceSpanThroughDateSerial2," & @CRLF & _
" InternalControlNumberA," & @CRLF & _
" InternalControlNumberB," & @CRLF & _
" InternalControlNumberC," & @CRLF & _
" ResponsiblePartyLine1," & @CRLF & _
" ResponsiblePartyLine2," & @CRLF & _
" ResponsiblePartyLine3," & @CRLF & _
" ResponsiblePartyLine4," & @CRLF & _
" ResponsiblePartyLine5," & @CRLF & _
" ValueCode1," & @CRLF & _
" ValueAmount1," & @CRLF & _
" ValueCode2," & @CRLF & _
" ValueAmount2," & @CRLF & _
" ValueCode3," & @CRLF & _
" ValueAmount3," & @CRLF & _
" ValueCode4," & @CRLF & _
" ValueAmount4," & @CRLF & _
" ValueCode5," & @CRLF & _
" ValueAmount5," & @CRLF & _
" ValueCode6," & @CRLF & _
" ValueAmount6," & @CRLF & _
" ValueCode7," & @CRLF & _
" ValueAmount7," & @CRLF & _
" ValueCode8," & @CRLF & _
" ValueAmount8," & @CRLF & _
" ValueCode9," & @CRLF & _
" ValueAmount9," & @CRLF & _
" ValueCode10," & @CRLF & _
" ValueAmount10," & @CRLF & _
" ValueCode11," & @CRLF & _
" ValueAmount11," & @CRLF & _
" ValueCode12," & @CRLF & _
" ValueAmount12," & @CRLF & _
" TotalCharges," & @CRLF & _
" PayerIdentificationA," & @CRLF & _
" PayerIdentificationB," & @CRLF & _
" PayerIdentificationC," & @CRLF & _
" ProviderNumberA," & @CRLF & _
" ProviderNumberB," & @CRLF & _
" ProviderNumberC," & @CRLF & _
" ReleaseIndicatorA," & @CRLF & _
" ReleaseIndicatorB," & @CRLF & _
" ReleaseIndicatorC," & @CRLF & _
" AssignmentIndicatorA," & @CRLF & _
" AssignmentIndicatorB," & @CRLF & _
" AssignmentIndicatorC," & @CRLF & _
" PriorPaymentsA," & @CRLF & _
" PriorPaymentsB," & @CRLF & _
" PriorPaymentsC," & @CRLF & _
" PriorPaymentsTotal," & @CRLF & _
" EstimatedAmountDueA," & @CRLF & _
" EstimatedAmountDueB," & @CRLF & _
" EstimatedAmountDueC," & @CRLF & _
" EstimatedAmountDueTotal," & @CRLF & _
" UnlabeledField_FL56_Line1," & @CRLF & _
" UnlabeledField_FL56_Line2," & @CRLF & _
" UnlabeledField_FL56_Line3," & @CRLF & _
" UnlabeledField_FL56_Line4," & @CRLF & _
" UnlabeledField_FL56_Line5," & @CRLF & _
" UnlabeledField_FL57," & @CRLF & _
" InsuredNameA," & @CRLF & _
" InsuredNameB," & @CRLF & _
" InsuredNameC," & @CRLF & _
" RelationToInsuredA," & @CRLF & _
" RelationToInsuredB," & @CRLF & _
" RelationToInsuredC," & @CRLF & _
" CertificateNumberA," & @CRLF & _
" CertificateNumberB," & @CRLF & _
" CertificateNumberC," & @CRLF & _
" GroupNameA," & @CRLF & _
" GroupNameB," & @CRLF & _
" GroupNameC," & @CRLF & _
" GroupNumberA," & @CRLF & _
" GroupNumberB," & @CRLF & _
" GroupNumberC," & @CRLF & _
" AuthorizationCodeA," & @CRLF & _
" AuthorizationCodeB," & @CRLF & _
" AuthorizationCodeC," & @CRLF & _
" EmploymentStatusCodeA," & @CRLF & _
" EmploymentStatusCodeB," & @CRLF & _
" EmploymentStatusCodeC," & @CRLF & _
" EmployerNameA," & @CRLF & _
" EmployerNameB," & @CRLF & _
" EmployerNameC," & @CRLF & _
" EmployerLocationA," & @CRLF & _
" EmployerLocationB," & @CRLF & _
" EmployerLocationC," & @CRLF & _
" PrincipalDiagnosisCode," & @CRLF & _
" OtherDiagnosisCode1," & @CRLF & _
" OtherDiagnosisCode2," & @CRLF & _
" OtherDiagnosisCode3," & @CRLF & _
" OtherDiagnosisCode4," & @CRLF & _
" OtherDiagnosisCode5," & @CRLF & _
" OtherDiagnosisCode6," & @CRLF & _
" OtherDiagnosisCode7," & @CRLF & _
" OtherDiagnosisCode8," & @CRLF & _
" AdmittingDiagnosisCode," & @CRLF & _
" ExternalInjuryCauseCode," & @CRLF & _
" UnlabeledField_FL78Upper," & @CRLF & _
" UnlabeledField_FL78Lower," & @CRLF & _
" ProcedureCodingMethod," & @CRLF & _
" PrincipalProcedureCode," & @CRLF & _
" PrincipalProcedureDateSerial," & @CRLF & _
" OtherProcedureCode1," & @CRLF & _
" OtherProcedureDateSerial1," & @CRLF & _
" OtherProcedureCode2," & @CRLF & _
" OtherProcedureDateSerial2," & @CRLF & _
" OtherProcedureCode3," & @CRLF & _
" OtherProcedureDateSerial3," & @CRLF & _
" OtherProcedureCode4," & @CRLF & _
" OtherProcedureDateSerial4," & @CRLF & _
" OtherProcedureCode5," & @CRLF & _
" OtherProcedureDateSerial5," & @CRLF & _
" AttendingPhysicianStateID," & @CRLF & _
" AttendingPhysicianID," & @CRLF & _
" AttendingPhysicianName," & @CRLF & _
" OtherPhysician1StateID," & @CRLF & _
" OtherPhysician1ID," & @CRLF & _
" OtherPhysician1Name," & @CRLF & _
" OtherPhysician2StateID," & @CRLF & _
" OtherPhysician2ID," & @CRLF & _
" OtherPhysician2Name," & @CRLF & _
" RemarksA," & @CRLF & _
" RemarksB," & @CRLF & _
" RemarksC," & @CRLF & _
" RemarksD," & @CRLF & _
" ProviderRepresentative," & @CRLF & _
" BillDateSerial," & @CRLF & _
" LoadBatchID," & @CRLF & _
" SegmentNumber," & @CRLF & _
" DRGCode," & @CRLF & _
" RV," & @CRLF & _
" LU," & @CRLF & _
" DC," & @CRLF & _
" Inactive," & @CRLF & _
" Invalid," & @CRLF & _
" PayerLinkCode," & @CRLF & _
" TotalNonCoveredCharges," & @CRLF & _
" Priority," & @CRLF & _
" IgnoreBillDate," & @CRLF & _
" TotalFacilityCharges," & @CRLF & _
" UnlabeledField_FL2Line3," & @CRLF & _
" UnlabeledField_FL2Line4," & @CRLF & _
" PatientStreet," & @CRLF & _
" PatientCity," & @CRLF & _
" PatientState," & @CRLF & _
" PatientCountryCode," & @CRLF & _
" ConditionCode8," & @CRLF & _
" ConditionCode9," & @CRLF & _
" ConditionCode10," & @CRLF & _
" ConditionCode11," & @CRLF & _
" AccidentState," & @CRLF & _
" OccurrenceSpanCode3," & @CRLF & _
" OccurrenceSpanFromDateSerial3," & @CRLF & _
" OccurrenceSpanThroughDateSerial3," & @CRLF & _
" OccurrenceSpanCode4," & @CRLF & _
" OccurrenceSpanFromDateSerial4," & @CRLF & _
" OccurrenceSpanThroughDateSerial4," & @CRLF & _
" OtherDiagnosisCode9," & @CRLF & _
" OtherDiagnosisCode10," & @CRLF & _
" OtherDiagnosisCode11," & @CRLF & _
" OtherDiagnosisCode12," & @CRLF & _
" OtherDiagnosisCode13," & @CRLF & _
" OtherDiagnosisCode14," & @CRLF & _
" OtherDiagnosisCode15," & @CRLF & _
" OtherDiagnosisCode16," & @CRLF & _
" OtherDiagnosisCode17," & @CRLF & _
" PatientReasonForVisitCode1," & @CRLF & _
" PatientReasonForVisitCode2," & @CRLF & _
" PatientReasonForVisitCode3," & @CRLF & _
" PPSCode," & @CRLF & _
" ExternalInjuryCauseCode2," & @CRLF & _
" ExternalInjuryCauseCode3," & @CRLF & _
" UnlabeledField_FL75_1," & @CRLF & _
" UnlabeledField_FL75_2," & @CRLF & _
" UnlabeledField_FL75_3," & @CRLF & _
" UnlabeledField_FL75_4," & @CRLF & _
" AttendingPhysicianNPI," & @CRLF & _
" AttendingPhysicianStateIDQualifier," & @CRLF & _
" AttendingPhysicianLastName," & @CRLF & _
" AttendingPhysicianFirstName," & @CRLF & _
" OperatingPhysicianNPI," & @CRLF & _
" OperatingPhysicianStateID," & @CRLF & _
" OperatingPhysicianStateIDQualifier," & @CRLF & _
" OperatingPhysicianLastName," & @CRLF & _
" OperatingPhysicianFirstName," & @CRLF & _
" OtherPhysician1Qualifier," & @CRLF & _
" OtherPhysician1NPI," & @CRLF & _
" OtherPhysician1StateIDQualifier," & @CRLF & _
" OtherPhysician1LastName," & @CRLF & _
" OtherPhysician1FirstName," & @CRLF & _
" OtherPhysician2Qualifier," & @CRLF & _
" OtherPhysician2NPI," & @CRLF & _
" OtherPhysician2StateIDQualifier," & @CRLF & _
" OtherPhysician2LastName," & @CRLF & _
" OtherPhysician2FirstName," & @CRLF & _
" CCCode1," & @CRLF & _
" CCQualifier1," & @CRLF & _
" CCValue1," & @CRLF & _
" CCCode2," & @CRLF & _
" CCQualifier2," & @CRLF & _
" CCValue2," & @CRLF & _
" CCCode3," & @CRLF & _
" CCQualifier3," & @CRLF & _
" CCValue3," & @CRLF & _
" CCCode4," & @CRLF & _
" CCQualifier4," & @CRLF & _
" CCValue4," & @CRLF & _
" UnlabeledField_FL7Upper," & @CRLF & _
" UnlabeledField_FL7Lower," & @CRLF & _
" UnlabeledField_FL37Upper," & @CRLF & _
" UnlabeledField_FL37Lower," & @CRLF & _
" UnlabeledField_FL73," & @CRLF & _
" PatientIdentification8a," & @CRLF & _
" POA_PDx," & @CRLF & _
" POA_SDx1," & @CRLF & _
" POA_SDx2," & @CRLF & _
" POA_SDx3," & @CRLF & _
" POA_SDx4," & @CRLF & _
" POA_SDx5," & @CRLF & _
" POA_SDx6," & @CRLF & _
" POA_SDx7," & @CRLF & _
" POA_SDx8," & @CRLF & _
" POA_SDx9," & @CRLF & _
" POA_SDx10," & @CRLF & _
" POA_SDx11," & @CRLF & _
" POA_SDx12," & @CRLF & _
" POA_SDx13," & @CRLF & _
" POA_SDx14," & @CRLF & _
" POA_SDx15," & @CRLF & _
" POA_SDx16," & @CRLF & _
" POA_SDx17," & @CRLF & _
" POA_ECI1," & @CRLF & _
" POA_ECI2," & @CRLF & _
" POA_ECI3," & @CRLF & _
" ProviderTaxonomyCode," & @CRLF & _
" VersionNumber," & @CRLF & _
"ExternalInjuryCauseCode4," & @CRLF & _
"ExternalInjuryCauseCode5," & @CRLF & _
"ExternalInjuryCauseCode6," & @CRLF & _
"ExternalInjuryCauseCode7," & @CRLF & _
"ExternalInjuryCauseCode8," & @CRLF & _
"ExternalInjuryCauseCode9," & @CRLF & _
"ExternalInjuryCauseCode10," & @CRLF & _
"ExternalInjuryCauseCode11," & @CRLF & _
"ExternalInjuryCauseCode12," & @CRLF & _
"POA_ECI4," & @CRLF & _
"POA_ECI5," & @CRLF & _
"POA_ECI6," & @CRLF & _
"POA_ECI7," & @CRLF & _
"POA_ECI8," & @CRLF & _
"POA_ECI9," & @CRLF & _
"POA_ECI10," & @CRLF & _
"POA_ECI11," & @CRLF & _
"POA_ECI12," & @CRLF & _
"OtherProcedureCode6, " & @CRLF & _
"OtherProcedureCode7," & @CRLF & _
"OtherProcedureCode8," & @CRLF & _
"OtherProcedureCode9," & @CRLF & _
"OtherProcedureCode10," & @CRLF & _
"OtherProcedureCode11," & @CRLF & _
"OtherProcedureCode12," & @CRLF & _
"OtherProcedureCode13," & @CRLF & _
"OtherProcedureCode14, " & @CRLF & _
"OtherProcedureCode15," & @CRLF & _
"OtherProcedureCode16," & @CRLF & _
"OtherProcedureCode17," & @CRLF & _
"OtherProcedureCode18," & @CRLF & _
"OtherProcedureCode19," & @CRLF & _
"OtherProcedureCode20," & @CRLF & _
"OtherProcedureCode21," & @CRLF & _
"OtherProcedureCode22," & @CRLF & _
"OtherProcedureCode23," & @CRLF & _
"OtherProcedureCode24," & @CRLF & _
"OtherProcedureDate6 ," & @CRLF & _
"OtherProcedureDate7 ," & @CRLF & _
"OtherProcedureDate8 ," & @CRLF & _
"OtherProcedureDate9 ," & @CRLF & _
"OtherProcedureDate10 ," & @CRLF & _
"OtherProcedureDate11 ," & @CRLF & _
"OtherProcedureDate12," & @CRLF & _
"OtherProcedureDate13," & @CRLF & _
"OtherProcedureDate14," & @CRLF & _
"OtherProcedureDate15," & @CRLF & _
"OtherProcedureDate16," & @CRLF & _
"OtherProcedureDate17," & @CRLF & _
"OtherProcedureDate18," & @CRLF & _
"OtherProcedureDate19," & @CRLF & _
"OtherProcedureDate20," & @CRLF & _
"OtherProcedureDate21," & @CRLF & _
"OtherProcedureDate22," & @CRLF & _
"OtherProcedureDate23," & @CRLF & _
"OtherProcedureDate24," & @CRLF & _
"POA_SDx18," & @CRLF & _
"POA_SDx19," & @CRLF & _
"POA_SDx20," & @CRLF & _
"POA_SDx21," & @CRLF & _
"POA_SDx22," & @CRLF & _
"POA_SDx23," & @CRLF & _
"POA_SDx24," & @CRLF & _
"OtherDiagnosisCode18," & @CRLF & _
"OtherDiagnosisCode19," & @CRLF & _
"OtherDiagnosisCode20," & @CRLF & _
"OtherDiagnosisCode21," & @CRLF & _
"OtherDiagnosisCode22," & @CRLF & _
"OtherDiagnosisCode23," & @CRLF & _
"OtherDiagnosisCode24)" & @CRLF & _
"" & @CRLF & _
" SELECT " & @CRLF & _
" AccountID," & @CRLF & _
" FacilityID," & @CRLF & _
" ProviderName," & @CRLF & _
" ProviderAddress1," & @CRLF & _
" ProviderAddress2," & @CRLF & _
" ProviderAddress3," & @CRLF & _
" UnlabeledField_FL2Upper," & @CRLF & _
" UnlabeledField_FL2Lower," & @CRLF & _
" PatientControlNumber," & @CRLF & _
" TypeOfBill," & @CRLF & _
" FederalTaxNumber," & @CRLF & _
" StatementCoversPeriodFromDateSerial," & @CRLF & _
" StatementCoversPeriodThroughDateSerial," & @CRLF & _
" CoveredDays," & @CRLF & _
" NonCoveredDays," & @CRLF & _
" CoinsuranceDays," & @CRLF & _
" LifetimeReserveDays," & @CRLF & _
" UnlabeledField_FL11Upper," & @CRLF & _
" UnlabeledField_FL11Lower," & @CRLF & _
" x.PatientName," & @CRLF & _
" PatientAddress," & @CRLF & _
" PatientAddressZIPCode," & @CRLF & _
" x.PatientBirthDateSerial," & @CRLF & _
" x.PatientSex," & @CRLF & _
" x.PatientMaritalStatus," & @CRLF & _
" x.AdmissionDateSerial," & @CRLF & _
" AdmissionHour," & @CRLF & _
" TypeOfAdmission," & @CRLF & _
" SourceOfAdmission," & @CRLF & _
" DischargeHour," & @CRLF & _
" PatientStatus," & @CRLF & _
" x.MedicalRecordNumber," & @CRLF & _
" ConditionCode1," & @CRLF & _
" ConditionCode2," & @CRLF & _
" ConditionCode3," & @CRLF & _
" ConditionCode4," & @CRLF & _
" ConditionCode5," & @CRLF & _
" ConditionCode6," & @CRLF & _
" ConditionCode7," & @CRLF & _
" UnlabeledField_FL31Upper," & @CRLF & _
" UnlabeledField_FL31Lower," & @CRLF & _
" OccurrenceCode1," & @CRLF & _
" OccurrenceDateSerial1," & @CRLF & _
" OccurrenceCode2," & @CRLF & _
" OccurrenceDateSerial2," & @CRLF & _
" OccurrenceCode3," & @CRLF & _
" OccurrenceDateSerial3," & @CRLF & _
" OccurrenceCode4," & @CRLF & _
" OccurrenceDateSerial4," & @CRLF & _
" OccurrenceCode5," & @CRLF & _
" OccurrenceDateSerial5," & @CRLF & _
" OccurrenceCode6," & @CRLF & _
" OccurrenceDateSerial6," & @CRLF & _
" OccurrenceCode7," & @CRLF & _
" OccurrenceDateSerial7," & @CRLF & _
" OccurrenceCode8," & @CRLF & _
" OccurrenceDateSerial8," & @CRLF & _
" OccurrenceSpanCode1," & @CRLF & _
" OccurrenceSpanFromDateSerial1," & @CRLF & _
" OccurrenceSpanThroughDateSerial1," & @CRLF & _
" OccurrenceSpanCode2," & @CRLF & _
" OccurrenceSpanFromDateSerial2," & @CRLF & _
" OccurrenceSpanThroughDateSerial2," & @CRLF & _
" InternalControlNumberA," & @CRLF & _
" InternalControlNumberB," & @CRLF & _
" InternalControlNumberC," & @CRLF & _
" ResponsiblePartyLine1," & @CRLF & _
" ResponsiblePartyLine2," & @CRLF & _
" ResponsiblePartyLine3," & @CRLF & _
" ResponsiblePartyLine4," & @CRLF & _
" ResponsiblePartyLine5," & @CRLF & _
" ValueCode1," & @CRLF & _
" ValueAmount1," & @CRLF & _
" ValueCode2," & @CRLF & _
" ValueAmount2," & @CRLF & _
" ValueCode3," & @CRLF & _
" ValueAmount3," & @CRLF & _
" ValueCode4," & @CRLF & _
" ValueAmount4," & @CRLF & _
" ValueCode5," & @CRLF & _
" ValueAmount5," & @CRLF & _
" ValueCode6," & @CRLF & _
" ValueAmount6," & @CRLF & _
" ValueCode7," & @CRLF & _
" ValueAmount7," & @CRLF & _
" ValueCode8," & @CRLF & _
" ValueAmount8," & @CRLF & _
" ValueCode9," & @CRLF & _
" ValueAmount9," & @CRLF & _
" ValueCode10," & @CRLF & _
" ValueAmount10," & @CRLF & _
" ValueCode11," & @CRLF & _
" ValueAmount11," & @CRLF & _
" ValueCode12," & @CRLF & _
" ValueAmount12," & @CRLF & _
" x.TotalCharges," & @CRLF & _
" PayerIdentificationA," & @CRLF & _
" PayerIdentificationB," & @CRLF & _
" PayerIdentificationC," & @CRLF & _
" ProviderNumberA," & @CRLF & _
" ProviderNumberB," & @CRLF & _
" ProviderNumberC," & @CRLF & _
" ReleaseIndicatorA," & @CRLF & _
" ReleaseIndicatorB," & @CRLF & _
" ReleaseIndicatorC," & @CRLF & _
" AssignmentIndicatorA," & @CRLF & _
" AssignmentIndicatorB," & @CRLF & _
" AssignmentIndicatorC," & @CRLF & _
" PriorPaymentsA," & @CRLF & _
" PriorPaymentsB," & @CRLF & _
" PriorPaymentsC," & @CRLF & _
" PriorPaymentsTotal," & @CRLF & _
" EstimatedAmountDueA," & @CRLF & _
" EstimatedAmountDueB," & @CRLF & _
" EstimatedAmountDueC," & @CRLF & _
" EstimatedAmountDueTotal," & @CRLF & _
" UnlabeledField_FL56_Line1," & @CRLF & _
" UnlabeledField_FL56_Line2," & @CRLF & _
" UnlabeledField_FL56_Line3," & @CRLF & _
" UnlabeledField_FL56_Line4," & @CRLF & _
" UnlabeledField_FL56_Line5," & @CRLF & _
" UnlabeledField_FL57," & @CRLF & _
" InsuredNameA," & @CRLF & _
" InsuredNameB," & @CRLF & _
" InsuredNameC," & @CRLF & _
" RelationToInsuredA," & @CRLF & _
" RelationToInsuredB," & @CRLF & _
" RelationToInsuredC," & @CRLF & _
" CertificateNumberA," & @CRLF & _
" CertificateNumberB," & @CRLF & _
" CertificateNumberC," & @CRLF & _
" GroupNameA," & @CRLF & _
" GroupNameB," & @CRLF & _
" GroupNameC," & @CRLF & _
" GroupNumberA," & @CRLF & _
" GroupNumberB," & @CRLF & _
" GroupNumberC," & @CRLF & _
" AuthorizationCodeA," & @CRLF & _
" AuthorizationCodeB," & @CRLF & _
" AuthorizationCodeC," & @CRLF & _
" EmploymentStatusCodeA," & @CRLF & _
" EmploymentStatusCodeB," & @CRLF & _
" EmploymentStatusCodeC," & @CRLF & _
" EmployerNameA," & @CRLF & _
" EmployerNameB," & @CRLF & _
" EmployerNameC," & @CRLF & _
" EmployerLocationA," & @CRLF & _
" EmployerLocationB," & @CRLF & _
" EmployerLocationC," & @CRLF & _
" PrincipalDiagnosisCode," & @CRLF & _
" OtherDiagnosisCode1," & @CRLF & _
" OtherDiagnosisCode2," & @CRLF & _
" OtherDiagnosisCode3," & @CRLF & _
" OtherDiagnosisCode4," & @CRLF & _
" OtherDiagnosisCode5," & @CRLF & _
" OtherDiagnosisCode6," & @CRLF & _
" OtherDiagnosisCode7," & @CRLF & _
" OtherDiagnosisCode8," & @CRLF & _
" AdmittingDiagnosisCode," & @CRLF & _
" ExternalInjuryCauseCode," & @CRLF & _
" UnlabeledField_FL78Upper," & @CRLF & _
" UnlabeledField_FL78Lower," & @CRLF & _
" ProcedureCodingMethod," & @CRLF & _
" PrincipalProcedureCode," & @CRLF & _
" PrincipalProcedureDateSerial," & @CRLF & _
" OtherProcedureCode1," & @CRLF & _
" OtherProcedureDateSerial1," & @CRLF & _
" OtherProcedureCode2," & @CRLF & _
" OtherProcedureDateSerial2," & @CRLF & _
" OtherProcedureCode3," & @CRLF & _
" OtherProcedureDateSerial3," & @CRLF & _
" OtherProcedureCode4," & @CRLF & _
" OtherProcedureDateSerial4," & @CRLF & _
" OtherProcedureCode5," & @CRLF & _
" OtherProcedureDateSerial5," & @CRLF & _
" AttendingPhysicianStateID," & @CRLF & _
" x.AttendingPhysicianID," & @CRLF & _
" AttendingPhysicianName," & @CRLF & _
" OtherPhysician1StateID," & @CRLF & _
" OtherPhysician1ID," & @CRLF & _
" OtherPhysician1Name," & @CRLF & _
" OtherPhysician2StateID," & @CRLF & _
" OtherPhysician2ID," & @CRLF & _
" OtherPhysician2Name," & @CRLF & _
" RemarksA," & @CRLF & _
" RemarksB," & @CRLF & _
" RemarksC," & @CRLF & _
" RemarksD," & @CRLF & _
" ProviderRepresentative," & @CRLF & _
" BillDateSerial," & @CRLF & _
" 717593," & @CRLF & _
" SegmentNumber," & @CRLF & _
" x.DRGCode," & @CRLF & _
" 0 as RV," & @CRLF & _
" GETDATE() as LU," & @CRLF & _
" GETDATE() as DC," & @CRLF & _
" 0 as Inactive," & @CRLF & _
" CASE WHEN ( ISNULL(PatientControlNumber,'')=''" & @CRLF & _
" OR ISDATE(x.AdmissionDate) != 1" & @CRLF & _
" OR (CertificateNumberA IS NULL AND CertificateNumberB IS NULL AND CertificateNumberC IS NULL)" & @CRLF & _
" OR PrincipalDiagnosisCode IS NULL" & @CRLF & _
" OR ISDATE(BillDate) !=1" & @CRLF & _
" )" & @CRLF & _
" THEN 1" & @CRLF & _
" ELSE 0" & @CRLF & _
" END AS Invalid," & @CRLF & _
" x.PayerLinkCode," & @CRLF & _
" TotalNonCoveredCharges," & @CRLF & _
" Priority," & @CRLF & _
" IgnoreBillDate," & @CRLF & _
" xd.TotalDetailFacilityCharges," & @CRLF & _
" UnlabeledField_FL2Line3," & @CRLF & _
" UnlabeledField_FL2Line4," & @CRLF & _
" PatientStreet," & @CRLF & _
" PatientCity," & @CRLF & _
" PatientState," & @CRLF & _
" PatientCountryCode," & @CRLF & _
" ConditionCode8," & @CRLF & _
" ConditionCode9," & @CRLF & _
" ConditionCode10," & @CRLF & _
" ConditionCode11," & @CRLF & _
" AccidentState," & @CRLF & _
" OccurrenceSpanCode3," & @CRLF & _
" OccurrenceSpanFromDateSerial3," & @CRLF & _
" OccurrenceSpanThroughDateSerial3," & @CRLF & _
" OccurrenceSpanCode4," & @CRLF & _
" OccurrenceSpanFromDateSerial4," & @CRLF & _
" OccurrenceSpanThroughDateSerial4," & @CRLF & _
" OtherDiagnosisCode9," & @CRLF & _
" OtherDiagnosisCode10," & @CRLF & _
" OtherDiagnosisCode11," & @CRLF & _
" OtherDiagnosisCode12," & @CRLF & _
" OtherDiagnosisCode13," & @CRLF & _
" OtherDiagnosisCode14," & @CRLF & _
" OtherDiagnosisCode15," & @CRLF & _
" OtherDiagnosisCode16," & @CRLF & _
" OtherDiagnosisCode17," & @CRLF & _
" PatientReasonForVisitCode1," & @CRLF & _
" PatientReasonForVisitCode2," & @CRLF & _
" PatientReasonForVisitCode3," & @CRLF & _
" PPSCode," & @CRLF & _
" ExternalInjuryCauseCode2," & @CRLF & _
" ExternalInjuryCauseCode3," & @CRLF & _
" UnlabeledField_FL75_1," & @CRLF & _
" UnlabeledField_FL75_2," & @CRLF & _
" UnlabeledField_FL75_3," & @CRLF & _
" UnlabeledField_FL75_4," & @CRLF & _
" AttendingPhysicianNPI," & @CRLF & _
" AttendingPhysicianStateIDQualifier," & @CRLF & _
" AttendingPhysicianLastName," & @CRLF & _
" AttendingPhysicianFirstName," & @CRLF & _
" OperatingPhysicianNPI," & @CRLF & _
" OperatingPhysicianStateID," & @CRLF & _
" OperatingPhysicianStateIDQualifier," & @CRLF & _
" OperatingPhysicianLastName," & @CRLF & _
" OperatingPhysicianFirstName," & @CRLF & _
" OtherPhysician1Qualifier," & @CRLF & _
" OtherPhysician1NPI," & @CRLF & _
" OtherPhysician1StateIDQualifier," & @CRLF & _
" OtherPhysician1LastName," & @CRLF & _
" OtherPhysician1FirstName," & @CRLF & _
" OtherPhysician2Qualifier," & @CRLF & _
" OtherPhysician2NPI," & @CRLF & _
" OtherPhysician2StateIDQualifier," & @CRLF & _
" OtherPhysician2LastName," & @CRLF & _
" OtherPhysician2FirstName," & @CRLF & _
" CCCode1," & @CRLF & _
" CCQualifier1," & @CRLF & _
" CCValue1," & @CRLF & _
" CCCode2," & @CRLF & _
" CCQualifier2," & @CRLF & _
" CCValue2," & @CRLF & _
" CCCode3," & @CRLF & _
" CCQualifier3," & @CRLF & _
" CCValue3," & @CRLF & _
" CCCode4," & @CRLF & _
" CCQualifier4," & @CRLF & _
" CCValue4," & @CRLF & _
" UnlabeledField_FL7Upper," & @CRLF & _
" UnlabeledField_FL7Lower," & @CRLF & _
" UnlabeledField_FL37Upper," & @CRLF & _
" UnlabeledField_FL37Lower," & @CRLF & _
" UnlabeledField_FL73," & @CRLF & _
" PatientIdentification8a," & @CRLF & _
" POA_PDx," & @CRLF & _
" POA_SDx1," & @CRLF & _
" POA_SDx2," & @CRLF & _
" POA_SDx3," & @CRLF & _
" POA_SDx4," & @CRLF & _
" POA_SDx5," & @CRLF & _
" POA_SDx6," & @CRLF & _
" POA_SDx7," & @CRLF & _
" POA_SDx8," & @CRLF & _
" POA_SDx9," & @CRLF & _
" POA_SDx10," & @CRLF & _
" POA_SDx11," & @CRLF & _
" POA_SDx12," & @CRLF & _
" POA_SDx13," & @CRLF & _
" POA_SDx14," & @CRLF & _
" POA_SDx15," & @CRLF & _
" POA_SDx16," & @CRLF & _
" POA_SDx17," & @CRLF & _
" POA_ECI1," & @CRLF & _
" POA_ECI2," & @CRLF & _
" POA_ECI3," & @CRLF & _
" ProviderTaxonomyCode," & @CRLF & _
" VersionNumber," & @CRLF & _
" ExternalInjuryCauseCode4," & @CRLF & _
" ExternalInjuryCauseCode5," & @CRLF & _
" ExternalInjuryCauseCode6," & @CRLF & _
" ExternalInjuryCauseCode7," & @CRLF & _
" ExternalInjuryCauseCode8," & @CRLF & _
" ExternalInjuryCauseCode9," & @CRLF & _
" ExternalInjuryCauseCode10," & @CRLF & _
" ExternalInjuryCauseCode11," & @CRLF & _
" ExternalInjuryCauseCode12," & @CRLF & _
" POA_ECI4," & @CRLF & _
" POA_ECI5," & @CRLF & _
" POA_ECI6," & @CRLF & _
" POA_ECI7," & @CRLF & _
" POA_ECI8," & @CRLF & _
" POA_ECI9," & @CRLF & _
" POA_ECI10," & @CRLF & _
" POA_ECI11," & @CRLF & _
" POA_ECI12," & @CRLF & _
" asdfs.sdfsadfaf=OtherProcedureCode6, " & @CRLF & _
" OtherProcedureCode7," & @CRLF & _
" OtherProcedureCode8," & @CRLF & _
" OtherProcedureCode9," & @CRLF & _
" OtherProcedureCode10," & @CRLF & _
" OtherProcedureCode11," & @CRLF & _
" OtherProcedureCode12," & @CRLF & _
" OtherProcedureCode13," & @CRLF & _
" OtherProcedureCode14, " & @CRLF & _
" OtherProcedureCode15," & @CRLF & _
" OtherProcedureCode16," & @CRLF & _
" OtherProcedureCode17," & @CRLF & _
" OtherProcedureCode18," & @CRLF & _
" OtherProcedureCode19," & @CRLF & _
" OtherProcedureCode20," & @CRLF & _
" OtherProcedureCode21," & @CRLF & _
" OtherProcedureCode22," & @CRLF & _
" OtherProcedureCode23," & @CRLF & _
" OtherProcedureCode24," & @CRLF & _
" OtherProcedureDate6 ," & @CRLF & _
" OtherProcedureDate7 ," & @CRLF & _
" OtherProcedureDate8 ," & @CRLF & _
" OtherProcedureDate9 ," & @CRLF & _
" OtherProcedureDate10 ," & @CRLF & _
" OtherProcedureDate11 ," & @CRLF & _
" OtherProcedureDate12," & @CRLF & _
" OtherProcedureDate13," & @CRLF & _
" OtherProcedureDate14," & @CRLF & _
" OtherProcedureDate15," & @CRLF & _
" OtherProcedureDate16," & @CRLF & _
" OtherProcedureDate17," & @CRLF & _
" OtherProcedureDate18," & @CRLF & _
" OtherProcedureDate19," & @CRLF & _
" OtherProcedureDate20," & @CRLF & _
" OtherProcedureDate21," & @CRLF & _
" OtherProcedureDate22," & @CRLF & _
" OtherProcedureDate23," & @CRLF & _
" OtherProcedureDate24," & @CRLF & _
" POA_SDx18," & @CRLF & _
" POA_SDx19," & @CRLF & _
" POA_SDx20," & @CRLF & _
" POA_SDx21," & @CRLF & _
" POA_SDx22," & @CRLF & _
" POA_SDx23," & @CRLF & _
" POA_SDx24," & @CRLF & _
" OtherDiagnosisCode18," & @CRLF & _
" OtherDiagnosisCode19," & @CRLF & _
" OtherDiagnosisCode20," & @CRLF & _
" OtherDiagnosisCode21," & @CRLF & _
" OtherDiagnosisCode22," & @CRLF & _
" OtherDiagnosisCode23," & @CRLF & _
" OtherDiagnosisCode24 " & @CRLF & _
" FROM STAGING.UB04_Load x" & @CRLF & _
" JOIN (SELECT UB04_LoadSegmentNumber, SUM(Charges) as TotalDetailCharges, SUM(CASE WHEN IsFacilityCharge = 1 THEN Charges ELSE 0 END) as TotalDetailFacilityCharges " & @CRLF & _
" FROM STAGING.UB04Detail_Load " & @CRLF & _
" WHERE LoadBatchID=717593" & @CRLF & _
" AND IsValid=1" & @CRLF & _
" GROUP BY UB04_LoadSegmentNumber" & @CRLF & _
" ) xd ON x.SegmentNumber=xd.UB04_LoadSegmentNumber" & @CRLF & _
" WHERE x.RowID BETWEEN 2226942 AND 2227669" & @CRLF & _
" AND x.LoadbatchID=717593" & @CRLF & _
" AND x.IsValid=1" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
" SELECT @UBRowsInserted=@@ROWCOUNT" & @CRLF & _
" Print 'Done. Inserted ' + cast(@UbRowsInserted as varchar(10)) + 'UB records.'" & @CRLF & _
" Print 'Inserting detail...'" & @CRLF & _
" /* INSERT UB DETAIL RECORDS */" & @CRLF & _
"" & @CRLF & _
" INSERT INTO app.UB04Detail (" & @CRLF & _
" UB04ID," & @CRLF & _
" RevenueCode," & @CRLF & _
" Description," & @CRLF & _
" HCPCSRates," & @CRLF & _
" ProcedureCode," & @CRLF & _
" RoomRate," & @CRLF & _
" ServiceDateSerial," & @CRLF & _
" ServiceUnits," & @CRLF & _
" Charges," & @CRLF & _
" NonCoveredCharges," & @CRLF & _
" UnlabeledField_FL49," & @CRLF & _
" RV)" & @CRLF & _
" SELECT ub.UB04ID," & @CRLF & _
" ubd.RevenueCode," & @CRLF & _
" ubd.Description," & @CRLF & _
" ubd.HCPCSRates," & @CRLF & _
" ubd.CPTCode," & @CRLF & _
" ubd.RoomRate," & @CRLF & _
" ubd.ServiceDateSerial," & @CRLF & _
" ubd.ServiceUnits," & @CRLF & _
" ISNULL(ubd.Charges,0)," & @CRLF & _
" ubd.NonCoveredCharges," & @CRLF & _
" ubd.UnlabeledField_FL49," & @CRLF & _
" 0 as RV" & @CRLF & _
" " & @CRLF & _
" FROM STAGING.UB04Detail_Load ubd" & @CRLF & _
" JOIN App.UB04 ub ON ubd.UB04_LoadSegmentNumber=ub.SegmentNumber AND ubd.LoadBatchID=ub.LoadBatchID" & @CRLF & _
" JOIN Staging.UB04_Load x ON x.SegmentNumber=ubd.UB04_LoadSegmentNumber AND ubd.LoadbatchID=x.LoadbatchID" & @CRLF & _
" WHERE ubd.IsValid=1" & @CRLF & _
" AND x.IsValid=1" & @CRLF & _
" AND ub.LoadbatchID=x.LoadbatchID" & @CRLF & _
" AND x.RowID BETWEEN 2226942 AND 2227669" & @CRLF & _
" AND x.LoadbatchID=717593" & @CRLF & _
" " & @CRLF & _
" SELECT @UBDetailRowsInserted=@@ROWCOUNT" & @CRLF & _
" " & @CRLF & _
" Print 'Done. Inserted ' + cast(@UBDetailRowsInserted as varchar(10)) + ' detail records.'" & @CRLF & _
" " & @CRLF & _
" Print 'Checking for errors...'" & @CRLF & _
"IF (ISNULL(@ValidUBCount,0) < @RecordsInBatch) OR ISNULL(@UBRowsInserted,0) != ISNULL(@ValidUBCount,0)" & @CRLF & _
"BEGIN" & @CRLF & _
" Print 'Found errors. Inserting UB level errors into LoadbatchError...'" & @CRLF & _
" INSERT INTO STAGING.LoadBatchError " & @CRLF & _
" (LoadBatchID, " & @CRLF & _
" RowID," & @CRLF & _
" ErrorDescription)" & @CRLF & _
" SELECT " & @CRLF & _
" 717593, " & @CRLF & _
" RowID," & @CRLF & _
" ValidationMessage" & @CRLF & _
"" & @CRLF & _
" FROM STAGING.UB04_Load x" & @CRLF & _
" WHERE IsValid=0" & @CRLF & _
" AND x.LoadbatchID=717593" & @CRLF & _
" AND x.RowID BETWEEN 2226942 and 2227669" & @CRLF & _
" " & @CRLF & _
" Print 'Done inserting UB errors'" & @CRLF & _
" Print 'Inserting detail errors...'" & @CRLF & _
" INSERT INTO STAGING.LoadBatchError " & @CRLF & _
" (LoadBatchID, " & @CRLF & _
" RowID," & @CRLF & _
" ErrorDescription)" & @CRLF & _
" SELECT " & @CRLF & _
" 717593, " & @CRLF & _
" ubd.RowID," & @CRLF & _
" 'Invalid Detail record:' + ISNULL(ubd.ValidationMessage,'(EMPTY)')" & @CRLF & _
" FROM STAGING.UB04Detail_Load ubd" & @CRLF & _
" JOIN Staging.UB04_Load xw ON x.SegmentNumber=ubd.UB04_LoadSegmentNumber AND ubd.LoadbatchID=x.LoadbatchID" & @CRLF & _
" WHERE ubd.IsValid=0" & @CRLF & _
" AND x.LoadbatchID=717593" & @CRLF & _
" AND x.RowID BETWEEN 2226942 and 2227669" & @CRLF & _
" Print ' Done inserting detail errors.'" & @CRLF & _
" " & @CRLF & _
"END" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
" PROC_CLEANUP:" & @CRLF & _
" Print 'Cleaning up load records...'" & @CRLF & _
" DELETE ubd " & @CRLF & _
" FROM STAGING.UB04Detail_Load ubd" & @CRLF & _
" JOIN App.UB04 ub ON ubd.UB04_LoadSegmentNumber=ub.SegmentNumber AND ub.LoadBatchID=717593" & @CRLF & _
" WHERE ubd.LoadbatchID=717593" & @CRLF & _
" AND ubd.IsValid=1" & @CRLF & _
" Print 'Done with UB cleanup.'" & @CRLF & _
" Print 'Cleaning up detail records...'" & @CRLF & _
" DELETE x" & @CRLF & _
" FROM STAGING.UB04_Load x" & @CRLF & _
" JOIN APP.UB04 ub ON x.RowID = ub.SegmentNumber AND ub.LoadBatchID=717593 " & @CRLF & _
" LEFT JOIN STAGING.UB04Detail_Load ubd ON ubd.UB04_LoadSegmentNumber=ub.SegmentNumber AND ub.LoadBatchID=717593" & @CRLF & _
" WHERE x.RowID BETWEEN 2226942 AND 2227669 " & @CRLF & _
" AND x.LoadbatchID=717593" & @CRLF & _
" AND ubd.RowID IS NULL" & @CRLF & _
" Print 'Done with detail..'" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
" SET @ReturnValue=0 " & @CRLF & _
" COMMIT TRANSACTION" & @CRLF & _
"" & @CRLF & _
"END TRY " & @CRLF & _
"BEGIN CATCH" & @CRLF & _
" Exec usp_PrintErrorInfo" & @CRLF & _
" RaisError('Problem in Ub Loader for LoadbatchID %d. Details to follow...', 16,1, 717593)" & @CRLF & _
" ROLLBACK TRANSACTION" & @CRLF & _
"END CATCH" & @CRLF & _
"" & @CRLF & _
"IF ISNULL(@UBRowsInserted,0) != ISNULL(@ValidUBCount,0)" & @CRLF & _
"BEGIN" & @CRLF & _
" RaisError('Number of UB records inserted %d does not match the number of valid records %d. Check to make sure each UB record has a detail record.', 16,1,@UbRowsInserted, @ValidUBCount)" & @CRLF & _
" SET @ReturnValue=99" & @CRLF & _
"END" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
"--RETURN @ReturnValue" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
"" & @CRLF & _
""
Local $aArray = StringRegExp($sString, $sRegex, $STR_REGEXPARRAYGLOBALFULLMATCH)
Local $aFullArray[0]
For $i = 0 To UBound($aArray) -1
_ArrayConcatenate($aFullArray, $aArray[$i])
Next
$aArray = $aFullArray
; Present the entire match result
_ArrayDisplay($aArray, "Result")
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for AutoIt, please visit: https://www.autoitscript.com/autoit3/docs/functions/StringRegExp.htm