Results for “cac_sr_publish_schedules”

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

Overview

The JTF.CAC_SR_PUBLISH_SCHEDULES table is a CRM Foundation (JTF) data object within Oracle E-Business Suite 12.1.1 and 12.2.2 that governs the publication and availability of resource schedules to downstream consumers, such as Resource Manager, iSupport, and interaction-center scheduling engines. Where CAC_SR_SCHEDULES_B stores the base definition of a schedulable resource calendar, CAC_SR_PUBLISH_SCHEDULES controls which specific schedule versions, shifts, or calendar components are actually exposed—or "published"—to a targeted business object. Each row therefore represents a publication event or association: a schedule made available against a particular customer, organization, resource, or other CRM entity for a defined active window.

The documented physical schema identifies 11 columns owned by the JTF schema. Based on the foreign-key structure—where the table references CAC_SR_SCHEDULES_B rather than being referenced by another hub—the heuristic Data Vault classification of this object is satellite-leaning. This should be treated as a modeling suggestion: it indicates that the table primarily carries descriptive, contextual, and validity attributes attached to a parent schedule business key, rather than acting as an independent hub or a pure transactional link.

Key Information Stored

The primary key of the table is CAC_SR_PUBLISH_SCHEDULES_PK, defined on the surrogate column PUBLISHED_SCHEDULE_ID. A unique index, CAC_SR_PUBLISH_SCHEDULES_U1, is also defined on PUBLISHED_SCHEDULE_ID, confirming it as the singular business-key candidate for the row.

• SCHEDULE_ID — foreign key to CAC_SR_SCHEDULES_B.SCHEDULE_ID; identifies the base schedule being published. This is the critical link column in every join.
• OBJECT_TYPE and OBJECT_ID — pair of columns that identify the target business object and its instance to which the schedule is published. Common JTF object types include resources, organizations, customers, and contacts.
• START_DATE_ACTIVE and END_DATE_ACTIVE — define the effective window during which the published schedule is active. These are the principal filters for date-range reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO/audit columns capturing creation and last-modification information for traceability and concurrency.

Common Use Cases and Queries

Typical reporting requirements include determining which schedules are currently published to a given object, auditing publication windows for scheduling accuracy, and reconciling published schedules against their base definitions. A representative query joins the publication table to the base schedule:

SELECT ps.published_schedule_id, ps.object_type, ps.object_id,
       ps.start_date_active, ps.end_date_active, sb.schedule_id
FROM   jtf.cac_sr_publish_schedules ps,
       jtf.cac_sr_schedules_b        sb
WHERE  ps.schedule_id = sb.schedule_id
AND    SYSDATE BETWEEN NVL(ps.start_date_active, SYSDATE)
                   AND NVL(ps.end_date_active, SYSDATE);

A secondary pattern joins OBJECT_ID to the appropriate CRM object table based on OBJECT_TYPE for attribute lookups. Auditors frequently use LAST_UPDATE_DATE to extract change history for publication changes.

Related Objects

• CAC_SR_SCHEDULES_B — parent schedule definition; joined via SCHEDULE_ID.
• CAC_SR_SCHEDULES_TL — translated names for the base schedule.
• CAC_SR_SCHEDULE_EXCEPTIONS — dates excluded from a published schedule.
• CAC_SR_SCHEDULE_SHIFTS — shift definitions within a schedule.
• CAC_SR_SCHEDULE_DETAILS — detailed calendar rows underlying a schedule.
• JTF_OBJECTS_B / JTF_OBJECTS_TL — object-type metadata referenced by OBJECT_TYPE.
• JTF_RS_RESOURCE_EXTNS — common OBJECT_ID target for resource-based publications.