Monday, December 15, 2008

Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table


A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting


C - Completed
I - Inactive
P - Pending
R - Running

Monday, December 8, 2008

DateTrack History view

The name of the default DateTrack History view is the same as that of the base table, except that the suffix _F is replaced by _D. For example, if the base table is PER_ALL_PEOPLE_F, the application looks for a view called PER_ALL_PEOPLE_D.

Note: It is possible to define more than one History view for each datetracked table, so there might be examples where the History view name does not follow this naming convention.

When a view exists, the application reads the information about the entity name and column prompts from the DateTrack tables:

If the column information is not available in the DT_COLUMN_PROMPTS_TL table, the information is obtained from the view definition. The DateTrack History code modifies the column names of the table or view before presenting them. Underscores are replaced by spaces and the first letter of each word appears in upper case.

Understanding the p_validate Control Parameter in HRMS API's

Every published API includes the p_validate control parameter. When this parameter is set to FALSE (the default value), the procedure executes all validation for that business function. If the operation is valid, the database rows/values are inserted or updated or deleted. Any non warning OUT parameters, warning OUT parameters and IN OUT parameters are all set with specific values.

When the p_validate parameter is set to TRUE, the API only checks that the operation is valid. It does so by issuing a savepoint at the start of the procedure and rolling back to that savepoint at the end. You do not have access to these internal savepoints. If the procedure is successful, without raising any validation errors, then non-warning OUT parameters are set to null, warning OUT parameters are set to a specific value, and IN OUT parameters are reset to their IN values.

In some cases we may want to write our own PL/SQL routines using the public API procedures as building blocks. This enables us to write routines specific to our business needs. For example, say that we have a business requirement to apply a DateTracked update to a row and then apply a DateTrack delete to the same row in the future. We could write an "update_and_future_del" procedure that calls two of the standard APIs.

When calling each standard API, p_validate must be set to false. If true is used the update procedure call is rolled back. So when the delete procedure is called, it is working on the non-updated version of the row. However when p_validate is set to false, the update is not rolled back. Thus, the delete call operates as if the user really wanted to apply the whole transaction.

If we want to be able to check that the update and delete operation is valid, you must issue your own savepoint and rollback commands. As the APIs do not issue any commits, there is no danger of part of the work being left in the database. It is the responsibility of the calling code to issue commits.

Note: You should not use our API procedure names for the savepoint names. An unexpected result may occur if you do not use different names.