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
auto_genjrn_post char(1),
Auto GJ Post
auto_genjrn_post_d char(1)
Auto GJ Post direct
----------------------------------------------------------------------
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
) ) ;
-----------------------------------------------------------------------