Version vom 10. Dezember 2021, 15:43 Uhr

Makros und Scripting

Beispiele

Beispiele Sammlung

Benutzerdefinierte Datenbanktabellen

Developer

Beispiele

Sonstiges

DMS Abkürzungen

Hier finden Sie eine Übersicht zu Makros und Funktionen die für die Artikelpflege gedacht sind.

ZUGFERD

Es gibt die Möglichkeit den Import und Export von ZUGFERD Dateien per Scripting zu ändern, falls der OBS-Standard nicht ausreichen sollte.

Import

Wenn es einen aktiven Eintrag in der Script Libary mit dem Namen IMPORT_ZUGFERD gibt wird der Import über die Pax Funktion ausgeführt.

Felder/Strukturen

    TZugPerson = class(TObject)
        cZUG_PsNr          : String;
        cZUG_Bemerkung     : String;
        cZUG_Name          : String;
        cZUG_Zeile1        : String;
        cZUG_Zeile2        : String;
        cZUG_Zeile3        : String;
        cZUG_Ort           : String;
        cZUG_PLZ           : String;
        cZUG_Land          : String;
        cZUG_AnspName      : String;
        cZUG_AnspTel       : String;
        cZUG_AnspEMail     : String;
        aFinanzIDs         : TxFinazID;

        Procedure AddFinanzID(cTmp1:String; cTmp2:String);

----------------------------------------

    TZugPosition = class(TObject)
        cZUP_UID          : String;
        cZUP_PosTyp       : String;
        cZUP_PosNr        : String;
        cZUP_ArtNr        : String;
        cZUP_ArtNrLief    : String;
        cZUP_ArtNrPsNr    : String;
        cZUP_ArtTyp       : String;
        cZUP_Bez1         : String;
        cZUP_Bez2         : String;
        cZUP_WGRName      : String;
        cZUP_LiefNr       : String;
        cZUP_Einheit      : String;
        cZUP_Text         : String;
        nZUP_Menge        : Double;
        nZUP_ENetto       : Double;
        nZUP_GNetto       : Double;
        nZUP_EBrutto      : Double;
        nZUP_MwstBetrag   : Double;
        nZUP_MwstSatz     : Double;
        cZUP_MWSTTyp      : String;
        cZUP_OBS_ERLKTO   : String;
        cZUP_OBS_Einheit  : String;
        cZUP_OBS_ArtNr    : String;
        cZUP_OBS_LiefNr   : String;
        cZUP_OBS_MwstSchl : String;
        cZUP_Fehler       : String;

----------------------------------------

    TZugHeaderFinanzen = class(TObject)
        oParent            : TZugFerd;
        cZUG_Waehrung      : String;
        nZUG_Netto         : Double;
        nZUG_Brutto        : Double;
        nZUG_MwstBetrag    : Double;
        nZUG_Netto1        : Double;
        nZUG_Netto2        : Double;
        nZUG_Netto3        : Double;
        nZUG_Brutto1       : Double;
        nZUG_Brutto2       : Double;
        nZUG_Brutto3       : Double;
        nZUG_MwstBetrag1   : Double;
        nZUG_MwstBetrag2   : Double;
        nZUG_MwstBetrag3   : Double;
        nZUG_MwstSatz1     : Double;
        nZUG_MwstSatz2     : Double;
        nZUG_MwstSatz3     : Double;
        cZUG_MwstSchl1     : String;
        cZUG_MwstSchl2     : String;
        cZUG_MwstSchl3     : String;
        cZUG_Zahlbed       : String;
        dZUG_FDatum        : TDateTime;

----------------------------------------

    TxZugPosition = Class(TObjectList<TZugPosition>);

    TZugFerd = class(TObject)
        oMyDB            : TxDB;
        oPersonen        : TZugHeaderPersonen;
        oFinanzen        : TZugHeaderFinanzen;
        oLieferung       : TZugHeaderLieferung;
        oPositionen      : TxZugPosition;
        cError           : String;
        lError           : Boolean;

        cZUG_UID         : String;
        oZUG_XML         : TStringList;
        cZUG_PDFName     : String;
        cZUG_DMSTyp      : String;
        cZUG_MD5         : String;
        cZUG_Nr          : String;
        cZUG_PDFData     : String;
        cZUG_Schema      : String;
        cZUG_TypCode     : String;

        cZUG_Fehler      : String;
        cZUG_Bestaetigt  : String;
        cZUG_SB          : String;
        cZUG_SysUID      : String;

        dZUG_RDatum      : TDateTime;
        dZUG_SDatum      : TDateTime;
        dZUG_ADatum      : TDateTime;

        function  InsertToPositionen     (nPos:Integer; oPos:TZugPosition):integer;
        function  AddToPositionen        (oPos:TZugPosition):integer;
        function  nPosCnt                ():Integer;
        function  GetPosition            (nPos:Integer):TZugPosition;
        function  AddProto               (const cMessage:String):Boolean;
        function  AddFile                (const cFileName:String; const cDMSType:String):Boolean;

Beispiel Script Import

//------------------------------------------------------------------------------
// Unit Name: IMPORT_ZUGFERD
// Author:    JB
// Date:      09-12-2021
//------------------------------------------------------------------------------

var oZugFerd : TZugFerd;
    oCW      : TfrmCW;

//------------------------------------------------------------------------------
//  Procedure: PersonHeader_Import_XML
//  Author:    JB
//  Date:      09-12-2021
//  Comment:
//------------------------------------------------------------------------------

function PersonHeader_Import_XML(oZugPersHeader:TZugPerson; cTradePartyXML:String):Boolean;
var nPos   : Integer;
    nStart : integer;
    cTemp1 : String;
    cTemp2 : String;
    cTemp3 : String;
begin
    CWPrintn(oCW, 'Importiere Header');
    GetXMLToken(cTradePartyXML,'ram:Name', oZugPersHeader.cZUG_Name);

    if (GetXMLToken(cTradePartyXML,'ram:PostalTradeAddress',cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:LineOne'     , oZugPersHeader.cZUG_Zeile1);
        GetXMLToken(cTemp1,'ram:CityName'    , oZugPersHeader.cZUG_Ort);
        GetXMLToken(cTemp1,'ram:PostcodeCode', oZugPersHeader.cZUG_PLZ);
        GetXMLToken(cTemp1,'ram:CountryID'   , oZugPersHeader.cZUG_Land);
    end;
    
    CWPrintn(oCW, 'Importiere Tax');

    nPos := 1;
    while (GetXMLToken(cTradePartyXML,'ram:SpecifiedTaxRegistration', nPos, cTemp1, False)) do begin
        nStart := 1;
        GetXMLTokenValue(cTemp1,'ram:ID', nStart, cTemp2, cTemp3); // value, key
        oZugPersHeader.AddFinanzID(cTemp3, cTemp2);
    end;
end;

//------------------------------------------------------------------------------
//  Procedure: Personen_Import_XML
//  Author:    JB
//  Date:      09-12-2021
//  Comment:
//------------------------------------------------------------------------------

function Personen_Import_XML(cXMLTradeAgreement:String):Boolean;
var cTemp1 : String;
begin
    CWPrintn(oCW, 'Importiere Auftragsnummer Kunde');

    GetXMLToken(cXMLTradeAgreement,'ram:BuyerReference',oZugFerd.oPersonen.cAufNrKu);

    if (GetXMLToken(cXMLTradeAgreement,'ram:BuyerTradeParty',cTemp1)) then begin
        PersonHeader_Import_XML(oZugFerd.oPersonen.oZUG_Buyer, cTemp1);
    end;

    if (GetXMLToken(cXMLTradeAgreement,'ram:SellerTradeParty',cTemp1)) then begin
        PersonHeader_Import_XML(oZugFerd.oPersonen.oZUG_Seller,cTemp1);
    end;
end;

//------------------------------------------------------------------------------
//  Procedure: Lieferung_Import_XML
//  Author:    JB
//  Date:      09-12-2021
//  Comment:
//------------------------------------------------------------------------------

function Lieferung_Import_XML(cXML:String):Boolean;
begin
    //Wenn Benötigt
    CWPrintn(oCW, 'Importiere Lieferung');
end;

//------------------------------------------------------------------------------
//  Procedure: Positionen_Import_XML
//  Author:    JB
//  Date:      09-12-2021
//  Comment:
//------------------------------------------------------------------------------

function Positionen_Import_XML(oPos:TZugPosition; cXMLTradeLineItem:String):Boolean;
var cTemp1    : String;
    cTemp2    : String;
    cValue    : String;
    nPos      : integer;
begin

    nPos := 1;
    
    CWPrintn(oCW, 'Importiere Positionen');

    if (GetXMLToken(cXMLTradeLineItem,'ram:AssociatedDocumentLineDocument',cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:LineID',oPos.cZUP_PosNr);
    end;

    if (GetXMLToken(cXMLTradeLineItem,'ram:SpecifiedTradeProduct',cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:Name'             , oPos.cZUP_Bez1 );
        GetXMLToken(cTemp1,'ram:SellerAssignedID' , oPos.cZUP_ArtNrLief);
        GetXMLTokenValue(cTemp1, 'ram:GlobalID'   , nPos, oPos.cZUP_ArtNr, oPos.cZUP_ArtTyp);
        GetXMLToken(cTemp1,'ram:Description'      , oPos.cZUP_Text);

        //Warengruppe

        if GetXMLToken(cTemp1,'ram:DesignatedProductClassification', cTemp2) then begin
            //nPos := 1;
            GetXMLToken(cTemp2,'ram:ClassName', oPos.cZUP_WGRName);
        end;
    end;

    if (GetXMLToken(cXMLTradeLineItem,'ram:SpecifiedLineTradeAgreement',cTemp1)) then begin
        if (GetXMLToken(cTemp1,'ram:GrossPriceProductTradePrice',cTemp2)) then begin
            GetXMLToken(cTemp2,'ram:ChargeAmount', cValue);
            oPos.nZUP_EBrutto := fVAl(cValue);
        end;
        if (GetXMLToken(cTemp1,'ram:NetPriceProductTradePrice',cTemp2)) then begin
            GetXMLToken(cTemp2,'ram:ChargeAmount', cValue);
            oPos.nZUP_ENetto := fVAl(cValue);
        end;
    end;

    if (GetXMLToken(cXMLTradeLineItem,'ram:SpecifiedLineTradeDelivery', cTemp1)) then begin
        GetXMLTokenValue(cTemp1,'ram:BilledQuantity', nPos, cValue, oPos.cZUP_Einheit);
        oPos.nZUP_Menge := fVAl(cValue);
    end;

    if (GetXMLToken(cXMLTradeLineItem,'ram:SpecifiedLineTradeSettlement',cTemp1)) then begin
        if (GetXMLToken(cTemp1,'ram:ApplicableTradeTax',cTemp2)) then begin
            GetXMLToken(cTemp2,'ram:RateApplicablePercent', cValue);
            oPos.nZUP_MwstSatz := fVAl(cValue);
            GetXMLToken(cTemp2,'ram:TypeCode', oPos.cZUP_MWSTTyp );
        end;

        GetXMLToken(cTemp1,'ram:LineTotalAmount', cValue);
        oPos.nZUP_GNetto := fVAl(cValue);
    end;

    if (oPos.nZUP_ENetto > 0) and (oPos.nZUP_MwstSatz > 0) then begin
        oPos.nZUP_MwstBetrag := oPos.nZUP_ENetto * (oPos.nZUP_MwstSatz / 100);
    end;

    oPos.cZUP_Fehler      := '';
end;

//------------------------------------------------------------------------------
//  Procedure: Finanzen_Import_XML
//  Author:    JB
//  Date:      09-12-2021
//  Comment:
//------------------------------------------------------------------------------

function Finanzen_Import_XML(oFinanzen:TZugHeaderFinanzen; cXMLTradeSettlement:String):Boolean;
var cTemp1     : String;
    cTemp2     : String;
    cTemp3     : String;
    cTemp4     : String;
    nPos       : Integer;
    oPos       : TZugPosition;
    oPosTmp    : TZugPosition;
    cIndikator : String;
    lIndikator : Boolean;
    cText      : String;
    nPosNr     : Integer;
    cPosNr     : String;
    i          : integer;
begin

    CWPrintn(oCW, 'Importiere Finanzen');

    lIndikator := false;
    
    if (GetXMLToken(cXMLTradeSettlement,'ram:InvoiceCurrencyCode',cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:InvoiceCurrencyCode', oFinanzen.cZUG_Waehrung);
    end;

    if (GetXMLToken(cXMLTradeSettlement,'ram:SpecifiedTradeSettlementPaymentMeans',cTemp1)) then begin
    
        oPos             := TZugPosition.Create();
        oPos.cZUP_UID    :=  TZugFerd(oFinanzen.oParent).cZUG_UID;
        oPos.cZUP_PosTyp := ZUG_POSTYP_MEMO;
        
        if (GetXMLToken(cTemp1,'ram:PayeePartyCreditorFinancialAccount',cTemp2)) then begin
            if (GetXMLToken(cTemp2,'ram:IBANID',cTemp3)) then begin
                cText := 'Betrag zu überweisen an:' + CRLF + 'IBAN ' + cTemp3;
            end;
        end;

        oPos.cZUP_Text := cText;
        //TObjectList(TZugFerd(oFinanzen.oParent).oPositionen).Insert(0, oPos);
        TZugFerd(oFinanzen.oParent).InsertToPositionen(0, oPos);
    end;

    if (GetXMLToken(cXMLTradeSettlement,'ram:SpecifiedTradeSettlementHeaderMonetarySummation',cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:TaxBasisTotalAmount', cTemp2);
        oZugFerd.oFinanzen.nZUG_Netto := fVal(cTemp2);
        GetXMLToken(cTemp1,'ram:TaxTotalAmount', cTemp2);
        oFinanzen.nZUG_MwstBetrag := fVal(cTemp2);
        GetXMLToken(cTemp1,'ram:GrandTotalAmount', cTemp2);
        oFinanzen.nZUG_Brutto := fVal(cTemp2);
    end;

    if (GetXMLToken(cXMLTradeSettlement,'ram:SpecifiedTradePaymentTerms',cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:Description', oFinanzen.cZUG_Zahlbed);
        if (GetXMLToken(cTemp1,'ram:DueDateDateTime',cTemp2, False)) then begin
            GetXMLTokenValue(cTemp2,'udt:DateTimeString', nPos,cTemp4, cTemp3);
            oFinanzen.dZUG_FDatum := SToD(cTemp4);
        end;
    end;

    nPos := 1;
    if (GetXMLToken(cXMLTradeSettlement,'ram:ApplicableTradeTax', nPos, cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:CalculatedAmount', cTemp2);
        oFinanzen.nZUG_MwstBetrag1 := fVal(cTemp2);

        GetXMLToken(cTemp1,'ram:BasisAmount', cTemp2);
        oFinanzen.nZUG_Netto1  := fVal(cTemp2);
        oFinanzen.nZUG_Brutto1 := oFinanzen.nZUG_Netto1 + oFinanzen.nZUG_MwstBetrag1;

        GetXMLToken(cTemp1,'ram:RateApplicablePercent', cTemp2);
        oFinanzen.nZUG_MwstSatz1 := fVal(cTemp2);
    end;

    if (GetXMLToken(cXMLTradeSettlement,'ram:ApplicableTradeTax', nPos, cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:CalculatedAmount', cTemp2);
        oFinanzen.nZUG_MwstBetrag2 := fVal(cTemp2);

        GetXMLToken(cTemp1,'ram:BasisAmount', cTemp2);
        oFinanzen.nZUG_Netto2  := fVal(cTemp2);
        oFinanzen.nZUG_Brutto2 := oFinanzen.nZUG_Netto2 + oFinanzen.nZUG_MwstBetrag2;

        GetXMLToken(cTemp1,'ram:RateApplicablePercent', cTemp2);
        oFinanzen.nZUG_MwstSatz2 := fVal(cTemp2);
    end;

    if (GetXMLToken(cXMLTradeSettlement,'ram:ApplicableTradeTax', nPos, cTemp1)) then begin
        GetXMLToken(cTemp1,'ram:CalculatedAmount', cTemp2);
        oFinanzen.nZUG_MwstBetrag3 := fVal(cTemp2);

        GetXMLToken(cTemp1,'ram:BasisAmount', cTemp2);
        oFinanzen.nZUG_Netto3  := fVal(cTemp2);
        oFinanzen.nZUG_Brutto3 := oFinanzen.nZUG_Netto3 + oFinanzen.nZUG_MwstBetrag3;

        GetXMLToken(cTemp1,'ram:RateApplicablePercent', cTemp2);
        oFinanzen.nZUG_MwstSatz3 := fVal(cTemp2);
    end;

    if (oFinanzen.nZUG_MWSTBetrag = 0) then begin
        oFinanzen.nZUG_MWSTBetrag := oFinanzen.nZUG_MwstBetrag1 + oFinanzen.nZUG_MwstBetrag2 + oFinanzen.nZUG_MwstBetrag3;
    end;

    CWPrintn(oCW, 'Importiere Rabatte und Zusatzkosten');

    // Rabatte und Zusatzkosten als Positionen erfassen
    nPos := 1;
    while (GetXMLToken(cXMLTradeSettlement, 'ram:SpecifiedTradeAllowanceCharge', nPos, cTemp1)) do begin

        // Höchste Positionsnummer ermitteln, damit Zusatzkosten mit
        // aufsteigender Pos-Nr erfasst werden können
        nPosNr := 0;
        
        //for i:=0 to TObjectList(TZugFerd(oFinanzen.oParent).oPositionen).Count do begin
        for i:=0 to TZugFerd(oFinanzen.oParent).nPosCnt()-1 do begin
           oPosTmp := TZugFerd(oFinanzen.oParent).GetPosition(i);
           cPosNr  := oPosTmp.cZUP_PosNr;      
           nPosNr  := Max(nPosNr, iVal(cPosNr));
           CWPrintn(oCW, 'MaxNr ' + cPosNr);
        end;
        
        oPos := TZugPosition.Create( TZugFerd(oFinanzen.oParent).cZUG_UID);
        oPos             := TZugPosition.Create();
        oPos.cZUP_UID    := TZugFerd(oFinanzen.oParent).cZUG_UID;        
        oPos.cZUP_PosNr  := TStr(nPosNr + 1, 0, 0);
        cText := '';

        if (GetXMLToken(cTemp1,'ram:ChargeIndicator',cTemp2)) then begin
            GetXMLToken(cTemp1,'udt:Indicator', cIndikator);
            lIndikator := lower(cIndikator) = 'true';
            if (lIndikator) then begin
                cText := cText + 'Zusatzkosten' + CRLF;
            end else begin
                cText := cText + 'Rabatt' + CRLF;
            end;
        end;

        GetXMLToken(cTemp1,'ram:Reason',oPos.cZUP_Bez1);

        if (GetXMLToken(cTemp1,'ram:BasisAmount',cTemp2)) then begin
            cText := cText + 'Grundlage: ' + cTemp2;
        end;

        if (GetXMLToken(cTemp1,'ram:ActualAmount',cTemp2)) then begin
            oPos.nZUP_ENetto := fVal(cTemp2);
            if (lIndikator) then begin // Rabatt
                oPos.nZUP_Menge := 1;
            end else begin
                oPos.nZUP_Menge := -1;
            end;
        end;

        if (GetXMLToken(cTemp1,'ram:CategoryTradeTax',cTemp2)) then begin
            if (GetXMLToken(cTemp2,'ram:RateApplicablePercent',cTemp3)) then begin
                opos.nZUP_MwstSatz := fVal(cTemp3);
            end;
        end;

        oPos.cZUP_Text := cText;
        oPos.nZUP_GNetto := oPos.nZUP_Menge * oPos.nZUP_ENetto;

         TZugFerd(oFinanzen.oParent).AddToPositionen(oPos);
         //TObjectList(TZugFerd(oFinanzen.oParent).oPositionen).Add(oPos);
    end;
end;

//------------------------------------------------------------------------------
//  Procedure: Startproc
//  Author:    JB
//  Date:      09-12-2021
//  Comment:   Startfunction 
//------------------------------------------------------------------------------

function  Startproc(oOBJ:TZugFerd; cXMLData:String):Boolean;
var cTemp1   : String;
    cTemp2   : String;
    cTemp3   : String;
    nPos     : Integer;
    oPos     : TZugPosition;
    lImport  : Boolean;
begin
   Result   := False;
   oZugFerd := oOBJ;

    oCW := CWOpen(20, 80, 'Importiere XML Zugferddaten');
    
    try
        if (GetXMLToken(cXMLData, 'ram:GuidelineSpecifiedDocumentContextParameter', cTemp1)) then begin
            GetXMLToken(cTemp1,'ram:ID', oZugFerd.cZUG_Schema);
        end;

        if (GetXMLToken(cXMLData, 'rsm:ExchangedDocument', cTemp1)) then begin
    
            nPos := 1;
          
            GetXMLTokenValue(cTemp1,'udt:DateTimeString', nPos, cTemp2, cTemp3);
            oZugFerd.dZUG_RDatum := SToD(cTemp2);

            nPos := 1;

            GetXMLToken(cTemp1,'ram:ID', oZugFerd.cZUG_Nr);
            GetXMLToken(cTemp1,'ram:TypeCode', oZugFerd.cZUG_TypCode);

            CWPrintn(oCW, 'Importiere Positionen Freitext');

            while (GetXMLToken(cTemp1, 'ram:IncludedNote', nPos, cTemp2)) do begin
                oPos := TZugPosition.Create(oZugFerd.cZUG_UID);

                oPos.cZUP_PosTyp := ZUG_POSTYP_MEMO;
                oPos.cZUP_Bez1   := 'Freitext';
                GetXMLToken(cTemp2, 'ram:Content', oPos.cZUP_Text);
                GetXMLToken(cTemp2, 'ram:SubjectCode', oPos.cZUP_Bez2);

                oZugFerd.AddToPositionen(oPos);
            end;
        end;

        if (GetXMLToken(cXMLData, 'rsm:SupplyChainTradeTransaction', cTemp1)) then begin

            lImport := False;

            if (GetXMLToken(cTemp1, 'ram:ApplicableHeaderTradeDelivery', cTemp2)) then begin
                CWPrintn(oCW, 'Importiere Lieferung');
                Lieferung_Import_XML(cTemp2);       
                lImport := True;
            end;

            if (GetXMLToken(cTemp1, 'ram:ApplicableHeaderTradeAgreement', cTemp2)) then begin
                CWPrintn(oCW, 'Importiere Personen');
                Personen_Import_XML(cTemp2);
                lImport := True;
            end;

            if (not lImport) then begin
                oZugFerd.AddProto('Kein Token ApplicableHeaderTradeDelivery vorhanden!');
                oZugFerd.AddProto('Kein Token ApplicableHeaderTradeAgreement vorhanden!');
                Result := False;
            end;

            nPos := 1;

            while (GetXMLToken(cTemp1, 'ram:IncludedSupplyChainTradeLineItem', nPos, cTemp2)) do begin
                oPos := TZugPosition.Create(oZugFerd.cZUG_UID);
                Positionen_Import_XML(oPos, cTemp2);
            
                oZugFerd.AddToPositionen(oPos);
            end;

            // Beträge nach den Positionen, damit Rabatte und Zusatzkosten als Positionen am Ende erfasst werden
            if (GetXMLToken(cTemp1, 'ram:ApplicableHeaderTradeSettlement', cTemp2)) then begin
                Finanzen_Import_XML(oZugFerd.oFinanzen, cTemp2);
            end;

            Result := True;
        end;
    finally
        CWClose(oCW);
    end;
end;

//------------------------------------------------------------------------------

Export

Wenn es einen aktiven Eintrag in der Script Libary mit dem Namen EXPORT_ZUGFERD gibt wird die Erstellung der XML-Datei über die Pax Funktion ausgeführt.

Beispiel Script Export

//------------------------------------------------------------------------------
// Unit Name: EXPORT_ZUGFERD
// Author:    JB
// Date:      09-12-2021
//------------------------------------------------------------------------------

var oXML         : TXMLWriter;
    cRechnungsNr : String;
    qRech        : TxFQuery;
    qKonst       : TxFQuery;
    qStamm       : TxFQuery;
    qPerson      : TxFQuery;
    dLiefDat     : TDateTime;
    nMWST        : Double;
    nMwstTotal   : Double;
    oFormat      : TFormatSettings;
        
//------------------------------------------------------------------------------
//  Procedure: _Format
//  Author:    JB
//  Date:      09-12-2021
//  Comment:   Zahlenausgabe für XML 
//------------------------------------------------------------------------------    
    
function _Format(nVal : Double): String;
 begin
     Result := ZugFerd_FormatDouble(nVal);
 end;

//------------------------------------------------------------------------------
//  Procedure: OBSRechnungsart_To_ZUGInvoiceTypeCode(
//  Author:    JB
//  Date:      09-12-2021
//  Comment:    
//------------------------------------------------------------------------------ 

function OBSRechnungsart_To_ZUGInvoiceTypeCode(const cOBSRechArt : String): String;
begin
    Result := '';
    if          (cOBSRechArt = '1') then begin // Standardrechnung
        Result := ZUG_RECHTYP_COMMERCIAL;
    end else if (cOBSRechArt = '2') then begin // Abschlagsrechnung
        Result := ZUG_RECHTYP_ABSCHLAG;
    end else if (cOBSRechArt = '3') then begin // Schlussrechnung
        Result := ZUG_RECHTYP_COMMERCIAL;
    end else if (cOBSRechArt = '4') then begin // Proformarechnung
        Result := ZUG_RECHTYP_PROFORMA;
    end else if (cOBSRechArt = '5') then begin // Gutschrift
        Result := ZUG_RECHTYP_GUTSCHRIFT;
    end else if (cOBSRechArt = '6') then begin // Stornorechnung
        Result := ZUG_RECHTYP_GUTSCHRIFT;
    end else if (cOBSRechArt = '7') then begin // Anzahlungsrechnung
        Result := ZUG_RECHTYP_PARTIAL;
    end;
end;

//------------------------------------------------------------------------------
//  Procedure: OBSMwstSchl_To_ZUGCategoryCode((
//  Author:    JB
//  Date:      09-12-2021
//  Comment:    
//------------------------------------------------------------------------------ 

function  OBSMwstSchl_To_ZUGCategoryCode(const cOBSMWST : String): String;
var nMwst : Integer;
begin
    nMWST := iVal(cOBSMWST);

    if (nMWST < 10) then begin
        Result := ZUG_MWSTTYP_STANDARD;
    end;
end;

//------------------------------------------------------------------------------
//  Procedure: GetVersandTyp(
//  Author:    JB
//  Date:      09-12-2021
//  Comment:    
//------------------------------------------------------------------------------ 

function GetVersandTyp(oMyDB: TxDB; cPerson: String): Integer;
begin
    Result := iVal(DB_ReadSQLValue(oMyDB,'ZUGFERD_PERSONEN','zp_versand','zp_psnr = '+DB_SQLVal(cPerson)));
end;

//------------------------------------------------------------------------------
//  Procedure: ZugFerd_Positionen
//  Author:    JB
//  Date:      09-12-2021
//  Comment:    
//------------------------------------------------------------------------------ 

procedure ZugFerd_Positionen(var oXML : TXMLWriter);
 var cSQL : String;
     qPos : TxFQuery;
 begin

     cSQL := 'SELECT * FROM RECHPOS'+
             ' LEFT JOIN s_mwst ON az_mwstsch = mw_nr' +
             ' WHERE az_nr = ' + DB_SQLVal(cRechnungsNr) +
             ' AND az_typ = 0';

     if (DB_SOpen(oDB, cSQL, qPos)) then begin

         dLiefDat := CToD(DB_ReadSQLValue(oDB,'LIEFSTA LEFT JOIN LIEFPOS ON a_nr = az_nr','a_datum','az_aufnr = '+DB_SQLVal(qPos.A2C('az_aufnr'))));
         if (empty(dLiefDat)) then begin
             dLiefDat := qRech.A2D('a_facdatum');
             if (empty(dLiefdat)) then begin
                 dLiefDat := qRech.A2D('a_datum');
             end;
         end;

         while not qPos.eof do begin
             with oXML do begin
                 OpenElement('ram:IncludedSupplyChainTradeLineItem');
                     OpenElement('ram:AssociatedDocumentLineDocument');
                         AddElement('ram:LineID', qPos.A2C('az_posnr')); // Positionsnummer // BR-21
                     CloseElement; // ram:AssociatedDocumentLineDocument

                     OpenElement('ram:SpecifiedTradeProduct');
                         AddElement('ram:SellerAssignedID', qPos.A2C('az_artnr')); //BT-155 Artikelnummer des Verkäufers
                         AddElement('ram:Name', qPos.A2C('az_bez1')); // Positionsname // BR-25
                     CloseElement; // ram:SpecifiedTradeProduct

                     OpenElement('ram:SpecifiedLineTradeAgreement');
                         OpenElement('ram:NetPriceProductTradePrice');
                             AddElement('ram:ChargeAmount', _Format(qPos.A2F('az_epreis'))); // Netto Einzelpreis // BR-26, BR-27
                         CloseElement; // ram:NetPriceProductTradePrice
                     CloseElement; // ram:SpecifiedLineTradeAgreement

                     OpenElement('ram:SpecifiedLineTradeDelivery');
                         AddAttrElement('ram:BilledQuantity', 'unitCode', 'H87', _Format(qPos.A2F('az_menge'))); // Einheit und Menge // BR-22, BR-23
                     CloseElement; // ram:SpecifiedLineTradeDelivery

                     OpenElement('ram:SpecifiedLineTradeSettlement');
                         OpenElement('ram:ApplicableTradeTax');
                             OpenElement('ram:TypeCode');
                                 SetContent('VAT'); // Steuerart, im Standard immer VAT
                             CloseElement; // ram:TypeCode
                             OpenElement('ram:CategoryCode');
                                 // Andere: (100 - 199)
                                 // AE:     $13b Reverse Charge (200 - 299)
                                 // K:      Innereuropäisch (400 - 499)
                                 SetContent('S');
                             CloseElement; // ram:CategoryCode
                             OpenElement('ram:RateApplicablePercent');
                                 SetContent(_Format(qPos.A2F('mw_satz')));
                             CloseElement; // ram:RateApplicablePercent
                         CloseElement; // ram:ApplicableTradeTax

                         if (qPos.A2F('az_rabatt') > 0) or (qPos.A2F('az_grabatt') > 0) then begin
                         OpenElement('ram:SpecifiedTradeAllowanceCharge');
                             OpenElement('ram:ChargeIndicator');
                                 AddElement('udt:Indicator','false'); // false = Abschlag, true = Zuschlag
                             CloseElement; // ram:ChargeIndicator

                             if (qPos.A2F('az_rabatt') > 0) then begin
                                 AddElement('ram:CalculationPercent', qPos.A2C('az_rabatt'));
                             end;

                             AddElement('ram:BasisAmount', _Format(qPos.A2F('az_menge')*qPos.A2F('az_epreis')));

                             if (qPos.A2F('az_rabatt') > 0) then begin // BR-41
                                 AddElement('ram:ActualAmount', _Format(qPos.A2F('az_menge')*qPos.A2F('az_epreis')*(qPos.A2F('az_rabatt')/100)));
                             end else if (qPos.A2F('az_grabatt') > 0) then begin
                                 AddElement('ram:ActualAmount', _Format(qPos.A2F('az_grabatt')));
                             end;
                             AddElement('ram:ReasonCode', '95');
                             AddElement('ram:Reason', 'Discount'); // BR-42
                         CloseElement; // ram:SpecifiedTradeAllowanceCharge
                         end;

                         OpenElement('ram:SpecifiedTradeSettlementLineMonetarySummation');
                             AddElement('ram:LineTotalAmount', _Format(qPos.A2F('az_gpreis'))); // Netto Gesampreis // BR-24
                         CloseElement; // ram:SpecifiedTradeSettlementLineMonetarySummation
                     CloseElement; // ram:SpecifiedLineTradeSettlement
                 CloseElement; // ram:IncludedSupplyChainTradeLineItem
             end;
             qPos.next();
         end;
     end;
     DB_Close(qPos);
 end;

//------------------------------------------------------------------------------
//  Procedure: StartProc
//  Author:    JB
//  Date:      09-12-2021
//  Comment:   Start Procwedure 
//------------------------------------------------------------------------------ 

function StartProc(xoXML         : TXMLWriter;
                   xcRechnungsNr : String;
                   xqRech        : TxFQuery;
                   xqKonst       : TxFQuery;
                   xqStamm       : TxFQuery;
                   xqPerson     : TxFQuery):Boolean;
begin
    Result        := True;
    oXML          := xoXML;                
    cRechnungsNr  := xcRechnungsNr;
    qRech         := xqRech;       
    qKonst        := xqKonst;      
    qStamm        := xqStamm;      
    qPerson       := xqPerson;     

    oFormat.DecimalSeparator := '.';
    oFormat.CurrencyDecimals := 2;
    
    oXML.OpenElement('rsm:CrossIndustryInvoice');
        oXML.SetAttribute('xmlns:a',   'urn:un:unece:uncefact:data:standard:QualifiedDataType:100');
        oXML.SetAttribute('xmlns:rsm', 'urn:un:unece:uncefact:data:standard:CrossIndustryInvoice:100');
        oXML.SetAttribute('xmlns:qdt', 'urn:un:unece:uncefact:data:standard:QualifiedDataType:10');
        oXML.SetAttribute('xmlns:ram', 'urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:100');
        oXML.SetAttribute('xmlns:xs',  'http://www.w3.org/2001/XMLSchema');
        oXML.SetAttribute('xmlns:udt', 'urn:un:unece:uncefact:data:standard:UnqualifiedDataType:100');
        oXML.OpenElement('rsm:ExchangedDocumentContext');
            oXML.OpenElement('ram:GuidelineSpecifiedDocumentContextParameter');
                oXML.AddElement('ram:ID', ZUG_COMPLIANCE_XRECHNUNG_2_0); // BR-1
            oXML.CloseElement; // ram:GuidelineSpecifiedDocumentContextParameter
        oXML.CloseElement; // rsm:ExchangedDocumentContext

        oXML.OpenElement('rsm:ExchangedDocument');
            oXML.AddElement('ram:ID', qRech.A2C('a_nr')); // Rechnungsnummer  // BR-2
            oXML.AddElement('ram:TypeCode', OBSRechnungsart_To_ZUGInvoiceTypeCode(qRech.A2C('a_art'))); // Rechnungstyp // BR-4
            oXML.OpenElement('ram:IssueDateTime');
                oXML.AddAttrElement('udt:DateTimeString','format', '102', DToS(qRech.A2D('a_datum'))); // Datum // BR-3
            oXML.CloseElement; // ram:IssueDateTime
            
            //Wenn Postgut
            //oXML.OpenElement('ram:IncludedNote');
            //    oXML.AddElement('ram:Content',qRech.A2C('a_aufnrku'));
            //    oXML.AddElement('ram:SubjectCode','INV');
            //oXML.CloseElement; //ram:IncludedNote
            
        oXML.CloseElement; // rsm:ExchangedDocument

        oXML.OpenElement('rsm:SupplyChainTradeTransaction');
        
            ZugFerd_positionen(oXML); // ram:IncludedSupplyChainTradeLineItem

            oXML.OpenElement('ram:ApplicableHeaderTradeAgreement');
                oXML.AddElement('ram:BuyerReference',qPerson.A2C('zp_leitwegid')); // Leitweg-ID für XRechnung

                oXML.OpenElement('ram:SellerTradeParty');
                    oXML.AddElement('ram:ID', qPerson.A2C('zp_kreditorennr')); // BT-29: Kreditorennummer beim Kunden
                    oXML.AddElement('ram:Name', qKonst.A2C('k_name')); // BR-6

                    oXML.OpenElement('ram:DefinedTradeContact'); // BG-6, BR-DE-5, BR-DE-6, BR-DE-7
                        oXML.AddElement('ram:PersonName', qPerson.A2C('zp_apname')); // BT-41 Name

                        oXML.OpenElement('ram:TelephoneUniversalCommunication'); // BT-42 Telefon
                            oXML.AddElement('ram:CompleteNumber', qPerson.A2C('zp_aptel'));
                        oXML.CloseElement();  // ram:TelephoneUniversalCommunication

                        oXML.OpenElement('ram:EmailURIUniversalCommunication'); // BT-43 E-Mail
                            oXML.AddElement('ram:URIID', qPerson.A2C('zp_apmail'));
                        oXML.CloseElement();  // ram:EmailURIUniversalCommunication
                    oXML.CloseElement();  // ram:DefinedTradeContact

                    oXML.OpenElement('ram:PostalTradeAddress'); // BR-8
                        oXML.AddElement('ram:PostcodeCode', qKonst.A2C('k_plz'));
                        oXML.AddElement('ram:LineOne', qKonst.A2C('k_strasse'));
                        oXML.AddElement('ram:CityName', qKonst.A2C('k_ort'));
                        oXML.AddElement('ram:CountryID', 'DE'); // BR-9
                    oXML.CloseElement; // ram:PostalTradeAddress

                    oXML.OpenElement('ram:SpecifiedTaxRegistration');
                        oXML.AddAttrElement('ram:ID', 'schemeID', 'VA', qKonst.A2C('k_ustid')); //
                    oXML.CloseElement; // ram:SpecifiedTaxRegistration
                oXML.CloseElement; // ram:SellerTradeParty

                oXML.OpenElement('ram:BuyerTradeParty');
                    oXML.AddElement('ram:Name',qRech.A2C('a_name')); // BR-7
                    oXML.OpenElement('ram:PostalTradeAddress'); // BR-10
                        oXML.AddElement('ram:PostcodeCode', qRech.A2C('a_plz'));
                        oXML.AddElement('ram:LineOne', qRech.A2C('a_strasse'));
                        oXML.AddElement('ram:CityName', qRech.A2C('a_ort'));
                        oXML.AddElement('ram:CountryID', 'DE'); // BR-11
                    oXML.CloseElement; // ram:PostalTradeAddress
                oXML.CloseElement; // ram:BuyerTradeParty

                oXML.OpenElement('ram:BuyerOrderReferencedDocument');
                    oXML.AddElement('ram:IssuerAssignedID',qRech.A2C('a_aufnrku'));
                oXML.CloseElement(); //ram:BuyerOrderReferencedDocument

            oXML.CloseElement; // ram:ApplicableHeaderTradeAgreement

            oXML.OpenElement('ram:ApplicableHeaderTradeDelivery'); // Tag muss vorhanden sein

                if (not empty(qRech.A2C('a_vstrasse'))) then begin
                    oXML.OpenElement('ram:ShipToTradeParty'); // Abweichender Warenempfänger
                        oXML.OpenElement('ram:PostalTradeAddress'); // BR-57
                            oXML.AddElement('ram:PostcodeCode', qRech.A2C('a_vplz'));
                            oXML.AddElement('ram:LineOne', qRech.A2C('a_vstrasse'));
                            oXML.AddElement('ram:CityName', qRech.A2C('a_vort'));
                            oXML.AddElement('ram:CountryID', 'DE'); // BR-11
                        oXML.CloseElement; // ram:PostalTradeAddress
                    oXML.CloseElement;
                end;

                oXML.OpenElement('ram:ActualDeliverySupplyChainEvent');
                    oXML.OpenElement('ram:OccurrenceDateTime');
                        oXML.AddAttrElement('udt:DateTimeString','format', '102', DToS(dLiefDat)); // Datum // BT-72
                    oXML.CloseElement;
                oXML.CloseElement;

            oXML.CloseElement;

            oXML.OpenElement('ram:ApplicableHeaderTradeSettlement');
                oXML.AddElement('ram:InvoiceCurrencyCode', 'EUR'); // Währungscode nach ISO 4217 // BR-5

                // BR-49, BR-50, BR-51
                if (not empty(qStamm.A2C('zus_iban1'))) then begin
                    oXML.OpenElement('ram:SpecifiedTradeSettlementPaymentMeans');
                        oXML.AddElement('ram:TypeCode', '30'); // 30 = Überweisung // BR-49
                        oXML.AddElement('ram:Information', qStamm.A2C('zus_bank1'));
                        oXML.OpenElement('ram:PayeePartyCreditorFinancialAccount');
                            oXML.AddElement('ram:IBANID', qStamm.A2C('zus_iban1'));
                        oXML.CloseElement; // ram:PayeePartyCreditorFinancialAccount
                    oXML.CloseElement; // ram:SpecifiedTradeSettlementPaymentMeans
                end;
                if (not empty(qStamm.A2C('zus_iban2'))) then begin
                oXML.OpenElement('ram:SpecifiedTradeSettlementPaymentMeans');
                    oXML.AddElement('ram:TypeCode', '30'); // 30 = Überweisung // BR-49
                    oXML.AddElement('ram:Information', qStamm.A2C('zus_bank2'));
                    oXML.OpenElement('ram:PayeePartyCreditorFinancialAccount');
                        oXML.AddElement('ram:IBANID', qStamm.A2C('zus_iban2'));
                    oXML.CloseElement; // ram:PayeePartyCreditorFinancialAccount
                oXML.CloseElement; // ram:SpecifiedTradeSettlementPaymentMeans
                end;
                if (not empty(qStamm.A2C('zus_iban3'))) then begin
                oXML.OpenElement('ram:SpecifiedTradeSettlementPaymentMeans');
                    oXML.AddElement('ram:TypeCode', '30'); // 30 = Überweisung // BR-49
                    oXML.AddElement('ram:Information', qStamm.A2C('zus_bank3'));
                    oXML.OpenElement('ram:PayeePartyCreditorFinancialAccount');
                        oXML.AddElement('ram:IBANID', qStamm.A2C('zus_iban3'));
                    oXML.CloseElement; // ram:PayeePartyCreditorFinancialAccount
                oXML.CloseElement; // ram:SpecifiedTradeSettlementPaymentMeans
                end;
                if (not empty(qStamm.A2C('zus_iban4'))) then begin
                oXML.OpenElement('ram:SpecifiedTradeSettlementPaymentMeans');
                    oXML.AddElement('ram:TypeCode', '30'); // 30 = Überweisung // BR-49
                    oXML.AddElement('ram:Information', qStamm.A2C('zus_bank4'));
                    oXML.OpenElement('ram:PayeePartyCreditorFinancialAccount');
                        oXML.AddElement('ram:IBANID', qStamm.A2C('zus_iban4'));
                    oXML.CloseElement; // ram:PayeePartyCreditorFinancialAccount
                oXML.CloseElement; // ram:SpecifiedTradeSettlementPaymentMeans
                end;
                if (not empty(qStamm.A2C('zus_iban5'))) then begin
                oXML.OpenElement('ram:SpecifiedTradeSettlementPaymentMeans');
                    oXML.AddElement('ram:TypeCode', '30'); // 30 = Überweisung // BR-49
                    oXML.AddElement('ram:Information', qStamm.A2C('zus_bank5'));
                    oXML.OpenElement('ram:PayeePartyCreditorFinancialAccount');
                        oXML.AddElement('ram:IBANID', qStamm.A2C('zus_iban5'));
                    oXML.CloseElement; // ram:PayeePartyCreditorFinancialAccount
                oXML.CloseElement; // ram:SpecifiedTradeSettlementPaymentMeans
                end;

                nMWSTTotal := 0;
                if (not empty(qRech.A2C('a_mwstschl1'))) then begin
                oXML.OpenElement('ram:ApplicableTradeTax');
                    nMWST := qRech.A2F('a_net1') * (qRech.A2F('a_mwst1') / 100);
                    nMWSTTotal := nMWSTTotal + nMWST;
                    oXML.AddElement('ram:CalculatedAmount',_Format(nMWST)); // BR-46
                    oXML.AddElement('ram:TypeCode', 'VAT');
                    oXML.AddElement('ram:BasisAmount', _Format(qRech.A2F('a_net1'))); // BR-45
                    oXML.AddElement('ram:CategoryCode', OBSMwstSchl_To_ZUGCategoryCode(qRech.A2C('a_art'))); // BR-47
                    oXML.AddElement('ram:RateApplicablePercent', _Format(qRech.A2F('a_mwst1'))); // BR-48
                oXML.CloseElement; // ram:ApplicableTradeTax
                end;
                if (not empty(qRech.A2C('a_mwstschl2'))) then begin
                oXML.OpenElement('ram:ApplicableTradeTax');
                    nMWST := qRech.A2F('a_net2') * (qRech.A2F('a_mwst2') / 100);
                    nMWSTTotal := nMWSTTotal + nMWST;
                    oXML.AddElement('ram:CalculatedAmount',_Format(nMWST)); // BR-46
                    oXML.AddElement('ram:TypeCode', 'VAT');
                    oXML.AddElement('ram:BasisAmount', _Format(qRech.A2F('a_net2'))); // BR-45
                    oXML.AddElement('ram:CategoryCode', OBSMwstSchl_To_ZUGCategoryCode(qRech.A2C('a_art'))); // BR-47
                    oXML.AddElement('ram:RateApplicablePercent', _Format(qRech.A2F('a_mwst2'))); // BR-48
                oXML.CloseElement; // ram:ApplicableTradeTax
                end;
                if (not empty(qRech.A2C('a_mwstschl3'))) then begin
                oXML.OpenElement('ram:ApplicableTradeTax');
                    nMWST := qRech.A2F('a_net3') * (qRech.A2F('a_mwst3') / 100);
                    nMWSTTotal := nMWSTTotal + nMWST;
                    oXML.AddElement('ram:CalculatedAmount',_Format(nMWST)); // BR-46
                    oXML.AddElement('ram:TypeCode', 'VAT');
                    oXML.AddElement('ram:BasisAmount', _Format(qRech.A2F('a_net3'))); // BR-45
                    oXML.AddElement('ram:CategoryCode', OBSMwstSchl_To_ZUGCategoryCode(qRech.A2C('a_art'))); // BR-47
                    oXML.AddElement('ram:RateApplicablePercent', _Format(qRech.A2F('a_mwst3'))); // BR-48
                oXML.CloseElement; // ram:ApplicableTradeTax
                end;

                oXML.OpenElement('ram:SpecifiedTradePaymentTerms');
                    // Fälligkeitsdatum
                    oXML.OpenElement('ram:DueDateDateTime');
                        oXML.AddAttrElement('udt:DateTimeString','format', '102', DToS(qRech.A2D('a_faellig'))); // Datum // BT-9, BR-CO-25
                    oXML.CloseElement; // ram:DueDateDateTime
                oXML.CloseElement; // ram:SpecifiedTradePaymentTerms

                oXML.OpenElement('ram:SpecifiedTradeSettlementHeaderMonetarySummation');
                    oXML.AddElement('ram:LineTotalAmount', _Format(qRech.A2F('a_nbetrag'))); // BR-12
                    oXML.AddElement('ram:TaxBasisTotalAmount', _Format(qRech.A2F('a_nbetrag'))); // BR-13
                    oXML.AddAttrElement('ram:TaxTotalAmount','currencyID','EUR',_Format(nMWSTTotal)); // BR-53
                    oXML.AddElement('ram:GrandTotalAmount', _Format(qRech.A2F('a_bbetrag'))); // BR-14
                    oXML.AddElement('ram:DuePayableAmount', _Format(qRech.A2F('a_bbetrag'))); // BR-15
                oXML.CloseElement(); // ram:SpecifiedTradeSettlementHeaderMonetarySummation

            oXML.CloseElement; // ram:ApplicableHeaderTradeSettlement

        oXML.CloseElement; // rsm:SupplyChainTradeTransaction

    oXML.CloseElement; // rsm:CrossIndustryInvoice
    
end;

OBS/Makros und Scripting/Anwendungsbereiche/ZUGFERD: Unterschied zwischen den Versionen

Version vom 10. Dezember 2021, 15:43 Uhr

Inhaltsverzeichnis

ZUGFERD

Import

Felder/Strukturen

Beispiel Script Import

Export

Beispiel Script Export

Navigationsmenü

OBS/Makros und Scripting/Anwendungsbereiche/ZUGFERD: Unterschied zwischen den Versionen

Version vom 10. Dezember 2021, 15:43 Uhr

ZUGFERD

Import

Felder/Strukturen

Beispiel Script Import

Export

Beispiel Script Export

Navigationsmenü

Suche