Delimiter //
/**
 * Author:  armen
 * Created: 2 déc. 2019
 * doit être lancé avant LOADER_ACCOUNTS_PENALTIES_INSERT
 */

Drop Procedure if exists LOADER_ACCOUNTS_PENALTIES_DELETE//
Create Procedure LOADER_ACCOUNTS_PENALTIES_DELETE (
    In batchnum int
)
Begin 
    set @lapd_batchnum = batchnum;
    Delete tar
-- select     * -- pour debug
    From
        ACCOUNTS_has_DF_PENALTIES_RULES tar
        Inner Join DF_PENALTIES_RULES dpr
            On tar.DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = dpr.ID_DF_PENALTIES_RULES
        Inner Join DF_PENALTIES_RULES_TYPES dprt
            On dpr.DF_PENALTIES_RULES_TYPES_ID_DF_PENALTIES_RULES_TYPES = dprt.ID_DF_PENALTIES_RULES_TYPES
    Where exists(
            Select
                la.ID_ACCOUNTS
            From LOADER_STATUS ls
                Inner Join LOADER_ACCOUNTS la
                    On ls.ID_LOADER_STATUS = la.ID_LOADER_ACCOUNTS
            Where
                la.ID_ACCOUNTS Is Not Null
                And ls.LOADER_STATUS_ERRCOUNT = 0
                and ls.LOADER_STATUS_BATCH_NUM = @lapd_batchnum
                and tar.ACCOUNTS_ID_ACCOUNTS = la.ID_ACCOUNTS
                And (
                    la.ID_DF_PENALTIES_RULES_CODE_FR4416 Is Not Null
                    Or la.ID_DF_PENALTIES_RULES_CODE_FRLTPNCSE Is Not Null

                )
            Limit 1
        )
    ;
End //

Delimiter ;
