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:

Passing Parameter between two files using MVVM

This examples shows how to pass parameter between two zul screens. In this example, we are passing some parameters from the parent vm to...