Information Systems:Journal in Error
Journal Does Not Post
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 Information Systems: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.