Copy Blueprint Fails with PersistenceApplicationException and ORA-01722
Article ID: 259717
Updated On:
Products
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.
Feedback
