Information Systems:Finance
Cannot Close Period
Check SROBTR and SROOVF. If an update does not complete properly, sometimes orphaned transactions are left behind.
For example, we have a custom system to receive transactions from the bank and post them to the store’s accounts receivable (see Bank EDI). Sometimes this leaves transactions in SROOVF. We can’t do anything but manually delete them, and do a journal entry.
- I can see only one place where this could be happening, but I haven’t figured out why. BERUP075 (BED -- Post A/R payment G/L corrections to ASW) calls GCFC851 (Interface accounting transfer), which calls GCFR851 (Interface accounting transaction transfer). If field &GRIW is returned as blank, FMC050 (G/L update transactions and balances) is not run.
- Nancy seems to have found the problem and a solution. We have changed the job queue QBATCH to run two jobs at a time. If Nancy waits until one card type (Visa, Master Card, etc) completes before starting the next, the problem doesn’t seem to happen.
A/R Reconciliation not in Balance
The first thing to find out is when it went out of balance. We have a copy of the A/R Reconciliation for every period end. To see them, on a command line key in WRKOUTQ FINANCE/FIN* and press enter. Page down through the older periods, until you get to the more recent ones.
Work with All Output Queues
Type options, press Enter.
2=Change 3=Hold 4=Delete 5=Work with 6=Release 8=Description
9=Work with Writers 14=Clear
Opt Queue Library Files Writer Status
__ FIN201505 FINANCE 11 RLS
__ FIN201506 FINANCE 11 RLS
__ FIN201507 FINANCE 11 RLS
__ FIN201508 FINANCE 11 RLS
__ FIN201509 FINANCE 11 RLS
__ FIN201510 FINANCE 11 RLS
__ FIN201511 FINANCE 11 RLS
5 FIN201512 FINANCE 11 RLS
__ FIN201601 FINANCE 11 RLS
__ FIN201602 FINANCE 11 RLS
Command
===>
F3=Exit F4=Prompt F5=Refresh F12=Cancel F24=More keys
Use option 5 to see the spool files.
Work with Output Queue
Queue: FIN201512 Library: FINANCE Status: RLS
Type options, press Enter.
1=Send 2=Change 3=Hold 4=Delete 5=Display 6=Release 7=Messages
8=Attributes 9=Work with printing status
Opt File User User Data Sts Pages Copies Form Type Pty
_ GCFP231 FINADMIN RDY 92 1 *STD 5
_ GCFP233 FINADMIN RDY 185 1 *STD 5
_ GCFP235 FINADMIN RDY 199 1 *STD 5
5 FSR297P FINADMIN RDY 2 1 *STD 5
_ FSR297P FINADMIN RDY 2 1 *STD 5
_ FKR041P FINADMIN RDY 2 1 *STD 5
_ QPQUPRFIL FINADMIN RDY 81 1 *STD 5
_ QPQUPRFIL FINADMIN RDY 10 1 *STD 5
_ DMR606P FINADMIN RDY 911 1 *STD 5
More...
Parameters for options 1, 2, 3 or command
===>
F3=Exit F11=View 2 F12=Cancel F20=Writers F22=Printers
F24=More keys
The first ‘FSR297P’ is the A/R reconciliation (the second is A/P). Use option 5 to display it. Do this on several periods until you find the first one that has a difference. In this case, it is 201501.
*VA/480B* A/R RECONCILIATION LIST
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
A/R AMOUNT A/R AMOUNT G/L AMOUNT G/L AMOUNT G/L AMOUNT DIFFERENCE
Main Grou Subg Temp intrupt(a) Balance amt (b) Temp intrupt(c) Balance amt (d) Journal Maint(e) Diff (a+b)-(c+d+e))
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
120000 4,452,168.26 4,452,160.94 7.32
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Grand total 4,452,168.26 4,452,160.94 7.32
Follow the instructions in ‘ASW Analyser / Rebuild / Rebuild System Base Levels’. This should be done on a Saturday, as it is not a good idea to rebuild Analyser while transactions are trying to update it.
Cannot Use A/P Payment Proposal
If you get a message saying that a previous step is still processing, and you know that it isn’t (make sure it isn’t in the job queue waiting for its turn to run, or in error while running) –
Using instructions in add link to DFU Technical / DFU – Data File Utility, edit file SROCTLSO (A/P Payment proposals control) in library UP1480BFVA, to clear the WSID field. There will probably only be one record in the file.
Journal in Error
Sometimes a journal does not automatically post to the G/L. It could be because something is wrong with an individual entry, or because something in it does not balance (to zero). You can tell which, by going into ‘work with journals in error’, and looking at the journal.
*VA/480B* Journal maintenance 1/22/15 09:22:36 FSR11001
Select journal
-------------------------------------------------------------------------------
Year Journal Transaction type User Date
5 15 8953 A/P batch invoices EDIUSER 1/21/15
------------------------------------------------------------------------------
Year Journal User
__ _____ __________
Select the journal to see the transactions.
*VA/480B* Journal maintenance - A/P invoices 1/22/15 09:23:57 FSR12101
Invalid transactions
-------------------------------------------------------------------------------
Accounting year..... 15 Journal number...... 8953
-------------------------------------------------------------------------------
Dt Doc no Supplier no Name Amount
5 EIN 66442 21948 MERCK CANADA INC. *
-------------------------------------------------------------------------------
F8=Valid transactions
If there are no ‘invalid transactions’, the journal is out of balance (see the section ‘Out of Balance’ to correct this). If there is, select one to see, and correct, the details.
*VA/480B* Journal maintenance - A/P invoices 1/22/15 09:25:49 FSR12104
-------------------------------------------------------------------------------
Accounting year..... 15 Journal number...... 8953
-------------------------------------------------------------------------------
Supplier number..... 21948 Supplier invoice no. 7178018779
Document type....... EIN Voucher type........ 33
Document number..... 66442 Voucher number...... 3302554
Document date....... 012115 Voucher date........ 012115
Due date............ Accounting period... 1512
Transaction amount.. 4869.97 Transaction currency CAD
System amount....... _______________
Primary amount...... Primary currency.... CAD
VAT amount.......... 14.470- Turnover amount..... ____________
Exchange rate period M
Exchange rate level. M Cost centre......... _______
Exchange rate time.. 001501 Import code......... 1
Terms of payment.... Supplier bank....... ___
Bank reference no... _______________________________
Text................ __________________________
Supplier inv date... 012115 Reception date...... 012215
Attestor............ NO ATTESTO Approved for payment N
Payment mode........ Payment stop code... __
VAT must have same sign as transaction if entered
This is a problem with the EDI data received (document type EIN is an EDI invoice). The correction is to take the minus sign off of the VAT (European terminology for tax). Then go to the section ‘Manually Post Batch’)’
Out of Balance
If there are no invalid transactions, press F3 to see the totals.
*VA/480B* Journal maintenance - G/L postings 8/20/15 08:39:53 FSR11105
-------------------------------------------------------------------------------
Accounting year..... 16 Journal number...... 5166
Journal date........ 8/19/15
-------------------------------------------------------------------------------
Per Vt Vou no Debit Credit Difference
1607 80 8000167 1644464.71 1644469.71 5.00-
-------------------------------------------------------------------------------
Forced update....... N
So far, we have had two different reasons for a journal to be out of balance; when the tax status has been changed, or when processing credit card transactions has failed.
Sales Tax
Base ASW (at least the version modified for the US) has different levels of tax (state, county, etc) but they all add to one bucket – both when they print on the invoice, and add to the G/L. We need to keep provincial and federal taxes separate. We have modified DMR343 (invoice generation) to recalculate the individual sales taxes. Very occasionally, when it does this, it does not agree with total tax amount previously calculated by ASW. Which means the detail taxes that print on the bottom of the invoice, and the journal entries created, do not add up to the invoice total. This happens so rarely, and the program is so complex, that I have been fixing the symptoms rather than fixing the source of the problem.
To find out which invoice has the problem, go to System Management / Query Manager / Run a Query Request. Run FINJERROR.
Display Spooled File
File . . . . . : QPQUPRFIL Page/Line 1/24
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+.
QUERY NAME . . . . . JOURNERR2
LIBRARY NAME . . . . XX2480BP
FILE LIBRARY MEMBER FORMAT
JETOTAL QTEMP JETOTAL JETOTAL
DATE . . . . . . . . 12/02/14
TIME . . . . . . . . 12:56:40
show out of balance document 12/02/14 12:56:40 PAGE 1
Docu Doc no Amount
type
INV 7711150 5.00-
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
You can reprint this invoice to see what it’s done. Typically, the modifications have calculated a tax when base ASW didn’t. To fix it, use DFU on file UZIVLIL3 in library UP1480BFVA.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : UZIVLIR File . . . . : UZIVLIL3
IVINV#: 7711150 IVINVL: _____
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
Key in the invoice number, and press enter. Press page down until you see the incorrect tax amount.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : UZIVLIR File . . . . : UZIVLIL3
IVINV#: 7711150 IVINVL: 1
IVUNI#: 2286388 IVPLNO: _________
IVORNO: 3230094 IVORLN: 10
IVIDAT: 20141201 IVDUED: 20141225
IVTYPP: 1 IVINYR: 2014
IVTRMS: Z80 IVTRSO: ORD
IVITEM: 02002657
IVTAX1: 5000 IVTAX2: _______________
IVTAX3: IVTAX4: _______________
IVTAX5: IVDIAM: _______________
IVDIPC: UZADBY: ANGELAC
UZADCE: 20 UZADYR: 14
UZADMO: 12 UZADDY: 1
UZADTI: 84423 UZUPBY: _______
UZUPCE: UZUPYR: __
UZUPMO: UZUPDY: __
UZUPTI: ______
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
Remove the sales tax, and press enter to update the file. You can reprint the invoice, and it will be correct.
But the journal entries created are still out of balance. To know how to fix them, you will have to see what was generated.
On an ASW command line, key in WRKQRY and press enter.
Work with Queries
Type choices, press Enter.
Option . . . . . . 1 1=Create, 2=Change, 3=Copy, 4=Delete
5=Display, 6=Print definition
8=Run in batch, 9=Run
Query . . . . . . . Name, F4 for list
Library . . . . . QTEMP Name, *LIBL, F4 for list
F3=Exit F4=Prompt F5=Refresh F12=Cancel
Key in option 1, and press enter.
Define the Query
Query . . . . . . : Option . . . . . : CREATE
Library . . . . : QTEMP CCSID . . . . . . : 65535
Type options, press Enter. Press F21 to select all.
1=Select
Opt Query Definition Option
1 Specify file selections
_ Define result fields
1 Select and sequence fields
1 Select records
_ Select sort fields
_ Select collating sequence
_ Specify report column formatting
_ Select report summary functions
_ Define report breaks
_ Select output type and output form
_ Specify processing options
F3=Exit F5=Report F12=Cancel
F13=Layout F18=Files F21=Select all
Select these three options, and press enter.
Specify File Selections
Type choices, press Enter. Press F9 to specify an additional
file selection.
File . . . . . . . . . sroovf Name, F4 for list
Library . . . . . . UP1480BFVA Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list
F3=Exit F4=Prompt F5=Report F9=Add file
F12=Cancel F13=Layout F24=More keys
Key in file SROOVF and library UP1480BFVA, then press enter twice.
Select and Sequence Fields
Type sequence number (0-9999) for the names of up to 500 fields to
appear in the report, press Enter.
Seq Field Text Len Dec
1 OVDIM1 Account part 1 20
1 OVDOTY Document type 3
1 OVIDNO Document number 7 0
1 OVDODT Document date 8 0
1 OVLAMO Amount system currency 17 3
1 OVTEXT Description 30
OVGRIW Group id/work station 10
OVTYP1 Type 1 1
OVTYP2 Type 2 1
OVSTAT Status 1
OVDIM2 Account part 2 20
OVDIM3 Account part 3 20
More...
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F20=Renumber F21=Select all F24=More keys
Select these six fields, and press enter twice.
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, 'Characters', or ...)
OVIDNO EQ 7711150________________________
____ _______________ _____ _______________________________
____ _______________ _____ _______________________________
____ _______________ _____ _______________________________
_____________________________________________________________________________
Field Text Len Dec
OVDIM1 Account part 1 20
OVDOTY Document type 3
OVIDNO Document number 7 0
OVDODT Document date 8 0
OVAMTR Amount transaction currency 17 3
More...
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
Key in this ‘field’ and ‘test’, and the invoice number. Press F5 to see the report.
Display Report
Report width . . . . . : 98
Position to line . . . . . Shift to column . . . . . .
Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...
Account part 1 Docu Doc no Doc Am trans curr Description
type date
000001 122000 INV 7711150 20141201 5.000- Gross sales 840
000002 210700 INV 7711150 20141201 7.000- Gross sales 840
000003 900000 INV 7711150 20141201 100.000- Gross sales 840
000004 122500 INV 7711150 20141201 5.000- GST payable 960
000005 120000 INV 7711150 20141201 112.000 NANOOSE MEDICINE CT INV7711150
****** ******** End of report ********
The line for 'GST payable' is the one that should not have been generated. This example is for an invoice that accounting did; the PST and GST were done manually using fictitious items. They should not have been generated automatically.
*VA/480B* Invoice enquiry 2/10/15 11:35:07 DMR12106
-------------------------------------------------------------------------------
Invoice number...... 7711150 Date................ 12/01/14
-------------------------------------------------------------------------------
Invoice customer.... 11305 Customer............ 11305
Name.... NANOOSE MEDICINE CENTRE Del addr NANOOSE MEDICINE CENTRE
Order number........ 3230094 Order date.......... 12/01/14
-------------------------------------------------------------------------------
Line Wh Item/Unit Qty S.unit Del dat Price P.unt Amount
1 MAI 02002657 500 FLYERS (MC) - DEC/15
1 EA 12/01/14 100.00 EA 100.00
2 MAI 02081263 GST
1 EA 12/01/14 5.00 EA 5.00
3 MAI 02000065 PST
1 EA 12/01/14 7.00 EA 7.00
F6=View controls F7=Create invoice copy
Do a forced update of this journal (see the section ‘Manually Post Batch), which will bring it into balance by adding a 5.00 debit to account 730200 ‘Out of Balance’.
Then you will need to do a manual entry to correct this.
Debit 5.00 122500 GST - collected Credit 5.00 730200 Out of Balance
Debit / Credit Card Transactions
Transactions through the debit / credit card machines in our shareholder stores come to us; we then apply them against the stores’ accounts receivable.
See ‘Bank EDI / Bank EDI Does Not Post / To Post Manually’ for an explanation of what happened.
The user will get the message –
Work with Messages
System: BART
Messages in: NANCYN
Type options below, then press Enter.
5=Display details and reply
Opt Message
From . . : NANCYN 04/14/15 10:25:44
No error was found in A/R payment journal 1844 (year 16), so update will
proceed
From . . : NANCYN 04/14/15 10:24:59
G/L transaction journal 1845 (year 16) is in error, no update will be
performed
From . . : NANCYN 04/14/15 10:24:57
Job 965499/NANCYN/AR_PAYMBAT completed normally on 04/14/15 at 10:24:07.
No error was found in G/L transaction journal 1843 (year 16), so update
will proceed
From . . : NANCYN 04/14/15 10:24:07
No error was found in A/R payment journal 1843 (year 16), so update will
More...
F1=Help F3=Exit F5=Refresh F12=Cancel F17=Top F18=Bottom
F21=Select assistance level F22=Display list details
To find out which voucher has the problem, go to System Management / Query Manager / Run a Query Request. Run FINJERROR.
Display Spooled File
File . . . . . : QPQUPRFIL Page/Line 1/24
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+.
QUERY NAME . . . . . JOURNERR2
LIBRARY NAME . . . . XX2480BP
FILE LIBRARY MEMBER FORMAT
JETOTAL QTEMP JETOTAL JETOTAL
DATE . . . . . . . . 04/14/15
TIME . . . . . . . . 14:19:20
show out of balance document
04/14/15 14:19:20 PAGE 1
Docu Doc no Amount
type
GLC 7500250 2796.94-
* * * E N D O F R E P O R T * * *
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
Do the same query on SROOVF that is in the section ‘Out of Balance – Sales Tax’, except add a sort by description OVTEXT).
Display Report
Report width . . . . . : 98
Position to line . . . . . Shift to column . . . . . .
Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...
Account part 1 Docu Doc no Doc Am trans curr Description
type date
000001 100100 GLC 7500250 20150414 2,021.680 Account correction: AMX
000002 730200 GLC 7500250 20150410 -2,796.940 Account correction: AMX
000003 730200 GLC 7500250 20150414 -2,021.680 Account correction: AMX
000004 100400 GLC 7500250 20150410 56,141.720 Account correction: DBT
000005 100400 GLC 7500250 20150414 61,011.190 Account correction: DBT
000006 730200 GLC 7500250 20150410 -56,141.720 Account correction: DBT
000007 730200 GLC 7500250 20150414 -61,011.190 Account correction: DBT
000008 100400 GLC 7500250 20150410 34,967.920 Account correction: MC
000009 100400 GLC 7500250 20150414 90,784.410 Account correction: MC
000010 730200 GLC 7500250 20150410 -34,967.920 Account correction: MC
000011 730200 GLC 7500250 20150414 -90,784.410 Account correction: MC
000012 100400 GLC 7500250 20150410 76,631.900 Account correction: VIS
000013 100400 GLC 7500250 20150414 160,622.210 Account correction: VIS
000014 730200 GLC 7500250 20150410 -76,631.900 Account correction: VIS
000014 730200 GLC 7500250 20150414 -160,622.210 Account correction: VIS
You can see by the document dates that this includes two sets of bank EDI transactions; one on Apr 10, and the other on Apr 14. For each date, each card type has a pair of transactions; debit the bank (100100 or 100400), and credit the out of balance account (730200; in this case used for temporary postings). You can see that the second line does not have the corresponding debit to the bank.
Force the out of balance journal to post – not as FINADMIN – to put the out of balance amount to 730200. (See Finance#Manually Post Batch for instructions.) Then manually transfer from 730200 to the correct bank.
Entry Generated Incorrectly
G/L entries are automatically generated by sales, receiving, and transactions in the warehouse. To see how this is done, look at the section ‘Generated Journal Entries’.
*VA/480B* Journal maintenance - G/L postings 2/10/15 11:40:01 FSR11104
-------------------------------------------------------------------------------
Accounting year..... 16 Journal number...... 185
-------------------------------------------------------------------------------
Main Grou Subg Cost Pro
______ ____ ____ ____ ___
Accounting period... 1601 Translation period..
Voucher date........ 020615 Document date....... 020515
Voucher type........ 83 Document type....... GLB
Voucher number...... 8300004 Document number..... 8183924
Transaction amount.. 21.42
Quantity............ 1.000
Quantity code.......
Transaction currency CAD
System amount....... 21.42
VAT amount..........
VAT am trans curr...
Text................ Stk val fict.item 903
VAT handling code... USST
System identity..... G/L
Account for Main must be entered +
This is from PO 8183924; so find a line on it for 21.42.
*VA/480B* Purchase order enquiry 2/10/15 11:46:14 DMR11904
Order lines
-------------------------------------------------------------------------------
Supplier.... 24277 AURO PHARMA Order value.
Order type.. BE Price Variance - charge t Ord val CAD
Order number 8183924
-------------------------------------------------------------------------------
Line Whs Item/Unit Quantity Disp dat Price P.unt Discount Sts
10 RET 02396737 AURO-GABAPENTIN 300MG CAPSULES
EA 5.00 02/04/15 25.30 60
20 RET 02396711 AURO-CYCLOBENZAPRINE 10MG TABS
EA 2.00 02/04/15 37.27 60
40 RET 02362598 AURO-MIRTAZAPINE OD 15MG TABS
EA 5.00 02/04/15 2.93 60
50 RET 02362606 AURO-MIRTAZAPINE OD 30MG TABS
EA 5.00 02/04/15 7.44 60
60 RET 02362614 AURO-MIRTAZAPINE OD 45MG TABS
EA 20.00 02/04/15 8.78 60
70 RET 02001832 VENDOR DISCOUNT
EA 1- 02/04/15 21.42 60
F6=View Controls F7=Print F8=Addresses F9=Header
Transaction types 903 (stock value fictitious item) uses the account group to build the journal entry. Most of them have been left blank, so that the G/L numbers can be manually keyed into the journal. This is easier, as the user can decide exactly where they want the entry to go; instead of having to configure every possible entry in Transaction Types, and the user knowing all of them.
Normally, Nancy has to correct this. If she is unavailable, key account number 210100 into ‘Main’, and manually post the batch. Provide the details to Nancy, so she can adjust it when she can. Do not leave it until she is available, as it will delay the update of the G/L even more.
Manually Post batch
Very important – if you are posting a journal that is out of balance, do NOT sign in as FINADMIN! That is a special profile that will NOT create a balancing entry first – it will post the journal as out of balance, and will put the G/L out of balance. Only do this if you are trying to correct the G/L when it is already out of balance!
Look at the details of the journal that is in error. There should be no invalid transactions.
*VA/480B* Journal maintenance - A/P invoices 1/22/15 09:42:31 FSR12101
Invalid transactions
-------------------------------------------------------------------------------
Accounting year..... 15 Journal number...... 8953
-------------------------------------------------------------------------------
Dt Doc no Supplier no Name Amount
-------------------------------------------------------------------------------
F8=Valid transactions
Press F3.
*VA/480B* Journal maintenance 1/22/15 09:45:14 FSR11004
Update prompt
-------------------------------------------------------------------------------
Year/Journal number. 15 8953 A/P batch invoices
-------------------------------------------------------------------------------
Printer queue....... PRT01
Number of copies.... 1
Hold on spool file.. N
F12=Journal selection
Press enter to update. Go back to a command line, and key in DSPMSG and press enter. When the update has completed, you will see something like -
Work with Messages
System: BART
Messages for: SHEILAV
Type options below, then press Enter.
5=Display details and reply
Opt Message
Messages needing a reply
(No messages available)
Messages not needing a reply
_ Job 494208/SHEILAV/AP_INV_BAT completed normally on 01/22/15 at
09:46:28.
_ No error was found in G/L transaction journal 8953 (year 15), so update
will proceed
From . . : SHEILAV 01/22/15 09:46:28
_ No error was found in A/P invoice journal 8953 (year 15), so update will
proceed
From . . : SHEILAV 01/22/15 09:46:28
Bottom
F1=Help F3=Exit F5=Refresh F6=Display system operator messages
F12=Cancel F17=Top F18=Bottom F24=More keys
This shows that the post has completed properly. If you go back into ‘work with journals in error’ there will be nothing there.
G/L Balances do Not Roll Over
From the scheduler, job ENDOFMONTH (program EOM) runs at 3:15AM on the first of every month. It calls UNIPHARMIT / EOMASW, which does a period roll over for every company. This is the command which retrieves the parameters, and the command that runs the roll over program. (Note that PAID is ‘parameter ID’.)
RTVASWPAR PGM(ASGC032) PAID(MONTHROLL) USER(*ALL)
EXCASWPGM PGM(ASGC032) PARM(Y) DTA('N NNY *DEVTYPE N')
To see the parameters, find this record in file SROLPA -
Program User id Parameter Description LPUSR1
ID
ASGC032 *ALL MONTHROLL Roll all companies ACCFICIUI1I2I3PLUCVA
To see the effects of this, on a command line key in GO GLSET and press enter. Select option 2 – Work with G/L control file.
*CF/480B* G/L control file maintenance 3/20/15 09:03:48 GDMD0911
-------------------------------------------------------------------------------
Period name End date
Actual year/period.. 1602 FEBRUARY 022815
MARCH 033115
Last closed period.. 1513 APRIL 043015
First stored period. 0508 MAY 053115
JUNE 063015
JULY 073115
AUGUST 083115
SEPTEMBER 093015
OCTOBER 103115
NOVEMBER 113015
No of normal periods 12 DECEMBER 123115
Total no of periods. 13 JANUARY 013116
YEAR END 043016
‘Actual year / period’ is moved forward every time a period roll over is performed.
Although when you are in G/L Transaction Inquiry, it might look like the opening balances for the year have not been created, it may be that the Analyser summary levels are incorrect.
A quick way of testing this, is to use query to look at the opening balances in Analyser. (See Utilities#QUERY for detailed instructions.)
Read files ANOSTK (keys) and ANOBOB (opening balances) matched on KESUTA eq BOSUTA, and KEREFK eq BOREFK.
Define a result field of BODAOB + BOCAOB.
Select records where BOYEAR eq the current fiscal year, and KEMK01 EQ some G/L account, for example ‘100200’ (CIBC – Operating Line).
When you run this query, you should see something like –
Display Report
Report width . . . . . : 917
Position to line . . . . . Shift to column . . . . . .
Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7..
BAL Lvl Key
cod 01
000001 497.40- 008 100200
000002 497.40- 002 100200
000003 497.40- 013 100200
000004 497.40- 006 100200
000005 497.40- 003 100200
000006 497.40- 004 100200
000007 497.40- 007 100200
000008 497.40- 009 100200
000009 497.40- 001 100200
000010 497.40- 014 100200
000011 497.40- 005 100200
000012 63,951.4000 A11 100200
000013 63,951.4000 A12 100200
****** ******** End of report ********
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
G/L Transaction Inquiry shows the balance for this account at 201512 as being 63951.50, which means the amounts showing for A11 and A12 (system base levels) are correct , and all the summary levels are wrong. This means they have to be rebuilt. For instructions, see the section ASW Analyser / Rebuild Custom Summary Level.
If you run the same query after the rebuilds, all the amounts should all be the same.
Display Report
Report width . . . . . : 917
Position to line . . . . . Shift to column . . . . . .
Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7..
BAL Lvl Key
cod 01
000001 63,951.4000 008 100200
000002 63,951.4000 002 100200
000003 63,951.4000 013 100200
000004 63,951.4000 006 100200
000005 63,951.4000 003 100200
000006 63,951.4000 004 100200
000007 63,951.4000 007 100200
000008 63,951.4000 009 100200
000009 63,951.4000 001 100200
000010 63,951.4000 014 100200
000011 63,951.4000 005 100200
000012 63,951.4000 A11 100200
000013 63,951.4000 A12 100200
****** ******** End of report ********
F3=Exit F12=Cancel F19=Left F20=Right F21=Split F22=Width 80
Cash Book not In Balance
It can be out of balance in two ways; the ‘open’ amount not equal to the total of the open transactions, or the ‘balance’ not equal to the bank balance in the G/L.
Open Amount not In Balance
*VA/480B* Work with Cash Book reconciliation 8/13/08 15:58:08 FSR46001
Open transactions
------------------------------------------------------------------------------
Bank: 002 CIBC - Chequing Account (US) Open...... 10429.60-
Reconciled 106436.62
Balance... 96007.02
-------------------------------------------------------------------------------
2=Change 5=Display 6=Related documents 7=Connected entries
8=Reconcile 9=Unreconcile 10=G/L transactions
Bank Dt Doc date Rscd Transaction ref. Open amount Curr B
002 CHQ 07/28/08 AP 235 969.60- USD
002 CHQ 07/30/08 AP 236 3500.00- USD
002 CHQ 08/06/08 AP 237 5960.00- USD
Bottom
Bank Dt Doc date Rscd Transaction ref. Open amount Open
002 Y
F1=Info F8=Move cursor
In this case, the total of the open transactions does agree with the open amount in the header.
On a command line in ASW, key in GO CBACC and press enter. Select the option ‘Rebuild Cash Book Bank balances from transactions’.
Cash Book Not Equal to G/L
Use a query to find each period's total from the cash book transaction file, and compare each to the G/L.
- File name is SROCBA
- Select CABKCO as the bank number, and CACTTP as ‘ 1’ or ‘ 4’ (note those leading spaces)
- Sort on CAPERI accounting period
- Accumulate total of CASCTR bank amount in system currency
- Break on change in CAPERI accounting period
In ASW, go into G/L Transaction Enquiry.
- For the bank account, key each period into both the from and to period, and press enter to display those transactions
- Press F10 to display the total of the selected transactions.
- Compare each period to the query report.
- For the opening balance, select a period range from blank to one before the first period listed in the query report.
- Nancy can adjust the opening balance of the cash book (so far, that is what has always been incorrect).
Cannot Close Cash Book Batch
On a command line in ASW, key in GO CASHBOOK and press enter. Select option ‘Work with Cash Book reconciliation’. Key in bank number, and page down until you get to the transaction.
*VA/480B* Work with Cash Book reconciliation 4/07/15 12:18:25 FSR46001
Open transactions
-------------------------------------------------------------------------------
Bank: 001 CIBC - Chequing Account (Cdn) Open...... 1969626.82-
Reconciled 116652750.67
Balance... 114683123.85
-------------------------------------------------------------------------------
2=Change 5=Display 6=Related documents 7=Connected entries
8=Reconcile 9=Unreconcile 10=G/L transactions
Bank Dt Doc date Rscd Transaction ref. Open amount Curr B
__ 001 CHQ 04/07/15 AP 70561 784.44- CAD
__ 001 CHQ 04/07/15 AP 70562 18900.79- CAD
__ 001 CHQ 04/07/15 AP 70563 1856.29- CAD
__ 001 EFT 03/02/15 AR05 201601P2 00000 425716.44- CAD *
__ 001 EFT 04/02/15 AP 8318745 01587 7396.53- CAD
__ 001 EFT 04/02/15 AP 8318746 01587 3158.45- CAD
__ 001 EFT 04/02/15 AP 8318747 01587 1194.96- CAD
__ 001 EFT 04/02/15 AP 8318748 01587 1101.74- CAD
More...
Bank Dt Doc date Rscd Transaction ref. Open amount Open
001 ___ ______ ____ _________________________________ ____________ Y
F1=Info F8=Move cursor
The asterisk on the right means that the batch is open – the program determines this because the voucher fields on this transaction are blank. In this case, there is another problem. This transaction was created by ARREFT60, which reads the EFT work file created from A/R statements, totals it, and gets the journal and voucher information from the A/R transaction file.
In this case, that information was not available in SRODTA while ARREFT60 was running. (I don’t know why). So get it from there now. In A/R transaction inquiry, lookup the document type and document date of the cash book transaction.
*VA/480B* A/R transactions and balance enquiry 4/07/15 13:54:59 FKR01610
-------------------------------------------------------------------------------
Sequence A/D
Debtor number....... __ _
_
Document type....... __ _
Document number..... __ _
Document date....... 1 D
Due date............ __ _
Transaction currency __ _
Selection
Debtor number....... ___________
Document type....... 1 EFT ___ ___ ___ ___ ___
Document number..... _ _______ _______ _______ _______ _______ _______
Document date....... 1 030215 ______ ______ ______ ______ ______
Due date............ _ ______ ______ ______ ______ ______ ______
A/R group........... _ __ __ __ __ __ __
Transaction currency _ ____ ____ ____ ____ ____ ____
Transaction amount.. _________________ - _________________
Settled items....... Y Open items.......... Y
F2=Print F6=Customer F7=Balances F8=Agreements F9=Statistics F10=Template
Look at the detail of any of the transactions shown.
*VA/480B* A/R transactions and balance enquiry 4/07/15 14:01:48 FKR01607
-------------------------------------------------------------------------------
Debtor number....... 10136 CATES MEDICINE CTR Cred limit 300000
-------------------------------------------------------------------------------
Document type....... EFT Voucher type........ 27
Document number..... 8317261 Voucher number...... 2700012
Document date....... 03/02/15 Voucher date........ 03/02/15
Due date............ 03/02/15 Period.............. 1602
Year/Journal number. 16 / 743 Batch number........
Text................ Statement Period 201601P2
Transaction currency CAD Primary currency.... CAD
Original amount..... 15695.06 Original amount..... 15695.06
Remaining amount.... Remaining amount....
Exchange rate....... 1.0000000 Exch rate period.... M 1503
A/R group........... 1
Interest stop code.. Payment stop code...
Reminder stop code.. Payment mode........
User................ NANCYN Last maint date.....
F2=G/L transactions info F6=Customer F7=Balances F8=Agreements
F9=Statistics F10=Text
Use DFU on file SR6CBA to add journal number, voucher type, voucher number, and voucher type to the cash book transaction.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : CBA File . . . . : SR6CBA
Bank company: 001
Transaction reference: 201601P2
Ref no: 23763324
Cash Book Data type: 1
Stat: Y
NCR acc ref: 3
Syst id: 2
Statement number:
Batch no:
Journal no: 743
Acc per: 201602
Doc date: 20150302
Docu type: EFT
Doc no: 8317280
Value date: 20150302
Vt: 27
Vou no: 2700012
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
Press enter to see the rest of the record.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : CBA File . . . . : SR6CBA
Vou dte: 20150302
Transaction text: EFT from A/R Statements
Bank Currency: CAD
Bank Amount: 425716440-
Bank Amount Sys curr: 425716440-
Bank Open amount: 425716440-
Bank Open amount sys curr: 425716440-
Exch rate per: 3
Exch rate lvl: M
Exch rate time: 201503
Exch rate trans: 10000000
Cash Book Reason Code: AR05
Crt dte: 20150302
Time: 104751
User id: NANCYN
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
Change Document Date
Depending on the type of transaction, use DFU on –
SR6CBA – Cash Book SR14DTA – Accounts Receivable SR14LTA – Accounts Payable
Rounding Error in Goods Receiving File
A small amount, due to rounding, can be left in SROGRT but the complete quantity has already been matched. The only I know of to fix this, is to clear it with DFU (use file SR15GRT).
Unmatched Purchase Orders Not in Balance
Unmatched purchase orders are in SROGRT, with a remaining amount. As part of month end, a query report is generated of this file, with sub totals by purchase order type. These sub totals can be compared to the corresponding G/L accounts to make sure they are in balance.
G/L account Purchase order type2 210050 MO Manual Order 210300 RT Return to Vendor 210500 CS Car Stock 210625 BE Price Variance – charge to H/O 210625 PV Price Variance – charge to Rep 210625 QV Quantity Variance 210675 PC Prairie Supply Co-op Credit (no longer used) 210675 PS Prairie Supply Co-op (no longer used) 220000 PE EDI Purchase Order 220000 PO Regular Purchase Order 220000 RO Rush Order 220000 XF Reference Purchase Order 220050 RE Receiving Error
If they are not in balance, the problem could be a timing issue. Receiving or invoicing can be done after the period end – which is after the report has been printed.
(Note that these examples are for regular PO types, that post to 220000.)
To check for this, run a query on file SROLOGGL, looking for any transactions for the previous period (or earlier), that were entered after the unmatched PO query report was run. Note that two checks have to be done. One for the day following, after 6:29AM – which is after the end of day processing has completed; and the other for any time after.
LGDIM1 EQ '220000' AND LGYYPP LE 201312 AND LGCDAT GT 20130201 OR LGDIM1 EQ '220000' AND LGYYPP LE 201312 AND LGCDAT EQ 20130201 AND LGCRTI GT 062900
This example looks for any transactions for the period just ended, that were entered after the report was printed.
Total LGSAMO, and compare to the difference.
Also look the other way; transactions done before the period end, but posted to a later period.
If it does not match, more digging has to be done. Run these queries (from library XX2480BP) to build a file of all purchase orders for which the total posted to the G/L does not agree with the remaining amount on SROGRT.
SROGRT is a balance file; not a transaction file, so you must restore it from the period end tape.
UNMATB02 – Extract purchase order transactions by document number.
Change this to include G/L transaction up the closing period.
UNMATB03 – Extract purchase order transactions by the PO number in the text
Change this to include G/L transaction up the closing period. The PO in the text can be different than the transaction number, if PO’s are matched against each other.
UNMATB04 – Summarized extracted G/L transactions by PO and G/L number
UNMATB05 – Build work file to use in join
UNMATB06A to UNMATM06F – Build separate file for each G/L number
UNMATB07 – Merge all these separate file together into one file, with separate columns for each G/L number
UNMATB08 – Summarized received and remaining amounts on SROGRT (goods receiving) by PO
Change this to read the version of SROGRT you have restored.
UNMATB09 – Build a version of the PO header file with a character PO number field
This is needed because that is what the PO number extracted from the G/L text field is.
UNMATB10 – Consolidate all files
Run RPG program XX2480BP/UNMATPO to add to this file, any PO numbers that have been matched together.
Download SHEILA2/ POCONS2 to Excel. The total difference between the G/L (SROLOGGL) and the unmatched PO (SROGRT) should match the amount you are looking for.
- taking into account that you will have to manually remove transactions done after the unmatched PO report was generated.
Note – Since we went live, there have been about 1304 unmatched PO’s.
182 Received before go live; invoiced after
1068 Debit Notes that no longer go into 220000. They are out
of balance by the fictitious item used to charge tax.
495 RT
436 QV
105 PV
32 BE
1073 records for a total of -30324.33
22 Adjustments
Which leaves only about 32 PO’s that didn’t balance. Five times the receiving transactions went into the G/L twice. Six times invoicing did both a debit and a credit to 220000. Seven times there was a fictitious item on the PO (some should have been type MO). For the rest, the invoice amount didn’t agree with the amount received. The last time that happened was November 2006, so perhaps processes have been changed?
Once there is a known point at which this is in balance, you only have to investigate things after that date. ** When you have the unmatched purchase orders in balance, update the last in balance date, so that you will have fewer PO’s to manually balance next year. **
In balance at 2013Feb01.
Balanced at 2013Dec31, but corrections for system outage on Dec 16 were put into 201412, as 201411 was closed. See unmatched PO 201411.xlsx for details
Problem with EFT Batch
Accounts Payable - RPG program EFRUP011 reads records from ASW payment proposal files and creates CIBC EFT interface transactions.
Accounts Receivable – RPG program ARREFT40 is called from ‘View Statement Control Periods’ to read records created by the statement run and create CIBC EFT interface transactions.
These transactions are sent to CIBC via their Business Banking web site.
1 01000 01000332370152291386 0010009001016512 uniPHARM CAD 5 7001386 08/17015237 6C 0002120700037311 000147216812293/1607P1 BALANCED HEALTH INTEGR 6C 00048024008600426726 000081617610164/1607P1 CLINIC DRUG STORE 6C 00049352009700646887 000060041710292/1607P1 FRONT STREET PHARMACY 6C 0003023201050798 000025661411286/1607P1 GLOVER'S MEDICINE CENT 6C 0004918605209049 000024437312305/1607P1 LONGEVITY COMPOUNDING 6C 0001098901036867 000016364010699/1607P1 PEOPLES DRUG MART #175 6C 001610690201834001 000000514411706/1607P1 PHARMASAVE #027 6C 0002920800006211 000077974312169/1607P1 PRESCRIPTION HEALTH ST 6C 0010029208821216 000267932611044/1607P1 SUNNYSIDE EVERGREEN PH 6C 0003028801056639 000099383111306/1607P1 SURLANG MEDICINE CENTR 6C 0001098901027581 000035411310684/1607P1 TOFINO PHARMACY 6C 0001321701006913 000195974411105/1607P1 VICTORIA COMPOUNDING P 6C 0001083801029021 000080552711112/1607P1 VITA VIE PHARMACY 6C 0010005209602011 000292013711124/1607P1 WEST END MEDICINE CENT 6C 0002913890015113 000277018711566/1607P1 WESTWINDS PHARMACY LTD 77000000150000000000 000016821140 9000001000015
Batch number (1386 in first and second record) – This sequential number ensures that no batches are missing, or posted twice.
Date (08/17, 015237 in second record) – Format is month / day, then Julian.
Transaction Amount (first 10 digits in second block of numbers), D/C for debit or credit (second character of detail record) - The amount on the trailer must be equal to the total of the transactions.
If Nancy realises, after the file has been sent, that is something wrong, she can delete it from the bank, edit it on her PC, and resend it. But she must increment the sequential number in the file, then I.T. must also increment the control file. To do that, go to Unity / Start uniPharm Extensions / VA Company / Work with UWD IT Tools / FTP Connections.
To change the number for A/P, select ‘A/P FTP Configuration’. Press page down to see the record.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : EFCONFR File . . . . : EFCONFP
*RECNBR: 1 Next Create No: 2173
CIBC Originator #: 0100043801 EFT Tx Type: 460
Originator Short Name: uniPHARM
Originator Long Name: uniPHARM Wholesale Drugs Ltd.
Sett Inst ID: 010 Sett Branch Transit: 16512
Settlement Account: 0090010
CIBC EFT Data Folder: EFT____________________________________________________
OMS footer line 1: Please be advised that with the electronic funds transfe
r (EFT) run
OMS footer line 2: above, the following invoices/credit notes will be settl
ed. Please
OMS footer line 3: direct any questions or concerns to Nancy Ng at 604.276.
5202 or
OMS footer line 4: nancyn@unipharm.com.
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
Change the ‘Next Create No.’ to the next available sequential number. Press enter to update, then F3 and enter to exit.
To change the number for A/R, select either ‘Statement CAD EDI Config’ or ‘Statement USD EDI Config’. Press page down to see the record.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : EFARCONFR File . . . . : EFARCONFP
*RECNBR: 1
Next Create No: 1388
CIBC DR Originator #: 0100023576
CIBC CR Originator #: 0100033237
EFT Tx Type: 700
Originator Short Name: uniPHARM
Originator Long Name: uniPHARM Wholesale Drugs Ltd.
Sett Inst ID: 010
Sett Branch Transit: 16512
Settlement Account: 0090010
CIBC EFT Data Folder: EFT
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
Change the ‘Next Create No.’ to the next available sequential number. Press enter to update, then F3 and enter to exit.
Cannot Add to Standing Order
Program FMR205 allows user to add, change, and delete records in a standing order. Every time a record is added, it is given the next sequential record number; the numbers from deleted records are not reused. This field (in file SRODTGI) is only 3 digits, so once 999 records have been added, records start being overwritten. The only option is to stop using this group, and create another one.
FIN03KFMG Does not Balance to FIN03
Use Query to look at records in ANOSTK that have
KESUTA = A11 KEMK01 = 730200
Look at field KEMK14. It should be MISC for all records. If it is not, it must be changed.
When an out of balance journal is forced to update, it creates a balancing record into the out of balance account, or 730200. When this transaction is added to ANOBAL, for some reason it does not set the proper keys.
Account number 730200 is set up in the chart of accounts to have a second summary identity of MISC, so why is it updated to a summary ID of blank?
Use DFU or EZVIEW on file AN1STK to make the key fields (11 to 14) the same as the other records that match this account number (parts 1 to 6).
Rebuild the ‘KPMG statement section’
Only ASW administrators can do this, so sign on as FINADMIN.
On a command line in ASW, key in GO GLPER and press enter. Select the option ‘Rebuild summary identities’.
*VA/480B* G/L summary identity rebuild 5/26/15 18:25:09 FMR22201
-------------------------------------------------------------------------------
Summary identity Changed Summary identity Changed
Statement Section... N
Cost Center Group 1. N
Cost Center Group 2. N
KPMG stmt section... N
NO SUMMARY IDENTITY HAVE BEEN CHANGED
THIS JOB WILL BE CANCELLED
If this process will not run, because no changes have been made, you can force it by setting the changed flag to ‘Y’ in G/L Control File Maintenance.
Key in GO GLSET and press enter. Select the option ‘Work with G/L control file’.
Press Enter until this screen shows.
*VA/480B* G/L control file maintenance 5/26/15 18:27:42 GDMD0922
-------------------------------------------------------------------------------
Related to
account part Changed
Summary id part 1... Statement Section 1 N
Summary id part 2... Cost Center Group 1 4 N
Summary id part 3... Cost Center Group 2 4 N
Summary id part 4... KPMG stmt section 1 N
F7=Translate
Change the column ‘Changed’ to Y for the section you want to rebuild (in this case ‘KPMG stmt section’), then go back to ‘Rebuild Summary Identities’.
Check results by running the report. Go to ASW / business reporting / work with reports. Print (option 6) FIN03-KPMG for the appropriate year and period .