Delimiter //
/**
 * Author:  armen
 * Created: 12 oct. 2016
 * check if contact is not member contacts (linked to account or entity)
 */
-- Drop Procedure if exists LOADER_MEMBERS_NORMALIZE_CHECK_NOT_ACCOUNT_CONTACT //
-- Create Procedure LOADER_MEMBERS_NORMALIZE_CHECK_NOT_ACCOUNT_CONTACT (
Drop Procedure if exists LOADER_MEMBERS_NORMALIZE_CHECK_NOT_MEMBER_CONTACT //
Create Procedure LOADER_MEMBERS_NORMALIZE_CHECK_NOT_MEMBER_CONTACT (
    In batchnum int
)
Begin
    Update LOADER_STATUS lst
	Inner Join LOADER_MEMBERS lmb
		On lst.ID_LOADER_STATUS = lmb.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 membre ne peut être créé / mise à jour car le contact est lie à un compte ou une entité :', lmb.CONTACTS_REF ) )
	Where
-- 		lmb.CONTACTS_REF In (
		Exists (
			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
				Where
					lmb.CONTACTS_REF = ct.CONTACTS_REF
					And (
						ct.ACCOUNTS_ID_ACCOUNTS Is Not Null
						or ent.ID_ENTITIES is Not null
					)
            Limit 1
		)
		And lst.LOADER_STATUS_BATCH_NUM = batchnum

	;


End //
Delimiter ;