PPM Fields to Database mapping
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.
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.
For request types I use the SQL below to map the fields. Hope it helps
select unique *
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'
end as table_name,
ps.prompt as scr_prompt,
ps.last_update_date as prompt_last_updated,
ps.parameter_token as token,
when ps.batch_number is null then ps.parameter_token
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,
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
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.