Delimiter //
Drop Procedure if exists LOADER_CREATE_TABLE_RECLAIMS//
Create Procedure LOADER_CREATE_TABLE_RECLAIMS ()
Begin
    Drop Table If Exists LOADER_RECLAIMS;
    
    Create Table LOADER_RECLAIMS (
        ID_LOADER_RECLAIMS Int Unsigned Not Null Auto_increment
        -- champs pour le traitements de l'import
        , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null

        -- champ de références
        , ACCOUNTS_REF Varchar(40) Not Null -- reference du compte. fourni par le fichier
        , ACCOUNTS_ID_ACCOUNTS Int Unsigned Null -- id du compte issu normalisation

        , ID_RECLAIMS Int Unsigned -- issu normalisation si code trouvé
        , NEW_ID_RECLAIMS Int Unsigned -- issu normalisation si code non trouvé

		, ENTRIES_ACC_EXT_REF Varchar(45) Not Null -- not null
        , ENTRIES_ID_ENTRIES Int Unsigned Null

		--
		, RECLAIMS_CREATED Datetime null
		, RECLAIMS_ORIGINAL_AMOUNT Decimal(19, 4) null
		, MEMBERS_ID_MEMBERS Int Unsigned Null
		-- RECLAIMS_IS_PARTIAL, RECLAIMS_IS_TMP


		-- simplification pour les historique
		, SUMMARIES_ID_SUMMARIES Int Unsigned Null
		--
		-- reclaim détail
		--
		
		-- status de reclamation
		, DF_RCLM_STT_ID_DF_RCLM_STT Int Unsigned Null
		, DF_RECLAIMS_STATUS_CODE Char(10)

		-- type de réclamation
		, DF_RCLM_ID_DF_RCLM Int Unsigned Null
		, DF_RECLAIMS_CODE Char(15) not null

		-- avis
		, RECLAIMS_DETAILS_OPINION Int Unsigned Null
		, DF_RECLAIMS_OPINION_CODE Char(15) null

		-- autre champ reclaim detail
		, RECLAIMS_DETAILS_BLOCKED smallint null
		, RECLAIMS_DETAILS_DEAD_LINE date null
		, RECLAIMS_DETAILS_CLOSED smallint null
		, RECLAIMS_DETAILS_AMOUNT Decimal(19, 4) null
		, RECLAIMS_DETAILS_PREV_RECLAIM  Int Unsigned Null
-- 		, RECLAIMS_DETAILS_IS_ACTIVE smallint null
		, RECLAIMS_DETAILS_NOTES Text


        -- contraintes et indexes
        , Primary Key (ID_LOADER_RECLAIMS)
        , Index (LOADER_STATUS_ID_LOADER_STATUS)
        , Index (ACCOUNTS_ID_ACCOUNTS, ENTRIES_ACC_EXT_REF)
        , Index (ENTRIES_ID_ENTRIES)
        , Index (ACCOUNTS_REF)
        , Index (DF_RECLAIMS_CODE)

    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
End //
Delimiter ;
call LOADER_CREATE_TABLE_RECLAIMS();
