We are performing PVO extractions every two hours; however, we are encountering reconciliation discrepancies during our quality checks due to the high frequency of data updates as system is constantly being used by users. To resolve this, we intend to utilize Oracle Flashback Technology to query data as of a specific timestamp, ensuring our validation matches the extraction window.
When attempting to execute the following query against the ApplicationDB_FSCM data source:
SQL
SELECT * FROM ap_invoices_all AS OF TIMESTAMP TO_TIMESTAMP(:P_AS_OF_DATE, 'YYYY-MM-DD HH24:MI:SS')
We receive an "insufficient privileges" error.
Clarification required on below points :
- Data Source Confirmation: if
ApplicationDB_FSCM supports Flashback Query or if we should be pointing to a different schema/data source for this purpose. - Permissions: What specific roles or system privileges (e.g.,
FLASHBACK or SELECT ANY TRANSACTION) are required for our integration user to execute "AS OF TIMESTAMP" queries on the ap_invoices_all table? - Configuration: Are there any specific profile options or database settings in this environment that need to be enabled to support this?
Is there any other mechanism through which we can reconcile the extract data with oracle fusion if we are performing the reconciliation after say 30 mins.
