Copy Blueprint Fails with PersistenceApplicationException and ORA-01722
search cancel

Copy Blueprint Fails with PersistenceApplicationException and ORA-01722


Article ID: 259717


Updated On:


Clarity PPM SaaS


Copy blueprint is failing in 16.1.2/PostreSQL com.niku.union.odf.exception.ODFException:com.niku.union.persistence.PersistenceApplicationException

And on Oracle systems with ORA-01722: invalid number in the logs


Release : 16.1.2


XXXXXXXXXXXX UTC:[23209]:STATEMENT:  select odf_q.*  from ( select row_number() over (  order by odf_pk) odf_row_num, count(*) over (partition by pmd_analytical_partition_by) odf_num_rows , odf_cols.* from (  select   'x' pmd_analytical_partition_by, odf_pk, action_associations.PLACEMENT_TYPE COLLATE "en-x-icu"  as placement_type, (select NAME from cmn_lookups_v where language_code = $1 and lookup_type = $2 and LOOKUP_CODE::text = action_associations.PLACEMENT_TYPE::text limit 1) as placement_type_caption, action_associations.IS_SYSTEM as is_system, action_associations.CODE COLLATE "en-x-icu"  as code, action_associations.IS_ACTIVE as is_active, action_associations.ACTION_TYPE COLLATE "en-x-icu"  as action_type, (select NAME from cmn_lookups_v where language_code = $3 and lookup_type = $4 and LOOKUP_CODE::text = action_associations.ACTION_TYPE::text limit 1) as action_type_caption, action_associations.ACTION_CODE COLLATE "en-x-icu"  as action_code, action_associations.DESCRIPTION COLLATE "en-x-icu"  as description, CREATED.FULL_NAME COLLATE "en-x-icu"  as created_by, action_associations.BLUEPRINT_ID as blueprint_id, (select name from ( SELECT AS id,
                       object.blp_name AS name
                                             from (
               select, as blp_name from
               where and blp.is_private = 0 and blp.is_system = 0
               ) object
               WHERE   1=$5 and 1=1 and 1=1) q_blueprint_id where = action_associations.BLUEPRINT_ID limit 1 ) blueprint_id_caption, action_associations.PROCESS_VERSION_ID as process_version_id, (select id from ( SELECT AS process_name,
             AS id,
                      bdp.process_code AS process_code,
                      bdo.object_type_code AS object_type_code,
             AS object_name,
                                               ccn.LAST_UPDATED_DATE AS LAST_UPDATED_DATE,
                                               l.ID AS LANGUAGE_ID,
                                               ccn.LANGUAGE_CODE AS LANGUAGE_CODE
                 FROM CMN_CAPTIONS_NLS ccn
                    , BPM_DEF_PROCESSES bdp
                              , CMN_LANGUAGES l
                      BPM_DEF_OBJECTS bdo
                   ON bdo.pk_id =
                  AND bdo.table_name = 'BPM_DEF_PROCESS_VERSIONS'
                  AND bdo.manual_start = 1
                  AND bdo.is_visible = 1
                  AND bdo.is_system = 0 LEFT OUTER JOIN
                      ODF_OBJECTS oo
                   ON oo.code = bdo.object_type_code LEFT OUTER JOIN
                      CMN_CAPTIONS_NLS ccn_object
                   ON ccn_object.table_name = 'ODF_OBJECTS'
                  AND ccn_object.pk_id =
                  AND ccn_object.language_code = $6
               WHERE ccn.pk_id =
                  AND ccn.table_name = 'BPM_DEF_PROCESSES'
                  AND bdpv.process_id =
                  AND bdpv.USER_STATUS_CODE = 'BPM_PUS_ACTIVE'
                  AND ccn.language_code = $7
                  AND ccn.LANGUAGE_CODE = l.LANGUAGE_CODE
                 AND 1=$8 and 1=1 and 1=1) q_process_version_id where q_process_version_id.process_name = action_associations.PROCESS_VERSION_ID limit 1 ) process_version_id_caption, action_associations.SEQUENCE as sequence, (select ea.attr_api_alias from
                               blp_visual_enabled_attrs ea,
                               blp_available_visuals av
                               WHERE  av.visual_category = 'MODULE'
                               and av.visual_enabled_attr_id =
                              and av.ID =ACTION_ASSOCIATIONS.MODULE_ID) COLLATE "en-x-icu"  as location_code, CAPTIONS.NAME COLLATE "en-x-icu"  as name, UPDATED.FULL_NAME COLLATE "en-x-icu"  as updated_by, action_associations.MODULE_ID as location, (select label from ( SELECT AS id,
      AS unique_code,
      AS label
                  blp_available_visuals av
                       av.blueprint_type = (select type from odf_blueprints where $9 = id )
                       and av.visual_category = 'MODULE'
                       and av.visual_enabled_attr_id in (select id from  blp_visual_enabled_attrs ea where ea.attr_api_alias in ('details', 'staff'))
                                            and 1=$10 and 1=1 and 1=1) q_location where = action_associations.MODULE_ID limit 1 ) location_caption, ACTION_ASSOCIATIONS.CREATED_DATE as created_date, ACTION_ASSOCIATIONS.LAST_UPDATED_DATE as last_updated_date, (select case when user_status_code='BPM_PUS_ACTIVE' then 'Active' when user_status_code='BPM_PUS_DRAFT' then 'Draft'
                               when user_status_code='BPM_PUS_ON_HOLD' then 'Hold' end status from BPM_DEF_PROCESS_VERSIONS bpv where COLLATE "en-x-icu"  as status  
                                                                           from action_associations action_associations
                                                                           JOIN srm_resources CREATED ON CREATED.user_id=action_associations.created_by
                                                                           JOIN srm_resources UPDATED ON UPDATED.user_id=action_associations.last_updated_by
                                                                           JOIN cmn_captions_nls CAPTIONS ON CAPTIONS.pk_id = and CAPTIONS.table_name = 'ACTION_ASSOCIATIONS' and CAPTIONS.language_code = $11 
                                                                           where 1 = 1 and 1=1  and action_associations.BLUEPRINT_ID = $12   ) odf_cols ) odf_q   
                                                                           where odf_q.odf_row_num between $13 and $14 order by odf_q.odf_row_num


This is Caused by an invalid lookup mapping. The lookup "Active and Validated Processes" should have hidden key as "ID", in this case the hidden key was "Process Name". After correcting the lookup using XOG, the issue is resolved.