Delimiter //
/**
    In batchnum Int : num du lot
*/
Drop Procedure if exists LOADER_RECLAIMS_DETAILS_INSERT//
Create Procedure LOADER_RECLAIMS_DETAILS_INSERT (
    In batchnum int
    , In lastId Int
)
Begin

        Insert Into RECLAIMS_DETAILS (
			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
        )  
        Select 
			src.ENTRIES_ID_ENTRIES, src.DF_RCLM_STT_ID_DF_RCLM_STT, src.DF_RCLM_ID_DF_RCLM
            , Coalesce(src.ID_RECLAIMS, src.NEW_ID_RECLAIMS + lastId), src.RECLAIMS_DETAILS_OPINION
			, Coalesce(src.RECLAIMS_CREATED, curdate()), src.RECLAIMS_DETAILS_BLOCKED, src.RECLAIMS_DETAILS_DEAD_LINE
			, src.RECLAIMS_DETAILS_CLOSED, src.RECLAIMS_DETAILS_AMOUNT, src.RECLAIMS_DETAILS_PREV_RECLAIM
			, 1, 0, src.RECLAIMS_DETAILS_NOTES
        From  
            LOADER_RECLAIMS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
        Where 
            lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
			-- on insere les détail que 
			-- -> pour que les nouvelle reclamation (src.NEW_ID_RECLAIMS <> null)
			-- --> pour les reclamation à actualiser (src.ID_RECLAIMS <> null)
            and Coalesce(src.NEW_ID_RECLAIMS, src.ID_RECLAIMS) Is Not Null
        ;
End //

Delimiter ;
