Copy Blueprint Fails with PersistenceApplicationException and ORA-01722
search cancel

Copy Blueprint Fails with PersistenceApplicationException and ORA-01722

book

Article ID: 259717

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

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

Environment

Release : 16.1.2

Cause

XXXXXXXXXXXX UTC:10.3.109.197(55036):niku@niku:[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, action_associations.id 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  object.id AS id,
                       object.blp_name AS name
                                             from (
               select blp.id, abs_blp.name as blp_name from
               ODF_ABSTRACT_BLUEPRINTS abs_blp, ODF_BLUEPRINTS blp
               where abs_blp.id= blp.id and blp.is_private = 0 and blp.is_system = 0
               ) object
               WHERE   1=$5 and 1=1 and 1=1) q_blueprint_id where q_blueprint_id.id = action_associations.BLUEPRINT_ID limit 1 ) blueprint_id_caption, action_associations.PROCESS_VERSION_ID as process_version_id, (select id from ( SELECT ccn.name AS process_name,
                      bdpv.id AS id,
                      bdp.process_code AS process_code,
                      bdo.object_type_code AS object_type_code,
                      ccn_object.name 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_PROCESS_VERSIONS bdpv LEFT OUTER JOIN
                      BPM_DEF_OBJECTS bdo
                   ON bdo.pk_id = bdpv.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 = oo.id
                  AND ccn_object.language_code = $6
               WHERE ccn.pk_id = bdp.id
                  AND ccn.table_name = 'BPM_DEF_PROCESSES'
                  AND bdpv.process_id = bdp.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 = ea.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 av.id AS id,
               av.id AS unique_code,
               av.name AS label
               FROM
                  blp_available_visuals av
                     WHERE
                       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 q_location.id = 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 bpv.id=ACTION_ASSOCIATIONS.PROCESS_VERSION_ID) 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 = action_associations.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

Resolution

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.