Technische Dokumentation Modul DEBCRED

Inhaltsverzeichnis

Informationen

Dokumentationstyp: Vollständig (zur Endanwender Dokumentation)
Erstellt am: 16.04.2024
Modulversion: 12
Modultyp: Basismodul
Beschreibung: Modul für Offene Posten
Benötigt Administrator Rechte: Nein
Ab OCT Version: 5.10.11

Tabellen

global.tDEBCRED_Accounts

Dimension table to store informations about debtor and creditor acccounts. Table contains all accounts per company with current (latest) name.

# Name Datentyp NULL Identity Berechnet
1 RowKey BIGINT highlight_off check_circle highlight_off
Technical primary Key for row identity.
2 AccountKey NVARCHAR(165) highlight_off highlight_off check_circle
Unique account key from the source system consisting of DataSourceKey | CompanyID | AccountID | AccountGroupCODE.
3 DataSourceKey INT highlight_off highlight_off highlight_off
Technical primary Key of datasource. DataSourceKey is used to create a unique identifier when paired with CompanyID.
4 CompanyID NVARCHAR(50) highlight_off highlight_off highlight_off
OCT identifier for the company.
5 AccountID NVARCHAR(50) highlight_off highlight_off highlight_off
Identifier for the debtor or creditor account.
6 AccountTypeCODE NVARCHAR(50) highlight_off highlight_off highlight_off
Classification for an account within a group of Accounts (DEBITOR or KREDITOR).
7 AccountName NVARCHAR(255) highlight_off highlight_off highlight_off
Name of the Account.
8 Street NVARCHAR(255) check_circle highlight_off highlight_off
Street and house number of customer or vendor.
9 PostalCode NVARCHAR(255) check_circle highlight_off highlight_off
Postal code of customer or vendor.
10 City NVARCHAR(255) check_circle highlight_off highlight_off
City of customer or vendor.
11 CountryIsoCODE NVARCHAR(50) check_circle highlight_off highlight_off
ISO 3166-1 alpha-2 – two-letter country codes.
12 ICPartnerID NVARCHAR(50) check_circle highlight_off highlight_off
intercompany identifier of the account.
13 CustomValuesJSON NVARCHAR(MAX) check_circle highlight_off highlight_off
Custom field to store any customer individual informations.
14 CustomValuesJSONUser NVARCHAR(MAX) check_circle highlight_off highlight_off
CustomValuesJSONUser
15 OwnerCODE NVARCHAR(50) highlight_off highlight_off highlight_off
OwnerCODE
16 MappingKey INT check_circle highlight_off highlight_off
MappingKey
17 UpdatedFlag INT highlight_off highlight_off highlight_off
UpdatedFlag

Referenziert von

integration.tDEBCRED_Accounts

Dimension table to store informations about debtor and creditor acccounts. Table contains all accounts per company with current (latest) name.

# Name Datentyp NULL Identity Berechnet
1 RowKey BIGINT highlight_off check_circle highlight_off
Technical primary Key for row identity.
2 DataSourceKey INT highlight_off highlight_off highlight_off
Number of the Datasoure. DataSourceKey is used to create a unique identifier when paired with CompanyID.
3 CompanyID NVARCHAR(50) highlight_off highlight_off highlight_off
Company ID which is used in the source database.
4 AccountID NVARCHAR(50) highlight_off highlight_off highlight_off
Identifier for the customer or vendor account.
5 AccountTypeCODE NVARCHAR(50) highlight_off highlight_off highlight_off
Classification for an account, e.g. DEBITOR or KREDITOR. When no classification is given from source system, the classification will be done later through agent.
6 AccountName NVARCHAR(255) highlight_off highlight_off highlight_off
Name of the Account. If name changes in time the last name.
7 Street NVARCHAR(255) check_circle highlight_off highlight_off
Street and house number of customer or vendor.
8 PostalCode NVARCHAR(255) check_circle highlight_off highlight_off
Postal code of customer or vendor.
9 City NVARCHAR(255) check_circle highlight_off highlight_off
City of customer or vendor.
10 CountryIsoCODE NVARCHAR(50) check_circle highlight_off highlight_off
ISO 3166-1 alpha-2 – two-letter country codes.
11 ICPartnerID NVARCHAR(50) check_circle highlight_off highlight_off
Identifier for the intercompany partner.
12 CustomValuesJSON NVARCHAR(MAX) check_circle highlight_off highlight_off
Custom field to store any customer individual informations.

Referenziert von

integration.tDEBCRED_Journal

Facttable to store all facts of module OpenItems.

# Name Datentyp NULL Identity Berechnet
1 RowKey BIGINT highlight_off check_circle highlight_off
Technical primary Key for row identity.
2 DataSourceKey INT highlight_off highlight_off highlight_off
Number of the Datasoure. DataSourceKey is used to create a unique identifier when paired with CompanyID.
3 CompanyID NVARCHAR(50) highlight_off highlight_off highlight_off
Company ID which is used in the source database.
4 AccountID NVARCHAR(255) highlight_off highlight_off highlight_off
Identifier for the customer or vendor account.
5 AccountTypeCODE NVARCHAR(50) highlight_off highlight_off highlight_off
Classification for an account, e.g. DEBITOR or KREDITOR. When no classification is given from source system, the classification will be done later through agent.
6 FinancialYear SMALLINT highlight_off highlight_off highlight_off
Fiscal year in which posting is made. In case of Financial Year <> Calender Year, this is the first Year of the Financial Years.
7 FinancialPeriod TINYINT highlight_off highlight_off highlight_off
Financial period in which posting is made.
8 ItemStatusCODE NVARCHAR(255) highlight_off highlight_off highlight_off
9 OpeningBalance BIGINT highlight_off highlight_off highlight_off
10 Debit BIGINT highlight_off highlight_off highlight_off
Debit value. Decimal places are eliminated through multiplication with 100.
11 Credit BIGINT highlight_off highlight_off highlight_off
Credit value. Decimal places are eliminated through multiplication with 100.
12 PostingDate DATE check_circle highlight_off highlight_off
13 PostingText NVARCHAR(255) check_circle highlight_off highlight_off
Posting text of the posting.
14 DocumentNumber NVARCHAR(255) highlight_off highlight_off highlight_off
Document number of the posting.
15 DocumentDate DATE highlight_off highlight_off highlight_off
Document date of the posting.
16 DocumentClosedDate DATE check_circle highlight_off highlight_off
17 DocumentDueDate DATE check_circle highlight_off highlight_off
Due date of the document
18 DocumentLine INT check_circle highlight_off highlight_off
19 DocumentTypeCODE NVARCHAR(255) check_circle highlight_off highlight_off
CODE of document type (e.g. INVOICE, CREDIT, PAYMENT, ...).
20 ExternalInvoiceNumber NVARCHAR(255) check_circle highlight_off highlight_off
Invoice number.
21 SourceID NVARCHAR(255) check_circle highlight_off highlight_off
Identifier of dataset in source system (e.g. row number).
22 DeltaValue BIGINT check_circle highlight_off highlight_off
Delta value to enable delta load. Should be the timestamp when dataset was inserted in source system or sequential number from source system.
23 CustomValuesJSON NVARCHAR(4000) check_circle highlight_off highlight_off
Custom field to store any customer individual informations.

Referenziert von

result.tDEBCRED_Buchungsjournal

Journal aller offenen und ausgeglichenen Posten.

# Name Datentyp NULL Identity Berechnet
1 MandantenID NVARCHAR(50) check_circle highlight_off highlight_off
2 MandantenName NVARCHAR(255) check_circle highlight_off highlight_off
3 Mandant NVARCHAR(308) check_circle highlight_off highlight_off
4 FinanzJahr SMALLINT check_circle highlight_off highlight_off
5 FinanzPeriode TINYINT check_circle highlight_off highlight_off
6 Perioden_Key INT check_circle highlight_off highlight_off
7 AccountKey NVARCHAR(165) check_circle highlight_off highlight_off
8 PersonenkontenID NVARCHAR(255) check_circle highlight_off highlight_off
9 PersonenkontenName NVARCHAR(255) check_circle highlight_off highlight_off
10 Personenkonto NVARCHAR(513) check_circle highlight_off highlight_off
11 KontenTyp NVARCHAR(50) check_circle highlight_off highlight_off
12 KontenTypBeschreibung NVARCHAR(255) check_circle highlight_off highlight_off
13 Status NVARCHAR(255) check_circle highlight_off highlight_off
14 StatusBeschreibung NVARCHAR(255) check_circle highlight_off highlight_off
15 Buchungsdatum DATE check_circle highlight_off highlight_off
16 Buchungstext NVARCHAR(255) check_circle highlight_off highlight_off
17 Belegdatum DATE check_circle highlight_off highlight_off
18 Belegnummer NVARCHAR(255) check_circle highlight_off highlight_off
19 Belegzeile INT check_circle highlight_off highlight_off
20 Ausgleichdatum DATE check_circle highlight_off highlight_off
21 Faelligkeitsdatum DATE check_circle highlight_off highlight_off
22 DokumentenTyp NVARCHAR(255) check_circle highlight_off highlight_off
23 ExterneRechnungsnummer NVARCHAR(255) check_circle highlight_off highlight_off
24 Saldovortrag MONEY check_circle highlight_off highlight_off
25 Soll MONEY check_circle highlight_off highlight_off
26 Haben MONEY check_circle highlight_off highlight_off
27 Saldo_Buchung MONEY check_circle highlight_off highlight_off
28 RowKey BIGINT check_circle highlight_off highlight_off

Referenziert von

result.tDEBCRED_Personenkonten

Journal aller offenen und ausgeglichenen Posten.

# Name Datentyp NULL Identity Berechnet
1 MandantenID NVARCHAR(50) check_circle highlight_off highlight_off
2 AccountKey NVARCHAR(165) check_circle highlight_off highlight_off
3 PersonenkontenID NVARCHAR(50) check_circle highlight_off highlight_off
4 PersonenkontenName NVARCHAR(255) check_circle highlight_off highlight_off
5 Personenkonto NVARCHAR(306) check_circle highlight_off highlight_off
6 KontenTyp NVARCHAR(50) check_circle highlight_off highlight_off
7 KontenTypBeschreibung NVARCHAR(255) check_circle highlight_off highlight_off
8 Straße NVARCHAR(255) check_circle highlight_off highlight_off
9 PLZ NVARCHAR(255) check_circle highlight_off highlight_off
10 Stadt NVARCHAR(255) check_circle highlight_off highlight_off
11 LandIsoCode NVARCHAR(50) check_circle highlight_off highlight_off
12 LandBeschreibung NVARCHAR(255) check_circle highlight_off highlight_off
13 ICPartnerID NVARCHAR(50) check_circle highlight_off highlight_off
14 RowKey BIGINT check_circle highlight_off highlight_off

Sichten

agent.vJSON_global_tDEBCRED_Accounts

This view resolves the CustomValuesJSON column in table global.tDEBCRED_Accounts to actual columns.

# Name Datentyp NULL Identity Berechnet
1 RowKey BIGINT highlight_off check_circle highlight_off
Technical primary Key for row identity.

Referenzierte Objekte

Referenziert von

agent.vJSON_integration_tDEBCRED_Accounts

This view resolves the CustomValuesJSON column in table integration.tDEBCRED_Accounts to actual columns.

# Name Datentyp NULL Identity Berechnet
1 RowKey BIGINT highlight_off check_circle highlight_off
Technical primary Key for row identity.

Referenzierte Objekte

agent.vJSON_integration_tDEBCRED_Journal

This view resolves the CustomValuesJSON column in table integration.tDEBCRED_Journal to actual columns.

# Name Datentyp NULL Identity Berechnet
1 RowKey BIGINT highlight_off check_circle highlight_off
Technical primary Key for row identity.

Referenzierte Objekte

Referenziert von

result.vDEBCRED_Buchungsjournal

View stellt das Buchungsjournal bereit. Es sind bereits alle Stammdaten gejoint um diese View einfach in nachfolgenden System verwenden zu können

# Name Datentyp NULL Identity Berechnet
1 MandantenID NVARCHAR(50) check_circle highlight_off highlight_off
Mandanten ID aus OCT.
2 MandantenName NVARCHAR(255) check_circle highlight_off highlight_off
Name des Mandanten.
3 Mandant NVARCHAR(308) check_circle highlight_off highlight_off
Kombination aus MandantenID und MandantenName.
4 FinanzJahr SMALLINT highlight_off highlight_off highlight_off
FinanzJahr, zu welcher die Buchung zugeordnet ist.
5 FinanzPeriode TINYINT highlight_off highlight_off highlight_off
Periode im FinanzJahr, zu welcher die Buchung zugeordnet ist.
6 Perioden_Key INT highlight_off highlight_off highlight_off
Kombination aus FinanzJahr, Geschäftsjahresbegin und FinanzPeriode.
7 AccountKey NVARCHAR(165) highlight_off highlight_off highlight_off
8 PersonenkontenID NVARCHAR(255) highlight_off highlight_off highlight_off
ID des Personenkontos.
9 PersonenkontenName NVARCHAR(255) highlight_off highlight_off highlight_off
Name des Personenkontos.
10 Personenkonto NVARCHAR(513) highlight_off highlight_off highlight_off
Kombination aus PersonenkontenID und PersonenkontenName.
11 KontenTyp NVARCHAR(50) highlight_off highlight_off highlight_off
Klassifizierung des Kontos innerhalb einer Gruppe (DEBITOR oder KREDITOR).
12 KontenTypBeschreibung NVARCHAR(255) highlight_off highlight_off highlight_off
Beschreibung des KontenTyps in Deutsch.
13 Status NVARCHAR(255) highlight_off highlight_off highlight_off
Status des Belegs (OPEN oder CLEARED).
14 StatusBeschreibung NVARCHAR(255) highlight_off highlight_off highlight_off
Beschreibung des Status in Deutsch.
15 Buchungsdatum DATE highlight_off highlight_off highlight_off
Buchungsdatum der Buchung.
16 Buchungstext NVARCHAR(255) highlight_off highlight_off highlight_off
Buchungstext der Buchung.
17 Belegdatum DATE highlight_off highlight_off highlight_off
Belegdatum der Buchung.
18 Belegnummer NVARCHAR(255) highlight_off highlight_off highlight_off
Belegnummer des Buchungsbelegs.
19 Belegzeile INT highlight_off highlight_off highlight_off
Belegzeile des Buchungsbelegs.
20 Ausgleichdatum DATE highlight_off highlight_off highlight_off
Datum an dem Beleg ausgeglichen wurde.
21 Faelligkeitsdatum DATE highlight_off highlight_off highlight_off
Datum an dem Beleg fällig zum Ausgleich ist.
22 DokumentenTyp NVARCHAR(255) highlight_off highlight_off highlight_off
Typ des Belegs.
23 ExterneRechnungsnummer NVARCHAR(255) highlight_off highlight_off highlight_off
Rechnungsnummer wie sie bei Kunden oder Lieferant geführt wird.
24 Saldovortrag MONEY highlight_off highlight_off highlight_off
Saldovortrag der Buchung.
25 Soll MONEY highlight_off highlight_off highlight_off
Sollwert der Buchung.
26 Haben MONEY highlight_off highlight_off highlight_off
Habenwert der Buchung.
27 Saldo_Buchung MONEY highlight_off highlight_off highlight_off
Der Saldo der Buchung (Saldovortrag + Soll - Haben).
28 RowKey BIGINT check_circle highlight_off highlight_off

Referenzierte Objekte

Referenziert von

result.vDEBCRED_Personenkonten

View stellt Stammdaten zu Konten bereit. Falls ein Standardkontenrahmen in den Mandantenstammdaten gesetzt ist, wird der Kontenrahmen automatisch zu den Konten zugeordnet (Level0 - Level6)

# Name Datentyp NULL Identity Berechnet
1 MandantenID NVARCHAR(50) check_circle highlight_off highlight_off
Mandanten ID aus OCT.
2 AccountKey NVARCHAR(165) highlight_off highlight_off highlight_off
3 PersonenkontenID NVARCHAR(50) highlight_off highlight_off highlight_off
ID des Personenkontos
4 PersonenkontenName NVARCHAR(255) highlight_off highlight_off highlight_off
Name des Personenkontos
5 Personenkonto NVARCHAR(306) highlight_off highlight_off highlight_off
Kombination aus PersonenkontenID und PersonenkontenName
6 KontenTyp NVARCHAR(50) highlight_off highlight_off highlight_off
Klassifizierung des Kontos innerhalb einer Gruppe (DEBITOR oder KREDITOR).
7 KontenTypBeschreibung NVARCHAR(255) highlight_off highlight_off highlight_off
Beschreibung des KontenTyps
8 Straße NVARCHAR(255) highlight_off highlight_off highlight_off
Straße und Hausnummer.
9 PLZ NVARCHAR(255) highlight_off highlight_off highlight_off
Postleitzahl.
10 Stadt NVARCHAR(255) highlight_off highlight_off highlight_off
Stadt.
11 LandIsoCode NVARCHAR(50) highlight_off highlight_off highlight_off
ISO 3166-1 alpha-2 Ländercode.
12 LandBeschreibung NVARCHAR(255) highlight_off highlight_off highlight_off
Name des Landes in deutsch.
13 ICPartnerID NVARCHAR(50) highlight_off highlight_off highlight_off
Intercompany-PartnerID des Personenkontos.
14 RowKey BIGINT check_circle highlight_off highlight_off

Referenzierte Objekte

Referenziert von

Prozeduren

agent.pDeleteIntegrationValuesDEBCRED

This stored procedure is used to delete data from all tables of the module "DEBCRED".

# Parameter Datentyp
1 @DataSourceKey INT
DataSourceKey
2 @CompanyID NVARCHAR(50)
CompanyID
3 @PeriodFrom INT
PeriodFrom
4 @PeriodTo INT
PeriodTo
5 @Username NVARCHAR(255)
Username
6 @IsDeltaloadBool BIT
IsDeltaloadBool
7 @Module NVARCHAR(255)
Module

Referenzierte Objekte

agent.pDoMaintenanceDEBCRED

pDoMaintenanceDEBCRED

# Parameter Datentyp
1 @Username NVARCHAR(255)
Username
2 @ProcessID NVARCHAR(50)
ProcessID
3 @SubProcessID NVARCHAR(50)
SubProcessID

Referenzierte Objekte

agent.pPostBulkloadDEBCRED

This the post bulkload procedure of module "DEBCRED". The procedure triggers the sync of master data from integration to global schema.

# Parameter Datentyp
1 @Username NVARCHAR(255)
Username
2 @CompanyKeys NVARCHAR(MAX)
CompanyKeys

Referenzierte Objekte

agent.pSyncDEBCRED

Procedure to transfer data of module DEBCRED to result schema.

# Parameter Datentyp
1 @Username NVARCHAR(255)
Username
2 @CompanyKeys NVARCHAR(MAX)
CompanyKeys
3 @FullRebuild BIT
Force full rebuild of fact tables.

Referenzierte Objekte

result.pDEBCRED_AccountBalancesPeriods

pDEBCRED_AccountBalancesPeriods

# Parameter Datentyp
1 @Username NVARCHAR(255)
Username
2 @ProcessID NVARCHAR(50)
ProcessID
3 @SubProcessID NVARCHAR(50)
SubProcessID

Referenzierte Objekte

Referenziert von

result.spVAL_DEBCRED_AbgleichFiBu

Abgleich der Ergebnisse für Personenkonto & Geschäftsjahr von DebCred-Journal und FIN-Buchungsjournal

# Parameter Datentyp
1 @Username NVARCHAR(255)
2 @FactoryID NVARCHAR(255)
3 @ProductLineID NVARCHAR(255)
4 @ProductID NVARCHAR(255)
5 @ParameterJSON NVARCHAR(MAX)

Referenzierte Objekte

result.spVAL_DEBCRED_Buchungsjournal

Protoyp Validierung DEBCRED

# Parameter Datentyp
1 @Username NVARCHAR(255)
2 @FactoryID NVARCHAR(255)
3 @ProductLineID NVARCHAR(255)
4 @ProductID NVARCHAR(255)
5 @ParameterJSON NVARCHAR(MAX)

Referenzierte Objekte