Delimiter //
/**
* pour les references mappées des correspondants, cette PS ajoute un prefixe
* -> permet d'identifier les références auto des références fournis dans le schéma d'import
* à l'issue la référence sera composé ;
*  - du préfix "LOADER" : chaine de caractères fixe
*  - un "." (point) : séparateur.
*  - la référence fourni
* arguments
* In batchnum Int : num du lot
*/

Drop Procedure if exists LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_ATTCHDMBR_REF_NT_PRVD//

Create Procedure LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_ATTCHDMBR_REF_NT_PRVD (
    In batchnum int
)
Begin
    lamnmamrnp_main: Begin
        If batchnum = 0 Then
            Leave lamnmamrnp_main;
        End if;

        Set @lamnmamrnp_batchnum = batchnum;
--         Set @lamnmamrnp_batchnum = 1;
        Set @lamnmamrnp_ref = CASH_LOADER_ACCOUNTS_has_MEMBERS_GET_REF(@lamnmamrnp_batchnum);
        -- select  @lamnmamrnp_batchnum, @lamnmamrnp_ref
        Update
            LOADER_STATUS lst
            Inner Join
                LOADER_ATTACHEDMEMBERS ATTMBR
                On ATTMBR.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
        Set

            LOADER_ATTACHEDMEMBERS_AHMREF_AUTO  = (
                    @lamnmamrnp_refNotProvided := ( Case When Trim(Coalesce(ATTMBR.ACCOUNTS_has_MEMBERS_REF, '')) = '' Then 1 Else 0 End  )
                )
            , ATTMBR.ACCOUNTS_has_MEMBERS_REF = 
                Case 
                    When @lamnmamrnp_refNotProvided = 1 -- <>0
                        Then @lamnmamrnp_ref
                    When ATTMBR.ACCOUNTS_has_MEMBERS_REF like 'LOADER.%'
                        Then ATTMBR.ACCOUNTS_has_MEMBERS_REF
                    Else
                        Concat('LOADER.'
                                , @lamnmamrnp_batchnum, '.'
                                , Substring(ATTMBR.ACCOUNTS_has_MEMBERS_REF, 1, 20) 
                            )
                End
--             , LOADER_ATTACHEDMEMBERS_AHMREF_AUTO  = @lamnmamrnp_refNotProvided
--             , LOADER_ATTACHEDMEMBERS_AHMREF_AUTO = 
--                 Case 
--                     When @lamnmamrnp_refNotProvided Then 0
--                     Else 1
--                 End
        Where
            lst.LOADER_STATUS_BATCH_NUM = @lamnmamrnp_batchnum
            And lst.LOADER_STATUS_ERRCOUNT = 0
        ;



    End;

End //
Delimiter ;