Resolution:
Following is the SQL statement:

CREATE VIEW &ltVIEW> AS SELECT MC1.* FROM MCT_39375 MC1  WHERE MC1.CMODVERSION = (select max(CMODVERSION) from MCT_39375 MCTX where MCTX.CPRODUCTKEYID = MC1.CPRODUCTKEYID AND    MCTX.CSTATE = 'CONFIRMED'  AND MCTX.CCREATIONDATE <  sysdate ) AND MC1.CACTIVE = 'Y' order by mc1.CPRODUCTID

Note: This SQL statement may not work on future versions of CIM and depends on the metadata defined for the CIM database instance. This works on CIM 6.1 currently.