Project and Portfolio Management Practitioners Forum
cancel

PPM Fields to Database mapping

Highlighted
ppm123
New Member.

PPM Fields to Database mapping

Hi,

 

it would be great if someone can help or give information about the User-interface Fields of PPM and the corresponding Database Tables and Columns.

 

What I am looking for is a document or somthing which tells me - for example:

 

GUI Field "Project Name"   is in the Database in this Tables in this Columns.

 

I would need for all possible GUI Fields, textboxes, dropdowns etc.

Does anyone have information about that ?

 

Any help or information is welcome.

 

THANKS!

 

6 REPLIES
brian_jobes
New Member.

Re: PPM Fields to Database mapping

Hi, what you are requesting doesn't quite exist as you describe. You are going to have to roll up your sleeves and do some investigation to become informed on this one.

 

I can assist some. I'm attaching the data model guide for 9.12, which is pretty consistent for versions 8x and above for the entities you relate interest in.

 

Pay close attention to kcrt_requests, pm_projects tables for the information you require. Also there's a kcrt_requests_v view with more expanded information.

 

      -Brian

Rich Y_1
Super Contributor.

Re: PPM Fields to Database mapping

For request types I use the SQL below to map the fields.  Hope it helps

 

select unique *
from(
select v.request_type_name,
hdt.request_header_type_name header,
case
when ps.parameter_table_name is null and psc.context_value = to_char(v.request_header_type_id) then 'KCRT_REQ_HEADER_DETAILS'
when ps.parameter_table_name is null and psc.context_value = to_char(v.request_type_id) then 'KCRT_REQUEST_DETAILS'
else ps.parameter_table_name
end as table_name,
ps.prompt as scr_prompt,
ps.last_update_date as prompt_last_updated,
ps.parameter_token as token,
case
when ps.batch_number is null then ps.parameter_token
else lpad(to_char(ps.parameter_column_number),2)
end as parameter,
ps.batch_number as batch_no,
ps.enabled_flag as enabled,
sect.section_name as screen_section,
val.validation_name as validation_name,
val.last_update_date as validation_updated,
ps.visible_default_const_value as default_value,
ps.display_only_flag as display_only,
ps.trans_history_flag as transaction_history_flag,
ps.notes_history_flag as notes_history_flag,
ps.multi_flag as multi_ent_flag,
ps.searchable_flag as searchable,
ps.visible_to_all_flag as visible_to_all,
ps.editable_by_all_flag as editable_by_all
from knta.KCRT_REQUEST_TYPES v,
knta.KNTA_PARAMETER_SET_CONTEXTS psc,
knta.knta_parameter_set_fields ps,
knta.KNTA_SECTIONS sect,
knta.KNTA_SECTION_LAYOUTS sl,
knta.KCRT_REQUEST_HEADER_TYPES hdt,
knta.KNTA_VALIDATIONS_V val
where (psc.context_value = to_char(v.request_header_type_id)
or psc.context_value = to_char(v.request_type_id))
and ps.parameter_set_context_id = psc.parameter_set_context_id
and sect.section_id = ps.section_id
and sl.section_id = sect.section_id
and val.validation_id = ps.validation_id
and hdt.request_header_type_id = v.request_header_type_id
and v.enabled_flag = 'Y'
and v.request_type_name = '<<< Enter Request Type Name Here >>>'
)   
order by request_type_name,scr_prompt,table_name,batch_no,parameter  

Derek Giedd
Honored Contributor.

Re: PPM Fields to Database mapping

I have a group of Excel macros that I have been working on as I get time that will document your PPM instance (request types, workflows, reports, project types, backend code).  For the request type documenter, it will tell you what the database table, field name, and batch number (if appropriate) for every field in the request type. 

The output for the request types is an Excel file per request type.  Other items create Excel files and Word documents.  For Workflows it provides you with info on each workflow but not the graphic (yet and maybe never unless I get more time).  It also will create a Word document of all notifications with embeded HTML objects so you can see how they look.

 

This is still a work in progress, but I can make it available if you are interested in it.  Just remember that it is not yet finished.  It would be good to have a few testers, but I do not want to release this to everyone yet.  If there is interest, I will make a new post about this requesting some testers with a full description of what this does.

-- Remember to give Kudos to answers! (click the KUDOS star)
AlexSavencu
Acclaimed Contributor.

Re: PPM Fields to Database mapping

Hi, Derek,

 

your initiative is excellent.

 

You can count on me for field testing.

 

cheers

alex


--remember to kudos people who helped solve your problem
AlfredoMonasi
Honored Contributor.

Re: PPM Fields to Database mapping

Sounds good, count with me to test it also!

haciyanli
Regular Contributor.

Re: PPM Fields to Database mapping

Please add me to your list and send the file. 

 

I can guarantee that I can test it on at least 3 live environment.

 

Regards

Sukru