AnsweredAssumed Answered

Error with Power Filter

Question asked by fabricio.de.marchi on Jul 20, 2011
Latest reply on Aug 23, 2011 by Chris_Hackett
Hi,

We are having issues with Power Filter defined in a Portfolio.

In our production environment that is working fine, in our non-prod environment this is not working.

We have the following power filter for Projects in a specific Portfolio:
project.is_active == 1 && project.is_template == 0 && project.is_program == 1 && project.schedule_finish >= date("2011-01-29T00:00:00") && project.schedule_start <= date("2012-02-03T00:00:00") && ( obs("project",5002097,"5002097","EQUALS","selected_unit_and_below", ctx, project) ) && project.dell_it_is_bus_prgm == 1

That power filter is evaluated successfuly but when we ask to synchronize the Portfolio projects this is the error we get in the log file:
ORA-01861: literal does not match format string

And once I check the SQL generated by Clarity when trying to do the Synch using the power filter this is the piece that is causing this issue:
trunc(to_date('2012-02-03 00:00:00'))

I have also tried connecting to the same database using a SQL client and run the following query:
select trunc(to_date('2012-02-03 00:00:00')) from dual

I get the same error.

Can you detail why is this happening and how can I fix it? I know there is nothing I need to change in the Power Filter since it dies work in the production environment so I am assuming it is something related to the database.

here the SQL related to the error message:
SELECT PROJECTS.CANDIDATE_ID,
        PORTCONTENTS.CONTENTS_INVEST_ID
        FROM
        ( SELECT
        ODF_PK CANDIDATE_ID
        FROM
        ODF_PROJECT_V2
        WHERE 1=1 AND ( (  (  (  (  (  (  ( ODF_PROJECT_V2.IS_ACTIVE = 1 )  and  ( ODF_PROJECT_V2.IS_TEMPLATE = 0 )  )  and  ( ODF_PROJECT_V2.IS_PROGRAM = 1 )  )  and  ( trunc(ODF_PROJECT_V2.schedule_finish) >= trunc(to_date('2011-01-29 00:00:00')) )  )  and  ( trunc(ODF_PROJECT_V2.schedule_start) <= trunc(to_date('2012-02-03 00:00:00')) )  )  and  ODF_PROJECT_V2.ODF_PK in ( select record_id from prj_obs_associations poa, obs_units_flat_by_mode ouf where poa.table_name = 'SRM_PROJECTS' and poa.unit_id = ouf.linked_unit_id and ouf.unit_mode = 'OBS_UNIT_AND_CHILDREN' and ouf.unit_id = 5002097)  )  and  ( NLS_UPPER(ODF_PROJECT_V2.dell_inv_cat) = NLS_UPPER('Master Program') )  ) )
        ) PROJECTS,
        ( SELECT
        INVEST_ID CONTENTS_INVEST_ID
        FROM
        PMA_PORTFOLIO_CONTENTS
        WHERE PORTFOLIO_ID = ?
        AND INVEST_TYPE = ?
        AND IS_ADDED_BY_EXPRESSION != 0
        ) PORTCONTENTS
        WHERE PROJECTS.CANDIDATE_ID = PORTCONTENTS.CONTENTS_INVEST_ID(+)
           AND 1 =
             CASE
               WHEN ( SELECT department_id
                        FROM pma_portfolios p
                       WHERE p.ID = ? ) IS NOT NULL
                 THEN
                   CASE ( SELECT portfolio_type
                            FROM pma_portfolios p
                           WHERE p.ID = ? )
                     WHEN ?
                       THEN ( SELECT 1
                                FROM DUAL
                               WHERE ( EXISTS (
                                         SELECT investment_id
                                           FROM cbk_gl_allocation a,
                                                      cbk_gl_allocation_detail ad
                                          WHERE ad.gl_allocation_id = a.ID
                                            AND a.investment_id = projects.candidate_id
                                            AND ad.department_id = (
                                                        SELECT department_id
                                                          FROM pma_portfolios
                                                         WHERE id = ?
                                                      )
                                            AND a.status_code = (
                                                        SELECT ID
                                                          FROM cmn_lookups
                                                         WHERE lookup_type = ?
                                                           AND lookup_code = ?
                                                      )
                                            AND a.chargeback_type = ?
                                            AND a.investment_id IS NOT NULL
                                            AND a.prchargecode_id IS NULL
                                            AND a.prtypecode_id IS NULL
                                            AND a.resource_class_id IS NULL
                                            AND a.utility_code_1 IS NULL
                                            AND a.utility_code_2 IS NULL
                                       )
                                     )
                            )
                     WHEN ?
                       THEN ( SELECT 1
                                FROM DUAL
                               WHERE projects.candidate_id IN (
                                       SELECT prj_obs_associations.record_id
                                         FROM inv_investments,
                                              prj_obs_associations,
                                              departments,
                                              pma_portfolios
                                        WHERE pma_portfolios.id = ?
                                          AND pma_portfolios.department_id = departments.id
                                          AND departments.obs_unit_id = prj_obs_associations.unit_id
                                          AND inv_investments.ID = prj_obs_associations.record_id
                                     )
                            )
                     ELSE 1
                   END
               ELSE 1
             END
        AND (EXISTS (SELECT USER_ID
        FROM cmn_sec_chk_user_r_v
        WHERE USER_ID = ?
        AND COMPONENT_CODE in ('INV', 'PRJ')
        AND OBJECT_CODE in
          (select s.object_code
          from odf_object_extensions oe
               , odf_objects o
               , cmn_sec_objects s
          where o.code = oe.object_code
          and oe.extension_code = 'inv'
          and s.object_type_code = 'RECORD'
          and s.object_code = (case when o.is_custom = 0 then 'INV_' || NLS_UPPER(oe.object_code) else 'odf_cst_' || oe.object_code end)
          union
          select s.object_code
          from cmn_sec_objects s
          where s.object_code='SRM_PROJECTS'
          and s.object_type_code='RECORD')
        AND PERMISSION_CODE='READ'
        AND OBJECT_TYPE='RECORD')
        OR
        PROJECTS.CANDIDATE_ID IN
        (SELECT OBJECT_INSTANCE_ID
        FROM CMN_SEC_CHK_USER_V
        WHERE USER_ID = ?
        AND COMPONENT_CODE in ('INV','PRJ')
        AND OBJECT_CODE in
        (select s.object_code
        from odf_object_extensions oe
             , odf_objects o
             , cmn_sec_objects s
        where o.code = oe.object_code
        and oe.extension_code = 'inv'
        and s.object_type_code = 'RECORD'
        and s.object_code = (case when o.is_custom = 0 then 'INV_' || NLS_UPPER(oe.object_code) else 'odf_cst_' || oe.object_code end)
        union
        select s.object_code
        from cmn_sec_objects s
        where s.object_code='SRM_PROJECTS'
        and s.object_type_code='RECORD')
        AND PERMISSION_CODE='READ'
        AND OBJECT_TYPE='RECORD')
        )
        AND PROJECTS.CANDIDATE_ID NOT IN
        ( SELECT
        INVEST_ID CONTENTS_INVEST_ID
        FROM
        PMA_PORTFOLIO_CONTENTS
        WHERE PORTFOLIO_ID = ?
        AND IS_ADDED_BY_EXPRESSION = 0 )
        AND (PROJECTS.CANDIDATE_ID IS NULL OR PORTCONTENTS.CONTENTS_INVEST_ID IS NULL)
        UNION
        SELECT PROJECTS.CANDIDATE_ID,
        PORTCONTENTS.CONTENTS_INVEST_ID
        FROM
        ( SELECT
        ODF_PK CANDIDATE_ID
        FROM
        ODF_PROJECT_V2
        WHERE 1=1 AND ( (  (  (  (  (  (  ( ODF_PROJECT_V2.IS_ACTIVE = 1 )  and  ( ODF_PROJECT_V2.IS_TEMPLATE = 0 )  )  and  ( ODF_PROJECT_V2.IS_PROGRAM = 1 )  )  and  ( trunc(ODF_PROJECT_V2.schedule_finish) >= trunc(to_date('2011-01-29 00:00:00')) )  )  and  ( trunc(ODF_PROJECT_V2.schedule_start) <= trunc(to_date('2012-02-03 00:00:00')) )  )  and  ODF_PROJECT_V2.ODF_PK in ( select record_id from prj_obs_associations poa, obs_units_flat_by_mode ouf where poa.table_name = 'SRM_PROJECTS' and poa.unit_id = ouf.linked_unit_id and ouf.unit_mode = 'OBS_UNIT_AND_CHILDREN' and ouf.unit_id = 5002097)  )  and  ( NLS_UPPER(ODF_PROJECT_V2.dell_inv_cat) = NLS_UPPER('Master Program') )  ) )
        ) PROJECTS,
        ( SELECT
        INVEST_ID CONTENTS_INVEST_ID
        FROM
        PMA_PORTFOLIO_CONTENTS
        WHERE PORTFOLIO_ID = ?
        AND INVEST_TYPE = ?
        AND IS_ADDED_BY_EXPRESSION != 0
        ) PORTCONTENTS
        WHERE PORTCONTENTS.CONTENTS_INVEST_ID = PROJECTS.CANDIDATE_ID(+)
        AND (EXISTS (SELECT USER_ID
        FROM cmn_sec_chk_user_r_v
        WHERE USER_ID = ?
        AND COMPONENT_CODE in ('INV','PRJ')
        AND OBJECT_CODE in
        (select s.object_code
        from odf_object_extensions oe
             , odf_objects o
             , cmn_sec_objects s
        where o.code = oe.object_code
        and oe.extension_code = 'inv'
        and s.object_type_code = 'RECORD'
        and s.object_code = (case when o.is_custom = 0 then 'INV_' || NLS_UPPER(oe.object_code) else 'odf_cst_' || oe.object_code end)
        union
        select s.object_code
        from cmn_sec_objects s
        where s.object_code='SRM_PROJECTS'
        and s.object_type_code='RECORD')
        AND PERMISSION_CODE='READ'
        AND OBJECT_TYPE='RECORD')
        OR
        PORTCONTENTS.CONTENTS_INVEST_ID IN
        (SELECT OBJECT_INSTANCE_ID
        FROM CMN_SEC_CHK_USER_V
        WHERE USER_ID = ?
        AND COMPONENT_CODE in ('INV', 'PRJ')
        AND OBJECT_CODE in
        (select s.object_code
        from odf_object_extensions oe
             , odf_objects o
             , cmn_sec_objects s
        where o.code = oe.object_code
        and oe.extension_code = 'inv'
        and s.object_type_code = 'RECORD'
        and s.object_code = (case when o.is_custom = 0 then 'INV_' || NLS_UPPER(oe.object_code) else 'odf_cst_' || oe.object_code end)
        union
        select s.object_code
        from cmn_sec_objects s
        where s.object_code='SRM_PROJECTS'
        and s.object_type_code='RECORD')
        AND PERMISSION_CODE='READ'
        AND OBJECT_TYPE='RECORD')
        )
        AND (PROJECTS.CANDIDATE_ID IS NULL OR PORTCONTENTS.CONTENTS_INVEST_ID IS NULL) 

Outcomes