Export Balance

Interface Client

• api: balance
• variant: standard

Daten für den Balance 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
bat_external_id	VARCHAR(32)		Externe ID der Saldoart
ped_start	DATE		Start-Datum der Periode
ped_end	DATE		End-Datum der Periode
ped_booking_date	DATE		Buchungsdatum der Periode
ped_booked	DATETIME		Zeitpunkt der Buchung
balance_year	INT		Jahr des Saldos
balance	INT		Aktueller Saldo
balance_inflow	INT		Saldo-Zugang
balance_outflow	INT		Saldo-Abgang
balance_correction	INT		Saldo-Korrektur
balance_comment	VARCHAR(255)		Kommentar zum Saldo
unbooked_absence	BOOLEAN		Gibt an, ob unverbuchte Absenzen vorhanden sind (0/1)
interface_code	INT		Schnittstellen-Code aus Zusatzdaten
balance_uuid	VARCHAR(40)	✓	UUID des Saldos
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: "balance"
    variant: "standard"
downloads:
  - api: "balance"
    table:
      name: "e_balance"
      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: "bat_external_id"
          db: "bat_external_id"
          type: "string"
        - api: "ped_start"
          db: "ped_start"
          type: "date"
        - api: "ped_end"
          db: "ped_end"
          type: "date"
        - api: "ped_booking_date"
          db: "ped_booking_date"
          type: "date"
        - api: "ped_booked"
          db: "ped_booked"
          type: "time"
        - api: "balance_year"
          db: "balance_year"
          type: "integer"
        - api: "balance"
          db: "balance"
          type: "integer"
        - api: "balance_inflow"
          db: "balance_inflow"
          type: "integer"
        - api: "balance_outflow"
          db: "balance_outflow"
          type: "integer"
        - api: "balance_correction"
          db: "balance_correction"
          type: "integer"
        - api: "balance_comment"
          db: "balance_comment"
          type: "string"
        - api: "unbooked_absence"
          db: "unbooked_absence"
          type: "boolean"
        - api: "interface_code"
          db: "interface_code"
          type: "integer"
        - api: "balance_uuid"
          db: "balance_uuid"
          type: "string"
        - api: "wp_external_id"
          db: "wp_external_id"
          type: "string"
        - api: "coco_external_id"
          db: "coco_external_id"
          type: "string"

SQL Script

CREATE TABLE e_balance (
    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,
    bat_external_id    VARCHAR(32) NULL,
    ped_start          DATE NULL,
    ped_end            DATE NULL,
    ped_booking_date   DATE NULL,
    ped_booked         DATETIME NULL,
    balance_year       INT NULL,
    balance            INT NULL,
    balance_inflow     INT NULL,
    balance_outflow    INT NULL,
    balance_correction INT NULL,
    balance_comment    VARCHAR(255) NULL,
    unbooked_absence   TINYINT NULL,
    interface_code     INT NULL,
    balance_uuid       VARCHAR(40) NOT NULL,
    coco_external_id   VARCHAR(32) NULL,
    wp_external_id     VARCHAR(32) NULL
);