[gnue-sb-discuss] General Ledger and Accounts Payable

[Jason Cater]

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.