Call center application data and interoperation architecture for a telecommunication service center

[0001] This application claims the benefit of priority under 35 U.S.C. §119 to Indian Patent Application No. 73/MUM/2008, filed Jan. 9, 2008, in India.

[0002] 1. Technical Field

[0003] This application relates to data processing systems, and in particular, to a call center system architecture.

[0004] 2. Related Art

[0005] The rapid growth of information technology has led to increasingly complex and sophisticated products and services. In an effort to provide meaningful assistance to customers, companies have made significant investments in call centers. The primary goals of a call center include working with customers to resolve issues, answering questions, and providing ongoing support for products and services.

[0006] Despite the general level of sophistication of a modern call center, challenges still remain. In particular, many areas are still prone to error, and suffer from the need to perform repetitive manual labor to carry out tasks. Furthermore, call center data was often managed in a chaotic manner, leading to data redundancy and data loss. At the same time, data manipulation was hectic and required a high level of skill for even routine maintenance. These drawbacks limited the ability of the call center to provide timely, accurate, and automated reports; to secure data; and to provide real-time data tracking.

[0007] A need exists for an improved call center application architecture.

[0008] A call center application data and interoperation architecture (“architecture”) provides a centralized design for managing the many applications that cooperate to provide call center functionality. The architecture coordinates information flow through a master application data repository and provides tailored support for applications that handle all aspects of call center functionality. The architecture adeptly handles information flow for all call center applications and for the employees who use the applications, including managers, supervisors, and call handlers.

[0009] The applications implement functionality applicable across the complete span of the employment cycle from hiring to termination, and well as other support functions. The applications may implement functionality including human resources functions, employee attendance tracking, and employee attrition management. Other examples of application functionality include employee leave management, employee commitment logging, and identification management. The applications are not limited to voice specific applications (e.g., teleconference based problem resolution), but may also encompass non-voice processes (e.g., email or Internet based order processing).

[0010] The architecture may be implemented in a network-based tool that provides significant improvements with respect to the labor intensive processes and data integrity challenges described above. The network may include local and remote networks, or interconnections of networks, whether private and internal, or publicly accessible. The network may include Internet based connectivity for local or remote access to the applications and the data stored in the master application data repository. Furthermore, the architecture provides both visibility and collaboration across the call center and its applications.

[0011] The dynamic environment of a call center means that resources, such as employees or call center equipment, are moved across processes very often. A process may be any instance of call center functionality, such as fielding technical inquiries from callers about specific products, activating products or services, or placing and confirming orders for products or services. The wide variety of processes and frequent resource movement across processes gives rise to a need to track the resources in a systematic manner. In this regard, the architecture facilitates, for example, tracking the processes for which any call handler or supervisor has worked during his or her tenure with the call center. The architecture may also track other relevant details, such as the call handler's duration in each process, subsequent supervisors and performance data, and other details.

[0012] Furthermore, the architecture tracks information about each employee within the system. The architecture may track information about each employee at every defined level of hierarchy. The hierarchy may reflect relative position or rank, such as manager->supervisor->call handler, with the architecture maintaining information on each employee at each level in the hierarchy. The architecture also supports, at every defined level in the hierarchy, specific individual access rights for all employees. The access rights may be set depending upon employee level in the hierarchy, the process the employee belongs to, and other factors.

[0013] As noted above, the architecture includes a master application data repository. In one implementation, the master application data repository adheres to a master data organization schema. The master data organization schema is unified across multiple call center application dataset requirements in that the master data schema handles all of the information flow generated by the call center applications. The master data organization schema is partitioned along a schema subdivision that defines multiple individual application data schemas that are specific to individual application dataset requirements for different call center applications.

[0014] A communication interface receives call center application data requests from the call center applications. A processor and supporting software (e.g., database management software) process the call center application data requests. In particular, the processor performs data manipulation responsive to the call center application data requests, while working with the specific application data schema in the master application data repository. Data manipulation may include adding, deleting, or changing data fields on a per-application basis, such as adding employee attendance records on a daily basis, changing the processes to which an employee is assigned, or deleting completed commitments from a commitment requirements list, when the employee has met the commitment. The architecture thereby relieves the call center applications from local database and processing overhead.

[0015] Other systems, methods, features and advantages will be, or will become, apparent to one with skill in the art upon examination of the following figures and detailed description. It is intended that all such additional systems, methods, features and advantages be included within this description, be within the scope of the invention, and be protected by the following claims.

[0043] FIG. 1 shows a call center application and interoperation architecture 100 (“architecture 100”). The architecture 100 provides a consistent and timely view of key records and improves the individual performance by empowering agents with their own information. The architecture 100 also provides the ability to drill from dashboards into analytical and detailed reports, both historical and real-time, for root cause analysis. A central repository of all the events in the organization provides accurate, timely, and secure data storage.

[0044] In particular, the central repository may be implemented as a master application data repository 102. In the master application data repository 102, data is organized according to a master data organization schema 104. The master data organization schema 104 will be described in more detail below.

[0045] The architecture 100 coordinates the processing of multiple call center applications and their interactions with the master application data repository 102. Examples of applications shown in FIG. 1 include a Case Management application, have three logical parts: an Accounts Integrity Management (“AIM”) application 106, an online order processing (“KANA”) application 108, and a Sales Order Entry (SOE) application 110. The AIM application 106 may, for example, provide a GUI and processing logic that allow users to correct account errors or otherwise maintain the integrity of any account. The KANA application 108 may provide a GUI and processing logic for processing online web orders. The SOE application 110 may provide a GUI and processing logic for processing telephone orders. Thus, once a customer subscribes over the phone, the SOE team loads the order into the system through the SOE application 110.

[0046] Other examples of applications include the Commitment Log application 112, the Attrition application 114, the Identification Management application 116, the Employee Details application 118, and the Leave application 120. Additional examples of applications include the Attendance application 122, the Transport application 124, the Movement Management application 126, and the human resources (HRIS) application 128.

[0047] The architecture 100 implements a hub and spoke design. The applications 106-120 exchange data with the master application data repository 102, which relieves each individual application of local database overhead and processing requirements. Additional, different, or fewer applications may be running in the architecture 100.

[0048] FIG. 1 also shows that the architecture 100 provides administrative access for the administration team 130. The administration team 130 may interact with the architecture 100 through a user interface through which authorized users may update personnel data maintained by the weekly roster module 132, or may make other changes to data stored in the master application data repository 102. In addition, a scheduling team 134 may provide input into the weekly roster module 132. Accordingly, the weekly roster application 132 maintains an up-to-date view of the employees and their schedules assigned to any particular process for a particular week. Furthermore, an upload process 136 may be established to manually or automatically upload attendance data into the attendance application 122.

[0049] Call centers work on shifts, and any employee can be scheduled to work in any shift. The schedule includes their working days and weekly offs (their time off). The scheduling team 134 prepares rosters of employees in shifts based on many different parameters. The architecture 100 makes the roster available every week (e.g., in an Excel™ spreadsheet format). The administration team 130 or scheduling team 134 enters weekly employee rosters into the architecture 100 (e.g., into the attendance module) through the upload process 136.

[0050] The processing in the architecture 100 may be hosted on an Internet Information Web Server (IIS). The processing may be implemented, as examples, in C# and using ASP.NET, with the data stored in a SQL 2000 database. For example, the databases and data schemas described above may be implemented with Microsoft® SQL Server 2000, Microsoft SQL Server version 7.0, or other database server technology. The relational database engine can return data as Extensible Markup Language (XML) documents. Additionally, XML can also be used to insert, update, and delete values in the database.

[0051] Furthermore, the architecture may run multiple instances of the relational database engine on the same or different machine. Each instance may have its own set of system and user databases. Applications can connect to each instance on a single machine similar to the way they connect to instances running on different machines.

[0052] Other technology that may be used to implement the architecture includes Asynchronous JavaScript and XML (AJAX) to create interactive Web applications, XHTML, for stricter, cleaner rendering of HTML into XML, Cascading Style Sheets (CSS) for marking up and adding styles, Javascript Document Object Model (DOM) for providing content, structure and style of a document to be dynamically accessed and updated, and an XMLHttpRequest object which exchanges data asynchronously with the Web server and reduces the need to continually fetch resources from the server.

[0053] Since data can be sent and retrieved without requiring the user to reload an entire Web page, small amounts of data can be transferred as and when required. Moreover, page elements can be dynamically refreshed at any level of granularity to reflect this. Unchanged information need not be sent across the network. The implementation may be standardized around the W3C ‘Document Object Model (DOM) Level 3 Load and Save Specification’ or other specification.

[0054] In addition to the applications described above, the architecture 100 may provide additional functionality. For example, the architecture 100 may provide an employee information access application 136 (“My OneView”). The My OneView application 136 may be accessed by any employee to review leave balance, schedules, holiday lists, and other aspects of employment associated with the employee. Additional examples are described in more detail below.

[0055] FIG. 2 shows another view of the call center application data and interoperation architecture 100 including a schema subdivision 202 defining multiple individual application data schemas 204-218 for specific applications. The schema subdivision 202 may be a logical or physical partitioning of database tables, fields, and other structures into sets of tables, fields, and other information specific to individual call center applications. In addition, the master data organization schema 104 defines a common application data schema 220. The common application data schema 220 may include fields, tables or other structures that multiple applications use to share data or have common access to in order to support their processing roles.

[0056] examples of individual application data schemas in FIG. 2 include: a Case Management AIM data schema 204, a Commitment Log data schema 206, a Case Management KANA data schema 208, and an Attrition data schema 210. Additional examples include: a leave a data schema 212, a movement data schema 214, a Case Management SOE data schema 216, and an attendance data schema 218. The data schemas 204-218 maintain the data specific to a particular application. More specifically, each data schema 204-218 supports a specific application data set requirement. For example, the Attrition data schema 210 includes tables and fields for maintaining any data that the Attrition application 114 manipulates, sends, receives, displays, or otherwise processes in performing its attrition management, tracking, and reporting role. The subdivision 202 ensures a clean separation between the data set requirements for each call center applications, rather than smearing all of the data set requirements across a set of tables and fields common to multiple applications.

[0057] The individual applications may communicate with the rest of the architecture 100 over one or more local or remote networks or interconnection of such networks. The individual applications may submit data requests, and receive data responses from the architecture 100 as they perform their processing. To that end, the architecture 100 implements a communication interface 222, and includes a processor 224 that may receive the data requests, process the data requests, and return data responses to the individual applications. More specifically, a memory 226 may store data request processing logic 228, such as a database management application. The data request processing logic 228 receives data requests (such as the attendance data request 230 and the attrition data requests 232). In, response, the data request processing logic 228 process the data requests by performing data manipulation (e.g., database queries, or database reads, writes, or updates) responsive to the data requests. The data request processing logic 228 adheres to the application data schemas in the master application data repository 102 so that specific application data schemas support specific call center application. One result is that the architecture 100 relieves the call center applications 106-128 from local database processing overhead, and provides centralized storage and control over the call center data across multiple applications.

[0058] Specific examples of call center application data schemas are given in the tables below.

[0059] The following Tables 1-4 show a transport application data schema that may support the Transportation application 124.

[0060]

TABLE 1
ADHOC_tbl_RequestAction
This table stores information about adhoc transportation requests.
Data field	Data Type	Constraints	Description
inActionId	Integer	Primary Key	Unique identifier to identify the possible actions
			that can be taken for adhoc transport requests.
vcActionName	string	Not Null	Describe whether adhoc is
			approved/denied/forwarded.
vcActionDescription	string	Not Null
vcNextPossibleAction	string	Null	Describe adhoc approved/denied/forwarded.
inActionType	integer	Not Null	1. Logged
			2. Forwarded/Approved
			3. Closed
inSquenceOrder	Integer	Not Null	Order in which to display the actions
lsbolActive	bit	Not Null	Whether the current action can be executed
			(Active)or not (Inactive).
dtCreatedOn	date	Not Null	Date on which the action was entered into the
			database.
inCreatedBy	numeric	Not Null	The action was created by which user

[0061]

TABLE 2
ADHOC_tbl_RequestDetails
This table describes the action taken on the adhoc
transportation requests.
Data field	Data Type	Constraints	Description
inDetailId	numeric	Primary Key	Unique identifier to identify
			the possible actions that
			can be taken for adhoc
			transport requests.
inAdhocId	numeric	Null	Reference from (Foreign Key)
			Adhoc_tbl_RequestMst.
inHeadID	numeric	Null	Reference from (Foreign Key)
			Adhoc_tbl_RequestHead
inStatus	integer	Null	1. Adhoc requested.
			2. Forwarded.
			3. Approved.
			4. Denied.
			5. Sent to transport.
inRequestedBy	numeric	Null	Employee who is requesting.
dtRequestedOn	date	Null	Date on which Adhoc is
			requested.
inClosedBy	numeric	Null	Supervisor who took action.
dtClosedOn	date	Null	Date on which Adhoc request
			is closed.
vcComments	string	Null	Wether approved/denied.

[0062]

TABLE 3
ADHOC_tbl_RequestHead
The table stores the information when adhoc transportation is
required by the employee and when it is approved.
Data field	Data Type	Constraints	Description
inHeadId	numeric	Primary Key	Unique identifier to identify
			the possible actions that can
			be taken for adhoc
			transport requests.
inAdhocId	numeric	Not Null	Reference from (Foreign Key)
			Adhoc_tbl_RequestMst.
inDetailId	numeric	Null	Reference from (Foreign Key)
			Adhoc_tbl_RequestDetails.
inRequestedBy	numeric	Not Null	Employee who is requesting
			for the transport.
dtRequestedOn	date	Not Null	Date on which the Adhoc has
			been requested.
inApprovedBy	numeric	Null	Supervisor who is approving.
dtApprovedOn	date	Null	Date on which it has
			been approved.
bolIsClosed	bit	Not Null	Approved/Denied

[0063]

TABLE 4
ADHOC_tbl_RequestMst
The table contains the ticket number and other
related information about the employee.
Data field	Data Type	Constraints	Description
inAdhocId	numeric	Primary Key	Unique identifier to identify the possible actions
			that can be taken for adhoc transport requests.
vcTicketNo	string	Null	New ticket is raised for each new request.
dtAdhocdate	date	Not Null	Date for which Adhoc is requested.
inTeamId	integer	Not Null	Team Leader Name.
inProcessId	integer	Not Null	Process which employee belongs.
inUserId	numeric	Not Null	Employee ID.
inSupervisorId	numeric	Not Null	Supervisor ID.
dtAdhocTime	date	Not Null	Time at which adhoc is required.
dtStarttime	date	Not Null	Shift start time.
dtEndtime	date	Not Null	Shift end time.
inAdhocType	integer	Not Null	1. Pick
			2. Drop
inReason	integer	Not Null	Team Meeting/Official
			Official.
vcComments	string	Not Null	Reason for Adhoc request.
dtCreatedOn	date	Not Null	Date on which adhoc is raised.
inCreatedBy	numeric	Not Null	Employee ID.

[0064] The following Tables 5-11 show a Commitment Logs data schema 206 that may support the Commitment Logs application 112. FIG. 5 provides additional detail. The career/organizational levels in may be defined from A to H, with A being the top level. For example, people in Level E may be team leaders, F may be Subject Matter Experts and G & H may be team member/Customer Support Representatives having similar work profiles and commitments.

[0065]

TABLE 5
AGM_tbl_CommitmentLogMst (Table 1002)
The table describes the commitment the Team Leader (“TL”) and
the Deputy Manager (“DM”) has to perform.
Data field	Data Type	Constraints	Description
inLogId	integer	Primary Key	Unique identifier to identify the possible Log
			Activity.
inLogType	integer	Not Null	1. TL Activity.
			2. DM Activity.
vcLogName	string	Not Null	1. Side by side monitoring.
			2. Remote monitoring.
			3. Team huddle
			Etc.
vcDescription	string	Null	1. 2 Per agent per month.
			3. per agent per month.
			3. Daily.
			Etc.
vcOrganizationLevel	string	Null
inCommitmentType	integer	Null	1. F, G + H Level.
			2. G + H Level.
bolIsActive	bit	Not Null	Status is Active/de-active.
inCreatedBy	numeric	Not Null	Employee ID.
dtCreatedOn	date	Not Null	Date on which
			Created.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date on which it is modified.

[0066]

TABLE 6
AGM_tbl_CommitmentLogTarget (Table 1006)
Target to be achieved by the supervisor.
Data field	Data Type	Constraints	Description
inTargetId	integer	Primary Key	Unique identifier to identify the possible Log
			Activity Target.
inLogId	integer	Not Null	Reference from (Foreign Key)
			AGM_tbl_CommitmentLogMst.
inProcessId	integer	Not Null	Process for which commitment are taken.
inOrganizationLevel	integer	Null	1. E
			2. F
			3. G + H
inLogType	integer	Null	1. TL Activity.
			2. DM Activity.
inCommitmentType	integer	Null	1. Team Commitment.
			2. Individual Commitment.
inTarget	integer	Null	Target Achieved.
inCreatedBy	numeric	Null	Employee ID.
dtCreatedOn	date	Null	Date on which created.

[0067]

TABLE 7
AGM_tbl_CommitmentLogTarget_History (Table 1008)
The table describes the history of commitment targets that have been done.
Data field	Data Type	Constraints	Description
inTargetId	integer	Primary Key	Unique identifier to identify the possible Log
			Activity Target.
inLogId	integer	Not Null	Reference from (Foreign Key)
			AGM_tbl_CommitmentLogMst.
inProcessId	integer	Not Null	Process for which commitment are taken.
inLogType	integer	Not Null	1. TL Activity.
			2. DM Activity.
inCommitmentType	integer	Not Null	1. Team Commitment.
			2. Individual Commitment.
inOrganizationLevel	integer	Null	1. E
			2. F
			3. G + H
inTarget	integer	Not Null	Target Achieved.
inCreatedBy	numeric	Not Null	Employee ID.
dtCreatedOn	date	Not Null	Date on which created.
inModifiedBy	numeric	Not Null	Employee ID.
dtModifiedOn	date	Not Null	Date on which modified.

[0068]

TABLE 8
AGM_CommitmentLogType (Table 1010)
The table describes the commitment of TL and DM.
Data field	Data Type	Constraints	Description
inLogType	integer	Primary Key	Unique identifier to identify
			the possible Log Activity
			Target.
vcLogType	string	Null	1. TL Activity.
			2. DM Activity.
vcLevels	string	Null	1. Senior Manager
			2. Manager
			3. Deputy Manager
bolIsActive	bit	Null	Active/de-active.
inCreatedBy	numeric	Null	Employee ID.
dtCreatedOn	date	Null	Date on which created.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date on which modified.

[0069]

TABLE 9
AGM_UserCommitment_Achieved (Table 1004)
This table describes commitments achieved.
Data field	Data Type	Constraints	Description
inAchievedId	numeric	Primary Key	Unique identifier to identify the possible Log
			Activity Target.
dtAchievedOn	date	Null	Date on which target achieved,
inCommitmentTo	numeric	Null	Employee ID.
inLogId	integer	Not Null	Reference from (Foreign Key)
			AGM_tbl_CommitmentLogMst.
inCommitmentBy	numeric	Not Null	Employee ID.
inLogType	integer	Not Null	1. TL Activity.
			2. DM Activity.
inCommitmentType	integer	Not Null	1. TL Activity.
			2. DM Activity.
inLevelId	integer	Null	G + H
inTargetAchieved	integer	Not Null	Targets that are achieved.
inProcessId	integer	Null	Process.
vcComments	string	Null	One on One.
inMonth	integer	Null	How many completed.
inYear	integer	Null	Which year.

[0070]

TABLE 10
AGM_tbl_UserCommitment_Schedule (Table 1014)
The table describes the commitments that are scheduled for the week.
Data field	Data Type	Constraints	Description
inScheduleId	numeric	Primary Key	Unique identifier to identify the possible Activity
			Scheduled.
inUserId	numeric	Null	Employee ID.
inProcessId	integer	Null	Process Name.
dtCommitmentdate	date	Null	Date on which committed.
dtFromtimeStamp	date	Null	Starting date.
dtTotimeStamp	date	Null	End date.
inLogId	integer	Null	Reference from (Foreign Key)
			AGM_tbl_CommitmentLogMst.
inLogType	integer	Null	1. TL Activity.
			2. DM Activity.
inCommitmentType	integer	Null	1. Individual Commitment.
			2. Team Commitment.
inScheduledTarget	integer	Null	Target to be achieved.
vcComments	string	Null	Reason.
inMonth	integer	Null	How many completed.
inYear	integer	Null	How many in one year.
inCreatedBy	numeric	Null	Employee ID.
dtCreatedOn	date	Null	Date on which created.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date on which modified.

[0071]

TABLE 11
AGM_tbl_UserCommitment_Target (Table 1012)
The table describes the target that are to be achieved.
Data field	Data Type	Constraints	Description
inTargetId	numeric	Primary Key	Unique identifier to identify the possible Target.
inCommitmentBy	numeric	Not Null	Employee ID.
inCommitmentTo	numeric	Not Null	1. TL
			2. DM.
inLogId	integer	Not Null	Reference from (Foreign Key)
			AGM_tbl_CommitmentLogMst.
inLogType	integer	Not Null	1. TL Activity.
			2. DM Activity.
inCommitmentType	integer	Not Null	1. Individual Commitment.
			2. Team Commitment.
inLevelId	integer	Null	G + H.
inTargetAssigned	integer	Not Null	Total target assigned.
inTargetAchieved	integer	Not Null	Target achieved.
inProcessId	integer	Null	Process Name.
inMonth	integer	Null	Achieved each month.
inYear	integer	Null	Achieved in year.

[0072] The following Table 12 shows a human resources IS (HRIS) data schema that may support the HRIS application 128 roster functionality.

[0073]

TABLE 12
HRIS_RPT_ViewRoster
This table describes the user who has viewed the data.
Data field	Data Type	Constraints	Description
inViewUser	numeric	Not Null	Employee ID.
dttimeStamp	date	Not Null	Date on which viewed.
inUserId	numeric	Not Null	Employee ID.
inLocationId	integer	Null	1. Mumbai
			2. Bangalore.
vcLocation	string	Null	Bangalore/Mumbai.
vcEmployeeCode	string	Null	Employee ID.
vcEmployeeName	string	Null	Employee Name.
inProcessId	integer	Null	1. Dial
			2. Dsl
			3. Felix
vcProcessName	string	Null	Felix/Dsl/Dial.
inTeamId	integer	Null	Supervisor ID.
vcTeamName	string	Null	Supervisor Name.
vcShiftStart	string	Null	Start Time.
vcShiftEnd	string	Null	End Time.
vcStatus	string	Null	1. Scheduled.
			2. Weekly Off

[0074] The following Tables 13-16 show an Attendance data schema 218 that may support the attendance application 122. FIG. 11 provides additional detail.

[0075]

TABLE 13
HRIS_tbl_Attendance_Details (Table 1108)
This table saves the information about the attendance of the employee.
Data field	Data Type	Constraints	Description
inDetailsid	numeric	Primary key	Unique identifier to identify the details.
inHeadId	numeric	Not Null	Reference from(Foreign Key)
			ADHOC_tbl_RequestHead
dtAttendance	date	Not Null	Present day.
inUserID	numeric	Not Null	Employee ID.
inParameterID	integer	Not Null	Reference from(Foreign Key)
			HRIS_tbl_AttendanceParameter
inRosterId	numeric	Null	Reference from(Foreign Key)
			HRIS_tbl_RosterHead.
inRosterType	integer	Null
inAttritionId	numeric	Not Null	Reference from(Foreign Key)
			HRIS_tbl_Attrition_Mst.
bolSelfMarked	bit	Not Null	1. Self Marked.
			2. Supervisor Marked.
vcComments	string	Null	1. Attendance Marked.
			2. Attendance not Marked.
inLocationId	integer	Null	1. Mumbai
			2. Bangalore
inDesignation	integer	Null	1. Analyst
			2. Agent
inMarkedBy	numeric	Null	Employee ID.
dtMarkedOn	date	Null	Date on which attendance is marked.

[0076]

TABLE 14
HRIS_tbl_Attendance_Head (Table 1102)
This table has the information about the Employee Status.
Data field	Data Type	Constraints	Description
inHeadId	numeric	Primary Key	Unique identifier to identify the details.
dtAttendance	date	Not Null	Attendance date.
inTeamID	integer	Not Null	Reference from(Foreign Key)
			Sys_tbl_ProcessTeams.
inParentTeam	integer	Null	Reference from(Foreign Key)
			Sys_tbl_ProcessTeams.
inProcessID	integer	Not Null	1. Dsl
			2. Dial
			3. Felix
inSupervisorID	numeric	Not Null	Supervisor ID.
inHeadCount	integer	Null	Total Strength.
inPreRosterabsenteeism	integer	Null	Employee who is rostered but not present.
inPostRosterabsenteeism	integer	Null	Employee who has informed before the roster.
inNotMarked	integer	Null	Total attendance not marked.
inWeeklyOffs	integer	Null	Total weekly Off.
inPresent	integer	Not Null	Total Present.
inAttrition	integer	Null	Total Attrition.
inSickLeaves	integer	Null	Total Sick Leave.
inUnauthorisedLeaves	integer	Not Null	Not approved leaves
bolVerified	bit	Not Null	If verified.
inVerifiedby	numeric	Null	Employee ID.
dtVerifiedOn	date	Null	Date on which verified.

[0077]

TABLE 15
HRIS_tbl_Attendance_History
This table contains the history of the employee attendance.
Data field	Data Type	Constraints	Description
inDetailsid	numeric	Not Null	Unique identifier to identify the details.
inHeadId	numeric	Not Null	Reference from(Foreign Key)
			HRIS_tbl_Attendance_Head
dtAttendance	date	Not Null	Attendance Date.
inUserID	numeric	Not Null	Employee ID.
inParameterID	integer	Not Null	1. Sick Leave
			2. Present
			3. Planned Leave
inOldParameterID	integer	Not Null
inRosterId	numeric	Null	Reference from(Foreign Key)
			HRIS_tbl_RosterHead
inRosterType	integer	Null
inAttritionId	numeric	Not Null	Reference from(Foreign Key)
			HRIS_tbl_Attrition_Mst
bolSelfMarked	bit	Not Null	If attendance is self marked.
vcComments	string	Null	Remarks.
inMarkedBy	numeric	Null	Employee ID.
dtMarkedOn	date	Null	Date on which marked.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date on which modified.

[0078]

TABLE 16
HRIS_tbl_AttendanceParameter (Table 1110)
This table contains the parameter about the
status of employee like present, leave.
Data field	Data Type	Constraints	Description
inParameterID	integer	Primary Key	Unique identifier to
			identify the details.
inParameterType	integer	Not Null
vcParameter	string	Not Null	1. Present
			2. Sick Leave
			3. Planned Leave
vcCode	string	Not Null	1. P
			2. SL
			3. PL
vcNotes	string	Null	1. Present
			2. Paid Leave
			3. Sick Leave
chSexApplicable	string	Not Null	Null
bolHoliday	bit	Not Null	1. Present
			2. Weekly Off
bolActive	bit	Not Null	Active.
bolShowBalance	bit	Not Null	Balance.
inSeqOrder	integer	Not Null	Sequence.
inCreatedBy	numeric	Not Null	Employee ID.
dtCreatedOn	date	Not Null	Date of Creation.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date of Modification.

[0079] The following Tables 17-20 show an Attrition data schema 210 that may support the Attrition application 114. FIG. 13 provides additional detail.

[0080]

TABLE 17
HRIS_tbl_Attrition_History (Table 1302)
The table contains the history of attritioned employees.
Data field	Data Type	Constraints	Description
inAttritionID	numeric	Primary Key	Unique identifier to identify the details.
vcTicketNo	string	Null	Ticket for each attrition.
inAttritionType	integer	Not Null	1. Approval Pending
			2. Approved
			3. Released
inProcessID	integer	Not Null	1. Dial
			2. Dsl
			3. Felix
inTeamID	integer	Not Null	Reference from(Foreign Key)
			Sys_tbl_ProcessTeams
inSupervisorID	integer	Not Null	Supervisor ID.
inUserID	integer	Not Null	Employee ID.
inHrRepresntative	decimal	Not Null	HR ID.
inAttritionStatus	integer	Not Null	1. Approval Pending
			2. Approved
			3. Released
dtResignation	date	Not Null	Date of resignation.
dtLastWorking	date	Null	Last working day.
inReasonId	integer	Not Null	Reason ID.
bolNoticePeriodWaiver	bit	Not Null	Notice period.
Boldocumentrecived	bit	Not Null	Document received.
dtInitiatedOn	date	Not Null	Date on which resigned.
inInitiatedBy	decimal	Not Null	Employee ID.
vcComments	string	Null	1. Resigned
			2. Absconding
			3. Approved
inApprovedBy	numeric	Null	Employee ID
dtApprovedOn	date	Null	Date on which approved.
vcApproverComments	string	Null	Employee ID.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date of modification.
vcReason2	string	Null	Reason for resigning.
vcReason3	string	Null	Reason for resigning.
vcEmpPerformance	string	Null	Grade

[0081]

TABLE 18
HRIS_tbl_Attrition_Mst (Table 1304)
The table contains the status of Attrition e.g., released.
Data field	Data Type	Constraints	Description
inAttritionID	numeric	Primary Key	Unique identifier to identify the details.
vcTicketNo	string	Null	Ticket for each attrition.
inAttritionType	int	Not Null	1. Approval Pending
			2. Approved
			3. Released
inProcessID	int	Not Null	1. Dial
			2. Dsl
			3. Felix
inTeamID	int	Not Null	Reference from(Foreign Key)
			Sys_tbl_ProcessTeams
inSupervisorID	int	Not Null	Supervisor ID.
inUserID	int	Not Null	Employee ID.
inHrRepresntative	decimal	Not Null	HR ID.
inAttritionStatus	int	Not Null	1. Approval Pending
			2. Approved
			3. Released
dtResignation	date	Not Null	Date of resignation.
dtLastWorking	date	Null	Last working day.
inReasonId	integer	Not Null	Reason for leaving.
bolNoticePeriodWaiver	bit	Not Null	Notice period completed.
Boldocumentrecived	bit	Not Null	Document received.
dtInitiatedOn	date	Not Null	Date on which resigned.
inInitiatedBy	decimal	Not Null	Employee ID.
vcComments	string	Null	1. Resigned
			2. Absconding
			3. Approved
inApprovedBy	numeric	Null	Employee ID
dtApprovedOn	date	Null	Date on which approved.
vcApproverComments	string	Null	Employee ID.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date of modification.
vcReason2	string	Null	Reason for resigning.
vcReason3	string	Null	Reason for resigning.
vcEmpPerformance	string	Null	Grade

[0082]

TABLE 19
HRIS_tbl_Attrition_PerformanceFeedback (Table 1306)
This table describes about the feedback of employee during
his/her tenure.
Data field	Data Type	Constraints	Description
inAttritionID	numeric	Primary Key	Unique identifier to identify the
			details.
inUserID	integer	Not Null	Employee ID.
vcQuestion1	string	Null	Performance
vcQuestion2	string	Null	Regular
vcQuestion3	string	Null	Other queries.
vcQuestion4	string	Null	Other queries
vcQuestion5	string	Null	Other queries
vcQuestion6	string	Null	Other queries
vcQuestion7	string	Null	Other queries
vcQuestion8	string	Null	Other queries
vcQuestion9	string	Null	Other queries
dtCreatedOn	date	Null	Date on which created.
inCreatedBy	numeric	Null	Employee ID.

[0083]

TABLE 20
HRIS_tbl_Attrition_Staus (Table 1308)
This table has the status of attrition (Approved/Released).
Data field	Data Type	Constraints	Description
inStatusId	integer	Primary Key	Unique identifier to
			identify the details.
vcStatus	String	Null	1. Approval Pending
			2. Approved
			3. Released back
Inactive	bit	Not Null	Active/de-active
vcNotes	string	Null	1. Approval Pending
			2. Approved
			3. Released back
vcNextPossibleStatus	string	Null	1. Approved
			2. Released back

[0084] The following Tables 21-22 show additional tables for the HRIS data schema.

[0085]

TABLE 21
HRIS_tbl_CompensatoryOff
This table has the information of employee Compensatory Off time.
Data field	Data Type	Constraints	Description
inUserid	numeric	Primary Key	Unique Employee ID.
dtCompensatoryOff	date	Primary Key	Unique Date.
vcReason	string	Null	Reason for Comp Off
inCreatedOn	date	Not Null	Date on which created.
inAppliedBy	numeric	Null	Employee ID.
dtAppliedOn	date	Null	Date on which applied.

[0086]

TABLE 22
HRIS_tbl_dayInterval
This table is used to manage the intervals in a day for retrieving
certain reports.
Data field	Data Type	Constraints	Description
inActionID	integer	Primary Key	Unique identifier to identify
			the details.
vcParameter	string	Null	Range of selection.
vcValue	string	Null	1. 1-30
			2. 31-60
			3. All
dtCreatedOn	date	Null	Date on which created
inCreatedBy	numeric	Null	Employee ID.

[0087] The following Tables 23-26 show tables for the movement data schema 214 that supports the Movement application 126. FIG. 12 provides additional detail.

[0088]

TABLE 23
HRIS_tbl_Movement_Details (Table 1202)
The table describe about the movement of the Employee from one
process to another.
Data field	Data Type	Constraints	Description
inDetailsid	numeric	Primary Key	Unique identifier to
			identify the details.
inMovementId	numeric	Not Null
inAllocatedBy	numeric	Null	Employee ID.
dtAllocatedOn	date	Null	Date on which allocated.
inActionedGroup	integer	Null
inStatusId	integer	Null
inActionedBy	numeric	Null	Employee ID.
dtActionedOn	date	Null	Date on which Movement
			is to be done.
vcComments	string	Null	Reason for movement.

[0089]

TABLE 24
HRIS_tbl_Movement_Head (Table 1204)
This table describes the movement of an employee from process to process.
Data field	Data Type	Constraints	Description
inMovementId	numeric	Primary Key	Unique identifier to identify the details.
vcTicketNo	string	Null	For every movement there is a new ticket.
dtMovementOn	date	Not Null	Date on which movement is done.
inMovementType	integer	Not Null	1. Movement In
			Movement Out
			3. Movement with in the process
inDetailsid	numeric	Null	Reference from(Foreign Key)
			HRIS_tbl_Movement_Details
bolIsClosed	bit	Not Null	Active/de-active
vcComments	string	Null	Reason for movement
inReasonId	integer	Not Null
inProcessFrom	integer	Not Null	Process from which movement is done.
inProcessTo	integer	Not Null	Process to which moving.
inTeamFrom	integer	Not Null	Team Name
inTeamTo	integer	Not Null	Team Name
inSupervisorFrom	numeric	Not Null	Supervisor Name
inSupervisorTo	numeric	Not Null	Supervisor Name
vcMovementUsers	string	Null	Selected Employees.
bolImplemented	bit	Null	Yes/No
inCreatedBy	integer	Not Null	Employee ID.
dtCreatedOn	date	Not Null	Date On which created.
dtModifiedOn	date	Null	Date on which Modified.
inModifiedBy	integer	Null	Employee ID.

[0090]

TABLE 25
HRIS_tbl_Movement_History (Table 1208)
This table contains information about movements.
Data field	Data Type	Constraints	Description
inUserID	Numeric	Primary Key	Unique Employee ID.
inProcessId	Integer	Not Null	1. Dsl
			2. Dial
			3. Felix
inTeamId	Integer	Not Null	Reference from(Foreign Key)
			Sys_tbl_ProcessTeams
inSupervisor	Numeric	Not Null	Supervisor ID.
dtFromDate	Date	Primary Key	Unique Date.
dtTodate	Date	Null	Upto which Date.
inMovementId	Numeric	Null	Reference from(Foreign Key)
			HRIS_tbl_Movement_Head
dtCreatedOn	Date	Not Null	Date on which Created.
inCreatedBy	Integer	Not Null	Employee ID.

[0091]

TABLE 26
HRIS_tbl_Movement_Status (Table 1206)
The table states the movement status of the ticket.
Data field	Data Type	Constraints	Description
inStatus	Integer	Primary Key	Unique identifier to identify
			the details.
vcStatus	String	Null	1. Requested.
			2. Forwarded.
			3. Approved.
			4. Canceled.
vcActionedGroup	String	Null	Active/de-active
bolIsActive	Bit	Not Null	Active/de-active
vcNextPossible	string	Null	1. Requested.
Status			2. Forwarded.
			3. Approved.
			4. Canceled.
bolClose	bit	Not Null	Active/de-active
inCreatedBy	numeric	Null	Employee ID.
dtCreatedOn	date	Null	Date on which created.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date on which modified.

[0092] The following Tables 27-33 show additional tables the HRIS data schema that support the HRIS application 128 roster functionality. Alternatively, the roster functionality may be implemented in a separate application and the roster tables may be organized into a separate roster application data schema that supports roster data set requirements.

[0093]

TABLE 27
HRIS_tbl_Roster_Details (Table 1112)
This table contains the start and end time of the employee shift.
Data field	Data Type	Constraints	Description
inDetailsid	numeric	Primary Key	Unique identifier to
			identify the details.
inUserId	numeric	Not Null	Employee ID.
inRosterID	numeric	Not Null
inProcessID	integer	Not Null	1. Dsl
			2. Dial
			3. Felix
inTeamID	integer	Not Null	Reference
			from(Foreign Key)
			Sys_tbl_ProcessTeams
dttimestamp	date	Not Null	Date on which Uploaded.
dtStarttime	date	Not Null	Start Time
dtEndtime	date	Not Null	End Time
dtProcessStarttime	date	Null	Process Start Time
dtProcessEndtime	date	Null	Process Start Time
inShiftHours	integer	Null	Total Hours.
inRosterStatus	integer	Not Null	Status of roster

[0094]

TABLE 28
HRIS_tbl_RosterHead (Table 1116)
This table contains the ticket number of each Roster that is uploaded.
Data field	Data Type	Constraints	Description
inRosterId	numeric	Primary Key	Unique identifier to
			identify the details.
vcTicketNo	string	Null	New ticket will be raised
			for each roster.
dtFromDate	date	Null	Start date
dtToDate	date	Null	End date
vcNotes	string	Null	Date from which to
			which the roster is
			uploaded.
vcFileName	string	Null	Roster Template.
vcFilePath	string	Null	File from where
			Uploaded.
inErrorCount	integer	Null	Total Error Count.
inUploadedCount	integer	Null	Total Uploaded.
inTotalHeadCount	date	Null	Total Strength.
dtUpoloadedOn	date	Null	Date on which uploaded.
inUploadedBy	numeric	Null	Employee ID.

[0095]

TABLE 29
HRIS_tbl_RosterParameter (Table 1118)
This table contains scheduled/leave parameters.
Data field	Data Type	Constraints	Description
inParameterId	integer	Primary Key	Unique identifier to
			identify the details.
vcParameter	string	Not Null	1. Scheduled
			2. Leave Scheduled
			3. Compensatory
			Leave
vcNotes	string	Null	1. Scheduled
			2. Leave Scheduled
			3. Compensatory
			Leave
inSeqOrder	integer	Null	1. Scheduled
			2. Leave Scheduled
			3. Compensatory
			Leave
vcPossibleLeave Type	string	Null	Null
bolIsWorking	bit	Null	Active/de-active.
inAttendanceType	integer	Null	1. Planned Leave
			2. Present
			3. Sick Leave
inCreatedBy	numeric	Null	Employee ID.
dtCreatedOn	date	Null	Date on which created.
inModifiedBy	numeric	Null	Employee ID.
dtModifiedOn	date	Null	Date on which
			modified.

[0096]

TABLE 30
HRIS_tbl_RosterSheet
This table contains the status that has been uploaded.
Data field	Data Type	Constraints	Description
inSheetID	numeric	Primary Key	Unique identifier to
			identify the details.
inRosterID	numeric	Not Null	Reference
			from(Foreign Key)
			HRIS_tbl_RosterHead
inUserId	numeric	Not Null	Employee ID.
dtRosterStartdate	date	Not Null	Start Date.
dtRosterEnddate	date	Not Null	End Date.
inRowIndex	integer	Not Null	Row Count.
inProcessID	integer	Null	1. Dsl
			2. Dial
			3. Felix
inTeamID	integer	Null	Reference
			from(Foreign Key)
			Sys_tbl_ProcessTeams
inEmployeeCode	numeric	Null	Employee ID.
vcStartTime	string	Null	Start Time.
vcEndTime	string	Null	End Time.
dtfirstWeekoff	date	Null	Weekly Off
dtsecondWeekoff	date	Null	Weekly Off

[0097]

TABLE 31
HRIS_tbl_RosterSheet_Error
This table tracks the error that occurs while uploading.
Data field	Data Type	Constraints	Description
inRosterId	numeric	Primary Key	Unique identifier to
			identify the details.
inEmployeeCode	numeric	Primary Key	Unique Employee ID.
inExcelRowIndex	integer	Not Null	Total Excel Row index
dttimeStamp	date	Primary Key	Date on which error occur.
dtShiftStarttime	date	Null	Shift Start Time.
dtShiftEndtime	date	Null	Shift End Time.
bolValid	bit	Null	Null
vcError	string	Null	Errors occur while
			uploading.

[0098]

TABLE 32
HRIS_tbl_RosterSheet_Legends
The legends are defined in this table related to Weekly Off, Leave.
Data field	Data Type	Constraints	Description
inAttendanceParameter	integer	Primary Key	Unique identifier to
			identify the details.
inRosterParameter	integer	Null	1. Weekly Off
			2. Leave
			3. Attrition
dtExceldata	date	Not Null	1. 1/1/1900
			2. 1/3/1900
			(legend to manage
			roster parameter)

[0099]

TABLE 33
HRIS_tbl_RosterSheet_Temp
This table is a temp table that stores values before uploading
them in a final table.
Data field	Data Type	Constraints	Description
inUploadedBy	numeric	Not Null	Employee ID.
dtUploadedOn	date	Null	Date on which uploaded.
Dttimestamp	date	Not Null	Date
inRowIndex	integer	Null
inUserId	numeric	Null	Employee ID.
inProcessID	integer	Null	1. Dsl
			2. Dial
			3. Felix
inTeamID	integer	Null	Reference
			from(Foreign Key)
			Sys_tbl_ProcessTeams
inEmployeeCode	numeric	Not Null	Employee ID.
inRosterSchedule	integer	Null
dtShiftStarttime	date	Null	Shift start Time.
dtShiftEndtime	date	Null	Shift end Time.
inTimeZoneDiff	integer	Null	Null
bolValid	bit	Null	Active/de-active.
bolError	string	Null	1. Active
			2. De-active
vcError	string	Null	Date Already Exist.

[0100] The following Tables 34-37 show additional tables for the HRIS data schema.

[0101]

TABLE 34
HRIS_tbl_RptHeadCount
This table contains the head count of the Employees.
Data field	Data Type	Constraints	Description
inViewUser	numeric	Not Null	Employee ID.
Dttimestamp	date	Not Null	Date.
vcEmployeeCode	string	Not Null	Employee ID.
inUserid	numeric	Not Null	Employee ID.
inLocation	integer	Null	1. Mumbai
			2. Bangalore.
vcLocation	string	Null	1. Mumbai
			2. Bangalore.
vcUserName	string	Null	Employee Name.
inProcessId	integer	Not Null	1. Dsl
			2. Dial
			3. Felix
inTeamId	integer	Null	Reference
			from(Foreign Key)
			Sys_tbl_ProcessTeams
vcTeamName	string	Null	Team Name.
inParentTeam	integer	Null	Supervisor ID.
vcParentTeam	string	Null	Supervisor Name.
vcdayStatus	string	Null	1. Scheduled
			2. Leave without Pay

[0102]

TABLE 35
HRIS_UploadUserInfo_Temp
This table contains the user information while they are uploaded
to a final table.
Data field	Data Type	Constraints	Description
inEmployeeCode	string	Primary Key	Unique Employee ID.
inRowIndex	integer	Not Null	Total record inserted.
inUserId	numeric	Null	Unique Employee ID.
vcFirstName	string	Not Null	Employee First name.
vcLastName	string	Not Null	Employee Last name.
vcEnterpriseID	string	Not Null	Employee Enterprise ID.
vcNetworkID	string	Null	Employees NT ID.
inProcessId	integer	Null	1. Dial
			2. Dsl
			3. Felix
vcGender	string	Not Null	1. Male
			2. Female
vcTeamName	string	Null	Team Name.
inTeamID	integer	Null	Reference
			from(Foreign Key)
			Sys_tbl_ProcessTeams
inSupervisorID	numeric	Null	Supervisor ID.
vcLevel	string	Null	1. G
			2. H
			3. F
inLevelID	integer	Null	Reference
			from(Foreign Key)
			Sys_tbl_Levels
inDesignation	integer	Null	Designation ID.
vcDesignation	string	Null	1. Analyst
			2. Software Developer
			3. Manager
dtCompanyDOJ	date	Null	Date on which company
			joined.
dtDOB	date	Null	Date of birth of Employee.
inLocation	integer	Null	1. Mumbai
			2. Bangalore
bolError	bit	Not Null	1. Active
vcError	string	Null	1. Team does not exist
dtUploadedOn	date	Not Null	Date on which uploaded.
inUploadedBy	numeric	Not Null	Employee ID.
inHrRepresntative	numeric	Null	Null

[0103]

TABLE 36
HRIS_UserInfo_Sheet
This table contains the information of employee that has been uploaded.
Data field	Data Type	Constraints	Description
inUniqueID	numeric	Primary Key	Unique identifier to identify the details.
inSheetId	numeric	Null	Sheet Number.
inRowIndex	integer	Null	Total Uploaded.
inEmployeeCode	numeric	Null	Unique Employee ID.
vcFirstName	string	Null	Employee First name.
vcLastName	string	Null	Employee Last name.
vcEnterpriseID	string	Null	Employee Enterprise ID.
vcNetworkID	string	Null	Employees NT ID.
inProcessId	integer	Null	1. Dial
			2. Dsl
			3. Felix
vcGender	string	Null	1. Male
			2. Female
inTeamID	integer	Null	Reference from(Foreign Key)
			Sys_tbl_ProcessTeams
inSupervisorID	numeric	Null	Supervisor ID.
inLevelID	integer	Null	Reference from(Foreign Key)
			Sys_tbl_Levels
inDesignation	integer	Null	Designation ID.
dtCompanyDOJ	date	Null	Date on which company joined.
dtDOB	date	Null	Date of birth of Employee.
inLocation	integer	Null	1. Mumbai
			2. Bangalore
dtCreatedOn	date	Null	Date on which created.
inCreatedBy	numeric	Null	Employee ID.

[0104]

TABLE 37
HRIS_UserInfoSheet_Detail
This table contains the file path from where it has been uploaded.
Data field	Data Type	Constraints	Description
inSheetId	numeric	Primary Key	Unique identifier to identify the details.
vcFileName	string	Null	Path from where the file is uploaded.
vcNote	string	Null	About user info and process.
inTotalCount	integer	Null	Total Uploaded.
inUploadedCount	integer	Null	Total Upload Count.
inErrorCount	integer	Null	Error occurs while uploading.
dtUploadedOn	date	Null	Date on which uploaded.
inUploadedBy	numeric	Null	Employee ID.

[0105] The following Tables 38-44 show tables for the ID management data schema that supports the ID management application 116.

[0106]

TABLE 38
IDM_tbl_Businessids_Mst
This is a master table and used to save types of business ids.
Data field	Data Type	Constraints	Description
inUniqueId	int	Primary Key	Auto-generated unique identifier of the table
vcIDName	varchar	Null	Name of the Id
vcDescription	varchar	Null	Description/Reason due to which the id is
			created
inTAT	int	Null	Turn around time taken for the creation of the id
bolIsActive	bit	Null	Denotes status (Active/Deactivated) of the ID
bolCompanyIds	bit	Null	Denotes whether the id is company or client
			provided
vcRequesttype	varchar	Null	Following requests can be raised for the business
			ids
			a) Creation
			b) Reactivation
			c) Deactivation
			d) Reset Password
			e) Recycling
			f) Deletion
dtCreatedOn	datetime	Null	Date on which the id was created
inCreatedBy	numeric	Null	User id of the id creator is saved n this field
dtModifiedOn	datetime	Null	Timestamp is captured for any modification made
unmodified	numeric	Null	User Id of the user who made latest modifications

[0107]

TABLE 39
IDM_tbl_BusinessIds_User
In this table users' different business ids are saved.
Data field	Data Type	Constraints	Description
inTransactionId	numeric	Primary Key	Auto-generated unique identifier of the table
inUserId	numeric	Not Null	User Id of the user whose business id details
inBusinessId	int	Not Null	User's Business id, referred from
			IDM_tbl_Businessids_Mst table
inProcessId	int	Not Null	Process id of the process to which the user
			belongs to
vcValue	varchar	Null	Value of the user's actual business id
vcPassword	varchar	Null	Password of the corresponding business id saved
			in encrypted mode
inStatus	int	Not Null	Status (active or deactivated) of the id
inCreatedBy	numeric	Not Null	User id of the creator of the id
dtCreatedOn	Datetime	Not Null	Id creation's timestamp

[0108]

TABLE 40
IDM_tbl_BusinessIds_UserHistory
This table is used to trace or maintain history of any changes made
in the IDM_tbl_BusinessIds_User table. So
this table's structure/design is replica of the IDM_tbl_BusinessIds_User table.
Data field	Data Type	Constraints	Description
inUserId	numeric	Not Null	Auto-generated unique identifier of the table
inBusinessId	int	Not Null	User Id of the user whose business id details
inProcessId	int	Not Null	User's Business id, referred from
			IDM_tbl_Businessids_Mst table
vcValue	varchar	Null	Process id of the process to which the user
			belongs to
vcPassword	varchar	Null	Value of the user's actual business id
inCreatedBy	numeric	Not Null	User id of the creator of the id
dtCreatedOn	datetime	Not Null	Id creation's timestamp
inModifiedBy	numeric	Not Null	User id of the modifier of the id
dtModifiedOn	datetime	Not Null	Timestamp when the id was modified

[0109]

TABLE 41
IDM_tbl_WorkFlow_Status
This is a master table where status of the work flows maintained for the
id management process is saved.
Data field	Data Type	Constraints	Description
inStatusId	int	Primary Key	Auto-generated unique identifier of the table
vcStatus	varchar	Null	Status description
vcNextPossibleStatus	varchar	Null	Next possible status
vcMailingGroups	varchar	Null	Mailing group id who needs to be informed
			about the work flow status change
bolClose	bit	Null	Boolean fields which denotes whether the
			status is close
inCreatedBy	numeric	Null	User Id of the creator
dtCreatedOn	datetime	Null	Timestamp of id creation
inModifiedBy	numeric	Null	User id of the modifier
dtModifiedOn	datetime	Null	Timestamp on which record was modified

[0110]

TABLE 42
IDM_UploadedSheets
Details of the id upload process is captured in this table.
Data field	Data Type	Constraints	Description
inSheetId	numeric	Primary Key	Auto-generated unique identifier of the table
inProcessId	int	Not Null	Process id of the process for which ids are
			uploaded
vcSheetName	varchar	Not Null	Path at which the source sheet is saved
inTotalRecords	int	Not Null	Total no. of records to be uploaded
inUploadedCount	int	Not Null	Total no. of records uploaded successfully
inErrorCount	int	Not Null	To no. of records which did not get uploaded
bolOverwrittenIds	bit	Not Null	Whether record is overwritten
inValueType	int	Not Null	Whether the record is for id or password
inUploadedBy	numeric	Not Null	User id of the user who uploaded the record
dtUploadedOn	datetime	Not Null	Timestamp on which the sheet was uploaded

[0111]

TABLE 43
IDM_UploadedSheets_Details
This is a child or details table of IDM_UploadedSheets
table.
Data field	Data Type	Constraints	Description
inSheetId	int	Not Null	Reference key to IDM_UploadedSheets table
inUserId	numeric	Not Null	User id of the user whose id is uploaded
inBusinessId	int	Not Null	Reference Key to IDM_tbl_BusinessIds_User table
			denoting business id of the user
inProcessId	int	Not Null	Process Id of the user's process
vcValue	varchar	Not Null	value of user's actual business id

[0112]

TABLE 44
IDM_UploadedSheets_Errors
While uploading ids if any error occurs, details each error record is saved.
Data field	Data Type	Constraints	Description
inSheetId	numeric	Not Null	Auto-generated unique identifier of the table
inUserId	numeric	Null	User id of the user whose id was being uploaded
inBusinessId	int	Null	Business Id, reference key from
			IDM_tbl_BusinessIds_User table
inProcessId	int	Not Null	Process id of the process to which the business id
			belonged
vcValue	varchar	Null	Actual value of the user's business id
vcError	varchar	Null	Reason for which the error occurred

[0113] The following Tables 45-49 show tables for the Case Management KANA data schema 208 that supports the Case Management KANA application 108. FIG. 6 provides additional detail.

[0114]

TABLE 45
Kana_tbl_AgentIdDetails
Used to map the Kana Agent Id to User Id.
Data field	Data Type	Constraints	Description
inAgentId	numeric	Primary Key	This is unique Agent ID for each case
inUserId	numeric	Not Null	This field represents the unique Id (w.r.t employee
			Code) of the employee who has raised the case
vcAgentName	string	Not Null	This field denotes Kana Agent Name
vcComments	string	Null	This field denotes description
bolActive	bit	Not Null	This field denotes the sequence order of action
			status
dtReceivedOn	date	Not Null	This field denotes Agent ID received date
dtCreatedOn	date	Not Null	This field denotes the Date/Time When Agent ID is
			created.
inCreatedBy	numeric	Not Null	This field denotes who has created Agent Id
inModifiedBy	date	Null	This field denotes who has modified Agent ID
dtModifiedOn	numeric	Null	This field denotes the Date/Time When Agent ID is
			modified.

[0115]

TABLE 46
Kana_tbl_CaseFlow_Action (Table 604)
All possible actions that can be taken in a particular case
Data field	Data Type	Constraints	Description
inStatusId	integer	Primary Key	This is unique Status ID for each case
inStatusType	integer	Not Null	This field denotes the case Status Type
vcStatus	string	Not Null	This field denotes the possible status on cases
			Order Process, Referal, Rejected, Outstanding
			Balance
vcNextPossibleAction	string	Null	This field denotes the next possible status as
			per status type.
vcNotes	string	Null	This field denotes the status description
IsbolActive	bit	Not Null	This field denotes whether action status is
			active/de-active
inSquenceOrder	integer	Not Null	This field denotes the sequence order of
			action status
dtCreatedOn	date	Not Null	This field denotes who has created action
			status.
inCreatedBy	numeric	Not Null	This field denotes the Date/Time When action
			status is created.
dtModifiedon	date	Null	This field denotes who has modified action
			status.
inModifiedBy	numeric	Null	This field denotes the Date/Time When action
			status is modified.

[0116]

TABLE 47
Kana_tbl_CaseFlow_Details (Table 602)
Detailed history of a case is stored here and the actions that were taken in the case
Data field	Data Type	Constraints	Description
inDetailId	numeric	Primary Key	This is unique Detail ID for each case
inCaseID	numenc	Not Null	This is Foreign Key with reference to
			‘Kana_tbl_CaseFlow_Mst’
inHeadId	numeric	Not Null	This is Foreign Key with reference to
			‘Kana_tbl_CaseFlow_Head’
inReferal	numeric	Null	This field denotes the Referral detail in case
			particular case referred to concern person.
inStatus	integer	Null	This field denotes the Status of particular logged
			case.
vcComment	string	Null	This field denotes the case description/comment
dtAssignOn	date	Null	This field denotes the Date/Time When case is
			assigned to user.
inAssignedBy	numeric	Null	This field denotes the Date/Time When case is
			assigned to user.
dtClosedOn	date	Null	This field captured the Date/Time for the possible
			action performed on particular cases.
inClosedBy	numeric	Null	This field denotes the User who has performed the
			action on case.

[0117]

TABLE 48
Kana_tbl_CaseFlow_Head (Table 608)
This table maintains the final status of a case. Will tell whether a case is closed or not.
Data field	Data Type	Constraints	Description
inHeadId	Numeric	Primary Key
inCaseID	Numeric	Not Null	This is Foreign Key with reference to
			‘Kana_tbl_CaseFlow_Mst’
inDetailId	Numeric	Null	This is Foreign Key with reference to
			‘Kana_tbl_CaseFlow_Details’
bolIsClosed	Bit	Null	This field denotes the Status whether case is
			closed or not.
dtCreatedOn	Date	Null	This field denotes the Date/Time When case is
			created by user.
inCreatedBy	Numeric	Null	This field denotes the user who has created the
			case.
dtClosedOn	Date	Null	This field denotes the Date/Time When case is
			Closed by user.
inClosedBy	Numeric	Null	This field denotes the user who has closed the
			case.

[0118]

TABLE 49
Kana_tbl_CaseFlow_Mst (Table 606)
Case information is stored in this table
Data field	Data Type	Constraints	Description
inCaseID	numeric	Primary Key	This is unique ID for each case
vcTicketNo	string	Null	This field represents the ticket number that gets
			generated against a case
inUserId	numeric	Not Null	This field represents the unique Id (w.r.t employee
			Code) of the employee who has raised the case
inSupervisorId	numeric	Not Null	This field denotes the ID of the supervisor under
			whom this employee works.
inTeamId	integer	Not Null	This field denotes the team in which the user works
			in
inCaseNo	numeric	Not Null	This field denotes the case number of the Case.
vcAccountNo	string	Not Null	This field denotes the account details of the Case.
vcAgentId	string	Not Null	This field denotes the unique kana agent id
inErrorId	integer	Not Null	This field denotes error details of logged case.
vcComment	string	Not Null	This field denotes case problem description
dtBeginDateTime	date	Not Null	This field denotes the Time when user click on
			‘Add new Case’ option
dtCreatedOn	date	Not Null	This field denotes the Date/Time when the case is
			logged.
inCreatedBy	numeric	Not Null	This field denotes the user, who logged the case.

[0119] The following Tables 50-58 show tables for the Leave data schema 212 that supports the Leave application 120. FIGS. 8 and 9 provide additional detail.

[0120]

TABLE 50
Leave_tbl_Allocation (Table 802)
This the master table used for leave allocation process/location wise.
Data field	Data Type	Constraints	Description
inAllocationId	numeric	Primary Key	Auto-generated unique identifier of the table
dttimestamp	datetime	Not Null	Date for which leave is allotted
inProcessID	int	Not Null	Process or line of business for which leave is being
			allotted
inLocationid	int	Not Null	Location/Center of the process for which leave is
			being allotted
inLevelId	int	Not Null	Level/Designation for which leave is being
			allocated
inEstimatedhead	int	Not Null	Estimated head count of the process and the
count			center for the day
inallottedPercentage	decimal	Not Null	Percentage of leave allocated for the date
inLeaveallotted	int	Not Null	No. of leave allotted for the date
inLeaveApplied	int	Not Null	Count of leaves applied for the date
inLeaveApproved	int	Not Null	Count of leaves approved for the date
inLeavesCanceled	int	Not Null	Count of leaves cancelled for the date
inBalanceLeave	int	Null	Count of leave balance available for the date
vcComments	varchar	Null	Comments
inCreatedBy	numeric	Not Null	User id of the record created by
dtCreatedOn	datetime	Not Null	Timestamp of the record created on
inModifiedBy	numeric	Null	User id of the record modified by
dtModifiedOn	datetime	Null	Timestamp of the record modified on
inAllotedLeaveException	int	Null	Used in case extra leave needs to be allocated
			above the normal allocation for the date. Gets
			added to available leave balance for the date

[0121]

TABLE 51
Leave_tbl_Allocation_History (Table 806)
Table structure of this table is almost replica of Leave_tbl_Allocation table and
used to save historical data of the mother table in case of any modification.
Data field	Data Type	Constraints	Description
inAllocationId	numeric	Null	Auto-generated unique identifier of the table
Dttimestamp	datetime	Not Null	Date for which leave is allotted
inProcessID	int	Not Null	Process or line of business for which leave
			is being allotted
inLocationid	int	Not Null	Location/Center of the process for which
			leave is being allotted
inLevelId	int	Not Null	Level/Designation for which leave is being
			allocated
inEstimatedheadcount	int	Not Null	Estimated head count of the process and
			the center for the day
inallottedPercentage	decimal	Not Null	Percentage of leave allocated for the date
inLeaveallotted	int	Not Null	No. of leave allotted for the date
inLeaveApplied	int	Not Null	Count of leaves applied for the date
inLeaveApproved	int	Not Null	Count of leaves approved for the date
inLeavesCanceled	int	Not Null	Count of leaves cancelled for the date
inBalanceLeave	int	Null	Count of leave balance available for the
			date
vcComments	varchar	Null	Comments
inCreatedBy	numeric	Not Null	User id of the record created by
dtCreatedOn	datetime	Not Null	Timestamp of the record created on
inModifiedBy	numeric	Null	User id of the record modified by
dtModifiedOn	datetime	Null	Timestamp of the record modified on

[0122]

TABLE 52
Leave_tbl_AppliedLeave (Table 902)
The head leave details are saved in this table.
Data field	Data Type	Constraints	Description
inHeadId	numeric	Primary Key	Auto-generated unique identifier of the table
inWorkFlowId	numeric	Null	Corresponding key to
			Leave_tbl_AppliedLeave_WorkFlow table
vcTicketNo	varchar	Null	Unique ticket no. based on inHeadId used for
			display purpose
inUserID	numeric	Not Null	User Id of the user who applied leave
inTeamId	int	Null	Team id of the team to which the user belongs to
inProcessId	int	Null	Process id of the process to which the user
			belongs to
dtLeaveFrom	datetime	Not Null	From date of the range the user applied leave
dtLeaveTo	datetime	Not Null	To date of the range the user applied leave
inTotaldays	int	Not Null	Count of days for which the user has applied leave
inTotalLeaves	int	Not Null	Count of days for which the user has applied leave
inTotaloff	int	Not Null	Count of work offs in the date range for which the
			user has applied leave
bolClosed	bit	Not Null	Boolean field which determines whether the
			transaction is closed
bolApproved	bit	Not Null	Boolean field which determines whether leave
			ticket has been approved
bolCanceled	bit	Not Null	Boolean field which determines whether leave
			ticket has been cancelled
dtAppliedon	datetime	Not Null	Timestamp on which the user applied leave
inAppliedBy	numeric	Not Null	User id of the user who applied leave
bolIsSpecialQuota	bit	Null	Boolean field determining whether the leave has
			been applied by special or normal quota

[0123]

TABLE 53
Leave_tbl_AppliedLeave_Details (Table 904)
This is a child or details table of Leave_tbl_AppliedLeave table.
Data field	Data Type	Constraints	Description
inDetailsId	numeric	Primary Key	Auto-generated unique identifier of the table
inHeadId	numeric	Not Null	Corresponding key to Leave_tbl_AppliedLeave
			table
inUserId	numeric	Null	User Id of the user who applied leave
dttimestamp	datetime	Null	Date for which the leave is applied
inLeaveType	int	Null	Type of leave applied. This can be of the following
			types:
			1. Vacation
			2. Compensatory Off
			3. Fixed holiday
			4. Optional holiday
vcReason	varchar	Null	Reason for which leave is applied
bolActive	bit	Not Null	Boolean value determining the status of the leave
			record

[0124]

TABLE 54
Leave_tbl_AppliedLeave_WorkFlow (Table 904)
Work flow of the leave application process is maintained and captured in this table.
Data field	Data Type	Constraints	Description
inWorkflowId	numeric	Primary Key	Auto-generated unique identifier of the table
inHeadID	numeric	Not Null	Corresponding key to Leave_tbl_AppliedLeave
			table
inAllocatedBy	numeric	Null	User id of the user who applied leave
dtAllocatedOn	datetime	Null	Corresponding key to
			Leave_tbl_AppliedLeave_WorkFlowStatus table
			determining the action performed
inStatusId	int	Null	Leave Status or workflow status
inActionedBy	numeric	Null	User Id of the user who performed the action
dtActionedOn	datetime	Null	Timestamp on which action was performed
vcComments	varchar	Null	Comments

[0125]

TABLE 55
Leave_tbl_AppliedLeave_WorkFlowStatus (Table 908)
This is a master table which saves the type of leave workflow statuses.
Data field	Data Type	Constraints	Description
inStatusId	int	Primary Key	Auto-generated unique identifier of the table
vcStatus	varchar	Not Null	Actual value of the work flow status
vcNotes	varchar	Null	Notes/Comments
bolIsActive	bit	Not Null	Boolean value determining the status of the
			record
inCreatedBy	numeric	Null	User id of the creator of the record
dtCreatedOn	datetime	Null	Timestamp on which the record was created
inModifiedBy	numeric	Null	User id of the user who last modified the record
dtModifiedOn	datetime	Null	Timestamp on which data was last modified

[0126]

TABLE 56
Leave_tbl_BalanceLeave (Table 804)
This table is used for maintaining users' different types of leaves.
Data field	Data Type	Constraints	Description
inUserID	numeric	Primary Key	User id of the user whose leave balance the record
			contains
inLeaveType	int	Primary Key	Key to HRIS_tbl_AttendanceParameter table,
			determines user's leave type
dtbalanceasOn	datetime	Not Null	Date till which balance is maintained
inOpeningBalance	float	Not Null	User opening leave balance from previous year
inFinancialYear	int	Not Null	Financial year for which user's leave balance is
			maintained
inLeaveCredited	float	Not Null	Leave hours credited to the user in the record's
			financial year
inLeaveDebited	float	Not Null	Leave hours debited from user's leave balance in
			the record's financial year
inAppliedLeave	float	Not Null	Hours of leave applied by the user
inLeaveBalance	float	Null	Leave balance of the user
inCreatedBy	int	Not Null	User id of the creator of the record
dtCreatedOn	datetime	Not Null	Timestamp on which the record was created
inModifiedBy	int	Null	User id of the last modifier of the record
dtModifiedOn	datetime	Null	Latest timestamp on which record was last
			modified

[0127]

TABLE 57
Leave_tbl_BalanceLeave_History
In this table leave balance history of the users are saved. When ever any leave balance
record is updated, the previous history of the same record is saved in this table.
Data field	Data Type	Constraints	Description
inUserID	Numeric	Primary Key	User id of the user whose leave balance the record
			contains
inLeaveType	Int	Primary Key	Key to HRIS_tbl_AttendanceParameter table,
			determines user's leave type
dtbalanceasOn	Datetime	Primary Key	Date till which balance is maintained
inOpeningBalance	Float	Not Null	User opening leave balance from previous year
inFinancialYear	int	Not Null	Financial year for which user's leave balance is
			maintained
inLeaveCredited	float	Not Null	Leave hours credited to the user in the record's
			financial year
inLeaveDebited	float	Not Null	Leave hours debited from user's leave balance in
			the record's financial year
inAppliedLeave	float	Not Null	Hours of leave applied by the user
inLeaveBalance	float	Null	Leave balance of the user
inCreatedBy	int	Not Null	User id of the creator of the record
dtCreatedOn	datetime	Not Null	Timestamp on which the record was created
inModifiedBy	int	Null	User id of the last modifier of the record
dtModifiedOn	datetime	Null	Latest timestamp on which record was last
			modified

[0128]

TABLE 58
Leave_tbl_Special_Quota
This table is used in case users need to be allotted leave above the normal quota.
Data field	Data Type	Constraints	Description
inAllocationId	Numeric	Primary Key	Reference key to Leave_tbl_Allocation table
inallottedPercentage	Decimal	Null	Percentage of special quota leave allocated
			for the date
inLeaveallotted	Decimal	Null	No. of leave allotted for the date
inLeaveApplied	Int	Null	Count of leaves applied for the date
inLeaveApproved	Int	Null	Count of leaves approved for the date
inLeavesCanceled	int	Null	Count of leaves cancelled for the date
inBalanceLeave	int	Null	Count of leave balance available for the date
vcComments	varchar	Null	Comments
inCreatedBy	numeric	Null	User id of the record created by
dtCreatedOn	datetime	Null	Timestamp of the record created on
inModifiedBy	numeric	Null	User id of the record modified by
dtModifiedOn	datetime	Null	Timestamp of the record modified on

[0129] The following Tables 59-66 show tables for the Case Management data schema AIM 204 that supports the Case Management AIM application 106. FIGS. 4 and 5 provide additional detail.

[0130]

TABLE 59
Maple_tbl_CaseFlow_Mst (Table 506)
Case information is stored in this table.
Data field	Data Type	Constraints	Description
inCaseId	Numeric	Primary Key	This is unique ID for each case
vcTicketNo	String	Null	This field represents the ticket number that gets
			generated against a case
inUserId	Numeric	Not Null	This field represents the unique Id (with respect to
			employee Code) of the employee who has raised
			the case
inSupervisorId	Numeric	Not Null	This field denotes the ID of the supervisor under
			whom this employee works.
inTeamId	Integer	Not Null	This field denotes the team in which the user works
			in
inAccountType	Integer	Not Null	This field denotes the type of the account in which
			user works in.
vcAccountNo	string	Not Null	This field denotes the account details of the Case.
inBatchId	integer	Not Null	This field denotes the batch number in which user
			works in.
inErrorId	integer	Not Null	This field denotes the Error details.
inErrorType	integer	Not Null	This field denotes the Error Type that's shows the
			respective Line of business.
inFranchise	integer	Not Null	This field denotes the Franchise details
inBenefit	integer	Not Null	This field denotes the Benefit details for that case.
bolIsWorked	bit	Not Null	This field denotes whether employee has worked on
			particular case or not.
vcComment	string	Not Null	This field denotes the problem summary of the case.
dtCreatedOn	date	Not Null	This field denotes the Date/Time when the case is
			logged.
dtBeginDateTime	date	Null	This field denotes the Time when user click on ‘Add
			new Case’ option
inCreatedBy	numeric	Not Null	This field denotes the user, who logged the case.
vcUpdationNotes	string	Not Null	This field denotes the updated comments in case of
			any updation done particular case.
dtUpdatedOn	date	Null	This field denotes the updated Date/Time.
inModifiedBy	numeric	Null	This field denotes the details of person who's ever
			updated the case details.