Tuesday, 27 May 2014

SQL Server Stored Procedure for EDI 837 P 5010 Version

Here is a sample Stored Procedure which is used to generate Loop 2010BB. For complete List, please email me at vbsenthilinnet@gmail.com

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



/********************************************************************************
Page No : 130
Purpose : PAYER NAME
********************************************************************************/

ALTER PROCEDURE [dbo].[ProcHCEDI_Loop2010BB]
@plngClaimNo Bigint = NULL,
@pstrSegDelim VARCHAR(1) = Null,
@plngInsPlanRowID Bigint = NULL

AS
DECLARE
@strValidText VARCHAR(8000),
@strInValidText VARCHAR(8000),
@StrInsName VARCHAR(100),
@strAddress1 VARCHAR(100),
@strAddress2 VARCHAR(100),
@strCity VARCHAR(50),
@strState VARCHAR(50),
@strZip VARCHAR(50),
@strPayerID VARCHAR(50),
@strNHPI VARCHAR(80)



BEGIN

SET @strValidText = ''
SET @strInvalidText = ''
SELECT @pstrSegDelim = IsNull(@pstrSegDelim,'*')

SET @strValidText = 'NM1' + @pstrSegDelim


/*
Page No : 131
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : NM1 Individual or Organizational Name
Data Element : NM101 - Entity Identifier Code
*/

SET @strValidText = @strValidText + 'PR' + @pstrSegDelim


/*
Page No : 131
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : NM1 Individual or Organizational Name
Data Element : NM102 - Entity Type Qualifier
*/

SET @strValidText = @strValidText + '2' + @pstrSegDelim


SELECT
@StrInsName = LTRIM(RTRIM(ISNULL(InsuranceName,''))),
@strAddress1 = LTRIM(RTRIM(ISNULL(Address1,''))),
@strAddress2 = LTRIM(RTRIM(ISNULL(Address2,''))),
@strCity = LTRIM(RTRIM(ISNULL(City,''))),
@strState = LTRIM(RTRIM(ISNULL(State,''))),
@strZip = LTRIM(RTRIM(ISNULL(Zip,''))),
@strPayerID = LTRIM(RTRIM(ISNULL(PayerID,'')))
FROM
ViewHCInsurance
WHERE
Code = (SELECT InsCode From ViewHCPatientPolicy Where InsPlanRowID =@plngInsPlanRowID)



SELECT @strNHPI = LTRIM(RTRIM(ISNULL(NHPI,'')))

FROM
ViewHCInsurancePlan
WHERE
PlanRowID = (SELECT PlanRowID From ViewHCPatientPolicy Where InsPlanRowID =@plngInsPlanRowID)



IF @StrInsName= ''
SET @strInValidText = @strInValidText + 'E2010BB_NM103' + @pstrSegDelim
ELSE IF LEN(@StrInsName) > 35
SET @strInValidText = @strInValidText + 'E2010BB_NM103_01' + @pstrSegDelim
ELSE
SET @StrValidText = @strValidText + @StrInsName + @pstrSegDelim


/*
Page No : 131
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : NM1 Individual or Organizational Name
Data Element : NM104,NM105,NM106,NM107
NOT USED
*/

SET @StrValidText = @strValidText + @pstrSegDelim + @pstrSegDelim + @pstrSegDelim + @pstrSegDelim

/*
Page No : 131
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : NM1 Individual or Organizational Name
Data Element : NM108,NM109
*/


IF @strPayerID = '' AND @strNHPI = ''
SET @strInValidText = @strInValidText + 'E2010BB_NM109' + @pstrSegDelim
ELSE
BEGIN
IF @strNHPI <> ''
SET @StrValidText = @strValidText + 'XV' + @pstrSegDelim + @strNHPI
ELSE IF @strPayerID <> ''
SET @StrValidText = @strValidText + 'PI' + @pstrSegDelim + @strPayerID
END


SELECT @strValidText as EDIinfo, @strInvalidText as ErrInfo,1 as RowNum,'2010BB - Payer Name' +space(200) AS LoopNo,'NM1' as SegmentName, '130' as PageNo into #temp
--**************************************************************************************************

/*
Page No : 134
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : N3 Address Information
*/


SET @strValidText = ''
SET @strInvalidText =''

/*
Page No : 134
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : N3 Address Information
Data Element : N301 - ALIAS: Payer Address 1
*/

SET @strValidText = 'N3' + @pstrSegDelim

IF @strAddress1 =''
SET @strInValidText = @strInValidText + 'E2010BB_N301' + @pstrSegDelim
ELSE
SET @StrValidText = @strValidText + @strAddress1


/*
Page No : 134
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : N3 Address Information
Data Element : N302 - ALIAS: Payer Address 2
*/

IF @strAddress2 <> ''
SET @StrValidText = @strValidText + @pstrSegDelim + @strAddress2
Insert into #temp SELECT @strValidText as EDIinfo, @strInvalidText ,2,'2010BB - Payer Name Address','N3', '134'
--**************************************************************************************************


/*
Page No : 135
Loop No : 2010BB
Loop Name : PAYER NAME
Segment Name : N4 Geographic Location PAYER CITY/STATE/ZIP CODE
*/

SET @strValidText = ''
SET @strInvalidText =''

SET @StrValidText = 'N4' + @pstrSegDelim


-- Page No 135
IF @strCity = ''
SET @strInValidText = @strInValidText + 'E2010BB_N401' + @pstrSegDelim
ELSE
SET @StrValidText = @strValidText + @strCity + @pstrSegDelim

-- Page No 135
IF @strState = ''
SET @strInValidText = @strInValidText + 'E2010BB_N402' + @pstrSegDelim
ELSE
SET @StrValidText = @strValidText + @strState + @pstrSegDelim

if @strZip <> ''
SET @strZip = REPLACE(@strZip,'-','')

-- Page No 135
IF @strZip = ''
SET @strInValidText = @strInValidText + 'E2010BB_N403' + @pstrSegDelim
ELSE
SET @StrValidText = @strValidText + @strZip

Insert into #temp SELECT @strValidText as EDIinfo, @strInvalidText ,3,'2010BB - Payer City/State/ZipCode','N4', '135'
--**************************************************************************************************


SELECT EDIinfo, ErrInfo,LoopNo,SegmentName,PageNo FROM #temp order by RowNum

IF @@ERROR = 0
RETURN 0
ELSE
RETURN 1

END



1 comment: