ERP/3.0/Developers Guide/Database Model/org.openbravo.model.dataimport/I Invoice
I_Invoice
You should supply the Document Type Name (or ID). The Document Type is on purpose not fully derived as it has too many consequences if it's wrong. The best way is to set the Document Type Name as a Constant in your Import File Format or as an imported fi
- Name:DataImportInvoice
- Classname:Invoice
This table contains the following columns:
| Name | Nullable | Data Type | Description |
| I_Invoice_ID | N | VARCHAR2(32) | Import Invoice |
| AD_Client_ID | Y | VARCHAR2(32) | Client for this installation. |
| AD_Org_ID | Y | VARCHAR2(32) | Organizational entity within client |
| AD_OrgTrx_ID | Y | VARCHAR2(32) | The organization which performs or initiates the transaction. |
| IsActive | Y | CHAR(1) | A flag indicating whether this record is available for use or de-activated. |
| Created | Y | DATE | The date that this record is completed. |
| CreatedBy | Y | VARCHAR2(32) | User who created this records |
| Updated | Y | DATE | x not implemented |
| UpdatedBy | Y | VARCHAR2(32) | User who updated this records |
| I_IsImported | Y | CHAR(1) | A indication that the desired process has been completed successfully. |
| I_ErrorMsg | Y | NVARCHAR2(2000) | A message that appears when errors occur during the importing process. |
| Processing | Y | CHAR(1) | A request to process the respective document or task. |
| Processed | Y | CHAR(1) | A confirmation that the associated documents or requests are processed. |
| M_PriceList_ID | Y | VARCHAR2(32) | A catalog of selected items with prices defined generally or for a specific partner. |
| C_Currency_ID | Y | VARCHAR2(32) | An accepted medium of monetary exchange that may vary across countries. |
| SalesRep_ID | Y | VARCHAR2(32) | The person in charge of a document. |
| IsSOTrx | Y | CHAR(1) | An indication that a transfer of goods and money between business partners is occurring. |
| C_BPartner_ID | Y | VARCHAR2(32) | Anyone who takes part in daily business operations by acting as a customer, employee, etc. |
| C_BPartner_Location_ID | Y | VARCHAR2(32) | The location of the selected business partner. |
| BPartnerValue | Y | NVARCHAR2(40) | An abbreviated name (or a code) to help find a business partner. |
| Name | Y | NVARCHAR2(60) | A non-unique identifier for a record/document often used as a search tool. |
| C_Location_ID | Y | VARCHAR2(32) | A specific place or residence. |
| Address1 | Y | NVARCHAR2(60) | A space to write the location of a business partner. |
| Address2 | Y | NVARCHAR2(60) | A space to write the location of a business partner. |
| Postal | Y | VARCHAR2(10) | A identification code used to help get items to a specific location. |
| City | Y | NVARCHAR2(60) | A populated defined area located within a larger area such as a state, province, or country. |
| C_Region_ID | Y | VARCHAR2(32) | An area of a specific country. |
| RegionName | Y | NVARCHAR2(60) | The name of an area in a specific country. |
| AD_User_ID | Y | VARCHAR2(32) | An acquaintance to reach for information related to the business partner. |
| Y | NVARCHAR2(60) | An email address for a specified business partner. | |
| ContactName | Y | NVARCHAR2(60) | The name of the person to get in touch with. |
| Phone | Y | NVARCHAR2(40) | A telephone number for a specified business partner. |
| C_Country_ID | Y | VARCHAR2(32) | A state or a nation. |
| CountryCode | Y | CHAR(2) | The geographic country code for a country based on the ISO standard. |
| C_DocType_ID | Y | VARCHAR2(32) | A value defining what sequence and process setup are used to handle this document. |
| DocTypeName | Y | NVARCHAR2(60) | A value defining what sequence and process setup are used to handle this document. |
| C_PaymentTerm_ID | Y | VARCHAR2(32) | The setup and timing defined to complete a specified payment. |
| PaymentTermValue | Y | NVARCHAR2(40) | Key of the Payment Term |
| C_Project_ID | Y | VARCHAR2(32) | Identifier of a project defined within the Project & Service Management module. |
| C_Campaign_ID | Y | VARCHAR2(32) | An advertising effort aimed at increasing sales. |
| C_Activity_ID | Y | VARCHAR2(32) | A distinct activity defined and used in activity based management. |
| C_Invoice_ID | Y | VARCHAR2(32) | A document listing products, quantities and prices, payment terms, etc. |
| DocumentNo | Y | NVARCHAR2(30) | An often automatically generated identifier for all documents. |
| Description | Y | NVARCHAR2(255) | A space to write additional related information. |
| M_Product_ID | Y | VARCHAR2(32) | An item produced by a process. |
| ProductValue | Y | NVARCHAR2(40) | A fast method for finding an item. |
| UPC | Y | NVARCHAR2(30) | A bar code with a number to identify a product. |
| SKU | Y | NVARCHAR2(30) | A "stock keeping unit" used to track items sold to business partners. |
| C_Tax_ID | Y | VARCHAR2(32) | The percentage of money requested by the government for this specified product or transaction. |
| TaxIndicator | Y | VARCHAR2(5) | A fast method for finding a specific tax. |
| TaxAmt | Y | NUMBER | The total sum of money requested by the government of the specified transaction. |
| C_InvoiceLine_ID | Y | VARCHAR2(32) | A statement displaying one item or charge in an invoice. |
| LineDescription | Y | NVARCHAR2(255) | An additional detail regarding one item or charge. |
| QtyOrdered | Y | NUMBER | The number of an item involved in a transaction, given in standard units. It is used to determine price per unit. |
| PriceActual | Y | NUMBER | The price that will be paid for a specified item. |
Other Info
Check constraints
These are the check constraints for this table:
- I_INVOICE_ISACTIVE_CHECK: ISACTIVE IN ('Y', 'N')
- I_INVOICE_ISSOTRX_CHECK: ISSOTRX IN ('Y', 'N')
- I_INVOICE_I_ISIMPORTED_CHECK: I_ISIMPORTED IN ('Y', 'N')
- I_INVOICE_PROCESSED_CHECK: PROCESSED IN ('Y', 'N')
Indexes
These are the indexes for this table (for each index there is a list of all the columns included within it):
Unique
- I_INVOICE_KEY
Columns
Import Invoice
Import Invoice
- Physical column name: I_Invoice_ID
- Property Name: id
- Reference: ID
Client
- Physical column name: AD_Client_ID
- Property Name: client
- Reference: TableDir
- Default value: @AD_CLIENT_ID@
- This column is part of the table's identifier
Foreign key column to AD_Client table, (column: AD_Client_ID)
Validation Rule AD_Client Security validation: Clients with user access rights. With the following code:
AD_Client.AD_Client_ID IN (@#User_Client@)
Organization
- Physical column name: AD_Org_ID
- Property Name: organization
- Reference: TableDir
- Default value: @AD_Org_ID@
Foreign key column to AD_Org table, (column: AD_Org_ID)
Trx Organization
- Physical column name: AD_OrgTrx_ID
- Property Name: trxOrganization
- Reference: Table
Foreign key column to AD_Org table, (column: AD_Org_ID)
Reference Table AD_Org (Trx) With the following where clause:
AD_Org.IsSummary='N' AND AD_Org_ID <> '0'
Active
There are two methods of making records unavailable in the system: One is to delete the record, the other is to de-activate the record. A de-activated record is not available for selection, but available for reporting. There are two reasons for de-activating and not deleting records:
(1) The system requires the record for auditing purposes.
(2) The record is referenced by other records. E.g., you cannot delete a Business Partner, if there are existing invoices for it. By de-activating the Business Partner you prevent it from being used in future transactions.
- Physical column name: IsActive
- Property Name: active
- Reference: YesNo
Creation Date
The Created field indicates the date that this record was created.
- Physical column name: Created
- Property Name: creationDate
- Reference: DateTime
Created By
- Physical column name: CreatedBy
- Property Name: createdBy
- Reference: Search
Foreign key column to AD_User table, (column: AD_User_ID)
Updated
The Updated field indicates the date that this record was updated.
- Physical column name: Updated
- Property Name: updated
- Reference: DateTime
Updated By
- Physical column name: UpdatedBy
- Property Name: updatedBy
- Reference: Search
Foreign key column to AD_User table, (column: AD_User_ID)
Import Process Complete
The Imported check box indicates if this import has been processed.
- Physical column name: I_IsImported
- Property Name: importProcessComplete
- Reference: YesNo
Import Error Message
The Import Error Message displays any error messages generated during the import process.
- Physical column name: I_ErrorMsg
- Property Name: importErrorMessage
- Reference: String
Process Now
When this field is set as 'Y' a process is being performed on this record.
- Physical column name: Processing
- Property Name: processNow
- Reference: Button
Processed
The Processed checkbox indicates that a document has been processed.
- Physical column name: Processed
- Property Name: processed
- Reference: YesNo
Price List
- Physical column name: M_PriceList_ID
- Property Name: priceList
- Reference: TableDir
Foreign key column to M_PriceList table, (column: M_PriceList_ID)
Currency
- Physical column name: C_Currency_ID
- Property Name: currency
- Reference: TableDir
Foreign key column to C_Currency table, (column: C_Currency_ID)
Sales Representative
- Physical column name: SalesRep_ID
- Property Name: salesRepresentative
- Reference: Table
Foreign key column to AD_User table, (column: AD_User_ID)
Reference Table AD_User SalesRep With the following where clause:
EXISTS (SELECT * FROM C_BPartner bp WHERE AD_User.C_BPartner_ID=bp.C_BPartner_ID AND bp.IsSalesRep='Y')
Sales Transaction
The Sales Transaction checkbox indicates if this item is a Sales Transaction.
- Physical column name: IsSOTrx
- Property Name: salesTransaction
- Reference: YesNo
Business Partner
- Physical column name: C_BPartner_ID
- Property Name: businessPartner
- Reference: Search
Foreign key column to C_BPartner table, (column: C_BPartner_ID)
Partner Address
- Physical column name: C_BPartner_Location_ID
- Property Name: partnerAddress
- Reference: TableDir
Foreign key column to C_BPartner_Location table, (column: C_BPartner_Location_ID)
Validation Rule C_BPartner Location: null. With the following code:
C_BPartner_Location.C_BPartner_ID=@C_BPartner_ID@
Business Partner Search Key
An abbreviated name (or a code) to help find a business partner. Usually, a company's internal identifier is entered here.
- Physical column name: BPartnerValue
- Property Name: businessPartnerSearchKey
- Reference: String
Name
A more descriptive identifier (that does need to be unique) of a record/document that is used as a default search option along with the search key (that is unique and mostly shorter). It is up to 60 characters in length.
- Physical column name: Name
- Property Name: name
- Reference: String
Location / Address
- Physical column name: C_Location_ID
- Property Name: locationAddress
- Reference: Search
Foreign key column to C_Location table, (column: C_Location_ID)
Address Line 1
The Address 1 identifies the address for an entity's location
- Physical column name: Address1
- Property Name: addressLine1
- Reference: String
Address Line 2
The Address 2 provides additional address information for an entity. It can be used for building location, apartment number or similar information.
- Physical column name: Address2
- Property Name: addressLine2
- Reference: String
Postal Code
The Postal Code field identifies the postal code for this entity's address.
- Physical column name: Postal
- Property Name: postalCode
- Reference: String
City Name
The City identifies a unique City for this Country or Region.
- Physical column name: City
- Property Name: cityName
- Reference: String
Region
- Physical column name: C_Region_ID
- Property Name: region
- Reference: TableDir
Foreign key column to C_Region table, (column: C_Region_ID)
Region Name
The Region Name defines the name that will print when this region is used in a document.
- Physical column name: RegionName
- Property Name: regionName
- Reference: String
User/Contact
- Physical column name: AD_User_ID
- Property Name: userContact
- Reference: TableDir
- Default value: -1
Foreign key column to AD_User table, (column: AD_User_ID)
Validation Rule AD_User C_BPartner User/Contacts: null. With the following code:
AD_User.C_BPartner_ID=@C_BPartner_ID@
The Email Address is the Electronic Mail ID for this User and should be fully qualified (e.g. joe.smith@company.com).
- Physical column name: Email
- Property Name: email
- Reference: String
Contact Name
Business Partner Contact Name
- Physical column name: ContactName
- Property Name: contactName
- Reference: String
Phone
The Phone field identifies a telephone number
- Physical column name: Phone
- Property Name: phone
- Reference: String
Country
- Physical column name: C_Country_ID
- Property Name: country
- Reference: TableDir
Foreign key column to C_Country table, (column: C_Country_ID)
ISO Country Code
For details - http://www.din.de/gremien/nas/nabd/iso3166ma/codlstp1.html or - http://www.unece.org/trade/rec/rec03en.htm
- Physical column name: CountryCode
- Property Name: iSOCountryCode
- Reference: String
Document Type
- Physical column name: C_DocType_ID
- Property Name: documentType
- Reference: TableDir
Foreign key column to C_DocType table, (column: C_DocType_ID)
Validation Rule C_DocType Invoices and Credit Memos: Document Type AR/AP Invoice and Credit Memos. With the following code:
C_DocType.DocBaseType IN ('ARI', 'API','ARC','APC') AND AD_ISORGINCLUDED(@AD_Org_ID@,C_DocType.AD_Org_ID, @#AD_Client_ID@) <> '-1'
Document Type Name
A value defining what sequence and process setup are used to handle this document.
- Physical column name: DocTypeName
- Property Name: documentTypeName
- Reference: String
Payment Terms
- Physical column name: C_PaymentTerm_ID
- Property Name: paymentTerms
- Reference: TableDir
Foreign key column to C_PaymentTerm table, (column: C_PaymentTerm_ID)
Payment Term Key
Key of the Payment Term
- Physical column name: PaymentTermValue
- Property Name: paymentTermKey
- Reference: String
Project
- Physical column name: C_Project_ID
- Property Name: project
- Reference: Search
Foreign key column to C_Project table, (column: C_Project_ID)
Sales Campaign
- Physical column name: C_Campaign_ID
- Property Name: salesCampaign
- Reference: TableDir
Foreign key column to C_Campaign table, (column: C_Campaign_ID)
Activity
- Physical column name: C_Activity_ID
- Property Name: activity
- Reference: TableDir
Foreign key column to C_Activity table, (column: C_Activity_ID)
Invoice
- Physical column name: C_Invoice_ID
- Property Name: invoice
- Reference: Search
Foreign key column to C_Invoice table, (column: C_Invoice_ID)
Document No.
The document number is usually automatically generated by the system and determined by the document type of the document. If the document is not saved, the preliminary number is displayed in "<>". If the document type of your document has no automatic document sequence defined, the field will be empty when creating a new document. This is for documents which usually have an external number (like vendor invoice). If you leave the field empty, the system will generate a document number for you. The document sequence used for this fallback number is defined in the "Document Sequence" window with the name "DocumentNo_<TableName>", where TableName is the actual name of the table inside the database (e.g. C_Order).
- Physical column name: DocumentNo
- Property Name: documentNo
- Reference: String
Description
A description is limited to 255 characters.
- Physical column name: Description
- Property Name: description
- Reference: String
Product
- Physical column name: M_Product_ID
- Property Name: product
- Reference: Search
Foreign key column to M_Product table, (column: M_Product_ID)
Product Search Key
A fast method for finding an item.
- Physical column name: ProductValue
- Property Name: productSearchKey
- Reference: String
UPC/EAN
Use this field to enter the bar code for the product in any of the bar code symbologies (Codabar, Code 25, Code 39, Code 93, Code 128, UPC (A), UPC (E), EAN-13, EAN-8, ITF, ITF-14, ISBN, ISSN, JAN-13, JAN-8, POSTNET and FIM, MSI/Plessey, and Pharmacode)
- Physical column name: UPC
- Property Name: uPCEAN
- Reference: String
SKU
The SKU indicates a user defined stock keeping unit. It may be used for an additional bar code symbols or your own schema.
- Physical column name: SKU
- Property Name: sKU
- Reference: String
Tax
- Physical column name: C_Tax_ID
- Property Name: tax
- Reference: TableDir
Foreign key column to C_Tax table, (column: C_Tax_ID)
Tax Search Key
The Tax Indicator identifies the short name that will print on documents referencing this tax.
- Physical column name: TaxIndicator
- Property Name: taxSearchKey
- Reference: String
Tax Amount
The Tax Amount displays the total tax amount for a document.
- Physical column name: TaxAmt
- Property Name: taxAmount
- Reference: Amount
Invoice Line
- Physical column name: C_InvoiceLine_ID
- Property Name: invoiceLine
- Reference: Search
Foreign key column to C_InvoiceLine table, (column: C_InvoiceLine_ID)
Line Description
An additional detail regarding one item or charge.
- Physical column name: LineDescription
- Property Name: lineDescription
- Reference: String
Ordered Quantity
The Ordered Quantity indicates the quantity of a product that was ordered.
- Physical column name: QtyOrdered
- Property Name: orderedQuantity
- Reference: Quantity
Unit Price
The actual price indicates the price for a product in source currency.
- Physical column name: PriceActual
- Property Name: unitPrice
- Reference: Price