Export Worktime

Interface Client

• api: worktime
• variant: standard

Daten für den Worktime Export

Feld	Datentyp	Nicht 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: Globale Identität
org_external_id	VARCHAR(8)		Organisation
org_acronym	VARCHAR(72)		Organisationskürzel
cc_external_id	VARCHAR(12)		Kostenstelle
worktime_date	DATE		Datum der Arbeitszeit
worktime_start	INT		Startzeit (in Minuten od. Timestamp)
worktime_pause	INT		Pausenzeit (in Minuten)
worktime_end	INT		Endzeit (in Minuten od. Timestamp)
worktime_duration	INT		Dauer (in Minuten)
tcl_external_id	VARCHAR(32)		Externe ID der Zeitart
worktime_text	VARCHAR(255)		Bemerkung/Text zur Arbeitszeit
pat_external_id	VARCHAR(32)		Externe ID der An/Abwesenheit aus tr_presence_absence_type
created_automatically	BOOLEAN		Gibt an, ob die Arbeitszeit Automatisch erstellt wurde
approved_timestamp	DATETIME		Zeitstempel der Genehmigung
confirmed_timestamp	DATETIME		Zeitstempel der Bestätigung
booking_timestamp	DATETIME		Zeitstempel der Buchung
interface_code	INT		Schnittstellen-Code
worktime_uuid	VARCHAR(40)	✓	UUID des Datensatzes
coco_external_id	VARCHAR(32)		Externe ID des Company Codes Entlastungskostenstelle des Users
wp_external_id	VARCHAR(32)		Externe ID des Arbeitsprofils

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: "worktime"
    variant: "standard"
downloads:
  - api: "worktime"
    table:
      name: "e_worktime"
      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: "worktime_date"
          db: "worktime_date"
          type: "date"
        - api: "worktime_start"
          db: "worktime_start"
          type: "integer"
        - api: "worktime_pause"
          db: "worktime_pause"
          type: "integer"
        - api: "worktime_end"
          db: "worktime_end"
          type: "integer"
        - api: "worktime_duration"
          db: "worktime_duration"
          type: "integer"
        - api: "tcl_external_id"
          db: "tcl_external_id"
          type: "string"
        - api: "worktime_text"
          db: "worktime_text"
          type: "string"
        - api: "pat_external_id"
          db: "pat_external_id"
          type: "string"
        - api: "created_automatically"
          db: "created_automatically"
          type: "tinyint"
        - 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: "interface_code"
          db: "interface_code"
          type: "integer"
        - api: "worktime_uuid"
          db: "worktime_uuid"
          type: "string"
        - api: "coco_external_id"
          db: "coco_external_id"
          type: "string"
        - api: "wp_external_id"
          db: "wp_external_id"
          type: "string"

SQL Script

CREATE TABLE e_worktime
(
    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,
    worktime_date         DATE NULL,
    worktime_start        INT NULL,
    worktime_pause        INT NULL,
    worktime_end          INT NULL,
    worktime_duration     INT NULL,
    tcl_external_id       VARCHAR(32) NULL,
    worktime_text         VARCHAR(255) NULL,
    pat_external_id       VARCHAR(32) NULL,
    created_automatically TINYINT NULL,
    approved_timestamp    DATETIME NULL,
    confirmed_timestamp   DATETIME NULL,
    booking_timestamp     DATETIME NULL,
    interface_code        INT NULL,
    worktime_uuid         VARCHAR(40) NOT NULL,
    coco_external_id      VARCHAR(32) NULL,
    wp_external_id        VARCHAR(32) NULL
);