CAMS to Workday Journal Import

Load Journal entries and Transaction Documents into Workday from CAMS.

Step 1. Connect to the CAMS database engine [FPCCAMSDB01]. You should see the CAMS_Enterprise database when you connect.

Step 2. Open the scripts available in Teams in DevOps=>Development=>CAMS 2 WD Scripts. Note that the Development channel is hidden.

Step 3. Modify the declared parameters of each of the three files:

Before:

After:

Before:

After:

Step 4. Begin by executing the CAMS2WD_ManualLoad_Journalsv2.sql script. Note the TermCalendarIDs labeled Current, future, or older in the output and ignore the VOIDs. Note in the example below that there is a CURRENT TermCalendarID of 53 and a future TermCalendarID of 57. The process will have to be run for these IDs. There is no older TermCalendarID in this example. Your output may vary.

Step 5. Change the TermID and run it for the next term on your list. In our example, the two TermID’s listed were 53 and 57. On the first run through, you would run the process for 53 and then 57 on the second run. You’ll have to perform this process for each of the TermIDs listed in Step 4.

Ex. First Run:

Ex. Second Run:

Step 6. Copy the header rows displayed in the query output. Ensure that you do not copy the column headings with the rows.

Step 7. Open the cross-department drive and navigate to the X:\Workday\EIB LOAD\EIB Templates\Journals folder. Right-click one of the files and navigate to Open with=>Excel to open the file in Excel.

Step 8. Ensure you are on the Import Accounting Journal sheet. Delete the data from the previous run.

Step 9. Paste the header rows into the sheet, ensuring that the Header Key column from the database output is in the Header Key column on the spreadsheet. This means there will be a blank column on the left of the spreadsheet.

Ex. Header Row Output

Ex. Paste into Sheet

Step 10. Now copy the line rows. Ensure that you do not copy the column headings with the rows.

Step 11. Change to the Journal Entry Line Replacement sheet in the spreadsheet. Clear the current data and then paste the rows into the spreadsheet. Note that you can use the first column for the dates that were returned as these will be ignored by the import process.

Step 12. At the bottom of the SQL script output, a filename will be given. Copy the filename and save the spreadsheet using the given filename. You may be prompted to ask if you would like to save the file in this format. Click Yes on the prompt if it appears.

Filename in SQL Server:

Filename in Save As for Excel:

Step 13. Log in to the Workday production tenant. Type Launch / Schedule Integration in the search bar. Click on the Launch / Schedule Integration task. [You may want to bookmark the URL to expedite the import of the files.]

Step 14. In the dialog, ensure that SIS Journal is the Integration selected and that Run Frequency is set to Run Now. Click OK.

Step 15. When the task starts, click the dropdown in the Value column and select Create Integration Attachment.

Step 16. On the attachment screen, select or drop the file you saved in Step 11 and click OK.

Step 17. Back at the Schedule an Integration screen, verify the file is in the Value column and click OK.

Step 18. The View Background Process screen will show that the job is running. Click Refresh to view the progress of the job. When it is complete, you should have two warnings for each line in the header section of the spreadsheet. These are safe to ignore.

Step 19. Back on the SQL Server Management Studio screen, change the Load bit to 1 and run the script to make the changes on the CAMS_Enterprise database.

Step 20. When the script execution is complete, verify that the run was done in LOAD mode. At the bottom of the output, check that the line under the filename shows the term was uploaded.

Step 21. Change the script to the next term and start at Step 4. When all terms are completed, set the Load bit back to 0 and save the script.

Step 22. Change to the CAMS2WD_ManualLoad_AdHocsv2.sql script. Ensure that the modifications in Step 3 have been completed and execute the script. Take note of the TermCalendarIDs and TransDoc numbers in the output, ignoring any that are labeled as Refunds since they will be processed using the Refunds script. Note that there are two sections of output: one with CURRENT and one with future terms. In the example, the combinations that will need to be run are [52, 168], [53,167], [53,168], [57, 171], [57, 177].

Step 23. Open the cross-department drive and navigate to the X:\Workday\EIB LOAD\EIB Templates\AdHocs folder. Open one of the files in this drive and clear the information like in Step 8. Note that there is only one sheet in this Excel file.

Step 24. Copy the output rows and paste them into the spreadsheet. The first column should be blank.

Ex. Rows in SQL Server

Ex. Rows in Excel

Step 25. Save the file with the specified filename near the bottom of the script output.

Step 26. Log in to the Workday production tenant. Enter Launch / Schedule Integration in the search bar. Click on the Launch / Schedule Integration task. [You may want to bookmark the URL to expedite the import of the files.]

Step 27. In the dialog, ensure that SIS Ad Hoc Bank Transactions is the Integration selected and that Run Frequency is set to Run Now. Click OK.

Step 28. When the task starts, click the dropdown in the Value column and select Create Integration Attachment.

Step 29. On the attachment screen, select or drop the file you saved in Step 24 and click OK. Ensure that you are getting the file from the Ad Hocs folder.

Step 30. Back at the Schedule an Integration screen, verify the file is in the Value column and click OK.

Step 31. The View Background Process screen will show that the job is running. Click Refresh to view the progress of the job. When it is complete, you should have no warnings or errors.

Step 32. Back on the SQL Server Management Studio screen, change the Load bit to 1 and run the script to make the changes on the CAMS_Enterprise database.

Step 33. Repeat steps from Step 22 to Step 31 for each of the Term and TransDoc combinations noted in Step 21.

Step 34. Change the Load bit back to 0 and the TermID back to the current term and save the script.

Step 35. Change to the CAMS2WD_ManualLoad_AdHocsv_Refunds.sql script. Ensure that the modifications in Step 3 have been completed and execute the script. Take note of the TermCalendarIDs in the output. Note that we ignored refunds with the last script because they must be handled with the CAMS2WD_ManualLoad_AdHocsv_Refunds.sql script. In the example below, the script should be run for TermCalendarID 52 and 53. There are no entries in the older or future sections.

Step 36. The refunds process also uses the spreadsheet available in the X:\Workday\EIB LOAD\EIB Templates\AdHocs folder. Open one of the files in this drive and clear the information.

Step 37. Copy the output rows and paste them into the spreadsheet. The first column should be blank.

Ex. Row in SQL Server

Ex. Row in Excel

Step 38. Save the file with the specified filename near the bottom of the script output.

Step 39. Log in to the Workday production tenant. Enter Launch / Schedule Integration in the search bar. Click on the Launch / Schedule Integration task. [You may want to bookmark the URL to expedite the import of the files.]

Step 40. In the dialog, ensure that SIS Ad Hoc Bank Transactions is the Integration selected and that Run Frequency is set to Run Now. Click OK.

Step 41. When the task starts, click the dropdown in the Value column and select Create Integration Attachment.

Step 42. On the attachment screen, select or drop the file you saved in Step 33 and click OK. Ensure that you are getting the file from the Ad Hocs folder.

Step 43. Back at the Schedule an Integration screen, verify the file is in the Value column and click OK.

Step 44. The View Background Process screen will show that the job is running. Click Refresh to view the progress of the job. When it is complete, you should have no warnings or errors.

Step 45. Back on the SQL Server Management Studio screen, change the Load bit to 1 and run the script to make the changes on the CAMS_Enterprise database.

Step 46. Repeat steps from Step 33 to Step 41 for each of the Term and TransDoc combinations noted in Step 32.

Step 47. Change the Load bit back to 0 and the TermID back to the current term and save the script.

Troubleshooting 1. If you receive an error message like the one shown below, change the database to CAMS_Enterprise. It is often defaulted to master.

Message:

Solution:

Step 1. Use this numerical step-by-step method to present the process. Start at the beginning where the user is reading this from their organization’s device. If you must input an important list of terms, format it like this:

(see Step 1) or Step 1

Unique Term

‘Unique Term’

“type this” or Ctrl + Shift

[type it in these brackets and not parentheses]

Step 2. Include visual aids like screenshots with highlighted actions and brief instructions corresponding with the step numbers to illustrate each step [see the example below and download this Adobe Illustrator file to copy and paste the graphics]. If your instructions seem long, break them down into smaller segments like this:

Step 3. Utilize hyperlinks to help direct users to specific web addresses they need to use to complete the steps correctly.