Currency_Function

(Optional; applies to Load Sets only. Oracle LSH does this automatically for Programs and Data Marts.) Use this function to determine the currency of the data in the external system. If you define a currency funtion, the system automatically invokes it when the job is executed and uses it to determine whether to run the job.

The currency function must return the source data currency. Oracle LSH job processing logic then determines whether the currency of the previous successful job was the same as the current job. If so, it marks the current job as duplicate and stops the job (unless the Force Execution flag is set to Yes by the Force Execution system Parameter). If the currency values are different, then the system proceeds to execute the job; see "Planning for Object Execution".

One of the system Parameters available for submitting a job is Currency_Type, whose value indicates whether the job will take the most current data or use a data snapshot. The default is to use the most current data. If there is no currency function available and the parameter value is for current data, the system processes the most current data.

The following shipped Oracle Clinical Load Set adapters use a currency function: Data Extract Oracle Views, Data Extract SAS Views, Labs, and Global Metadata.

This procedure has the following required signature:

PROCEDURE getCurrency(pi_nCompanyID        IN NUMBER
  ,pi_nPrrefId          IN NUMBER
  ,pi_nPrrefVer         IN NUMBER
  ,pi_cRunParamNVPair   IN CDR_NAME_VALUE_PAIR_COLL
  ,pi_cSysParamNVPair   IN CDR_NAME_VALUE_PAIR_COLL
  ,po_cCurrencyListColl OUT NOCOPY CDR_CURRENCY_LIST_COLL);

The procedure's parameters take values as follows:

• company_id see "Getting Your Company ID"

• prref_id see "Getting an Object's Prref_Id and Prref_Ver"

• prref_ver see "Getting an Object's Prref_Id and Prref_Ver"

• pi_cRunParamNVPair is a collection with a name,value pair for each runtime Parameter for the job that is passed to the currency function. For example, Oracle Clinical Labs Load Sets pass the remote location and lab name to the adapter's currency function.

• pi_cSysParamNVPair is a collection with a name,value pair for each predefined system Parameter for the job that is passed to the currency function. System Parameters include currency type, job priority, force execution, and more. For a complete list and descriptions, see the Oracle Life Sciences Data Hub User's Guide.

• po_cCurrencyListColl this output parameter is a collection of type CDR_CURRENCY_LIST_COLL, which is a table of CDR_CURRENCY_OBJ_TYPE that has the following elements:

  • VCCURRVALUE VARCHAR2(4000)

  • DTCURRVALUE DATE

  • NUMCURRVALUE NUMBER

  The adapter can return how current the data in the remote system is by either returning a character, date or number currency or any combination of the three. For example, the Oracle Clinical Labs currency function fetches the max date value from the tables labs, lab_range_subsets and ranges.