[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[gnue-sb-discuss] General Ledger and Accounts Payable
From: |
Jason Cater |
Subject: |
[gnue-sb-discuss] General Ledger and Accounts Payable |
Date: |
Mon, 10 Mar 2003 22:38:26 -0600 |
Greetings,
I'd like to kick off the discussions on GNUe/SB's General Ledger
and Accounts Payables modules. I have attached my notes from the
spring of 2000 when I started writing an inhouse accounting package.
Other work projects claimed all my time, so this was put on hold.
It was of course designed around what we needed at my office, but
general principles still apply.
I'd like to see work get underway with these two modules as they
are the ones I need more than any of the other. Hopefully if we
can agree on some schemas, I can start spitting out forms. If we
have something usable in these two areas be June, I imagine I can
switch NCS over in July (as that' s when our fiscal year begins.)
This document is split into three further sections. In the first
section, I'll list my basic (incomplete) requirements. The second
section lists all the table names with a brief description of
functionality. The third and final section shows the schema for
the tables described in section 2. Not all table definitions have
been started or completed.
Note: This is based on a schema I started in Spring 2000,
and did not take things such as GNUe Small Business' Contact
Management into consideration. Also, any number(12) fields are
ID/key fields in my schemas.
-- Jason
======================================================================
My Short-term Requirements:
Overall
* Must support multiple companies
* Must support fiscal years; not forced to use calendar year
General Ledger
* Quick Journals
* Journal Entry posting
* Inter-Company Posting
* Manual and Recurring transactions
* Year end functions
* Balance Sheet
* Daily Status Reports
* P&L Statements
Accounts Payable
* Print checks
* Batch entry/posting
* Print 1099s
* Import vendors from outside sources (I can do this manually w/scripts)
* Detail and summary posting to G/L
======================================================================
Common Tables
-------------
companies
Contains one record per company.
General Ledger Tables
---------------------
gl_periods
Contains one record per period per company. Once a period is
closed, no further activity is allowed on that period.
gl_account_segments
Defines the segments used in an account number.
gl_account_categories
Defines the account categories an account can be associated with.
Affects the location of the accounton the balance sheet.
gl_accounts
One row per account per company.
gl_account_balances
Contains the balances by period for each account.
gl_account_budgets
Contains the budgeted amount by period for each account.
gl_batches
Contains one record per transaction batch. Batches can be one-time
or recurring weekly, biweekly, monthly, quarterly, etc.
gl_transactions
Contains the transaction detail information, both posted and non-
posted.
gl_journal
Contains one record per general journal entry.
gl_quick_journals
One record per defined quick journal definition. Quick journals are
like a template for common journal entries. It contains all the detail
rows for a recurring journal entry, but without any values. When it
comes
time to do the journal entry, the accountant can simply pull up the
quick journal and fill in values without looking up accounts.
gl_quick_journal_details
Contains the detail information for each quick journal.
Accounts Payable Tables
-----------------------
ap_payment_terms
Contains payment terms to be associated with vendors and
invoices; e.g., Net 10 days, Net 45 days, Prepayment.
ap_vendor_classes
Contains classifications used to group vendors. This is
arbitrary and may not be used by all businesses. I would
have "Magazine Publishers", "General Suppliers", "Employees",
"Prize Vendors", "Sales Representatives", "Miscellanous",
et al, as vendor classes.
This may be duplicating a table in the Contact Management
system, though it may still need to be separate.
ap_vendors
Contains one row per vendor. It contains information such as
vendor payment terms.
This could/should be tied into the Contact Management schema.
ap_invoice_classes
Arbitrary classification of invoices; can be used for reporting
or querying.
ap_invoices
One row for each a/p invoice, whether paid or awaiting payment.
ap_payments
One row for each physical disbursement. Multiple invoices can
be assocatied with a single payment, though only one vendor
would be associated.
ap_invoice_payments
Payment details for invoices. Invoices can be partially paid, so
there can be a 1:n relationship between ap_invoices and
ap_invoice_payments.
======================================================================
------------------------------------
companies
------------------------------------
create table companies
(ID number(12) not null,
NAME varchar(40) not null,
FEDERAL_ID varchar(10) unique,
ACTIVE varchar(1) not null, -- Y/N
FISCAL_START number(2) not null, -- Month (07-Starts in July)
CURRENT_YEAR number(4) not null, -- ???? still relevant
CURRENT_PERIOD number(2) not null) -- ???? w/gl_periods?
------------------------------------
ap_payment_terms
------------------------------------
create table ap_payment_terms
(ID number(12) PRIMARY KEY,
DESCRIPTION varchar(20) not null,
DAYS_TO_PAY number(3) not null);
0, 0, 'Cash/Prepayment';
1, 10, 'Net 10 Days';
2, 15, 'Net 15 Days';
3, 20, 'Net 20 Days';
4, 30, 'Net 30 Days';
------------------------------------
ap_vendor_classes
------------------------------------
ID number(12) primary key,
COMPANY number(12) not null,
VENDOR_CLASS number(12) not null,
DESCRIPTION varchar(30) not null,
TEN99 varchar(1) not null,
PAYMENT_TERMS number(12) not null)
------------------------------------
ap_vendors
------------------------------------
create table ap_vendors
(ID number(12) primary key,
COMPANY number(12) not null,
VENDOR_CODE varchar(15) not null,
VENDOR_CLASS number(12) not null,
NAME varchar(40) not null,
MASTER_LOOKUP number(12) not null,
ACTIVE varchar(1) not null, -- Y/N
IMPORTED varchar(1) not null, -- Y/N
TEMPORARY varchar(1) not null, -- Y/N (One-time)
PAYMENT_TERMS number(12) not null,
unique (vendor, company) )
------------------------------------
ap_invoice_classes
------------------------------------
create table ao_invoice_classes
(INVOICE_CLASS number(12),
DESCRIPTION varchar(30) not null,
COMPANY number(12) not null,
AR_ACCOUNT number(12) not null, -- ???
constraint pk__invoice_classes
primary key (invoice_class)
constraint fk__invoice_classes__account)
cache;
------------------------------------
ap_invoices
------------------------------------
create table ap_invoices
(INVOICE number(12),
INVOICE_CLASS number(12) not null,
AR_VENDOR ??
SALES_AGENT ??
POSTED date,
constraint pk__invoices
primary key (invoice)
constraint fk__invoices__class
foreign key (invoice_class)
references invoice_classes (invoice_class),
)
------------------------------------
ap_invoice_payments
------------------------------------
create table ap_invoice_payments
(ID number(12),
INVOICE number(12) not null,
CHECK_NO number(10),
TRANS_DATE datetime not null,
POSTED date,
AMOUNT number(14,4) not null,
PREPAYMENT varchar(1) not null, -- Y/N
)
------------------------------------
gl_periods
------------------------------------
create table gl_periods
(ID number(12) not null,
COMPANY number(12) not null,
FISCAL_YEAR number(4) not null,
FISCAL_PERIOD number(2) not null, -- 1-12 (or 1-53, etc)
FIRST_DATE date not null,
LAST_DATE date not null,
OPEN_FOR_POSTING varchar2(1) not null) -- Y/N
------------------------------------
gl_default_accounts
------------------------------------
create table gl_default_accounts (
ID number(12) primary key,
DESCRIPTION varchar(40) not null,
COMPANY number(12) not null,
ACCOUNT number(12) not null )
------------------------------------
gl_account_segments
------------------------------------
create table gl_account_segments
(ID number(12) primary key,
COMPANY number(12) not null,
PARENT_SEGMENT number(12) not null,
ACCOUNT_SEGMENT varchar(10) not null,
SEQUENCE number(2) not null,
DESCRIPTION varchar(40) not null)
------------------------------------
gl_account_categories
------------------------------------
create table gl_account_categories
(ID varchar(3) not null,
COMPANY number(12) not null,
DESCRIPTION varchar(50) not null,
unique (company, description) )
------------------------------------
gl_accounts
------------------------------------
create table gl_accounts
(ID number(12) primary key,
COMPANY number(12) not null,
ACCOUNT varchar(30) not null, -- Full account code
NAME varchar(40) not null,
ACCOUNT_CATEGORY number(12) not null,
POSTING_TYPE varchar(1) not null, -- B=batch???/P= ????
TYPICAL_BALANCE varchar(1) not null, -- C=credit/D=debit
ACTIVE varchar(1) not null, -- Y/N
unique (account, company) )
------------------------------------
gl_account_balances
------------------------------------
create table gl_account_balances
(ID number(12) primary key,
ACCOUNT number(10) not null,
YEAR number(4) not null,
PERIOD number(2) not null, -- 0=beginning balance/1-12
BALANCE number(14,4) not null,
unique (account, period, year) )
------------------------------------
gl_account_budgets
------------------------------------
create table gl_account_budgets
(ACCOUNT number(12) not null,
YEAR number(4) not null,
PERIOD number(2) not null,
BUDGETED number(14,4) not null,
unique (account, year, period) )
------------------------------------
gl_transactions
------------------------------------
create table gl_transactions
(TRANSACTION number(14),
COMPANY varchar(3) not null,
ACCOUNT number(10) not null,
JOURNAL_ENTRY number(10),
REFERENCE varchar(30),
DESCRIPTION varchar(30),
TRANS_DATE date not null,
POSTING_DATE date not null,
PERIOD number(12) not null,
AMOUNT number(14,4) not null,
MODULE number(12) not null,
SOURCE_PROCESS varchar(4), -- (dependent on series)
SOURCE_DOCUMENT varchar(15),
SOURCE_TRANSACTION varchar(15),
SOURCE_MASTER_ID varchar(20), -- (e.g., vendor id)
SOURCE_MASTER_NAME varchar(30) -- (e.g., vendor name)
)
Notes: I forget what the SOURCE_* stuff meant exactly, but I do
think it was to tie back into the source modules. In other words,
the source* fields mean nothing to the G/L system but would be
meaningful to the A/P system or the A/R system if those were the
source of the transactions.
- [gnue-sb-discuss] General Ledger and Accounts Payable,
Jason Cater <=