Defenseload

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 ;