Results for “jtf_dsp_msite_sct_sects_pk”

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

Overview

JTF_DSP_MSITE_SCT_SECTS is a CRM Foundation (JTF) transactional table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the association between mini-sites and their sections. In the Oracle CRM mini-site framework, a mini-site is a self-contained, branded web presence assembled from reusable content sections. This table records which sections belong to a given mini-site and, critically, the hierarchical parent-child relationships that govern how sections are nested and rendered within that mini-site.

The object is owned by the JTF schema and holds a VALID status in the ETRM 12.2.2 data dictionary, with a documented physical schema of 16 columns. From a Data Vault modeling perspective, the FK topology (a composite business key resolving to a single parent mini-site plus self-referencing parent/child section pointers) suggests classification as a link table. It captures relationships between hubs (mini-sites and sections) rather than descriptive attributes, though the presence of date-effective and ordering columns gives it light satellite characteristics.

Key Information Stored

The 16 documented columns fall into three functional groups: identity, relationship, and control.

• MINI_SITE_SECTION_SECTION_ID — The surrogate primary key (JTF_DSP_MSITE_SCT_SECTS_PK) and also the basis for unique index U1. This is a system-generated identifier for each association row.
• MINI_SITE_ID — Foreign key to JTF_MSITES_B. Identifies the owning mini-site. Part of unique index U2.
• PARENT_SECTION_ID — Foreign key to JTF_DSP_SECTIONS_B. Identifies the containing (parent) section. Part of U2.
• CHILD_SECTION_ID — Foreign key to JTF_DSP_SECTIONS_B. Identifies the contained (child) section. Part of U2 and U3.
• START_DATE_ACTIVE and END_DATE_ACTIVE — Date-effective windows controlling when the section association is active within the mini-site.
• LEVEL_NUMBER — The nesting depth of the child section in the mini-site hierarchy, supporting recursive rendering.
• SORT_ORDER — Determines display sequence among sibling sections sharing the same parent.
• CONCAT_IDS — A concatenated path of section identifiers, used for efficient hierarchical queries and subtree resolution.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enabling multi-tenant / operating-unit visibility filtering.
• OBJECT_VERSION_NUMBER — Optimistic locking column supporting concurrent updates.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide audit metadata.

Unique index U2 (MINI_SITE_ID, PARENT_SECTION_ID, CHILD_SECTION_ID) is the principal business-key candidate, guaranteeing that a section-to-parent assignment within a mini-site is recorded only once. U3 (CHILD_SECTION_ID, MINI_SITE_ID) supports lookup by child section.

Common Use Cases and Queries

Typical scenarios include rebuilding mini-site navigation trees, auditing section nesting, and reporting on date-effective section availability.

• Listing active sections for a mini-site:

  SELECT CHILD_SECTION_ID, PARENT_SECTION_ID, LEVEL_NUMBER, SORT_ORDER
  FROM   JTF.JTF_DSP_MSITE_SCT_SECTS
  WHERE  MINI_SITE_ID = :p_mini_site_id
  AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
                     AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
  ORDER BY LEVEL_NUMBER, SORT_ORDER;
  

• Walking the hierarchy using CONCAT_IDS to fetch an entire subtree in one predicate.
• Validating orphaned child sections by outer-joining to JTF_DSP_SECTIONS_B.
• Auditing sort-order gaps or duplicate sibling positions before publishing a mini-site.
• BI reporting on section reuse across multiple mini-sites.

Related Objects

• JTF_MSITES_B — joined on MINI_SITE_ID; the mini-site definition base table.
• JTF_DSP_SECTIONS_B — joined twice, on PARENT_SECTION_ID and CHILD_SECTION_ID; the section definition base table.
• JTF_MSITES_TL and JTF_DSP_SECTIONS_TL — translation tables supplying display names for reporting.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for access control.
• JTF_DSP_MSITE_SCT_SECTS_PK / _U1 / _U2 / _U3 — indexes enforcing identity and uniqueness.