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 ;