CREATE OR REPLACE PROCEDURE SSEUPDATESTATUS
IS

BEGIN
   	UPDATE TXN_SERVICE_ORDER SET STATUS_ID= 
	(SELECT B.STATUS_ID --,B.STATUS,A.BEP_STATUS,A.BES_STATUS 
 	  FROM TXN_STATUS B,PSG_STATUS_MAPPING A
 	  WHERE A.BEP_STATUS=B.STATUS
 	  GROUP BY B.STATUS_ID)  	 
	WHERE BEP_SERVICEORDER_ID IN (SELECT BEP_SERVICEORDER_ID 
				      FROM   SSE_UPDATE_STATUS);
	
	COMMIT;
   	
   	UPDATE TXN_SERVICE SET SERVICE_STATUS =
   	  (SELECT STATUS FROM 
   	    (SELECT A.SERVICE_ID,B.STATUS_ID,D.STATUS
   	     FROM   TXN_SERVICE A,TXN_SERVICE_ORDER B,TXN_SERVICE_ORDER C,TXN_STATUS D
   	     WHERE  A.SERVICE_ID = B.SERVICE_ID
   	     AND    B.SERVICE_ORDER_ID = C.SERVICE_ORDER_ID
   	     AND    C.STATUS_ID = D.STATUS_ID
   	     GROUP BY A.SERVICE_ID,B.STATUS_ID,D.STATUS) TMP
   	   WHERE TMP.SERVICE_ID = TXN_SERVICE.SERVICE_ID)  
   	WHERE SERVICE_ID IN (SELECT AB.SERVICE_ID 
   			     FROM TXN_SERVICE_ORDER AB,SSE_UPDATE_STATUS SS
   			     WHERE AB.BEP_SERVICEORDER_ID = SS.BEP_SERVICEORDER_ID);
   	
   	COMMIT;
 
END;