| Oracle® Retail Invoice Matching Operations Guide Release 19.2.000 F36992-03 |
|
 Previous |
 Next |
This chapter describes how Invoice Matching integrates with other systems, related interfaces, and file layouts. It includes an integration overview, a discussion of EDI (with layouts), an explanation of how Invoice Matching interfaces with financial systems, and a summary of LDAP user authentication.
Additional configuration may be needed to properly integrate other systems with Invoice Matching.
This section provides a diagram that shows the overall direction of the data among the applications and tables. The accompanying explanations are written from a system/staging table-to-system/staging table perspective, illustrating the movement of data.
Invoice Matching receives supplier invoices and credit notes through EDI or through on-line entry processes. These document types are described later in this chapter.
Invoice Matching generates debit memos, credit note requests and credit memos for various reasons. Each of these documents is recorded in Invoice Matching tables to allow for retailer reporting. Also, a Invoice Matching process reads these tables and creates a file of these documents to support the retailer's EDI transmissions to suppliers.
For a description of the data that is sent through this interface, see "Financial System Interface" later in this chapter.
Invoice Matching is able to access foundation data, such as item, purchase order, supplier, and other information directly from Merchandising tables. Invoice Matching provides the drivers to access these tables without further integration work.
Receipts
Receipts are records of purchased merchandise arriving at the store or warehouse. Receipt data is accessed in Merchandising, and certain data elements are extracted from Merchandising into Invoice Matching tables to support Invoice Matching-specific actions performed against receipts (for example, splitting receipt quantities, updating statuses, and so on).
Purchase Orders
Purchase orders (POs) are created in Merchandising and represent a legally binding agreement between retailer and supplier for the purchase and sale of goods. The retailer records the quantity, cost and delivery location of items from the supplier. On a single PO, Merchandising supports different costs for the same item going to different locations. PO costs are used to value receipt quantities.
Item
Invoice Matching processes matches at the item transaction-level (that is, SKUs). For reference purposes, UPCs may be used, so they should be provided by the merchandising system. See Oracle Retail Merchandising System documentation for more information about the multi-level item structure.
Partner
A partner is a business that supplies and bills a retailer for non-merchandise services. Examples of partners are banks, agents, and expense suppliers. A partner cannot send merchandise invoices to retailers.
Valued Added Tax (Tax) Code and Rate
Invoice Matching provides for validation of taxes charged on the invoice against Tax codes/rates set up in MFCS. If Single Tax is being used, the tax data is in tax tables for the item in MFCS. For global tax, tax validation is done based on the tax rules defined in the Tax Engine. In this case other tax attributes such as tax type are used for tax validation.
Consignment
Consignment is an arrangement whereby the physical control of merchandise (but not the title of ownership) is transferred from one business known as the consignor (for example, the vendor) to another known as the consignee (for example, the retailer). The title to the goods remains with the consignor until the goods are sold. When consigned goods are sold, the consignor invoices the consignee. On this invoice, the cost of each item is reduced to a certain proportion, called the consignment rate. The consignment rate, predetermined by both parties, represents the consignor's share of the sale. Once the merchandising system records a sale, a consignment invoice is created in Invoice Matching for a percentage of the sale cost. The receipt is implied based on the consignment rate applied to the selling price; accordingly, the self-billed invoice is assumed to be in matched status.
Return to Vendor (RTV)
An RTV is a retailer-initiated purchase return of inventoried goods to an external vendor. The merchandising system uses RTV data to update inventory positions and write requisite transactions to the stock ledger. Invoice Matching receives RTV data through the merchandising system from the store and warehouse inventory systems where it is initiated, where a charge-back document is created.
Deal Bill Backs
Merchandising tracks certain types of supplier deals (for example, rebates, vendor funded markdowns, and so on) for billing back to the supplier. Information to support these billings is received in Invoice Matching through an Merchandising extract. Invoice Matching creates a charge back document for these billings, which may be subject to edit/approval in Invoice Matching or automatically processed to the financial staging table for export to the retailer's accounts payable solution, based on an Merchandising parameter.
Other Data Elements Received from Merchandising
Non-merchandise codes
Currency
Exchange rates
Store/warehouse location type
Supplier information
Supplier address (invoice address, returns address, and so on)
Merchandise hierarchy
Business date
Terms and terms ranking
Receiver cost and unit adjustments are initiated in Invoice Matching update receipts held in Merchandising tables. Receiver adjustments, resulting from the Invoice Matching discrepancy resolution process, create cost and/quantity adjustments to receipt tables in Merchandising, as well as to supplier and purchase order tables for certain types of cost resolutions.
Receipt Status
When the entire receipt is matched (all the lines to invoices), Invoice Matching provides and update to the invoice match status (that is, from unmatched to matched) on the shipment table in Merchandising.
Shipment (Receipts) Table Quantity Matched Update
When Invoice Matching matches a portion and/or all of a receipt line to an invoice line, Invoice Matching makes a corresponding update to the quantity matched column.
Electronic Data Interchange (EDI) facilitates the computer-to-computer transmission of business information and transactions, such as invoices and purchase orders. EDI represents a convenient method by which a retailer and its suppliers can transfer information back and forth. The Voluntary Interindustry Commerce Standard (VICS) EDI is used by the general merchandise retail industry.
Invoice Matching has two file-based EDI interfaces. Note that neither follows the VICS EDI standard. The Invoice Matching EDI interfaces have been customized, and the retailer must translate them.
The interfaces represent the upload of invoices or other documents from a supplier or another application and the download of documents to suppliers. These two common types of EDI are described below:
EDI Injector is the standard description for an EDI process that uploads documents.
EDI invoice download is the standard description for an EDI process that downloads Debit Memo, Credit Note Request, and Credit Memo data from Invoice Matching to suppliers.
For information about Invoice Matching batch processes related to both of these types of EDI, see "Chapter 5, "Batch Processes". Note that although the majority of invoices are created through either EDI Injector or batch entry, users can also create invoices online and add details, or use the online dialog to add details to an invoice that was EDI uploaded.
The EDI Injector (reimediinjector) batch process uploads invoices and credit notes from EDI files into the Injector workspace tables (IM_INJECT_xxx), validates the data against a set of rules, and then moves valid documents into the operational tables. This process validates the information in the file against itself and against Merchandising/Invoice Matching database. A limited set of data validation errors can cause the invalid transaction to remain in the Injector workspace tables (IM_INJECT_DOC_xxx) in fixable status where the data can be corrected through an online process.
The following errors can be manually corrected through the front end:
Supplier number (or Partner ID): This value must be a valid supplier site (SUPS table) or partner (PARTNER table) in Merchandising (or the equivalent merchandising system).
Order numbers: Order numbers must be approved and created for the supplier or linked suppliers in Merchandising (or the equivalent merchandising system) on the ORDHEAD table. Non-merchandise invoices may not have any order numbers associated, so this validation should be skipped for this type of invoice.
Order/location combination: The system validates that all order number/location combinations in the file are valid within Merchandising or the equivalent merchandising system (meaning that the relationship must exist on the ORDLOC table).
Terms code: All terms must exist within Merchandising or the equivalent merchandising system on the TERMS table.
Document date: A document cannot be older than the VDATE minus the post-dated document days' system level parameter value or newer than the VDATE.
Merchandise invoices cannot be associated with a partner; they must only be associated with a supplier.
Credit notes from a partner cannot have item records attached unless the partner type is a manufacturer, distributor, or wholesaler (type S1, S2, or S3).
Location: Document location should be a valid store or a physical warehouse within Merchandising system
Item: Document item should be a valid approved item within Merchandising system
VPN: If an item is identified by VPN, it should represent a valid item within Merchandising system
Document UPC: If an item is identified by UPC, it should represent a valid item within Merchandising system
Document item/supplier: Document item/Document UPC/Document VPN should be valid for the document's supplier or other suppliers within the supplier group
The majority of the data validation errors (identified in the file layout Validation column) cause the invalid transaction to be written to the reject file (named by the retailer).
The following describes the input and output specification for the EDI Injector File.
Input file format:
FHEAD (1): Start of file.
THEAD (1…n): Transaction (document) level info. Each file must have at least 1 THEAD.
TDETL (0…n): Item detail records for this transaction. TDETL is required for Merchandise Invoices and Credit Note documents, and debit memo documents. It should not be used for Non-Merchandise Invoices
TDTLT (0…n): Item detail tax records for this transaction. TDTLT is a child record for TDETL and should be used in Global tax mode. Single tax info can be provided either in TDETL or TDTLT records. TDTLT is optional.
TALLW (0…n): Allowance records for this item. TALLW is optional.
TALLT (0…n): Allowance tax records for this item. TALLT is a child record for TALLW and should be used in Global tax mode. Single tax info can be provided either in TALLW or TALLT records. TALLT is optional.
TNMRC (0…n): Non-merchandise records for this transaction. Required on non-merchandise documents.
TNMRT (0…n): Non-merchandise Tax records for this transaction. TNMRT is a child record for TNMRC and should be used in Global tax mode. Single tax info can be provided either in TNMRC or TNMRT records. TNMRT is optional.
TPORD (0…n): Additional Purchase order records that would identify purchase orders the invoices could be matched against. TPORD is optional.
TVATS (0…n): Tax breakdown by Tax code. TVATS is optional.
TTAIL (1…n): Marks the end of a THEAD record. Each THEAD requires exactly one TTAIL.
FTAIL (1): Marks the end of the file.
TDETL and TNMRC do not need to occur in order. TALLW must follow TDETL. TALLT must follow TALLW. TNMRT must follow TNMRC and TDTLT must follow TDETL.
If records are encountered in any order other than specified above, execution of program will halt.
Example:
FHEAD
THEAD
TNMRC
FTAIL (no TTAIL encountered)
If a record descriptor is encountered other than those specified in this document, execution of program will halt.
Reject file will have an identical format. If no records are rejected, it will consist of only the FHEAD and FTAIL lines.
All character variables should be right-padded with blanks and left justified; all numerical variables should be left-padded with zeroes and right-justified. Null variables should be blank.
Single location invoices will be inserted into IM_DOC_HEAD, IM_INVOICE_DETAIL and IM_DOC_NON_MERCH.
It is assumed all values that have dependent information included in the file (for example, location has dependent information of order no, upc, upc-supp, and so on) are valid for the Merchandising system. The following is never anticipated to happen: only locations A, B, and C exist in Merchandising; EDI reads a transaction that has location D. This sort of file may not be flagged as invalid in any way.
Uploaded documents with details must have at most one associated UPC, item or VPN identifier. When system tax processing is enabled, documents that fail to meet these criteria will be rejected to the file by the EDI Injector Batch process. When tax is disabled, the document will be available for review and correction through the Invoice Matching user interface in the EDI Maintenance screen.
FHEAD - File Header. First record of an upload file.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record Descriptor | Char(5) | Describes file record type | Y | Halt execution if not FHEAD. |
| Line id | Number(10) | Sequential file line number. | Y | Halt execution if not 0000000001. |
| Gentran ID | Char(5) | The type of transaction this file represents. | Y | Halt execution if not UPINV. |
| Current date | Char(14) | File date in YYYYMMDDHH24MISS format. | Y | Halt execution if invalid date format. |
| File version | Char(2) | Describes file format version | N | Defaults to 01 (previous format version). Current format is version 02. |
THEAD - Transaction Header. Start of a document transaction. This information is from the IM_DOC_HEAD table.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type | Y | THEAD |
| Line id | Number(10) | Sequential file line number | Y | Halt execution if not in sequence |
| Transaction number | Number(10) | Sequential transaction number. All records within this transaction will also have this transaction number. | Y | Reject entire file if:transaction number is not numeric or not in sequencefirst transaction number is not 0000000001 |
| Document Type | Char(6) | Describes the type of document being uploaded. The document type will determine the types of detail information that are valid for the document upload. Stored in IM_DOC_HEAD.TYPE.
Valid values are: MRCHI - Merchandise Invoice NMRCHI - Non Merchandise Invoice CRDNT - Credit Note DBMC - Debit Memo Cost DBMQ - Debit Memo Qty CRDMC - Credit Memo Cost CNRC- Credit Note Request Cost CNRQ- Credit Note Request Qty |
Y | Reject transaction to file if document type is null document type is not MRCHI (merchandise invoice), NMRCHI (non-merchandise invoice), CRDNT (credit note), DBMC (Debit Memo-Cost), DBMQ (Debit Memo-Qty), CNRC (Credit Note Request-Cost), CNRQ (Credit Note Request-Qty), CRDMC (Credit Memo Cost) document type is CRDNT (credit note); vendor is not a supplier, manufacturer, distributor, or wholesaler. document type is CRDNT and TALLW records exist document type is MRCHI and item detail records DO exist for this transaction (this type of transaction must have no item detail records) document type is CRDNT,NMRCHI, DBMC, DBMQ, CRDMC, CNRC, CNRQ and any error occurs with the document |
| Vendor Document Number | Char(50) | Vendor's document number. Stored in IM_DOC_HEAD.EXT_DOC_ID with all characters converted to their upper case (for example, ThisDocId -> THISDOCID). | Y | Reject entire upload file if the same vendor document number occurs more than once in the file.
Reject transaction to file if:
|
| Group ID | Char(10) | The Group ID is an informational field, which can be used to identify groups of invoices that were transmitted to Invoice Matching together. | N | Reject transaction to file if:
|
| Vendor Type | Char(6) | Type of vendor (either supplier or partner) for this document. Stored in IM_DOC_HEAD.VENDOR_TYPE
Valid values are: SUPP - Supplier BK - Bank AG - Agent FF - Freight Forwarder IM - Importer BR - Broker FA - Factory AP - Applicant CO - Consolidator CN - Consignee S1 - Merch Supp level 1 S2 - Merch Supp level 2 S3 - Merch Supp level 3 |
Y | Reject transaction to file if:
|
| Vendor ID | Char(10) | Vendor for this document (if a supplier, this field must be a supplier site) . Stored in IM_DOC_HEAD.VENDOR | Y | Reject transaction to tables if:
|
| Vendor Document Date | Char(14) | Date document was issued by the vendor (in YYYYMMDDHH24MISS format). Stored in IM_DOC_HEAD.DOC_DATE | Y | Reject transaction to file if:
Reject transaction to tables if Vendor Document Date is:
|
| Order Number/ RTV Order Number | Number(12) | Merchandising system order number for this document. Required for merchandise invoices and optional for others. Store in IM_DOC_HEAD.ORDER_NO.
This field can also contain the RTV order number if the RTV flag is 'Y' |
N | Reject transaction to file if:
Reject transaction to tables if RTV flag is null or 'N' AND:
Reject transaction to file if RTV flag is 'Y' AND:
|
| Location | Number(10) | Merchandising system location for this document. Required for merchandise invoices and optional for others. Stored in IM_DOC_HEAD.LOCATION. | Y | Reject transaction to file if:
Reject transaction to tables if Location and Location Type exist but are not valid. |
| Location Type | Char(1) | Merchandising system location type (either Store or Warehouse) for this document. Required for merchandise invoices and optional for others. Stored in IM_DOC_HEAD.LOC_TYPE. | N | Reject transaction to file if Location type exists and location is null. |
| Terms | Char(15) | Terms of this document. If terms are not provided, the vendor's default terms are associated with this record. Stored in IM_DOC_HEAD.TERMS. This value is used to get the Terms Discount Percentage to be stored on IM_DOC_HEAD.TERMS_DSCNT_PCT. | N | Reject transaction to tables if Terms exist and are not valid. |
| Due Date | Char(14) | Date the amount due is due to the vendor (YYYYMMDDHH24MISS format). If due date is not provided, default due date is calculated based on vendor and terms. Stored in IM_DOC_HEAD.DUE_DATE. | N | Reject transaction to file if:
|
| Payment method | Char(6) | Method for paying this document. Stored in IM_DOC_HEAD.PAYMENT_METHOD. | N | Reject transaction to file if Payment method exists and is not valid. |
| Currency code | Char(3) | Currency code for all monetary amounts on this document. Stored in IM_DOC_HEAD.CURRENCY_CODE. | Y | Reject transaction to file if:
|
| Exchange rate | NUMBER(20,10) | Exchange rate for conversion of document currency to the location currency. Stored in IM_DOC_HEAD.EXCHANGE_RATE. | N | Reject transaction to file if Exchange rate exists and is not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) total cost amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Total Cost | Number(20,4) | Total document cost, including all items and costs on this document. This value is in the document currency. Stored in IM_DOC_HEAD.TOTAL_COST and IM_DOC_HEAD.RESOLUTION_ADJUSTED_TOTAL_COST. | N | Reject transaction to file if:
|
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) total tax amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Total Tax Amount | Number(20,4) | Total tax amount, including all items and costs on this document. This value is in the document currency. | N | Treat as zero if null.Reject transaction to file if:
|
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) total tax amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Total Quantity | Number(12,4) | Total quantity of items on this document. This value is in EACHES (no other units of measure are supported in Invoice Matching). Stored in IM_DOC_HEAD.TOTAL_QTY and IM_DOC_HEAD.RESOLUTION_ADJUSTED_TOTAL_QTY. | Y | Reject transaction to file if:
|
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) total tax amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Total Discount | Number(12,4) | Total discount applied to this document. This value is in the document currency. Stored in IM_DOC_HEAD.TOTAL_DISCOUNT | Y | Reject transaction to file if:
|
| Freight Type | Char(6) | The freight method for this document. | N | Reject transaction to file if Freight type exists and is not valid. |
| Paid Ind | Char(1) | Indicates if this document has been paid. Stored in IM_DOC_HEAD.MANUALLY_PAID_IND. | Y | Reject transaction to file if:
|
| Multiple Location Indicator | Char(1) | Indicates whether this invoice goes to multiple locations. | ||
| Merchandise Type | Char(1) | Indicates the type of merchandise. | ||
| Deal Id | Number(10) | Deal Id from Merchandising if this invoice is a deal bill back invoice. | N | If Deal Id is not null, Deal Approval indicator must be 'M' or 'A'.Do not reject transaction to table if deal id is not null. |
| Deal Detail Id | Number(10) | Deal Detail Id from Merchandising and is stored in IM_DOC_HEAD. DEAL_DETAIL_ID | N | |
| Ref CNR Ext Doc Id | Char(50) | Reference to the External Id of Credit.
Note: Request associated with this document. |
||
| Ref INV Ext Doc Id | Char(50) | Reference to the External Id of Invoice associated with this document. | ||
| Deal Approval Indicator | Char(1) | Indicates if the document on IM_DOC_HEAD is to be created in Approved or Submitted status. | N | Reject to file if not blank, 'M' Submitted status or 'A' approved.Do not reject transaction to table if value is not null. |
| RTV indicator | Char(1) | Indicates if this invoice is a RTV invoice. | Y | Reject transaction to file if:
Do not reject transaction to table if RTV is Y. |
| Custom Document Reference 1 | Char(90) | This optional field is included in the upload file for client customization. No validation is performed on this field. Stored in IM_DOC_HEAD.CUSTOM_REF_1. | N | |
| Custom Document Reference 2 | Char(90) | This optional field is included in the upload file for client customization. No validation is performed on this field. Stored in IM_DOC_HEAD.CUSTOM_REF_2. | N | |
| Custom Document Reference 3 | Char(90) | This optional field is included in the upload file for client customization. No validation is performed on this field. Stored in IM_DOC_HEAD.CUSTOM_REF_3. | N | |
| Custom Document Reference 4 | Char(90) | This optional field is included in the upload file for client customization. No validation is performed on this field. Stored in IM_DOC_HEAD.CUSTOM_REF_4. | N | |
| Cross-reference document number | Number(10) | Document that a credit note is for. Blank for all document types other than merchandise invoices. Stored in IM_DOC_HEAD.REF_DOC. | N | Reject transaction to file if Cross-reference document number exists and is not numeric |
TVATS - Tax breakdown by Tax code. This information is inserted in IM_DOC_TAX
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Field record descriptor | Char(5) | Marks costs at tax rate line. | Y | TVATSReject entire transaction to file if this type of record exists and the transaction has any error. See technical design for additional validations.Reject to file if in im_system_options tax is on, but there is no TVATS. |
| Line id | Char(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction number | Number(10) | Transaction number for this item detail record. | Y | Reject entire file if:
|
| Tax code | Char(6) | Tax code that applies to cost. | Y | Reject to file if Tax code is not valid. |
| Tax rate | Number(20,10) | Tax Rate corresponding to the tax code. | Y | Reject to file if Tax rate is not numeric. |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) taxable value | N | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Taxable Value at this Tax code | Number(20,4) | Total amount that must be taxed at the above Tax code. | N | Reject to file if not numeric. |
| Tax Amount Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) tax amount. | N | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Tax amount at this Tax code | Number(20,4) | Total amount for the above Tax code. | N | Reject to file if not numeric. |
TDETL - Item Detail Record. This information is inserted into the IM_INVOICE_DETAIL table for Merchandise Invoice and IM_DOC_DETAIL_REASON_CODES for Credit Notes.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TDETL |
| Line Id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction number | Number(10) | Transaction number for this item detail record. | Y | Reject to file if tax rate is not numeric
|
| UPC | Char(25) | UPC for this detail record. Valid item number is retrieved for the UPC. Stored in IM_INVOICE_DETAIL.ITEM or IM_DOC_DETAIL_REASON_CODES.ITEM. | Y
Exclusive with item |
Reject transaction to file if:
Reject transaction to tables if:
|
| UPC Supplement | Number(5) | Supplement for the UPC. | N | Reject transaction to file if:
|
| Item | Char(25) | Item for this detail record. Item number is verified and stored in IM_INVOICE_DETAIL.ITEM or IM_DOC_DETAIL_REASON_CODES.ITEM. | Y
Exclusive with UPC |
Reject transaction to file if:
|
| VPN | Char(30) | Vendor Product Number provided by the supplier. It is used to identify an item when an item number has not been provided. VPN is displayed on the Invoice Maintenance screen and may be used during the on-line matching process. | Y
Exclusive with item and UPC |
Reject transaction to file if:
Reject transaction to tables if:
|
| Unit Quantity Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) Original Document Quantity amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Original Document Quantity | Number(12,4) | Quantity, in EACHES, of the item on this detail record. Stored in IM_INVOICE_DETAIL.INVOICE_QTY and IM_INVOICE_DETAIL. RESOLUTION_ADJUSTED_QTY. | Y | Reject transaction to file if:
|
| Unit Cost Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) Original Unit Cost amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Original Unit Cost | Number(20,4) | Unit cost, in document currency, of the item on this detail record. Stored in IM_INVOICE_DETAIL.UNIT_COST and IM_INVOICE_DETAIL.RESOLUTION_ADJUSTED_UNIT_COST. | Y | Reject transaction to file if:
|
| Original Tax Code | Char(6) | Tax code for item. | N | Reject to file if tax code is invalid. |
| Original Tax rate | Number(20,10) | Tax Rate for the tax code/item. | N | Reject to file if tax rate is not numeric. |
| Allowance Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) allowance | Y | Reject transaction to file if:
|
| Total Allowance | Number(20,4) | Sum of allowance details for this item detail record. If no allowances exist for this item detail record, value is 0. | Y | Reject transaction to file if:
|
| Tax Amount Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) taxable amount for rate based tax | Y | Reject transaction to file if:
Sign indicator is not + or -. |
| Taxable amount | Number(20,4) | Taxable amount for the line item for rate base tax | N | Reject transaction to file if:
Taxable amount is not numeric. |
| Tax Per Unit Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) per unit tax value. Used for per unit tax, optional otherwise | N | Reject transaction to file if populated and:
Sign indicator is not + or -. |
| Per unit tax value | Number(20,4) | Per unit tax value. Used for per unit tax, optional otherwise | N | Reject transaction to file if populated and:
Taxable amount is not numeric. |
TDTLT - Item Detail Tax Record. This information is inserted into the IM_INVOICE_DETAIL_TAX table for Merchandise Invoice and IM_DOC_DETAIL_RC_TAX for Credit Notes.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TDTLT |
| Line Id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction number | Number(10) | Transaction number for this item detail record. | Y | Reject to file if tax rate is not numeric
|
| UPC | Char(25) | UPC for this detail record. Valid item number is retrieved for the UPC. Stored in IM_INVOICE_DETAIL.ITEM or IM_DOC_DETAIL_REASON_CODES.ITEM. | Y
Exclusive with item |
Reject transaction to file if:
Reject transaction to tables if:
|
| UPC Supplement | Number(5) | Supplement for the UPC. | N | Reject transaction to file if:
|
| Item | Char(25) | Item for this detail record. Item number is verified and stored in IM_INVOICE_DETAIL.ITEM or IM_DOC_DETAIL_REASON_CODES.ITEM. | Y
Exclusive with UPC |
Reject transaction to file if:
|
| VPN | Char(30) | Vendor Product Number provided by the supplier. It is used to identify an item when an item number has not been provided. VPN is displayed on the Invoice Maintenance screen and may be used during the on-line matching process. | Y
Exclusive with item and UPC |
Reject transaction to file if:
Reject transaction to tables if:
|
| Original Tax Code | Char(6) | Tax code for item. | Y | Reject to file if tax code is invalid. |
| Original Tax rate | Number(20,10) | Tax Rate for the tax code/item. | Y | Reject to file if tax rate is not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) taxable amount for rate based tax | Y | Reject transaction to file if:
Sign indicator is not + or -. |
| Taxable amount | Number(20,4) | Taxable amount for the line item for rate base tax | Y | Reject transaction to file if:
Taxable amount is not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) per unit tax value. Used for per unit tax, optional otherwise | N
Exclusive with tax rate |
Reject transaction to file if populated and:
Sign indicator is not + or -. |
| Per unit tax value | Number(20,4) | Per unit tax value. Used for per unit tax, optional otherwise | N
Exclusive with tax rate |
Reject transaction to file if populated and:
Taxable amount is not numeric. |
TALLW - Allowance Record. This information is inserted into IM_INVOICE_DETAIL_ALLOWANCE table.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TALLW |
| Line id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction Number | Number(10) | Transaction number for this item allowance record. | Y | Reject entire file if:
|
| Allowance Code | Char(6) | Allowance code for this allowance record. Stored in IM_INVOICE_DETAIL_ALLOWANCE.ALLOWANCE_CODE. | Y | Reject transaction to file if:
|
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) allowance amount. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Allowance Amount | Number (20,4) | Amount of allowance in document currency. Stored in IM_INVOICE_DETAIL_ALLOWANCE.ALLOWANCE_AMOUNT. | Y | Reject transaction to file if allowance amount is null or not numeric. |
| Allowance Tax Code | Char(6) | Tax Code for Allowance. Should be populated if tax is required. In global tax mode tax can be provided in TALLW record. | N | Reject to file if tax code is not valid. |
| Allowance Tax rate at this tax code | Number (20,10) | Tax Rate corresponding to the tax code. Should be populated if tax is required. In global tax mode tax can be provided in TALLW record. | N | Reject to file if not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) allowance taxable amount. Should be populated if tax is required. In global tax mode tax can be provided in TALLW record. | N | Reject transaction to file if sign indicator is populated and it is not + or -. |
| Allowance Tax taxable Amount | Number (20,4) | Taxable allowance amount for the tax code. Should be populated if tax is required. In global tax mode tax can be provided in TALLW record. | N | Reject transaction to file if taxable amount is populated and it is not numeric. |
TALLT- Allowance Tax Record. This information is inserted into IM_INVOICE_DETAIL_ALLW_TAX table.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TALLW |
| Line id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction Number | Number(10) | Transaction number for this item allowance record. | Y | Reject entire file if:
|
| Allowance Code | Char(6) | Allowance code for this allowance record. Stored in IM_INVOICE_DETAIL_ALLOWANCE.ALLOWANCE_CODE. | Y | Reject transaction to file if:
|
| Allowance Tax Code | Char(6) | Tax Code for Allowance. Should be populated if tax is required. | Y | Reject to file if tax code is null or is not valid. |
| Allowance Tax rate at this Tax code | Number (20,10) | Tax Rate corresponding to the tax code. Should be populated if tax is required. | Y | Reject to file if it is null or not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) allowance taxable amount. Should be populated if tax is required. | Y | Reject transaction to file if sign indicator is null or it is not + or -. |
| Allowance Tax taxable Amount | Number (20,4) | Taxable allowance amount for the tax code. Should be populated if tax is required. | Y | Reject transaction to file if taxable amount is null or is not numeric. |
TNMRC - Non-Merchandise Record. Records of this type will contain non-merchandise costs. These costs are inserted into the IM_DOC_NON_MERCH table. Non-merchandise costs records are only required when the document type is non-merchandise. Non-merchandise cost records are also associated with merchandise type documents if the vendor associated with the document allows non-merch costs on merchandise invoices (IM_SUPPLIER_OPTIONS. MIX_MERCH_NON_MERCH_IND).
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TNMRC |
| Line id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction number | Number(10) | Transaction number for this non-merchandise record. | Y | Reject entire file if:
|
| Non Merchandise Code | Char(6) | Non-Merchandise code that describes this cost. Stored in IM_DOC_NON_MERCH.NON_MERCH_CODE. | Y | Reject transaction to file if:
|
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) Non Merchandise Amt. | Y | Reject transaction to file if sign indicator is null or if it is not + or -. |
| Non Merchandise Amt | Number(20,4) | Cost in the document currency. Stored in IM_DOC_NON_MERCH.NON_MERCH_AMT. | Y | Reject transaction to file if:
|
| Non Merch Tax Code | Char(6) | Tax Code for Non-Merchandise. | N | Reject to file if tax code is populated and is not valid. |
| Non Merch Tax rate at this tax code | Number(20,10) | Tax Rate corresponding to the tax code. | N | Reject to file if it is populated and not numeric. |
| Service Performed Indicator | Char(1) | Indicates if a service has actually been performed. Stored in IM_DOC_NON_MERCH.SERVICE_PERF_IND. | Y | Reject transaction to file if:
|
| Store | Number(10) | Store at which the service was performed. Stored in IM_DOC_NON_MERCH.STORE. | N | Reject transaction to file if:
|
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) non-merch taxable amount. Should be populated if tax is required. In global tax mode tax can be provided in TNMRT record. | N | Reject transaction to file if sign indicator is populated and it is not + or -. |
| Non Merch taxable Amount | Number (20,4) | Taxable non-merch amount for the tax code. Should be populated if tax is required. In global tax mode tax can be provided in TNMRT record. | N | Reject transaction to file if taxable amount is populated and is not numeric. |
TNMRT- Non-Merchandise Tax Record. Records of this type will contain non-merchandise costs. These costs are inserted into the IM_DOC_NON_MERCH_TAX table. Non-merchandise costs records are only required when the document type is non-merchandise. Non-merchandise cost records are also associated with merchandise type documents if the vendor associated with the document allows non-merch costs on merchandise invoices (IM_SUPPLIER_OPTIONS. MIX_MERCH_NON_MERCH_IND).
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TNMRC |
| Line Id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction number | Number(10) | Transaction number for this non-merchandise record. | Y | Reject entire file if:
|
| Non Merchandise Code | Char(6) | Non-Merchandise code that describes this cost. Stored in IM_DOC_NON_MERCH.NON_MERCH_CODE. | Y | Reject transaction to file if:
|
| Non Merch Tax Code | Char(6) | Tax Code for Non-Merchandise. | Y | Reject to file if tax code is null orit is not valid. |
| Non Merch Tax rate at this Tax code | Number(20,10) | Tax Rate corresponding to the tax code. | Y | Reject to file if it is null or not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) non-merch taxable amount. Should be populated if tax is required. | Y | Reject transaction to file if sign indicator is null or it is not + or -. |
| Non Merch taxable Amount | Number (20,4) | Taxable non-merch amount for the tax code. Should be populated if tax is required. | Y | Reject transaction to file if taxable amount is null or it is not numeric. |
TPORD is an optional record type. It is a Purchase order record that would identify purchase orders the invoices could be matched against.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type | Y | TPORD |
| Line Id | Number(10) | Sequential file line number | Y | Halt execution if not in sequence |
| Transaction number | Number(10) | Transaction number for this record | Y | Reject entire file if:
|
| Match Attrib Type | Char(6) | PO | Y | The attribute types. Valid value is PO for Purchase Order. |
| Match Attrib Value | Number(12) | Order No | Y | This is the order Number |
TTAIL - Transaction Tail. Marks the end of a transaction.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TTAIL |
| Line id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Transaction number | Number(10) | Transaction number for the transaction that this record is closing. | Y | Reject entire file if:
|
| Transaction lines | Number(6) | Total number of detail lines within this transaction. | Y | Reject transaction to file if transaction lines is not numeric, if it does not match the count of lines within the transaction, or if it is zero (transaction must have details). |
FTAIL - File TAIL. Marks the end of the upload file.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | FTAIL |
| Line id | Number(10) | Sequential file line number. | Y | Halt execution if not in sequence. |
| Number of lines | Number(10) | Total number of lines within this file not counting FHEAD and FTAIL. | Y | Halt execution if number of lines is not numeric, if it does not match the count of lines within the file (excluding FHEAD and FTAIL), or if it is 2 (FHEAD and FTAIL only, file has no transactions). |
Consider the following.
The EDI Injector Batch process has the ability to recognize only a new document type. In FHEAD of the EDI flat file, the Document Type does not include CRDMC (credit memo cost). When the document type in the flat file is Debit Memo Cost, Debit Memo Qty, Credit Note Request Cost, or Credit Note Request Qty, and if the amount (Total Cost) for a Deal Charge Back Document that is sent over from Merchandising is negative a Credit Memo Cost is created.
For charge back documents, use the following flow chart to determine what document type to be populated in the database.
If the document type is merchandise invoice and the consignment indicator is C, the status is matched; if the consignment indicator is not C, the status is ready for match; if the document type is not merchandise invoice, the status is approved.
The tax codes and rates in the detail of documents are those known for the item and location when the document is not an import Document. Given a combination of TDETL.item and location, we could find a tax. The tax code and tax rate in the tax should be the same as the original tax code and original tax rate in the TDETL.
The merchandises header tax and detail tax are consistent (for example, tax basis by tax rate and tax amount by tax rate). Total header Merchandise Tax information is calculated from total document tax information and tax information for non-merchandise costs. For example, for each Tax Code in TDETL and TNMRC: Thead.Total Tax Amount at this Tax code = total tax from TDETL at this tax code + total tax from TNMRC at this tax code. Total Tax from TDETL at this tax code = sum(original document quantity * original unit cost * original tax rate). Total Tax from TNMRC at this tax code =sum(Non Merch Tax rate * Non Merch Amt).Thead.Total Tax Amount at this Tax code = sum(TVATS.Vat rate * TVATS.cost at this Tax code).
For an EDI upload document, if the Tax Region of the header is different from the tax region of the supplier, it is an import document. Import document will not contain tax information. (LocVatRegion != SupplierVatRegion, then it is an import document). If a document is not an import document, plus the system_option.vat is on; if the TVATS is null, reject to file.
To decide whether a tax code is valid in the TDETL, first find the tax code given the information of item and location. If they are equal, then the tax code is valid; if they are not equal, check if the tax code exists in the effective tax codes; if the tax code exists, then it is valid but is populated to the audit table.
If RTV indicator or consignment indicator is Yes and deal ID is not null, it must reject to file.
Output file format:
FHEAD (1): Start of file.
THEAD (1…n): Transaction (document) level info. Each file must have at least 1 THEAD.
TDETL (0…n): Item detail records for this transaction.
TDTLT (0…n): Item detail tax records for this transaction.
TNMRC (0…n): Non-merchandise records for this transaction. Required on non-merchandise documents, optional otherwise.
TNMRT (0…n): Non-merchandise tax records for this transaction.
TVATS (0…n): Doc tax detail records for this transaction, optional.
TTAIL (1…n): Marks the end of a THEAD record. Each THEAD requires exactly one TTAIL.
FTAIL (1): Marks the end of the file.
If records are encountered in any order other than specified above, execution of program will halt.
Example 1:
FHEAD
THEAD
TNMRC
TVATS
FTAIL (no TTAIL encountered)
Example 2:
FHEAD
THEAD
TNMRC
TNMRT
TVATS
TTAIL /EOF
(no FTAIL encountered)
If a record descriptor is encountered other than those specified in this document, execution of the program will halt.
All character variables should be right-padded with blanks and left justified; all numerical variables should be left-padded with zeroes and right-justified. Null variables should be blank.
|
Note: The file is not threaded, but rather ordered by vendor id (THEAD). It is assumed that this file is broken out by vendor id during the translation process. |
FHEAD - File Header. First record of an upload file.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | FHEAD | Y | |
| Line id | Number(10) | Generated Sequential file line number. | Y | |
| Gentran ID | Char(5) | DNINV | Y | |
| Current date | Char(14) | File date in YYYYMMDDHH24MISS format. | Y | |
| File version | Char(2) | Describes file format version | N | Defaults to 01 (previous format version). Current format is version 02. |
THEAD - Transaction Header. Start of a document transaction.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | THEAD | Y | |
| Line id | Number(10) | Generated Sequential file line number. | Y | |
| Transaction number | Number(10) | Sequential transaction number. All records within this transaction will also have this transaction number. | Y | |
| Document Type | Char(6) | Describes the type of document being downloaded. The document type will determine the types of detail information that are valid for the document downloaded. Retrieved from IM_DOC_HEAD.TYPE where type is debit memo, credit note request or credit memo and in Approved or Posted Status. | Y | |
| Vendor Document Number | Char(50) | Vendor's document number. Retrieved from IM_DOC_HEAD.EXT_DOC_ID. | Y | |
| Invoice Number | Char(6) | Corresponding invoice resolved by the document. Retrieved from IM_DOC_HEAD.REF_DOC. | Y | |
| Vendor ID | Number(10) | Vendor for this document. Retrieved from IM_DOC_HEAD.VENDOR | Y | |
| Document Date | Char(14) | Date the document was entered into the system in YYYYMMDDHH24MISS format. Retrieved from IM_DOC_HEAD.DOC_DATE | Y | |
| Order | Number(10) | Order number for this document, if any. Retrieved from IM_DOC_HEAD.ORDER_NO | N | |
| Location | Number(10) | Location for this document, if any. Retrieved from IM_DOC_HEAD.LOCATION. | N | |
| Location Type | Char(1) | Location type for this document, if any. Retrieved from IM_DOC_HEAD.LOC_TYPE. | N | |
| Terms | Char(15) | Terms of this document. Retrieved from IM_DOC_HEAD.TERMS. | N | |
| Due Date | Char(14) | Date the amount due is due from the vendor (YYYYMMDDHH24MISS format). Retrieved from IM_DOC_HEAD.DUE_DATE. | N | |
| Currency Code | Char(3) | Currency code for this document. Retrieved from IM_DOC_HEAD.CURRENCY_CODE. | N | |
| Exchange Rate | Number(20,10) | Exchange rate for conversion of document currency to the location currency. Retrieved from IM_DOC_HEAD.EXCHANGE_RATE. | N | |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) total cost. | Y | |
| Total Cost | Number(20,4) | Total document cost, including all items and costs on this document. This value is in the document currency. Retrieved from IM_DOC_HEAD.TOTAL_COST. | Y | |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) total tax amount | Y | |
| Total Tax Amount | Number(20,4) | Total Tax amount, including all items and costs on this document. This value is in the document currency. | N | |
| Sign indicator | Char(1) | Indicates a positive (+) or negative (-) quantity | Y | |
| Total Quantity | Number(12,4) | Total quantity of items on this document. This value is in EACHES (no other units of measure are supported in Invoice Matching). Retrieved from IM_DOC_HEAD.TOTAL_QTY. | Y |
TDETL - Item Detail Record. This information is from the IM_DOC_DETAIL_REASON_CODES table.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | TDETL | Y | |
| Line id | Number(10) | Generated Sequential file line number. | Y | |
| Transaction number | Number(10) | Generated Transaction number for this item detail record | Y | |
| Item | Char(25) | Internal SKU/Item for this document. This is always sent. Retrieved from IM_DOC_DETAIL.ITEM. | Y | |
| UPC | Char(25) | UPC for this detail record. | N | |
| UPC Supplement | Number(5) | Supplement for the UPC. Retrieved from UPC_EAN.UPC_SUPPLEMENT. This field is sent if available. | N | |
| VPN | Char(30) | Vendor Product Number. This field is sent if available. Retrieved from ITEM_SUPPLIER.VPN. | N | |
| Comments | Char(200) | Comments associated with Reason Code. Retrieved from IM_DOC_DETAIL_COMMENTS.TEXT | Y | |
| Reason Code | Char(6) | Reason Code for this document. Retrieved from IM_DOC_DETAIL_REASON_CODES.REASON_CODE_ID | Y | |
| Reason Code description | Char(50) | Description associated with Reason Code. Retrieved from IM_REASON_CODES.REASON_CODE_DESC | ||
| Sign indicator | Char(1) | Indicates a positive (+) discrepant qty. | Y | |
| Discrepant Quantity | Number(12,4) | Quantity, in EACHES, of the item that is discrepant for this detail record. Retrieved from IM_DOC_DETAIL_REASON_CODES.ADJUSTED_QTY. | Y | |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) discrepant cost. | Y | |
| Discrepant cost | Number(20,4) | Unit cost, in document currency, of the item that is discrepant for this detail record. Retrieved from IM_DOC_DETAIL_REASON_CODES.ADJUSTED_UNIT_COST. | Y | |
| Original Tax code | Char(6) | Tax code for item. | N | |
| Original Tax rate | Number(20,10) | Tax Rate for the tax code/item. Used for rate tax. Optional otherwise. | N | |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) taxable amount for rate based tax. Used for rate tax. Optional otherwise. | N | |
| Taxable amount | Number(20,4) | Taxable amount for the line item for rate base tax. Used for rate tax. Optional otherwise. | N | |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) per unit tax value. Used for per unit tax, optional otherwise | N | |
| Per unit tax value | Number(20,4) | Per unit tax value. Used for per unit tax, optional otherwise | N |
TDTLT - Item Detail Tax Record. This information is from the IM_DOC_DETAIL_RC_TAX table.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | TDTLT | Y | |
| Line Id | Number(10) | Generated Sequential file line number. | Y | |
| Transaction number | Number(10) | Generated Transaction number for this item detail record | Y | |
| Item | Char(25) | Internal SKU/Item for this document. This is always sent. Retrieved from IM_DOC_DETAIL.ITEM. | Y | |
| UPC | Char(25) | UPC for this detail record. | N | |
| UPC Supplement | Number(5) | Supplement for the UPC. Retrieved from UPC_EAN.UPC_SUPPLEMENT. This field is sent if available. | N | |
| VPN | Char(30) | Vendor Product Number. This field is sent if available. Retrieved from ITEM_SUPPLIER.VPN. | N | |
| Original Tax code | Char(6) | Tax code for item. | N | |
| Original Tax rate | Number(20,10) | Tax Rate for the tax code/item. Used for rate tax. Optional otherwise. | N | |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) taxable amount for rate based tax. Used for rate tax. Optional otherwise. | N | |
| Taxable amount | Number(20,4) | Taxable amount for the line item for rate base tax. Used for rate tax. Optional otherwise. | N | |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) per unit tax value. Used for per unit tax, optional otherwise | N | |
| Per unit tax value | Number(20,4) | Per unit tax value. Used for per unit tax, optional otherwise | N |
TNMRC - Non-Merchandise Record. Records of this type will contain non-merchandise costs. These costs are retrieved from the IM_DOC_NON_MERCH table. Non-merchandise cost records are only required when the document type is non-merchandise.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | TNMRC | Y | |
| Line id | Number(10) | Generated Sequential file line number. | Y | |
| Transaction number | Number(10) | Generated Transaction number for this non-merchandise record. | Y | |
| Non Merchandise Code | Char(6) | Non-Merchandise code that describes this cost. Retrieved from IM_DOC_NON_MERCH.NON_MERCH_CODE. | Y | |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) non merchandise amount. | Y | |
| Non Merchandise Amt | Number(20,4) | Cost in the document currency. Retrieved from IM_DOC_NON_MERCH.NON_MERCH_AMT. | Y | |
| Non-merch Tax code | Char(6) | Tax Code for Non-merchandise amount. | ||
| Non-merch Tax rate at this Tax code | Number(20,10) | Tax Rate corresponding to the Tax code. | ||
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) non-merch taxable amount. | ||
| Non-merch taxable Amount | Number (20,4) | Taxable non-merch amount for the Tax code. |
TNMRT- Non-Merchandise Tax Record. Records of this type will contain non-merchandise costs taxes. These cost taxes are retrieved from the IM_DOC_NON_MERCH_TAX table. Non-merchandise cost tax records are only required when the document type is non-merchandise. For a merchandising document the records are optional.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | TNMRC | Y | |
| Line Id | Number(10) | Generated Sequential file line number. | Y | |
| Transaction number | Number(10) | Generated Transaction number for this non-merchandise record. | Y | |
| Non Merchandise Code | Char(6) | Non-Merchandise code that describes this cost. Retrieved from IM_DOC_NON_MERCH.NON_MERCH_CODE. | Y | |
| Non-merch Tax code | Char(6) | Tax Code for Non-merchandise amount. | ||
| Non-merch Tax rate at this tax code | Number(20,10) | Tax Rate corresponding to the Tax code. | ||
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) non-merch taxable amount. Should be populated if tax is required. In global tax mode tax can be provided in TNMRT record. | ||
| Non-merch taxable Amount | Number (20,4) | Taxable non-merch amount for the tax code. Should be populated if tax is required. In global tax mode tax can be provided in TNMRT record. |
TVATS - Tax Detail record.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | TVATS | Y | |
| Line id | Number(10) | Sequential line number. | Y | |
| Transaction number | Number(10) | Y | ||
| Tax code | Char(6) | Tax code that applies to cost. | Y | |
| Tax rate | Number(20,10) | Tax Rate corresponding to the Tax code. | Y | |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) Original Document Quantity amount. | Y | |
| Taxable value at this Tax code | Number(20,4) | Total amount that must be taxed at the above Tax code. | N |
TTAIL - Transaction Tail. Marks the end of a transaction.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | TTAIL | Y | |
| Line id | Number(10) | Generated Sequential file line number. | Y | |
| Line number | Number(10) | Generated Transaction number for the transaction that this record is closing. | Y | |
| Transaction lines | Number(6) | Total number of detail lines within this transaction. | Y |
FTAIL - File TAIL. Marks the end of the upload file.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | FTAIL | Y | |
| Line id | Number(10) | Generated Sequential file line number. | Y | |
| Number of lines | Number(10) | Total number of lines within this file, not including FHEAD and FTAIL. | Y |
The application allows injecting spreadsheets in ODS format with document data to be processed similarly to EDI injector flow. ODS files are spreadsheets in ODF data format. This format is a binary format and is nothing but archive (zip) of actual data in XML format.
Out of the box the application will have a set of templates defined within the system. The templates will be grouped by a category. At the same time retailers will be able to define custom templates.
Template categories will be document type based. There will be one or more Merchandising Invoice templates, one or more Credit Note templates, etc.
The purpose of templates is to define the set of worksheets that are suitable for document entry process. Each worksheet will have a set of fields. While some worksheets are mandatory for a particular document type, some may be optional and as such can be removed by customization process to better suite retailer business process. Same logic applies to worksheet fields. While some of the fields are mandatory, some are optional and can be removed by customization.
Customization will be done via backend. Retailers will be able to delete base installed template. It will be up to retailers to guarantee that all the required templates are present and correct, as well as that they will not conflict with existing templates.
Template definition will be done using existing data model.
The template type will match existing document types that are allowed to be manually entered.
NMRCHI
MRCHI
DEBMEQ
DEBMEC
CRDNRQ
CRDNRC
CRDMEQ
CRDMEC
The main difference for default template definitions will be presence or lack thereof of tax related information. Another difference will be the presence or lack thereof of detail records.
Templates are defined in the following tables
S9T_TEMPLATE - template table
S9T_TMPL_WKSHT_DEF - template worksheets table
S9T_TMPL_COLS_DEF- template worksheet columns table.
The template data structure is shared by multiple applications. Invoice Matching templates are defined under DOCS9T category.
The order of the columns within the worksheet is NOT customizable and is predefined within the application. The order of the worksheets, on the other hand can be changed.
Errors identified during processing will be included in an error worksheet within the rejection file that can be retrieved via UI.
Invoice Matching exports data to financial staging tables. This section describes these tables.
The following types of financial information are imported in Invoice Matching:
Terms ranking data
Variable department/class account segments
Variable company/location account segments
Terms ranking information is used in the best terms calculation to choose the best term for each document. This best terms information is posted to the financial system.
Variable department/class and company/location segments are used to determine the account segments to which a document is posted.
The retailer is responsible for populating variable department/class and company/location segments. No API is provided.
Partners can only be set up as suppliers in the Financial System. After the partner is setup as a supplier, the supplier integration step is run and the partner is created as a supplier in Merchandising. Then the partner must be manually created in Merchandising using the Merchandising Supplier ID which was generated as part of the supplier integration step. Partner functionality within Merchandising and Invoice Matching can then proceed normally. The Merchandising supplier generated as part of this process is not used.
Invoice Matching uses location account segments in general ledger (GL) account mappings. The location account segments are accessed and maintained through the Invoice Matching user interface. The segments are dynamically assigned during posting, based on the location on the invoice. The data is stored in the location account segments table (IM_DYNAMIC_SEGMENT_LOC).
Invoice Matching uses department/class account segments in GL account mappings. The department/class account segments are accessed and maintained through the Invoice Matching user interface. The segments are dynamically assigned during posting, based on the item on the invoice. The data is stored in the department/class account segment table (IM_DYNAMIC_SEGMENT_DEPT_CLASS).
Invoice Matching writes two types of transactions to the financial staging tables. Documents which are expected to be sent to the A/P system are sent to the IM_AP_STAGE_HEAD and IM_AP_STAGE_DETAIL tables. Transactions expected to go to the G/L system go to the IM_FINACIALS_STAGE table. The RFI integration will send the data in these tables to EBS or Peoplesoft. Retailers using other financial systems can still use these tables to integrate to their specific A/P and G/L systems.
For complex and fixed deals, batch processes copy most of the data from the Merchandising staging tables into Invoice Matching detail tables (IM_COMPLEX_DEAL_DETAIL, IM_FIXED_DEAL_DETAIL). Some of the data on these tables is later referenced during the posting process for the created documents, including:
Location
Item
To understand the process that posts data from Invoice Matching to the financials staging table (IM_FINANCIAL_STAGE), see "Financial Posting Batch Design".
Receipt tracking functionality allows the retailer to track what receipts have posted. This processing helps the retailer check the integrity of its financial data.
Note that Oracle Retail does not provide packaged reporting in conjunction with this processing. Rather, the retailer builds its own processes and creates its own reporting mechanisms against the data resulting from the receipt tracking functionality.
The tables illustrated below are for the retailer's understanding, but the data on these tables should not be used by the retailer as it builds its processes and reports.
Each area of the system that matches receipts to invoices updates the IM_RECEIPT_ITEM_POSTING table. This table tracks how much of an individual receipt item has been matched and posted.
IM_RECEIPT_ITEM_POSTING
| Column Type | Type | Nullable |
|---|---|---|
| SEQ_NO | NUMBER(10) | N |
| RECEIPT_ID | NUMBER(12) | N |
| ITEM_ID | VARCHAR(25) | N |
| QTY_MATCHED | NUMBER(20,4) | Y |
| QTY_POSTED | NUMBER(20,4) | Y |
IM_RCPT_ITEM_POSTING_INVOICE
| Column Type | Type | Nullable |
|---|---|---|
| SEQ_NO (from IM_RECEIPT_ITEM_POSTING) | NUMBER(10) | N |
Once posting is completed, the following staging tables contain all currently posted entries. Thus, to build processes and reporting that tracks receipt posts, the retailer should use only the data from these staging tables.
IM_RECEIPT_ITEM_POSTING_STAGE
| Column Type | Type | Nullable |
|---|---|---|
| SEQ_NO | NUMBER(10) | N |
| RECEIPT_ID | NUMBER(12) | N |
| ITEM_ID | VARCHAR(25) | N |
| QTY_POSTED | NUMBER(20,4) | N |
| CREATE_DATE | DATE | N |
IM_RCPT_ITEM_POSTING_INV_STAGE
| Column Type | Type | Nullable |
|---|---|---|
| SEQ_NO | NUMBER(10) | N |
| DOC_NO | NUMBER(10) | N |
For a given line item on a receipt, a line item can be split between multiple invoices. For example, one invoice could match half of a line item; another invoice could match the other half of the line item. Two separate lines would thus appear. The retailer should note that these values (and those in equivalent business scenarios) need adding together to indicate how much of a given receipt item is posted.
