The Oracle Argus Analytics Presentation Catalog displays columns that you can use to create requests.
See Also:
Chapter 3, "Working with Reports"
Table B-1 describes the Dimensions displayed in the Oracle Argus Analytics Presentation Catalog. For each column, it shows the name of the Column, its description, and the Dimension containing the column. The table is sorted by Dimension, and by Column within Dimension. This corresponds to how the Dimension columns are organized in the Presentation Catalog.
Please note that some columns have been de-normalized, so that they appear in more than one Dimension. This de-normalization speeds query execution by eliminating the need for a join to that column in another Dimension. It also supports the ability to constrain columns in a Prompt, so that when you select a value in one column, the other columns are constrained to those related to your selection. When you select a de-normalized column from a Dimension, your results will be limited to the grain of the Dimension.
Table B-1 Presentation Catalog - Dimensions
Dimension | Column | Description |
---|---|---|
Case Processing Site |
Case Processing Site |
Case Processing Site that the user belongs to. |
Enterprise |
Enterprise ID |
CRO Enterprise (Client) Short Name |
Enterprise |
Last Refresh Date |
Program_run_dt from w_control_s/pva_control_s Table |
Enterprise |
ETL Incremental Status |
Value 'Running' if source_extraction_end_dt < source_extraction_start_dt from w_control_s/pva_control_s else has a value 'Completed' Table |
Enterprise |
Enterprise Name |
Enterprise Name |
Period |
Year |
Per_name_year From W_day_d Table |
Period |
Quarter |
Per_name_qtr From W_day_d Table |
Period |
Month |
Per_name_month From W_day_d Table |
Period |
Week |
Per_name_week From W_day_d Table |
Period |
Day |
Calendar_date From W_day_d Table |
Product |
Product Group |
Therapeutic Alignment Of User Groups By Product Indication (For Example, Oncology Group, Gynecology Group Etc) |
Product |
Product Family |
Product Family - It is used to group Like products that share Datasheets and Primary Ingredients. |
Product |
PSUR Group |
PSUR Group Name |
Product |
Product Name |
Product Name |
Product |
Product Formulation |
Formulation of the Product |
Product |
Product Concentration |
Concentration/Strength of the Product |
Study |
Study ID |
Study ID |
Study |
Project ID |
Project ID for Study |
Product |
Generic Name |
Generic Name of the Product |
License |
Product Name |
Product Name |
License |
Product Formulation |
Formulation of the Product |
License |
Product Concentration |
Concentration/Strength of the Product |
License |
License ID |
Product License Number |
License |
License Type |
Product License Type (like Marketed Drug, Investigational Vaccine etc.) |
License |
License Name |
Concatenated value of license trade name, license authorization country and license number displayed as - trade name || ' (' || country|| ') ' || license number |
License |
License MAH |
Manufacturer of the licensed product |
License |
License Country |
Country of license award |
License |
CTPR Group |
CTPR Group Name |
License |
Trade Name |
Trade Name |
Report Destination |
Report Destination Country |
Country of destination to which the report is submitted |
Report Destination |
Report Destination |
Destination to which the report is submitted |
This section lists the Facts and Sources displayed in the Oracle Argus Analytics Presentation Catalog.
For each Fact, it shows the name of the Column, its Description, and the Source Table/Column that contains the Column.
This corresponds to how the Fact columns are organized in the Presentation Catalog, within the Fact folder.
Table B-2 Presentation Catalog - Facts
Fact | Column | Description | Source Table/Column | Comments/Details |
---|---|---|---|---|
Case |
Case ID |
Case Identification (same as Manufacturer Code Number [MCN]) |
case_master.case_num |
|
Case |
Case Internal ID |
Internal Case ID Generated |
case_master.case_id |
|
Case |
Enterprise ID |
CRO Enterprise (Client) Short Name |
cfg_enterprise.enterprise_abbrv |
|
Case |
Initial / Follow-Up |
Initial or Follow-up entry of the Case |
N/A |
If Version = 1 then Initial Else Follow-up |
Case |
Case Deletion Indicator |
A Flag to Identify that case has been "Deleted" or not |
nvl(case_master.state_id, 0) = 1 or case_master.deleted is not null |
|
Case |
Case Creator ID |
ID of Case Author (who created the case) |
case_master.user_id |
|
Case |
Case Creator Name |
Case Author (who created the case) |
cfg_users.user_fullname where cfg_users.user_id = case_master.user_id |
|
Case |
Case Processing Site |
Site (for ex., drug safety case processing location) |
case_master.site_id |
|
Case |
Country Of Incidence |
Country where the adverse event occurred |
case_master.country_id |
|
Case |
Case Type |
Case Report Type (for ex., Spontaneous/Clinical/Literature etc.) |
case_master.Report_type |
|
Case |
Compliance Classification |
7-day Case, 15-day Case, Non-Expedited Case |
lm_case_classification.description |
where lm_case_classification.description has values in (7-day Case, 15-day Case, Non-Expedited Case) |
Case |
S/R/U Classification |
SAE Case, SAR Case, SUSAR Case |
lm_case_classification.description |
where lm_case_classification.description has values in (SAE Case, SAR Case, SUSAR Case) |
Case |
F/LT Classification |
Fatal / Life-Threatening Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Fatal / Life-Threatening Case) |
Case |
Pregnancy Classification |
Pregnancy Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Pregnancy Case) |
Case |
Source Classification |
Authority Case, Consumer Case, Literature Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Authority Case, Consumer Case, Literature Case) |
Case |
Confirmation Classification |
Medically Confirmed Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Medically Confirmed Case) |
Case |
Case Classification Group 7 |
Case Classification Group 7 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 7) |
Case |
Case Classification Group 8 |
Case Classification Group 8 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 8) |
Case |
Case Classification Group 9 |
Case Classification Group 9 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 9) |
Case |
Case Classification Group 10 |
Case Classification Group 10 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 10) |
Case |
Product Type |
Drug, Device and Vaccine |
case_product.views_available |
|
Case |
Study ID |
Study ID |
case_study.study_num |
|
Case |
Center ID |
Center ID for the Study in the case |
lm_centers.center_no where case_study.center_id = lm_centers.center_id |
|
Case |
Case Seriousness |
Case Serious or Non-Serious (Y/N) |
case_assess.seriousness |
|
Case |
Case Listedness |
Unlisted/Unexpected/listed/expected |
case_assess.listedness |
|
Case |
Case Causality |
Adverse event caused by the product (related/not related) |
case_assess.agent_suspect |
|
Case |
Case Outcome |
Case Level Outcome using the ICH guidelines eg "Recovered" |
case_assess.outcome |
|
Case |
Gender |
Gender of the Patient |
case_pat_info.gender_id |
|
Case |
Pregnant |
Patient pregnant (Yes/No) |
case_pat_info.pat_stat_preg |
|
Case |
Age Group |
Patient Age Group |
case_pat_info.age_group_id |
|
Case |
Ethnicity |
Ethnicity of the Patient |
case_pat_info.ethnicity_id |
|
Case |
Lactation |
Patient breastfeeding |
case_pregnancy.breastfeeding |
|
Case |
Primary Reporter Type |
Type of the first/primary reporter (for ex., Physician, Consumer etc) |
case_reporters.reporter_type where primary_contact = 1 |
|
Case |
Primary Suspect Product |
Primary Suspect Product for a case |
case_product.product_name where case_product.first_sus_prod = 1 |
|
Case |
HCP |
Health Care Professional (ex., physician, nurse, pharmacist) flag. Primary Reporter of the case is HCP or not |
case_reporters.hcp_flag where primary_contact = 1 |
|
Case |
SUSAR |
Suspected Unexpected Serious Adverse Reaction Flag(0 / 1) |
case_master.susar |
|
Case |
Case Owner ID |
ID of user who is responsible for the case throughout its lifetime |
case_master.worklist_owner_id |
|
Case |
Case Owner Name |
User who is responsible for the case throughout its lifetime |
cfg_users.user_fullname where cfg_users.user_id = case_master.worklist_owner_id |
|
Case |
Initial Receipt Date |
Date on which initial case information was received |
case_master.init_rept_date |
|
Case |
Safety Receipt Date |
Case receipt date at Central Safety |
case_master.safety_date |
|
Case |
Clock Start Date |
Case Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case |
Case Deletion Timestamp |
Date of the case deletion |
nvl(case_master.deleted, case_master.last_update_time) where nvl(case_master.state_id, 0) = 1 or case_master.deleted is not null |
|
Case |
Lock Timestamp |
Case is ready for submission. Lock date of the case. |
case_master.date_locked |
|
Case |
Lock Target Days |
Number of target days for processing a case which is Number of days from Clock Start Date to Lock Timestamp |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
|
Case |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
Clock Start Date + Lock Target Days |
Case |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Case |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case post it being Globally Locked. Will be null if either global lock or local lock is null. |
CASE_ROUTING.ROUTE_DATE |
|
Case |
CRO Project Code |
If clinical case then Study Configuration -> Other ID else Product Configuration -> Company Drug Code for the Primary Suspect Product |
LM_REPORT_TYPE.INCL_TRIAL LM_STUDIES.OTHER_ID LM_PRODUCTS.DRUG_CODE |
If LM_REPORT_TYPE.INCL_TRIAL = 1 then LM_STUDIES.OTHER_ID else LM_PRODUCTS.DRUG_CODE for Primary Suspect Product |
Case |
Case Deletion User ID |
ID of user who deleted the case |
case_master.last_update_user_id |
|
Case |
Case Deletion User Name |
User who deleted the case |
cfg_users.user_fullname where cfg_users.user_id = case_master.last_update_user_id |
|
Case |
Case Deletion Reason |
Reason for deleting the case |
case_justifications.j_text where field_id = 2110018 |
|
Case |
Primary Event PT |
Primary event (Preferred Term) for the case |
case_event.pref_term for min sort_id for min seq_num |
|
Case |
Duration Global to Local Lock Per Case |
Calculates the time between the latest local case lock and the last global case lock that occurred before that local case lock. Will be null if either global lock or local lock are null. |
N/A |
|
Case |
Primary Indication |
Primary indication (Preferred Term) for the primary suspect product |
case_prod_indications.ind_pref_term for min seq_num |
|
Case |
Duration Case Modified Per Case |
Sum of Edit Duration Per Case |
N/A |
sum(Duration Case Modified Per User/State) Group by Case ID |
Case |
Duration Case Unmodified Per Case |
Sum of Edit Duration Per Case |
N/A |
sum(Duration Case Unmodified Per User/State) Group by Case ID |
Case |
Duration Case Read Per Case |
Sum of Read Duration Per Case |
N/A |
sum(Duration Case Read Per User/State) Group by Case ID |
Case |
Avg Duration Case Modified Per Case |
Average time spent in actual work done on one case across all users and all workflow states |
N/A |
sum(Duration Case Modified Per Case) / (total number of records in Case History) |
Case |
Avg Duration Case Unmodified Per Case |
Average time spent in work done without any modification on one case across all users and all workflow states |
N/A |
sum(Duration Case Unmodified Per Case) / (total number of records in Case History) |
Case |
Avg Duration Case Read Per Case |
Average time spent in work done in read-only mode on one case across all users and all workflow states |
N/A |
sum(Duration Case Read Per Case) / (total number of distinct records in Case History) |
Case |
Avg # Suspect Products Per Case |
Average number of suspect products in a case |
N/A |
sum(# Products Per Case) / # Cases Locked Once |
Case |
Avg # Events Per Case |
Average number of events in a case |
N/A |
sum(# Events Per Case) / # Cases Locked Once |
Case |
Avg # F/U Versions Per Case |
Average number of follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
sum(# F/U Versions Per Case) / # Cases Locked Once |
Case |
Avg # Sig F/U Versions Per Case |
Average Significant Follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
sum(# Sig F/U Versions Per Case) / # Cases Locked Once |
Case |
Avg # F/U Received Per Case |
Average number of follow-up received per case (It is the case f/u [case_followup]) |
N/A |
sum(# F/U Received Per Case) / # Cases Locked Once |
Case |
Avg # Sig F/U Received Per Case |
Average Significant Follow-up received per case (It is the case f/u [case_followup]) |
N/A |
sum(# Sig F/U Received Per Case) / # Cases Locked Once |
Case |
Min Duration Case Modified Per Case |
Minimum time spent in actual work done on one case across all users and all workflow states |
N/A |
min(Duration Case Modified Per Case) |
Case |
Min Duration Case Unmodified Per Case |
Minimum time spent in work done without any modification on one case across all users and all workflow states |
N/A |
min(Duration Case Unmodified Per Case) |
Case |
Min Duration Case Read Per Case |
Minimum time spent in work done in read-only mode on one case across all users and all workflow states |
N/A |
min(Duration Case Read Per Case) |
Case |
Min # Suspect Products Per Case |
Minimum number of suspect products in a case |
N/A |
min(# Products Per Case) |
Case |
Min # Events Per Case |
Minimum number of events in a case |
N/A |
min(# Events Per Case) |
Case |
Min # F/U Versions Per Case |
Minimum number of follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
min(# F/U Versions Per Case) |
Case |
Min # Sig F/U Versions Per Case |
Minimum Significant Follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
min(# Sig F/U Versions Per Case) |
Case |
Min # F/U Received Per Case |
Minimum number of follow-up received per case (It is the case f/u [case_followup]) |
N/A |
min(# F/U Received Per Case) |
Case |
Min # Sig F/U Received Per Case |
Minimum Significant Follow-up received per case (It is the case f/u [case_followup]) |
N/A |
min(# Sig F/U Received Per Case) |
Case |
Max Duration Case Modified Per Case |
Maximum time spent in actual work done on one case across all users and all workflow states |
N/A |
max(Duration Case Modified Per Case) |
Case |
Max Duration Case Unmodified Per Case |
Maximum time spent in work done without any modification on one case across all users and all workflow states |
N/A |
max(Duration Case Unmodified Per Case) |
Case |
Max Duration Case Read Per Case |
Maximum time spent in work done in read-only mode on one case across all users and all workflow states |
N/A |
max(Duration Case Read Per Case) |
Case |
Max # Suspect Products Per Case |
Maximum number of suspect products in a case |
N/A |
max(# Products Per Case) |
Case |
Max # Events Per Case |
Maximum number of events in a case |
N/A |
max(# Events Per Case) |
Case |
Max # F/U Versions Per Case |
Maximum number of follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
max(# F/U Versions Per Case) |
Case |
Max # Sig F/U Versions Per Case |
Maximum Significant Follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
max(# Sig F/U Versions Per Case) |
Case |
Max # F/U Received Per Case |
Maximum number of follow-up received per case (It is the case f/u [case_followup]) |
N/A |
max(# F/U Received Per Case) |
Case |
Max # Sig F/U Received Per Case |
Maximum Significant Follow-up received per case (It is the case f/u [case_followup]) |
N/A |
max(# Sig F/U Received Per Case) |
Case |
# Cases Locked Once |
Count of Cases that have been locked atleast once |
count(distinct case_routing.case_id) where case_routing.comment_text like 'case locked%' |
|
Case |
# Suspect Products Per Case |
Total number of suspect products in a case |
count(1) where drug_type = 1 group by case_product.case_id |
|
Case |
# Events Per Case |
Total number of events in a case |
count(1) group by case_event.case_id |
|
Case |
# F/U Versions Per Case |
Total number of follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
count(number of records from Case Version History where Version Type in (Significant, Non-Significant)) group by Case ID |
Case |
# Sig F/U Versions Per Case |
Total Significant Follow-up versions processed per case (number of significant f/u locks. It is not the case f/u [case_followup]) |
N/A |
count(number of records from Case Version History where Version Type = Significant) group by Case ID |
Case |
# F/U Received Per Case |
Total number of follow-up received per case (It is the case f/u [case_followup]) |
N/A |
(total follow-up records in case_followup table for locked cases) group by Case ID |
Case |
# Sig F/U Received Per Case |
Total Significant Follow-up received per case (It is the case f/u [case_followup]) |
N/A |
(total significant follow-up records in case_followup table for locked cases) |
Case Version |
Version ID |
Case Version (1,2,3...) |
A new case version is created whenever a case is unlocked or reopened. Version number starting with 1 and incremented by 1 for every new version. |
|
Case Version |
Version Type |
Significant, Non-Significant, No Followup - Significant/Non-Significant (Is this follow-up information considered significant, based on company specific policies? If this follow-up information is marked as significant, the regulatory report algorithm will re-run and will calculate the due date based on the most recent significant follow-up date. 1 = Yes, 0 = No) |
N/A |
No Followup: If there is no followup between two Version Lock DateTime Significant: If there is atleast 1 significant followup between two Version Lock DateTime Non-Significant: If there is followup but no significant followup between two Version Lock DateTime |
Case Version |
Version Receipt Date |
Date on which initial/follow-up information was received |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
|
Case Version |
Safety Receipt Date |
Case Version receipt date at Central Safety |
case_followup.safety_date (Case_Master.safety_date for Initial case version) |
|
Case Version |
Clock Start Date |
Case Version Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case Version |
Version Creation Timestamp |
The timestamp when the case version was created (Case Book-in timestamp for initial case version or routing timestamp when the case was unlocked/unarchived for other case versions) |
N/A |
For initial case version, case_master.create_time and for other case versions it is case_routing.route_date where lower(case_routing.comment_text) like 'case unlocked%' or case_routing.to_state_id = 2 |
Case Version |
Lock Timestamp |
Case is ready for submission. First lock date of the case version. |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
|
Case Version |
Lock Target Days |
Number of target days for processing a case version which is Number of days from Clock Start Date to Lock Timestamp |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
Case Version |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
Clock Start Date + Lock Target Days |
Case Version |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Case Version |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case Version post it being Globally Locked. Will be null if either global lock or local lock are null within the specific case version. |
CASE_ROUTING.ROUTE_DATE |
|
Case Version |
Duration Case Modified Per Version |
Sum of Edit Duration Per Case Version |
N/A |
sum(Duration Case Modified Per User/State) Group by Case ID, Version ID |
Case Version |
Duration Case Unmodified Per Version |
Sum of Edit Duration Per Case Version |
N/A |
sum(Duration Case Unmodified Per User/State) Group by Case ID, Version ID |
Case Version |
Duration Case Read Per Version |
Sum of Read Duration Per Case Version |
N/A |
sum(Duration Case Read Per User/State) Group by Case ID, Version ID |
Case Version |
Duration Version-Receipt-To-Safety- Receipt |
(How Long Does It Take Before A Case Version Reaches The Safety Group?) The Difference Between The Case Version Receipt Date And The Case Version Safety Receipt Date. |
days between (Safety Receipt Date, Version Receipt Date) |
|
Case Version |
Duration Clock-Start-To-Lock |
Interval Between Case Version Clock Start Date And Case Version Lock Datetime |
days between (Clock Start Date, Lock Timestamp) |
|
Case Version |
Duration Version-Creation-To-Lock |
Interval Between Case Version Create DateTime And Case Version Lock Datetime |
days between (Version Creation Timestamp, Lock Timestamp) |
|
Case Version |
Duration Global to Local Lock Per Case Version Sum |
Calculates the time between the latest local case lock in the specific version and the last global case lock that occurred before that local case lock in the same version. Will be null if either global lock or local lock are null within the specific case version |
||
Case Version |
Avg Duration Clock-Start-To-Lock |
Average Interval between Case Version Clock Start Date and Case Version Lock DateTime |
sum(Duration Clock-Start-To-Lock)/# Versions Locked Once |
|
Case Version |
Avg Duration Version-Creation-To-Lock |
Average Interval Between Case Version Create DateTime And Case Version Lock DateTime |
sum(Duration Version-Creation-To-Lock)/# Versions Locked Once |
|
Case Version |
Avg Duration Case Modified Per Version |
Average time spent in actual work done on one case version across all users and all workflow states |
N/A |
sum(Duration Case Modified Per Version) / (total number of records in Case Version History) |
Case Version |
Avg Duration Case Unmodified Per Version |
Average time spent in work done without any modification on one case version across all users and all workflow states |
N/A |
sum(Duration Case Unmodified Per Version) / (total number of records in Case Version History) |
Case Version |
Avg Duration Case Read Per Version |
Average time spent in work done in read-only mode on one case version across all users and all workflow states |
N/A |
sum(Duration Case Read Per Version) / (total number of distinct records in Case Version History) |
Case Version |
Min Duration Clock-Start-To-Lock |
Minimum Interval Between Case Version Clock Start Date And Case Version Lock DateTime |
min(Duration Clock-Start-To-Lock) |
|
Case Version |
Min Duration Version-Creation-To-Lock |
Minimum Interval Between Case Version Create DateTime And Case Version Lock DateTime |
min(Duration Version-Creation-To-Lock) |
|
Case Version |
Min Duration Case Modified Per Version |
Minimum time spent in actual work done on one case version across all users and all workflow states |
N/A |
min(Duration Case Modified Per Version) |
Case Version |
Min Duration Case Unmodified Per Version |
Minimum time spent in work done without any modification on one case version across all users and all workflow states |
N/A |
min(Duration Case Unmodified Per Version) |
Case Version |
Min Duration Case Read Per Version |
Minimum time spent in work done in read-only mode on one case version across all users and all workflow states |
N/A |
min(Duration Case Read Per Version) |
Case Version |
Max Duration Clock-Start-To-Lock |
Maximum Interval Between Case Version Clock Start Date And Case Version Lock DateTime |
max(Duration Clock-Start-To-Lock) |
|
Case Version |
Max Duration Version-Creation-To-Lock |
Maximum Interval Between Case Version Create DateTime And Case Version Lock DateTime |
max(Duration Version-Creation-To-Lock) |
|
Case Version |
Max Duration Case Modified Per Version |
Maximum time spent in actual work done on one case version across all users and all workflow states |
N/A |
max(Duration Case Modified Per Version) |
Case Version |
Max Duration Case Unmodified Per Version |
Maximum time spent in work done without any modification on one case version across all users and all workflow states |
N/A |
max(Duration Case Unmodified Per Version) |
Case Version |
Max Duration Case Read Per Version |
Maximum time spent in work done in read-only mode on one case version across all users and all workflow states |
N/A |
max(Duration Case Read Per Version) |
Case Version |
#Versions Locked Once |
Number Of Case Versions Received (Including All Versions) |
Count (Number of records from Case Version History) |
|
Case Version |
#Sig Versions Locked Once |
Number Of Case Versions Received (Including Significant Versions Only) |
Count (Number of records from Case Version History) where Version Type = Significant |
|
Case Version |
#Versions Locked On-Time |
Number Of Case Versions Processed Within Target Days |
Lock Timestamp - Clock Start Date <= Lock Target Days |
|
Case Version |
%Versions Locked On-Time |
Percentage Of Case Versions Processed On Time |
(# Versions Locked On-Time/# Versions Locked Once) * 100 |
|
Case Version |
#Versions Locked Late |
Number Of Case Versions Processed Over Target Days |
Lock Timestamp - Clock Start Date > Lock Target Days |
|
Case Version |
%Versions Locked Late |
Percentage Of Case Versions Not Processed Within Target Days |
(# Versions Locked Late/# Versions Locked Once) * 100 |
|
Case Routing |
Version ID |
Case Version (1,2,3...) |
A new case version is created whenever a case is unlocked or reopened. Version number starting with 1 and incremented by 1 for every new version. |
|
Case Routing |
Version Type |
Significant, Non-Significant, No Followup - Significant/Non-Significant (Is this follow-up information considered significant, based on company specific policies? If this follow-up information is marked as significant, the regulatory report algorithm will re-run and will calculate the due date based on the most recent significant follow-up date. 1 = Yes, 0 = No) |
N/A |
No Followup: If there is no followup between two Version Lock DateTime Significant: If there is atleast 1 significant followup between two Version Lock DateTime Non-Significant: If there is followup but no significant followup between two Version Lock DateTime |
Case Routing |
Version Receipt Date |
Date on which initial/follow-up information was received |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
|
Case Routing |
Safety Receipt Date |
Case Version receipt date at Central Safety |
case_followup.safety_date (Case_Master.safety_date for Initial case version) |
|
Case Routing |
Clock Start Date |
Case Version Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case Routing |
Version Creation Timestamp |
The timestamp when the case version was created (Case Book-in timestamp for initial case version or routing timestamp when the case was unlocked/unarchived for other case versions) |
case_master.create_time case_routing.route_date |
For initial case version, case_master.create_time and for other case versions it is case_routing.route_date where lower(case_routing.comment_text) like 'case unlocked%' or case_routing.to_state_id = 2 |
Case Routing |
Lock Timestamp |
Case is ready for submission. First lock date of the case version. |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
|
Case Routing |
Lock Target Days |
Number of target days for processing a case version which is Number of days from Clock Start Date to Lock Timestamp |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
|
Case Routing |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
Clock Start Date + Lock Target Days |
Case Routing |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Case Routing |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case Version post it being Globally Locked. Will be null if either global lock or local lock are null within the specific case version. |
CASE_ROUTING.ROUTE_DATE |
|
Case Routing |
Routing User Group |
User Group which worked on the workflow step |
cfg_workflow_rules.group_id where cfg_workflow_rules.seq_num = case_routing.workflow_seq_num |
|
Case Routing |
Routing Justification |
Case Routing Justification |
case_routing.justification_id |
|
Case Routing |
Routing ID |
Workflow Sequence Number |
case_routing.seq_num |
|
Case Routing |
State Routed From |
Workflow state from which case was promoted to next workflow state |
cfg_workflow_states.state_name where case_routing.fr_state_id = cfg_workflow_states.state_id |
|
Case Routing |
State Routed To |
Workflow state to which case was promoted from previous workflow state |
cfg_workflow_states.state_name where case_routing.to_state_id = cfg_workflow_states.state_id |
|
Case Routing |
Routing Timestamp From State |
The timestamp on which case was routed From Workflow State |
case_routing.route_date |
|
Case Routing |
Routing Timestamp To State |
The timestamp on which case was routed To Workflow State |
case_routing.route_date |
|
Case Routing |
Routing User ID |
ID of the user who last worked on the Workflow step |
case_routing.user_id |
|
Case Routing |
Routing User Name |
The User who last worked on the Workflow step |
cfg_users.user_fullname where cfg_users.user_id = case_routing.user_id |
|
Case Routing |
Follow-Up ID |
Follow-up number of the case to which this routing record belongs |
Followup # as appear on Argus Case Form against each followup starting with 1 and incremented by 1 for every new folllowup. (case_routing.followup_num joins with case_followup.seq_num) |
|
Case Routing |
Comments |
Routing comments |
case_routing.comment_text |
|
Case Routing |
Pseudo State |
|||
Case Routing |
Duration Case Idle |
Duration from case version routing timestamp to minimum open timestamp greater than the current routing timestamp and less than next routing timestamp. If case version is not open after routing then duration shall be the complete duration the case was in that particular workflow state (from workflow state date - to workflow state date). Duration from case workflow state route date to minimum/first open timestamp for same workflow state |
N/A |
min(Case Open Timestamp) - Routing Timestamp To State |
Case Routing |
Avg Duration Case Idle Per Case |
Average idle duration per case |
N/A |
sum(Idle Duration Per Version Routing) / (total number of distinct Case ID) |
Case Routing |
Avg Duration Case Idle Per Version |
Average idle duration per case version |
N/A |
sum(Idle Duration Per Version Routing) / (total number of distinct Case ID, Version ID) |
Case Routing |
Avg Duration Case Idle Per Routing |
Average idle duration per case version routing timestamp |
N/A |
total(Idle Duration Per Version Routing) for all case versions / (total number of routing records) |
Case Routing |
Min Duration Case Idle |
Minimum idle duration per case version routing timestamp |
N/A |
min(Idle Duration Per Version Routing) |
Case Routing |
Max Duration Case Idle |
Maximum idle duration per case version routing timestamp |
N/A |
max(Idle Duration Per Version Routing) |
Case Workflow State |
Version ID |
Case Version (1,2,3...) |
A new case version is created whenever a case is unlocked or reopened. Version number starting with 1 and incremented by 1 for every new version. |
|
Case Workflow State |
Version Type |
Significant, Non-Significant, No Followup - Significant/Non-Significant (Is this follow-up information considered significant, based on company specific policies? If this follow-up information is marked as significant, the regulatory report algorithm will re-run and will calculate the due date based on the most recent significant follow-up date. 1 = Yes, 0 = No) |
No Followup: If there is no followup between two Version Lock DateTime Significant: If there is atleast 1 significant followup between two Version Lock DateTime Non-Significant: If there is followup but no significant followup between two Version Lock DateTime |
|
Case Workflow State |
Version Receipt Date |
Date on which initial/follow-up information was received |
case_followup.receipt_date Case_Master.init_rept_date |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
Case Workflow State |
Safety Receipt Date |
Case Version receipt date at Central Safety |
case_followup.safety_date Case_Master.safety_date |
case_followup.safety_date (Case_Master.safety_date for Initial case version) |
Case Workflow State |
Clock Start Date |
Case Version Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case Workflow State |
Version Creation Timestamp |
The timestamp when the case version was created (Case Book-in timestamp for initial case version or routing timestamp when the case was unlocked/unarchived for other case versions) |
case_master.create_time case_routing.route_date |
For initial case version, case_master.create_time and for other case versions it is case_routing.route_date where lower(case_routing.comment_text) like 'case unlocked%' or case_routing.to_state_id = 2 |
Case Workflow State |
Lock Timestamp |
Case is ready for submission. First lock date of the case version. |
case_routing.route_date |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
Case Workflow State |
Lock Target Days |
Number of target days for processing a case version which is Number of days from Clock Start Date to Lock Timestamp |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
|
Case Workflow State |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
Clock Start Date + Lock Target Days |
Case Workflow State |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Case Workflow State |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case Version post it being Globally Locked. Will be null if either global lock or local lock are null within the specific case version. |
CASE_ROUTING.ROUTE_DATE |
|
Case Workflow State |
State Name |
Workflow State (From Workflow State) |
||
Case Workflow State |
State Finalization User Group |
The Last User Group To Which The Case Version Was Routed To For The Workflow State |
||
Case Workflow State |
State Finalization User ID |
ID of the User Who Finished The Last Execution Of The Workflow State For The Case Version |
||
Case Workflow State |
State Finalization User Name |
The User Who Finished The Last Execution Of The Workflow State For The Case Version |
||
Case Workflow State |
State Start Timestamp |
The First Timestamp When The Workflow State Was Started |
||
Case Workflow State |
State End Timestamp |
The Last Timestamp When The Workflow State Was Completed |
||
Case Workflow State |
Duration Case Modified Per State |
Total time spent in each workflow state (across all users) for actual work done on one case version |
N/A |
sum(Duration Case Modified Per User/State) group by State Name, Case ID, Case Version |
Case Workflow State |
Duration Case Unmodified Per State |
Total time spent in each workflow state (across all users) for work done without any modification on one case version |
N/A |
sum(Duration Case Unmodified Per User/State) group by State Name, Case ID, Case Version |
Case Workflow State |
Duration Case Read Per State |
Sum of Read Duration Per Workflow State |
N/A |
sum(Duration Case Read Per User/State) Group by Workflow State At Case Open, Case ID, Version ID |
Case Workflow State |
Duration State-Start-To-End |
Number Of Days Taken From The First Time The Workflow State Was Started To The Last Time The Workflow State Was Completed (Within A Case Version) |
N/A |
If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result for B is duration from beginning of first B through end of last B, including time spent in second and third A |
Case Workflow State |
Duration Within State |
Total Time The Case Spent In The Workflow State (Within A Case Version) |
N/A |
Sum ("Case Routing History".Routing Timestamp To State - "Case Routing History".Routing Timestamp From State) Group by ("Case Routing History"."Case ID", "Version", "State Routed From") If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result for B is duration of first B plus duration of second B plus duration of third B (does NOT include any time spent in A) |
Case Workflow State |
Duration Version-Creation-To-State- End |
Time Taken To Accomplish A Workflow State After A Case Version Was Created (Including Any Repetition) |
N/A |
State End Timestamp - Version Creation Timestamp |
Case Workflow State |
Average Duration State-Start-To-End |
Average Number Of Days Taken From The First Time The Workflow State Was Started To The Last Time The Workflow State Was Completed (Within A Case Version) |
N/A |
total(Duration State-Start-To-End) for all case versions / # Versions Locked Once |
Case Workflow State |
Average Duration Within State |
Average Time The Case Spent In The Workflow State (Within A Case Version) |
N/A |
total(Duration Within State) for all case versions / # Versions Locked Once |
Case Workflow State |
Average Duration Version-Creation-To-State- End |
Average Time Taken To Accomplish A Workflow State After A Case Version Was Created (Including Any Repetition) |
N/A |
total(Duration Version-Creation-To-State-End) for all case versions / # Versions Locked Once |
Case Workflow State |
Avg Duration Case Modified Per State |
Average time spent in each workflow state (across all users) for actual work done on one case version |
N/A |
(sum(Duration Case Modified Per State) group by State Name) / (total number of distinct Case ID, Version ID group by State Name) |
Case Workflow State |
Avg Duration Case Unmodified Per State |
Average time spent in each workflow state (across all users) for work done without any modification on one case version |
N/A |
(sum(Duration Case Unmodified Per State) group by State Name) / (total number of distinct Case ID, Version ID group by State Name) |
Case Workflow State |
Avg Duration Case Read Per State |
Average time spent in each workflow state (across all users) for work done in read-only mode on one case version |
N/A |
sum(Duration Case Read Per State) / (total number of records in Workflow State History) |
Case Workflow State |
Min Duration Case Modified Per State |
Minimum time spent in each workflow state (across all users) for actual work done on one case version |
N/A |
min((Duration Case Modified Per State) group by State Name) |
Case Workflow State |
Min Duration Case Unmodified Per State |
Minimum time spent in each workflow state (across all users) for work done without any modification on one case version |
N/A |
min((Duration Case Unmodified Per State) group by State Name) |
Case Workflow State |
Min Duration Case Read Per State |
Minimum time spent in each workflow state (across all users) for work done in read-only mode on one case version |
N/A |
min(Duration Case Read Per State) |
Case Workflow State |
Max Duration Case Modified Per State |
Maximum time spent in each workflow state (across all users) for actual work done on one case version |
N/A |
max((Duration Case Modified Per State) group by State Name) |
Case Workflow State |
Max Duration Case Unmodified Per State |
Maximum time spent in each workflow state (across all users) for work done without any modification on one case version |
N/A |
max((Duration Case Unmodified Per State) group by State Name) |
Case Workflow State |
Max Duration Case Read Per State |
Maximum time spent in each workflow state (across all users) for work done in read-only mode on one case version |
N/A |
max(Duration Case Read Per State) |
Case Workflow State |
#Time State Repeated |
The Number Of Times A Workflow State Is Repeated (Within A Case Version) |
Count(*) -1 Group by ("Case Routing History"."Case ID", "Version", "State Routed From") If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result for B is 2 |
|
Case Workflow State |
#Users Who Worked On State |
Number Of Distinct Users Who Worked On A Workflow State For A Case Version |
count(Distinct "Case Routing History".Routing User) Group by ("Case Routing History"."Case ID", "Version", "State Routed From") |
|
Case Workflow State |
# Completed State |
Total Number Of Workflow States Completed |
count (State Name) If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result is 3 |
|
Case Workflow State |
# Repeated State |
Total Count Of All The Workflow States That Were Repeated |
count (State Name) where "# Times State Repeated" > 0 If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result is 2 |
|
Case Workflow State |
% Repeated State |
Total Percentage Of All The Workflow States That Were Repeated |
(# Repeated States / # Completed States ) * 100 If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result is 66.6% |
|
Case Work |
Version ID |
Case Version (1,2,3...) |
A new case version is created whenever a case is unlocked or reopened. Version number starting with 1 and incremented by 1 for every new version. |
|
Case Work |
Version Type |
Significant, Non-Significant, No Followup - Significant/Non-Significant (Is this follow-up information considered significant, based on company specific policies? If this follow-up information is marked as significant, the regulatory report algorithm will re-run and will calculate the due date based on the most recent significant follow-up date. 1 = Yes, 0 = No) |
No Followup: If there is no followup between two Version Lock DateTime Significant: If there is atleast 1 significant followup between two Version Lock DateTime Non-Significant: If there is followup but no significant followup between two Version Lock DateTime |
|
Case Work |
Version Receipt Date |
Date on which initial/follow-up information was received |
case_followup.receipt_date Case_Master.init_rept_date |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
Case Work |
Safety Receipt Date |
Case Version receipt date at Central Safety |
case_followup.safety_date Case_Master.safety_date |
case_followup.safety_date (Case_Master.safety_date for Initial case version) |
Case Work |
Clock Start Date |
Case Version Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case Work |
Case Deletion Timestamp |
Date of the case deletion |
nvl(case_master.deleted, case_master.last_update_time) where nvl(case_master.state_id, 0) = 1 or case_master.deleted is not null |
|
Case Work |
Version Creation Timestamp |
The timestamp when the case version was created (Case Book-in timestamp for initial case version or routing timestamp when the case was unlocked/unarchived for other case versions) |
case_master.create_time case_routing.route_date |
For initial case version, case_master.create_time and for other case versions it is case_routing.route_date where lower(case_routing.comment_text) like 'case unlocked%' or case_routing.to_state_id = 2 |
Case Work |
Lock Timestamp |
Case is ready for submission. First lock date of the case version. |
case_routing.route_date |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
Case Work |
Lock Target Days |
Number of target days for processing a case version which is Number of days from Clock Start Date to Lock Timestamp |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
|
Case Work |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
Clock Start Date + Lock Target Days |
Case Work |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Case Work |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case Version post it being Globally Locked. Will be null if either global lock or local lock are null within the specific case version. |
CASE_ROUTING.ROUTE_DATE |
|
Case Work |
Case Open Timestamp |
Date with timestamp when a case version was opened |
USER_CASE_PROCESSING_TIME.OPEN_DATE |
|
Case Work |
Case Close Timestamp |
Date with timestamp when a case version was closed |
USER_CASE_PROCESSING_TIME.CLOSE_DATE |
|
Case Work |
Duration Case Open |
Duration between case version open timestamp to case version close timestamp. |
N/A |
Case Close Timestamp - Case Open Timestamp |
Case Work |
User With Case Open ID |
ID of user who opens a case version |
USER_CASE_PROCESSING_TIME.USER_ID |
|
Case Work |
User With Case Open Name |
User who opens a case version |
cfg_users.user_fullname where cfg_users.user_id = USER_CASE_PROCESSING_TIME.USER_ID |
|
Case Work |
Case Open Type |
Whether the case version was opened up in (read-only mode) or (editable mode and modified) or (editable mode and not modified). Values - Read, Modified, Unmodified |
USER_CASE_PROCESSING_TIME.READ_ONLY USER_CASE_PROCESSING_TIME.ISMODIFIED |
If USER_CASE_PROCESSING_TIME.READ_ONLY = 1 then Read If USER_CASE_PROCESSING_TIME.ISMODIFIED = 1 then Modified If USER_CASE_PROCESSING_TIME.READ_ONLY = 0 and USER_CASE_PROCESSING_TIME.ISMODIFIED = 0 then Unmodified |
Case Work |
Workflow State At Case Open |
Workflow state of a case version when it is opened by a user |
USER_CASE_PROCESSING_TIME.WORKFLOW_STATE_ID |
|
Case Work |
Routing ID |
|||
Case Work User |
Version ID |
Case Version (1,2,3...) |
A new case version is created whenever a case is unlocked or reopened. Version number starting with 1 and incremented by 1 for every new version. |
|
Case Work User |
Version Type |
Significant, Non-Significant, No Followup - Significant/Non-Significant (Is this follow-up information considered significant, based on company specific policies? If this follow-up information is marked as significant, the regulatory report algorithm will re-run and will calculate the due date based on the most recent significant follow-up date. 1 = Yes, 0 = No) |
No Followup: If there is no followup between two Version Lock DateTime Significant: If there is atleast 1 significant followup between two Version Lock DateTime Non-Significant: If there is followup but no significant followup between two Version Lock DateTime |
|
Case Work User |
Version Receipt Date |
Date on which initial/follow-up information was received |
case_followup.receipt_date Case_Master.init_rept_date |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
Case Work User |
Safety Receipt Date |
Case Version receipt date at Central Safety |
case_followup.safety_date Case_Master.safety_date |
case_followup.safety_date (Case_Master.safety_date for Initial case version) |
Case Work User |
Clock Start Date |
Case Version Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case Work User |
Version Creation Timestamp |
The timestamp when the case version was created (Case Book-in timestamp for initial case version or routing timestamp when the case was unlocked/unarchived for other case versions) |
case_master.create_time case_routing.route_date |
For initial case version, case_master.create_time and for other case versions it is case_routing.route_date where lower(case_routing.comment_text) like 'case unlocked%' or case_routing.to_state_id = 2 |
Case Work User |
Lock Timestamp |
Case is ready for submission. First lock date of the case version. |
case_routing.route_date |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
Case Work User |
Lock Target Days |
Number of target days for processing a case version which is Number of days from Clock Start Date to Lock Timestamp |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
|
Case Work User |
Lock Due Date |
Clock Start Date + Lock Target Days |
N/A |
Clock Start Date + Lock Target Days |
Case Work User |
Lock Due Soon Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Case Work User |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case Version post it being Globally Locked. Will be null if either global lock or local lock are null within the specific case version. |
CASE_ROUTING.ROUTE_DATE |
|
Case Work User |
Workflow State At Case Open |
Internal Due Date For Locking A Case (Case Processing Completion) |
USER_CASE_PROCESSING_TIME.WORKFLOW_STATE_ID |
|
Case Work User |
User With Case Open ID |
ID of user who opens a case version |
USER_CASE_PROCESSING_TIME.USER_ID |
|
Case Work User |
User With Case Open Name |
User who opens a case version |
cfg_users.user_fullname where cfg_users.user_id = USER_CASE_PROCESSING_TIME.USER_ID |
|
Case Work User |
Duration Case Modified Per User/State |
Total edit work duration of open timestamp to close timestamp per user per workflow state per case version. If the workflow state is repeated within same case version then the time spent in every iteration of that state shall be aggregated. |
N/A |
sum(Duration Case Open) group by User At Case Open, Workflow State At Case Open, Case ID, Version ID where Case Open Type = Modified |
Case Work User |
Duration Case Unmodified Per User/State |
Total unmodified duration of open timestamp to close timestamp per user per workflow state per case version. If the workflow state is repeated within same case version then the time spent in every iteration of that state shall be aggregated. |
N/A |
sum(Duration Case Open) group by User At Case Open, Workflow State At Case Open, Case ID, Version ID where Case Open Type = Unmodified |
Case Work User |
Duration Case Read Per User/State |
Total read work duration of open timestamp to close timestamp per user per workflow state per case version. If the workflow state is repeated within same case version then the time spent in every iteration of that state shall be aggregated. |
N/A |
sum(Duration Case Open) group by User At Case Open, Workflow State At Case Open, Case ID, Version ID where Case Open Type = Read |
Case Work User |
Avg Duration Case Modified Per User/State |
Average time spent for actual work done on one case version by each user within each workflow state. Comparison metrics for work efficiency of users within one workflow state. |
N/A |
(sum(Duration Case Modified Per User/State) group by User At Case Open, Workflow State At Case Open) / (total number of distinct Case ID, Version ID group by User At Case Open, Workflow State At Case Open) where Case Open Type = Modified |
Case Work User |
Avg Duration Case Unmodified Per User/State |
Average time spent for work done without any modification on one case version by each user within each workflow state. |
N/A |
(sum(Duration Case Unmodified Per User/State) group by User At Case Open, Workflow State At Case Open) / (total number of distinct Case ID, Version ID group by User At Case Open, Workflow State At Case Open) where Case Open Type = Unmodified |
Case Work User |
Avg Duration Case Read Per User/State |
Average time spent for work done in read-only mode on one case version by each user within each workflow state. |
N/A |
(sum(Duration Case Read Per User/State) group by User At Case Open, Workflow State At Case Open) / (total number of distinct Case ID, Version ID group by User At Case Open, Workflow State At Case Open) where Case Open Type = Read |
Case Work User |
Min Duration Case Modified Per User/State |
Minimum time spent for actual work done on a case version by each user within each workflow state. Comparison metrics for work efficiency of users within one workflow state. |
N/A |
min(Duration Case Modified Per User/State) group by User At Case Open, Workflow State At Case Open |
Case Work User |
Min Duration Case Unmodified Per User/State |
Minimum time spent for work done without any modification on a case version by each user within each workflow state. |
N/A |
min(Duration Case Unmodified Per User/State) group by User At Case Open, Workflow State At Case Open |
Case Work User |
Min Duration Case Read Per User/State |
Minimum time spent for work done in read-only on a case version by each user within each workflow state. |
N/A |
min(Duration Case Read Per User/State) group by User At Case Open, Workflow State At Case Open |
Case Work User |
Max Duration Case Modified Per User/State |
Maximum time spent for actual work done on a case version by each user within each workflow state. Comparison metrics for work efficiency of users within one workflow state. |
N/A |
max(Duration Case Modified Per User/State) group by User At Case Open, Workflow State At Case Open |
Case Work User |
Max Duration Case Unmodified Per User/State |
Maximum time spent for work done without any modification on a case version by each user within each workflow state. |
N/A |
max(Duration Case Unmodified Per User/State) group by User At Case Open, Workflow State At Case Open |
Case Work User |
Max Duration Case Read Per User/State |
Maximum time spent for work done in read-only mode on a case version by each user within each workflow state. |
N/A |
max(Duration Case Read Per User/State) group by User At Case Open, Workflow State At Case Open |
Expedited Submission |
Version ID |
Case Version (1,2,3...) |
A new case version is created whenever a case is unlocked or reopened. Version number starting with 1 and incremented by 1 for every new version |
|
Expedited Submission |
Version Type |
Significant, Non-Significant, No Followup - Significant/Non-Significant (Is this follow-up information considered significant, based on company specific policies? If this follow-up information is marked as significant, the regulatory report algorithm will re-run and will calculate the due date based on the most recent significant follow-up date. 1 = Yes, 0 = No) |
No Followup: If there is no followup between two Version Lock DateTime Significant: If there is atleast 1 significant followup between two Version Lock DateTime Non-Significant: If there is followup but no significant followup between two Version Lock DateTime |
|
Expedited Submission |
Version Receipt Date |
Date on which initial/follow-up information was received |
case_followup.receipt_date Case_Master.init_rept_date |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
Expedited Submission |
Safety Receipt Date |
Case Version receipt date at Central Safety |
case_followup.safety_date Case_Master.safety_date |
case_followup.safety_date (Case_Master.safety_date for Initial case version) |
Expedited Submission |
Version Creation Timestamp |
The timestamp when the case version was created (Case Book-in timestamp for initial case version or routing timestamp when the case was unlocked/unarchived for other case versions) |
case_master.create_time case_routing.route_date |
For initial case version, case_master.create_time and for other case versions it is case_routing.route_date where lower(case_routing.comment_text) like 'case unlocked%' or case_routing.to_state_id = 2 |
Expedited Submission |
Lock Timestamp |
Case is ready for submission. First lock date of the case version. |
case_routing.route_date |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
Expedited Submission |
Lock Target Days |
Number of target days for processing a case version which is Number of days from Clock Start Date to Lock Timestamp |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - If Case Serious then 10 Days else 30 Days |
|
Expedited Submission |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
N/A |
Clock Start Date + Lock Target Days |
Expedited Submission |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
N/A |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Lock Due Date - (2) |
Expedited Submission |
Local Lock Timestamp |
Latest Local Lock Timestamp for the Case Version post it being Globally Locked. Will be null if either global lock or local lock are null within the specific case version. |
CASE_ROUTING.ROUTE_DATE |
|
Expedited Submission |
Report ID |
Unique Identifier for the report |
cmn_reg_reports.reg_report_id |
|
Expedited Submission |
Regulatory Report Type |
Report Form ID |
cmn_reg_reports.report_form_id |
|
Expedited Submission |
Report Destination |
CRO Client/Sponsor/License Partner/Regulatory Authority |
cmn_reg_reports.agency_id |
|
Expedited Submission |
Foreign / Domestic |
Domestic if the Agency Country = Country of Incidence for the Case else Foreign |
CASE_MASTER.COUNTRY_ID LM_REGULATORY_CONTACT.COUNTRY |
Domestic if CASE_MASTER.COUNTRY_ID = (SELECT COUNTRY FROM LM_REGULATORY_CONTACT WHERE AGENCY_ID = CMN_REG_REPORTS.AGENCY_ID) else Foreign |
Expedited Submission |
Report Assigned User Group |
User Group responsible for the report |
cmn_reg_reports.group_id |
|
Expedited Submission |
Report Timeframe |
Timeframe when the report is due. This field specifies the report's scheduled due-date based on the number of days, after the initial receipt or significant follow-up date. |
cmn_reg_reports.timeframe |
|
Expedited Submission |
Report Submission Country |
Country of destination to which report is submitted |
cmn_reg_reports.country_id |
|
Expedited Submission |
Report Nullification Indicator |
A flag to identify nullified reports |
cmn_reg_reports.nullification_reason |
If cmn_reg_reports.nullification = 1 then 'Yes' else 'No' |
Expedited Submission |
Report Nullification Reason |
Reason for report nullification |
cmn_reg_reports.nullification_reason |
|
Expedited Submission |
Report Submission User ID |
ID of user submitting the case to the agency |
cmn_reg_reports.user_id |
|
Expedited Submission |
Report Submission User Name |
User submitting the case to the agency |
cfg_users.user_fullname where cfg_users.user_id = cmn_reg_reports.user_id |
|
Expedited Submission |
Report Due Date |
Date the report is due |
cmn_reg_reports.due_date |
|
Expedited Submission |
Report Submission / MDN Timestamp |
For non-E2B reports - Date on which submission was sent to the destination For E2B reports - Date when report was successfully received by the destination. Only those reports which are accepted by the destination. |
N/A |
If Report Completion Type = Submitted then: For non-E2B reports - Report Submission Timestamp For E2B reports - MDN Timestamp Else Null |
Expedited Submission |
Report Submission Timestamp |
Date on which submission was sent to the agency |
cmn_reg_reports.date_submitted |
|
Expedited Submission |
Report Schedule Timestamp |
Date on which report got scheduled |
cmn_reg_reports.date_scheduled |
|
Expedited Submission |
Report Generation Timestamp |
Report Generation Date |
cmn_reg_reports.date_generated |
|
Expedited Submission |
Report Approval Timestamp |
Report Approved Date |
case_reg_reports.date_approved |
|
Expedited Submission |
Report Submission Comments |
Submission Notes |
cmn_reg_reports.submit_notes |
|
Expedited Submission |
Report Initial / Follow-Up ID |
Report Followup Number (1,2,3…. 0 - for Initial) 'Initial' if followup number = 0 else 'F/U #' || followup number |
cmn_reg_reports.followup_num |
DECODE (cmn_reg_reports.followup_num, 0, 'Initial', 'F/U #' || cmn_reg_reports.followup_num ) |
Expedited Submission |
Report Completion Type |
1 - Submitted, 2 - Deleted, 3 - No Submission Required, 4 - Failed / Pending ACK: 1 - Submitted: If non-E2B report is submitted (submission date is present) or successful ACK is present for E2B reports then 'Submitted' 2 - Deleted: If report is deleted (deleted date is present) then 'Deleted'. It includes the reports which are marked as Submission Not Required but deleted. 3 - No Submission Required: If report is marked as Submission Not Required and report is not deleted then 'No Submission Required' 4 - Failed / Pending ACK: For E2B reports, if ACK failed or ACK is not received then 'Failed / Pending ACK' |
Submitted: cmn_reg_reports.date_submitted is not null and ((cmn_reg_reports.report_form_id <> 27) or (SAFETYREPORT.REPORTACKNOWLEDGMENT.REPORTACKNOWLEDGMENTCODE in (''1'',''01'') and (cmn_reg_reports.report_form_id = 27)) Deleted: cmn_reg_reports.deleted is not null No Submission Required: cmn_reg_reports.submit_required = 0 and cmn_reg_reports.deleted is null Failed / Pending ACK: cmn_reg_reports.date_submitted is not null and SAFETYREPORT.REPORTACKNOWLEDGMENT.REPORTACKNOWLEDGMENTCODE not in (''1'',''01'') and cmn_reg_reports.report_form_id = 27 |
|
Expedited Submission |
Report Downgrade Indicator |
A flag to identify downgrade reports |
cmn_reg_reports.submit_notes |
If cmn_reg_reports.submit_notes like '*Downgrade*%' then 'Yes' else 'No' |
Expedited Submission |
Report Deletion Timestamp |
Date when report is deleted |
cmn_reg_reports.deleted |
|
Expedited Submission |
Report No Submission Required Timestamp |
Date that non-submission of report was determined |
cmn_reg_reports.date_submission_determined |
|
Expedited Submission |
Report No Submission Required Reason |
Reason if submission is not required |
cmn_reg_reports.non_submit_reason |
|
Expedited Submission |
Report Submission User Site |
Site of the person submitting the case to the destination |
lm_sites.site_desc where lm_sites.site_id = cfg_users.site_id where cmn_reg_reports.owner_id = cfg_users.user_id |
|
Expedited Submission |
ACK Timestamp |
Report acknowledgement timestamp - timestamp when outgoing E2B report is accepted / rejected by recipient's database |
messages.acknowledgmentheader.messagedate |
|
Expedited Submission |
MDN Timestamp |
Message delivery notification timestamp - timestamp when outgoing E2B report is received by recipient's gateway from sender's gateway |
EDI_INFO.EDI_COMPLETE_DATE where MESSAGES.EDI_TRACKING_ID = EDI_INFO.EDI_TRACKING_ID and safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
EDI In Timestamp |
Timestamp when outgoing E2B report is received by sender's gateway from Argus Safety |
MESSAGES .DATE_TRANSMITTED where safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
EDI Out Timestamp |
Timestamp when outgoing E2B report is transmitted by sender's gateway to recipient's gateway |
EDI_INFO.EDI_TRANSMIT_DATE where MESSAGES.EDI_TRACKING_ID = EDI_INFO.EDI_TRACKING_ID and safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
ACK Status |
Values - Success, Pending, Warning, Failure, Not Applicable |
If (esm_ae_status_lkup.esm_status_type_cd in (4,10) and cfg_sender.transmission_method = 2) then 'Failure' If (esm_ae_status_lkup.esm_status_type_cd = 5 and messages.acknowledgmentheader.transmissionacknowledgmentcode in ('01', '02', '1', '2')) then If safetyreport.reportacknowledgment.reportacknowledgmentcode in ('02', '2') then 'Failure' If safetyreport.reportacknowledgment.reportacknowledgmentcode in ('01', '1') and safetyreport.reportacknowledgment.errormessagecomment is not null then 'Warnings' If safetyreport.reportacknowledgment.reportacknowledgmentcode in ('01', '1') then 'Success' IF esm_ae_status_lkup.esm_status_type_cd in (12,13,14,17,18,19) then 'Pending' Else 'Not Applicable' where ESM_AE_STATUS_LKUP.ESM_STATUS_TYPE_CD = MESSAGES.STATUS and safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
MDN Status |
Values - Success, Pending, Failure, Not Applicable |
If cfg_sender.transmission_method = 2 then 'Not Applicable' If esm_ae_status_lkup.esm_status_type_cd = 11 then 'Pending' If esm_ae_status_lkup.esm_status_type_cd in (5,12,13,14,17,18,19) then 'Success' If esm_ae_status_lkup.esm_status_type_cd = 15 then 'Failure' Else 'Not Applicable' where ESM_AE_STATUS_LKUP.ESM_STATUS_TYPE_CD = MESSAGES.STATUS and safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
EDI In Status |
Values - Success, Pending, Failure, Not Applicable |
If esm_ae_status_lkup.esm_status_type_cd = 1 then 'Pending' If esm_ae_status_lkup.esm_status_type_cd in (2,3,4,5,10,11,12,13,14,15,17,18,19) then 'Success' If esm_ae_status_lkup.esm_status_type_cd in (6, 7) then 'Failure' Else 'Not Applicable' where ESM_AE_STATUS_LKUP.ESM_STATUS_TYPE_CD = MESSAGES.STATUS and safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
EDI Out Status |
Values - Success, Pending, Failure, Not Applicable |
If cfg_sender.transmission_method = 2 then 'Not Applicable' If esm_ae_status_lkup.esm_status_type_cd in (2, 3) then 'Pending' If esm_ae_status_lkup.esm_status_type_cd in (5,11,12,13,14,15,17,18,19) then 'Success' If esm_ae_status_lkup.esm_status_type_cd in (4, 10) then 'Failure' Else 'Not Applicable' where ESM_AE_STATUS_LKUP.ESM_STATUS_TYPE_CD = MESSAGES.STATUS and safetyreport.msg_id = messages.msg_id and cmn_reg_reports.esm_report_id = safetyreport.report_id |
|
Expedited Submission |
ACK Comments |
Acknowledgement comments received from the receiver |
SAFETYREPORT.REPORTACKNOWLEDGMENT.ERRORMESSAGECOMMENT |
|
Expedited Submission |
Duration Clock-Start-Date-To-Submission |
Time taken (in days) to submit a report from the Aware Date |
N/A |
days between (Report Submission / MDN Timestamp, Clock Start Date) |
Expedited Submission |
Duration Version-Lock-To-Submission |
Time taken (in days) to submit a case from the completion date |
N/A |
days between (Report Submission / MDN Timestamp, Lock Timestamp) |
Expedited Submission |
Duration Report-Generation-To-Submission |
Interval between Report Generation Date and Report Submission / MDN Timestamp |
N/A |
days between (Report Submission / MDN Timestamp, Report Generation Date) |
Expedited Submission |
Duration Report-Schedule-To-Submission |
Interval between Report Schedule Date and Report Submission / MDN Timestamp |
N/A |
days between (Report Submission / MDN Timestamp, Report Schedule Date) |
Expedited Submission |
Avg Duration Report-Generation-To-Submission |
Average duration between Report Generation Date and Report Submission Date |
N/A |
Sum(Duration Report-Generation-To-Submission)/# Reports Submitted |
Expedited Submission |
Avg Duration Report-Schedule-To-Submission |
Average duration between Report Schedule Date and Report Submission Date |
N/A |
Sum(Duration Report-Schedule-To-Submission)/# Reports Submitted |
Expedited Submission |
Min Duration Report-Generation-To-Submission |
Minimum duration between Report Generation Date and Report Submission Date |
N/A |
Minimum(Duration Report-Generation-To-Submission) |
Expedited Submission |
Min Duration Report-Schedule-To-Submission |
Minimum duration between Report Schedule Date and Report Submission Date |
N/A |
Minimum(Duration Report-Schedule-To-Submission) |
Expedited Submission |
Max Duration Report-Generation-To-Submission |
Maximum duration between Report Generation Date and Report Submission Date |
N/A |
Maximum(Duration Report-Generation-To-Submission) |
Expedited Submission |
Max Duration Report-Schedule-To-Submission |
Maximum duration between Report Schedule Date and Report Submission Date |
N/A |
Maximum(Duration Report-Schedule-To-Submission) |
Expedited Submission |
# Reports Nullified |
Total number of Nullified Submitted reports |
N/A |
count(Nullification Report Flag) where Nullification Report Flag = 'Yes' |
Expedited Submission |
# Days Report Late |
Number of days taken from Due Date to Report Submission / MDN Timestamp |
N/A |
Report Completion Type = Submitted If Days between (Report Submission / MDN Timestamp, Report Due Date) > 0 then Days between (Report Submission / MDN Timestamp, Report Due Date) else 0 |
Expedited Submission |
# Reports Submitted |
Total number of submissions |
N/A |
count(Number of records from Expedited Submission History) where Report Submission / MDN Timestamp is not null |
Expedited Submission |
# Reports Submitted On-Time |
Number of reports submitted by the regulatory due date |
N/A |
count(Number of records from Expedited Submission History) where Report Submission / MDN Timestamp <= Report Due Date and Report Submission / MDN Timestamp is not null |
Expedited Submission |
% Reports Submitted On-Time |
Percentage of reports submitted on time by the regulatory due date |
N/A |
(# Reports Submitted On-Time/# Reports Submitted) * 100 |
Expedited Submission |
# Reports Submitted Late |
Number of reports submitted late by the regulatory due date |
N/A |
count(Number of records from Expedited Submission History) where Report Submission / MDN Timestamp > Report Due Date and Report Submission / MDN Timestamp is not null |
Expedited Submission |
% Reports Submitted Late |
Percentage of reports submitted late by the regulatory due date |
N/A |
(# Reports Submitted Late/# Reports Submitted) * 100 |
Expedited Submission |
# Reports Deleted |
Total number of deleted reports |
N/A |
count(Report Deleted Indicator) where Report Deleted Indicator = 'Yes' |
Expedited Submission |
# Reports No Submission Required |
Total number of reports marked as Submission Not Required |
N/A |
count(Number of records from Expedited Submission History) where Report Submission Type = 'No Submission Required' |
Expedited Submission |
# Reports Downgraded |
Total number of downgraded reports |
N/A |
count(Report Downgrade Indicator) where Report Downgrade Indicator = 'Yes' |
Report Routing |
Report ID |
Unique Identifier for the report |
rpt_routing.reg_report_id |
|
Report Routing |
Report Routing ID |
Workflow Sequence Number |
rpt_routing.seq_num |
|
Report Routing |
Report Routing Justification |
Report Routing Justification |
LM_JUSTIFICATIONS.JUSTIFICATION WHERE JUSTIFICATION_ID = RPT_ROUTING.JUSTIFICATION_ID AND LM_JUSTIFICATIONS.FIELD_ID=6150002 |
|
Report Routing |
Report State Routed From |
Workflow state from which case was promoted to next workflow state |
CFG_RPT_WF_STATES.state_name where rpt_routing.from_report_state_id = CFG_RPT_WF_STATES.REPORT_STATE_ID |
|
Report Routing |
Report State Routed To |
Workflow state to which case was promoted from previous workflow state |
CFG_RPT_WF_STATES.state_name where rpt_routing.from_report_state_id = CFG_RPT_WF_STATES.REPORT_STATE_ID |
|
Report Routing |
Report Routing Timestamp From State |
The timestamp on which case was routed From Workflow State |
rpt_routing.route_date |
|
Report Routing |
Report Routing Timestamp To State |
The timestamp on which case was routed To Workflow State |
rpt_routing.route_date |
|
Report Routing |
Report Routing User ID |
ID of the User who last worked on the Workflow step |
rpt_routing.user_id |
|
Report Routing |
Report Routing User Name |
The User who last worked on the Workflow step |
cfg_users.user_fullname where cfg_users.user_id = rpt_routing.user_id |
|
Report Routing |
Report Comments |
Routing comments |
rpt_routing.comment_text |
rpt_routing.comment_text |
The following table displays how Argus Analytics populates each column from an Argus Safety database.
Table B-3 Presentation Catalog - Oracle Argus Safety Sources
Fact | Column | Description | Source Table/Column | Comments/Details |
---|---|---|---|---|
Case |
Case ID |
Case Identification (same as Manufacturer Code Number [MCN]) |
case_master.case_num |
|
Case |
Case Internal ID |
Internal Case ID Generated |
case_master.case_id |
|
Case |
Enterprise ID |
CRO Enterprise (Client) Short Name |
cfg_enterprise.enterprise_abbrv |
|
Case |
Case Creator ID |
ID of Case Author (who created the case) |
case_master.user_id |
|
Case |
Case Creator Name |
Case Author (who created the case) |
cfg_users.user_fullname where cfg_users.user_id = case_master.user_id |
|
Case |
Case Processing Site |
Site (for ex., drug safety case processing location) |
case_master.site_id |
|
Case |
Country Of Incidence |
Country where the adverse event occurred |
case_master.country_id |
|
Case |
Case Type |
Case Report Type (for ex., Spontaneous/Clinical/Literature etc.) |
case_master.Report_type |
|
Case |
Compliance Classification |
7-day Case, 15-day Case, Non-Expedited Case |
lm_case_classification.description |
where lm_case_classification.description has values in (7-day Case, 15-day Case, Non-Expedited Case) |
Case |
S/R/U Classification |
SAE Case, SAR Case, SUSAR Case |
lm_case_classification.description |
where lm_case_classification.description has values in (SAE Case, SAR Case, SUSAR Case) |
Case |
F/LT Classification |
Fatal / Life-Threatening Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Fatal / Life-Threatening Case) |
Case |
Pregnancy Classification |
Pregnancy Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Pregnancy Case) |
Case |
Source Classification |
Authority Case, Consumer Case, Literature Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Authority Case, Consumer Case, Literature Case) |
Case |
Confirmation Classification |
Medically Confirmed Case |
lm_case_classification.description |
where lm_case_classification.description has values in (Medically Confirmed Case) |
Case |
Case Classification Group 7 |
Case Classification Group 7 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 7) |
Case |
Case Classification Group 8 |
Case Classification Group 8 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 8) |
Case |
Case Classification Group 9 |
Case Classification Group 9 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 9) |
Case |
Case Classification Group 10 |
Case Classification Group 10 |
lm_case_classification.description |
where lm_case_classification.description has values in (Case Classification 10) |
Case |
Product Type |
Drug, Device and Vaccine |
case_product.views_available |
|
Case |
Study ID |
Study ID |
case_study.study_num |
|
Case |
Center ID |
Center ID for the Study in the case |
lm_centers.center_no where case_study.center_id = lm_centers.center_id |
|
Case |
Case Seriousness |
Case Serious or Non-Serious (Y/N) |
case_assess.seriousness |
|
Case |
Case Listedness |
Unlisted/Unexpected/listed/expected |
case_assess.listedness |
|
Case |
Case Causality |
Adverse event caused by the product (related/not related) |
case_assess.agent_suspect |
|
Case |
Case Outcome |
Case Level Outcome using the ICH guidelines eg "Recovered" |
case_assess.outcome |
|
Case |
Gender |
Gender of the Patient |
case_pat_info.gender_id |
|
Case |
Pregnant |
Patient pregnant (Yes/No) |
case_pat_info.pat_stat_preg |
|
Case |
Age Group |
Patient Age Group |
case_pat_info.age_group_id |
|
Case |
Ethnicity |
Ethnicity of the Patient |
case_pat_info.ethnicity_id |
|
Case |
Lactation |
Patient breastfeeding |
case_pregnancy.breastfeeding |
|
Case |
Primary Reporter Type |
Type of the first/primary reporter (for ex., Physician, Consumer etc) |
case_reporters.reporter_type where primary_contact = 1 |
|
Case |
Primary Suspect Product |
Primary Suspect Product for a case |
case_product.product_name where case_product.first_sus_prod = 1 |
|
Case |
HCP |
Health Care Professional (ex., physician, nurse, pharmacist) flag. Primary Reporter of the case is HCP or not |
case_reporters.hcp_flag where primary_contact = 1 |
|
Case |
SUSAR |
Suspected Unexpected Serious Adverse Reaction Flag(0 / 1) |
case_master.susar |
|
Case |
Initial Receipt Date |
Date on which initial case information was received |
case_master.init_rept_date |
|
Case |
Clock Start Date |
Case Aware Date |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
Case |
Lock Timestamp |
Case is ready for submission. Lock date of the case version. |
case_master.date_locked |
|
Case |
Locked Indicator |
Flag to indicate whether the case version is locked or unlocked |
case_master.date_locked |
If case_master.date_locked is not null then 'Yes' else 'No' |
Case |
Case Owner ID |
ID of user who is responsible for the case throughout its lifetime |
case_master.worklist_owner_id |
|
Case |
Case Owner Name |
User who is responsible for the case throughout its lifetime |
cfg_users.user_fullname where cfg_users.user_id = case_master.worklist_owner_id |
|
Case |
State Name |
Workflow State (From Workflow State) |
||
Case |
CRO Project Code |
If clinical case then Study Configuration -> Other ID, else Product Configuration -> Company Drug Code for the Primary Suspect Product |
LM_REPORT_TYPE.INCL_TRIAL |
If LM_REPORT_TYPE.INCL_TRIAL = 1 then LM_STUDIES.OTHER_ID, else LM_PRODUCTS.DRUG_CODE for Primary Suspect Product |
Pending Cases |
Follow-up ID |
Latest Case Follow-up # (0,1,2,3...) |
case_routing.followup_num |
Followup # as appear on Argus Case Form for the latest followup. It shall be 0 if the latest case version is Initial |
Pending Cases |
Initial / Follow-Up |
Initial or Follow-up entry of the Case |
N/A |
If Version = 1 then Initial Else Follow-up |
Pending Cases |
Follow-up Type |
Significant, Non-Significant - |
case_followup.significant |
If Case_followup.Significant = 1 then 'Significant' else 'Non-Significant'. If the latest case version is Initial then 'Significant'. |
Pending Cases |
Latest Followup Receipt Date |
Date On Which Latest Follow-up Information Was Received. If There Is No Followup Then Followup Receipt Date Will Be Null |
case_followup.receipt_date |
|
Pending Cases |
Case Deletion Date |
Date On Which Latest Follow-up Information WasReceived. If There Is No Followup Then FollowupReceipt Date Will Be Null |
case_followup.receipt_date |
|
Pending Cases |
Assigned User ID |
ID of the User Assigned To The Current Work |
case_master.owner_id |
|
Pending Cases |
Assigned User Name |
User Assigned To The Current Work |
cfg_users.user_fullname where cfg_users.user_id = case_master.owner_id |
|
Pending Cases |
User With Case ID |
Case Worklist User. User Who Has The Case Open. |
web_cases_open.user_id |
|
Pending Cases |
User With Case Open Name |
Case Worklist User. User Who Has The Case Open |
cfg_users.user_fullname where cfg_users.user_id = web_cases_open.user_id |
|
Pending Cases |
Routing Justification |
Case Routing Justification For The Latest Routing |
case_routing.justification_id |
|
Pending Cases |
Routing ID |
Workflow Sequence Number |
case_routing.seq_num |
|
Pending Cases |
Routing Timestamp |
The Timestamp On Which Case Was Routed To Current Workflow State |
case_routing.route_date |
|
Pending Cases |
Routing User Group |
User Group To Which A Case Is Routed. |
cfg_workflow_rules.group_idwhere cfg_workflow_rules.seq_num =case_routing.workflow_seq_num |
|
Pending Cases |
Routing User ID |
Id of the User Group To Which A Case Is Routed. |
case_routing.user_id |
|
Pending Cases |
Routing User Name |
User Group To Which A Case Is Routed. |
cfg_users.user_fullname where cfg_users.user_id = case_routing.user_id |
|
Pending Cases |
Local Lock Timestamp |
Latest Local Lock Timestamp when the current version of the case is Locally Locked or Globally and Locally Locked. Will be null if either global lock or local lock is null. |
CASE_ROUTING.ROUTE_DATE |
|
Pending Cases |
Local Locked Indicator |
Will be 1 when the current version of the case is Locally Locked or Globally and Locally Locked. Otherwise, it will be 0 or null. |
CASE_ROUTING.ROUTE_DATE |
|
Pending Cases |
Duration Global to Local Lock |
Is the time between the Current Versions latest local case lock timestamp and the Current Versions last global case lock that occurred before that local case lock. Will be null if either global lock or local lock is null. |
CASE_ROUTING.ROUTE_DATE |
|
Pending Cases |
# Days Case Open |
Number of days elapsed since aware date (Clock Start Date) of the case |
NVL(TRUNC(SYSDATE) - TRUNC(Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1))), 0) |
|
Pending Cases |
# Days Remaining |
Number of days remaining for case processing as per the due soon date configured in Argus Safety |
NVL(TRUNC(case_master.due_soon) - TRUNC (SYSDATE), 0) |
|
Pending Expedited Reports |
Report ID |
Unique Identifier for the report |
cmn_reg_reports.reg_report_id |
|
Pending Expedited Reports |
Report Initial / Follow-Up ID |
Report Followup Number (1,2,3…. 0 - for Initial)'Initial' if followup number = 0 else 'F/U #' || followup number |
DECODE (cmn_reg_reports.followup_num, 0, 'Initial', 'F/U #' || cmn_reg_reports.followup_num) |
|
Pending Expedited Reports |
Regulatory Report Type |
Report Form ID |
cmn_reg_reports.report_form_id |
|
Pending Expedited Reports |
Report 7/15 Timeframe |
Timeframe when the report is due.This field specifies the report's scheduled due-date based on the number of days, after the initial receipt or significant follow-up date.If timeframe <= 7 then '7' else '15' |
case when cmn_reg_reports.timeframe <= 7 then 7 else 15 end |
|
Pending Expedited Reports |
Foreign / Domestic |
Domestic if the Agency Country = Country of Incidence for the Case else Foreign |
Domestic if CASE_MASTER.COUNTRY_ID = (SELECT COUNTRY FROM LM_REGULATORY_CONTACT WHERE AGENCY_ID = CMN_REG_REPORTS.AGENCY_ID) else Foreign |
|
Pending Expedited Reports |
Report Assigned User Group |
User Group responsible for the report |
cmn_reg_reports.group_id |
|
Pending Expedited Reports |
Report Assigned User ID |
ID of the user assigned to the report |
cmn_reg_reports.owner_id |
|
Pending Expedited Reports |
Report Assigned User Name |
User assigned to the report |
cfg_users.user_fullname where cfg_users.user_id = cmn_reg_reports.owner_id |
|
Pending Expedited Reports |
Report Due Date |
Date the report is due |
cmn_reg_reports.due_date |
|
Pending Expedited Reports |
Pending Expedited Reports |
Internal due date for report submission |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days"Pending Expedited Reports".Report Due Date - (2) |
|
Pending Expedited Reports |
Report On-Time Indicator |
Report submission on-time indicator. The values here are Red, Yellow and Green.Green: when Report Due Soon Date has not elapsedYellow: when no. of days remaining for report submission are less than or equal to the no. of due soon days configured in the PVARed: when Report Due Date has elapsed |
Green: count(Number of records from Pending Expedited Reports) where sysdate <= Report Due Soon DateYellow: count(Number of records from Pending Expedited Reports) where sysdate > Report Due Soon Date and sysdate <= Report Due DateRed: count(Number of records from Pending Expedited Reports) where sysdate > Report Due Date |
|
Pending Expedited Reports |
Report Schedule Timestamp |
Date on which report got scheduled |
cmn_reg_reports.date_scheduled |
|
Pending Expedited Reports |
Report Generation Timestamp |
Report Generation Date |
cmn_reg_reports.date_generated |
|
Pending Expedited Reports |
Report Approval Timestamp |
Report Approved Date |
case_reg_reports.date_approved |
|
Pending Expedited Reports |
Report Downgrade Indicator |
A flag to identify downgrade reports |
If cmn_reg_reports.submit_notes like '*Downgrade*%' then 'Yes' else 'No' |
|
Pending Expedited Reports |
# Days Report Late |
Number of days by which report is past due date |
N/A |
sysdate - Report Due Date |
Pending Expedited Reports |
# Days Report Open |
Number of days that the report is open |
N/A |
sysdate - NVL(Clock Start Date, Report Schedule Timestamp) |
Pending Expedited Reports |
# Pending Reports |
Total number of reports not submitted |
N/A |
count(Number of records from Pending Expedited Reports) |
Pending Expedited Reports |
# Pending Reports Downgraded |
Total number of downgraded reports |
N/A |
count(Report Downgrade Indicator) where Report Downgrade Indicator = 'Yes' |
Pending Expedited Reports |
# Pending Reports On-Time |
Count of pending reports where submission is On-Time (Report On-Time Indicator is Green)[Number of cases submitted by the inernal due soon date] |
N/A |
where Report On-Time Indicator is Green |
Pending Expedited Reports |
% Pending Reports On-Time |
Percentage of cases submitted on time by the internal due soon date |
N/A |
(# Pending Reports On-Time/# Pending Reports) * 100 |
Pending Expedited Reports |
# Pending Reports Due Soon |
Count of pending reports where submission is Due Soon (Report On-Time Indicator is Yellow)[Number of reports due soon for submission] |
N/A |
where Report On-Time Indicator is Yellow |
Pending Expedited Reports |
% Pending Reports Due Soon |
Percentage of reports due soon for submission |
N/A |
(# Pending Reports Due Soon/# Reports Not Submitted) * 100 |
Pending Expedited Reports |
# Pending Reports Overdue |
Count of pending reports that have not been submitted by due date (Report On-Time Indicator is Red) |
N/A |
where Report On-Time Indicator is Red |
Pending Expedited Reports |
% Pending Reports Overdue |
Percentage of reports overdue for submission |
N/A |
(# Reports Overdue For Submission/# Reports Not Submitted) * 100 |