Accounts Receivable Tables
==========================
-----------------------------------------------------------------------
strcashe - Cash Receipts Entry Header Table
rcpt_date date,
Receipt date
doc_no serial not null,
Document number
cust_code char(20),
Customer reference code
if NULL then it is a non-a/p cash disbursment
gross_entry char(1)
Is gross entry used?
def_mtaxcd char(6)
Default multilevel tax code
check_no char(10),
Check number (freeform)
doc_desc char(30),
Document description
cash_amt decimal(10,2),
Cash account amount
cash_acct integer,
Cash account number
cash_department char(3),
Cash account department
cash_deb_cred char(2),
Cash account debit/credit ("DB" or "CR")
oa_amt decimal(10,2),
Amount to apply to the on account balance
oa_acct integer,
On account account number
oa_department char(3),
On account department
oa_deb_cred char(2),
On account debit/credit
ok_to_post char(1)
"Y" edit list has been printed for this doc.
"N" document has been modified since last
edit list was printed
batch_id integer
Batch ID
orig_journal char(2)
For drill down reference
trans_doc_no integer
For drill down reference
posted char(1)
For drill down reference
index i1rcashe on strcashe (doc_no)
index i2rcashe on strcashe (orig_journal, trans_doc_no)
-----------------------------------------------------------------------
strcashd - Cash Receipts Entry Detail Table
doc_no integer,
Document number
inv_doc_no integer,
Invoice document number to affect
inv_no char(10),
Invoice number (freeform)
due_date date,
Invoice due date
dist_acct integer,
AP distribution account number
dist_department char(3),
AP distribution department
dist_amt decimal(10,2),
AP distribution amount
dist_deb_cred char(2),
AP distribution debit/credit ("DB" or "CR")
mtax_code char(6),
Tax code applied to this line (multi-tax)
good_amount decimal(12)
Goods amount
disc_acct integer,
Discount account number
disc_department char(3),
Discount department
disc_amt decimal(10,2),
Discount amount
disc_deb_cred char(2)
Discount debit/credit ("DB" or "CR")
index i1rcashd on strcashd (doc_no,due_date,inv_doc_no)
-----------------------------------------------------------------------
strcnotd - Customer Notes Table
doc_no integer,
cust_code char(20),
note_date date,
user_id char(8)
unique index i1rcnotd on strcnotd (cust_code,doc_no)
index i2rcnotd on strcnotd (doc_no)
-----------------------------------------------------------------------
strcntrc - Accounts Receivable Control Table
terms_code char(6),
Default terms code used if the terms code field in the Customer
Table is null.
tax_pct decimal(6),
Sales tax percentage to be applied to invoice totals.
ar_acct_no integer,
Default A/R Account Number that is used if the default
A/R Account number field in the Customer Table is null.
ar_sales_acct_no integer,
Default sales account number that is used it the default
Sales Account number field in the Customer Table is null.
ar_tax_acct_no integer,
Default tax account number.
ar_frght_acct_no integer,
Default freight account number.
ar_misc_acct_no integer,
Default miscellaneous account number.
ar_fc_acct_no integer,
Default finance charge account number.
cr_cash_acct_no integer,
Default cash account number used in cash receipts.
cr_disc_acct_no integer,
Default discount accountt number used in cash receipts.
ar_doc_no integer,
Last a/r document number used during posting.
ar_post_no integer,
Last a/r posting batch number.
ar_balanced char(1),
a/r setup complete ? (Y/N)
Y - Beginning balances have been calculated and A/R is ready
to use. Transaction posting can begin.
N - Beginning balances are not complete. No document posting can
take place before setup is completed. If posting has taken
place and this flag is reset to 'N' G/L orphans can and most
likely will occur.
cr_doc_no integer,
Last cash receipts document number.
cr_post_no integer,
Last Cash Receipt posting batch number.
disc_misc char(1),
Calculate discounts on miscellaneous charges (Y/N)?
disc_frght char(1),
Calculate discounts on freight (Y/N)?
disc_tax char(1),
Calculate discount on sales tax (Y/N)?
auto_inactive_days smallint,
This column indicates how many days after last order
before the customer becomes inactive.
The O/E module makes use of this column.
inactive_ret_days smallint,
This column indicates the number of days to hold this customer
before removal from the system.
age_datetype char(1),
Age based on invoice ("I") or due ("D") date
age_per1 smallint,
Number of days in aging period #1
age_per2 smallint,
Number of days in aging period #2
age_per3 smallint,
Number of days in aging period #3
age_dsc1 char(15),
Aging period #1 description (right justified)
age_dsc2 char(15),
Aging period #2 description
age_dsc3 char(15),
Aging period #3 description
age_dsc4 char(15),
Aging period #4 description
mtax_misc char(6),
Default multilevel tax code for miscellaneous
mtax_frght char(6),
Default multilevel tax code for freight
def_mtaxcd char(6),
Default multilevel tax code for detail lines on invoices
gross_entry char(1),
Use gross entry for initial price entry (Y/N)?
mtax_dsc char(1),
Calculate multilevel tax on cash discounts (Y/N)?
mtax_fc char(6),
Default mutilevel tax code for finance charges
last_inv_no integer
Last invoice number used for automatic update
use_batch_inv char(1)
Use batch control for invoices
use_batch_rec char(1)
Use batch control for cash receipts
use_approv_post char(1)
Use approval code to post
approval_code char(8)
Approval code to post
age_per4 smallint,
Number of days in aging period #4
age_per5 smallint,
Number of days in aging period #5
age_per6 smallint,
Number of days in aging period #6
Period #7 is anything greater than period #6
age_dsc5 char(15),
Aging period #5 description
age_dsc6 char(15),
Aging period #6 description
age_dsc7 char(15),
Aging period #7 description
per_no smallint
Period Number
woff_acct_no integer
Default GL Account Number for Writeoffs
-----------------------------------------------------------------------
strcustr - Customer Reference Table
cust_code char(20),
Customer identification code
bridge_code char(20), Field not in use.
Common code for multiple sites
bus_name char(30),
Business name
taxable char(6),
Default multilevel tax code for this customer
contact char(20),
Contact name
phone char(20),
Phone number
fax_phone char(20),
Fax Phone Number
address1 char(30),
Address line #1
address2 char(30),
Address line #2
city char(20),
City
state char(2),
State
zip char(10),
Zip
country char(20),
Country
ar_type char(1),
Accounts Receivable balance type
O - Open Item
B - Balance Forward
preferred char(1), Field not in Use.
Customer has preferred status (best discounts)
frequent char(1), Field not in Use.
Customer is frequent customer (near preferred)
stmt_cycle smallint,
Statement cycle identifier
fin_chg char(1),
Does this customer qualify for finance charges?
credit_limit decimal(10,2),
Credit limit for this customer
order_limit decimal(12),
Order limit for this customer
terms_code char(6),
Terms reference code (lookup to terms table strtermr)
act_grp char(6),
Default account group for this customer
ar_acct_dflt integer,
Default Accounts Receivable account number
ar_department_dflt char(3),
Default Accounts Receivable department number
stmt_date date,
Date of last statement
stmt_amount decimal(10,2),
Amount of last statement
acct_bal decimal(10,2),
Current customer account balance
obtained_date date,
Date company order was first obtained
last_order_date date,
Day last order was received
last_pay_date date,
Date we were last paid by this customer
inactive_date date, Field not in Use.
Date customer became inactive
on_acct_amt decimal(10,2),
Current "on account" balance
arch_bal decimal(10,2),
Last archive balance
sls_psn_code char(6),
Salesperson code (for order entry)
trd_ds_code char(6),
Trade discount code (for order entry)
st_tx_code char(6), Obsolete field - see multilevel tax code
State sales tax code (for order entry)
co_tx_code char(6), Obsolete field - see multilevel tax code
County sales tax code (for order entry)
ci_tx_code char(6), Obsolete field - see multilevel tax code
City sales tax code (for order entry)
comm_code char(6),
Commission code for sales to this customer
pay_method char(6),
How is the order to be paid for (VISA, CASH, CHECK)
card_no char(20),
Credit card number
exp_date char(5),
Credit card expiration date
card_holder char(20),
Name of card holder
cc_method char(6),
Which card company (VISA, MC, AMEX, DISCOVERY)
mtax_fc char(6),
Tax code for use with finance charges
currency_code char(3),
Currency code for use with the MultiCurrency module
mtax_freight char(6),
Default tax code for freight amounts on invoices
mtax_misc char(6)
Default tax code for miscellaneous amounts on invoices
ship_via_cd char(3),
Ship Via Code
ship_terms char(15)
ups_account char(10),
email char(50),
web_address char(50),
cell_phone char(20),
credit_hold char(1),
[Y/N]? If 'Y' display message when creating order or invoice
credit_manager char(8),
credit_letter char(1),
[Y/N]?
credit_hold_date date,
residential_cust char(1)
[Y/N]?
ship_complete char(1)
[Y/N]?
constraint check (credit_letter IN ("Y" ,"N" )) constraint
c1rcustr
constraint check (ship_complete IN ("Y" ,"N" )) constraint
c2rcustr
-----------------------------------------------------------------------
strmemod - Cash Receipts Memo Table. Used for chrage backs and
write offs
doc_no integer
Document number. Join to strcashe and strcashd.
inv_doc_no integer
Invoice document number. Join to strcashd.
line_no smallint
Line number. Used for ordering.
memo_amt decimal(12)
Amount of charge back or write off to be created
memo_no char(10)
Memo number for charge back or write off to be created
memo_desc char(30)
Description for memo to create
memo_acct integer
GL account for the AR offset (detail line)
memo_dept char(3)
Department for the detail line
memo_deb_cred char(2)
If memo_deb_cred = "DB", then this is a charge back (debit to
AR,
credit to AR).
If memo_deb_cred = "CR", then this is a write off (credit to
AR,
DB to exp)
memo_date date
Invoice date for the memo to create. Drives discount date as
well.
Defaults to original invoice date but user can change it. Only
relevant to charge backs, not write offs.
create index i1rmemod on strmemod (doc_no,inv_doc_no);
-----------------------------------------------------------------------
strmrgcr -
old_cust_code char(20),
new_cust_code char(20),
data_type char(50),
line_no smallint,
label_line char(15),
data char(60)
create index i1rmrgcr on strmrgcr (old_cust_code);
create index i2rmrgcr on strmrgcr (new_cust_code);
-----------------------------------------------------------------------
stxtranr - Master Transaction Identification Table
orig_journal char(2) not null,
What journal did this trx. come from?
doc_no integer not null,
Document number (unique identifier)
post_no integer not null,
Posting sequence number (on post rpts)
post_date date not null,
Date of posting (system date)
doc_date date not null,
Document date (entered)
ref_code char(20),
Customer/Vendor/(Journal code for G/L)
doc_desc char(30)
Document description
user_id char(8)
user id
unique index i1xtranr on stxtranr (orig_journal,doc_no)
index i2xtranr on stxtranr (ref_code)
-----------------------------------------------------------------------
strtranr - Accounts Receivable Transaction Table
- One entry for every Accounts Receivable document
TIP: Link strtranr to stxtranr to get doc_date and other transaction
information.
orig_journal char(2) not null,
Original journal code
doc_no integer not null,
Document number
inv_chk_no char(10),
Invoice or Check reference number
doc_type char(2) not null
Document type
IN - invoice
CR - cash receipt
CM - credit memo
DM - debit memo
unique index i1rtranr on strtranr (orig_journal,doc_no)
-----------------------------------------------------------------------
stractvd - Accounts Receivable Activity Detail Table
- one entry for every transaction that affects a balance in a/r
orig_journal char(2) not null,
Original journal
doc_no integer not null,
Document number
act_type char(1) not null,
Activity type
A - affects an a/r balance
D - affects a discount balance
inv_doc_no integer not null,
Document number of the invoice that this transaction affected.
or 0 if it affected the "on account" amount.
amount decimal(10,2) not null,
Amount of the transaction (signed)
(+) positive numbers increase balances
(-) negative numbers decrease balances
currency_code char(3),
Defined code for use in multi-currency
curr_ex_rate decimal(16),
Units per one home_curr unit exchange
home_curr_amount decimal(12)
Amount of transaction in home currency
index i1ractvd on stractvd (orig_journal,doc_no)
index i2ractvd on stractvd (inv_doc_no)
-----------------------------------------------------------------------
stropend - Accounts Receivable Open Items Table
cust_code char(20) not null,
Customer reference code
inv_no char(10),
Invoice number (not used as a key)
doc_no integer not null,
Document number (key to this table)
inv_desc char(30),
Invoice description
inv_date date not null,
Invoice entry date
orig_amount decimal(10,2) not null,
Original invoice amount
disc_amt decimal(10,2) not null,
Original discount amount
balance decimal(10,2) not null,
Current balance
disc_bal decimal(10,2) not null,
Current discount balance
due_date date not null,
Invoice due date
disc_date date not null,
Discount due date
ar_acct_no integer not null,
Accounts Receivable account number invoice posted to
ar_department char(3) not null,
Accounts Receivable dept. invoice posted to
po_no char(10),
Purchase order number
po_date date,
Purchase order date
item_type char(2),
Classification code for particular item
currency_code char(3),
Defined code for use in multi-currency
curr_ex_rate decimal(16),
Units per one home_curr unit exchange
home_curr_amount decimal(12)
Amount of transaction in home currency
last_pay_date date
Last payment date
sls_psn_code char(6)
Salesperson Code
index i1ropend on stropend (cust_code)
unique index i2ropend on stropend (doc_no)
index i3ropend on stropend (inv_no)
-----------------------------------------------------------------------
strinvce - Invoice Entry Header Table
doc_no serial not null,
Document number
inv_no char(10),
Invoice number
department char(3),
Default department
file_type char(1),
I - invoice
D - debit memo
C - credit memo
ref_no integer,
If credit or debit memo type document, this represents
the invoice document number that is being credited or debited
tax char(6),
Multilevel tax code for invoices
inv_desc char(30),
Document description
inv_date date,
Document date
inv_note char(30),
Note to show on invoice
cust_code char(20),
Customer reference code
ship_to_code char(6),
Customer default ship-to code
posted char(1),
Y/N only recurring invoices will be marked
"Y" because all others are deleted upon posting.
recurring char(1),
Y/N marked for recurring
terms_code char(6),
Payment terms code (lookup into the terms table strtermr)
due_date date,
Date this invoice is due
disc_date date,
Date discount must be taken by
disc_pct float,
Discount percent (for calculations)
po_no char(10),
Purchase order number
po_date date,
Purchase order date
disc_acct_no integer,
Discount account number
disc_department char(3),
Discount department
disc_amount decimal(10,2),
Discount amount
disc_debit_credit char(2),
Discount "CR" or "DB" (credit or debit)
tax_acct_no integer,
Tax account number
tax_department char(3),
Tax department
tax_amount decimal(10,2),
Tax amount
tax_debit_credit char(2),
Tax "CR" or "DB" (credit or debit)
frght_acct_no integer,
Freight account number
frght_department char(3),
Freight department
frght_amount decimal(10,2),
Freight amount
frght_debit_credit char(2),
Freight "CR" or "DB" (credit or debit)
misc_acct_no integer,
Miscellaneous account number
misc_department char(3),
Miscellaneous department
misc_amount decimal(10,2),
Miscellaneous amount
misc_debit_credit char(2),
Miscellaneous "CR" or "DB" (credit or debit)
ar_acct_no integer,
Accounts Receivable account number
ar_department char(3),
Accounts Receivable department
ar_amount decimal(10,2),
Accounts Receivable amount
ar_debit_credit char(2),
Accounts Receivable "CR" or "DB" (credit or
debit)
ok_to_post char(1),
Y - edit list has been printed for this doc.
N - document has been modified since last edit list was printed
recurr_ref (10),
Reference code for Credit/Debit memo
gross_entry char(1),
Flag: use gross entry for initial price entry
currency_code char(3),
Defined code for use in multi-currency
(For Multicurrency module)
curr_ex_rate decimal(16),
Units per one home_curr unit exchange
(For Multicurrency module)
home_curr_amount decimal(12)
Amount of transaction in home currency
(For Multicurrency module)
batch_id integer
Batch ID
orig_journal char(2),
trans_doc_no integer,
doc_date date
Used to determine what accounting period to post to
index i1rinvce on strinvce (doc_no)
index i2rinvce on strinvce (cust_code,inv_no)
index i3rinvce on strinvce (inv_no)
index i4rinvce on strinvce(orig_journal, trans_doc_no)
--------------------------------------------------------------------r
strinvcd - Invoice Entry Detail Table
doc_no integer not null,
Document number
line_no smallint,
Line number (for sorting purposes)
acct_no integer,
Account number
department char(3),
Department
amount decimal(10,2),
Amount
debit_credit char(2),
"DB" or "CR" (debit or credit)
item_no char(8),
Item number to show on invoice
quantity float,
Quantity
pack char(6),
Unit (pack) description
description char(20),
Line item description
price decimal(10,2)
Price per
mtax_code char(6)
Tax code applied (multi-tax form)
index i1rinvcd on strinvcd (doc_no,line_no)
-----------------------------------------------------------------------
strletrd - Collection Letter Detail Table
letter_code char(8) not null,
Code for letter type
line_no smallint not null,
Line number
text char(65)
Actual letter text
unique index i1rletrd on strletrd (letter_code, line_no)
constraint (foreign key (letter_code) references strletre constraint
c1rletrd)
-----------------------------------------------------------------------
strletre - Collection Letter Header Table
letter_code char(8) not null,
Code for letter type
description char(30),
Description of letter type
due_days smallint not null,
# days payment is past due for letter type
credit_hold char(1) not null
[Y/N]? Is the letter type 'Credit Hold'
unique index i1rletre on strletre (letter_code)
constraint primary key (letter_code) constraint c1rletre
-----------------------------------------------------------------------
strletsd - Collection Letter Detail Table
letter_code char(8) not null,
special_chars char(4) not null,
data_source char(255) not null
index i1rletsd on strletsd (letter_code, special_chars)
constraint (foreign key (letter_code) references strletre constraint
c1rletsd)
-----------------------------------------------------------------------
strlettd - Collection Letter Detail Table
cust_code char(20) not null,
letter_code char(8) not null,
letter_date date not null
index i1rlettd on strlettd (cust_code, letter_code, letter_date)
constraint (foreign key (letter_code) references strletre constraint
c1rlettd)
-----------------------------------------------------------------------
strshipr - Ship-To Reference Table
cust_code char(20),
Customer reference code
ship_to_code char(6),
Customer ship to code
bus_name char(30),
Ship to name
taxable char(6),
Is this ship-to address taxable ? Y/N
contact char(20),
Contact person
phone char(20),
Contact phone
address1 char(30),
Address line 1
address2 char(30),
Address line 2
city char(20),
City
state char(2),
State
zip char(10),
Zip
country char(20),
Country
sls_psn_code char(6),
Salesperson code (for order entry)
trd_ds_code char(6),
Trade discount code (for order entry)
st_tx_code char(6), Obsolete field - see multilevel tax code
State sales tax code (for order entry)
co_tx_code char(6), Obsolete field - see multilevel tax code
County sales tax code (for order entry)
ci_tx_code char(6) Obsolete field - see multilevel tax code
City sales tax code (for order entry)
comm_code char(6)
Commission code for sales to this ship-to
mtax_freight char(6)
Multilevel tax code for freight
mtax_misc char(6)
Multilevel tax code for miscellaneous
ship_via_cd char(6)
Ship via code
ship_terms char(15)
email char(50)
web_address char(50)
cell_phone char(20)
fax_phone char(20)
residential_cust char(1)
[Y/N]?
unique index i1rshipr on strshipr (cust_code,ship_to_code)
-----------------------------------------------------------------------
strtermr - Customer Terms Code Reference Table
terms_code char(6),
Terms code
terms_desc char(30),
Terms description
due_days smallint,
Number of days until due
disc_days smallint,
Number of days for discount
disc_pct smallfloat
Discount percentage
terms_type char(1)
Terms Type
fix_due_days smallint
Fix due Days
cut_off_day smallint
Number of cut off days
-----------------------------------------------------------------------
strtranv - Accounts Receivable Document View
select
stxtranr.orig_journal, char(2)not null #original journal
stxtranr.doc_no, integer not null #document number
stxtranr.post_no, integer #post number
stxtranr.post_date, date #post date
stxtranr.doc_date, date not null #document date
stxtranr.ref_code, char(20) #reference code
stxtranr.doc_desc, char(30) #document description
strtranr.inv_chk_no, char(10) #invoice or check number
strtranr.doc_type char(2) #document type
from stxtranr, strtranr
where stxtranr.orig_journal = strtranr.orig_journal and
stxtranr.doc_no = strtranr.doc_no;
-----------------------------------------------------------------------
stractvv - Accounts Receivable Document Activity View
select
stxtranr.orig_journal, char(2) not null #original journal
stxtranr.doc_no, integer, not null #document number
stxtranr.post_no, integer #post number
stxtranr.post_date, date #post date
stxtranr.doc_date, date #document date
stxtranr.ref_code, char(20) #reference code
stxtranr.doc_desc, char(30) #doc description
strtranr.inv_chk_no, char(10) #invoice check #
strtranr.doc_type, char(2 #document type
stractvd.act_type, char(1) #activity type
stractvd.inv_doc_no, integer #invoice document#
stractvd.amount decimal (12) #amount
from
strtranr, stxtranr, outer stractvd
where
strtranr.doc_no = stxtranr.doc_no and
strtranr.doc_no = stractvd.doc_no and
strtranr.orig_journal = stxtranr.orig_journal and
strtranr.orig_journal = stractvd.orig_journal;
-----------------------------------------------------------------------