Delimiter //
/**
 * Author:  armen
 * Created: 08 octobre 2021
 */
Drop Procedure if exists LOADER_ACCOUNTS_COPY_ACCOUNTSSTATUS_INSERT//
Create Procedure LOADER_ACCOUNTS_COPY_ACCOUNTSSTATUS_INSERT (
    In batchnum int
    , In lastId Int
)
Begin
    set @lacasi_lastId = lastId;
    set @lacasi_batchnum = batchnum;

    -- insertion des nouveau compte avec status
    Insert Into ACCOUNTS_STATUS (
            ACCOUNTS_ID_ACCOUNTS
            , DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS
            , MEMBERS_ID_MEMBERS
            , ACCOUNTS_STATUS_VAL_DATE
            , ACCOUNTS_STATUS_COMMENT
            , ACCOUNTS_STATUS_CREATION_DATE
            , ACCOUNTS_STATUS_UPDATE_DATE
            , ACCOUNTS_STATUS_UPDATE_SRC
            , ACCOUNTS_STATUS_DATE_UPDT_IMPORT
        ) 
    Select 
        src.ID_ACCOUNTS_NEW + @lacasi_lastId ID_ACCOUNTS
        , src.DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS
        , 1
        , Coalesce(src.ACCOUNTS_STATUS_VAL_DATE, curdate())
        , src.ACCOUNTS_STATUS_COMMENT
        , curdate()
        , curdate()
        , 2
        , curdate()
    From  
        LOADER_ACCOUNTS src  
        Inner Join LOADER_STATUS lst  
            On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
    Where 1 = 1  
        And lst.LOADER_STATUS_ERRCOUNT = 0  
        And lst.LOADER_STATUS_BATCH_NUM =  @lacasi_batchnum
        and Coalesce(src.ID_ACCOUNTS_NEW, 0) <> 0 
        And src.DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS Is Not NULL
    ;


    -- insertion des nouveaux status des comptes existants
    -- => compte existant, status non créés
    Insert Into ACCOUNTS_STATUS (
            ACCOUNTS_ID_ACCOUNTS
            , DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS
            , MEMBERS_ID_MEMBERS
            , ACCOUNTS_STATUS_VAL_DATE
            , ACCOUNTS_STATUS_COMMENT
            , ACCOUNTS_STATUS_CREATION_DATE
            , ACCOUNTS_STATUS_UPDATE_DATE
            , ACCOUNTS_STATUS_UPDATE_SRC
            , ACCOUNTS_STATUS_DATE_UPDT_IMPORT
        ) 
    Select 
        src.ID_ACCOUNTS
        , src.DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS
        , 1
        , Coalesce(src.ACCOUNTS_STATUS_VAL_DATE, curdate())
        , src.ACCOUNTS_STATUS_COMMENT
        , curdate()
        , curdate()
        , 2
        , curdate()
    From  
        LOADER_ACCOUNTS src  
        Inner Join LOADER_STATUS lst  
            On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
        Left Join ACCOUNTS_STATUS tchk
            On src.ID_ACCOUNTS = tchk.ACCOUNTS_ID_ACCOUNTS
    Where 1 = 1  
        And lst.LOADER_STATUS_ERRCOUNT = 0  
        And lst.LOADER_STATUS_BATCH_NUM =  @lacasi_batchnum
        and ID_ACCOUNTS Is Not Null 
        And src.DF_ACCOUNTS_STATUS_ID_DF_ACCOUNTS_STATUS Is Not NULL
        And tchk.ACCOUNTS_ID_ACCOUNTS Is Null
    ;


End //

Delimiter ;


