Delimiter //
/**
 * Author:  armen
 * Created: 21 juil. 2020
 */
/**
 * Credit limit data update => history
 * @param int batchnum
 * @author Ben
 */
Drop Procedure If Exists LOADER_CREDITPROVIDERS_CREDITSPROVIDERS_INSERT//

Create Procedure LOADER_CREDITPROVIDERS_CREDITSPROVIDERS_INSERT (
    In batchnum Int
    , In lastId Int
)
Begin
    set @lcpci_batchnum = batchnum;
    set @lcpci_lastId = lastId;
    Insert Into CREDITS_PROVIDERS (
        ID_CREDITS_PROVIDERS
        , ACCOUNTS_ID_ACCOUNTS
        , MEMBERS_ID_MEMBERS
        , CREDITS_PROVIDERS_REF
        , CREDITS_PROVIDERS_LABEL
        , CREDITS_PROVIDERS_COMMENT
        , CREDITS_PROVIDERS_CREDIT_LIMIT
        , CREDITS_PROVIDERS_SCORE
        , CREDITS_PROVIDERS_CREATE_DATE
        , CREDITS_PROVIDERS_VAL_DATE
    )  
    Select 
        src.ID_CREDITS_PROVIDERS_NEW + @lcpci_lastId
        , src.ACCOUNTS_ID_ACCOUNTS
        , null
        , tbl1.CREDITS_PROVIDERS_REF
        , tbl1.CREDITS_PROVIDERS_LABEL
        , tbl1.CREDITS_PROVIDERS_COMMENT
        , tbl1.CREDITS_PROVIDERS_CREDIT_LIMIT
        , tbl1.CREDITS_PROVIDERS_SCORE
        , tbl1.CREDITS_PROVIDERS_CREATE_DATE
        , NullIf(tbl1.CREDITS_PROVIDERS_VAL_DATE, '0000-00-00')
    From  
        LOADER_CREDITPROVIDERS_ACCOUNTS src  
        Inner Join LOADER_CREDITPROVIDERS tbl1
            On src.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS = tbl1.ID_LOADER_CREDITPROVIDERS
        Inner Join LOADER_STATUS lst  
            On tbl1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
    Where lst.LOADER_STATUS_ERRCOUNT = 0  
        And lst.LOADER_STATUS_BATCH_NUM =  @lcpci_batchnum
--         And Coalesce(src.ID_CREDITS_PROVIDERS_NEW, 0) <> 0
        And src.ID_CREDITS_PROVIDERS_NEW Is Not Null
        And src.ID_CREDITS_PROVIDERS Is Null
        And tbl1.LOADER_CREDITPROVIDERS_ROW_REJECTED = 0
    ;
End //

Delimiter ;

