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

ocean_ins decimal(12)

Ocean shipment insurance rate

init_ord_stage char(3),

Initial Order Stage

rel_hld_auth char(10)

Release Hold Authorization

bko_printpt char(1)

Streamline order entry - BKO print PT?

reb_cog_acct_no integer,

Rebate COG account number

reb_recv_acct_no integer

Rebate Received account number

var_acct_no integer,

Standard costing account

ordr_printpo char(1),

Order Print PO (Real Time Processing)

avl_password char(8),

Approved Vendor List password

avl_required char(1),

Approved Vendor List required

auto_post_rec char(1),

Real Time Processing Auto Post Receipts

auto_post_ap char(1),

Real Time Processing Auto Post AP

auto_post_ap_dsp char(1),

Real Time Processing Auto Post AP

auto_post_rec_dsp char(1)

Real Time Processing Auto Post Receipts

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

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

obsolete_date date

Obsolete Date

purch_unit char(2),

Purchase Unit of Measure

purch_factor decimal(6)

Purchase Factor

fmd_received char(1)

RoHS Compliance

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.

cost decimal(14,4)

Approved Vendor List Cost

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

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

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

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

Freight cost expected for this purchase order. The amount is

entered by the operator.

misc_amount decimal(14), REQUIRED

Miscellaneous expected costs for purchase order. The amount

is entered by the operator.

goods_amount decimal(14), REQUIRED

Total of extended amounts from all order lines.

total_amount decimal(14), REQUIRED

Order total amount. This consists of this sum:

goods_amount +

trd_ds_amount -

tax_amount +

frght_amount +

misc_amount +

prepay_amount decimal(14),

** 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

multiple_orders char(1)

If you wish to create multiple purchase orders

change this value to Y

contract_no char(20)

Contract Number

container_no char(20)

Container Number

orig_order_doc_no integer

If original SO has been cancelled then this value is populated.

contact_name char(20)

Contact Name

order_ref_no char(20),

RMA Order reference number

return_reason char(10)

Return Reason

prod_order char(7)

Production Order

prod_order_lot char(7),

Production Order Lot

rlse_no integer,

Blanket POs - release number

ship_date date

Blanket POs - Ship date

unique index on (doc_no);

index on (order_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(14), REQUIRED

Quantity if the item being ordered on this line. In

purchasing units.

rlse_qty decimal(14),

Not currently used by system.

rjct_qty decimal(14),

Quantity rejected during receipt process.

recv_qty decimal(14), REQUIRED

Quantity of this line item received to date.

cost_qty decimal(14,4), REQUIRED

Quantity of this line item invoiced (costed) to date.

acpt_qty decimal(14),

Not currently used by system.

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

volume decimal(8,3)

weight decimal(8,3)

return_reason char(10)

Return reason

comp_sequence char(10)

Link component to supplying PO's when short

ship_date date,

Blanket POS - ship date

blanket_doc_no integer

Blanket Document Number

unique index on (doc_no,line_no)

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

reverse char(1)

Reverse

rev_doc_no integer

Reverse Doc Number

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.

item_cost decimal(14,4)

Item cost

landed_cost decimal(14,4)

Landed Cost

extended_cost decimal(12,2)

(item_cost + landed_cost) * received_qty

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

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

vend_code char(20)

Vendor Code

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

Quantity on Invoice. Defaults to quantity received

cost decimal(14,4),

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)

item_code char(20)

Item Code

line_type char(3)

Line Type

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

The quantity of the item being invoiced at the new price.

old_price decimal(14,4) not null,

The originally ordered price of the item in question.

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

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

stuordrl PO landed Cost summary

doc_no integer, - PO doc number

category char(30), - Category

estimated_cost decimal(12,2), - Estimated Cost

received_cost decimal(12,2), - Received Cost

invoiced_cost decimal(12,2), - Invoiced Cost

var_acct_no integer, - Variance account number

alloc_method char(1), - Alloc method (see stilccat)

ok_to_post char(1), - Ok to post

posted char(1), - Posted (Y,N)

trans_doc_no integer - Activity transaction doc_no

index i1uordrl on stuordrl(doc_no);

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

stuordrm PO landed cost detail

doc_no integer, - PO doc number

line_no smallint, - PO line number

category char(30), - Category

estimated_cost decimal(12,2), - Estimated Cost

received_cost decimal(12,2), - Received Cost

invoiced_cost decimal(12,2) - Invoiced Cost

index i1uordrm on stuordrm(doc_no, line_no);

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

sturectl Received landed cost summary

rec_doc_no integer, - Receipt doc number

category char(30), - Category

received_cost decimal(12,2) - Received landed cost

index i1urectl on sturectl(rec_doc_no);

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

sturectm Received landed cost detail

rec_doc_no integer, - Receipt doc number

line_no smallint, - Receipt line number

category char(30), - Category

received_cost decimal(12,2) - Received Cost

index i1urectm on sturectm(rec_doc_no, line_no);

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

sturectd Cost to 3 decimals

item_cost decimal(12,3),

landed_cost decimal(12,3)

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

stuordre, sturecte, stuinvce increase po_no to char(20),

stuordre - Added contact_name char(20)

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

sturecve - Receive by Vendor (Header Table)

doc_no serial not null, - Unique doc_no

vend_code char(20), - Vendor Code

receipt_date date, - Receipt Date

batch_id integer - Batch Number

ok_post char(1) - Posted

create unique index "informix".i1urecve on sturecve(doc_no);

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

sturecvd - Receive by Vendor (Detail Table)

doc_no integer, - Join with header

line_no smallint, - Sequential number

rec_doc_no integer, - Receipt doc_no

rec_line_no integer, - Receipt line_no

item_code char(20), - Item Code

desc1 char(30), - Description

desc2 char(30), - Description

exp_recv_qty decimal(10), - Expected Received Qty

recv_qty decimal(10), - Actual Received Qty

rjct_qty decimal(10), - Reject Qty

rjct_code char(10), - Reject Code

po_no char(20), - PO Number

po_doc_no integer, - PO doc number

po_line_no smallint, - PO line number

item_cost decimal(14,4), - Item Cost

landed_cost decimal(14,4), - Landed Cost

extended_cost decimal(12,2) - Extended Cost

ok_post char(1) - Posted

create unique index "informix".i1urecvd on sturecvd(doc_no,line_no);

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

stumfrbe - Create rebate import mapping tables (Header)

vend_code char(20), - Vendor Code

contract char(20), - Contract

description char(60), - Description

start_date date, - Start Date

end_date date - End Date

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

stumfrbd - Create rebate import mapping tables

vend_code char(20), - Vendor Code

contract char(20), - Contract

item_code char(20), - Item Code

contract_cost decimal(11,4) - Contract cost

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

stuorrld -- FR3223 - Blanket POs

doc_no integer, - Document Number

line_no smallint, - Line Number

ship_no smallint, - Ship Number

rlse_qty decimal(10,4), - Release Qty

balance decimal(10,4), - Balance

ship_date date, - Ship Date

required_date date - Required Date

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

sturlsed -- FR3223 - Blanket POs

doc_no integer, - Document Number

line_no smallint, - Line Number

item_code char(20), - Item Code

ordr_qty decimal(14), - Order Qty

rlse_qty decimal(14), - Release Qty

remain_qty decimal(14), - Remain Qty

qty_to_rlse decimal(14) - Qty To Release

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

sturlsee -- FR3223 - Blanket POs

doc_no serial, - Document Number

po_doc_no integer, - PO doc number

po_no char(20), - PO number

rlse_date date, - Release Date

ship_date date, - Ship Date

required_date date - Required Date