Skip to main content
Solved

Creating an XML Data Provider

There is a lot of documentation and training around non-XML data providers but not very much around XML data providers.  The documentation and examples don’t feel very clear around what can be done before custom development is required.

To create this XML file going out, can I use the existing XML data provider or will I have to create my own?  (There are more elements but this example shows the deepest elements and entities.)

<?xml version="1.0" standalone="no"?>
<File PmtRecCount="7" PmtRecTotal="7000.00">
  <PmtRec PmtCrDr="C" PmtMethod="CHK">
    <Message MsgType="CHK">
       <MsgText>Check Memo~35</MsgText>
    </Message>
    <Check>
      <ChkNum>1234567890</ChkNum>
      <ChkDocNum>CR9999CL9999WS9999</ChkDocNum>
      <DeliveryCode>100</DeliveryCode>
    </Check>
    <OrgnrParty>
      <Name>
        <Name1>ABC Company~60</Name1>
      </Name>
      <PostAddr>
        <Addr1>501 N Main Street~55</Addr1>
        <Addr2>Suite 100~55</Addr2>
        <Addr3>1st Floor</Addr3>
        <City>Phoenix~30</City>
        <StateProv>AZ</StateProv>
        <PostalCode>85003</PostalCode>
        <Country>US</Country>
      </PostAddr>
    </OrgnrParty>
    <PmtDetail>
      <InvoiceInfo InvoiceType="IV" InvoiceNum="INV001" EffDt="2021-04-15" NetCurAmt="1000.00" TotalCurAmt="1050.00" DiscountCurAmt="50.00">
        <Note NoteType="INV">
          <NoteText>Invoice Description</NoteText>
        </Note>
        <POInfo POType="PO">
          <PONum>PO Number</PONum>
        </POInfo>
      </InvoiceInfo>
    </PmtDetail>
    <PmtID>1234567890</PmtID>
    <CurAmt>1000.00</CurAmt>
    <CurCode>USD</CurCode>
    <ValueDate>2021-04-15</ValueDate>
  </PmtRec>
  <FileInfoGrp FileDate="2021-04-15" FileControlNumber="9999999999"/>
</File>

 

15 replies

Gabriel Michaud
Rank
Userlevel 7
Badge +10

The existing XML Data provider can’t be used for arbitrary file formats. My suggestion is to create your own data provider. This article is a good starting point: https://asiablog.acumatica.com/2016/09/custom-integration-services-data.html.

If you have a GitHub account please send me your GitHub username and I will invite you to a private repository that includes a data provider that I wrote (for the ISO 20022 payment format -- XML based).

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
D
Rank
Userlevel 7
Badge +5

@Gabriel Michaud - thank you!  From your example and then going back to the blog post you supplied I was able to move forward.

In the end, because I had an option to I decided on a flat file format over XML. I used the code from the blog post and commented out the code that is writing the column headers. I’m using a custom table that essentially holds a bunch of rows of text. I’ll add an action button to the Batch Payments screen to run my code that will build the flat file in the custom table and then I’ll run the export scenario and allow the built-in synchronization logic to work.

There are probably a few steps I could remove from the process to be more efficient but this will work and let me do some debugging before the file is physically generated.

Do you know if the code can be placed into a customization project or will I have to compile the code into a DLL?  I’ve been looking to see if I can get a file into the App_Code folder via a Customization Project but without success.

Gabriel Michaud
Rank
Userlevel 7
Badge +10

@ddunn the code I shared with you is actually designed to be used for Batch Payments -- you don’t need to add a custom action, you can pick an Export Scenario in the payment method and the code will invoke your data provider. The code also deals with attaching the exported file to the Batch Payment.

You could place this directly into a customization project (Acumatica will add to App_RunTime code when you publish, and not App_Code), but I personally prefer working with class libraries for more complex projects like this one.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
D
Rank
Userlevel 7
Badge +5

@Gabriel Michaud - correct! I am adding another step in my process. Part of that is my journey into the Acumatica world and into C# as well so I’m breaking things up into pieces before combining into more concise (and arguably, correct) code.

I tried dropping the .cs file into App_Code and the new provider was available.  So I removed the .cs file and added it as a Code Item and it compiled but it doesn’t show up in the list of providers types. Since the site is hosted I won’t have access to App_Code directly so I was hoping to get it into a customization project without a DLL but maybe a DLL is my only option. (Or maybe a support case to have support add the file to that folder manually.)

D
Rank
Userlevel 7
Badge +5

Okay - I’m getting further as I dig into the code.

In my case I’ve decided not to use the XML format and instead focusing on creating a text file with variable length records and about seven different record types (header, total, payment type, payor details, payee details, payment type details, invoices being paid).

I’m using GetSchemaObjects and GetSchema fields to return the list of possible fields instead of deriving that from an imported file. I’ll create ‘set’ of schema fields for each record type. Then, the information that can be derived from the Batch Payments screen will be available in the Export Scenario’s prepared data. Other data elements that are not directly available will have to be handled manually within the data provider’s Export method (meaning that I cannot pull the value from the prepared ES data).

Onwards and upwards.

D
Rank
Userlevel 7
Badge +5

Hi @Gabriel Michaud,

I’m at the point where I’m able to create some data and then I make a call to SetFile which, in turn, is calling PX.SM.UploadFileMaintenace.SaveFile.

If I manually run the scenario via Export by Scenario, a file is created.

However, if I run my Export Scenario via the Batch Payments Export action, no file is created. If I look at the Export by Scenario screen I see data so I know that I’m setting the parameter correctly and that the scenario is running. The row in the ES shows Processed = True.

Did you encounter a similar problem when you were generating your data provider?

Gabriel Michaud
Rank
Userlevel 7
Badge +10

@ddunn there’s additional logic in the file that I shared with you to attach this file to the Batch Payment -- check the SaveNotes message. NoteID must be one of the fields in your export scenario, and linked to the payment batch NoteID field.

        protected override PX.SM.FileInfo SetFile(byte[] bytes)
        {
            PX.SM.FileInfo fi = base.SetFile(bytes);
            if (_notes != null && _notes.Count > 0) SaveNotes((Guid)fi.UID, _notes);
            return fi;
        }

        protected virtual void SaveNotes(Guid fileId, IEnumerable<String> notes)
        {
            foreach (String note in notes)
            {
                if (!String.IsNullOrEmpty(note))
                {
                    using (var record = PXDatabase.SelectSingle<NoteDoc>(
                        new PXDataField("NoteID"),
                        new PXDataFieldValue("NoteID", PXDbType.UniqueIdentifier, Guid.Parse(note)),
                        new PXDataFieldValue("FileID", PXDbType.UniqueIdentifier, fileId)))
                    {

                        if (record == null)
                        {
                            PXDatabase.Insert<NoteDoc>(
                                new PXDataFieldAssign("NoteID", PXDbType.UniqueIdentifier, Guid.Parse(note)),
                                new PXDataFieldAssign("FileID", PXDbType.UniqueIdentifier, fileId));
                        }
                    }
                }
            }
        }

 

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
D
Rank
Userlevel 7
Badge +5

Hi @Gabriel Michaud - yay!  I’m generating a file!

In the end I scrapped what I had created from the developer blog and took your file for my template. When you mentioned linking the NoteID I went back to the ACH export and found an example of that mapping. I also suspect I had some issues with my parameters - they need to match what the Batch Payments screen is setting so that helped too.

Thanks for your help once again!

For others:

 

Gabriel Michaud
Rank
Userlevel 7
Badge +10

P.S. just discovered that there was an earlier thread with additional code -- if that can help anyone, it’s here: 

 

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
D
Rank

The existing XML Data provider can’t be used for arbitrary file formats. My suggestion is to create your own data provider. This article is a good starting point: https://asiablog.acumatica.com/2016/09/custom-integration-services-data.html.

If you have a GitHub account please send me your GitHub username and I will invite you to a private repository that includes a data provider that I wrote (for the ISO 20022 payment format -- XML based).

@Gabriel Michaud would you please invite me on the GitHub repository? I want to review it and learn how to do this right for xml providers.

My user is diperez

Gabriel Michaud
Rank
Userlevel 7
Badge +10

Happy New Year @DPerez - just invited you to the repo. Check out file PaymentFileProvider.cs

Please do not share the rest of the code :)

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
D
Rank

Happy New Year @DPerez - just invited you to the repo. Check out file PaymentFileProvider.cs

Please do not share the rest of the code :)

Thanks @Gabriel Michaud. Happy New Year for you too. I promise I won’t share any file :P.

J
Rank
Userlevel 3
Badge

@Gabriel Michaud I want to create my own XML Data Provider for import. Can you invite me to your private repository for XML provider? My github username is junmao-cbiz (jun.mao@cbiz.com).

Thanks.

N
Rank
Userlevel 2
Badge

@Gabriel Michaud would it be possible to invite me to your private XML Provider repository as well? I’ve been searching for references but my project is quite complex. My github username is nfpurnama if you would be willing that would be of great help.

Thank You!

Gabriel Michaud
Rank
Userlevel 7
Badge +10

@naufal58 send me your GitHub ID in private message

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica

Reply


Terms & ConditionsCookie settings