Purchasing Tables
=================
----------------------------------------------------------------------
stucntrc Purchasing Control Table
exempt_tax_code char(6),
** not used at present time.
To be used as default tax group code in Multicurrency
computations.
frght_tax_code char(6),
Tax group code to be used as default for computing sales tax
on freight charges. If this field is null then no tax will
be computed on freight.
misc_tax_code char(6),
Tax group code to be used as default for computing sales tax
on miscellaneous charges. If this field is null then no tax
will be computed on miscellaneous.
mtaxg_code char(6),
Multilevel Tax group code to be used as default for
computing sales tax.
gross_entry char(1),
** not used at present time.
entry_by_line char(1),
** not used at present time.
whse_shipto char(10),
Default ship-to warehouse code. This will be the main
receiving location for purchases.
whse_billto char(10),
** not used at present time.
po_type char(3),
Default purchase order type. Must be a type defined in
stuotypr. At present this is limited to REG - regular
purchase orders.
retention_days smallint,
** not used at present time.
This will be the number of days to retain documents in
active tables after they have reached a completed stage.
After this time documents are available to be archived.
eta_days smallint,
** not used at present time.
Default estimated number of days till arrival of goods. This
value is used to compute the expected arrival date of items on
the order. It is used for non-inventory items and for items
where there is no value in the eta_days column for the vendor or
pay-to.
cm_reason char(3),
** not used at present time.
Default credit memo reason code. This value is used to pre-fill
the reason code field for detail lines on credit memos. It
should be filled with the most common credit memo reason.
dm_reason char(3),
** not used at present time.
default debit memo reason code. this value is used to pre-fill
the reason code field for detail lines on debit memos. see the
explanation for cm_reason above.
terms_code char(6),
**not used at present time.
used for defaulting the terms in purchase order entry.
ap_acct_no integer,
Default A/P account for A/P purchases. The value in this column
is used by the purchasing posting program (p_pstord) when
posting a document to A/P. It is used if there is no account
number specified for the vendor.
disc_acct_no integer,
Default discounts taken account. It is used to post
discounts taken on purchases.
frght_acct_no integer,
Default freight-in account number. It is used to post
freight charges paid on purchases.
misc_acct_no integer,
Default miscellaneous purchase charges account number.
inv_acct_no integer,
This field stores the default Inventory (asset) account to be
used when goods are received into inventory. It will be
overridden with the STK Line Type default account number if
one has been specified.
adj_acct_no integer,
Inventory holding account used until invoicing
prepaid_acct_no integer,
** not used at present time.
supp_acct_no integer,
This is the default account number for supplies purchases.
Supplies purchases are identified by choosing the SUP line
type.
Note that each Line Type also has a default account number,
and that the Line Type account number will be used as a
default if it exists. In general it is better to specify
the account defaults with the Line Types than to specify
them here.
cap_acct_no integer,
The default account number for capital purchases. Capital
purchases are identified by choosing the CAP line type.
non_acct_no integer,
This is the default account number for other NON-stock
purchases. These are identified by choosing the NON line
type.
diff_acct_no integer,
This account will be used to track differences between the
original PO amounts and the final invoiced amounts.
cash_acct_no integer,
Default cash account for cash purchases. The value in this
column is used by the purchasing posting program (p_pstord) when
posting a cash transaction. It is only used if the cash_acct_no
column from the vendor (stpvendr) row contains a null value.
buyer_code char(6),
Default buyer or purchasing agent. This must be one of the
valid buyer codes and will be used if no buyer is specified
for a purchase order.
price_tolerance decimal(10),
During the invoicing phase of the purchasing cycle it may
occur that the invoiced price for a particular item is
different from the original order price. The percentage
entered here determines the maximum allowable difference
that will be accepted when an invoice price is modified.
Setting this tolerance allows for some editing control to
eliminate data entry errors.This tolerance can be overridden
during invoice entry.
line_type char(3),
Default type of item to purchase if no line type is specified
during requisition or order entry.If most of your purchases
are of inventory items you will probably enter STK here.
use_department char(1),
Use order dept for asset/liability (Y/N). This indicates to
the purchasing posting program (p_pstord) whether assets, such
as inventory; or liabilities, such as accounts payable, should
use the department specified for the order.
req_doc_no integer,
Next automatic sequential document number to be used for
requisition documents.
po_doc_no integer,
Next automatic sequential document number to be used for
purchase order documents.
rec_doc_no integer,
Next automatic sequential document number to be used for
receipt documents.
inv_doc_no integer,
Next automatic sequential document number to be used for
invoice documents.
req_post_no integer,
Next automatic sequential posting number to be used when
converting requisitions to purchase orders.
rec_post_no integer,
Next automatic sequentail posting number to be used when
posting receipt documents.
inv_post_no integer,
Next automatic sequentail posting number to be used when
posting invoice documents.
merge_requisitions char(1)
** not used at present time.
ship_via char(15)
Default ship via code
fob_point char(15)
Default FOB point code
print_notes char(1)
Print notes on purchase order?
use_batch_rec char(1)
Use batch control for Purchasing Receipts
use_batch_inv char(1)
Use batch control for Invoices
use_approv_post char(1)
Use Approval code to post
approval_code char(8)
Approval Code to Post
----------------------------------------------------------------------
stultypr Purchase Order Line Types
When requesting or ordering items for purchase it is necessary to
identify to the system which type of item is being purchased. This
is accomplished by choosing one of the valid line types.
line_type char(3),
The valid pre-defined line types are:
SUP - supplies purchases
SER - services purchases
CAP - capital purchases
NON - other non-stockkeeping purchases
STK - inventory (or stock) purchases
line_desc char(30),
This is an short description of the type of purchase line.
gl_acct_no integer,
Each line type has a default general ledger account number
specified.
line_item_type char(1),
There are five item types, corresponding to the pre-defined
line types.
U - Supplies
E - Services
C - Capital Expenditures
N - Other non-inventory purchases
S - Inventory or stock items
You must choose one of these options for any new line types.
This will provide a line type similar in behavior to the
selected Item Type.
update_description char(1),
Entering Y in this field allows the user to modify the
description of the item being ordered during entry, for this
line type. If this field is N or left blank the user will not
be allowed to modify item descriptions.
update_price char(1)
Use this field to indicate whether or not the user should be
allowed to override the unit cost defaulted from the
vendor-item catalog for a particular item. An entry of Y will
allow this change to be made, an entry of N will force the user
to accept the existing catalog cost for the item for this
particular vendor.
unique index on (line_type);
----------------------------------------------------------------------
stuotypr Purchase Order Types Reference
po_type char(3),
Code uniquely identifying each order type. At this time there
is only one fully implemented order type:
REG - regular purchase order
Future order types will include:
QUO - quotes
RCR - recurring purchases
ACC - accumulative orders
BLO - blanket orders
description char(30),
Short description of order type.
vend_check char(1),
** not used at present time.
master_ord char(1),
** not used at present time.
part_relse char(1),
** not used at present time.
accumulate char(1),
** not used at present time.
ord_post char(1),
** not used at present time.
process_hold char(1),
** not used at present time.
reqdate char(1),
** not used at present time.
lead_calc char(1),
** not used at present time.
print_rec char(1),
** not used at present time.
print_ack char(1),
** not used at present time.
print_po char(1),
Y/N flag indicating if a purchase order is to be printed for
this order type.
no_cost_print char(1),
Y/N flag indicating if cost and value details are to be
printed on the purchase order.
post_to_ledgers char(1)
Y/N flag indicating if the Order Type posts to General Ledger
Accounts Payable, and Inventory.
unique index on (po_type);
----------------------------------------------------------------------
stuctlgd Vendor/Item Catalog Detail
This table stores approved vendor codes and the items approved for
purchase from each vendor. The same item may be approved for
purchase from multiple vendors.
vendor_code char(20), REQUIRED
Code for approved vendor.
item_code char(20), REQUIRED
Code for approved purchase item.
cost decimal(12), REQUIRED
Current cost of this item from this vendor.
vend_item_code char(20)
Vendor's item code alias for your internal item code.
primary_vendor char(1)
This is a Y/N column. It contains a Y if the
vendor is the primary vendor. This column is used
by the direct ship feature of order entry. If there
is a Y in the primary_vendor column, this vendor
is used for direct shipments.
currency_code char(3) For use with Multicurrency Module.
Currency of the item price originally determined by the
currency code of the vendor (stpvendr.currency_code).
The amount stored in stuctlgd is in the foreign currency.
The amount is translated to the home currency using the
rate type setup for purchasing via update multicurrency
defaults(stmcntrc).
line_code char(6)
line code
unique index on (vendor_code, item_code);
index on (item_code);
index on (vend_item_code);
----------------------------------------------------------------------
sturqste Requisition Header Table
doc_no integer,
Unique document number assigned by system. Next number is
found in stucntrc table.
requestor_code char(6),
Code for creator of this requisition.
whse_shipto char(10),
Warehouse ship-to address code. This code defaults from the
ship-to code assigned to the requestor. Each requisition
detail line will default to this ship-to code.
whse_billto char(10),
** not used at present time.
po_type char(3),
This field defaults to REG.Only regular purchase orders can
be crerated from requisitions.
request_status char(3),
Status reflects the lowest stage of the individual requisition
lines.Data entry to document is on;y allowed when status is
REQ. See line_stage field in sturqstd table for further
details.
request_no integer,
This is a free-form field for entry of any user-assigned
requisition identifier.
request_date date,
The requisition date, which defaults to the surrent date.
required_date date,
The date by which the requestor requires delivery of the
items being requested.
authorization_code char(6)
Valid authorization code entered to approve this requisition.
----------------------------------------------------------------------
sturqstd Requisition Item Detail
doc_no integer,
Document number from header portion of requisition document,
used for join purposes.
line_no smallint,
Unique system assigned requisition line number.
line_type char(3),
Code identifying type of purchase item. See
stultypr.line_type for more information.
line_stage char(3),
System maintained stage of each item detail line. Possible
stages are:
REQ - new requisition detail line
AUT - line has been authorized
LCK - line has been approved and vendor is assigned
ORD - lines has been converted to purchase order
CAN - line has been canceled
item_code char(20),
Code for item being requested on this line. This must be a
valid pre-assigned item code.
desc1 char(30),
desc2 char(30),
Two lines of description are available for each detail line.
These lines default from the description entries in item
table (stiinvtr).
unit char(2),
Purchase unit for the item. This unit comes from the
vendor-item catalog (stuctlgd). It cannot be modified at
requisition entry time.
ordr_qty decimal(12),
The number of purchase units being requisitioned.
instruct_code char(6),
A special handling instructions code.The reference table for
instructions codes is not currently available.
reference_no char(13),
This is a free-form field for entry of any user-assigned
requisition identifier.When requisitions are created from OE
sales orders this field is filled with a reference to the
originating sales order.
whse_shipto char(10),
Warehouse ship-to address code for shipment of requested
goods. Presently, this field will contain the default
whse_shipto code from the requisition header. In a future
release it will be possible to assign differnet shipto
locations for each requisition detail line.
whse_billto char(10),
** not used at present time.
vend_code char(20),
Code for the approved vendor from which item will be
purchased. Requestor does not need to be responsible for
assigning this code, although if only one approved vendor
exists for an item the code will be assigned automatically.
Otherwise,vendor must be assigned in the "Assign
Vendors"
process.
requestor_code char(6),
Will contain the requistion header requestor code. (?)
request_no char(10),
Filled from requisition header field of same name.
request_date date,
Filled from requisition header field of same name.
authorization_code char(6),
Filled from requisition header field of same name.
acct_no integer,
General ledger account number for posting of this item.This
number will default from the line type. The number can be
changed after requisition line has been transferred to a
purchase order.
req_post_no integer,
This system assigned sequential posting number is filled when
the requisition line is comverted to a purchase order.
po_doc_no integer,
This is the purchase order doc_no to which the requisition was
transferred.
po_line_no smallint,
This is the line number of purchase order doc_no to which the
requisition was transferred.
recv_qty decimal(12),
When ordered items are received this field is posted with the
received quantity.
The following four fields are filled when a requisition is created
from a sales order.
ref_type char(2),
This field will contain OE.
ref_doc_no integer,
The Order Entry sales order document number will be posted to
this field.
ref_line_no integer,
The Order Rntry sales order line number will be posted to this
field.
ref_ship_no integer
The Order Entry shipment number will be posted to this field.
----------------------------------------------------------------------
stuordre Purchase Order Document Header
This table is where header, default, and total document information
is stored for purchasing documents. Rows are added to this table
(one per document) by two programs. The requisition posting program
(p_autopo) adds rows to this table as it posts authorized requisitions.
The purchase order entry program (i_order) adds a row for each purchase
order a user enters.
doc_no integer, REQUIRED
Document number, assigned when the document is created. If the
document is canceled, it is marked as canceled rather than
being deleted. Thus, document numbers are not lost.
orig_doc_no integer,
When a portion of a blanket order is released a new order is
created. This field contains the doc_no of the master
document for the blanket order.
po_no char(10), REQUIRED
Purchase order or memo number, assigned by the user. If left
null, the program assigns doc_no to po_no. This field is not
validated and there is no checking for duplicates.
buyer_code char(6),
optional buyer code. used to put buyer code in stutranr record
for purchasing reporting. the buyer code is defined in
stxinfor, src_type = "B".
po_type char(3), REQUIRED
Code uniquely identifying each order type. At this time there
is only one fully implemented order type:
REG - regular purchase order
po_date date, REQUIRED
date order is accepted. defaults to entry date. this is
use to calculate the expected receipt date.
po_status char(3),
Display status of order for user. Not used for control
purposes. Possible codes: ACTive/ORDered/CANcelled
po_stage char(3), REQUIRED
Stage reflects the lowest stage of the individual purchase
order lines. Data entry to document is only allowed when
status is ORD. See line_stage field in stuordrd table for
further details.
complete_date date, REQUIRED
The system maintains the date when the order has been
completed. This occurs when invoicing is complete, or when
all non-invoiced order lines have been cancelled.
required_date date,
The date goods are required for this order can either be
entered directly into the order,or have been transferred
from a requisition.
whse_billto char(10),
** not used at present time.
whse_shipto char(10), REQUIRED
Warehouse shipto address code.
department char(3), REQUIRED
This department code is used by the receipt and invoice
posting programs to determine which department code to use
when posting to the general ledger.
mtaxg_code char(6), REQUIRED
Tax group code to be used as default for computing sales tax
on purchases. Required if taxes are to be computed for
invoice.
vend_code char(20), REQUIRED
Vendor code for this purchase.If purchase order was created
from requisitions this will already be assigned,otherwise it
must be chosen from among the approved vendors.
pay_to_code char(6), REQUIRED
Purchase address code for the vendor. It defaults to PAYTO.
If there exists a pay-to record for this vendor which has
PAYTO as the code, then the pay-to information will be retrieved
from that record. If such a pay-to record does not exist,
then the information from the vendor record will be used.
If the operator enters any other code, there must exist a
pay-to recorde with that code.
bus_name char(30), REQUIRED
Business name of vendor for the purchase order.
order_doc_no integer,
The sales order doc_no for drop ship orders converted to
purchase orders.
cust_code char(20),
When drop ship sales orders are converted to purchase orders
the drop ship customer code is posted to this field.
order_no_vnd char(10),
Vendors order number.
order_reference char(13),
Used for sales order document and line numbers for orders
converted to purchase orders.
currency_code char(3), (For use with Multicurrency Module)
Currency of the purchase order determined by the currency
code of the vendor (stpvendr.currency_code). This column is null
if not using multicurrency, but required if using multicurrency.
ord_printed char(1), REQUIRED
This field defaults to N and is changed to Y after the
purchase order has been printed.This is a system maintained
field.
total_weight decimal(12),
The total weight of the order, computed as the sum of the
weights stored in the item table.
item_amount decimal(12),
** not used at present time.
discountable decimal(12), REQUIRED
Total of order amounts that are discountable. Items in lines
may or may not be discountable (this is set in the item row
in the inventory control module).
trd_ds_amount decimal(12), REQUIRED
Amount of the trade discount for the order. Computed by taking
the discountable amount multiplied by the trd_ds_pct. This
amount is deducted from the order total.
tax_amount decimal(12), REQUIRED
Total of order amounts that are taxable. Also, freight and
miscelleneous may or may not be taxable. This is set in the
purchasing control table.
frght_amount decimal(12), REQUIRED
Freight cost expected for this purchase order. The amount is
entered by the operator.
misc_amount decimal(12), REQUIRED
Miscellaneous expected costs for purchase order. The amount
is entered by the operator.
goods_amount decimal(12), REQUIRED
Total of extended amounts from all order lines.
total_amount decimal(12), REQUIRED
Order total amount. This consists of this sum:
goods_amount +
trd_ds_amount -
tax_amount +
frght_amount +
misc_amount +
prepay_amount decimal(12),
** not used at present time.
freight_terms char(6),
** not used at present time.
create_date date,
The date the order was first entered.
create_time char(8),
The time the order was first entered.
create_id char(8),
The user id of the person who first entered the order.
l_mod_date date,
The date the order was most recently modified or billed.
l_mod_time char(8),
The time the order was most recently modified or billed.
l_mod_id char(8)
The user id of the person who most recently modified or billed
the order.
curr_rate_type char(6) (For use with Multicurrency Module).
This field is not currently used.
The rate type to use for the order is stored here.
Initially this rate type would be the same rate type as is
stored in stmcntrc.pu_rate_type but later on we may want to
provide the flexibility to change the rate type on a transaction
by transaction basis. This column is null if not using multicurrency
in Purchasing.
currency_rate decimal(16) (For use with Multicurrency Module).
Actual exchange rate used for pricing of the pruchase
order. All amounts on the purchase order appear in the
foreign currency. The currency_rate is the exchange rate
used when the purchase order is initially entered. The
rate is retrieved by the rate type, date, and currency
code. It is required that an exchange rate exists for a
currency in order for a purchase order to be entered. This
column is null if not using multicurrency, but required
if using multicurrency.
terms_code char(6)
Default from the vendor and if vendor's term is
null then use the purchasing control's term code.
This is used for printing of the purchase order and
for defaulting of the vendor invoice.
misc_tax_code char(6)
Multicurrency tax code for miscellaneous charges
misc_act_no integer
General Ledger account code for miscellaneous charges
misc_department char(3)
General Ledger department code for miscellaneous charges
frght_tax-code char(6)
Multicurrency tax code for freight charges
frght_acct_no integer
General Ledger account code for freight charges
frght_department char(3)
General Ledger department code for freight charges
confirmed_to char(20
Name of person confirmed order
reprint_no smallint
Reprint Number
fob_point char(15)
FOB Point
ship_via char(15)
Ship via
confirm_date date
on_board_date date
unique index on (doc_no);
----------------------------------------------------------------------
stuordrd Purchase Order Line Item Detail
doc_no integer, REQUIRED
Document number, assigned when the document is created.
If the document is canceled, it is marked as canceled
rather than being deleted. Used as join criteria with PO
header.
line_no smallint, REQUIRED
System-generated unique line number for this order line.
cm_dm_reason char(3),
Credit/Debit reason code.
mtaxg_code char(6), REQUIRED
Multilevel Tax group code for this order line.
line_type char(3), REQUIRED
Line type for this order line. See stoltypr for futher
information.
line_stage char(3), REQUIRED
Processing stage for this order line.
ORD - entry: Order information for line can be changed.
POG - noentry: Purchase order printed
REC - noentry: Line fully received
INV - noentry: Line fully invoiced
CAN - noentry: Line cancelled
receiver_printed smallint,
Number of times receipt has been printed.Not currently used
by system.
request_date date,
Requisition date for this line item.
po_date date, REQUIRED
Date of purchase order. Duplicated information from PO
header.
rcpt_date date,
Date of last receipt for this line item.
inv_date date,
Last invoicing date for this line item.
required_date date,
Required date for receipt of this item.
whse_shipto char(10), REQUIRED
Ship-to warehouse code for this line item.
whse_billto char(10),
Not currently used by system.
item_code char(20), REQUIRED
Item code for this purchase line item.
desc1 char(30), REQUIRED
First description line for this item.
desc2 char(30),
Second description line for this item.
td_disc_allowed char(1),
Trade discount allowed flag as set in stpvendr (vendor file)
bo_allowed char(1),
Backorder allowed flag as set in stpvendr (vendor file).Not
currently used for control purposes by system.
ordr_qty decimal(10), REQUIRED
Quantity if the item being ordered on this line. In
purchasing units.
rlse_qty decimal(10),
Not currently used by system.
rjct_qty decimal(10),
Quantity rejected during receipt process.
recv_qty decimal(10), REQUIRED
Quantity of this line item received to date.
cost_qty decimal(10), REQUIRED
Quantity of this line item invoiced (costed) to date.
acpt_qty decimal(10),
Not currently used by system.
exp_rec_qty decimal(10), REQUIRED
Expected quantity remaining to be received. Equals ordr_qty
before any receipts and 0 when the line has been fully
received.
exp_inv_qty decimal(10), REQUIRED
Expected quantity remaining to be invoiced. Equals recv_qty
if no quantity has been invoiced.
sell_unit char(2),
Selling unit for this item. Not currently used.
purch_unit char(2), REQUIRED
Purchase unit for this line item.
stock_unit char(2),
Stocking unit for this item.
unit_factor decimal(6),
Not currently used.
cost decimal(12), REQUIRED
Unit cost for this line item.
gl_acct_no integer, REQUIRED
General ledger account number for posting of this purchase
line.
net_price decimal(10), REQUIRED
Extended cost * quantity
department char(3), REQUIRED
Accounting department for posting of this purchase line.
Required if use_department is active.
instruct_code char(6),
Free-form: handling instructions code.
authorization_code char(6),
Not currently implemented.
inspection_code char(6),
Not currently implemented.
alias_code char(20),
Vendor item code for this purchase item.
weight decimal(9),
Not currently implemented.
staging_area char(6)
Not currently implemented.
order_doc_no integer
Order Document Number
order_line_no integer
Order Line Number
order_ship_no integer
Order Ship Number
note_flag char(1)
Note Flag
unit_tax decimal(12)
Tax on unit when tax is included (landed cost)
confirm_date date,
on_board_date date
unique index on (doc_no,line_no)
----------------------------------------------------------------------
sturecte Receipt Document Header
rec_doc_no integer, REQUIRED
Unique document number for this receipt. Number is taken
from next rec_doc_no field in purchasing control table.Used
to join with receipt detail.
receipt_date date, REQUIRED
Date of this receipt.
When entering DIRect ship bills of lading this is the date
the vendor shipped the goods to the customer.
receipt_ref char(10),
Free-form reference field.When entering DIRect ship bills of
lading this field is used to store the carrier reference
number.
po_no char(10), REQUIRED
Purchase order number used for selection of PO to receive
against. This is stuordre.po_no NOT stuordre.doc_no. These
two will be the same if user has not filled in po_no field
during data entry.
po_doc_no integer, REQUIRED
Used as join criteria. This is the doc_no of the related
purchase order. Note that this is NOT the po_no of the
purchase order.
ok_post char(1) REQUIRED
Posting control flag set to:
N: upon entry of new receipt line
Y: by receipt edit list process if receipt entry passes
all posting criteria.
P: after receipt has been posted
C: if line has been cancelled
ship_via char(10)
This field is used only for DIRect ship orders generated in
OE. When a bill of lading is received from the vendor this
field is updated with the carrier used to ship the goods to
the customer.
batch_id integer
Batch Control ID
ship_date date
Ship Date
unique index on (rec_doc_no);
----------------------------------------------------------------------
sturectd Receipt of Goods Item Detail
rec_doc_no integer, REQUIRED
Unique document number for receipt.Number is taken from
next rec_doc_no field in purchasing control table.
rec_line_no integer, REQUIRED
Unique line number for this receipt line.
recv_qty decimal(10), REQUIRED
Quantity of the item on this line received on this receipt.
rjct_qty decimal(10), REQUIRED
Quantity of the item on this line rejected.
rjct_code char(10),
Freeform (at this time) code describing rejection reason.
po_doc_no integer, REQUIRED
Used as join criteria. This is the doc_no of the related
purchase order. Note that this is NOT the po_no of the
purchase order.
po_line_no smallint REQUIRED
Used as join criteria.Line number on purchase order releated
to this receipt line. This join is crucial since no item
information is stored in the receipt tables.
unique index on (rec_doc_no,rec_line_no);
----------------------------------------------------------------------
stuinvce Invoice Document Header
inv_doc_no integer not null,
Invoice Document Number. Each invoice is assigned a unique,
sequential number. Non-entry field.
inv_post_no integer,
Invoice Post Number
inv_post_date date,
Invoice Posted Date
pay_to_code char(6),
Vendor pay-to code
description char(20),
General Description of Invoice
inv_date date,
Invoice Date - Defaults to current date
inv_no char(20),
Vendor's invoice number
terms_code char(6),
Terms code on vendor's invoice
pay_date date,
Pay on date. The date the balance will be paid
due_date date,
Due Date. Date payments are due
discount_date date,
Discount Date. Date thru which the discounts are available.
discount_percent decimal(10),
Discount Percent
po_no char(10),
Purchase Order Number. Your PO number that matches this
vendor's invoice.
po_doc_no integer,
Purchase Order Document Number
misc_amount decimal(10),
Total of any miscellaneous costs on this invoice
frght_amount decimal(10),
Total freight on this invoice
goods_total decimal(10),
Total goods amount on this invoice
tax_total decimal(10),
Total tax amount on this invoice
inv_total decimal(10),
Total invoice amount
diff_total decimal(10),
Difference between invoice and Purchase Order
ok_to_post char(1)
Okay to Post?
currency_code char(3) (For use with Multicurrency Module)
Currency of the invoice originally determined by the
currency code of the vendor (stpvendr.currency_code).
This currency code is taken from stuordre instead of
performing a lookup to vendor to plan ahead for this
flexibility. This column remains null if not using
multicurrency but, required if using multicurrency.
curr_rate_type char(6) (For use with Multicurrency Module)
The rate type to use for the invoice is stored
here. This rate type is the same rate type as is stored in
stmcntrc.pu_rate_type. This column remains null if not using
multicurrency but required if using multicurrency.
currency_rate decimal(16) (For use with Multicurrency Module)
Actual exchange rate used for posting to gl. All amounts
on the invoice appear in the foreign currency. The
currency_rate is the exchange rate when the invoice is
initially entered. The rate is retrieved by the rate
type, date, and currency code. It is required that an
exchange rate exists for a currency in order for an
invoice to be entered. This column remains null if not
using multicurrency but required if using multicurrency.
batch_id integer
Batch Control ID
unique index on (inv_doc_no);
----------------------------------------------------------------------
stuinvcd Invoice Document Detail
inv_doc_no integer,
Invoice document number
inv_line_no smallint,
Invoice Line Number - used for sorting
po_doc_no integer,
Purchase Order document number
po_line_no smallint,
Purchase Order line number
cost_qty decimal(10),
Quantity on Invoice. Defaults to quantity received
cost decimal(10,3),
Cost Price. Defaults to price on PO
net_price decimal(10),
Net Price calculated by system. (cost * cost_qty)
gl_acct_no integer,
General Ledger account number to post this line item.
department char(3),
General Ledger department to post this line item.
mtaxg_code char(6),
Multilevel tax code group for line item.
exp_tax_amt decimal(10),
exp_tax_frz char(1)
unique index on (inv_doc_no,inv_line_no);
----------------------------------------------------------------------
stulockr Table Locking Control
Each row in this table contains the notes on how the table is
used during posting, and the effects of locking it during posting.
tabname char(18),
table name
lock_required char(1)
Y/N/T. if "Y" is specified, then during "Post
Receipts" and
"Post Invoices" the specified table will be locked for the
duration of the begin/commit work for each document. The only
reason you would want to lock a table is if you run out of unix
system resources (too many open files, or too many record
locks). If "N" is specified then the lock will not be
attempted
during posting. An "N" means that the entry is for
information
only. If "T" is specified a lock will be attempted during
posting. If you specify that a table should be locked but the
program is unable to lock the table then the posting program
will not execute and the user will see that the lock could not
be executed. If during posting you run out of system resources,
you can do one of 3 things:
1) re-compile your unix kernel for more locks/open files
2) consider moving your database to informix's
"online"
(formerly "turbo") engine. this does not use up
unix
resources for open tables & file locks.
3) mark certain tables for locking at the time of posting
----------------------------------------------------------------------
stuprche Invoicing Price Changes
Occasionally, the invoiced cost for an item will differ from the
originally quoted cost. This table maintains a record of these price
changes to support management reporting and vendor analysis.
inv_doc_no integer not null,
This will be the invoice document number of the invoice
containing a price change.
inv_line_no smallint not null,
The line number on the invoice which was changed.
item_code char(20),
Inventory item code
desc1 char(30),
desc2 char(30),
The description of the price change item at the
time of the invoice are stored.
cost_qty decimal(10,2) not null,
The quantity of the item being invoiced at the new price.
old_price decimal(10,2) not null,
The originally ordered price of the item in question.
new_price decimal(10,2) not null
The price of the item on the vendor invoice. This is the
price actually owed to the vendor and posted to A/P.
----------------------------------------------------------------------
sturqsor Requestor Reference
Each person who will be creating Materials Requisitions must have an
entry in this table. It is used for validating requestors and
authorizers of requisitions.
requestor_code char(6),
Requestor Code
request_desc char(30),
Requestor's name
authorization_code char(6),
whse_shipto char(10),
Requestor's warehouse ship-to location.
bus_name char(30),
**Field not in use
contact char(20),
**Field not in use
phone char(20),
**Field not in use
address1 char(30),
**Field not in use
address2 char(30),
**Field not in use
city char(20),
**Field not in use
state char(2),
**Field not in use
zip char(10),
**Field not in use
country char(20),
**Field not in use
spec_shipping char(50),
**Field not in use
sls_psn_code char(6),
**Field not in use
trd_ds_code char(6),
**Field not in use
approval_level smallint
Requestor's approval level (0-9)
----------------------------------------------------------------------
stupricd Price Header Table
price_code integer # Price Code
disc_qty decimal(10,3) # Discount Qty.
disc_code char(12) # Discount Code
disc_type char(1) # Discount Type
----------------------------------------------------------------------
stuprice Price Detail Table
price_code serial # Price Code
description char(30) # Description
price_level smallint # Price Level
item_code char(20) # Item Code
item_class char(6) # Item Class
vend_code char(20) # Vendor Code
trd_ds_code char(6) # Trade Discount Code
order_type char(3) # Order Type
purch_unit char(2) # Purchase Unit
begin_date date # Begin Date
end_date date # End Date
disc_type char(1) # Discount Type
tolerance_level smallint # Tolerance Level
----------------------------------------------------------------------