By using this website, you agree to our Terms of Use (click here)
Good day all.
My client has a need for 96 character flat file output of check info to upload to Positive Pay system at bank.
Very disappointed on Acumatica not having this native, but it still needs to be done.
If I use Report Designer, and can format the output correctly,. Am I even going to be able to get it to a text file?
Thoughts or suggestions?
Hi Happ,
I haven't seen a text file output option on the Report Designer, but the Data Providers for Export Scenarios allow for csv and flat files. I've been working on a flat file to provide an Inventory Feed with specific file formatting requirements.
What I'm doing is using an Export Scenario on a scheduled Export to prepare and export the results from a Generic Inquiry into a flat file with the required formatting. Shortly thereafter another automated process syncs the flat file from Acumatica's filestorage to a shared network folder. (Acumatica is in an On-Premise installation).
It would be possible for you to setup something similar if the Generic Inquiry displays all the checks for the day (assuming the Client's bank supports multi-check batches in the file), and the Export Scenario can be exposed on the Site Map via the Details tab on the Export by Scenario process screen for the client to skip the automation and prepare the file manually. The Scenario could still be automated to run at the end of the business day and the file in Acumatica would retain the daily revisions from each export if a historical archive is needed.
Is it possible to use parameters in export solution? Like date (s) or range.
I'm not sure how to even get started with export solution. Could you point me in direction of documentation?
Thanks for your response.
Hi HappGilmore,
Do you have a text file in the required output format that we could use to match to? I think that Casval has a great suggestion to use an Export Scenario. I haven't done much with Export Scenarios so I'm not sure if they take parameters, but I could look into it. Or maybe Casval knows off the top of their head.
Output sample:
0000091074200000921826022417000XXXXXXXXXXXXX COMPANY T
0000091075200000045823022417000XXXXX T
0000091076200000243600022417000XXXXXXXXFARM MANAGEMENT T
0000091077200000116085022417000AUNT XXXXXXXXXXXXXXXXXXXX T
0000091078200000228185022417000XXXXXXXXXXXXXXXXX, INC. T
0000091079200002079973022417000XXXXXXXXXX FARM T
0000091080200000063465022417000XXXXXXXXXXXX REFRESHMENTS, INC. T
X - REPRESENT PAYEE COMPANY NAME ON CHECK.
position 1, 10 characters = check number, zero left filled
position 11, 1 character = 1 digit fill (using '2' from example)
Position 12, 11 characters = check amount, no comma, no decimal. '00000045823' reads 458.23 on check
position 23, 6 characters = check Date format MMddyy
Position 29, 3 characters = fill, currently using '000'
position 32, up to 96 characters = Payee name. (T at the end of name is no longer relevant).
the file, does not need a header record, nor footer record - thankfully.
My example is the only Export Scenario I've worked on, so I'm not an expert either. Skimming through the latest "I100 Integration Scenarios" course from the Acumatica Partner/Open University. Part 1 includes the configuration of the csv data provider, Part 4 covers export scenarios, and Part 6 covers file synchronization scheduling.
Example 4.1.2 covers exporting AR Invoices (which can easily be modified to AP Payments), it doesn't look like it allows parameters being passed by the user running the export, but does allow source restrictions in the configuration of the Export Scenario and shows how to update the linked Note record for the AP Payments record being exported indicating it has been exported. So with this example, I'd recommend using the source restrictions to check for payment method which requires Positive Pay, payments not already exported, and if the client is regularly using the Acumatica Reconciliation Statements to "Clear" the transactions in cash accounts you can further restrict it to payments that have "False" set for the Cleared field. To be cleaner if I were to go this route instead of using the built in Notes, I would add a field for "Positive Pay Exported" as a boolean to the APPayment DAC (APPayment.UsrPPExported) in a customization and change that to true when exported, this field should default to "false" on new records and be visible but disabled in the GUI to prevent manual changes by users.
Example 4.1.4 covers exporting a GI, similar to what I've been working on, It does show how to pass parameters to the GI that are hardcoded into the Export Scenario configuration but they can't be changed by the user running the Export. This does not allow a change to the APPayment record as does example 4.1.2.
Example 6.1.2 covers the scheduling that I'm doing, so if the export is scheduled at say 11:59pm client local time, this would create a daily archive for payments in either example so the date range would be in the files exported instead of the parameter for the export. Each time the schedule runs it would create a "revision" for that day's payments in the file stored in Acumatica via Document Management. By exposing the File Maintenance screen for that file via the Site Map, any user with view or higher permissions to that screen can download the file revision generated on any given day. Exposing the Export by Scenario in the Sitemap to allow the user to manually run the export would just add multiple revisions to the file for that day, so an Archive for all payments would still be available with an automated Export enabled.
Hi Casval,
Any reason you can think of not to use the feature where you can go to the Payment Methods screen and select the Create Batch Payments option to tie it to an Export Scenario like this:
Then you go to the Batch Payments screen and click the EXPORT button to create a CSV file that attaches to the payment record itself:
What do you think about this method?
My initial assumption is the export scenario is INSTEAD of printing a check. The Fedwire option is to process ACH payment. I need to print physical checks then send record of check to bank BEFORE the checks start to clear.
I'm continuing down path of report writer, will export to excel then save as text. Or then try to set as odata direct to word.
Thanks for still thinking about it.
Ha! Silly me, wasn't thinking it all the way through.
I assume that you've looked at the 3rd party add-ons from SWK and PC Bennett?
The Fedwire Export Scenario is indeed a replacement for printed checks.
An Export Scenario of a GI would not prevent/replace printing checks. Additionally, GI's have the same formula builder capabilities of the Report Designer. It would be a rather complex string concatenation formula but completely workable to display the multiple data fields into 1 column in a GI with the required output you described.