Solved

Creating an XML Data Provider

  • 16 April 2021
  • 8 replies
  • 56 views

Userlevel 1
Badge

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>

 

icon

Best answer by Gabriel Michaud 26 April 2021, 22:04

@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));
}
}
}
}
}

 

View original

8 replies

Userlevel 6
Badge +6

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).

Userlevel 1
Badge

@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.

Userlevel 6
Badge +6

@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.

Userlevel 1
Badge

@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.)

Userlevel 1
Badge

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.

Userlevel 1
Badge

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?

Userlevel 6
Badge +6

@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));
}
}
}
}
}

 

Userlevel 1
Badge

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:

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved