Please see the SAP Analytics Approach document, section 'Documentation' for more information about the context of this document.

High-Level Specification

Functional Overview

The reconciliation mechanism ensures reliability and traceability for custom integrations (e.g., Icertis to CPI) by logging failed messages, managing scheduled retries, tracking retry attempts, and notifying stakeholders of persistent failures. 

Scope and Objectives

Scope

The scope of this development covers the design, implementation, and monitoring of a reconciliation mechanism for custom integrations, such as the Icertis to CPI interface. The solution encompasses:

• Logging of all failed integration messages in a centralized master data table.
• Automated, scheduled retry of failed messages, with tracking of each attempt.
• Escalation via automated notifications to stakeholders if failures persist beyond a defined threshold.
• Providing visibility to authorized users for monitoring and manual intervention.
• Ensuring compliance with Syensqo’s security, data privacy, and audit requirements.
• Supporting reporting and analytics needs as defined in related Jira requests.

This scope encompasses integration with both internal and external systems, covering all technical, functional, and compliance aspects necessary for reliable and transparent message processing.

Objectives

• Reliability: Ensure that all failed integration messages are captured, retried, and escalated as needed to minimize data loss and integration gaps.
• Traceability: Maintain a comprehensive audit trail for each integration attempt, including message content, timestamps, error details, and a record of retries.
• Timely Escalation: Automatically notify relevant stakeholders when persistent failures occur, enabling prompt manual intervention.
• User Transparency: Provide authorized users with real-time visibility into the status of integration messages for proactive monitoring and troubleshooting.
• Compliance: Adhere to Syensqo’s IT security, data privacy, and regulatory requirements throughout the process.
• Reporting: Enable robust reporting and analytics on integration performance, failure rates, and resolution times to support continuous improvement.

Assumptions

• Source systems (e.g., Icertis) are responsible for initial message transmission and may perform immediate retries, but scheduled reconciliation is handled separately.
• The master data table is the single source of truth for failed integration attempts.
• Scheduled jobs (e.g., every 12 or 24 hours) are configured and maintained by IT operations.
• Email notification infrastructure is available and configured to alert stakeholders after repeated failures.
• Users have appropriate access to view failed message statuses via the Icertis UI or other authorized interfaces.
• All integrations adhere to Syensqo’s security and compliance policies.

Dependencies

• Reliable connectivity between source systems (Icertis) and the target system.
• Scheduled batch jobs must run after source systems have completed their integration attempts.
• The email notification system must be operational for timely stakeholder alerts.
• Data retention and archiving policies must be defined and adhered to.

Special Requirements

• Integration with third-party systems (Icertis, CPI) requires secure authentication (e.g., OAuth, certificates).
• Audit trails must be maintained for all message processing and status changes.
• System must support optimistic concurrency control (e.g., via ETag).
• All error messages and notifications must be clear, actionable, and compliant with internal communication standards.

Component Diagram

Key Features

1. Failure Logging

   • When an integration call (e.g., Icertis to CPI) fails, the failed message is logged in a master data table.
   • The log includes details such as message content, timestamp, error details, and a retry count.

2. Master Data Table

   • Acts as the single source of truth for all failed integration attempts.

3. Scheduled Retry Job

   • Runs once or twice daily.
   • Picks up failed messages from the master data table and attempts to resend them to the target endpoint.
   • Updates the retry count and status after each attempt.

4. Retry Count & Failure Notification

   • Each message’s retry count is incremented with every attempt.
   • If a message fails more than 3 times, an automated failure notification email is sent to relevant stakeholders.
   • This ensures timely awareness and manual intervention if needed.

5. User Visibility

   • Users with access (e.g., via Icertis UI) can view the status of failed integrations directly from the master data.
   • This promotes transparency and allows for proactive monitoring.

Sequence Diagram

Below is a step-by-step flow of how the reconciliation mechanism operates in real time:

1. Integration Attempt
   • Icertis (or another system) sends a message to the CPI endpoint.
2. Failure Detection
   • If the message fails (e.g., due to a network error or endpoint issue), the failure is immediately logged in the master data table with all relevant details.
3. Immediate Retry (by Source System)
   • The source system (e.g., Icertis) may attempt a quick retry, but this does not replace the scheduled reconciliation process.
4. Scheduled Reconciliation Job
   • At scheduled intervals (e.g., every 12 or 24 hours), the reconciliation job scans the master data for messages with status “Pending” or “Failed.”
   • The job attempts to resend these messages to the endpoint.
5. Update Master Data
   • After each retry, the master data is updated:
     • Retry count is incremented.
     • Status is updated (e.g., “Retried,” “Success,” or “Failed”).
     • Last attempt timestamp is recorded.
6. Failure Escalation
   • If a message’s retry count exceeds 3, the system triggers an automated failure notification email to stakeholders.
7. User Monitoring
• Users can access the master data (e.g., via Icertis UI) to view the status of all failed or retried messages.

Masterdata Attributes/Columns

Solution Overview

Dimensions & Measures: Requirements View

Dimensions (AKA Characteristics or Entities)

This section defines the level of detail and the business entities by which the key figures and measures will be analyzed. All relevant dimensions—whether used in rows, columns, or as free characteristics—are listed below. Each field is described to ensure clarity and completeness, except where marked as optional.

Security & Authorization

Variables

Data Processing Logic Considerations

• Failed messages are logged with all relevant details (request, response, error, timestamps, etc.).
• Scheduled job queries master data for messages with status “Pending” or “Failed.”
• Each retry increments TryCount and updates status/timestamps.
• If TryCount > 3, triggers notification and flags record.
• Users can query master data for monitoring and reporting.
• Data joins may be required for enrichment (e.g., user info, integration config).
• Filters applied for active records (IsActive=1).

History, Plan and Targets

• Data loads are incremental, capturing new and updated records.
• Historical data retained per Syensqo policy (e.g., 12 months).
• Mapping from legacy system identifiers to new system values handled by migration team.

Currency and/or Unit Conversions

• Not applicable unless integration payloads include currency or units; if so, conversions must use standard Syensqo exchange rates and unit mappings.

Volumetrics

• Expected records per day: 100–500 (scalable).
• Long-term growth: 10,000+ records/year.
• Data retention: 12–24 months, subject to compliance.

Data Load Frequency

• Scheduled job: Daily (default), can be configured to run twice daily.
• Near-real-time not required; batch processing is sufficient.
• Snapshots not required unless for audit purposes.

Performance Considerations

• Reports should handle up to 10,000 records efficiently.
• Indexing on key fields (ID, Status, StartedAt) recommended.
• Scheduled jobs must complete within 30 minutes.

Testing

How to test

• Use test data to simulate failed, retried, and successful messages.
• Validate retry logic, status updates, and notification triggers.
• Test both positive (successful retry) and negative (persistent failure) scenarios.
• Use Data Analyzer or equivalent tool for data validation.
• Provide test user accounts with varying access levels.

Test Conditions and Expected Results

Testing Considerations / Dependencies

• Test after integration endpoints are available.
• Validate email notifications with test recipients.
• Test UI access for different user roles.

Other Requirements

• All logs and notifications must be auditable.
• System must support future integration types with minimal changes.
• Documentation must be maintained for all configuration and logic.
• Ensure compliance with Syensqo IT security and data privacy policies.

See also

Change log