| Oracle® Retail Invoice Matching Operations Guide Release 19.2.000 F36992-03 |
|
 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
Invoice Matching batch processes are run as Java applications. Batch processes engage in a shared processing with the UI in a client server model.
Services retrieve the data on which the batch processes work to complete their tasks. 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 Invoice Matching 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. However, the volume can be managed using the inclusion-exclusion configuration feature (see section Batch Configuration)
Invoice Matching batches should run only in the batch window when no users exist in the system. This requirement is related to locking consideration (see locking section for more information).
They run in a client server model. Client side code is a plain java which basically responsible for the validation of input parameters, retrieval of user credentials for the given alias name.
In case there is no valid user/password pair is found for the given alias name, the client program complains the same and terminates. On the successful identification of credentials, client program makes a call to execute<<Batch>> method of corresponding EJB on the application server
For programs in the financial posting batch cycle, we have implemented a new configuration feature. The volume of data processed by each batch execution can be controlled through a table configuration. While executing a particular batch program (say AUTOMATCH), we may restrict the batch candidates to either a particular supplier or exclude a particular supplier to manage the batch load. At any point of time, a max of one exclude or one include should be configured per a particular day.
A new table IM_BATCH_CONFIG is included in the data model which is used for configuring the exclusion or inclusion of suppliers per batch for a particular day. An indicator called 'PROCESSED_IND' is in the table that tells if the inclusion/ exclusion are already processed or not. Its initial value is N.
Table 5-1 IM_BATCH_CONFIG Parameters
| Parameter | Description |
|---|---|
|
BATCH_NAME |
Used for configuring the batch name. |
|
SUPPLIER |
The supplier that the batch should exclude/include. |
|
SCOPE |
Exclude (E)/Include (I) |
|
PROCESSED |
Initial value N. Once the batch completes, program sets it to Y so that it would not considered next execution. |
|
PROCESSED_DATE |
This attribute is added for the auditing purpose. It also cater as a constraint to make sure that only one Exclude or Include are configured per supplier per day. |
The query that picks the candidates for the each batch is modified to join with IM_BATCH_CONFIG table to first exclude the documents/data that are of SUPPLIERS configured to exclude in the particular batch program with a PROCESSED value N. Then the inclusion logic is applied, if any. If no exclusions/inclusions have been configured for a particular batch with PROCESSED value N, then the batch will process all the eligible data.
The batches that have this capability are
Automatch batch
Financial Posting batch
Resolution Action Rollup batch
Complex Deal Upload
Fixed Deal Upload
Invoice Matching EDI-related batch processes are file based. For example, they either input a flat file into the system (EDI Injector) 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 Injector and the EDI invoice download batch processes are described later in this chapter. For the EDI Injector batch, the input file/folder and the rejection file/folder should be on the same physical machine as the application server is running on. This is a requirement from the file system access permission stand point. Similarly, the EDI download batch process can save the edi output file only to a folder that is on the same machine as the server is running on.
Other batch processes within Invoice Matching 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
Reason Code action rollup
The third type of batch process within Invoice Matching 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, Invoice Matching 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.
The fourth type of batch process within Invoice Matching extracts data from merchandising system staging tables, create documents with the data, and write the data to Invoice Matching tables. The batch processes that follow this processing pattern include the following:
Complex deal upload
Fixed deal upload
The following table describes Invoice Matching batch processes. The table order reflects the dependencies that exist among the Invoice Matching batch processes but does not include any dependencies that exist between Invoice Matching and the merchandising system with which it interacts.
Table 5-2 Batch Names
| Batch Name | Class (oracle.retail.apps.reim.batch.client) |
|---|---|
|
Tables purge |
TablesPurgeBatchClient |
|
Account purge |
AccountWorkspacePurgeBatchClient |
|
EDI Injector |
EdiInjectorBatchClient |
|
Auto-match |
AutoMatchBatchClient |
|
Receipt write-off |
ReceiptWriteOffBatchClient |
|
Reason code action rollup |
ReasonCodeActionRollupBatchClient |
|
Financial posting |
FinancialPostingBatchClient |
|
EDI Invoice download |
EdiDownloadBatchClient |
|
Complex deal upload |
ComplexDealUploadBatchClient |
|
Fixed deal upload |
FixedDealUploadBatchClient |
|
Financial Posting workspace Purge |
FinancialPostingWorkspacePurgeBatchClient |
The following table summarizes Invoice Matching batch processes and includes both a description of each batch process's business functionality and its batch dependencies:
Table 5-3 Invoice Matching Batch Processes
| Batch Processes | Details | Batch Dependencies |
|---|---|---|
|
Batch purge |
This process deletes data from database tables while maintaining database integrity. This process deletes records from the Invoice Matching 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 Invoice Matching application. |
|
|
EDI Injector |
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. |
|
|
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. 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 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 Merchandising (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. |
|
|
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. Documents which should be sent to the Financial A/P system are sent to the AP staging tables: IM_AP_STAGE_HEAD, and IM_AP_STAGE_DETAIL. Transactions which should be sent to the Financial G/L system are sent to the IM_FINANCIALS_STAGE table |
|
|
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 Merchandising staging tables, creates credit memos, debit memos, and credit note requests out of the data, and stores the supporting deal data on a Invoice Matching table for later use during posting. |
|
|
Fixed deal upload |
This module reads data from Merchandising staging tables, creates credit memos, debit memos, and credit note requests out of those, and stores the supporting deal data on a Invoice Matching table for later use during posting. |
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 Invoice Matching batch processes utilize:
SUCCESS = 0
FAILED_INIT = 1
FAILED_PROCESS = 2
The client side log file location is determined by the retailer through the logj4.properties file. The errors that occur on the server side program will be written to the server log which can be configured by weblogic administrator. If an error occurs that causes a batch process to suddenly come to a complete halt, the system writes to the configured log file. See "Chapter 2, "Backend System Administration and Configuration" for more information.
The following batch processes shown below have multi-threading capabilities. The configuration related to some of the multi threaded batches can be configured in REIM System options. 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 Invoice Matching 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 injector (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 batch client framework. This framework is responsible for 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 EdiInjectorBatch process:
reimediinjector batch-alias-name input-file/input-path input-file/input-path output-file/output-path
The batch client programs require the application libraries (JAR files) to be on the classpath in order to execute successfully.
The batch purging process deletes data from database tables while maintaining database integrity. This process deletes records from the Invoice Matching 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_OPERATIONALS | PURGE_WORKSPACES | PURGE_WORKSPACES_AND_OPERATIONALS]
The first argument is batch alias name. The second argument is the purge action. PURGE_OPERATIONALS would make the batch purge data from operational tables alone, PURGE_WORKSPACES would make the batch purge data from all workspace tables, PURGE_WORKSPACES_AND_OPERATIONALS would make the batch purge data from both operational and workspace tables. By default, the purge action is committed.
Data from the operational tables will be purged based on the document history days system option. The tables are purged in the reverse order of their relationship to each other, history tables first and then the details and then the main or primary table.
The workspace tables used for display or internal calculation purposes for Search, document maintenance, Matching or Posting processes would be truncated. Indexes on all affected tables would be rebuilt after truncate. It is recommended that the workspace tables be truncated frequently, as they need to be well maintained to ensure optimal performance of the front end.
Data from the workspace tables are truncated followed by the deletion of data from the operational tables.
This process deletes the accounts maintained locally in the Invoice Matching 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 Invoice Matching. |
The EDI Injector 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 can cause invalid EDI transaction to be fixable, where the data can be corrected through online process. The rest of the data validation errors cause the invalid transaction to be written to a set of reject files where a user must correct the problems and re-run the files.
Validates the data against the Invoice Matching system and Merchandising.
Any errors found are recorded in to the error table (IM_INJECT_DOC_ERROR) so that users can audit and fix any transactions that were rejected.
Adds the data to the Invoice Matching system. All valid transactions are written to the IM_DOC_xxx, IM_INVOICE_xxxtables.
The size of the Logical Unit of work for each chunk needs to be defined in Invoice Matching's system options.
The following arguments are applicable for the EDI Injector Batch process:
reimediinjector batch-alias-name input-file/input-path output-file/output-path
This process can be run ad-hoc but in general it should be run before the auto-match process.
If the EDI Injector Batch aborts without processing an entire file, the file can 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 diagram offers a high-level view of the processing logic utilized within the EDI Injector Batch process.
The following tables are involved in the EDI Injector Batch process:
Operational Data Tables
IM_DOC_HEAD
IM_DOC_TAX
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
Injector Workspace Tables
IM_INJECT_DOC_DETAIL
IM_INJECT_DOC_DETAIL_ALLOWANCE
IM_INJECT_DOC_DETAIL_ALLOW_TAX
IM_INJECT_DOC_DETAIL_TAX
IM_INJECT_DOC_ERROR
IM_INJECT_DOC_HEADER
IM_INJECT_DOC_NON_MERCH
IM_INJECT_DOC_NON_MERCH_TAX
IM_INJECT_DOC_RECORD
IM_INJECT_DOC_RULE
IM_INJECT_DOC_TAX
IM_INJECT_STATUS
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 inputs into the auto-match process include the following:
Invoices
Receipts
Purchase orders
Match Strategy
Tolerance
Invoice Matching owns invoices, Match Strategy, and Tolerance while receipts and purchase orders are owned by a merchandising system, such as Merchandising.
The Match Strategy rules feature allows retailers to build and maintain match strategies which specifically define the types of matches which should be attempted and the order in which they should be tried during the auto-match process. The match strategies can be defined at the system, supplier group or supplier level. The creation of a Supplier Group is tightly integrated to the logic for selecting documents to be processed by the match engine. If a Supplier Group is created, all the documents for all the suppliers in the group are considered by the match engine together. If a match strategy is defined at the Supplier Group level, then it is used to determine what match attempts to apply against the documents in the supplier group. If a match strategy is not defined at the supplier group level, then the system default match strategy is used to determine which match attempts are used to attempt to match documents for the supplier group. If a match strategy was set up for one of the suppliers for a supplier group, it is ignored by the match engine. If a supplier is not part of a supplier group, then all the documents for that supplier are considered by the match engine together. If a match strategy is defined at the Supplier level, then it is used to determine what match attempts to apply against the documents for that supplier. If a match strategy is not defined at the supplier level, then the system default match strategy is used to determine which match attempts should be used to attempt to match documents for the supplier.
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 if Cost Pre-matching is opted while running the auto-match batch.
When a complete match cannot be made, manual intervention is required through online processes.
The size of the Logical Unit of work for each chunk needs to be defined in Invoice Matching's system options.
The following arguments are applicable for the Invoice Auto-Match Batch process:
reimautomatch batch-alias-name
The following algorithms comprise the auto-match process:
Cost pre-matching
The Cost Pre-Matching routine is optional but if it is run, it runs as the first step of the Auto-match batch. When the Cost Pre-Matching routine is run, it is run against all suppliers. The routine is only executed if no receipt exists for the order on the invoice. If it finds differences in the cost on the order and the cost on the invoice which are outside of the tolerance level, it generates a cost discrepancy. 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.
Summary matching
The Auto match batch attempts various types of Summary Matches based on the Match Strategy associated with the supplier or supplier group. Summary Matching involves looking at the total document values (cost and optionally quantity) without considering the specific items on each document.
Summary Match All-to-All
The all-to-all match attempts to match all invoice documents to all receipt documents in the match pool. Used in combination with the Match Strategy table, all-to-all matching provides the user:
The option to choose whether or not to run the all-to-all match.
The option to choose the order in which the all-to-all match is attempted. The user could decide to execute other match attempts before the all-to-all match.
The option to decide how to group the invoices and receipts together to attempt matching by specifying the match key.
Summary Match One-to-Many
The one-to-many match attempts to match one invoice document to one or more receipt documents. There are two options when performing a one-to-many summary match:
Regular Match
Regular Match attempts to match the invoices and receipts in the pool as one to one matches. If an invoice could match to two or more receipts within tolerance, then the match fails. Similarly, if two or more receipts could match to a single receipt (within tolerance), then the match fails and both invoices are put in multi-unresolved status. If Regular Match fails because the invoice could be matched to multiple receipts or if it failed because multiple invoices could be matched to one receipt, the invoice is flagged as multi-unresolved. If a Regular Match fails for any other reason, the invoice is flagged as an 'Unresolved' match.
Best Match
The Best Match setting applies additional logic to select better matches when multiple receipts or receipt combinations can be matched to a single invoice. The best match process creates all combinations of one invoice to one or more receipts and selects the best match. The best match logic selects the receipt or combination of receipts that provides the lowest absolute variance. If two potential matches to the invoice have the same absolute variance but one is an overbill and one is an underbill, the underbill takes precedence. If the two potential matches have identical variances, then the invoice quantity matching will be used as an additional criteria. The match with the smallest absolute quantity variance, taken as the best match. If the absolute quantity variances are the same, then no best match can be determined, and the invoice is left as unmatched.
SKU Compliance on Summary Match
The SKU compliance feature can be used to match invoice items and values to what was actually received. SKU compliance is only calculated if all invoices in a match have details. Therefore, if any of the invoices in the match is a header only invoice, the SKU compliance is skipped. SKU compliance checks for how many of the items on the invoice(s) are on the receipt(s) and how many of the items on the receipt(s) are on the invoice(s). There is a percent calculation for each of these ratios, and both ratios must pass the SKU compliance percent for the match to be accepted.
Tax Validation on Header only Matches
Invoice Matching uses a routine in the auto-matching program to perform a tax validation for header only invoices. The tax validation is executed when a header only invoice matches (either perfectly or within tolerance). The tax validation compares the taxes on the invoice to the taxes generated by the items from the receipt. In addition, the tax validation:
ensures that all tax codes used on the invoice(s) are also used on the receipts in the match, and that the tax rates are exactly the same.
ensures that all tax codes on the receipts used in the match are also on the invoice(s) and that the rates match.
If the match passes these two criteria, the invoice (and receipts) can be considered matched. If the validation fails, the invoice(s) are put into tax discrepant status.
Detail matching
In auto-matching, matching can be performed for entire invoices or broken down to the line level. Detail matching is performed by item.
Eligibility for Detail Matching
In order to be eligible for detail matching, an invoice or receipt must meet the following conditions:
Item lines must be present on the invoice:
The invoice or receipt must not be part of a manual group.
Regular versus Best Match
Regular detail matching compares the invoice item with the matching receipt item from all receipts in the pool. When regular matching is only done within a PO, the unit cost on all the receipt items is the same. If the match key being used allows the user to cross PO's, a constraint is included to require all receipt costs on an item to be the same. If receipt costs are different for the same item, detail matching is not allowed for the item.
The Best Match Strategy for detail matching does two separate routines to attempt to match items within the match pool:
If the cost of the item on all invoices in the pool is the same, Best Match attempts to match all invoices to all receipts in the pool for that item (an all-to-all match). If they are within tolerance for both cost and quantity in the all-to-all step, then the item is matched on all invoices and on all receipts within the pool. If either the cost or quantity match fails, then nothing is flagged as matched.
For the item, look at the item on each invoice in the pool individually and compare it to the sum of all receipts in the pool and select the best match. The criteria for determining the best match in this scenario is as follows:
Calculate the unit cost variance, and if it is out of tolerance the invoice is rejected from best match consideration.
Calculate the quantity variance and if it is out of tolerance the invoice is rejected from best match consideration.
Calculate the variance on the extended cost between the invoice item and the receipt item(s). This variance is compared against all matches which pass the previous steps. Compare the absolute variance for all the matches which are eligible for best match consideration. Take the match with the least absolute variance as the best match. If two matches have the same absolute variance but one is an overbill and one is an underbill, select the underbill as the best match. If the variances are identical, then a best match is not possible, so the match is skipped.
If the Best Match attempt is unsuccessful, it means that the Regular Match would also have not been successful. However, if the routing date has passed you should attempt regular matching including the auto-resolution process and the generation of discrepancies.
The Regular Match attempts to match the invoice item with the receipt items from all receipts in the match key (receipt unit cost must all match the unit cost of the item on the PO(/loc) for the invoice being matched).
Generating Discrepancies
During regular detail matching, the auto-matching process generates discrepancies for cost and quantity discrepancies which are outside of tolerance.
The Tolerance table includes an Auto Resolution column which is used to determine the variance percent (or amount) allowed to complete an automatic resolution. The Auto Resolution column means that there are three types of discrepancies:
Discrepancies which are within the 'variance within tolerance' (VWT) setting.
Discrepancies which have a variance which can be automatically resolved.
Discrepancies which have a variance which is too large to be resolved automatically. These variances generate a discrepancy and are sent to the Discrepancy Review list.
Automated Discrepancy Resolution
When a discrepancy has been identified as one which can be automatically resolved (based on comparing the variance with the applicable tolerance from the tolerance table), the system looks up the appropriate resolution action on the reason code table using the code assigned on the tolerance table row associated with the discrepancy. The resolution action is applied to resolve the discrepancy. If this is the last item on the invoice to be resolved then the whole invoice is flagged as matched. If this was the last item for the receipt to be resolved, then the receipt is also flagged as matched.
Consider the following assumptions and scheduling notes.
Auto-match cannot 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. Match Keys which involve multiple currencies or vat regions or set of books will be removed from the matching process.
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(Merchandising)
SHIPSKU(Merchandising)
IM_PARTIALLY_MATCHED_RECEIPTS
ORDHEAD(Merchandising)
ORDSKU(Merchandising)
ORDLOC(Merchandising)
IM_TOLERANCE_LEVEL_MAP
IM_SUPPLIER_OPTIONS
IM_SYSTEM_OPTIONS
IM_MATCH_INVC_WS
IM_MATCH_INVC_DETL_WS
IM_MATCH_RCPT_WS
IM_MATCH_RCPT_DETL_WS
IM_MATCH_GROUP_HEAD_WS
IM_MATCH_GROUP_INVC_WS
IM_MATCH_GROUP_RCPT_WS
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 the Oracle Retail Invoice Matching User Guide.
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.
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:
Deal ID
Deal Component ID
Credit Note Request ID
Original Invoice ID
PO/Location combination
To enable the use of all five keys, the reference fields in the credit notes and credit note requests must be populated. For information, see the Oracle Retail Invoice Matching User Guide. The reference credit note request ID field holds the credit note request ID, reference invoice ID field holds the original invoice ID, deal ID field holds the deal ID and deal detail ID field holds the deal component ID. In case none of these 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 for each of the document-key set:
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 with respect to the system. For information, see the Oracle Retail Invoice Matching User Guide.
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.
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 following arguments are applicable for the Credit Note Auto-Match Batch process:
reimcreditnoteautomatch batch-alias-name
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.
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 5-4 Primary Tables Involved
| 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_LEVEL_MAP |
Tolerance properties associated with supplier. The data is required when performing matches within tolerances. |
|
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 5-5 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 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 5-6 History Table
| 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:
Table 5-7
| 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. |
The following arguments are applicable for the Receipt Write-Off Batch process:
reimreceiptwriteoff batch-alias-name
When setting up the Close Open Receipt Months in Invoice Matching Supplier Options and/or System Options, the value should be less than or equal to Merchandising 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.
The action staging table is used during posting to post the reason code actions to the financial staging table.
The following arguments are applicable for the Reason Code Action Rollup Batch process:
reimrollup batch-alias-name
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
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.
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.
The following arguments are applicable for the Financial Posting Batch process:
reimposting batch-alias-name <extract_indicator(Y/N)> [<extract_directory>]
Before posting can occur, the following information must be set up:
Segment definitions in the GL options.
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, void, and approved documents.
The IM_DOC_NON_MERCH table contains the non-merchandise costs for invoices.
IM_GL_OPTIONS. Order of segments, business attributes, 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 Merchandising Location/Org Unit data to find the Org Unit.
The Org Unit to read new Merchandising 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 Invoice Matching posting process to better support integration with EBS with respect to Tax requirements. Previous to the modifications, Invoice Matching would post Invoices to the staging tables which passed information to Accounts Payables in a manner where in certain scenarios, the Tax lines could not be easily associated with corresponding Merchandise lines on the invoice. The details of the association between Items and Tax Codes/Rates is available in Invoice Matching, 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 Tax Code and the association with the appropriate Tax lines. Previous to the modifications, Invoice Matching made it's postings for financial integration by rolling up the Items in the posting to a GL Account Segment level. So, all Merchandising items that are mapped to the same GL Account Codes in Invoice Matching will be combined into a single posting line. Along with this, TAX lines for the item lines are also posted, one for each Tax Rate that was applicable to the items included in the ITEM line. This did not provide the ability to easily determine the Tax basis that was used to determine the Tax line once posted to the financial system.
The modification changes the level at which the Item lines are posted so that the Tax 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 Tax code level. TAX line are then posted with each Item line for the corresponding Tax 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.
To facilitate the integration process with Financial Systems, a file based integration solution was added to the Financial Posting Batch. An optional parameter <extract_location> can be provided to the batch. If specified, the Posting process will extract staged data into export files. There will be 3 files produced, one for each staging table:
IM_AP_STAGE_HEAD
IM_AP_STAGE_DETAIL
IM_FINANCIALS_STAGE
These files will be staged in the <reim_batch_dir>/data/posting/out directory where they are then added to a single zip file named REIM_POSTING_<current_timestamp>.zip with the following structure:
REIM_POSTING_<current_timestamp>.zip
IM_AP_STAGE_HEAD_<current_timestamp>.csv
IM_AP_STAGE_DETAIL_<current_timestamp>.csv
IM_FINANCIALS_STAGE_<current_timestamp>.csv
|
Note: <reim_batch_dir> is the directory supplied to the batch process in the batch script via the parameter -DBATCHDIR=<reim_batch_dir>. The <reim_batch_dir> is usually the batch directory for invoice matching inside the retail directory as configured by the installer. Also note that <current_timestamp> is the time that the respective file was generated in the format "yyyyMMddHHmmss". |
After the REIM_POSTING_<current_timestamp>.zip file is created in the staging directory, the zip file will be copied into the posting extract archive directory for backup:
<reim_batch_dir>/data/posting/archive/REIM_POSTING_<current_timestamp>.zip
Once archived, the zip file in the staging directory will be duplicated, creating a REIM_POSTING_<current_timestamp>.zip.complete file which is needed for file transfer.
Finally, the two zip files in the staging directory will be moved to the extract location:
<extract_location>/REIM_POSTING_<current_timestamp>.zip <extract_location>/REIM_POSTING_<current_timestamp>.zip.complete
|
Note:
|
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.
The following arguments are applicable for the EDI Invoice Download Batch process:
reimediinvdownload batch-alias-name
Consider the following assumptions and scheduling notes.
All data is valid in the IM_DOC_HEAD tables. Invoice Matching 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 Merchandising.
For each combination of deal ID and deal detail ID on the Merchandising 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 Merchandising staging tables into one Invoice Matching detail table (IM_COMPLEX_DEAL_DETAIL). This data is later referenced during the posting process for the created documents.
The following arguments are applicable for the Complex Deal Upload Batch process:
reimcomplexdealupload batch-alias-name block-size
|
Note: For descriptions of Merchandising tables, see the Oracle Retail Merchandising System Data Model. |
STAGE_COMPLEX_DEAL_HEAD (Merchandising table)
STAGE_COMPLEX_DEAL_DETAIL (Merchandising 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 Invoice Matching. 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 Merchandising system generates Debit Memos (or Credit Note Requests) for Fixed or Complex deals and pass them through to Invoice Matching via custom upload batch programs. The Merchandising 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 Merchandising 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 Invoice Matching for each deal/component. Note that if the deal/component level is selected, Invoice Matching 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 Merchandising.
For each deal ID on the Merchandising 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 Merchandising staging tables into one Invoice Matching 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 Merchandising location, the org unit has been added to the Merchandising 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 following arguments are applicable for the Fixed Deal Upload Batch process:
reimfixeddealupload batch-alias-name block-size
|
Note: For descriptions of Merchandising tables, see the Oracle Retail Merchandising System Data Model. |
STAGE_FIXED_DEAL_HEAD (Merchandising table)
STAGE_FIXED_DEAL_DETAIL (Merchandising 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 Invoice Matching 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 Merchandising system generates Debit Memos (or Credit Note Requests) for Fixed or Complex deals and pass them through to Invoice Matching via custom upload batch programs. The Merchandising 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 Merchandising 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 Invoice Matching for each deal/component. Note that if the deal/component level is selected, Invoice Matching 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.
