To summarize the steps, I have:
1. Created BI report using SQL data model with CSV output
2. Created an Enterprise Scheduled job to trigger the same report from ERP's Scheduled Processes
3. Created a Data Source integration application in Data Management
4. Finalized the mapping details in Data Management
All I have to do now is to run the integration! Unfortunately, the data load rule failed at "Import data" stage and I bumped into this error.
FATAL [AIF]: Error in Insert Process Details
The good thing is the ERP extract is getting exported and pushed to EPM without any issues, it's just failing to import in the staging area for some reason. How did I know the extract was working from ERP side? Well I checked the scheduled job output files in ERP and Data Management files in EPM (used EPM Automate) and the extract is there.
Here is the scheduled processes status in ERP indicating the job was successful from ERP side.
The log didn't have any meaningful information apart from FATAL [AIF]: Error in Insert Process Details, only this line so I had to create an SR with Oracle to sort it out and luckily they got back to me with the solution! it was the period mapping type! I'm trying to load the extract in BegBalance & No Year so I was using application level defined periods (not general mapping) and the integration had the "Explicit" Period Mapping Type by default.
After changing it to default it worked just fine.