Delimiter //
/**
 * Author:  armen
 * Created: 24 nov. 2017
 * test si un contact et lié à un membre ou un entité
 */
Drop Procedure if exists LOADER_CONTACTS_NORMALIZE_CHECK_NOT_ACCOUNT_CONTACTS //
Create Procedure LOADER_CONTACTS_NORMALIZE_CHECK_NOT_ACCOUNT_CONTACTS (
    In batchnum int
)
Begin
    Update LOADER_STATUS lst
	Inner Join LOADER_MEMBERS lct
		On lst.ID_LOADER_STATUS = lct.LOADER_STATUS_ID_LOADER_STATUS
	Set
		lst.LOADER_STATUS_ERRCOUNT = lst.LOADER_STATUS_ERRCOUNT + 1
        , lst.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(lst.LOADER_STATUS_ERRMSGS, Concat('Le contact ne peut être créé / mise à jour car le contact est lie à un membre ou une entité :', lct.CONTACTS_REF ) )
	Where
		lct.CONTACTS_REF In (
			select ct.CONTACTS_REF
				From CONTACTS ct
					Left Join (
						CONTACTS_BUSINESS cb
						Inner Join ENTITIES ent
							On cb.ID_CONTACTS_BUSINESS =ent.CONT_BUSINESS_ID_CONT_BUSINESS
					) On ct.ID_CONTACTS = cb.CONTACTS_ID_CONTACTS
                    Left Join (
                        CONTACTS_INDIVIDUAL ci
                        Inner Join MEMBERS mbr
                            On ci.ID_CONTACTS_INDIVIDUAL = mbr.CONTACT_INDIV_ID_CONTACT_INDIV
                    ) On ct.ID_CONTACTS = ci.CONTACTS_ID_CONTACTS
				Where
					lct.CONTACTS_REF = ct.CONTACTS_REF
					And (
						mbr.ID_MEMBERS Is Not Null
						or ent.ID_ENTITIES is Not null
					)
		)
		And lst.LOADER_STATUS_BATCH_NUM = batchnum

	;


End //
Delimiter ;