Delimiter //
/**
 * limitation 1 ecriture / reclamtaion
 * pour savoir si c'est un nouvelle reclmataion ou une mise à jour, 
 * on recherche une reclamtaion de même code sur la même ecriture, non fermé
 * et mise à jour par le membre batch
 * doit être executé apres LOADER_RECLAIMS_NORMALIZE_MAP_MEMBER
 */
Drop Procedure If Exists LOADER_RECLAIMS_NORMALIZE_MAP_RECLAIMS_CREATE_OR_UPDATE//
Create Procedure LOADER_RECLAIMS_NORMALIZE_MAP_RECLAIMS_CREATE_OR_UPDATE(
    In batchnum Int
)
Begin
    
	-- recherche d'une reclmation ouvert correspondant
	Update
		LOADER_STATUS STS
		Inner Join LOADER_RECLAIMS LRD
			On LRD.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
		Inner Join RECLAIMS_DETAILS RCLD
			On LRD.ENTRIES_ID_ENTRIES = RCLD.ENTRIES_ID_ENTRIES -- meme ecriture
			And LRD.DF_RCLM_ID_DF_RCLM = RCLD.DF_RCLM_ID_DF_RCLM -- meme type de reclamation
			And RCLD.RECLAIMS_DETAILS_IS_TMP = 0 -- non provisoire
			And RCLD.RECLAIMS_DETAILS_IS_ACTIVE = 1 -- detail "courrant
			And RCLD.RECLAIMS_DETAILS_CLOSED = 0 -- non ferme
		Inner Join RECLAIMS RCM
			On RCLD.RECLAIMS_ID_RECLAIMS = RCM.ID_RECLAIMS
			And RCM.MEMBERS_ID_MEMBERS = LRD.MEMBERS_ID_MEMBERS
			And RCM.RECLAIMS_IS_TMP = 0
	Set
		LRD.ID_RECLAIMS = RCM.ID_RECLAIMS
		, LRD.RECLAIMS_DETAILS_PREV_RECLAIM = RCLD.ID_RECLAIMS_DETAILS
	Where
		STS.LOADER_STATUS_BATCH_NUM = batchnum
		and STS.LOADER_STATUS_ERRCOUNT = 0

	;
	-- calcul id pour les nouvelle reclamation
	Drop temporary table if exists TMP_RECLAIMS_NEW;
	Create temporary table TMP_RECLAIMS_NEW (
		ID_TMP_RECLAIMS_NEW Int unsigned not null Primary Key auto_increment
		, LOADER_STATUS_ID_LOADER_STATUS Int unsigned not null
		, index (LOADER_STATUS_ID_LOADER_STATUS)
	);
	Insert Into TMP_RECLAIMS_NEW(LOADER_STATUS_ID_LOADER_STATUS)
	Select 
		STS.ID_LOADER_STATUS
	From
		LOADER_STATUS STS
		Inner Join LOADER_RECLAIMS LRD
			On LRD.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
	Where 
		STS.LOADER_STATUS_BATCH_NUM = batchnum
		and STS.LOADER_STATUS_ERRCOUNT = 0	
		And LRD.ID_RECLAIMS is Null
	;
	-- mise à jour avec les nouveau id
	Update
		LOADER_STATUS STS
		Inner Join LOADER_RECLAIMS LRD
			On LRD.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
		Inner Join TMP_RECLAIMS_NEW TNRC
			On TNRC.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
	Set
		LRD.NEW_ID_RECLAIMS = TNRC.ID_TMP_RECLAIMS_NEW
	Where
		STS.LOADER_STATUS_BATCH_NUM = batchnum
		and STS.LOADER_STATUS_ERRCOUNT = 0
	;

	-- purge 
	Drop temporary table if exists TMP_RECLAIMS_NEW;

	-- empecher la creation de nouveau reclaim detail si le status n'est pas modifié par rapport au dernier
	-- on les indique par un alerte
	Update
		LOADER_STATUS STS
		Inner Join LOADER_RECLAIMS LRD
			On LRD.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
		Inner Join RECLAIMS_DETAILS RCLD
			On LRD.RECLAIMS_DETAILS_PREV_RECLAIM = RCLD.ID_RECLAIMS_DETAILS
	Set
		LRD.ID_RECLAIMS = null -- empeche la creation de nouveau reclaim detail dans la procédure d'inserton
		, STS.LOADER_STATUS_WARNCOUNT = STS.LOADER_STATUS_WARNCOUNT + 1
        , STS.LOADER_STATUS_WARNMSGS = LOADER_CONCAT_ERR(STS.LOADER_STATUS_WARNMSGS, Concat('Réclamation non mise à jour : nouveau status identique à l''ancien' ) )
	Where
		STS.LOADER_STATUS_BATCH_NUM = batchnum
		and STS.LOADER_STATUS_ERRCOUNT = 0
		And LRD.DF_RCLM_STT_ID_DF_RCLM_STT = RCLD.DF_RCLM_STT_ID_DF_RCLM_STT
	;

	-- empecher la creation de nouveau reclaim detail si le status n'est pas modifié par rapport au dernier
	


End//
Delimiter ;