Welcome to JRI's Help page for Auto Invoicing/Statements

There are a number of downloads that we get from ADP (no uploads to ADP at this time). Some are done via cron as background jobs while others are done on demand (like USA Payroll).

Some of these downloads are:

  • Payroll download
  • Employee download
  • Location download
  • Location download
  • Orginazation structure download
  • Labor Efficiency downloads
  • FSA download
  • Short term disability download (then upload to Voya)

Most of these are background jobs that run via cron (see SFTP/Samba Controls).

For the background jobs, there are 2 typical types of errors:

  • Connection failure &
  • Out-of-date file(s)

For connection failures, the driving program, SYSS9002.3 is smart enough to detect this. If the connection fails, it will sleep for 30 seconds and try again. It will try up to 5 times before giving up.

For out-of-date files:

  • The download is ignored
  • An email is sent to those effected
  • A HelpStar ticket is created

The programmer needs to follow up with this. Periodically test the FTP site (FileZilla is suggested due to it's ease of use). If an updated file is eventually put on the FTP site, the programmer should manually run the job (see SFTP/Samba page, then click on the "Cron Jobs & Logic Flow" tab for details about each job). The error should also be tracked in the ADP Failure Timeline (but only if you are familiar with XML!) Add the details to the file on this web site: ADP\xml\ADP_Details.xml.

Company 01 (USA) Payroll data is exported by ADP to their FTP site. Company 02 (Canada) is handled differently. This web page focuses on 01 because it uses FTP to download the data from ADP. 02 Generally works the same way other than how/where it gets the file from.

Both 01 & 02 use GLM9014 to load the data into Avante, but where they get the data is different.

  • 01 pulls in the data from ADP's FTP site
  • 02 gets the data directly from the UNIX box at /av1/igi/avante98/flodata/ADP.GL.EXPORT1
1 Sherri Hart, Tracy Jones, Neal North & JRI have a symbolic link from their O: drive to this dir and it is called ADP.GL.EXPORT.


Query ADP's FTP Site:

When running GLM9014, the first prompt is for which company. Based on the answer to this, the program determines where to get the file.

For 01, the first step is to query ADP's FTP site. GLMS9014.1 does this using the alternative calling of SYSS9002.3 (see complete details on SFTP & Samba).

  • HIDE.TCL = 1
  •  
  •  
  • DLDATA.KEY = ""
  • FILE.PREFIX = ""
  • FTP.TO = "" ;* Determined by control record
  • FTP.PASSWORD = "" ;* Determined by control record
  • DATE.TIME = ""
  • OPTIONS = ""
  • OPTIONS<1, 9> = HIDE.TCL
  • OPTIONS<1, 21> = "ADP.GL.QUERY" ;* SFTP.CTL.KEY control record
  • FUTURE3 = ""
  • CALL SYSS9002.3(DLDATA.KEY, FILE.PREFIX, FTP.TO, FTP.PASSWORD, DATE.TIME, OPTIONS, SFTP.RESULTS, FUTURE3)
  • ERROR.MSG = OPTIONS<1, 30>
ED SFTP.CTL ADP.GL.QUERY
001: Query ADP's GL FTP site
002: dsupADPGL@sdgcl.adp.com
003: cd OUTBOUND^253ls -la^253quit

GLMS9014.1 then parses SFTP.RESULTS to determine success or failure. If success, it writes the file names in TNN.


Chose File to Download:

GLMS9014.1 then launches GLMX9014.1 to allow the user to pick which of the files to download.


Download the Chosen File:

Assuming the user picked a file, GLMS9014.1 then launches a 2nd alternative SFTP command to actually download that file:

  • DLDATA.KEY = ""
  • FILE.PREFIX = ""
  • FTP.TO = "" ;* Determined by control record
  • FTP.PASSWORD = "" ;* Determined by control record
  • DATE.TIME = ""
  • OPTIONS = ""
  • OPTIONS<1, 9> = HIDE.TCL
  • OPTIONS<1, 21> = "ADP.GL.GET" ;* SFTP.CTL.KEY control record
  • OPTIONS<1, 22> = filename ;* File name to get from ADP
  • FUTURE3 = ""
  • CALL SYSS9002.3(DLDATA.KEY, FILE.PREFIX, FTP.TO, FTP.PASSWORD, DATE.TIME, OPTIONS, SFTP.RESULTS, FUTURE3)
There is special logic in SYSS9002.3 that knows how to take the Filename chosen and load it into the SFTP.CTL record dynamically.
ED SFTP.CTL ADP.GL.GET
001: Get a specific file from ADP's GL FTP site
002: dsupADPGL@sdgcl.adp.com
003: cd OUTBOUND^253lcd /vol1/prod/work^253get $FILENAME$^253quit


Further Steps:

The downloading logic is complete. The following general steps now loosely occur:

  • Move the file from CYBORG to EDI.GL
  • Make a backup of the original file
  • Validate the data in the file and adjust the GLs as needed
  • Write data to GLJRNL
  • Purge any unposted prior data
  • Update control record


Delete the file from ADP's FTP Site:

Prompt the user if they want to remove the file that was just posted. Unless there was some problem, the answer should always be yes. This will run the 3rd and final FTP command:

  • DLDATA.KEY = ""
  • FILE.PREFIX = ""
  • FTP.TO = "" ;* Determined by control record
  • FTP.PASSWORD = "" ;* Determined by control record
  • DATE.TIME = ""
  • OPTIONS = ""
  • OPTIONS<1, 9> = @TRUE
  • OPTIONS<1, 21> = "ADP.GL.REMOVE" ;* SFTP.CTL.KEY control record
  • OPTIONS<1, 22> = filename ;* File name to get from ADP
  • FUTURE3 = ""
  • CALL SYSS9002.3(DLDATA.KEY, FILE.PREFIX, FTP.TO, FTP.PASSWORD, DATE.TIME, OPTIONS, SFTP.RESULTS, FUTURE3)
There is special logic in SYSS9002.3 that knows how to take the Filename chosen and load it into the SFTP.CTL record dynamically.
ED SFTP.CTL ADP.GL.REMOVE
001: Remove a file from ADP's GL FTP site
002: dsupADPGL@sdgcl.adp.com
003: cd OUTBOUND^253rm $FILENAME$^253quit


Bad Data in File:

Occasionally, the data in the file from ADP will not agree with Avante (week is off by 1 or something like that).

To keep the flow working the same way, follow these steps:

  • Download a copy of the file from ADP's FTP site to your PC (again, FileZilla is hightly suggested)
  • Make a backup on your PC (just in case)
  • Using a text editor, adjust the file on your PC as needed
  • Delete the file from the FTP site (you can delete, but not ovwrwrite)
  • Upload the adjusted file from your PC back to ADP's FTP site
  • Re-run GLM9014 and download the adjusted file - process normally
  • Delete the backup file from your PC once everything works

Occationally, there is a problem with Auto Invoicing, usually with Optio failing to build the needed PDFs. The following instructions are the likely steps to fix this1:

  1. Always review the AUTO.INVOICE.RESULTS file.
  2. Determine how many PDFs were actually built:
    1. Go to G:\Avante Attachments\Invoices\LIVE\yyyy
    2. Sort the PDFs by date
    3. Count the PDFs that were built by Auto Invoicing (any PDF after 9:10 PM for the day in question)
    4. Compare this count vs. the counts reported in the Auto Invoicing emails
  3. Determine how many invoices were built:
    1. COUNT ICEHDRHST WITH INV_DATE = "mm/dd/yy" BY COMPANY
  4. Determine how many invoices were built w/o a PDF.DATE:
    1. COUNT ICEHDRHST WITH RELEASE_NBR # "0" AND WITH INV_DATE > "06/30/08" AND WITH NO PDF.DATE
    2. This number should agree with the # of missing PDFs
    3. For each invoice that still needs a PDF, set ICEHDRHST.USR PDF.DATE to null (they are probably already null)
  5. Create the missing PDFs:
    1. Go to the Avante menus
    2. Run process SOP9121
    3. Enter a "Y" for the single prompt and hit F2
    4. Check on the status of the PDFs:
      1. Go to G:\Avante Attachments\Invoices\LIVE\yyyy
      2. Wait until the PDFs stop being created
      3. Count the number of PDFs from the manual run plus those from Auto Invoicing
      4. They better match the number of total invoices!
  6. Once all of the missing PDFs have been built:
    1. From TCL:
      1. Make sure that SYSTBL LAST.INVOICE exists (it does not matter what is in it)
      2. Change DSCTBL SYSS9111.11.DRIVER F7 to match the company you wish to process:
      3. COMO ON JRI
      4. SYSS9111.11.DRIVER
        1. Enter the SAVEDLIST name (from the email)
        2. This will:
          • Verify the keys in the SAVEDLIST are valid
          • Give you the # of keys in the SAVEDLIST (it beeter agree with what was missing!)
          • Show the PDF.DATE that will be used
          • Show the time range of the PDFs
          • Prompt for you to continue (must enter "Y***")

            Note that this can take a while since it is doing a lot of validations
          • When finished, you should see a message similar to the one below:
      5. COMO OFF
      6. This will complete the PDF process:
        1. Build the PDFs on the G: drive
        2. Email the PDFs to customers as needed
        3. Bundle & FTP the "snail mail" PDFs to Lanvera
      7. Review the COMO file
      8. Change DSCTBL SYSS9111.11.DRIVER F7 back to 01
1 Note that since these steps are being done due to an unexpected error, these steps may not be 100% correct for your specific situation. This is just a best guess as to what steps need to be followed.