Fixed Assets Tables
===================
------------------------------------------------------------------------
stfasstr - asset reference table
asst_code char(6) not null, # asset identification code
asst_desc char(30), # asset description
clss_code char(6), # asset class
vend_code char(20), # vendor code
vend_desc char(30), # vendor description
po_number char(10), # purchase order number
serial_no char(20), # asset serial number if any
in_service date, # date asset was placed in service
depr_acct integer, # depreciation account for the asset
depr_dept char(3), # depreciation department
accm_acct integer, # accumulated depreciation account
accm_dept char(3), # accumulated depreciation department
asset_cost decimal(12,2), # original cost of the asset
cap_expenses decimal(12,2), # total expenses to be capitalized
depr_adjust decimal(12,2), # expenses applied to accum.
# depreciation
salvage_val decimal(12,2), # asset salvage value (default)
retr_date date, # date asset was retired
retr_value decimal(12,2), # reimbursement when asset was retired
retr_expense decimal(12,2), # retirement expenses for the asset
listed_asset char(1), # "listed" property for tax
reporting
bus_use decimal(4,2), # assets business use percentage
warranty_date date, # date that the warranty expires
warranty_desc char(30), # description of the warranty
location char(6) # asset location
------------------------------------------------------------------------
stfdeprd - asset book detail table
asst_code char(6) # asset identification code
# (join column)
book_code char(6) not null, # book reference code
last_depr date, # date depreciation was last
# calculated
last_post date, # date depreciation was last posted
asst_basis decimal(12,2), # asset basis for depreciation
salvage_val decimal(12,2), # asset salvage value
bonus_amt decimal(12,2), # section 179/bonus depreciation
# amount
prior_depr decimal(12,2), # prior depreciation
curr_depr decimal(12,2), # current depreciation
net_value decimal(12,2), # net value of the asset
depr_code char(6), # depreciation schedule identification
# code
limit_code char(6), # optional code to specify limit table
avg_conv char(1), # averaging convention to apply;
# one of
# F - Full Month
# H - Half Month
# M - Mid-Month
# Q - Mid-Quarter
# Y - Half Year (default)
sl_conversion char(1), # flag for conversion to straight line
# Y - convert
# X - converted
manual_val char(1), # flag to mark manually adjusted
# amounts
ok_to_post char(1), # flag to mark okay to post current
# depr.
asst_life decimal(4,1), # asset useful life
ok_to_adjust char(1) # used as part of the adjustments
# report
------------------------------------------------------------------------
stfclssr - asset class reference table
clss_code char(6) not null, # class identification code
clss_desc char(30), # description
cd_dacct integer, # default depreciation acct number
cd_ddept char(3), # default depreciation department
cd_aacct integer, # default accum. depreciation acct
cd_adept char(3), # default accum. depreciation dept
cd_depr char(6), # default depreciation code
cd_limit char(6), # default limit table
cd_life decimal(4), # default asset life
cd_conv char(1) # default averaging convention
------------------------------------------------------------------------
stfbookr - book definition reference table
book_code char(6) not null, # book identification code
book_desc char(60), # description of the book
bd_depr char(6), # default depreciation schedule
last_depr date, # date depreciation was last posted
bd_conv char(1) # default averaging convention
------------------------------------------------------------------------
stfnoter - free form asset notes header table
doc_no serial not null, # join column
asst_code char(6) not null, # asset reference code
note_title char(40), # note title
note_date date # date note was last updated
------------------------------------------------------------------------
stfnoted - asset notes line table (detail)
doc_no integer not null, # join column
line_no smallint, # line nuber for ordering
note_line char(60) # text line
------------------------------------------------------------------------
stfdtabr - depreciation table header
depr_code char(6) not null, # depreciation identification code
depr_desc char(60), # depreciation description
depr_rate decimal(5,4), # general rate (if any)
use_salvage char(1), # flag to use salvage value in
# calculations
internal_flag char(1) # flag for internally defined codes
------------------------------------------------------------------------
stfdtabd - depreciation table rates (detail)
depr_code char(6) not null, # depreciation reference code
year_idx smallint, # year index
period_idx smallint, # period index
depr_rate decimal(5,4) # depreciation rate for year/period
------------------------------------------------------------------------
stfltabr - limit table header
limit_code char(6) not null, # limit table identification code
lim_desc char(60) # description of limit table
------------------------------------------------------------------------
stfltabd - limit table detail
limit_code char(6) not null, # limit table reference code
year_idx smallint, # year index
depr_limit decimal(12,2) # depreciation limit for the year
------------------------------------------------------------------------
stfcntrc - fixed assets control/defaults table
main_book char(6), # book to post to GL and use for
# defaults
fed_book char(6), # book to use for federal tax data
# reports
bns_limit decimal(12,2), # annual limit on section 179 bonus
bns_max decimal(12,2), # annual limit on section 179
# investment
dflt_dacct integer, # default depreciation account
dflt_ddept char(3), # default depreciation department
dflt_aacct integer, # default accum. depreciation acct
dflt_adept char(3), # default accum. depreciation dept
dflt_depr char(6), # default depreciation schedule
dflt_life decimal(4), # default asset life
dflt_conv char(1) # default averaging convention
fa_doc_no integer, # fixed assets document number
fa_post_no integer, # fixed assets posting sequence number
ace_book char(6), # book to use for ace
amt_book char(6), # book to use for alternative
# minimum tax
fiscal_end date # end date for the fiscal year
-----------------------------------------------------------------------
stfsyear - fixed assets short years table
book_code char(6), # main book code
s_start_date date, # start date of short year
s_end_date date # end date of short year
-----------------------------------------------------------------------
stftranr - fixed assets transaction record table
orig_journal char(2), # original journal for the transaction
# (FA)
doc_no integer # transaction document number
------------------------------------------------------------------------
stfactvd - fixed assets activity detail table
orig_journal char(2), # original journal for the transaction
# (FA)
doc_no integer, # transaction document number
book_ref char(6), # book reference code
depr_date date, # depreciation calculation date
depr_amount decimal(12,2), # depreciation amount
depr_dept char(3), # depreciation department
act_status char(1) # activity status
# N-normal, V-voided,
# I-Initial Posting)
------------------------------------------------------------------------
stflocar - fixed assets locations table
location_code char(6), # location code
location_desc char(30), # description for the location
addr1 char(30), # first line of the address
addr2 char(30), # second line of the address
city char(20), # city
state char(2), # state
zip char(10), # zip code
country char(30) # country
------------------------------------------------------------------------
stfmtncd - fixed assets maintenance table
asset_code char(6) not null, # asset code to join to stfasstr
start_date date, # date maintenance work begins
end_date date, # date maintenance work ends
estimate decimal(12), # estimate of maintenance work
actual_cost decimal(12), # actual cost of maintenance work
work_desc char(20) # description of work done
------------------------------------------------------------------------
stfclone - Fixed Asset Clone Header
doc_no serial, not null # Document Number
asst_code char(6) # Asset to be cloned from
------------------------------------------------------------------------
stfclond - Fixed Asset Clone Detail
doc_no integer # Document Number
new_asst_code char(6) # New Fixed Asset Code
asst_description char(30) # New Fixed Asset Description
------------------------------------------------------------------------
stfactvv - Fixed Asset 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 # Date Posted
doc_date date # Document Date
ref_code char(20) # Reference
doc_desc char(30) # Document Description
book_ref char(6) # Book Reference
depr_date date # Depreciation Date
depr_amount decimal(12) # Depreciation Date
depr_dept char(3) # Depreciation Department
act_status char(1) # Activity Status
create view "root".stfactvv (orig_journal,doc_no,post_no,post_date,
doc_date,ref_code,doc_desc,book_ref,depr_date,depr_amount,depr_dept,
act_status) as
select x1.orig_journal ,x1.doc_no ,x1.post_no ,x1.post_date
,x1.doc_date ,x1.ref_code ,x1.doc_desc ,x2.book_ref ,x2.depr_date
,x2.depr_amount ,x2.depr_dept ,x2.act_status from
"root".stftranr
x0 ,"root".stxtranr x1 ,"root".stfactvd 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 ) )
;
-----------------------------------------------------------------------
stftranv - Fixed Asset Transaction View Table
orig_journal char(2), not null # Original Journal
doc_no integer, not null # Document Number
post_no integer # Posting Number
post_date date # Posting Date
doc_date date, not null # Document Date
ref_code char(20) # Reference Code
doc_desc char(30) # Document Description
create view "root".stftranv (orig_journal,doc_no,post_no,post_date,
doc_date,ref_code,doc_desc) as
select x1.orig_journal ,x1.doc_no ,x1.post_no ,x1.post_date
,x1.doc_date ,x1.ref_code ,x1.doc_desc from "root".stftranr
x0
,"root".stxtranr x1 where ((x0.orig_journal =
x1.orig_journal
) AND (x0.doc_no = x1.doc_no ) ) ;
------------------------------------------------------------------------