Clarity

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

Thanks for the heads-up, Connie

NJ

Connie_Fu:

In 13.3 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

Thanks for sharing this with the community Connie!

Thanks for the knowledge Connie...

"In 13.3 we have enhanced the OBS component to make the financial OBS (department and location) configurable"

Is there documentation on the updated configurability of the financial OBS?  I didn't see anything in the 13.3 Change Impact and Upgrade Guide nor mention of the table change.  Thanks

Hi GemrichA

My bad it is 13.2 not 13.3, I have attached the Change Impact and Upgrade guide for 13.2 here

Thanks for catching that!

Connie

While we have not identify the exact steps to recreate the duplicate association records for resources, we have identified steps for Investments and are tracking it as CLRT-74529 which is currently being worked on with not yet a target fixed version determined. Our teams are looking into both the functional and technical aspects with the new Configurable OBS feature to make sure we deliver a good fix.

Here are the steps

1) Create department OBS and location OBS with one level. Say dobs_1, lobs_1.
2) Associate both OBS with the Idea and Project objects
3) Create an entity with the above OBS. Say entity_1.
4) Create one more set of department and location obs, associate only with the Project object. Say dobs_2, lobs_2.
5) Create entity. Say entity_2, using dobs_2 and lobs_2
6) Create department and locations for both entities and associate them. Say department_1, location_1 and department_2, location_2
7) Create an idea and go to financial page, assign department and location of entity_2 i.e. department_2, location_2 and click Save. No error.

(I should not be able to select department_2 and location_2 because the OBS they are created on are not associated with the Idea object)

8) Go to the Main Properties page of the idea. Observe that 'Department OBS' field is blank (because OBS dobs_2 and lobs_2 are not associated with the Idea object)
9) Update any field on the Main Properties page and click on save.
10) Go back to financial page and see that the department field is blank. Assign the same department again, click on save.

Expected Result : update is successful.
Actual result : System error. Contact system administrator.

Up to this point we don't have duplicates in PRJ_OBS_ASSOCIATIONS table. To get the duplicates continue with the steps below:

11) Go to financial page again, this time assign the department as department_1 (whose OBS **IS** associated to the Idea object).
12) Click on save. Save is successful.
13) Go back to properties page, you would see that the 'Department OBS' field is populated with the correct department "department_1" as expected.
14) Go to Administration -> OBS, select the OBS #2 created in step 4. Associate them to the Idea object. (Previously they were associated only with the Project object.)
15) Go to Home > Ideas, try to open the idea that was created in step 7.

Expected Result : The idea is open without any error.
Actual result : System error. Contact system administrator (You can't even open the idea properties page).

Ideas (as with all investments) are stored in pac_mnt_projects and this table doesn't have entity references as does pac_mnt_resources, so the query would be a little different but not too far off.

select inv.code, poa.unit_id, pou.name, pot.unique_name
from prj_obs_associations poa, inv_investments inv, prj_obs_units pou, prj_obs_types pot, prj_obs_object_types poot, entity e
where poa.table_name = 'INV_IDEA'
and poa.unit_id = pou.id
and pou.type_id = pot.id
and pot.id = poot.type_id
and inv.id = poa.record_id
and e.org_chart_obs_type_id = pou.type_id
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 poa.unit_id = pou.id
AND upper(poa.table_name) = 'INV_IDEA'
AND pou.type_id = pot.id
AND pot.id = poot.type_id
AND upper(poa.table_name) = upper(poot.table_name)
AND poot.assoc_att_code = 'odf_obs_fin_dept'
GROUP BY poa.record_id HAVING COUNT(poa.record_id)>1)
group by inv.code, poa.unit_id, pou.name, pot.unique_name

The real challenge is on "how to systematically identify and remove the extra obs association" without the pac_mnt_projects table telling us which department to keep. If you find that you have run into this scenario, open a Support ticket and have your data examined for data cleanup; if you can come up with a more efficient query please share with us here! :D

Thanks for sharing this, Connie

NJ