Oracle® Health Sciences Pharmacovigilance Operational Analytics User and Administrator Guide Release 1.0 E23554-01 |
|
|
PDF · Mobi · ePub |
The OPVA Presentation Catalog displays columns that you can use to create requests.
This appendix contains the following topics:
See Also:
Chapter 3, "Working with Reports"
Table B-1 describes the Dimensions displayed in the OPVA 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. Each Dimension column has an ID number, which is provided for cross-reference to other tables in this appendix.
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
ID | Dimension | Column | Description |
---|---|---|---|
AD05-03 |
Case Processing Site |
Case Processing Site |
Case Processing Site That The User Belongs To. |
TA-CVH04 |
Enterprise |
Enterprise ID |
CRO Enterprise (Client) Short Name |
Enterprise |
Enterprise Name |
Enterprise Name |
|
AD01-01 |
Period |
Year |
Per_name_year From W_day_d Table |
AD01-02 |
Period |
Quarter |
Per_name_qtr From W_day_d Table |
AD01-03 |
Period |
Month |
Per_name_month From W_day_d Table |
AD01-04 |
Period |
Week |
Per_name_week From W_day_d Table |
AD01-05 |
Period |
Day |
Calendar_date From W_day_d Table |
AD02-02 |
Product |
Product Group |
Therapeutic Alignment Of User Groups By Product Indication (For Example, Oncology Group, Gynecology Group Etc) |
AD02-03 |
Product |
Product Family |
Product Family - It Is Used To Group Like Products That Share Datasheets And Primary Ingredients |
AD02-04 |
Product |
PSUR Group |
PSUR Group Name |
AD02-01 |
Product |
Product Name |
Product Name |
AD02-05 |
Product |
Product Formulation |
Formulation Of The Product |
AD02-06 |
Product |
Product Concentration |
Concentration/Strength Of The Product |
TA-CRH06 |
State |
State Routed From |
Workflow State From Which The Case Was Promoted To The Next Workflow State |
TA-CRH07 |
State |
State Routed To |
Workflow State From Which The Case Was Promoted From The Previous Workflow State |
TA-CWSH04 |
State |
State Name |
Workflow State (From Workflow State) |
AD03-01 |
Study |
Study ID |
Study ID |
AD03-02 |
Study |
Project ID |
Project ID For Study |
TA-CRH10 |
User |
Routing User ID |
The User Who Last Worked On The Workflow Step |
AD05-02 |
User |
Routing User Name |
Routing User Name |
AD05-03 |
User |
Case Processing Site |
Case Processing Site That The User Belongs To. |
TA-CWSH07 |
User |
State Finalization User ID |
The User Who Finished The Last Execution Of The Workflow State For The Case Version |
AD05-02 |
User |
State Finalization User Name |
State Finalization User Name |
TA-CRH11 |
User Group |
Routing User Group |
User Group Which Worked On The Workflow Step |
TA-CWSH08 |
User Group |
State Finalization User Group |
The Last User Group To Which The Case Version Was Routed To For The Workflow State |
AD06-01 |
User Group |
Routing User Group |
User Group Name |
Table B-2 describes the Facts displayed in the OPVA Presentation Catalog, and their Columns. For each Column, it shows the name of the Column, its description, and the Fact and Group within the Fact that contain the Column. The table is sorted by Group and Column, within Fact. This corresponds to how the Fact columns are organized in the Presentation Catalog, within the Fact folder. Each Fact Column has an ID number, which is provided for cross-reference to other tables in this Appendix.
Table B-2 Presentation Catalog - Facts
ID | Fact | Column | Description |
---|---|---|---|
TA-CVH01 |
Case |
Case ID |
Case Identification (Same As Manufacturer Code Number [Mcn]) |
TA-CVH02 |
Case |
Version ID |
Case Version (1,2,3...) |
Case |
Case Internal ID |
Internal Case Id Generated |
|
TA-CVH03 |
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) |
TA-CVH04 |
Case Version |
Enterprise ID |
CRO (Enterprise) Short Name |
TA-CVH05 |
Case Version |
Initial/Follow-up |
Initial Or Follow-up Entry Of The Case |
TA-CVH06 |
Case Version |
Case Deleted Indicator |
A Flag To Identify If The Case Has Been "Deleted" Or Not |
TA-CVH07 |
Case Version |
Case Creator ID |
Case Author (Who Created The Case) |
TA-CVH08 |
Case Version |
Case Creator Name |
|
TA-CVH09 |
Case Version |
Case Processing Site |
Site (Example: Drug Safety Case Processing Location) |
TA-CVH10 |
Case Version |
Country Of Incidence |
Country Where The Adverse Event Occurred |
TA-CVH10 |
Case Version |
Case Type |
Case Report Type (For Ex., Spontaneous/Clinical/Literature, Etc.) |
TA-CVH11 |
Case Version |
Compliance Classification |
7-day Case, 15-day Case, Non-Expedited Case |
TA-CVH12 |
Case Version |
S/R/U Classification |
SAE Case, SAR Case, SUSAR Case |
TA-CVH13 |
Case Version |
F/LT Classification |
Fatal/Life-Threatening Case |
TA-CVH14 |
Case Version |
Pregnancy Classification |
Pregnancy Case |
TA-CVH15 |
Case Version |
Source Classification |
Authority Case, Consumer Case, Literature Case |
TA-CVH16 |
Case Version |
Confirmation Classification |
Medically Confirmed Case |
TA-CVH17 |
Case Version |
Case Classification Group7 |
Case Classification Group7 |
TA-CVH18 |
Case Version |
Case Classification Group8 |
Case Classification Group8 |
TA-CVH19 |
Case Version |
Case Classification Group9 |
Case Classification Group9 |
TA-CVH20 |
Case Version |
Case Classification Group10 |
Case Classification Group10 |
TA-CVH21 |
Case Version |
Product Type |
Drug, Device, And Vaccine |
TA-CVH22 |
Case Version |
Study ID |
Study ID |
TA-CVH23 |
Case Version |
Center ID |
Center ID For The Study In The Case Version |
TA-CVH24 |
Case Version |
Case Seriousness |
Case Serious or Non-Serious (Y/N) |
TA-CVH25 |
Case Version |
Case Listedness |
Unlisted/Unexpected/Listed/Expected |
TA-CVH26 |
Case Version |
Case Causality |
Adverse Event Caused By The Product (Related/Not Related) |
TA-CVH27 |
Case Version |
Case Outcome |
Case Level Outcome Using The ICH Guidelines. Example: 'Recovered; |
TA-CVH28 |
Case Version |
Gender |
Gender Of The Patient |
TA-CVH29 |
Case Version |
Pregnant |
Patient Pregnant (Yes/No) |
TA-CVH30 |
Case Version |
Age Group |
Patient Age Group |
TA-CVH31 |
Case Version |
Ethnicity |
Ethnicity Of The Patient |
TA-CVH32 |
Case Version |
Lactation |
Patient Breastfeeding |
TA-CVH33 |
Case Version |
Primary Reporter Type |
Type Of The First/Primary Reporter (Example: Physician, Consumer, Etc.) |
TA-CVH34 |
Case Version |
Primary Suspect Product |
Primary Suspect Product For A Case |
TA-CVH35 |
Case Version |
HCP |
Health Care Professional (Example: Physician, Nurse, Pharmacist) Flag. Primary Reporter Of The Case Is HCP Or Not. |
TA-CVH36 |
Case Version |
SUSAR |
Suspected Unexpected Serious Adverse Reaction Flag (0 / 1) |
TA-CVH37 |
Case Version |
Case Owner ID |
User Who Is Responsible For The Case Throughout Its Lifetime |
Case Version |
Case Owner Name |
||
TA-CVH38 |
Case Version |
Initial Receipt Date |
Date On Which Initial Case Information Was Received |
TA-CVH39 |
Case Version |
Version Receipt Date |
Date On Which Initial/follow-up Information Was Received |
TA-CVH40 |
Case Version |
Safety Receipt Date |
Case Version Receipt Date At Central Safety |
TA-CVH41 |
Case Version |
Clock Start Date |
Case Version Aware Date |
TA-CVH42 |
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) |
TA-CVH43 |
Case Version |
Lock Timestamp |
Case Is Ready For Submission. First Lock Date Of The Case Version. |
TA-CVH44 |
Case Version |
Case Deletion Date |
Date Of Case Deletion |
M-CVH01 |
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 |
M-CVH02 |
Case Version |
Lock Due Date |
Clock Start Date + Lock Target Days |
M-CVH03 |
Case Version |
Lock Due Due Date Soon |
Customizable By The Customer, By Default - 2 Days Lock Due Date - (2) |
M-CVH04 |
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. |
M-CVH05 |
Case Version |
Duration Clock-Start-To-Lock |
Interval Between Case Version Clock Start Date And Case Version Lock Datetime |
M-CVH06 |
Case Version |
Duration Version-Creation-To-Lock |
Interval Between Case Version Create DateTime And Case Version Lock Datetime |
M-CVH07 |
Case Version |
Avg Duration Clock-Start-To-Lock |
Average Interval between Case Version Clock Start Date and Case Version Lock DateTime |
M-CVH08 |
Case Version |
Avg Duration Version-Creation-To-Lock |
Average Interval Between Case Version Create DateTime And Case Version Lock DateTime |
M-CVH09 |
Case Version |
Min Duration Clock-Start-To-Lock |
Minimum Interval Between Case Version Clock Start Date And Case Version Lock DateTime |
M-CVH10 |
Case Version |
Min Duration Version-Creation-To-Lock |
Minimum Interval Between Case Version Create DateTime And Case Version Lock DateTime |
M-CVH11 |
Case Version |
Max Duration Clock-Start-To-Lock |
Maximum Interval Between Case Version Clock Start Date And Case Version Lock DateTime |
M-CVH12 |
Case Version |
Max Duration Version-Creation-To-Lock |
Maximum Interval Between Case Version Create DateTime And Case Version Lock DateTime |
M-CVH13 |
Case Version |
#Versions Locked Once |
Number Of Case Versions Received (Including All Versions) |
M-CVH14 |
Case Version |
#Sig Versions Locked Once |
Number Of Case Versions Received (Including Significant Versions Only) |
M-CVH15 |
Case Version |
#Versions Locked On-Time |
Number Of Case Versions Processed Within Target Days |
M-CVH16 |
Case Version |
%Versions Locked On-Time |
Percentage Of Case Versions Processed On Time |
M-CVH17 |
Case Version |
#Versions Locked Late |
Number Of Case Versions Processed Over Target Days |
M-CVH18 |
Case Version |
%Versions Locked Late |
Percentage Of Case Versions Not Processed Within Target Days |
TA-CRH03 |
Case Routing |
Enterprise ID |
CRO Enterprise (Client) ID |
TA-CRH04 |
Case Routing |
Routing Justification |
Case Routing Justification |
TA-CRH05 |
Case Routing |
Routing ID |
Workflow Sequence Number |
TA-CRH08 |
Case Routing |
Routing Timestamp From State |
The Timestamp On Which Case Was Routed From Workflow State |
TA-CRH09 |
Case Routing |
Routing Timestamp To State |
The Timestamp On Which Case Was Routed To Workflow State |
TA-CRH12 |
Case Routing |
Follow-up ID |
Follow-up Number Of The Case To Which This Routing Record Belongs. |
TA-CRH13 |
Case Routing |
Comments |
Routing Comments |
Case Routing |
Pseudo State |
||
TA-CRH03 |
Case Workflow State |
Enterprise ID |
CRO Enterprise (Client) Short Name |
TA-CRH05 |
Case Workflow State |
State Start Timestamp |
The First Timestamp When The Workflow State Was Started |
TA-CRH06 |
Case Workflow State |
State End Timestamp |
The Last Timestamp When The Workflow State Was Completed |
M-CWSH01 |
Case Workflow State |
#Time State Repeated |
The Number Of Times A Workflow State Is Repeated (Within A Case Version) |
M-CWSH02 |
Case Workflow State |
#Users Who Worked On State |
Number Of Distinct Users Who Worked On A Workflow State For A Case Version |
M-CWSH03 |
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) |
M-CWSH04 |
Case Workflow State |
Duration Within State |
Total Time The Case Spent In The Workflow State (Within A Case Version) |
M-CWSH05 |
Case Workflow State |
# Completed State |
Total Number Of Workflow States Completed |
M-CWSH06 |
Case Workflow State |
# Repeated State |
Total Count Of All The Workflow States That Were Repeated |
M-CWSH07 |
Case Workflow State |
% Repeated State |
Total Percentage Of All The Workflow States That Were Repeated |
M-CWSH08 |
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) |
M-CWSH09 |
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) |
M-CWSH10 |
Case Workflow State |
Average Duration Within State |
Average Time The Case Spent In The Workflow State (Within A Case Version) |
M-CWSH11 |
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) |
M-CWSH12 |
Case Workflow State |
Minimum Duration State-Start-To-End |
Minimum 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) |
M-CWSH13 |
Case Workflow State |
Minimum Duration Within State |
Minimum Time The Case Spent In The Workflow State (Within A Case Version) |
M-CWSH14 |
Case Workflow State |
Minimum Duration Version-Creation-To-State-End |
Minimum Time Taken To Accomplish A Workflow State After A Case Version Was Created (Including Any Repetition) |
M-CWSH15 |
Case Workflow State |
Maximum Duration State-Start-To-End |
Maximum 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) |
M-CWSH16 |
Case Workflow State |
Maximum Duration Within State |
Maximum Time The Case Spent In The Workflow State (Within A Case Version) |
M-CWSH17 |
Case Workflow State |
Maximum Duration Version-Creation-To-State-End |
Maximum Time Taken To Accomplish A Workflow State After A Case Version Was Created (Including Any Repetition) |
TA-PC01 |
Pending Cases |
Case ID |
Case Identification (Same As Manufacturer Code Number - MCN) |
Pending Cases |
Case Internal ID |
||
TA-PC02 |
Pending Cases |
Follow-up ID |
Latest Case Follow-up # (0,1,2,3...) |
TA-PC03 |
Pending Cases |
Follow-up Type |
Significant, Non-Significant - 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) |
TA-PC04 |
Pending Cases |
Enterprise ID |
CRO Enterprise (Client) Short Name |
TA-PC06 |
Pending Cases |
Case Creator ID |
Case Author (Who Created The Case) |
TA-PC07 |
Pending Cases |
Case Processing Site |
Site (For Example, Drug Safety Case Processing Location) |
TA-PC08 |
Pending Cases |
Country of Incidence |
Country Where The Adverse Event Occurred |
TA-PC09 |
Pending Cases |
Case Type |
Case Report Type (For Example, Spontaneous/Clinical/Literature, Etc.) |
TA-PC10 |
Pending Cases |
Compliance Classification |
7-day Case, 15-day Case, Non-Expedited Case |
TA-PC11 |
Pending Cases |
S/R/U Classification |
SAE Case, SAR Case, SUSAR Case |
TA-PC12 |
Pending Cases |
F/LT Classification |
Fatal / Life-Threatening Case |
TA-PC13 |
Pending Cases |
Pregnancy Classification |
Pregnancy Case |
TA-PC14 |
Pending Cases |
Source Classification |
Authority Case, Consumer Case, Literature Case |
TA-PC15 |
Pending Cases |
Confirmation Classification |
Medically Confirmed Case |
TA-PC16 |
Pending Cases |
Case Classification Group 7 |
Case Classification Group 7 |
TA-PC17 |
Pending Cases |
Case Classification Group 8 |
Case Classification Group 8 |
TA-PC18 |
Pending Cases |
Case Classification Group 9 |
Case Classification Group 9 |
TA-PC19 |
Pending Cases |
Case Classification Group 10 |
Case Classification Group 10 |
TA-PC20 |
Pending Cases |
Product Type |
Drug, Device and Vaccine |
TA-PC21 |
Pending Cases |
Study Type |
Study ID |
TA-PC22 |
Pending Cases |
Center ID |
Center ID for the Study in the case version |
TA-PC23 |
Pending Cases |
Case Seriousness |
Case Serious Or Non-serious (Y/N) |
TA-PC24 |
Pending Cases |
Case Listedness |
Unlisted/Unexpected/Listed/Expected |
TA-PC25 |
Pending Cases |
Case Causality |
Adverse Event Caused By The Product (Related/Not Related) |
TA-PC26 |
Pending Cases |
Case Outcome |
Case Level Outcome Using The ICH Guidelines, Such As 'Recovered' |
TA-PC27 |
Pending Cases |
Gender |
Gender Of The Patient |
TA-PC28 |
Pending Cases |
Pregnant |
Patient Pregnant (Yes/No) |
TA-PC29 |
Pending Cases |
Age Group |
Patient Age Group |
TA-PC30 |
Pending Cases |
Ethnicity |
Ethnicity Of The Patient |
TA-PC31 |
Pending Cases |
Lactation |
Patient Breastfeeding |
TA-PC32 |
Pending Cases |
Primary Reporter Type |
Type Of Reporter (Example: Physician, Consumer, Etc.) |
TA-PC33 |
Pending Cases |
Primary Suspect Product |
Primary Suspect Product For A Case |
TA-PC34 |
Pending Cases |
HCP |
Health Care Professional (Such As Physician, Nurse, Pharmacist) Flag. Primary Reporter Of The Case Is Hcp Or Not |
TA-PC35 |
Pending Cases |
SUSAR |
Suspected Unexpected Serious Adverse Reaction Flag (0 / 1) |
TA-PC36 |
Pending Cases |
Initial Receipt Date |
Date On Which Initial Case Information Was Received |
TA-PC37 |
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 |
TA-PC40 |
Pending Cases |
Clock Start Date |
Case Version Aware Date |
TA-PC50 |
Pending Cases |
Case Deletion Date |
Deletion Date Of The Case |
TA-PC41 |
Pending Cases |
Case Owner |
User Who Is Responsible For The Case Throughout Its Lifetime |
TA-PC42 |
Pending Cases |
Assigned User |
User Assigned To The Current Work |
TA-PC43 |
Pending Cases |
User With Case Open |
Case Worklist User. User Who Has The Case Open (It May Be Null If Case Is Not Open) |
TA-PC44 |
Pending Cases |
Routing Justification |
Case Routing Justification For The Latest Routing |
TA-PC45 |
Pending Cases |
Routing ID |
Workflow Sequence Number |
TA-PC46 |
Pending Cases |
Routing Timestamp |
The Timestamp On Which Case Was Routed To Current Workflow State |
TA-PC47 |
Pending Cases |
Start Name |
Workflow State To Which Case Was Promoted From Previous Workflow State |
TA-PC48 |
Pending Cases |
Routing User |
The User To Whom The Case Has Been Routed In The Current Workflow State |
TA-PC49 |
Pending Cases |
Routing User Group |
User Group To Which A Case Is Routed. |
M-PC01 |
Pending Cases |
Earliest Exp Report Due Date |
Earliest Due Date By External(Regulatory/sla) For Expedited Report For This Case Version. This Measure Will Have Null Value If There Are No Reports Due For This Case Version. |
M-PC02 |
Pending Cases |
Earliest Exp Report Due Soon Date |
Earliest Due Date By Internal Process For Expedited Report For This Case Version. This Measure Will Have Null Value If There Are No Reports Due For This Case Version. |
M-PC03 |
Pending Cases |
Earliest Exp Report On-Time Indicator |
Earliest Expedited Report Submission On-time Indicator. The Values Here Are Red, Yellow And Green. |
M-PC04 |
Pending Cases |
Lock Target Days |
Target Days For Processing A Case Version Which Is Number Of Days From Clock Start Date To Lock Datetime. |
M-PC05 |
Pending Cases |
Lock Due Date |
Due Date For Locking A Case (Case Processing Completion) |
M-PC06 |
Pending Cases |
Lock Due Soon Date |
Internal Due Date For Locking A Case (Case Processing Completion) |
M-PC07 |
Pending Cases |
Lock On-Time Indicator |
Case Version Lock On-time Indicator. The Values Here Are Red, Yellow And Green. |
M-PC08 |
Pending Cases |
Start Due Date - I |
Due Date For Completion Of A Workflow State By Static Workflow Method |
M-PC09 |
Pending Cases |
Start Due Soon Date - I |
Due Soon Date For Completion Of A Workflow State By Static Workflow Method |
M-PC10 |
Pending Cases |
Start On-Time Indicator - I |
Workflow State On-Time Indicator by Static Workflow Method. The values here are Red, Yellow and Green |
M-PC11 |
Pending Cases |
Start Due Date - II |
Due Date For Completion Of A Workflow State By Dynamic Workflow Method |
M-PC12 |
Pending Cases |
Start Due Soon Date - II |
Due Soon Date For Completion Of A Workflow State By Dynamic Workflow Method |
M-PC13 |
Pending Cases |
Start On-Time Indicator - II |
Workflow State On-time Indicator By Dynamic Workflow Method. The Values Here Are Red, Yellow And Green |
M-PC14 |
Pending Cases |
Case-Open-By-User Indicator |
Case Currently Worked Upon By Any User |
M-PC15 |
Pending Cases |
# Unlocked Cases w Expedited Report On-Time |
Count Of Open Cases With Expedited Report On-time (Where Earliest Exp Report On-time Indicator Is Green) |
M-PC16 |
Pending Cases |
# Unlocked Cases w Expedited Report Due Soon |
Count Of Open Cases With Expedited Report Due Soon (Where Earliest Exp Report On-time Indicator Is Yellow) |
M-PC17 |
Pending Cases |
# Unlocked Cases w Expedited Report Overdue |
Count Of Open Cases With Expedited Report Overdue (Where Earliest Exp Report On-time Indicator Is Red) |
M-PC18 |
Pending Cases |
# Unlocked Cases On-Time |
Count Of Open Cases Where Case Processing Is On-time (Lock On-time Indicator Is Green) |
M-PC19 |
Pending Cases |
# Unlocked Cases Due Soon |
Count Of Open Cases Where Case Processing Is Due Soon (Lock On-time Indicator Is Yellow) |
M-PC20 |
Pending Cases |
# Unlocked Cases Overdue |
Count Of Open Cases That Have Not Been Completed Past Due Date (Lock On-time Indicator Is Red) |
M-PC21 |
Pending Cases |
# Unlocked Cases w State On-Time - I |
Count Of Cases With Workflow State Completion On-time By Static Workflow Method |
M-PC22 |
Pending Cases |
# Unlocked Cases w State Due Soon - I |
Count Of Cases With Workflow State Completion Due Soon By Static Workflow Method |
M-PC23 |
Pending Cases |
# Unlocked Cases w State Overdue - I |
Count Of Cases With Workflow State Not Been Completed Past Due Date By Static Workflow Method |
M-PC24 |
Pending Cases |
# Unlocked Cases w State On-Time - II |
Count Of Cases With Workflow State Completion On-time By Dynamic Workflow Method |
M-PC25 |
Pending Cases |
# Unlocked Cases w State Due Soon - II |
Count Of Cases With Workflow State Completion Due Soon By Dynamic Workflow Method |
M-PC26 |
Pending Cases |
# Unlocked Cases w State Overdue - II |
Count of cases with Workflow State not been completed past due date by Dynamic Workflow method |
M-PC27 |
Pending Cases |
# Unlocked Cases |
Total Number of Pending Cases |
Table B-3 describes how OPVA populates each column from an Oracle Argus Safety database. Each Measure has an ID number, which is provided for cross-reference to other tables in this Appendix. To display the information compactly, Table B-3 uses aliases for table names.
Table B-3 Presentation Catalog - Oracle Argus Safety Sources
ID | Source Table/Column | Comments/Details |
---|---|---|
TA-CVH01 |
case_master.case_num |
|
TA-CVH02 |
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_master.case_id |
||
TA-CVH03 |
No Followup: If there is no followup between two Version Lock DateTimeSignificant: If there is atleast 1 significant followup between two Version Lock DateTimeNon-Significant: If there is followup but no significant followup between two Version Lock DateTime |
|
TA-CVH04 |
cfg_enterprise.enterprise_abbrv |
|
TA-CVH05 |
If Version = 1 then Initial Else Follow-up |
|
TA-CVH06 |
case_master.deleted |
|
TA-CVH07 |
case_master.user_id |
|
cfg_users.user_fullname |
||
TA-CVH08 |
case_master.site_id |
|
TA-CVH09 |
case_master.country_id |
|
TA-CVH10 |
case_master.Report_type |
|
TA-CVH11 |
..where lm_case_classification.description has values in (7-day Case, 15-day Case, Non-Expedited Case) |
|
TA-CVH12 |
..where lm_case_classification.description has values in (SAE Case, SAR Case, SUSAR Case) |
|
TA-CVH13 |
..where lm_case_classification.description has values in (Fatal / Life-Threatening Case) |
|
TA-CVH14 |
..where lm_case_classification.description has values in (Pregnancy Case) |
|
TA-CVH15 |
..where lm_case_classification.description has values in (Authority Case, Consumer Case, Literature Case) |
|
TA-CVH16 |
..where lm_case_classification.description has values in (Medically Confirmed Case) |
|
TA-CVH17 |
..where lm_case_classification.description has values in (Case Classification 7) |
|
TA-CVH18 |
..where lm_case_classification.description has values in (Case Classification 8) |
|
TA-CVH19 |
..where lm_case_classification.description has values in (Case Classification 9) |
|
TA-CVH20 |
..where lm_case_classification.description has values in (Case Classification 10) |
|
TA-CVH21 |
case_product.views_available |
|
TA-CVH22 |
case_study.study_num |
|
TA-CVH23 |
lm_centers.center_no |
|
TA-CVH24 |
case_assess.seriousness |
|
TA-CVH25 |
case_assess.listedness |
|
TA-CVH26 |
case_assess.agent_suspect |
|
TA-CVH27 |
case_assess.outcome |
|
TA-CVH28 |
case_pat_info.gender_id |
|
TA-CVH29 |
case_pat_info.pat_stat_preg |
|
TA-CVH30 |
case_pat_info.age_group_id |
|
TA-CVH31 |
case_pat_info.ethnicity_id |
|
TA-CVH32 |
case_pregnancy.breastfeeding |
|
TA-CVH33 |
case_reporters.reporter_type |
|
TA-CVH34 |
case_product.product_name |
|
TA-CVH35 |
case_reporters.hcp_flag |
|
TA-CVH36 |
case_master.susar |
|
TA-CVH37 |
case_master.worklist_owner_id |
|
cfg_users.user_fullname |
||
TA-CVH38 |
case_master.init_rept_date |
|
TA-CVH39 |
case_followup.receipt_date (Case_Master.init_rept_date for Initial case version) |
|
TA-CVH40 |
case_followup.safety_date |
|
TA-CVH41 |
Max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
TA-CVH42 |
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 |
|
TA-CVH43 |
case_routing.route_date where case_routing.comment_text like 'case locked%' or case_routing.to_state_id = 2 |
|
TA-CVH44 |
case_master.deleted |
|
M-CVH01 |
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 |
|
M-CVH02 |
Clock Start Date + Lock Target Days |
|
M-CVH03 |
Customizable By The Customer, By Default - 2 Days Lock Due Date - (2) |
|
M-CVH04 |
days between (Safety Receipt Date, Version Receipt Date) |
|
M-CVH05 |
days between (Clock Start Date, Lock Timestamp) |
|
M-CVH06 |
days between (Version Creation Timestamp, Lock Timestamp) |
|
M-CVH07 |
Sum(Duration Clock-Start-To-Lock)/# Versions Locked Once |
|
M-CVH08 |
Sum(Duration Version-Creation-To-Lock)/# Versions Locked Once |
|
M-CVH09 |
Minimum(Duration Clock-Start-To-Lock) |
|
M-CVH10 |
Minimum(Duration Version-Creation-To-Lock) |
|
M-CVH11 |
Maximum(Duration Clock-Start-To-Lock) |
|
M-CVH12 |
Maximum(Duration Version-Creation-To-Lock) |
|
M-CVH13 |
Count (Number of records from Case Version History) |
|
M-CVH14 |
Count (Number of records from Case Version History) where Version Type = Significant |
|
M-CVH15 |
Lock Timestamp - Clock Start Date <= Lock Target Days |
|
M-CVH16 |
(# Versions Locked On-Time/# Versions Locked Once) * 100 |
|
M-CVH17 |
Lock Timestamp - Clock Start Date > Lock Target Days |
|
M-CVH18 |
(# Versions Locked Late/# Versions Locked Once) * 100 |
|
TA-CRH03 |
cfg_enterprise.enterprise_abbrv |
|
TA-CRH04 |
case_routing.justification_id |
|
TA-CRH05 |
case_routing.seq_num |
|
TA-CRH08 |
case_routing.route_date |
|
TA-CRH09 |
case_routing.route_date |
|
TA-CRH12 |
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) |
|
TA-CRH13 |
case_routing.comment_text |
Routing Comments |
Derived Column: 2. There are three Pseudo states – a. Locked – Whenever there is a lock, 'Routing From State' shall be TO_STATE_ID of case and 'Routing To State' shall be 'Locked'.b. F/U Data Entry - Whenever there is Unlock, 'Routing From State' shall be 'F/U Data Entry' and 'Routing To State' shall be TO_STATE_ID of the routing record immediately after the unlock.c. Unarchived – Whenever case is unarchived, 'Routing From State' shall be FROM_STATE_ID of case and 'Routing To State' shall be 'Unarchived'. |
||
TA-CRH03 |
cfg_enterprise.enterprise_abbrv |
|
TA-CRH05 |
||
TA-CRH06 |
The Last Timestamp When The Workflow State Was Completed |
|
M-CWSH01 |
Count(*) -1Group 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 |
|
M-CWSH02 |
count(Distinct "Case Routing History".Routing User) Group by ("Case Routing History"."Case ID", "Version", "State Routed From") |
|
M-CWSH03 |
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 |
|
M-CWSH04 |
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) |
|
M-CWSH05 |
count (State Name) If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result is 3 |
|
M-CWSH06 |
count (State Name) where "# Times State Repeated" > 0If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result is 2 |
|
M-CWSH07 |
(# Repeated States / # Completed States ) * 100If case goes through workflow states A -> B -> A -> B -> A -> B -> C, then result is 66.6% |
|
M-CWSH08 |
State End Timestamp - Version Creation Timestamp |
|
M-CWSH09 |
total(Duration State-Start-To-End) for all case versions / # Versions Locked Once |
|
M-CWSH10 |
total(Duration Within State) for all case versions / # Versions Locked Once |
|
M-CWSH11 |
total(Duration Version-Creation-To-State-End) for all case versions / # Versions Locked Once |
|
M-CWSH12 |
minimum(Duration State-Start-To-End) |
|
M-CWSH13 |
minimum(Duration Within State) |
|
M-CWSH14 |
minimum(Duration Version-Creation-To-State-End) |
|
M-CWSH15 |
maximum(Duration State-Start-To-End) |
|
M-CWSH16 |
maximum(Duration Within State) |
|
M-CWSH17 |
maximum(Duration Version-Creation-To-State-End) |
|
AD06-01 |
cfg_groups.group_name |
|
TA-PC01 |
case_master.case_num |
|
case_master.case_id |
||
TA-PC02 |
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 |
TA-PC03 |
case_followup.significant |
If Case_followup.Significant = 1 then 'Significant' else 'Non-Significant'. If the latest case version is Initial then 'Significant' |
TA-PC04 |
cfg_enterprise.enterprise_abbrv |
|
TA-PC06 |
case_master.user_id |
|
TA-PC07 |
case_master.site_id |
|
TA-PC08 |
case_master.country_id |
|
TA-PC09 |
case_master.report_type |
|
TA-PC10 |
lm_case_classification.description |
..where lm_case_classification.description has values in (7-day Case, 15-day Case, Non-Expedited Case) |
TA-PC11 |
lm_case_classification.description |
..where lm_case_classification.description has values in (SAE Case, SAR Case, SUSAR Case) |
TA-PC12 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Fatal / Life-Threatening Case) |
TA-PC13 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Pregnancy Case) |
TA-PC14 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Authority Case, Consumer Case, Literature Case) |
TA-PC15 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Medically Confirmed Case) |
TA-PC16 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Case Classification 7) |
TA-PC17 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Case Classification 8) |
TA-PC18 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Case Classification 9) |
TA-PC19 |
lm_case_classification.description |
..where lm_case_classification.description has values in (Case Classification 10) |
TA-PC20 |
case_product.views_available |
|
TA-PC21 |
case_study.study_num |
|
TA-PC22 |
lm_centers.center_no |
where case_study.center_id = lm_centers.center_id |
TA-PC23 |
case_assess.seriousness |
|
TA-PC24 |
case_assess.listedness |
|
TA-PC25 |
case_assess.agent_suspect |
|
TA-PC26 |
case_assess.outcome |
|
TA-PC27 |
case_pat_info.gender_id |
|
TA-PC28 |
case_pat_info.pat_stat_preg |
|
TA-PC29 |
case_pat_info.age_group_id |
|
TA-PC30 |
case_pat_info.ethnicity_id |
|
TA-PC31 |
case_pregnancy.breastfeeding |
|
TA-PC32 |
case_reporters.reporter_type |
where primary_contact = 1 |
TA-PC33 |
case_product.product_name |
where case_product.first_sus_prod = 1 |
TA-PC34 |
case_reporters.hcp_flag where |
primary_contact = 1 |
TA-PC35 |
case_master.susar |
|
TA-PC36 |
case_master.init_rept_date |
|
TA-PC37 |
case_followup.receipt_date |
|
TA-PC40 |
max(case_master.init_rept_date/(case_followup.receipt_date where significant = 1)) |
|
TA-PC50 |
case_master.deleted |
|
TA-PC41 |
case_master.worklist_owner_id |
|
TA-PC42 |
case_master.owner_id |
|
TA-PC43 |
web_cases_open.user_id |
|
TA-PC44 |
case_routing.justification_id |
|
TA-PC45 |
case_routing.seq_num |
|
TA-PC46 |
case_routing.route_date |
|
TA-PC47 |
cfg_workflow_states.state_name |
where case_routing.to_state_id = cfg_workflow_states.state_id |
TA-PC48 |
case_routing.user_id |
|
TA-PC49 |
cfg_workflow_rules.group_id |
where cfg_workflow_rules.seq_num = case_routing.workflow_seq_num |
M-PC01 |
case_master.due_soon |
|
M-PC02 |
case_master.due_soon - (2) |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days |
M-PC03 |
when due_soon - due_soon_val > sysdate then 1; when sysdate between (due_soon - due_soon_val) and due_soon then 2; when sysdate > cvd.due_soon then 3 |
|
M-PC04 |
It is customizable by the customer. They can write the logic to populate this column. By default, if Case Serious then 10 Days, else 30 Days |
|
M-PC05 |
clock_start_date + lock_target_days |
|
M-PC06 |
It shall be customizable by the customer, they shall be able to write the logic to populate this column. By default - 2 Days Pending Cases Fact. Lock Due Date - (2) |
|
M-PC07 |
when (clock_start_date + lock_target_days - due_soon_val) > sysdate then 1when sysdate between (clock_start_date + lock_target_days - due_soon_val) and (clock_start_date + lock_target_days) then 2when sysdate > (clock_start_date + lock_target_days) then 3 |
|
M-PC08 |
case_routing.route_date + cfg_workflow_rules.max_time |
|
M-PC09 |
nvl(to_number(to_char(routing_timestamp + max_time, 'RRRRMMDD')), -1) |
|
M-PC10 |
when routing_timestamp + normal_time > sysdate then 1when sysdate between (routing_timestamp + normal_time) and (routing_timestamp + max_time) then 2when sysdate > (routing_timestamp + max_time) then 3 |
|
M-PC11 |
routing_timestamp + ((case due_soon - trunc(due_soon)when 0 then (due_soon + (86399 / 86400)) else cvd.due_soon - case when followup_max_time_stamp > nvl(create_time, followup_max_time_stamp - 1) then followup_max_time_stampelse reate_time) * (state_unit/total_state_units)) case_dyn_due_date_2, nvl(to_number(to_char(cvd.routing_timestamp + ((case cvd.due_soon - trunc(cvd.due_soon)when 0 then (cvd.due_soon + (86399 / 86400)) else due_soon" |
|
M-PC12 |
routing_timestamp - due_soon_val +((case due_soon - trunc(due_soon) when 0 then (due_soon + (86399 / 86400))else due_soon - case when followup_max_time_stamp > nvl(create_time, followup_max_time_stamp - 1) thenfollowup_max_time_stamp else create_time) * (state_unit/total_state_units) |
|
M-PC13 |
when state due date - II > current_date then 1 when current_date between state due soon date - II and state due date - II then 2when current_date > state due soon date - II then 3 |
|
M-PC14 |
web_cases_open.case_id |
|
M-PC15 |
sum(if due soon >= sysdate then 1 else 0) |
|
M-PC16 |
sum(if due soon - due soon val >= sysdate then 1 else 0 ) |
|
M-PC17 |
sum(if due soon < sysdate then 1 else 0) |
|
M-PC18 |
sum(if clock start date + lock target days - due soon val) >= sysdate then 1 else 0) |
|
M-PC19 |
sum(if sysdate between (clock start date + lock target days - due soon val) and (clock start date + lock target days) then 1 else 0) |
|
M-PC20 |
sum(if (clock start date + lock target days) < sysdate then 1 else 0) |
|
M-PC21 |
sum(if (routing timestamp + normal time) >= sysdate then 1 else 0) |
|
M-PC22 |
sum(if sysdate between (routing timestamp + normal time) and (routing timestamp + max time) then 1 else 0) |
|
M-PC23 |
sum(if (routing timestamp + max time) < sysdate then 1 else 0) |
|
M-PC24 |
sum(dynamic case on time) |
|
M-PC25 |
sum(dynamic case due soon) |
|
M-PC26 |
sum(dynamic case overdue) |
|
M-PC27 |
count(case when not case_internal_id is null then 1 end) |