Outline Code Table

[CQ]

It was suggested to me that since this is really more of a reporting type
question maybe I should post it here as my original concern is not the real
issue.

Here it is: Somehow, on our system, we have three Enterprise Global
Templates.
Everything is working normally and in the table, one of them clearly is
marked as "inactivated enterprise template" and another one is the active
template (I have no idea about the third one - it doesn't show up that i
can find). Everything seems to work fine. EXCEPT - reporting.
I have a number of reports that use the "Outline_Codes" table, and when
I link to it, it comes up with three results for every task or every code
field or whatever
I link to it.

Here is an example, I have a custom field with ID of 188744589 and with 6
unique values. So, when I run a report and link to it I would expect that
for one single schedule, I would get one of the 6 possible options - but I
actually get 3 of the same result back. When I go into the table itself, and
I do a search on that ID, I would expect to get back a total of 6 rows...but
I get 18 (6 rows times 3 templates). I get this:
PROJ_ID OC_FIELD_ID OC_NAME
752 188744589 Active
752 188744589 Cancelled
752 188744589 Completed
752 188744589 On Hold
752 188744589 Proposed
752 188744589 New
755 188744589 Active
755 188744589 Cancelled
755 188744589 Completed
755 188744589 On Hold
755 188744589 Proposed
755 188744589 New
756 188744589 Active
756 188744589 Cancelled
756 188744589 Completed
756 188744589 On Hold
756 188744589 Proposed
756 188744589 New

ANy thoughts?

 

[Guidho]

In the MSP_OUTLINE_CODES table, multiple version of the Enterprise Global are
stored (normally 2).

So when you link/join to the MSP_OUTLINE_CODES table, you need to make sure
you filter out all old Enterprise Global values and only keep the current
Enterprise Global values.

You can do this by adding the following code to your join clause:

AND PROJ_ID = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_TYPE = 2)

PROJ_TYPE = 2 is the current enterprise global.

This should work.
Good luck!

Guidho