Integration with the Sales Audit Module of the Oracle Retail Merchandising Foundation Cloud Service
Overview: Use the integration with Sales Audit module of the Oracle Retail Merchandising Foundation Cloud Service (Sales Audit module) to export information on sales and returns.
Types of information sent for transactions: The following information is included:
• transaction header
• customer
• items
• discounts
• tax
• payment
• tenders
• transaction tail
The file also includes transaction open and close records, and file header and trail records.
Tracking shipments and credits: If you enable the Sales Audit module sales audit integration in Order Management System Cloud Service, the system writes records in the INT_RESALOG table in the database, and updates the table with records as shipments and order credits bill throughout the day.
Packaging the DAT file into a ZIP file: On a daily basis, you use a periodic process to add file header and trailer records to “wrap” the transaction information, and convert the contents of the INT_RESALOG table into a DAT file that is ready for Sales Audit module to retrieve and process. This file is named RTLOG_6_20190721145902950.DAT, where 6 is the company number or the store number, and 20190721145902950 is the date and time stamp. The DAT file is then packaged into a ZIP file of the same name, for example, RTLOG_6_20190721145902950.ZIP.
In this chapter:
• Data Flow from Order Management System Cloud Service to Sales Audit Module
• Configuration for the Sales Audit Module Integration
- Configuration within Order Management System Cloud Service
- Mapping Configuration between Order Management System Cloud Service and the Sales Audit Module
• Considerations for the Sales Audit module Integration
Data Flow from Order Management System Cloud Service to Sales Audit Module
If your company is configured for Sales Audit module integration, processing takes place as follows:
• Sale or credit invoices are created through the day and are processed by the billing async job in Background Job Control (MBJC).
• Outbound Interface Transaction trigger records with a File setting of IHD are created based on the invoices. You can review these trigger records in Working with Outbound Interface Transactions (WOIT).
• The INVOIC_OUT process in Working with Integration Layer Processes (IJCT) works through the IHD trigger records to collect the required information for each invoice, and creates records in the INT_RESALOG database table for transactions in each company that is configured for the Sales Audit module integration.
• When the RTLOG periodic function runs, it:
- Uses the records in the INT_RESALOG table to create a DAT file that contains the transaction data from the temporary file for the company specified for the function, as well as the header, close store, and footer records. The DAT file name is RTLOG_1234_20190621140400561.DAT, where:
* 1234 is the Parameter specified for the periodic function. This needs to be the store number assigned by the Sales Audit module. If no store number is specified as the parameter, the company number is used.
* 20190621140400561 is the date and time.
- Creates a compressed ZIP file containing the DAT file, using the same naming convention as described above the for DAT file and places the ZIP file in the CWDIRECTCP_FTP_FOLDER.
The Sales Audit module then needs to retrieve the ZIP file from the CWDIRECTCP_FTP_FOLDER.
Configuration for the Sales Audit Module Integration
Configuration within Order Management System Cloud Service
The configuration required in Order Management System Cloud Service to support the integration with Sales Audit module includes the following.
System Control Values
Set the following system control values:
• ReSA RTLOG Format (M39): Must be set to RTLOG or RTLOGQ.
• Consolidated Invoice (B49): Needs to be unselected. Otherwise, if this system control value is selected, it is possible to generate multiple transaction heads with the transaction numbers, as individual lines on the order are billed. The items that bill first could then accumulate and repeat on each subsequent transaction: for example, if 3 items ship separately, invoice 1 contains item 1; invoice 2 contains items 1 and 2; invoice 3 contains invoice 1, 2, and 3.
• Create Generic Invoice Download Trigger Records (I17): Must be selected in order to create the IHD trigger records for processing.
• Use Async Start Date for Billing Transactions (E95): Oracle recommends leaving this system control value unselected.
• Item for Non-Merchandise Amounts (L39): Used as the non-merchandise item (NMITEM) in the DAT file for Sales Audit module.
• Default Warehouse (A04): Used as the return warehouse for a credit invoice that processes a return for a non-inventory item.
Property
The process uses the CWDIRECTCP_FTP_FOLDER, defined through Working with Admin Properties (CPRP), to place the ZIP file for the Sales Audit module.
Periodic Function and Process
Use Working with Periodic Functions (WPER) to set up the RTLOG periodic function. If the function needs to run in more than one company, mapping to more than one store in the Sales Audit module, you can name the function RTLOGN, where N is a unique number. When setting up the function:
• Set the Program name to PFR0201
• Set the Parameter to the store number assigned by the Sales Audit module, but if not passed, uses company number
Use Working with Periodic Processes (WPPR) to assign the periodic function to a periodic process. The process should run at the end of day, because records accumulate in temporary file until the function runs.
Miscellaneous Setup
Do not set Trigger Rules or XML Inclusion rules for the INVOIC_OUT job in Working with Integration Layer Processes (IJCT).
Set the Outbound version for INVOIC_OUT to 3.0 in Working with Integration Layer Processes (IJCT).
Mapping Configuration between Order Management System Cloud Service and the Sales Audit Module
In addition to the Configuration within Order Management System Cloud Service, described above, you need to map the following information from the Sales Audit module:
• Store number: Specify as the Parameter for the RTLOG periodic function. This store number is used as part of the DAT file name, as described above, and also populates the Location Number in the FHEAD record. If no Parameter is specified for the periodic function, the company number is used.
• Return Reason Codes: The return reason codes used must map to SARR codes defined in the Sales Audit module.
• Return Warehouse Codes: Any warehouse codes that might be passed as return warehouses must map to Physical Warehouses defined in the Sales Audit module.
• Pay Types: Each pay type sent must map to a Pay Type in the Sales Audit module.
• Items: The Reference # for the item must map to an item in the Sales Audit module. Items are ordinarily imported from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) into the Sales Audit module and Order Management System Cloud Service, and the Reference # is set to the same value as the item code. The Reference # should not be changed. See Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Pricing Cloud Service (RPCS) Integration for background.
Considerations for the Sales Audit module Integration
The RTLOG periodic function needs to run on the same server as the CWDIRECTCP_FTP_FOLDER, and where you run INVOIC_OUT process.
Cross-channel orders are not excluded from the RTLOG file for the Sales Audit module, and as a result can be submitted by multiple systems that integrate with the Sales Audit module.
Example: Xstore submits an order to Order Broker, and the order is fulfilled through assignment to Order Management System Cloud Service; in this case, both Xstore and Order Management System Cloud Service submit the order to the Sales Audit module. Similarly, if the Use OROB for Fulfillment Assignment (M31) system control value is selected, Order Management System Cloud Service sends invoices for both the originating order and fulfilling order, or there could be multiple transactions sent for same order if a line splits in Order Broker.
The Cross Channel Orders to Exclude in Sales Feed (L35) does not affect the selection of orders to include in the export file.
It is not necessary to create queues for the INVOIC_OUT job in Working with Integration Layer Processes (IJCT) if the trigger records are required only for the Sales Audit module integration. In this case, you should set the ReSA RTLOG Format (M39) to RTLOG. If you set the ReSA RTLOG Format (M39) to RTLOGQ rather than RTLOG, errors will be logged if no queues have been created.
The invoice amount passed in the RTLOG file may not be the same as the paid amount in the case of an underpayment, overpayment, or canceled return.
The file type record descriptors for each record type included in the RTLog file, and a brief summary of their contents:
• FHEAD: The header for the file, indicating basic information such as the originating system, the store number, and the file creation date and time. Added through the RTLOG periodic function. One header per file.
• THEAD: This File Type Descriptor contains the header for each transaction, indicating the date and time generated, the invoice (transaction) number, and whether the transaction was a shipment (SALE) or refund (RETURN).
The first THEAD record in the file is an OPEN transaction following the initial FHEAD, and the last THEAD record is a CLOSE transaction at the end of the transactions. Unlike the OPEN transaction, the CLOSE transaction is created through the RTLOG periodic function.
• TCUST: Information about the customer, including customer number and name and address.
• TITEM: Information about each item sold or returned, including item number, quantity, and pricing, as well as the order number and invoice number.
• IDSIC: Discount line information for sales transactions.
• TTAX: Tax amounts.
• TPYMT: Payment amounts for sales, exclusive of tax.
• TTEND: Tender information for each transaction.
• TTAIL: Indicates the total number of lines in the transaction.
• FTAIL: The total number of lines in the file. The last entry in the file. Added through the RTLOG periodic function.
The following table describes the fields that are mapped into the RTLog file, or that are hard-coded.
Note: Fields that are left blank are not included in the following table.
Field |
Data Type and Length |
Value Passed |
Description/ Comments |
|
This File Type Descriptor identifies a single record at the beginning of the file. Created by the RTLog periodic function, and not found in the temporary file. |
|||
File Line Identifier |
Number (10) |
0000000001 |
Identifies the line number in the file. Zero-padded. Set to 0000000001 for the FHEAD. |
File Type Definition |
Character (4) |
RTLG |
Hard-coded. |
File Create Date |
Character (14) |
System date and time |
The date and time when the file was created. Example: 20190621140400, where 2019 is the year, 06 is the month, 21 is the date, and 140400 is the time. |
Business Date |
Character (8) |
System date |
The date when the file was created. Example: 20190621, where 2019 is the year, 06 is the month, and 21 is the date. |
Location Number |
Character (10) |
Location ID |
The location ID assigned in the Sales Audit module. From the Parameter defined for the RTLOG periodic function. If no Parameter was defined, this is the company number. |
RTLOG Originating System |
Character (3) |
OMS |
Hard-coded. |
|
The first record in the file with a File Type Descriptor of THEAD is an OPEN transaction, and follows the initial FHEAD record in the DAT file created by the RTLOG periodic function. |
|||
File Line Identifier |
Number (10) |
Next sequential number in the file. |
Identifies the line number in the file. Ordinarily set to 0000000002, since this line follows the FHEAD record. Zero-padded. |
Register |
Character (5) |
01 |
Hard-coded. |
Transaction Date |
Character (14) |
System date and time |
The date and time when the record was created. Example: 20190621140400, where 2019 is the year, 06 is the month, 21 is the date, and 140400 is the time. |
Transaction Number |
Number (10) |
Next transaction number in the file. |
Zero-padded. Ordinarily set to 0000000002 for the OPEN transaction type. |
Transaction Type |
Character (6) |
OPEN |
Hard-coded. |
Sub-transaction Type |
Character (6) |
OSTORE |
Hard-coded. |
THEAD: SALE or RETURN transaction Records with this File Type Descriptor contains the header for each transaction, indicating the date and time generated, the invoice (transaction) number, and whether the transaction was a shipment (SALE) or refund (RETURN). The first THEAD record in the file is an OPEN transaction following the initial FHEAD, and the last THEAD record is a CLOSE transaction at the end of the transactions. |
|||
File Line Identifier |
Number (10) |
Next sequential number |
Identifies the line number in the file. Zero-padded. |
Register |
Character (5) |
01 |
Hard-coded. |
Transaction Date |
Character (14) |
System date and time |
The date and time when the INVOICE_OUT job processed the transaction record. Example: 20190621140400, where 2019 is the year, 06 is the month, 21 is the date, and 140400 is the time. |
Transaction Number |
Number (10) |
Invoice number |
The unique number identifying the invoice in Order Management System Cloud Service. |
Cashier |
Character (10) |
Salesman number |
The unique number identifying the salesman, if any, associated with the order. |
Salesperson |
Character (10) |
Salesman number |
The unique number identifying the salesman, if any, associated with the order. |
Transaction Type |
Character (6) |
SALE or RETURN |
Set to SALE if the invoice type is I; otherwise, set to RETURN if the invoice type is C. |
Value Sign |
Character (1) |
P |
Set to P for both sales and credit invoices. |
Rounded Amount Sign |
Character (1) |
P |
Set to P for both sales and credit invoices. |
Rounded Off Sign |
Character (1) |
P |
Set to P for both sales and credit invoices. |
Transaction Processing System |
Character (3) |
POS or OMS |
Passes POS: • For a debit invoice, the delivery type for the Order Ship To is P (store pickup) or R (retail pickup), or • For a debit invoice, the delivery type is S (ship-for-pickup) and the Payment at POS for Ship for Pickup Orders (L60) SCV is set to Y, or • For a credit invoice, the return disposition value matches the Return Disposition Code to Exclude in ORCE Sales Feed (M22), and Suppress Refund = Y. Otherwise, passes OMS. |
TCUST File Type Records with this File Type Descriptor contains information about the sold-to customer, including customer number and name and address. TCUST records follow THEAD records with File Type Descriptors of SALE or RETURN. The information in this record type is primarily from the Sold To Customer record. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
Customer ID |
Character (16) |
Sold-to customer number. |
The sold-to customer on the order. |
Customer Type ID |
Character (6) |
CUSTID |
Hard-coded. |
Customer Name |
Character (120) |
Customer first name, last name, and company. |
Values are concatenated, with spaces between each. |
Address 1 |
Character (240) |
First address line |
All address and phone number information is for the sold-to customer. |
Address 2 |
Character (240) |
Second address line |
Blank if no second address line exists. Apartment number is not passed. |
City |
Character (120) |
City |
|
State |
Character (12) |
State or province code |
If blank, spaces are passed. |
Zip Code |
Character (30) |
Zip or postal code |
If blank, spaces are passed. |
Country |
Character (3) |
Country code |
Two positions. |
Home Phone |
Character (20) |
Day phone |
|
Work Phone |
Character (20) |
Evening phone |
|
Character (100) |
Email address |
The sold-to customer’s primary email address. |
|
TITEM File Type Records with this File Type Descriptor contains information about each item sold or returned, including item number, quantity, and pricing, as well as the order number and invoice number. The information for this record type is primarily from the Invoice Detail table. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
Item Status |
Character (6) |
ORD or C |
Set to ORD if the invoice type is I; otherwise, set to R if the invoice type is C. |
Item Type |
Character (6) |
ITEM or NMITEM |
Set to NMITEM for the Item for Non-Merchandise Amounts (L39); otherwise, set to ITEM. |
Item Number Type |
Character (6) |
ITEM |
Hard-coded. |
Item |
Character (25) |
Item code |
The item’s Reference # (retail reference number) from the SKU table. The Reference # is automatically populated with the item number through Importing Enterprise Foundation Data through Omnichannel Cloud Data Service (OCDS). If for any reason the Reference # is blank, the base item code is passed instead. The item code passed does not include a SKU. SKUs are not supported in the Sales Audit module. Note: Each item must map to an item in the Sales Audit module. |
Non-Merchandise Item |
Character (25) |
Non-merchandise item code |
From the Item for Non-Merchandise Amounts (L39) system control value. This must be a valid item code in the Sales Audit module. |
Quantity Sign |
Character (1) |
P or N |
Set to P for a sale invoice; otherwise, set to N for a credit invoice. |
Quantity |
Number (12) |
Shipped or returned unit quantity |
Zero-padded with a 4-position implied decimal; for example, a quantity of 2 is passed as 000000020000. Absolute value. Quantity of 1 is passed for the Item for Non-Merchandise Amounts. |
Selling Unit of Measure |
Character (4) |
EA |
Hard-coded. |
Unit Retail |
Number (20) |
Actual price |
Zero-padded with a 4-position implied decimal; for example, a price of 1.50 is passed as 00000000000000015000. Absolute value. When the record is for the Item for Non-Merchandise Amounts, then this field is the net total of the additional charge amount, freight amount, and the handling charge amount. |
Override Reason |
Character (6) |
OMS |
Hard-coded. |
Original Unit Retail |
Number (20) |
Pre-discount price |
The original offer price. See Overriding the Item/SKU Offer Price for a discussion of how the offer price for an Order Detail record is determined when offers are not set up, and a price override reason code is used to set the price for the order line. |
Taxable Indicator |
Character (1) |
Y or N |
Set to Y, unless: • The item is tax exempt; see Working with Item Tax Exemptions (WITX), or • This is the Item for Non-Merchandise Amounts. |
Item_swiped_ind |
Character (1) |
N |
Hard-coded. |
Return Reason Code |
Character (6) |
Return reason code |
The return reason code used for a return of an order line. See Establishing Return Reason Codes (WRTR) for background. Note: The return reason codes used MUST map to SARR codes defined in the Sales Audit module. |
Salesperson |
Character (10) |
Salesman number |
The unique number identifying the salesman, if any, associated with the order. |
Drop Ship Ind |
Character (1) |
Y or N |
Set to Y for a drop ship item on a sale transaction; otherwise, set to N. Set to N for a return. |
Uom_qty |
Number (12) |
Shipped quantity |
The shipped quantity on the invoice detail. Zero-padded with a 4-position implied decimal; for example, a quantity of 2 is passed as 000000020000. Absolute value. Quantity of 1 passed for the Item for Non-Merchandise Amounts. |
Catchweight_ind |
Character (1) |
N |
Hard-coded. |
Selling item |
Character (25) |
Item code |
The base item code. Does not include a SKU. SKUs are not supported in the Sales Audit module. |
Customer Order Number |
Character (48) |
Order number |
The Order Management System Cloud Service order number. Does not include the ship-to suffix. |
Customer Order Date |
Character (14) |
Order date |
The order date from the Order Header. YYYYMMDD format. |
Fulfillment Order Number |
Character (48) |
Invoice number |
Included only for sales transactions. Not included for returns. |
No Inventory Return |
Character (1) |
Y or N |
Used only for credit invoice records. Set to Y when inventory was not updated for the transaction creating the invoice detail record (there is no Item Transaction History record, as indicated in Display Inventory Transaction History (DITH)). Otherwise, set to N if inventory was updated, or if this is a non-merchandise item (NMITEM). |
Sales Type |
Character (1) |
E |
Hard-coded. |
Return Warehouse |
Character (10) |
Warehouse code |
Used only for return records that are not for the non-merchandise item (NMITEM): • If inventory updated: If No Inventory Return is set to N and this is a return that affected inventory (that is, not for the NMITEM), the Return Warehouse is the warehouse code from the inventory transaction history record. • If inventory not updated: If No Inventory Return is set to Y and this is not for the NMITEM, this is the warehouse defined in the Default Warehouse (A04) system control value. If no Default Warehouse is defined, this is hard-coded to 1. Note: Any warehouse codes that might be passed MUST map to Physical Warehouse defined in the Sales Audit module. |
Return Disposition |
Character (10) |
COR |
Hard-coded. Sent only when No Inventory Return is set to N, or the record is for the NMITEM. |
Location Type |
Character (2) |
Location type identifier |
• ST for a store pickup, retail pickup, or ship-for-pickup order. • WH for a delivery order. • Blank for any other order type. Not passed for returns. |
Location Number |
Character (10) |
Code identifying fulfilling or pickup location |
• The pickup location code from the Order Broker record for a store pickup, retail pickup, or ship-for-pickup order. • The fulfilling location code from the Order Broker record, for a brokered backorder, if specified. • Otherwise, the OCDS RMS Location Identifier (M52). Not passed for returns. |
IDISC Records with this File Type Descriptor contain discount line information for sales transactions when the selling price is lower than the offer price. These records are not created for returns. Also, if the offer price is lower than the selling price, such as when the offer price is zero, no discount record is created. This record follows the TITEM record for the discounted item. The information for this record type is primarily from the Invoice Detail table. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
RMFCS Promotion Number |
Character (6) |
2000 |
Hard-coded. |
Discount Type |
Character (6) |
ITEM |
Hard-coded. |
Quantity Sign |
Character (1) |
P |
Hard-coded. A quantity sign of N is not used, since only sales transactions create these records. |
Quantity |
Number (12) |
Shipped quantity |
The shipped quantity on the invoice detail. Zero-padded with a 4-position implied decimal; for example, a quantity of 2 is passed as 000000020000. |
Unit Discount Amount |
Number (20) |
Discount amount divided by shipped quantity |
The discount amount shipped quantity on the invoice detail. Zero-padded with a 4-position implied decimal; for example, a unit discount amount of 2.5 is passed as 000000025000. |
Uom_qty |
Number (12) |
Shipped quantity |
The shipped quantity on the invoice detail. Zero-padded with a 4-position implied decimal; for example, a quantity of 2 is passed as 000000020000. |
Catchweight_ind |
Character (1) |
N |
Hard-coded. |
TTAX Records with this File Type Descriptor contain tax amounts. Note: VAT amounts are not passed in this record type, since they are included in item prices. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
Tax Code |
Character (6) |
TOTAX |
Hard-coded. |
Tax Sign |
Character (1) |
P or N |
Set to P for a sale invoice; otherwise, set to N for a credit invoice. |
Tax Amount |
Number (20) |
Tax amount |
The total tax amount for the invoice, including any GST or PST. Zero-padded with a 4-position implied decimal; for example, a tax amount of 2.38 is passed as 00000000000000023800. Absolute value. |
TPYMT Records with this File Type Descriptor contain payment amounts for sales, exclusive of tax, although VAT is not subtracted if the order is subject to VAT. These records are not created for return or credit transactions. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
Payment Sign |
Character (1) |
P |
Hard-coded. A payment sign of N is not used, since only sales transactions create these records. |
Payment Amount |
Number (20) |
Deposit amount minus tax |
The deposit amount for the invoice payment method, exclusive of tax. Zero-padded with a 4-position implied decimal; for example, a payment amount of 37.60 is passed as 00000000000000376000. |
TTEND Records with this File Type Descriptor contain tender information for each transaction. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
Tender Type Group |
Character (6) |
CHECK or CCARD |
Set to CHECK if the payment method is a check pay category; otherwise, set to CCARD if the payment method is a credit card. |
Tender Type ID |
Number (6) |
Pay type code |
The pay type code, as set up in Work with Pay Types (WPAY). Note: Each pay type must map to a Pay Type in the Sales Audit module. |
Tender Sign |
Character (1) |
P or N |
Set to P for a sale invoice; otherwise, set to N for a credit invoice. |
Tender Amount |
Number (20) |
Deposit amount |
The deposit amount for the invoice payment method, including tax. Zero-padded with a 4-position implied decimal; for example, a payment amount of 39.98 is passed as 00000000000000399800. |
TTAIL File Type A Type Record Descriptor for the transaction trailer record following each transaction, including an OPEN or CLOSE transaction. |
|||
File Line Identifier |
Number (10) |
Line number |
Identifies the line number in the file. Zero-padded. |
Transaction Record Counter |
Number (10) |
Number of records in the transaction |
Set to 0000000000 for an OPEN or CLOSE transaction. Zero-padded. |
THEAD: CLOSE transaction The last record in the file with a File Type Descriptor of THEAD is a CLOSE transaction. This record is followed by the TTAIL and FTAIL records. Created by the RTLog periodic function, and not found in the temporary file. The CLOSE THEAD record is followed by a TTAIL record, and then the FTAIL record. |
|||
File Line Identifier |
Number (10) |
Next sequential number in the file. |
Identifies the line number in the file. Zero-padded. |
Register |
Character (5) |
01 |
Hard-coded. |
Transaction Date |
Character (14) |
Date and time |
The date and time when the RTLOG function created the DAT file. |
Transaction Number |
Number (10) |
Next transaction number in the file. |
Identifies the transaction number in the file. Zero-padded. |
Transaction Type |
Character (6) |
CLOSE |
Hard-coded. |
Sub-transaction Type |
Character (6) |
CSTORE |
Hard-coded. |
FTAIL The total number of lines in the file. The last entry in the file. Added through the RTLOG periodic function. |
|||
File Line Identifier |
Number (10) |
Next sequential number in the file |
Identifies the line number in the file. Zero-padded. |
File Record Counter |
Number (10) |
Next sequential number in the file |
Identifies the transaction number in the file. Zero-padded. |
