Delimiter //
/**
 * Author:  armen
 * Created: 26 mai 2016
 * #7326
 * les nouveauw reclaim details cloturant les reclamation ouverte en cas de letrage totaux
 * ATTENTION
 * cette table est une préparation. Il peux contenir plus de données qu'il y aura réélement
 * appliqué dans les tables de production.
 * En effet on doit tenir compte du statue de léttrage "FERMER"
 * le batch qui calcul le status de lettrage intervient la pahose de copie du dernier lot
 * c'est donc apres qu'on appliquera les filtre nécessaires
 */
Drop Procedure if Exists LOADER_MATCHINGCODE_NORMALIZE_MAP_NEW_RECLAIMS_DETAILS//
 
Create Procedure LOADER_MATCHINGCODE_NORMALIZE_MAP_NEW_RECLAIMS_DETAILS (
    In batchnum int
)
Begin
	Set @admin = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);
	Set @sumarieIdReclaimUpdated = (
				Select ID_DF_SUMMARIES 
				from DF_SUMMARIES 
				where ADMIN_ID_ADMIN = @admin 
					and DF_SUMMARIES_CODE = 'RECLA-ST-UPD'
			)
		;
	Insert into LOADER_MATCHINGCODES_NEW_RECLAIMS_DETAILS (
		LOADER_STATUS_ID_LOADER_STATUS, ENTRIES_ID_ENTRIES, DF_RCLM_STT_ID_DF_RCLM_STT
		, DF_RCLM_ID_DF_RCLM, RECLAIMS_ID_RECLAIMS, RECLAIMS_DETAILS_OPINION, RECLAIMS_DETAILS_LAST_UPDATE
		, RECLAIMS_DETAILS_BLOCKED, RECLAIMS_DETAILS_DEAD_LINE
		, RECLAIMS_DETAILS_CLOSED, RECLAIMS_DETAILS_AMOUNT, RECLAIMS_DETAILS_PREV_RECLAIM
		, RECLAIMS_DETAILS_IS_ACTIVE, RECLAIMS_DETAILS_IS_TMP
		, RECLAIMS_DETAILS_NOTES, DF_SUMMARIES_ID_DF_SUMMR
	)
	Select
		LDS.ID_LOADER_STATUS, ENT.ID_ENTRIES, DFRS.ID_DF_RECLAIMS_STATUS
		, RCDT.DF_RCLM_ID_DF_RCLM, RCDT.RECLAIMS_ID_RECLAIMS, Null RECLAIMS_DETAILS_OPINION, now() RECLAIMS_DETAILS_LAST_UPDATE
		, DFR.DF_RECLAIMS_LEAVEOUT_MODE RECLAIMS_DETAILS_BLOCKED, date_add(curdate(), interval DFR.DF_RECLAIMS_DEAD_LINE Day) RECLAIMS_DETAILS_DEAD_LINE
		, DFRS.DF_RECLAIMS_STATUS_CLOSED, ENT.ENTRIES_ENT_BALANCE, RCDT.ID_RECLAIMS_DETAILS
		, 1 RECLAIMS_DETAILS_IS_ACTIVE, 0 RECLAIMS_DETAILS_IS_TMP
		, null RECLAIMS_DETAILS_NOTES, @sumarieIdReclaimUpdated
	From
		LOADER_STATUS LDS
		Inner Join LOADER_MATCHINGCODES LDM	
			On LDS.ID_LOADER_STATUS = LDM.LOADER_STATUS_ID_LOADER_STATUS
		Inner Join ENTRIES ENT
			On LDM.ID_ENTRIES = ENT.ID_ENTRIES
		Inner Join DF_MATCHINGCODE_STATE DFMS
			On ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = DFMS.ID_DF_MATCHINGCODE_STATE
		Inner Join RECLAIMS_DETAILS RCDT
			On ENT.ID_ENTRIES = RCDT.ENTRIES_ID_ENTRIES
	-- 	Inner Join RECLAIMS RCLM
	-- 		on RCDT.
		Inner Join ACCOUNTS ACC
			On LDM.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
		Inner Join ENTITIES ETS
			On ACC.ENTITIES_ID_ENTITIES = ETS.ID_ENTITIES
		Inner Join DF_RECLAIMS_STATUS DFRS
			On ETS.ENTITIES_RCLM_COD_FOR_CLOSE = DFRS.ID_DF_RECLAIMS_STATUS
		Inner Join DF_RECLAIMS DFR
			On RCDT.DF_RCLM_ID_DF_RCLM = DFR.ID_DF_RECLAIMS

	Where
		LDS.LOADER_STATUS_ERRCOUNT = 0
		-- ici , le DF_MATCHINGCODE_STATE_CLOSED  n'est pas encore à jour
		-- il sera mise à jour lors de la copie
		-- il faudr filter à ce moment
-- 		And DFMS.DF_MATCHINGCODE_STATE_CLOSED = 0
		And RCDT.RECLAIMS_DETAILS_IS_ACTIVE = 1
		And RCDT.RECLAIMS_DETAILS_CLOSED = 0
		And ETS.ENTITIES_RCLM_COD_FOR_CLOSE Is Not Null
	;

    

End //
Delimiter ;