Mystery of _unnest_subquery=FALSE resolved

Continuing from where I left off in my previous post, we promptly referred this issue with the COBOL process SFPGLINT to Oracle as we had not done any customization. In the meanwhile we switched back the parameter "_unnest_subquery" to TRUE as an interim workaround. Oracle on their part started the investigation of the case with the usual suspicion around whether we are on a "supported version" of Oracle DB, PeopleTools, Weblogic etc...

Going down this path uncovered the horror that the production database (on version 11.2.0.2) was not up to date on the mandatory oracle patches for PeopleTools 8.52.We were missing patch numbers - 12704078, 13504791, 16065596 

What were these mandatory Oracle patches?
You can get them here ->[Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]
Based on your database version and PeopleTools version you need to be up to date and not have missed any of them.

How did Oracle find out what we missed? 
Oracle requested for the init.ora file and the output of this command (Need to login as SYS/SYSADM) - 

 $ORACLE_HOME/OPatch/opatch lsinventory > /tmp/opls.txt   

This text file will contain the oracle patch numbers installed in the database. You can then compare if you have all those mandatory patches (relevant to you) from the PeopleTools patch spreadsheet listed in this text file. If you can't find it, then it is highly likely you are missing it.

Eventually after these missing patches were applied in production we then switched back the database parameter flag _unnest_subquery to FALSE and ran the accounting entry generation process SFPGLINT and it worked fine. Thus the issue was resolved without having to do any further investigation.

That a SQL query would stop fetching rows with a database parameter set to False when the database is missing mandatory patches was beyond me. Murphy's law in full force. This issue highlighted the importance of not missing on mandatory patches, a lesson I won't forget soon.

Note: While applying the missing patches you we realized each patch has certain pre-requisite patch and then there is possibility of the mandatory patch mentioned being overridden by another recent patch which was not applicable to our DB version. If you end up in a similar situation you need to log a case with oracle requesting for a merge-patch for your DB version.