Results for “resource_status”

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

Overview

The view APPS.AST_RS_CAMPAIGN_SCHEDS_V belongs to the Oracle Advanced Scheduler (AST) TeleSales product family. It presents a consolidated, active-only snapshot of resource assignments to TeleSales campaign schedules, joining resource master data with campaign schedule definitions and the assignment intersection entity. Its principal role in Oracle EBS 12.1.1 and 12.2.2 is as a reporting and integration surface: rather than querying the underlying transactional tables directly, functional users, BI Publisher reports, and outbound integrations interrogate this view to answer the question of which TeleSales resources are currently assigned to which active campaign schedules, and for what effective period.

A defining characteristic of the view is that it is a "current state" view rather than a historical one. The view text applies TRUNC(SYSDATE) predicates against both the resource effective dating and the schedule start/end date-times, so only rows that are valid as of the day the query executes are returned. It is also restricted to SCH.ACTIVITY_ID = 460 and SCH.ACTIVE_FLAG = 'Y', narrowing output to a single campaign activity classification and to active schedules only.

Underlying Base Objects

The documented base objects referenced by the view are:

• JTF_RS_EMP_DTLS_VL (VIEW) — the resource/employee detail value-list view, aliased RS, supplying resource number, full name, and effective start/end dates.
• AMS_CAMPAIGN_SCHEDULES_VL (VIEW) — the campaign schedule value-list view, aliased SCH, supplying schedule name, status code, source code, activity identification, and schedule date-time boundaries.
• AST_RS_CAMPAIGNS (SYNONYM) — aliased ASSG, the TeleSales resource-to-campaign assignment entity that supplies the assignment start date, end date, status, and enabled flag. In the APPS schema this is a synonym resolving to the underlying AST_RS_CAMPAIGNS table.

Join logic is defined by three conditions: RS.RESOURCE_ID = ASSG.RESOURCE_ID links the resource to its assignment; ASSG.CAMPAIGN_ID = SCH.SCHEDULE_ID links the assignment to the schedule; and the two TRUNC(SYSDATE) BETWEEN ... predicates enforce current validity on both the resource record and the schedule window. The NVL wrapping around the boundary columns means a null effective or end date is treated as open-ended, so such rows remain visible.

Key Columns

The view exposes sixteen columns, several of which carry an alias differing from the underlying source column name:

• RS_CAMPAIGN_ID, SCHEDULE_ID, SCHEDULE_NAME — identify the campaign schedule and its relationship to the TeleSales campaign.
• RESOURCE_ID, RESOURCE_NUMBER, RESOURCE_FULL_NAME — the assigned resource's identifier, employee/resource number, and display name.
• RESOURCE_START_DATE, RESOURCE_END_DATE — the assignment window, projected from ASSG.START_DATE and ASSG.END_DATE. This pair is the most common target of ad hoc filtering, and it is the column family implicated by searches for "resource_end_date."
• RESOURCE_STATUS, RESOURCE_ENABLED_FLAG — projected from the assignment's STATUS and ENABLED_FLAG, indicating assignment state.
• SCHEDULE_STATUS_CODE, SCHEDULE_SOURCE_CODE — schedule lifecycle status and the originating source of the schedule.
• RESOURCE_EFFECTIVE_START_DATE, RESOURCE_EFFECTIVE_END_DATE — the HR-style effective dating of the resource record itself, distinct from the assignment dates.
• ACTIVITY_ATTRIBUTE4 — a flexfield-style descriptive attribute carried through from the schedule, used for client-specific classification.

Common Use Cases and Queries

The most frequent scenario is identifying which resources are assigned to currently active TeleSales campaign schedules, and isolating assignments ending within a given horizon. Because the view is already SYSDATE-filtered, the RESOURCE_END_DATE predicate is applied against an already-restricted row set:

• Assignments expiring soon:
  SELECT resource_id, resource_full_name, schedule_name, resource_start_date, resource_end_date FROM apps.ast_rs_campaign_scheds_v WHERE resource_end_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 30 ORDER BY resource_end_date;
• Open-ended assignments: filtering on resource_end_date IS NULL returns resources with no defined termination on the campaign.
• Resource roster by campaign: selecting resource_number, resource_full_name, and resource_status grouped by schedule_id or schedule_name.
• Effective-date reconciliation: comparing resource_effective_end_date against resource_end_date to detect assignments that outlive the resource record.

Because the view depends on the AMS_CAMPAIGN_SCHEDULES_VL and JTF_RS_EMP_DTLS_VL value-list views in addition to local tables, query performance is sensitive to the effective-date and translation joins within those views, and users should apply additional selective predicates (resource, schedule, or campaign) rather than relying on full-table scans of the assignment entity.