Export Absence Planning

Interface Client

• api: absence_planning
• variant: standard

Daten für den Absence Planning Export

Column Name	Data type	Not Null	Bemerkung
firstname	NVARCHAR(50)		Vorname
lastname	NVARCHAR(50)		Nachname
personnel_number	VARCHAR(32)		Personalnummer
usr_external_id	VARCHAR(32)		Externe ID: identifiziert den User
usr_external_id2	VARCHAR(32)		Zweite externe ID: Zusätzliche Identifikation, rein informativ
gid	VARCHAR(32)		GID
org_external_id	VARCHAR(8)		Organisation
org_acronym	VARCHAR(72)		Organisationskürzel
cc_external_id	VARCHAR(12)		Kostenstelle
interface_code	INT		Schnittstellen-Code aus Zusatzdaten
absence_from	DATE		Start-Datum der Absenz
absence_to	DATE		End-Datum der Absenz
absence_in_minutes	INT		Dauer der Absenz in Minuten
absence_in_percent	INT		Dauer der Absenz relativ zur Total möglichen Absenz-Dauer (zwischen 0 und 1)
absence_text	VARCHAR(250)		Kommentar zur Absenz
absence_split	BIT		Gibt an, ob die Absenz gesplittet wurde
pat_external_id	VARCHAR(32)		Externe ID der Abwesenheitsart
tcl_external_id	VARCHAR(32)		Externe ID der Zeitklasse
approved_timestamp	DATETIME		Zeitpunkt, an dem die Abwesenheit bewilligt wurde
confirmed_timestamp	DATETIME		Zeitpunkt, an dem die Abwesenheit bestätigt wurde
booking_timestamp	DATETIME		Zeitpunkt, an dem die Abwesenheit verbucht wurde
absence_id	int		ID der Abwesenheit
absence_uuid	varchar(40)		UUID der Abwesenheit
cr_user	VARCHAR(50)		User, welcher die Abwesenheit erstellt hat
cr_time	DATETIME		Zeitpunkt, an dem die Abwesenheit erstellt wurde
mut_user	VARCHAR(50)		User, welcher die Abwesenheit zuletzt bearbeitet hat
mut_time	DATETIME		Zeitpunkt, an dem die Abwesenheit zuletzt bearbeitet wurde
coco_external_id	varchar(32)		Externe ID des Company Codes
wp_external_id	varchar(32)		Externe ID des Arbeitsprofils
planned_absence_time_id	INT		ID der geplanten Abwesenheitszeit
planned_absence_time_day	DATE		Tag der geplanten Abwesenheitszeit
planned_absence_time_start	INT		Start der geplanten Abwesenheitszeit (in Minuten seit Tagesbeginn)
planned_absence_time_end	INT		Ende der geplanten Abwesenheitszeit (in Minuten seit Tagesbeginn)

DB-Tabellen-Struktur

In den folgenden Tabs finden Sie fertige Konfigurationen und Skripte zur Erstellung der erforderlichen Datenstrukturen für verschiedene Technologien.

yaml

exp:
  - api: "absence_planning"
    variant: "standard"
downloads:
  - api: "absence_planning"
    table:
      name: "e_absence_planning"
      columns:
        - api: "firstname"
          db: "firstname"
          type: "string"
        - api: "lastname"
          db: "lastname"
          type: "string"
        - api: "personnel_number"
          db: "personnel_number"
          type: "string"
        - api: "usr_external_id"
          db: "usr_external_id"
          type: "string"
        - api: "usr_external_id2"
          db: "usr_external_id2"
          type: "string"
        - api: "gid"
          db: "gid"
          type: "string"
        - api: "org_external_id"
          db: "org_external_id"
          type: "string"
        - api: "org_acronym"
          db: "org_acronym"
          type: "string"
        - api: "cc_external_id"
          db: "cc_external_id"
          type: "string"
        - api: "interface_code"
          db: "interface_code"
          type: "integer"
        - api: "absence_from"
          db: "absence_from"
          type: "date"
        - api: "absence_to"
          db: "absence_to"
          type: "date"
        - api: "absence_in_minutes"
          db: "absence_in_minutes"
          type: "integer"
        - api: "absence_in_percent"
          db: "absence_in_percent"
          type: "integer"
        - api: "absence_text"
          db: "absence_text"
          type: "string"
        - api: "absence_split"
          db: "absence_split"
          type: "tinyint"
        - api: "pat_external_id"
          db: "pat_external_id"
          type: "string"
        - api: "tcl_external_id"
          db: "tcl_external_id"
          type: "string"
        - api: "approved_timestamp"
          db: "approved_timestamp"
          type: "time"
        - api: "confirmed_timestamp"
          db: "confirmed_timestamp"
          type: "time"
        - api: "booking_timestamp"
          db: "booking_timestamp"
          type: "time"
        - api: "absence_id"
          db: "absence_id"
          type: "integer"
        - api: "absence_uuid"
          db: "absence_uuid"
          type: "string"
        - api: "cr_user"
          db: "cr_user"
          type: "string"
        - api: "cr_time"
          db: "cr_time"
          type: "time"
        - api: "mut_user"
          db: "mut_user"
          type: "string"
        - api: "mut_time"
          db: "mut_time"
          type: "time"
        - api: "wp_external_id"
          db: "wp_external_id"
          type: "string"
        - api: "coco_external_id"
          db: "coco_external_id"
          type: "string"
        - api: "planned_absence_time_id"
          db: "planned_absence_time_id"
          type: "integer"
        - api: "planned_absence_time_day"
          db: "planned_absence_time_day"
          type: "date"
        - api: "planned_absence_time_start"
          db: "planned_absence_time_start"
          type: "integer"
        - api: "planned_absence_time_end"
          db: "planned_absence_time_end"
          type: "integer"

SQL Script

CREATE TABLE e_absence_planning (
    firstname NVARCHAR(50) NULL,
    lastname NVARCHAR(50) NULL,
    personnel_number VARCHAR(32) NULL,
    usr_external_id VARCHAR(32) NULL,
    usr_external_id2 VARCHAR(32) NULL,
    gid VARCHAR(32) NULL,
    org_external_id VARCHAR(8) NULL,
    org_acronym VARCHAR(72) NULL,
    cc_external_id VARCHAR(12) NULL,
    interface_code INT NULL,
    absence_from DATE NULL,
    absence_to DATE NULL,
    absence_in_minutes INT NULL,
    absence_in_percent INT NULL,
    absence_text VARCHAR(250) NULL,
    absence_split BIT NULL,
    pat_external_id VARCHAR(32) NULL,
    tcl_external_id VARCHAR(32) NULL,
    approved_timestamp DATETIME NULL,
    confirmed_timestamp DATETIME NULL,
    booking_timestamp DATETIME NULL,
    absence_id int NULL,
    absence_uuid varchar(40) NOT NULL,
    cr_user VARCHAR(50) NULL,
    cr_time DATETIME NULL,
    mut_user VARCHAR(50) NULL,
    mut_time DATETIME NULL,
    coco_external_id   varchar(32),
    wp_external_id     varchar(32),
    planned_absence_time_id INT NULL,
    planned_absence_time_day DATE NULL,
    planned_absence_time_start INT NULL,
    planned_absence_time_end INT NULL
);