AnsweredAssumed Answered

Error 500 - Internal Server Error. The server could not retrieve the document due to server- configuration or other technical problems. Contact your site administratior.

Question asked by sravani_nidamanuri on Nov 24, 2015
Latest reply on Nov 24, 2015 by Dave_3.0

We are seeing this error on running Custom portlet. Verified app-ca logs as well but couldn't find any error. But this issue seems to be sporadic. We are unable to replicate this issue every time.

 

The NSQLused by this portlet is as follows

 

SELECT @SELECT:DIM:USER_DEF:IMPLIED:ROLE:R.ROLE_ID:ROLE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ROLE:R.ROLE_CODE:ROLE_CODE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ROLE:R.ROLE_NAME:ROLE_NAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ROLE:R.HOURS_TYPE:HOURS_TYPE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:ROLE:R.STD_CAL_AVAIL:STD_CAL_AVAIL@
, @SELECT:DIM:USER_DEF:IMPLIED:TIMEPERIOD:R.SLICE_DATE:SLICE_DATE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEPERIOD:TO_CHAR(R.SLICE_DATE, 'MM/DD/YYYY'):SLICE_DATE@
, @SELECT:METRIC:USER_DEF:IMPLIED:R.AVAIL:AVAIL@
, @SELECT:METRIC:USER_DEF:IMPLIED:R.ALLOC:ALLOC@
, @SELECT:METRIC:USER_DEF:IMPLIED:R.AVAIL - R.ALLOC:REMAIN@

FROM (SELECT S.ROLE_ID ROLE_ID, ROL.UNIQUE_NAME ROLE_CODE, ROL.FULL_NAME ROLE_NAME
  , S.SLICE_DATE, PA.HOURS_TYPE HOURS_TYPE, CAL.SLICE STD_CAL_AVAIL
  , SUM(CASE WHEN S.SLICE_TYPE = 'AVAIL' THEN S.SLICE ELSE 0 END) / CASE WHEN PA.HOURS_TYPE = 'FTE' THEN CAL.SLICE ELSE 1 END AVAIL
  , SUM(CASE WHEN S.SLICE_TYPE = 'ALLOC' THEN S.SLICE ELSE 0 END) / CASE WHEN PA.HOURS_TYPE = 'FTE' THEN CAL.SLICE ELSE 1 END ALLOC
  FROM (SELECT NVL(PRJR.PRPRIMARYROLEID,2) ROLE_ID, 'AVAIL' SLICE_TYPE, AV.SLICE_DATE, SUM(AV.SLICE) SLICE
    FROM SRM_RESOURCES SRMR
    JOIN PRJ_RESOURCES PRJR ON SRMR.ID = PRJR.PRID
    JOIN ODF_CA_RESOURCE ODFR ON SRMR.ID = ODFR.ID
    LEFT JOIN INV_INVESTMENTS APP ON APP.ID = ODFR.OPT_PRIMARY_APP
    JOIN PRJ_BLB_SLICES AV ON PRJR.PRID = AV.PRJ_OBJECT_ID AND AV.SLICE_REQUEST_ID = (SELECT SR.ID FROM PRJ_BLB_SLICEREQUESTS SR WHERE SR.REQUEST_NAME = @WHERE:PARAM:USER_DEF:STRING:SLICE_BY_STRING@ || 'RESOURCEAVAILCURVE')
    WHERE SRMR.IS_ACTIVE = 1 AND SRMR.RESOURCE_TYPE = 0 AND PRJR.PRISROLE = 0
    AND (@WHERE:PARAM:USER_DEF:INTEGER:EMPLOYEE_TYPE@ IS NULL OR SRMR.PERSON_TYPE = @WHERE:PARAM:USER_DEF:INTEGER:EMPLOYEE_TYPE@)
    AND (@WHERE:PARAM:USER_DEF:STRING:PRI_BILLING_APP@ IS NULL OR APP.CODE = @WHERE:PARAM:USER_DEF:STRING:PRI_BILLING_APP@)
    AND (@WHERE:PARAM:USER_DEF:STRING:LOCATION@ IS NULL OR ODFR.OPT_BUILD_LOCATION = @WHERE:PARAM:USER_DEF:STRING:LOCATION@)
    AND (@WHERE:PARAM:USER_DEF:STRING:ON_OFF_SHORE@ IS NULL OR ODFR.OPT_ONSHORE_OFFSHORE = @WHERE:PARAM:USER_DEF:STRING:ON_OFF_SHORE@)
    and (@WHERE:PARAM:USER_DEF:INTEGER:EXECUTIVE@ IS NULL OR ODFR.OPT_EXECUTIVE2 = @WHERE:PARAM:USER_DEF:INTEGER:EXECUTIVE@)
    AND (@WHERE:PARAM:USER_DEF:STRING:DIVISION@ IS NULL OR UPPER(ODFR.OPT_DIVISION) LIKE UPPER(REPLACE(@WHERE:PARAM:USER_DEF:STRING:DIVISION@, '*' ,'%')))
    AND (@WHERE:PARAM:USER_DEF:INTEGER:CAPACITY_OBS_ID@ 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 = @WHERE:PARAM:USER_DEF:INTEGER:CAPACITY_OBS_ID@ AND OBSM.UNIT_MODE = NVL(@WHERE:PARAM:USER_DEF:STRING:CAPACITY_OBS_MODE@, 'OBS_UNIT_AND_CHILDREN')))
    GROUP BY NVL(PRJR.PRPRIMARYROLEID,2), AV.SLICE_DATE
    UNION ALL
    SELECT NVL(PRJR.PRPRIMARYROLEID, NVL(TM.PRROLEID,2)) ROLE_ID, 'ALLOC' SLICE_TYPE, AL.SLICE_DATE, SUM(AL.SLICE) SLICE
    FROM INV_INVESTMENTS INVI
    JOIN ODF_CA_PROJECT ODFP ON INVI.ID = ODFP.ID
    JOIN PRTEAM TM ON INVI.ID = TM.PRPROJECTID
    JOIN SRM_RESOURCES SRMR ON TM.PRRESOURCEID = SRMR.ID
    JOIN PRJ_RESOURCES PRJR ON SRMR.ID = PRJR.PRID
    JOIN ODF_CA_RESOURCE ODFR ON SRMR.ID = ODFR.ID
    LEFT JOIN INV_INVESTMENTS APP ON APP.ID = ODFR.OPT_PRIMARY_APP
    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 = @WHERE:PARAM:USER_DEF:STRING:SLICE_BY_STRING@ || 'RESOURCEALLOCCURVE')
    WHERE SRMR.IS_ACTIVE = 1 AND SRMR.RESOURCE_TYPE = 0
    AND INVI.STATUS <>8
    AND (@WHERE:PARAM:USER_DEF:INTEGER:PROJECT_STATUS@ IS NULL OR INVI.STATUS = @WHERE:PARAM:USER_DEF:INTEGER:PROJECT_STATUS@)
    AND (@WHERE:PARAM:USER_DEF:INTEGER:PROJECT_PRIORITY@ IS NULL OR INVI.PRIORITY = @WHERE:PARAM:USER_DEF:INTEGER:PROJECT_PRIORITY@)
    AND (@WHERE:PARAM:USER_DEF:STRING:PROJECT_SUBTYPE@ IS NULL OR ODFP.OPT_SUB_TYPE = @WHERE:PARAM:USER_DEF:STRING:PROJECT_SUBTYPE@)
    AND (@WHERE:PARAM:USER_DEF:INTEGER:BOOKING_STATUS@ IS NULL OR TM.PRBOOKING = @WHERE:PARAM:USER_DEF:INTEGER:BOOKING_STATUS@)
    AND (@WHERE:PARAM:USER_DEF:INTEGER:EMPLOYEE_TYPE@ IS NULL OR SRMR.PERSON_TYPE = @WHERE:PARAM:USER_DEF:INTEGER:EMPLOYEE_TYPE@)
    AND (@WHERE:PARAM:USER_DEF:STRING:PRI_BILLING_APP@ IS NULL OR APP.CODE = @WHERE:PARAM:USER_DEF:STRING:PRI_BILLING_APP@)
    AND (@WHERE:PARAM:USER_DEF:STRING:LOCATION@ IS NULL OR ODFR.OPT_BUILD_LOCATION = @WHERE:PARAM:USER_DEF:STRING:LOCATION@)
    AND (@WHERE:PARAM:USER_DEF:STRING:ON_OFF_SHORE@ IS NULL OR ODFR.OPT_ONSHORE_OFFSHORE = @WHERE:PARAM:USER_DEF:STRING:ON_OFF_SHORE@)
    AND (@WHERE:PARAM:USER_DEF:INTEGER:EXECUTIVE@ IS NULL OR ODFR.OPT_EXECUTIVE2 = @WHERE:PARAM:USER_DEF:INTEGER:EXECUTIVE@)
    AND (@WHERE:PARAM:USER_DEF:STRING:DIVISION@ IS NULL OR UPPER(ODFR.OPT_DIVISION) LIKE UPPER(REPLACE(@WHERE:PARAM:USER_DEF:STRING:DIVISION@, '*' ,'%')))
    AND (@WHERE:PARAM:USER_DEF:INTEGER:INVEST_OBS_ID@ IS NULL OR INVI.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 JOIN ODF_OBJECTS O ON OBSA.TABLE_NAME = O.OBS_CODE WHERE OBSM.UNIT_ID = @WHERE:PARAM:USER_DEF:INTEGER:INVEST_OBS_ID@ AND OBSM.UNIT_MODE = NVL(@WHERE:PARAM:USER_DEF:STRING:INVEST_OBS_MODE@, 'OBS_UNIT_AND_CHILDREN') AND O.CODE = INVI.ODF_OBJECT_CODE))
    AND (@WHERE:PARAM:USER_DEF:INTEGER:DEMAND_OBS_ID@ 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 = @WHERE:PARAM:USER_DEF:INTEGER:DEMAND_OBS_ID@ AND OBSM.UNIT_MODE = NVL(@WHERE:PARAM:USER_DEF:STRING:DEMAND_OBS_MODE@, 'OBS_UNIT_AND_CHILDREN')))
    GROUP BY NVL(PRJR.PRPRIMARYROLEID, NVL(TM.PRROLEID,2)), AL.SLICE_DATE) S
  LEFT JOIN SRM_RESOURCES ROL ON ROL.ID = S.ROLE_ID
  LEFT JOIN PRJ_RESOURCES PROL ON ROL.ID = PROL.PRID AND PROL.PRISROLE = 1
  JOIN (SELECT SRMR.ID, AV.SLICE_DATE, SUM(AV.SLICE) SLICE
    FROM SRM_RESOURCES SRMR
    JOIN PRJ_BLB_SLICES AV ON SRMR.ID = AV.PRJ_OBJECT_ID AND AV.SLICE_REQUEST_ID = (SELECT SR.ID FROM PRJ_BLB_SLICEREQUESTS SR WHERE SR.REQUEST_NAME = @WHERE:PARAM:USER_DEF:STRING:SLICE_BY_STRING@ || 'RESOURCEAVAILCURVE')
    WHERE SRMR.UNIQUE_NAME = 'admin'
    GROUP BY SRMR.ID, AV.SLICE_DATE) CAL ON S.SLICE_DATE = CAL.SLICE_DATE
  JOIN (SELECT NVL(@WHERE:PARAM:USER_DEF:STRING:HOURS_TYPE@, 'FTE') HOURS_TYPE FROM DUAL) PA ON 1=1
  WHERE 1=1
  AND S.SLICE_DATE BETWEEN
  CASE @WHERE:PARAM:USER_DEF:STRING:SLICE_BY_STRING@
    WHEN 'WEEKLY' THEN TRUNC(@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'IW') - 1
    WHEN 'MONTHLY' THEN TRUNC(@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'MM')
    WHEN 'QUARTERLY' THEN TRUNC(@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'Q')
    WHEN 'YEARLY' THEN TRUNC(@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'YY')
  END
AND
  CASE @WHERE:PARAM:USER_DEF:STRING:SLICE_BY_STRING@
    WHEN 'WEEKLY' THEN TRUNC(@WHERE:PARAM:USER_DEF:DATE:FINISH_DATE@, 'IW') + 5
    WHEN 'MONTHLY' THEN LAST_DAY(TRUNC(@WHERE:PARAM:USER_DEF:DATE:FINISH_DATE@, 'MM'))
    WHEN 'QUARTERLY' THEN LAST_DAY(ADD_MONTHS(TRUNC(@WHERE:PARAM:USER_DEF:DATE:FINISH_DATE@, 'Q'), 2))
    WHEN 'YEARLY' THEN LAST_DAY(ADD_MONTHS(TRUNC(@WHERE:PARAM:USER_DEF:DATE:FINISH_DATE@, 'YY'), 11))
  END

  GROUP BY S.ROLE_ID, ROL.UNIQUE_NAME, ROL.FULL_NAME, S.SLICE_DATE, PA.HOURS_TYPE, CAL.SLICE) R

WHERE 1=1
AND (@WHERE:PARAM:USER_DEF:INTEGER:OVER_PCT@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:OVER_PCT@ > (CASE WHEN R.AVAIL > 0 THEN (R.ALLOC / R.AVAIL * 100) - 100 END))
AND ROWNUM<(SELECT opt_query_limit FROM odf_ca_opt_system_settings)

AND @FILTER@

 

 

I just searched articles and found this

 

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec438891.aspx

 

As per this article Dimension key should not have any null values or duplicates. We are able to see duplicate values for Role_code by running the query in backend. But if this is the causing issue the portlet should throw error every time.

 

If this is the reason behind the error can we replace union all with union? Will this solve the issue.

 

Any suggestion would really be helpful. Thanks in advance.

 

Sravani

 

Message was edited by: Sravani Nidamanuri

Outcomes