Information Systems:Gilead Sales Report

The Gilead Sales Report is a daily CSV (actually pipe-delimited) report that is generated every morning and transmitted to ValueCentric (data provider). This program is written in Java and run as an Integrator timer job.

sFTP

ValueCentric only accepts connections from whitelisted IPs. I believe our Telus WAN subnet has been whitelisted, or at the very least, both the WAN IPs of bart and superman, so the regular programs can run (it used to run on Bart). This is just something to keep in mind in case you try to either upload the file manually from home (won't work even through VPN) or run the program.

The sFTP connection also requires public key authentication (no password authentication). This private-public key pair was generated by us and the public key was provided to ValueCentric. The name of the key is isvcacct.key, found in the SSH keys folder in superserver\tech\common

Manually running the report

A workflow for this that can be executed by a non-developer is still being worked on. This is because the Class Runner feature in Integrator, which is how you would normally run a one-off job, doesn't accept command line arguments (runtime args), which is needed to instruct the program to generate a report for a specific date. If no argument is supplied, the program generates a report for the previous day, which is of limited use.

An ideal solution will likely involve running the Java program from the command line on the Integrator server, but with the class path pre-set to look in the lib folder. Otherwise, the program's dependencies will not be included. The command will look something like:

C:\Program Files\Java\jdk1.8.0_251\bin\java.exe com.unipharm.integrations.bizreports.GileadDailyActivityReport " " "20210422" Note the format of the date and the fact that it is the second command line argument (args[1])

How to Manually Run Report and Manually send the file via SFTP

If there is a problem with the daily Gilead sales report that is sent to ValueCentric you can rerun the Integrator process that generates the file and sends it to ValueCentric as long as it's the same day. The program is preconfigured to only produce a file for the previous day. If you need to generate a file from an earlier day, then the only way to do this is to manually create the file.

To manually create a file, I generally use the previous days file as a guide to help make sure the values make sense. The report needs to be created as per the file specs which can be found here:

\\Superserver\Tech\Common\Gilead Daily Sales Report\File Specs\VT Standard File Layout - 852 short form 2015.doc

I use a template created in excel to help fill in the values as it's easier to work with. The template has all the column headers and includes the item numbers in Column A to make it easier to fill in the values. The template comes prefilled with data but should be replaced with the appropriate data needed to make the file that you're making. Columns G through Q need to be updated with the new data minus column O, which can stay the same. I use the InfoNet program Item Transaction Tracking found in Inventory under the uniVIEW section.

\\Superserver\Tech\Common\Gilead Daily Sales Report\Manual File Generation Templates\Gilead Daily Sales File Excel Template.xlsx  

To access the data needed to create the report for each item

1. Fill in the Date From and the Date To.  It's recommended to put in a Date From that is at least a month or two ahead of the date you're making the report for so if there is a history you can see it.  The Date To would be the date of the report.
2. Fill in the item number, which can be found in column A of the template.
3. Check the Richmond box to use the MAI warehouse.
4. Press the search button to get the results.
5. The results will show: Beginning Inventory Qty, Sold Qty, On PO Qty, Received Qty, Adjusted Qty, Transactions Qty, which is everything you need for the report.
6. The only thing missing will be the Qty Forecast.  To get these numbers I just use the previous days report and copy the values.

Once the data is accurate the easiest way to create the file is to:

1. Delete column A as it's not part of the file spec and is just there for reference.
2. Delete row 1 the header row as it's not part of the file spec and is just there for reference.
3. Save as a .csv file and use the following filename convention: man852_uniPHARM_yyyymmdd_VC155 where the date is the date of the report
4. Close the newly created .csv file and open it with notepad.  
5. Select Replace from the Edit menu and put a comma (,) in the Find what box and a pipe (|) in the Replace with box and the select Replace All and then close the Replace dialog box.
6. From the file menu choose Save As and save the file using the same file name but this time as type .txt file.
7. Once the .txt file is created the .csv file can be deleted.

To send these manually create files by FTP you must configure a FTP client with the following credentials and send it from within the building:

Protocol: SFTP – SSH File Transfer Protocol
Host: b2b.valuecentric.com
Port: 22
Login Type: Key file
User: isvcacct

The key file is located in the following location:

\\Superserver\Tech\Common\Gilead Daily Sales Report\SSH Key\isvcacct.ppk

Additional instructions can be found here:

\\Superserver\Tech\Common\Gilead Daily Sales Report\SFTP Profile\Manually Connecting with ValueCentric via SFTP.docx