Exporting All global codes from P6 to Excel

1st from the report group I would run report CV03 Activity Code Value

then is that is not what you want you can see of the global codes but do not know which project is using any of them 

However there is a sql script to run to delte unused global activity codes

 Log into your P6 database as privileged user (ex: privuser):

b. Run below script

--------------------------------------------------------------GLOBAL ACTIVITY CODES--------------------------------
-Before deleting unused global actvitiy child code
SELECT count(*)
FROM actvcode
WHERE actv_code_type_id IN
  (SELECT actv_code_type_id FROM actvtype WHERE actv_code_type_scope='AS_Global'
  )
AND actv_code_id NOT IN
  (SELECT actv_code_id FROM taskactv
  );
  /

--Delete the unused global actvitiy child code
DELETE
FROM actvcode
WHERE actv_code_type_id IN
  (SELECT actv_code_type_id FROM actvtype WHERE actv_code_type_scope='AS_Global'
  )
AND actv_code_id NOT IN
  (SELECT actv_code_id FROM taskactv
  );
  /
 
  commit;
  /
 
--Display the count of unused global actvitiy child code after deletion
SELECT count(*)
FROM actvcode
WHERE actv_code_type_id IN
  (SELECT actv_code_type_id FROM actvtype WHERE actv_code_type_scope='AS_Global'
  )
AND actv_code_id NOT IN
  (SELECT actv_code_id FROM taskactv
  );
  /

You can click on the activity code value list, press CTRL+A to seelct all, then copy the full list from P6 and paste them into excel. But you'll need to do this one code at a time.

__Duplicate Post__

Zoltan,

How does that SQL Code handle Activity Codes that are held in a hierarchy style list?

If a parent code value is not directly assigned then it will not appear in the TASKACTV table & the SQL script will then delete the parent even if the child codes are used in some projects. 

Would you not also need to check that it does not appear in the parent_actv_code_id field of the ACTVCODE table?

Regards,

Steve

Thanks all,

I have found in P6 EPPm  Under the Administration>Enterprise Data there is an Import/Export Enterprise Data

This gives a deatioled report into excel for everything.

then just run the 1st part each part explains what it is doing

Hi Luke,

Glad to see you worked it out. Hope the help from the fellow forum members helped. I just wanted to comment on and let you know of two apps that might help your process after the export. 

• ScheduleReader - can help you preview the XER (or XML, XLS) file and easily see which cata is contained within. 
• ScheduleCleaner - can help you remove (clean) categories of project data. It also has a convert Global to Project data option for calendars and activity codes.

Both apps offer a 15-day free trial period.

Regards

here is another sql query to run to see what codes are not being used

SELECT PCATVAL.proj_catg_id AS CodeValue_id, PCATVAL.proj_catg_short_name AS Project_Code_Value_Name,PCATTYPE.proj_catg_type_id AS Code_id, PCATTYPE.proj_catg_type AS Project_Code_Name
FROM PCATVAL JOIN PCATTYPE ON ( PCATVAL.proj_catg_type_id =PCATTYPE.proj_catg_type_id)
WHERE PCATVAL.proj_catg_id NOT IN (SELECT DISTINCT PROJPCAT.proj_catg_id FROM PROJPCAT);