Thank You every one for you inputs.
I just took a trace of portlet and ran the query in Toad.
For a resource Named Agarwal all the records are returned but for other resources named Paramkusham no records are found.
By commenting the line ROWNUM < (select opt_query_limit from odf_ca_opt_system_settings) I could get all the rows for paramkusham as well.
I this something related to Row Limit or sorting order.
Below line is added additionally in trace query
select * from (select row_number() over ( order by slice_date_id asc, resource_name asc, project_name asc) row_num, count(*) over () num_rows.
Is this something clarity is adding during executing the portlet?
I couldn't understand if the issue is due to query or limitation from clarity side?
Please share your inputs.
Here is the query from trace
select * from (select row_number() over ( order by slice_date_id asc, resource_name asc, project_name asc) row_num, count(*) over () num_rows, q.* from ( SELECT TM.PRID ID
, SRMR.ID RESOURCE_ID
, SRMR.UNIQUE_NAME RESOURCE_CODE
, SRMR.FULL_NAME RESOURCE_NAME
, RMGR.ID RESOURCE_MANAGER_ID
, RMGR.FULL_NAME RESOURCE_MANAGER
, ROL.ID PRIMARY_ROLE_ID
, ROL.FULL_NAME PRIMARY_ROLE
, SRMR.PERSON_TYPE EMPLOYMENT_TYPE_ID
, ET.NAME EMPLOYMENT_TYPE
, E.ID EXECUTIVE_ID
, E.FULL_NAME EXECUTIVE
, ODFR.OPT_PRIMARY_APP PRIMARY_APP_ID
, APP.NAME PRIMARY_APP
, ODFR.OPT_ONSHORE_OFFSHORE ONSHORE_OFFSHORE_ID
, OO.NAME ONSHORE_OFFSHORE
, ODFR.OPT_BUILD_LOCATION BUILD_LOCATION
, INVI.ID PROJECT_ID
, INVI.CODE PROJECT_CODE
, INVI.NAME PROJECT_NAME
, INVI.STATUS PROJECT_STATUS_ID
, ST.NAME PROJECT_STATUS
, ODFP.OPT_SUB_TYPE PROJECT_SUB_TYPE_ID
, SUB.NAME PROJECT_SUB_TYPE
, TM.PRID TEAM_ID
, TROL.ID TEAM_ROLE_ID
, TROL.FULL_NAME TEAM_ROLE
, TM.PRBOOKING BOOKING_STATUS_ID
, BS.NAME BOOKING_STATUS
, AL.SLICE_DATE SLICE_DATE_ID
, TO_CHAR(AL.SLICE_DATE, 'MM/DD/YYYY') SLICE_DATE
, SUM(AL.SLICE) HOURS
FROM SRM_RESOURCES SRMR
JOIN PRJ_RESOURCES PRJR ON SRMR.ID = PRJR.PRID AND PRJR.PRISROLE = 0
JOIN ODF_CA_RESOURCE ODFR ON SRMR.ID = ODFR.ID
LEFT JOIN SRM_RESOURCES RMGR ON SRMR.MANAGER_ID = RMGR.USER_ID
LEFT JOIN SRM_RESOURCES ROL ON PRJR.PRPRIMARYROLEID = ROL.ID
LEFT JOIN CMN_LOOKUPS_V ET ON SRMR.PERSON_TYPE = ET.ID AND ET.LOOKUP_TYPE = 'SRM_RESOURCE_TYPE' AND ET.LANGUAGE_CODE = 'en'
LEFT JOIN SRM_RESOURCES E ON ODFR.OPT_EXECUTIVE2 = E.USER_ID
LEFT JOIN INV_INVESTMENTS APP ON ODFR.OPT_PRIMARY_APP = APP.ID AND APP.ODF_OBJECT_CODE = 'application'
LEFT JOIN CMN_LOOKUPS_V OO ON ODFR.OPT_ONSHORE_OFFSHORE = OO.LOOKUP_CODE AND OO.LOOKUP_TYPE = 'OPT_ONSHORE_OFFSHORE' AND OO.LANGUAGE_CODE = 'en'
JOIN PRTEAM TM ON SRMR.ID = TM.PRRESOURCEID
LEFT JOIN CMN_LOOKUPS_V BS ON TM.PRBOOKING = BS.LOOKUP_ENUM AND BS.LOOKUP_TYPE = 'BOOKING_STATUS_LIST' AND BS.LANGUAGE_CODE = 'en'
LEFT JOIN SRM_RESOURCES TROL ON TM.PRROLEID = TROL.ID
JOIN INV_INVESTMENTS INVI ON TM.PRPROJECTID = INVI.ID
LEFT JOIN ODF_CA_PROJECT ODFP ON INVI.ID = ODFP.ID
LEFT JOIN CMN_LOOKUPS_V ST ON INVI.STATUS = ST.LOOKUP_ENUM AND ST.LOOKUP_TYPE = 'INVESTMENT_OBJ_STATUS' AND ST.LANGUAGE_CODE = 'en'
LEFT JOIN ODF_CA_OPT_PROJECT_SUBTYPE SUB ON ODFP.OPT_SUB_TYPE = SUB.CODE
JOIN PRJ_BLB_SLICES AL ON TM.PRID = AL.PRJ_OBJECT_ID AND AL.SLICE_REQUEST_ID = (SELECT SR.ID FROM PRJ_BLB_SLICEREQUESTS SR WHERE SR.REQUEST_NAME = 'MONTHLY' || 'RESOURCEALLOCCURVE')
WHERE SRMR.IS_ACTIVE = 1
AND AL.SLICE_DATE BETWEEN TO_DATE('2016-01-01', 'YYYY-MM-DD') AND TO_DATE('2016-12-31', 'YYYY-MM-DD')
AND (0 = 1 OR (0 = 0 AND TM.PRALLOCSUM>0 ))
AND (0 = 1 OR (NVL(0, 0) = 0 ))
AND (5250272 IS NULL OR SRMR.ID IN (SELECT OBSA.RECORD_ID FROM OBS_UNITS_FLAT_BY_MODE OBSM JOIN PRJ_OBS_ASSOCIATIONS OBSA ON OBSM.LINKED_UNIT_ID = OBSA.UNIT_ID AND OBSA.TABLE_NAME = 'SRM_RESOURCES' WHERE OBSM.UNIT_ID = 5250272 AND OBSM.UNIT_MODE = NVL('OBS_UNIT_AND_CHILDREN', 'OBS_UNIT_AND_CHILDREN') ))
AND (NULL IS NULL OR INVI.ODF_OBJECT_CODE IN (SELECT O.CODE FROM OBS_UNITS_FLAT_BY_MODE OBSM JOIN PRJ_OBS_ASSOCIATIONS OBSA ON OBSM.LINKED_UNIT_ID = OBSA.UNIT_ID JOIN ODF_OBJECTS O ON OBSA.TABLE_NAME = O.OBS_CODE WHERE OBSM.UNIT_ID = NULL AND OBSM.UNIT_MODE = NVL('OBS_UNIT_AND_CHILDREN', 'OBS_UNIT_AND_CHILDREN') AND OBSA.RECORD_ID = INVI.ID ))
AND CASE WHEN INVI.STATUS = 1 OR INVI.STATUS= 5 THEN 1 ELSE 0 END = 1
AND TM.PRALLOCSUM IS NOT NULL
AND INVI.IS_ACTIVE = 1
AND 1=1 and 1=1 and 2 = 2 AND ROWNUM < (select opt_query_limit from odf_ca_opt_system_settings)
GROUP BY SRMR.ID
, SRMR.UNIQUE_NAME
, SRMR.FULL_NAME
, RMGR.ID
, RMGR.FULL_NAME
, ROL.ID
, ROL.FULL_NAME
, SRMR.PERSON_TYPE
, ET.NAME
, E.ID
, E.FULL_NAME
, ODFR.OPT_PRIMARY_APP
, APP.NAME
, ODFR.OPT_ONSHORE_OFFSHORE
, ODFR.OPT_BUILD_LOCATION
, ODFR.OPT_DIVISION
, ODFR.OPT_BUILD_LOCATION
, ODFR.OPT_ONSHORE_OFFSHORE
, OO.NAME
, ODFR.OPT_EXECUTIVE2
, ODFR.OPT_PRIMARY_APP
, INVI.ID
, INVI.CODE
, INVI.NAME
, INVI.STATUS
, ST.NAME
, ODFP.OPT_SUB_TYPE
, SUB.NAME
, TM.PRID
, TROL.ID
, TROL.FULL_NAME
, TM.PRBOOKING
, BS.NAME
, AL.SLICE_DATE
HAVING 1=1 ORDER BY slice_date_id,resource_name,project_name ) q) q where q.row_num < 50001 order by q.row_num