Results for “ams_tcop_requests_u2”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS.AMS_TCOP_REQUESTS is an Oracle Advanced Marketing (AMS) transactional table that stores scheduling requests known internally as "traffic cop" requests. Its documented purpose is to hold requests that must be scheduled so that fatigue rules can be applied to campaign activities. Each row represents a discrete unit of scheduling work placed against a campaign schedule, together with the request lifecycle status and the workflow context that originated it.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT FREE of 10, owned by the AMS schema and registered under FND Design Data as AMS.AMS_TCOP_REQUESTS. It is classified as VALID in ETRM 12.1.1 and 12.2.2. Under a heuristic Data Vault classification, the structure is satellite-leaning: REQUEST_ID functions as the surrogate primary key, while SCHEDULE_ID and the workflow attributes behave as descriptive, time-varying context around the request. Modelers treating this as a satellite would anchor REQUEST_ID as the hub key and treat STATUS, COMPLETION_DATE, and request timestamps as the sat ellited payload. The table does not reference any database object directly at the database level, though logical foreign keys are documented.

Key Information Stored

The table documents thirteen columns. The most significant are:

• REQUEST_ID (NUMBER, mandatory) — unique identifier for a traffic cop request; it is the surrogate primary key (AMS_TCOP_REQUESTS_PK) and the column of unique index AMS_TCOP_REQUESTS_U1.
• SCHEDULE_ID (NUMBER) — identifies the schedule that placed the request; it is the second business-key candidate, enforced by the unique index AMS_TCOP_REQUESTS_U2. Logically it references AMS.AMS_CAMPAIGN_SCHEDULES_B.
• REQUEST_DATE (DATE) — the date the request was raised.
• STATUS (VARCHAR2(30)) — the request status; it carries a non-unique index (AMS_TCOP_REQUESTS_N1) and is the primary filter for pending work.
• COMPLETION_DATE (DATE) — the date the request was completed, used to measure turnaround.
• WF_ITEM_TYPE (VARCHAR2(30)) — the type of workflow process that placed the request.
• WF_ITEM_ID (VARCHAR2(240)) — the instance identifier of the placing workflow; logically tied to GML_BATCH_SO_WORKFLOW.
• SECURITY_GROUP_ID (NUMBER) — hosting and multi-tenant separation, logically referencing FND_SECURITY_GROUPS.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording insert and update lineage.

The distinction between REQUEST_ID and SCHEDULE_ID matters: both are unique-indexed business-key candidates, but REQUEST_ID is the declared primary key and the reliable row identifier.

Common Use Cases and Queries

The dominant operational use case is monitoring the queue of scheduling requests awaiting fatigue-rule processing, and reconciling completed requests to their workflow origin. A typical pending-work query filters on STATUS with the N1 index:

• SELECT REQUEST_ID, SCHEDULE_ID, REQUEST_DATE, STATUS FROM AMS.AMS_TCOP_REQUESTS WHERE STATUS = :status;
• Turnaround reporting compares REQUEST_DATE with COMPLETION_DATE to derive elapsed processing time per schedule.
• Workflow tracing joins WF_ITEM_TYPE and WF_ITEM_ID back to GML_BATCH_SO_WORKFLOW for the originating process instance.
• Schedule-centric reporting joins SCHEDULE_ID to AMS_CAMPAIGN_SCHEDULES_B to enrich requests with campaign schedule detail.
• Access-control auditing groups rows by SECURITY_GROUP_ID to confirm hosting isolation.

Because AMS_TCOP_REQUESTS_U2 is unique on SCHEDULE_ID, at most one active traffic cop request is expected per schedule at a time, making the unique index the natural guard against duplicate scheduling.

Related Objects

• AMS.AMS_TCOP_REQUESTS as APPS synonym AMS_TCOP_REQUESTS — the runtime access path referenced in the dependency listing.
• AMS.AMS_CAMPAIGN_SCHEDULES_B — joined via SCHEDULE_ID to resolve the schedule that placed the request.
• GML_BATCH_SO_WORKFLOW — joined via WF_ITEM_ID to identify the placing workflow process instance.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for hosting and security-group resolution.
• AMS_TCOP_REQUESTS_PK, AMS_TCOP_REQUESTS_U1, AMS_TCOP_REQUESTS_U2, and AMS_TCOP_REQUESTS_N1 — the primary key and index structures that govern uniqueness and status-based retrieval.
• Workflow and fatigue-rule scheduling processes in AMS that consume pending STATUS rows represent the principal functional dependency.