Search Results ams_act_wf_requests_pk

Overview

AMS_ACT_WF_REQUESTS is an Oracle Marketing (AMS) transactional table that stores every request submitted to Oracle Workflow in support of the marketing approval process. When a marketing activity, campaign, or change request is routed for approval, the application inserts a row here to track the workflow instance, the submitting user, the approval routing type, and the current status of the underlying approval transaction. The table therefore functions as the persistence layer between Oracle Marketing's approval business logic and the Oracle Workflow engine (WF). It is owned by the AMS schema and is valid in both Oracle E-Business Suite 12.1.1 and 12.2.2, with 20 documented columns and a single-column primary key.

The ETRM metadata classifies this object, heuristically, as satellite-leaning within a Data Vault model. In that modeling convention, it would most naturally be treated as a satellite attached to a parent approval or change-request hub, since it captures descriptive, time-stamped attributes (status, workflow item key, notes) about an approval transaction rather than acting as a pure associative link. This is a modeling suggestion, not a mandatory design constraint.

Key Information Stored

The surrogate primary key is ACTIVITY_WF_REQUEST_ID, enforced by AMS_ACT_WF_REQUESTS_PK and also exposed as a unique business-key candidate through AMS_ACT_WF_REQUESTS_U1. The most significant columns include:

• ACTIVITY_WF_REQUEST_ID — the unique workflow request identifier and primary key.
• ACT_WF_REQ_SUBMITTED_FOR_ID and ARC_ACT_WF_REQ_SUBMITTED_FOR — identify the object (and its archive context) for which approval was requested.
• SUBMITTED_BY_USER_ID — foreign key to FND_USER, recording who submitted the approval request.
• REQUEST_TYPE and APPROVAL_TYPE — classify the request and the approval routing style.
• WORKFLOW_ITEM_KEY and WORKFLOW_PROCESS_NAME — the Oracle Workflow item key and process definition name used to correlate the AMS record with WF runtime tables.
• USER_STATUS_ID — foreign key to AMS_USER_STATUSES_B, carrying the user-defined status value.
• STATUS_CODE and STATUS_DATE — the current workflow status and the timestamp of the last status transition.
• DESCRIPTION and NOTES — free-text context supplied with the request.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS supporting multi-organization data security.
• Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical scenarios include auditing pending approvals, identifying stalled workflow requests, and reporting approval turnaround times. A common pattern joins to FND_USER to resolve the submitter and to AMS_USER_STATUSES_B for the status label:

• List all pending approval requests for a given submitter, filtered on STATUS_CODE.
• Reconcile AMS approval records against Oracle Workflow runtime tables using WORKFLOW_ITEM_KEY and WORKFLOW_PROCESS_NAME.
• Report average time between CREATION_DATE and STATUS_DATE to measure approval cycle time.
• Join to AMS_ACT_CHANGE_REQ_ITEMS on ACTIVITY_WF_REQUEST_ID to trace which change-request items drove each workflow submission.

Related Objects

The documented relationships identify the following significant objects:

• FND_USER — joined via AMS_ACT_WF_REQUESTS.SUBMITTED_BY_USER_ID.
• AMS_USER_STATUSES_B — joined via AMS_ACT_WF_REQUESTS.USER_STATUS_ID.
• FND_SECURITY_GROUPS — joined via AMS_ACT_WF_REQUESTS.SECURITY_GROUP_ID.
• AMS_ACT_CHANGE_REQ_ITEMS — references this table through AMS_ACT_CHANGE_REQ_ITEMS.ACTIVITY_WF_REQUEST_ID.

Together these relationships place AMS_ACT_WF_REQUESTS at the center of the Oracle Marketing approval workflow chain, linking request metadata to users, statuses, security groups, and the change-request items that initiate approvals.