GeMIS_WEB/GUI/Tools/SESAMSQL_Korr_1.p

&ANALYZE-SUSPEND _VERSION-NUMBER AB_v10r12 - ADM2
&ANALYZE-RESUME
{adecomm/appserv.i}
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure 
/*------------------------------------------------------------------------
    File        : 
    Purpose     :

    Syntax      :

    Description :

    Author(s)   :
    Created     :
    Notes       :
  ----------------------------------------------------------------------*/
/*          This .W file was created with the Progress AppBuilder.      */
/*----------------------------------------------------------------------*/

/* ***************************  Definitions  ************************** */


DEF VAR ObjRecordSet    AS COM-HANDLE           NO-UNDO.
DEF VAR ObjConnection   AS COM-HANDLE           NO-UNDO.
DEF VAR ObjCommand      AS COM-HANDLE           NO-UNDO.
DEF VAR ODBC-DSN        AS CHAR                 NO-UNDO.
DEF VAR ODBC-SERVER     AS CHAR                 NO-UNDO.
DEF VAR ODBC-USERID     AS CHAR                 NO-UNDO.
DEF VAR ODBC-PASSWD     AS CHAR                 NO-UNDO.
DEF VAR ODBC-QUERY      AS CHAR                 NO-UNDO.
DEF VAR ODBC-STATUS     AS CHAR                 NO-UNDO.
DEF VAR ODBC-RECCOUNT   AS INT                  NO-UNDO.
DEF VAR ODBC-NULL       AS CHAR                 NO-UNDO.
DEF VAR ODBC-CURSOR     AS INT                  NO-UNDO.

DEF VAR hBeleg          AS HANDLE               NO-UNDO.
DEF VAR hBelegDetail    AS HANDLE               NO-UNDO.
DEF VAR hOpListe        AS HANDLE               NO-UNDO.

DEF VAR fEnde           AS LOG                  NO-UNDO.
DEF VAR TempName        AS CHAR                 NO-UNDO.
DEF VAR cJournal        AS CHAR                 NO-UNDO.
DEF VAR WebString       AS CHAR                 NO-UNDO.

DEF VAR AllesOK         AS LOG                  NO-UNDO.
DEF VAR Sprcd           AS INT                  NO-UNDO.
DEF VAR LVAdx           AS CHAR                 NO-UNDO EXTENT 15.
DEF VAR LVAdy           AS CHAR                 NO-UNDO EXTENT 15.
DEF VAR cOrt            AS CHAR                 NO-UNDO.
DEF VAR Firma           AS CHAR                 NO-UNDO INIT '9999'.
DEF VAR GVFirma         AS CHAR                 NO-UNDO INIT '9999'.
DEF VAR ja              AS LOG                  NO-UNDO.
DEF VAR CrLf            AS CHAR                 NO-UNDO.

DEF STREAM Logfile.

DEF TEMP-TABLE tBeleg
    FIELD DocKey        AS INT
    FIELD DocType       AS INT
    FIELD OpID          AS CHAR
    FIELD KndNr         AS INT
    FIELD Konto         AS INT
    FIELD opText        AS CHAR
    FIELD opDate        AS DATETIME
    FIELD ZabInd        AS INT
    FIELD SortId        AS CHAR
    FIELD Firma         AS CHAR
    FIELD Zusatz1       AS CHAR
    FIELD Strasse       AS CHAR
    FIELD Plz           AS CHAR
    FIELD Ort           AS CHAR
    FIELD Telefon1      AS CHAR
    FIELD Telefon4      AS CHAR
    FIELD EsrNr         AS CHAR
    FIELD Sprache       AS CHAR
    FIELD Transfer      AS DATETIME
    FIELD FibuDatum     AS DATETIME
    FIELD OPfaellig     AS DATETIME
    FIELD OpCode        AS CHAR
    FIELD BelegNr       AS INT
    FIELD Zusatz2       AS CHAR
    FIELD Sammelkonto   AS CHAR
    FIELD Land          AS CHAR
    
    INDEX Faknr IS PRIMARY
        OpID
    INDEX DocKey
        DocKey.

DEF TEMP-TABLE tBelegDetail
    FIELD DocLKey       AS CHAR
    FIELD DocKey        AS INT
    FIELD DocLine       AS INT
    FIELD Konto         AS CHAR
    FIELD cText         AS CHAR
    FIELD MwstTyp       AS CHAR
    FIELD Betrag        AS DEC
    FIELD BetragMwst    AS DEC
    FIELD BetragFW      AS DEC
    FIELD BuchTyp       AS CHAR
    FIELD Kostenstelle  AS CHAR
    FIELD cCode         AS CHAR
    
    INDEX DocKey IS PRIMARY
        DocKey
        Konto.

DEF TEMP-TABLE tOpListe
    FIELD OpId          AS CHAR
    FIELD PkNr          AS INT
    FIELD Betrag        AS DEC
    FIELD RechBetrag    AS DEC
    FIELD Datum         AS DATETIME
    
    INDEX OpID IS PRIMARY
        OpID.
        
DEF TEMP-TABLE tDebop   LIKE Debop
    FIELD Ansatz_8      AS DEC
    FIELD Ansatz_25     AS DEC
    FIELD Ansatz_0      AS DEC
    FIELD SageAns_8     AS DEC
    FIELD SageAns_25    AS DEC
    FIELD SageAns_0     AS DEC
    FIELD DetailBetr    AS DEC
    FIELD SageSaldo     AS DEC
    FIELD cTransfer     AS CHAR
    FIELD lOpListe      AS LOG
    .
DEF TEMP-TABLE txBelegDetail    LIKE tBelegDetail
    .
DEF TEMP-TABLE tMwstBetr
    FIELD Konto         AS CHAR
    FIELD MwstCd        AS INT
    FIELD MwstPfl       AS DEC  DECIMALS 2
    FIELD MwstBet       AS DEC  DECIMALS 2
    FIELD MwstAns       AS DEC  DECIMALS 2
    .
DEF TEMP-TABLE tSollIst
    FIELD Knr           AS INT
    FIELD Faknr         AS INT
    FIELD Fakdat        AS DATE
    FIELD Fakbetr       AS Dec
    FIELD Konto         AS CHAR
    FIELD MwstAns       AS CHAR
    FIELD Soll          AS DEC
    FIELD Ist           AS DEC
    .

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK 

/* ********************  Preprocessor Definitions  ******************** */

&Scoped-define PROCEDURE-TYPE Procedure
&Scoped-define DB-AWARE no



/* _UIB-PREPROCESSOR-BLOCK-END */
&ANALYZE-RESUME


/* ************************  Function Prototypes ********************** */

&IF DEFINED(EXCLUDE-CloseConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD CloseConnection Procedure 
FUNCTION CloseConnection RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-closeSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD closeSQLTransaction Procedure 
FUNCTION closeSQLTransaction RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD CreateConnection Procedure 
FUNCTION CreateConnection RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateSetDaten) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD CreateSetDaten Procedure 
FUNCTION CreateSetDaten RETURNS CHARACTER
  ( iphBuffer AS HANDLE, ipArt AS INT )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-deleteBelege) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD deleteBelege Procedure 
FUNCTION deleteBelege RETURNS INTEGER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-getDocKey) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getDocKey Procedure 
FUNCTION getDocKey RETURNS INTEGER
  ( ipDocKey AS INT )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-getODBCDaten) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getODBCDaten Procedure 
FUNCTION getODBCDaten RETURNS LOGICAL
  ( iphBuffer AS HANDLE )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenBeleg Procedure 
FUNCTION lesenBeleg RETURNS INTEGER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenBelegDetail Procedure 
FUNCTION lesenBelegDetail RETURNS INTEGER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenOpListe) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD lesenOpListe Procedure 
FUNCTION lesenOpListe RETURNS INTEGER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-readDebop) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD readDebop Procedure 
FUNCTION readDebop RETURNS INTEGER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-reorgBelege) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD reorgBelege Procedure 
FUNCTION reorgBelege RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-startSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD startSQLTransaction Procedure 
FUNCTION startSQLTransaction RETURNS LOGICAL
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD updateBeleg Procedure 
FUNCTION updateBeleg RETURNS LOGICAL
  ( ipTHandle AS HANDLE, ipArt AS INT )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD updateBelegDetail Procedure 
FUNCTION updateBelegDetail RETURNS LOGICAL
  ( ipTHandle AS HANDLE, ipArt AS INT )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-writeLogFiles) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD writeLogFiles Procedure 
FUNCTION writeLogFiles RETURNS LOGICAL
  ( ipMessage AS CHAR  )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF


/* *********************** Procedure Settings ************************ */

&ANALYZE-SUSPEND _PROCEDURE-SETTINGS
/* Settings for THIS-PROCEDURE
   Type: Procedure
   Allow: 
   Frames: 0
   Add Fields to: Neither
   Other Settings: CODE-ONLY COMPILE APPSERVER
 */
&ANALYZE-RESUME _END-PROCEDURE-SETTINGS

/* *************************  Create Window  ************************** */

&ANALYZE-SUSPEND _CREATE-WINDOW
/* DESIGN Window definition (used by the UIB) 
  CREATE WINDOW Procedure ASSIGN
         HEIGHT             = 27.48
         WIDTH              = 77.8.
/* END WINDOW DEFINITION */
                                                                        */
&ANALYZE-RESUME

 


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure 


/* ***************************  Main Block  *************************** */

    TempName = SUBSTITUTE('C:\Temp\SESAMSQL_&1_&2.log', STRING(TODAY,'99-99-9999'), REPLACE(STRING(TIME,'HH:MM:SS'), ':', '-')).
    cJournal = SUBSTITUTE('C:\Temp\SESAMSQL_&1_&2.csv', STRING(TODAY,'99-99-9999'), REPLACE(STRING(TIME,'HH:MM:SS'), ':', '-')).

    ja = DYNAMIC-FUNCTION('CreateConnection':U) NO-ERROR.

    IF NOT ja THEN QUIT.
DYNAMIC-FUNCTION('writeLogFiles':U, 'Connected').    
    DYNAMIC-FUNCTION('deleteBelege':U).
    /*
    DYNAMIC-FUNCTION('readDebop':U).
    DYNAMIC-FUNCTION('lesenBeleg':U).
    DYNAMIC-FUNCTION('lesenBelegDetail':U).
    DYNAMIC-FUNCTION('lesenOpListe':U).
    
    OUTPUT TO 'C:\Temp\OffenePosten_Ge_MIS.csv' NO-MAP NO-CONVERT.
    FOR EACH tDebop:
        EXPORT DELIMITER ';'
            tDebop.Knr
            tDebop.Faknr
            tDebop.FakDat   FORMAT "99.99.9999"
            tDebop.Fakbetr
            tDebop.Saldo
            tDebop.DetailBetr
            tDebop.SageSaldo
            tDebop.cTransfer
            tDebop.lOpListe
            tDebop.Ansatz_8
            tDebop.Ansatz_25
            tDebop.Ansatz_0
            tDebop.SageAns_8
            tDebop.SageAns_25
            tDebop.SageAns_0
            .
    END.
    OUTPUT CLOSE.
    
    DEF VAR dDatum      AS DATE                     NO-UNDO.
    DEF VAR nSoll       AS DEC                      NO-UNDO.
    DEF VAR nIst        AS DEC                      NO-UNDO.
    DEF VAR nSaldo      AS DEC                      NO-UNDO.
    
    EMPTY TEMP-TABLE tSollIst.
    FOR EACH tDebop:
        FIND FIRST tBeleg NO-LOCK
                WHERE tBeleg.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>')).
        dDatum = DATE(tBeleg.Transfer) NO-ERROR.
        IF dDatum < 09/28/2017 THEN NEXT.
        FOR EACH tBelegDetail NO-LOCK
                WHERE tBelegDetail.DocKey = tBeleg.DocKey:
            CREATE  tSollIst.
            ASSIGN  tSollIst.Knr     = tDebop.Knr
                    tSollIst.Faknr   = tDebop.Faknr
                    tSollIst.Fakdat  = tDebop.Fakdat
                    tSollIst.Fakbetr = tDebop.Fakbetr
                    tSollIst.Konto   = tBelegDetail.Konto
                    tSollIst.MwstAns = tBelegDetail.MwstTyp
                    tSollIst.Ist     = tBelegDetail.Betrag.
        END.
        FOR EACH txBelegDetail NO-LOCK
                WHERE txBelegDetail.DocKey = tBeleg.DocKey:
            FIND FIRST tSollIst
                    WHERE tSollIst.Faknr   = tDebop.Faknr
                    AND   tSollIst.Konto   = txBelegDetail.Konto
                    AND   tSollIst.MwstAns = txBelegDetail.MwstTyp NO-ERROR.
            IF NOT AVAILABLE tSollIst THEN DO:
                CREATE  tSollIst.
                ASSIGN  tSollIst.Knr     = tDebop.Knr
                        tSollIst.Faknr   = tDebop.Faknr
                        tSollIst.Fakdat  = tDebop.Fakdat
                        tSollIst.Fakbetr = tDebop.Fakbetr
                        tSollIst.Konto   = txBelegDetail.Konto
                        tSollIst.MwstAns = txBelegDetail.MwstTyp.
            END.
            tSollIst.Soll = txBelegDetail.Betrag.
        END.
    END.
        
    OUTPUT TO 'C:\Temp\Schnittstellen_Detail.csv'.
    FOR EACH tSollIst
            BREAK BY tSollIst.Knr
                  BY tSollIst.Faknr
                  BY tSollIst.Konto
                  BY tSollIst.MwstAns:
        IF FIRST-OF ( tSollIst.Faknr ) THEN DO:
            FIND FIRST tDebop NO-LOCK
                    WHERE tDebop.Firma = '1000'
                    AND   tDebop.Knr   = tSollIst.Knr
                    AND   tDebop.Faknr = tSollIst.Faknr.
            ASSIGN  nSoll  = 0
                    nIst   = 0
                    nSaldo = tDebop.Saldo.
        END.
        ASSIGN  nSoll = nSoll + tSollIst.Soll
                nIst  = nIst  + tSollIst.Ist.
        IF FIRST-OF (tSollIst.Faknr) THEN EXPORT DELIMITER ';' tSollIst.Knr
                                                               tSollIst.Faknr
                                                               tSollIst.Fakdat
                                                               tSollIst.Fakbetr
                                                               tSollIst.Konto
                                                               tSollIst.MwstAns
                                                               tSollIst.Soll
                                                               tSollIst.Ist
                                                               (tSollIst.Soll - tSollIst.Ist).
        ELSE                              EXPORT DELIMITER ';' ''
                                                               ''
                                                               ''
                                                               ''
                                                               tSollIst.Konto
                                                               tSollIst.MwstAns
                                                               tSollIst.Soll
                                                               tSollIst.Ist
                                                               (tSollIst.Soll - tSollIst.Ist).
        IF NOT LAST-OF ( tSollIst.Faknr ) THEN NEXT.
        EXPORT DELIMITER ';' ''
                             ''
                             ''
                             ''
                             ''
                             ''
                             nSoll
                             nIst
                             ''
                             (nSoll - nIst)
                             (tDebop.Fakbetr - nSaldo).
        PUT CONTROL CHR(10).
    END.
    OUTPUT CLOSE.
    /*
    RUN FUELLEN_TBELEG.
    
    RUN LESEN_OFFENESALDI.
    */
    */
    DYNAMIC-FUNCTION('CloseConnection':U) NO-ERROR.

/*     QUIT. */

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* **********************  Internal Procedures  *********************** */

&IF DEFINED(EXCLUDE-FUELLEN_TBELEG) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE FUELLEN_TBELEG Procedure 
PROCEDURE FUELLEN_TBELEG :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    DEF VAR xx      AS INT                      NO-UNDO.
    DEF VAR dDatum  AS DATE                     NO-UNDO.

    xx = DYNAMIC-FUNCTION('lesenBeleg':U) NO-ERROR.
    
message xx ' Records gelesen ' VIEW-AS ALERT-BOX.    

    OUTPUT TO 'C:\TEMP\Dump_Beleg.csv' NO-MAP NO-CONVERT.
    FOR EACH tBeleg NO-LOCK:
        EXPORT DELIMITER ';' tBeleg.
    END.
    OUTPUT CLOSE.
    
/*     FOR EACH tBeleg: */
/*         dDatum = DATE(tBeleg.Transfer) NO-ERROR. */
/*         IF ERROR-STATUS:ERROR OR */
/*            dDatum = ?         THEN DELETE  tBeleg. */
/*     END. */
/*     FOR EACH tBeleg */
/*             BREAK BY tBeleg.OpID: */
/*         IF FIRST-OF ( tBeleg.OpID ) THEN NEXT. */
/*     END. */
/*    */
/*     FOR EACH tBeleg: */
/*         dDatum = DATE(tBeleg.Transfer). */
/*         IF dDatum >= 09/29/2017 THEN NEXT. */
/*         DELETE tBeleg. */
/*     END. */

    OUTPUT TO VALUE(cJournal) NO-MAP NO-CONVERT APPEND.
    PUT CONTROL STRING(TODAY,'99.99.9999') ' ' STRING(TIME,'HH:MM:SS') ' Anzahl eingelesene Beleg-Details aus SESAM -> ' xx CHR(10).
    OUTPUT CLOSE.
    
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-LESEN_OFFENESALDI) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE LESEN_OFFENESALDI Procedure 
PROCEDURE LESEN_OFFENESALDI :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

    DEF VAR Saldo       AS DEC                      NO-UNDO.
    DEF VAR Diff        AS DEC                      NO-UNDO.
    DEF VAR dDatum      AS DATE                     NO-UNDO.

    EMPTY TEMP-TABLE tDebop.
    FOR EACH Debop NO-LOCK
            WHERE Debop.Firma  = '1000':
        FIND FIRST tBeleg NO-LOCK
                WHERE tBeleg.OpId = TRIM(STRING(Debop.Faknr,'>>>>>>>')) NO-ERROR.
        IF NOT AVAILABLE tBeleg THEN NEXT.
        CREATE  tDebop.
        BUFFER-COPY Debop TO tDebop.
    END.
    
    DYNAMIC-FUNCTION('lesenOpSaldo':U) NO-ERROR.

    FOR EACH Debst NO-LOCK:
        FOR EACH tDebop USE-INDEX Debop-k1
                WHERE tDebop.Firma = Debst.Firma
                AND   tDebop.Knr   = Debst.Knr:
            FIND FIRST tOpListe NO-LOCK USE-INDEX OpId
                WHERE tOpListe.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>>'))
                AND   tOpListe.PkNr = Debst.Knr NO-ERROR.
            IF AVAILABLE tOpListe THEN Diff = tDebop.Saldo - tOpListe.Betrag.
            ELSE                       Diff = (IF tDebop.Fakdat < (TODAY - 5) THEN tDebop.Saldo ELSE 0).
            IF Diff <> 0 THEN DO:
                tDebop.ZahBetr = tDebop.ZahBetr + Diff.
                tDebop.Saldo   = tDebop.Fakbetr - tDebop.Zahbetr - tDebop.Skonto.
                tDebop.Zahdat  = TODAY.
            END.
        END.
    END.
    
    OUTPUT TO 'C:\TEMP\tDebop_OPSaldo.csv' NO-MAP NO-CONVERT.
    FOR EACH tDebop NO-LOCK:
        FIND Adresse NO-LOCK
                WHERE Adresse.Firma = tDebop.Firma
                AND   Adresse.Knr   = tDebop.Knr.
        EXPORT DELIMITER ';'
            tDebop.Knr
            Adresse.Anzeig_Br
            tDebop.Faknr
            tDebop.FakDat
            tDebop.Fakbetr
            tDebop.ZahBetr
            tDebop.SageSaldo.
    END.
    OUTPUT CLOSE.
    
    FOR EACH tDebop:
        FIND FIRST tBeleg NO-LOCK
                WHERE tBeleg.OpID = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR.
        IF AVAILABLE tBeleg THEN NEXT.
        DELETE  tDebop.
    END.
    
    OUTPUT TO 'C:\TEMP\tDebop_Schnittstelle.csv' NO-MAP NO-CONVERT.
    FOR EACH tDebop NO-LOCK:
        FIND Adresse NO-LOCK
                WHERE Adresse.Firma = tDebop.Firma
                AND   Adresse.Knr   = tDebop.Knr.
        EXPORT DELIMITER ';'
            tDebop.Knr
            Adresse.Anzeig_Br
            tDebop.Faknr
            tDebop.FakDat
            tDebop.Fakbetr
            tDebop.ZahBetr
            tDebop.Saldo.
    END.
    OUTPUT CLOSE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-RUNDEN) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE RUNDEN Procedure 
PROCEDURE RUNDEN :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

DEF INPUT        PARAMETER Rundcode     AS INT              NO-UNDO.
DEF INPUT-OUTPUT PARAMETER Rundbetr     AS DEC  DECIMALS 4  NO-UNDO.

DEF VAR VBetr       AS DEC  DECIMALS 4                      NO-UNDO.
DEF VAR VOp         AS DEC              INIT 0.2            NO-UNDO.
DEF VAR XPChar      AS CHAR                                 NO-UNDO.

DO TRANSACTION:
    VBetr = Rundbetr.

    IF Rundcode = 1 THEN DO:
        VBetr = ROUND((VBetr / 100 * VOp), 4) / VOp * 100.
    END.
    IF Rundcode = 2 THEN DO:
            VBetr = ROUND((VBetr / 100), 3) * 100.
    END.
    IF Rundcode = 3 THEN DO:
        VBetr = VBetr + 0.0499.
            VBetr = ROUND((VBetr / 100), 3) * 100.
    END.
    IF Rundcode = 4 THEN DO:
        VBetr = VBetr - 0.0500.
            VBetr = ROUND((VBetr / 100), 3) * 100.
    END.
    IF Rundcode = 5 THEN DO:
            VBetr = ROUND((VBetr / 100), 2) * 100.
    END.
    IF Rundcode = 6 THEN DO:
            VBetr = VBetr + 0.4999.
            VBetr = ROUND((VBetr / 100), 2) * 100.
    END.
    IF Rundcode = 7 THEN DO:
            VBetr = VBetr - 0.5000.
            VBetr = ROUND((VBetr / 100), 2) * 100.
    END.
    IF Rundcode = 8 THEN DO:
            VBetr = ROUND((VBetr / 1000), 2) * 1000.
    END.
    IF Rundcode = 9 THEN DO:
            VBetr = VBetr + 4.9999.
            VBetr = ROUND((VBetr / 1000), 2) * 1000.
    END.
    IF Rundcode = 10 THEN DO:
            VBetr = VBetr - 5.0000.
            VBetr = ROUND((VBetr / 1000), 2) * 1000.
    END.

    IF Rundcode = 99 THEN DO:
            XPChar = STRING(VBetr,"-99999999.999").
            VBetr  = DECIMAL(SUBSTRING(XPChar,01,12)).
        IF SUBSTRING(XPChar,13,01) > "4" THEN VBetr = VBetr + 0.01.
    END.

    Rundbetr = VBetr.

END.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

/* ************************  Function Implementations ***************** */

&IF DEFINED(EXCLUDE-CloseConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION CloseConnection Procedure 
FUNCTION CloseConnection RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    ObjConnection:Close NO-ERROR.

    RELEASE OBJECT ObjConnection NO-ERROR.
    RELEASE OBJECT ObjCommand    NO-ERROR.
    RELEASE OBJECT ObjRecordSet  NO-ERROR.
    
    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-closeSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION closeSQLTransaction Procedure 
FUNCTION closeSQLTransaction RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    ODBC-QUERY = 'COMMIT'.

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 ).

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateConnection) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION CreateConnection Procedure 
FUNCTION CreateConnection RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cConnCommand    AS CHAR             NO-UNDO.
    DEF VAR cDSN            AS CHAR             NO-UNDO.
    DEF VAR cServer         AS CHAR             NO-UNDO.
    DEF VAR ix              AS INT              NO-UNDO.
    DEF VAR ii              AS INT              NO-UNDO.
    DEF VAR cMessage        AS CHAR             NO-UNDO.

    Create "ADODB.Connection"   ObjConnection NO-ERROR.
    Create "ADODB.RecordSet"    ObjRecordSet  NO-ERROR.
    Create "ADODB.Command"      ObjCommand    NO-ERROR.

    GET-KEY-VALUE SECTION 'Grundeinstellungen'
                  KEY     'SQLServer'
                  VALUE   cServer.
    IF cServer = ?  THEN RETURN FALSE.
    IF cServer = '' THEN RETURN FALSE.
  
    GET-KEY-VALUE SECTION 'Grundeinstellungen'
                  KEY     'SQLDSN'
                  VALUE   cDSN.
    IF cDSN    = ?  THEN RETURN FALSE.
    IF cDSN    = '' THEN RETURN FALSE.
  
    ASSIGN  ODBC-STATUS   = ''
            ODBC-NULL     = ''
            ODBC-DSN      = cDSN
            ODBC-SERVER   = cServer
            ODBC-USERID   = 'gebtools'
            ODBC-PASSWD   = 'gebtools'
            ODBC-QUERY    = ''.

    cConnCommand = 'data source='
                 + ODBC-DSN
                 + ';server='
                 + ODBC-SERVER
                 .
    ObjConnection:Open ( cConnCommand, ODBC-USERID, ODBC-PASSWD, 0 ) NO-ERROR.
    If ( ERROR-STATUS:NUM-MESSAGES > 0 ) THEN DO:
        ii = ERROR-STATUS:NUM-MESSAGES.
        ODBC-STATUS = "Error: keine Verbindung zu Datenserver "
                    + cConnCommand.
        cMessage = ODBC-STATUS.
        DO ix = 1 TO ii:
            cMessage = cMessage
                     + CHR(10)
                     + ERROR-STATUS:GET-MESSAGE(ix).
        END.
        DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
        RETURN FALSE.
    END.

    ASSIGN  ObjCommand:ActiveConnection = ObjConnection
            ObjCommand:CommandType       = 1 /* adCmdText       */
            ObjConnection:CursorLocation = 3 /* adUseClient     */
            ObjRecordSet:CursorType      = 3 /* adOpenStatic    */.

    cMessage = 'Start SESAM SQL mit Connection ' + cConnCommand.
    DYNAMIC-FUNCTION('writeLogFile':U, cMessage) NO-ERROR.
    
    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-CreateSetDaten) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION CreateSetDaten Procedure 
FUNCTION CreateSetDaten RETURNS CHARACTER
  ( iphBuffer AS HANDLE, ipArt AS INT ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cSet        AS CHAR                     NO-UNDO.
    DEF VAR ii          AS INT                      NO-UNDO.
    DEF VAR hFeld       AS HANDLE                   NO-UNDO.
    DEF VAR cFeld       AS CHAR                     NO-UNDO.
    DEF VAR cInhalt     AS CHAR                     NO-UNDO.
    DEF VAR cFelder     AS CHAR                     NO-UNDO.
    DEF VAR cInhalte    AS CHAR                     NO-UNDO.
    DEF VAR xFeld       AS CHAR                     NO-UNDO.
    DEF VAR xInhalt     AS CHAR                     NO-UNDO.

    cSet     = ''.
    cFelder  = ''.
    cInhalte = ''.
    DO ii = 1 TO iphBuffer:NUM-FIELDS:
        hFeld = iphBuffer:BUFFER-FIELD(ii).
        IF hFeld:BUFFER-VALUE(0) = ? THEN NEXT.
        cFeld = hFeld:NAME.
        IF cFeld = 'cCode'   THEN cFeld = 'Code'. /*  ProgressSchlüsselFeld  */
        IF cFeld = 'cText'   THEN cFeld = 'Text'. /*  ProgressSchlüsselFeld  */
        IF cFeld = 'iStatus' THEN NEXT.           /*  Kein SQL-Feld          */
        DO WHILE TRUE:
            IF hFeld:BUFFER-VALUE(0) <> ?   AND
               hFeld:BUFFER-VALUE(0) <> '?' THEN LEAVE.

            CASE hFeld:DATA-TYPE:
                WHEN 'INTEGER' THEN hFeld:BUFFER-VALUE(0) = '0'.
                WHEN 'DECIMAL' THEN hFeld:BUFFER-VALUE(0) = '0.0'.
                WHEN 'DATE'    THEN hFeld:BUFFER-VALUE(0) = ''.
                WHEN 'LOGICAL' THEN hFeld:BUFFER-VALUE(0) = STRING(FALSE).
                OTHERWISE hFeld:BUFFER-VALUE(0) = ''.
            END CASE.

            LEAVE.
        END.

        xFeld   = "[" + TRIM(cFeld) + "]".
        xInhalt = TRIM(hFeld:BUFFER-VALUE(0)).
        xInhalt = REPLACE(xInhalt, "'", '"').
        xInhalt = "'" + xInhalt + "'".

        cInhalte = cInhalte + "," + xInhalt.
        cFelder  = cFelder  + "," + xFeld.
    END.
    cInhalte = SUBSTRING(cInhalte,02).
    cFelder  = SUBSTRING(cFelder ,02).

    CASE ipArt:
        WHEN 1 THEN DO:
            cSet = '('
                 + cFelder
                 + ') VALUES ('
                 + cInhalte
                 + ')'.
        END.
        WHEN 0 THEN DO:
            cSet = 'SET '.
            DO ii = 1 TO NUM-ENTRIES(cFelder, ','):
                cSet = cSet + ENTRY(ii, cFelder, ',')
                            + ' = '
                            + ENTRY(ii, cInhalte, ',').
                IF ii < NUM-ENTRIES(cFelder, ',') THEN cSet = cSet + ', '.
            END.
        END.
    END CASE.

    RETURN cSet.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-deleteBelege) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION deleteBelege Procedure 
FUNCTION deleteBelege RETURNS INTEGER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR dDatum      AS DATE                     NO-UNDO.
    DEF VAR ii          AS INT                      NO-UNDO.
    DEF VAR iDocKey     AS INT                      NO-UNDO.

    EMPTY TEMP-TABLE tBeleg.
    hBeleg = TEMP-TABLE tBeleg:DEFAULT-BUFFER-HANDLE.

    ODBC-QUERY = "SELECT * FROM Huber.dbo.Beleg WHERE OpID = '557768'".
DYNAMIC-FUNCTION('writeLogFiles':U, ODBC-QUERY).    

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
            ODBC-RECCOUNT          = ObjRecordSet:RecordCount .
            
MESSAGE ODBC-RECCOUNT
    VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
    
    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  tBeleg.
            DYNAMIC-FUNCTION('getODBCDaten':U, hBeleg ).
            ASSIGN  ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.

    FOR EACH tBeleg:
        DISPLAY tbeleg.
        ii = ii + 1.
    END.
    
/*     ii = 0.                                                                                            */
/*     FOR EACH tBeleg NO-LOCK:                                                                           */
/*         ODBC-QUERY = 'DELETE FROM [Huber].[dbo].[BelegDetail] '                                        */
/*                    + 'WHERE DocKey = ' + STRING(tBeleg.DocKey) + ' '.                                  */
/* /*         MESSAGE ODBC-QUERY VIEW-AS ALERT-BOX. */                                                    */
/*         /*                                                                                             */
/*         ASSIGN  ObjCommand:CommandText = ODBC-QUERY                                                    */
/*                 ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )               */
/*                 ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.                            */
/*                                                                                                        */
/*         MESSAGE 'Anzahl gelöschte BelegDetail = ' ODBC-RECCOUNT VIEW-AS ALERT-BOX.                     */
/*         */                                                                                             */
/*         ODBC-QUERY = 'DELETE FROM [Huber].[dbo].[Beleg] '                                              */
/*                    + 'WHERE OpId = "' + tBeleg.OpId + '" AND DocKey = ' + STRING(tBeleg.DocKey) + ' '. */
/*         MESSAGE ODBC-QUERY VIEW-AS ALERT-BOX.                                                          */
/*         /*                                                                                             */
/*         ASSIGN  ObjCommand:CommandText = ODBC-QUERY                                                    */
/*                 ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )               */
/*                 ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.                            */
/*                                                                                                        */
/* /*         MESSAGE 'Anzahl gelöschte Belege = ' ODBC-RECCOUNT VIEW-AS ALERT-BOX. */                    */
/*         */                                                                                             */
/*         ii = ii + 1.                                                                                   */
/*     END.                                                                                               */
/* message 'Anzahl gelöschte Belege = ' ii view-as alert-box.                                             */
    RETURN ii.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-getDocKey) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getDocKey Procedure 
FUNCTION getDocKey RETURNS INTEGER
  ( ipDocKey AS INT ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR iDocKey     AS INT                      NO-UNDO.

    iDocKey    = ipDocKey.
    ODBC-QUERY = "SELECT [DocKey] FROM [Huber].[dbo].[Beleg] "
               + "WHERE OpId = '" + STRING(ipDocKey,'999999') + "' ".

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
            ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ObjRecordSet:MoveFirst NO-ERROR.
        iDocKey = ObjRecordSet:FIELDS("DocKey"):VALUE.
        LEAVE.
    END.

    RETURN iDocKey.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-getODBCDaten) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getODBCDaten Procedure 
FUNCTION getODBCDaten RETURNS LOGICAL
  ( iphBuffer AS HANDLE ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR ii      AS INT                      NO-UNDO.
    DEF VAR hFeld   AS HANDLE                   NO-UNDO.
    DEF VAR cFeld   AS CHAR                     NO-UNDO.

    DO ii = 1 TO iphBuffer:NUM-FIELDS:
        hFeld = iphBuffer:BUFFER-FIELD(ii).
        cFeld = hFeld:NAME.
        IF cFeld = 'cText' THEN cFeld = 'Text'.
        IF cFeld = 'cCode' THEN cFeld = 'Code'.
        hFeld:BUFFER-VALUE(0) = ObjRecordSet:FIELDS(cFeld):VALUE NO-ERROR.
    END.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenBeleg Procedure 
FUNCTION lesenBeleg RETURNS INTEGER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR dDatum      AS DATE                     NO-UNDO.
    DEF VAR dFakBetr    AS DEC  DECIMALS 2          NO-UNDO.
    DEF VAR BuchBetr    AS DEC  DECIMALS 2          NO-UNDO.
    DEF VAR Ansatz      AS DEC                      NO-UNDO.
    DEF VAR Inkl        AS LOG                      NO-UNDO.
    DEF VAR ii          AS INT                      NO-UNDO.
    DEF VAR cString     AS CHAR                     NO-UNDO.
    DEF VAR iDocKey     AS INT                      NO-UNDO.
    DEF VAR dMwst       AS DEC                      NO-UNDO.
    DEF VAR lMwstAdd    AS LOG                      NO-UNDO.
    DEF VAR nDiff       AS DEC  DECIMALS 2          NO-UNDO.


    EMPTY TEMP-TABLE txBelegDetail.
    EMPTY TEMP-TABLE tBeleg.
    hBeleg = TEMP-TABLE tBeleg:DEFAULT-BUFFER-HANDLE.

    ODBC-QUERY = 'SELECT * FROM [Huber].[dbo].[Beleg]'.

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
            ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  tBeleg.
            DYNAMIC-FUNCTION('getODBCDaten':U, hBeleg ).
            ASSIGN  ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.

    FOR EACH tDebop:
        FIND FIRST tBeleg 
                WHERE tBeleg.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR.
        IF NOT AVAILABLE tBeleg THEN DO:
            DELETE  tDebop.
            NEXT.
        END.
        dDatum = DATE(tBeleg.Transfer) NO-ERROR.
        IF ERROR-STATUS:ERROR OR
           dDatum = ?         THEN DO:
            DELETE  tBeleg.
            DELETE  tDebop.
        END.
    END.
    
    FIND Steuer NO-LOCK
            WHERE Steuer.Firma = '1000' NO-ERROR.
    
    FOR EACH tDebop NO-LOCK:
        FIND FIRST tBeleg 
                WHERE tBeleg.OpId = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR.
        dDatum = DATE(tBeleg.Transfer) NO-ERROR.
        IF dDatum <= 09/28/2017 THEN NEXT.
        
        dFakBetr = tDebop.FakBetr.
        EMPTY TEMP-TABLE tMwstBetr.
        FOR EACH Interf NO-LOCK
                WHERE Interf.Firma    = Steuer.Fwc09
                AND   Interf.TrNr1    = tDebop.TrNr1 
                AND   Interf.TrNr2    = tDebop.TrNr2
                AND   Interf.Herk    >= 50
                AND   Interf.Herk    <= 59
                AND   Interf.MWSt_Art = 0
                      BREAK BY Interf.Firma
                            BY Interf.TrNr1
                            BY Interf.TrNr2
                            BY Interf.Trnr :
                                    
            FIND FIRST tMwstBetr
                    WHERE tMwstbetr.Konto  = Interf.Kto2
                    AND   tMwstbetr.MwstCd = Interf.Mwst_Cd NO-ERROR.
            IF NOT AVAILABLE tMwstBetr THEN DO:
                CREATE  tMwstBetr.
                ASSIGN  tMwstBetr.Konto   = Interf.Kto2
                        tMwstBetr.MwstCd  = Interf.Mwst_Cd
                        tMwstBetr.MwstAns = tDebop.Fakwpro[Interf.Mwst_Cd].
            END.
            ASSIGN  tMwstBetr.MwstPfl = tMwstBetr.MwstPfl + Interf.Betrag.
            IF NOT LAST-OF ( Interf.TrNr2 ) THEN NEXT.
            
            BuchBetr = 0.
            lMwstAdd = FALSE.
            FOR EACH tMwstBetr
                    BREAK BY tMwstBetr.MwstCd:
                IF FIRST-OF ( tMwstBetr.MwstCd ) THEN BuchBetr = 0.
                BuchBetr = BuchBetr + tMwstBetr.MwstPfl.
                IF NOT LAST-OF ( tMwstBetr.MwstCd ) THEN NEXT.
                
                ii = tMwstBetr.MwstCd.
                IF ABS(tDebop.FakWpfl[ii] - BuchBetr) < ABS(tDebop.FakWpfl[ii] * 0.01) THEN DO.
                    lMwstAdd = TRUE.
                    LEAVE.
                END.
            END.

            /*  lMwstAdd = TRUE --> Beim Betrag muss die Mwst dazugerechnet werden  */
            /*  In der Schnittstelle werden nur Inklusiv-Beträge übermittelt        */
            IF lMwstAdd THEN DO:
                FOR EACH tMwstBetr :
                    tMwstBetr.MwstPfl = tMwstBetr.MwstPfl * (100 + tMwstBetr.MwstAns) / 100.
                END.
            END.
           
            /*  Different zwischen Fakbetr und einzelnen Buchungen errechnen    */
            /*  Differenz auf dem Konto mit dem grössten Betrag ausgleichen     */
            Buchbetr = 0.
            FOR EACH tMwstBetr:
                tMwstBetr.MwstPfl = DECIMAL(STRING(tMwstBetr.MwstPfl,'999999999.99-')).
                BuchBetr          = BuchBetr + tMwstBetr.MwstPfl.
            END.
            Buchbetr = DECIMAL(STRING(Buchbetr,'999999999.99-')).
            nDiff    = dFakBetr - Buchbetr.
            FOR FIRST tMwstBetr
                    BY tMwstBetr.MwstPfl DESCENDING:
                tMwstBetr.MwstPfl = tMwstBetr.MwstPfl + nDiff.
            END.

            ii = 0.            
            FOR EACH tMwstBetr
                    BY tMwstBetr.Konto
                    BY tMwstBetr.MwstCd:
                ii = ii + 1.
                CREATE  txBelegDetail.
                ASSIGN  txBelegDetail.DocKey       = tBeleg.DocKey
                        txBelegDetail.DocLine      = ii
                        txBelegDetail.Konto        = tMwstBetr.Konto
                        txBelegDetail.MwstTyp      = STRING(tMwstBetr.MwstCd,'99')
                        txBelegDetail.Betrag       = tMwstBetr.MwstPfl
                        txBelegDetail.BetragMwst   = 0
                        txBelegDetail.BetragFW     = 0
                        txBelegDetail.BuchTyp      = ''
                        txBelegDetail.Kostenstelle = ''
                        txBelegDetail.cCode        = ''.
            END.
        END.
    END.
    
    RETURN ODBC-RECCOUNT.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenBelegDetail Procedure 
FUNCTION lesenBelegDetail RETURNS INTEGER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    EMPTY TEMP-TABLE tBelegDetail.
    hBelegDetail = TEMP-TABLE tBelegDetail:DEFAULT-BUFFER-HANDLE.

    ODBC-QUERY = 'SELECT * FROM [Huber].[dbo].[BelegDetail]'.

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
            ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  tBelegDetail.
            DYNAMIC-FUNCTION('getODBCDaten':U, hBelegDetail ).
            ASSIGN  ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.

    FOR EACH tBelegDetail:
        FIND FIRST tBeleg NO-LOCK USE-INDEX docKey
                WHERE tBeleg.DocKey = tBelegDetail.DocKey NO-ERROR.
        IF AVAILABLE tBeleg THEN NEXT.
        DELETE tBelegDetail.
    END.
                
    RETURN ODBC-RECCOUNT.

END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-lesenOpListe) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION lesenOpListe Procedure 
FUNCTION lesenOpListe RETURNS INTEGER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR dDatum      AS DATE                     NO-UNDO.

    EMPTY TEMP-TABLE tOpListe.
    hOpListe = TEMP-TABLE tOpListe:DEFAULT-BUFFER-HANDLE.
    
    ODBC-QUERY = "SELECT * FROM [Huber].[dbo].[OpListe] WHERE OpId > '300000' ".

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
            ODBC-RECCOUNT          = ObjRecordSet:RecordCount NO-ERROR.

    DO WHILE TRUE:
        IF ODBC-RECCOUNT = 0 THEN LEAVE.
        IF ODBC-RECCOUNT = ? THEN LEAVE.

        ODBC-CURSOR = 0.
        ObjRecordSet:MoveFirst NO-ERROR.

        DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
            CREATE  tOpListe.
            DYNAMIC-FUNCTION('getODBCDaten':U, hOpListe ).
            ASSIGN  ODBC-CURSOR = ODBC-CURSOR + 1.
            ObjRecordSet:MoveNext NO-ERROR.
        END.
        LEAVE.
    END.
    
    FOR EACH tDebop:
        FIND FIRST tOpListe NO-LOCK
                WHERE tOpListe.OpID = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR.
        IF AVAILABLE tOpListe THEN DO:
            tDebop.SageSaldo = tOpListe.Betrag.
            tDebop.lOpListe  = TRUE.
        END.
        FIND FIRST tBeleg NO-LOCK
                WHERE tBeleg.OpID = TRIM(STRING(tDebop.Faknr,'>>>>>>>')) NO-ERROR.
        IF AVAILABLE tBeleg THEN DO:
            dDatum = DATE(tBeleg.Transfer) NO-ERROR.
            IF ERROR-STATUS:ERROR OR
               dDatum = ?         THEN tDebop.cTransfer = ''.
            ELSE                       tDebop.cTransfer = STRING(DATE(tBeleg.Transfer),'99.99.9999').
            FOR EACH tBelegDetail NO-LOCK
                    WHERE tBelegDetail.DocKey = tBeleg.DocKey:
                tDebop.DetailBetr = tDebop.DetailBetr + tBelegDetail.Betrag.
                CASE tBelegDetail.MwstTyp:
                    WHEN '01' OR
                    WHEN '02' THEN DO:
                        tDebop.SageAns_8  = tDebop.SageAns_8  + tBelegDetail.Betrag.
                    END.
                    WHEN '03' OR
                    WHEN '04' THEN DO:
                        tDebop.SageAns_25 = tDebop.SageAns_25 + tBelegDetail.Betrag.
                    END.
                    WHEN '11' THEN DO:
                        tDebop.SageAns_0  = tDebop.SageAns_0  + tBelegDetail.Betrag.
                    END.
                END.
            END.
        END.
        IF NOT tDebop.lOpListe    AND
           tDebop.cTransfer <> '' THEN tDebop.SageSaldo = tDebop.DetailBetr.
    END.

    RETURN ODBC-RECCOUNT.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-readDebop) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION readDebop Procedure 
FUNCTION readDebop RETURNS INTEGER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR ii      AS INT                      NO-UNDO.

    EMPTY TEMP-TABLE tDebop.
    FOR EACH Debop NO-LOCK
            WHERE Debop.Firma  = '1000'
            /*
            AND   Debop.Saldo <>  0
            */
            :
        CREATE  tDebop.
        BUFFER-COPY Debop TO tDebop
            ASSIGN  tDebop.lOpListe   = FALSE
                    tDebop.Ansatz_8   = tDebop.FakWpfl[01] + tDebop.FakWust[01]
                                      + tDebop.FakWpfl[02] + tDebop.FakWust[02]
                    tDebop.Ansatz_25  = tDebop.FakWpfl[03] + tDebop.FakWust[03]
                                      + tDebop.FakWpfl[04] + tDebop.FakWust[04]
                    tDebop.Ansatz_0   = tDebop.FakWpfl[11].
                    .
        ii = ii + 1.
    END.

    RETURN ii.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-reorgBelege) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION reorgBelege Procedure 
FUNCTION reorgBelege RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR dDatum      AS DATE                     NO-UNDO.

    FOR EACH tBeleg:
        dDatum = DATE(tBeleg.Transfer) NO-ERROR.
        IF NOT ERROR-STATUS:ERROR AND
           dDatum <> ?            THEN NEXT.
        ODBC-QUERY = "DELETE FROM [Huber].[dbo].[BelegDetail] "
                   + "WHERE DocKey  = &1 ".
        ODBC-QUERY = SUBSTITUTE(ODBC-QUERY, tBeleg.DocKey).

        ASSIGN  ObjCommand:CommandText = ODBC-QUERY
                ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 )  NO-ERROR.
    END.
                
  RETURN FALSE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-startSQLTransaction) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION startSQLTransaction Procedure 
FUNCTION startSQLTransaction RETURNS LOGICAL
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    ODBC-QUERY = 'BEGIN TRANSACTION'.

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 ).

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBeleg) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION updateBeleg Procedure 
FUNCTION updateBeleg RETURNS LOGICAL
  ( ipTHandle AS HANDLE, ipArt AS INT ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cSet    AS CHAR                     NO-UNDO.
    DEF VAR hFeld   AS HANDLE                   NO-UNDO.
    DEF VAR cFeld   AS CHAR                     NO-UNDO.

    cSet  = DYNAMIC-FUNCTION('CreateSetDaten':U, INPUT ipTHandle, INPUT ipArt) NO-ERROR.
    hFeld = ipTHandle:BUFFER-FIELD('OpId').
    cFeld = hFeld:BUFFER-VALUE(0).

    IF ipArt = 1 THEN DO:   /*  INSERT  */
        ODBC-QUERY = "INSERT INTO [Huber].[dbo].[Beleg] "
                   + cSet.
    END.
    IF ipArt = 0 THEN DO:   /*  UPDATE  */
        ODBC-QUERY = "UPDATE [Huber].[dbo].[Beleg] "
                   + cSet
                   + " WHERE OpId = '"
                   + cFeld
                   + "' ".
    END.

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE(OUTPUT ODBC-NULL, "", 32)
            NO-ERROR.

    IF ODBC-NULL <> '1' THEN DO:
        OUTPUT TO VALUE(TempName) NO-MAP NO-CONVERT APPEND.
        PUT CONTROL 'Fehler ' ODBC-NULL ' bei ' ODBC-QUERY CHR(10).
        OUTPUT CLOSE.
        RETURN FALSE.
    END.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-updateBelegDetail) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION updateBelegDetail Procedure 
FUNCTION updateBelegDetail RETURNS LOGICAL
  ( ipTHandle AS HANDLE, ipArt AS INT ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cSet    AS CHAR                     NO-UNDO.
    DEF VAR hFeld   AS HANDLE                   NO-UNDO.
    DEF VAR cFaknr  AS CHAR                     NO-UNDO.
    DEF VAR cIndex  AS CHAR                     NO-UNDO.

    cSet   = DYNAMIC-FUNCTION('CreateSetDaten':U, INPUT ipTHandle, INPUT ipArt) NO-ERROR.
    hFeld  = ipTHandle:BUFFER-FIELD('DocKey').
    cFaknr = hFeld:BUFFER-VALUE(0).
    hFeld  = ipTHandle:BUFFER-FIELD('DocLine').
    cIndex = hFeld:BUFFER-VALUE(0).

    IF ipArt = 1 THEN DO:   /*  INSERT  */
        ODBC-QUERY = "INSERT INTO [Huber].[dbo].[BelegDetail] "
                   + cSet.
    END.
    IF ipArt = 0 THEN DO:   /*  UPDATE  */
        ODBC-QUERY = "UPDATE [Huber].[dbo].[BelegDetail] "
                   + cSet
                   + " WHERE DocKey = '"
                   + cFaknr
                   + "' "
                   + "AND  DocLine = '"
                   + cIndex
                   + "'".
    END.

    ASSIGN  ObjCommand:CommandText = ODBC-QUERY
            ObjRecordSet           = ObjCommand:EXECUTE( OUTPUT ODBC-NULL, "", 32 )
            NO-ERROR.

    IF ODBC-NULL <> '1' THEN DO:
        OUTPUT TO VALUE(TempName) NO-MAP NO-CONVERT APPEND.
        PUT CONTROL 'Fehler ' ODBC-NULL ' bei ' ODBC-QUERY CHR(10).
        OUTPUT CLOSE.
        RETURN FALSE.
    END.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF

&IF DEFINED(EXCLUDE-writeLogFiles) = 0 &THEN

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION writeLogFiles Procedure 
FUNCTION writeLogFiles RETURNS LOGICAL
  ( ipMessage AS CHAR  ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/

    DEF VAR cString         AS CHAR                     NO-UNDO.

    cString = SUBSTITUTE('&1 &2 -> &3', STRING(TODAY,'99.99.9999'), STRING(TIME,'HH:MM:SS'), ipMessage).
    OUTPUT STREAM LogFile TO VALUE(TempName) APPEND.
    PUT STREAM LogFile CONTROL cString CHR(10).
    OUTPUT STREAM LogFile CLOSE.

    RETURN TRUE.

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ENDIF