General Ledger Table Maps
=========================

----------------------------------------------------------------------
stgstder - standard entry header table for recurring documents

doc_no serial not null,
transaction document number.

doc_desc char(30),
transaction document description.

doc_src char(6),
source journal identification. uses descriptions found
in table stxinfor.

eop_rev char(1),
if "Y", automatically reverse this transaction as part
of the begin a new period process.
if "A", Auto-reversing - System Generated on entry reversed from
previous period.

file_type char(2),
file type is always "SJ".

post_type char(1)
user defined variable that determines how frequently this
recurring document will be posted.
[A] - always post the document
[Y] - post once, change to "N" after posting
[N] - do not post


----------------------------------------------------------------------
stgcntrc - general ledger control table

retain_earnings integer,
default retained earnings account.

genjrn_doc_no integer not null,
most recent general journal document number.

genjrn_post_no integer not null,
most recent general journal posting number.

stdent_doc_no integer not null,
most recent standard entry document number.

genled_post_no integer not null,
most recent general ledger transaction posting number.

curr_period char(2),
current accounting period.

curr_year char(4),
current accounting year.

gl_balanced char(1),
indicates whether the general ledger module setup is
complete. g/l setup must be complete before general
journal transactions in the activity tables may be
posted to the general ledger (stxchrtd).

dir_db_cr char(1),
user defined variable.
[Y] debits and credits are automatically entered in
the transactions using account "increase with
credit" information (stxchrtr).
[N] allows the debit/credit field to be manually
updated.

setup_date date
the general ledger setup complete date is only used only
when the gl module is set to complete. transactions
posted to the general journal (activity tables) on or after
this date will be posted to the original general ledger
period. transactions posted to the general journal
(activity tables) prior to the gl setup complete date
will not be posted to the general ledger.
to the initial period under gl control.

use_batch_gen integer
Use batching in General Ledger Journals

use_approv_post char(1)
Require approval code to post

purge_date date,

approval_code char(8),
Approval code to post

periods_back smallint,
# periods before current before authorization is required for entry

periods_forward smallint,
# periods after current before authorization is required for entry

period_password char(8)
# authorization code for entries beyond accepatble

recalc_date date
Make p_recalc start date user definable

----------------------------------------------------------------------
stgjoure - general journal entry header table

doc_no serial not null,
document (transaction) number.

doc_desc char(30),
document description.

doc_date date,
document date of entry.

doc_src char(6),
source journal (module) of the document.

auto_rev char(1),
user defined variable to automatically reverse this
document when beginning a new accounting period.
[Y] - yes, automatically reverse
[N] - no, do not reverse
[M] - manually reverse this document
[A] - yes, automatically reverse

file_type char(2),
this is linked to table stgjourd and is always "GJ".

posted char(1),
system defined variable (Y/N) indicating whether a standard
entry document has been posted.

ok_to_post char(1)
system defined variable indicating whether an edit list
including this document has been printed. a status of
"N" indicates that the document was created or modified
since the last printing of the list.

batch_id, integer
Batch id

user_id, char(8),
User ID

orig_journal char(2),
For drill down info

trans_doc_no integer
For drill down info

create index i1stgjoure on stgjoure(orig_journal, trans_doc_no)
----------------------------------------------------------------------
stgjourd - general journal entry detail table

orig_journal char(2) not null,
"GJ" if detail for general journal.
"SJ" if detail for standard entry.

doc_no integer not null,
document number.

line_no smallint,
line number for sorting.

acct_no integer,
account number.

department char(3),
department code.

amount decimal(12),
detail row amount.

debit_credit char(1)
debit (D) or credit (C).


----------------------------------------------------------------------
stgtranr - general journal transaction table (activity)

TIP: Link stgtranr to stxtranr for doc_date and other transaction info.

orig_journal char(2) not null,
source journal (module) for this transaction. join
criterion to other activity tables.

doc_no integer not null,
document number for this transaction. join criterion
to other activity tables.

acct_period char(2),
accounting period to which the document was posted.
this will be null for a future period transaction.

acct_year char(4),
accounting year to which the document was posted.
this will be null for a future period transaction.

status char(1)
a system defined variable, status indicates whether
this document has been posted to the ledger.
P - posted
N - not posted
A - archived


----------------------------------------------------------------------
stgactvd - general journal account distribution detail table (activity)

orig_journal char(2) not null,
source journal (module) for this transaction. join
criterion to other activity tables.

doc_no integer not null,
document number for this transaction. join criterion
to other activity tables.

acct_no integer,
account number for this detail row.

department char(3),
department code for this detail row.

amount decimal(12),
monetary amount for this detail row.

debit_credit char(1)
debit or credit indication for this detail row.


----------------------------------------------------------------------
stxtranr - general journal transaction identification table (activity)

orig_journal char(2) not null,
source journal (module) for this transaction. join
criterion to other activity tables.

doc_no integer not null,
document number for this transaction. join criterion
to other activity tables.

post_no integer,
posting sequence number.

post_date date,
system date of posting.

doc_date date not null,
document date (entered).

ref_code char(20),
customer, vendor, employee or journal code for G/L.

doc_desc char(30)
document description (entered).

user_id char(8)
user id

----------------------------------------------------------------------
stxchrtd - chart of accounts history and balance table

acct_no integer,
chart of account number.

department char(3),
department code.

period_month char(2),
accounting period.

period_year char(4),
accounting year.

activity decimal(12),
the activity column plus the this_month column represent all
activity posted to an account for a particular period. trans-
actions initially post to the this_month column. begin a new
period rolls the this_month amount into activity and null
this_month.


balance decimal(12),
YTD balance amount.

this_month decimal(12),
when a given period is current, transaction amounts
accumulate in the this_month column. during the "begin a new
period" process, the this_month amount is transferred
to the activity column and the this_month column is nulled.
this_month will then accumulate prior period postings. i.e.,
expect all postings to the current month to hit the this_month
column. for prior periods, if you notice an amount in the
this_month column it indicates that a posting to that period
has occurred from the current period. using the this_month
column in this fashion allows the financial reports to flag
those accounts that show a prior period posting (possibly
indicating a problem that needs to be looked into.)

budget decimal(12)
user defined budget amount for the account-dept.-period.


----------------------------------------------------------------------
stgcmndr - financial report writer command reference table
Table not in use - For Future Use

cmd_command char(2) not null,
command name.

cmd_desc char(35)
command description.


----------------------------------------------------------------------
stgrowpd - financial report writer row detail table
Table not in use - For Future Use

rod_profile_id char(6),
user defined row detail name.

rod_line_num smallint,
system/user defined row detail line number.

rod_cmd char(2),
user defined row detail command.

rod_beg_dept char(3),
user defined row detail beginning department.

rod_end_dept char(3),
user defined row detail ending department.

rod_beg_acct integer,
user defined row detail beginning account number.

rod_end_acct integer,
user defined row detail ending account number.

rod_acct_dsc char(40),
user defined row detail account description.

rod_tot_a char(1),
user defined row detail operation (+,-,0)

rod_tot_b char(1),
user defined row detail operation (+,-,0)

rod_tot_c char(1),
user defined row detail operation (+,-,0)

rod_tot_d char(1),
user defined row detail operation (+,-,0)

rod_tot_e char(1),
user defined row detail operation (+,-,0)

rod_tot_f char(1),
user defined row detail operation (+,-,0)

rod_tot_g char(1),
user defined row detail operation (+,-,0)

rod_tot_h char(1),
user defined row detail operation (+,-,0)

rod_tot_i char(1),
user defined row detail operation (+,-,0)

rod_prt_row char(1),
user defined row detail print row code.

rod_prt_dbcr char(1),
user defined row detail.

rod_prt_rev_sgn char(1),
user defined row detail print reverse sign code (not used).

rod_prt_dol_sgn char(1),
user defined row detail.

rod_lines smallint
user defined row detail number of additional lines on report.


----------------------------------------------------------------------
stgrowpe - financial report writer row header table
Table not in use - For Future Use

roh_profile_id char(8) not null,
user defined row header name.

roh_dsc char(40),
user defined row header description.

roh_last_change date
date of the last update.


----------------------------------------------------------------------
stgcolcd - financial report writer
Table not in use - For Future Use

coc_content char(2) not null,

coc_desc char(20),

coc_format char(40)


----------------------------------------------------------------------
stgcolpd - financial report writer column detail table
Table not in use - For Future Use

cod_stmt_id char(6) not null,
user defined name.

cod_column char(1),
user defined id (A,B...)

cod_content char(2),
user defined content code.

cod_desc char(20),
user defined description.

cod_beg_period char(2),
user defined beginning period.

cod_beg_perd_year char(4),
user defined beginning year.

cod_end_period char(2),
user defined ending period.

cod_end_perd_year char(4),
user defined ending year.

cod_formula char(40),
user defined period.

cod_format char(40),
user defined format of row detail descriptions.

cod_heading1 char(40),
user defined column heading, top line

cod_heading2 char(40),
user defined column heading, line 2.

cod_heading3 char(40),
user defined column heading, bottom line.

cod_print_col char(1),
user defined print column code (Y/N).

cod_scale smallint,
user defined scaler for monetary amounts.

cod_spaces smallint,
user defined number of additional spaces.

cod_fmt_len smallint


----------------------------------------------------------------------
stgcolpe - financial report writer column header table
Table not in use - For Future Use

coh_stmt_id char(6),
user defined column header statement id

coh_header1 char(40),
user defined column header string, top

coh_header2 char(40),
user defined column header string, row 2

coh_header3 char(40),
user defined column header string, row 3

coh_header4 char(40),
user defined column header string, bottom

coh_footer1 char(40),
user defined column footer string, top

coh_footer2 char(40),
user defined column footer string, row 2

coh_footer3 char(40),
user defined column footer string, row 3

coh_footer4 char(40),
user defined column footer string, bottom

coh_blank_line smallint
user defined number of additional blank lines.


----------------------------------------------------------------------
stxchrtv - chart of accounts view table

acct_no integer,
not null, account number

acct_type char(15),
account type: ASSET, LIABILITY, CAPITAL, INCOME, COST OF GOODS,
EXPENSE

acct_desc char(30),
account description

acct_cat char(1),
account category: A = asset account
B = liability account
C = capital account
D = income account
E = cost of goods account
F = expense account

processing_seq char(1),
processing sequence: 1 = current assets
2 = fixed asset
3 = current liability
4 = long term liability
5 = capital
6 = income
7 = cost of goods
8 = expense

incr_with_crdt char(1),

increase with a credit? [Y/N]

subtotal_group char(30)
subtotal group heading


create view "root".stxchrtv (acct_no,acct_type,acct_desc,acct_cat,
processing_seq,incr_with_crdt,subtotal_group,department,period_month,
period_year,activity,balance,this_month,budget) as
select x0.acct_no ,x0.acct_type ,x0.acct_desc ,x0.acct_cat ,
x0.processing_seq ,x0.incr_with_crdt ,x0.subtotal_group ,
x1.department ,x1.period_month ,x1.period_year ,x1.activity
,x1.balance ,x1.this_month ,x1.budget from "root".stxchrtr x0
,"root".stxchrtd x1 where (x0.acct_no = x1.acct_no ) ;

----------------------------------------------------------------------
stgactvv - General Ledger Activity View Table

orig_journal char(2, not null # Original Journal
doc_no integer, not null # Document Number
post_no integer # Post Number
post_date date # Post Date
doc_date date, not null # Document Date
ref_code char(20) # Reference Code
doc_desc char(30) # Document Description
acct_period char(2) # Accounting Period
acct_year char(4) # Accounting Year
status char(1) # Status
acct_no integer, not null # Account Number
department char(3), not null # Department
amount decimal(12), not null # Amount
debit_credit char(1), not null # Debit or Credit

create view "root".stgactvv (orig_journal,doc_no,post_no,post_date,
doc_date,ref_code,doc_desc,acct_period,acct_year,status,acct_no,
department,amount,debit_credit) as
select x1.orig_journal ,x1.doc_no ,x1.post_no ,x1.post_date
,x1.doc_date ,x1.ref_code ,x1.doc_desc ,x0.acct_period ,x0.acct_year
,x0.status ,x2.acct_no ,x2.department ,x2.amount ,x2.debit_credit
from "root".stgtranr x0 ,"root".stxtranr x1 ,"root".stgactvd x2
where ((((x0.doc_no
= x1.doc_no ) AND (x0.doc_no = x2.doc_no ) ) AND (x0.orig_journal
= x1.orig_journal ) ) AND (x0.orig_journal = x2.orig_journal
) ) ;
------------------------------------------------------------------------
stgtranv - General Ledger Tranaction View Table

orig_journal char(2),not null # Original Journal
doc_no integer,not null # Document Number
post_no integer # Post Number
post_date date # Post Date
doc_date date, not null # Document Date
ref_code char(20) # Reference Code
doc_desc char(30) # Document Description
acct_period char(2) # Accounting Period
acct_year char(4) # Accounting Year
status char(1) # Status

create view "root".stgactvv (orig_journal,doc_no,post_no,post_date,
doc_date,ref_code,doc_desc,acct_period,acct_year,status,acct_no,
department,amount,debit_credit) as
select x1.orig_journal ,x1.doc_no ,x1.post_no ,x1.post_date
,x1.doc_date ,x1.ref_code ,x1.doc_desc ,x0.acct_period ,x0.acct_year
,x0.status ,x2.acct_no ,x2.department ,x2.amount ,x2.debit_credit
from "root".stgtranr x0 ,"root".stxtranr x1 ,"root".stgactvd x2
where ((((x0.doc_no
= x1.doc_no ) AND (x0.doc_no = x2.doc_no ) ) AND (x0.orig_journal
= x1.orig_journal ) ) AND (x0.orig_journal = x2.orig_journal
) ) ;
-----------------------------------------------------------------------