Search Results csd_return_stream_reasons_u1

Overview

CSD.CSD_RETURN_STREAM_REASONS is a reference and configuration table in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases, owned by the CSD schema. Its documented purpose is to link return streams to a return reason code. In operational terms, the table defines which return reasons are valid for a given return stream, and it further qualifies each pairing with flags that indicate whether the combination is associated with takeback, dead-on-arrival (DOA), or defective handling. Because return streams and return reasons are both modeled as lookup codes, this table functions as a controlled cross-reference that constrains and enriches the values presented to users and consumed by the returns processing logic.

The table resides in the APPS_TS_TX_DATA tablespace, and its unique indexes are placed in APPS_TS_SEED, reflecting the fact that the relationship data it holds is seeded and validated reference data rather than high-volume transactional data. The documented physical schema contains 29 columns and is defined with a primary key named CSD_RETURN_STREAM_REASONS_PK1 on RETURN_STREAM_REASON_ID. The standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER support concurrency control and auditability. Based on the supplied heuristic Data Vault classification, the object is standalone; in Data Vault modeling terms it may therefore be treated as a reference or link-style construct, with the unique key on (RETURN_REASON_CODE, RETURN_STREAM_CODE) serving as a strong business-key candidate.

Key Information Stored

The most important columns are the identifiers and the return classification flags:

• RETURN_STREAM_REASON_ID — the surrogate primary key and unique identifier for each return stream/reason pairing. It is the single-column unique index CSD_RETURN_STREAM_REASONS_U1 and the basis of CSD_RETURN_STREAM_REASONS_PK1.
• RETURN_STREAM_CODE — the return stream lookup code, with a documented length of 30 characters.
• RETURN_REASON_CODE — the return reason lookup code, also 30 characters.
• TAKEBACK_FLAG — indicates whether the stream/reason combination is associated with takeback handling.
• DOA_FLAG — indicates whether the combination is treated as dead on arrival.
• DEFECTIVE_FLAG — indicates whether the combination is treated as defective.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO and optimistic locking columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield segment columns (150 characters each), with ATTRIBUTE_CATEGORY at 30 characters, allowing extensibility without schema change.
• POM_OFFSET — an additional documented numeric column present in the 12.2.2 physical schema.

The business-key candidate is the pairing of RETURN_REASON_CODE and RETURN_STREAM_CODE, enforced by the unique index CSD_RETURN_STREAM_REASONS_U2. The surrogate RETURN_STREAM_REASON_ID is the unique index CSD_RETURN_STREAM_REASONS_U1. Distinguishing the two is important: the surrogate key is not meaningful to users, whereas the code pair is the logical identity of the record and should be used in joins by code and in validation checks.

Common Use Cases and Queries

Typical use cases include validating that a selected return reason is permitted for a return stream, determining whether a stream/reason combination requires takeback, DOA, or defective processing, and reporting on the configured reason coverage by stream. A basic lookup by stream is:

SELECT rsr.return_stream_code,
       rsr.return_reason_code,
       rsr.takeback_flag,
       rsr.doa_flag,
       rsr.defective_flag
FROM   csd.csd_return_stream_reasons rsr
WHERE  rsr.return_stream_code = :stream_code;

Validation by the business key uses the unique index directly:

SELECT COUNT(*)
FROM   csd.csd_return_stream_reasons
WHERE  return_stream_code = :stream_code
AND    return_reason_code = :reason_code;

Reporting queries frequently join the lookup meanings of the stream and reason codes in FND_LOOKUP_VALUES to present descriptive labels alongside the codes, and filter on DOA_FLAG or DEFECTIVE_FLAG to identify combinations requiring specialized handling. Because the table resides in APPS_TS_SEED and holds reference data, extraction and comparison across environments is a common data-migration and configuration-audit activity.

Related Objects

The supplied relationship data classifies this object as standalone with no documented foreign key dependencies. The most significant related objects are therefore the lookup-value and returns-processing objects that consume or reference these codes:

• FND_LOOKUP_VALUES — provides the meanings for RETURN_STREAM_CODE and RETURN_REASON_CODE, joined on LOOKUP_CODE and LOOKUP_TYPE.
• The return stream definition table (CSD) — the parent reference that defines RETURN_STREAM_CODE and is constrained here.
• The return reasons definition table (CSD) — the parent reference that defines RETURN_REASON_CODE and is constrained here.
• Returns processing and RMA-related objects — consume the stream/reason pairing and the TAKEBACK_FLAG, DOA_FLAG, and DEFECTIVE_FLAG values during return authorization and receipt.
• FND_DESCR_FLEX_COLUMNS / FND_DESCR_FLEX_CONTEXTS — support the ATTRIBUTE_CATEGORY and ATTRIBUTE1..15 descriptive flexfield definition.
• CSD_RETURN_STREAM_REASONS_PK1 and the unique indexes U1/U2 — enforce row identity and business-key uniqueness for the table itself.