B OPVA Presentation Catalog

The OPVA Presentation Catalog displays columns that you can use to create requests.

This appendix contains the following topics:

• Dimensions in OPVA Presentation Catalog

• Facts in OPVA Presentation Catalog

• Oracle Argus Safety Sources for OPVA Presentation Catalog

See Also:

Chapter 3, "Working with Reports"

B.1 Dimensions in OPVA Presentation Catalog

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

B.2 Facts in OPVA Presentation Catalog

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

B.3 Oracle Argus Safety Sources for OPVA Presentation Catalog

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)