Tuesday, 27 May 2014

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 ;

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...