Facts and Sources in Oracle Argus Analytics Presentation Catalog

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 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
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 - 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)	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. (It May Be Null If Case Is Not Open)	web_cases_open.user_id	--
Pending Cases	User With Case Open Name	Case Worklist User. User Who Has The Case Open (It May Be Null If Case Is Not 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) [Number of reports overdue for submission]	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