sreeram

To show null  in the Allocation timeframe in my Portlet

Discussion created by sreeram on Jun 22, 2009
Latest reply on Jun 23, 2009 by sreeram
Hi   I have designed the portlet using Nsql query to show the List of Resources belonging to the department and their corresponding assignments/planning in one or more projects.The portlet is presented with data organized per project and based on selected timeframe. In the Portlet ,there's filter called 'Is Assigned', it is combo box.When I filter it for 'Yes',I should get only the resources which is assigned and planned(ETC >0).When I filter it for 'NO' I should get the resources which do not have ETC and also   no allocation.(See Below for the Code)  In this I successfully queried everything ,the Problem is the client needs to show blank at the Allocation with in Monthly Time Frame for the resource having no allocation,which is quiet impossible in the NSQL .In the code Below I've   tried to add the paramterized Filter to show the Period.When I Tried the Sql Trace on this Nsql Query the Last statement changes to   A.PERIOD_START_DATE >= ? AND (A.PERIOD_START_DATE IS NULL OR A.PERIOD_START_DATE = A.PERIOD_START_DATE) .so when i try to filter for Null values in allocation .It doesn't bring the Null values.  Without the Last statement in the query it Works fine showing column called NO_PERIOD(replacing all the monthly time frame view) for   Allocation =0 ,but the client requires to view blank with   monthly time frame view   for   Allocation =0..  Is there any other way to meet the requirements  Thanks in Advance  RegardsRAM   SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:A.ID:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.DEPT_FILTER:DEPT_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.DEPTNAME:DEPTNAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.COST_CENTER_FILTER:COST_CENTER_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.COST_CENTER_DESC:COST_CENTER_DESC@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.COST_CENTER:COST_CENTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.RES_ID:RES_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.RES_FILTER:RES_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.RES_NAME:RES_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.PART_TIME:PART_TIME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.REG_TEMP:REG_TEMP@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.CHARGEABLE:CHARGEABLE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.DATEOFTERM:DATEOFTERM@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.PROJID:PROJID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.PROJCODE:PROJCODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.PROJNAME:PROJNAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.PROJDESCR:PROJDESCR@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.SCHEDSTRT:SCHEDSTRT@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.SCHEDFIN:SCHEDFIN@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.MGR_NAME:MGR_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.MGR_FILTER:MGR_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.TOA:TOA@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.PT_FILTER:PT_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.TASK_ID:TASK_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.TASK_NAME:TASK_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.START_DATE:START_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.END_DATE:END_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.WBE_ID:WBE_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.WBE_FILTER:WBE_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.WBE_DESC:WBE_DESC@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.WBE_START_DATE:WBE_START_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.WBE_END_DATE:WBE_END_DATE@,
@SELECT:DIM:USER_DEF:IMPLIED:TIMEPERIOD:NVL(A.PERIOD_NAME,'NO_PERIOD'):PERIOD_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEPERIOD:A.PERIOD_START_DATE:PERIOD_START_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.ASSIGN_ETC:ASSIGN_ETC@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.ASGN_TYPE:ASGN_TYPE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.AVAILIBILITY:AVAILIBILITY@,
@SELECT:METRIC:USER_DEF:IMPLIED:A.ALLOC_PERC:ALLOC_PERC@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.BU_NAME:BU_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.BU_FILTER:BU_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.HUB_NAME:HUB_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.HUB_FILTER:HUB_FILTER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:A.OC_NAME:OC_NAME@
FROM
(SELECT
PA.PRID||RES.ID ID,
DEPT.ID DEPT_FILTER,
DEPT.NAME DEPTNAME,
CC.ID COST_CENTER_FILTER,
CC.ATTR_DESCR COST_CENTER_DESC,
CC.CODE COST_CENTER,
RES.UNIQUE_NAME RES_ID,
RES.ID RES_FILTER,
RES.FULL_NAME RES_NAME,
(CASE ORES.ATTR_PARTTIME
WHEN 1 THEN 'PT'
WHEN 0 THEN 'FT'
END) PART_TIME,
REG.NAME REG_TEMP,
PRES.PRISOPEN CHARGEABLE,
RES.DATE_OF_TERMINATION DATEOFTERM,
INV.ID PROJID,
INV.CODE PROJCODE,
INV.NAME PROJNAME,
INV.DESCRIPTION PROJDESCR,
INV.SCHEDULE_START SCHEDSTRT,
INV.SCHEDULE_FINISH SCHEDFIN,
MGR.FIRST_NAME|| ' '||MGR.LAST_NAME MGR_NAME,
MGR.ID MGR_FILTER,
TOA.PRNAME TOA,
PTP.LOOKUP_CODE PT_FILTER,
WS.PRID TASK_ID,
WS.PREXTERNALID TASK_NAME,
PA.PRSTART START_DATE,
PA.PRFINISH END_DATE,
WBE.CODE WBE_ID,
WBE.ID WBE_FILTER,
WBE.ATTR_DESCRIPTION WBE_DESC,
WBE.ATTR_START_DATE WBE_START_DATE,
WBE.ATTR_END_DATE WBE_END_DATE,
TO_CHAR(ALLOC.SLICE_DATE,'YYYY-MM') PERIOD_NAME,
ALLOC.SLICE_DATE PERIOD_START_DATE,
PA.PRESTSUM/3600 ASSIGN_ETC,
CASE WHEN PA.PRESTSUM > 0 THEN 1 ELSE 0 END ASGN_TYPE,
(SELECT MAX(S.SLICE)
FROM PRJ_BLB_SLICES S
WHERE S.SLICE_REQUEST_ID=1
AND S.PRJ_OBJECT_ID =RES.ID) AVAILIBILITY,
NVL(ALLOC.ESTSUM,0) ALLOC_PERC,
BU.NAME BU_NAME,
BU.ID BU_FILTER,
HUB.NAME HUB_NAME,
HUB.ID HUB_FILTER,
OC.NAME OC_NAME
FROM PRTASK PT
INNER JOIN ODF_CA_TASK OT ON PT.PRID = OT.ID
LEFT OUTER JOIN PRTASK WS ON WS.PRID = Z_GET_WS_ID(PT.PRID)
LEFT OUTER JOIN PRTASK TOA ON TOA.PRID = Z_GET_ACTIVITY_TYPE_ID(PT.PRID)
LEFT OUTER JOIN ODF_CA_OBJ_COSTCENTER CC ON OT.ATTR_OUCOSTCENTER = CC.ID
INNER JOIN PRJ_OBS_UNITS DEPT ON DEPT.ID = OT.ATTR_SOBSU_V1
INNER JOIN
(SELECT DISTINCT OBS.LINKED_UNIT_ID
FROM OBS_UNITS_FLAT_BY_MODE OBS
INNER JOIN DEPARTMENTS D
ON OBS.UNIT_ID = D.OBS_UNIT_ID
AND OBS.UNIT_MODE = 'OBS_UNIT_AND_CHILDREN'
WHERE (
D.DEPARTMENT_MANAGER_ID = @WHERE:PARAM:USER_ID@
)

UNION

SELECT DISTINCT OBS.LINKED_UNIT_ID
FROM OBS_UNITS_FLAT_BY_MODE OBS
INNER JOIN DEPARTMENTS D
ON OBS.UNIT_ID = D.OBS_UNIT_ID
AND OBS.UNIT_MODE='OBS_UNIT_AND_CHILDREN'
INNER JOIN ODF_MULTI_VALUED_LOOKUPS ML
ON ML.PK_ID = D.ID
AND ML.OBJECT = 'department'
AND ML.ATTRIBUTE ='attr_dep_man_***2'
WHERE (ML.VALUE = @WHERE:PARAM:USER_ID@)
) DEPMAN
ON OT.ATTR_SOBSU_V1 = DEPMAN.LINKED_UNIT_ID
INNER JOIN PRASSIGNMENT PA ON PA.PRTASKID = PT.PRID
INNER JOIN ODF_CA_ASSIGNMENT OASS ON PA.PRID = OASS.ID
LEFT OUTER JOIN ODF_CA_SUBOBJ_WBE WBE ON OASS.ATTR_WBE_ID = WBE.ID
INNER JOIN SRM_RESOURCES RES ON PA.PRRESOURCEID = RES.ID AND RES.FIRST_NAME 'Planet'
INNER JOIN PRJ_RESOURCES PRES ON RES.ID = PRES.PRID AND PRES.PRID 5029137 AND PRES.PRID 5033489
INNER JOIN ODF_CA_RESOURCE ORES ON RES.ID = ORES.ID AND ORES.ATTR_ISFAMILY_V1=0
LEFT OUTER JOIN CMN_LOOKUPS_V REG ON REG.LOOKUP_CODE = ORES.REGULAR_TEMPORARY AND REG.LOOKUP_TYPE ='REGULAR_TEMPORARY' AND REG.LANGUAGE_CODE='en'
INNER JOIN INV_INVESTMENTS INV ON PT.PRPROJECTID = INV.ID
INNER JOIN ODF_CA_PROJECT OPRJ ON OPRJ.ID = INV.ID
LEFT OUTER JOIN CMN_LOOKUPS_V PTP ON PTP.LOOKUP_CODE = OPRJ.ATTR_PRJTYPEPHASE AND PTP.LANGUAGE_CODE='en' AND PTP.LOOKUP_TYPE ='LK_PHASE'
LEFT OUTER JOIN PRJ_OBS_UNITS BU ON BU.ID = OPRJ.ATTR_BU_V1
LEFT OUTER JOIN PRJ_OBS_UNITS HUB ON HUB.ID = OPRJ.ATTR_HUB_V1
LEFT OUTER JOIN PRJ_OBS_UNITS OC ON OC.ID = OPRJ.ATTR_OC_V1
INNER JOIN CMN_SEC_USERS MGR ON OPRJ.ATTR_PM_V1 = MGR.ID
LEFT OUTER JOIN
(SELECT NVL(S.SLICE,0)*100 ESTSUM,
S.SLICE_DATE ,
A.PRID ASSIGN_ID
FROM ODF_SL_5020131 S,
PRASSIGNMENT A ,
ODF_CA_RESOURCE OCR
WHERE S.PRJ_OBJECT_ID = A.PRID
AND A.PRRESOURCEID = OCR.ID
AND OCR.ATTR_ISFAMILY_V1 = 0 ) ALLOC ON ALLOC.ASSIGN_ID = PA.PRID
WHERE (INV.ODF_OBJECT_CODE ='project')
AND (INV.IS_ACTIVE = 1)

UNION

SELECT
to_char(R.ID) ID,
OBS.ID DEPT_FILTER,
OBS.NAME DEPTNAME,
NULL,
NULL,
NULL,
R.UNIQUE_NAME RES_ID,
R.ID RES_FILTER,
R.FULL_NAME RES_NAME,
(CASE ORES.ATTR_PARTTIME
WHEN 1 THEN 'PT'
WHEN 0 THEN 'FT'
END) PART_TIME,
REG.NAME REG_TEMP,
PR.PRISOPEN CHARGEABLE,
R.DATE_OF_TERMINATION DATEOFTERM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'NO_PERIOD',
NULL,
NULL,
0 ASGN_TYPE,
(SELECT MAX(S.SLICE)
FROM PRJ_BLB_SLICES S
WHERE S.SLICE_REQUEST_ID=1
AND S.PRJ_OBJECT_ID =R.ID) AVAILIBILITY,
NULL ALLOC_PERC,
NULL,
NULL,
NULL,
NULL,
NULL
FROM
SRM_RESOURCES R,
ODF_CA_RESOURCE ORES,
PRJ_RESOURCES PR,
PRJ_OBS_ASSOCIATIONS ASSOCS,
PRJ_OBS_UNITS OBS,
CMN_LOOKUPS_V REG,
(SELECT DISTINCT OBS.LINKED_UNIT_ID
FROM OBS_UNITS_FLAT_BY_MODE OBS
INNER JOIN DEPARTMENTS D
ON OBS.UNIT_ID = D.OBS_UNIT_ID
AND OBS.UNIT_MODE = 'OBS_UNIT_AND_CHILDREN'
WHERE (
D.DEPARTMENT_MANAGER_ID = @WHERE:PARAM:USER_ID@
)

UNION

SELECT DISTINCT OBS.LINKED_UNIT_ID
FROM OBS_UNITS_FLAT_BY_MODE OBS
INNER JOIN DEPARTMENTS D
ON OBS.UNIT_ID = D.OBS_UNIT_ID
AND OBS.UNIT_MODE='OBS_UNIT_AND_CHILDREN'
INNER JOIN ODF_MULTI_VALUED_LOOKUPS ML
ON ML.PK_ID = D.ID
AND ML.OBJECT = 'department'
AND ML.ATTRIBUTE ='attr_dep_man_***2'
WHERE (ML.VALUE = @WHERE:PARAM:USER_ID@)
) DEPMAN
WHERE ORES.ID =R.ID
AND ORES.ATTR_ISFAMILY_V1 = 0
AND PR.PRID = R.ID
AND PR.PRISROLE =0
AND OBS.ID = ASSOCS.UNIT_ID
AND ASSOCS.RECORD_ID = R.ID
AND ASSOCS.TABLE_NAME='SRM_RESOURCES'
AND ASSOCS.UNIT_ID = DEPMAN.LINKED_UNIT_ID
AND REG.LOOKUP_CODE(+) = ORES.REGULAR_TEMPORARY
AND REG.LOOKUP_TYPE(+) ='REGULAR_TEMPORARY'
AND REG.LANGUAGE_CODE(+)='en'
AND R.ID NOT IN(SELECT DISTINCT PR.PRID FROM PRASSIGNMENT PA,
PRJ_RESOURCES PR,SRM_RESOURCES SR
WHERE
PR.PRID = PA.PRRESOURCEID AND SR.ID =PR.PRID
AND PR.PRISROLE =0
AND PR.PRID 5029137 AND PR.PRID 5033489 AND SR.FIRST_NAME 'Planet'))A

WHERE
@FILTER@
AND (A.PERIOD_START_DATE IS NULL OR @WHERE:PARAM:USER_DEF:DATE:A.PERIOD_START_DATE:PERIOD@)
order by A.PERIOD_NAME desc   
 

Outcomes