| Oracle® Invoice Matching Operations Guide Release 13.2.9 E73506-01 |
|
 Previous |
This chapter provides the following:
An overview of the batch architecture
A functional summary of each batch process, along with its dependencies
A description of some of the features of the batch processes (batch return values, batch threading, and so on)
Development designs for each batch process
ReIM batch processes are run as Java applications. Batch processes engage in their own primary processing. However, they utilize services when they must engage in actions outside their primary processing (for example, when they utilize a helper method, touch the database, and so on).
Services retrieve the data on which the batch processes work to complete their tasks. As noted in Chapter 3, "Technical Architecture," the service layer consists of a collection of Java classes that implements business logic (data retrieval, updates, deletions, and so on) through one or more high-level methods.
The business logic occurs within the service code, while the technical processing occurs within the batch code.
Note the following characteristics of the ReIM batch processes:
They are not accessible through a graphical user interface (GUI).
They are scheduled by the retailer.
They are designed to process large volumes of data.
Although ReIM runs at all times, batch processes should be executed when the fewest number of users are in the system, such as at night.
ReIM EDI-related batch processes are file based. For example, they either input a flat file into the system (EDI invoice upload) from outside the system, or they output a flat file from the system (EDI invoice download) to be sent to another system (that of a vendor). Both the EDI invoice upload and the EDI invoice download batch processes are described later in this chapter.
Other batch processes within ReIM do not input or output files. Rather, the goal of these batch processes is to take a snapshot of potentially large amounts of data from the key tables within the database, transform that data through processing, and then return it.
Internal batch processes that are described later in this chapter include:
Auto-match
Batch purge
Account purge
Discrepancy purge
Disputed credit memo action rollup
Reason credit action rollup
The third type of batch process within ReIM takes a snapshot of potentially large amounts of data from the key tables within the database, transforms that data through processing, and then writes that data to staging tables.
This communication process has been designed with the assumption that, during production, ReIM will reside within the same database as the merchandising system. Presumably, during implementation, the retailer will develop an optimum way to move the applicable data from the staging tables to the appropriate location for that data.
The internal batch processes that write to staging tables are described later in this chapter. They include the following:
Financial posting
Receiver adjustment
The fourth type of batch process within ReIM extracts data from merchandising system staging tables, create documents with the data, and write the data to ReIM tables. The batch processes that follow this processing pattern include the following:
Complex deal upload
Fixed deal upload
The following table describes ReIM batch processes. The table order reflects the dependencies that exist among the ReIM batch processes but does not include any dependencies that exist between ReIM and the merchandising system with which it interacts.
| Batch Name | Class (oracle.retail.reim.batch.jobs) |
|---|---|
| Tables purge | TablesPurgeBatch |
| Account purge | AccountWorkspacePurgeBatch |
| Discrepancy purge | DiscrepancyPurgeBatch |
| EDI Invoice upload | EdiUploadBatch |
| Auto-match | AutoMatchBatch |
| Receipt write-off | ReceiptWriteOffBatch |
| Reason code action rollup | ReasonCodeActionRollupBatch |
| Disputed credit memo action rollup | DisputedCreditMemoResolutionRollupBatch |
| Financial posting | FinancialPostingBatch |
| EDI Invoice download | EdiDownloadBatch |
| Complex deal upload | ComplexDealUploadBatch |
| Fixed deal upload | FixedDealUploadBatch |
The following table summarizes ReIM batch processes and includes both a description of each batch process's business functionality and its batch dependencies:
| Batch Processes | Details | Batch Dependencies |
|---|---|---|
| Batch purge | This process deletes data from database tables while maintaining database integrity. This process deletes records from the ReIM application that meet certain business criteria (for example, records that are marked for deletion by the application user, records that linger in the system beyond certain number of days, and so on). | |
| Account purge | This process deletes the accounts maintained locally in the ReIM application. | |
| Discrepancy purge | The discrepancy purging program deletes data from database tables while maintaining database integrity. This program deletes records from ReIM that have discrepancies of zero. | |
| EDI invoice upload | This batch process uploads merchandise, non-merchandise invoices, credit notes, debit memos, and credit note requests from the EDI into the invoice-matching tables. |
|
| Auto-match | Auto-match is a system batch process that attempts to match invoices to receipts without manual intervention. Invoices that are in ready for match, unresolved, or multi-unresolved status are retrieved from the database to be run through the auto-match algorithm. The processing consists of three levels - summary, detail, and header (Tax only). | |
| Receipt write-off | In order for retailers to track received goods not invoiced, they must have the ability to 'write-off' these goods for financial tracking. ReIM has a system parameter (which can be overwritten at the supplier level) defining the maximum amount of time an open, non-fully matched receipt will be available for matching. Every time the Receipt write-off process is run, each non-fully matched open receipt received date is compared with the current date minus the system parameter. If the received date is before this difference, the receipt is 'written-off,' and the invoice match status is closed. | Auto-match and any associated processing must run prior to this batch processing. |
| Reason code action rollup | This batch process sweeps the action staging table and creates debit memos, credit memos, and credit note requests as needed. Only a single debit or credit memo is created per invoice/discrepancy type, with line details from all related actions for the same discrepancy type. If hold invoice functionality is on, each generated document is assigned the invoice number to which it corresponds to ensure all related documents are released to accounts payable at the same time. This process deletes these records when completed; they are deleted after posting. Note that a separate, retailer-created batch process sweeps the receiver adjustment table. The action staging table is used during posting to post the reason code actions to the financial staging table. A separate, retailer-created batch process sweeps the receiver adjustment table. The process compares the unit cost and/or quantity received for the item on the shipment with the expected unit cost and/or quantity on the IM_RECEIVER_COST_ADJUST and/or IM_RECEIVER_UNIT_ADJUST tables. If a match exists, the receiver cost and/or unit adjustment has occurred in RMS (or the equivalent merchandising system). As a result, the process sets the 'pending adjustment' flag on IM_INVOICE_DETAIL table to false for the invoice line. The reason code actions are rolled up for an invoice only if no invoice lines on the invoice have any pending adjustments. | |
| Disputed credit memo action rollup | The disputed credit memo action rollup process checks the records on the IM_REVERSAL_RESOLUTION_ACTION table and rolls up the credit memo detail lines by document/item/reason code. The rollup occurs only if all lines on a disputed credit memo have been completely resolved (that is, no cost or quantity discrepancy records remain for the credit memo). After the rollup, a new set of detail lines associated with the resolution reason codes replace the original set of detail lines associated with the debit reason codes on the IM_DOC_DETAIL_REASON_CODES table. | The disputed credit memo action rollup must occur before resolution posting and after receiver adjustment. |
| Financial posting | A recurring resolution posting process retrieves all matched invoices and approved documents. If hold invoice functionality is used, then matched Credit Notes rather than approved Credit Notes are processed. For each invoice, the batch process writes applicable financial accounting transactions to either of the following tables: IM_FINANCIALS_STAGEThe AP staging tables, IM_AP_STAGE_HEADER and IM_AP_STAGE_DETAIL, if the RMS System-Options table: FINANCIAL_AP = O. | |
| EDI invoice download | The EdiDownload module creates a flat file to match the EDI invoice download file format. The module retrieves all header, detail, and non-merchandise information and formats the data as needed.In other words, the EDI invoice download process retrieves debit memos, credit note requests, and credit memos in 'approved' status from the resolution posting process and creates a flat file. The client converts the flat file into an EDI format by the client and sends it through the EDI invoice download transaction set. | Auto-match must run prior to the EDI invoice download. |
| Complex deal upload | This module reads data from RMS staging tables, creates credit memos, debit memos, and credit note requests out of the data, and stores the supporting deal data on a ReIM table for later use during posting. | The RMS staged data must be purged after the upload |
| Fixed deal upload | This module reads data from RMS staging tables, creates credit memos, debit memos, and credit note requests out of those, and stores the supporting deal data on a ReIM table for later use during posting. | The RMS staged data must be purged after the upload |
This section describes the features of batch processes.
If the client uses a scheduler, batch process arguments are placed into the scheduler.
If the client does not use a scheduler, batch process parameters must be passed in at the UNIX command line.
Each of these scripts interacts with the generic shell script. These scripts take any and all arguments that their corresponding batch process would take when executing.
The following guidelines describe the batch process return values that ReIM batch processes utilize:
SUCCESS = 0
FAILED_INIT = 1
FAILED_PROCESS = 2
FAILED_WRAPUP = 3
SUCCESS_WITH_REJECTS_TO_DB = 4
SUCCESS_WITH_REJECTS_TO_FILE = 5
SUCCESS_WITH_REJECTS_TO_DB_AND_FILE = 6
UNKNOWN = -1
Log file locations are determined by the retailer through the logj4.properties file. If an error occurs that causes a batch process to suddenly come to a complete halt, the system writes to the configured log appender. See Chapter 2, "Backend System Administration and Configuration," for more information.
The following batch processes shown below have multi-threading capabilities. The settings related to the multi-threading options for each batch process are established in the reim.properties file. See Chapter 2, "Backend System Administration and Configuration," for more information.
This process is threaded by a group (or bulk) of deals. Each group constitutes a thread.
This process is threaded by a group (or bulk) of deals. Each group constitutes a thread.
Most ReIM batch processes do not utilize any type of restart and recovery procedures. Rather, if a restart is required, the process can simply be restarted, and it will start where it left off.
This solution is true for all batch processes other than those noted below:
EDI invoice upload (its restart and recovery methods is described in its design below).
EDI invoice download (its restart and recovery methods is described in its design below).
Batch processes are executed through the BatchRunner framework. This framework is responsible for bootstrapping the Spring container and ensuring that the batch job is passed the appropriate arguments. The arguments for the batch runner are as follows:
Batch job class name
batch-alias-name
Batch arguments
|
Note: Batches are run with an alias name rather than with a user name/password combination. The alias name is mapped to the user credentials inside a password store called a wallet.At run time the batches access the wallet and retrieve the user ID and password for authentication purposes. |
Below is an example of how the batch runner would be utilized to execute the EdiUploadBatch process:
reimediinvupload batch-alias-name/dir/input.dat /dir2/output.dat
The BatchRunner requires the application libraries (JAR files) to be on the classpath in order to execute successfully. Retailers wishing to configure the BatchRunner manually should consult the generic UNIX batch script generated during the install process for assistance in determining which libraries should be included for a particular batch process.
The batch purging process deletes data from database tables while maintaining database integrity. This process deletes records from the ReIM application that meet certain business criteria (for example, records that are marked for deletion by the application user, records that linger in the system beyond certain number of days, and so on). The TablesPurge process does not generate any cascade relationships and/or SQL queries on the fly. The main features of the process are illustrated below:
The following arguments are applicable for the TablesPurgeBatch process:
reimpurge batch-alias-name PURGE [ALL|<table name>] [NOCOMMIT|COMMIT]
The first argument is batch alias name. The second argument is the word PURGE. The third argument is either ALL or a single table name. Table name can be any one of the following:
IM_DOC_GROUP_LIST
IM_DOC_GROUP_HEAD
IM_PARENT_INVOICE
IM_REASON_CODES
IM_PARTIALLY_MATCHED_RECEIPTS
IM_TOLERANCE_DEPT_AUDIT
IM_TOLERANCE_SUPP_AUDIT
IM_TOLERANCE_SUTRT_AUDIT
IM_TOLERANCE_SYS_AUDIT
ALL deletes data from all of the above tables. Finally, the fourth argument can be either NOCOMMIT or COMMIT. If there is no fourth argument, the default is NOCOMMIT.
Delete statements have been optimized by minimizing the usage of nested SELECT statements and by maximizing the 'table joins' in the WHERE clause. Any additions and/or modifications to the database require manual additions and/or modifications, respectively, to the existing SQL queries. All of the delete statements belonging to one cascade structure are added to a batch and executed at the end. It uses a single connection for each parent/children tree. Every cascade structure is a logical group.
Every time there is a change in the relationship between tables, this process must be modified to reflect that change. Table relationship changes occur when clients decide to make significant customizations to the application.
The developer must manually code the parent/child relationships between tables. For example, in order to delete records for the IM_DOC_HEAD table, records must be deleted from children tables in the following sequence of steps. The table sequence is not important within a single step.
Step 1
Delete from: IM_DETAIL_MATCH_INVC_HISTORYDelete from: IM_INVOICE_DETAIL_ALLOWANCEDelete from: IM_QTY_DISCREPANCY_ROLEDelete from: IM_QTY_DISCREPANCY_RECEIPT
Step 2
Delete from: IM_DOC_DETAIL_COMMENTSDelete from: IM_MANUAL_GROUP_INVOICESDelete from: IM_DOC_HEAD_COMMENTSDelete from: IM_INVOICE_DETAILDelete from: IM_DOC_HEAD_LOCKDelete from: IM_FINANCIALS_STAGEDelete from: IM_COST_DISCREPANCYDelete from: IM_RESOLUTION_ACTIONDelete from: IM_REVERSAL_RESOLUTION_ACTIONDelete from: IM_SUMMARY_MATCH_INVC_HISTORYDelete from: IM_QTY_DISCREPANCYDelete from: IM_DOC_DETAIL_REASON_CODESDelete from: IM_FINANCIALS_STAGE_ERRORDelete from: IM_DOC_NON_MERCH Delete from: IM_COMPLEX_DEAL_DETAIL_TAXDelete from: IM_DOC_DETAIL_RC_TAXDelete from: IM_DOC_NON_MERCH_TAXDelete from: IM_FIXED_DEAL_DETAIL_TAXDelete from: IM_INVOICE_DETAIL_ALLW_TAXDelete from: IM_INVOICE_DETAIL_TAXDelete from: IM_ITEM_TAX_AUDITDelete from: IM_ORDER_ITEM_TAX_AUDITDelete from: IM_TAX_DISCREPANCY Delete from: IM_DOC_TAX
Step 3
Delete from: IM_DOC_HEAD
Cascade relationships are wired in the TablesPurge.java.
Every time there is a change in the relationships among tables, the TablesPurge process has to be updated to accommodate these changes.
This process deletes the accounts maintained locally in the ReIM application. The batch retrieves the accounts in IM_VALID_ACCOUNTS table and validates the account against the integrated financial system. Accounts that are invalid in the financial system are deleted from IM_VALID_ACCOUNTS table.
|
Note: Run the batch whenever account information changes are communicated to ReIM. |
The discrepancy purging program deletes data from database tables while maintaining database integrity. This program deletes records from ReIM that have discrepancies of zero. Main features of the process are as follows:
The following arguments are applicable for the DiscrepancyPurgeBatch process:
reimdiscrepancypurge batch-alias-name PURGE [ALL|<table name>] [NOCOMMIT|COMMIT]
Where the first argument is batch alias name. The second argument is the word PURGE. The third argument is either ALL or a single table name. Table name can be any one of the following:
IM_COST_DISCREPANCY
IM_QTY_DISCREPANCY
ALL will delete data from all of the above-mentioned tables. Finally, the fourth argument can be either NOCOMMIT or COMMIT. If there is no fourth argument, the default is NOCOMMIT.
SQL Queries
The tables mentioned above are checked for merchandise invoices with cost and/or quantity discrepancies of zero. If they exist, the record is deleted from the table and the corresponding invoice detail line to will be updated to cost or qty matched. If the invoice line is now cost and qty matched the status of the line is set to matched and in return if all of the invoice lines are matched, the invoice itself is set to matched.
EDI invoice upload is a standardized file format specification designed for vendors to send invoicing information electronically. The EDI invoice upload batch process performs the following:
Reads each transaction within the file.
Runs a file format validation (verifying file descriptors and line numbers; ensuring that numeric fields are all numeric and that character fields are all characters; looking for the invalid ordering of record type-THEAD followed directly by another THEAD; and so on). Certain file formatting errors cause the process to terminate with a message indicating the problem. 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 rest of the data validation errors cause the invalid transaction to be written to a reject file where a user must correct the problems and re-run the file.
Validates the data against the ReIM system and the merchandising system (such as RMS).
Any errors found are recorded in an error log so that users can fix any transactions that were rejected to file.
Adds the data to the ReIM system. All valid transactions are written to the IM_DOC_xxx, IM_INVOICE_xxx, IM_PARENT_xxx tables.
This process must be run before the auto-match process.
All quantities are assumed to be in eaches when uploaded.
If the EDI invoice upload aborts without processing an entire file, the file needs to simply be rerun. When this action is completed, there will be multiple errors for the transactions that were successfully uploaded and the other transactions will be uploaded at that time as well. If the cause of the aborted process is software related, this fix may not solve the issue. Other steps may be required to ensure that the process completes its entire initial run.
The following tables are involved in the EDI Upload batch process.
IM_DOC_HEAD
IM_INVOICE_DETAIL
IM_INVOICE_DETAIL_TAX
IM_INVOICE_DETAIL _ALLOWANCE
IM_INVOICE_DETAIL_ALLW_TAX
IM_DOC_NON_MERCH
IM_DOC_NON_MERCH_TAX
IM_DOC_DETAIL_REASON_CODES
IM_DOC_DETAIL_RC_TAX
IM_PARENT_INVOICE
IM_PARENT_INVOICE_TAX
IM_PARENT_INVOICE_DETAIL
IM_PARENT_NON_MERCH
IM_PARENT_NON_MERCH_TAX
IM_EDI_REJECT_DOC_DETAIL
IM_EDI_REJECT_DOC_DETAIL_ALLOW
IM_EDI_RJT_DOC_DTL_ALLW_TAX
IM_EDI_REJECT_DOC_HEAD
IM_EDI_REJECT_DOC_TAX
IM_EDI_REJECT_DOC_NON_MERCH
IM_EDI_RJT_DOC_NON_MERCH_TAX
IM_DOC_TAX
IM_EDI_REJECT_DOC_DETAIL_TAX
IM_EDI_REJECT_DOC_TAX
IM_EDI_RJT_DOC_NON_MERCH_TAX
Auto-match is a system batch process that attempts to match invoices to receipts without manual intervention. Invoices that are in ready-for-match, unresolved, or multi-unresolved status are retrieved from the database to be run through the auto-match algorithm.
The three inputs into the auto-match process include the following:
Invoices
Receipts
Purchase orders
ReIM owns invoices, while receipts and purchase orders are owned by a merchandising system, such as RMS.
The processing consists of three levels: summary, detail, and header. Summary-level matching attempts to match all invoices to receipts at a summary level. Detail-level matching attempts to match all invoices (that do not match at a summary level) to receipts at a line item level. Header level matching attempts to validate TAX before continuing to attempt to match all invoices.
The auto-match process attempts to match the invoices to receipts to the best of its abilities. The process assign different statuses according to the level of matching achieved.
If an invoice arrives prior to a receipt (for a particular PO), the auto-match process attempts only to match invoice unit cost to PO unit cost.
When a complete match cannot be made, manual intervention is required through online processes.
The following algorithms comprise the auto-match process:
Cost pre-matching
This process identifies any cost discrepancies prior to the arrival of receipts. If no receipts exist for the PO location, the invoices are sent to the cost pre-matching algorithm. Cost pre-matching is where unit costs on the invoice are compared with unit costs on the purchase order at a line level. If a match can be obtained, the invoice remains in ready-for-match status and is retrieved again for matching once the receipt comes in. If no match can be obtained, a cost discrepancy is created and routed immediately.
Summary matching
Invoices are grouped with receipts based upon purchase order location. A match is attempted for all invoices and receipts for the PO location. The invoices' total extended costs are summed and compared with the receipts' total extended costs. Based on a supplier option, the invoices' total quantity is summed and compared with the receipts' summed total quantity. If a match is achieved, all invoices and receipts are set to matched status. Otherwise, one-to-one matching is attempted for the PO location.
One-to-one invoice matching
This processing attempts to match a single invoice to a single receipt for the applicable PO location. If all invoices and receipts are set to matched status, the next PO location is processed.
If a multi-unresolved scenario exists (where more than one invoice can be matched with one or more receipts), all un-matched invoices are given the multi-unresolved status and no further processing occurs for this PO location.
Detail matching
During detail matching processing, an attempt is made to match each line on the invoice to an unmatched receipt line for the same item. Both the unit cost and quantity are always compared at the line level. If both the cost and quantity match, the invoice line and receipt line are placed into matched status. If the cost fails or the quantity fails, the cost or quantity discrepancies are generated and routed.
Header matching
Invoices created without details are not able to have their TAX information validated at invoice creation. All header level only invoices are created with a status of Ready for Match. For TAX validation, this processing determines whether a header level only invoice that has been matched to a receipt should continue in the matching and posting process or whether it should be marked as having a TAX discrepancy and removed from the matching process.
Consider the following assumptions and scheduling notes.
Although not recommended, auto-match can be run during the day when there are users online interacting with the system.
Both the invoice unit cost and the unit cost of the PO must be expressed in the same currency. In order to compare the invoice unit costs with the PO's unit costs, auto-match does not engage in currency conversion. The system assumes that tolerance costs are always in the system's primary currency. If RMS is the applicable merchandising system, auto-match performs currency conversion if the currency on the order is different from the primary currency. RMS existing currency conversion engine is used to perform this conversion. If RMS is not being utilized, another currency conversion engine must be provided to support this functionality.
The quantities on the invoice must be expressed in the same unit of measure as the quantities on the receipt. Auto-match performs no unit of measure conversion.
The batch process runs after EDI upload (Invoice Matching) and Receipt upload (Merchandising system, such as RMS).
Supplier options. All suppliers must have options defined in order for their invoices to be processed by the system, and the terms defined for those suppliers have to be completely updated in RMS. In order to support the use of suppliers in ReIM, the ENABLED_FLAG (set to Y), START_DATE_ACTIVE and END_DATE_ACTIVE are the required entries in the TERMS_DETAIL table in RMS.
Auto-match automatically invokes the best terms calculation for invoices that it matches.
Auto-match automatically posts invoices that it matches.
The following diagram offers a high-level view of the processing logic utilized within the auto-match batch process.
The following tables are involved in the Invoice Auto-Match batch process.
IM_DOC_HEAD
IM_INVOICE_DETAIL
SHIPMENT(RMS)
SHIPSKU(RMS)
IM_PARTIALLY_MATCHED_RECEIPTS
ORDHEAD(RMS)
ORDSKU(RMS)
ORDLOC(RMS)
IM_TOLERANCE_DEPT
IM_TOLERANCE_SUPP
IM_TOLERANCE_SYSTEM
IM_COST_DISCREPANCY
IM_QTY_DISCREPANCY
IM_QTY_DISCREPANCY_RECEIPT
IM_QTY_DISCREPANCY_ROLE
IM_SUPPLIER_OPTIONS
IM_SYSTEM_OPTIONS
Credit Note Auto-Matching pairs credit note requests to corresponding credit notes sent by the supplier. The CreditNoteAutoMatchBatch attempts auto-matching of credit notes from suppliers, to credit note requests from the retailer without manual intervention. The batch also creates and resolves detail level discrepancies utilizing a predefined set of reason codes. These reason codes are defined within Invoice Matching through the System Options Maintenance screen. In addition, the batch utilizes a variety of configurable keys to allow for document groups to be matched in ways other than just distinct purchase order and location combinations.
When invoked, the batch creates a pool of matchable credit notes and credit note requests. The candidates are selected depending on which customizable fields are populated and a status of credit notes and credit note requests. For information, see "Credit Note Auto-Matching" in Chapter 4.
Once a pool of matchable documents is established, the batch proceeds to group the documents with respect to unique suppliers listed on the documents. Suppliers are the first layer of grouping, which facilitates further processing of each group in parallel using threads. For information, see "Credit Note AutoMatch Batch Multi-threading Options" in Chapter 2.
If threading is enabled for the batch, each supplier based group is processed in its own thread. Each supplier based group further divides the documents for that supplier into smaller document-key sets. These document-key sets are categorized by common attributes defined on the document itself. The attributes, also referred to as Configurable or Flexible 'Pool Keys' allow documents to be grouped in several combinations in addition to the distinct purchase order and location combination (which is the only combination possible in the current Invoice Auto-Matching framework).
Matching is not attempted for groups not containing both credit notes and credit note requests.
By default the CreditNoteAutoMatch process creates document-key sets based on the following key distinctions:
Credit Note Request ID
Original Invoice ID
PO/Location combination
To enable the use of all three keys, the customizable reference fields in the credit notes and credit note requests must be populated. For information, see "Credit Note Auto-Matching" in Chapter 4. The customizable Ref No. 3 field holds the credit note request ID, and Ref No. 4 field holds the original invoice ID. In case none of the customizable fields are populated with the required data, the PO/Location combination is the only key available to the CreditNoteAutoMatchBatch process.
Within each document-key set, matching is attempted using three algorithms: summary, one-to-one matching, and detail level matching. Summary-level matching attempts to match all credit notes with credit note requests at a summary level by comparing extended costs, or quantities within tolerance. One-to-one matching requires that extended costs or quantities of one distinct credit note match to only one distinct credit note request within tolerance. Line-level matching is only attempted if there is one unmatched credit note left. It attempts to match the line items of an unmatched credit note with line items of all unmatched credit note requests.
Below is the flow for attempting a match in a document-key set when no match is found:
Credit Note Request ID (configurable key)
Summary Matching (matching algorithm)
One to One Matching (matching algorithm)
Line-level Matching (matching algorithm)
Original Invoice ID (configurable key)
Summary Matching (matching algorithm)
One to One Matching (matching algorithm)
Line-level Matching (matching algorithm)
PO/Location (configurable key)
Summary Matching (matching algorithm)
One to One Matching (matching algorithm)
Line-level Matching (matching algorithm)
If Tax is enabled in the system, CreditNoteAutoMatchBatch only detects Tax discrepancies at the detail level. This means that when documents are being processed by the detail matching algorithm, a check is performed prior to matching, ensuring that the Tax codes and rates for each item on the credit note match those on the credit note request for the corresponding item. When a discrepancy is detected, processing for that document stops and detail matching is not performed for that document. In such a case, the Invoice Matching user will have to match and resolve the Tax discrepancy manually through the user interface.
Tolerances are handled in a manner similar to the Invoice auto-match batch process. The tolerances are first selected with respect to supplier, then the department, and lastly with respect to the system. For information, see "Credit Note Auto-Matching" in Chapter 4.
If a match is achieved, the information related to the matched document is migrated to the history tables, and all CreditNoteAutoMatch Batch related tables are purged for those documents. The migration process is enabled depending on the value of the creditnoteautomatchbatch.workspace.cleanup property in the reim.properties file. For information, see "Credit Note AutoMatch Workspace Cleanup Setting" in Chapter 2.
In case of an unsuccessful match manual intervention is required through online processes, and the match attempt related data for those documents is not cleaned up from the respective tables. See "Primary Tables Involved" in this section for more details on the tables involved.
The Credit NoteAutoMatch batch process includes the following algorithms.
Summary Matching
Credit notes and credit note requests in the document set are matched at the summary level by comparing extended costs. If the extended costs of the document set falls within tolerances, the documents are considered matched and flagged as such, processing continues with the next set. Note that since total extended costs are being compared, only total merchandise amounts will be factored into the actual matching calculations. If the documents in the set are from a supplier that requires quantity matching, quantity matching will be performed within tolerances as well.
One to One Matching
One to one matching is a variation of summary matching. It requires that one distinct credit note matches to only one distinct credit note request within tolerance for the document set. Extended costs are compared and quantities are also compared if the supplier option for quantity matching is enabled.
Detail Matching
For a given document set, when only one credit note remains unmatched and multiple credit note requests remain unmatched, the system will attempt to match line items from the credit note to the credit note request at the line level. If a match is not found, discrepancies are created and routed for resolution. When discrepancies are created as part of the detail (line-level) matching process, they are automatically resolved by the batch process. This resolution will take place by selecting the appropriate pre-defined reason code from the system options and resolving the discrepancy. During the reason code action rollup process, these newly created resolution actions will be rolled up to create the appropriate resolution documents. In case no applicable reason codes exist in the system for the discrepancy, the credit note will not be matched and processing will stop for the document set.
Consider the following assumptions and scheduling notes.
Both the credit note and credit note request unit cost must be expressed in the same currency. If the currency on the credit note and credit request is the same, but differs from the primary system currency, then an attempt will be made to perform currency conversion only if RMS is the applicable merchandising system.
The quantities on the credit note must be expressed in the same unit of measure as the quantities on the credit note requests. The batch performs no unit of measure conversion.
CreditNoteAutoMatch updates the status of qualified documents that have been matched.
The CreditNoteAutoMatch workspace is cleaned up depending on the related setting in the reim.properties file (refer to the Credit Note AutoMatch Workspace Cleanup Setting section in the reim.properties section).
The batch creates and resolves discrepancies by utilizing pre-defined reason codes. The Reason Code Rollup Batch must ensure that the respective documents are created.
The following are lookup tables that must be populated.
| Table Name | Contents |
|---|---|
|
IM_DOC_HEAD |
Credit notes and credit note requests with relevant information (such as supplier and status). |
|
IM_SUPPLIER_GROUP_MEMBERS |
Supplier group related information. |
|
IM_DOC_DETAIL_REASON_CODES |
The Item Detail record for credit notes. Data related to items must exist in this table to enable line-level matching. |
|
IM_TOLERANCE_SUPP |
Tolerance properties associated with a supplier. The data is required when performing matches within tolerances at the supplier level. |
|
IM_TOLERANCE_DEPT |
Tolerance properties associated with a department. The data is required when performing matches within tolerances at the department level. |
|
IM_TOLERANCE_SYSTEM |
Tolerance properties associated with a system. The data is required when performing matches within tolerances at the system level. |
|
IM_SYSTEM OPTIONS |
Properties associated with the Invoice Matching function, such as enabling TAX or enabling tolerances. |
The following are tables to which the process posts data.
| Table Name | Contents |
|---|---|
|
IM_MATCH_POOL_CONFIG |
Data for the matching process. This data determines which groupings the system utilizes when attempting to match and also dictates the order in which the groupings run. |
|
IM_MATCH_DOC |
The pool of documents that the batch process will attempt to match. |
|
IM_MATCH_POOL_TOLERANCES |
The calculated tolerances for each candidate document to be matched. |
|
IM_MATCH_POOL_RESULTS |
Cost and quantity total for a document set being matched and the variance between the documents being matched. Also included is the party the variance favors (retailer or supplier). |
|
IM_MATCH_POOL_ITEM |
Actual item detail unit cost and quantities to be used for matching. Details may be from IM_DOC_DETAIL_REASON_CODES or IM_INVOICE_DETAILS, depending on the type of match performed. |
|
IM_MATCH_QTY_VAR |
The quantity discrepancy calculated while attempting a match in a document set. |
|
IM_MATCH_COST_VAR |
The cost discrepancy calculated while attempting a match in a document set. |
The following tables are populated for compatibility with the existing Invoice Matching discrepancy related data model.
| Table Name | Contents |
|---|---|
|
IM_QTY_DISCREPANCY |
Quantity discrepancy records. |
|
IM_QTY_DISCREPANCY_ROLE |
The Associate roles with the access to generate discrepancies. |
|
IMP_QTY_DISCREPANCY_CNR |
Quantity discrepancies on the credit note get associated with participating credit note requests. |
|
IM_COST_DISCREPANCY |
Cost discrepancy records. |
|
IM_COST_DISCREPANCY_CNR |
Cost discrepancies on the credit note get associated with participating credit note requests. |
The following new history tables are populated upon the successful completion of the CreditNoteAutoMatch batch. The tables allow the retailer to track match history and locate aggregate data in the other match history tables based on the appropriate match and document type.
| Table Name | Contents |
|---|---|
|
IM_MATCH_DOC_HIST |
Upon successful completion of the matching process, documents contained in IM_MATCH_DOC are moved to this history table. |
|
IM_MATCH_POOL_ITEM_HIST |
History of the items that were on the credit note when matched. |
|
IM_MATCH_POOL_RESULTS_HIST |
Data from the MATCH_POOL_RESULTS table is moved to this table after a successful match. |
|
IM_MATCH_QTY_VAR_HIST |
|
|
IM_MATCH_COST_VAR_HIST |
History related to any quantity or cost variance detected during the match process. |
The following tables are populated for compatibility with the existing Invoice Matching history maintenance data model.
IM_CN_SUMMARY_MATCH_HIS
IM_CN_DETAIL_MATCH_HIS
Retailers track received goods that are not invoiced, and they must have the ability to 'write-off' these goods for financial tracking. Two types of processes can determine when these written-off goods will be written to financials: purged receipts from merchandising system, and close open receipts from invoice matching. Because receipts can be purged outside of the invoice matching dialogue, these purged receipts must be maintained until their unmatched amount has been accounted for. These receipts are tracked through STAGE_PURGED_SHIPMENTS and STAGE_PURGED_SHIPSKUS. Every purged shipment record that is not fully matched will have a record by item written to the stage tables. In addition, invoice matching has a system parameter (which can be overwritten at the supplier level) defining the maximum amount of time an open, non-fully matched receipt will be available for matching.
Every time the write-off process is run, each non-fully matched open receipt received date is compared with the current date minus the system parameter. If the received date is before this difference, then the receipt will be written-off and the invoice match status is closed.
The department/class of each receipt item must be identified to ensure accurate accounting. The form of the accounting distribution is as follows:
| Transaction Type | Sign | Value | Notes |
|---|---|---|---|
| Unmatched receipt | Debit | Value of unmatched items on receipt | |
| Receipt write-Off | Credit | Same as above | |
| Trade accounts payable | Credit | 0 | Written as a matter of form |
This account distribution mapping is set up through the account cross-reference screen.
|
Note: If IM_SUPPLIER_OPTIONS.CLOSE_OPEN_RECEIPT_MONTHS is not defined, the value is retrieved from IM_SYSTEM_OPTIONS.CLOSE_OPEN_RECEIPT_MONTHS. |
When setting up the Close Open Receipt Months in ReIM Supplier Options and/or System Options, the value should be less than or equal to RMS UNIT_OPTIONS.ORDER_HISTORY_MONTHS if the intention is to have invoice matching pick up receipts prior to purging.
Auto-match and any associated processing must be run prior to this batch processing.
Reason code actions are resolutions assigned at the discrepancy line level. A number of fixed actions are available to resolve a line item discrepancy; the specific results depend on the action.
The resolution posting process sweeps the IM_RESOLUTION_ACTION table and creates debit and credit memos as needed. Only a single debit or credit memo is created per invoice/discrepancy type, with line details from all related actions for the same discrepancy type.
This process does not delete these records when completed; rather, they are deleted after posting.
A separate, client-created batch process sweeps the receiver adjustment table. The action staging table is used during posting to post the reason code actions to the financial staging table.
To resolve a cost discrepancy, the user can select a Receiver Cost Adjustment action from the cost resolution screen. Similarly, to resolve a quantity discrepancy, the user can select a Receiver Unit Adjustment action from the quantity resolution screen. The actions are written to the IM_RESOLUTION_ACTION table in an unrolled status with the amount of adjustment. The IM_INVOICE_DETAIL table also receives a flag that signifies a pending adjustment for the invoice line.
At the same time, the actions are written to the IM_RECEIVER_COST_ADJUST and IM_RECEIVER_QTY_ADJUST tables to indicate the expected receiver adjustment amount on the RMS (or equivalent merchandising system) side. In sum, these two tables serve as the staging tables for the RMS (or equivalent merchandising system) process to actually perform the adjustment.
For a receiver cost adjustment, IM_RECEIVER_COST_ADJUST holds the order unit cost for the item after the adjustment. For a receiver unit adjustment, IM_RECEIVER_UNIT_ADJUST holds the received quantity for the item on the shipment after the adjustment.
The process compares the unit cost and/or quantity received for the item on the shipment with the expected unit cost and/or quantity on the IM_RECEIVER_COST_ADJUST and/or IM_RECEIVER_UNIT_ADJUST tables. If a match exits, the receiver cost and/or unit adjustment has occurred in RMS (or the equivalent merchandising system). As a result, the process sets the pending adjustment flag on IM_INVOICE_DETAIL table to false for the invoice line. The reason code actions are only rolled up for an invoice if no invoice lines on the invoice have any pending adjustments.
Because ReIM cannot control when and how the receiver adjustments are happening on the RMS side (or the equivalent merchandising system), records written to the IM_RECEIVER_COST_ADJUST and IM_RECEIVER_UNIT_ADJUST tables are considered final.
As a result, when the user resolves a cost or quantity discrepancy, the receiver adjustment must fully resolve a discrepancy before the user leaves the screen, and there should be no re-route actions involved. On the RMS side, the amount of adjustment must be exactly the same as expected.
The IM_PARTIALLY_MATCHED_RECEIPTS table holds the amount of a receipt item that has been matched during invoice matching. The quantity received on the SHIPSKU table subtracts the quantity matched on the IM_PARTIALLY_MATCHED_RECEIPT table, giving the available to match quantity for the receipt item. Auto-match, summary matching, detail matching and quantity discrepancy resolution processes all keep track of the matched quantity bucket to determine how much of the receipt item has already been matched and how much of the receipt item remains available to be matched. In the case of a Receiver Unit Adjustment, the IM_PARTIALLY_MATCHED_RECEIPTS table is updated to reserve the entire remaining unmatched bucket for the receipt item. This logic prevents the adjusted receipt quantity from being used for any other matching or quantity resolutions.
The memo staging table sweep must occur before the posting batch process, or a delay of one day results before posting can occur.
The following diagram offers a high-level view of the processing logic utilized within the reason code action rollup batch process.
The following tables are involved in the Reason Code Action Rollup batch process.
IM_DOC_HEAD
IM_INVOICE_DETAIL
IM_PARTIALLY_MATCHED_RECEIPTS
IM_RESOLUTION_ACTION
IM_RECEIVER_COST_ADJUST
IM_RECEIVER_UNIT_ADJUST
When a disputed credit memo is first created as a reversal to a debit memo, cost, or quantity discrepancies are generated for each line on the credit memo, and the original debit memo reason codes are associated with the new credit memo detail lines.
As the user takes actions to resolve the discrepancy online, a record is written to the IM_REVERSAL_RESOLUTION_ACTION table for each resolution action taken. The only actions allowed to resolve the discrepancy are Deny Dispute or Approve Credit in Disputed Status. However, the user can choose multiple reason codes associated with Deny or Approve actions to resolve the disputed line. Also, the user can either resolve the disputed line completely, or partially resolve it. Upon complete resolution of a disputed line, the cost or quantity discrepancy is deleted from the system.
The disputed credit memo action rollup process checks the records on the IM_REVERSAL_RESOLUTION_ACTION table and rolls up the credit memo detail lines by document/item/reason code. The rollup occurs only if all lines on a disputed credit memo have been completely resolved (that is, no cost or quantity discrepancy records remain for the credit memo).
After the rollup, a new set of detail lines associated with the resolution reason codes replace the original set of detail lines associated with the debit reason codes on the IM_DOC_DETAIL_REASON_CODES table. The new credit memo lines are in Approved or Denied status depending on the resolution action. The credit memo header status is updated to Approved status. The lines that are approved are rolled up to calculate the header level total cost and total quantity. Non-merchandise costs can be associated with a credit memo that is created as a debit memo reversal, but no resolution actions can be taken on non-merchandise costs. Non-merchandise costs should be included in the credit memo's total cost.
The disputed credit memo action rollup must occur before resolution posting and after receiver adjustment.
The following tables are used for the debit memo reversal, resolution, and rollup processes:
IM_DOC_HEAD. This table holds the document header information.
IM_DOC_DETAIL_REASON_CODES. This table holds the document detail information by item/reason code. Before resolution rollup, this table holds the document detail information based on the original debit reason codes. After resolution rollup, this table holds the document detail information based on the reason codes used to resolve the disputed credit memo lines.
IM_REVERSAL_RESOLUTION_ACTION. This table holds the resolution actions the user takes to approve or deny the disputed credit memo line.
IM_COST_DISCREPANCY. This table holds the disputed credit memo lines for a debit memo cost reversal.
IM_QTY_DISCREPANCY. This table holds the disputed credit memo lines for a debit memo quantity reversal.
IM_QTY_DISCREPANCY_ROLE. This table holds the routing information for a credit memo quantity.
For each invoice, the batch process writes applicable financial accounting transactions to either of the following tables:
The Financials staging table, IM_FINANCIALS_STAGE.
The AP staging tables, IM_AP_STAGE_HEADER and IM_AP_STAGE_DETAIL, or the IM_FINANCIALS_STAGE, depending on the transaction type (if the RMS System-Options table: FINANCIAL_AP = O).
The processing occurs after discrepancies for documents have been resolved by resolution documents. Once all of the resolution documents for a matched invoice are built, and all of the RCA/RUA external processing has been confirmed, the process inserts financial accounting transactions to the financials staging table, to represent the resolution and consequent posting of the invoice. The process also inserts financial accounting transactions for the approved documents that are being handled.
Once all of the transactions have been written, the process switches the status of the current invoices/documents to Posted and moves on to the next invoice/document.
If a segment look-up fails, the failed record is written to a financials error table.
Before posting can occur, the following information must be set up:
Segment definitions in the system.properties.
GL account segments on the GL Options screen.
All the accounts using the GL Cross Reference screen.
Country
Location
Dept
Class
If dynamic segments are defined, the values for the segments must be defined in the applicable tables, IM_DYNAMIC_SEGMENT_DEPT_CLASS or IM_DYNAMIC_SEGMENT_LOC.
The following tables are involved in the Financial Posting batch process.
The IM_DOC_HEAD table contains the matched and approved documents.
The IM_DOC_NON_MERCH table contains the non-merchandise costs for invoices.
IM_GL_OPTIONS. Order of segments and dynamic segments defined.
IM_GL_CROSS_REF. Account values defined for account types and account codes.
IM_DYNAMIC_SEGMENT_DEPT_CLASS. Accounts defined for each department/class combination.
IM_DYNAMIC_SEGMENT_LOC. Accounts defined for each location/company combination.
|
Note: The table to which the process posts data is either IM_FINANCIALS_STAGE or IM_AP_STAGE_HEAD |
Transaction code
Debit/credit indicator
Invoice ID
Invoice date
Supplier
Purchase order (if available)
Shipment/receipt (only if unmatched receipt is being written)
Currency
Amount
Best terms ID
Terms date
Pre-paid indicator
Comments
Create user ID
Create date-time
Segments that determine the mapping account in the external financial system (as defined in the IM_GL_CROSS_REF table).
Sequence Number: Automatically generated line numbers 1, 2, 3, and so on; incremented for each detail record per DOC ID for identification purposes.
Doc_id: Similar to IM_FINANCIALS_STAGE.
The Invoice Type Lookup Code for merchandise invoices and credit memos (where IM_DOC_HEAD.TYPE is MRCHI, CRDMEC or CRDMEQ) is STANDARD. For positive non-merchandise invoices (where IM_DOC_HEAD.TYPE is NMRCHI) the Invoice Type Lookup Code also is Standard. For negative non-merchandise invoices and all other documents, the Invoice Lookup Code is CREDIT.
invoice_number: The concatenated data is as follows:
chars 1-34: the first 34 characters from the EXT DOC ID
char 35: a hyphen
chars 36-50: the DOC ID
Vendor: Same as for current im staging table.
Oracle_site_id:
The loc from this transaction to read new RMS Location/Org Unit data to find the Org Unit.
The Org Unit to read new RMS Supplier Addr/Org Unit/Site ID data to find Oracle Site ID.
The Org Unit of the Location from this transaction should match the Org Unit of the Site ID. Otherwise, this field value will be null.
Currency Code: Valued if this is a foreign currency invoice, otherwise null.
Exchange Rate: If exchange rate is valued, this should be the literal, USER; otherwise blank.
Exchange Rate Type
Document Date: Same as in current im staging table.
Amount: The TOTAL amount including tax.
Best Terms Date: Same as in current im staging table.
Segment1: Same as in current IM financials staging table.
Segment2: Same as in current IM financials staging table.
Segment3: Same as in current IM financials staging table.
Segment 4: Same as in current IM financials staging table.
Segment 5: Same as in current IM financials staging table.
Segment 6: Same as in current IM financials staging table.
Segment 7: Same as in current IM financials staging table.
Segment 8: Same as in current IM financials staging table.
Segment 9: Same as in current IM financials staging table.
Segment 10: Same as in current IM financials staging table.
Create Date: Same as in current IM financials staging table.
Best Terms ID: Same as in current IM financials staging table.
Doc_id
Sequence number: Automatically generated line numbers 1, 2, 3, and so on; incremented for each detail record per DOC ID; for identification purpose.
Transaction Code
Line Type Lookup Code: This value varies. The rules are:
If the tran-code is UNR or VWT or REASON or CRN then this value is ITEM.
If this is a generated tax line, then this value will be TAX.
Recent modifications have been made to the ReIM posting process to better support integration with EBS with respect to VAT requirements. Previous to the modifications, ReIM would post Invoices to the staging tables which passed information to Accounts Payables in a manner where in certain scenarios, the VAT lines could not be easily associated with corresponding Merchandise lines on the invoice. The details of the association between Items and VAT Codes/Rates is available in ReIM, however it could be lost during the posting process with Accounts Payable.
These new modifications provided a more detailed breakdown of information for items by VAT Code and the association with the appropriate VAT lines. Previous to the modifications, ReIM made it's postings for financial integration by rolling up the Items in the posting to a GL Account Segment level. So, all RMS items that are mapped to the same GL Account Codes in ReIM will be combined into a single posting line. Along with this, TAX lines for the item lines are also posted, one for each VAT Rate that was applicable to the items included in the ITEM line. This did not provide the ability to easily determine the VAT basis that was used to determine the VAT line once posted to the financial system.
The modification changes the level at which the Item lines are posted so that the VAT Rate of the items provides a further breakdown of the Item line posting. The posting now makes the same roll up to the common GL Accounts Segment level and then provides a further breakdown to the VAT code level. TAX line are then posted with each Item line for the corresponding VAT rate of the items. No Item line would have more than 1 TAX line associated with it.
If none of the above, then this value will be MISCELLANEOUS.
Amount
Vat Code: Same as in current IM staging table except for generated tax lines, where the amount for this line should be the amount from the taxable line times the tax rate
Segment1: For regular lines, same as in current staging table; for generated tax line, use values from source line.
Segment2: (see rules for segment 1)
Segment3: (see rules for segment 1)
Segment4: (see rules for segment 1)
Segment5: (see rules for segment 1)
Segment6: (see rules for segment 1)
Segment7: (see rules for segment 1)
Segment8: (see rules for segment 1)
Segment9: (see rules for segment 1)
Segment10: (see rules for segment 1)
Create Date: Same as in current IM staging table.
The EDI invoice download process retrieves debit memos, credit note requests, and credit memos in 'approved' or 'posted' status from the resolution posting process and creates a flat file. The client converts the flat file into an EDI format and sends it through the EDI invoice download transaction set to the respective vendors.
Consider the following assumptions and scheduling notes.
All data is valid in the IM_DOC_HEAD tables. ReIM does not validate details.
Auto-match must run prior to the EDI invoice download.
The EDI invoice download batch process reads from the following tables:
IM_DOC_HEAD
IM_DOC_DETAIL_REASON_CODES
IM_DOC_NON_MERCH
IM_DOC_DETAIL_COMMENTS
If the EDI invoice download aborts while processing, an incomplete file is generated. To generate a complete file, the process simply needs to be rerun and allowed to fully process. If the cause of the aborted process is software related, this action might not solve the issue; other steps may be required to ensure that the process completes its entire initial run.
The Complex Deal Upload batch process reads data from header and detail complex deals staging tables in RMS.
For each combination of deal ID and deal detail ID on the RMS staging tables, the batch process creates a credit memo, a debit memo, or a credit note request, depending upon an indicator on the staging tables.
The batch process also copies most of the data from the RMS staging tables into one ReIM detail table (IM_COMPLEX_DEAL_DETAIL). This data is later referenced during the posting process for the created documents.
The RMS staging header and detail must be purged nightly after the upload has run.
|
Note: For descriptions of RMS tables, see the Oracle Retail Merchandising System Data Model. |
STAGE_COMPLEX_DEAL_HEAD (RMS table)
STAGE_COMPLEX_DEAL_DETAIL (RMS table)
IM_DOC_HEAD. This table holds general information for documents of all types. Documents include merchandise invoices, non-merchandise invoices, consignment invoices, credit notes, credit note requests, credit memos, and debit memos. Documents remain on this table for SYSTEM_OPTIONS.DOC_HISTORY_MONTHS after they are posted to the ledger.
IM_DOC_DETAIL_REASON_CODES. This table contains quantity/unit cost adjustments for a given document/item/reason code.
IM_DOC_TAX. This table associates the document with its value added tax information.
IM_COMPLEX_DEAL_DETAIL. This table holds the details of the complex deal stored in ReIM. It is used during complex deal detail posting.
IM_COMPLEX_DEAL_DETAIL_TAX. This table holds the tax information of the complex deal.
The Complex Deals upload batch is run in multi-threaded mode as follows:
reimcomplexdealupload user/password BlockSize PartitionNo
The BlockSize is used to decide how many deal IDs to process in every thread. It should be greater than 1.
For example, if there are 15 deals to be processed in the staging tables and BlockSize input argument is provided as 3, then there will be 5 threads to process 3 deals each simultaneously.
A total of 3 deals records are processed in each of the 5 threads.
The PartitionNo is used by huge data block that are in the units of millions (for example, 4 million).
The batch is used by the query to pick all the records and it retrieves ALL the deal numbers to be processed by the batch.
For example, the input command line arguments:
reimfixeddealupload user/password 3 1
The RMS system generates Debit Memos (or Credit Note Requests) for Fixed or Complex deals and pass them through to ReIM via custom upload batch programs. The RMS system includes a system option called 'Credit Memo Level' which will control the level at which the Debit Memo (or Credit Note Request) is generated.
The valid values for the Credit Memo Level option are:
L – Location
T – Transfer Entity
B – Set of Books
D – Deal/Component
Therefore, if a retailer wishes to generate a separate Debit Memo (or Credit Note Request) for each location on a deal, they would set the Credit Memo Level option to 'L', and RMS would send a separate transaction for each location on the deal. If this level of detail is not needed, the retailer could set the option to 'D' and only a single document would be sent to ReIM for each deal/component. Note that if the deal/component level is selected, ReIM still internally tracks the locations on each deal and will use this information to credit the correct locations on the deal when making the accounting entries.
The Fixed Deal Upload batch process reads data from header and detail fixed deals staging tables in RMS.
For each deal ID on the RMS staging tables, the batch process creates a credit memo, a debit memo, or a credit note request, depending upon an indicator on the staging tables.
The batch process also copies most of the data from the RMS staging tables into one ReIM detail table (IM_FIXED_DEAL_DETAIL). This data is later referenced during the posting process for the created documents.
For non-merchandise fixed deals that are not associated with an RMS location, the org unit has been added to the RMS staging table. During the Fixed Deal upload process, the set of books ID associated with this org unit is used to access a new table (FIXED_DEAL_SOB_LOC_DEFAULT) to get the location to use for the deal document in IM_DOC_HEAD. Then, the resolution posting job populates the financial staging tables with the set of books ID associated with the location just like it does with all other documents.
The RMS staging header and detail must be purged nightly after the upload has run.
|
Note: For descriptions of RMS tables, see the Oracle Retail Merchandising System Data Model. |
STAGE_FIXED_DEAL_HEAD (RMS table)
STAGE_FIXED_DEAL_DETAIL (RMS table)
IM_DOC_HEAD. This table holds general information for documents of all types. Documents include merchandise invoices, non-merchandise invoices, consignment invoices, credit notes, credit note requests, credit memos, and debit memos. Documents remain on this table for SYSTEM_OPTIONS.DOC_HISTORY_MONTHS after they are posted to the ledger.
IM_DOC_NON_MERCH. This table holds various user-defined non-merchandise costs associated with an invoice. Non merchandise costs can be associated with merchandise invoice if the IM_SUPPLIER_OPTIONS.MIX_MERCH_NON_MERCH_IND for the vendor is 'Y'. If the MIX_MERCH_NON_MERCH_IND for the vendor is N, non merchandise expenses can only be on non merchandise invoice documents.
IM_DOC_TAX. This table associates the document with its value added tax information.
IM_FIXED_DEAL_DETAIL. This table holds the details of the fixed deals in the ReIM system. It will be used during fixed deal detail posting.
IM_FIXED_DEAL_DETAIL_TAX. This table holds the tax information of the fixed deal.
The Fixed Deals upload batch is run in multi-threaded mode as follows:
reimfixeddealupload user/password BlockSize PartitionNo
The BlockSize is used to decide how many deal IDs to process in every thread. It should be greater than 1.
For example, if there are 15 deals to be processed in the staging tables and BlockSize input argument is provided as 3, then there will be 5 threads to process 3 deals each simultaneously.
A total of 3 deals records are processed in each of the 5 threads.
The PartitionNo is used by huge data block that are in the units of millions (for example, 4 million).
The batch is used by the query to pick all the records and it retrieves ALL the deal numbers to be processed by the batch.
For example, the input command line arguments:
reimfixeddealupload user/password 3 1
The RMS system generates Debit Memos (or Credit Note Requests) for Fixed or Complex deals and pass them through to ReIM via custom upload batch programs. The RMS system includes a system option called 'Credit Memo Level' which will control the level at which the Debit Memo (or Credit Note Request) is generated.
The valid values for the Credit Memo Level option are:
L – Location
T – Transfer Entity
B – Set of Books
D – Deal/Component
Therefore, if a retailer wishes to generate a separate Debit Memo (or Credit Note Request) for each location on a deal, they would set the Credit Memo Level option to 'L', and RMS would send a separate transaction for each location on the deal. If this level of detail is not needed, the retailer could set the option to 'D' and only a single document would be sent to ReIM for each deal/component. Note that if the deal/component level is selected, ReIM still internally tracks the locations on each deal and will use this information to credit the correct locations on the deal when making the accounting entries.
