Introduction Research Plateau Data PRD Job Aid Data
Introduction – Research Plateau Data PRD Job Aid Data Model Guide 1
Data Models in this Job Aid 4 User Record 4 Items and Learning History 4 Curriculum Status 4 Scheduled Offerings/Registration 4 Custom Columns 4 Online Content 4 SF Online Exams 4 Admins, User Prefs, and Security 4 Training Planner 2
Resources for Report Design Plateau provides a data dictionary in Excel An Entity Relationship Diagram (ERD) provides a visual map of tables and fields 3
Key Tables for Plateau Reports Table Name Relevant Report PA_STUDENT Account Data and many others PA_SCHED Sign in Sheet, Learning Calendar PA_SCH_SEG Sign in Sheet – Segments section PA_ENROLL_SEAT Sign in Sheet – Enrollment section PS_SCHD_RESOURCES Summary table for offerings PA_ORG* Org details for many reports PA_CPNT Item Data, many others PA_CPNT_EVTHST Learning History PA_CMPL_STAT Learning History, Item Status PA_QUAL Curriculum Status 4
EVOLUTION OF TERMINOLOGY 5
RESEARCH REPORT IN PRD 4 2 1 3 Query for user account data. Includes syntax/comment for search by user. Security domain is based on PA_STUDENT. domain field 6
Tables Related to the User Record 7
MAP GUI FIELDS TO THE DATABASE 1 2 3 Plateau GUI PA_STUDENT Table Plateau Database 8
DEMO FOR PLATEAU REPORTS Account Data report in a Report 1 2 3 9
TABLES RELATED TO PA_STUDENT 1 2 3 10
Talent Profiles - User Data PA_STUD_TP Related Tables PA_STUD_AWARD PA_STUD_EDUCATION PA_STUD_GROUP PA_STUD_IM_DETAIL PA_STUD_INTEREST PA_STUD_NOTES PA_STUD_PROJECT PA_STUD_PUBLIC_PROFILE PA_STUD_SECURITY_CLEARANCE 11
Tables Related to Learning History Tables Related to Items and Learning History 12
ITEM DATA REPORT FIELDS Report Label Field Name from PA_CPNT_TYP_ID + ____ CPNT_ID (REV_DTE) _____ + ____ CPNT_TITLE DMN_ID CPNT_SRC_ID RTYP_ID (PA_RQMT_TYPE. rtype_desc) CREATE_DTE CPNT_DESC REV_DTE REV_NUM MAX_ENRL MIN_ENRL Course Length CPNT_LEN 13
Build a Data Model: Learning History 2 1 5 6 7 8 3 4 9 10 14
Data Model: History (contd) 2 1 5 6 7 8 3 4 9 10 Table Name: PA_CPNT_EVTHST Table Name: PA_CMPL_STAT Table Name: PA_STUDENT 15
Data Model: Learning History (contd) 1 2 5 6 7 3 8 4 9 PA_CPNT_EVTHST Table Name: 6 1 5 8 7 9 Table Name: 10 PA_CMPL_STAT 10 Table Name: PA_STUDENT 3 2 4 16
Expand the Data Model: Item Details PA_CPNT_EVTHST PA_STUDENT PA_CMPL_STAT PA_CPNT PA_RQMT_TYPE 17
Tables Related to Curriculum Status Tables Related to the Curriculum Status 18
Notes on Curriculum Structure 4 Each curriculum can contain one or more items that will be assigned directly or indirectly to users 4 The source of items in a curriculum can be: – One or more items (from the Items tab), mix of required and optional (these will be assigned directly to user’s Learning Plan) AND/OR – One or more items from one or more subcurricula from the Subcurricula tab (items from subcurricula will be assigned directly to user’s Learning Plan; requirements from subcurricula do not assign items directly to user’s Learning Plan) AND/OR – One or more requirements (from the Requirements tab where type is No of items from Pool of Items or No. of Hours from Pool of Items – items ARE NOT assigned directly to user’s Learning Plan) – SEE NOTES PAGE FOR DETAILS 19
Curriculum Status Reports > User Management > Curriculum Status Report 20
Curriculum Status Report Model Reports > User Management > Curriculum Status Report PA_STUD_QUAL. stud_id PA_STUDENT. fname and lname and MI from PA_STUD_QUAL_CPNT psqc PA_STUD_QUAL. qual_id PA_QUAL. qual_title Yes/No (calculated field) PA_CPNT pc pc. cpnt_id pc. cpnt_desc psq. assgn_dte psqc. rem_days from item with next due date PA_STUD_QUAL_CPNT psqc. compl_dte psqc. cmpl_stat_id psqc. req_dte Use cmpl_stat_id to join with PA_CMPL_STAT and retrieve cmpl_stat_desc Field for user_add shows if item was added by user (P), supervisor (S), or admin (A) How calculate completion status? Report uses a package/set of queries for this 21
PA_QUAL_JP QUAL_ID JP_ID PA_JOB_POS JP_ID JP_DESC Tables with dashed lines show tables used for reports on user status PA_QUAL_ID DMN_ID QUAL_TYP_ID NOTACTIVE CREATE_DTE QUAL_TITLE QUAL_DESC LST_UPD_USR LST_UPD_TSTMP FORCE_INCOMPLETE BASIS_DATE ESIG_ENABLED PA_QUAL_SUBQUAL_ID_SUB LST_UPD_USR LST_UPD_TSTMP PA_STUD_QUAL_CPNT STUD_ID QUAL_ID_ROOT QUAL_ID CPNT_TYP_ID CPNT_ID REV_DTE REQ_SEQ ASSGN_DTE RTYP_ID (assignment type) REQ_DTE EXP_DTE RMNDR_DTE COMPL_DTE RETRNG_INT LST_UPD_USR LST_UPD_TSTMP PA_QUAL_REQ QUAL_ID REQ_SEQ REQ_GROUP_ID PA_REQ_CPNT REQ_SEQ CPNT_ID Requirement details on next slide PA_QUAL_RELATION QUAL_ID_PARENT QUAL_ID_CHILD DIRECT_LINK LST_UPD_USR LST_UPD_TSTMP Table only populated if parent/child relationship exists. If direct_link = N then curriculum is not child, but grandchild or great grandchild In PA_STUD_QUAL_ CPNT, if qual_id_root != qual_id, then parent / child relationship exists PA_STUD_QUAL_RELATIO N STUD_ID QUAL_ID_ROOT PA_STUDENT STUD_ID etc There is entry in PA_QUAL_CPNT if curriculum has items OR if curriculum has requirements pool with specific items PA_QUAL_CPNT (from Items tab, not reqs) QUAL_ID from PA_QUAL CPNT_TYP_ID PA_CPNT is CPNT_ID on next slide REV_DTE RTYP_ID INIT_PD RETRNG_INT STAT_EFFECT_DTE SEQ_NUM LST_UPD_USR LST_UPD_TSTMP INIT_PERIOD_TYPE_ID INIT_NUMBER INIT_BASIS_TYPE_ID RETRNG_PERIOD_TYPE_ID RETRNG_NUMBER RETRNG_BASIS_TYPE_ID INIT_THRESHOLD_DAYS BASIS_DATE 22
PA_REQUIREMENT REQ_SEQ REQ_TYPE_ID REQ_DESC CREATE_DATE DMN_ID ACTIVE HOUR_TYPE_ID NUM_HOURS NUM_ITEMS COMMENTS LST_UPD_USR LST_UPD_TSTMP PA_STUD_QUAL_REQ STUD_ID QUAL_ID_ROOT QUAL_ID REQ_SEQ ASSGN_DTE REQ_DTE EXP_DTE RMNDR_DTE COMPL_DTE RETRNG_INT LST_UPD_USR LST_UPD_TSTMP PA_REQ_CPNT (if Hour Type From Pool or Items from Pool req type) REQ_SEQ CPNT_TYP_ID CPNT_ID REV_DTE REMOVAL_DATE LST_UPD_USR LST_UPD_TSTMP PA_QUAL_REQ QUAL_ID REQ_SEQ REQ_GROUP_ID INIT_PERIOD_TYPE_ID INIT_NUMBER INIT_BASIS_TYPE_ID RETRNG_PERIOD_TYPE_ID RETRNG_NUMBER RETRNG_BASIS_TYPE_ID STAT_EFFECT_DTE LST_UPD_USR LST_UPD_TSTMP INIT_THRESHOLD_DAYS BASIS_DATE PA_STUDENT STUD_ID etc PA_CPNT (if Hour Type From Pool or Items from Pool) CPNT_TYP_ID CPNT_ID REV_DTE DELIVERY_METHOD CPNT_CLASSIFICATION CPNT_TITLE CONTACT (Design Data) PA_QUAL_REQ QUAL_ID REQ_SEQ Previous slide PA_STUD_CPNT See Notes page for scenarios where item is in this table STUD_ID CPNT_TYP_ID CPNT_ID REV_DTE RTYP_ID (assignment type) COMPL_DTE CMPL_STAT_ID ONLINE_LAUNCHED ORIGIN (may be Curriculum Requirement) PA_QUAL_REQ_GRP (if group exists; not needed for report) REQ_GROUP_ID REQ_GROUP_DESC QUAL_ID INIT_PERIOD_TYPE_ID INIT_NUMBER INIT_BASIS_TYPE_ID RETRNG_PERIOD_TYPE_ID RETRNG_NUMBER RETRNG_BASIS_TYPE_ID STAT_EFFECT_DTE LST_UPD_USR LST_UPD_TSTMP INIT_THRESHOLD_DAYS 23 BASIS_DATE
Compare Report Output to GUI Curriculum Item Status Report Neither Curriculum Item Status nor Curriculum Status report displays a field labeled Expiration Date. But if any completed item has a populated Required Date, the NEXT item required date is the same as the curriculum expiration date. Plateau calculates curriculum Days Remaining as difference between sysdate and req_dte of item with the “most aggressive” due date 24
Compare Rpt Output to GUI (cont’d) User side: Go to Learning > Curriculum Status Curriculum Expiration Date reflects the NEXT item required date when retraining is needed Next Action Date = PA_STUD_QUAL_CPNT. req_dte of item that is due soonest. If curriculum is complete and no items require retraining, this field will be null. Expiration Date = PA_STUD_QUAL. exp_dte (only populated if at least one item requires retraining. ) Select curriculum title to open Curriculum Details screen Retraining Assignments: 1 Years - Calendar Required By = PA_STUD_QUAL_CPNT. req_dte for retraining date due Date Completed = for successful completion = PA_STUD_QUAL_CPNT. cmpl_dte Status = not shown in words. We display diploma ( ) icon for successful completion, where PA_CMPL_STAT. provide_crdt = ‘Y’. Actual status earned = PA_STUD_QUAL_CPNT. cmpl_stat_id See next page for details on status where PA_CMPL_STAT. provide_crdt = ‘N’ 25
Compare Report Output to Admin GUI User Management > User record > Curricula tab User A is current on all curriculum requirements User B is overdue for an item that requires retraining If the expiration date of any item is in the past, the expiration date for the curriculum is N/A 26
Compare Rpt Output to GUI (cont’d) Curriculum Details screen with unsuccessful completions Required By = PA_STUD_QUAL_CPNT. req_dte – still populated because item was not completed successfully. Unsuccessful = failed exam where you track failure in the user learning history; or item was recorded with completion status such as “INCOMPLETE” or “NO SHOW” Date Completed = for failure, PA_STUD_QUAL_CPNT. failure_dte Status = not shown in words. We display the no diploma ( ) icon for unsuccessful completion, where PA_CMPL_STAT. provide_crdt = ‘N’. The actual status earned = PA_STUD_QUAL_CPNT > failure_cmpl_stat_id 27
Understanding Curriculum Requirements PS_QUAL_REQUIREMENT_TYPE contains 3 possible requirement types: • Hours of Hour Type No. of hours from any entry in PA_CPNT_EVTHST –OR- from PA_XCPNT_EVTHST • Hours of Hour Type From Pool • Items from Pool 28
Curriculum Status Report: Requirement with Number Of Hours 29
Curriculum Status on the User Side: Requirement With No. Of Hours 30
Curriculum Status on the User Side: Use Requirement with a Group (OR relationship) 31
Curriculum Status Rpt: Requirement with No. Of Hrs and Requirement with Group 32
Query for Requirements • For the Plateau-provided Curriculum Data Report, we allow the admin to select the requirement type at run time SELECT q. qual_id, q. qual_title, q. dmn_id, d. dmn_desc, DECODE (q. notactive, 'Y', 'N', 'Y') active, q. qual_desc, q. create_dte, q. force_incomplete FROM pa_qual q, pa_domain d WHERE q. dmn_id = d. dmn_id(+) /** AND qual_id in [Curriculum. Search] */ /** AND [sub-select: Requirement. Type. Search "q. qual_id in (select qual_id from pa_qual_req qr, pa_requirement r where req_type_id in [Requirement. Type. Search] and qr. req_seq = r. req_seq)"]*/ /** AND notactive = [Not. Active] */ /** AND [security: PA_QUAL q]*/ ORDER BY qual_title 33
Final Piece of Curriculum Puzzle - Docs PA_DOC DOC_ID DMN_ID DOC_TYP_ID NOTACTIVE DOC_TITLE DOC_SRC DOC_LOCN AUTHOR REV_NUM REV_DTE REVISER APPRVR COMMENTS LST_UPD_USR LST_UPD_TSTMP PA_QUAL_ID No joins between PA_STUD_QUAL* tables related to docs. Docs are not “assigned” to users PA_QUAL_DOC QUAL_ID DOC_ID If you do not use the Document functionality, no need to include these tables in reports 34
Tables Related to Scheduled Offerings and Enrollment 35
Registration - Sign In Sheet 36
Scheduled Offering Summary GUI Display Table: PA_SCHED PK 37
Segment Details GUI Display PA_SCH_SEG. start_dte, end_dte, start_time, end_time PA_SSG_INST. inst_id PA_INST. fname & lname PA_SSG_LOCN. locn_id PA_LOCN. locn_desc PA_SCH_SEG. seg_desc Tables PA_SCH_SEG PA_SSG_INST PA_SSG_LOCN 38
Offerings – Segments PS_SCHD_RESOURCES PA_SCHED PA_SCH_SEG PA_INST PA_SSG_INST PA_LOCN PA_SSG_LOC See notes page 39
Offerings – Schedule Blocks PS_SCHD_RESOURCES PA_SCHED PA_SCH_SEG = 'A' PA_INST PA_SSG_INST PA_ACTIVITY See notes page 40
Enrollment/Registration Details Only list users for whom student is enrolled (PA_ENROLL_STAT. enrl_stat_id = ‘E’) Report Output lname, fname, MI and stud_id from PA_STUDENT where user_id matches super field user ID Tables PA_ENROLL_SEAT PA_STUDENT PA_ENROLL_STAT 41
Offerings – Enrollment PA_SCHED PA_ENROLL_SEAT PA_STUDENT PA_ORG PA_ENROLL_STAT 42
Job Aid on Using Customer Columns in Plateau Reports This icon on a slide means you should refer to Notes pages for more detail 43
Topics in this Job Aid 4 List of entities that support custom columns 4 Relationship of GUI to database tables 4 Relationship of each user record to custom columns 4 Data model for custom columns 4 Query samples to learn more about YOUR environment: a. b. c. Retrieve custom column definitions Retrieve user data with the non-referenced custom column values Retrieve user data with the referenced custom column values 4 Query registered users and retrieve custom column 4 Define reusable sub-select statement for ANY query 4 Query PV_STUD_USER as alternative 4 Options for using query in PRD with sample reports to download: a. b. Option A: Edit Main Query Option B: Add Child Query for Custom Columns 44
Desired Result: Display Custom Field Business Unit Airline Solutions Travel Network Sales Honors -- Sign in sheet displays a custom column from the user record. Where did this come from? This PPT explains the custom column data model and provides queries to retrieve fields from the database 45
![Entities with Custom Columns PA_USRCL_[ENTITY] contains list of custom Curriculum QUAL columns and whether](http://slidetodoc.com/presentation_image_h/eb49ed341ba97b71cc8ec92c4e9a8cf9/image-46.jpg "Entities with Custom Columns PA_USRCL_[ENTITY] contains list of custom Curriculum QUAL columns and whether")
Entities with Custom Columns PA_USRCL_[ENTITY] contains list of custom Curriculum QUAL columns and whether referenced PA_USRRF_[ENTITY] contains referenced values, if applicable PA_ [ENTITY]_USER SCHD, SCHED contains values for each custom column when it is set in the entity 46
Relationship of GUI to Database Tables GUI ACTIONS Admin goes to System Admin > Custom Columns > User to add new or edit existing PA_USRCL_STUD Admin can select Referenced checkbox and create list of entries to populate dropdown for user record DATABASE ACTIONS Each record populates PA_USRCL_STUD PA_USRRF_STUD If admin selects Referenced checkbox, system populates PA_USRRF_STUD PA_USRCL_STUD. label is the “plain English” name for the custom column PA_USRRF_STUD. user_desc is the “plain English” value of the column 47
Relationship of Each User to Database The user record is updated with entries for the custom columns. This action populates PA_STUD_USER with values for each user. PA_STUD_USER Notice that in PA_STUD_USER, the field is called user_value. In PA_USRCL_STUD, the same field is called user_id There is no representation for a user record with null entries. No row exists in PA_STUD_USER for a col_num unless the user has an entry in that field. 48
Data Model for User Custom Columns PV_STUD_USER 49
Query Your Env for Custom Fields SELECT * from PA_USRCL_STUD ORDER BY col_num SELECT * from PA_USRRF_STUD ORDER BY col_num 50
Query Your Env for User Data Get to know YOUR user custom columns. Run this query with your column number and a meaningful alias. In this query, column 30 = Business Unit: SELECT s. stud_id, nvl(bu. bus_unit, 'Not Specified') as bus_unit FROM pa_student s, (SELECT user_cust. stud_id, ref. user_desc as bus_unit FROM PA_STUD_USER user_cust, PA_USRRF_STUD ref WHERE user_cust. user_value = ref. user_id AND user_cust. col_num = ref. col_num (+) AND ref. col_num (+)= 30 ) bu WHERE s. stud_id = bu. stud_id (+) Sub-select statement retrieves each user’s business unit, including null. Alias bu represents output of the sub-select to display on the report 51
Query Your Env for More User Data Select all custom columns and list referenced values, if applicable: SELECT a. col_num, a. stud_id, c. label, user_value, user_desc FROM PA_STUD_USER a, PA_USRRF_STUD b, PA_USRCL_STUD c WHERE a. col_num = c. col_num AND a. col_num = b. col_num(+) AND a. user_value = b. user_id(+) ORDER BY col_num 52
Registration + Custom Column Retrieve user custom column based on enrollment table (registration in a scheduled offering). User is retrieved even if their custom column is null SELECT pes. stud_id, pes. schd_id, ref. user_desc as acct_code FROM PA_STUD_USER psu, PA_USRRF_STUD ref, PA_ENROLL_SEAT pes WHERE psu. user_value = ref. user_id(+) AND ref. col_num (+)= 10 Jump to slide 16 to download sample file AND pes. stud_id=psu. stud_id(+) ORDER BY pes. schd_id 53
Re-Usable Sub-Select Statement SELECT st. stud_id, jb. Job. Band, jt. Job. Track FROM pa_student st, (SELECT su. stud_id, rf. user_desc Job. Band This query uses subselect statements for 2 referenced custom columns and creates an alias for each. FROM pa_stud_user su, pa_usrrf_stud rf WHERE su. col_num = rf. col_num AND su. user_value = rf. user_id AND su. col_num (+)= 30) JB, (SELECT su. stud_id, rf. user_desc Job. Track FROM pa_stud_user su, pa_usrrf_stud rf You can edit the query to return as many fields as you wish to retrieve. You can add this query as a sub-select to any other query WHERE su. col_num = rf. col_num AND su. user_value = rf. user_id AND su. col_num (+)= 20) JT WHERE st. stud_id = jb. stud_id (+) AND st. stud_id = jt. stud_id (+) The outer join (indicated by +) will pick up the user even if their user record has no entry in this custom column 54
Non-Referenced Custom Columns Query learning history and include user custom columns. For nonreferenced fields, you do not need a join to PA_USRRF_STUD SELECT c. stud_id, c. lname, c. fname, a. cpnt_id, a. compl_dte, a. CMPL_STAT_ID, b. cmpl_stat_desc, b. PROVIDE_CRDT, a. SCHD_ID, a. TOTAL_HRS, c. JL_ID, c. JP_ID, c. DMN_ID, sup. stud_id superid, sup. lname superlname, sup. fname superfname , ( SELECT user_cust. user_value FROM PA_STUD_USER user_cust WHERE user_cust. col_num (+)= 10 AND user_cust. stud_id=a. stud_id ) acct_code Sub-select statement retrieves each user’s account code, including null. Alias acct_code represents output of the subselect to display on the report FROM PA_CPNT_EVTHST a, PA_CMPL_STAT b, PA_STUDENT c, PA_STUDENT sup WHERE a. cmpl_stat_id = b. cmpl_stat_id AND a. stud_id = c. stud_id AND c. super = sup. stud_id (+) /** AND (a. cpnt_typ_id, cpnt_id, rev_dte) in [Item. Search] and c. stud_id in [User. Search] and b. cmpl_stat_id in [Completion. Status. Search] and compl_dte > [From. Date] and not compl_dte > [To. Date] and [security: pa_student c] */ 55
For User Custom Column – PV Table Check PV_STUD_USER (view) You can write a query to this view table. However, that might be resource intensive and slow the response time of the report. The PV_AP_STUD_USER table is NOT populated with the stud_id if the user has no custom columns populated. To check: SELECT nvl(pv. stud_id, 'No Custom Columns') as PV_USER, st. stud_id as STUD_USER FROM PV_AP_STUD_USER pv, PA_STUDENT st WHERE st. stud_id=pv. stud_id(+) Jump to slide 16 to download sample file 56
Plateau Report Designer and Custom Columns 57
Option A in PRD: Edit Main Query 4 Edit the existing enrollment query: – Edit the query as in Slide 11 and retrieve the custom column value – Edit the query as in Slide 12 and add a sub-select Click here to download report design file with sample solution – with child query Click here to download report design file with sample solution - with CSV output View Notes page for a query to retrieve custom column for a learning history report 58
Option B in PRD: Add Child Query 4 Add a new child query specifically for the custom column(s) 4 Query is re-usable in multiple reports 4 Requires insert of a child/nested table 4 Will not work with CSV output reports (which cannot have child queries) 59
Option B in PRD: Child Query (cont’d) 4 Can set header to print “---” if custom column is blank 4 Define visibility for header row to hide if no detail rows returned Set visibility for header row to hide if the search returns a row for account code : Hide element if Total. count() > 0 Click here to download report design file with sample solution 60
Other Examples Retrieve Custom Column for a Scheduled Offering Retrieve Custom Colum for an Item (see Notes page) 61
Sched Offering with Custom Column Now you know how the custom columns work for the user record. Remember that scheduled offerings can also use custom columns. The next few slides show a scenario where the scheduled offering has a custom column for Caterer. Here is the admin’s view: 62
Data Model: Offering Custom Columns Scheduled offering with custom columns – database configuration Define column definitions Define values for referenced custom column – populates pick list 63
Data Model: Offering (cont’d) Scheduled offering with custom columns – view of specific offering Column definitions Column values Entry for this scheduled offering 64
Tables Related to Online Content 65
Online Content Structure Data Model pca. app_id = object ID pca. app_title = object title primary_param = launch method > file name (path) PA_CBT_APPLICATION PA_CBT_APP_DATA Only applicable for Team Content PA_CBT_CPNT Content object > details PA_CBT_CPNT_MOD Item > online setting details Module type: 0 - Group 1 - Content 2 - Exam Item > online setting details Relevant if imported AICC content 66
Online Content (cont’d) SELECT pc. cpnt_id item_id , pc. cpnt_typ_id, pc. rev_dte , pc. cpnt_title item_title, pc. dmn_id item_domain , decode(pc. notactive, 'Y', 'N', 'Y') as item_active , pccm. module_id item_mod_id, pca. app_id object_id, pca. app_title object_title , pca. dmn_id as object_domain, pcc. is_active as object_active , pca. primary_param as launch_url FROM PA_CBT_APPLICATION pca , PA_CBT_CPNT pcc , PA_CBT_CPNT_MOD pccm , PA_CPNT pc WHERE pca. app_id = pccm. app_id and pc. cpnt_id = pccm. cpnt_id and pc. cpnt_typ_id = pccm. cpnt_typ_id and pc. rev_dte = pccm. rev_dte and pcc. cpnt_id = pccm. cpnt_id and pc. cpnt_id = pcc. cpnt_id and pc. rev_dte = pcc. rev_dte ORDER BY object_id, item_id 67
Online Item Status PA_CBT_STUD_CPNT PA_CMPL_STAT PA_CBT_STUD_CPNT_MOD PA_STUD_CPNT or PA_STUD_QUAL_CPNT PA_STUDENT 68
Online Content - SCORM Details Research SCORM 2004 data – test answers. See Notes page for details PA_CBT_SCO_INTERACTION PA_CBT_STUD_CPNT_MOD PA_CBT_STUD_CPNT PA_CBT_CPNT_MOD Note: To generate a report on learner response, use a query that extracts the relevant text substring 69
Tables Related to Plateau Online Exams Tables Related to the Plateau Online Exams 70
Tips for Reports Working with Exams This presentation captures a variety of details about how customers track user performance on Plateau online exams Notes v Refer to the data dictionary for additional details on field use v Plateau online exams are a special type of content object : Exam objects use many of the same tables as other online content Not all fields are applicable for Plateau online exams Some fields only used when customer creates exams in OTHER applications such as Question. Mark v Plateau-provided reports of interest (and useful for query research): Exam Item Analysis (available as CSV; compiles data on questions asked answered by all users, number attempted, number correct; does NOT give percentage of users who answered wrong or right for each question distractor User Exam Data -- not converted to PRD yet; search by user, by date range completed, by Exam Objects -- not converted to PRD yet; displays high level settings from exam such as Mark item completed when exam passed and Mark item failed when exam failed v Reports that are related to objects that are NOT exams (exam is where module_type = 2): Score Summary by Content Object (or grouped by other attributes) v Previous versions of Plateau used some exam-related tables that are no longer relevant after version SP 4. Customers will not see any data in these tables – but if you have a database with older Plateau test data, you may see entries. Ignore all entries where question name like ‘%Legacy%’ 71
Tips on Data Captured for Exams – Data Model with Sample Data PA_CBT_STUD_CPNT for the assigned item that contains an exam - stud_id joins to PA_STUDENT, cpnt_id joins to PA_CPNT and to PA_STUD_CPNT or to PA_STUD_QUAL_CPNT for learning plan PA_CBT_STUD_CPNT_MOD for the assigned item with a content object that is an exam (module_type = 2). stud_cpnt_id joins to PA_CBT_STUD_CPNT, module_id joins to PA_CBT_STUD_EXAM and PA_CBT_STUD_EXAM_QUESTION is_active = Y only when user is in middle of exam at time query runs PA_CBT_STUD_EXAM for the exam associated with the assigned item. This tables tracks one iteration for every time this user takes an exam. If complete_date is null – exam is not complete. If complete_date has entry, but complete = N, user has failed PA_CBT_STUD_EXAM_QUESTION for the questions that this user was presented for each iteration. Join to PA_CBT_EXAM and PA_CBT_EXAM _QUESTION via question_id 72
Sample Data for How the User Answered a Question PA_CBT_STUD_EXAM_QUESTION STUD_CPNT_ID QUESTION_NO MODULE_ID EXAM_NAME ITERATION QUESTION_ID OBJECTIVE_ID DISTRACTOR_SEED ANSWERED (if ‘N’, user skipped question) ANSWER_DTE CORRECT TOTAL_TIME QUESTION (from legacy only) LAST_UPD_USER LAST_UPD_TSTMP TOTAL_POINTS_SCORED ANSWER_SUMMARY (only for pre-SP 5) ANSWER See Notes page for skipped questions 73
How Admins Can View Iteration Details – From User Record 74
How Admins Can View Iteration Details – From Question Go to Content > Questions and access question record View Variant Usage Select Show Details 75
Plateau-Provided Reports for User Exam Details User Exam Data (not PRD yet, csv in legacy ) Sample output 76
How to Extract the Text for the Question Stem To generate details on each question, Plateau uses this query in Plateau-provided reports: select qe. question_name, qe. version, qe. revision, m. description, cnt. question_id , cnt. learners as responses, cnt. exam_name, qe. question_type_id, pkg_exam. get_question_stem (pkg_tool. get_clob_from_blob (qe. question_data)) as question_object from pa_cbt_question qe, pa_cbt_question_metadata m, (select question_id, count(*) as learners , exam_name from pa_cbt_stud_exam_question c where c. exam_name=? group by question_id, exam_name ) cnt where qe. question_id = cnt. question_id and qe. question_name = m. question_name; See Notes page re: error in earlier versions of SP 5 The raw result from this query contains format characters – the actual question is shown here in bold: <p><FONT SIZE=4>True or False? </FONT></p><p> </p><p> Heredity can affect susceptibility to infection. </p> At runtime in Plateau, we use the operator Get. Student. Exam. Question. Object. Text to extract the question stem and strip the formatting. ** To avoid using the package (and therefore avoid using the operator), replace the statement in the box at the top of this screen with this statement to retrieve the stem directly: qe. question_stem as question 77
How to Extract the Text for Each Answer In Plateau legacy reports, we use packages and operators Get. Student. Exam. Question. Response and Get. Student. Exam. Question. Detail. Correct. Answer. That is how we collect the user’s answer and determine if the answer is correct. The query on the Notes page extracts answer text while reporting results compiled across all users. Sample A below shows the original Plateau select statement and the results: Extract details: TABLE (pkg_exam. get_question_responses (cnt. exam_name, cnt. question_id)) y Use the field from this package in the SELECT statement: y. distractor (or sometimes as y. question_object) Results: 3 -<CHOICE iscorrect="true" style_id="84“> <TEXT><
Notes on Tables Storing Details on Questions v Tables used to track questions: PA_CBT_EXAM_QUESTION is populated when an exam contains any free-standing questions (not associated with objectives) PA_CBT_EXAM_OBJ is populated when exam contains any question associated with an objective v Tables NOT used for exam reports: PA_CBT_QUESTION_OBJECT – legacy table only, not relevant for versions after SP 4 PA_QUESTION_ANSWER_CHOICES – used for evaluations (surveys), not for exam questions PA_CBT_RESOURCE (and related tables) – store data re: images or links used in questions PA_CBT_OBJ_BANK* - stores data from CBTs with exams, not for Plateau online exams 79
How Plateau Tracks Question Variants/Versions v v When admin add new question, Plateau populates PA_CBT_QUESTION with new question ID, version (variant) 1 When admin adds 2 nd variant (different way to ask the same question), Plateau creates NEW question ID in PA_CBT_QUESTION User is presented only one variant per question per exam, randomly selected by Plateau when exam is generated If admin revises a question, the revision field is updated with the next sequential number PA_CBT_QUESTION entries for 2 variants 80
Data Model for Questions and Objectives PA_CBT_EXAM_DEF EXAM_NAME DESCRIPTION DMN_ID PLAYER_TYPE IS_ACTIVE EXAM_TYPE AICC_ID HAS_ACCESS CHILD_COUNT PASS_GRADE MINIMUM_QUESTIONS MAXIMUM_QUESTIONS NO_OF_QUESTIONS PROCTOR_REQUIRED REPEAT_COUNT RESUMEABLE (user can start/stop) REVIEWABLE GRADE_EXAM EXIT_COURSE_WHEN_PASSED LOCK_EXAM_WHEN_PASSED CLEAR_ALL_FLAGS_WHEN_FAILED EXIT_COURSE_WHEN_FAILED LOCK_EXAM_WHEN_FAILED NOTIFY_PROCTOR_WHEN_FAILED SET_FLAGS_WHEN_PASSED CLEAR_FLAGS_WHEN_PASSED SET_FLAGS_WHEN_FAILED CLEAR_FLAGS_WHEN_FAILED PA_CBT_EXAM_QUESTION EXAM_NAME QUESTION_ID POINT_VALUE LAST_UPD_USER LAST_UPD_TSTMP PA_CBT_QUESTION_ID QUESTION_NO DMN_ID VERSION REVISION LAST_EDIT_DATE LAST_PRESENTED IS_ACTIVE OBJECTIVE_ID CHOICES_RANDOMIZED DESCRIPTION POINT_VALUE HAS_PLATEAU_OBJECTS IS_WEB_ENABLED LAST_UPD_USER LAST_UPD_TSTMP QUESTION_TYPE_ID QUESTION_DATA QUESTION_NAME IMPORT_DATE QUESTION_STEM FEEDBACK PA_CBT_EXAM_QUESTION contains standalone questions in the exam. PA_CBT_EXAM_OBJ contains objectives in an exam that generate pools of questions PA_CBT_EXAM_OBJ EXAM_NAME OBJECTIVE_ID PASS_GRADE MIN_QUESTIONS MAX_QUESTIONS POINTS_PER_OBJECTIVE LAST_UPD_USER LAST_UPD_TSTMP PA_CBT_OBJECTIVE_ID OBJECTIVE DESCRIPTION DMN_ID SOURCE_FILE IS_ACTIVE IS_ONLINE LAST_UPD_USER LAST_UPD_TSTMP IMPORT_VERSION 81
Data Model for Users Answering Questions PA_CBT_STUD_CPNT_MOD (continued) COMPLETE_ON_LAUNCH HAS_ADMIN_MODIFIED EXIT_COMPONENT COMPLIANCE_BLOB_ID TRIGGER_CPNT_PASS (complete item successfully when exam is passed) TRIGGER_CPNT_FAIL (complete item as a failure when exam is failed) LAST_UPD_USER LAST_UPD_TSTMP FREEZE_CPNT_OBJS_DURING_LAUNCH FREEZING_OTHER_CPNT_OBJECTS Not relevant for exams: CMI_COMPLETION_STATUS SUCCESS_STATUS PROGRESS_MEASURE CMI_ENTRY LESSON_LOCATION SCORE_RAW SCORE_MIN SCORE_MAX SCORE_SCALED CMI_EXIT ACTIVITY_ID MASTERY_SCORE OFFLINE_ACCESSIBLE (Offline Player) EXAM_DURATION (only if timed) EXAM_REVIEW_OPTION (relevant if exam_revw-enabled = ‘Y’) AICC_DATA PA_CBT_STUD_CPNT_MOD STUD_CPNT_ID MODULE_ID EXAM_NAME APP_ID DEVELOPER_ID MODULE_NAME (= exam name) MODULE_TYPE (2 = exam) IS_ACTIVE (is currently launched) HAS_ACCESS (is not locked out) AICC_ID AICC_LESSON_LOC PARENT_ID (not for exams) CHILD_COUNT (not for exams) FINISHED COMPLETE_DATE INITIAL_DATE LAST_ACCESS TOTAL_TIME (only if timed exam) TOTAL_TIMES FORCE_ORDER (not for exams) EXPANDED (not for exams) NEEDS_REVIEW (not for exams) QUESTIONS_ASKED QUESTIONS_CORRECT SCORE COMMENTS APP_CMD_OPT (not for exams) PLAY_ALL_SCRNS (not for exams) EXAM_REVW_ENABLED EXAM_REVW_PROMPT PA_CBT_EXAM_DEF EXAM_NAME DESCRIPTION DMN_ID PA_CBT_CPNT_MOD CPNT_ID CPNT_TYP_ID REV_DTE EXAM_NAME PA_CBT_STUD_CPNT_ID STUD_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE PA_CBT_STUD_EXAM (next slide) INITIAL_DATE STUD_CPNT_ID MODULE_ID EXAM_NAME ITERATION PA_CBT_STUD_EXAM_QUESTION STUD_CPNT_ID MODULE_ID EXAM_NAME QUESTION_ID QUESTION_NO (order question was presented to this user) See next slide 82
More Data Model for Users Answering Questions PA_CBT_STUD_CPNT_ID STUD_ID CPNT_TYP_ID CPNT_ID REV_DTE CMPL_STAT_ID COMPL_DTE PA_CBT_STUD_EXAM INITIAL_DATE STUD_CPNT_ID MODULE_ID EXAM_NAME ITERATION (show MAX only for latest) COMPLETE_DATE TOTAL_TIME COMPLETE SCORE PASS_GRADE LAST_QUESTION SID_REQUIRED (e-signature) SID_ENTERED (e-signature) FINISHED (finished exam at least once, maybe not passed) LAST_ACCESS_DATE TOTAL_TIMES HAS_ACCESS CURRENT_QUESTION IS_ACTIVE ENTRY_PROCTOR_INST_ID ENTRY_PROCTOR_CODE FAILED_PROCTOR_INST_ID FAILED_PROCTOR_CODE EXIT_COMPONENT EXAM_ABORTED LAST_UPD_USER LAST_UPD_TSTMP RECORDED_EXAM_DURATION (only if timed exam) PA_CBT_STUD_EXAM_QUESTION STUD_CPNT_ID MODULE_ID EXAM_NAME QUESTION_NO (the order in which this user saw this question in this exam) ITERATION QUESTION_ID OBJECTIVE_ID DISTRACTOR_SEED ANSWERED (‘N’ if skipped or timed out) ANSWER_DTE CORRECT (Y or N) TOTAL_TIME (only if timed exam) QUESTION (always null LAST_UPD_USER LAST_UPD_TSTMP TOTAL_POINTS_SCORED ANSWER_SUMMARY (pre-SP 4) ANSWER (blob – Lane check purpose) PA_CBT_STUD_CPNT_MOD STUD_CPNT_ID MODULE_ID EXAM_NAME See previous slide 83
Exam Relationships to User’s Learning Plan/Assigned Items PA_CBT_STUD_EXAM INITIAL_DATE STUD_CPNT_ID MODULE_ID EXAM_NAME PA_CBT_STUD_CPNT_ID STUD_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE PA_STUD_CPNT STUD_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE PA_STUD_QUAL_CPNT STUD_ID QUAL_ID_ROOT QUAL_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE Exam relationships to user’s learning plan: • Item containing an exam is assigned via PA_STUD_CPNT (free standing item) or PA_STUD_QUAL_CPNT (curriculum), then system populates tables shown above • Exam remains associated with user record until passed successfully or failed (if failure writes to learning history) 84
Exam Relationships to User’s Learning History PA_CBT_STUD_EXAM INITIAL_DATE STUD_CPNT_ID MODULE_ID EXAM_NAME ITERATION COMPLETE_DATE COMPLETE SCORE LAST_ACCESS_DATE TOTAL_TIMES LAST_UPD_USER LAST_UPD_TSTMP PA_CBT_STUD_CPNT_ID STUD_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE CMPL_STAT_ID COMPL_DTE PA_CPNT_EVTHST STUD_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE CMPL_STAT_ID COMPL_DTE PA_STUD_CPNT STUD_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE CMPL_STAT_ID COMPL_DTE PA_STUD_QUAL_CPNT STUD_ID QUAL_ID_ROOT QUAL_ID CPNT_ID (join to PA_CPNT) CPNT_TYP_ID REV_DTE CMPL_STAT_ID COMPL_DTE Exam relationships to learning history • When PA_CBT_STUD_EXAM complete = Y, complete_dte and score will also be populated • If PA_CBT_STUD_CPNT_MOD. trigger_cpnt_pass = Y, successful completion is written to PA_CPNT_EVTHST for this item. Does NOT capture score or grade in this table. • If PA_CBT_STUD_CPNT_MOD. trigger_cpnt_fail = Y, failure completion is written to PA_CPNT_EVTHST for this item 85
How Plateau Stores Exam History v PH_CBT_STUD_EXAM_QUESTION for questions that were answered on an exam iteration BEFORE the current one (in PA_CBT_STUD_EXAM) v PH_CBT_STUD_EXAM for exam results that were completed (successfully or unsuccessfully) v PH_CBT_STUD_CPNT_MOD 86
Tracking Results for Printed Exams Entered by Admins Customer wants to find out what answers an admin entered on a printed exam And “find out if admin changed answers” But admin cannot change existing answers – admin can record the entire exam a second time Admin first generate 1 or more exams from Content > Exam Objects > exam record > Printed Exam tab When scored for a user, printed exams write to PA_CBT_STUD_EXAM There is not a separate table to store user answers PA_CBT_STUD_EXAM contains current data – exam entered most recently PH_CBT_STUD_EXAM holds historic data for all exams entered before current one Where pa. stud_cpnt_id = ph. stud_cpnt_id (1037) And pa. module_id = ph. module_id (1329) And pa. exam_name = ph. exam_name (‘TRAFFIC_EXAM) And last_upd_user not like ‘STUDENT%’ 87
Tables Used for Plateau Exam Reports where Exam Is Printed PA_CBT_PRINTED_EXAM GENERATED_SEQ_ID EXAM_NAME GENERATION_DATE LAST_UPD_USER LAST_UPD_TSTMP PA_CBT_EXAM_DEF EXAM_NAME DESCRIPTION DMN_ID … PA_CBT_PRINTED_EXAM_QUESTION GENERATED_SEQ_ID QUESTION_NO EXAM_NAME QUESTION_ID DISTRACTOR_SEED TOTAL_POINTS LAST_UPD_USER LAST_UPD_TSTMP PA_CBT_PRINTED_EXAM_OBJ GENERATED_SEQ_ID OBJECTIVE_ID EXAM_NAME PASS_GRADE POINTS_PER_OBJECTIVE LAST_UPD_USER LAST_UPD_TSTMP To find out if an exam was entered by an admin via a printed exam – PA_CBT_STUD_EXAM. lst_upd_user not like ‘STUDENT%’ 88
Supplementary Queries for Other Exam Reports Generate list of all exams and number of users who have attempted the exam at least once Includes exams completed in the UI and for printed exams select c. exam_name, c. description, count(distinct b. stud_id) as learner_usage from pa_cbt_stud_exam_question a, pa_cbt_stud_cpnt b, pa_cbt_exam_def c where c. exam_name =a. exam_name and a. stud_cpnt_id = b. stud_cpnt_id GROUP BY c. exam_name, c. description; 89
Tables Related to Plateau Online Exams Tables Related to Admins User Prefs, and Security 90
Highlights for Preferences and for Admin Security This presentation captures a variety of details about how customers can locate details such as admin preferences (timezone, locale/language preference) and also admin security (one or more assigned roles and associated workflows and domain restrictions) Notes on Preferences v In previous versions, admins were called users and users were called students. This can be confusing when researching admin settings vs user settings v Key is recognizing user type (from PS_USER_PREFERENCE_USER_TYPE): “A” is admin and “S” is user. Notes on Security v Plateau does not have any out-of-the-box reports related to admin access or security v Key concepts for admin access: An admin can have functional permission actions: Search, View, Edit, Copy, Add, Delete A workflow determines WHAT an admin can do by combining an action + an entity, such as: search for a report, view a user record, edit an item, copy a scheduled offering, add a curriculum, delete a learning event A domain restriction determines WHERE (in which domains) an admin can perform the action on the entity A role is combination of workflows and domain restrictions 91
How Admin Preferences are Stored – Front End UI and Database PA_USER_PREFERENCE USER_ID USER_TYPE PREFERENCES LST_UPD_USR LST_UPD_TSTMP PREFERRED_TIMEZONE CURRENCY_CODE PA_USER_PRFL USER_NAME LNAME FNAME MI EMAIL_ADDR SMTP_SERVER EMAIL_REPLY_ADDR EMAIL_NICKNAME LST_UPD_USR LST_UPD_TSTMP PIN_DATE USER_PASSWORD DMN_ID LOCKED PASSWORD_EXP_DATE SECURITY_QUESTION SECURITY_ANSWER See next slide 92
How Admin Preferences are Stored – XML for PREFERENCES Blob PA_USER_PREFERENCE. preferences is stored as an XML blob field: <? xml version="1. 0" encoding="ISO-8859 -1"? > <preferences> <timezone>America/New_York</timezone> see timezone field <displayscheduleinusertimezone>N</displayscheduleinusertimezone> <locale>English</locale> <dateformat>MMM/d/yyyy</dateformat> <timeformat>hh: mm: aa</timeformat> <longformat>Long_01</longformat> <doubleformat>Double_01</doubleformat> <percentformat>Percentage_01</percentformat> <currencyformat>Currency_01</currencyformat> see currency_code field </preferences> <? xml version="1. 0" encoding="ISO-8859 -1"? > <preferences> <timezone>America/New_York</timezone> <displayscheduleinusertimezone>N</displayscheduleinusertimezone> <locale>English</locale> <dateformat>MMM/d/yyyy</dateformat> <timeformat>hh: mm: aaa</timeformat> <longformat>Long_01</longformat> <doubleformat>Double_01</doubleformat> <percentformat>Percentage_01</percentformat> <currencyformat>Currency_01</currencyformat> </preferences> 93
Data Model for Admin and User Preferences PA_USER_PREFERENCE USER_ID USER_TYPE PREFERENCES LST_UPD_USR LST_UPD_TSTMP PREFERRED_TIMEZONE CURRENCY_CODE PA_USER_PRFL USER_NAME LNAME FNAME MI EMAIL_ADDR SMTP_SERVER EMAIL_REPLY_ADDR EMAIL_NICKNAME LST_UPD_USR LST_UPD_TSTMP PIN_DATE USER_PASSWORD DMN_ID LOCKED PASSWORD_EXP_DATE SECURITY_QUESTION SECURITY_ANSWER PS_USER_PREFERENCE_USER_TYPE (‘A’ = admin, ‘S’ = user) USER_TYPE_DESC LABEL_ID LST_UPD_USR LST_UPD_TSTMP PS_I 18 N_TIMEZONE_ID DESCRIPTION LABEL_ID OFFSET DISPLAY PS_I 18 N_DATE_FMT_PATTERN_TYPE_DESC LABEL_ID PS_I 18 N_NUM_FMT_PATTERN_TYPE_DESC LABEL_ID PS_I 18 N_LOCALE_ID ISO_LANGUAGE_CODE ISO_COUNTRY_CODE LST_UPD_USR LST_UPD_TSTMP PA_CURRENCY_CODE DESCRIPTION ACTIVE IS_DEFAULT SYMBOL LABEL_ID LST_UPD_USR LST_UPD_TSTMP PA_USER_PREFERENCE_DTE_PATTERN USER_ID USER_TYPE LOCALE_ID PATTERN_TYPE LST_UPD_USR LST_UPD_TSTMP PA_USER_PREFERENCE_NUM_PATTERN USER_ID USER_TYPE LOCALE_ID PATTERN_TYPE LST_UPD_USR LST_UPD_TSTMP
How to Research Assigned Admin Roles Log in as an admin who has rights to manage admin accounts. Go to System Admin > Application Admin > Admin Management and locate an admin account Go to Assigned Roles tab to see the list of all security roles for this admin An admin cannot view/edit his or her own roles. So you do not see the Assigned Roles tab if you look at your own record The role with the least restrictive security level will be implemented. In the record shown on the left, the admin has the ALL role. The ALL role has NO restrictions on workflow actions or domains. Therefore this admin has ALL rights and privileges. The PCW TRNGADMIN role will be ignored 95
How to Research Admin Roles - Workflows and Domain Access Log in as an admin who has rights to manage security. Go to System Admin > Security > Role Management and locate the record for one role Go to Workflows tab to see the list of all functional areas that have been associated with this admin role Expand the functional area to see the list of one or more workflows To find other workflows related to a functional area, select add one or more from list Now you know what actions an admin with this role can perform See next slide to research domains to find out WHERE this admin role can perform these workflows 96
How to Research Admin Workflows and Domain Access (cont’d) Log in as an admin who has rights to manage security. Go to System Admin > Security > Domain Restrictions and locate one domain restriction ID Look at the Domains tab to list all included domains Notice that PUBLIC is an open domain to which every admin has access Go to System Admin > Security > Role Management and locate a role Go to the Entity Restr(ictions) tab to see the list of all entities for which this admin has at least one workflow The Domain Restriction ID identifies the domain(s) you researched in the previous slide The State Restriction column can be used to limit access to records that are Active, Inactive, or Both 97
Data Model for Admin Security – Workflows and Access Rights PA_USER_PRFL USER_NAME LNAME FNAME MI EMAIL_ADDR SMTP_SERVER EMAIL_REPLY_ADDR EMAIL_NICKNAME LST_UPD_USR LST_UPD_TSTMP PIN_DATE USER_PASSWORD DMN_ID LOCKED PASSWORD_EXP_DATE SECURITY_QUESTION SECURITY_ANSWER PA_ROLE_ID ROLE_DESC LST_UPD_USR LST_UPD_TSTMP DMN_ID ROLE_TYPE_ID PS_WORKFLOW_ID WORKFLOW_DESC WORKFLOW_GRP_ID WORKFLOW_TYPE LST_UPD_USR LST_UPD_TSTMP LABEL_ID PA_ROLE_WORKFLOW ROLE_ID WORKFLOW_ID LST_UPD_USR LST_UPD_TSTMP PA_USER_PRFL_ROLE USER_NAME ROLE_ID LST_UPD_USR LST_UPD_TSTMP PS_ENTITY_ID ENTITY_DESC ENTITY_GRP_ID HAS_STATE HAS_DMN LST_UPD_USR LST_UPD_TSTMP LABEL_ID PS_FUNCTION FCT_ID FCT_DESC LST_UPD_USR LST_UPD_TSTMP LABEL_ID PS_WORKFLOW_ENTITY_FCT_ID WORKFLOW_ID ENTITY_ID FCT_ID PA_ROLE_ENTITY_ACCESS ROLE_ID (from PA_ROLE) ENTITY_ID DMN_RESTRICTION_ID STATE_RESTRICTION PA_ROLE_WF_ENTITY_FCT_ACCESS ROLE_ID WORKFLOW_ENTITY_FCT_ID DMN_RESTRICTION_ID STATE_RESTRICTION WORKFLOW_TYPE PA_DOMAIN_RESTRICTION DMN_RESTRICTION_ID DMN_RESTRICTION_DESC DMN_ID PS_WF_SEARCH_CTX_WF_ENTITY_FCT and PS_WF_SEARCH_CTS are cross ref tables not used in queries 98
How to Query Admin Roles – Workflows and Domain Access List full details about existing admin accounts, workflows, and domain restrictions SELECT up. user_name, up. fname, up. lname, refa. role_id, refa. entity_id, refa. fct_id, refa. dmn_restriction_id FROM PA_USER_PRFL up, PA_USER_PRFL_ROLE ur, PA_ROLE_ENTITY_FCT_ACCESS refa, PA_ROLE ro WHERE up. user_name = ur. user_name --<AND user_name = this. user> AND ur. role_id = ro. role_id AND ro. role_id != 'ALL' AND refa. dmn_restriction_id is not null AND refa. fct_id like 'View%' ORDER BY up. lname, up. fname, refa. role_id, refa. entity_id; Find all workflows, actions, and domain restrictions for a specific role SELECT rw. workflow_entity_fct_id, rw. dmn_restriction_id, rw. state_restriction FROM PA_ROLE_WF_ENTITY_FCT_ACCESS RW WHERE rw. role_id = 'TC' ORDER BY rw. workflow_entity_fct_id; Find all roles that have any rights to delete one or more records SELECT role_id, workflow_entity_fct_id FROM PA_ROLE_WF_ENTITY_FCT_ACCESS WHERE workflow_entity_fct_id like 'Delete%'; See more queries on Notes page 99
How Plateau Stores History for Admin Accounts v PH_USER_PRFL for changes to admin accounts such as changes to email or name v PH_USER_PRFL_ROLE for changes to admin accounts that involve add (M for modify) or remove (D for delete) roles 100
Tables Related to Plateau Online Exams Tables Related to the SF Online Exams 101
SURVEY TABLES v v v PA_SURVEY_RESPONSES PA_CPNT_SURVEY PA_STUD_SURVEY PA_SURVEY_QUESTION PA_SSG_INST PA_SURVEY_SECTION (page) 102
Tables Related to Plateau Online Exams Tables Related to the SF Training Planner 103
TRAINING PLANNER TABLES: b 1302 v PA_TRAINING_REQUEST_PERIOD v PA_CURRENCY_CONVERSION_RATE v PA_ORG_BUDGET v PA_TRAINING_REQUEST_PROGRAM v PA_TRAINING_REQUEST_LIMIT v PS_TRAINING_REQUEST_STATUS (UNSENT, USER_SENT, SUPERVISOR_SENT, ASSIGNED, DEFERRED, SUPERVISOR_REJECTED) 104
TRAINING PLANNER DATA RELATIONSHIPS 105
- Slides: 105
