Information Systems:Upload Budget from Spreadsheet - Revision history

Norwinu at 22:41, 2 March 2017

2017-03-02T22:41:21Z

Norwinu at 22:41, 2 March 2017

2017-03-02T22:41:05Z

New page

Obtain file from Nancy or Stephen. When you open it, notice the values in column B, rows 3 to 9. The upload program reads these, so make sure they are correct.

Open, then reformat period totals (columns J to U) to have no commas, and no decimal positions. Save as a comma delimited file, named VAbud_YY.csv where YY is the fiscal year. (Actually the only thing that matters is that the file name starts with VA and is in the format 8.3)

Go into System i Navigator / Bart / File Systems / Integrated File System / QDLS / PCBUDUPL. Drag and drop the CSV file. If the file won’t copy, then its name is not in the format 8.3. As this process will delete the file from this folder, make sure to keep a copy.

On a command line in ASW, key in GO BUDGET and press enter. Run option 9 - Budget Upload. This will run in batch. When complete, you will get a message like ‘Job 657271/SHEILAV/GBFC301 completed normally on 02/19/15 at 17:03:09’.

All file names in Pcbudupl are put into file GBOBUO. That file is then read and any file starting with the company code (VA) will be read and the records put into GBOBUF.

GBFR301 will validate them, and print an error report. If there are no errors found, there will be no report, and the file will be gone from PCBudupl.

When complete, the budget records are written to ANOBUD. Make sure this is correct by using [[QUERY|Query]] to get a total to compare to the spreadsheet.

This process does not update the summary files for Analyser – it must be done manually.

- Sign on as FINADMIN.

- On a command line in ASW, key in GO GLSET and press enter. Run option 2 Work with G/L control file.

- On the third screen, Change Summary ID part 4 to changed = Y. (This is the same as using [[Information Systems:DFU]] to go into the G/L Control File SROSTYSF (which only contains one record) and change the field ‘Sum. Id 4 changed’ from N to Y.)

- On a command line in ASW, key in GO GLPER and press enter. Run option 1 Rebuild summary identities. (If every summary identity is set to changed = N, this will not run.)

If the budget needs to be reloaded, use SQL to remove all records for the current year from ANOBUD.

STRSQL
delete from up1480bfva/anobud where buyear = 20??

Or change cell B7 to R for replace.

[[Category: Periodic Tasks]]

Norwinu: Text replacement - "DFU" to "Information Systems:DFU"

2016-06-17T22:22:55Z

Text replacement - "DFU" to "Information Systems:DFU"

Sheilav at 21:44, 20 November 2015

2015-11-20T21:44:36Z

Sheilav at 21:44, 20 November 2015

2015-11-20T21:44:11Z

Sheilav: Created page with "Obtain file from Nancy or Stephen. When you open it, notice the values in column B, rows 3 to 9. The upload program reads these, so make sure they are correct. Open, then r..."

2015-11-20T21:42:14Z

Created page with "Obtain file from Nancy or Stephen. When you open it, notice the values in column B, rows 3 to 9. The upload program reads these, so make sure they are correct. Open, then r..."

New page

Obtain file from Nancy or Stephen. When you open it, notice the values in column B, rows 3 to 9. The upload program reads these, so make sure they are correct.

Open, then reformat period totals (columns J to U) to have no commas, and no decimal positions. Save as a comma delimited file, named VAbud_YY.csv where YY is the fiscal year. (Actually the only thing that matters is that the file name starts with VA and is in the format 8.3)

Go into System i Navigator / Bart / File Systems / Integrated File System / QDLS / PCBUDUPL. Drag and drop the CSV file. If the file won’t copy, then its name is not in the format 8.3. As this process will delete the file from this folder, make sure to keep a copy.

On a command line in ASW, key in GO BUDGET and press enter. Run option 9 - Budget Upload. This will run in batch. When complete, you will get a message like ‘Job 657271/SHEILAV/GBFC301 completed normally on 02/19/15 at 17:03:09’.

All file names in Pcbudupl are put into file GBOBUO. That file is then read and any file starting with the company code (VA) will be read and the records put into GBOBUF.

GBFR301 will validate them, and print an error report. If there are no errors found, there will be no report, and the file will be gone from PCBudupl.

When complete, the budget records are written to ANOBUD. Make sure this is correct by using [[QUERY|Query]] to get a total to compare to the spreadsheet.

This process does not update the summary files for Analyser – it must be done manually.

- Sign on as FINADMIN.

- On a command line in ASW, key in GO GLSET and press enter. Run option 2 Work with G/L control file.

- On the third screen, Change Summary ID part 4 to changed = Y.
(This is the same as using DFU to go into the G/L Control File SROSTYSF (which only contains one record) and change the field ‘Sum. Id 4 changed’ from N to Y.)

- On a command line in ASW, key in GO GLPER and press enter. Run option 1 Rebuild summary identities. (If every summary identity is set to changed = N, this will not run.)

If the budget needs to be reloaded, use SQL to remove all records for the current year from ANOBUD.

STRSQL
delete from up1480bfva/anobud where buyear = 20??

Or change cell B7 to R for replace.

← Older revision		Revision as of 22:41, 2 March 2017
Line 30:		Line 30:
	Or change cell B7 to R for replace.		Or change cell B7 to R for replace.

−	[[Category: Periodic Tasks]]	+	[[Category: I.T. Periodic Tasks]]

@@ Line 19: / Line 19: @@
 - On a command line in ASW, key in  GO GLSET  and press enter.  Run option 2 Work with G/L control file.
-- On the third screen, Change Summary ID part 4 to changed = Y.  (This is the same as using [[DFU]] to go into the G/L Control File SROSTYSF (which only contains one record) and change the field ‘Sum. Id 4 changed’ from N to Y.)
+- On the third screen, Change Summary ID part 4 to changed = Y.  (This is the same as using [[Information Systems:DFU]] to go into the G/L Control File SROSTYSF (which only contains one record) and change the field ‘Sum. Id 4 changed’ from N to Y.)
 - On a command line in ASW, key in  GO GLPER  and press enter.  Run option 1 Rebuild summary identities.  (If every summary identity is set to changed = N, this will not run.)