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