Delimiter //
/**
 * Author:  armen
 * Created: 06 dec. 2019
 * renvoi l'id de l'ecriture avec permenant attachement = 1 
 * des écritures qui correspondent à la référence fournis et au compte
 * on prend de préférence les écriture au débit (factures) la plus ancienne
 */

drop function if exists CASH_BUSINESS_FIND_ENTRIES_WITH_ENTRIES_REF //

Create Function CASH_BUSINESS_FIND_ENTRIES_WITH_ENTRIES_REF(
		entries_ref varchar(60)
        , account_ref varchar(40)
		, id_entities Int unsigned
	)
	returns Int unsigned
Begin
	return (
			Select
				ent.ID_ENTRIES
			From
				ACCOUNTS acc
				Inner Join ENTRIES_has_ACCOUNTS eha 
					On acc.ID_ACCOUNTS = eha.ACCOUNTS_ID_ACCOUNTS
                    AND ENTRIES_PERMANENT_ATTACHEMENT = 1
				Inner Join ENTRIES ent
					On ent.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
			Where
				acc.ENTITIES_ID_ENTITIES = id_entities
                And acc.ACCOUNTS_REF = account_ref
				And ent.ENTRIES_REF = entries_ref
				And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
                
			Order By
				ent.ENTRIES_DATE, ent.ENTRIES_ENT_CREDIT
			Limit 1
		)
	;
End //
Delimiter ;