Delimiter //
/**
 * Author:  armen
 * Created: 20 sept. 2017
 * purge et rempli la table LOADER_MATCHINGCODES_ENTRIES_TOREMOVE
 * contentant la liste des écritures à déléttrer
 */
Drop Procedure if Exists LOADER_MATCHINGCODE_NORMALIZE_MAP_ENTRIES_TO_REMOVE//
Create Procedure LOADER_MATCHINGCODE_NORMALIZE_MAP_ENTRIES_TO_REMOVE (
    In batchnum int
	, In stdLetteringMode Int 
)
Begin
	lmcnmetr_Main: Begin	
		Set @stdLetteringMode = Coalesce(stdLetteringMode, 0); 
		if @stdLetteringMode = 0 then
				-- lettrage mode NON stadard (d'un point de vu comptable)
				-- on ne réutilise pas un code lettrage
				-- on le recréé systématiquemnt

			Insert Into LOADER_MATCHINGCODES_ENTRIES_TOREMOVE (
					ENTRIES_ID_ENTRIES
				)
			Select distinct
				 Coalesce(EM2.ENTRIES_ID_ENTRIES, LM.ID_ENTRIES)
			From
				LOADER_STATUS LS
				Inner Join LOADER_MATCHINGCODES LM
					On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
				Left Join ENTRIES_MATCHCOD EM1
					On LM.ID_ENTRIES = EM1.ENTRIES_ID_ENTRIES
--                     And EM1.ENTRIES_MATCHCOD_CURRENT = 1
                -- tester si ci dessu utile
				Left Join ENTRIES_MATCHCOD EM2
					On EM1.MATCHCOD_ID_MATCHCOD = EM2.MATCHCOD_ID_MATCHCOD
    				And EM2.ENTRIES_MATCHCOD_CURRENT = 1

			Where
				LS.LOADER_STATUS_ERRCOUNT = 0
				And LS.LOADER_STATUS_BATCH_NUM = batchnum
			;

		Else
			-- en mode standard, doivent être préseant dans cette table
			-- les ecritures à relettrer
			-- les ectitures delettrage forcé
			
			
			Insert Into LOADER_MATCHINGCODES_ENTRIES_TOREMOVE (
					ENTRIES_ID_ENTRIES
				)
			Select distinct
				 Coalesce(EM2.ENTRIES_ID_ENTRIES, LM.ID_ENTRIES)
			From
				LOADER_STATUS LS
				Inner Join LOADER_MATCHINGCODES LM
					On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
				Left Join ENTRIES_MATCHCOD EM1
					On LM.ID_ENTRIES = EM1.ENTRIES_ID_ENTRIES
--                     And EM1.ENTRIES_MATCHCOD_CURRENT = 1 
                    -- tester si ci dessu util
				Left Join ENTRIES_MATCHCOD EM2
					On EM1.MATCHCOD_ID_MATCHCOD = EM2.MATCHCOD_ID_MATCHCOD
                    And EM2.ENTRIES_MATCHCOD_CURRENT = 1

			Where
				LS.LOADER_STATUS_ERRCOUNT = 0
				And (
					LM.MATCHINGCODES_UNMATCH_FORCED = 1
					Or LM.MATCHINGCODES_ID_MATCHINGCODES Is Null -- non lettré
					-- code letrrage different
					Or LM.MATCHINGCODES_ID_MATCHINGCODES <> EM1.MATCHCOD_ID_MATCHCOD 
				)
				And LS.LOADER_STATUS_BATCH_NUM = batchnum
			;

		End If;

	End ;

End //
Delimiter ;

