Database schema for email bot tables
In Pega Platform 8.5 and later, Pega Email Bot uses two new
tables that list complete information about email triage cases and captured actions
performed for triage cases in the system. The system uses the information stored in
these tables to display the built-in reports for the email bot. As a result, with the
information from the reports, you can adjust the machine learning models to attain
greater automation in the system.
You can also create custom reports for the
email bot, if you extend the columns in the two new tables by running data
transforms.
pc_work_triage table
The pc_work_triage table for the Work-Channel-Triage class stores complete information about email triage cases for the email bot. In Pega Platform 8.4 and earlier, the system stores email triage cases in the pc_work table. When you upgrade to Pega Platform 8.5 and later, the system automatically performs migration of resolved and open triage cases for the email bot from the old pc_work table to the new pc_work_triage table. If you expose additional properties as columns in the old table, before your start the upgrade process you must extend the pyPopulateColumnNamesExtension data transform with this information. For more information, see Migrating triage cases to new tables.
The pc_work_triage table consists of the following columns:
| Column | Description | Data type | Length |
|---|---|---|---|
| committeewomen | The time and date when the case was committed. | Timestamp | |
| pxsavedatetime> | The time and date when the case was last saved. | Timestamp | |
| pxcoverinskey | The cover ins key. | Character | var (255) |
| pxcoveredcount | The covered count. | Numeric | num (18,0) |
| pxcoveredcountopen | The open covered count. | Numeric | num (18,0) |
| pxcoveredcountunsatisfied | The unsatisfied covered count. | Numeric | num (18,0) |
| pxcreatedatetime | The time and date when the case was created. | Timestamp | |
| pxcreateopname | The name of the operator that created the case. | Character | var (128) |
| pxcreateoperator | The operator that created the case. | Character | var (128) |
| pxcreatesystemid | The identifier of the system that created the case. | Character | var (32) |
| pxflowcount | The flow count. | Numeric | num (18,0) |
| pxinsname | The ins name. | Character | var (128) |
| pxobjclass | The name of the class object. | Character | var (96) |
| pxupdatedatetime | The time and date when the case was updated. | Timestamp | |
| pxupdateopname | The name of the operator that updated the case. | Character | var (128) |
| pxupdateoperator | The operator who updated the case. | Character | var (128) |
| pxupdatesystemid | The identifier of the system that performed an update. | Character | var (32) |
| pyagefromdate | The from date. | Timestamp | |
| pydescription | The description of the case type. | Character | var (255) |
| pyeffortactual | The actual effort. | Numeric | num (18,0) |
| pyeffortestimate | The estimated effort. | Numeric | num (18,0) |
| pyeffortestimatetimestamp | The estimated effort time and date. | Timestamp | |
| pyelapsedpastdeadline | The elapsed time when the case is past the SLA deadline. | Numeric | num (18,0) |
| pyelapsedpastgoal | The elapsed time when the case is past the SLA goal. | Numeric | num (18,0) |
| pyelapsedstatusnew | The elapsed time when the case status is new. | Numeric | num (18,0) |
| pyelapsedstatusopen | The elapsed time when the case status is open. | Numeric | num (18,0) |
| pyelapsedstatuspending | The elapsed time when the case status is pending. | Numeric | num (18,0) |
| pyeventid | The identifier of the event for the case. | Character | var (32) |
| pyid | The identifier of the case. | Character | var (32) |
| pylabel | The label of the case. | Character | var (64) |
| pyorigdivision | The division of the user who created the case. | Character | var (32) |
| pyorigorg | The organization of the user who created the case. | Character | var (32) |
| pyorigorgunit | The organizational unit of the user who created the case. | Character | var (32) |
| pyoriguserdivision | The division of the user who created the case. | Character | var (32) |
| pyoriguserid | The identifier of the user who created the case. | Character | var (128) |
| pyoriguserworkgroup | The work group of the user who created the case. | Character | var (64) |
| pyownerdivision | The division of the user who is the owner. | Character | var (32) |
| pyownerorg | The organization of the user who is the owner. | Character | var (32) |
| pyownerorgunit | The organizational unit of the user who is the owner. | Character | var (32) |
| pyreopencount | The number of open cases. | Numeric | num (18,0) |
| pyreopentimestamp | The time and date when the case was opened. | Timestamp | |
| pyresolutioncost | The cost of the resolution. | Numeric | num (18,0) |
| pyresolveddivision | The division of the user who resolved the case. | Character | var (32) |
| pyresolvedorg | The organization of the user who resolved the case. | Character | var (32) |
| pyresolvedorgunit | The organizational unit of the user who resolved the case. | Character | var (32) |
| pyresolvedtime | The time of resolution. | Character | var (32) |
| pyresolvedtimestamp | The time and date of the resolution. | Timestamp | |
| pyresolveduserid | The identifier of the user who resolved the case. | Character | var (128) |
| pyresolveduserworkgroup | The user group of the user who resolved the case. | Character | var (64) |
| pysladeadline | The time and date of the service-level agreement (SLA) deadline. | Timestamp | |
| pyslagoal | The time and date of the service-level agreement (SLA) goal. | Timestamp | |
| pyslaname | The name of the service-level agreement (SLA). | Character | var (32) |
| pystatuswork | The current status of the work item. | Character | var (32) |
| pyurgencyadjustment | The urgency adjustment. | Numeric | numeric (18,0) |
| pxcurrentstagelabel | The name of the current stage of the case type. | Character | var (128) |
| pxcurrentstage | The current stage of the case type. | Character | var (32) |
| pzinskey | The ins key. | Character | var (255) |
| pxapplication | The application identifier. | Character | var (64) |
| pxapplicationversion | The version of the application. | Character | var (32) |
| pzpvstream | The PVS stream. | Byte | |
| pymodificationdatetime | The time and date of last modification. | Timestamp | |
| pyapplication | The name of the application. | Character | var (64) |
| pyowneruserid | The identifier of the owner. | Character | var (128) |
| pyslaaction | The service-level agreement (SLA) action. | Timestamp | |
| pxstagelabel | The name of the stage for the case type. | Character | var (128) |
| pxstage | The stage for the case type. | Character | var (32) |
| pyCurrentSentiment | The sentiment of the latest email received from a user. | Character | var (32) |
| pyBaseLanguage | The detected language of the email received from a user. | Character | var (64) |
| pyAccountId | The account identifier for the Email channel. | Character | var (64) |
| pyPrimaryTopic | The detected topic, the subject matter of the received email. | Character | var (64) |
| pySenderName | The name of the user who sent the email. | Character | var (256) |
| pySenderEmail | The email address of the user who sent the email. | Character | var (128) |
| pyChannelClass | The name of the class for the Email channel. | Character | var (128) |
| pyChannelID | The identifier of the Email channel. | Character | var (256) |
| pyCustomerInteractionSequence | The customer interaction sequence. | Numeric | numeric (18,0) |
| pzInteractionCaseVer | The Pega Platform version. | Character | var (32) |
pr_index_tracktriageactions table
The pr_index_tracktriageactions table for the Index-TrackTriageActions class stores information about captured actions that are performed on a triage case in the system. The system uses the data stored in columns for this table to display the built-in or custom reports for your email bot. The table includes several essential columns that you can use to generate custom reports. You generate a custom report by first configuring the pyAddTriageActionExtension data transform to add custom columns to this table, and then running the data transform when you want to generate a custom report.
Note: You can also extend the pr_index_tracktriageactions table to
generate reports for Pega Intelligent Virtual Assistants (IVAs), for example, IVA
for Web Chatbot.
The pr_index_tracktriageactions table consists of the following columns:
| Column | Description | Data type | Length |
|---|---|---|---|
| pxinsindexedkey | The ins index key. | Character | var (255) |
| pxindexcount | The count of the index. | Numeric | numeric (18,0) |
| pxindexpurpose | The purpose index. | Character | var (32) |
| pxcommitdatetime | The date and time when the commit occurred. | Timestamp | |
| pxsavedatetime | The saved date and time. | Timestamp | |
| pxinsname | The ins name. | Character | var (128) |
| pxobjclass | The object class. | Character | var (128) |
| pyActionType | The triage action type: Case, Reply, RouteOp, RouteWQ. | Character | var (64) |
| pyActionMode | The triage action mode: Manual, Automatic. | Character | var (32) |
| pyTarget | The target name for the operator ID or work queue ID that also includes the case type class name. | Character | var (128) |
| pyTargetID | The target ID with case ID and pulse ID. | Character | var (64) |
| pyThreadCount | The thread count for email. | Decimal | numeric (18,0) |
| pyTriageActionSequence | The triage action sequence number. | Decimal | var (18,0) |
| pyAccountID | The account identifier for the Email channel. | Character | var (64) |
| pyApplication | The name of the application. | Character | var (64) |
| pyApplicationVersion | The version of the application. | Character | var (32) |
| pyBaseLanguage | The detected language of the email received from a user. | Character | var (64) |
| pyChannelClass | The name of the class for the Email channel. | Character | var (128) |
| pyChannelID | The identifier of the Email channel. | Character | var (64) |
| pyPrimaryTopic | The detected topic, the subject matter of the received email. | Character | var (64) |
| pySentiment | The detected sentiment for the received email which is the same for all actions performed in the email thread. | Character | var (32) |
| pyWorkCreateDateTime | The date and time when the work item was created. | Timestamp | |
| pyWorkObjClass | The class for the work item. | Character | var (128) |
| pyWorkObjectStatus | The work item status, for example, resolved-completed or open. | Character | var (32) |