Results for “csd_bulletin_scs_u1”

5 results




Overview
Explore in AI Chat

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

Overview

CSD.CSD_BULLETIN_SCS is a transactional intersection table in the Oracle E-Business Suite 12.1.1 and 12.2.2 Customer Service (CSD) schema. It links a service bulletin to one or more service codes, establishing the many-to-many relationship that determines which service codes a given bulletin applies to. The table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and its indexes are stored separately in APPS_TS_TX_IDX. The object carries FND design data under the identifier CSD.CSD_BULLETIN_SCS and is documented as VALID in ETRM.

From a Data Vault modeling perspective, CSD_BULLETIN_SCS is best classified heuristically as a link table. It holds no descriptive business attributes of its own; its purpose is to resolve the association between two hub-like entities — CSD_BULLETINS_B and CSD_SERVICE_CODES_B — through the foreign key columns BULLETIN_ID and SERVICE_CODE_ID. This classification is a modeling suggestion derived from the foreign key structure rather than an Oracle-defined construct.

Key Information Stored

The table contains nine documented columns. The most significant are:

  • BULLETIN_SC_ID — the surrogate primary key, a NUMBER column that uniquely identifies each bulletin-to-service-code association row. It is enforced by the unique index CSD_BULLETIN_SCS_U1 and by the primary key constraint CSD_BULLETIN_SCS_PK1.
  • BULLETIN_ID — foreign key to CSD.CSD_BULLETINS_B, identifying the parent bulletin. Together with SERVICE_CODE_ID it forms the business-key candidate enforced by the unique index CSD_BULLETIN_SCS_U2.
  • SERVICE_CODE_ID — foreign key to CSD.CSD_SERVICE_CODES_B, identifying the service code to which the bulletin applies.
  • OBJECT_VERSION_NUMBER — the standard Oracle Applications object version column used for optimistic locking during concurrent updates.
  • CREATED_BY, CREATION_DATE — the Who columns recording the user who inserted the row and the insertion timestamp (CREATED_BY is a foreign key to FND_USER.USER_ID).
  • LAST_UPDATED_BY, LAST_UPDATE_DATE — the Who columns recording the user who last modified the row and the modification timestamp.
  • LAST_UPDATE_LOGIN — the operating system login of the last updating user, keyed to FND_LOGINS.LOGIN_ID.

The dual uniqueness — one index on the surrogate key and another on the BULLETIN_ID/SERVICE_CODE_ID pair — guarantees that a given service code cannot be associated with the same bulletin more than once, which is the essential business rule of the table.

Common Use Cases and Queries

The most common operational and reporting need is to enumerate the service codes attached to a bulletin, or conversely the bulletins attached to a service code. A representative query joins the intersection table to both parent tables:

  • Listing service codes for a bulletin: SELECT b.bulletin_id, sc.service_code_id FROM csd.csd_bulletin_scs sc JOIN csd.csd_bulletins_b b ON b.bulletin_id = sc.bulletin_id WHERE sc.bulletin_id = :p_bulletin_id;
  • Finding bulletins applicable to a service code: filtering on SERVICE_CODE_ID to drive service-order or repair-diagnosis logic.
  • Audit and reconciliation reporting: using CREATION_DATE and LAST_UPDATE_DATE to detect recently modified bulletin mappings, and CREATED_BY / LAST_UPDATED_BY for user attribution.
  • Integrity validation: counting rows grouped by BULLETIN_ID to confirm that every bulletin has at least one service code association.
  • Extract scripts that begin with the documented query text and then join outward to the bulletin and service code base tables to produce a flattened reporting view.

Related Objects

The table's relationships are defined by its foreign keys and by the objects that depend on it:

  • CSD.CSD_BULLETINS_B — the parent bulletin base table, joined on CSD_BULLETIN_SCS.BULLETIN_ID = CSD_BULLETINS_B.BULLETIN_ID.
  • CSD.CSD_SERVICE_CODES_B — the parent service code base table, joined on CSD_BULLETIN_SCS.SERVICE_CODE_ID = CSD_SERVICE_CODES_B.SERVICE_CODE_ID.
  • CSD.CSD_BULLETIN_SCS# — the companion object referenced in dependency information, typically the shadow or synonym representation used during patching and editioning.
  • FND_USER — referenced indirectly through the CREATED_BY and LAST_UPDATED_BY Who columns.
  • FND_LOGINS — referenced through the LAST_UPDATE_LOGIN column.
  • The unique indexes CSD_BULLETIN_SCS_U1 and CSD_BULLETIN_SCS_U2, which are the primary access paths for lookups by surrogate key and by bulletin/service-code pair respectively.

Because the dependencies section states that CSD_BULLETIN_SCS references no database object directly beyond its FK targets and is itself referenced only by the CSD-owned companion object, the intersection table functions as a self-contained linking entity within the CSD schema.