1. User and Administrator's Guide
  2. Use Advanced Conditions to Create Point-in-Time Queries on Oracle Argus Mart

6 Use Advanced Conditions to Create Point-in-Time Queries on Oracle Argus Mart

Oracle Argus Insight also supports point-in-time queries for analysis of the historical case data based on specific date/time through Oracle Argus Mart.

To enable access to this data in Argus Mart, you need to set specific attributes.

See Table 12-1, for more information on these attributes.

Benefits of running queries on Oracle Argus Mart:

  • Enables you to create the advanced condition queries using the list of fields configured in the common fields table for Oracle Argus Mart data source. These advanced condition can be executed to generate case series.
  • Displays the Effective Start Date of the case revision in the resultant case series for all the case series generated on Oracle Argus Mart data.
  • Supports different type of queries for fetching the historical data while executing queries on Oracle Argus Mart data source.
    • Current Data Queries—Provides a consistent view of the most recent revisions available in Oracle Argus Safety regardless of whether the cases are locked or unlocked. These queries and query results are same as the existing queries created on Oracle Argus Insight data source that is, case data for cases present in the case series will be refreshed if a case changes in Oracle Argus Safety. Any reporting done on such case series/queries will always return the latest data related to cases in the case series.
    • As of Date (Historical Data)—Refers to the data that was current at some point in the past. This query type allows the use of an As of Date that is earlier than the most recent ETL high water mark and later than ETL low water mark. These queries uses the Effective Start Date and End Date of the locked and unlocked revisions.

      Note:

      • ETL High Watermark — Date when ETL was executed for the last time.
      • ETL Low Watermark — Date when ETL was executed for the first time.
    • At Lock—Provides the case data at one or more user lock actions. A typical use case for an At Lock query displays the data that was provided to local affiliates for review and possible submission. The query returns the revisions at the time of the user lock action. That is, the post-lock revisions are not included. The list of lock dates (and times) is provided by Oracle Argus Safety tables that record the user lock action. These queries use the valid Start Date and End Date of the locked revisions.
    • Last Locked Revision as of a Point in Time—Provides a consistent view of the most recent locked revisions that were committed to the database on or before the as-of date. The as-of date can be arbitrary (a date that may not correspond to any actual transaction). When there are post-lock revisions, the query returns any post-lock revision data valid at the given point-in-time. These queries return only a single revision of each case. The as-of date is the same for all cases (a single value for the query). These queries use the valid Start Date and End Date of the locked revisions.
    • Last Locked Revision for a Version in a Period—Provides a consistent view of the most recent locked revision for case versions in a time period. You can choose between Case Creation Date, Case Receipt Date, and Case Lock Date for determining the versions in a period. Regardless of the kind of date used for determining in period, the key point of this type of query is that the revision may have been committed to the database outside the period. However, revisions that belong to later versions of a case not included in the period are not returned. These queries return only a single revision of each case. The as-of date may vary by case (no single as-of date will be sufficient to return the needed revisions across all cases). These queries use the valid Start Date and End Date of the locked revisions.
    • Aggregate Queries—Provides a consistent view of the most recent locked revision for case versions for a specific time period based on the Case Receipt Date. You may also view the most recent unlocked revision with this type of query.
  • Enables saving the advanced conditions along with query type and As of Date for future use.
  • Supports all the existing features of advanced conditions with Oracle Argus Mart data source and point in time queries. For example, View SQL, Parameterized queries, etc.

For more information, see: