&ANALYZE-SUSPEND _VERSION-NUMBER AB_v10r12 /* Procedure Description "Structured Procedure File Template. Use this template to create a new Structured Procedure file to compile and run PROGRESS 4GL code. You edit structured procedure files using the AB's Section Editor." */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure /*------------------------------------------------------------------------ File : ReadISOBESRDatei.p Purpose : Syntax : Description : Author(s) : walter.riechsteiner Created : Thu Mar 24 11:59:46 CET 2022 Notes : ----------------------------------------------------------------------*/ /*----------------------------------------------------------------------*/ /* *************************** Definitions ************************** */ DEF INPUT PARAMETER ipDateiName AS CHAR NO-UNDO. DEF INPUT-OUTPUT PARAMETER ophHeader AS HANDLE NO-UNDO. DEF INPUT-OUTPUT PARAMETER ophSubHeader AS HANDLE NO-UNDO. DEF INPUT-OUTPUT PARAMETER ophZahlung AS HANDLE NO-UNDO. /*DEFINE VARIABLE ipDateiName AS CHARACTER INIT 'N:\12_ESR\camt054_esr_ch3280808006672314278_20220307094125.xml'.*/ DEFINE VARIABLE iId AS INTEGER NO-UNDO INIT 0. DEFINE VARIABLE iSeq AS INTEGER NO-UNDO INIT 0. DEFINE VARIABLE iZahlung AS INTEGER NO-UNDO INIT 0. { incl/ttcamt054_esr.i } DEFINE TEMP-TABLE ttZahlungen FIELD iId AS INTEGER FIELD cZahler AS CHARACTER FIELD cIBAN AS CHARACTER FIELD cFrw AS CHARACTER FIELD iAnzZahl AS INTEGER FIELD nTotalBetrag AS DECIMAL FIELD nTotalTaxen AS DECIMAL. DEFINE TEMP-TABLE ttBuchungen NO-UNDO FIELD iId AS INTEGER FIELD cFrw AS CHARACTER FIELD cTeilNr AS CHARACTER FIELD cZahlArt AS CHARACTER FIELD dBuchdat AS DATE FIELD dValuta AS DATE FIELD cReferenz AS CHARACTER FIELD nBetrag AS DECIMAL FIELD nTaxen AS DECIMAL . DEFINE TEMP-TABLE Document NO-UNDO FIELD foo AS LOGICAL XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE BkToCstmrDbtCdtNtfctn NO-UNDO FIELD foo AS LOGICAL XML-NODE-TYPE "HIDDEN" FIELD Document_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE GrpHdr NO-UNDO FIELD MsgId AS CHARACTER FIELD CreDtTm AS CHARACTER FIELD AddtlInf AS CHARACTER FIELD BkToCstmrDbtCdtNtfctn_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE MsgRcpt NO-UNDO FIELD Nm AS CHARACTER FIELD GrpHdr_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE MsgPgntn NO-UNDO FIELD PgNb AS INTEGER FIELD LastPgInd AS LOGICAL FIELD GrpHdr_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Ntfctn NO-UNDO FIELD Id AS CHARACTER FIELD ElctrncSeqNb AS INTEGER FIELD CreDtTm AS CHARACTER FIELD BkToCstmrDbtCdtNtfctn_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Acct NO-UNDO FIELD Ccy AS CHARACTER FIELD Ntfctn_id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Id NO-UNDO FIELD IBAN AS CHARACTER FIELD Acct_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Svcr NO-UNDO FIELD Acct_id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE FinInstnId NO-UNDO FIELD Nm AS CHARACTER FIELD Svcr_id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE TxsSummry NO-UNDO FIELD Ntfctn_id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE TtlNtries NO-UNDO FIELD NbOfNtries AS INTEGER FIELD Sum AS DECIMAL FIELD TxsSummry_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Ntry NO-UNDO FIELD NtryRef AS CHARACTER FIELD CdtDbtInd AS CHARACTER FIELD RvslInd AS LOGICAL FIELD Sts AS CHARACTER FIELD AcctSvcrRef AS CHARACTER FIELD Ntfctn_id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Amt NO-UNDO FIELD CCy AS CHARACTER XML-NODE-TYPE "ATTRIBUTE" FIELD Amt AS DECIMAL XML-NODE-TYPE "TEXT" FIELD Ntry_Id AS RECID XML-NODE-TYPE "HIDDEN" FIELD TxDtls_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE BookgDt NO-UNDO FIELD Dt AS CHARACTER FIELD Ntry_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE ValDt NO-UNDO FIELD Dt AS CHARACTER FIELD Ntry_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE NtryDtls NO-UNDO FIELD Ntry_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE TxDtls NO-UNDO FIELD CdtDbtInd AS CHARACTER FIELD NtryDtls_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Chrgs NO-UNDO FIELD Ntry_Id AS RECID XML-NODE-TYPE "HIDDEN" FIELD TxDtls_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE TtlChrgsAndTaxAmt NO-UNDO FIELD Ccy AS CHARACTER XML-NODE-TYPE "ATTRIBUTE" FIELD TtlChrgsAndTaxAmt AS DECIMAL XML-NODE-TYPE "TEXT" FIELD Chrgs_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE RmtInf NO-UNDO FIELD TxDtls_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Strd NO-UNDO FIELD RmtInf_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE CdtrRefInf NO-UNDO FIELD Ref AS CHARACTER FIELD Strd_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE Tp NO-UNDO FIELD CdtrRefInf_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE TEMP-TABLE CdOrPrtry NO-UNDO FIELD Prtry AS CHARACTER FIELD Cd AS CHARACTER FIELD Tp_Id AS RECID XML-NODE-TYPE "HIDDEN" . DEFINE DATASET camt54 FOR Document, BkToCstmrDbtCdtNtfctn, GrpHdr, MsgRcpt, MsgPgntn, Ntfctn, Acct, Svcr, FinInstnId, Id, TxsSummry, TtlNtries, Ntry, Amt, BookgDt, ValDt, Chrgs, NtryDtls, TxDtls, TtlChrgsAndTaxAmt, RmtInf, Strd, CdtrRefInf, Tp, CdOrPrtry PARENT-ID-RELATION Relation001 FOR Document, BkToCstmrDbtCdtNtfctn PARENT-ID-FIELD Document_id PARENT-ID-RELATION Relation002 FOR BkToCstmrDbtCdtNtfctn, GrpHdr PARENT-ID-FIELD BkToCstmrDbtCdtNtfctn_Id PARENT-ID-RELATION Relation003 FOR GrpHdr, MsgRcpt PARENT-ID-FIELD GrpHdr_Id PARENT-ID-RELATION Relation004 FOR GrpHdr, MsgPgntn PARENT-ID-FIELD GrpHdr_Id PARENT-ID-RELATION Relation005 FOR BkToCstmrDbtCdtNtfctn, Ntfctn PARENT-ID-FIELD BkToCstmrDbtCdtNtfctn_Id PARENT-ID-RELATION Relation006 FOR Ntfctn, Acct PARENT-ID-FIELD Ntfctn_Id PARENT-FIELDS-BEFORE (Id, ElctrncSeqNb, CreDtTm) PARENT-ID-RELATION Relation007 FOR Acct, Id PARENT-ID-FIELD Acct_Id PARENT-FIELDS-AFTER (Ccy) PARENT-ID-RELATION Relation008 FOR Acct, Svcr PARENT-ID-FIELD Acct_Id PARENT-ID-RELATION Relation009 FOR Svcr, FinInstnId PARENT-ID-FIELD Svcr_Id PARENT-ID-RELATION Relation010 FOR Ntfctn, TxsSummry PARENT-ID-FIELD Ntfctn_Id PARENT-ID-RELATION Relation011 FOR TxsSummry, TtlNtries PARENT-ID-FIELD TxsSummry_Id PARENT-ID-RELATION Relation012 FOR Ntfctn, Ntry PARENT-ID-FIELD Ntfctn_Id PARENT-ID-RELATION Relation013 FOR Ntry, Amt PARENT-ID-FIELD Ntry_Id PARENT-FIELDS-BEFORE (NtryRef) PARENT-FIELDS-AFTER (CdtDbtInd, RvslInd, Sts, AcctSvcrRef) PARENT-ID-RELATION Relation014 FOR Ntry, BookgDt PARENT-ID-FIELD Ntry_Id PARENT-ID-RELATION Relation015 FOR Ntry, ValDt PARENT-ID-FIELD Ntry_Id PARENT-ID-RELATION Relation016 FOR Ntry, Chrgs PARENT-ID-FIELD Ntry_Id PARENT-ID-RELATION Relation017 FOR Ntry, NtryDtls PARENT-ID-FIELD Ntry_Id PARENT-ID-RELATION Relation018 FOR NtryDtls, TxDtls PARENT-ID-FIELD NtryDtls_Id PARENT-ID-RELATION Relation019 FOR TxDtls, Amt PARENT-ID-FIELD TxDtls_Id PARENT-ID-RELATION Relation020 FOR TxDtls, Chrgs PARENT-ID-FIELD TxDtls_Id PARENT-ID-RELATION Relation021 FOR Chrgs, TtlChrgsAndTaxAmt PARENT-ID-FIELD Chrgs_Id PARENT-ID-RELATION Relation022 FOR TxDtls, RmtInf PARENT-ID-FIELD TxDtls_Id PARENT-ID-RELATION Relation023 FOR RmtInf, Strd PARENT-ID-FIELD RmtInf_Id PARENT-ID-RELATION Relation024 FOR Strd, CdtrRefInf PARENT-ID-FIELD Strd_Id PARENT-ID-RELATION Relation025 FOR CdtrRefInf, TP PARENT-ID-FIELD CdtrRefInf_Id PARENT-FIELDS-AFTER (Ref) PARENT-ID-RELATION Relation026 FOR Tp, CdOrPrtry PARENT-ID-FIELD Tp_Id . /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK /* ******************** Preprocessor Definitions ******************** */ /* _UIB-PREPROCESSOR-BLOCK-END */ &ANALYZE-RESUME /* *********************** Procedure Settings ************************ */ &ANALYZE-SUSPEND _PROCEDURE-SETTINGS /* Settings for THIS-PROCEDURE Type: Procedure Template Allow: Frames: 0 Add Fields to: Neither Other Settings: CODE-ONLY COMPILE */ &ANALYZE-RESUME _END-PROCEDURE-SETTINGS /* ************************* Create Window ************************** */ &ANALYZE-SUSPEND _CREATE-WINDOW /* DESIGN Window definition (used by the UIB) CREATE WINDOW Procedure ASSIGN HEIGHT = 15 WIDTH = 60. /* END WINDOW DEFINITION */ */ &ANALYZE-RESUME &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure /* *************************** Main Block *************************** */ /*DATASET camt54:READ-XML("file", "C:\Entwicklung\TEMP\LaTorre\E010518757_20220322081701.xml", "empty", ?, FALSE, ?, "IGNORE") NO-ERROR.*/ DATASET camt54:READ-XML("file", ipDateiname, "empty", ?, FALSE, ?, "IGNORE") NO-ERROR. IF ERROR-STATUS:ERROR THEN DO: MESSAGE 'XML-Datei kann nicht eingelesen werden' VIEW-AS ALERT-BOX. RETURN 'ERROR'. END. EMPTY TEMP-TABLE tHeader . EMPTY TEMP-TABLE tSubHeader. EMPTY TEMP-TABLE tZahlung . FOR EACH BkToCstmrDbtCdtNtfctn NO-LOCK, FIRST GrpHdr NO-LOCK WHERE GrpHdr.BkToCstmrDbtCdtNtfctn_Id = RECID(BkToCstmrDbtCdtNtfctn). FIND MsgRcpt NO-LOCK WHERE MsgRcpt.GrpHdr_Id = RECID(GrpHdr) NO-ERROR. FIND MsgPgntn NO-LOCK WHERE MsgPgntn.GrpHdr_Id = RECID(GrpHdr) NO-ERROR. FOR EACH Ntfctn NO-LOCK WHERE Ntfctn.BkToCstmrDbtCdtNtfctn_Id = RECID(BkToCstmrDbtCdtNtfctn): FIND Acct NO-LOCK WHERE Acct.Ntfctn_id = RECID(Ntfctn) NO-ERROR. FIND Id NO-LOCK WHERE Id.Acct_Id = RECID(Acct) NO-ERROR. FIND Svcr NO-LOCK WHERE Svcr.Acct_Id = RECID(Acct) NO-ERROR. IF AVAILABLE Svcr THEN DO: FIND FinInstnId NO-LOCK WHERE FinInstnId.Svcr_id = RECID(Svcr) NO-ERROR. END. FIND TxsSummry NO-LOCK WHERE TxsSummry.Ntfctn_id = RECID(Ntfctn) NO-ERROR. IF AVAILABLE TxsSummry THEN DO: FIND TtlNtries NO-LOCK WHERE TtlNtries.TxsSummry_Id = RECID(TxsSummry) NO-ERROR. END. iId = iId + 1. CREATE tHeader. ASSIGN tHeader.iInd = iId tHeader.Sabe = (IF AVAILABLE MsgRcpt THEN MsgRcpt.Nm ELSE '') tHeader.Prod = GrpHdr.AddtlInf tHeader.MsgId = GrpHdr.MsgId tHeader.Datum = GrpHdr.CreDtTm tHeader.BankSeqNr = STRING(Ntfctn.ElctrncSeqNb) tHeader.IBAN = (IF AVAILABLE Id THEN Id.IBAN ELSE '') tHeader.Bank = (IF AVAILABLE FinInstnId THEN FinInstnId.Nm ELSE '') tHeader.Frw = Acct.Ccy tHeader.Total = (IF AVAILABLE TtlNtries THEN TtlNtries.Sum ELSE 0.00) tHeader.Anzahl = (IF AVAILABLE TtlNtries THEN TtlNtries.NbOfNtries ELSE 0) . iSeq = 0. FOR EACH Ntry NO-LOCK WHERE Ntry.Ntfctn_id = RECID(Ntfctn): FIND BookgDt NO-LOCK WHERE BookgDt.Ntry_Id = RECID(Ntry) NO-ERROR. FIND ValDt NO-LOCK WHERE ValDt.Ntry_Id = RECID(Ntry) NO-ERROR. FIND Amt NO-LOCK WHERE Amt.Ntry_Id = RECID(Ntry) NO-ERROR. FIND Chrgs NO-LOCK WHERE Chrgs.Ntry_Id = RECID(Ntry) NO-ERROR. IF AVAILABLE Chrgs THEN DO: FIND TtlChrgsAndTaxAmt NO-LOCK WHERE TtlChrgsAndTaxAmt.Chrgs_Id = RECID(Chrgs) NO-ERROR. END. iSeq = iSeq + 1. CREATE tSubHeader. ASSIGN tSubHeader.iInd = tHeader.iInd tSubHeader.iSeq = iSeq tSubHeader.TeilnrNr = Ntry.NtryRef tSubHeader.Buchdat = BookgDt.Dt tSubHeader.Valuta = ValDt.Dt tSubHeader.Frw = Amt.CCy tSubHeader.Total = Amt.Amt tSubHeader.Taxen = (IF AVAILABLE TtlChrgsAndTaxAmt THEN TtlChrgsAndTaxAmt.TtlChrgsAndTaxAmt ELSE 0.00) tSubHeader.lStorno = (IF Ntry.CdtDbtInd = 'CRDT' THEN FALSE ELSE TRUE) . FIND NtryDtls NO-LOCK WHERE NtryDtls.Ntry_Id = RECID(Ntry) NO-ERROR. iZahlung = 0. FOR EACH TxDtls NO-LOCK WHERE TxDtls.NtryDtls_Id = RECID(NtryDtls): FIND Amt NO-LOCK WHERE Amt.TxDtls_Id = RECID(TxDtls) NO-ERROR. FIND RmtInf NO-LOCK WHERE RmtInf.TxDtls_Id = RECID(TxDtls) NO-ERROR. FIND Strd NO-LOCK WHERE Strd.RmtInf_Id = RECID(RmtInf) NO-ERROR. FIND CdtrRefInf NO-LOCK WHERE CdtrRefInf.Strd_Id = RECID(Strd) NO-ERROR. FIND Chrgs NO-LOCK WHERE Chrgs.TxDtls_Id = RECID(TxDtls) NO-ERROR. IF AVAILABLE Chrgs THEN DO: FIND TtlChrgsAndTaxAmt NO-LOCK WHERE TtlChrgsAndTaxAmt.Chrgs_Id = RECID(Chrgs) NO-ERROR. END. iZahlung = iZahlung + 1. CREATE tZahlung. ASSIGN tZahlung.iInd = tSubHeader.iInd tZahlung.iSeq = tSubHeader.iSeq tZahlung.iZahlung = iZahlung tZahlung.Betrag = Amt.Amt tZahlung.Frw = Amt.CCy tZahlung.lStorno = (IF TxDtls.CdtDbtInd = 'CRDT' THEN FALSE ELSE TRUE) tZahlung.Taxen = (IF AVAILABLE TtlChrgsAndTaxAmt THEN TtlChrgsAndTaxAmt.TtlChrgsAndTaxAmt ELSE 0.00) tZahlung.TaxFrw = (IF AVAILABLE TtlChrgsAndTaxAmt THEN TtlChrgsAndTaxAmt.Ccy ELSE '' ) tZahlung.Referenz = CdtrRefInf.Ref tZahlung.lBetrag = TRUE. DO WHILE TRUE: FIND Tp NO-LOCK WHERE Tp.CdtrRefInf_Id = RECID(CdtrRefInf) NO-ERROR. IF NOT AVAILABLE Tp THEN LEAVE. FIND CdOrPrtry NO-LOCK WHERE CdOrPrtry.Tp_Id = RECID(Tp) NO-ERROR. IF NOT AVAILABLE CdOrPrtry THEN LEAVE. IF CdOrPrtry.Prtry <> '' THEN tZahlung.AufRef = CdOrPrtry.Prtry. IF CdOrPrtry.Cd <> '' THEN tZahlung.AufRef = CdOrPrtry.Cd . LEAVE. END. IF tZahlung.AufRef = '' THEN tZahlung.AufRef = 'BESR'. END. END. END. END. DATASET camt54:WRITE-XML("file", "C:\Entwicklung\TEMP\LaTorre\CAMT054.xml", TRUE, 'UTF-8', '', FALSE) NO-ERROR. FOR EACH tHeader WHERE tHeader.Anzahl = 0: FOR EACH tZahlung WHERE tZahlung.iInd = tHeader.iInd: ASSIGN tHeader.Anzahl = tHeader.Anzahl + 1 tHeader.Total = tHeader.Total + tZahlung.Betrag tHeader.Taxen = tHeader.Taxen + tZahlung.Taxen. END. END. FOR EACH tHeader: ophHeader:BUFFER-CREATE(). ophHeader:BUFFER-COPY(htHeader). END. FOR EACH tSubHeader: ophSubHeader:BUFFER-CREATE(). ophSubHeader:BUFFER-COPY(htSubHeader). END. FOR EACH tZahlung: ophZahlung:BUFFER-CREATE(). ophZahlung:BUFFER-COPY(htZahlung). END. /*OUTPUT TO 'C:\Entwicklung\TEMP\LaTorre\tHeader.csv' NO-MAP NO-CONVERT. */ /*FOR EACH tHeader: */ /* EXPORT DELIMITER ';' tHeader. */ /*END. */ /*OUTPUT CLOSE. */ /* */ /*OUTPUT TO 'C:\Entwicklung\TEMP\LaTorre\tSubHeader.csv' NO-MAP NO-CONVERT.*/ /*FOR EACH tSUbHeader: */ /* EXPORT DELIMITER ';' tSubHeader. */ /*END. */ /*OUTPUT CLOSE. */ /* */ /*OUTPUT TO 'C:\Entwicklung\TEMP\LaTorre\tZahlung.csv' NO-MAP NO-CONVERT. */ /*FOR EACH tZahlung: */ /* EXPORT DELIMITER ';' tZahlung. */ /*END. */ /*OUTPUT CLOSE. */ /* _UIB-CODE-BLOCK-END */ &ANALYZE-RESUME