The data file should be formatted like the IFIS F60 record layout with the exception that SAP data will be used instead of IFIS data.
This process typically can be used, but is not limited to, uploading some of the year end journal entries such as: College Work Study charge backs, Salary Prorates, SPS Benefit deductions, Endowment Market Adjustments, and Endowment Income Distribution.
This procedure will outline the following:
1.Formatting the Excel spreadsheet so that the F60 record layout is resembled.
2.Populating the correct fields with the correct SAP data within the spreadsheet.
3.Saving the spreadsheet file in the correct format so that it can be fed to SAP.
Procedural Steps
Open a new Excel spreadsheet and format it as follows:
a.Open a new spreadsheet within Excel.
b.Click the blank button above the row 1 button to select the entire sheet.
c.Change the font to Courier New 8.
d.With the entire sheet highlighted, click the Align Left button.
This ensures all the data is left justified within the cells.
e.With the sheet still highlighted, select Format > Cells.
Click the Number tab.
Select Text, then click OK.
This will format the cells to display exactly as the data is entered.
f.Format the width of columns A-M by clicking once on the column letter to highlight the column.
From there select Format > Column > Width.
Enter the width (number of characters) for the column, then click OK. Format columns A-M with the following widths:
A=1, B=3, C=29, D=6, E=6, F=6, G=10, H=11, I=6, J=11, K=50, L=36, M=1
Note: All of the columns for this file must be formatted even though only a few of them will actually contain data.
2.Save the spreadsheet as an Excel file (with a .xls extension).
This will serve as a template for entering journal entries.
3.Open the Excel template created in step 2.
To create the journal entry, populate the following columns in the spreadsheet with the data indicated:
B = F60
D = Business Area (needed if entry is a Business Area/GL entry only)
E = Cost Center
F = GL acct
G = Internal Order
I = Document date (‘YYMMDD format)—you must start the entry with the hyphen (‘) to obtain proper filling of zeros
J = Amount (whole number, no decimal, ex: enter $150.25 as 15025)
K = Line item text
M = D or C (debit/credit indicator)
Note: All data should be in uppercase.
4.Save the file with a name different from that of the template created in step 2. This file should be saved as a PRN file.
Proceed as follows:
a.Click File > Save As
b.Name the file, then select .prn file extension from within the Save As Type field.
c.Click Save.
d.Click OK on the window that appears.
e.When closing the file, click Yes on the window that appears.
The Save As window will re-appear. Click Save, then click Yes.
Click OK.
The file should now close.
5.The file can now be forwarded to Business & Finance Information Systems for testing and processing.
6.A sample spreadsheet template file prior to saving it as a .prn file is on the next page.
0 comments:
Post a Comment