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

deposit_amt decimal(12,2)

Deposit amount

deposit_acct integer

Deposit account number

deposit_department char(3)

Deposit department

deposit_deb_cred char(2)

Deposit: CR - Credit or DB - Debit

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")

deposit_amt decimal(12,2),

Deposit amount

cdepr_doc_no integer,

Deposit document number

order_doc_no integer

Deposit order document number

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

cc_doc_no integer

Last Credit Card Doc Number used by Customer Credit Card Auth.

Used by ar:i_ccard to send a unique doc_no to Skipjack

bank_deposit_id integer

Last bank deposit Id used by Deposit program

fin_chg char(1),

Default finance charge flag

cust_assign char(1),

Auto Assign Customer Code

cust_next integer

Next customer code in auto assign process

ship_to_assign char(1),

Auto Sequence Ship To Code

ship_to_next integer

Next Ship To Code

credit_manager char(8),

Credit Manager

credit_check char(1),

Credit Check

over_credit_pct decimal(4,2),

Over Credit Percentage

hold_code char(6),

Hold Code

auto_invce_print char(1),

Real Time Processing Invoice Print

auto_invce_post char(1),

Real Time Processing Invoice Post

auto_rcpt_post char(1),

Real Time Processing Receipt Post

auto_deposit_print char(1),

Real Time Processing Deposit Print

auto_deposit_post char(1),

Real Time Processing Deposti Post

auto_invce_post_d char(1),

Real Time Processing Invoice Post

auto_rcpt_post_d char(1),

Real Time Processing Receipt Post

auto_dep_post_d char(1)

Real Time Processing Deposit Post

-----------------------------------------------------------------------

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(2),

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]?

deposit_amt decimal(12,2)

Deposit Amount

route_code char(10)

Route Code

resale_no char(15),

Resale number

resale_expiry date

Resale Expiry Date

discount_level char(1)

Item Price Level

ytd_sales decimal(14),

YTD Sales

lifetime_sales decimal(14)

Lifetime Sales

label1 char(30),

Phone Label 1

phone1 char(20),

Phone Number 1

label2 char(30),

Phone Label 2

phone2 char(20),

Phone Number 2

label3 char(30),

Phone Label 3

phone3 char(20),

Phone Number 3

label4 char(30),

Phone Label 4

phone4 char(20),

Phone Number 4

label5 char(30),

Phone Label 5

phone5 char(20),

Phone Number 5

label6 char(30),

Phone Label 6

phone6 char(20),

Phone Number 6

label7 char(30),

Phone Label 7

phone7 char(20),

Phone Number 7

label8 char(30),

Phone Label 8

phone8 char(20),

Phone Number 8

label9 char(30),

Phone Label 9

phone9 char(20),

Phone Number 9

label10 char(30),

Phone Label 10

phone10 char(20)

Phone Number 10

split_terms_code char(6);

Split Payment Terms

account_type char(25),

Account Type

account_source char(25) );

Account Source

warehouse_code char(10)

Default Warehouse Code By Customer

duns_no char(9),

Customer Credit Management - Duns Number

active char(1),

Customer Credit Management - Active

acct_established date,

Customer Credit Management - Account Established

collection_contact char(30),

Customer Credit Management - Collection Contact

collection_phone char(20),

Customer Credit Management - Collection Phone

credit_check char(1),

Customer Credit Management - Credit Check

over_credit_pct decimal(4,2),

Customer Credit Management - Over Credit Percentage

credit_approve_dt date,

Customer Credit Management - Credit Approve

credit_approve_by char(8),

Customer Credit Management - Credit Approve By

avg_pay_days smallint,

Customer Credit Management - Average Pay Days

last_payment decimal(12,2),

Customer Credit Management - Last Payments

open_orders decimal(12,2),

Customer Credit Management - Open Orders

hold_code char(6),

Customer Credit Management - Hold Code

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

deposit char(1)

customer deposits (Y)es or (N)o

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(20),

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

order_doc_no integer

Deposit order document number

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

pay_method char(6),

Payment Method

card_name char(20),

Credit Card Name (VISA, MC)

card_number char(30),

Last four digits of the credit card number

auth_amt decimal(10,2),

Authorization amount

auth_code char(8),

Authorization Code

auth_date date,

Authorization Date

decline_code char(8),

Authorization Decline Code

decline_message char(60),

Authorization Decline Message

settle_decl_code char(8),

Settle Decline Code

settle_decl_mssg char(60),

Settle Decline Message

cc_batch_id char(12),

Authorization Batch Id

cc_batch_name char(12),

Authorization Batch Name

settled char(1),

Has invoice been settled?

trans_ref_no char(40),

Transaction Id generated by Skipjack

cc_s_batch_id char(12),

Settlement Batch Id

cc_s_batch_name char(12)

Settlement Batch Name

inv_printed char(1)

Invoice Printed? Y/N

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(14,4)

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'

minimun_due decimal(10,2)

Minimun Due

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(2),

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]?

route_code char(2)

Route Code

warehouse_code char(10)

Default Warehouse Code by Ship To used in Sales Order

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;

-----------------------------------------------------------------------

strcdepr - Cash Deposits

doc_no integer, -- Join strcashe.doc_no

cust_code char(20),

order_no char(20),

order_doc_no integer,

contract_no char(20),

check_no char(10),

deposit_amt decimal(12,2),

reversal_doc_no integer

create index "informix".i1rcdepr on strcdepr (doc_no);

create index "informix".i2rcdepr on strcdepr (order_no);

create index "informix".i3rcdepr on strcdepr (contract_no);

-----------------------------------------------------------------------

strccard - Customer Credit Card

cust_code char(20) not null, # Costomer Code

pay_method char(6) not null, # Payment Method (CCARD, DDEBIT)

card_name char(20), # Card Name (VISA, MC)

card_number char(20), # Last four digits credit card number

acct_no char(30), # DDEBIT Account Number

route_no char(30), # DDEBIT Router number

exp_mo char(2), # Expiration Month

exp_year char(4), # Expiration Year

first_name char(20), # First Name

middle_initial char(1), # Middle Initial

last_name char(30), # Last Name

address1 char(30), # Address

address2 char(30), # Address

city char(20), # City

state char(2) , # State

zip char(10), # Zip Code

country char(20), # Country

phone char(20), # Phone

email char(50), # Email

fax char(20), # Fax

primary_card char(1) not null, # Is the primary card?

trans_ref_no char(40), # Transaction Id generated by Skipjack

social_security char(20), # Social Security (DDEBIT)

license char(20), # License (DDEBIT)

license_state char(2), # License State (DDEBIT)

cc_doc_no char(20) # Unique doc_no send to Skipjack

create index "informix".i1rccard on strccard (cust_code, card_name, card_number);

alter table strccard lock mode(row);

-----------------------------------------------------------------------

strbnkdr - Bank Deposit Header

deposit_date date not null, # Deposit Date

bank_deposit_id integer not null, # Bank Deposit ID (Unique)

bank_code char(10) not null, # Bank Code

branch char(10) not null, # Branch

deposit_total decimal(12,2), # Deposit Total

posted char(1) # Posted (Y/N)

create unique index "informix".i1rbnkdr on strbnkdr (bank_deposit_id);

create index "informix".i2rbnkdr on strbnkdr (bank_code, branch, deposit_date);

-----------------------------------------------------------------------

strbnkdd - Bank Deposit Detail

bank_deposit_id integer not null, # Bank Deposit Id (join with header)

include char(1), # Include (Y/N)

post_no integer, # Post Number

amount decimal(12,2) # Amount

create index "informix".i1rbnkdd on strbnkdd (bank_deposit_id);

-----------------------------------------------------------------------

strcommd --- FR2522 - Add new table for COMMISSION CALCULATION BY CUSTOMER

cust_code char(20), # Customer code

item_code char(20), # Item Code

begin_price decimal(14), # Begin Price

end_price decimal(14), # End Price

comm_pct decimal(14) # Commission Percentage

-----------------------------------------------------------------------

strcrhdd -- FR3323 - Customer Credit Management

cust_code char(20), # Customer Code

ag_current decimal(12), # Current Aging

ag_over_30 decimal(12), # Over 30

ag_over_60 decimal(12), # Over 60

ag_over_90 decimal(12) # Over 90

-----------------------------------------------------------------------

strcrhde -- FR3323 - Customer Credit Management

hold_code char(6), # Hold Code

description char(50), # Description

hold_wo char(1), # Hold WO

hold_po char(1), # Hold PO

print_opicker char(1), # Print picker

update_pqtys char(1), # Update pick qty

update_sqtys char(1), # Updte qty

print_oshipr char(1), # Print oshipr

create_invoice char(1), # Create Invoice

print_oinvce char(1) # Print Invoice