Troubleshooting Oracle Hosted Sites Not Posting to Reporting & Analytics

1. Introduction

The purpose of this document is to outline the procedure for troubleshooting Oracle Hosted Sites that are not sending sales data to Reporting & Analytics (R&A).
This process should be used only as a last resort—after all standard troubleshooting methods have been attempted and before logging a case with Oracle Support.

2. Procedure

Step 1: Perform Initial Troubleshooting
Begin by checking common causes that prevent CAPS from posting to R&A:

• Firewall issues blocking CAPS from reaching the E-Gateway
• Incorrect Regional Settings on the system
• Incorrect IP configurations on workstations or CAPS

Once verified, check R&A for sales posting.
If no sales appear, proceed to Step 2.

Step 2: Determine Database Type

Identify whether the site is using MySQL or Microsoft SQL Server.

For MySQL:

• Download and install MySQL Workbench from: https://dev.mysql.com/downloads/workbench/

For Microsoft SQL:

• Use Query Express Plus
• Log into the respective SQL environment.

Step 3: Run Database Queries

Use the queries below to check posting status in CAPS.

If using MySQL

USE CheckPosting;
SELECT
    CONVERT(ReqTime, DATE) AS BusinessDate,
    CASE
        WHEN ReqState = '0' THEN 'Pending to Post'
        WHEN ReqState = '1' THEN 'Posted'
        WHEN ReqState = '2' THEN 'Failed - Retry Later'
        WHEN ReqState = '3' THEN 'Failed - Rejected'
    END AS Enterprise_Posting_Status,
    ReqState,
    COUNT(ReqState) AS Mrequest_Count
FROM MREQUESTS
GROUP BY BusinessDate, ReqState
ORDER BY BusinessDate, ReqState;

If using Microsoft SQL

SELECT
    CONVERT(DATE, ReqTime) AS BusinessDate,
    CASE
        WHEN MREQUESTS.ReqState = '0' THEN 'Pending to Post'
        WHEN MREQUESTS.ReqState = '1' THEN 'Posted'
        WHEN MREQUESTS.ReqState = '2' THEN 'Failed - Retry Later'
        WHEN MREQUESTS.ReqState = '3' THEN 'Failed - Rejected'
    END AS Enterprise_Posting_Status,
    ReqState,
    COUNT(ReqState) AS Mrequest_Count
FROM [CheckPostingDB].[dbo].[MREQUESTS]
GROUP BY ReqState, CONVERT(DATE, ReqTime)
ORDER BY CONVERT(DATE, ReqTime), ReqState;

These queries display the CAPS posting status.
Take note of any entries showing “Failed - Rejected” (ReqState = 3).

Step 4: Verify Failed Requests

To determine how many requests are in a “Failed - Rejected” state, run the following query:
SELECT *
FROM MREQUESTS
WHERE ReqState = 3;
The number of results should match the Mrequest_Count with a status of 3 from Step 3.

Step 5: Next Actions Based on Findings

• If the ReqState = 3 entries are preventing further postings, you need to escalate to the POS Technical Team for further assistance.
• If re-running the monitoring script shows changing totals under the “Posted” column, this indicates that the site is actively reposting sales — no immediate action is required. Continue to monitor progress by comparing Property Financial and Daily Ops reports in R&A.

Important Note:

If there is no clear reason for the failed postings after restarting CAPS and the workstations, and network settings (firewall, IPs) are confirmed correct:
Do NOT replay the checks.
Replaying checks should only be done if the CAPS database is corrupt and cannot process data. This guidance is based on Oracle documentation.