Export Scenario with Incremental - All Records not exporting all newly created and modified records

It sounds like the issue you're experiencing with missed payments during export could be related to timing, data locking, or system state inconsistencies in Acumatica. Since you’re using a SQL provider and exporting directly to a table, there are several things to check and consider to identify why these payments are skipped. Here are some areas to focus on:

1. Export Scenario Filtering Logic

Ensure the filtering logic in your export scenario is not inadvertently excluding certain records. Since payments created, applied, released, and closed all within a short period are being skipped, it could be that these records are in a state where they don't meet your filtering criteria at the moment the export runs.

• Check if the filters used in your export scenario are too restrictive.
• Pay special attention to how you're filtering based on status, last modified date, or any other fields used to determine which payments to export. For example, if you’re filtering based on LastModifiedDateTime, there could be a gap in the timestamp where certain payments are missed.
• Ensure that you’re capturing payments in both open and closed states, especially for payments that go through multiple status changes rapidly.

2. Timing/Concurrency Issues

Since payments can be created, modified, released, and closed in a short time frame, it’s possible that the export runs at a point where the payment is not yet fully processed, which may cause it to be skipped.

• Concurrency Issues: If the payment is still being processed (i.e., status changes are ongoing or data is still being written to the database), it might not yet meet the criteria for export, resulting in the export skipping it.
• Consider adding a buffer period or using a timestamp cutoff in your export scenario to account for payments that were recently modified but not yet exported.
• Check the timing of your export scenario. You may want to run the export after a set period once all relevant processes (payment release, closing, etc.) are fully completed.

3. Check Batch Jobs/Processing Times

Since you mentioned that the issue occurs more often with credit card payments, this could be related to how Acumatica processes credit card payments, which involve multiple steps (authorization, capture, etc.). Payments that go through rapid transitions from created to applied to released may not be fully processed in time for the export.

• Ensure that background processing or batch jobs that finalize payments (like credit card processing) are fully completed before running the export.
• Investigate if there’s any lag time between when a payment is marked as released/closed and when it becomes eligible for export.

4. Check Database Triggers or Workflows

Since you're exporting data directly into a table, ensure that there are no workflows or processes that update the ARRegister, ARAdjust, or related tables after the payment has been processed.

• Check if triggers or scheduled workflows might be modifying the payment records post-processing, leading to some payments being skipped in the export run.

5. Revisit the SQL Query

Since your export scenario is using a SQL provider, double-check the SQL query that pulls the payments. Make sure the logic accounts for payments that go through fast transitions (e.g., created, modified, applied, released, closed) and doesn’t skip them unintentionally.

• Check for transaction locking: If multiple processes are accessing the payment records simultaneously, there could be transaction locking in the database that causes the export scenario to skip certain records that are locked or being modified at the time.
• Ensure that the SQL query isn't excluding records based on complex status changes or rapid state transitions.

6. Check Acumatica Event Logging

Enable Acumatica trace logging to see if there are any relevant system logs for skipped payments during the time the export runs. This might give you more insight into whether the payments were skipped due to system delays, locks, or some other issue.

7. Consider Transactional States and Logs

In scenarios where payments are processed quickly (like credit card payments), the transactional state might not be final when the export runs, especially if these payments are processed via an external payment gateway or batch job.

• Investigate if there are any deferred processing states or pending statuses that might cause these payments to be skipped during export.
• Consider adding a check in your SQL query to handle payments that may have rapid transitions between statuses.

8. Re-run the Export

If you're able to identify missed payments manually after the export runs, consider re-running the export with a different time range to catch any payments that were missed. This would help in cases where payments are processed after the export runs but before the next scheduled export.

Suggestions for Improvement:

1. Extend the Time Window for Data Retrieval: Adjust the filter criteria in your export scenario to ensure that records created or modified just before the export runs are not missed. This could include setting a wider time window or ensuring that payments with a Closed status are included.

2. Use Batch Processing or Scheduled Exports: If you are already running the export scenario manually, consider automating it with scheduled batch jobs, so it runs after business hours when all payments are processed.

3. Add a Custom Status/Flag: If necessary, you can implement a custom flag or status field on payments to indicate that they’ve been successfully exported. This way, you can ensure that every payment gets exported once and only once.

By checking the timing, filtering logic, and ensuring that your SQL query accounts for different payment states, you should be able to identify and resolve why certain payments are being skipped.

Thank you for sharing your solution with the community @kmckenzie!