Subledgers Subledger Tables and Tips Mary Canan Bellevue Community College Teresa Feltmann Center for Information Services BAR 101 April 21, 2006 Renton Technical College Subledgers Subledgers can be established for any general ledger to capture year-to-date and, optionally, biennium-to-date general ledger activity. Subledgers • Subledgers are maintained on the Subledger Table (GA1078) and assigned to the general ledgers on the General Ledger Table (GA1031) with the posting routine controlled on the Transaction Code Table (GA1093). Subledgers Go to the CIS website for documentation on FMS Tables: http://www.cis.ctc.edu Applications- FMS FMS Document Index FMS Module &Functions FMS Control Tables Tables Control Tables Subledger Table GA1078 General Ledger Table GA1031 Transaction Code Table GA1093 GA1078 • Subledger Control Table Assigns the Subledger Code Codes 01-20 are CIS-defined. Values 01 Encumbrance 02 Reimbursable 03 Credit Memo 04 Accounts Payable 05 Deferred Revenue 06 Tuition Distribution 07 Payroll Handwrites 08 Accounts Payable Accrual Subledger 09 Accounts Receivable Subledger 10 Travel Advances GA1078 • GA1078-001 SUBLEDGER TABLE • SUBLEDGER CODE  SUBLEDGER TITLE [AR SUBLEDGER • SUBLEDGER SELECTION INDICATOR [ A] • SUBLEDGER PURGE INDICATOR [ 9] • SUBLEDGER LIFE INDICATOR [N] • • RVSN DATE [ 050402] 9005:INQUIRY complete FYR=05-06 ] SUBLEDGER STATUS [A] GA1078 • Subledger Purge Indicator is controlled on this table by the following values- The charge and the payment must equal zero by the following parameters • • • • • • • 0 1 2 3 4 5 6 • • • Do not purge FND or APPR-INDX, GL & DOC-NUM FND or APPR-INDX, GL & ID-NUM-TYP (CUST-ID) FND or APPR-INDX, GL, ID-NUM-TYP & DOC-NUM REIM-CD & CAMPUS (on ORG TABLE) FND or APPR-INDX, GL & ID-NUM-TYP (CUST-ID) FND or APPR-INDX, GL, ID-NUM-TYP (CUST-ID), CHRG-ID & CUST-REF 7 FND or APPR-INDX, GL, ID-NUM-TYP (CUST-ID), CUST-REF & YRQ 8 Developed for Customer Accounts use FND or APPR-INDX, GL, ID-NUM-TYP (CUST-ID), CHRG-ID, DOC-NUM, PRG-ORG & CUST-REF 9 FND or APPR-INDX, GL, ID-NUM-TYP (CUST-ID), DOC-NUM & PRG-ORG GA1031 • General Ledger Table tie the subledger code from GA1078 to the general ledger. Examples • GL 1312 Subledger Code = 09 • GL 1319 Subledger Code = 07 • GL 5192 Subledger Code = 05 • GL 6410 Subledger Code = 01 • GL 6505 Subledger Code = 02 GA1031 • GA1031-002 GENERAL LEDGER TABLE • GENERAL LEDGER  GL TITLE [CURRENT ACCTS REC ] GL STATUS [A] • • SUBLEDGER CODE  SUBLEDGER UPDATE IND [S] • GL DR/CR IND [+] • • • • GL CLOSE IND  GL OPEN IND [S] GL FYR OPEN TRNS CD [S05] GL BIEN OPEN TRNS CD [S05] • • • GL GROUP  GL CLASS [C] GL SUBCLASS  • • • GL BDGT UPDATE IND [ ] • • AFRS GL REPORT IND [Y] RVSN DATE  9005:INQUIRY complete FYR=05-06 GA1031 • GL 1312 Subledger indicator value A. Update all occurrences of this GL to the Subledger regardless of the transaction code S. Updated selected occurrences based on specific transaction code GA1093 • Transcode Table Posting Routine ~ GA1906 Values: Budget = Budget Status File GA1906 = Subledgers FINAID = Financial Aid Y-N for dr/cr must match field for GL GA1093 • GA1093-001 TRANSACTION CODE TABLE-FILE POSTING FYR=05-06 • TRANSACTION CODE  • • • FILE POSTING POSTING RTN NUM TITLE DR1 CR1 DR2 CR2 DR3 CR3 DR4 CR4 [GA1906] [AR SUBLEDGER ] [Y] [N] [N] [N] [N] [N] [N] [N] • 9005:INQUIRY complete TRNS CODE TITLE [A/R-RECORD CURRENT ] GA1093 • GA1093-001 TRANSACTION CODE TABLE-FILE POSTING FYR=05-06 • TRANSACTION CODE  • • • • FILE POSTING POSTING RTN NUM TITLE DR1 CR1 DR2 CR2 DR3 CR3 DR4 CR4 [GA1906] [DEFERRED REVENUE] [N] [Y] [N] [N] [N] [N] [N] [N] • 9005:INQUIRY complete TRNS CODE TITLE [C/R-DEFERRED REVENUE] Jobs & Reports: See the CIS website for documentation on Job Scheduling: http://www.cis.ctc.edu Applications- CIS Job Scheduling Index FMS - Select job category BAxxxxJ BMxxxxJ FMxxxxJ GAxxxxJ MMxxxxJ PMxxxxJ FGxxxx Job Scheduling for Subledgers 1. Daily posting updates subledger 2. FG009M ~ monthly purge after month end – GA1305J job runs purge – GA1314J Purge Report (Note: GA1305J job purges all subledger – their isn’t a selection criteria) Subledger Reports • Job GA1337J to generate subledger reports • College Option ~ • 01 = Encumbrance • 02 = Reimbursable (see GA3236J) • 03 = Credit Memo • 04 = Accounts Payable • 05 = Deferred Revenue • 06 = Tuition Distribution (see GA2102J) • 07 = Payroll Handwrites (1399 GL) • 09 = Accounts Receivable (see BM 1781J, BM 1780J & BM 1711J) • 10 = Travel Advance TIPS AR Subledgers Run AR subledger reports a minimum of monthly AR Subledgers – BM 1781J for Detail 1. Change Customer ID # frequently if you are having issues with purging. 2. Organize your Customer ID#. 3. Review purge criteria 4. Schedule BM1780J - AR Trial Balance for an overview tool. 5. Schedule BM1711J – to monitor the long term outstanding and problem receivables. 6. Student Social Security numbers can not be corrected to the SID #. Call CIS for a manual correction. Tips for Payroll/Travel Advance Run GA1337J Subledgers 07 and 10 – minimum monthly Payroll – issues are normally between Social Security with an (E-employee) versus (S-student) you will have to manually correct these with a GAT sheet through screen GA1103. Verify monthly that handwrites are clearing- talk to the payroll department for all outstanding handwrites to verify which payroll will clear them. (the B payroll will always carry over to the next month) Travel Advances – monitor to verify travel expense reimbursements are returning timely. If using transaction code 077 (GL1383) for the travel advance use 078 for the travel expense-this will clear the advance in one entry. Tips for Deferred Revenue 1. 2. 3. 4. 5. 6. Review debt types- Table BM1020 – all fees do not have to defer – such as auxiliary funds. Review fee codes – Table BM1001 – verify that fee codes that do not defer have the appropriate debt type. Talk to cashiers regarding the Year Quarter of their cashiering screens for non-registration activity. Run the reverse deferred revenue job FG034Aas soon as possible after year end. Must be run for all quarters separately Run the purge report GA1305J after July ME. Run Subledger GA1337J Code 05 to find items that did not purge. Credit Memos Run GA1337J at minimum quarterly. Especially as you near year end. Request a check from the vendor whenever possible. Make sure that vendor payments are not being paid when there is an outstanding credit memo. These transactions are normal in the bookstore operation. Accounts Payable Run GA1337J – subledger code 04. Are you utilizing this subledger? Check GA1031 for ledger 6505 and 6510. If it has the code 04 it is updating an accounts payable subledger. If it has the code 02 it is updating a reimbursable subledger. College Defined Subledgers Examples of where you may want to establish your own unique subledger 22 Customer Deposits 23 Dormant Checks Set-up on Tables: GA1078; GA1031; GA1093 Balancing Balance Monthly – The key to a clean close/open is not to have issues in the subledgers Monthly Run Reports Designate a person responsible for monitoring the reports Develop procedures for a high level review Look for credit amounts in the AR Subledger Look at the aging reports Scan the Prog/Org Trial Balance Report Monthly Run the SMART GA1409 checklist. http://www.sbctc.ctc.edu/SMART/logon.asp • Correct entries each month • The next day run the SMART GA1409 checklist to verify your correction fixed the error. (Be sure to upload current data) Tricks with Accounts Receivable ID • Assign CUST-ID numbers to assist your quick reviews • Rotate large agency numbers quarterly – IE: DVR-A561; DVR-A562 • Assign a number by staff responsible - PC-SBCTC - FS-DSHS Establish Quick DATAX for balances • We can Share Datax files through CIS. Your IT person will need to be involved. • Samples available Close/Open • The General Ledgers with a GL open indicator of a “S” on the GA1031 table will open from the Subledgers • How do they get out of balance? – Manual Adjusting entries sent to SBCTC during closing – Using incorrect transaction codes. Transaction codes for Opening Entries Current Year Transaction Codes post to the subledger Opening Transaction Codes are as follows: • A__ do not post to subledger • R __ post to the subledger • S __ do not post to the subledger Prog/Org Trial Balance GA1335 Example of when S__ transaction codes maybe necessary. Accounts Receivables have purged – you have a GL1312 balance off setting in two different prog/org but nothing on the AR subledger. Use S05 and S05R (they must be in the same fund) to correct. Correct a manual closing entry Some manual entries can be reversed by SBCTC. If they are reflected on your opening adjusting entries you must enter them in the new year: Fund 149 149 GL GA1409 1354 2,000 9580 -1,250,000 YREND 2,400 -1,250,400 Difference 400 -400 Entry in new year FMS by General Accounting Batch: GA1103 (If receivable is still Outstanding correct it as follows: TC Fund GL Amount R14 149 1354/9999 400 (this will post to the subledger) A01R 149 9999/9580 -400 If the receivable is no longer outstanding— R14 149 1354/9999 400 (this will post to the subledger) 150R 149 3205/1354 -400 (this will post to the subledger) A01R 149 9999/9580 400 When all else fails – call CIS Customer Support • Things that happen that you can’t fix. – Social Security Numbers in the AR subledger versus student ID numbers – Encumbrance/Liquidation that get caught between fiscal years Questions?