Thursday, August 11, 2016

Oracle Applications table suffix naming conventions

The simple rule of tables you would want to query:

No suffix (similar to a base table)

Base table. This is the primary table containing the base data - in theory language agnostic. An associated _TL table will have the translated display value (what will be displayed on the applications front end) for a specific code in the base table. If multi language has not been enabled there will be a one to one relationship between base and translation table. (see additional info below)
If Oracle apps has been configured for multi org then these tables will have the combined data of all operating units.
Views, in a lot of cases you may prefer to use the views which are a standardised of viewing data from more than one table
View with language information
View with resolved lookups instead of only showing the codes
    In some cases you might want to use these tables:
    Audit tables
    Audit view of what was changed, when it was changed and by who
    Tracked tables, each record associated with the same entity has a start and end date that won't overlap. Used in HR and Payroll.

    In most cases you will ignore unless there is a specfic requirements:
    Table with language, is associated with a base or unsuffixed tabled. Contains translations of terms (multi language) relating to the base table. One record in a base table could have many translations.

    Global temporary; data only visible by session owner. Will likely be populated when a process on the apps front end has been run, so most of the time you won't see any data in these tables.
    Is an interface or processing table, it will be populate with records that are to be processed into base and other tables.
    Analytical criteria sources; assuming if you have analytics enabled this ties the data to the associated analytic tables, otherwise they are not populated.
    History table; updated only when data from the related tables has been exported (via concurrent job I am assuming), you can trace back exactly what version / state the data was in at the time of export. Useful for analytics perhaps, when the data is being exported to populate a dimension for reporting.
    Standard interface or import table. Records are to be processed into base and other tables.
    Detail table, contains additional detail for a base table. E.g. address detail table will contain the extended geographic location details of the address table.

      Multi-language tables and views:
      _TL and _VL extend their associated tables with language information (Multi Language Support) if it is being used by Oracle Apps. For example the TL table contain one or more language indicators for a code so that when it is viewed on the front end it uses the associated values according to the correct language that Oracle Applications has been configured for.
      A simple example would be names of months, January could be code '1' and if language code = 'US' display value = 'January'; 'FR' display value = 'Janvier'; 'SP' display value = 'Enero etc.

      Monday, August 1, 2016

      Some Scheduled Requests Are Duplicated Or Stopped

      With Release 12.1.1, find that the submitted scheduled requests are getting duplicated or stop working.

      It is expected that the scheduled requests work fine with same schedule parameters.

      1. Navigate to Concurrent > Program > Define, query for "Workflow Background Process" program or any other program for which schedule is not working correctly.

      2. De-select the option "Restart on System Failure"

      3. Save the change and reschedule the request.