Delimiter //
/**
 * Map the given Account Ref to Account Id
 * @param int @lcnma_batchnum
 * @author Ben
 */
Drop Procedure If Exists LOADER_CREDITLIMIT_NORMALIZE_MAP_ACCOUNTS//
Create Procedure LOADER_CREDITLIMIT_NORMALIZE_MAP_ACCOUNTS(
    In batchnum Int
)
Begin
    Set @lcnma_batchnum = batchnum;
    -- pas d'info pour le batch 0, on utilise un greatest avec 1
    Set @lcnma_idEntities = GET_LOADER_INFO('ID_ENTITIES',Greatest( @lcnma_batchnum, 1));

    lcnma_findAccounts: Begin
        -- on doit obligatoirement appeler cette PS dans TOUS les lots DONT le 0, pour init la table est les données
        call LOADER_FIND_ACCOUNTS_BY_SIREN (
                    @lcnma_batchnum -- In batchnum Int
                    , 'LOADER_CREDITLIMIT' -- , In tableSrc Varchar(50)
                    , 'ID_LOADER_CREDITLIMIT' -- , In fieldIdTableSrc Varchar(50)
                    , 'CONTACTS_BUSINESS_SIRET' -- , In fieldSiren Varchar(50)
                    , 'LOADER_STATUS_ID_LOADER_STATUS' -- , In fieldIdLoaderStatus Varchar(50)
                    , 'ACCOUNTS_REF' -- , In fieldAccountRef Varchar(50)
                    , '0' -- champ flag : provisoire en attendant test, remplacer par ci dessous
--                     , 'LOADER_CREDITLIMIT_MAIN_CONTACT_ONLY' -- champ flag : tous les contacts/ main contacts only
                    , 'TMP_LCNMA_ACCOUNTS_FOUND_BY_SIREN' -- , In tableAccountFound Varchar(50)
                    , 1 -- , In clearOutputTable SmallInt
                )
            ;
    End ;
    lcnma_cpFoundAccounts: Begin
        Insert Into LOADER_CREDITLIMIT_ACCOUNTS (
                LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT
                , LOADER_STATUS_ID_LOADER_STATUS, ACCOUNTS_ID_ACCOUNTS
            )
        Select
            LOADER_BASE_TABLE_ID, LOADER_STATUS_ID_LOADER_STATUS
            , ACCOUNTS_ID_ACCOUNTS
        From TMP_LCNMA_ACCOUNTS_FOUND_BY_SIREN
        ;
    End;

    lcnma_updterr: Begin

        -- Maj des erreurs
        Update
            LOADER_STATUS STS
            Inner Join LOADER_CREDITLIMIT LDR
                On LDR.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
        Set
            STS.LOADER_STATUS_ERRCOUNT = STS.LOADER_STATUS_ERRCOUNT + 1
            , STS.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(STS.LOADER_STATUS_ERRMSGS
                                                        , Case When Trim(Coalesce(LDR.ACCOUNTS_REF, '')) <> ''
                                                            Then Concat('Référence de compte inexistante :', LDR.ACCOUNTS_REF ) 
                                                            Else Concat('Siren/Siret non trouvé :', LDR.CONTACTS_BUSINESS_SIRET ) 
                                                          End

                                                    ) 

        Where 
            STS.LOADER_STATUS_BATCH_NUM = @lcnma_batchnum
            And STS.LOADER_STATUS_ERRCOUNT = 0 -- les lignes erronnées n'on pas été traité ici
            And Not Exists (
                Select FOUNDACC.LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT
                From LOADER_CREDITLIMIT_ACCOUNTS FOUNDACC
                Where LDR.ID_LOADER_CREDITLIMIT =  FOUNDACC.LOADER_CREDITLIMIT_ID_LOADER_CREDITLIMIT
                Limit 1
            )
        ;
    End;
End//
Delimiter ;