| Oracle® Invoice Matching Operations Guide Release 13.2.9 E73506-01 |
|
 Previous |
 Next |
This chapter describes how ReIM integrates with other systems, related interfaces, and file layouts. It includes an integration overview, a discussion of EDI (with layouts), an explanation of how ReIM interfaces with financial systems, and a summary of LDAP user authentication.
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.
ReIM receives supplier invoices and credit notes through EDI or through on-line entry processes. These document types are described later in this chapter.
ReIM generates debit memos, credit note requests and credit memos for various reasons. Each of these documents is recorded in ReIM tables to allow for retailer reporting. Also, an ReIM 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.
ReIM is able to access foundation data, such as item, purchase order, supplier, and other information directly from RMS tables. ReIM 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 RMS, and certain data elements are extracted from RMS into ReIM tables to support ReIM-specific actions performed against receipts (for example, splitting receipt quantities, updating statuses, and so on).
Purchase Orders
Purchase orders (POs) are created in RMS 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, RMS supports different costs for the same item going to different locations. PO costs are used to value receipt quantities.
Supplier Trait
An RMS function, supplier traits are used as a grouping mechanism for suppliers with common characteristics. They are utilized for mass updates. This data is used in setting up tolerances within ReIM.
Item
ReIM 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
TAX is embedded in the cost of the item. ReIM provides for validation of taxes charged on the invoice against TAX codes/rates stored in RMS tables for the item.
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 ReIM 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. ReIM 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
RMS 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 ReIM through an RMS extract. ReIM creates a charge back document for these billings, which may be subject to edit/approval in ReIM or automatically processed to the financial staging table for export to the retailer's accounts payable solution, based on an RMS parameter.
Other Data Elements Received from RMS
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 ReIM update receipts held in RMS tables. Receiver adjustments, resulting from the ReIM discrepancy resolution process, create cost and/quantity adjustments to receipt tables in RMS, 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), ReIM provides and update to the invoice match status (that is, from unmatched to matched) on the shipment table in RMS.
Shipment (Receipts) Table Quantity Matched Update
When ReIM matches a portion and/or all of a receipt line to an invoice line, ReIM makes a corresponding update to the quantity matched column.
For information about Oracle Single Sign-On and Oracle Retail Invoice Matching (ReIM) 13.2.4, see the latest Oracle Retail Invoice Matching Installation Guide. For information on how to integrate ReIM with Oracle Retail Workspace, see the latest Oracle Retail Workspace Implementation Guide. Oracle Retail Workspace is a supported configuration of Oracle WebCenter Spaces 11.1.1.5 for Oracle Retail. For the Oracle Retail 13.2.x enterprise, Oracle Retail Workspace replaces previous versions of Oracle Retail Workspace. There is no more packaged Oracle Retail Workspace code, only configuration instructions for Oracle WebCenter Spaces 11.1.1.5. The Oracle Retail Workspace configuration utilizes the external application functionality and the application navigator taskflow of the Oracle WebCenter Framework to configure ReIM in Oracle WebCenter Spaces.
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.
ReIM has two file-based EDI interfaces. Note that neither follows the VICS EDI standard. The ReIM 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 invoice upload 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 ReIM to suppliers.
For information about ReIM batch processes related to both of these types of EDI, see Chapter 9, "Batch Processes." Note that although the majority of invoices are created through either EDI upload 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 invoice upload (ediupinv) batch process uploads invoices and credit notes from the EDI into the invoice-matching tables. This process validates the information in the file against itself and against the RMS (or equivalent merchandising system)/ReIM database. A limited set of data validation errors cause the invalid transaction to be written to error tables (IM_EDI_REJECT_DOC_xxx) where the data can be corrected through an online process.
The following errors are written to the EDI reject table for the user to manually correct through the front end:
Supplier number (or Partner ID): This value must be a valid supplier (SUPS table) or partner (PARTNER table) in RMS (or the equivalent merchandising system).
Order numbers: Order numbers must be approved and created for the supplier or linked suppliers in RMS (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 RMS or the equivalent merchandising system (meaning that the relationship must exist on the ORDLOC table).
Terms code: All terms must exist within RMS or the equivalent merchandising system on the TERMS table.
Invoice 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).
A limited set of 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 Invoice Upload 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 optional for Credit Note docs and debit memo docs.TALLW (0…n): Allowance records for this item. TALLW is optional.TNMRC (0…n): Non-merchandise records for this transaction. Required on non-merchandise documents, optional otherwise.TVATS (0…n): VAT breakdown by VAT 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 TDETLIf records are encountered in any order other than specified above, execution of program will halt.Example:FHEADTHEADTNMRCFTAIL (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. Multi-location invoices will be inserted into IM_PARENT_INVOICE, IM_PARENT_INVOICE_DETAIL and IM_PARENT_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 RMS system. The following is never anticipated to happen: only locations A, B, and C exist in RMS; 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 this criteria will be rejected to the file by the EDI Upload 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. |
THEAD - Transaction Header. Start of a document transaction.
| 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(6) | 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:
|
| Vendor Type | Char(6) | Type of vendor (either supplier or partner) for this document. Stored in IM_DOC_HEAD.VENDOR_TYPEValid 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:
Reject transaction to tables if:
|
| Vendor ID | Char(10) | Vendor for this document. Stored in IM_DOC_HEAD.VENDOR_TYPE | Y | Reject transaction to file if:
Reject transaction to tables if Vendor Document Date is:
|
| Vendor Document Date | Char(14) | Date document was issued by the vendor (in YYYYMMDDHH24MISS format). Stored in IM_DOC_HEAD.DOC_DATE | Y | |
| Order Number/ RTV Order Number | Number(10) | 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 (12,4) | Exchange rate for conversion of document currency to the primary 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 VAT Amount | Number(20,4) | Total VAT 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 ReIM). 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:
|
| Multi Location | Char(1) | Indicates if this invoice goes to multiple locations. If Yes, the record should be inserted to IM_PARENT_INVOICE table. | Y | Reject transaction to file if:
|
| Consignment indicator | Char(1) | Y | Y | Reject transaction to file if:
Do not reject transaction to table if Consignment is Y. |
| Deal Id | Number(10) | Deal Id from RMS 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 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(30) | 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(30) | 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(30) | 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(30) | 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 - VAT breakdown by VAT 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) | Y | Reject entire file if:
|
|
| VAT code | Char(6) | VAT code that applies to cost. | Y | Reject to file if VAT code is not valid. |
| VAT rate | Number(20,10) | VAT Rate corresponding to the Tax code. | Y | Reject to file if VAT rate is not numeric. |
| 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 -. |
| Cost at this VAT code | Number(20,4) | Total amount that must be taxed at the above VAT code. | Y | 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. | YExclusive with item | Reject transaction to file if:
Reject transaction to tables if:
|
| UPC Supplement | Number(5) | Supplement for the UPC. Note: UPC Supp is only valid for 9.0 implementation. For 10.1 implementation, this field will ALWAYS be blank. | 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. | YExclusive 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:
|
| 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 -. |
| OriginalDocument Quantity | Number(1,2,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:
|
| 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(2,0.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 VAT Code | Char(6) | Tax code for item. | Y | Reject to file if VAT code is invalid. |
| Original VAT rate | Number(20,10) |
Tax Rate for the Tax code/item. |
Y | Reject to file if VAT rate is not numeric. |
| Sign Indicator | Char(1) | Indicates either a positive (+) or a negative (-) Original Document Quantity amount. | Y | Reject transaction to file if:
|
| Total Allowance | Number(2,0,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:
|
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 VAT Code | Char(6) | VAT Code for Allowance. | Y | Reject to file if VAT code is not valid. |
| Allowance VAT rate at this VAT code | Number (20,10) | VAT Rate corresponding to theVAT code. | Y | Reject to file if 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.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(5) | Describes file record type. | Y | TNMRC |
| Line id | Char(5) | 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 VAT Code | Char(6) | VAT Code for Non-Merchandise. | Y | Reject to file if VAT code is not valid. |
| Non Merch VAT code at this VAT code | Number(20,10) | VAT Rate corresponding to the VAT code. | Y | Reject to file if not numeric. |
| 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 - |
| Service Performed Indicator | Char(6) | 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:
|
TTAIL - Transaction Tail. Marks the end of a transaction.
| Field Name | Field Type | Description | Req | Validation |
|---|---|---|---|---|
| Record descriptor | Char(6) | 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 document upload 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 RMS is negative a Credit Memo Cost is created.
For the charge back documents, use the following flow chart to determine the what document type to be populated in the database.
If the document type is merchandise invoice and the consignment indicator is Y, the status is matched; if the consignment indicator is not Y, the status isready for match; if the document type is not merchandise invoice, the status is approved.
If the consignment indicator is Y, then set the terms to Due Immediately terms (term ID = 48), and set the terms discount percentage to 0.
That 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 VAT 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 VAT Amount at this VAT code = sum(TVATS.Vat rate * TVATS.cost at this VAT 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.
If Item field is populated and there is an error it should always reject to file. In order to reject to the tables, we must have the UPC field populated and not the Item field.
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. TNMRC (0…n): Non-merchandise records for this transaction.Required on non-merchandise documents, optional otherwise.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:FHEADTHEADTNMRCTVATSFTAIL (no TTAIL encountered)If a record descriptor is encountered other than those specified in this document, execution of 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 |
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(30) | Vendor's document number. Retrieved from IM_DOC_HEAD.EXT_DOC_ID. | Y | |
| Group Id | Char(10) | Invoice Group ID Retrieved from IM_DOC_HEAD.GROUP_ID. | Y | |
| Invoice Number | Char(10) | 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(12,4) | Exchange rate for conversion of document currency to the primary 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 VAT amount | Y | |
| Total VAT Amount | Number(20,4) | Total VAT 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 ReIM). Retrieved from IM_DOC_HEAD.TOTAL_QTY. | Y |
TDETL - Item Detail Record. This information is inserted into 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. Retrieved from UPC_EAN.UPC (RMS 9.0) or ITEM_MASTER.ITEM (RMS 10.1). This field is sent if available. Note: UPC is used for RMS 9.0 and Ref-Item is used for RMS 10.1. Ref-Item consists of UPC and UPC-Supp appended together with a separating hyphen(-). | N | |
| UPC Supplement | Number(5) | Supplement for the UPC. Retrieved from UPC_EAN.UPC_SUPPLEMENT. This field is sent if available.Note: UPC Supp is only valid for 9.0 implementation. For 10.1 implementation, this field will always be blank. | 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 VAT code | Char(6) | VAT code for item. | ||
| Original VAT rate | Number(20,10) | VATRate for the VAT code/item. |
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. 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) | 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 VAT code | Char(6) | VAT Code for Non_merchandise. | Y | |
| Non Merch VAT code at this VAT code | Number(20,10) | VATRate corresponding to the VAT code. |
TVATS - VAT 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 | ||
| VAT code | Char(6) | VAT code that applies to cost. | Y | |
| VAT rate | Number(20,10) | VAT Rate corresponding to the VAT code. | Y | |
| Sign indicator | Char(1) | Indicates either a positive (+) or a negative (-) Original Document Quantity amount. | Y | |
| VAT Basis | Number(20,4) | Total amount that must be taxed at the above VAT code. | Y |
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 |
ReIM exports data to financial staging tables. This section describes these tables.
The following types of financial information are imported in ReIM:
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.
ReIM uses location account segments in general ledger (GL) account mappings. The location account segments are accessed and maintained through the ReIM 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).
ReIM uses department/class account segments in GL account mappings. The department/class account segments are accessed and maintained through the ReIM 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).
To be independent of any single financial product, such as Oracle Financials, Oracle retail has created a generic interface. That is, Oracle Retail writes records to a single generic table from which custom retailer code can read records and process data as necessary. The retailer is responsible for creating a process that sends transactions to the financials system.
For complex and fixed deals, batch processes copy most of the data from the RMS staging tables into ReIM 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 ReIM to the financials staging table (IM_FINANCIAL_STAGE), see "Financial Posting" in Chapter 9.
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(10) | N |
| ITEM_ID | VARCHAR(25) | N |
| QTY_MATCHED | NUMBER(12,4) | Y |
| QTY_POSTED | NUMBER(12,4) | Y |
IM_RCPT_ITEM_POSTING_INVOICE
| Column Type | Type | Nullable |
|---|---|---|
| SEQ_NO (from IM_RECEIPT_ITEM_POSTING) | NUMBER(10) | N |
| DOC_ID | NUMBER(10) | N |
| STATUS | VARCHAR2(1) | Y |
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(10) | N |
| ITEM_ID | VARCHAR(25) | N |
| QTY_POSTED | NUMBER(12,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.
ReIM supports only LDAP user authentication. To indicate the authentication method, select LDAP for the property called authentication_source in the reim.properties file.
For information, see "Authentication Settings" in Chapter 2,
Baclemd System Administration and Configuration."
Light Directory Access Protocol (LDAP) is the means of user authentication supported by ReIM. It defines a network protocol for accessing information in a directory.
Only LDAP is used within ReIM for user authentication. Because ReIM has specific requirements for ReIM user roles and permissions that are easily configurable by the retailer, they are defined in the application itself. ReIM reads standard user information from an LDAP server.
There are two types of roles; business roles and enterprise roles. Business roles are defined within the ReIM application and define what functional privileges the user has. Enterprise roles are defined in LDAP (it is the same as user group). Any ReIM user defined in LDAP should be part of an LDAP user group defined by roles_base_dn in ldap.properties
If the retailer already stores user information using LDAP, the only interfacing configuration required is through the LDAP-specific properties file. The entries in this file point ReIM to the appropriate machine and port to find the LDAP server. Other properties also may be modified to reflect the names of attributes that the retailer uses in its LDAP schema.
Setting up the LDAP interface entails completing tasks within LDAP andReIM as follows.
In LDAP, complete the following steps.
Define the following attributes (or find the existing attributes that provide the same information) within your LDAP (actual name is not important).
User ID (standard uid).
Password.
First name (standard cn).
Last name (standard sn).
Preferred user language (identifies user locale).
Preferred user country (identifies user locale).
Preferred email (standard mail).
|
Note: All attributes listed above should be single value and mandatory. For attributes with multiple values, the first value is used within ReIM. |
Create an attribute class that encompasses all the attributes above.
Select a container within your LDAP to hold users, which may be created directly in the container or in enclosed containers.
Either create new users based on the attribute class just created above, or add the attribute class to the existing users. Define missing values as needed.
In ReIM, complete the following steps.
In reim.properties, change authentication_source to LDAP.
In ldap.properties, define the values for the parameters shown in the following table.
| Parameter | Description |
|---|---|
| connection_url | Machine and port for your LDAP server |
| user_dn | User name for the user defined within LDAP that has ADMIN privileges |
| user_password | Password for the user above |
| base_dn | Name of the container for ReIM users |
| login_id_attribute_name | Name for user ID attribute used within the attribute class |
| user_first_name_attribute_name- | Name for the first name attribute used within the attribute class |
| user_last_name_attribute_name | Name for the last name attribute used within the attribute class |
| user_email_attribute_name- | Name for the email attribute used within the attribute class |
| user_password_attribute_name- | Name for the password attribute used within the attribute class |
| user_language_attribute_name | Name for the preferred language attribute used within the attribute class |
| user_country_attribute_name- | Name for the preferred country attribute used within the attribute class |
| WildCard | Wild character used to identify any search criteria filter |
http://www.openldap.org/
This site contains the OpenLDAP main page. This site contains introduction, downloads, and documentation.
http://www.iit.edu/~gawojar/ldap/
This site is the LDAP browser site.
http://ldap.akbkhome.com/
This site contains an LDAP schema view with some definitions of the standard LDAP object classes and attributes.
