Can repetitive QAD processes be automated?
This article is a short recap of a presentation delivered at MWUG Conference back in 2010. It shares best practice how QAD user company can benefit from automating month-end reporting, postings, document printing, etc with the use of QAD Batch functionality.
We have been practicing these techniques for years and so, your company can do it too without buying any 3rd party software or paying consulting fees.
How does QAD batch processing works?
First we create a batch ID in 36.14.1
mgbcmt.p 2+ 36.14.1 Batch ID Maintenance 03/13/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Batch ID: night │ │ Permanent: Yes │ │ Priority: 0 │ └──────────────────────────────────────────────────────────────────────────────┘
Submit a report or job to that batch.
iclorp.p 2+ 3.6.6 Inventory Detail by Location 03/13/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Item Number: To: │ │ Site: ABC To: ABC │ │ Location: To: │ │ Output: LTA79 │ │ Batch ID: night │ └──────────────────────────────────────────────────────────────────────────────┘
Then create a shell script and parameter files like shown on the diagram below and schedule it to crontab to run at a specific time.
 |
How QAD Batch manages Report Parameters?
When we submit a report to the batch, QAD batch processor captures all the report parameters and stores them.
arcsrp05.p 1+ 27.18 AR Aging as of Effective Date 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Bill To: To: │ │ Customer Type: To: │ │ Reference: To: │ │ Salesperson: To: │ │ Account: To: │ │ Sub-Account: To: │ │ Cost Center: To: │ │ Entity: To: │ │ List Type: │ │Age by Date (Due,Eff,Inv): Due Show Customer PO: No │ │ Effective Date: 02/01/10 Show Payment Detail: No │ │ Summary/Detail: Detail Show Invoice Comments: No │ │ Currency: Show Master Comments: No │ │ Reporting Currency: Type: AR │ │ Language: │ │ Column Days: 30 [2]: 60 [3]: 90 Output: LTA79 │ │ Batch ID: night │ └──────────────────────────────────────────────────────────────────────────────┘ Report request queued for batch processing
You can browse parameters as well as some other job attributes by going to 36.14.13 menu.
mgbcdmt.p 1+ 36.14.3 Batch Request Detail Maintenance 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Domain: QP │ │ Batch ID: night │ │ Submit Date: 02/01/10 │ │ Submit Time: 22:17:22 │ │ Submitted By: mfg │ │ │ │ Program: arcsrp05.p 27.18 AR Aging as of Effective Date │ │ Run Date: │ │ Run Time: │ │ Parameter Data: "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" " │ │ " "Due" "02/01/10" "no" "" "" "no" "no" "no" "no" │ │ "AR" "" "30" "60" "90" │ │ │ │ │ │ Priority: 0 Run Status: │ │ Permanent: Yes │ │ Process: Yes │ │ Output: LTA79 │ └──────────────────────────────────────────────────────────────────────────────┘
How to run date sensitive reports in batch?
If we schedule a report, let’s say 27.18 AR Aging, to run overnight (today is Feb 01, 2010) and we want the report to run automatically every night as of the day it runs. What we will find out next days is that the report always runs as of Feb 01/2010, regardless of the day we run it, Feb 02, 03, 04, etc…
arcsrp05.p 1+ 27.18 AR Aging as of Effective Date 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Bill To: To: │ │ Customer Type: To: │ │ Reference: To: │ │ Salesperson: To: │ │ Account: To: │ │ Sub-Account: To: │ │ Cost Center: To: │ │ Entity: To: │ │ List Type: │ │Age by Date (Due,Eff,Inv): Due Show Customer PO: No │ │ Effective Date: 02/01/10 Show Payment Detail: No │ │ Summary/Detail: Detail Show Invoice Comments: No │ │ Currency: Show Master Comments: No │ │ Reporting Currency: Type: AR │ │ Language: │ │ Column Days: 30 [2]: 60 [3]: 90 Output: LTA79 │ │ Batch ID: night │ └──────────────────────────────────────────────────────────────────────────────┘
Why this happens? Because the date parameter 02/01/10 is captured by the system and remains unchanged. You can see it in 36.14.3 for the corresponding job request.
mgbcdmt.p 1+ 36.14.3 Batch Request Detail Maintenance 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Domain: QP │ │ Batch ID: night │ │ Submit Date: 02/01/10 │ │ Submit Time: 22:17:22 │ │ Submitted By: mfg │ │ │ │ Program: arcsrp05.p 27.18 AR Aging as of Effective Date │ │ Run Date: │ │ Run Time: │ │ Parameter Data: "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" " │ │ " "Due" "02/01/10" "no" "" "" "no" "no" "no" "no" │ │ "AR" "" "30" "60" "90" │ │ │ │ │ │ Priority: 0 Run Status: │ │ Permanent: Yes │ │ Process: Yes │ │ Output: LTA79 │ └──────────────────────────────────────────────────────────────────────────────┘
This screen shows the content of bcd_det record for the selected batch request. BCD_DET table stores batch job attributes, such as :
bcd_batch Batch ID x(8) bcd_parm Parameter Data x(200) bcd_exec Program x(8) bcd_dev Output x(8) bcd_priority Priority x(8) bcd_userid User ID x(8)
And some others… In order to run the date sensitive report correctly in batch, the following has to happen:
1) The date parameter should be changed in the bcd_parm field
2) New date value should relate to the current system date
3) The report batch job itself should run afterwords
How it can be done? We introduce a program called “Batch Date Parameter Manager”, which will be changing date parameters of the submitted jobs. (You can get a free copy of the program from 32Soft).
xxdtmgr.p 32soft 36.14.22 * Batch Date Param Manager 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Batch ID: night Submitted By: user │ │ Program Name: arcsrp05.p Submit Date: 02/28/10 │ │ Menu Number: 27.18 Submit Time: 22:17:22 │ │ Menu Label: AR Aging as of Effective Date │ │ Total Params: 31 │ │ Parameters: "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "Due" │ │ "02/01/10" "no" "" "" "no" "no" "no" "no" "AR" "" "30" " │ │ 60" "90" │ │ │ │ Modify Param #: 19 │ │ Set Parameter To: 0 0-Today; 1-Beg of GL Period; 2-End of GL Period; │ │ 3-Beg of Cur Week; 4-End of Cur Week; │ │ 5-Beg of Cur Year; 6-End of Cur Year │ │ 7-Beg of Prev GL Period; 8-End of Prev GL Period; │ │Add/Subtract Days: 0 │ │ Batch ID: night │ └──────────────────────────────────────────────────────────────────────────────┘
So, set date parameter # 19 to be changed to 0 (today’s system date) and add/subtract 0 days (from today’s system date). Batch ID should be the same as the report batch ID, i.e. “night”. And you hit Enter submitting this request to the batch processor. And so now it can be seen in 36.14.3 as well.
mgbcdmt.p 1+ 36.14.3 Batch Request Detail Maintenance 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Domain: QP │ │ Batch ID: night │ │ Submit Date: 02/28/10 │ │ Submit Time: 23:51:13 │ │ Submitted By: user │ │ │ │ Program: xxdtmgr.p 36.14.22 * Batch Date Manager │ │ Run Date: │ │ Run Time: │ │ Parameter Data: "night" "arcsrp05.p" "19" "0" "0" │ │ │ │ │ │ │ │ │ │ Priority: 0 Run Status: │ │ Permanent: Yes │ │ Process: Yes │ │ Output: │ └──────────────────────────────────────────────────────────────────────────────┘
The second step
The Report Batch Job 27.18 – should run after the date parameter is changed, right? So make sure the Date Manager job runs with higher priority, so simply change it to 9 or any number higher then priority of the report job itself.
mgbcdmt.p 1+ 36.14.3 Batch Request Detail Maintenance 02/28/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Domain: QP │ │ Batch ID: night │ │ Submit Date: 02/28/10 │ │ Submit Time: 23:51:13 │ │ Submitted By: user │ │ │ │ Program: xxdtmgr.p 36.14.22 * Batch Date Manager │ │ Run Date: │ │ Run Time: │ │ Parameter Data: "night" "arcsrp05.p" "19" "0" "0" │ │ │ │ │ │ │ │ │ │ Priority: 9 Run Status: │ │ Permanent: Yes │ │ Process: Yes │ │ Output: │ └──────────────────────────────────────────────────────────────────────────────┘
That’s it. Now this report will run every night with proper date parameter.
Many reports have 2 date parameters “From” and “To”, so you can set both of them following the same token via Date manager. You can submit user reports to daily, weekly, monthly, batches, so they won’t not need running their reports during the day anymore and get more free time.
Using this approach, you can schedule document printing and any posting jobs to run automatically in less stressful hours. This will allow avoiding, system problems when postings are done simultaneously by different people.
Month End Reports
Using date manager program, you can automate month reporting for accounting. That would make their job much easier since they are struggling to do a lot of things during short period of time – makes it very stressful.
If your fiscal month ends fit calendar month ends, then you belong to a few lucky ones who can set up monthly batch very fast by scheduling month.sh script to run the first date of each month at 00 hours 01 minutes.
If your fiscal periods do not match calendar periods (majority of businesses), then setup your monthly batch to run every day at 00:01, and the first program on the batch should be a custom program, performing the following:
– it compares current system date at the run time with the GL calendar glc_cal.glc_start
– in case it’s a match, the program logic assigns bcd_det.bcd_process = YES to the rest of the jobs listed on “month” batch and they will be executed. Otherwise the program should assign bcd_det.bcd_process = NO to the rest of the jobs in “month” batch and nothing will be executed.
Where to get this program: take batch date manager logic as a starting point and make mods, perform testing you your DEV prior to set it up in production. We may give away our version of this program later on as well.
QAD batch functionality for complicated processes
In addition to batch reports, you may consider some processes to run as batch process on demand. A good candidates could be cost roll up or simulated cost set update or something else in this nature. Accounting staff run periodically a sequence of programs one by one as shown on the diagram below.
 |
It’s very time consuming when user runs these programs manually. You can schedule them in a dedicated batch and add date manager requests for steps 5 and 6. Then simply run this batch from 36.14.13 menu when business users request it. In the case, the process needs to be run again later – you already have everything prepared, run it again from 36.14.13 or schedule to the cron as one time job.
In case this process should be run next time with slightly different parameters, use our other program “Random Batch Parameter Updater” – is part of free toolkit you can get from us.
xxuprbprm.p 32soft 36.14.21 * Random Parm Updater 03/15/10 ┌──────────────────────────────────────────────────────────────────────────────┐ │ Batch ID: night │ │ Program: iclorp.p Inventory Detail by Location │ │ Submitted By: user │ │ Submit Date: 03/13/10 │ │ Submit Time: 18:59:55 Total Parms: 6 │ │ Parameters: "" "" "ABC" "ABC" "" "" │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────────────────┘
List of programs/processes, candidates for batch automation
- Invoice print, Invoice post
- SO Confirmation print and distribution to sales reps, customers
- AR memos print, AR Statements
- AR aging print and distribution to sales reps
- GL postings
- GL Report writer sync and GLRW report preparations
- AP voucher reports
- Payment remittances
- PO receipts report, PO print
- Sales analysis reporting
- Inventory valuation reporting
- 16.21 WO Accounting close
- Supplier schedule print and distribution
- Cost roll up, periodic simulated cost updates
Electronic Output versus Paper Output
Electronic output is preferable. You may already use some of 3-rd party output solutions for QAD such as cyberquery, optio, jet forms, or open source PDF libraries, etc.. In case you still deciding which output solution to select for batch processing, we have a look at our LBOX, which is affordable, simple in use and delivers a great value, more information.
We Also Recommend:
