Create Procedure SP_Evasan_SendEndorseCancel 
				@FromIssuanceDate dtDate
				,@ToIssuanceDate dtDate
				,@PolicyKey dtKey=Null 
With Encryption
As
Begin
	Declare @id Varchar(50),
			@refrenceid Varchar(50),
			@bno Varchar(50),
			@cancelDate Varchar(50),
			@userId Varchar(50),
			@EvasanCode nvarchar(Max), 
			@ErrMessage nvarchar(Max), 
			/*@UnqCod nvarchar(30) ,
			@ErrorCods nvarchar(30) ,
			@ErrorNams nvarchar(300),*/
			@StartTime datetime;

	DECLARE Evasan_Cursor CURSOR FOR
	SELECT 	P.PolicyKey,0 id ,ReIns2 refrenceid,P.PolicyNo bno ,E.IssuanceDate cancelDate ,100 userId 
	FROM V_AXA_PolicyMapfre P Join V_CMN_Endorse E ON P.PolicyKey=E.PolicyKey And E.EndorseType=5
		 Where P.PolicyBranch=2240
		 And P.Reins2 Is Not Null
		 And P.Reins1 Is Null
		 And E.IssuanceDate Between @FromIssuanceDate And @ToIssuanceDate
		 And (@PolicyKey Is Null OR HeaderPolicyKey=@PolicyKey)
		 
	OPEN Evasan_Cursor;
	FETCH NEXT FROM Evasan_Cursor
	into @PolicyKey,@id ,@refrenceid,@bno ,@cancelDate ,@userId 
	WHILE @@FETCH_STATUS = 0
	   BEGIN
		  Set @StartTime=GETDATE()

		  exec xp_EvasanInsertCANCEL
				Null,Null,@id ,@refrenceid,@bno ,@cancelDate ,@userId ,@EvasanCode out,@ErrMessage Out
				  

		  IF @EvasanCode <>'-1'
			  Update V_cmn_policy
			  Set Reins1=@EvasanCode
			  Where HeaderPolicyKey=@PolicyKey
		  Else 
			  Update V_cmn_policy
			  Set Reins3=Left(@ErrMessage,250)
			  Where HeaderPolicyKey=@PolicyKey

		  FETCH NEXT FROM Evasan_Cursor
			into @PolicyKey,@id ,@refrenceid,@bno ,@cancelDate ,@userId 
	   END;
	CLOSE Evasan_Cursor;
	DEALLOCATE Evasan_Cursor;
End        

Go
 ----------------
 --- !version script(1001210)
--- for AXA
Insert Into T_CMN_VERSIONLOG(AppID, CommonVersion, DBVersion, MinSysVersion, MinLibVersion)
Values(1001210,325,138,139,480)
GO        
    