Hi Everyone,
I have written the following NSQL code for a Milestone Reporting portlet, however, the security construct is not working, can I get some help on what I might be coding incorrectly?
CODE:
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:MST:ROWNUM:uq_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."ID":id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project Name":Project_Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Task Name":Task_Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery.externalid:externalid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project ID":project_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Audit ID":audit_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."CSOC ID":csoc_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Tech Risk ID":tech_risk_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Systems Pillar Sponsor":systems_pillar_sponsor@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project Manager":project_manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Task Owner":task_owner@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."OM_ID":OM_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Business Lead":business_lead@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."BL_ID":BL_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Business Owner":business_owner@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."BO_ID":BO_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Start":start_Date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Finish":finish_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Baseline Start":baseline_start@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Baseline Finish":baseline_finish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."RAG Status":rag_status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status Report Date":status_report_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."ST_ID":ST_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status":status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status Comment":status_comment@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.prismilestone:milestone@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.priskey:key_task@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.is_active:is_active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Product":product@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Sub Product":sub_product@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Strategic Investment Category":strategic_investment_category@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery.hg_has_children:hg_has_children@
FROM(
SELECT P.UNIQUE_NAME "Project ID",
P.ID "ID",
CP.AUDIT_ID "Audit ID",
CP.CSOC_ID "CSOC ID",
CP.TECHNOLOGY_RISK_ID "Tech Risk ID",
P.NAME "Project Name",
NULL "Task Name",
P.ID HG_HAS_CHILDREN,
SPS.NAME "Systems Pillar Sponsor",
RPM.FULL_NAME "Project Manager",
NULL "Task Owner",
RPM.ID "OM_ID",
RBL.FULL_NAME "Business Lead",
RBL.ID "BL_ID",
RBO.FULL_NAME "Business Owner",
RBO.ID "BO_ID",
PR.PRSTART "Start",
PR.PRFINISH "Finish",
PR.PRBASESTART "Baseline Start",
PR.PRBASEFINISH "Baseline Finish",
CASE WHEN ST.OVERALL > 0 THEN ST.OVERALL ELSE 0 END "RAG Status",
TO_CHAR(ST.COP_REPORT_DATE, 'MM/DD/YYYY') "Status Report Date",
PR.PROGRESS "ST_ID",
DECODE(PR.PROGRESS,
0,
'Not Started',
1,
'Started',
2,
'Completed') "Status",
ST.COP_REPORT_UPDATE "Status Comment",
0 PRISMILESTONE,
0 PRISKEY,
cast(P.IS_Active as Integer) is_active,
PRO.NAME "Product",
SUBPR.NAME "Sub Product",
CP.BBH_STRATEGICINVCATE "Strategic Investment Category",
P.UNIQUE_NAME EXTERNALID
FROM NIKU.PRJ_PROJECTS PR,
NIKU.SRM_PROJECTS P,
NIKU.ODF_CA_PROJECT CP,
NIKU.SRM_RESOURCES RBO,
NIKU.SRM_RESOURCES RPM,
NIKU.SRM_RESOURCES RBL,
NIKU.ODF_CA_INV SP,
(SELECT ODF.ODF_PARENT_ID,
ODF.COP_REPORT_DATE,
ODF.COP_REPORT_UPDATE,
(ODF.COP_SCHEDULE_STATUS + ODF.COP_SCOPE_STATUS +
ODF.COP_COST_EFT_STATUS) OVERALL
FROM NIKU.ODF_CA_COP_PRJ_STATUSRPT ODF,
(SELECT OD.ODF_PARENT_ID, MAX(OD.ID) "ID"
FROM NIKU.ODF_CA_COP_PRJ_STATUSRPT OD
WHERE OD.COP_REPORT_STATUS = 'FINAL'
AND OD.COP_REPORT_DATE >=
ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)
GROUP BY OD.ODF_PARENT_ID) LU
WHERE LU.ID = ODF.ID) ST,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY4') SPS,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY1') PRO,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY2') SUBPR
WHERE P.ID = PR.PRID
AND CP.ID = P.ID(+)
AND CP.OBJ_STAKEHOLDER1 = RBO.ID(+)
AND RPM.USER_ID = PR.MANAGER_ID(+)
AND CP.BBH_BUSINESSLEAD = RBL.ID(+)
AND P.ID = SP.ID(+)
AND SP.OBJ_PFL_CATEGORY4 = SPS.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY1 = PRO.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY2 = SUBPR.LOOKUP_CODE(+)
AND P.ID = ST.ODF_PARENT_ID(+)
AND (PR.PRCLOSEDTIME IS NULL OR PR.PRCLOSEDTIME >= '01-JAN-17')
AND P.IS_TEMPLATE = 0
AND P.IS_PROGRAM = 0
AND PR.PRUSERTEXT1 NOT LIKE '%HARD%'
AND PR.PRUSERTEXT1 NOT LIKE '%ROUTE%'
AND @WHERE:PARAM:user_def:STRING:hg_row_id@ IS NULL
UNION
SELECT P.UNIQUE_NAME "Project ID",
T.PRID "ID",
CP.AUDIT_ID "Audit ID",
CP.CSOC_ID "CSOC ID",
CP.TECHNOLOGY_RISK_ID "Tech Risk ID",
P.NAME "Project Name",
T.PRNAME "Task Name",
NULL HG_HAS_CHILDREN,
NULL "Systems Pillar Sponsor",
NULL "Project Manager",
RTO.FULL_NAME "Task Owner",
RTO.ID "OM_ID",
NULL "Business Lead",
NULL "BL_ID",
NULL "Business Owner",
NULL "BO_ID",
T.PRSTART "Start",
T.PRFINISH "Finish",
BA.START_DATE "Baseline Start",
BA.FINISH_DATE "Baseline Finish",
CAST(CT.MILESTONE_STATUS AS INTEGER) "RAG Status",
TO_CHAR(SYSDATE, 'MM/DD/YYYY') "Status Report Date",
T.PRSTATUS "ST_ID",
DECODE(T.PRSTATUS,
0,
'Not Started',
1,
'Started',
2,
'Completed') "Status",
T.PRUSERTEXT1 "Status Comment",
T.PRISMILESTONE,
T.PRISKEY,
cast(P.IS_ACTIVE as Integer) is_active,
PRO.NAME "Product",
SUBPR.NAME "Sub Product",
CP.BBH_STRATEGICINVCATE "Strategic Investment Category",
T.PREXTERNALID EXTERNALID
FROM NIKU.PRTASK T,
NIKU.ODF_CA_TASK CT,
NIKU.SRM_RESOURCES RTO,
NIKU.SRM_PROJECTS P,
NIKU.PRJ_PROJECTS PR,
NIKU.ODF_CA_PROJECT CP,
NIKU.ODF_CA_INV SP,
NIKU.SRM_RESOURCES RBO,
NIKU.SRM_RESOURCES RBL,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY4') SPS,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY1') PRO,
(SELECT CMN.LOOKUP_CODE, CMN.NAME
FROM NIKU.CMN_LOOKUPS_V CMN
WHERE CMN.LANGUAGE_CODE = 'en'
AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY2') SUBPR,
(SELECT B.OBJECT_ID, B.START_DATE, B.FINISH_DATE
FROM NIKU.PRJ_BASELINE_DETAILS B
WHERE B.OBJECT_TYPE = 'TASK') BA
WHERE T.PRID = CT.ID
AND T.TASK_OWNER = RTO.ID(+)
AND T.PRISTASK = 1
AND (T.PRISKEY = 1 OR T.PRISMILESTONE = 1)
AND P.ID = T.PRPROJECTID
AND CP.ID = P.ID(+)
AND P.ID = PR.PRID
AND CP.BBH_BUSINESSLEAD = RBL.ID(+)
AND CP.OBJ_STAKEHOLDER1 = RBO.ID(+)
AND SP.ID = P.ID(+)
AND SP.OBJ_PFL_CATEGORY4 = SPS.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY1 = PRO.LOOKUP_CODE(+)
AND SP.OBJ_PFL_CATEGORY2 = SUBPR.LOOKUP_CODE(+)
AND T.PRID = BA.OBJECT_ID(+)
AND (PR.PRCLOSEDTIME IS NULL OR PR.PRCLOSEDTIME >= '01-JAN-17')
AND P.IS_TEMPLATE = 0
AND P.IS_PROGRAM = 0
AND PR.PRUSERTEXT1 NOT LIKE '%HARD%'
AND PR.PRUSERTEXT1 NOT LIKE '%ROUTE%'
AND (t.PRPROJECTID = @WHERE:PARAM:user_def:STRING:hg_row_id@ OR
@WHERE:PARAM:USER_DEF:STRING:hg_all_rows@ = 1)
) hgquery
where (
@WHERE:PARAM:USER_DEF:INTEGER:OBS@ IS NULL
OR
(
SELECT DISTINCT pou.id AS prj_unit_id
FROM prj_obs_associations poa
JOIN prj_obs_units pou ON pou.ID = poa.unit_id
WHERE poa.table_name = 'SRM_PROJECTS'
AND poa.record_id = hgquery."ID"
AND pou.type_id = (SELECT DISTINCT P.type_id
FROM prj_obs_units P
WHERE P.id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@ )
)
IN
(
SELECT DISTINCT F.unit_id
FROM prj_obs_units_flat F
WHERE branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@
)
)
AND hgquery."ID" = @NVL@(@WHERE:PARAM:XML:INTEGER:/data/id/@value@,hgquery."ID")
AND @WHERE:SECURITY:PROJECT:SRM_PROJECTS.ID@
AND @filter@