EmployeeExpenseReport

Flow version 5 as of 08/01/18

Table of Contents

  1. Introduction
  2. General Data Flow
  3. Components
  4. Dependencies
  5. Scheduling and Triggers
  6. Recovery
  7. Archiving
  8. Notifications
  9. Troubleshooting
  10. Maintenance
  11. Security
  12. Change Control
  13. Key Contacts
  14. Related Files

Introduction

This flow runs the Integrations process which gathers the current days transactions from the website. The flow runs the following Lawson jobs to process the incoming data: AP510-DLTA, AP510-142, AP510-30, AP510-180, AP520-RFND, AP520-70, AP520-910, AP520-981

Back to Table of Contents


General Data Flow

The flow first connects to APCExpense using a webservice node. Once connected, XML request objects for User and Expense are built and sent to APCExpense to get back User and Expense response objects. The retrieved user.xml and expense.xml undergo cleanup to ultimately output userout.xml and expenseout.xml.

Then, lwpf85.pl PERL script is written and executed to create an XML parser. This results in printing of lwpf85.txt, lwpf85.log, and lwpf85_skipped.txt using information from userout.xml and expenseout.xml. The userout and expenseout xml files are archived as userout.txt and expenseout.txt. At this point, file cleanup is performed to delete all the used .xml and PERL scripts. Next, lwpf85.txt is read to ensure it is not empty. If empty, the process ends but otherwise, it is archived.

Data is then dumped from APCVENMAST to lwpf250.txt using rngdbdump system command. The flow then uses SQL transaction nodes to TRUNCATE (delete table data) four tables: ls_apps.APCATTACH, ls_apps.APCVENMAST, ls_apps.APCDISTRIB, and ls_apps.APCINVOICE. Next, LWRPG090.pl PERL script is written and executed which prints out APCVENMAST.csv, APCDISTRIB.csv, and APCINVOICE.csv using data from lwpf85.txt. Using system command importdb and the three .csv files just printed, APCVENMAST, APCDISTRIB, and APCINVOICE are loaded while also printing to lwpf85.log. Then, the LWCLP090 job is run. Finally, lwpf85.txt is cleared, APCVENMAST is refreshed using importdb with lwpf250.txt.

Back to Table of Contents


Components Table

Type Name Notes
Input file <!_configuration.main.tempdir>\user.xml
Input file <!_configuration.main.tempdir>\useroutout.xml
Input file <!_configuration.main.tempdir>\expense.xml
Input file <!_configuration.main.tempdir>\expenseout.xml
Input file <!_configuration.main.FilePathLocal>\lwpf85.txt
Output file C:/Clients/XYZ/CloudIPA/Integrations/APCwireToLawson/APCwireRequest.xsd
Output file <!_configuration.main.tempdir>\user.xml
Output file <!_configuration.main.tempdir>\userout.xml
Output file <!_configuration.main.tempdir>\expense.xml
Output file <!_configuration.main.tempdir>\expenseout.xml
Output file <!_configuration.main.FilePathLocal>\lwpf85.txt
Output file <!_configuration.main.FilePathLocal>\logs\lwpf85.log
Output file <!_configuration.main.FilePathLocal>\lwpf85_skipped.txt
Output file <!_configuration.main.FilePathLocal>\ARCHIVE\lwclp090_userout-<!TodayAGS>.txt
Output file <!_configuration.main.FilePathLocal>\ARCHIVE\lwclp090_expenseout-<!TodayAGS>.txt
Output file <!_configuration.main.FilePathLocal>\ARCHIVE\lwpf85-<!TodayAGS>.txt
Output file <!_configuration.main.tempdir>\lwpf250.txt
Output file <!_configuration.main.tempdir>\APCVENMAST.csv
Output file <!_configuration.main.tempdir>\APCINVOICE.csv
Output file <!_configuration.main.tempdir>\APCDISTRIB.csv
Script <!_configuration.main.tempdir>\lwpf85.pl
Script <!_configuration.main.tempdir>\LWRPG090.pl
Database ls_apps.APCVENMAST database table
Database ls_apps.APCATTACH database table
Database ls_apps.APCDISTRIB database table
Database ls_apps.APCINVOICE database table
Job LWCLP090 job
Configuration Variable <!_configuration.main.tempdir>
Configuration Variable <!_configuration.main.FilePathLocal>
Configuration Variable <!_configuration.main.LawsonDB>

Back to Table of Contents


Dependencies

-ls_apps.APCVENMAST, ls_apps.APCATTACH, ls_apps.APCDISTRIB, ls_apps.APCINVOICE database tables must be able to be accessed and updated

-LWCLP090 job must exist

-User must be authorized to run LWCLP090 job

Back to Table of Contents


Scheduling and Triggers

-OnPrem Trigger Method: appworx

-Cloud Trigger Method: Every day at 12:05am

Back to Table of Contents


Recovery (Refer to General Data Flow)

Scenario Procedure
1. Fail @ "Uses webservice node to connect to APCwire" step 1. Requires correct configuration of WebSvc9530 node
2. Fail @ "Builds XML request objects to retrieve user and expense responses from APCwire" step 2. Relies on successful connection to APCwire in step 1
3. Fail @ "Performs cleanup on user.xml and expense.xml to output userout.xml and expenseout.xml" step 3. Relies on steps 1 and 2
4. Fail @ "Writes/executes lwpf85.pl to print lwpf85.txt, lwpf85.log, and lwpf85_skipped.txt" step 4. Manually run C:\INBOUND\ProdLib\TEMP\lwpf85.pl or use the PERL script in FileAccess1160 node by using system command
start perl C:\INBOUND\ProdLib\TEMP\lwpf85.pl
5. Fail @ "Archives userout.xml and expenseout.xml and performs file cleanup" step 5. Use system commands:
powershell "copy C:\INBOUND\ProdLib\TEMP\userout.xml C:\INBOUND\ProdLib\FILES\ARCHIVE\lwclp090_userout-<!TodayAGS>.txt"
and
powershell "copy C:\INBOUND\ProdLib\TEMP\expenseout.xml C:\INBOUND\ProdLib\FILES\ARCHIVE\lwclp090_expenseout-<!TodayAGS>.txt"
Manually delete userout.xml, expense.xml, and expenseout.xml from C:\INBOUND\ProdLib\TEMP
6. Fail @ "Reads lwpf85.txt and archives it" step 6. Use system command:
powershell "copy C:\INBOUND\ProdLib\FILES\lwpf85.txt C:\INBOUND\ProdLib\FILES\ARCHIVE\lwpf85-<!TodayAGS>.txt"
7. Fail @ "Dumps data from APCVENMAST to lwpf250.txt" step 7. Use system command:
rngdbdump -ct lsapps APCVENMAST > C:\INBOUND\ProdLib\TEMP\lwpf250.txt
8. Fail @ "TRUNCATEs SQL tables APCATTACH, APCVENMAST, APCDISTRIB, and APCINVOICE" step 8. If ls_apps.APCATTACH, ls_apps.APCVENMAST, ls_apps.APCDISTRIB, and ls_apps.APCINVOICE SQL tables able to be accessed/updated, manually apply the SQL TRUNCATE statements in SQLTxn5280, SQLTxn2670, SQLTxn9270, and SQLTxn8460 nodes
9. Fail @ "Writes/executes LWRPG090.pl to create APCVENMAST, APCDISTRIB, and APCINVOICE" step 9. Manually run C:\INBOUND\ProdLib\TEMP\LWRPG090.pl or use the PERL script in FileAccess7070 node by using system command
start perl C:\INBOUND\ProdLib\TEMP\LWRPG090.pl
10. Fail @ "Load APCVENMAST, APCDISTRIB, & APCINVOICE using importdb and printing to lwpf85.log" step 10. Use system commands:
importdb -af lsapps APCVENMAST C:\INBOUND\ProdLib\TEMP\APCVENMAST.csv >> C:\INBOUND\ProdLib\FILES\logs\lwpf85.log
and
importdb -af lsapps APCINVOICE C:\INBOUND\ProdLib\TEMP\APCINVOICE.csv >> C:\INBOUND\ProdLib\FILES\logs\lwpf85.log
and
importdb -af lsapps APCDISTRIB C:\INBOUND\ProdLib\TEMP\APCDISTRIB.csv >> C:\INBOUND\ProdLib\FILES\logs\lwpf85.log
11. Fail @ "Run job LWCLP090" step 11. Verify existence of LWCLP090 job assigned to job owner INFOR\crv-ipabatchuser.
-LWCLP090 job can be run with web program:
lawson-ios/action/SubmitJob?jobName=LWCLP090&jobOwner=INFOR\crv-ipabatchuser&wait=TRUE
12. Fail @ "Clear lwpf85.txt and refresh APCVENMAST using importdb with lwpf250.txt" step 12. Manually clear all content from C:\INBOUND\ProdLib\FILES\lwpf85.txt
Use system command:
importdb -af lsapps APCVENMAST C:\INBOUND\ProdLib\TEMP\lwpf250.txt

Back to Table of Contents


Archiving

File Location of archive
Output LWCLP090_userout File <!_configuration.main.FilePathLocal>\ARCHIVE\lwclp090_userout-<!TodayAGS>.txt
Output LWCLP090_expenseout File <!_configuration.main.FilePathLocal>\ARCHIVE\lwclp090_expenseout-<!TodayAGS>.txt
Output LWPF85 File <!_configuration.main.FilePathLocal>\ARCHIVE\lwpf85-<!TodayAGS>.txt

Back to Table of Contents


Notifications

Back to Table of Contents


Troubleshooting

-View Workunit log

-Check inbound directory for file

-Check for failed email responses

-Review errors in WebSphere logs

-See record counts in APC tables

-Check FTP transmission logs

-Check for done.txt file in the work directory

Back to Table of Contents


Maintenance

-Log and archive files need to be maintained over time as they are not being cleaned up periodically

Back to Table of Contents


Security

-IPA user must have access to read/write/delete all interface files

-IPA user must have access to run SQL queries/transactions on ls_apps.APCVENMAST, ls_apps.APCATTACH, ls_apps.APCDISTRIB, ls_apps.APCINVOICE database tables

-IPA user must be authorized to run LWCLP090 job

-IPA user must have importdb access

Back to Table of Contents


Change Control

Name Date Update Notes
Tan Rezaei 6/5/19 Initial draft
Young Lee 6/14/19 File format update

Key Contacts

Name Company Phone Email Notes
Tan Rezaei Nogalis Inc. (844) 664-2547 info@nogalis.com Flow developer

Back to Table of Contents


Related Files

1.Importing Security Certificate into the JDK.pdf

2.Integrating data with APCExpense web services v2.0.doc

Back to Table of Contents