SNCControl/A_Daten/DB Create Scripts/ZdbSNCDatawarehouseDATEV_Tabellen.sql

USE [ZdbSNCDatawarehouseDATEV]
GO

/****** Object:  Table [dbo].[dKonto]    Script Date: 20.01.2022 10:43:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dKonto](
	[Mandant ID] [varchar](3) NOT NULL,
	[Konto_Nr] [varchar](10) NOT NULL,
	[Konto_Bezeichnung] [varchar](255) NULL,
	[Konto_Bezeichnung-Nr.] [varchar](63) NULL,
	[Konto_Nr-Bez.] [varchar](63) NULL,
	[Konto_Art] [varchar](255) NULL,
	[Kostenstelle] [varchar](50) NULL,
	[STK] [int] NULL,
	[Konto_1] [varchar](50) NULL,
	[Konto_2] [varchar](50) NULL,
	[Konto_3] [varchar](50) NULL,
	[Konto_4] [varchar](50) NULL,
	[Konto_5] [varchar](50) NULL,
	[Ebene1] [varchar](100) NULL,
	[Ebene2] [varchar](100) NULL,
	[Ebene3] [varchar](100) NULL,
	[Ebene4] [varchar](100) NULL,
	[Ebene5] [varchar](100) NULL,
	[Ebene6] [varchar](100) NULL,
	[Ebene7] [varchar](100) NULL,
	[Ebene8] [varchar](100) NULL,
	[Ebene9] [varchar](100) NULL,
	[Ebene10] [varchar](100) NULL,
	[Ebene11] [varchar](100) NULL,
	[Ebene12] [varchar](100) NULL,
	[Ebene13] [varchar](100) NULL,
	[Ebene14] [varchar](100) NULL,
	[Ebene15] [varchar](100) NULL,
	[Ebene16] [varchar](100) NULL,
	[Ebene17] [varchar](100) NULL,
	[Ebene18] [varchar](100) NULL,
	[Ebene19] [varchar](100) NULL,
	[Ebene20] [varchar](100) NULL,
	[Ebene21] [varchar](100) NULL,
	[Ebene22] [varchar](100) NULL,
	[Ebene23] [varchar](100) NULL,
	[Ebene24] [varchar](100) NULL,
	[Ebene25] [varchar](100) NULL,
	[Ebene26] [varchar](100) NULL,
	[Ebene27] [varchar](100) NULL,
	[Ebene28] [varchar](100) NULL,
	[Ebene29] [varchar](100) NULL,
	[Ebene30] [varchar](100) NULL,
	[Ebene31] [varchar](100) NULL,
	[Ebene32] [varchar](100) NULL,
	[Ebene33] [varchar](100) NULL,
	[Ebene34] [varchar](100) NULL,
	[Ebene35] [varchar](100) NULL,
	[Ebene36] [varchar](100) NULL,
	[Ebene37] [varchar](100) NULL,
	[Ebene38] [varchar](100) NULL,
	[Ebene39] [varchar](100) NULL,
	[Ebene40] [varchar](100) NULL,
	[Ebene41] [varchar](100) NULL,
	[Ebene42] [varchar](100) NULL,
	[Ebene43] [varchar](100) NULL,
	[Ebene44] [varchar](100) NULL,
	[Ebene45] [varchar](100) NULL,
	[Ebene46] [varchar](100) NULL,
	[Ebene47] [varchar](100) NULL,
	[Ebene48] [varchar](100) NULL,
	[Ebene49] [varchar](100) NULL,
	[Ebene50] [varchar](100) NULL,
	[Ebene51] [varchar](100) NULL,
	[Ebene52] [varchar](100) NULL,
	[Ebene53] [varchar](100) NULL,
	[Ebene54] [varchar](100) NULL,
	[Ebene55] [varchar](100) NULL,
	[Ebene56] [varchar](100) NULL,
	[Ebene57] [varchar](100) NULL,
	[Ebene58] [varchar](100) NULL,
	[Ebene59] [varchar](100) NULL,
	[Ebene60] [varchar](100) NULL,
	[Ebene61] [varchar](100) NULL,
	[Ebene62] [varchar](100) NULL,
	[Ebene63] [varchar](100) NULL,
	[Ebene64] [varchar](100) NULL,
	[Ebene65] [varchar](100) NULL,
	[Ebene66] [varchar](100) NULL,
	[Ebene67] [varchar](100) NULL,
	[Ebene68] [varchar](100) NULL,
	[Ebene69] [varchar](100) NULL,
	[Ebene70] [varchar](100) NULL,
	[Ebene71] [varchar](100) NULL,
	[Ebene72] [varchar](100) NULL,
	[Ebene73] [varchar](100) NULL,
	[Ebene74] [varchar](100) NULL,
	[Ebene75] [varchar](100) NULL,
	[Ebene76] [varchar](100) NULL,
	[Ebene77] [varchar](100) NULL,
	[Ebene78] [varchar](100) NULL,
	[Ebene79] [varchar](100) NULL,
	[Ebene80] [varchar](100) NULL,
	[Ebene81] [varchar](100) NULL,
	[Ebene82] [varchar](100) NULL,
	[Ebene83] [varchar](100) NULL,
	[Ebene84] [varchar](100) NULL,
	[Ebene85] [varchar](100) NULL,
	[Ebene86] [varchar](100) NULL,
	[Ebene87] [varchar](100) NULL,
	[Ebene88] [varchar](100) NULL,
	[Ebene89] [varchar](100) NULL,
	[Ebene90] [varchar](100) NULL,
	[Ebene91] [varchar](100) NULL,
	[Ebene92] [varchar](100) NULL,
	[Ebene93] [varchar](100) NULL,
	[Ebene94] [varchar](100) NULL,
	[Ebene95] [varchar](100) NULL,
	[Ebene96] [varchar](100) NULL,
	[Ebene97] [varchar](100) NULL,
	[Ebene98] [varchar](100) NULL,
	[Ebene99] [varchar](100) NULL,
	[Ebene100] [varchar](100) NULL,
	[Kontobeschriftung] [varchar](255) NULL,
 CONSTRAINT [PK_dKonto] PRIMARY KEY CLUSTERED 
(
	[Mandant ID] ASC,
	[Konto_Nr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[fBuchungsbelege]    Script Date: 20.01.2022 10:43:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[fBuchungsbelege](
	[Mandant ID] [varchar](3) NULL,
	[Konto] [int] NULL,
	[Belegdatum] [datetime] NULL,
	[BU-Schlüssel] [varchar](50) NULL,
	[Gegenkonto] [int] NULL,
	[Buchungstext] [varchar](100) NULL,
	[Belegfeld 1] [varchar](50) NULL,
	[BSNr] [varchar](50) NULL,
	[Herkunft-Kz] [varchar](50) NULL,
	[Belegfeld 2] [varchar](50) NULL,
	[KOST1 - Kostenstelle] [varchar](50) NULL,
	[KOST2 - Kostenstelle] [varchar](50) NULL,
	[Kost-Menge] [int] NULL,
	[Stück] [int] NULL,
	[Buchungs GUID] [varchar](50) NOT NULL,
	[Leistungsdatum] [date] NULL,
	[Datum Zuord  Steuerperiode] [date] NULL,
	[Konto_Datev_Kontenbeschriftung] [numeric](18, 0) NULL,
	[Kontobeschriftung_Datev_Kontenbeschriftung] [varchar](255) NULL,
	[Kostenstelle_Traeger] [varchar](50) NULL,
	[Kurzbezeichnung] [varchar](50) NULL,
	[Langbezeichnung] [varchar](100) NULL,
	[Konto_Zeichen] [varchar](10) NULL,
	[Länge_Konto_Zeichen] [int] NULL,
	[Standort_ID] [varchar](8) NULL,
	[Marke_ID] [varchar](8) NULL,
	[KST_ID] [varchar](8) NULL,
	[Absatzkanal_ID] [varchar](8) NULL,
	[Antriebsart_ID] [varchar](8) NULL,
	[Bookkeep Date] [datetime] NULL,
	[Kostenträger_ID] [varchar](11) NULL,
	[Acct Nr] [varchar](19) NULL,
	[Umsatz Soll] [numeric](18, 5) NULL,
	[Umsatz Haben] [numeric](18, 5) NULL,
	[Betrag] [numeric](18, 5) NULL,
	[PK_Auto] [int] NOT NULL,
	[Marke_mit_Bez] [varchar](63) NULL,
	[Kostenträger_mit_Bez] [varchar](106) NULL,
	[Absatzkanal_mit_Bez] [varchar](63) NULL,
	[Text] [varchar](153) NULL,
	[KST_mit_Bez] [varchar](63) NULL,
 CONSTRAINT [PK_fBuchungsbelege] PRIMARY KEY CLUSTERED 
(
	[Buchungs GUID] ASC,
	[PK_Auto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[fOffenePosten]    Script Date: 20.01.2022 10:43:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[fOffenePosten](
	[Kontonummer] [int] NOT NULL,
	[Name (Adressattyp Unternehmen)] [varchar](50) NULL,
	[Rechnungs-Nr] [varchar](32) NOT NULL,
	[Fälligkeit] [int] NULL,
	[Betrag Soll] [numeric](28, 8) NULL,
	[Betrag Haben] [numeric](28, 8) NULL,
	[Saldo] [numeric](28, 8) NULL,
	[S H Saldo] [varchar](10) NULL,
	[Gegenkonto] [int] NULL,
	[Fällig] [int] NULL,
	[Ausgleich] [varchar](10) NULL,
	[Belegfeld 2] [varchar](100) NULL,
	[Kz ] [varchar](2) NULL,
	[Buchungstext] [varchar](60) NULL,
	[USt %] [numeric](28, 8) NULL,
	[Stapel-Nr ] [varchar](20) NOT NULL,
	[BSNr] [int] NOT NULL,
	[Postensperre] [int] NULL,
	[KOST1 - Kostenstelle] [varchar](50) NULL,
	[KOST2 - Kostenstelle] [varchar](50) NULL,
	[Postleitzahl] [varchar](7) NULL,
	[Ort] [varchar](50) NULL,
	[Kurzbezeichnung] [varchar](50) NULL,
	[Mahnstufe] [int] NULL,
	[Mandant ID] [varchar](3) NOT NULL,
	[Länge_Rechnungsnummer] [int] NULL,
	[Beleg] [varchar](106) NULL,
	[Kunde] [varchar](63) NULL,
	[Standort] [varchar](1) NULL,
	[Konto_1_Stelle] [varchar](1) NULL,
	[Fälligkeitsdatum] [date] NULL,
	[Invoice Date] [date] NULL,
	[Rechnungsdatum] [date] NOT NULL,
	[Fälligkeit_Jahr] [varchar](4) NULL,
	[Fälligkeit_Monat] [varchar](2) NULL,
	[Fälligkeit_Tag] [varchar](2) NULL,
	[Fälligkeit_Datum_Zahl] [int] NULL,
	[Fälligkeit_Zahl] [int] NULL,
	[Fälligkeit_Zeichen] [nvarchar](8) NULL,
	[Anz_Stellen_Fälligkeit] [int] NULL,
	[Tage] [int] NULL,
	[Staffel] [varchar](13) NULL,
	[Sel Name] [varchar](47) NULL,
	[Kostenstelle] [varchar](15) NULL,
	[Bereich] [varchar](47) NULL,
	[Forderungsart] [varchar](47) NULL,
	[Abwarten] [varchar](47) NULL,
	[Gesamt offen KD (Info)] [numeric](18, 5) NULL,
	[offen] [numeric](18, 5) NULL,
	[Rechnungsbetrag] [numeric](18, 5) NULL,
 CONSTRAINT [PK_fOffenePosten] PRIMARY KEY CLUSTERED 
(
	[Kontonummer] ASC,
	[Rechnungs-Nr] ASC,
	[Stapel-Nr ] ASC,
	[BSNr] ASC,
	[Mandant ID] ASC,
	[Rechnungsdatum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO