Connie_Fu

CA Clarity Tuesday Tip - Finding duplicate financial OBS associations

Discussion created by Connie_Fu on Apr 14, 2014
Latest reply on May 20, 2014 by navzjoshi00

In 13.2 we have enhanced the OBS component to make the financial OBS (department and location) configurable, with this enhancement we now have a new column in the PRJ_OBS_OBJECT_TYPES table, called ASSOC_ATT_CODE. Financial department OBS will have the value of odf_obs_fin_dept in this column, while financial location OBS does not.

With the enhancement OWB now checks to see if a project and its resources have duplicate department associations and if it does, will throw an error "The expected data type from the stream was different from what the stream contained"

We have seen several occurrences of a project or a resource being assocated to more than 1 department and/or location OBS, in some cases they could cause a project to not open, or throw system error on the General Properties page. Although we have not replicated this scenario, here are a couple of queries to help identify such duplicates:

For departments:

select count(poa.unit_id) rec_count, poa.record_id res_id
from prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot,
prj_obs_object_types poot, pac_mnt_resources pac, entity e
where NLS_UPPER(poa.table_name) = NLS_UPPER('SRM_RESOURCES')
and poa.unit_id = pou.id
and poa.record_id = pac.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and e.org_chart_obs_type_id = pou.type_id
and NLS_UPPER(poa.table_name) = NLS_UPPER(poot.table_name)
and poot.assoc_att_code = 'odf_obs_fin_dept'
and pac.departcode is not null
group by poa.record_id
having count(poa.unit_id) > 1

For locations:

select count(poa.unit_id) rec_count, poa.record_id res_id
from prj_obs_associations, poa, prj_obs_units pou, prj_obs_types pot,
prj_obs_object_types poot, pac_mnt_resources pac, entity e
where NLS_UPPER(poa.table_name) = NLS_UPPER('SRM_RESOURCES')
and poa.unit_id = pou.id
and poa.record_id = pac.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and e.geo_chart_obs_type_id = pou.type_id
and NLS_UPPER(poa.table_name) = NLS_UPPER(poot.table_name)
and poot.assoc_att_code is null
and pac.locationid is not null
group by poa.record_id
having count(poa.unit_id) > 1

 

To list the extra assocation record for department:

select poa.id
from prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot,
prj_obs_object_types poot, pac_mnt_resources pac, entity e
where NLS_UPPER(poa.table_name) = NLS_UPPER('SRM_RESOURCES')
and poa.unit_id = pou.id
and poa.record_id = pac.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and e.org_chart_obs_type_id = pou.type_id
and NLS_UPPER(poa.table_name) = NLS_UPPER(poot.table_name)
and pou.unique_name <> pac.departcode
and poot.assoc_att_code = 'odf_obs_fin_dept'
and pac.departcode is not null

To list the extra assocation record for location:

select poa.id
from prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot,
prj_obs_object_types poot, pac_mnt_resources pac, entity e
where NLS_UPPER(poa.table_name) = NLS_UPPER('SRM_RESOURCES')
and poa.unit_id = pou.id
and poa.record_id = pac.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and e.geo_chart_obs_type_id = pou.type_id
and NLS_UPPER(poa.table_name) = NLS_UPPER(poot.table_name)
and pou.unique_name <> pac.locationid
and poot.assoc_att_code is null
and pac.locationid is not null

Here's a query that shows the mismatch of department, a similar query can be worked out for location:

select poa.id as obs_assoc_id, poa.unit_id obs_unit_id, poa.record_id as
resource_id, pot.name, pot.description, pacres.departcode ResEntityOBS,
pou.unique_name ResDeptOBS
from prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot,
prj_obs_object_types poot, pac_mnt_resources pacres
where NLS_UPPER(poa.table_name) = NLS_UPPER('SRM_RESOURCES')
and poa.record_id in
(
select poa.record_id
from prj_obs_associations poa, prj_obs_units pou, prj_obs_types
pot, prj_obs_object_types poot
where NLS_UPPER(poa.table_name) = NLS_UPPER('SRM_RESOURCES')
and poa.unit_id = pou.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and NLS_UPPER(poa.table_name) = NLS_UPPER(poot.table_name)
and poot.assoc_att_code = 'odf_obs_fin_dept'
GROUP BY poa.record_id
having count(poa.unit_id) > 1
)
and poa.unit_id = pou.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and NLS_UPPER(poa.table_name) = NLS_UPPER(poot.table_name)
and poot.assoc_att_code = 'odf_obs_fin_dept'
and poa.record_id = pacres.id
and pacres.departcode <> pou.unique_name
order by resource_id

If you can help us identify the steps to reproduce duplicate assocation records, and/or if you have found some and would like to clean them up, we would like to hear from you - please submit a Support case

Outcomes