MYSQL 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

DELIMITER $$


DROP PROCEDURE IF EXISTS `Loop2010BB`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Loop2010BB`(pclaimNo INT, patientInsID LONG)
BEGIN
	DECLARE varReturnValue				VARCHAR(5000);
	DECLARE fieldSep 				VARCHAR(10);   
	DECLARE	varname					VARCHAR(100);
	DECLARE	varaddress1				VARCHAR(100);	
	DECLARE	varaddress2				VARCHAR(100);	
	DECLARE	varcity					VARCHAR(30);
	DECLARE	varstate				VARCHAR(10);
	DECLARE	varzip					VARCHAR(20);
	DECLARE varpayerID				VARCHAR(50);
	DECLARE varLastBilledInfo			VARCHAR(500);
	DECLARE varInsCode				VARCHAR(50);
	
	SET fieldSep= '*';
	SET varReturnValue = '';
	
	SELECT 
		IFNULL(ins.Code,''), IFNULL(ins.name,''),IFNULL(ins.address1,''),IFNULL(ins.address2,''),
		IFNULL(ins.city,''),IFNULL(ins.state,''),IFNULL(ins.zip,''),IFNULL(ins.payerID,'')
	INTO
		varInsCode, varname,varaddress1,varaddress2,varcity,varstate,varzip,varpayerID		 
	FROM
		patientinsurance patins, insurance ins
	WHERE 
		patins.ID = patientInsID
	AND
		patins.insuranceID = ins.ID;	
	
	SET varLastBilledInfo = CONCAT(':',varInsCode,':',varname,':',varpayerID);
	
	
	SET varReturnValue = CONCAT(varReturnValue,'NM1',fieldSep);
	/*
		 Page No		:	131
		 Loop No		:	2010BB
		 Loop Name		:	PAYER NAME
		 Segment Name		:	NM1 Individual or Organizational Name
		 Data Element		:   	NM101 - Entity Identifier Code
	*/
	SET varReturnValue = CONCAT(varReturnValue,'PR',fieldSep);
	/*
		 Page No		:	131
		 Loop No		:	2010BB
		 Loop Name		:	PAYER NAME
		 Segment Name		:	NM1 Individual or Organizational Name
		 Data Element		:   	NM102 - Entity Type Qualifier
	*/
	SET varReturnValue = CONCAT(varReturnValue,'2',fieldSep);
	
	SET varReturnValue = CONCAT(varReturnValue,varname,fieldSep);
	
	/*
		 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 varReturnValue = CONCAT(varReturnValue,fieldSep);
	SET varReturnValue = CONCAT(varReturnValue,fieldSep);
	SET varReturnValue = CONCAT(varReturnValue,fieldSep);
	SET varReturnValue = CONCAT(varReturnValue,fieldSep);
	
	/*
		 Page No		:	131
		 Loop No		:	2010BB
		 Loop Name		:	PAYER NAME
		 Segment Name		:	NM1 Individual or Organizational Name
		 Data Element		:   	NM108,NM109
	*/
	
	SET varReturnValue = CONCAT(varReturnValue,'PI',fieldSep);
	SET varReturnValue = CONCAT(varReturnValue,varpayerID,'~');
    
	IF varAddress1 <> '' THEN
		#2010BB Payer Address
		SET varReturnValue = CONCAT(varReturnValue,'N3',fieldSep);
		#N301	Address Line 1
		SET varReturnValue = CONCAT(varReturnValue,varAddress1);
		#N302	Address Line 2
		IF IFNULL(varAddress2,'') <> '' THEN
			SET varReturnValue = CONCAT(varReturnValue,fieldSep,varAddress2,'~');
		ELSE
			SET varReturnValue = CONCAT(varReturnValue,'~');
		END IF;
	END IF;
		
	IF varCity <> '' THEN
		#2010BB Payer City/State/Zip code
		SET varReturnValue = CONCAT(varReturnValue,'N4',fieldSep);
		
		#N401	City Name
		SET varReturnValue = CONCAT(varReturnValue,varCity,fieldSep);
		#N402	State
		SET varReturnValue = CONCAT(varReturnValue,varState,fieldSep);
		#N402	Zip
		SET varReturnValue = CONCAT(varReturnValue,varzip,'~');
	END IF;
	
	UPDATE claimheader  SET tmpLastBilledInfo = CONCAT(IFNULL(tmpLastBilledInfo,'')  ,varLastBilledInfo) 
	WHERE claimNo = pclaimNo;
	
	UPDATE claimdetail  SET tmpLastBilledInfo = CONCAT(IFNULL(tmpLastBilledInfo,'')  ,varLastBilledInfo) 
	WHERE claimNo = pclaimNo AND claimTranModeInsID = patientInsID; 
	
	SELECT varReturnValue;
		
END$$

DELIMITER ;