Variable Usage Problems (LARGE POST)
TheSQLGuru
Posts: 78 Silver 2
Given the sproc below, there are a number of places where variables are declared as being unused and/or having a value set but not referenced afterwards. Please review. It appears that the STUFF command is definitely one of the culprits. BTW, if you can get Refactor to handle THIS BEAST without a hitch you will be doing quite well I think! 3152 lines of code, and a hundred or more to go before I am finished! :-)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID('dbo.rcms_837I_To_UB04Document') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.rcms_837I_To_UB04Document
IF OBJECT_ID('dbo.rcms_837I_To_UB04Document') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.rcms_837I_To_UB04Document >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.rcms_837I_To_UB04Document >>>'
END
go
CREATE PROC dbo.rcms_837I_To_UB04Document ( @ClaimID int)
as
/***********************************************************************************************
* Name: rcms_837I_To_UB04Document
* Author: Kevin Boles
* Date: 3/09/2007
*
* Output:
*
* Desc:
* Revision:
*
***********************************************************************************************/
SET NOCOUNT ON
DECLARE
@ClaimTypeKey int, @ClaimCode varchar(2),
@InsCompanyKey int, @ProvName_010 varchar(50),
@ProvName_020 varchar(50), @ProvName_030 varchar(50),
@ProvName_040 varchar(50), @Misc2 varchar(30),
@PayToName_010 varchar(25), @PayToName_020 varchar(25),
@PayToName_030 varchar(25),
@PatientControlNo varchar(38), @PatientControlNo_Print varchar(38),
@TypeofBill char(4), @FedTaxNo varchar(11),
@StmtCovFm varchar(10), @StmtCovThr varchar(10),
@CoveredDays varchar(6), @NonCovDays varchar(6),
@CoinsDays varchar(6), @LifeResDays varchar(6),
@Misc11 varchar(15), @PatientName varchar(50),
@PatientAddress varchar(51), @Birthdate varchar(10),
@Sex char(2), @MaritalStatus char(2),
@AdmDate varchar(10), @AdmHR varchar(8),
@AdmType char(1), @AdmSrc char(3),
@DischgHR varchar(8), @Status char(3),
@MedRecordNo varchar(20), @CondCode_010 char(3),
@CondCode_020 char(3), @CondCode_030 char(3),
@CondCode_040 char(3), @CondCode_050 char(3),
@CondCode_060 char(3), @CondCode_070 char(3),
@CondCode_080 char(3),
@CondCode_090 char(3),
@CondCode_100 char(3),
@CondCode_110 char(3),
@Misc31 varchar(10), @OccCode_01a char(3),
@OccDate_01a varchar(10), @OccCode_01b char(3),
@OccDate_01b varchar(10), @OccCode_02a char(3),
@OccDate_02a varchar(10), @OccCode_02b char(3),
@OccDate_02b varchar(10), @OccCode_03a char(3),
@OccDate_03a varchar(10), @OccCode_03b char(3),
@OccDate_03b varchar(10), @OccCode_04a char(3),
@OccDate_04a varchar(10), @OccCode_04b char(3),
@OccDate_04b varchar(10), @OccSpCode_05a char(3),
@OccSpFm_05a varchar(10), @OccSpThr_05a varchar(10),
@OccSpCode_05b char(3), @OccSpFm_05b varchar(10),
@OccSpThr_05b varchar(10),
@OccSpCode_05c char(3),
@OccSpFm_05c varchar(10),
@OccSpThr_05c varchar(10),
@OccSpCode_05d char(3),
@OccSpFm_05d varchar(10),
@OccSpThr_05d varchar(10),
@ICNDCN_01a varchar(26),
@ICNDCN_01b varchar(26), @ICNDCN_01c varchar(26),
@Misc38_010 varchar(50), @Misc38_020 varchar(50),
@Misc38_030 varchar(50), @Misc38_040 varchar(50),
@Misc38_050 varchar(50), @ValCdCode_01a char(2),
@ValCdAmt_01a varchar(18), @ValCdCode_01b char(2),
@ValCdAmt_01b varchar(18), @ValCdCode_01c char(2),
@ValCdAmt_01c varchar(18), @ValCdCode_01d char(2),
@ValCdAmt_01d varchar(18), @ValCdCode_02a char(2),
@ValCdAmt_02a varchar(18), @ValCdCode_02b char(2),
@ValCdAmt_02b varchar(18), @ValCdCode_02c char(2),
@ValCdAmt_02c varchar(18), @ValCdCode_02d char(2),
@ValCdAmt_02d varchar(18), @ValCdCode_03a char(2),
@ValCdAmt_03a varchar(18), @ValCdCode_03b char(2),
@ValCdAmt_03b varchar(18), @ValCdCode_03c char(2),
@ValCdAmt_03c varchar(18), @ValCdCode_03d char(2),
@ValCdAmt_03d varchar(18), @TotServUnits varchar(8),
@TotCharges decimal(19,2), @TotNChanges decimal(19,2),
@Payer_01a varchar(23), @Payer_01b varchar(23),
@Payer_01c varchar(23), @HealthPlanID_01a varchar(15),
@HealthPlanID_01b varchar(15), @HealthPlanID_01c varchar(15),
@RelInfo_01a char(1), @RelInfo_01b char(1),
@RelInfo_01c char(1), @AsgBen_01a char(1),
@AsgBen_01b char(1), @AsgBen_01c char(1),
@PrPymts_01a decimal(19,2), @PrPymts_01b decimal(19,2),
@PrPymts_01c decimal(19,2), @TotPrPymt decimal(19,2),
@EstAmtDue_01a decimal(19,2), @EstAmtDue_01b decimal(19,2),
@EstAmtDue_01c decimal(19,2), @TotEstAmtDue decimal(19,2), --need calculation for this??
@Misc56_01a varchar(15), @Misc56_01b varchar(15),
@Misc56_01c varchar(15), @Misc56_01d varchar(15),
@Misc56_01e varchar(15), @InsName_01a varchar(25),
@InsName_01b varchar(25), @InsName_01c varchar(25),
@PRelIns_01a char(2), @PRelIns_01b char(2),
@PRelIns_01c char(2), @InsID_01a varchar(20),
@InsID_01b varchar(20), @InsID_01c varchar(20),
@GrpName_01a varchar(14), @GrpName_01b varchar(14),
@GrpName_01c varchar(14), @InsGrpNo_01a varchar(17),
@InsGrpNo_01b varchar(17), @InsGrpNo_01c varchar(17),
@TrmtAuth_01a varchar(19), @TrmtAuth_01b varchar(19),
@TrmtAuth_01c varchar(19), @EmpSt_01a char(2),
@EmpSt_01b char(2), @EmpSt_01c char(2),
@EmpName_01a varchar(25), @EmpName_01b varchar(25),
@EmpName_01c varchar(25), @EmpLoc_01a varchar(36),
@EmpLoc_01b varchar(36), @EmpLoc_01c varchar(36),
@PrinDiagCD varchar(8), @OthDiagCD_010 varchar(8),
@OthDiagCD_020 varchar(8), @OthDiagCD_030 varchar(8),
@OthDiagCD_040 varchar(8), @OthDiagCD_050 varchar(8),
@OthDiagCD_060 varchar(8), @OthDiagCD_070 varchar(8),
@OthDiagCD_080 varchar(8), @OthDiagCD_090 varchar(8),
@OthDiagCD_100 varchar(8), @OthDiagCD_110 varchar(8),
@OthDiagCD_120 varchar(8), @OthDiagCD_130 varchar(8),
@OthDiagCD_140 varchar(8), @OthDiagCD_150 varchar(8),
@OthDiagCD_160 varchar(8), @OthDiagCD_170 varchar(8),
@AdmDiagCD varchar(7), @PatVisitReasonCD varchar(7),
@ECode varchar(8), @Misc78 char(4),
@PC char(2), @PrinProcCode varchar(7),
@PrinProcDate varchar(6), @OthProcCode_01a varchar(7),
@OthProcDate_01a varchar(6), @OthProcCode_01b varchar(7),
@OthProcDate_01b varchar(6), @OthProcCode_01c varchar(7),
@OthProcDate_01c varchar(6), @OthProcCode_01d varchar(7),
@OthProcDate_01d varchar(6), @OthProcCode_01e varchar(7),
@OthProcDate_01e varchar(6),
@AttPhysNPI varchar(11), @AttPhysSecQual char(2),
@AttPhysSecID varchar(9), @AttPhysLastName varchar(16),
@AttPhysFirstName varchar(12),
@OperPhysNPI varchar(11), @OperPhysSecQual char(2),
@OperPhysSecID varchar(9), @OperPhysLastName varchar(16),
@OperPhysFirstName varchar(12),
@OtherPhysNPI varchar(11), @OtherPhysSecQual char(2),
@OtherPhysSecID varchar(9), @OtherPhysLastName varchar(16),
@OtherPhysFirstName varchar(12),
@Remarks_010 varchar(19),
@Remarks_020 varchar(24), @Remarks_030 varchar(24),
@Remarks_040 varchar(24),
@ProvRep varchar(24),
@RepDate varchar(10), @ATBBalance decimal(19,2),
@PrimarySecondaryCode varchar(1), @PatientLastName varchar(50),
@PatientFirstName varchar(20), @PatientMiddleName varchar(20),
@MedicarePartB smallint, @ProcessedCount int,
@ATBDate datetime, @GroupKey int,
@ProcessedFlag tinyint, @LastProcessed datetime,
@DateAdded datetime, @Misc57 varchar(50),
@AppendLine66 varchar(82), @InsCompany varchar(35)
DECLARE @NM109_PatientPrimaryID varchar(80),
@N301_PatientAddressLine1 varchar(55),
@N401_PatientCity varchar(30),
@N402_PatientState char(2),
@N403_PatientZipCode varchar(15),
@N404_PatientCountryCode char(3)
DECLARE @str1 varchar(78), @str2 varchar(78), @str3 varchar(78), @str4 varchar(78)
DECLARE @FL84Override varchar(255)
DECLARE @FL38Override varchar(255)
DECLARE @FL51Override varchar(255)
DECLARE @Section50counter tinyint, --1=A, 2=B, 3=C line
@Section58Counter tinyint --1=A, 2=B, 3=C line
DECLARE @pageOutput TABLE (line varchar(82))
DECLARE @ServiceLineMaxCount smallint, @totalLineNoncoveredCharges decimal(19,2),
@ServiceLinesOutput smallint, @PageCounter tinyint, @TotalPageCount tinyint,
@rowcount int, @CreationDate char(6), @totalLineCharges decimal(19,2)
DECLARE @FL56_NPI varchar(15)
DECLARE @header TABLE (linedata varchar(82))
DECLARE @service TABLE (linedata varchar(82))
DECLARE @footer TABLE (linedata varchar(82))
DECLARE @line varchar(82)
--TOP OF PAGE STUFF?? Mike, what goes here?
--for now, just blank lines
--INSERT @header
--VALUES ('0000000001111111111222222222233333333334444444444555555555566666666667777777777888')
INSERT @header
VALUES ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012')
DECLARE @TransactionSetID int, @HL01_Claim int, --@ClaimID int,
@PatientFlag char(1), --'P' for tbl837IPatient, 'S' for tbl837ISubscriber
@HL01_BillingProvider int, @HL01_Subscriber int,
@HL01_Patient int, @i tinyint
--Client Specific Override variable gathering section &CSO&
/* current allowable values for this are:
PatientName: put patientname and address in FL38
*/
SELECT @FL38Override = ItemValue
FROM dbo.tblClientSettings (NOLOCK)
WHERE ItemName = '837I_UB92_FL38Override'
/* current allowable values for this are:
SecondaryIDNoPriority: uses tbl837IBillingProviderRef for A and tbl837IOtherSubscriberPayerRef for B and C
with no priority for the qualifier
*/
SELECT @FL51Override = ItemValue
FROM dbo.tblClientSettings (NOLOCK)
WHERE ItemName = '837I_UB92_FL51Override'
/* current allowable values for this are:
50APayerName_and_Address: uses name and address for the FL50A payer
*/
SELECT @FL84Override = ItemValue
FROM dbo.tblClientSettings (NOLOCK)
WHERE ItemName = '837I_UB92_FL84Override'
SET @MaritalStatus = '' --hard coded to empty, since 837I doesn't have this field
--Claim stuff
SELECT @TransactionSetID = TransactionSetID,
@HL01_Claim = HL01_IDNumber,
@PatientControlNo_Print = ISNULL(CLM01_PatientControlNumber_837I, ''), --are these backwards?
@PatientControlNo = ISNULL(CLM01_PatientControlNumber, ''),
@TypeofBill = ISNULL(CLM05_1_FacilityTypeCode, '') + ISNULL(CLM05_3_ClaimFrequencyCode, ''),
@StmtCovFm = ISNULL(dbo.fn_DateToString_MMDDYY(DTP03_StatementStartDate), ''),
@StmtCovThr = ISNULL(dbo.fn_DateToString_MMDDYY(DTP03_StatementEndDate), ''),
@DischgHR = ISNULL(LEFT(CASE WHEN DTP03_DischargeHour <> '' THEN DTP03_DischargeHour ELSE '0' END, 2), ''),
@TotCharges = ISNULL(CAST(CLM02_TotalClaimCharges as decimal(19,2)), 0.00),
@AdmDate = ISNULL(dbo.fn_DateToString_MMDDYY(DTP03_AdmissionDate), ''),
@AdmHR = ISNULL(LEFT(CONVERT(varchar(8), DTP03_AdmissionDate, 8), 2), ''),
@AdmType = ISNULL(CL101_AdmissionTypeCode, ''),
@AdmSrc = ISNULL(CL102_AdmissionSourceCode, ''),
@Status = ISNULL(CL103_PatientStatusCode, ''),
@RelInfo_01a = ISNULL(LEFT(CLM09_ReleaseOfInformationCode, 1), ''),
@AsgBen_01a = ISNULL(LEFT(CLM08_AssignmentofBenefitsIndicator, 1), '')
FROM dbo.tbl837IClaim (NOLOCK)
WHERE ClaimID = @ClaimID
SELECT @HL01_BillingProvider = NULL
--Try to get Subscriber stuff rom Claim HL
SELECT @HL01_BillingProvider = HL02_ParentIDNumber,
@InsName_01a = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 25),
@PRelIns_01a = ISNULL(LEFT(SBR02_PatientRelationshipToInsured, 2), ''),
@InsID_01a = ISNULL(LEFT(NM109_SubscriberPrimaryID, 20), ''),
@GrpName_01a = ISNULL(LEFT(SBR04_GroupName, 14), ''),
@InsGrpNo_01a = ISNULL(LEFT(SBR03_GroupNumber, 17), ''),
@PatientName = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 29),
@NM109_PatientPrimaryID = ISNULL(LEFT(NM109_SubscriberPrimaryID, 19), ''),
@N301_PatientAddressLine1 = ISNULL(LEFT(N301_SubscriberAddressLine1, 40), ''),
@N401_PatientCity = ISNULL(LEFT(N401_SubscriberCity, 30), ''),
@N402_PatientState = ISNULL(LEFT(N402_SubscriberState, 2), ''),
@N403_PatientZipCode = ISNULL(LEFT(N403_SubscriberZipCode, 9), ''),
@N404_PatientCountryCode = ISNULL(LEFT(N404_SubscriberCountryCode, 3), ''),
@Birthdate = ISNULL(dbo.fn_DateToString_MMDDCCYY(DMG02_SubscriberBirthDate), ''),
@Sex = ISNULL(DMG03_SubscriberGenderCode, ''),
@Remarks_010 = ISNULL(LEFT(N301_SubscriberAddressLine1, 19), ''),
@Remarks_020 = ISNULL(LEFT(N302_SubscriberAddressLine2, 24), ''),
@Remarks_030 = LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 24),
@Remarks_040 = '',
@Payer_01a = ISNULL(LEFT(NM103_PayerName, 23), ''),
@HealthPlanID_01a = ISNULL(LEFT(NM109_PayerID, 15), ''),
@Misc38_010 = LEFT(ISNULL(CASE WHEN NM103_ResponsiblePartyLastName = '' THEN NULL
ELSE NM103_ResponsiblePartyLastName
END + ', ', '') +
ISNULL(NM104_ResponsiblePartyFirstName + ' ', '') +
ISNULL(NM105_ResponsiblePartyMiddleName + ' ', '') +
ISNULL(NM107_ResponsiblePartyNameSuffix + ' ', ''), 40),
@Misc38_020 = ISNULL(LEFT(N301_ResponsiblePartyAddressLine1, 40), ''),
@Misc38_030 = ISNULL(LEFT(N302_ResponsiblePartyAddressLine2, 40), ''),
@Misc38_040 = LEFT(ISNULL(CASE WHEN N401_ResponsiblePartyCity = '' THEN NULL
ELSE N401_ResponsiblePartyCity
END + ', ', '') +
ISNULL(N402_ResponsiblePartyState + ' ', '') +
ISNULL(N403_ResponsiblePartyZipCode + ' ', ''), 40),
@HL01_Subscriber = HL01_IDNumber
FROM dbo.tbl837ISubscriber (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Claim
IF @HL01_BillingProvider IS NOT NULL
BEGIN
--Patient IS Subscriber
SET @PatientFlag = 'S'
--&CSO& override to get FL51 field for FL51a
IF @FL51Override = 'SecondaryIDNoPriority'
BEGIN
SET @HealthPlanID_01a = ''
SELECT TOP 1 @HealthPlanID_01a = LEFT(ISNULL(REF02_ReferenceID, ''), 15)
FROM dbo.tbl837IBillingProviderRef (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_BillingProvider
END
END
ELSE
BEGIN
--Patient IS tbl837IPatient
SET @PatientFlag = 'P'
--first get the patient stuff, including the subscriber HL
SELECT @HL01_Subscriber = HL02_ParentIDNumber,
@PatientName = LEFT(ISNULL(CASE WHEN NM103_PatientLastName = '' THEN NULL
ELSE NM103_PatientLastName
END + ', ', '') +
ISNULL(NM104_PatientFirstName + ' ', '') +
ISNULL(NM105_PatientMiddleName + ' ', '') +
ISNULL(NM107_PatientNameSuffix + ' ', ''), 29),
@NM109_PatientPrimaryID = ISNULL(LEFT(NM109_PatientPrimaryID, 19), ''),
@N301_PatientAddressLine1 = ISNULL(LEFT(N301_PatientAddressLine1, 40), ''),
@N401_PatientCity = ISNULL(LEFT(N401_PatientCity, 30), ''),
@N402_PatientState = ISNULL(LEFT(N402_PatientState, 2), ''),
@N403_PatientZipCode = ISNULL(LEFT(N403_PatientZipCode, 9), ''),
@N404_PatientCountryCode = ISNULL(LEFT(N404_PatientCountryCode, 3), ''),
@Birthdate = ISNULL(dbo.fn_DateToString_MMDDCCYY(DMG02_PatientBirthDate), ''),
@Sex = ISNULL(DMG03_PatientGenderCode, ''),
@PRelIns_01a = ISNULL(PAT01_PatientRelationshipToInsured, ''),
@InsID_01a = ISNULL(LEFT(NM109_PatientPrimaryID, 20), '')
FROM dbo.tbl837IPatient (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Claim
--now get the Subscriber stuff using the pat parent HL
SELECT @HL01_BillingProvider = HL02_ParentIDNumber,
@InsName_01a = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 25),
@PRelIns_01a = ISNULL(LEFT(SBR02_PatientRelationshipToInsured, 2), ''),
@InsID_01a = ISNULL(LEFT(NM109_SubscriberPrimaryID, 20), ''),
@GrpName_01a = ISNULL(LEFT(SBR04_GroupName, 14), ''),
@InsGrpNo_01a = ISNULL(LEFT(SBR03_GroupNumber, 17), ''),
@Remarks_010 = ISNULL(LEFT(N301_SubscriberAddressLine1, 19), ''),
@Remarks_020 = ISNULL(LEFT(N302_SubscriberAddressLine2, 24), ''),
@Remarks_030 = LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 24),
@Remarks_040 = '',
@Payer_01a = ISNULL(LEFT(NM103_PayerName, 23), ''),
@HealthPlanID_01a = ISNULL(LEFT(NM109_PayerID, 15), ''),
@Misc38_010 = LEFT(ISNULL(CASE WHEN NM103_ResponsiblePartyLastName = '' THEN NULL
ELSE NM103_ResponsiblePartyLastName
END + ', ', '') +
ISNULL(NM104_ResponsiblePartyFirstName + ' ', '') +
ISNULL(NM105_ResponsiblePartyMiddleName + ' ', '') +
ISNULL(NM107_ResponsiblePartyNameSuffix + ' ', ''), 40),
@Misc38_020 = LEFT(N301_ResponsiblePartyAddressLine1, 40),
@Misc38_030 = LEFT(N302_ResponsiblePartyAddressLine2, 40),
@Misc38_040 = LEFT(ISNULL(CASE WHEN N401_ResponsiblePartyCity = '' THEN NULL
ELSE N401_ResponsiblePartyCity
END + ', ', '') +
ISNULL(N402_ResponsiblePartyState + ' ', '') +
ISNULL(N403_ResponsiblePartyZipCode + ' ', ''), 40)
FROM dbo.tbl837ISubscriber (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Subscriber
--&CSO& override to get FL51 field for FL51a
IF @FL51Override = 'SecondaryIDNoPriority'
BEGIN
SET @HealthPlanID_01a = ''
SELECT TOP 1 @HealthPlanID_01a = LEFT(ISNULL(REF02_ReferenceID, ''), 15)
FROM dbo.tbl837IBillingProviderRef (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_BillingProvider
END
END
SELECT @str1 = '', @str2 = '', @str3 = '', @str4 = ''
--TODO: how to handle case where PayToProvider is actually the provider??
--don't think this occurs in Cleveland data however
SELECT @ProvName_010 = ISNULL(LEFT(NM103_BillingProviderName, 25), ''),
@ProvName_020 = ISNULL(LEFT(N301_BillingProviderAddressLine1, 25), ''),
@ProvName_030 = LEFT(ISNULL(RTRIM(N401_BillingProviderCity) + ', ', '') +
ISNULL(RTRIM(N402_BillingProviderState) + ' ', '') +
ISNULL(RTRIM(N403_BillingProviderZipCode), ''), 25),
@str1 = ISNULL(CASE WHEN PER03_1_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER04_1_CommunicationNumber, 12))
WHEN PER05_1_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER06_1_CommunicationNumber, 12))
WHEN PER07_1_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER08_1_CommunicationNumber, 12))
WHEN PER03_2_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER04_2_CommunicationNumber, 12))
WHEN PER05_2_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER06_2_CommunicationNumber, 12))
WHEN PER07_2_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER08_2_CommunicationNumber, 12))
END, ''),
@str2 = ISNULL(CASE WHEN PER03_1_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER04_1_CommunicationNumber, 10))
WHEN PER05_1_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER06_1_CommunicationNumber, 10))
WHEN PER07_1_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER08_1_CommunicationNumber, 10))
WHEN PER03_2_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER04_2_CommunicationNumber, 10))
WHEN PER05_2_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER06_2_CommunicationNumber, 10))
WHEN PER07_2_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER08_2_CommunicationNumber, 10))
END, ''),
@str3 = ISNULL(LEFT(N404_BillingProviderCountryCode, 3), ''),
@PayToName_010 = ISNULL(LEFT(NM103_PayToProviderName, 25), ''),
@PayToName_020 = ISNULL(LEFT(N301_PayToProviderAddressLine1, 25), ''),
@PayToName_030 = LEFT(ISNULL(RTRIM(N401_PayToProviderCity) + ', ', '') +
ISNULL(RTRIM(N402_PayToProviderState) + ' ', '') +
ISNULL(RTRIM(N403_PayToProviderZipCode), ''), 25),
@FL56_NPI = ISNULL(LEFT(NM109_BillingProviderID, 15), '')
FROM dbo.tbl837IBillingProvider (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_BillingProvider
--concatenate field
SET @ProvName_040 = REPLICATE(' ', 25)
SET @ProvName_040 = STUFF(@ProvName_040, 1, 0, ISNULL(@str1,''))
SET @ProvName_040 = STUFF(@ProvName_040, 13, 0, ISNULL(@str2, ''))
SET @ProvName_040 = STUFF(@ProvName_040, 23, 0, @str3)
SET @ProvName_040 = LEFT(@ProvName_040, 25)
--&CSO&
IF @FL38Override = 'PatientName'
BEGIN
SELECT @Misc38_010 = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 50),
@Misc38_020 = LEFT(N301_SubscriberAddressLine1, 50),
@Misc38_030 = CASE WHEN N302_SubscriberAddressLine2 IS NULL
OR N302_SubscriberAddressLine2 = ''
THEN LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 50)
ELSE LEFT(ISNULL(N302_SubscriberAddressLine2, ''), 50)
END,
@Misc38_040 = CASE WHEN N302_SubscriberAddressLine2 IS NULL
OR N302_SubscriberAddressLine2 = ''
THEN ''
ELSE LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 50)
END
FROM dbo.tbl837ISubscriber (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Subscriber
END
--reset vars
SELECT @str1 = '', @str2 = '', @str3 = ''
--LINE 1
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_010)
--FL2 PayTo Info
SET @line = STUFF(@line, 26, 0, @PayToName_010)
--FL3a PCN
SET @line = STUFF(@line, 54, 0, @PatientControlNo)
INSERT @header
VALUES (LEFT(@line, 82))
DECLARE @refid varchar(30), @qual char(3)
DECLARE ClaimRefCursor CURSOR FAST_FORWARD FOR
SELECT REF01_ReferenceIDQualifier, ISNULL(REF02_ReferenceID, '')
FROM dbo.tbl837IClaimRefs (NOLOCK)
WHERE ClaimID = @ClaimID
ORDER BY REF01_ReferenceIDQualifier DESC
OPEN ClaimRefCursor
FETCH NEXT FROM ClaimRefCursor INTO @qual, @refid
@FETCH_STATUS <> -1)
BEGIN
@FETCH_STATUS <> -2)
BEGIN
IF @qual = 'F8'
BEGIN
SET @ICNDCN_01a = LEFT(@refid, 26)
END
ELSE
BEGIN
IF @qual = 'EA' --MRN
BEGIN
SET @MedRecordNo = LEFT(@refid, 20)
END
ELSE
BEGIN
IF @qual = 'G1' --prior auth #
BEGIN
SET @TrmtAuth_01a = LEFT(@refid, 30)
END
ELSE
IF @qual = '9F' --referral number
BEGIN
IF ISNULL(@TrmtAuth_01a, '') <> ''
BEGIN
SET @TrmtAuth_01a = LEFT(@TrmtAuth_01a + '/' + LEFT(@refid, 30), 30)
END
ELSE
BEGIN
SET @TrmtAuth_01a = LEFT('/' + @refid, 30)
END
END END END
END
FETCH NEXT FROM ClaimRefCursor INTO @qual, @refid
END
CLOSE ClaimRefCursor
DEALLOCATE ClaimRefCursor
--LINE 2
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_020)
--FL2 PayTo Info
SET @line = STUFF(@line, 26, 0, @PayToName_020)
--FL3b Medical Record No
SET @line = STUFF(@line, 54, 0, @MedRecordNo)
--FL4 TypeOfBill
SET @line = STUFF(@line, 78, 0, @TypeofBill)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 3
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_030)
--FL2 PayTo Info
SET @line = STUFF(@line, 26, 0, @PayToName_030)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 4
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_040)
--FL5 FedTaxID
SET @line = STUFF(@line, 51, 0, @FedTaxNo)
--FL6 StmtCovFm
SET @line = STUFF(@line, 61, 0, @StmtCovFm)
--FL6 StmtCovThru
SET @line = STUFF(@line, 68, 0, @StmtCovThr)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 5
SET @line = REPLICATE(' ', 82)
--FL 8b, PatientNameID
SET @line = STUFF(@line, 12, 0, @NM109_PatientPrimaryID)
--FL9a, pat address
SET @line = STUFF(@line, 42, 0, @N301_PatientAddressLine1)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 6
SET @line = REPLICATE(' ', 82)
--FL 8a, PatientName
SET @line = STUFF(@line, 2, 0, @PatientName)
--FL9b, pat address
SET @line = STUFF(@line, 32, 0, @N401_PatientCity)
--FL9c, pat address
SET @line = STUFF(@line, 65, 0, @N402_PatientState)
--FL9d, pat address
SET @line = STUFF(@line, 69, 0, @N403_PatientZipCode)
--FL9e, pat address
SET @line = STUFF(@line, 80, 0, @N403_PatientZipCode)
INSERT @header
VALUES (LEFT(@line, 82))
INSERT @header
SELECT ''
--LINE 8
SET @line = REPLICATE(' ', 82)
--FL 10, Patient bday
SET @line = STUFF(@line, 1, 0, @BirthDate)
--FL11, pat sex
SET @line = STUFF(@line, 10, 0, @Sex)
--FL12, adm date
SET @line = STUFF(@line, 13, 0, @AdmDate)
--FL13, adm hour
SET @line = STUFF(@line, 20, 0, @AdmHR)
--FL14, pat type
SET @line = STUFF(@line, 23, 0, @AdmType)
--FL15, pat source
SET @line = STUFF(@line, 25, 0, @AdmSrc)
--FL16, discharge hour
SET @line = STUFF(@line, 28, 0, @DischgHR)
--FL17, pat status code
SET @line = STUFF(@line, 31, 0, @Status)
--TODO move/consolidate code
--VERY complicated ClaimInformationCodes section
--initialize variables to empty strings
SELECT @CondCode_010 = '',
@CondCode_020 = '',
@CondCode_030 = '',
@CondCode_040 = '',
@CondCode_050 = '',
@CondCode_060 = '',
@CondCode_070 = '',
@CondCode_080 = '',
@CondCode_090 = '',
@CondCode_100 = '',
@CondCode_110 = '',
@OccCode_01a = '',
@OccDate_01a = '',
@OccCode_01b = '',
@OccDate_01b = '',
@OccCode_02a = '',
@OccDate_02a = '',
@OccCode_02b = '',
@OccDate_02b = '',
@OccCode_03a = '',
@OccDate_03a = '',
@OccCode_03b = '',
@OccDate_03b = '',
@OccCode_04a = '',
@OccDate_04a = '',
@OccCode_04b = '',
@OccDate_04b = '',
@OccSpCode_05a = '',
@OccSpFm_05a = '',
@OccSpThr_05a = '',
@OccSpCode_05b = '',
@OccSpFm_05b = '',
@OccSpThr_05b = '',
@OccSpCode_05c = '',
@OccSpFm_05c = '',
@OccSpThr_05c = '',
@OccSpCode_05d = '',
@OccSpFm_05d = '',
@OccSpThr_05d = '',
@ValCdCode_01a = '',
@ValCdAmt_01a = '',
@ValCdCode_01b = '',
@ValCdAmt_01b = '',
@ValCdCode_01c = '',
@ValCdAmt_01c = '',
@ValCdCode_01d = '',
@ValCdAmt_01d = '',
@ValCdCode_02a = '',
@ValCdAmt_02a = '',
@ValCdCode_02b = '',
@ValCdAmt_02b = '',
@ValCdCode_02c = '',
@ValCdAmt_02c = '',
@ValCdCode_02d = '',
@ValCdAmt_02d = '',
@ValCdCode_03a = '',
@ValCdAmt_03a = '',
@ValCdCode_03b = '',
@ValCdAmt_03b = '',
@ValCdCode_03c = '',
@ValCdAmt_03c = '',
@ValCdCode_03d = '',
@ValCdAmt_03d = '',
@OthDiagCD_010 = '',
@OthDiagCD_020 = '',
@OthDiagCD_030 = '',
@OthDiagCD_040 = '',
@OthDiagCD_050 = '',
@OthDiagCD_060 = '',
@OthDiagCD_070 = '',
@OthDiagCD_080 = '',
@OthDiagCD_090 = '',
@OthDiagCD_100 = '',
@OthDiagCD_110 = '',
@OthDiagCD_120 = '',
@OthDiagCD_130 = '',
@OthDiagCD_140 = '',
@OthDiagCD_150 = '',
@OthDiagCD_160 = '',
@OthDiagCD_170 = '',
@PrinProcCode = '',
@PrinProcDate = '',
@OthProcDate_01a = '',
@OthProcDate_01b = '',
@OthProcDate_01c = '',
@OthProcDate_01d = '',
@OthProcDate_01e = '',
@OthProcCode_01a = '',
@OthProcCode_01b = '',
@OthProcCode_01c = '',
@OthProcCode_01d = '',
@OthProcCode_01e = ''
DECLARE @previousqual1 char(3), @lastemptycode tinyint
DECLARE
@HI01_1_CodeQualifier char(3),
@HI01_2_IndustryCode varchar(30),
@HI01_4_DateTimePeriod varchar(17),
@HI01_5_MonetaryAmount money,
@HI02_1_CodeQualifier char(3),
@HI02_2_IndustryCode varchar(30),
@HI02_4_DateTimePeriod varchar(17),
@HI02_5_MonetaryAmount money,
@HI03_1_CodeQualifier char(3),
@HI03_2_IndustryCode varchar(30),
@HI03_4_DateTimePeriod varchar(17),
@HI03_5_MonetaryAmount money,
@HI04_1_CodeQualifier char(3),
@HI04_2_IndustryCode varchar(30),
@HI04_4_DateTimePeriod varchar(17),
@HI04_5_MonetaryAmount money,
@HI05_1_CodeQualifier char(3),
@HI05_2_IndustryCode varchar(30),
@HI05_4_DateTimePeriod varchar(17),
@HI05_5_MonetaryAmount money,
@HI06_1_CodeQualifier char(3),
@HI06_2_IndustryCode varchar(30),
@HI06_4_DateTimePeriod varchar(17),
@HI06_5_MonetaryAmount money,
@HI07_1_CodeQualifier char(3),
@HI07_2_IndustryCode varchar(30),
@HI07_4_DateTimePeriod varchar(17),
@HI07_5_MonetaryAmount money,
@HI08_1_CodeQualifier char(3),
@HI08_2_IndustryCode varchar(30),
@HI08_4_DateTimePeriod varchar(17),
@HI08_5_MonetaryAmount money,
@HI09_1_CodeQualifier char(3),
@HI09_2_IndustryCode varchar(30),
@HI09_4_DateTimePeriod varchar(17),
@HI09_5_MonetaryAmount money,
@HI10_1_CodeQualifier char(3),
@HI10_2_IndustryCode varchar(30),
@HI10_4_DateTimePeriod varchar(17),
@HI10_5_MonetaryAmount money,
@HI11_1_CodeQualifier char(3),
@HI11_2_IndustryCode varchar(30),
@HI11_4_DateTimePeriod varchar(17),
@HI11_5_MonetaryAmount money,
@HI12_1_CodeQualifier char(3),
@HI12_2_IndustryCode varchar(30),
@HI12_4_DateTimePeriod varchar(17),
@HI12_5_MonetaryAmount money,
@HI01_4_DateTimePeriod2 varchar(6),
@HI02_4_DateTimePeriod2 varchar(6),
@HI03_4_DateTimePeriod2 varchar(6),
@HI04_4_DateTimePeriod2 varchar(6),
@HI05_4_DateTimePeriod2 varchar(6),
@HI06_4_DateTimePeriod2 varchar(6),
@HI07_4_DateTimePeriod2 varchar(6),
@HI08_4_DateTimePeriod2 varchar(6),
@HI09_4_DateTimePeriod2 varchar(6),
@HI10_4_DateTimePeriod2 varchar(6),
@HI11_4_DateTimePeriod2 varchar(6),
@HI12_4_DateTimePeriod2 varchar(6)
DECLARE ClaimInfoCodesCursor CURSOR FAST_FORWARD FOR
SELECT
ISNULL(HI01_1_CodeQualifier, ''), ISNULL(HI01_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI01_4_DateTimePeriod, 8)), HI01_5_MonetaryAmount,
ISNULL(HI02_1_CodeQualifier, ''), ISNULL(HI02_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI02_4_DateTimePeriod, 8)), HI02_5_MonetaryAmount,
ISNULL(HI03_1_CodeQualifier, ''), ISNULL(HI03_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI03_4_DateTimePeriod, 8)), HI03_5_MonetaryAmount,
ISNULL(HI04_1_CodeQualifier, ''), ISNULL(HI04_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI04_4_DateTimePeriod, 8)), HI04_5_MonetaryAmount,
ISNULL(HI05_1_CodeQualifier, ''), ISNULL(HI05_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI05_4_DateTimePeriod, 8)), HI05_5_MonetaryAmount,
ISNULL(HI06_1_CodeQualifier, ''), ISNULL(HI06_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI06_4_DateTimePeriod, 8)), HI06_5_MonetaryAmount,
ISNULL(HI07_1_CodeQualifier, ''), ISNULL(HI07_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI07_4_DateTimePeriod, 8)), HI07_5_MonetaryAmount,
ISNULL(HI08_1_CodeQualifier, ''), ISNULL(HI08_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI08_4_DateTimePeriod, 8)), HI08_5_MonetaryAmount,
ISNULL(HI09_1_CodeQualifier, ''), ISNULL(HI09_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI09_4_DateTimePeriod, 8)), HI09_5_MonetaryAmount,
ISNULL(HI10_1_CodeQualifier, ''), ISNULL(HI10_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI10_4_DateTimePeriod, 8)), HI10_5_MonetaryAmount,
ISNULL(HI11_1_CodeQualifier, ''), ISNULL(HI11_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI11_4_DateTimePeriod, 8)), HI11_5_MonetaryAmount,
ISNULL(HI12_1_CodeQualifier, ''), ISNULL(HI12_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI12_4_DateTimePeriod, 8)), HI12_5_MonetaryAmount,
--TODO: BUG IN ORIGINAL SPROC. This is needed for second date in RD8 parts like BI section
ISNULL(SUBSTRING(HI01_4_DateTimePeriod, 14, 2) + SUBSTRING(HI01_4_DateTimePeriod, 16, 2) + SUBSTRING(HI01_4_DateTimePeriod, 12, 2), '') AS HI01_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI02_4_DateTimePeriod, 14, 2) + SUBSTRING(HI02_4_DateTimePeriod, 16, 2) + SUBSTRING(HI02_4_DateTimePeriod, 12, 2), '') AS HI02_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI03_4_DateTimePeriod, 14, 2) + SUBSTRING(HI03_4_DateTimePeriod, 16, 2) + SUBSTRING(HI03_4_DateTimePeriod, 12, 2), '') AS HI03_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI04_4_DateTimePeriod, 14, 2) + SUBSTRING(HI04_4_DateTimePeriod, 16, 2) + SUBSTRING(HI04_4_DateTimePeriod, 12, 2), '') AS HI04_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI05_4_DateTimePeriod, 14, 2) + SUBSTRING(HI05_4_DateTimePeriod, 16, 2) + SUBSTRING(HI05_4_DateTimePeriod, 12, 2), '') AS HI05_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI06_4_DateTimePeriod, 14, 2) + SUBSTRING(HI06_4_DateTimePeriod, 16, 2) + SUBSTRING(HI06_4_DateTimePeriod, 12, 2), '') AS HI06_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI07_4_DateTimePeriod, 14, 2) + SUBSTRING(HI07_4_DateTimePeriod, 16, 2) + SUBSTRING(HI07_4_DateTimePeriod, 12, 2), '') AS HI07_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI08_4_DateTimePeriod, 14, 2) + SUBSTRING(HI08_4_DateTimePeriod, 16, 2) + SUBSTRING(HI08_4_DateTimePeriod, 12, 2), '') AS HI08_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI09_4_DateTimePeriod, 14, 2) + SUBSTRING(HI09_4_DateTimePeriod, 16, 2) + SUBSTRING(HI09_4_DateTimePeriod, 12, 2), '') AS HI09_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI10_4_DateTimePeriod, 14, 2) + SUBSTRING(HI10_4_DateTimePeriod, 16, 2) + SUBSTRING(HI10_4_DateTimePeriod, 12, 2), '') AS HI10_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI11_4_DateTimePeriod, 14, 2) + SUBSTRING(HI11_4_DateTimePeriod, 16, 2) + SUBSTRING(HI11_4_DateTimePeriod, 12, 2), '') AS HI11_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI12_4_DateTimePeriod, 14, 2) + SUBSTRING(HI12_4_DateTimePeriod, 16, 2) + SUBSTRING(HI12_4_DateTimePeriod, 12, 2), '') AS HI12_4_DateTimePeriod2
FROM dbo.tbl837IClaimInformationCodes (NOLOCK)
WHERE ClaimID = @ClaimID
ORDER BY HI01_1_CodeQualifier --TODO is this needed and does it affect things?
SELECT @previousqual1 = 'ZZZ', @i = 1
OPEN ClaimInfoCodesCursor
FETCH NEXT FROM ClaimInfoCodesCursor INTO
@HI01_1_CodeQualifier, @HI01_2_IndustryCode, @HI01_4_DateTimePeriod, @HI01_5_MonetaryAmount,
@HI02_1_CodeQualifier, @HI02_2_IndustryCode, @HI02_4_DateTimePeriod, @HI02_5_MonetaryAmount,
@HI03_1_CodeQualifier, @HI03_2_IndustryCode, @HI03_4_DateTimePeriod, @HI03_5_MonetaryAmount,
@HI04_1_CodeQualifier, @HI04_2_IndustryCode, @HI04_4_DateTimePeriod, @HI04_5_MonetaryAmount,
@HI05_1_CodeQualifier, @HI05_2_IndustryCode, @HI05_4_DateTimePeriod, @HI05_5_MonetaryAmount,
@HI06_1_CodeQualifier, @HI06_2_IndustryCode, @HI06_4_DateTimePeriod, @HI06_5_MonetaryAmount,
@HI07_1_CodeQualifier, @HI07_2_IndustryCode, @HI07_4_DateTimePeriod, @HI07_5_MonetaryAmount,
@HI08_1_CodeQualifier, @HI08_2_IndustryCode, @HI08_4_DateTimePeriod, @HI08_5_MonetaryAmount,
@HI09_1_CodeQualifier, @HI09_2_IndustryCode, @HI09_4_DateTimePeriod, @HI09_5_MonetaryAmount,
@HI10_1_CodeQualifier, @HI10_2_IndustryCode, @HI10_4_DateTimePeriod, @HI10_5_MonetaryAmount,
@HI11_1_CodeQualifier, @HI11_2_IndustryCode, @HI11_4_DateTimePeriod, @HI11_5_MonetaryAmount,
@HI12_1_CodeQualifier, @HI12_2_IndustryCode, @HI12_4_DateTimePeriod, @HI12_5_MonetaryAmount,
@HI01_4_DateTimePeriod2, @HI02_4_DateTimePeriod2, @HI03_4_DateTimePeriod2,
@HI04_4_DateTimePeriod2, @HI05_4_DateTimePeriod2, @HI06_4_DateTimePeriod2,
@HI07_4_DateTimePeriod2, @HI08_4_DateTimePeriod2, @HI09_4_DateTimePeriod2,
@HI10_4_DateTimePeriod2, @HI11_4_DateTimePeriod2, @HI12_4_DateTimePeriod2
@FETCH_STATUS <> -1)
BEGIN
@FETCH_STATUS <> -2)
BEGIN
--handle rows that can have repeat 2
IF @previousqual1 = @HI01_1_CodeQualifier
BEGIN
SET @i = @i + 1
END
ELSE
BEGIN
SET @i = 1
END
IF @HI01_1_CodeQualifier = 'BK'
BEGIN
SET @PrinDiagCD = LEFT(@HI01_2_IndustryCode, 8)
IF @HI02_1_CodeQualifier = 'BJ'
BEGIN
SET @AdmDiagCD = LEFT(@HI02_2_IndustryCode, 7)
END
IF @HI02_1_CodeQualifier = 'ZZ'
BEGIN
SET @PatVisitReasonCD = LEFT(@HI02_2_IndustryCode, 7)
END
IF @HI03_1_CodeQualifier = 'BN'
BEGIN
SET @ECode = LEFT(@HI03_2_IndustryCode, 8)
END
END
IF @HI01_1_CodeQualifier = 'BF' --2 repeats
BEGIN
IF @i = 1
BEGIN
SELECT @OthDiagCD_010 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_020 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_030 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI12_2_IndustryCode, 8)
-- set @lastemptycode used if 2 repeats
SET @lastemptycode =
CASE WHEN @OthDiagCD_010 = '' THEN 1
WHEN @OthDiagCD_020 = '' THEN 2
WHEN @OthDiagCD_030 = '' THEN 3
WHEN @OthDiagCD_040 = '' THEN 4
WHEN @OthDiagCD_050 = '' THEN 5
WHEN @OthDiagCD_060 = '' THEN 6
WHEN @OthDiagCD_070 = '' THEN 7
WHEN @OthDiagCD_080 = '' THEN 8
WHEN @OthDiagCD_090 = '' THEN 9
WHEN @OthDiagCD_100 = '' THEN 10
WHEN @OthDiagCD_110 = '' THEN 11
WHEN @OthDiagCD_120 = '' THEN 12
WHEN @OthDiagCD_130 = '' THEN 13
END
END
ELSE
BEGIN --2nd line of BF codes
IF @lastemptycode = 1 --this probably shouldn't happen, but you never know
BEGIN
SELECT @OthDiagCD_010 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_020 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_030 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 2
BEGIN
SELECT @OthDiagCD_020 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_030 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 3
BEGIN
SELECT @OthDiagCD_030 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 4
BEGIN
SELECT @OthDiagCD_040 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 5
BEGIN
SELECT @OthDiagCD_050 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 6
BEGIN
SELECT @OthDiagCD_060 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 7
BEGIN
SELECT @OthDiagCD_070 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI11_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 8
BEGIN
SELECT @OthDiagCD_080 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI10_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 9
BEGIN
SELECT @OthDiagCD_090 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI09_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 10
BEGIN
SELECT @OthDiagCD_100 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI08_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 11
BEGIN
SELECT @OthDiagCD_110 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI07_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 12
BEGIN
SELECT @OthDiagCD_120 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI06_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 13
BEGIN
SELECT @OthDiagCD_130 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI05_2_IndustryCode, 8)
END
--bunch of ends for the elses
END END END END END END END END END END END END
END --END of BF section
END
IF @HI01_1_CodeQualifier IN ('BP', 'BR')
BEGIN
SET @PrinProcCode = LEFT(@HI01_2_IndustryCode, 7)
SET @PrinProcDate = @HI01_4_DateTimePeriod
END
IF @HI01_1_CodeQualifier IN ('BO', 'BQ')
BEGIN
IF @i = 1
BEGIN
SET @OthProcCode_01a = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01a = @HI01_4_DateTimePeriod
SET @OthProcCode_01b = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01b = @HI02_4_DateTimePeriod
SET @OthProcCode_01c = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI03_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI04_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI04_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI05_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI05_4_DateTimePeriod
-- set @lastemptycode used if 2 repeats
SET @lastemptycode =
CASE WHEN @OthProcCode_01a = '' THEN 1
WHEN @OthProcCode_01b = '' THEN 2
WHEN @OthProcCode_01c = '' THEN 3
WHEN @OthProcCode_01d = '' THEN 4
WHEN @OthProcCode_01e = '' THEN 5
END
END
ELSE
BEGIN --2nd line of BO codes
IF @lastemptycode = 1 --this probably shouldn't happen, but you never know
BEGIN
SET @OthProcCode_01a = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01a = @HI01_4_DateTimePeriod
SET @OthProcCode_01b = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01b = @HI02_4_DateTimePeriod
SET @OthProcCode_01c = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI03_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI04_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI04_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI05_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI05_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 2
BEGIN
SET @OthProcCode_01b = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01b = @HI01_4_DateTimePeriod
SET @OthProcCode_01c = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI02_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI03_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI04_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI04_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 3
BEGIN
SET @OthProcCode_01c = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI01_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI02_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI03_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 4
BEGIN
SET @OthProcCode_01d = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI01_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI02_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 5
BEGIN
SET @OthProcCode_01e = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI01_4_DateTimePeriod
END
--bunch of ends for the elses
END END END END
END
END --END BO section
IF @HI01_1_CodeQualifier = 'BI'
BEGIN
IF @i = 1
BEGIN
SELECT @OccSpCode_05a = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05a = @HI01_4_DateTimePeriod,
@OccSpThr_05a = @HI01_4_DateTimePeriod2,
@OccSpCode_05b = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05b = @HI02_4_DateTimePeriod,
@OccSpThr_05b = @HI02_4_DateTimePeriod2,
@OccSpCode_05c = LEFT(@HI03_2_IndustryCode, 2),
@OccSpFm_05c = @HI03_4_DateTimePeriod,
@OccSpThr_05c = @HI03_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI04_2_IndustryCode, 2),
@OccSpFm_05d = @HI04_4_DateTimePeriod,
@OccSpThr_05d = @HI04_4_DateTimePeriod2
-- set @lastemptycode used if 2 repeats
SET @lastemptycode =
CASE WHEN @OccSpCode_05a = '' THEN 1
WHEN @OccSpCode_05b = '' THEN 2
WHEN @OccSpCode_05c = '' THEN 3
WHEN @OccSpCode_05d = '' THEN 4
END
END
ELSE
BEGIN --2nd line of BO codes
IF @lastemptycode = 1 --this probably shouldn't happen, but you never know
BEGIN
SELECT @OccSpCode_05a = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05a = @HI01_4_DateTimePeriod,
@OccSpThr_05a = @HI01_4_DateTimePeriod2,
@OccSpCode_05b = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05b = @HI02_4_DateTimePeriod,
@OccSpThr_05b = @HI02_4_DateTimePeriod2,
@OccSpCode_05c = LEFT(@HI03_2_IndustryCode, 2),
@OccSpFm_05c = @HI03_4_DateTimePeriod,
@OccSpThr_05c = @HI03_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI04_2_IndustryCode, 2),
@OccSpFm_05d = @HI04_4_DateTimePeriod,
@OccSpThr_05d = @HI04_4_DateTimePeriod2
END
ELSE
BEGIN
IF @lastemptycode = 2
BEGIN
SELECT @OccSpCode_05b = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05b = @HI01_4_DateTimePeriod,
@OccSpThr_05b = @HI01_4_DateTimePeriod2,
@OccSpCode_05c = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05c = @HI02_4_DateTimePeriod,
@OccSpThr_05c = @HI02_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI03_2_IndustryCode, 2),
@OccSpFm_05d = @HI03_4_DateTimePeriod,
@OccSpThr_05d = @HI03_4_DateTimePeriod2
END
ELSE
BEGIN
IF @lastemptycode = 3
BEGIN
SELECT @OccSpCode_05c = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05c = @HI01_4_DateTimePeriod,
@OccSpThr_05c = @HI01_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05d = @HI02_4_DateTimePeriod,
@OccSpThr_05d = @HI02_4_DateTimePeriod2
END
ELSE
BEGIN
IF @lastemptycode = 4
BEGIN
SELECT @OccSpCode_05d = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05d = @HI01_4_DateTimePeriod,
@OccSpThr_05d = @HI01_4_DateTimePeriod2
END END END END
END
END --END BI section
IF @HI01_1_CodeQualifier = 'BH'
BEGIN
IF @i = 1
BEGIN
SELECT @OccCode_01a = LEFT(@HI01_2_IndustryCode, 2),
@OccDate_01a = @HI01_4_DateTimePeriod,
@OccCode_01b = LEFT(@HI02_2_IndustryCode, 2),
@OccDate_01b = @HI02_4_DateTimePeriod,
@OccCode_02a = LEFT(@HI03_2_IndustryCode, 2),
@OccDate_02a = @HI03_4_DateTimePeriod,
@OccCode_02b = LEFT(@HI04_2_IndustryCode, 2),
@OccDate_02b = @HI04_4_DateTimePeriod,
@OccCode_03a = LEFT(@HI05_2_IndustryCode, 2),
@OccDate_03a = @HI05_4_DateTimePeriod,
@OccCode_03b = LEFT(@HI06_2_IndustryCode, 2),
@OccDate_03b = @HI06_4_Da
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID('dbo.rcms_837I_To_UB04Document') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.rcms_837I_To_UB04Document
IF OBJECT_ID('dbo.rcms_837I_To_UB04Document') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.rcms_837I_To_UB04Document >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.rcms_837I_To_UB04Document >>>'
END
go
CREATE PROC dbo.rcms_837I_To_UB04Document ( @ClaimID int)
as
/***********************************************************************************************
* Name: rcms_837I_To_UB04Document
* Author: Kevin Boles
* Date: 3/09/2007
*
* Output:
*
* Desc:
* Revision:
*
***********************************************************************************************/
SET NOCOUNT ON
DECLARE
@ClaimTypeKey int, @ClaimCode varchar(2),
@InsCompanyKey int, @ProvName_010 varchar(50),
@ProvName_020 varchar(50), @ProvName_030 varchar(50),
@ProvName_040 varchar(50), @Misc2 varchar(30),
@PayToName_010 varchar(25), @PayToName_020 varchar(25),
@PayToName_030 varchar(25),
@PatientControlNo varchar(38), @PatientControlNo_Print varchar(38),
@TypeofBill char(4), @FedTaxNo varchar(11),
@StmtCovFm varchar(10), @StmtCovThr varchar(10),
@CoveredDays varchar(6), @NonCovDays varchar(6),
@CoinsDays varchar(6), @LifeResDays varchar(6),
@Misc11 varchar(15), @PatientName varchar(50),
@PatientAddress varchar(51), @Birthdate varchar(10),
@Sex char(2), @MaritalStatus char(2),
@AdmDate varchar(10), @AdmHR varchar(8),
@AdmType char(1), @AdmSrc char(3),
@DischgHR varchar(8), @Status char(3),
@MedRecordNo varchar(20), @CondCode_010 char(3),
@CondCode_020 char(3), @CondCode_030 char(3),
@CondCode_040 char(3), @CondCode_050 char(3),
@CondCode_060 char(3), @CondCode_070 char(3),
@CondCode_080 char(3),
@CondCode_090 char(3),
@CondCode_100 char(3),
@CondCode_110 char(3),
@Misc31 varchar(10), @OccCode_01a char(3),
@OccDate_01a varchar(10), @OccCode_01b char(3),
@OccDate_01b varchar(10), @OccCode_02a char(3),
@OccDate_02a varchar(10), @OccCode_02b char(3),
@OccDate_02b varchar(10), @OccCode_03a char(3),
@OccDate_03a varchar(10), @OccCode_03b char(3),
@OccDate_03b varchar(10), @OccCode_04a char(3),
@OccDate_04a varchar(10), @OccCode_04b char(3),
@OccDate_04b varchar(10), @OccSpCode_05a char(3),
@OccSpFm_05a varchar(10), @OccSpThr_05a varchar(10),
@OccSpCode_05b char(3), @OccSpFm_05b varchar(10),
@OccSpThr_05b varchar(10),
@OccSpCode_05c char(3),
@OccSpFm_05c varchar(10),
@OccSpThr_05c varchar(10),
@OccSpCode_05d char(3),
@OccSpFm_05d varchar(10),
@OccSpThr_05d varchar(10),
@ICNDCN_01a varchar(26),
@ICNDCN_01b varchar(26), @ICNDCN_01c varchar(26),
@Misc38_010 varchar(50), @Misc38_020 varchar(50),
@Misc38_030 varchar(50), @Misc38_040 varchar(50),
@Misc38_050 varchar(50), @ValCdCode_01a char(2),
@ValCdAmt_01a varchar(18), @ValCdCode_01b char(2),
@ValCdAmt_01b varchar(18), @ValCdCode_01c char(2),
@ValCdAmt_01c varchar(18), @ValCdCode_01d char(2),
@ValCdAmt_01d varchar(18), @ValCdCode_02a char(2),
@ValCdAmt_02a varchar(18), @ValCdCode_02b char(2),
@ValCdAmt_02b varchar(18), @ValCdCode_02c char(2),
@ValCdAmt_02c varchar(18), @ValCdCode_02d char(2),
@ValCdAmt_02d varchar(18), @ValCdCode_03a char(2),
@ValCdAmt_03a varchar(18), @ValCdCode_03b char(2),
@ValCdAmt_03b varchar(18), @ValCdCode_03c char(2),
@ValCdAmt_03c varchar(18), @ValCdCode_03d char(2),
@ValCdAmt_03d varchar(18), @TotServUnits varchar(8),
@TotCharges decimal(19,2), @TotNChanges decimal(19,2),
@Payer_01a varchar(23), @Payer_01b varchar(23),
@Payer_01c varchar(23), @HealthPlanID_01a varchar(15),
@HealthPlanID_01b varchar(15), @HealthPlanID_01c varchar(15),
@RelInfo_01a char(1), @RelInfo_01b char(1),
@RelInfo_01c char(1), @AsgBen_01a char(1),
@AsgBen_01b char(1), @AsgBen_01c char(1),
@PrPymts_01a decimal(19,2), @PrPymts_01b decimal(19,2),
@PrPymts_01c decimal(19,2), @TotPrPymt decimal(19,2),
@EstAmtDue_01a decimal(19,2), @EstAmtDue_01b decimal(19,2),
@EstAmtDue_01c decimal(19,2), @TotEstAmtDue decimal(19,2), --need calculation for this??
@Misc56_01a varchar(15), @Misc56_01b varchar(15),
@Misc56_01c varchar(15), @Misc56_01d varchar(15),
@Misc56_01e varchar(15), @InsName_01a varchar(25),
@InsName_01b varchar(25), @InsName_01c varchar(25),
@PRelIns_01a char(2), @PRelIns_01b char(2),
@PRelIns_01c char(2), @InsID_01a varchar(20),
@InsID_01b varchar(20), @InsID_01c varchar(20),
@GrpName_01a varchar(14), @GrpName_01b varchar(14),
@GrpName_01c varchar(14), @InsGrpNo_01a varchar(17),
@InsGrpNo_01b varchar(17), @InsGrpNo_01c varchar(17),
@TrmtAuth_01a varchar(19), @TrmtAuth_01b varchar(19),
@TrmtAuth_01c varchar(19), @EmpSt_01a char(2),
@EmpSt_01b char(2), @EmpSt_01c char(2),
@EmpName_01a varchar(25), @EmpName_01b varchar(25),
@EmpName_01c varchar(25), @EmpLoc_01a varchar(36),
@EmpLoc_01b varchar(36), @EmpLoc_01c varchar(36),
@PrinDiagCD varchar(8), @OthDiagCD_010 varchar(8),
@OthDiagCD_020 varchar(8), @OthDiagCD_030 varchar(8),
@OthDiagCD_040 varchar(8), @OthDiagCD_050 varchar(8),
@OthDiagCD_060 varchar(8), @OthDiagCD_070 varchar(8),
@OthDiagCD_080 varchar(8), @OthDiagCD_090 varchar(8),
@OthDiagCD_100 varchar(8), @OthDiagCD_110 varchar(8),
@OthDiagCD_120 varchar(8), @OthDiagCD_130 varchar(8),
@OthDiagCD_140 varchar(8), @OthDiagCD_150 varchar(8),
@OthDiagCD_160 varchar(8), @OthDiagCD_170 varchar(8),
@AdmDiagCD varchar(7), @PatVisitReasonCD varchar(7),
@ECode varchar(8), @Misc78 char(4),
@PC char(2), @PrinProcCode varchar(7),
@PrinProcDate varchar(6), @OthProcCode_01a varchar(7),
@OthProcDate_01a varchar(6), @OthProcCode_01b varchar(7),
@OthProcDate_01b varchar(6), @OthProcCode_01c varchar(7),
@OthProcDate_01c varchar(6), @OthProcCode_01d varchar(7),
@OthProcDate_01d varchar(6), @OthProcCode_01e varchar(7),
@OthProcDate_01e varchar(6),
@AttPhysNPI varchar(11), @AttPhysSecQual char(2),
@AttPhysSecID varchar(9), @AttPhysLastName varchar(16),
@AttPhysFirstName varchar(12),
@OperPhysNPI varchar(11), @OperPhysSecQual char(2),
@OperPhysSecID varchar(9), @OperPhysLastName varchar(16),
@OperPhysFirstName varchar(12),
@OtherPhysNPI varchar(11), @OtherPhysSecQual char(2),
@OtherPhysSecID varchar(9), @OtherPhysLastName varchar(16),
@OtherPhysFirstName varchar(12),
@Remarks_010 varchar(19),
@Remarks_020 varchar(24), @Remarks_030 varchar(24),
@Remarks_040 varchar(24),
@ProvRep varchar(24),
@RepDate varchar(10), @ATBBalance decimal(19,2),
@PrimarySecondaryCode varchar(1), @PatientLastName varchar(50),
@PatientFirstName varchar(20), @PatientMiddleName varchar(20),
@MedicarePartB smallint, @ProcessedCount int,
@ATBDate datetime, @GroupKey int,
@ProcessedFlag tinyint, @LastProcessed datetime,
@DateAdded datetime, @Misc57 varchar(50),
@AppendLine66 varchar(82), @InsCompany varchar(35)
DECLARE @NM109_PatientPrimaryID varchar(80),
@N301_PatientAddressLine1 varchar(55),
@N401_PatientCity varchar(30),
@N402_PatientState char(2),
@N403_PatientZipCode varchar(15),
@N404_PatientCountryCode char(3)
DECLARE @str1 varchar(78), @str2 varchar(78), @str3 varchar(78), @str4 varchar(78)
DECLARE @FL84Override varchar(255)
DECLARE @FL38Override varchar(255)
DECLARE @FL51Override varchar(255)
DECLARE @Section50counter tinyint, --1=A, 2=B, 3=C line
@Section58Counter tinyint --1=A, 2=B, 3=C line
DECLARE @pageOutput TABLE (line varchar(82))
DECLARE @ServiceLineMaxCount smallint, @totalLineNoncoveredCharges decimal(19,2),
@ServiceLinesOutput smallint, @PageCounter tinyint, @TotalPageCount tinyint,
@rowcount int, @CreationDate char(6), @totalLineCharges decimal(19,2)
DECLARE @FL56_NPI varchar(15)
DECLARE @header TABLE (linedata varchar(82))
DECLARE @service TABLE (linedata varchar(82))
DECLARE @footer TABLE (linedata varchar(82))
DECLARE @line varchar(82)
--TOP OF PAGE STUFF?? Mike, what goes here?
--for now, just blank lines
--INSERT @header
--VALUES ('0000000001111111111222222222233333333334444444444555555555566666666667777777777888')
INSERT @header
VALUES ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012')
DECLARE @TransactionSetID int, @HL01_Claim int, --@ClaimID int,
@PatientFlag char(1), --'P' for tbl837IPatient, 'S' for tbl837ISubscriber
@HL01_BillingProvider int, @HL01_Subscriber int,
@HL01_Patient int, @i tinyint
--Client Specific Override variable gathering section &CSO&
/* current allowable values for this are:
PatientName: put patientname and address in FL38
*/
SELECT @FL38Override = ItemValue
FROM dbo.tblClientSettings (NOLOCK)
WHERE ItemName = '837I_UB92_FL38Override'
/* current allowable values for this are:
SecondaryIDNoPriority: uses tbl837IBillingProviderRef for A and tbl837IOtherSubscriberPayerRef for B and C
with no priority for the qualifier
*/
SELECT @FL51Override = ItemValue
FROM dbo.tblClientSettings (NOLOCK)
WHERE ItemName = '837I_UB92_FL51Override'
/* current allowable values for this are:
50APayerName_and_Address: uses name and address for the FL50A payer
*/
SELECT @FL84Override = ItemValue
FROM dbo.tblClientSettings (NOLOCK)
WHERE ItemName = '837I_UB92_FL84Override'
SET @MaritalStatus = '' --hard coded to empty, since 837I doesn't have this field
--Claim stuff
SELECT @TransactionSetID = TransactionSetID,
@HL01_Claim = HL01_IDNumber,
@PatientControlNo_Print = ISNULL(CLM01_PatientControlNumber_837I, ''), --are these backwards?
@PatientControlNo = ISNULL(CLM01_PatientControlNumber, ''),
@TypeofBill = ISNULL(CLM05_1_FacilityTypeCode, '') + ISNULL(CLM05_3_ClaimFrequencyCode, ''),
@StmtCovFm = ISNULL(dbo.fn_DateToString_MMDDYY(DTP03_StatementStartDate), ''),
@StmtCovThr = ISNULL(dbo.fn_DateToString_MMDDYY(DTP03_StatementEndDate), ''),
@DischgHR = ISNULL(LEFT(CASE WHEN DTP03_DischargeHour <> '' THEN DTP03_DischargeHour ELSE '0' END, 2), ''),
@TotCharges = ISNULL(CAST(CLM02_TotalClaimCharges as decimal(19,2)), 0.00),
@AdmDate = ISNULL(dbo.fn_DateToString_MMDDYY(DTP03_AdmissionDate), ''),
@AdmHR = ISNULL(LEFT(CONVERT(varchar(8), DTP03_AdmissionDate, 8), 2), ''),
@AdmType = ISNULL(CL101_AdmissionTypeCode, ''),
@AdmSrc = ISNULL(CL102_AdmissionSourceCode, ''),
@Status = ISNULL(CL103_PatientStatusCode, ''),
@RelInfo_01a = ISNULL(LEFT(CLM09_ReleaseOfInformationCode, 1), ''),
@AsgBen_01a = ISNULL(LEFT(CLM08_AssignmentofBenefitsIndicator, 1), '')
FROM dbo.tbl837IClaim (NOLOCK)
WHERE ClaimID = @ClaimID
SELECT @HL01_BillingProvider = NULL
--Try to get Subscriber stuff rom Claim HL
SELECT @HL01_BillingProvider = HL02_ParentIDNumber,
@InsName_01a = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 25),
@PRelIns_01a = ISNULL(LEFT(SBR02_PatientRelationshipToInsured, 2), ''),
@InsID_01a = ISNULL(LEFT(NM109_SubscriberPrimaryID, 20), ''),
@GrpName_01a = ISNULL(LEFT(SBR04_GroupName, 14), ''),
@InsGrpNo_01a = ISNULL(LEFT(SBR03_GroupNumber, 17), ''),
@PatientName = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 29),
@NM109_PatientPrimaryID = ISNULL(LEFT(NM109_SubscriberPrimaryID, 19), ''),
@N301_PatientAddressLine1 = ISNULL(LEFT(N301_SubscriberAddressLine1, 40), ''),
@N401_PatientCity = ISNULL(LEFT(N401_SubscriberCity, 30), ''),
@N402_PatientState = ISNULL(LEFT(N402_SubscriberState, 2), ''),
@N403_PatientZipCode = ISNULL(LEFT(N403_SubscriberZipCode, 9), ''),
@N404_PatientCountryCode = ISNULL(LEFT(N404_SubscriberCountryCode, 3), ''),
@Birthdate = ISNULL(dbo.fn_DateToString_MMDDCCYY(DMG02_SubscriberBirthDate), ''),
@Sex = ISNULL(DMG03_SubscriberGenderCode, ''),
@Remarks_010 = ISNULL(LEFT(N301_SubscriberAddressLine1, 19), ''),
@Remarks_020 = ISNULL(LEFT(N302_SubscriberAddressLine2, 24), ''),
@Remarks_030 = LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 24),
@Remarks_040 = '',
@Payer_01a = ISNULL(LEFT(NM103_PayerName, 23), ''),
@HealthPlanID_01a = ISNULL(LEFT(NM109_PayerID, 15), ''),
@Misc38_010 = LEFT(ISNULL(CASE WHEN NM103_ResponsiblePartyLastName = '' THEN NULL
ELSE NM103_ResponsiblePartyLastName
END + ', ', '') +
ISNULL(NM104_ResponsiblePartyFirstName + ' ', '') +
ISNULL(NM105_ResponsiblePartyMiddleName + ' ', '') +
ISNULL(NM107_ResponsiblePartyNameSuffix + ' ', ''), 40),
@Misc38_020 = ISNULL(LEFT(N301_ResponsiblePartyAddressLine1, 40), ''),
@Misc38_030 = ISNULL(LEFT(N302_ResponsiblePartyAddressLine2, 40), ''),
@Misc38_040 = LEFT(ISNULL(CASE WHEN N401_ResponsiblePartyCity = '' THEN NULL
ELSE N401_ResponsiblePartyCity
END + ', ', '') +
ISNULL(N402_ResponsiblePartyState + ' ', '') +
ISNULL(N403_ResponsiblePartyZipCode + ' ', ''), 40),
@HL01_Subscriber = HL01_IDNumber
FROM dbo.tbl837ISubscriber (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Claim
IF @HL01_BillingProvider IS NOT NULL
BEGIN
--Patient IS Subscriber
SET @PatientFlag = 'S'
--&CSO& override to get FL51 field for FL51a
IF @FL51Override = 'SecondaryIDNoPriority'
BEGIN
SET @HealthPlanID_01a = ''
SELECT TOP 1 @HealthPlanID_01a = LEFT(ISNULL(REF02_ReferenceID, ''), 15)
FROM dbo.tbl837IBillingProviderRef (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_BillingProvider
END
END
ELSE
BEGIN
--Patient IS tbl837IPatient
SET @PatientFlag = 'P'
--first get the patient stuff, including the subscriber HL
SELECT @HL01_Subscriber = HL02_ParentIDNumber,
@PatientName = LEFT(ISNULL(CASE WHEN NM103_PatientLastName = '' THEN NULL
ELSE NM103_PatientLastName
END + ', ', '') +
ISNULL(NM104_PatientFirstName + ' ', '') +
ISNULL(NM105_PatientMiddleName + ' ', '') +
ISNULL(NM107_PatientNameSuffix + ' ', ''), 29),
@NM109_PatientPrimaryID = ISNULL(LEFT(NM109_PatientPrimaryID, 19), ''),
@N301_PatientAddressLine1 = ISNULL(LEFT(N301_PatientAddressLine1, 40), ''),
@N401_PatientCity = ISNULL(LEFT(N401_PatientCity, 30), ''),
@N402_PatientState = ISNULL(LEFT(N402_PatientState, 2), ''),
@N403_PatientZipCode = ISNULL(LEFT(N403_PatientZipCode, 9), ''),
@N404_PatientCountryCode = ISNULL(LEFT(N404_PatientCountryCode, 3), ''),
@Birthdate = ISNULL(dbo.fn_DateToString_MMDDCCYY(DMG02_PatientBirthDate), ''),
@Sex = ISNULL(DMG03_PatientGenderCode, ''),
@PRelIns_01a = ISNULL(PAT01_PatientRelationshipToInsured, ''),
@InsID_01a = ISNULL(LEFT(NM109_PatientPrimaryID, 20), '')
FROM dbo.tbl837IPatient (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Claim
--now get the Subscriber stuff using the pat parent HL
SELECT @HL01_BillingProvider = HL02_ParentIDNumber,
@InsName_01a = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 25),
@PRelIns_01a = ISNULL(LEFT(SBR02_PatientRelationshipToInsured, 2), ''),
@InsID_01a = ISNULL(LEFT(NM109_SubscriberPrimaryID, 20), ''),
@GrpName_01a = ISNULL(LEFT(SBR04_GroupName, 14), ''),
@InsGrpNo_01a = ISNULL(LEFT(SBR03_GroupNumber, 17), ''),
@Remarks_010 = ISNULL(LEFT(N301_SubscriberAddressLine1, 19), ''),
@Remarks_020 = ISNULL(LEFT(N302_SubscriberAddressLine2, 24), ''),
@Remarks_030 = LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 24),
@Remarks_040 = '',
@Payer_01a = ISNULL(LEFT(NM103_PayerName, 23), ''),
@HealthPlanID_01a = ISNULL(LEFT(NM109_PayerID, 15), ''),
@Misc38_010 = LEFT(ISNULL(CASE WHEN NM103_ResponsiblePartyLastName = '' THEN NULL
ELSE NM103_ResponsiblePartyLastName
END + ', ', '') +
ISNULL(NM104_ResponsiblePartyFirstName + ' ', '') +
ISNULL(NM105_ResponsiblePartyMiddleName + ' ', '') +
ISNULL(NM107_ResponsiblePartyNameSuffix + ' ', ''), 40),
@Misc38_020 = LEFT(N301_ResponsiblePartyAddressLine1, 40),
@Misc38_030 = LEFT(N302_ResponsiblePartyAddressLine2, 40),
@Misc38_040 = LEFT(ISNULL(CASE WHEN N401_ResponsiblePartyCity = '' THEN NULL
ELSE N401_ResponsiblePartyCity
END + ', ', '') +
ISNULL(N402_ResponsiblePartyState + ' ', '') +
ISNULL(N403_ResponsiblePartyZipCode + ' ', ''), 40)
FROM dbo.tbl837ISubscriber (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Subscriber
--&CSO& override to get FL51 field for FL51a
IF @FL51Override = 'SecondaryIDNoPriority'
BEGIN
SET @HealthPlanID_01a = ''
SELECT TOP 1 @HealthPlanID_01a = LEFT(ISNULL(REF02_ReferenceID, ''), 15)
FROM dbo.tbl837IBillingProviderRef (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_BillingProvider
END
END
SELECT @str1 = '', @str2 = '', @str3 = '', @str4 = ''
--TODO: how to handle case where PayToProvider is actually the provider??
--don't think this occurs in Cleveland data however
SELECT @ProvName_010 = ISNULL(LEFT(NM103_BillingProviderName, 25), ''),
@ProvName_020 = ISNULL(LEFT(N301_BillingProviderAddressLine1, 25), ''),
@ProvName_030 = LEFT(ISNULL(RTRIM(N401_BillingProviderCity) + ', ', '') +
ISNULL(RTRIM(N402_BillingProviderState) + ' ', '') +
ISNULL(RTRIM(N403_BillingProviderZipCode), ''), 25),
@str1 = ISNULL(CASE WHEN PER03_1_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER04_1_CommunicationNumber, 12))
WHEN PER05_1_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER06_1_CommunicationNumber, 12))
WHEN PER07_1_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER08_1_CommunicationNumber, 12))
WHEN PER03_2_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER04_2_CommunicationNumber, 12))
WHEN PER05_2_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER06_2_CommunicationNumber, 12))
WHEN PER07_2_CommunicationQualifier = 'TE'
THEN RTRIM(LEFT(PER08_2_CommunicationNumber, 12))
END, ''),
@str2 = ISNULL(CASE WHEN PER03_1_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER04_1_CommunicationNumber, 10))
WHEN PER05_1_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER06_1_CommunicationNumber, 10))
WHEN PER07_1_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER08_1_CommunicationNumber, 10))
WHEN PER03_2_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER04_2_CommunicationNumber, 10))
WHEN PER05_2_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER06_2_CommunicationNumber, 10))
WHEN PER07_2_CommunicationQualifier = 'FX'
THEN RTRIM(LEFT(PER08_2_CommunicationNumber, 10))
END, ''),
@str3 = ISNULL(LEFT(N404_BillingProviderCountryCode, 3), ''),
@PayToName_010 = ISNULL(LEFT(NM103_PayToProviderName, 25), ''),
@PayToName_020 = ISNULL(LEFT(N301_PayToProviderAddressLine1, 25), ''),
@PayToName_030 = LEFT(ISNULL(RTRIM(N401_PayToProviderCity) + ', ', '') +
ISNULL(RTRIM(N402_PayToProviderState) + ' ', '') +
ISNULL(RTRIM(N403_PayToProviderZipCode), ''), 25),
@FL56_NPI = ISNULL(LEFT(NM109_BillingProviderID, 15), '')
FROM dbo.tbl837IBillingProvider (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_BillingProvider
--concatenate field
SET @ProvName_040 = REPLICATE(' ', 25)
SET @ProvName_040 = STUFF(@ProvName_040, 1, 0, ISNULL(@str1,''))
SET @ProvName_040 = STUFF(@ProvName_040, 13, 0, ISNULL(@str2, ''))
SET @ProvName_040 = STUFF(@ProvName_040, 23, 0, @str3)
SET @ProvName_040 = LEFT(@ProvName_040, 25)
--&CSO&
IF @FL38Override = 'PatientName'
BEGIN
SELECT @Misc38_010 = LEFT(ISNULL(CASE WHEN NM103_SubscriberLastName = '' THEN NULL
ELSE NM103_SubscriberLastName
END + ', ', '') +
ISNULL(NM104_SubscriberFirstName + ' ', '') +
ISNULL(NM105_SubscriberMiddleName + ' ', '') +
ISNULL(NM107_SubscriberNameSuffix + ' ', ''), 50),
@Misc38_020 = LEFT(N301_SubscriberAddressLine1, 50),
@Misc38_030 = CASE WHEN N302_SubscriberAddressLine2 IS NULL
OR N302_SubscriberAddressLine2 = ''
THEN LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 50)
ELSE LEFT(ISNULL(N302_SubscriberAddressLine2, ''), 50)
END,
@Misc38_040 = CASE WHEN N302_SubscriberAddressLine2 IS NULL
OR N302_SubscriberAddressLine2 = ''
THEN ''
ELSE LEFT(ISNULL(CASE WHEN N401_SubscriberCity = '' THEN NULL
ELSE N401_SubscriberCity
END + ', ', '') +
ISNULL(N402_SubscriberState + ' ', '') +
ISNULL(N403_SubscriberZipCode + ' ', ''), 50)
END
FROM dbo.tbl837ISubscriber (NOLOCK)
WHERE TransactionSetID = @TransactionSetID
AND HL01_IDNumber = @HL01_Subscriber
END
--reset vars
SELECT @str1 = '', @str2 = '', @str3 = ''
--LINE 1
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_010)
--FL2 PayTo Info
SET @line = STUFF(@line, 26, 0, @PayToName_010)
--FL3a PCN
SET @line = STUFF(@line, 54, 0, @PatientControlNo)
INSERT @header
VALUES (LEFT(@line, 82))
DECLARE @refid varchar(30), @qual char(3)
DECLARE ClaimRefCursor CURSOR FAST_FORWARD FOR
SELECT REF01_ReferenceIDQualifier, ISNULL(REF02_ReferenceID, '')
FROM dbo.tbl837IClaimRefs (NOLOCK)
WHERE ClaimID = @ClaimID
ORDER BY REF01_ReferenceIDQualifier DESC
OPEN ClaimRefCursor
FETCH NEXT FROM ClaimRefCursor INTO @qual, @refid
@FETCH_STATUS <> -1)
BEGIN
@FETCH_STATUS <> -2)
BEGIN
IF @qual = 'F8'
BEGIN
SET @ICNDCN_01a = LEFT(@refid, 26)
END
ELSE
BEGIN
IF @qual = 'EA' --MRN
BEGIN
SET @MedRecordNo = LEFT(@refid, 20)
END
ELSE
BEGIN
IF @qual = 'G1' --prior auth #
BEGIN
SET @TrmtAuth_01a = LEFT(@refid, 30)
END
ELSE
IF @qual = '9F' --referral number
BEGIN
IF ISNULL(@TrmtAuth_01a, '') <> ''
BEGIN
SET @TrmtAuth_01a = LEFT(@TrmtAuth_01a + '/' + LEFT(@refid, 30), 30)
END
ELSE
BEGIN
SET @TrmtAuth_01a = LEFT('/' + @refid, 30)
END
END END END
END
FETCH NEXT FROM ClaimRefCursor INTO @qual, @refid
END
CLOSE ClaimRefCursor
DEALLOCATE ClaimRefCursor
--LINE 2
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_020)
--FL2 PayTo Info
SET @line = STUFF(@line, 26, 0, @PayToName_020)
--FL3b Medical Record No
SET @line = STUFF(@line, 54, 0, @MedRecordNo)
--FL4 TypeOfBill
SET @line = STUFF(@line, 78, 0, @TypeofBill)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 3
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_030)
--FL2 PayTo Info
SET @line = STUFF(@line, 26, 0, @PayToName_030)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 4
SET @line = REPLICATE(' ', 82)
--FL 1, Provider Info
SET @line = STUFF(@line, 1, 0, @ProvName_040)
--FL5 FedTaxID
SET @line = STUFF(@line, 51, 0, @FedTaxNo)
--FL6 StmtCovFm
SET @line = STUFF(@line, 61, 0, @StmtCovFm)
--FL6 StmtCovThru
SET @line = STUFF(@line, 68, 0, @StmtCovThr)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 5
SET @line = REPLICATE(' ', 82)
--FL 8b, PatientNameID
SET @line = STUFF(@line, 12, 0, @NM109_PatientPrimaryID)
--FL9a, pat address
SET @line = STUFF(@line, 42, 0, @N301_PatientAddressLine1)
INSERT @header
VALUES (LEFT(@line, 82))
--LINE 6
SET @line = REPLICATE(' ', 82)
--FL 8a, PatientName
SET @line = STUFF(@line, 2, 0, @PatientName)
--FL9b, pat address
SET @line = STUFF(@line, 32, 0, @N401_PatientCity)
--FL9c, pat address
SET @line = STUFF(@line, 65, 0, @N402_PatientState)
--FL9d, pat address
SET @line = STUFF(@line, 69, 0, @N403_PatientZipCode)
--FL9e, pat address
SET @line = STUFF(@line, 80, 0, @N403_PatientZipCode)
INSERT @header
VALUES (LEFT(@line, 82))
INSERT @header
SELECT ''
--LINE 8
SET @line = REPLICATE(' ', 82)
--FL 10, Patient bday
SET @line = STUFF(@line, 1, 0, @BirthDate)
--FL11, pat sex
SET @line = STUFF(@line, 10, 0, @Sex)
--FL12, adm date
SET @line = STUFF(@line, 13, 0, @AdmDate)
--FL13, adm hour
SET @line = STUFF(@line, 20, 0, @AdmHR)
--FL14, pat type
SET @line = STUFF(@line, 23, 0, @AdmType)
--FL15, pat source
SET @line = STUFF(@line, 25, 0, @AdmSrc)
--FL16, discharge hour
SET @line = STUFF(@line, 28, 0, @DischgHR)
--FL17, pat status code
SET @line = STUFF(@line, 31, 0, @Status)
--TODO move/consolidate code
--VERY complicated ClaimInformationCodes section
--initialize variables to empty strings
SELECT @CondCode_010 = '',
@CondCode_020 = '',
@CondCode_030 = '',
@CondCode_040 = '',
@CondCode_050 = '',
@CondCode_060 = '',
@CondCode_070 = '',
@CondCode_080 = '',
@CondCode_090 = '',
@CondCode_100 = '',
@CondCode_110 = '',
@OccCode_01a = '',
@OccDate_01a = '',
@OccCode_01b = '',
@OccDate_01b = '',
@OccCode_02a = '',
@OccDate_02a = '',
@OccCode_02b = '',
@OccDate_02b = '',
@OccCode_03a = '',
@OccDate_03a = '',
@OccCode_03b = '',
@OccDate_03b = '',
@OccCode_04a = '',
@OccDate_04a = '',
@OccCode_04b = '',
@OccDate_04b = '',
@OccSpCode_05a = '',
@OccSpFm_05a = '',
@OccSpThr_05a = '',
@OccSpCode_05b = '',
@OccSpFm_05b = '',
@OccSpThr_05b = '',
@OccSpCode_05c = '',
@OccSpFm_05c = '',
@OccSpThr_05c = '',
@OccSpCode_05d = '',
@OccSpFm_05d = '',
@OccSpThr_05d = '',
@ValCdCode_01a = '',
@ValCdAmt_01a = '',
@ValCdCode_01b = '',
@ValCdAmt_01b = '',
@ValCdCode_01c = '',
@ValCdAmt_01c = '',
@ValCdCode_01d = '',
@ValCdAmt_01d = '',
@ValCdCode_02a = '',
@ValCdAmt_02a = '',
@ValCdCode_02b = '',
@ValCdAmt_02b = '',
@ValCdCode_02c = '',
@ValCdAmt_02c = '',
@ValCdCode_02d = '',
@ValCdAmt_02d = '',
@ValCdCode_03a = '',
@ValCdAmt_03a = '',
@ValCdCode_03b = '',
@ValCdAmt_03b = '',
@ValCdCode_03c = '',
@ValCdAmt_03c = '',
@ValCdCode_03d = '',
@ValCdAmt_03d = '',
@OthDiagCD_010 = '',
@OthDiagCD_020 = '',
@OthDiagCD_030 = '',
@OthDiagCD_040 = '',
@OthDiagCD_050 = '',
@OthDiagCD_060 = '',
@OthDiagCD_070 = '',
@OthDiagCD_080 = '',
@OthDiagCD_090 = '',
@OthDiagCD_100 = '',
@OthDiagCD_110 = '',
@OthDiagCD_120 = '',
@OthDiagCD_130 = '',
@OthDiagCD_140 = '',
@OthDiagCD_150 = '',
@OthDiagCD_160 = '',
@OthDiagCD_170 = '',
@PrinProcCode = '',
@PrinProcDate = '',
@OthProcDate_01a = '',
@OthProcDate_01b = '',
@OthProcDate_01c = '',
@OthProcDate_01d = '',
@OthProcDate_01e = '',
@OthProcCode_01a = '',
@OthProcCode_01b = '',
@OthProcCode_01c = '',
@OthProcCode_01d = '',
@OthProcCode_01e = ''
DECLARE @previousqual1 char(3), @lastemptycode tinyint
DECLARE
@HI01_1_CodeQualifier char(3),
@HI01_2_IndustryCode varchar(30),
@HI01_4_DateTimePeriod varchar(17),
@HI01_5_MonetaryAmount money,
@HI02_1_CodeQualifier char(3),
@HI02_2_IndustryCode varchar(30),
@HI02_4_DateTimePeriod varchar(17),
@HI02_5_MonetaryAmount money,
@HI03_1_CodeQualifier char(3),
@HI03_2_IndustryCode varchar(30),
@HI03_4_DateTimePeriod varchar(17),
@HI03_5_MonetaryAmount money,
@HI04_1_CodeQualifier char(3),
@HI04_2_IndustryCode varchar(30),
@HI04_4_DateTimePeriod varchar(17),
@HI04_5_MonetaryAmount money,
@HI05_1_CodeQualifier char(3),
@HI05_2_IndustryCode varchar(30),
@HI05_4_DateTimePeriod varchar(17),
@HI05_5_MonetaryAmount money,
@HI06_1_CodeQualifier char(3),
@HI06_2_IndustryCode varchar(30),
@HI06_4_DateTimePeriod varchar(17),
@HI06_5_MonetaryAmount money,
@HI07_1_CodeQualifier char(3),
@HI07_2_IndustryCode varchar(30),
@HI07_4_DateTimePeriod varchar(17),
@HI07_5_MonetaryAmount money,
@HI08_1_CodeQualifier char(3),
@HI08_2_IndustryCode varchar(30),
@HI08_4_DateTimePeriod varchar(17),
@HI08_5_MonetaryAmount money,
@HI09_1_CodeQualifier char(3),
@HI09_2_IndustryCode varchar(30),
@HI09_4_DateTimePeriod varchar(17),
@HI09_5_MonetaryAmount money,
@HI10_1_CodeQualifier char(3),
@HI10_2_IndustryCode varchar(30),
@HI10_4_DateTimePeriod varchar(17),
@HI10_5_MonetaryAmount money,
@HI11_1_CodeQualifier char(3),
@HI11_2_IndustryCode varchar(30),
@HI11_4_DateTimePeriod varchar(17),
@HI11_5_MonetaryAmount money,
@HI12_1_CodeQualifier char(3),
@HI12_2_IndustryCode varchar(30),
@HI12_4_DateTimePeriod varchar(17),
@HI12_5_MonetaryAmount money,
@HI01_4_DateTimePeriod2 varchar(6),
@HI02_4_DateTimePeriod2 varchar(6),
@HI03_4_DateTimePeriod2 varchar(6),
@HI04_4_DateTimePeriod2 varchar(6),
@HI05_4_DateTimePeriod2 varchar(6),
@HI06_4_DateTimePeriod2 varchar(6),
@HI07_4_DateTimePeriod2 varchar(6),
@HI08_4_DateTimePeriod2 varchar(6),
@HI09_4_DateTimePeriod2 varchar(6),
@HI10_4_DateTimePeriod2 varchar(6),
@HI11_4_DateTimePeriod2 varchar(6),
@HI12_4_DateTimePeriod2 varchar(6)
DECLARE ClaimInfoCodesCursor CURSOR FAST_FORWARD FOR
SELECT
ISNULL(HI01_1_CodeQualifier, ''), ISNULL(HI01_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI01_4_DateTimePeriod, 8)), HI01_5_MonetaryAmount,
ISNULL(HI02_1_CodeQualifier, ''), ISNULL(HI02_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI02_4_DateTimePeriod, 8)), HI02_5_MonetaryAmount,
ISNULL(HI03_1_CodeQualifier, ''), ISNULL(HI03_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI03_4_DateTimePeriod, 8)), HI03_5_MonetaryAmount,
ISNULL(HI04_1_CodeQualifier, ''), ISNULL(HI04_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI04_4_DateTimePeriod, 8)), HI04_5_MonetaryAmount,
ISNULL(HI05_1_CodeQualifier, ''), ISNULL(HI05_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI05_4_DateTimePeriod, 8)), HI05_5_MonetaryAmount,
ISNULL(HI06_1_CodeQualifier, ''), ISNULL(HI06_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI06_4_DateTimePeriod, 8)), HI06_5_MonetaryAmount,
ISNULL(HI07_1_CodeQualifier, ''), ISNULL(HI07_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI07_4_DateTimePeriod, 8)), HI07_5_MonetaryAmount,
ISNULL(HI08_1_CodeQualifier, ''), ISNULL(HI08_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI08_4_DateTimePeriod, 8)), HI08_5_MonetaryAmount,
ISNULL(HI09_1_CodeQualifier, ''), ISNULL(HI09_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI09_4_DateTimePeriod, 8)), HI09_5_MonetaryAmount,
ISNULL(HI10_1_CodeQualifier, ''), ISNULL(HI10_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI10_4_DateTimePeriod, 8)), HI10_5_MonetaryAmount,
ISNULL(HI11_1_CodeQualifier, ''), ISNULL(HI11_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI11_4_DateTimePeriod, 8)), HI11_5_MonetaryAmount,
ISNULL(HI12_1_CodeQualifier, ''), ISNULL(HI12_2_IndustryCode, ''), dbo.fn_DateToString_MMDDYY(LEFT(HI12_4_DateTimePeriod, 8)), HI12_5_MonetaryAmount,
--TODO: BUG IN ORIGINAL SPROC. This is needed for second date in RD8 parts like BI section
ISNULL(SUBSTRING(HI01_4_DateTimePeriod, 14, 2) + SUBSTRING(HI01_4_DateTimePeriod, 16, 2) + SUBSTRING(HI01_4_DateTimePeriod, 12, 2), '') AS HI01_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI02_4_DateTimePeriod, 14, 2) + SUBSTRING(HI02_4_DateTimePeriod, 16, 2) + SUBSTRING(HI02_4_DateTimePeriod, 12, 2), '') AS HI02_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI03_4_DateTimePeriod, 14, 2) + SUBSTRING(HI03_4_DateTimePeriod, 16, 2) + SUBSTRING(HI03_4_DateTimePeriod, 12, 2), '') AS HI03_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI04_4_DateTimePeriod, 14, 2) + SUBSTRING(HI04_4_DateTimePeriod, 16, 2) + SUBSTRING(HI04_4_DateTimePeriod, 12, 2), '') AS HI04_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI05_4_DateTimePeriod, 14, 2) + SUBSTRING(HI05_4_DateTimePeriod, 16, 2) + SUBSTRING(HI05_4_DateTimePeriod, 12, 2), '') AS HI05_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI06_4_DateTimePeriod, 14, 2) + SUBSTRING(HI06_4_DateTimePeriod, 16, 2) + SUBSTRING(HI06_4_DateTimePeriod, 12, 2), '') AS HI06_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI07_4_DateTimePeriod, 14, 2) + SUBSTRING(HI07_4_DateTimePeriod, 16, 2) + SUBSTRING(HI07_4_DateTimePeriod, 12, 2), '') AS HI07_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI08_4_DateTimePeriod, 14, 2) + SUBSTRING(HI08_4_DateTimePeriod, 16, 2) + SUBSTRING(HI08_4_DateTimePeriod, 12, 2), '') AS HI08_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI09_4_DateTimePeriod, 14, 2) + SUBSTRING(HI09_4_DateTimePeriod, 16, 2) + SUBSTRING(HI09_4_DateTimePeriod, 12, 2), '') AS HI09_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI10_4_DateTimePeriod, 14, 2) + SUBSTRING(HI10_4_DateTimePeriod, 16, 2) + SUBSTRING(HI10_4_DateTimePeriod, 12, 2), '') AS HI10_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI11_4_DateTimePeriod, 14, 2) + SUBSTRING(HI11_4_DateTimePeriod, 16, 2) + SUBSTRING(HI11_4_DateTimePeriod, 12, 2), '') AS HI11_4_DateTimePeriod2,
ISNULL(SUBSTRING(HI12_4_DateTimePeriod, 14, 2) + SUBSTRING(HI12_4_DateTimePeriod, 16, 2) + SUBSTRING(HI12_4_DateTimePeriod, 12, 2), '') AS HI12_4_DateTimePeriod2
FROM dbo.tbl837IClaimInformationCodes (NOLOCK)
WHERE ClaimID = @ClaimID
ORDER BY HI01_1_CodeQualifier --TODO is this needed and does it affect things?
SELECT @previousqual1 = 'ZZZ', @i = 1
OPEN ClaimInfoCodesCursor
FETCH NEXT FROM ClaimInfoCodesCursor INTO
@HI01_1_CodeQualifier, @HI01_2_IndustryCode, @HI01_4_DateTimePeriod, @HI01_5_MonetaryAmount,
@HI02_1_CodeQualifier, @HI02_2_IndustryCode, @HI02_4_DateTimePeriod, @HI02_5_MonetaryAmount,
@HI03_1_CodeQualifier, @HI03_2_IndustryCode, @HI03_4_DateTimePeriod, @HI03_5_MonetaryAmount,
@HI04_1_CodeQualifier, @HI04_2_IndustryCode, @HI04_4_DateTimePeriod, @HI04_5_MonetaryAmount,
@HI05_1_CodeQualifier, @HI05_2_IndustryCode, @HI05_4_DateTimePeriod, @HI05_5_MonetaryAmount,
@HI06_1_CodeQualifier, @HI06_2_IndustryCode, @HI06_4_DateTimePeriod, @HI06_5_MonetaryAmount,
@HI07_1_CodeQualifier, @HI07_2_IndustryCode, @HI07_4_DateTimePeriod, @HI07_5_MonetaryAmount,
@HI08_1_CodeQualifier, @HI08_2_IndustryCode, @HI08_4_DateTimePeriod, @HI08_5_MonetaryAmount,
@HI09_1_CodeQualifier, @HI09_2_IndustryCode, @HI09_4_DateTimePeriod, @HI09_5_MonetaryAmount,
@HI10_1_CodeQualifier, @HI10_2_IndustryCode, @HI10_4_DateTimePeriod, @HI10_5_MonetaryAmount,
@HI11_1_CodeQualifier, @HI11_2_IndustryCode, @HI11_4_DateTimePeriod, @HI11_5_MonetaryAmount,
@HI12_1_CodeQualifier, @HI12_2_IndustryCode, @HI12_4_DateTimePeriod, @HI12_5_MonetaryAmount,
@HI01_4_DateTimePeriod2, @HI02_4_DateTimePeriod2, @HI03_4_DateTimePeriod2,
@HI04_4_DateTimePeriod2, @HI05_4_DateTimePeriod2, @HI06_4_DateTimePeriod2,
@HI07_4_DateTimePeriod2, @HI08_4_DateTimePeriod2, @HI09_4_DateTimePeriod2,
@HI10_4_DateTimePeriod2, @HI11_4_DateTimePeriod2, @HI12_4_DateTimePeriod2
@FETCH_STATUS <> -1)
BEGIN
@FETCH_STATUS <> -2)
BEGIN
--handle rows that can have repeat 2
IF @previousqual1 = @HI01_1_CodeQualifier
BEGIN
SET @i = @i + 1
END
ELSE
BEGIN
SET @i = 1
END
IF @HI01_1_CodeQualifier = 'BK'
BEGIN
SET @PrinDiagCD = LEFT(@HI01_2_IndustryCode, 8)
IF @HI02_1_CodeQualifier = 'BJ'
BEGIN
SET @AdmDiagCD = LEFT(@HI02_2_IndustryCode, 7)
END
IF @HI02_1_CodeQualifier = 'ZZ'
BEGIN
SET @PatVisitReasonCD = LEFT(@HI02_2_IndustryCode, 7)
END
IF @HI03_1_CodeQualifier = 'BN'
BEGIN
SET @ECode = LEFT(@HI03_2_IndustryCode, 8)
END
END
IF @HI01_1_CodeQualifier = 'BF' --2 repeats
BEGIN
IF @i = 1
BEGIN
SELECT @OthDiagCD_010 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_020 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_030 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI12_2_IndustryCode, 8)
-- set @lastemptycode used if 2 repeats
SET @lastemptycode =
CASE WHEN @OthDiagCD_010 = '' THEN 1
WHEN @OthDiagCD_020 = '' THEN 2
WHEN @OthDiagCD_030 = '' THEN 3
WHEN @OthDiagCD_040 = '' THEN 4
WHEN @OthDiagCD_050 = '' THEN 5
WHEN @OthDiagCD_060 = '' THEN 6
WHEN @OthDiagCD_070 = '' THEN 7
WHEN @OthDiagCD_080 = '' THEN 8
WHEN @OthDiagCD_090 = '' THEN 9
WHEN @OthDiagCD_100 = '' THEN 10
WHEN @OthDiagCD_110 = '' THEN 11
WHEN @OthDiagCD_120 = '' THEN 12
WHEN @OthDiagCD_130 = '' THEN 13
END
END
ELSE
BEGIN --2nd line of BF codes
IF @lastemptycode = 1 --this probably shouldn't happen, but you never know
BEGIN
SELECT @OthDiagCD_010 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_020 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_030 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 2
BEGIN
SELECT @OthDiagCD_020 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_030 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 3
BEGIN
SELECT @OthDiagCD_030 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_040 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 4
BEGIN
SELECT @OthDiagCD_040 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_050 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 5
BEGIN
SELECT @OthDiagCD_050 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_060 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 6
BEGIN
SELECT @OthDiagCD_060 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_070 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI11_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI12_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 7
BEGIN
SELECT @OthDiagCD_070 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_080 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI10_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI11_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 8
BEGIN
SELECT @OthDiagCD_080 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_090 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI09_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI10_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 9
BEGIN
SELECT @OthDiagCD_090 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_100 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI08_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI09_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 10
BEGIN
SELECT @OthDiagCD_100 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_110 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI07_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI08_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 11
BEGIN
SELECT @OthDiagCD_110 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_120 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI06_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI07_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 12
BEGIN
SELECT @OthDiagCD_120 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_130 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI05_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI06_2_IndustryCode, 8)
END
ELSE
BEGIN
IF @lastemptycode = 13
BEGIN
SELECT @OthDiagCD_130 = LEFT(@HI01_2_IndustryCode, 8),
@OthDiagCD_140 = LEFT(@HI02_2_IndustryCode, 8),
@OthDiagCD_150 = LEFT(@HI03_2_IndustryCode, 8),
@OthDiagCD_160 = LEFT(@HI04_2_IndustryCode, 8),
@OthDiagCD_170 = LEFT(@HI05_2_IndustryCode, 8)
END
--bunch of ends for the elses
END END END END END END END END END END END END
END --END of BF section
END
IF @HI01_1_CodeQualifier IN ('BP', 'BR')
BEGIN
SET @PrinProcCode = LEFT(@HI01_2_IndustryCode, 7)
SET @PrinProcDate = @HI01_4_DateTimePeriod
END
IF @HI01_1_CodeQualifier IN ('BO', 'BQ')
BEGIN
IF @i = 1
BEGIN
SET @OthProcCode_01a = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01a = @HI01_4_DateTimePeriod
SET @OthProcCode_01b = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01b = @HI02_4_DateTimePeriod
SET @OthProcCode_01c = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI03_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI04_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI04_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI05_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI05_4_DateTimePeriod
-- set @lastemptycode used if 2 repeats
SET @lastemptycode =
CASE WHEN @OthProcCode_01a = '' THEN 1
WHEN @OthProcCode_01b = '' THEN 2
WHEN @OthProcCode_01c = '' THEN 3
WHEN @OthProcCode_01d = '' THEN 4
WHEN @OthProcCode_01e = '' THEN 5
END
END
ELSE
BEGIN --2nd line of BO codes
IF @lastemptycode = 1 --this probably shouldn't happen, but you never know
BEGIN
SET @OthProcCode_01a = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01a = @HI01_4_DateTimePeriod
SET @OthProcCode_01b = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01b = @HI02_4_DateTimePeriod
SET @OthProcCode_01c = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI03_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI04_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI04_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI05_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI05_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 2
BEGIN
SET @OthProcCode_01b = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01b = @HI01_4_DateTimePeriod
SET @OthProcCode_01c = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI02_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI03_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI04_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI04_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 3
BEGIN
SET @OthProcCode_01c = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01c = @HI01_4_DateTimePeriod
SET @OthProcCode_01d = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI02_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI03_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI03_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 4
BEGIN
SET @OthProcCode_01d = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01d = @HI01_4_DateTimePeriod
SET @OthProcCode_01e = LEFT(@HI02_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI02_4_DateTimePeriod
END
ELSE
BEGIN
IF @lastemptycode = 5
BEGIN
SET @OthProcCode_01e = LEFT(@HI01_2_IndustryCode, 7)
SET @OthProcDate_01e = @HI01_4_DateTimePeriod
END
--bunch of ends for the elses
END END END END
END
END --END BO section
IF @HI01_1_CodeQualifier = 'BI'
BEGIN
IF @i = 1
BEGIN
SELECT @OccSpCode_05a = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05a = @HI01_4_DateTimePeriod,
@OccSpThr_05a = @HI01_4_DateTimePeriod2,
@OccSpCode_05b = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05b = @HI02_4_DateTimePeriod,
@OccSpThr_05b = @HI02_4_DateTimePeriod2,
@OccSpCode_05c = LEFT(@HI03_2_IndustryCode, 2),
@OccSpFm_05c = @HI03_4_DateTimePeriod,
@OccSpThr_05c = @HI03_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI04_2_IndustryCode, 2),
@OccSpFm_05d = @HI04_4_DateTimePeriod,
@OccSpThr_05d = @HI04_4_DateTimePeriod2
-- set @lastemptycode used if 2 repeats
SET @lastemptycode =
CASE WHEN @OccSpCode_05a = '' THEN 1
WHEN @OccSpCode_05b = '' THEN 2
WHEN @OccSpCode_05c = '' THEN 3
WHEN @OccSpCode_05d = '' THEN 4
END
END
ELSE
BEGIN --2nd line of BO codes
IF @lastemptycode = 1 --this probably shouldn't happen, but you never know
BEGIN
SELECT @OccSpCode_05a = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05a = @HI01_4_DateTimePeriod,
@OccSpThr_05a = @HI01_4_DateTimePeriod2,
@OccSpCode_05b = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05b = @HI02_4_DateTimePeriod,
@OccSpThr_05b = @HI02_4_DateTimePeriod2,
@OccSpCode_05c = LEFT(@HI03_2_IndustryCode, 2),
@OccSpFm_05c = @HI03_4_DateTimePeriod,
@OccSpThr_05c = @HI03_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI04_2_IndustryCode, 2),
@OccSpFm_05d = @HI04_4_DateTimePeriod,
@OccSpThr_05d = @HI04_4_DateTimePeriod2
END
ELSE
BEGIN
IF @lastemptycode = 2
BEGIN
SELECT @OccSpCode_05b = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05b = @HI01_4_DateTimePeriod,
@OccSpThr_05b = @HI01_4_DateTimePeriod2,
@OccSpCode_05c = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05c = @HI02_4_DateTimePeriod,
@OccSpThr_05c = @HI02_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI03_2_IndustryCode, 2),
@OccSpFm_05d = @HI03_4_DateTimePeriod,
@OccSpThr_05d = @HI03_4_DateTimePeriod2
END
ELSE
BEGIN
IF @lastemptycode = 3
BEGIN
SELECT @OccSpCode_05c = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05c = @HI01_4_DateTimePeriod,
@OccSpThr_05c = @HI01_4_DateTimePeriod2,
@OccSpCode_05d = LEFT(@HI02_2_IndustryCode, 2),
@OccSpFm_05d = @HI02_4_DateTimePeriod,
@OccSpThr_05d = @HI02_4_DateTimePeriod2
END
ELSE
BEGIN
IF @lastemptycode = 4
BEGIN
SELECT @OccSpCode_05d = LEFT(@HI01_2_IndustryCode, 2),
@OccSpFm_05d = @HI01_4_DateTimePeriod,
@OccSpThr_05d = @HI01_4_DateTimePeriod2
END END END END
END
END --END BI section
IF @HI01_1_CodeQualifier = 'BH'
BEGIN
IF @i = 1
BEGIN
SELECT @OccCode_01a = LEFT(@HI01_2_IndustryCode, 2),
@OccDate_01a = @HI01_4_DateTimePeriod,
@OccCode_01b = LEFT(@HI02_2_IndustryCode, 2),
@OccDate_01b = @HI02_4_DateTimePeriod,
@OccCode_02a = LEFT(@HI03_2_IndustryCode, 2),
@OccDate_02a = @HI03_4_DateTimePeriod,
@OccCode_02b = LEFT(@HI04_2_IndustryCode, 2),
@OccDate_02b = @HI04_4_DateTimePeriod,
@OccCode_03a = LEFT(@HI05_2_IndustryCode, 2),
@OccDate_03a = @HI05_4_DateTimePeriod,
@OccCode_03b = LEFT(@HI06_2_IndustryCode, 2),
@OccDate_03b = @HI06_4_Da
Kevin G. Boles
SQL Server MVP 2007-2012
Indicium Resources, Inc.
SQL Server MVP 2007-2012
Indicium Resources, Inc.
Comments
SQL Server MVP 2007-2012
Indicium Resources, Inc.
That is a beast of a proc and no mistake, if you can email it jonathan [dot] watts [at] red [dash] gate [dot] com, I will take a look at it in SQL Refactor.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd
Cheers,
Jonathan
-Project Manager
-Red Gate Software Ltd