Appeal Dataset Specification

The "Appeal Dataset Specification" contains Appeal information for request items. Its unique keys are Appeal_Key and Appeal_ID. Appeal_Key is enforced.

References:
Members by Member_ID.
Requests by both Request_Key and Request_ID.
Referenced back from Request_Item by Item_Details.Review_Outcomes.Appeal.Appeal_Key.

Key Components

Appeal Case Records

Documents essential details of each appeal, including reasons, current status, and results.

Customizable Data Points

"Appeal Custom Fields" allow for recording specific, additional information tailored to individual appeals, facilitating detailed tracking.

Action and Event Logs

The "Appeal Log" provides a chronological record of actions and events, creating a detailed timeline for the progression of each case.

This dataset supports effective oversight and analysis of the appeals process, helping healthcare providers manage patient disputes efficiently and transparently.

Table Structure

Table Name: Appeal

Column Name	Is Nullable	Data Type	Description
`Member_ID`	YES	STRING	Identifier for the member associated with the appeal.
`Request_Key`	YES	INT64	Unique key of the request that the appeal is related to.
`Request_ID`	YES	STRING	Unique identifier of the request that the appeal is addressing.
`Appeal_Key`	YES	INT64	Primary key of the appeal record.
`Level_Key`	YES	INT64	Identifier for the specific level or stage of the appeal process.
`Appeal_ID`	YES	STRING	A unique identifier assigned to the appeal.
`Appeal_Void_Indicator`	YES	BOOL	Boolean indicator showing whether the appeal has been voided or cancelled.
`Process_Type`	YES	STRING	Describes the type of appeal process being undertaken. See Dictionary Appeal Process Type table in the Dictionary Dataset Specification.
`Process_Type_Description`	YES	STRING	Detailed description of the appeal process type. See Dictionary Appeal Process Type table in the Dictionary Dataset Specification.
`Appeal_Created_User_ID`	YES	STRING	Identifier for the user who created the appeal.
`Appeal_Created_User_Name`	YES	STRING	Name of the user who created the appeal.
`Appeal_Last_Modified_User_ID`	YES	STRING	Identifier for the user who last modified the appeal.
`Appeal_Last_Modified_User_Name`	YES	STRING	Name of the user who last modified the appeal.
`Appeal_Created_Timestamp`	YES	TIMESTAMP	Timestamp for when the appeal was created.
`Appeal_Last_Modified_Timestamp`	YES	TIMESTAMP	Timestamp for the last modification of the appeal.
`Level_ID`	YES	STRING	Identifier for the level of the appeal within the process. See Dictionary Appeal Level table in the Dictionary Dataset Specification.
`Level_Code`	YES	STRING	Code representing the level of the appeal within the process.
`Level_Description`	YES	STRING	Description of the level of the appeal. See Dictionary Appeal Level table in the Dictionary Dataset Specification.
`Level_Type`	YES	STRING	Type of appeal level. See Dictionary Appeal Type table in the Dictionary Dataset Specification.
`Level_Type_Description`	YES	STRING	Description of the appeal level type. See Dictionary Appeal Type table in the Dictionary Dataset Specification.
`Level_Notify_Timestamp`	YES	TIMESTAMP	Timestamp indicating when parties were notified of this level of the appeal.
`Level_Due_Timestamp`	YES	TIMESTAMP	Timestamp indicating the due date for this level of the appeal.
`Level_Resolution_Timestamp`	YES	TIMESTAMP	Timestamp when the appeal was resolved at this level.
`Level_All_Documents_Received_Timestamp`	YES	TIMESTAMP	Timestamp indicating when all necessary documents for the appeal were received.
`Level_Reason`	YES	STRING	Reason for the appeal at this level. See Dictionary Appeal Reason table in the Dictionary Dataset Specification.
`Level_Reason_Description`	YES	STRING	Description of the reason for the appeal at this level. See Dictionary Appeal Reason table in the Dictionary Dataset Specification.
`Level_Void_Indicator`	YES	BOOL	Indicates whether the appeal at this level was voided.
`Level_Source`	YES	STRING	The source of the appeal at this level. See Dictionary Appeal Source table in the Dictionary Dataset Specification.
`Level_Source_Description`	YES	STRING	Description of the source of the appeal at this level. See Dictionary Appeal Source table in the Dictionary Dataset Specification.
`Level_Requestor`	YES	STRING	Identifier for the requester of the appeal at this level. See Dictionary Appeal Requestor table in the Dictionary Dataset Specification.
`Level_Requestor_Description`	YES	STRING	Description of the requester of the appeal at this level. See Dictionary Appeal Requestor table in the Dictionary Dataset Specification.
`Level_Reviewer`	YES	STRING	Identifier for the reviewer of the appeal at this level. See Dictionary Appeal Reviewer table in the Dictionary Dataset Specification.
`Level_Reviewer_Description`	YES	STRING	Description of the reviewer of the appeal at this level. See Dictionary Appeal Reviewer table in the Dictionary Dataset Specification.
`Level_Due_Date_Reason`	YES	STRING	Reason for the due date set for this level of the appeal. See Dictionary Appeal Due Date Change Reason table in the Dictionary Dataset Specification.
`Level_Due_Date_Reason_Description`	YES	STRING	Description of the reason for the due date at this level of the appeal. See Dictionary Appeal Due Date Change Reason table in the Dictionary Dataset Specification.
`Level_Created_Timestamp`	YES	TIMESTAMP	Timestamp when the level of the appeal level was created.
`Level_Last_Modified_Timestamp`	YES	TIMESTAMP	Timestamp when the level of the appeal level was last modified.
`Due_Date_Change_Indicator`	YES	BOOL	Indicates whether there was a change in the due date during this level of the appeal process. This condition determines if a due date for a task has been set.

Appeal Dataset Specification

Due Date Reason

The system checks the Due_Date_Reason field to see if it contains a specific reason, and that the reason is not "System Calculated." If these conditions are met, the due date is considered manually set. This allows for precise tracking of due dates that are adjusted by users, ensuring that manual interventions are documented and clear.

Appeal Level Fields

Appeal Level_Created_User_ID

Type: STRING
Description: User ID of the person who created the appeal level.

Appeal Level_Created_User_Name

Type: STRING
Description: Full name of the person who created the appeal level.
The full name of the user, as listed in User.Full_Name, is associated with the account that created the entry, identified by Levels.Created_User_ID.

Appeal Level_Last_Modified_User_ID

Type: STRING
Description: User ID of the person who last modified the appeal level.

Appeal Level_Last_Modified_User_Name

Type: STRING
Description: Full name of the person who last modified the appeal level.
The full name of the user, as listed in User.Full_Name, is linked to the account that last modified the entry, identified by Levels.Last_Modified_User_ID.

Appeal Level_Owner_ID

Type: STRING
Description: The current owner of the appeal level.
The Owner_ID represents the current owner of the item. It is determined by checking the Levels.User_Owner_ID. If this is not available, the ownership defaults to Levels.Department_Owner_ID. This ensures that each item is always associated with an owner, whether an individual user or a department.

Appeal Level_Owner_Type

Type: STRING
Description: The type of the current owner of the appeal level (individual or department).
Ownership is categorized as follows:
- 'INDV' when there is a Levels.User_Owner_ID.
- 'DEPT' when there is a Levels.Department_Owner_ID.
- NULL otherwise.

Appeal Level_Owner_Name

Type: STRING
Description: The name of the current owner of the appeal level.
Ownership type is determined as follows:
- If Levels.User_Owner_ID is present, the owner type is labeled 'INDV' for individual.
- If Levels.Department_Owner_ID is present, the owner type is labeled 'DEPT' for department.
- If neither identifier is available, the owner type remains unspecified.

Appeal First Level Fields

Appeal First_Level_Owner_ID

Type: STRING
Description: The owner ID of the first level found based on history records in the appeal log.
The First_Owner_ID represents the owner ID of the initial level recorded in the appeal log, based on historical entries.
This ID is retrieved from the earliest record for each request ID in the Appeal_LOG table, where records are organized by the earliest Last_Modified_Timestamp.
The system uses User_Owner_ID if available; otherwise, it defaults to Owner_Department_ID to identify the first owner.

Appeal First_Level_Owner_Type

Type: STRING
Description: The owner type of the first level found based on history records in the appeal log.
The First Owner Type indicates whether the initial owner, according to historical data in the appeal log, is an individual or a department.
Determination is made from the oldest entry for each level ID in the Appeal_LOG table, sorted by the earliest Last_Modified_Timestamp.
- If Appeal_LOG.Levels.User_Owner_ID is present, the owner type is labeled 'INDV' for individual.
- If Appeal_LOG.Levels.Department_Owner_ID is present, the owner type is labeled 'DEPT' for department.
- If neither is available, no owner type is assigned.

Appeal First_Level_Owner_Name

Type: STRING
Description: The owner name of the first level found based on history records in the appeal log.
The First Owner Name identifies the name of the earliest recorded owner for each level ID in the Appeal_LOG table, based on the order of entries by the earliest Last_Modified_Timestamp.
- If there is a User_Owner_ID listed in Appeal_LOG.Levels, the individual's full name from owner_user.Full_Name is used.
- If a Department_Owner_ID is present, the department's name from owner_dept.Department_Name is used.
- If neither identifier is found, no owner name is provided.

Appeal Previous Level Fields

Appeal Previous_Level_Owner_ID

Type: STRING
Description: The previous owner ID before the current level in the appeal process.
The Previous Owner ID identifies the owner immediately preceding the current level in the appeal process.
This ID is determined by examining the history records for each level ID in the Appeal_LOG table, which are organized by the earliest Last_Modified_Timestamp.
The system selects the User_Owner_ID if available; otherwise, it uses the Department_Owner_ID to identify the previous owner.

Appeal Previous_Level_Owner_Type

Type: STRING
Description: The type of the previous owner before the current level in the appeal process.
The Previous Owner Type identifies whether the owner immediately before the current level in the appeal process is an individual or a department.
Determination is made by looking at the history records for each level ID in the Appeal_LOG table, sorted by the earliest Last_Modified_Timestamp.
- If a User_Owner_ID from Appeal_LOG.Levels is present, the owner type is classified as 'INDV' for individual.
- If a Department_Owner_ID is recorded instead, the owner type is classified as 'DEPT' for department.
- If no owner ID is available, the owner type remains unspecified.

Appeal Previous_Level_Owner_Name

Type: STRING
Description: The name of the previous owner before the current level in the appeal process.
The Previous Owner Name determines the name of the owner just before the current level in the appeal process.
This is established by examining the historical records for each level ID in the Appeal_LOG table, arranged by the earliest Last_Modified_Timestamp.
- If a User_Owner_ID is listed within Appeal_LOG.Levels, the corresponding individual's full name from owner_user.Full_Name is used.
- If a Department_Owner_ID is noted instead, the name from owner_dept.Department_Name is applied.
- If neither identifier is found, no owner name is recorded.

Appeal Level Outcome Fields

Appeal Level_Outcome_Key

Type: INT64
Description: Unique key associated with the outcome of the appeal level.

Appeal Level_Outcome_Appeal_Status

Type: STRING
Description: Status of the appeal level outcome.

Appeal Dataset Specification

Appeal Level Outcome

Appeal Level_Outcome_Appeal_Status_Description

Is Nullable: YES
Data Type: STRING
Description: Description of the appeal status at this level. See Dictionary Appeal Status table in the Dictionary Dataset Specification.

Appeal Review_Outcome_Key

Is Nullable: YES
Data Type: INT64
Description: Unique key associated with the outcome of the appeal review.

Appeal Outcome_ID

Is Nullable: YES
Data Type: STRING
Description: Identifier for the outcome of the appeal level.

Appeal Level_Outcome_Created_Timestamp

Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the outcome of the appeal level was created.

Appeal Level_Outcome_Last_Modified_Timestamp

Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the outcome of the appeal level was last modified.

Appeal Turnaround_Time_Minute

Is Nullable: YES
Data Type: INT64
Description: The time taken to resolve the appeal at this level, measured in minutes.
The Turnaround Time Minute calculates the duration between the notification and resolution times in minutes.
If the resolution timestamp is not set, the duration remains undefined.
If this duration exceeds 9,999,999.99 minutes, it is also considered undefined, ensuring that only valid time frames are recorded.

Appeal Resolve_Indicator

Is Nullable: YES
Data Type: BOOL
Description: Boolean value indicating whether the appeal level has been resolved.
The Resolve Indicator is a true/false value that shows whether the appeal level has been resolved.
If there is no resolution timestamp recorded, the indicator is set to false, indicating that the appeal is still open.
Otherwise, it is set to true, signifying that the appeal has been resolved.

Appeal Resolved_State

Is Nullable: YES
Data Type: STRING
Description: The Resolution State indicates the timing status of the appeal level's resolution.
It classifies the resolution as UNKNOWN if there is no timestamp indicating resolution.
If the resolution occurs on or before the due timestamp, it is classified as TIMELY.
If it occurs after the due timestamp, it is considered UNTIMELY.

Appeal Resolved_Days_Count

Is Nullable: YES
Data Type: INT64
Description: The number of days taken to resolve the appeal at this level.
The Resolved Days Count measures the number of days it took to resolve the appeal at this level.
If no resolution timestamp is recorded, the count remains undefined.
Otherwise, it is calculated as the difference in days between the notification and resolution timestamps.

Appeal Resolved_Days_Range_Description

Is Nullable: YES
Data Type: STRING
Description: The Resolution Time Category describes the range of days it took to resolve the appeal, categorized into intervals.
If no resolution timestamp is available, the category remains unspecified.
Otherwise, the duration is categorized as follows:
- Less than 8 days: Closed in 0 - 7 Days
- 8 to 14 days: Closed in 8 - 14 Days
- 15 to 21 days: Closed in 15 - 21 Days
- 22 to 30 days: Closed in 22 - 30 Days
- 31 to 60 days: Closed in 31 - 60 Days
- 61 to 90 days: Closed in 61 - 90 Days
- More than 90 days: Closed in > 90 Days
If the resolution takes exceptionally long, exceeding 99,999 days, the category is also left unspecified to avoid misrepresentation.

Appeal Resolved_Days_Range_Sort_Order

Is Nullable: YES
Data Type: INT64
Description: The Resolution Days Sorting Index assigns a numeric value to categorize the range of days taken to resolve an appeal, facilitating sorting and analysis.
The categorization is defined as follows:
- Resolution time is unspecified or exceedingly long (more than 99,999 days): 99
- Resolved in less than 8 days: 1
- Resolved in 8 to 14 days: 2
- Resolved in 15 to 21 days: 3
- Resolved in 22 to 30 days: 4
- Resolved in 31 to 60 days: 5
- Resolved in 61 to 90 days: 6
- Resolved in more than 90 days: 7
This indexing helps streamline the management and prioritization of appeals based on their resolution times.

Appeal Custom Fields

Appeal_Custom_Field_Key

Is Nullable: YES
Data Type: INT64
Description: The unique identifier for a custom field within the appeal record.

Select_Key

Is Nullable: YES
Data Type: INT64
Description: A key that links the custom field to a predefined list of selections or options.

Level_Key

Is Nullable: YES
Data Type: INT64
Description: The key associating the custom field with a specific level of the appeal process.

ID

Is Nullable: YES
Data Type: STRING
Description: A unique identifier or code for the custom field, often used for internal tracking.

Title

Is Nullable: YES
Data Type: STRING
Description: The display name or title of the custom field, used to label the field in user interfaces.

Use

Is Nullable: YES
Data Type: STRING
Description: Describes how the custom field is utilized within the appeal process or its intended purpose.

Created_Timestamp

Is Nullable: YES
Data Type: TIMESTAMP
Description: The timestamp when the custom field was created, providing a historical record of when it was added to the system.

Last_Modified_Timestamp

Is Nullable: YES
Data Type: TIMESTAMP
Description: The timestamp of the last modification made to the custom field, helping track changes and updates.

Value

Is Nullable: YES
Data Type: STRING
Description: The actual data or content entered into the custom field for a specific appeal case.

Value_Description

Is Nullable: YES
Data Type: STRING
Description: A descriptive text or explanation of the data entered into the custom field, providing context or additional details.

Select_Created_Timestamp

Is Nullable: YES
Data Type: TIMESTAMP
Description: The creation timestamp for the selection or option associated with the custom field, indicating when the option was added to the list.
The timestamp is sourced from Custom_Fields.Created_Timestamp.
If this value is not available, it defaults to Custom_Fields.Values.Created_Timestamp.

Select_Last_Modified_Timestamp

Is Nullable: YES
Data Type: TIMESTAMP
Description: The last modified timestamp for the selection or option tied to the custom field, showing when any changes were made to the option list.
The timestamp is obtained from Custom_Fields.Last_Modified_Timestamp.
If this is unavailable, it falls back to Custom_Fields.Values.Last_Modified_Timestamp.

Appeal Log

Member_ID

Is Nullable: YES
Data Type: STRING
Description: Identifier for the member related to the appeal.

Request_Key

Is Nullable: YES
Data Type: INT64
Description: Unique key for the request.

Appeal Dataset Specification

Appeal Log

Fields

Request_ID
Type: STRING
Required: YES
Description: Identifier for the specific request tied to the appeal.
Appeal_Key
Type: INT64
Required: YES
Description: Unique key for the appeal record.
Appeal_ID
Type: STRING
Required: YES
Description: Identifier for the specific appeal.
Void_Indicator
Type: BOOL
Required: YES
Description: Boolean flag indicating if the appeal is voided.
Process_Type
Type: STRING
Required: YES
Description: Type of appeal process being followed.
Process_Type_Description
Type: STRING
Required: YES
Description: Description of the appeal process type.
Created_User_ID
Type: STRING
Required: YES
Description: User ID of the person who created the appeal record.
Last_Modified_User_ID
Type: STRING
Required: YES
Description: User ID of the person who last modified the appeal record.
Created_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp of when the appeal was created.
Last_Modified_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp of the last modification to the appeal.
Levels
Type: ARRAY
Required: NO
Description: Array of structures containing detailed information about each level of the appeal process, including the level's identifier, code, description, timestamps for various stages, reasons, and other related data.

Levels

Fields

Key
Type: INT64
Required: YES
Description: Unique identifier for the appeal log entry.
Level_ID
Type: STRING
Required: YES
Description: Identifier for the level of appeal within the process.
Code
Type: STRING
Required: YES
Description: Status code related to the appeal's current stage.
Description
Type: STRING
Required: YES
Description: Brief description of the appeal's current status.
Type
Type: STRING
Required: YES
Description: Type categorization of the appeal.
Type_Description
Type: STRING
Required: YES
Description: Detailed description of the appeal type.
Notify_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the appeal was officially notified.
Due_Timestamp
Type: TIMESTAMP
Required: YES
Description: Expected timestamp by which the appeal should be resolved.
Resolution_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the appeal was resolved.
All_Documents_Received_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp marking when all required documents for the appeal were received.
Reason
Type: STRING
Required: YES
Description: Reason for the appeal being logged.
Reason_Description
Type: STRING
Required: YES
Description: Detailed explanation of the reason for the appeal.
Void_Indicator
Type: BOOL
Required: YES
Description: Indicates whether the log entry has been voided.
Source
Type: STRING
Required: YES
Description: Origin of the appeal or where it was initiated.
Source_Description
Type: STRING
Required: YES
Description: Description of the source from which the appeal originated.
Requestor
Type: STRING
Required: YES
Description: Identifier of the person or entity requesting the appeal.
Requestor_Description
Type: STRING
Required: YES
Description: Description of the requestor involved in the appeal.
Reviewer
Type: STRING
Required: YES
Description: Identifier of the person or entity reviewing the appeal.
Reviewer_Description
Type: STRING
Required: YES
Description: Description of the reviewer's role or background.
Due_Date_Reason
Type: STRING
Required: YES
Description: Reason for setting the specific due date for the appeal.
Due_Date_Reason_Description
Type: STRING
Required: YES
Description: Detailed description of why the due date was set.
Created_User_ID
Type: STRING
Required: YES
Description: User ID of the person who created the log entry.
Last_Modified_User_ID
Type: STRING
Required: YES
Description: User ID of the person who last modified the log entry.
Owner_User_ID
Type: STRING
Required: YES
Description: User ID of the owner of the appeal process.
Owner_Department_ID
Type: STRING
Required: YES
Description: Department ID responsible for overseeing the appeal process.
Created_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the log entry was created.
Last_Modified_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the log entry was last modified.

Outcomes

Key
Type: INT64
Required: YES
Description: Unique identifier for each outcome associated with the appeal.
Appeal_Status
Type: STRING
Required: YES
Description: The status of the appeal at the time the outcome was recorded.
Appeal_Status_Description
Type: STRING
Required: YES
Description: Description of the appeal status at the time of recording.
Outcome_Key
Type: INT64
Required: YES
Description: Key linking to a specific appeal outcome.
Outcome_ID
Type: STRING
Required: YES
Description: Identifier for the specific outcome of the appeal.
Created_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the outcome was recorded in the log.
Last_Modified_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the outcome record was last updated.

Custom Fields

Key
Type: INT64
Required: YES
Description: Unique identifier for each custom field entry.
ID
Type: STRING
Required: YES
Description: Identifier for the custom field.
Title
Type: STRING
Required: YES
Description: Title of the custom field, indicating its content.
Use
Type: STRING
Required: YES
Description: Intended use or application of the custom field.
Values
Type: ARRAY
Required: YES
Description: An array of structures containing detailed information about the list of values stored within the custom field.

Values

Select_Key
Type: INT64
Required: YES
Description: Unique key used to select the value.
Value
Type: STRING
Required: YES
Description: Specific value stored within the custom field.
Value_Description
Type: STRING
Required: YES
Description: Description of the value, providing additional details.
Created_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the value was created.
Last_Modified_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp when the value was last updated.
Created_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp indicating when the custom field was created.
Last_Modified_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp indicating when the custom field was last updated.

Additional Fields

Record_Last_Modified_Timestamp
Type: TIMESTAMP
Required: YES
Description: Timestamp of the last modification to the appeal log record.
Transaction_User_ID
Type: STRING
Required: YES
Description: User ID associated with the last transaction on the appeal log.
Deleted_Indicator
Type: BOOL
Required: YES
Description: Boolean flag indicating if the log entry has been marked as deleted.
Type
Type: STRING
Required: YES
Description: Type of log entry, providing context for the record.
Tenant
Type: STRING
Required: YES
Description: Identifier of the tenant or domain within which the appeal is managed.

Appeal Dataset Specification

Appeal Log

Columns

Active_Version
Is Nullable: YES
Data Type: BOOL
Description: Boolean flag indicating if the log entry is the active version of the appeal record.
Date
Is Nullable: YES
Data Type: DATE
Description: Date associated with the log entry.
Sequence
Is Nullable: YES
Data Type: INT64
Description: Sequence number of the log entry, indicating its order.
Source_System_Name
Is Nullable: YES
Data Type: STRING
Description: Name of the source system where the log entry originated.
Source_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp from the source system for the log entry.
Source_File_Name
Is Nullable: YES
Data Type: STRING
Description: Name of the file in the source system that corresponds to the log entry.
Schema_Version
Is Nullable: YES
Data Type: FLOAT64
Description: Version of the schema used for the appeal log.
Member_Partition
Is Nullable: YES
Data Type: INT64
Description: Partition key used for segmenting data by member.
Action
Is Nullable: YES
Data Type: STRING
Description: Action taken in the log entry, such as creating, updating, or deleting an appeal record.
Transaction_Actor_Type
Is Nullable: YES
Data Type: STRING
Description: Type of the actor performing the transaction.

Appeal Raw

Columns

Member_ID
Is Nullable: YES
Data Type: STRING
Description: The identifier for the member associated with the appeal, linking the appeal process to the specific member's case.
Request_Key
Is Nullable: YES
Data Type: INT64
Description: A unique identifier for the request related to the appeal, providing a link to the specific service or action being appealed.
Request_ID
Is Nullable: YES
Data Type: STRING
Description: A string identifier for the request, often used as a human-readable reference in the appeal process.
Appeal_Key
Is Nullable: YES
Data Type: INT64
Description: The unique key for the appeal record, serving as a primary identifier within the system.
Appeal_ID
Is Nullable: YES
Data Type: STRING
Description: A string identifier for the appeal, used for tracking and reference in communications or documentation.
Void_Indicator
Is Nullable: YES
Data Type: BOOL
Description: A boolean flag indicating whether the appeal record has been voided or invalidated in the system.
Process_Type
Is Nullable: YES
Data Type: STRING
Description: Identifies the type of appeal process being followed, categorizing the appeal based on the procedural rules or steps involved.
Process_Type_Description
Is Nullable: YES
Data Type: STRING
Description: A detailed description of the process type, explaining the procedural context of the appeal.
Created_User_ID
Is Nullable: YES
Data Type: STRING
Description: The user ID of the individual who created the appeal record, for audit and tracking purposes.
Last_Modified_User_ID
Is Nullable: YES
Data Type: STRING
Description: The user ID of the individual who last modified the appeal record, useful for audit trails and accountability.
Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: The timestamp when the appeal record was first created, indicating the initiation time of the appeal process.
Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: The timestamp of the most recent modification to the appeal record, showing the latest update or change.
Levels
Is Nullable: NO
Data Type: ARRAY
Description: An array of structures containing detailed information about each level of the appeal process, including IDs, descriptions, timestamps, and other relevant data.

Levels

Fields

Key
Is Nullable: YES
Data Type: INT64
Description: Unique identifier for the record.
Level_ID
Is Nullable: YES
Data Type: STRING
Description: Identifier for the level of appeal.
Code
Is Nullable: YES
Data Type: STRING
Description: Code for the appeal status.
Description
Is Nullable: YES
Data Type: STRING
Description: Description of the appeal.
Type
Is Nullable: YES
Data Type: STRING
Description: Type of the appeal.
Type_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the appeal type.
Notify_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the appeal was notified.
Due_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Expected completion date for the appeal.
Resolution_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the appeal was resolved.
All_Documents_Received_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when all required documents were received.
Reason
Is Nullable: YES
Data Type: STRING
Description: Reason for the appeal.
Reason_Description
Is Nullable: YES
Data Type: STRING
Description: Detailed description of the reason for the appeal.
Void_Indicator
Is Nullable: YES
Data Type: BOOL
Description: Indicator if the record is voided.
Source
Is Nullable: YES
Data Type: STRING
Description: Source of the appeal.
Source_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the appeal source.
Requestor
Is Nullable: YES
Data Type: STRING
Description: ID or name of the person who filed the appeal.
Requestor_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the requestor.
Reviewer
Is Nullable: YES
Data Type: STRING
Description: ID or name of the person reviewing the appeal.
Reviewer_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the reviewer.
Due_Date_Reason
Is Nullable: YES
Data Type: STRING
Description: Reason for setting the appeal deadline.
Due_Date_Reason_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the reason for setting the deadline.
Created_User_ID
Is Nullable: YES
Data Type: STRING
Description: ID of the user who created the record.
Last_Modified_User_ID
Is Nullable: YES
Data Type: STRING
Description: ID of the user who last modified the record.
Owner_User_ID
Is Nullable: YES
Data Type: STRING
Description: ID of the user responsible for the record.
Owner_Department_ID
Is Nullable: YES
Data Type: STRING
Description: ID of the department responsible for the record.
Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the record was created.
Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the record was last modified.

Levels Outcomes

Fields

Key
Is Nullable: YES
Data Type: INT64
Description: Unique identifier for the outcome.
Appeal_Status
Is Nullable: YES
Data Type: STRING
Description: Status of the appeal at the time of recording.
Appeal_Status_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the appeal status.
Outcome_Key
Is Nullable: YES
Data Type: INT64
Description: Key associated with a specific outcome.
Outcome_ID
Is Nullable: YES
Data Type: STRING
Description: Identifier for the outcome of the appeal.
Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the outcome record was created.
Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the outcome record was last modified.

Levels Custom Fields

Fields

Key
Is Nullable: YES
Data Type: INT64
Description: Unique identifier for the custom field.
ID
Is Nullable: YES
Data Type: STRING
Description: Identifier of the custom field.
Title
Is Nullable: YES
Data Type: STRING
Description: Title of the custom field.
Use
Is Nullable: YES
Data Type: STRING
Description: Use of the custom field.
Values
Is Nullable: YES
Data Type: ARRAY
Description: Array containing values associated with the custom field.

Appeal Dataset Specification

Custom Fields

Values

Select_Key
Is Nullable: YES
Data Type: INT64
Description: Unique key for selecting.
Value
Is Nullable: YES
Data Type: STRING
Description: Value of the field.
Value_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the field value.
Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the field value was created.
Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the field value was last modified.

Custom Fields Metadata

Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the custom field was created.
Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the custom field was last modified.

Appeal Raw Data

Record Metadata

Record_Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp indicating the last modification to the appeal record, providing a point of reference for the most recent update.
Transaction_User_ID
Is Nullable: YES
Data Type: STRING
Description: The user ID associated with the last transaction on the appeal record, identifying who made the most recent change.
Deleted_Indicator
Is Nullable: YES
Data Type: BOOL
Description: A boolean flag indicating whether the appeal record has been marked as deleted, providing a status of the record’s lifecycle.

Appeal Metadata

Type
Is Nullable: YES
Data Type: STRING
Description: The type of the record in the appeal process, categorizing the nature or context of the appeal.
Tenant
Is Nullable: YES
Data Type: STRING
Description: Identifies the tenant or organizational unit within which the appeal exists, useful for multi-tenant systems.
Active_Version
Is Nullable: YES
Data Type: BOOL
Description: A boolean value indicating whether this record is the active version of the appeal, signifying its current use or relevance.
Date
Is Nullable: YES
Data Type: DATE
Description: The date associated with the record, often reflecting the date of the appeal log entry or related action.
Sequence
Is Nullable: YES
Data Type: INT64
Description: A sequence number for the record, which might indicate its order in a series or process flow.
Source_System_Name
Is Nullable: YES
Data Type: STRING
Description: The name of the system from which the appeal data originated, helping to trace the source of information.
Source_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: The timestamp when the data was entered or captured in the source system, providing a temporal context.
Source_File_Name
Is Nullable: YES
Data Type: STRING
Description: The name of the file from the source system that includes the appeal record, useful for data lineage and tracking.
Schema_Version
Is Nullable: YES
Data Type: FLOAT64
Description: The version of the data schema used for the appeal record, indicating the structure or format of the data.
Member_Partition
Is Nullable: YES
Data Type: INT64
Description: An identifier used for partitioning data by member, which can help in organizing and optimizing data storage and access.
Transaction_Actor_Type
Is Nullable: YES
Data Type: STRING
Description: Type of the actor performing the transaction.

Appeal Features

Appeal Case Records

Documents essential details of each appeal, including reasons, current status, and results.

Customizable Data Points

"Appeal Custom Fields" allow for recording specific, additional information tailored to individual appeals, facilitating detailed tracking.

Action and Event Logs

The "Appeal Log" provides a chronological record of actions and events, creating a detailed timeline for the progression of each case.

Resolution Time Categories

Time Ranges

Less than 8 days: 'Closed in 0 - 7 Days'
8 to 14 days: 'Closed in 8 - 14 Days'
15 to 21 days: 'Closed in 15 - 21 Days'
22 to 30 days: 'Closed in 22 - 30 Days'
31 to 60 days: 'Closed in 31 - 60 Days'
61 to 90 days: 'Closed in 61 - 90 Days'
More than 90 days: 'Closed in > 90 Days'

Resolution Codes

Resolution time is unspecified or exceedingly long (more than 99,999 days): 99
Resolved in less than 8 days: 1
Resolved in 8 to 14 days: 2
Resolved in 15 to 21 days: 3
Resolved in 22 to 30 days: 4
Resolved in 31 to 60 days: 5
Resolved in 61 to 90 days: 6
Resolved in more than 90 days: 7

Appeal Table Specification

Table Name	Column Name	Is Nullable	Data Type	Description
Appeal	Member_ID	YES	STRING	Identifier for the member associated with the appeal.
Appeal	Request_Key	YES	INT64	Unique key of the request that the appeal is related to.
Appeal	Request_ID	YES	STRING	Unique identifier of the request that the appeal is addressing.
Appeal	Appeal_Key	YES	INT64	Primary key of the appeal record.
Appeal	Level_Key	YES	INT64	Identifier for the specific level or stage of the appeal process.
Appeal	Appeal_ID	YES	STRING	A unique identifier assigned to the appeal.
Appeal	Appeal_Void_Indicator	YES	BOOL	Boolean indicator showing whether the appeal has been voided or cancelled.
Appeal	Process_Type	YES	STRING	Describes the type of appeal process being undertaken.
Appeal	Process_Type_Description	YES	STRING	Detailed description of the appeal process type.
Appeal	Appeal_Created_User_ID	YES	STRING	Identifier for the user who created the appeal.
Appeal	Appeal_Created_User_Name	YES	STRING	Name of the user who created the appeal.
Appeal	Appeal_Last_Modified_User_ID	YES	STRING	Identifier for the user who last modified the appeal.
Appeal	Appeal_Last_Modified_User_Name	YES	STRING	Name of the user who last modified the appeal.
Appeal	Appeal_Created_Timestamp	YES	TIMESTAMP	Timestamp for when the appeal was created.
Appeal	Appeal_Last_Modified_Timestamp	YES	TIMESTAMP	Timestamp for the last modification of the appeal.
Appeal	Level_ID	YES	STRING	Identifier for the level of the appeal within the process.
Appeal	Level_Code	YES	STRING	Code representing the level of the appeal within the process.
Appeal	Level_Description	YES	STRING	Description of the level of the appeal.

Appeal Dataset Specification

Appeal Level Attributes

Level_Type

Required: YES
Data Type: STRING
Description: Type of appeal level. See Dictionary Appeal Type table in the Dictionary Dataset Specification.

Level_Type_Description

Required: YES
Data Type: STRING
Description: Description of the appeal level type. See Dictionary Appeal Type table in the Dictionary Dataset Specification.

Level_Notify_Timestamp

Required: YES
Data Type: TIMESTAMP
Description: Timestamp indicating when parties were notified of this level of the appeal.

Level_Due_Timestamp

Required: YES
Data Type: TIMESTAMP
Description: Timestamp indicating the due date for this level of the appeal.

Level_Resolution_Timestamp

Required: YES
Data Type: TIMESTAMP
Description: Timestamp when the appeal was resolved at this level.

Level_All_Documents_Received_Timestamp

Required: YES
Data Type: TIMESTAMP
Description: Timestamp indicating when all necessary documents for the appeal were received.

Level_Reason

Required: YES
Data Type: STRING
Description: Reason for the appeal at this level. See Dictionary Appeal Reason table in the Dictionary Dataset Specification.

Level_Reason_Description

Required: YES
Data Type: STRING
Description: Description of the reason for the appeal at this level. See Dictionary Appeal Reason table in the Dictionary Dataset Specification.

Level_Void_Indicator

Required: YES
Data Type: BOOL
Description: Indicates whether the appeal at this level was voided.

Level_Source

Required: YES
Data Type: STRING
Description: The source of the appeal at this level. See Dictionary Appeal Source table in the Dictionary Dataset Specification.

Level_Source_Description

Required: YES
Data Type: STRING
Description: Description of the source of the appeal at this level. See Dictionary Appeal Source table in the Dictionary Dataset Specification.

Level_Requestor

Required: YES
Data Type: STRING
Description: Identifier for the requester of the appeal at this level. See Dictionary Appeal Requestor table in the Dictionary Dataset Specification.

Level_Requestor_Description

Required: YES
Data Type: STRING
Description: Description of the requester of the appeal at this level. See Dictionary Appeal Requestor table in the Dictionary Dataset Specification.

Level_Reviewer

Required: YES
Data Type: STRING
Description: Identifier for the reviewer of the appeal at this level. See Dictionary Appeal Reviewer table in the Dictionary Dataset Specification.

Level_Reviewer_Description

Required: YES
Data Type: STRING
Description: Description of the reviewer of the appeal at this level. See Dictionary Appeal Reviewer table in the Dictionary Dataset Specification.

Level_Due_Date_Reason

Required: YES
Data Type: STRING
Description: Reason for the due date set for this level of the appeal. See Dictionary Appeal Due Date Change Reason table in the Dictionary Dataset Specification.

Level_Due_Date_Reason_Description

Required: YES
Data Type: STRING
Description: Description of the reason for the due date at this level of the appeal. See Dictionary Appeal Due Date Change Reason table in the Dictionary Dataset Specification.

Level_Created_Timestamp

Required: YES
Data Type: TIMESTAMP
Description: Timestamp when the level of the appeal was created.

Level_Last_Modified_Timestamp

Required: YES
Data Type: TIMESTAMP
Description: Timestamp when the level of the appeal was last modified.

Due_Date_Change_Indicator

Required: YES
Data Type: BOOL
Description: Indicates whether there was a change in the due date during this level of the appeal process.
This condition determines if a due date for a task has been set manually.
The system checks the Due_Date_Reason field to see if it contains a specific reason, and that the reason is not "System Calculated."
If these conditions are met, the due date is considered manually set.
This allows for precise tracking of due dates that are adjusted by users, ensuring that manual interventions are documented and clear.

Level_Created_User_ID

Required: YES
Data Type: STRING
Description: User ID of the person who created the appeal level.

Level_Created_User_Name

Required: YES
Data Type: STRING
Description: Full name of the person who created the appeal level.
The full name of the user, as listed in User.Full_Name, is associated with the account that created the entry, identified by Levels.Created_User_ID.

Level_Last_Modified_User_ID

Required: YES
Data Type: STRING
Description: User ID of the person who last modified the appeal level.

Level_Last_Modified_User_Name

Required: YES
Data Type: STRING
Description: Full name of the person who last modified the appeal level.
The full name of the user, as listed in User.Full_Name, is linked to the account that last modified the entry, identified by Levels.Last_Modified_User_ID.

Level_Owner_ID

Required: YES
Data Type: STRING
Description: The current owner of the appeal level.
The Owner_ID represents the current owner of the item.
It is determined by checking the Levels.User_Owner_ID.
If this is not available, the ownership defaults to Levels.Department_Owner_ID.
This ensures that each item is always associated with an owner, whether an individual user or a department.

Level_Owner_Type

Required: YES
Data Type: STRING
Description: The type of the current owner of the appeal level (individual or department).
Ownership is categorized as follows:
- 'INDV' when there is a Levels.User_Owner_ID.
- 'DEPT' when there is a Levels.Department_Owner_ID.
- NULL otherwise.

Level_Owner_Name

Required: YES
Data Type: STRING
Description: The name of the current owner of the appeal level.
The current owner type identifies whether the owner of the appeal level is an individual or a department.
This is determined by checking for an owner identifier:
- If Levels.User_Owner_ID is present, the owner type is labeled 'INDV' for individual.
- If Levels.Department_Owner_ID is present instead, the owner type is labeled 'DEPT' for department.
- If neither identifier is available, the owner type remains unspecified.

First_Level_Owner_ID

Required: YES
Data Type: STRING
Description: The owner ID of the first level found based on history records in the appeal log.
The First_Owner_ID represents the owner ID of the initial level recorded in the appeal log, based on historical entries.
This ID is retrieved from the earliest record for each request ID in the Appeal_LOG table, where records are organized by the earliest Last_Modified_Timestamp.
The system uses User_Owner_ID if available.

Appeal Dataset Specification

Appeal Attributes

First_Level_Owner_Type

Required: YES
Type: STRING
Description:\ The owner type of the first level found based on history records in the appeal log.
The First Owner Type indicates whether the initial owner, according to historical data in the appeal log, is an individual or a department.
Determined from the oldest entry for each level ID in the Appeal_LOG table, sorted by the earliest Last_Modified_Timestamp.
If Appeal_LOG.Levels.User_Owner_ID is present, the owner type is labeled INDV for individual.
If Appeal_LOG.Levels.Department_Owner_ID is present instead, the owner type is labeled DEPT for department.
If neither is available, no owner type is assigned.

First_Level_Owner_Name

Required: YES
Type: STRING
Description:\ The owner name of the first level found based on history records in the appeal log.
Identifies the name of the earliest recorded owner for each level ID in the Appeal_LOG table, based on the order of entries by the earliest Last_Modified_Timestamp.
If there is a User_Owner_ID listed in Appeal_LOG.Levels, the individual's full name from owner_user.Full_Name is used.
If a Department_Owner_ID is present, the department's name from owner_dept.Department_Name is used.
If neither identifier is found, no owner name is provided.

Previous_Level_Owner_ID

Required: YES
Type: STRING
Description:\ The previous owner ID before the current level in the appeal process.
Identifies the owner immediately preceding the current level in the appeal process.
Determined by examining the history records for each level ID in the Appeal_LOG table, organized by the earliest Last_Modified_Timestamp.
The system selects the User_Owner_ID if available; otherwise, it uses the Department_Owner_ID to identify the previous owner.

Previous_Level_Owner_Type

Required: YES
Type: STRING
Description:\ The type of the previous owner before the current level in the appeal process.
Identifies whether the owner immediately before the current level in the appeal process is an individual or a department.
Determined by looking at the history records for each level ID in the Appeal_LOG table, sorted by the earliest Last_Modified_Timestamp.
If a User_Owner_ID from Appeal_LOG.Levels is present, the owner type is classified as INDV for individual.
If a Department_Owner_ID is recorded instead, the owner type is classified as DEPT for department.
If no owner ID is available, the owner type remains unspecified.

Previous_Level_Owner_Name

Required: YES
Type: STRING
Description:\ The name of the previous owner before the current level in the appeal process.
Determines the name of the owner just before the current level in the appeal process.
Established by examining the historical records for each level ID in the Appeal_LOG table, arranged by the earliest Last_Modified_Timestamp.
If a User_Owner_ID is listed within Appeal_LOG.Levels, the corresponding individual's full name from owner_user.Full_Name is used.
If a Department_Owner_ID is noted instead, the name from owner_dept.Department_Name is applied.
If neither identifier is found, no owner name is recorded.

Level_Outcome_Key

Required: YES
Type: INT64
Description:\ Unique key associated with the outcome of the appeal level.

Level_Outcome_Appeal_Status

Required: YES
Type: STRING
Description:\ Status of the appeal at this level.
See Dictionary Appeal Status table in the Dictionary Dataset Specification.

Level_Outcome_Appeal_Status_Description

Required: YES
Type: STRING
Description:\ Description of the appeal status at this level.
See Dictionary Appeal Status table in the Dictionary Dataset Specification.

Review_Outcome_Key

Required: YES
Type: INT64
Description:\ Unique key associated with the outcome of the appeal review.

Outcome_ID

Required: YES
Type: STRING
Description:\ Identifier for the outcome of the appeal level.

Level_Outcome_Created_Timestamp

Required: YES
Type: TIMESTAMP
Description:\ Timestamp when the outcome of the appeal level was created.

Level_Outcome_Last_Modified_Timestamp

Required: YES
Type: TIMESTAMP
Description:\ Timestamp when the outcome of the appeal level was last modified.

Turnaround_Time_Minute

Required: YES
Type: INT64
Description:\ The time taken to resolve the appeal at this level, measured in minutes.
Calculates the duration between the notification and resolution times in minutes.
If the resolution timestamp is not set, the duration remains undefined.
Measures the time elapsed from the Notify Timestamp to the Resolution Timestamp.
If this duration exceeds 9,999,999.99 minutes, it is also considered undefined, ensuring that only valid time frames are recorded.

Resolve_Indicator

Required: YES
Type: BOOL
Description:\ Boolean value indicating whether the appeal level has been resolved.
A true/false value that shows whether the appeal level has been resolved.
If there is no resolution timestamp recorded, the indicator is set to false, indicating that the appeal is still open.
Otherwise, it is set to true, signifying that the appeal has been resolved.

Resolved_State

Required: YES
Type: STRING
Description:\ The Resolution State indicates the timing status of the appeal level's resolution.
Classified as UNKNOWN if there is no timestamp indicating resolution.
If the resolution occurs on or before the due timestamp, it is classified as TIMELY.
If it occurs after the due timestamp, it is considered UNTIMELY.

Resolved_Days_Count

Required: YES
Type: INT64
Description:\ The number of days taken to resolve the appeal at this level.
Measures the number of days it took to resolve the appeal at this level.
If no resolution timestamp is recorded, the count remains undefined.
Calculated as the difference in days between the notification and resolution timestamps.

Resolved_Days_Range_Description

Required: YES
Type: STRING
Description:\ The Resolution Time Category describes the range of days it took to resolve the appeal.

Appeal Dataset Specification

Resolution Duration Categorization

If no resolution timestamp is available, the category remains unspecified. Otherwise, the duration is categorized as follows:

Less than 8 days: Closed in 0 - 7 Days
8 to 14 days: Closed in 8 - 14 Days
15 to 21 days: Closed in 15 - 21 Days
22 to 30 days: Closed in 22 - 30 Days
31 to 60 days: Closed in 31 - 60 Days
61 to 90 days: Closed in 61 - 90 Days
More than 90 days: Closed in > 90 Days

If the resolution takes exceptionally long, exceeding 99,999 days, the category is also left unspecified to avoid misrepresentation.

Resolution Days Sorting Index

Column: Resolved_Days_Range_Sort_Order

Data Type: INT64
Nullable: YES
Description: The Resolution Days Sorting Index assigns a numeric value to categorize the range of days taken to resolve an appeal, facilitating sorting and analysis. The categorization is defined as follows:
Resolution time is unspecified or exceedingly long (more than 99,999 days): 99
Resolved in less than 8 days: 1
Resolved in 8 to 14 days: 2
Resolved in 15 to 21 days: 3
Resolved in 22 to 30 days: 4
Resolved in 31 to 60 days: 5
Resolved in 61 to 90 days: 6
Resolved in more than 90 days: 7

This indexing helps streamline the management and prioritization of appeals based on their resolution times.

Table: Appeal_Custom_Fields

Columns

Appeal_Custom_Field_Key

Data Type: INT64
Nullable: YES
Description: The unique identifier for a custom field within the appeal record.

Select_Key

Data Type: INT64
Nullable: YES
Description: A key that links the custom field to a predefined list of selections or options.

Level_Key

Data Type: INT64
Nullable: YES
Description: The key associating the custom field with a specific level of the appeal process.

Data Type: STRING
Nullable: YES
Description: A unique identifier or code for the custom field, often used for internal tracking.

Title

Data Type: STRING
Nullable: YES
Description: The display name or title of the custom field, used to label the field in user interfaces.

Use

Data Type: STRING
Nullable: YES
Description: Describes how the custom field is utilized within the appeal process or its intended purpose.

Created_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: The timestamp when the custom field was created, providing a historical record of when it was added to the system.

Last_Modified_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: The timestamp of the last modification made to the custom field, helping track changes and updates.

Value

Data Type: STRING
Nullable: YES
Description: The actual data or content entered into the custom field for a specific appeal case.

Value_Description

Data Type: STRING
Nullable: YES
Description: A descriptive text or explanation of the data entered into the custom field, providing context or additional details.

Select_Created_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: The creation timestamp for the selection or option associated with the custom field, indicating when the option was added to the list. The timestamp is sourced from Custom_Fields.Created_Timestamp. If this value is not available, it defaults to Custom_Fields.Values.Created_Timestamp.

Select_Last_Modified_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: The last modified timestamp for the selection or option tied to the custom field, showing when any changes were made to the option list. The timestamp is obtained from Custom_Fields.Last_Modified_Timestamp. If this is unavailable, it falls back to Custom_Fields.Values.Last_Modified_Timestamp.

Table: Appeal_log

Columns

Member_ID

Data Type: STRING
Nullable: YES
Description: Identifier for the member related to the appeal.

Request_Key

Data Type: INT64
Nullable: YES
Description: Unique key for the request associated with the appeal.

Request_ID

Data Type: STRING
Nullable: YES
Description: Identifier for the specific request tied to the appeal.

Appeal_Key

Data Type: INT64
Nullable: YES
Description: Unique key for the appeal record.

Appeal_ID

Data Type: STRING
Nullable: YES
Description: Identifier for the specific appeal.

Void_Indicator

Data Type: BOOL
Nullable: YES
Description: Boolean flag indicating if the appeal is voided.

Process_Type

Data Type: STRING
Nullable: YES
Description: Type of appeal process being followed.

Process_Type_Description

Data Type: STRING
Nullable: YES
Description: Description of the appeal process type.

Created_User_ID

Data Type: STRING
Nullable: YES
Description: User ID of the person who created the appeal record.

Last_Modified_User_ID

Data Type: STRING
Nullable: YES
Description: User ID of the person who last modified the appeal record.

Created_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: Timestamp of when the appeal was created.

Last_Modified_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: Timestamp of the last modification to the appeal.

Levels

Data Type: ARRAY
Nullable: NO
Description: Array of structures containing detailed information about each level of the appeal process, including the level's identifier, code, description, timestamps for various stages, reasons, and other related data.

Levels Substructure

Key

Data Type: INT64
Nullable: YES
Description: Unique identifier for the appeal log entry.

Level_ID

Data Type: STRING
Nullable: YES
Description: Identifier for the level of appeal within the process.

Code

Data Type: STRING
Nullable: YES
Description: Status code related to the appeal's current stage.

Description

Data Type: STRING
Nullable: YES
Description: Brief description of the appeal's current status.

Type

Data Type: STRING
Nullable: YES
Description: Type categorization of the appeal.

Type_Description

Data Type: STRING
Nullable: YES
Description: Detailed description of the appeal type.

Notify_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: Timestamp when the appeal was officially notified.

Due_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: Expected timestamp by which the appeal should be resolved.

Resolution_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: Timestamp when the appeal was resolved.

All_Documents_Received_Timestamp

Data Type: TIMESTAMP
Nullable: YES
Description: Timestamp marking when all required documents for the appeal were received.

Reason

Data Type: STRING
Nullable: YES
Description: Reason for the appeal being logged.

Appeal Dataset Specification

Reason Description

Column Name: Reason_Description
Is Nullable: YES
Data Type: STRING
Description: Detailed explanation of the reason for the appeal.

Levels

Void Indicator

Column Name: Void_Indicator
Is Nullable: YES
Data Type: BOOL
Description: Indicates whether the log entry has been voided.

Source

Column Name: Source
Is Nullable: YES
Data Type: STRING
Description: Origin of the appeal or where it was initiated.

Source Description

Column Name: Source_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the source from which the appeal originated.

Requestor

Column Name: Requestor
Is Nullable: YES
Data Type: STRING
Description: Identifier of the person or entity requesting the appeal.

Requestor Description

Column Name: Requestor_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the requestor involved in the appeal.

Reviewer

Column Name: Reviewer
Is Nullable: YES
Data Type: STRING
Description: Identifier of the person or entity reviewing the appeal.

Reviewer Description

Column Name: Reviewer_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the reviewer's role or background.

Due Date Reason

Column Name: Due_Date_Reason
Is Nullable: YES
Data Type: STRING
Description: Reason for setting the specific due date for the appeal.

Due Date Reason Description

Column Name: Due_Date_Reason_Description
Is Nullable: YES
Data Type: STRING
Description: Detailed description of why the due date was set.

Created User ID

Column Name: Created_User_ID
Is Nullable: YES
Data Type: STRING
Description: User ID of the person who created the log entry.

Last Modified User ID

Column Name: Last_Modified_User_ID
Is Nullable: YES
Data Type: STRING
Description: User ID of the person who last modified the log entry.

Owner User ID

Column Name: Owner_User_ID
Is Nullable: YES
Data Type: STRING
Description: User ID of the owner of the appeal process.

Owner Department ID

Column Name: Owner_Department_ID
Is Nullable: YES
Data Type: STRING
Description: Department ID responsible for overseeing the appeal process.

Created Timestamp

Column Name: Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the log entry was created.

Last Modified Timestamp

Column Name: Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the log entry was last modified.

Outcomes

Column Name: Outcomes
Is Nullable: YES
Data Type: ARRAY
Description: An array of outcomes related to the appeal.

Outcomes Key

Column Name: Outcomes.Key
Is Nullable: YES
Data Type: INT64
Description: Unique identifier for each outcome associated with the appeal.

Appeal Status

Column Name: Outcomes.Appeal_Status
Is Nullable: YES
Data Type: STRING
Description: The status of the appeal at the time the outcome was recorded.

Appeal Status Description

Column Name: Outcomes.Appeal_Status_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the appeal status at the time of recording.

Outcome Key

Column Name: Outcomes.Outcome_Key
Is Nullable: YES
Data Type: INT64
Description: Key linking to a specific appeal outcome.

Outcome ID

Column Name: Outcomes.Outcome_ID
Is Nullable: YES
Data Type: STRING
Description: Identifier for the specific outcome of the appeal.

Created Timestamp

Column Name: Outcomes.Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the outcome was recorded in the log.

Last Modified Timestamp

Column Name: Outcomes.Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the outcome record was last updated.

Custom Fields

Column Name: Custom_Fields
Is Nullable: YES
Data Type: ARRAY
Description: An array of custom fields associated with the appeal for additional data tracking.

Custom Fields Key

Column Name: Custom_Fields.Key
Is Nullable: YES
Data Type: INT64
Description: Unique identifier for each custom field entry.

Custom Fields ID

Column Name: Custom_Fields.ID
Is Nullable: YES
Data Type: STRING
Description: Identifier for the custom field.

Custom Fields Title

Column Name: Custom_Fields.Title
Is Nullable: YES
Data Type: STRING
Description: Title of the custom field, indicating its content.

Custom Fields Use

Column Name: Custom_Fields.Use
Is Nullable: YES
Data Type: STRING
Description: Intended use or application of the custom field.

Custom Fields Values

Column Name: Custom_Fields.Values
Is Nullable: YES
Data Type: ARRAY
Description: An array of structures containing detailed information about the list of values stored within the custom field.

Select Key

Column Name: Custom_Fields.Values.Select_Key
Is Nullable: YES
Data Type: INT64
Description: Unique key used to select the value.

Value

Column Name: Custom_Fields.Values.Value
Is Nullable: YES
Data Type: STRING
Description: Specific value stored within the custom field.

Value Description

Column Name: Custom_Fields.Values.Value_Description
Is Nullable: YES
Data Type: STRING
Description: Description of the value, providing additional details.

Created Timestamp

Column Name: Custom_Fields.Values.Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the value was created.

Last Modified Timestamp

Column Name: Custom_Fields.Values.Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp when the value was last updated.

Created Timestamp

Column Name: Custom_Fields.Created_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp indicating when the custom field was created.

Last Modified Timestamp

Column Name: Custom_Fields.Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp indicating when the custom field was last updated.

Appeal Log

Record Last Modified Timestamp

Column Name: Record_Last_Modified_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp of the last modification to the appeal log record.

Transaction User ID

Column Name: Transaction_User_ID
Is Nullable: YES
Data Type: STRING
Description: User ID associated with the last transaction on the appeal log.

Deleted Indicator

Column Name: Deleted_Indicator
Is Nullable: YES
Data Type: BOOL
Description: Boolean flag indicating if the log entry has been marked as deleted.

Type

Column Name: Type
Is Nullable: YES
Data Type: STRING
Description: Type of log entry, providing context for the record.

Tenant

Column Name: Tenant
Is Nullable: YES
Data Type: STRING
Description: Identifier of the tenant or domain within which the appeal is managed.

Active Version

Column Name: Active_Version
Is Nullable: YES
Data Type: BOOL
Description: Boolean flag indicating if the log entry is the active version of the appeal record.

Date

Column Name: Date
Is Nullable: YES
Data Type: DATE
Description: Date associated with the log entry.

Sequence

Column Name: Sequence
Is Nullable: YES
Data Type: INT64
Description: Sequence number of the log entry, indicating its order.

Source System Name

Column Name: Source_System_Name
Is Nullable: YES
Data Type: STRING
Description: Name of the source system where the log entry originated.

Source Timestamp

Column Name: Source_Timestamp
Is Nullable: YES
Data Type: TIMESTAMP
Description: Timestamp from the source system for the log entry.

Source File Name

Column Name: Source_File_Name
Is Nullable: YES
Data Type: STRING
Description: Name of the file in the source system that corresponds to the log entry.

Schema Version

Column Name: Schema_Version
Is Nullable: YES
Data Type: FLOAT64
Description: Version of the schema used for the appeal log.

Member Partition

Column Name: Member_Partition
Is Nullable: YES
Data Type: INT64
Description: Partition key used for segmenting data by member.

Action

Column Name: Action
Is Nullable: YES
Data Type: STRING
Description: Action taken in the log entry, such as creating, updating, or deleting an appeal record.

Transaction Actor Type

Column Name: Transaction_Actor_Type
Is Nullable: YES
Data Type: STRING
Description: Type of the actor performing the transaction.

Table Content

Appeal Raw

Member ID

Column Name: Member_ID
Is Nullable: YES
Data Type: STRING
Description: The identifier for the member associated with the appeal, linking the appeal process to the specific member's case.

Request Key

Column Name: Request_Key
Is Nullable: YES
Data Type: INT64
Description: A unique identifier for the request related to the appeal, providing a link to the specific service or action being appealed.

Request ID

Column Name: Request_ID
Is Nullable: YES
Data Type: STRING
Description: A string identifier for the request, often used as a human-readable reference.

Appeal Dataset Specification

Appeal_raw Fields

Appeal_Key

Type: INT64
Required: YES
Description: The unique key for the appeal record, serving as a primary identifier within the system.

Appeal_ID

Type: STRING
Required: YES
Description: A string identifier for the appeal, used for tracking and reference in communications or documentation.

Void_Indicator

Type: BOOL
Required: YES
Description: A boolean flag indicating whether the appeal record has been voided or invalidated in the system.

Process_Type

Type: STRING
Required: YES
Description: Identifies the type of appeal process being followed, categorizing the appeal based on the procedural rules or steps involved.

Process_Type_Description

Type: STRING
Required: YES
Description: A detailed description of the process type, explaining the procedural context of the appeal.

Created_User_ID

Type: STRING
Required: YES
Description: The user ID of the individual who created the appeal record, for audit and tracking purposes.

Last_Modified_User_ID

Type: STRING
Required: YES
Description: The user ID of the individual who last modified the appeal record, useful for audit trails and accountability.

Created_Timestamp

Type: TIMESTAMP
Required: YES
Description: The timestamp when the appeal record was first created, indicating the initiation time of the appeal process.

Last_Modified_Timestamp

Type: TIMESTAMP
Required: YES
Description: The timestamp of the most recent modification to the appeal record, showing the latest update or change.

Levels

Type: ARRAY
Required: NO
Description: An array of structures containing detailed information about each level of the appeal process, including IDs, descriptions, timestamps, and other relevant data.

Levels Fields

Key

Type: INT64
Required: YES
Description: Unique identifier for the record.

Level_ID

Type: STRING
Required: YES
Description: Identifier for the level of appeal.

Code

Type: STRING
Required: YES
Description: Code for the appeal status.

Description

Type: STRING
Required: YES
Description: Description of the appeal.

Type

Type: STRING
Required: YES
Description: Type of the appeal.

Type_Description

Type: STRING
Required: YES
Description: Description of the appeal type.

Notify_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the appeal was notified.

Due_Timestamp

Type: TIMESTAMP
Required: YES
Description: Expected completion date for the appeal.

Resolution_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the appeal was resolved.

All_Documents_Received_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when all required documents were received.

Reason

Type: STRING
Required: YES
Description: Reason for the appeal.

Reason_Description

Type: STRING
Required: YES
Description: Detailed description of the reason for the appeal.

Void_Indicator

Type: BOOL
Required: YES
Description: Indicator if the record is voided.

Source

Type: STRING
Required: YES
Description: Source of the appeal.

Source_Description

Type: STRING
Required: YES
Description: Description of the appeal source.

Requestor

Type: STRING
Required: YES
Description: ID or name of the person who filed the appeal.

Requestor_Description

Type: STRING
Required: YES
Description: Description of the requestor.

Reviewer

Type: STRING
Required: YES
Description: ID or name of the person reviewing the appeal.

Reviewer_Description

Type: STRING
Required: YES
Description: Description of the reviewer.

Due_Date_Reason

Type: STRING
Required: YES
Description: Reason for setting the appeal deadline.

Due_Date_Reason_Description

Type: STRING
Required: YES
Description: Description of the reason for setting the deadline.

Created_User_ID

Type: STRING
Required: YES
Description: ID of the user who created the record.

Last_Modified_User_ID

Type: STRING
Required: YES
Description: ID of the user who last modified the record.

Owner_User_ID

Type: STRING
Required: YES
Description: ID of the user responsible for the record.

Owner_Department_ID

Type: STRING
Required: YES
Description: ID of the department responsible for the record.

Created_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the record was created.

Last_Modified_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the record was last modified.

Levels.Outcomes Fields

Key

Type: INT64
Required: YES
Description: Unique identifier for the outcome.

Appeal_Status

Type: STRING
Required: YES
Description: Status of the appeal at the time of recording.

Appeal_Status_Description

Type: STRING
Required: YES
Description: Description of the appeal status.

Outcome_Key

Type: INT64
Required: YES
Description: Key associated with a specific outcome.

Outcome_ID

Type: STRING
Required: YES
Description: Identifier for the outcome of the appeal.

Created_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the outcome record was created.

Last_Modified_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the outcome record was last modified.

Levels.Custom_Fields Fields

Key

Type: INT64
Required: YES
Description: Unique identifier for the custom field.

ID

Type: STRING
Required: YES
Description: Identifier of the custom field.

Title

Type: STRING
Required: YES
Description: Title of the custom field.

Use

Type: STRING
Required: YES
Description: Use of the custom field.

Values

Type: ARRAY
Required: YES
Description: An array of structures containing detailed information about values of the custom fields.

Levels.Custom_Fields.Values Fields

Select_Key

Type: INT64
Required: YES
Description: Unique key for selecting.

Value

Type: STRING
Required: YES
Description: Value of the field.

Value_Description

Type: STRING
Required: YES
Description: Description of the field value.

Created_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the field value was created.

Last_Modified_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp when the field value was last modified.

Record_Last_Modified_Timestamp

Type: TIMESTAMP
Required: YES
Description: Timestamp of the last modification to the appeal record.

Appeal Dataset Specification

Appeal_raw Fields

Timestamp

Required: YES
Type: TIMESTAMP
Description: Timestamp indicating the last modification to the appeal record, providing a point of reference for the most recent update.

Transaction_User_ID

Required: YES
Type: STRING
Description: The user ID associated with the last transaction on the appeal record, identifying who made the most recent change.

Deleted_Indicator

Required: YES
Type: BOOL
Description: A boolean flag indicating whether the appeal record has been marked as deleted, providing a status of the record’s lifecycle.

Type

Required: YES
Type: STRING
Description: The type of the record in the appeal process, categorizing the nature or context of the appeal.

Tenant

Required: YES
Type: STRING
Description: Identifies the tenant or organizational unit within which the appeal exists, useful for multi-tenant systems.

Active_Version

Required: YES
Type: BOOL
Description: A boolean value indicating whether this record is the active version of the appeal, signifying its current use or relevance.

Date

Required: YES
Type: DATE
Description: The date associated with the record, often reflecting the date of the appeal log entry or related action.

Sequence

Required: YES
Type: INT64
Description: A sequence number for the record, which might indicate its order in a series or process flow.

Source_System_Name

Required: YES
Type: STRING
Description: The name of the system from which the appeal data originated, helping to trace the source of information.

Source_Timestamp

Required: YES
Type: TIMESTAMP
Description: The timestamp when the data was entered or captured in the source system, providing a temporal context.

Source_File_Name

Required: YES
Type: STRING
Description: The name of the file from the source system that includes the appeal record, useful for data lineage and tracking.

Schema_Version

Required: YES
Type: FLOAT64
Description: The version of the data schema used for the appeal record, indicating the structure or format of the data.

Member_Partition

Required: YES
Type: INT64
Description: An identifier used for partitioning data by member, which can help in organizing and optimizing data storage and access.

Transaction_Actor_Type

Required: YES
Type: STRING
Description: Type of the actor performing the transaction.

Request Dataset Specification

Provider Dataset Specification

Letter Dataset Specification

Questionnaire Dataset Specification

Program Dataset Specification