Invoice

foreign_currency

Each row of foreign_currency describes a currency that is foreign relative to the local system.

Column

Data Type

Not NULL?

Comment

id

bigint

false

System-generated sequential ID.

accounting_code_num

int

false

Accounting code number.

code

varchar

false

Identifies the currency.

rate

numeric

false

Identifies the rate of exchange, that is, the value of the foreign currency in terms of the local currency.

description

varchar

false

Describes the currency.

format

varchar

false

Specifies the format of the money. For example, ,.2$ for standard US money, and .,0L for Italian Lira. This field comprises four elements:

Places Separator - a punctuation character that separates every three digits for non-fractional values in large numbers; e.g., the commas in 1,000,000.


Fractional Separator - a punctuation character that separates fractional values; e.g., the period in 12.95 (to separate dollars from cents).

Precision - a number that specifies the number of decimal places in the currency; e.g., 133.57 has two places of precision.

Symbol - the currency symbol; it can be a single character (e.g., $), a string (e.g., HK$), or a braced diacritic (e.g., {185} for £ the British pound).

invoice_record

Each row of invoice_record contains data fields for a specific invoice record.

Column

Data Type

Not NULL?

Comment

id

bigint

false

System-generated sequential ID.

record_id

bigint

false

Foreign key to record.

accounting_unit_code_num

int

false

Accounting unit code number.

invoice_date_gmt

timestamptz

false

The date from the vendor's invoice entered by the user.

paid_date_gmt

timestamptz

false

The date the invoice was finished.

status_code

varchar

false

Status of the invoice. The status is changed by the system as the user processes an invoice.

CODE     

DEFINITION

s

suspended

f

ready to post

1

post step 1

2

post step 2

3

post step 3

4

post step 4

5

post step 5

6

post step 6

c

complete

 

posted_data_gmt

timestamptz

false

The date the invoice was posted.

is_paid_date_received_date

boolean

false

Specifies whether the paid date and received dates are identical.

ncode1

char

false

Not currently used.

ncode2

char

false

Not currently used.

ncode3

char

false

Not currently used.

invoice_number_text

varchar

false

Invoice number entered from the vendor's invoice.

iii_user_name

varchar

false

Specifies the user name that created the invoice.

foreign_currency_code

varchar

false

Specifies the foreign currency code.

foreign_currency_format

varchar

false

Specifies the foreign currency format.

foreign_currency_exchange_rate

numeric

false

Specifies the foreign currency exchange rate.

tax_fund_code

varchar

false

Specifies the name of the dedicated fund for tax (if applicable).

tax_type_code

varchar

false

Specifies the type of tax (GST or VAT).

discount_amt

numeric

false

Discount amount or service charge applied to the invoice.

grand_total_amt

numeric

false

Grand total amount for the invoice.

subtotal_amt

numeric

false

Subtotal amount for the invoice.

shipping_amt

numeric

false

Total shipping amount for the invoice.

total_tax_amt

numeric

false

Total tax amount for the invoice.

use_tax_fund_code

varchar

false

Code to identify the use tax fund.

use_tax_percentage_rate

numeric

false

Specifies the use tax percentage rate.

use_tax_type_code

varchar

false

Indicates the type of use tax applied to the invoice.

use_tax_ship_service_code

varchar

false

Code for use tax applied to shipping charges.

is_suppressed

boolean

false

Specifies whether the record is suppressed from public display.

invoice_record_line

Each row of invoice_record_line contains data for a specific line item in an invoice.

Column

Data Type

Not NULL?

Comment

id

bigint

false

System-generated sequential ID.

invoice_record_id

bigint

false

Foreign key to invoice_record.

order_record_metadata_id

bigint

false

Foreign key to order_record_metadata.

paid_amt

numeric

false

Amount paid for the order, excluding any hundreds or fractions separators. If the invoice is in foreign currency, this amount is the foreign currency amount.

lien_amt

numeric

false

Lien amount. If the status of the order is "Fully paid", this field contains a value of "0". If the lien_flag is "0" or "-1", the value in this field is calculated by multiplying the number of copies by the estimated_price. (See order_record view.) If the lien_flag field has a value of "-2", the value of this field is the remaining unpaid balance for a single copy partial payment.

lien_flag

int

false

Lien flag.

CODE

DEFINITION

0

Normal payment

-1

Status 'g' payment

-2

Single copy partial payment

 

list_price

numeric

false

List price. This field appears only if the system is set to support list prices.

fund_code

varchar

false

Fund code to be charged for the ordered items.

subfund_num

int

false

Subfund number.

copies_paid_cnt

int

false

Number of copies paid for from the fund associated with the line item.

external_fund_code_num

int

false

External fund code.

status_code

varchar

false

Status of the order record after the invoice is posted.

STD CODE

DEFINITION

o

On order (encumbered monograph)

a

Fully paid (fully disencumbered monograph)

q

Partially paid (partially disencumbered monograph)

z

Canceled (fully disencumbered)

1

On hold (not yet encumbered)

2

Approval rejection (not encumbered)

c

Serial on order (encumbered new serial)

d

Serial paid (fully paid and disencumbered serial)

e

Serial partially paid (partially paid, partially disencumbered serial)

f

Serial no encumbering (but can make payments - for continuation orders)

g

Serial encumbered (re-encumber fund for renewal)

 

note

varchar

false

Free-text note field entered by the user during the invoicing process. For a single copy partial payment, the note starts with an asterisk (*).

is_single_copy_partial_pmt

boolean

false

Specifies whether the line represents a single copy partial payment.

title

varchar

false

Title from the bibliographic record to which the order record is attached. This field stores up to 31 characters of the title.

multiflag_code

char

false

A code used to note whether the line item is part of a multi-fund group (multiple copies with different funds on a single order).

CODE

DEFINITION

!

First line of a multi-fund group

*

Middle line of a multi-fund group

1

Last line of a multi-fund group

9

Single fund payment (i.e., not part of a multi-fund group)

 

line_level_tax

numeric

false

Line-level tax. The tax is either GST or VAT entered by the user at the line level during invoicing or a prorated amount added by the system during posting.

vendor_code

varchar

false

Vendor code.

accounting_transaction_voucher_num

int

false

Voucher id field that links the line item to the appropriate invoice vendor variable-length field. The value of this field is in the format:

<n>.<m>

where:

  • <n> = A value that uniquely identifies a specific vendor in the invoice.
  • <m> = An index used to record whether the entry is the first, second, third, etc. in the invoice for the vendor identified by the <n> element.

For example, the first entry for the first vendor in an invoice has a value of "0.0", the second entry a value of "0.1", and so on. The first entry for the second vendor in the invoice has a value of "1.0", the second entry "1.1", and so on.

accounting_transaction_voucher_seq_num

int

false

Auto-incremented number used to count the line items in the invoice. This number starts at '0'.

line_cnt

int

false

Invoice line number for this line item.

invoice_record_vendor_summary_id

bigint

false

Foreign key to invoice_record_vendor_summary.

is_use_tax

boolean

false

Specifies whether the payment is for use tax.

invoice_record_vendor_summary

Each row of invoice_record_vendor_summary contains information about a vendor used on an invoice.

Column

Data Type

Not NULL?

Comment

id

bigint

false

System-generated sequential ID.

invoice_record_id

bigint

false

Foreign key to invoice_record.

vendor_code

varchar

false

Vendor code. For invoices created manually, the system drops the initial subfield delimiter (e.g "|a").

vendor_address_line1

varchar

false

Vendor address from the order record (all data on the first line up to the '$'symbol). This subfield displays only if the vendor code in subfield 'a' is "none".

voucher_num

int

false

System-generated voucher number of the transaction.

voucher_total

int

false

Voucher total assigned during posting.

display_order

int

false

Integer to manage the display order of a list.

invoice_view

Each row of invoice_view includes metadata and data for one invoice record. The contents include identification and acquisitions information, as well as data that determines how the system handles the record.

Column

Data Type

Not NULL?

Comment

id

bigint

false

System-generated sequential ID.

record_type_code

char

false

Record type code, i.e., 'n'.

record_num

int

false

Record number.

accounting_unit_code_num

int

false

Accounting unit code number.

invoice_date_gmt

timestamptz

false

The date from the vendor's invoice entered by the user.

paid_date_gmt

timestamptz

false

The date the invoice was finished.

status_code

varchar

false

Status of the invoice. The status is changed by the system as the user processes an invoice.

CODE

DEFINITION

s

suspended

f

ready to post

1

post step 1

2

post step 2

3

post step 3

4

post step 4

5

post step 5

6

post step 6

c

complete

 

posted_date_gmt

timestamptz

false

The date the invoice was posted.

is_paid_date_received_date

boolean

false

Specifies whether the paid date and received dates are identical.

ncode1

char

false

Not currently used.

ncode2

char

false

Not currently used.

ncode3

char

false

Not currently used.

invoice_number_text

varchar

false

Invoice number entered from the vendor's invoice.

iii_user_name

varchar

false

Specifies the user name that created the invoice.

foreign_currency_code

varchar

false

Specifies the foreign currency code.

foreign_currency_format

varchar

false

Specifies the foreign currency format.

foreign_currency_exchange_rate

numeric

false

Specifies the foreign currency exchange rate.

tax_fund_code

varchar

false

Specifies the name of the dedicated fund for tax (if applicable).

tax_type_code

varchar

false

Specifies the type of tax (GST or VAT).

discount_amt

numeric

false

Discount amount or service charge applied to the invoice.

grand_total_amt

numeric

false

Grand total amount for the invoice.

subtotal_amt

numeric

false

Subtotal amount for the invoice.

shipping_amt

numeric

false

Total shipping amount for the invoice.

total_tax_amt

numeric

false

Total tax amount for the invoice.

use_tax_fund_code

varchar

false

Code to identify the use tax fund.

use_tax_percentage_rate

numeric

false

Specifies the use tax percentage rate.

use_tax_type_code

varchar

false

Code that indicates the type of use tax applied to the invoice.

use_tax_ship_service_code

varchar

false

Code for use tax applied to shipping charges.

is_suppressed

boolean

false

Specifies whether the record is suppressed from public display.

record_creation_date_gmt

timestamptz

false

Record creation date.

user_defined_ncode1_myuser

Each row of user_defined_ncode1_myuser identifies a user-defined fixed-length field for invoice records.

Column

Data Type

Not NULL?

Comment

code

varchar

false

User-defined code to represent the user-defined field.

user_defined_category_id

int

false

Foreign key to user_defined_category.

display_order

int

false

Integer to manage the display order of a list.

name

varchar

false

The user-defined name assigned to the user-defined field.

user_defined_ncode2_myuser

Each row of user_defined_ncode2_myuser identifies a user-defined fixed-length field for invoice records.

Column

Data Type

Not NULL?

Comment

code

varchar

false

User-defined code to represent the user-defined field.

user_defined_category_id

int

false

Foreign key to user_defined_category.

display_order

int

false

Integer to manage the display order of a list.

name

varchar

false

The user-defined name assigned to the user-defined field.

user_defined_ncode3_myuser

Each row of user_defined_ncode3_myuser identifies a user-defined fixed-length field for invoice records.

Column

Data Type

Not NULL?

Comment

code

varchar

false

User-defined code to represent the user-defined field.

user_defined_category_id

int

false

Foreign key to user_defined_category.

display_order

int

false

Integer to manage the display order of a list.

name

varchar

false

The user-defined name assigned to the user-defined field.