Information Systems:Gilead Sales Report - Old

From uniWIKI
Jump to navigation Jump to search

This program has been re-factored in Python, refer here: Gilead Sales Report

Overview

Gilead requires daily sales and inventory reporting from uniPHARM, so a solution needed to be developed to generate and transmit such reports according to their specifications. This endeavor was a greenfield project of sorts, in that it was developed from scratch and comprises new techniques, all of which will be discussed here. This article represents the documentation for this solution.

Important notes:

  • Gilead uses ValueCentric as their data/analytics provider, so reports are sent to their servers. There is no communication with Gilead servers in this process.

Components of the solution

The components are as follows:

  • NC_GILREP.PGM: A non-CGI WebSmart-generated RPG program.
  • GILSUMFTP: A CL program that calls all the other programs.
  • GILEADREP: Job scheduler entry that calls GILSUMFTP.
  • sFTP utility in PASE on IBM i.
  • IFS on the IBM i to store report files for transmission and archiving.

NC_GILREP (RPG)

In its essence, WebSmart is an RPG translator of sorts, so it is possible to create powerful RPG batch programs in WebSmart i.e. using WebSmart's PML (language) and IDE (graphical editor). In WebSmart, creating such a batch program is done by compiling the program as non-CGI (hence the prefix in NC_GILREP). When compiled as a non-CGI, the program is no different than any other RPG batch program - there is no interface (web or green screen); it simply gets called and does its input/output over files. NC_GILREP performs the following tasks:

  1. Collects inventory statistics for Gilead items and generates a pipe-delimited IFS file. This is the 852 file.
  2. Same as above, but collects sales statistics and generates a separate IFS file. This is the 867 file.
  3. Cleans up old reports by moving them to the archive folder.
  4. (Disabled) Emails admin upon successful run of program.
I don't think it's necessary to provide further detail of the program's code, at least until we establish a documentation standard. If you're still interested at this point, you'll likely know how to look at the source...

File format and layout

The report files are based on EDI standards, namely, the 852 and 867 document types. The file spec and file naming convention were provided by ValueCentric.

GILSUMFTP (CL)

This script calls NC_GILREP to generate the reports on the IFS and issues the sFTP command to transmit the files to ValueCentric.

GILEADREP

This is the Job Scheduler entry that runs Monday to Friday and Sunday at 7AM. It runs GILSUMFTP under ISVCACCT.

sFTP Transmission

To date, there already exists several B2B communications on the i, but a) it is all done via FTP and b) it is mostly in a pull configuration (i.e. downloading files). sFTP transmission, as required by ValueCentric, cannot use the built-in FTP client on the i, because it involves a different protocol (SSH) altogether, while the FTP client on the i is for basic FTP transmission. IBM i does have sFTP functionality, but this is offered as a PASE utility (read more about PASE here). Running it is thus a different process (it's not directly via command-line, instead must execute in QSH).

Key authentication

The sFTP client on the i does not allow for password authentication when run in batch (only interactive), so key-based authentication is being used instead. The following steps were taken to facilitate this:

  1. A new user profile was created: ISVCACCT. (We need a new service account anyway, I think too many things run under EODJOB). One of the limitations of sFTP on the i is that the username needs to be 8 characters or less, otherwise key-based authentication fails. The home directory for the user profile also needed to be explicitly set as (/home/ISVCACCT), as that is where the .ssh folder and keys are found.
  2. A new keypair was generated. There is a tool in the PASE environment called ssh-keygen that generates a pair. RSA was used, and the public key was provided to ValueCentric to be whitelisted. The private key is found in the home directory of the ISVCACCT user profile and bears the same filename.

IFS files and folders

  • The report files are in /UWD Staging/Reports/Gilead/. There should be two .txt files (867 & 852) with a datestamp of the current day, but with a filename of the previous day (i.e. report is generated the following day)
  • The Archive folder contains report files for previous dates.
  • The Log folder contains log files for the report run and connectivity.

To-do

  • Specify which libraries the source and program objects are in.
  • Create PASE page.
Retrieved from "https://owl.unipharm.com/mediawiki/index.php?title=Information_Systems:Gilead_Sales_Report_-_Old&oldid=11952"